Мастера диаграмм

Окончательный результат решения подзадачи 1) выглядит следующим образом:

Замечание: Каждый элемент построенной диаграммы может быть дополнительно отформатирован после завершения работы Мастера диаграмм. В рассматриваемом случае такое форматирование потребовалось для трех элементов: основной и дополнительной осей У и для области построения диаграммы (т.е. ее центральной части, где изображены графики). На основной оси У Excel ввел числа с точностью до двух знаков после запятой, на дополнительной оси У Excel ввел числа не в процентном, а в общем формате; в области же построения диаграммы Excel использовал принятую по умолчанию серую заливку. Для подправки каждого из перечисленных элементов сначала нужно выделить диаграмму щелчком ЛКМ по ее границе, затем подвести курсор к соответствующему элементу диаграммы (например, к дополнительной оси У) и после того, как Excel зафиксирует правильное позиционирование курсора на выбранном элементе диаграммы соответствующим сообщением (в данном случае это сообщение выглядит так: «Дополнительная ось значений»), нужно исполнить! ПКМ на выбранном элементе и в выпавшем контекстном меню активизировать команду Формат элемента ( в данном случае эта команда имеет вид: «Формат оси»). После этого нужно следовать инструкциям, прописанным в окне Формат элемента. Таким образом, основной оси был назначен числовой формат с целым представлением числа, дополнительной оси - процентный формат, а для области построения была выбрана заливка белым цветом. Отметим, что передвигаться по элементам диаграммы после ее выделения проще всего с помощью клавиатурных стрелок.

Выполняемые действия при решении подзадач 2) и 3) аналогичны предыдущим, только предварительно нужно выделить несмежные диапазоны А3: В16, F3: F16 и J3: J16, в которых содержится информация о ценах за три года (подзадача 2)), и диапазоны А3: А16, С3: С16, G3: G16 и К3: К16, в которых содержится информация о процентных приростах цен за три года (подзадача 3)).Построенные по этим исходным данным диаграммыприведены ниже.

Пример 7.7. Цель: Научиться вычислять значения функций двух переменных в конечном числе точек.

Постановка задачи: Сформировать таблицу умножения от 1 до 21 с шагом 2.

Действия, ссылки в главной формуле и форматы ссылок видны из следующей схемы:

Результат:

.

Комментарии и советы:

1. На самом деле в этой задаче мы вычислили значения функции двух переменных типа х * у в дискретных точках (1, 3), (1, 5), …, (21, 21), когда переменные х и у независимо друг от друга принимают значения от 1 до 21 с шагом 2.

2. Нетрудно обобщить использованный метод расчета на произвольные функции двух переменных. Например, если нужно вычислить значения функции (х+у^1/2)/(х * у) в точках, в которых переменная х принимает значения в промежутке от 0, 1 до 1 с шагом 0, 1, а переменная у - в промежутке от 1 до 10 с шагом 1, то мы в таблице Ехсеl отведем столбец под значения переменной х и строку - под значения переменной у; на «пересечении» первых ячеек указанных столбца и строки введем главную формулу и «протяжкой» маркера заполнения получим результат:

3. Точно также можно получать ряды значений для функций одной переменной. Например, если переменная х принимает значения в промежутке от 0, 1 до 1 с шагом 0, 1, то для функции у=sin(x^1/2+1+cos²(2х)) получаем следующий результат:

Решение последней задачи аналогично решению предыдущей и понятно из схемы, приведенной на последнем рисунке. Однако следует обратить внимание на то, как в Ехсеl вводятся вложенные функции (в математике их аналог – сложные функции). Опишем действия при вводе вложенной функции на примере ввода главной формулы в ячейку Р2 (См. последний рисунок):

I) Выделяем ячейку Р2.

II) Вызываем Мастер функций (! ЛКМ по кнопке < Вставка функции>).

III) В окне Мастера функций в списке Категория выбираем Математические, в списке Функция – функцию SIN.

IV) Нажимаем кнопку < ОК>.

V) Открывается диалоговое окно этой функции. Курсор находится в поле ввода. Исполняем! ЛКМ по ячейке P1 на листе Ехсеl. В поле ввода набираем с клавиатуры ^(1/2)+1+*

VI) Не нажимая кнопки < ОК> окна функции SIN, отжимаем кнопку < Изменить функцию> (на ней изображен знак =) в строке формул. Курсор ввода должен находиться в позиции после второго знака + (=SIN(P1^(1/2)+1+ ç ç это позиция курсора).

VII) Опять вызываем Мастер функций (! ЛКМ по кнопке < Вставка функции>).

