Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Excel works!

menu

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

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

Как это все сделать, читайте ниже 🙂

Вводная информация

В данном примере я буду использовать пример из статьи. Сравнивая использование этой конструкции с VPR, вот некоторые моменты, которые следует рассмотреть:

Индекс и Поискпоз

Насколько легко заполнять формы, когда вы видите их впервые?

Сначала нужно решить, где находится исходная таблица и откуда брать данные! Шаг за шагом, что необходимо заполнить.

  1. Вставляем формулу в нужную область таблицы
  2. На место $G:$G поставьте те ячейки, в которых должно быть найдено значение, и соответственно, должно появиться в результате. Мы ищем фамилию, значит ищите столбец с фамилиями в начальной таблице.
  3. Замените $J:$J на в зависимости, от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС, около которых должны появиться значения.
  4. Вместо $H:$H заполняем столбец, в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.

Если вам интересно, для чего нужны знаки $ в формуле, прочтите здесь

ИНДЕКС и ПОИСКПОЗ. Что это за функции?

ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.

= ИНДЕКС(массив; номер строки; номер столбца)

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

ИНДЕКС пример

Мы получили соответствующее значение, установив строки и столбцы массива данных J1:K4 равным двум.

Если мы посмотрим на исходную формулу

Это происходит потому, что вместо второго аргумента (номер строки) у нас есть формула ПОИСК. Что она здесь делает?

Читайте так же:
Как импортировать / скопировать данные из закрытой книги в текущую?

ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.

Находим номер строки = 2 в одномерном массиве $G:$G. Формула будет применяться к каждой ячейке в столбце J.

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

Функции поискпоз и индекс

Существует таблица Pupils, которая содержит данные об учениках. Данные в столбце «Имя» не отсортированы. Вы должны ввести имя и фамилию студента в одну ячейку, чтобы отобразить его номер и номер телефона.

Для решения этой проблемы удобно использовать функции SEARCHPOZ() и INDEX(). На первом этапе функция SEARCHPOZ() используется для определения номера, а на втором этапе функция INDEX() используется для определения номера телефона указанного студента (Рисунок 27).

Рисунок 27. Поиск информации

Дает номер строки или столбца элемента массива, который соответствует указанному значению указанным способом. Если вам нужна позиция элемента в диапазоне, а не сам элемент, следует использовать SEARCH() вместо VIEWER().

ПОИСК(значение_поиска; навигационная_матрица; тип_участия)

Поиск значения в массиве предполагает использование параметра sought_value. Оно используется для поиска конкретного значения в поиске. В аргументе browse_array значение sought_value представляет собой значение, для которого ищется соответствие. Искомое_значение может быть значением (число, текст или логическое значение) или ссылкой на ячейку, содержащую число, текст или логическое значение.

Просмотренный_массив — это непрерывный интервал ячеек, возможно, содержащий искомые значения.

Тип_соответствия — это число -1, 0 или 1. Excel использует match_type для определения соответствия искомого_значения со значениями в аргументе view_array.

Если match_type равен 0, функция SEARCH находит первое значение, которое точно равно аргументу seek_value. То есть он используется, когда требуется точное соответствие между значением seek_value и данными view_array. Массив view_array может быть расположен в любом порядке. Особую осторожность следует проявлять при работе с текстовыми данными. В случае ошибки:- проверьте наличие лишних пробелов;- английские и русские символы;- не сокращайте полные имена.

Читайте так же:
Как затенять пустую ячейку, пока что-то не будет введено в Excel?

Если match_type равен 1, функция SEARCH(0) находит наибольшее значение, равное или меньшее, чем search_value. Таблица_поиска должна быть расположена в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE.

Если match_type равен -1, функция ПОИСКПОЗ находит наименьшее значение, которое больше и равно искомому_значению. Массив для поиска должен быть отсортирован в порядке убывания: TRUE, FALSE, Z-A, . 2, 1, 1, 0, -1, -2, . и так далее.

