Информатика_Excel_лаб_раб. Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.
 Скачать 1.91 Mb.
|
|
2 Изменение типа диаграммы После того как построена диаграмма Excel предоставляет большие возможности для изменения ее содержимого и вида. Готовая диаграмма состоит из ряда элементов, которые можно выбирать, щелкнув по ним мышью, и изменять. Выбранный элемент помечается маркерами. Выделив диаграмму с помощью вкладки Конструктор, группы Тип, кнопки Изменить тип диаграммы можно изменить тип диаграммы. Упражнение 3.4. Измените тип диаграммы. 51 Выделите диаграмму. Выполнив Конструктор Тип Изменить тип диаграммы Установите объемный вид гистограммы, рисунок 3.4. Попробуйте различные типы диаграмм и подберите наиболее наглядный из них. Сохраните таблицу с диаграммой под именем tab3. xlsx. Рисунок 3.4 – Объемная гистограмма Не все типы диаграмм подходят для наглядного представления данных, а некоторые невозможно построить. Например, поданным, представленным выше диаграммы типа Точечная или график построить нельзя. Некоторые типы объемных диаграмм тоже могут плохо отразить данные и привести к неразберихе. Внимание Если в результате экспериментов диаграмма испорчена, то удалите ее и начните построение сначала. Для удаления следует выделить диаграмму, а затем нажать клавишу Delete. Круговые диаграммы — наиболее часто используемый тип. Они подходят для наглядного представления долей и процентов от целого. 52 Упражнение 3.5. Построить объемную круговую диаграмму, а на основе данных представленных на рисунке 3.5, в результате получить диаграмму, представленную на рисунке 3.6. Рисунок 3.5 – Исходные данные Перейдите на Лист текущей рабочей книги. Лист назовите Круговая диаграмма Введите заголовок таблицы и данные в соответствии с рисунком Постройте диаграмму по шагам Нам шаге выделите исходные данные A1:B10 . Нам шаге выполните Вставка Диаграмма Круговая Объемная резаная круговая. Нам шаге укажите месторасположение легенды и добавьте подписи данных – Макет Подписи Подписи данных У вершины снаружи. 53 Рисунок 3.6 – Результат построения диаграммы Сохраните диаграмму. Построение графиков Для построения обыкновенных графиков функций х) используется тип диаграммы точечная. Этот тип диаграммы требует два ряда значений Х-значения должны быть расположены в левом столбце (или в верхней строке, а значения — в правом или в нижней строке. На одной диаграмме можно построить несколько графиков функций. Эта возможность используется для проведения сравнительного анализа значений Y при одних и тех же значениях X, а также для графического решения систем уравнений с двумя переменными. Упражнение 3.6. На одной диаграмме построить графики функций Y1=x 2 -1, х + 1, Y=10*(Y1/Y2) поданным лабораторной работы № 2 , рисунок 2.1. 54 Загрузите файл tab х. Снимите защиту с листа Табулирование Скопируйте основную и вспомогательную таблицы на Лист файла tab 3.xls, при вставек скопированных данныхис- пользуйте Специальную вставку Лист назовите X-Y диаграмма Выделите данные для построения диаграммы В Выполните Вставка Диаграмма Точечная Точечная с гладкими кривыми и маркерами В соответствии с образцом, рисунок 3.7, добавьте заголовок диаграммы и названия осей – Макет Подписи. Рисунок 3.7 – Результат построения диаграммы Отформатируйте область диаграммы. Подберите оптимальный размер области диаграммы так, чтобы таблица значений и график размещались на одном листе в альбомной ориентации. Сохраните диаграмму. 55 III Задания для самостоятельной работы Задание 1. На основе таблицы Использование домашнего компьютера, рисунок 3.8, постройте две диаграммы, представленные на рисунке 3.8. Рисунок 3.8 – Задание 1 для самостоятельного выполнения Примечания 1. Построение таблицы выполняйте на Листе текущей рабочей книги, дав ему имя Самостоятельная работа 2. При построении и редактировании обратите внимание на все элементы оформления таблицы и диаграмм. Повторите их. Расположите таблицу и диаграммы на разных листах. Задание 2. Выполнить табулирование функций и на основе полученных данных построить совмещенные графики. Для вы- Использование домашнего компьютера исследования фирмы MICROSOFT) 24,50% 15,40% 26,50% 8,80% 16,60% 8% Игры Обработка текстов Ведение Финансов Работа, выполняемая дома Образование Домашний бизнес Использование домашнего компьютера 16,6 8,2 8,8 26,5 15,4 24,5 0 5 10 15 20 25 Игры Обработка текстов Веде н ие Ф и на н со в Р а бота выполняемая дома Образование До м аш ни й б из нес бора функции используйте таблицу 3.1. Номер варианта указывает преподаватель. Таблица 3.1 - Варианты заданий Вариант Функция 1 Функция 2 Предел изменения X 1 Y = -Х Х [0,5; 5] шаг 0,5 2 Y=Х -2 Y=Х -3 [-3; -0] шаг 0,5 3 Х 3] шаг 0,5 4 Y= 2 x Y= (1/2) x [-2; 2] шаг 0,5 5 Y=ln X Х [0,5; 1] шаг 0,5 6 Х 9] шаг 0,5 Примечания 1. При построении и редактировании обратите внимание на элементы оформления графика на рисунке 3.7. Повторите их. 2. Расположите таблицу с ХУ-значениями и график на одном Листе. IV Контрольные вопросы 1 Перечислите этапы построения диаграммы 2 Из каких элементов состоит диаграмма и каких добавить удалить 3 Как выполняется изменение типа диаграммы 4 Как построить совмещенные графики 5 Приведите примеры задач для построения круговой диаграммы, гистограммы и линейного графика. 57 Лабораторная работа № 4 Тема Базы данных в Microsoft Excel I Цель работы Научиться создавать базу данных выполнять сортировку данных, выборку данных по различным критериям производить автоматическое подведение итогов в базе данных. II Теоретическое введение и упражнения База данных это совокупность определенным образом организованной информации на какую-то тему (в рамках некоторой предметной области. Microsoft Excel содержит широкий набор средств для обеспечения эффективного управления базами данных создание структуры базы данных сортировка данных фильтрация данных подведение итогов. Создание структуры базы данных В Excel базы данных размещаются в таблицах. Каждая таблица состоит из строки столбцов, которые в базах данных называются записями и полями, соответственно. Информация в базах данных имеет постоянную структуру. Каждую строку можно рассматривать как единичную запись. Информация в пределах каждой записи содержится в полях. При работе с базами данных в Excel необходимо создать структуру базы данных для этого ввести имена полей. Затем объявить тип и длину каждого поля. И только после этого вводить информацию в базу данных. Упражнение 4.1. Создайте базу данных Адресно - телефонная книга Назовите Лист Адресно - телефонная книга рисунок Рисунок 4.1 – Формирование имен полей базы данных Введите во второй строке названия полей базы данных: Фамилия, Имя, Отчество, Дата рождения, Индекс, Улица, Дом, Квартира, Телефон, рисунок 4.1. Отформатируйте поля базы данных в соответствии с рисунком. Установите ширину полей. В поле Дата рождения установите формат ячеек Дата Для этого выделите ячейки, выполните команды Главная Ячейки Формат Формат ячеек Число Дата, выберите привычный вид даты и нажмите ОК. Для поля Телефон установите соответствующий формат на вкладке Число/дополнительный. Заполните базу данных. Данные возьмите из таблицы на рисунке 4.2. Рисунок 4.2 – Пример базы данных 59 Сортировка базы данных После ввода данных вам может потребоваться упорядочить данные. Процесс упорядочения записей в базе данных называется сортировкой. При сортировке изменяется порядок следования записей в базе данных или таблице. Команда Данные Сортировка и фильтр Сортировка устанавливает порядок записей в базе данных в соответствии с содержимым конкретных столбцов. При выполнении этой команды открывается окно диалога Сортировка, которое позволяет указать поля для сортировки и определить критерии сортировки, рисунок 4.3. Рисунок 4.3 – Окно сортировки С помощью раскрывающегося списка Сортировать по выбирается столбец для сортировки. Порядок сортировки устанавливается раскрывающимися списками Сортировка предлагается сортировка по формату) и Порядок определяет как сортировать, в зависимости от типа данных предлагается порядок сортировки. С помощью кнопки Параметры при сортировке можно учитывать регистра также указать, что сортировать строки или столбцы диапазона, рисунок 4.4. 60 Рисунок 4.4 – Дополнительные параметры сортировки С помощью кнопки Добавить уровень можно определить порядок вторичной сортировки для записей, в которых имеются совпадающие значения, рисунок 4.5. Рисунок 4.5 – Сортировка по нескольким полям Упражнение 4.2. Расположите в адресно-телефонной книге фамилии абонентов в алфавитном поряке. Установите курсор на ячейку Адресно-телефонной книги. Выполните Данные Сортировка и фильтр Сортировка. В раскрывающемся списке Сортировать по выберите поле Фамилия, в списке Порядок установите От А до Я. Нажмите ОК. На экране появится база данных после сортировки. Примечание Простейшую сортировку поданным одного столбца можно выполнить следующим образом 1. Выделите одну любую ячейку в столбце, поданным которого 61 сортируется таблица. 2. Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите направление сортировки. Предположим необходимо расположить фамилии сотрудников по алфавиту, но при наличии одинаковых фамилий необходимо, чтобы имена и отчества также располагались в алфавитном порядке. После выполнения команды Данные Сортировка и фильтр Сортировка в раскрывающемся списке Сортировать по выберите поле Фамилия. Добавьте дважды уровень сортировки с помощью кнопки Добавить уровень в раскрывающемся списке Затем по – Имя, ив последнем уровне в установите поле Отчество. Нажмите ОК. На экране появится упорядоченная база данных. Сохраните файл как tab4. Фильтрация данных При фильтрации данных находится и отбирается для обработки часть записей в базе данных. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям. При этом остальные строки оказываются скрытыми. Для фильтрации данных используются команда Фильтр, группы Сортировка и фильтр, вкладки Данные, или Глав- ная/Редактирование/Сортировка и фильтр Фильтр для организации фильтрации данных на месте их расположения - Авто- фильтр. А также Расширенный фильтр – элемент управления Дополнительно, группы Сортировка и фильтр, вкладки Данные. В случае простых критериев используется Автофильтр. При использовании сложных критериев применяют Расширенный фильтр. 1 Автофильтр Команда Фильтр, группы Сортировка и фильтр, вкладки Данные, или Главная Редактирование Сортировка и 62 фильтр Фильтр помещает кнопки раскрывающихся списков кнопки со стрелкой) непосредственно в строку с метками столбцов, рисунок 4.6. Рисунок 4.6 – Автофильтр С помощью этих кнопок можно выбрать строки таблицы, которые необходимо вывести на экран. Нажав кнопку со стрелкой, появится раскрывающийся список этого поля, в котором выводятся все значения встречающиеся в столбце. Для выборки данных с использованием фильтра следует выбрать значение или параметр выборки. На значках раскрывающихся списков в названиях столбцов, выбранных из таблицы с помощью фильтра по которым была произведена выборка, появляется особая отметка. В строке состояния окна Excel в течение некоторого времени отображается текст с указанием количества найденных записей и общего количества записей в таблице, рисунок 4.7. Рисунок 4.7 – Результат работы автофильтра 63 Можно выбрать строки по цвету ячейки, цвету текста или значку одного или нескольких столбцов, для этого надо щелкнуть по значку раскрывающегося списка столбца, по которому производится отбор и выбрать команду Формат по цвету, а затем в подчиненном меню выбрать цвет ячейки, цвет шрифта или значок, рисунок 4.8. Рисунок 4.8 - Отбор по формату Можно производить выборку не только по конкретному значению, но и по условию. Условие можно применять для числовых значений, текстовых значений, дат. Для отбора по условию надо. щелкнуть по значку раскрывающегося списка столбца, по которому производится отбор 2. выбрать команду Числовые фильтры(или Текстовые фильтры или Фильтры по дате – в зависимости от типа фильтруемых данных, а затем в подчиненном меню выбрать применяемое условие, рисунок 4.9. 64 Рисунок 4.9 – Отбор по условию 3. При использовании условий равно, неравно, больше, меньше, больше или равно, меньше или равно установите значение условия в окне Пользовательский автофильтр, рисунок. Значение условия можно выбрать из списка или ввести с клавиатуры. Рисунок 4.10 – Пользовательский фильтр Для возвращения исходного списка 1. щелкнуть по значку раскрывающегося списка столбца, по которому производится отбор 2. выбрать Снять фильтр с … 65 Упражнение 4.3. В базе данных Телефонный справочник найдите всех сотрудников с фамилиями Иванов. На Листе 2 файла tab 4.xls на основе базы данных Ад- ресно-телефонная книга, создайте базу данных Телефонный справочник дополнив ее полем Льготы. Лист переименуйте Телефонный справочник. Скопируйте данные с листа Адресно- телефонная книга. В поле Льготы внесите значения 0 или 1 в зависимости от наличия льгот Выделите область сданными. Выполните команду Фильтр, группы Сортировка и фильтр, вкладки Данные В полях меток столбцов появятся кнопки раскрывающихся списков. Снимите знак ˅ со всех фамилий за исключением фамилии Иванов. Нажмите ОК. Нажмите кнопку раскрывающегося списка в поле Фамилия. Появится раскрывающийся список, в котором выводятся все значения этого столбца. Раскрывающийся список исчезнет, а на экране появятся соответствующие строки базы данных, рисунок 4.11. Рисунок 4.11 – Результат фильтрации Верните исходный список базы данных. Для этого в раскрывающемся списке по полю Фамилия выберите команду Снять фильтр с Фамилия Упражнение 4.4. Выведите данные по абонентам, номера телефонов которых начинаются с 64. Выполните команду Фильтр, группы Сортировка и фильтр, вкладки Данные В полях меток столбцов появятся кнопки раскрывающихся списков. 66 Нажмите кнопку для раскрытия списка в поле Телефон и выберите Текстовые фильтры / начинается с … Появится окно диалога Пользовательский автофильтр, рисунок 4.12. Рисунок 4.12 – Пользовательский фильтр Нажмите кнопку ОК. На экране появится список абонентов, номера телефонов которых начинаются с 64. 2 Расширенный фильтр Команда Данные Сортировка и фильтр Дополнительно позволяет отыскивать строки с помощью более сложных критериев, чем в пользовательских автофильтрах (2-3 и более условия. При этом Расширенный фильтр использует для фильтрации данных интервал критериев по нескольким полям. На экран выводятся только те строки, которые удовлетворяют всем критериям, записанным в интервале критериев. После выполнения команды на экране появляется окно диалога Расширенный фильтр, в котором определяются параметры фильтра, рисунок 4.13. 67 Рисунок 4.13 – Окно расширенного фильтра Область Обработка содержит переключатель, который можно установить в положение Фильтровать список на месте или Скопировать результат на другое место. При установке переключателя в положение Фильтровать список на месте на экране отображаются только те строки, которые удовлетворяют указанному критерию. При выборе Скопировать результат на другое место отфильтрованные данные копируются на другое место на том же рабочем листе. В поле ввода Исходный диапазон указывается интервал, содержащий список, подлежащий фильтрации. В поле ввода Диапазон критериев указывается интервал ячеек на рабочем листе, который содержит критерии фильтрации. В поле ввода Поместить результат в диапазон указывается интервал ячеек, в который копируются строки, удовлетворяющие критериям. Это поле доступно только в том случае, когда выбран переключатель Скопировать результат на другое место. Флажок Только уникальные записи предназначен для вывода только неповторяющихся строк. Упражнение 4.5. Выведите данные по абонентам, имеющим льготы Скопируйте ячейки с заголовками базы данных в другое место таблицы(например, ниже базы данных в ячейки А. 68 Введите в ячейку J20 критерий поискав нашем случае наличие льгот – 1, рисунок 4.14. Рисунок 4.14 – Подготовка к расширенному фильтру Установите курсор в произвольную ячейку базы данных и выполните команду Данные Сортировка и фильтр Дополнительно. Появится окно диалога Расширенный фильтр. Установите переключатель Скопировать результат на другое место. Становится доступным поле ввода Поместить результат в диапазон. Нажмите мышью на поле ввода Исходный диапазон ивы- делите список, ячейки А. При этом в поле ввода появятся адреса выделенных ячеек, рисунок 4.15. Перейдите в поле ввода Диапазон критериев и выделите ячейки, где указаны критерии поиска (не забудьте выделить названия столбцов в диапазоне критериев, A20:J21. При этом в поле ввода появятся адреса выделенных ячеек. Перейдите в поле ввода Поместить результат в диапазон и укажите ячейку, начиная с которой будет выводиться отфильтрованный список, A23. При этом в поле ввода появятся адреса выделенных ячеек. Нажмите ОК. На экране появятся данные по абонентам, имеющим льготы. 69 Рисунок 4.15 – Значения для фильтрации Упражнение 4.6. Выведите данные по абонентам, не имеющим льгот, номера телефонов которых начинаются с 4. Скопируйте ячейки с заголовками базы данных в другое место таблицы К Введите в ячейку S3 критерий поискав данном случае символы 4*, а в ячейку T3 – 0. Примечание символ * означает, что за цифрой 4 могут стоять любые символы. Выполните команду Данные Сортировка и фильтр До- полнительно.Появится окно диалога Расширенный фильтр. Установите переключатель Скопировать результат на другое место. Нажмите мышью на поле ввода Исходный диапазон. Выделите список, ячейки А. Нажмите мышью на поле ввода Диапазон критериев. Выделите ячейки, где указаны критерии поиска (не забудьте выделить названия столбцов, Т. Нажмите мышью на поле ввода Поместить результат в диапазон и укажите ячейку, начиная с которой будет выводиться отфильтрованный список, К. Подведение итогов в базе данных 70 Один из способов обработки данных состоит в подведении промежуточных и конечных итогов. С помощью команды Данные Структура Промежуточные итоги можно вставить строки итогов в список произведя суммирование данных. При вставке строк Excel автоматически помещает вконец списка данных строку общих итогов. После выполнения команды Данные Структура Промежуточные итоги появится окно диалога Промежуточные итоги, рисунок 4.16. В поле ввода При каждом изменении в определяется столбец, который содержит группы, для которых нужно вычислять промежуточные итоги. Надо выбрать в раскрывающемся списке название нужного столбца для сортировки списка. Рисунок 4.16 – Окно Промежуточные итоги В раскрывающемся списке Операция выберать функцию, которая вычисляет итоги. По умолчанию Excel использует функцию Сумма для числовых значений или функцию Счет для текстовых значений. Кроме того можно использовать следующие функции Среднее, Максимум, Минимум, Произведение, Кол 71 во чисел, Несмещенное отклонение, Смещенное отклонение, Несмещенная Смещенная дисперсия. Вычисленные итоги располагаются под столбцом, который выделяется в поле ввода Добавить итоги по. Можно выделить более одного столбца, поставив напротив названия |