Як позначити порожню комірку у формулі excel

Сьогодні йтиметься про роботу з формулами. Однією з базових речей під час роботи з Excel є визначення необхідних значень, із якими проводитимуться операції. Пропоную на простих прикладах ознайомитись з тим, як можна зафіксувати ці значення.

У мене є таблиця з даними:

Робота із числами.

Припустимо, нам необхідно помножити всі значення в стовпці "Ціна" на 2. При такому завданні досить просто написати формулу і розтягнути її значення на всі осередки таблиці.

1. Насамперед, виділяємо будь-яку комірку навпроти першого значення і вводимо формулу "=B2*2":

Головне, щоб комірка з формулою знаходилася на одному рядку зі значенням, яке воно братиме з комірки. У нашому випадку, осередок E2 знаходиться на одному рядку з осередком B2 .

2. Щоб не писати в кожному осередку подібну формулу, розробники вигадали функцію копіювання. Ми їй і скористаємось.

Копіювати значення осередку E2 на всі інші осередки в цьому стовпці можна натиснувши на правий нижній кут цього осередку і перемістивши покажчик миші до останнього рядка:

3. У результаті вийшло таке:

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

Висновок: якщо у формулі є цифра, то вона постійна (не плутати з позначенням осередку), так як вона є значенням.

Робота зі значеннями осередків.

Давайте замінимо цифру 2 з попереднього прикладу значення будь-якої комірки. Я вирішив ускладнити завдання і помножити його не на значення навпаки, а на значення:

У такому випадку нумерація в стовпці B триватиме після цифри 2, а в стовпці E після цифри 4:

Кожен стовпець використовує свою нумерацію, незалежно від інших.

Тут ми наближаємося до теми нашої статті.

Як зафіксувати значення одного осередку у формулі?

Розробники передбачили функцію, яка почне працювати тоді, коли Ви напишіть у формулі знак долара "$". Але тут є невеликі нюанси:

• Якщо написати "$C4", то незмінним залишиться тільки стовпець, у нашому випадку це "C", а значення після 4 також змінюватимуться по порядку.
• Якщо написати "C$4", то незмінним залишиться рядок "4", а стовпці будуть змінюватися при копіюванні.
• Якщо написати "$C$4", то незмінними будуть і рядок і стовпець.

Повернемося до нашої формули та допишемо знак долара перед рядком та стовпцем:

Тепер, при копіюванні, програма залишатиме значення комірки "C4", а значення стовпця "B" змінюватимуться згідно з порядковим номером. Що нам і було потрібне.

Давайте ще виділимо іншу комірку і подивимося її формулу:

Все правильно! Комірка C4 зафіксована, а інша комірка змінила свій номер на номер рядка.

Дякуємо за прочитання цієї статті! Якщо сподобалося – ставте лайки. Ставте запитання в коментарях. Радий допомогти!

Кожна з функцій Еперевіряє вказане значення та повертає залежно від результату значення ІСТИНА або БРЕХНЯ. Наприклад, функція ЕПУСТО повертає логічне значення ІСТИНА, якщо значення, що перевіряється, є посиланням на порожню комірку; в іншому випадку повертається логічне значення брехня.

Функції Е використовуються для отримання відомостей про значення перед виконанням обчислення або іншої дії.Наприклад, для виконання іншої дії при виникненні помилки можна використовувати функцію ПОМИЛКА у поєднанні з функцією ЯКЩО:

= ЯКЩО( ПОМИЛКА(A1); "Відбулася помилка."; A1 * 2)

Синтаксис

Аргумент функції Е наведено нижче.

Обов'язковий аргумент. Перевірене значення. Значенням цього аргументу може бути порожній осередок, значення помилки, логічне значення, текст, число, посилання на будь-який із перерахованих об'єктів або ім'я такого об'єкта.

Повертає значення ІСТИНА, якщо

Аргумент "значення" посилається на порожній осередок

Аргумент "значення" посилається на логічне значення

Аргумент "значення" посилається на будь-який елемент, який не є текстом. (Зверніть увагу, що функція повертає значення ІСТИНА, якщо аргумент посилається на порожню комірку.)

Аргумент "значення" посилається на число

Аргумент "значення" посилається на посилання

Аргумент "значення" посилається на текст

Зауваження

