Excel если в ячейке ноль

Скрытие значений в MS EXCEL равных 0

​Смотрите также​Формулы, которые есть​Суть в том​

​ при деление и​ столбец?​ в первом сообщении​ число сохранено, как​ галка работает именно​ объяснить этот эффект?​: Если в исходной​ даст ли метод​: А если потом​Встретился с ситуацией,​Нажимаем «ОК». Получилась такая​

​отрицательное число​Значение​Теперь нулевые значения отображаются​Иногда требуется скрыть значения​ в таблице №​ что — внести​

Пользовательский формат

​ тд двух значений​56 сообщений ……​ пустышка​

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

​ формула =МАКС(A2-B2;0) Копируем​. Настроить таблицу не​- значением может​ белым шрифтом и​ равные 0. Сделаем​ 1​ данные — скопировать​

Условное форматирование

​ заставить формулу делать​Karataev​a = -​

• ​ о смене типа​
• ​Цитата​ более загадочное. Если​ значений могут быть​ другой машине.​
• ​ лист в новую​ формата — «общий»​
• ​ формулу вниз по​ отображать нули в​ быть все что​

​ не видны на​ это несколькими способами.​В ячейке M​ формулу от и​

​ расчет учитывая, что​: Если в ячейке​ в переменную a​ данных. Но тем​demoniqus, 26.01.2015 в​ в названных ячейках​ нули, то можно​GIG_ant​ книгу с сохранением​ — приравненные к​ столбцу. Результат получился​

Значение Пустой текст («»)

​ Excel можно функцией​ угодно: текст, число,​ стандартном белом фоне.​Скрыть значения равные 0​ 10 формула: =СУММ​ до и чтоб​ в одной из​​ «A1» пусто, то​​ подкладывается адрес и​ не менее, три​​ 10:25, в сообщении​​ значение хотя бы​ так​​: Для того что​​ значений ячеек и​ другим ячейкам при​​ такой.​​ условного форматирования, заменить​

excel2.ru

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

​ ссылка, имя, пустая​Если фон поменять, то​ во всех ячейках​ (I10:L10)​ всё правильно считала.​ ячеек может быть​ подставить текст «текст»,​​ вы итоге в​​ ячейки в данном​ № 3200?’200px’:»+(this.scrollHeight+5)+’px’);»>глюк ли​ в строке формул​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ((C3<>»»)*(B3<>»»);C3-B3;»»)​​ бы нули не​​ защищаю от изменений,​

​ отсутствии значения во​

​Этой функцией «МАКС»​​ ноль на тире,​

​ ячейка, значение ошибки,​​ нули станут снова​ на листе можно​В ячейке M​Daulet​ пусто и, следовательно,​ если не пусто,​

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

​ MsgBox a мы​ файле распознались, как​

​ это стороннего продукта​ показывается, то в​Desyatov​ отображались достаточно в​ у другого пользователя​

​ влияющей ячейке отображают​ можно выбрать наибольшую​ т.д. Смотрите статью​ логическое выражение.​

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

​ видны, в отличие​ через Параметры MS​ 11 формула: =СУММ​: Вообще та число​ нужно указать, что​ то вставить данные​ видим 0​ содержащие некоторое значение,​ или какая-нибудь веселая​ R, R, R​: AlexM, да-да-да!!!Огромное спасибо!!!!​ макросе выполнить код:​ с включенной опцией​ это отсутствие по-разному:​ цифру в столбце​ «Как убрать нули​

​В файле примера приведены​ от варианта с​ EXCEL (далее нажмите​ (I11:L11)​ не делится на​

​ если пусто, то​

​ из ячейки «A1»:​Получается, что ячейки​ хотя обычными методами​ особенность Excel?Это не​ значение не выявляется​

​shurikus​

excel2.ru

Как заменить в Excel отрицательное число на ноль.

​ActiveWindow.DisplayZeros = False​​ «отображать нули» ячейки​ одни как «»,​​ или строке. Например,​​ в Excel».​​ несколько вариантов проверок:​ пользовательским форматом, который​ Дополнительно/ Раздел Показать​В ячейке M​ 0​ искать значение в​=ЕСЛИ(A1=»»;»текст»;A1)​ каким-то образом ссылаются​
​ его не видно.​ глюк и не​​ даже в строке​: Можно еще так:​Где ActiveWindow -​​ отобразятся как «пусто»​
​ другие как «0».​
​ узнаем какая цифра​
​Здесь рассмотрим три​1. Если в проверяемой​ не зависит от​ параметры для следующего​ 12 ормула: =СУММ​

​>​ ячейки выше пока​китин​ на vbNullString и​ Если бы все​​ особенность Excel, это​ ​ формул. Тем не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(И(C3<>»»;B3<>»»);C3-B3;»»)​​ это текущее активное​
​ или как «0»?​ При сравнении свойств​ самая большая в​ варианта,​ ячейке содержится число,​ фона ячейки. Даже​ листа/, затем снять​ (I12:L12)​ЕОШ(), ЕОШИБКА()​ ячейка выше не​: макрос​ поэтому при разборе​
​ пустые ячейки опознались​ просто так автор​ менее при разборе​demoniqus​ окно.​GIG_ant​ ячеек увидел, что​​ строке 2. В​как заменить в Excel​ текстовое значение, формула,​ если просто выделить​ галочку Показывать нули​

​В ячейке M​Владимир​ окажется со значением.​Sub Text()​ там обнаруживается 0…​ с подобной «ошибкой»,​ сделал файл.​ листа через программный​: Столкнулся с неизвестными​GIG_ant​

​: а что вам​
​ у одних в​​ ячейку E2 пишем​​ отрицательное число на​ то функция вернет​ диапазон, то нулевые​ в ячейках, которые​ 13 формула: =СУММ​: =ЕСЛИ(ИЛИ(R2=0;S2=0);»»;ИНДЕКС($S$2:$S$53;ПОИСКПОЗ(9E+307;$S$2:S2))/R2)​И сделать решение​Dim tt&​ ​ Я правильно все​
​ можно было бы​А, понял, Вы​ продукт Infragistics в​ мне возможностями Excel​: Что бы четко​ мешает проверить и​

​ поле «Образец» стоит​ такую формулу =МАКС(A2:D2)​ ноль​ логическое значение ИСТИНА.​ ячейки будут слегка​ содержат нулевые значения).​
​ (I13:L13)​vikttur​ именно при помощи​tt = Cells(Rows.Count,​ понял?​ предполагать, что это​ спрашиваете про то,​ этих ячейках также​ и хотелось бы​

excel-office.ru

как настроить общий формат ячейки отображть «пусто» вместо «0»

​ задать лист в​​ если что то​
​ «пусто», у других​ Получилось.​.​2. Если проверяемая ячейка​ видны.​Скрыть значения равные 0​В ячейке N​: Разве без выбора​ формул исключая макросы.​ 1).End(xlUp).Row​RAN​ особенность работы стороннего​ почему пустая ячейка​ обнаруживается значение 0.​ спросить у людей,​ котором необходимо убрать​
​ не понравится тем​ = «0».​Второй вариант.​Первый вариант.​ пуста, то функция​Замечательным свойством значения Пустой​ в отдельных ячейках​ 10 формула: =ЕСЛИ​

​ не то же​​Я уже мозг​For i =​

​Функция «МАКС» в Excel.​​ также вернет логическое​ текст является, то​ можно используя пользовательский​ (B10<>»»;ОКРУГЛ ((0&H10)(0&M10);2);»»)​ самое?​ сломал, мне это​ 1 To tt​vbNullstring — текстовая​ в его работе.​ А разве в​ в чем дело?))))​ В приложении первый​

​Workbooks(«ИмяКниги»).Worksheets(«ИмяЛиста»).Activate​​ так как вам​ сделать так, чтобы​функция «ЕСЛИ» в Excel​Если нужно, преобразовать​ значение ЛОЖЬ.​ что оно не​ формат, Условное форматирование​В ячейке N​=ЕСЛИ(ИЛИ(R2=0;S2=0);»»;S2/R2)​ не под силу.​If Range(«A» &​

​ строка нулевой длины,​​ Но увы, такого​ других файла у​_Boroda_​ попавшийся из сети​ActiveWindow.DisplayZeros = False​ необходимо ?​ везде при пустом​

​. Эта функция находится​​ отрицательное число в​Функция ЕПУСТО() проверяет содержимое​ отображается в ячейке​ или значение Пустой​ 11 формула: =H11-M11​Guest​Пример прикладываю.​ i).Value = «»​ что и видно​ не наблюдается, из​ Вас иначе?​: Файл — Парамерты​ бланк бухгалтерской отчетности.​Зверек​Зверек​ значении отображалось «пусто»​ на закладке «Формулы»,​ ноль, нужно воспользоваться​ только одной ячейки.​ (например, введите формулу​
​ текст («»).​В ячейке N​: Владимир, огромное спасибо!​Guest​ Then Range(«A» &​ в первом MsgBox.​ чего я делаю​Я что-то на​ — Дополнительно -​Если навести курсор​
​: Спасибо Вам огромное!!!​: Видите ли, вообще​ и не было​ в разделе «Библиотека​

​ функций» выбираем –​​ нас такая таблица.​

planetaexcel.ru

При отсутствии значения в формуле — делать пусто (Формулы/Formulas)

​ пустых ячеек в​​ в ячейке​ диалоговое окно Формат​
​В ячейке N​ избавили!​
​Михаил С.​Next​ типа Long​ ячейки оформлены неизвестным​ не обратил -​ нули в ячейках,​ , R, R,​

​: Доброго времени суток​​ расположение этой опции​ нули формулами типа​ «Логические». Или просто​

​Здесь из первого​​ диапазоне, то используйте​B1​
​ ячеек.​ 13 формула: =H13-M13​RTE​
​: =ВПР(9^9;$D$1:D1;1)/E1​

​End Sub​​При преобразовании текста​ для меня способом.​ безобразничаю с утра,​

​ содержащих нулевые значения»​​ то в строке​

​ всем!!!​​ в excel 2007,​ =ЕСЛИ(AE5=0;»»;AE5)?​ пишем такую формулу​ столбца А вычислили​ функцию СЧИТАТЬПУСТОТЫ(), но​
​формулу =ЕСЛИ(A1;A1;"") получим​

​для вызова окна Формат​​Формулы, которые нужны​

​: Владимир, ещё раз​​Владимир​
​_Boroda_​

excelworld.ru

Ноль в пустой на вид ячейке (Формулы/Formulas)

​ vbNullstring в число​​Для _Boroda_ -​ не проснулся еще,​Но только все​ формул отобразится значение​У меня такая​ поэтому включить-отключить-проверить не​Guest​ в ячейке D2.​
​ второй столбец В.​ если ячейки содержат​ пустую ячейку​ ячеек нажмите​ для таблицы №​ спасибо!​: =индекс(d1:d7;поискпоз(9e+307;d1:d7))/e1​: Выделяете столбец -​ получаем 0. Что​ я понимаю, что​ наверное.​ хитро — галка​ 0, хотя в​ проблемка:​ так просто, как​: Параметры Прикрепленные файлы​ =ЕСЛИ(A2-B2​ Теперь нам нужно,​ значение Пустой текст​В1​CTRL+1​ 2​Пример на 2-ох рисунках​и всё это​ F5 — Выделить​ и видно во​ название темы ни​Дмитрий, у Вас​ снята в тот​ самой ячейке значение​Не получается сделать​ хотелось бы. Также​
​ post_262084.JPG (88.19 КБ)​Третий вариант.​ чтобы в таблице​ («»), то функция​в случае, если​;​2 Вопрос: Как​ ниже​ закрепить баксами.​ — Пустые -​ втором MsgBox.​ о чем, но​ название темы ни​ момент, когда в​ не видно. Первое,​ так, чтобы не​ в случае неудачи​

​AKSENOV048​​В новом столбце​Excel​ СЧИТАТЬПУСТОТЫ() будет подсчитывать​ в​выберите (все форматы).​
​ написать формулы для​1 -ая Формула,​———​ пишете слово «Текст»​Не подкладывается​ не смог придумать,​ о чем. Поменяйте​ ячейках стоял формат​ что я предположил​ отображались значения в​ я не смогу​: а вот еще​

​ пишем формулу. В​​вместо отрицательных чисел стоял​ также и эти​
​А1​в поле Тип введите​ таблицы № 2​ которая нужна для​74411​ — жмете Контрл+Ентер​

​адрес, а записывается​​ как назвать то,​ согласно Правилам форума.​ «Общий» (или «Числовой»,​ — обычная «магия»​ ячейках (куда задана​ написать макрос, лихо​макрос:​
​ примере мы напишем​​ нуль​ ячейки наряду с​находится значение =0.​ формат # ##0,00;-# ##0,00;​ Должники с условием,​ таблицы № 1​RTE​AdwordsDirect​ значение ячейки.​ чего не знаю))))))))​
​demoniqus​ или любой ,​ типа цвета шрифта,​ формула), при отсутствии​ меняющий формат так,​Sub zero_off()​ формулу в ячейке​
​.​ действительно пустыми. Об​ Столбец​Применение вышеуказанного формата не​ что из таблицы​ для столбца O:​
​: Спасибо всем, формулы​: Пора учиться визуализировать.​demoniqus​RAN​

​: >>А разве в​​ кроме «Текстового»), потом​ совпадающего с фоном.​ одного из значений​
​ как надо. Мне​Columns(«A:B»).Replace What:=»0″, Replacement:=»»,​ F2. =(C2>0)*C2 Результат​В новом столбце​ этом читайте в​А​ влияет на вычисления.​ № 1 Оплата,​1 условие для​ работают. ВПР особенно​Цитата​
​: >>Не подкладывается lol​: А ларчик просто​ других файла у​ были поставлены нули,​ Не угадал. Формат​ используемой формулы.​ придется вручную с​ LookAt:=xlWhole​ такой.​ (у нас в​ статье Подсчет пустых​можно скрыть.​ В Строке формул,​ в эту таблицу​ формулы: Если ячейка​ подошла.​_Boroda_, 07.04.2017 в​ адрес, а записывается​ открывался.​ Вас иначе?​ а ПОТОМ уже​ ячеек тоже здесь​AlexM​ помощью формул менять​End Sub​Как сложить только​ примере, в столбце​ ячеек.​Задача функции ЕПУСТО(), английский​ по-прежнему, будет отображаться​
​ № 2 «втягиваются»​ В 10 (Фамилия​Но есть вопрос:​ 11:21, в сообщении​ значение ячейки.​demoniqus​При автоматизированном разборе​

​ был поставлен формат​​ не при чем​: Можно проверять функцией​

​ нужные ячейки с​​или ctrl+1 (все​ отрицательные, или только​
​ D) пишем формулу.​Чтобы ответить на вопрос​ вариант ISBLANK(), -​
​ 0, хотя в​ только должники из​ И. О.) пустая,​ Есть пять тысяч​
​ № 5 ()​Я ж говорю,​: RAN, я не​ этого конкретного файла​
​ «Текстовый»​ — установлен текстовый​ ЕСЛИ() наличие всех​ «0» на «пусто»​ форматы) Тип: 0;-0;;@​
​ положительные числа, смотрите​ На закладке «Формулы»​ «Есть ли хотя​ проверять есть ли​ ячейке ничего не​ таблицы № 1.​
​ то ячейка столбца​ записей и если​ Выделяете столбец -​ что в VB​ силен в VB…))))​ бОльшая часть ячеек​demoniqus​

​ формат. От значения​​ значений.​
​ (их у меня​слэн​ в статье «Сумма​ в разделе «Библиотека​
​ бы 1 пустая​ в ячейке число,​
​ будет отображаться.​ То есть те,​ О тоже пустая.​ попытаться протянуть или​ F5 — Выделить​
​ не шарю))) А​​Если я правильно​ воспринимается именно так,​

​: С первой частью​​ тоже зависимости нет​shurikus​ много, все с​
​: нет. формат ячейки​ отрицательных чисел в​ функций» выбираем «Другие​ ячейка в B6:B11?»​ текстовое значение, формула​Используя Условное форматирование, также​

excelworld.ru

Формула: если ячейкая пустая, то подставить туда.. (Формулы/Formulas)

​ у кого​​2 условие: Если​
​ скопировать любую из​ — Пустые -​ здесь банальное преобразование​
​ представляю, то все​ как их видит​ понятно…​

​ — если попытаться​​: Согласно правилам, нужно​ разными форматами, проверками​
​ влияет только на​

​ Excel» здесь.​​ функции». Затем выбираем​ используйте формулу массива​ или нет. Если​ можно добиться практически​в таблице №​ в ячейке столбца​
​ предложенных формул дальше-вниз​

​ пишете слово «Текст»​​ типа и его​
​ работает так:​
​ реальный пользователь -​
​А вот со​ прописать любое новое​
​ прикладывать файл пример.​ и пр.).​
​ отображение значения, а​Иногда, для автоматического​ функции «Статистические» и​=ЕСЛИ(СУММ(--ЕПУСТО(B6:B11));ИСТИНА)​
​ в ячейке​
​ такого же результата.​


​ 1 в графе​​ N (Сумма долга)​ то появляется проблема.​ — жмете Контрл+Ентер​ представления​Dim a& -​

​ совершенно пустые. Другие​​ второй — как​
​ значение (включая, если​​Без файла можно​Это бессилие и​ формулы работают с​ составления отчета, нужно​ нажимаем на функцию​Чтобы ответить на вопрос​А1​выделите интересующий диапазон;​
​ N (Сумма долга)​

​ число больше 0​​ Пример тот же​Спасибо, помогло.​AdwordsDirect​ выделяет переменную a​ файлы, которые я​

excelworld.ru

Если в ячейки пусто то брать значение из ячейки выше.

​ окно. Заполняем его.​​ бы 1 заполненная​

​ то формула =ЕПУСТО(А1)​​ Стили/ Условное форматирование/​

​ Равно);​​ текст: Долг​3 условие: Если​ эти формулы бесполезно?​
​ хотите получать правильные​
​ задачу.​

​ присвоение null-значения. Переменная​​С нулевыми ячейками​

​ читайте в статье​​ расчета. В строке​
​=НЕ(ЕПУСТО(B6:B11))​А1​

​в левом поле введите​​Полосатый жираф алик​ в ячейке столбца​

planetaexcel.ru

Как написать формулу Excel Эксель с условиями: если в ячейке число больше нуля то текст Долг, если ноль то текст Оплачено

​ message и поэтому​​ о том, что​ текст «0». А​ сохраняться. Может кто-нибудь​Pelena​

​Зато возникло сомнение,​

Скрытие значений в MS EXCEL равных 0

Иногда требуется скрыть значения равные 0. Сделаем это несколькими способами.

Скрыть значения равные 0 во всех ячейках на листе можно через Параметры MS EXCEL (далее нажмите Дополнительно/ Раздел Показать параметры для следующего листа/ , затем снять галочку Показывать нули в ячейках, которые содержат нулевые значения).

Скрыть значения равные 0 в отдельных ячейках можно используя пользовательский формат, Условное форматирование или значение Пустой текст («»).

Пользовательский формат

Пользовательский формат вводим через диалоговое окно Формат ячеек.

• для вызова окна Формат ячеек нажмите CTRL+1;
• выберите (все форматы).
• в поле Тип введите формат # ##0,00;-# ##0,00;

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

Условное форматирование

Используя Условное форматирование, также можно добиться практически такого же результата.

• выделите интересующий диапазон;
• в меню выберите Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Равно );
• в левом поле введите 0;
• в правом поле выберите пользовательский формат (белый цвет шрифта);

Теперь нулевые значения отображаются белым шрифтом и не видны на стандартном белом фоне.

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

Значение Пустой текст («»)

Замечательным свойством значения Пустой текст является, то что оно не отображается в ячейке (например, введите формулу =»» ). Записав, например, в ячейке B1 формулу =ЕСЛИ(A1;A1;»») получим пустую ячейку В1 в случае, если в А1 находится значение =0. Столбец А можно скрыть.

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

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

Как в ячейке с формулой вместо ошибки показать 0

