Тема 10. Электронные таблицы

10.1. Понятие и назначение табличных процессоров

10.2. Типы используемых данных

10.3. Редактирование электронных таблиц

10.4. Средства для анализа данных

10.1. Понятие и назначение табличных процессоров

Электронной таблицей называется компьютерный эквивалент обычной

таблицы, состоящий из строк и граф, на пересечении которых расположены

клетки, содержащие числовую информацию, формулы или текст. Значение в

числовой клетке таблицы или записывается, или рассчитывается по

соответствующей формуле. В формулах могут присутствовать обращения к

другим клеткам.

При любом изменении значения в клетке таблицы, осуществлении

записи в нее нового значения с клавиатуры пересчитываются также и значения

во всех тех клетках, в которых стоят величины, зависящие от данной клетки.

Каждый документ любого табличного процессора представляет собой

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

состоит из набора строк и столбцов. Столбцы обозначаются буквами латинского

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

цифрами, расположенными в первой колонке. Пересечение строки и столбца

образует ячейку таблицы, имеющую свой уникальный адрес. Этот адрес ячейки

определяется названием столбца и номером строки. При этом способ указания

адреса ячейки называется ссылкой.

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

есть диапазона ячеек, имеющего свой собственный адрес. Адрес блока задается

указанием первой и последней из его ячеек.

Под полосой меню находятся панели инструментов. Чуть ниже — строка

формул, предназначенная для ввода данных в ячейки таблицы.

Табличные процессоры являются удобным средством для проведения

бухгалтерских и статистических расчетов. Каждый пакет включает в себя сотни

встроенных математических функций и алгоритмов статистической обработки

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

создания и редактирования электронных баз данных.

С помощью специфических средств можно автоматически получать и

распечатывать настраиваемые отчеты и использовать десятки различных типов

таблиц, графиков, диаграмм, снабжать их комментариями и графическими

иллюстрациями.

Табличные процессоры обладают встроенной справочной системой,

предоставляющей пользователю информацию по каждой из конкретных команд меню

и другие справочные данные. С помощью многомерных таблиц можно быстро делать

выборки в базе данных по любому критерию.

На сегодняшний день распространены два вида табличных процессоров:

Excel и Calc.

Табличный процессор Microsoft Excel — это инструмент для хранения,

обработки и представления чисел и таблиц. С помощью Excel можно вести

простые базы данных для учета материалов, товаров, денег, времени, погоды и

т.д. Кроме того, в Excel существует возможность оформления всех бухгалтерских

и финансовых документов, а также возможность быстрого анализа данных и

представление их в графическом виде.

Calc является приложением для работы с электронными таблицами, с

помощью которого можно выполнять расчёты, анализировать данные и

управлять ими. Кроме того, с его помощью можно импортировать и изменять

таблицы Excel. Calc предоставляет возможность вводить текст, цифры,

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

производить простые и сложные вычисления, эта программа также содержит

ряд автоматических функций для упрощения работы.

Основные возможности приложения Calc:

• Расчёты. В Calc предусмотрены функции, в том числе статистические и

финансовые, которые можно использовать для создания формул и выполнения

сложных расчётов. Для создания формул можно также использовать мастер

функций.

• Расчёты вида что-если. Эта функция позволяет немедленно отображать

результаты изменений одного из показателей, если при расчёте используются

несколько показателей. Например, при изменении временного периода,

используемого для расчёта займа, можно проследить его влияние на

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

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

• Функции базы данных. Электронные таблицы можно использовать для

упорядочивания, хранения и фильтрования данных. Calc позволяет

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

источников данных для создания стандартных писем в текстовом редакторе

Writer.

• Упорядочивание данных. С помощью мыши можно легко настроить

электронные таблицы: отобразить или скрыть диапазоны данных, изменить

форматирование диапазонов в соответствии с определёнными условиями, либо

рассчитать промежуточные и общие итоги.

• Динамические диаграммы в Calc позволяют представлять данные

электронной таблицы в динамике, то есть с обновлением при каждом

изменении данных.

• Открытие и сохранение файлов Microsoft Office. Фильтры OpenOffice

можно использовать для преобразования файлов Excel, а также для открытия и

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

10.2. Типы используемых данных

Ячейки электронных таблиц могут содержать следующие основные

