зміст
Експерименти роблять нас героями.
(спалах)
Хоча інструмент Миттєве заповнення (Flash Fill) з’явився в Excel з версії 2013 року, але чомусь цей факт залишився непоміченим для багатьох користувачів. І абсолютно даремно. У багатьох випадках це виявляється простішим, легшим і швидшим, ніж аналогічні рішення на основі формул або макросів. З мого досвіду, на тренінгах ця тема викликає постійне «вау!» аудиторія – незалежно від просунутості та/або втоми слухачів.
Механізм роботи цього інструменту простий: якщо у вас є один або кілька стовпців з вихідними даними, і ви починаєте вводити їх поруч один з одним у наступному стовпці, але в якомусь потрібному вам зміненому вигляді, то рано чи пізно Excel натякне, що він готовий продовжувати далі поза вами:
Щоб виявити логіку (паттерн, патерн) перетворення і запустити цю функцію Excel, зазвичай досить ввести перші 1-3 результуючих значення вручну. Якщо запропонований варіант вас влаштовує, то просто натисніть
Що натомість? Створіть віртуальну версію себе у
– і решта списку буде завершено миттєво.
Якщо ви вже ввели перші 2-3 значення, а продовження все одно не з'являється, то ви можете форсувати процес за допомогою комбінації клавіш Ctrl+E або скористайтеся кнопкою Миттєве заповнення (Flash Fill) таб дані (Дата):
Давайте розглянемо кілька прикладів того, як цей інструмент можна використовувати на практиці, щоб зрозуміти його можливості.
Виділення слів із тексту та перестановки
Написати формулу, яка виділяє, наприклад, третє слово з тексту в комірці, не є маленькою справою. Розберіть фразу за пропуском у різні стовпці за допомогою Дані – текст за стовпцями (Дані — текст у стовпці) Це також не швидко. З моментальним заповненням це робиться легко і красиво. Крім того, ви можете одночасно змінювати витягнуті слова місцями, поєднуючи їх у будь-якому порядку:
Поділ тексту регістром
Щоб виділяти слова для миттєвого заповнення, зовсім не обов'язково мати пробіл. Будь-який інший роздільник працюватиме нормально, наприклад кома чи крапка з комою після імпортування файлу CSV. Але що дуже круто, так це те, що роздільника може не бути взагалі – достатньо лише великих літер:
Реалізувати такі формули дуже складно. Якщо без моментального заповнення, то допоможе тільки макрос.
Склеювання тексту
Якщо можна розділити, то можна і склеїти! Instant Fill з легкістю збере для вас довгу фразу з кількох фрагментів, вставивши в них потрібні пробіли, коми, союзи або слова:
Вилучення окремих символів
Зазвичай для вилучення окремих символів і підрядків в Excel використовуються функції ЛЕВСІМВ (ВЛІВО), ПРАВО (ПРАВО), PSTR (середина) і тому подібне, але миттєве заповнення вирішує цю проблему з легкістю. Класичним прикладом є утворення повного імені:
Вилучайте лише числа, текст або дати
Якщо ви коли-небудь намагалися витягнути з буквено-цифрової каші тільки потрібний тип даних, то повинні розуміти всю складність цього, здавалося б, простого завдання. Моментальне наповнення і тут справляється з чубчиком, але вам потрібен легкий пендель у формі Ctrl+E:
Те саме стосується вилучення тексту.
Дати теж не проблема (навіть якщо вони написані в різних форматах):
Перетворення форматів числа або дати
Flash Fill може допомогти змінити зовнішній вигляд наявних даних або привести їх до одного знаменника. Наприклад, щоб перетворити звичайну дату «нагору» у формат Unix:
Тут нюанс полягає в тому, що перед введенням потрібно заздалегідь змінити формат отриманих комірок на текстовий, щоб Excel не намагався розпізнати «неправильні» дати, введені вручну як зразок.
Подібним чином ви також можете правильно представити телефонні номери, додавши код країни та тризначний префікс оператора (місто) у дужках:
Не забудьте спочатку змінити формат комірок у стовпці B на текстовий – інакше Excel розглядатиме значення uXNUMXbuXNUMXb, що починаються зі знака «+», як формули.
Перетворення тексту (чисел) на дату
Під час завантаження з різних систем ERP і CRM дата часто представляється у вигляді 8-значного числа у форматі РРРРММДД. Ви можете перетворити його на звичайний вигляд за допомогою функції ІДЕНТИФІКАТОР ДАНИХ (DATEVALUE), або набагато простіше – моментальне заповнення:
Змінити регістр
Якщо ви отримали текст з неправильним регістром, то можете просто підказати в наступному стовпці, до якого типу ви хочете його перетворити – і миттєве заповнення зробить всю роботу за вас:
Трохи складніше буде, якщо для різних частин тексту потрібно змінити регістр по-різному. Наприклад, напишіть з великої літери лише друге слово, залишивши перше в його звичайній формі. Тут двох значень, введених як вибірку, буде недостатньо і доведеться вносити зміни, які моментальне заповнення відразу врахує в результатах:
Обмеження та нюанси
Використовуючи Flash Fill у своїй роботі, слід пам’ятати про кілька речей:
- Це працює, лише якщо вводити зразки строго поруч – у попередній або наступний стовпець праворуч від даних. Якщо від вихідного тексту відступити одну порожню колонку, то нічого не вийде.
- Коли закономірність знайдена враховуються всі значення підряд — ліворуч і праворуч від колонки введення. Мораль: зайві стовпці, які можуть заплутати алгоритм або внести шум, слід заздалегідь відокремити від робочих даних порожніми стовпцями або видалити.
- Миттєве заповнення чудово працює в розумних таблицях.
- найменший помилка або опечатка під час введення зразків клітинок може призвести до того, що флеш-заливка не розкриє візерунок і не спрацює. Будь обережний.
- Бувають ситуації, коли шаблон визначено неправильно, тому завжди потрібно перевіряти результатиякі ви отримали (принаймні вибірково).
- Як витягти останнє слово з тексту в комірці
- Пошук нечіткого тексту (Пушкін = Пушкін) із нечітким пошуком у Excel
- Три способи вставки тексту в Excel