Сортування проміжних підсумків в Excel

Це глава з книги Білла Джелу Гуру Excel розширюють горизонти: робіть неможливе з Microsoft Excel .

Завдання: у вас є база даних продажів по клієнтах (рис. 1), необхідно створити діаграму, яка відображатиме обсяг продажів п’яти найбільших клієнтів. (Видно, що є кілька рядків на кожного клієнта.)

Мал.  1. Вихідна структура даних

Мал. 1. Вихідна структура даних

Завантажити замітку в форматі Word або pdf , приклади в форматі Excel

Рішення: ви можете сортувати вашу базу даних, якщо створите структуру і включіть проміжні підсумки. [1] Для цього виконайте наступне. Виберіть будь-яку клітинку в стовпці клієнтів (Customer). На вкладці ДАНІ в області Сортування і фільтри натисніть кнопку АЯ для сортування по зростанню (рис. 2).

Мал.  2. Відсортована база даних

Мал. 2. Відсортована база даних

На вкладці ДАНІ в області Структура , натисніть кнопку Проміжний підсумок .Налаштуйте параметри в діалоговому вікні Проміжні підсумки (рис. 3). Excel додає проміжні підсумки для кожного покупця і створює структуру даних. Подивіться зліва від стовпця A: Excel додав три групи і кнопки, помічені 1, 2 і 3. Кнопки дозволяють розгортати і згортати структуру.

Мал.  3. Налаштування параметрів структури даних в діалоговому вікні Проміжні підсумки

Мал. 3. Налаштування параметрів структури даних в діалоговому вікні Проміжні підсумки

Натисніть кнопку 2, щоб згорнути структуру до другого рівня — один рядок для кожного клієнта (рис. 4).

Мал.  4. Структура даних, згорнута до другого рівня

Мал. 4. Структура даних, згорнута до другого рівня

Виберіть будь-яку клітинку в стовпці Продажі ( Sales ). На вкладці ДАНІ в областіСортування і фільтри натисніть кнопку ЯА для сортування за обсягом продажів по спадаючій (рис. 5).

Мал.  5. Дані відсортовані за обсягом продажів по клієнту по спадаючій

Мал. 5. Дані відсортовані за обсягом продажів по клієнту по спадаючій

Виділіть область A1: В21. В Excel 2007 натисніть Alt + F1, щоб створити гістограму на поточній сторінці (рис. 6), або перейдіть на вкладку вставити в область Діаграми , і виберіть відповідний вид діаграми. [2]

Мал.  6. Діаграма, що відображає обсяг продажів п'яти найбільших клієнтів

Мал. 6. Діаграма, що відображає обсяг продажів п’яти найбільших клієнтів

Пояснення: тут проявилися дві дивні особливості. По-перше, ви можете сортувати структуру в згорнутому стані за проміжними підсумками. Excel насправді переставляє не по одному рядку, а відразу групи рядків під час виконання сортування. (Кожна група містить приховані рядки деталізації для кожного клієнта і видиму рядок проміжних підсумків.) По-друге, ви скористатися тим фактом, що графіки за замовчуванням не відображають приховування дані. Хоча ваш вибір області побудови діаграми включав рядки з 1 по 21, графік показує тільки видимі проміжні підсумки в рядках 5, 9, 13, 17 і 21.

Резюме: Excel коректно сортує дані згорнуті в структуру, використовуючи проміжні підсумки. Діаграма на основі структури також відображає тільки проміжні підсумки.

[1] На мій погляд, рішення за допомогою зведеної таблиці простіше і гнучкіше для подальшого використання. Детальніше див. Білл Джела, Майкл Александер.Зведені таблиці в Microsoft Excel 2013  — тут і далі прим. Багузіна

[2] За замовчуванням Excel сам розраховує мінімальну і максимальну межі по осі ординат (Y). У наведеному прикладі він встановить мінімум на ровне 42 000, що спотворить діаграму. Клацніть правою кнопкою миші на осі ординат, виберіть опціюФормат осі і встановіть значення мінімуму кордону рівне нулю. Детальніше див. Дарелл Хафф. Як брехати за допомогою статистики і  як за допомогою діаграми прикрасити дійсність? або про фактор брехні Едварда Тафт

Джерело: http://baguzin.ru/wp/?p=13178

Прокоментуйте першими

Залишити коментар

Ваш e-mail не буде публікуватись.

*