Як автоматично заповнювати послідовні дані в Excel за допомогою ручки заповнення

Заливка ручка в Excel дозволяє автоматично заповнювати список даних (чисел або тексту) в рядку або стовпці просто перетягуванням ручки. Це може заощадити багато часу при введенні послідовних даних у великі аркуші та зробити вас більш продуктивними.

Замість того, щоб вручну вводити цифри, часи або навіть дні тижня знову і знову, можна використовувати функції "Автозаповнення" (ручка заповнення або команда "Заливка" на стрічці), щоб заповнити клітинки, якщо ваші дані слідують шаблону або базуються на дані в інших клітинах. Ми покажемо, як заповнювати різні типи даних за допомогою функцій автозаповнення.

Заповніть лінійні ряди в сусідні клітини

Одним із способів використання ручки заповнення є введення ряду лінійних даних в рядок або стовпець сусідніх комірок. Лінійний ряд складається з чисел, де наступне число отримується шляхом додавання значення «крок» до числа перед ним. Найпростішим прикладом лінійного ряду є 1, 2, 3, 4, 5. Проте лінійний ряд може бути і серією десяткових чисел (1,5, 2,5, 3,5…), зменшуючи числа на два (100, 98, 96). …), Або навіть негативні числа (-1, -2, -3). У кожній лінійній серії ви додаєте (або віднімаєте) одне і те ж значення кроку.

Припустимо, ми хочемо створити стовпець послідовних чисел, збільшуючись на одиницю в кожній клітині. Можна ввести перший номер, натиснути клавішу Enter, щоб перейти до наступного рядка в цьому стовпці, і ввести наступний номер тощо. Дуже нудний і трудомісткий, особливо для великих обсягів даних. Ми збережемо час (і нудьгу), використовуючи ручку заповнення для заповнення стовпця лінійним рядком чисел. Для цього введіть 1 у першій комірці стовпця, а потім виберіть цю клітинку. Зверніть увагу на зелений квадрат у нижньому правому куті вибраної комірки? Це ручка заповнення.

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

З чорним знаком плюс над ручкою заповнення натисніть і перетягніть ручку вниз по стовпцю (або праворуч по рядку), доки не досягнете кількості комірок, які потрібно заповнити.

Коли ви відпустите кнопку миші, ви помітите, що значення було скопійовано в клітинки, за якими ви перетягнули ручку заповнення.

Чому він не заповнював лінійні серії (1, 2, 3, 4, 5 у нашому прикладі)? За замовчуванням, коли ви вводите одне число, а потім використовуєте ручку заповнення, це число копіюється до сусідніх комірок, не збільшуючись.

ПРИМІТКА. Щоб швидко скопіювати вміст комірки над поточною вибраною коміркою, натисніть Ctrl + D або скопіюйте вміст комірки ліворуч від вибраної комірки, натисніть Ctrl + R. Попереджайте, що копіювання даних із сусідньої комірки замінює будь-які дані, які наразі знаходяться у вибраній комірці.

Щоб замінити копії лінійними рядами, натисніть кнопку "Параметри автозаповнення", яка відображатиметься після завершення перетягування ручки заповнення.

За замовчуванням використовується перший параметр - Копіювати стільники. Ось чому ми закінчили з п'ятьма 1s, а не з лінійними рядами 1-5. Щоб заповнити лінійний ряд, вибираємо «Fill Series» з контекстного меню.

Інші чотири 1s замінені на 2-5, а наша лінійна серія заповнена.

Ви можете, однак, зробити це без необхідності вибору Fill Series з меню Auto Fill Options. Замість того, щоб вводити лише одне число, введіть перші два числа в перших двох клітинках. Потім виберіть ці дві клітинки і перетягніть ручку заповнення, доки не буде вибрано всі клітинки, які потрібно заповнити.

Оскільки вам було надано два фрагменти даних, воно буде знати значення кроку, яке ви хочете використовувати, і заповнити залишилися клітинки відповідним чином.

Також можна натиснути і перетягнути ручку заповнення за допомогою право клавішу миші замість лівої. Ви все ще повинні вибрати "Fill Series" з контекстного меню, але це меню автоматично відображається, коли ви перестаєте перетягувати і відпускаєте праву кнопку миші, так що це може бути зручним ярликом.

