5 вариантов использования функции ИНДЕКС (INDEX)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ЗНАЧЕН в Excel для преобразования текста в число

Функция ЗНАЧЕН в Excel делает операцию преобразования строчки в числовое значение в тех вариантах, где это может быть. Данная функция возвращает число в случае успешно выполненного процесса преобразования либо код ошибки #ЗНАЧ!, если преобразование текста в число нереально.

Примеры функции ЗНАЧЕН в Excel и индивидуальности ее использования

Данная функция имеет последующую синтаксическую запись:

Интересно почитать: Функции в excel впр

Единственный аргумент — текст является неотклонимым для наполнения. Другими словами, данная функция воспринимает на вход текстовые данные, которые могут быть представлены в виде текстовой строчки, введенной в кавычках (к примеру, ЗНАЧЕН(“9 300 ₽”) либо ссылки на ячейку таблицы, в какой содержится текст.

Формат текста, принимаемого в качестве параметра функции ЗНАЧЕН, должен быть одним из предусмотренных в Excel (время, дата, число, валютный). Результатом выполнения функции будет являться ошибка #ЗНАЧ!, если начальный текст не соответствует одному из этих типов данных.
На практике функция ЗНАЧЕН употребляется достаточно изредка, так как в Excel реализован механизм автоматического преобразования значений подобно неким языкам программирования, в каких находится неявное преобразование типов данных. Эта функция реализована с целью сопоставимости с иными программными продуктами, поддерживающими работу с таблицами.
Функция полезна в вариантах, когда числовые значения внесены в ячейки с установленным текстовым форматом данных. Также в Excel можно применять надстройку, выполняющую поиск чисел, отформатированных в качестве текста и следующее преобразование их в числовой формат.
Почаще всего функция ЗНАЧЕН употребляется для преобразования результатов работы функций ПСТР и ЛЕВСИМВ (ПРАВСИМВ), но для корректного отображения к результатам вычислений данных функций можно просто прибавить 0 (нуль). Подробнее разглядим это в одном из примеров.

Примеры использования функции ЗНАЧЕН в Excel

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

Часть таблицы смотрится последующим образом:

Для расчета полного количества представленных 5 наименований продукции используем последующую формулу:

Функция СУММ воспринимает числовые значения и производит их суммирование. В данном примере наименование и количество единиц неких продуктов записаны в одной строке. Функция ПРАВСИМВ «отрезает» часть строчки не считая 2-ух крайних знаков, отображающих числовое значение – количество продуктов. При помощи функции ЗНАЧЕН, принимающей в качестве параметра итог работы функции ПРАВСИМ, мы производим прямое преобразование выделенных знаков в числовое значение.

В итоге получим последующее:

Другими словами, в данной части таблицы представлены 265 единиц продуктов.

Как конвертировать валютный формат в числовое значение

Пример 2. Данные о зарплатах служащих некого компании представлены в таблице Excel. Предприятие получило программный продукт, база данных которого не поддерживает валютный формат данных (лишь числовой). Было принято решение сделать отдельную таблицу, в какой вся информация о заработной плате представлена в виде числовых значений.

Вначале таблица смотрится последующим образом:

Для конвертирования валютных данных в числовые была применена функция ЗНАЧЕН. Расчет производится для всякого сотрудника по-отдельности. Приведем пример использования для сотрудника с фамилией Иванов:

Аргументом функции является поле валютного формата, содержащее информацию о зарплате Иванова. Аналогично делается расчет для других служащих. В итоге получим:

Функция ЗНАЧЕН и формат времени

Пример 3. Самолет вылетел согласно расписанию в 13:40 и был должен совершить высадку в конечном пт перелета в 17:20. Во время полета произошел инцидент, в связи которым рейс был задержан на определенное время. Самолет приземлился в 19:13. Нужно найти время задержки в часах и минутках. Данные о времени вылета, расчетного и фактического времени прибытия занесены в ячейки таблицы формата «Дата».

Таблица имеет последующий вид:

Для решения задачки запишем последующую формулу:

Примечание: функция ЗНАЧЕН применена для очевидного преобразования дат в числа в коде времени Excel. Практически, записи «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» являются эквивалентными, так как Excel делает неявное преобразование. Прямое преобразование имеет практический смысл при использовании приобретенных значений в остальных программных продуктах, не поддерживающих форматы даты и времени Excel.

В итоге будет получено число в коде времени Excel:

Для получения искомого значения нужно конвертировать формат данных ячейки в «Время»:

Функция INDEX (ИНДЕКС) в Excel. Как применять?

Функция INDEX (ИНДЕКС) в Excel употребляется для получения данных из таблицы, при условии что вы понимаете номер строчки и столбца, в каком эти данные находятся.

Интересно почитать: Функция транспонирования в excel

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

Что возвращает функция

Возвращает данные из определенной строчки и столбца табличных данных.

Синтаксис

=INDEX (array, row_num, [col_num]) – британская версия

=INDEX (array, row_num, [col_num], [area_num]) – британская версия

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

=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) – российская версия

Аргументы функции

