Contents
Di Excel de tabloyên we yên bi daneyan hene ku mezinahiya wan were guhertin, ango di dema xebatê de hejmara rêzan (stûnan) zêde bibe an kêm bibe? Ger mezinahiyên tabloyê "herikin", wê hingê hûn neçar in ku bi berdewamî vê gavê bişopînin û rast bikin:
- lînkên di formulên raporê de ku vedigerin tabloya me
- rêzikên destpêkê yên tabloyên pivot ku li gorî tabloya me hatine çêkirin
- rêzikên destpêkê yên nexşeyan li gorî tabloya me hatine çêkirin
- rêzikên ji bo daxistinan ku tabloya me wekî çavkaniyek daneyê bikar tînin
Ev hemî bi tevahî dê nehêle hûn aciz bibin 😉
Dê pir rehettir û rasttir be ku meriv navçeyek "rubber" ya dînamîkî biafirîne, ku dê bixweber mezinahiyê li gorî hejmara rastîn a rêz û stûnên daneyê rast bike. Ji bo pêkanîna vê, çend awayan hene.
Rêbaz 1. Tabloya Smart
Rêzeya hucreyên xwe ronî bikin û ji tabê hilbijêrin Mal - Format wekî Tablo (Hal - Format wekî Tablo):
Ger hewcedariya we bi sêwirana xerîdar a ku wekî bandorek alî li ser maseyê tê zêdekirin tune be, wê hingê hûn dikarin wê li ser tabloya ku xuya dike vekin. Çêker (Design). Her tabloyek ku bi vî rengî hatî çêkirin navek werdigire ku dikare li heman cîhê li ser tabloyê bi yekî rehettir were guheztin. Çêker (Design) li meydanê Navê maseyê (Navê maseyê).
Naha em dikarin girêdanên dînamîkî bi "maseya xweya jîr" re bikar bînin:
- Table 1 - Girêdana tevahiya tabloyê ji xeynî rêza sernavê (A2: D5)
- Tablo 1[#Hemû] - Girêdana tevahiya tabloyê (A1: D5)
- Tablo 1[Petrûs] - referansa rêz-stûnek bêyî sernavê şaneya yekem (C2: C5)
- Tablo 1[#Headers] - bi navên stûnan ve bi "sernivîsê" ve girêdin (A1: D1)
Referansên weha di formulan de pir baş dixebitin, mînakî:
= SUM (Tablo 1[Moskow]) - Hesabkirina berhevoka stûna "Moskow"
or
=VPR(F5;Table 1;3;0) - di tabloya mehê de ji hucreya F5 bigerin û ji bo wê berhevoka St. Petersburgê derxînin (VLOOKUP çi ye?)
Girêdanên weha dikarin bi serfirazî werin bikar anîn dema ku tabloyên pivot bi bijartina li ser tabê têne çêkirin Têxe - Tabloya Pivot (Têxe - Tabloya Pivot) û navê tabloya jîr wekî çavkaniya daneyê têkevin:
Ger hûn perçeyek ji tabloyek wusa hilbijêrin (mînak, du stûnên pêşîn) û nexşeyek ji her celebî biafirînin, wê hingê gava ku rêzikên nû lê zêde bikin, ew ê bixweber li şemayê werin zêdekirin.
Dema ku navnîşên dakêşan diafirînin, girêdanên rasterast bi hêmanên sifrê yên biaqil re nekarin werin bikar anîn, lê hûn dikarin bi hêsanî vê sînorkirinê bi karanîna hîleyek taktîkî derbas bikin - fonksiyonê bikar bînin NADIREKT (BNDXWNE), ku nivîsê vediguherîne girêdanek:
Ewan. girêdanek ji tabloyek jîr re di forma rêzika nivîsê de (di nav nîşanan de!) vediguhere girêdanek bêkêmasî, û navnîşa dakêşanê bi gelemperî wê dihese.
Rêbaz 2: Rêzeya bi navê dînamîk
Ger ji ber hin sedeman veguherandina daneyên xwe di tabloyek jîr de nexwestî ye, wê hingê hûn dikarin rêbazek piçek tevlihevtir, lê pir naziktir û pirrengtir bikar bînin - di Excel de navçeyek bi navê dînamîk a ku behsa tabloya me dike biafirînin. Dûv re, wekî di tabloyek jîr de, hûn dikarin bi serbestî navê qada çêkirî di her formul, rapor, nexşe û hwd de bikar bînin. Ka em bi mînakek hêsan dest pê bikin:
Karî: Rêzek bi navê dînamîk çêbikin ku navnîşek bajaran vedibêje û dema ku bajarên nû lê zêde bike an jêbirin wan bixweber mezinahiyê dirêj bike û piçûk bike.
Em ê hewceyê du fonksiyonên Excel-ê yên çêkirî yên ku di her guhertoyê de peyda dibin - POICPOZ (WEKWÎ) ji bo diyarkirina şaneya dawî ya rêzê, û NAVEROK (NAVEROK) ji bo afirandina girêdanek dînamîk.
Dîtina hucreya paşîn bi karanîna MATCH
MATCH(nirx_lêgerîn, rêjeyek, lihevhatina_cure) - fonksiyonek ku li nirxek diyarkirî di nav rêzek (rêz an stûnek) de digere û jimareya rêzî ya şaneya ku lê hatiye dîtin vedigerîne. Mînakî, formula MATCH("Adar";A1:A5;0) dê di encamê de hejmara 4 vegerîne, ji ber ku peyva "Adar" di şaneya çaremîn a stûna A1:A5 de cih digire. Argumenta fonksiyonê ya paşîn Match_Type = 0 tê vê wateyê ku em li hevberek rastîn digerin. Ger ev arguman neyê diyar kirin, wê hingê fonksiyon dê ji bo nirxa herî piçûktir veguhezîne moda lêgerînê - ev tam ya ku dikare bi serfirazî were bikar anîn da ku şaneya paşîn a dagirkirî di rêza me de bibîne.
Esasê hîleyê hêsan e. MATCH li hucreyên di rêzê de ji serî heta binî digere û, di teoriyê de, dema ku ew nirxa herî piçûk a nirxa hatî dayîn bibîne divê raweste. Ger hûn nirxek ku eşkere ji ya ku di tabloyê de peyda dibe mezintir e wekî nirxa xwestî destnîşan bikin, wê hingê MATCH dê bigihîje dawiya tabloyê, tiştek nebîne û jimareya rêza şaneya dagirtî ya dawî bide. Û em hewce ne!
Ger di rêzika me de tenê jimar hebin, wê hingê em dikarin hejmarek wekî nirxa xwestinê diyar bikin, ku eşkere ji yek ji yên di tabloyê de mezintir e:
Ji bo garantiyek, hûn dikarin hejmara 9E + 307 bikar bînin (9 car 10 bi hêza 307, ango 9 bi 307 sifir) - hejmara herî zêde ya ku Excel dikare di prensîbê de bixebite.
Ger di stûna me de nirxên nivîsê hebin, wê hingê wekî hevwateya jimara herî mezin a gengaz, hûn dikarin avakirina REPEAT ("i", 255) têxin - rêzek nivîsê ku ji 255 tîpan "i" pêk tê - tîpa dawî ya alfabe. Ji ber ku Excel di dema lêgerînê de bi rastî kodên karakteran dide ber hev, her metnek di tabloya me de dê ji hêla teknîkî ve ji rêzek wusa dirêj "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" "biçûktir" be:
Bi karanîna INDEX ve girêdanek çêbikin
Naha ku em pozîsyona hêmana paşîn a ne-vala di tabloyê de dizanin, ew dimîne ku em bi tevahî rêza xwe ve girêdanek çêbikin. Ji bo vê em fonksiyonê bikar tînin:
INDEX(range; rêz_hejmar; stûn_hejmar)
Ew naveroka hucreyê ji rêzê bi jimareya rêz û stûnê dide, ango, fonksiyona =INDEX(A1:D5;3;4) di tabloya me de bi bajar û mehan ji rêbaza berê dê 1240 bide - naverok. ji rêza 3. û stûna 4. ango şaneyên D3. Heger tenê stûnek hebe, wê hingê hejmara wê dikare were jêbirin, ango formula INDEX(A2:A6;3) di dîmena paşîn de "Samara" dide.
Û nuwazek ne bi tevahî eşkere heye: heke INDEX ne tenê piştî nîşana =-ê, wekî her carê bikeve hucreyê, lê wekî beşa dawîn a referansê ya rêza piştî kolonê were bikar anîn, wê hingê ew êdî dernakeve. naveroka hucreyê, lê navnîşana wê! Ji ber vê yekê, formulek mîna $A$2:INDEX($A$2:$A$100;3) dê di encam de referansek rêza A2:A4 bide.
Û li vir fonksiyona MATCH tê, ya ku em têxin hundurê INDEX-ê da ku bi dînamîk dawiya navnîşê diyar bikin:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("I";255);A2:A100))
Rêzek bi navê ava bikin
Ew dimîne ku ew hemî di yek yekane de pak bikin. Tabloyek vekin formîl (Formulên) Û binivîse Gerînendeyê Navê (Navê Rêvebir). Di pencereya ku vedibe, bişkojka bikirtînin Xûliqandin (nşh), nav û formula me ya rêzê li qadê binivîsin Dirêjahî (Nûçik):
Ew dimîne ku li ser bitikîne OK û rêzika amade dikare di her formul, navnîşên dakêşan an nexşeyan de were bikar anîn.
- Ji bo girêdana tablo û nirxên lêgerînê fonksiyona VLOOKUP bikar bînin
- Meriv çawa navnîşek dakêşana xweser a xwerû biafirîne
- Meriv çawa tabloyek pivot biafirîne da ku hejmareke mezin a daneyan analîz bike