Цель работы
Знакомство с методами обработки данных, организованных в списки, сортировкой, редактированием, просмотром, поиском и извлечением данных по различным критериям.
Подготовка к работе
По указанной литературе изучить приёмы работы со списками, сортировка списка по строкам, столбцам, обработка списка с помощью формы, изменение структуры таблицы.
Контрольные вопросы
Дать понятие списков связанных данных (ССД).
Дать понятие записи.
Дать понятия поля.
Добавление данных к существующему списку.
Сортировка списка по строкам.
Сортировка списка по столбцам.
Сортировка данных по заданному условию.
Обработка списков с помощью формы.
Задание на выполнение и методические указания
I. Сортировка списка по строкам
Ввести таблицу, приведенную на рисунке 1.1.
Рисунок 1.1.
Пользуясь командами "Данные"-"Сортировка" и "Данные"-"Итоги", проделайте задание 3 и 4.
Укажите, сколько уровней сортировки нужно использовать, название полей, по которым производится сортировка, и направление сортировки по каждому уровню.
Предварительно скопируйте исходную таблицу на отдельные листы или используйте режим групповой работы с листами (результат выполнения каждого пункта должен располагаться на отдельном листе или ниже в этом же листе).
Есть ли в фирме женщины-секретари?
Сколько сотрудников с фамилией Иванов работают в фирме и кто из них самый молодой? (Предварительно добавьте столбец Возраст).
Каков средний возраст мужчин и женщин, работающих в фирме?
Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей?
В пунктах 3 и 4 после сортировки выполнить расчеты с помощью меню "Данные-Итоги".
II. Сортировка списка по столбцам
Ввести таблицу, представленную на рисунке 2.1, на чистый лист книги.
Рисунок 2.1
Отсортировать ее так, чтобы предметы (названия полей) располагались в алфавитном порядке. Скопировать ее с транспонированием на свободный лист (использовать меню Специальная вставка).
Добавить в конец таблицы строку "Средний балл" и заполнить её, используя функцию СРЗНАЧ.
Выполнить сортировки по столбцам. Перед каждой сортировкой копировать таблицу на новый лист.
Переставить столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке.
Переставить столбцы так, чтобы в первых колонках были худшие учащиеся (с минимальным средним баллом).
Переставить столбцы так, чтобы в первых колонках были учащиеся, хорошо успевающие по математике, но с провалами по физкультуре.
Переставить столбцы так, чтобы фамилии студентов расположились в алфавитном порядке, снова транспонируйте таблицу и поместите ее на Листе 1, начиная с клетки A10.
III. Обработка списков с помощью формы
Активизировать лист с исходной таблицей (рисунок 1.1) и убедиться, что первая строка содержит заголовки полей.
Используя окно "Форма", просмотреть значения в списке.
Принять на работу одного инженера и уволить одного водителя.
Сотруднице Абрамовой поменять фамилию на Иванова.
Изменить структуру таблицы.
Ввести дополнительное поле "Оклад" после поля "Пол" и заполнить его осмысленными значениями. Заполнение производить в окне "Форма" после поиска записей с заданными должностями. При задании критерия поиска использовать минимальное количество букв и символы шаблона ?, *.
После поля "Оклад" добавить еще три поля: "Надбавки", "Налоги", "К выплате".
Установить надбавки в размере 1000 руб. женщинам старше 50 лет и мужчинам старше 60. Директору и референту - 2000 руб. Поиск соответствующих лиц выполнять через форму.
В обычном режиме редактирования заполнить поле "Налог" - 5% от суммы оклада и надбавки, если она не превышает 1 тыс. руб., и 10%, если свыше. При заполнении поля записать формулу с использованием функции ЕСЛИ.
Записать формулу и заполнить поле "К выплате" ("Оклад" + "Надбавки" - "Налоги").
В окне "Форма" выполнить поиск сотрудников по следующим критериям:
Женщин с низким окладом (меньше 4000 руб.);
Мужчин, получающих больше 1 тыс. руб. и моложе 30 лет;
Предъявить результаты преподавателю.
Лабораторная работа 2.
Фильтрация данных и работа с функциями базы данных в организованных списках электронной таблицы
Цель работы
Освоить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.
Подготовка к работе
По указанной литературе изучить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.
Контрольные вопросы
Дать понятие фильтра.
Функция автофильтра.
Функция расширенного фильтра.
Понятие поля.
Понятие критерия.
Понятие базы данных.
Работа с функциями базы данных.
Задание на выполнение и методические указания
I. Фильтрация записей с помощью функции автофильтра
Для выполнения задания скопировать итоговый список сотрудников из 1 лабораторной работы (рисунок1.1) в новую книгу.
Установить автофильтр (меню "Данные"-"Фильтр"-"Автофильтр") и, выбирая соответствующие должности, принять на работу новых сотрудников. Штат должен содержать троих менеджеров, четырех инженеров, двух секретарей, двух референтов, одного водителя, директора и заместителя директора (с одинаковыми окладами для одинаковых должностей).
Применяя автофильтр и функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ, которая вызывается кнопкой на панели инструментов "Стандартная", определить:
сколько в фирме женщин и каков их средний заработок;
список Совета Старейшин (трое самых великовозрастных, независимо от пола);
какой максимальный оклад имеет сотрудница, не получающая надбавку;
каков суммарный заработок у менеджеров и инженеров;
сколько в фирме работает Ивановых, и каков их суммарный оклад;
сколько сотрудников получают больше 9000 руб. или меньше 5000 руб., и кто из них не получает надбавки;
составить список трех самых молодых инженеров.
II. Расширенная Фильтрация
Вызвать справочную систему Excel, познакомиться со справочным материалом по этой теме, рассмотреть примеры.
Для выполнения задания скопировать "Ведомость оценок" (рисунок 2.1 из 1 работы), расположить ее в начале чистого листа, добавить в таблицу столбец "Ср. балл".
Определить область критериев справа от таблицы.
Извлечь (на месте) данные о студентах, имеющих:
средний балл меньше 4;
средний балл больше, чем 3,5 и оценку по математике больше 3;
4 и 5 по всем предметам;
средний балл больше, чем 3,5, но меньше 4 (реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца.).
средний балл не меньше, чем 4,5 или меньше 4, но по биологии – 5
Определив область для извлечения данных под таблицей, найти студентов, имеющих:
тройки по математике;
тройки по математике, но четверки по физике;
двойки по математике, но пять по физике и средний балл больше 3,5;
тройки по математике или тройки по физике;
двойку по любому предмету (хотя бы одну).
Извлечь только фамилии студентов:
не имеющих двоек;
не имеющих двоек и средний балл не меньше 4;
Примечание. Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, ввести название заголовка столбца "Фамилия".
Предъявить результаты преподавателю.
III. Работа с функциями Базы данных
Для выполнения задания скопировать итоговый список сотрудников из 1 лабораторной работы (рисунок 1) в новую книгу.
Добавьте к этому списку еще один столбец с названием «Заработная плата» заполнив его с помощью функции =СЛЧИС()*(25000-3,5)+3,5и отформатируйте данный столбец как денежный.
Добавьте критерий в данную таблицу. Рисунок1.2.
Рисунок 1.2.
С помощью соответствующей функции работы с базой данных определить и вывести в свободную ячейку Листа "Отчет" следующий параметр:
Суммарную заработную плату секретарей;
Найти Фамилию сотрудника получающего максимальную заработную плату;
Найти фамилию сотрудника год рождения 1936, пол женский;
Подсчитать количество телефонов у референтов;
Найти занимаемую должность сотрудника имеющего минимальную заработную плату;
Телефон самого пожилого сотрудника;
Занимаемая должность самого молодого сотрудника.
Лабораторная работа №3
Построение сводной таблицы с помощью электронных таблиц Excel
1. Подготовка к работе
По указанной литературе изучить методику создания сводных таблиц и диаграмм, порядок работы с помощью пакета Excel, ответить на контрольные вопросы.
2. Контрольные вопросы
2.1. Для чего используются сводные таблицы?
2.2. Представление отчета сводной таблицы?
2.3. Опишите структуру пользовательского интерфейса пакета Excel.
2.4. Для чего предназначен отчет сводной таблицы?.
2.5.Перечислите этапы создания сводного отчета.
2.6. Как изменить макет сводной таблицы?
2.7. Как осуществляется фильтрация данных в сводных таблицах?
2.8. Как построить сводную диаграмму с помощью пакета Excel?
2.9. Как произвести сортировку данных в сводном отчете?
2.10. Изменение положения полей в сводной таблице.
3. Задания на выполнение лабораторной работы
ЗАДАНИЕ 1. Построить сводную таблицу. Научиться пользоваться интерфейсом сводной таблицы.
Создать таблицу, приведенную на рисунке 1.1
Отформатировать ее произвольным образом, но так чтобы названия телефонов сортировались по цветам, каждой марки телефона свой цвет.
Рисунок 1.1.
На основе этой таблицы создать сводный отчет, для этого необходимо перейти на вкладку «Вставка» как показано на рисунке 1.2
Рисунок 1.2
Выбрать команду «Сводная таблица» в результате появится окно (Рисунок 1.3).
Рисунок 1.3
Указываем диапазон таблицы и помещаем отчет сводной таблицы на новый лист. В результате у Вас получится следующая таблица (Рисунок 1.4)
Рисунок 1.4
Для того чтобы Ваша таблица выглядела подобным образом Вам необходимо проделать следующие операции, перенести названия полей в названия столбцов и строк. Для этого Вам необходимо воспользоваться кнопкой «Список полей» (Рисунок 1.5)
Рисунок 1.5.
Появится окно (Рисунок 1.6), в котором Вы сможете расположить поля как предложено в задании.
Рисунок 1.6
Как мы видим, поле «Сумма с продаж» названа не удачно, ее следует переименовать на «Деньги», проделав это в основной таблице, Вы должны обновить макет в результате проделанных действий у Вас должна получиться следующая таблица (Рисунок 1.7).
Рисунок 1.7
Для того чтобы макет обновился Вам необходимо воспользоваться кнопками «Изменить источник данных», а затем «Обновить» (Рисунок 1.8).
Рисунок 1.8
Далее следует переделать макет, как показано на рисунке 1.9, перетащив поле «Квартал» из названия столбцов в название строк.
Рисунок 1.8
Самостоятельно.
Сделать фильтр таблицы, оставив только телефоны Nokia.
Сделать фильтр таблицы, оставив данные по 1 кварталу телефона Panasonic.
Сделать фильтр таблицы, оставив данные по 4 кварталу.
Преобразовать таблицу, убрав поле продажи.
ЗАДАНИЕ 2. Построить сводную диаграмму.
Научиться пользоваться интерфейсом сводной диаграммы.
Для построения диаграммы воспользуемся кнопкой «Сводная диаграмма» и перенесем полученную диаграмму на другой лист, применив команду «Копировать» и «Вставить» (Рисунок 1.9)
Рисунок 1.9
Так как на этой сводной диаграмме плохо видно объем продаж, разделим данную диаграмму на две 1 «Объем продаж телефонов», а вторая полученные деньги с продаж (Рисунок 1.10) Для этого нужно убрать галочку с поля «Деньги» или «Продажи».
 
Рисунок 1.10
Для диаграммы «Деньги с продажи телефонов» необходимо поменять формат оси на «Денежный».
Самостоятельно.
Постройте круговую диаграмму продаж телефона Nokia.
Постройте график деньги с продаж за 4 квартал.
|
Скачать 0.98 Mb.