Bài viết bên dưới đây chúng ta sẽ cùng nhau tìm hiểu cụ thể về giải pháp sử dụng những hàm tra cứu kiếm với tính tổng vào Excel: VLOOKUP, SUM cùng SUMIF.

Bạn đang xem: Hàm tìm kiếm và tính tổng trong excel


KẾT HỢP HÀM VLOOKUP VÀ SUMIF

Chúng ta ban đầu với ví dụ đầu tiên. Yêu mong của lấy ví dụ như này đó đó là thực hiện tính tổng sản lượng của Cam vào tháng 1,2,3 bằng phương pháp dùng bí quyết sau đây:

=SUM(VLOOKUP( A2 , A1:I8 , 2,3,4 , FALSE ))

*

Sau khi bạn đã thực hiện nhập cách làm này, hãy lưu ý rằng vì đó là công thức mảng, nên sau khoản thời gian tiến hành nhập công thức, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để hoàn toàn có thể thực hiện tại nhập được bí quyết và mang lại ra tác dụng đúng nhất.

Để chúng ta có thể hiểu hơn, ta sẽ cùng nhau phân tích bí quyết mảng này bằng vấn đề đưa ra 3 công thức tương đương như sau:

=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )

Phần 2,3,4 nằm trong cách làm mảng phía trên tức là cột 2,3,4 trong mảng tài liệu A1:I8. Chắc rằng khi đọc cho đây các chúng ta cũng có thể sẽ đề ra câu hỏi, bởi sao ta lại không áp dụng công thức dưới đây để tính tổng

=SUM(B2:D2)

Lí giải về thắc mắc này như sau, mục tiêu đưa ra những công thức phức tạp này là để họ sẽ giao hàng cho việc tạo báo cáo hoặc dashboard. Nếu như khách hàng dùng một công thức để tính tổng đối chọi thuần như là hàm SUM thì lúc bạn có một ô chứa các loại hàng hóa và muốn thay đổi ô này để triển khai tính tổng sản lượng cho một loại sản phẩm thì sẽ rất lâu cho việc thay đổi công thức SUM theo. Vày thế, khi sử dụng công thức mảng kết hợp SUM cùng VLOOKUP, các bạn sẽ dễ dàng tạo thành một báo cáo về sản lượng cho các thành phầm như hình minh họa bên dưới đây:

*

Liên hệ ví dụ ngơi nghỉ trên, chúng ta có thể thực hiện thay thế hàm SUM bằng một số hàm không giống bất cứ khi nào để giao hàng mục đích duy nhất định của công ty như hàm MIN, MAX, AVERAGE hay những phép toán khác. Hãy chú ý rằng những công thức này các sẽ là bí quyết mảng nên các bạn vẫn luôn cần cần sử dụng đến tổ hợp phím CTRL + SHIFT + ENTER khi triển khai nhập công thức này vào Excel.

Xem thêm: Cách Tết Tóc Xương Cá Cho Bạn Gái Tự Tin Dạo Phố Ngày Tết, Cách Để Tết Tóc Đuôi Cá

TỔNG HỢP DỮ LIỆU KHÔNG THÊM CỘT PHỤ, KẾT HỢP HÀM LOOKUP VÀ SUM:

Ta có ví dụ như sau, gồm 2 bảng dữ liệu, trong đó bảng đầu tiên gồm có sản phẩm và đối chọi giá với bảng trang bị 2 bao gồm khách hàng, thành phầm cùng số số lượng hàng hóa mà quý khách đã mua. Ai đang muốn đi tính tổng giá bán trị của 1 khách hàng

*


Bình thường, bạn sẽ cần sử dụng cột phụ như sau:

*

Tham khảo những công thức bên dưới:

G2=VLOOKUP(E2,bang_SP,2,FALSE)

H2=F2*G2

K2=SUMIF(D:D,K1,H:H)

Đối với trường đúng theo không thêm được cột phụ, bạn cũng có thể sử dụng công thức sau trên 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))

Chú ý rằng khi sử dụng công thức phối kết hợp SUM với LOOKUP này:

Cột A lúc này phải được bố trí theo đồ vật tự tăng đột biến hoặc là tự A mang lại Z, để hàm LOOKUP hoàn toàn có thể cho ta giá tốt trị đúng.Đảm nói rằng công thức được nhập vào bằng câu hỏi dùng tổ hợp phím CTRL + SHIFT + ENTER. Nếu như không muốn sử dụng tổ hợp phím này, chúng ta cũng có thể thay hàm SUM bởi 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) sẽ mang đến ta công dụng như là cột 1-1 giá vào hình chụp phía trênPhần $F$2:$F$8: mảng con số các sản phẩmPhần ($D$2:$D$8=K1) tạo ra 1 mảng bao gồm các quý giá đúng và sai, khi triển khai lấy mảng này nhân với 1 số, quy tắc sau đây sẽ được áp dụng: (TRUE được tư tưởng là 1, FALSE được quan niệm là 0)Cuối cùng, hàm SUM sẽ thực hiện tính tổng và mang đến ta hiệu quả cuối cùng

KẾT 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ụ sau đây:

*


Tại lấy ví dụ này, ta sẽ sở hữu được 2 bảng, 1 bảng sẽ là tên Telesale với ID của mình và 1 bảng không giống chỉ có ID với doanh số. Nhiệm vụ của công ty ở ví dụ này kia là bắt buộc đi tính tổng doanh thu của bất kể Telesale nào dựa trên tên của họ.

Công thức được sử dụng tại chỗ này sẽ là

=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)

VLOOKUP bây giờ sẽ có trọng trách đưa lại mã ID của Telesale với tên tương ứng, rồi sau đó phụ thuộc ID này ta vận dụng hàm SUMIF như 1 ví dụ dễ dàng bình thường.

TÍNH TỔNG NHIỀU ĐIỀU KIỆN Ở CÙNG 1 CỘT:

Ta có ví dụ tính tổng sau đây, trong các số đó điều khiếu nại tính tổng đa số nằm ở một cột: thực hiện tính tổng của những giao dịch cùng với đầu mã là 111 cùng 131

*

Để có thể làm được điều này, ta gồm 3 giải pháp tính. Trong các số ấy với giải pháp mà công thức có dấu , có nghĩa là công thức mảng, bạn cần nhớ nhấn tổng hợp phím tắt CTRL + SHIFT + ENTER sau khi đã 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à chúng ta đã vừa thuộc nhau tò mò về một số phương pháp để kết phù hợp công thức, những hàm tính tổng cùng với phần nhiều hàm dò tìm, từ đó xử lý được các yêu cầu làm report nhanh chóng, dễ dàng mà không cần dùng mang đến cột phụ. Mong muốn những thông tin chia sẻ trong nội dung bài viết trên đây sẽ giúp đỡ ích được cho mình nhiều vào công việc, nhất là lĩnh vực kế toán, lập report trong kho,…