Chỉ có 3 ký tự đại diện Excel [dấu hoa thị, dấu chấm hỏi và dấu ngã] và có thể thực hiện được rất nhiều việc bằng cách sử dụng các ký tự này
Trong hướng dẫn này, tôi sẽ chỉ cho bạn bốn ví dụ trong đó các ký tự đại diện Excel này là cứu cánh tuyệt đối
Hướng dẫn này bao gồm
- Ký tự đại diện Excel – Giới thiệu
- Ký tự đại diện Excel - Ví dụ
- #1 Lọc dữ liệu bằng ký tự đại diện Excel
- #2 Tra cứu một phần bằng ký tự đại diện & VLOOKUP
- 3. Tìm và thay thế một phần phù hợp
- 4. Đếm các ô không trống có chứa văn bản
Ký tự đại diện Excel – Giới thiệu
Ký tự đại diện là các ký tự đặc biệt có thể thay thế bất kỳ ký tự nào [do đó có tên – ký tự đại diện].
Có ba ký tự đại diện trong Excel
- * [dấu hoa thị] – Nó đại diện cho bất kỳ số lượng ký tự nào. For example, Ex* could mean Excel, Excels, Example, Expert, etc.
- ? [question mark] – It represents one single character. Ví dụ: Tr?mp có thể có nghĩa là Trump hoặc Kẻ lang thang.
- ~ [dấu ngã] – Dùng để xác định ký tự đại diện [~, *, ?] trong văn bản. Ví dụ: giả sử bạn muốn tìm cụm từ chính xác Excel* trong danh sách. Nếu bạn sử dụng Excel* làm chuỗi tìm kiếm, nó sẽ cung cấp cho bạn bất kỳ từ nào có Excel ở đầu, theo sau là bất kỳ số lượng ký tự nào [chẳng hạn như Excel, Excels, Excellent]. Để tìm cụ thể excel*, chúng ta cần sử dụng ~. Vì vậy, chuỗi tìm kiếm của chúng tôi sẽ là excel~*. Ở đây, sự hiện diện của ~ đảm bảo rằng excel đọc ký tự sau đây chứ không phải ký tự đại diện.
Ghi chú. Tôi chưa gặp nhiều tình huống mà bạn cần sử dụng ~. Tuy nhiên, nó là một điều tốt để biết tính năng
Bây giờ, hãy xem qua bốn ví dụ tuyệt vời trong đó các ký tự đại diện thực hiện tất cả các công việc nặng nhọc
Ký tự đại diện Excel - Ví dụ
Bây giờ, hãy xem bốn ví dụ thực tế trong đó các ký tự đại diện trong Excel có thể rất hữu ích
- Lọc dữ liệu bằng ký tự đại diện
- Tra cứu một phần bằng ký tự đại diện và VLOOKUP
- Tìm và thay thế một phần phù hợp
- Đếm các ô không trống có chứa văn bản
#1 Lọc dữ liệu bằng ký tự đại diện Excel
Các ký tự đại diện trong Excel rất hữu ích khi bạn có tập dữ liệu lớn và bạn muốn lọc dữ liệu dựa trên một điều kiện
Giả sử bạn có một tập dữ liệu như hình bên dưới
Bạn có thể sử dụng ký tự đại diện dấu hoa thị [*] trong bộ lọc dữ liệu để nhận danh sách các công ty bắt đầu bằng bảng chữ cái A
Đây là cách để làm điều này
- Chọn các ô mà bạn muốn lọc
- Chuyển đến Dữ liệu -> Sắp xếp và Lọc -> Bộ lọc [Phím tắt – Control + Shift + L]
- Nhấp vào biểu tượng bộ lọc trong ô tiêu đề
- Trong trường [bên dưới tùy chọn Bộ lọc văn bản], nhập A*
- Nhấp vào OK
Điều này sẽ ngay lập tức lọc kết quả và cung cấp cho bạn 3 tên – ABC Ltd. , amazon. com và Cửa hàng Apple
Làm thế nào nó hoạt động? . Điều này là do dấu hoa thị [là ký tự đại diện của Excel] có thể đại diện cho bất kỳ số lượng ký tự nào
Giờ đây, với cùng một phương pháp, bạn có thể sử dụng nhiều tiêu chí khác nhau để lọc kết quả
Ví dụ: nếu bạn muốn lọc các công ty bắt đầu bằng bảng chữ cái A và chứa bảng chữ cái C trong đó, hãy sử dụng chuỗi A*C. Điều này sẽ chỉ cho bạn 2 kết quả – ABC Ltd. và Amazon. com.
Nếu bạn sử dụng A?C thay vào đó, kết quả là bạn sẽ chỉ nhận được ABC Ltd [vì chỉ có một ký tự được phép giữa 'a
Note: The same concept can also be applied when using Excel Advanced Filters.#2 Tra cứu một phần bằng ký tự đại diện & VLOOKUP
Tra cứu một phần là cần thiết khi bạn phải tìm kiếm một giá trị trong danh sách và không có kết quả khớp chính xác
Ví dụ: giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn tìm công ty ABC trong một danh sách, nhưng danh sách này có ABC Ltd thay vì ABC
Bạn không thể sử dụng hàm VLOOKUP thông thường trong trường hợp này vì giá trị tra cứu không khớp chính xác
Nếu bạn sử dụng VLOOKUP với đối sánh gần đúng, nó sẽ cho bạn kết quả sai
Tuy nhiên, bạn có thể sử dụng ký tự đại diện trong hàm VLOOKUP để có được kết quả phù hợp
Nhập công thức sau vào ô D2 và kéo nó cho các ô khác
=VLOOKUP["*"&C2&"*",$A$2:$A$8,1,FALSE]Công thức này hoạt động như thế nào?
Trong công thức trên, thay vì sử dụng giá trị tra cứu như hiện tại, giá trị này được đặt ở hai bên với dấu hoa thị ký tự đại diện Excel [*] – “*”&C2&”*
Điều này cho excel biết rằng nó cần tìm bất kỳ văn bản nào có chứa từ đó trong C2. Nó có thể có bất kỳ số ký tự nào trước hoặc sau văn bản trong C2
Do đó, công thức tìm kiếm một giá trị khớp và ngay khi nhận được giá trị khớp, nó sẽ trả về giá trị đó
3. Tìm và thay thế một phần phù hợp
Ký tự đại diện Excel khá linh hoạt
Bạn có thể sử dụng nó trong một công thức phức tạp cũng như trong chức năng cơ bản như Tìm và Thay thế
Giả sử bạn có dữ liệu như hình bên dưới
Trong dữ liệu trên, khu vực đã được nhập theo nhiều cách khác nhau [chẳng hạn như Tây Bắc, Tây Bắc, Tây Bắc]
Đây thường là trường hợp với dữ liệu bán hàng
Để làm sạch dữ liệu này và làm cho dữ liệu nhất quán, chúng ta có thể sử dụng Tìm và Thay thế bằng ký tự đại diện Excel
Đây là cách để làm điều này
- Chọn dữ liệu mà bạn muốn tìm và thay thế văn bản
- Đi tới Trang chủ -> Tìm & Chọn -> Chuyển đến. Thao tác này sẽ mở hộp thoại Tìm và Thay thế. [Bạn cũng có thể sử dụng phím tắt – Control + H]
- Nhập văn bản sau vào hộp thoại tìm và thay thế
- Tìm gì. Bắc*W*
- Thay thế bằng. Tây Bắc
- Nhấp vào Thay thế tất cả
Điều này sẽ ngay lập tức thay đổi tất cả các định dạng khác nhau và làm cho nó phù hợp với Tây Bắc
Cái này hoạt động ra sao?
Trong trường Tìm, chúng tôi đã sử dụng North*W* để tìm bất kỳ văn bản nào có từ North và chứa bảng chữ cái ' .
Do đó, nó bao gồm tất cả các kịch bản [Tây Bắc, Tây Bắc và Tây Bắc]
Tìm và Thay thế tìm tất cả các trường hợp này và thay đổi nó thành Tây Bắc và làm cho nó nhất quán
4. Đếm các ô không trống có chứa văn bản
Tôi biết bạn thông minh và bạn nghĩ rằng Excel đã có sẵn chức năng để làm việc này
Bạn hoàn toàn đúng
Điều này có thể được thực hiện bằng cách sử dụng chức năng COUNTA
NHƯNG… Có một vấn đề nhỏ với nó
Rất nhiều khi bạn nhập dữ liệu hoặc sử dụng trang tính của người khác, bạn sẽ nhận thấy rằng có những ô trống trong khi đó có thể không phải là trường hợp
Các ô này trông trống nhưng có =”” trong đó. Vấn đề là
Vấn đề là hàm COUNTA không coi đây là một ô trống [nó coi đó là văn bản]
Xem ví dụ bên dưới
Trong ví dụ trên, tôi sử dụng hàm COUNTA để tìm các ô không trống và nó trả về 11 chứ không phải 10 [nhưng bạn có thể thấy rõ ràng chỉ có 10 ô có chữ]
Lý do, như tôi đã đề cập, là nó không coi A11 là trống [trong khi nó nên như vậy]
Nhưng đó là cách Excel hoạt động
Cách khắc phục là sử dụng ký tự đại diện Excel trong công thức
Dưới đây là công thức sử dụng hàm COUNTIF chỉ đếm các ô có văn bản trong đó
=COUNTIF[A1:A11,"?*"]Công thức này yêu cầu excel chỉ đếm nếu ô có ít nhất một ký tự
Trong kết hợp ?* .
- ? [dấu chấm hỏi] đảm bảo có ít nhất một ký tự.
- * [dấu hoa thị] nhường chỗ cho bất kỳ số lượng ký tự bổ sung nào.
Ghi chú. Công thức trên hoạt động khi chỉ có giá trị văn bản trong các ô. Nếu bạn có một danh sách có cả văn bản cũng như số, hãy sử dụng công thức sau
=COUNTA[A1:A11]-COUNTBLANK[A1:A11]
Tương tự, bạn có thể sử dụng ký tự đại diện trong nhiều hàm Excel khác, chẳng hạn như IF[], SUMIF[], AVERAGEIF[] và MATCH[]
Cũng rất thú vị khi lưu ý rằng mặc dù bạn có thể sử dụng các ký tự đại diện trong hàm TÌM KIẾM, nhưng bạn không thể sử dụng nó trong hàm TÌM
Hy vọng những ví dụ này cung cấp cho bạn sự tinh tế về tính linh hoạt và sức mạnh của các ký tự đại diện trong Excel
Nếu bạn có bất kỳ cách sáng tạo nào khác để sử dụng nó, hãy chia sẻ nó với tôi trong phần bình luận
Bạn có thể tìm thấy các hướng dẫn Excel sau đây hữu ích
- Sử dụng COUNTIF và COUNTIFS với nhiều tiêu chí
- Tạo danh sách thả xuống trong Excel
- Toán tử giao nhau trong Excel
SÁCH EXCEL MIỄN PHÍ
Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên
CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI
14 suy nghĩ về “Ký tự đại diện trong Excel – Đây là gì và cách sử dụng nó tốt nhất”
Bhisham
Tháng Bảy 2020 lúc 6. 25 giờ chiều
Tôi đã sử dụng Ký tự đại diện từ lâu, tuy nhiên bạn đã cho tôi những chiều hướng mới để hình dung việc sử dụng Ký tự đại diện
người Đan Mạch
Tháng hai 2019 tại 10. 24 giờ chiều
Ví dụ ban đầu của bạn khi sử dụng A*C sẽ cho hai kết quả không chính xác vì nó bắt đầu bằng A [ABC ltd. ] nhưng không kết thúc bằng C. Bạn nên sử dụng A*C*
Tim Kemper
Tháng mười hai 2018 tại 3. 50 giờ sáng
Cảm ơn bạn
Đồng xu
Tháng mười hai 2018 tại 7. 57 giờ tối
Xin chào Sumit,
Cảm ơn bạn vì thông tin tuyệt vời này. Tôi muốn biết cách xóa các ô ghi chú, công thức và khoảng trắng khi sử dụng số và một số kết hợp chữ cái/số trong cùng một trang tính. Khoảng trắng có thể ở trước hoặc sau các tổ hợp số hoặc chữ cái/số. Khi tôi cố gắng thay thế các khoảng trắng bằng không có khoảng trắng, nó chỉ cần đặt dấu * hoặc ? . Thanks.Dolon Mukherjee
Tháng Mười 2018 lúc 12. 18 giờ chiều
Xin chào Sumit. Cảm ơn bạn rất nhiều vì đã viết một hướng dẫn thông tin như vậy. Tôi muốn biết cách tìm tên kết thúc bằng chữ “y”. Bởi vì khi tôi sử dụng “*y” thì Excel sẽ hiển thị tất cả các tên có “y” trong đó
Bạn biết
Tháng Bảy 2019 lúc 11. 37 giờ chiều
chuyển đến bộ lọc văn bản, chọn 'kết thúc bằng'
miekoo
Tháng Mười 2018 lúc 1. 11 giờ sáng
Tôi có vấn đề sau
1. Tài liệu Excel chứa 1000 hàng có mẫu URI trong Cột A
– a. trang mạng. com/path1/######
– b. trang mạng. com/path1/######/path2
– c. trang mạng. com/path1/######?query
– d. trang mạng. com/path1/######/path2?query
– e. trang mạng. com/path1/path2/######
– f. trang mạng. com/path1/path2/######/path3
– g. trang mạng. com/path1/path2/######?query
– h. trang mạng. com/path1/path2/######/path3?query2. Tôi phải trích xuất mẫu “/#######” [chính xác 8 ký tự] trong cột B dưới dạng một công thức
3. Tôi đã thử nhiều cách tra cứu ký tự đại diện khác nhau/tìm/tìm kiếm/mảng/khớp nhưng chỉ thành công một phần
miekoo
Tháng Mười 2018 lúc 1. 13 giờ sáng
Làm cách nào để ký tự đại diện CHỈ khớp với các giá trị số, loại trừ giá trị ALPHABET, chỉ ký tự đại diện của ???????
George
Tháng Mười 2019 lúc 8. 59 giờ chiều
Bạn ơi, nếu tất cả những gì bạn cần cho tất cả 1000 hàng luôn là 8 ký tự đầu tiên của các ô trong cột A, thì công thức của bạn trong B1 có thể là =LEFT[A1,8]. Điều này trích xuất 8 ký tự đầu tiên từ chuỗi gốc của bạn
Kawser Ahmed
Tháng Ba 2017 lúc 8. 06 giờ chiều
Xin chào Sumit,
Đây thực sự là một hướng dẫn chi tiết về cách sử dụng ký tự đại diện với Excel.
Tôi cũng đã viết một hướng dẫn về chủ đề tương tự. Bạn vui lòng kiểm tra nó và đưa ra những nhận xét quý báu của bạn?
Đây là liên kết của bài viết. http. //www. ưu tú. com/how-to-use-wildcards-in-excel/Brett Jones
Tháng Giêng 2017 lúc 12. 27 giờ sáng
Thanks. Rất hữu ích
Sumit Bansal
Tháng Giêng 2017 lúc 5. 26 giờ chiều
Cảm ơn vì đã nhận xét Brett. Vui mừng bạn thích nó
Bến
Tháng Chín 2016 lúc 3. 17 giờ chiều
Có cách nào sử dụng bất kỳ thủ thuật tìm thay thế nào trong số này để thay đổi một loạt ô có giá trị tăng dần trong đó, ở định dạng “Text_1_OtherText” thành “Text_01_OtherText” mà không thay đổi “Text_10_OtherText” thành “Text_010_OtherText” không?
Tôi có thể sử dụng ký tự đại diện trong câu lệnh if trong Excel không?
Công thức IF AND của Excel với các ký tự đại diện . use the COUNTIFS function with wildcards for the logical test.IFS có hỗ trợ ký tự đại diện không?
Ký tự đại diện hoạt động tốt với các hàm như SUMIFS, AVERAGEIFS và COUNTIFS; . they don't work with IF functions.Bạn có thể sử dụng ký tự đại diện trong và công thức không?
Ký tự đại diện là ký tự đặc biệt trong Microsoft Excel cho phép bạn mở rộng hoặc thu hẹp truy vấn tìm kiếm của mình. Bạn có thể sử dụng các ký tự đại diện này để tìm hoặc lọc dữ liệu và bạn cũng có thể sử dụng chúng trong công thức . Trong Excel, có ba ký tự đại diện. Dấu hoa thị [*], dấu chấm hỏi [?] và dấu ngã [~].