Урок 6 из 8 · 20 мин · обновлено

Сортировка, фильтры и условное форматирование

Как сортировать список, не перепутав строки, как работает фильтр и почему СУММ продолжает считать скрытые строки, как закрепить шапку и подсветить строки по правилу.

Сортировка: порядок без потерь

Сортировка расставляет строки по значению столбца: по дате, по сумме, по алфавиту. Выделите любую ячейку внутри списка и выберите сортировку в меню «Данные». Укажите столбец, направление и отметьте, что первая строка — заголовок, чтобы шапка не уехала вниз.

Главное правило: сортируется весь список. Каждая строка — запись, и переставлять её нужно целиком. Если выделить один столбец «Сумма» и отсортировать только его, суммы перестроятся, а менеджеры и товары останутся на месте. Ошибку не заметить: таблица выглядит нормально, но Ивану теперь приписаны чужие продажи.

Если после сортировки по сумме по убыванию первым стоит заказ Ивана на 2000, а за ним ежедневники Ольги на 1780 — всё сделано верно.

Вторая ловушка — числа, записанные текстом. Их таблица сравнивает посимвольно:

Результат
1
10
100
2

«10» встало раньше «2», потому что символ «1» меньше «2». Если сортировка ведёт себя странно, проверьте выравнивание: числа, прижатые влево, — это текст.

Фильтр: только нужные строки

Фильтр показывает часть списка и прячет остальное. Выделите ячейку в списке и включите фильтр в меню «Данные» — в шапке появятся кнопки со стрелками. Через них можно:

  • оставить конкретные значения: менеджер «Иван»;
  • задать условие для чисел: сумма больше 1000;
  • выбрать даты за период;
  • совместить условия в разных столбцах.

Спрятанные строки никуда не делись: снимите фильтр — они вернутся.

Важная деталь, на которой ошибаются даже опытные: СУММ считает и скрытые строки. Включите фильтр «Менеджер = Иван» — на экране три заказа, а формула =СУММ(F2:F11) по-прежнему покажет 10710. Сумму только видимых строк считает другая функция:

Формула
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;F2:F11)
Результат
3700

Первый аргумент выбирает действие: 109 — сумма видимых строк. С другими номерами функция считает среднее, количество, максимум. Ставьте её в строку, которую фильтр не спрячет: ниже списка, в стороне от него. Ячейка сбоку в строке 5 исчезнет вместе с пятой строкой, если фильтр её скроет.

Закрепление шапки

Когда в списке сотни строк, при прокрутке шапка уходит вверх, и становится непонятно, где количество, а где цена. В меню «Вид» закрепите первую строку — она останется на месте, а список будет прокручиваться под ней. Так же закрепляют первый столбец, если таблица широкая.

Условное форматирование: подсветка по правилу

Условное форматирование красит ячейки автоматически, по правилу: суммы больше 1500 — зелёным, просроченные даты — красным. В отличие от ручной заливки, правило следит за данными: изменилось число — изменился цвет.

Готовые правила — «больше», «меньше», «между», «текст содержит», цветовые шкалы — выбираются в меню форматирования за пару щелчков. Самый гибкий вариант — правило по формуле. Подсветим целиком строки крупных заказов: выделите A2:J11 и задайте правило с формулой

Формула
=$F2>=1500

Формула пишется для верхней левой ячейки диапазона, а дальше таблица копирует её на остальные ячейки — как обычную формулу. Поэтому знак $ стоит перед F: при переходе к соседним столбцам проверка должна оставаться на столбце сумм. А строка не закреплена — для каждой строки проверяется своя сумма.

Попробуйте сами. В учебной таблице закрепите шапку. Отсортируйте заказы по сумме от большей к меньшей. Включите фильтр и оставьте только заказы Ивана, а ниже списка, в стороне, посчитайте сумму видимых строк. Затем снимите фильтр и подсветите зелёным строки заказов от 1500 рублей.

Решение
  1. Шапка: меню «Вид» → закрепить первую строку.
  2. Сортировка: выделите ячейку в списке, «Данные» → сортировка по столбцу «Сумма», по убыванию, с отметкой о строке заголовка. Первым станет заказ Ивана на 2000, последним — маркеры Ольги на 540. Формулы в F, G и H пересчитаются правильно: строки переехали целиком.
  3. Фильтр: «Данные» → фильтр, в столбце «Менеджер» оставьте только Ивана. Видно три заказа.
  4. Сумма видимых: в свободной ячейке ниже списка, например M13:
Формула
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;F2:F11)
Результат
3700

Для сравнения поставьте рядом =СУММ(F2:F11) — она покажет 10710.

  1. Подсветка: снимите фильтр, выделите A2:J11, создайте правило по формуле =$F2>=1500 с зелёной заливкой. Подсветятся две строки — 2000 и 1780.

Измените количество в любом заказе так, чтобы сумма перевалила за 1500, — строка сразу станет зелёной.

Мини-тест: проверьте себя

Закрепите урок: несколько вопросов, разбор ответов — в конце.

  1. Что случится, если выделить только столбец «Сумма» и отсортировать его?

  2. Включили фильтр «Менеджер = Иван». Что покажет =СУММ(F2:F11)?

  3. Чем фильтр отличается от сортировки?

  4. Правило условного форматирования для диапазона A2:J11 — формула =F2>=1500. Почему подсветка ложится не туда?