Файл: 3146.pdf

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

6 

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

Преимущества формул массивов: 



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



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



Меньший размер файлов - вместо нескольких промежуточных формул можно использовать 
одну формулу массива. 

Изменение формулы массива 

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

1.

Выделить весь массив: 



вручную 



выделить ячейку с формулой массива, нажать клавишу 

F5

, выбрать 

Выделить 

[Special], 

затем 

Текущий массив

 [Current array]. 

2.

Изменить формулу в строке формул или нажать клавишу 

F2

 для изменения в ячейке (во 

время редактирования фигурные скобки пропадают). 

3.

Завершить формулу нажатием 

Ctrl

+

Shift

+

Enter

.

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

7 

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

Последовательность действий: 

1.

Выделить ячейку для результата (или диапазон ячеек), ввести с клавиатуры знак 

=

. 

2.

Написать формулу: 



Выбрать функцию, которая работает с диапазонами (например, СУММ, СРЗНАЧ, МАКС, 
МИН, ИНДЕКС, ПОИСКПОЗ, ВПР и т.д.) 



Выделить 1-й массив (строка, столбец, таблица или именованный диапазон) 



Ввести знак операции: 

+

, 

-

, 

*

, 

/

, 

&



Выделить 2-й массив данных и т.д. 

3.

Нажать 

Ctrl

+

Shift

+

Enter

.

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

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

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

8 

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

ПОИСКПОЗ

(Искомое_значение;Просматриваемый_массив;Тип_сопоставления) 

– находит 

относительное положение элемента в диапазоне данных (поиск позиции). 

MATCH

(

Lookup_value; Lookup_array; Match_type

)



Искомое_значение

 [Lookup_value] – значение, для которого определяется относительное 

положение в диапазоне данных. 



Просматриваемый_массив 

[Lookup_array] – диапазон ячеек, в котором производится 

поиск. Чаще один столбец или одна строка; если указать несколько, то ищет совпадения в 
каждом. 



Тип_сопоставления 

[Match_type] – может принимать значения  1, 0 и -1. Определяет, 

каким образом Искомое_значение сопоставляется со значениями в аргументе 
Просматриваемый_массив. 

0 

1 

(значение по умолчанию)

-1 

Первое 

точное совпадение

при просмотре сверху вниз 

(слева направо)

Max_значение≤Искомое, 

сортировка по возрастанию

Min_значение



Искомое, 

сортировка по убыванию

10 

Пример: 
30→2 
25→# Н/Д 

10 

Пример: 

25→2 
30→3

30 

Пример: 

25→1 
10→3

30 

20 

20 

20 

30 

10 

Если функция 

ПОИСКПОЗ

 не находит соответствующего значения при точном совпадении, то 

возвращается значение ошибки 

#Н/Д

 [#N/A]. 

ПРИМЕР

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

Июнь

. 

=ПОИСКПОЗ(G1;А2:А13;0)

 – находит для значения из ячейки 

G1

 (Июнь) относительную позицию в 

просматриваемом массиве 

А2:А13

 (Месяцы). 

=ПОИСКПОЗ(G2;

B1:D1;0)

 – находит для значения из ячейки 

G2

 (Набор Gold) относительную 

позицию в просматриваемом массиве 

B1:D1

 (Наборы). 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

9 

ИНДЕКС

(Массив;Номер_строки;Номер_столбца) 

– возвращает значение ячейки из диапазона, 

заданной номером строки и номером столбца. 

INDEX

(

Array;Row_num;Column_num

)



Массив 

[Array] – таблица (массив), состоит из строк и столбцов. Если 

Массив

 содержит 

только один столбец (строку), то соответствующий аргумент Номер_строки или Номер 
столбца не является обязательным. 



Номер_строки

 [Row_num] – номер строки в массиве, из которой нужно определить 

значение. Если значение не указано, то требуется указать номера столбца. 



Номер_столбца

 [Column_num] – номер столбца в массиве, из которого определяется 

значение. Если значение не указано, то требуется указать номер строки. 

ПРИМЕР

: Определить значение Суммы продажи, если известен номер строки и номер столбца, 

в которой оно расположено. 

=ИНДЕКС(B2:D13; G4;G5)

– определение Суммы продажи (данные диапазона 

В2:D13

), получаемой 

на пересечении номера строки 6 (значение ячейки 

G4 

– позиция месяца Июнь) и номером столбца 

2 (значение ячейки 

G5

 – позиция набора Gold). 

Объединив в одну формулу функции 

ИНДЕКС

 и 

ПОИСКПОЗ

, получаем сразу результат по задаче: 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

10 

=ИНДЕКС(B2:D13;ПОИСКПОЗ(G1;A2:A13;0);ПОИСКПОЗ(G2;

B1:D1;0))

– определение Суммы 

продажи (данные диапазона 

В2:D13

), получаемой на пересечении номера строки с указанным 

месяцем Июнь (значение ячейки 

G1

)и указанным набором Набор Gold (значение ячейки 

G2

). 

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

=ИНДЕКС(СуммаПродажи;ПОИСКПОЗ(Месяц;Месяцы;0);ПОИСКПОЗ(Набор;Наборы;0)) 

Извлечение данных по нескольким критериям 

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

&

 и формулы массива.  

ПРИМЕР

: Определить номер строки в таблице для указанного Кода клиента и Кода 

сотрудника. 

{=ПОИСКПОЗ(H2&H3;A2:A25&E2:E25;0)}

 – определение позиции в таблице (номер строки), в 

которой одновременно находится Код клиента 

OTTIK

 (значение ячейки 

Н2

) и Код сотрудника 

AVA

(значение ячейки 

Н3

). Для этого Искомое_значение объединяется текстовым оператором 

&

 - 

H2&H3 и Просматриваемый_массив в такой же последовательности A2:A25&E2:E25, формула 

завершается как формула массива нажатием клавиш 

Ctrl

+

Shift

+

Enter

.