Аргументи у функціях Е не перетворюються. Будь-які числа, укладені в лапки, сприймаються як текст. Наприклад, у більшості інших функцій, що вимагають числового аргументу, текстове значення "19" перетворюється на число 19. Однак у формулі число("19") це значення не перетворюється з тексту на число, і функція число повертає значення брехня.

За допомогою функцій Е зручно перевіряти результати обчислень у формулах. Комбінуючи ці функції з функцією ЯКЩО, можна знаходити помилки у формулах (див. наведені нижче приклади).

Приклади

Приклад 1

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового листа Excel. Щоб відобразити результати формул, перейдіть до них та натисніть клавішу F2, а потім — клавішу ENTER. У разі потреби змініть ширину стовпців, щоб бачити всі дані.

Перевіряє, чи є значення ІСТИНА логічним

Перевіряє, чи є значення "ІСТИНА" логічним

Перевіряє, чи є значення 4 числом

Перевіряє, чи є значення G8 допустимим посиланням

Перевіряє, чи є значення XYZ1 допустимим посиланням

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового листа Excel. Щоб відобразити результати формул, перейдіть до них та натисніть клавішу F2, а потім — клавішу ENTER. У разі потреби змініть ширину стовпців, щоб бачити всі дані.

Перевіряє, чи осередок C2 порожній

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

Потреба відображати нульові значення (0) на аркушах не завжди. Чи вимагають стандарти форматування чи власні переваги відображати чи приховувати нульові значення, є кілька способів реалізації всіх цих вимог.

Приховування та відображення всіх нульових значень на аркуші

Виберіть Файл > Установки > Додатково.

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

Щоб відобразити в осередках нульові значення (0), встановіть прапорець Показувати нулі в осередках, які містять нульові значення.

Щоб відобразити нульові значення у вигляді пустих осередків, зніміть прапорець Показувати нулі в осередках, які містять нульові значення.

Приховування нульових значень у виділених осередках

Ці дії приховують нульові значення у вибраних осередках за допомогою числових форматів. Приховані значення відображаються лише на панелі формул і не друкуються.Якщо значення в одному з цих осередків зміниться на неосвоє число, то значення відобразиться в комірці, а формат значення буде аналогічним загальному числовому формату.

Виділіть комірки, що містять нульові значення (0), які потрібно приховати.

Ви можете натиснути клавіші CTRL+1 або на вкладці Головна клацнути Формат > Формат комірок.

Натисніть Число > Усі формати.

У полі Тип введіть вираз 0;-0;;@ і натисніть кнопку ОК.

Відображення прихованих значень.

Виділіть комірки із прихованими нульовими значеннями.

Ви можете натиснути клавіші CTRL+1 або на вкладці Головна клацнути Формат > Формат комірок.

Щоб використовувати числовий формат, визначений за промовчанням, виберіть Число > Загальний та натисніть кнопку ОК.

Приховування нульових значень, повернутих формулою

Виділіть комірку, що містить нульове (0) значення.

На вкладці Головна клацніть стрілку поруч із кнопкою Умовне форматування та оберіть "Правила виділення осередків" > "Рівне".

У лівому полі введіть 0.

У правому полі виберіть формат користувача.

У полі Формат комірки відкрийте вкладку Шрифт.

У списку Колір виберіть білий колір та натисніть кнопку ОК.

Відображення нулів у вигляді пробілів або тире

Для вирішення цього завдання скористайтеся функцією ЯКЩО.

Якщо осередок містить нульові значення, для повернення порожнього осередку використовуйте формулу, наприклад:

Ось як читати формулу. Якщо результат обчислення (A2-A3) дорівнює "0", нічого не відображається, у тому числі і "0" (це вказується подвійними лапками ""). Інакше відображається результат обчислення A2-A3. Якщо вам потрібно не залишати комірки порожніми, але відображати не "0", а щось інше, між подвійними лапками вставте дефіс "-" або інший символ.

Приховування нульових значень у звіті зведеної таблиці

Виберіть звіт таблиці.

На вкладці Аналіз у групі Зведена таблиця клацніть стрілку поруч із командою Параметри та виберіть пункт Параметри.

Перейдіть на вкладку Розмітка та формат, а потім виконайте такі дії.

