Глава 6. Сортировка, фильтрация, срезы Таблиц Excel

Это продолжение перевода книжки Зак Барресс и Кевин Джонс. Таблицы Excel: Полное управление для сотворения, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.).

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

Рис. 6.1. Меню сортировки Таблиц

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

Исходя из убеждений доступных вариантов сортировки, нет никакой различия меж сортировкой обычного спектра ячеек и сортировкой Таблицы. Существует три главных типа сортировки: по возрастанию, по убыванию, по цвету.

При сортировке по цвету Excel показывает перечень всех цветов заливки ячеек, применяемых в избранном столбце. В Excel перечислены лишь цвета заливки ячеек, примененные вручную либо с условным форматированием (но не цвета стиля Таблицы). Если цвет заливки ячейки, примененный вручную, переопределяется условно примененным цветом заливки ячейки, то базисный цвет заливки ячейки, примененный вручную, не учитывается при построении перечня либо сортировке перечня.

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

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

Рис. 6.2. Автофильтр и перечень проверки данных

Не считая того, раскрывающиеся элементы управления Автофильтром показываются постоянно, независимо от того, выбрана ячейка либо нет. Элементы управления перечнем проверки показываются лишь тогда, когда ячейка выбрана и активна.

Вы также сможете получить доступ к командам сортировки, пройдя по меню Основная –> Редактирование –> Сортировка и фильтр.

Пользовательская сортировка

В нижней части меню Сортировать по цвету находится команда Пользовательская сортировка (рис. 6.3). К ней также можно получить доступ, пройдя по меню Данные –> Сортировка.

Рис. 6.3. Вызов пользовательской сортировки

При выбирании данной нам команды Excel открывает диалоговое окно Сортировка, которое дозволяет делать сортировку по нескольким столбцам и условиям:

Рис. 6.4. Диалоговое окно Сортировка для пользовательской сортировки; чтоб прирастить изображение кликните на нем правой клавишей мыши и изберите Открыть картину в новейшей вкладке

Если столбец отсортирован значок Автофильтра меняется, чтоб показать стрелку ввысь (по возрастанию / по алфавиту) либо вниз (по убыванию):

Рис. 6.5. Значок Автофильтра опосля сортировки

Фильтры

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

Excel дозволяет делать фильтрацию несколькими методами: по цвету, по тексту/числу/дате.

Команда Фильтр по цвету доступна лишь в этом случае, если в столбце есть ячейки с цветами заливки, хорошими от тех, что используются в стиле Таблицы.

Если столбец содержит в главном текстовые значения, Excel показывает меню Текстовые фильтры с различными параметрами фильтрации:

Рис. 6.6. Текстовые фильтры

Если столбец содержит в главном числовые значения, Excel показывает меню Числовые фильтры:

Рис. 6.7. Числовые фильтры

Если столбец содержит в главном значения дат, Excel показывает меню Фильтры по дате:

Рис. 6.8. Фильтры по дате

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

Когда Таблица отфильтрована, номера строк в левой части окна рабочего листа меняются с темного на голубий:

Рис. 6.9. Номера строк отфильтрованной Таблицы получают голубий цвет

Очередной метод выяснить, использован ли фильтр (и какие столбцы фильтруются) – поглядеть на значки Автофильтра. Когда столбец фильтруется, значок меняется на символ воронки (см. рис. 6.9).

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

Чтоб удалить фильтр из столбца, кликните стрелку Автофильтра и изберите пункт Удалить фильтр из столбца… Если столбец не фильтруется, эта команда отключена.

Срезы

Срезы были в первый раз представлены в Excel 2010 лишь для сводных таблиц. Начиная с Excel 2013 они доступны и для Таблиц. Срез – это зрительный интерактивный отран управления фильтрацией. Срезы выводят неповторимые значения 1-го столбца в виде клавиш. По дефлоту при нажатии клавиши Excel включает это значение и исключает все другие. Если надавить кнопку Ctrl и одну из клавиш, а потом отпустить Ctrl, выберутся все клавиши, не считая нажатой. Если надавить и задерживать кнопку Shift (либо Ctrl), и надавить несколько клавиш, то все, относящиеся к ним значения, будут показаны.

Чтоб добавить срез на лист Excel, кликните всякую ячейку Таблицы и пройдите по меню Конструктор –> Вставить срез. Раскроется диалоговое окно вставка срезов:

Рис. 6.10. Диалоговое окно Вставка срезов

Вы сможете избрать столбец(ы), для которого вы желаете вывести срез, а потом надавить ОК. Excel делает по одному срезу для всякого избранного столбца. Опосля сотворения среза Excel помещает его на рабочий лист. Если кликнуть по срезу, покажется вкладка Характеристики:

Рис. 6.11. Вкладка Характеристики для опции среза

Вы сможете добавить дублирующие срезы для 1-го и такого же столбца. При всем этом срезы по дефлоту соединены с одним и этим же источником данных, и поменять эту связь нереально. Вы не сможете фильтровать значения столбца, если они неактивны из-за фильтра в остальных столбцах. Вы сможете указать Excel, что созодать со значениями, которые недосягаемы, установив один из 3-х флагов в диалоговом окне Настройка среза: Скрыть элементы без данных, Выделить пустые элементы (при всем этом они будут неактивны и иметь наиболее бледноватый тон) и Показывать пустые элементы крайними (если галочку отжать, то все элементы, и активные, и неактивные, будут отсортированы по алфавиту / по возрастанию).

Рис. 6.12. Настройка среза

Вкладка ленты Инструменты для среза –> Характеристики дозволяет настроить зрительный стиль, количество столбцов, размеры среза, клавиш и др.

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

Рис. 6.13. Контекстное меню среза Таблицы

Если строчка заголовка Таблицы отключена, вы не сможете употреблять срезы. Все срезы, связанные с Таблицей показываются как устаревшие (рис. 6.14). Вы сможете надавить клавишу Обновить, чтоб включить строчку заголовка таблицы и создать срезы активными.

Рис. 6.14. Неактивные срезы опосля отключения заголовка Таблицы

Временная шкала – это вариант среза, который специально предназначен для дат. Временные шкалы доступны лишь для сводных таблиц, но не для Таблиц Excel.