Заповніть лінійні ряди в сусідні комірки за допомогою команди заповнення

Якщо у вас виникли проблеми з використанням ручки заповнення, або ви віддаєте перевагу використанню команд на стрічці, ви можете скористатися командою "Заливка" на вкладці "Домашня сторінка", щоб заповнити серію в сусідні комірки. Команда "Заливка" також корисна, якщо ви заповнюєте велику кількість осередків.

Щоб скористатися командою "Заливка" на стрічці, введіть перше значення в клітинку і виберіть цю клітинку і всі сусідні комірки, які ви хочете заповнити (або вниз, або вгору по стовпцю, або ліворуч або праворуч по рядку). Потім натисніть кнопку "Заповнити" в розділі "Редагування" вкладки "Домашня сторінка".

Виберіть "Серія" у спадному меню.

У діалоговому вікні "Серія" виберіть, чи бажаєте ви Серія у рядках або стовпцях. У полі "Тип" виберіть "Лінійний". Пізніше ми обговоримо параметри "Зростання" та "Дата", а параметр "Автозаповнення" просто копіює значення до інших вибраних комірок. Введіть значення «Крок» або інкремент для лінійного ряду. У нашому прикладі ми збільшуємо числа в нашому ряді на 1. Натисніть "OK".

Лінійні ряди заповнюються обраними осередками.

Якщо ви маєте дійсно довгий стовпець або рядок, які потрібно заповнити лінійним рядком, можна скористатися значенням стопу у діалоговому вікні "Серія". Для цього введіть перше значення в першій комірці, яку потрібно використовувати для серії в рядку або стовпці, і знову натисніть "Заповнення" на вкладці "Домашня сторінка". На додаток до параметрів, які ми обговорювали вище, введіть значення в поле "Стоп-значення", яке ви хочете, як останнє значення в серії. Потім натисніть "OK".

У наступному прикладі ми помістимо 1 у першу комірку першого стовпця, а цифри від 2 до 20 автоматично ввійдуть у наступні 19 комірок.

Заповніть лінійну серію під час пропуску рядків

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

Щоб пропустити рядок під час заповнення лінійного ряду, введіть перший номер у першій комірці, а потім виберіть цю клітинку та одну сусідню клітинку (наприклад, наступну клітинку в стовпці).

Потім перетягніть ручку заповнення вниз (або поперек), поки не заповніть потрібну кількість осередків.

Коли ви закінчите перетягування ручки заповнення, ви побачите, що ваші лінійні ряди заповнюють всі інші рядки.

Якщо ви бажаєте пропустити більше одного рядка, просто оберіть клітинку, що містить перше значення, а потім виберіть кількість рядків, які потрібно пропустити відразу після цієї комірки. Потім перетягніть ручку заповнення над осередками, які потрібно заповнити.

Можна також пропустити стовпці під час заповнення рядків.

Заповніть формули в сусідні клітини

Можна також використовувати ручку заповнення для розповсюдження формул до сусідніх комірок. Просто виділіть клітинку, що містить формулу, яку потрібно заповнити в сусідні клітини, і перетягніть ручку заповнення вниз клітинками в стовпці або по клітинах рядка, який потрібно заповнити. Формула копіюється до інших комірок. Якщо ви використовували відносні посилання на клітинки, вони будуть змінюватися відповідно для посилання на клітинки відповідних рядків (або стовпців).

Також можна заповнити формули за допомогою команди Fill на стрічці. Просто виберіть клітинку, що містить формулу, і клітинки, які ви хочете заповнити цією формулою. Потім натисніть "Заповнити" в розділі "Редагування" вкладки "Домашня сторінка" та виберіть "Вниз", "Вправо", "Вгору" або "Вліво", залежно від того, в якому напрямку потрібно заповнити комірки.

ПРИМІТКА. Скопійовані формули не будуть повторно обчислені, якщо не ввімкнено автоматичний розрахунок робочої книги.

Можна також використовувати комбінації клавіш Ctrl + D і Ctrl + R, як обговорювалося раніше, для копіювання формул до сусідніх комірок.

