Пусто в Excel, что это значит

Обновлено: 04.07.2024

Некоторые рабочие листы Microsoft Excel содержат ячейки, оставленные пустыми, чтобы облегчить чтение заголовков и подзаголовков. Однако, если вы хотите отсортировать или отфильтровать список, вам необходимо заполнить пробелы, скопировав значение из первой заполненной ячейки над пробелом. Следуйте инструкциям на этой странице, чтобы заполнить пустые ячейки вручную или с помощью макроса Excel.

Примечание. Чтобы заполнить пустые ячейки в заголовках сводной таблицы, узнайте, как повторять метки элементов в Excel 2010 и более поздних версиях.

Заполнить пустые ячейки сверху

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

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

Видео: заполнение пустых ячеек вручную

В этом видео показано, как вручную выбрать и заполнить пустые ячейки значением из ячейки выше. Затем с помощью ярлыка мыши измените формулы на значения, чтобы вы могли безопасно сортировать и фильтровать данные.

Примечание. В конце страницы есть стенограмма видео.

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

Чтобы заполнить пустые ячейки вручную, необходимо выполнить 3 основных шага:

1) Выберите пустые ячейки

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

Чтобы выбрать пустые ячейки с помощью встроенной в Excel функции "Перейти к специальной", выполните следующие действия:

