Търсене и открояване на данни в Excel (с условно форматиране)

Гледайте видео - Търсете и маркирайте данни, използвайки условно форматиране

Ако работите с големи набори от данни, може да се наложи да създадете функционалност за търсене, която ви позволява бързо да маркирате клетки/редове за търсения термин.

Въпреки че няма пряк начин да направите това в Excel, можете да създадете функционалност за търсене с помощта на условно форматиране.

Да предположим например, че имате набор от данни, както е показано по -долу (на изображението). Той има колони за Име на продукта, Търговски представител и Държава.

Сега можете да използвате условно форматиране, за да търсите ключова дума (като я въведете в клетка C2) и да маркирате всички клетки, които имат тази ключова дума.

Нещо, както е показано по -долу (където въвеждам името на елемента в клетка В2 и натискам Enter, целият ред се маркира):

В този урок ще ви покажа как да създадете това търсене и да подчертаете функционалността в Excel.

По -късно в урока ще преминем малко напред и ще видим как да го направим динамичен (така че да се откроява, докато пишете в полето за търсене).

Щракнете тук, за да изтеглите примерния файл и следвайте.

Търсене и маркиране на съвпадащи клетки

В тази секция. Ще ви покажа как да търсите и маркирате само съвпадащите клетки в набор от данни.

Нещо, както е показано по -долу:

Ето стъпките за търсене и маркиране на всички клетки, които имат съвпадащ текст:

1. Изберете набора от данни, към който искате да приложите условно форматиране (A4: F19 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. В падащите опции щракнете върху Ново правило.
   
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху опцията „Използвайте формула, за да определите кои клетки да форматирате“.
6. Въведете следната формула: = A4 = $ B $ 1
7. Кликнете върху бутона „Форматиране …“.
8. Посочете форматирането (за да маркирате клетки, които съответстват на търсената ключова дума).
9. Щракнете върху OK.

Сега въведете всичко в клетка B1 и натиснете enter. Той ще подчертае съвпадащите клетки в набора от данни, които съдържат ключовата дума в B1.

Как работи това?

Условното форматиране се прилага винаги, когато формулата, посочена в него, връща TRUE.

В горния пример проверяваме всяка клетка, използвайки формулата = A4 = $ B $ 1

Условното форматиране проверява всяка клетка и я проверява, съдържанието в клетката е същото като това в клетка В1. Ако е същото, формулата връща TRUE и клетката се маркира. Ако не е същото, формулата връща FALSE и нищо не се случва.

Щракнете тук, за да изтеглите примерния файл и следвайте.

Търсете и маркирайте редове със съответстващи данни

Ако искате да маркирате целия ред, а не само съвпадащите клетки, можете да направите това, като промените малко формулата.

По -долу е даден пример, при който целият ред се подчертава, ако видът на продукта съвпада с този в клетка В1.

Ето стъпките за търсене и маркиране на целия ред:

1. Изберете набора от данни, към който искате да приложите условно форматиране (A4: F19 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. В падащите опции щракнете върху Ново правило.
   
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху опцията „Използвайте формула, за да определите кои клетки да форматирате“.
6. Въведете следната формула: = $ B4 = $ B $ 1
7. Кликнете върху бутона „Форматиране …“.
8. Посочете форматирането (за да маркирате клетки, които съответстват на търсената ключова дума).
9. Щракнете върху OK.

Горните стъпки ще търсят посочения елемент в набора от данни и ако той намери съответстващия елемент, той ще маркира целия ред.

Обърнете внимание, че това ще провери само за колоната с елементи. Ако въведете име на представител по продажбите тук, това няма да работи. Ако искате тя да работи за името на търговския представител, трябва да промените формулата на = $ C4 = $ B $ 1

Забележка: Причината да подчертава целия ред, а не само съответстващата клетка е, че сме използвали знак $ преди препратката към колоната ($ B4). Сега, когато условното форматиране анализира клетките в един ред, то проверява дали стойността в колона B на този ред е равна на стойността в клетка B1. Така че дори когато анализира A4 или B4 или C4 и т.н., той проверява само стойността на B4 (тъй като сме заключили колона B с помощта на знака за долар).

Можете да прочетете повече за абсолютни, относителни и смесени препратки тук.

Редове за търсене и подчертаване (въз основа на частично съвпадение)

В някои случаи може да искате да маркирате редове въз основа на частично съвпадение.

Например, ако имате елементи като Бяла дъска, Зелена дъска и Сива дъска и искате да подчертаете всичко това въз основа на думата Board, тогава можете да направите това с помощта на функцията SEARCH.

Нещо, както е показано по -долу:

Ето стъпките за това:

1. Изберете набора от данни, към който искате да приложите условно форматиране (A4: F19 в този пример).
2. Щракнете върху раздела Начало.
3. В групата Стили щракнете върху Условно форматиране.
4. В падащите опции щракнете върху Ново правило.
   
5. В диалоговия прозорец „Ново правило за форматиране“ щракнете върху опцията „Използвайте формула, за да определите кои клетки да форматирате“.
6. Въведете следната формула: = И ($ B $ 1 ””, ISNUMBER (ТЪРСЕНЕ ($ B $ 1, $ B4)))
7. Кликнете върху бутона „Форматиране …“.
8. Посочете форматирането (за да маркирате клетки, които съответстват на търсената ключова дума).
9. Щракнете върху OK.

Как работи това?

• Функцията SEARCH търси низ/ключова дума за търсене във всички клетки подред. Той връща грешка, ако ключовата дума за търсене не е намерена, и връща число, ако намери съвпадение.
• Функцията ISNUMBER преобразува грешката във FALSE, а числовите стойности в TRUE.
• Функцията AND проверява за допълнително условие - че клетка C2 не трябва да е празна.

Така че сега, когато въвеждате ключова дума в клетка B1 и натискате Enter, тя подчертава всички редове, които имат клетките, които съдържат тази ключова дума.

Бонус съвет: Ако искате да направите регистъра за търсене чувствителен, използвайте функцията FIND вместо SEARCH.

Щракнете тук, за да изтеглите примерния файл и следвайте.

Функция за динамично търсене и подчертаване (Акценти при въвеждане)

Използвайки същите трикове за условно форматиране, описани по -горе, можете също да направите крачка напред и да го направите динамичен.

Например, можете да създадете лента за търсене, където съответстващите данни да бъдат подчертани, докато пишете в лентата за търсене.

Нещо, както е показано по -долу:

Това може да стане с помощта на ActiveX контроли и може да бъде добра функционалност за използване при създаване на отчети или табла за управление.

По -долу е видео, където показвам как да създам това:

Намерихте ли този урок за полезен? Кажете ми вашите мисли в секцията за коментари.

Може да харесате и следните уроци по Excel:

• Динамичен филтър на Excel - Извлича данни, докато пишете.
• Създайте падащ списък с предложение за търсене.
• Създаване на топлинна карта в Excel.
• Маркирайте редове въз основа на стойност на клетката в Excel.
• Маркирайте активния ред и колона в диапазон от данни в Excel.
• Как да маркирате празни клетки в Excel.