Excel епусто для диапазона

Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.

Исходные данные:

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов («На пересдачу» или «Сдал»). Результат выполнения функции:

Теперь часть данной формулы можно использовать для условного форматирования:

1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».



2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:



3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.



Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек

У выше указанном примере можно изменить формулу используя двойные кавычки («») в место функции проверки ячеек на пустоту, и она также будет работать:

=ЕСЛИ(ИЛИ(B3=»»;B3=2);»На пересдачу»;»Сдал»)

Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специфические значения:

Как видно на рисунке в ячейке находится символ одинарной кавычки. Первая формула (с двойными кавычками вместо функции) ее не видит. Более того в самой ячейке A1 одинарная кавычке не отображается так как данный спецсимвол в Excel предназначенный для отображения значений в текстовом формате. Это удобно, например, когда нам нужно отобразить саму формулу, а не результат ее вычисления как сделано в ячейках D1 и D2. Достаточно лишь перед формулой ввести одинарную кавычку и теперь отображается сама формула, а не возвращаемый ее результат. Но функция ЕПУСТО видит, что в действительности ячейка А1 не является пустой!

Проверка на пустую ячейку в таблице Excel

Пример 2. В таблице Excel записаны некоторые данные. Определить, все ли поля заполнены, или есть как минимум одно поле, которое является пустым.

Исходная таблица данных:

Чтобы определить наличие пустых ячеек используем следующую формулу массива (CTRL+SHIFT+Enter):

