В этой статье подробно расскажу, как работать с чек-боксами в Эксель, то есть как вставить в ячейку галочку, с помощью которой можно будет отмечать какие-либо пункты в списке или управлять форматированием целой строки. Также чек-боксы, как и другие элементы управления, широко используются при создании дашбордов.
Итак, давайте рассмотрим работу с чек-боксами на примерах.
Включаем вкладку Разработчик
Для начала нужно будет включить вкладку Разработчик, которая по умолчанию в Экселе спрятана. Для этого нужно перейти в Параметры программы - вызваем контекстное меню щелчком правой кнопки мыши на любой вкладке и выбираем Настройка ленты.
В появившемся окне в разделе Настройки ленты находим и отмечаем вкладку Разработчик.
Вставка и настройка Флажка
После применения изменений на ленте появится эта вкладка и в группе Элементы управления раскроем выпадающий список Вставить. Выбираем элемент управления Флажок.
Теперь его нужно будет разместить на листе. Для этого достаточно щелкнуть на любой ячейке мышью. Элемент управления Флажок будет сразу выделен и мы можем с помощью мыши его спозиционировать в нужной ячейке.
Если снять выделение с элемента, например, щелкнув мышью по любой ячейке рядом, то при повторном щелчке мышью по элементу управления галочка будет появляться или исчезать. Чтобы выбрать элемент управления нужно будет снова щелкнуть на нем правой кнопкой мыши. Из появившегося контекстного меню можно, например, выбрать Изменить текст и убрать подпись, которая появляется по умолчанию.
Также выбрать элемент управления можно щелкнув по нему левой кнопкой мыши, но при этом нужно удерживать нажатой клавишу Ctrl на клавиатуре.
В данном случае у меня на листе представлены цены на услуги шиномонтажа и я бы хотел отмечать галочкой выполненные работы, поэтому перемещу элемент управления в ячейку напротив первой услуги и размещу ее по середине.
Скопировать чек-бокс на остальные ячейки можно также, как это делается с формулами или любыми другими значениями - захватываем мышью маркер автозаполнения в правом нижнем углу ячейки с чек-боксом и протягиваем выделение на весь столбец со значениями.
Если чек-боксы нужны лишь для того, чтобы визуально отметить тот или иной пункт списка, то на этом можно и закончить.
Однако чек-бокс в Эксель не просто так называют элементом управления. Так как чек-бокс имеет два состояния - установлен флажок или нет, то в зависимости от состояния мы можем управлять пунктом списка к которому чек-бокс относится, например, изменять форматирование или задействовать данные в формулах.
В первую очередь нам необходимо вывести в какую-либо ячейку состояние чек-бокса. Для этого необходимо связать элемент управления с ячейкой листа. Делается это следующим образом - с помощью щелчка правой кнопки мыши вызываем контекстное меню на чек-боксе и из него выбираем Формат объекта.
В появившемся окне нас будет интересовать поле Связь с ячейкой. Выбираем ячейку на листе, которая будет связана с конкретным чек-боксом. Выберу ячейку рядом с ним.
Теперь при установке или снятии галочки состояние чек-бокса будет выводиться в связанной ячейке, то есть если галочка установлена, то будет отображаться ИСТИНА, если нет - ЛОЖЬ.
Аналогичные действия нужно будет проделать со всеми последующими чек-боксами и автоматизировать эту операцию, к сожалению, не выйдет. Можно несколько ускорить процесс, вызывая окно Формат элемента управления не через контекстное меню, а с помощью сочетания клавиш Ctrl + 1. То есть сначала выделяем с помощью нажатой клавиши Ctrl и левой кнопки мыши чек-бокс, а затем нажимаем сочетание клавиш. Или можно еще слегка упростить процесс - выделяем чек-бокс и в строке формул ставим знак равенства, а затем указываем ячейку на листе и нажимаем Enter.
Когда все чек-боксы связаны с ячейками, мы можем их использовать в связке с другими инструментами Эксель, например, с условным форматированием.
Создадим новое правило для всего списка (1-2, рис. ниже). Так как условное форматирование применяется к ячейкам в случае если формула возвращает значение ИСТИНА, то нам достаточно сослаться на ячейку, связанную с чек-боксом. При этом по умолчанию поставится абсолютная ссылка на ячейку, а поскольку мы предварительно выделили весь диапазон значений, то для правильной работы условного форматирования необходимо, чтобы в ссылке на ячейку был закреплен только столбец (4).
Осталось лишь указать необходимое форматирование (5), например, изменим заливку ячейки и цвет текста.
Теперь при выделении чек-бокса вся строка будет изменять форматирование.
Использование Флажка в формулах
Также мы можем использовать состояния чек-боков в формулах. Для примера подсчитаем количество выполненных операций, то есть количество установленных чек-боксов. Для этого задействуем функцию СЧЁТЕСЛИ, которая подсчитывает количество непустых ячеек в указанном диапазоне. В качестве диапазона укажем весь столбец со связанными ячейками. В качестве критерия - значение ИСТИНА.
И давайте добавим общую сумму оказанных услуг. Рассчитать ее поможет функция СУММЕСЛИ. Сначала указываем диапазон поиска критерия - это столбец со связанными ячейками, затем критерий - мы ищем ИСТИНА, и последним аргументом указываем столбец для суммирования в случае выполнения условия.
Давайте рассмотрим еще один пример. В Экселе составлен план некоего проекта, который состоит из нескольких этапов. Реализация каждого этапа отмечается флажком.
Необходимо визуально отобразить готовность проекта в процентах. Как и в предыдущем примере воспользуемся функцией СЧЁТЕСЛИ для подсчета установленных флажков. Если это значение разделить на количество этапов, то получим процент готовности. Для этого разделим ранее полученное значение на количество этапов, подсчитанных с помощью функции СЧЁТЗ.
Осталось лишь присвоить ячейке процентный формат.
Давайте сделаем значение более наглядным, для этого задействуем условный формат.
Для ячейки выберем гистограмму, а затем откорректируем правило условного форматирования и выставим минимальные и максимальные значения для гистограммы.
В итоге при выборе чек-боксов мы будем видеть выполненный процент работ по проекту.
Также мы можем использовать чек-боксы в формулах, которые не относятся к первоначальному списку. Поясню, что имею в виду.
Например, нам нужно вынести наименования еще не выполненных этапов проекта в отдельный список. Для этого задействуем функцию ФИЛЬТР, которая относится к функциям динамических массивов.
В качестве массива, который будем фильтровать, укажем весь список этапов. В качестве критерия фильтрации будет выступать столбец со связанными с чек-боксами ячейками, в котором мы будем искать значение ЛОЖЬ, ведь нам нужны еще не выполненные этапы. Ну а если в списке не будет значений, удовлетворяющих условию, то выведем «нет данных».
Таким образом мы получим динамически изменяющийся список с этапами, которые еще предстоит выполнить.
Флажок и диаграммы
Ну и рассмотрим применение чек-боксов на дашбордах или в каких-то графических отчетах.
Есть некоторые данные за первый квартал трех лет.
Нужно их сравнить в графическом виде, взяв за эталон значение 21-ого года. Сделать это довольно просто с помощью комбинированной диаграммы, задав для 21-ого года, например, гистограмму, а для последующих лет графики с маркерами.
Но давайте сделаем так, чтобы графики за 22-ой и 23-ий годы можно было отключать. Для этого нам понадобятся чек-боксы и вспомогательная таблица, которая будет построена на основе исходной с учетом состояния чек-боксов.
Вставим два чек-бокса и на этот раз воспользуемся стандартными подписями, указав в них соответствующие годы. Затем свяжем чек-боксы с ячейками, которые для удобства также подпишем.
Скопируем исходную таблицу. Значения для 22-ого и 23-го годов будем подтягивать с помощью простой формулы - если значение соответствующего чек-бокса равно ИСТИНА, то подставим значение из соответствующей ячейки.
В данном случае вспомогательные данные построены таким образом, что можем в ссылке на связанную с чек-боксом ячейку зафиксировать столбец и тогда формулу можно будет размножить на всю таблицу. Останется лишь изменить данные для диаграммы. Для этого можно просто перетянуть мышью соответствующий диапазон на вновь созданную таблицу.
Теперь на диаграмме будут отображаться лишь данные за выбранные годы.
Новый флажок
Ну и не могу не упомянуть об относительно новом инструменте Флажок, который появился в Excel из офиса Microsoft 365. На вкладке Вставка появилась группа Элементы управления. На данный момент там только один вариант - Флажок.
Чтобы вставить флажок достаточно предварительно выбрать нужную ячейку или ячейки и нажать на соответствующий инструмент. Во все выбранные ячейки будет вставлен флажок и его состояние - по умолчанию ЛОЖЬ.
Этот вариант чек-бокса действительно удобнее, так как он сразу связан с ячейкой, в которую устанавливается и не требуется производить довольно утомительные действия, связывая чек-бокс с какой-то ячейкой на листе. Также к флажку можно применять стандартные инструменты форматирования, например изменить его размер с помощью установки значения размера шрифта или задать ему цвет.
А теперь давайте решим предыдущую задачу еще раз с помощью данного инструмента.
Снова скопирую исходную таблицу но в этот раз вставлю чек-бокс в первом столбце, а затем задействую функцию ЕСЛИ - если значение в ячейке с флажком будет ИСТИНА, то выведем всю строку исходной таблицы.
В таком виде будут задействованы динамические массивы и мы получим всю строку исходной таблицы. Достаточно раскопировать формулу на все ячейки ниже, чтобы получить нужные значения.
Ну а теперь нажимаем сочетание клавиш Alt + F1, чтобы на основе данных вставить диаграмму. При необходимости можем откорректировать внешний вид диаграммы, изменив его на комбинированный.
Итак, флажки работают и при их выключении соответствующие данные с диаграммы исчезают.
Больше информации по работе в Excel в моих курсах.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм