როგორ გამოვიყენოთ INDEX და MATCH ფუნქცია Excel-ში

2024 ავტორი: Abigail Brown | [email protected]. ბოლოს შეცვლილი: 2023-12-17 06:52

რა უნდა იცოდე

• INDEX ფუნქციის გამოყენება შესაძლებელია ცალკე, მაგრამ მასში MATCH ფუნქციის ჩასმა ქმნის გაფართოებულ ძიებას.
• ეს ჩადგმული ფუნქცია უფრო მოქნილია ვიდრე VLOOKUP და შეუძლია უფრო სწრაფად გამოიღოს შედეგი.

ეს სტატია განმარტავს, თუ როგორ გამოიყენოთ INDEX და MATCH ფუნქციები ერთად Excel-ის ყველა ვერსიაში, მათ შორის Excel 2019 და Microsoft 365.

რა არის INDEX და MATCH ფუნქციები?

INDEX და MATCH არის Excel-ის საძიებო ფუნქციები. მიუხედავად იმისა, რომ ისინი წარმოადგენენ ორ სრულიად ცალკეულ ფუნქციას, რომელთა გამოყენება შესაძლებელია დამოუკიდებლად, ისინი ასევე შეიძლება გაერთიანდეს გაფართოებული ფორმულების შესაქმნელად.

INDEX ფუნქცია აბრუნებს მნიშვნელობას ან მნიშვნელობაზე მითითებას კონკრეტული შერჩევის შიგნით. მაგალითად, ის შეიძლება გამოყენებულ იქნას მონაცემთა ნაკრების მეორე მწკრივში მნიშვნელობის საპოვნელად, ან მეხუთე მწკრივში და მესამე სვეტში.

მიუხედავად იმისა, რომ INDEX შეიძლება გამოყენებულ იქნას ცალკე, MATCH-ის ჩადგმა ფორმულაში მას უფრო სასარგებლოს ხდის. MATCH ფუნქცია ეძებს მითითებულ ელემენტს უჯრედების დიაპაზონში და შემდეგ აბრუნებს ელემენტის შედარებით პოზიციას დიაპაზონში. მაგალითად, ის შეიძლება გამოყენებულ იქნას იმის დასადგენად, რომ კონკრეტული სახელი არის მესამე ელემენტი სახელების სიაში.

INDEX და MATCH სინტაქსი და არგუმენტები

ასე უნდა დაიწეროს ორივე ფუნქცია, რათა Excel გაიგოს ისინი:

=INDEX(მასივი, მწკრივის_რაოდენობა, [სვეტის_რაოდენობა])

• მასივი არის უჯრედების დიაპაზონი, რომელსაც გამოიყენებს ფორმულა. ეს შეიძლება იყოს ერთი ან მეტი მწკრივი და სვეტი, როგორიცაა A1:D5. ეს აუცილებელია.
• სტრიქონი_რაოდენობა არის მწკრივი მასივში, საიდანაც უნდა დაბრუნდეს მნიშვნელობა, როგორიცაა 2 ან 18. ეს აუცილებელია, თუ არ არის სვეტის_num.
• column_num არის სვეტი მასივში, საიდანაც უნდა დაბრუნდეს მნიშვნელობა, როგორიცაა 1 ან 9. ეს არჩევითია.

=MATCH(ძიების_მნიშვნელობა, საძიებო_მასივი, [შემთხვევის_ტიპი])

• lookup_value არის მნიშვნელობა, რომლის შედარებაც გსურთ lookup_მასივში. ეს შეიძლება იყოს რიცხვი, ტექსტი ან ლოგიკური მნიშვნელობა, რომელიც აკრეფილია ხელით ან მოხსენიებულია უჯრედის მითითებით. ეს აუცილებელია.
• lookup_array არის უჯრედების დიაპაზონი, რომელიც უნდა გადახედოთ. ეს შეიძლება იყოს ერთი მწკრივი ან ერთი სვეტი, როგორიცაა A2:D2 ან G1:G45. ეს აუცილებელია.
• შემთხვევის_ტიპი შეიძლება იყოს -1, 0, ან 1. ის განსაზღვრავს, თუ როგორ შეესაბამება lookup_value მნიშვნელობებს lookup_array-ში (იხ. ქვემოთ). 1 არის ნაგულისხმევი მნიშვნელობა, თუ ეს არგუმენტი გამოტოვებულია.

