9.1. Общие сведения

MSExcel– универсальная система обработки данных – является самой объемной и существенной частью пакетаMicrosoftOffice. Ее главное назначение – использование электронных таблиц для вычислений, проектирования и анализа числовых данных, а также для отображения результатов в виде диаграмм и профессионально подготовленных документов. При работе с электронной таблицейExcelиспользуются стандартные средства продуктов фирмыMicrosoft: работа с файлами, создание, редактирование и форматирование рабочих листов, ввод, редактирование и форматирование текста и формул, работа с окнами, использование справочной системы, использование буфера обмена, работа с экраном, использование панелей инструментов и т. д. Эти вопросы подробно изложены в литературе [1 – 5]. В данной лабораторной работе рассматриваются возможностиExcelв области вычислений, порядок работы с многостраничными книгами, а также вопросы по созданию и применению удобного, но пока непривычного для большинства пользователей средства – макросов.

Excelхранит свои файлы в виде книги, состоящей из отдельных листов. Рабочие листы обычно содержат электронные таблицы или диаграммы. Электронная таблица состоит из строк и столбцов, на пересечении которых находятся ячейки, обозначаемые, как и вWord,A1, Н8 и т. д. Как и вWord, в ячейках могут находиться формулы, текст, рисунки, графические объекты.

Основное назначение Excel– работа с таблицами для проведения расчетов типа бухгалтерских, например ведомостей начисления заработной платы и т. д. Кроме того,Excelпозволяет очень легко решать многие математические задачи. Все это делаетExcelнезаменимым инструментом для специалистов разных направлений.

9.2. Формулы и Мастер функций

В любой ячейке рабочего листа может находиться формула, в которой также могут быть ссылки на другие ячейки, константы или функции, соединенные знаками арифметических операций и операций отношения. Порядок выполнения действий в формуле соответствует принятому в математике. Ввод формулы должен начинаться со знака равенства. Если формула записана верно, то обычно в ячейке отображается не сама формула, а результат ее вычисления. Можно настроить вид рабочего листа так, чтобы наоборот отражались расчетные формулы, а не результаты. Переключение режимов достигается нажатием сочетания клавиш Ctrl + ~, где знак «~»находится слева от клавиши с цифрой 1 в алфавитно-цифровой части клавиатуры.

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

Т а б л и ц а 1

Сообщения об ошибках в формулах

Код ошибки	Возможные причины ошибки
#####	Размеры ячейки недостаточны для размещения результата или числовой константы
#ДЕЛ/0!	Деление на ноль. Эта ошибка обычно возникает, когда в делителе есть ссылка на пустую ячейку
#ЗНАЧ!	Введена математическая формула, которая ссылается на текстовое значение
#ИМЯ?	Неверное имя функции или диапазона ячеек. Это же сообщение выводится, когда строка символов не заключена в двойные кавычки
#Н/Д	Нет доступного значения. Обычно такое значение ошибки вводится самим пользователем в те ячейки, которые впоследствии будут содержать данные, отсутствующие в настоящий момент. Это показывает, что они зарезервированы для ожидаемых данных
#ПУСТО!	Попытка указать в формуле пересечение двух областей, которые не имеют общих ячеек
#ССЫЛКА!	Отсутствует диапазон ячеек, на которые ссылается формула (возможно, диапазон удален)
#ЧИСЛО!	Ошибка при организации вычислений: задан неправильный аргумент функции. #ЧИСЛО! может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе

Для облегчения ввода формул используется строка формул. Чтобы поместить формулу в текущую ячейку, надо щелкнуть мышью по кнопке Изменить формулу, расположенную в строке формул (рис. 1).

На кнопке для запуска Мастер функцийизображено имя СУММ (см. рис. 1). Это означает, что данная функция выбрана в качестве текущей. Вместо нее могла быть другая функция. Если щелкнуть по этой кнопке, то откроется окно для выбора параметров функции СУММ. С помощью кнопки для выбора функции можно выбрать и другую функцию. Если в списке функций выбратьДругие функции, то откроется окно диалогаМастер функций, изображенное на рис. 2 (следует отметить, что открыть это окно можно и с помощью кнопкиВставка функции на панели инструментов Стандартная).

Допустим, выбрали функцию определения максимального значения и нажали клавишу ОК. Появится диалоговое окно ввода параметров (рис. 3). Данная функция может иметь несколько аргументов (в отличие, например, от функцииSIN, которая может иметь только один аргумент). Предположим, что нужно найти максимальное значение среди ячеекA2:D4,E1:E5,D6,A5. Это можно сделать вручную и с помощью мыши.