array (массив) – спектр ячеек либо массив данных для поиска;
row_num (номер_строки) – номер строчки, в какой находятся разыскиваемые данные;
[col_num] ([номер_столбца])(необязательный аргумент) – номер колонки, в какой находятся разыскиваемые данные. Этот аргумент необязательный. Но если в аргументах функции не указаны аспекты для row_num (номер_строки), нужно указать аргумент col_num (номер_столбца);
[area_num] ([номер_области]) – (необязательный аргумент) – если аргумент массива состоит из нескольких диапазонов, то это число будет употребляться для выбора всех диапазонов.

Доборная информация

Если номер строчки либо колонки равен “0”, то функция возвращает данные всей строчки либо колонки;
Если функция употребляется перед ссылкой на ячейку (к примеру, A1), она возвращает ссылку на ячейку заместо значения (см. примеры ниже);
Почаще всего INDEX (ИНДЕКС) употребляется вместе с функцией MATCH (ПОИСКПОЗ);
В отличие от функции VLOOKUP (ВПР), функция INDEX (ИНДЕКС) может возвращать данные как справа от искомого значения, так и слева;
Функция употребляется в 2-ух формах – Массива данных и Формы ссылки на данные:

– Форма “Массива” употребляется когда вы желаете отыскать значения, основанные на определенных номерах строк и столбцов таблицы;

– Форма “Ссылок на данные” употребляется при поиске значений в нескольких таблицах (используете аргумент [area_num] ([номер_области]) для выбора таблицы и лишь позже сориентируете функцию по номеру строчки и столбца.

Примеры использования функции ИНДЕКС в Excel

Пример 1. Отыскиваем результаты экзамена по физике для Алексея

Представим, у вас есть результаты экзаменов в табличном виде по нескольким студентам:

Для того, чтоб отыскать результаты экзамена по физике для Андрея нам нужна формула:

=INDEX($B$3:$E$9,3,2) – британская версия

=ИНДЕКС($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 (ПОИСКПОЗ) и выпадающего перечня

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

Интересно почитать: Функция vlookup в excel

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

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

Для того, чтоб выполнить динамическую подстановку данных с внедрением функций 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, мы на месте ввода имени и предмета сделаем выпадающие списки:

Избираем ячейку, в какой мы желаем показать выпадающий перечень с именами студентов;
Кликаем на вкладку “Data” => Data Tools => Data Validation;
В окне Data Validation на вкладке “Settings” в подразделе Allow избираем “List”;
В качестве Source нам необходимо избрать спектр ячеек, в каком указаны имена студентов;
Кликаем ОК

Сейчас у вас есть выпадающий перечень с именами студентов в ячейке G5. Таковым же образом вы сможете сделать выпадающий перечень с предметами.

Пример 4. Внедрение трехстороннего поиска при помощи INDEX (ИНДЕКС) / MATCH (ПОИСКПОЗ)

Функция INDEX (ИНДЕКС) быть может применена для обработки трехсторонних запросов.

Что такое трехсторонний поиск?

В приведенных выше примерах мы употребляли одну таблицу с оценками для студентов по различным предметам. Это пример двунаправленного поиска, так как мы используем две переменные для получения оценки (имя студента и предмет).

Сейчас представим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Итоговый экзамен».

Трехсторонний поиск – это возможность получить отметки студента по данному предмету с обозначенным уровнем экзамена.

Вот вам наглядный пример трехстороннего поиска:

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

Для таковых расчетов нам поможет формула:

=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, которая имеет наиболее 1-го синтаксиса.

=INDEX (array, row_num, [col_num]) – британская версия

=INDEX (array, row_num, [col_num], [area_num]) – британская версия

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

=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) – российская версия

По всем вышеприведенным примерам мы употребляли 1-ый синтаксис, но для трехстороннего поиска нам необходимо применять 2-ой синтаксис.

Разглядим каждую часть формулы на базе второго синтаксиса.

array(массив) – ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23):Заместо использования 1-го массива, в этом случае мы употребляли три массива в круглых скобках.
row_num (номер_строки) – MATCH($G$4,$A$3:$A$7,0): функция MATCH (ПОИСКПОЗ) употребляется для поиска имени студента для ячейки $G$4 из перечня всех студентов.
col_num (номер_столбца) – MATCH($H$3,$B$2:$E$2,0): функция MATCH (ПОИСКПОЗ) употребляется для поиска наименования предмета для ячейки $H$3 из перечня всех предметов.
[area_num] ([номер_области]) – IF($H$2=”Вступительный”,1,IF($H$2=”Полугодовой”,2,3)): Значение номера области докладывает функции INDEX (ИНДЕКС) , какой массив с данными избрать. В этом примере у нас есть три массива в первом аргументе. Если вы выберете “Вступительный” из раскрывающегося меню, функция IF (ЕСЛИ) возвратит значение “1”, а функция INDEX (ИНДЕКС) изберут 1-й массив из 3-х массивов ($B$3:$E$7).

Уверен, что сейчас вы тщательно исследовали работу функции INDEX (ИНДЕКС) в Excel!

Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!