Посмотреть видео - Заполнить в Excel
Часто вы можете встретить набор данных, в котором только одна ячейка заполнена данными, а ячейки под ней остаются пустыми до следующего значения.
Что-то вроде того, что показано ниже:
Хотя этот формат подходит для некоторых людей, проблема с данными такого рода заключается в том, что вы не можете использовать их для создания сводных таблиц или в расчетах.
И это легко исправить!
В этом уроке я покажу вам, как быстро заполнять ячейки в Excel до следующего заполненного значения.
Вы можете легко сделать это, используя простой метод перехода к специальному диалоговому окну, VBA или Power Query.
Итак, приступим!
Метод 1 - заполнение с помощью "Перейти к специальному + формула"
Предположим, у вас есть набор данных, показанный ниже, и вы хотите заполнить данные в столбцах A и B.
В столбце B цель состоит в том, чтобы заполнить «Принтер» до последней пустой ячейки под ним, а затем, когда «Сканер» запустится, заполнить «Сканер» в ячейках ниже, пока ячейки не станут пустыми.
Ниже приведены шаги, которые нужно использовать, перейдите к специальному разделу, чтобы выбрать все пустые ячейки и заполнить их, используя простую формулу:
- Выберите данные, которые вы хотите заполнить (A1: D21 в нашем примере)
- Перейдите на вкладку "Главная"
- В группе редактирования нажмите значок «Найти и выбрать» (в раскрывающемся списке появятся дополнительные параметры).
- Нажмите на опцию «Перейти».
- В открывшемся диалоговом окне Go-To нажмите кнопку Special. Откроется диалоговое окно «Перейти к специальному».
- В диалоговом окне "Перейти к специальному" нажмите "Бланки".
- Нажмите ОК.
Вышеупомянутые шаги выберут все пустые ячейки в наборе данных (как показано ниже).
В выбранных пустых ячейках вы заметите, что одна ячейка светлее, чем остальная часть выделения. Эта ячейка является активной ячейкой, в которую мы собираемся ввести формулу.
Не беспокойтесь о том, где находится ячейка в выделенном фрагменте, наш метод будет работать в любом случае.
Теперь ниже приведены шаги по заполнению данных в выбранных пустых ячейках:
- Нажмите клавишу «равно» (=) на клавиатуре. Это вставит знак равенства в активную ячейку.
- Нажмите клавишу со стрелкой вверх. Это вставит ссылку на ячейку над активной ячейкой. Например, в нашем случае B3 является активной ячейкой, и когда мы выполняем эти два шага, он вводит = B2 в ячейку.
- Удерживая клавишу Control, нажмите клавишу Enter.
Приведенные выше шаги автоматически вставят все пустые ячейки со значением над ними.
Хотя это может показаться слишком большим количеством шагов, как только вы освоите этот метод, вы сможете быстро заполнить данные в Excel в течение нескольких секунд.
Теперь есть еще две вещи, о которых вам нужно позаботиться при использовании этого метода.
Преобразование формул в значения
Первый - убедиться, что вы конвертируете формулы в значения (чтобы у вас были статические значения, и что-то не испортится, если вы измените данные в будущем).
Ниже приведено видео, в котором показано, как быстро преобразовать формулы в значения в Excel.
Изменить формат даты
Если вы используете даты в своих данных (как я в примере данных), вы могли бы заметить, что заполненные значения в столбце даты являются числами, а не датами.
Если вывод в столбце даты имеет желаемый формат даты, все в порядке и больше ничего делать не нужно.
Но если они имеют неправильный формат даты, вам необходимо внести незначительные изменения. Пока у вас есть правильные значения, вам просто нужно изменить формат, чтобы он отображался как дата в ячейке.
Ниже приведены шаги для этого:
- Выберите столбец с датами
- Перейдите на вкладку "Главная"
- В группе «Число» щелкните раскрывающийся список «Формат» и выберите формат даты.
Если вам нужно время от времени делать это заполнение, я рекомендую вам использовать эту специальную технику Go-To и формулу.
Хотя он состоит из нескольких шагов, он прост и дает результат прямо в вашем наборе данных.
Но если вам приходится делать это довольно часто, я предлагаю вам взглянуть на методы VBA и Power Query, которые рассматриваются далее.
Метод 2 - заполнение с помощью простого кода VBA
Вы можете использовать действительно простой код макроса VBA, чтобы быстро заполнить ячейки в Excel до последнего пустого значения.
Вам нужно всего лишь один раз добавить код VBA в файл, а затем вы можете легко повторно использовать код несколько раз в одной книге или даже в нескольких книгах в вашей системе.
Ниже приведен код VBA, который пройдет через каждую выделенную ячейку и заполнит все пустые ячейки:
'Этот код VBA создается Sumit Bansal из TrumpExcel.com Sub FillDown () для каждой ячейки в выделении If cell = "" Then cell.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. Обратите внимание, что в пустых ячейках в Power Query будет отображаться значение «null».
- В редакторе Power Query выберите столбцы, которые вы хотите заполнить. Для этого, удерживая клавишу Control, щелкните заголовок столбцов, которые вы хотите выбрать. В нашем примере это столбцы «Дата» и «Продукт».
- Щелкните правой кнопкой мыши любой из выбранных заголовков
- Перейдите к опции «Заливка» и нажмите «Вниз». Это заполнит данные во всех пустых ячейках.
- Перейдите на вкладку «Файл» и нажмите «Закрыть и загрузить». Это вставит новый рабочий лист в книгу с итоговой таблицей.
Хотя этот метод может показаться излишним, одно большое преимущество использования Power Query заключается в том, что он позволяет быстро обновлять полученные данные в случае изменения исходных данных.
Например, если вы добавляете больше записей к исходным данным, вам не нужно делать все вышеупомянутое снова. Вы можете просто щелкнуть правой кнопкой мыши получившуюся таблицу, а затем нажать «Обновить».
Хотя метод Power Query работает хорошо, лучше всего использовать его, когда вы уже используете Power Query в своем рабочем процессе.
Если у вас есть просто набор данных, в котором вы хотите заполнить пустые ячейки, то более удобным будет использование специального метода Go To или метода VBA (описанного выше).
Итак, это три простых способа, которые вы можете использовать для заполнять пустые ячейки до следующего значения в Excel.
Надеюсь, вы нашли этот урок полезным.
