Пример 7.11. Цель: Рассмотреть возможность использования имен в формулах в качестве ссылок на (именованные) ячейки и диапазоны.

Постановка задачи: Имеются данные об объемах реализации одного и того же товара в магазине за второй квартал года по определенной цене. Требуется найти выручку от реализации этого товара за каждый месяц квартала (числовые значения см. ниже по тексту), используя в качестве ссылок в формулах Ехсеl имена диапазона входных данных (в частности, набранные из заголовков соответствующих строк и столбцов).

Результат:

Комментарии:

1. Присваивание имени ячейке С1: <Ctrl> + <F3> / Появляется диалоговое окно

,

Рис.7.13. Диалоговое окно Присвоение имени

/ !ЛКМ по полю Имя / Ввод Цена_за_ед_продукции / !ЛКМ по полю Формула / !ЛКМ по ячейке С1 на рабочем листе / !ЛКМ по кнопке <Добавить> / <ОК>.

2. Аналогично (только при помощи команд Вставка / Имя / Создать) присваиваются имена ячейкам В4, В5, В6, состоящие из заголовков ячеек А4, А5, А6. Диапазону В4:В6 присваивается имя Объем_реализации на основе заголовка столбца В4:В6, находящегося в ячейке В3. Напомним, что в этом случае обязательно предварительно нужно выделить блок А3:В6.

3. Далее в ячейки F4, F5, F6 стандартным образом вводятся формулы, показанные на рисунке (не забывайте нажимать клавишу <F3> в соответствующих позициях курсора ввода в набираемой формуле для ввода имен из диалогового окна Вставка Имени).

4. Достоинства использования имен:

• меньшее число ошибок при вводе ссылок в формулах (т.к. вместо ударов ЛКМ по ячейкам рабочего листа осуществляется выбор имен из диалогового окна Вставка Имени),

• определенная логика, вытекающая из смысловой нагрузки имен,

• относительно простое восприятие уже введенных формул (их лучшая «читаемость»).

5. Существенный недостаток использования абсолютных имен: нельзя копировать (перенастраиваемые) формулы с помощью маркера заполнения (их нужно вводить в каждую ячейку заново). Этот недостаток снимается применением относительных и смешанных имен. Однако, последние в настоящем пособии не рассматриваются.

Формулы массивов

Массив – множество данных, обрабатываемых Ехсеl как единое целое специальными формулами, называемыми формулами массивов. Хотя зачастую массивами называют произвольные множества данных (как правило, содержащие более одного элемента), строго говоря, эти множества не следует рассматривать как массивы до тех пор, пока они не обработаны формулой массива. Исходя из этого определения, можно сказать, что массивы данных это просто аргументы формул массивов и, в принципе, могут содержать, например, всего одно данное. Но в этом случае, видимо, не имеет смысла использовать формулы массива.

В стандартной ситуации формулы массивов оперируют на нескольких множествах данных, называемых массивами аргументов. Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений, причем число последних не обязательно совпадает с числом аргументов или числом элементов массива. Каждый элемент в массиве аргументов должен иметь соответствующие номера строки и столбца. Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем для фиксации ввода все вместе нажимаются клавиши <Ctrl> + <Shift> + <Enter> (уточнение: фиксацию ввода удобнее производить, нажимая третью и удерживая две первые клавиши). При этом Excel автоматически заключает формулы массивов в фигурные скобки { } (Заметим, что эти фигурные скобки нельзя набирать с клавиатуры, поскольку Ехсеl воспримет такое введенное значение как текстовую константу). Диапазон, в который введена формула массива, называется диапазоном массива. Иными словами, диапазон массива – это область ячеек, которая имеет общую формулу массива.

Иногда приходится очищать диапазон массива от формулы массива (или всего содержимого диапазона массива), редактировать формулу массива, изменять диапазон массива, сохраняя «старую» формулу массива или подправляя ее. В этих случаях следует придерживаться перечисленных ниже правил.

При изменении формулы массива нужно:

1. Выделить любую ячейку диапазона массива.

2. Исполнить !ЛКМ по строке формул (При этом исчезают фигурные скобки { } массива в строке формул).

3. Отредактировать формулу.

4. Нажать клавиши <Ctrl > + <Shift> + <Enter>.

При очистке диапазона массива от формулы нужно:

1. Выделить диапазон массива.

2. Нажать клавишу <Delete>

Замечание: При очистке всего содержимого, а не только формулы, нужно после выделения диапазона массива использовать команды Правка / Очистить / Все.

При изменении диапазона массива и сохранении «старой» формулы массива (или при ее незначительной подправке) нужно:

1. Выделить диапазон массива.

2. Исполнить !ЛКМ по строке формул.

3. Снять знак равенства в строке формул и нажать клавиши <Ctrl> + <Shift> + <Enter> или <Ctrl> + <Enter> (При этом в диапазоне массива возникает «массив» одинаковых текстовых значений, соответствующих синтаксису ранее введенной формулы массива).

4. Изменить диапазон массива (т.е. выделить новый диапазон, включающий хотя бы одно текстовое значение, о котором говорилось в п.3, а при необходимости внести изменение и в это текстовое значение).

