Excel là công cụ mạnh mẽ cho việc xử lý dữ liệu, đặc biệt khi kết hợp các hàm. Bài viết này sẽ hướng dẫn bạn cách kết hợp VLOOKUP, SUM và SUMIF để tra cứu và tính tổng dữ liệu dựa trên nhiều điều kiện, giúp tối ưu hóa việc tạo báo cáo và dashboard.
Ví dụ kết hợp VLOOKUP và SUM
Kết Hợp VLOOKUP và SUM
Ví dụ: Tính tổng sản lượng Cam trong quý 1 (tháng 1, 2, 3).
Công thức mảng: =SUM(VLOOKUP(A2,A1:I8,{2,3,4},FALSE))
Lưu ý: Nhấn Ctrl + Shift + Enter sau khi nhập công thức mảng.
Công thức này tương đương với:
=VLOOKUP(A2,A1:I8,2,FALSE) + VLOOKUP(A2,A1:I8,3,FALSE) + VLOOKUP(A2,A1:I8,4,FALSE)
Ưu điểm của công thức mảng là linh hoạt khi tạo báo cáo. Bạn có thể dễ dàng thay đổi ô sản phẩm để tính tổng sản lượng mà không cần sửa công thức.
Báo cáo sản lượng
Hàm SUM có thể được thay thế bằng AVERAGE, MIN, MAX hoặc các phép tính khác. Nhớ nhấn Ctrl + Shift + Enter cho mọi công thức mảng.
Tổng Hợp Dữ Liệu Không Cột Phụ: LOOKUP và SUM
Ví dụ: Tính tổng giá trị mua hàng của mỗi khách hàng, không dùng cột phụ.
Dữ liệu khách hàng và sản phẩm
Công thức tại K2: =SUM(LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8)*$F$2:$F$8*($D$2:$D$8=K1))
Lưu ý:
- Cột A (sản phẩm) phải được sắp xếp tăng dần.
- Nhấn
Ctrl + Shift + Enterhoặc dùng hàm SUMPRODUCT thay cho SUM.
Tính tổng với cột phụ
Giải thích công thức:
LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8): Trả về mảng đơn giá tương ứng với sản phẩm.$F$2:$F$8: Mảng số lượng sản phẩm.($D$2:$D$8=K1): Tạo mảng TRUE/FALSE (1/0) dựa trên điều kiện khách hàng.
Kết Hợp VLOOKUP và SUMIF: Tra Cứu và Tổng Hợp Theo Điều Kiện
Ví dụ: Tính tổng doanh số của từng Telesale dựa trên tên.
Dữ liệu Telesale và doanh số
Công thức: =SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)
VLOOKUP trả về ID của Telesale, sau đó SUMIF tính tổng doanh số dựa trên ID.
Tính Tổng Nhiều Điều Kiện Cùng Cột
Ví dụ: Tính tổng giao dịch với đầu mã 111 và 131.
Ba cách tính:
=SUMIF(A:A,"111*",C:C)+SUMIF(A:A,"131*",C:C)=SUM(SUMIF(A:A,{"111*","131*"},C:C))(công thức mảng)=SUM(C2:C14*(--(LEFT(A2:A14,3)={"111","131"})))(công thức mảng)
SUMIF kết hợp VLOOKUP: Ví dụ thực tế
Ví dụ SUMIF và VLOOKUP
Yêu cầu: Tính tổng số lượng bán theo tên mặt hàng (F11).
Công thức: =SUMIF(A2:A15,VLOOKUP(F11,E2:F8,2,0),C2:C15)
Công thức SUMIF và VLOOKUP
SUMIFS kết hợp VLOOKUP: Ví dụ nâng cao
Yêu cầu: Tính tổng số lượng bán với 3 điều kiện: từ ngày, đến ngày và tên hàng.
Công thức: =SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"<="&F12,A2:A15,VLOOKUP(F13,E2:F8,2,0))
Kết luận
Việc kết hợp các hàm VLOOKUP, SUM và SUMIF mang lại sự linh hoạt và hiệu quả trong việc xử lý dữ liệu trên Excel. Bài viết này đã cung cấp các ví dụ cụ thể và chi tiết để bạn có thể áp dụng vào công việc.
Discussion about this post