Ko te "Index" i roto i te Excel: whakaahuatanga, tono me tauira

I tenei wāhi, Excel hoki tona rongonui he Kupu iho anake. Na te reira e mea ohie ki te mahi i te whānui o tātaitanga ōhanga, me te tauanga momo i runga i moni nui o ngā raraunga. Hoki tenei whakaaro, e whakarato ana te reira mo te tokomaha nui o hanga-i roto i te āhuatanga, tae atu te tautoko. Etahi o ratou e taea ki te mahi mahi, tae atu i runga i huinga raraunga. "Index" mahi me ki te hamani ratou. I roto i te Excel, te whakamahia reira e rua motuhake, me te "MATCH", ka e kia kōrero i raro nei.

whakaahuatanga

"Index" mahi i roto i te Excel hoki te uara (uara tohutoro i runga i) te tirotiro pūtau, rarangi i homai me tau tīwae o te tepu o te awhe tuaruahia ranei.

Ko tōna wetereo ohie me e whai ake: Index (ngohi rarangi №, tīwae №).

taea hoki te whakahaeretia tēnei taumahi ki te rarangi kotahi te tīwae kotahi ranei. I roto i tenei take, i muri i te tauwhāiti he ngohi kotahi-ahu whakaturia te kotahi tau. E pā ana te reira ki te tau raina ki te ko te tīwae te ngohi, a hurihia.

Ko te "Index" i roto i Excel ētahi wā homai te uara "#REF!". puta tenei tino maha, ka ko te pūtau kei i te pūtahitanga o te haupae mea me tīwae waho te whānuitanga whakaritea.

tauira tono

Fakakaukau angé ki he wā torutoru te whakamahi i te "Index" i roto i te mahi.

Me kī i reira ko te ngohi o 4 tīwae me 4 nga rarangi (kite. Ripanga). Ki te tomo koutou ki tetahi o pūtau te tepu kei waho te whānuitanga o A1: faaiteraa E5 "= Index (B2: E5, 2, 3)" (i waho te korukī) me te pēhi i "Tomo", ka ki "begonia" ka kia tukuna i roto i te whakautu.

Ki te hiahia koe ki te mohio e hia ngā ākonga Rōpū 2 i te tauanga "ta'efakafiemālie" i roto i te pouaka e tika ana tomo te faaiteraa e whai ake nei: Index (C2: C5, 1).

Ko te "MATCH" i roto i te Excel

Rua tauira homai i runga e kore e mahi ki huinga raraunga nui. Ko te meka Ko e te whakamahi i te "Index" i roto i Excel riro te rarangi tāuru me kahore he tīwae te tepu iho me te ngohi raraunga. Ko te uaua tino ki te mahi, ka e korero tatou e pā ana ki te maha nui o ngā huānga. Hei whakaoti i te raruraru e taea te āwhina i tētahi atu mahi ekselevskaya.

A feruri i te take i reira ngā te ngohi o te raina kotahi.

Ko te whānuitanga o ngā uara i roto i tenei B3 take: B6.

Te kōwhiri i te pūtau i roto i te rarangi i te tahi atu, hei tauira D1. tomo tatou i ki reira te ingoa o te hua, i te mea te tūranga e hiahia ana tatou ki te kitea, i roto i tenei take, i te "karaka". I roto i te pūtau (E1), i reira tatou e hiahia ana ki te tuhi i te tau raina hāngai, tomo "= MATCH (te D1: B3: B6; 0)" (kite tepu.). Ka rite ki te hua, i reira puta te tau 3. Ko te tokomaha i roto i te whānuitanga o te B3 tenei: B6 i roto i te parau "karaka".

Last 0 te tikanga e hiahia ana koe ki te kitea he ōritenga ki te uara D1.

Me pēhea te ki te kitea te uara kuputuhi katoa e makona kahore tetahi paearu

I roto i te puka whakaaturia i runga ake, te "MATCH" hoki kotahi anake uara (te tuatahi, arā. E. Top). Ko te aha, ki te korerotia te rārangi kei te. I roto i tenei take, e tauturu te tātai ngohi. te awhe katoa o te raraunga, me te whakamahi i te pahekotanga pātuhi «Ctrl + Shift + E tomo» kia tohaina mo ratou te whakamahi. Heoi, e kore he whakaaro ona te kaupapa o tēnei tuhinga.

