Trong nội dung này, mình sẽ hướng dẫn các bạn làm thế nào để có thể phối hợp các hàm VLOOKUP, SUM và SUMIF để dò tìm và tính tổng dựa trên 1 số điều kiện.
Mục lục
- Phối hợp hàm VLOOKUP và SUMIF
- Tổng hợp dữ liệu không thêm cột phụ, phối hợp hàm LOOKUP và SUM
- Phối hợp hàm VLOOKUP và SUMIF tra cứu và tổng hợp dữ liệu theo điều kiện
- Tính tổng nhiều điều kiện ở cùng 1 cột
Table of Contents
Phối hợp hàm VLOOKUP và SUMIF
Trước khi khởi đầu vào bài này, chắc hẳn các bạn đã thành thục với hàm VLOOKUP và hàm SUMIF rồi. Vậy nên tất cả chúng ta có thể khởi đầu vào ngay ví dụ trước tiên.
Trong ví dụ này, tất cả chúng ta sẽ đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 bằng cách sử dụng công thức sau đây:
=SUM(VLOOKUP( A2 , A1:I8 , {2,3,4} , FALSE ))
Sau khoảng thời gian nhập công thức này, các bạn lưu ý vì đây là công thức mảng, vậy nên sau thời điểm nhập công thức, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể nhập được công thức và có kết quả đúng.
Để các bạn có thể hiểu hơn, tất cả chúng ta sẽ phân tích công thức mảng này bằng cách mang ra 3 công thức tương đương sau đây:
=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )
Phần {2,3,4} ở trong công thức mảng phía trên nghĩa là cột 2,3,4 trong mảng dữ liệu A1:I8. Đọc đến đây, có thể các bạn có thể sẽ có thắc mắc, vì sao tất cả chúng ta không sử dụng công thức sau đây để tính tổng
=SUM(B2:D2)
Mục đích mang ra những công thức phức tạp này, tất cả chúng ta sẽ phục vụ cho việc tạo giải trình hoặc dashboard. Nếu tất cả chúng ta sử dụng một công thức tính tổng đơn thuần bằng hàm SUM, khi tất cả chúng ta có 1 ô chứa các loại hàng hoá, muốn thay đổi ô này để tính tổng sản lượng của 1 sản phẩm, tất cả chúng ta phải thay đổi công thức SUM theo.
Với công thức mảng phối hợp hàm SUM và VLOOKUP như ở trên, tất cả chúng ta có thể tạo ra 1 giải trình về sản lượng của các sản phẩm 1 cách nhanh chóng như sau:
Từ ví dụ phía trên, tất nhiên, ta có thể thay hàm SUM bằng 1 số hàm khác để phục vụ mục đích của tất cả chúng ta như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép tính toán khác … Và lưu ý vì các công thức này đều là công thức mảng, nên bạn cần sử dụng tổ hợp phím tắt CTRL + SHIFT + ENTER khi nhập công thức này trong Excel.
Tổng hợp dữ liệu không thêm cột phụ, phối hợp hàm LOOKUP và SUM:
Tất cả chúng ta có ví dụ sau, trong ví dụ này, tất cả chúng ta có 2 bảng dữ liệu, 1 bảng gồm có sản phẩm và đơn giá; bảng thứ 2 bao gồm khách hàng, sản phẩm và số lượng sản phẩm khách hàng đã mua. Tất cả chúng ta sẽ muốn đi tính tổng giá trị của 1 khách hàng
Bình thường, tất cả chúng ta sẽ cần dùng cột phụ như sau:
Các công thức như sau:
G2=VLOOKUP(E2,bang_SP,2,FALSE)
H2=F2*G2
K2=SUMIF(D:D,K1,H:H)
</code.
Trong trường hợp không thêm được cột phụ, tất cả chúng ta có thể sử dụng công thức sau tại K2:
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 ý khi sử dụng công thức phối hợp SUM và LOOKUP này:
- Cột A phải được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z, để hàm LOOKUP cho tất cả chúng ta giá trị đúng.
- Công thức được nhập vào bằng tổ hợp phím
CTRL + SHIFT + ENTER
Nếu không sử dụng tổ hợp phím này, bạn có thể thay hàm SUM bằng hàm SUMPRODUCT
Công thức này được hiểu như vậy nào?
- Phần
LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8)cho tất cả chúng ta kết quả như cột đơn giá trong hình chụp phía trên - Phần
$F$2:$F$8là mảng số lượng các sản phẩm - Phần
($D$2:$D$8=K1)tạo ra 1 mảng gồm các giá trị đúng và sai, khi lấy mảng này nhân với 1 số, thì quy tắc sau đây được ứng dụng: (TRUE được khái niệm là 1, FALSE được khái niệm là 0) - Và cuối cùng, hàm SUM sẽ tính tổng và cho ta kết quả cuối cùng
Tới đây, bạn có thể download file excel để theo dõi cho dễ tại đây
Phối hợp hàm VLOOKUP và SUMIF tra cứu và tổng hợp dữ liệu theo điều kiện
Ví dụ:
Trong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesales và ID của họ, 1 bảng khác chỉ có ID và lợi nhuận. Nhiệm vụ ở ví dụ này: cần đi tính tổng lợi nhuận của bất kì Telesales nào dựa vào tên của họ.
Công thức tất cả chúng ta sẽ sử dụng ở đây là
=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)
VLOOKUP sẽ có nhiệm vụ mang lại mã ID của Telesales với tên tương ứng, dựa vào ID này tất cả chúng ta ứng dụng SUMIF như một ví dụ đơn giản bình thường.
Tính tổng nhiều điều kiện ở cùng 1 cột:
Tất cả chúng ta có ví dụ tính tổng sau đây, điều kiện tính tổng đều năm ở 1 cột: Tính tổng của các giao dịch với đầu mã là 111 và 131
Để làm được điều này, tất cả chúng ta có 3 phương pháp tính, với cách mà công thức có dấu {}, nghĩa là công thức mảng, bạn cần nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau thời điểm nhập công thức để được kết quả
=SUMIF(A:A,”111*”,C:C)+SUMIF(A:A,”131*”,C:C)
{=SUM(SUMIF(A:A,{“111*”,”131*”},C:C))}
{=SUM(C2:C14*(–(LEFT(A2:A14,3)={“111″,”131”})))}
Vậy là trong bài này, tất cả chúng ta đã cùng nhau tìm hiểu vài cách phối hợp công thức, hàm tính tổng với các hàm dò tìm để khắc phục nhu cầu làm giải trình mà không cần dùng thêm cột phụ. Hi vọng nội dung có ích cho công việc của các bạn nhất là trong công việc kế toán, lập giải trình trong kho.
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:
Một số hàm cơ bản thường gặp như:
- SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
- Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
- Định dạng theo điều kiện với Conditional formatting
- Thiết lập điều kiện nhập dữ liệu với Data Validation
- Cách đặt Name và sử dụng Name trong công thức
- Lập giải trình với Pivot Table…
Các ví dụ Excel nâng cao: phối hợp VLOOKUP, SUM và SUMIF
Cách sử dụng hàm Sumif phối hợp hàm vlookup
Để giúp tất cả chúng ta dễ hình dung được khi nào thì sử dụng hàm Sumif phối hợp hàm vlookup, hãy xem ví dụ sau đây:
Tất cả chúng ta có 1 bảng dữ liệu từ A1:C15, gồm các cột Mã hàng, Ngày bán, Số lượng bán được của từng mặt hàng.
Tuy nhiên yêu cầu của tất cả chúng ta là tính tổng số lượng bán theo Tên mặt hàng được chọn trong ô F11. Trong bảng dữ liệu A1:C15 không có cột tên mặt hàng. Muốn biết Tên mặt hàng đó ứng với Mã hàng nào, tất cả chúng ta phải tham chiếu trong bảng E1:F8.
Như vậy:
- Yêu cầu tính tổng theo điều kiện => Sử dụng hàm SUMIF xác định kết quả
- Điều kiện không xác định được 1 cách trực tiếp mà phải tham chiếu tới 1 bảng khác => Sử dụng VLOOKUP xác định điều kiện
Cách làm như sau:
Viết hàm SUMIF
Cấu trúc của hàm SUMIF bao gồm: =SUMIF(Range, Criteria, Sum_range)
- Range: vùng chứa điều kiện. Ở trong bảng A1:C15 chỉ có cột Mã hàng là có liên quan tới điều kiện => Chọn vùng A2:A15 (không chọn dòng tiêu đề)
- Criteria: điều kiện của đề bài là Tên hàng. Nhưng Range lại là Mã hàng. Do đó phải tham chiếu ra Mã hàng dựa vào Tên hàng => Sử dụng hàm VLOOKUP.
- Sum_range: kết quả cần tính là Số lượng. Do đó chọn vùng C2:C15
Viết hàm VLOOKUP
Tất cả chúng ta muốn tham chiếu Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUP sẽ viết như sau:
=VLOOKUP(giá trị tìm kiếm, vùng tham chiếu, cột chứa kết quả, phương thức tìm kiếm)
- Giá trị tìm kiếm: là tên hàng tại ô F11
- Vùng tham chiếu: Vùng bảng E2:F8 (có thể bỏ qua dòng tiêu đề)
- Cột chứa kết quả: cột Mã hàng là cột thứ 2
- Phương thức tìm kiếm: tìm kiếm chuẩn xác theo tên hàng => nhập số 0
Như vậy ta có VLOOKUP(F11,E2:F8,2,0)
Xem thêm: Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp
Khi phối hợp hai hàm với nhau ta có công thức tại ô F13 như sau:
=SUMIF(A2:A15,VLOOKUP(F11,E2:F8,2,0),C2:C15)
Khi viết hàm SUMIF phối hợp với hàm VLOOKUP, tất cả chúng ta cần phải xác định: hàm VLOOKUP sẽ đặt tại vị trí tham số nào trong hàm SUMIF. Thông thường hay gặp nhất chính là: Điều kiện trong hàm SUMIF không xác định được 1 cách trực tiếp, mà phải tham chiếu tới 1 bảng tính nào đó.
Hướng dẫn cách sử dụng hàm Sumifs phối hợp hàm vlookup trong Excel
Hàm SUMIFS là một hàm rất hữu ích trong Excel giúp tất cả chúng ta có thể tính tổng theo nhiều điều kiện song song. Nhưng trong một số trường hợp điều kiện của giải trình lại không xác định được một cách trực tiếp mà phải tham chiếu tới 1 vùng dữ liệu khác. Khi đó tất cả chúng ta cần phải phối hợp hàm SUMIFS với hàm VLOOKUP. Trong nội dung này Học Excel Online sẽ hướng dẫn các bạn biết sử dụng hàm Sumifs phối hợp hàm vlookup trong Excel.
Trước tiên hãy xét ví dụ sau đây:
Trong yêu cầu trên, tất cả chúng ta thấy có tới 3 điều kiện:
- Điều kiện 1: Từ ngày 01/06/2018
- Điều kiện 2: Đến ngày 30/06/2018
- Điều kiện 3: Tên hàng là Mận Hà Giang loại 2 (tương ứng với mã là M102 trong bảng E1:F8)
Vì vậy để có thể tính được số lượng bán thỏa mãn đồng thời cả 3 điều kiện trên, tất cả chúng ta sẽ cần dùng tới hàm SUMIFS.
Nhưng ở điều kiện 3 là điều kiện liên quan tới tên hàng. Tên hàng không có sẵn trong bảng dữ liệu A1:C15 mà phải xác định thông qua bảng E1:F8. Từ tên hàng (ở ô F13) tham chiếu ra mã hàng tương ứng. Sử dụng mã hàng đó tham chiếu tiếp tới cột Mã hàng trong bảng A1:C15 để ra kết quả cho hàm SUMIFS.
Cách viết hàm SUMIFS
Cấu trúc hàm SUMIFS với 3 điều kiện bao gồm:
=SUMIFS(Vùng tính tổng, vùng điều kiện thứ 1, điều kiện 1, vùng điều kiện thứ 2, điều kiện 2, vùng điều kiện thứ 3, điều kiện 3)
- Vùng tính tổng: Tính tổng số lượng, do đó lấy theo vùng C2:C15
- Vùng điều kiện thứ 1: cột Ngày (vì chứa điều kiện 1 là ngày) vùng B2:B15
- Điều kiện thứ 1: Từ ngày 01/06/2018 là giá trị tại ô F11. Tuy nhiên từ ngày được hiểu là >=, do đó cách viết là “>=”&F11
- Vùng điều kiện thứ 2: cột Ngày (vì chứa điều kiện 2 là ngày) vùng B2:B15
- Điều kiện thứ 2: Đến ngày 30/06/2018 là giá trị tại ô F12. Tuy nhiên từ ngày được hiểu là <=, do đó cách viết là “<=”&F12
- Vùng điều kiện thứ 3: cột Mã hàng, vùng A2:A15
- Điều kiện thứ 3: Mã hàng, được xác định bởi hàm Vlookup tham chiếu theo Tên hàng được chọn ở ô F13
Cách viết hàm VLOOKUP
húng ta muốn tham chiếu Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUP sẽ viết như sau:
=VLOOKUP(giá trị tìm kiếm, vùng tham chiếu, cột chứa kết quả, phương thức tìm kiếm)
- Giá trị tìm kiếm: là tên hàng tại ô F13
- Vùng tham chiếu: Vùng bảng E2:F8 (có thể bỏ qua dòng tiêu đề)
- Cột chứa kết quả: cột Mã hàng là cột thứ 2
- Phương thức tìm kiếm: tìm kiếm chuẩn xác theo tên hàng => nhập số 0
Như vậy ta có VLOOKUP(F13,E2:F8,2,0)
Khi phối hợp 2 hàm này, tất cả chúng ta có kết quả tại ô F15 như sau:
=SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"<="&F12,A2:A15,VLOOKUP(F13,E2:F8,2,0))
Hãy thử thay đổi tên hàng tại ô F13 để xem kết quả của hàm SUMIFS tại ô F15 thay đổi đúng không nhé.
Tổng kết
Hàm SUMIFS có thể viết được với rất nhiều điều kiện. Khi đó tất cả chúng ta phải xác định rõ: Điều kiện đó có thể xác định trực tiếp được trong bảng dữ liệu không.
- Nếu có: Tham chiếu trực tiếp điều kiện đó (như giá trị Từ ngày, đến ngày ở ví dụ trên)
- Nếu không: Phải sử dụng các hàm phối hợp (như hàm VLOOKUP, hàm DATE, hàm EOMONTH…) để giúp từ các điều kiện đó gián tiếp tạo ra 1 điều kiện có thể xác định trực tiếp được trong vùng dữ liệu gốc.
Đây là một trong những kỹ thuật cơ bản giúp lập giải trình theo nhiều điều kiện một cách chuẩn xác, nhanh chóng.
Chúc các bạn ứng dụng tốt tri thức này vào công việc nhé!
Rất nhiều tri thức phải không nào? Toàn bộ những tri thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới Chuyên Viên của Học Excel Online. Đây là khóa học giúp bạn hệ thống tri thức một cách đầy đủ, cụ thể. Hơn nữa không hề có hạn chế về thời gian học tập nên bạn có thể thoải mái học bất kể lúc nào, dễ dàng tra cứu lại tri thức khi cần. Hiện tại hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Cụ thể xem tại: HocExcel.Online
