Функция в excel найти значение в массиве: Функция ПОИСКПОЗ — Служба поддержки Майкрософт

Как найти значение в массиве в Excel

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Елизавета КМ На чтение 6 мин Опубликовано 13.01.2021

Чтобы в Microsoft Office Excel найти значения ячейки, находящейся на пересечении столбика и строчки в табличном массиве, необходимо применить функцию «ИНДЕКС», а также вспомогательную «ПОИСКПОЗ». Найти значение в массиве требуется, когда пользователь работает с большой табличкой, и ему необходимо «подтянуть» ряд данных. В этой статье будет рассмотрен подробный алгоритм использования функции «ИНДЕКС» для поиска значений в массиве.

Содержание

1. Запись функции «ИНДЕКС»
2. Запись функции «ПОИСПОЗ»
3. Как найти значение в массиве
4. Заключение

Запись функции «ИНДЕКС»

Такой оператор в форме массива записывается следующим образом: =ИНДЕКС (массив; номер строчки; номер столбика). Вместо слов в скобках указываются соответствующие номера ячеек в исходной таблице.

Запись функции «ПОИСПОЗ»

Этот вспомогательный оператор для первой функции, который также будет использоваться при поиске значений в массиве. Его запись в Excel оформляется так: =ПОИСКПОЗ (значение, которое надо найти; табличный массив; тип сопоставления).

Обратите внимание! При записи аргументов для функции «ИНДЕКС» номер столбца указывать не обязательно.

Как найти значение в массиве

Для понимания темы алгоритм выполнения поставленной задачи необходимо рассмотреть на конкретном примере. Составим в Excel таблицу заказов за один день, в которой будут столбцы: «№ заказа», «Клиент», «Товар», «Количество», «Цена за ед. », «Сумма». Необходимо найти значение в массиве, т.е. составить индивидуальную карточку заказа клиента, чтобы можно было в сжатом виде получить информацию из ячеек исходной таблицы.

Внешний вид составленной таблички

Для этого надо выполнить ряд действий по алгоритму:

1. Составить карточку заказа клиента.

Карточка заказа клиента

2. Для первой строчки карточки надо создать выпадающий список, в котором будут прописаны имена клиентов из основного массива. Впоследствии, выбрав то или иное имя, пользователь увидит сжатую информацию по нему, которая отобразится в других строках карточки заказа.
3. Поставить курсор мышки в первую строку карточки и войти в раздел «Данные» сверху главного меню программы.
4. Кликнуть по кнопке «Проверка данных».
5. В отобразившемся окошке в поле «Тип данных» указать вариант «Список», а в качестве источника выделить диапазон ячеек исходного массива, в котором прописан перечень всех клиентов.

Необходимые действия в окошке «Проверка вводимых значений». Здесь выбираем вариант «Список» и указываемы диапазон всех клиентов

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

Перечень клиентов, который появится в первой строке карточки после выполнения предыдущих манипуляций

7. В строке «№ заказа» прописать функцию «=ИНДЕКС(», после чего кликнуть по значку «fx» рядом со строкой формул Эксель.
8. В открывшемся меню Мастера функций из списка выбрать форму массива для функции «ИНДЕКС» и нажать на «ОК».

Выбор формы массива для функции «ИНДЕКС»

9. Откроется окно «Аргументы функции», в котором надо заполнить все строки, указывая соответствующие диапазоны ячеек.

Внешний вид окошка «Аргументы функции»

10. Сначала надо кликнуть по пиктограмме напротив поля «Массив» и выделить целиком исходную табличку вместе с шапкой.

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

11. В поле «Номер строки» нужно заполнить функцию «ПОИСКПОЗ». На первом месте в скобке в качестве аргумента указываем название клиента, который был выбран в карточке заказа. В качестве второго аргумента функции «ПОИСКПОЗ» нужно указать весь диапазон клиентов в исходном табличном массиве. На месте третьего аргумента необходимо прописать число 0, т.к. будет искаться точное совпадение.

Заполнение поля Номер строки в меню аргументов функции. Здесь применяется оператор «ПОИСКПОЗ»

Важно! После заполнения каждого элемента для функции «ПОИСКПОЗ» потребуется нажимать на кнопку «F4», чтобы навесить знаки долларов перед каждым символом в аргументе. Это позволит формуле не «съехать» в процессе выполнения.

12. В строчке «Номер столбца» еще раз прописать вспомогательную функцию «ПОИСКПОЗ» с соответствующими аргументами.
13. В качестве первого аргумента для функции надо указать незаполненную ячейку в строке «Товар» в карточке заказа. При этом навешивать знаки долларов на аргументы теперь не надо, т.к. искомый аргумент должен получиться «плавающим».
14. Заполняя второй аргумент функции «ПОИСКПОЗ», нужно выделить шапку исходного массива, а затем нажать на кнопку «F4» для закрепления символов.
15. В качестве последнего аргумента необходимо прописать 0, закрыть скобку и щелкнуть по «ОК» внизу окошка «Аргументы функции». В данной ситуации число 0 является точным совпадением.

Заполнение поля «Номер столбца». Здесь еще раз надо указать все аргументы для функции «ПОИСКПОЗ», выделяя соответствующие диапазоны ячеек табличного массива. В качестве последнего аргумента указывается 0

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

Финальный результат. В поле«№ заказа» появилось соответствующее значение из исходного табличного массива

17. На последнем этапе формулу потребуется растянуть на все ячейки карточки заказа до конца, чтобы заполнить оставшиеся строчки.

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

Дополнительная информация! При выборе клиента из выпадающего списка карточки заказов вся информация по этому человеку отобразится в остальных строках массива.

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Excel ИНДЕКС как найти значение в массиве поиск значения в массиве ПОИСПОЗ Эксель

Adblock
detector

Поиск значений в списке данных

Excel

Формулы и функции

Для работы со ссылками

Для работы со ссылками

Поиск значений в списке данных

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще. ..Меньше

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

Что необходимо сделать

• Точное совпадение значений по вертикали в списке

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

• Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

• Точное совпадение значений по горизонтали в списке

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

• Создание формулы подступа с помощью мастера подметок (только в Excel 2007)

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Дополнительные сведения см. в этой информации.

Примеры индексов и совпадений

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.

К началу страницы

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

Для этого используйте функцию ВЛВП.

Важно:  Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.

Дополнительные сведения см. в этой информации.

К началу страницы

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

К началу страницы

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

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

Для этого используйте функцию ГГПУ.

Важно:  Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

К началу страницы

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

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

1. Щелкните ячейку в диапазоне.

2. На вкладке Формулы в группе Решения нажмите кнопку Под поиск.

3. Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.

   Загрузка надстройки «Мастер подстройок»

4. Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.

5. В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

6. В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.

7. Следуйте инструкциям мастера.

К началу страницы

Функция ПРОСМОТР в Excel с примерами формул

В учебном пособии объясняются формы векторов и массивов функции ПРОСМОТР в Excel и демонстрируется типичное и нетривиальное использование функции ПРОСМОТР в Excel с примерами формул.

Один из самых частых вопросов, который время от времени задает каждый пользователь Excel, звучит так: « Как найти значение на одном листе и перенести соответствующее значение на другой лист? ». Конечно, может быть много вариантов базового сценария: вы можете искать наиболее близкое совпадение, а не точное совпадение, вы можете искать по вертикали в столбце или по горизонтали в строке, оценивать один или несколько критериев и т. д. Однако , суть та же — нужно уметь искать в экселе.

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

• Функция ПРОСМОТР в Excel — синтаксис и использование
  • Векторная форма поиска в Excel
  • Форма массива поиска Excel
• Как использовать ПРОСМОТР в Excel — примеры формул
  • Поиск последней непустой ячейки в столбце
  • Поиск последней непустой ячейки в строке
  • Получить значение, связанное с последней записью
  • Поиск как альтернатива вложенным IF

Функция ПРОСМОТР в Excel — синтаксис и использование

На самом базовом уровне функция ПРОСМОТР в Excel ищет значение в одном столбце или строке и возвращает соответствующее значение из той же позиции в другом столбце или строке.

В Excel существует две формы ПРОСМОТРА: Вектор и Массив . Каждая форма поясняется отдельно ниже.

Функция ПРОСМОТР в Excel — векторная форма

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

Синтаксис поиска вектора следующий:

ПРОСМОТР(искомое_значение, искомый_вектор, [результативный_вектор])

Где:

• Lookup_value (обязательно) — значение для поиска. Это может быть число, текст, логическое значение ИСТИНА или ЛОЖЬ или ссылка на ячейку, содержащую искомое значение.
• Lookup_vector (обязательно) — диапазон из одной строки или одного столбца для поиска. Он должен быть отсортирован в порядке возрастания .
• Result_vector (необязательно) — диапазон из одной строки или одного столбца, из которого вы хотите вернуть результат — значение в той же позиции, что и искомое значение. Result_vector должен быть того же размера , что и lookup_range . Если опущено, результат возвращается из lookup_vector .

Следующие примеры демонстрируют две простые формулы поиска в действии.

Формула вертикального поиска — поиск в диапазоне одного столбца

Допустим, у вас есть список продавцов в столбце D (D2:D5) и товары, которые они продали, в столбце E (E2:E5). Вы создаете информационную панель, где ваши пользователи будут вводить имя продавца в B2, и вам нужна формула, которая выведет соответствующий продукт в B3. Задачу легко решить с помощью этой формулы:

=ПРОСМОТР(B2,D2:D5,E2:E5)

Чтобы лучше понять аргументы, посмотрите на этот снимок экрана:

Формула горизонтального поиска — поиск в диапазоне одной строки

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

=ПРОСМОТР(B2,E1:h2 ,E2:h3)

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

5 вещей, которые вы должны знать о векторной форме Excel ПРОСМОТР

1. Значения в lookup_vector должны быть отсортированы в порядке возрастания , то есть от наименьшего к наибольшему или от А до Я, иначе ваша формула поиска в Excel может вернуть ошибку или неправильный результат. Если вам нужно выполнить поиск на несортированные данные , затем используйте ПОИСКПОЗ ИНДЕКС или ПОИСКПОЗ СМЕЩЕНИЯ.
2. Lookup_vector и result_vector должны быть однострочными или одностолбцовыми диапазонами одинакового размера.
3. Функция ПРОСМОТР в Excel нечувствительна к регистру , она не различает текст в верхнем и нижнем регистре.
4. ПОИСК в Excel работает на основе приблизительного соответствия . Точнее, формула поиска сначала ищет точное совпадение. Если он не может точно найти искомое значение, он ищет следующее наименьшее значение , то есть наибольшее значение в lookup_vector , которое меньше или равно lookup_value .

   Например, если искомое значение равно «5», формула сначала будет искать его. Если «5» не найдено, он будет искать «4». Если «4» не найдено, он будет искать «3» и так далее.

5. Если искомое_значение на меньше , чем наименьшее значение в искомый_вектор , функция ПРОСМОТР в Excel возвращает ошибку #Н/Д.

Функция ПРОСМОТР в Excel — форма массива

Форма массива функции ПРОСМОТР ищет указанное значение в первом столбце или строке массива и извлекает значение из той же позиции в последнем столбце или строке массива.

Массив Поиск имеет 2 аргумента, оба обязательные:

ПРОСМОТР(искомое_значение, массив)

Где:

• Искомое_значение — значение для поиска в массиве.
• Массив — диапазон ячеек, в которых вы хотите найти искомое значение. Значения в первом столбце или строке массива (в зависимости от того, выполняете ли вы V-просмотр или H-просмотр) должны быть отсортированы в порядке возрастания. Прописные и строчные символы считаются эквивалентными.

Например, с именами продавцов, расположенными в первом столбце массива (столбец A), и датами заказа в последнем столбце массива (столбец C), вы можете использовать следующую формулу для поиска имени и извлечения соответствия дата:

=ПРОСМОТР(B2,D2:F5)

Примечание. Форму массива функции ПРОСМОТР Excel не следует путать с формулами массива Excel. Несмотря на то, что он работает с массивами, ПРОСМОТР по-прежнему является обычной формулой, которая завершается обычным образом нажатием клавиши Enter.

4 вещи, которые вы должны знать о форме массива Excel ПРОСМОТР

1. Если массив имеет на строк больше, чем столбцов или такое же количество столбцов и строк, формула поиска выполняет поиск в первом столбце (горизонтальный поиск).
2. Если в массиве столбцов на больше, чем строк , функция ПРОСМОТР в Excel выполняет поиск в первой строке (вертикальный поиск).
3. Если формула не может найти искомое значение, она использует наибольшее значение в массиве, которое меньше или равно искомое_значение .
4. Если значение поиска меньше наименьшего значения в первом столбце или строке массива (в зависимости от размерности массива), формула поиска возвращает ошибку #Н/Д.

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

Как использовать функцию ПРОСМОТР в Excel — примеры формул

Хотя в Excel существуют более мощные функции для поиска и сопоставления (что является предметом нашего следующего руководства), ПРОСМОТР полезен во многих ситуациях, и следующие примеры продемонстрируйте пару нетривиальных применений. Обратите внимание, что все приведенные ниже формулы используют векторную форму Excel LOOKUP.

Поиск значения в последней непустой ячейке в столбце

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

ПРОСМОТР(2, 1/( столбец <>«»), столбец )

В приведенной выше формуле все аргументы, кроме ссылки на столбец, являются константами. Итак, чтобы получить последнее значение в определенном столбце, вам просто нужно указать ссылку на соответствующий столбец. Например, чтобы извлечь значение последней непустой ячейки в столбце A, используйте следующую формулу:

=ПРОСМОТР(2, 1/(A:A<>""), A:A)

В получить последнее значение из других столбцов, изменить ссылки на столбцы, как показано на снимке экрана ниже: первая ссылка — это столбец, который нужно проверить на наличие пустых/непустых ячеек, а вторая ссылка — это столбец, из которого нужно вернуть значение:

Как работает эта формула

В аргументе lookup_value вы указываете 2 или любое другое число больше 1 (сейчас вы поймете, почему).

В аргументе lookup_vector вы помещаете это выражение: 1/(A:A<>«»)

• Сначала вы выполняете логическую операцию A:A<>«», которая сравнивает каждую ячейку в столбце A с пустая строка и возвращает TRUE для пустых ячеек и FALSE для непустых ячеек. В приведенном выше примере формула в F2 возвращает следующий массив: {ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ…}
• Затем вы делите число 1 на каждый элемент приведенного выше массива. Если TRUE приравнивается к 1, а FALSE приравнивается к 0, вы получаете новый массив, состоящий из 1 и #DIV/0! ошибок (результат деления на 0), и этот массив используется как lookup_vector . В этом примере это {1;1;1;1;#DIV/0!…}
.

Теперь, как получается, что формула возвращает последнее непустое значение в столбце, учитывая, что lookup_value не соответствует ни одному элементу lookup_vector ? Ключом к пониманию логики является то, что функция ПРОСМОТР в Excel выполняет поиск с приблизительным соответствием, т. е. когда точное искомое значение не найдено, оно соответствует следующему наибольшему значению в искомый_вектор , которое меньше, чем искомое_значение . В нашем случае lookup_value равно 2, а наибольшее значение в lookup_vector равно 1, поэтому LOOKUP соответствует последней 1 в массиве, которая является последней непустой ячейкой!

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

Совет. Если вы хотите получить номер строки , содержащей последнее значение, используйте функцию ROW для его извлечения. Например: =ПРОСМОТР(2,1/(A:A<>""),СТРОКА(A:A))

Поиск значения в последней непустой ячейке в строке

Если ваш источник данные расположены в строках, а не в столбцах, вы можете получить значение последней непустой ячейки, используя эту формулу:

ПРОСМОТР(2, 1/( строка <>«»), строка )

На самом деле эта формула не что иное, как небольшая модификация предыдущей формулы, с той лишь разницей, что вы используете ссылку на строку вместо ссылки на столбец.

Например, чтобы получить значение последней непустой ячейки в строке 1, используйте следующую формулу:

=ПРОСМОТР(2, 1/(1:1<>""), 1:1)

На следующем снимке экрана показан результат:

Получить значение, связанное с последней записью в строке

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

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

Задача может быть решена с помощью следующей формулы ПРОСМОТР:

=ПРОСМОТР(2, 1/(B2:h3="да"), $B$1:$H$1)

Логика формулы в основном так же, как описано в первом примере. Разница в том, что вы используете оператор «равно» («=») вместо «не равно» («<>») и работаете со строками, а не со столбцами.

На следующем снимке экрана показан результат:

Поиск как альтернатива вложенным ЕСЛИ

Во всех формулах поиска, которые мы обсуждали до сих пор, аргументы lookup_vector и result_vector были представлены ссылками на диапазоны. Однако синтаксис функции ПРОСМОТР в Excel позволяет предоставлять векторы в виде константы вертикального массива, что позволяет воспроизвести функциональность вложенного ЕСЛИ с помощью более компактной и удобной для чтения формулы.

Допустим, у вас есть список сокращений в столбце A, и вы хотите заменить их полными именами, где «C» означает «Завершено», «D» — «Разработка», а «T» — «Тестирование». Задачу можно выполнить с помощью следующей вложенной функции ЕСЛИ:

=ЕСЛИ(A2="c", "Завершено", ЕСЛИ(A2="d", "Разработка", ЕСЛИ(A2="t", "Тестирование ", "")))

Или, используя эту формулу поиска:

=ПРОСМОТР(A2, {"c";"d";"t"}, {"Завершено";"Разработка";" Тестирование"})

Как показано на снимке экрана ниже, обе формулы дают одинаковые результаты:

Примечание. Чтобы формула поиска в Excel работала правильно, значения в lookup_array должны быть отсортированы от А до Я или от меньшего к большему.

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

Предположим, что искомое значение находится в ячейке E2, таблица поиска — A2:C7, а интересующий столбец («Статус») — это 3 ^rd справочной таблицы, следующая формула делает работу: «}, {«Завершено»;»Разработка»;»Тестирование»})