типы данных:

1. Символьные (текстовые) данные имеют описательный характер. Они

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

2. Числовые данные не могут содержать алфавитных и специальных

символов, поскольку с ними производятся математические операции.

Единственными исключениями являются точка или запятая и знак числа,

стоящий перед ним.

3. Формулы. Видимое на экране содержимое ячейки – это результат

вычислений, произведенных по имеющейся в ней формуле. Формула может

включать ряд арифметических, логических и прочих действий, производимых с

данными из других ячеек.

4. Функции. Функция представляет собой программу с уникальным

именем, для которой пользователь должен задать конкретные значения

аргументов функции. Функцию как и число можно считать частным случаем

формулы. Различают следующие виды функций:

- математические;

- статистические;

- текстовые;

- логические;

- финансовые;

- функции даты и времени.

5. Даты, особым типом входных данных являются даты. Этот тип

данных обеспечивает выполнение таких функций, как добавление числа к дате

(пересчет даты вперед и назад) или вычисление разности двух дат

(длительности периода).

10.3. Редактирование электронных таблиц

Для создания нового документа, при загруженной электронной таблице

нужно выбрать пункт меню Файл>Создать Электронную таблицу. Для

сохранения готовой таблицы необходимо воспользоваться пунктом меню

Файл>Сохранить как и в строке Фильтр открывшегося диалога выбрать

подходящий формат (например, XLS).

Работа с окнами (аналогична в Excel и в Calc)

Для открытия существующего документа используется пункт меню

Файл> Открыть. Если лист при печати не умещается на одной странице, то

табличный процессор равномерно разобьёт этот лист на несколько страниц. Так

как автоматический разрыв страницы не всегда совпадает с требованиями

пользователя, то этот разрыв можно установить вручную. Для того, чтобы

увидеть, как документ будет распределён по страницам при печати, необходимо

в меню выбрать Вид> Предварительный просмотр деления на страницы.

Синими линиями будут показаны границы листов, а на фоне страниц можно

будет увидеть номера листов. В этом режиме можно изменять границы листов.

Для этого следует курсором мышки перетащить синюю линию в нужное место.

Для доступа к функции «Список всех открытых документов» выберите

«Окно». Здесь приведён список всех открытых документов. Текущие видимые

окна документов отмечены галочкой. При пометке пункта «Фиксировать» в

меню Окно лист разделён относительно верхнего левого угла активной ячейки и

области слева вверху зафиксированы, то есть их нельзя больше перемещать.

Управление листами

Электронная таблица может состоять из одного или более листов. По

умолчанию все табличные процессоры отображают три листа от Лист1 до Лист

3 в каждой новой электронной таблице. Вкладка (ярлык) текущего листа

выделена белым цветом. Слева от ярлыков листов расположены кнопки

перемещения по листам. Кнопки с треугольником перемещают на соседний

лист, а кнопки, где треугольник упирается в полоску, перемещает пользователя

на первый или последний листы.

В Excel предлагаемое по умолчанию количество листов можно

скорректировать командой Сервис — Параметры — Общие.

Листы можно добавлять и удалять. Чтобы добавить в рабочую книгу лист,

надо выбрать команду Вставка — Лист. Чтобы удалить лист, нужно щелчком левой

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

Правка — Удалить лист. Ярлыки рабочих листов расположены в порядке

возрастания номеров. Листу можно присвоить имя, вызвав контекстное меню на

ярлыке листа и выбрав команду Переименовать.

Листы можно перемещать и копировать в ту же или в другую рабочую

книгу. Перемещение листа внутри активной рабочей книги осуществляется

перетаскиванием ярлыка листа в новую позицию левой кнопкой мыши. Копию

активного листа или перемещение его в другую книгу можно сделать с помощью

команд меню Правка — Переместить/скопировать лист. Для этого в верхнем поле

окна Переместить или Скопировать нужно указать имя книги, куда перемещается

лист, затем выбрать лист, перед которым размещается копия, и поставить флажок

Создавать копию.

В Calc в контекстном меню (правая кнопка мыши на ярлыке листа)

выполните команду Вставить или Удалить для вставки или удаления листа,

либо используйте пункт меню Вставить> Лист. Для управления листами при

помощи контекстного меню, например, для переименования листа в

контекстном меню выберите пункт Переименовать.

