Симонович С.В. (ред.) Информатика для юристов и экономистов
Подождите немного. Документ загружается.
первой строке или столбце А, то разрыв страницы задается только по одному
направлению.
13.9. ПРИМЕНЕНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ДЛЯ
ЭКОНОМИЧЕСКИХ И БУХГАЛТЕРСКИХ РАСЧЕТОВ
Построение диаграмм и графиков
В программе Excel термин диаграмма используется для обозначения всех
видов графического представления числовых данных. Построение
графического изображения производится на основе ряда данных. Так называют
группу ячеек с данными в пределах отдельной строки или столбца. На одной
диаграмме можно отображать несколько рядов данных.
Диаграмма представляет собой вставной объект, внедренный на один из
листов рабочей книги. Она может располагаться на том же листе, на котором
находятся данные, или на любом другом листе (часто для отображения
диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на
основе которых она построена, и при обновлении этих данных автоматически
меняет свой вид.
Для построения диаграммы обычно используют Мастер диаграмм,
запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели
инструментов. Часто удобно заранее выделить область, содержащую данные,
которые будут отображаться на диаграмме, но задать эту информацию можно и
в ходе работы мастера.
Выбор типа диаграммы
На первом этапе работы мастера выбирают форму диаграммы. Доступные
формы перечислены в списке Тип на вкладке Стандартные. Для выбранного
типа диаграммы справа имеется несколько вариантов представления данных
(палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке
Нестандартные отображается набор полностью сформированных типов
диаграмм с готовым форматированием. После задания формы диаграммы
следует щелкнуть на кнопке Далее.
Выбор данных
Второй этап работы мастера служит для выбора данных, по которым
будет строиться диаграмма (рис. 13.10). Если диапазон данных был выбран
заранее, то в области предварительного просмотра в верхней части окна
мастера появится приблизительное отображение будущей диаграммы. Если
данные образуют единый прямоугольный диапазон, то их удобно выбирать при
помощи вкладки Диапазон данных. Если данные не образуют единой группы,
то информацию для отрисовки отдельных рядов данных задают на вкладке Ряд.
Предварительное представление диаграммы автоматически обновляется при
изменении набора отображаемых данных.
501
Оформление диаграммы
Третий этап работы мастера (после щелчка на кнопке Далее) состоит в
выборе оформления диаграммы. На вкладках окна мастера задаются:
• название диаграммы, подписи осей (вкладка Заголовки);
• отображение и маркировка осей координат (вкладка Оси);
• отображение сетки линий, параллельных осям координат (вкладка
Линии сетки);
• описание построенных графиков (вкладка Легенда);
• отображение надписей, соответствующих отдельным элементам данных
на графике (вкладка Подписи данных);
• представление данных, использованных при построении графика, в виде
таблицы (вкладка Таблица данных).
В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсут ствовать.
Размещение диаграммы
На последнем этапе работы мастера (после щелчка на кнопке Далее)
указывается, следует ли использовать для размещения диаграммы новый
рабочий лист или один из имеющихся. Обычно этот выбор важен только для
последующей печати документа, содержащего диаграмму. После щелчка на
кнопке Готово диаграмма строится автоматически и вставляется на указанный
рабочий лист (рис. 13.11).
502
Рис. 13.11. Готовая диаграмма Excel
Редактирование диаграммы
Готовую диаграмму можно изменить. Она состоит из набора отдельных
элементов, таких, как сами графики (ряды данных), оси координат, заголовок
диаграммы, область построения и прочее. При щелчке на элементе диаграммы
он выделяется маркерами, а при наведении на него указателя мыши —
описывается всплывающей подсказкой. Открыть диалоговое окно для
форматирования элемента диаграммы можно через меню Формат (для
выделенного элемента) или через контекстное меню (команда Формат).
Различные вкладки открывшегося диалогового окна позволяют изменять
параметры отображения выбранного элемента данных.
Если требуется внести в диаграмму существенные изменения, следует
вновь воспользоваться мастером диаграмм. Для этого следует открыть рабочий
лист с диаграммой или выбрать диаграмму, внедренную в рабочий лист с
данными. Запустив мастер диаграмм, можно изменить текущие параметры,
которые рассматриваются в окнах мастера, как заданные по умолчанию.
Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она
расположена (Правка > Удалить лист), или выбрать диаграмму, внедренную в
503
рабочий лист с данными, и нажать клавишу DELETE.
13.10. ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ
КАК БАЗ ДАННЫХ
Обычно бвзы данных представляют собой набор взаимосвязанных
таблиц. Простейшие базы данных состоят из одной таблицы. В качестве такой
базы данных вполне можно использовать электронную таблицу Excel.
Программа Excel включает набор функций, позволяющих выполнять все
основные операции, присущие базам данных.
Информация в базе данных состоит из набора записей, каждая из которых
содержит один и тот же набор полей. Записи характеризуются порядковыми
номерами, а каждое поле имеет заголовок, описывающий его назначение.
Чтобы содержимое рабочего листа рассматривалось как база данных
Excel, необходимо придерживаться строгих правил. Каждому полю записи
соответствует один столбец рабочего листа. Столбцы базы данных должны
идти подряд, без промежутков между ними. В первой строке каждого столбца
(точнее, в той строке, с которой начинается база данных) должен быть указан
заголовок соответствующего поля. Заголовок поля должен занимать не более
одной ячейки. Содержимое ячейки заголовка должно быть уникально в
пределах рабочего листа.
Записи базы данных должны идти непосредственно ниже строки
заголовков. Пустые строки не допускаются. Вообще, пустая строка
рассматривается как признак окончания базы данных, то есть, записи должны
идти подряд, без промежутков между ними.
В базе данных, оформленной таким образом, возможно выполнение
большинства операций, характерных для баз данных. Все операции с базами
данных выполняются примерно одинаково. Сначала необходимо выбрать
любую ячейку в базе данных, а затем начать нужную операцию. При этом весь
диапазон записей базы данных выбирается автоматически.
Сортировка базы данных
Для сортировки базы данных используется команда Данные >
Сортировка (рис. 13.12). Диалоговое окно Сортировка диапазона служит для
выбора поля, по которому производится сортировка. Если заданы заголовки
полей, то в качестве обозначений используются именно они, если заголовки
отсутствуют, в качестве заголовков используются обозначения столбцов.
Первое из выбранных полей определяет режим первичной сортировки. Если
при совпадении значений в этом поле существуют дополнительные критерии,
определяющие порядок записей, то можно также задать поля для вторичной и
третичной сортировки. По любому из полей сортировка может выполняться в
порядке возрастания или убывания.
504
Фильтрация базы данных
Так как база данных может включать огромное число записей (в
программе Excel естественным пределом служит максимальное число строк
рабочего листа — 65536), не всегда требуется отображать все эти записи.
Выделение подмножества общего набора записей называется фильтрацией.
Наиболее простым способом фильтрации в программе Excel является
использование автофилътра.
Применение автофильтра. Включение режима фильтрации
осуществляется командой Данные > Фильтр * Автофильтр. При этом для
каждого поля базы данных автоматически создается набор стандартных
фильтров, доступных через раскрывающиеся списки. Раскрывающие кнопки
этих списков отображаются возле поля заголовка каждого столбца
По умолчанию используется вариант Все, указывающий, что записи базы
данных должны отображаться без фильтрации. Вариант Первые 10 позволяет
отобрать определенное число (или процент) записей по какому-либо критерию.
Вариант Условие позволяет задать специальное условие фильтрации. Кроме
того, имеется возможность отбора записей, имеющих в нужном поле
конкретное значение.
При применении фильтра записи, не попадающие в отобранное
подмножество, скрываются и не отображаются. Общее число записей базы
данных и число отобранных записей отображается в строке состояния
программы. Исходные номера отобранных записей сохраняются, но
отображаются голубым цветом. Точно таким же цветом изображается
заголовок поля (или нескольких полей), по которому была выполнена
фильтрация.
Отфильтрованная база данных может использоваться при печати
(печатаются только записи, относящиеся к выбранному подмножеству) и при
построении диаграмм (график строится на базе выбранных записей). В
505
последнем случае смена критериев фильтрации автоматически изменяет вид
диаграммы.
Расширенная фильтрация. Если требуется, чтобы диаграмма не
зависела от смены критериев фильтрации, необходимо создать копию
соответствующих записей. Создать такую копию можно только с помощью
расширенного фильтра. Расширенный фильтр оперирует с двумя базами
данных одинаковой структуры, то есть с одинаковым набором полей. Обычно,
для записи условий фильтрации создают отдельный рабочий лист.
Запись в базе данных условий может содержать пустые поля. Каждое
непустое поле задает определенное условие фильтрации. Для текстовых полей
требуется совпадение поля в базе данных с заданным здесь значением. Для
числовых полей можно также указывать знаки сравнения. Условия, заданные в
пределах одной записи, должны выполняться одновременно. Для условий,
заданных в рамках разных записей, достаточно выполнения хотя бы одного.
При выборе расширенной фильтрации командой Данные > Фильтр >
Расширенный фильтр можно выполнить фильтрацию на месте или извлечь
отфильтрованные записи и поместить их отдельно, на любой рабочий лист
любой открытой рабочей книги.
Анализ данных. Подробный анализ данных из таблицы базы данных
требует возможности получения сводной информации по записям базы данных.
Фактически, речь идет о совмещении средств фильтрации и итоговых
вычислений. Результаты такого анализа могут быть представлены в виде
сводной таблицы или сводной диаграммы. При создании сводной диаграммы
автоматически создается и сводная таблица, содержащая данные, на основе
которых строится диаграмма.
Построение сводной таблицы. Первоначальное построение сводной
таблицы производится с помощью Мастера сводной таблицы. Для этого служит
команда Данные > Сводная таблица. Первоначально, как обычно, требуется
выделить ячейку, относящуюся к базе данных.
На первом этапе работы мастера выбирается тип данных (для
использования базы данных Excel — это пункт В списке или базе данных
Microsoft Excel) и тип оформления сводных данных — Сводная таблица. Затем,
после щелчка на кнопке Далее, надо уточнить, правильно ли был выбран
диапазон, охватывающий базу данных. Если придерживаться правил
ограничения базы данных пустыми ячейками, то автоматический выбор
осуществляется безошибочно. После очередного щелчка на кнопке Далее
выбирается местоположение сводной таблицы. В большинстве случаев следует
размещать ее на новом рабочем листе (переключатель Новый лист).
Содержание сводной таблицы. Но одновременно с этим надо
сформировать содержание и оформление сводной таблицы. Для выбора
содержания надо щелкнуть на кнопке Макет. Сводная таблица состоит из
четырех областей: Страница, Строка, Столбец и Данные (рис. 13.14).
506
Рис. 13.14. Формирование макета сводной таблицы
Каждой области сводной таблицы должны соответствовать одно или
несколько полей базы данных. Исключение делается только для поля Страница,
которое можно оставить пустым. При заполнении сводной таблицы данные из
соответствующих полей берутся автоматически. Для заполнения этих полей
служат кнопки, названия которых совпадают с названиями полей базы данных.
Поместить кнопку в нужное поле можно методом перетаскивания. В сводной
таблице можно использовать только те поля, которые необходимы.
Незадействованные поля в отборе и вычислениях не участвуют.
Область Страница играет роль внешнего фильтра. Данные в остальной
части таблицы относятся к значению, выбранному в этом поле. Область Строка
содержит значения, определяющие содержимое строки таблицы, и задает
второй уровень сортировки. Аналогичным образом, область Столбец
определяет содержимое столбцов. В отличие от области Страница, в сводную
таблицу помещаются данные для всех возможных значений полей, указанных в
этих областях.
Область Данные определяет собственно содержимое таблицы. В отличие
от всех остальных областей, к данным, попадающим в ячейку таблицы,
применяется функция для итоговых вычислений (по умолчанию —
суммирование). Если необходимо изменить эту функцию, надо дважды
щелкнуть на соответствующей кнопке и выбрать нужную операцию из
раскрывающегося списка.
Кроме стандартного набора итоговых функций, можно использовать и
дополнительные вычисления. Для этого надо щелкнуть на кнопке
Дополнительно, выбрать нужное значение из раскрывающегося списка
Дополнительные вычисления и, если требуется, указать необходимые
507
параметры. После выбора и настройки данных следует щелкнуть на кнопке ОК.
Оформление сводной таблицы. Для настройки внешнего вида сводной
таблицы надо щелкнуть на кнопке Параметры. Здесь можно задать имя листа
сводной таблицы, включить режим суммирования данных по строкам и
столбцам, разрешить доступ к информации, использованной при вычислении,
выполнить другие настройки. По их завершении требуется щелкнуть на кнопке
ОК, а затем на кнопке Готово, и сводная таблица строится автоматически.
Работа со сводной таблицей
При создании сводной таблицы автоматически открывается и панель
инструментов Сводные таблицы. В дальнейшем открывать и закрывать эту
панель можно, щелкая правой кнопкой мыши на любой из открытых панелей
инструментов и выбирая пункт Сводные таблицы из контекстного меню (рис.
13.15).
Область
Страницы
Информация об
Области данных
Область строк
Панель
инструментов
Сводные таблицы
Названия полей
Рис. 13.15.
Работа со сводной таблицей
Если требуется изменить вид сводной таблицы, это можно сделать с
помощью кнопок, помещенных в таблице в соответствующих областях. При
щелчке на соответствующей раскрывающей кнопке появляется небольшое
окно, в котором можно отключить некоторые из отображаемых показателей.
Для области Страница справа отображается раскрывающийся список,
позволяющий выбрать разные условия фильтрации. Кроме того, щелкнув в
пределах сводной таблицы правой кнопкой мыши и выбрав в контекстном
меню пункт Отобразить страницы, можно разделить единую сводную таблицу
508
на набор таблиц, соответствующих разным значениям, которые можно выбрать
в области Страница.
Сводная таблица остается динамически связанной с данными, на
основании которых она была построена. Это, в частности, означает, что для
любого значения, приведенного в сводной таблице, можно выяснить, как оно
было получено. Для этого надо дважды щелкнуть на соответствующей ячейке
таблицы. При этом автоматически создается новый рабочий лист, содержащий
выборку записей, использованных при вычислении данного значения.
Динамическая связь с исходными данными проявляется и в том, что при
изменении данных не требуется заново формировать сводную таблицу.
Достаточно щелкнуть в пределах таблицы правой кнопкой мыши и выбрать в
контекстном меню пункт Обновить данные.
Впрочем, это не относится к часто встречающейся ситуации, когда записи
базы данных добавляются или удаляются. В этом случае необходимо снова
запустить мастер сводных таблиц (проще всего для этого воспользоваться
кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы),
щелкнуть на кнопке Назад и уточнить диапазон ячеек, включающих записи
базы данных. После щелчка на кнопке Готово данные сводной таблицы будут
обновлены с учетом изменения числа записей.
Построение сводных диаграмм
Сводная диаграмма представляет собой диаграмму, построенную на
основе сводной таблицы. При создании сводной диаграммы соответствующая
ей сводная таблица создается автоматически, даже если явного запроса на ее
создание не было. Для создания сводной диаграммы по уже существующей
сводной таблице проще всего щелкнуть на кнопке Мастер диаграмм на панели
инструментов Сводные таблицы. Чтобы создать сводную диаграмму
непосредственно на основе базы данных, надо запустить Мастер сводных
таблиц командой Данные > Сводная таблица и на первом этапе работы мастера
установить переключатель Сводная диаграмма. Далее проще всего сразу
щелкнуть на кнопке Готово.
Программа Excel автоматически создаст новые рабочие листы,
соответствующие создаваемой сводной таблице и сводной диаграмме, и
откроет лист с диаграммой. Реальной информации этот лист пока что не
содержит. Формирование содержимого диаграммы осуществляется точно так
же, как формирование сводной таблицы: перетаскиванием кнопок,
соответствующих полям базы данных, в нужные области диаграммы (рис.
13.16). Таких областей три.
• Область страницы располагается в верхней части диаграммы.
• Область данных совпадает с областью построения.
• Область категорий (включающая строки и столбцы промежуточной
сводной таблицы) располагается в нижней части диаграммы или слева от нее.
Кнопки полей, которые можно перетаскивать, в данном случае
располагаются не средствеино на панели инструментов Сводные таблицы.
Чтобы отменить исш. зование поля, его надо переместить из области
509
диаграммы обратно на панель.
Информация о полях базы данных отображается на диаграмме точно IVK
же в сводной таблице, — с раскрывающими кнопками. Используя их, ыоллю i
нить правила фильтрации или отключить отображение некоторых значении.
Дальнейшее форматирование и настройка диаграммы осуществляется
через контекстные меню. Так, чтобы изменить вид диаграммы, надо щелкнуть
правой кнопкой мыши на области построения и выбрать в контекстном меню
пункт Тип диаграммы. Для изменения формата щелкните на соответствующем
элементе диаграммы правой кнопкой мыши и выберите в контекстном меню
соответствующий пункт Формат... В целом, возможности управления
отображением сводных диаграмм несколько ниже, чем для обычных диаграмм,
что связано с более сложным внутренним механизмом их построения.
Рис. 13.16. Формирование сводной диаграммы
13.11. СРЕДСТВА КОНТРОЛЯ
Контроль рабочих листов
Если рабочие книги Excel используются для создания документации
(особенно в бухгалтерской деятельности), необходима гарантия, что
содержащиеся в них данные не содержат ошибок. Механический ввод данных
всегда чреват опечатками, особенно когда речь идет о числах. При вводе текста
510