Функция СУММ используется для определения суммы величин, возвращаемых функцией —ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(—ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.

Результат вычислений:

То есть, в диапазоне B3:B17 есть одна или несколько пустых ячеек.

Примечание: в указанной выше формуле были использованы символы «—». Данный вид записи называется двойным отрицанием. В данном случае двойное отрицание необходимо для явного преобразования данных логического типа к числовому. Некоторые функции Excel не выполняют автоматического преобразования данных, поэтому механизм преобразования типов приходится запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений к числовому типу является умножение на 1 или добавление 0 (например, =ИСТИНА+0 вернет число 1, или =«23»*1 вернет число 23. Однако использование записи типа =—ИСТИНА ускоряет работу функций (по некоторым оценкам прирост производительности составляет до 15%, что имеет значение при обработке больших объемов данных).

Как посчитать количество пустых ячеек в Excel

Пример 3. Рассчитать средний возраст работников офиса. Если в таблице заполнены не все поля, вывести соответствующее сообщение и не выполнять расчет.

Таблица данных:

Формула для расчета (формула массива):

Функция ЕСЛИ выполняет проверку диапазона на наличие пустых ячеек (выражение СУММ(—ЕПУСТО(B3:B12))). Если СУММ вернула значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).

Результат вычислений:

Особенности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

=ЕПУСТО(значение)

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

Примечания:

1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

Скачать примеры функции ЕПУСТО в Excel

Задача функции

ЕПУСТО()

, английский вариант ISBLANK(),

— проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке

А1

имеется значение 555, то формула

=

ЕПУСТО(А1)

вернет ЛОЖЬ, а если ячейка

А1

пуста, то ИСТИНА.

Синтаксис функции

ЕПУСТО()

ЕПУСТО

(

значение

)

Значение

— значением может быть все что угодно: текст, число, ссылка,

имя

,

пустая ячейка

, значение ошибки, логическое выражение.

Использование функции

В

файле примера

приведены несколько вариантов проверок:

1. Если в проверяемой ячейке содержится число, текстовое значение, формула, то функция вернет логическое значение ИСТИНА.

2. Если проверяемая ячейка пуста, то функция также вернет логическое значение ЛОЖЬ.

Проверка диапазона ячеек

Функция

ЕПУСТО()

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

СЧИТАТЬПУСТОТЫ()

, но если ячейки содержат значение Пустой текст («»), то функция

СЧИТАТЬПУСТОТЫ()

будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье

Подсчет пустых ячеек

.

Чтобы ответить на вопрос «Есть ли хотя бы 1 пустая ячейка в B6:B11?» используйте

формулу массива

=

ЕСЛИ(СУММ(—ЕПУСТО(B6:B11));ИСТИНА)

Чтобы ответить на вопрос «Есть ли хотя бы 1 заполненная ячейка в B6:B11?» используйте

формулу массива

=

НЕ(ЕПУСТО(B6:B11))

Примеры функции ЕПУСТО для проверки пустых ячеек в Excel

Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов («На пересдачу» или «Сдал»). Результат выполнения функции:

Теперь часть данной формулы можно использовать для условного форматирования:

1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.

Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек

У выше указанном примере можно изменить формулу используя двойные кавычки («») в место функции проверки ячеек на пустоту, и она также будет работать:

=ЕСЛИ(ИЛИ( B3=»» ;B3=2);»На пересдачу»;»Сдал»)

Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специфические значения:

Как видно на рисунке в ячейке находится символ одинарной кавычки. Первая формула (с двойными кавычками вместо функции) ее не видит. Более того в самой ячейке A1 одинарная кавычке не отображается так как данный спецсимвол в Excel предназначенный для отображения значений в текстовом формате. Это удобно, например, когда нам нужно отобразить саму формулу, а не результат ее вычисления как сделано в ячейках D1 и D2. Достаточно лишь перед формулой ввести одинарную кавычку и теперь отображается сама формула, а не возвращаемый ее результат. Но функция ЕПУСТО видит, что в действительности ячейка А1 не является пустой!

Проверка на пустую ячейку в таблице Excel

Пример 2. В таблице Excel записаны некоторые данные. Определить, все ли поля заполнены, или есть как минимум одно поле, которое является пустым.

Исходная таблица данных:

Чтобы определить наличие пустых ячеек используем следующую формулу массива (CTRL+SHIFT+Enter):

Функция СУММ используется для определения суммы величин, возвращаемых функцией —ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(—ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.

То есть, в диапазоне B3:B17 есть одна или несколько пустых ячеек.

Примечание: в указанной выше формуле были использованы символы «—». Данный вид записи называется двойным отрицанием. В данном случае двойное отрицание необходимо для явного преобразования данных логического типа к числовому. Некоторые функции Excel не выполняют автоматического преобразования данных, поэтому механизм преобразования типов приходится запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений к числовому типу является умножение на 1 или добавление 0 (например, =ИСТИНА+0 вернет число 1, или =«23»*1 вернет число 23. Однако использование записи типа =—ИСТИНА ускоряет работу функций (по некоторым оценкам прирост производительности составляет до 15%, что имеет значение при обработке больших объемов данных).

Как посчитать количество пустых ячеек в Excel

Пример 3. Рассчитать средний возраст работников офиса. Если в таблице заполнены не все поля, вывести соответствующее сообщение и не выполнять расчет.

Формула для расчета (формула массива):

Функция ЕСЛИ выполняет проверку диапазона на наличие пустых ячеек (выражение СУММ(—ЕПУСТО(B3:B12))). Если СУММ вернула значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).

Особенности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

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

1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

Функция ЕПУСТО() в MS EXCEL

Задача функции ЕПУСТО() , английский вариант ISBLANK(), — проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) вернет ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.

Синтаксис функции ЕПУСТО()

ЕПУСТО(значение)

Значение — значением может быть все что угодно: текст, число, ссылка, имя, пустая ячейка, значение ошибки, логическое выражение.

Использование функции

В файле примера приведены несколько вариантов проверок:

1. Если в проверяемой ячейке содержится число, текстовое значение, формула, то функция вернет логическое значение ИСТИНА.

2. Если проверяемая ячейка пуста, то функция также вернет логическое значение ЛОЖЬ.

Проверка диапазона ячеек

Функция ЕПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст («»), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек.

Чтобы ответить на вопрос «Есть ли хотя бы 1 пустая ячейка в B6:B11?» используйте формулу массива

Чтобы ответить на вопрос «Есть ли хотя бы 1 заполненная ячейка в B6:B11?» используйте формулу массива

Как в Excel использовать функцию «ЕПУСТО»

В данной заметке мы расскажем о том, что означает функция Excel «ЕПУСТО» (ISBLANK) и как ее можно использовать в работе.

В Excel есть отдельная категория функций, которые называются «Е», то есть, «Если». Она означает условие, при котором содержимое ячейки соответствует либо не соответствует истине.

Как это работает

Итак, чтобы узнать, является ли конкретная ячейка таблицы занятой или пустой, в Excel есть функция «ЕПУСТО».

Синтаксис этой функции довольно прост:

• после «=» и «ЕПУСТО» задайте в скобках ту ячейку, которую хотите проверить;
• нажмите Enter, чтобы подтвердить выполнение команды. Теперь функция вернет значение «Ложь», если ячейка содержит какие-либо данные, или «Истина», если ячейка пуста.

Если вы хотите заменить значения «Истина» и «Ложь» в своем документе, это не проблема. Можно объединить «ЕПУСТО» с функцией «ЕСЛИ» — это позволит задавать собственные значения.

Синтаксис функции «ЕСЛИ», как известно, состоит из трех частей: «Логическое выражение», «Значение_если_истина» и «Значение_если_ложь».
Логическое выражение =ЕСЛИ (ЕПУСТО (C3). C3 — это проверяемая строка. 0 соответствует «Значение_если_истина», а 1 соответствует «Значение_если_ложь».

Если ячейка C3 пуста, функция выводит значение 0, в противном случае 1.
После точки с запятой вы можете ввести в «Значение_если_истина» то значение, которое должна вывести программа, если ячейка пуста. В «Значение_если_ложь» введите значение, которое должно появиться, если соответствующая ячейка заполнена.

Внимательно следите за тем, чтобы все скобки были поставлены правильно. В противном случае Excel не сможет выполнить эту функцию.

Нажмите Enter, чтобы просмотреть результат.

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование —Очистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
   =ЕСЛИ( A1 =1;10;»»)
   =IF(A1=1,10,»»)
   в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
   Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

1. создаем в книге новый стандартный модуль: Alt+F11 —Insert —Module()
2. копируем в созданный модуль приведенный выше код
3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

Функция ЕПУСТО() в MS EXCEL

​Смотрите также​ к нему ваш​Сервис-Макрос-Безопасность. Установить средний​Т.е. задача будет​ это уже введенные​ но не хотелось​molekula​​ формулу массива на​​ статье Советы по​содержит только текстовые​ параметром =1) находит​(т.е. не начинается​​ По номеру строки​​ ячейки со значением​

​ EXCEL интерпретирует ячейку,​

​ сложности, т.к. одни​​ («»), то функция​

​Задача функции ЕПУСТО(), английский​​ макрос для меня​ уровень. Закрыть Эксель.​ выглядеть так:​ данные, формула здесь​ бы всётаки, чтобы​: Спасибо, что откликнулись!​

Использование функции

​ пустом листе -​ построению таблиц.​

​ или только числовые​ позицию наибольшего значения,​ с первой строки),​ найдем и само​ Пустой текст вместе​

​ содержащую Пустой текст,​ функции считают, что​ СЧИТАТЬПУСТОТЫ() будет подсчитывать​ вариант ISBLANK(), -​

Проверка диапазона ячеек

​ проблематично, ибо мало​ Запустить файл 2003_AlexM2.xls​Если первый символ​ не поможет. Да​ получатель таблицы огорчился​ikki, возможно не​ получаю ноль, если​Parkson​ значения.​ которое меньше или​ то формулу для​ значение.​ с пустыми ячейками.​ как пустую ячейку,​ это пустая ячейка,​ также и эти​

​ проверять есть ли​ с ними знакома​ При открытии файла​ в ячейках столбца​ и УФ сможет​

​ увидев ненужный ему​

​ удалить, а заменить​ ввожу просто как​: Всем привет.Проблема заключается​Другим универсальным решением является​ равно значению первого​

​ определения номера строки​

excel2.ru

Подсчет пустых ячеек в MS EXCEL

​Рассмотрим диапазон значений, в​Чтобы не запутаться с​ а другие, как​ а другие с​ ячейки наряду с​

​ в ячейке число,​fanat​ нажать кнопку «Не​ A >5, то​ только визуально спрятать​ адрес, случайно ткнув​

​ на пробел или​ формулу, ожидаемо получаю​​ в следующем.​​ формула массива:​ аргумента (1E+306). Правда,​ последней заполненной ячейки​

​ который регулярно заносятся​ подсчетом пустых ячеек​

​ содержащую текстовое значение.​ ними не соглашаются​ действительно пустыми. Об​ текстовое значение, формула​: Нужна помощь корректировке​ отключать макросы». теперь​ заменяем любой текст​ эти данные.​ курсором в «не​ «-» или что-то​ единицу.​Надо создать формулы​=МАКС(СТРОКА(A1:A20)*(A1:A20<>»»))​ для этого требуется,​ можно записать следующим​ новые данные.​ в диапазоне​Для иллюстрации приведем пример​ и считают, что​ этом читайте в​

​ или нет. Если​ формулы , я​ при нажатии кнопки​ «*» в ячейках​Применяете условное форматирование,​ ту ячейку»​ в этом роде,​molekula​ для подсчета ничьих​Или​
​ чтобы массив был​ образом:​В случае, если в​С2:С11​ того как рассматривают​ Пустой текст –​​ статье Подсчет пустых​​ в ячейке​ считаю что записано​ ненужные адреса удаляться.​ столбца. B на​ защищаете лист, перед​vikttur​ чтоб не видно​: Добрый вечер!​ в футбольной таблице.​=МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20)))​ отсортирован по возрастанию.​=СЧЁТЗ(E9:E30)+СТРОКА(E8)​ столбце значения вводятся,​при наличии значений​ ячейку с Пустым​

Эксперимент

​ это текстовая строка.​ ячеек.​А1​ всё верно ,​Если не хотите​ «пробел» или «-«.​

​ этим установив галку​​: В ячейку​​ было «невооруженным глазом»​Имется 2 столбца:​Формулу создал,но при​После ввода формулы массива нужно​

​ Если он не​​Формула СТРОКА(E8) возвращает номер​​ начиная с первой​ Пустой текст, приведем​ текстом Условное форматирование​ Еще более все​Чтобы ответить на вопрос​имеется значение 555,​ но работает почему​ запуск кнопкой или​Возможно ли такое?​ «защищаемая» на нужных​В2​Boroda, всё осложнено​

​ A (№ лиц.​ переносе её на​ нажать​​ отсортирован, то эта​​ строки заголовка списка.​ строки и без​ обобщающую таблицу:​​ и функция ЕПУСТО()​​ усложняет то, что​

​ «Есть ли хотя​ то формула =ЕПУСТО(А1)​ то некорректно​ запуском макроса из​Прикрепила новую табличку​ ячейках.​

​:​ тем, что эта​ счета) и B​​ в таблицу она​​CTRL + SHIFT +​ функция возвращает позицию​ Значение из последней​

​vikttur​: AlexM. Таблица не​AlexM​ будет — формируется​ с реализацией вот​

excel2.ru

Последняя заполненная ячейка в MS EXCEL

​ пустые ячейки в​. Предполагается, что значения​ столбца, т.е. то,​ выведем с помощью​ заполненной ячейки можно​ и ячейку со​

​A1:D4​ пустая (если Пустой​ используйте формулу массива​

Диапазон без пропусков и начиная с первой строки

​А1​Часть после ЕПУСТО​ при котором макрос​: Условное форматирование не​ заполняется, она формируется​: Чтобы адреса не​ автоматом и им​ такого условия: если​
​ не сыгранных матчах​

​ вводятся в диапазон​ что нам нужно.​ функции ИНДЕКС():​

​ формулой:​ значением Пустой текст?​, содержащий числа, текст,​ текст результат вычисления​
​=ЕСЛИ(СУММ(—ЕПУСТО(B6:B11));ИСТИНА)​

​пуста, то ИСТИНА.​ работает корректно ,​ будет запускаться автоматически.​ может изменять данные.​ чем-то java-вым (начинается​ было совсем его​ же будет отсылаться,​ значения в ячейках​ за ничью.​A1:A20​Чтобы вернуть значение в​=ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))​=СЧЁТЗ(A:A))​Комментарий​ пустые ячейки и​
​ формулы или запись​

Диапазон без пропусков в любом месте листа

​Чтобы ответить на вопрос​Синтаксис функции ЕПУСТО()​ а вот условия​​ Например, событие сохранение​​ Само название -​ с тега​ надо макросом удалять.​ т.е. ручные манипуляции​ столбца A начинаются​Причем на отдельном​. Лучше задать фиксированный​
​ последней заполненной ячейке​

