Excel поєднує кілька аркушів в один аркуш. Кожен аркуш може мати різну кількість записів з кожним днем, тому формули не найкращий шлях. Натомість, маловідомий інструмент під назвою Power Query дозволить вам просто та швидко об’єднати дані.
Переглянути відео
- Дуг: Як поєднати чотири аркуші, де кожен має різну кількість рядків?
- Використовуйте Power Query
- Відформатуйте кожен аркуш як таблицю за допомогою Ctrl + T
- Перейменуйте таблиці
- Для кожної таблиці новий запит із таблиці. Додайте спеціальний стовпець для регіону
- Замість Закрити та Завантажити виберіть Закрити та Завантажити для… Тільки створити з’єднання
- Використовуйте новий запит, об’єднайте запит, додайте. 3 або більше таблиць. Виберіть таблиці та додайте
- Закрийте та завантажте, і дані з’являться на новому аркуші
- Для однієї таблиці з додатковим стовпцем: дані відображаються лише для записів цього аркуша
- Для однієї таблиці, де стовпці були в неправильному порядку: Power Query працював правильно!
- Легко оновити пізніше
Автогенерована стенограма
- Дізнайтеся Excel з епізоду Podcast
- 2178 злиття для робочих аркушів сьогодні
- питання через YouTube від Дуга має таке
- У нас є чотири аркуші, де кожен
- аркуш - це регіон з даними про продаж і
- кількість записів змінюється щомісяця і
- зараз Дуг намагається використовувати формули
- але коли кількість рядків змінилася, це
- стає кошмаром прямо тому я сказав hey
- Дуг, чи можемо ми використовувати енергетичний запит, якщо у вас є
- Excel 2010 або Excel 2013 це безкоштовно
- завантажте з Microsoft або він побудований
- в 2016 і в Office 365 це означає, що так
- так ось ось що ми маємо
- чотири повідомлення центральний регіон схід
- регіон південний регіон і захід
- регіон, і кожен з них має різний
- кількість записів, як тут на Півдні
- Регіон у нас 72 записи на сході
- регіон 193 записи, і це буде
- змінити право
- кожного разу, коли ми запускаємо цей звіт
- інша кількість записів, які я зараз маю
- зробив тут деякі припущення, що
- немає стовпця, який називається центральним і
- тоді також я буду цілком злим
- тут і візьміть Південний регіон, який я хочу
- спробуй зіпсувати це я збираюся взяти
- стовпець прибутку виріжте його та вставте як
- змінити їх, а потім добре, так що ми
- є таку, де стовпці зворотні
- а потім ще один, де ми збираємося
- додати додатковий стовпець відсотка валового прибутку
- так що це буде прибуток, поділений на
- дохід в ідеальному світі - це все
- формувався точно такий же, але як я дізнався
- нещодавно я проводив семінар в
- Північна Кароліна, якщо з ними не все гаразд
- у когось була ситуація, добре ви знаєте
- на півдорозі рік все змінилося
- і вони додали нову колонку або переміщення
- колони навколо нас справді раділи
- переконайтеся, що енергетичний запит зміг впоратися
- з цим все гаразд, тому ми візьмемося
- кожен з цих звітів і внести його в
- офіційний формат таблиці як таблиця так
- це контрольний чай, або ви можете використовувати a
- діапазон імен для мене контролю t - це
- простіший шлях і що вони тут роблять
- вони називають цей стіл одним, який я збираюся
- перейменувати це, щоб називатися центральним і
- далі ми переходимо до східного контролю T натисніть кнопку OK
- і це зараз буде називатися Схід, ей
- у попередньому подкасті я показав, як якщо
- це були чотири окремі файли
- міг би використовувати енергетичний запит просто для
- комбінувати файли, але це не працює, коли
- це чотири окремі або чотири робочі листи
- в тій же книзі так добре там ми
- піти і потім
- як цей контроль-т трохи нудно
- налаштувати це вперше, але хлопчик
- там буде чудово
- кожен раз, коли вам доведеться оновлювати це пізніше
- так що ми будемо робити це ми йдемо
- обрати цю першу таблицю центральною
- регіону, і якщо ви знаходитесь у 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
