Симонович С.В. (ред.) Информатика для юристов и экономистов

Подождите немного. Документ загружается.

Оформление диаграммы

Третий этап работы мастера (после щелчка на кнопке Далее) состоит в

выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заголовки);

• отображение и маркировка осей координат (вкладка Оси);

• отображение сетки линий, параллельных осям координат (вкладка Ли-

нии сетки);

• описание построенных графиков (вкладка Легенда);

• отображение надписей, соответствующих отдельным элементам данных

на графике (вкладка Подписи данных);

• представление данных, использованных при построении графика, в виде

таблицы (вкладка Таблица данных).

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

Размещение диаграммы

На последнем этапе работы мастера (после щелчка на кнопке Далее) ука-

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

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

щей печати документа, содержащего диаграмму. После щелчка на кнопке Гото-

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

(рис. 13.11).

491

Рис. 13.11. Готовая диаграмма Excel

Редактирование диаграммы

Готовую диаграмму можно изменить. Она состоит из набора отдельных

элементов, таких, как сами графики (ряды данных), оси координат, заголовок

диаграммы, область построения и прочее. При щелчке на элементе диаграммы

он выделяется маркерами, а при наведении на него указателя мыши — описы-

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

ния элемента диаграммы можно через меню Формат (для выделенного элемен-

та) или через контекстное меню (команда Формат). Различные вкладки открыв-

шегося диалогового окна позволяют изменять параметры отображения выбран-

ного элемента данных.

Если требуется внести в диаграмму существенные изменения, следует

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

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

ными. Запустив мастер диаграмм, можно изменить текущие параметры, кото-

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

Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она

расположена (Правка > Удалить лист), или выбрать диаграмму, внедренную в

492

рабочий лист с данными, и нажать клавишу DELETE.

13.10. ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ

КАК БАЗ ДАННЫХ

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

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

базы данных вполне можно использовать электронную таблицу Excel. Програм-

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

рации, присущие базам данных.

Информация в базе данных состоит из набора записей, каждая из которых

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

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

Чтобы содержимое рабочего листа рассматривалось как база данных Ex-

cel, необходимо придерживаться строгих правил. Каждому полю записи соот-

ветствует один столбец рабочего листа. Столбцы базы данных должны идти

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

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

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

одной ячейки. Содержимое ячейки заголовка должно быть уникально в преде-

лах рабочего листа.

Записи базы данных должны идти непосредственно ниже строки заголов-

ков. Пустые строки не допускаются. Вообще, пустая строка рассматривается

как признак окончания базы данных, то есть, записи должны идти подряд, без

промежутков между ними.

В базе данных, оформленной таким образом, возможно выполнение

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

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

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

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

Сортировка базы данных

Для сортировки базы данных используется команда Данные > Сортиров-

ка (рис. 13.12). Диалоговое окно Сортировка диапазона служит для выбора

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

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

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

полей определяет режим первичной сортировки. Если при совпадении значений

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

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

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

убывания.

493

Фильтрация базы данных

Так как база данных может включать огромное число записей (в програм-

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

листа — 65536), не всегда требуется отображать все эти записи. Выделение

подмножества общего набора записей называется фильтрацией. Наиболее про-

стым способом фильтрации в программе Excel является использование авто-

филътра.

Применение автофильтра. Включение режима фильтрации осуще-

ствляется командой Данные > Фильтр * Автофильтр. При этом для каждого

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

ступных через раскрывающиеся списки. Раскрывающие кнопки этих списков

отображаются возле поля заголовка каждого столбца

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

данных должны отображаться без фильтрации. Вариант Первые 10 позволяет

отобрать определенное число (или процент) записей по какому-либо критерию.

Вариант Условие позволяет задать специальное условие фильтрации. Кроме

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

ное значение.

При применении фильтра записи, не попадающие в отобранное подмно-

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

число отобранных записей отображается в строке состояния программы. Ис-

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

цветом. Точно таким же цветом изображается заголовок поля (или нескольких

полей), по которому была выполнена фильтрация.

