Home-theater-designers
При заполнении электронных таблиц функции автозаполнения Excel - самый эффективный способ сэкономить время. Большинство людей не понимают, что они могут автоматизировать многие вещи, которые они делают вручную в Excel.
Например, вы хотите применить формулу только к каждой второй или третьей строке при перетаскивании вниз до автозаполнения. Или, может быть, вы хотите заполнить все пробелы на листе. Эта статья покажет вам пять наиболее эффективных способов автоматизации заполнения столбцов.
1. Автозаполнение всех остальных ячеек в Excel
Любой, кто какое-то время использовал Excel, знает, как использовать функцию автозаполнения для автозаполнения одной ячейки Excel на основе другой.
Вы просто щелкаете и удерживаете мышь в правом нижнем углу ячейки и перетаскиваете ее вниз, чтобы применить формулу в этой ячейке к каждой ячейке под ней (аналогично копирование формул в Excel ).
В случае, когда первая ячейка представляет собой просто число, а не формулу, Excel просто автоматически заполняет ячейки, считая вверх на единицу.
Однако что, если вы не хотите применять формулу автозаполнения Excel к каждой ячейке под ней? Например, что, если вы хотите, чтобы каждая вторая ячейка автоматически заполняла и объединяла имя и фамилию, но вы хотите оставить адресные строки нетронутыми?
Как автоматически заполнить каждую другую ячейку в Excel
Вы можете сделать это, немного изменив процедуру автозаполнения. Вместо того, чтобы нажимать на первую ячейку и затем перетаскивать ее из правого нижнего угла, вы вместо этого выделите первые две ячейки. Затем поместите мышь в нижний правый угол двух ячеек, пока курсор не изменится на ' + '.
Теперь удерживайте и перетащите его вниз, как обычно.
Excel больше не заполняет автоматически каждую ячейку на основе первой ячейки, а теперь заполняет только каждую вторую ячейку в каждом блоке.
Как обрабатываются другие клетки
Что, если эти вторые ячейки не пустые? Что ж, в этом случае Excel будет применять те же правила во второй ячейке первого блока, который вы выделили, и ко всем остальным ячейкам.
Например, если во второй ячейке стоит «1», Excel автоматически заполнит все остальные ячейки, считая на 1.
Вы можете только представить, как эта гибкость может значительно повысить эффективность автоматического заполнения данных в таблицах. Это один из многих способов, с помощью которых Excel поможет вам сэкономить время при работе с большим объемом данных.
как настроить Windows 10 для игр
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 , он применит ту же формулу ко всем другим пустым ячейкам. Очистка беспорядочной электронной таблицы с помощью этой функции может быть довольно быстрой и простой.
уменьшение размера jpg
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 здесь .
Можете ли вы подключить аэродромы к Xbox One?
Автозаполнение столбцов Excel - это легкий ветерок
Как видите, подход, который вы выбираете при автоматическом заполнении столбцов, действительно может снизить вашу рабочую нагрузку. Это особенно важно, когда вы имеете дело с таблицами с тысячами столбцов или строк. После некоторого терпения и практики вы без труда сможете автоматически заполнять формулы в Excel.
Делиться Делиться Твитнуть Эл. адрес Как использовать функцию RANDBETWEEN в ExcelФункция Excel RANDBETWEEN возвращает новое случайное число при каждом пересчете вашей электронной таблицы. Вот как им пользоваться!
Читать далее Похожие темы- Продуктивность
- Таблица
- Майкрософт Эксель
- Советы по Microsoft Office
- Советы по продуктивности
Райан имеет степень бакалавра в области электротехники. Он проработал 13 лет в области автоматизации, 5 лет в ИТ, а теперь работает инженером по приложениям. Бывший управляющий редактор MakeUseOf, он выступал на национальных конференциях по визуализации данных и выступал на национальном телевидении и радио.
Ещё от Ryan DubeПодписывайтесь на нашу новостную рассылку
Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!
Нажмите здесь, чтобы подписаться