Mukati
Pakutanga kuona (kunyanya kana kuverenga rubatsiro), basa INDIRECT (ZVINOKOSHA) anotaridzika ari nyore uye kunyange asina kufanira. Chinhu chayo ndechekushandura mavara anoita senge chinongedzo kuita chinongedzo chakazara. Avo. kana tichida kureva sero A1, saka tinogona kugara tichiita chinongedzo chakananga (isa chiratidzo chakaenzana muD1, tinya paA1 wodzvanya Enter), kana isu tinogona kushandisa. INDIRECT nokuda kwechinangwa chimwe chete.
Ndokumbira utarise kuti iyo basa nharo - rengedzo kuA1 - inopindirwa mumakotesheni mamaki, zvekuti, chokwadi, chinyorwa pano.
“Zvakanaka, zvakanaka,” unodaro. "Uye chii chinobatsira?"
Asi usatonga nemafungiro ekutanga - inonyengera. Iyi ficha inogona kukubatsira mune akawanda mamiriro.
Muenzaniso 1. Transpose
Yemhando yepamusoro yemhando: iwe unofanirwa kushandura iyo yakatwasuka dia
groove kuenda kuhorizontal (transpose). Zvechokwadi, iwe unogona kushandisa yakakosha kuisa kana basa TRANSP (TRANSPOSE) mune array formula, asi iwe unogona kupfuura neyedu INDIRECT:
Pfungwa yacho iri nyore: kuti titore kero yesero rinotevera, tinonamira tsamba "A" neiyo yakakosha hunhu "&" uye nhamba yekoramu yesero razvino, iro basa rinotipa. COLUMN (COLUMN).
Iyo reverse maitiro inoitwa zviri nani zvishoma zvakasiyana. Kubva panguva ino isu tinofanirwa kuumba chinongedzo kumaseru B2, C2, D2, nezvimwewo, zviri nyore kushandisa iyo R1C1 yekubatanidza modhi panzvimbo yeiyo yekare "hondo yegungwa". Mune iyi modhi, maseru edu anongosiyana chete mukoramu nhamba: B2 = R1C2, C2=R1C3, D2=R1C4 etc.
Apa ndipo panouya yechipiri sarudzo yekushanda nharo. INDIRECT. Kana zvakaenzana KUNYEPA (NHEMA), ipapo unogona kuseta kero yekubatanidza muR1C1 modhi. Saka isu tinokwanisa kuchinjisa zviri nyore kuchinjika renji kudzokera kune yakatwasuka:
Muenzaniso 2. Sum by interval
Isu takatoongorora imwe nzira yekupfupisa pamusoro pehwindo (renji) yehukuru hwakapihwa papepa uchishandisa basa. KURASA (OFFSET). Dambudziko rakafanana rinogona kugadziriswa uchishandisa INDIRECT. Kana isu tichida kupfupisa data chete kubva kune imwe nguva-yenguva, saka tinogona kuinamatira kubva muzvidimbu tozoishandura kuita yakazara-yakazara link, yatinogona kuisa mukati mebasa. SUM (SUM):
Muenzaniso 3. Smart table dropdown list
Dzimwe nguva Microsoft Excel haibate akangwara tafura mazita uye makoramu seakazara link. Saka, semuenzaniso, paunenge uchiedza kugadzira rondedzero yekudonha (tab Data - Kusimbiswa kweData) zvichibva pambiru vashandi kubva ku smart table vanhu tichawana kukanganisa:
Kana isu "tikapeta" chinongedzo nebasa redu INDIRECT, ipapo Excel inozvigamuchira zviri nyore uye yedu yekudonha-pasi runyoro ichagadziridzwa zvine simba kana ichiwedzera vashandi vatsva kumagumo etafura yakangwara:
Muenzaniso 4. Unbreakable Links
Sezvaunoziva, Excel inogadzirisa otomatiki kero dzereferenzi mumafomula paunenge uchiisa kana kudzima mitsara-makoramu papepa. Muzviitiko zvakawanda, izvi zvakarurama uye zviri nyore, asi kwete nguva dzose. Ngatitii isu tinoda kuendesa mazita kubva kudhairekitori revashandi kuenda kumushumo:
Kana iwe ukaisa zvisungo zvenguva dzose (pinda = B2 musero yekutanga yakasvibirira uye uikope pasi), ipapo paunobvisa, semuenzaniso, Dasha, tichawana #LINK! kukanganisa musero regirini rinoenderana naye. (#REF!). Muchiitiko chekushandisa basa kugadzira zvinongedzo INDIRECT hapazovi nedambudziko rakadaro.
Muenzaniso 5: Kuunganidza data kubva kune akawanda mapepa
Ngatitii tine mapepa mashanu ane mishumo yemhando imwechete kubva kuvashandi vakasiyana (Mikhail, Elena, Ivan, Sergey, Dmitry):
Ngatitorei kuti chimiro, saizi, chinzvimbo uye kutevedzana kwezvinhu uye mwedzi mumatafura ese zvakafanana - nhamba chete dzinosiyana.
Iwe unogona kuunganidza data kubva pamapepa ese (usaite muchidimbu, asi isa pasi peumwe neumwe mu "murwi") neformula imwe chete:
Sezvauri kuona, iyo pfungwa yakafanana: isu tinonamira chinongedzo kune yaidiwa sero yepepa rakapihwa, uye INDIRECT anorishandura kuita "live". Kuti zvive nyore, pamusoro petafura, ndakawedzera mabhii emakoroni (B, C, D), uye kurudyi - nhamba dzemutsara dzinoda kutorwa kubva papepa rimwe nerimwe.
Pitfalls
Kana uri kushandisa INDIRECT (ZVINOKOSHA) iwe unofanirwa kuyeuka nezve utera hwayo:
- Kana iwe ukabatanidza kune imwe faira (nekunamira zita refaira mumabhuraketi akaenzana, zita reshizha, uye kero yesero), zvino inoshanda chete apo faira rekutanga rakavhurika. Kana tikavhara, tinowana kukanganisa # LINK!
- INDIRECT haigone kureva renji ine mazita. Pa static - hapana dambudziko.
- INDIRECT ibasa risingachinjiki kana kuti “risingachinji”, kureva kuti rinoverengerwa kune chero shanduko mune chero sero rebepa, uye kwete kungofurira maseru, senge mumabasa akajairika. Izvi zvine mhedzisiro yakaipa pakuita uye zviri nani kuti usatakurwe nematafura makuru INDIRECT.
- Maitiro ekugadzira dhizaini renji ine auto-saizi
- Kupfupisa pamusoro pehwindo-hwindo pane pepa rine OFFSET basa