Nilalaman
Mayroon ka bang mga talahanayan na may data sa Excel na maaaring baguhin ang laki, ibig sabihin, ang bilang ng mga hilera (column) ay maaaring tumaas o bumaba sa kurso ng trabaho? Kung ang laki ng talahanayan ay "lumulutang", pagkatapos ay kailangan mong patuloy na subaybayan ang sandaling ito at itama ito:
- mga link sa mga formula ng ulat na tumutukoy sa aming talahanayan
- mga paunang hanay ng mga pivot table na binuo ayon sa aming talahanayan
- mga paunang hanay ng mga chart na binuo ayon sa aming talahanayan
- mga hanay para sa mga dropdown na gumagamit ng aming talahanayan bilang data source
Ang lahat ng ito sa kabuuan ay hindi hahayaang magsawa 😉
Ito ay magiging mas maginhawa at tama upang lumikha ng isang dynamic na hanay ng "goma", na awtomatikong mag-aakma sa laki sa aktwal na bilang ng mga hilera at haligi ng data. Upang ipatupad ito, mayroong ilang mga paraan.
Paraan 1. Smart table
I-highlight ang iyong hanay ng mga cell at pumili mula sa tab Home – I-format bilang Talahanayan (Home – I-format bilang Talahanayan):
Kung hindi mo kailangan ang may guhit na disenyo na idinagdag sa talahanayan bilang side effect, maaari mo itong i-off sa tab na lalabas Tagabuo (Disenyo). Ang bawat talahanayan na ginawa sa ganitong paraan ay tumatanggap ng isang pangalan na maaaring palitan ng isang mas maginhawang isa sa parehong lugar sa tab Tagabuo (Disenyo) sa bukid Pangalan ng talahanayan (Pangalan ng Talahanayan).
Ngayon ay maaari na kaming gumamit ng mga dynamic na link sa aming "smart table":
- Table 1 – link sa buong table maliban sa header row (A2:D5)
- Talahanayan 1[#Lahat] – link sa buong talahanayan (A1:D5)
- Talahanayan 1[Peter] – reference sa hanay-column na walang unang cell-header (C2:C5)
- Talahanayan 1[#Headers] – link sa “header” na may mga pangalan ng column (A1:D1)
Ang ganitong mga sanggunian ay mahusay na gumagana sa mga formula, halimbawa:
= SUM (Talahanayan 1[Moscow]) - pagkalkula ng kabuuan para sa hanay na "Moscow"
or
=VPR(F5;Table 1;3;0) – maghanap sa talahanayan para sa buwan mula sa cell F5 at mag-isyu ng St. Petersburg sum para dito (ano ang VLOOKUP?)
Ang ganitong mga link ay maaaring matagumpay na magamit kapag lumilikha ng mga pivot table sa pamamagitan ng pagpili sa tab Insert – Pivot Table (Insert – Pivot Table) at paglalagay ng pangalan ng smart table bilang data source:
Kung pumili ka ng isang fragment ng naturang talahanayan (halimbawa, ang unang dalawang column) at lumikha ng isang diagram ng anumang uri, pagkatapos ay kapag nagdaragdag ng mga bagong linya, awtomatiko silang idaragdag sa diagram.
Kapag gumagawa ng mga drop-down na listahan, hindi magagamit ang mga direktang link sa mga elemento ng smart table, ngunit madali mong malalampasan ang limitasyong ito gamit ang isang taktikal na trick – gamitin ang function HINDI DIREKTA (INDIRECT), na ginagawang link ang teksto:
Yung. ang isang link sa isang matalinong talahanayan sa anyo ng isang text string (sa mga panipi!) ay nagiging isang ganap na link, at ang drop-down na listahan ay karaniwang nakikita ito.
Paraan 2: Dynamic na pinangalanang hanay
Kung hindi kanais-nais na gawing matalinong talahanayan ang iyong data sa ilang kadahilanan, maaari kang gumamit ng bahagyang mas kumplikado, ngunit mas banayad at maraming nalalaman na paraan - lumikha ng isang dynamic na pinangalanang hanay sa Excel na tumutukoy sa aming talahanayan. Pagkatapos, tulad ng sa kaso ng isang matalinong talahanayan, maaari mong malayang gamitin ang pangalan ng ginawang hanay sa anumang mga formula, ulat, chart, atbp. Magsimula tayo sa isang simpleng halimbawa:
Gawain: gumawa ng isang dynamic na pinangalanang hanay na tumutukoy sa isang listahan ng mga lungsod at awtomatikong mag-uunat at lumiliit sa laki kapag nagdaragdag ng mga bagong lungsod o tinatanggal ang mga ito.
Kakailanganin namin ang dalawang built-in na Excel function na magagamit sa anumang bersyon − POICPOZ (MATCH) upang matukoy ang huling cell ng hanay, at INDEX (INDEX) para gumawa ng dynamic na link.
Paghahanap ng huling cell gamit ang MATCH
MATCH(lookup_value, range, match_type) – isang function na naghahanap ng ibinigay na halaga sa isang hanay (row o column) at ibinabalik ang ordinal na numero ng cell kung saan ito natagpuan. Halimbawa, ang formula na MATCH("March";A1:A5;0) ay magbabalik ng numero 4 bilang resulta, dahil ang salitang "March" ay matatagpuan sa ikaapat na cell sa column A1:A5. Ang huling function argument na Match_Type = 0 ay nangangahulugang naghahanap kami ng eksaktong tugma. Kung hindi tinukoy ang argumentong ito, lilipat ang function sa mode ng paghahanap para sa pinakamalapit na pinakamaliit na halaga – ito mismo ang maaaring matagumpay na magamit upang mahanap ang huling na-occupy na cell sa aming array.
Ang kakanyahan ng lansihin ay simple. Ang MATCH ay naghahanap ng mga cell sa hanay mula sa itaas hanggang sa ibaba at, sa teorya, ay dapat huminto kapag nahanap nito ang pinakamalapit na pinakamaliit na halaga sa ibinigay na isa. Kung tumukoy ka ng value na halatang mas malaki kaysa sa anumang available sa talahanayan bilang gustong halaga, maaabot ang MATCH sa pinakadulo ng talahanayan, walang hahanapin at ibibigay ang sequence number ng huling napunong cell. At kailangan namin ito!
Kung mayroon lamang mga numero sa aming array, maaari naming tukuyin ang isang numero bilang ang nais na halaga, na malinaw na mas malaki kaysa sa alinman sa mga nasa talahanayan:
Para sa isang garantiya, maaari mong gamitin ang numerong 9E + 307 (9 beses 10 hanggang sa kapangyarihan ng 307, ibig sabihin, 9 na may 307 na mga zero) – ang maximum na bilang na maaaring gamitin ng Excel sa prinsipyo.
Kung mayroong mga halaga ng teksto sa aming column, kung gayon bilang katumbas ng pinakamalaking posibleng numero, maaari mong ipasok ang construction REPEAT(“i”, 255) – isang text string na binubuo ng 255 letrang “i” – ang huling titik ng ang alpabeto. Dahil ang Excel ay aktwal na naghahambing ng mga code ng character kapag naghahanap, anumang teksto sa aming talahanayan ay teknikal na magiging "mas maliit" kaysa sa isang mahabang linyang "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Bumuo ng link gamit ang INDEX
Ngayong alam na natin ang posisyon ng huling hindi-bakanteng elemento sa talahanayan, nananatili itong bumuo ng isang link sa aming buong hanay. Para dito ginagamit namin ang function:
INDEX(saklaw; row_num; column_num)
Nagbibigay ito ng mga nilalaman ng cell mula sa hanay ayon sa numero ng hilera at hanay, halimbawa, ang function na =INDEX(A1:D5;3;4) sa aming talahanayan na may mga lungsod at buwan mula sa nakaraang pamamaraan ay magbibigay ng 1240 – ang nilalaman mula sa ika-3 hilera at ika-4 na hanay, ibig sabihin, mga cell D3. Kung mayroon lamang isang column, maaaring tanggalin ang numero nito, ibig sabihin, ang formula na INDEX(A2:A6;3) ay magbibigay ng "Samara" sa huling screenshot.
At mayroong isang hindi lubos na halatang nuance: kung ang INDEX ay hindi lamang ipinasok sa cell pagkatapos ng = sign, gaya ng dati, ngunit ginagamit bilang huling bahagi ng reference sa hanay pagkatapos ng colon, pagkatapos ay hindi na ito nagbibigay ng out. ang mga nilalaman ng cell, ngunit ang address nito! Kaya, ang isang formula tulad ng $A$2:INDEX($A$2:$A$100;3) ay magbibigay ng reference sa hanay na A2:A4 sa output.
At dito papasok ang MATCH function, na ipinapasok namin sa loob ng INDEX para dynamic na matukoy ang dulo ng listahan:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“Ako”;255);A2:A100))
Lumikha ng pinangalanang hanay
Ito ay nananatiling i-pack ang lahat ng ito sa isang solong kabuuan. Magbukas ng tab pormula (Mga Formula) At i-click ang Pangalan ng Tagapamahala (Name Manager). Sa window na bubukas, i-click ang button Lumikha (bago), ilagay ang aming pangalan ng hanay at formula sa field Saklaw (Sanggunian):
Ito ay nananatiling mag-click sa OK at ang handa na hanay ay maaaring gamitin sa anumang mga formula, drop-down na listahan o chart.
- Gamit ang VLOOKUP function para i-link ang mga table at lookup value
- Paano gumawa ng listahan ng dropdown na awtomatikong napo-populate
- Paano gumawa ng pivot table para pag-aralan ang malaking halaga ng data