Với đa số ai liên tiếp xử lý bảng dữ liệu Excel và tính toán các số liệu, thì chắc chắn là sẽ biết đến 2 hàm Excel thông dụng là Sumif cùng hàm Vlookup. Hàm Sumif dùng để tính tổng những giá trị bao gồm điều kiện, còn hàm Vlookup để tìm kiếm quý giá trong mảng. Cùng việc phối hợp 2 hàm Sumif cùng hàm Vlookup trong Excel cùng được vận dụng rất nhiều.
Bạn đang xem: Cách dùng hàm vlookup và sumif
Bài viết dưới đây sẽ giới thiệu cho chính mình cách phối kết hợp của 2 hàm Sumif và hàm Vlookup bên trên Excel.
1. Biện pháp dùng hàm Sumif Excel:
Khi hàm Sum chất nhận được người dùng tính tổng các giá trị của một vùng dữ liệu nào đó, thì hàm Sumif sẽ giúp bạn có thể tính tổng vùng tài liệu mà bỏ qua mất giá trị như thế nào trong dãy. Bạn cũng có thể loại quý giá nào, áp dụng giá trị như thế nào kèm với đk để tính tổng.
Bạn đọc hoàn toàn có thể tham khảo biện pháp áp dụng chi tiết hàm Sumif trong bài viết Cách sử dụng hàm SUMIF vào Excel.
2. Phương pháp dùng hàm Vlookup trong Excel:
Hàm Vlookup trên Excel dùng để tìm kiếm dữ liệu trong một mảng như thế nào đó. Hàm có thể chấp nhận được người sử dụng tra cứu tài liệu trên một chuỗi nhất định theo những điều kiện mang đến trước. Hàm Vlookup hay được thực hiện ở những kiểu bảng dữ liệu tra cứu mã học tập sinh, mã nhân viên... Phương pháp dùng hàm Vlookup trong Excel khá nhiều dạng, linh hoạt, vào đó, phối kết hợp Sumif với Vlookup là một trường hợp sử dụng khá phổ biến.
3. Cách kết hợp hàm Sumif và Vlookup:
Với gần như bảng tài liệu cần tìm kiếm đối tượng, dữ liệu có đk kèm theo thì hàm Sumif và hàm Vlookup sẽ tìm tìm nhanh dữ liệu hơn, kết quả đúng mực ngay cả khi biến hóa đối tượng. Đặc biệt bạn không cần phải gõ lại công thức.
Để làm rõ hơn về phong thái áp dụng hàm Sumif và hàm Vlookup, chúng ta sẽ có ví dụ như dưới đây.
Bảng lợi nhuận dưới đây bao gồm 3 bảng nhỏ tuổi với 3 câu chữ khác nhau. Bảng 1 sẽ là nhân viên kèm mã số của từng người, Bảng 2 vẫn là mã số nhân viên cấp dưới và doanh thu đạt được của từng người. Bảng 3 sẽ là bảng điền hiệu quả và được nhằm trống.
Nội dung của bài bác đó là nhập tác dụng tên nhân viên cũng như doanh số của fan đó đạt được vào Bảng 3. Ngoài ra có thể tra cứu giúp doanh số của những nhân viên không giống khi biến đổi họ tên tương ứng.
Ở đây bạn cần sử dụng cho 2 hàm Sumif với hàm Vlookup, nhằm tính tổng doanh thu nhân viên với điều kiện cho trước.
Nếu chỉ sử dụng hàm Sumif thì họ không thể tính tổng doanh thu của nhân viên cấp dưới vì cột Mã SV ko ở thuộc 1 bảng. Như vậy, bạn phải đến hàm Vlookup nhằm tìm mã số nhân viên tưng ứng với từng người, rồi kết hợp thêm hàm Sumif để tính tổng lợi nhuận của nhân viên có kèm điều kiện.
Bước 1:
Chúng ta sẽ vận dụng công thức vào bảng. Phương pháp sẽ là:
=SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)
Trong đó:
Sumif với Vlookup là hàm tính tổng với hàm search kiếm tất cả điều kiện.D:D là vùng chứa những ô điều kiện.B12 là vùng giá chỉ trị so sánh với cột doanh số, là giá trị cần tìm kiếm. Khi đổi khác tên thì cột lợi nhuận cũng thay đổi theo.A3:B7 là vùng dữ liệu cột phải lấy dữ liệu để dò tìm giá bán trị đến vùng B12 ở bên trên.Số 2 là trang bị tự xuất quý giá hiển thị lên màn hình, phụ thuộc vào cột đề nghị lấy tài liệu có từng nào cột. Ở phía trên cột nên lấy dữ liệu là Mã NV ở đoạn thứ 2 cột B cần thứ tự sẽ là 2.Flase là phạm vi kiếm tìm kiếm hoàn hảo nhất cho kết quả đúng đắn thay vì áp dụng True cho hiệu quả tương đối.E:E là khu vực cho trước lợi nhuận của từng nhân viên cấp dưới đạt được.Bước 2:
Bạn đã nhập công thức bên trên tại ô C12 sống Bảng 3, rồi điền tên nhân viên cấp dưới muốn tính tổng doanh thu tại ô B12. Ở phía trên tôi sẽ tính tổng số lệch giá của nhân viên Phí Thanh Lan.
Ngay sau đó sẽ hiển thị tổng doanh thu mà nhân viên này đạt được. Tổng cộng tiền hoàn toàn chính xác.
Lưu ý với những người dùng trong trường đúng theo khi tính tổng doanh thu và không hiển thị vệt phẩy phân cách các lớp sản phẩm trong dãy số, bạn cũng có thể tham khảo bài viết Cách phân cách hàng trăm bằng vết phẩy trong Excel nhằm hiển thị lại vết phẩy phân làn số trong Excel
Chúng ta sẽ đề xuất đổi cột đó về định hình Number là số, rồi kiểm soát và điều chỉnh để hiển thị dấu phẩy ngăn cách trong Format Cells. Phần Decimal places bạn có thể tùy chỉnh tùy theo số mà bạn phải tính toán. Để dễ hơn có thể nhìn vào phần Sample.
Bạn bao gồm thể đổi khác định dạng của cột trước hoặc sau thời điểm cho ra hiệu quả tính phần đông được.
Bước 3:
Bây giờ bạn có thể đổi tên của bất cứ nhân viên nào, không yêu cầu nhập cách làm tính khác mà vẫn đồng ý cho ra tác dụng chính xác. Lấy ví dụ tôi đang nhập vào ô B12 tên nhân viên Trần Thu Hà với mã số nhân viên là MS01, công dụng doanh thu bắt buộc tìm kiếm vẫn đồng ý cho ra hiệu quả chính xác.
Trên đấy là bài phía dẫn chi tiết cách kết hợp hàm Sumif cùng hàm Vlookup trên Excel. Hai hàm Sumif với hàm Vlookup là hàm tính toán, hàm search kiếm dữ liệu vô cùng căn bản trên Excel. Với việc kết hợp 2 hàm lại sẽ giúp người dùng hoàn toàn có thể tìm kiếm dữ liệu nhanh hơn, mà lại không cần tính toán bằng tay cho cho dù đổi dữ liệu tìm kiếm đi chăng nữa.
Nghe nói tới hai hàm SUMIFvà VLOOKUPthì thân quen gì với người dùng Excel, nhị hàm “chuyên cần” này khôn cùng được ưu dùng. Tuy nhiên rằng với những các bạn mới có tác dụng quen cùng với Excelthì kiên cố cũng không biết đến việc phối hợp hai hàm trên sẽ đã cho ra một giải pháp làm hoàn hảo đối với công việc của các bạn và việc dùng hàm SUMIF cùng hàm
VLOOKUP được sử dụng rất thông dụng cũng rất công dụng trong Excel. Khi nhì hàm hợp tác và ký kết để xử lý các bước sẽ khiến cho người dùng cảm thấy vô thuộc hài lòng. Giải pháp kết hợphàm Sumif và Vlookup thật dễ dàng qua quá trình làm dưới đây.
1. Hàm SUMIF cùng hàm VLOOKUP
Hàm SUMIF
Hàm SUMIF vào Excel
Hàm SUMIF là một trong hàm để tính tổng các ô đáp ứng một tiêu chuẩn duy nhất. SUMIF hoàn toàn có thể được sử dụng để tính tổng những ô dựa vào ngày, số và văn bạn dạng phù hợp với tiêu chí vắt thể
Công thức hàm Sumif:
= SUMIF (phạm vi, tiêu chí,
- Phạm vi ( range) phạm vi của các ô mà bạn muốn áp dụng tiêu chí.
- tiêu chí ( Criteria ) các tiêu chuẩn được áp dụng để khẳng định các ô đề xuất thêm.
-
Ví dụ minh họa hàm SUMIF:Cho bảng dữ liệu sau bao gồm thông tin sản phẩm và số lượng hàng hóa đã bán, yêu ước tính tổng giá chỉ trị sản phẩm thuộc hãng sản xuất OPPO.
Ví dụ về hàm Sumif - Hình 1
- bước 1: Nhập công thức=SUMIF(C3:C11;C14;D3:D11) vào ô bạn có nhu cầu hiển thị kết quả.
Trong đó: C3:C11 tìm hãng sản xuất là OPPO
- bước 2: Nhấn Enter để dấn kết quả.
Ví dụ về hàm Sumif - Hình 2
Hàm Vlookup
Hàm Vlookup ( viết tắt của tra cứu dọc ) là một hàm Excel tích thích hợp được tạo nên để tra cứu và truy xuất dữ liệu xuất phát điểm từ 1 cột ví dụ trong bảng. Đối cùng với một cực hiếm được chỉ định, hàm sẽ tìm hoặc tra cứu giá trị trong một cột dữ liệu và trả về quý hiếm tương ứng từ 1 cột khác.
Hàm Vlookup trong Excel
Công thức của hàm Vlookup:
= VLOOKUP (giá trị, bảng, col_index,
- cực hiếm ( value ) giá bán trị đề xuất tìm vào cột thứ nhất của bảng.
- Bảng ( Table_array ) là bảng để đưa giá trị.
- Col_index cột vào bảng để lấy giá trị.
Xem thêm: Top Songs: Bigbang Đặc Sắc Nhất, Bigbang Đặc Sắc Nhất
- Phạm vi_lookup -
Ví dụ minh họa về hàm Vlookup:Cho bảng thông tin thành phầm như bộc lộ dưới hình. Yêu mong tìm giá bán của sản phẩm.
- bước 1:Nhập công thức=VLOOKUP(C14;A2:D11;4;0) vào ô bạn có nhu cầu hiển thị kết quả.
- cách 2:Nhấn Enter để thừa nhận kết quả.
Ví dụ về hàm Vlookup
2. Phối hợp hàm Sumif với hàm Vlookup
Để biết được cách phối hợp hai hàm SUMIF kết hợp
VLOOKUPtrong ví dụ tiếp sau đây sẽ trình làng một giải pháp chi tiết.
Ví dụ đến trang tính bao gồm 3 bảng dữ liệu khác biệt như mặt dưới:
Ví dụ kết hợp hàm SUMIFvà hàm
VLOOKUP. Hình 1
=> mục tiêu của lấy một ví dụ là tìm lợi nhuận ứng cùng với tên tương ứng cho bảng 3.
Nếu như chỉ vận dụng hàm tính tổng Sumif để tiến hành bài toán này thì sẽ không ho ra câu trả lời đầy đủ, vậy nên kết hợp Sumif với Vlookup để tiến hành việc tìm kiếm mã của mỗi nhân viên và tính lợi nhuận theo đk của nhân viên.
Các cách tính được tiến hành dùng công thức kết hợp hàm cùng những giá trị ví dụ trong trang tính.
Công thứchàm vlookup kết hợp sumif như sau:
=SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)
Trong đó:
-Sumif với Vlookup là hàm tính tổng và hàm tìm kiếm kiếm gồm điều kiện.
-D:D là vùng chứa những ô điều kiện.
-B12 là vùng giá trị đối chiếu với cột doanh số, là giá chỉ trị cần tìm kiếm. Khi biến hóa tên thì cột doanh thu cũng đổi khác theo.
- A3:B7 là vùng dữ liệu cột nên lấy dữ liệu để dò tìm giá chỉ trị mang lại vùng B12 ở mặt trên.
- Số 2 là trang bị tự xuất cực hiếm hiển thị lên màn hình, phụ thuộc vào cột phải lấy tài liệu có bao nhiêu cột. Ở phía trên cột đề xuất lấy dữ liệu là Mã NV ở chỗ thứ 2 cột B buộc phải thứ tự đã là 2.
- Flase là phạm vi tìm kiếm tuyệt đối cho kết quả đúng chuẩn thay vì thực hiện True cho hiệu quả tương đối.
- E:E là khoanh vùng cho trước lệch giá của từng nhân viên đạt được.
Dùng phương pháp trên nhập vào trong bảng 3 ngơi nghỉ C12, trong ví dụ tính lệch giá cho phí tổn Lan, nên ở đoạn họ tên nhập tên của chi phí Lan:
Ví dụ phối hợp hàm SUMIF vàhàm VLOOKUP. Hình 2
Khi nhập hoàn thành công thức, sẽ đã cho ra ngay hiệu quả doanh thu của chi phí Lan với lợi nhuận là 6500000.
Và khi đổi tên thay bằng bất kỳ tên làm sao vẫn không thay đổi công thức và mang đến ra hiệu quả số doanh thu tương ứng và đúng chuẩn của người đấy.
3. Một trong những lỗi thường gặp gỡ khi phối hợp hàm SUMIF cùng với hàm VLOOKUP
Trong quá trình kết hợp giữa hàm vlookup phối kết hợp hàm sumif sẽ xảy ra 3 lỗi cơ bạn dạng dưới đây:
Lỗi #N/A
- lý do xảy ra lỗi #N/A là do nhập thiếu thốn đối số hoặc đối số sum_range chưa hẳn dải số.
- hạn chế và khắc phục lỗi này bằng cách kiếm tra lại các đối số xem vẫn nhập đủ cùng đúng chưa khi sử dụng hàm SUMIF.
Lỗi #N/A
Lỗi trả về 0
- tại sao xảy ra lỗi là do những đối số khai báo sai thứ hạng dữ liệu.
- khắc phục lỗi này bằng phương pháp kiểm tra những đối số xem sẽ nhập đúng tuyệt chưa.
Lỗi trả về 0
Lỗi #ERROR
- tại sao của lỗi này là do nhập không nên cú pháp hàm.
- khắc phục lỗi này bằng phương pháp kiểm tra các đối số và cách trình diễn xem đang nhập đúng tuyệt chưa.
Lỗi #ERROR
4. Một số chú ý khi phối kết hợp hàm SUMIF với hàm VOOKUP
- Cả nhì hàm Vlookup và hàm Sumif hồ hết không riêng biệt chữ hoa.
- Hàm Vlookup có 2 kiểu tìm tìm là tuyệt vời và hoàn hảo nhất và tương đối.
- Hàm Vlookup chỉ tra cứu kiếm tài liệu từ trái qua phải
- Đối số Range và sum_Range phải tất cả cùng kích cỡ và ngang nhau.
- Hàm SUMIFS kết hợp Vlookupcó thể đo lường và thống kê nhiều điều kiện.
Vậy bài toán kết thích hợp hai hàm SUMIF vàhàm VLOOKUP để áp dụng làm lấy một ví dụ trên trở nên dễ dàng và đơn giản khi tiến hành việc tìm tổng lệch giá của mỗi cá thể kèm theo điều kiện.
Ngoài ra,chúng tôi còn có một số khóa đào tạo và huấn luyện liên quan mang lại khóa học tập Excel trực tuyến đường tại chuyenly.edu.vn khác, bạn đọc nhiệt tình hãy mày mò và hối hả sở hữu hầu hết khóa học cân xứng nhất với bạn nhé.
