Excel как использовать впр в excel: Краткий справочник: функция ВПР

Пример использования функции ВПР в Эксель: пошаговая инструкция

Sign in

Password recovery

Восстановите свой пароль

Ваш адрес электронной почты

MicroExcel.ru Уроки Excel Использование функции ВПР в программе Excel

Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.

Содержание

• Описание функции ВПР
• Применение функции ВПР на практике
• Заключение

Описание функции ВПР

Смотрите также: “Как убрать колонтитулы в Экселе”

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

С помощью данного инструмента Excel можно отыскать совпадение значения в заданном столбце и “подтянуть” необходимые данные в выбранную ячейку из другой таблицы. Чтобы было понятнее, давайте разберем, как работает функция на практическом примере.

Применение функции ВПР на практике

Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.

Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.

Смотрите также: “Автофильтр в Excel: как сделать, использовать, отменить”

Порядок действий в данном случае следующий:

1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
3. Теперь предстоит правильно заполнить аргументы функции:
   • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
   • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
     Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
   • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
   • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
   • в значении аргумента “Интервальный_просмотр” можно указать два значения:
     • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
     • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
     • мы выбираем первый вариант, так как нам важна предельная точность.
   • Когда все готово, нажимаем OK.
4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Заключение

Смотрите также: “Дубликаты в Excel: как найти, выделить или удалить”

Таким образом, функция ВПР – один из самых полезных инструментов при работе в Excel, когда нужно сопоставить данные двух таблиц и перенести значения из одной в другую при совпадении заданных значений. Правильное использование данной функции позволит сэкономить немало времени и сил, позволив автоматизировать процесс заполнения данных и исключив возможные ошибки из-за опечаток и т.д.

ЧАЩЕ ВСЕГО ЗАПРАШИВАЮТ

Таблица знаков зодиака

Нахождение площади трапеции: формула и примеры

Нахождение длины окружности: формула и задачи

Римские цифры: таблицы

Таблица синусов

Тригонометрическая функция: Тангенс угла (tg)

Нахождение площади ромба: формула и примеры

Нахождение объема цилиндра: формула и задачи

Тригонометрическая функция: Синус угла (sin)

Геометрическая фигура: треугольник

Нахождение объема шара: формула и задачи

Тригонометрическая функция: Косинус угла (cos)

Нахождение объема конуса: формула и задачи

Таблица сложения чисел

Нахождение площади квадрата: формула и примеры

Что такое тетраэдр: определение, виды, формулы площади и объема

Нахождение объема пирамиды: формула и задачи

Признаки подобия треугольников

Нахождение периметра прямоугольника: формула и задачи

Формула Герона для треугольника

Что такое средняя линия треугольника

Нахождение площади треугольника: формула и примеры

Нахождение площади поверхности конуса: формула и задачи

Что такое прямоугольник: определение, свойства, признаки, формулы

Разность кубов: формула и примеры

Степени натуральных чисел

Нахождение площади правильного шестиугольника: формула и примеры

Тригонометрические значения углов: sin, cos, tg, ctg

Нахождение периметра квадрата: формула и задачи

Теорема Фалеса: формулировка и пример решения задачи

Сумма кубов: формула и примеры

Нахождение объема куба: формула и задачи

Куб разности: формула и примеры

Нахождение площади шарового сегмента

Что такое окружность: определение, свойства, формулы

Как работает функция ВПР в Excel.

Подробно на примерах.

Количество просмотров:
1 294

Использование ВПР В Excel

Сегодня мы рассмотрим, как работает функция ВПР в Excel.  Рассмотрим ее особенности подробно на практических примерах из реальных рабочих ситуаций Функция ВПР в Excel позволяет извлечь данные из таблицы на пересечении указанной колонки и строки ячейки первого столбца этой таблицы, содержащей значение, указанное для поиска. Описание выглядит немного скомкано, но скоро все встанет на свои места.

Чтобы разобраться, как работает эта функция, надо немного отойти от Excel в сторону реальной жизни. Каждый из нас делает звонки. Неважно, со смартфона или айфона. Главное тут другое. Редко кто набирает нужный телефонный номер вручную. Наших контактов так много, что запоминать их нет смысла. Мы все эти номера и не запоминаем, а храним всю эту информацию в телефонной книжке нашего гаджета. И вот тут наступает интересный момент. При звонке мы выбираем ИМЯ АБОНЕНТА, с которым хотим соединиться по телефону. А вот сам звонок идет уже по его НОМЕРУ. Как это происходит? Да просто. Наш гаджет проверяет первый столбец телефонной книжки. Как только он находит выбранное нами ИМЯ АБОНЕНТА, он в строчке с найденным ИМЕНЕНМ переходит в столбец с НОМЕРАМИ и получает нужную информацию.

Такой же принцип использует и функция ВПР. Она выполняет поиск заданного нами значения в первом столбце нашей таблицы. Как только значение найдено, функция переходит в тот столбец этой таблицы, номер которого мы указали, и выдает нам найденную информацию.

Схематично это выглядит следующим образом:

Синтаксис функции ВПР.

Как же использовать функцию ВПР в Excel? Для этого вначале разберемся в порядке указания данных при вводе ВПР, другими словами, в синтаксисе. Указывают по очереди, в скобках после названия, следующие исходные данные, или, по-научному, аргументы.

1. Искомое значение. Именно его Excel будет искать в ПЕРВОМ столбце нашей таблицы. Тут важно отметить такие тонкости. Прежде всего это значение в первом столбце таблицы должно быть уникальным. Само название ВПР расшифровывается не как «Вертикальный Поиск Решения», как многие думают, а как «Вертикальный ПЕРВЫЙ Результат». Именно ПЕРВЫЙ. К примеру, в приведенной раньше на рисунке таблице нельзя для поиска использовать столбец с именем или фамилией. Они могут быть одинаковыми, а вот номер телефона всегда уникальный. Кстати, именно поэтому нельзя в телефонной книге на телефоне указать одинаковые имена для разных людей. Телефон просто не даст это сделать. Другая тонкость в том, что, как ни странно, но Excel будет искать как точное значение, так и близкое к нему. Другими словами, вовсе не факт, что ВПР найдет именно то значение, которое мы задали. Подробнее об этом поговори чуть-чуть попозже.
2. Таблица поиска. Тут надо указать диапазон, в ПЕРВОМ столбце, или, если кому-то так проще, в ПЕРВОЙ КОЛОНКЕ которого Excel станет искать заданное нами искомое значение. В этой таблице может быть любое количество столбцов. Столбец может быть и только один. Обычно такое применяется, когда надо определить, присутствует ли наше значение в списке или нет. С другой стороны, в этом случае проще и быстрее использовать функции ПОИСКПОЗ или СЧЕТЕСЛИ.
3. Номер столбца. Порядковый номер столбца нашей таблицы, из которого мы желаем получить результат. Еще раз обращаю внимание на этот аспект. Наше тестовое значение Excel ищет в первом столбце, а вот результат берет из этой же таблицы, но уже колонки с указанным нами номером. Еще раз отмечу – номер столбца задается ВНУТРИ таблица поиска. Именно поэтому на примере ниже третьим аргументом указана цифра «2», хотя если считать от начала листа, то колонка явно не будет третьей.

4. Интервальный просмотр. Это если говорить официально. А если по-простому, то способ поиска, о чем уже говорилось. Есть два варианта.

• Указать слово ИСТИНА, ввести цифру 1 или вообще ничего не указать. Тогда Excel будет искать как само значение, так и ближайшее к нему, но меньше. Значения в первом столбце нашей таблицы поиска должны располагаться по возрастанию. Данный вариант применяется, когда надо найти не конкретное значение, а обработать некий диапазон. На предыдущем рисунке обрабатываются диапазоны цен, для каждого из которых задан свой процент скидки.
• Написать слово ЛОЖЬ или цифру 0. В этом случае будет идти точный поиск заданного значения. Именно таким образом по указанному номеру мы определили фамилию абонента.

Работа функции ВПР и возможные проблемы.

Часто на просторах интернета можно найти высказывание, что если задать четвертым аргументом значение ИСТИНА ИЛИ 1, то ВПР сработает быстрее. Это неправда. Посмотрите на скриншоты ниже. Обратите внимание, что быстрее оказался вариант, в котором в качестве интервального просмотра использована ЛОЖЬ.

