Създаването на поле за търсене в Excel подобрява функционалността на вашите електронни таблици, като улеснява филтрирането и бързия достъп до определени данни. Това ръководство обхваща няколко метода за внедряване на поле за търсене, обслужващи различни версии на Excel. Независимо дали сте начинаещ или напреднал потребител, тези стъпки ще ви помогнат да настроите динамично поле за търсене, като използвате функции като функцията FILTER, условно форматиране и различни формули.

• Лесно създайте поле за търсене с функция FILTER
  (налично в Excel 2019 и по-нови версии, Excel за Microsoft 365)
• Създайте поле за търсене, като използвате Условно форматиране
  (налично във всички версии на Excel)
• Създайте поле за търсене с комбинации от формули
  (налично във всички версии на Excel)

Създайте лесно поле за търсене с функцията FILTER

• Тази функция автоматично актуализира изхода, когато вашите данни се променят.
• Функцията FILTER може да върне произволен брой резултати, от един ред до хиляди, в зависимост от това колко записа във вашия набор от данни отговарят на критериите, които сте задали.

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

Стъпка 1: Вмъкнете текстово поле и конфигурирайте свойствата

1. Отидете в Софтуерен Инженер кликнете върху Поставете > Т.ext Box (ActiveX Control).
   тип: Ако Софтуерен Инженер не се показва на лентата, можете да го активирате, като следвате инструкциите в този урок: Как да покажа/покажа раздела за програмисти в лентата на Excel?
   
2. Курсорът ще се превърне в кръст и след това трябва да плъзнете курсора, за да нарисувате текстовото поле на мястото в работния лист, където искате да поставите текстовото поле. След като нарисувате текстовото поле, отпуснете мишката.
   
3. Щракнете с десния бутон върху текстовото поле и изберете Имоти от контекстното меню.
   
4. в Имоти панел, свържете текстовото поле с клетка, като въведете препратката към клетката в Свързана клетка поле. Например, като напишете "J2“ гарантира, че всички данни, въведени в текстовото поле, се актуализират автоматично в клетка J2 и обратно.
   
5. Щракнете върху Режим на проектиране под Софтуерен Инженер за да излезете от режима на проектиране.
   

Текстовото поле вече ви позволява да въвеждате текст.

Стъпка 2: Приложете функцията FILTER

1. Преди да използвате функцията FILTER, копирайте оригиналния заглавен ред в нова област. Тук поставям заглавния ред под полето за търсене.
   тип: Този подход позволява на потребителите ясно да виждат резултатите под същите заглавия на колони като оригиналните данни.
   
2. Изберете клетката под първата заглавка (напр I5 в този пример), въведете следната формула в него и натиснете Въведете ключ за получаване на резултата.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Както е показано на екранната снимка по-горе, тъй като текстовото поле вече няма въвеждане, формулата показва резултата "Няма намерени данни"В I5.

Резултат: Тествайте полето за търсене

Нека сега тестваме полето за търсене. В този пример, когато въведа име на клиент в полето за търсене, съответните резултати ще бъдат филтрирани и показани веднага.

Създайте поле за търсене с помощта на условно форматиране

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

Стъпка 1: Вмъкнете текстово поле и конфигурирайте свойствата

1. Отидете в Софтуерен Инженер кликнете върху Поставете > Т.ext Box (ActiveX Control).
   тип: Ако Софтуерен Инженер не се показва на лентата, можете да го активирате, като следвате инструкциите в този урок: Как да покажа/покажа раздела за програмисти в лентата на Excel?
   
2. Курсорът ще се превърне в кръст и след това трябва да плъзнете курсора, за да нарисувате текстовото поле на мястото в работния лист, където искате да поставите текстовото поле. След като нарисувате текстовото поле, отпуснете мишката.
   
3. Щракнете с десния бутон върху текстовото поле и изберете Имоти от контекстното меню.
   
4. в Имоти панел, свържете текстовото поле с клетка, като въведете препратката към клетката в Свързана клетка поле. Например, като напишете "J3“ гарантира, че всички данни, въведени в текстовото поле, се актуализират автоматично в клетка J3 и обратно.
   
5. Щракнете върху Режим на проектиране под Софтуерен Инженер за да излезете от режима на проектиране.
   

Текстовото поле вече ви позволява да въвеждате текст.

Стъпка 2: Приложете условното форматиране за търсене на данни

1. Изберете целия диапазон от данни за търсене. Тук избирам диапазона A3:G279.
2. Под Начало кликнете върху Условно форматиране > Ново правило.
   
3. в Ново правило за форматиране диалогов прозорец:
   1. Изберете Използвайте формула, за да определите кои клетки да форматирате в Изберете тип правило опции.
   2. Въведете следната формула в Форматиране на стойности, където тази формула е вярна кутия.

      =$B3=$J$3

      Тук 3 долара представлява първата клетка в колоната, която искате да съпоставите с критериите за търсене в избрания диапазон, и $J$3 е клетката, свързана с полето за търсене.
   3. Щракнете върху формат бутон, за да зададете цвят за запълване на резултатите от търсенето.
   4. Щракнете върху OK бутон. Вижте екранна снимка:
      

Резултат

Нека сега тестваме полето за търсене. В този пример, когато въведа име на клиент в полето за търсене, съответните редове, които съдържат този клиент в колона B, ще бъдат незабавно маркирани с указания цвят за запълване.

Създайте поле за търсене с комбинации от формули

Ако не използвате най-новата версия на Excel и предпочитате да не маркирате само редове, методът, описан в този раздел, може да бъде полезен. Можете да използвате комбинация от формули на Excel, за да създадете функционално поле за търсене във всяка версия на Excel. Моля, следвайте стъпките по-долу.

Стъпка 1: Създайте списък с уникални стойности от колоната за търсене

1. В този случай избирам и копирам диапазона B4: B281 към нов работен лист.
2. След като поставите диапазона в нов работен лист, оставете поставените данни избрани, отидете на Дата И изберете Премахване на дубликати.
   
3. В откриването Премахване на дубликати кликнете върху OK бутон.
   
4. A Microsoft Excel след това изскача полето за подкана, за да покаже колко дубликати са премахнати. Кликнете OK.
   
5. След като премахнете дубликатите, изберете всички уникални стойности в списъка, с изключение на заглавката, и задайте име на този диапазон, като го въведете в Име кутия. Тук кръстих диапазона като Клиентски.
   

Стъпка 2: Вмъкнете разгъващ се списък и конфигурирайте свойствата

1. Върнете се към работния лист, съдържащ набора от данни, който искате да търсите. Отидете на Софтуерен Инженер кликнете върху Поставете > Комбо поле (ActiveX Control).
   тип: Ако Софтуерен Инженер не се показва на лентата, можете да го активирате, като следвате инструкциите в този урок: Как да покажа/покажа раздела за програмисти в лентата на Excel?
   
2. Курсорът ще се превърне в кръст и след това трябва да плъзнете курсора, за да начертаете комбинираното поле на мястото в работния лист, където искате да поставите полето за търсене. След като нарисувате комбинираното поле, отпуснете мишката.
   
3. Щракнете с десния бутон върху разгъващото се поле и изберете Имоти от контекстното меню.
   
4. в Имоти панел:
   1. Свържете комбинираното поле с клетка, като въведете препратката към клетката в Свързана клетка поле. я пиша "M2".
      Съвет: Посочването на това поле гарантира, че всички данни, въведени в комбинираното поле, ще се актуализират автоматично в клетка M2 и обратно.
   2. в ListFillRange поле, въведете име на диапазон сте посочили за уникалния списък в стъпка 1.
   3. Променете MatchEntry поле към 2 – fmMatchEntryNone.
   4. Затвори Имоти панел.
      
5. Щракнете върху Режим на проектиране под Софтуерен Инженер за да излезете от режима на проектиране.
   

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

Стъпка 3: Приложете формули

1. Създайте три помощни колони в съседство с оригиналния диапазон от данни. Вижте екранна снимка:
   
2. В клетката (H5) под заглавието на първата помощна колона въведете следната формула и натиснете Въведете.

   =ROWS($B$5:B5)

   Тук B5 е клетката, съдържаща името на първия клиент в колоната, в която ще се търси.
   
3. Щракнете два пъти върху долния десен ъгъл на клетката с формула, следващата клетка автоматично ще попълни същата формула.
   
4. В клетката (I5) под заглавката на втората помощна колона въведете следната формула и натиснете Въведете. След това щракнете двукратно върху долния десен ъгъл на клетката с формула, за да попълните автоматично клетките по-долу със същата формула.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Тук M2 е клетката, свързана с разгъващия се списък.
   
5. В клетката (J5) под заглавката на третата помощна колона въведете следната формула и натиснете Въведете. След това щракнете двукратно върху долния десен ъгъл на клетката с формула, за да попълните автоматично клетките по-долу със същата формула.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Копирайте оригиналния заглавен ред в нова област. Тук поставям заглавния ред под полето за търсене.
   
7. Изберете клетката под първата заглавка (напр L5 в този пример), въведете следната формула в него и натиснете клавиша Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Тук A5: G281 е целият диапазон от данни, който искате да се покаже в клетката с резултати.
   
8. Изберете тази клетка с формула, плъзнете Дръжка за пълнене надясно и след това надолу, за да приложите формулата към съответните колони и редове.
   
   бележки:

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

Резултат

Нека сега тестваме полето за търсене. В този пример, когато въведа или избера име на клиент от падащото поле, съответните редове, които съдържат името на клиента в колона B, ще бъдат филтрирани и незабавно показани в диапазона на резултатите.

Създаването на поле за търсене в Excel може значително да подобри начина, по който взаимодействате с вашите данни, като направи вашите електронни таблици по-динамични и удобни за потребителя. Независимо дали избирате простотата на функцията FILTER, визуалната помощ на условното форматиране или гъвкавостта на комбинациите от формули, всеки метод предоставя ценни инструменти за подобряване на възможностите ви за манипулиране на данни. Експериментирайте с тези техники, за да намерите коя работи най-добре за вашите специфични нужди и сценарии с данни. За онези, които желаят да навлязат по-дълбоко във възможностите на Excel, нашият уебсайт може да се похвали с изобилие от уроци. Открийте още съвети и трикове за Excel тук.

Свързани статии