Часть 2. Итоги.
Используя функцию «Итоги» из меню «Данные» можно выполнить детальный анализ сводных показателей для групп данных, объединенных каким-либо общим признаком. Чтобы формирование итогов имело смысл, необходимо предварительно отсортировать исходные данные по желаемому признаку.
Команда «Данные» - «Сортировка» позволяет упорядочить строки в списке в соответствии с содержимым определенных столбцов. Для этого нужно выделить сортируемые строки в списке или его части, определить требуемый порядок сортировки.
Сортировка осуществляется в выделенной области, порядок сортировки данных указывается по значениям одного, двух или трех столбцов по возрастанию или убыванию. Причем для результата сортировки существенен порядок выбора столбцов. При сортировке по возрастанию упорядочению записей по значению указанного столбца идет следующим образом – от меньшего к большему, по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию использует обратный порядок (исключение – пустые ячейки, расположенные в конце списка).
Кроме того, вы можете использовать пользовательский порядок сортировки. При этом программа использует порядок, заданный в созданном пользовательском списке. Пользовательский список, задающий порядок сортировки, можно выбрать в окне диалога «Параметры сортировки».
К
25
С помощью списка «при каждом изменении в:» выбирается поле, по которому оно разбивается на группы записей. При первом открытии окна диалога автоматически выбрано имя первого поля. В списке «Операция» указывается функция, которая должна использоваться при определении промежуточных итогов. В поле «Добавить итоги по:» указывается поле, по которому должны быть определены промежуточные итоги. При первом открытии окна диалога по умолчанию выбирается последнее поле.
Команда «Данные» - «Итоги» может выполняться для одного и того же списка записей многократно, ранее созданные промежуточные итоги могут как заменяться новыми, так и оставаться неизменными посредством установки или снятия флажка параметра «Заменить текущие итоги». Таким образом, имеется возможность подведения итогов различных уровней вложенности.
Для того чтобы отдельные группы записей были расположены и выведены на печать на различных страницах, устанавливается флажок параметра «Конец страницы между группами».
По умолчанию промежуточные итоги будут отображены под отдельными группами записей, а общий итог – в конце списка. Для того чтобы отобразить строки с итогами над детальными данными, необходимо сбросить флажок «Итоги по данным».
Задание №2. В Excel набрать таблицу 12 и её проанализировать.
Шаг 1. Набрать таблицу 12 в Excel.
Таблица 12
Исходные данные
|
A |
B |
C |
D |
1 |
ФИО |
Товар |
Выручка |
Дата |
2 |
Иван |
стол |
20 |
25.01 |
3 |
Иван |
стул |
80 |
12.04 |
4 |
Иван |
шкаф |
60 |
25.03 |
5 |
Олег |
стол |
50 |
25.02 |
6 |
Олег |
стул |
40 |
21.03 |
7 |
Олег |
шкаф |
30 |
10.02 |
8 |
Олег |
шкаф |
10 |
16.04 |
9 |
Петр |
стол |
100 |
2.03 |
10 |
Петр |
стул |
10 |
1.01 |
11 |
Петр |
стул |
20 |
12.02 |
Примечание: В столбце D даты видоизменятся. Например, дата 12.04 будет обозначаться 12 апр.
Шаг 2. Отсортируем таблицу 12 по колонке ФИО, сумме и выручке.
Д
26
Рис.6. Диалоговое Окно «Промежуточные итоги»
В результате получаем таблицу изображенную на рис. 7
Рис. 7. Сортировка исходных данных по выручке
Задание №3. Найти итоги продаж по видам товаров. Отсортируем данные по полю Товар, а затем определим итоги по полю Товар с суммой по полю Выручка.
Шаг 1. Отсортируем таблицу 12 по полю «Товар».
Для этого на отдельный лист копируем таблицу 12, выделяем ее мышью и заходим в меню «Данные» окно «Сортировка». В поле «сортировать по..» выбираем необходимые данные и ставим галочку напротив «товар» (рис. 8).
27
Рис. 8. Диалоговое окно «Сортировка диапазона»
В результате таблица 12 принимает вид показанный на рисунке 9.
Рис. 9. Таблица данных отсортированная по полю «Товар»
Шаг 2. После сортировки данных по видам товаров необходимо выделить мышью таблицу (рис. 9) и зайти в меню «Данные», и в нем выбрать окно «Итоги» (после чего появляется окно «Промежуточные итоги»). В окне «Промежуточные итоги» в поле «при каждом изменении в:» выбираем необходимые данные, ставим галочку напротив «товар», в поле «Операция» выбираем значение «сумма», а в поле «добавить итоги по» выбираем «выручка» и нажимаем кнопку «OK» (рис. 10). В результате таблица (рис. 9) видоизменится и примет вид согласно рисунку 11.
28
Рис. 10. Диалоговое окно «Промежуточные итоги»
Рис. 11. Сортировка данных по товару, сумме и выручке
Задание №4. Самостоятельно отсортировать таблицу 13.
В таблице 13 зафиксированы платежи, поступающие из разных стран в наличном и безналичном видах за 2010 год. Подвести итоги:
1. Набрать таблицу 13 в Excel.
2. Произвести сортировку таблицы 13 по странам.
3. По каждой из стран найти сумму платежей.
4. По каждой из стран найти количество платежей.
5. Отсортировать таблицу 13 по городам.
6. По каждому из городов найти сумму платежей.
29
Таблица 13
Исходные данные
-
А
В
С
D
E
1
Страна
Город
Вид
Платеж
Дата
2
РФ
Курск
нал
50
05 янв
3
Украина
Киев
банк
100
14 янв
4
Беларусь
Минск
нал
80
25 янв
5
Беларусь
Брест
банк
24
9 февр
6
РФ
Москва
банк
90
12 февр
7
РФ
Курск
нал
300
20 февр
8
Украина
Киев
банк
120
8 март
9
Украина
Киев
банк
150
18 март
10
РФ
Москва
нал
65
24 март
11
РФ
Москва
нал
70
26 март
12
Украина
Львов
нал
200
1 апр
13
РФ
Курск
банк
120
6 апр
14
Беларусь
Минск
нал
60
8 апр
15
Беларусь
Брест
банк
90
20 апр
16
Беларусь
Минск
банк
50
21 апр
17
РФ
Москва
нал
100
25 апр
КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Что такое влияющая ячейка?
2. Назовите алгоритм графического изображения зависимостей.
3. Для чего используется функция «Итоги»?
4. Для чего используется команда «Сортировка»?
5. Каким образом осуществляется сортировка данных?
30