Билет № 18

1. Технология обработки информации в электронных таблицах (ЭТ). Структура электронной таблицы. Типы данных: числа, формулы, текст. Правила записи формул. Основные встроенные функции. Абсолютные и относительные ссылки. Графическое представление данных.

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

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

На сегодняшний день самыми мощными программами, предназначенными для работы с электронными таблицами, являются Microsoft Excel и OpenOffice.org Calc.

Окно OpenOffice.org Calc 2.2

Окно Microsoft Excel 2007

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

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

При запуске программы ЭТ открывается окно, содержащее новую рабочую книгу. Рабочая книга - это многостраничный документ ЭТ, каждая страница которого называется рабочим листом. В окне документа отображается активная страница. По умолчанию книга содержит три рабочих листа с именами Лист1, Лист2 и ЛистЗ. Пользователь может вставить в книгу дополнительные листы (максимально возможное их количество в Excel - 255). На левом конце горизонтальной полосы прокрутки находятся ярлычки листов и кнопки прокрутки для их просмотра.

Таблицы в программе ЭТ создаются на рабочем листе. Рабочий лист разделен на строки и столбцы, которые, пересекаясь, образуют ячейки. В ячейки вводится содержимое таблицы. По умолчанию строки нумеруются, а столбцы обозначаются одной или двумя латинскими буквами.

Каждая ячейка имеет собственный адрес, он определяется именем столбца и номером строки, которым принадлежит ячейка. Например, ячейка в левом верхнем углу рабочего листа с содержащимся в ней текстом «Наименование» имеет адрес А1. Хотя создается впечатление, что часть текста переходит в ячейку В1 следующего столбца, на самом деле весь текст расположен в ячейке А1.

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

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

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

Если при вводе данных произошла ошибка, щелкните в строке формул на кнопке Отмена с изображением крестика красного цвета. Отменяется ввод и посредством нажатия клавиши [Esc] на клавиатуре. Нажмите кнопку Ввод (на ней изображена галочка зеленого цвета), чтобы закончить ввод данных, — кнопки строки формул исчезнут. Для завершения ввода и перехода к другой ячейке воспользуйтесь клавишей [Enter]. В случае, когда введенные числовые значения не помещаются в ячейку из-за недостаточной ширины столбца, вместо них на экране отображается несколько символов диеза (#).

Существует множество приемов создания формул. К примеру, в рабочем листе требуется умножить цену на количество и определить стоимость товара. Чтобы получить в ячейке С2 размер определить стоимость товара, следует активизировать эту ячейку, ввести знак равенства, а затем — адреса ячеек A2 и B3, соединенных знаком умножения (*). Введенная в ячейку формула должна иметь следующий вид: = A2* B3

Ввод формулы завершается нажатием клавиши [Enter], после чего в ячейке появляется результат вычислений. Однако фактическим содержимым ячейки по-прежнему будет формула, которая отображается в строке формул при активизации ячейки, а также в режиме редактирования содержимого данной ячейки (установить этот режим позволяет функциональная клавиша [F2]).

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

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

При любом способе создания формул для контроля ввода можно пользоваться строкой формул, которая активизируется нажатием клавиши [=].

Формула должна начинаться со знака равенства и может включать в себя числа, имена ячеек, функции (Математические, Статистические, Финансовые, Дата и время и т.д.) и знаки математических: операций. Например, формула «=А1+В2» обеспечивает сложение чисел, хранящихся в ячейках А1 и В2, а формула «=А1*5» — умножение числа, хранящегося в ячейке А1, на 5. При изменении исходных значений, входящих в формулу, результат пересчитывается немедленно.

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

Относительные и абсолютные ссылки

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

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

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

Абсолютные ссылки в исходной формуле можно задавать вводя с клавиатуры перед соответствующими адресами строк и столбцов символ $. Однако если вы не собираетесь заполнять формулу или копировать ее в другие ячейки, в указании абсолютных ссылок нет никакой необходимости. Кроме того, можно задавать абсолютные ссылки в Excel, пользуясь клавишей [F4]. Клавиша [F4] действует как переключатель. При первом нажатии она преобразует относительную ссылку в абсолютную, при втором в смешанную типа A$1, при третьем – в смешанную типа $A1, а при четвертом нажатии смешанная ссылка вновь будет преобразована в относительную. Если вам нужно, чтобы в результате копирования формулы изменялся один компонент адреса, используйте смешанные ссылки.

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

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

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

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

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

Круговая диаграмма Круговая диаграмма представляет собой круг, разбитый на несколько секторов, каждый из которых соответствует определенному значению, входящему в суммарный показатель. При этом сумма всех значений принимается за 100 %. Например, для наглядного отображения того, какой товар пользуется наибольшим спросом можно построить следующую диаграмму. Исходные данные для этих диаграмм извлекаются из диапазонов А3: А7 и D3: D7.

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

Гистограмма и линейная диаграмма

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

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

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

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