На самом деле скорость зависит от расположения заданного нами искомого значения в таблице. Если оно ближе к началу, то быстрее сработает вариант с ЛОЖЬЮ, а если ближе к концу, то быстрее вариант с ИСТИНОЙ. Дело в самом поиске. Если мы укажем четвертым аргументом ИСТИНУ, то Excel ищет наше значение в первом столбце СНИЗУ ВВЕРХ. Если же выбрать вариант ЛОЖЬ, то поиск будет идти СВЕРХУ ВНИЗ.

При выборе варианта ИСТИНА Excel двигается по первому столбцу вверх до тех пор, пока не наткнется либо на указанное нами значение, либо на первое же значение, которое заведомо меньше указанного нами. Как только это произошло, поиск заканчивается.

Именно поэтому в примере ниже для директора Excel выводит оклад равный окладу бухгалтера, хотя вообще-то для директора значатся совсем другие цифры…

Еще раз повторю. Если мы указываем в конце истину, или единицу, то первый столбец в нашей таблице для поиска ОБЯЗАН быть по возрастанию. Именно с этим связана проблема на скриншоте выше.

В реальных таблицах из жизни, а не учебных, сортировка первого столбца по возрастанию встречается крайне редко. Более того, зачастую она просто невозможна, как в примере ниже. Учитывая это, применяйте вариант с приблизительным поиском, то есть с единицей либо с истиной в качестве интервального просмотра очень осторожно и только тогда, когда это действительно нужно. В том же примере с окладами мы не увидели никакой ошибки, хотя Excel из-за нашей ошибки взял явно не те данные.

Вторая проблема всплывает при указании в качестве искомого значения числового кода. Посмотрите сами на следующее.

Значение в столбце код есть, а ВПР не смог найти расчетную цену. Как же так? А все просто.

Составители прайса применили формат из девяти нулей. В результате те позиции, где не оказалось реальных чисел заполнились нулями. Обратите внимание на иллюстрации ниже на строку формул.

Еще одна проблема может быть связана с тем, что заданное нами для поиска число в первом столбце может быть указано как текст.

Работа функции ВПР и исправление ошибок.

Вы видите, что не всегда Функция ВПР в Excel работает безошибочно. Как же избежать неверного результата при работе ВПР? Прежде всего создавайте таблицы поиска аккуратно. Если уж вам позарез нужны нули в начале кода и без них ну никак, используйте текстовый формат. Да, Excel пожалуется, что цифры записаны как текст. Уверяю вас, на распечатке это никак не скажется. Excel печатает метки ошибок только если вы сами это указали в настройках печати и в параметрах.

Если же таблица уже готовая, проверьте, имеются ли ошибки при поиске значения, которое в ней явно присутствует. Обычно это как раз числа в виде текста. Если это так, добавьте в начало колонку, в которой принудительно переведите все значения в текст с помощью одноименной функции и используйте для поиска именно этот столбец. Как вариант, примените комбинацию ВПР и ЕСЛИОШИБКА. Например, в следующем примере Excel принудительно ищет заданное значение в первом столбце как число, а если это не удается, то уже как текст.

Обратите внимание, что несовпадение в форматах уже никак не помешало получить результат

Заключение. 

Сегодня мы с вами разобрали, как использовать функцию ВПР в Excel, как ее правильно применять в работе. Мы узнали, с какими проблемами можно столкнуться при использовании ВПР. Выяснили причину ошибок в работе данной функции и способы из решения.

Остается только добавить, что, во-первых, в Excel имеется полностью идентичная по написанию и применению функции ВПР функция ГПР. Разница только в том, что если ВПР работает со столбцами, то ГПР работает со строками. Во-вторых, в английском варианте эти функции называются соответственно VLOOKUP и HLOOKUP. Ну и в-третьих, если ваше искомое значение не находится в самом первом столбце, к примеру, если вам в прайсе, скриншот которого приведен выше, надо по названию товара определить его код, то использование ВПР ничего не даст. Надо применять комбинацию функций ИНДЕКС или СМЕЩ с функцией ПОИСКПОЗ. ОБ этом мы поговорим немного попозже.

