При отображении данных на диаграммах Microsoft Excel выводит значения ячеек в качестве столбцов, линий, сегментов и других элементов диаграмм. При создании диаграммы числовые значения ячеек автоматически представляются в диаграммах. Например, в гистограмме мастер диаграмм для каждого значения из листа создает столбец определенной высоты. Для построения диаграмм необходимо, прежде всего, определить ряды и категории данных.
Excel позволяет создавать диаграммы 14 стандартных типов: гистограмма, линейчатая, график, круговая, точечная, с областями, кольцевая, лепестковая, поверхность, пузырьковая, биржевая, коническая, цилиндрическая, пирамидальная. Каждый тип может иметь несколько вариантов (подтипов). Кроме того, имеются 20 типов нестандартных диаграмм.
Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных Данные одного ряда для большинства типов диаграмм закрашиваются одним цветом. На круговой диаграмме отображается только один ряд данных, при этом сектора диаграммы окрашиваются разными цветами.
Категория данных понятие, взаимосвязанное с рядом данных. Если в качестве рядов данных выбраны столбцы таблицы, то категориями будут называться строки и наоборот. Обычно названия категорий располагаются вдоль оси Х.
Параметры диаграммы:
· заголовки;
· оси;
· линии сетки;
· легенда;
· подписи данных;
· таблица данных.
Заголовки содержат названия различных элементов диаграммы:
· заголовок диаграммы;
· название оси категорий (ось Х);
· название оси значений (ось Y);
Легенда – подпись, определяющая закраску или цвета рядов данных диаграммы. Легенда содержит ключи и соответствующие им названия рядов данных. Ключ легенды определяет цвет и узор, заданный для элементов определенного ряда.
Подписи данных – это значения (метки), проставленные рядом с точками данных, предоставляющие дополнительные сведения о точках данных, отображающих значения ячеек. Подписями данных могут быть снабжены как отдельные точки данных, так и весь ряд целиком. В зависимости от типа диаграммы подписи данных могут отображать значения, названия рядов и категорий, доли или их комбинации.
Таблица данных диаграммы – это таблица, размещенная на диаграмме, содержащая отображаемые на диаграмме данные. Каждая строка таблицы данных содержит ряд данных. Таблица данных обычно связана с осью категорий и заменяет подписи оси категорий.
Область диаграммы – это вся диаграмма, вместе со всеми ее элементами.
Область построения – это область, ограниченная осями и содержащая все ряды диаграммы. В трехмерной диаграмме это область, ограниченная осями и включающая ряды данных, названия категорий, подписи меток оси и названия осей.
Диаграммы строятся с помощью мастера диаграмм, который вызывается щелчком по кнопке инструментальной панели с соответствующим названием. Перед тем как строить диаграмму целесообразно выделить ячейки исходной таблицы, включая некоторые заголовки. Для построения диаграммы по несмежным областям эти области выделяются мышью при нажатой клавише «Ctrl». Процесс построения содержит 4 шага:
· выбор типа диаграммы;
· выбор источника данных;
· определение параметров диаграммы;
· выбор размещения диаграммы.
Для создания диаграммы можно использовать специальное средство Мастер диаграмм или использовать установки EXCEL по умолчанию.
EXCEL обладает достаточно мощными средствами по настройке и редактированию диаграмм, такими как:
· изменение диапазонов данных, добавление новых или удаление существующих;
· выбор линий сетки;
· определение размера и расположения легенды;
· изменение места пересечения осей, корректировка масштаба осей;
· добавление заголовков к осям и диаграмме, размещение текста в диаграмме и т. д.
Представление данных в виде диаграмм позволяет осуществить анализ этих данных, в том числе построить линии тренда, которые могут использоваться для анализа и прогнозирования данных. Подобный анализ называется также регрессионный анализ. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.
Существует шесть различных видов линий тренда (аппроксимация и сглаживание), которые могут быть добавлены в диаграмму Microsoft Excel. Использование линии тренда того или иного вида определяется типом данных.
Выполнение лабораторной работы
1. Загрузите программу Excel 2003.
Построение и редактирование гистограммы
2. Загрузите файл lab1.xls с рабочей книгой, созданной в первой лабораторной работе.
3. Скопируйте таблицу «Лицевой счет» (диапазон ячеек A1:J9) в буфер обмена.
4. Перейдите на новый рабочий лист (Лист2), установите курсор в ячейку A1 и вставьте содержимое буфера обмена командой Правка/Специальная вставка… В диалоговом окне команды установите переключатель значения, нажмите кнопку ОК. Таблица «Лицевой счет» будет вставлена в рабочий лист, но при этом все формулы в ней будут заменены значениями.
5. Выделите несмежные диапазоны ячеек с фамилиями, а также с начисленными и удержанными суммами (B3:B9, H3:H9, I3:I9) для нанесения на диаграмму и нажмите кнопку инструментального меню Мастер диаграмм.
6. Постройте трехмерную гистограмму для сравнительного анализа начисленных и удержанных сумм всех работников предприятия. Для этого выполните следующее.
На первом шаге работы мастера диаграмм выберите на вкладке Стандартные тип диаграммы – гистограмма, вид диаграммы – трехмерная гистограмма с раскладкой значений по категориям и рядам данных. Нажмите кнопку Далее.
На втором шаге работы мастера:
· на вкладке Диапазон данных выберите ряды в столбцах;
· на вкладке Ряд выберите из списка Ряд элемент Ряд 1 и введите «Начислено» в поле ввода Имя (аналогично введите имя второго ряда – «Удержано»);
· в качестве подписей по оси Х (подписи категорий) укажите диапазон ячеек с фамилиями ($B$3:$B$9);
· нажмите кнопку Далее.
На третьем шаге работы мастера:
· на вкладке Заголовки введите название диаграммы – «Результаты расчетов З/П по предприятию за текущий месяц», название оси Х – «Фамилии работников» и название оси Z – «Сумма (руб.)»;
· на вкладке Подписи данных в группе Подписи значений выберите переключатель нет;
· на вкладке Легенда отмените параметр Добавить легенду;
· на вкладке Таблица данных установите параметр Таблица данных для вывода в табличной форме данных гистограммы;
· нажмите кнопку Далее.
На четвертом шаге работы мастера выберите в группе Поместить диаграмму на листе переключатель на отдельном. Нажмите кнопку Готово.
7. Отредактируйте диаграмму, расположенную на листе Диаграмма 1, используя кнопки панели инструментов Диаграммы.
7.1. Установите новый фон области диаграммы. Для этого:
· нажмите кнопку инструментального меню Элементы диаграммы и выберите в списке элемент Область диаграммы;
· нажмите кнопку инструментального меню Формат области диаграммы;
· на вкладке Вид в окне команды в группе Рамка выберите переключатель Обычная, тип и толщину линии (толстая линия);
· на вкладке Вид в группе Заливка нажмите кнопку Способы заливки;
· в окне Способы заливки выберите вкладку Градиентная, а в группе Цвета – переключатель Заготовка;
· в появившемся списке Название заготовки выберите тот элемент, который понравился (например, Рассвет);
· в группе Тип штриховки выберите переключатель От центра и вариант фона;
· два раза щелкните по кнопке OK.
7.2. Выполните форматирование заголовка диаграммы, изменив начертание, размер и цвет символов шрифта. Для этого:
· нажмите кнопку инструментального меню Элементы диаграммы и выберите в списке элемент Заголовок диаграммы;
· нажмите кнопку инструментального меню Формат заголовка диаграммы;
· в окне команды на вкладке Шрифт выберите из списка шрифт – Arial Black, начертание – обычный, размер символов – 20, подчеркивание – одинарное по значению, цвет символов – коричневый;
· щелкните по кнопке OK.
7.3. Выполните форматирование заголовков оси Х (ось категорий) и оси Z (ось значений). Повторите действия предыдущего пункта для элементов Название оси категорий и Заголовок оси значений, установив шрифт Arial Black, начертание – обычный, размер символов – 16, цвет – темно-красный.
7.4. Выполните форматирование оси категорий, оси значений и оси ряда данных, установив красный цвет для символов.
7.5. Измените фон стен и угол разворота диаграммы. Для этого:
· щелкните правой клавишей мыши в области стены диаграммы между линиями сетки;
· из контекстного меню выберите команду Формат стенок;
· на вкладке Вид в группе Заливка нажмите кнопку Способы заливки;
· в окне Заливка выберите на вкладке Текстура вариант фона, например, «белый мрамор»;
· 2 раза щелкните по кнопке OK;
· повторно щелкните правой клавишей мыши в области стены диаграммы между линиями сетки;
· из контекстного меню выберите команду Объемный вид;
· в окне Формат трехмерной проекции с помощью кнопок Увеличить и Уменьшить установите угол возвышения, угол поворота вокруг оси Z и значение параметра Перспектива;
· щелкните по кнопке Применить и, не закрывая окна команды, проанализируйте результат разворота. Если результат устраивает, щелкните по кнопке OK. В противном случае повторите действия, начиная с предыдущего шага.
7.6. Измените на диаграмме расположение рядов, расположив первым ряд «Начислено». Для этого:
· щелкните правой клавишей мыши по ряду данных «Начислено»;
· выберите из контекстного меню команду Формат рядов данных;
· на вкладке Порядок рядов для выбранного ряда «Начислено» нажмите кнопку Вниз;
· на вкладке Вид выберите ярко синий цвет и любой из градиентных способов заливки для фигуры ряда данных «Начислено»;
· два раза щелкните по кнопке OK.
7.7. Увеличьте размер символов в таблице данных. Для этого щелкните правой клавишей мыши по таблице данных и выберите из меню команду Формат таблицы данных. На вкладке Шрифт установите размер 14.
8. Выполните предварительный просмотр листа с диаграммой перед печатью. Для этого щелкните по кнопке инструментального меню Предварительный просмотр. Для выхода из команды щелкните по кнопке Закрыть.
9. Самостоятельно постройте объемный вариант круговой диаграммы на основании данных столбцов «Фамилия» и «З/П к выдаче» таблицы «Лицевой счет» и расположите ее на том же рабочем листе. Установите следующие параметры диаграммы:
· заголовок диаграммы – «Распределение заработной платы по работникам предприятия»;
· подписи данных – категория и доля значений в общем объеме.
Выполните форматирование всех элементов диаграммы.
10. Введите на новый лист рабочей книги следующую таблицу:
Проект отраслевого бюджета на 1998 – 2000 г. г.
Отрасли | Проектные годы | ||
1998 г. | 1999 г. | 2000 г. | |
Культура | 100 | 120 | 140 |
Образование | 800 | 890 | 1 020 |
Здравоохранение | 1 500 | 1 600 | 1 700 |
Туризм и спорт | 80 | 90 | 100 |
Постройте гистограмму, отражающую динамику изменения объемов финансирования каждой отрасли по годам. Введите название диаграммы – «Динамика изменения объемов финансирования по отраслям» и заголовки осей: Х – «Отрасли социальной сферы» и Y – «В млрд. рублей». Разместите гистограмму на отдельном листе рабочей книги.
11. Отредактируйте построенную диаграмму. Для этого:
· исключите из диаграммы ряд, соответствующий 1999 г. Для этого выберите команду Диаграмма/Исходные данные… и на вкладке Ряд выберите ряд «1999 г.» и нажмите кнопку Удалить;
· добавьте в диаграмму новый ряд для 2001 г. Для этого добавьте соответствующий ряд в исходную таблицу, выберите команду Диаграмма/Исходные данные… и на вкладке Ряд нажмите кнопку Добавить. Сошлитесь на добавленный ряд исходной таблицы;
· используйте при построении диаграммы в качестве рядов на столбцы, а строки исходной таблицы. Такая диаграмма будет наглядно характеризовать динамику роста финансирования каждой социальной отрасли. Для этого выберите команду Диаграмма/Исходные данные… и на вкладке «Диапазон данных» включите переключатель «Ряды в строках»
· измените место размещения диаграммы. Для этого выберите команду Диаграмма/Размещение… Разместите диаграмму на листе с исходной таблицей;
· измените тип диаграммы, преобразовав диаграмму в объемную цилиндрическую. Для этого выполните команду Диаграмма/Тип диаграммы… и в списке типов выберите «Цилиндрическая диаграмма».
Построение линейчатой диаграммы с накоплением
12. Постройте сравнительную гистограмму распределения мужчин и женщин по возрастным группам в соответствии с прилагаемой таблицей.
Для этого выполните следующие действия:
· вызовите мастер диаграмм и выберите тип диаграммы - линейчатая с накоплением;
· задайте диапазон ячеек для построения диаграммы;
· вместо легенды используйте текстовые поля с надписями «Женщины» и «Мужчины»;
· разместите диаграмму в имеющемся рабочем листе;
· для размещения меток делений с левой стороны вызовите формат оси категорий и на вкладке «Вид» установите переключатель «Метки делений внизу»;
· для перевода отрицательных процентов в положительные на оси значений создайте и примените специальный пользовательский формат: 0%;0%;0%. Для этого вызовите формат оси значений, на вкладке «Число» выберите в списке числовых форматов «Все форматы» и в поле ввода «Тип» введите нужный.
Построение пузырьковой диаграммы
13. Самостоятельно постройте пузырьковую диаграмму, характеризующую зависимость суммы продаж товара от затрат на его рекламу в течение 12 месяцев по данным приведенной таблицы.
Построение лепестковой диаграммы
 |
