Lielākajai daļai Excel lietotāju, kad vārds “datu filtrēšana” ienāk prātā, no cilnes parādās tikai parastais klasiskais filtrs. Dati - filtrs (Dati — filtrs):
Šāds filtrs, bez šaubām, ir pazīstams, un vairumā gadījumu tas derēs. Tomēr ir situācijas, kad nepieciešams filtrēt pēc liela skaita sarežģītu nosacījumu vairākās kolonnās vienlaikus. Parastais filtrs šeit nav īpaši ērts, un es gribu kaut ko jaudīgāku. Šāds rīks varētu būt uzlabots filtrs, īpaši ar nelielu “apdare ar failu” (pēc tradīcijas).
Bāze
Lai sāktu, ievietojiet dažas tukšas rindiņas virs datu tabulas un kopējiet tur tabulas galveni — tas būs diapazons ar nosacījumiem (skaidrības labad iezīmēts dzeltenā krāsā):
Starp dzeltenajām šūnām un sākotnējo tabulu ir jābūt vismaz vienai tukšai rindiņai.
Dzeltenajās šūnās ir jāievada kritēriji (nosacījumi), saskaņā ar kuriem pēc tam tiks veikta filtrēšana. Piemēram, ja jums ir jāizvēlas banāni Maskavas “Auchan” III ceturksnī, tad apstākļi izskatīsies šādi:
Lai filtrētu, atlasiet jebkuru šūnu diapazonā ar avota datiem, atveriet cilni Datums Un noklikšķiniet uz Papildus (Dati — papildu). Atvērtajā logā jau automātiski jāievada diapazons ar datiem, un mums būs tikai jānorāda nosacījumu diapazons, ti, A1:I2:
Lūdzu, ņemiet vērā, ka nosacījumu diapazonu nevar piešķirt “ar rezervi”, ti, nevar atlasīt papildu tukšas dzeltenas līnijas, jo tukšu šūnu nosacījumu diapazonā Excel uztver kā kritērija neesamību, bet veselu tukšu. rindu kā pieprasījumu bez izšķirības parādīt visus datus.
Slēdzis Kopējiet rezultātu uz citu vietu ļaus filtrēt sarakstu nevis tieši šajā lapā (kā ar parasto filtru), bet gan izlādēt atlasītās rindas citā diapazonā, kas pēc tam būs jānorāda laukā Ievietojiet rezultātu diapazonā. Šajā gadījumā mēs neizmantojam šo funkciju, mēs atstājam Filtru saraksts ir vietā un noklikšķiniet OK. Atlasītās rindas tiks parādītas lapā:
Makro pievienošana
"Nu, kur šeit ir ērtības?" tu jautāsi un tev būs taisnība. Jums ne tikai ar rokām jāievada nosacījumi dzeltenajās šūnās, bet arī jāatver dialoglodziņš, jāievada tur diapazoni, jānospiež OK. Skumji, piekrītu! Bet “viss mainās, kad tie nāk ©” – makro!
Darbu ar uzlaboto filtru var ievērojami paātrināt un vienkāršot, izmantojot vienkāršu makro, kas automātiski palaidīs uzlaboto filtru, kad tiks ievadīti nosacījumi, ti, mainot jebkuru dzelteno šūnu. Ar peles labo pogu noklikšķiniet uz pašreizējās lapas cilnes un atlasiet komandu Avota teksts (Avota kods). Atvērtajā logā kopējiet un ielīmējiet šādu kodu:
Private Sub Worksheet_Change(ByVal Target As Range) Ja nekrustojas(Mērķis, Diapazons("A2:I5")) nav nekas, tad ir kļūda. Atsākt nākamo ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=RlaceFilterInria :=Diapazons("A1").PašreizējaisReģions Beigas Ja Beigas apakš Šī procedūra tiks izpildīta automātiski, kad tiks mainīta jebkura pašreizējās darblapas šūna. Ja mainītās šūnas adrese iekrīt dzeltenajā diapazonā (A2:I5), tad šis makro noņem visus filtrus (ja tādi ir) un atkārtoti piemēro paplašināto filtru avota datu tabulai sākot ar A7, proti, viss tiks filtrēts uzreiz, uzreiz pēc nākamā nosacījuma ievadīšanas:
Tātad viss ir daudz labāk, vai ne? 🙂
Sarežģītu vaicājumu ieviešana
Tagad, kad viss tiek filtrēts lidojuma laikā, mēs varam nedaudz iedziļināties niansēs un izjaukt sarežģītāku vaicājumu mehānismus uzlabotajā filtrā. Papildus precīzas atbilstības ievadīšanai varat izmantot dažādas aizstājējzīmes (* un ?) un matemātiskās nevienlīdzības zīmes dažādos apstākļos, lai īstenotu aptuvenu meklēšanu. Rakstura gadījumam nav nozīmes. Skaidrības labad visas iespējamās iespējas esmu apkopojis tabulā:
| Kritērijs | Rezultāts |
| gr* vai gr | visas šūnas, sākot ar GrIe Grauss, Grapfrūts, Granat un tā joprojām |
| = sīpols | visas šūnas precīzi un tikai ar vārdu Loks, ti, precīza atbilstība |
| *liv* vai *liv | šūnas, kas satur Liv kā pasvītrot, ti ОLivKa, Livep, AtbilstošiLiv un tā joprojām |
| =p*v | vārdi, kas sākas ar П un beidzas ar В ie Пpirmaisв, Пēterisв un tā joprojām |
| a*s | vārdi, kas sākas ar А un tālāk satur СIe Аpelсin, Аnanaс, Asai un tā joprojām |
| =*s | vārdi, kas beidzas ar С |
| =???? | visas šūnas ar tekstu, kas sastāv no 4 rakstzīmēm (burti vai cipari, ieskaitot atstarpes) |
| =m??????n | visas šūnas ar tekstu, kas sastāv no 8 rakstzīmēm, sākot ar М un beidzas ar НIe Мandariн, Мnemiersн un tā joprojām |
| =*n??a | visi vārdi, kas beidzas ar А, kur ir 4. burts no gala НIe Starsнikа, Atbilstošiнozа un tā joprojām |
| >=e | visi vārdi sākas ar Э, Ю or Я |
| <>*o* | visi vārdi, kas nesatur burtu О |
| <>*vich | visi vārdi, izņemot tos, kas beidzas ar HIV (piemēram, filtrējiet sievietes pēc otrā vārda) |
| = | visas tukšas šūnas |
| <> | visas netukšas šūnas |
| > = 5000 | visas šūnas, kuru vērtība ir lielāka vai vienāda ar 5000 |
| 5 vai =5 | visas šūnas ar vērtību 5 |
| >=3/18/2013 | visas šūnas ar datumu pēc 18. gada 2013. marta (ieskaitot) |
Smalki punkti:
- Zīme * nozīmē jebkuru rakstzīmju skaitu, un ? - jebkurš varonis.
- Teksta un ciparu vaicājumu apstrādes loģika nedaudz atšķiras. Tātad, piemēram, nosacījuma šūna ar skaitli 5 nenozīmē meklēt visus skaitļus, kas sākas ar pieci, bet nosacījuma šūna ar burtu B ir vienāda ar B*, proti, meklēs jebkuru tekstu, kas sākas ar burtu B.
- Ja teksta vaicājums nesākas ar = zīmi, tad beigās var likt *.
- Datumi jāievada ASV formātā mēnesis-diena-gads un ar daļskaitli (pat ja jums ir Excel un reģionālie iestatījumi).
Loģiskie savienojumi UN-OR
Nosacījumi, kas rakstīti dažādās šūnās, bet vienā rindā, tiek uzskatīti par savstarpēji saistītiem ar loģisko operatoru И (UN):
Tie. filtru banāni man trešajā ceturksnī, precīzi Maskavā un tajā pašā laikā no Auchan.
Ja nepieciešams saistīt nosacījumus ar loģisko operatoru OR (OR), tad tie vienkārši jāievada dažādās rindās. Piemēram, ja mums ir jāatrod visi menedžera Volina pasūtījumi Maskavas persikiem un visi sīpolu pasūtījumi trešajā ceturksnī Samarā, tad to var norādīt dažādos apstākļos:
Ja vienai kolonnai ir jāuzliek divi vai vairāki nosacījumi, varat vienkārši dublēt kolonnas galveni kritēriju diapazonā un zem tās ievadīt otro, trešo utt. noteikumiem. Tātad, piemēram, varat atlasīt visus darījumus no marta līdz maijam:
Kopumā pēc “pabeigšanas ar failu” uzlabotais filtrs izrādās diezgan pieklājīgs rīks, dažviet ne sliktāks par klasisko automātisko filtru.
- Superfiltrs makro
- Kas ir makro, kur un kā ievietot makro kodu programmā Visual Basic
- Viedās tabulas programmā Microsoft Excel