Cách sử dụng ký tự đại diện?

Posted on: 2020年11月12日 2020年11月13日
Tags: tips

Trong bài viết này, chúng ta sẽ tìm hiểu về cách và khi nào sử dụng các ký tự Wildcard hoặc các ký tự regex (biểu thức chính quy) được tạo sẵn cho các hàm excel.

Excel cho phép bạn sử dụng các ký tự Đại diện để bắt các chuỗi và thực hiện các chức năng trên đó.

Có ba ký tự đại diện trong Excel. Dấu hỏi (?): Ký tự đại diện này được sử dụng để tìm kiếm bất kỳ ký tự đơn nào.

Dấu hoa thị (): Ký tự đại diện này được sử dụng để tìm bất kỳ số ký tự nào đứng trước hoặc sau bất kỳ ký tự nào.
Dấu ngã (~): Ký tự đại diện này là một ký tự thoát, được sử dụng trước dấu hỏi (?) Hoặc dấu hoa thị ().

Hãy sử dụng các ký tự đại diện này trong các ví dụ Ở đây, chúng ta cần các sản phẩm bắt đầu bằng C. Vì vậy, trước tiên chúng ta sẽ áp dụng bộ lọc cho danh sách bằng Ctrl + Shift + L. Sau đó, trong hộp tìm kiếm Sản phẩm, nhập “C *” như hình dưới đây.

Như bạn có thể thấy, toàn bộ danh sách được lọc với tất cả các sản phẩm Bắt đầu bằng C.

Lưu ý: * (ký tự đại diện) tìm bất kỳ số ký tự nào.

Ở đây chúng ta cần các giá trị Số lượng nằm trong khoảng từ 30-39. Vì vậy, trước tiên, chúng tôi sẽ áp dụng bộ lọc cho danh sách bằng Ctrl + Shift + L. Sau đó, trong hộp tìm kiếm số lượng, nhập “3?” như hình bên dưới.

Như bạn có thể thấy, toàn bộ danh sách được lọc với tất cả các sản phẩm Bắt đầu bằng C.

Ghi chú: ? (ký tự đại diện) tìm các ký tự đơn lẻ.

Ký tự đại diện dấu ngã (~) được sử dụng khi chúng ta cần tìm biểu tượng dấu hỏi hoặc dấu hoa thị (không phải dưới dạng ký tự đại diện). Ví dụ: nếu bạn đang tìm kiếm một chuỗi kết thúc bằng dấu chấm hỏi. Bạn gõ “* ~?” . Điều này sẽ tìm cho bạn tất cả các chuỗi có dấu chấm hỏi.

Ở đây chúng ta cần tìm dữ liệu kết thúc bằng (dấu gạch ngang). Vì vậy, chúng tôi sẽ áp dụng hai ký tự đại diện ~. * Dấu gạch ngang đầu tiên được sử dụng làm ký tự đại diện và ký tự thứ hai được sử dụng để tìm ký hiệu astrick trong dữ liệu.

Lưu ý: ~ (ký tự đại diện) tìm thấy hai ký tự &?

Không phải tất cả các hàm đều có thể thực thi Ký tự đại diện với các hàm Excel.

Đây là danh sách các hàm chấp nhận ký tự đại diện.

AVERAGEIF, AVERAGEIFS COUNTIF, COUNTIFS SUMIF, SUMIFS VLOOKUP, HLOOKUP MATCH TÌM KIẾM ===== Ví dụ: Tính tổng số tiền chi cho xoài. Trong hình trên, chúng ta có bảng này về số tiền chi tiêu cho các loại trái cây khác nhau trong các tháng khác nhau. Chúng tôi chỉ cần tính tổng số tiền chi tiêu cho xoài trong tất cả những tháng này.

Trong I2 công thức là

Điều này mang lại 525 là tổng số tiền chi cho xoài. Bạn có thể thấy điều này trong hình ảnh trên.

Nó hoạt động như thế nào? Vâng, nó là dễ dàng. Hãy chia nhỏ công thức và hiểu nó từng phần.

(B2: B9 = H2): Phần này so sánh từng giá trị trong phạm vi B2: B9 với H2 và trả về một mảng TRUE và FALSE.

\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.

(B2: B9 = H2) * C2: E9: Ở đây chúng ta nhân từng giá trị trong mảng trên với các giá trị trong C2: E9. C2: C9 cũng được coi là một mảng 2D. Cuối cùng, câu lệnh này trả về một mảng 2D \ {0,0,0; 47,57,67; 0,0,0; 0,0,0; 0,0,0; 0,0,0; 0,0, 0; 108,118,128}.

Nó chỉ có giá trị của xoài. Nó tổng hợp chúng và trả về kết quả là 525.

Một phương pháp khác có thể có một cột tổng và sau đó sử dụng nó với hàm SUMIF để lấy tổng của tất cả các cột. Nhưng đó không phải là những gì chúng tôi muốn làm.

Hàm SUMIF trong Excel

