Автозаполнение ячеек в Эксель

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

Стоит отметить, что подобные инструменты отсутствуют в программе Microsoft Word. Некоторые пользовали прибегают к хитрости. Заполняют таблицу нужными значениями в Экселе, а затем переносят их в Ворд. Вы можете делать то же самое.

Относительные и абсолютные ссылки в Excel

При протягивании важно помнить, что именно содержится в протягиваемых ячейках. Если в ячейке значение (число, текст, дата), то при протягивании в новые ячейки попадёт это значение (и формат). Если в ячейке была формула, то значение в новой ячейке будет зависеть от того, какой тип ссылок был использован. При протягивании все обычные ссылки на другие ячейки автоматически смещаются, это относительная ссылка. Чтобы ссылка не смещалась по ячейкам (т.е. чтобы сохранить ссылку на конкретную ячейку, строку или столбец), в формуле добавляется символ $ перед адресом строки и/или столбца, который необходимо оставить неизменным. Если символ $ добавляется в обоих случаях, то это абсолютная ссылка на конкретную ячейку. Если меняется только строка или только столбец, то такая ссылка является смешанной.

Практическая работа: абсолютные и относительные ссылки

Заполните столбец «Постоянные расходы»: обратите внимание на формулу в ячейке Е11. Протяните эту ячейку до декабря. Посмотрите на формулу в любой из ячеек Е12:Е21. Увидите, что адрес ячейки в формуле каждый раз новый. Это правильное использование относительной ссылки.

Теперь введите в ячейку D10 формулу для расчёта маржинальной прибыли в январе: «=C10*C7/(1+C7)». Маржинальная прибыль для января посчитана. Протяните эту формулу до декабрьской ячейки: увидите, что использование относительной ссылки в данном случае некорректно (выделите любую ячейку от D11 до D21, нажмите мышью на строку формул или нажмите F2). Программа верно берёт для расчёта значение выручки из столбца С, но значение маржинальной прибыли сместилось из-за протягивания, и теперь во всех ячейках, кроме первой, неверные формулы. Нам нужно, чтобы в исходной формуле адрес ячейки С10 менялся при протягивании, а адрес С7 — нет. Вернитесь в ячейку D10 и исправьте формулу следующим образом: «=C10*C$7/(1+C$7)». Значение в ячейке не изменилось, так как для этой ячейки все виды ссылок совпадут. Протяните новую формулу до декабрьской ячейки, теперь суммы рассчитаны верно. Здесь мы видим пример использования смешанной ссылки.

Читайте так же:

Можно ли заменить стекло на планшете

Добавьте суммирование годовой выручки: выделите ячейку В22, нажмите кнопку меню Главная — Сумма (иконка с символом ∑), либо клавиши Alt+=, в ячейку автоматически будет подставлена формула суммирования СУММ. Проверьте правильность диапазона суммирования В10:В21 и нажмите Enter, будет посчитана итоговая выручка. Протяните эту формулу вправо на три соседние ячейки. Добавьте расчёт прибыли до налогообложения в ячейку F10 и протяните её до декабря. �?тоговые суммы рассчитаются автоматически.

Теперь рассмотрим работу смешанных ссылок по столбцам и горизонтальное протягивание. В следующем задании в ячейку С32 поставьте формулу «=В32*(1+D29)». Для данной ячейки формула будет верная, но при протягивании вправо относительная ссылка испортит расчёт. Добавьте символ $ перед буквой столбца: «=В32*(1+$D29)» и протяните формулу горизонтально.

Рассмотрите последнее задание со всеми вариантами адресации, кроме относительной. В нём рассчитывается общая выручка, исходя из заданного базового объёма производства, средней цены за единицу в четырёх сценариях увеличения объёма производства. Попробуйте корректно написать формулу в ячейке С45 и заполнить всю остальную таблицу протягиванием: сначала протянуть ячейку горизонтально (или вертикально), потом протянуть всю строку (или столбец) на всю таблицу. Ссылка на ячейку с ценой С38 должна быть абсолютной как по строкам, так и по столбцам, поэтому в формуле эта ячейка будет такой: $C$38.

Заполнение с формулами

В excel очень удобно проводить расчеты с использованием формул. Если таблица состоит из списка данных, которые нужно обработать с использованием математических функций, автозаполнение придет на помощь.

Для одной ячейки задаете формулу.

Заполняете ячейки, потянув за маркер вниз.

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

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

Тонкости функции Автозаполнения в MS Excel

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

Читайте так же:

Как в ворде вернуть стандартные настройки

Как пользоваться автозаполнением или маркер автозаполнения

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

По умолчанию Excel продолжит заполнение данными ячейки в зависимости от имеющихся в выделенных ячейках/ячейке данных, например, если просто растянуть число «1», то остальные ячейки заполнятся единицами, если выделить две ячейки с числами «1» и «2», то Excel продолжит ряд данных: «3», «4», «5» и т.д.

Можно ли как-то повлиять на виды автозаполнения?

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

Аналогичного результата (имеется ввиду изменения режима автозаполнения) можно добиться, если воспользоваться командой «Заполнить» из вкладки «Главная», группа «Редактирование», но, в это случае, сначала надо выделить область на листе, которая будет изменяться, а потом воспользоваться соответствующей командой.

А что, если необходимо продолжить текстовый перечень или даты?

Автозаполнение отлично работает с датами, при использовании простого растягивания с помощью маркера заполнения, изменение идет аналогично числам.

А если воспользоваться правой клавишей мышки, или командой «Заполнить», то появляются дополнительные возможности для заполнения.

Примечание: вид отображения дат в Excel зависит от региональных настроек на пользовательском компьютере.

Автозаполнение текста в Excel

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

