Как отсортировать значения в Excel
![](http://officeassist.ru/wp-content/uploads/12910/tmp-b80fa859-e250-4ed1-ad8b-da7d7d841e9d-360x252.jpg)
Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: у вас есть база данных продаж по клиентам (рис. 1), необходимо создать диаграмму, отражающую объем продаж пяти крупнейших клиентов. (Видно, что имеется несколько строк на каждого клиента.)
![Рис. 1. Исходная структура данных](http://officeassist.ru/wp-content/uploads/12910/tmp-b80fa859-e250-4ed1-ad8b-da7d7d841e9d.jpg)
Рис. 1. Исходная структура данных
Скачать заметку в формате Word или pdf, примеры в формате Excel
Решение: вы можете сортировать вашу базу данных, если создадите структуру и включите промежуточные итоги. [1] Для этого выполните следующее. Выберите любую ячейку в столбце клиентов (Customer). На вкладке ДАННЫЕ в области Сортировка и фильтры нажмите кнопку АЯ для сортировки по возрастанию (рис. 2).
![Рис. 2. Отсортированная база данных](http://officeassist.ru/wp-content/uploads/12910/tmp-42d69df2-4a39-45f9-8a04-b96ea9e01edc.jpg)
Рис. 2. Отсортированная база данных
На вкладке ДАННЫЕ в области Структура, нажмите кнопку Промежуточный итог. Настройте параметры в диалоговом окне Промежуточные итоги (рис. 3). Excel добавляет промежуточные итоги для каждого покупателя и создает структуру данных. Посмотрите слева от столбца A: Excel добавил три группы и кнопки, помеченные 1, 2 и 3. Кнопки позволяют разворачивать и сворачивать структуру.
![Рис. 3. Настройка параметров структуры данных в диалоговом окне Промежуточные итоги](http://officeassist.ru/wp-content/uploads/12910/tmp-0eb92341-0126-4d74-801c-dfa087bfe565.jpg)
Рис. 3. Настройка параметров структуры данных в диалоговом окне Промежуточные итоги
Нажмите кнопку 2, чтобы свернуть структуру до второго уровня — одна строка для каждого клиента (рис. 4).
![Рис. 4. Структура данных, свернутая до второго уровня](http://officeassist.ru/wp-content/uploads/12910/tmp-f0d6081a-b609-4fdd-b9db-1e00258406d6.jpg)
Рис. 4. Структура данных, свернутая до второго уровня
Выберите любую ячейку в столбце Продажи (Sales). На вкладке ДАННЫЕ в области Сортировка и фильтры нажмите кнопку ЯА для сортировки по объему продаж по убыванию (рис. 5).
![Рис. 5. Данные отсортированы по объему продаж по клиенту по убыванию](http://officeassist.ru/wp-content/uploads/12910/tmp-a1d8a9b7-4c36-456f-9290-8c40202d66ba.jpg)
Рис. 5. Данные отсортированы по объему продаж по клиенту по убыванию
Выделите область A1:В21. В Excel 2007 нажмите Alt+F1, чтобы создать гистограмму на текущей странице (рис. 6), или перейдите на вкладку ВСТАВИТЬ в область Диаграммы, и выберите подходящий вид диаграммы. [2]
![Рис. 6. Диаграмма, отражающая объем продаж пяти крупнейших клиентов](http://officeassist.ru/wp-content/uploads/12910/tmp-27210335-60b5-410e-ba22-e3a65e0dc955.jpg)
Рис. 6. Диаграмма, отражающая объем продаж пяти крупнейших клиентов
Пояснения: здесь проявились две удивительные особенности. Во-первых, вы можете сортировать структуру в свернутом состоянии по промежуточным итогам. Excel на самом деле переставляет не по одной строке, а сразу группы строк во время выполнения сортировки. (Каждая группа содержит скрытые строки детализации для каждого клиента и видимую строку промежуточных итогов.) Во-вторых, вы воспользоваться тем фактом, что графики по умолчанию не отражают скрытие данные. Хотя ваш выбор области построения диаграммы включал строки с 1 по 21, график показывает только видимые промежуточные итоги в строках 5, 9, 13, 17 и 21.
Резюме: Excel корректно сортирует данные свернутые в структуру, используя промежуточные итоги. Диаграмма на основе структуры также отражает только промежуточные итоги.
[1] На мой взгляд, решение с помощью сводной таблицы проще и гибче для дальнейшего использования. Подробнее см. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013 — здесь и далее прим. Багузина
[2] По умолчанию Excel сам рассчитывает минимальную и максимальную границы по оси ординат (Y). В приведенном примере он установит минимум на ровне 42 000, что исказит диаграмму. Кликните правой кнопкой мыши на оси ординат, выберите опцию Формат оси и установите значение минимума границы равное нулю. Подробнее см. Дарелл Хафф. Как лгать при помощи статистики и Как с помощью диаграммы приукрасить действительность? или о факторе лжи Эдварда Тафти.