14. Самостоятельно постройте заполненную лепестковую диаграмму, характеризующую сезонность продаж туристских путевок в течение года по данным приведенной таблицы.
Построение диаграммы Ганта
15. Постройте простую диаграмму Ганта, отображающую во времени начала работ проекта и их продолжительность. Исходные данные содержатся в приведенной ниже таблице.
Для построения диаграммы выполните следующие действия:
· вызовите мастер диаграмм и на шаге 1 выберите линейчатую диаграмму с накоплением;
· на шаге 2 выберите ряды в столбцах. Укажите диапазон ячеек для ряда 1 – ячейки с датами начала работ. Установите вкладку Ряд и добавьте ряд 2, содержащий данные о продолжительности работ. Укажите диапазон ячеек для подписей оси Х – ячейки с наименованиями работ.
· на шаге 3 введите название диаграммы;
· на шаге 4 внедрите диаграмму на текущий лист;
· измените формат оси категорий (ось Х): установите на вкладке Шкала обратный порядок категорий и пересечение с осью Х в максимальной категории;
· выделите ряд 1 и в окне «Формат ряда данных» сделайте границу ряда невидимой, а заливку прозрачной;
· измените минимальное значение шкалы по оси значений, введя дату 01.02.98;
· Отредактируйте размеры шрифтов отдельных элементов и размеры области диаграммы.
Трендовый анализ
16. Перейдите на новый лист Рабочей книги Excel.
17. Введите приведенную ниже таблицу.
Динамика спроса | |
Месяц | Спрос (шт.) |
Январь | 44 |
Февраль | 50 |
Март | 55 |
Апрель | 70 |
Май | 68 |
Июнь | 62 |
Июль | 63 |
18. Для этой таблицы сначала постройте линейный график.
19. Постройте линию тренда. Для этого:
· выделите ряд данных для построения тренда, щелкнув по линии графика;
· выберите команду Добавить линию тренда в меню Диаграмма;
· на вкладке Тип выберите вид линии тренда: полиномиальная 4-й степени;
· на вкладке Параметры установите: «прогноз вперед на 1 период» и «поместить на диаграмму величину достоверности аппроксимации»;
· нажмите кнопку ОК;
· щелкните по линии тренда правой клавишей мыши и из контекстного меню выберите Формат рядов данных;
· на вкладке Вид установите цвет – красный;
· нажмите кнопку ОК.
20. Сохраните рабочую книгу на дискете в файле с именем lab2.xls .
21. Для выхода из Excel выберить из меню команду Файл/Выход.
ЛАБОРАТОРНАЯ РАБОТА № 3
Структурирование, консолидация данных,
построение сводных таблиц и диаграмм
Цель лабораторной работы
Лабораторная работа служит для получения практических навыков по изучению следующих тем:
· манипулирование данными, расположенными на разных листах рабочей книги;
· списки и операции со списками (фильтрация, сортировка);
· использование диалоговых окон для изменения информации в списках;
· структура таблицы (создание и удаление);
· формирование таблиц с общими и частными итогами;
· консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
· построение сводных таблиц и сводных диаграмм.
Основные сведения о списках, структуре рабочего
листа, консолидации и сводных таблицах
Список – это упорядоченный набор данных, база данных на рабочем листе.
Столбцы списка называются полями, строки – записями.
Ведение списка можно осуществлять в диалоговом окне (форме).
Над списками можно выполнять такие операции, как фильтрация и сортировка.
В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или трех столбцов.
Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).
Структура таблицы позволяет скрыть или отобразить уровни детализации простым нажатием кнопки мыши. Структура наиболее полезна для создания итоговых отчетов, в которых не нужно приводить все детали. Структура может иметь до восьми уровней вложения.
Простейший способ получения итоговой информации – использования механизма подведения общих и промежуточных итогов. Перед подведением итогов таблицу следует упорядочить в соответствии со значением того поля, по которому будут подводиться итоги. При подведении итогов можно использовать различные математические и статистические функции. При использовании команды подведения итогов создается структура, позволяющая управлять уровнем детализации выводимых данных.
Консолидация – это объединение данных из двух или более рабочих листов и вывод их в выбранный для хранения консолидируемых данных диапазон ячеек. При консолидации данных могут использоваться различные функции: суммирования, расчета среднего арифметического, подсчетов максимальных и минимальных значений и т. п. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.
Сводная таблица – это динамический итог данных, содержащихся в базе данных. Сводная таблица позволяет создавать настраиваемую таблицу для организации полей на листе в новых сочетаниях. Создается сводная таблица только при помощи специального средства Мастер сводных таблиц и диаграмм, позволяющего также создавать сводные диаграммы, наглядно представляющие информацию сводных таблиц.
Содержание лабораторной работы
Перед вами стоит задача совместной обработки нескольких таблиц-списков, расположенных на разных листах рабочей книги.
Выполнение лабораторной работы
1. Загрузите программу Excel 2003.
2. На листе рабочей книги (Лист1) создайте табл.1 с исходными данными о выпуске учебников:
Таблица 1
Номер | Название | Автор | Цена | Тираж | Сумма | Квартал |
1 | Математика | Воробьев | 10000 | 800000 | Кв1 | |
2 | Физика | Воробьев | 20000 | 1700000 | Кв2 | |
3 | История | Щеглов | 10000 | 750000 | Кв1 | |
4 | Литература | Щеглов | 10000 | 900000 | Кв3 | |
5 | Химия | Семенов | 100000 | 8300000 | Кв3 | |
6 | Биология | Семенов | 20000 | 1560000 | Кв1 | |
7 | Философия | Грачев | 10000 | 920000 | Кв1 | |
8 | Экономика | Грачев | 10000 | 900000 | Кв3 | |
9 | Информатика | Воробьев | 10000 | 1200000 | Кв3 | |
10 | Психология | Грачев | 15000 | 1350000 | Кв4 |
3. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода и последующего копирования формулы.
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 |