Однако, пользователю доступна возможность задавать такие списки, а также просматривать существующие. Настройка списков находится по адресу: Файл / Параметры Excel / Дополнительно / Группа Общие / Изменить списки…

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

Пример своего списка для автозаполнения в Excel Результат работы пользовательского списка автозаполнения

Хитрые приемы, о которых мало кто знает.

Вот так работает автозаполнение в Excel, в завершение, пару хитростей работы с автозаполнением.

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

Читайте так же:

Где искать настройки браузера яндекс

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

Например, ячейка A2 заполнена КТЭ00251, и вам необходимо заполнить ячейки ниже тем же содержимым этой ячейки. Когда мы перетаскиваем дескриптор автозаполнения ячейки A2 вниз, число автоматически увеличивается (см. Снимок экрана ниже). Итак, есть ли способ предотвратить увеличение числа при использовании автозаполнения в Excel. Нижеприведенный метод поможет вам это сделать.

Предотвращение увеличения числа при использовании автозаполнения по горячей клавише
Предотвращение увеличения числа с помощью функции заполнения
Предотвратить увеличение числа с помощью Kutools for Excel

Предотвращение увеличения числа при использовании автозаполнения по горячей клавише

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

В нашем случае выберите ячейку A2; удерживайте Ctrl клавиша, которая изменит курсор на двойной крест; а затем перетащите дескриптор автозаполнения вниз к ячейкам по мере необходимости.

И теперь нижние ячейки заполняются содержимым первой ячейки ровно без автоматического увеличения числа. См. Снимок экрана ниже:

Легко заполнять пустые ячейки значениями выше / ниже / справа / слева в Excel

Kutools для Excel Заполнить пустые ячейки Утилита может помочь пользователям Excel легко заполнить все пустые ячейки значениями выше / ниже / справа / слева в указанном диапазоне. Эта утилита также поддерживает заполнение пустых ячеек фиксированной текстовой строкой или числом. Полнофункциональная бесплатная 30-дневная пробная версия!

Предотвращение увеличения числа с помощью функции заполнения

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

Выделите ячейку с содержимым под пустыми ячейками и нажмите Главная > Заполнять > вниз. Смотрите скриншот:

И теперь внизу пустые ячейки заполняются содержимым первой ячейки ровно без автоматического увеличения числа. Смотрите скриншот:

Предотвратить увеличение числа с помощью Kutools for Excel

Если у вас установлен Kutools for Excel, вы можете применить его Заполнить пустые ячейки утилита, позволяющая легко предотвратить увеличение числа в Excel. Пожалуйста, сделайте следующее:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Читайте так же:

Как ввести переменную в excel

1. Выберите ячейку с содержимым под пустыми ячейками и щелкните Kutools > Вставить > Заполнить пустые ячейки.

2. В открывшемся диалоговом окне «Заполнить пустые ячейки» установите флажок На основе ценностей вариант в Заполнить раздел, проверьте вниз вариант в Параметры и нажмите Ok кнопку.

Внимание: Это Заполнить пустые ячейки Утилита также поддерживает заполнение содержимого ячейки до левых пустых ячеек или над пустыми ячейками. См. Снимок экрана ниже:

Автоматическая нумерация в Excel

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

Как сделать автоматический номер в Excel?

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

Теперь мы проверили, что автозаполнение включено, есть три метода автоматической нумерации в Excel:

Заполните столбец серией цифр.
Использование функции row ().
Использование функции offset ().

# 1 — В файле перейдите на вкладку параметров перехода.

# 2 — В расширенном разделе под параметрами редактирования установите флажок Включить дескриптор заполнения и перетаскивание ячейки в Excel.

3 основных способа получить автоматическую нумерацию в Excel

Есть несколько способов получить автоматическую нумерацию в Excel.

Заполните столбец серией цифр.
Используйте функцию Row ()
Использовать функцию смещения ()

Разберем все перечисленные способы на примерах.

# 1 — Заполните столбец серией чисел

У нас есть следующие данные,

Мы попытаемся вставить автоматические числа в столбец A Excel.

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

Мы выполнили нумерацию 1 в ячейке A2 и 2 в ячейке A3, чтобы начать узор. Теперь выберите начальные значения, т.е. ячейки A2 и A3.

Читайте так же:

Можно ли в пдф добавить страницу

Указатель (точка) в выбранной ячейке отображается стрелкой; щелкните по нему и перетащите в нужный диапазон, то есть в ячейку A11.

Теперь у нас есть последовательная нумерация наших данных.

# 2 — Используйте функцию ROW ()

Мы будем использовать те же данные, чтобы продемонстрировать последовательную нумерацию функцией row ().

Ниже наши данные,

В ячейке A2 или там, где мы хотим начать нашу автоматическую нумерацию в Excel, мы выбираем конкретную ячейку.

Введите = СТРОКА () в ячейку A2 и нажмите клавишу ВВОД.

Это дало нам нумерацию от числа 2, потому что функция строки выдает номер для текущей строки.

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

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

Теперь у нас есть автоматическая нумерация данных в Excel с помощью функции row ().

# 3 — Использование функции Offset ()

Мы можем выполнить автоматическую нумерацию в Excel, также используя функцию Offset ().

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

Как мы видим, я удалил текст, записанный в ячейке A1 «Серийный номер», поскольку при использовании функции смещения ссылка должна быть пустой.

На приведенном выше снимке экрана показаны аргументы функции, используемые в функции смещения.

Использовать автозаполнение для дублирования данных в ячейке

Вот как выполнить тот же эффект, что и команда Fill Down, но вместо этого с функцией автозаполнения:

Введите число в клетка в электронной таблице Excel.

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

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

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

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

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

голоса

Рейтинг статьи