Выделение частей таблицы

Для того, чтобы сделать ячейку активной, нужно щелкнуть по ней

указателем мыши. Для выделения группы ячеек следует воспользоваться

указателем мыши в сочетании с клавишами CTRL (можно выделить ячейки в

разных местах таблицы) и SHIFT (для выделения последовательно

расположенных ячеек).

Для выделения прямоугольного диапазона можно щелкнуть левой

кнопкой мыши в левом верхнем углу диапазона ячеек, затем, удерживая

нажатой клавишу SHIFT, щелкнуть в правом нижнем углу диапазона. Или

очертить диапазон ячеек указателем мыши.

Чтобы выделить столбец, нужно щелкнуть левой кнопкой мыши по

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

по номеру строки.

Чтобы выделить полностью активный лист, нужно щелкнуть левой

кнопкой мыши в левом верхнем углу листа на пересечении номеров строк и

заголовков столбцов таблицы.

Перемещение или копирование формулы

Формулы имеют ссылки на ячейки с данными. Ссылки делятся на:

абсолютные, относительные и комбинированные. Также в электронных

таблицах используется прием автозаполнения.

Форматирование таблиц

К параметрам формата листа относятся: высота строк, ширина столбцов,

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

Форматирование можно изменить шрифт, размер символов, стиль и цвет

содержимого выделенных ячеек.

Кнопки выравнивания панели инструментов Форматирование служат для

выравнивания текста по горизонтали. Чтобы разместить данные другим образом,

например, расположить заголовки столбцов под углом, выбираем Формат —

Ячейки — Выравнивание.

Для отображения данных в полном виде без обрезания в меню панели

инструментов выбираем Формат — Ячейки — Выравнивание, где указываем

Переносить по словам или Автоподбор ширины.

Объединение ячеек для ввода текста

При создании заголовков столбцов, так называемой «шапки» таблицы, часто

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

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

использовании кнопки Объединить и поместить в центре, расположенной на панели

инструментов Форматирование. Предварительно нужно ввести в одну ячейку текст,

затем выделить объединяемые ячейки, включая ячейку с введенным текстом, затем

нажать на эту кнопку. Другой способ для объединения — Формат — Ячейки —

вкладка Выравнивание — поставить флажок Объединение ячеек. Чтобы отказаться от

объединения ячеек, нужно сделать активной зону объединенных ячеек и, вызвав тот

же пункт меню, снять флажок Объединение ячеек.

В объединенную ячейку помещаются только данные верхнего левого угла

из выделенного диапазона. Для включения в объединенную ячейку всех данных

диапазона необходимо скопировать все данные в верхнюю левую ячейку внутри

диапазона.

Использование рамок в ячейках

Для облегчения восприятия таблиц, в табличных процессорах возможно

обрамление как одной, так и группы ячеек различными видами рамок. Для

установки вида рамки используется меню Формат – Ячейки – Граница, где

указываются необходимые параметры.

10.4. Средства анализа данных

Анализ данных с помощью диаграмм

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

данных в виде диаграммы. Диаграммы связаны с данными листа, на основе

которых они были созданы, и изменяются каждый раз, когда изменяются данные

на листе.

Можно создать либо внедренную диаграмму, либо отдельный лист

диаграммы. Внедренная диаграмма — это диаграмма, которая отображается

непосредственно на рабочем листе так, чтобы при сохранении или печати листа

вместе с ним сохранялась и печаталась соответствующая диаграмма. Обратите

внимание: диаграммы внедрять не обязательно — можно создать диаграмму на

отдельном (ее собственном) листе рабочей книги. Внедрение используется в

случае, если необходимо распечатать диаграмму вместе с соответствующими ей

данными, указанными на рабочем листе. Размещение диаграммы на отдельном

листе применяют, когда требуется распечатать диаграмму отдельно от листа.

Перечислим основные элементы диаграммы.

1. Панель инструментов диаграммы, которая появляется при создании

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

2. Подписи данных – это элемент диаграммы, с помощью которого

представлено одно значение в электронной таблице. Подписи данных одной

формы составляют ряд данных в диаграмме.

3. Ряд данных диаграммы – это группа связанных значений. В

диаграмме может содержаться как один ряд данных, так и несколько.

