Hướng Dẫn Cách Tạo Hộp Tìm Kiếm Và Tô Màu Dữ Liệu Trong Excel

Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính:

Cơ chế hoạt động: Sử dụng Conditional Formatting kết hợp với công thức logic (TRUE/FALSE) để tự động định dạng.
Tô màu ô đơn: Áp dụng công thức so sánh tuyệt đối để làm nổi bật chính xác từ khóa.
Tô màu toàn bộ hàng: Sử dụng tham chiếu hỗn hợp (Mixed References) để khóa cột kiểm tra nhưng áp dụng định dạng cho cả dòng.
Tìm kiếm gần đúng: Kết hợp hàm SEARCH (hoặc FIND) và ISNUMBER để xử lý các từ khóa một phần.
Tự động hóa: Cách thiết lập để Excel tự động tô màu ngay khi dữ liệu thay đổi.

Việc quản lý và phân tích các tập dữ liệu lớn trong Excel đòi hỏi sự chính xác và tốc độ. Khi bạn đối mặt với bảng tính chứa hàng nghìn dòng, việc tìm kiếm thủ công một giá trị cụ thể giống như “mò kim đáy bể”. Mặc dù Excel không có tính năng tích hợp sẵn để tạo thanh tìm kiếm tô màu trực tiếp, bạn hoàn toàn có thể xây dựng chức năng này thông qua Conditional Formatting (Định dạng có điều kiện).

Phương pháp này biến một ô tính bất kỳ thành hộp tìm kiếm. Khi bạn nhập từ khóa, Excel sẽ lập tức quét toàn bộ dữ liệu và làm nổi bật các ô hoặc hàng tương ứng. Kỹ thuật này đặc biệt hữu ích cho các báo cáo quản trị, bảng theo dõi kho hàng hoặc danh sách nhân sự, giúp người xem định vị thông tin quan trọng ngay lập tức.

Demo tính năng tìm kiếm và tô màu dữ liệu trong Excel

Bài viết này sẽ hướng dẫn chi tiết quy trình thiết lập công cụ tìm kiếm nội bộ này, từ cấp độ cơ bản (tô màu ô) đến nâng cao (tô màu hàng và tìm kiếm gần đúng).

1. Cơ Chế Hoạt Động Của Conditional Formatting Trong Tìm Kiếm

Trước khi đi vào các bước kỹ thuật, bạn cần hiểu rõ nguyên lý cốt lõi. Conditional Formatting hoạt động dựa trên logic Boolean (Đúng/Sai).

Khi bạn thiết lập một quy tắc (Rule), Excel sẽ kiểm tra từng ô trong vùng dữ liệu được chọn:

Nếu công thức trả về kết quả TRUE: Định dạng (màu sắc, phông chữ) sẽ được áp dụng.
Nếu công thức trả về kết quả FALSE: Ô giữ nguyên định dạng mặc định.

Để tạo hộp tìm kiếm, chúng ta sẽ viết một công thức liên kết từng ô trong vùng dữ liệu với “ô tìm kiếm” (ví dụ: ô B1 hoặc C2). Sự thay đổi giá trị tại ô tìm kiếm sẽ kích hoạt lại quá trình kiểm tra logic này trên toàn bộ bảng tính.

2. Cách Tìm Kiếm Và Tô Màu Các Ô Trùng Khớp (Matching Cells)

Đây là cấp độ cơ bản nhất. Mục tiêu là khi nhập từ khóa vào ô tìm kiếm, chỉ những ô chứa dữ liệu y hệt mới được tô màu.

Ví dụ: Bạn có danh sách sản phẩm và muốn tìm tất cả các ô chứa chữ “Printer”.

Ví dụ về việc chỉ tô màu các ô khớp với từ khóa

Quy trình thực hiện chi tiết

Để thiết lập tính năng này, hãy thực hiện chính xác theo 9 bước sau:

