POWER PIVOT LÀ GÌ?

GIẢI PHÁP MỚI THAY THẾ CHO VIỆC SỬ DỤNG VLOOKUP

Luôn có nhiều cách để hoàn thành bất cứ điều gì trong Microsoft Excel. Nhưng con đường ngắn nhất sẽ dẫn chúng ta đi nhanh nhất cũng giống như trong excel có một số giải pháp không hiệu quả bằng những giải pháp khác. Gần đây, mình đã gặp phải một workbook mất hơn 20 phút để tính toán. Khi mình bắt đầu tìm hiểu logic của bảng tính, mình tìm thấy một bảng tính tổng hợp có 60,000 hàng, 75 cột và hàng loạt các công thức VLOOKUP. Nếu bạn nghiên cứu Excel đủ nhiều, bạn chắc có lẽ cũng biết rằng con đường ngắn nhất để làm cho workbook Excel chậm đi là sử dụng hàm VLOOKUP. Ngoài ra, khi nhắc tới VLOOKUP, mình lại nhớ ngay tới 1 điều mà 95% người dùng Excel cũng thường xuyên nhầm lẫn đó là giới hạn lưu trữ dữ liệu của Excel là 1,048,576 dòng, điều này hoàn toàn sai. Power Pivot (không phải Pivot Table) chính là đáp án mà bất kỳ ai cũng ước rằng mình biết ngay từ đầu khi sử dụng Excel. Xem giới thiệu nhanh về Power Pivot qua video bên dưới.

MỘT MỚ HỖN LOẠN

Người tạo trang tính này đã sử dụng các công thức để kéo dữ liệu từ 20 trang tính khác vào một trang tính nguyên khối để nó có thể được đưa vào một bảng tổng hợp. Các công thức hoạt động nhưng không hiệu quả. Ví dụ: để điền các cột được gọi là Quota Jan, Quota Feb, Quota Mar, đến Quote Dec, công thức đã sử dụng 12 câu lệnh IF lồng nhau riêng biệt. Lưu ý rằng các phần màu xanh lam đang thay đổi cột tra cứu được trả về bởi hàm VLOOKUP:

=IF(MONTH(C2)=1,VLOOKUP(A2,Quotas!$A$1:$M$9999,2,False), IF(MONTH(C2)=2,VLOOKUP(A2,Quotas!$A$1:$M$9999,3,False), IF(MONTH(C2)=3,VLOOKUP(A2,Quotas!$A$1:$M$9999,4,False), IF(MONTH(C2)=4,VLOOKUP(A2,Quotas!$A$1:$M$9999,5,False), IF(MONTH(C2)=5,VLOOKUP(A2,Quotas!$A$1:$M$9999,6,False), IF(MONTH(C2)=6,VLOOKUP(A2,Quotas!$A$1:$M$9999,7,False), IF(MONTH(C2)=7,VLOOKUP(A2,Quotas!$A$1:$M$9999,8,False), IF(MONTH(C2)=8,VLOOKUP(A2,Quotas!$A$1:$M$9999,9,False), IF(MONTH(C2)=9,VLOOKUP(A2,Quotas!$A$1:$M$9999,10,False), IF(MONTH(C2)=10,VLOOKUP(A2,Quotas!$A$1:$M$9999,11,False), IF(MONTH(C2)=11,VLOOKUP(A2,Quotas!$A$1:$M$9999,12,False), IF(MONTH(C2)=12,VLOOKUP(A2,Quotas!$A$1:$M$9999,13,False)))))))))))).

Công thức lớn này đang cố gắng phát hiện cột nào của bảng VLOOKUP sẽ trả về. Dựa trên ngày trong cột C, các câu lệnh IF được dịch sang số tháng, sau đó chọn cột nào sẽ trả về từ bảng tra cứu. Hãy nhớ rằng, có 60.000 hàng nhân với 12 cột của công thức lớn này. Đó là 720,000 công thức VLOOKUP tìm kiếm trong một bảng có 10,000 hàng.

Sẽ đơn giản hơn nếu có một công thức VLOOKUP duy nhất và có một số logic bên trong đối số thứ ba để quyết định cột nào sẽ trả về. Trong trường hợp cụ thể này, logic cho đối số thứ ba là số tháng của ngày được tìm thấy trong C2 cộng với 1. Bạn có thể đơn giản hóa công thức thành:

=VLOOKUP (A2, Quotas! $ A $ 1: $ M $ 9999, MONTH (C2) + 1, False).

Điều gì sẽ xảy ra nếu chuyển đổi không đơn giản như vậy? Điều gì sẽ xảy ra nếu bạn đang cố gắng sử dụng tên vùng trong cột D để tìm một cột cụ thể trong bảng tra cứu? Nếu các tiêu đề trong bảng tra cứu chứa các tên vùng chính xác được tìm thấy trong hàng tiêu đề của bảng, bạn có thể sử dụng công thức MATCH để trả về số cột chính xác:

=VLOOKUP (A2, RegionTable! $ A $ 1 $ P $ 9999, MATCH (D2, RegionTable! $ 1: $ 1,0), False).

Nhưng ngay cả những giải pháp này cũng sẽ để lại 720.000 công thức VLOOKUP. Bạn vẫn sẽ có thời gian tính toán lại rất chậm.

Hơn nữa, bảng tổng hợp kết quả đã bị sai định dạng. Nhà phân tích này đã sử dụng đến việc thêm 12 trường Quota vào khu vực Row để chúng xuất hiện trong bảng tổng hợp. Bất kỳ lúc nào bạn có 12 cột hiển thị Quota (hoặc bất kỳ số nào) cho mỗi tháng, bạn có thể tạo bảng tổng hợp tốt hơn bằng cách giải nén dữ liệu gốc. Công cụ Power Query mới của Microsoft giúp bạn giải nén một cách nhanh chóng thông qua thao tác Unpivot.

THỜI ĐẠI CỦA BẢNG TỔNG HỢP MASTER ĐÃ SẮP KẾT THÚC

Trước đây, dữ liệu cho bảng tổng hợp phải ở một nơi. Bắt đầu từ Excel 2013, bạn có thể dễ dàng tạo bảng tổng hợp từ các bảng riêng biệt. Trước khi bạn tạo bảng tổng hợp, hãy làm theo các quy tắc đơn giản sau cho dữ liệu chính của bạn và cho mỗi bảng tra cứu:

  1. Chọn từng tập dữ liệu và nhấn Ctrl + T. Điều này xác định tập dữ liệu là một bảng. Đi tới trường Table trên tab Design và thay đổi tên mặc định (Table1) thành một cái gì đó mang tính mô tả.
  2. Đảm bảo rằng có một cột duy nhất trong bảng của bạn sẽ liên quan đến một cột duy nhất trong bảng khác. Trong Hình 1, cột G là một cột mới nối số khách hàng và tên tháng để tạo liên kết một cột với một bảng tra cứu.
  3. Tạo bảng tổng hợp từ bảng dữ liệu chính. Sử dụng Insert đến Pivot table. Trong hộp thoại Tạo Pivot table, hãy chọn hộp Add This Data to the Data model. (Xem Hình 2).
  4. Pivot table pane sẽ hiển thị các cột từ bảng gốc của bạn, nhưng một dòng phụ gần đầu bảng sẽ cung cấp các lựa chọn ACTIVE hoặc ALL (Xem Hình 3.) Chọn ALL, và bây giờ bạn có thể chọn từ bất kỳ bảng nào trong workbook. Lần đầu tiên bạn chọn một trường từ một bảng mới, ngăn Pivot Table Fields sẽ cảnh báo bạn rằng bạn cần xác định mối quan hệ giữa các bảng, như thể hiện trong Hình 4.5. Nhấn vào Create và xác định cột đơn có thể hoạt động như một trường khóa giữa hai bảng. (Xem Hình 5.)

Bây giờ bạn có thể kéo các trường từ các trang tính khác nhau vào bảng tổng hợp. Nếu bạn muốn kéo dài hàng tháng trên báo cáo, bạn có thể đặt trường Ngày vào vùng Cột thay vì kéo 12 trường khác nhau vào bảng tổng hợp.

MỞ RỘNG TÍNH TOÁN RA KHỎI GIỚI HẠN CỦA PIVOT TABLE THÔNG THƯỜNG

Khả năng kết nối nhiều bảng cùng lúc trong một mô hình dữ liệu để bạn có thể Pivot dễ dàng xuất hiện lần đầu tiên trong Excel 2010 và được cập nhật liên tục theo thời gian. Phiên bản Excel 365 đang là bản mới nhất với đầy đủ những cập nhật thú vị. Tóm lại, một công thức có 12 VLOOKUP có thể hoạt động, nhưng nó có thể làm chậm quá trình tính toán lại để thu thập thông tin. Thay vào đó, sử dụng mô hình dữ liệu đa bảng có liên quan sẽ giải quyết được sự cố và làm cho trang tính có thể sử dụng được.

Ngoài ra, nếu bạn biết về Power Query, thì bạn có thể sử dụng tính năng Merge thay cho VLOOKUP. Xem thêm qua video bên dưới.

 

Trả lời