Название предприятия I кв. II кв. Ill кв. IV кв.

Предприятие А 236 452 123 526

Предприятие В 184 250 340 480

В качестве примера рассмотрим приведенные в табл. 13.6 данные выпуска продукции (например, станков) по кварталам года двумя предприятиями А и В. Пример простой, плоской диаграммы, которая построена по расположенным в строках этой таблицы числовым данным, приведен на рис. 13.13. Диаграмма, изображенная на этом рисунке, имеет заголовок Выпуск продукции в 1966 году. По оси категорий отложены кварталы года (ось называется Кварталы года), а по оси значений — количество выпущенных этими предприятиями станков (эта ось называется Выпуск станков (в штуках)). Для лучшей ориентации на графике имеется координатная сетка. График выпуска продукции отдельным предприятием образует ряд. Справа внизу в рамке расположена легенда, из которой следует, линией какого цвета изображен ряд, построенный по данным предприятия А, и какого — предприятия В. Причем для отметки точек первого графика используются маркеры в виде ромбиков, а для отметки точек второго графика — маркеры в виде квадратиков.

На рисунке 13.14 приведена построенная по тем же данным объемная диаграмма. Роль маркеров в этой диаграмме играют объемные столбики — параллелепипеды. Создание и включение каждой из этих диаграмм в рабочий лист требует от пользователя выполнения одной и той же очень простой последовательности действий. Для построения диаграммы по находящимся в строках или столбцах какой-либо таблицы числовым данным нужно:

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

2. нажать кнопку Мастер диаграмм панели инструментов Стандартная или выполнить команду Вставка > Диаграмма...;

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

Рис. 13.13.Пример плоской диаграммы

Рис. 13.14.Пример объемной диаграммы

Выполнение первых двух шагов указанной последовательности действий приведет к вызову мастера построения диаграмм. На рисунке 13.15 изображено диалоговое окно первого шага мастера. Это окно имеет две страницы. На первой странице в списке Тип: находятся названия 14 стандартных типов диаграмм, а на второй странице этот же список содержит названия 20 нестандартных типов диаграмм, которые могут быть построены мастером. Справа в окне находится область Вид:, которая содержит образцы имеющихся разновидностей диаграмм данного типа. Выбранный образец выделяется инверсным цветом. Ниже расположено текстовое описание выбранного варианта построения диаграммы. На рисунке 13.15 изображена ситуация, в которой выбрано построение диаграммы типа Гистограмма в разновидности Объемный вариант обычной гистограммы (второй ряд, первый слева образец). Посмотреть, как будет выглядеть диаграмма выбранного образца, можно с помощью нажатия кнопки Просмотр результата. Если пользователя устраивает предложенный мастером вариант, он может завершить работу по построению диаграммы, нажав кнопку Готово. Если требуется уточнить какие-либо детали оформления диаграммы: снабдить ее заголовком, названиями координатных осей и т. д., то следует нажать кнопку Далее> и перейти к следующему шагу построения диаграммы.

Рис. 13.15.Диалоговое окно первого шага мастера диаграмм

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

Рис. 13.16.Страница Диапазон данных окна второго шага мастера диаграмм

Программа MS Excel автоматически формирует ряды данных по строкам или по столбцам, исходя из того, что на диаграмме должно быть меньше рядов данных, чем числовых значений в каждом ряду. Если, например, столбцов выделено больше, чем строк, то ряды будут формироваться по содержимому ячеек в строках. С помощью переключателей Ряды в: рассматриваемой страницы окна пользователь может изменить предложенный программой MS Excel выбор. Если для данных из табл. 13.6 включить переключатель столбцах, то это будет означать, что на графике рис. 13.13 должны разместиться четыре ряда по два значения в каждом ряду. Очевидно, что для заявленной цели — проследить ход выпуска продукции двумя разными предприятиями (два ряда) по кварталам года (четыре значения) — должен быть включен переключатель строках так, как это показано на рис. 13.16.