Chọn vùng dữ liệu: Bôi đen toàn bộ vùng dữ liệu bạn muốn áp dụng tìm kiếm (ví dụ: A4:F19). Lưu ý không chọn tiêu đề bảng để tránh định dạng nhầm tiêu đề.
Truy cập Conditional Formatting: Trên thanh công cụ (Ribbon), chọn thẻ Home.Vị trí thẻ Home trên thanh công cụ Excel
Tại nhóm Styles, nhấp vào Conditional Formatting.
Trong menu thả xuống, chọn New Rule.Chọn New Rule trong menu Conditional Formatting
Chọn loại quy tắc: Trong hộp thoại “New Formatting Rule”, chọn dòng cuối cùng: Use a formula to determine which cells to format (Sử dụng công thức để xác định ô cần định dạng).Hộp thoại New Formatting Rule trong Excel
Nhập công thức logic: Nhập công thức sau vào ô “Format values where this formula is true”:
=A4=$B$1
(Trong đó: A4 là ô đầu tiên của vùng dữ liệu được chọn, B1 là ô dùng để nhập từ khóa tìm kiếm).Nhập công thức so sánh dữ liệu
Thiết lập định dạng: Nhấp vào nút Format….Nút Format để thiết lập màu sắc
Chọn thẻ Fill và chọn màu sắc mong muốn (ví dụ: màu cam hoặc vàng để dễ nhìn). Nhấp OK.Chọn màu nền để làm nổi bật ô tìm kiếm
Nhấp OK một lần nữa để hoàn tất.

Giải thích công thức `=A4=$B$1`

A4: Đây là tham chiếu tương đối (Relative Reference). Excel sẽ ngầm hiểu A4 là đại diện cho ô đang được xét. Khi xét đến ô A5, công thức tự động đổi thành A5=$B$1.
$B$1: Đây là tham chiếu tuyệt đối (Absolute Reference). Dấu $ khóa cố định ô B1. Dù Excel đang xét ô nào trong bảng (A4, B10, F19…), nó vẫn luôn so sánh với giá trị tại ô tìm kiếm B1.

Kết quả: Bất kỳ ô nào có nội dung giống hệt ô B1 sẽ trả về TRUE và được tô màu.

3. Cách Tô Màu Toàn Bộ Hàng Chứa Dữ Liệu (Highlight Rows)

Trong thực tế, việc tô màu chỉ một ô đơn lẻ đôi khi không cung cấp đủ ngữ cảnh. Bạn thường cần xem toàn bộ thông tin của hàng đó (ví dụ: Tên sản phẩm, Người bán, Doanh số, Khu vực). Kỹ thuật dưới đây sẽ giúp bạn tô màu toàn bộ dòng khi một cột cụ thể khớp với từ khóa.

Bảng dữ liệu được tô màu toàn bộ hàng khi khớp từ khóa

Quy trình thực hiện

Các bước thực hiện tương tự như phần trên, nhưng công thức sẽ có sự thay đổi quan trọng:

Chọn vùng dữ liệu (A4:F19).
Vào Home > Conditional Formatting > New Rule.
Chọn Use a formula to determine which cells to format.
Nhập công thức sau:
=$B4=$B$1Công thức tô màu toàn bộ hàng dựa trên cột B
Thiết lập màu sắc tại nút Format và nhấn OK.

Phân tích sự khác biệt: Tham chiếu hỗn hợp (Mixed Reference)

Điểm mấu chốt nằm ở công thức =$B4=$B$1.

$B4: Dấu $ đặt trước chữ B (khóa cột) nhưng không đặt trước số 4 (thả lỏng dòng).
Cơ chế: Khi Excel định dạng cho ô A4, nó kiểm tra xem B4 có bằng B1 không. Khi Excel định dạng cho ô C4, nó vẫn quay lại kiểm tra xem B4 có bằng B1 không.
Hệ quả: Vì tất cả các ô trên hàng 4 (A4, B4, C4, D4…) đều dựa vào giá trị của ô B4 để quyết định đúng/sai, nên nếu B4 khớp, cả hàng 4 sẽ cùng đổi màu.

Lưu ý quan trọng: Công thức trên chỉ tìm kiếm trong cột B (Tên sản phẩm). Nếu bạn nhập tên “Người bán” (cột C) vào ô tìm kiếm, công thức này sẽ không hoạt động. Để tìm theo cột C, bạn cần sửa công thức thành =$C4=$B$1.

