зміст
Одне з відео, яке найчастіше переглядають на моєму каналі YouTube, — це відео про Flash Fill у Microsoft Excel. Суть цього інструменту полягає в тому, що якщо вам потрібно якось трансформувати вихідні дані, то вам просто потрібно почати вводити результат, який ви хочете отримати, у сусідній колонці. Після кількох введених вручну комірок (зазвичай достатньо 2-3) Excel «зрозуміє» логіку необхідних перетворень і автоматично продовжить набране, завершивши за вас всю монотонну роботу:
Квінтесенція ефективності. Чарівна кнопка «зроби це правильно», яку ми всі так любимо, чи не так?
Власне, в Power Query є аналог такого інструменту – там він називається Колонка з прикладами (Колонка з прикладами). Насправді це невеликий штучний інтелект, вбудований у Power Query, який може швидко вивчати ваші дані, а потім перетворювати їх. Давайте детальніше розглянемо його можливості в кількох практичних сценаріях, щоб зрозуміти, де він може нам стати в нагоді в реальних завданнях.
Приклад 1. Наклеювання/вирізання тексту
Припустимо, у нас є така «розумна» таблиця в Excel з даними про співробітників:
Завантажте його в Power Query стандартним способом – кнопкою З таблиці/діапазону таб дані (Дані — з таблиці/діапазону).
Припустимо, потрібно додати графу з прізвищами та ініціалами для кожного співробітника (Іванов С.В. для першого співробітника та ін.). Щоб вирішити цю проблему, можна скористатися одним із двох методів:
- клацніть правою кнопкою миші заголовок стовпця з вихідними даними та виберіть команду Додайте стовпець із прикладів (Додати колонку з прикладів);
- виберіть один або кілька стовпців з даними та на вкладці Додавання колонки вибрати команду Колонка з прикладами. Тут у розкривному списку можна вказати, чи потрібно аналізувати всі чи лише вибрані стовпці.
Далі все просто – у стовпчик, який з’являється праворуч, ми починаємо вводити приклади бажаних результатів, а штучний інтелект, вбудований у Power Query, намагається зрозуміти нашу логіку перетворень і продовжити далі самостійно:
До речі, ви можете вводити правильні параметри в будь-які комірки цього стовпця, тобто не обов'язково зверху вниз і підряд. Крім того, ви можете легко додати або видалити стовпці з аналізу пізніше, використовуючи прапорці в рядку заголовка.
Зверніть увагу на формулу у верхній частині вікна – це те, що створює Smart Power Query, щоб отримати потрібні результати. У цьому, до речі, полягає принципова відмінність даного засобу від Миттєве заповнення в Excel. Миттєве заповнення працює як «чорний ящик» — воно не показує нам логіку перетворень, а просто дає готові результати, які ми сприймаємо як належне. Тут все прозоро і завжди можна абсолютно чітко зрозуміти, що саме відбувається з даними.
Якщо ви бачите, що Power Query «зрозумів ідею», то можете сміливо натискати кнопку OK або комбінацію клавіш Ctrl+ Що натомість? Створіть віртуальну версію себе у – буде створено спеціальний стовпець із формулою, створеною Power Query. До речі, потім його можна легко редагувати як звичайний стовпець, створений вручну (за допомогою команди Додавання стовпця – спеціальний стовпець), натиснувши значок шестірні праворуч від назви кроку:
Приклад 2: Відмінок як у реченнях
Якщо клацнути правою кнопкою миші на заголовку стовпця з текстом і вибрати команду Перетворення (Трансформація), то ви побачите три команди, відповідальні за зміну реєстру:
Зручно і круто, але в цьому списку, наприклад, мені особисто завжди не вистачало ще одного варіанту – регістр як у реченнях, коли велика (велика) стає не першою літерою в кожному слові, а лише першою літерою в клітинці, і решта тексту, коли Це відображається малими (маленькими) літерами.
Цю відсутню функцію легко реалізувати за допомогою штучного інтелекту Колонки з прикладами – просто введіть кілька параметрів, щоб Power Query продовжив у тому ж дусі:
Як формула тут Power Query використовує купу функцій Текст.Верхній и Текст.Нижній, перетворення тексту відповідно у верхній і нижній регістри та функції Текст.Початок и Текст.Сер – аналоги функцій Excel LEFT і PSTR, здатні виділяти підрядок з тексту зліва і з середини.
Приклад 3. Перестановка слів
Іноді при обробці отриманих даних виникає необхідність переставити слова в клітинках в заданій послідовності. Звичайно, ви можете розділити колонку на окремі колонки слів роздільником, а потім склеїти її назад у вказаному порядку (не забудьте додати пробіли), але за допомогою інструменту Колонка з прикладами все буде набагато простіше:
Приклад 4: Тільки числа
Ще одне дуже важливе завдання - витягнути з вмісту комірки тільки цифри (числа). Як і раніше, після завантаження даних у Power Query переходимо на вкладку Додавання колонки – Стовпець із прикладів і заповнюємо пару клітинок вручну, щоб програма розуміла, що саме ми хочемо отримати:
Бінго!
Знову ж таки, варто поглянути на верхню частину вікна, щоб переконатися, що Query правильно згенерував формулу – у цьому випадку вона містить функцію текст. Виберіть, який, як можна здогадатися, витягує задані символи з вихідного тексту відповідно до списку. Згодом цей список, звичайно, можна легко редагувати в рядку формул, якщо це необхідно.
Приклад 5: Лише текст
Як і в попередньому прикладі, можна витягнути і навпаки – тільки текст, видаливши всі цифри, знаки пунктуації тощо.
У цьому випадку використовується вже протилежна за змістом функція – Text.Remove, яка видаляє символи з вихідного рядка відповідно до заданого списку.
Приклад 6: Вилучення даних з буквено-цифрової каші
Power Query може допомогти і в більш складних випадках, коли потрібно витягти з буквено-цифрової каші в комірці корисну інформацію, наприклад, отримати номер рахунку з опису призначення платежу на банківській виписці:
Зверніть увагу, що згенерована Power Query формула перетворення може бути досить складною:
Для зручності читання та розуміння його можна перетворити в набагато більш розумну форму за допомогою безкоштовного онлайн-сервісу. Форматувальник Power Query:
Дуже зручна річ – респект творцям!
Приклад 7: Перетворення дат
Інструмент Колонка з прикладами також можна застосувати до стовпців дати або дати та часу. Коли ви введете перші цифри дати, Power Query допоможе відобразити список усіх можливих варіантів перетворення:
Тож ви можете легко конвертувати вихідну дату в будь-який екзотичний формат, наприклад «рік-місяць-день»:
Приклад 8: Категоризація
Якщо ми використовуємо інструмент Колонка з прикладами до стовпця з числовими даними, це працює інакше. Припустімо, у нас є результати тестування співробітників, завантажені в Power Query (умовні бали в діапазоні 0-100), і ми використовуємо таку умовну градацію:
- Майстри – ті, хто набрав більше 90 балів
- Експерти – від 70 до 90 балів
- Користувачів – від 30 до 70
- Початківці – ті, хто набрав менше 30 балів
Якщо ми додамо до списку стовпець із прикладів і почнемо розставляти ці градації вручну, то дуже скоро Power Query підхопить нашу ідею і додасть стовпець із формулою, де оператори вкладені один в одного if буде реалізована логіка, дуже схожа на те, що нам потрібно:
Знову ж таки, можна не дотискати ситуацію до кінця, а клацати далі OK а потім виправте порогові значення вже у формулі - так швидше:
Висновки
Звичайно, інструмент Колонка з прикладами не є «чарівною пігулкою» і рано чи пізно виникнуть нестандартні ситуації або особливо запущені випадки «колгоспу» в даних, коли Power Query дасть збій і не зможе відпрацювати те, що ми хочемо правильно для нас. Однак як допоміжний засіб він дуже хороший. Крім того, вивчаючи формули, які він згенерував, ви можете розширити свої знання про функції мови M, що завжди стане в нагоді в майбутньому.
- Розбір тексту за допомогою регулярних виразів (RegExp) у Power Query
- Пошук нечіткого тексту в Power Query
- Flash Fill у Microsoft Excel