Змінення відображення помилки У полі Формат встановіть прапорець Для помилок відображати введіть у поле значення, яке потрібно виводити замість помилок.

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

Потреба відображати нульові значення (0) на аркушах виникає не завжди.

Відображення та приховування всіх нульових значень на аркуші

Виберіть Файл > Установки > Додатково.

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

Щоб відобразити в осередках нульові значення (0), встановіть прапорець Показувати нулі в осередках, які містять нульові значення.

Щоб відобразити нульові значення у вигляді пустих осередків, зніміть прапорець Показувати нулі в осередках, які містять нульові значення.

Приховування нульових значень у виділених осередках за допомогою числового формату

Ці дії дозволяють приховати нульові значення у виділених осередках.

Виділіть комірки, що містять нульові значення (0), які потрібно приховати.

Ви можете натиснути клавіші CTRL+1 або на вкладці Головна клацнути Формат > Формат комірок.

У списку Категорія виберіть пункт Користувач.

У полі Тип введіть 0;-0;;@

Приховані значення відображаються лише у рядку формул або комірці, якщо ви редагуєте її вміст. Ці значення не друкуються.

Щоб знову відобразити приховані значення, виділіть комірки, а потім натисніть клавіші CTRL+1 або на вкладці Головна в групі Комірки наведіть вказівник миші на елемент Формат і виберіть Формат комірок. Щоб застосувати стандартний цифровий формат, у списку Категорія виберіть Загальний. Щоб знову відобразити дату та час, виберіть відповідний формат дати та часу на вкладці Число.

Приховування нульових значень, повернутих формулою, за допомогою умовного форматування

Виділіть комірку, що містить нульове (0) значення.

На вкладці Головна в групі Стилі клацніть стрілку поруч із елементом Умовне форматування, наведіть вказівник на елемент Правила виділення осередків та виберіть Опції.

У лівому полі введіть 0.

У правому полі виберіть формат користувача.

У діалоговому вікні Формат комірок відкрийте вкладку Шрифт.

У полі Колір виберіть білий.

Використання формули для відображення нулів у вигляді пробілів або тире

Для виконання цього завдання використовуйте функцію ЯКЩО.

Щоб цей приклад було простіше зрозуміти, скопіюйте його на порожній лист.

Друге число віднімається з першого (0).

Повертає порожню комірку, якщо значення дорівнює нулю

Повертає дефіс (-), якщо значення дорівнює нулю

Щоб отримати додаткові відомості про використання цієї функції, див. функцію ЯКЩО.

Приховування нульових значень у звіті зведеної таблиці

Натисніть на звіт зведеної таблиці.

На вкладці Параметри у групі Параметри зведеної таблиці клацніть стрілку поруч із командою Параметри та виберіть пункт Параметри.

Перейдіть на вкладку Розмітка та формат, а потім виконайте такі дії.

Зміна способу відображення помилок. У полі Формат встановіть прапорець Для помилок. Введіть у поле значення, яке потрібно виводити замість помилок. Щоб відобразити помилки у вигляді порожніх осередків, видаліть з поля весь текст.

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

Потреба відображати нульові значення (0) на аркушах не завжди. Чи вимагають стандарти форматування чи власні переваги відображати чи приховувати нульові значення, є кілька способів реалізації всіх цих вимог.

Відображення та приховування всіх нульових значень на аркуші

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

Щоб відобразити в осередках нульові значення (0), встановіть прапорець Показувати нулі в осередках, які містять нульові значення.

Щоб відобразити нульові значення у вигляді пустих осередків, зніміть прапорець Показувати нулі в осередках, які містять нульові значення.

Приховування нульових значень у виділених осередках за допомогою числового формату

Ці дії дозволяють приховати нульові значення у виділених осередках. Якщо значення в одній із осередків стане ненульовим, його формат буде аналогічним загальному числовому формату.

Виділіть комірки, що містять нульові значення (0), які потрібно приховати.

Ви можете натиснути клавіші CTRL+1 або на вкладці Головна в групі Осередки клацнути Формат > Формат комірок.

У списку Категорія виберіть пункт Користувач.

У полі Тип введіть 0;-0;;@

Приховані значення відображаються лише в або в комірці, якщо ви редагуєте комірку, і не друкуються.

