Холоднов В.А. Системный анализ и принятие решений. Компьютерное моделирование и оптимизация объектов химической технологии в MathCad и Excel
Подождите немного. Документ загружается.
391
Здесь если при выполнении Выражения_2 возникает ошибка, то выполняется
Выражение_2. Для обработки ошибок полезна и функция error(S), которая при
возникновении ошибки в программном моделе возвращает окошко с надписью,
хранящейся в символьной переменной S.
392
Приложение Б
(Справочное)
Работа с электронными таблицами Excel
Табличный процессор Excel – это пакет программ, предназначенный для
обработки данных, хранящихся в прямоугольных таблицах. Электронные таблицы
используются в технических, научных и бухгалтерских расчетах для обработки
табличных данных, связанных с постоянной корректировкой, пересчетом и
выдачей на печать документации одинаковой формы. Электронные таблицы
позволяют обрабатывать тексты, управлять базами данных.
Электронные таблицы можно эффективно использовать для решения научно-
технических задач, встречающихся в химии и химической технологии, например
для обработки результатов эксперимента, проведения однотипных расчетов над
большими наборами данных; построения диаграмм и графиков по имеющимся
данным; проведения поиска оптимальных значений параметров.
Особенность электронных таблиц заключается в возможности применения
формул для описания связи между значениями различных ячеек. Расчет по
заданным формулам в Excel выполняется автоматически.
Электронные таблицы Excel – компонент интегрированной офисной системы.
Представляет интерес соединение ее с другими офисными программами, такими
как, например, редактор текста Word или графическая программа CorelDraw.
Таблицы размещаются в рабочей книге, состоящей из рабочих листов
различного типа. Рабочий лист состоит из столбцов и строк. Столбцы таблицы
озаглавлены буквами, а строки пронумерованы. Место пересечения столбца и
строки называется ячейкой. В ячейках могут храниться числа, тексты и формулы.
Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и
номера строки, например, A3, F45.
Б.1 Начало работы с Excel
Б.1.1 Запуск Excel
При запуске Excel из Windows нужно дважды щелкнуть по ярлыку Excel на
рабочем столе.
При отсутствии ярлыка для запуска Excel нужно:
– щелкнуть мышью по кнопке Пуск;
– выделить группу Программы;
– в открывшемся списке щелкнуть Microsoft Excel .
В результате на экране появится окно Microsoft Excel (Рисунок Б.1).
393
Б.1.2 Окно приложения Microsoft Excel
Поле имени Стандартная панель Панель инструментов
Строка меню инструментов форматирования
Строка формул Строка заголовка
Кнопки прокрутки Область Стрелка Бегунок
ярлычков сообщений прокрутки прокрутки
Рисунок Б.1 – Окно Excel
Окно Excel содержит много элементов, присущих большинству программ в среде
Windows, а именно: Строку заголовка окна, Строку меню, Панели инструментов
Стандартная и Форматирования, Полосу прокрутки, Строку состояния, кнопки
Свернуть, Развернуть, Закрыть.
Кроме того, окно приложения Excel содержит элементы присущие только этому
приложению: ярлычки листов, кнопки прокрутки ярлычков, строку формул, вешку
ярлычков.
Б.1.3 Работа с файлами
Основными типами файлов, с которыми работает Excel является рабочая книга и
файл шаблона, имеющие расширение .xls и .xlt.
Б.1.3.1 Создание рабочей книги
После запуска Excel автоматически создает новую книгу с именем Книга1.
Новую рабочую книгу можно также создать одним из следующих способов:
– Выбором команды Файл Создать.
– Нажатием кнопки (Создать).
Б.1.3.2 Сохранение рабочей книги
Рабочая книга сохраняется одним из следующих способов:
Активная
ячейка
Заголовки
столбцов
Заголовки
строк
Ярлычки листов
394
– Выполнением команд Файл Сохранить
– Нажатием кнопки (Сохранить).
Если рабочая книга сохранялась раньше, то новая версия сохранится на диске
под тем же именем.
При первом сохранении рабочей книги или при необходимости сохранить книгу
под новым именем нужно выполнить команды Файл Сохранить как. После
выполнения этих команд на экране появится диалоговое окно Сохранение
документа. В поле Имя файла вводится имя, под которым будет сохранена
данная рабочая книга.
Б.1.3.3 Операции с рабочими листами
Размер рабочего листа определяется системой по умолчанию, максимально
возможное число столбцов – 256, строк – 65536. Последовательный переход от
одного рабочего листа к другому осуществляется при помощи кнопок прокрутки
ярлычков.
Для выделения рабочего листа нужно щелкнуть левой кнопкой мыши на ярлыке
рабочего листа.
Для выделения всех рабочих листов нужно нажать правую кнопку мыши и в
контекстном меню выполнить команду Выделить все листы.
Для выделения нескольких смежных рабочих листов нужно:
– Выделить первый рабочий лист.
– Установить мышь на ярлык последнего выделяемого листа.
– Нажать клавишу Shift и, не отпуская ее, щелкнуть левой кнопкой мыши.
Для выделения нескольких несмежных рабочих листов нужно нажать
клавишу Ctrl и не отпуская ее поочередно щелкнуть левой кнопкой мыши на
ярлыках всех выделяемых рабочих листов.
Для снятия выделения нескольких рабочих листов нужно правой кнопкой мыши
вызвать контекстное меню и выполнить команду Разгруппировать листы.
Рабочие листы можно переименовывать, копировать, перемещать, удалять и
вставлять.
Для переименования листа нужно:
– щелкнуть правой кнопкой мыши на ярлычке листа,
– в раскрывшемся контекстном меню выбрать команду Переименовать,
– в открывшемся диалоговом окне Переименование листа в поле Имя листа
ввести новое имя листа,
– нажать кнопку ОК.
Переименовать выделенный лист можно также командой Формат, Лист,
Переименовать.
Для перемещения или копирования листа нужно:
– щелкнуть правой кнопкой мыши на ярлычке листа,
– в раскрывшемся контекстном меню выбрать команду Переместить/
Скопировать.
395
– в диалоговом окне Переместить или Скопировать указать, в какую рабочую
книгу следует переместить выбранный лист и перед каким листом он должен
располагаться.
В случае копирования листа необходимо установить флажок Создавать копию.
Копировать или перемещать выделенный лист можно и с помощью команды
Правка, Переместить/Скопировать.
Операцию перемещения листа удобно производить мышью. Для этого установить
указатель мыши на ярлычке листа и, нажав левую кнопку мыши, перетащить
ярлычок в нужное место. Подобным образом можно буксировать не только
ярлычки листов, но и выделенные ячейки, их группы, и любые графические
объекты. Если при этом нажата клавиша Ctrl, то объект копируется.
Для удаления рабочего листа щелкнуть правой кнопкой мыши на ярлычке листа и
в раскрывшемся контекстном меню выбрать команду Удалить. Удалить лист
можно и командой Правка, Удалить лист.
Для вставки нового листа щелкнуть правой кнопкой мыши на ярлычке листа,
перед которым необходимо вставить новый лист. В раскрывшемся контекстном
меню в диалоговом окне Вставка выбрать тип вставляемого листа. Новый
рабочий лист можно вставить и с помощью команды Вставка, Лист.
Б.2 Ввод исходных данных в ячейку
В ячейки рабочего листа вводятся два вида данных: константы и формулы.
Константы делятся на числовые, текстовые, логические, типа дата/время.
Формулы строятся как выражения для вычисления нового значения.
При вводе содержимое активной ячейки дублируется в строке формул. Ввод
данных заканчивается нажатием клавиши Enter или нажатием кнопки .
По умолчанию текст, введенный в ячейку, выравнивается по ее левому краю, а
числа – по правому краю. Изменить способ выравнивания можно с помощью
кнопок панели Форматирование (выравнивание по левому краю),
(выравнивание по центру), (выравнивание по правому краю).
При необходимости отцентрировать текст в нескольких столбцах следует
выделить нужный диапазон ячеек и нажать кнопку (объединить и поместить в
центре).
Числовые значения, которые вводятся или вычисляются, обычно не
отформатированы, поэтому для более наглядного представления данных в
таблицах перед вводом нужно задать формат ячейки.
Б.2.1 Форматы данных
Для форматирования данных в ячейке или блоке ячеек открыть диалоговое окно
Формат ячеек, это можно одним из следующих способов:
1 способ
1. Щелчком правой кнопки мыши на ячейке вызвать контекстное меню.
2. Выбрать команду Формат ячеек.
396
2 способ
1. Выполнить команду Ячейки… в меню Формат.
Диалоговое окно Формат ячеек имеет несколько вкладок (Рисунок Б.2),
позволяющих выбирать формат записи данных:
– Вкладка Число – используется для задания типа числового формата:
количество знаков после запятой, указание денежной единицы, способ записи
даты, а также создание пользовательских форматов.
– Вкладка Выравнивание – определяет способ выравнивания значений ячейки
по горизонтали и вертикали, ориентацию текста – горизонтально, вертикально,
сверху вниз, снизу вверх, перенос по словам длинных строк текста внутри ячейки.
– Вкладка Шрифт – изменяет шрифт, стиль, размер, цвет, подчеркивание
данных.
– Вкладка Рамка – создает рамки или линии вокруг ячейки (обрамление).
– Вкладка Вид – позволяет задать окраску ячейки (цвет и узор).
– Вкладка Защита – управляет скрытием формул и блокировкой ячеек (запрет
редактирования данных ячеек).
Рисунок Б.2 – Диалоговое окно Формат ячеек
Для задания числового формата нужно:
– Выбрать вкладку Число.
– В окне Числовые форматы выбрать требуемый формат и, в случае
необходимости, в открывшемся окне указать тип.
Окно Формат ячеек позволяет также создавать пользовательский формат. Для
этого нужно:
– Выбрать вкладку Число.
– В окне Числовые форматы выбрать (все форматы).
– В верхней строке окна Тип набрать свой формат.
397
Б.2.2 Заполнение и создание рядов с помощью мыши
Для быстрого заполнения ячеек и создания рядов можно использовать маркер
заполнения. Для этого нужно:
– Записать в ячейку первое значение.
– Выделить ячейку и установить указатель на маркере заполнения.
– Перетащить правой кнопкой мыши маркер заполнения в нужном направлении.
Рисунок Б.3 – Контекстное меню для выбора способа заполнения ячеек
– В открывшемся контекстном меню выбрать команду Прогрессия (рисунок Б.3).
– В окне диалога Прогрессия указать вид прогрессии и шаг (рисунок Б.4).
Рисунок Б.4 – Окно диалога Прогрессия
Если прогрессия является арифметической, то для быстрого заполнения ячеек
можно использовать следующий способ:
– Набрать первые два значения, выделить их
– Перетащить левой кнопкой мыши маркер заполнения в нужном направлении.
398
Для заполнения столбцов или строчек текстом типа
Также можно использовать маркер заполнения, для этого нужно:
– Заполнить первую ячейку
– Выделить ячейку и установить указатель на маркере заполнения.
– Перетащить левой кнопкой мыши маркер заполнения в нужном направлении.
Б.2.3 Операции с блоками ячеек
Блок – прямоугольная область смежных или несмежных ячеек. Применительно к
ячейке и блоку выполняются следующие типовые операции (таблица Б. 1):
выделение, форматирование, удаление, вставка строк и столбцов, копирование,
перенос, очистка и заполнение блока ячеек значениями.
Таблица Б.1 – Типовые операции.
№ Название операции Порядок выполнения операции
1 Выделение блока
смежных ячеек
–
Установить курсор в ячейку, начиная с которой
выполняется выделение.
– Нажать левую кнопку мыши.
– Протащить курсор, закрашивая область выделения.
2
Выделение блока
несмежных ячеек
– Выделить блок смежных ячеек.
– Нажать клавишу Ctrl.
– Выделить следующий блок смежных ячеек и т.д.
3 Удаление блока
ячеек
– Выделить блок ячеек.
– Выполнить в меню Правка команду Удалить.
– В открывшемся окне указать объект удаления.
4 Вставка строк или
столбцов
– Выделить блок ячеек, перед которым вставляются
строки (столбцы), причем число выделенных строк
(столбцов) должно быть равно числу вставляемых
строк (столбцов).
– Выполнить в меню Вставка команду
Строки
(Столбцы).
5 Перенос блока ячеек – Выделить блок ячеек.
– Установить курсор мыши на любую линию контура.
– Нажать левую кнопку мыши и, не отпуская ее,
переместить блок в нужное место.
1 день 1995 год год 1995
2 день 1996 год год 1996
3 день или 1997 год или год 1997
4 день 1998 год год 1998
399
№ Название операции Порядок выполнения операции
6 Копирование блока
ячеек
– Выделить блок ячеек.
– Установить курсор мыши на любую линию контура.
– Нажать клавишу Ctrl.
– Нажать левую кнопку мыши и, не отпуская ее,
переместить блок в место вставки.
– Отпустить клавишу Ctrl.
7 Очистка блока ячеек – Выделить блок ячеек.
– Нажать клавишу Delete.
8 Форматирование
блока ячеек
– Выделить блок ячеек.
– Щелчком правой кнопки мыши вызвать контекстное
меню.
– Выбрать команду Формат ячеек.
– В окне Формат ячеек выбрать соответствующий
формат.
9 Заполнение блока
ячеек при
копировании
– Ввести значения или формулы в смежные ячейки.
– Выделить блок заполненных ячеек.
– Установить курсор на маркер заполнения
1
(в
нижний правый угол) блока.
– Нажать правую кнопку мыши и протянуть курсор на
требуемый размер.
– В контекстном меню выбрать команду Копировать
10 Заполнение блока
ячеек
– Ввести значения или формулы в смежные ячейки.
– Выделить блок заполненных ячеек.
– Установить курсор на маркер заполнения блока.
– Нажать правую кнопку мыши и протянуть курсор на
требуемый размер.
– В контекстном меню выбрать команду Заполнить
значения.
11 Заполнение блока
ячеек значениями
согласно прогрессии
– Ввести значения или формулы в смежные ячейки.
– Выделить блок заполненных ячеек.
– Установить курсор на маркер заполнения блока.
– Нажать правую кнопку мыши и протянуть курсор на
требуемый размер.
– В контекстном меню выбрать команду Прогрессия
– Указать параметры и тип прогрессии.
12 Создание имени
блока ячеек
– Выделить блок ячеек.
– Выполнить команду Вставка, Имя, Создать.
– Указать источник имени: верхняя или нижняя
строка, левый или правый столбец.
13 Построение
экстраполяционных
рядов (прогнозные
ряды)
– Ввести значения в смежные ячейки.
– Выделить блок заполненных ячеек.
– Установить курсор в нижний правый угол блока,
нажать правую кнопку мыши и протянуть курсор на
требуемый размер.
– Выполнить команду контекстного меню
Линейная/Экспоненциальная экстраполяция
1
Маркер заполнения – это черный квадрат в нижнем правом углу
выделенной ячейки или диапазона ячеек.
400
№ Название операции Порядок выполнения операции
14 Скрыть блок строк
(столбцов)
– Установить курсор на строке с именами столбцов
или на строке с номерами строк.
– Выделить блок строк (столбцов).
– Вызвать контекстное меню.
– Выполнить команду Скрыть
Б.3 Использование формул
Формулой в Excel называется последовательность символов, начинающаяся со
знака “=” или “+”, или ”-”.
В эту последовательность могут входить:
– числа;
– операции, тексты (вводятся в двойных кавычках, например, "Задача");
– логические значения (например, ИСТИНА, ЛОЖЬ, условия типа А23 = А45 и
т.д.);
– ссылки на ячейки или имена ячеек; круглые скобки ( );
– встроенные функции ППП Excel.
Результатом работы формулы является новое значение, которое выводится как
результат вычисления формулы по имеющимся данным. Если значения в ячейках,
на которые есть ссылки в формулах, меняются, то результат изменится
автоматически.
Б.3.1 Ссылки
Ссылка однозначно определяет ячейку или группу ячеек. Ссылки указывают, в
каких ячейках находятся используемые значения.
Существуют понятия абсолютной, относительной и смешанной ссылки.
Абсолютная ссылка – это ссылка, которая не изменяется при копировании
содержащей ее формулы, например, $A$1. Относительная ссылка – это ссылка,
которая подстраивается при копировании содержащей ее формулы, например,
D6. Если знак доллара стоит только перед буквой или номером, например, $A1
или F$9, то абсолютной является ссылка на столбец или строку. Такая ссылка
называется смешанной. Для создания абсолютной ссылки на ячейку, поставьте
знак доллара ($) перед той частью ссылки, которая не должна изменяться.
По умолчанию Excel создает в формулах относительные ссылки на адреса ячеек.
Формула, где в качестве операндов используются относительные ссылки,
воспринимаются системой как шаблон, а ссылки ячеек в таком шаблоне – как
средство указания на местоположение ячеек с операндами относительно ячейки с
формулой. Например,