Brug af avanceret filter i Excel til at løse Morts problem. Selvom almindelige filtre er blevet mere kraftfulde, er der stadig tidspunkter, at det avancerede filter kan udføre nogle tricks, som andre ikke kan.
Se video
- Det avancerede filter er mere "avanceret" end almindeligt filter, fordi:
- 1) Det kan kopiere til et nyt interval
- 2) Du kan opbygge mere komplekse kriterier såsom felt 1 = A eller felt 2 = A
- 3) Det er hurtigt
- Mort forsøger at behandle 100K rækker i VBA ved at løbe gennem poster eller bruge en matrix
- Det vil altid være hurtigere at bruge indbyggede Excel-funktioner end at skrive din egen kode.
- Du har brug for et inputområde og derefter et kriterieområde og / eller et outputområde
- For inputområdet: enkelt række overskrifter over dataene
- Tilføj en midlertidig række til overskrifter
- For outputområdet: en række overskrifter til de kolonner, du vil udtrække
- For kriterieområdet: overskrifter i række 1, værdier, der starter i række 2
- Komplikation: Ældre versioner af Excel tillader ikke, at outputområdet er på et andet ark
- Hvis du skriver en makro, der muligvis køres i 2003, skal du bruge et navngivet område til inputområde for at omgå
Videoudskrift
Lær Excel fra Podcast, afsnit 2060: Excel Advanced Filter
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål sendt af Mort. Mort, han har 100.000 rækker data, og han er interesseret i kolonne A, B og D, hvor kolonne C matcher et bestemt år. Så han vil have en person til at gå ind i et år og derefter få kolonne A, B og D. Og Mort har noget VBA, hvor han bruger arrays til at gøre dette, og jeg sagde: ”Vent et øjeblik, du ved, det avancerede filter ville gøre dette en meget bedre. ” Okay, og nu bare for at gennemgå gik jeg tilbage, jeg kiggede tilbage gennem mine videoer. Jeg har ikke dækket avanceret filter i lang tid, så vi skal tale om dette.
Avanceret filter kræver et inputområde og derefter mindst et af disse: et kriterieområde eller et outputområde. Selvom vi i dag skal bruge begge disse. Okay, så inputområdet er dine data, og du skal have overskrifter over dataene. Så, Mort har ikke overskrifter, og så vil jeg midlertidigt indsætte en række heroppe og bare gøre som felt 1. Mort ved, hvad hans data er, og så han kunne placere rigtige overskrifter deroppe. Og vi bruger ikke noget, der kaldes - disse data i kolonner E til O, så jeg behøver ikke tilføje overskrifter der, okay? Så nu, A1 til D, bliver 100000 mit inputområde. Og så outputområdet og kriterieområdet - Nå, outputområdet er bare en liste over de overskrifter, du vil have. Så jeg vil sætte outputområdet her, og vi har ikke brug for felt 3, så jeg 'Jeg tager det bare ud til siden. Så nu, dette interval lige her, bliver A1 til C1 mit outputområde, der fortæller Excel, hvilke felter jeg vil have fra inputområdet. Og de kunne være i en anden rækkefølge, hvis du ville omarrangere tingene, som hvis jeg først vil have felt 4 og derefter felt 1 og derefter felt 2. Og igen vil det være rigtige overskrifter som fakturanummer. Jeg ved bare ikke, hvordan Morts data ser ud.
Og så er kriterieområdet en overskrift, og hvilken værdi du vil have. Så lad os sige, at jeg forsøgte at få noget i år 2014. Dette bliver kriterieområdet sådan. Okay, bare et ord med forsigtighed her. Jeg er i Excel 2016, og det er muligt at lave et avanceret filter mellem to ark i Excel 2016, men hvis du går langt tilbage, og jeg ikke kan huske, hvad vejen tilbage er, måske 2003, er jeg ikke sikker. På et tidspunkt i fortiden plejede det at være, at du ikke kunne lave et avanceret filter fra et ark til et andet ark, så du bliver nødt til at komme her og navngive dit inputområde. Du bliver nødt til at oprette et navn her. MyName eller noget lignende, okay? Og det ville være den måde, du ville være i stand til at trække dette af, okay. Ikke nødvendigvis i Excel 2016 men igen, jeg 'Jeg er ikke sikker på, om Mort vil køre dette i ældre versioner af dataene.
Okay, så tilbage her på Data går vi til Advanced Filter, okay. Og vi skal kopiere til et andet sted, der muliggør vores outputområde der. Okay, så listeområdet, hvor er dataene? Fordi jeg er i Excel 2016, vil jeg pege på dataene i stedet for at bruge navneområdet - så det er mit inputområde. Kriterierne er disse celler lige der og derefter, hvor vi skal - output til, det vil bare være disse tre celler der. Og så klikker vi OK. Okay, og BAM! Sådan er det hurtigt, hurtigt. Og hvad hvis vi ville have et andet år? Hvis vi ønskede et andet år, ville vi slette resultaterne, sætte i 2015 og derefter lave et avanceret filter igen, Kopier til en anden placering, klik på OK, og der er alle 2015-poster. Lynhurtigt.
Okay, mens jeg er fan af avanceret filter i almindelig Excel, var jeg en massiv fan af avanceret filter i VBA, okay, fordi VBA gør forhåndsfilter virkelig, virkelig, virkelig simpelt. Okay, så vi skal skrive en kode her for Mort, forudsat at Morts data ikke har nogen overskrifter, og vi bliver nødt til midlertidigt at tilføje overskrifterne, okay? Så jeg skifter til VBA, Alt + F11, og vi kører dette fra regnearket, der har dataene. Så: Dim WS som regneark, indstil WS = ActiveSheet. Indsæt derefter række 1 og tilføj bare nogle overskrifter: A, B, år og D. Find ud af, hvor mange rækker med data vi har i dag, og start derefter fra celle A1, gå ud 4 kolonner ned til den sidste række, navngiv det til være inputområde. Okay, og så er dette faktisk Morts kode lige her, hvor han bad om InputBox,får det år, de vil have, og så spørger han, hvilket år eller hvad de vil navngive det nye ark, okay. Så det vil faktisk indsætte et ark på Fly og derefter I-dimensionere et nyt ark, WSN, som ActiveSheet. Så jeg ved, at WS er det originale ark, WSN er det nye ark, der lige blev tilføjet. På det nye ark skal du sætte kriterierne, så under kolonne E er der den overskrift, der matcher denne overskrift her, og derefter, uanset hvilket svar de gav os, går i E2. Outputområdet vil være mine andre tre overskrifter: A, B og D. Og igen, hvis du eller Mort ændrer disse til ægte overskrifter, hvilket sandsynligvis er en bedre ting at gøre end A, B, D, og du ville også ændre disse til rigtige overskrifter, okay? Så alt dette er bare en lille smule forarbejde her. Denne enestående kode kode vil gøre hele det avancerede filter. Så,fra InputRange laver vi et AdvancedFilter, vi skal kopiere. Det er vores valgfilter på plads eller kopi. CriteriaRange er E1 til E2, CopyToRange er A til C. Unikke værdier -Nej, vi vil have alle værdierne. Okay, den ene linje kode der gør al den magi, der går gennem alle poster eller erstatter, der løber gennem alle poster eller udfører arrays. Og så er vi færdige, vi rydder kriterieområdet og sletter derefter række 1 tilbage på det originale regneark.Og så er vi færdige, vi rydder kriterieområdet og sletter derefter række 1 tilbage på det originale regneark.Og så er vi færdige, vi rydder kriterieområdet og sletter derefter række 1 tilbage på det originale regneark.
Okay, så lad os skifte tilbage her til vores data. Vi vil gøre det let at køre dette, så: Indsæt, en figur og kald dette filter, Hjem, Center, Center, Større, Større, Større, højreklik, Tildel makro, og tildel det til MacroForMort. Okay, så her går vi. Vi skal lave en test. Se, at vi er på databladet, klik på filteret, hvilket år vil vi have? Vi vil have 2015. Hvad vil jeg kalde det? Jeg vil gerne kalde det 2015, okay. Og BAM! Der er det gjort. Sådan er det hurtigt, det er så hurtigt det er.
Nu, da Morts originale data ikke havde overskrifter, skulle disse data måske ikke have overskrifter. Så lad os gå med Alt + F11, lige her vil vi rydde kriterieområdet. Vi vil også rækker (1). Slet. Okay, så nu næste gang vi var på dette, vil det slippe af med disse overskrifter. Og lad os bare - I stedet for at køre det hele hurtigt, lad os se her med 2014. Så jeg vælger en celle på Data, Alt + F11, og jeg vil løbe lige ned til det punkt, hvor vi gør avanceret filter. Så vi kan se og se, hvad hele makroen laver her. Så vi klikker på Kør, og jeg vil have 2014. 2014, okay. Og så tryk på F8, vi er ved at lave det avancerede filter. Vi kan rulle tilbage til Excel her og se, hvad der er sket.
Første ting, der er sket - Nu, første ting, der er sket, er, at vi har tilføjet en ny midlertidig række med overskrifterne. Indsatte dette regneark, byggede et kriterieområde med en overskrift, og hvilket år de indtastede, valgte de felter, vi vil lave, og derefter tilbage i VBA, jeg kører den næste linje med koder, det er F8, der gør det avancerede filter lige der . Det er utroligt hurtigt, og du vil se, at det faktisk nu har bragt os alle poster. Derfra er det bare lidt oprydning, slet dette, slet dette. Jeg går tilbage til dataene og sletter række 1, så er vi klar til at gå. Så jeg skal bare lade resten af det køre, fjerne det brudpunkt, okay? Så der er VBA. For mig er dette, jeg tror, den hurtigste vej, hurtigste vej at gå.
Okay, episodeoptagelse: Det avancerede filter er mere avanceret end det almindelige filter, fordi det kan kopiere til et nyt interval. Og nu viste jeg det ikke i denne video, men du kan opbygge komplekse kriterier, hvor felt 1 = A eller felt 2 = A. Det almindelige automatiske filter kan ikke gøre det, og det er hurtigt. Mort forsøger at behandle 100.000 rækker i VBA ved hjælp af et array eller ved looping, men det vil altid være hurtigere at bruge Excel-bygningsfunktioner end at skrive din egen kode. Du skal definere et inputområde, kriterieområde, outputområde. Du har altid brug for et inputområde i mindst en af disse, selvom jeg i dag bruger begge dele. For inputområdet, enkelt række overskrifter over dataene. Så vi tilføjer en midlertidig række overskrifter. For outputområdet er de samme overskrifter, som du vil udtrække, okay. Så ved du, hvis det var A, B,År og D, vi sætter bare A, B og D som outputområde. For kriterieområdet, overskrifter i række 1. Så dette er det felt, jeg vil bygge et kriterium på, og det er den værdi, jeg leder efter. Komplikationer: Ældre versioner af Excel tillader ikke, at outputområdet er på et andet ark, så din kode kører muligvis dengang. Du vil bruge et navngivet område til inputområdet, for fra dette ark kender du det navngivne område, selvom det er på et andet ark, mener arket, at navnet forgrener sig på det aktuelle ark. Så det ville give det avancerede filter mulighed for at arbejde.Ældre versioner af Excel tillader ikke, at outputområdet er på et andet ark, så din kode kører muligvis dengang. Du vil bruge et navngivet område til inputområdet, for fra dette ark kender du det navngivne område, selvom det er på et andet ark, mener arket, at navnet forgrener sig på det aktuelle ark. Så det ville give det avancerede filter mulighed for at arbejde.Ældre versioner af Excel tillader ikke, at outputområdet er på et andet ark, så din kode kører muligvis dengang. Du vil bruge et navngivet område til inputområdet, for fra dette ark kender du det navngivne område, selvom det er på et andet ark, mener arket, at navnet forgrener sig på det aktuelle ark. Så det ville give det avancerede filter mulighed for at arbejde.
Okay, ja, der har du det. Jeg vil takke Mort for at sende dette spørgsmål. Jeg vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2060.xlsm
