Содержание
Функции автозаполнения Excel предлагают наиболее эффективные способы экономии времени при заполнении электронных таблиц.
Большинство людей не понимают, что многие вещи, которые они делают вручную, можно автоматизировать. Например, может быть, вы хотите применить формулу только к каждой второй или третьей строке, когда вы перетаскиваете вниз до автозаполнения. Или, может быть, вы хотите заполнить все пробелы в листе.
В этой статье мы покажем вам, как выполнить пять самых эффективных автоматизаций для автозаполнения столбцов.
1. Заполните все остальные ячейки
В основном любой, кто использовал Excel в течение некоторого времени, знает, как использовать функцию автозаполнения.
Вы просто нажимаете и удерживаете мышь в правом нижнем углу ячейки и перетаскиваете ее вниз, чтобы применить формулу в этой ячейке к каждой ячейке под ней.
В случае, когда первая ячейка является просто числом, а не формулой, Excel просто автоматически заполнит ячейки, посчитав вверх на единицу.
Однако что делать, если вы не хотите применять формулу автозаполнения к каждой ячейке под ней? Например, что если вы хотите, чтобы каждая другая ячейка объединяла имя и фамилию, но вы хотите оставить адресные строки без изменений?
Применить формулу к каждой другой ячейке
Вы можете сделать это, слегка изменив процедуру автозаполнения. Вместо того, чтобы щелкнуть первую ячейку, а затем перетащить ее из нижнего правого угла, вместо этого вы выделите первые две ячейки. Затем поместите мышь в нижний правый угол двух ячеек, пока курсор не изменится на «+».
Теперь удерживайте и перетащите его вниз, как обычно.
Вы заметите, что теперь вместо автозаполнения каждой ячейки, Excel выполняет автозаполнение только каждой второй ячейки в каждом блоке.
Как обрабатываются другие клетки
Что если эти вторые ячейки не пустые? Ну, в этом случае Excel будет применять те же правила во второй ячейке первого блока, который вы выделили, также и ко всем другим ячейкам. Например, если во второй ячейке есть «1», то Excel будет автоматически заполнять все остальные ячейки, считая на 1.
Вы можете просто представить, как эта гибкость может значительно повысить эффективность автоматического заполнения данных в листах. Это один из многих способов, которыми Excel помогает вам сэкономить время
имея дело с большим количеством данных.
2. Автозаполнение до конца данных
При работе с рабочими листами Excel в корпоративной среде люди часто сталкиваются с большими листами.
Достаточно просто перетащить курсор мыши сверху вниз из набора от 100 до 200 строк, чтобы автоматически заполнить этот столбец. Но что, если на самом деле в таблице 10 000 или 20 000 строк? Перетаскивание курсора мыши вниз на 20000 строк займет очень много времени.
Есть очень быстрый способ сделать это более эффективным. Вместо того, чтобы перетаскивать весь столбец вниз, просто удерживайте клавишу Shift на клавиатуре. Теперь вы заметите, когда вы помещаете указатель мыши в нижний правый угол ячейки, вместо значка «плюс» это значок с двумя горизонтальными параллельными линиями.
Теперь все, что вам нужно сделать, это дважды щелкнуть по этому значку, и Excel автоматически автоматически заполнит весь столбец, но только там, где в соседнем столбце действительно есть данные.
Этот трюк может сэкономить бесчисленные часы
впустую пытаясь перетащить мышь вниз через сотни или тысячи строк.
3. Заполните пробелы
Представьте, что вам поручено очистить электронную таблицу Excel, и ваш босс хочет, чтобы вы применили определенную формулу
каждой пустой ячейке в столбце. Вы не можете видеть какой-либо предсказуемый шаблон, поэтому вы не можете использовать трюк автозаполнения «каждый второй х» выше. Плюс такой подход уничтожит все существующие данные в столбце. Что ты можешь сделать?
Ну, есть еще один трюк, который вы можете использовать только для заполнения пустые клетки с чем угодно.
На приведенном выше листе ваш босс хочет, чтобы вы заполнили любую пустую ячейку строкой «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 — это просто
Как вы можете видеть, подход, используемый вами при автозаполнении столбцов, может реально снизить нагрузку. Это особенно верно, когда вы имеете дело с массивными электронными таблицами с тысячами столбцов или строк.
