Холоднов В.А. Системный анализ и принятие решений. Компьютерное моделирование и оптимизация объектов химической технологии в MathCad и Excel
Подождите немного. Документ загружается.
401
Рисунок Б.5 – Пример формулы
клетка со ссылкой С1 (рисунок Б.5) содержит формулу шаблон, сложения чисел
находящихся в ячейках А1 и D1. Ссылки А1 и D1 являются относительными и
отражают ситуацию взаимного расположения ячейки с формулой и ячейки с
данными. А1 указывает на клетку, расположенную в той же строке и смещенную
на две клетки влево, а D1 на клетку, расположенную в той же строке и смещенную
на одну клетку вправо.
Б.3.2 Операции
При написании выражений используются следующие операции:
арифметические операции:
^ (возведение в степень),
* (умножение), / (деление),
+ (сложение), - (вычитание);
операции отношения:
> (больше),
>= (не меньше),
< (меньше),
<= (не больше),
= (равно),
<> (не равно),
& операция конкатенации.
Excel предоставляет большое число встроенных функций для различных типов
вычислений (математические, статистические, логические, тестовые,
информационные и другие функции).
Б.3.3 Копирование формул
Формулы можно копировать в другие ячейки. При копировании формул можно
использовать один из двух способов.
1. С помощью маркера заполнения
– Выделить ячейку, в которой находится копируемая формула.
– Поместить указатель мыши на маркере заполнения выделенной ячейки.
– Протащить маркер заполнения вниз (вправо, влево, вверх), чтобы заполнить
нужные ячейки.
2. С помощью буфера обмена
– Выделить ячейку, в которой находится копируемая формула.
– Нажать кнопку (копировать).
– Выделить ячейку, в которую копируется формула.
– Нажать кнопку (вставить).
402
При этом в зависимости от типа ссылок, входящих в копируемую формулу,
осуществляется их настройка. Относительные ссылки, например, А2 или С34, при
копировании изменяются так, чтобы отобразить правило их вхождения в формулу
относительно ее нового местоположения.
Если необходимо, чтобы ссылки не изменялись при копировании формулы в
другую ячейку, воспользуйтесь абсолютными ссылками. Абсолютные ссылки при
копировании либо остаются неизменными, например, $A$2, либо изменяются
частично, например, $D10. Например, если имеется формула, умножающая
содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в
другую ячейку изменятся обе ссылки. Относительные ссылки автоматически
корректируются при их копировании, а абсолютные ссылки – нет.
Б.4 Использование функций Excel в формулах
Для выполнения стандартных вычислений в Excel используются функции.
Значения, которые используются для вычисления функций, называются
аргументами. Аргументы функции записываются в круглых скобках и
отделяются друг от друга символом “ ; “. Открывающаяся и закрывающаяся скобки
должны обязательно присутствовать при записи функций, при этом нельзя
вставлять пробелы между названием функции и скобками. Аргументы функции
могут быть как константами, так и формулами, причем формулы могут содержать
другие функции. Функции, являющиеся аргументами других функций, называются
вложенными.
Ввод функций можно производить с клавиатуры или с помощью диалогового окна
Мастер функций, вызываемого командой Вставка, Функция или нажатием
кнопки панели инструментов Стандартная. Мастер функций содержит список
всех функций, встроенных в Excel, а также справки по синтаксису функций и их
применению.
Пример работы с Мастером функций:
– Нажать кнопку
– В появившемся диалоговом окне, указать категорию и требуемую функцию
(рисунок Б.6)
403
Рисунок Б.6 – Окно диалога мастера функций
– Нажать кнопку ОК
– В открывшемся втором диалоговом окне (Рисунок Б. 7) указать аргументы
Рисунок Б.7 – Задание аргумента функции cos
– Нажать кнопку ОК
Б.4.1 Пример создания таблицы
Задание: Создать таблицу объема продаж канцелярских товаров (Рисунок Б.8).
404
Рисунок Б.8 – Объем продаж канцелярских товаров
– Выделить блок ячеек A1:E1. Нажать клавишу (объединить и поместить в
центре). Задать размер шрифта (14) и начертание (Полужирный, Курсив), ввести
текст «Канцелярские товары».
– Аналогичным образом ввести текст «Объем продаж, руб.»
– В ячейки B5, C5, D5, E5 и F5 ввести текст «Тетради», «Карандаши», «Ручки»,
«Блокноты», «Всего за месяц», соответственно.
– Выделить блок ячеек B5:F5. Выполнить команду Ячейки из меню Формат. В
окне Формат ячеек выбрать вкладку Выравнивание и указать ориентацию
текста (Рисунок Б.9).
405
Рисунок Б.9 – Окно Формат ячеек
– В ячейку А6 ввести «Январь» (Рисунок Б.10), выделить эту ячейку и с
помощью маркера заполнения заполнить ячейки А7:А11.
Рисунок Б.10 – Заполнение ячеек с помощью маркера заполнения
– Ввести данные в ячейки B6:F11.
– Используя Автосумму, вычислить итоговую сумму продаж в столбце Тетради и
записать результат в ячейку В12 (Рисунок Б.11).
406
Рисунок Б.11 – Вычисление с помощью автосуммы
Аналогичным образом вычисляются остальные суммы.
Б.5 Работа с массивами
При работе с таблицами часто нужно применить одну и ту же операцию к целому
диапазону ячеек или выполнить расчеты по формулам, зависящим от большого
массива данных, к таким задачам относятся, например, умножение матрицы на
матрицу или умножение матрицы на число. Для решения таких задач в Excel
можно использовать массивы.
Массивы применяются для создания формул, которые возвращают некоторое
множество результатов или оперируют множеством значений, а не отдельными
значениями. Формула массива использует несколько множеств значений,
называемых массивами аргументов, и вычисляет одно или несколько значений.
Диапазон массива – это блок ячеек, который имеет общую формулу массива.
Б.5.1 Правила для формул массива
– Перед вводом формулы массива нужно выделить ячейку или диапазон
ячеек, который будет содержать результаты. При этом выделенный диапазон
должен быть такого же размера и формы, как диапазон массива.
– Для фиксации ввода формулы массива нажмите клавиши Ctrl+Shift+Enter.
Excel заключает формулу в фигурные скобки в строке формул, указывая тем
самым, что она является формулой массива. Не вводите фигурные скобки
сами, они будут расценены как ввод текстового значения.
– В диапазоне нельзя изменять, очищать или перемещать отдельные ячейки, а
так же вставлять или удалять ячейки. Ячейки нужно рассматривать в диапазоне
массива как единое целое и редактировать их все сразу.
– Чтобы выделить весь массив, щелкните на любой ячейке в массиве и нажмите
клавиши Ctrl+/.
– Для изменения или очистки массива выделите весь массив и активизируйте
строку формул. (Фигурные скобки вокруг формулы исчезнут.) Измените или
очистите формулу, и затем нажмите клавиши Ctrl+Shift+Enter.
– Вырезать, очищать или редактировать часть массива нельзя, но можно
назначать разные форматы отдельным ячейкам в массиве и вставлять их в
другие области рабочего листа.
407
Б.5.2 Одномерные массивы
В случае одномерного массива формула массива может размещаться в строке
либо в столбце. На Рисунке Б.11 формула массива введена в вертикальный
диапазон.
Рисунок 1.12 – Использование формулы массива для вычисления произведений в
каждой строке
Пример
Вычисление среднего значения произведений элементов двух массивов.
Для вычисления среднего арифметического произведений каждой пары значений
можно использовать формулу массива и функцию СРЗНАЧ.
Для этого нужно:
– Выделить любую пустую ячейку (например, С1).
– Введить формулу =СРЗНАЧ(А1:А7*В1:В7)
– Нажать клавиши Ctrl+Shift+Enter (Рисунок Б.13).
Рисунок Б.13 – Вычисление среднего значения произведений элементов
двух массивов
408
Б.5.3 Двумерные массивы
В Excel имеются следующие специальные функции для работы с матрицами:
– МОБР Обратная матрица
– МОПРЕД Определитель матрицы
– МУМНОЖ Матричное произведение двух матриц
– ТРАНСП Транспонированная матрица
Во всех случаях при работе с матрицами перед вводом формулы надо выделить
область на рабочем листе, куда будет выведен результат вычислений.
Пример
Решим в качестве примера систему линейных уравнений с двумя неизвестными
2y5x4
3yx2
Запишем матрицу коэффициентов ячейки А4:В5, а свободные члены – в ячейки
D4:D5.
Известно, что решение линейной системы
АХ=В,
где А – матрица коэффициентов,
В – столбец (вектор ) свободных членов,
Х – столбец (вектор) неизвестных, имеет вид
Х = А
-1
В,
где А
-1
– матрица, обратная по отношению к А. Для решения нашей системы
уравнений выделим под вектор решений диапазон F4:F5 и введем в него
формулу, как показано на рисунке Б.14.
Рисунок 1.14 – Решение системы линейных уравнений
Для получения решения нажмем клавиши Ctrl+Shift+Enter.
409
Б.6 Построение диаграмм
В Excel существует созможность представлять данные и результатй расчетов в
разных типах диаграмм:
– гистограмма;
– линейчатая;
– график;
– круговая;
– точечная;
– с областями;
– кольцевая;
– лепестковая;
– поверхность;
– пузырьковая;
– биржевая;
– цилиндрическая;
– коническая;
– пирамидальная.
Б.6.1 Элементы диаграммы
В диаграмме Excel существуют следующие отдельные элементы:
– горизонтальная ось (ось X) – по этой оси откладываются значения из ряда
категорий.
– вертикальная ось (ось Y) – по этой оси откладываются наборы значений. На
одной диаграмме можно представить до 6 различных рядов значений.
– легенда – текст на диаграмме, который показывает, какие маркеры, цвета и
узоры используются для изображения каждого набора значений.
– подпись данных – текст или числа, предназначенные для идентификации
отдельных точек на диаграмме.
– заголовки – текст, предназначенный для идентификации диаграммы и ее
осей.
Диаграмма создается с помощью Мастера диаграмм, вызываемого командой
Вставка, Диаграмма, или кнопкой ("Построение диаграмм") на панели
Стандартная.
Мастер диаграмм выполняет построение новой диаграммы в интерактивном
режиме за 4 шага. Последовательность действий при построении диаграммы:
1. Выделить участок таблицы, для которого строится диаграмма.
2. Вызвать Мастера диаграмм.
3. Построение диаграммы с помощью Мастера диаграмм.
Шаг 1. Указать тип и вид диаграммы.
Excel позволяет строить диаграммы 14 стандартных типов плоскостного и
объемного представления (с областями, линейчатая, гистограмма, график,
точечная, кольцевая, объемные указанных типов, поверхностные) и 20
410
нестандартных типов (логарифмическая, графики (2 оси), гладкие графики,
цветные графики и т.д.).
Каждому типу диаграммы предоставляется на выбор несколько форматов,
которые делают диаграмму более выразительной.
Нажав кнопку Просмотр, можно выполнить предварительный просмотр
диаграммы.
Шаг 2. Источник данных диаграммы.
Второй этап мастера служит для выбора данных, по которым будет строиться
диаграмма.
На этом этапе нужно указать, где находятся ряды, а также задать имена рядов.
Для этого нужно сначала проверить диапазон данных. Для этого в группе Ряды
данных находятся установить переключатель в положении В столбцах или В
строчках. После этого щелкнуть по вкладке Ряд и указать имена рядов. В конце
нужно проверить диапазон значений и подписей по оси Х, если это необходимо
внести изменения.
Шаг 3. Параметры диаграммы.
Заголовки, оси, подписи значений, легенду, сетки и таблицу данных в диаграмме
можно либо вывести на экран, либо скрыть.
Для задания заголовков переходят на вкладку Названия элементов диаграмм.
Затем вводят необходимый текст в поля Название диаграммы, а также в поля
заголовков осей. Для добавления или удаления легенды нужно выбирают
вкладку Легенда, установливают (удаляют) флажок в поле Добавить легенду и
задают необходимый параметр в пункте Размещение.
Для добавления подписей значений выбирают необходимые параметры на
вкладке Подписей значений.
Для отображения или скрытия сетки переходят к вкладке Сетка и
устанавливают (снимают) соответствующие флажки.
Скрытие оси приводит к скрытию подписей делений оси. Для отображения или
скрытия оси переходят к вкладке Оси и устанавливают нужные флажки.
В линейчатой диаграмме, графике, заполненной диаграмме, гистограмме могут
быть отображены таблицы данных. Для этого переходят к вкладке Таблица
данных и установливают флажок в поле Таблица данных.
Шаг 4. Размещение диаграммы.
Для этой операции можно выбрать для размещения диаграммы новый лист или
уже имеющийся. Этот выбор важен для последующей печати документа,
содержащего диаграмму, причем необходимо указать лист для размещения
диаграммы.
Пример построения графика функции
Рассмотрим пример построения графика функции
x2xsin4y
2
на отрезке
[0,1]. Для построения графика функции необходимо сначала построить таблицу
значений функции при различных значениях аргумента, причем обычно аргумент
изменяется с фиксированным шагом. Примем в рассматриваемом случае шаг
равным 0.1.