No Image

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

СОДЕРЖАНИЕ
1 просмотров
10 марта 2020

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

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция "Мастер подСтановок" больше не доступна в Excel.

Вот пример того, как использовать функцию ВПР.

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого необходима функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этим аргументом может быть ссылка на ячейку или фиксированное значение, например "Иванов" или "21 000". Второй аргумент — это диапазон ячеек с ячейкой C2: E7, в которой нужно найти искомое значение. Третьим аргументом является столбец в этом диапазоне ячеек, который содержит искомое значение.

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

В этом примере показано, как работает функция. При вводе значения в ячейке B2 (первый аргумент) функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбец E (Третий аргумент).

Четвертый аргумент пустой, поэтому функция возвращает приблизительное совпадение. Если это не так, необходимо было бы ввести одно из значений в столбцах C или D, чтобы получить результат.

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

Использование функции индекс и СОВПАДЕНИе вместо функции ВПР

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

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо ВПР см. в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ Билл Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

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

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

Совет: Прежде чем вставлять данные в Excel, задайте ширину столбцов для столбцов от A до 250 пикселей и нажмите кнопку Перенос текста (вкладка " Главная ", Группа " Выравнивание ").

Бывает у вас такое: смотришь на человека и думаешь "что за @#$%)(*?" А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза "внешность обманчива" работает на 100%. Одна из наиболее многогранных и полезных – функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Читайте также:  Лучшие сайты для создания сайтов бесплатно

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

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

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов – заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

Вариант 4. Ссылка на столбец / строку

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

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

Нечто похожее можно реализовать функцией СМЕЩ (OFFSET) , но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.

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

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

Описание функции ИНДЕКС

ИНДЕКС входит в категорию операторов “Ссылки и массивы”, причем имеет два разных набора аргументов:

1. Для массивов

Формула функции в данном случае выглядит так:

Может быть заполнен один из двух аргументов: “Номер_строки” или “Номер_столбца”. Все зависит от того, с каким массивом приходиться иметь дело. Если, например, массив занимает только одну строку, то аргумент “Номер_строки” не актуален, и заполняется только “Номер_столбца”. Для массива, занимающего всего лишь один столбец все аналогично. Здесь есть один нюанс – указывать нужно номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри заданного массива.

2. Для ссылок

Формула функции выглядит следующим образом:

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

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

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

Функция ИНДЕКС для массивов

Допустим, у нас есть таблица с наименованиями товаров, их ценой, количеством и итоговой суммой.

Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.

  1. Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).
  2. В открывшемся окне вставки функции выбираем категорию “Ссылки и массивы” (или “Полный алфавитный перечень”), отмечаем строку “ИНДЕКС” и жмем OK.
  3. Далее программа предложит на выбор один из двух наборов аргументов, о которых мы писали выше (для массива или для ссылок). В рамках поставленной задачи выбираем первый вариант и жмем OK.
  4. Теперь нам нужно заполнить аргументы функции:
    • в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
    • в аргументе “Номер_строки” пишем цифру 5, так как согласно поставленной задаче требуется отобрать 5-ую позицию из списка.
    • в значении аргумента “Номер_столбца” пишем число 1, потому что наименования позиций находятся в первом столбце рассматриваемого массива.
    • по готовности жмем OK.
    • В выбранной ячейке отобразится результат согласно заданным условиям отбора в аргументах функции. В нашем случае – это содержимое ячейки, находящейся в 1-ом столбце и 5-ой строке выделенного массива.

    Как мы ранее отмечали, один из аргументов функции (“Номер_столбца” или “Номер_строки”) может оставаться незаполненным, если выделенный массив будет одномерным, т.е. занимать либо одну строку, либо один столбец. На практике это выглядит следующим образом.

    1. В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.
    2. Нажав кнопку OK мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формула будут отличаться от первоначального варианта.

    Функция ИНДЕКС для ссылок

    А сейчас давайте разберемся, как можно работать с несколькими таблицами, используя функцию ИНДЕКС. В этом случае нам понадобится список аргументов для ссылок с полем “Номером_области”.

    Допустим, у нас есть 4 таблицы. В каждой из них представлена информация по продажам за определенный период времени (1, 2, 3 и 4 кварталы).

    Нам нужно узнать продажи 4-ой позиции (“Системный блок”) за второй квартал в штуках.

    1. Встаем в ячейку, куда планируем выводить итоговый результат и жмем кнопку “Вставить функцию” (fx).
    2. Выбираем функцию ИНДЕКС и жмем OK.
    3. Во вспомогательном окошке останавливаемся на втором варианте (для ссылки) и жмем кнопку OK.
    4. Отобразится окно с аргументами функции для заполнения:
      • поле “Ссылка” заполняется таким же образом, как и аргумент “Массив” в примере выше (вручную или методом выделения в самой таблице). Единственная разница заключается в том, что в данном случае нам нужно указать вместо одного сразу 4 диапазона ячеек, перечислив их через точку с запятой. Т.е. указываем первую область, ставим знак “;”, затем указываем вторую область и т.д. Когда все будет сделано, ставим открывающую и закрывающую скобки в начале и конце ссылки, соответственно.
      • в значении аргумента “Номер_строки” пишем число 4, так как нас интересуют данные по четвертой позиции.
      • в поле “Номер_столбца” пишем число 3, так как нам на нужны продажи в шт., а это третий по счету столбец в выделенных диапазонах.
      • в поле аргумента “Номер области” указываем число 2, так как требуется отобразить данные по второму кварталу, что соответствует второму отмеченному диапазону в аргументе “Ссылка”.
      • когда все готово, жмем OK.
      • В выбранной ячейке с функцией отобразится требуемый результат согласно заданным условиям в аргументах.

      Использование с оператором СУММ

      Оператор ИНДЕКС можно использовать вместе с функцией СУММ, формула которой выглядит так:

      Если применить функцию суммирования к нашей таблице, например, к столбцу с суммой, формула будет выглядет так: =СУММ(D2:D9) .

      Читайте также:  При подготовке текста часто встречаются ситуации

      Мы можем слегка изменить формулу, добавив в нее оператор ИНДЕКС:

      1. В качестве первого аргумента функции СУММ оставляем координаты ячейки, которая является началом диапазона суммирования.
      2. Второй аргумент (крайняя нижняя ячейка диапазона) будем задавать с помощью оператора ИНДЕКС. Перейдя в режим редактирования формулы (двойным щелчком мыши по ячейке с формулой или просто кликнув по строке формул, предварительно выбрав нужную ячейку), приводим ее к следующему виду с учетом нашей таблицы:
        =СУММ(D2:ИНДЕКС(D2:D9;8)) .Цифра 8 означает, что мы ограничиваем указанный диапазон между ячейками D2 и D9 позицией под номером 8. Мы можем задать любую цифру (больше или равно 0), не превышающую общее количество всех позиций в выбранном массиве.
      3. Нажав Enter мы получим результат в выбранной ячейке.

      Сочетание с функцией ПОИСКПОЗ

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

      Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.

      Формула функции выглядит следующим образом:

      Давайте “пробежимся” по аргементам функции:

      • Искомое значение – то значение, которе мы хотим найти в выделенном диапазоне;
      • Просматриваемый массив – область ячеек, в которой мы будем искать искомое значение;
      • Тип сопоставления – с помощью данного аргумента (не является обязательным) можно задать точность поиска.

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

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

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

      1. Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
      2. Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
      3. Выбираем функцию ИНДЕКС из списка операторов.
      4. Выбираем список аргументов для массива и жмем OK.
      5. Приступаем к заполнению аргументов:
        • в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
        • в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
          • перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
          • в раскрывшемся перечне выбираем пункт “Другие функции”.
          • в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
          • в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения. В аргумент “Тип_сопоставления” указываем цифру .
          • Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.
          • появится снова список аргументов функции ИНДЕКС. Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK.Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
          • Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
          • Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
          • Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.

          Заключение

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

          Комментировать
          1 просмотров
          Комментариев нет, будьте первым кто его оставит

          Это интересно
          No Image Компьютеры
          0 комментариев
          No Image Компьютеры
          0 комментариев
          No Image Компьютеры
          0 комментариев
          No Image Компьютеры
          0 комментариев
          Adblock detector