Как обозначить пустую ячейку в формуле 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 зафиксирована, а другая ячейка поменяла свой номер на номер строки.
Спасибо за прочтение этой статьи! Если понравилось - ставьте лайки. Задавайте вопросы в комментариях. Буду рад помочь!
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки - бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 - не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными:
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) - она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную - это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно "но".
Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь - даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки.
Если ввести в ячейку формулу:
=ДВССЫЛ("C5")
=INDIRECT("C5")
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
Способ 3. Power Query
Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
Задача функции ЕПУСТО() , английский вариант ISBLANK(), — проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) вернет ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.
Синтаксис функции ЕПУСТО()
ЕПУСТО(значение)
Значение — значением может быть все что угодно: текст, число, ссылка, имя, пустая ячейка, значение ошибки, логическое выражение.
Использование функции
В файле примера приведены несколько вариантов проверок:
1. Если в проверяемой ячейке содержится число, текстовое значение, формула, то функция вернет логическое значение ИСТИНА.
2. Если проверяемая ячейка пуста, то функция также вернет логическое значение ЛОЖЬ.
Проверка диапазона ячеек
Функция ЕПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст (""), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек.
Чтобы ответить на вопрос "Есть ли хотя бы 1 пустая ячейка в B6:B11?" используйте формулу массива
Чтобы ответить на вопрос "Есть ли хотя бы 1 заполненная ячейка в B6:B11?" используйте формулу массива
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Иногда требуется проверить, пуста ли ячейка. Обычно это делается, чтобы формула не выводила результат при отсутствии входного значения.
В данном случае мы используем ЕСЛИ вместе с функцией ЕПУСТО:
Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст "Пустая", в противном случае вернуть текст "Не пустая"). Вы также можете легко использовать собственную формулу для состояния "Не пустая". В следующем примере вместо функции ЕПУСТО используются знаки "". "" — фактически означает "ничего".
Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст "Пустая", в противном случае вернуть текст "Не пустая"). Вот пример распространенного способа использования знаков "", при котором формула не вычисляется, если зависимая ячейка пуста:
Если (D3 — Nothing, возвращается Nothing, в противном случае вычисляется формула).
Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.
Примеры использования функции ЕПУСТО в Excel
Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.
Выделим ячейки C3:C18 и запишем следующую формулу:
Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов ("На пересдачу" или "Сдал"). Результат выполнения функции:
Теперь часть данной формулы можно использовать для условного форматирования:
- Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
- Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:
Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек
У выше указанном примере можно изменить формулу используя двойные кавычки ("") в место функции проверки ячеек на пустоту, и она также будет работать:
=ЕСЛИ(ИЛИ( 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
Формула для расчета (формула массива):
Особенности использования функции ЕПУСТО в Excel
Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:
Единственный аргумент является обязательным для заполнения и может принимать ссылку на ячейку или на диапазон ячеек, в которых необходимо определить наличие каких-либо данных. Если функция принимает диапазон ячеек, функция должна быть использована в качестве формулы массива.
Для того, чтобы использовать возможности Excel на всю мощь, нужно правильно организовывать данные. Если таблица содержит пустые ячейки, то могут некорректно работать фильтры, сортировка, формулы и сводные таблицы. "Красивое" оформление важно, но его нужно добиваться без потери функциональности.
Очень часто можно встретить такой пример неправильной организации данных.
Таблица слева не позволит нормально фильтровать и сортировать месяцы и годы, а также не даст создать полнофункциональную сводную таблицу (поля "Год" и "Месяц" в ней будут, по сути, бесполезны). Многие пользователи готовы пойти на такие неудобства из-за того, что не хотят тратить время на исправление таблицы. Но они просто еще не знают, что заполнить все пустые ячейки в такой таблице можно за несколько секунд.
Заполнение пустых ячеек таблицы значениями "Сверху"
1) Выделите проблемные столбцы, начиная первой заполненной строкой и заканчивая последней, которую нужно заполнить;
2) Вызовите окно "Выделить группу ячеек". Это можно сделать двумя способами:
- " Главная " - " Найти и выделить " - " Выделить группу ячеек ";
3) В открывшемся окне "Выделить группу ячеек" выберите " Пустые ячейки ";
4) Не снимая выделение, введите в активную ячейку ссылку на предыдущую верхнюю ячейку (эта ячейка должна быть заполнена);
5) Чтобы ввести формулу во все пустые ячейки, завершите ввод нажатием Ctrl+Enter;
6) Выделите заполненный диапазон, скопируйте и замените на значения (чтобы удалить ненужные больше формулы).
Как видите, привести таблицу в порядок - дело пары минут. Их стоит потратить на такую простую операцию, чтобы в дальнейшем не было проблем с обработкой таблицы.
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Читайте также: