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 của Uniace qua video bên dưới.
High Potential Talent: Chương trình dành cho bất kỳ ai muốn nghiêm túc phát triển bản thân để đạt được những thành công vững chắc và sớm hơn. Xem chi tiết.
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:.
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.
Cập nhật các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel.