ИНДЕКС (функция ИНДЕКС)

ИНДЕКС (функция ИНДЕКС)

Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.

Функцию ИНДЕКС можно использовать двумя способами:

Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.

Если требуется возвращать ссылку на указанные ячейки, см. раздел Ссылочная форма.

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

Возвращает значение элемента в таблице или массиве, выбранном с помощью индексов строк и столбцов.

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

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

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

IsArray — обязательный аргумент. Диапазон ячеек или константа массива.

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

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

Номер_строки Обязательный, если не указан column_num. Выбирает строку в массиве, из которой требуется возвратить значение. Если row_num опущено, column_num является обязательным.

Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если column_num опущено, row_num является обязательным.

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

row_num и column_num должны указывать на ячейку в массиве; в противном случае функция индекс возвращает значение #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

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

Примечание: Если у вас установлена текущая версия Microsoft 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выделив диапазон вывода, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.

Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.

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

Примечание: Если у вас установлена текущая версия Microsoft 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае формула должна быть введена как устаревшая формула массива, если сначала выбрать две пустые ячейки, ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.

Значение ячейки на пересечении второй строки и второго столбца в массиве, указанном выше.

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

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

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

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

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

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

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

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

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

area_num — необязательный аргумент. Выделяет диапазон в ссылке, из которого нужно возвращать пересечение row_num и column_num. Первая выделенная или введенная область имеет номер 1, второй — 2 и т. д. Если area_num опущен, то функция индекс использует область 1. Все перечисленные здесь области должны располагаться на одном листе. Если вы указали области, которые не находятся на одном листе, это вызовет #VALUE! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. Если необходимо использовать диапазоны, расположенные на разных листах, рекомендуется использовать форму массива функции индекс и использовать другую функцию для вычисления диапазона, который составляет массив. Например, чтобы вычислить диапазон, который будет использоваться, можно использовать функцию выбор.

Например, если в ссылке описаны ячейки (a1: B4, D1: E4, G1: H4), то area_num 1 — диапазон a1: B4, area_num 2 — диапазон D1: E4, а area_num 3 — диапазон ячеек G1: H4.

После того как вы выберете ссылку и area_num выберете определенный диапазон, row_num и column_num Выбери определенную ячейку: row_num 1 — Первая строка диапазона, column_num 1 — первый столбец и т. д. Ссылка, возвращаемая функцией индекс, — это пересечение row_num и column_num.

Если вы задаете row_num или column_num 0 (ноль), функция индекс возвращает ссылку на весь столбец или строку соответственно.

row_num, column_num и area_num должны указывать на ячейку в ссылке; в противном случае функция индекс возвращает значение #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. Если опущены row_num и column_num, функция индекс возвращает область ссылки, указанную в area_num.

Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

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

Источник:
http://support.microsoft.com/ru-ru/office/%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81-a5dcf0dd-996d-40a4-a822-b56b061328bd

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

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

Использование функции ИНДЕКС

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

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

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

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

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

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

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

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

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

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

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

Открывается небольшое окошко, в котором нужно выбрать один из типов функции: «Массив» или «Ссылка». Нужный нам вариант «Массив». Он расположен первым и по умолчанию выделен. Поэтому нам остается просто нажать на кнопку «OK».

Открывается окно аргументов функции ИНДЕКС. Как выше говорилось, у неё имеется три аргумента, а соответственно и три поля для заполнения.

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

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

Читайте также  Строим график функции, заданный системой уравнений, в EXCEL