• Выберите столбцы A и B, содержащие пустые ячейки.
• Используйте команды ленты:
  • На вкладке "Главная" ленты Excel в группе "Редактирование" нажмите "Найти и выбрать".
  • Нажмите команду "Перейти к специальному"
  • Нажмите Ctrl + G, чтобы открыть окно "Перейти".
  • Нажмите Alt + S, чтобы открыть диалоговое окно "Перейти к специальному"

  
  

  Перейти к специальному диалоговому окну

  • В диалоговом окне "Перейти к специальному" нажмите "Пробелы"
    • ИЛИ нажмите Alt + K (подчеркнутая буква в поле "Пробелы").

    
    

    Выбраны пустые ячейки

    После закрытия диалогового окна "Перейти к специальному":

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

    
    

    2) Создать простую формулу

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

    В настоящее время выбраны пустые ячейки в столбцах A и B, а ячейка A3 является активной.

    Чтобы построить формулу, выполните следующие действия:

    • Выделив пустые ячейки, введите знак равенства, чтобы начать формулу.
    • На клавиатуре нажмите клавишу со стрелкой вверх
      • Это вводит относительную ссылку на ячейку выше.
      • Ячейка A3 активна, поэтому она ссылается на ячейку A2, ячейку непосредственно над ней.

      На снимке экрана ниже вы можете увидеть формулу в ячейке A3 и в строке формул:

      
      

      3) Изменить формулы на значения

      Последний шаг перед сортировкой или фильтрацией данных — преобразование формул "пустых ячеек" в значения, чтобы зафиксировать их.

      • ВНИМАНИЕ! Если вы пропустите эти шаги и оставите формулы ссылки на ячейку в ячейках, результаты формулы будут отображать неверные данные позже, если вы отсортируете список.
      • используйте команды ленты, чтобы изменить формулы на значения (шаги ниже)
      • ИЛИ используйте быструю комбинацию клавиш мыши (видео ниже)

      Использование команд ленты для вставки значений

      Выполните следующие действия, чтобы изменить формулы "пустой ячейки" на значения

      • Выберите все столбцы, в которых вы заполнили пробелы формулами — в этом примере столбцы A:B
        • Примечание. Не выделяйте столбцы целиком, если в других ячейках этих столбцов содержатся формулы, которые необходимо сохранить. Вы же не хотите случайно изменить эти другие формулы на значения.

        
        

        Видео: вставка ярлыков мыши как значений

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

        Значения во всех выбранных ячейках

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

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

        Заполнение пустых ячеек — макросы

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

        Совет. Дополнительные сведения о поиске последней строки см. на странице Рона де Брюйна: Поиск последней строки, столбца или последней ячейки. Рон объясняет преимущества и недостатки каждого метода.

        — Макрос 3 — Дэйв Петерсон и Рон де Брюин

        Макрос заполнения пустых ячеек — пример 1

        В этом макросе заполнения пустых ячеек от Дэйва Петерсона:

        • использует формулу для заполнения ячеек
        • вставляет результаты как значения.
        • использует метод .SpecialCells(xlCellTypeLastCell) для поиска последней строки
        • заполняет пробелы от строки 2 до последней строки

        Вы можете скопировать приведенный ниже код и вставить его в обычный модуль кода в своем собственном файле Excel. Или загрузите образец файла Excel и скопируйте оттуда код.

        Макрос заполнения пустых ячеек — пример 2

        Макрос заполнения пустых ячеек от Рика Ротштейна

        • использует метод .Find для вычисления последней строки
        • Вместо использования формулы для заполнения сверху каждая ячейка получает свое значение из ячейки над первой ячейкой области, в которой она находится, с помощью свойства "Смещение".

        Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.

        Макрос заполнения пустых ячеек — пример 3

        В этом примере код Дейва Петерсона (пример 1) сочетается с тестом специальных ячеек от Рона де Брюина.

        • В Excel 2007 и более ранних версиях возникает проблема со специальными ячейками, если в диапазоне специальных ячеек более 8192 различных областей.
          • ПРИМЕЧАНИЕ. Эта проблема была устранена в Excel 2010.

          Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.

          Макрос заполнения пустых ячеек — пример 4

          Этот пример макроса принадлежит Алексу Блэкенбургу, который вводил числа в виде текста в свои данные и не хотел, чтобы они преобразовывались в действительные числа. Кроме того, его данные не всегда начинались с первой строки рабочего листа.

          В этом макросе Алекс решил следующие проблемы:

          • макрос начинается с активной ячейки
          • останавливается на последней строке в активном столбце после сброса последней ячейки
          • использует .FillDown вместо .Value = .Value
            • использование .Value = .Value преобразует текстовые числа в действительные числа

            Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.

            Скрыть повторяющиеся заголовки

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

            Это облегчит чтение списка, как это было с пустыми ячейками.

            В этом видео показаны шаги, а письменные инструкции находятся на странице примеров условного форматирования.

            Заполнить пустые ячейки стенограммы видео

            Вот стенограмма видео "Заполнить пустые ячейки" в верхней части этой страницы.

            Введение

            Иногда в Excel вы получаете такие данные, возможно, экспортированные из другой системы, где у вас есть заголовки, но пустые ячейки под этими заголовками.

            Здесь мы видим регион, и он указан только один раз, а затем пуст до конца региона и сотрудников в каждом регионе. У вас может быть один или несколько сотрудников, но опять же, пробел под именами этих сотрудников.

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

            Выбрать пустые ячейки

            Сначала мы выберем столбцы A и B, в которых есть пробелы.

            • Затем на вкладке "Главная" ленты выберите "Найти и выбрать", "Перейти к специальному".
            • В окне "Перейти к специальному" нажмите "Пробелы", а затем нажмите "ОК".

            Добавить формулу

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

            • Введите знак равенства, а затем нажмите стрелку вверх на клавиатуре. Вы можете видеть, что формула теперь изменилась на A2.
            • Чтобы заполнить этой формулой все выделенные ячейки, нажмите клавишу Ctrl, а затем нажмите Enter.

            Это помещает одну и ту же формулу во все выбранные ячейки.

            Изменить значения

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

            • Я выберу столбцы A и B, в которых мы заполнили пробелы, а затем укажу на границу выделенного. Там вы увидите четырехконечную стрелку.
            • Нажмите правую кнопку мыши и слегка перетащите вправо. Затем перетащите обратно туда, где вы начали.
            • Отпустите правую кнопку мыши, и во всплывающем меню появится несколько вариантов. Нажмите «Копировать сюда только как значения».

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

            Получить образец файла

            Чтобы просмотреть отчет с пустыми ячейками и протестировать макросы «Заполнить пустые ячейки», вы можете загрузить образец файла книги Excel. Файл заархивирован, имеет формат xlsm и содержит макросы.

            
            

            Функция ЕПУСТО в Excel возвращает значение ИСТИНА, если ячейка пуста, и ЛОЖЬ, если ячейка не пуста. Например, если A1 содержит слово "яблоко", IПУСТО(A1) возвращает ЛОЖЬ.

            Функция ЕПУСТО возвращает ИСТИНА, если ячейка пуста, и ЛОЖЬ, если ячейка не пуста. Например, если A1 содержит слово «яблоко», IПУСТО(A1) возвращает ЛОЖЬ. Используйте функцию IПУСТО, чтобы проверить, пуста ячейка или нет. Функция ЕПУСТО принимает один аргумент, значение, которое представляет собой ссылку на ячейку, например A1.

            Слово "пустой" в Excel несколько вводит в заблуждение, поскольку ячейка, содержащая только пробел, будет выглядеть пустой, но не быть пустой. В общем, лучше всего думать, что IПУСТО означает "пусто", так как оно вернет ЛОЖЬ, когда ячейка выглядит пустой, но не является пустой.

            Примеры

            Если ячейка A1 вообще ничего не содержит, функция ЕПУСТО вернет значение ИСТИНА:

            Если ячейка A1 содержит любое значение или любую формулу, функция ЕПУСТО вернет ЛОЖЬ:

            Не пусто

            Чтобы проверить, является ли ячейка не пустой, вставьте ISBLANK внутрь функции NOT следующим образом:

            Приведенная выше формула возвращает значение TRUE, если ячейка не пуста, и FALSE, если ячейка пуста.

            Синтаксис пустой строки

            Во многих формулах используется сокращенный синтаксис для проверки наличия пустых ячеек вместо функции ЕПУСТО. В этом синтаксисе используется пустая строка ("") с математическими операторами Excel "=" или "<>". Например, чтобы проверить, пуст ли A1, вы можете использовать:

            Чтобы проверить, не пуст ли ячейка A1:

            Этот синтаксис взаимозаменяем с ISBLANK. Например, внутри функции ЕСЛИ:

            эквивалентно:

            Аналогично формула:

            Оба возвращают result1, если A1 не пуст, и result2, когда A1 пуст.

            Пустые строки

            Если ячейка содержит какую-либо формулу, функция ЕПУСТО и приведенные выше альтернативы вернут ЛОЖЬ, даже если формула возвращает пустую строку (""). Это может вызвать проблемы, если целью является подсчет или обработка пустых ячеек, содержащих пустые строки.

            Одним из способов обхода является использование функции LEN для проверки нулевой длины. Например, приведенная ниже формула вернет значение TRUE, если ячейка A1 пуста или содержит формулу, возвращающую пустую строку:

            Меня больше всего раздражает то, как термины [уникальный и отдельный] и [пустой и пустой] взаимозаменяемо используются в Excel.Они имеют разное значение, поэтому очень сложно понять, когда название функции или утилиты предполагает, что она будет делать одно, а на самом деле другое.

            Я определил уникальный и отличный в предыдущих сообщениях, таких как этот, поэтому я отложу их в сторону и сосредоточу сегодняшний разглагольствования на пустом и пустом. Прежде чем я расскажу о несоответствиях между ISBLANK() , COUNTBLANK() и VBA.IsEmpty() , вот мои определения пустых и пустых ячеек:

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

            Обратите внимание, что форматирование игнорируется. Эти определения являются только моими собственными, поэтому, пожалуйста, не принимайте их как истину, но они основаны на поведении VBA.IsEmpty() и COUNTBLANK() .

            Ячейка пуста?

            На рабочем листе лучший способ проверить, является ли ячейка пустой, – это использовать функцию рабочего листа ISBLANK():

            
            

            Строка 3 указывает, что находится в соответствующем столбце строки 4.

            Свойства Range.Value и Range.Value2 возвращают значение Variant/Empty, когда заданная ячейка пуста, поэтому лучший способ проверить, пуста ли ячейка в VBA, — это использовать функцию VBA.Information.IsEmpty() для их вывод:

            Функция рабочего листа ISBLANK() и VBA.IsEmpty() дают абсолютно одинаковые результаты, поэтому, если только кто-то не считает пустым и пустым одно и то же, их имена явно несовместимы. По моему собственному определению, ISBLANK() должна была называться ISEMPTY(). Стоит отметить, что функция рабочего листа ISBLANK() недоступна в VBA через класс Application.WorksheetFunction: предположительно, потому, что Microsoft признала, что ее функциональность уже покрыта VBA.IsEmpty(), который быстрее вызывается из VBA.

            Ячейка пуста?

            Функция рабочего листа СЧИТАТЬПУСТОТЫ() ведет себя не так, как ее аналог ЕПУСТО(). COUNTBLANK() будет подсчитывать пустые ячейки, ячейки с нулевой строкой и ячейки, содержащие символ префикса. Да, я знаю, это довольно запутанно. Преимущество в том, что мы можем использовать COUNTBLANK(), чтобы проверить, действительно ли ячейка пуста:

            
            

            Простой способ проверить, пуста ли ячейка в VBA, — это вызвать свойство Range.Value (или Range.Value2 ) и сравнить результат с константой VBA.Constants.vbNullString:

            В VBA эффективнее сделать это таким образом, чем вызывать метод Application.WorksheetFunction.CountBlank().

            Содержит ли ячейка нулевую строку?

            Я несколько раз упомянул пустые строки, чтобы лучше объяснить, что они из себя представляют. Пустая строка — это строка нулевой длины, которая может быть константой или результатом формулы. Например, эта формула возвращает нулевую строку:

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

            Приведенная ниже функция HasNullString() возвращает значение True, если ячейка содержит нулевую строку. Если вы хотите, чтобы результаты формулы игнорировались (т. е. проверялись только константы), передайте True в параметр blnConstantsOnly. Если в ячейке есть символ префикса, функция вернет False .

            Итак, я просто веду себя как старый придурок или вас тоже раздражают эти несоответствия?

            Используйте функцию ЕСЛИ и пустую строку в Excel, чтобы проверить, пуста ли ячейка. Используйте IF и ISBLANK, чтобы получить точно такой же результат.

            Если пусто

            Помните, что функция ЕСЛИ в Excel проверяет, выполняется ли условие, и возвращает одно значение, если оно истинно, и другое значение, если оно ложно.

            <р>1. Приведенная ниже функция ЕСЛИ возвращает Да, если входное значение равно пустой строке (две двойные кавычки, между которыми ничего нет), в противном случае она возвращает Нет.

            
            

            Примечание: если ячейка ввода содержит пробел, она выглядит пустой. Однако в этом случае входное значение не равно пустой строке, и приведенная выше функция ЕСЛИ вернет Нет.

            <р>2. Используйте IF и ISBLANK, чтобы получить точно такой же результат.

            
            

            Примечание: функция ЕПУСТО возвращает ИСТИНА, если ячейка пуста, и ЛОЖЬ, если нет. Если входная ячейка содержит пробел или формулу, которая возвращает пустую строку, она выглядит пустой.Однако в этом случае входная ячейка не пуста, и приведенная выше формула вернет Нет.

            Если не пусто

            В Excel <> означает не равно.

            <р>1. Приведенная ниже функция ЕСЛИ умножает входное значение на 2, если входное значение не равно пустой строке (две двойные кавычки, между которыми ничего нет), в противном случае возвращается пустая строка.

            
            

            <р>2. Используйте ЕСЛИ, НЕ и ЕПУСТО, чтобы получить точно такой же результат.

            
            

            Выделить пустые ячейки

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

            <р>1. Например, выберите диапазон A1:H8.

            
            

            <р>2. На вкладке "Главная" в группе "Стили" нажмите "Условное форматирование".

            
            

            <р>3. Нажмите «Правила выделения ячеек», «Дополнительные правила».

            
            

            <р>4. В раскрывающемся списке выберите «Пробелы», выберите стиль форматирования и нажмите «ОК».

            
            

            
            

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

            Читайте также:

              
            • Прошивка модема Lte 4g
              
            • Как заменить первую цифру 8 на 7 в Excel
              
            • Если вы обновите драйверы, все будет удалено
              
            • Поиск в ячейке по цвету в Excel
              
            • К сожалению, произошла непредвиденная ошибка, и приложение Word не удалось запустить 24