Ang esensya ng isang query sa pagpili ay ang pumili ng mga row mula sa source table na nakakatugon sa ilang partikular na pamantayan (katulad ng paggamit ng isang standard). Pumili tayo ng mga value mula sa source table gamit ang . Hindi tulad ng paggamit ( CTRL+SHIFT+L o Data/ Pag-uri-uriin at Salain/ Salain) ang mga napiling row ay ilalagay sa isang hiwalay na talahanayan.
Sa artikulong ito, titingnan natin ang mga pinakakaraniwang query, halimbawa: pagpili ng mga hilera ng talahanayan na ang halaga mula sa isang numeric na column ay nasa loob ng isang tinukoy na hanay (interval); pagpili ng mga hilera na ang petsa ay kabilang sa isang partikular na panahon; mga gawain na may 2 pamantayan sa teksto at iba pa. Magsimula tayo sa mga simpleng query.
1. Isang numerical criterion (Piliin ang Mga Produktong iyon na ang presyo ay mas mataas kaysa sa minimum)
halimbawa ng file, sheet Isang criterion - numero ).
Kinakailangan na ipakita sa isang hiwalay na talahanayan lamang ang mga talaan (mga hilera) mula sa talahanayan ng Pinagmulan na ang presyo ay mas mataas sa 25.
Madali mong malulutas ito at ang mga kasunod na problema gamit ang . Upang gawin ito, piliin ang mga header ng Source table at i-click CTRL+SHIFT+L. Pumili mula sa drop-down na listahan sa tabi ng header ng Mga Presyo Mga filter na numero..., pagkatapos ay itakda ang mga kinakailangang kundisyon sa pag-filter at i-click ang OK.
Ang mga rekord na nakakatugon sa mga kundisyon sa pagpili ay ipapakita.
Ang isa pang diskarte ay ang paggamit. Sa kabaligtaran, ang mga napiling row ay ilalagay sa isang hiwalay na talahanayan - isang natatangi, na, halimbawa, ay maaaring i-format sa isang istilong naiiba sa Pinagmulan na talahanayan o iba pang mga pagbabago ay maaaring gawin.
Ilalagay namin ang criterion (minimum price) sa cell E6 , talahanayan para sa na-filter na data - nasa hanay D10:E19 .
Ngayon ay piliin natin ang hanay D11:D19 (Produkto ng Column) at ilagay ang:
INDEX(A11:A19;
MALIIT(KUNG ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-ROW($B$10))
sa halip na PUMASOK pindutin ang keyboard shortcut CTRL+SHIFT+ENTER(ang array formula ay ).
E11:E19 (Presyo ng column) kung saan maglalagay tayo ng katulad:
INDEX(B11:B19;
MALIIT(KUNG ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-ROW($B$10))
Bilang resulta, makakakuha tayo ng bagong talahanayan na maglalaman lamang ng mga produkto na ang mga presyo ay hindi bababa sa mga nakasaad sa cell E6 .
Upang ipakita ang dynamism ng natanggap na Sample na Kahilingan, papasok kami sa E6 halaga 55. 2 tala lamang ang isasama sa bagong talahanayan.
Kung nagdagdag ka ng bagong produkto na may Presyo na 80 sa talahanayan ng Pinagmulan, pagkatapos ay awtomatikong idaragdag ang isang bagong tala sa bagong talahanayan.
Tandaan. Maaari mo ring gamitin at upang ipakita ang na-filter na data. Ang pagpili ng isang partikular na tool ay depende sa gawaing kinakaharap ng user.
Kung hindi ka kumportable sa paggamit array formula, na nagbabalik ng maraming value, pagkatapos ay maaari kang gumamit ng isa pang diskarte, na tinatalakay sa mga seksyon sa ibaba: 5.a, 7, 10 at 11. Sa mga kasong ito, .
2. Dalawang numerical na pamantayan (Piliin ang mga Produktong iyon na ang presyo ay nasa loob ng hanay)
Hayaang magkaroon ng Initial table na may listahan ng mga Produkto at Presyo (tingnan. halimbawa ng file, sheetSaklaw ng Numero).
Ilalagay namin ang pamantayan (mas mababa at mas mataas na mga limitasyon ng presyo) sa hanay E5:E6 .
Yung. kung nasa loob ng tinukoy na agwat ang Presyo ng Produkto, lalabas ang naturang talaan sa bagong talahanayan ng Na-filter na Data.
Hindi tulad ng nakaraang gawain, gagawa kami ng dalawa: Mga Produkto at Mga Presyo (magagawa mo nang wala ang mga ito, ngunit maginhawa sila kapag nagsusulat ng mga formula). Ang kaukulang mga formula ay dapat magmukhang sa Name Manager ( Mga Formula/ Tinukoy na Pangalan/ Name Manager) bilang mga sumusunod (tingnan ang figure sa ibaba).
Ngayon ay piliin natin ang hanay D11:D19 at pumasok kami:
INDEX(Mga Produkto;
LEAST(
KUNG(($E$5<=Цены)*($E$6>=Mga Presyo);ROW(Mga Presyo);"");
sa halip na PUMASOK pindutin ang keyboard shortcut CTRL+SHIFT+ENTER.
Gagawin namin ang parehong mga manipulasyon sa hanay E11:E19 kung saan ipapakilala namin ang isang katulad:
INDEX(Mga Presyo;
LEAST(
KUNG(($E$5<=Цены)*($E$6>=Mga Presyo);ROW(Mga Presyo);"");
LINE(Mga Presyo)-ROW($B$10))-ROW($B$10))
Bilang resulta, makakakuha tayo ng bagong talahanayan na maglalaman lamang ng mga produkto na ang mga presyo ay nasa loob ng pagitan na tinukoy sa mga cell E5 At E6 .
Upang ipakita ang dynamism ng natanggap na Ulat (Sampling Request), papasok kami E6 halaga 65. Ang isa pang tala mula sa talahanayan ng Pinagmulan na nakakatugon sa bagong pamantayan ay idaragdag sa bagong talahanayan.
Kung nagdagdag ka ng bagong produkto na may Presyo sa hanay mula 25 hanggang 65 sa talahanayan ng Pinagmulan, pagkatapos ay may idaragdag na bagong tala sa bagong talahanayan.
Ang halimbawang file ay naglalaman din ng mga array formula na may error sa paghawak kapag ang Price column ay naglalaman ng isang error value, halimbawa #DIV/0! (tingnan ang sheet Error sa Paghawak).
Ang mga sumusunod na problema ay nalutas sa katulad na paraan, kaya hindi namin isasaalang-alang ang mga ito nang detalyado.
3. Isang criterion na Petsa (Piliin ang mga Produktong iyon na ang Petsa ng Paghahatid ay tumutugma sa tinukoy)
halimbawa ng file, sheetIsang pamantayan - Petsa).
Upang pumili ng mga row, ginagamit ang mga array formula na katulad ng Gawain 1 (sa halip na ang criterion<= используется =):
=INDEX(A12:A20,MALIIT(KUNG($E$6=B12:B20,ROW(B12:B20),"");ROW(B12:B20)-ROW($B$11))-ROW($B$11) )
INDEX(B12:B20,MALIIT(KUNG ($E$6=B12:B20,ROW(B12:B20),"");ROW(B12:B20)-ROW($B$11))-ROW($B$11) )
4. Dalawang pamantayan ng Petsa (Piliin ang mga Produktong iyon na ang Petsa ng Paghahatid ay nasa saklaw)
Hayaang magkaroon ng talahanayan ng Pinagmulan na may listahan ng Mga Produkto at Petsa ng Paghahatid (tingnan. halimbawa ng file, sheetSaklaw ng Petsa).
Pakitandaan na HINDI SORTED ang column na Petsa.
Solusyon1: Maaari mong gamitin.
Mag-type sa cell D12 array formula:
INDEX(A$12:A$20;
PINAKAMALAKING(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(ROW($B$12:$B$20)-ROW($B$11));
$J$12-ROW(A12)+ROW($B$11)+1))
Tandaan: Pagkatapos ipasok ang formula, sa halip na ang ENTER key, kailangan mong pindutin ang key combination CTRL+SHIFT+ENTER. Ginagamit ang keyboard shortcut na ito para magpasok ng mga array formula.
Kopyahin ang array formula hanggang sa nais na bilang ng mga cell. Ibabalik lang ng formula ang mga halagang iyon para sa Mga Produkto na naihatid sa loob ng tinukoy na hanay ng petsa. Ang natitirang mga cell ay maglalaman ng #NUM mga error. Mga error sa halimbawang file (Sheet 4.Hanay ng Petsa) .
Dapat maglagay ng katulad na formula para sa mga petsa sa column E.
Sa isang selda J12 Ang bilang ng mga row ng source table na nakakatugon sa pamantayan ay kinakalkula:
COUNTIFS(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)
Ang mga row sa source table na nakakatugon sa pamantayan ay .
Solusyon2: Para pumili ng mga row, maaari kang gumamit ng mga array formula na katulad ng Gawain 2 (ibig sabihin):
=INDEX(A12:A20,MALIIT(KUNG(($E$6<=B12:B20)*($E$7>=B12:B20);ROW(B12:B20);"");ROW(B12:B20)-ROW($B$11))-ROW($B$11))
INDEX(B12:B20,MALIIT(KUNG(($E$6<=B12:B20)*($E$7>=B12:B20);ROW(B12:B20);"");ROW(B12:B20)-ROW($B$11))-ROW($B$11))
Upang ipasok ang unang formula, piliin ang hanay ng mga cell G12:G20 . Pagkatapos ipasok ang formula, sa halip na ang ENTER key, kailangan mong pindutin ang key combination CTRL+SHIFT+ENTER.
Solusyon3: Kung SORTED ang column ng Petsa, hindi mo kailangang gumamit ng mga array formula.
Una kailangan mong kalkulahin ang una at huling mga posisyon ng mga hilera na nakakatugon sa pamantayan. Pagkatapos ay i-output ang mga linya.
Ang halimbawang ito ay muling malinaw na nagpapakita kung gaano kadaling magsulat ng mga formula.
5. Isang criterion na Petsa (Piliin ang mga Produktong iyon na ang Petsa ng Paghahatid ay hindi mas maaga/hindi lalampas sa tinukoy)
Hayaang magkaroon ng talahanayan ng Pinagmulan na may listahan ng Mga Produkto at Petsa ng Paghahatid (tingnan. halimbawa ng file, sheet Isang pamantayan - Petsa (hindi mamaya) ).
Upang pumili ng mga row na ang petsa ay hindi mas maaga (kabilang ang petsa mismo), isang array formula ang ginagamit:
=INDEX(A12:A20,MALIIT(KUNG ($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Gayundin sa halimbawang file ay mga formula para sa mga kundisyon: Hindi bago (hindi kasama); Hindi mamaya (kabilang); Hindi mamaya (hindi kasama).
7. Isang pamantayan ng Teksto (Pumili ng Mga Produkto ng isang tiyak na uri)
Hayaang magkaroon ng Initial table na may listahan ng mga Produkto at Presyo (tingnan. halimbawa ng file, sheetIsang pamantayan - Teksto).
8. Dalawang pamantayan sa Teksto (Pumili ng Mga Produkto ng isang partikular na uri, ihahatid sa isang partikular na buwan)
Hayaang magkaroon ng Initial table na may listahan ng mga Produkto at Presyo (tingnan. halimbawa ng file, sheet 2 pamantayan - teksto (I) ).
INDEX ($A$11:$A$19;
MALIIT(KUNG(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);ROW($A$11:$A$19)-ROW($A$10); 30);ROW(INDIRECT("A1:A"&ROW($A$11:$A$19)))))
Pagpapahayag ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) tumutukoy sa parehong kundisyon (Produkto at Buwan).
Pagpapahayag ROW(INDIRECT("A1:A"&ROW($A$11:$A$19))) mga anyo (1:2:3:4:5:6:7:8:9), i.e. mga numero ng hilera sa talahanayan.
9. Dalawang Pamantayan sa Teksto (Mga Pumili ng Mga Produkto ng Ilang Mga Uri)
Hayaang magkaroon ng Initial table na may listahan ng mga Produkto at Presyo (tingnan. halimbawa ng file, sheet2 pamantayan - teksto (O)).
Hindi tulad ng Problema 7, pipili kami ng mga hilera na may 2 uri ng mga kalakal ().
Ginagamit ang array formula para pumili ng mga row:
INDEX(A$11:A$19;
MALAKI((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(ROW($A$11:$A$19)-ROW($A$10) ); COUNTIF($A$11:$A$19,$E$6)+COUNTIF($A$11:$A$19,$E$7)-ROW($A$11:A11)+1))
Kundisyon ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) ginagarantiyahan na ang mga produkto lamang ng mga tinukoy na uri ang pipiliin mula sa mga dilaw na selula (Produkto2 at Produkto3). Ang + sign (dagdag) ay ginagamit upang tukuyin (hindi bababa sa 1 criterion ang dapat matugunan).
Ibabalik ng expression sa itaas ang array (0:0:0:0:1:1:1:0:0). Pagpaparami nito sa ekspresyon ROW($A$11:$A$19)-ROW ($A$10), ibig sabihin. sa (1:2:3:4:5:6:7:8:9), nakakakuha tayo ng hanay ng mga posisyon (mga numero ng hilera ng talahanayan) na nakakatugon sa pamantayan. Sa aming kaso ito ay isang array (0:0:0:0:5:6:7:0:0).
Bilang halimbawa, nagbibigay kami ng mga solusyon sa sumusunod na problema: Piliin ang Mga Produkto na ang presyo ay nasa isang partikular na hanay at inuulit sa isang tiyak na bilang ng beses o higit pa.
Kunin natin ang talahanayan ng mga padala ng mga kalakal bilang paunang isa.
Ipagpalagay natin na interesado tayo sa kung ilan at anong mga batch ng mga kalakal ang naibigay sa presyong 1000 rubles. hanggang sa 2000 kuskusin. (criterion 1). Bukod dito, dapat mayroong hindi bababa sa 3 lot na may parehong presyo (criterion 2).
Ang solusyon ay isang array formula:
MALIIT(ROW ($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))
Ibinabalik ng formula na ito ang mga row number na nakakatugon sa parehong pamantayan.
Formula =SUMPRODUCT(($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10)) binibilang ang bilang ng mga hilera na nakakatugon sa pamantayan.
11. Gamitin ang criterion value (Anumang) o (Lahat)
SA halimbawa ng file sa sheet na "11. Criterion Alinman o (Lahat)" Naipatupad na ang bersyong ito ng criterion.
Ang formula sa kasong ito ay dapat maglaman ng IF() function. Kung ang halaga (Lahat) ay pinili, pagkatapos ay isang formula ang ginagamit upang ipakita ang mga halaga nang hindi isinasaalang-alang ang pamantayang ito. Kung pipiliin ang anumang iba pang halaga, gumagana ang pamantayan tulad ng dati.
KUNG ($C$8="(Lahat)";
MALIIT((ROW ($B$13:$B$26)-ROW($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
MALIIT((ROW($B$13:$B$26)-ROW($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8) ;F13+($G$6-$G$7)))
Ang natitirang bahagi ng formula ay katulad ng mga tinalakay sa itaas.
Unang Paraan: Paggamit ng advanced na autofilter
Sa isang Excel sheet, kailangan mong pumili ng isang lugar sa mga data na gusto mong piliin. Sa tab na "Home", i-click ang "Pag-uuri at Filter" (matatagpuan sa bloke ng mga setting ng "Pag-edit"). Susunod, mag-click sa filter.
Magagawa mo ito nang iba: pagkatapos piliin ang lugar, pumunta sa tab na "Data" at mag-click sa "Filter", na matatagpuan sa pangkat na "Pag-uuri at Filter".
Kapag nakumpleto ang mga pagkilos na ito, dapat na lumitaw ang mga icon sa header ng talahanayan upang simulan ang pag-filter. Ipapakita ang mga ito ng point down sa maliliit na tatsulok sa kanang gilid ng mga cell. Mag-click sa icon na ito sa simula ng column kung saan pipili ka. Buksan ang menu, pumunta sa “Mga text filter” at piliin ang “Custom na filter...”.
Dapat na ngayong i-activate ang Custom Filtering window. Sa loob nito itinakda mo ang paghihigpit kung saan isasagawa ang pagpili. Maaari kang pumili ng isa sa limang iminungkahing uri ng mga kundisyon: katumbas, hindi katumbas, mas malaki kaysa, mas malaki kaysa, o katumbas ng, mas mababa kaysa.
Pagkatapos ng pag-filter, ang mga linyang iyon lang ang mananatili kung saan ang halaga ng kita ay lumampas sa 10,000 (bilang halimbawa).
Sa parehong column maaari kang magdagdag ng pangalawang kundisyon. Kailangan mong bumalik muli sa custom na window ng pag-filter, at magtakda ng ibang limitasyon sa pagpili sa ibabang bahagi nito. Itakda ang switch sa "Mas" na posisyon, at ilagay ang "15000" sa field sa kanan.
Ang talahanayan ay maglalaman lamang ng mga row kung saan ang halaga ng kita ay hindi bababa sa 10,000, ngunit hindi hihigit sa 15,000.
Sa ibang mga column, ang pagpili ay naka-configure sa katulad na paraan. Sa gustong column, mag-click sa icon ng filter, at pagkatapos ay sunud-sunod na mag-click sa mga item sa listahan na "I-filter ayon sa petsa" at "Custom na filter".
Dapat bumukas ang window ng Custom na AutoFilter. Halimbawa, magsagawa ng seleksyon ng mga resulta sa talahanayan mula Mayo 4 hanggang Mayo 6, 2016, kasama. I-click ang "Pagkatapos o katumbas", at sa patlang sa kanan ipasok ang halaga na "05/04/2016". Sa ibabang bloke, ilagay ang switch sa posisyon na "Bago o katumbas", at sa kanang field ipasok ang "05/06/2016". Iwanan ang switch sa pagiging tugma ng kondisyon sa default na posisyon, iyon ay, "AT". Upang ilapat ang pag-filter, i-click ang OK.
Ang listahan ay dapat na ngayong mabawasan nang higit pa, dahil ang mga hilera lamang ang mananatili kung saan ang halaga ng kita ay nag-iiba mula 10,000 hanggang 15,000, at ito ay para sa panahon mula 05/04 hanggang 05/06/2016 kasama.
Sa isa sa mga column, maaari mong i-reset ang pag-filter kung ninanais. Halimbawa, magagawa mo ito para sa mga halaga ng kita. Mag-click sa icon ng autofilter sa kaukulang column. Piliin ang "Alisin ang filter".
Idi-disable ang pagpili ayon sa halaga ng kita at ang pagpili lamang ayon sa mga petsa ang mananatili (mula 05/04/2016 hanggang 05/06/2016).
May isa pang column sa table na tinatawag na "Pangalan". Naglalaman ito ng data sa format ng teksto. Gamit ang mga value na ito, maaari ka ring bumuo ng sample. Sa pangalan ng column, mag-click sa icon ng filter. Pumunta sa "Mga Filter ng Teksto" at pagkatapos ay "Custom Filter...".
Magbubukas muli ang window ng custom na filter, kung saan maaari kang pumili, halimbawa, sa pamamagitan ng mga pangalan na "Meat" at "Potatoes". Sa unang bloke kailangan mong itakda ang switch sa "Pantay" na posisyon at ipasok ang "Patatas" sa patlang sa kanan nito. Itakda ang switch ng mas mababang bloke sa "Pantay" na posisyon, at sa patlang na kabaligtaran - "Meat". Ngayon ay dapat mong itakda ang switch ng compatibility ng kundisyon sa posisyong "OR". I-click ang OK.
Sa bagong sample, ang mga paghihigpit ay itinakda ayon sa petsa (mula 05/04/2016 hanggang 05/06/2016) at ayon sa pangalan (Meat and Potatoes). Walang mga paghihigpit lamang sa halaga ng kita.
Maaari mong ganap na alisin ang filter at ito ay ginagawa gamit ang parehong mga pamamaraan na ginamit upang itakda ito. Upang i-reset ang pag-filter, sa tab na "Data", mag-click sa "Filter" sa pangkat na "Pag-uuri at Filter".
Sa pangalawang opsyon, maaari kang pumunta sa tab na "Home" at mag-click sa "Pag-uuri at Filter" sa "Pag-edit". Susunod, mag-click sa "Filter".
Kung gagamit ka ng alinman sa mga pamamaraang ito, tatanggalin ang talahanayan at iki-clear ang mga resulta ng pagpili. Iyon ay, ipapakita ng talahanayan ang lahat ng data na dati nang ipinasok dito.
Ikalawang Paraan: Paggamit ng array formula
Sa parehong Excel sheet, lumikha ng isang walang laman na talahanayan na may parehong mga pangalan ng column sa header na nasa pinagmulan.
Dapat piliin ang lahat ng walang laman na cell sa unang column ng bagong talahanayan. Ilagay ang cursor sa formula bar upang ipasok ang formula - =INDEX(A2:A29,SMALL(IF(15000)
Upang ilapat ang formula, pindutin ang Ctrl+Shift+Enter key.
Piliin ang pangalawang column na may mga petsa at ilagay ang cursor sa formula bar para ipasok - =INDEX(B2:B29,SMALL(IF(15000)
Sa parehong paraan, ilagay ang sumusunod na formula sa column ng kita - =INDEX(C2:C29,SMALL(IF(15000)
Magbubukas ang isang window ng pag-format kung saan kailangan mong piliin ang tab na "Numero". Sa "Mga Format ng Numero" piliin ang "Petsa". Sa kanang bahagi ng window, kung ninanais, maaari mong piliin ang uri ng petsa na ipinapakita, at kapag nagawa na ang lahat ng mga setting, mag-click sa OK.
Ngayon ang lahat ay magiging maganda, at ang petsa ay ipapakita nang tama. Kung ang mga cell ay nagpapakita ng halaga na "#NUMBER!", pagkatapos ay kailangan mong ilapat ang conditional formatting. Dapat piliin ang lahat ng mga cell ng talahanayan (maliban sa header) at, sa tab na "Home", mag-click sa "Conditional Formatting" (sa tool block na "Mga Estilo". May lalabas na listahan kung saan dapat mong piliin ang “Gumawa ng panuntunan...”.
Piliin ang mga panuntunang "I-format lamang ang mga cell na naglalaman", at sa unang field, na matatagpuan sa ilalim ng linyang "I-format lamang ang mga cell kung saan natutugunan ang sumusunod na kundisyon", piliin ang "Mga Error" at i-click ang "Format...".
Magbubukas ang isang window ng pag-format, kung saan pumunta sa "Font" at piliin ang puti. Mag-click sa OK.
Ang isang handa na sample ayon sa tinukoy na limitasyon ay lilitaw sa harap mo, at lahat ito ay nasa isang hiwalay na talahanayan.
Pangatlong paraan: Pag-sample ng ilang kundisyon gamit ang isang formula
Ang mga kundisyon sa hangganan para sa sample ay dapat ilagay sa isang hiwalay na column.
Isa-isa, piliin ang mga walang laman na column ng bagong talahanayan para ipasok ang kinakailangang tatlong formula sa kanila. Sa unang column ipasok ang - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 )-ROW($C$1)). Susunod, ilagay ang parehong mga formula sa mga column, baguhin lamang ang mga coordinate pagkatapos ng pangalan ng operator ng INDEX sa mga kinakailangan at tumutugma sa ilang mga column. Ang lahat ay katulad ng nakaraang pamamaraan. Sa tuwing papasok ka, huwag kalimutang pindutin ang key combination na Ctrl+Shift+Enter.
Kung kailangan mong baguhin ang mga hangganan ng sampling, maaari mo lamang baguhin ang mga numero ng hangganan sa column ng mga kondisyon at pagkatapos ay awtomatikong mababago ang resulta ng pagpili.
Ikaapat na paraan: Random sampling
Sa kaliwang bahagi ng talahanayan kailangan mong laktawan ang isang column, at sa cell ng susunod na isa ay ipasok ang formula - =RAND() upang magpakita ng random na numero. Upang isaaktibo ito, pindutin ang ENTER.
Kung kailangan mong gumawa ng isang buong column ng mga random na numero, pagkatapos ay ilagay ang cursor sa kanang sulok sa ibaba ng cell na naglalaman ng formula. Dapat lumitaw ang isang fill marker, na dapat i-drag pababa sa pamamagitan ng pagpindot sa kaliwang pindutan ng mouse. Ginagawa ito parallel sa talahanayan na may data at hanggang sa dulo.
Ang hanay ng mga cell ay maglalaman ng RAND formula, ngunit hindi mo kailangang gumamit ng mga pure value. Kopyahin sa walang laman na column sa kanan at pumili ng hanay ng mga cell na may mga random na numero. Sa tab na "Home", i-click ang "Kopyahin".
Pumili ng isang walang laman na column at i-right-click upang ilabas ang menu ng konteksto. Sa pangkat ng tool na "Insert Options", piliin ang "Values" (ipinapakita bilang isang icon na may mga numero).
Sa tab na "Home", mag-click sa "Pag-sort at Filter", at pagkatapos ay "Custom Sorting".
Sa tabi ng opsyong "Ang aking data ay naglalaman ng mga header," lagyan ng check ang kahon. Sa linyang "Pagbukud-bukurin", ipahiwatig ang pangalan ng hanay kung saan matatagpuan ang mga kinopyang random na halaga ng numero. Sa linyang "Pag-uuri", ang mga setting ay nananatili sa kanilang mga default. Sa linyang "Order", piliin ang opsyong "Pataas" o "Pababa". Mag-click sa OK.
Ang mga halaga ng talahanayan ay dapat ayusin sa pataas o pababang pagkakasunud-sunod ng mga random na numero. Ang anumang bilang ng mga unang hilera mula sa talahanayan ay maaaring kunin at ituring na resulta ng isang random na sample.
Ang macro ay isang pagkakasunud-sunod ng mga aksyon na naitala at nai-save para magamit sa ibang pagkakataon. Ang isang naka-save na macro ay maaaring i-play pabalik gamit ang isang espesyal na command. Sa madaling salita, maaari mong i-record ang iyong mga aksyon sa isang macro, i-save ito, at pagkatapos ay payagan ang ibang mga user na i-play muli ang mga aksyon na naka-save sa macro gamit ang isang simpleng keystroke. Ito ay lalong kapaki-pakinabang kapag namamahagi ng mga ulat ng PivotTable.
Sabihin nating gusto mong bigyan ang iyong mga kliyente ng kakayahang magpangkat ng mga ulat ng PivotTable ayon sa buwan, quarter, at taon. Sa teknikal na paraan, maaaring gawin ng sinuman ang proseso ng pagpapangkat, ngunit hindi mararamdaman ng ilan sa iyong mga kliyente ang pangangailangan na maunawaan ito. Sa ganoong sitwasyon, maaari kang magtala ng isang pagpapangkat ng macro ayon sa buwan, isa pa ayon sa quarter, at isang pangatlo ayon sa taon. Pagkatapos ay lumikha ng tatlong mga pindutan - isa para sa bawat macro. Pagkatapos ang iyong mga kliyente na bago sa PivotTables ay kakailanganin lamang na mag-click ng isang button upang maayos na pagpangkatin ang ulat ng PivotTable.
Ang pangunahing pakinabang ng paggamit ng mga macro sa mga ulat ng PivotTable ay upang paganahin ang mga kliyente na mabilis na magsagawa ng mga operasyon sa PivotTable na hindi nila karaniwang magagawa. Dahil dito, ang kahusayan ng pagsusuri sa ibinigay na data ay makabuluhang tumataas.
I-download ang tala sa o format, i-download na may mga halimbawa (sa loob ay isang Excel file na may mga macro; hindi pinapayagan ng patakaran ng provider ang direktang pag-upload ng file na may ganitong format sa site).
Mag-record ng macro
Tingnan ang talahanayan ng buod na ipinapakita sa Fig. 1. Maaari mong i-refresh ang pivot table na ito sa pamamagitan ng pag-right click sa loob nito at pagpili Update. Kung nagtala ka ng mga pagkilos bilang isang macro habang ina-update ang PivotTable, ikaw o sinuman ay maaaring i-replay ang mga pagkilos na iyon at i-update ang PivotTable bilang resulta ng pagpapatakbo ng macro.
kanin. 1. Ang pagre-record ng mga aksyon habang ina-update ang pivot table na ito ay magbibigay-daan sa iyong i-update ang data sa hinaharap bilang resulta ng pagpapatakbo ng macro
Ang unang hakbang sa pagre-record ng macro ay ang pagtawag sa dialog box Mag-record ng macro. Pumunta sa tab Developer ribbon at i-click ang button Mag-record ng macro. (Kung hindi mo mahanap ang tab sa ribbon Developer, piliin ang tab file, at mag-click sa pindutan Mga pagpipilian. Sa dialog box na lalabas Mga Pagpipilian sa Excel piliin ang kategorya I-customize ang Ribbon at sa listahan sa kanan, lagyan ng check ang kahon Developer. Bilang resulta, may lalabas na tab sa ribbon Developer.) Ang isang alternatibong paraan upang simulan ang pag-record ng macro ay ang pag-click sa button (Larawan 2).
Sa dialog box Mag-record ng macro Ilagay ang sumusunod na macro information (Figure 3):
Pangalanmacro. Dapat ilarawan ng pangalan ang mga pagkilos na ginawa ng macro. Ang pangalan ay dapat magsimula sa isang titik o underscore; hindi dapat maglaman ng espasyo o iba pang mga ilegal na character; hindi dapat kapareho ng built-in na pangalan ng Excel o ang pangalan ng isa pang bagay sa workbook.
Kumbinasyonmga susi. Maaari kang magpasok ng anumang titik sa field na ito. Ito ay magiging bahagi ng keyboard shortcut na gagamitin para i-play ang macro. Hindi kinakailangang tumukoy ng key combination. Bilang default, Ctrl lang ang inaalok bilang simula ng kumbinasyon. Kung gusto mong isama din ng kumbinasyon ang Shift, i-type ang titik sa window habang pinipindot ang Shift key
I-saveV. Dito nakaimbak ang macro. Kung balak mong ipamahagi ang ulat ng PivotTable sa ibang mga user, piliin ang opsyon Itoaklat. Pinapayagan ka rin ng Excel na mag-save ng isang macro sa Bagong libro o sa Personal na macro book.
Paglalarawan. Ang isang paglalarawan ng macro na gagawin ay ipinasok sa field na ito.
kanin. 3. Pag-customize ng window Mag-record ng macro
Dahil ina-update ng macro ang pivot table, piliin ang pangalan Update ng Data. Maaari mo ring italaga ang keyboard shortcut na Ctrl+Shift+Q sa isang macro. Tandaan na sa sandaling lumikha ka ng isang macro, gagamitin mo ang keyboard shortcut na ito upang patakbuhin ito. Para sa lokasyon ng macro storage, piliin ang opsyon Ang librong ito at i-click OK.
Pagkatapos mag-click sa dialog box Mag-record ng macro sa pindutan OK Magsisimula ang macro recording. Sa puntong ito, lahat ng gagawin mo sa Excel ay maitatala.
Mag-right-click sa lugar ng PivotTable at piliin Update(tulad ng sa Fig. 1, ngunit sa macro recording mode). Pagkatapos i-update ang pivot table, maaari mong ihinto ang proseso ng macro recording gamit ang button Itigil ang pagre-record mga tab Developer. O mag-click muli sa pindutan na ipinapakita sa Fig. 2.
Kaya't naitala mo ang iyong unang macro. Ngayon ay maaari mong isagawa ang macro gamit ang key na kumbinasyon Ctrl+Shift+Q.
Macro security warning. Dapat tandaan na kung ang mga macro ay naitala ng gumagamit, sila ay isasagawa nang walang anumang mga paghihigpit mula sa subsystem ng seguridad. Gayunpaman, kung namahagi ka ng workbook na naglalaman ng mga macro, dapat mong bigyan ang iba pang mga user ng pagkakataon upang matiyak na walang panganib sa pagbubukas ng mga file ng trabaho at na ang pagpapatakbo ng mga macro ay hindi makakahawa sa system ng mga virus. Sa partikular, agad mong mapapansin na ang halimbawang file na ginamit sa kabanatang ito ay hindi gagana nang buo maliban kung partikular mong pinapayagan ang Excel na magpatakbo ng mga macro dito.
Ang pinakamadaling paraan upang matiyak ang macro security ay ang lumikha ng pinagkakatiwalaang lokasyon - isang folder kung saan ang mga "pinagkakatiwalaang" workbook lang na walang mga virus ang ilalagay. Ang isang pinagkakatiwalaang lokasyon ay nagbibigay-daan sa iyo at sa iyong mga kliyente na magpatakbo ng mga macro sa mga workbook nang walang anumang mga paghihigpit sa seguridad (ang gawi na ito ay nagpapatuloy hangga't ang mga workbook ay nasa isang pinagkakatiwalaang lokasyon).
Upang mag-set up ng pinagkakatiwalaang lokasyon, sundin ang mga hakbang na ito:
Pumili ng ribbon tab Developer at mag-click sa pindutan Macro Security. May lalabas na dialog box sa screen Trust Center.
I-click ang button Magdagdag ng bagong lokasyon.
I-click ang button Balik-aral para tumukoy ng folder para sa mga file ng trabaho na pinagkakatiwalaan mo.
Sa sandaling tumukoy ka ng pinagkakatiwalaang lokasyon, lahat ng workbook sa lokasyong iyon ay tatakbo ng mga arbitrary na macro bilang default.
Sa Excel 2013, ang modelo ng seguridad ay pinahusay. Ngayon ang mga file ng workbook na dati ay "pinagkakatiwalaan" ay naaalala, i.e. pagkatapos buksan ang Excel workbook at i-click ang button Isama ang nilalaman Naaalala ng Excel ang estado na ito. Bilang resulta, ang aklat na ito ay nabibilang sa kategorya ng mga pinagkakatiwalaan, at ang mga hindi kinakailangang tanong ay hindi itatanong sa susunod na oras na ito ay mabuksan.
Paglikha ng User Interface Gamit ang Mga Kontrol sa Form
Ang pagpapatakbo ng macro gamit ang kumbinasyon ng Ctrl+Shift+Q na key ay makakatulong kapag mayroon lamang isang macro sa ulat ng PivotTable. (Gayundin, dapat alam ng mga user ang kumbinasyong ito.) Ngunit ipagpalagay na gusto mong bigyan ang iyong mga kliyente ng ilang mga macro na nagsasagawa ng iba't ibang mga aksyon. Sa kasong ito, kailangan mong bigyan ang mga kliyente ng isang malinaw at simpleng paraan upang patakbuhin ang bawat macro, nang hindi kinakailangang tandaan ang mga pangunahing kumbinasyon. Ang perpektong solusyon ay isang simpleng user interface na may hanay ng mga kontrol gaya ng mga button, scroll bar, at iba pang mga kontrol na nagbibigay-daan sa iyong magsagawa ng mga macro gamit ang mga pag-click ng mouse.
Nag-aalok sa iyo ang Excel ng isang hanay ng mga tool na idinisenyo upang lumikha ng mga user interface nang direkta sa iyong spreadsheet. Ang mga tool na ito ay tinatawag na mga kontrol ng form. Ang pangunahing ideya ay maaari kang maglagay ng kontrol ng form sa isang spreadsheet at magtalaga dito ng isang macro na dati nang naitala. Sa sandaling italaga sa isang kontrol, ang macro ay ilulunsad sa pamamagitan ng pag-click sa elementong iyon.
Ang mga kontrol sa form ay matatagpuan sa grupo Mga kontrol sa form mga tab ng laso Developer. Upang buksan ang control palette, i-click ang button sa pangkat na ito Ipasok(Larawan 4).
kanin. 4. Kontrol sa anyo Pindutan
Pakitandaan: bilang karagdagan sa mga kontrol ng form, naglalaman din ang palette Mga kontrol ng ActiveX. Bagama't magkapareho sila, sa programmatically sila ay ganap na magkakaibang mga bagay. Mga kontrol sa form sa kanilang limitadong mga kakayahan at simpleng mga setting, sila ay partikular na idinisenyo upang ilagay sa mga worksheet. Kasabay nito Mga kontrol ng ActiveX pangunahing ginagamit sa mga pasadyang anyo. Ugaliing maglagay lamang ng mga kontrol sa form sa iyong mga worksheet.
Dapat mong piliin ang mga kontrol na pinakaangkop sa gawain sa kamay. Sa halimbawang ito, kailangang ma-update ng mga kliyente ang pivot table sa pamamagitan ng pag-click sa isang button. Mag-click sa control Pindutan, ilipat ang pointer ng mouse sa lugar sa worksheet kung saan mo gustong ilagay ang button, at i-click.
Sa sandaling ilagay mo ang pindutan sa talahanayan, magbubukas ang isang dialog box Magtalaga ng macro bagay(Larawan 5). Piliin ang kinakailangang macro (sa aming kaso - Update ng Data, naitala nang mas maaga) at mag-click sa pindutan OK.
kanin. 5. Piliin ang macro na gusto mong italaga sa button at i-click ang button OK. Sa kasong ito, dapat mong gamitin ang macro Update ng Data
Kapag nailagay mo na ang lahat ng kinakailangang kontrol sa iyong ulat sa PivotTable, maaari mong i-format ang talahanayan upang lumikha ng pangunahing interface. Sa Fig. Ipinapakita ng Figure 6 ang ulat ng PivotTable pagkatapos mag-format.
Pag-edit ng Recorded Macro
Kapag nag-record ka ng macro, gagawa ang Excel ng module na nag-iimbak ng mga aksyon na iyong ginawa. Ang lahat ng naitalang aksyon ay kinakatawan ng mga linya ng VBA code na bumubuo sa macro. Maaari kang magdagdag ng iba't ibang functionality sa iyong mga ulat sa PivotTable sa pamamagitan ng pag-customize ng iyong VBA code upang makagawa ng mga resultang gusto mo. Upang gawing mas madaling maunawaan kung paano gumagana ang lahat ng ito, gumawa tayo ng bagong macro na nagpapakita ng unang limang tala ng customer. Pumunta sa tab Developer at mag-click sa pindutan Mag-record ng macro. Magbubukas ang dialog box na ipinapakita sa Figure. 7. Pangalanan ang macro na iyong ginagawa UnaMga customer at ipahiwatig ang lokasyon ng pag-save Ang librong ito. I-click OK upang simulan ang pag-record ng macro.
Kapag nagsimula ka nang mag-record, mag-click sa arrow sa tabi ng field Pangalan ng customer, piliin I-filter ayon sa halaga at opsyon Unang 10(Larawan 8a). Sa lalabas na dialog box, itakda ang mga setting tulad ng ipinapakita sa Fig. 8b. Isinasaad ng mga setting na ito na ipakita ang data ng limang kliyente na may pinakamahuhusay na dami ng benta. I-click OK.
kanin. 8. Piliin ang filter (a) at ayusin ang mga opsyon (b) upang ipakita ang nangungunang limang customer ayon sa mga benta
Pagkatapos na matagumpay na maitala ang lahat ng mga hakbang na kinakailangan upang kunin ang nangungunang limang lead sa pagbebenta, pumunta sa tab Developer at mag-click sa pindutan Itigil ang pagre-record.
Mayroon ka na ngayong macro na magpi-filter sa pivot table para kunin ang nangungunang limang sales account. Kinakailangang gawing reaksyon ang macro sa estado ng scrollbar, i.e. Gamit ang scroll bar, kailangan mong masabi sa macro ang bilang ng mga kliyente na ang data ay ipapakita sa ulat ng pivot table. Kaya, gamit ang scroll bar, magagawa ng user na makuha ang nangungunang limang, nangungunang walo, o nangungunang tatlumpu't dalawang kliyente sa kanilang paghuhusga.
Upang magdagdag ng scrollbar sa iyong spreadsheet, i-click ang Developer, mag-click sa pindutan Ipasok, pumili ng kontrol sa palette Scroll bar at ilagay ito sa iyong worksheet. Mag-right click sa control Scroll bar Format ng Bagay. Magbubukas ang isang dialog box Format ng Kontrol(Larawan 9). Sa loob nito, gawin ang mga sumusunod na pagbabago sa mga setting: parameter Pinakamababang halaga magtalaga ng value 1 sa parameter Pinakamataas na halaga- halaga 200, at sa field Cell Communication Ipasok ang $M$2 upang ipakita ang halaga ng scroll bar sa cell M2. I-click ang button OK upang ilapat ang naunang tinukoy na mga setting.
Ngayon ay kailangan mong itugma ang kamakailang naitala na macro UnaMga customer may control element Scroll bar matatagpuan sa worksheet. Mag-right click sa control Scroll bar at sa menu ng konteksto piliin ang utos Magtalaga ng macro para buksan ang macro assignment dialog box. Magtalaga ng naka-record na macro sa scrollbar Mga customer ng FirstN. Ang macro ay isasagawa sa tuwing mag-click ka sa scroll bar. Subukan ang ginawang scrollbar. Ang pag-click sa bar ay maglulunsad ng macro Mga customer ng FirstN at ang numero sa cell M2 ay magbabago upang ipakita ang estado ng scroll bar. Ang numero sa cell M2 ay mahalaga dahil ginagamit ito upang itali ang macro sa scrollbar.
Ang tanging bagay na natitira upang gawin ay gawin ang macro process ang numero sa cell M2 sa pamamagitan ng pag-uugnay nito sa scrollbar. Upang gawin ito, kailangan mong pumunta sa VBA code ng macro. Upang gawin ito, pumunta sa tab Developer at mag-click sa pindutan Mga macro. Magbubukas ang isang dialog box Macro(Larawan 10). Dito maaari mong patakbuhin, tanggalin at i-edit ang napiling macro. Upang ipakita ang VBA code ng isang macro sa screen, piliin ang macro at i-click ang button Baguhin.
kanin. 10. Upang ma-access ang VBA code ng macro UnaMga customer, piliin ang macro at i-click ang button Baguhin
Lalabas sa screen ang isang Visual Basic editor window na may VBA macro code (Fig. 11). Ang iyong layunin ay palitan ang hard-coded na numero 5, na itinakda kapag ang macro ay naitala, na may halaga sa cell M2, na nakatali sa scrollbar. Sa una, isang macro ang naitala upang ipakita ang unang limang customer na may pinakamataas na kita.
Alisin ang numero 5 mula sa code at sa halip ay ilagay ang sumusunod na expression:
ActiveSheet.Range("M2 ").Value
Magdagdag ng dalawang linya sa simula ng macro para i-clear ang mga filter:
Saklaw(" A4 "). Piliin
ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Pangalan ng Customer ").ClearAllFilters
Ngayon ang macro code ay dapat magmukhang ipinapakita sa Fig. 12.
Isara ang Visual Basic Editor at bumalik sa ulat ng PivotTable. Subukan ang scroll bar sa pamamagitan ng pag-drag sa slider sa 11. Dapat tumakbo ang macro at i-filter ang nangungunang 11 account sa pagbebenta.
I-sync ang dalawang pivot table gamit ang isang drop-down list
Ang ulat na ipinapakita sa Fig. 13 ay naglalaman ng dalawang talahanayan ng buod. Ang bawat isa sa kanila ay may isang patlang ng pahina na nagbibigay-daan sa iyo upang pumili ng isang merkado ng pagbebenta. Ang problema ay sa tuwing pipili ka ng market sa field ng mga page ng isang pivot table, kailangan mong piliin ang parehong market sa field ng mga page ng isa pang pivot table. Ang pag-synchronize ng mga filter sa pagitan ng dalawang talahanayan sa panahon ng yugto ng pagsusuri ng data ay hindi isang malaking problema, ngunit may posibilidad na makalimutan mo o ng iyong mga kliyente na gawin ito.
kanin. 13. Ang dalawang pivot table ay naglalaman ng mga page field na nag-filter ng data ayon sa market. Upang suriin ang data para sa iisang market, kailangan mong i-synchronize ang parehong pivot table
Ang isang paraan upang panatilihing naka-sync ang mga pivot table na ito ay ang paggamit ng drop-down list. Ang ideya ay mag-record ng macro na pumipili ng gustong market mula sa field Market ng pagbebenta sa magkabilang mesa. Pagkatapos ay kailangan mong lumikha ng isang drop-down na listahan at punan ito ng mga pangalan ng mga merkado mula sa dalawang pivot table. Sa wakas, ang naitala na macro ay dapat mabago upang i-filter ang parehong pivot table gamit ang mga halaga mula sa drop-down na listahan. Upang malutas ang problemang ito kailangan mong gawin ang mga sumusunod:
1. Gumawa ng bagong macro at bigyan ito ng pangalan SynchMarkets. Kapag nagsimula ang pagre-record, piliin sa field Sales market para sa dalawa mga talahanayan ng buod ng merkado ng pagbebenta California at ihinto ang pagre-record ng macro.
2. Ipakita ang palette ng Form Controls at magdagdag ng drop-down list sa worksheet.
3. Gumawa ng hardcoded na listahan ng lahat ng market sa pivot table. Pakitandaan na ang unang elemento ng listahan ay ang halaga (Lahat). Dapat mong paganahin ang elementong ito kung gusto mong mapili ang lahat ng mga market mula sa drop-down na listahan.
4. Sa puntong ito, ang ulat ng PivotTable ay dapat magmukhang katulad ng ipinapakita sa Figure. 14.
kanin. 14. Nasa iyo ang lahat ng kinakailangang tool: isang macro na nagbabago sa field Market ng pagbebenta parehong pivot table, isang drop-down list at isang listahan ng lahat ng mga sales market na nasa pivot table
5. Mag-right-click sa drop-down na listahan at piliin ang command mula sa menu ng konteksto Format ng Bagay upang i-configure ang kontrol.
6. Una, itakda ang paunang hanay ng mga halaga na ginamit upang i-populate ang drop-down na listahan, tulad ng ipinapakita sa Figure. 15. Sa kasong ito, pinag-uusapan natin ang listahan ng mga merkado ng benta na ginawa mo sa hakbang 3. Pagkatapos ay ipahiwatig ang cell na nagpapakita ng serial number ng napiling elemento (sa halimbawang ito, ito ay cell H1). Parameter Bilang ng mga linya ng listahan tinutukoy kung gaano karaming mga hilera ang ipapakita nang sabay-sabay sa drop-down na listahan. I-click ang button OK.
kanin. 15. Ang mga setting ng drop-down na listahan ay dapat tumuro sa listahan ng mga merkado ng pagbebenta bilang paunang hanay ng mga halaga, at tukuyin ang cell H1 bilang anchor point
7. Ngayon ay mayroon kang pagkakataon na pumili ng isang merkado ng pagbebenta sa drop-down na listahan, pati na rin matukoy ang serial number na nauugnay dito sa cell H1 (Fig. 16). Ang tanong ay lumitaw: bakit ang halaga ng index nito ay ginagamit sa halip na ang tunay na pangalan ng merkado? Dahil ang listahan ng dropdown ay hindi nagbabalik ng isang pangalan, ngunit isang numero. Halimbawa, kung pipiliin mo ang California mula sa drop-down na listahan, lalabas ang value 5 sa cell H1 Nangangahulugan ito na ang California ang ikalimang item sa listahan.
kanin. 16. Ang drop-down list ay napuno na ngayon ng mga pangalan ng mga market, at ang serial number ng napiling market ay ipinapakita sa cell H1
8. Upang gumamit ng sequence number sa halip na isang market name, dapat mong ipasa ito gamit ang INDEX function.
9. Ipasok ang function na INDEX, na nagko-convert ng serial number mula sa cell H1 sa isang makabuluhang halaga.
10. Ang INDEX function ay tumatagal ng dalawang argumento. Ang unang argumento ay kumakatawan sa hanay ng mga halaga ng listahan. Sa karamihan ng mga kaso, gagamitin mo ang parehong hanay na pumupuno sa dropdown na menu. Ang pangalawang argumento ay ang sequence number. Kung ang serial number ay ipinasok sa isang cell (halimbawa, sa cell H1, tulad ng sa Fig. 17), maaari kang sumangguni lamang sa cell na ito.
kanin. 17. Kino-convert ng INDEX function sa cell I1 ang sequence number na nakaimbak sa cell H1 sa isang value. Gagamitin mo ang halaga sa cell I1 upang baguhin ang macro
11. I-edit ang macro SynchMarkets, gamit ang value sa cell I1 sa halip na ang hardcoded na halaga. Pumunta sa tab Developer at mag-click sa pindutan Mga macro. Ang dialog box na ipinapakita sa Fig. 1 ay lalabas sa screen. 18. Pumili ng macro mula dito SynchMarkets at i-click ang Change button.
kanin. 18. Upang ma-access ang VBA code ng macro, piliin ang macro SynchMarkets at i-click Baguhin
12. Kapag nagre-record ng macro, pinili mo ang merkado ng pagbebenta ng California mula sa field sa parehong pivot table Market ng pagbebenta. Tulad ng makikita mula sa Fig. 19, ang merkado ng California ay naka-hardcode na ngayon sa VBA macro code.
13. Palitan ang value na "California" ng expression na Activesheet.Range("I1").Value, na tumutukoy sa value sa cell I1. Sa yugtong ito, ang macro code ay dapat magmukhang ipinapakita sa Fig. 20. Pagkatapos baguhin ang macro, isara ang Visual Basic Editor at bumalik sa spreadsheet.
kanin. 20. Palitan ang value na "California" ng ActiveSheet.Range("I1").Value at isara ang Visual Basic Editor
14. Ang natitira na lang ay upang matiyak na ang macro ay naisakatuparan kapag pumipili ng market ng pagbebenta sa drop-down list. Mag-right click sa dropdown at piliin ang opsyon Magtalaga ng macro. Pumili ng macro SynchMarket at mag-click sa pindutan OK.
15. Itago ang mga row at column ng field ng page sa mga pivot table, pati na rin ang listahan ng mga market at index formula na iyong ginawa.
Sa Fig. Ipinapakita ng Figure 21 ang huling resulta. Mayroon ka na ngayong user interface na nagbibigay-daan sa mga customer na pumili ng market sa parehong pivot table gamit ang isang drop-down list.
Kapag pumili ka ng bagong item mula sa drop-down na listahan, awtomatikong magre-resize ang mga column para ma-accommodate ang lahat ng data na ipinapakita nila. Ang pag-uugali na ito ng programa ay medyo nakakainis kapag nag-format ng isang template ng worksheet. Maiiwasan mo ito sa pamamagitan ng pag-right click sa pivot table at pagpili Mga Pagpipilian sa PivotTable. Ang isang dialog box na may parehong pangalan ay lilitaw sa screen, kung saan kailangan mong i-reset ang checkbox Awtomatikong baguhin ang mga lapad ng column sa pag-update.
Ang tala ay isinulat batay sa aklat ni Jelen, Alexander. . Kabanata 12.
Upang gawin ito, kailangan mong buksan ang Visual Basic Editor (key combination na "Alt+F11" o i-right click sa shortcut ng anumang sheet at piliin ang "Source Text" o sa "Code" na grupo ng tab na "Developer" na menu. , mag-click sa item na "Visual Basic") ) at magpasok ng isang karaniwang module sa proyekto. At magdagdag ng dalawang program code sa modyul na ito (tingnan ang Larawan 8.) - at .
|
Dim sheet Bilang Worksheet Dim cell Bilang Saklaw Gamit ang ActiveWorkbook Para sa Bawat sheet Sa ActiveWorkbook.Worksheets Itakda ang cell = Worksheet(1).Cells(sheet.Index, 1) Worksheet(1).Hyperlinks.Add anchor:=cell, Address:="", cell.Formula = sheet.Pangalan |
Paano i-optimize ang pagpili ng maraming mga halaga ng paghahanap mula sa iba't ibang mga talahanayan sa Excel
Maaari mong i-optimize ang pagpili ng ilang mga nais na halaga (mga halaga ng pagbabayad para sa iba't ibang mga supplier) ayon sa isang parameter (halimbawa, ayon sa petsa) mula sa isang maliit na bilang ng mga talahanayan gamit ang halos parehong pamamaraan tulad ng na may pagkakaiba lamang na ang sheet na "Talaan ng Mga Nilalaman ng Aklat" ay magpapakita ng isang listahan ng mga pangalan ng mga talahanayan na kalahok sa pagpili at mga resulta nito, at bilang karagdagan, isang bahagyang naiibang formula sa paghahanap ng talahanayan ang gagamitin:
"=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)", kung saan:
- cell C1 (quarter number) - nagtatakda ng halaga ng parameter;
- “INDIRECT(A2)” – tumutukoy sa isang text link sa isang pinangalanang hanay, ang pangalan nito ay nasa cell A2;
- Ang “2” ay ang column number ng source table ng mga supplier, na naglalaman ng mga halaga ng pagbabayad na kailangan namin;
- "FALSE" (maaaring palitan ng 0) - nagpapahiwatig sa VLOOKUP function na kailangan ng eksaktong tugma.
