Задание 3. Цель. Получение навыков создания и редактирования графиков и диаграмм.

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

Порядок выполнения.

1. Прочитать текст на страницах 25 - 30.

2. В папке Группа_N где N - номер вашей группы создать папку с именем ЕЗ. Скопировать в нее файл 00000_371\Inform\Excel \Ex_3.xls

3. Запустить табличный процессор Excel и открыть в нем рабочую книгу Ех 3.xls.

4. Построить на листе ТАБЛИЦА в диапазоне А20: Н35 обычную плоскую гистограмму с легендой, отражающую общее количество преступлений в районах города N за два последних года. Для диаграммы, осей X и Yиспользовать соответственно следующие названия - «Зарегистрированные преступления», «Районы» и «Количество». Подписи оси X повернуть на угол 90 градусов. Для шрифта области диаграммы установить цвет красный и размер 8 пунктов. Залить область диаграммы светло-зеленым цветом, а область построения диаграммы - светло-желтым цветом.

5. Построить на отдельном листе «Статистика» диаграмму с легендой. Название диаграммы - «Статистика преступлений». Тип диаграммы и другие е параметры выбрать самостоятельно. Построенная диаграмма должна содержать необходимые сведения, быть аналитичной и наглядной.

6. Построить на листе КОПИЯ2 в диапазоне А11: Н24 плоскую линейчатую диаграмму с легендой, отражающую удельный вес краж в общем количестве преступлений в районах города N за два последних года (итоговые данные по городу не отображать). Перед построением диаграммы скорректировать формулы в диапазоне D5: D9, для этого выделить ячейку D5, удалить из формулы множитель 100 и знак умножения, установить для ячейки процентный формат и при помощи маркера заполнения скопировать формулу в ячейки D6: D9. При построении диаграммы задать расположение рядов в строках таблицы. Для диаграммы и оси Y использовать соответственно следующие заголовки «Удельный вес краж» и «Доля краж». Для шрифта области диаграммы установить размер 10 пунктов.

7. Вставить в книгу новый лисит с названием «Преступления в РФ» и создать в левом верхнем углу листа следующую таблицу.

Преступления в РФ

8. Построить ниже таблицы в диапазоне В6: М22 график с маркерами отражающий динамику преступлений в РФ с 1995 по 2004 годы. Для диаграммы и оси Y использовать соответственно следующие заголовки «Зарегистрировано преступлений в РФ» и «Количество». Для области диаграммы задать шрифт размером 8 пунктов. Затем изменить форматирование заголовка диаграммы (установить шрифт размером 12 пунктов голубого цвета), заголовка и подписей оси Y (установить шрифт красного цвета). Подписи оси X повернуть на угол 90 градусов. Область диаграммы залить светло-желтым цветом, для заливки области построения диаграммы использовать текстуру «Водяные капли». Установить размер маркеров на графике - 8 пунктов, для верхнего и нижнего рядов графика использовать тонкие линии, соответственно синего и фиолетового цвета.

9.Сохранить рабочую книгу в своей папке под именем ЗаданиеЗ.

Обработка данных

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

Закрепление областей

При просмотре данных таблиц, содержащих большое количество строк и (или) столбцов, заголовки таблиц и сами данные могут не помещаться в область экрана. Для устранения этого недостатка области таблицы следует закрепить, что позволяет при просмотре областей списка одновременно видеть на экране часть его заголовка и расположенные слева столбцы. С целью закрепления областей вызовите команду Окно/Закрепить области. При этом закрепляются ячейки выше и левее ячейки, в которой стояла курсорная рамка. Пример одновременного отображения различных областей таблицы приведен на рисунке 20. Курсорная рамка стояла в ячейке D1.

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

Рисунок 20. Лист с закрепленными областями

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

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

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

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

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

Применение автофильтра

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

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

Рисунок 21. Выбор условия для фильтрации.

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

• Все - вывод всех записей. Используется для отмены условий автофильтра в выбранном поле;

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

• Условие... - вывод на экран диалогового окна Пользовательский автофильтр (рис. 22) для задания сложных условий выборки с использованием 12 различных операторов («больше», «меньше или равно», «начинается с», «содержит» и т.д.). Пользовательский автофильтр позволяет установить один или два критерия выборки объединенных логическими операциями И или ИЛИ.

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

С помощью автофильтра можно задать условия выборки для нескольких полей списка, в этом случае фильтрация производится по объединенному критерию (условия соединяются оператором И), т.е. выводимая информация будет удовлетворять всем установленным условиям.

Использование расширенного фильтра

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

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

Рисунок 23. Расширенный автофильтр.

Чтобы снова вывести все записи следует в меню Данные - Фильтр выбрать пункт Отобразить все.

Формы данных

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

Рисунок 24. Форма данных.

Работа со списком выполняется с помощью следующих кнопок формы:

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

Удалить - удаляет отображаемую в форме запись (при этом остальные записи списка сдвигаются, заполняя освободившееся место).

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

Назад - выводит предыдущую запись списка, а если задан критерий отбора (кнопка Критерии), то - предыдущую запись из тех, которые удовлетворяют заданному критерию.

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

Критерии - очищает поля формы и переводит ее в режим ввода критериев отбора записей.

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

Очистить - удаляет существующие критерии, эта кнопка появляется после нажатия кнопки Критерии.

Закрыть - закрывает форму данных.

Критериями отбора для текстовых полей формы могут быть символьные константы, например, имя Иван для поля ИМЯ или название города для поля ГОРОД. В Excel критерий отбора для текстового поля можно вводить не только целым словом, но и частью слова с символами подстановки (* - заменяет любое число символов в слове, а? - один символ в слове). Если поиск проводится по первым буквам слова, символы подстановки можно не использовать. Например, одна буква, введенная в качестве критерия в текстовое поле формы, рассматривается как начальная буква слова в соответствующем поле (без учета регистра).

При установке критериев для числовых полей можно использовать соответствующие константы (например, числа или даты) и выражения со следующими операторами сравнения:

• = - равно;

• < > - не равно;

• < - меньше;

• > - больше;

• < = - меньше или равно;

• > = - больше или равно.

Итоги

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

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

Команда Данные-Итоги вставляет в список строки промежуточных (для групп) и общих (для всего списка) итогов, причем для подведения итогов можно использовать различные итоговые функции:

Сумма - суммирует числа в группе и в столбце;

Кол - во значений - определяет количество непустых ячеек в группе;

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

Максимум - определяет наибольшее значение в группе и в столбце;

Минимум - определяет наименьшее значение в группе и в столбце;

Произведение - вычисляет произведение всех значений в группе и в столбце;

Количество чисел - вычисляет количество ячеек с числами в группе ив столбце;

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

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

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

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

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

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

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

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

Мастер сводных таблиц создает таблицу за четыре шага:

1. Задание типа источника данных.

2. Указание местонахождения исходных данных.

3. Задание макета таблицы и выбор итоговой функции.

4. Указание места для размещения таблицы.

На первом шаге, после запуска Мастера сводных таблиц на экран выводится следующее диалоговое окно (рис. 27).

Рисунок 27. Создание сводной таблицы. Задание типа источника данных.

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

Рисунок 28. Указание местонахождения исходных данных.

На втором шаге нужно указать, где расположены исходные данные для сводной таблицы. Если источником данных является список Excel, то на этом шаге на экран выводится диалоговое окно, представленное на рисунке 28. Причем в этом окне уже будет указан адрес списка данных, если перед запуском мастера была выделена одна ячейка этого списка. Для указания другого диапазона нужно щелкнуть мышью по кнопке в строке Диапазон (при этом окно свернется в строку) и выделить требуемый диапазон, обязательно включив в него заголовки полей списка. Для перехода к следующему шагу создания сводной таблицы нужно восстановить окно, повторно щелкнув мышью по кнопке в строке Диапазон, и нажать в нем кнопку Далее.

Рисунок 29. Задание макета таблицы и выбор итоговой функции

На третьем шаге на экран выводится диалоговое окно для задания макета сводной таблицы (работа с макетом выводится на экран кнопкой Макет). Условное изображение макета сводной таблицы (рис. 29) расположено в центре этого окна и содержит три основных области Столбец, Строка и Данные. Область Страница используется для создания многомерной сводной таблицы, все страницы которой размещаются на одном листе. Справа от макета таблицы расположены кнопки с названиями полей списка данных (диапазон данных с этими полями был задан на втором шаге). Кнопки используются для формирования структуры сводной таблицы путем их перетаскивания мышью в различные области макета таблицы (в одну область можно перетащить сразу несколько кнопок). По умолчанию Мастер сводных таблиц суммирует числовые значения в области данных, поэтому там отображается надпись «Сумма по полю Всего» (для нечисловых значений мастер вычисляет их количество). Для смены итоговой функции нужно дважды щелкнуть мышью по новому имени поля в области данных, в появившемся на экране окне (рис. 30) выбрать необходимую операцию (на рисунке выбрана операция Среднее) и нажать на кнопку ОК.

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

На четвертом шаге работы Мастера сводных таблиц на экран выводится окно (рис. 31), в котором нужно указать место размещения сводной таблицы. Предпочтительным вариантом является выбор нового листа для сводной таблицы.

Рисунок 30. Смена итоговой функции.

Рисунок 31. Указание места для размещения таблицы.

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

Результат работы Мастера сводных таблиц (первый вариант сводной таблицы) выводится на экран после нажатия кнопки Готово. Пример сводной таблицы представлен на рис. 32.

Рисунок 32. Пример сводной таблицы.

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

Реорганизация сводной таблицы выполняется для упрощения работы с данными, для получения более детальной информации или конкретного среза данных для просмотра и анализа. Для фильтрации информации удобно использовать значения полей в области страниц. Например, если в таблице на рисунке 32 перетащить поле район в область страниц (левый верхний угол листа), то сводная таблица примет вид, показанный на рисунке 33. В такой компактной таблице можно просматривать данные списка по отдельным районам города (верхнее ниспадающее меню, открывается кнопкой ).

Рисунок 33. Реорганизованная сводная таблица.

Консолидация данных

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

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

Наиболее распространены следующие способы консолидации данных:

• с помощью формул со ссылками на исходные диапазоны;

• по расположению (для данных одинаковым расположением и структурой);

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

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

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

Рисунок 34. Диалоговое окно Консолидация данных.

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

Рисунок 35. Исходные таблицы для консолидации.

Таблицы имеют однотипные данные, но разное число строк и столбцов. Для консолидации таких данных по категориям, в отличие от консолидации по расположению, при выделении исходных данных нужно выделять также заголовки строк и столбцов с этими данными, а в области Использовать в качестве имен диалогового окна Консолидация (рис. 34) установить оба переключателя. Таблица консолидации данных приведена на рисунке 36. При этом ячейки для заполнения которых не хватает данных остаются не заполненными.

Создание колонтитулов

Выводимые на печать отчеты часто требуется снабдить дополнительной информацией, например, пронумеровать страницы, указать дату вывода документа и имя его автора. Такую информацию можно внести в колонтитулы. Управление их содержимым и параметрами осуществляется при помощи вкладки Вид/Колонтитулы (рис. 37).

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

Рисунок 37. Диалоговое окно Параметры страницы, вкладка Колонтитулы.

Рисунок 38. Диалоговое окно Bерхний колонтитул

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