Cách Sử Dụng Hàm GETPIVOTDATA Để Lấy Dữ Liệu Từ PivotTable Nhanh Chóng

Hàm GETPIVOTDATA xuất hiện tự động mỗi khi bạn nhấp vào PivotTable, nhưng ít người biết tại sao Excel làm vậy. Nhiều người vội vàng tắt tính năng này đi mà không hiểu được giá trị thực sự của nó. Thực tế, đây là một trong những hàm mạnh mẽ nhất để trích xuất dữ liệu động từ bảng tổng hợp.

Mục Lục

Tại Sao GETPIVOTDATA Tự Động Xuất Hiện

Khi bạn tạo công thức và nhấp vào ô trong PivotTable, Excel tự động chèn hàm GETPIVOTDATA thay vì tham chiếu ô đơn giản như B4. Điều này xảy ra vì tính năng Generate GetPivotData được bật mặc định. Lý do Microsoft thiết kế như vậy là để đảm bảo công thức của bạn vẫn lấy đúng dữ liệu ngay cả khi cấu trúc PivotTable thay đổi.

Tham chiếu ô thông thường như B4 sẽ bị sai khi bạn thêm hoặc xóa trường trong PivotTable. GETPIVOTDATA lấy dữ liệu dựa trên tên trường và điều kiện, không phải vị trí ô, nên luôn trả về đúng giá trị bạn cần. Tôi từng mất 2 giờ sửa 50 công thức báo cáo vì đã dùng tham chiếu ô và quên cập nhật sau khi thay đổi layout PivotTable.

Cú Pháp Và Cách Hoạt Động

Cú pháp của hàm GETPIVOTDATA có vẻ phức tạp lúc đầu nhưng logic rất rõ ràng. Công thức cơ bản gồm hai tham số bắt buộc và các cặp trường điều kiện tùy chọn.

Cấu trúc đầy đủ:

=GETPIVOTDATA(tên_trường_dữ_liệu, ô_bất_kỳ_trong_pivot, [trường_1, giá_trị_1], [trường_2, giá_trị_2])

Tham số đầu tiên là tên trường giá trị bạn muốn lấy, ví dụ “Doanh Thu” hoặc “Số Lượng”. Tham số thứ hai là tham chiếu đến bất kỳ ô nào trong PivotTable, thường dùng ô đầu tiên góc trên bên trái. Các cặp trường và giá trị sau đó là điều kiện lọc, giống như bạn đang nói với Excel rằng lấy doanh thu của sản phẩm A tại khu vực Hà Nội.

Ví dụ thực tế: Giả sử PivotTable của bạn có dữ liệu doanh thu theo sản phẩm và khu vực. Để lấy doanh thu của sản phẩm Laptop tại Hà Nội, công thức sẽ là:

=GETPIVOTDATA("Doanh Thu", $A$3, "Sản Phẩm", "Laptop", "Khu Vực", "Hà Nội")

Trong đó A3 là ô đầu tiên của PivotTable. Sử dụng tham chiếu tuyệt đối với dấu dollar để công thức không thay đổi khi copy sang ô khác.

Cách Nhập Công Thức Nhanh

Thay vì gõ toàn bộ cú pháp, Excel cung cấp cách nhập nhanh bằng chuột. Điều này giúp bạn tránh gõ sai tên trường hoặc giá trị.

Các bước:

Chọn ô ngoài PivotTable nơi bạn muốn hiển thị kết quả
Gõ dấu bằng và nhấp vào ô trong PivotTable chứa dữ liệu cần lấy
Excel tự động tạo công thức GETPIVOTDATA hoàn chỉnh
Nhấn Enter để hoàn tất

Khi bạn nhấp vào ô trong PivotTable, Excel phân tích vị trí đó và tự động xác định tên trường cùng các điều kiện cần thiết. Phương pháp này nhanh hơn gõ tay và đảm bảo tên trường chính xác 100 phần trăm.

XEM THÊM: Hàm SORTBY Excel 2021 Giúp Tôi Sắp Xếp 10.000 Dòng Trong 2 Giây Thay Vì 5 Phút

Lấy Dữ Liệu Với Điều Kiện Động

Sức mạnh thực sự của GETPIVOTDATA nằm ở khả năng sử dụng tham chiếu ô cho điều kiện. Thay vì gõ cứng giá trị như “Laptop” hay “Hà Nội”, bạn có thể tham chiếu đến ô khác chứa giá trị đó.

Ví dụ với tham chiếu động:

=GETPIVOTDATA("Doanh Thu", $A$3, "Sản Phẩm", H7, "Khu Vực", I7)

Trong công thức này, H7 chứa tên sản phẩm và I7 chứa tên khu vực. Khi bạn thay đổi giá trị trong H7 hoặc I7, kết quả tự động cập nhật mà không cần sửa công thức. Đây là cách tạo bảng báo cáo linh hoạt từ PivotTable.

Tôi sử dụng kỹ thuật này để tạo dashboard doanh thu theo tháng. Ô dropdown chọn tháng, công thức GETPIVOTDATA tự động lấy dữ liệu tương ứng từ PivotTable mà không cần thay đổi gì. Tiết kiệm 15 phút mỗi lần cập nhật báo cáo.

Tránh Lỗi REF Với Dữ Liệu Ngày Tháng

Lỗi REF là vấn đề phổ biến nhất khi làm việc với GETPIVOTDATA, đặc biệt với dữ liệu ngày tháng. Lỗi này xuất hiện khi Excel không tìm thấy giá trị phù hợp trong PivotTable hoặc khi định dạng ngày không khớp.

Nguyên nhân chính: Khi bạn gõ ngày tháng dưới dạng văn bản như “15/03/2025”, Excel có thể hiểu sai định dạng hoặc PivotTable lưu ngày dưới dạng số thứ tự khác. Điều này khiến hàm không tìm được dữ liệu phù hợp và trả về lỗi REF.

Giải pháp 1 – Sử dụng hàm DATE:

=GETPIVOTDATA("Số Lượng", $A$3, "Ngày", DATE(2025,3,15))

Hàm DATE tạo giá trị ngày chuẩn mà Excel và PivotTable đều nhận diện được. Cú pháp DATE gồm năm, tháng, ngày theo thứ tự đó.

Giải pháp 2 – Tham chiếu ô chứa ngày:

=GETPIVOTDATA("Số Lượng", $A$3, "Ngày", D5)

Trong đó D5 là ô chứa ngày tháng đã được định dạng đúng. Đây là cách an toàn nhất vì bạn kiểm soát hoàn toàn định dạng ngày trong ô D5.

Giải pháp 3 – Dùng số thứ tự: Mỗi ngày trong Excel có số thứ tự duy nhất. Ngày 01/01/2025 là số 45658. Bạn có thể dùng số này trực tiếp:

=GETPIVOTDATA("Số Lượng", $A$3, "Ngày", 45658)

Để tìm số thứ tự của một ngày, nhập ngày vào ô rồi đổi định dạng thành Number. Phương pháp này tránh hoàn toàn vấn đề định dạng ngày.

Khi Nào Nên Tắt Tự Động Generate

Mặc dù GETPIVOTDATA hữu ích, đôi khi bạn chỉ cần tham chiếu ô đơn giản. Tính năng tự động generate có thể gây phiền toái khi bạn muốn copy công thức nhanh hoặc tạo phép tính đơn giản.

Cách tắt tính năng:

Nhấp vào bất kỳ ô nào trong PivotTable
Vào tab Analyze trên Ribbon
Nhấp mũi tên thả xuống bên cạnh nút Options
Bỏ chọn Generate GetPivotData

Cài đặt này áp dụng cho toàn bộ Excel trên máy tính của bạn, không chỉ file hiện tại. Khi tắt, Excel sẽ chèn tham chiếu ô thông thường như B4 thay vì hàm GETPIVOTDATA. Lưu ý rằng các công thức đã tạo trước đó không thay đổi, chỉ các công thức mới bị ảnh hưởng.

Khi nào nên tắt: Tắt khi bạn cần tạo công thức tính toán đơn giản giữa các ô trong PivotTable, ví dụ tính tỷ lệ phần trăm hoặc chênh lệch. Tham chiếu ô nhanh hơn và dễ đọc hơn trong trường hợp này.

XEM THÊM: Hàm ROWS Excel: Từ Cú Pháp Đến Kết Hợp Với 5 Hàm Phổ Biến

Khi nào nên bật: Giữ bật khi bạn xây dựng dashboard hoặc báo cáo từ PivotTable cần cập nhật tự động. GETPIVOTDATA đảm bảo công thức không bị sai khi cấu trúc PivotTable thay đổi.

Cách Loại Bỏ GETPIVOTDATA Không Cần Tắt

Có tình huống bạn muốn giữ tính năng bật nhưng thỉnh thoảng cần tham chiếu ô thông thường. May mắn, có ba cách để vượt qua GETPIVOTDATA mà không cần vào cài đặt.

Phương pháp 1 – Gõ bằng bàn phím: Thay vì nhấp chuột vào ô PivotTable, gõ trực tiếp địa chỉ ô như B4. Khi bạn gõ tay, Excel không kích hoạt tự động generate.

Phương pháp 2 – Kéo thả ô: Chọn một ô ngoài PivotTable, giữ chuột và kéo thả vào ô PivotTable bạn muốn tham chiếu. Động tác kéo thả tránh được cơ chế tự động chèn hàm.

Phương pháp 3 – Chọn nhiều ô rồi xóa: Chọn hai ô cùng lúc trong công thức, ví dụ B4 đến B5, sau đó nhấn Backspace để xóa B5. GETPIVOTDATA chỉ xuất hiện khi bạn chọn một ô đơn lẻ, không phải vùng ô. Cách này nhanh nhất khi bạn cần nhiều tham chiếu ô liên tiếp.

So Sánh GETPIVOTDATA Với Tham Chiếu Ô

Hiểu rõ sự khác biệt giúp bạn chọn phương pháp phù hợp cho từng tình huống.

Tham chiếu ô (ví dụ B4):

Đơn giản và dễ đọc
Nhanh khi tạo công thức phức tạp
Bị sai khi layout PivotTable thay đổi
Phù hợp cho phép tính tạm thời hoặc kiểm tra nhanh

GETPIVOTDATA:

Công thức dài hơn và phức tạp hơn
Luôn trả về đúng dữ liệu dù layout thay đổi
Hỗ trợ điều kiện động qua tham chiếu ô
Phù hợp cho báo cáo và dashboard cần bền vững

Tôi từng làm báo cáo quý sử dụng tham chiếu ô. Khi team khác thêm cột mới vào PivotTable, toàn bộ 30 công thức đều bị sai. Phải mất nửa ngày để sửa lại. Sau đó chuyển sang dùng GETPIVOTDATA, PivotTable thay đổi thoải mái mà báo cáo vẫn chính xác.

Lưu Ý Khi Sử Dụng

Một số điểm cần nhớ để tránh lỗi và làm việc hiệu quả hơn với hàm này.

Tên trường phải chính xác: GETPIVOTDATA nhạy cảm với chữ hoa chữ thường và dấu cách. Nếu trường trong PivotTable là “Doanh Thu” thì bạn phải gõ chính xác như vậy, không được viết “doanh thu” hoặc “Doanh thu”.

Giá trị phải tồn tại trong PivotTable: Hàm chỉ lấy được dữ liệu đang hiển thị trong PivotTable. Nếu bạn lọc ẩn một sản phẩm đi, GETPIVOTDATA sẽ trả về lỗi REF khi cố lấy dữ liệu sản phẩm đó.

Ô tham chiếu nên là ô góc: Tham số thứ hai nên dùng ô đầu tiên góc trên bên trái của PivotTable, ví dụ A3. Nếu PivotTable thu nhỏ sau khi làm mới dữ liệu, ô góc này vẫn tồn tại. Dùng ô ở giữa có thể bị lỗi khi PivotTable thay đổi kích thước.

Tính năng tính toán tùy chỉnh: Nếu PivotTable có tính toán như “Tổng của Doanh Thu” hoặc “Trung Bình Số Lượng”, bạn phải dùng tên chính xác này trong công thức, không chỉ “Doanh Thu” đơn thuần.

Tối Ưu Hiệu Suất Với PivotTable Lớn

Khi làm việc với PivotTable chứa hàng nghìn dòng, nhiều công thức GETPIVOTDATA có thể làm chậm file Excel. Có vài cách để cải thiện tốc độ.

XEM THÊM: Tôi Thay VLOOKUP Bằng Hàm MATCH Và Tiết Kiệm 70% Thời Gian Dò Tìm Dữ Liệu

Giảm số lượng công thức: Thay vì đặt GETPIVOTDATA ở mỗi ô, tạo bảng tra cứu nhỏ lấy dữ liệu quan trọng rồi dùng VLOOKUP hoặc INDEX MATCH để phân phối. Điều này giảm số lần Excel phải truy vấn PivotTable.

Tắt tính toán tự động: Vào tab Formulas, chọn Calculation Options, chuyển sang Manual. Excel sẽ không tính lại công thức liên tục khi bạn thay đổi dữ liệu. Nhấn F9 khi cần cập nhật. File của tôi giảm từ 12 giây xuống 2 giây mỗi lần thay đổi filter nhờ cách này.

Tạo bảng trung gian: Nếu cần lấy nhiều giá trị từ cùng một PivotTable, tạo bảng nhỏ chứa tất cả GETPIVOTDATA cần thiết. Các công thức khác tham chiếu đến bảng này thay vì truy vấn trực tiếp PivotTable. Số lượng truy vấn giảm mạnh.

Áp Dụng Vào Dashboard Thực Tế

GETPIVOTDATA tỏa sáng khi bạn xây dựng dashboard tương tác từ PivotTable. Kết hợp với dropdown và biểu đồ, bạn có bảng điều khiển cập nhật tự động.

Tạo dropdown chọn sản phẩm:

Tạo Data Validation với danh sách sản phẩm trong ô H1
Dùng công thức GETPIVOTDATA tham chiếu H1 cho điều kiện sản phẩm
Khi người dùng chọn sản phẩm khác, tất cả số liệu tự động thay đổi

Kết hợp với biểu đồ: Tạo dãy ô chứa công thức GETPIVOTDATA lấy dữ liệu theo tháng. Dùng dãy này làm nguồn dữ liệu cho biểu đồ. Biểu đồ sẽ tự cập nhật khi PivotTable thay đổi, không cần chỉnh sửa gì thêm.

Dashboard doanh thu của tôi sử dụng 15 công thức GETPIVOTDATA kết hợp 3 dropdown. Thay đổi bất kỳ điều kiện nào, toàn bộ số liệu và 5 biểu đồ cập nhật trong nửa giây. Báo cáo được giám đốc khen vì tiết kiệm 3 giờ mỗi tuần so với cách cũ là copy paste dữ liệu thủ công.

Xử Lý Khi PivotTable Bị Xóa Hoặc Di Chuyển

Công thức GETPIVOTDATA sẽ lỗi REF nếu PivotTable bị xóa hoặc di chuyển sang sheet khác. Có cách phòng tránh và khắc phục.

Đặt tên cho PivotTable: Vào PivotTable Tools, tab Analyze, gõ tên cho PivotTable ví dụ “BangDoanhThu”. Trong công thức, thay vì dùng A3, dùng tên này:

=GETPIVOTDATA("Doanh Thu", BangDoanhThu, "Sản Phẩm", "Laptop")

Khi di chuyển PivotTable, công thức vẫn hoạt động vì tham chiếu theo tên không phải vị trí.

Dùng IFERROR bọc ngoài:

=IFERROR(GETPIVOTDATA("Doanh Thu", $A$3, "Sản Phẩm", "Laptop"), "Không có dữ liệu")

Nếu xảy ra lỗi, công thức hiển thị thông báo thay vì REF. Cách này giúp file trông chuyên nghiệp hơn khi chia sẻ với người khác.

Tương Thích Với Các Phiên Bản Excel

Hàm GETPIVOTDATA có sẵn từ Excel 2000 trở đi, hoạt động giống nhau trên Excel 365, 2021, 2019, 2016. Không có khác biệt về cú pháp hay tính năng giữa các phiên bản.

Tính năng Generate GetPivotData cũng hoạt động nhất quán. Cách bật tắt giống hệt nhau từ Excel 2010 trở lên. Điều này có nghĩa công thức bạn viết trên Excel 365 sẽ chạy ngon lành trên máy đồng nghiệp dùng Excel 2016.

Một lưu ý nhỏ là Excel trên Mac đặt menu ở vị trí hơi khác so với Windows, nhưng công thức và logic hoàn toàn giống nhau. File chứa GETPIVOTDATA mở trên Excel Online cũng hoạt động bình thường.

Bước Tiếp Theo

Hàm GETPIVOTDATA mạnh nhất khi kết hợp với các hàm khác như SUM, AVERAGE, COUNTIF để tạo báo cáo phức tạp. Nếu bạn thường xuyên làm việc với PivotTable lớn, đầu tư thời gian thành thạo hàm này tiết kiệm hàng chục giờ mỗi tháng.

Bắt đầu với công thức đơn giản chỉ có hai tham số bắt buộc, sau đó thêm dần điều kiện lọc khi quen. Thử nghiệm với tham chiếu động để thấy được sức mạnh thực sự. Nhớ rằng mỗi lần PivotTable thay đổi layout mà báo cáo của bạn vẫn chính xác là lúc GETPIVOTDATA phát huy giá trị.