На этом наш урок закончен. Надеюсь, было полезно не только новичкам. До встречи на страницах нашего сайта!

498-11cookie-checkКак работает функция ВПР в Excel. Особенности применения.no

Создание связи между таблицами в Excel

Использовали ли вы когда-нибудь функцию ВПР для переноса столбца из одной таблицы в другую? Теперь, когда в Excel есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе сопоставления данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы взяты из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете захотеть импортировать и связать данные аналитики времени, чтобы анализировать модели продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

Когда вы импортируете связанные таблицы из реляционной базы данных, Excel часто может создавать эти отношения в модели данных, которую он строит за кулисами. Во всех остальных случаях вам нужно будет создавать связи вручную.

1. Убедитесь, что рабочая книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.

2. Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.

3. Дайте каждой таблице значимое имя: В Работа с таблицами щелкните Дизайн > Имя таблицы > введите имя.

4. Убедитесь, что столбец в одной из таблиц имеет уникальные значения данных без дубликатов. Excel может создать связь только в том случае, если один столбец содержит уникальные значения.

   Например, чтобы связать продажи клиентов с аналитикой времени, обе таблицы должны включать даты в одном и том же формате (например, 01.01. 2012), и по крайней мере в одной таблице (аналитика времени) каждая дата указана в столбце только один раз.

5. Нажмите Данные > Отношения .

Если Отношения неактивны, ваша рабочая книга содержит только одну таблицу.

1. В поле Управление отношениями нажмите Создать .

2. В поле Создать связь щелкните стрелку Таблица и выберите таблицу из списка. В отношении «один ко многим» эта таблица должна быть на стороне многих. Используя наш пример с данными о клиентах и ​​времени, вы должны сначала выбрать таблицу продаж клиентов, потому что многие продажи, вероятно, будут иметь место в любой день.

3. Для столбца (внешнего) выберите столбец, содержащий данные, связанные с связанным столбцом (первичным) . Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

4. Для связанной таблицы выберите таблицу, которая имеет хотя бы один столбец данных, связанных с таблицей, которую вы только что выбрали для Таблица .

5. Для Связанный столбец (первичный) выберите столбец с уникальными значениями, совпадающими со значениями в столбце, который вы выбрали для Столбец .

6. org/ListItem»>

   Нажмите OK .

Подробнее о связях между таблицами в Excel

• Примечания об отношениях

• Пример: привязка данных логики времени к данным о рейсах авиакомпаний

• «Могут потребоваться связи между таблицами»

  • Шаг 1. Определите, какие таблицы следует указать в отношении

  • Шаг 2. Найдите столбцы, которые можно использовать для создания пути от одной таблицы к другой.

Примечания о взаимоотношениях

• org/ListItem»>

  Вы узнаете, существует ли связь, когда перетащите поля из разных таблиц в список полей сводной таблицы. Если вам не предлагается создать связь, Excel уже имеет информацию об отношениях, необходимую для связи данных.

• Создание связей похоже на использование ВПР: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel мог сопоставлять строки в одной таблице со строками в другой таблице. В примере с логикой операций со временем таблица Customer должна иметь значения дат, которые также существуют в таблице логики операций со временем.

• В модели данных отношения между таблицами могут быть «один к одному» (каждый пассажир имеет один посадочный талон) или «один ко многим» (на каждом рейсе много пассажиров), но не «многие ко многим». Отношения «многие ко многим» приводят к ошибкам циклической зависимости, например «Обнаружена циклическая зависимость». Эта ошибка возникает, если вы устанавливаете прямое соединение между двумя таблицами типа «многие ко многим» или косвенные соединения (цепочка отношений таблиц, которые являются «один ко многим» внутри каждого отношения, но «многие ко многим» при просмотре). до конца.Подробнее об отношениях между таблицами в модели данных.

• Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.

• Другие способы создания отношений могут быть более интуитивно понятными, особенно если вы не уверены, какие столбцы использовать. См. раздел Создание связи в представлении схемы в Power Pivot.

Пример: сопоставление данных временной аналитики с данными о рейсах авиакомпании

