Filtrul Excel este una dintre cele mai utilizate funcționalități atunci când lucrați cu date. În această postare pe blog, vă voi arăta cum să creați o casetă de căutare cu filtru dinamic Excel, astfel încât să filtreze datele pe baza a ceea ce tastați în caseta de căutare.
Ceva așa cum se arată mai jos:
Există o funcționalitate dublă - puteți selecta numele unei țări din lista derulantă sau puteți introduce manual datele în caseta de căutare și vă va afișa toate înregistrările potrivite. De exemplu, atunci când tastați „I” vă oferă toate numele țărilor cu alfabetul I în el.
Vizionați videoclipul - Crearea unei casete de căutare dinamică a filtrului Excel
Crearea unei casete de căutare a filtrului dinamic Excel
Acest filtru dinamic Excel poate fi creat în 3 pași:
- Obținerea unei liste unice de articole (țări în acest caz). Aceasta ar fi folosită la crearea listei derulante.
- Crearea casetei de căutare. Aici am folosit o casetă combinată (Control ActiveX).
- Setarea datelor. Aici aș folosi trei coloane de ajutor cu formule pentru a extrage datele potrivite.
Iată cum arată datele brute:
SFAT UTIL: Este aproape întotdeauna o idee bună să vă convertiți datele într-un tabel Excel. Puteți face acest lucru selectând orice celulă din setul de date și utilizând comanda rapidă de la tastatură Control + T.
Pasul 1 - Obținerea unei liste unice de articole
- Selectați toate țările și lipiți-le într-o nouă foaie de lucru.
- Selectați lista țărilor -> Accesați Date -> Eliminați duplicatele.
- În caseta de dialog Eliminare duplicate, selectați coloana în care aveți lista și faceți clic pe Ok. Acest lucru va elimina duplicatele și vă va oferi o listă unică, așa cum se arată mai jos:
- Un pas suplimentar este crearea unui interval numit pentru această listă unică. Pentru a face acest lucru:
- Accesați fila Formula -> Definiți numele
- În caseta de dialog Definire nume:
- Nume: CountryList
- Domeniu: Caiet de lucru
- Se referă la: = UniqueList! $ A $ 2: $ A $ 9 (Am lista într-o filă separată numită UniqueList în A2: A9. Puteți face referire la oriunde se află lista dvs. unică)
NOTĂ: Dacă utilizați metoda „Eliminați duplicatele” și vă extindeți datele pentru a adăuga mai multe înregistrări și țări noi, va trebui să repetați acest pas din nou. Alternativ, puteți, de asemenea, o formulă pentru a face acest proces dinamic.
Pasul 2 - Crearea casetei de căutare a filtrului dinamic Excel
Pentru ca această tehnică să funcționeze, ar trebui să creăm o „Casetă de căutare” și să o conectăm la o celulă.
Putem folosi caseta combinată în Excel pentru a crea acest filtru de căutare. În acest fel, ori de câte ori introduceți ceva în caseta combinată, acesta se va reflecta și într-o celulă în timp real (așa cum se arată mai jos).
Iată pașii pentru a face acest lucru:
- Accesați fila Dezvoltator -> Controale -> Inserare -> Controale ActiveX -> Casetă combinată (Controale ActiveX).
- Dacă nu aveți vizibilă fila Dezvoltator, iată pașii pentru activare.
- Dacă nu aveți vizibilă fila Dezvoltator, iată pașii pentru activare.
- Faceți clic oriunde pe foaia de lucru. Va introduce caseta combinată.
- Faceți clic dreapta pe caseta combinată și selectați Proprietăți.
- În fereastra Proprietăți, efectuați următoarele modificări:
- Celulă legată: K2 (puteți alege orice celulă unde doriți să afișeze valorile de intrare. Vom folosi această celulă în setarea datelor).
- ListFillRange: CountryList (acesta este intervalul numit pe care l-am creat la Pasul 1. Aceasta ar arăta toate țările din meniul derulant).
- MatchEntry: 2-fmMatchEntryNone (aceasta asigură faptul că un cuvânt nu este completat automat pe măsură ce tastați)
- Cu caseta combinată selectată, accesați fila Dezvoltator -> Comenzi -> Faceți clic pe Modul de proiectare (aceasta vă scoate din modul de proiectare, iar acum puteți introduce orice în caseta combinată. Acum, orice ați scrie ar fi reflectat în celula K2 in timp real)
Pasul 3 - Setarea datelor
În cele din urmă, conectăm totul prin coloane de ajutor. Folosesc trei coloane de ajutor aici pentru a filtra datele.
Coloana 1 de ajutor: Introduceți numărul de serie pentru toate înregistrările (20 în acest caz). Puteți utiliza formula ROWS () pentru a face acest lucru.
Coloana 2 de ajutor: În coloana auxiliară 2, verificăm dacă textul introdus în caseta de căutare se potrivește cu textul din celulele din coloana țării.
Acest lucru se poate face folosind o combinație de funcții IF, ISNUMBER și SEARCH.
Iată formula:
= IF (ISNUMBER (CAUTARE ($ K $ 2, D4)), E4, "")
Această formulă va căuta conținutul din caseta de căutare (care este legată de celula K2) din celula care are numele țării.
Dacă există o potrivire, această formulă returnează numărul rândului, altfel returnează un gol. De exemplu, dacă caseta combinată are valoarea „SUA”, toate înregistrările cu țara ca „SUA” ar avea numărul rândului, iar restul ar fi necompletat („”)
Coloana de ajutor 3: În coloana de asistență 3, trebuie să obținem toate numerele de rânduri din coloana de ajutor 2 stivuite împreună. Pentru a face acest lucru, putem folosi o combinație dacă IFERROR și formule MICI. Iată formula:
= IFERROR (MIC ($ F $ 4: $ F $ 23, E4), "")
Această formulă stivuiește toate numerele de rând potrivite împreună. De exemplu, dacă caseta combinată are valoarea SUA, toate numerele rândurilor cu „SUA” în ea se stivuiesc împreună.
Acum, când avem numerele de rând stivuite împreună, trebuie doar să extragem datele din acest număr de rând. Acest lucru se poate face cu ușurință folosind formula indexului (introduceți această formulă în locul în care doriți să extrageți datele. Copiați-o în celula din stânga sus unde doriți ca datele să fie extrase, apoi trageți-le în jos și în dreapta).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNE ($ I $ 3: I3)), "")
Această formulă are 2 părți:
INDEX - Aceasta extrage datele pe baza numărului de rând.
IFERROR - Acest lucru revine gol atunci când nu există date.
Iată un instantaneu cu ceea ce obțineți în cele din urmă:
Caseta combinată este o listă derulantă, precum și o casetă de căutare. Puteți ascunde datele originale și coloanele de ajutor pentru a afișa numai înregistrările filtrate. Puteți avea, de asemenea, datele brute și coloanele de ajutor în altă foaie și să creați acest filtru excel dinamic într-o altă foaie de lucru.
Fii creativ! Încercați câteva variante
Puteți încerca să îl personalizați în funcție de cerințele dvs. Poate doriți să creați mai multe filtre Excel în loc de unul. De exemplu, poate doriți să filtrați înregistrările în cazul în care reprezentantul de vânzări este Mike și Țara este Japonia. Acest lucru se poate face exact urmând aceiași pași, cu unele modificări în formula din coloanele de ajutor.
O altă variantă ar putea fi filtrarea datelor care încep cu caracterele pe care le introduceți în caseta combinată. De exemplu, atunci când introduceți „I”, poate doriți să extrageți țări începând cu I (în comparație cu construcția actuală, unde vă va oferi și Singapore și Filipine, deoarece conține alfabetul I).
Ca întotdeauna, majoritatea articolelor mele sunt inspirate de întrebările / răspunsurile cititorilor mei. Mi-ar plăcea să primesc feedback-ul dvs. și să învăț de la dvs. Lasă-ți gândurile în secțiunea de comentarii.
Notă: În cazul în care utilizați Office 365, puteți utiliza funcția FILTER pentru a filtra rapid datele pe măsură ce introduceți text. Este mai ușor decât metoda prezentată în acest tutorial.