Если type_comparison опущен, он принимается равным 1.

Особенности:

ПОИСКПОЗ() возвращает позицию соответствующего значения в аргументе просматриваемый_массив, а не само значение. Например: ПОИСКПОЗ(«б»;<"а";"б";"в">;0) возвращает 2 — относительную позицию буквы «б» в массиве <"а";"б";"в">.

SEARCHPOZ() не различает записи при сопоставлении с текстом.

Если функция ПОИСК() не нашла соответствующего значения, возвращается значение ошибки #N/D.

Значение_поиска может содержать символы подстановки, звездочку (*) и вопросительный знак (?). Звездочки представляют собой любую последовательность символов, а вопросительные знаки — любой отдельный символ.

В этом примере вводится формула для определения номера по имени и фамилии ученика:

=ПОИСКПОЗ(D13;D3:D10;0)

Возвращает значение или ссылку на значение из массива или диапазона. Функция INDEX() имеет два синтаксиса: ссылка и массив. Форма reference всегда возвращает ссылку, а форма array всегда возвращает значение или массив значений.

Форма матрицы. I NDEX(массив; номер_строки; номер_столбца).

Возвращает значение указанной ячейки или массив значений в аргументе array.

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

Номер_столбца — это номер столбца в таблице, из которого должно быть возвращено значение. Если аргумент column_number опущен, необходимо указать аргумент row_number.

Пример: Чтобы определить номер телефона по имени и фамилии студента, введите формулу: = INDEX(C3:E10;C13;3).

Особенности:

-Если используются аргументы row_row и column_number, функция INDEX() возвращает значение, расположенное в ячейке на пересечении указанной строки и указанного столбца.

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

Существует только один аргумент row_row или column_number, поэтому если массив занимает более одной строки и более одного столбца, функция INDEX(0) возвращает массив из строки или столбца массива.

Используя row_row или column_number равный 0 (нулю), функция INDEX() возвращает массив значений для целочисленного столбца или целочисленной строки соответственно.

В качестве формулы массива необходимо ввести функцию INDEX() в горизонтальный интервал ячеек, чтобы использовать результаты массива. Для формулы массива нажмите CTRL+SHIFT+ENTER.

Читайте так же:
Как найти или проверить, открыта ли конкретная книга в Excel?

ФУНКЦИЯ ПРОСМОТР В EXCEL НА ПРОСТОМ ПРИМЕРЕ

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

П АРАМЕТРЫ ФУНКЦИИ ПРОСМОТРА ЗАПИСИ

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

МАССИВНАЯ ФОРМА

Как и в случае с VPR и DGR, массив имеет очень похожую форму. БПФ ищет значение в первой строке, ВПР — в первом столбце, а функция VIEW — либо в первом столбце, либо в первой строке в зависимости от размерности массива. Есть и другие различия, но они менее существенны.

Эта форма записи не будет рассматриваться подробно, так как она уже давно устарела и сохранилась в Excel только из соображений обратной совместимости. Вместо этого рекомендуется использовать функции FFT или GPR.

ВЕКТОРНАЯ ФОРМА

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

Это чертовски интересно. …. Давайте рассмотрим небольшой пример, чтобы было понятнее.

ПРИМЕР №1

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

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

Первым аргументом функции VIEW является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является видимым диапазоном, также называемым видимым вектором. Из соответствующей ячейки в диапазоне A1:A7 функция VIEW возвращает результат, этот диапазон также называется вектором результата. Нажав Enter, вы убедитесь, что все правильно.

ПРИМЕР 2

Читайте так же:
Как найти значение с двумя или несколькими критериями в Excel?

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

Ниже приведен пример одного из них:

В этом примере видно, что диапазоны смещены относительно друг друга как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размеры векторов были одинаковыми. Как только мы нажмем Enter, появится нужный результат:

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

П РИМЕРЫ ИНДЕКСИРОВАНИЯ И ПОИСКА В EXCEL