Вы можете узнать как об отношениях между таблицами, так и об аналитике времени, используя бесплатные данные в Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики и требуют быстрого подключения к Интернету для завершения загрузки данных за разумный период времени.

1. Запустите Power Pivot в надстройке Microsoft Excel и откройте окно Power Pivot.

2. Щелкните Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace . Домашняя страница Microsoft Azure Marketplace открывается в мастере импорта таблиц.

3. Под Цена , нажмите Бесплатно .

4. В категории щелкните Наука и статистика .

5. Найдите DateStream и нажмите Подписаться .

6. Войдите в свою учетную запись Microsoft и нажмите Войти . В окне должен появиться предварительный просмотр данных.

7. Прокрутите вниз и нажмите Выберите запрос .

8. Нажмите Далее .

9. Выберите BasicCalendarUS , а затем нажмите Готово , чтобы импортировать данные. При быстром интернет-соединении импорт займет около минуты. Когда закончите, вы должны увидеть отчет о состоянии 73 414 переданных строк. Нажмите Закрыть .

10. Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace для импорта второго набора данных.

11. Под Введите , щелкните Данные .

12. Под Цена , нажмите Бесплатно .

13. Найдите Задержки рейсов авиаперевозчика США и нажмите Выберите .

14. Прокрутите вниз и нажмите Выберите запрос .

15. Нажмите Далее .

16. Нажмите Готово , чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии 2 427 284 переданных строк. Нажмите Закрыть . Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам понадобятся совместимые столбцы в каждой таблице.

17. Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени, FlightDate , значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные того же типа данных, и по крайней мере один из столбцов ( DateKey ) содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для связи таблиц.

18. В окне Power Pivot щелкните Сводная таблица , чтобы создать сводную таблицу на новом или существующем листе.

19. В списке полей разверните On_Time_Performance и щелкните ArrDelayMinutes , чтобы добавить его в область значений. В сводной таблице вы должны увидеть общее время задержки рейсов, измеряемое в минутах.

20. Разверните BasicCalendarUS и щелкните MonthInCalendar , чтобы добавить его в область строк.

21. Обратите внимание, что в сводной таблице теперь перечислены месяцы, но общее количество минут одинаково для каждого месяца. Повторяющиеся одинаковые значения указывают на необходимость связи.

22. org/ListItem»>

    В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите Создать .

23. В связанной таблице выберите On_Time_Performance и в связанном столбце (первичном) выберите FlightDate .

24. В таблице выберите BasicCalendarUS , а в столбце (иностранный) выберите DateKey . Нажмите OK , чтобы создать связь.

25. Обратите внимание, что сумма задержек в минутах теперь меняется для каждого месяца.

26. В BasicCalendarUS и перетащите YearKey в область строк над MonthInCalendar .

Теперь задержки прибытия можно разделить по годам и месяцам или другим значениям в календаре.

Советы:  По умолчанию месяцы перечислены в алфавитном порядке. Используя надстройку Power Pivot, вы можете изменить сортировку, чтобы месяцы отображались в хронологическом порядке.

1. Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.

2. В домашней таблице нажмите Сортировать по столбцу .

3. В Сортировке выберите MonthInCalendar

4. org/ListItem»>

   В By выберите МесяцГода .

Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, поскольку фид DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу аналитики времени, ваш шаг будет другим.

«Могут потребоваться связи между таблицами»

При добавлении полей в сводную таблицу вы будете проинформированы о том, требуется ли связь между таблицами для понимания полей, выбранных вами в сводной таблице.

Хотя Excel может сообщить вам, когда требуется связь, он не может сказать вам, какие таблицы и столбцы использовать, или даже возможна ли связь между таблицами. Попробуйте выполнить следующие шаги, чтобы получить ответы, которые вам нужны.

Шаг 1: Определите, какие таблицы указать в отношении

Если ваша модель содержит всего несколько таблиц, может быть сразу очевидно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, может понадобиться помощь. Один из подходов — использовать представление схемы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной части модели.

