Excel бағдарламасында INDEX және MATCH функциясын пайдалану жолы

2024 Автор: Abigail Brown | [email protected]. Соңғы өзгертілген: 2023-12-17 06:53

Не білу керек

• INDEX функциясын жалғыз пайдалануға болады, бірақ оның ішіне MATCH функциясын кірістіру кеңейтілген іздеуді жасайды.
• Бұл кірістірілген функция VLOOKUP функциясына қарағанда икемді және нәтижелерді тезірек бере алады.

Бұл мақала Excel бағдарламасының барлық нұсқаларында, соның ішінде Excel 2019 және Microsoft 365 нұсқаларында INDEX және MATCH функцияларын бірге пайдалану жолын түсіндіреді.

INDEX және SATCH функциялары дегеніміз не?

INDEX және SATCH - Excel іздеу функциялары. Олар өздігінен пайдалануға болатын екі бөлек функция болғанымен, оларды кеңейтілген формулалар жасау үшін біріктіруге болады.

INDEX функциясы белгілі бір таңдау ішіндегі мәнді немесе мәнге сілтемені қайтарады. Мысалы, оны деректер жиынының екінші жолындағы немесе бесінші жол мен үшінші бағандағы мәнді табу үшін пайдалануға болады.

INDEX жалғыз пайдаланылуы мүмкін болғанымен, формуладағы MATCH ұяшығы оны біршама пайдалырақ етеді. MATCH функциясы ұяшықтар ауқымында көрсетілген элементті іздейді, содан кейін ауқымдағы элементтің салыстырмалы орнын қайтарады. Мысалы, оны белгілі бір атау атаулар тізіміндегі үшінші элемент екенін анықтау үшін пайдалануға болады.

INDEX және MATCH синтаксисі мен аргументтері

Excel оларды түсінуі үшін екі функцияны да осылай жазу керек:

=ИНДЕКС(массив, жол_нөмірі, [баған_нөмірі])

• массив – формула қолданылатын ұяшықтар ауқымы. Бұл A1:D5 сияқты бір немесе бірнеше жолдар мен бағандар болуы мүмкін. Бұл қажет.
• жол_нөмірі - 2 немесе 18 сияқты мәнді қайтаратын массивтегі жол. Баған_нөмірі болмаса, ол қажет.
• баған_нөмірі – массивтегі 1 немесе 9 сияқты мәнді қайтаратын баған. Бұл міндетті емес.

=СӘйкес(іздеу_мәні, іздеу_жиымы, [сәйкестік_түрі])

• іздеу_мәні - іздеу_массивінде сәйкестендіру қажет мән. Бұл қолмен терілген немесе ұяшық сілтемесі арқылы сілтеме жасалған сан, мәтін немесе логикалық мән болуы мүмкін. Бұл қажет.
• lookup_array - қарап шығуға болатын ұяшықтар ауқымы. Бұл A2:D2 немесе G1:G45 сияқты бір жол немесе бір баған болуы мүмкін. Бұл қажет.
• сәйкестік_түрі -1, 0 немесе 1 болуы мүмкін. Ол іздеу_мәні іздеу_массивіндегі мәндермен қалай сәйкес келетінін көрсетеді (төменде қараңыз). Бұл аргумент алынып тасталса, 1 әдепкі мән болып табылады.

Қолданылатын сәйкестік түрі
Сәйкестік түрі	Бұл не істейді	Ереже	Мысалы
1	Іздеу_мәнінен кіші немесе оған тең ең үлкен мәнді табады.	Іздеу_жиымы мәндері өсу ретімен орналастырылуы керек (мысалы, -2, -1, 0, 1, 2; немесе A-Z; немесе ЖАЛҒАН, ШЫН.	іздеу_мәні 25, бірақ іздеу_жиымында ол жоқ, сондықтан оның орнына 22 сияқты келесі ең кіші санның орны қайтарылады.
0	Іздеу_мағынасына тура тең бірінші мәнді табады.	Іздеу_жиымының мәндері кез келген тәртіпте болуы мүмкін.	іздеу_мәні 25, сондықтан ол 25 орнын қайтарады.
-1	Іздеу_мәнінен үлкен немесе оған тең ең кіші мәнді табады.	Іздеу_массивінің мәндері кему ретімен орналастырылуы керек (мысалы, 2, 1, 0, -1, -2).	іздеу_мәні 25, бірақ іздеу_массивінде ол жоқ, сондықтан оның орнына 34 сияқты келесі ең үлкен санның орны қайтарылады.

Сандармен жұмыс істегенде және жуықтаулар дұрыс болған кезде, масштаб бойынша шамамен іздеуді орындау қажет болған кезде 1 немесе -1 мәнін пайдаланыңыз. Бірақ егер сәйкестік_түрін көрсетпесеңіз, 1 әдепкі мән болатынын есте сақтаңыз, егер дәл сәйкестікті шынымен қаласаңыз, нәтижелерді бұрмалауы мүмкін.

Мысал INDEX және SATCH формулалары

INDEX және MATCH бір формулаға қалай біріктіру керектігін қарастырмас бұрын, бұл функциялардың өздігінен қалай жұмыс істейтінін түсінуіміз керек.

INDEX мысалдары

=ИНДЕКС(A1:B2, 2, 2)
=ИНДЕКС(A1:B1, 1)
=ИНДЕКС(2:2, 1)=ИНДЕКС(B1:B2, 1)





Бұл бірінші мысалда әртүрлі мәндерді алу үшін пайдалануға болатын төрт INDEX формуласы бар:

=INDEX(A1:B2, 2, 2) екінші бағандағы және екінші жолдағы мәнді табу үшін A1:B2 арқылы қарайды, бұл Стейси.
=INDEX(A1:B1, 1) бірінші бағандағы мәнді табу үшін A1:B1 арқылы қарайды, ол Джон.
=INDEX(2:2, 1) бірінші бағандағы мәнді табу үшін екінші жолдағы барлығын қарап шығады, ол Тим.
=INDEX(B1:B2, 1) бірінші жолдағы мәнді табу үшін B1:B2 арқылы қарайды, ол Эми.

МАТЧ мысалдары
=МАТЧ("Стэйси", A2:D2, 0)
=СӘТКЕУ(14, D1:D2)
=СӘТТЕУ(14, D1:D2, -1)=СӘйкес(13, A1:D1, 0)





Міне, MATCH функциясының төрт оңай мысалы:

=MATCH("Stacy", A2:D2, 0) A2:D2 ауқымында Stacy іздеуде және нәтиже ретінде 3 мәнін береді.
=MATCH(14, D1:D2) D1:D2 ауқымында 14 мәнін іздеуде, бірақ ол кестеде табылмағандықтан, MATCH келесі ең үлкен мәнді табады бұл 14 мәнінен кіші немесе оған тең, бұл жағдайда ол 13 болып табылады, ол іздеу_массивінің 1-позициясында.
=MATCH(14, D1:D2, -1) оның үстіндегі формуламен бірдей, бірақ массив -1 талап ететіндей кему ретінде болмағандықтан, қате алдық.
=MATCH(13, A1:D1, 0) парақтың бірінші жолынан 13-ті іздейді, ол осы массивтің төртінші элементі болғандықтан 4 мәнін қайтарады.


INDEX-SATCH мысалдары
Міне, INDEX және MATCH бір формулада біріктіруге болатын екі мысал:
Ұяшық сілтемесін кестеден табыңыз
=ИНДЕКС(B2:B5, СӘЙКЕСТІК(F1, A2:A5))






Бұл мысал INDEX формуласының ішіне SATCH формуласын енгізеді. Мақсат - элемент нөмірін пайдаланып элемент түсін анықтау.
Суретке қарасаңыз, формулалардың өздігінен қалай жазылатынын "Бөлінген" жолдардан көре аласыз, бірақ біз оларды кірістіріп жатқандықтан, болып жатқан жағдай:

MATCH(F1, A2:A5) A2:A5 деректер жинағында F1 мәнін (8795) іздейді. Егер бағанды кері санасақ, оның 2 екенін көреміз, сондықтан MATCH функциясы дәл осылай анықтады.
INDEX массиві B2:B5, өйткені біз ақыр соңында сол бағандағы мәнді іздейміз.
INDEX функциясын енді осылай қайта жазуға болады, себебі 2 сәйкестік табылды: INDEX(B2:B5, 2, [баған_нөмірі]).
баған_нөмірі міндетті емес болғандықтан, біз мынаны қалдыру үшін оны өшіре аламыз: INDEX(B2:B5, 2).
Ендеше, бұл қалыпты INDEX формуласы сияқты, біз B2:B5 ішіндегі екінші элементтің мәнін тауып жатырмыз, ол қызыл.

Жол және баған тақырыптары бойынша іздеу
=ИНДЕКС(B2:E13, СӘйкестік(G1, A2:A13, 0), SATCH(G2, B1:E1, 0))






MATCH және INDEX мысалында біз екі жақты іздеуді жасап жатырмыз. Мамыр айында жасыл заттардан қанша ақша тапқанымызды көру. Бұл жоғарыдағы мысалға өте ұқсас, бірақ қосымша MATCH формуласы INDEX ішіне енгізілген.

МАТЧ(G1, A2:A13, 0) – осы формулада шешілген бірінші тармақ. Ол белгілі бір мәнді алу үшін A2:A13 ішінен G1 («Мамыр» сөзі) іздейді. Біз оны көрмейміз, бірақ 5.
MATCH(G2, B1:E1, 0) – екінші МАТЧ формуласы және ол біріншіге шынымен ұқсас, бірақ оның орнына G2 («Жасыл» сөзі) іздейді) B1:E1 баған тақырыптарында. Бұл 3-ке дейін шешіледі.
Енді не болып жатқанын көру үшін INDEX формуласын келесідей қайта жаза аламыз: =INDEX(B2:E13, 5, 3). Бұл $180 қайтаратын бесінші жол мен үшінші баған үшін B2:E13 бүкіл кестеден қарастырылады.


