Kaundan
Aduna ka bay mga lamesa nga adunay datos sa Excel nga mahimong usbon ang gidak-on, ie ang gidaghanon sa mga laray (kolum) mahimong modaghan o mokunhod sa dagan sa trabaho? Kung ang mga gidak-on sa lamesa "naglutaw", nan kinahanglan nimo nga kanunay nga bantayan kini nga higayon ug tul-iron kini:
- mga link sa mga pormula sa report nga nagtumong sa among lamesa
- inisyal nga mga han-ay sa mga pivot table nga gitukod sumala sa among lamesa
- inisyal nga mga han-ay sa mga tsart nga gitukod sumala sa among lamesa
- mga han-ay alang sa mga dropdown nga naggamit sa among lamesa isip tinubdan sa datos
Kining tanan sa kinatibuk-an dili nimo tugotan nga makalaay 😉
Mas sayon ug husto ang paghimo sa usa ka dinamikong "goma" nga hanay, nga awtomatiko nga mag-adjust sa gidak-on sa aktwal nga gidaghanon sa mga laray ug mga kolum sa datos. Aron mapatuman kini, adunay daghang mga paagi.
Pamaagi 1. Smart nga lamesa
I-highlight ang imong range sa mga cell ug pilia gikan sa tab Panimalay – Pormat isip Talaan (Balay – Pormat isip Talaan):
Kung dili nimo kinahanglan ang guhit nga disenyo nga gidugang sa lamesa ingon usa ka epekto, mahimo nimo kini i-off sa tab nga makita. Konstruktor (Disenyo). Ang matag lamesa nga gihimo niining paagiha makadawat usa ka ngalan nga mahimong pulihan sa usa ka labi ka kombenyente sa parehas nga lugar sa tab Konstruktor (Disenyo) sa uma Ngalan sa lamesa (Ngalan sa Talaan).
Karon makagamit na kami ug dinamikong mga link sa among "smart table":
- Table 1 – link sa tibuok lamesa gawas sa header row (A2:D5)
- Talaan 1[#Tanan] – link sa tibuok lamesa (A1:D5)
- Talaan 1[Pedro] – reference sa usa ka range-column nga wala ang unang cell-header (C2:C5)
- Talaan 1[#Headers] – link sa “header” nga adunay mga ngalan sa mga column (A1:D1)
Ang ingon nga mga pakisayran maayo kaayo sa mga pormula, pananglitan:
= SUM (Talaan 1[Moscow]) - pagkalkula sa kantidad alang sa kolum nga "Moscow"
or
=VPR(F5;Table 1;3;0) – pangitaa sa lamesa para sa bulan gikan sa cell F5 ug i-issue ang St. Petersburg sum para niini (unsa ang VLOOKUP?)
Ang ingon nga mga link mahimong malampuson nga magamit kung maghimo mga pivot table pinaagi sa pagpili sa tab Isuksok – Pivot Table (Isuksok – Pivot Table) ug pagsulod sa ngalan sa smart table isip tinubdan sa datos:
Kung magpili ka usa ka tipik sa ingon nga lamesa (pananglitan, ang una nga duha ka kolum) ug maghimo usa ka diagram sa bisan unsang klase, unya kung magdugang bag-ong mga linya, awtomatiko kini nga idugang sa diagram.
Kung maghimo og mga drop-down list, dili magamit ang direktang mga link sa mga elemento sa smart table, apan dali ka makalikay niini nga limitasyon gamit ang taktikal nga limbong - gamita ang function. KINAHANGLAN (INDIREKTO), nga naghimo sa teksto nga usa ka link:
Mga. ang usa ka link sa usa ka maalamon nga lamesa sa porma sa usa ka text string (sa mga marka sa kinutlo!) nahimo nga usa ka hingpit nga link, ug ang drop-down nga lista sa kasagaran nakasabut niini.
Pamaagi 2: Dynamic nga ginganlan nga range
Kung ang paghimo sa imong datos nga usa ka maalamon nga lamesa dili gusto sa usa ka hinungdan, nan mahimo nimong gamiton ang usa ka gamay nga labi ka komplikado, apan labi ka labi ka maliputon ug daghang gamit nga pamaagi - paghimo usa ka dinamikong gingalan nga range sa Excel nga nagtumong sa among lamesa. Dayon, sama sa kaso sa usa ka maalamon nga lamesa, mahimo nimong gawasnon nga gamiton ang ngalan sa gimugna nga hanay sa bisan unsang mga pormula, mga taho, mga tsart, ug uban pa. Magsugod kita sa usa ka yano nga pananglitan:
Task: paghimo ug dinamikong gihinganlan nga han-ay nga magtumong sa listahan sa mga siyudad ug awtomatik nga mag-inat ug mokunhod ang gidak-on kon magdugang ug bag-ong mga siyudad o magtangtang niini.
Magkinahanglan kami og duha ka built-in nga Excel function nga anaa sa bisan unsang bersyon − POICPOZ (PAGTABANG) aron mahibal-an ang katapusang cell sa range, ug INDEX (INDEX) sa paghimo sa usa ka dinamikong link.
Pagpangita sa katapusang cell gamit ang MATCH
MATCH(lookup_value, range, match_type) – usa ka function nga mangita sa gihatag nga value sa usa ka range (row o column) ug ibalik ang ordinal number sa cell diin kini nakit-an. Pananglitan, ang pormula nga MATCH("Marso"; A1:A5;0) ibalik ang numero 4 isip resulta, tungod kay ang pulong "Marso" nahimutang sa ikaupat nga selula sa kolum A1:A5. Ang katapusan nga argumento sa function Match_Type = 0 nagpasabut nga nangita kami usa ka eksaktong tugma. Kung kini nga argumento wala gitino, nan ang function mobalhin sa search mode alang sa labing duol nga pinakagamay nga kantidad - mao gyud kini ang mahimong malampuson nga magamit aron makit-an ang katapusan nga na-okupar nga cell sa among array.
Ang diwa sa lansis yano ra. MATCH pagpangita alang sa mga selula sa han-ay gikan sa ibabaw ngadto sa ubos ug, sa teoriya, kinahanglan nga mohunong sa diha nga kini makakaplag sa labing duol nga pinakagamay nga bili sa gihatag nga usa. Kung imong gipiho ang usa ka kantidad nga klaro nga labi ka dako kaysa bisan unsang magamit sa lamesa ingon ang gitinguha nga kantidad, nan ang MATCH moabut sa katapusan sa lamesa, wala’y makit-an ug ihatag ang sequence number sa katapusang napuno nga cell. Ug gikinahanglan nato kini!
Kung adunay mga numero lamang sa among laray, nan mahimo namon ipiho ang usa ka numero ingon ang gitinguha nga kantidad, nga klaro nga labi ka daghan kaysa sa bisan unsang naa sa lamesa:
Alang sa usa ka garantiya, mahimo nimong gamiton ang numero nga 9E + 307 (9 ka beses nga 10 hangtod sa gahum sa 307, ie 9 nga adunay 307 nga mga sero) - ang labing kadaghan nga numero nga magamit sa Excel sa prinsipyo.
Kung adunay mga kantidad sa teksto sa among kolum, nan ingon nga katumbas sa labing kadaghan nga posible nga numero, mahimo nimong isulud ang pagtukod REPEAT("i", 255) - usa ka string sa teksto nga gilangkuban sa 255 nga mga letra "i" - ang katapusan nga letra sa ang alpabeto. Tungod kay ang Excel aktuwal nga nagtandi sa mga code sa karakter sa pagpangita, bisan unsang teksto sa among lamesa mahimong "mas gamay" kay sa taas nga linya nga "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Paghimo ug link gamit ang INDEX
Karon nga nahibal-an na namon ang posisyon sa katapusan nga wala’y sulod nga elemento sa lamesa, kini nagpabilin nga usa ka link sa among tibuuk nga sakup. Alang niini among gigamit ang function:
INDEX(range; row_num; column_num)
Naghatag kini sa mga sulod sa cell gikan sa range pinaagi sa row ug column number, pananglitan, ang function =INDEX(A1:D5;3;4) sa among lamesa nga adunay mga siyudad ug mga bulan gikan sa miaging pamaagi maghatag og 1240 – ang sulod gikan sa 3rd row ug 4th column, ie cells D3. Kung adunay usa ra ka kolum, nan ang numero niini mahimong tangtangon, ie formula INDEX(A2:A6;3) maghatag "Samara" sa katapusang screenshot.
Ug adunay usa nga dili kaayo klaro nga nuance: kung ang INDEX dili lang gisulod sa cell pagkahuman sa = sign, sama sa naandan, apan gigamit ingon ang katapusan nga bahin sa paghisgot sa range pagkahuman sa colon, nan dili na kini maghatag. ang sulod sa selula, apan ang adres niini! Busa, ang pormula sama sa $A$2:INDEX($A$2:$A$100;3) maghatag ug reperensiya sa range A2:A4 sa output.
Ug kini diin ang MATCH function moabut, nga among gisulud sa sulod sa INDEX aron dinamikong mahibal-an ang katapusan sa lista:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“Ako”;255);A2:A100))
Paghimo og usa ka ginganlan nga range
Nagpabilin kini nga i-pack ang tanan sa usa ka tibuuk. Ablihi ang usa ka tab pormula (Mga pormula) Ug i-klik ang Pangalan sa Tigdumala (Ngalan Manager). Sa bintana nga nagbukas, i-klik ang buton Paghimo (bag-o nga), isulod ang among range name ug formula sa field range (Sumbanan):
Kini nagpabilin sa pag-klik sa OK ug ang andam nga hanay mahimong magamit sa bisan unsang mga pormula, drop-down nga mga lista o mga tsart.
- Gamit ang VLOOKUP function para i-link ang mga table ug lookup values
- Giunsa paghimo ang usa ka lista sa dropdown nga nag-auto-populate
- Giunsa paghimo ang usa ka pivot table aron pag-analisar sa daghang mga datos