Основные функциональные возможности современных табличных процессоров

Электронная таблица представляется в виде прямоугольной таблицы, разделенной на строки и столбцы (рис.7.1 – Excel, рис.7.2 – Corel QuattroPro).

Рис. 7.1.Пример электронной таблицы Excel

Рис. 7.2.Пример электронной таблицы Corel QuattroPro

В ячейках электронных таблиц можно хранить:

· числовую информацию в различных форматах (целые числа, числа с десятичной точкой, числа в экспоненциальном формате);

· текстовую информацию, представленную в виде последовательности символов, в том числе пояснительные тексты;

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

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

Минимальным элементом электронной таблицы, с которым можно выполнять те или иные операции, является клетка таблицы, которая называется ячейкой. Именно в ячейках хранятся данные различных типов, перечисленные ранее. Каждая ячейка имеет уникальный адрес, который включает в себя номер столбца и строки, на пересечении которых располагается ячейка. Нумерация столбцов обычно осуществляется с помощью латинских букв. Так как букв всего 26, а столбцов значительно больше, начиная с 27-го столбца в нумерации используются две буквы, начиная с AA: AA, AB, ..., AZ, BA, BB, BC и т.п. Нумерация строк ведется десятичными числами, начиная с единицы. Таким образом, возможны адреса ячеек B2, C265, AD11 и т.д.

Следующий объект таблицы – диапазон ячеек. Он может представлять идущие подряд ячейки строки или столбца или же диапазон ячеек может быть прямоугольной областью. При задании диапазона указывают его начальную (левую верхнюю) и конечную (правую нижнюю) ячейки, разделенные двоеточием. Наибольший диапазон представляет вся таблица, наименьший – ячейка. Примеры диапазонов – A1:A100; B12:AZ12; B2:K40.

В MS Excel таблица с данными является составной частью рабочего листа; рабочие листы, в свою очередь, объединяются в рабочую книгу. В электронных таблицах Corel Quattro Pro – такое же представление данных, но используются другие названия: вместо рабочей книги (Workbook) – блокнот (NoteBk), вместо обозначений листов "Лист1", "Лист2" и т. д. для обозначений листов используются буквы латинского алфавита (рис.7.2).

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

· Можно создавать как новые пустые рабочие книги или блокноты, так и рабочие книги или блокноты на основе одного из шаблонов, который поставляется вместе с приложением.

· Таблицы или листы можно объединять в рабочие книги или папки. В нижней части электронной таблицы расположен в виде закладок указатель, который обеспечивает доступ к рабочим листам (ярлычки листов). На рис.7.3 (a) показаны ярлычки рабочих листов в MS Excel, а на рис.7.3 (b) – ярлычки листов в Corel Quattro Pro. Пользователь может присваивать содержательные имена листам рабочей книги (рис.7.1), что значительно облегчает поиск и переход от листа к листу. Некоторые команды табличного процессора могут выполняться для группы листов.

Рис. 7.3.Ярлычки листов а) Excel б) Corel QuattroPro

· Наличие средств для оформления и модификации экрана и таблиц. Внешний вид рабочего окна и прочих элементов экранного интерфейса может быть определен в соответствии с требованиями пользователя, что делает работу максимально удобной. Среди таких возможностей – разбивка экрана на несколько панелей, фиксация заголовков строк и столбцов, высвечивание формул в ячейках и т.д. Например, в Microsoft Excel 2007 в диалоговом окне Параметры Excel (рис.7.4), которое открывается после нажатия кнопки Microsoft Office , можно установить удобные для работы опции, выбирая на левой панели группы параметров.

Рис. 7.4.Диалоговое окно Параметры Excel

· Широкие возможности оформления и вывода таблиц на печать. Для удобства пользователя предусмотрены средства, обеспечивающие печать таблиц, такие как выбор размера страницы, установка ширины полей страниц, оформление колонтитулов, разбивка на страницы, а также предварительный просмотр получившихся страниц перед печатью. Например, при использовании вкладки Лист диалогового окна Параметры страницы (рис.7.5) в поле сквозные строки можно задать одну или несколько строк, которые будут печататься сверху на каждой странице распечатываемой таблицы в качестве горизонтального заголовка, а в поле сквозные столбцы – один или несколько столбцов, которые будут печататься слева на каждой странице таблицы в качестве вертикального заголовка. Это значительно облегчит задачу установки заголовков граф и строк для многостраничной таблицы.