Заповніть лінійну серію шляхом подвійного натискання на ручку заповнення

Ви можете швидко заповнити лінійні ряди даних у стовпці, двічі клацнувши ручку заповнення. Використовуючи цей метод, Excel заповнює клітинки лише на основі найдовшого суміжного стовпця даних на робочому аркуші. Суміжний стовпець у цьому контексті - це будь-який стовпець, з яким Excel стикається праворуч або ліворуч від заповненого стовпця, поки не буде досягнуто порожній стовпець. Якщо стовпці безпосередньо на обох сторонах вибраного стовпця порожні, ви не можете використовувати метод подвійного клацання для заповнення комірок у стовпці. Крім того, за замовчуванням, якщо деякі клітинки в діапазоні клітин, які ви заповнюєте, вже мають дані, заповнюються лише порожні клітинки над першою коміркою, що містить дані. Наприклад, на зображенні нижче, є значення в комірці G7, тому, коли ви двічі клацнете на ручці заповнення на комірці G2, формула копіюється тільки через клітинку G6.

Заповнити серію зростання (геометричний зразок)

До цих пір ми обговорювали заповнення лінійних рядів, де кожне число в серії обчислюється шляхом додавання значення кроку до попереднього числа. У серії росту або геометричному шаблоні наступне число обчислюється за допомогою множення попереднє число за значенням кроку.

Існує два способи заповнення ряду зростання, ввівши перші два числа і ввівши перше число і значення кроку.

Метод перший: введіть перші два числа в серії росту

Щоб заповнити ряд росту, використовуючи перші два числа, введіть два числа в перші дві клітинки рядка або стовпця, які потрібно заповнити. Клацніть правою кнопкою миші та перетягніть ручку заповнення на стільки клітин, скільки хочете заповнити. Коли ви закінчите перетягування ручки заповнення над клітинами, які потрібно заповнити, виберіть "Тенденція зростання" у контекстному меню, яке автоматично відображається.

ПРИМІТКА. Для цього методу потрібно ввести два числа. Якщо ви цього не зробите, параметр Trend Trend буде змінено.

Excel знає, що значення кроку дорівнює 2 від двох чисел, які ми ввели в перших двох осередках. Отже, кожне наступне число обчислюється шляхом множення попереднього на 2.

Що робити, якщо ви хочете почати з числа, відмінного від 1, використовуючи цей метод? Наприклад, якщо ви хочете почати вищезгадані серії на 2, ви введете 2 і 4 (тому що 2 × 2 = 4) у перших двох клітинках. Excel зрозуміє, що значення кроку дорівнює 2 і продовжує серію зростання з 4, помноживши кожне наступне число на 2, щоб отримати наступну рядок у рядку.

Другий метод: Введіть перший номер у серії росту та вкажіть значення кроку

Щоб заповнити серію росту на основі одного числа і значення кроку, введіть перше число (воно не повинно бути 1) у першій комірці і перетягніть ручку заповнення над клітинами, які потрібно заповнити. Потім у спливаючому меню, яке автоматично відображається, виберіть «Серія».

У діалоговому вікні Серія виберіть, чи потрібно заповнювати Серію рядками або стовпцями. У розділі Тип виберіть: "Зростання". У полі "Значення кроку" введіть значення, яке потрібно множити, щоб отримати наступне значення. У нашому прикладі ми хочемо помножити кожний номер на 3. Натисніть "OK".

Серія росту заповнюється у вибраних клітинах, причому кожне наступне число втричі перевищує попереднє число.

Заповніть серію за допомогою вбудованих елементів

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

Наступне зображення показує деякі з рядків, які вбудовані в Excel, розширені по рядках. Елементи, виділені жирним шрифтом і червоним кольором, є початковими значеннями, які ми ввели, а решта елементів у кожному рядку - розширені значення рядків. Ці вбудовані серії можна заповнити за допомогою ручки заповнення, як ми раніше описали для лінійних і ростових серій. Просто введіть початкові значення та виберіть їх. Потім перетягніть ручку заповнення на потрібні клітинки, які потрібно заповнити.

Заповніть серію дат, використовуючи команду заповнення