I NDEXING и SEARCHING в Excel — хорошая альтернатива ERS, DGR и SPACE. Эта комбинация универсальна и сочетает в себе все возможности этих функций. А в некоторых случаях, например при поиске двумерных данных на рабочем листе, она будет просто незаменима. В этом учебном пособии мы рассмотрим различия между функциями ПОИСКПОЗ и ИНДЕКС, а затем увидим, как использовать их вместе в Excel.

Мы поговорим о UPP и VIEWER более подробно.

ФУНКЦИЯ ПОЗИЦИОННОГО ПОИСКА В EXCEL

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

Например, на рисунке ниже формула вернет число 5, поскольку имя "Дарья" находится в пятой строке диапазона A1:A9.

В следующем примере формула возвращает 3, поскольку число 300 находится в третьем столбце диапазона В1:I1.

Как видно из этих примеров, первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом является диапазон, в котором вы ищете значение. Также есть третий аргумент, который определяет тип, которому должна соответствовать функция. Он может принимать один из трех вариантов:

— 0 — Функция ПОИСК находит первое значение, которое точно равно заданному значению. Сортировка не требуется.

Функция ПОИСКПОЗ ищет наибольшее значение, которое меньше или равно значению, заданному как 1 или вовсе опущено. Сортировка в порядке возрастания.

-1 — Функция ПОИСКПОЗ ищет наименьшее значение, которое больше или равно заданному значению. Требуется сортировка в порядке убывания.

Сама по себе функция ПОИСКПОЗ обычно малопригодна, поэтому в Excel она часто используется в сочетании с функцией ИНДЕКС.

Читайте так же:
Как извлечь последние два слова из текстовых строк в Excel?

ФУНКЦИЯ «ИНДЕКС» В EXCEL

Функция ИНДЕКС возвращает данные ячейки на пересечении двух строк или столбцов. Приведенная ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 столбцов и 2 строк.

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

Одна строка или один столбец в массиве, т.е. является вектором, то второй аргумент функцииINDEX задает номер значения в этом векторе. Три аргумента не требуются.

Например, следующая формула возвращает пятое значение диапазона A1:A12 (вертикального вектора):

На основе диапазона A1:L1 (горизонтальный вектор) данная формула возвращает третье значение:

ПОДЕЛИТЬСЯ ПОИСКОМ И ИНДЕКСОМ В EXCEL

Вы должны знать, что функции FFT, FGD и SCAN в Excel способны выполнять поиск только в одномерном массиве. Иногда, однако, требуется двумерный поиск, когда необходимо одновременно искать два параметра. В таких случаях SEARCH и INDEX в Excel незаменимы.

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

Мы можем указать месяц в ячейке C15, например, май. В ячейке C16 также указывается тип продукта, например, «Овощи». В ячейку C17 введите следующую формулу:

= ИНДЕКС(B2:E13 ; КОНСУЛЬТАЦИЯ(C15;A2:A13;0) ; КОНСУЛЬТАЦИЯ(C16;B1:E1;0))

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

Функции I NDEX принимают все 3 аргумента в следующей формуле

1. Наш первый аргумент определяет диапазон поиска, который установлен на B2:E13.

(2) Вторым аргументом функции INDEX является номер строки. Мы получаем это число с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Чтобы объяснить это, нам нужно рассчитать, что дает эта формула:

Третьим аргументом в функции INDEX является номер столбца. Мы получаем это число с помощью функции SEARCH(C16;B1:E1;0). Для наглядности давайте рассчитаем это значение дальше:

В результате подстановки уже рассчитанных данных из ячеек D15 и D16 в исходную громоздкую формулу вместо функции ПОИСКПОЗ получится более компактная и четкая формула:

=ИНДЕКС(B2:E13;D15;D16)

Дата добавления: 2018-02-15; просмотров: 2490; Мы можем помочь вам написать работу!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector