Bạn đang tìm cách tạo báo cáo Excel hiệu quả, tính tổng các giá trị dựa trên điều kiện cụ thể? Bài viết này sẽ hướng dẫn chi tiết cách kết hợp hàm VLOOKUP với SUM và SUMIF trong Excel, giúp bạn xử lý dữ liệu số một cách nhanh chóng và chính xác. Tìm hiểu ngay!
Microsoft Excel cung cấp nhiều hàm mạnh mẽ để xử lý dữ liệu, và việc kết hợp các hàm này có thể mở ra vô vàn khả năng. Bài viết này sẽ tập trung vào việc kết hợp hàm VLOOKUP với SUM và SUMIF, giúp bạn tra cứu và tính tổng giá trị dựa trên một hoặc nhiều điều kiện. Các ví dụ thực tế sẽ giúp bạn dễ dàng áp dụng vào công việc.
Kết Hợp VLOOKUP và SUM: Tính Tổng Giá Trị Phù Hợp
Kết hợp VLOOKUP và SUM cho phép bạn trích xuất giá trị từ một bảng và tính tổng các số trong nhiều cột hoặc hàng.
Ví dụ: Giả sử bạn có danh sách sản phẩm với doanh số bán hàng theo tháng (bảng “Doanh số tháng”). Bạn muốn tạo bảng tổng hợp doanh thu cho từng sản phẩm.
Doanh số bán hàng theo tháng
Công thức: =SUM(VLOOKUP(lookup_value, lookup_range, {2,3,…,n}, FALSE))
Trong đó:
lookup_value: Giá trị cần tra cứu (ví dụ: tên sản phẩm).lookup_range: Bảng chứa dữ liệu cần tra cứu (ví dụ: bảng “Doanh số tháng”).{2,3,…,n}: Mảng các chỉ số cột cần tính tổng.FALSE: Chỉ định tìm kiếm chính xác.
Áp dụng: Để tính tổng doanh thu từ cột B đến M trong bảng “Doanh số tháng”, công thức sẽ là:
{=SUM(VLOOKUP(B2, 'Doanh số tháng'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}
Lưu ý: Đây là công thức mảng, cần nhấn Ctrl + Shift + Enter sau khi nhập. Excel sẽ tự động thêm dấu ngoặc nhọn {}.
Tổng doanh thu theo sản phẩm
Sử dụng bảng Excel (Insert > Table) giúp tham chiếu dữ liệu dễ dàng hơn, ví dụ [@Product].
Tuy nhiên, công thức mảng có thể làm chậm hiệu suất nếu bảng dữ liệu lớn. Một giải pháp thay thế là kết hợp INDEX và MATCH.
Thực Hiện Các Phép Tính Khác với VLOOKUP
Bạn có thể kết hợp VLOOKUP với các hàm khác như AVERAGE, MAX, MIN để thực hiện các phép tính khác.
| Phép Tính | Công Thức | Mô Tả |
|---|---|---|
| Trung bình | {=AVERAGE(VLOOKUP(A2, 'Bảng Tra Cứu'!$A$2:$D$10, {2,3,4}, FALSE))} | Tính trung bình các giá trị ở cột B, C, D. |
| Lớn nhất | {=MAX(VLOOKUP(A2, 'Bảng Tra Cứu'!$A$2:$D$10, {2,3,4}, FALSE))} | Tìm giá trị lớn nhất ở cột B, C, D. |
| Nhỏ nhất | {=MIN(VLOOKUP(A2, 'Bảng Tra Cứu'!$A$2:$D$10, {2,3,4}, FALSE))} | Tìm giá trị nhỏ nhất ở cột B, C, D. |
| Phần trăm | {=0.3*SUM(VLOOKUP(A2, 'Bảng Tra Cứu'!$A$2:$D$10, {2,3,4}, FALSE))} | Tính 30% tổng giá trị ở cột B, C, D. |
Lưu ý: Nhớ nhấn Ctrl + Shift + Enter sau khi nhập công thức mảng.
Kết quả các phép tính
Kết Hợp LOOKUP và SUM: Tra Cứu trong Mảng
Khi giá trị tra cứu là một mảng, hãy sử dụng hàm LOOKUP.
Ví dụ: Tính tổng đơn hàng của một khách hàng cụ thể.
Dữ liệu đơn hàng
Công thức: {=SUM(LOOKUP($C$2:$C$10,'Bảng Tra Cứu'!$A$2:$A$16,'Bảng Tra Cứu'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))}
Lưu ý: Cột tra cứu trong bảng “Bảng Tra Cứu” phải được sắp xếp tăng dần.
Tổng đơn hàng theo khách hàng
Kết Hợp VLOOKUP và SUMIF: Tra Cứu và Tính Tổng với Tiêu Chí
SUMIF cho phép tính tổng giá trị dựa trên điều kiện.
Ví dụ: Tính tổng doanh số của một nhân viên bán hàng dựa trên ID.
Công thức: =SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
Kết Luận
Việc kết hợp VLOOKUP với SUM và SUMIF mang đến sự linh hoạt trong việc xử lý dữ liệu Excel. Hy vọng bài viết này giúp bạn áp dụng hiệu quả các kỹ thuật này vào công việc. Hãy khám phá thêm các bài viết khác trên Thủ Thuật để nâng cao kỹ năng Excel của bạn.
Discussion about this post