Как показано на снимке экрана ниже, формула извлекает статус проекта из справочной таблицы и заменяет аббревиатуру соответствующим словом:

Подсказка . Если вы используете Excel 2016 в рамках подписки на Office 365, вы можете использовать функцию ПЕРЕКЛЮЧЕНИЕ для аналогичных целей.

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

Вас также может заинтересовать

Может ли функция ИНДЕКС Excel возвращать массив?

OFFSET, вероятно, функция, которую вы хотите.

 =СМЕЩЕНИЕ(A1:A4,1,2)
 

Но чтобы ответить на ваш вопрос, ИНДЕКС действительно можно использовать для возврата массива. Точнее, две функции ИНДЕКС с двоеточием между ними:

 =ИНДЕКС(A1:A4,2):ИНДЕКС(A1:A4,3)
 

Это связано с тем, что ИНДЕКС фактически возвращает ссылку на ячейку ИЛИ число, и Excel определяет, какие из них вам нужны, в зависимости от контекста, в котором вы спрашиваете. Если вы поместите двоеточие в середине двух функций ИНДЕКС, Excel скажет: «Эй, двоеточие… обычно с каждой стороны одной из них есть ссылка на ячейку», и поэтому интерпретирует ИНДЕКС именно так. Подробнее об этом можно прочитать на http://www.excelhero.com/blog/2011/03/the-imposing-index.html

