2. Основные понятия электронных таблиц

Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа Microsoft Excel, в частности, ее версии - Excel 97, Excel 2000 и Excel 2002 (XP). Эта программа является типичным Windows-приложением и предоставляет в распоряжение пользователя стандартную информационную среду – окно программы, содержащее следующие элементы (Рис.1):

• строка заголовка;

• панель меню;

• панели инструментов;

• строка формул;

• окно документа;

• строка состояния.

Рис.1. Окно программы Excel

При создании новых версий корпорация Microsoft основными задачами считает улучшение и дальнейшее развитие взаимодействия между различными приложениями Office, а также возможность совместной работы над документами. Окно программы Excel 2002, кроме перечисленных элементов, содержит область задач и смарт-теги.

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

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

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

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

Ячейка является минимальным элементом хранения данных. Обозначение ячейки (ее номер) выполняет адресную функцию и включает в себя номера столбца и строки, на пересечении которых она расположена, например, A1, DE234. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в различных ячейках. Одна из ячеек является активной и выделяется рамкой активной ячейки. Эта рамка играем роль табличного курсора. Операции ввода и редактирования всегда производятся в активной ячейке.

На данные расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используются прямоугольные диапазоны, которые обозначаются номерами ячеек, расположенными, в противоположных углах прямоугольника, например: A1:C5. Программа Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка А1) и содержащую все заполненные ячейки.

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число, или формула. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Ввод формулы всегда начинается с символа = (знака равенства).

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

Оператор	Значение
(	Открыть скобку
)	Закрыть скобку
*	Умножить
/	Разделить
+	Сложить
-	Вычесть
=	Равно
<	Меньше
<=	Меньше или равно
>	Больше
>=	Больше или равно
<>	Не равно

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

Например: =A2*B1. Здесь A2 и B1 – ссылки. Ссылки играют роль адресов ячеек, содержимое которых используется в вычислениях. Различают абсолютную и относительную ссылки на ячейку. По умолчанию, ссылки на ячейки рассматриваются как относительные. Это означает, что адрес в ссылке при копировании формулы из одной ячейки в другую автоматически изменяется. Например: пусть формула, находящаяся в ячейке B2, имеет ссылку на ячейку А3, которая располагается на один столбец левее и на одну строку ниже. Если формула будет скопирована в другую ячейку, то относительное указание ссылки сохранится. Например, при копировании формулы в ячейку C7, ссылка будет продолжать указывать на ячейку, расположенную левее и ниже, в данном случае на ячейку B8. При абсолютной адресации адреса ссылок при копировании формулы не изменяются. Элементы номера ячейки, использующие абсолютную адресацию, начинаются символом $, например, $B$8. Для изменения способа адресации при редактировании формулы надо выделить ссылку и нажать клавишу F4.

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

Функция состоит из двух частей: имени функции и одного или нескольких аргументов. Имя функции – как, например, СУММ или СРЗНАЧ – описывает операцию, выполняемую этой функцией. Например, функция округления имеет следующий синтаксис:

=ОКРУГЛ(число; количество_цифр)

Аргумент число может быть числом или ссылкой на ячейку, в которой содержится значение, а второй аргумент количество_цифр – определяет, до какой цифры округляется заданное значение.

Если функция использует несколько аргументов, то каждый из них отделяется друг от друга точками с запятой. Например,

=ПРОИЗВЕД(C1;C2;C5).

В качестве аргумента можно использовать как ссылку на отдельную ячейку, так и ссылку на диапазон. Например, формула

=СУММ(A1:A5;C2:C10;D3:D17)

имеет всего три аргумента, но при этом охватывает 29 ячеек.

В качестве аргументов можно использовать не только ссылки на ячейки или диапазоны, но и числовые, текстовые и логические значения, имена диапазонов, массивы. Рассмотрим эти типы аргументов по порядку:

• Численное значение. Аргументом функции может быть любое число. Например, =СУММ(327;209;175). Однако чаще всего числовые значения вводятся не в саму формулу, а в ячейку листа, на которую потом и ссылаются аргументы функции;

• Текстовые значения. В формуле =ТЕКСТ(ТДАТА();”Д МММ ГГГГ”) вторым аргументом является текстовое значение, которое задает шаблон вывода дат при преобразовании десятичного значения даты, возвращаемого функцией ТДАТА, в строку символов. Текстовый аргумент может быть строкой символов, заключенных в двойные кавычки, или ссылкой на ячейку, которая содержит текст;

• Логические значения. Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Логическое выражение возвращает на рабочий лист или в ячейку, в которой эта формула находится, одно из логических значений. Например, формула =ЕСЛИ(А1=ИСТИНА;”Новая”;”Старая”)&”цена”- это логическое выражение. Если значение в ячейки А1 равно ИСТИНА, то функция ЕСЛИ возвращает строку Новая, а вся формула в целом возвращает значение Новая цена;

• Именованные ссылки. Аргументов функции может быть имя диапазона ячеек;

• Массивы. Массивы могут состоять из числовых, текстовых или логических значений;

• Аргументы смешанных типов. В одной функции можно применять аргументы различных типов. Например, в формуле =СРЗНАЧ(Группа1;А3;5*3) аргументами являются: в первом случае имя диапазона (Группа1), во втором – ссылка на ячейку (А3), а в третьем – числовое выражение (5*3). При этом сама формула возвращает только числовое значение.

Встроенные функции можно разделить на пять групп: математические, текстовые, логические, информационные и функции просмотра и ссылок.

Некоторые из этих функций приведены в таблицах 3-5.

Текстовые функции полезны при работе с текстом. С их помощью можно производить те же операции, что и при использовании обычных текстовых редакторов. Например, такие функции, как СЖПРОБЕЛЫ и ПЕЧСИМВ удаляют из текста лишние пробелы и непечатаемые символы, что очень полезно при чистке импортированных файлов. Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ изменяют регистр букв в тексте, преобразуя строчные буквы в прописные (или наоборот) или преобразуя в прописные все начальные буквы слов в тексте. Часть текстовых функций описана в таблице 4.

Таблица 3. Математические функции

Название функции	Синтаксис	Описание
Суммирование	=СУММ(числа)	Суммирует числа и возвращает численное значение суммы. Аргумент может включать до 30 элементов, представленных числами, формулами, диапазонами и ссылками
Произведение	=ПРОИЗВЕД (число1; число2;…)	Перемножает числа, заданные в качестве аргументов и возвращает их произведение. Может содержать до 30 аргументов, которые являются числами, логическими значениями или текстовыми представлениями числа
Сумма произведений	=СУММПРОИЗВ(массив1; массив2; …)	Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Может содержать до 30 массивов. Нечисловые элементы массивов трактуются как нулевые.
Остаток от деления	=ОСТАТ(число; делитель)	Остаток, полученный от деления аргумента число на делитель
Число комбинаций	=ЧИСЛКОМБ(число; число_выбранных)	Определяет число возможных комбинаций или групп для заданного числа элементов. Число – общее количество элементов, число выбранных – количество элементов в каждой комбинации.
Округление числа	=ОКРУГЛ(число; количество_цифр)	Округляет число, задаваемое аргументом, до указанного количества десятичных знаков.

Таблица 4. Текстовые функции

Название функции	Синтаксис	Описание
Текст	=ТЕСКТ(значение; формат)	Преобразует число в текстовую строку заданного формата. Аргумент значение может быть любым числом, формулой или ссылкой на ячейку, аргумент формат определяет формат возвращаемой строки. Пример: =ТЕКСТ(98/4;”0,00”) возвращает текстовую строку 24,50.
Рубль	=РУБЛЬ(число; число_знаков)	Преобразует число в текст, но возвращает его всегда в денежном формате с заданным количеством десятичных знаков. Пример: =РУБЛЬ(45,899;2) возвращает текстовую строку 45,90р.
Длина строки	=ДЛСТР(текст)	Возвращает количество символов в текстовой строке. Пример: =ДЛСТР(“Компьютер”) возвратит значение 9.
Совпадение	=СОВПАД(текст1; текст2)	Сравнивает две строки на предмет совпадения, включая регистр букв. Если обе строки идентичны, то функция возвращает значение ИСТИНА. Оба аргумента должны быть строками символов, заключенными в двойные кавычки, или же ссылками на ячейки, в которых хранится текст.
Сцепить	=СЦЕПИТЬ(текст1; текст2;…)	Используется для объединения текстовых строк.

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

Таблица 5. Логические функции

Название функции	Синтаксис	Описание
Если	=Если(лог_выражение; значение_если_истина; значениее_если _ложь)	Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Пример: =ЕСЛИ(А6<22;5;10) возвращает значение 5, если в ячейке А6 записано число меньше 22. В противном случае возвращается число 10.
Логическое умножение (И), логическое сложение (ИЛИ), отрицание (НЕ)	И(логическое_значение1; логическое_значение2; …) ИЛИ(логическое_значение1; логическое_значение2; …) НЕ(логическое значение)	Позволяют создавать сложные логические выражения. Функции И и ИЛИ могут включать до 30 аргументов, которые могут быть условными выражениями, массивами или ссылкам на ячейки, содержащие логическое значения. Функция НЕ имеет только один аргумент. Пример: =ЕСЛИ(И(G4<5;F4>75); “Сдал”;“Не сдал”) возвращает текстовое значение Сдал, если студент по результатам экзаменов набирает средний балл выше 75 (это число хранится в ячейке F4) и при этом пропустил не более 5 (это число хранится в ячейке G4) занятий без уважительных причин. Если хотя бы одно из условий не выполняется результат будет Не сдал.

Иногда бывает довольно сложно сформировать логическое выражение, используя только логические функции И,ИЛИ и НЕ. В этом случае можно применять вложенные функции ЕСЛИ. Например, формула:

=ЕСЛИ(А1=100;”Всегда”;ЕСЛИ(И(А1>=80;A1<=100);”Обычно”;ЕСЛИ(И(А1>=60;A1<=80);”Иногда”;”Увы!”)))

читается следующим образом: «Если значение ячейки А1 равно 100, то возвратить текстовую строку Всегда; в противном случае, если значение больше или равно 80, но меньше 100, то возвратить – Обычно; в противном случае, если это значение больше или равно 60, но меньше 80, возвратить – Иногда; и наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!».

Информационные функции можно назвать системой внутреннего мониторинга. Эти функции не выполняют никаких специфических вычислений, однако их можно использовать для получения информации об отдельных элементах интерфейса программы Excel. Информационные функции позволяют собирать сведения о содержимом ячеек, об использованных форматах, а также проверять тип значений в ячейках для задания условий и логических выражений. Например, функция ТИП определяет тип значения аргумента: содержит ли ячейка текст, число, логическое значение, массив или ошибочное значение. Результат возвращается в виде кода типа значений: 1 – число, 2 – текст, 4 – логическое значение, 16- ошибочное значение, 64 – массив. Например, если ячейка А1 содержит число 100, то формула =ТИП(А1) возвратит код 1.

Функции просмотра и ссылок помогают использовать электронную таблицу как базу данных, а полученную информацию применять в других формулах. Основные три функции для просмотра данных в таблицах и для работы со ссылками – это ПРОСМОТР, ВПР и ГПР. Функции ВПР и ГПР - почти идентичные функции, предназначенные для поиска определенной информации в таблицах. Они определяют соответственно горизонтальную и вертикальную координату искомой ячейки. Функция ПРОСМОТР ищет в заданном диапазоне наиболее близкое значение, которое меньше или равно искомому, и возвращает соответствующее значение. Функции ЧСТРОК и ЧИСЛСТОЛБЦОВ возвращают соответственно количество строк и столбцов в таблице.

Достаточно полное описание встроенных функций программы можно найти в [1].

Кроме перечисленных встроенных функций электронная таблица содержит средства финансового и статистического анализа, а также анализ «Что если».

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

К функциям для анализа инвестиций (их более 10) относятся:

• ПС (приведенная стоимость) – для оценки привлекательности долгосрочных вложений;

• ЧПС (чистая приведенная стоимость) – для оценки выгодности инвестиционного предложения;

• БС (будущая стоимость) – возвращает будущую стоимость инвестиций на основе периодических платежей и постоянной процентной ставки;

• ПЛТ – возвращает сумму периодического платежа для погашения ссуды за определенный период времени на основе постоянства сумм платежей и неизменной процентной ставки;

• ПРПЛТ – возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки;

Примеры функций для вычисления амортизации:

• АПЛ – возвращает величину амортизации актива за один период, рассчитанную линейным методом;

• ДДОВ – возвращает значение амортизации актива за данный период, используя метод двойного уменьшения остатка;

• ПУО – возвращает величину амортизации актива для любого выбранного периода;

• АСЧ - возвращает величину амортизации актива заданный период, рассчитанную методом весовых коэффициентов.

К средствам анализа типа «Что если» относятся:

• Таблица данных (таблица чувствительности, таблица подстановок) – это специальная таблица, в которой отображается результат изменения значений формулы при изменении одной или двух входящих в нее переменных.

• Диспетчер сценариев - позволяет моделировать задачи оперирующие не одной или двумя переменными, а тридцатью двумя!

• Команда Подбор параметров позволяет вычислить значение определенного параметра, чтобы получить итоговый желаемый результат;

• Инструмент Поиск решения позволяет найти несколько параметров или комбинацию параметров, определяющих максимальное или минимальное значение в заданной ячейке.

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