Методические указания (стр. 3 )

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

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

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

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

Параметры диаграммы:

·  заголовки;

·  оси;

·  линии сетки;

·  легенда;

·  подписи данных;

·  таблица данных.

Заголовки содержат названия различных элементов диаграммы:

·  заголовок диаграммы;

·  название оси категорий (ось Х);

·  название оси значений (ось Y);

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

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

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

Область диаграммы – это вся диаграмма, вместе со всеми ее элементами.

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

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

·  выбор типа диаграммы;

·  выбор источника данных;

·  определение параметров диаграммы;

·  выбор размещения диаграммы.

Для создания диаграммы можно использовать специальное средство Мастер диаграмм или использовать установки EXCEL по умолчанию.

EXCEL обладает достаточно мощными средствами по настройке и редактированию диаграмм, такими как:

·  изменение диапазонов данных, добавление новых или удаление существующих;

·  выбор линий сетки;

·  определение размера и расположения легенды;

·  изменение места пересечения осей, корректировка масштаба осей;

·  добавление заголовков к осям и диаграмме, размещение текста в диаграмме и т. д.

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

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

Размещение диаграммы можно изменить при помощи инструмента Переместить диаграмму из группы Рас­положение на вкладке Конструктор выбирается одно из трех мест размещения диаграммы. Можно расположить диаграмму на том же листе, на котором находятся данные, на другом листе книги или создать для диаграммы отдельный лист в книге. Сделав выбор, щелкните на кнопке Готово.

Выполнение лабораторной работы

1.  Загрузите программу Excel 2007.

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

2. Загрузите файл lab1.xls с рабочей книгой, созданной в первой лабораторной работе.

3. Скопируйте таблицу «Лицевой счет» (диапазон ячеек A1:J9) в буфер обмена. (Главная, раздел Буфер обмена, копировать).

4. Перейдите на новый рабочий лист (Лист2), установите курсор в ячейку A1 и вставьте содержимое буфера обмена командой Специальная вставка… (Нажмите правую кнопку мыши). Если нет свободного листа, то можно при необходимости добавлять или удалять листы (включая, в частности, такие типы листов, как листы диаграмм, листы макросов и листы диалогов). Имя (или заголовок) листа отображается на его ярлычке в нижней части экрана. По умолчанию листам присваиваются имена «Лист1», «Лист2» и т. д., но имя любого листа на более подходящее.

Вставка нового листа

Чтобы вставить новый лист, выполните одно из следующих действий.

Чтобы быстро вставить новый лист после существующих листов, щелкните вкладку Вставить лист в нижней части экрана.

Чтобы вставить новый лист перед существующим листом, выберите этот лист, на вкладке Главная в группе Ячейки выберите пункт Вставить, а затем — команду Вставить лист.

 Совет. Можно также щелкнуть правой кнопкой мыши ярлычок одного из листов и выбрать команду Вставить. На вкладке Общие выделите значок Лист и нажмите кнопку ОК.

В диалоговом окне команды установите переключатель значения, нажмите кнопку ОК. Таблица «Лицевой счет» будет вставлена в рабочий лист, но при этом все формулы в ней будут заменены значениями.

5. Выделите несмежные диапазоны ячеек с фамилиями, а также с начисленными и удержанными суммами (B3:B9, H3:H9, I3:I9) для нанесения на диаграмму и нажмите кнопку инструментального меню Мастер диаграмм. (Вставка/Диаграмма).

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

На первом шаге работы мастера диаграмм выберите на вкладке тип диаграммы – гистограмма, вид диаграммы – Объёмная гистограмма, переходим в режим конструктора.

На втором шаге работы мастера:

·  В режиме конструктора Выбрать данные выберите ряды в столбцах;

·  на вкладке Ряд выберите из списка Ряд элемент Ряд 1 и введите «Начислено» в поле ввода Имя (аналогично введите имя второго ряда – «Удержано»); Можно щелкнуть по ячейке с надписью (названием столбца).

·  в качестве подписей по оси Х (подписи категорий) укажите диапазон ячеек с фамилиями ($B$3:$B$9);

На третьем шаге работы мастера:

·  на вкладке Макет введите название диаграммы – «Результаты расчетов З/П по предприятию за текущий месяц», название оси Х – «Фамилии работников» и название оси Z – «Сумма (руб.)»;

·  на вкладке Таблица данных установите параметр Таблица данных для вывода в табличной форме данных гистограммы;

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. Отредактируйте построенную диаграмму. Для этого:

·  используйте при построении диаграммы в качестве рядов на столбцы, а строки исходной таблицы. Такая диаграмма будет наглядно характеризовать динамику роста финансирования каждой социальной отрасли. Для этого выберите в режиме Конструктор… и на вкладке «Данные» включите переключатель «строка/столбец»

·  измените тип диаграммы, преобразовав диаграмму в объемную цилиндрическую. Для этого выполните команду Конструктор/Изменить тип диаграммы… и в списке типов выберите «Цилиндрическая диаграмма».

Построение линейчатой диаграммы с накоплением

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.  Организуйте консолидируемые данные на каждом отдельном листе.

§  Необходимо, чтобы все диапазоны данных были представлены в формате списка (Список. Набор строк, содержащий взаимосвязанные данные, или набор строк, которому назначаются функции листа с помощью команды Создать список.): первая строка каждого столбца содержит название, остальные строки — однотипные данные; пустые строки или столбцы в списке отсутствуют.

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

§  Убедитесь, что макеты всех диапазонов совпадают.

§  Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена нажмите кнопку Имя диапазона и введите имя диапазона в поле Имя.

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

3.  На вкладке Данные в группе Средства обработки данных выберите команду Консолидация.

4.  Выберите в раскрывающемся списке Функция итоговую функцию (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), которую требуется использовать для консолидации данных.

5.  Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор.

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

6.  Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.

7.  Выберите способ обновления консолидации. Выполните одно из следующих действий.

§  Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными.

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

9.  Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.

10.  Оставьте пустыми все поля в группе Использовать в качестве имен. Названия исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать названия в консолидированные данные, сделайте это вручную.

Консолидация по категории

§  Организуйте консолидируемые данные на каждом отдельном листе.

§  Необходимо, чтобы все диапазоны данных были представлены в формате списка (Список. Набор строк, содержащий взаимосвязанные данные, или набор строк, которому назначаются функции листа с помощью команды Создать список.): первая строка каждого столбца содержит название, остальные строки — однотипные данные; пустые строки или столбцы в списке отсутствуют.

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

§  Убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации.

§  Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена щелкните стрелку, расположенную рядом с полем Присвоить имя и введите имя диапазона в поле Имя.

§  Щелкните левый верхний угол области основного листа, в которой требуется разместить консолидированные данные.

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

§  На вкладке Данные в группе Средства обработки данных выберите команду Консолидация.

§  Выберите в раскрывающемся списке Функция итоговую функцию (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), которую требуется использовать для консолидации данных.

§  Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор.

§  Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.

§  Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.

§  Выберите способ обновления консолидации. Выполните одно из следующих действий.

§  Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными.

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

§  Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.

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

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

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

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

Содержание лабораторной работы

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

Выполнение лабораторной работы

1. Загрузите программу Excel 2007.

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. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода и последующего копирования формулы.