Excel объединяет несколько листов в один лист. Каждый рабочий лист может иметь разное количество записей изо дня в день, поэтому формулы - не лучший вариант. Вместо этого малоизвестный инструмент под названием Power Query позволит вам просто и быстро объединить данные.
Смотреть видео
- Дуг: Как объединить четыре листа, на каждом из которых разное количество строк?
- Используйте Power Query
- Отформатируйте каждый рабочий лист как таблицу с помощью Ctrl + T
- Переименуйте таблицы
- Для каждой таблицы новый запрос From Table. Добавить настраиваемый столбец для региона
- Вместо «Закрыть и загрузить» выберите «Закрыть и загрузить, чтобы… только создать соединение».
- Использовать новый запрос, объединить запрос, добавить. 3 или более таблиц. Выберите таблицы и добавьте
- Закройте и загрузите, и данные появятся на новом листе
- Для одной таблицы с дополнительным столбцом: данные отображаются только для записей этого листа
- Для одной таблицы, в которой столбцы были расположены в неправильном порядке: Power Query работал правильно!
- Легко обновить позже
Автоматически созданная стенограмма
- Изучите Excel из эпизода подкаста
- 2178 слияние рабочих листов сегодня
- вопрос через YouTube от Дуга имеет это
- ситуация у нас есть четыре листа, где каждый
- лист - это регион с данными о продажах и
- количество записей меняется ежемесячно и
- прямо сейчас Дуг пытается использовать формулы
- но когда количество строк изменилось,
- становится кошмаром, так что я сказал эй
- Дуг, можем ли мы использовать запрос мощности, если у вас есть
- Excel 2010 или Excel 2013 это бесплатно
- скачать из Microsoft или он построен
- в 2016 и Office 365 он говорит: да, все
- Итак, вот что у нас есть
- четыре сообщает центральный регион восток
- регион южный регион и запад
- регион, и у каждого свой
- количество записей, как здесь, на юге
- Регион у нас 72 записи на востоке
- регион 193 записи, и это будет
- изменить право
- каждый раз, когда мы запускаем этот отчет, будет
- другое количество записей сейчас у меня
- сначала сделал некоторые предположения, что
- нет столбца под названием центральный и
- Тогда я буду полностью злым
- вот и возьмите Южный регион, который я хочу
- Попробуй облажаться, я возьму
- столбец прибыли вырезать и вставить как
- поменять их местами и тогда все в порядке
- есть тот, где столбцы перевернуты
- а затем еще один, где мы собираемся
- добавить дополнительный столбец «Процент валовой прибыли»
- так что это будет прибыль, разделенная на
- доход в идеальном мире - это все
- форма точно такая же, но как я узнал
- недавно я проводил семинар в
- Северная Каролина, если с ними не все в порядке
- у кого-то была ситуация, вы знаете
- в середине года все изменилось
- и они добавили новый столбец или переместили
- колонны вокруг мы были очень счастливы
- увидеть, что запрос мощности смог справиться
- с этим все в порядке, так что мы возьмем
- каждый из этих отчетов и превратить его в
- официальный формат таблицы как таблица так
- это контрольный чай, или вы могли бы использовать
- диапазон имен для меня контроль t это
- более легкий путь и что они здесь делают
- они называют этот стол один, я собираюсь
- переименуйте его в центральный и
- затем переходим к Восточному контролю T нажимаем ОК
- И теперь это будет называться Восток, эй
- в предыдущем подкасте я показал, как если
- это были четыре отдельных файла, которые мы
- мог бы использовать запрос мощности только для
- объединить файлы, но это не работает, когда
- это четыре отдельных или четыре листа
- в той же самой книге так хорошо там мы
- иди а потом
- как этот control-t немного утомительно
- настроил это в первый раз, но мальчик
- там будет круто
- каждый раз, когда вам нужно обновить это позже
- так мы собираемся сделать это мы идем
- выбрать этот первый стол в центре
- регион, и если вы в 2010 или 2013 и
- скачал запрос питания
- будет своя вкладка, но через 16 дюймов
- Excel 2016 действительно получает
- преобразованный, который является второй группой в
- Office 365 трансформируется
- это первая группа, поэтому мы
- собираюсь сказать, что они собирались создать это
- данные из таблицы или диапазона все в порядке и
- there is our data now we don't have a
- region field and the combined files
- would have added the region field so in
- this case I'm just gonna add a column a
- new custom call the headings gonna be
- region and this one is going to be what
- was this central right like that
- click OK alright now here's the
- important part when we're done this with
- this we're gonna go home not choose
- close and load we're gonna open the
- drop-down close and load to only create
- a connection click OK
- perfect we have our connection only now
- the next thing we have to do is repeat
- these steps for the next three regions
- and now that would be really a bit
- boring to you so let's just speed up the
- video to 10x for this
- alright there we are for connections set
- up now here's where we're going to do
- the magic I'm gonna insert a new blank
- worksheet and I'm gonna say get data
- combine queries and I want to append two
- queries from this workbook and I'm gonna
- say three or more tables and the
- available tables are Central through
- West click Add BAM click OK and then we
- can close and load and what we have here
- is we have a superset of all of the
- records in all of the tables all right
- and where we tried to screw it up where
- I purposely tried to screw up by
- reversing cost of goods sold and profit
- down in what was that that was Central
- East South in the South Region I'll just
- go check those right and it looks like
- yeah generally feels right they used the
- heading to figure it out because the
- profit is always higher than cost of
- goods sold and so that worked and then
- down here in the West where we added
- gross profit percent we actually get
- that data for the tables that had it and
- for the tables that didn't have it we
- just get null which is perfect alright
- now duck
- here's what you're gonna do so the next
- time that you have some more data and
- I'll just let's create some some extra
- records here we'll just add some ABC
- with a date of today and all retail and
- it's called Doug's new records and just
- some garbage out here let's just put in
- a hundred all the way across in the
- interest of time okay so now because
- this is a table the table automatically
- expands to the new records which is
- beautiful had they been named range I
- would have had a redefine that's why I
- really like the table instead of the
- name range but we come back here to the
- resulting workbook with 563 rows loaded
- and I click refresh
- and bam now I have 572 Rose loaded
- including let's see if we can find them
- in here
- Doug's new records right there at the
- end of the South Region
- isn't that just an awesome awesome way
- to go yes it definitely takes longer to
- set up the first day we're up to seven
- minutes already if I hadn't sped that up
- to 10x but once it's set up now life is
- gonna be super super easy from here on
- out way this is where I usually promote
- my own book but no this time let's talk
- about this awesome book Emma's for data
- monkey by Ken polls in Miguel Escobar
- everything I learned about power query I
- learned from this book look at the eye
- on the top right hand corner for more
- information about that book all right
- wrap up topics in this episode Doug how
- to combine four sheets where each sheet
- has a different number of rows we can
- use power query make sure to format each
- worksheet as a table with ctrl T or use
- named ranges but I prefer ctrl T rename
- the tables from each table choose new
- query from table add a custom column for
- a region and then instead of close and
- load choose close and load to only
- create a connection do that for all four
- queries and then new query combined
- query append choose three or more tables
- choose the tables and click Add
- now some older versions of power query
- you couldn't do three or more tables you
- have to do two and then do another query
- to add the third one and then do another
- query to add the fourth one either way
- it would be more hassle that way I'm
- glad that they added the three or more
- tables close and load this time close
- and load to the worksheet and and then
- later on if you add more data to any of
- the four tables just go back to your
- query and click refresh and you're good
- to go
- power query and amazing new feature from
- Microsoft I love it I thank Doug for
- Сказать этот вопрос хорошо, спасибо
- чтобы заглянуть, увидимся в следующий раз
- для другой чистой трансляции из
Скачать файл
Загрузите образец файла здесь: Podcast2178.xlsm
