Лабораторные работы по EXCEL. Лабораторная работа Сортировка и обработка списков связанных данных в электронной таблице
Лабораторная работа 1.
Сортировка и обработка списков связанных данных в электронной таблице
Знакомство с методами обработки данных, организованных в списки, сортировкой, редактированием, просмотром, поиском и извлечением данных по различным критериям.
По указанной литературе изучить приёмы работы со списками, сортировка списка по строкам, столбцам, обработка списка с помощью формы, изменение структуры таблицы.
- Дать понятие списков связанных данных (ССД).
- Дать понятие записи.
- Дать понятия поля.
- Добавление данных к существующему списку.
- Сортировка списка по строкам.
- Сортировка списка по столбцам.
- Сортировка данных по заданному условию.
- Обработка списков с помощью формы.
- Пользуясь командами » Данные»-«Сортировка» и «Данные»-«Итоги «, проделайте задание 3 и 4.
- Укажите, сколько уровней сортировки нужно использовать, название полей, по которым производится сортировка, и направление сортировки по каждому уровню.
- Предварительно скопируйте исходную таблицу на отдельные листы или используйте режим групповой работы с листами (результат выполнения каждого пункта должен располагаться на отдельном листе или ниже в этом же листе).
- Есть ли в фирме женщины-секретари?
- Сколько сотрудников с фамилией Иванов работают в фирме и кто из них самый молодой? (Предварительно добавьте столбец Возраст).
- Каков средний возраст мужчин и женщин, работающих в фирме?
- Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей?
Лабораторная работа 2.
Фильтрация данных и работа с функциями базы данных в организованных списках электронной таблицы
Освоить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.
По указанной литературе изучить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.
- Дать понятие фильтра.
- Функция автофильтра.
- Функция расширенного фильтра.
- Понятие поля.
- Понятие критерия.
- Понятие базы данных.
- Работа с функциями базы данных.
- Для выполнения задания скопировать итоговый список сотрудников из 1 лабораторной работы (рисунок 1) в новую книгу.
- Добавьте к этому списку еще один столбец с названием «Заработная плата» заполнив его с помощью функции =СЛЧИС()*(25000-3,5)+3,5и отформатируйте данный столбец как денежный.
- Добавьте критерий в данную таблицу. Рисунок1.2.
- С помощью соответствующей функции работы с базой данных определить и вывести в свободную ячейку Листа «Отчет» следующий параметр:
- Суммарную заработную плату секретарей;
- Найти Фамилию сотрудника получающего максимальную заработную плату;
- Найти фамилию сотрудника год рождения 1936, пол женский;
- Подсчитать количество телефонов у референтов;
- Найти занимаемую должность сотрудника имеющего минимальную заработную плату;
- Телефон самого пожилого сотрудника;
- Занимаемая должность самого молодого сотрудника.
Построение сводной таблицы с помощью электронных таблиц Excel
По указанной литературе изучить методику создания сводных таблиц и диаграмм, порядок работы с помощью пакета Excel, ответить на контрольные вопросы.
2.3. Опишите структуру пользовательского интерфейса пакета Excel.
2.7. Как осуществляется фильтрация данных в сводных таблицах?
2.8. Как построить сводную диаграмму с помощью пакета Excel?
2.9. Как произвести сортировку данных в сводном отчете?
ЗАДАНИЕ 1. Построить сводную таблицу. Научиться пользоваться интерфейсом сводной таблицы.
Отформатировать ее произвольным образом, но так чтобы названия телефонов сортировались по цветам, каждой марки телефона свой цвет.
На основе этой таблицы создать сводный отчет, для этого необходимо перейти на вкладку «Вставка» как показано на рисунке 1.2
Выбрать команду «Сводная таблица» в результате появится окно (Рисунок 1.3).
Указываем диапазон таблицы и помещаем отчет сводной таблицы на новый лист. В результате у Вас получится следующая таблица (Рисунок 1.4)
Для того чтобы Ваша таблица выглядела подобным образом Вам необходимо проделать следующие операции, перенести названия полей в названия столбцов и строк. Для этого Вам необходимо воспользоваться кнопкой «Список полей» (Рисунок 1.5)
Появится окно (Рисунок 1.6), в котором Вы сможете расположить поля как предложено в задании.
Как мы видим, поле «Сумма с продаж» названа не удачно, ее следует переименовать на «Деньги», проделав это в основной таблице, Вы должны обновить макет в результате проделанных действий у Вас должна получиться следующая таблица (Рисунок 1.7).
Для того чтобы макет обновился Вам необходимо воспользоваться кнопками «Изменить источник данных», а затем «Обновить» (Рисунок 1.8).
Далее следует переделать макет, как показано на рисунке 1.9, перетащив поле «Квартал» из названия столбцов в название строк.
- Сделать фильтр таблицы, оставив только телефоны Nokia.
- Сделать фильтр таблицы, оставив данные по 1 кварталу телефона Panasonic.
- Сделать фильтр таблицы, оставив данные по 4 кварталу.
- Преобразовать таблицу, убрав поле продажи.
Научиться пользоваться интерфейсом сводной диаграммы.
Для построения диаграммы воспользуемся кнопкой «Сводная диаграмма» и перенесем полученную диаграмму на другой лист, применив команду «Копировать» и «Вставить» (Рисунок 1.9)
Так как на этой сводной диаграмме плохо видно объем продаж, разделим данную диаграмму на две 1 «Объем продаж телефонов», а вторая полученные деньги с продаж (Рисунок 1.10) Для этого нужно убрать галочку с поля «Деньги» или «Продажи».
Для диаграммы «Деньги с продажи телефонов» необходимо поменять формат оси на «Денежный».
[expert_bq id=»1570″]Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида Чтобы сохранилось соответствие значений в строках, выбираем действие автоматически расширить выделенный диапазон. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Иногда можно столкнуться с тем, что стандартные инструменты сортировки в Excel не способны сортировать данные в необходимом порядке. К счастью, Excel позволяет создавать настраиваемый список для собственного порядка сортировки.
Как сделать сортировку списка в excel?
- Пользуясь командами » Данные»-«Сортировка» и «Данные»-«Итоги «, проделайте задание 3 и 4.
- Укажите, сколько уровней сортировки нужно использовать, название полей, по которым производится сортировка, и направление сортировки по каждому уровню.
- Предварительно скопируйте исходную таблицу на отдельные листы или используйте режим групповой работы с листами (результат выполнения каждого пункта должен располагаться на отдельном листе или ниже в этом же листе).
- Есть ли в фирме женщины-секретари?
- Сколько сотрудников с фамилией Иванов работают в фирме и кто из них самый молодой? (Предварительно добавьте столбец Возраст).
- Каков средний возраст мужчин и женщин, работающих в фирме?
- Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей?
Нажатие в диалоге сортировки
коробка показывает несколько разных вариантов для изменения ваших сортов, хотя некоторые из
они, если вы точно не знаете, как каждый из них влияет на ваш вид, скорее всего,
привести к нежелательным результатам.
