Nemo bayanai a cikin Lissafi tare da aikin INDEX

01 na 02

Ayyukan Aikin Kwashi na Excel - Fassara Form

Ayyukan INDEX Excel

Gaba ɗaya, ana iya amfani da aikin INDEX don nemowa da sake dawo da wani adadi mai mahimmanci ko samun tantancewar tantanin halitta zuwa wurin da wannan darajar take a cikin takarda.

Akwai nau'i biyu na aikin INDEX da ake samuwa a cikin Excel: Formar Array da Form Form.

Babban bambanci tsakanin nau'i biyu na aikin shine:

• Fom ɗin Array ya sake dawo da adadin bayanan da aka samo shi a matsayi na tsakiya na jere na musamman da shafi na bayanai;
• ma'anar takarda ta sake dawo da tantancewar kwayar halitta ta hanyar tsinkayar wani shafi da jere.

Ayyukan Aikin Kwashi na Excel - Fassara Form

An yi la'akari da tsararrakin zama rukuni na ƙunshe a cikin takardun aiki. A cikin hoton da ke sama, tsararren zai zama gunkin sel daga A2 zuwa C4.

A cikin wannan misali, nau'i nau'i na aikin INDEX wanda ke cikin tantanin halitta C2 ya dawo da adadin bayanai - Widget - aka samo shi a matsayi na tsakiya na jere 3 da shafi na 2.

Ayyukan INDEX (Array Form) Daidaitawa da Magana

Haɗin aikin aiki yana nufin layout na aikin kuma ya haɗa da sunan aiki, shafuka, rabuɗɗun takaddama, da muhawara .

Haɗin aikin aikin INDEX shine:

= INDEX (Array, Row_num, Column_num)

Array - Siffofin tantance halitta don kewayon kwayoyin da za a bincike ta wurin aikin don bayanin da aka so

Row_num (zaɓi) - Lissafin jeri a cikin tsararren daga abin da zai dawo da darajar. Idan an cire wannan hujja, ana buƙatar Column_num.

Column_num (zaɓi) - Lambar shafi a cikin tsararren daga abin da zai dawo da darajar. Idan an cire wannan hujja, ana buƙatar Row_num.

• Domin duka jayayyar Row_num da Column_num , ko dai jimlar jeri da lambobi ko tantanin halitta da aka ambata game da wannan bayanin a cikin takardun aiki za a iya shiga.

SANYAR KYAU (Array Form) Misali

Kamar yadda aka ambata, misalin a cikin hoton da ke sama yana amfani da tsarin Array na aikin INDEX don dawo da kalmar Widget daga lissafin kaya.

Bayanin da ke ƙasa ya rufe matakan da ake amfani dashi don shigar da aikin INDEX a cikin sel B8 na takardar aiki.

Matakan suna amfani da bayanan salula na jigilar Row_num da Column_num , maimakon shigar da waɗannan lambobi kai tsaye.

Shigar da aikin INDEX

Zaɓuɓɓukan don shigar da aikin da ƙididdigar sun hada da:

1. Rubuta cikakken aikin: = INDEX (A2: C4, B6, B7) cikin cell B8
2. Zabi aikin da kuma muhawara ta amfani da akwatin maganganun INDEX

Kodayake yana yiwuwa don kawai aiwatar da cikakken aiki tare da hannu, mutane da yawa sun fi sauƙi don amfani da akwatin maganganu don shigar da muhawarar aiki.

Matakan da ke ƙasa suna amfani da akwatin maganganu don shigar da muhawarar aikin.

Ana buɗe akwatin maganganu

Tun da akwai nau'i biyu na aikin - kowannensu da nasu jayayya - kowane nau'i na buƙatar akwatin maganganun raba.

A sakamakon haka, akwai ƙarin matakai don bude akwatin maganin INDEX ba tare da mafi yawan ayyuka na Excel ba. Wannan mataki ya shafi dauka ko dai tsarin Array ko tsari na jigilar muhawara.

Da ke ƙasa akwai matakai da ake amfani dashi don shigar da aikin INDEX da muhawarar zuwa cikin cell B8 ta yin amfani da akwatin maganganun aikin.

1. Danna b8 B8 a cikin takardun aiki - wannan shine inda aikin zai kasance
2. Danna maɓallin Formulas na shafin rubutun
3. Zabi Binciken da Magana daga ribbon don bude jerin jerin ayyuka
4. Danna kan INDEX a cikin jerin don kawo akwatin maganganu na Zaɓaɓɓun Magana - wanda zai baka damar zaɓan tsakanin Tsarin Array da Tsarin Magana na aikin
5. Danna kan tashar, row_num, zaɓi na column_num
6. Danna OK don buɗe aikin INDEX - Rubutun maganganu na tsari

Shigar da jayayyar Magana

