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

EXCEL Задачи Обобщение_ данных.

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

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

Исходная таблица

Страна	Город	Вид	Платеж (тыс.руб)	Дата
РФ	Курск	нал		5 янв
Украина	Киев	банк		14 янв
Беларусь	Минск	нал		25 янв
Беларусь	Брест	банк		9 фев
РФ	Москва	банк		12 фев
РФ	Курск	нал		20 фев
Украина	Киев	банк		8 мар
Украина	Киев	банк		18 мар
РФ	Москва	нал		24 мар
РФ	Москва	нал		26 мар
Украина	Львов	нал		1 апр
РФ	Курск	банк		6 апр
Беларусь	Минск	нал		8 апр
Беларусь	Брест	банк		20 апр
Беларусь	Минск	банк		21 апр
РФ	Москва	нал		25 апр

Сортировка.

Используя кнопки сортировки, расположить данные таким образом, чтобы с начала были упорядочены по алфавиту Страны, затем по алфавиту Города, затем в порядке уменьшения значения Платежей.

(Данные + Сортировка).

Фильтрация – Автофильтр.

Отобрать платежи, относящиеся к РФ, сделанные в феврале.

Отобрать платежи, превышающие 65 тыс. руб., относящиеся к Москве и Минску.

Фильтрация – Расширенный фильтр.

Отобрать наличные платежи, относящиеся к апрелю, поступившие из РФ и Беларуси, превышающие 55 тыс.

Итоги.

Подвести итоги по наличному/безналичному расчету.

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

Страна	Город	Вид	Платеж (тыс.руб)	Дата
РФ	Курск	нал		янв
Украина	Киев	банк		янв
Беларусь	Минск	нал		янв
				Янв Всего

Подвести следующие итоги (предварительно выполнить необходимые сортировки):

- по каждой из стран найти сумму платежей, число платежей, максимальное значение платежа;

- по каждому из городов найти сумму платежей.

Часть результата (только для Беларуси) показана в табл.

Страна	Город	Вид	Платеж (тыс.руб)	Дата
Беларусь	Брест	банк		9 фев
Беларусь	Брест	банк		20 апр
	Брест Всего
Беларусь	Минск	нал		25 янв
Беларусь	Минск	нал		08 апр
Беларусь	Минск	банк		21 апр
	Минск Всего
Беларусь Максимум
Беларусь Кол-во знач
Беларусь Всего

Консолидация.

Выполнить консолидацию платежей по городам.

Выяснить число всех наличных и безналичных платежей.

6. Сводная таблица.

Построить сводную таблицу.

Далее преобразовать ее следующим образом:

- Отобрать платежи только для городов Москва и Киев.

Действия: Двойной щелчок по кнопке Город; окно Вычисление поля сводной таблицы; Скрыть элементы (города Брест, Курск, Львов, Минск).

Показать наличные \ безналичные платежи по странам.

Действия: Перетащить кнопку Вид на горизонтальную ось, а кнопку Город – на ось Страниц.

Вид	(Все)
Дата	(Все)
Сумма по полю Платеж	Город
Страна	Брест	Киев	Курск	Львов	Минск	Москва	Общий итог
Беларусь
РФ
Украина
Общий итог

Вид	(Все)
Дата	(Все)
Сумма по полю Платеж	Город
Страна	Киев	Москва	Общий итог
РФ
Украина
Общий итог

Город	(Все)
Дата	(Все)
Сумма по полю Платеж	Вид
Страна	банк	нал	Общий итог
Беларусь
РФ
Украина
Общий итог

Показать суммы платежей по странам, городам и датам.

Действия: Перетащить кнопку Дата на горизонтальную ось, кнопку Вид – на ось Страниц, кнопку Города – на вертикальную ось, ниже кнопки Страна.

Вид	(Все)
Сумма по полю Платеж		Дата
Страна	Город	5 янв	14 янв	25 янв	9 фев	20 апр	21 апр	25 апр	Общий итог
Беларусь	Брест
	Минск
Беларусь Итог
РФ	Курск
	Москва
РФ Итог
Украина	Киев
	Львов
Украина Итог
Общий итог

Показать суммы помесячных платежей по странам.

Действия: Расположить поля по нужным осям. Щелчок правой кнопкой мыши по пиктограмме Дата или любой дате; пункт Группа и структура + Группировать; окно Группирование; в разделе с шагом выбрать Месяц.

Город	(Все)
Вид	(Все)
Сумма по полю Платеж	Дата
Страна	янв	фев	мар	апр	Общий итог
Беларусь
РФ
Украина
Общий итог

Показать суммы поквартальных платежей по городам.

Действия: С Датой выполнить операции, перечисленные выше, но выбрать пункт Квартал.

Найти максимальные выплаты по кварталам.

Действия: Щелчок правой кнопкой мыши по любому платежу в таблице; пункт Поле; окно Вычисление поля сводной таблицы; Операция; Максимум по полю Платеж.

Город	(Все)
Вид	(Все)
Максимум по полю Платеж	Дата
Страна	Кв-л1	Кв-л2	Общий итог
Беларусь
РФ
Украина
Общий итог

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

Придумайте сами и реализуйте желаемый вид сводной таблицы.

Задания по вариантам

№вар

Данные по вариантам для исходной таблицы (колонка Платеж (т.руб))