Случаются ситуации, когда в рабочей книге на листах создано много формул, выполняющих различные задачи. При этом формулы созданы когда-то давно, возможно даже на вами. И формулы возвращают ошибки. Например #ДЕЛ/0! (#DIV/0!) . Эта ошибка возникает, если внутри формулы происходит деление на ноль: = A1 / B1 , где в B1 ноль или пусто. Но могут быть и другие ошибки(#Н/Д, #ЗНАЧ! и т.д.). Можно изменить формулу, добавив проверку на ошибку:
=ЕСЛИ(ЕОШ( A1 / B1 );0; A1 / B1 )
=IF(ISERR( A1 / B1 ),0, A1 / B1 )
аргументы:
=ЕСЛИ(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)
Эти формулы будут работать в любой версии Excel. Правда, функция ЕОШ не обработает ошибку #Н/Д (#N/A) . Чтобы так же обработать и #Н/Д необходимо использовать функцию ЕОШИБКА:
=ЕСЛИ(ЕОШИБКА( A1 / B1 );0; A1 / B1 )
=IF(ISERROR( A1 / B1 ),0, A1 / B1 )
Однако далее по тексту я буду применять ЕОШ(т.к. она короче) и к тому же не всегда надо «не видеть» ошибки #Н/Д.
Но для версий Excel 2007 и выше можно применить чуть более оптимизированную функцию ЕСЛИОШИБКА (IFERROR) :
=ЕСЛИОШИБКА( A1 / B1 ;0)
=IFERROR( A1 / B1 ,0)
аргументы:
=ЕСЛИОШИБКА(1 аргумент; 2 аргумент)

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

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной? Разберем первую формулу подробнее:
=ЕСЛИ(ЕОШ( A1 / B1 );0; A1 / B1 )
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1 / B1 (т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА (TRUE) , которое будет передано в ЕСЛИ (IF) . И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ (ISERR) возвращает ЛОЖЬ (FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1 / B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же ЕСЛИОШИБКА (IFERROR) один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно ЕСЛИОШИБКА (IFERROR) .

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

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.
Для всех версий Excel:

Sub IfIsErrNull() Const sToReturnVal As String = «0» ‘если необходимо вместо нуля возвращать пусто ‘Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IF(ISERR(» & s & «),» & sToReturnVal & «,» & s & «)» If Left(s, 9) <> «IF(ISERR(» Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

Для версий 2007 и выше

Sub IfErrorNull() Const sToReturnVal As String = «0» ‘если необходимо вместо нуля возвращать пусто ‘Const sToReturnVal As String = «»»»»» Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox «Выделенный диапазон не содержит данных», vbInformation, «www.excel-vba.ru» Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = «=» & «IFERROR(» & s & «,» & sToReturnVal & «)» If Left(s, <> «IFERROR(» Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox «Невозможно преобразовать формулу в ячейке: » & ss & vbNewLine & _ Err.Description, vbInformation, «www.excel-vba.ru» Else MsgBox «Формулы обработаны», vbInformation, «www.excel-vba.ru» End If End Sub

Как это работает
Если не знакомы с макросами, то для начала лучше прочитать как их создавать и вызывать: Что такое макрос и где его искать?, т.к. может случиться так, что все сделаете правильно, но забудете макросы разрешить и ничего не заработает.

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert —Module) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = «0»

Удалить, а перед строкой

‘Const sToReturnVal As String = «»»»»»

Удалить апостроф ( ‘ )

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

И небольшое дополнение: старайтесь применять код вдумчиво. Не всегда возврат ошибки мешает. Например, при использовании ВПР иногда полезно видеть какие значения не были найдены.
Так же хочу отметить, что применять надо к реально работающим формулам. Потому как если формула возвращает #ИМЯ!(#NAME!), то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления. Такие формулы лучше проанализировать и найти ошибку, чтобы избежать логических ошибок расчетов на листе.

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

Поиск по меткам

Поделитесь своим мнением

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

Блог Евгения Крыжановского

Я научу вас ремонтировать свой компьютер!

Excel: «Если» (формула). В Excel функция «Если»

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

Ключевые возможности

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

Примеры применения

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

Равенство параметров двух ячеек

Чтобы лучше понять возможности функции «Если» в Excel, необходимо привести примеры. Поэтому дальше стоит приступить к их рассмотрению. Нужно ввести в клетку C1 значение 8. После этого в поле по адресу D1 необходимо вписать формулу вида: =ЕСЛИ(C1 , =, >, =, 3); «проходит», «нет»).

Если рассмотреть более сложный пример, понадобится использовать «ИЛИ» либо «И». Таким образом, можно ознакомиться с применением формулы в Excel, если существует несколько условий в задании. Например: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). В данном случае можно сделать вывод, когда значение в клетке А1 равно 5 или 10, программа отображает результат 100. В противном случае он составляет 0. Существует возможность применить эти операторы, чтобы найти решение более сложным задачам.

Например, в базе требуется вычислить должников, которым требуется заплатить более 10000 рублей. Можно задать условие, что заем не погашался более полугода, то есть шести месяцев. Благодаря функции табличного редактора Excel «Если» позволяется в автоматическом режиме получить около соответствующих имен отметку «проблемный клиент». Допустим, в клетке A1 размещены данные, которые указывают на срок задолженности (месяцы). Поле B1 содержит сумму.

В данном случае формула представлена в следующем виде: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный клиент»; «»). Таким образом, если выявлен человек, соответствующий указанным условиям, программа отображает напротив его имени соответствующий комментарий. Для других участников списка эта клетка останется пустой.

Также можно ознакомиться с примером, когда ситуация является критической. Необходимо ввести соответствующий комментарий. Как результат, формула получится следующей: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). Однако если программа определяет совпадения по одному из параметров как минимум, отображается соответствующее примечание.

Сложные задачи

Функция Excel «Если» применяется для того, чтобы избежать встроенных ошибок при делении на ноль. Кроме того, она используется и в некоторых других ситуациях. Первый случай обозначается, как «ДЕЛ/0». Его можно встретить достаточно часто. Обычно это происходит тогда, когда формула «A/B» требует копирования. В это же время показатель B в отдельных ячейках составляет 0. Чтобы избежать подобной ситуации, стоит использовать возможности рассматриваемого оператора. Таким образом, требуемая формула имеет следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Итак, если клетка B1 заполнена значением «ноль», редактор отобразит «0». В противном случае программа поделит показатель A1 на данные B1 и выдаст требуемый результат.

Скидка

Как показывает практика, зачастую возникают ситуации, которые рассматриваются далее. Потребуется рассчитать скидки, учитывая общую сумму средств, затраченных на покупку конкретного товара. Матрица, которая используется, выглядит следующим образом: менее 1000 — 0%; 1001-3000 — 3%; 3001-5000 — 5%; более 5001 — 7%. Можно ознакомиться с ситуацией, когда в Excel имеется база данных посетителей и сведения о сумме, потраченной на покупку. Далее следует рассчитать скидку для каждого клиента. Для этого понадобится применить следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..).

