Bạn đang xem 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 đượ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.
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 Tính Tổng Trong Excel: Dùng Hàm Sum Kết Hợp Filter Và Subtotal
Hướng dẫn tính tổng Excel bằng hàm SUM kết hợp với các công cụ như AutoSum, Filter và SUBTOTAL.
Trong bài viết này freetuts sẽ chia sẻ tới bạn đọc những cách tính tổng trong Excel có thể áp dụng trên nhiều phiên bản khác nhau như Excel 2010, 2013, 2023 và cả Excel 2023 mới nhất.
1. Sử dụng hàm SUM để tính tổng trong ExcelĐây là cách thủ công và đơn giản nhất để tính tổng trong Excel, bạn chỉ cần nhập công thức của hàm SUM vào ô muốn tính tổng rồi ngay lập tức sẽ nhận được kết quả.
=SUM(B4:B10) 2. Tính tổng trong excel bằng công cụ AutoSumBước 1: Bấm chuột trái vào ô dưới cùng của cột muốn tính tổng trong bảng / danh sách.
Bước 3: Công cụ AutoSum sẽ tự động nhập hàm SUM và chọn sẵn vùng cần tính tổng, nếu không cần chỉnh sửa gì thêm thì bạn chỉ việc bấm Enter để nhận kết quả.
3. Tính tổng trong Excel bằng cách sử dụng công cụ FilterĐây là cách kết hợp tính năng chọn lọc (Filter) để tìm giá trị nhất định rồi sau đó tính tổng của chúng. Bạn hãy thực hiện theo các thao tác sau đây để tính tổng trong Excel với công cụ Filter.
Bước 1: Bấm chuột trái vào dòng đầu tiên trong bảng / danh sách cần tính tổng.
Bước 2: Bấm vào tab Data trên thanh công cụ sau đó chọn Filter.
Bước 3: Bấm vào nút xổ xuống ở đầu danh sách.
Bước 4: Bỏ chọn mục (Select All) sau đó chọn những mục bạn muốn tính tổng.
Bước 6: Cuối cùng sẽ có kết quả như hình dưới, bạn chỉ việc bấm Enter trên bàn phím để hoàn thành việc tính tổng.
Phương pháp tính tổng kết hợp với công cụ Filter (lọc) giúp nhanh chóng tìm ra kết quả tổng của những mục xác định có trong bảng.
Nhược điểm là nó chỉ có thể tính tổng trong một cột, kết quả trả về không thể copy hay di chuyển mà chỉ hiển thị cho bạn xem trước. Bây giờ hãy làm theo bước sau để xem tổng của một cột trong Excel.
Bạn chỉ cần bấm chuột trái vào ô ABCD…trên cùng của trang tính, ví dụ mình muốn xem tổng cột ô B thì bấm chuột trái vào đầu dòng cột B.
Để thuận tiện trong quá trình tính toán thì tốt nhất bạn nên chuyển bảng tính thông thường sang bảng Excel bằng các thao tác sau, từ đó giúp tính tổng một cách cực kỳ nhanh chóng.
Bước 1: Bôi đen toàn bộ bảng tính (Chọn vùng) sau đó bấm tổ hợp phím Ctrl + T trên bàn phím.
Bước 2: Khi cửa sổ Create Table hiện ra, bạn kiểm tra vùng chọn đã chuẩn xác chưa sau đó bấm OK.
Bước 3: Sau khi chuyển thành công sang bảng Excel thì việc đơn giản cần làm tiếp theo là bấm vào tab Design rồi tích chọn Total Row.
Tính tổng trong Excel bằng SUBTOTAL
6. Kết luậnHàm Tìm Kiếm Vlookup Và Hlookup Trong Excel
Nếu công việc của bạn thường xuyên làm việc với Excel và xử lý dữ liệu thì việc phải nắm vững các hàm tìm kiếm là bắt buộc. Trong bài viết này, mình sẽ chia sẻ với các bạn hai hàm tìm kiếm thường xuyên được sử dụng là hàm Vlookup và Hlookup.
VLOOKUP là hàm dùng để tìm kiếm giá trị tương ứng trên ô cột đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong cột chỉ định
Cú Pháp:
Trong đó:
Lookup_value: giá trị đối chiếu với cột đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
Table_ array: địa chỉ tuyệt đối của bảng tham chiếu, không lấy dòng tiêu đề.
Col_index_Num: Số thứ tự cột chứa giá trị cần lấy của bảng tham chiếu (tính theo thứ tự từ trái qua phải và bắt đầu từ 1).
Range_lookup: Cách tìm kiếm trên bảng tham chiếu. + 0: Cột đầu tiên của bảng tham chiếu chưa sắp xếp. + 1: Cột đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định).
Để tìm kiếm giá trị mức thưởng của bảng tham chiếu và cho ra kết quả Học Bổng tương ứng với mức thưởng đó chúng ta làm như sau:
Trong đó:
K6: Cột Mức thưởng cần đối chiếu với cột đầu tiên của bảng tham chiếu
$A$12:$B$14 : là địa chỉ tuyệt đối của bảng tham chiếu
2: cột học bổng là cột thứ 2 của bảng tham chiếu
0: cột đầu tiên của bảng tham chiếu chưa sắp xếp
2. Hàm HLOOKUP
HLOOKUP là hàm tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong dòng chỉ định.
Cú pháp :HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)
Trong đó:
Lookup_value: giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
Table_ array: là địa chỉ tuyệt đối của bảng tham chiếu, không lấy cột tiêu đề.
Row_index_Num: Số thứ tự dòng chứa giá trị cần lấy của bảng tham chiếu (tính theo thứ tự từ trên xuống dưới và bắt đầu từ số 1).
Range_lookup: Cách tìm kiếm trên bảng tham chiếu. + 0: Dòng đầu tiên của bảng tham chiếu chưa sắp xếp. + 1: dòng đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định).
Ví dụ:
Ta có: =HLOOKUP(A2:A4;$A$6:$D$7;2;1)
Trong đó:
A2:A4: là địa chỉ Mã đơn vị đối chiếu với dòng đầu tiên của bảng tham chiếu
2: dòng đơn vị là dòng thứ 2 của bảng tham chiếu
1: dòng đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định)
Kết quả nhận được như sau:
Hàm Vlookup Và Hlookup Để Tìm Kiếm Trong Excel
Trong Excel, bạn phải thường xuyên xử lý dữ liệu thì việc phải nắm vững các hàm tìm kiếm là điều bắt buộc. Cụ thể, hàm VLOOKUP và HLOOKUP là cặp hàm tìm kiếm thường xuyên được sử dụng. Tuy nhiên, khi ứng dụng nhiều người vẫn còn nhầm lẫn về công dụng của hai hàm này. Hãy để Hocexcelcoban giải quyết giúp bạn
CÁCH DÙNG HÀM HLOOKUP VÀ VLOOKUP TRONG EXCEL 1.HÀM VLOOKUP 1.1 CHỨC NĂNG CỦA HÀM TÌM KIẾM VLOOKUPHà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.
1.2 CÚ PHÁP HÀMCú 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 – Địa chỉ tuyệt đối của bảng tham chiếu, không lấy dòng tiêu đề.
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
Nếu bạn thắc mắc địa chỉ tuyệt đối của bảng, tham khảo ngày: Hướng dẫn sử dụng địa chỉ ô Excel
1.3 VÍ DỤ SỬ DỤNG HÀM VLOOKUP TRONG EXCELCÁC TRƯỜNG HỢP SỬ DỤNG
Tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi.
VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.
Tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn Lookup_value.
VD: Xếp loại học lực của học sinh theo điểm tổng kết.
Ví dụ 1: Dùng hàm VLOOKUP để tìm kiếm chính xác
Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?
Để điển thông tin Trình độ học vấn cho nhân viên, tại ô F4 , ta nhập công thức dò tìm chính xác như sau:
=VLOOKUP($B4,$B$11:$D$15,3,FALSE)Trong đó:
Sau khi điền xong công thức cho ô F4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.
Các bạn tự thực hành với Quê quán của nhân viên để hiểu rõ hơn nha !
Ví dụ 2: Sử dụng hàm VLOOKUP để tìm kiếm tương đối
Dựa vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:
Giờ để nhập xếp loại cho các học sinh, ta sẽ sử dụng hàm VLOOKUP. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.
=VLOOKUP($E4,$B$11:$C$14,2,TRUE)Trong đó:
Sau khi điền xong công thức cho ô F4 , tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.
2.1 HÀM TRA CỨU NGANG HLOOKUPHàm HLOOKUP ( Tra cứu ngang) là hàm tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và sau đó trả về một giá trị trong dòng mà bạn chỉ định.
2.2 CÚ PHÁP HÀM VLOOKUPCú pháp:
=HLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])
Lookup_value – Giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
Table – Địa chỉ tuyệt đối của bảng tham chiếu, không lấy dòng tiêu đề.
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
2.3 VÍ DỤ HÀM HLOOKUP ĐỂ TRA CỨU TRONG EXCELCũng với ví dụ tưởng tự Hàm tra cứu dọc, dựa vào bảng quy định xếp loại tương ứng với điểm đã cho, hãy xếp loại học lực cho các sinh viên có tên trong danh sách:
Ta sẽ sử dụng hàm HLOOKUP xếp loại. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ trái qua phải (từ yếu đến giỏi).
=VLOOKUP($E4,$D$10:$G$11,2,TRUE)Trong đó:
Sau khi điền xong công thức cho ô F4 , tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.
3. PHÂN BIỆT HÀM VLOOKUP VÀ HLOOKUPCả hai hàm HLOOKUP VÀ VLOOKUP dùng để tìm kiếm một giá trị. Nhưng chúng khác nhau trong cách hoạt động. Chúng ta dễ dàng nhận ra tên của hai hàm khác nhau ở chữ cái đầu
Do đó, bạn sử dụng hàm VLOOKUP để tìm giá trị theo cột ở phía bên trái của dữ liệu bạn muốn tìm. Hàm HLOOKUP dùng để tìm giá trị theo hàng ngang. Nó tìm giá trị ở hàng đầu tiên của bảng và trả lại giá trị ở hàng được chỉ định ở trong cùng một cột.
VIDEO SỬ DỤNG HÀM HOOKUP VÀ VLOOKUP TRONG EXCEL – NÂNG CAO:Cập nhật thông tin 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 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!