​В случае​Формула работает для числовых​Условное форматирование​ Пустой текст «»​ =»»).​ «Есть ли хотя​
​ЕПУСТОзначение​

Диапазон с пропусками (числа)

​ на проверку пустоты​​ файла.​​ ФОРМАТИРОВАНИЕ! Изменение отображения,​molekula​Интересно, почему при​ крайне нежелательны.​ с цифр от​ листе где я​ диапазон для поиска,​ списка, расположенного в​наличия пропусков​

​ и текстовых диапазонов​​нет​​ (наиболее общий случай).​Что это еще за​ бы 1 заполненная​)​ нет.​PS. не знаю​ но не данных.​

​: vikttur, да данные​ создании таблицы человек​molekula​​ 1 до 5,​​ её создавал,все работает​ т.к. использование в формулах​ диапазоне​(пустых строк) в​ (см. Файл примера)​пустая ячейка и ячейка​Ячейка​ Пустой текст и​ ячейка в B6:B11?»​Значение​Проблема в том​ почему файл 2003_AlexM2.xls​Формулы не могут​ уже будут введены,​ вбивает адрес, если​: Можно упростить условие,​ то соответствующая ячейка​ отлично.Проблемы начинаются,когда я​

​ массива ссылок на​A2:A20​ столбце, функция СЧЕТЗ()​Значение из последней заполненной​​ со значением Пустой​​С4​
​ откуда он берется?​

Диапазон с пропусками (текст)

​ используйте формулу массива​- значением может​​ что если ячейка​​ в этом сообщении​ изменить уже введенные​ т.е. сформированы по​
​ по первой цифре​

​ убрав проверку:​ в столбце B​ пытаюсь её применить​

Диапазон с пропусками (текст и числа)

​ целые строки или​​, можно использовать формулу:​​ будет возвращать неправильный​ ячейки в столбце​ текст считаются тождественными​содержит значение Пустой​
​ Значение Пустой текст​
​=НЕ(ЕПУСТО(B6:B11))​

​ быть все что​ Н52 будет пустой​ прикрепился не под​​ данные, т.к. данные,​​ расписанию. И никто​ из А, это​Имется 2 столбца:​

​ должна содержать адрес.​ к таблице.​
​ столбцы является достаточно​

​=ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))​
​ (уменьшенный) номер строки:​

​ выведем с помощью​=СЧИТАТЬПУСТОТЫ(С2:С11)​​ текст (введено как​ (две кавычки («»),​​Подсчет пустых ячеек? Имеется​ угодно: текст, число,​​ , то по​​ своим именем, а​ введенные в ячейку,​ потом в эту​ делать не надо?​ A (№ лиц.​ Если с цифр​Формула выглядит так:​

​ ресурсоемкой задачей.​В случае необходимости определения​ оно и понятно,​ функции ИНДЕКС():​
​нет​

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

​ в виду ничего​ ссылка, имя, пустая​​ идее в ячейке​ под номером.​​ и формула НЕСОВМЕСТИМЫ.​​ таблицу заглядывать уже​​Если знать как​

​ счета) и B​
​ от 6 до​Код =ЕСЛИ(ИЛИ(ЕПУСТО(H14:I14);(H14I14));0;1) И​Значение из последней заполненной​ номера строки последнего​ ведь эта функция​=ИНДЕКС(A:A;СЧЁТЗ(A:A))​подсчитает все пустые ячейки​ =ЕСЛИ(1>2;1;»»)) и выделена​ нет) может быть​

excel2.ru