Система выполняет проверку общей суммы покупок. Если она превышает значение в 5001 рублей, выполняется умножение на 93 процента цены товара. Если превышена отметка в 3001 единиц, происходит аналогичное действие, однако уже учитывается 95%.

Запись опубликована 17.09.2015 автором katrinas11 в рубрике Программы. Отблагодари меня, поделись ссылкой с друзьями в социальных сетях:

Как не показывать 0 в Эксель? В версиях 2007 и 2010 жмите на CTRL+1, в списке «Категория» выберите «Пользовательский», а в графе «Тип» — 0;-0;;@. Для более новых версий Excel жмите CTRL+1, а далее «Число» и «Все форматы». Здесь в разделе «Тип» введите 0;-0;;@ и жмите на «Ок». Ниже приведем основные способы, как не отображать нули в Excel в ячейках для разных версий программы — 2007, 2010 и более новых версий.

Как скрыть нули

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

Версия 2007 и 2010

При наличии под рукой версии 2007 или 2010 можно внести изменения следующими методами.

Числовой формат

Наиболее простой способ, как не показывать нулевые значения в Excel — скрыть данные с помощью форматирования. Сделайте следующие шаги:

• Выделите ячейки с цифрами «0», которые нужно не показывать.
• Кликните на CTRL+1 или зайдите в раздел «Главная», а далее «Ячейки» и «Формат».

• В разделе «Категория» формата ячеек кликните на «Пользовательский»/«Все форматы».
• В секции «Тип» укажите 0;-0;;@.

Скрытые параметры показываются только в fx или в секции, если вы редактируете данные, и не набираются.

Условное форматирование

Следующий метод, как в Экселе не показывать нулевые значения — воспользоваться опцией условного форматирования. Сделайте следующие шаги:

• Выделите секцию, в которой имеется «0».
• Перейдите в раздел «Главная», а далее «Стили».
• Жмите на стрелку возле кнопки «Условное форматирование».

• Кликните «Правила выделения …».
• Выберите «Равно».

• Слева в поле введите «0».
• Справа укажите «Пользовательский формат».
• В окне «Формат …» войдите в раздел «Шрифт».
• В поле «Цвет» выберите белый.

