Функция index (индекс) в excel

Функция ПОИСКПОЗ в Excel

При этом регистр не учитывается.=ПОИСКПОЗ(«е»; ; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.=ПОИСКПОЗ(«е»; ; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.=ПОИСКПОЗ(«в»; ; -1) – результат 3.=ПОИСКПОЗ(«д»; ; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

Как создать таблицу в Word

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

Для представленного выше списка из 2-х учеников потребуется создать в редакторе MS Word таблицу из 3-х строк (учеников у нас в списке пока двое, а самую первую строку таблицы мы используем для заголовков столбцов), и из 5-и столбцов для ввода в них следующих данных:

• фамилия ученика
• имя ученика
• отчество ученика
• дата рождения ученика
• телефон ученика.

Приступаем. Откроем текстовый редактор Ворд. Введем в первой строке заголовок нашей таблицы, например, «Список учащихся 8В класса».

• Затем поставим курсор на вторую строку,
• откроем вкладку «Вставка» (1 на рис. 1),
• кликнем по иконке «Таблица» (2 на рис. 1) и
• выберем строку меню «Вставить таблицу» (3 на рис. 1).

Рис. 1 (кликните по рисунку для увеличения). Создание новой таблицы в редакторе Word.

В открывшемся служебном окне «Вставка таблицы»

• указываем «Число столбцов» 5 (1 на рис. 2) и
• «Число строк» 3 (2 на рис. 2) ,
• далее жмем на кнопку «ОК» (3 на рис. 2).

Рис. 2. Описание вставляемой таблицы (указание числа столбцов и строк) в редакторе Ворд.

Таблица успешно вставляется и имеет вид, как показано на рис. 3.

Рис. 3 (кликните по рисунку для увеличения). Вставленная пока еще пустая таблица из 3-х строк и 5-и столбцов в редакторе Microsoft Word.

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

В оставшиеся 2 строки таблицы заносим данные ученика по фамилии Иванов и ученицы по фамилии Петрова (рис. 4).

Рис. 4 (кликните по рисунку для увеличения). Заполненная таблица, сделанная в редакторе Ворд.

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

Как выделить и перенести часть таблицы в Ворде

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

Скажем, нам нужны только фамилии учеников. Выделяем данные из первого столбца таблицы. Для этого помещаем курсор мышки в клетку, которая будет второй по вертикали в первой колонке таблицы – на фамилию «Иванов». Нажимаем на левую кнопку мышки и, не отпуская ее, перемещаем курсор мышки в третью строку таблицы. То есть как бы «проводим» мышкой по клеткам таблицы, где занесены фамилии учеников. Результат такого выделения показан на рис. 5.

Рис. 5 (кликните по рисунку для увеличения). Выделение одной колонки таблицы в редакторе MS Word.

Затем копируем эти данные в буфер обмена (клик мышкой на кнопку «Копировать» — 1 на рис. 6).

Рис. 6 (кликните по рисунку для увеличения). Копирование выделенной части таблицы в буфер обмена в редакторе Microsoft Word.

После копирования части таблицы в буфер обмена давайте установим курсор под таблицей не прямо на следующей строке, а через одну строку (выделено синим цветом – 1 на рис. 7), и кликнем по кнопке «Вставить» (2 на рис. 7).

Рис. 7 (кликните по рисунку для увеличения). Подготовка к вставке части таблицы в другое место текста в редакторе Word.

Часть таблицы, а именно – фамилии учеников – вставится ниже под основной таблицей (рис. 8).

Рис. 8 (кликните по рисунку для увеличения). Часть таблицы (фамилии учеников) воспроизведена в другом месте текста в редакторе Ворд.

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

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

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

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

Поиск позиции с использованием подстановочных знаков

Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.

Предположим, что имеется перечень товаров и мы не знаем точно как записана товарная позиция относящаяся к яблокам: яблоки или яблоко .

В качестве критерия можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).

Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).

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

Если задать для аргумента «номер_строки» или «номер_столбца» значение 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 и получим тот же результат.

What is the INDEX function?

The INDEX Function returns a cell value from a list or table based on it’s column and row numbers.

Basic Example

We will start with a simple list of items (column A below). We want our formula to return the 3rd item from the list.

Within the INDEX Function, first we enter the array to search. Then we specify the row number of the item we want.

Table Example

Let’s build a multiplication table and demonstrate how to use the INDEX Function to return an item based on row / column index.

To get the result of 3 times 4 (and not just write the math equation), you could write a formula of

In this scenario, we’ve told the formula to go to the 3rd row within the specified range, and the 4th column. This is going to give a reference to cell E4, and we thus get a value of 12.

Table Example – Text

We can do the same with text values.

Here, our formula goes down to the 3rd row (for the 3rd quarter) and the 2nd column. That returns a reference to C4 and the result of “Aug”.

MATCH with INDEX

The INDEX Function is often used in conjunction with the MATCH Function to create a lookup formula.

Let’s look at a grade book example where our teacher wants to be able to give a subject and student name and find the corresponding grade.

Our formula is.

We’ve used two different MATCH function to return the relative position of each of our search terms. The first one is looking for the value of H1, “Reading”, in the range of A2:A5. Reading is the 3rd item in the list. Our 2nd MATCH is similarly looking for the value of “Bob” within the range B1:E1. Bob is the 2nd item in this list. Thus, our formula will be evaluated like so:

You can learn more about the INDEX / MATCH Formula by clicking the link.

Функция АДРЕС в Excel: описание особенностей синтаксиса

Функция АДРЕС имеет следующую синтаксическую запись:

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

• Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
• Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
• – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:

1. абсолютная на всю ячейку, например — $A$4
2. абсолютная только на строку, например — A$4;
3. абсолютная только на столбец, например — $A4;
4. относительная на всю ячейку, например A4.

• – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
• – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.

1. Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
2. Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
3. Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр , который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».



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

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

Формула =ВПР(«яблоки»;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) и т.д. Но, записав формулу ввиде:

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 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 . Задавая номер строки, столбца (в подтаблице) и номер области, можно вывести соответствующий объем продаж. В файле примера , выбранные строка и столбец выделены цветом с помощью Условного форматирования .

5 интересных способов как происходит нумерация строк в Excel

Добрый день уважаемый друг!

На данный момент можно выделить 5 способов как происходит нумерация строк в Excel:

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

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

1. Третьим вариантом будет нумерация с помощью формулы «=А4+1», эта простая формула берет вышестоящее значение и прибавляет к нему 1. Как итог, получаем пронумерованный список любой длины всего за несколько секунд. Даже когда вам нужно добавить пару строк в диапазон списка, то простая протяжка формулы на месте вставки и всё прекрасно работает и радует глаз;
2. Можно усовершенствовать формулу сделав так что вертикальная нумерация строк привязалась к горизонтальной и тогда ссылка станет абсолютной. Горизонтальную нумерацию делаем при помощи функции СТОЛБЕЦ, а вертикальную при помощи функции СТРОКА, в итоге формула списка приобретёт следующий вида: =СТРОКА() -СТРОКА($A$4);
3. Ну и на закусочку можно поговорить о том как получается интеллектуальная нумерация. Для этого используем формулу следующего вида: =ЕСЛИ(ЕПУСТО(B4);»»;СЧЁТЗ($B$4:B4)). С формулы вы видите что логическая функция ЕСЛИ проверяет пустой или заполненный список. При пустом значении ячейка не заполняется, а вот при заполненной, функция СЧЁТЗ возвращает значение +1, увеличивая тем самым номер списка. Огромным преимуществом становится то, что при делении значения список сам перестраивается игнорируя пустые поля и у вас получается автоматическая нумерация в Excel.

Скачать пример можно здесь.

Не забудьте поблагодарить автора!

Деньгам по-пустому лежать не надлежит. Александр Васильевич Суворов

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

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

или в английском варианте =SUMPRODUCT(—(A2:A20B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
• и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

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

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

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

Выглядит страшновато, но свою работу выполняет отлично

The Range Format of the Excel INDEX Function

The Range format of the Index function can be used to extract references from ranges that are made up of more than one area.

The syntax of the function is:

INDEX( range, row_num, , )

Where the arguments are as follows:

range	—	Note: If multiple areas are input directly into the function, the individual areas should be separated by commas and surrounded by brackets — ie. ( A1:B2, C3:D4, etc).
row_num	—	If set to zero or blank, this defaults to all rows of the specified area within the supplied range.
	—	If set to zero or blank, this defaults to all columns of the specified area within the supplied range.
	—	If the argument is omitted, it defaults to the value 1 (i.e. the reference is taken from the first area in the supplied range.

Note that either (but not both) of the row_num or the arguments may be zero or blank. If they are both zero or blank, the Index function will return an error.

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

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

Исходная таблица имеет следующий вид:

Исходная таблица.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png» >

Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).

• 6 – номер строки искомой ячейки;
• 2 – номер столбца, в котором содержится ячейка;
• 1 – тип ссылки (абсолютная);
• 0 – указание на стиль R1C1.

В результате получим ссылку:

Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:

Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.

голоса

Рейтинг статьи

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; ), где

• ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
• диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
• номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
• интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
  • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
  • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

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

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук»

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

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;;2;ЛОЖЬ) – результат выполнения строка «Два».

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

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

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

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

Примеры применения

Рассмотрим несколько случаев использования функции “ИНДЕКС” на практике.

Предположим, имеется массив, состоящий из 4 столбцов и 4 строк (см. таблицу). Если ввести в одну из ячеек таблицы расположенное вне диапазона А1:Е5 выражение «=ИНДЕКС (В2:Е5, 2, 3)» (без кавычек) и нажать на «Ввод», то в ответ будет выдано значение «бегония».

	A	B	C	D	E
1	N/N	1	2	3	4
2	1	мак	роза	жасмин	ромашка
3	2	хризантема	нарцис	бегония	гортензия
4	3	тюльпан	фиалка	подснежник	гладиолус
5	4	астра	пион	лилия	гвоздика

Если требуется узнать, сколько учащихся Группы 2 получили оценку «неудовлетворительно», то в соответствующую ячейку следует ввести выражение: ИНДЕКС (С2:С5, 1).

	A	B	C	D	E
1	N/N	Группа 1	Групп 2	Группа 3	Группа 4
2	«неудовлетворительно»	5	4	2	4
3	«удовлетворительно»	12	10	13	11
4	«хорошо»	7	8	8	10
5	«отлично»	1	3	5	4

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

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Объединяем несколько условий.

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

Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.

Пример 1

Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.

= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)

Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.

Пример 2.

Давайте использовать несколько более сложных условий в функции ЕСЛИ.

Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.

= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)

Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:

1. Регион — Запад или Юг
2. Число больше 100.

Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)

Во-вторых, здесь все очень просто: E2> 100

В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.

В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.

Пример 3.

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

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

Все наши ранее написанные условия, в свою очередь, становятся первым аргументом в новой функции И:

1. Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
2. В названии шоколада присутствует слово «черный».

В результате мы получаем формулу ЕСЛИ с несколькими условиями:

= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.

= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

В результате количество вложенных условий в Excel может быть очень большим

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

Примеры

Пример 1

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

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

Данные	Данные
Яблоки	Лимоны
Бананы	Груши
Формула	Описание	Результат
=ИНДЕКС(A2:B3;2;2)	Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.	Груши
=ИНДЕКС(A2:B3;2;1)	Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.	Бананы

Пример 2

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

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

Формула	Описание	Результат
=ИНДЕКС({1;23;4};0;2)	Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.	2
	Значение ячейки на пересечении второй строки и второго столбца в массиве, указанном выше.	4

К началу страницы