Làm thế nào để bạn làm nổi bật các nhóm cụ thể trong excel?

Thử nghiệm bằng cách thay đổi các giá trị trong N1 [=độ rộng băng tần] và N2 [hàng đầu tiên được đánh dấu] để thấy sự khác biệt

Một lần nữa N1 là số X của bạn [1. 10 hoặc cao hơn]

Trân trọng,
George

Svetlana Cheusheva nói.
Ngày 5 tháng 3 năm 2015 lúc 10. 27 giờ sáng

Chào LT,

Trước hết, tôi xin lỗi vì lỗi in của mình, nó phải là "công thức được viết cho _top_ ô bên trái của phạm vi"

Tôi muốn nói rằng trong các quy tắc định dạng có điều kiện, một công thức phải luôn tham chiếu đến ô ngoài cùng bên trái của phạm vi mà quy tắc áp dụng. Ví dụ: nếu bạn đang tạo quy tắc cho các ô từ A1 đến A10, bạn viết =$A1=1; . A10, thì công thức của bạn là =$A2=1

Nếu bạn đang tạo quy tắc cho A2. A10, nhưng công thức của bạn nói =$A1=1, bóng sẽ bị tắt một hàng, như báo cáo của Dio

> Nó có biết kiểm tra giá trị khi bạn đi không?

Có, Excel điều chỉnh công thức cho tất cả các hàng khác giống như khi bạn kéo công thức xuống một cột, miễn là bạn sử dụng tham chiếu ô chính xác [tuyệt đối so với. họ hàng] trong công thức của bạn

Nếu bạn làm việc với các tập dữ liệu lớn, có thể cần phải tạo chức năng tìm kiếm cho phép bạn đánh dấu nhanh các ô/hàng cho cụm từ được tìm kiếm

Mặc dù không có cách trực tiếp để thực hiện việc này trong Excel, nhưng bạn có thể tạo chức năng tìm kiếm bằng Định dạng có điều kiện

Ví dụ: giả sử bạn có tập dữ liệu như bên dưới [trong ảnh]. Nó có các cột cho Tên sản phẩm, Đại diện bán hàng và Quốc gia

Bây giờ bạn có thể sử dụng định dạng có điều kiện để tìm kiếm một từ khóa [bằng cách nhập nó vào ô C2] và đánh dấu tất cả các ô có từ khóa đó

Một cái gì đó như được hiển thị bên dưới [nơi tôi nhập tên mục vào ô B2 và nhấn Enter, toàn bộ hàng được tô sáng]

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách tạo chức năng tìm kiếm và tô sáng này trong Excel

Ở phần sau của hướng dẫn, chúng ta sẽ nâng cao một chút và xem cách làm cho nó động [để nó nổi bật khi bạn đang nhập vào hộp tìm kiếm]

Bấm vào đây để tải file ví dụ về và làm theo

Hướng dẫn này bao gồm

Tìm kiếm và đánh dấu các ô phù hợp

Trong phần này. Tôi sẽ chỉ cho bạn cách tìm kiếm và chỉ đánh dấu các ô phù hợp trong tập dữ liệu

Một cái gì đó như hình dưới đây

Dưới đây là các bước để tìm kiếm và đánh dấu tất cả các ô có văn bản phù hợp

1. Chọn tập dữ liệu mà bạn muốn áp dụng Định dạng có điều kiện [A4. F19 trong ví dụ này]
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm vào Định dạng có Điều kiện
4. Trong các tùy chọn thả xuống, nhấp vào Quy tắc mới
   
5. Trong hộp thoại 'Quy tắc định dạng mới', nhấp vào tùy chọn 'Sử dụng công thức để xác định ô cần định dạng'
6. Nhập công thức sau. =A4=$B$1
7. Nhấp vào 'Định dạng. ' cái nút
8. Chỉ định định dạng [để đánh dấu các ô khớp với từ khóa đã tìm kiếm]
9. Nhấp vào OK

Bây giờ hãy nhập bất cứ thứ gì vào ô B1 và ​​nhấn enter. Nó sẽ đánh dấu các ô phù hợp trong tập dữ liệu có chứa từ khóa trong B1

Cái này hoạt động ra sao?

Định dạng có điều kiện được áp dụng bất cứ khi nào công thức được chỉ định trong đó trả về TRUE

Trong ví dụ trên, chúng tôi kiểm tra từng ô bằng công thức =A4=$B$1

Định dạng có điều kiện kiểm tra từng ô và xác minh nội dung trong ô giống với nội dung trong ô B1. Nếu giống nhau, công thức trả về TRUE và ô được đánh dấu. Nếu không giống nhau, công thức trả về FALSE và không có gì xảy ra

Bấm vào đây để tải file ví dụ về và làm theo

Tìm kiếm và đánh dấu các hàng có dữ liệu phù hợp

Nếu bạn muốn đánh dấu toàn bộ hàng thay vì chỉ các ô phù hợp, bạn có thể làm điều đó bằng cách điều chỉnh công thức một chút

Dưới đây là một ví dụ trong đó toàn bộ hàng được tô sáng nếu loại sản phẩm khớp với loại trong ô B1

Dưới đây là các bước để tìm kiếm và đánh dấu toàn bộ hàng

1. Chọn tập dữ liệu mà bạn muốn áp dụng Định dạng có điều kiện [A4. F19 trong ví dụ này]
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm vào Định dạng có Điều kiện
4. Trong các tùy chọn thả xuống, nhấp vào Quy tắc mới
   
5. Trong hộp thoại 'Quy tắc định dạng mới', nhấp vào tùy chọn 'Sử dụng công thức để xác định ô cần định dạng'
6. Nhập công thức sau. =$B4=$B$1
7. Nhấp vào 'Định dạng. ' cái nút
8. Chỉ định định dạng [để đánh dấu các ô khớp với từ khóa đã tìm kiếm]
9. Nhấp vào OK

Các bước trên sẽ tìm kiếm mục được chỉ định trong tập dữ liệu và nếu tìm thấy mục phù hợp, nó sẽ đánh dấu toàn bộ hàng

Lưu ý rằng điều này sẽ chỉ kiểm tra cột mục. Nếu bạn nhập tên Đại diện bán hàng ở đây, nó sẽ không hoạt động. Nếu bạn muốn nó hoạt động cho tên Đại diện bán hàng, bạn cần thay đổi công thức thành =$C4=$B$1

Ghi chú. Lý do nó làm nổi bật toàn bộ hàng chứ không chỉ ô phù hợp là vì chúng tôi đã sử dụng ký hiệu $ trước tham chiếu cột [$B4]. Bây giờ khi định dạng có điều kiện phân tích các ô trong một hàng, nó sẽ kiểm tra xem giá trị trong cột B của hàng đó có bằng giá trị trong ô B1 hay không. Vì vậy, ngay cả khi nó đang phân tích A4 hoặc B4 hoặc C4, v.v., nó chỉ kiểm tra giá trị B4 [vì chúng tôi đã khóa cột B bằng cách sử dụng ký hiệu đô la]

Bạn có thể đọc thêm về tham chiếu tuyệt đối, tương đối và hỗn hợp tại đây

Tìm kiếm và Đánh dấu Hàng [dựa trên Kết hợp Một phần]

Trong một số trường hợp, bạn có thể muốn đánh dấu các hàng dựa trên kết quả khớp một phần

Ví dụ: nếu bạn có các mục như Bảng trắng, Bảng xanh và Bảng xám và bạn muốn đánh dấu tất cả những mục này dựa trên bảng từ, thì bạn có thể thực hiện việc này bằng chức năng TÌM KIẾM

Một cái gì đó như hình dưới đây

Dưới đây là các bước để làm điều này

1. Chọn tập dữ liệu mà bạn muốn áp dụng Định dạng có điều kiện [A4. F19 trong ví dụ này]
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm vào Định dạng có Điều kiện
4. Trong các tùy chọn thả xuống, nhấp vào Quy tắc mới
   
5. Trong hộp thoại 'Quy tắc định dạng mới', nhấp vào tùy chọn 'Sử dụng công thức để xác định ô cần định dạng'
6. Nhập công thức sau. =AND[$B$1””,ISNUMBER[SEARCH[$B$1,$B4]]]
7. Nhấp vào 'Định dạng. ' cái nút
8. Chỉ định định dạng [để đánh dấu các ô khớp với từ khóa đã tìm kiếm]
9. Nhấp vào OK

Cái này hoạt động ra sao?

• Hàm TÌM KIẾM tìm kiếm chuỗi/từ khóa tìm kiếm trong tất cả các ô liên tiếp. Nó trả về lỗi nếu không tìm thấy từ khóa tìm kiếm và trả về một số nếu tìm thấy kết quả khớp
• Hàm ISNUMBER chuyển đổi lỗi thành FALSE và các giá trị số thành TRUE
• Hàm AND kiểm tra một điều kiện bổ sung – ô C2 không được để trống

