Перегляд відео - заповнення в Excel
Дуже часто ви можете зіткнутися з набором даних, де лише одна клітинка заповнена даними, а клітинки під нею порожні до наступного значення.
Щось, як показано нижче:
Хоча цей формат працює для деяких людей, проблема такого типу даних полягає в тому, що ви не можете використовувати його для створення зведених таблиць або використання їх у обчисленнях.
І це легко виправити!
У цьому уроці я покажу вам, як швидко заповнювати клітинки в Excel до наступного заповненого значення.
Ви можете легко це зробити, використовуючи простий метод переходу до спеціального діалогового вікна, VBA або Power Query.
Тож почнемо!
Спосіб 1 - заповніть, використовуючи Перейти до спеціальної + формули
Припустимо, у вас є набір даних, як показано нижче, і ви хочете заповнити дані у стовпцях А та В.
У стовпці В мета заповнювати «Принтер» до останньої порожньої клітинки під ним, а потім, коли запускається «Сканер», потім заповнювати «Сканер» у клітинках нижче, доки клітини не будуть порожніми.
Нижче наведено кроки, які потрібно використати, щоб вибрати усі порожні клітинки та заповнити їх за допомогою простої формули:
- Виберіть дані, які потрібно заповнити (A1: D21 у нашому прикладі)
- Перейдіть на вкладку «Домашня сторінка»
- У групі «Редагування» натисніть на піктограму «Знайти та вибрати» (у спадному меню з’являться інші параметри)
- Натисніть на опцію "Перейти"
- У діалоговому вікні Перейти, що відкриється, натисніть кнопку Спеціальне. Відкриється діалогове вікно «Перейти до спеціального»
- У діалоговому вікні «Перейти до спеціального» натисніть «Пробіли»
- Натисніть OK
Вищезазначені кроки виділять усі порожні клітинки в наборі даних (як показано нижче).
У пустих клітинках, які вибрано, можна помітити, що одна клітинка світліша за решту виділення. Ця клітина є активною клітиною, у яку ми збираємося ввести формулу.
Не турбуйтесь про те, де знаходиться клітинка у виділенні, оскільки наш метод у будь -якому випадку спрацює.
Нижче наведено кроки для заповнення даних у вибраних порожніх клітинках:
- Натисніть клавішу з рівністю (=) на клавіатурі. Це додасть знак рівності до активної комірки
- Натисніть клавішу зі стрілкою вгору. Це додасть посилання на клітинку клітинки над активною клітиною. Наприклад, у нашому випадку B3 - це активна клітинка, і коли ми виконуємо ці два кроки, вона вводить = B2 у клітинку
- Утримуйте клавішу Control і натисніть клавішу Enter
Наведені вище кроки автоматично вставлять усі порожні клітинки зі значенням над ними.
Хоча це може виглядати як забагато кроків, але коли ви освоїте цей метод, ви зможете швидко заповнити дані в Excel за кілька секунд.
Тепер є ще дві речі, на які потрібно звернути увагу при використанні цього методу.
Перетворення формул у значення
По -перше, переконайтеся, що ви перетворюєте формули у значення (щоб у вас були статичні значення, і речі не зіпсувалися б у разі зміни даних у майбутньому).
Нижче наведено відео, яке покаже вам, як швидко перетворити формули на значення в Excel.
Змініть формат дати
Якщо ви використовуєте дати у своїх даних (як я в наведених прикладах), ви помітите, що заповнені значення у стовпці дати-це цифри, а не дати.
Якщо вихідні дані у стовпці дати мають потрібний формат дати, ви добре, і вам нічого більше не потрібно робити.
Але якщо вони не в правильному форматі дати, вам потрібно внести незначні зміни. Хоча у вас є правильні значення, вам просто потрібно змінити формат, щоб він відображався як дата у комірці.
Нижче наведено кроки для цього.
- Виберіть стовпець з датами
- Натисніть вкладку Домашня сторінка
- У групі Число натисніть спадне меню Формат, а потім виберіть формат дати.
Якщо вам потрібно час від часу виконувати це заповнення, я рекомендую вам скористатися цією спеціальною технікою переходу та формулою.
Хоча він має кілька кроків, він простий і дає результат прямо у вашому наборі даних.
Але якщо вам доводиться робити це досить часто, то я пропоную вам подивитися на методи VBA та Power Query, розглянуті далі
Спосіб 2 - заповнення за допомогою простого коду VBA
Ви можете використовувати дійсно простий код макросу VBA, щоб швидко заповнити клітинки в Excel до останнього порожнього значення.
Вам потрібно лише один раз додати код VBA до файлу, а потім ви зможете легко повторно використовувати код кілька разів в одній книзі або навіть у кількох книгах у вашій системі.
Нижче наведено код VBA, який пройде через кожну клітинку у виділенні та заповнить усі порожні клітинки:
'Цей код VBA створено Sumit Bansal з TrumpExcel.com Sub FillDown () Для кожної клітинки у виділеному клітинка = "" Тоді клітинка.
У наведеному вище коді використовується цикл For для проходження кожної комірки у виділенні.
У циклі For я використовував умову If-Then, яка перевіряє, порожня клітинка чи ні.
У разі, якщо комірка порожня, вона заповнюється значенням з комірки вище, а якщо вона не порожня, то цикл for просто ігнорує комірку і переходить до наступної.
Тепер, коли у вас є код VBA, дозвольте мені показати вам, де розмістити цей код в Excel:
- Виберіть дані, які потрібно заповнити
- Перейдіть на вкладку Розробник
- Натисніть на значок Visual Basic (або ви можете скористатися комбінацією клавіш ALT + F11). Це відкриє редактор Visual Basic в Excel
- У редакторі Visual Basic ліворуч у вас буде Провідник проектів. Якщо ви не бачите його, натисніть опцію «Перегляд» у меню, а потім натисніть Провідник проектів
- Якщо у вас відкрито кілька робочих книг Excel, Project Explorer покаже вам усі назви робочих книг. Знайдіть назву робочої книги, де у вас є дані.
- Клацніть правою кнопкою миші будь-який об’єкт у книзі, перейдіть до пункту Вставити, а потім клацніть на Модуль. Це додасть новий модуль для цієї книги
- Двічі клацніть «Модуль», який ви щойно вставили на кроці вище. Відкриється вікно коду для цього модуля
- Скопіюйте та вставте код VBA у вікно коду
- Поставте курсор у будь -якому місці коду, а потім запустіть макрокод, натиснувши зелену кнопку на панелі інструментів або скориставшись комбінацією клавіш F5
Наведені вище кроки запускатимуть код VBA, і ваші дані будуть заповнені.
Якщо ви хочете знову використовувати цей код VBA у майбутньому, вам потрібно зберегти цей файл як книгу Excel з підтримкою макросів (з розширенням .XLSM)
Інше, що ви можете зробити, це додати цей макрос до панелі швидкого доступу, яка завжди відображається, і ви матимете доступ до цього макросу одним натисканням кнопки (у книзі, де у вас є код у бекенді).
Отже, наступного разу, коли у вас будуть дані, які потрібно заповнити, все, що вам потрібно зробити, це зробити вибір, а потім натиснути кнопку макросу на панелі швидкого доступу.
Ви також можете додати цей макрос до книги «Особистий макрос», а потім використовувати його у будь -якій книзі у вашій системі.
Натисніть тут, щоб дізнатися більше про особисті книги з макросами та про те, як додати до них код.
Спосіб 3 - заповнення за допомогою Power Query
Power Query має вбудовану функціональність, яка дозволяє заповнювати її одним натисканням кнопки.
Використовувати Power Query рекомендується, коли ви все одно використовуєте його для перетворення даних або об’єднання даних з кількох аркушів чи кількох книг.
Як частину існуючого робочого процесу, ви можете скористатися опцією заповнення в Power Query, щоб швидко заповнити порожні клітинки.
Для використання Power Query рекомендується, щоб ваші дані були у форматі таблиці Excel. Якщо ви не можете перетворити свої дані в таблицю Excel, вам доведеться створити іменований діапазон для даних, а потім використовувати цей іменований діапазон у Power Query.
Нижче у мене є набір даних, який я вже перетворив на таблицю Excel. Ви можете зробити це, вибравши набір даних, перейшовши на вкладку Вставка, а потім натиснувши на піктограму Таблиця.
Нижче описано, як використовувати Power Query для заповнення даних до наступного значення:
- Виберіть будь -яку клітинку в наборі даних
- Перейдіть на вкладку Дані
- У групі Отримати та перетворити дані натисніть «З аркуша». Відкриється редактор Power Query. Зауважте, що порожні клітинки показуватимуть значення "null" у Power Query
- У редакторі Power Query виберіть стовпці, які потрібно заповнити. Для цього утримуйте клавішу Control, а потім клацніть на заголовку стовпців, які потрібно вибрати. У нашому прикладі це будуть стовпці Дата та Продукт.
- Клацніть правою кнопкою миші будь-який із вибраних заголовків
- Перейдіть до опції "Заповнити", а потім натисніть "Вниз". Це заповнить дані у всіх порожніх клітинках
- Перейдіть на вкладку Файл, а потім натисніть «Закрити та завантажити». Це додасть новий робочий аркуш у книгу з отриманою таблицею
Хоча цей метод може здатися трохи надмірним, одна велика перевага використання Power Query полягає в тому, що він дозволяє швидко оновлювати отримані дані у разі зміни вихідних даних.
Наприклад, якщо ви додаєте до вихідних даних більше записів, вам не потрібно повторювати все вищесказане. Ви можете просто клацнути правою кнопкою миші на отриманій таблиці, а потім натиснути Оновити.
Хоча метод Power Query працює добре, найкраще використовувати його, коли ви вже використовуєте Power Query у своєму робочому процесі.
Якщо у вас просто є набір даних, де ви хочете заповнити порожні клітинки, то більш зручним буде використання спеціального методу Go To або методу VBA (розглянутого вище).
Отже, це три прості способи, якими ви можете скористатися заповнюйте порожні клітинки до наступного значення в Excel.
Сподіваюся, цей підручник був вам корисним.