Рис. 7.5.Вкладка Лист диалогового окна Параметры страницы

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

Рис. 7.6.Вкладка Выравнивание диалогового окна Формат ячеек

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

Рис. 7.7.Диалоговое окно для форматирования числовых значений

Кроме того, во все программы работы с электронными таблицами встроены средства обеспечения автоматического форматирования, т. е. различные варианты автоматического оформления таблиц. Пользователю остается только выбрать наиболее подходящий из уже имеющихся форматов. Например, для MS Excel версии 2007 это выбор стиля ячеек или стиля таблицы (рис.7.8).

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

Рис. 7.8.Шаблоны стиля таблицы

Рис. 7.9.Выбор шаблона рабочего листа, содержащего расчетные формулы

Общей чертой всех табличных процессоров является способ ссылок на ячейки, используемых при конструировании формул. Ссылки бывают трех типов: абсолютные, относительные и смешанные. По умолчанию при создании формул применяются относительные ссылки. Относительная ссылка на ячейку, например, A1, основана на положении этой ячейки по отношению к ячейке, содержащей формулу. При перемещении ячейки с формулой или при копировании формулы вдоль строк или вдоль столбцов относительные ссылки, включенные в формулу, автоматически корректируются. Например, формула "=A1+B1" в ячейке C1 при копировании вниз в ячейку C2 изменяется на "=A2+B2", а при копировании вправо в ячейку D1 изменяется на "= B1+C1".

Абсолютная ссылка на ячейку, например, $A$1, всегда указывает на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, формула "=$A$1+$B$1", введенная в ячейку C1, при копировании в ячейку C2 или D1 останется без изменения.

Смешанная ссылка содержит либо абсолютный адрес столбца и относительный адрес строки, например, $A1, $B1, либо абсолютный адрес строки и относительный адрес столбца, например, A$1, B$1 и т. д. При копировании или перемещении ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная часть остается неизменной, т.е. при копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная часть ссылки не изменяется. Например, формула "=$A1+$B1", расположенная в ячейке C1, при копировании в C2 изменится на "=$A2+$B2", а при копировании в D1 останется без изменения. Напротив, формула "=A$1+B$1", расположенная в ячейке C1, при копировании в C2 останется без изменения, а при копировании в D1 изменится на "=B$1+C$1".

При изменении позиции любой ячейки, на которую ссылается формула, изменяется и ссылка на эту ячейку независимо от того, использовалась ли в формуле абсолютная или относительная ссылка.

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

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

Все табличные процессоры используют одинаковые знаки арифметических и логических операций: "+" – сложение, "–" – вычитание, "*" – умножение, "/" – деление, "^" – возведение в степень, ">" – больше, "<" – меньше, "=" – равно, ">=" – больше или равно, "<=" – меньше или равно.

Рис. 7.10.Диалоговое окно Консолидация

Для удобства вычислений в табличные процессоры встроены функции различных категорий, а именно: математические, статистические, финансовые, функции даты и времени, логические и другие. Мастер функций (рис.7.11) облегчает и ускоряет ввод формул, позволяет выбрать нужную функцию и, заполнив ее параметры, получить результат сразу в диалоговом окне.

Например, пусть требуется решить в MS Excel следующую финансовую задачу: "Через сколько лет ежеквартальные вклады размером 160 тыс. руб. принесут доход в 10 млн руб. при ставке процента 13,5% годовых? Процент начисляется по полугодиям". Для решения этой задачи применяется финансовая функция КПЕР, диалоговое окно которой с заполненными полями и результатом вычисления приведено на рис.7.11.

Рис. 7.11.Аргументы финансовой функции КПЕР

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

Приложение Microsoft Excel версии 2007 поддерживает различные типы диаграмм, позволяя строить не только диаграммы стандартных типов, но и смешанные диаграммы, используя несколько типов диаграмм.

Рис. 7.12.Диалоговое окно выбора типа линии тренда

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

При добавлении линии тренда на диаграмму доступно шесть различных типов тренда: прямая, логарифмическая, полиномиальная, степенная и экспоненциальная линии тренда, а также линия тренда с линейной фильтрацией (рис.7.13).

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

