Лабораторная работа №10 Автоматическое подведение итогов в MS Excel.

Итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Итоги подразделяются на:

• простые промежуточные;

• сложные промежуточные;

• связанные с вычислением частичных сумм (используется мастер частичных сумм).

Задание 1. Дан список со следующими полями (рис. 46): № пп, Продавец, Товар, Номер партии, Цена, Количество, Итого, Дата продажи, Покупатель. Определить количество товаров, проданных

конкретным продавцом за конкретную дату.

Решение

1. Выделите список (или — установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала — по полю Продавец, затем — по полю Дата продажи (рис. 47).

Рис. 46. Список продаж

2. Примените команду Данные | Итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 48: для получения верхнего (первого) уровня итогов — общее количество товаров, проданных конкретным продавцом.

 

Рис. 47.Сортировка списка

Рис. 48. Окно Промежуточные итоги для получения итогов по полю Продавец

3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 49.

Рис.49. Окно Промежуточные итоги для получения итогов по полю Дата продажи

 

4. Полученные промежуточные итоги представлены на рис. 50.

Рис. 50.Вложенные промежуточные итоги

Консолидация данных

Консолидация предназначается для обобщения однородных данных. Ее осуществление предполагает использование следующей методики:

1. Указать местоположение будущих консолидированных данных.

2. Выбрать команду Данные | Консолидация.

3. В открывшемся окне указать диапазоны данных, подлежащие консолидации.

4. Указать способ консолидации:

• согласно расположению в диапазоне — сняты все флажки области Использовать в качестве имен;

• согласно заголовкам строк и столбцов— установлены

флажки подписи верхней строки и значения левого столбца.

5. Выбрать тип консолидации, т. е. указать, какая операция будет проводиться с консолидируемыми данными.

6. При необходимости указать добавление структуры — установить флажок Создавать связи с исходными данными.

Задание 1. Объединить данные о количестве и стоимости проданных товаров в сети магазинов, которые представлены в виде списка со следующими полями (рис. 51): Товар, Стоимость, Количество, расположены на листе 2, листе 4 и листе 5.

Решение: Следуя рекомендациям методики проведения консолидации, заполните окно

Рис. 51.Данные о реализованных товарах

Консолидация в соответствии с данными рис. 52. Объединенные данные представлены на рис. 53.

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

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или в таблицах, созданных в других приложениях. Внешне сводные

Рис. 52. Ввод данных в окно Консолидация

Рис. 53. Представление консолидированных данных

 

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

 

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, связанных в других приложениях. Внешние сводные таблицы являются структурой, позволяющей размещать данные в трехмерном виде. Сводные таблицы могут использоваться:

• для обобщения большого количества однотипных данных;

• для реорганизации данных (с помощью перетаскивания);

• для отбора и группировки данных;

• для построения диаграмм.

Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) по следующей методике:

1. Выбрать место для сводной таблицы, т. е. установить указатель ячейки в необходимое место на рабочем листе.

2. Выполнить команду Данные | Сводная таблица.

3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мастера (рис. 54 и 55). После нажатия кнопки Далее в окне мастера, приведенном на рис. 55, откроется окно 3-го шага мастера (рис. 56).

4. Прежде чем указать местоположение будущей таблицы (рис. 56), необходимо нажать кнопку Макет и в открывшемся окне (рис. 57) сформировать макет сводной таблицы (т. е. задать страницу, строки, столбцы, итоговые и вычисляемые поля сводной таблицы).

Рис. 54Определение местоположения данных для сводной таблицы

Рис. 55.Диапазон данных для сводной таблицы

Рис. 56.Указание местоположения будущей сводной таблицы

 

5. Для определения необходимой операции для полей, помещенных в область Данные, либо задания вычисляемого поля дважды щелкнуть левой кнопкой мыши на поле, помещенном в область Данные (рис. 57), и выбрать необходимые действия в окне Вычисление поля сводной таблицы (рис. 58).

6. Нажать кнопку Параметры (рис. 56) и в открывшемся окне (рис. 59) установить необходимые параметры сводной таблицы.

Рис. 57.Формирование макета сводной таблицы

Рис. 58. Окно Вычисление поля сводной таблицы

Рис. 59. Установка параметров сводной таблицы

7. После проведения всех подготовительных операций нажать кнопку Готово (рис. 56).

При создании, редактировании и работе со сводными таблицами необходимо учитывать следующее:

• местоположение сводной таблицы — желательно располагать ее на отдельном листе, т. к. при обновлении, группировках сводной таблицы информация, заключающаяся на рабочих листах рядом со сводной таблицей, может оказаться скрытой;

• местонахождение исходных данных – список MS Excel, внешний источник, диапазоны консолидации, находящиеся в другой сводной таблице;

• необходимость при создании структуры сводной таблицы определить:

1) поля, находящиеся в строках и столбцах таблицы;

2) поля, по которым подводятся итоги (с выбором необходимой операции);

3) поля для страниц, что позволяет представить информацию в трехмерном виде.

• сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице необходимо внести изменения в источник данных, а затем обновить сводную (кнопкой Обновить данные на панели инструментов Сводные таблицы (рис. 60);

Рис. 60. Панель инструментов Сводные таблицы

• в сводных таблицах можно изменять названия полей, что не влечет изменений в полях исходных данных. Манипулирование элементами сводной таблицы можно также осуществлять мышью.

• сводные таблицы допускают возможность группировки элементов полей по различным уровням иерархии путем объединения в группы. Для этой цели в меню Данные | Группа и структура существуют две кнопки Группировать и Разгруппировать. Группы эти можно переименовывать по желанию.

• возможность построения диаграмм на основе сводных таблиц.

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

Решение

1. Выделите подготовленный диапазон данных либо установите в него указатель ячейки.

2. Выполните команду Данные | Сводная таблица.

3. Работая с мастером сводных таблиц, определите все необходимые элементы сводной таблицы.

4. Выполните группировку по полю Год выпуска — после создания сводной таблицы поочередно выделите мышью необходимые года выпуска: 1978—1990, 1991—1995, 1996—1999 и

воспользуйтесь командой Группировать контекстного меню Группа и структура либо соответствующей командой меню Данные | Группа и структура.

5. Отформатируйте сводную таблицу, вызвав кнопкой Формат отчета панели инструментов Сводные таблицы диалоговое окно Автоформат с вариантами форматирования.

6. Подготовленная сводная таблица представлена на рис. 70.

Рис. 70. Пример сводной таблицы

 

ВАРИАНТ I.

 

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Марка машины

Цифры номера

Буквы номера

Год выпуска

Год приобретения

Цвет машины

Пробег

Цена

Владелец

Задания на использование автофильтрации

• Определить белые Ауди, год выпуска которых больше 1990, но меньше либо равен 1996. Отсортировать полученные данные по возрастанию года выпуска автомобилей.

• Определить красные Форды, пробег которых больше либо равен 150 000 км, а цена — меньше либо равна 10 000 у. е. Отсортировать полученные данные по убыванию цены.

• Определить автомобили, год приобретения которых больше либо равен 1997, и цена находится в следующих пределах: от 5000 до 14 000 у. е. Отсортировать эти данные сначала по возрастанию года выпуска, а затем — по убыванию цены.

• Определить желтые автомобили, пробег которых меньше либо равен 50000 км, и год выпуска 1998. Отсортировать полученные данные сначала по марке автомобиля (по алфавиту), а затем — по возрастанию пробега автомобилей.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

 

• Определить белые или черные Мерседесы или Ауди, год выпуска которых больше 1990 года, а пробег находится в пределах от 150 000 до 200 000 км.

• Определить машины, год выпуска и год приобретения которых совпадает, а также— вторая буква номера которых "С" или "X".

• Определить машины, цена которых не превосходит 15% средней цены для машин 1996 года выпуска, или машины, пробег которых больше максимального пробега для черных Мерседесов.

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Средняя, максимальная и минимальная цены определенной марки машины с учетом конкретного года приобретения.

• Суммарный и средний пробег определенной марки машины с учетом конкретного года выпуска.

• Количество и средняя цена машин определенного цвета с учетом конкретного года приобретения.

• Суммарный пробег для конкретного года выпуска машин, учитывающий среднюю цену данной марки машины.

Задания на использование консолидации

Определить среднюю цену автомобилей данных марок, которые реализуются некоторой сетью магазинов.

Задания на использование сводных таблиц

Создать сводную таблицу, выводящую для каждой марки автомобилей итоги по суммарному пробегу, средней цене и вычисляемому полю — цена из у. е. переводится на российские рубли (или наоборот). Произвести следующие группировки:

• фамилии объединить по первой букве алфавита (А, Б, В и т. д.);

• год выпуска объединить с интервалом в 3 года.

ВАРИАНТ II.

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Дата

Город

Вид осадков

Количество осадков

Температура

Давление

Направление ветра

Сила ветра

Задания на использование автофильтрации

• Определить города, температура в которых за конкретную дату превышала 9 °С. Отсортировать полученные данные сначала по городу, а затем — по возрастанию температуры.

• Определить данные по погоде для конкретного города за конкретный промежуток времени. Отсортировать полученные данные сначала по виду, а затем — по возрастанию количества осадков.

• Определить города, в которых наблюдалось северо-восточное направление ветра за конкретный период времени. Отсортировать эти данные сначала по городам, а затем — по возрастанию даты.

• Определить города, температура в которых наблюдалась в пределах от 5 до 14 °С за конкретную дату. Отсортировать полученные данные сначала по городам, а затем — по возрастанию температуры.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

• Определить города, для которых направление ветра — северное или северо-западное, температура воздуха в которых больше 8 °С, но меньше 12 °С.

• Определить данные о погоде для Санкт-Петербурга или Минска за некоторый конкретный промежуток времени.

• Определить города, в которых за конкретный промежуток времени выпал снег или снег с дождем, а также — температура в которых находилась в пределах от -5 °С до +3 °С.

 

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Среднее количество осадков данного вида с учетом данного города и конкретной даты.

• Суммарное и среднее количество осадков данного вида для конкретного города.

• Количество случаев с определенным направлением ветра с учетом конкретной даты.

• Средние температуру и давление для конкретного города с учетом конкретной даты.

• Средняя сила ветра определенного направления для конкретного города.

Задания на использование консолидации

Определить среднее количество осадков, средние температуру и давление для некоторых городов области.

Задания на использование сводных таблиц

Создать сводную таблицу, выводящую для каждого города по конкретной дате суммарное количество осадков, среднюю температуру и по вычисляемому полю, переводящему среднее давление из мм рт. ст. в гектопаскали. Произвести следующие группировки:

• по дате с некоторым интервалом;

• объединить некоторые виды осадков по конкретному признаку.

 

ВАРИАНТ III.

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Фами-лия

Имя

Отчество

Дата рождения

Год поступле-ния

Факуль- тет

Курс

Спортив- ный норматив

Резуль- тат

Оценка (балл)

Задания на использование автофильтрации

• Определить студентов конкретного года рождения, оценки которых за спортивные нормативы больше 3. Отсортировать эти данные сначала по факультету, затем — по фамилии студента.

• Определить студентов конкретного года рождения, которые сдали определенный норматив. Отсортировать эти данные сначала по году поступления студентов, а затем — по результатам сдачи.

• Определить студентов конкретного факультета, которые сдали определенный норматив. Отсортировать эти данные сначала по курсу, а затем — по результатам сдачи.

• Определить студентов конкретного курса, оценка которых за сдачу спортивного норматива больше 2, но меньше либо равна 4. Отсортировать полученные данные сначала по факультету, а затем — по оценке.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

• Определить студентов одного года рождения, обучающихся на математическом, физико-техническом или экономическом факультетах, сдавших с оценкой "4" бег на 100 м или с оценкой "5" прыжки в длину.

• Определить студентов первого или второго курса исторического и юридического факультетов, оценки которых за спортивный норматив больше либо равны средней оценке для всехстудентов.

• Определить студентов с максимальными показателями (оценками) по всем спортивным нормативам для каждого курса и факультета.

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Количество однофамильцев и их средний балл с учетом года поступления.

• Суммарный балл для каждого факультета с учетом вида спортивного норматива.

• Средний результат для данного спортивного норматива с учетом курса и факультета.

• Средние балл и результат по данному спортивному нормативу, учитывающие суммарные балл и результат по каждому факультету и группе.

Задания на использование консолидации

Суммарный балл за конкретные спортивные нормативы для данных групп.

Задания на использование сводных таблиц

Создать сводную таблицу, выводящую для данного курса и факультета суммарный балл, средний результат. Вычисляемое поле — Результат, деленный на 250. Произвести следующие группировки:

• по году поступления (с некоторым интервалом);

• по виду спортивного норматива (объединить близкие виды нормативов в группы).

ВАРИАНТ IV.

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Продавец (фирма)

Товар

Страна-импортер

Количество

Цена

Дата

Покупатель (фирма)

Задания на использование автофильтрации

• Определить товары, цена которых находится в некоторых пределах и которые проданы за конкретную дату. Отсортировать полученные данные сначала по стране-импортеру, а затем — по фирме-продавцу.

• Определить фирмы-покупатели, количество купленных товаров которых за конкретную дату превысило 100 единиц. Отсортировать полученные данные сначала по товару, затем — по возрастанию цены товара.

• Определить страны-импортеры, продающие конкретные виды товаров (например, дискеты и бумагу для офиса), цена которых больше некоторого значения. Отсортировать эти данные сначала по стране, а затем — по виду товаров.

• Определить товары, проданные за конкретный промежуток времени некоторой страной-импортером. Отсортировать полученные данные сначала по наименованию товара, а затем — по дате продажи.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

• Определить товары, проданные фирмами-продавцами из Франции, Германии или Бельгии, количество которых больше 100 единиц и меньше либо равно 1000 единиц.

• Определить фирмы-покупатели из стран России или Беларуси, купившие товар за конкретный период времени и по цене, меньшей или равной средней цены для фирм-продавцов из Германии.

• Определить фирмы-продавцов с максимальным и минимальным объемами продаж.

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Средние цену и количество проданных товаров конкретной фирмой-продавцом за конкретную дату.

• Общее количество товаров данного вида с учетом среднего количества товаров, проданных конкретной страной.

• Общее количество товаров, купленное фирмой-покупателем с учетом вида товара и его средней цены.

• Количество торговых сделок за конкретную дату, учитывающих общее количество и среднюю цену данного вида товара.

Задания на использование консолидации

Определить общее количество некоторых видов товаров, проданных через магазины некоторой фирмы.

Задания на использование сводных таблиц

Создать сводную таблицу, выводящую для данного вида товара среднюю цену и суммарное количество. Вычисляемое поле— Стоимость товара. Произвести следующие группировки:

• по дате с некоторым интервалом;

• по фирме-продавцу (объединить фирмы, реализующие товары одинаковой номенклатуры).

 

ВАРИАНТ V.

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

ФИО

Курс

Группа

Тема курсовой работы

Научный руководитель

Кафедра

Дата выдачи задания

Дата защиты

Оценка

 

Задания на использование автофильтрации

• Определить студентов данного научного руководителя, защитивших курсовые работы на 4 и 5. Отсортировать эти данные сначала по дате выдачи курсового задания, затем — по фамилии студентов.

• Определить работы, выданные не позднее конкретного числа и защищенные до конкретной даты включительно. Отсортировать полученные данные сначала по кафедре, затем — по научному руководителю.

• Определить студентов данного курса, имеющих курсовые работы по заданной кафедре. Отсортировать полученные данные сначала по группе, а затем — по научному руководителю.

• Определить студентов данной группы и данного курса, защитивших курсовые работы не позднее конкретного числа. Отсортировать полученные данные сначала по кафедре, затем — по оценке.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

• Определить студентов данного научного руководителя, получивших "4" или "5" по курсовой работе и сдавших работу до определенного числа.

• Определить студентов третьего или четвертого курса, специализирующихся на кафедре ИВТ или на кафедре математического анализа, оценка которых за курсовую работу больше либо равна средней оценке для студентов четвертого курса.

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Количество курсовых работ, выданных с конкретной датой на данной кафедре конкретным научным руководителем.

• Средняя оценка по курсовым работам за конкретную дату защиты по конкретным курсам и группам.

• Количество курсовых работ, защищенных с конкретной датой, со средней оценкой по данному курсу.

Задания на использование консолидации

Определить среднюю оценку по защите курсовых работ для данного научного руководителя по данным защиты за несколько дней.

Задания на использование сводных таблиц

Создать сводную таблицу, выводящую для каждого курса и группы максимальную, минимальную и среднюю оценки.

Вычисляемое поле— Время, прошедшее от даты выдачи курсового проекта до защиты. Произвести следующие группировки:

• фамилии студентов объединить по первой букве алфавита (А, Б, В и т. д.);

• по дате выдачи курсового проекта (с некоторым интервалом).

ВАРИАНТ VI.

Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.

Название книги

Автор

Тема книги

Год издания

Место издания (город)

Издательство

Кол-во страниц

Тираж

Цена

Задания на использование автофильтрации

• Определить книги данного года издания, тираж которых находился в некоторых пределах. Отсортировать полученные данные сначала по городу издания, а затем — по издательству.

• Определить книги заданной темы, цена которых находится в некоторых пределах. Отсортировать эти данные сначала по году издания, а затем — по цене.

• Определить авторов книг, публикующихся по конкретной теме, место издания книг которых— два конкретных города. Отсортировать полученные данные сначала по тиражу, а затем — по цене книг.

• Определить книги, количество страниц которых больше заданного числа, а тираж находится в некоторых пределах. Отсортировать эти данные сначала по теме, затем — по цене книг.

Задания на использование расширенного фильтра. Выполнить каждое задание двумя способами: с помощью расширенного фильтра и с помощью функции условий.

• Определить книги, цена которых отличается не более, чем на 15% для книг математической тематики, или не более 25% для книг биологической тематики.

• Определить книги, изданные в Москве или Минске, тираж которых составляет 50% от тиража книг, изданных в Киеве, или больше либо равен максимальному тиражу книг, изданных в Санкт-Петербурге.

• Определить книги, изданные в Санкт-Петербурге или Минске, средняя цена которых составляет 70% от средней цены книг, изданных в Москве, или количество страниц которых находится в пределах от 200 до 350.

Задания на подведение промежуточных итогов. Вывести следующие итоги.

• Суммарный тираж книг данной темы, учитывающий средний тираж каждого года издания.

• Средняя цена книг данного автора с учетом суммарного тиража за конкретный год издания.

• Среднее количество страниц для данного издательства за конкретный год издания.

• Общее количество страниц для данного автора с учетом средней цены для конкретной темы.

Задания на использование консолидации

Определить общий тираж книг данной тематики, издающихся в различных городах.

Задания на использование сводных таблиц

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

• объединить по году издания (с некоторым интервалом);

• фамилии авторов объединить по двум буквам алфавита (А-Б, В-Г и т. д.).