Примечание. Возможно создание неоднозначных отношений, которые недопустимы при использовании в сводной таблице или отчете Power View. Предположим, что все ваши таблицы каким-то образом связаны с другими таблицами в модели, но при попытке объединить поля из разных таблиц вы получаете сообщение «Могут потребоваться связи между таблицами». Наиболее вероятная причина заключается в том, что вы столкнулись с отношениями «многие ко многим». Если вы проследите цепочку отношений между таблицами, которые связаны с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или более связи между таблицами типа «один ко многим». Не существует простого обходного пути, подходящего для любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одну таблицу.

Шаг 2. Найдите столбцы, которые можно использовать для создания пути от одной таблицы к другой

После того, как вы определили, какая таблица не связана с остальной частью модели, просмотрите ее столбцы, чтобы определить, содержит ли другой столбец в другом месте модели совпадающие значения.

Например, предположим, что у вас есть модель, которая содержит продажи продуктов по территории, и вы впоследствии импортируете демографические данные, чтобы выяснить, существует ли корреляция между продажами и демографическими тенденциями в каждой территории. Поскольку демографические данные поступают из другого источника данных, его таблицы изначально изолированы от остальной части модели. Чтобы интегрировать демографические данные с остальной частью вашей модели, вам нужно найти столбец в одной из демографических таблиц, соответствующий столбцу, который вы уже используете. Например, если демографические данные организованы по регионам, а ваши данные о продажах указывают, в каком регионе произошла продажа, вы можете связать два набора данных, найдя общий столбец, такой как «Штат», «Почтовый индекс» или «Регион», для обеспечения поиска.

Помимо сопоставления значений, есть несколько дополнительных требований для создания связи:

• Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликатов. В модели данных нули и пустые строки эквивалентны пробелу, который является отдельным значением данных. Это означает, что в столбце поиска не может быть нескольких нулей.

• Типы данных как исходного столбца, так и столбца подстановки должны быть совместимы. Дополнительные сведения о типах данных см. в разделе Типы данных в моделях данных.

Дополнительные сведения о связях между таблицами см. в разделе Связи между таблицами в модели данных.

Верх страницы

Как использовать новую функцию динамического массива XLOOKUP() в Excel

Как использовать новую функцию динамического массива XLOOKUP() в Excel

Функция динамического массива Microsoft Excel XLOOKUP() может полностью заменить функции VLOOKUP() и HLOOKUP().

Изображение: 200dgr/Shutterstock

Обязательная к прочтению информация о Windows

• Виндовс 11 22х3 уже здесь

• Контрольный список: защита систем Windows 10

• Защитите свою сеть с помощью внешних служб безопасности Майкрософт

• Как очистить кеш DNS в Windows 10

Функции поиска Microsoft Excel являются мощными, но часто неправильно понимаются, поскольку их поведение немного противоречит ожиданиям пользователей. Вы используете эти функции, когда хотите найти значения на основе значения в соответствующей ячейке. Благодаря новой функции динамического массива XLOOKUP() теперь у вас больше возможностей и меньше путаницы. В этой статье мы сравним XLOOKUP() с VLOOKUP(), чтобы вы увидели, в чем превосходство XLOOKUP(), и сразу начали его использовать.

SEE: 83 Советы по Excel, которые должен усвоить каждый пользователь (TechRepublic)

Я использую Microsoft 365 в 64-разрядной системе Windows 10. Эта новая функция доступна в Microsoft 365 и Excel 2021, а также в Excel для Интернета. Для вашего удобства вы можете скачать демонстрационный файл в формате .xlsx. В этой статье предполагается, что у вас есть базовые навыки работы с Excel, но даже новичок должен быть в состоянии следовать инструкциям для достижения успеха.

О XLOOKUP() в Excel

XLOOKUP() — одна из нескольких новых функций динамического массива. Если вы когда-либо вводили выражение с помощью Ctrl + Shift + Enter, то вы уже знакомы с тем, как Excel работал с динамическими массивами. Благодаря новой функции динамического массива эти типы выражений намного проще создавать и поддерживать, поскольку вы можете вводить выражение, как обычно, — с помощью простого Enter. Результаты перетекают в ячейки ниже, заполняя столько ячеек, сколько необходимо для завершения вычислений выражения. Это называется диапазоном разлива. Если вы видите ошибку разлива, то диапазон, необходимый для выполнения функции, недоступен. Это означает, что вы можете использовать одну функцию для возврата нескольких столбцов (или строк) результирующих значений.