Vì vậy, bây giờ, bất cứ khi nào bạn nhập từ khóa vào ô B1 và ​​nhấn Enter, nó sẽ tô sáng tất cả các hàng có các ô chứa từ khóa đó

Mẹo thưởng . Nếu bạn muốn phân biệt chữ hoa chữ thường trong tìm kiếm, hãy sử dụng Hàm TÌM thay vì TÌM KIẾM.

Bấm vào đây để tải file ví dụ về và làm theo

Chức năng tìm kiếm động và đánh dấu [Làm nổi bật khi bạn nhập]

Sử dụng các thủ thuật Định dạng có điều kiện tương tự được trình bày ở trên, bạn cũng có thể tiến thêm một bước và làm cho nó động.

Ví dụ: bạn có thể tạo thanh tìm kiếm trong đó dữ liệu phù hợp được đánh dấu khi bạn nhập vào thanh tìm kiếm

Một cái gì đó như hình dưới đây

Điều này có thể được thực hiện bằng cách sử dụng các điều khiển ActiveX và có thể là một chức năng tốt để sử dụng khi tạo báo cáo hoặc bảng điều khiển

Dưới đây là video tôi hướng dẫn cách tạo cái này

Bạn có thấy hướng dẫn này hữu ích không?

Bạn cũng có thể thích các Hướng dẫn Excel sau

• Dynamic Excel Filter – Trích xuất dữ liệu khi bạn nhập
• Tạo danh sách thả xuống với đề xuất tìm kiếm
• Tạo bản đồ nhiệt trong Excel
• Đánh dấu hàng dựa trên giá trị ô trong Excel
• Đánh dấu hàng và cột hiện hoạt trong phạm vi dữ liệu trong Excel
• Cách đánh dấu các ô trống trong Excel

21 suy nghĩ về “Tìm kiếm và đánh dấu dữ liệu bằng cách sử dụng định dạng có điều kiện”

1. Britta

   Tôi đang cố gắng thực hiện chính xác điều này nhưng với một bước bổ sung và tôi không biết làm thế nào. Tôi muốn quét vào hộp tìm kiếm và đánh dấu cột của ô tương ứng và vẫn được đánh dấu cho đến khi tôi hoàn thành tất cả các lần quét. Sau đó, tôi chỉ muốn xóa các định dạng đánh dấu và giữ lại bảng tính ban đầu để thực hiện lại từ đầu. Tôi đang tạo một hệ thống theo dõi và sắp xếp lại hàng tồn kho thực sự thấp mà bất kỳ ai cũng có thể sử dụng. Điều này thậm chí có thể ??

2. Sơn

   Trang tính 1 cột A. Một số ô có số [giả sử 100 số].
   Sheet2 cột A, B C & D có một vài số [giả sử 50 số] khớp chính xác/bằng với sheet1numbers.
   Bây giờ muốn đánh dấu các ô hoặc hàng của sheet1 trên cơ sở số của sheet2.
   Tnx cho tất cả các chuyên gia trước.

3. Faruk

   giải thích sai

4. sanath

   bài học tốt

5. mohamed ikrima

   Chào. tôi cần trợ giúp về excel

6. MLA

   Công thức này
   =AND[$B$1″”,ISNUMBER[SEARCH[$B$1,$B4]]]
   từ Tìm kiếm và
   is wrong…
   There is a problem with this formular, is all excel have to say about this

   • MLA

     Nevermind , thay thế , thông qua ;

     • kiện

       Tôi không thể làm cho công thức này hoạt động. Bạn đã thay thế cái gì=AND[$B$1″”,ISNUMBER[SEARCH[$B$1,$B4]]]

7. Tahj

   Có cách nào để đưa dữ liệu được đánh dấu từ bộ lọc tìm kiếm lên đầu tập dữ liệu không? .
   Cảm ơn

8. nitram ksub

   HI
   Tôi không thể làm cho công thức hoạt động. Excel sẽ không nhận ra nó.
   Tôi đã dịch sang tiếng Đan Mạch.

   vấn đề có vẻ là “”,ELLER

   bất kì giải pháp nào?

   =OG[$C$2″”,ELLER[ER. TAL[SØG[$c$2,$A7. H7,1]]]]

   sẽ đánh giá cao nó rất nhiều

   Nitram

9. Sergio

   Xin lỗi anh. Tôi không hiểu tại sao, trong trường hợp của tôi, nó không hoạt động. Trong các cược của các tình huống, nó nổi bật như hai ô mà họ không có gì để làm

10. EmoCore Zurc

    Cảm ơn Sumit

