Ang Mga Dynamic na Array ay ang pinakamalaking pagbabago sa mga formula ng Excel sa mga taon. Siguro ang pinakamalaking pagbabago kailanman. Ito ay dahil sa pinapayagan ka ng Mga Dynamic na Aray na madali kang gumana sa maraming mga halaga nang sabay sa isang formula. Para sa maraming mga gumagamit, ito ang magiging unang pagkakataon na nauunawaan nila at gumagamit ng mga formula ng array.
Ito ay isang malaking pag-upgrade at maligayang pagdating sa pagbabago. Malulutas ng mga Dynamic na Aray ang ilang talagang mahirap na mga problema sa Excel, at panimulang baguhin ang paraan ng pagdisenyo at pagbuo ng mga worksheet.
Pagkakaroon
Ang mga Dynamic na array at ang mga bagong pag-andar sa ibaba ay magagamit lamang Excel 365 . Ang Excel 2016 at Excel 2019 ay hindi nag-aalok ng suporta sa dynamic na formula formula. Para sa kaginhawaan, gagamitin ko ang 'Dynamic Excel' (Excel 365) at 'Tradisyonal na Excel' (2019 o mas maaga) upang maiba ang mga bersyon sa ibaba.
Bago: Pagsasanay sa video ng Dynamic Array Formula
Mga bagong pagpapaandar
Bilang bahagi ng pabago-bagong pag-update ng array, nagsasama ngayon ang Excel ng 8 mga bagong pag-andar na direktang magagamit ng mga dynamic na array upang malutas ang mga problema na ayon sa kaugalian ay mahirap malutas sa maginoo na mga formula. I-click ang mga link sa ibaba para sa mga detalye at halimbawa para sa bawat pagpapaandar:
| Pag-andar | Layunin |
|---|---|
| Saringan | Salain ang data at ibalik ang mga tala ng pagtutugma |
| RANDARRAY | Bumuo ng hanay ng mga random na numero |
| SUSUNOD | Bumuo ng hanay ng mga sunud-sunod na numero |
| PAGSUSURI | Pagbukud-bukurin ang hanay ayon sa haligi |
| SORTBY | Pagbukud-bukurin ang saklaw ng isa pang saklaw o array |
| NATATANGING | Kumuha ng mga natatanging halaga mula sa isang listahan o saklaw |
| XLOOKUP | Modernong kapalit ng VLOOKUP |
| XMATCH | Modernong kapalit para sa pagpapaandar ng MATCH |
Video: Mga bagong pag-andar ng array na array sa Excel (mga 3 minuto).
paano i-format ang petsa sa excel
Tandaan: XLOOKUP at XMATCH wala sa orihinal na pangkat ng mga bagong pag-andar ng array, ngunit mahusay ang pagpapatakbo ng mga ito sa bagong makina ng engine ng array. Pinalitan ng XLOOKUP ang VLOOKUP at nag-aalok ng isang moderno, kakayahang umangkop na diskarte na sinasamantala ang mga arrays. Ang XMATCH ay isang pag-upgrade sa pagpapaandar ng MATCH, na nagbibigay ng mga bagong kakayahan sa INDEX at MATCH mga pormula
Halimbawa
Bago namin makuha ang mga detalye, tingnan natin ang isang simpleng halimbawa. Sa ibaba ginagamit namin ang bago Natatanging pagpapaandar upang makuha ang mga natatanging halaga mula sa saklaw na B5: B15, na may a walang asawa pormula na ipinasok sa E5:
= UNIQUE (B5:B15) // return unique values in B5:B15
Ang resulta ay isang listahan ng limang natatanging mga pangalan ng lungsod, na lilitaw sa E5: E9.
Tulad ng lahat ng mga formula, awtomatikong maa-update ang UNIQUE kapag nagbago ang data. Sa ibaba, pinalitan ng Vancouver ang Portland sa hilera 11. Kasama sa resulta mula sa UNIQUE ang Vancouver:
Spilling - isang formula, maraming mga halaga
Sa Dynamic Excel, ang mga formula na magbabalik ng maraming halaga ay ' laro 'ang mga halagang ito nang direkta papunta sa worksheet. Agad na magiging mas lohikal ito sa mga gumagamit ng pormula. Ito rin ay isang ganap na pabagu-bagong pag-uugali - kapag nagbago ang pinagmulan ng data, agad na mai-update ang mga natapon na resulta.
Ang rektanggulo na nakapaloob sa mga halaga ay tinawag na ' saklaw ng laro '. Mapapansin mo na ang saklaw ng spill ay may espesyal na pag-highlight. Sa natatanging halimbawa sa itaas, ang saklaw ng spill ay E5: E10.
Kapag nagbago ang data, ang saklaw ng spill ay lalawak o makakontrata kung kinakailangan. Maaari kang makakita ng mga bagong halagang idinagdag, o nawawala ang mga umiiral na halaga. Sa ganitong paraan, ang isang saklaw ng pagbagsak ay isang bagong uri ng pabagu-bagong saklaw.
Tandaan: kapag ang pag-spilling ay na-block ng iba pang data, makakakita ka ng isang error na # SPILL. Kapag gumawa ka ng puwang para sa saklaw ng spill, ang formula ay awtomatikong bubo.
Video: Spilling at ang saklaw ng spill
Sanggunian ng saklaw ng spill
Upang mag-refer sa isang saklaw ng spill, gumamit ng isang hash na simbolo (#) pagkatapos ng unang cell sa saklaw. Halimbawa, upang sanggunian ang mga resulta mula sa pag-andar ng UNIQUE sa itaas na ginagamit:
=E5# // reference UNIQUE results
Ito ay kapareho ng pagsangguni sa buong saklaw ng pagbagsak, at makikita mo ang syntax na ito kapag sumulat ka ng isang pormula na tumutukoy sa isang kumpletong saklaw ng spill.
Maaari mong pakainin nang direkta ang isang sanggunian sa saklaw ng spill sa iba pang mga formula. Halimbawa, upang mabilang ang bilang ng mga lungsod na ibinalik ng UNIQUE, maaari mong gamitin ang:
= COUNTA (E5#) // count unique cities
Kapag nagbago ang saklaw ng spill, isasalamin ng formula ang pinakabagong data.
Napakalaking pagpapasimple
Ang pagdaragdag ng mga bagong formula ng pabagu-bagong array ay nangangahulugang ang ilang mga formula ay maaaring pasimplehin na pinasimple. Narito ang ilang mga halimbawa:
- I-extract at ilista ang mga natatanging halaga ( dati pa | pagkatapos )
- Bilangin ang mga natatanging halaga ( dati pa | pagkatapos )
- Salain at kunin ang mga tala ( dati pa | pagkatapos )
- I-extract ang bahagyang mga tugma ( dati pa | pagkatapos )
Ang lakas ng isa
Ang isa sa pinakamakapangyarihang benepisyo ng 'isang pormula, maraming halaga' na diskarte ay hindi gaanong umaasa ganap o magkakahalo mga sanggunian Bilang isang pabuya ng formula ng array ay nagbubuhos ng mga resulta sa worksheet, ang mga sanggunian ay mananatiling hindi nagbabago, ngunit ang formula ay bumubuo ng wastong mga resulta.
Halimbawa, sa ibaba ginagamit namin ang pag-andar ng FILTER upang kumuha ng mga tala sa pangkat na 'A'. Sa cell F5, isang solong pormula ang ipinasok:
= FILTER (B5:D11,B5:B11='a') // references are relative
Pansinin ang parehong mga saklaw ay naka-unlock na mga kamag-anak na sanggunian, ngunit perpektong gumagana ang formula.
Ito ay isang malaking pakinabang para sa maraming mga gumagamit, sapagkat ginagawang mas simple ang proseso ng pagsulat ng mga formula. Para sa isa pang magandang halimbawa, tingnan ang talahanayan ng pagpaparami sa ibaba.
Mga pagpapaandar sa kadena
Ang mga bagay ay talagang naging kawili-wili kapag magkasama ka sa higit sa isang pag-andar ng array. Marahil nais mong ayusin ang mga resulta na ibinalik ng UNIQUE? Madali. Balutan lang ang SORT function sa paligid ng UNIQUE function na tulad nito:
Tulad ng dati, kapag nagbago ang data ng mapagkukunan, awtomatikong lilitaw ang mga bagong natatanging resulta, maayos na pinagsunod-sunod.
Ugali ng katutubong
Mahalagang maunawaan na ang pag-uugali ng pabagu-bagong array ay isang katutubong at malalim na isinama . Kailan kahit ano Nagbabalik ang formula ng maraming mga resulta, ang mga resulta ay bubuhos sa maraming mga cell sa worksheet. Kasama dito ang mas matandang mga pag-andar na hindi orihinal na idinisenyo upang gumana sa mga pabagu-bagong array.
Halimbawa, sa Tradisyunal na Excel, kung ibibigay namin ang Pag-andar ng LEN sa saklaw ng mga halaga ng teksto, makikita natin ang a walang asawa resulta Sa Dynamic Excel, kung bibigyan namin ang pag-andar ng LEN ng isang saklaw ng mga halaga, makikita natin maramihang mga resulta Ipinapakita ng screen na ito sa ibaba ang dating pag-uugali sa kaliwa at ang bagong pag-uugali sa kanan:
Ito ay isang malaking pagbabago na maaaring makaapekto sa lahat ng uri ng mga formula. Halimbawa, ang Pag-andar ng VLOOKUP ay idinisenyo upang makuha ang isang solong halaga mula sa isang talahanayan, gamit ang isang index ng haligi. Gayunpaman, sa Dynamic Excel, kung bibigyan namin ang VLOOKUP ng higit sa isang haligi ng index gamit ang isang pare-pareho ang array ganito:
= VLOOKUP ('jose',F7:H10,{1,2,3},0)
Ibabalik ng VLOOKUP ang maraming mga haligi:
Sa madaling salita, kahit na ang VLOOKUP ay hindi kailanman dinisenyo upang ibalik ang maraming halaga, maaari mo na ito gawin ngayon, salamat sa bagong formula engine sa Dynamic Excel.
Lahat ng formula
Panghuli, tandaan na gumagana ang mga dynamic na array lahat ng formula hindi lang pagpapaandar . Sa halimbawa sa ibaba ang cell C5 ay naglalaman ng isang solong pormula:
kung paano makahanap ng pagkakaiba sa porsyento sa excel
=B5:B14*C4:L4
Ang resulta ay bubo sa isang 10 hanggang 10 na saklaw na nagsasama ng 100 mga cell:
Tandaan: Sa Tradisyunal na Excel, makakakita ka ng maraming mga resulta na ibinalik sa pamamagitan ng array formula kung ikaw gamitin ang F9 upang siyasatin ang pormula . Ngunit maliban kung papasok ka sa formula bilang a formula ng multi-cell array , isang halaga lamang ang ipapakita sa worksheet.
Ang mga array ay punong mainstream
Gamit ang paglulunsad ng mga dynamic na array, ang salitang ' array 'ay mag-pop up nang mas madalas. Sa katunayan, maaari mong makita ang 'array' at 'saklaw' na ginamit halos palitan. Makakakita ka ng mga array sa Excel na nakapaloob sa mga kulot na tirante tulad nito:
{1,2,3} // horizontal array {123} // vertical array
Ang Array ay isang termino para sa pagprograma na tumutukoy sa isang listahan ng mga item na lilitaw sa isang partikular na pagkakasunud-sunod. Ang kadahilanan ng mga array ay madalas na lumalabas sa mga formula ng Excel ay maaari ang mga arrays perpektong ipahayag ang mga halaga sa isang saklaw ng mga cell .
Video: Ano ang isang array?
Naging mahalaga ang operasyon ng array
Dahil ang mga pormula ng Dynamic na Excel ay madaling gumana sa maraming mga halaga, ang mga pagpapatakbo ng array ay magiging mas mahalaga. Ang term na 'operasyon ng array' ay tumutukoy sa isang expression na nagpapatakbo ng isang lohikal na pagsubok o pagpapatakbo ng matematika sa isang array. Halimbawa, ang ekspresyon sa ibaba ay sumusubok kung ang mga halaga sa B5: B9 ay katumbas ng 'ca'
=B5:B9='ca' // state = 'ca'
dahil mayroong 5 mga cell sa B5: B9, ang resulta ay 5 TRUE / FALSE na halaga sa isang array:
{FALSETRUEFALSETRUETRUE}
Ang operasyon ng array sa ibaba ay sumusuri para sa mga halagang higit sa 100:
=C5:C9>100 // amounts > 100
Pinagsasama ng huling operasyon ng array ang pagsubok A at pagsubok B sa isang solong pagpapahayag:
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100
Tandaan: Awtomatikong pinipilit ng Excel ang TUNAY at MALI na mga halaga sa 1 at 0 sa panahon ng pagpapatakbo ng matematika.
Upang maibalik ito sa mga formula ng pabagu-bagong array sa Excel, ipinapakita ng halimbawa sa ibaba kung paano namin magagamit ang eksaktong parehong operasyon ng array sa loob ng pag-andar ng FILTER bilang isama argumento:
Ibinabalik ng FILTER ang dalawang mga tala kung saan estado = 'ca' at halagang> 100.
Para sa isang demonstrasyon, tingnan ang: Paano mag-filter gamit ang dalawang pamantayan (video)
Bago at lumang mga formula ng array
Sa Dynamic Excel, hindi na kailangang ipasok ang mga formula ng array na may control + shift + enter. Kapag nilikha ang isang pormula, sinusuri ng Excel kung ang formula ay maaaring magbalik ng maraming mga halaga. Kung gayon, awtomatiko itong mai-save bilang isang dynamic na formula sa pag-array, ngunit hindi mo makikita ang mga kulot na brace. Ang halimbawa sa ibaba ay nagpapakita ng isang tipikal na formula ng array na ipinasok sa Dynamic Excel:
Kung bubuksan mo ang parehong pormula sa Tradisyunal na Excel, makakakita ka ng mga kulot na tirante:
Pagpunta sa iba pang direksyon, kapag binuksan ang isang 'tradisyonal' na formula sa pag-array sa Dynamic Excel, makikita mo ang mga kulot na tirante sa formula bar. Halimbawa, ang screen sa ibaba ay nagpapakita ng isang simpleng formula sa array sa Tradisyunal na Excel:
Gayunpaman, kung muling ipinasok mo ang formula na walang mga pagbabago, ang mga kulot na brace ay aalisin, at ang formula ay nagbabalik ng parehong resulta:
Sa ilalim na linya ay ang mga formula ng array na ipinasok na may control + shift + enter (CSE) na gumagana pa rin upang mapanatili ang pagiging tugma, ngunit hindi mo kailangang ipasok ang mga formula ng array sa CSE sa Dynamic Excel.
Ang @ character
Sa pagpapakilala ng mga pabago-bagong array, makikita mo ang character na @ na lumilitaw nang mas madalas sa mga formula. Nagbibigay-daan ang character na @ sa isang pag-uugali na kilala bilang ' implicit intersection '. Ang implicit intersection ay isang lohikal na proseso kung saan maraming halaga ang nabawasan sa isang halaga.
Sa Tradisyunal na Excel, ang implicit intersection ay isang tahimik na pag-uugali na ginamit (kung kinakailangan) upang mabawasan ang maraming halaga sa isang solong resulta sa isang cell. Sa Dynamic Excel, hindi ito karaniwang kinakailangan, dahil maraming mga resulta ang maaaring mapunta sa worksheet. Kapag kinakailangan ito, ang implicit intersection ay manu-manong na-invoke gamit ang @ character.
Kapag ang pagbubukas ng mga spreadsheet ay lumikha ng isang mas lumang bersyon ng Excel, maaari mong makita ang character na @ na awtomatikong idinagdag sa mga mayroon nang mga formula na mayroon ang potensyal upang ibalik ang maraming mga halaga. Sa Tradisyunal na Excel, ang isang formula na nagbabalik ng maraming halaga ay hindi bubuhos sa worksheet. Pinipilit ng character na @ ang parehong pag-uugali na ito sa Dynamic Excel upang ang formula ay kumilos sa parehong paraan at ibabalik ang parehong resulta tulad ng ginawa nito sa orihinal na bersyon ng Excel.
Sa madaling salita, idinagdag ang @ upang maiwasan ang isang mas matandang pormula mula sa pagbubuhos ng maraming mga resulta sa worksheet. Nakasalalay sa formula, maaari mong alisin ang character na @ at ang pag-uugali ng formula ay hindi magbabago.
Buod
- Ang mga Dynamic na Array ay gagawing mas madaling isulat ang ilang mga formula.
- Maaari mo na ngayong mai-filter ang pagtutugma ng data, pag-uri-uriin, at kunin ang mga natatanging halaga nang madali sa mga formula.
- Ang mga pormula ng Dynamic na Array ay maaaring ikadena (pugad) upang gawin ang mga bagay tulad ng filter at pag-uuri.
- Ang mga formula na nagbabalik ng higit sa isang halaga ay awtomatikong magbubuhos.
- Hindi kinakailangan na gumamit ng Ctrl + Shift + Enter upang maglagay ng isang formula sa array.
- Magagamit lamang ang mga Dynamic na formula ng array sa Excel 365.
