Заполнить пустые ячейки в Excel значением из ячейки выше быстрее всего так: выделите столбец, нажмите F5 → «Выделить» → «Пустые ячейки», введите знак «=», нажмите стрелку ↑ и затем Ctrl+Enter. Каждая пустая ячейка получит формулу со ссылкой на ячейку над ней; останется заменить формулы значениями. Тем же способом пустые ячейки заполняются нулями — вместо «=» и стрелки введите 0. А сверху, снизу или нулями без формул пустые ячейки заполняет процедура !SEMTools в 2 клика.
Часто в больших таблицах для придания им более читабельного вида данные, повторяющиеся подряд для нескольких ячеек, не заполняют. Или в столбцах с числовыми значениями присутствуют пустые ячейки вместо нулей.
Для анализа такие данные едва пригодны. Если нужно создать на основе таких таблиц сводную, произвести сортировку или фильтрацию данных, потребуется вначале заполнить все пустые ячейки. Рассмотрим ситуации подробнее на примерах.
Заполнить пустые ячейки значением сверху за 4 шага
Это самый быстрый штатный способ: он не требует фильтров и работает сразу на всём столбце.
- Выделите диапазон с пропусками, например A2:A500. Первая ячейка диапазона должна быть заполнена.
- Нажмите F5 (или Ctrl+G) → «Выделить…» → «Пустые ячейки» → «ОК». Выделятся только пустые ячейки, активной станет первая из них.
- Не щёлкая мышью, введите «=» и нажмите стрелку ↑ — в формуле появится ссылка на ячейку над активной.
- Нажмите Ctrl+Enter: формула со ссылкой «на ячейку выше» запишется во все выделенные пустые ячейки.
После этого выделите столбец и замените формулы значениями, иначе при сортировке ссылки собьются и данные перепутаются.
Заполнить пустые ячейки предыдущим значением формулой
Если исходный столбец трогать нельзя, заполненную копию удобно построить во вспомогательном столбце: формула берёт значение из исходной ячейки, а если она пустая — из ячейки выше в своём же столбце.
| A | B | C | |
|---|---|---|---|
| 1 | Регион | Заполнено | Менеджер |
| 2 | Москва | Москва=A2 | Иванов |
| 3 | Москва=ЕСЛИ(A3="";B2;A3) | Петров | |
| 4 | Москва=ЕСЛИ(A4="";B3;A4) | Сидоров | |
| 5 | Казань | Казань=ЕСЛИ(A5="";B4;A5) | Орлова |
| 6 | Казань=ЕСЛИ(A6="";B5;A6) | Ким | |
| 7 | Самара | Самара=ЕСЛИ(A7="";B6;A7) | Белова |
| 8 | Самара=ЕСЛИ(A8="";B7;A8) | Зайцев |
В B3 формула видит пустую A3 и берёт значение из B2 — «Москва». Зелёным отмечены ячейки, которые были пустыми. B2 просто ссылается на A2: первая ячейка столбца должна быть заполнена.
В английском Excel: =IF(A3="",B2,A3)
Стандартными средствами Excel это делается в несколько шагов:
- Создается фильтр по столбцу,
- В первой пустой ячейке прописывается формула, ссылающаяся на предыдущую ячейку, например, в ячейке A3 будет формула "=A2",
- Копируем ячейку с формулой,
- Отфильтровываем только пустые ячейки столбца,
- Выделяем их и вставляем в них формулу сочетанием Ctrl + V,
- Снимаем фильтр,
- Выделяем весь столбец и удаляем формулы, оставляя значения.
У данного решения есть очевидные минусы:
- это рутинная многоступенчатая операция, отнимающая время;
- если ячейки столбца имеют разное форматирование, при копировании ячейки с формулой может потребоваться его восстанавливать, т.к. вместе с формулой ячейки получат в наследство и форматирование исходной;
- при этом штатными методами Excel вставить в отфильтрованные ячейки только формулы невозможно.
Заполнить пустые ячейки в 2 клика с !SEMTools
Надстройка !SEMTools позволяет решить задачу в пару мгновений, не меняя форматирование пустых ячеек. Процедура находится в меню «Изменить ячейки» в группе «ИЗМЕНИТЬ».
Заполнить пустые ячейки значением ниже
Штатно — тем же способом, что и сверху, только после «=» нажмите стрелку ↓: каждая пустая ячейка сошлётся на ячейку под ней. Если пустых ячеек несколько подряд, формулы сошлются друг на друга по цепочке и все получат значение ближайшей заполненной ячейки снизу. С !SEMTools — без формул:
Заполнить пустые ячейки нулями
Заполнение пустых ячеек нулями — несложная операция. Быстрее всего — выделить диапазон, F5 → «Выделить» → «Пустые ячейки», ввести 0 и нажать Ctrl+Enter. Можно произвести её и с помощью процедуры "Найти и заменить", выделив диапазон и вызвав диалог сочетанием клавиш Ctrl + H. Конфигурация замены должна быть такой, как на картинке — ищем ячейки целиком, в которых ничего нет, и заменяем пустоты на нули.
С помощью надстройки !SEMTools, тем не менее, это можно сделать еще быстрее:
Почему пустые ячейки не заполняются — частые ошибки
- Excel пишет «Не найдено ни одной ячейки». Ячейки только выглядят пустыми: в них пробелы или формулы, возвращающие "". Сначала удалите пробелы или замените формулы значениями.
- Данные перепутались после сортировки. В заполненных ячейках остались формулы со ссылками «на ячейку выше», и после сортировки они ссылаются на другие строки. Перед сортировкой всегда заменяйте формулы значениями.
- В таблице объединённые ячейки. Объединённая ячейка хранит значение только в первой из них. Выделите столбец, снимите объединение («Главная → Объединить и поместить в центре») и заполните появившиеся пустые ячейки способом выше.
- Формула заполнила ячейки нулями. Первая ячейка диапазона была пустой, и ссылка «на ячейку выше» вела на заголовок или пустоту. Начинайте выделение с заполненной ячейки.
Заполнить пустые ячейки — частые вопросы
Как заполнить пустые ячейки значением из ячейки выше?
Выделите столбец, F5 → «Выделить» → «Пустые ячейки», введите «=», нажмите ↑ и Ctrl+Enter. Затем замените формулы значениями.
Как заполнить все пустые ячейки нулями?
Выделите диапазон, F5 → «Выделить» → «Пустые ячейки», введите 0 и нажмите Ctrl+Enter. Или Ctrl+H с пустым полем «Найти», включённой опцией «Ячейка целиком» и 0 в поле «Заменить на».
Какой формулой заполнить пропуски во вспомогательном столбце?
=ЕСЛИ(A3="";B2;A3) — берёт значение из исходной ячейки, а если она пустая, то из ячейки выше в своём столбце.
Как заполнить пустые ячейки после снятия объединения?
Снимите объединение, оставив выделенным тот же диапазон, затем выделите пустые ячейки через F5 и заполните их ссылкой на ячейку выше.
Заполнить пустые ячейки значениями сверху, снизу или нулями можно в 2 клика с !SEMTools — без выделения группы ячеек, формул и последующей замены их значениями, с сохранением форматирования.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как заполнить пустые ячейки в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли заполнить пропуски в своей таблице. Для разовой задачи хватит «Выделить группу ячеек» и Ctrl+Enter, если исходный столбец трогать нельзя — формулы во вспомогательном столбце. Если такие таблицы приходят регулярно, быстрее заполнять их процедурой в 2 клика. Вас наверняка заинтересуют и похожие операции: удалить пустые строки и добавить текст во все ячейки.