Чтобы задать аргументы функции вручную, надо в поле Число 1 (см. рис. 3) ввестиA2:D4, нажать клавишуTAB, курсор перейдет в поле ввода следующего аргумента, одновременно появится поле ввода третьего аргумента. В поле ввода второго аргументаЧисло 2 надо ввестиE1:E5, затем нажать клавишуTAB. В поле ввода третьего аргументаЧисло 3 ввести D6, нажать клавишуTАВ, ввести последний аргумент и нажать клавишуEnter. В текущей ячейке будет результат вычисления по формуле.

Гораздо удобнее вводить аргументы функции, выделяя их с помощью мыши непосредственно в таблице. Для этого в открывшемся окне ввода параметров (аргументов) надо щелкнуть мышью по пестрой кнопке в правой части поля Число 1. Окно диалога свернется в строку, открывая доступ к ячейкам таблицы. Далее нужно выделить мышью ячейкиA2:D4 и щелкнуть по кнопке в правой части свернутого окна аргументов. Окно ввода аргументов развернется на экране. Аналогично следует ввести остальные аргументы и нажать клавишуОК.

Можно поступить еще проще, если учесть, что Excelдопускает одновременное выделение нескольких диапазонов. После щелчка по кнопкеЧисло 1(в правой части поля) надо выделить первый диапазон мышью, как и в предыдущем случае. Далее нажать клавишуCtrlи, не отпуская ее, выделить все остальные диапазоны.

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

Для редактирования ранее введенной формулы надо выполнить на ней двойной щелчок и делать редактирование непосредственно в ячейке или в строке формул. А можно просто щелкнуть в ячейке, содержащей формулу, и редактирование проводить в строке формул.

При копировании формулы в другую (другие) ячейку происходит настройка ссылок формулы по месту. Например, если в ячейке А2 находилась формула «= А1 + 10», то при копировании этой формулы в ячейку Е6 в последней будет находиться «= Е5 + 10».

При перемещении формул настройка ссылок не происходит.

В формулах обычно присутствуют ссылки на какие-либо ячейки. В соответствии с этим различают зависимые и влияющие ячейки. Excelпо умолчанию делает автоматический пересчет значений зависящих ячеек при изменении значений влияющих ячеек. Можно настроитьExcelна ручной пересчет с помощью командыСервис, Параметры, на вкладкеВычисления которой для этого надо сбросить флажокВычисления автоматическии установить флажокВычисления вручную. НажатиеклавишиF9 делает пересчет зависимых ячеек всей книги, а нажатие кнопкиПересчет листа вкладкиВычисления осуществляет пересчет только активного листа. Ручной пересчет удобен, если таблица большая и изменений вносится много. Тогда удобнее внести все изменения, а потом выполнить пересчет.

В качестве примера рассмотрим табуляцию двух функций одной переменной, например y=sin²(x) иy=cos(x/2), где х меняется от 1 до 2 с шагом 0,2. Для этого выполните следующие действия:

1) в ячейку А1 введите число 1;

2) в ячейку А2 введите число 1,2;

3) выделите блок ячеек А1:В1. Зацепите мышью маленький крестик внизу справа (за прямоугольной рамкой выделения блока) и протяните мышью до получения в ячейке А6 числа 2;

4) в ячейку В1 введите формулу «= sin(x)^2» (вручную или с помощью Мастера функций);

5) в ячейку С1 введите формулу «= cos(x/2)»;

6) для копирования формулы в ячейки блока В2:С6 выделите блок ячеек В1:С1. Затем зацепите мышью маленький крестик внизу справа (за прямоугольной рамкой выделения блока) и протяните мышью до строки 6 (включительно).

На экране будет таблица двух функций, по которой, например, можно построить графики этих функций. Для этого надо выделить блок В1:С6, щелкнуть мышью по кнопке Мастер диаграмм на панели инструментовСтандартная и выбрать нужный тип. Простейший тип диаграммыГрафик с маркерами, помечающими точки данных, показан на рис. 4.

В качестве другого примера рассмотрим табуляцию функции двух переменных, например у = sin(x + e^xt), где х меняется от 1 до 2 с шагом 0,2, а t – от –0,5 до 0,5 с шагом 0,25.

Для решения этой задачи выполните следующие действия:

1) в ячейках В1:F1 разместите числа –0,5; –0,25; …; 0,5; а ячейках А2:А7 – числа 1; 1,2; …; 2;

2) в ячейке В2 разместите формулу «= SIN ($A2 + EXP($A2*B$1))». Отметим, что в формуле используется смешанная адресация ячеек. Это сделано потому, что значения х надо брать из столбика А, а значения t – из строки 1. Если значок «$» не поставить, то при копировании формулы в другие ячейки будут изменяться имена строк и столбцов;

3) выделите ячейку В2 и скопируйте формулу в блок ячеек В3:В7;

4) выделите блок B2:В7 и скопируйте формулу в блок ячеек B3:F7. Получилась таблица, приведенная на рис. 5.