Bạn đang xem bài viết 2.2.6. Hàm Tìm Kiếm Một Chuỗi Con đượ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.
Tác giả: Bùi Nguyễn Triệu Tường (BNTT – GPE) Hình sau đây là một minh họa cho phần vừa trình bày ở trên:
2.2.6. HÀM TÌM KIẾM VÀ THAY THẾ MỘT CHUỖI (Searching for Substrings, Substituting One Substring for Another)
Tổng hợp: phamnhukhang (GPE)
Hàm FIND và Hàm SEARCH
Trích xuất họ và tên (ví dụ dùng để trích cho tên tiếng Anh, bỏ qua tên đệm)
Đây là dạng bài toán đã được rất nhiều bạn hỏi trên GPE.
Cách làm là dùng hàm FIND() để tìm những khoảng trắng phân cách giữa họ và tên, sau đó dùng hàm Để lấy phần tên (First Name), chúng ta dùng công thức sau (giả sử họ tên nằm ở cell A2): LEFT() để tách phần tên, và hàm RIGHT() để tách phần họ.
=LEFT(A2, FIND(” “, A2) – 1)
Nghĩa là dùng hàm khi đó hàm FIND() để tìm vị trí của ký tự trắng đầu tiên kể từ bên trái, ví dụ nó là vị trí thứ 5,LEFT() sẽ xác định được cái tên này gồm có 4 chữ (= 5-1).
Để lấy phần họ (Last Name), chúng ta dùng công thức:
=RIGHT(A2, LEN(A2) – FIND(” “, A2))
Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt)
Đây cũng là dạng bài toán đã được rất nhiều bạn hỏi trên GPE. Cách làm giống như bài Trích xuất họ và tên ở trên, tuy nhiên có khác một chút, để trích thêm phần tên đệm. Karen E. Hammond
Đầu tiên, như bài trên, dùng công thức sau để tách phần Tên (first name):
Giả sử Họ và Tên (full name) nằm ở cell A2, và đang có giá trị là
Công thức FIND(” “, A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen). Để tìm vị trí của khoảng trắng thứ hai, thì bạn phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng kết quả của FIND(” “, A2) cộng thêm 1:
=FIND(” “, A2, FIND(” “,A2) + 1)
Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ (last name):
Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm:
Xác định tên của cột (Determining the Column Letter)
Trong Excel có hàm COLUMN(), cho ra kết quả là số của cột (ví dụ, gõ hàm này trong cột B thì kết quả sẽ là 2). Nhưng đôi khi bạn muốn kết quả là tên của cột chứ không muốn đó là con số (B chứ không phải là 2), thì làm sao?
Đây là một vấn đề đòi hỏi sự khôn khéo một chút, vì tên cột trong bảng tính chạy từ A đến Z, từ AA đến AZ… và cho tới tận cùng là XFD (!)
Có một hàm giúp chúng ta tìm địa chỉ tuyệt đối của một cell, đó là hàm CELL(“address”), ví dụ $A$2, hoặc $B$10…
Trích:
=MID(CELL(“Address”), 2, num_chars)
Cái khó là cái num_chars này đây, vì tên cột thì có thể là 1, 2, hoặc 3 ký tự (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi:
FIND(“$”, CELL(“address””,A2), 3) – 2
=MID(CELL(“Address”), 2, FIND(“$”, CELL(“address”), 3) – 2)
Công thức này áp dụng cho chính cell chứa công thức. Nếu muốn tìm tên cột tại một cell nào đó, bạn chỉ việc thêm địa chỉ (hoặc một cái gì đó tham chiếu đến địa chỉ này) của cell muốn tìm vào phía sau cái “address”
Ví dụ, muốn tìm tên của cell AGH68, bạn gõ:
Có nhiều chương trình có khả năng tìm kiếm một số đoạn văn và thay thế nó bằng đoạn văn khác. Excel cũng có khả năng làm chuyện đó bằng cách dùng hàm. Đó là hàm REPLACE và hàm SUBSTITUTE.
Hàm REPLACE
Với A1 = Expense Budget for 2007
Top
Một Số Hàm Cắt Chuỗi Trong Excel
Trong excel, ngoài việc kết hợp các chuỗi riêng lẻ với nhau dựa vào hàm con-catenate hay dùng toán tử & như bài học hôm trước, thì excel cũng trang bị cho chúng ta cách để cắt chuỗi ra thành các chuỗi nhỏ hơn. Hôm nay tôi xin giới thiệu đến các bạn một số hàm cắt chuỗi trong excel như sau:
Hàm Right: Dùng để cắt và lấy phần bên phải của một chuỗiHàm Left: Dùng để cắt và lấy phần bên trái của một chuỗiHàm Mid: Dùng để cắt và lấy phần giữa của một chuỗi
1. :=Right(Text;[num_c-har])Có thể được hiểu như sau=Right(“Chuỗi gốc”;”Số ký tự muốn cắt lấy”)Trong đó“Chuỗi lớn” hay Text là một chuỗi chứa trong ô mà ta muốn cắt“Số ký tự muốn cắt lấy” hoặc [num_c-hars] là số ký tự bạn muốn hàm Right trả về. Số này phải là số nguyên lớn hơn 0. nếu bằng không thì hàm right sẽ không cắt được ký tự nào cả.Ví dụ: xuất ra quốc tịch của từng người trong ô B1~B3B 1 Nguyễn Văn Phong – Kỹ sư – Việt Nam =right(A1;8)2 Đào Hồng Đăng -Kỹ Sư – Lào =right(A2;3)3 Ưa Mít Ướt – Kế Toán – Campuchia =right(A3;9)
Như vậy muốn cắt chữa Việt Nam ở ô A1 và cho nó xuất hiện ở ô B1 thì dùng hàm Right, Text=A1,Num_C-hars=8 (Số ký tự của chữ Việt Nam là 8 (8 C-haracters). và ô B2, Num_C-hars = 3 vì chữ LÀO chỉ có 3 kí tự thôi.
Bây giờ chúng ta học cách sử dụng hàm Left.2. thì ngược với hàm Right thôi, là cắt và xuất ra phần chuỗi nằm bên trái của chuỗi lớn.=Left(Text;[num_c-har])Có thể được hiểu như sau=Left(“Chuỗi gốc”;”Số ký tự muốn cắt lấy”)Ví dụ: xuất ra họ và tên của nhân viên trong ô B1~B3B 1 Nguyễn Văn Phong – Kỹ sư – Việt Nam =left(A1;8)2 Đào Hồng Đăng – Kỹ Sư – Lào =left(A2;3)3 Ưa Mít Ướt – Kế Toán – Campuchia =left(A3;9)
Chữ Việt Nam có 8 ký tự (kể cả khoảng trắng) nên ta nhập vào Num_c-hars=8
3. : Cắt phần ở giữa một chuỗi và xuất ra.Cú pháp:=MID(Text;start_num;Num_c-hars) và được hiểu như sau:=MID(“Chuỗi gốc”;”vị trí cắt”;”Số ký tự cần cắt và xuất ra”)
Áp dụng hàm mid để cắt và xuất ra phần text nghề nghiệp ở bảng sau:B 1 Nguyễn Văn Phong – Kỹ sư – Việt Nam =mid(A1;21;5)2 Đào Hồng Đăng -Kỹ Sư – Lào =mid(A2;18;5)3 Ưa Mít Ướt – Kế Toán – Campuchia =mid(A3;13;8)
Ta thấy chữ Kỹ Sư trong ô A1 nằm ở vị trí 21 và có 5 ký tự bao gồm cả khoảng trắng Space Bar.
Tuy nhiên nếu chúng ta dùng hàm mà phải đi đếm bằng mắt rồi sửa bằng tay cho từng công thức thì không có ý nghĩa gì cả. Cái ta muốn là làm sao viết một công thức rồi áp dụng cho tất cả các dòng thì mới thực sự mang lại hiệu quả trong công việc.
B C D 1 Họ Tên – Nghề Nghiệp – Quốc Tịch Họ Và Tên 2 Nguyễn Văn Phong -Kỹ sư – Việt Nam =LEFT(A2;SEARCH(” – “;A2)-1) 3 Đào Hồng Đăng -Kỹ Sư – Lào =LEFT(A3;SEARCH(” – “;A3)-1) 4 Ưa Mít Ướt – Kế Toán – Campuchia =LEFT(A4;SEARCH(” – “;A4)-1)
Cột C: Nghề nghiệp có công thức như sau:C2 =MID(RIGHT(A2;LEN(A2)-LEN(B2)-3);1;LEN(LEFT(RIGHT(A2;LEN(A2)-LEN(B2)-3);SEARCH(” – “;RIGHT(A2;LEN(A2)-LEN(B2)-3)))))C3=MID(RIGHT(A3;LEN(A3)-LEN(B3)-3);1;LEN(LEFT(RIGHT(A3;LEN(A3)-LEN(B3)-3);SEARCH(” – “;RIGHT(A3;LEN(A3)-LEN(B3)-3)))))C4=MID(RIGHT(A4;LEN(A4)-LEN(B4)-3);1;LEN(LEFT(RIGHT(A4;LEN(A4)-LEN(B4)-3);SEARCH(” – “;RIGHT(A4;LEN(A4)-LEN(B4)-3)))))
* Ta dùng Hàm Len() dùng để đếm chiều dài của chuỗi trong ô A2,A3,A4, B2,B3,B4, từ đó ta tính toán để suy ra vị trí cần cắt.
Hàm Mid như trên có thể thay thế bằng:C2=LEFT(RIGHT(A2;LEN(A2)-LEN(B2)-3);SEARCH(” – “;RIGHT(A2;LEN(A2)-LEN(B2)-3)))
Và cột D: Quốc tịch có công thứ như sau:D2=RIGHT(A2;LEN(A2)-LEN(B2)-LEN(C2)-5)D3=RIGHT(A3;LEN(A3)-LEN(B3)-LEN(C3)-5)D4=RIGHT(A4;LEN(A4)-LEN(B4)-LEN(C4)-5)
Hà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ề 2.2.6. Hàm Tìm Kiếm Một Chuỗi Con 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!