Проверка на пустоту ячейки в Excel с добавлением условий
Содержание
Работая с таблицами, пользователи могут столкнуться с самыми разными задачами. Одна из них — возврат определенного значения в ячейке Excel, если она изначально пустая. Существует несколько способов проверки таблицы на наличие пустых клеток. В данном материале рассмотрим, как определить наличие символов в ячейках и добавить условные конструкции для выполнения тех или иных действий.
Функция ЕПУСТО
Чтобы добавить какое-либо условие, предварительно необходимо понять, как выполнить проверку ячейки на наличие значений. Существует несколько способов, каждый из которых имеет свои достоинства и недостатки. Можно выполнить проверку при помощи условных конструкций и операторов, однако намного лучше всего использовать специальные формулы, т.к. такая запись более лаконичная и понятная, как самому автору, так и пользователям, работающим в режиме просмотра документа.
Для проверки ячейки на наличие значений применяется функция в Excel — «Если не пусто» или кратко ЕПУСТО. При использовании в чистом виде, вне каких-либо условных конструкций, она возвращает «ИСТИНА», когда в клетке нет никакой информации, или «ЛОЖЬ», если она не пустая.
В качестве аргумента функция ЕПУСТО принимает ссылку на ячейку. Очевидно, что за один раз она может проверить только одну клетку, поэтому данная формула не подходит для проверки целого диапазона. Однако ее можно использовать в сочетании с другими функциями. Примеры таких конструкций будут рассмотрены далее.
Отметим, что функция ЕПУСТО реагирует на любые значения, независимо от формата. К примеру, если в клетке записан пробел, который не видно при просмотре таблицы, то это не пустая ячейка. Следовательно, функция вернет значение «Ложь». Также формула реагирует в том случае, если в клетку записана ссылка на результат вычислений из других ячеек. В этом случае при работе она просто выдает ошибку.
В целом, функция ЕПУСТО является основным инструментом для проверки наличия значений в ячейках Эксель. Единственный ее минус в том, что она не проверяет целый диапазон, а только одну клетку в таблице. При этом она возвращает логическое значение, которое не во всех случаях целесообразно записывать в документ. Тем не менее эту формулу можно использовать для проверочных выражений. Однако прежде чем рассмотреть пример применения данной функции, определим альтернативные методы проверки таблицы.
Проверка диапазона на наличие пустых и непустых ячеек
Далеко не во всех случаях перед пользователем стоит задача выполнить действие по условию. Иногда просто возникает необходимость определить, сколько ячеек содержат значение или сколько в диапазоне пустых клеток, в которых ничего не записано. Для двух таких случаев в Excel предусмотрены специальные функции, которые позволяют быстро выполнить проверку.
Функция СЧИТАТЬПУСТОТЫ для проверки диапазона
Пользователи MS Excel могут быстро подсчитать количество незаполненных ячеек. Для этого применяется функция СЧИТАТЬПУСТОТЫ. Данная формула принимает ссылку на диапазон в качестве аргумента и возвращает число — количество клеток, которые не содержат каких-либо значений.
Рассмотрим порядок применения данной функции на простом примере. В столбце А записаны числа, причем некоторые ячейки пропущены. Задача состоит в том, чтобы посчитать количество клеток, в которых ничего не записано. Для этих целей вводим функцию СЧИТАТЬПУСТОТЫ и указываем ссылку на числа в столбце А в качестве аргумента.
В результате выполнения данной формулы мы получим количество пустых ячеек в диапазоне. В нашем случае их 3.
Отметим, что функция СЧИТАТЬПУСТОТЫ не всегда работает корректно. Она может реагировать на записанные в ячейках пробелы и пустые строки. В этом случае количество визуально пустых клеток будет отличаться от числа действительно незаполненных. К слову, представленную функцию можно использовать для проверки нескольких столбцов или целой таблицы. Для этого достаточно указать диапазон в качестве аргумента. Необязательно проверять только одну колонку.
Функция СЧЁТЗ для подсчета непустых ячеек в диапазоне
Альтернативный вариант для определения количества пустых и непустых клеток — применение формулы СЧЁТЗ. Она работает также как СЧИТАТЬПУСТОТЫ, только подсчитывает число заполненных, а не пустых ячеек. В этом легко убедиться на простом примере.
В ячейке для записи результата прописываем формулу СЧЁТЗ. В качестве аргумента указываем диапазон на столбец А, где прописаны числа.
В результате применения формулы мы получим число непустых клеток. В нашем случае их 10. Так же как и в случае с предыдущей функцией, можно выполнить проверку нескольких столбцов или всего листа.
Минимально разбираясь в специфике применения формул в Excel, можно вычислить количество непустых клеток, зная количество заполненных. Для этого достаточно отнять результат выполнения формулы СЧЁТЗ от общего количества ячеек в нужном диапазоне. В нашем случае общее количество клеток в столбце — 13. Отняв результат формулы СЧЕТЗ от этого числа, мы получим количество пустых клеток.
Такой вариант использовать необязательно, ведь вместо этого намного проще использовать формулу СЧИТАТЬПУСТОТЫ. Тем не менее данный способ имеет право на существование, и в определенных ситуациях может помочь с определением количества незаполненных клеток в таблице.
Функция СЧЕТЕСЛИ для проверки диапазона
Еще один способ определения количества пустых ячеек в диапазоне — применение функции СЧЕТЕСЛИ. Таким образом мы постепенно подходим к использованию условных конструкций в MS Excel. Формула немного сложнее, чем СЧЁТЗ и СЧИТАТЬПУСТОТЫ, так как условие для поиска нужно прописать вручную. Способ применения данной функции следующий:
- Выбираем ячейку для записи результата. Далее вводим знак равенства и прописываем формулу СЧЁТЕСЛИ. В скобках записываем первый аргумент — диапазон, в котором будет вести поиск по условию. В нашем примере это столбец А, в котором записаны числа.
Указываем диапазон для функции СЧЕТЕСЛИ - Далее необходимо задать критерий. Задача состоит в том, чтобы формула посчитала ячейку, если в ней записано пустое значение. Поэтому в качестве аргумента указываем две кавычки. Между диапазоном и критерием обязательно нужно поставить специальный символ — точку с запятой, чтобы конструкция работала правильно.
Задаем критерий для функции СЧЕТЕСЛИ - Указав аргументы для формулы, нажимаем . В результате мы получим количество незаполненных ячеек в диапазоне.
Результат выполнения СЧЕТЕСЛИ
Отметим, что данная формула очень похожа на ЕСЛИ. Однако в отличие от нее, СЧЕТЕСЛИ выполняет подсчет ячеек, а не подставляет какое-либо значение. Поэтому использовать данную логическую конструкцию удобно далеко не во всех случаях.
Функция ЕСЛИ
Мы плавно подошли к применению функции ЕСЛИ, которая позволяет добавлять в ячейку то или иное значение при соответствии или несоответствии заданному условию. В качестве аргумента данная формула принимает логическое выражение, а также два варианта возвращаемых записей. Одно указывается в ячейке, если результат выполнения соответствует значению «ЛОЖЬ», а второе — если оно соответствует «ИСТИНА». На первый взгляд может показаться непонятным, как это относится к выполнению действий, которые позволяют определить, пусто или не пусто в ячейке Excel. Поэтому рассмотрим несколько простых примеров применения данной функции, как самостоятельно, так и в сочетании с другими формулами.
Простой пример проверки с функцией ЕСЛИ
При использовании функции ЕСЛИ самое главное — правильно задать логическое условие. Тогда проблем с возвратом результат не возникнет. Данную формулу очень удобно применять в том случае, если нужно проверить, является ли ячейка заполненной и вернуть определенное значение по результатам такой проверки.
Рассмотрим простейший пример, который поможет разобраться в специфике данной функции:
- В нашем примере есть таблица с оценками студентов. Некоторые ячейки пропущены и остались незаполненными. По условиям задачи необходимо напротив каждой фамилии оставить запись. Если оценка стоит — экзамен сдан, а в обратном случае возвращается надпись «Пересдача».
Таблица с примером для формулы ЕСЛИ - Для решения задачи нужно сделать проверку. В случае, если ячейка с оценкой не пустая, то Excel вернет одно значение, а если пустая — другое. Вводим знак равенства и вписываем функцию ЕСЛИ. В качестве первого аргумента указываем условие — ячейка ничего не содержит. Для этого через знак «=» прописываем пустые кавычки.
Вводим условие для проверки - После того как логическое выражение записано, необходимо прописать варианты значений. Если условие соблюдается и в ячейке с оценкой пусто, оставляем значение «Пересдача». Если же оценка в клетке записана, то формула пропишет текст — «Сдал».
Записываем значения для результатов ЛОЖЬ и ИСТИНА - Записав аргументы функции, нажимаем . Программа вернет соответствующее значение в зависимости от того, записана ли в ячейке оценка или нет. Применяем формулу ко всему столбцу, используя маркер автозаполнения.
Результат применения ЕСЛИ
Как видно, с помощью функции ЕСЛИ можно быстро задать критерий и вернуть то значение, которое будет необходимо при соблюдении или несоблюдении условия. Однако это не единственный способ применения этой формулы для подобных задач. Рассмотрим альтернативные варианты ее использования в сочетании с другими формулами.
Пример подсчета с формулой ЕПУСТО
В предыдущем примере логическое выражение было записано с помощью простых операторов. Этот способ самый быстрый и удобный. Однако не во всех случаях он работает корректно. К примеру, могут возникнуть трудности при несоответствии форматов данных или, когда в диапазоне содержатся ссылки на другие ячейки, которые, в свою очередь, содержат ошибку.
Чтобы исключить вероятность некорректной работы, при оформлении логического выражения можно использовать формулу ЕПУСТО. Алгоритм действия этой конструкции будет таким же, как и вышеописанном примере: если ячейка содержит значение, то формула вернет одно значение, а если пустая — другое.
На практике реализовать данную функцию можно следующим образом: сначала записываем формулу ЕСЛИ, открываем скобку и указываем логическое выражение. В данном случае это формула ЕПУСТО с диапазоном ячеек, содержащих оценки. Далее, как и в прошлом примере, записываем значения при возврате «ЛОЖЬ» или «ИСТИНА». Выглядит такая конструкция следующим образом.
Алгоритм действия данной конструкции идентичен. Поэтому и результат выполнения будет таким же. Разница лишь в том, что ЕПУСТО реагирует на любые ошибки и ячейки с несоответствующими форматами. При работе с большими таблицами это позволит существенно снизить вероятность некорректной работы условного оператора, функцию которого выполняет формула ЕСЛИ.
Похожую конструкцию можно составить и с помощью вспомогательной формулы ЕПУСТО. В данном случае логическое выражение будет проверять, является ли результат выполнения ЕПУСТО истинной или ложью. Очевидно, что такой способ менее удобен, так как запись более длинная и менее понятная. Поэтому пример с таким выражением в данной статье не рассматривается.
Пример с формулой СЧЕТЕСЛИ или СЧИТАТЬПУСТОТЫ
При работе с таблицами может возникнуть необходимость выполнения определенных действий при обнаружении пустых ячеек в диапазоне. Чаще всего речь идет о возврате какого-либо значения, однако также могут применяться и определенные формулы. В данном случае необходимо ввести в функцию ЕСЛИ формулы СЧЕТЕСЛИ или СЧИТАТЬПУСТОТЫ, чтобы выполнить проверку диапазона на наличие пустых клеток.
Для примера, необходимо определить, есть ли в списке студенты, которые не сдали экзамен. В данном случае их количество не имеет значения. Главное требование — определить, присутствуют ли такие студенты в группе. Для решения задачи применяем конструкцию, которая состоит из функции ЕСЛИ, принимающую в качестве одного из аргументов формулу СЧИТАТЬПУСТОТЫ.
Логическое выражение в данной конструкции проверяет, является ли результат выполнения формулы больше 0. Если найдены пустые ячейки, мы получим сообщение о том, что в группе есть студенты, не сдавшие экзамен. В противном случае вернется сообщение с обратной информацией.
Очень похожим способом можно использовать функцию СЧЕТЕСЛИ. Она также включается в конструкцию в качестве проверки в логическом выражении формулы ЕСЛИ. Функция сначала считает количество пустых ячеек. Если их больше нуля, возвращается соответствующее значение из формулы ЕСЛИ.
Подобные проверочные конструкции удобно вставлять в объемные таблицы, где сложно определить наличие пустых или заполненных ячеек невооруженным взглядом. К тому же стоит учитывать, что в зависимости от того, пустая клетка или нет, будет меняться и результат выдачи формулы ЕСЛИ. В этом легко убедиться даже на примере, приведенном выше. Если заполнить пустые ячейки с оценками, значение в нижней части таблицы изменится.
Аналогичным образом данный механизм сработает и при использовании других формул, если указанные конструкции использовались для обозначения логического выражения функции ЕСЛИ. Это относится, в том числе, к формулам СЧЕТЗ и СЧИТАТЬПУСТОТЫ, примеры с которыми были приведены выше.
Подводим итоги
Наличие или отсутствие символов в ячейке может являться условием для выполнения определенных действий: вставки формулы или подстановки некого значения. Для этих целей предварительно необходимо выполнить проверку — пустая клетка или заполненная. Это можно сделать при помощи функций ЕПУСТО, СЧИТАТЬПУСТОТЫ, СЧЁТЗ или СЧЁТЕСЛИ.
Для подстановки формул или значений проверочную формулу вставляют в качестве аргумента функции ЕСЛИ. С ее помощью пользователь сможет получить определенное значение, если записанное логическое условие соблюдается или, наоборот, нарушается. На практике вариантов применения подобных конструкций очень много. Поэтому в специфике применения этих формул стоит разобраться каждому пользователю MS Excel.
Загрузка...
