Jadawalin tantanin halitta da aka zaɓa

A ce ni da ku muna buƙatar ganin bayanai daga tebur mai zuwa tare da ƙimar siyar da mota ta ƙasashe daban-daban a cikin 2021 (ainihin bayanan da aka ɗauka daga nan, ta hanya):

Tunda yawan jerin bayanai (ƙasashe) suna da yawa, ƙoƙarin cusa su duka a cikin jadawali ɗaya lokaci ɗaya ko dai zai haifar da mummunan “taswirar spaghetti” ko kuma gina sigogi daban-daban na kowane jeri, wanda ke da wahala sosai.

Kyakkyawan mafita ga wannan matsala na iya zama ƙirƙira ginshiƙi kawai akan bayanan daga jere na yanzu, watau jeren inda tantanin halitta yake:

Aiwatar da wannan abu ne mai sauƙi - kawai kuna buƙatar dabaru biyu da ƙaramin macro ɗaya a cikin layi 3.

Mataki 1. Lambar layin yanzu

Abu na farko da muke buƙata shine kewayon mai suna wanda ke ƙididdige lambar layi akan takardar inda tantanin mu na aiki yake yanzu. Yana buɗewa akan shafi Formulas - Mai sarrafa Suna (Formulas - Mai sarrafa sunan), danna maballin Create (ƙirƙira) sannan ku shigar da wannan tsari a can:

Mataki 2. Haɗa zuwa take

Don nuna ƙasar da aka zaɓa a cikin take da almara na ginshiƙi, muna buƙatar samun nuni ga tantanin halitta mai suna (ƙasar) daga shafi na farko. Don yin wannan, mun ƙirƙiri wani gida (watau Area = takardar yanzu, ba Littafi ba!) kewayon mai suna tare da dabara mai zuwa:

Anan, aikin INDEX yana zaɓar daga kewayon da aka bayar (shafi A, inda ƙasashen sa hannu suke kwance) tantanin halitta mai lambar jere wanda muka ƙaddara a baya.

Mataki 3. Haɗa zuwa bayanai

Yanzu, a irin wannan hanya, bari mu sami hanyar haɗi zuwa kewayon tare da duk bayanan tallace-tallace daga jere na yanzu, inda tantanin halitta yake yanzu. Ƙirƙiri wani kewayon mai suna tare da dabara mai zuwa:

Anan, hujja na uku, wanda shine sifili, yana sa INDEX ta dawo ba ƙima ɗaya ba, amma gabaɗayan layi a sakamakon.

Mataki na 4. Sauya hanyoyin haɗi a cikin Chart

Yanzu zaɓi taken tebur da jere na farko tare da bayanai (kewayon) kuma gina ginshiƙi akan su ta amfani da su Saka - Charts (Saka - Charts). Idan ka zaɓi jere tare da bayanai a cikin ginshiƙi, to za a nuna aikin a mashigin dabara Row (SERIES) aiki ne na musamman wanda Excel ke amfani da shi ta atomatik lokacin ƙirƙirar kowane ginshiƙi don komawa ga ainihin bayanai da takubban:

A hankali mu maye gurbin hujja ta farko (sa hannu) da ta uku (bayanai) a cikin wannan aikin tare da sunayen jeri na mu daga matakai 2 da 3:

Taswirar za ta fara nuna bayanan tallace-tallace daga jere na yanzu.

Mataki 5. Sake kirga macro

Taɓawar ƙarshe ta rage. Microsoft Excel yana sake ƙididdige ƙididdiga kawai lokacin da bayanan da ke kan takardar ya canza ko lokacin da aka danna maɓalli F9, kuma muna son sake lissafin ya faru lokacin da zaɓin ya canza, watau lokacin da aka motsa tantanin halitta mai aiki a cikin takardar. Don yin wannan, muna buƙatar ƙara macro mai sauƙi zuwa littafin aikin mu.

Danna-dama akan shafin bayanan bayanan kuma zaɓi umarnin source (Madogararsa). A cikin taga da ke buɗewa, shigar da lambar macro-handler don taron canjin zaɓi:

Kamar yadda zaku iya tsammani cikin sauƙi, duk abin da yake yi yana haifar da sake lissafin takarda a duk lokacin da matsayin tantanin halitta mai aiki ya canza.

Mataki 6. Haskaka Layin Yanzu

Don bayyanawa, Hakanan zaka iya ƙara ƙa'idar tsara tsari don haskaka ƙasar da aka nuna a halin yanzu akan ginshiƙi. Don yin wannan, zaɓi tebur kuma zaɓi Gida - Tsarin Yanayi - Ƙirƙirar Doka - Yi amfani da Formula don Ƙaddamar Sel don Tsara (Gida - Tsarin Yanayi - Sabuwar doka - Yi amfani da dabara don tantance waɗanne sel don tsarawa):

Anan tsarin yana bincika kowane tantanin halitta a cikin tebur cewa lambar layinsa ta yi daidai da lambar da aka adana a cikin ma'aunin TekRow, kuma idan akwai ashana, sai a kunna cika da launi da aka zaɓa.

Shi ke nan - mai sauƙi da kyau, daidai?

Notes

• A kan manyan teburi, duk wannan kyakkyawa na iya raguwa - tsara yanayin yanayi abu ne mai mahimmancin albarkatu, kuma sake ƙididdigewa ga kowane zaɓi na iya zama nauyi.
• Don hana bayanai daga bacewa akan ginshiƙi lokacin da aka zaɓi tantanin halitta bisa bazata a sama ko ƙasan tebur, zaku iya ƙara ƙarin rajistan shiga cikin sunan TekRow ta amfani da ayyukan IF na tsari:

  =IF(CELL("jere")<4,IF(CELL("jere")>4,CELL("jere")))
  

• Haskaka ƙayyadaddun ginshiƙai a cikin ginshiƙi
• Yadda ake ƙirƙirar ginshiƙi mai hulɗa a cikin Excel
• Zaɓin Haɗawa