Ko te "Index" me "MATCH" i roto i te Excel: Tauira

A feruri na e whai koe ki te whiriwhiri i te ngohi makona nui o anake etahi raraunga. Hoki tapatahi, whakaaro te take o te tau iti o ngā huānga. Hei tauira, i koe he pūrongo i runga i te mahi o te maha ngā rōpū o ngā ākonga, me ō rātou aro mātai. Pea hiahia koe puta te maha o ngā ākonga i roto i te pūtau H2 te aromatawaitia rite "ta'efakafiemālie".

Ki te mahi i tenei, he reira pai ki te whakamahi i tahi mahi e rua. Hei matau e me tomo koutou i roto i H2, te tuatahi whakaaro te faaiteraa tino ohie, e taea te whakamahi mō tenei whakaaro. I roto i ngā, e taea te whiwhi te uara e hiahiatia ana mā te tuhituhi ki tenei pūtau "Index = (A2: E5: 1; 2)." Tenei whakamahia matou he kē o te tauira o mua, i te mea kua tatau ā te tau aho, me te tīwae. Heoi, to matou whāinga - ki te whakaaunoa tenei tukanga. Ki te mahi i tenei, hei utu o rua, a kotahi, e tohu te haupae hiahiatia me tīwae roto i te ngohi ki te tuhi i te mahi "MATCH" hāngai te tuku i ēnei tau. Me mōhio e rapu tatou mo te parau "Oud", kei roto i te G2 pūtau me "t. 2 'i H2. I tua atu, e hiahia ana matou he ōritenga, kia rite te whakamutunga, tuatoru, te tautohe i roto i te wā e rua tohu 0.

Na, hei utu o te 1 i roto i te tātai Index (A2: E5: 1; 2) ki te kia tuhia: MATCH (G2; A2: A5: 0), a hei utu o 2 - MATCH (H2: A2: E2; 0).

I muri i whakauru i tatou: Index (A2: E5: MATCH (G2: A2: A5: 0), MATCH (H2: A2: E2; 0)). Ka rite ki te hua, te pēhi "E tomo", to tatou "10" i roto i te pūtau.

Me pēhea te ki te whakawhānui i te tātai ki te awhe riro

Ka rite ki mohiotia te, te "Index" i roto i te Excel e taea te "totoro" ki te whānuitanga etahi. I roto i te tauira kōrero i runga, ko te pūtau katoa 4 o H2 tenei: J3. I roto i tenei hononga ko reira e tika ana ki te feruri i roto i pehea ki te rave i te reira, "totoro atu" te tika tātai, me te ki raro, ki te tiki i te uara tika.

takoto te fifi matua i roto i te meka e te ngohi A2: E5 kua he wāhitau whanaunga. Hei whakatika i tēnei, kia tahuri koe i te reira ki te tino. Ki te tuhituhi i tēnei ngohi te rite $ te $ 2: $ E $ 5. kia te taua kia meatia hoki e rua mahi hanga-i roto i, arā, kia titiro ratou rite MATCH ($ G $ 2, $ He $ 2: $ He $ 5, 0) .. Na MATCH ($ H $ 2, me te $ 2: $ E 2: 0).

Ko te ahua whakamutunga o te tātai, ko te: Index ($ A $ 2: $ E $ 5, MATCH ($ te G $ 2, $ He $ 2: He $ 5: 0), MATCH ($ H $ 2, $ He $ 2: $ E $ 2: 0)).

Ka rite ki te hua, kua tatou i whakaaturia te ripanga i raro nei

Ki te tiki i te hua ko te mea e tika ana ki te kia i te uara kuputuhi tika tuhia, tae atu kore i roto i hapa anō mokowā ranei. Te kore e kore e whakaaro ki te hōtaka a ratou rite te taua.

Na e matau ana koutou ki te whakamahi i te "Index" i roto i Excel. Tauira o tona whakamahi ki te "MATCH" mohio ano koe, a ka taea ki te tono tika a ratou ki te whakaoti maha raruraru mahi koe.