Отфильтрованная база данных может использоваться при печати (печата-

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

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

чае смена критериев фильтрации автоматически изменяет вид диаграммы.

494

Расширенная фильтрация. Если требуется, чтобы диаграмма не зависе-

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

щих записей. Создать такую копию можно только с помощью расширенного

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

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

фильтрации создают отдельный рабочий лист.

Запись в базе данных условий может содержать пустые поля. Каждое не-

пустое поле задает определенное условие фильтрации. Для текстовых полей

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

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

пределах одной записи, должны выполняться одновременно. Для условий, за-

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

При выборе расширенной фильтрации командой Данные > Фильтр >

Расширенный фильтр можно выполнить фильтрацию на месте или извлечь от-

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

открытой рабочей книги.

Анализ данных. Подробный анализ данных из таблицы базы данных тре-

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

Фактически, речь идет о совмещении средств фильтрации и итоговых вычисле-

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

блицы или сводной диаграммы. При создании сводной диаграммы автоматиче-

ски создается и сводная таблица, содержащая данные, на основе которых

строится диаграмма.

Построение сводной таблицы. Первоначальное построение сводной та-

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

команда Данные > Сводная таблица. Первоначально, как обычно, требуется вы-

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

На первом этапе работы мастера выбирается тип данных (для использова-

ния базы данных Excel — это пункт В списке или базе данных Microsoft Excel)

и тип оформления сводных данных — Сводная таблица. Затем, после щелчка на

кнопке Далее, надо уточнить, правильно ли был выбран диапазон, охватываю-

щий базу данных. Если придерживаться правил ограничения базы данных пу-

стыми ячейками, то автоматический выбор осуществляется безошибочно. По-

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

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

(переключатель Новый лист).

Содержание сводной таблицы. Но одновременно с этим надо сфор-

мировать содержание и оформление сводной таблицы. Для выбора содержания

надо щелкнуть на кнопке Макет. Сводная таблица состоит из четырех областей:

Страница, Строка, Столбец и Данные (рис. 13.14).

495

Рис. 13.14. Формирование макета сводной таблицы

Каждой области сводной таблицы должны соответствовать одно или

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

которое можно оставить пустым. При заполнении сводной таблицы данные из

соответствующих полей берутся автоматически. Для заполнения этих полей

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

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

таблице можно использовать только те поля, которые необходимы. Незадей-

ствованные поля в отборе и вычислениях не участвуют.

Область Страница играет роль внешнего фильтра. Данные в остальной

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

содержит значения, определяющие содержимое строки таблицы, и задает вто-

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

содержимое столбцов. В отличие от области Страница, в сводную таблицу по-

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

стях.

Область Данные определяет собственно содержимое таблицы. В отличие

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

няется функция для итоговых вычислений (по умолчанию — суммирование).

Если необходимо изменить эту функцию, надо дважды щелкнуть на соответ-

ствующей кнопке и выбрать нужную операцию из раскрывающегося списка.

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

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

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

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

стройки данных следует щелкнуть на кнопке ОК.

496

Оформление сводной таблицы. Для настройки внешнего вида сводной

таблицы надо щелкнуть на кнопке Параметры. Здесь можно задать имя листа

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

цам, разрешить доступ к информации, использованной при вычислении, выпол-

нить другие настройки. По их завершении требуется щелкнуть на кнопке ОК, а

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

Работа со сводной таблицей

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

инструментов Сводные таблицы. В дальнейшем открывать и закрывать эту па-

нель можно, щелкая правой кнопкой мыши на любой из открытых панелей

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

13.15).

Область

Страницы

Информация об

Области данных

Область строк

Панель инструмен-

тов

Сводные таблицы

Названия полей

Рис. 13.15. Ра-

бота со сводной таблицей

Если требуется изменить вид сводной таблицы, это можно сделать с по-

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

щелчке на соответствующей раскрывающей кнопке появляется небольшое

окно, в котором можно отключить некоторые из отображаемых показателей.

Для области Страница справа отображается раскрывающийся список, позволя-

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

сводной таблицы правой кнопкой мыши и выбрав в контекстном меню пункт

Отобразить страницы, можно разделить единую сводную таблицу на набор та-

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

497

Страница.

Сводная таблица остается динамически связанной с данными, на основа-

нии которых она была построена. Это, в частности, означает, что для любого

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

лучено. Для этого надо дважды щелкнуть на соответствующей ячейке таблицы.

При этом автоматически создается новый рабочий лист, содержащий выборку

записей, использованных при вычислении данного значения.

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

изменении данных не требуется заново формировать сводную таблицу. Доста-

точно щелкнуть в пределах таблицы правой кнопкой мыши и выбрать в

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

Впрочем, это не относится к часто встречающейся ситуации, когда записи

базы данных добавляются или удаляются. В этом случае необходимо снова