4. Tìm Kiếm Dữ Liệu Gần Đúng (Partial Match)

Hai phương pháp trên yêu cầu dữ liệu phải khớp chính xác 100%. Tuy nhiên, người dùng thường chỉ nhớ một phần của tên sản phẩm (ví dụ: nhớ “Board” thay vì “White Board”). Để giải quyết vấn đề này, chúng ta cần kết hợp hàm SEARCH (Tìm kiếm chuỗi).

Tìm kiếm và tô màu dữ liệu dựa trên từ khóa không đầy đủ

Công thức cho tìm kiếm một phần

Thực hiện các bước tạo Rule mới như trên, và sử dụng công thức sau:

=AND($B$1<>"",ISNUMBER(SEARCH($B$1,$B4)))

Công thức phức hợp để tìm kiếm một phần chuỗi ký tự

Giải phẫu công thức

Công thức này bao gồm ba lớp logic hoạt động phối hợp:

SEARCH($B$1, $B4): Hàm này tìm vị trí của từ khóa (B1) trong ô dữ liệu (B4).
- Nếu tìm thấy: Nó trả về một con số (vị trí bắt đầu của ký tự).
- Nếu không tìm thấy: Nó trả về lỗi #VALUE!.
ISNUMBER(…): Hàm này kiểm tra kết quả của hàm SEARCH.
- Nếu là số (tìm thấy): Trả về TRUE.
- Nếu là lỗi (không tìm thấy): Trả về FALSE.
AND($B$1<>””, …): Hàm AND đảm bảo hai điều kiện phải cùng đúng:
- Ô tìm kiếm ($B$1) không được để trống (<>"").
- Từ khóa phải được tìm thấy trong dữ liệu (ISNUMBER).
- Tại sao cần điều này? Nếu không có điều kiện $B$1<>"", khi bạn để trống ô tìm kiếm, Excel sẽ coi đó là tìm thấy “rỗng” trong mọi ô và tô màu toàn bộ bảng tính, gây rối mắt.

Phân biệt SEARCH và FIND

SEARCH: Không phân biệt chữ hoa/thường (Case-insensitive). Tìm “board” sẽ ra cả “Board”, “BOARD”. Đây là lựa chọn tốt nhất cho ô tìm kiếm thông thường.
FIND: Phân biệt chữ hoa/thường (Case-sensitive). Tìm “Board” sẽ không ra “board”. Sử dụng hàm này nếu dữ liệu của bạn yêu cầu độ chính xác tuyệt đối về ký tự.

5. Mở Rộng: Tìm Kiếm Trên Nhiều Cột

Nếu bạn muốn tạo một ô tìm kiếm “đa năng”, có thể tìm thấy từ khóa bất kể nó nằm ở cột Sản phẩm, Tên người bán hay Quốc gia, bạn cần mở rộng phạm vi kiểm tra của công thức.

Thay vì chỉ kiểm tra cột B, bạn có thể nối chuỗi các cột lại với nhau.
Công thức gợi ý:
=AND($B$1<>"",ISNUMBER(SEARCH($B$1,$B4&$C4&$D4)))

Công thức này sẽ ghép nội dung cột B, C và D của dòng hiện tại lại thành một chuỗi văn bản dài, sau đó tìm kiếm từ khóa trong chuỗi tổng hợp đó. Nếu từ khóa xuất hiện ở bất kỳ cột nào trong ba cột này, cả hàng sẽ được tô màu.

6. Tạo Hộp Tìm Kiếm Động (Dynamic Search)

Phương pháp Conditional Formatting nêu trên sẽ cập nhật màu sắc ngay sau khi bạn nhấn phím Enter. Tuy nhiên, để tạo trải nghiệm người dùng cao cấp hơn – nơi các ô được tô màu ngay lập tức khi bạn đang gõ từng ký tự (giống Google Search) – bạn cần sử dụng ActiveX Controls (cụ thể là Text Box) kết hợp với một chút mã VBA đơn giản để liên kết Text Box với một ô tham chiếu.

Hiệu ứng tô màu động ngay khi nhập liệu trong Excel