4. Формула ряда – это формула, описывающая текущий ряд данных. В

формуле располагается ссылка на ячейку с названием ряда, ссылки на ячейки

рабочего листа с категориями и значениями, используемыми в диаграмме, а

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

фактические данные для составления графика диаграммы. Ее можно

редактировать и таким образом управлять графическим порядком.

5. Оси – это линии, которые являются основой при составлении

графика в диаграмме. В двухмерных диаграммах используют две оси — X

(горизонтальная) и Y (вертикальная). В большинстве двухмерных диаграмм

(кроме гистограмм) вдоль оси X располагаются категории, а вдоль оси Y—

значения. В гистограммах все иначе: значения отображаются вдоль оси Y. В

круговых диаграммах оси вообще отсутствуют. В объемных диаграммах

используют ось X, ось Y и ось Z. Оси X и Y необходимы для определения

горизонтальной поверхности диаграммы. Ось Z является вертикальной осью

для представления глубины третьего измерения в диаграмме.

6. Метка деления – это небольшая черточка, пересекающая ось. Эта

метка деления необходима для определения шкалы категорий или ряда данных

диаграмм. Возле метки может находиться надпись.

7. Область построения диаграммы – это область, в которой строится

диаграмма, содержит оси и все подписи данных.

8. Линии сетки – это линии, проведенные от меток деления поперек

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

данных.

9. Текст диаграммы – это метка или заголовок, вводимые в диаграмму.

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

подписью данных или другим объектом диаграммы. При перемещении объекта

перемещается и прилагаемый текст.

10. Легенда – это активная зона, щелкнув на которой можно установить

образцы, цвета и символы, связанные с подписями данных диаграмм. В легенде

указано название ряда данных, соответствующего каждой подписи.

Для того, чтобы построить диаграмму необходимо выделить ячейки,

содержащие данные, нажать кнопку Мастер диаграмм и следовать его

инструкциям.

Для создания диаграмм из несмежных диапазонов нужно выделить первую

группу ячеек, содержащих необходимые данные, далее, удерживая клавишу CTRL,

выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер

диаграмм.

Для изменения цвета, линий или рамок на диаграмме необходимо

установить указатель мыши на изменяемый элемент диаграммы и дважды

щелкнуть по левой кнопке, затем выбрать вкладку Узор и указать необходимые

параметры. Для заливки можно выбрать соответствующую команду и указать

необходимые параметры на вкладках Градиентная, Текстура и Узор.

Функция Поиск решения

Поиск решения является инструментом оптимизации, с его помощью

можно найти оптимальное или заданное значение некоторой ячейки путем

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

условиям.

Изменяемые ячейки — это ячейки, от которых зависит значение целевой

ячейки. Целевая ячейка — это ячейка, для которой нужно найти максимальное,

минимальное или заданное значения. Она должна содержать формулу, зависящую

от изменяемых ячеек. Пока не будет найдено решение, программа подбирает

значения изменяемых ячеек.

Существует возможность определения результирующего значения при

необходимости изменения нескольких используемых в формуле ячеек, для

которых существуют несколько ограничений. Для получения нужного результата

формулы подбираются значения в указанных ячейках.

Процедуру поиска решения можно использовать, например, для

определения расходов на рекламу, обеспечивающих максимальную прибыль.

Поиск решения относится к надстройкам и может отсутствовать в меню.

Для его установки используется команда Сервис — Надстройки.

Чтобы вызвать Поиск решения, используется команда Сервис — Поиск

решения. В появившемся диалоговом окне необходимо сделать следующие

установки. В поле Установить целевую ячейку вводится адрес или имя ячейки, в

которой находится формула оптимизируемой модели.

Для максимизирования или минимизирования значения целевой ячейки

путем изменения значений изменяемых ячеек необходимо установить

соответствующий переключатель в положение максимальное или минимальное

значение соответственно. Если нужно получить значение в целевой ячейке, равное

некоторому числу, то переключатель необходимо установить в положение

Значение и ввести нужное число.

В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек через

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

Допускается установка до 200 изменяемых ячеек. В поле Ограничения вводятся

все ограничения, накладываемые на значения ячеек исходной таблицы. Кнопка

Выполнить инициирует процесс решения. Через некоторое время в диалоговом

окне появится сообщение о том, что решение найдено или нет.