На странице Ряд окна второго шага размещены элементы управления рядами строящейся диаграммы (рис. 13.17). С помощью кнопок Добавить и Удалить можно изменить первоначально определенное количество рядов на диаграмме. Для удаления достаточно в списке Ряд выделить строку, соответствующую удаляемому ряду диаграммы, и нажать кнопку Удалить. Для добавления еще одного ряда вначале следует нажать кнопку Добавить, а затем в поля Имя:, Значения: и Подписи оси X: окна ввести адреса или диапазоны адресов ячеек таблицы, которые содержат соответственно название ряда, образующие ряд числовые значения и подписи в разметке оси категорий. Адреса и диапазоны адресов можно вводить с клавиатуры. Кроме того, с помощью кнопки , которая расположена в правом конце каждого поля ввода, можно временно перейти к исходной таблице и в ней выделить соответствующие ячейки или их диапазоны. С помощью этих же элементов управления можно изменить автоматически определяемую информацию о первоначально выделенных рядах.

Рис. 13.17.Страница Ряд окна второго шага мастера диаграмм

Заметим, что во время анализа таблицы мастер построения диаграмм рассматривает данные, расположенные в ячейках первой выделенной строки, как названия отметок координатных линий оси категорий. Сравните содержимое первой строки табл. 13.6 с подписями под горизонтальной осью рис. 13.13. Если таблица, для которой строится диаграмма, действительно содержит данные, которые можно рассматривать как названия отметок для координатных линий, то эту строку рекомендуется включать в выделенный фрагмент. Аналогичным образом первый столбец рассматривается мастером построения диаграмм как названия для рядов и легенды. Сравните данные в ячейках первого столбца табл. 13.6 с текстом легенды на рис. 13.13 и 13.14, а также названиями рядов на рис. 13.14. Если данные в ячейках первого столбца таблицы, для которой строится диаграмма, действительно могут играть роль обозначений рядов, то этот столбец следует включать в выделенный фрагмент. В этом случае заботиться об изменении данных в окне второго шага мастера диаграмм не придется.

Работа с окном второго шага также завершается нажатием кнопки Далее>, в результате чего на экране появляется многостраничное окно третьего шага мастера построения диаграмм (рис. 13.18). Это окно содержит шесть страниц. На странице Заголовки находятся поля, в которых можно задать названия всей диаграммы и каждой из ее осей. На странице Оси находятся флажки, с помощью которых включается или выключается режим вывода разметки осей категорий и значений. На странице Линии сетки находятся флажки, управляющие режимом вывода координатной сетки раздельно по каждой из осей диаграммы. На странице Подписи данных можно выбрать способ указания на диаграмме числовых значений, соответствующих маркерам рядов. Поскольку вывод значений загромождает область построения диаграммы, по умолчанию числовые значения в область диаграммы не выводятся. На странице Таблица данных можно включить флажок одновременного вывода как самой диаграммы, так и исходной таблицы числовых данных. И наконец, на странице Легенда находятся флажок и переключатели, управляющие выводом и положением легенды в области диаграммы.

Рис. 13.18. Диалоговое окно третьего шага мастера диаграмм

В последнем, четвертом, окне мастера построения диаграмм (рис. 13.19) определяется местоположение диаграммы. Ее можно поместить на отдельном листе, включив переключатель отдельном:. Тогда для размещения диаграммы в рабочую книгу будет добавлена отдельная страница, и в находящемся рядом с переключателем поле нужно задать ее название. Мастер диаграмм предлагает название Диаграмма 1, которое пользователь может поменять на любое другое. Кроме того, с помощью переключателя имеющемся: диаграмму можно разместить на любом существующем листе, выбрав его название в находящемся справа списке. Завершается процесс создания диаграммы нажатием на копку Готово. Последнее диалоговое окно мастера закрывается, а на экране остается рабочий лист с включенной в него готовой диаграммой. Пользователю следует самым тщательным образом проанализировать полученную диаграмму и еще раз проверить числовые данные, по которым она построена.

Рис. 13.19.Диалоговое окно четвертого шага мастера построения диаграмм

СОВЕТ

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

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

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

Базы данных в MS Excel

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