11. marco alexis maghuyop

    Xin chào, Tại sao =AND[$C$2″”, OR[ISNUMBER[SEARCH[$C$2, $B5]]]] hoạt động? Vì tôi đã thử công thức đó để ngay cả khi tôi chỉ nhập một chuỗi hoặc một từ khóa . Công thức đó hoạt động nhưng nếu tôi cố gắng nhập sản phẩm, nhưng nếu tôi cố gắng nhập tên và địa lý thì nó không hoạt động

12. kiran

    Xin chào Sumit,
    Tôi thấy Tìm kiếm và đánh dấu các ô bằng Định dạng có điều kiện rất hữu ích, tuy nhiên, tôi đã thử mọi khả năng khác nhưng không làm nổi bật bất kỳ dữ liệu nào. Áp dụng tương tự với biểu đồ Gantt Tôi không thể định dạng lịch ngày. Tôi đang sử dụng Microsoft office 2007. Hy vọng bạn có thể giúp tôi ra ngoài.

    • Χρήστος Φλωροπουλος

      Xin chào , tôi cũng không thấy sự cần thiết của OR ở đây, tôi đã sử dụng =SEARCH[input;$B16&$C16&$D16&$E16&$F16] , trong đó $B16 &…. phạm vi của bạn để hàng được tô sáng và “nhập” tên của ô tìm kiếm, đồng thời đảm bảo rằng bạn chọn khu vực bạn muốn bằng Shift+Ctrl trước khi chuyển đến Định dạng có điều kiện, hy vọng nó phù hợp với bạn

13. con sâu

    Tôi không hiểu tại sao chúng ta lại sử dụng các hàm OR ở đây –

    =AND[$C$2″”,OR[$C$2=$B5. $D5]]

    • Sumit Bansal

      Công thức OR sẽ làm nổi bật toàn bộ hàng. Công thức đầu tiên giới thiệu cách bạn chỉ có thể đánh dấu các ô phù hợp [trong cột quốc gia]. Tôi đã sử dụng công thức OR trong tab thứ hai [trong tệp tải xuống], trong đó mục đích là làm nổi bật toàn bộ hàng

      • con sâu

        Tôi hiểu HOẶC là đánh dấu toàn bộ hàng nhưng tôi không hiểu logic. Ý tôi là không có HOẶC tại sao nó không hoạt động? . $D5]

        • Sumit Bansal

          Nó không hoạt động nếu không có OR như $C$2=$B5. $D5 không trả về TRUE hoặc FALSE. Thay vào đó, nó trả về một mảng Đúng/Sai. Nếu bạn không sử dụng OR, sẽ luôn có FALSE trong mảng và do đó AND sẽ luôn trả về FALSE

14. con sâu

    Tôi có thắc mắc, tại sao C2 không bằng D5, ý tôi là –> =AND[$C$2″”,$C$2=D5] nhưng thay vào đó bạn lại sử dụng =AND[$C$2″”,$C$2=B5

    Bạn có thể vui lòng giải thích ?

    • Sumit Bansal

      Xin chào Deepak. khi chúng ta chọn toàn bộ phạm vi [B5. D22] trong trường hợp này, công thức được áp dụng để lưu ý đến ô hiện hoạt. Và định dạng có điều kiện đánh giá từng ô dựa trên nó. Ví dụ: khi nó đang đánh giá ô B5, nó đã sử dụng công thức =AND[$C$2″”,$C$2=B5]. Khi nó di chuyển đến ô C5, nó sẽ đánh giá nó bằng công thức =AND[$C$2″”,$C$2=C5]

      Làm cách nào để thêm định dạng có điều kiện làm nổi bật các nhóm trong Excel?

      Trên tab Trang chủ, trong nhóm Kiểu, nhấp vào mũi tên bên cạnh Định dạng có điều kiện, sau đó nhấp vào Đánh dấu quy tắc ô . Chọn lệnh bạn muốn, chẳng hạn như Giữa, Bằng với Văn bản Chứa hoặc Ngày Xảy ra. Nhập các giá trị bạn muốn sử dụng, sau đó chọn một định dạng.

      Điều gì cho phép bạn đánh dấu các ô đáp ứng các tiêu chí cụ thể trong Excel?

      Định dạng có điều kiện trong Excel được sử dụng để áp dụng định dạng nhất định cho dữ liệu đáp ứng một hoặc nhiều điều kiện. Giống như định dạng ô thông thường, nó cho phép bạn đánh dấu và phân biệt dữ liệu của mình theo nhiều cách khác nhau bằng cách thay đổi màu tô, màu phông chữ, kiểu đường viền, v.v. của ô