Mukati
Iwe une matafura ane data muExcel inogona kudzokororwa, kureva nhamba yemitsara (makoroni) inogona kuwedzera kana kuderera mukufamba kwebasa? Kana iyo tafura saizi "yayangarara", ipapo iwe uchafanirwa kugara uchitarisa nguva ino uye kuigadzirisa:
- zvinongedzo mumishumo mafomula anonongedzera kutafura yedu
- ekutanga matafura epivot anovakwa zvinoenderana netafura yedu
- ekutanga machati akavakwa zvinoenderana netafura yedu
- siyana dzekudonhedza dzinoshandisa tafura yedu sesosi yedata
Zvese izvi zvese hazvikusiye uchibhowekana 😉
Zvichave zviri nyore uye zvakaringana kugadzira iyo ine simba "rabha" renji, iyo inongoerekana yagadzirisa saizi kune chaiyo nhamba yemitsara nemakoramu e data. Kuti uite izvi, kune nzira dzakawanda.
Nzira 1. Tafura yakachenjera
Simbisa huwandu hwemasero ako uye sarudza kubva pane tab Kumba - Fomati seTafura (Kumba - Fomati seTafura):
Kana iwe usingade iyo ine mitsetse dhizaini inowedzerwa patafura senge mhedzisiro, saka unogona kuidzima pane iyo tab inoratidzika. Mugadziri (Design). Tafura imwe neimwe yakagadzirwa nenzira iyi inogamuchira zita rinogona kutsiviwa nerimwe rakanakira panzvimbo imwe chete pane tab Mugadziri (Design) mumunda Tafura zita (Zita retafura).
Iye zvino isu tinogona kushandisa zvinongedzo zvinongedzo kune yedu "smart tafura":
- Tafura 1 - chinongedzo kune tafura yese kunze kweiyo musoro mutsara (A2: D5)
- Tafura1[#Zvose] - chinongedzo kune tafura yese (A1: D5)
- Tafura1[Petro] -kureva kune renji-columni isina yekutanga sero-musoro (C2: C5)
- Tafura1[#Misoro] - chinongedzo kune "musoro" nemazita emakoramu (A1: D1)
Mareferensi akadaro anoshanda zvakanyanya mumafomula, semuenzaniso:
= SUM (Tafura1[Moscow]) - kuverenga kwehuwandu hwekoramu "Moscow"
or
=VPR(F5;Tafura 1;3;0) - tsvaga patafura yemwedzi kubva muchitokisi F5 uye wopa St. Petersburg mari yayo (chii chinonzi VLOOKUP?)
Izvi zvinongedzo zvinogona kushandiswa zvinobudirira pakugadzira pivot matafura nekusarudza pane iyo tab Isa – Pivot Tafura (Isa – Pivot Tafura) uye nekuisa zita reiyo smart tafura sesosi yedata:
Kana ukasarudza chidimbu chetafura yakadaro (somuenzaniso, makoramu maviri ekutanga) uye kugadzira dhayagiramu yemhando ipi zvayo, ipapo kana uchiwedzera mitsara mitsva, ivo vanozongowedzerwa kumufananidzo.
Paunenge uchigadzira zvinyorwa zvekudonhedza-pasi, zvinongedzo zvakananga kune smart tafura zvinhu hazvigone kushandiswa, asi unogona kutenderera zviri nyore kutenderedza ichi chigadziro uchishandisa hunyanzvi hwekunyepedzera - shandisa basa racho. INDIRECT (ZVINOKOSHA), izvo zvinoshandura zvinyorwa kuita link:
Avo. chinongedzo kune tafura yakangwara muchimiro chetambo yemavara (mune quotation mamaki!) inoshanduka kuita yakazara-yakazara link, uye rondedzero yekudonha kazhinji inozviona.
Method 2: Dynamic zita renji
Kana kushandura data rako kuita tafura yakangwara kusingadiwe nekuda kwechimwe chikonzero, saka iwe unogona kushandisa nzira yakati nyoro, asi yakanyanya kujeka uye inosiyana-siyana - gadzira ine simba rine zita renji muExcel rinoreva tafura yedu. Zvadaro, sepatafura yakangwara, unogona kushandisa zvakasununguka zita reiyo yakagadzirwa mune chero mafomula, mishumo, machati, nezvimwe. Ngatitange nemuenzaniso wakapfava:
basa: gadzira dhizaini ine zita renji iro rinogona kureva runyorwa rwemaguta uye wobva watambanudza otomatiki uye nekuderera muhukuru paunenge uchiwedzera maguta matsva kana kuadzima.
Tichada maviri akavakirwa-muExcel mabasa anowanikwa mune chero vhezheni - POICPOZ (MATCH) kuona sero yekupedzisira yemhando, uye INDEX (INDEX) kugadzira chinongedzo chine simba.
Kutsvaga sero yekupedzisira uchishandisa MATCH
MATCH(kutarisisa_kukosha, huwandu, match_type) - basa rinotsvaga kukosha kwakapihwa mumutsara (mutsara kana koramu) uye inodzosera nhamba yeordinal yesero payakawanikwa. Semuenzaniso, fomula MATCH("Kurume";A1:A5;0) ichadzosa nhamba 4 semhedzisiro, nekuti izwi rekuti "Kurume" riri musero rechina mukoramu A1:A5. Yekupedzisira basa nharo Match_Type = 0 zvinoreva kuti tiri kutsvaga chaiyo match. Kana iyi nharo isina kutsanangurwa, ipapo basa rinoshandura kune yekutsvaga maitiro kune iri pedyo kukosha kukosha - izvi ndizvo chaizvo zvinogona kushandiswa zvinobudirira kuwana yekupedzisira yakagarwa sero muhurongwa hwedu.
Izvo zvakakosha zvehunyengeri zviri nyore. MATCH inotsvaga maseru muchikamu kubva kumusoro kusvika kuzasi uye, nedzidziso, inofanira kumira kana yawana iri padyo diki kukosha kune yakapihwa. Kana iwe ukatsanangura kukosha kuri pachena kudarika chero inowanikwa mutafura sehukoshi hunodiwa, ipapo MATCH ichasvika kumagumo etafura, usawane chinhu uye upe kutevedzana kwesero yekupedzisira yakazadzwa. Uye tinochida!
Kana pane nhamba chete muhurongwa hwedu, saka tinogona kutsanangura nhamba seyokoshi yatinoda, iyo iri pachena yakakura kudarika chero ipi zvayo iri patafura:
Kuti uwane vimbiso, unogona kushandisa nhamba 9E + 307 (9 nguva 10 kune simba re307, kureva 9 ne 307 zero) - nhamba yepamusoro iyo Excel inogona kushanda nayo mumusimboti.
Kana paine mameseji kukosha mukoramu yedu, saka seyakaenzana nenhamba huru inokwanisika, unogona kuisa chivakwa REPEAT ("i", 255) - tambo yemavara ine mazana maviri nemakumi mashanu neshanu mavara "i" - iyo yekupedzisira tsamba ye the alphabet. Sezvo Excel ichinyatso enzanisa makodhi ehunhu kana uchitsvaga, chero chinyorwa chiri patafura yedu chinozove "chidiki" pane chakareba "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" mutsara:
Gadzira chinongedzo uchishandisa INDEX
Zvino zvatave kuziva chinzvimbo chekupedzisira chisina chinhu mutafura, chinosara kugadzira chinongedzo kune yedu yese. Nokuda kweizvi tinoshandisa basa:
INDEX(range; row_num; column_num)
Inopa zviri mukati mesero kubva pachiyero nemutsara uye nhamba yekoramu, kureva semuenzaniso, basa = INDEX (A1: D5; 3; 4) patafura yedu nemaguta uye mwedzi kubva kune yapfuura nzira ichapa 1240 - zviri mukati. kubva pamutsara wechitatu uye 3th column, kureva maseru D4. Kana paine koramu imwe chete, saka nhamba yayo inogona kusiiwa, kureva formula INDEX(A3:A2;6) ichapa "Samara" mumufananidzo wekupedzisira.
Uye pane imwe isina kunyatsojeka nuance: kana iyo INDEX isina kungopinda muchitokisi mushure me = chiratidzo, semazuva ese, asi inoshandiswa sechikamu chekupedzisira cherengedzo kune renji mushure mecolon, saka haichapi. zviri mukati mesero, asi kero yayo! Saka, fomula yakaita se $A $2:INDEX($A$2:$A$100;3) ichapa chirevo kune renji A2:A4 pane zvinobuda.
Uye apa ndipo panouya basa reMATCH, iro ratinoisa mukati meINDEX kuti tinyatsoona kupera kwechinyorwa:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255)A2:A100))
Gadzira renji ine mazita
Inosara yekuirongedza yese kuita imwe chete. Vhura tab Formula (Maumbirwo) Uye chengetedza Zita reManeja (Maneja weZita). Pahwindo rinovhurwa, tinya bhatani sika (nyowani), isa zita redu remhando uye fomula mumunda dungwerungwe (Referensi):
Rinosara kudzvanya OK uye iyo yakagadzirira renji inogona kushandiswa mune chero mafomula, kudonhedza pasi zvinyorwa kana machati.
- Uchishandisa basa reVLOOKUP kubatanidza matafura uye maitiro ekutarisa
- Maitiro ekugadzira iyo auto-populating dropdown list
- Maitiro ekugadzira tafura yepivot yekuongorora huwandu hukuru hwe data