Создание связи между двумя таблицами в Excel
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
- Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
- Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
- Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор >Имя таблицы и введите имя.
- Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения. Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
- Щелкните Данные>Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
- В окне Управление связями нажмите кнопку Создать.
- В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
- Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
- В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
- В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
- Нажмите кнопку ОК.
Дополнительные сведения о связях между таблицами в Excel
- Примечания о связях
- Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
- «Могут потребоваться связи между таблицами»
- Шаг 1. Определите, какие таблицы указать в связи
- Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблицы к другой
Примечания о связях
- Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
- Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
- В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
- Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
- Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
- Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.
- Нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
- В разделе Price (Цена) нажмите Free (Бесплатно).
- В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
- Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
- Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
- Чтобы импортировать второй набор данных, нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace.
- В разделе Type (Тип) нажмите Data Данные).
- В разделе Price (Цена) нажмите Free (Бесплатно).
- Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
- Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
- В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
- В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
- Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
- Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
- В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
- В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
- В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
- Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
- В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
- Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
- В главной таблице нажмите Сортировка по столбцу.
- В поле «Сортировать» выберите MonthInCalendar.
- В поле «По» выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
«Могут потребоваться связи между таблицами»
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
- Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
- Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Как в Эксель сравнить два столбца на совпадения и найти расхождения
Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.
Как сравнить столбцы в Эксель
Одна из особенностей приложения — возможность в Эксель сравнить столбцы (два и более) на факт отличий и различий, а после вывести результаты в виде подсвечивания цветом. Ниже рассмотрим, как правильно сделать эту работу для разного количества столбцов.
Два
При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.
Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:
- =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
- =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).
При этом в таблице выводится информация о наличии совпадений или отличий.
Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Альтернативный вариант
Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.
Больше двух
По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.
Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).
Поиск совпадений в двух и более столбцах
Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).
В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).
В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».
Как вывести результат в Эксель
Выше мы уже рассмотрел, как в Excel сравнить данные для двух или более столбцов. В указанных выше случаях информация выводится в последней строчке таблицы и показывает, совпадают данные или различаются в зависимости от задачи и используемой команды. Но существует еще один путь — выделение совпадений одним цветом в двух и более столбцах. В таком случае информация выглядит более наглядно.
Для этого в Эксель сделайте следующее:
- Выделите вертикальные секции с данными, которые нужно сравнить.
- Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
- Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».
- В появившемся диалоговом окне выберите слева пункт «Повторяющиеся», а в правом списке укажите, каким цветом будут выделяться данные. Жмите на кнопку «ОК».
- После этого в выделенной колонке подсвечиваются цветом совпадения.
При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:
- С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
- Выделите область с информацией в дополнительной колонке.
- В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
- Кликните на «Повторяющиеся значения».
- Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.
Теперь вы знаете, как в Эксель сравнить два столбика и более, а после вывести результаты цветом или в последней колонке. В комментариях поделитесь, удалось ли вам сделать работу, и какие еще методы можно использовать.
Как сравнить два столбца таблицы Excel на совпадения значений
Допустим вы работаете с таблицей созданной сотрудником, который в неупорядоченный способ заполняет информацию, касающеюся объема продаж по определенным товарам. Одной из ваших задач будет – сравнение. Следует проверить содержит ли столбец таблицы конкретное значение или нет. Конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих клавиш CTRL+F). Однако при регулярной необходимости выполнения поиска по таблице данный способ оказывается весьма неудобным. Кроме этого данный инструмент не позволяет выполнять вычисления с найденным результатом. Каждому пользователю следует научиться автоматически решать задачи в Excel.
Функция СОВПАД позволяет сравнить два столбца таблицы
Чтобы автоматизировать данный процесс стоит воспользоваться формулой с использованием функций =ИЛИ() и =СОВПАД().
Чтобы легко проверить наличие товаров в таблице делаем следующее:
- В ячейку B1 вводим названия товара например – Монитор.
- В ячейке B2 вводим следующую формулу:
- Обязательно после ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Ведь данная формула должна выполняться в массиве. Если все сделано правильно в строке формул вы найдете фигурные скобки.
В результате формула будет возвращать логическое значение ИСТИНА или ЛОЖЬ. В зависимости от того содержит ли таблица исходное значение или нет.
Разбор принципа действия формулы для сравнения двух столбцов разных таблиц:
Функция =СОВПАД() сравнивает (с учетом верхнего регистра), являются ли два значения идентичными или нет. Если да, возвращается логическое значение ИСТИНА. Учитывая тот факт что формула выполняется в массиве функция СОВПАД сравнивает значение в ячейке B1 с каждым значением во всех ячейках диапазона A5:A10. А благодаря функции =ИЛИ() формула возвращает по отдельности результат вычислений функции =СОВПАД(). Если не использовать функцию ИЛИ, тогда формула будет возвращать только результат первого сравнения.
Вот как можно применять сразу несколько таких формул на практике при сравнении двух столбцов в разных таблицах одновременно:
Достаточно ввести массив формул в одну ячейку (E2), потом скопировать его во все остальные ячейки диапазона E3:E8. Обратите внимание, что теперь мы используем абсолютные адреса ссылок на диапазон $A$2:$A$12 во втором аргументе функции СОВПАД.
В первом аргументе должны быть относительные адреса ссылок на ячейки (как и в предыдущем примере).
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
ВПР в Excel: пошаговая инструкция для чайников
Функция ВПР существенно упрощает обработку данных в Excel. Она предназначена для автоматического переноса информации из одного файла в другой. Предлагаем подробный обзор того, как работает функция ВПР, какие у нее есть возможности и варианты использования.
Бизнес
Читайте также:
Зачем нужна функция ВПР и когда ее используют
Альтернативное название ВПР в Excel — Vlookup. Инструмент перетаскивает значения в соответствии с заданной формулой. Рассмотрим на примере, как он работает. Компания торгует мебелью. У менеджера две таблицы: в одной приведены характеристики товаров и стоимость, а в другой — контакты покупателей, которые оставили заявки.
Менеджеру удобнее, чтобы в таблице с заявками отображались цены. Функция ВПР в Excel объединит информацию из двух документов и выведет стоимость каждой позиции в соответствующей ячейке. Область применения формулы ВПР не ограничивается торговлей. Другие примеры использования: - учет имущества, финансов;
- кадровое делопроизводство;
- сопоставление данных из двух таблиц.
Vlookup ценят за возможность быстро найти и перенести нужные данные — вручную процесс займет в разы больше времени.
Как пользоваться ВПР: руководство с примерами
Опишем, как работает ВПР. Программа исследует обозначенный диапазон до тех пор, пока не обнаружит указанный элемент (до первого совпадения). После этого копирует его в назначенную ячейку.
Вставляем формулу
С ВПР в Excel удобнее работать в одном документе, размещая таблицы на разных листах. Рассмотрим порядок действий.
Добавляем колонку со стоимостью в лист 2 (в примере он назван «Заявки»). Выделяем верхнюю ячейку нового столбца. В представленном образце добавлен еще один параметр — «Стоимость» (программа автоматически рассчитает чек заказа).
В верхней части рабочей панели перейдите в раздел «Формулы». Слева найдите инструмент «fx» (вставка функции). Когда нажмете, появится окно со списком доступных функций — выберите «ВПР».
Заполняем аргументы
Предстоит ввести четыре параметра: искомое значение (модель, цена, характеристика и так далее), таблицу (область поиска), номер столбца (порядковый), интервальный просмотр. Разберем каждый из них подробнее.
Искомое значение. Им в формуле ВПР служит содержимое ячейки, общее для обеих таблиц. Впоследствии программа сканирует диапазон первого листа, пока не найдет указанный идентификатор. В рассматриваемом варианте это «Аэлита». Активируем ячейку — «координаты» появятся в поле ввода. В строке формул автоматически появился фрагмент функции: «=ВПР(D3)».
Таблица. В поле «Таблица» указываем область данных (берем ее из первого листа — у нас это «Каталог»). Функция работает корректно, если элемент, который вы ищете, размещен в первом столбце. Теперь документ отображается так:
Закрепляем диапазон («F4» для Windows, комбинация «Cmd+T» для macOS). Фиксация диапазона дает возможность распространить формулу ВПР в Excel на все строки столбца. Теперь в строке формул отображается выражение «=ВПР(D3; Каталог!$A$2:$E$21)».
Номер столбца. Введем порядковый номер колонки, в которой указана искомая характеристика. «Стоимость» находится в пятом столбце. Теперь вверху отображается формула «=ВПР(D3;Каталог!$A$2:$E$21;5)».
Интервальный просмотр. Поле воспринимает два значения: «0» или «1», и определяет точность поиска. Для абсолютного совпадения установите «0». Если вас устроит приблизительное соответствие, введите «1». Теперь в строке формул отображается функция ВПР в окончательном варианте: «=ВПР(D3;Каталог!$A$2:$E$21;5;0)».
Получаем результат
Как только вы введете четыре аргумента и нажмете «Ок», в ячейке, в которую введена формула, увидите результат поиска.
Выделите ячейку с результатом, установите курсор на квадратик в нижнем правом углу и потяните его вниз, зажав кнопку компьютерной мыши.
Программа автоматически перенесет стоимость заказанной мебели из первого листа во второй. Ошибки исключены.
Аналогичным образом протягивается формула для колонки «Стоимость заказа». На изображении выше в 18-й строке в колонке с ценами отображается «#Н/Д». Это означает, что формула ВПР в ячейке некорректна.
Почему не работает ВПР в Excel
Ошибки возникают по разным причинам. Рассмотрим типичные варианты.
В ячейке F18 выводится «#Н/Д». Разберемся, почему это произошло. Проверим, есть ли искомое значение в листе «Каталог» (для 18-й строки это «Хилтон»). Оно есть, но написано с ошибкой — «Хиллтон». Так как одна буква не совпадает, приложение воспринимает это как отсутствие значения. Также ВПР не выведет результаты, если в ячейках с требуемыми критериями поиска присутствуют лишние пробелы или замена символов (латиница вместо кириллицы).
Часто пользователи забывают зафиксировать диапазон. В этом случае формула нашего примера отобразится в таком виде: «=ВПР(D3;Каталог!A2:E21;5;0)» (отсутствует знак $). Тогда таблица будет выглядеть так:
Вследствие смещения диапазона во многих ячейках отображается ошибка. Решение: установите «$» перед всеми координатами формулы.
Если диапазон обозначен некорректно, ВПР не выведет нужные результаты. Выделяйте все ячейки первого листа, которые несут полезную информацию (кроме заголовков, номеров колонок). Столбец с элементами, по которым проводится поиск, всегда размещайте первым. Перенесите колонку с нужными данными в начало листа или выделяйте диапазон так, чтобы колонка с параметром поиска размещалась слева от первой.
Как использовать формулу ВПР для сравнения двух таблиц
Давайте узнаем, какие названия моделей отсутствуют в первом листе. Удалим из него три строки:
Изменения отразились в листе «Заявки» — в трех строках возникло выражение «#Н/Д». Именно эти модели мы удалили вначале.
Проводить такое сравнение можно по любым другим параметрам: стоимости, цене, характеристикам товара.
Пример
Есть два прайса, менеджер хочет сравнить старые цены с новыми. Данные представлены в таком виде:
Подтянем значения из первой таблицы во вторую с помощью формулы ВПР:
Если обновились не только цены, но и ассортимент, результат получится такой:
Выглядит не очень эстетично. Скорректируем с помощью функции «ЕСЛИОШИБКА». После знака «=» в строке формул введите «ЕСЛИОШИБКА([ВПР — формула]); значение, которое выводится при ошибке)». В нашем случае формула получила вид: «=ЕСЛИОШИБКА(ВПР(D3;$A$3:$B$15;2;0);0)». А таблица стала выглядеть так:
Привлекайте, конвертируйте
и анализируйте ваших клиентов
Платформа омниканального маркетингаКоротко о главном
- ВПР — функция Excel, предназначенная для поиска и переноса данных из одного листа в другой.
- В общем виде формула выглядит так: ВПР (искомое значение; диапазон; номер столбца, в котором находятся нужные данные; тип интервального просмотра — «1» или «0»). «1» обозначает приблизительное совпадение («истина»), «0» — точное («ложь»).
- Порядок запуска ВПР: перейдите в «Формулы», выберите «вставить функцию», затем — «ВПР». Далее введите аргументы функции, нажмите «Ок». Выделите первую ячейку с введенной формулой, установите курсор на квадратик внизу справа, протяните по всему столбцу, чтобы воспользоваться автозаполнением.
FAQ
Как написать формулу ВПР в Excel?
Существуют два способа:
— Найдите во вкладке «Формулы» опцию «Вставить функцию» и нажмите на нее.
— Кликните на «fx» в строке формул в одной из вкладок таблицы. Справа вы увидите окно «Построитель формул» — найдите в нем функцию ВПР и кликните на «Вставить функцию».Почему выходит значение «0»?
Самая распространенная причина — ошибка во вводе аргумента «номер_столбца» или указание числа меньше единицы для значения индекса.
Почему ВПР выдает некорректные данные?
Возможно, типы информации не идентичны. Например, если вы используете ВПР в численном значении, а исходные данные сохраняются в виде текста, формула работать не будет.