Примеры формул ИНДЕКС и ПОИСКПОЗ для выборки из списка в Excel
Часто случается так что при считывании значений столбцов таблиц поиск значений для выборки следует выполнять по крайним правым столбцам. Excel предлагает несколько функций возвращающих значения ячеек находящиеся по левой стороне столбца, по которому и выполняется поиск.
Как применить формулу ИНДЕКС и ПОИСКПОЗ в Excel
Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:
Функция ИНДЕКС возвращает значение ячейки, находящееся в определенной строке и столбце указанного диапазона ячеек. В данном случае в аргументе функции как диапазон ячеек указана исходная таблица магазинов. Во втором аргументе указывается сначала номер строки, а в третьем – номер столбца. В формуле для поиска города значения считываются с первого столбца, поэтому последний аргумент содержит число 1.
Формула для поиска номера магазина по названию города возвращает значения из четвертого столбца:
Поэтому последний аргумент в функции ВПР равен числу 4.
Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки H3, которая находится во второй строке второго столбца диапазона G2:P10.
Во втором аргументе функции ИНДЕКС содержится функция ПОИСКПОЗ. В ее аргументах всегда указывается диапазон ячеек, которые содержат только одну строку или один столбец. Если указать диапазон из более чем одной строки или столбца, тогда функция ПОИСКПОЗ возвращает ошибку #Н/Д!
Чтобы получить номер соответствующей строки для функции ИНДЕКС необходимо использовать функцию ПОИСКПОЗ, которая возвращает позицию найденного значения в таблице. Функция ПОИСКПОЗ использует 3 аргумента в своем синтаксисе:
- Искомое значение – то что следует найти.
- Просматриваемый массив – одномерный массив или диапазон ячеек из одной строки или из одного столбца где должно быть найдено искомое значение.
- Тип сопоставления – точность соответствия (необязательный аргумент для заполнения), при точном соответствии указывается значение ЛОЖЬ или 0. Если требуется неточное совпадение значений, тогда ИСТИНА или 1.
В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.
После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».
Внимание! Если в аргументах функции ИНДЕКС указать номер строки больше чем количество строк в диапазоне ячеек или номер столбца больше чем количество столбцов, тогда функция возвращает ошибку #ССЫЛКА!
Как сделать выборку из списка формулой ПРОСМОТР в Excel
Формула из комбинации функций ИНДЕКС и ПОИСКПОЗ – это самая популярный тип формул для поиска значений в таблице Excel. Значительно реже используется функция ПРОСМОТР. В ее синтаксисе используется 3 аргумента:
- Искомое значение – значение позицию которого необходимо найти.
- Просматриваемый вектор – одномерный массив или диапазон ячеек из одной строки или из одного столбца где будет вестись поиск искомого значения.
- Вектор результатов — одномерный массив или диапазон ячеек из одной строки или из одного столбца из которого следует вернуть результат (необязательный для заполнения).
Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:
- Нет возможности установить точное совпадение как в функциях ВПР, ГПР и ПОИСКПОЗ.
- Просматриваемый одномерный массив вектора должен быть отсортирован по возрастанию иначе функция будет возвращать ошибочные результаты как оказано на рисунке:
Поэтому сначала перед применением функции отсортируем просматриваемый диапазон вектора по возрастанию. Следующие две формулы предназначены для альтернативного поиска города:
И соответственного номера магазина:
Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.
Первые два аргумента функции ПРОСМОТР такие же, как и у функции ПОИСКПОЗ. Эти две функции работают по одному и тому же принципу. То есть ищет заданное значение в одном направлении вектора. Отличие в том, что она возвращает не позицию где находится найденная ячейка в диапазоне с нужным значением, а содержимое соответственной ячейки находящиеся на позиции (указанной в векторе результатов) от нее. Ну и не стоит забывать, что для функции ПРОСМОТР нужно сортировать таблицу по возрастанию просматриваемого диапазона и у нее нет аргумента точности совпадения при поиске значений.
Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
СТОЛБЕЦ (функция СТОЛБЕЦ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция СТОЛБЕЦ возвращает номер столбца заданного ссылка на ячейку. Например, формула =СТОЛБЕЦ(D10) возвращает 4, поскольку столбец D — четвертый столбец.
Синтаксис
СТОЛБЕЦ([ссылка])
аргумент функции СТОЛБЕЦ описаны ниже.
-
Ссылка — необязательный аргумент. Ячейка или диапазон ячеек, для которого требуется возвратить номер столбца.
- Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
Если аргумент «ссылка» не указан или ссылается на диапазон ячеек, а функция СТОЛБЕЦ введена как формула горизонтального массива, то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива.
Определение номера столбца по его значению
В книге эксель на листе «Данные» есть таблица с значениями
На лист2 в столбец B выводится адрес ячейки, который соответствует тому или иному названию столбца.
Необходимо чтобы в столбец C выводился порядковый номер столбца
Т.е. например в ячейке А2 написано «Код», в ячейке B2 прописан адрес с названием данного столбца на листе «Данные»
В столбце C формула должна определить что столбец B на листе «Данные» является вторым по счету и поставить в ячейку С2 значение «2».
Если использую формулу:
=СТОЛБЕЦ(Данные!B2)
она работает
Но это надо вводить руками. Нужно автоматом, т.к. если в следующий раз поместить на лист «Данные» новые значения столбец с названием «Код» может быть не в столбце B, а в другом, и нужно чтобы формула автоматом нашла данный столбец и вывела его порядковый номер.
Пробовал ставить такую формулу —
=СТОЛБЕЦ(СЦЕПИТЬ("Данные!";ТЕКСТ(B2;"ММММ")))
Пишет — в формуле ошибка
Пример файла прилагаю. Заранее спасибо!
Как найти номер столбца в excel по значению ячейки
Добрый день. Подскажите пожалуйста. Есть таблица с колонками. В первой ячейке название колонки. На другом листе выпадающий список с названиями. Как определить номер колонки таблицы по значению выпадающего списка макросом?
Пользователь
Сообщений: 2735 Регистрация: 25.12.2012
07.10.2014 15:53:35
такое чувство, что можно и без макроса обойтись, но вопрос задан так, что без поллитры не обойтись. Приложите пример чтоли
Учимся сами и помогаем другим.
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 15:56:48
ber$erk, прав насчёт примера. Николай Малыгин, без файла на пальцах объяснять затруднительно.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
07.10.2014 16:07:47
| Цитата |
|---|
| Николай Малыгин пишет: Как определить номер колонки таблицы по значению выпадающего списка макросом? |
если вып.список содержит названия «колонок», то определить номер по порядку можно без макроса, при помощи функции =ПОИСКПОЗ(выпавшая_колонка;список_колонок;0).
Наверное, список_колонок — это диапазон первой строки таблицы на первом листе.
а выпавшая_колонка — это значение ячейки с выпадающим списком
а в макросе это можно точно так же сделать при помощи Application.WorksheetFunction.Match
F1 творит чудеса
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 16:45:56
Пример прилагаю. Может есть и другое решение. Я хотел по значению из списка определить номер колонки в базе и использовать в функции VLOOKUP. Но при попытке в формуле использовать вместо номера колонки значение переменной ничего не получилось.
Прикрепленные файлы
- Пример.xlsm (17.47 КБ)
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 16:51:30
=ВПР($A2;База!$1:$1048576;ПОИСКПОЗ($C$1;База!$1:$1;0);0)
в яч. C2 и протянуть вниз.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 16:55:31
А макросом возможно?
Пользователь
Сообщений: 5099 Регистрация: 22.12.2012
07.10.2014 17:00:00
как вариант вместо вашей формулы прописать новую. и судя по коду придется его (код) повесить на событие изменения ячейки С1 чтобы автоматом отрабатывал.
Изменено: V — 07.10.2014 17:01:42
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:00:19
| Цитата |
|---|
| Николай Малыгин пишет: при попытке в формуле использовать вместо номера колонки значение переменной ничего не получилось |
Не понимаю зачем макрос, если используете формулу.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 17:03:56
На листе «Счет» нет формул и не надо их там. База скрыта и закрыта.
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:12:28
Попробуйте воспользоваться Find.
MsgBox Sheets("База").Rows(1).Find(what:=Range("Счет!C1")).Column
Изменено: JayBhagavan — 07.10.2014 17:15:07
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 17:53:32
Уже хорошо, спасибо. Но как ввести это значение переменной в формулу в макросе в качестве номера столбца?. Что то ни фига не выходит
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:58:52
.Value = "=VLOOKUP(A2," & iAddress$ & ", " & Sheets("База").Rows(1).Find(what:=Range("Счет!C1")).Column & " ,0)"
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 18:14:22
Отлично. Про кавычки я забыл. Всем спасибо
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
07.10.2014 18:21:54
Только лучше эту процедуру повесить на Worksheet_Change по изменению ячейки C1
Sub ВПР() With Sheets("База") iAddress$ = .Range(.Range("A2"), .Range("A65536") _ .End(xlUp)).Resize(, 6).Address(External:=True) On Error Resume Next n = Application.WorksheetFunction.Match(Sheets("Счет").Range("C1").Value, .Rows(1), 0) If Err <> 0 Then Exit Sub On Error GoTo 0 End With With Sheets("Счет") With .Range(.Range("A2"), .Range("A65536").End(xlUp)).Offset(, 2) .Value = "=VLOOKUP(A2," & iAddress$ & "," & n & ",0)" .Value = .Value .Replace What:="#N/A", Replacement:=0 End With End With End Sub