Робота з даними у Excel. Фільтрація, сортування даних, зведені таблиці та проміжні підсумки

 Откройте таблицу "База данных Автосалон" и загрузите себе копию

Откройте таблицу "База данных Студенты" и загрузите себе копию

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

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

Рис. 1. Исходная структура данных

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Рис. 2. Отсортированная база данных

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

Рис. 3. Настройка параметров структуры данных в диалоговом окне Промежуточные итоги

Нажмите кнопку 2, чтобы свернуть структуру до второго уровня – одна строка для каждого клиента (рис. 4).

Рис. 4. Структура данных, свернутая до второго уровня

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

Рис. 5. Данные отсортированы по объему продаж по клиенту по убыванию

Выделите область A1:В21. В Excel 2007 нажмите Alt+F1, чтобы создать гистограмму на текущей странице (рис. 6), или перейдите на вкладку ВСТАВИТЬ в область Диаграммы, и выберите подходящий вид диаграммы. [2]

Рис. 6. Диаграмма, отражающая объем продаж пяти крупнейших клиентов

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

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

Сводные таблицы 

Некоторые пользователи любят «красиво» форматировать исходные данные (рис. 5). К сожалению, после этого они совершенно не подходят для использования в качестве источника данных для сводной таблицы.

Существует весьма хитрый метод заполнения пустых ячеек (только в английской версии программы. – Примеч. ред.). Выделите весь диапазон данных. Перейдите на вкладку Главная ленты и щелкните в разделе Редактирование на кнопке Найти и выделить. В раскрывающемся меню выберите команду Перейти. В диалоговом окне Переход щелкните на кнопке Выделить. В диалоговом окне Выделение группы ячеек установите переключатель Пустые ячейки. Выделив все пустые ячейки (эта ситуация как раз и отражена на рис. 5), начните ввод формулы нажатием =, затем нажмите клавишу ↑ (стрелка вверх, расположенная между буквенной и цифровой частями клавиатуры), а после этого комбинацию клавиш <Ctrl+Enter>, чтобы занести формулу во все пустые ячейки (фактически это ввод формулы массива). После этого не забудьте скопировать и выполнить специальную вставку значения для преобразования формул в значения. (Проверил, действительно не работает в русском Excel. – Прим. Багузина)

Рис. 5.  «Красивый» отчет не подходит в качестве источника данных для сводной таблицы

Скучаете по старому доброму методу перетаскивания полей? Один из методов управления сводными таблицами, которым уже невозможно воспользоваться, начиная с версии Excel 2007, — это перетаскивание полей непосредственно в отчет сводной таблицы. Отныне перетаскивание полей осуществляется только в пределах окна области задач Список полей сводной таблицы. Но для вас есть и хорошая новость, которая заключается в том, что компания Microsoft включила в Excel 2010 поддержку классического макета сводной таблицы, характерного для предыдущих версий программы. Чтобы перейти к классическому интерфейсу управления макетом сводной таблицы, щелкните в любом ее месте правой кнопкой мыши и выберите в контекстном меню команду Параметры сводной таблицы. В появившемся на экране диалоговом окне перейдите на вкладку Вывод и установите флажок Классический макет сводной таблицы (разрешено перетаскивание полей). Щелкните ОК (рис. 6).

Рис. 6. Для возможности перетаскивания полей непосредственно в отчете сводной таблицы установите флажок Классический макет…

Глава 3. Настройка сводной таблицы

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

1. Выделите ячейку в сводной таблице.
2. Откройте диалоговое окно Параметры сводной таблицы. Для этого: (а) на вкладке Анализ щелкните на кнопке Параметры, находящейся в группе Сводная таблица, и выберите в раскрывающемся меню команду Параметры; (б) щелкните правой кнопкой мыши и выберите пункт Параметры сводной таблицы.
3. На вкладке Макет и формат диалогового окна Параметры сводной таблицы в разделе Формат поставьте галочку на поле Для пустых ячеек отображать и введите значение 0 (по умолчанию ноль там уже стоит) (рис. 7).
4. Щелкните ОК, чтобы применить внесенные в настройки изменения.

Рис. 7. Замена пустых ячеек нулями в окне Параметры сводной таблицы

Изменение имени поля сводной таблицы. Просто встаньте на ячейку и введите требуемое имя. Помните, что использовать имя, уже имеющееся в сводной таблице в качестве названия поля, нельзя. Чтобы обойти это ограничение, введите в конце названия пробел. Например, поле «Доход˽» (с пробелом в конце) и «Доход» (без пробела) программа Excel будет рассматривать как разные объекты сводной таблицы. Данная хитрость не видна пользователям сводной таблицы, а потому о ней будете знать только вы, ее создатель.

Заполнение пустых ячеек. Если информация, выведенная в сводной таблице, будет скопирована в новую область и будет использоваться в дальнейшей обработке, пустые ячейки нежелательны (рис. 8а). Перейдите на вкладку Конструктор в область Макет и щелкните на кнопке Макет отчета. В выпавшем меню щелкните на Повторять все подписи элементов. Пустые ячейки в области Строк сводной таблицы заполняться (рис. 8б).

Рис. 8. Заполнение пустых ячеек с помощью опции Повторять все подписи элементов: (а) исходный вид сводной таблицы; (б) после включения опции

Чтобы выделить сводную таблицу целиком воспользуйтесь комбинацией клавиш Ctrl+Shift+* (к сожалению, область Фильтров не выделяется).

Управление промежуточными итогами, пустыми строками и другими настройками. Промежуточные итоги могут располагаться под или над группой исходных данных или вообще могут быть исключены из исходной таблицы. Перемещение промежуточных сумм в верхнюю часть группы данных позволяет уменьшить общий размер сводной таблицы (рис. 9). Тем не менее сами промежуточные итоги выводятся только в случае представления сводной таблицы в форме структуры или в сжатой форме. Настраиваются промежуточные суммы с помощью меню Промежуточные итоги контекстной вкладки Конструктор.

Рис. 9. Перемещение промежуточных сумм в верхнюю часть группы данных позволяет уменьшить общий размер сводной таблицы: (а) промежуточные итоги внизу; (б) промежуточные итоги вверху

Отключение не всех промежуточных итогов, а только по одному полю. Если в отчете содержится много полей строк, вывод промежуточных итогов может сбить вас с толку. Например, как показано на рис. 10, промежуточные итоги не требуются для каждого рынка сбыта отдельно, поскольку за каждый рынок отвечает единственный менеджер.

Рис. 10. Затрудняющие восприятие промежуточные итоги

Кликните правой кнопкой мыши на ячейку в столбце Рынок сбыта, выберите опцию Параметры поля, перейдите на вкладку Промежуточные итоги и фильтры, и в области Итоги, установите Нет (рис. 11).

Рис. 11. Отключите промежуточные итоги только для поля Рынки сбыта

Получившаяся сводная таблица выглядит более профессионально (рис. 12).

Рис. 12. Сводная таблица с отключенными промежуточными итогами по полю Рынки сбыта; промежуточные итоги по полю Регион по-прежнему отражаются

Добавление множества промежуточных итогов в одно поле. Вы всегда можете добавить в область строк (или столбцов) дополнительные промежуточные итоги. Например, в качестве исходной можно взять сводную таблицу, изображенную на рис. 12. Кликните правой кнопкой мыши на любую ячейку в поле Регион. Выберите опцию Параметры поля, перейдите на вкладку Промежуточные итоги и фильтры, и в области Итоги, установите Другие (рис. 13), и укажите в списке типы промежуточных вычислений, которые требуется выполнять в сводной таблице.

Рис. 13. Установите пять видов промежуточных итогов для поля Регион

Получившаяся сводная таблица (рис. 14) несколько громоздка, зато весьма информативна.

Рис. 14. Сводная таблица с пятью промежуточными итогами для поля Регион

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

Добавление пустых строк. Раскрывающееся меню Пустые строки (вкладка Конструктор, область Макет) позволяет добавлять пустые строки между группами данных (рис. 15). Эта настройка может применяться только к сводным таблицам, включающим более одного поля в области строк. Пустые строки облегчают восприятие больших сводных таблиц и выглядят профессионально!

Рис. 15. Пустые строки, отделяющие блоки сводной таблицы

Если несколько сводных таблиц расположены одна под другой, то в случае изменения ширины столбца одной сводной таблицы может измениться ширина столбцов другой сводной таблицы. В результате в сводных таблицах могут появиться символы ####, показывающие, что содержимое ячейки не поместилось, и не может быть отражено полностью. Подобное поведение объясняется тем, что по умолчанию Excel автоматически изменяет ширину столбцов, подгоняя ее под содержимое последней обновленной сводной таблицы. Чтобы изменить заданное по умолчанию поведение, щелкните правой кнопкой мыши на каждой сводной таблице (из тех что расположены друг под другом), и выберите пункт Параметры сводной таблицы. На вкладке Макет и формат снимите галочку Автоматически изменять ширину столбцов при обновлении (см. рис. 7).

Один из самых существенных факторов, влияющих на проводимые расчеты, — это правильность автоматического определения программой типов данных полей. Например, одно единственное пустое поле в исходных данных, может привести к тому, что Excel решит, что в столбце содержатся текстовые данные, и при перетаскивании названия этого поля в область значений, выведет не сумму, а количество значений.

Будьте предельно внимательны при перетаскивании полей в область Значения. Если полученные суммы кажутся вам подозрительно маленькими, обязательно проверьте, какой тип функции применяется в итоговых вычислениях. Вполне возможно, что вместо суммы по полю вычисляется количество записей. Для изменения неправильной формулы выделите одну из ячеек в столбце сводной таблицы. Щелкните правой кнопкой мыши. Выберите опцию Итоги по, и Сумма (рис. 16).

Рис. 16. Выберите Сумма в качестве операции, обрабатывающей исходные данные