Tránh sử dụng VLOOKUP bằng Mô hình Dữ liệu. Vì vậy, bạn có hai bảng cần được kết hợp với VLOOKUP trước khi bạn có thể tạo bảng tổng hợp. Nếu bạn có Excel 2013 hoặc mới hơn trên PC Windows, giờ đây bạn có thể thực hiện việc này một cách đơn giản và dễ dàng.
Giả sử bạn có tập dữ liệu với thông tin sản phẩm, khách hàng và bán hàng.
Bộ phận CNTT quên đưa sector vào đó. Đây là bảng tra cứu ánh xạ khách hàng đến khu vực. Thời gian cho một VLOOKUP, phải không?
Không cần thực hiện hàm VLOOKUP để nối các tập dữ liệu này nếu bạn có Excel 2013 hoặc Excel 2016. Cả hai phiên bản Excel này đều đã kết hợp công cụ Power Pivot vào Excel cốt lõi. (Bạn cũng có thể thực hiện việc này bằng cách sử dụng bổ trợ Power Pivot cho Excel 2010, nhưng có một vài bước bổ sung.)
Trong cả tập dữ liệu gốc và bảng tra cứu, hãy sử dụng Trang chủ, Định dạng dưới dạng Bảng. Trên tab Công cụ bảng, đổi tên bảng từ Table1 thành một cái gì đó có ý nghĩa. Tôi đã sử dụng Dữ liệu và Sectors.
Chọn một ô trong bảng dữ liệu. Chọn Chèn, Bảng tổng hợp. Bắt đầu từ Excel 2013, có một hộp bổ sung Thêm Dữ liệu Này vào Mô hình Dữ liệu mà bạn nên chọn trước khi bấm OK.
Danh sách Trường Pivot Table xuất hiện cùng với các trường từ bảng Dữ liệu. Chọn Doanh thu. Bởi vì bạn đang sử dụng Mô hình Dữ liệu, một dòng mới xuất hiện ở đầu danh sách, cung cấp Hoạt động hoặc Tất cả. Nhấp vào Tất cả.
Đáng ngạc nhiên, danh sách Trường PivotTable cung cấp tất cả các bảng khác trong sổ làm việc. Đây là một bước đột phá. Bạn chưa thực hiện một VLOOKUP. Mở rộng bảng Sectors và chọn Sector. Hai điều xảy ra để cảnh báo bạn rằng có một vấn đề.
Đầu tiên, bảng tổng hợp xuất hiện với cùng một số trong tất cả các ô.
Có lẽ cảnh báo tinh tế hơn là một hộp màu vàng xuất hiện ở đầu danh sách Trường PivotTable cho biết rằng bạn cần tạo mối quan hệ. Chọn Tạo. (Nếu bạn đang sử dụng Excel 2010 hoặc 2016, hãy tận dụng cơ hội với tính năng Tự động phát hiện.)
Trong hộp thoại Tạo mối quan hệ, bạn có bốn menu thả xuống. Chọn Dữ liệu trong Bảng, Khách hàng trong Cột (Nước ngoài) và Ngành trong Bảng Liên quan. Power Pivot sẽ tự động điền vào cột phù hợp trong Cột Liên quan (Chính). Bấm OK.
Bảng tổng hợp kết quả là sự kết hợp của dữ liệu gốc và bảng tra cứu. Không cần hàm VLOOKUP.
Xem video
- Bắt đầu trong Excel 2013, hộp thoại Pivot Table cung cấp Mô hình Dữ liệu
- Đây là từ mã của Power Pivot Engine
- Để sử dụng mô hình dữ liệu, hãy tạo bảng Ctrl + T từ mỗi bảng trong sổ làm việc
- Tạo bảng tổng hợp từ bảng đầu tiên
- Trong danh sách trường bảng tổng hợp, thay đổi từ Hoạt động thành Tất cả
- Chọn một trường từ bảng tra cứu
- Tạo mối quan hệ hoặc Tự động phát hiện
- Tự động phát hiện không có ở đó vào năm 2013
- Cảm ơn Colin Michael và Alejandro Quiceno đã đề xuất Power Pivot nói chung.
Bản ghi video
Học Excel từ podcast, tập 2014 - Loại bỏ hàm VLOOKUP!
Podcasting toàn bộ cuốn sách này, hãy nhấp vào chữ "i" ở góc trên bên phải của danh sách phát!
Xin chào, chào mừng bạn quay trở lại netcast, tôi là Bill Jelen, đây thực sự được gọi là Loại bỏ VLOOKUP bằng Mô hình Dữ liệu! Bây giờ tôi xin lỗi, đây là Excel 2013 và mới hơn, nếu bạn quay lại Excel 2010, bạn phải tải xuống phần bổ trợ Power Pivot, tất nhiên là miễn phí vào năm 2010. Vì vậy, những gì chúng tôi có ở đây là chúng tôi có tập dữ liệu chính, có trường Khách hàng ở đây và sau đó tôi có một bảng nhỏ ánh xạ khách hàng theo lĩnh vực, tôi cần tạo tổng doanh thu theo lĩnh vực, phải không? Đây là một hàm VLOOKUP, chỉ cần thực hiện một hàm VLOOKUP, nhưng này, nhờ Excel 2013, chúng tôi không phải thực hiện một hàm VLOOKUP! Tôi đã tạo cả hai cái này thành một bảng và trên Table Tools, Design, tôi đổi tên bảng, tôi gọi cái này là Sectors và tôi gọi cái này là Data, để biến nó thành bảng, chỉ cần chọn một ô, nhấn Ctrl + T. Vì vậy, nếu chúng ta có một số tiêu đề và một số số, khi bạn nhấn Ctrl + T,họ hỏi "Dữ liệu cho bảng của bạn ở đâu?", bảng của tôi có tiêu đề, và sau đó họ gọi nó là Table3, bạn gọi nó là cái gì khác. Được rồi, đó là cách tôi tạo hai bảng đó, tôi sắp xóa bảng này, được rồi.
Vì vậy, để thủ thuật này hoạt động, tất cả dữ liệu phải nằm trong bảng. Chúng tôi chuyển đến tab Chèn, chọn PivotTable, và ngay dưới đây ở dưới cùng, Thêm dữ liệu này vào Mô hình Dữ liệu. Điều này nghe rất vô thưởng vô phạt phải không? Không có gì giống như điểm nhấp nháy đang nói "Này, nó sẽ cho phép bạn làm những điều tuyệt vời!" Và những gì họ đang nói ở đây, những gì họ đang cố gắng không nói là- Ồ, nhân tiện, mọi bản sao của Excel 2013 đều có công cụ Power Pivot đằng sau nó. Bạn biết đấy, nếu bạn đang sử dụng Office 365, bạn đang trả 10 đô la một tháng và họ muốn bạn trả 12 đô la hoặc 15 đô la một tháng để có Power Pivot, thêm hai hoặc năm đô la. Ồ, này, xin đừng nói, bạn thực sự đã có hầu hết Power Pivot trong Excel 2013. Được rồi, vì vậy tôi nhấp vào OK, mất một chút thời gian để tải mô hình dữ liệu, được rồi, nhưng không sao, và kết thúc đây,trong các trường PivotTable, tôi có danh sách tất cả các trường. Vì vậy, tôi muốn hiển thị Doanh thu, chắc chắn, nhưng điều khác biệt là ở đây với Hoạt động và Tất cả. Khi tôi chọn Tất cả, tôi nhận được tất cả các bảng trong sổ làm việc. Được rồi, vì vậy tôi chuyển đến Sectors và tôi đã nói rằng tôi muốn đặt sector trong vùng Rows. Bây giờ, ban đầu, báo cáo sẽ sai, hãy xem con số 6,7 triệu giảm xuống, và cảnh báo màu vàng ở đây sẽ nói rằng bạn phải tạo mối quan hệ.và cảnh báo màu vàng ở đây sẽ nói rằng bạn phải tạo mối quan hệ.và cảnh báo màu vàng ở đây sẽ nói rằng bạn phải tạo mối quan hệ.
Được rồi, vào năm 2010 với Power Pivot, nó sẽ cung cấp AutoDetect, vào năm 2013 họ đã đưa AutoDetect ra và vào năm 2016, họ đã đưa AutoDetect trở lại, được chứ? Tôi sẽ cho bạn thấy CREATE trông như thế nào, nhưng khi tôi nhấp vào nút CREATE này, ồ, vậy là xong, tốt rồi. Vì vậy, từ Bảng dữ liệu đầu tiên của chúng tôi, tôi có một trường gọi là Khách hàng, từ bảng liên quan Sectors, tôi có một trường gọi là Khách hàng, và sau đó bạn nhấp vào OK, được rồi. Nhưng hãy để tôi chỉ cho bạn thấy AutoDetect thú vị như thế nào, nếu bạn tình cờ đến vào năm 2016, ở đó, họ đã tìm ra, điều đó tuyệt vời như thế nào, phải không? Bạn không cần phải lo lắng về hàm VLOOKUP và dấu phẩy rơi ở cuối, nếu hàm VLOOKUP khiến bạn đau đầu, bạn sẽ yêu thích Mô hình dữ liệu. Bạn biết đấy, lấy hai bảng đó, nối chúng lại với nhau, giống như Access sẽ làm, tôi đoán vậy, và tạo một bảng Pivot, hoàn toàn tuyệt vời.Vì vậy, hãy kiểm tra mô hình dữ liệu vào lần tới khi bạn phải thực hiện một hàm VLOOKUP giữa hai bảng. Điều này và tất cả 40 mẹo khác đều có trong cuốn sách, Hãy nhấp vào chữ "i" ở góc trên bên phải. Bạn có thể mua cuốn sách, có tài liệu tham khảo chéo đầy đủ về toàn bộ loạt video này, tất cả tháng 8, cả tháng 9, ôi chao, chúng tôi thậm chí có thể chuyển sang tháng 10 để hoàn thành toàn bộ.
Được rồi, tóm tắt lại hôm nay: bắt đầu từ Excel 2013, hộp thoại Pivot Table cung cấp một thứ gọi là Mô hình Dữ liệu, đó là từ mã cho công cụ Power Pivot. Trước khi bạn tạo bảng Pivot, hãy thực hiện Ctrl + T để tạo bảng từ mỗi sổ làm việc, tôi đã dành thêm thời gian để đặt tên cho từng bảng. Tạo bảng Tổng hợp từ bảng đầu tiên, sau đó trong danh sách trường, đi lên trên cùng và thay đổi từ Hoạt động thành Tất cả. Chọn một trường từ bảng tra cứu và sau đó nó sẽ cảnh báo bạn rằng bạn phải tạo mối quan hệ, hoặc Tự động phát hiện, vào năm 2013, bạn phải nhấp vào TẠO. Nhưng đó là những gì, 4 lần nhấp để tạo nó, 5 lần nếu bạn đếm nút OK, thật sự rất dễ làm.
Được rồi, Colin, Michael và Alejandro Quiceno đã đề xuất Power Pivot nói chung cho các cuốn sách, cảm ơn họ, cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp lại bạn lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2014.xlsx