VIII)В окне Мастера функций в списке Категория выбираем Математические, в списке Функция – функцию COS.

IX) Нажимаем кнопку < ОК>.

X) Открывается диалоговое окно этой функции. Курсор находится в поле ввода. Набираем с клавиатуры 2*. Исполняем! ЛКМ по ячейке P1. Набираем с клавиатуры ^2.

XI) Нажимаем на клавишу < Enter>.

Замечания:

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

2. Если вложенных функций несколько, то после п. X) нужно снова перейти к п. VII) и далее выполнять предписания, содержащиеся в п.п. VII) – X).

3. Описанные действия, выполняемые с целью ввода вложенных функций, следует хорошо освоить, поскольку при работе в Ехсеl их приходится часто повторять в тех или иных ситуациях. Шаг, на котором допускается больше всего ошибок, соответствует п. VI).

4. Часто приходится редактировать формулы, т.к. при их вводе допускаются ошибки. Процесс редактирования формул аналогичен процессу правки текстовых и числовых констант, кроме, пожалуй, лишь одного: редактирования встроенных в Ехсеl функций. Существенным вопросом здесь является то, как попасть в диалоговое окно уже введенной ранее функции? Для этого нужно, находясь в строке формул, установить курсор ввода на имя функции и нажать кнопку < Изменить функцию>. После этого откроется окно функции, в полях которого следует сделать исправления.

Вернемся к началу примера и обсудим еще один (более простой!) вариант его решения, основанный на использовании формул массивов. Попросту говоря*, массивы это определенным образом упорядоченные наборы данных, с которыми можно выполнять некоторые действия как с единым целым и специфически обрабатывать их, используя специальные средства, называемые формулами массивов. С точки зрения понятия массива в нашей задаче требуется строку чисел (вектор-строку) (1; 3; 5; …) умножить на аналогичный столбец чисел (вектор-столбец) (1: 3: 5: …) таким образом, чтобы получить известный нам результат. Ехсеl “берет” целую строку и каждый ее элемент умножает на первый элемент столбца, и точно также поступает со всеми прочими элементами столбца. На “выходе” возникает набор новых строк по числу элементов в столбце, т.е. массив иной структуры по сравнению с вектор-столбцом и вектор-строкой (как говорят, массив типа матрицы). Здесь в описательном плане мы использовали формулу произведения массивов, но момент, с которого начинается такой расчет в Ехсеl, состоит в ответе на вопрос: ”Где разместить массив-результат? ” С ответа на него (т.е., по сути, с конца) начинается и решение сформулированной задачи, которое опишем по шагам:

I) Выделяем диапазон В2: L12 под массив-результат.

II) Вводим с клавиатуры знак равенства или исполняем! ЛКМ по кнопке < Изменить функцию> в строке формул.

III) Применяем процедуру ПиО_ЛКМ по отношению к строке B1: L1.

IV) Вводим знак умножения с клавиатуры.

V) Применяем процедуру ПиО_ЛКМ по отношению к столбцу А2: А12.

VI) Осуществляем ввод результата вычисления, удерживая две клавиши < Ctrl> + < Shift> и ударяя по третьей клавише < Enter> ( краткая “формула” ввода массива < Ctrl> + < Shift> + < Enter>).

Заметим, что результат расчета остается прежним, если поменять местами шаги III ) и V ) в перечисленных выше действиях.

В описываемом случае формула массива имеет вид {=B1: L1*A2: A12}, т.е. ее синтаксис (и это всегда так, а не только в данном конкретном случае) включает внешние фигурные скобки*, затем идет знак равенства, за ним следуют обрабатываемые операнды (операнды-диапазоны) и обрабатывающие их операторы (здесь это знак *).

Сказанное выше проиллюстрируем рисунком:

.

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

Остается ответить на вопрос: «Так в чем же состоит преимущество использования формулы массива, по сравнению с обычным способом решения задачи?». Только в одном – нам не надо было задумываться над тем, какой формат смешанных ссылок использовать (а это, как правило, наиболее трудная часть работы в Ехсеl). Часто при применении формул массивов это преимущество сохраняется.

Пример 7.8. Цель: Научиться строить графики непрерывных и разрывных функций.