Như tên cho thấy, công thức SUMIF trong Excel tính tổng các giá trị trong một phạm vi với một điều kiện nhất định.

Công thức SUMIF chung trong Excel:

=SUMIF(condition_range,condition,sum range)

Hãy xem một ví dụ. Nhưng lý thuyết…, Ah! Chúng tôi sẽ đề cập đến nó sau.

Sử dụng SUMIF để tính tổng các giá trị trên một điều kiện Đối với ví dụ này, tôi đã chuẩn bị dữ liệu này.

Dựa trên dữ liệu này, chúng tôi cần trả lời các câu hỏi sau:

Hãy bắt đầu với câu hỏi đầu tiên.

SUMIF với điều kiện văn bản Chúng ta cần cho biết tổng giá mà Ravi đã thắng.

Vì vậy, phạm vi điều kiện của chúng tôi sẽ là phạm vi tên và đó là A2: A10.

Điều kiện của chúng ta là Ravi và phạm vi Sum là E2: E10.

Vì vậy trong ô I2 chúng ta sẽ viết:

=SUMIF(A2:A10,”ravi”,E2:E10)

Lưu ý rằng ravi nằm trong dấu ngoặc kép. Điều kiện văn bản luôn được viết trong dấu ngoặc kép. Đây không phải là trường hợp của những con số.

Lưu ý rằng ravi được viết bằng tất cả các chữ nhỏ. Vì SUMIF không phân biệt chữ hoa chữ thường, do đó nó không thành vấn đề.

Công thức SUMIF ở trên sẽ trả về 31907 như thể hiện trong hình dưới đây.

SUMIF với các toán tử logic Đối với câu hỏi thứ hai, phạm vi điều kiện của chúng ta sẽ là D2: D10.

Điều kiện là> 70 và Phạm vi tổng giống như trước.

=SUMIF(D2:D10,”>70″,E2:E10)

Công thức SUMIF ở trên sẽ trả về 103973 như trong hình dưới đây.

SUMIF với các nhà khai thác thẻ hoang dã Trong câu hỏi thứ ba, điều kiện của chúng tôi là Delhi. Nhưng chúng tôi không có cột thành phố. Hmmm… Vậy chúng ta có gì? Aha! Mã thành phố. Điều này sẽ hoạt động.

Chúng tôi biết rằng tất cả các mã thành phố Delhi đều bắt đầu từ 1100. Mã thành phố có 6 chữ số. Vì vậy, chúng tôi biết nó là 1100 ??. “?” toán tử được sử dụng khi chúng ta biết số lượng ký tự nhưng không biết ký tự. Như ở đây, chúng tôi biết rằng có hai số nữa sau 1100. Chúng có thể là bất cứ thứ gì, vì vậy chúng tôi đã sử dụng “?”. Nếu không biết số lượng ký tự, chúng tôi sẽ sử dụng “*”.

Hãy nhớ rằng các toán tử thẻ đại diện chỉ hoạt động với các giá trị văn bản. Do đó bạn cần chuyển đổi mã thành phố thành văn bản.

Bạn có thể nối các số với “” để biến chúng thành giá trị văn bản.

(formula to convert number into text) _ = number & “”

_or =CONCATENATE(number,””)

Bây giờ trong ô I2 viết công thức này

=SUMIF(B2:B10,”1100??”,E2:E10)

Điều này sẽ trả về tổng giá có mã thành phố bắt đầu bằng 1100.

Trong ví dụ của chúng tôi, nó là 79836.

Ghi chú chuyên nghiệp:

Nếu phạm vi tổng và phạm vi điều kiện của bạn giống nhau, bạn có thể bỏ qua biến sum_range trong hàm SUMIF. = SUMIF (E2: E10, “> 15000”) và = SUMIF (E2: E10, “> 15000”, E2: E10) sẽ cho ra kết quả tương tự, 56163.
Giá trị văn bản được gói gọn trong dấu ngoặc kép, nhưng số thì không.

SUMIF (C2: C10,103, E2: E10) điều này sẽ hoạt động tốt và sẽ trả về 28026.

Tuy nhiên, trong khi làm việc với các toán tử logic, bạn cần sử dụng dấu ngoặc kép. Giống như ví dụ của chúng tôi = SUMIF (D2: D10, “> 70”, E2: E10)

Nó chỉ có thể kiểm tra một điều kiện. Đối với nhiều điều kiện, chúng tôi sử dụng hàm SUMIFS trong Excel.

Sooo… Điều này có hữu ích không? Hãy cho tôi biết trong phần ý kiến. Yêu cầu truy vấn SUMIF được cá nhân hóa của bạn nếu bạn vẫn gặp khó khăn.

Vì vậy, các bạn, đây là cách bạn có thể sử dụng các ký tự đại diện ?. Hãy cho tôi biết nếu bạn có bất kỳ nghi ngờ nào về bài viết này hoặc bất kỳ bài viết nào khác trên trang web này. Bạn cũng có thể hỏi các truy vấn liên quan đến Excel 2019, 2016, 2013 và các phiên bản cũ hơn.