Неліктен INDEX және MATCH Excel бағдарламасындағы VLOOKUP қарағанда жақсы

Біз бұрын жаңадан бастаушыларға VLOOKUP (ағылш. VLOOKUP, аббревиатура «тік іздеу функциясы» дегенді білдіреді) негізгі функцияларын қалай пайдалану керектігін түсіндірдік. Ал тәжірибелі пайдаланушыларға бірнеше күрделі формулалар көрсетілді.

Және бұл мақалада біз тік іздеумен жұмыс істеудің басқа әдісі туралы ақпарат беруге тырысамыз.

Сіз: «Бұл не үшін қажет?» Деген сұрақ туындауы мүмкін. Және бұл барлық ықтимал іздеу әдістерін көрсету үшін қажет. Сонымен қатар, көптеген VLOOKUP шектеулері қалаған нәтижеге қол жеткізуге кедергі жасайды. Осыған байланысты, INDEX( ) MATCH( ) әлдеқайда функционалды және әртүрлі және олардың шектеулері де аз.

Негіздер INDEX MATCH

Бұл нұсқаулықтың мақсаты осы мүмкіндіктің қаншалықты жақсы екенін көрсету болғандықтан, біз Оның жұмыс істеу принциптеріне қатысты негізгі ақпаратты қарастырайық. Біз мысалдарды көрсетеміз, сонымен қатар неге бұл VLOOKUP () қарағанда жақсы екенін қарастырамыз.

INDEX функциясының синтаксисі және қолданылуы

Бұл функция баған немесе жол нөмірі негізінде көрсетілген іздеу аймақтары арасында қажетті мәнді табуға көмектеседі. Синтаксис:

=INDEX(массив, жол нөмірі, баған нөмірі):

• массив – іздеу жүргізілетін аймақ;
• жол нөмірі – көрсетілген массивте ізделетін жолдың нөмірі. Жол нөмірі белгісіз болса, баған нөмірін көрсету керек;
• баған нөмірі – көрсетілген массивте табылатын бағанның нөмірі. Мән белгісіз болса, жол нөмірі қажет.

Қарапайым формуланың мысалы:

=ИНДЕКС(A1:S10,2,3)

Функция A1 мен C10 аралығында іздейді. Сандар қажетті мәнді қай жолдан (2) және (3) бағаннан көрсету керектігін көрсетеді. Нәтиже C2 ұяшығы болады.

Өте қарапайым, солай ма? Бірақ нақты құжаттармен жұмыс істегенде, сізде баған нөмірлері немесе ұяшықтар туралы ақпарат болуы екіталай. MATCH() функциясы осыған арналған.

MATCH функциясының синтаксисі және қолданылуы

MATCH() функциясы қажетті мәнді іздейді және көрсетілген іздеу аймағында оның шамамен санын көрсетеді.

Searchpos() синтаксисі келесідей көрінеді:

=MATCH(іздеу үшін мән, іздеу үшін массив, сәйкестік түрі)

• іздеу мәні – табуға болатын сан немесе мәтін;
• іздеу массиві – іздеу жүргізілетін аймақ;
• сәйкестік түрі – нақты мәнді немесе оған жақын мәндерді іздеу керектігін анықтайды:
  • 1 (немесе мән көрсетілмеген) – көрсетілген мәнге тең немесе одан аз ең үлкен мәнді қайтарады;
  • 0 – ізделетін мәнмен дәл сәйкестікті көрсетеді. INDEX() MATCH() комбинациясында әрқашан дерлік дәл сәйкестік қажет болады, сондықтан біз 0 деп жазамыз;
  • -1 – формулада көрсетілген мәннен үлкен немесе оған тең ең кіші мәнді көрсетеді. Сұрыптау кему ретімен жүзеге асырылады.

Мысалы, В1:В3 диапазонында Нью-Йорк, Париж, Лондон тіркелген. Төмендегі формула 3 санын көрсетеді, себебі Лондон тізімде үшінші орында:

=EXPOSE(Лондон,B1:B3,0)

INDEX MATCH функциясымен жұмыс істеу жолы 

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

=INDEX(мән қайтарылатын баған, MATCH(іздейтін мән, ізделетін баған, 0))