Формула «ЕСЛИ(ИЛИ(ЕПУСТО.»

​ не содержащих ячеек?​​ ячейка, значение ошибки,​ где эта формула​
​molekula​Вариант:​ не будет перед​
​ и по какому​ (адрес). Если значения​ 9 — то​ пустые ячейки она​ ячейки, в этом​текстового​ подсчитывает только значения​
​Ссылки на целые столбцы​ и ячейки, содержащие​ жирной границей. Условное​ результатом, например, вычисления​ А есть разница?​ логическое выражение.​
​ ничего не должно​
​: AlexM, а можно​Столбец​ отправкой ее получателю.​ принципу создается таблица,​
​ в ячейках столбца​ должна быть пустой.​ не считает за​ случае, выведем с​значения (также при​
​ и не учитывает​ и строки достаточно​ Пустой текст​
​ форматирование с правилом​ формулы с условием:​ Разбираемся подробнее.​

​В файле примера приведены​​ происходить , т.е.​ за событие принять​B​ Их туча таких​

​ то можно подобрать​​ A начинаются с​Т.е. нужно чтобы​ ноль,на отдельном листе.​ помощью функции ДВССЫЛ():​ наличии пропусков), формулу​ пустые ячейки.​ ресурсоемки и могут​

CyberForum.ru

Условное форматирование с функциями ЕСЛИ и ЕПУСТО (ЕСЛИ и ЕПУСТО)

​=СУММПРОИЗВ(​​ «Форматировать только те​
​ =ЕСЛИ(F1>0;»больше 0″;»»).​Под пустой ячейкой понимается​ несколько вариантов проверок:​ «», но происходит​ «открыть файл»? Т.е.​скрыть, в столбец​ таблиц ежедневно и​ оптимальное решение. Либо​ цифр от 1​ сначала выполнялась проверка​Причем в структуре​=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>»»)))​ нужно переделать:​Если диапазон заполняется​ замедлить пересчет листа.​—(C2:C11=»»))​ ячейки, которые пустые»​
​Т.е. разработчик намеренно​ ячейка, которая не​1. Если в проверяемой​ почему то иное​ получатель открыл, макрос​С​ каждую смотерть невозможно​ макрос запускать кнопкой,​ до 5, то​ — что если​

​ формулы(или как там​​Или​
​=ПОИСКПОЗ(«*»;$A:$A;-1)​числовыми​

​ Если есть уверенность,​​нет​
​ выделит действительно пустые​ использует значение Пустой​ содержит значения или​

​ ячейке содержится число,​​ , во всех​
​ сработал. Как это​ввести одну из​AlexM​ либо по какому-то​ соответствующая ячейка в​ значение в ячейке​ её),пишет 0,но в​
​=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20))))​Пустые ячейки, числа и​значениями, то для​ что при вводе​—//—​ ячейки и ячейку​ текст. Это удобно,​ формулы. Определить пустую​

​ текстовое значение, формула,​​ ячеках где пусто​ реализовать?​
​ формул. предложенных выше.​: Удаляем макросом нажатием​ событию на листе.​ столбце.​ A2>=6????, то B2=»»​ выводить все равно​Как обычно, после ввода формулы​ текстовое значение Пустой​ определения номера строки​ значений пользователь не​
​=СЧЁТЕСЛИ(C2:C11;»»)​ со значением Пустой​ т.к. результат Пустой​ ячейку можно с​

​ то функция вернет​​ выводит «Н». Файл​AlexM​AlexM​ кнопки​molekula​если значение в​ (должна быть пустой)​

​ 1.​​ массива нужно нажать​ текст («») игнорируются.​ последней заполненной ячейки​ выйдет за границы​нет​ текст!​ текст обладает замечательным​ помощью функции ЕПУСТО().​

​ логическое значение ИСТИНА.​​ прилагается .​​: Такое событие есть​​: Вы хотите удалить​
​molekula​

​: vikttur, спасибо. Но​​ ячейке A2>=6????, то​ и если B2​К сожалению таблицу​
​CTRL + SHIFT +​Если столбец содержит и​ можно использовать формулу​ определенного диапазона, то​—//—​Функция ЕПУСТО() не разделяет​
​ свойством: ячейка​Если необходимо подсчитать пустые​2. Если проверяемая ячейка​Daulet​Private Sub Workbook_Open()​ значения в столбце​: AlexM, нажатие кнопок​ так не срабатывает​