Щоб знову відобразити приховані значення, виділіть комірки, а потім на вкладці Головна в групі Комірки наведіть вказівник миші на елемент Формат і виберіть Формат комірок. Щоб застосувати стандартний цифровий формат, у списку Категорія виберіть Загальний. Щоб знову відобразити дату та час, виберіть відповідний формат дати та часу на вкладці Число.

Приховування нульових значень, повернутих формулою, за допомогою умовного форматування

Виділіть комірку, що містить нульове (0) значення.

На вкладці Головна в групі Стилі клацніть стрілку поруч із кнопкою Умовне форматування та виберіть "Правила виділення осередків" > "Рівне".

У лівому полі введіть 0.

У правому полі виберіть формат користувача.

У діалоговому вікні Формат комірок відкрийте вкладку Шрифт.

У полі Колір виберіть білий.

Використання формули для відображення нулів у вигляді пробілів або тире

Для виконання цього завдання використовуйте функцію ЯКЩО.

Щоб цей приклад було простіше зрозуміти, скопіюйте його на порожній лист.

Виділіть приклад, наведений у цій статті.

Важливо: Не виділяйте заголовки рядків чи стовпців.

Виділення прикладу у довідці

Натисніть клавіші CTRL+C.

В Excel створіть порожню книгу чи аркуш.

Виділіть на аркуші комірку A1 і натисніть клавіші CTRL+V.

Важливо: Щоб приклад правильно працював, його потрібно вставити в комірку A1.

