Урок 7. Сортировка, фильтр и умные таблицы
Когда в таблице сотни строк, важно быстро навести в ней порядок: расставить по алфавиту или по сумме, показать только нужный город или месяц. Для этого есть сортировка и фильтр, а умная таблица делает работу с такими списками ещё удобнее.
Пример для урока — продажи менеджеров:
| A | B | C | |
|---|---|---|---|
| 1 | Менеджер | Город | Сумма |
| 2 | Анна | Минск | 1200 |
| 3 | Борис | Гомель | 800 |
| 4 | Вера | Минск | 1500 |
| 5 | Глеб | Брест | 650 |
| 6 | Дарья | Гомель | 950 |
| 7 | Егор | Минск | 400 |
Главное требование к таким данным: одна строка заголовков, в каждом столбце — данные одного вида, без пустых строк и объединённых ячеек внутри. Тогда Excel сам поймёт, где таблица начинается и кончается.
Сортировка
Щёлкните одну ячейку в столбце «Сумма» и на вкладке «Данные» нажмите кнопку сортировки по убыванию (или «Главная» → «Сортировка и фильтр» → «Сортировка от максимального к минимальному»):
| Менеджер | Город | Сумма |
|---|---|---|
| Вера | Минск | 1500 |
| Анна | Минск | 1200 |
| Дарья | Гомель | 950 |
| Борис | Гомель | 800 |
| Глеб | Брест | 650 |
| Егор | Минск | 400 |
Строки переставились целиком: у каждой суммы остался свой менеджер. Для текста кнопки называются «от А до Я» и «от Я до А», для дат — «от старых к новым» и обратно.
Не выделяйте для сортировки один столбец. Если Excel спросит, расширить ли выделенный диапазон, выбирайте «автоматически расширить выделенный диапазон». Вариант «сортировать в пределах указанного выделения» переставит только этот столбец, суммы разъедутся с менеджерами — и если файл сохранить, связь не вернуть. Сразу после ошибки помогает Ctrl+Z.
По нескольким столбцам. «Данные» → «Сортировка» открывает окно настраиваемой сортировки. Первый уровень — «Город» по алфавиту, затем кнопка «Добавить уровень» и второй — «Сумма» по убыванию. Флажок «Мои данные содержат заголовки» должен быть включён, чтобы строка заголовков осталась наверху:
| Менеджер | Город | Сумма |
|---|---|---|
| Глеб | Брест | 650 |
| Дарья | Гомель | 950 |
| Борис | Гомель | 800 |
| Вера | Минск | 1500 |
| Анна | Минск | 1200 |
| Егор | Минск | 400 |
Фильтр
Фильтр временно скрывает строки, которые не подходят под условие. Щёлкните любую ячейку таблицы и нажмите «Данные» → «Фильтр» (или Ctrl+Shift+L). В заголовках появятся кнопки-стрелки.
- Нажмите стрелку у «Город», снимите флажок «(Выделить все)» и отметьте «Минск» — останутся три строки.
- «Числовые фильтры» в том же меню — условия вроде «больше», «между», «первые 10»; для текста — «Текстовые фильтры»: «содержит», «начинается с».
- Фильтры в разных столбцах складываются: Минск и сумма больше 1000.
- У отфильтрованного столбца стрелка меняется на значок воронки. Снять фильтр — в том же меню («Удалить фильтр из столбца») или кнопкой «Очистить» на вкладке «Данные».
Ctrl+Shift+Lубирает кнопки фильтра совсем.
Номера скрытых строк пропадают, а видимые подсвечиваются синим — так видно, что на листе включён фильтр.
Итог по видимым строкам
СУММ считает все ячейки диапазона, в том числе скрытые фильтром. Итог только по видимым даёт ПРОМЕЖУТОЧНЫЕ.ИТОГИ, где первое число задаёт действие: 9 — сумма, 1 — среднее, 2 — количество чисел.
=СУММ(C2:C7)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C7)| Фильтр | СУММ | ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;…) |
|---|---|---|
| нет | 5500 | 5500 |
| Город = Минск | 5500 | 3100 |
Ставьте такие формулы над таблицей или под ней, а не в строках, которые фильтр может скрыть.
Умная таблица
Обычный диапазон можно превратить в таблицу Excel (её часто называют умной таблицей): щёлкните ячейку с данными и нажмите Ctrl+T (или «Главная» → «Форматировать как таблицу»). В окне проверьте диапазон и флажок «Таблица с заголовками», нажмите OK.
Что это даёт:
- Оформление и фильтр сразу. Полосатые строки, кнопки фильтра в заголовках.
- Таблица растёт сама. Новая строка, введённая сразу под таблицей, становится её частью: с тем же оформлением, а формулы, диаграммы и сводные таблицы на её основе её учитывают.
- Формулы на весь столбец. Введите формулу в первую строку нового столбца — Excel сам заполнит её до конца таблицы и будет дописывать в новые строки.
- Строка итогов. На вкладке «Конструктор таблиц» (в старых версиях — «Конструктор») включите флажок «Строка итогов» — под таблицей появится строка, где для каждого столбца можно выбрать сумму, среднее, количество. Она сама использует
ПРОМЕЖУТОЧНЫЕ.ИТОГИ, поэтому считает только видимые строки. - Понятные ссылки. Щёлкая ячейки внутри таблицы, вы получите формулы с именами столбцов вместо адресов. Например, если в таблице есть столбцы «Цена» и «Количество»:
=[@Цена]*[@Количество][@Цена] значит «значение из столбца "Цена" в этой же строке». Снаружи таблицы на столбец ссылаются по имени таблицы: =СУММ(Таблица1[Сумма]) — имя таблицы видно и меняется на той же вкладке «Конструктор таблиц».
Вернуть таблицу в обычный диапазон можно командой «Преобразовать в диапазон» на той же вкладке — данные и оформление останутся.
В LibreOffice Calc умных таблиц в таком виде нет. Сортировка — «Данные» → «Сортировка», фильтр — «Данные» → «Фильтр» → «Автофильтр»; ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает так же.
Коротко
- Таблица для сортировки и фильтра: одна строка заголовков, без пустых строк и объединённых ячеек.
- Для сортировки щёлкайте одну ячейку столбца, не выделяйте столбец целиком — иначе строки разъедутся.
- Несколько уровней сортировки — «Данные» → «Сортировка» → «Добавить уровень».
- Фильтр (
Ctrl+Shift+L) скрывает строки;СУММих всё равно считает,ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;…)— нет. Ctrl+T— умная таблица: растёт сама, формулы на весь столбец, ссылки по именам столбцов, строка итогов.