Лабораторная работа № 1. Основы EXCEL / Раздел 3. Обработка данных / ТУСУР / ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ИНФОРМАТИКЕ С. Л. Миньков

Раздел 3. Обработка данных

3.1 Сортировка и фильтрация

Поместить на листе Excel табл. 3.1 и, используя ее данные, создать на этом же листе новую (рабочую) таблицу с относительными величинами, разделив все параметры на соответствующий параметр Земли. Не забудьте изменить названия полей в новой таблице!

Для проведения дальнейших операций с полученной таблицей необходимо, чтобы в ее ячейках находились числовые значения, а не формулы, их вычисляющие1. Замена производится с помощью опции Специальная вставка

Задание 3.1. Сортировка

В полученной таблице, используя вкладку Сортировка и фильтр ленты Главная или ленты Данные (рис. 3.1), выполнить следующие операции:

1) отсортировать данные в порядке убывания количества спутников;

2) отсортировать данные в алфавитном порядке названий планет;

3) отсортировать данные в порядке возрастания массы.

Разместить все результаты сортировок на одном листе рабочей книги.

Задание 3.3. Автофильтр

С помощью Автофильтра найти:

1) планеты, имеющие диаметр менее 4-х диаметров Земли и период обращения более 80 земных лет;

2) планеты, находящиеся от Солнца на расстоянии не менее 0,5 расстояния от Земли, имеющие массу от одной до 100 масс Земли и не более 2-х спутников;

3) три планеты, имеющие самый большой диаметр.

Разместить результаты фильтрации на различных листах рабочей книги.

Задание 3.4. Расширенный фильтр

С помощью Расширенного фильтра найти:

1) планеты с периодом обращения от 10 до 100 земных лет и количеством спутников не более 15;

2) планеты, у которых либо диаметр не менее 4-х земных, либо масса более 100 земных масс;

3) среди планет без спутников тех, которые находятся от Солнца на расстоянии менее половины земного, а среди планет с не менее чем 14-ю спутниками тех, которые находятся от Солнца не ближе чем 10 земных расстояний.

Результаты поместить на одном листе в последовательности: исходная таблица, условия, результат, условия, результат и т. д.

Задание 3.5. Создание сводной таблицы

Заполнить табл. 3.2, занеся недостающие данные и рассчитав процент удержания с начисленной суммы по следующему правилу: при количестве иждивенцев более трех — 0 %, при трех — 5 %, при двух — 10 %, при одном — 12 %, если нет — 14 %. Расчет оформить с помощью вложенных функций ЕСЛИ.

На основе построенной таблицы создать сводную таблицу, найдя итоги (суммы) по полям Всего начислено, Всего удержано, Сумма к выдаче по каждой фамилии, расположив фамилии в алфавитном порядке, и провести фильтрацию отдельно по категории Количество иждивенцев и по категории Отдел. Поместить сводную таблицу на отдельном листе. Постарайтесь создать компактную и наглядную сводную таблицу, избежав излишнего текста и пустых ячеек.

На основе сводной таблицы сформировать отчет по лицам, работающим в третьем отделе и имеющим по одному иждивенцу.

Задание 3.6. Получение промежуточных итогов

На таблице 3.2 получить промежуточные итоги во всех от-делах по позициям Количество иждивенцев, Всего начислено, Всего удержано и Сумма к выдаче. Изучить и описать структуру полученной таблицы.

Задание 3.7. Формула связи

Занести табл. 3.3 на три листа (не забудьте о возможности, описанной в п. 1.4!). Рассчитать выручку и изменить название листов на Январь, Февраль, Март. Подкорректировать заголовки таблиц и изменить данные второго и третьего столбцов (по вашему усмотрению).

На четвертом листе (Квартал. Способ 1) создать таблицу итоговых показателей (Продано и Выручка за квартал) по всему ассортименту продукции за квартал и заполнить эту таблицу, суммируя данные, находящиеся в соответствующих ячейках ли-стов показателей за январь-март.

На пятом листе (Квартал. Способ 2) получить аналогичную таблицу, но с помощью консолидации.

Задание 3.8. Консолидация

Выбрав данные из табл. 3.4, разнести их поквартально по разным листам одного файла. Определить среднюю цену производителей по каждой позиции в каждом квартале (в тыс. руб.) и соотношение цен на отдельные виды энергоресурсов с ценой на нефть (в процентах). В другом файле с помощью консолидации сформировать таблицу со среднегодовыми данными по ценам и соотношению цен.

Задание 3.9. Создание формы данных

Создать форму данных для таблицы 3.1.

ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ИНФОРМАТИКЕ, автор С. Л. Миньков

Лабораторная работа № 1. Основы EXCEL ................................ 9

Раздел 3. Обработка данных ....................................................... 53

3.1 Сортировка и фильтрация ..................................................... 53

Задание 3.1. Сортировка .......................................................... 54

Задание 3.2. Фильтрация .......................................................... 55

Задание 3.3. Автофильтр .......................................................... 56

Задание 3.4. Расширенный фильтр ......................................... 59

3.2 Сводные таблицы .................................................................. 59

Задание 3.5. Создание сводной таблицы ................................ 61

3.3 Промежуточные итоги .......................................................... 62

Задание 3.6. Получение промежуточных итогов .................. 63

3.4 Связь таблиц (консолидация данных) ................................. 63

Задание 3.7. Формула связи ..................................................... 65

Задание 3.8. Консолидация ...................................................... 66

3.5 Форма данных ........................................................................ 67

Задание 3.9. Создание формы данных.................................... 68