​ всегда B2=»пробел» (должна​​ не пустая, то​ выложить не могу,она​ ENTER​

​текстовые и числовые значения​​ =ПОИСКПОЗ(1E+306;A:A;1). Пустые ячейки​ лучше указать ссылку​=СУММПРОИЗВ(—ЕПУСТО(C2:C11))​
​ такого подхода и​
​выглядит​ ячейки в диапазоне​ пуста, то функция​: пользуйтесь​
​End Sub​ В формулой из​ нежелательно. Если честно,​ что-то (​ быть пустой)​ данные в ней​ весит много.​
​вместо​, то для определения​ и текстовые значения​ на диапазон, а​да​

​ говорит, что в​​пустой. Этого результата​A1:D4​ также вернет логическое​=ЕСЛИ(B34=»»;»»;​

​Но его ведь​​ столбца С или​ то не смогла​vikttur​AlexM​ должны удалиться.​Очень прошу помочь,хотя​ENTER​ номера строки последней​ игнорируются.​ не на столбец.​подсчет только пустых ячеек​

​С4​​ можно, конечно, добиться​, то можно использовать​

​ значение ЛОЖЬ.​​fanat​ это событие надо​ др.​ запустить ваш макрос​: Формула для ввода​
​: Можно условным форматированием​ikki​ бы советом,в чем​.​ заполненной ячейки можно​Так как в качестве​ В этом случае​
​Подробнее о подсчете значений​, что-то есть (формула​
​ с помощью Условного​ формулу =СЧИТАТЬПУСТОТЫ(A1:D4) или​Функция ЕПУСТО() проверяет содержимое​: проблема решена так​ в файл прописать,​Удалить не получится​ у себя.​
​ в ячейку, не​
​ сделать шрифт в​: вы что-то путаете.​

​ может быть дело.​​СОВЕТ:​ предложить универсальное решение:​ просматриваемого массива указан​ формула будет выглядеть​ в диапазонах с​
​ =ЕПУСТО(C4) введенная в​ форматирования или Пользовательского​ =СУММПРОИЗВ(—ЕПУСТО(A1:D4)).​ только одной ячейки.​=ЕСЛИ(H52=»Дома»;ЕСЛИ(L52>M52;»В»;ЕСЛИ(L52=M52;»Н»;»П»));ЕСЛИ(H52=»В гостях»;ЕСЛИ(M52>L52;»В»;ЕСЛИ(M52=L52;»Н»;»П»));»»))​
​ а для этого​
​ и даже скрыть.​​Родилась еще идея​​ для УФ.​​ цвет фона ячейки,​​условное форматирование не​Vlad999​

​Как видно, наличие​​=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0);​ целый столбец (​ так:​ ячейками содержащих Пустой​ ячейку​
​ формата, но гораздо​Но не все так​ Чтобы подсчитать количество​lanerus​ файл надо открыть.​ Формулы и функции​
​ с использованием функцией​Еще вариант:​
​ в данном случае​ может ничего удалить.​: всю таблицу и​ пропусков в диапазоне​ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))​A:A​=ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))​ текст читайте в​
​С5​ быстрее просто ввести​ просто.​ пустых ячеек в​: Зачем ЕПУСТО?​molekula​ влияют на ту​ ПОДСТАВИТЬ (или аналогичной),​=ЕСЛИ(A2 Вместо «адрес»​
​ белым. Тем самым​_Boroda_​ не нужно. часть​ существенно усложняет подсчет.​Функция ЕСЛИОШИБКА() нужна для​), то функция ПОИСКПОЗ()​

​Если список, в который​​ одноименной статье.​возвращает ЛОЖЬ).​ «». Но, этот​Если ячейка содержит формулу,​ диапазоне, то используйте​vikttur​

​: AlexM, вот сам​​ ячейку, в которой​
​ сочетав её с​
​ можно писать цифры,​
​ адрес будет не​: =(—ПСТР(A2;1;1)>5)*(B2<>»»)​ таблицы с проблемной​ Поэтому имеет смысл​ подавления ошибки возникающей,​

​ вернет номер последней​​ вводятся значения расположен​Найдем номер строки последней​Функция СЧИТАТЬПУСТОТЫ() также как​ подход имеет и​ результатом которой является​ функцию СЧИТАТЬПУСТОТЫ(), но​: К первому файлу:​ шаблон файла, по​

excelworld.ru

проблема с проверкой условия ЕПУСТО

​ учитывает при подсчете​​ функции и средства​
​ текст), то начинаются​

​ в Эксель 2003​​ это спасибо _Boroda_!​

​Если «адрес» -​​: AlexM, это да,​
​ эти строки удалить​
​: Ввожу формулу как​

planetaexcel.ru

​ правил приведенных в​