5. Ввести знак равенства перед текстовым значением, соответствующим синтаксису формулы массива (тем самым мы снова вводим формулу массива).

6. Нажать клавиши <Ctrl> + <Shift> + <Enter> (тем самым мы фиксируем ввод формулы массива).

Массивы аргументов могут состоять из констант (в этом случае они называются массивами констант), ссылок на ячейки или диапазоны (в этом случае они называются массивами ссылок), или из тех и других (в этом случае они называются смешанными массивами). Массивы констант не размещаются в ячейках листа Ехсеl, а фигурируют только в формулах массивов. Там они отграничиваются от прочих данных фигурными скобками (и то в варианте, когда число констант более одного), набираемых с клавиатуры. Массивы ссылок не отграничиваются фигурными скобками.

Формирование массивов констант подчиняется определенным правилам:

1. Массив констант может состоять из чисел, текста, логических значений или значений ошибок.

2. Числа в массиве могут быть целыми, с десятичной запятой или экспоненциальными.

3. Текст должен быть взят в двойные кавычки.

4. Массив констант может состоять из элементов разного типа, например {1;3;4:ИСТИНА;ЛОЖЬ;ИСТИНА}.

5. Элементы массива должны быть константами, но не формулами.

6. Массив констант не может содержать знаки доллара ($), круглых скобок и процента (%).

7. Массив констант не может содержать ссылок.

8. Массив констант не может иметь столбцы или строки разного размера.

Пример 7.12. Цель: Отработать понятия массива констант, формулы массива и действия, выполняемые при редактировании диапазона массива.

{1,31;2,2;3,8;4,42} – это массив-строка или вектор-строка. {1,31:2,2: :3,8:4,42} – это массив-столбец или вектор-столбец. {1,31;2,2:3,8;4,42} – это массив-матрица (размерности 2х2, т.е. он размещается в двух строках и двух столбцах). Обратите внимание, что точка с запятой является разделителем элементов списка вектор-строки, а двоеточие - разделителем элементов списка вектор-столбца. Эти множества данных являются массивами, если они обрабатываются некоторыми формулами массива, к примеру, функцией ЦЕЛОЕ, возвращающей целые части чисел. При этом под результат, в первом случае, надо отвести четыре смежных «горизонтальных» ячейки, во втором случае - четыре смежных «вертикальных» ячейки, а в третьем - прямоугольный блок размерности 2х2 (напомним, что с выделения соответствующих диапазонов под возвращаемые значения нужно всегда начинать применение формул массивов).

Результат вычислений (нахождение целой части каждого из чисел умноженного на два) показан ниже:

Аналогичным образом можно применять формулы массивов не только к числовым, но и к текстовым (и прочим) данным. К примеру ниже показан результат применения функции ТРАНСП (преобразующей вертикальные ряды данных в горизонтальные и наоборот; в алгебре такое преобразование называется транспонированием) к набору текстовых значений, расположенных в ячейках А2:А5:

Предположим, что на этом этапе требуется в формуле массива сослаться на ячейку А6 с новым текстовым значением Катя, дополнительно введенным в эту ячейку с клавиатуры. Для этого нужно:

1. Выделить «старый» диапазон С1:F1 массива.

2. Исполнить !ЛКМ по строке формул.

3. Отредактировать формулу (заменив в ней ссылку А5 на ссылку А6).

4. Нажать клавиши <Ctrl > + <Shift> + <Enter>.

5. Выделить «новый» диапазон С1:G1 массива.

6. Исполнить !ЛКМ по строке формул.

7. Опять нажать клавиши <Ctrl > + <Shift> + <Enter>.

Результат показан ниже:

.

Предположим, что на этом этапе по какой-либо причине требуется очистить ячейку А6. Тогда придется отредактировать формулу массива заново, поскольку в ячейке G1 отобразится нуль как результат ссылки на пустую ячейку А6. Если просто выделить отдельную ячейку G1 и нажать клавишу <Delete> (для очистки ее содержимого), то Ехсеl отреагирует на это следующим образом:

Рис.7.14. Предупреждение Excel о неправильном редактировании

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

поскольку диапазон массива – это единое целое и его нельзя редактировать по частям. Правильные действия таковы:

1. Выделить «старый» диапазон С1:G1 массива.

2. Исполнить !ЛКМ по строке формул.

3. Снять знак равенства перед формулой.

4. Отредактировать текстовое значение ТРАНСП(A2:A6) (заменив в нем текст А6 на текстА5).

5. Нажать клавиши <Ctrl> + <Enter> (или <Ctrl> + <Shift> + <Enter>). После выполнения этого пункта на экране отобразится:

6. Выделить ячейку G1 и очистить ее содержимое.

7. Выделить «новый» диапазон С1:F1 массива.

8. Исполнить !ЛКМ по строке формул.

9. Восстановить знак равенства перед текстовым значением ТРАНСП(A2:A5), тем самым превратив его в формулу =ТРАНСП(A2:A5).

10. Опять нажать клавиши <Ctrl > + <Shift> + <Enter> и тем самым зафиксировать ввод формулы массива {=ТРАНСП(A2:A5)}.

Естественно, мы вернемся к «старому» результату.