Сортировка промежуточных итогов

Рубрика: 7. Полезняшки 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, что исказит диаграмму. Кликните правой кнопкой мыши на оси ординат, выберите опцию Формат оси и установите значение минимума границы равное нулю. Подробнее см. Дарелл Хафф. Как лгать при помощи статистики и Как с помощью диаграммы приукрасить действительность? или о факторе лжи Эдварда Тафти.


Прокомментировать