Много раз вы можете столкнуться с набором данных Excel, в котором только одна ячейка заполнена данными, а ячейки под ней пусты до следующего значения.
Как показано ниже:
Хотя этот формат работает для некоторых людей, проблема с такими данными заключается в том, что вы не можете использовать их для создания сводных таблиц или использования в вычислениях.
Это легко исправить!
В этом уроке я покажу вам, какБыстро заполнять ячейки до следующего значения заполнения в Excel.
Вы можете легко сделать это, используя простые специальные диалоговые методы Go-To, VBA или Power Query.
Итак, приступим!
Содержание:
Способ 1. Заполните, используя формулу «Перейти к специальному +».
Предположим, у вас есть набор данных, как показано ниже, и вы хотите заполнить данные в столбцах A и B.
В столбце B цель состоит в том, чтобы заполнить «принтер» до последней пустой ячейки под ним, затем, когда запустится «сканер», затем заполнить «сканер» в ячейке ниже, пока ячейка не станет пустой.
В приведенных ниже шагах используется специальный переход, чтобы выбрать все пустые ячейки и заполнить их простой формулой:
- Выберите данные для заполнения (A1:D21 в нашем примере)
- Перейти на вкладку «Главная»
- В группе редактирования щелкните значок «Найти и выбрать» (это откроет дополнительные параметры в раскрывающемся меню).
- Нажмите на опцию «Перейти»
- В открывшемся диалоговом окне «Перейти» нажмите кнопку «Специальная».Откроется диалоговое окно «Перейти к специальному».
- В диалоговом окне «Перейти к специальному» нажмите «Пустой».
- Нажмите ОК
Приведенные выше шаги выберут все пустые ячейки в наборе данных (показано ниже).
Среди выделенных пустых ячеек вы заметите, что одна ячейка ярче, чем остальная часть выделения.Эта ячейка является активной ячейкой, в которую мы хотим ввести формулу.
Не беспокойтесь о позиции ячейки в выделении, так как наш метод будет работать в любом случае.
Теперь вот шаги для заполнения данных в выбранных пустых ячейках:
- Нажмите клавишу знака равенства (=) на клавиатуре.Это добавит знак равенства в активную ячейку.
- Нажмите клавишу со стрелкой вверх.Это вставит ссылку на ячейку над активной ячейкой.Например, в нашем случае B3 является активной ячейкой, и когда мы делаем эти два шага, она вводит =B2 в ячейку.
- Удерживая нажатой клавишу Control, нажмите Enter
Приведенные выше шаги автоматически вставят все пустые ячейки с указанными выше значениями.
Хотя это может показаться слишком большим количеством шагов, как только вы освоитесь, вы сможете быстро заполнять данные в Excel за считанные секунды.
Теперь есть еще две вещи, о которых вам нужно знать при использовании этого метода.
Преобразование формулы в значение
Во-первых, убедитесь, что вы конвертируете формулы в значения (чтобы у вас были статические значения и вы не облажались, когда будете изменять данные в будущем).
Изменить формат даты
Если вы используете даты в своих данных (как я сделал в своем примере данных), вы заметите, что заполненные значения в столбце даты являются числами, а не датами.
Если вывод в столбце даты находится в нужном формате даты, то все в порядке и больше ничего делать не нужно.
Но если эти даты не в правильном формате, требуется точная настройка.Пока у вас есть правильное значение, вам просто нужно изменить формат, чтобы оно отображалось как дата в ячейке.
Вот шаги, чтобы сделать это:
- Выберите столбцы, содержащие даты
- Щелкните вкладку Главная
- В группе «Числа» щелкните раскрывающееся меню «Формат» и выберите формат даты.
Если вам нужно время от времени делать эту прокладку, я рекомендую эту специальную технику и формулу Go-To.
Хотя он состоит из нескольких шагов, он прост и дает вам результаты в наборе данных.
Но если вам приходится делать это часто, я предлагаю вам взглянуть на VBA и метод Power Query, описанный далее.
Способ 2. Заполните простой код VBA
Вы можете использовать очень простой код макроса VBA для быстрого заполнения ячеек в Excel до последнего пустого значения.
Вам нужно добавить код VBA в файл только один раз, после чего вы можете легко повторно использовать этот код несколько раз в одной и той же книге или даже в нескольких книгах в вашей системе.
Вот код VBA, который будет перебирать каждую ячейку в выборе и заполнять все пустые ячейки:
Sub FillDown() Для каждой ячейки в выборе Если ячейка = "" Тогда ячейка.FillDown End If Next End Sub
В приведенном выше коде используется цикл For для перебора каждой выбранной ячейки.
В цикле For я использую условие If-Then, чтобы проверить, пуста ли ячейка.
Если ячейка пуста, она заполняется значением из ячейки выше, если нет, цикл for игнорирует ячейку и переходит к следующей ячейке.
Теперь, когда у вас есть код VBA, позвольте мне показать вам, куда поместить этот код в Excel:
- Выберите данные для заполнения
- Нажмите на вкладку разработчика
- Щелкните значок Visual Basic (или можно использовать сочетание клавиш ALT+F11).Это откроет редактор Visual Basic в Excel.
- В редакторе Visual Basic слева у вас будет Project Explorer.Если вы его не видите, нажмите «Просмотр» в меню, затем нажмите «Проводник проекта».
- Если у вас открыто несколько книг Excel, Project Explorer отображает имена всех книг.Найдите имя книги, в которой у вас есть данные.
- Щелкните правой кнопкой мыши любой объект в книге, выберите «Вставить» и выберите «Модуль».Это вставит новый модуль для этой книги.
- Дважды щелкните «Модуль», который вы только что вставили на предыдущих шагах.Откроется окно кода для этого модуля.
- Скопируйте и вставьте код VBA в окно кода.
- Поместите курсор в любое место кода и запустите код макроса, нажав зеленую кнопку на панели инструментов или используя сочетание клавиш F5.
Вышеуказанные шаги запустят код VBA, и ваши данные будут заполнены.
Если вы хотите снова использовать этот код VBA в будущем, вам нужно будет сохранить этот файл как книгу Excel с поддержкой макросов (с расширением .XLSM).
Вы также можете добавить этот макрос на панель быстрого доступа, которая всегда видна, и вы можете получить доступ к этому макросу одним щелчком мыши (в рабочей книге с кодом в бэкэнде).
Поэтому в следующий раз, когда у вас появятся данные, которые нужно заполнить, вы можете просто сделать выбор и нажать кнопку макроса на панели быстрого доступа.
Вы также можете добавить этот макрос в свою личную книгу макросов, а затем использовать его в любой книге в вашей системе.
Нажмите здесь, чтобы узнать больше о вашей личной книге макросов и о том, как добавить в нее код.
Способ 3 — заполнить с помощью Power Query
Power QueryИмеет встроенный функционал, позволяющий заполнить его одним щелчком мыши.
Рекомендуется, если вы все еще используете Power Query для преобразования данных или объединения данных из нескольких листов или нескольких книг.
В рамках существующего рабочего процесса вы можете быстро заполнить пустые ячейки с помощью параметра «Заполнить» в Power Query.
Чтобы использовать Power Query, рекомендуется, чтобы ваши данные были в формате таблицы Excel.Если вы не можете преобразовать данные в таблицу Excel, необходимо создать именованный диапазон для данных, а затем использовать этот именованный диапазон в Power Query.
Ниже у меня есть набор данных, который был преобразован в таблицу Excel.Это можно сделать, выбрав набор данных, перейдя на вкладку «Вставка» и щелкнув значок «Таблица».
Вот шаги, чтобы использовать Power Query для заполнения данных до следующего значения:
- Выберите любую ячейку в наборе данных
- Перейдите на вкладку Данные
- В группе «Получить и преобразовать данные» щелкните «Из рабочего листа».Это откроет редактор Power Query.Обратите внимание, что пустые ячейки будут отображать значение «null» в Power Query.
- В редакторе Power Query выберите столбец, который вы хотите заполнить.Для этого, удерживая нажатой клавишу Control, щелкните заголовок столбца, который вы хотите выбрать.В нашем примере это будут столбцы даты и продукта.
- Щелкните правой кнопкой мыши любой выбранный заголовок
- Перейдите к опции «Заливка» и нажмите «Вниз».Это заполнит все пустые ячейки данными
- Перейдите на вкладку «Файл», а затем нажмите «Закрыть и загрузить».Это вставит новый рабочий лист с таблицей результатов в рабочую книгу.
Хотя этот подход может показаться излишним, одно из больших преимуществ использования Power Query заключается в том, что он позволяет быстро обновлять полученные данные при изменении исходных данных.
Например, если вы добавите дополнительные записи к исходным данным, вам не нужно будет снова делать все вышеперечисленное.Вы можете просто щелкнуть правой кнопкой мыши полученную таблицу и нажать «Обновить».
Хотя метод Power Query работает хорошо, его лучше использовать, когда вы уже используете Power Query в своем рабочем процессе.
Если у вас есть только один набор данных с пустыми ячейками для заполнения, удобнее использовать специальный метод «Перейти» или метод VBA (описанный выше).
Таким образом, вы можете использовать эти три простых метода дляЗаполнять пустые ячейки до следующего значения в Excel.
Я надеюсь, что вы нашли этот урок полезным.
