ЛАБОРАТОРНОЙ РАБОТЫ. Цель работы получить навыки работы по созданию, редактированию и расчетам с помощью электронных таблиц
 Скачать 1.33 Mb.
|
|
Задание 5.2. Построить объемную круговую диаграмму, иллюстрирующую сведения о средних баллах, полученных студентами в экзаменационную сессию. Выполнение. Основными элементами для построения диаграммы являются: область диаграммы, область построения диаграммы, ряды данных, оси координат, заголовки, легенда, линии сетки, подписи данных. Для построения диаграммы необходимо выполнить следующие действия: 1) Выделить в таблице диапазон ячеек с исходными данными (область данных диаграммы). Для примера на листе Успеваемость(задание 5.1.) выделить диапазон ячеек F5:F16 (на круговой диаграмме можно отобразить значения только одного ряда данных). Если выделен диапазон ячеек, содержащий более 1 строки или более 1 столбца, то Excel интерпретирует строки или столбцы как отдельные ряды, содержащие элементы данных. Замечание. Для выделения несвязанных диапазонов ячеек таблицы необходимо выполнить эти действия при нажатой клавише Ctrl. 2)Вставить диаграмму, выполнив команду: вкладке Вставка → Диаграммы → Круговая.  В списке выбрать – Объемная круговая. Нажать кнопку [Ok]. Появится следующая диаграмма в заданном месте рабочего листа.  3)Определить названия рядов и подписи категорий. – Перейти на вкладку Работа с диаграммами. – Выбрать вкладку Выбрать данные. Откроется диалоговое окно Выбор источника данных. – Позиционировать курсор в поле Изменить, щелкнуть по кнопке свертывания, находящейся в правой части поля, и выделить ячейки С5:С16 на листе Список с фамилиями студентов для задания текста легенды. – Выполнить щелчок по кнопке свертывания. В поле Подписи категорий появится ссылка: =Список!$C$5:$C$16. – Нажать кнопку [OK]. 4) Задать заголовок диаграммы, указать расположение легенды, отобразить подписи значений рядов.Для этого выполнить действия: – перейти на вкладку Макет → Название диаграммы; – в поле Название диаграммы выбрать позицию Над диаграммой и ввести текст Сравнительный анализ среднего баллауспеваемости студентов по фамилиям; – перейти на вкладку Легенда, включить параметр Добавить легенду и установить флажок Справа для указания расположения легенды; – перейти на закладку Подписи данных; – в группе переключателей выбрать параметр У вершины внутри для отображения на диаграмме значения среднего балла каждого студента; – нажать левую клавишу мыши. При необходимости можно откорректировать размер диаграммы с помощью маркеров размера. Вид построенной диаграммы представлен на рисунке.  После активизации диаграммы (щелчка левой клавиши мыши внутри диаграммы) можно выделить любой из ее элементов для корректировки, которая заключается в удалении либо изменении свойств элемента. Изменение вида элемента (цвета фона, выбор узора, использование рамок и т.д.) или шрифта (типа, стиля и размера используемых символов текста) выполняется с помощью форматирования. Окно форматирования для любого элемента диаграммы можно открыть с помощью двойного щелчка мыши на нем. Задание 5.3. Построить диаграмму, отображающие оценки, полученные студентами по всем предметам в экзаменационную сессию, используя таблицы Задания 5.1.; выбрать тип диаграммы Гистограмма. Для области диаграммы установить размер шрифта - 8, для оси категорий изменить способ выравнивания подписей. Выполнение. Указываем диапазон ячеек, в котором располагаются данные: – установить курсор в нужном месте листа; – выделить на листе Успеваемость диапазон ячеек С5:Е16, содержащий оценки студентов по трем предметам; – рядом сВставка → Диаграммынажмите на Кнопку вызова диалогового окна  и в окне выберите Гистограмма → Гистограмма с группировкой.Отображаемподписи значений элементов ряда: – выполнить команду Конструктор → Выбрать данные; – в окне Изменить щелкнуть по кнопке свертывания, находящейся в правой части поля, и выделить ячейки С5:С16 на листе Список с фамилиями студентов для задания; – нажать кнопку [ОК]. Отображаемназвания рядов в легенде: – в списке Элементы легенды окна Выбор источника данных выделить значение Ряд 1, в окне Изменить щелкнуть по кнопке свертывания и ввести текст Оценка по математике. Аналогично для рядов Ряд 2 и Ряд 3 ввести текст Оценка по информатике и Оценка по химии соответственно; Отображаем заголовок диаграммы: – выполнить команду Макет → Название диаграммы; – ввести текст в поле Название диаграммы: Сравнительный анализ успеваемости студентов, в поле Название осейв поле Название основной горизонтальной оси ввести Фамилии, в поле Название основной вертикальной оси – Повернутое название: Оценки; – нажать на кнопку [ОК]. Получим следующую диаграмму:  Задание 5.4. Построить график функций  .Выполнение. Для построения графика функции необходимо сначала надо построить таблицу ее значений при различных значениях аргумента. Аргумент обычно изменяется с фиксированным шагом. Пусть шаг изменения х равен 0,1. Надо найти у(0), у(0.1), … у(1). В ячейки A2:A12 вводятся автозаполнением числа 0, 0.1, … ,1. В ячейку B2 вводится формула заданной функции. Заполняем теперь ячейки B2:B12 значениями у вычисленными по формуле путем протягивания ячейки B2 вниз до B12. Технология построения графика: 1. Выделяем диапазон ячеек A1:B12. 2. Выбираем Вставка → Диаграммы → Точечная→ Точечная с гладкими кривыми и маркерами. 3. Используя инструменты для работы с диаграммами, отформатируем полученный график:  Построение графика функции с двумя и более условиями. Для построения таких графиков используется функция логического ветвления ЕСЛИ(лог_выражения; значение_если истина; значение_если_ложь).Эта функция вводится в первую ячейку, где начинается вычисление функции и далее, всё проводится, как и для простого графика. Еслизадана функция с тремя условиями, то вводится формула =ЕСЛИ(лог_выр1; значение1; ЕСЛИ(И (лог_выр2; лог_выр3);Значение2; Значение3)).  Содержание отчета Отчет должен содержать ход работы и результаты выполнения своих вариантов: 1. Представить Круговую диаграмму, а также Гистограммув соответствии со своим вариантом. 2. Построить график функции  , где (n – номер варианта).3. Построить график функции согласно своему варианту. Контрольные вопросы 1. Что такое диаграмма в Excel? 2. Как задать ряды данных? 3. Как задать заголовок диаграммы? 4. Как задать подписи к осям диаграммы? 5. Что такое легенда? 6. Как изменить тип диаграммы? 7. Какие диаграммы можно построить только одного ряда данных? 8. Построение графика функции с двумя и более условиями. Варианты заданий 1. Построить диаграммы на базе таблиц Задания 5.1. в соответствии со своими вариантами. Задание 1 Вариант 1. Круговая. Легенда сверху. Вариант 2. Разрезанная круговая. Легенда снизу. Вариант 3. Объемная Разрезанная круговая. Легенда справа. Вариант 4. Вторичная круговая. Легенда слева. Вариант 5. Вторичная гистограмма. Легенда сверху. Вариант 6. Кольцевая. Легенда снизу. Вариант 7. Разрезанная кольцевая. Легенда справа. Вариант 8. Лепестковая. Легенда слева. Вариант 9. Заполненная лепестковая. Легенда сверху справа. Вариант 10.Поверхность. Контурная. Легенда сверху. Задание 2 Вариант 1. Гистограмма с накоплением. Вариант 2. Гистограмма с группировкой. Вариант 3. Объемная гистограмма. Вариант 4. Объемная гистограмма с накоплением. Вариант 5. Цилиндрическая с накоплением. Вариант 6. Объемная цилиндрическая. Вариант 7. Пирамидальная с накоплением. Вариант 8. Объемный график. Вариант 9. Линейчатая с группировкой. Вариант 10.Объемная линейчатая с накоплением. 2. Построить графики функций с двумя и более условиями на заданном диапазоне.
Сортировка и фильтрация данных. Создание связанных таблиц Теоретические сведения Сортировка.Excel позволяет сортировать ячейки таблицы по возрастанию или убыванию.Команда Сортировка → Данные открывает диалоговое окно сортировки. В раскрывающемся списке Сортировать по выберите имя первого поля, по которому будет производиться сортировка. Выберите опцию по возрастанию или по убыванию. Если необходимо отменить сортировку, произведенную до этого, в списке Сортировать по укажите Не сортировать. Чтобы произвести сортировку по нескольким столбцам, в раскрывающемся списке Затем по выберите имя поля, по которому будут дополнительно сортироваться данные. Будьте внимательны: если выделена не вся область данных, то при сортировке они перемешаются. Фильтрация.В отфильтрованных данных отображаются только строки, соответствующие заданным условиям, а ненужные строки скрываются. После отбора данные в этом подмножестве можно копировать, искать, изменять, форматировать, преобразовывать в диаграммы и выводить на печать; при этом их местонахождение и порядок не изменятся. Возможен отбор по нескольким столбцам. Несколько фильтров можно применять одновременно. Фильтры действуют по дополнительному принципу, т. е. каждый новый фильтр накладывается на фильтр, примененный до него, и в еще большей степени ограничивает подмножество данных. С помощью автофильтра можно создать три типа фильтров: по значениям списка, по формату или по условиям. Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы. Например, можно выполнить отбор по цвету ячеек или по списку чисел, но нельзя использовать оба типа одновременно; точно так же, необходимо выбрать один тип из двух, если требуется выполнить отбор по значкам или на основе фильтра, заданного пользователем. Для получения более точных результатов рекомендуется не смешивать в одном столбце данные разных форматов, например текст и числа, числа и даты, поскольку для каждого столбца может использоваться только один тип команды фильтра. Если в столбце представлено несколько форматов, отображена будет команда для преобладающего формата. Например, если столбец содержит три значения в числовом формате и четыре — в текстовом, то отображается команда фильтра Текстовые фильтры. Консолидация.Консолидация данных позволяет создавать таблицу-сводку по одной или нескольким категориям данных, объединив информацию разных листов одной или нескольких книг. Консолидировать данные (команда Данные → Работа с данными → Консолидация) можно по расположению или по категории. Если вы консолидируете данные по расположению, MS Excel собирает информацию из одинаково расположенных ячеек каждого исходного листа. При консолидации данных по категории Excel использует в качестве основы для объединения листов заголовки столбцов или строк. Консолидация по категории предоставляет большую свободу для организации данных в исходных листах. При консолидации можно установить или убрать связь с исходнами листами. Функции консолидации: – Количество значений; – Среднее; – Максимум; – Минимум; – Произведение/Количество чисел; – Несмещенное отклонение; – Смещенное отклонение; – Несмещенная дисперсия. Промежуточные итоги. Этот инструмент помогает быстро подвести итоги. Подведение итогов начинается с сортировки. Необходимо сгруппировать записи, по полю или полям которых требуется получить итоговые значения.Команда Данные → Структура → Промежуточные итоги откроет диалоговое окно Промежуточные итоги. Сводные таблицы. Сводная таблица служит для быстрого подведения итогов или объединения больших объемов данных. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц(Вставка → Таблицы → Сводная таблица). Этапы работы мастера: 1. На первом этапе выбираем, откуда выбираются данные: из одного диапазона, разных диапазонов или внешних источников. 2. Собственно выбор диапазонов 3. Макетирование сводной таблицы: необходимо мышью перетащить кнопки полей. Практическая часть Команды меню Данные работают корректно, когда шапка таблицы расположена в одной строке (если бы колонки таблицы были пронумерованы, то для работы команды Данные можно было бы использовать нумерацию в качестве названия колонок). При работе с командой Данные Excel рассматривает таблицу как базу данных, в которой строки являются записями, а столбцы полями. Для дальнейшей работы преобразуем на новых листах таблицы из Задания 5.1. и 5.2. к виду:   Задание 5.5. Найти в преобразованной таблицеСписок(см. выше) тех студентов, у которых фамилия начинается с буквы "Б". Выполнение. 1. Установить курсор в позиции «Ф.И.О», 2. Выполнить команду Данные → Фильтр. Раскрыть список команд столбца «Ф.И.О». 3. Выбра позицию Текстовые фильтры, затем – Начинается с.. 4. В окне Пользовательский автофильтр в поле Ф.И.О., которое будет участвовать в определении критерия, ввести критерий: Б*.  6. Нажать кнопку ОК, чтобы перейти к записи, удовлетворяющей введенному критерию. Задание 5.6. Отсортировать таблицу Список по столбцам Группа и Номер зачетки. Выполнение. 1) Выделить диапазон таблицы, включая шапку (А2:F14). 2) Выполнить команду Данные → Сортировка. 3) В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать из списка имя столбца Группа, а в поле Затем по–Номер зачетки, установив переключатели для обоих столбцов –По убыванию.  4)Нажать кнопку [ОК]. Задание 5.7. Использование числового фильтра. Выбрать из таблицы Успеваемость, тех студентов, средний балл которых >=6. |