Mặc dù việc này đòi hỏi kỹ thuật cao hơn, nhưng nguyên lý cốt lõi vẫn dựa trên các quy tắc Conditional Formatting mà chúng ta đã thiết lập ở trên. Ô liên kết (Linked Cell) của Text Box sẽ đóng vai trò như ô B1 trong các ví dụ trước.

7. Các Lỗi Thường Gặp Và Cách Khắc Phục

Trong quá trình thiết lập, người dùng thường gặp phải một số lỗi khiến tính năng không hoạt động như ý muốn. Dưới đây là các điểm kiểm tra nhanh:

Tham chiếu bị sai lệch: Lỗi phổ biến nhất là quên dấu $ hoặc đặt sai vị trí. Hãy nhớ nguyên tắc: Khóa ô tìm kiếm ($B$1) và khóa cột kiểm tra ($B4) nếu muốn tô màu cả dòng.
Vùng áp dụng (Applies to) bị lệch: Đôi khi việc chèn/xóa dòng làm thay đổi vùng áp dụng của Rule. Hãy vào Conditional Formatting > Manage Rules để kiểm tra lại phạm vi trong ô “Applies to”.
Thứ tự ưu tiên của Rule: Nếu một ô chịu tác động của nhiều quy tắc Conditional Formatting, quy tắc nằm trên cùng trong danh sách “Manage Rules” sẽ được ưu tiên. Đảm bảo quy tắc tìm kiếm của bạn nằm trên cùng nếu muốn nó đè lên các màu sắc khác.
Dữ liệu có khoảng trắng thừa: Từ khóa “Apple ” (có dấu cách cuối) sẽ không khớp với “Apple” trong bảng dữ liệu nếu dùng phép so sánh chính xác (=). Hàm SEARCH hoặc dùng thêm hàm TRIM có thể khắc phục điều này.

FAQ: Câu Hỏi Thường Gặp Về Tìm Kiếm Dữ Liệu Trong Excel

Câu hỏi 1: Làm thế nào để xóa bỏ quy tắc tô màu tìm kiếm đã tạo?
Trả lời: Bạn chọn vùng dữ liệu, vào thẻ Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Hoặc vào Manage Rules, chọn quy tắc cần xóa và nhấn nút Delete Rule.

Câu hỏi 2: Tôi có thể sử dụng nhiều màu sắc khác nhau cho các từ khóa khác nhau không?
Trả lời: Có. Bạn cần tạo nhiều Rule (Quy tắc) riêng biệt. Ví dụ: Rule 1: Nếu ô B1 = “High”, tô màu đỏ. Rule 2: Nếu ô B1 = “Medium”, tô màu vàng. Excel sẽ xử lý các quy tắc này theo thứ tự ưu tiên.

Câu hỏi 3: Conditional Formatting có làm chậm file Excel không?
Trả lời: Với các tập dữ liệu nhỏ và vừa (dưới 10.000 dòng), tác động là không đáng kể. Tuy nhiên, nếu bạn sử dụng các công thức mảng phức tạp (như SEARCH trên nhiều cột) trên hàng trăm nghìn dòng, tốc độ tính toán của Excel có thể bị ảnh hưởng mỗi khi bạn thay đổi dữ liệu hoặc từ khóa tìm kiếm.

Câu hỏi 4: Tại sao khi tôi copy định dạng sang bảng khác thì công thức bị lỗi?
Trả lời: Khi copy Conditional Formatting, các tham chiếu trong công thức có thể bị dịch chuyển nếu bạn không khóa cố định chúng (dùng $). Hãy kiểm tra lại công thức trong phần Manage Rules của bảng mới để đảm bảo tham chiếu đang trỏ đúng ô tìm kiếm.

Nguồn Tham Khảo

Microsoft Support: Use conditional formatting to highlight information – Tài liệu chính thức từ Microsoft về cách sử dụng định dạng có điều kiện.
Excel Jet: Excel Formula for Conditional Formatting based on another cell – Hướng dẫn chi tiết về các công thức logic trong định dạng điều kiện.
Ablebits: Excel Conditional Formatting formulas – Tổng hợp các công thức nâng cao và ứng dụng thực tế.
W3Schools: Excel Conditional Formatting – Bài học cơ bản về các quy tắc định dạng trong Excel.