Функция ВПР с несколькими условиями критериев поиска в Excel
Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.
Работа функции ВПР по нескольким критериям
Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:
В данном отчете необходимо найти показатель выручки для определенного торгового представителя в определенную дату. Учитывая условия поиска наш запрос должен содержать 2 условия:
- – Дата сдачи выручки в кассу.
- – Фамилия торгового представителя.
Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:
- В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
- В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
- В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
- После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.
Результат поиска в таблице по двум условиям:
Найдена сумма выручки конкретного торгового представителя на конкретную дату.
Разбор принципа действия формулы для функции ВПР с несколькими условиями:
Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.
Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:
- элемент – Дата.
- элемент – Фамилия.
- элемент – Выручка.
А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.
А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.
Число 0 в последнем аргументе функции указывает на то, то совпадение должно быть абсолютно точным.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Создание связи между двумя таблицами в 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. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
- Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
- Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Использование функции ВПР (VLOOKUP) для подстановки значений
Итак, имеем две таблицы — таблицу заказов и прайс-лист: Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.
Решение
В наборе функций Excel, в категории Ссылки и массивы(Lookup and reference) имеется функция ВПР(VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так: Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define)или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист. Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции: Заполняем их по очереди:
- Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
- Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
- Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
- Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
- Если введено значение 0 или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
- Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.
Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.
Ошибки #Н/Д и их подавление
Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:
- Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
- Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
- Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;0)
Подробнее об этом можно почитать тут. - Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
=VLOOKUP(TRIM(CLEAN(B3));прайс;0)
Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:
P.S.
Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.
Ссылки по теме
- Усовершенствованный вариант функции ВПР (VLOOKUP 2).
- Быстрый расчет ступенчатых (диапазонных) скидок при помощи функции ВПР.
- Как сделать «левый ВПР» с помощью функций ИНДЕКС и ПОИСКПОЗ
- Как при помощи функции ВПР (VLOOKUP) заполнять бланки данными из списка
- Как вытащить не первое, а сразу все значения из таблицы
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
Многоразовый ВПР (VLOOKUP)
Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5
Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:
=ЕСЛИ(ЕОШ( ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5))) ;»»; ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)) )
В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА — она позволяет решить задачу более компактно:
В англоязычной версии Excel эти функции будут выглядеть так:
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для нахождения данных в таблице
- Улучшенный вариант функции ВПР (VLOOKUP2), который умеет искать в любом столбце и не только первое значение
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
- Что такое формулы массива и с чем их едят
23.12.2012 16:32:17
Работает. Супер. ОГРОМНОЕ СПАСИБО.
ахалай махалай
25.12.2012 16:08:04
осилил и пригодилось ))) огромное спасибо!
05.01.2013 22:51:48
У меня список заказов с номерами оформлен таблицей, заменил диапазоны из примера на столбцы таблицы — не получается. Что я делаю не так? Excel 2010.
12.01.2013 07:34:22
Спасибо вам за огромную работу. А можно вынести результаты в одну ячейку? В моем отчете обычно для товары бывают 1 рода и поэтому в бланке выделена одна строке. Но иногда выходит 2-3 вида, в этом случае чтобы результаты вышли в одну ячейку. Преждевременно запасить 2-3 строки не желательно т.к. таких товаров в бланке много и на каждый товар 2-3 строки это уже превышает поле и выглядеть плохо.
20.01.2013 23:26:37
А если не в столбец, а в строку? И склеивать потом функцией СЦЕПИТЬ?
30.01.2013 10:12:40
Вот у меня такая же проблема. как их сцепить?
30.01.2013 14:18:19
Функция СЦЕПИТЬ (CONCATENATE) из категории Текстовые.
22.08.2013 15:45:55
Как сделать из столбца строку? Если транспонировать выдает ошибки!
30.01.2014 14:04:07
Чтобы вывести результирующий список в строку, необходимо
1) выделять строку вместо столбца перед вставкой формулы массива
2) изменить формулу из примера в части «СТРОКА()-5» соответственно на «СТОЛБЕЦ()-Х», где Х=номер первого столбца в списке минус 1
13.06.2018 13:01:41
Николай, у меня два глобальных вопроса. Как научиться хорошо Понимать такие вот мартёшки-формулы (чтоб самому писать их), а так же как всё запоминать? Смотрел уроки по Экселю, заносил всё в памятку, проходит неделя-две, значительную часть не помню! =(
20.01.2013 23:00:03
Для Excel 2007 и выше можно так (не формула массива):
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));»»)
Формулу протянуть вниз на несколько ячеек.
Не я придумал, из уроков Mike Girvin
20.01.2013 23:27:02
Ух ты! Интересное решение, спасибо! Только функция АГРЕГАТ, по-моему, только в 2010 версии появилась?
17.05.2016 12:23:43
Николай, добрый день!
А не могли бы вы подробнее разобрать данную формулу
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));»»)
Подставить её легко можно изменив на нужные диапазоны, но нет понимания откуда что берётся.
03.10.2014 16:07:46
Огромное спасибо. Выручил!
14.09.2022 15:42:14
Оставлю комент тут как добавлять условие
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/ ( ($E$2=$A$2:$A$16)+($E$3=$A$2:$A$16)+($D$10=$A$2:$A$16) ) ;СТРОКА()-5));»»;)
Добавляем через + в скобочках а так же не забываем общие скобки
Так же ссылку можно заменить на текст
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/ ( («Вася»=$A$2:$A$16)+(«петя»=$A$2:$A$16)+(«маша»=$A$2:$A$16) ) ;СТРОКА()-5));»»;)
22.01.2013 14:44:02
Не совсем понятно, зачем вводим минус. По идее, шапку таблицы формула и так не должна учитывать, так как мы начали диапазон со второй строки (В2:В16). Со вторым минусом также не понятно — что означает строка () и почему -5. Вообще, нельзя ли разжевать синтаксис поподробнее — например, в случае с ВПР-ом у Вас даже видео есть, и схема, откуда что берется и куда переносится, хотя сама формула ВПР и так очень наглядная и логичная. Здесь же как-то все запутано.
Заранее спасибо
24.08.2019 11:19:21
Здесь используется формула СТРОКА(). Поэтому: -1.
22.01.2013 19:44:13
Извиняюсь, функции АГРЕГАТ нет в Excel 2007.
Чтобы увидеть что происходит в какой-либо части функции нужно выделить ее, нажать F9. Возврат в прежнее состояние Ctrl+Z или Esc. Как происходит вычисление функции (формулы): панель ФОРМУЛЫ —>ВЫЧИСЛИТЬ ФОРМУЛУ.
24.01.2013 11:56:35
Добрый день,
пример СУПЕРСКИЙ-РЕСПЕКТ АВТОРУ. Мне облегчит жизнь очень и очень.
Пользуюсь excel-ем не первый день, но никогда не приходилось через панель «ФОРМУЛЫ —>ВЫЧИСЛИТЬ ФОРМУЛУ» смотреть, что и как вычисляется. Вчера пытался разобраться, но не понял сути (может есть инфа где почитнуть можно что бы разобраться?).
ПОЖАЛУЙСТА, РАЗЖУЙТЕ ДАННЫЙ КУСОЧЕК ФОРМУЛЫ. Поскольку применить для своих данных не получается.
НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»;);СТРОКА()-5))
26.01.2013 23:43:13
Индекс(Массив;Номер_строки), отсюда
Массив=$B$2:$B$16, Номер_строки=НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)
НАИМЕНЬШИЙ(Массив;k), отсюда
Массив=ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»), k=СТРОКА()-5)
ЕСЛИ(лог_выраж.;знач._если_истина;знач._если_ложь), отсюда
если значение $E$2 равно значению из диапазона A2:A16, если ИСТИНА, то СТРОКА(В2:В16)-1, иначе пусто «».
Здесь СТРОКА(В2:В16) получает массив строк =, а вычитаем 1 для уменьшения массива до = .
То же самое и с k:
Функция СТРОКА() в ячейке D6 (как в примере) получает , т.е. номер строки, вычитаем 5, получаем
В ячейке D7 получаем , вычитаем 5, получаем
Если бы диапазон «В заказ входят» начинался бы к примеру не с D6, а с D3, то мы бы отняли .
смекайте.
правильно! Два
Как мог.
11.04.2020 21:35:22
Спасибо Вам большое, за разъяснения ка и что работает в этой формуле! Весьма признателен.
28.01.2013 11:13:16
спасибо, буду разбираться.
13.02.2013 17:46:34
Добрый день, уже 4й день сижу на вашем сайте не могу найти решение:( Имеется таблица первый столбец с адресами домов и 3 следующих столбца с телефонами. Необходимо в пятый столбец вывести телефоны, которые встречаются только с одним и тем же адресом.
Я бы прикрепил пример но как это тут сделать не понятно??
15.02.2013 07:49:28
вам на форум нужно. по вопросу — поможет функция ВПР
18.02.2013 12:34:48
Да, Игорь, лучше создайте тему на форуме и приложите свой файл-пример. «По фотографии лечить тяжело», а тут файл не прикрепить — это комментарии к приему, а не форум.
25.09.2018 07:52:05
Дайте, пожалуйста, ссылку на вашу тему на форуме, если создавали. Аналогичная ситуация, сижу уже несколько дней над проблемой.
23.03.2013 10:09:18
а есть возможность, чтобы результат выводился не в столбец, а в строку?
19.06.2013 11:31:04
Можно переделать формулу или просто транспонировать столбец в стр оку.
19.06.2013 02:51:28
Николай! Благодарю за формулу!
С Вашей помощью удалось создать компактный проект. И на этом волшебство этой формулы не заканчивается: часто формулы возвращают нули, текст нулевой длины или просто есть пустые ячейки. Их можно удалять с помощью «Многоразового ВПР».
Пример на форуме
24.06.2013 19:44:04
Отличное решение, но не понял как его применить для поиска совпадений по нескольким условиям. Допустим у меня вместо одного номера заказа, столбец значений — E2 : E10, в котором числа <3;4;1;2;2;2;4;4;5>, а в D6 : D16 мне нужно вывести все значения из B2:B12, которые совпадают с числами E2:E10, в том числе и дубликаты.
30.10.2014 09:14:37
Используем ту же формулу что предложил Rustem
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1) /($E$2=$A$2:$A$16) ;СТРОКА()-5));»»)
Выделенная область является условием, что бы сделать больше условий необходимо так же через знак дроби (/) их прописать дальше. Единственное не получилось сделать условие с неопределенными типа A2&»*» в таком случае результат не выдавался.
11.02.2018 21:53:22
Все получилось)
11.04.2020 21:33:42
Спасибо Вам мил человек! Как же я это решение искал.
23.03.2021 07:35:31
Вадим, помогите, пожалуйста.
У меня не работает доп. условие через дробь, выдает ошибку #ЧИСЛО (условие ищется в том же диапазоне что и первое)
Вас не затруднит прописать на примере выше доп условие через «/» с произвольными данными просто для понимания структуры формулы.
Заранее большое спасибо!
Нигде не смог найти подобное, вся надежда на Вас!
14.09.2022 15:36:06
Оставлю комент тут как добавлять условие
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/ ( ($E$2=$A$2:$A$16)+($E$3=$A$2:$A$16)+($D$10=$A$2:$A$16) ) ;СТРОКА()-5));»»;)
Добавляем через + в скобочках а так же не забываем общие скобки
Так же ссылку можно заменить на текст
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/ ( («Вася»=$A$2:$A$16)+(«петя»=$A$2:$A$16)+(«маша»=$A$2:$A$16) ) ;СТРОКА()-5));»»;)
26.09.2013 12:19:03
Автору спасибо!
Чтобы данные выводились в строку, можно сделать так. На листе с примером выделите диапазон F2:I2
и введите в него как формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($A2=$A$2:$A$16;СТРОКА($B$2:$B$16)-1;»»);СТОЛБЕЦ()-5));»»)
Затем формулу можно «протянуть» вниз.
В строках диапазона F2:I2 напротив номера заказа по столбцам будут разнесены товары, которые в него входят. Затем формулой их можно сцепить в одну ячейку.
14.10.2013 14:13:56
Огромное спасибо автору за формулу!
27.10.2013 01:07:37
Автору респект. Очаровательная просто формула. Пользуюсь ей для превращения больших таблиц где есть множество пустых строк и столбцов в маленькие результирующие таблицы содержащие только данные. Спасибо
05.12.2013 00:08:26
А можно сделать так, чтобы переносилась не одна ячейка «Яблоки» а две «Яблоки» + цена к примеру, или «Яблоки» по названию. Или сделать то же но для двух условий.
Спасибо, если поняли.
16.01.2014 13:17:09
а если № заказа содержит 0 в начале?
24.04.2014 08:55:19
можно ли исключить дубликаты?
т.е. если номер заказа и товар совпадает несколько раз, то в итоговую таблицу попадало только одно значение?
08.05.2014 10:15:29
Я бы сначала убил дубликаты тогда, а потом уже извлекал.
08.05.2014 10:31:50
спасибо!
воспользовался «Способ 3. Выборка уникальных записей формулой»
24.07.2014 15:41:55
Николай!
здравствуйте, помогите с решением проблемы.
суть такова.
есть файл с двумя листами (лист1 и лист2) на листе1 выгрузка из программы (таблица excel) очень много строк и столбцов с данными (даты, суммы, двадцатизначные номера и т.д.)
на листе2 в ячейке А2 выпадающий список со уникальными значениями из столбца А2:А7000 листа 1. в ячейку В2 заведена заведена формула =ЕСЛИ(ЕНД(ВПР. либо (=ИНДЕКС(ПОИСКПОЗ. ) с поиском и подстановкой всех имеющихся значений из листа 1 (т.е. грубо говоря лист 2 пустой в нем выпадающий список и формула, протянутая на весь массив данных как ы листе1.
лист 2 заполняется по мере поступления заказа в обработку.
проблема 1: искать значения в выпадающем списке долго (их более 7000), заводить вручную значения из выпадающего списка тоже долго (значения двадцатизначные, можно ошибиться) какую формулу либо макрос можно применить, чтобы через фильтр искать нужные значения в выпадающем списке допустим по первым пяти введенным знакам иили более и выбирать уже из фильтра.
проблема2: при вводе значения на листе 2 вручную или через выпадающий список, значения листа 1 либо вводят сведения других срок, либо указывают на ссылку. причем если значения выпадающего списка вводятся по порядку, то бпроблем нет а если выборочно, то сведения в ячейках листа 2 не соответствуют сведениям ячеек листа 1.
24.08.2014 15:59:56
Здравствуйте!
Подскажите, пж, а как сделать так. чтобы в массиве выбиралось по значениям меньше и больше.
Например, имеем три столбца для подбора оборудования, в первом столбце значение объема помещения min, во втором — max, а в третьем наименование. Задан объем комнаты, например, 2,4 m3 и надо, что бы показало два котла (котел 1 и котел 1.1)
| 2 m3 / | 4 m3 | котел 1 |
| 2 m3 / | 4 m3 | котел 1.1 |
| 5 m3 / | 8 m3 | котел 2 |
| 7 m3 / | 12 m3 | котел 3 |
=ЕСЛИОШИБКА(ИНДЕКС($H$14:$H$50;НАИМЕНЬШИЙ(ЕСЛИ( G$58>F$14:F$50 что то сетка не отобразилась )) 29.10.2014 13:24:06 Николай, подскажите, если возможно, как усовершенствовать формулу многоразового ВПР, чтобы поиск осуществлялся по приблизительным формулировкам, а не точным (например, чтобы в запросе не писать «женьшень», а указать — «жень» или «шен», а в итоге чтобы было «женьшень»). Можно ли как-то организовать такой поиск по маске — например *женьш* и т. п.? 27.11.2014 14:41:07 Доброго времени суток! Николай — огромное спасибо, за ваши «приемы» — по работе неоценимо помогло. Работаю с большим колличеством входящих данных из БД, из которых необходима сортировка по одному уникальному значению остальных ячеек, (помогло очень VLook). А для выборки текущих заданий закрепленных на меня! + в нужном статусе — да еще и чтобы возвращалась в виде гипперссылки — неоценимо помогла (INDEX — функция, все переработал под свои нужды) т.к. INDEX у вас описанна для сравнения определенного/фиксированного «$E$2», и после нажатия Ctrl + Shift + Enter использовал только для одной ячейки и автозаполнением «протянул» вниз но при этом не верно стала работать ф-я: «СТРОКА() — n» ROW()-n) начал было в ручную менять значение «n» на нужное, но потом исправил следующим: образом: = IFERROR (INDEX(O$80:$O$5045,SMALL(IF( MID($C8,35,12 )=D$80:$D$5045,ROW($O$80:$O$5045)-79,»»), ROW()-(ROW()-1) )),INDEX(O$80:$O$5045,SMALL(IF($ C8 =D$80:$D$5045,ROW($O$80:$O$5045)-79,»»),ROW()- (ROW()-1) ))) 13.01.2015 13:11:03 В моем случае при использовании формулы =IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»») 14.01.2015 16:22:30 Добрый день! Подскажите, пожалуйста, возможно ли, чтобы список создавался, если рядом с ячейкой поставить флажок? Мне нужно из множества позиций выбирать несколько и чтобы они добавлялись в новый список, по порядку. Спасибо! 17.01.2015 22:35:13 У меня похожая задача. Есть организация с несколькими объектами. Нужно вывести список объектов. Сколько не бьюсь — ничего не получается. Попробовала точ в точ набрать Ваш пример — тоже ничего не выходит. Не могу понять, почему. Формат ячеек «общий». 04.02.2015 12:50:51 Большое спасибо за ценный пример. Давно искал способ делать такие выборки.
2 m3 —
4 m3
котел 1
2 m3 —
4 m3
котел 1.1
5 m3 —
8 m3
котел 2
7 m3 —
12 m3
котел 3
Т.к. данные в искомом диапазоне представленны в двух вариантах ссылкой, и частью ссылки, для этого использованна «MID» функция.
ячейка пустая, но когда смотришь формулу подробно в развернутом варианте, значение формула определяет верно.
Помогите, пожалуйста, разобраться почему выводится пустое значение и как получить искомое?
В моем случае заработало не с первого раза: в диапазоне ячеек была одна со значением #Н/Д