Постановка задачи: 1) Выполнить табуляцию приведенных ниже непрерывных функций у₁ и у₂ на промежутке [-3; 0] c шагом 0, 2 и построить их графики; 2) Решить аналогичную задачу для разрывной функции у₃ на отрезке [0; 1, 5] с шагом 0, 025.

.

Решение подзадачи 1) достигается

· введением в ячейки А48 и А49 первых членов арифметической прогрессии и применением к ним процедуры автозаполнения (т.е. «протяжкой» маркера заполнения вниз вплоть до ячейки А63);

· введением в ячейки В48 и С48 формул =2*SIN(A48) и =3*COS(2*A48)-SIN(A48) соответственно и применением к этим ячейкам процедуры «формульного» автозаполнения (т.е. копированием диапазона В48: С48 помощью маркера заполнения вниз вплоть до диапазона В63: С63);

· применением к диапазону А48: С63 Мастера диаграмм (в первом окне которого в списке Стандартные выбран Тип: Точечная *)

Напомним, что кроме применения процедуры «формульного» автозаполнения формулы в диапазоны В48: В63 и С48: С63 можно ввести еще двумя более простыми способами:

1. Выделить диапазон В48: В63, сделать активной ячейку В48 (используя клавишу < Tab> при необходимости перемещения по выделенному диапазону), ввести формулу =2*SIN(A48) и нажать клавиши < Ctrl> + < Enter>. Точно также ввести формулу =3*COS(2*A48)-SIN(A48) в диапазон С48: С63. В результате оба диапазона В48: В63 и С48: С63 будут заполнены правильно настроенными формулами, ссылающимися на ячейки диапазона А48: А63.

2. Выделить диапазон В48: В63, ввести формулу массива { =2*SIN(A48: А63)}. Аналогично ввести формулу массива { =3*COS(2*(A48: А63))-SIN((A48: А63))} в диапазон С48: С63 и нажать клавиши < Ctrl> + < Shift> + < Enter>.

Решение подзадачи 2) проводится по той же схеме, что и решение подзадачи 1). Различие состоит в следующих двух обстоятельствах. А) Поскольку функция у₃ сшита из двух кусков: (1+½ х½)/
(1+х²) при х< 0, 5 и х^1/3 при х> =0, 5, - то для их “объединения” в единую формулу удобно использовать встроенную функцию ЕСЛИ.
Б) Точка х=0, 5 является точкой разрыва первого рода функции у₃. Поэтому (для правильного отображения на графике скачка этой функции в точке х=0, 5) при построении ряда категорий значение 0, 5 нужно повторить в двух подряд идущих ячейках, на которые должны быть сделаны ссылки в ряде данных функции у₃ , причем в соответствующей первой зависимой ячейке должна быть реализована формула Excel на основе математической функции (1+½ х½)/(1+х²), а в соответствующей второй зависимой ячейке – на основе функции х^1/3 . Результат показан ниже:

Пример 7.9. Цель: Осознать, в каких случаях имеет смысл пользоваться абсолютными ссылками.

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

Результат:

Обсуждение: Выручка находится по алгебраической формуле < ЦЕНА> *< ОБЪЕМ РЕАЛИЗАЦИИ>. Цена записана в ячейке D3, а формулы Ехсеl, реализующие решение задачи, находятся в ячейках диапазона G7: I9. Поскольку цена товара за единицу продукции одна и та же для всех магазинов и для всех месяцев квартала, то рационально в главной формуле («лежащей» в ячейке G7) дать абсолютную ссылку на ячейку D3 (на рисунке стрелками показано ее «влияние» на все ячейки диапазона с результатами вычислений), а на ячейку В7 – дать относительную ссылку. Тогда без труда всю область выходных данных можно заполнить формулами, возникающими в результате копирования главной формулы с помощью маркера заполнения сначала вдоль соответствующей горизонтали, а затем вдоль соответствующей вертикали (или в обратном порядке). Ниже показаны формулы Ехсеl в зависимых ячейках и схема копирования главной формулы.

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

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

§ только блока входных данных,

§ только блока выходных данных,

§ блока, содержащего и те, и другие данные?

Пример 7.10. Цель: Познакомиться с «работой» функции СУММ.

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

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

Справка

Функция СУММ с уммирует все числа в интервале ячеек.

Синтаксис: = СУММ(Число1; Число2;...), где Число1, Число2,... - это от 1 до 30 аргументов, для которых требуется определить итог или сумму. При этом:

Рис. 7.12. Иллюстрация к примеру 7.10.

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

