Как быстро заполнить пустые ячейки в Excel?
В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значениями выше или ниже нулями или любым другим шаблоном.
Заполнять пробелы или нет? Этот вопрос часто относится к пустым ячейкам в таблицах Excel. С одной стороны, ваша таблица выглядит аккуратнее и читабельнее, если вы не загромождаете ее повторяющимися значениями. С другой стороны, пустые ячейки могут вызвать проблемы при сортировке, фильтрации данных или создании сводной таблицы. В этом случае желательно заполнить все поля.
Итак, мой ответ — Заливка. Теперь посмотрим, как это сделать. Есть несколько способов решить эту проблему. Я покажу вам быстрый и ОЧЕНЬ быстрый способ заполнения пустых ячеек значениями.
Как выделить пустые ячейки на листах Excel.
Перед тем, как заполнить поля в таблице Excel, вам необходимо их сначала выделить. Если у вас есть большая электронная таблица с десятками пустых областей, разбросанных по ней, на то, чтобы сделать это вручную, потребуется много времени. Вот быстрый трюк для выбора пустых ячеек.
Выберите столбцы или строки, в которых вы хотите заполнить пробелы.
- Нажмите Ctrl + G или F5, чтобы открыть диалоговое окно «Перейти”.
- Нажмите кнопку «Выбрать».
- Выберите «Пустые ячейки».
Совет. Если вы забыли сочетания клавиш, перейдите в группу «Найти и выбрать» на вкладке «Главная» и выберите «Выбрать группу ячеек» в раскрывающемся меню. Отображается тот же диалог.
Затем выберите, что выделить. Например, формулы, комментарии, константы, пробелы и т.д.
Установите переключатель «Пустые ячейки» и нажмите «ОК».
Теперь выделены только пустые ячейки выбранного диапазона, и вы готовы к следующему шагу.
Формула Excel для заполнения пустых ячеек значениями, стоящими выше / ниже
Выбирая пустые ячейки в таблице, вы можете заполнить их верхними или нижними значениями или просто ввести определенное содержимое.
Если вы собираетесь заполнить пустые поля значением из ближайшей сплошной ячейки выше или ниже, вам нужно ввести очень простую формулу в одну из пустых ячеек. Затем скопируйте его на все остальные. Вот как это сделать.
Выделите все пустые ячейки, как описано выше.
Нажмите F2 или просто поместите курсор в строку формул, чтобы начать ввод формулы в активной ячейке.
Как видно на скриншоте ниже, активная ячейка — A3, то есть по умолчанию это верхняя левая часть всех пробелов.
Введите знак равенства (=).
Переместите курсор в ячейку выше или ниже с помощью клавиши со стрелкой вверх или вниз или просто щелкните по ней мышью.
Формула (= A2) показывает, что A3 получит значение из A2 и будет заполнено предыдущим значением.
Нажмите Ctrl + Enter, чтобы автоматически вставить формулу одновременно во все выбранные места.
Хороший! Каждая выделенная ячейка теперь ссылается на ячейку над ней.
Совет. Вы должны помнить, что все ранее пустые ячейки теперь содержат формулы. А если вы хотите, чтобы ваша таблица была в порядке, лучше эти формулы заменить значениями. В противном случае вы получите беспорядок при сортировке или добавлении строк или столбцов в таблицу.
Поэтому рекомендую не останавливаться и сразу после ввода формул заменить их значениями. Следуйте этим простым шагам:
- Вы выбрали все ячейки с формулами, которые вы только что ввели и хотите преобразовать.
- Нажмите Ctrl + C или Ctrl + Ins, чтобы скопировать формулы и их результаты в буфер обмена.
- Нажмите Shift + F10, а затем V, чтобы вставить обратно только значения в выбранных местах.
Shift + F10 + V — самый быстрый способ использовать диалоговое окно Excel «Специальная вставка».
Заполните пустые ячейки нулями или другим определенным значением
Что делать, если вам нужно заполнить все пробелы в таблице нулями, любыми другими числами или точно такими же данными? Вот два способа решить эту проблему.
Способ 1.
- Выделите пустые ячейки, как мы.
- Нажмите F2, чтобы активировать режим редактирования в строке формул. Или просто щелкните там мышью.
- Введите желаемый номер или текст.
- Нажмите Ctrl + Enter.
Несколько секунд и все пустые ячейки заполняются таким же образом введенным словом, символом или нулями при необходимости.
Способ 2.
- Выберите диапазон с пустыми ячейками.
- Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить». Или воспользуйтесь меню
- В этом окне перейдите на вкладку «Заменить».
- Оставьте поле «Найти» пустым и введите желаемое значение в текстовое поле «Заменить на».
- Нажмите «Заменить все».
Пустые ячейки будут заполнены указанным значением.
Заполнение пустых ячеек при помощи макроса VBA.
Если вам часто приходится выполнять такую операцию, есть смысл создать для нее отдельный макрос, чтобы не повторять вручную всю цепочку действий, перечисленных выше. Для этого нажмите Alt + F11 или кнопку Visual Basic на вкладке «Разработчик», чтобы открыть редактор VBA, затем вставьте новый пустой модуль через меню «Вставка — модуль». Затем скопируйте или вставьте следующий короткий код:
Sub Fill_Blanks()
Для каждой выбранной ячейки
Если IsEmpty (ячейка) Тогда cell.Value = cell.Offset (-1, 0) .Value
Следующая ячейка
Конец подзаголовка
Как вы можете легко понять, этот макрос последовательно просматривает все выбранные ячейки и, если они не пустые, заполняет их значениями из предыдущей ячейки выше.
Для удобства вы можете назначить этому макросу сочетание клавиш или даже поместить его в свою личную книгу макросов, чтобы он был доступен, когда вы работаете с любым из ваших файлов Excel.
Какой бы метод вы ни выбрали, заполнение таблицы Excel займет буквально минуту.
Как быстро заполнить пустые ячейки без использования формул.
Если вы не хотите, чтобы вам приходилось иметь дело с формулами каждый раз, когда вы заполняете пробелы в своей электронной таблице, вы можете использовать очень полезное дополнение Ultimate Suite Excel, созданное разработчиками Ablebits. Включенная в него утилита «Заполнить пустые ячейки» автоматически копирует значение из первой заполненной ячейки снизу или сверху в пустые ячейки таблицы. Далее посмотрим, как это работает.
Вот наши показатели продаж по менеджерам и по регионам. Некоторые из продавцов работали в разных регионах, их информация о продажах записана друг под другом. Ячейки месяца также объединены. Таблица выглядит вполне разборчивой. Однако, если вам нужно отфильтровать или обобщить данные по менеджерам или найти объем продаж по регионам за конкретный месяц, это будет очень сложно сделать. Пустые объединенные ячейки будут этому мешать.
Поэтому постараемся привести таблицу к стандартному виду, заполнив все пробелы и разделив ранее совмещенные области.
Перейдите на вкладку AblebitsTools на ленте.
Установите курсор на любую ячейку таблицы, в которой вам нужно заполнить пустые ячейки.
Щелкните значок «Заполнить пустые ячейки)».
На экране появится окно надстройки, в котором будут перечислены все столбцы и указаны параметры заполнения.
Снимите флажки со столбцов, в которых нет пустых ячеек.
Выберите действие из раскрывающегося списка в правом нижнем углу окна.
Если вы хотите заполнить пустые поля значением ячейки, указанным выше, выберите параметр «Заполнить ячейки вниз». Если вы хотите скопировать содержимое из ячейки ниже, выберите «Заполнить ячейки вверх» в том же раскрывающемся списке. В нашем случае мы выбираем заливку.
Нажмите кнопку «Заливка).
Готово!