запустить мастер сводных таблиц (проще всего для этого воспользоваться

кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы),

щелкнуть на кнопке Назад и уточнить диапазон ячеек, включающих записи

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

обновлены с учетом изменения числа записей.

Построение сводных диаграмм

Сводная диаграмма представляет собой диаграмму, построенную на осно-

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

сводная таблица создается автоматически, даже если явного запроса на ее со-

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

ной таблице проще всего щелкнуть на кнопке Мастер диаграмм на панели

инструментов Сводные таблицы. Чтобы создать сводную диаграмму непосред-

ственно на основе базы данных, надо запустить Мастер сводных таблиц ко-

мандой Данные > Сводная таблица и на первом этапе работы мастера устано-

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

кнопке Готово.

Программа Excel автоматически создаст новые рабочие листы, соответ-

ствующие создаваемой сводной таблице и сводной диаграмме, и откроет лист с

диаграммой. Реальной информации этот лист пока что не содержит. Формиро-

вание содержимого диаграммы осуществляется точно так же, как формирова-

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

данных, в нужные области диаграммы (рис. 13.16). Таких областей три.

• Область страницы располагается в верхней части диаграммы.

• Область данных совпадает с областью построения.

• Область категорий (включающая строки и столбцы промежуточной

сводной таблицы) располагается в нижней части диаграммы или слева от нее.

Кнопки полей, которые можно перетаскивать, в данном случае располага-

ются не средствеино на панели инструментов Сводные таблицы. Чтобы отме-

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

панель.

Информация о полях базы данных отображается на диаграмме точно IVK

498

же в сводной таблице, — с раскрывающими кнопками. Используя их, ыоллю i

нить правила фильтрации или отключить отображение некоторых значении.

Дальнейшее форматирование и настройка диаграммы осуществляется че-

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

правой кнопкой мыши на области построения и выбрать в контекстном меню

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

элементе диаграммы правой кнопкой мыши и выберите в контекстном меню со-

ответствующий пункт Формат... В целом, возможности управления отображе-

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

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

Рис. 13.16. Формирование сводной диаграммы

13.11. СРЕДСТВА КОНТРОЛЯ

Контроль рабочих листов

Если рабочие книги Excel используются для создания документации (осо-

бенно в бухгалтерской деятельности), необходима гарантия, что содержащиеся

в них данные не содержат ошибок. Механический ввод данных всегда чреват

опечатками, особенно когда речь идет о числах. При вводе текста в качестве

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

кается на «неправильное» слово, у большинства людей автоматически срабаты-

499

вает внутренний сигнал. При вводе числовых данных определить «на глаз»,

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

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

ком ошибок.

Контроль логических ошибок

Ошибки в формулах можно разделить на две категории. Если в результа-

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

ка ошибок не помогут. В таком случае необходимо, чтобы вмешался специа-

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

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

При наличии ошибок другой категории нарушается логика работы про-

граммы. В этом случае сама программа Excel способна помочь в их поиске и

исправлении. Эти ошибки также можно разделить на две группы: невычисляе-

мые формулы и циклические ссылки.

Неверные формулы. Если получить значение в результате вычисления

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

значения ячейки код ошибки. Возможные коды ошибок и причины их появле-

ния приведены в таблице 13.2.

Циклические ссылки. При возникновении ошибок другой категории —

циклических ссылок — программа Excel выдает сообщение об ошибке не-

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

циклическая ссылка делает невозможным вычисление данных в одной или

нескольких ячейках.

Если формула при вычислении использует значения, располагающиеся в

других ячейках, говорят, что она зависит от них. Соответствующая ячейка на-

зывается зависимой. Наоборот, используемая ячейка влияет на значение форму-

лы и поэтому называется влияющей.

Таблица 13.2.

Стандартные сообщения программы об ошибках

КОД ОШИБКИ ПРИЧИНА ВОЗНИКНОВЕНИЯ

##### (СИМВОЛЫ

заполняют ячейку

целиком)

Числовые данные не помещаются в ячейку по ширине или же при вы-

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

#ДЕЛ/0! В знаменателе формулы получается нулевое значение или стоит ссылка

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

пользованием относительной ссылки

#ЗНАЧ! У функции пропущен обязательный параметр или же в качестве пара-

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

которых не соответствует типу нужного параметра

#ИМЯ? Опечатка в имени функции или имени ячейки (диапазона). Может быть

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

указании текстового параметра

500