Оның қалай жұмыс істейтінін әлі де түсіну қиын ба? Мүмкін мысал жақсырақ түсіндіреді. Сізде әлемдік астаналар мен олардың тұрғындарының тізімі бар делік:

Белгілі бір астананың, мысалы, Жапония астанасының халқының санын білу үшін келесі формуланы қолданамыз:

=INDEX(C2:C10, MATCH(Жапония, A2:A10,0))

Түсіндіру:

• MATCH() функциясы A2:A10 массивінен «Жапония» мәнін іздейді және 3 санын қайтарады, себебі Жапония тізімдегі үшінші мән болып табылады. 
• Бұл көрсеткіш барадыжол нөміріINDEX() формуласында ” параметрін таңдап, функцияға осы жолдан мәнді басып шығару керектігін айтады.

Сонымен, жоғарыдағы формула стандартты формулаға айналады ИНДЕКС(C2:C10,3). Формула C2 ұяшықтарынан C10-ға дейін іздейді және осы ауқымдағы үшінші ұяшықтан, яғни C4 деректерін қайтарады, себебі кері санақ екінші жолдан басталады.

Формуладағы қала атын жазуды қаламайсыз ба? Содан кейін оны кез келген ұяшыққа жазыңыз, F1 деп айтыңыз және оны MATCH() формуласында сілтеме ретінде пайдаланыңыз. Сіз динамикалық іздеу формуласымен аяқталасыз:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Маңызды! Жолдар саны массив INDEX() ішіндегі жолдар санымен бірдей болуы керек массив деп есептеледі MATCH() ішінде, әйтпесе қате нәтиже аласыз.

Бір минут күтіңіз, неге VLOOKUP() формуласын қолданбасқа?

=VLOOKUP(F1, A2:C10, 3, False)

 INDEX MATCH-тің барлық осы күрделіліктерін анықтауға тырысып, уақытты жоғалтудың мәні неде?

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

INDEX MATCH немесе VLOOKUP

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

INDEX() және MATCH() функцияларының VLOOKUP() функциясынан негізгі артықшылықтары мыналар:

• Оңнан солға қарай іздеу. VLOOKUP() оңнан солға қарай іздей алмайды, сондықтан сіз іздеген мәндер әрқашан кестенің ең сол жақ бағандарында болуы керек. Бірақ INDEX() және MATCH() мұны еш қиындықсыз өңдей алады. Бұл мақала сізге іс жүзінде қалай көрінетінін айтады: сол жақта қажетті мәнді қалай табуға болады.

1. Бағандарды қауіпсіз қосу немесе алып тастау. VLOOKUP() формуласы бағандарды жою немесе қосу кезінде дұрыс емес нәтижелерді көрсетеді, себебі VLOOKUP() сәтті болуы үшін нақты баған нөмірін қажет етеді. Әрине, бағандар қосылған немесе жойылған кезде олардың сандары да өзгереді. 

Ал INDEX() және MATCH() формулаларында жеке бағандар емес, бағандар ауқымы көрсетілген. Нәтижесінде формуланы әр уақытта жаңартпай-ақ бағандарды қауіпсіз қосуға және жоюға болады.

1. Іздеу көлемдеріне шектеулер жоқ. VLOOKUP() функциясын пайдаланған кезде іздеу шартының жалпы саны 255 таңбадан аспауы керек, әйтпесе сіз #VALUE аласыз! Сондықтан деректеріңізде таңбалар көп болса, INDEX() және MATCH() ең жақсы нұсқа болып табылады.
2. Жоғары өңдеу жылдамдығы. Егер сіздің кестелеріңіз салыстырмалы түрде кішкентай болса, сіз ешқандай айырмашылықты байқай алмайсыз. Бірақ, егер кестеде жүздеген немесе мыңдаған жолдар болса және сәйкесінше жүздеген және мыңдаған формулалар болса, INDEX () және MATCH () VLOOKUP () қарағанда әлдеқайда жылдамырақ жұмыс істейді. Мәселе мынада, Excel бүкіл кестені өңдеудің орнына тек формулада көрсетілген бағандарды өңдейді. 

VLOOKUP() функциясының өнімділігі жұмыс парағында VLOOKUP() және SUM() сияқты формулалар көп болса, әсіресе байқалады. Жиымдағы әрбір мәнді талдау үшін VLOOKUP() функцияларын бөлек тексеру қажет. Сондықтан Excel үлкен көлемдегі ақпаратты өңдеуі керек және бұл жұмысты айтарлықтай баяулатады.

Формула мысалдары 

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

Оңнан солға қарай іздеу формуласы

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

Мысалы, кестеміздің сол жағына деңгейлік бағаны қосып, еліміздің астанасы халық саны бойынша қандай орынды алып жатқанын анықтауға тырысамыз.

G1 ұяшығына табылатын мәнді жазамыз, содан кейін C1:C10 ауқымында іздеу үшін келесі формуланы қолданамыз және сәйкес мәнді A2:A10 ішінен қайтарамыз:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Шақыру. Бұл формуланы бірнеше ұяшықтар үшін пайдалануды жоспарласаңыз, абсолютті адрестеу арқылы ауқымдарды түзетіңіз (мысалы, $A$2: $A$10 және $C$2: 4C$10).

КӨРСЕТКІШ КӨБІРЕК АШЫҚТАЙДЫ  бағандар мен жолдар бойынша іздеу

Жоғарыда келтірілген мысалдарда біз бұл функцияларды алдын ала анықталған жолдар ауқымынан мәндерді қайтару үшін VLOOKUP() функциясының орнына пайдаландық. Бірақ матрицалық немесе екі жақты іздеу қажет болса ше?

Күрделі естіледі, бірақ мұндай есептеулер формуласы стандартты INDEX() MATCH() формуласына ұқсас, тек бір айырмашылығы бар: MATCH() формуласы екі рет қолданылуы керек. Бірінші рет жол нөмірін алу үшін, ал екінші рет баған нөмірін алу үшін:

=INDEX(массив, MATCH(тік іздеу мәні, іздеу бағаны, 0), MATCH(көлденең іздеу мәні, іздеу жолы, 0))

Төмендегі кестеге қарап, формула құрастырып көрейік INDEX() EXPRESS() EXPRESS() таңдалған жылдағы белгілі бір елдегі демографиялық мәліметтерді көрсету үшін.

Мақсатты ел G1 ұяшығында (тік іздеу) және мақсатты жыл G2 ұяшығында (көлденең іздеу). Формула келесідей болады:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Бұл формула қалай жұмыс істейді

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

• СӘЙКЕСТІК(G1,A2:A11,0) – (G1) мәнін A2:A11 диапазонында іздейді және осы мәннің нөмірін көрсетеді, біздің жағдайда ол 2;
• ІЗДЕУ(G2,B1:D1,0) – B2:D1 ауқымында мәнді (G1) іздейді. Бұл жағдайда нәтиже 3 болды.

Табылған жол және баған нөмірлері INDEX() формуласындағы сәйкес мәнге жіберіледі:

=ИНДЕКС(B2:D11,2,3)

Нәтижесінде бізде B2:D3 ауқымындағы 2 жол мен 11 бағанның қиылысында орналасқан ұяшықтағы мән бар. Ал формула D3 ұяшығында болатын қажетті мәнді көрсетеді.

INDEX және MATCH көмегімен бірнеше шарттар бойынша іздеңіз

VLOOKUP() нұсқаулығын оқыған болсаңыз, сіз бірнеше іздеу формулаларын қолданып көрген боларсыз. Бірақ бұл іздеу әдісінің бір маңызды шектеуі бар - көмекші бағанды ​​қосу қажеттілігі.

Бірақ жақсы жаңалық - бұл INDEX() және MATCH() көмегімен жұмыс парағын өңдеу немесе өзгерту қажетсіз бірнеше шарттарды іздеуге болады.

Міне INDEX() MATCH() үшін жалпы көп шартты іздеу формуласы:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Ескерту: бұл формула пернелер тіркесімімен бірге қолданылуы керек CTRL+SHIFT+ENTER.

Сізге 2 шарт негізінде іздеген мәнді табу керек делік: сатып алушы и Өнім.

