Национальный исследовательский университет "Высшая Школа Экономики"
Опубликован: 19.11.2012 | Доступ: свободный | Студентов: 2185 / 480 | Длительность: 30:21:00
Специальности: Менеджер, Преподаватель
Лекция 7:

Электронные таблицы

< Лекция 6 || Лекция 7: 123 || Лекция 8 >

Общей чертой всех табличных процессоров является способ ссылок на ячейки, используемых при конструировании формул. Ссылки бывают трех типов: абсолютные, относительные и смешанные. По умолчанию при создании формул применяются относительные ссылки. Относительная ссылка на ячейку, например, 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.3.
Программный продукт Open Office.org XML CSV XLS HTML PDF LaTeX ODF.ods OOXML.xlsx
Gnumeric Да Да Да Да Да Да Да Да
IBM Lotus Symphony Да Да Да Да Да Нет Да Нет
KSpread Да Да Нет Да Да Да Да Нет
Mariner Calc Нет Да Да Нет Да Нет Нет Нет
Mesa Нет Да Да Нет Да Нет Нет Нет
Microsoft Excel Нет Да Да Частично Да Нет Да Да
Numbers Нет Да Да Нет Да Нет Нет Нет
OpenOffice. org Calc Да Да Да Да Да Нет Да Да
Quattro Pro Нет Да Да Да Да Нет Нет Нет
PlanMaker Нет Да Да Да Да Нет Нет Нет
Resolver One Нет Да Да Нет Нет Нет Нет Нет
Siag Нет Да Нет Да Да Да Нет Нет
Tables Нет Да Да Да Да Да Да Нет
Таблица 7.4.
Программный продукт Open Office.org XML CSV XLS HTML LaTeX ODF.ods OOXML.xlsx
Gnumeric Да Да Да Да Да Да Да
IBM Lotus Symphony Да Да Да Нет Нет Да Да
KSpread Да Да Да Нет Нет Да Нет
Mariner Calc Нет Да Да Нет Нет Нет Нет
Mesa Нет Да Да Нет Нет Нет Нет
Microsoft Excel Нет Да Да Да Нет Да Да
Numbers Нет Да Да Нет Нет Да Да
OpenOffice. org Calc Да Да Да Да Нет Да Да
Quattro Pro Нет Да Да Да Нет Да Да
PlanMaker Нет Да Да Нет Нет Нет Да
Resolver One Нет Да Да Нет Нет Нет Нет
Siag Частично Да Частично Да Нет Нет Нет
Tables Частично Да Да Нет Нет Да Нет

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

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

  1. Как задается адрес ячейки в электронной таблице?
  2. Какие знаки операций используются в формулах электронных таблиц?
  3. С какого знака начинается ввод формулы?
  4. Как записываются абсолютные и относительные ссылки на ячейки?
  5. Что происходит с относительными ссылками при копировании формул?
  6. Каким образом нумеруются столбцы таблицы, если их количество превышает число 26?
  7. Можно ли задать в формуле ссылку на ячейку, расположенную в другой рабочей книге?
  8. Какие основные категории функций присутствуют практически во всех табличных процессорах?
  9. Какие возможности реализованы в табличных процессорах для работы со списками (табличными базами данных)?
  10. Какие функции чаще всего используются для построения условных конструкций?
  11. Какие средства предусмотрены для удобства пользователя при печати таблиц большой размерности?
  12. Какие специальные средства для эффективного моделирования различных ситуаций присутствуют в электронных таблицах?
  13. Какой тип анализа позволяет реализовать мастер сценариев?
  14. Почему с помощью инструментального средства Macrorecorder (макрорекордер) легче всего записать макрос?
  15. Как можно сохранить электронную таблицу в формате файлов, который доступен в диалоговом окне сохранения документов?
< Лекция 6 || Лекция 7: 123 || Лекция 8 >
Аннна Миллер
Аннна Миллер
Екатерина Дмитриева
Екатерина Дмитриева