Elettracompany.com

Компьютерный справочник
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

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

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

Функция ЕСЛИ: производим действия в зависимости от условия

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

В примере ниже столбец F содержит даты завершения закупок шоколада.

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

Формула в ячейке F3:

Как работает эта формула?

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек, значит, пустых ячеек нет и можно производить вычисление. Если равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.

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

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

Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае — производим вычисления.

Все достаточно просто, но перечислять кучу ссылок на ячейки не слишком удобно. К тому же, здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы она нуждается в корректировке. Это не слишком удобно, да и забыть можно сделать это.

Рассмотрим теперь более универсальные решения.

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

И, наконец, еще одна формула Excel, которая позволит производить расчет только при наличии непустых ячеек.

Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

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

Итак, перед нами уже знакомая формула

Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.

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

IF: как оставить ячейку пустой, если условие ложно ( «не работает» )

Я хотел бы написать инструкцию IF, где ячейка остается пустой, если условие FALSE.
Заметим, что если в C1 (, для которой условие ложно) введена следующая формула:

и если C1 проверяется на то, что он пуст или не использует =ISBLANK(C1) , это вернет FALSE , даже если C1 кажется пустым. Это означает, что формула =IF(A1=1,B1,»») технически не оставляет ячейки пустыми, если условие не выполнено.

Любые мысли о способе достижения этого? Спасибо,

Попробуйте это вместо

Это вернет true для ячеек, которые действительно пусты или содержат только пробел.

См. этот пост для нескольких других опций.

изменить

Чтобы отразить комментарии и то, что вы закончили: вместо того, чтобы оценивать «, введите другое значение, такое как» Удалить «, а затем выполните поиск» deleteeme» вместо пробелов.

К сожалению, нет формулы, способной привести к поистине пустой ячейке, «» — это лучшие формулы, которые могут предложить.

Мне не нравится ISBLANK, потому что он не будет видеть ячейки, у которых «» есть пробелы. Вместо этого я предпочитаю COUNTBLANK, который будет считать «» пустым, поэтому в основном =COUNTBLANK(C1)>0 означает, что C1 пуст или имеет «» .

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

Я хотел добавить, что есть еще одна возможность — использовать функцию na() .

например. =if(a2 = 5,»good»,na());

Это заполнит ячейку # N/A, и если вы нарисуете столбец, данные не будут отображаться. Я знаю, что он не является «пустым» как таковым, но это еще одна возможность, если у вас есть пустые строки в ваших данных и «» является допустимым вариантом.

Кроме того, count(a:a) не будет считать ячейки, которые были установлены на n/a, выполнив это.

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

предполагая, что C1 является «пустой» ячейкой

Я нашел, что это обходное решение похоже на трюк:

Измените исходную формулу:

Затем выберите столбец, найдите и замените «filler» ничем. Ячейки, которые вы хотите быть пустыми/пустыми, на самом деле пустые, и если вы протестируете «ISBLANK», он вернет TRUE. Не самый элегантный, но он быстрый, и он работает.

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

Это будет работать (модификация выше, обходной путь, а не формула)

Измените исходную формулу:
= ЕСЛИ (А1 = 1, В1, «наполнитель» )

Поместите фильтр в электронную таблицу, выберите только «filler» в столбце B, выделите все ячейки с помощью «filler» в них, нажмите «Удалить», удалите фильтр

Вы можете сделать что-то вроде этого, чтобы показать пустое пространство:

Внутри if перед первой запятой это условие, тогда результат и возвращаемое значение, если true и последнее значение будет пустым, если условие false

либо дает ответ «» (который не рассматривается как пустой), либо содержимое B1.

Если вы хотите, чтобы формула в D1 отображала TRUE, если C1 является «» и FALSE, если у C1 есть что-то другое, тогда используйте формулу

Вот что я делаю

Используйте условие ИЛИ ИЛИ (ISBLANK (ячейка), ячейка = «»)

Я думаю, все, что вам нужно сделать, это установить значение условия NOT TRUE, чтобы оно показывало любую ошибку, а затем вы фильтруете ошибки с помощью IFNA() .

Вот как должна выглядеть ваша формула =ifna(IF(A1=1,B1,NA()))

Чтобы проверить данные в столбце A для пробелов

Шаг 1: Шаг 1: B1 = isblank (A1)

Шаг 2: перетащите формулу для всего столбца, скажем, B1: B100; Это возвращает Ture или False от B1 до B100 в зависимости от данных в столбце A

Шаг 3: CTRL + A (Selct all), CTRL + C (Скопировать все), CRTL + V (Вставить все как значения)

Шаг4: Ctrl + F; Найти и заменить функцию Найти «False», заменить « оставить это пустое поле«; Найти и заменить ВСЕ

Там ты идешь Чувак!

Вместо использования «» используйте 0. Затем используйте условное форматирование для цвета 0 в цвет фона, чтобы он выглядел пустым.

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

Это должно работать: = IF (A1 = 1, B1)

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

Функции 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 заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).

Приветствую всех, дорогие читатели блога 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 способа, чтобы убрать нули из таблицы:

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Длина строки excel формула
Ссылка на основную публикацию
Adblock
detector