Бұл келесі формуланы қажет етеді:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Бұл формулада C2:C10 - іздеу жүргізілетін ауқым, F1 – бұл жағдай, A2:A10 — шартты салыстыру диапазоны, F2 – 2-шарт, V2: V10 – 2-шартты салыстыру диапазоны.

Формуламен жұмыстың соңында комбинацияны басуды ұмытпаңыз CTRL + SHIFT + ENTER – Excel мысалда көрсетілгендей бұйра жақшалармен формуланы автоматты түрде жабады:

Жұмысыңыз үшін массив формуласын пайдаланғыңыз келмесе, формулаға басқа INDEX() қосып, ENTER пернесін басыңыз, ол мысалдағыдай болады:

Бұл формулалар қалай жұмыс істейді

Бұл формула стандартты INDEX() MATCH() формуласымен бірдей жұмыс істейді. Бірнеше шарттарды іздеу үшін сіз жай ғана дұрыс және дұрыс емес жеке шарттарды білдіретін бірнеше Жалған және Шын шарттарын жасайсыз. Содан кейін бұл шарттар массивтің барлық сәйкес элементтеріне қолданылады. Формула False және True аргументтерін сәйкесінше 0 және 1-ге түрлендіреді және 1 жолда табылған сәйкес мәндер болатын массив шығарады. MATCH() 1-ге сәйкес келетін бірінші мәнді тауып, оны INDEX() формуласына береді. Және ол, өз кезегінде, қажетті бағаннан көрсетілген жолдағы қажетті мәнді қайтарады.

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

Бұл формуланың артындағы логиканың ұзақ түсіндірмесі. Қосымша ақпарат алу үшін мақаланы оқыңыз «Бірнеше шарты бар INDEX MATCH«.

INDEX және MATCH бойынша AVERAGE, MAX және MIN

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

INDEX MATCH және MAX

D бағанындағы ең үлкен мәнді табу және оны С бағанында көрсету үшін мына формуланы пайдаланыңыз: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH және MIN

D бағанындағы ең кіші мәнді табу және оны С бағанында көрсету үшін келесі формуланы пайдаланыңыз:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

Іздеу индексі және SERPENT

D бағанындағы орташа мәнді табу және бұл мәнді C тілінде көрсету үшін:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Деректеріңіздің жазылу жолына байланысты MATCH() параметрінің үшінші аргументі 1, 0 немесе -1 болады:

• егер бағандар өсу ретімен сұрыпталса, 1-ні орнатыңыз (онда формула орташа мәннен аз немесе оған тең ең үлкен мәнді есептейді);
• егер сұрыптау кему болса, онда -1 (формула орташадан үлкен немесе оған тең ең төменгі мәнді шығарады);
• егер іздеу массивінде орташа мәнге дәл тең мән болса, оны 0-ге орнатыңыз. 

 Біздің мысалда популяция кему ретімен сұрыпталған, сондықтан біз -1 қоямыз. Нәтиже Токио болып табылады, өйткені халық саны (13,189 000) орташа мәнге (XNUMX) жақын.

VLOOKUP() функциясы да осындай есептеулерді орындай алады, бірақ тек массив формуласы ретінде: AVERAGE, MIN және MAX бар VLOOKUP.

INDEX MATCH және ESND/IFERROR

Сіз формула қажетті мәнді таба алмаса, қате жіберетінін байқаған боларсыз # Жоқ. Сіз стандартты қате туралы хабарды көбірек ақпаратпен алмастыра аласыз. Мысалы, формуладағы аргументті орнатыңыз ХNUMX-шы жылы:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Осы формула арқылы кестеде жоқ деректерді енгізсеңіз, пішін сізге көрсетілген хабарламаны береді.

Егер сіз барлық қателерді ұстағыңыз келсе, қоспағанда ХNUMX-шы жылы пайдаланылуы мүмкін ҚАТЕЛІК:

=ҚАУІЛІК(ИНДЕКС(C2:C10, MATCH(F1,A2:A10,0)), "Бірдеңе дұрыс болмады!")

Бірақ қателерді осылай жасыру жақсы идея емес екенін есте сақтаңыз, өйткені стандартты қателер формуладағы бұзушылықтар туралы хабарлайды.

INDEX MATCH() функциясын пайдалану бойынша нұсқаулық сізге пайдалы болды деп үміттенеміз.