Материал: Базовые технологии в табличном процессе Exel - Учебное пособие (Ильина О.П.)

Предмет: Информационные технологии

Просмотров: 3663


Функции  категории «ссылки и массивы»

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

По умолчанию в Microsoft Excel используются ссылки вида строка_столбец,  в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536). Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, нужно ввести ссыл 

1 На рис.26 в столбце E приведены значения, определённые по приведённой формуле.

 

 

ку на верхний левый угол диапазона, поставить двоеточие (:), а затем ввести ссылку на правый нижний угол диапазона. Например, B10:E15 -  это диапазон ячеек с 10-й по 15-ю строку в столбцах B и E.

Список имеет прямоугольную структуру, поэтому может рассматриваться как матрица, состоящая из столбцов и строк.

 

Таблица   2  Встроенные   функции   EXCEL   категории  Ссылки  и массивы

 

Функции

Назначение

АДРЕС

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

ВПР

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

ВЫБОР

Выбор значения из списка по индексу (порядковому номеру)

ГИПЕРССЫЛКА

Ссылка на документ (на жестком диске, сервере сети или в INTERNET).

ГПР

Просмотр верхней строки массива и возврат значения из ячейки в найденном столбце и указанной строке.

ДВССЫЛ

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

ИНДЕКС

Выбор значения из ссылки или массива по индексу.

ОБЛАСТИ

Вычисление количества областей в ссылке.

ПОИСКПОЗ

Поиск значения в ссылке или массиве.

ПРОСМОТР

Поиск значения в векторе или массиве.

СМЕЩ

Возврат ссылки, смещенной относительно заданной ссылки.

СТОЛБЕЦ

Возврат номера столбца, указанного в ссылке

СТРОКА

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

ТРАНСП

Возврат транспонированного массива.

ЧИСЛСТОЛБ

Подсчет количество столбцов в массиве или ссылке.

ЧСТРОК

Возврат количества строк в ссылке.

 

 

1.         АДРЕС формирование ссылки на ячейку путем указания строки и столбца, на пересечении которых находится ячейка. Ссылка может быть как относительной, так и абсолютной, включать имя листа, на котором находится ячейка. Например, формула вида =АДРЕС(10;5;1;1;"пример") вычисляет адрес ячейки: пример!$E$10,

•           "пример" имя рабочего листа (5-й параметр);

 

 

•           номер строки – 10 (1-й параметр),

•           номер столбца 5 (2-й параметр),

•           тип ссылки – 1 (3-й параметр абсолютная),

•           формат ссылки – А1 (4 –й параметр, задается как ИСТИНА или число 1).

2.         ДВССЫЛ  получение значения из указанной ячейки. Например, результат формулы: = ДВССЫЛ("E1") значение ячейки E1.

3.         Функции размерности списка:

= ЧСТРОК (блок_базы_данных) – число строк,

= ЧИСЛСТОЛБ (блок_базы_данных) – число столбцов.

4.         Функции определения номера строки или столбца для ячейки с указанным адресом или для именованного блока:

= СТОЛБЕЦ(ссылка/имя_блока)

= СТРОКА(ссылка/имя_блока).

5.         ВЫБОР  выбор значения из списка по указанному индексу (номеру).

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

=ВЫБОР(1;128;G3;G8;1024) возвращает значение 128, которое имеет индекс 1. Диапазон задается в виде конкретных значений или ссылок на ячейки, содержащие эти значения.

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

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

Например, формула вида =ИНДЕКС(База_данных;1;5) возвращает значение из ячейки в 1 строке столбца 5 (название столбца Цена) блока База_данных.

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

1 поиск наибольшего значения, которое равно или меньше искомого, массив должен быть упорядочен по возрастанию (по умолчанию);

0 поиск первого значения, которое в точности равно искомому; массив может быть в произвольном порядке. Можно использовать символы шаблона в искомом значении;

-1 -поиск наименьшего значения, которое равно или больше искомого; массив должен быть упорядочен по убыванию.

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

8.      ПРОСМОТР обеспечивает:

 

 

♦ проверку наличия искомого значения в массиве, если значение существует, выводится само значение, в противном случае выводится #Н/Д (нет данных);

♦ просмотр искомого значения в векторе просмотра и вывод соответствующего ему значения из вектора-результата.

Например,  для  формулы:  =ПРОСМОТР(1,2; HDD_Gb)  результат  #Н/Д, поскольку нет компьютеров с магнитными дисками емкостью 1,2 Гб

(список упорядочен по столбцу HDD Gb). Результат вычисления форму-

лы: =ПРОСМОТР(6,4;HDD_Gb;RAM

Mb) равен 16, поскольку для пер-

вого подходящего компьютера с указанной емкостью магнитного диска размер ОЗУ 16 Мб.

До выполнения функции ПРОСМОТР  следует  отсортировать массив в порядке возрастания  значений вектора  просмотра.

9.         ВПР "вертикальный" просмотр массива по первому столбцу. Если искомое значение найдено, отображается соответствующий результат из указанного столбца найденной строки с учетом типа сопоставления: ИСТИНА (или 1 )точное, ЛОЖЬ (или 0) приближенное сравнение.

Результат формулы: =ВПР(6,4;C1:F16;2;ИСТИНА) равен S3 Trio 3D

4Mb, осуществляется просмотр первого столбца массива C1:F16.Для первого магнитного диска заданной емкости (6,4) определяется тип видеокарты из столбца, отстоящего от первого столбца на 2 вправо (столбец Е). Сопоставление точное (тип сопоставления – ИСТИНА).

10.    ГПР "горизонтальный" просмотр массива по первой строке. Если искомое значение найдено, отображается соответствующий результат из указанной строки найденного столбца с учетом типа сопоставления: ИСТИНА (или 1) точное, ЛОЖЬ (или 0) приближенное сравнение.

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

11.    СМЕЩ  – смещение от ссылки (ячейки или блока) на указанное число строк и столбцов в нужном направлении и выбор блока ячеек требуемой конфигурации (заданной высоты и ширины).

Результатом формулы: =СМЕЩ(База_данных;0;0;1;1) является содержимое ячейки первой строки первого столбца блока База_данных имя первого поля списка.

12.    ТРАНСП  "разворот" на 90 градусов блока ячеек, вводится   как массив формул. Выделяется область, куда будет помещаться результат транспонирования, указывается ссылка на массив или имя блока, заканчивается формула нажатием клавиш ++ одновременно.