Bài viết bên dưới đây chúng ta sẽ cùng mọi người trong nhà tìm hiểu cụ thể về biện pháp sử dụng những hàm tìm kiếm kiếm và tính tổng trong Excel: VLOOKUP, SUM và 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 bắt đầu với lấy ví dụ như đầu tiên. Yêu mong của lấy ví dụ này đó đó là thực hiện nay tính tổng sản lượng của Cam trong tháng 1,2,3 bằng cách dùng công thức sau đây:

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

*

Sau khi chúng ta đã triển khai nhập phương pháp này, hãy chú ý rằng vì đó là công thức mảng, nên sau khi 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 để có thể thực hiện nhập được cách làm và mang đến ra hiệu quả đúng nhất.

Để chúng ta có thể hiểu hơn, ta sẽ cùng mọi người trong nhà phân tích cách làm mảng này bằng câu hỏi đư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 phương pháp 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ó thể sẽ đưa ra câu hỏi, vì sao ta lại không áp dụng công thức tiếp sau đây để tính tổng

=SUM(B2:D2)

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

*

Liên hệ ví dụ sinh hoạt trên, chúng ta có thể thực hiện sửa chữa thay thế hàm SUM bằng 1 số ít hàm khác bất cứ khi nào để giao hàng mục đích tuyệt nhất định của chúng ta như hàm MIN, MAX, AVERAGE hay các phép toán khác. Hãy xem xét rằng những công thức này số đông sẽ là công thức mảng nên các bạn vẫn luôn cần dùng đến tổ hợp phím CTRL + SHIFT + ENTER khi thực hiện nhập công thức này trong Excel.

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, có 2 bảng dữ liệu, trong những số đó bảng đầu tiên gồm có sản phẩm và đối chọi giá cùng bảng sản phẩm 2 bao gồm khách hàng, thành phầm cùng số lượng sản phẩm mà người tiêu dùng đã mua. Nhiều người đang muốn đi tính tổng giá bán trị của một khách hàng

*


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

*

Tham khảo các công thức mặt dưới:

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

H2=F2*G2

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

Đối với trường hợp không thêm được cột phụ, chúng ta có thể sử dụng phương pháp 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 hợp SUM cùng LOOKUP này:

Cột A hôm nay phải được sắp xếp theo lắp thêm tự tăng mạnh hoặc là tự A cho Z, nhằm 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 bài toán dùng tổng hợp phím CTRL + SHIFT + ENTER. Nếu như không thích sử dụng tổ hợp phím này, chúng ta cũng có thể thay hàm SUM bằng hàm SUMPRODUCT

Công thức này được hiểu như thế nào?

Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) sẽ đến ta tác dụng như là cột solo giá trong 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 cực hiếm đúng cùng sai, khi tiến hành lấy mảng này nhân với cùng 1 số, quy tắc tiếp sau đây sẽ được áp dụng: (TRUE được khái niệm là 1, FALSE được quan niệm là 0)Cuối cùng, hàm SUM sẽ triển khai tính tổng và mang lại ta kết 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 một ví dụ này, ta sẽ có được 2 bảng, 1 bảng sẽ là tên gọi Telesale cùng ID của mình và 1 bảng không giống chỉ tất cả ID với doanh số. Nhiệm vụ của người sử dụng ở lấy ví dụ này kia là buộc phải đi tính tổng doanh thu của bất kỳ Telesale nào dựa trên tên của họ.

Xem thêm: Yêu Em Hơn Cả Tử Thần : Khi Tình Yêu Xoa Dịu Vết Thương Lòng

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

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

VLOOKUP từ bây giờ sẽ có trọng trách đưa lại mã ID của Telesale với thương hiệu tương ứng, rồi sau đó nhờ vào ID này ta áp dụng hàm SUMIF như 1 ví dụ đơn giản 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 hồ hết nằm tại 1 cột: triển khai tính tổng của những giao dịch với đầu mã là 111 cùng 131

*

Để hoàn toàn có thể làm được điều này, ta tất cả 3 bí quyết tính. Trong những số đó 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ổ hợp phím tắt CTRL + SHIFT + ENTER sau thời điểm đã nhập cách làm để đượ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ố cách để kết hòa hợp công thức, các hàm tính tổng cùng với số đông hàm dò tìm, trường đoản cú đó giải quyết được các nhu cầu làm báo cáo nhanh chóng, dễ dãi mà không cần dùng mang đến cột phụ. Hy vọng những thông tin chia sẻ trong nội dung bài viết trên đây sẽ giúp đỡ ích được cho chính 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,…