Cập nhật nội dung chi tiết về Cách Kết Hợp Hàm Sumif Và Vlookup, Dùng Hàm Tìm Kiếm Và Tính Tổng Trong Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Chẳng hẳn với những người học Excel đều biết đến 2 hàm là hàm Sumif và Vlookup, một hảm tính tổng giá trị theo điều kiện và một hàm tìm kiếm giá trị trong chuỗi. Nhưng nếu chúng ta sử dụng hàm Sumif kết hợp Vlookup thì sao, bạn đã bao giờ gặp những bài toàn khó mà phải sử dụng đến cả 2 hàm này một lúc. Bài viết sau đây sẽ cho bạn hiểu rõ.
Hàm Sumif và Vlookup được sử dụng rất nhiều trong Excel, sự kết hợp giữa hai hàm này trong Excel là rất phổ biến. Tuy vậy để hiểu rõ được công dụng cũng như việc tại sao phải kết hợp và sử dụng 2 hàm Sumif và hàm Vlookup trong excel này, điểm mạnh và điểm yếu của chúng thì không phải ai cũng biết.
Cách kết hợp hàm Sumif và Vlookup, hàm sumif kết hợp vlookup
Hàm Sumif là gì ?
Hàm Sum là hàm cho phép bạn tính tổng các giá trị trong cột. Tuy nhiên trong một số trường hợp bạn bỏ qua một vài giá trị có trong dãy, lúc này hàm Sumif được sử dụng thay thế cho Sum. Với điều kiện được thêm vào dãy, hàm Sumif cho phép người sử dụng loại bỏ, bỏ qua các giá trị không thảo mãn điều kiện và tính tổng các giá trị thỏa mãn còn lại.
Công thức hàm Sumif: SUMIF(range, Criteria, sum_range)
Range: Là vùng được lựa chọn chứa các ô điều kiện.Criteria: Là điều kiện để thực hiện hàm nàySum_range: Vùng cần tính tổng.
Để biết rõ hơn về hàm SUMIF cũng như các ví dụ cụ thể bạn đọc có thể tham khảo qua bài viết giới thiệu và hàm SumIF mà chúng tôi đã biên soạn.
Hàm Vlookup là gì ?
Khác với Hàm Sum, hàm Vlookup không tính toán bất cứ một dữ liệu nào mà chỉ tìm kiếm dữ liệu đó. Hàm cho phép người sử dụng tra cứu dữ liệu trên một chuối nhất định theo các điều kiện đề ra sao cho kết quả hiển thị thỏa mãn, Thông thường hàm Vlookup trong Excel hay được sử dụng để tra cứu các loại mã số như mã học sinh, mã nhân viên thuận tiện cho việc truy xuất dữ liệu.
Công thức hàm Vlookup: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Lookup_value: Giá trị dùng để dò tìmTable_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4)Col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.Range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
Hàm Sumif và Vlookup, cách kết hợp Sumif và Vlookup trong Excel
Việc sử dụng hàm Sumif và Vlookup kết hợp lại với nhau giúp chúng ta có thể truy xuất nhanh mọi dữ liệu đối tượng cần tìm cho ra kết quả chính xác nhất mà khi thay đổi đối tượng sẽ không cần phải gõ lại công thức. Sử dụng kết hợp Sumif và Vlookup là một trong những bài học Excel nâng cao.
Do đó để hiểu kĩ hơn về cách kết hợp 2 hầm Sumif và Vlookup chúng ta sẽ đến với ví dụ cụ thể ngay sau đây. Để hiểu cách sử dụng về hàm Sumif và Vlookup chúng ta có 3 bảng dữ liệu sau.
Ở bảng danh sách trên chúng ta thấy có 3 bảng.
Bảng 1: Bao gồm họ tên, mã nhân viên.Bảng 2: Bao gồm mã nhân viên và doanh số của các nhân viên đó.Bảng 3: Bao gồm họ tên và doanh số (đang để trống không có dữ liệu)
Đề bài yêu cầu xuất kết quả ra bảng số 3 bao gồm họ tên và doanh số của người đó đạt được. Ngoài ra có thể tra cứu được doanh số của những nhân viên khác khi thay đổi họ tên tương ứng.
Bước 1: Ở đề bài này chúng ta sẽ sử dụng hàm Sumif và Vlookup để tính tổng doanh số của nhân viên đó với điều kiện tìm kiếm thỏa mãn các yêu cầu.
Công thức của bài này là : =SUMIF(D:D,VLOOKUP(G7,A7:B13,2,FALSE),E:E)
– SumiF và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.– D:D là vùng được lựa chọn để chứa các ô điều kiện– G7 là giá trị đối chiếu với cột doanh số, là giá trị dò tìm. Tại đây khi bạn thay đổi tên thì cột doanh số cũng thay đổi theo.– A7:B13 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị G7 ở trên.– Số 2 là thứ tự xuất giá trị, hiển thị lên màn hình tùy theo cột cần lấy dữ liệu có mấy cột, vì cột Mã NV ở vị trí thứu 2 nên chúng ta đặt là 2.– False là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.
Bước 3: Kết quả doanh số ra là 7.250.000 tương ứng với tổng doanh số nhân viên đó đạt được, bạn có thể kiểm tra lại bằng cách tính bằng tay.
Bước 4: Để chắc ăn hãy đổi tên ở Bảng 3 cột họ tên sang một nhân viên khác như Lê Tiến Lùi cũng cho một kết quả chính xác.
Cách Kết Hợp Hàm Sumif Và Hàm Vlookup Trên Excel
Với những ai thường xuyên xử lý bảng dữ liệu Excel và tính toán các số liệu, thì chắc chắn sẽ biết đến 2 hàm Excel phổ biến là Sumif và hàm Vlookup. Hàm Sumif dùng để tính tổng các giá trị có điều kiện, còn hàm Vlookup để tìm kiếm giá trị trong mảng. Và việc kết hợp 2 hàm Sumif và hàm Vlookup trong Excel cùng được áp dụng rất nhiều.
1. Cách dùng hàm Sumif Excel:
Khi hàm Sum cho phép 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 dữ liệu mà bỏ qua giá trị nào trong dãy. Bạn có thể loại giá trị nào, sử dụng giá trị nào kèm với điều kiện để tính tổng.
Bạn đọc có thể tham khảo cách áp dụng chi tiết hàm Sumif trong bài viết Cách sử dụng hàm SUMIF trong Excel.
2. Cách 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 nào đó. Hàm cho phép người dùng tra cứu dữ liệu trên một chuỗi nhất định theo các điều kiện cho trước. Hàm Vlookup thường được sử dụng ở những kiểu bảng dữ liệu tra cứu mã học sinh, mã nhân viên… Cách dùng hàm Vlookup trong Excel khá đa dạng, linh hoạt, trong đó, kết hợp Sumif và 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 những bảng dữ liệu cần tìm đối tượng, dữ liệu có điều kiện kèm theo thì hàm Sumif và hàm Vlookup sẽ tìm kiếm nhanh dữ liệu hơn, kết quả chính xác ngay cả khi thay đổi đối tượng. Đặc biệt bạn không cần phải gõ lại công thức.
Nội dung của bài đó là nhập kết quả tên nhân viên cũng như doanh số của người đó đạt được vào Bảng 3. Bên cạnh đó có thể tra cứu doanh số của những nhân viên khác khi thay đổi họ tên tương ứng.
Ở đây bạn cần sử dụng đến 2 hàm Sumif và hàm Vlookup, để tính tổng doanh số nhân viên với điều kiện cho trước.
Nếu chỉ sử dụng hàm Sumif thì chúng ta không thể tính tổng doanh thu của nhân viên vì cột Mã SV không ở cùng 1 bảng. Như vậy, bạn cần đến hàm Vlookup để 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 doanh thu của nhân viên có kèm điều kiện.
Bước 1:
Chúng ta sẽ áp dụng công thức vào bảng. Công thức sẽ là:
=SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)
Trong đó:
Sumif và Vlookup là hàm tính tổng và hàm tìm kiếm có điều kiện.
D:D là vùng chứa các ô điều kiện.
B12 là vùng giá trị đối chiếu với cột doanh số, là giá trị cần tìm kiếm. Khi thay đổi tên thì cột doanh số cũng thay đổi theo.
A3:B7 là vùng dữ liệu cột cần lấy dữ liệu để dò tìm giá trị cho vùng B12 ở bên trên.
Số 2 là thứ tự xuất giá trị hiển thị lên màn hình, tùy theo cột cần lấy dữ liệu có bao nhiêu cột. Ở đây cột cần lấy dữ liệu là Mã NV ở vị trí thứ 2 cột B nên thứ tự sẽ là 2.
Flase là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.
E:E là khu vực cho trước doanh thu của từng nhân viên đạt được.
Bước 2:
Bạn sẽ nhập công thức bên trên tại ô C12 ở Bảng 3, rồi điền tên nhân viên muốn tính tổng doanh thu tại ô B12. Ở đây tôi sẽ tính tổng số doanh thu 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 số tiền hoàn toàn chính xác.
Lưu ý với người dùng trong trường hợp khi tính tổng doanh thu và không hiển thị dấu phẩy phân cách các lớp hàng trong dãy số, bạn có thể tham khảo bài viết Cách phân cách hàng nghìn bằng dấu phẩy trong Excel để hiển thị lại dấu phẩy phân cách số trong Excel
Chúng ta sẽ cần đổi cột đó về định dạng Number là số, rồi điều chỉnh để hiển thị dấu phẩy phân cách trong Format Cells. Phần Decimal places chúng ta có thể tùy chỉnh tùy theo số mà bạn cần tính toán. Để dễ hơn có thể nhìn vào phần Sample.
Bạn có thể thay đổi định dạng của cột trước hoặc sau khi cho ra kết quả tính đều đượ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 cần nhập công thức tính khác mà vẫn cho ra kết quả chính xác. Ví dụ tôi sẽ 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, kết quả doanh thu cần tìm kiếm vẫn cho ra kết quả chính xác.
Sử Dụng Hàm Vlookup Kết Hợp Hàm Sumif, Sum Trong Excel
Hai hàm SUMIF và VLOOKUP thì không còn xa lạ gì với người dùng Excel, nhất là với những ai thường xuyên xử lý bảng dữ liệu Excel và tính toán các số liệu . Tuy nhiên với những bạn mới làm quen với Excel thì chắc cũng chưa biết việc kết hợp hai hàm trên sẽ là công cụ tuyệt vời đối với công việc của bạn. Vậy bạn đã biết cách sử dụng hàm vlookup kết hợp hàm sumif hay chưa ?
Bài viết sau đây sẽ cho bạn hiểu rõ các ví dụ công thức hiểu cách các hàm này hoạt động và cách sử dụng chúng cho dữ liệu thực tế. Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.
Hàm VLOOKUP ( Tra cứu dọc) tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.
Cú pháp:
=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])
Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.
Table – Bảng để truy xuất một giá trị.
Col_index – Cột trong bảng để truy xuất một giá trị.
range_lookup – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác
1.2 CÁCH SỬ DỤNG HÀM SUM, SUMIF TRONG EXCEL
Hàm Sum cho phép người dùng tính tổng các giá trị của một vùng dữ liệu nào đó
Cú pháp:
=SUM(num1,num2, ... num_n)Giống với hàm SUM, hàm SUMIF cũng là hàm tính tổng tuy nhiên trong một số trường hợp bạn bỏ qua một vài giá trị có trong dãy, lúc này hàm Sumif được sử dụng thay thế cho Sum. Với điều kiện được thêm vào dãy, hàm Sumif cho phép người sử dụng loại bỏ, bỏ qua các giá trị không thảo mãn điều kiện và tính tổng các giá trị thỏa mãn còn lại.
Cú pháp:
=SUMIF(Range,Criteria,Sum_range)
Range: Là địa chỉ dãy ô chứa dữ liệu.
Là giá trị điều kiện (có thể ở dạng biểu thức số, biểu thức kí tự, biểu thức logic) được đặt trong dấu nháy kép cùng kiểu dữ liệu với Range.
Sum_range: Là địa chỉ dãy ô cần tính tổng cần tính tổng.
2. HÀM VLOOKUP KẾT HỢP HÀM SUMIF, SUM
2.1 TÍNH TỔNG CỦA CÁC GIÁ TRỊ KẾT HỢP HÀM SUM – HÀM VLOOKUP TRONG EXCEL
Giả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.
2.2 TÍNH TỔNG CỦA CÁC GIÁ TRỊ CÓ ĐIỀU KIỆN KẾT HỢP HÀM SUMIF – HÀM VLOOKUP
Hàm SUMIF trong Excel thì tương tự với hàm SUM về mặt tính tổng các giá trị. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ.
Bảng Table 2 chứa nhiều mục có cùng số ID theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng Table 2.
CÔNG THỨC:
=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)
SumiF và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.
E:E là vùng được lựa chọn để chứa các ô điều kiện
I2 là giá trị đối chiếu với cột doanh số, là giá trị dò tìm. Tại đây khi bạn thay đổi tên thì cột doanh số cũng thay đổi theo.
B2:C10 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị I2 ở trên.
Số 2 là thứ tự xuất giá trị, hiển thị lên màn hình tùy theo cột cần lấy dữ liệu có mấy cột, vì cột Mã ID ở vị trí thứ 2 nên chúng ta đặt là 2.
False là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.
Chúng ta sẽ cần đổi cột đó về định dạng Currency là Tiền tệ, rồi điều chỉnh để hiển thị dấu phẩy phân cách trong Format Cells. Phần Decimal places chúng ta có thể tùy chỉnh tùy theo số mà bạn cần tính toán. Để dễ hơn có thể nhìn vào phần Sample.
Bạn có thể thay đổi định dạng của cột trước hoặc sau khi cho ra kết quả tính đều được.
Ví dụ tôi sẽ nhập vào ô I4 Tên người bán: Emily với mã số ID là 01, kết quả doanh thu cần tìm kiếm vẫn cho ra kết quả chính xác.
Hàm tính toán SUMIF, hàm VLOOKUP tìm kiếm dữ liệu, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Và việc kết hợp 2 hàm lại sẽ giúp người dùng có thể tìm kiếm dữ liệu nhanh hơn, mà không cần tính toán thủ công cho dù đổi dữ liệu tìm kiếm đi chăng nữa. Ngoài ra còn rất nhiều hàm nâng cao khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công cụ như Data validation, Pivot table, Power Query…
VIDEO HƯỚNG DẪN SỬ DỤNG HÀM SUM, HÀM SUMIF, HÀM SUMIFS:
Cách Dùng Hàm Vlookup, If Kết Hợp Trong Excel
Nói về hàm Vlookup thì đây là hàm tìm kiếm giá trị cần thiết trong một cột, sử dụng hàm Vlookup để tra cứu các thông tin thỏa mãn hàng và cột. Nếu như kết hợp cách dùng hàm Vlookup và IF với nhau chúng ta sẽ được một phép toán tìm kiếm thỏa mãn rất nhiều điều kiện bao gồm cả hàng và cột. Để có thể dễ hiểu hơn về cách dùng hàm Vlookup và IF kết hợp với nhau thì hướng dẫn sau đây sẽ làm giải thích cách dùng cho bạn.
Còn đây là chỉ tiêu của các nhóm được thể hiện ở dưới, bảng này rất quan trọng vì nếu sử dụng Vlookup chúng ta cần phải tra cứu thông tin từ đây.
Hàm IF ở đây có tác dụng gì:
Hàm IF trong ví dụ này sẽ chỉ ra được trường hợp nào thỏa mãn được doanh thu và cho ra kết quả đạt hay không đạt tại ô kết quả. Tuy nhiên hàm excel IF không thể truy xuất được vào dữ liệu ở bảng thứ 2 để biết được doanh thu của nhóm có đạt chỉ tiêu hay không, nhất là khi có đến 4 chỉ tiêu khác nhau. Nếu đây là một bảng gồm 100 nhân viên với hàng chục nhóm chắc bạn sẽ mất cả ngày để tạo xử lý.
Công thức:
– IF (Logical_test, Value_if_True, Value_if_False)
Giải thích:
– Nếu như “Logical_test” đúng thì kết quả hàm trả về là “Value_if_True”, ngược lại trả về “Value_if_False”.
Hàm Vlookup có tác dụng gì:
Như đã nói ở trên, hàm excel IF không thể biết được các nhân viên kia có đạt chỉ tiêu hay không thì hàm Vlookup sẽ có nhiệm vụ lọc ra giá trị thỏa mãn của từng thành viên trong nhóm riêng và sau đó sẽ xét tiếp đến hàm IF để cuối cùng cho ra.
Công thức:
– VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Giải thích :
– lookup_value: Giá trị dùng để dò tìm
– table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4)
– col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
– range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
– C4,$C$16:$D$20,2,0: Tham chiếu cột nhóm nằm ở cột C và thứ tự dòng là 16 cho đến cột chỉ tiêu ở cột D với giá trị cuối cùng là dòng 20.
Lưu ý: để hiển thị ký tự dạng liệt kê $ bạn phải khoanh vùng như bình thường rồi nhấn F4.
Bước 2: Và khi nhấn Enter bạn sẽ thấy kết quả hiện ra nhân viên đầu tiên Nguyễn Ngọc Anh thuộc nhóm A có doanh Thu là 18, khi chiếu xuống nhóm (hình số 3) sẽ thấy nhóm A chỉ cần chỉ tiêu là 18 nên nhân viên Nguyễn Ngọc Anh được hiển thị Đạt.
Và tất nhiên bạn cũng có thể kiểm chứng lại mức độ chính xác khi xài cách dùng hàm Vlookup và IF kết hợp với nhau.
http://thuthuat.taimienphi.vn/cach-dung-ham-vlookup-if-ket-hop-24105n.aspx Ngoài ra, trong Excel còn rất nhiều hàm cơ bản khác mà bạn có thể tự học và thực hành, với mỗi hàm cơ bản trong Excel đều có những cú pháp và công dụng khác nhau để bạn lựa chọn và kết hợp vào các bài toán của mình, việc nhớ được hết các hàm cơ bản trong Excel đòi hỏi bạn phải cần cù và trí nhớ tốt.
Bạn đang đọc nội dung bài viết Cách Kết Hợp Hàm Sumif Và Vlookup, Dùng Hàm Tìm Kiếm Và Tính Tổng Trong Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!