მავნე პროგრამა არის შემაშფოთებელი ან საშიში პროგრამა, რომელიც...
ნამდვილად უფრო ადვილია ძიება ერთი, თუმცა დიდი, მაგრამ მთლიანი ცხრილის ან უჯრედების მიმდებარე დიაპაზონში, ვიდრე რამდენიმე ცხრილის მეშვეობით, რომელიც იყოფა ნაწილებად, რომლებიც მიმოფანტულია სხვადასხვა არამიმდებარე დიაპაზონში ან თუნდაც ცალკეულ ფურცლებზე. იმ შემთხვევაშიც კი, თუ ერთდროულად რამდენიმე მაგიდაზე ავტომატურ ძიებას ახორციელებთ, შეიძლება წარმოიშვას მნიშვნელოვანი დაბრკოლებები. და ყველა მონაცემის ერთ ცხრილში მოთავსება რთულია, ზოგჯერ თითქმის შეუძლებელი. კონკრეტული მაგალითის გამოყენებით, ჩვენ ვაჩვენებთ სწორ გადაწყვეტას Excel-ში რამდენიმე ცხრილის ერთდროული ძიებისთვის.
ერთდროული ძებნა მრავალ დიაპაზონში
ვიზუალური მაგალითისთვის, მოდით შევქმნათ სამი მარტივი ცალკეული ცხრილი, რომლებიც მდებარეობს იმავე ფურცლის არამიმდებარე დიაპაზონში:
თქვენ უნდა მოძებნოთ 20 ცალი პროდუქტის წარმოებისთვის საჭირო რაოდენობა. სამწუხაროდ, ეს მონაცემები სხვადასხვა სვეტებსა და რიგებშია. ამიტომ, პირველ რიგში, თქვენ უნდა შეამოწმოთ რამდენი დრო დასჭირდება ამ პროდუქტების წარმოებას (პირველი ცხრილი).
მიღებული მონაცემებიდან გამომდინარე, აუცილებელია დაუყოვნებლივ გადავიდეთ სხვა ცხრილში ძებნაზე და იპოვოთ რამდენი მუშა უნდა იყოს ჩართული წარმოების მოცემულ მოცულობაში. მიღებული შედეგი უნდა შევადაროთ მესამე ცხრილის მონაცემებს. ამრიგად, სამ ცხრილის ერთ საძიებო ოპერაციაში ჩვენ დაუყოვნებლივ განვსაზღვრავთ საჭირო ხარჯებს (თანხას).
Excel-ის საშუალო მომხმარებელი ეძებს გამოსავალს ფორმულების გამოყენებით, რომელიც დაფუძნებულია ფუნქციებზე, როგორიცაა VLOOKUP. ასევე შეასრულებდა ძიებას 3 ეტაპად (თითოეული ცხრილისთვის ცალ-ცალკე). გამოდის, რომ თქვენ შეგიძლიათ დაუყოვნებლივ მიიღოთ დასრულებული შედეგი სპეციალური ფორმულის გამოყენებით ძიების მხოლოდ 1 ეტაპზე. Ამისთვის:
- E6 უჯრედში შეიყვანეთ მნიშვნელობა 20, რაც საძიებო მოთხოვნის პირობაა.
- E7 უჯრედში შეიყვანეთ შემდეგი ფორმულა:
წარმოების ღირებულება 20 ცალი. გარკვეული პროდუქტი.
როგორ მუშაობს ფორმულა VLOOKUP-თან მრავალ ცხრილში:
ამ ფორმულის მოქმედების პრინციპი ეფუძნება VLOOKUP ფუნქციის (პირველი) ყველა არგუმენტის თანმიმდევრულ ძიებას. პირველი, მესამე VLOOKUP ფუნქცია ეძებს პირველ ცხრილს იმ დროის რაოდენობას, რომელიც საჭიროა 20 ცალი პროდუქტის წარმოებისთვის, რომელიც მითითებულია როგორც მნიშვნელობა E6 უჯრედისთვის (რომელიც შემდეგ შეიძლება შეიცვალოს საჭიროების შემთხვევაში). შემდეგ მეორე VLOOKUP ფუნქცია ეძებს მნიშვნელობას მთავარი ფუნქციის პირველი არგუმენტისთვის.
მესამე ფუნქციის ძიების შედეგად ვიღებთ მნიშვნელობას 125, რომელიც მეორე ფუნქციის პირველი არგუმენტია. ყველა პარამეტრის მიღების შემდეგ, მეორე ფუნქცია ეძებს მეორე ცხრილში წარმოებისთვის საჭირო მუშაკების რაოდენობას. შედეგად, დაბრუნდა მნიშვნელობა 5, რომელსაც გამოიყენებს მთავარი ფუნქცია. ყველა მიღებულ მონაცემზე დაყრდნობით, ფორმულა აბრუნებს გაანგარიშების საბოლოო შედეგს. კერძოდ, 20 ცალი კონკრეტული პროდუქტის დასამზადებლად საჭიროა 1750 დოლარის ოდენობა.
ამ პრინციპის მიხედვით, შეგიძლიათ გამოიყენოთ ფორმულები VLOOKUP ფუნქციისთვის რამდენიმე ფურცლიდან.
Microsoft Excel ხშირად მუშაობს დიდი რაოდენობით ინფორმაციასთან. ის ქმნის უზარმაზარ ცხრილებს ათასობით მწკრივით, სვეტით და პოზიციით. ძნელია იპოვოთ რაიმე კონკრეტული მონაცემები ასეთ მასივში. და ზოგჯერ ეს საერთოდ შეუძლებელია. ეს ამოცანა შეიძლება გამარტივდეს. ისწავლეთ როგორ იპოვოთ სწორი სიტყვა Excel-ში. ეს გაგიადვილებთ დოკუმენტში ნავიგაციას. და თქვენ შეგიძლიათ სწრაფად გადახვიდეთ იმ ინფორმაციაზე, რომელსაც ეძებთ.
ყველა უჯრედის მისამართების საჩვენებლად, რომლებსაც აქვთ ის, რასაც ეძებთ, გააკეთეთ შემდეგი:
- თუ გაქვთ Office 2010, გადადით მენიუში - რედაქტირება - ძებნა.
- გაიხსნება ფანჯარა შეყვანის ველით. ჩაწერეთ მასში საძიებო ფრაზა.
- Excel 2007-ში ეს ღილაკი არის მთავარი მენიუში რედაქტირების პანელში. ის არის მარჯვნივ.
- ყველა ვერსიაში მსგავსი შედეგის მიღწევა შესაძლებელია Ctrl + F კლავიშების დაჭერით.
- ველში ჩაწერეთ სიტყვა, ფრაზა ან რიცხვები, რომელთა პოვნაც გსურთ.
- დააწკაპუნეთ "ყველას პოვნა" მთელი დოკუმენტის მოსაძებნად. თუ დააწკაპუნებთ „შემდეგ“, პროგრამა სათითაოდ შეარჩევს უჯრედებს, რომლებიც მდებარეობს Excel-ის უჯრედის კურსორის ქვემოთ.
- დაელოდეთ პროცესის დასრულებას. რაც უფრო დიდია დოკუმენტი, მით უფრო დიდხანს იძიებს სისტემა.
- გამოჩნდება სია შედეგებით: უჯრედების სახელები და მისამართები, რომლებიც ემთხვევა მოცემულ ფრაზას და მათში ჩაწერილი ტექსტი.
- როდესაც თითოეულ ხაზს დააწკაპუნებთ, შესაბამისი უჯრედი მონიშნული იქნება.
- მოხერხებულობისთვის შეგიძლიათ ფანჯრის „გაჭიმვა“. ასე რომ, ის აჩვენებს მეტ ხაზს.
- მონაცემების დასალაგებლად დააწკაპუნეთ სვეტების სახელებზე ძიების შედეგების ზემოთ. თუ დააწკაპუნებთ „ფურცელზე“, ხაზები ანბანურად დალაგდება ფურცლის სახელიდან გამომდინარე, თუ აირჩევთ „მნიშვნელობებს“ - ისინი განთავსდება მნიშვნელობის მიხედვით.
- ეს სვეტებიც „გაჭიმულია“.
თქვენ შეგიძლიათ დააყენოთ საკუთარი პირობები. მაგალითად, დაიწყეთ მრავალი სიმბოლოს ძებნა. აი, როგორ იპოვოთ სიტყვა Excel-ში, რომელიც მთლიანად არ გახსოვთ:
- შეიყვანეთ ეტიკეტის მხოლოდ ნაწილი. თქვენ შეიძლება გქონდეთ მინიმუმ ერთი ასო - შეირჩევა ყველა ადგილი, სადაც ის იმყოფება.
- გამოიყენეთ სიმბოლოები * (ვარსკვლავი) და? (კითხვის ნიშანი). ისინი ცვლიან დაკარგული სიმბოლოებს.
- კითხვა მიუთითებს ერთ გამოტოვებულ ნივთზე. თუ დაწერთ, მაგალითად, "P???", გამოჩნდება უჯრედები, რომლებსაც აქვთ "P"-ით დაწყებული ოთხნიშნა სიტყვა: "გუთანი", "ველი", "წყვილი" და ა.შ.
- ვარსკვლავი (*) ცვლის ნებისმიერი რაოდენობის სიმბოლოს. ყველა მნიშვნელობის საპოვნელად, რომელიც შეიცავს ფესვს "rast", დაიწყეთ კლავიშის "*rast*" ძებნა.
თქვენ ასევე შეგიძლიათ გადახვიდეთ პარამეტრებზე:
- ძებნა ფანჯარაში დააწკაპუნეთ ოფციები.
- განყოფილებებში „დათვალიერება“ და „ძიების არეალი“ მიუთითეთ სად და რა კრიტერიუმებით გსურთ შესატყვისების ძიება. თქვენ შეგიძლიათ აირჩიოთ ფორმულები, შენიშვნები ან მნიშვნელობები.
- იმისათვის, რომ სისტემამ განასხვავოს მცირე და დიდი ასოები, მონიშნეთ ველი "გამგეობის მგრძნობიარე".
- თუ მონიშნავთ ველს „მთელი უჯრედი“, შედეგები აჩვენებს უჯრედებს, რომლებიც შეიცავს მხოლოდ მითითებულ საძიებო ფრაზას და სხვა არაფერს.
უჯრედის ფორმატის პარამეტრები
იმისათვის, რომ იპოვოთ მნიშვნელობები კონკრეტული შევსებით ან სტილით, გამოიყენეთ პარამეტრები. აი, როგორ მოვძებნოთ სიტყვა Excel-ში, თუ ის განსხვავდება დანარჩენი ტექსტისგან:
- საძიებო ველში დააჭირეთ ღილაკს "ოფციები" და დააჭირეთ ღილაკს "ფორმატი". გაიხსნება მენიუ რამდენიმე ჩანართით.
- თქვენ შეგიძლიათ მიუთითოთ კონკრეტული შრიფტი, ჩარჩოს ტიპი, ფონის ფერი, მონაცემთა ფორმატი. სისტემა მოძებნის ადგილებს, რომლებიც შეესაბამება მოცემულ კრიტერიუმებს.
- მიმდინარე უჯრედიდან (ამ ეტაპზე არჩეული) ინფორმაციის მისაღებად დააწკაპუნეთ „ამ უჯრედის ფორმატის გამოყენება“. შემდეგ პროგრამა იპოვის ყველა მნიშვნელობას, რომელსაც აქვს იგივე ზომის და ტიპის სიმბოლოები, იგივე ფერი, იგივე საზღვრები და მსგავსი.
მრავალი სიტყვის ძებნა
Excel-ში შეგიძლიათ იპოვოთ უჯრედები მთელი ფრაზებით. მაგრამ თუ თქვენ შეიყვანეთ "ლურჯი ბურთი" გასაღები, სისტემა იმუშავებს ზუსტად ამ მოთხოვნაზე. "Blue Crystal Ball" ან "Blue Glitter Ball" მნიშვნელობები არ გამოჩნდება შედეგებში.
Excel-ში ერთზე მეტი სიტყვის მოსაძებნად, მაგრამ რამდენიმე ერთდროულად, გააკეთეთ შემდეგი:
- ჩაწერეთ ისინი საძიებო ზოლში.
- მოათავსეთ ვარსკვლავები მათ შორის. გამოვა "*ტექსტი* *ტექსტი2* *ტექსტი3*". ეს იპოვის ყველა მნიშვნელობას, რომელიც შეიცავს მითითებულ წარწერებს. მიუხედავად იმისა, არიან თუ არა მათ შორის პერსონაჟები.
- ამ გზით თქვენ შეგიძლიათ დააყენოთ გასაღები ცალკეული ასოებითაც კი.
ფილტრი
აი, როგორ მოძებნოთ Excel-ში ფილტრის გამოყენებით:
- აირჩიეთ ნებისმიერი შევსებული უჯრედი.
- დააწკაპუნეთ მთავარი - სორტირება - ფილტრი.
- ისრები გამოჩნდება უჯრედების ზედა მწკრივში. ეს არის ჩამოსაშლელი მენიუ. გახსენი.
- შეიყვანეთ თქვენი მოთხოვნა ტექსტის ველში და დააჭირეთ OK.
- სვეტი აჩვენებს მხოლოდ უჯრედებს, რომლებიც შეიცავს საძიებო ფრაზას.
- შედეგების აღსადგენად, ჩამოსაშლელ სიაში შეამოწმეთ „აირჩიეთ ყველა“.
- ფილტრის გამორთვისთვის კვლავ დააწკაპუნეთ მასზე დახარისხებისას.
ეს მეთოდი არ იმუშავებს, თუ არ იცით რომელ რიგში არის თქვენთვის საჭირო მნიშვნელობა.
Excel-ში ფრაზის ან ნომრის საპოვნელად გამოიყენეთ ჩაშენებული ინტერფეისის ფუნქციები. შეგიძლიათ აირჩიოთ დამატებითი ძიების პარამეტრები და ჩართოთ ფილტრი.
ეს გაკვეთილი საუბრობს ფუნქციების მთავარ უპირატესობებზე ინდექსიდა მატჩი Excel-ში, რაც მათ უფრო მიმზიდველს ხდის მათთან შედარებით VLOOKUP. თქვენ ნახავთ ფორმულების რამდენიმე მაგალითს, რომლებიც დაგეხმარებათ მარტივად გაუმკლავდეთ ბევრ რთულ ამოცანებს, რომელთა წინაშეც დგას ფუნქცია. VLOOKUPუძლური.
რამდენიმე ბოლო სტატიაში ჩვენ ყველანაირად ვცდილობთ ავუხსნათ ფუნქციის საფუძვლები ახალბედა მომხმარებლებს. VLOOKUPდა აჩვენეთ უფრო რთული ფორმულების მაგალითები მოწინავე მომხმარებლებისთვის. ახლა ჩვენ ვეცდებით, თუ არა, მოგერიდოთ გამოყენებას VLOOKUP, მაშინ მაინც აჩვენე ალტერნატიული გზები Excel-ში ვერტიკალური ძიების განსახორციელებლად.
რატომ გვჭირდება ეს? - გეკითხებით. დიახ, იმიტომ VLOOKUPარ არის ერთადერთი საძიებო ფუნქცია Excel-ში და მისმა მრავალრიცხოვანმა შეზღუდვებმა შეიძლება ხელი შეგიშალოთ სასურველი შედეგის მიღებაში ბევრ სიტუაციაში. მეორეს მხრივ, ფუნქციები ინდექსიდა მატჩი- უფრო მოქნილი და აქვს მთელი რიგი ფუნქციები, რაც მათ უფრო მიმზიდველს ხდის ვიდრე VLOOKUP.
ძირითადი ინფორმაცია INDEX-ისა და MATCH-ის შესახებ
ვინაიდან ამ გაკვეთილის მიზანია აჩვენოს ფუნქციების შესაძლებლობები ინდექსიდა მატჩი Excel-ში ვერტიკალური ძიების განსახორციელებლად, ჩვენ არ შევჩერდებით მათ სინტაქსსა და აპლიკაციაზე.
ჩვენ აქ ვაძლევთ აუცილებელ მინიმუმს არსის გასაგებად, შემდეგ კი დეტალურად გავაანალიზებთ ფორმულების მაგალითებს, რომლებიც აჩვენებს გამოყენების უპირატესობებს ინდექსიდა მატჩიმაგივრად VLOOKUP.
INDEX - სინტაქსი და ფუნქციის გამოყენება
ფუნქცია ინდექსი(INDEX) Excel-ში აბრუნებს მნიშვნელობას მასივიდან მოცემულ მწკრივისა და სვეტის ნომრებზე. ფუნქციას აქვს შემდეგი სინტაქსი:
თითოეულ არგუმენტს აქვს ძალიან მარტივი ახსნა:
- მასივი(მასივი) არის უჯრედების დიაპაზონი, საიდანაც ხდება მნიშვნელობის ამოღება.
- row_num(rownumber) არის მწკრივის ნომერი მასივში, საიდანაც ხდება მნიშვნელობის ამოღება. თუ არ არის მითითებული, საჭიროა არგუმენტი სვეტის_რაოდენობა(სვეტის_ნომერი).
- სვეტის_რაოდენობა(სვეტის_ნომერი) არის მასივის სვეტის ნომერი, საიდანაც ხდება მნიშვნელობის ამოღება. თუ არ არის მითითებული, საჭიროა არგუმენტი row_num(ხაზის ნომერი)
თუ ორივე არგუმენტი მოცემულია, მაშინ ფუნქცია ინდექსიაბრუნებს მნიშვნელობას უჯრედიდან მითითებული მწკრივისა და სვეტის კვეთაზე.
აქ არის მარტივი ფუნქციის მაგალითი ინდექსი(INDEX):
INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)
ფორმულა ეძებს დიაპაზონში A1: C10და აბრუნებს უჯრედის მნიშვნელობას მე-2ხაზი და 3მსვეტი, ანუ უჯრედიდან C2.
ძალიან მარტივია, არა? თუმცა, პრაქტიკაში, თქვენ ყოველთვის არ იცით რომელი მწკრივი და სვეტი გჭირდებათ და, შესაბამისად, გჭირდებათ ფუნქციის დახმარება მატჩი.
MATCH - სინტაქსი და ფუნქციის გამოყენება
ფუნქცია მატჩი(MATCH) Excel-ში ეძებს მითითებულ მნიშვნელობას უჯრედების დიაპაზონში და აბრუნებს ამ მნიშვნელობის ფარდობით პოზიციას დიაპაზონში.
მაგალითად, თუ დიაპაზონში B1: B3შეიცავს ნიუ-იორკის, პარიზის, ლონდონის მნიშვნელობებს, შემდეგ შემდეგი ფორმულა დააბრუნებს ციფრს 3 , რადგან „ლონდონი“ სიის მესამე პუნქტია.
MATCH ("ლონდონი", B1:B3,0)
=MATCH("ლონდონი",B1:B3,0)
ფუნქცია მატჩი(MATCH) აქვს შემდეგი სინტაქსი:
MATCH(ძიების_მნიშვნელობა,ძიების_მასივი,)
MATCH(ძიების_მნიშვნელობა, საძიებო_მასივი, [შემთხვევის_ტიპი])
- საძიებელი_მნიშვნელობა(lookup_value) არის ნომერი ან ტექსტი, რომელსაც ეძებთ. არგუმენტი შეიძლება იყოს მნიშვნელობა, მათ შორის ლოგიკური ან უჯრედის მითითება.
- ძიება_მასივი(lookup_array) არის უჯრედების დიაპაზონი, რომელშიც უნდა მოძებნოთ.
- მატჩის_ტიპი(შემთხვევა_ტიპი) - ეს არგუმენტი ეუბნება ფუნქციას მატჩიგსურთ იპოვოთ ზუსტი შესატყვისი თუ სავარაუდო შესატყვისი:
- 1 ან მითითებული არ არის– პოულობს მაქსიმალურ მნიშვნელობას სასურველზე ნაკლები ან ტოლი. შესანახი მასივი უნდა იყოს დალაგებული აღმავალი თანმიმდევრობით, ანუ უმცირესიდან დიდამდე.
- 0 - პოულობს პირველ მნიშვნელობას სასურველის ტოლი. კომბინაციისთვის ინდექსი/მატჩითქვენ ყოველთვის გსურთ ზუსტი დამთხვევა, ამიტომ მესამე ფუნქციის არგუმენტი მატჩითანაბარი უნდა იყოს 0 .
- -1 - პოულობს უმცირეს მნიშვნელობას სასურველ მნიშვნელობაზე მეტი ან ტოლი. ნახული მასივი უნდა იყოს დალაგებული კლებადობით, ანუ უდიდესიდან უმცირესამდე.
ერთი შეხედვით, ფუნქციის სარგებელი მატჩიეჭვებს ბადებს. ვინ უნდა იცოდეს ელემენტის პოზიცია დიაპაზონში? ჩვენ გვინდა ვიცოდეთ ამ ელემენტის ღირებულება!
შეგახსენებთ, რომ თქვენთვის სასურველი მნიშვნელობის ფარდობითი პოზიცია (ანუ მწკრივის და/ან სვეტის ნომერი) არის ზუსტად ის, რაც უნდა მივუთითოთ არგუმენტებისთვის. row_num(ხაზის_ნომერი) და/ან სვეტის_რაოდენობა(სვეტის_ნომერი) ფუნქციები ინდექსი(INDEX). როგორც გახსოვთ, ფუნქცია ინდექსიშეუძლია დააბრუნოს მნიშვნელობა მოცემული მწკრივისა და სვეტის კვეთაზე, მაგრამ ვერ განსაზღვრავს რომელი მწკრივი და სვეტი გვაინტერესებს.
როგორ გამოვიყენოთ INDEX და MATCH Excel-ში
ახლა, როდესაც თქვენ იცით ძირითადი ინფორმაცია ამ ორი ფუნქციის შესახებ, მე მჯერა, რომ უკვე ნათელი ხდება, როგორ ფუნქციონირებს მატჩიდა ინდექსიშეუძლია ერთად იმუშაოს. მატჩიგანსაზღვრავს მოძიებული მნიშვნელობის ფარდობით პოზიციას უჯრედების მოცემულ დიაპაზონში და ინდექსიიყენებს ამ რიცხვს (ან ციფრებს) და აბრუნებს შედეგს შესაბამისი უჯრედიდან.
ჯერ კიდევ არ არის სრულიად ნათელი? მახასიათებლების წარმოდგენა ინდექსიდა მატჩიამ ფორმით:
INDEX(,(MATCH ( სასურველი ღირებულება,სვეტი, რომელსაც ჩვენ ვეძებთ,0))
=INDEX( სვეტი, საიდანაც უნდა ამოიღონ;(მატჩი( სასურველი ღირებულება;სვეტი, რომელსაც ჩვენ ვეძებთ;0))
ვფიქრობ, კიდევ უფრო ადვილი გასაგები იქნება მაგალითით. დავუშვათ, რომ თქვენ გაქვთ სახელმწიფო დედაქალაქების შემდეგი სია:
მოდით ვიპოვოთ ერთ-ერთი დედაქალაქის მოსახლეობა, ვთქვათ იაპონია, შემდეგი ფორმულის გამოყენებით:
INDEX($D$2:$D$10,MATCH(„იაპონია“,$B$2:$B$10,0))
=INDEX($D$2:$D$10;MATCH("იაპონია";$B$2:$B$10;0))
ახლა მოდით დავშალოთ რას აკეთებს ამ ფორმულის თითოეული ელემენტი:
- ფუნქცია მატჩი(MATCH) ეძებს მნიშვნელობას "Japan" სვეტში ბდა კონკრეტულად უჯრედებში B2: B10და აბრუნებს ნომერს 3 , ვინაიდან "იაპონია" სიაში მესამე ადგილზეა.
- ფუნქცია ინდექსი(INDEX) იყენებს 3
არგუმენტისთვის row_num(ხაზის_ნომერი), რომელიც განსაზღვრავს რომელი მწკრივიდან უნდა დაბრუნდეს მნიშვნელობა. იმათ. მიიღება მარტივი ფორმულა:
INDEX ($D$2:$D$10.3)
=INDEX($D$2:$D$10,3)ფორმულა ამბობს დაახლოებით ასე: შეხედე უჯრედებში D2ადრე D10და ამოიღეთ მნიშვნელობა მესამე რიგიდან, ანუ უჯრედიდან D4, ვინაიდან დათვლა მეორე ხაზიდან იწყება.
აქ არის შედეგი Excel-ში:
Მნიშვნელოვანი! რიგებისა და სვეტების რაოდენობა მასივში, რომელსაც იყენებს ფუნქცია ინდექსი(INDEX), უნდა ემთხვეოდეს არგუმენტის მნიშვნელობებს row_num(ხაზის_ნომერი) და სვეტის_რაოდენობა(სვეტის_ნომერი) ფუნქციები მატჩი(მატჩი). წინააღმდეგ შემთხვევაში, ფორმულის შედეგი მცდარი იქნება.
გაჩერდი, გაჩერდი... რატომ არ შეგვიძლია უბრალოდ გამოვიყენოთ ფუნქცია VLOOKUP(VLOOKUP)? აქვს თუ არა აზრი ლაბირინთების გარკვევის მცდელობაში დროის დაკარგვას მატჩიდა ინდექსი?
VLOOKUP ("იაპონია", $B$2:$D$2,3)
=VLOOKUP("იაპონია",$B$2:$D$2,3)
ამ შემთხვევაში აზრი არ აქვს! ამ მაგალითის მიზანი არის წმინდა საილუსტრაციო, ასე რომ თქვენ შეგიძლიათ გაიგოთ როგორ ფუნქციონირებს მატჩიდა ინდექსიმუშაობა წყვილებში. შემდეგი მაგალითები გაჩვენებთ პაკეტის ნამდვილ ძალას. ინდექსიდა მატჩი, რომელიც ადვილად უმკლავდება ბევრ რთულ სიტუაციას, როცა VLOOKUPიჭედება.
რატომ არის INDEX/MATCH უკეთესი ვიდრე VLOOKUP?
როდესაც გადაწყვეტთ, რომელი ფორმულა გამოვიყენოთ ვერტიკალური ძიებისთვის, Excel-ის გურუების უმეტესობას სჯერა, რომ ინდექსი/მატჩიბევრად უკეთესი ვიდრე VLOOKUP. თუმცა, Excel-ის ბევრი მომხმარებელი მაინც მიმართავს გამოყენებას VLOOKUP, იმიტომ ეს ფუნქცია ბევრად უფრო ადვილია. ეს იმის გამო ხდება, რომ ძალიან ცოტა ადამიანს ესმის სრულად გადართვის სარგებელი VLOOKUPთაიგულზე ინდექსიდა მატჩიდა არავის სურს დროის დახარჯვა უფრო რთული ფორმულის შესწავლაზე.
MATCH/INDEX-ის გამოყენების ტოპ 4 უპირატესობა Excel-ში:
1. ძიება მარჯვნიდან მარცხნივ.როგორც Excel-ის ნებისმიერმა განათლებულმა მომხმარებელმა იცის, VLOOKUPვერ იყურება მარცხნივ, რაც ნიშნავს, რომ სასურველი მნიშვნელობა აუცილებლად უნდა იყოს შესასწავლი დიაპაზონის მარცხენა სვეტში. იმ შემთხვევაში, თუ მატჩი/ინდექსი, საძიებო სვეტი შეიძლება იყოს საძიებო დიაპაზონის მარცხნივ ან მარჯვნივ. მაგალითი: აჩვენებს ამ ფუნქციას მოქმედებაში.
2. სვეტების უსაფრთხო დამატება ან მოხსნა.ფორმულები ფუნქციით VLOOKUPშეაჩერეთ მუშაობა ან დააბრუნეთ არასწორი მნიშვნელობები, თუ ამოიღებთ ან დაამატებთ სვეტს საძიებო ცხრილში. ფუნქციისთვის VLOOKUPნებისმიერი ჩასმული ან ამოღებული სვეტი შეცვლის ფორმულის შედეგს სინტაქსის გამო VLOOKUPმოითხოვს, რომ მიუთითოთ მთელი დიაპაზონი და კონკრეტული სვეტის ნომერი, საიდანაც გსურთ მონაცემების ამოღება.
მაგალითად, თუ თქვენ გაქვთ მაგიდა A1: C10და გსურთ ამოიღოთ მონაცემები სვეტიდან ბ, მაშინ თქვენ უნდა დააყენოთ მნიშვნელობა 2 არგუმენტისთვის col_index_num(სვეტის_ნომერი) ფუნქციები VLOOKUP, ამგვარად:
VLOOKUP("ძიების მნიშვნელობა",A1:C10,2)
=VLOOKUP("ძიების მნიშვნელობა";A1:C10;2)
თუ მოგვიანებით ჩასვით ახალი სვეტი სვეტებს შორის ადა ბ, მაშინ არგუმენტის მნიშვნელობა უნდა შეიცვალოს 2 on 3 , წინააღმდეგ შემთხვევაში ფორმულა დააბრუნებს შედეგს ახლად ჩასმული სვეტიდან.
გამოყენება მატჩი/ინდექსი, შეგიძლიათ წაშალოთ ან დაამატოთ სვეტები შესასწავლ დიაპაზონში შედეგის დამახინჯების გარეშე, რადგან სასურველი მნიშვნელობის შემცველი სვეტი პირდაპირ არის განსაზღვრული. მართლაც, ეს არის დიდი უპირატესობა, განსაკუთრებით მაშინ, როდესაც თქვენ გიწევთ მუშაობა დიდი რაოდენობით მონაცემებთან. თქვენ შეგიძლიათ დაამატოთ და წაშალოთ სვეტები ყოველგვარი ფუნქციის გამოსწორების გარეშე, რომელსაც იყენებთ VLOOKUP.
3. არ არსებობს შეზღუდვა მოძიებული მნიშვნელობის ზომაზე.გამოყენება VLOOKUP, გაითვალისწინეთ 255-სიმბოლოიანი ლიმიტი მოძიებული მნიშვნელობის სიგრძეზე, წინააღმდეგ შემთხვევაში თქვენ რისკავთ შეცდომის მიღებას #ღირებულება!(#ღირებულება!). ასე რომ, თუ ცხრილი შეიცავს გრძელ რიგებს, ერთადერთი ეფექტური გამოსავალი არის გამოყენება ინდექსი/მატჩი.
ვთქვათ, იყენებთ შემდეგ ფორმულას: VLOOKUP, რომელიც ეძებს უჯრედებში საიდან B5ადრე D10უჯრედში მითითებული მნიშვნელობა A2:
VLOOKUP(A2,B5:D10,3,FALSE)
=VLOOKUP(A2,B5:D10,3,FALSE)
ფორმულა არ იმუშავებს, თუ მნიშვნელობა უჯრედშია A2 255 სიმბოლოზე მეტი. ამის ნაცვლად, თქვენ უნდა გამოიყენოთ მსგავსი ფორმულა ინდექსი/მატჩი:
INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
4. უფრო მაღალი სირბილის სიჩქარე.თუ თქვენ მუშაობთ მცირე ცხრილებთან, მაშინ Excel-ის შესრულებაში განსხვავება სავარაუდოდ არ იქნება შესამჩნევი, განსაკუთრებით ბოლო ვერსიებში. თუ თქვენ მუშაობთ დიდ ცხრილებთან, რომლებიც შეიცავს ათასობით მწკრივს და ასობით საძიებო ფორმულას, Excel იმუშავებს ბევრად უფრო სწრაფად, როდესაც იყენებთ მატჩიდა ინდექსიმაგივრად VLOOKUP. ზოგადად, ასეთი ჩანაცვლება ზრდის Excel-ის სიჩქარეს 13% .
გავლენა VLOOKUP Excel-ის შესრულება განსაკუთრებით შესამჩნევია, თუ სამუშაო წიგნი შეიცავს ასობით რთულ მასივის ფორმულას, როგორიცაა VLOOKUP+SUM. ფაქტია, რომ მასივში თითოეული მნიშვნელობის შემოწმება მოითხოვს ცალკე ფუნქციის გამოძახებას VLOOKUP. ამიტომ, რაც უფრო მეტ მნიშვნელობას შეიცავს მასივი და რაც უფრო მეტ მასივის ფორმულას შეიცავს თქვენი ცხრილი, მით უფრო ნელია Excel.
მეორეს მხრივ, ფორმულა ფუნქციებით მატჩიდა ინდექსიუბრალოდ ახორციელებს ძიებას და აბრუნებს შედეგს, იგივე სამუშაოს შესამჩნევად სწრაფად აკეთებს.
INDEX და MATCH - ფორმულების მაგალითები
ახლა, როდესაც გესმით მიზეზები, თუ რატომ უნდა ისწავლოთ ფუნქციები მატჩიდა ინდექსი, გადავიდეთ ყველაზე საინტერესოზე და ვნახოთ, როგორ შეგიძლიათ თეორიული ცოდნის პრაქტიკაში გამოყენება.
როგორ მოძებნოთ მარცხენა მხარეს MATCH-ისა და INDEX-ის გამოყენებით
ნებისმიერი სახელმძღვანელო VLOOKUPამბობს, რომ ეს ფუნქცია მარცხნივ ვერ გამოიყურება. იმათ. თუ საძიებო სვეტი არ არის ყველაზე მარცხენა სვეტი საძიებო დიაპაზონში, მაშინ არ არის იმის შანსი, რომ მიიღოთ VLOOKUPსასურველი შედეგი.
ფუნქციები მატჩიდა ინდექსი Excel ბევრად უფრო მოქნილია და არ აინტერესებს სად მდებარეობს ამოსატანი მნიშვნელობის მქონე სვეტი. მაგალითად, დავუბრუნდეთ ცხრილს სახელმწიფო დედაქალაქებთან და მოსახლეობასთან ერთად. ამჯერად ჩვენ ვწერთ ფორმულას მატჩი/ინდექსი, რომელიც აჩვენებს, თუ რა ადგილი უჭირავს რუსეთის დედაქალაქს (მოსკოვს) მოსახლეობის მხრივ.
როგორც ქვემოთ მოცემულ ფიგურაში ხედავთ, ფორმულა შესანიშნავად ასრულებს ამ ამოცანას:
INDEX($A$2:$A$10,MATCH(„რუსეთი“,$B$2:$B$10,0))
ახლა თქვენ არ უნდა გქონდეთ პრობლემა იმის გაგებაში, თუ როგორ მუშაობს ეს ფორმულა:
- პირველი, მოდით გამოვიყენოთ ფუნქცია მატჩი(მატჩი), რომელიც სიაში პოულობს პოზიციას "რუსეთი":
MATCH ("რუსეთი",$B$2:$B$10.0))
=MATCH("რუსეთი",$B$2:$B$10,0)) - შემდეგი, დააყენეთ ფუნქციის დიაპაზონი ინდექსი(INDEX) მნიშვნელობის მისაღებად. ჩვენს შემთხვევაში ეს A2: A10.
- შემდეგ ჩვენ ვაკავშირებთ ორივე ნაწილს და ვიღებთ ფორმულას:
INDEX($A$2:$A$10;MATCH(„რუსეთი“;$B$2:$B$10;0))
=INDEX($A$2:$A$10,MATCH("რუსეთი",$B$2:$B$10,0))
მინიშნება:სწორი გამოსავალი არის ყოველთვის გამოიყენოთ აბსოლუტური მითითებები ინდექსიდა მატჩირათა საძიებო დიაპაზონი არ დაიკარგოს ფორმულის სხვა უჯრედებში კოპირებისას.
გამოთვლები INDEX-ისა და MATCH-ის გამოყენებით Excel-ში (AVERAGE, MAX, MIN)
თქვენ შეგიძლიათ ჩასვათ Excel-ის სხვა ფუნქციები ინდექსიდა მატჩიმაგალითად, იპოვონ მინიმალური, მაქსიმალური ან ყველაზე ახლოს საშუალო მნიშვნელობასთან. აქ მოცემულია ფორმულების რამდენიმე ვარიანტი, რომლებიც გამოიყენება ცხრილში:
1. მაქს(მაქსი). ფორმულა პოულობს მაქსიმუმს სვეტში დ Cიგივე ხაზი:
INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
შედეგი: პეკინი
2. მინ(MIN). ფორმულა პოულობს მინიმუმს სვეტში დდა აბრუნებს მნიშვნელობას სვეტიდან Cიგივე ხაზი:
INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
შედეგი: ლიმა
3. საშუალო(საშუალო). ფორმულა ითვლის საშუალო დიაპაზონში D2:D10, შემდეგ პოულობს მასთან ყველაზე ახლოს და აბრუნებს მნიშვნელობას სვეტიდან Cიგივე ხაზი:
INDEX($C$2:$C$10,MATCH(საშუალო($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,MATCH(საშუალო($D$2:D$10),$D$2:D$10,1))
შედეგი: მოსკოვი
დასამახსოვრებელი ქულები AVERAGE ფუნქციის გამოყენებისას INDEX-ით და MATCH-ით
ფუნქციის გამოყენებით საშუალოკომბინაციაში ინდექსიდა მატჩი, როგორც მესამე ფუნქციის არგუმენტი მატჩიყველაზე ხშირად დაგჭირდებათ დაკონკრეტება 1 ან -1 თუ არ ხართ დარწმუნებული, რომ ნახული დიაპაზონი შეიცავს საშუალოს ტოლ მნიშვნელობას. თუ დარწმუნებული ხართ, რომ არსებობს ასეთი მნიშვნელობა, ჩადეთ 0 ზუსტი შესატყვისის მოსაძებნად.
- თუ დააკონკრეტეთ 1 , საძიებო სვეტის მნიშვნელობები უნდა იყოს დალაგებული ზრდადი თანმიმდევრობით და ფორმულა დააბრუნებს მაქსიმალურ მნიშვნელობას საშუალოზე ნაკლები ან ტოლი.
- თუ დააკონკრეტეთ -1 , საძიებო სვეტის მნიშვნელობები უნდა იყოს დალაგებული კლებადობით და დაბრუნდება საშუალოზე მეტი ან ტოლი მინიმალური მნიშვნელობა.
ჩვენს მაგალითში, მნიშვნელობები სვეტში დდალაგებულია ზრდის მიხედვით, ამიტომ ვიყენებთ მატჩის ტიპს 1 . ფორმულა ინდექსი/SEARCHPOზაბრუნებს "მოსკოვს", რადგან მოსკოვის მოსახლეობა საშუალოზე ყველაზე დაბალია (12,269,006).
როგორ გამოვიყენოთ INDEX და MATCH ცნობილ მწკრივზე და სვეტზე მოსაძებნად
ეს ფორმულა უდრის 2D ძიებას VLOOKUPდა გაძლევთ საშუალებას იპოვოთ მნიშვნელობა გარკვეული მწკრივისა და სვეტის კვეთაზე.
ამ მაგალითში, ფორმულა ინდექსი/მატჩიძალიან წააგავს ფორმულებს, რომლებიც უკვე განვიხილეთ ამ გაკვეთილზე, მხოლოდ ერთი განსხვავებით. Იცი რა?
როგორც გახსოვთ, ფუნქციის სინტაქსი ინდექსი(INDEX) იძლევა სამ არგუმენტს:
INDEX (მასივი, რიგის_რაოდენობა,)
INDEX(მასივი, მწკრივის_ნომერი, [სვეტის_ნომერი])
და გილოცავთ მათ, ვინც ეს გაარკვია!
დავიწყოთ ფორმულის შაბლონის დაწერით. ამისათვის ჩვენ ვიღებთ უკვე ნაცნობ ფორმულას ინდექსი/მატჩიდა დაამატეთ მას სხვა ფუნქცია მატჩი, რომელიც დააბრუნებს სვეტის ნომერს.
INDEX(თქვენი მაგიდა ,(MATCH(, სვეტი საძიებლად,0)),(MATCH(, სტრიქონი საძიებლად,0))
=INDEX(თქვენი მაგიდა,(MATCH( მნიშვნელობა ვერტიკალური ძიებისთვის,სვეტი საძიებლად,0)),(მატჩი( მნიშვნელობა ჰორიზონტალური ძიებისთვის,სტრიქონი საძიებლად,0))
გთხოვთ გაითვალისწინოთ, რომ ორგანზომილებიანი ძიებისთვის, თქვენ უნდა მიუთითოთ მთელი ცხრილი არგუმენტში მასივი(მასივი) ფუნქციები ინდექსი(INDEX).
ახლა მოდით ეს ნიმუში გამოვცადოთ. ქვემოთ ხედავთ მსოფლიოს ყველაზე დასახლებული ქვეყნების სიას. დავუშვათ, ჩვენი ამოცანაა გავარკვიოთ შეერთებული შტატების მოსახლეობა 2015 წელს.
კარგი, მოდით დავწეროთ ფორმულა. როდესაც მჭირდება რთული ფორმულის შექმნა Excel-ში ჩადგმული ფუნქციებით, ჯერ თითოეულ ჩადგმულს ცალკე ვწერ.
ასე რომ, დავიწყოთ ორი ფუნქციით მატჩი, რომელიც დააბრუნებს ფუნქციის მწკრივისა და სვეტის ნომრებს ინდექსი:
- MATCH სვეტისთვის- სვეტში ვიყურებით ბუფრო ზუსტად, დიაპაზონში B2:B11, მნიშვნელობა, რომელიც მითითებულია უჯრედში H2(ᲐᲨᲨ). ფუნქცია ასე გამოიყურება:
მატჩი ($H$2,$B$1:$B$11.0)
=MATCH($H$2,$B$1:$B$11,0)4 , ვინაიდან "USA" არის სიის მე-4 ელემენტი სვეტში ბ(სათაურის ჩათვლით).
- მატჩი სიმისთვის- ჩვენ ვეძებთ უჯრედის მნიშვნელობას H3(2015) რიგში 1
, ანუ უჯრედებში A1: E1:
მატჩი ($H$3,$A$1:$E$1,0)
=MATCH($H$3,$A$1:$E$1,0)ამ ფორმულის შედეგი იქნება 5 რადგან "2015" მე-5 სვეტშია.
ახლა ჩვენ ჩავსვით ეს ფორმულები ფუნქციაში ინდექსიდა ვოილა:
INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11.0),MATCH($H$3,$A$1:$E$1.0))
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
თუ შევცვლით ფუნქციებს მატჩიმათ მიერ დაბრუნებულ მნიშვნელობებზე, ფორმულა გახდება მარტივი და გასაგები:
INDEX($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))
ეს ფორმულა აბრუნებს მნიშვნელობას კვეთაზე მე-4ხაზები და მე-5სვეტები დიაპაზონში A1: E11, ანუ უჯრედის მნიშვნელობა E4. Უბრალოდ? დიახ!
მოძებნეთ მრავალი კრიტერიუმი INDEX-ით და MATCH-ით
სახელმძღვანელოში VLOOKUPჩვენ ვაჩვენეთ ფორმულის მაგალითი ფუნქციით VLOOKUPმრავალი კრიტერიუმით მოძიება. თუმცა, ამ გადაწყვეტის მნიშვნელოვანი შეზღუდვა იყო დამხმარე სვეტის დამატების საჭიროება. კარგი ამბავი: ფორმულა ინდექსი/მატჩიშეუძლია მოძებნოს მნიშვნელობები ორ სვეტში, დამხმარე სვეტის შექმნის გარეშე!
დავუშვათ, გვაქვს შეკვეთების სია და გვინდა ვიპოვოთ ჯამი ორი კრიტერიუმით − მყიდველის სახელი(დამკვეთი) და პროდუქტი(პროდუქტი). საქმეს ართულებს ის ფაქტი, რომ ერთ მყიდველს შეუძლია ერთდროულად რამდენიმე სხვადასხვა პროდუქტის ყიდვა და მყიდველების სახელები ცხრილის ფურცელზე. საძიებო ცხრილიმოწყობილი შემთხვევითი თანმიმდევრობით.
აქ არის ფორმულა ინდექსი/მატჩიწყვეტს პრობლემას:
(=INDEX("საძიებო ცხრილი"!$A$2:$C$13,MATCH(1,(A2="ძიების ცხრილი"!$A$2:$A$13)*
(B2="ძებნის ცხრილი"!$B$2:$B$13),0),3))
(=INDEX("საძიებო ცხრილი"!$A$2:$C$13;MATCH(1;(A2="ძიების ცხრილი"!$A$2:$A$13)*
(B2="ძებნის ცხრილი"!$B$2:$B$13);0);3))
ეს ფორმულა უფრო რთულია, ვიდრე სხვები, რომლებიც ადრე განვიხილეთ, მაგრამ აღჭურვილია ფუნქციების ცოდნით ინდექსიდა მატჩითქვენ მას გადალახავთ. ურთულესი ნაწილი ფუნქციაა მატჩივფიქრობ, ჯერ ამის ახსნაა საჭირო.
MATCH(1,(A2="ძიების ცხრილი"!$A$2:$A$13),0)*(B2="ძიების ცხრილი"!$B$2:$B$13)
MATCH(1;(A2="ძიების ცხრილი"!$A$2:$A$13);0)*(B2="ძიების ცხრილი"!$B$2:$B$13)
ზემოთ ნაჩვენები ფორმულაში, მნიშვნელობა, რომელსაც ეძებთ არის 1 და საძიებელი მასივი გამრავლების შედეგია. კარგი, რა უნდა გავამრავლოთ და რატომ? ავიღოთ ყველაფერი თანმიმდევრობით:
- მიიღეთ პირველი მნიშვნელობა სვეტში ა(დამკვეთი) ფურცელზე მთავარი მაგიდადა შეადარეთ იგი მყიდველების ყველა სახელს ფურცელზე მოცემულ ცხრილში საძიებო ცხრილი(A2:A13).
- თუ მატჩი იპოვეს, განტოლება ბრუნდება 1 (მართალი), და თუ არა - 0 (იტყუება).
- შემდეგი, ჩვენ იგივე ვაკეთებთ სვეტის მნიშვნელობებს ბ(პროდუქტი).
- შემდეგ ვამრავლებთ მიღებულ შედეგებს (1 და 0). მხოლოდ იმ შემთხვევაში, თუ ორივე სვეტში მოიძებნება შესატყვისები (ანუ ორივე კრიტერიუმი მართალია) თქვენ მიიღებთ 1 . თუ ორივე კრიტერიუმი მცდარია, ან მხოლოდ ერთი მათგანი დაკმაყოფილებულია, თქვენ მიიღებთ 0 .
ახლა გესმით, რატომ ვიკითხეთ 1 როგორც სასურველი მნიშვნელობა? სწორია, რომ ფუნქცია მატჩიდააბრუნა პოზიცია მხოლოდ მაშინ, როდესაც ორივე კრიტერიუმი დაკმაყოფილდება.
Შენიშვნა:ამ შემთხვევაში, თქვენ უნდა გამოიყენოთ მესამე არჩევითი ფუნქციის არგუმენტი ინდექსი. აუცილებელია, რადგან პირველ არგუმენტში ჩვენ ვაზუსტებთ მთელ ცხრილს და უნდა ვუთხრათ ფუნქციას რომელი სვეტიდან ამოიღოს მნიშვნელობა. ჩვენს შემთხვევაში, ეს არის სვეტი C(ჯამ) და ასე გავაცანი 3 .
და ბოლოს იმიტომ ჩვენ უნდა შევამოწმოთ მასივის თითოეული უჯრედი, ეს ფორმულა უნდა იყოს მასივის ფორმულა. ამის დანახვა შეგიძლიათ ხვეული ბრეკეტებით, რომელშიც ის არის ჩასმული. ასე რომ, როდესაც დაასრულებთ ფორმულის შეყვანას, არ დაგავიწყდეთ დააჭიროთ Ctrl+Shift+Enter.
თუ ყველაფერი სწორად გაკეთდა, მიიღებთ შედეგს, როგორც ქვემოთ მოცემულ ფიგურაში:
INDEX და MATCH გაერთიანებულია IFERROR-თან Excel-ში
როგორც თქვენ უკვე შენიშნეთ (და არაერთხელ), თუ შეიტანეთ არასწორი მნიშვნელობა, მაგალითად, რომელიც არ არის ნახულ მასივში, ფორმულა ინდექსი/მატჩიშეცდომის შესახებ იტყობინება #არ/ა(# N/A) ან #ღირებულება!(#ღირებულება!). თუ გსურთ შეცვალოთ ასეთი შეტყობინება უფრო გასაგები რამით, შეგიძლიათ ჩასვათ ფორმულა ინდექსიდა მატჩიფუნქციაში ესლიოშიბკა.
ფუნქციის სინტაქსი ესლიოშიბკაძალიან მარტივი:
IFERROR (მნიშვნელობა, მნიშვნელობა_თუ_შეცდომა)
IFERROR (მნიშვნელობა, მნიშვნელობა_თუ_შეცდომა)
სად არის არგუმენტი ღირებულება(მნიშვნელობა) არის მნიშვნელობა, რომელიც შემოწმებულია შეცდომისთვის (ჩვენს შემთხვევაში, ფორმულის შედეგი ინდექსი/მატჩი); და არგუმენტი მნიშვნელობა_თუ_შეცდომა(value_if_error) არის მნიშვნელობა, რომელიც უნდა დაბრუნდეს, თუ ფორმულა შეცდომას უშვებს.
მაგალითად, შეგიძლიათ ჩასვათ ფუნქცია ესლიოშიბკაამგვარად:
IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11.0),MATCH($G$3,$A$1:$E$1.0)),
"შემთხვევა ვერ მოიძებნა. გთხოვთ სცადოთ ხელახლა!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 :$E$1;0));
"შემთხვევები ვერ მოიძებნა. გთხოვთ სცადოთ ხელახლა!")
ახლა კი, თუ ვინმე შეაქვს მცდარ მნიშვნელობას, ფორმულა მისცემს შემდეგ შედეგს:
თუ გირჩევნიათ უჯრედი ცარიელი დატოვოთ შეცდომის შემთხვევაში, შეგიძლიათ გამოიყენოთ ბრჭყალები (“”), როგორც მეორე ფუნქციის არგუმენტის მნიშვნელობა. ესლიოშიბკა. Ამგვარად:
IFERROR(INDEX(მასივი,MATCH(საძიებო_მნიშვნელობა, საძიებო_მასივი,0),"")
IFERROR(INDEX(მასივი,MATCH(ძიების_მნიშვნელობა,ძიების_მასივი,0),"")
ვიმედოვნებ, რომ ამ სახელმძღვანელოში აღწერილი ფორმულებიდან ერთი მაინც სასარგებლო მოგეჩვენათ. თუ თქვენ შეგხვედრიათ სხვა საძიებო ამოცანები, რომლებისთვისაც ამ გაკვეთილის ინფორმაციაში ვერ იპოვნეთ შესაბამისი გამოსავალი, მოგერიდებათ აღწეროთ თქვენი პრობლემა კომენტარებში და ჩვენ ყველა ერთად შევეცდებით გადავჭრათ იგი.
ვთქვათ, თქვენ უნდა იპოვოთ თანამშრომლის ტელეფონის გაფართოება მათი ნომრის გამოყენებით და სწორად შეაფასოთ საკომისიოს განაკვეთი გაყიდვების თანხისთვის. თქვენ ეძებთ მონაცემებს, რათა სწრაფად და ეფექტურად იპოვოთ კონკრეტული მონაცემები სიაში და ავტომატურად ამოწმებთ, არის თუ არა მონაცემები სწორად გამოყენებული. მონაცემების დათვალიერების შემდეგ, შეგიძლიათ განახორციელოთ გამოთვლები და აჩვენოთ შედეგები დაბრუნებული მნიშვნელობების მითითებით. მონაცემთა სიაში მნიშვნელობების მოსაძებნად და შედეგების ჩვენების რამდენიმე გზა არსებობს.
ამ სტატიაში
სიაში მნიშვნელობების პოვნა ვერტიკალურად ზუსტი დამთხვევის მიხედვით
ამ ამოცანის შესასრულებლად შეგიძლიათ გამოიყენოთ VLOOKUP ფუნქცია ან INDEX და MATCH ფუნქციების კომბინაცია.
VLOOKUP ფუნქციის გამოყენების მაგალითები
VLOOKUP ფუნქცია.
INDEXES და MATCHES-ის მაგალითები
Რას ნიშნავს:
=INDEX(საჭიროა დააბრუნოს მნიშვნელობა C2:C10-დან, რომელიც ემთხვევა MATCH(პირველი მნიშვნელობა "კომბოსტო" მასივში B2:B10))
ფორმულა ეძებს პირველ მნიშვნელობას უჯრედში C2:C10 შესაბამისი კომბოსტო(B7-ში) და აბრუნებს მნიშვნელობას C7-ში ( 100 ) არის პირველი შესაბამისი მნიშვნელობა კომბოსტო.
დამატებითი ინფორმაციისთვის იხილეთ ინდექსის ფუნქცია და MATCH ფუნქცია.
სიაში მნიშვნელობების პოვნა ვერტიკალურად სავარაუდო შესატყვისით
ამისათვის გამოიყენეთ VLOOKUP ფუნქცია.
Მნიშვნელოვანი:დარწმუნდით, რომ პირველ რიგში მნიშვნელობები დალაგებულია ზრდის მიხედვით.
ზემოთ მოცემულ მაგალითში VLOOKUP ფუნქცია ეძებს სტუდენტის სახელს, რომელსაც აქვს 6 დაგვიანება A2:B7 დიაპაზონში. არ არის ჩანაწერი ცხრილში 6 დაგვიანებულია, ამიტომ VLOOKUP ეძებს შემდეგ უმაღლეს შესატყვისს 6-ის ქვემოთ და პოულობს მნიშვნელობას 5, რომელიც ასოცირდება სახელთან დეივდა ამიტომ ბრუნდება დეივ.
დამატებითი ინფორმაციისთვის იხილეთ VLOOKUP ფუნქციის განყოფილება.
ვერტიკალური მნიშვნელობების პოვნა უცნობი ზომის სიაში ზუსტი შესატყვისით
ამ ამოცანის შესასრულებლად გამოიყენეთ OFFSET და MATCH ფუნქციები.
Შენიშვნა:ეს მიდგომა გამოიყენება, თუ მონაცემები გარე მონაცემთა დიაპაზონშია, რომელსაც ყოველდღიურად ახლებთ. თქვენ იცით, რომ B სვეტს აქვს ფასი, მაგრამ თქვენ არ იცით, რამდენ რიგს აბრუნებს სერვერი და პირველი სვეტი არ არის დალაგებული ანბანურად.
C1არის დიაპაზონის ზედა მარცხენა უჯრედი (ასევე უწოდებენ სასტარტო უჯრედს).
მატჩი ("ფორთოხალი"; C2: C7; 0)ეძებს ნარინჯისფერ ფერს C2:C7 დიაპაზონში. თქვენ არ უნდა შეიტანოთ საწყისი უჯრედი დიაპაზონში.
1 - სვეტების რაოდენობა საწყისი უჯრედის მარჯვნივ, რომლებისთვისაც დაბრუნებული მნიშვნელობა უნდა დაბრუნდეს. ჩვენს მაგალითში, დაბრუნების მნიშვნელობა არის სვეტში D, Გაყიდვების.
სიაში მნიშვნელობების პოვნა ჰორიზონტალურად ზუსტი დამთხვევის მიხედვით
ამ ამოცანის შესასრულებლად გამოიყენება HLOOKUP ფუნქცია. ქვემოთ მოცემულია მაგალითი.
HLOOKUP ფუნქცია ასრულებს სვეტის ძიებას Გაყიდვებისდა აბრუნებს მნიშვნელობას მე-5 სტრიქონიდან მითითებულ დიაპაზონში.
დამატებითი ინფორმაციისთვის იხილეთ HLOOKUP ფუნქციების განყოფილება.
შექმენით საძიებო ფორმულა საძიებო ოსტატის გამოყენებით (მხოლოდ Excel 2007)
Შენიშვნა: Lookup Wizard-ის დანამატი მოძველებულია Excel 2010-ში. ეს ფუნქცია შეიცვალა Function Wizard-ით და ხელმისაწვდომი საძიებო და მითითების (მინიშნება) ფუნქციებით.
Excel 2007-ში საძიებო ოსტატი ქმნის საძიებო ფორმულას სამუშაო ფურცლის მონაცემებზე დაყრდნობით, რომელსაც აქვს მწკრივების და სვეტების სათაურები. საძიებო ოსტატი დაგეხმარებათ იპოვოთ სხვა მნიშვნელობები ზედიზედ, როდესაც იცით მნიშვნელობა ერთ სვეტში და პირიქით. საძიებო ოსტატი იყენებს ინდექსს და MATCH-ს მის მიერ შექმნილ ფორმულებში.
Excel საოფისე პროგრამის მთავარი მიზანი გათვლების განხორციელებაა. ამ პროგრამის დოკუმენტი (წიგნი) შეიძლება შეიცავდეს ბევრ ფურცელს გრძელი ცხრილებით, სავსე რიცხვებით, ტექსტით ან ფორმულებით. ავტომატური სწრაფი ძებნა საშუალებას გაძლევთ იპოვოთ მათში საჭირო უჯრედები.
მარტივი ძებნა
Excel-ის ცხრილებში მნიშვნელობის მოსაძებნად, მთავარი ჩანართზე, გახსენით Find and Replace ინსტრუმენტის ჩამოსაშლელი სია და დააწკაპუნეთ პუნქტზე Find. იგივე ეფექტის მიღება შესაძლებელია კლავიატურის მალსახმობის Ctrl + F გამოყენებით.
უმარტივეს შემთხვევაში, Find and Replace ფანჯარაში, რომელიც გამოჩნდება, შეიყვანეთ სასურველი მნიშვნელობა და დააწკაპუნეთ Find All.
როგორც ხედავთ, ძიების შედეგები გამოჩნდა დიალოგური ფანჯრის ბოლოში. ნაპოვნი მნიშვნელობები ხაზგასმულია ცხრილში წითლად. თუ "იპოვე ყველას" ნაცვლად დააწკაპუნებ "იპოვე შემდეგი", მაშინ პირველი ამ მნიშვნელობის მქონე უჯრედი მოიძებნება, ხოლო როდესაც ისევ დააწკაპუნებთ - მეორე.
ტექსტი იძებნება იმავე გზით. ამ შემთხვევაში, საძიებო ტექსტი იწერება საძიებო ზოლში.
თუ მონაცემები ან ტექსტი არ არის მოძიებული მთელ Excel-ის ცხრილებში, მაშინ ჯერ უნდა აირჩიოთ საძიებო ზონა.
გაფართოებული ძებნა
დავუშვათ, რომ გსურთ იპოვოთ ყველა მნიშვნელობა 3000-დან 3999-მდე. ამ შემთხვევაში, თქვენ ჩაწერეთ 3??? საძიებო სტრიქონში. Wildcard "?" ცვლის ნებისმიერ სხვას.
შესრულებული ძიების შედეგების გაანალიზებით, შეიძლება აღინიშნოს, რომ სწორ 9 შედეგთან ერთად, პროგრამამ გამოიღო მოულოდნელი, წითლად ხაზგასმული. ისინი ასოცირდება უჯრედში ან ფორმულაში 3 რიცხვის არსებობასთან.
თქვენ შეგიძლიათ კმაყოფილი იყოთ მიღებული შედეგების უმეტესობით, არასწორის უგულებელყოფით. მაგრამ Excel 2010-ში საძიებო ფუნქციას შეუძლია ბევრად უფრო ზუსტად იმუშაოს. ეს არის ოფციების ინსტრუმენტი დიალოგურ ფანჯარაში.
"ოფციების" დაწკაპუნებით მომხმარებელს შეუძლია განახორციელოს გაფართოებული ძებნა. უპირველეს ყოვლისა, ყურადღება მივაქციოთ „Search area“ პუნქტს, რომელშიც ნაგულისხმევად არის მითითებული „Formulas“ მნიშვნელობა.
ეს ნიშნავს, რომ ძიება განხორციელდა, მათ შორის იმ უჯრედებში, სადაც არის არა მნიშვნელობა, არამედ ფორმულა. მათში მე-3 ნომრის არსებობამ სამი არასწორი შედეგი მისცა. თუ ძიების ზონად აირჩევთ „მნიშვნელობებს“, მხოლოდ მონაცემები მოიძებნება და ფორმულის უჯრედებთან დაკავშირებული არასწორი შედეგები გაქრება.
იმისათვის, რომ თავიდან აიცილოთ ერთადერთი დარჩენილი არასწორი შედეგი პირველ სტრიქონზე, გაფართოებული ძიების ფანჯარაში აირჩიეთ პუნქტი "მთელი უჯრედი". ამის შემდეგ, ძიების შედეგი 100% ზუსტი ხდება.
ასეთი შედეგის მიღწევა შესაძლებელი იყო პუნქტის „მთელი უჯრედის“ დაუყოვნებლივ არჩევით (თუნდაც „ფორმულების“ მნიშვნელობის დატოვება „ძებნის ზონაში“).
ახლა მოდით მივმართოთ "ძებნა" პუნქტს.
თუ ნაგულისხმევი "ფურცელზე" ნაცვლად აირჩევთ მნიშვნელობას "წიგნში", მაშინ არ არის საჭირო თქვენთვის სასურველი უჯრედების ფურცელზე ყოფნა. სკრინშოტი აჩვენებს, რომ მომხმარებელმა დაიწყო ძებნა ცარიელ ფურცელ 2-ზე ყოფნისას.
გაფართოებული ძიების ფანჯარაში შემდეგი ელემენტია "Browse", რომელსაც აქვს ორი მნიშვნელობა. ნაგულისხმევად, ის დაყენებულია "სტრიქონების მიხედვით", რაც ნიშნავს უჯრედების სკანირების თანმიმდევრობას რიგების მიხედვით. სხვა მნიშვნელობის არჩევა - "სვეტების მიხედვით", მხოლოდ შეცვლის ძიების მიმართულებას და შედეგების თანმიმდევრობას.
Microsoft Excel-ის დოკუმენტებში ძიებისას, ასევე შეგიძლიათ გამოიყენოთ სხვა სიმბოლო - "*". თუ განიხილება "?" ნიშნავს ნებისმიერ სიმბოლოს, მაშინ "*" ცვლის არა ერთ, არამედ ნებისმიერი რაოდენობის სიმბოლოს. ქვემოთ მოცემულია ლუიზიანას ძიების ეკრანის სურათი.
ზოგჯერ ძიება უნდა იყოს რეგისტრირებული. თუ სიტყვა louisiana არის დიდი ასოებით, მაშინ ძიების შედეგები არ შეიცვლება. მაგრამ თუ გაფართოებული ძიების ფანჯარაში აირჩიეთ "case sensitive", ძიება წარუმატებელი იქნება. პროგრამა განიხილავს სიტყვებს Louisiana და louisiana განსხვავებულად და, რა თქმა უნდა, ვერ იპოვის პირველ მათგანს.
მოძებნეთ ჯიშები
მატჩების პოვნა
ზოგჯერ საჭიროა ცხრილში დუბლიკატი მნიშვნელობების აღმოჩენა. შესატყვისების მოსაძებნად, ჯერ უნდა მონიშნოთ საძიებო დიაპაზონი. შემდეგ, იმავე Home ჩანართზე, Styles ჯგუფში, გახსენით პირობითი ფორმატირების ხელსაწყო. შემდეგი, თანმიმდევრულად აირჩიეთ ელემენტი "უჯრედების შერჩევის წესები" და "მნიშვნელობების დუბლიკატი".
შედეგი ნაჩვენებია ქვემოთ მოცემულ ეკრანის სურათზე.
საჭიროების შემთხვევაში, მომხმარებელს შეუძლია შეცვალოს შესაბამისი უჯრედების ვიზუალური ჩვენების ფერი.
ფილტრაცია
სხვა ტიპის ძებნა არის ფილტრაცია. დავუშვათ, მომხმარებელს სურს მოიძიოს რიცხვითი მნიშვნელობები B სვეტში 3000-დან 4000-მდე.
როგორც ხედავთ, ნაჩვენებია მხოლოდ რიგები, რომლებიც აკმაყოფილებენ შეყვანილ მდგომარეობას. ყველა დანარჩენი დროებით დამალული იყო. საწყის მდგომარეობაში დასაბრუნებლად გაიმეორეთ ნაბიჯი 2.
განიხილებოდა ძიების სხვადასხვა ვარიანტი Excel 2010-ის მაგალითის გამოყენებით. როგორ მოვიძიოთ სხვა ვერსიების Excel-ში? 2003 წლის ვერსიაში ფილტრაციაზე გადასვლაში განსხვავებაა. მენიუში „მონაცემები“ ზედიზედ აირჩიეთ ბრძანებები „ფილტრი“, „ავტოფილტრი“, „მდგომარეობა“ და „მომხმარებლის ავტოფილტრი“.
ვიდეო: ძიება Excel-ის ცხრილებში
