5 уловок с автозаполнением в Microsoft Excel для более быстрого создания электронных таблиц

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

Например, вы хотите применить формулу только к каждой второй или третьей строке при перетаскивании вниз до автозаполнения. Или, может быть, вы хотите заполнить все пробелы на листе. Эта статья покажет вам пять наиболее эффективных способов автоматизации заполнения столбцов.

1. Автозаполнение всех остальных ячеек в Excel

Любой, кто какое-то время использовал Excel, знает, как использовать функцию автозаполнения для автозаполнения одной ячейки Excel на основе другой.

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

В случае, когда первая ячейка представляет собой просто число, а не формулу, Excel просто автоматически заполняет ячейки, считая вверх на единицу.

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

Как автоматически заполнить каждую другую ячейку в Excel

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

Теперь удерживайте и перетащите его вниз, как обычно.

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

Как обрабатываются другие клетки

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

Например, если во второй ячейке стоит «1», Excel автоматически заполнит все остальные ячейки, считая на 1.

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

2. Автозаполнение до конца данных в Microsoft Excel

Одна вещь, с которой люди часто сталкиваются при работе с листами Excel в корпоративной среде, – это большие листы.

Достаточно просто перетащить курсор мыши сверху вниз для набора из 100–200 строк, чтобы заполнить этот столбец автоматически. Но что, если на самом деле в электронной таблице 10 000 или 20 000 строк? Перетаскивание курсора мыши вниз через 20 000 строк займет много времени.

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

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

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

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

3. Заполните пустые поля.

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

Вы не можете увидеть какой-либо предсказуемый шаблон, поэтому вы не можете использовать описанный выше трюк с автозаполнением «все остальные x». Кроме того, такой подход уничтожит все существующие данные в столбце. Что ты можешь сделать?

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

На листе выше ваш босс хочет, чтобы вы заполнили любую пустую ячейку строкой «N / A». На листе, состоящем всего из нескольких строк, это будет простой ручной процесс. Но на листе с тысячами строк на это у вас уйдет целый день.

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

В следующем окне выберите Бланки .

В следующем окне вы можете ввести формулу в первую пустую ячейку. В этом случае вы просто наберете N / A, а затем нажмите Ctrl + Enter, чтобы то же самое применилось к каждой найденной пустой ячейке.

Если вы хотите, вместо «N / A» вы можете ввести формулу в первую пустую ячейку (или щелкнуть предыдущее значение, чтобы использовать формулу из ячейки чуть выше пустой).

Когда вы нажимаете Ctrl + Enter , он применяет ту же формулу ко всем другим пустым ячейкам. Очистка беспорядочной электронной таблицы с помощью этой функции может быть довольно быстрой и простой.

4. Заполнить макросом предыдущего значения

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

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

Чтобы создать макрос, щелкните пункт меню « Разработчик» и щелкните значок « Макросы» .

Назовите макрос и нажмите кнопку « Создать макрос». Откроется окно редактора кода. Вставьте следующий код в новую функцию.

 FirstColumn = InputBox("Please enter the column letter.")
 FirstRow = InputBox("Please enter the row number.")
 LastRow = Range(FirstColumn & "65536").End(xlUp).Row
 For i = FirstRow To LastRow
 If Range(FirstColumn & i).Value = "" Then
 Range(FirstColumn & (i - 1)).Copy Range(FirstColumn & i)
 End If
 Next i

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

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

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

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

5. Макрос итерационных вычислений

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

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

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

 FirstColumn = InputBox("Please enter the column letter.")
 FirstRow = InputBox("Please enter the first row number.")
 LastRow = InputBox("Please enter the last row number.")
 For i = FirstRow To LastRow
 Range(FirstColumn & i).Value = 5000 + (Range(FirstColumn & (i - 1)).Value * 0.1)
 Next i

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

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

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

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

Автозаполнение столбцов Excel очень просто

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