Рис. 7.13.Диалоговое окно выбора типа линии тренда

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

Рис. 7.14.Пример сводной таблицы

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

В дополнение к использованию табличных баз все современные программы работы с электронными таблицами включают средства обработки внешних баз данных, созданных, например, в формате dBase, позволяя конструировать и обрабатывать запросы к базе данных.

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

· подбор параметров,

· прогноз поведения моделируемой системы,

· анализ зависимостей,

· планирование.

Методы оптимизации (рис.7.15) варьируются от простого подбора (при этом значения ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равным заданному значению) до решения оптимальных задач со многими переменными и ограничениями.

Рис. 7.15.Установка параметров поиска решения

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

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

Рассмотрим применение аппарата сценария на примере решения задачи следующего содержания. Предположим, что вероятность того, что некто, посетивший электронный магазин на некотором сайте в Интернете, совершит покупку, составляет 20%. Если сайт посетят одновременно 10 человек, то какова вероятность того, что:

1. никто не сделает ни одной покупки;

2. точно два человека совершат по одной покупке;

3. не более 2-х человек совершат по одной покупке;

4. по крайней мере два человека совершат по одной покупке.

Как изменятся вероятности в пунктах 1)-4), если сайт посетят одновременно 20 человек или если вероятность совершения покупки одним человеком составляет 10%?

На рис.7.16 показан отчет по сценариям для решения каждого пункта сформулированной задачи.

Рис. 7.16.Пример отчета по сценариям

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

Самый простой макрос – это записанная последовательность нажатия клавиш, перемещений и щелчков кнопками мыши. Инструментальное средство Macrorecorder (Макрорекордер) отслеживает действия пользователя, выполняемые при помощи команд меню или клавиатурных эквивалентов ("горячих" клавиш), генерирует и записывает соответствующие макрокоманды до тех пор, пока пользователь не остановит процесс записи. В результате создается программа (макрос), выполнение которой дает тот же эффект, что и выполненные действия. Последовательность действий может быть воспроизведена, как магнитофонная запись. Это позволяет автоматизировать процессы обработки данных, проводимые командами меню, неоднократно выполняя записанную программу. Ее можно обработать и изменить, добавив стандартные макрокоманды и макрофункции. Для использования макрорекордера не требуется никаких знаний по программированию. При помощи макрорекордера удобно создавать макросы, которые автоматизируют рутинные операции: форматирование таблиц, создание диаграмм и сводных таблиц, работу со списками данных и т.д.

Созданный макрос можно изменить, добавив операторы встроенного языка программирования или его стандартные функции и организовав диалоги с пользователем. Современные программы обработки электронных таблиц позволяют пользователю создавать на базе табличного процессора новые приложения со специализированными диалоговыми окнами, что делает работу с приложением максимально удобной. Для создания приложений табличные процессоры содержат в качестве дополнительной компоненты язык программирования высокого уровня. Например, в MS Excel это язык Visual Basic for Application (VBA) – объектно-ориентированный язык программирования, который базируется на командах и синтаксисе языка Basic. VBA встроен в офисную среду и позволяет манипулировать не только объектами MS Excel, но и объектами всех популярных приложений Microsoft Office.

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

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

Вопросы для самопроверки

2. Как задается адрес ячейки в электронной таблице?

3. Какие знаки операций используются в формулах электронных таблиц?

4. С какого знака начинается ввод формулы?

5. Как записываются абсолютные и относительные ссылки на ячейки?

6. Что происходит с относительными ссылками при копировании формул?

7. Каким образом нумеруются столбцы таблицы, если их количество превышает число 26?

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

9. Какие основные категории функций присутствуют практически во всех табличных процессорах?

10. Какие возможности реализованы в табличных процессорах для работы со списками (табличными базами данных)?

11. Какие функции чаще всего используются для построения условных конструкций?

12. Какие средства предусмотрены для удобства пользователя при печати таблиц большой размерности?

13. Какие специальные средства для эффективного моделирования различных ситуаций присутствуют в электронных таблицах?

14. Какой тип анализа позволяет реализовать мастер сценариев?

15. Почему с помощью инструментального средства Macrorecorder (макрорекордер) легче всего записать макрос?

16. Как можно сохранить электронную таблицу в формате файлов, который доступен в диалоговом окне сохранения документов?