§ Если аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

§ Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.

Иллюстративные примеры

1. Если в ячейке содержится формула =СУММ(3; 2), то ее отображаемое значение равно 5 (далее для краткости будем писать СУММ(3; 2) = 5).

2. СУММ(" 3"; 2; ИСТИНА) = 6, так как текстовое значение " 3" преобразуется в число 3 (обратите внимание: в формулах текстовые строки, в том числе и числовые тексты, записываются в двойных кавычках), а логическое значение ИСТИНА преобразуется в число 1.

3. Если ячейка A1 содержит текст " 3", а ячейка B1 содержит логическое значение ИСТИНА, то СУММ(A1; B1; 2)=2, так как нечисловые значения в ссылках игнорируются.

4. Если ячейки диапазона A2: E2 содержат числа 5, 1, 3, 4 и 10, то СУММ(A2: C2)=23.

5. Если ячейки диапазона A2: E2 содержат тексты а, б, в, г, д, то СУММ(A2: Е2) = 0.

Совет: Обдумайте, как в рассмотренном выше основном примере во втором варианте расчета выручки нарастающим итогом «работают» формулы =СУММ($B$4: B4), =СУММ($B$4: B5), =СУММ($B$4: B6) и т.д. по диапазону Н4: Н9.

Использование абсолютных имен

Ячейкам, формулам и константам в Excel можно присваивать имена, которые впоследствии используются как ссылки в формулах. При этом следует придерживаться следующих правил:

1. Первый символ в имени должен быть буквой или символом подчеркивания. Остальные символы имени могут быть: буквами, числами, точками и символами подчеркивания.

2. Имена не должны иметь сходства со ссылками, например, Z$100 или R1C1.

3. Использование пробела запрещено. В качестве разделителей слов можно использовать символы подчеркивания и точки, например: «Первый.Квартал» или «Процентная_Ставка».

4. Имя может состоять из строчных или прописных букв, хотя Excel их не различает.

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

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

2. Исполнить! ЛКМ в поле имени, которое расположено в левой части строки формул.

3. Ввести с клавиатуры имя согласно упомянутым выше правилам.

4. Нажать клавишу < Enter>.

Существует альтернативный способ присваивания имен, включающий п.1 предыдущего списка (этот пункт не является обязательным) и выполнение команд Вставка / Имя / Присвоить * /! ЛКМ по полю Имя / Введение с клавиатуры выбранного имени /! ЛКМ в поле Формула /! ЛКМ по ячейке рабочего листа (или ПиО_ЛКМ по смежному диапазону) /! ЛКМ по кнопке < Добавить> / < ОК>.

Замечание: Созданные таким образом имена ячеек или диапазонов по умолчанию в формулах соответствуют абсолютным ссылкам на эти (поименованные) ячейки или диапазоны. Поэтому такие имена мы вправе называть абсолютными именами ( заметим, что в Ехсеl используются также относительные и смешанные имена ). Если в процессе ввода формулы требуется сделать ссылку на поименованный диапазон, нужно исполнить! ЛКМ по соответствующему имени в диалоговом окне Вставка имени, которое вызывается нажатием клавиши < F3> (или! ЛКМ непосредственно по именованной ячейке, или ПиО_ЛКМ по именованному диапазону).

Отметим еще одно полезное свойство существующих на рабочем листе именованных диапазонов: их можно использовать как метки при перемещениях по листу, исполняя! ЛКМ по соответствующим именам, предварительно открыв «карман имен» в левой части строки формул (при вводе формул «карман имен» становится «карманом функций», ранее применявшихся в обработке данных в текущем сеансе работы в открытой книге Ехсеl).

Очень удобно присваивать ячейкам и смежным диапазонам ячеек имена при помощи существующих (ранее набранных на клавиатуре на рабочем листе) заголовков строк и столбцов. В этом случае следует:

1. Выделить область, ячейкам которой (или которой как целому) предполагается присвоить имя, вместе со строкой или/и столбцом заголовков.

2. Выполнить команды Вставка / Имя / Создать.

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

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

Пример 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)}.

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

Пример 7.13. Постановка задачи: Число 2 требуется возвести в степень каждого из четырех чисел, записанных в ячейках А41: А44.

Результат:

Вопрос: Почему в формуле массива 2^А41: А44 диапазон А41: А44 необязательно помещать внутрь круглых скобок? (Подсказка: Вспомните понятие приоритета операторов).