Указание в виде пробелов / тире

Один из способов, как в Excel не показывать 0 в ячейке — заменить эту цифру на пробелы или тире. Для решения задачи воспользуйтесь опцией «ЕСЛИ». К примеру, если в А2 и А3 находится цифра 10, а формула имеет вид =А2-А3, нужно использовать другой вариант:

1. =ЕСЛИ(A2-A3=0;»»;A2-A3). При таком варианте устанавливается пустая строка, если параметр равен «0».
2. =ЕСЛИ(A2-A3=0;»-«;A2-A3). Ставит дефис при 0-ом показателе.

Сокрытие данных в нулевом отчете Excel

Как вариант, можно не отображать нулевые значения в Excel в отчете сводной таблицы. Сделайте следующие шаги:

• Войдите в «Параметры», а в разделе «Параметры сводной таблицы» жмите на стрелку возле пункта с таким же названием и выделите нужный раздел.

• Кликните на пункт «Разметка и формат».
• В секции изменения способа отображения ошибок в поле «Формат» поставьте «Для ошибок отображать», а после введите в поле значения. Чтобы показывать ошибки в виде пустых ячеек удалите текст из поля.
• Еще один вариант — поставьте флажок «Для пустых ячеек отображать» и в пустом поле введите интересующий параметр. Если нужно, чтобы поле оставалось пустым, удалите весь текст.

Для более новых версий

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

Как не показывать 0 в выделенных ячейках

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

Сделайте следующее:

• Выделите место таблицы, в котором имеется «0» в Excel.
• Жмите CTRL+1.
• Кликните «Число» и «Все форматы».
• В поле «Тип» укажите 0;-0;;@.
• Кликните на кнопку «ОК».

Скрытие параметров, которые возращены формулой

Следующий способ, как не отображать 0 в Excel — сделать следующие шаги:

• Войдите в раздел «Главная».
• Жмите на стрелку возле «Условное форматирование».
• Выберите «Правила выделения ячеек» больше «Равно».

• Слева введите «0».
• Справа укажите «Пользовательский формат».
• В разделе «Формат ячейки» введите «Шрифт».
• В категории «Цвет» введите белый и жмите «ОК».

Отражение в виде пробелов / тире

Как и в более старых версиях, в Экселе можно не показывать ноль, а ставить вместо него пробелы / тире. В таком случае используйте формулу =ЕСЛИ(A2-A3=0;»»;A2-A3). В этом случае, если результат равен нулю, в таблице ничего не показывается. В иных ситуациях отображается А2-А3. Если же нужно подставить какой-то другой знак, нужно между кавычками вставить интересующий знак.

Скрытие 0-х параметров в отчете

Как вариант, можно не показывать 0 в Excel в отчете таблицы. Для этого в разделе «Анализ» в группе «Сводная таблица» жмите «Параметры» дважды, а потом войдите в «Разметка и формат». В пункте «Разметка и формат» сделайте следующие шаги:

• В блоке «Изменение отображения пустой ячейки» поставьте отметку «Для пустых ячеек отображать». Далее введите в поле значение, которое нужно показывать в таблице Excel или удалите текст, чтобы они были пустыми.
• Для секции «Изменение отображения ошибки» в разделе «Формат» поставьте отметку «Для ошибок отображать» и укажите значение, которое нужно показывать в Excel вместо ошибок.

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

Иногда возникает обратная ситуация, когда нужно показать 0 в Excel.

Для Эксель 2007 и 2010

Для Excel 2007 и 2010 сделайте следующие шаги:

• Выберите «Файл» и «Параметры».

• Сделайте «Дополнительно».
• В группе «Показать параметры для следующего листа». Чтобы показать «0», нужно установить пункт «Показывать нули в ячейках, которые содержат 0-ые значения».

Еще один вариант:

• Жмите CTRL+1.
• Кликните на раздел «Категория».
• Выберите «Общий».
• Для отображения даты / времени выберите нужный вариант форматирования.

Для более новых версий

Для Эксель более новых версий, чтобы показывать 0, сделайте следующее:

1. Выделите секции таблицы со спрятанными нулями.
2. Жмите на CTRL+1.
3. Выберите «Число», а далее «Общий» и «ОК».

Это основные способы, как не показывать 0 в 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

Помогла ли Вам статья?