რომელი შესატყვისი ტიპი გამოიყენება
შემთხვევის ტიპი	რას აკეთებს	წესი	მაგალითი
1	იპოვებს უდიდეს მნიშვნელობას, რომელიც ნაკლებია ან ტოლია lookup_მნიშვნელობის.	საძიებელი_მასივის მნიშვნელობები უნდა განთავსდეს ზრდადი თანმიმდევრობით (მაგ., -2, -1, 0, 1, 2; ან A-Z;, ან FALSE, TRUE.	lookup_value არის 25, მაგრამ ის აკლია lookup_array-ს, ამიტომ მის ნაცვლად დაბრუნდა შემდეგი უმცირესი რიცხვის პოზიცია, როგორიცაა 22.
0	იპოვებს პირველ მნიშვნელობას, რომელიც ზუსტად უდრის lookup_მნიშვნელობას.	ძიების_მასივის მნიშვნელობები შეიძლება იყოს ნებისმიერი თანმიმდევრობით.	lookup_value არის 25, ამიტომ ის აბრუნებს 25-ის პოზიციას.
-1	იპოვებს უმცირეს მნიშვნელობას, რომელიც მეტია ან ტოლია lookup_მნიშვნელობის.	საძიებელი_მასივის მნიშვნელობები უნდა განთავსდეს კლებადობით (მაგ., 2, 1, 0, -1, -2).	lookup_value არის 25, მაგრამ ის აკლია lookup_array-ს, ამიტომ დაბრუნებულია შემდეგი უდიდესი რიცხვის პოზიცია, როგორიცაა 34.

გამოიყენეთ 1 ან -1 იმ დროისთვის, როდესაც გჭირდებათ მიახლოებითი ძიება მასშტაბის გასწვრივ, მაგალითად, როდესაც საქმე გვაქვს ციფრებთან და როდესაც მიახლოებები ნორმალურია. მაგრამ გახსოვდეთ, რომ თუ არ მიუთითებთ match_type-ს, ნაგულისხმევი იქნება 1, რამაც შეიძლება შეცვალოს შედეგები, თუ ნამდვილად გსურთ ზუსტი დამთხვევა.

მაგალითი INDEX და MATCH ფორმულები

სანამ გადავხედავთ, თუ როგორ გავაერთიანოთ INDEX და MATCH ერთ ფორმულაში, უნდა გავიგოთ, როგორ მუშაობს ეს ფუნქციები დამოუკიდებლად.

INDEX მაგალითები

=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2, 1)





პირველ მაგალითში არის ოთხი INDEX ფორმულა, რომელიც შეგვიძლია გამოვიყენოთ სხვადასხვა მნიშვნელობების მისაღებად:

=INDEX(A1:B2, 2, 2) იკვლევს A1:B2 მნიშვნელობის საპოვნელად მეორე სვეტსა და მეორე რიგში, რომელიც არის Stacy.
=INDEX(A1:B1, 1) ეძებს A1:B1 მნიშვნელობის საპოვნელად პირველ სვეტში, რომელიც არის Jon.
=INDEX(2:2, 1) ათვალიერებს ყველაფერს მეორე რიგში, რათა აღმოაჩინოს მნიშვნელობა პირველ სვეტში, რომელიც არის Tim.
=INDEX(B1:B2, 1) იკვლევს B1:B2-ს, რათა აღმოაჩინოს მნიშვნელობა პირველ რიგში, რომელიც არის Amy.

MATCH მაგალითები
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)





აქ არის MATCH ფუნქციის ოთხი მარტივი მაგალითი:

=MATCH("Stacy", A2:D2, 0) ეძებს Stacy-ს A2:D2 დიაპაზონში და შედეგად აბრუნებს 3.
=MATCH(14, D1:D2) ეძებს 14-ს D1:D2 დიაპაზონში, მაგრამ რადგან ის არ არის ნაპოვნი ცხრილში, MATCH პოულობს შემდეგ უდიდეს მნიშვნელობას ეს არის 14-ზე ნაკლები ან ტოლი, რაც ამ შემთხვევაში არის 13, რომელიც არის lookup_მასივის 1 პოზიციაზე.
=MATCH(14, D1:D2, -1) იდენტურია მის ზემოთ მოცემული ფორმულისა, მაგრამ ვინაიდან მასივი არ არის კლებადობით, როგორც ამას მოითხოვს -1, ჩვენ ვიღებთ შეცდომას.
=MATCH(13, A1:D1, 0) ეძებს 13-ს ფურცლის პირველ რიგში, რომელიც აბრუნებს 4-ს, რადგან ის მეოთხე ელემენტია ამ მასივში.


INDEX-MATCH მაგალითები
აქ არის ორი მაგალითი, სადაც შეგვიძლია გავაერთიანოთ INDEX და MATCH ერთ ფორმულაში:
იპოვეთ უჯრედის მითითება ცხრილში
=INDEX(B2:B5, MATCH(F1, A2:A5))






ეს მაგალითი ათავსებს MATCH ფორმულას INDEX ფორმულაში. მიზანია ნივთის ფერის იდენტიფიცირება ნივთის ნომრის გამოყენებით.
თუ დააკვირდებით სურათს, ხედავთ "განცალკევებულ" სტრიქონებში, როგორ ჩაიწერება ფორმულები დამოუკიდებლად, მაგრამ რადგან ჩვენ მათ ვაწყობთ, ეს არის ის, რაც ხდება:

