Excel проверка на пустую ячейку

Примеры функции ЕПУСТО для проверки пустых ячеек в 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)

Источник: exceltable.com

Найти первое непустое значение в строке

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).

Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: <ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА>.

Читайте также:  Как в excel умножить ячейку на число

Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: <0;0;1;0;0;0;0;0;0>.

Итак, фрагмент формулы 1-ЕПУСТО(С2:К2) возвращает массив <0;0;1;0;0;0;0;0;0>. Это немного странно, так как от такого фрагмента Excel ожидает, что вернется одно значение. Странно, но не смертельно. Функция ИНДЕКС также обычно возвращает одно значение. Но вот, что написано в Справке Excel: Если указать в качестве аргумента номер_строки или номер_столбца значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца.

Если функция ИНДЕКС возвращает массив, ее можно использовать внутри других функций, ожидающих, что аргумент является массивом.

Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1;) значение ноль, мы получим массив <0;0;1;0;0;0;0;0;0>.

Функция ПОИСКПОЗ выполняет поиск искомого значения в одномерном массиве и возвращает относительную позицию первого найденного совпадения. Формула =ПОИСКПОЗ(1,МАССИВ,0) просит Excel найти номер ячейки в МАССИВЕ, которая содержит первую встретившуюся единицу. Функция ПОИСКПОЗ определяет в каком столбце содержится первая непустая ячейка. Когда вы просите ПОИСКПОЗ найти первую 1 в массиве <0;0;1;0;0;0;0;0;0>, она возвращает 3.

Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1;<0;0;1;0;0;0;0;0;0>;0) и возвращает результат 3.

В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).

Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

Дополнительные сведения: если все ячейки пустые, то формула возвращает ошибку #Н/Д.

Альтернативные стратегии: когда вы из единицы вычитаете значение ЕПУСТО, вы преобразуете логические значения ИСТИНА/ЛОЖЬ в числовые 1/0. Вы могли бы пропустить этот шаг, но тогда вам придется искать ЛОЖЬ в качестве первого аргумента функция ПОИСКПОЗ: =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)).

Источник: baguzin.ru

Как заполнить пустые ячейки?

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

Заполнение ячеек с отсутствующими значениями стандартными средствами Excel

Заполнение пустых ячеек нулями (Поиск и замена)

1. Выделение пустых ячеек в нужном диапазоне: Вкладка «Главная», группа кнопок «Редактирование», меню кнопки «Найти и выделить», пункт меню «Выделить группу ячеек». В диалоговом окне «Выделить группу ячеек» выбирается опция «Пустые ячейки»;

2. Когда пустые ячейки выделены, вызывается диалоговое окно «Найти и заменить» либо через меню этой же кнопки «Найти и выделить», либо сочетанием горячих клавиш Ctrl+F и перейти на вкладку «Заменить».

3. Поле «Найти» остается пустым, в поле «Заменить на» вписывается нуль, либо другое необходимое значение. После этого нажимается кнопка «Заменить все».

Заполнение пустых ячеек нулями (Ctrl+Enter)

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

2. Курсор помещается в строку формул и вписывается необходимое значение, например, нуль;

3. Зажимается клавиша Ctrl на клавиатуре, после чего нажимается клавиша Enter.

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

Заполнение пустых ячеек верхними значениями (формула)

1. Пустые ячейки можно заполнить значениями предыдущих ячеек.

2. Выделяются пустые ячейки (также как это описано выше);

3. Курсор помещается в строку формул и ставится знак «равно», после этого указывается адрес вышестоящей ячейки (можно просто кликнуть по нужной ячейке левой кнопкой мыши);

4. Удерживая клавишу Ctrl на клавиатуре нажимается клавиша Enter.

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

Быстрое заполнение пустых ячеек верхними значениями при помощи макроса

Во всех описанных выше способах пустые ячейки, подлежащие заполнению должны быть предварительно выделены, но этого можно и не делать, если использовать процедуру, написанную на VBA. Пример такой процедуры, представлен на странице http://macros-vba.ru/makrosy/excel/266-kak-zapolnit-pustye-yachejki. Макрос проверяет есть ли значения в ячейках выделенного диапазона и заполняет пустые ячейки значениями верхних ячеек в заданном столбце. При необходимости в процедуру можно добавить цикл по столбцам, тогда заполняться будут пустые ячейки выделенной области в нескольких столбцах.

Надстройка для быстрого заполнения пустых ячеек верхними, нижними, левыми или правыми значениями

Максимально быстро, не делая лишних манипуляций, буквально за пару кликов можно осуществить заполнение пустых ячеек значениями соседних ячеек при помощи надстройки для Excel. Достаточно только вызвать диалоговое окно надстройки, указать диапазон ячеек и выбрать одну из доступных опций: заполнить верхними значениями, заполнить нижними значениями, заполнить левыми значениями, заполнить правыми значениями после чего нажать кнопку «Пуск».

Надстройка позволяет:

1. Выделять необходимый диапазон для заполнения пустых ячеек значениями соседних ячеек;

2. заполнять пустые ячейки значениями ячеек, находящихся:

Видео по работе с надстройкой:

Источник: macros-vba.ru

excel проверка на пустую ячейку

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

В данном случае мы используем ЕСЛИ вместе с функцией ЕПУСТО:

Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вы также можете легко использовать собственную формулу для состояния «Не пустая». В следующем примере вместо функции ЕПУСТО используются знаки «». «» — фактически означает «ничего».

Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вот пример распространенного способа использования знаков «», при котором формула не вычисляется, если зависимая ячейка пуста:

Если (D3 — Nothing, возвращается Nothing, в противном случае вычисляется формула).

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

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

Читайте также:  Как в excel уменьшить ячейки в

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

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

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

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

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

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

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

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

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

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

Периодически при создании различных макросов в VBA Excel возникает потребность в том, чтобы алгоритм макроса самостоятельно определял, пуста ли ячейка или заполнена данными.

Далее приведем несколько простых макросов для определения заполнения ячейки данными и разберем принцип их работы. Фрагменты этих макросов вы можете добывать в свой код для реализации проверки заполнения ячеек.

Рассмотрим несколько вариантов VBA алгоритмов:

Алгоритм для строго определенной ячейки с применением её адреса.

Пример для ячейки с адресом B6:

  • Private Sub CommandButton1_Click() ‘наименование алгоритма
  • If IsEmpty(Range(«B6»)) = True Then ‘условие, если ячейка пустая, то
  • MsgBox («В ячейке нет данных») ‘вывод сообщения, что в ячейке нет данных
  • Else ‘в противном случае
  • MsgBox («Данные внесены в ячейку») ‘вывод сообщения, что в ячейке есть данные
  • End If ‘конец блока «если»
  • End Sub ‘конец алгоритма

Источник: window-10.ru

Отображать пустое значение при ссылке на пустую ячейку в Excel 2010

У меня есть книга Excel 2010, в которой содержится несколько отдельных рабочих листов. Ячейки на одном из листов связаны с отдельными ячейками на двух других листах в одной книге. Я использую ссылку на прямую ячейку, которая по существу говорит, что любое значение, введенное в конкретную ячейку на одном листе, также заполняет ячейки на двух других листах. Я использовал функцию (=) со ссылкой на ячейку, чтобы выполнить это.

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

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

17 ответов

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

Это заставит Excel сделать ссылку на ячейку текстовым значением, тем самым предотвращая превращение заготовок в нули.

1) Предоставление other.cell.reference представляет собой ссылочную формулу, которую вы в настоящее время используете после = (например, Sheet17!$H$42 ), замените ссылку на ссылку с помощью

2) Установите формат «Число» ваших связанных ячеек в «Пользовательский»: General;–General; .

3) В разделе «Параметры Excel», «Дополнительно» на странице «Параметры отображения для этой рабочей таблицы» снимите флажок «Показывать нуль в ячейках с нулевым значением». Предупреждение: это приведет к исчезновению всех нулей на листе.

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

  • вам не нужны лишние ячейки для ввода формулы, а затем ссылки на вторую ячейку
  • Вам не нужно вводить формулу дважды
  • этот метод различает нулевое значение и пустую ячейку
  • не требуется VBA
  • не требует именных диапазонов

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

A1 в этом случае может быть заменен любой ячейкой, включая другой лист, книгу или INDIRECT ().

Примечания о том, как это работает:
IFERROR () – второй аргумент установлен на пустую строку, поэтому, если возникает ошибка, мы получаем пустую строку. Поэтому нам нужно убедиться, что исходная ячейка пуста, возникает ошибка.

Числовой метод . Строгое значение источника, затем умножьте на 1. Литеральная строка emtpy * 1 = #VALUE, String с числовым автоматически преобразуется в числовое значение и не возникает ошибка.

Я тоже не нашел лучшего решения, кроме Скотта.

Но в сочетании с подходом отсюда он может быть почти терпимым, я думаю:

Скажем, у меня есть такая формула

Эта формула считывает значение ячейки из листа данных с использованием условного выбора и представляет их на другом листе. У меня нет контроля над формированием ячейки в листе данных.

Я иду в Insert> Имя> Определите и в «Именах в книге» я создаю новое имя «RC_P_B». Затем в поле «Относится к» я копирую свою формулу (без <> символов – это формула массива).

Затем вы можете использовать формулу Скотта без необходимости повторять весь текст формулы:

Я считаю, что это лучше, чем копировать всю формулу.

Я просто не использую формулу для решения этой проблемы. Все, что я делаю, – это условно форматировать ячейки до цвета шрифта white, если значение ячейки равно 0.

Существует еще один трюк: установите пустую ячейку soucre в формулу =”” . Подробнее см. .

Если связанная ячейка не является числовой, вы можете использовать оператор IF с ISTEXT:

Я долго искал элегантное решение этой проблемы.

Я использовал формулу = if (a2 = “”, “”, a2) в течение многих лет, но я считаю, что это немного громоздко.

Я попробовал выше предложение = a2 & “”, и хотя он, похоже, работает, он показывает число, которое на самом деле является текстом, поэтому форматирование номера не может применяться и статистические операции не работают, такие как сумма, средняя, ​​средняя и т. поэтому, если это работоспособные номера, которые вы ищете, это не соответствует счету.

Я экспериментировал с некоторыми другими функциями и нашел то, что я считаю самым элегантным решением на сегодняшний день. Продолжая приведенный выше пример:

= CELL (“contents”, A2)

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

Решение для Excel 2010:

  1. Откройте «Файл»
  2. Нажмите «Опции»
  3. Нажмите «Дополнительно»
  4. В разделе «Параметры отображения» для этого рабочего листа «BBBB»
  5. Разогнать поле «Показать нулевое значение для ячеек с нулевым значением».

Источник: sprosi.pro

Как убрать нули в ячейках в Excel?

Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).

Читайте также:  Как в excel сделать ссылку на ячейку другого листа

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.

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

Обычно в таких случаях нули в таблицах заменяют либо на пустое поле, либо на аналогичные по смыслу символы (к примеру, в виде прочерка).

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

  • На всем листе (в каждой ячейке);
  • В конкретных (выделенных) ячейках;
  • В формулах;
  • В сводных таблицах.

Как скрыть нулевые значения в Excel?

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

На всем листе

Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):

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

Нажимаем OK и в результате исходная таблица приобретает следующий вид:

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

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

Важная деталь.

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

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

Частично эту проблему может решить следующий вариант.

В выделенных ячейках

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

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

  • A — запись когда число положительное;
  • B — запись когда число отрицательное;
  • C — запись когда число равно нулю;
  • D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

Так как наша задача в удалении именно нуля из записи, то нам нужно в маске прописать как будет выглядеть третий параметр (часть C из маски), а заодно и первый (часть A, положительное число) со вторым (часть B, отрицательное число).

Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;”-“, чтобы в ячейке стоял прочерк), и нажимаем OK.

В результате получаем:

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

В формулах

Проблему с нулями в ячейках также можно решить и формульным путем.
Если в ячейке значение вычисляется по формуле, то с помощью функции ЕСЛИ мы можем задать различные сценарии отображения (например, если значение не равно 0, то оставляем значение, а если равно 0, то возвращаем пустое поле или любой другой альтернативный вариант).

Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;”-“;A1-B1):

Как мы видим теперь в столбце с отклонениями вместо 0 стоят прочерки, чего мы как раз и хотели добиться.

В сводных таблицах

Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:

Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:

  • Работа с данными как с ячейками листа;
    В этом случае мы не делаем разницы между ячейками сводной таблицы и ячейками листа, т.е. можем воспользоваться вышеописанными способами, чтобы скрыть 0.
  • Работа с данными в сводной таблице.
    В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.

Так как первый вариант мы уже детально разбирали выше, то давайте поподробнее остановимся на втором, в котором также выделим 2 способа, чтобы убрать нули из таблицы:

  • С помощью создания фильтров;
  • С помощью настройки параметров сводной таблицы.

Рассмотрим оба варианта.

Создание и применение фильтра

Давайте в сводной таблице добавим в качестве фильтра поля, по которым мы хотим скрыть нули (в данном примере это Количество, но в принципе тут может стоят все что угодно).

Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений или (пусто), тем самым исключив эти значения из отображения:

Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.

Перейдем к следующему варианту.

Настройка параметров сводной таблицы

С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:

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

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

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

Источник: tutorexcel.ru