Как создать подстрочный индекс в Excel? | (Жгучая кнопка для подстрочного индекса)

Как создать подстрочный индекс в Excel? | (Жгучая кнопка для подстрочного индекса)

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

Ярлычек для подстрочного индекса в Excel

Ярлычек подстрочного индекса Excel «Ctrl + 1, Alt + B и Enter» употребляется для форматирования выражений, таковых как «CH4», в вашей электрической таблице Excel. Это просит использования индексов для преобразования его в верный формат «CH 4 ».

Как употреблять этот ярлычек подстрочного индекса Excel? (3 обычных шага)

Будьте аккуратны, чтоб не жать клавиши сходу, каждую комбинацию кнопок следует жать и отпускать по очереди:

  • Шаг 1: Изберите знак либо несколько знаков, которые вы желаете отформатировать.
  • Шаг 2. Чтоб открыть диалоговое окно «Формат ячеек», нажмите Ctrl + 1.
  • Шаг 3: Потом нажмите Alt + B, чтоб избрать опцию Subscript, и нажмите кнопку Enter, чтоб применить форматирование и закрыть диалоговое окно.

Примеры

Давайте разберемся с работой Subscript на примерах ниже. Подстрочный индекс весьма прост и может употребляться в листе Excel для хоть какого знака / числа и может употребляться в VBA.

Вы сможете скачать этот шаблон Excel с индексами тут — Шаблон Excel с индексами

Пример # 1

Вы сможете применить нижний индекс, используя два нижеприведенных способа в хоть какой ячейке листа Excel.

Метод 1:
  • Шаг 1. Два раза щелкните ячейку A2 и изберите значение «2».

  • Шаг 2. Потом щелкните правой клавишей мыши и изберите Формат ячеек.

  • Шаг 3. Раскроется диалоговое окно «Формат ячеек».

  • Шаг 4. На вкладке «Шрифт» под флагом «Эффект» в нижнем индексе нажмите «ОК».

  • Шаг 5: Потом он конвертирует H2O в H 2 O

Метод 2:

2-ой метод — избрать ячейку и работать прямо в строке формул.

  • Шаг 1. По-прежнему, пристально изберите текст, который желаете отформатировать. Потом используйте «Форматировать ячейки», чтоб применить подстрочный индекс по собственному усмотрению.

  • Шаг 2: Вы сможете отменить либо удалить форматирование подстрочного индекса, выбрав ячейку, нажав «Форматировать ячейки» и сняв соответственный флаг.

Пример # 2

В этом примере мы применяем индекс к каналу CH4.

Сейчас мы используем 2-ой способ, просто изберите «4» в строке формул, потом щелкните правой клавишей мыши и изберите Формат ячеек.

(Покажется диалоговое окно «Форматирование ячеек»), потом установите флаг «Подстрочный индекс».

Сейчас на выходе будет СН 4.

Пример # 3

Нижний индекс также употребляется для различения разных типов субатомных частиц. Таковым образом, электрические, мюонные и тау-нейтрино обозначаются ν e, ν μ и ν τ.

Давайте преобразуем ve, Vu и vτ в νe, νμ и ντ в excel.

  • Откройте диалоговое окно «Формат ячеек», нажав Ctrl + 1, либо щелкните выделенный фрагмент правой клавишей мыши и изберите «Формат ячеек».

  • В диалоговом окне «Формат ячеек» перейдите на вкладку «Шрифт» и изберите «Подстрочный индекс» в разделе «Эффекты».

  • Нажмите ОК, чтоб сохранить конфигурации и закрыть диалоговое окно.

Пример # 4

Вы также сможете конвертировать весь текст ячейки в подстрочный индекс.

Просто изберите ячейку и нажмите ctrl + 1, а потом изберите нижний индекс в разделе «Эффекты».

Как употреблять сочетание кнопок для преобразования текста выделенной ячейки в подстрочный индекс сходу.

  • Нажмите Ctrl + Shift + F, чтоб открыть ячейку формата

  • Нажмите ALT + B, он автоматом изберет опцию нижнего индекса в разделе «Эффекты» и нажмите «Ввод», опосля что текст для избранной ячейки будет автоматом преобразован в нижний индекс.

Подстрочный индекс с внедрением кода VBA

Представим, у нас есть текст в Sheet1 и в ячейке D2, потом примените индекс, используя приведенный ниже код VBA.

ИНДЕКС Функция Excel

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

Функция ИНДЕКС в Excel относится к группы «Формула поиска и ссылки».

Индекс Функция

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

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

Функция ИНДЕКС может употребляться в 2-ух различных целях:

1) найдите значение, которое находится на пересечении строчки и столбца.

2) найдите определенную таблицу, а потом в данной для нас определенной таблице найдите значение ячейки, которое находится при объединении строчки и столбца.

Формула ИНДЕКС в Excel

  • Форма массива

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

Характеристики формулы ИНДЕКС в Excel

  • Массив: массив определяется как определенный спектр ячеек
  • row_num: обозначает позицию строчки в обозначенном массиве.
  • [column_num]: Обозначает позицию столбца в обозначенном массиве.

Примечание. Неотклонимым является хоть какой номер строчки / столбца; это даст # ЗНАЧЕНИЕ! Ошибка, если оба значения пусты / ноль.

Как употреблять функцию ИНДЕКС в Excel

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

Пример # 1

ИНДЕКС Пример функции 1

Итог:

В приведенном выше примере функция Index имеет лишь один спектр и возвращает позицию в строке 5 спектра C3: C7, которая является ячейкой C7. Имеет значение 4

Пример # 2

ИНДЕКС Пример функции 2

Итог:

Пример функции ИНДЕКС 2-1

В приведенном выше примере Index возвратит ссылку на ячейку номер 4 и строчку 3 из спектра B3: F7, который является ячейкой E5. Это значение 629

Пример функции ИНДЕКС 2-2

Пример возвратит #VALUE, если обе строчки №, столбец № равны нулю.

  • Справочная форма

= ИНДЕКС (ссылка; номер_строки; [column_num], [area_num])

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

  1. Массив: массив определяется как определенный спектр ячеек / спектр. В случае нескольких диапазонов отдельные области делятся запятыми и запираются скобками — к примеру. (A1: C2, C4: D7).
  2. row_num: обозначает позицию строчки в обозначенном массиве.
  3. [column_num]: Обозначает позицию столбца в обозначенном массиве.
  4. Area_num: Номер области выбирает спектр в ссылке, из которого ворачивается пересечение Column_num и Row_num.
Интересно почитать:  Подбор параметра в excel функция

Заметка: Если поле Area_num оставлено пустым, функция INDEX в Excel по дефлоту употребляет область 1.

Функция индекса возвращает #VALUE! Ошибка, если область, обозначенная в формуле ИНДЕКС в Excel, находится на любом другом листе. Области, обозначенные в INDEX Formula excel, должны быть размещены на одном листе.

К примеру:

Пример 3

Пример # 3

Пример 3-1

В приведенном выше примере у нас есть 3 различных спектра ячеек; как следует, массив для вышеупомянутого будет упоминаться как (B3: E7, D10: F12, C15: E18)

Итог:

Пример 3-2

В приведенном выше примере функция Index возвращает ссылку на столбец номер 4 и строчку номер 3 2-ой области , которая относится к ячейке E11.

Функция ИНДЕКС в программке Microsoft Excel

Функция ИНДЕКС в Microsoft Excel

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

Внедрение функции ИНДЕКС

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

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

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

Синтаксис для ссылочного варианта смотрится так:

Здесь буквально так же можно употреблять лишь один аргумент из 2-ух: «Номер строчки» либо «Номер столбца». Аргумент «Номер области» совершенно является необязательным и он применяется лишь тогда, когда в операции участвуют несколько диапазонов.

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

Метод 1: внедрение оператора ИНДЕКС для массивов

Давайте, до этого всего, разберем на простом примере метод использования оператора ИНДЕКС для массивов.

Имеем таблицу зарплат. В первом её столбце отображены фамилии работников, во 2-м – дата выплаты, а в 3-ем – величина суммы заработка. Нам необходимо вывести имя работника в третьей строке.

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

Переход в Мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

В поле «Массив» необходимо указать адресок обрабатываемого спектра данных. Его можно вбить вручную. Но для облегчения задачки мы поступим по другому. Ставим курсор в соответственное поле, а потом обводим весь спектр табличных данных на листе. Опосля этого адресок спектра здесь же отобразится в поле.

В поле «Номер строчки» ставим цифру «3», потому что по условию нам необходимо найти третье имя в перечне. В поле «Номер столбца» устанавливаем число «1», потому что колонка с именами является первой в выделенном спектре.

Окно аргументов функции ИНДЕКС в Microsoft Excel

Результат обработки функции ИНДЕКС в Microsoft Excel

Мы разобрали применение функции ИНДЕКС в многомерном массиве (несколько столбцов и строк). Если б спектр был одномерным, то наполнение данных в окне аргументов было бы ещё проще. В поле «Массив» этим же способом, что и выше, мы указываем его адресок. В этом случае спектр данных состоит лишь из значений в одной колонке «Имя». В поле «Номер строчки» указываем значение «3», потому что необходимо выяснить данные из третьей строчки. Поле «Номер столбца» совершенно можно бросить пустым, потому что у нас одномерный спектр, в каком употребляется лишь один столбец. Нажимаем на клавишу «OK».

Итог будет буквально таковой же, что и выше.

Результат обработки функции ИНДЕКС для одномерного массива в Microsoft Excel

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

Метод 2: применение в комплексе с оператором ПОИСКПОЗ

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

Главный задачей функции ПОИСКПОЗ является указание номера по порядку определенного значения в выделенном спектре.

Синтаксис оператора ПОИСКПОЗ таковой:

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

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

При помощи этого инструмента можно заавтоматизировать введение аргументов «Номер строчки» и «Номер столбца» в функцию ИНДЕКС.

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

    До этого всего, узнаем, какую зарплату получает работник Парфенов Д. Ф. Вписываем его имя в соответственное поле.

Имя вписано в поле в Microsoft Excel

В поле «Массив» вносим координаты столбца, в каком находятся суммы заработных плат работников.

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

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

Окно аргументов функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

Изменение значений при использовании функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

Метод 3: обработка нескольких таблиц

Сейчас поглядим, как при помощи оператора ИНДЕКС можно обработать несколько таблиц. Для этих целей будет применяться доп аргумент «Номер области».

Имеем три таблицы. В каждой таблице отображена зарплата работников за отдельный месяц. Нашей задачей является выяснить зарплату (3-ий столбец) второго работника (2-ая строчка) за 3-ий месяц (3-я область).

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

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

Выбор ссылочного вида функции ИНДЕКС в Microsoft Excel

В поле «Номер строчки» указываем цифру «2», потому что отыскиваем вторую фамилию в перечне.

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

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

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

Метод 4: вычисление суммы

Ссылочная форма не так нередко применяется, как форма массива, но её можно употреблять не только лишь при работе с несколькими спектрами, да и для остальных нужд. К примеру, её можно использовать для расчета суммы в композиции с оператором СУММ.

При сложении суммы СУММ имеет последующий синтаксис:

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

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

В этом случае в координатах начала массива указывается ячейка, с которой он начинается. А вот в координатах указания окончания массива употребляется оператор ИНДЕКС. В этом случае 1-ый аргумент оператора ИНДЕКС показывает на спектр, а 2-ой – на последнюю его ячейку – шестую.

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

Мы рады, что смогли посодействовать Для вас в решении задачи.

Кроме данной для нас статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Функция ИНДЕКС в Excel — Примеры формул + БЕСПЛАТНОЕ видео

Функция ИНДЕКС в Excel - Примеры формул + БЕСПЛАТНОЕ видео

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

Что это возвращает

Он возвращает значение из таблицы для обозначенного номера строчки и номера столбца.

Синтаксис

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

Функция ИНДЕКС имеет 2 синтаксиса. 1-ый употребляется почти всегда, но в случае трехстороннего поиска употребляется 2-ой (описанный в примере 5).

Входные аргументы

  • огромное количество — а спектр ячеек либо константа массива.
  • row_num — номер строчки, из которой обязано быть извлечено значение.
  • [col_num] — номер столбца, из которого обязано быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его нужно указать.
  • [area_num] — (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет употребляться для выбора ссылки из всех диапазонов.

Доп примечания (кислый материал… но принципиально знать)

  • Если номер строчки либо столбца равен 0, он возвращает значения всей строчки либо столбца соответственно.
  • Если функция ИНДЕКС употребляется перед ссылкой на ячейку (к примеру, A1 :), она возвращает ссылку на ячейку заместо значения (см. Примеры ниже).
  • Более обширно употребляется вкупе с функцией ПОИСКПОЗ.
  • В отличие от ВПР, функция ИНДЕКС может возвращать значение слева от значения поиска.
  • Функция ИНДЕКС имеет две формы — форма массива и справочная форма.
    • «Форма массива» — это пространство, где вы выбираете значение на базе номера строчки и столбца из данной таблицы.
    • «Справочная форма» — это когда есть несколько таблиц, и вы используете аргумент area_num, чтоб избрать таблицу, а потом получить значение в ней, используя номер строчки и столбца (см. Жив пример ниже).

    Функция ИНДЕКС в Excel — Примеры

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

    Пример 1. Поиск оценок Тома по физике (двухсторонний поиск)

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

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

    Эта формула ИНДЕКС описывает массив как $ B $ 3: $ E $ 10, в каком есть оценки для всех предметов. Потом он употребляет номер строчки (3) и номер столбца (2) для получения оценок Тома по физике.

    Пример 2 — Создание динамического значения LOOKUP при помощи функции MATCH

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

    Что-то вроде того, что показано ниже:

    Это можно создать при помощи композиции ИНДЕКС и функции ПОИСКПОЗ.

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

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

    • Динамический номер строчки задается последующей частью формулы — ПОИСКПОЗ ($ G $ 5, $ A $ 3: $ A $ 10,0). Он сканирует имена студентов и описывает значение поиска (в этом случае $ G $ 5). Потом он возвращает номер строчки искомого значения в наборе данных. К примеру, если значение поиска — Мэтт, оно возвратит 1, если это Боб, оно возвратит 2 и так дальше.
    • Номер динамического столбца задается последующей частью формулы — ПОИСКПОЗ ($ H $ 4, $ B $ 2: $ E $ 2,0). Он сканирует имена субъектов и описывает значение поиска (в этом случае $ H $ 4). Потом он возвращает номер столбца искомого значения в наборе данных. К примеру, если значение поиска Math, оно возвратит 1, если это Physics, оно возвратит 2 и так дальше.

    Пример 3 — Внедрение раскрывающихся списков в качестве значений поиска

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

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

    Что-то вроде того, что показано ниже:

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

    Как это создать:

    Формула, использованная в этом случае, таковая же, как в Примере 2.

    Значения поиска преобразованы в раскрывающиеся списки.

    Вот шаги для сотворения раскрывающегося перечня Excel:

    • Изберите ячейку, в какой вы желаете открыть раскрывающийся перечень. В этом примере в G4 нам необходимы имена учеников.
    • Перейдите в Data -> Data Tools -> Data Validation.
    • В диалоговом окне «Проверка данных» на вкладке опций изберите «Перечень» в раскрывающемся перечне «Разрешить».
    • В источнике изберите $ A $ 3: $ A $ 10
    • Щелкните ОК.

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

    Пример 4 — Возвращаемые значения из всей строчки / столбца

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

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

    Говоря обычным языком, вы желаете поначалу получить всю строчку баллов для ученика (скажем, Боба), а потом в рамках этих значений найти наивысший балл либо общую сумму всех баллов.

    В функции ИНДЕКС Excel при вводе номер столбца как 0, он возвратит значения всей данной для нас строчки.

    Итак, формула для этого будет:

    Сейчас это формула. если употреблять как есть, возвратит #VALUE! ошибка. В то время как он показывает ошибку, в бэкэнде он возвращает массив, в каком все есть оценки для Тома — <57,77,91,91>.

    Если вы выберете формулу в режиме редактирования и нажмете F9, вы можете узреть массив, который она возвращает (как показано ниже):

    Буквально так же, зависимо от того, что такое значение поиска, когда номер столбца указан как 0 (либо оставлен пустым), он возвращает все значения в строке для значения поиска.

    Сейчас, чтоб подсчитать общий балл, приобретенный Томом, мы можем просто употреблять приведенную выше формулу в функции СУММ.

    Аналогично, для вычисления наивысшего балла мы можем употреблять MAX / LARGE, а для вычисления минимума — MIN / SMALL.

    Пример 5 — Трехсторонний поиск с внедрением INDEX / MATCH

    Функция ИНДЕКС Excel создана для обработки трехстороннего поиска.

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

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

    Сейчас представим, что за год у студента есть экзамены 3-х различных уровней: модульный тест, промежный экзамен и итоговый экзамен (это то, что я сдавал, когда был студентом).

    Трехсторонний поиск — это возможность получить оценки учащегося по обозначенному предмету на данном уровне экзамена. Это сделало бы поиск трехсторонним, так как есть три переменных (имя студента, имя предмета и уровень экзамена).

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

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

    Вот формула, применяемая в ячейке H4:

    Давайте разберемся с данной для нас формулой, чтоб осознать, как она работает.

    Эта формула воспринимает четыре аргумента. ИНДЕКС — одна из тех функций в Excel, которые имеют наиболее 1-го синтаксиса.

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

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

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

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

    Пример 6 — Создание ссылки при помощи функции ИНДЕКС (динамические именованные спектры)

    Это одно из сумасшедших применений функции ИНДЕКС в Excel.

    Возьмем обычный пример.

    У меня есть перечень имен, как показано ниже:

    Сейчас я могу употреблять ординарную функцию ИНДЕКС, чтоб получить фамилию в перечне.

    = ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

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

    Итак, что тут происходит с мистикой.

    Если вы расположите формулу перед ссылкой на ячейку, формула возвратит ссылку на ячейку с совпадающим значением (заместо самого значения).

    Вы ждете, что приведенная выше формула возвратит = A2: «Джош» (где Джош — крайнее значение в перечне). Но он возвращает = A2: A9 и, как следует, вы получаете массив имен, как показано ниже:

    Одним из практических примеров, где быть может полезна эта техника, является создание динамических именованных диапазонов.

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

    Примечание. Я изо всех сил старался проверить это управление, но если вы обнаружите какие-либо ошибки либо орфографические ошибки, сообщите мне о этом 🙂

    Функция ИНДЕКС в Excel — видеоурок

    • Функция ВПР в Excel.
    • Функция Excel HLOOKUP.
    • Функция НЕПРЯМАЯ в Excel.
    • Функция ПОИСКПОЗ в Excel.
    • Функция смещения Excel.

    Для вас также могут приглянуться последующие управления по Excel:

Ссылка на основную публикацию
Adblock
detector