Bạn đang xem bài viết Sử Dụng Hàm Vlookup Kết Hợp Hàm Sumif, Sum Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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 EXCELHà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 EXCELGiả 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 VLOOKUPHà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 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.
Cách Sử Dụng Kết Hợp Hàm Vlookup Với Hàm Left
Việc kết hợp sử dụng các hàm với nhau trong Excel sẽ khiến cho công việc tính toán của bạn trở nên thuận lợi và tăng độ chính xác của phép tính lên rất nhiều. Vlookup là một hàm tra cứu dựa trên điều kiện cụ thể, hàm Left thì lại là một hàng lấy ký tự ở phía bên trái của một chuỗi ký tự. Khi ta kết hợp hai hàm Vlookup và Left này với nhau sẽ giúp cho hàm Vlookup thực hiện tra cứu một cách chính xác, nhanh chóng hơn.
HƯỚNG DẪN CÁCH SỬ DỤNG KẾT HỢP HÀM VLOOKUP VỚI HÀM LEFT Cú pháp và cách sử dụng của hàm Vlookup và hàm Left
Hàm Vlookup: Là hàm tra cứu và trả về kết quả theo hàng dọc.
– Cú pháp: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup]
Trong đó:
+ lookup_value: Giá trị dùng để dò tìm
+ table_array: Vùng dữ liệu tra cứu
+ 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).
Lưu ý: Đối với giá trị lookup_value ấn F4 3 lần, đối với giá trị table_array ấn F4 1 lần.
Ý nghĩa của việc sử dụng F4
– F4 (1 lần): để có giá trị tuyệt đối. Tuyệt đối được hiểu là cố định cột và cố định dòng ⇒ $cột$dòng
Ví dụ: $A$8 ⇒ cố định cột A và cố định dòng 8
– F4 (2 lần): để có giá trị tương đối cột và tuyệt đối dòng – Được hiểu là cố định dòng , không cố định cột ⇒ cột$dòng
Ví dụ: A$8 ⇒ cố định dòng 8, không cố định cột A
– F4 (3lần): để có giá trị tương đối dòng và tuyệt đối cột – Được hiểu là cố định cột, không cố định dòng ⇒ $cộtdòng
Hàm Left: Là hàm dùng để cắt lấy các ký tự bên trái của một chuỗi ký tự
+ Text: chuỗi ký tự cần cắt
+ n: số ký tự muốn cắt
Chúng ta có một bảng dữ liệu như hình dưới
Chúng ta sẽ sử dụng hàm LEFT để lấy Mã ngành từ cột Lớp.
Bước 1: Điền vào ô C6 công thức: =LEFT(B6,2). Ý nghĩa của công thức trên là cắt 2 ký tự ở ô B6
Bước 2: Sau đó ấn Enter. Kết quả hiện ra là 2 ký tự CN đã được cắt ra từ chuỗi ký tự CNTT1.
Bước 3: Các bạn kéo từ ô C6 xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng.
Bước 4: Các bạn điền vào ô D6 công thức: =VLOOKUP($C6,$G$6:$H$10,2,0). $C6 là Giá trị dùng để tìm kiếm, ,$G$6:$H$10 là Vùng dữ liệu tra cứu, 2 là thứ tự cột cần lấy giá trị trong Vùng dữ liệu tra cứu, 0 là phạm vi tìm kiếm mang tính tuyệt đối.
Bước 5: Sau đó ấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã Ngành. Các bạn lại kéo xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng
Bước 6: Các bạn điền vào ô E6 công thức: =VLOOKUP(LEFT(B6,2),$G$6:$H$10,2,0). Chúng ta thay giá trị dùng để tìm kiếm từ $C6 thành LEFT(B6,2).
Bước 7: Sau đó ấn Enter và làm tương tự như bước 5.
Cách Sử Dụng Hàm Sumif Và Sumifs Để Tính Tổng Có Điều Kiện Trong Excel
Thực chất hàm SumIFs là hàm tính tổng có điều kiện nâng cấp của hàm SumIF, nếu như SumIF tính tổng với 1 điều kiện thì SumIFs cho phép ta tính tổng với nhiều điều kiện. Trong bài viết này, chúng ta sẽ cùng tìm hiểu cách dùng hàm tính tổng có điều kiện Sumif và SumIFs.
Cách dùng hàm SUMIF để tính tổng có điều kiện trong Excel
Công dụng: Hàm SUMIF là hàm tính tổng có điều kiện trong Excel
Cú pháp (syntax): =SUMIF (range, criteria, [sum_range])
Trong đó
Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoăc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
Criteria: Tiêu chí xác định các giá trị thêm vào.
Sum_range: (tùy chọn) Các giá trị được gắn thêm. Nếu sum_range bị bỏ quá, những ô trong phạm vi đánh giá sẽ được thay thế.
Chú ý:
_ Khi sum_range bị bỏ qua, các ô trong phạm vi sẽ được cộng lại.
_ Khu vực xác định có chứa chữ hay ký hiệu toán học đều phải được đặt trong dấu ngoặc kép.
_ Phạm vi xác định dạng số có thể được cung cấp là số sẽ không phải dùng đến dấu ngoặc.
_ Các ký tự ? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
Lưu ý: Để làm theo hướng dẫn các bạn có thể tải file Tại Đây
Một ví dụ cách sử dụng hàm SUMIF đểTính tổng cột Số lượng với Tên hàng là Red Apples, như vậy chúng ta nhập công thức như hướng dẫn hình sau:
Tính tổng các giá trị LỚN HƠN 200 trong các ô E4:E12.
<
=SUMIF(E4:E12,”<300″, B2:B10)
Tính tổng các giá trị trong các ô E4:E12 nếu giá trị tương ứng trong cột A NHỎ HƠN 300.
=
=SUMIF(D4:D12,E4)
Tính tổng các giá trị trong các ô D4:D12 mà có giá trị BẰNG với giá trị trong ô E4.
Tính tổng các giá trị trong các ô F4:F12 nếu giá trị tương ứng trong cột C KHÁC với giá trị trong ô I3.
Tính tổng các ô có giá trị Lớn hơn hoặc bằng 175 trong dải E4:E12
<=
=SUMIF(E4:E12,”<=150″, F4:F12)
Tính tổng các giá trị trong các ô F4:F12 nếu giá trị trong ô tương ứng trong cột E Nhỏ hơn hoặc bằng 150
Sử dụng SUMIF với các tiêu chí so sánh kiểu chuỗi
Tính tổng nếu bằng
Đối chiếu hoàn toàn: =SUMIF(C4:C12, “bananas” ,F4:F12)
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa chính xác từ “bananas” và không dư một từ hay kí tự nào. Các ô chứa “green bananas”, “yellow bananas” không thoả điều kiện này nên không được tính.
Tính tổng nếu khác
Đối chiếu từng phần: =SUMIF(C4:C12, “*bananas*”, F4:F12)
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “green bananas”, “yellow bananas” thoả điều kiện này nên được tính.
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví như “yellow bananas” hoặc “Green bananas” sẽ được tính tổng.
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác. Các ô như “yellow bananas” hay “Green bananas” sẽ không được tính.
Cách dùng hàm SUMIFS để tính tổng có điều kiện trong Excel
Công dụng: Hàm SUMIFS là hàm tính tổng có nhiều điều kiện trong Excel
Cú pháp (syntax): =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).
Trong đó:
– sum_range: Vùng cần tính tổng.
– criteria_range1: Vùng điều kiện cần tính tổng, tham số bắt buộc.
– criteria1: Tiêu chí so sánh vùng điều kiện, là tham số bắt buộc.
– criteria_range2, criteria2: Tùy chọn các phạm vi và điều kiện bổ sung, cho phép tối đa 127 cặp criteria_range2, criteria2.
– Các ô giá trị trong vùng tính tổng sum_range chứa giá trị True được coi là 1, False được coi là 0.
– Đối số vùng điều kiện criteria_range phải có cùng kích thước với sum_range.
– Trong giá trị điều kiện criteria có thể dùng kí tự thay thế ? hoặc *.
Hãy tham khảo cách dùng hàm sumifs trong bảng sau: Tính tổng thành tiền với Tên hàng là Red Apples và có số lượng lớn hơn hoặc bằng 150.
Cập nhật thông tin chi tiết về Sử Dụng Hàm Vlookup Kết Hợp Hàm Sumif, Sum Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!