Задание 3. Форматирование диаграммы
1. Измените настройку объемного вида трехмерной диаграммы (рис. 3).
2. Измените настройку области диаграммы и области построения диаграммы.
3. Измените форму представления данных на диаграмме: рядов данных и их элементов.
4. Измените отображение осей диаграммы.
5. Проведите форматирование сетки в области построения диаграммы.
6. На созданной диаграмме поместите новую легенду и проведите ее форматирование.
Технология работы
1. Измените настройки параметров диаграммы:
· активизируйте внедренную диаграмму, щелкнув правой кнопкой мыши в пустой области диаграммы;
· в появившемся меню выберите команду Объемный вид и в диалоговом окне установите следующие параметры:
| Возвышение: 15 Поворот: 20 Изометрия: флажок Автомасштаб: флажок |
· нажмите кнопку ОК.
2. Проведите форматирование области диаграммы и области построения диаграммы:
· активизируйте внедренную диаграмму и в появившемся меню выберите команду Формат области диаграммы: задайте на вкладках диалогового окна установки:
| Вкладка Вид: Рамка - невидимая с тенью Заливка: голубой цвет Вкладка Шрифт: Шрифт Times New Roman Cyr Стиль: обычный Размер: 14 |
· нажмите кнопку ОК;
· в области построения диаграммы вызовите контекстное меню, а в нем команду Формат области построения;
· задайте в диалоговом окне Вид установки:
| Рамка: автоматическая Заливка: белый цвет |
· нажмите кнопку ОК.
|
|
|
3. Проведите форматирование рядов данных и их элементов:
· установите указатель мыши на ряде 1, вызовите контекстное меню и выполните команду Формат рядов данных;
· на вкладке Параметры произведите настройку:
| Глубина зазора: 200 Ширина зазора: 170 Глубина диаграммы: 90 |
· на вкладке Порядок данных в окне установите курсор на название Информатика и щелкните по кнопке Вверх. Закройте окно;
· на вкладке Подписи данных установите переключатель Значения;
· на вкладке Вид установите параметры:
| Рамка: автоматическая Заливка: синий цвет |
· нажмите клавишу ОК;
· повторите установку параметров на вкладке Вид для остальных рядов диаграммы: для ряда 2 - желтый, для ряда 3 - зеленый.
4. Проведите форматирование осей диаграммы:
· на оси X вызовите контекстное меню, выполните команду Формат оси и установите параметры на вкладках:
| Вкладка Вид: Метки делений - внизу, основные - наружу Вкладка Шкала: Число категорий между подписями делений - 1, число категорий между делениями - 1 Вкладка Выравнивание: 30 снизу вверх |
· выполните форматирование оси Y:
| Вкладка Вид: Метки делений - внизу, основные - наружу Вкладка Шкала: Число категорий между подписями делений - 1, число категорий между делениями - 1 Вкладка Выравнивание: Авто |
· выполните форматирование оси Z:
|
|
|
| Вкладка Вид: Метки делений - рядом с осью, основные - наружу Вкладка Шкала: минимальное значение - 0, максимальное значение - 5, цена основных делений - 0,5, цена промежуточных делений - 0,1, плоскость XY пересекает в значении 0 Вкладка Число: Числовые форматы: общий Вкладка Выравнивание: горизонтальное |
Рис. 4. Итоговый результат задания по редактированию диаграммы.
5. Проведите форматирование сетки, стен и основания:
· в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Линии сетки установите параметры:
| Ось X: флажки - основные линии и промежуточные линии Ось Y: флажки - основные линии и промежуточные линии Ось Z: флажок - основные линии |
· в области стен диаграммы вызовите контекстное меню и выберите команду Формат стенок. Во вкладке Вид выберите светло-желтый цвет заливки;
· в области основания диаграммы вызовите контекстное меню и выберите команду Формат основания. Во вкладке Вид выберите светло-желтый цвет заливки.
6. Проведите форматирование легенды:
· в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
|
|
|
· в окне легенды вызовите контекстное меню, выполните команду Формат легенды и установите следующие параметры:
| Вкладка Вид: рамка - обычная, заливка - светло-желтый цвет Вкладка Размещение: в верхнем правом углу |
7. Сравните созданную вами диаграмму с образцом на рис. 4.
Задание 4. Построение тренда:
Создайте таблицу, аналогичную табл. 3.
Постройте гистограмму распределения оценок по информатике по группам.
Постройте линейный тренд для гистограммы.
Постройте полиномиальный тренд для гистограммы.
Оформите диаграмму и линии тренда, как представлено на рис. 5.
Таблица 3
Технология работы
1. Создайте таблицу, представленную в табл. 3.
2. Постройте диаграмму распределения по группам оценок по информатике:
· вызовите Мастер диаграмм, выберите на вкладке Стандартные обычный тип диаграммы и нажмите кнопку Далее;
· установите курсор в строку Диапазон и выделите в таблице блок ячеек A2:B7;
· оформите заголовки и названия осей так, как показано на рис. 5.
3. Постройте линейный тренд для диаграммы:
· установите указатель мыши на один из столбиков гистограммы и щелкните левой кнопкой мыши так, чтобы появились на всех столбиках черные метки;
|
|
|
· для выделенной гистограммы вызовите контекстное меню и выполните команду Добавить линию тренда;
· в диалоговом окне <<Линия тренда>> на вкладке Тип выберите окно Линейная;
· на вкладке Параметры установите параметры:
| Прогноз: вперед на 1 период Показывать уравнение на диаграмме: установите флажок Поместить на диаграмму величину достоверности аппроксимации: установите флажок |
· нажмите кнопку ОК.
4. Постройте полиномиальный тренд для гистограммы по технологии п. 3.
5. Оформите диаграмму и линии тренда так, как представлено на рис. 5.
Рис. 5 . Гистограмма и тренды
Лабораторная работа 4
Сортировка данных
Цель: научиться сортировать данные
Задание 1. Сортировка данных:
Создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на Сортировка.
На листе Список создайте таблицу, приведенную в табл. 1.
Произведите копирование списка (базы данных) с листа Список на лист Сортировка.
Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.
Технология работы
1. Проведите подготовительную работу в соответствии с п. 1 задания.
2. Сформируйте на листе Список шапку таблицы 1. Для этого:
· выделите первую строку, вызовите контекстное меню и выберите команду Формат ячеек;
· произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание:
| По горизонтали: по значению По вертикали: по верхнему краю Переносить по словам: установить флажок |
· введите названия столбцов (имен полей) в соответствии с таблицей 1;
· заполните таблицу данными.
3. Выделите список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Сортировка.
4. Выполните сортировку по столбцу Таб. № препод. Для этого:
· установите курсор в поле списка и введите команду Данные —› Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;
· в диалоговом окне <<Сортировка диапазона>> установите:
| Сортировать по: поле Таб. № препод., по возрастанию Затем по: Номер группы, по возрастанию В последнюю очередь по: поле Код предмета, по возрастанию |
· установите флажок Идентифицировать поля по записям.
Таблица 1
Лабораторная работа 5
Фильтрация (выборка) данных
Цель: научиться обрабатывать данные
Задание 1. Выборка данных из списка по критерию отбора, используя Автофильтр:
1. Переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (табл. 1 из 4 ЛР).
2. Выберите из списка данные, используя критерий:
· для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку;
· вид занятий - лк.
3. Отмените результат автофильтрации.
4. Выберите из списка данные, используя критерий: для группы 1А получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.
5. Отмените результат автофильтрации.
Технология работы
1. Переименуйте Лист3 на Автофильтр и скопируйте на него исходную базу данных.
2. Для выполнения п. 2 задания:
· установите курсор в область списка и выполните команду Данные —› Фильтр —› Автофильтр (в каждом столбце появятся кнопки списка);
· сформируйте условия отбора записей: в столбце Таб. № препод. из списка условий отбора выберите а1; в столбце Оценка из списка условий выберите Условие и в диалоговом окне сформируйте условие отбора >2;
в столбце Вид занятия из списка условий выберите лк.
3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.
4. Выполните п. 4 задания, воспользуясь аналогичной п. 3 технологией фильтрации.
5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.
Задание 2. Выборка данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию:
1. Переименуйте новый лист на Расширенный лист и скопируйте на него исходную базу данных (табл. 1 из 4 ЛР).
2. Скопируйте имена полей списка в другую область на том же листе.
3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 1А по предмету п1 на оценки 4 и 5.
4. Произведите фильтрацию записей на том же листе.
5. Сформируйте в области условий отбора Вычисляемый критерий: для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятия - лк.
6. Произведите фильтрацию записей на том же листе.
Технология работы
1. Переименуйте Лист4 на Расширенный лист и скопируйте на него исходную базу данных.
2. Скопируйте все имена полей списка в другую область (область формирования условий отбора записей) на том же листе, например установив курсор в ячейку J1.
3. Сформируйте в области условий отбора Критерий сравнения в соответствии с п. 3 задания. Для этого после имен полей введите в столбец Номер группы - 1А, в столбец Код предмета - п1, в столбец Оценка - условие - >3.
4. Произведите фильтрацию записей расширенным фильтром на том же листе:
· установите курсор в область списка (базы данных);
· выполните команду Данные —› Фильтр —› Расширенный фильтр;
· в диалоговом окне Расширенный фильтр задайте параметры:
· Скопировать результат в другое место: установить флажокИсходный диапазон: A1:G17Диапазон условия: J1:P2Поместить результат в диапазон: J4
· нажмите кнопку ОК.
5. Сформируйте в области условий отбора Вычисляемый критерий в соответствии с п. 3 задания. Для этого:
· в столбец Вид занятия введите лк;
· переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;
· в столбец Оценка 1 введите вычисляемый критерий вида
=G2>СРЗНАЧ($G$2:$G$17).
6. Произведите фильтрацию записей расширенным фильтром на том же листе аналогично п. 4.
Задание 3. Выборка данных из списка с использованием Формы:
1. Переименуйте новый лист на Форма и скопируйте на него исходную базу данных (табл. 1 из 4 ЛР).
2. Просмотрите записи списка с помощью формы и добавьте две новые.
3. Сформируйте условие отбора с помощью формы данных: для преподавателя а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - лк.
4. Просмотрите отобранные записи.
Технология работы
1. Переименуйте Лист5 на Расширенный лист и скопируйте на него исходную базу данных. Установите курсор в область списка и выполните команду Данные —› Форма.
2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопок Назад и Далее. С помощью кнопки Добавить добавьте две новые записи.
3. Сформируйте условие отбора в соответствии с заданием:
· нажмите кнопку Критерии, название которой поменяется на Правка;
· в пустых строках имен списка введите критерии: в строку Таб. № препод. введите а1, в строку Вид занятия - лк, в строку Оценка - условие >2.
4. Просмотрите отобранные записи, нажимая на кнопку Назад или Далее.
Лабораторная работа 6
Структурирование таблиц
Цель: научиться работать со сводными данными
Задание 1. Структурирование таблицы ручным способом:
1. Откройте книгу с таблицей, отображенной в табл. 1 из 4 ЛР, переименуйте новый лист на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы.
3. Вставьте пустые разделяющие строки между учебными группами.
4. Создайте структурные части таблицы для учебных групп.
5. Создайте структурную часть таблицы для столбцов: Код предмета, Таб. № препод., Вид занятия.
6. Закройте и откройте структурные части таблицы.
7. Отмените структурирование.
Технология работы
1. Откройте книгу с именем Spisok, переименуйте Лист6 на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы (команда Данные —› Сортировка).
3. Вставьте пустые разделяющие строки между учебными группами:
· выделите первую строку с другим, отличным от предыдущей строки, номером группы;
· вызовите контекстное меню и выполните команду Добавить ячейки.
4. Создайте структурные части таблицы для учебных групп:
· выделите блок строк, относящихся к первой группе;
· выполните команду Данные —› Группа и структура —› Группировать. В появившемся окне установите флажок строки;
аналогичные действия повторите для других групп.
5. Создайте структурную часть таблицы для столбцов Код предмета, Таб. № препод., Вид занятия аналогично п. 4 (в появившемся окне установите флажок столбцы).
6. Закройте и откройте созданные структурные части таблицы, нажимая на кнопки Минус или Плюс.
7. Отмените структурирование командой Данные —› Группа и структура —› Разгруппировать.
Задание 2. Автоструктурирование таблицы и введение дополнительного иерархического уровня структуры ручным способом:
1. Откройте книгу с именем Spisok, вставьте и назовите новый рабочий лист.
2. Создайте таблицу расчета заработной платы (табл. 1), в которой:
- в столбцы Фамилия, Зарплата, Надбавка, Премия надо ввести константы;
- в строке Итого подсчитываются суммы по каждому столбцу;
- в остальные столбцы надо ввести формулы:
· Подоходный налог = 0,12*Зар.плата
· Пенсионный фонд = 0,01*Зар.плата
· Общий налог = Подоходный налог + Пенсионный фонд
· Итого доплат = Надбавка + Премия
· Сумма к выдаче = Зар.плата - Общий налог + Итого доплат
Таблица 1
Пример исходной таблицы для автоструктурирования
3. Создайте автоструктуру таблицы расчета заработной платы.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам.
Технология работы
1. Откройте книгу с именем Spisok, вставьте новый рабочий лист и назовите его - Зар.плата.
2. Создайте таблицу согласно табл. 1. Введите формулы в ячейки в соответствии с п. 2 задания, используя метод автозаполнения. Проведите сортировку в списке по фамилиям.
3. Создайте автоструктуру таблицы расчета заработной платы: установите курсор в любую ячейку области данных и выполните команду Данные —› Группа и структура —› Создать структуру.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и на кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам, разделив весь список фамилий на группы по две фамилии. Для этого:
· вставьте пустую строку после первых двух фамилий: выделите третью строку и в контекстном меню выберите команду Добавить ячейки;
· аналогично вставьте пустую строку перед строкой Итого;
· выделите строки с первыми двумя фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать;
· выделите строки с остальными фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать.
Задание 14. Структурирование таблицы с автоматическим подведением итогов по группам таблицы, представленной в табл. 1 из 4 ЛР:
1. Откройте книгу с таблицей, отображенной в табл. 1 из 4 ЛР 4, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе.
4. Создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни.
Технология работы
1. Откройте книгу Spisok, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте список записей с помощью команды Данные —› Сортировка, выбрав в старшем ключе номер группы, в промежуточном - код предмета, в младшем - вид занятий. Установите флажок Идентифицировать поля по подписям.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе:
· установите курсор в произвольную ячейку списка и выполните команду Данные —› Итоги;
· в диалоговом окне Промежуточные итоги укажите:
| При каждом изменении в - Номер группы Операция: Среднее Добавить итоги по: Оценка Заменять текущие итоги: нет Конец страницы между группами: нет Итоги под данными: да |
4. Аналогично п. 3 создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Аналогично п. 3 создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни, используя кнопки с минусом и плюсом.
Лабораторная работа 7
Создание сводных таблиц
Цель работы: Получение навыков по составлению сводных таблиц.
Пояснение к работе:
Сводная таблица – специальный тип таблицы, суммирующий информацию из конкретных плей списка или базы данных, допускающая после построения на различные разрезы данных.
Свободная таблица обобщает сведения в полях данных в их связи друг с другом и позволяет получить быстрый ответ на многие вопросы.
Свободная таблица – идеальный источник данных для диаграммы.
Подготовка к построению
Следует выделить одну из ячеек списка(любую).
Затем следует выбрать в меню Данные команду Свободная таблица
Далее в работу включается мастер свободных таблиц и диаграмм
Разрезом данных называется совокупность списка полей списка, из которых выбираются данные, и способов вычисления поля данных, их группировки и подведение промежуточных и общих итогов Поле свободной таблицы – категория информации, эквивалент поля в списке – источнике данных.
Заголовок поля – надпись, описывающая содержимое поля. Перетаскивание заголовков можно менять разрез данных свободной таблицы.
Элемент поля – значения, которые поле принимает в источнике данных, для поля данных – вычисляемые значения.
Итоговая функция – функция, используемая в свободной таблице для вычисления значения элементов поля данных ( по умолчанию для числовых данных это функция СУММ)
Задание. Для таблицы 1 из 4 ЛР постройте следующие виды сводных таблиц:
1. По учебным группам подведите итоги по каждому предмету и виду занятий с привязкой к преподавателю: средний балл, количество оценок, минимальная оценка, максимальная оценка.
2. По каждому преподавателю подведите итоги в разрезе предметов и номеров учебных групп: количество оценок, средний балл, структура успеваемости.
Технология работы
1. Откройте книгу Spisok, переименуйте новый лист на Итоги и скопируйте на него исходную базу данных.
2. Создайте сводную таблицу с помощью Мастера сводных таблиц по шагам (команда Данные —› Сводная таблица):
этап 1 (выбор источника данных ) - щелкните по кнопке <в списке или базе данных Excel> и по кнопке <Далее>;
этап 2 - отображение в строке Диапазон блока ячеек списка (базы данных). Если диапазон указан неверно, то его надо стереть и указать нужный блок ячеек;
этап 3 - построение макета сводной таблицы для п. 1 задания. Технология его построения состоит в следующем:
· перетащите элемент с именем поля, находящегося в правой стороне макета, в одну из областей: № группы - в Страницу; № зач. кн. -
в Строку, Таб. № преп. и Вид занятия - в Столбец, Оценку - в Данные,
· в области Данные два раза щелкните левой кнопкой мыши и в диалоговом окне Вычисление поля сводной таблицы выберите соответствующую операцию над значением поля;
этап 4 - выбор места расположения: существующий лист.
3. Выполните автоформатирование полученной сводной таблицы (команда Формат —› Автоформат).
4. Внесите изменения в исходные данные и выполните команду Данные —› Обновить данные.
5. Аналогично постройте сводную таблицу для п. 2 задания.
Лабораторная работа 8
Консолидация данных
Цель работы: изучение технологий связей между файлами и консолидации данных.
Пояснение к работе:
Инструмент Консолидация позволяет объединить таблицы, находящиеся на разных рабочих листах и даже в разных книгах. Но такие таблицы должны иметь идентичную структуру.
Функция ПРОСМОТР
Для проверки значений и организации поиска в векторе или массиве.
Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. Векторная форма
Функция ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки.
Дата добавления: 2019-02-12; просмотров: 750; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!