MATCH(F1, A2:A5) ეძებს F1 მნიშვნელობას (8795) მონაცემთა ნაკრებში A2:A5. თუ ჩვენ ჩამოვთვლით სვეტს, დავინახავთ, რომ ის არის 2, ასე რომ, MATCH ფუნქციამ ახლახან გაარკვია.
INDEX მასივი არის B2:B5, რადგან ჩვენ საბოლოოდ ვეძებთ მნიშვნელობას ამ სვეტში.
INDEX ფუნქცია ახლა შეიძლება გადაიწეროს ასე, რადგან 2 არის ის, რაც MATCH იპოვა: INDEX(B2:B5, 2, [სვეტის_რაოდენობა]).
რადგან სვეტის_რაოდენობა არასავალდებულოა, ჩვენ შეგვიძლია წავშალოთ ის, რომ დარჩეს შემდეგი: INDEX(B2:B5, 2).
ახლა, ეს ჰგავს ჩვეულებრივ INDEX ფორმულას, სადაც ჩვენ ვპოულობთ მეორე ელემენტის მნიშვნელობას B2:B5-ში, რომელიც არის წითელი.

მოძებნა მწკრივისა და სვეტის სათაურების მიხედვით
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))






MATCH-ისა და INDEX-ის ამ მაგალითში ჩვენ ვაკეთებთ ორმხრივ ძიებას. იდეა მდგომარეობს იმაში, რომ დავინახოთ, რამდენი ფული გამოვიმუშავეთ მწვანე ნივთებისგან მაისში. ეს ნამდვილად ჰგავს ზემოთ მოცემულ მაგალითს, მაგრამ დამატებითი MATCH ფორმულა ჩასმულია INDEX-ში.

MATCH(G1, A2:A13, 0) არის პირველი ამოხსნილი ელემენტი ამ ფორმულაში. ის ეძებს G1-ს (სიტყვა „მაისი“) A2:A13-ში კონკრეტული მნიშვნელობის მისაღებად. ჩვენ არ ვხედავთ მას აქ, მაგრამ ეს არის 5.
MATCH(G2, B1:E1, 0) არის მეორე MATCH ფორმულა და ის ნამდვილად ჰგავს პირველს, მაგრამ სანაცვლოდ ეძებს G2-ს (სიტყვა "მწვანე") სვეტების სათაურებში B1:E1. ეს წყვეტს 3.
ახლა შეგვიძლია გადავიწეროთ INDEX ფორმულა ასე, რათა ვიზუალურად წარმოვიდგინოთ რა ხდება: =INDEX(B2:E13, 5, 3). ეს ეძებს მთელ ცხრილში, B2:E13, მეხუთე მწკრივს და მესამე სვეტს, რომელიც აბრუნებს $180.


MATCH და INDEX წესები
არის რამდენიმე რამ, რაც უნდა გაითვალისწინოთ ამ ფუნქციებით ფორმულების დაწერისას:

MATCH არ არის რეგისტრირებული, ამიტომ დიდი და პატარა ასოები ერთნაირად განიხილება ტექსტის მნიშვნელობების შესატყვისისას.
MATCH აბრუნებს N/A-ს მრავალი მიზეზის გამო: თუ შესატყვისი_ტიპი არის 0 და საძიებელი_მნიშვნელობა ვერ მოიძებნა, თუ შესატყვისი_ტიპი არის -1 და ძიება_მასივი არ არის კლებადობით, თუ შესატყვისი_ტიპი არის 1 და ძიება_მასივი არ არის აღმავალი შეკვეთა და თუ lookup_მასივი არ არის ერთი მწკრივი ან სვეტი.
შეგიძლიათ გამოიყენოთ wildcard სიმბოლო lookup_value არგუმენტში, თუ match_type არის 0 და lookup_value არის ტექსტის სტრიქონი. კითხვის ნიშანი ემთხვევა რომელიმე სიმბოლოს და ვარსკვლავი ემთხვევა სიმბოლოების ნებისმიერ თანმიმდევრობას (მაგ.გ., =MATCH("ჯო", 1:1, 0)). იმისათვის, რომ გამოიყენოთ MATCH რეალური კითხვის ნიშნის ან ვარსკვლავის საპოვნელად, ჯერ აკრიფეთ ~.
INDEX აბრუნებს REF! თუ row_num და column_num არ მიუთითებს მასივის უჯრედზე.


დაკავშირებული Excel ფუნქციები
MATCH ფუნქცია ჰგავს LOOKUP-ს, მაგრამ MATCH აბრუნებს ელემენტის პოზიციას თავად ელემენტის ნაცვლად.
VLOOKUP არის კიდევ ერთი საძიებო ფუნქცია, რომელიც შეგიძლიათ გამოიყენოთ Excel-ში, მაგრამ განსხვავებით MATCH-ისგან, რომელიც საჭიროებს INDEX-ს გაფართოებული საძიებლად, VLOOKUP ფორმულებს მხოლოდ ეს ერთი ფუნქცია სჭირდება.