Глава 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 автоматически ограничивает область сортировки диапазоном тела данных Таблицы, и не включает в сортировку строки, лежащие ниже Таблицы.

Предыдущая глава        Содержание    Следующая глава

Ris. 6.1. Menyu sortirovki Tablits

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

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

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

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

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

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

Ris. 6.2. Avtofiltr i spisok proverki dannyh

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

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

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

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

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

Ris. 6.3. Vyzov polzovatelskoj sortirovki

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

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

Ris. 6.4. Dialogovoe okno Sortirovka dlya polzovatelskoj sortirovki

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

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

Ris. 6.5. Znachok Avtofiltra posle sortirovki

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

Фильтры

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

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

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

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

Ris. 6.6. Tekstovye filtry

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

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

Ris. 6.7. CHislovye filtry

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

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

Ris. 6.8. Filtry dat

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

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

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

Ris. 6.9. Nomera strok otfiltrovannoj Tablitsy priobretayut sinij tsvet

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

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

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

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

Срезы

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

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

Ris. 6.10. Dialogovoe okno Vstavka srezov

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

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

Ris. 6.11. Vkladka Parametry dlya nastrojki sreza

Рис. 6.11. Вкладка Параметры для настройки среза

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

Ris. 6.12. Nastrojka sreza

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

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

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

Ris. 6.13. Kontekstnoe menyu sreza Tablitsy

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

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

Ris. 6.14. Neaktivnye srezy posle otklyucheniya zagolovka Tablitsy

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

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

Добавить комментарий

Ваш адрес email не будет опубликован.