Contents
A kallon farko (musamman lokacin karanta taimako), aikin GASKIYA (GASKIYA) ya dubi mai sauƙi har ma ba dole ba. Asalinsa shine juya rubutu mai kama da hanyar haɗi zuwa cikakkiyar hanyar haɗi. Wadancan. Idan muna buƙatar komawa zuwa tantanin halitta A1, to, muna iya ko dai a al'ada don yin hanyar haɗin kai tsaye (shigar da alamar daidai a D1, danna A1 kuma danna Shigar), ko kuma za mu iya amfani da shi. GASKIYA domin wannan manufa:
Lura cewa hujjar aikin - nuni ga A1 - an shigar da shi cikin alamomin zance, ta yadda, a zahiri, rubutu ne a nan.
"To, OK," ka ce. "Kuma menene amfanin?"
Amma kada kuyi hukunci da ra'ayi na farko - yana da yaudara. Wannan fasalin zai iya taimaka muku fita a cikin yanayi da yawa.
Misali 1. Fassara
A classic na nau'in: kuna buƙatar kunna dia a tsaye
tsagi zuwa kwance (transpose). Tabbas, zaku iya amfani da sakawa ko aiki na musamman TRANSP (TSARKI) a cikin tsari na tsararru, amma zaku iya samun ta tare da mu GASKIYA:
Hankali yana da sauƙi: don samun adireshin tantanin halitta na gaba, muna manne harafin "A" tare da hali na musamman "&" da lambar shafi na tantanin halitta na yanzu, wanda aikin ya ba mu. shafi (COLUMN).
A baya hanya ne mafi alhẽri yi kadan daban-daban. Tun daga wannan lokacin muna buƙatar samar da hanyar haɗi zuwa sel B2, C2, D2, da dai sauransu, ya fi dacewa don amfani da yanayin hanyar haɗin R1C1 maimakon classic "yakin teku". A wannan yanayin, ƙwayoyin mu za su bambanta kawai a cikin lambar shafi: B2=R1C2, C2=R1C3, D2=R1C4 da dai sauransu.
Wannan shine inda hujjar aikin zaɓi na biyu ta shigo. GASKIYA. Idan daidai ne KARYA (KARYA), sannan zaku iya saita adireshin mahaɗin a yanayin R1C1. Don haka a sauƙaƙe za mu iya mayar da kewayon kwance zuwa tsaye:
Misali 2. Jima ta tazara
Mun riga mun bincika wata hanya ta tarawa akan taga (kewaye) na girman da aka ba a kan takardar ta amfani da aikin JUYAWA (KASHE). Hakanan ana iya magance irin wannan matsala ta amfani da ita GASKIYA. Idan muna buƙatar taƙaita bayanai kawai daga wani lokaci-lokaci, to, za mu iya manna shi daga guntu sannan mu juya shi zuwa cikakkiyar hanyar haɗi, wanda za mu iya saka shi a cikin aikin. SUM (SUM):
Misali 3. Smart tebur jerin zaɓuka
Wani lokaci Microsoft Excel ba ya ɗaukar sunayen tebur masu wayo da ginshiƙai azaman cikakkun hanyoyin haɗin gwiwa. Don haka, alal misali, lokacin ƙoƙarin ƙirƙirar jerin zaɓuka (tab Data - Tabbatar da Data) bisa shafi ma'aikata daga tebur mai wayo mutane za mu sami kuskure:
Idan muka "nannade" hanyar haɗi tare da aikinmu GASKIYA, to Excel zai yarda da shi cikin sauƙi kuma jerin abubuwan da muke saukewa za a sabunta su da ƙarfi yayin ƙara sabbin ma'aikata zuwa ƙarshen tebur mai wayo:
Misali 4. Haɗin da ba a iya warwarewa
Kamar yadda kuka sani, Excel ta atomatik yana gyara adiresoshin tunani a cikin dabara yayin sakawa ko share ginshiƙan layi akan takarda. A mafi yawan lokuta, wannan daidai ne kuma ya dace, amma ba koyaushe ba. Bari mu ce muna buƙatar canja wurin sunaye daga kundin adireshin ma'aikata zuwa rahoton:
Idan ka sanya hanyoyin haɗin kai na yau da kullun (shigar = B2 a cikin tantanin halitta na farko sannan ka kwafi shi), to idan ka goge, misali, Dasha, za mu sami # LINK! kuskure a cikin koren cell din daidai da ita. (#REF!). A cikin yanayin amfani da aikin don ƙirƙirar hanyoyin haɗi GASKIYA ba za a sami irin wannan matsalar ba.
Misali 5: Tattara bayanai daga zanen gado da yawa
Idan muna da zanen gado 5 tare da rahotanni iri ɗaya daga ma'aikata daban-daban (Mikhail, Elena, Ivan, Sergey, Dmitry):
Bari mu ɗauka cewa siffar, girman, matsayi da jerin kayayyaki da watanni a cikin dukkanin tebur iri ɗaya ne - kawai lambobi sun bambanta.
Kuna iya tattara bayanai daga duk zanen gado (kada ku taƙaita shi, amma ku sanya shi ƙarƙashin juna a cikin “tari”) tare da dabara ɗaya kawai:
Kamar yadda kake gani, ra'ayin iri ɗaya ne: muna manne hanyar haɗi zuwa tantanin halitta da ake so na takardar da aka ba, kuma GASKIYA juya shi zuwa "rayuwa". Don dacewa, sama da tebur, Na ƙara haruffan ginshiƙai (B, C, D), kuma a dama - lambobin layin da ake buƙatar ɗauka daga kowane takarda.
pitfalls
Idan kana amfani GASKIYA (GASKIYA) kana bukatar ka tuna game da rauninsa:
- Idan kun haɗa zuwa wani fayil (ta hanyar haɗa sunan fayil ɗin a cikin madauri, sunan takardar, da adireshin tantanin halitta), to yana aiki ne kawai yayin da ainihin fayil ɗin ke buɗe. Idan muka rufe, muna samun kuskuren #LINK!
- INDIRECT ba zai iya komawa zuwa kewayo mai ƙarfi mai ƙarfi ba. A tsaye - babu matsala.
- INDIRECT aiki ne mai canzawa ko “maras ƙarfi”, watau ana sake ƙididdige shi don kowane canji a kowane tantanin halitta na takardar, kuma ba kawai tasirin sel ba, kamar a cikin ayyuka na yau da kullun. Wannan yana da mummunan tasiri akan aikin kuma yana da kyau kada a tafi da shi tare da manyan tebur na INDIRECT.
- Yadda ake ƙirƙirar kewayo mai ƙarfi tare da girman kai
- Ƙaddamar da kewayon taga akan takarda tare da aikin OFFSET