Программа MS Excel располагает средствами для работы с простыми базами данных, которые могут быть реализованы с помощью обычной (плоской, двумерной) таблицы. Примером такой простой базы данных может служить телефонный справочник. Основной отличительной особенностью таблицы, которая рассматривается как база данных, являются ее большие размеры. В соответствии с терминологией, принятой в теории баз данных, всю таблицу, содержащую данные, будем называть списком или базой данных. Столбцы таблицы будем называть полями, а строки — записями.

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

Вводить данные можно непосредственно в таблицу MS Excel, однако удобнее воспользоваться так называемой формой, которая представляет собой диалоговое окно, содержащее поля ввода, соответствующие столбцам таблицы. Пример формы для табл. 13.6 изображен на рис. 13.20. Для открытия окна формы нужно выделить всю таблицу, в том числе и строку, содержащую названия полей, а затем выполнить команду Данные > Форма.... В выведенном на экран окне формы с помощью кнопок Далее и Назад можно перемещаться между существующими записями и вносить в них изменения, редактируя содержимое соответствующих полей формы. С помощью кнопки Добавить можно включить новую запись (строку) в таблицу, а с помощью кнопки Удалить — исключить из нее любую запись.

С помощью формы можно обратиться к механизмам поиска записей, удовлетворяющих некоторым простым условиям. В качестве условия может выступать искомый набор символов — образец поиска. Пусть, например, нужно найти запись с конкретным названием предприятия. В этом случае образец поиска может выглядеть, скажем, так: Предприятие 675. В образцах поиска могут использоваться символы подстановки ? и *. Напоминаем, что символу ? соответствует любой одиночный символ поля записи, а символу * — любая последовательность любых символов. Например, образцу поиска «д?м» соответствуют слова «дым» и «дом», но не соответствует слово «другом». А образцу поиска «*ино» соответствуют названия «Люблино» и «Выхино».

Рис. 13.20.Пример формы

Условия можно накладывать на числовые значения, даты, время и т. д. Например, если нужно найти предприятия, которые за первый квартал выпустили более трехсот единиц продукции, то в этом случае условие будет иметь вид: >300. В таких условиях можно использовать операции сравнения <, <=, >, >=, =, <>, которые применяются к значениям числовых полей.

Поиск организуется следующим образом. В форме есть кнопка Критерии, после нажатия которой все поля формы станут пустыми, а на месте кнопки Критерии появится кнопка Правка. В поля формы, по которым будет проводиться поиск, вводятся образцы поиска или условия. Так, для формы, изображенной на рис. 13.20, упомянутые выше образцы поиска должны вводиться в поля Название предприятия: и 1 квартал:. Одновременно можно заполнить несколько полей. Это означает, что нужно найти записи, которые одновременно удовлетворяют всем записанным в полях формы условиям. Другими словами, такие условия связаны операцией «логическое И» (конъюнкцией). После заполнения полей следует нажать кнопку Далее. Программа MS Excel отберет в списке все записи, удовлетворяющие поставленным условиям. Для перехода между выбранными записями можно использовать кнопки Назад и Далее.

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

С помощью форм легко осуществить поиск по критериям, в которых используется операция «логическое И», однако для более сложных условий поиска формы не подходят. В этом случае следует обратиться к команде Данные > фильтр > Автофильтр. В результате ее выполнения возле каждого поля появится кнопка раскрытия списка (рис. 31.21). В списке (на рис. 13.21 развернут список поля 1 квартал) содержатся все значения поля и пункт Условие..., позволяющий определить более сложный критерий. Выбор этого пункта вызывает диалоговое окно Пользовательский автофильтр (рис. 13.22), с помощью элементов управления которого можно задать достаточно сложное условие отбора записей.

Рис. 13.21. Выполнение команды Автофильтр

Рис. 13.22.Окно Пользовательский автофильтр

В приведенном на рис. 13.22 примере запрашивается поиск записей, в которых за первый квартал произведено больше чем 100 единиц продукции или меньше чем 50. Когда записи появятся на экране, их (не все, а только выбранные) можно распечатать, выполнив команду Файл >Печать.... По окончании работы автофильтр с помощью той же самой команды Данные > Фильтр > Автофильтр следует отключить.

Существует также возможность одновременного поиска по нескольким полям и поиска по вычисляемому критерию. Для этого служит команда Данные > Фильтр >Расширенный фильтр.... Прежде чем воспользоваться этой возможностью, необходимо сформировать таблицу диапазона условий, которая состоит, по крайней мере, из одной строки заголовков полей и одной строки условий. Эта таблица может содержать несколько одинаковых заголовков полей. Если в сложном условии используется операция «логическое И», то входящие в него простые условия записываются в одной и той же строке условий таблицы. Если применяется операция «логическое ИЛИ» (дизъюнкции), то простые условия следует записать друг под другом в одном и том же поле (столбце). Если, например, нужно отобрать предприятия, которые выпустили в первом квартале не меньше чем 100 и не больше чем 300 единиц продукции и при этом во втором квартале выпустили либо больше 200, либо меньше 500 единиц продукции, то таблица диапазона условий должна выглядеть следующим образом:

I кв. I кв. II кв.

>=100 <=300 >200

<500

ВНИМАНИЕ

В этой таблице дважды используется заголовок одного и того же поля. Это значит, что значение из этого поля используется в сложном условии несколько раз и участвует в операции «логическое И».

После завершения построения таблицы диапазона условий нужно выполнить команду Данные > Фильтр > Расширенный фильтр... и ввести адреса содержащих критерии поиска ячеек в поле Диапазон условий: диалогового окна команды. Поле Исходный диапазон, как правило, заполняется автоматически. С помощью группы переключателей Обработка отобранные данные можно поместить в новый диапазон ячеек (переключатель скопировать результат в другое место) или произвести отбор в исходном диапазоне ячеек (переключатель фильтровать на месте). Если отбор записей произведен в исходном диапазоне, вернуть на экран всю базу данных можно с помощью команды Данные > Фильтр > Отобразить все.

Для ускорения поиска информации существующую базу данных можно преобразовать, расположив данные в нужном порядке (например, по алфавиту, в порядке возрастания или убывания числовых значений и т. д.). Такое преобразование, состоящее в изменении порядка следования строк в таблице, принято называть сортировкой.Для выполнения сортировки нужно поместить курсор в любую ячейку базы данных и воспользоваться командой Данные > Сортировка.... В окне команды следует указать, по какому полю (столбцу) нужно выполнить сортировку и в каком (убывающем или возрастающем) порядке это нужно сделать. Можно отсортировать лишь часть записей — тогда их следует выделить. Чтобы строка, в которой содержатся названия полей, не участвовала в сортировке, нужно в списке переключателей Идентифицировать поля по включить вариант подписям (первая строка диапазона).

Часто данные, содержащиеся в списке, требуется представлять различными способами, подводя различные промежуточные итоги. Для этого можно использовать возможности команд Данные > Итоги... и Данные > Сводная таблица.... Выполнение команды Данные > Итоги... приводит к созданию таблицы специальной структуры, подобной вложенному списку, отдельные элементы которой могут быть ради удобства анализа скрыты или вновь показаны. Пусть, например, в таблице накапливаются сведения о ежедневных продажах, выпрлненных разными отделениями торговой организации. Чтобы с помощью команды Данные > Итоги... получить сведения о продажах по каждому отделению, в диалоговом окне команды в списке При каждом изменении в: окна команды следует выбрать заголовок поля, содержащего названия отделений. А если нужно подводить ежедневные итоги, то в этом списке следует выбрать заголовок поля, содержащего даты выполнения продаж. Далее, в списке Операция нужно выбрать способ подведения итогов. Это может быть количество продаж или сумма значений в каком-либо поле, например, в поле стоимости проданного товара. После чего в списке флажков Добавить итоги по: включить флажки, соответствующие тем полям списка, по которым следует сформировать промежуточные итоги. Чтобы вернуть таблице исходный вид, нужно вновь открыть окно команды Данные > Итоги... и нажать кнопку Убрать все.

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