Заполнить пустые ячейки в 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. С одной стороны, ваша таблица выглядит аккуратнее и читабельнее, если вы не загромождаете ее повторяющимися значениями. С другой стороны, пустые ячейки Excel могут вызвать проблемы при сортировке, фильтрации данных или создании сводной таблицы. В этом случае вам необходимо заполнить все поля. Существуют разные методы решения этой проблемы. Я покажу вам один быстрый и один ОЧЕНЬ быстрый способ заполнить пустые ячейки разными значениями в Excel 2010 и 2013.

            Поэтому мой ответ "Заполнить". А теперь давайте посмотрим, как это сделать.

            Как выделить пустые ячейки на листах Excel

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

            
            

            Примечание. Если вы забыли сочетания клавиш, перейдите в группу Редактирование на вкладке ГЛАВНАЯ и выберите команду Перейти к специальному из меню Найти и выбрать. На экране появится такое же диалоговое окно.

            
            

            
            

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

            Формула Excel для заполнения пустых ячеек значением выше/ниже

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

            1. Оставьте выбранными все незаполненные ячейки.
            2. Нажмите F2 или просто поместите курсор в строку Формулы, чтобы начать ввод формулы в активной ячейке.

            А вот и ты! Теперь каждая выделенная ячейка имеет ссылку на ячейку поверх нее.

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

            Используйте надстройку «Заполнить пустые ячейки» от Ablebits

            Если вы не хотите иметь дело с формулами каждый раз, когда заполняете пробелы в ячейке выше или ниже, вы можете использовать очень полезную надстройку для Excel, созданную разработчиками Ablebits. Утилита «Заполнить пустые ячейки» автоматически копирует значение из первой заполненной ячейки вниз или вверх. Продолжайте читать и узнайте, как это работает.

            и установите его на свой компьютер.

            
            

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

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

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

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

            Способ 1

            Несколько секунд, и все пустые ячейки заполнены введенным вами значением.

            Метод 2

            Он автоматически заполнит пустые ячейки значением, которое вы ввели в текстовое поле Заменить на.

            Какой бы способ вы ни выбрали, заполнение таблицы Excel займет у вас минуту.

            Теперь вы знаете, как заполнить пустые поля разными значениями в Excel 2013. Уверен, вам не составит труда сделать это с помощью простой формулы, функции поиска и замены Excel или удобной надстройки Ablebits. .

            Вас также может заинтересовать

            172 комментария к "Как заполнить пустые ячейки 0 со значением выше/ниже в Excel"

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

            Привет!
            Я не могу угадать, какую функцию вы использовали и какие именно ячейки остались пустыми. Можешь описать?

            Здравствуйте, сэр/мэм,
            Пожалуйста, помогите мне, как заполнить все пробелы в нескольких выбранных строках нулем (0).

            Привет!
            Обратите внимание на следующий абзац статьи выше — Заполните пустые ячейки 0 или другим конкретным значением.

            Отличное решение, спасибо, что поделились им! Сэкономлено довольно много времени

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

            Здравствуйте, команда Ablebits!

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

            Привет!
            Формула Excel может вставлять данные только в ту ячейку, в которой они записаны. Для заполнения пустых ячеек данными рекомендую обратить внимание на инструмент «Заполнить пустые ячейки». Он доступен как часть нашего Ultimate Suite for Excel, который вы можете установить в пробном режиме и бесплатно проверить, как он работает.

            Привет,
            У меня есть данные, в которых я хочу заполнить текстом последней строки в верхних строках. Я хочу заполнить 320304 во всех верхних строках.

            55
            215,24
            0,23
            237,24
            756
            7 745,72
            841,02
            1 806,26
            307,32
            0,04 Товары Квитанция 320304

            Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

            Проблема: кто-то настроил данные в представлении структуры. Мне нужно отсортировать по столбцам A, B и C, поэтому мне нужно заполнить все пробелы.

            
            

            Заполните пропуски.

            Стратегия: используйте «Перейти к специальному», чтобы выбрать пустые ячейки. Затем 3 простых нажатия клавиш заполнят пустые ячейки указанным выше значением. Выполните следующие действия.

            1. Выберите от A3 до последнего пробела в столбце C.
            2. Выберите «Главная», «Найти и выбрать», «Перейти к специальному».
            3. В диалоговом окне «Перейти к специальному» выберите «Пробелы» и нажмите «ОК».
            4. Введите знак равенства и нажмите стрелку вверх. Это создаст формулу, указывающую на одну ячейку вверх.

            
            

            1. Нажмите Ctrl+Enter, чтобы заполнить все выделенные ячейки аналогичной формулой.

            
            

            1. Прежде чем вы сможете сортировать, вам нужно преобразовать формулы в значения. Вставить значения не работает для несмежных выделенных фрагментов, поэтому вам придется повторно выбрать столбцы A:C
            2. Ctrl+C для копирования
            3. Главная страница, раскрывающийся список "Вставить", "Вставить значения", чтобы преобразовать формулы в значения.

            Дополнительные ресурсы для Microsoft Excel

            ExcelArticles.com предоставляет примеры формул, функций и процедур Visual Basic только для иллюстрации, без явных или подразумеваемых гарантий, включая, помимо прочего, подразумеваемые гарантии товарного состояния и/или пригодности для определенной цели. Формулы, функции и процедуры Visual Basic на этом веб-сайте предоставляются «как есть», и мы не гарантируем, что их можно использовать во всех ситуациях.

            Excel® является зарегистрированным товарным знаком корпорации Microsoft.
            MrExcel® является зарегистрированным товарным знаком Tickling Keys, Inc.

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

            Как показано ниже:

            
            

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

            И это легко исправить!

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

            Это можно легко сделать, используя простой прием специального диалогового окна Go-To, VBA или Power Query.

            Итак, приступим!

            Это руководство охватывает:

            Способ 1. Заполнить с помощью команды «Переход к специальному + формула»

            Предположим, у вас есть набор данных, как показано ниже, и вы хотите заполнить данные в столбцах A и B.

            
            

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

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

            1. Выберите данные, которые вы хотите заполнить (в нашем примере A1:D21)
            2. Перейти на вкладку "Главная".

            
            

            1. В группе «Редактирование» нажмите значок «Найти и выбрать» (в раскрывающемся списке появятся дополнительные параметры).

            
            

            
            

            1. В открывшемся диалоговом окне «Переход» нажмите кнопку «Специальная». Откроется диалоговое окно «Перейти к специальному».
            1. В диалоговом окне "Перейти к специальному" нажмите "Пробелы".

            
            

            Вышеуказанные шаги позволят выбрать все пустые ячейки в наборе данных (как показано ниже).

            
            

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

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

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

            1. Нажмите клавишу "равно" (=) на клавиатуре. Это добавит знак равенства в активную ячейку.
            2. Нажмите клавишу со стрелкой вверх.Это вставит ссылку на ячейку над активной ячейкой. Например, в нашем случае B3 является активной ячейкой, и когда мы делаем эти два шага, в ячейке появляется =B2
            3. Удерживая нажатой клавишу Control, нажмите клавишу Enter.

            Вышеуказанные шаги автоматически вставят все пустые ячейки со значением над ними.

            
            

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

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

            Преобразование формул в значения

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

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