1. A cikin akwatin maganganu, danna kan Array line
2. Sanya siffofin A2 zuwa C4 a cikin takardun aiki don shigar da kewayon cikin akwatin maganganu
3. Danna kan layin Row_num a cikin akwatin maganganu
4. Danna kan B6 don shiga wannan tantanin halitta a cikin akwatin maganganu
5. Danna kan layin Column_num cikin akwatin maganganu
6. Danna kan tantanin halitta B7 don shigar da wannan tantanin halitta a cikin akwatin maganganu
7. Danna Ya yi don kammala aikin kuma rufe akwatin maganganu
8. Kalmar nan Gizmo ta bayyana a cikin cell B8 tun lokacin shine lokacin a cikin tantanin halitta ta tsakiya tsakanin jere na uku da na biyu na sassa kaya
9. Lokacin da ka danna kan b8 B8 cikakkiyar aikin = INDEX (A2: C4, B6, B7) ya bayyana a cikin maɓallin tsari a sama da takardun aiki

Ƙididdiga Kuskuren Yanayin Ƙididdiga

Kuskuren ɓataccen ɓangaren da ke hade da aikin INDEX - Fassara tsari ne:

#VALUE! - Yana faruwa idan ko dai Row_num , Column_num muhawara ba lambobi ba ne.

#ref! - Yana faruwa idan dai:

• Jirgin Row_num ya fi yawan layuka a cikin zaɓin da aka zaba;
• Gurbin Col_num ya fi yawan ginshiƙai a cikin zaɓin da aka zaba.

Abinda aka yi Magana da Abubuwan Taɗi

Abũbuwan amfãni ga yin amfani da akwatin maganganu don shigar da bayanai don muhawarar aiki shine:

1. Maganar maganganun tana kula da haɗin gwargwadon aikin - yana sa ya fi sauƙi don shigar da muhawarar aiki a lokaci daya ba tare da shigar da alamar daidai ba, ƙuƙwalwa, ko kalmomi waɗanda ke aiki a matsayin raba tsakanin gardama.
2. Bayanan salula, irin su B6 ko B7, za a iya shiga cikin maganganun ta hanyar amfani da ma'ana , wanda ya haɗa da danna kan zaɓuɓɓukan da aka zaɓa tare da linzamin kwamfuta maimakon buga su a cikin. Ba wai kawai yana nuna sauki ba, yana kuma taimaka wajen rage kurakurai a cikin siffofin da aka lalacewa kuskuren salula ba daidai ba.

02 na 02

Ayyukan INDEX Excel - Fassara Takardar

Ayyukan INDEX Excel - Fassara Takardar

Hanyoyin da aka rubuta game da aikin ya dawo darajar adadin tantanin tantanin halitta wanda ke da wuri a tsakiya tsakanin wani jeri da shafi na bayanai.

Za'a iya ɗaukar jerin tsararraki masu yawa da ba a kusa ba kamar yadda aka nuna a cikin hoton da ke sama.

Ayyukan INDEX (Fassara Magana) Magana da Magana

Hadawa da jayayya ga tsarin INDEX rubutun su ne:

= INDEX (Magana, Row_num, Column_num, Area_num)

Nassin - (da ake buƙata) sanannun tantancewar sel don kewayon kwayoyin da za a bincike ta wurin aikin don bayanin da aka so.

• Idan an shigar da jeri, ba a kunna jeri ba don wannan hujja, dole ne a kewaye da jerin jeri na kusa da madaidaiciya kamar yadda aka nuna a cikin tsarin INDEX: = INDEX ((A1: A5, C1: E1, C4: D5), B7 , B8) dauke daga hoton da ke sama

Row_num - jere a cikin jeri daga abin da zai dawo da darajar.

• Zaɓuɓɓuka don jeri guda ɗaya
• da ake buƙata don ƙungiya guda da jere jeri na jeri

Column_num - lambar mahaɗin a cikin tsararren daga abin da zai dawo da darajar.

• Zaɓuɓɓuka don shafi ɗaya shafi Harsunan jeri
• da ake buƙata don jere guda ɗaya da mahallin mahaɗin Matakan jeri

Lura: Domin duka jigilar Row_num da Column_num , ko dai jimlar jeri da lambobi ko tantanin halitta da aka ambata a wurin da za'a sanya wannan bayanin a cikin takardun aiki zai iya shiga.

Area_num (zaɓuɓɓuka) - idan ƙudidar da aka ƙidaya ta ƙunshi nau'i masu yawa da ba a kusa ba, wannan shawara ta zaɓi abin da kewayon sel don dawo da bayanai daga. Idan an cire shi, aikin yana amfani da jigon farko da aka jera a cikin shawara.

• Na farko jeri da aka shiga a cikin Magana na ƙidaya ya ƙidaya 1;
• na biyu shine 2;
• na uku shine 3, da sauransu.

SANYAR KYAU (Fassara Misalin) Misali