МАТЧ және INDEX ережелері
Бұл функциялармен формулаларды жазғанда есте сақтау керек бірнеше нәрсе бар:

MATCH регистрді ескермейді, сондықтан мәтін мәндерін сәйкестендіру кезінде бас және кіші әріптер бірдей қолданылады.
СӘйкестік_түрі 0 болса және іздеу_мәні табылмаса, сәйкестік_түрі -1 болса және іздеу_жиымы кему ретінде болмаса, сәйкестік_түрі 1 болса және іздеу_жиымы өсу бағытында болмаса MATCH N/A қайтарады. реті және іздеу_жиымы бір жол немесе баған болмаса.
Сәйкестік_түрі 0 және іздеу_мәні мәтін жолы болса, іздеу_мәні аргументінде қойылмалы таңбаны пайдалануға болады. Сұрақ белгісі кез келген жалғыз таңбаға, ал жұлдызша кез келген таңбалар тізбегіне сәйкес келеді (мысалы.г., =СӘйкестендіру("Jo", 1:1, 0)). Нақты сұрақ белгісін немесе жұлдызшаны табу үшін MATCH функциясын пайдалану үшін алдымен ~ теріңіз.
INDEX мәні REF! егер жол_нөмірі және баған_нөмірі массив ішіндегі ұяшықты көрсетпесе.


Қатысты Excel функциялары
СӘйкестендіргіш функциясы ІЗДЕУ функциясына ұқсас, бірақ SATCH элементтің орнына элементтің орнын қайтарады.
VLOOKUP – Excel бағдарламасында пайдалануға болатын басқа іздеу функциясы, бірақ кеңейтілген іздеулер үшін INDEX талап ететін MATCH-тен айырмашылығы, VLOOKUP формулалары тек бір функцияны қажет етеді.