Как использовать индекс в excel
Функция INDEX (ИНДЕКС) в Excel. Как использовать?
Функция INDEX (ИНДЕКС) в Excel используется для получения данных из таблицы, при условии что вы знаете номер строки и столбца, в котором эти данные находятся.
Например, в таблице ниже, вы можете использовать эту функцию для того, чтобы получить результаты экзамена по Физике у Андрея, зная номер строки и столбца, в которых эти данные находятся.
Что возвращает функция
Возвращает данные из конкретной строки и столбца табличных данных.
Синтаксис
=INDEX (array, row_num, [col_num]) — английская версия
=INDEX (array, row_num, [col_num], [area_num]) — английская версия
=ИНДЕКС(массив; номер_строки; [номер_столбца]) — русская версия
=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) — русская версия
Аргументы функции
Дополнительная информация
— Форма «Массива» используется когда вы хотите найти значения, основанные на конкретных номерах строк и столбцов таблицы;
— Форма «Ссылок на данные» используется при поиске значений в нескольких таблицах (используете аргумент [area_num] ([номер_области]) для выбора таблицы и только потом сориентируете функцию по номеру строки и столбца.
Примеры использования функции ИНДЕКС в Excel
Пример 1. Ищем результаты экзамена по физике для Алексея
Предположим, у вас есть результаты экзаменов в табличном виде по нескольким студентам:
Для того, чтобы найти результаты экзамена по физике для Андрея нам нужна формула:
=INDEX($B$3:$E$9,3,2) — английская версия
=ИНДЕКС($B$3:$E$9;3;2) — русская версия
Пример 2. Создаем динамический поиск значений с использованием функций ИНДЕКС и ПОИСКПОЗ
Не всегда есть возможность указать номера строки и столбца вручную. У вас может быть огромная таблица данных, отображение данных которой вы можете сделать динамическим, чтобы функция автоматически идентифицировала имя или экзамен, указанные в ячейках, и дала правильный результат.
Пример динамического отображения данных ниже:
Для динамического отображения данных мы используем комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).
Вот такая формула поможет нам добиться результата:
=INDEX($B$3:$E$9,MATCH($G$4,$A$3:$A$9,0),MATCH($H$3,$B$2:$E$2,0)) — английская версия
=ИНДЕКС($B$3:$E$9;ПОИСКПОЗ($G$4;$A$3:$A$9;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0)) — русская версия
В формуле выше, не используя сложного программирования, мы с помощью функции MATCH (ПОИСКПОЗ) сделали отображение данных динамическим.
Динамический отображение строки задается следующей частью формулы —
MATCH($G$4,$A$3:$A$9,0) — английская версия
ПОИСКПОЗ($G$4;$A$3:$A$9;0) — русская версия
Она сканирует имена студентов и определяет значение поиска ($G$4 в нашем случае). Затем она возвращает номер строки для поиска в наборе данных. Например, если значение поиска равно Алексей, функция вернет “1”, если это Максим, оно вернет “4” и так далее.
Динамическое отображение данных столбца задается следующей частью формулы —
MATCH($H$3,$B$2:$E$2,0) — английская версия
ПОИСКПОЗ($H$3;$B$2:$E$2;0) — русская версия
Она сканирует имена объектов и определяет значение поиска ($H$3 в нашем случае). Затем она возвращает номер столбца для поиска в наборе данных. Например, если значение поиска Математика, функция вернет “1”, если это Физика, функция вернет “2” и так далее.
Пример 3. Создаем динамический поиск значений с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка
На примере выше мы вручную вводили имена студентов и названия предметов. Вы можете сэкономить время на вводе данных, используя выпадающие списки. Это актуально, когда количество данных огромное.
Используя выпадающие списки, вам нужно просто выбрать из списка имя студента и функция автоматически найдет и подставит необходимые данные.
Используя такой подход, вы можете создать удобный дашборд, например для учителя. Ему не придется заниматься фильтрацией данных или прокруткой листа со студентами, для того чтобы найти результаты экзамена конкретного студента, достаточно просто выбрать имя и результаты динамически отразятся в лаконичной и удобной форме.
Для того, чтобы осуществить динамическую подстановку данных с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка, мы используем ту же формулу, что в Примере 2:
=INDEX($B$3:$E$9,MATCH($G$4,$A$3:$A$9,0),MATCH($H$3,$B$2:$E$2,0)) — английская версия
=ИНДЕКС($B$3:$E$9;ПОИСКПОЗ($G$4;$A$3:$A$9;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0)) — русская версия
Единственное отличие, от Примера 2, мы на месте ввода имени и предмета создадим выпадающие списки:
Теперь у вас есть выпадающий список с именами студентов в ячейке G5. Таким же образом вы можете создать выпадающий список с предметами.
Пример 4. Использование трехстороннего поиска с помощью INDEX (ИНДЕКС) / MATCH (ПОИСКПОЗ)
Функция INDEX (ИНДЕКС) может быть использована для обработки трехсторонних запросов.
Что такое трехсторонний поиск?
В приведенных выше примерах мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двунаправленного поиска, поскольку мы используем две переменные для получения оценки (имя студента и предмет).
Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Итоговый экзамен».
Трехсторонний поиск — это возможность получить отметки студента по заданному предмету с указанным уровнем экзамена.
Вот пример трехстороннего поиска:
В приведенном выше примере, кроме выбора имени студента и названия предмета, вы также можете выбрать уровень экзамена. Основываясь на уровне экзамена, формула возвращает соответствующее значение из одной из трех таблиц.
Для таких расчетов нам поможет формула:
=INDEX(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),MATCH($G$4,$A$3:$A$7,0),MATCH($H$3,$B$2:$E$2,0),IF($H$2=»Вступительный»,1,IF($H$2=»Полугодовой»,2,3))) — английская версия
=ИНДЕКС(($B$3:$E$7;$B$11:$E$15;$B$19:$E$23);ПОИСКПОЗ($G$4;$A$3:$A$7;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0); ЕСЛИ($H$2=»Вступительный»;1;ЕСЛИ($H$2=»Полугодовой»;2;3))) — русская версия
Давайте разберем эту формулу, чтобы понять, как она работает.
Эта формула принимает четыре аргумента. Функция INDEX (ИНДЕКС) — одна из тех функций в Excel, которая имеет более одного синтаксиса.
=INDEX (array, row_num, [col_num]) — английская версия
=INDEX (array, row_num, [col_num], [area_num]) — английская версия
=ИНДЕКС(массив; номер_строки; [номер_столбца]) — русская версия
=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) — русская версия
По всем вышеприведенным примерам мы использовали первый синтаксис, но для трехстороннего поиска нам нужно использовать второй синтаксис.
Рассмотрим каждую часть формулы на основе второго синтаксиса.
Уверен, что теперь вы подробно изучили работу функции INDEX (ИНДЕКС) в Excel!
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Функция ИНДЕКС() в EXCEL
history 29 декабря 2013 г.
Синтаксис функции
ИНДЕКС (массив; номер_строки; номер_столбца)
Массив — ссылка на диапазон ячеек.
Номер_строки — номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.
Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.
Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! Например, формула =ИНДЕКС(A2:A13;22) вернет ошибку, т.к. в диапазоне А2:А13 только 12 строк.
Значение из заданной строки диапазона
Пусть имеется одностолбцовый диапазон А6:А9.
Если диапазон горизонтальный (расположен в одной строке, например, А6:D6 ), то формула для вывода значения из 2-го столбца будет выглядеть так =ИНДЕКС(A6:D6;;2)
Значение из заданной строки и столбца таблицы
Пусть имеется таблица в диапазоне А6:B9.
Использование функции в формулах массива
Зачем это нужно? Теперь удалить по отдельности значения из ячеек А21 , А22 , А23 не удастся, мы получим предупреждение «нельзя изменять часть массива».
Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Затем нажмите CTRL+SHIFT+ENTER и получим тот же результат.
Использование массива констант
Вместо ссылки на диапазон можно использовать массив констант :
ПОИСКПОЗ() + ИНДЕКС()
Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает цену товара Яблоки из таблицы, размещенную в диапазоне A35:B38
Ссылочная форма
Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.
получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).
Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)
Аналогичный результат можно получить используя функцию СМЕЩ()
Теперь более сложный пример, с областями.
Пусть имеется таблица продаж нескольких товаров по полугодиям.
Функция индекс в excel – примеры использования
Microsoft Office Excel имеет в своем арсенале формулу, которая позволяет определять координаты ячейки и возвращать ее значение в любую точку на рабочем листе. Такая опция полезна при работе с объемными базами данных, содержащих большое количество информации. Сегодня разберемся, как работает функция индекс в excel.
Описание
Чтобы детально разобрать принцип работы инструмента, необходимо создать массив данных, например количество потребляемых калорий за день в течение недели:
Необходимо узнать, сколько калорий должен содержать ланч в пятницу. Порядок использования функции ИНДЕКС следующий:
где B2:E8 — диапазон значений, внутри которого необходимо найти требуемую позицию. Два последующих числа обозначают номер строки и столбца соответственно.
Важно! Начало отсчета номеров осуществляется в рамках выделенного диапазона, при этом заголовки сверху и слева не учитываются.
Данная пошаговая инструкция подойдет как для чайников, так и для более уверенных пользователей редактора. Использование функции ИНДЕКС не вызывает особых затруднений.
Примеры
Рассмотрим примеры с несколькими условиями. Например, необходимо найти количество продаж стульев в третьем магазине. Исходные данные выглядят следующим образом:
Запишем формулу, при этом блок диапазонов записывается через точку с запятой и заключается в круглые скобки. Далее указываете номера строки, столбца и диапазона для поиска.
На заметку! Для каждого отдельного массива нумерация начинается с единицы, без учета заголовков.
Чтобы понять к чему относится полученный результат, в отдельной ячейке снова запишем ИНДЕКС, но уже с применением функции поискпоз, которая возвращает числовое значение позиции в массиве данных.
Как видите, использования инструмента ИНДЕКС не вызывает особых сложностей. Однако в сочетании с другими формулами возможности функции расширяются, что позволяет решать более трудные задачи.
Жми «Нравится» и получай только лучшие посты в Facebook ↓
Функция ИНДЕКС в Excel и примеры ее работы с массивами данных
Функция ИНДЕКС предназначена для выборки значений из таблиц Excel по их координатам. Ее особенно удобно использовать при работе с базами данных. Данная функция имеет несколько аналогов, такие как: ПОИСКПОЗ, ВПР, ГПР, ПРОСМОТР, с которыми может прекрасно сочетаться в сложных формулах. Но все же гибкость и простота в этой функции на первом месте.
Как работает функция ИНДЕКС в Excel?
Допустим мы работаем с большой таблицей данных с множеством строк и столбцов. Первая строка данной таблицы содержит заголовки столбцов. А в первом столбце соответственно находиться заголовки строк. Пример такой таблицы изображен ниже на рисунке:
Задача следующая: необходимо определить какое числовое значение относится к конкретному отделу и к конкретной статьи. Другими словами, необходимо получить значение ячейки на пересечении определенного столбца и строки.
Функция ИНДЕКС в Excel пошаговая инструкция
В результате получаем значение на пересечении столбца 3 и строки 7:
Как видно значение 40 имеет координаты Отдел №3 и Статья №7. При этом функцией ИНДЕКС не учитываются номера строк листа Excel, а только строки и столбцы таблицы в диапазоне B2:G10.
Описание примера как работает функция ИНДЕКС
В первом аргументе функции указывается диапазон ячеек таблицы, по которому будет выполнен поиск значений на пересечении строк и столбцов. Во втором аргументе сначала указываем номер строки, а потом в третьем аргументе – номер столбца. На основе этой информации функция ищет соответствующее значение в таблице.
Внимание! Для функции ИНДЕКС номера строк и столбцов определяют высоту и ширину таблицы, указанной в ее первом аргументе. Они никак не связаны с номерами рабочего листа Excel и не обязаны соответствовать им.
Функция ИНДЕКС в Excel. Как работать с функцией ИНДЕКС в Excel, и чем она может быть полезна
Табличный процессор имеет обширное количество функций, которые позволяют пользователю проводить различные виды обработки информации. Функция ИНДЕКС помогает реализовывать поиск значений в обозначенной локации заданного диапазона, а затем осуществляет вывод результата в выделенном секторе. В статье будет подробно рассмотрено, как применять функцию ИНДЕКС разнообразными методами.
Описание функции ИНДЕКС
ИНДЕКС – интегрированная в табличный процессор функция, которая позволяет получить информацию из таблицы при том условии, что пользователь знает номер строчки и столбика, в котором располагается эта информация.
Что возвращает функция
Данная функция осуществляет возвращение значений из определенной строчки и столбика таблицы.
Синтаксис
Существует четыре вариации синтаксиса этой функции. Две русские версии:
Две английские версии:
Аргументы функции
Разберемся более подробно, что означает каждый из приведенных ниже аргументов:
Дополнительная информация
Рассмотрим некоторые особенности функции, которые необходимо знать при ее использовании:
Как работает функция ИНДЕКС в Excel?
Рассмотрим процесс работы функции ИНДЕКС с различными типами данных в табличном процессоре.
Функция ИНДЕКС в Excel пошаговая инструкция
Пошаговая инструкция для работы с функцией ИНДЕКС имеет различный вид в зависимости от того, для чего она применяется. Ее можно использовать для работы с массивами, ссылками и другими вспомогательными операторами.
Функция ИНДЕКС для массивов
К примеру, есть табличка с названиями продукции, ее стоимостью, числом и конечной суммой.
Цель: в выделенном секторе показать название пятой в списке позиции. Пошаговая инструкция выглядит так:
Обратите внимание! Один из аргументов можно не заполнять в случае, если массив является одномерным.
Такие действия выглядят следующим образом:
Функция ИНДЕКС для ссылок
Функция ИНДЕКС может осуществлять свою работу сразу с некоторым количеством таблиц. Для реализации этого действия необходим перечень значений для ссылок с полем «Номер_области». К примеру, мы имеем четыре таблички. В них располагаются данные по продажам за различный промежуток времени.
10
Цель: выявить количество продаж в 4-й позиции за 2-й квартал в штуках. Пошаговое руководство выглядит следующим образом:
Использование с оператором СУММ
Функцию ИНДЕКС часто применяют совместно с оператором СУММ. Общий вид оператора: =СУММ(Адрес_массива). Применив СУММ, к рассматриваемой нами табличке, мы сможем получить итоговую сумму. Формула для подсчета суммы будет выглядеть следующим образом: =СУММ(D2:D9).
17
Можно немного отредактировать формулу, встроив в нее функцию ИНДЕКС. Пошаговое руководство выглядит следующим образом:
Сочетание с функцией ПОИСКПОЗ
Переходим к разбору более сложных задач. Ниже будет рассмотрен пример использования функции ИНДЕКС с оператором ПОИСКПОЗ. ПОИСКПОЗ позволяет осуществить возврат указанного показателя в выделенном диапазоне секторов. Общий вид формулы: =ПОИСКПОЗ(Искомое_значение,Просматриваемый_массив,[Тип_сопоставления]). Разберем каждый показатель функции более подробно:
Для наглядности использования двух функций разберем все на конкретных примерах. Возьмем ту же таблицу, которую мы рассматривали в предыдущих примерах. Около нее располагается маленькая табличка, в которой находится по одному пустому значению для названия и цены. Цель: применяя ПОИСКПОЗ и ИНДЕКС, реализовать добавление в сектор G2 функции, выводящей определенное значение в зависимости от указанного в секторе названия. Пошаговое руководство выглядит так:
Обработка нескольких таблиц
Рассмотри процесс обработки нескольких таблиц. К примеру, у нас есть 3 таблички. В них отображается зарплата сотрудников по месяцам. Цель: выявить зарплату второго сотрудника за 3-й месяц. Пошаговое руководство выглядит следующим образом:
Примеры использования функции ИНДЕКС в Excel
Дополнительно разберем еще один пример использования. Например, у нас есть следующая табличная информация:
35
Ошибки
Функция ИНДЕКС выводит ошибку, если один из аргументов выходит за границы диапазона.
Ошибка возникает, если табличный процессор не понимает, какой сектор необходимо вернуть.
Заключение
Фунция ИНДЕКС – эффективный оператор в табличном процессоре Эксель, позволяющий реализовывать огромный перечень разнообразных действий. Изучив работу с этим оператором, можно значительно ускорить процесс работы с большими объемами информации.