Флажки (checkboxes) в Excel и Google Spreadsheets

Ренат Шагабутдинов 10 July 2024

Введение

Ссылка на книгу Excel с примерами (скачивайте на локальный диск как книгу Excel)

Флажки (checkboxes) есть и в Excel, и в Google Таблицах. Это переключатели, которые могут в быть в одном из двух положений — вкл/выкл и не зависят при этом друг от друга (в отличие от радиокнопок).

В Google Таблицах флажки в ячейках появились несколько лет назад, в Excel — в 2024 году только в Microsoft 365. Но в старых версиях флажки тоже были, только они не привязаны к ячейкам и поэтому не так удобны (каждый флажок вставляется отдельно, а сразу в целый диапазон ячеек добавить флажки нельзя).

Вкл/выкл в случае таблиц — это ИСТИНА / TRUE и ЛОЖЬ / FALSE, ноль и единица, двоичные/булевы/логические значения — как угодно. Значение можно увидеть в строке формул. Меняются значения флажков пробелом или кликом мышки и в Excel, и в Google Таблицах.

В Excel 365: вкладка ленты Вставка — Флажок (Insert — Checkbox):

В Google Таблицах тоже Вставка — Флажок (Insert — Checkbox):

Эти значения ИСТИНА и ЛОЖЬ могут формироваться и формулами, и вводиться вручную. Но флажки — наглядно и красиво. Как их можно использовать?

Чек-лист: поставили галочку, пункт списка закрасился/зачеркнулся.
Столбец с неким условием: даем ли скидку, едет ли сотрудник на корпоратив и так далее. Затем этот столбец используем в расчетах (формулах). Или формируем выборку по этому столбцу: два отдельных списка (со скидкой и без, едут и не едут).
Задаем параметры, используем флажки в интерактивных отчетах: сортируем ли данные, выводим ли их по месяцам или дням, какие столбцы забираем из исходника и так далее.
Выбираем, какие ряды (регионы/филиалы/показатели и что угодно) отображать в диаграмме.
И многое другое, на что хватит фантазии 🙂

Рассмотрим некоторые из этих сценариев! Скриншоты будут в Excel.

Условное форматирование

Начнем с простого: сделаем список задач (чек-лист) с флажками и будем вычеркивать пункты и красить зеленым.

Для этого нужно условное форматирование. Так как мы выделяем два столбца (текст и сами флажки), то нам нужен вариант с собственной формулой, то есть “Создать правило” — “Использовать формулу”. Потому что стандартные варианты условного форматирования предполагают, что вы красите те ячейки, которые сами соответствуют заданным критериям (например, ИСТИНА или число больше 10 или текст содержит слово “Лемур”). А если нужно красить ячейки на основе значений других, ссылаться на другие ячейки, нужна формула.

А вот сама формула будет ну очень простая. Потому что в условном форматировании формула должна возвращать ИСТИНА / TRUE, чтобы ячейки форматировались. А у нас, чтобы закрашивать ячейки, нужно, чтобы флажок был в положении “вкл”, то есть ИСТИНА. Так что мы просто ссылаемся на него. Но важно закрепить столбец долларом, сделать ссылку относительной:

=$B2

Почему? И почему строка 2?

Потому что наш форматируемый диапазон — A2:B7. И когда вы пишете формулу в условном форматировании, представляйте, что вы вводите ее в первую ячейку форматируемого диапазона и потом “протягиваете” вправо и вниз. Как поменяется ссылка? При смещении вниз на B3 (то есть будем смотреть на флажок в строке 3) и так далее. А вправо, если не закрепить столбец, B3 превратится в C3. То есть мы будем проверять уже не столбец с флажками, а пустой столбец справа.

Итак, вводим формулу, переходим в “Формат”:

И настраиваем формат по вкусу. Например, зачеркнутый + зеленый цвет:

Et voila!

Используем флажки в формулах

Допустим, мы хотим флажком отмечать выполнение какого-то условия. Проставлять таким образом, что мы хотим дать скидку в конкретном случае.

Тут все будет просто, как и с условным форматированием: в функции ЕСЛИ / IF, позволяющей проверить условие и вернуть тот или иной результат в зависимости от его выполнения, условие тоже должно быть равно ИСТИНА или ЛОЖЬ, то есть это логическое значение.

Поэтому не обязательно даже проверять условие вот так явным образом, если мы ссылаемся на ячейку с флажком:

