Excel kết hợp một số trang tính thành một trang tính. Mỗi trang tính có thể có một số lượng bản ghi khác nhau hàng ngày, vì vậy công thức không phải là cách để thực hiện. Thay vào đó, một công cụ ít được biết đến có tên Power Query sẽ cho phép bạn hợp nhất dữ liệu một cách đơn giản và nhanh chóng.
Xem video
- Doug: Làm thế nào để kết hợp bốn trang tính mà mỗi trang có số hàng khác nhau?
- Sử dụng Power Query
- Định dạng từng trang tính dưới dạng Bảng bằng Ctrl + T
- Đổi tên bảng
- Đối với mỗi bảng, truy vấn mới Từ Bảng. Thêm cột tùy chỉnh cho Khu vực
- Thay vì Đóng & Tải, hãy chọn Đóng & Tải để… Chỉ Tạo kết nối
- Sử dụng truy vấn mới, kết hợp truy vấn, nối. 3 hoặc nhiều bảng. Chọn các bảng và thêm
- Đóng và Tải và dữ liệu xuất hiện trên một trang tính mới
- Đối với một bảng có thêm cột: dữ liệu chỉ hiển thị cho các bản ghi của trang tính đó
- Đối với một bảng có các cột không đúng thứ tự: Power Query hoạt động chính xác!
- Dễ dàng làm mới sau
Bản ghi được tạo tự động
- Học Excel từ Podcast Episode
- 2178 hợp nhất cho trang tính ngày nay
- câu hỏi qua YouTube từ Doug có cái này
- tình huống chúng tôi có bốn tờ trong đó mỗi tờ
- trang tính là khu vực có dữ liệu bán hàng và
- số lượng hồ sơ thay đổi hàng tháng và
- ngay bây giờ Doug đang cố gắng sử dụng các công thức
- nhưng khi số lượng hàng thay đổi
- trở thành một cơn ác mộng đúng không nên tôi đã nói này
- Doug, chúng tôi có thể sử dụng truy vấn nguồn nếu bạn có
- Excel 2010 hoặc Excel 2013 hoàn toàn miễn phí
- tải xuống từ Microsoft hoặc nó được xây dựng
- sang năm 2016 và office 365 nó nói có tất cả
- đúng vậy đây là những gì chúng tôi có chúng tôi có
- bốn báo cáo miền trung miền đông
- khu vực miền nam và miền tây
- khu vực và mỗi khu vực có một
- số lượng kỷ lục như đây ở miền Nam
- Khu vực chúng tôi có 72 hồ sơ ở miền đông
- khu vực 193 hồ sơ và điều này sẽ
- đổi bên phải
- mỗi khi chúng tôi chạy báo cáo này sẽ có
- một số hồ sơ khác bây giờ tôi
- đã đưa ra một số giả định ở đây trước rằng
- không có cột nào được gọi là trung tâm và
- sau đó tôi cũng sẽ hoàn toàn xấu xa
- ở đây và lấy Khu vực phía Nam mà tôi muốn
- thử và vặn nó lên, tôi sẽ lấy
- cột lợi nhuận cắt nó và dán nó như thế nào để
- đảo ngược những điều đó và sau đó tất cả đều ổn để chúng tôi
- có một nơi các cột bị đảo ngược
- và sau đó là một cái khác, nơi chúng ta sẽ
- thêm một cột bổ sung phần trăm lợi nhuận gộp
- vì vậy đây sẽ là lợi nhuận chia cho
- doanh thu trong một thế giới lý tưởng, tất cả đều là
- có hình dạng giống hệt như tôi đã học
- gần đây tôi đã tham gia một cuộc hội thảo ở
- Bắc Carolina nếu họ không ổn
- ai đó đã có một tình huống mà bạn biết rõ
- giữa năm mọi thứ đã thay đổi
- và họ đã thêm một cột mới hoặc di chuyển
- các cột xung quanh chúng tôi thực sự rất vui khi
- thấy rằng truy vấn quyền lực đã có thể giải quyết
- với điều này tất cả các quyền, vì vậy chúng tôi sẽ lấy
- từng báo cáo này và biến nó thành
- một định dạng bảng chính thức như bảng
- đó là trà kiểm soát hoặc bạn có thể sử dụng
- phạm vi tên đối với tôi điều khiển t là
- cách dễ dàng hơn để đi và những gì họ làm ở đây
- họ gọi cái bàn này là bàn tôi sẽ
- đổi tên cái này được gọi là trung tâm và
- sau đó chúng ta chuyển sang điều khiển Đông T bấm OK
- và bây giờ nó sẽ được gọi là Đông này
- trên một podcast trước đó, tôi đã chỉ ra cách nếu
- đây là bốn tệp riêng biệt, chúng tôi
- có thể đã sử dụng truy vấn nguồn chỉ để
- kết hợp các tệp nhưng điều đó không hoạt động khi
- chúng là bốn hoặc bốn trang tính riêng biệt
- trong cùng một cuốn sách rất tốt ở đó chúng tôi
- đi và sau đó
- như điều khiển này - hơi tẻ nhạt với
- thiết lập điều này lần đầu tiên nhưng cậu bé
- sẽ rất tuyệt
- mỗi lần bạn phải cập nhật điều này sau
- vì vậy chúng tôi sẽ làm là chúng tôi sẽ
- để chọn trung tâm bảng đầu tiên này
- khu vực và nếu bạn đang ở năm 2010 hoặc 2013 và
- đã tải xuống truy vấn nguồn, bạn sẽ
- sẽ có tab riêng nhưng sau 16 in
- Excel 2016 đang thực sự nhận được
- đã biến đổi đó là nhóm thứ hai trong
- office 365 hiện đang được chuyển đổi
- đó là nhóm đầu tiên và vì vậy chúng tôi
- sẽ nói rằng họ sẽ tạo ra cái này
- dữ liệu từ một bảng hoặc phạm vi đều đúng và
- 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
- nói câu hỏi đó tốt cảm ơn bạn
- đã dừng lại, chúng tôi sẽ gặp bạn lần sau
- cho một diễn viên ròng khác từ
Tải tập tin
Tải file mẫu tại đây: Podcast2178.xlsm
