Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.
Внешний вид отображения данных также является достаточно важным атрибутом, и если ненулевые значения мы привыкли показывать, то бывают ситуации когда нули визуально лучше не показывать и скрыть.
Обычно в таких случаях нули в таблицах заменяют либо на пустое поле, либо на аналогичные по смыслу символы (к примеру, в виде прочерка).
В зависимости от требуемых задач можно выделить несколько различных начальных условий для подобной замены нулей:
Почему Эксель не считает формулу: что делать
- A — запись когда число положительное;
- B — запись когда число отрицательное;
- C — запись когда число равно нулю;
- D — запись если в ячейке не число, а текст (обычно для чисел не используется).
Описанная выше процедура не подходит для одной или нескольких ячеек. Она действует для всего листа. Чтобы обойти это ограничение, некоторые пользователи вводят один апостроф перед формулой в ячейке. Например, ‘=СУММ(1+2+3)
Решение 2: отключаем режим “Показать формулы”
Когда мы вместо результатов видим сами формулы, это может быть связано с тем, что активирован режим показа формул, и его нужно отключить.
Иногда может возникать ситуация, когда формула посчитала какой-то результат, однако, если мы решим изменить значение в одной из ячеек, на которую формула ссылается, пересчет выполнен не будет. Это исправляется в параметрах программы.
Почему Эксель не считает сумму выделенных ячеек или формулу автоматически, не протягивается формула, не суммируются числа или столбец в Excel
- #ДЕЛ/0! – результат деления на ноль;
- #Н/Д – ввод недопустимых значений;
- #ЧИСЛО! – неверное числовое значение;
- #ЗНАЧ! – используется неправильный вид аргумента в функции;
- #ПУСТО! – неверно указан адрес дапазона;
- #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
- #ИМЯ? – некорректное имя в формуле.
Совет: Подсказка на случай, если вам не удастся найти клавишу значка тупого ударения (`): обычно (но не всегда) она располагается в верхнем левом углу клавиатуры. Если на вашей клавиатуре ее трудно найти, вы можете отобразить и скрыть формулы, перейдя на вкладку Формулы и выбрав команду Показывать формулы. Чтобы вернуться к стандартному режиму, нажмите эту кнопку еще раз.
Найти первое непустое значение в строке
Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).
Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве
Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).
Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: .
Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: .
Если функция ИНДЕКС возвращает массив, ее можно использовать внутри других функций, ожидающих, что аргумент является массивом.
Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1;0) значение ноль, мы получим массив .
Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1;;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)).
-
Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.
Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий.
Как быть здесь. Сперва предлагаю почитать об ошибках — здесь. В этой же статье предложено решение многих проблем. Чтобы разобраться со случаем, когда ошибка в расчетах, существует специальный инструмент — Вычислить формулу (Формулы — Раздел Зависимости формул — Вычислить формулу)
