Файл: 3146.pdf

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

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

www.specialist.ru 

11 

Формула объединяет содержимое Кода клиента и Кода сотрудника, затем находит этот текст в 
массиве, состоящем из соответствующего объединенного текста в диапазонах Кодов клиентов и 
Кодов сотрудников. 

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

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

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

OTTIK

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

Н2

) и Код 

сотрудника 

AVA

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

Н3

). Такая форма записи 

H2&";"&H3

 исключает какие-либо 

другие комбинации, например, Код клиента 

OTTI

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

KAVA

 при объединении дадут 

тот же результат 

OTTIKAVA

.  

ПРИМЕР

: Определить Получателя для указанного Кода клиента и Кода сотрудника. 

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

– определение значение получателя 

(ячейки 

B2:B25

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

OTTIK

 (значение 

ячейки 

Н2

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

AVA 

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

Н3

). 

Использование функции ДВССЫЛ для обработки данных с одного 
или нескольких листов 

Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле, не изменяя 
саму формулу. 

ДВССЫЛ

(Ссылка_на_ячейку;А1) 

– возвращает ссылку заданную текстовой строкой. 

INDIRECT

(

Ref_text;A1

)



Ссылка_на_ячейку 

[Ref_text] – ссылка на ячейку, которая содержит ссылку в стиле А1 или 

R1C1, имя, определенное как ссылка, или ссылку на ячейку в виде текстовой строки.  



Если значение аргумента 

Cсылка_на_текст

 не является допустимой ссылкой, функция 

ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.  



Если значение аргумента 

Cсылка_на_ячейку

 является ссылкой на другую книгу 

(внешней ссылкой), другая книга должна быть открыта. В противном случае функция 
ДВССЫЛ возвращает значение ошибки #ССЫЛКА! 



A1

 [A1] – необязательный аргумент. Логическое значение, определяющее тип ссылки, 

содержащейся в поле 

Cсылка_на_ячейку

: 



1 (ИСТИНА) или опущен – стиль ссылки 

A1

. 



0 (ЛОЖЬ) – стиль ссылки 

R1C1

. 

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

www.specialist.ru 

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

12 

Например, если необходимо получить 

Сумму продажи

 за март-месяц, то можно написать 

формулу 

=F4

. Тем не менее, если нужно определять адрес ячейки при меняющихся исходных 

данных, то можно воспользоваться функцией 

ДВССЫЛ

.  

=ДВССЫЛ("F"&ПОИСКПОЗ(B3;E1:E13;0)) 

– определение значение из адреса нужной ячейки 

F4

. 

Функция 

ПОИСКПОЗ

 определяет номер строки 

4

, происходит сцепка 

"F"&4

, в результате которой 

формируется адрес 

F4

. 

=СУММ(ДВССЫЛ("F"&ПОИСКПОЗ(B3;E1:E13;0)&":F"&ПОИСКПОЗ(B4;E1:E13;0))) 

– функция СУММ 

суммирует диапазон ячеек 

F4:F6

, который определяет функция ДВССЫЛ. 

Извлечение данных с использованием функций СТРОКА, СТОЛБЕЦ  

Функции СТРОКА и СТОЛБЕЦ определяют номер строки и номер столбца соответственно, начиная 
с первой ячейки листа A1. 

СТРОКА

(Ссылка) 

– возвращает номер строки, определяемой ссылкой. 

ROW

(

Reference

)



Ссылка 

[Reference] – ячейка или диапазон ячеек, для которых требуется вернуть номер 

строки. Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой 
находится сама функция СТРОКА. 

СТРОЛБЕЦ

(Ссылка) 

– возвращает номер столбца, определяемой ссылкой. 

COLUMN

(

Reference

)



Ссылка 

[Reference] – ячейка или диапазон ячеек, для которых требуется вернуть номер 

столбца. Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой 
находится сама функция СТОЛБЕЦ. 

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

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

www.specialist.ru 

13 

ПРИМЕР

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

E3

. Определить 

доставку и скидку для указанного кода товара в ячейке 

E8

. 

=ВПР($E3;$A$3:$C$8;СТОЛБЕЦ(B2);0) 

– определение кода товара из таблицы (диапазон 

$A$3:$C$8

) 

по наименованию (ячейка 

E3

). Номер столбца в функции ВПР определяется по формуле 

СТОЛБЕЦ(B2)

, т.к. столбец 

код товара

 и в выделенной таблице и на листе является 2-м. 

=ВПР($E8;$B$13:$D$18;СТОЛБЕЦ(C12)-1;0) 

– определение значения доставки из таблицы 

(диапазон 

$B$13:$D$18

) по коду товара (ячейка 

E8

). Номер столбца в функции ВПР определяется 

по формуле 

СТОЛБЕЦ(C10)-1

, т.к. столбец 

доставка

 в выделенной таблице 2-й, а на листе 3-й. 

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

Транспонирование таблиц 

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

Транспонировать

 в 

Специальной вставке

 решает 

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

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

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

www.specialist.ru 

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

14 

Использование функции ТРАНСП и формулы массива 

ТРАНСП

(Массив) 

– преобразует вертикальный диапазон в горизонтальный, или наоборот. 

TRANSPOSE

(

Array

)



Массив 

[Array] – диапазон ячеек на листе или массив значений, который нужно 

транспонировать. 

ПРИМЕР

: Транспонировать вертикальную таблицу (ячейки 

B2:E6

) в горизонтальную таблицу. 

1.

Выделить диапазон ячеек для размещения транспонированной таблицы (ячейки 

G2:K5

). 

2.

Ввести с клавиатуры знак 

=

. 

3.

Выбрать функцию 

ТРАНСП

, выделить исходную таблицу. 

4.

Нажать 

Ctrl

+

Shift

+

Enter

.

{=ТРАНСП(B2:E6)}

 - транспонирует диапазон ячеек 

В2:Е6

 в выделенные ячейки. 

С использованием функций ДВССЫЛ, АДРЕС, СТРОКА и СТОЛБЕЦ 

Функция АДРЕС используется для получения адреса ячейки на листе, для которой указаны номера 
строки и столбца. 

АДРЕС

(Номер_строки;Номер_столбца;Тип_ссылки;А1;Имя_листа) 

– возвращает ссылку на одну 

ячейку рабочего листа в виде текста. 

ADDRESS

(

Row_num;Column_num;Abs_num;A1;Sheet_text

) 



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

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



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

[Column_num] – номер столбца, используемый в ссылке на ячейку.  



Тип_ссылки 

[Abs_num] – значение от 1 до 4, определяет тип ссылки: 



1 или опущен – $A$1 (абсолютная ссылка)  



2 – A$1  (абсолютная строка; относительный столбец)  



3 – $A1 (относительная строка; абсолютный столбец) 



4 – A1 (относительная ссылка) 



A1 

[A1] – определяет тип ссылок: А1 или R1C1 

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

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

www.specialist.ru 

15 



1 (ИСТИНА) или опущен – стиль ссылки 

A1

. 



0 (ЛОЖЬ) – стиль ссылки 

R1C1

. 



Имя_листа 

[Sheet_text] – текстовое значение, определяющее имя листа. Если аргумент 

отсутствует, то адрес, возвращаемый функцией, ссылается на ячейку текущего листа. 

ПРИМЕР

: Определить значение ячейки, которое находится на листе Заказы в 4-й строке и 3-м 

столбце. 

=ДВССЫЛ(АДРЕС(C3;C4;C5;C6;C2))

 – функция 

АДРЕС

 формирует адрес ячейки (Заказы!$C$4), а 

функция 

ДВССЫЛ

 возвращает значение из указанного адреса ячейки. 

ПРИМЕР

: Транспонировать вертикальную таблицу (ячейки 

B2:E6

) в горизонтальную таблицу 

(ячейки 

G2:K5

). 

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ()-5;СТРОКА(B2)))

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

функцией 

АДРЕС

 по номеру строки 

СТОЛБЕЦ()-5

 (-5 т.к. столбец, в который помещается результат 

находится на 5 столбцов правее) и номеру столбца 

СТРОКА(B2)

.