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