XLOOKUP() возвращает данные в таблице или диапазоне построчно. Возможно, вы захотите вернуть цену товара или номер телефона клиента. Используя XLOOKUP(), вы можете быстро получить информацию на основе условия поиска в соответствующей ячейке.

Вот лишь некоторые из обновлений XLOOKUP:

• XLOOKUP() поддерживает вертикальный и горизонтальный поиск.
• XLOOKUP() выполняет поиск слева и справа, поэтому переупорядочивать столбцы больше не нужно.
• XLOOKUP() поддерживает относительные ссылки, поэтому вы можете вставлять и удалять столбцы (или строки), и функция будет обновляться соответствующим образом.
• XLOOKUP() по умолчанию использует точное совпадение, что является предпочтительным значением по умолчанию; старые функции поиска по умолчанию используют ближайшее совпадение.

Новый режим сопоставления

• XLOOKUP() обеспечивает более гибкий поиск.

ПОСМОТРЕТЬ: Windows 11: советы по установке, безопасности и т. д. (бесплатный PDF) (TechRepublic)

Теперь давайте посмотрим на синтаксис этой функции:

XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Обязательны первые три аргумента:

• искомое_значение: поисковый запрос.
• lookup_array: Диапазон поиска (или исходные данные).
• return_array: Диапазон возврата (или результатов).
• if_not_found: текст, возвращаемый, когда действительное совпадение не найдено. Если этот параметр опущен, функция возвращает #Н/Д.
• match_mode: определяет тип соответствия. Соответствующие значения и пояснения см. в таблице A .
• search_mode: указывает режим поиска. См. Таблица B для соответствующих значений и пояснений.

Стол А

Стол Б

Значение	Пояснение
1	Начать поиск с первого элемента. Это значение по умолчанию для этого аргумента.
-1	Начать поиск с последнего элемента.
2	Для поиска требуется, чтобы lookup_array был отсортирован в порядке возрастания. В противном случае функция возвращает неверные результаты, а не ошибку.
-2	Для поиска требуется, чтобы lookup_array был отсортирован в порядке убывания. В противном случае функция возвращает неверные результаты, а не ошибку.

Это много информации, но большая ее часть похожа на старые функции поиска. Теперь давайте перейдем к нескольким примерам.

Быстрое сравнение XLOOKUP() и VLOOKUP()

Функция XLOOKUP() может использоваться для возврата одного значения, аналогично функции VLOOKUP(), но использует другие аргументы. Давайте сравним две функции, используя набор данных в Рисунок А . В частности, мы вернем идентификатор сотрудника и дату, используя значение персонала в качестве условия поиска (K1).

Рисунок А

Во-первых, давайте рассмотрим функции ID:

K3: =ВПР($K$1,Таблица1[[Персонал]:[ID]],2)

K4: =XLOOKUP($K$1,Таблица1[Персонал],Таблица1[ID])

Обе функции используют значение Luke в K1 в качестве условия поиска. Самое важное, что следует отметить, это то, что функция ВПР() в K3 возвращает неправильное значение, тогда как функция ВПР() в K4 возвращает правильное значение. ВПР() требует отсортированного набора данных, а ХПР() — нет. XLOOKUP() возвращает первое совпадающее значение — настройки по умолчанию.

Функции в L3 и L4 пытаются вернуть дату на основе искомого значения Luke, используя функции

L3: =ВПР($K$1,Таблица1[[Персонал]:[ID]],-1)

L4: =XLOOKUP($K$1,Таблица1[Персонал],Таблица1[Дата])

Вероятно, вы ожидали значение ошибки в L3, потому что функция ВПР() не поддерживает поиск слева от искомого значения; функция просто не понимает аргумент, -1. XLOOKUP() делает это, и вместо использования отрицательного значения вы ссылаетесь на фактический столбец, и снова функция не возражает против того, что набор данных не отсортирован. В этом простом примере сортировка данных не имеет значения, но иногда вам приходится работать с порядком набора данных, так что это новое поведение — отличное обновление.

Оставайтесь с нами

Теперь вы можете начать использовать XLOOKUP() вместо старых функций поиска, если хотите. У вас достаточно информации для начала. Не беспокойтесь о замене старых функций поиска; сомнительно, что Microsoft откажется от них в ближайшем будущем.

В моей следующей статье мы будем использовать расширенные функции для использования нескольких критериев с XLOOKUP(). Мы также узнаем, как возвращать несколько столбцов с помощью одной функции XLOOKUP().

Сьюзан Харкинс

Опубликовано:

18 октября 2021 г., 9:18 PDT

Изменено:

16 октября 2021 г., 10:18 PDT

Увидеть больше Программное обеспечение

См. также

• Как делать меньше ошибок и работать более эффективно, используя интеллектуальный ввод текста в Microsoft 365
  (ТехРеспублика)

• Как использовать множество вариантов переноса текста в Microsoft Word
  (ТехРеспублика)

• Шпаргалка по Windows 11: все, что вам нужно знать
  (ТехРеспублика)

• Zoom против Microsoft Teams, Google Meet, Cisco WebEx и Skype: выбор подходящих приложений для видеоконференций (бесплатный PDF)
  (ТехРеспублика)

• Контрольный список: защита систем Windows 10
  (ТехРеспублика Премиум)

• Еще одно обязательное к прочтению освещение Microsoft
  (TechRepublic на Flipboard)

Поделиться: Как использовать новую функцию динамического массива XLOOKUP() в Excel

• Майкрософт

• Программного обеспечения

Выбор редактора

• Изображение: Rawpixel/Adobe Stock

  ТехРеспублика Премиум

  Редакционный календарь TechRepublic Premium: ИТ-политики, контрольные списки, наборы инструментов и исследования для загрузки
  

  Контент TechRepublic Premium поможет вам решить самые сложные проблемы с ИТ и дать толчок вашей карьере или новому проекту.

  Персонал TechRepublic

  Опубликовано:
  
  21 октября 2022 г., 9:30 по тихоокеанскому времени

  Изменено:
  
  21 октября 2022 г., 13:16 PDT
  Читать далее

  Узнать больше

• Изображение: diy13/Adobe Stock

  Программного обеспечения

  Виндовс 11 22х3 уже здесь
  

  Windows 11 получает ежегодное обновление 20 сентября, а также ежемесячные дополнительные функции. На предприятиях ИТ-отдел может выбирать, когда их развертывать.

  Мэри Бранскомб

  Опубликовано:
  
  20 сентября 2022 г., 10:00 по тихоокеанскому времени

  Изменено:
  
  20 сентября 2022 г. , 10:17 по тихоокеанскому времени
  Читать далее

  Увидеть больше Программное обеспечение

• Изображение: Кто такой Дэнни/Adobe Stock

  Край

  ИИ на переднем крае: 5 трендов, за которыми стоит следить
  

  Edge AI предлагает возможности для нескольких приложений. Посмотрите, что организации делают для его внедрения сегодня и в будущем.

  Меган Краус

  Опубликовано:
  
  19 сентября 2022 г., 11:46 по тихоокеанскому времени

  Изменено:
  
  19 сентября 2022 г., 11:46 по тихоокеанскому времени
  Читать далее

  Увидеть больше

• Изображение: яблоко

  Программного обеспечения

  Шпаргалка по iPadOS: все, что вы должны знать
  

  Это полное руководство по iPadOS от Apple. Узнайте больше об iPadOS 16, поддерживаемых устройствах, датах выпуска и основных функциях с помощью нашей памятки.

  Персонал TechRepublic

  Опубликовано:
  
  30 августа 2022 г., 7:00 по тихоокеанскому времени

  Изменено:
  
  14 сентября 2022 г., 11:07 по тихоокеанскому времени
  Читать далее

  Увидеть больше Программное обеспечение

• Изображение: Worawut/Adobe Stock
• Изображение: Bumblee_Dee, iStock/Getty Images

  Программного обеспечения

  108 советов по Excel, которые должен усвоить каждый пользователь
  

  Независимо от того, являетесь ли вы новичком в Microsoft Excel или опытным пользователем, эти пошаговые руководства принесут вам пользу.