Пример 7.14. Цель: Провести сравнение двух способов решения одной и той же задачи: основанного на использовании формул массивов и обычных «ячеечных» формул.

Постановка задачи: Для рабочих, занятых на двух видах работ, введена почасовая система оплаты. Кроме основного заработка, начисляемого исходя из общего объема отработанного времени, определены различные премиальные в зависимости от выполненного вида работы, начисляемые как процент от основного заработка за вычетом удержаний. Соответствующие числовые значения и прочие сведения указаны ниже в таблицах входных данных. Определить суммы к выплате, получаемые из кассы каждым рабочим.

Решение:

Результат в отображаемых значениях:

Используемые в вычислениях алгебраические формулы:

1. < Начислено по видам работ> = < Почасовая оплата> *< Всего отработано часов> *< % по видам работ>.

2. < Удержано по видам работ> = < Начислено по видам работ> *< Процент удержаний>.

3. < Премиальные к выплате по видам работ> = (< Начислено по видам работ> - < Удержано по видам работ>)*< Процент премиальных по видам работ>.

4. < Удержание с премиальных> = < Сумма премиальных к выплате по обеим видам работ> *< Процент удержаний>.

5. < Сумма к выплате > = < Всего начислено> - < Удержано с начисления> + < Всего премиальных> - < Удержано с премиальных>.

6. < Из кассы> = < Сумма к выплате> - < Долг>.

Результат в формулах массивов:

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

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

Вопрос: О какой подправке формулы идет речь на третьем шаге заполнения таблицы формулами?

Анализ последних двух рисунков показывает, что для решения задачи с помощью обычных «ячеечных» формул требуется выполнить как минимум 11 действий, не говоря уже о том, что на каждом шаге надо думать над форматом применяемых ссылок. Поэтому там, где это возможно, часто (но далеко не всегда!) имеет смысл использовать формулы массивов. Заметим, что некоторые встроенные функции «не умеют» работать с массивами «должным образом», т.е. так хотелось бы или как представляется пользователю. Поэтому во всех «подозрительных» случаях надо проводить «эксперименты» на небольшом числе данных, а также обращаться к справке Ехсеl (< F1 >) по поводу синтаксиса используемых функций.

Пример 7.15. Цель: Рассмотреть возможность совместного использования формул массивов и обычных «ячеечных» формул.

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

1) Ежемесячную выручку каждого магазина.

2) Итоговую выручку всех магазинов за каждый месяц и в целом за квартал, а также каждого магазина в целом за квартал.

3) Среднюю выручку по всем магазинам за каждый месяц и среднюю выручку за квартал по каждому магазину.

4) Среднюю суммарную выручку по всем магазинам за квартал.

5) Среднюю ежемесячную выручку по всем магазинам за квартал.

6) Максимальную и минимальную выручку по всем магазинам за каждый месяц.

7) Максимальную и минимальную выручку каждого магазина за квартал.

8) Процент месячной выручки всех магазинов в общем итоге их работы за квартал.

9) Процент суммарной выручки данного магазина за квартал от общего квартального итога работы всех магазинов.

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

Входные данные:

Результат достигается введением в соответствующие ячейки таблицы

формул, позволяющих найти ответы на поставленные в задаче вопросы. Эти формулы вместе с адресами ячеек, в которые они введены, а также направление перемещения маркера заполнения в процессе процедуры ПиО_ЛКМ приведены ниже (границы «протяжки» маркера ясны из предшествующей таблицы):

Как видно, здесь используются как формулы массивов, так и «ячеечные» формулы.

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

Справка

Функция СРЗНАЧ возвращает среднее арифметическое своих аргументов. Синтаксис: = СРЗНАЧ(число1; число2;...), где ч исло1, число2,... - это от 1 до 30 аргументов, для которых вычисляется среднее. При этом аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

Функция МАКС возвращает наибольшее значение из набора значений. Синтаксис: МАКС(число1; число2;...), где число1, число2,... - это от 1 до 30 чисел, среди которых ищется максимальное значение.

Смысл функции МИН и ее синтаксис очевидны.

Ответ на последний из поставленных в задаче вопросов содержится в следующих таблицах

,

.

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

Справка