=ЕСЛИ(D3=ИСТИНА; …

Можно просто:

=ЕСЛИ(D3;

В следующем примере умножаем стоимость на 0,9 (даем скидку 10%), если в столбце “Скидка?” флажок включен:

Фильтруем данные по отмеченным флажкам

Что если у нас есть таблица с флажками, а мы хотим отдельно сформировать список только отмеченных или только не отмеченных строк? На примере из прошлого пункта — только строк со скидкой или без нее.

В таком случае можно воспользоваться функцией ФИЛЬТР / FILTER (в Google Таблицах FILTER). Она возвращает только те строки из диапазона / массива (первого аргумента), для которых во втором массиве будет значение ИСТИНА / TRUE. То есть если бы мы хотели строки с ценой выше 2000, то функция выглядела бы так:

=ФИЛЬТР(A3:E10;C3:C10>2000)

Что возвращает ее второй аргумент C3:C10>2000?

Массив значений ИСТИНА или ЛОЖЬ, в зависимости от выполнения условия для каждой очередной ячейки:

Понятное дело, что он никуда не выводится в ячейки, здесь я это делаю для демонстрации. Такой массив рассчитывается виртуально. И ФИЛЬТР возвращает только те строки из своего первого аргумента, для которых в этом массиве (втором аргументе) будет ИСТИНА.

А значит, в случае с флажками мы просто ссылаемся на столбец с ними в функции ФИЛЬТР без дополнительных манипуляций:

Обратите внимание, что функция ФИЛЬТР переносит значения, а не форматы. Соответственно, флажки переносятся как ИСТИНА. Вернуть им флажковый вид можно, если уже на месте работы функции вставить флажки в эти ячейки:

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

Что, кстати, можно использовать! Если вы хотите автоматически проверять несколько условий и показывать их выполнение флажком, введите формулу, которая будет возвращать ИСТИНА или ЛОЖЬ и вставьте поверх этих формул флажки.

Если нужно фильтровать по отключенным флажкам, просто добавим сверху к ссылке на флажки функцию НЕ / NOT — она меняет ИСТИНУ на ЛОЖЬ и наоборот.

Выбираем столбцы

Допустим, мы хотим выбирать только некоторые столбцы из диапазона — а какие именно, хотим выбирать флажками. Вставим все названия столбцов и добавим к ним флажки.

Чтобы выбрать не все столбцы, а только некоторые — например, первый, третий и пятый — нужна функция ВЫБОРСТОЛБЦ / CHOOSECOLS. Первый ее аргумент — диапазон / массив, а далее все последующие — это номера нужных столбцов.

Как получить номера столбцов? Мы поступим так — получим массив из всех номеров столбцов — это функция СТОЛБЕЦ / COLUMN, если применить ее к диапазону (в новой версии Excel, конечно, но и в принципе всё, о чем мы говорим тут, будет работать только в ней):

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

Остается засунуть это внутрь функции ВЫБОРСТОЛБЦ в качестве номеров столбцов — и магия случится!

Строим диаграмму с выключением рядов флажками

Если в диаграмме используется какой-то диапазон, и в некоторых ячейках будет ошибка #Н/Д (#N/A) — данные из этих ячеек не будут отображаться на диаграмме.

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

Итак, у нас есть несколько регионов или других рядов данных — добавим рядом с ними флажки:

А далее построим в отдельном диапазоне набор данных для диаграммы — в нем будем формулой проверять, включен ли флажок в каждой очередной строке и если да — то будем оставлять данные как есть, а иначе отображать #Н/Д с помощью одноименной функции:

=ЕСЛИ(ячейка с флажком; значение; НД())

В нашем случае так:

Третий флажок отключен — данные по Екатеринбургу заменились на #Н/Д. Остается построить на основе этой (второй, нижней) таблички диаграмму и включать/выключать города — будут отображаться отмеченные:

Флажки — элементы управления (в старых версиях Excel)

Как обычно с новыми чудо-функциями (некоторые из них использовались и тут, вроде ВЫБОРСТОЛБЦ), магия доступна только пользователям Excel по подписке Microsoft 365 / Excel Online.

Но для флажков все-таки есть альтернатива в старых версиях Excel. Во всех версиях есть элементы управления, в том числе флажок, и доступны они на вкладке “Разработчик”. Она может не отображаться у вас — ее можно включить в параметрах Excel в настройке ленты:

И на ней вставить флажок:

Нам нужен флажок из верхней группы — “Элементы управления формы”. Именно его можно будет привязать к ячейке.

Эти флажки живут на графическом слое, над ячейками. И это минус по сравнению с новыми — нельзя вставить сразу 4 для всех регионов или сразу в 100 ячеек. Эти вставляются по одному и к ячейкам не привязаны — их сложно выравнивать красиво.

Но к ячейкам их привязать можно! Клик правой кнопкой, выбираем “Формат объекта” и там — “Связь с ячейкой” на вкладке “Элемент управления”, после чего кликаем на нужную ячейку:

Ну вот, теперь этот флажок будет влиять на значение в ячейке P2, а дальше — дело техники, все по тому же алгоритму — ссылаемся на эти ячейки, в случае ЛЖИ выдаем #Н/Д и получаем интерактивную диаграмму.