Misali a cikin hoton da ke sama yana amfani da nau'in Nassin aikin INDEX don dawowa watan Yuli daga yanki na 2 daga fushin A1 zuwa E1.

Bayanin da ke ƙasa ya rufe matakan da ake amfani dashi don shigar da aikin INDEX a cikin sel B10 na takardar aiki.

Matakai suna yin amfani da bayanan salula na Row_num, Column_num, da Magana na Area_num , maimakon shigar da waɗannan lambobi kai tsaye.

Shigar da aikin INDEX

Zaɓuɓɓukan don shigar da aikin da ƙididdigar sun hada da:

1. Rubuta cikakken aikin: = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) zuwa cikin cell B10
2. Zabi aikin da kuma muhawara ta amfani da akwatin maganganun INDEX

Kodayake yana yiwuwa don kawai aiwatar da cikakken aiki tare da hannu, mutane da yawa sun fi sauƙi don amfani da akwatin maganganu don shigar da muhawarar aiki.

Matakan da ke ƙasa suna amfani da akwatin maganganu don shigar da muhawarar aikin.

Ana buɗe akwatin maganganu

Tun da akwai nau'i biyu na aikin - kowannensu da nasu jayayya - kowane nau'i na buƙatar akwatin maganganun raba.

A sakamakon haka, akwai ƙarin matakai don bude akwatin maganin INDEX ba tare da mafi yawan ayyuka na Excel ba. Wannan mataki ya shafi dauka ko dai tsarin Array ko tsari na jigilar muhawara.

Da ke ƙasa akwai matakai da ake amfani dashi don shigar da aikin INDEX da muhawarar zuwa cikin tantanin halitta B10 ta amfani da akwatin maganganun aikin.

1. Danna b8 B8 a cikin takardun aiki - wannan shine inda aikin zai kasance
2. Danna maɓallin Formulas na shafin rubutun
3. Zabi Binciken da Magana daga ribbon don bude jerin jerin ayyuka
4. Danna kan INDEX a cikin jerin don kawo akwatin maganganu na Zaɓaɓɓun Magana - wanda zai baka damar zaɓan tsakanin Tsarin Array da Tsarin Magana na aikin
5. Danna kan mahimmanci, row_num, column_num, zaɓi na yanki_num
6. Danna OK don buɗe aikin INDEX - Rubutun maganganun tsari

Shigar da jayayyar Magana

1. A cikin akwatin maganganu, danna kan layi na layi
2. Shigar da takalmin bude " ( " a kan wannan layi a cikin akwatin maganganu
3. Sanya siffofin A1 zuwa A5 a cikin takardun aiki don shigar da layin bayan bayanan budewa
4. Rubuta takaddama don aiki a matsayin mai raba tsakanin jigon farko da na biyu
5. Hanya sassa C1 zuwa E1 a cikin takardar aiki don shigar da bayanan bayan ƙirar
6. Rubuta lamba na biyu don aiki a matsayin mai raba tsakani tsakanin na biyu da na uku
7. Sanya siffofin C4 zuwa D5 a cikin takardun aiki don shigar da layin bayan bayanan
8. Shigar da takalmin rufewa " ) " bayan bayanan na uku don kammala bayanin Magana
9. Danna kan layin Row_num a cikin akwatin maganganu
10. Danna kan tantanin halitta B7 don shigar da wannan tantanin halitta a cikin akwatin maganganu
11. Danna kan layin Column_num cikin akwatin maganganu
12. Danna b8 B8 don shigar da wannan tantanin halitta a cikin akwatin maganganu
13. Danna kan layi na Zone_num a cikin akwatin maganganu
14. Danna kan b9 B9 don shigar da wannan tantanin halitta a cikin akwatin maganganu
15. Danna Ya yi don kammala aikin kuma rufe akwatin maganganu
16. Yuli Yuli ya bayyana cikin tantanin halitta B10 tun lokacin watan ne a cikin tantanin halitta yana rabawa jeri na farko da na biyu na sashi na biyu (iyakar C1 zuwa 1)
17. A yayin da ka danna kan tantanin halitta B8 da cikakken aikin = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) ya bayyana a cikin maɓallin tsari a sama da takardun aiki

Ƙididdiga Kuskuren Yanayin Ƙididdiga

Kuskuren ɓataccen ɓangaren da ke hade da aikin INDEX - Fassara takarda shine:

#VALUE! - Yana faruwa idan ko dai Row_num , Column_num, ko Yanayi na Area_num ba lambobi ba ne.

#ref! - Yana faruwa idan:

• Jirgin Row_num ya fi yawan layuka a cikin zaɓin da aka zaba;
• Gurbin Col_num ya fi yawan ginshiƙai a cikin zaɓin da aka zaba.
• Shawarar yankin_num ta fi yawan yawan wurare a cikin zaɓin da aka zaba.