Функция РАНГ возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией). Синтаксис: РАНГ(Число; Ссылка; Порядок), где Число - это значение (или ссылка, или массив), для которого определяется ранг. Ссылка - это массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются. Порядок - это число, определяющее способ упорядочения. Если Порядок равен 0 (нулю) или опущен, то Ехсеl определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если Порядок - это любое ненулевое число, то Ехсеl определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Функция РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. Например, если Порядок - ненулевое число и в списке целых чисел число 10 повторяется дважды и имеет ранг 5, а следующим за 10 в порядке возрастания является число 11, то оно будет иметь ранг 7 (и никакое число не будет иметь ранг 6). Здесь предполагается, что числовая последовательность сортируется по возрастанию, т.е. элемент Порядок функции РАНГ является ненулевым числом. Другой пример: если ячейки A1: A5 содержат числа 7, 3, 5, 3, 5, 1, 2 соответственно, то функция = РАНГ(A2; A1: A5; 1) возвращает значение 3 (предварительно список 7, 3, 5, 3, 5, 1, 2 нужно упорядочить по возрастанию, т.е. преобразовать в список 1, 2, 3, 5, 3, 5, 7; тогда элемент 1 занимает первое место, элемент 2 – второе, элемент 3, 5 – третье и элемент 7 – пятое место, четвертого места вообще нет), а функции = РАНГ(A1; A1: A5; 0) или = РАНГ(A1; A1: A5) возвращают значение 1 (предварительно список 7, 3, 5, 3, 5, 1, 2 нужно упорядочить по убыванию, т.е. преобразовать в список 7, 3, 5, 3, 5, 2, 1; тогда элемент 7 занимает первое место, элемент 3, 5 – второе, элемент 2 – четвертое и элемент 1 – пятое место, третьего места вообще нет).

Используя функцию РАНГ, можно найти как места, занимаемые магазинами по квартальной выручке

,

так и соответствующее распределение мест всех магазинов по всем месяцам квартала

Логические функции

В Ехсеl имеются две логические константы ИСТИНА и ЛОЖЬ и шесть логических функций ИСТИНА(), ЛОЖЬ(), И(…; …; …), НЕ(…), ИЛИ(…; …; …) и ЕСЛИ(…; …; …). Вместо многоточий в четырех из них И(…; …; …), НЕ(…), ИЛИ(…; …; …) и ЕСЛИ(…; …; …) проставляются аргументы, которые могут быть константами ИСТИНА или ЛОЖЬ, или вложенными функциями, возвращающими значения этих констант. Первые две функции ИСТИНА() и ЛОЖЬ() вообще не содержат аргументов (круглые скобки без аргументов вводить обязательно!) и всегда возвращают значения логических констант ИСТИНА и ЛОЖЬ соответственно. Эти функции используются редко (в основном для совместимости различных электронных таблиц) и почти всегда могут быть заменены соответствующими логическими значениями. Функции И(…; …
; …) и ИЛИ(…; …; …) могут зависеть от одного до 30 аргументов, которые должны принимать только логические значения, включая и возвращаемые другими логическими функциями. Функция НЕ(…) зависит только от одного логического аргумента (который также может возвращаться другими логическими функциями). Эти три функции при правильном вводе аргументов всегда возвращают одно из двух возможных логических значений ИСТИНА или ЛОЖЬ. Если же аргументы введены неверно, например, вместо логических значений введен текст, то может быть возвращено значение ошибки #ЗНАЧ! или такой аргумент может быть проигнорирован.Во всех логических функциях вместо логических констант можно использовать их числовые представления: 0 отвечает константе ЛОЖЬ, а иные числа (чаще всего 1) – константе ИСТИНА. Логическая функция ЕСЛИ(…; …; …) зависит от трех аргументов, причем первый (считая слева направо) должен принимать только логические значения, включая и возможность их возвращения другими логическими функциями, а два других аргумента могут принимать любые значения, допустимые в Ехсеl. Первый аргумент является обязательным и (при правильном вводе или при правильной ссылке) принимает одно из двух значений ИСТИНА или ЛОЖЬ. Из двух других аргументов хотя бы один является обязательным, а второй может отсутствовать. В этом случае синтаксис функции ЕСЛИ выглядит следующим образом: = ЕСЛИ(…; …) или = ЕСЛИ(…; …;), или = ЕСЛИ(…;; …) (обратите внимание на расстановку точек с запятыми ).

Логические функции предназначены для проверки выполнения одного или нескольких условий (как говорят, для проверки истинности). Так, функция ЕСЛИ возвращает значение своего второго аргумента, если первый аргумент принимает значение ИСТИНА; в противном случае она возвращает значение третьего аргумента.

Определения и иллюстративные примеры