После того, как все указанные настройки совершены, щелкаем по кнопке «OK».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    После того, как все значения внесены, жмем на кнопку «OK».

    Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».

  • Теперь, если в поле «Имя» мы изменим содержимое с «Парфенов Д.Ф.», на, например, «Попова М. Д.», то автоматически изменится и значение заработной платы в поле «Сумма».
  • Способ 3: обработка нескольких таблиц

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

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

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

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

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

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

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

    После того, как все данные введены, щелкаем по кнопке «OK».

  • После этого в предварительно выделенную ячейку выводятся результаты вычисления. Там отображается сумма заработной платы второго по счету работника (Сафронова В. М.) за третий месяц.
  • Способ 4: вычисление суммы

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

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

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

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

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

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

    Источник:
    http://lumpics.ru/the-index-function-in-excel/

    Функция ИНДЕКС() в EXCEL

    29 декабря 2013 г.

    Синтаксис функции

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

    Массив — ссылка на диапазон ячеек.

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

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

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

    Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! Например, формула =ИНДЕКС(A2:A13;22) вернет ошибку, т.к. в диапазоне А2:А13 только 12 строк.

    Значение из заданной строки диапазона

    Пусть имеется одностолбцовый диапазон А6:А9.

    Выведем значение из 2-й строки диапазона, т.е. значение Груши . Это можно сделать с помощью формулы =ИНДЕКС(A6:A9;2)

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

    Значение из заданной строки и столбца таблицы

    Пусть имеется таблица в диапазоне А6:B9.

    Выведем значение, расположенное в 3-й строке и 2-м столбце таблицы, т.е. значение 200 . Это можно сделать с помощью формулы =ИНДЕКС(A6:B9;3;2)

    Использование функции в формулах массива

    Если задать для аргумента «номер_строки» или «номер_столбца» значение 0, функция ИНДЕКС() возвратит массив значений для целого столбца или, соответственно, целой строки (не всего столбца/строки листа, а только столбца/строки входящего в массив). Чтобы использовать массив значений, введите функцию ИНДЕКС() как формулу массива .

    Пусть имеется одностолбцовый диапазон А6:А9. Выведем 3 первых значения из этого диапазона, т.е. на А6 , А7 , А8 . Для этого выделите 3 ячейки ( А21 , А22 , А23 ), в Строку формул введите формулу =ИНДЕКС(A6:A9;0) , затем нажмите CTRL+SHIFT+ENTER .

    Зачем это нужно? Теперь удалить по отдельности значения из ячеек А21 , А22 , А23 не удастся, мы получим предупреждение «нельзя изменять часть массива».

    Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Затем нажмите CTRL+SHIFT+ENTER и получим тот же результат.

    Использование массива констант

    Вместо ссылки на диапазон можно использовать массив констант :

    ПОИСКПОЗ() + ИНДЕКС()

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

    Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает цену товара Яблоки из таблицы, размещенную в диапазоне A35:B38

    Связка ПОИСКПОЗ() + ИНДЕКС() даже гибче, чем функция ВПР() , т.к. с помощью ее можно, например, определить товар с заданной ценой (обратная задача, так называемый «левый ВПР()»). Формула =ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0)) определяет товар с ценой 200. Если товаров с такой ценой несколько, то будет выведен первый сверху.

    Ссылочная форма

    Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.

    Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, . 9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

    Читайте также  Как сделать среднее значение в excel график?

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

    Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

    Аналогичный результат можно получить используя функцию СМЕЩ()

    Теперь более сложный пример, с областями.

    Пусть имеется таблица продаж нескольких товаров по полугодиям.

    Задав Товар , год и полугодие , можно вывести соответствующий объем продаж с помощью формулы =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

    Вся таблица как бы разбита на 3 подтаблицы (области), соответствующие отдельным годам: B9:C12 ; D9:E12 ; F9:G12 . Задавая номер строки, столбца (в подтаблице) и номер области, можно вывести соответствующий объем продаж. В файле примера , выбранные строка и столбец выделены цветом с помощью Условного форматирования .

    Источник:
    http://excel2.ru/articles/funkciya-indeks-v-ms-excel-indeks

    Excel 2. Нумерация ячеек

    По окончании урока вы сможете:

    1. Определить виды курсоров при работе с таблицей
    2. Ввести список перетаскиванием курсора
    3. Ввести список прогрессией
    4. Ввести список формулой
    5. Сравнить три способа нумерации ячеек

    Вы знаете, что строчки в Excel’е нумеруются. И вы можете сказать: этого хватить. Да, но номера строчек не выводятся на печать, а в таблицах, выводимых на печать, номера бывают нужны. Откройте Excel – начинаем!

    1. Виды курсоров в Excel’е

    Для начала маленький ликбез, договариваемся на берегу, как и что мы будем называть:

    1. Моя любимая панель быстрого доступа (настраивается, импортируется и экспортируется аналогично уроку 18 , уроку 19 и секрету 5)
    2. Поле «Имя ячейки»
    3. Никогда не пользуюсь этим полем (а вообще это команды «Отмена», «Ввод» и «Вставить функцию»)
    4. Поле «Строка формул» (где прекрасно работается с клавиатуры, поэтому нет нужды в пункте 3
    5. Имя столбца (буквенное, но можно сделать и численное)
    6. Выделенная ячейка. Обратите внимание, что при выделении ячейки в правом нижнем углу зелёного прямоугольника отчётливо виден зелёный квадратик.

    1. Имя строки (только численное). Смотрим на поле «Имя ячейки» ‒ имя А1 на пересечении соответствующих строки и столбца
    2. Имя листа (можно переименовать, покрасить, переместить, удалить и т.д.)
    3. Кнопка создания ещё одно листа. Сколько раз щёлкните по плюсику, столько листов добавиться

    При работе с Excel’ем важно замечать изменения курсора – это сигнал о смене режима работы.

    1. Знакомый курсор ‒ стрелка. Активна только с командами на лентах
    2. Курсор изменения размера ячейки. На границе имени столбца или строчки позволяет увеличить или уменьшить размер ячейки (нажали ЛМ ‒ потянули)
    3. Курсор выбора ячейки или диапазона ячеек (нажали ЛМ ‒ потянули)
    4. Курсор заполнения ячеек (нажали ЛМ на зелёный квадратик ‒ потянули, а в некоторых случаях – 2 раза щёлкнули ЛМ по зелёному квадратику)
    5. Курсор перемещения ячейки или диапазона ячеек (нажали ЛМ ‒ переместили)

    А теперь подготовительные работы.

    Шаг 1. Открываем таблицу

    Шаг 2. В ячейке А1 набираем слово «Перетаскивание». Вот неожиданность ‒ слово вышло за пределы ячейки.

    1. Подводим курсор на строку имён столбцов между «А» и «В» – курсор превращается в двойную стрелку
    2. Нажимаем ЛМ претаскиваем курсор на нужное нам расстояние

    Шаг 3. Набираем в ячейку B1 – слово «Прогрессия», в ячейке С1 – слово «Формула». При необходимости изменяем ширину ячеек. Можно и не вручную изменять размер ячеек, но об этом как-нибудь потом. (Вы обратили внимание, что в Excel’е первые буквы предложения не становятся автоматически прописными, как в Word’е?)

    Получили такую картину:

    2. Нумерация ячеек перетаскиванием курсора. Самой простой способ введения списка

    Шаг 1. Щёлкаем ячейку А1 и вводим с клавиатуры «1» и Enter. Зелёный прямоугольник перескочил на ячейку вниз. Что поделаешь – так устроен Excel. Поэтому ещё раз щёлкаем по ячейке А2.

    Шаг 2. Подводим курсор к маленькому зелёному квадратику – курсор превращается в маленький чёрный крест

    Шаг 3. Нажимаем одновременно ЛМ+Ctrl (клавиша такая на клавиатуре)

    Как только мы это сделали, так сразу вверху справа от маленького чёрного крестика появился плюсик:

    Шаг 4. Не отпуская ЛМ+Ctrl, тянем мышь до 21 строчки:

    Я не удержалась и обратила ваше внимание на бледный значок, который появился внизу колонки чисел. Значок называется «Параметры автозамены». Если вы нажмёте на этот значок ЛМ (кстати, курсор превратился в знакомую белую стрелку), то вы увидите выпадающее меню:

    Сделайте отметочку в памяти: всё это может понадобится когда-нибудь.

    А теперь посмотрим на результат – мы получили пронумерованные ячейки.

    3. Нумерация ячеек прогрессией

    Шаг 1. Щёлкаем ячейку В2 и вводим с клавиатуры «1» и Enter. Зелёный прямоугольник перескочил на ячейку вниз. Что поделаешь – так устроен Excel. Поэтому ещё раз щёлкаем по ячейке В2.

    Шаг 2. Открываем диалоговое окно «Прогрессия» (лента Главная → группа команд Редактирование → выпадающее меню → команда Прогрессия → диалоговое окно Прогрессия):

    Шаг 3. Вводим свои «хотелки» в диалоговое окно:

    1. Расположение по столбцам ‒ мы хотим получить колонку чисел
    2. Тип прогрессии – арифметический. (Арифмети́ческая прогре́ссия ‒ последовательность чисел (членов прогрессии), в которой каждое число, начиная со второго, получается из предыдущего добавлением к нему постоянного числа (шага, или разности прогрессии)
    3. Шаг равен 1 (1, 1+1=2, 2+1=3 и так далее)
    4. Предельное значение равно 20. Если вы не поставите предельное значение, то прогрессия заполнит весь столбик до конца, а где этот конец – не знает никто
    5. Нажимаем ОК

    Получаем такую картину:

    4. Нумерация ячеек формулой

    Шаг 1. Выделили ячейку С2. А теперь внимательно – учимся вводить формулы для этого существуют несколько способов. У каждого из способов есть достоинства и недостатки. По мере дальнейшего знакомства с Excel будем учитывать особенности введения формул.

    Шаг 2. Набираем в ячейке с клавиатуры знак «=»:

    Видите – появился текстовый курсор.

    Шаг 3. Набираем с клавиатуры поочерёдно буквы «стр»:

    Проявляется немного непривычное меню. И потрясающая фраза: «Возвращает номер строки, определяемой ссылкой». Перевожу на русский язык: в ячейке появится номер соответствующей строки, в нашем случае «2», строка-то вторая.

    Шаг 4. Двойной щелчок ЛМ по слову «СТРОКА», а потом нажимаем Enter и любуемся результатом:

    Какую формулу задали, то и получили. Но нам нужна нумерация ячеек, которая начинается с единицы. Значит в формулу надо внести изменения: «=СТРОКА()-1»:

    Шаг 5. Выделяем ячейку С2 (просто щёлкаем по ней ЛМ). Обращаем внимание на строку формул:

    Шаг 6. Щёлкаем ЛМ в строке формул и добавляем «-1» и нажимаем Enter:

    Шаг 7. Теперь подводим курсор к зелёному маленькому квадратику в правом нижнем углу выделенной ячейки (курсор превращается в маленький чёрный крестик) делаем двойной щелчок ЛМ:

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

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

    1. Выделяю ячейку
    2. Набираю знак «=» с клавиатуры и начало формулы – мне тут же услужливо предлагается набор формул
    3. Выбираю нужную мне формулу
    4. Тут же в ячейке добавляю «-1»
    5. Нажимаю Enter

    5. Нумерация ячеек. Сравнение трёх способов

    Шаг 1. Выделим несколько строк:

    1. подводим курсор к столбцу названия строк, пока курсор не превратиться в горизонтально лежащую толстую стрелку
    2. нажимаем ЛМ и, не отпуская, тащим вниз (я выделила три строки)

    Шаг 2. Нажимаем ПМ и из контекстного меню выбираем команду «Удалить»

    Получаем такой результат:

    Я обращаю ваше внимание, что в столбце с формулами значения пересчитались, и нумерация ячеек не нарушилась. А вот в двух первых столбцах нумерация ячеек нарушилась. Так что выбирайте сами, какой из способов для вас выгоднее в настоящий момент. Для отчётных документов я выбираю третий способ нумерации ячеек.

    Если вам нужно добавить строчки, то добавляйте.

    Сначала шаг 1 и шаг 2 из раздела 5 (только выбираем команду «Вставить»):

    Я выделила три строчки, потому что хочу вставить три строчки, а вы выбирайте сами.

    Потом шаг 7 раздела 4 «Нумерация ячеек формулой»

    Как видите, нумерация ячеек в третьем столбце сохранилась.

    6. Особенности сложной нумерации ячеек

    Шаг 1. Подготовьте ячейки на листе следующим образом:

    Шаг 2. Первые две ячейки протянем с нажатой клавишей Ctrl, а две последний ячейки ‒ без этой клавиши:

    В данном случае нумерация ячеек при нажатой клавиши Ctrl не приводит изменениям. Где нам поможет это свойство Excel’я?

    Теперь вы сможете:

    1. Определить виды курсоров при работе с таблицей
    2. Ввести список перетаскиванием курсора
    3. Ввести список прогрессией
    4. Ввести список формулой
    5. Сравнить три способа нумерации ячеек

    Источник:
    http://prooffice24.ru/cell-numbering/

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

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

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

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

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

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

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

    2. Для ссылок

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

    Читайте также  Как в Excel выделить ячейку цветом при определенном условии

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

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

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

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

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

      Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).

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

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

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

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

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

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

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

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

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

    Если применить функцию суммирования к нашей таблице, например, к столбцу с суммой, формула будет выглядет так: =СУММ(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.Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
    6. Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
    7. Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
    8. Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.

    Заключение

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

    Источник:
    http://microexcel.ru/funkcziya-indeks/

    Функция индекс в excel – примеры использования

    Microsoft Office Excel имеет в своем арсенале формулу, которая позволяет определять координаты ячейки и возвращать ее значение в любую точку на рабочем листе. Такая опция полезна при работе с объемными базами данных, содержащих большое количество информации. Сегодня разберемся, как работает функция индекс в excel.

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

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

    1. Выбираете произвольную ячейку для отображения результата.
    2. В строке формул записываете =ИНДЕКС(B2:E8;5;3)

    где B2:E8 — диапазон значений, внутри которого необходимо найти требуемую позицию. Два последующих числа обозначают номер строки и столбца соответственно.

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

    1. Результат формулы соответствует значению в таблице, значит все сделано правильно.

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

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

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

    На заметку! Для каждого отдельного массива нумерация начинается с единицы, без учета заголовков.

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

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

    Жми «Нравится» и получай только лучшие посты в Facebook ↓

    Источник:
    http://mir-tehnologiy.ru/funktsiya-indeks-v-excel/