9.2 Вычислительные офисные приложения

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

•  исключается необходимость в многократном вводе данных;

•  исключаются ошибки, возникающие в результате неверных расчетов и занесения в таблицу неверных результатов;

•  исключается противоречия в данных, возникающие в результате ввода ошибочно рассчитанных взаимозависимых величин;

•  существенно сокращается время и трудоемкость создания и редактирования документов.

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

Рабочий лист состоит из строк и столбцов . Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего Рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами от 1 до 65 536.

На пересечении строк и столбцов образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена. Обозначение ячейки (ее номер) - это ее адрес . Понятие адреса ячейки очень важно, так как является основой для автоматических вычислений.

Типы ячеек. Ячейка может содержать данные следующих типов:

- числовой – десятичные числа с указанным количеством знаков после запятой

- денежный – хранит суммы денежных средств с указанием символа валюты;

- дата – предназначен для хранения дат и их отображения в выбранном национальным формате;

- время – предназначен для хранения и отображения в выбранном национальном формате времени;

- процентный – хранит числа в сотых долях (процентах) и отображает их с указанием знака процента;

- экспоненциальный – используется для хранения и отображения в научном формате (например, 5,3 * 10 5 ) больших числовых величин;

- текстовый – хранит стоку текста;

Формат ячеек. В зависимости от типа ячейки над ней допустим свой набор операций. Тип ячейки можно указать как минимум двумя способами: выбрать пункт меню Формат a Ячейки и на закладке «Число» указать тип, либо щелкнуть правой кнопкой мыши и в контекстном меню выбрать пункт «Формат ячейки». При этом откроется то же диалоговое окно.

Кроме того в диалоговом окне «Формат ячейки» можно задавать следующие параметры отображения значений в ячейках:

- Закладка «выравнивание» – служит для задание выравнивания по горизонтали и вертикали. Также возможно установить режим поворота текста.

- закладка «шрифт» позволяет установить тип шрифта, его размер, начертание (обычный, полужирный, курсив) для отображения содержимого ячейки;

- закладка границы позволяет устанавливать начертание рамки для ячейки;

- закладка «вид» позволяет выбрать цвет и узор для закрашивания поля ячейки.

Формулы. Основным средством создания мощных вычислений в Excel является возможность ввода формул. Формула в любом случае начинается со знака « = », после которого могут быть записаны математические выражения, содержащие адреса ячеек, а также функции. Самым простым примером формулы является арифметические операции над числами. Например, если в ячейку ввести формулу «=1+2*3» система вычислит результат выражения и отобразит в ячейке значение 7. Более интересен случай, когда в формуле используются значения других ячеек. Пример: в ячейке B 2 записана цена товара в ячейке С2 – количество, в ячейке D 2 необходимо подсчитать итог. Для этого введем в ячейку D 2 формулу: «=B2*C2». Адреса ячеек можно вводить непосредственно с клавиатуры, а можно выделить нужную ячейку мышью, тогда её адрес будет автоматически добавлен в строку ввода формулы. После завершения ввода формулу следует нажать клавишу < Enter >. В ячейке с формулой автоматически отобразиться вычисленное значение (см. рис. 9.7). Если в процессе вычисления произошла ошибка, например, если в ячейке, используемой в арифметических вычислениях, содержится текстовое значение, то в ячейке будет выведен текст « #ЗНАЧ! », который сигнализирует об ошибке.

Рис. 9.7. Пример таблицы с вычислениями

Копирование значений. При работе с таблицами часто возникает необходимость копировать содержимое ячеек. Кроме возможностей, которые предоставляют команды «копировать», «вырезать» и «вставить», действие которых аналогично действием соответствующих команд в системе Microsoft Word , существует возможность копирования значений ячейки в прилегающий вертикальный или горизонтальный диапазон ячеек - автозаполнение . Самый простой случай автозаполнения – это заполнить определенный диапазон ячеек одним значением. Для этого надо навести курсор мыши в правый нижний угол ячейки, в которую введено текстовое или цифровое значение. Курсор при этом изменит вид с на E . Заполнение осуществляется перемещением мышь при нажатой левой кнопке по направлению вдоль текущего столбца или вдоль текущей строки. Выделенный диапазон будет заполнен исходным значением. Более сложным случаем является заполнение диапазона значений, следующих в определенном порядке. Если в ячейку введено название дня недели (или двухбуквенное сокращение), название месяца (или трехбуквенное сокращение), слово, заканчивающееся номером, то при автозаполнении система Excel автоматически будет подставлять следующие значения. Например, если исходное значение было «пн», то автозаполнение на три ближайших ячейки позволит получить значение «вт», . «ср» и «чт». Исходное значение «товар 1» позволит при автозаполнении получить значения «товар 2», «товар 3» и «товар 4». Аналогично можно получить ряд возрастающих числовых значений, для этого надо в две прилегающие ячейки ввести последовательные значения числового ряда, например 1 и 2. Затем эти последовательные ячейки необходимо выделить и применить процедуру автозаполнения (см. рис. 9.8). Следующие ячейки примут значение 3, 4, 5 и т.д.

Рис. 9.8. Автозаполнение для числового ряда

Автозаполнение может быть применено и к ячейки, которая содержит формулу. Это способ легко создавать значительные массивы вычислительных ячеек для обработки значительных объемов данных. При применении автозаполнения к формулам происходит их индексирование – адреса ячеек автоматически изменяются. Например, если в ячейки D 2 содержалась формула = B 2* C 2, то при её распространении на ячейку D 3 ссылки на ячейки автоматически изменяться и формула примет вид = B 3* C 3. При распространении формула на горизонтальный диапазон будут индексироваться имена столбцов.

Такое индексирование максимально соответствует обработке массивов данных. Однако в ряде случаев требуется ссылка в формуле на конкретную ячейку. Например, если цена задана в иностранной валюте, и для пересчета цен в рубли используется курс валюты. В этом случае адрес ячейки, в которой записан курс валюты, не должен индексироваться и должен указывать только на эту ячейку. В этом случае используется абсолютный адрес ячейки , который записывается с использованием символа «$» перед именем столбца и номером строки. Например «$ B $1». Формула в ячейке С3 будет иметь вид: «=B3*$B$1». При автозаполнении ячейки C 4 адрес ячейки B 3 будет автоматически изменен на B 4, а адрес ячейки $B$1 останется без изменений (см. рис.). Возможно применение символа «$» только к имени столбца или только к номеру строку. В этом случае будет не возможно индексирование по столбцу (или соответственно по строке) и возможно по другому направлению.

Рис. 9.9. Автозаполнение формул

Кроме использования фиксированного адреса ячейки для ссылки на конкретную ячейку можно дать ей имя. Для присвоения имени ячейки используется команда меню « Вставка -> Имя -> Присвоить ». Также имя можно непосредственно ввести в поле «Имя» на панели инструментов. Вставить имя ячейки в формулу можно непосредственно набрав его с клавиатуры, выбрав ячейку с помощью мыши, или из меню « Вставка -> Имя -> Вставить », выбрав имя из диалогового окна. Имя может быть присвоено также диапазону ячеек.

Функции. Наиболее мощные вычислительные возможности предоставляет использование функций. Система MS Excel включает большое количество функций различных по своему назначению: математические, статистические, финансовые, логические, текстовые, функции работы с датой, временем, массивами.

Отдельно выделим функции агрегирования данных: суммирование, вычисление среднего, максимума и минимума. Добавить функцию можно либо нажав кнопку на панели инструментов (нажатие на основное поле кнопки добавляет функцию суммирования, а нажатие на треугольник справа позволяет выбрать функцию) или из меню выбрав пункт « Вставка -> Функция ». Выбор данного пункта меню приводит к открытию диалогового окна выбора функции (см. рис. 9.10)

Рис. 9.10 Выбор функции

Поле «категория» позволяет выбрать категорию функции: математические, статистические, финансовые, логические, текстовые и т.д. В поле выбора функции отображается список всех функций данной категории. Ниже приводится название выбранной функции с указанием её аргументов. После выбора функции открывается диалоговое окно для ввода её аргументов. В зависимости от вида функции её аргументы могут быть как отдельные значения или адреса одиночных ячеек, так и массивы. Адрес массива задается указанием его левой верхней ячейки и через двоеточие правой нижней ячейки, например « A 3: B 6» также массив может быть задан выделением области с помощью мыши.

Ввод аргументов осуществляется в диалоговом окне «Аргументы функции» (см. рис. 9.11). Значения аргументов, в том числе адреса, могут выть набраны с клавиатуры либо выбраны с помощью мыши. Если диалоговое окно мешает обзору рабочего листа диалоговое окно можно спрятать на время выбора ячейки с помощью кнопки в правой части поля для ввода аргумента.

В качестве примера функции рассмотрим функцию, определяющую чистую текущую стоимость ( NPV ) инвестиционного проекта. Данная функция принадлежит категории «финансовые» и носит название «ЧПС». Её аргументы – ставка дисконтирование и произвольное число значений денежного потока. Исходные данные для вычисления внесены в таблицу (см. рис. 9.12). Формула в ячейке B 9 имеет вид: «=ЧПС(B1;B3;B4;B5;B6;B7;B8)»

Рис. 9.11. задание аргументов функции

Рис. 9.12. Исходные данные для вычисления NPV

Описание некоторых функций приведено в приложении 2.