На самом деле я предпочитаю ИНДЕКС смещению, потому что смещение является изменчивым, что означает, что оно постоянно пересчитывается в мгновение ока, а затем заставляет все формулы после него делать то же самое. Подробнее об этом читайте в моем посте https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

На самом деле вы можете использовать только , один INDEX и вернуть массив, но это сложно и требует так называемого разыменования. Вот некоторый контент из книги, которую я пишу по этому поводу:

Рабочий лист на этом снимке экрана имеет именованный диапазон данных, назначенный диапазону A2:E2 вверху. Этот диапазон содержит числа 10, 20, 30, 40 и 50. И он также имеет именованный диапазон под названием Элементы, назначенный диапазону A5:B5. Этот диапазон элементов сообщает формуле в A8: B8, какое из этих пяти чисел из диапазона данных следует отображать.

Если вы посмотрите на формулу в A8:B8, вы увидите, что это функция ИНДЕКС с вводом массива: {=ИНДЕКС(Данные,Элементы)}. Эта формула гласит: «Перейдите к диапазону данных и извлеките из него элементы на основе любых элементов, выбранных пользователем в диапазоне элементов». В данном конкретном случае пользователь запросил у него пятый и второй элементы. И, конечно же, это именно то, что ИНДЕКС извлекает в ячейки A8:B8: соответствующие значения 50 и 20.

Но посмотрите, что произойдет, если вы возьмете эту прекрасную функцию ИНДЕКС и попытаетесь обвести ее СУММОЙ, как показано на A11. Вы получаете неверный результат: 50+20 не равно 50. Что случилось с 20, Excel?

По какой-то причине, в то время как =ИНДЕКС(Данные,Элементы) вполне успешно извлечет откуда-то разрозненные элементы, а затем вернет эти числа по отдельности в диапазон, он довольно неохотно подчинится, если вы попросите вместо этого передать эти числа другая функция. На самом деле он настолько неохотно, что передает в функцию только первый элемент.