HÀM TÌM KIẾM VÀ TÍNH TỔNG TRONG EXCEL

Trong nội dung bài viết này, mình đang giải đáp chúng ta làm vậy như thế nào để rất có thể phối kết hợp những hàm VLOOKUP.., SUM và SUMIF để dò tìm và tính tổng dựa vào một số ít điều kiện.

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

Mục lục

Kết phù hợp hàm VLOOKUP cùng SUMIFTổng phù hợp dữ liệu ko thêm cột phú, phối kết hợp hàm LOOKUPhường. cùng SUMKết vừa lòng hàm VLOOKUPhường cùng SUMIF tra cứu với tổng hòa hợp tài liệu theo điều kiệnTính tổng nhiều điều kiện sống cùng 1 cột

Kết hợp hàm VLOOKUPhường cùng SUMIF

Trước lúc ban đầu vào bài xích này, chắc hẳn chúng ta đang thành thạo cùng với hàm VLOOKUP với hàm SUMIF rồi. Vậy nên bạn có thể bắt đầu vào ngay lập tức ví dụ đầu tiên.


*

Trong ví dụ này, chúng ta đang đi tính tổng sản lượng của Cam hồi tháng 1, mon 2, mon 3 bằng phương pháp sử dụng phương pháp sau đây:

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

Sau lúc nhập công thức này, các bạn để ý bởi đó là cách làm mảng, vậy đề xuất sau khi nhập phương pháp, chúng ta bắt buộc thực hiện tổ hợp phím CTRL + SHIFT + ENTER để rất có thể nhập được bí quyết cùng bao gồm hiệu quả đúng.

Để các bạn có thể phát âm rộng, bọn họ vẫn phân tích phương pháp mảng này bằng cách đưa ra 3 cách làm tương tự 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 nghỉ ngơi trong bí quyết mảng bên trên tức thị cột 2,3,4 trong mảng tài liệu A1:I8. Đọc đến đây, rất có thể các bạn cũng có thể sẽ sở hữu được thắc mắc, tại sao họ ko thực hiện bí quyết dưới đây để tính tổng

=SUM(B2:D2)

Mục đích chỉ dẫn hồ hết công thức phức tạp này, chúng ta vẫn giao hàng cho câu hỏi chế tạo báo cáo hoặc dashboard. Nếu họ sử dụng một phương pháp tính tổng đơn thuần bằng hàm SUM, Lúc họ có 1 ô đựng các loại hàng hoá, ao ước thay đổi ô này nhằm tính tổng sản lượng của 1 sản phẩm, họ bắt buộc chuyển đổi phương pháp SUM theo.

Xem thêm: Năm Nhuận Là Gì, Có Bao Nhiêu Ngày? Cách Tính Năm Nhuận Cách Tính Năm Nhuận

Với bí quyết mảng phối kết hợp hàm SUM và VLOOKUP nlỗi ở bên trên, chúng ta cũng có thể tạo nên 1 báo cáo về sản lượng của các sản phẩm 1 cách nhanh chóng nhỏng sau:

Từ ví dụ bên trên, tất nhiên, ta hoàn toàn có thể thế hàm SUM bằng 1 số ít hàm không giống nhằm giao hàng mục đích của bọn họ như hàm AVERAGE, hàm MIN, hàm MAX hoặc triển khai những phnghiền tính toán khác … Và để ý bởi những công thức này những là công thức mảng, phải bạn cần sử dụng tổ hợp phím tắt CTRL + SHIFT + ENTER Lúc nhập công thức này trong Excel.

Tổng hợp dữ liệu ko thêm cột prúc, phối kết hợp hàm LOOKUPhường với SUM:

Chúng ta có ví dụ sau, vào ví dụ này, chúng ta bao gồm 2 bảng tài liệu, 1 bảng có tất cả thành phầm và solo giá; bảng thứ 2 bao gồm quý khách hàng, thành phầm với số số lượng hàng hóa quý khách hàng đang thiết lập. Chúng ta vẫn ao ước đi tính tổng vốn của một khách hàng hàng


Excel nâng cấp, kết hợp hàm SUM và LOOKUP


Bình hay, họ vẫn đề xuất cần sử dụng cột prúc nhỏng sau:

Các bí quyết như sau:

G2=VLOOKUP(E2,bang_SPhường,2,FALSE)

H2=F2*G2

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

Trong trường hợp ko thêm được cột prúc, chúng ta cũng 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))

Chụ ý lúc sử dụng cách làm kết hợp SUM cùng LOOKUPhường. này:

Cột A buộc phải được bố trí theo thiết bị từ bỏ tăng cao hoặc tự A cho Z, để hàm LOOKUP cho họ cực hiếm đúng.Công thức được nhập vào bằng tổng hợp phím CTRL + SHIFT + ENTER. Nếu không sử dụng tổng hợp phím này, bạn có thể nạm hàm SUM bởi 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) cho họ tác dụng nlỗi cột đối chọi giá chỉ vào hình chụp phía trênPhần $F$2:$F$8 là mảng số lượng các sản phẩmPhần ($D$2:$D$8=K1)  tạo nên 1 mảng tất cả những quý giá đúng và không nên, khi rước mảng này nhân với cùng 1 số, thì nguyên tắc sau đây được áp dụng: (TRUE được định nghĩa là 1 trong, FALSE được quan niệm là 0)Và sau cuối, hàm SUM và tính tổng với mang lại ta kết quả cuối cùng

Tới phía trên, chúng ta cũng có thể download file excel để quan sát và theo dõi mang lại dễ tại đây

Kết phù hợp hàm VLOOKUP và SUMIF tra cứu vớt cùng tổng đúng theo dữ liệu theo điều kiện

Ví dụ:


Excel cải thiện, phối hợp VLOOKUP với SUMIF


Trong ví dụ này, ta có 2 bảng, 1 bảng là tên gọi Telesale với ID của mình, 1 bảng khác chỉ có ID cùng lợi nhuận. Nhiệm vụ làm việc ví dụ này: đề xuất đi tính tổng doanh số của bất kể Telesale nào dựa vào thương hiệu của họ.

Công thức chúng ta đã áp dụng tại chỗ này là

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

VLOOKUP. sẽ có nhiệm vụ đưa lại mã ID của Telesale với thương hiệu khớp ứng, nhờ vào ID này họ áp dụng SUMIF nlỗi một ví dụ dễ dàng bình thường.

Tính tổng những ĐK sinh hoạt cùng 1 cột:

Chúng ta bao gồm ví dụ tính tổng tiếp sau đây, điều kiện tính tổng các năm ở 1 cột: Tính tổng của các thanh toán giao dịch cùng với đầu mã là 111 với 131

Để làm cho được vấn đề đó, họ tất cả 3 cách tính, với phương pháp cơ mà phương pháp có lốt , tức là cách làm mảng, bạn cần nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khoản thời gian 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à trong bài này, bọn họ đã cùng nhau khám phá một trong những phương pháp kết hợp bí quyết, hàm tính tổng cùng với các hàm dò tìm để xử lý nhu cầu làm cho báo cáo nhưng không đề xuất cần sử dụng thêm cột phụ. Hi vọng bài viết hữu ích đến các bước của chúng ta nhất là trong quá trình kế toán thù, lập báo cáo trong kho.

Bên cạnh đó nhằm ứng dụng Excel vào các bước một cách hiệu quả thì chúng ta còn yêu cầu sử dụng xuất sắc các hàm, những quy định khác của Excel:

Một số hàm cơ bản thường xuyên gặp gỡ như:

SUMIF, SUMIFS để tính tổng theo 1 ĐK, nhiều điều kiệnCOUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, những điều kiệnCác hàm cách xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tra cứu tmê man chiếu Index+Match, hàm SUMPRODUCT…

Một số lao lý hay sử dụng như:

Định dạng theo điều kiện với Conditional formattingThiết lập điều kiện nhập dữ liệu cùng với Data ValidationCách đặt Name và thực hiện Name vào công thứcLập report cùng với Pivot Table…

Rất các kiến thức đề nghị không nào? Toàn cỗ gần như kỹ năng này các bạn đầy đủ rất có thể học tập được trong khóa học EX101 – Excel từ cơ bản tới chăm gia của Học Excel Online. Đây là khóa đào tạo giúp đỡ bạn hệ thống kiến thức và kỹ năng một giải pháp không thiếu thốn, cụ thể. Ngoài ra không còn có số lượng giới hạn về thời hạn tiếp thu kiến thức đề nghị bạn có thể dễ chịu và thoải mái học tập bất cứ lúc như thế nào, thuận lợi tra cứu lại kiến thức Khi đề xuất. Hiện nay khối hệ thống đang sẵn có ưu tiên rất lớn cho bạn Lúc ĐK tyêu thích gia khóa học. Chi huyết coi tại: goldmarkcity-hanoi.com.Online