5 хитростей автозаполнения в Excel, чтобы быстрее создавать электронные таблицы

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

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

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

Заполните все остальные ячейки

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

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

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

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

Применить формулу к каждой другой ячейке

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

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

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

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

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

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

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

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

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

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

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

впустую пытаясь перетащить мышь вниз через сотни или тысячи строк.

Заполните пробелы

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

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

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

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

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

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

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

Эта особенность может сделать очистку грязной таблицы очень быстрой и простой.

Заполните предыдущим значением макрос

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

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

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

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

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

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

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

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

Итеративный расчет — это расчет, выполненный на основе результатов предыдущего ряда.

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

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

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

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

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

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

Вы можете узнать больше об этом в нашей статье об автоматизации ваших электронных таблиц

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

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

Нередко нужно произвести сложение чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75 000 руб. Для этого используем данные таблицы зависимости комиссионных от стоимости имущества (рис. 8).

Постоянная ячейка в формуле Excel — Формула деления в Экселе — Как в офисе.

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

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

Последняя заполненная ячейка в EXCEL. Примеры и описание

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

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

5 хитростей автозаполнения в Excel, чтобы быстрее создавать электронные таблицы

СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.

Диапазон ячеек представляет собой некоторую прямоугольную область рабочего листа и однозначно определяется адресами ячеек, расположенными в противоположных углах диапазона. Разделённые символом “:” (двоеточие), эти две координаты составляют адрес диапазона. Например, чтобы получить сумму значений ячеек диапазона C3:D7, используйте формулу =СУММ(C3:D7).

Как в excel поставить формулу на весь столбец. Использование вычисляемых столбцов в таблице Excel Online

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

СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .

Автоматизация расчётов в электронных таблицах Excel

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

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

10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только

В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.

В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до D, его адрес записывается в виде В:D. Аналогично если диапазон целиком состоит из строк с 6-й по 15-ю, то он имеет адрес 6:15. Кроме того, при записи формул можно использовать объединение нескольких диапазонов или ячеек, разделяя их символом “;” (точка с запятой), например C3:D7; E5;F3:G7.

Создание формулы в вычисляемом столбце в таблице

1. выделяем диапазон
   • мышью, если это столбец, строка или лист
   • сочетаниями Ctrl+Shift+стрелки или Shift+стрелки, если ячейки или диапазоны ячеек
2. копируем сочетанием Ctrl+C
3. перемещаемся стрелками к диапазону, куда нужно вставить данные и/или Ctrl+Tab для перехода в другую книгу
4. вызываем контекстное меню клавишей на клавиатуре, иногда ее нет, но обычно на клавиатурах она есть, рядом с правым ALT
5. Стрелками перемещаемся к команде (Вниз-вниз-вправо)
6. Клавиша Enter

Обратите внимание, что в качестве диапазона для проверки условия мы выбираем интервал ячеек А2:А6 (стоимость имущества), а в качестве диапазона суммирования — В2:В6 (комиссионные), при этом условие имеет вид (>75000). Результат нашего расчёта составит 27 000 руб.