Es nesen rakstīju rakstu par to, kā izmantot Excel kopsavilkuma funkcijas, lai viegli apkopotu lielu datu apjomu, bet šajā rakstā tika ņemti vērā visi dati darblapā. Ko darīt, ja vēlaties aplūkot tikai datu apakšgrupu un apkopot datu apakškopu?
Programmā Excel varat izveidot filtru slejās, kas slēpj rindas, kas neatbilst jūsu filtram. Turklāt Excel var izmantot īpašas funkcijas, lai apkopotu datus, izmantojot tikai filtrētos datus.
Šajā rakstā es tev sekosšu filtru izveides soļus Excel un arī, izmantojot iebūvēto funkciju, apkopot šos filtrētos datus.
Programmā Excel varat izveidot vienkāršus filtrus un sarežģītus filtrus. Sāksim ar vienkāršiem filtriem. Strādājot ar filtriem, jums vienmēr ir jābūt vienai rindai augšpusē, ko izmanto etiķetēm. Nevar prasīt, lai šī rinda tiktu izmantota, bet tas nedaudz atvieglos darbu ar filtriem.
Virs man ir daži viltus dati, un es vēlos izveidot filtru Pilsēta sleja. Excel programmā tas ir patiešām viegli izdarāms. Iet uz priekšu un noklikšķiniet uz Dati lentē cilni un pēc tam noklikšķiniet uz Filtrēt poga Jums arī nav jāizvēlas dati lapā vai pirmajā rindā.
Kad jūs noklikšķiniet uz filtra, katras pirmās rindas kolonnā automātiski tiks izveidota neliela nolaižamā poga, kas pievienota labajā malā.
Tagad iet uz priekšu un noklikšķiniet uz nolaižamās bultiņas slejā Pilsēta. Jūs redzēsiet pāris dažādas opcijas, kuras es paskaidrošu tālāk.
Augšdaļā varat ātri kārtot visas rindas pēc kolonnas "Pilsētas" vērtībām. Ņemiet vērā, ka, sakārtojot datus, tā pārvietos visu rindu, nevis tikai kolonnas "Pilsēta" vērtības. Tas nodrošinās, ka jūsu dati paliek neskarti tāpat kā agrāk.
Tāpat ir ieteicams pievienot kolonnu ļoti priekšā ar nosaukumu ID un numurēt to no vienas uz daudzām rindām, kas jums ir jūsu darblapā. Tādā veidā jūs vienmēr varat sakārtot pēc slejas ID un saņemt savus datus tādā pašā secībā, kāds tas bija sākotnēji, ja tas jums ir svarīgi.
Kā redzat, visi izklājlapas dati tagad ir sakārtoti pēc kolonnas "Pilsētas" vērtībām. Līdz šim neviena rinda nav paslēpta. Tagad pievērsīsimies rūtiņām filtra dialoga apakšā. Manā piemērā man kolonā Man ir tikai trīs unikālas vērtības un tie tiek parādīti sarakstā.
Es devos uz priekšu un nekontrolēju divas pilsētas un atstāja vienu pārbaudi. Tagad man ir tikai 8 datu rindas, un pārējie ir paslēpti. Jūs varat viegli pateikt, ka meklējat filtrētos datus, ja pārbaudāt rindu numurus tālu kreisajā pusē. Atkarībā no tā, cik daudz rindu ir paslēptas, jūs redzēsiet dažas papildu horizontālās līnijas un ciparu krāsa būs zila.
Tagad pieņemsim, ka vēlos filtrēt otrajā slejā, lai vēl vairāk samazinātu rezultātu skaitu. C slejā man ir kopējais dalībnieku skaits katrā ģimenē, un es vēlos tikai redzēt rezultātus ģimenēm, kurās ir vairāk nekā divi dalībnieki.
Ejiet uz priekšu un noklikšķiniet uz nolaižamās bultiņas C slejā un kolonnā redzēsiet tās pašas izvēles rūtiņas katrai unikālajai vērtībai. Tomēr šajā gadījumā mēs vēlamies uzklikšķināt uz Numuru filtri un pēc tam noklikšķiniet uz Lielāks nekā. Kā jūs varat redzēt, ir arī vairākas citas iespējas.
Tiks atvērts jauns dialogs, kurā jūs varat ievadīt filtra vērtību. Varat arī pievienot vairāk kā vienu kritēriju ar AND un OR funkciju. Jūs varētu teikt, ka vēlaties rindas, kuru vērtība ir lielāka par 2 un nav vienāda ar 5, piemēram.
Tagad es esmu uz leju, lai iegūtu tikai 5 datu rindas: tikai no New Orleans ģimenes un ar 3 vai vairāk dalībniekiem. Pietiekami viegli? Ņemiet vērā, ka slejā varat viegli notīrīt filtru, noklikšķinot uz nolaižamās izvēlnes un pēc tam noklikšķinot uz Notīrīt filtru no "kolonnas nosaukuma" saite
Tā tas ir par vienkāršiem filtriem programmā Excel. Tās ir ļoti viegli izmantot, un rezultāti ir diezgan taisni uz priekšu. Tagad pieņemsim apskatīt sarežģītos filtrus, izmantojot Progresīvs filtru dialogs.
Ja vēlaties izveidot vairāk uzlabotus filtrus, jums ir jāizmanto Progresīvs filtra dialogs. Piemēram, pieņemsim, ka es gribēju redzēt visas ģimenes, kas dzīvo Ņūorleānā, un viņu ģimenē ir vairāk nekā divi locekļi VAI visas ģimenes Clarksville ar vairāk nekā 3 locekļiem savā ģimenē UN tikai tie, kuriem ir a .EDU beidzot e-pasta adresi. Tagad jūs to nevarat izdarīt ar vienkāršu filtru.
Lai to izdarītu, mums ir jākonstatē Excel lapa mazliet savādāk. Iet uz priekšu un ievietojiet pāris rindas virs jūsu datu kopas un kopējiet virsraksta etiķetes tieši pirmajā rindā, kā parādīts zemāk.
Tagad šeit ir, kā darbojas uzlaboti filtri. Vispirms ierakstiet kritērijus augšdaļā esošajās slejās un pēc tam noklikšķiniet uz Progresīvs poga zem Kārtot un filtrēt uz Dati tab.
Tātad, ko tieši mēs varam ierakstīt šajās šūnās? Labi, tad sāksim ar mūsu piemēru. Mēs tikai vēlamies redzēt datus no Ņūorleānas vai Klarksvillas, tāpēc ievadiet tos šūnās E2 un E3.
Ievadot vērtības dažādās rindās, tas nozīmē OR. Tagad mēs vēlamies, lai New Orleans ģimenes ar vairāk nekā diviem locekļiem un Clarksville ģimenēm ar vairāk nekā 3 locekļiem. Lai to izdarītu, ierakstiet >2 C2 un >3 C3.
Tā kā> 2 un New Orleans atrodas tajā pašā rindā, tas būs AND operators. Tas pats attiecas uz 3. rindu iepriekš. Visbeidzot, mēs vēlamies tikai ģimenes ar .EDU beigu e-pasta adresi. Lai to izdarītu, vienkārši ierakstiet * .edu gan D2, gan D3. * Simbols nozīmē jebkuru rakstzīmju skaitu.
Kad tas ir izdarīts, noklikšķiniet jebkur jūsu datu kopas vietā un pēc tam noklikšķiniet uz Progresīvs poga The Saraksta rangsE lauks automātiski noskaidro jūsu datu kopu, jo tajā noklikšķinājāt, pirms noklikšķinājāt uz pogas Papildu.Tagad noklikšķiniet uz mazās pogas labajā pusē Kritēriju diapazons poga
Atlasiet visu, sākot no A1 līdz E3, un pēc tam vēlreiz noklikšķiniet uz vienas un tās pašas pogas, lai atgrieztos dialoglodziņā Uzlabota filtrs. Noklikšķiniet uz Labi un jūsu dati tagad ir jāfiltrē!
Kā redzat, tagad man ir tikai 3 rezultāti, kas atbilst visiem šiem kritērijiem. Ņemiet vērā, ka kritēriju diapazona etiķetēm precīzi jāatbilst datu kopas etiķetēm, lai tas darbotos.
Ar šo metodi, protams, var radīt daudz sarežģītākus vaicājumus, tāpēc spēlē ar to, lai iegūtu vēlamos rezultātus. Visbeidzot, sāksim runāt par summēšanas funkciju piemērošanu filtrētiem datiem.
Tagad pieņemsim, ka es vēlos apkopot ģimenes locekļu skaitu manos filtrētos datos, kā es to darīšu? Nu, izdzēsīsim filtru, noklikšķinot uz Skaidrs pogas lente. Neuztraucieties, ir viegli no jauna piemērot uzlaboto filtru, vienkārši noklikšķinot uz pogas Papildu un atkal noklikšķinot uz Labi.
Datu kopas apakšā, pievienosim saucamo šūnu Kopā un pēc tam pievienojiet summas funkciju, lai apkopotu kopējos ģimenes locekļus. Manā piemērā es tikko drukāju = SUM (C7: C31).
Tātad, ja skatos uz visām ģimenēm, man ir 78 dalībnieki. Tagad ejam uz priekšu un atkārtoti izmantojiet mūsu uzlaboto filtru un skatiet, kas notiek.
Hops! Tā vietā, lai parādītu pareizo numuru, 11, es joprojām redzu kopējais ir 78! Kāpēc ir tā, ka? Nu, SUM funkcija neņem vērā slēptās rindas, tāpēc tā joprojām veic aprēķinu, izmantojot visas rindas. Par laimi ir vairākas funkcijas, kuras varat izmantot, lai ignorētu slēptās rindas.
Pirmais ir SUBTOTĀLS. Pirms mēs izmantojam kādu no šīm īpašajām funkcijām, jūs vēlaties iztīrīt filtru un pēc tam ievadīt funkciju.
Kad filtrs ir notīrīts, iet uz priekšu un ierakstiet = SUBTOTAL ( un jums vajadzētu redzēt nolaižamo lodziņu, parādās ar virkni iespēju. Izmantojot šo funkciju, vispirms izvēlieties summas funkcijas veidu, kuru vēlaties izmantot, izmantojot skaitli.
Mūsu piemērā es vēlos izmantot SUM, tāpēc es ievadīju skaitli 9 vai vienkārši noklikšķiniet uz tā no nolaižamās izvēlnes. Pēc tam ierakstiet komatu un atlasiet šūnu diapazonu.
Nospiežot enter, jums vajadzētu redzēt vērtību 78 ir tāda pati kā iepriekš. Tomēr, ja jūs tagad atkal izmantojat filtru, mēs redzēsim 11!
Excellent! Tas ir tieši tas, ko mēs gribam. Tagad jūs varat pielāgot savus filtrus, un vērtība vienmēr atspoguļos tikai pašlaik rādītās rindas.
Otra funkcija, kas darbojas diezgan daudz tieši tāpat kā SUBTOTAL funkcija ir AGGREGATE. Vienīgā atšķirība ir tāda, ka AGGREGATE funkcijā ir vēl viens parametrs, kurā jums ir jānorāda, ka vēlaties ignorēt slēptās rindas.
Pirmais parametrs ir summēšanas funkcija, kuru vēlaties izmantot, un kā ar SUBTOTAL, 9 norāda SUM funkciju. Otra iespēja ir vieta, kur jums jāievada 5, lai ignorētu slēptās rindas. Pēdējais parametrs ir vienāds un tas ir šūnu diapazons.
Varat arī izlasīt manu rakstu par kopsavilkuma funkcijām, lai uzzinātu, kā sīkāk izmantot funkciju AGGREGATE un citas funkcijas, piemēram, MODE, MEDIAN, AVERAGE utt.
Cerams, ka šis raksts dod jums labu sākumpunktu filtru izveidei un lietošanai programmā Excel. Ja jums ir kādi jautājumi, rakstiet komentāru. Izbaudi!