Під час заповнення серії дат можна скористатися командою "Заливка" на стрічці, щоб вказати інкремент для використання. Введіть першу дату у вашій серії у клітинку та виберіть цю клітинку та клітинки, які потрібно заповнити. У розділі Редагування на вкладці Домашня сторінка натисніть кнопку "Заповнення", а потім виберіть "Серія".

У діалоговому вікні "Серія" параметр "Серія в" автоматично вибирається відповідно до вибраних клітинок. Тип також автоматично встановлюється на дату. Щоб вказати інкремент, який буде використовуватися при заповненні серії, виберіть одиницю Дата (День, День тижня, Місяць або Рік). Вкажіть значення кроку. Ми хочемо заповнити серію з кожним днем ​​тижня, тому ми вводимо значення 1 як значення кроку. Натисніть "OK".

Серія заповнена датами, які є лише у будні дні.

Заповніть серію за допомогою спеціальних елементів

Можна також заповнити серію власними спеціальними елементами. Скажімо, ваша компанія має офіси в шести різних містах, і ви часто використовуєте ці назви міст у таблицях Excel. Ви можете додати цей список міст як спеціальний список, який дозволить вам використовувати ручку заповнення для заповнення серії після введення першого елемента. Щоб створити власний список, перейдіть на вкладку "Файл".

На екрані за лаштунками натисніть "Опції" у списку елементів ліворуч.

Натисніть "Додатково" у списку елементів з лівого боку діалогового вікна Параметри Excel.

У правій панелі перейдіть до розділу Загальні та натисніть кнопку "Редагувати власні списки".

У діалоговому вікні "Спеціальні списки" є два способи заповнити ряд користувацьких елементів. Серію можна засновувати на новому списку елементів, створених безпосередньо в діалоговому вікні "Спеціальні списки", або в існуючому списку вже на робочому аркуші поточної робочої книги. Ми покажемо вам обидва методи.

Метод перший: заповнення користувацької серії на основі нового списку елементів

У діалоговому вікні "Спеціальні списки" переконайтеся, що у вікні "Спеціальні списки" вибрано "Новий список". Клацніть у вікні «Список записів» і введіть елементи у ваші власні списки, один елемент у рядок. Обов'язково введіть елементи в тому порядку, в якому ви бажаєте їх заповнити в клітинки. Потім натисніть "Додати".

Спеціальний список додається до поля "Спеціальні списки", де його можна вибрати, щоб ви могли редагувати його, додаючи або видаляючи елементи з поля "Список записів" і знову натискаючи "Додати", або видалити список, натиснувши "Видалити". Натисніть "OK".

Натисніть кнопку "OK" у діалоговому вікні параметрів Excel.

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

Другий метод: заповнення користувацької серії на основі існуючого списку елементів

Можливо, ви зберігаєте свій власний список на окремому аркуші робочої книги. Ви можете імпортувати ваш список з робочого аркуша в діалогове вікно Custom Lists. Щоб створити власний список на основі існуючого списку на робочому аркуші, відкрийте діалогове вікно Custom Lists і переконайтеся, що у вікні Custom list вибрано NEW LIST, як і в першому методі. Однак для цього методу натисніть кнопку діапазону комірок праворуч від поля "Імпортувати список з комірок".

Виберіть вкладку для робочого аркуша, який містить користувацький список у нижній частині вікна Excel. Потім виберіть комірки, що містять елементи у вашому списку. Ім'я робочого аркуша та діапазон комірок автоматично вводяться у вікно редагування власних списків. Натисніть кнопку діапазону стільників знову, щоб повернутися до повного діалогового вікна.

Тепер натисніть "Імпортувати".

Користувальницький список додається до поля "Спеціальні списки", і ви можете вибрати його і відредагувати список у вікні "Список записів", якщо хочете. Натисніть "OK". Ви можете заповнити клітинки за допомогою власного списку за допомогою ручки заповнення, як це було зроблено вище з першого методу.

Ручка заповнення в Excel є дуже корисною функцією, якщо ви створюєте великі таблиці, які містять багато послідовних даних. Ви можете заощадити багато часу і нудьги. Щасливе наповнення!