Сьогодні проблемою є об’єднані комірки, але це вертикальні об’єднані комірки. Погляньте на малюнок нижче. Якщо для регіону Середній Захід є 7 рядків, слово Середній Захід з’являється лише в першому рядку. Потім хтось об’єднує решту шість порожніх комірок з клітиною Середнього Заходу, щоб створити об’єднану комірку заввишки 7 клітин.
Це жахливий спосіб розсилати дані. Якщо хтось добре володіє Excel, можливо, він захоче відсортувати дані, додати проміжні підсумки та використовувати зведені таблиці. Об’єднані клітини запобігають цьому.
Що ще гірше, ви щодня отримуєте новий файл із штаб-квартири, структурований таким чином. HQ надсилає подібні файли до 500 філій. Будучи лише одним крихітним форпостом, у вас не так багато шансів переконати штаб, що це жахливий спосіб розсилати дані.
Сьогоднішня стаття про те, як швидко вирішити цю проблему за допомогою Power Query. Рішення Power Query стане простішим у дні з 2 по 9999, ніж наступні кроки в Excel:
Гуру Excel може пропонувати ці кроки щодня:
- Виділіть усі комірки, вибравши прямокутник вгорі та ліворуч від А1.
- Клацніть панель запуску діалогових вікон у нижньому правому куті групи Вирівнювання на вкладці Домашня сторінка.
- Двічі клацніть поле «Об’єднати клітинки», щоб скасувати вибір.
- Натисніть OK, щоб закрити діалогове вікно Форматування комірок
- Виберіть з кінця стовпця A назад до A1.
- Домашня сторінка, Знайти та вибрати, Перейти до спеціальних, пробіли, добре
- Тип = Стрілка вгору. Натисніть Ctrl + Enter
- Виберіть з кінця стовпця A назад до A1.
- Ctrl + C для копіювання. Клацніть правою кнопкою миші та вставте значення
Але є набагато простіший спосіб. Нові інструменти Power Query вбудовані у версії Windows Office 365 та Excel 2016. Якщо у вас версія Windows 2010 Excel 2010 або Excel 2013, ви можете завантажити Power Query безкоштовно від Microsoft.
Ось стратегія роботи з Power Query:
- Складіть план, по якому ви будете зберігати книгу кожного дня із штаб-квартири в одній папці з однаковим ім’ям.
- Відкрийте нову порожню книгу, де зберігатимуться фіксовані дані з штабу.
-
У порожній книзі виберіть Дані, Отримати дані, З файлу, З робочої книги.
Запустіть процес Power Query - Перейдіть до папки та файлу з кроку 1.
-
Коли відкриється вікно Power Query, зауважте, що об’єднаних комірок немає. У вас є Середній Захід у рядку 1, а потім шість клітинок, які містять "нуль". Виберіть цей стовпець, натиснувши заголовок Регіон.
Power Query автоматично видаляє об’єднані комірки -
У Power Query перейдіть на вкладку Transform. Виберіть Заповнення, Вниз. Слово Середній Захід копіюється з рядка 1 у рядки 2 - 7. Подібні трансформації відбуваються у наборі даних.
Заповніть нульові клітинки значенням зверху -
Дім, закрити та завантажити. Power Query буде закрито. Приблизно через 10-20 секунд очищені дані із штаб-квартири з’являться на новому аркуші в книзі.
Поверніть дані в Excel - Збережіть книгу з іменем, таким як CleanedDataFromHQ.xlsx.
- Коли дані вибрано, ви побачите панель Запити та підключення праворуч. Якщо панель не відображається, перейдіть до розділу Дані, запити та підключення.
-
Клацніть правою кнопкою миші запит на панелі Запити та підключення. Виберіть Властивості.
Відкрийте Властивості для цього запиту -
Виберіть "Оновити дані під час відкриття файлу". Клацніть OK.
Встановіть запит для запуску кожного разу, коли ви відкриваєте книгу.
Потім ваш робочий процес стає: (a) Отримайте книгу від штаб-квартири електронною поштою. (b) Збережіть вкладення у правильній папці з правильною назвою. (c) Відкрийте книгу CleanedDataFromHQ.xlsx. Нові дані завантажуватимуться до книги.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2221: Залийте об’єднані клітини вниз.
Привіт, ласкаво просимо до мережі. Я Білл Джелен, я був у місті Калумет, з'являється Елізабет і каже, що отримує цей файл - наш файл, як і цей файл - щодня, і це жахливо. Тут, у стовпці А, справа не тільки в тому, що вони поставили Середній Захід і залишили купу пробілів, це об’єднана комірка. Добре? Вони надсилають - штаб-квартира розсилає ці файли по місцях по всьому світу з цими дивовижними об’єднаними клітинами. Злиті клітини - це зло.
Добре, ось як від цього позбутися. По-перше, я збираюся клацнути правою кнопкою миші, перемістити або скопіювати, створити копію та перемістити її до нової книги. Тож ми робимо вигляд, що це ніби робоча книга, яку отримує Елізабет. Файл, Збережи як, і кожного разу, коли Елізабет отримує одну з цих книг, я хочу, щоб вона розмістила її в тому самому місці з точно такою ж назвою. Добре? Точне те саме ім'я-- ідеально. Отже, зараз у нас є лише один файл, один аркуш, я подаю файл закритим. А потім ми збираємося створити нову книгу. Я просто збираюся вставити сюди новий аркуш, і цей аркуш буде називатися звітом. І на цьому порожньому місці наш Звіт буде робити Дані, Отримати дані, З файлу, З робочої книги, вказувати на файл, в який ми збираємось його зберігати - отже, з точно однаковим іменем кожного разу - клацнути Імпортувати , виберіть перший аркуш,а потім натисніть Редагувати. Гаразд По-перше, це дивовижно дивовижно - запит харчування не матиме нічого з цієї дурниці клітин об’єднання, вони миттєво перетворюють комірки об’єднання в окремі комірки, і всі ці комірки нульові. Виберіть цей стовпець, він уже вибраний тут, а потім перетворити, заповнити, заповнити, як це, а потім Домашня сторінка, закрити та завантажити. Гаразд, отже, є моя робоча книжка.
Зараз, я бачу, у деяких є додаткові стовпці. Дозвольте мені клацнути правою кнопкою миші, і ми редагуємо.
І схоже на те, що все, що знаходиться праворуч від вартості, має зникнути. Скільки там зайвого? Клацніть правою кнопкою миші та Видаліть ці стовпці - ідеально - Закрити та завантажити. Добре, тепер робочий процес стає, щоразу, коли Елізабет отримує новий звіт зі штабу, вона збирається зберігати його там само з надійним іменем або як завгодно - як завгодно - ми називаємо це, а потім відкриваємо цю книгу і то тут натисніть Оновити.
Або, якщо ми хотіли працювати автоматично, клацніть тут правою кнопкою миші, опустіться вниз до Властивості, що знаходиться за межами вашого екрана внизу, а потім скажіть: Щоразу, коли я відкриваю цей файл, оновлюйте дані. Отже, кожного разу, коли я відкриваю цю книгу, вона перейде до нової книги, яку ми отримали у штабі, вона замінить ці порожні об’єднані клітинки значеннями, які там повинні бути, і життя таке чудове.
Power Query висвітлюється в моїй книзі, але це також висвітлюється в цій книзі Кена Пулса та Мігеля Ескобара, М - для (ДАНИХ) МАЙМУН.
Гаразд, так, Елізабет: Як мені позбутися вертикальних клітин злиття? Моя штаб-квартира щодня надсилає мені ці файли. Отже, план: Ми збираємось зберегти книгу в надійному місці з надійною назвою; створити порожню книгу звітності «Дані», «Отримати дані», «З файлу», «З робочої книги», вказати аркуш, БАМ! Клітини злиття зникли. Я вибираю цей стовпець і заповнюю, закриваю та завантажую; тоді кожного разу, коли ви отримуєте нову книгу, зберігайте її в надійному місці з такою ж надійною назвою; Я відкриваю книгу звітування та оновлення, або ми просто встановлюємо книгу звітів автоматично оновлювати при відкритті.
Завантажити книгу із сьогоднішнього відео - це URL-адреса в описі YouTube.
Хочу подякувати Елізабет за участь у моєму семінарі в місті Калумет, я хочу подякувати вам за заїзд. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: fill-merged-cells-down.xlsx
Power Query - це дивовижний інструмент - він дозволяє вирішувати різні
Саїд Алімохаммаді