Щоб переключитися між переглядом результатів та переглядом формул, які повертають ці результати, натисніть клавіші CTRL+` (знак наголосу) або на вкладці Формули у групі "Залежності формул" натисніть кнопку Показати формули.

Скопіювавши приклад на порожній аркуш, ви можете налаштувати його так, як вам потрібно.

Маємо діапазон осередків із даними, в якому є порожні осередки:

Завдання - видалити порожні комірки, залишивши лише комірки з інформацією.

Спосіб 1. Грубо та швидко

1. Виділяємо вихідний діапазон
2. Тиснемо клавішу F5, далі кнопка Виділити (Special) . У вікні вибираємо Порожні осередки (Blanks) і тиснемо ОК.

Спосіб 2. Формула масиву

Для спрощення дамо нашим робочим діапазонам імена, використовуючи Менеджер Імен (Name Manager) на вкладці Формули (Formulas) або - в Excel 2003 і старше - меню Вставка - Ім'я - Присвоїти (Insert - Name - Define)

Діапазону B3:B10 даємо ім'я ЄПорожні, діапазону D3:D10 - НіПорожніх. Діапазони повинні бути строго одного розміру, а розташовані можуть бути будь-де відносно один одного.

Тепер виділимо перший осередок другого діапазону (D3) і введемо в неї таку страшну формулу:

В англійській версії це буде:
=IF(ROW()-ROW(Ні Порожніх)+1>ROWS(Є Порожні)-COUNTBLANK(Є Порожні),"",INDIRECT(ADDRESS(SMALL((IF(Єст))) ьПорожні<>"",ROW(Є Порожні),ROW()+ROWS(Є Порожні))),ROW()-ROW(Ні Порожніх)+1),COLUMN(Є Порожні),4)))

Причому ввести її як формулу масиву, тобто. після вставки натиснути не Enter (як завжди), а Ctrl+Shift+Enter. Тепер формулу можна скопіювати вниз, використовуючи автозаповнення (потягти за чорний хрестик у правому нижньому кутку комірки) - і ми отримаємо вихідний діапазон, але без порожніх осередків:

Спосіб 3. функція користувача на VBA

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

Для цього відкрийте редактор Visual Basic (ALT+F11), вставте новий порожній модуль (меню Insert - Module) і скопіюйте туди текст цієї функції:

Не забудьте зберегти файл і поверніться з редактора Visual Basic до Excel. Щоб використовувати цю функцію в нашому прикладі:

Функція ISBLANK в Excel для перевірки порожнього чи комірки

Існує безліч ситуацій, коли необхідно перевірити, чи пустий осередок чи ні. Наприклад, якщо осередок порожній, то ви можете захотіти підсумовувати, підрахувати, скопіювати значення з іншого осередку або нічого не робити. У цих сценаріях ISBLANK є правильною функцією, яку можна використовувати, іноді окремо, але найчастіше у поєднанні з іншими функціями Excel.

Функція Excel ISBLANK

Функція ISBLANK в Excel перевіряє, чи осередок порожній чи ні. Як і інші функції IS, вона завжди повертає в якості результату булеве значення: TRUE, якщо комірка порожня, і FALSE, якщо комірка не порожня.

Синтаксис ISBLANK передбачає лише один аргумент:

Де значення це посилання на комірку, яку ви хочете перевірити.

Наприклад, щоб дізнатися, чи є комірка A2 порожній , використовуйте цю формулу:

Щоб перевірити, чи є A2 не порожній Використовуйте ISBLANK разом із функцією NOT, яка повертає зворотне логічне значення, тобто. TRUE для незаповнених рядків та FALSE для порожніх.

Скопіюйте формули ще в кілька осередків і отримайте такий результат:

ISBLANK в Excel - що потрібно пам'ятати

Головне, що слід пам'ятати, це те, що функція Excel ISBLANK ідентифікує справді порожні клітини тобто. осередки, які не містять абсолютно нічого: ні прогалин, ні табуляції, ні повернення каретки, нічого, що тільки відображається порожнім у виставі.

Для осередку, який виглядає порожнім, але насправді таким не є, формула ISBLANK повертає FALSE. Така поведінка відбувається, якщо осередок містить одне з наступних значень:

• Формула, що повертає порожній рядок, наприклад, IF(A1"", A1, "").
• Рядок нульової довжини, імпортований із зовнішньої бази даних або отриманий в результаті операції копіювання/вставки.
• Пробіли, апострофи, пробіли ( ), переклад рядка або інші недруковані символи.

Як використовувати ISBLANK в Excel

Щоб краще зрозуміти, на що здатна функція ISBLANK, розглянемо кілька практичних прикладів.

Формула Excel: якщо комірка порожня, то

Оскільки в Microsoft Excel немає вбудованої функції типу IFBLANK, вам необхідно використовувати IF та ISBLANK разом, щоб перевірити комірку та виконати дію, якщо комірка порожня.

IF(ISBLANK( осередок ), " якщо порожньо ", " якщо не порожньо ")

Щоб побачити його в дії, давайте перевіримо, чи має осередок у стовпці B (дата поставки) будь-яке значення. Якщо осередок порожній, виведіть "Відкрито"; якщо осередок не порожній, виведіть "Завершено".

=IF(ISBLANK(B2), "Відкрито", "Завершено")

Будь ласка, пам'ятайте, що функція ISBLANK визначає лише абсолютно порожні осередки Якщо комірка містить щось невидиме для людського ока, наприклад, рядок нульової довжини, ISBLANK поверне FALSE. Щоб проілюструвати це, подивіться на скріншот нижче. Дати в стовпці B взяті з іншого листа за допомогою цієї формули:

В результаті B4 та B6 містять порожні рядки ("").Для цих осередків наша формула IF ISBLANK видає "Completed", оскільки з погляду ISBLANK осередки є порожніми.

Якщо ваша класифікація "порожніх" осередків включає осередки, що містять формулу, яка призводить до порожній рядок , потім використовуйте для логічного тесту:

=IF(B2="", "Відкрито", "Завершено")

На скріншоті нижче показана різниця:

Формула Excel: якщо комірка не порожня, то

Якщо ви уважно стежили за попереднім прикладом і зрозуміли логіку формули, у вас не повинно виникнути труднощів з її модифікацією для конкретного випадку, коли дія повинна виконуватися лише тоді, коли осередок не порожній.

Виходячи з визначення "заготовок", виберіть один з наступних підходів.

Щоб визначити лише справді незаповнений осередки, зверніть логічне значення, що повертається ISBLANK, обернувши його в NOT:

IF(NOT(ISBLANK( осередок )), " якщо не порожньо ", "")

Або використовуйте вже знайому формулу IF ISBLANK (зверніть увагу, що порівняно з попередньою формулою значення_якщо_істина і значення_якщо_хибно значення міняються місцями):

IF(ISBLANK( осередок ), "", якщо не порожньо ")

Соска рядки нульової довжини як прогалини, використовуйте "" для логічного тесту IF:

Для нашої таблиці-зразка підійде будь-яка з наведених нижче формул. Всі вони повернуть значення "Завершено" у стовпці C, якщо осередок у стовпці B не порожній:

Якщо осередок порожній, то залишити порожнім

У певних сценаріях вам може знадобитися формула такого типу: якщо осередок порожній, нічого не робити, інакше вжити будь-яких дій. По суті, це не що інше, як варіація загальної формули IF ISBLANK, розглянутої вище, в якій ви вводите порожній рядок ("") для комірки значення_якщо_істина аргумент та бажане значення/формула/вираз для значення_якщо_хибно .

Для абсолютно порожніх осередків:

IF(ISBLANK( осередок ), "", якщо не порожньо ")

Розглядати порожні рядки як порожні:

У таблиці нижче припустимо, що ви хочете зробити таке:

• Якщо стовпець B порожній, залиште стовпець C порожнім.
• Якщо у стовпці B вказано кількість продажів, розрахуйте комісійні у розмірі 10%.

Для цього помножимо суму B2 на відсоток і підставимо вираз у третій аргумент IF:

Після копіювання формули через стовпець C результат виглядає так:

Якщо якась комірка в діапазоні порожня, то зробіть щось

У Microsoft Excel існує кілька способів перевірки діапазону на наявність порожніх осередків. Ми будемо використовувати оператор IF для виведення одного значення, якщо в діапазоні є хоча б один порожній осередок, та іншого значення, якщо порожніх осередків немає взагалі. У логічному тесті ми підраховуємо загальну кількість порожніх осередків у діапазоні, а потім перевіряємо, чи більше це кількість, ніж нуль. Це можна зробити за допомогою оператора IF або Функція COUNTBLANK або COUNTIF:

Або трохи складніша формула SUMPRODUCT:

SUMPRODUCT(--( асортимент =""))>0

Наприклад, щоб надати статус "Відкритий" будь-якому проекту, що має один або кілька пробілів у стовпцях B-D, можна використовувати будь-яку з наведених нижче формул:

Примітка. Усі ці формули розглядають порожні рядки як прогалини.

Якщо всі комірки в діапазоні порожні, то зробіть щось

Щоб перевірити, чи всі комірки в діапазоні порожні, ми будемо використовувати той самий підхід, що і на прикладі вище. Різниця полягає у логічному тесті IF. На цей раз ми підраховуємо комірки, які не порожні. Якщо результат більший за нуль (тобто логічний тест оцінюється як TRUE), ми знаємо, що не всі комірки в діапазоні порожні.Якщо логічний тест FALSE, це означає, що всі осередки в діапазоні порожні.

У цьому прикладі ми повернемо "Not Started" для проектів, які мають всі етапи в колонках B-D порожні.

Найпростіший спосіб підрахунку непустих осередків в Excel - це використання функції COUNTA:

=IF(COUNTA(B2:D2)>0, "", "Не розпочато")

Інший спосіб - COUNTIF для непустих рядків ("" як критерій):

=IF(COUNTIF(B2:D2,"")>0, "", "Не розпочато")

Або функція SUMPRODUCT з тією ж логікою:

=IF(SUMPRODUCT(--(B2:D2""))>0, "", "Не розпочато")

ISBLANK також можна використовувати, але тільки як формулу масиву, яка має бути завершена натисканням Ctrl+Shift+Enter, та у поєднанні з функцією AND. AND необхідна для того, щоб логічний тест оцінювався як TRUE тільки тоді, коли результат ISBLANK для кожного осередку дорівнює TRUE.

=IF(AND(ISBLANK(B2:D2)), "Not Started", "")

Примітка. При виборі формули для робочого листа важливо враховувати ваше розуміння порожніх осередків. Формули на основі ISBLANK, COUNTA та COUNTIF з "" як критерій шукають абсолютно порожні комірки. SUMPRODUCT також розглядає порожні рядки як порожні.

Формула Excel: якщо осередок не порожній, то сума

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

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

Якщо не порожньо, то сума

Щоб отримати загальну кількість доставлених предметів, перевірте, чи є у полі Дата постачання у стовпці B не порожньо, а якщо не порожньо, то підсумуйте значення в стовпці C:

Якщо порожньо, то сума

Щоб отримати загальну кількість недопоставлених товарів, підсумуйте, якщо Дата постачання у стовпці B порожній:

Сума, якщо всі осередки в діапазоні не порожні

Для підсумовування осередків або виконання іншого обчислення лише тоді, коли всі осередки в заданому діапазоні не порожні, можна знову використовувати функцію ЯКЩО з відповідним логічним тестом.

Наприклад, COUNTBLANK може вивести загальну кількість пропусків у діапазоні B2:B6. Якщо рахунок дорівнює нулю, ми запускаємо формулу SUM, інакше нічого не робимо:

Такого ж результату можна досягти за допомогою масив Формула IF ISBLANK SUM (не забудьте натиснути Ctrl+Shift+Enter для правильного завершення):

В даному випадку ми використовуємо ISBLANK у поєднанні з функцією OR, тому логічний тест буде TRUE, якщо в діапазоні є хоча б один пустий осередок. Отже, функція SUM переходить до параметра значення_якщо_хибно аргумент.

Формула Excel: підрахунок, якщо комірка не порожня

Як ви, мабуть, знаєте, у Excel є спеціальна функція для підрахунку непустих осередків – функція COUNTA. Майте на увазі, що ця функція підраховує комірки, що містять будь-який тип даних, включаючи логічні значення TRUE та FALSE, помилки, прогалини, порожні рядки тощо.

Наприклад, для підрахунку непустий комірки в діапазоні B2:B6, потрібно використовувати таку формулу:

Такого ж результату можна досягти за допомогою COUNTIF з непустим критерієм (""):

рахувати порожній осередків, використовуйте функцію COUNTBLANK:

Excel ISBLANK не працює

Як уже згадувалося, ISBLANK в Excel повертає TRUE тільки для справді порожні клітини які не містять абсолютно нічого. Для уявні порожніми осередки містять формули, що створюють порожні рядки, прогалини, апострофи, недруковані символи тощо, ISBLANK повертає FALSE.

У ситуації, коли ви хочете розглядати візуально порожні комірки як прогалини, розгляньте такі обхідні шляхи.

Розглядайте рядки нульової довжини як прогалини

Щоб рахувати комірки з рядками нульової довжини порожніми, в логічному тесті IF поставте або порожній рядок (""), або функцію LEN, що дорівнює нулю.

=IF(A2="", "порожній", "не порожній")

=IF(LEN(A2)=0, "порожній", "не порожній")

Видалення або ігнорування зайвих прогалин

Якщо функція ISBLANK не працює через пробіли, найочевидніше рішення - позбутися їх. У наступному посібнику пояснюється, як швидко видалити ведучі, наступні і кілька проміжних пробілів, крім одного символу пробілу між словами: Як видалити зайві прогалини в Excel.

Якщо з якоїсь причини видалення зайвих прогалин не допомагає, ви можете змусити Excel ігнорувати їх.

Розглядати клітини, що містять тільки пробільні символи як порожній, увімкніть LEN(TRIM(cell))=0 в логічний тест IF як додаткову умову:

=IF(OR(A2="", LEN(TRIM(A2))=0), "порожній", "не порожній")

Щоб ігнорувати спеціальний недрукований символ знайти його код і передати його в функцію CHAR.

Наприклад, для ідентифікації клітин, що містять порожні рядки і простори, що не перекриваються ( ) як пробіли, використовуйте таку формулу, де 160 - код символу для нерозривної пробілу:

=IF(OR(A2="", A2=CHAR(160)), "порожній", "не порожній")

Ось як використовувати функцію ISBLANK для визначення порожніх осередків в Excel. Я дякую вам за читання і сподіваюся побачити вас у нашому блозі наступного тижня!

Доступні завантаження

Приклади формули Excel ISBLANK

Michael Brown

Майкл Браун - захоплений технологічний ентузіаст, що прагне спростити складні процеси за допомогою програмних інструментів. Маючи більш ніж десятирічний досвід роботи в технологічній галузі, він відточив свої навички у Microsoft Excel та Outlook, а також у Google Sheets та Docs. Блог Майкла присвячений тому, щоб ділитися своїми знаннями та досвідом з іншими, надаючи прості поради та навчальні посібники для підвищення продуктивності та ефективності. Чи ви є досвідченим професіоналом або новачком, у блозі Майкла ви знайдете цінну інформацію та практичні поради, які допоможуть вам максимально ефективно використовувати ці важливі програмні інструменти.