Valikupäringu olemus on valida lähtetabelist teatud kriteeriumidele vastavad read (sarnaselt standardse kasutamisele). Valime lähtetabelist väärtused, kasutades . Erinevalt kasutamisest ( CTRL+SHIFT+L või Andmed/ Sorteeri ja filtreeri/ Filter) paigutatakse valitud read eraldi tabelisse.
Käesolevas artiklis vaatleme levinumaid päringuid, näiteks: tabeli ridade valimine, mille väärtus arvulisest veerust jääb etteantud vahemikku (intervalli); ridade valimine, mille kuupäev kuulub kindlasse perioodi; ülesanded 2 tekstikriteeriumiga ja muud. Alustame lihtsate päringutega.
1. Üks numbriline kriteerium (valige need Tooted, mille hind on kõrgem kui miinimum)
näidisfail, leht Üks kriteerium - arv ).
Eraldi tabelis on vaja kuvada ainult need kirjed (read) tabelist Allikas, mille hind on kõrgem kui 25.
Seda ja järgnevaid probleeme saate hõlpsalt lahendada, kasutades . Selleks valige tabeli Source päised ja klõpsake nuppu CTRL+SHIFT+L. Valige päise Hinnad kõrval olevast ripploendist Numbrifiltrid..., seejärel määrake vajalikud filtreerimistingimused ja klõpsake nuppu OK.
Kuvatakse kirjed, mis vastavad valikutingimustele.
Teine lähenemisviis on kasutada. Seevastu valitud read paigutatakse eraldi tabelisse - unikaalsesse tabelisse, mille saab näiteks vormindada Source-tabelist erinevas stiilis või teha muid muudatusi.
Lahtrisse paneme kriteeriumi (minimaalne hind). E6 , filtreeritud andmete tabel – vahemikus D10:E19 .
Nüüd valime vahemiku D11:D19 (Veerg Toode) ja sisestage:
INDEX(A11:A19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-RIDA (10 $ B $)
Selle asemel SISESTA vajutage kiirklahvi CTRL+SHIFT+ENTER(massiivi valem on ).
E11:E19 (veerg Hind), kuhu sisestame sarnase:
INDEX(B11:B19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-RIDA (10 $ B $)
Selle tulemusena saame uue tabeli, mis sisaldab ainult tooteid, mille hinnad ei ole madalamad kui lahtris märgitud E6 .
Saabunud näidistaotluse dünaamilisuse näitamiseks sisestame E6 väärtus 55. Uude tabelisse lisatakse ainult 2 kirjet.
Kui lisate Allika tabelisse uue toote hinnaga 80, siis lisatakse uude tabelisse automaatselt uus rekord.
Märkus. Saate kasutada ka ja filtreeritud andmete kuvamiseks. Konkreetse tööriista valik sõltub kasutaja ülesandest.
Kui teil ei ole mugav kasutada massiivi valem, mis tagastab mitu väärtust, siis võite kasutada teist lähenemisviisi, mida käsitletakse allolevates jaotistes: 5.a, 7, 10 ja 11. Nendel juhtudel .
2. Kaks numbrilist kriteeriumi (valige need Tooted, mille hind jääb vahemikku)
Olgu seal esialgne tabel toodete ja hindade loendiga (vt. näidisfail, lehtNumbrivahemik).
Paneme kriteeriumid (alumine ja ülemine hinnapiir) vahemikku E5:E6 .
Need. kui Toote hind jääb määratud intervalli sisse, siis ilmub selline rekord uude Filtreeritud andmete tabelisse.
Erinevalt eelmisest ülesandest loome kaks: Tooted ja Hinnad (saate ka ilma nendeta hakkama, aga valemite kirjutamisel on need mugavad). Vastavad valemid peaksid välja nägema nagu nimehalduris ( Valemid/ Määratletud nimed/ Nimehaldur) järgmiselt (vt joonist allpool).
Nüüd valime vahemiku D11:D19 ja me sisestame:
INDEX(Tooted;
LAST(
IF(($E$5<=Цены)*($E$6>=Hinnad);ROW(hinnad);"");
Selle asemel SISESTA vajutage kiirklahvi CTRL+SHIFT+ENTER.
Teeme samad manipulatsioonid vahemikuga E11:E19 kus tutvustame sarnast:
INDEX(Hinnad;
LAST(
IF(($E$5<=Цены)*($E$6>=Hinnad);ROW(hinnad);"");
RIDA(Hinnad)-RIDA($B$10))-RIDA($B$10))
Selle tulemusena saame uue tabeli, mis sisaldab ainult tooteid, mille hinnad jäävad lahtrites määratud intervalli E5 Ja E6 .
Saadud aruande (proovivõtutaotluse) dünaamilisuse näitamiseks sisestame E6 väärtus 65. Uude tabelisse lisatakse veel üks kirje allika tabelist, mis vastab uuele kriteeriumile.
Kui lisate Allika tabelisse uue toote, mille Hind jääb vahemikku 25 kuni 65, siis lisatakse uude tabelisse uus kirje.
Näidisfail sisaldab ka veakäsitlusega massiivi valemeid, kui veerus Hind sisaldab vea väärtust, näiteks #DIV/0! (vt lehte Vigade käsitlemine).
Järgmised probleemid lahendatakse sarnaselt, seega me neid nii üksikasjalikult ei käsitle.
3. Üks kriteeriumi kuupäev (valige need tooted, mille tarnekuupäev ühtib määratud kuupäevaga)
näidisfail, lehtÜks kriteerium – kuupäev).
Ridade valimiseks kasutatakse massiivi valemeid, mis on sarnased ülesandega 1 (kriteeriumi asemel<= используется =):
=INDEKS(A12:A20,VÄIKE(IF($E$6=B12:B20,RIDA(B12:B20),"");RIDA(B12:B20)-RIDA($B$11))-RIDA($B$11) )
INDEKS(B12:B20,VÄIKE(IF($E$6=B12:B20,RIDA(B12:B20),"");RIDA(B12:B20)-RIDA($B$11))-RIDA($B$11) )
4. Kaks kuupäeva kriteeriumi (valige need tooted, mille tarnekuupäev jääb vahemikku)
Olgu seal tabel Source koos toodete ja tarnekuupäevade loendiga (vt. näidisfail, lehtKuupäevavahemik).
Pange tähele, et veerg Kuupäev EI OLE SORTEERITUD.
Lahendus 1: Võite kasutada.
Sisestage lahtrisse D12 massiivi valem:
INDEKS(12 A$:20 A$;
SUURIM((6 $E$<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(RIDA($B$12:$B$20)-RIDA($B$11));
$J$12-RIDA(A12)+RIDA($B$11)+1))
Märkus: Pärast valemi sisestamist tuleb klahvi ENTER asemel vajutada klahvikombinatsiooni CTRL+SHIFT+ENTER. Seda kiirklahvi kasutatakse massiivivalemite sisestamiseks.
Kopeerige massiivi valem soovitud arvu lahtriteni. Valem tagastab ainult need väärtused toodete puhul, mis tarniti määratud kuupäevavahemikus. Ülejäänud lahtrid sisaldavad viga #NUM! Vead sisse näidisfail (leht 4. Kuupäevavahemik) .
Sarnane valem tuleb sisestada veergu E kuupäevade jaoks.
Rakus J12 Arvutatakse kriteeriumidele vastavate lähtetabeli ridade arv:
COUNTIFS(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)
Lähtetabelis olevad kriteeriumid vastavad read on .
Lahendus 2: ridade valimiseks võite kasutada 2. ülesandega sarnaseid massiivivalemeid (st):
=INDEX(A12:A20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RIDA(B12:B20);"");RIDA(B12:B20)-RIDA($B$11))-RIDA($B$11))
INDEX(B12:B20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RIDA(B12:B20);"");RIDA(B12:B20)-RIDA($B$11))-RIDA($B$11))
Esimese valemi sisestamiseks valige lahtrite vahemik G12:G20 . Pärast valemi sisestamist peate klahvi ENTER asemel vajutama klahvikombinatsiooni CTRL+SHIFT+ENTER.
Lahendus 3: kui veerg Kuupäev on SORTEERITUD, ei pea te massiivivalemeid kasutama.
Kõigepealt peate arvutama kriteeriumidele vastavate ridade esimese ja viimase positsiooni. Seejärel väljasta read.
See näide näitab taas selgelt, kui lihtne on valemeid kirjutada.
5. Ühe kriteeriumi kuupäev (valige need Tooted, mille tarnekuupäev ei ole määratud kuupäevast varasem/hiljem)
Olgu seal tabel Source koos toodete ja tarnekuupäevade loendiga (vt. näidisfail, leht Üks kriteerium – kuupäev (mitte hilisem) ).
Ridade valimiseks, mille kuupäev ei ole varasem (kaasa arvatud kuupäev ise), kasutatakse massiivi valemit:
=INDEX(A12:A20,SMALL(IF($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Näidisfail sisaldab ka valemeid tingimuste jaoks: Mitte varem (ei kaasa arvatud); Mitte hiljem (kaasa arvatud); Mitte hiljem (ei kaasa arvatud).
7. Üks tekstikriteerium (valige teatud tüüpi tooted)
Olgu seal esialgne tabel toodete ja hindade loendiga (vt. näidisfail, lehtÜks kriteerium – Tekst).
8. Kaks tekstikriteeriumi (valige teatud tüüpi tooted, mis tarnitakse antud kuul)
Olgu seal esialgne tabel toodete ja hindade loendiga (vt. näidisfail, leht 2 kriteeriumi – tekst (I) ).
INDEX($11:$19;
VÄIKE(IF(($F$6=$11:$A$19)*($F$7=$B$11:$B$19);RIDA($11:$A$19)-RIDA($10); 30);RIDA(INDIRECT("A1:A"&ROW($11:$A$19)))))
Väljendus ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) määrab mõlemad tingimused (toode ja kuu).
Väljendus ROW(INDIRECT("A1:A"&ROW($A$11:$A$19))) vormid (1:2:3:4:5:6:7:8:9), st. tabeli ridade numbrid.
9. Kaks tekstikriteeriumi (teatud tüüpi tooted)
Olgu seal esialgne tabel toodete ja hindade loendiga (vt. näidisfail, leht2 kriteeriumi – tekst (VÕI)).
Erinevalt ülesandest 7 valime 2 tüüpi kaupadega read ().
Ridade valimiseks kasutatakse massiivi valemit:
INDEX(11 A$:19 A$;
SUUR(((6 $6 = 11 $: 19 $)+(7 $ = 11 $: 19 A $))* (RIDA (11 $: 19 $) - RIDA (10 $) );
Seisund ($E$6=$A$11:$A$19)+($E$7=$11:$A$19) garanteerib, et kollastest lahtritest (Toode2 ja Toode3) valitakse ainult määratud tüüpi tooteid. Märkimiseks kasutatakse + (lisa) märki (vähemalt 1 kriteerium peab olema täidetud).
Ülaltoodud avaldis tagastab massiivi (0:0:0:0:1:1:1:0:0). Korrutades selle avaldisega RIDA (11 $: 19 $) - RIDA (10 $), st. kuni (1:2:3:4:5:6:7:8:9), saame positsioonide massiivi (tabeli ridade numbrid), mis vastavad kriteeriumidele. Meie puhul on see massiiv (0:0:0:0:5:6:7:0:0).
Näitena anname lahendused järgmisele probleemile: Valige Tooted, mille hind jääb teatud vahemikku ja mida korratakse määratud arv kordi või rohkem.
Võtame esialgseks kaubasaadetiste tabeli.
Oletame, et meid huvitab, kui palju ja milliseid kaubapartiisid tarniti hinnaga 1000 rubla. kuni 2000 rubla. (1. kriteerium). Lisaks peab olema vähemalt 3 sama hinnaga osa (kriteerium 2).
Lahendus on massiivivalem:
VÄIKE(RIDA(14 $: 27 $)*(14 $: 27 $>= 7 $)*(14 C $: 27 C $<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8–$G$9))
See valem tagastab reanumbrid, mis vastavad mõlemale kriteeriumile.
Valem =SUMPRODUCT(($C$14:$C$27>=$B$7)*($14:$C$27<=$C$7)*($D$14:$D$27>= $10)) loendab kriteeriumidele vastavate ridade arvu.
11. Kasutage kriteeriumi väärtust (kõik) või (kõik)
IN näidisfail lehel "11. Kriteerium ükskõik milline või (kõik)" See kriteeriumi versioon on rakendatud.
Sel juhul peab valem sisaldama funktsiooni IF(). Kui valitud on väärtus (Kõik), kasutatakse väärtuste kuvamiseks valemit ilma seda kriteeriumi arvesse võtmata. Kui on valitud mõni muu väärtus, töötab kriteerium nagu tavaliselt.
IF($C$8="(Kõik)";
VÄIKE((RIDA($B$13:$B$26)-RIDA($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
VÄIKE((RIDA($B$13:$B$26)-RIDA($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$8C$) ;F13+($G$6-$G$7)))
Ülejäänud valem on sarnane ülalpool käsitletuga.
Esimene meetod: täiustatud automaatfiltri kasutamine
Exceli lehel peate valima andmete hulgast ala, mida soovite valida. Klõpsake vahekaardil "Kodu" nuppu "Sortimine ja filtreerimine" (asub seadete plokis "Redigeerimine"). Järgmisena klõpsake filtril.
Saate seda teha teisiti: pärast piirkonna valimist minge vahekaardile "Andmed" ja klõpsake rühmas "Sortimine ja filtreerimine" asuvat nuppu "Filter".
Kui need toimingud on lõpetatud, peaksid tabeli päisesse ilmuma ikoonid, et alustada filtreerimist. Need kuvatakse lahtrite paremas servas väikeste kolmnurkadena. Klõpsake seda ikooni selle veeru alguses, mille jaoks soovite valiku teha. Käivitage menüü, minge jaotisse "Tekstifiltrid" ja valige "Kohandatud filter...".
Kohandatud filtreerimise aken peaks nüüd olema aktiveeritud. Selles määrate valiku tegemise piirangu. Saate valida ühe viiest pakutud tingimuse tüübist: võrdne, mitte võrdne, suurem kui, suurem või võrdne, väiksem kui.
Pärast filtreerimist jäävad alles need read, mille tulude summa ületab 10 000 (näiteks).
Samas veerus saate lisada teise tingimuse. Peate uuesti naasma kohandatud filtreerimisaknasse ja määrama selle alumises osas teistsuguse valikupiirangu. Seadke lüliti asendisse "Vähem" ja sisestage parempoolsele väljale "15000".
Tabelis on ainult need read, mille tulude summa ei ole väiksem kui 10 000, kuid mitte suurem kui 15 000.
Teistes veergudes on valik konfigureeritud sarnaselt. Klõpsake soovitud veerus filtriikooni ja seejärel järjestikku loendiüksustel "Filtreeri kuupäeva järgi" ja "Kohandatud filter".
Kohandatud automaatfiltri aken peaks avanema. Näiteks esitage tabelist tulemuste valik 4. maist kuni 6. maini 2016 (kaasa arvatud). Klõpsake "Pärast või võrdne" ja sisestage parempoolsele väljale väärtus "05/04/2016". Asetage alumises plokis lüliti asendisse "Enne või võrdne" ja parempoolsele väljale sisestage "05/06/2016". Jätke tingimuste ühilduvuse lüliti vaikeasendisse, st "JA". Filtreerimise rakendamiseks klõpsake nuppu OK.
Nimekirja tuleks nüüd veelgi vähendada, sest alles jäävad vaid read, mille tulude summa kõigub 10 000-15 000 ja seda perioodiks 04.05-05.06.2016 kaasa arvatud.
Ühes veerus saate soovi korral filtreerimise lähtestada. Näiteks saate seda teha tulude väärtuste jaoks. Klõpsake vastavas veerus automaatfiltri ikooni. Valige "Eemalda filter".
Tulu summa järgi valimine keelatakse ja jääb alles ainult kuupäevade järgi valimine (05/04/2016 kuni 05/06/2016).
Tabelis on veel üks veerg nimega "Nimi". See sisaldab andmeid tekstivormingus. Neid väärtusi kasutades saate moodustada ka valimi. Klõpsake veeru nimes filtriikooni. Minge jaotisse "Tekstifiltrid" ja seejärel "Kohandatud filter...".
Avaneb taas kohandatud filtri aken, milles saab teha valiku näiteks nimede “Liha” ja “Kartul” järgi. Esimeses plokis peate seadma lüliti asendisse "Võrdne" ja sisestama sellest paremal asuvale väljale "Kartul". Seadke alumise ploki lüliti asendisse "Võrdne" ja vastassuunas - "Liha". Nüüd peaksite seadma tingimuste ühilduvuse lüliti asendisse "OR". Klõpsake nuppu OK.
Uues proovis on piirangud seatud kuupäeva järgi (alates 05.04.2016 kuni 05.06.2016) ja nime järgi (Liha ja kartul). Piirangud puuduvad ainult tulu suurusele.
Saate filtri täielikult eemaldada ja seda tehakse samade meetoditega, mida kasutati selle seadistamiseks. Filtreerimise lähtestamiseks klõpsake vahekaardil "Andmed" jaotises "Sortimine ja filtreerimine" nuppu "Filter".
Teises valikus saate minna vahekaardile "Kodu" ja klõpsata jaotises "Redigeerimine" "Sortimine ja filtreerimine". Järgmisena klõpsake nuppu "Filter".
Kui kasutate mõnda neist meetoditest, siis tabel kustutatakse ja valiku tulemused kustutatakse. See tähendab, et tabelis kuvatakse kõik sinna eelnevalt sisestatud andmed.
Teine meetod: massiivivalemi kasutamine
Looge samal Exceli lehel tühi tabel, mille päises on samad veerunimed, mis on lähtes.
Uue tabeli esimeses veerus tuleb valida kõik tühjad lahtrid. Valemi sisestamiseks asetage kursor valemiribale - =INDEX(A2:A29,SMALL(IF(15000
Valemi rakendamiseks vajutage klahve Ctrl+Shift+Enter.
Valige teine veerg kuupäevadega ja asetage kursor valemiribale, et sisestada - =INDEX(B2:B29,SMALL(IF(15000
Samal viisil sisestage tulu veergu järgmine valem - =INDEX(C2:C29,SMALL(IF(15000
Avaneb vormindamisaken, kus peate valima vahekaardi "Number". Valige jaotises "Numbrivormingud" "Kuupäev". Akna paremas osas saate soovi korral valida kuvatava kuupäeva tüübi ja kui kõik seadistused on tehtud, klõpsake nuppu OK.
Nüüd on kõik ilus ja kuupäev kuvatakse õigesti. Kui lahtrites kuvatakse väärtus "#NUMBER!", peate rakendama tingimusvormingu. Valida tuleb kõik tabeli lahtrid (välja arvatud päis) ja vahekaardil “Kodu” klõpsata “Tingimuslik vormindamine” (tööriistaplokis “Stiilid”). Ilmub loend, milles peaksite valima "Loo reegel...".
Valige reeglid "Vorminda ainult sisaldavad lahtrid" ja esimesel väljal, mis asub rea "Vorminda ainult lahtrid, mille jaoks on täidetud järgmine tingimus" all, valige "Vead" ja klõpsake "Vorminda ...".
Avaneb vormindamise aken, kus minge jaotisse "Font" ja valige valge värv. Klõpsake nuppu OK.
Teie ette ilmub valmis näidis vastavalt määratud piirangule ja see kõik on eraldi tabelis.
Kolmas meetod: valimi võtmine mitme tingimuse alusel, kasutades valemit
Valimi piirtingimused tuleks sisestada eraldi veergu.
Valige ükshaaval uue tabeli tühjad veerud, et sisestada neisse kolm vajalikku valemit. Esimesse veergu sisestage - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 ) )-RIDA($C$1)). Järgmisena sisestage veergudesse samad valemid, muutke ainult koordinaadid pärast operaatori INDEX nimetust nendeks, mis on vajalikud ja vastavad teatud veergudele. Kõik on sarnane eelmisele meetodile. Iga kord, kui sisestate, ärge unustage vajutada klahvikombinatsiooni Ctrl+Shift+Enter.
Kui teil on vaja valimi piire muuta, saate lihtsalt tingimuste veerus piiride numbreid muuta ja seejärel muudetakse valikutulemust automaatselt.
Neljas meetod: juhuslik valim
Tabeli vasakus servas peate ühe veeru vahele jätma ja järgmise lahtrisse sisestage juhusliku arvu kuvamiseks valem - =RAND(). Selle aktiveerimiseks vajutage ENTER.
Kui teil on vaja teha terve veerg juhuslikest arvudest, asetage kursor valemit sisaldava lahtri alumisse paremasse nurka. Ilmuma peaks täitemarker, mida tuleks hiire vasakut nuppu all hoides alla lohistada. Seda tehakse andmetega tabeliga paralleelselt ja lõpuni.
Lahtrite vahemik sisaldab RAND-valemit, kuid te ei pea töötama puhaste väärtustega. Kopeerige paremal asuvasse tühja veergu ja valige juhuslike numbritega lahtrivahemik. Klõpsake vahekaardil "Kodu" nuppu "Kopeeri".
Valige tühi veerg ja paremklõpsake kontekstimenüü kuvamiseks. Tööriistarühmas "Sisestamisvalikud" valige "Väärtused" (kuvatakse numbritega ikoonina).
Vahekaardil "Kodu" klõpsake "Sortimine ja filtreerimine" ja seejärel "Kohandatud sortimine".
Märkige ruut valiku „Minu andmed sisaldavad päiseid” kõrval. Real "Sorteeri" märkige veeru nimi, milles kopeeritud juhuslike arvude väärtused asuvad. Real "Sortimine" jäävad sätted vaikeväärtustele. Valige real "Tellimus" valik "Kasvav" või "Kahanev". Klõpsake nuppu OK.
Tabeli väärtused tuleb paigutada juhuslike arvude kasvavas või kahanevas järjekorras. Tabelist võib võtta mis tahes arvu esimesi ridu ja lugeda juhusliku valimi tulemuseks.
Makro on toimingute jada, mis salvestatakse ja salvestatakse hilisemaks kasutamiseks. Salvestatud makrot saab taasesitada spetsiaalse käsu abil. Teisisõnu saate oma toimingud makrosse salvestada, salvestada ja seejärel lubada teistel kasutajatel makrosse salvestatud toiminguid lihtsa klahvivajutusega taasesitada. See on eriti kasulik PivotTable-liigendtabeli aruannete levitamisel.
Oletame, et soovite anda oma klientidele võimaluse rühmitada PivotTable-liigendtabeli aruandeid kuu, kvartali ja aasta järgi. Tehniliselt saab rühmitamist teha igaüks, kuid mõned teie kliendid ei tunne vajadust seda mõista. Sellisel juhul saate salvestada ühe rühmitamismakro kuu, teise kvartali ja kolmanda aasta järgi. Seejärel looge kolm nuppu – üks iga makro jaoks. Seejärel peavad teie kliendid, kes pole PivotTable-liigendtabelit uuesti kasutanud, klõpsama nuppu, et PivotTable-liigendtabeli aruanne õigesti rühmitada.
Peamine eelis makrode kasutamisest PivotTable-liigendtabeli aruannetes on võimaldada klientidel teha PivotTable-liigendtabelitega kiiresti toiminguid, mida nad tavaliselt ei saaks teha. Tänu sellele suureneb oluliselt esitatud andmete analüüsimise efektiivsus.
Laadige märkus alla või vormingus, laadige alla koos näidetega (sees on makrodega Exceli fail; teenusepakkuja poliitika ei luba selles vormingus faili otse saidile üles laadida).
Salvestage makro
Vaadake joonisel fig. 1. Saate seda pivot-tabelit värskendada, paremklõpsates selle sees ja valides Värskenda. Kui salvestasite PivotTable-liigendtabeli värskendamise ajal toimingud makrona, saate teie või keegi teine neid toiminguid uuesti esitada ja makro käitamise tulemusel PivotTable-liigendtabelit värskendada.
Riis. 1. Toimingute salvestamine selle liigendtabeli värskendamise ajal võimaldab teil tulevikus makro käitamise tulemusel andmeid värskendada
Makro salvestamise esimene samm on dialoogiboksi kutsumine Salvestage makro. Minge vahekaardile Arendaja lindile ja klõpsake nuppu Salvestage makro. (Kui te ei leia lindilt vahekaarti Arendaja, valige vahekaart Fail ja klõpsake nuppu Valikud. Ilmuvas dialoogiboksis Exceli valikud vali kategooria Kohandage lindi ja märkige parempoolses loendis ruut Arendaja. Selle tulemusena ilmub lindile vahekaart Arendaja.) Alternatiivne viis makro salvestamise alustamiseks on klõpsata nupul (joonis 2).
Dialoogiboksis Salvestage makro Sisestage järgmine makroteave (joonis 3):
Nimimakro. Nimi peaks kirjeldama makro tehtud toiminguid. Nimi peab algama tähe või alakriipsuga; ei tohi sisaldada tühikuid ega muid keelatud märke; ei tohi olla sama, mis Exceli sisseehitatud nimi või mõne muu töövihikus oleva objekti nimi.
Kombinatsioonvõtmed. Sellele väljale saate sisestada mis tahes tähe. Sellest saab osa kiirklahvist, mida kasutatakse makro esitamiseks. Klahvikombinatsiooni pole vaja määrata. Vaikimisi pakutakse kombinatsiooni alguseks ainult Ctrl. Kui soovite, et kombinatsioon sisaldaks ka Shift, tippige täht aknasse, hoides samal ajal all tõstuklahvi
SalvestaV. See on koht, kus makro salvestatakse. Kui kavatsete levitada PivotTable-liigendtabeli aruannet teistele kasutajatele, valige suvand Seeraamat. Excel võimaldab ka makro salvestada Uus raamat või sisse Isiklik makroraamat.
Kirjeldus. Sellele väljale sisestatakse loodava makro kirjeldus.
Riis. 3. Akende kohandamine Salvestage makro
Kuna makro värskendab pivot-tabelit, valige nimi Andmete värskendamine. Samuti saate makrole määrata kiirklahvi Ctrl+Shift+Q. Pidage meeles, et kui olete makro loonud, kasutate selle käivitamiseks seda kiirklahvi. Tehke makro salvestuskoha jaoks suvand See raamat ja klõpsake OK.
Pärast dialoogiboksis klõpsamist Salvestage makro nupul OK Makro salvestamine algab. Sel hetkel salvestatakse kõik, mida teete Excelis.
Paremklõpsake PivotTable-liigendtabeli alal ja valige Värskenda(nagu joonisel 1, kuid makrosalvestusrežiimis). Pärast pivot-tabeli värskendamist saate makro salvestamise protsessi peatada, kasutades nuppu Lõpetage salvestamine vahelehed Arendaja. Või klõpsake uuesti joonisel fig. 2.
Nii et olete just salvestanud oma esimese makro. Nüüd saate makro käivitada klahvikombinatsiooniga Ctrl+Shift+Q.
Makro turvahoiatus. Tuleb märkida, et kui kasutaja salvestab makrod, käivitatakse need ilma turbe alamsüsteemi piiranguteta. Kui aga levitate makrosid sisaldavat töövihikut, peate andma teistele kasutajatele võimaluse veenduda, et tööfailide avamisel pole ohtu ja makrode käitamine ei nakata süsteemi viirustega. Eelkõige märkate kohe, et selles peatükis kasutatud näidisfail ei tööta täielikult, kui te just ei luba Excelil selles makrosid käivitada.
Lihtsaim viis makroturvalisuse tagamiseks on luua usaldusväärne asukoht – kaust, kuhu paigutatakse ainult "usaldusväärsed" töövihikud, mis ei sisalda viirusi. Usaldusväärne asukoht võimaldab teil ja teie klientidel käivitada makrosid töövihikutes ilma turbepiiranguteta (see käitumine püsib seni, kuni töövihikud on usaldusväärses asukohas).
Usaldusväärse asukoha seadistamiseks toimige järgmiselt.
Valige lindi vahekaart Arendaja ja klõpsake nuppu Makro turvalisus. Ekraanile ilmub dialoogiboks Usalduskeskus.
Klõpsake nuppu Lisage uus asukoht.
Klõpsake nuppu Ülevaade et määrata tööfailide kaust, mida usaldate.
Kui olete usaldusväärse asukoha määranud, käitavad kõik selles asukohas olevad töövihikud vaikimisi suvalisi makrosid.
Excel 2013-s on turbemudelit täiustatud. Nüüd jäävad meelde töövihiku failid, mis olid varem “usaldusväärsed”, st. pärast Exceli töövihiku avamist ja nupu klõpsamist Kaasake sisu Excel jätab selle oleku meelde. Seetõttu kuulub see raamat usaldusväärsete hulka ja järgmisel avamisel tarbetuid küsimusi ei esitata.
Kasutajaliidese loomine vormi juhtelementide abil
Makro käivitamine klahvikombinatsiooniga Ctrl+Shift+Q aitab, kui PivotTable-liigendtabeli aruandes on ainult üks makro. (Samuti peavad kasutajad seda kombinatsiooni teadma.) Kuid oletame, et soovite pakkuda oma klientidele mitu makrot, mis täidavad erinevaid toiminguid. Sel juhul peate pakkuma klientidele selge ja lihtsa viisi iga makro käitamiseks, ilma et peaksite klahvikombinatsioone meeles pidama. Ideaalne lahendus on lihtne kasutajaliides koos juhtelementide komplektiga, nagu nupud, kerimisribad ja muud juhtelemendid, mis võimaldavad makrosid hiireklõpsuga käivitada.
Excel pakub teile tööriistakomplekti, mis on loodud aitama teil luua kasutajaliideseid otse arvutustabelis. Neid tööriistu nimetatakse vormi juhtelementideks. Põhiidee on see, et saate paigutada vormi juhtelemendi arvutustabelisse ja määrata sellele varem salvestatud makro. Kui makro on juhtelemendile määratud, käivitatakse see sellel elemendil klõpsates.
Vormi juhtelemendid leiate rühmast Vormi juhtelemendid lindi sakid Arendaja. Juhtpaleti avamiseks klõpsake selles rühmas olevat nuppu Sisesta(joonis 4).
Riis. 4. Vormikontroll Nupp
Pange tähele: lisaks vormi juhtelementidele sisaldab palett ka ActiveX-juhtelemendid. Kuigi nad on sarnased, on nad programmiliselt täiesti erinevad objektid. Vormi juhtelemendid oma piiratud võimaluste ja lihtsate seadistustega olid need spetsiaalselt loodud töölehtedele paigutamiseks. Samal ajal ActiveX-juhtelemendid kasutatakse peamiselt kohandatud vormides. Muutke oma töölehtedele harjumuseks paigutada ainult vormi juhtelemendid.
Peaksite valima juhtnupud, mis vastavad antud ülesandele kõige paremini. Selles näites peavad kliendid saama nupul klõpsates pivot-tabelit värskendada. Klõpsake juhtnupul Nupp, liigutage hiirekursor töölehel sellesse kohta, kuhu soovite nupu paigutada, ja klõpsake nuppu.
Kui olete nupu tabelisse asetanud, avaneb dialoogiboks Määra makro objektiks(joonis 5). Valige vajalik makro (meie puhul - Andmete värskendamine, salvestatud varem) ja klõpsake nuppu OK.
Riis. 5. Valige makro, mille soovite nupule määrata, ja klõpsake nuppu OK. Sel juhul peaksite kasutama makrot Andmete värskendamine
Kui olete PivotTable-liigendtabeli aruandesse paigutanud kõik vajalikud juhtelemendid, saate põhiliidese loomiseks tabeli vormindada. Joonisel fig. Joonis 6 näitab PivotTable-liigendtabeli aruannet pärast vormindamist.
Salvestatud makro redigeerimine
Kui salvestate makro, loob Excel mooduli, mis salvestab teie tehtud toimingud. Kõik salvestatud toimingud on esitatud VBA-koodi ridadega, mis moodustavad makro. Saate lisada oma PivotTable-liigendtabeli aruannetele mitmesuguseid funktsioone, kohandades oma VBA-koodi soovitud tulemuste saamiseks. Et oleks lihtsam aru saada, kuidas see kõik toimib, loome uue makro, mis kuvab esimesed viis kliendikirjet. Minge vahekaardile Arendaja ja klõpsake nuppu Salvestage makro. Avaneb joonisel näidatud dialoogiboks. 7. Andke loodavale makrole nimi EsiteksNkliendid ja märkige salvestuskoht See raamat. Klõpsake OK makro salvestamise alustamiseks.
Kui olete salvestamise alustanud, klõpsake välja kõrval olevat noolt Kliendi nimi, valige Filtreeri väärtuse järgi ja valik Esimesed 10(joonis 8a). Ilmuvas dialoogiboksis määrake sätted nagu näidatud joonisel fig. 8b. Need sätted näitavad viie parima müügimahuga kliendi andmete kuvamist. Klõpsake OK.
Riis. 8. Valige filter (a) ja kohandage valikuid (b), et kuvada müügi järgi viis parimat klienti
Pärast kõigi viie parima müügivihje väljavõtmiseks vajalike sammude edukat salvestamist avage vahekaart Arendaja ja klõpsake nuppu Lõpetage salvestamine.
Nüüd on teil makro, mis filtreerib pivot-tabeli, et eraldada viis parimat müügikontot. Vaja on panna makro reageerima kerimisriba olekule, st. Kerimisriba kasutades peate suutma makrole öelda klientide arvu, kelle andmeid pivot-tabeli aruandes kuvatakse. Seega saab kasutaja kerimisriba kasutades hankida oma äranägemise järgi viis, kaheksa või kolmkümmend kaks parimat klienti.
Arvutustabelile kerimisriba lisamiseks klõpsake Arendaja, klõpsake nuppu Sisesta, valige paletist juhtelement Kerimisriba ja asetage see oma töölehel. Paremklõpsake juhtnupul Kerimisriba Objekti vorming. Avaneb dialoogiboks Juhtimisvorming(joonis 9). Selles tehke seadetes järgmised muudatused: parameeter Minimaalne väärtus määrake parameetrile väärtus 1 Maksimaalne väärtus- väärtus 200 ja väljal Raku side Sisestage $M$2, et kuvada kerimisriba väärtus lahtris M2. Klõpsake nuppu OK eelnevalt määratud sätete rakendamiseks.
Nüüd peate sobitama hiljuti salvestatud makro EsiteksNkliendid koos juhtelemendiga Kerimisriba asub töölehel. Paremklõpsake juhtnupul Kerimisriba ja valige kontekstimenüüst käsk Määra makro makro määramise dialoogiboksi avamiseks. Määrake kerimisribale salvestatud makro FirstN kliente. Makro käivitatakse iga kord, kui klõpsate kerimisribal. Testige loodud kerimisriba. Ribal klõpsates käivitatakse makro FirstN kliente ja number lahtris M2 muutub, et näidata kerimisriba olekut. Lahtris M2 olev arv on oluline, kuna seda kasutatakse makro sidumiseks kerimisribaga.
Ainus asi, mida teha, on muuta makro protsess lahtris M2 olevaks numbriks, sidudes selle kerimisribaga. Selleks peate minema makro VBA koodi juurde. Selleks minge vahekaardile Arendaja ja klõpsake nuppu Makrod. Avaneb dialoogiboks Makro(joonis 10). Selles saate valitud makro käivitada, kustutada ja redigeerida. Ekraanil makro VBA-koodi kuvamiseks valige makro ja klõpsake nuppu Muuda.
Riis. 10. Juurdepääs makro VBA koodile EsiteksNkliendid, valige makro ja klõpsake nuppu Muuda
Ekraanile ilmub Visual Basicu redaktori aken VBA makrokoodiga (joonis 11). Teie eesmärk on asendada makro salvestamisel määratud kodeeritud number 5 väärtusega lahtris M2, mis on seotud kerimisribaga. Esialgu salvestati makro, et kuvada esimesed viis suurima sissetulekuga klienti.
Eemaldage koodist number 5 ja sisestage selle asemel järgmine avaldis:
ActiveSheet.Range(" M2 ").Väärtus
Filtrite tühjendamiseks lisage makro algusesse kaks rida.
Vahemik(" A4 "). Valige
ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Kliendi nimi ").ClearAllFilters
Nüüd peaks makrokood välja nägema selline, nagu on näidatud joonisel fig. 12.
Sulgege Visual Basicu redaktor ja naaske PivotTable-liigendtabeli aruande juurde. Testige kerimisriba, lohistades liugurit 11-ni. Makro peaks töötama ja filtreerima 11 parimat müügikontot.
Sünkroonige kaks pivot-tabelit ühe ripploendi abil
Joonisel fig. 13 sisaldab kahte kokkuvõtvat tabelit. Igal neist on leheväli, mis võimaldab valida müügituru. Probleem on selles, et iga kord, kui valite turu ühe liigendtabeli lehtede väljalt, peate valima sama turu teise pivot-tabeli lehtede väljalt. Filtrite sünkroonimine kahe tabeli vahel andmeanalüüsi etapis ei ole suur probleem, kuid on võimalus, et teie või teie kliendid unustavad selle teha.
Riis. 13. Kaks liigendtabelit sisaldavad lehevälju, mis filtreerivad andmeid turu järgi. Ühtse turu andmete analüüsimiseks peate sünkroonima mõlemad pivot-tabelid
Üks viis pivot-tabelite sünkroonis hoidmiseks on kasutada ripploendit. Idee on salvestada makro, mis valib väljalt soovitud turu Müügiturg mõlemas tabelis. Seejärel peate looma ripploendi ja täitma selle kahe pivot-tabeli turgude nimedega. Lõpuks tuleb salvestatud makrot muuta, et filtreerida mõlemad pivot-tabelid, kasutades ripploendi väärtusi. Selle probleemi lahendamiseks peate tegema järgmist.
1. Looge uus makro ja pange sellele nimi SynchMarkets. Kui salvestamine algab, valige väljal Müügiturg mõlemale müügituru koondtabelid California ja lõpetage makro salvestamine.
2. Kuvage palett Vormi juhtelemendid ja lisage töölehel rippmenüü.
3. Looge kõigist pivot-tabelis olevatest turgudest kõvakoodiga loend. Pange tähele, et loendi esimene element on väärtus (Kõik). Peaksite selle elemendi lubama, kui soovite ripploendist valida kõik turud.
4. Siinkohal peaks PivotTable-liigendtabeli aruanne välja nägema nagu joonisel näidatud. 14.
Riis. 14. Teie käsutuses on kõik vajalikud tööriistad: makro, mis muudab välja Müügiturg mõlemad pivot-tabelid, rippmenüü ja kõigi pivot-tabelis sisalduvate müügiturgude loend
5. Paremklõpsake ripploendil ja valige kontekstimenüüst käsk Objekti vorming juhtseadme konfigureerimiseks.
6. Esmalt määrake ripploendi täitmiseks kasutatud väärtuste esialgne vahemik, nagu on näidatud joonisel. 15. Sel juhul räägime müügiturgude loendist, mille lõite sammus 3. Seejärel märkige lahter, mis kuvab valitud elemendi seerianumbrit (antud näites on see lahter H1). Parameeter Loendi ridade arv määrab, mitu rida ripploendis korraga kuvatakse. Klõpsake nuppu OK.
Riis. 15. Rippmenüü seaded peaksid osutama müügiturgude loendile kui algväärtuste vahemikule ja määrama lahtri H1 kinnituspunktina
7. Nüüd on teil võimalus valida ripploendist müügiturg ja määrata ka sellega seotud seerianumber lahtris H1 (joonis 16). Tekib küsimus: miks kasutatakse selle indeksi väärtust turu tegeliku nimetuse asemel? Kuna ripploend ei tagasta nime, vaid numbri. Näiteks kui valite ripploendist California, kuvatakse lahtris H1 väärtus 5. See tähendab, et California on loendis viies üksus.
Riis. 16. Rippmenüü on nüüd täidetud turgude nimedega ja lahtris H1 kuvatakse valitud turu seerianumber
8. Turu nime asemel järjenumbri kasutamiseks peate selle edastama funktsiooni INDEX abil.
9. Sisestage funktsioon INDEX, mis teisendab lahtri H1 seerianumbri tähenduslikuks väärtuseks.
10. Funktsioonil INDEX on kaks argumenti. Esimene argument esindab loendi väärtuste vahemikku. Enamikul juhtudel kasutate sama vahemikku, mis täidab rippmenüü. Teine argument on järjekorranumber. Kui seerianumber on sisestatud lahtrisse (näiteks lahtrisse H1, nagu joonisel 17), saate lihtsalt sellele lahtrile viidata.
Riis. 17. Funktsioon INDEX lahtris I1 teisendab lahtrisse H1 salvestatud järjenumbri väärtuseks. Makro muutmiseks kasutate väärtust lahtris I1
11. Muutke makrot SynchMarkets, kasutades kõvakoodi väärtuse asemel lahtris I1 olevat väärtust. Minge vahekaardile Arendaja ja klõpsake nuppu Makrod. Ekraanile ilmub joonisel 1 näidatud dialoogiboks. 18. Valige selles makro SynchMarkets ja klõpsake nuppu Muuda.
Riis. 18. Makro VBA-koodile juurdepääsu saamiseks valige makro SynchMarkets ja klõpsake Muuda
12. Makro salvestamisel valisite mõlema pivot-tabeli väljalt müügituru California Müügiturg. Nagu näha jooniselt fig. 19, California turg on nüüd kõvakodeeritud VBA makrokoodis.
13. Asendage väärtus "California" avaldisega Activesheet.Range("I1").Value, mis viitab väärtusele lahtris I1. Selles etapis peaks makrokood välja nägema selline, nagu on näidatud joonisel fig. 20. Pärast makro muutmist sulgege Visual Basicu redaktor ja naaske arvutustabelisse.
Riis. 20. Asendage väärtus "California" väärtusega ActiveSheet.Range("I1").Value ja sulgege Visual Basicu redaktor
14. Jääb üle vaid tagada, et ripploendist müügituru valimisel makro täidetakse. Paremklõpsake rippmenüül ja valige suvand Määra makro. Valige makro SynchMarket ja klõpsake nuppu OK.
15. Peida pivot-tabelites lehevälja read ja veerud, samuti enda loodud turgude ja indeksivalemite loend.
Joonisel fig. Joonis 21 näitab lõpptulemust. Nüüd on teil kasutajaliides, mis võimaldab klientidel valida ühe ripploendi abil turu mõlemas liigendtabelis.
Kui valite ripploendist uue üksuse, muudetakse veergude suurust automaatselt, et mahutada kõik kuvatavad andmed. Programmi selline käitumine on töölehe malli vormindamisel üsna tüütu. Saate seda vältida, paremklõpsates liigendtabelit ja valides PivotTable-liigendtabeli valikud. Ekraanile ilmub samanimeline dialoogiboks, milles peate märkeruudu lähtestama Muuda värskendamisel automaatselt veergude laiust.
Märkus on kirjutatud Jeleni Aleksandri raamatu põhjal. . 12. peatükk.
Selleks peate avama Visual Basicu redaktori (klahvikombinatsioon "Alt+F11" või paremklõpsake mis tahes lehe otseteel ja valige "Alliktekst" või menüü vahekaardi "Arendaja" rühmast "Kood" , klõpsake üksusel "Visual Basic") ja sisestage projekti standardmoodul. Ja lisada sellele moodulile kaks programmikoodi (vt joonis 8.) - ja .
|
Tume leht töölehena Dim cell as Range ActiveWorkbookiga Iga lehe jaoks jaotises ActiveWorkbook.Worksheets Määra lahter = Töölehed(1). Lahtrid(leht.indeks, 1) Töölehed(1).Hyperlinks.Add anchor:=cell, Address:="", lahter.Valem = leht.Nimi |
Kuidas optimeerida mitme otsinguväärtuse valikut erinevatest tabelitest Excelis
Saate optimeerida mitme soovitud väärtuse (maksesummad erinevatele tarnijatele) valimist ühe parameetri järgi (näiteks kuupäeva järgi) väikesest arvust tabelitest, kasutades peaaegu sama skeemi nagu ainsa erinevusega, et lehel “Raamatu sisukord” kuvatakse valikus osalevate tabelite nimed ja selle tulemused ning lisaks kasutatakse veidi teistsugust tabeliotsingu valemit:
"=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)", kus:
- lahter C1 (kvartal number) – määrab parameetri väärtuse;
- “INDIRECT(A2)” – määratleb tekstilingi nimega vahemikku, mille nimi on lahtris A2;
- “2” on tarnijate lähtetabelite veeru number, mis sisaldavad meile vajalikke maksesummasid;
- “FALSE” (saab asendada 0-ga) – näitab funktsioonile VLOOKUP, et vaja on täpset vastet.
