Ofaya daga cikin mahimman abubuwan fasalin shirin Excel shine mai sarrafa INDEX. Yana bincika bayanai a cikin kewayon a tsakiyar hanyar da aka ƙayyade jere da shafi, yana mai dawo da sakamakon zuwa cikin tantanin da aka tsara. Amma cikakkun damar wannan aikin an bayyana lokacin da aka yi amfani da su a cikin matattara mai hade da sauran masu gudanar da aiki. Bari mu bincika zaɓuɓɓuka daban-daban don aikace-aikacensa.
Yin amfani da aikin INDEX
Mai aiki INDEX na rukuni ne na rukuni na ayyuka Tunani da Arrays. Yana da nau'ikan guda biyu: don kayan aiki da kuma nassoshi.
Zaɓin don tsara abubuwa yana da syntax mai zuwa:
= INDEX (tsararru; jere_number; shafi_number)
A lokaci guda, za a iya amfani da mahawara ta ƙarshe a cikin dabara duka biyun tare kuma kowane ɗayansu, idan tsararru ɗaya ne. Don fannoni da yawa, yakamata a yi amfani da ƙimar duka. Hakanan ya kamata a la'akari da cewa jere da lambar lamba ba a fahimta da lambar ba a kan daidaitawa na takaddar, amma tsari a cikin tsarin da aka ƙaddara shi kansa.
Gwanin magana don zaɓi shine kamar haka:
= INDEX (mahaɗa; jere_number; shafi_number; [yanki_number])
Anan, a cikin hanyar, zaka iya amfani da hujja ɗaya kawai cikin biyu: Lambar layi ko Lambar Harafi. Hujja "Lambar Yanki" ana amfani da shi gabaɗaya kuma ana amfani dashi ne kawai lokacin da aka keɓance jeri da yawa cikin aikin.
Don haka, mai aiki yana bincika bayanai a cikin kewayon da aka ƙayyade lokacin ƙayyade jere ko shafi. Wannan yanayin yana kama da Mai aiki na VLR, amma ba kamar shi ba, yana iya bincika kusan ko'ina, kuma ba kawai a cikin ɓangaren hagu na tebur ba.
Hanyar 1: yi amfani da INDEX mai ba da izini don tsara bayanai
Da farko, bari mu bincika mai aiki ta amfani da mafi kyawun misali INDEX don arrays.
Muna da tebur na albashi. A cikin shafi na farko, an nuna sunayen ma'aikata, a cikin na biyu - ranar biya, kuma a na uku - adadin adadin albashin da aka samu. Muna buƙatar nuna sunan ma'aikaci a layi na uku.
- Zaɓi sel wanda za'a nuna sakamakon aiki. Danna alamar "Saka aikin", wanda yake nan da nan zuwa hagu na masarar dabara.
- Tsarin kunnawa a gaba Wizards na Aiki. A cikin rukuni Tunani da Arrays wannan kayan aiki ko "Cikakken jerin haruffa" neman suna INDEX. Bayan gano wannan ma'aikacin, zaɓi shi kuma danna maɓallin "Ok", wanda yake a kasan taga.
- Wani karamin taga yana buɗewa wanda kuke buƙatar zaɓi ɗayan nau'in ayyukan: Shirya ko Haɗi. Muna buƙatar zaɓi Shirya. Ana samunsa da farko kuma an inganta shi ta tsohuwa. Saboda haka, dole ne mu danna maɓallin "Ok".
- Farashin muhawara na aiki zai bude INDEX. Kamar yadda aka ambata a sama, tana da hujja guda uku, kuma saboda haka, fannoni uku da za a cika.
A fagen Shirya Dole ne a tantance adireshin kewayon bayanan da ake sarrafawa. Ana iya tura shi da hannu. Amma don sauƙaƙe aikin, za mu yi in ba haka ba. Sanya siginan kwamfuta a cikin filin da ya dace, sannan kuma kewaya dukkanin keɓaɓɓun bayanan bayanan akan takardar. Bayan haka, adireshin kewayon zai bayyana kai tsaye a fagen.
A fagen Lambar layi sanya lamba "3", tunda da sharadin muna buƙatar sanin sunan na uku a cikin jerin. A fagen Lambar Harafi saita lamba "1", tunda shafi tare da suna shine farkon a cikin kewayon da aka zaɓa.
Bayan an gama saitunan da aka ƙayyade, danna maballin "Ok".
- Sakamakon aiki ya nuna a cikin tantanin da aka nuna a sakin farko na wannan umarnin. Wato sunan mahaukaci shine na uku a cikin jerin cikin jerin bayanan data zabi.
Mun bincika aikace-aikacen aikin INDEX a cikin tsararru masu yawa (ginshiƙai masu yawa da layuka). Idan kewayon ma'auni ɗaya ne, cika bayanai a cikin taga gardi zai fi sauƙi. A fagen Shirya ta wannan hanyar kamar yadda muke sama, muna nuna adireshin sa. A wannan yanayin, kewayon bayanan ya ƙunshi ƙimar kawai a cikin shafi ɗaya. "Suna". A fagen Lambar layi nuna darajar "3", tunda kuna buƙatar gano bayanan daga layi na uku. Filin Lambar Harafi gabaɗaya, zaku iya barin shi wofi, tunda muna da kewayon girma guda ɗaya wanda muke amfani da shafi ɗaya kawai. Latsa maballin "Ok".
Sakamakon zai kasance iri ɗaya ne kamar na sama.
Wannan shi ne mafi sauƙin misali a gare ku don ganin yadda wannan aikin ke aiki, amma a aikace, nau'in nau'in amfanin sa har yanzu ba a taɓa yin amfani da shi ba.
Darasi: Mayan Maɗaukaki
Hanyar 2: amfani dashi tare da yin aiki tare da mai binciken SEARCH
A aikace, aikin INDEX galibi ana amfani da hujja Neman. Bunch INDEX - Neman kayan aiki ne mai ƙarfi yayin aiki a cikin Excel, wanda a cikin aikinsa ya fi sassauƙa fiye da analog ɗinsa na kusa - mai aiki VPR.
Babban maƙasudin aikin Neman alama ce ta lamba saboda adadin wata daraja a cikin kewayon da aka zaɓa.
Syntax mai aiki Neman kamar:
= SEARCH (bincike_value, lookup_array, [match_type])
- Sanya darajar - wannan shine darajar wanda matsayin sa a cikin kewayon da muke nema;
- Ganin Array shine kewayon da yake wannan darajar;
- Nau'in Match - Wannan sigar zaɓin zaɓi ne wanda ke yanke shawara ko bincika ƙimar daidai ko kusan. Zamu nemi kyawawan dabi'u, saboda haka ba a amfani da wannan hujja ba.
Amfani da wannan kayan aiki zaku iya sarrafa kansa da hujjoji Lambar layi da Lambar Harafi a aiki INDEX.
Bari mu ga yadda za a yi wannan tare da takamaiman misali. Muna aiki tare da tebur iri ɗaya, wanda aka tattauna a sama. Na dabam, muna da ƙarin ƙarin filayen guda biyu - "Suna" da "Adadin". Wajibi ne a tabbata cewa idan ka shigar da sunan ma'aikaci, ana nuna yawan kudaden da aka samu ta atomatik. Bari mu ga yadda za a iya aiwatar da wannan ta hanyar aiwatar da aiyuka INDEX da Neman.
- Da farko dai, mun gano irin albashin da ma'aikacin Parfenov D.F. yake karba. Shigar da sunan shi a filin da ya dace.
- Zaɓi sel a cikin filin "Adadin"wanda za a nuna sakamakon karshe. Kaddamar da taga aikin muhawara INDEX don arrays.
A fagen Shirya mun shigar da daidaitawar shafin inda albashin ma'aikata yake.
Filin Lambar Harafi bar shi fanko, kamar yadda muke amfani da kewayon ɗaya-misali azaman misali.
Amma a fagen Lambar layi kawai muna buƙatar rubuta aiki Neman. Don rubuta shi, muna bin koyarwar da aka tattauna a sama. Nan da nan shigar da sunan mai aiki a filin "Neman" ba tare da ambato ba. To, nan da nan buɗe buɗe sashi kuma nuna alamun daidaitawar ƙimar da ake so. Waɗannan su ne masu kula da tantanin halitta a cikinmu wanda muka rubuta sunan ma'aikacin Parfenov daban. Mun sanya wani ɗan kwasis ɗin kuma nuna alamun daidaitawa na kewayon da ake kallo. A cikin lamarinmu, wannan shine adireshin shafi tare da sunayen ma'aikata. Bayan haka, rufe murfin.
Bayan an shigar da dukkan dabi'u, danna maballin "Ok".
- Sakamakon yawan abin da aka samu D. Parfenov bayan an nuna sarrafa shi a cikin filin "Adadin".
- Yanzu idan a fagen "Suna" za mu canza abinda ke ciki da "Parfenov D.F.", misali, "Popova M. D.", sannan darajar albashi a cikin filin zai canza ta atomatik "Adadin".
Hanyar 3: rike da tebur da yawa
Yanzu bari mu ga yadda amfani da afareta INDEX Kuna iya aiwatar da tebur da yawa. A saboda wannan dalili ana amfani da ƙarin hujja. "Lambar Yanki".
Muna da tebur uku. Kowane tebur yana nuna albashin ma’aikata na tsawon wata guda. Aikinmu shi ne gano albashi (kashi na uku) na ma'aikaci na biyu (jere na biyu) don wata na uku (yanki na uku).
- Zaɓi wayar wanda sakamakon zai fito kuma ta hanyar da aka saba buɗe Mayan fasalin, amma lokacin zabar nau'in afareta, zaɓi duba mai duba. Muna buƙatar wannan saboda wannan nau'in yana goyan bayan kulawar gardama. "Lambar Yanki".
- Tattaunawa ta buɗe tana buɗewa. A fagen Haɗi Muna buƙatar bayyana adreshin dukkan jeri uku. Don yin wannan, saita siginan kwamfuta a fagen zaɓi zaɓi na farko tare da maɓallin linzamin kwamfuta na hagu. Sa'an nan kuma sanya semicolon. Wannan yana da mahimmanci, saboda idan kai tsaye ka zaɓi zaɓi na jerin shirye-shirye na gaba, to adireshin sa zai canza sauƙin daidaitawa na baya. Don haka, bayan shigar da Semicolon, zaɓi kewayon na gaba. Bayan haka kuma mun sake sanya wani ɗan kwaskwarima kuma zaɓi zaɓi na ƙarshe. Duk bayanin da ke cikin filin Haɗi ɗauka a baka.
A fagen Lambar layi nuna lambarta "2", tunda muna neman na biyu na ƙarshe a cikin jerin.
A fagen Lambar Harafi nuna lambarta "3"tunda kashin albashi shine na uku a jere a kowane tebur.
A fagen "Lambar Yanki" sanya lamba "3", tunda muna buƙatar samo bayanai a cikin tebur na uku, wanda ya ƙunshi bayani game da albashi na watan uku.
Bayan an shigar da dukkan bayanan, danna maɓallin "Ok".
- Bayan wannan, sakamakon ƙididdigar yana nunawa a cikin tantanin da aka zaɓi a baya. Ya nuna adadin albashin ma'aikaci na biyu (V. M. Safronov) na wata na uku.
Hanyar 4: lissafta adadin
Ba'a amfani da tsari mai amfani kamar yadda ake amfani dashi ba azaman tsararru, amma ana iya amfani dashi ba kawai lokacin aiki tare da layuka da yawa ba, har ma don wasu buƙatu. Misali, ana iya amfani dashi don kirga adadin a hade tare da wani ma'aikaci SAURARA.
Lokacin da aka kara adadin SAURARA yana da wadannan kalmomin:
= SUM (tsararren_address)
A cikin halinmu na musamman, ana iya yin lissafin adadin albashin daukacin ma'aikatan wata-wata ta amfani da wannan tsari:
= SUM (C4: C9)
Amma zaku iya canza shi ta amfani da aikin INDEX. Sannan zai sami tsari mai zuwa:
= SUM (C4: INDEX (C4: C9; 6))
A wannan yanayin, daidaitawa na farkon tsararren yana nuna tantanin halitta wanda yake farawa. Amma a cikin daidaitawa waɗanda ke nuna ƙarshen array, ana amfani da mai aiki INDEX. A wannan yanayin, gardamar farko ta mai aiki INDEX yana nuna kewayon, kuma na biyu - a sel na ƙarshe - na shida.
Darasi: Abubuwan Kyakkyawan fasali na Excel
Kamar yadda kake gani, aikin INDEX ana iya amfani dashi a cikin Excel don warware ayyukan da suka bambanta. Kodayake munyi la'akari da nisa daga duk zaɓuɓɓuka masu yiwuwa don aikace-aikacen ta, amma mafi mashahuri kawai. Akwai nau'ikan biyu na wannan aikin: nunin aiki da kuma tsarin bayanai. Ana iya amfani dashi mafi inganci a hade tare da sauran masu gudanar da aiki. Formulas da aka kirkira ta wannan hanyar zasu iya magance matsalolin mafi rikitarwa.
