Power Query - розділіть комірки з розділеннями на рядки

Це перша з двох публікацій, яка показує, як розділити та об’єднати рядки з/до розділених комірок. У минулі дні нам знадобилося багато часу, щоб вирішити ці сценарії. Але завдяки Power Query це швидкий і простий процес, який можна оновити простим оновленням.

У цій першій частині ми розглянемо, як розділити розділені клітинки на рядки за допомогою Power Query.

Перегляньте відео:

Завантажте прикладний файл
Я рекомендую завантажити файли, які підтримують цю публікацію, оскільки ви зможете працювати разом із прикладами. Це найкращий спосіб вчитися. Ви зможете переглянути рішення в дії, а також файл буде корисним для подальшого використання. Файли підтримки доступні БЕЗКОШТОВНО передплатникам розсилок.

Клацніть нижче, щоб підписатися та отримати доступ до зони абонента. Ви також отримаєте:

Мої улюблені поради та підказки прямо у вашу поштову скриньку
Ексклюзивний вміст (доступний лише передплатникам)
БЕЗКОШТОВНІ інструменти та завантаження

Якщо ви вже підписалися, натисніть тут, щоб увійти в область завантаження абонента.

Назва файлу для цього повідомлення: 0023 Розділіть клітинки з розділеннями на рядки. Zip

Сценарій

Почнемо з розгляду нашого сценарію даних.

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

Також записуються атрибути кожного предмета. Наприклад, ноутбук Анни Уоткінс має ідентифікаційний номер ID0011 та картку доступу з номером 231 тощо. Елемент відокремлюється від атрибута двокрапкою та як пробіл.

Хоча формат корисний для швидкого перегляду всіх співробітників, він не ідеальний, якщо ми хочемо знати, хто має конкретний товар. Наприклад, у кого є кредитна картка компанії? Або хто має який номер телефону?

Наступний формат є більш корисним з точки зору обробки даних.

Якби у нас був такий формат, ми могли б легко відфільтрувати стовпець Обладнання та ідентифікувати осіб за допомогою кредитної картки компанії або навіть створити телефонний список співробітників.

Ми будемо використовувати Power Query для швидкого перетворення вихідних даних у цей формат.

Завантажте дані в Power Query

Для початку нам потрібно отримати дані в Power Query. Виділіть будь-яку комірку в таблиці, а потім натисніть Дані -> З таблиці/діапазону.

Відкриється редактор Power Query, в якому відображатимуться дані з таблиці.

Добре, ми готові до початку магії:

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

Розділити на ряди

Добре, поділимо стовпець Обладнання на рядки.

Виберіть Обладнання натиснувши на заголовок стовпця.
Клацніть Домашня сторінка -> Розділений стовпець (спадне меню) -> За допомогою розділювача.
У прикладі, який ми обробляємо, кожна частина обладнання відокремлена крапкою з комою та пробілом. Цього немає в списку роздільників за замовчуванням, тому нам потрібно вибрати На замовлення зі спадного меню, потім у полі нижче введіть a крапка з комою (;) слідом за а простору. Виберіть варіант розділення кожне входження роздільника.
Розкрийте доступні опції, клацнувши Розширені опції, потім виберіть Рядки.
Клацніть в порядку виконати перетворення.

Дані тепер виглядають так:

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

Розбийте стовпець на стовпці

Тепер настав час відокремити обладнання від його атрибутів.

Як і раніше, клацніть Домашня сторінка -> Розділений стовпець (спадне меню) -> За допомогою розділювача щоб відкрити діалогове вікно Розділити стовпець за допомогою роздільника.

Замість розбиття на рядки ми можемо розбити на стовпці. Наш роздільник - товста кишка (:), слідом за а простору.

Клацніть в порядку розділити стовпець Обладнання.

Перейменуйте стовпці

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

Вікно попереднього перегляду тепер виглядає так.

Завантажте електронну книгу 100 макросів Excel

Містить 100 макросів Excel VBA
Вивчіть VBA, слідуючи разом із прикладами кодів
Застосовуйте до своїх макросів, автоматизуйте Excel, заощаджуйте час.

Завантажте електронну книгу сьогодні!

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

Завантажте дані в Excel

Усе завершено, тому ми готові повернути таблицю в Excel. Клацніть Головна -> Закрити та завантажити (спадне меню) -> Закрити та завантажити…

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

Клацніть в порядку щоб закрити діалогове вікно Імпорт даних.

Остаточні дані тепер у програмі Excel.

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

Висновок

Power Query - це чудовий інструмент. Розбиття комірок просто недоступне за допомогою звичайного інтерфейсу Excel. Нам довелося б використовувати макрос або якісь надзвичайно складні формули. Однак за допомогою Power Query ми можемо розділити розділені клітинки всього за кілька кліків.

Не забувайте:

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

Вам потрібна допомога у адаптації цього до ваших потреб?

Я припускаю, що приклади в цій публікації не точно відповідають вашій ситуації. Всі ми використовуємо Excel по-різному, тому неможливо написати допис, який би відповідав потребам усіх. Витративши час на розуміння методів і принципів у цій публікації (та в інших місцях на цьому сайті), ви повинні мати можливість адаптувати її до своїх потреб.

Але, якщо ви все ще боретесь, вам слід:

Читайте інші блоги або дивіться відео YouTube на ту саму тему. Ви отримаєте набагато більше користі, відкривши власні рішення.
Запитайте «Excel Ninja» у вашому офісі. Дивно, що знають інші люди.
Задайте питання на форумі, як Mr Excel, або спільноті відповідей Microsoft. Пам’ятайте, люди на цих форумах, як правило, віддають свій час безкоштовно. Тож подбайте про розробку свого питання, переконайтеся, що воно чітке та стисле. Перелічіть все, що ви спробували, та надайте знімки екрана, сегменти коду та приклади книг.
Скористайтеся програмою Excel Rescue, яка є моїм консультантом. Вони допомагають, надаючи рішення менших проблем Excel.

Що далі?
Ще не йдіть, ще багато чого можна навчити в Excel Off The Grid. Перегляньте останні повідомлення: