Excel საძიებელი ფორმულა მრავალი კრიტერიუმი

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

მასივის ფორმულა მოიცავს მაკეტის ფუნქციას ინექციის ფუნქციის შიგნით.

ამ tutorial მოიცავს ეტაპობრივად მაგალითი შექმნის lookup ფორმულა, რომელიც იყენებს მრავალჯერადი კრიტერიუმები მოძიების მიმწოდებელი ტიტანის Widgets in ნიმუში მონაცემთა ბაზაში.

ქვემოთ მოყვანილი ნაბიჯების გადაღების შემდეგ ქვემოთ ჩამოთვლილი ფორმულის შექმნისა და გამოყენებისას თქვენ დადის.

01 09

შეიყვანეთ სამეურვეო მონაცემები

პირველი ნაბიჯი სამეურვეოში არის Excel- ის მონაცემების შეყვანა.

იმისათვის, რომ დაიცვას ნაბიჯი სამეურვეოში შეიყვანოთ მონაცემები ნაჩვენები გამოსახულების ზემოთ შემდეგ საკნებში .

• შეიტანეთ მონაცემები დიაპაზონზე D1 to F2
• შეიყვანეთ მეორე დიაპაზონი საკნების D5- დან F11- ში

რიგები 3 და 4 დარჩა ცარიელი, რათა განთავსდეს მასივი ფორმულა ამ tutorial დროს.

სამეურვეოში არ შედის სურათზე გამოსახული ფორმატირება, მაგრამ ეს არ იმოქმედებს, თუ როგორ გამოიყურება საძიებელი ფორმულა.

ინფორმაცია ზემოთ ჩამოთვლილი პარამეტრების ფორმატირების შესახებ ხელმისაწვდომია ამ ძირითადი Excel ფორმატირების სამეურვეოში.

09 09

დაწყების INDEX ფუნქცია

INDEX ფუნქცია ერთ-ერთია იმ Excel- ში, რომელსაც აქვს მრავალი ფორმა. ფუნქციას აქვს Array ფორმა და Reference ფორმა .

Array Form იბრუნებს ფაქტობრივი მონაცემებს მონაცემთა ბაზაში ან მონაცემების ცხრილზე, ხოლო რეფერირების ფორმა გაძლევთ ცხრილში მითითებულ საკანში მითითებას ან ადგილმდებარეობას.

ამ tutorial- ში ჩვენ გამოვიყენებთ Array Form- ს, რადგან ჩვენ გვინდა ტიპური ვიჯეტების მიმწოდებლის სახელი ვიცოდეთ, ვიდრე ამ მომწოდებელს ამ ბაზარზე მითითებულ მონაცემთა ბაზაში.

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

სამეურვეო ნაბიჯები

1. დააჭირეთ საკანში F3, რათა ის აქტიური საკანში . ეს არის სადაც ჩვენ შეიყვანთ წყობილი ფუნქცია.
2. დაწკაპეთ ფორმულების ჩანართის ლენტი მენიუ.
3. აირჩიეთ საძიებელი და მითითება ლენტიდან ფუნქციის ჩამოსატვირთი სიის გასახსნელად.
4. დააჭირეთ INDEX- ში სიაში აირჩიეთ არგუმენტების დიალოგი ყუთი .
5. აირჩიეთ მასივი, row_num, col_num ვარიანტი დიალოგურ ფანჯარაში.
6. დააჭირეთ OK გახსნას INDEX ფუნქციის დიალოგური ფანჯარა.

09 09

EnterEX ფუნქციის Array დავა

საჭიროა პირველი არგუმენტი Array არგუმენტი. ეს არგუმენტი განსაზღვრავს საკნების სპექტრი სასურველ მონაცემებს.

ამ ლოგიკისთვის ეს არგუმენტი იქნება ჩვენი საჩვენებელი მონაცემთა ბაზა .

სამეურვეო ნაბიჯები

1. In INDEX ფუნქციის დიალოგური ფანჯარა , დააჭირეთ Array ხაზი.
2. დიალოგური ფანჯრის დიაპაზონში შეყვანის სამუშაოებში, D6- დან F11- ის გამოყოფა.

04 09

დაწყებული სავალდებულო MATCH ფუნქცია

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

წყობილი ფუნქცია უნდა ჩაიწეროს როგორც პირველი ფუნქციის ერთ-ერთი არგუმენტი.

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

მნიშვნელოვანია აღინიშნოს, რომ, როდესაც ფუნქციების ხელით შესვლისას, ფუნქციის არგუმენტები ერთმანეთისგან გამოყოფილია მძიმით "," .

შეყვანის MATCH ფუნქციის Lookup_value დავა

პირველი ნაბიჯი შეყვანილი MATCH ფუნქცია შესვლის Lookup_value არგუმენტი.

Lookup_value იქნება ადგილმდებარეობის ან საკანში მინიშნება საძიებო ტერმინი ჩვენ გვინდა, რომ ემთხვევა მონაცემთა ბაზაში.

ჩვეულებრივ Lookup_value იღებს მხოლოდ ერთი ძიების კრიტერიუმს ან ტერმინს. სხვადასხვა კრიტერიუმების მოძიებისთვის, ჩვენ უნდა გავაგრძელოთ Lookup_value .

ეს კეთდება ორი ან მეტი უჯრედის მითითების შეთავსებით ან მიერთებით, ერთად ampersand სიმბოლო " & " გამოყენებით.

სამეურვეო ნაბიჯები

1. IN INDEX ფუნქციის დიალოგური ფანჯარა, დააჭირეთ Row_num ხაზს.
2. შეიტანეთ ფუნქციის სახელი მატჩი, რომელსაც მოყვება ღია მრგვალი ფრჩხილი " ( "
3. დაწკაპეთ საკანში D3, რომ შეიყვანოთ ეს საკანში მინიშნება დიალოგურ ყუთში.
4. შეიყვანეთ ampersand " & " შემდეგ საკანში მინიშნება D3 იმისათვის, რომ დაამატოთ მეორე საკანში მინიშნება.
5. დააჭირეთ საკანში E3 ამ მეორე საკანში მინიშნება შევიდა დიალოგური ფანჯარა.
6. შეიტანეთ მძიმით "," საკანში მითითების შემდეგ E3 დაასრულოს MATCH ფუნქციის Lookup_value არგუმენტის შესვლის.
7. დატოვეთ INDEX ფუნქცია დიალოგური ფანჯარაში, რომელიც ღიაა შემდეგი ნაბიჯით სამეურვეოში.

უკანასკნელი ნაბიჯი სამეურვეოში Lookup_values ​​იქნება შევიდა საკნებში D3 და E3 სამუშაოები.

09 09

დასტა Lookup_array ამისთვის MATCH ფუნქცია

ეს ნაბიჯი მოიცავს დამატებით Lookup_array არგუმენტს წყობილი MATCH ფუნქციისთვის.

Lookup_array არის რიგი საკნები, რომ MATCH ფუნქცია მოძებნის მოძიების Lookup_value არგუმენტი დასძინა წინა ნაბიჯი სამეურვეო.

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

მრავალრიცხოვან მასივებში შესასვლელად ჩვენ კვლავ ვიყენებთ ampersand " & " ერთმანეთთან შერწყმა .

სამეურვეო ნაბიჯები

ეს ნაბიჯები უნდა შეიყვანოთ წინამორბედ ნაბიჯში მწკრივების Row_num ხაზის INDEX ფუნქციის დიალოგურ ფანჯარაში .

1. დაწკაპეთ Row_num ხაზის შემდეგ მძიმით მოთავსება ჩაწერის პუნქტში ბოლოს შესვლის ბოლოს.
2. ხაზგასმულია დიაპაზონზე D6- დან D11- ზე. ეს არის პირველი მასივი ფუნქცია არის ძებნის.
3. შეიყვანეთ ampersand " & " შემდეგ საკანში მითითება D6: D11 რადგან ჩვენ გვინდა ფუნქცია ძებნის ორი მასივები.
4. გამოაგზავნეთ უჯრედების E6 to E11 in worksheet შესვლის სპექტრი. ეს არის მეორე მასივი ფუნქციის მოძიება.
5. შეიტანეთ მძიმით "," საკანში მითითების შემდეგ E3 დაასრულოს MATCH ფუნქციის Lookup_array არგუმენტის შესვლის.
6. დატოვეთ INDEX ფუნქცია დიალოგური ფანჯარაში, რომელიც ღიაა შემდეგი ნაბიჯით სამეურვეოში.

09 09

დაამატეთ მატჩის ტიპი და შეავსეთ მატჩის ფუნქცია

MATCH ფუნქციის მესამე და საბოლოო არგუმენტი არის Match_type არგუმენტი.

ეს არგუმენტი ეუბნება Excel როგორ შეესაბამება Lookup_value ერთად ღირებულებების Lookup_array. არჩევანი: 1, 0, ან -1.

ეს არგუმენტი არის სურვილისამებრ. თუ იგი არ არის გამოტოვებული, ფუნქცია იყენებს 1-ის მნიშვნელობას.

• თუ Match_type = 1 ან გამოტოვებული: მატჩი პოულობს უმსხვილეს მნიშვნელობას, რომელიც ნაკლებია ან ტოლია Lookup_value. Lookup_array მონაცემები უნდა დალაგდეს აღმავალი შეკვეთით.
• თუ match_type = 0: მატჩი აღმოაჩენს პირველ მნიშვნელობას, რომელიც ზუსტად შეესაბამება Lookup_value- ს. Lookup_array მონაცემები შეიძლება დალაგებული ნებისმიერი მიზნით.
• თუ Match_type = -1: მატჩი პოულობს ყველაზე პატარა ღირებულებას, რომელიც მეტია ან ტოლია Lookup_value. Lookup_array მონაცემები უნდა დალაგდეს დაღმავალი მიზნით.

სამეურვეო ნაბიჯები

ეს ნაბიჯები უნდა შეიყვანოთ წინამორბედ ნაბიჯში მწკრივების Row_num ხაზის INDEX ფუნქციის დიალოგურ ფანჯარაში .

1. მას შემდეგ, რაც Row_num ხაზის მძიმით გამოტოვეთ ნულოვანი " 0 ", ვინაიდან ჩვენ გვინდა, რომ წყობილი ფუნქცია ზუსტი უჯრედების დაბრუნების პირობებში დავწეროთ საკნებში D3 და E3.
2. აკრიფეთ დახურვის მრგვალი ფრჩხილი " ) " დასრულება MATCH ფუნქცია.
3. დატოვეთ INDEX ფუნქცია დიალოგური ფანჯარაში, რომელიც ღიაა შემდეგი ნაბიჯით სამეურვეოში.

09 09

Back to INDEX ფუნქცია

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

ეს მესამე და საბოლოო არგუმენტი არის Column_num არგუმენტი, რომელიც ეუბნება Excel- ს სვეტის ნომრის დიაპაზონში D6- დან F11- ს, სადაც იპოვის ის ინფორმაცია, რომელსაც ჩვენ გვინდა დავბრუნდეთ ფუნქციით. ამ შემთხვევაში, მიმწოდებელი ტიტანის ვიჯეტები .

სამეურვეო ნაბიჯები

1. დააჭირეთ ღილაკს Column_num დიალოგურ ფანჯარაში.
2. შეიყვანეთ ნომერზე სამი " 3 " (არ არის შეთავაზებები), რადგან ჩვენ ვეძებთ მონაცემებს D6- დან F11- ის დიაპაზონის მესამე სვეტში.
3. არ დააჭიროთ OK ან დახურეთ INDEX ფუნქციის დიალოგური ფანჯარა. ეს უნდა იყოს ღია მომავალი ნაბიჯი სამეურვეოში - შექმნის მასივი ფორმულა .

08 09

შექმნა Array ფორმულა

დიალოგური ფანჯრის დახურვის დაწყებამდე ჩვენ უნდა შევაგროვოთ ჩვენი წყობილი ფუნქცია მასივის ფორმულაში .

მასივი ფორმულა არის ის, რაც საშუალებას იძლევა მოძებნოთ მრავალი ტერმინი მონაცემთა ცხრილზე. ამ ტერმინალში ჩვენ ვეძებთ ორ პირობას: ვიჯეტები სვეტიდან 1 და ტიტანის სვეტიდან 2.

Excel- ში მასივი ფორმულის შექმნა კეთდება CTRL , SHIFT და კლავიშების კლავიშზე დაჭერით.

ამ გასაღებების დაჭერის ეფექტი ერთად არის ფუნქციის გარშემო curly braces: {} მითითება იმისა, რომ ეს არის მასივი ფორმულა.

სამეურვეო ნაბიჯები

1. დასრულებული დიალოგური ფანჯრით, რომელიც ჯერ კიდევ გახსნილია ამ ტუტორიის წინა საფეხურზე, დააჭირეთ ღილაკს CTRL და SHIFT კლავიშზე კლავიშზე, დააჭირეთ ღილაკს და გაააქტიურეთ ღილაკი Enter .
2. თუ სწორად გაკეთდა, დიალოგური ფანკი დაიხურება და # N / შეცდომა გამოჩნდება უჯრედის F3 - ის საკანში, სადაც შევედით ფუნქციას.
3. # N / შეცდომა გამოჩნდება უჯრედის F3- ში, რადგან უჯრედები D3 და E3 ცარიელია. D3 და E3 არის უჯრედები, სადაც ვუთხარი ფუნქციას, რომ იპოვოთ Lookup_values- ის 5-ე ეტაპზე. მას შემდეგ, რაც მონაცემები ამ ორ უჯრედს დაემატება, შეცდომა მონაცემთა ბაზაში შეიტანება.

09 09

ძიების კრიტერიუმების დამატება

ბოლო ნაბიჯი სამეურვეოში არის საძიებო ტერმინების დამატება ჩვენი worksheet- ში.

როგორც წინა ნაბიჯში აღვნიშნეთ, ჩვენ ვსაუბრობთ ვადები ვიჯეტებიდან სვეტის 1 და ტიტანის 2-დან.

თუ მხოლოდ, თუ ჩვენი ფორმულა იკვლევს ორივე ტერმინს მონაცემთა ბაზაში შესაბამისი სვეტებისთვის, ის დააბრუნებს მნიშვნელობას მესამე სვეტში.

სამეურვეო ნაბიჯები

1. დაწკაპეთ D3- ზე.
2. დააჭირეთ Widgets და დააჭირეთ Enter ღილაკს კლავიატურაზე.
3. დაწკაპეთ საკანში E3.
4. ტიპი ტიტანის და დააჭირეთ Enter ღილაკს კლავიატურაზე.
5. მიმწოდებლის სახელი Widgets Inc. უნდა გამოჩნდეს საკანში F3 - ფუნქციის მდებარეობა, რადგან ეს არის ერთადერთი მიმწოდებელი ჩამოთვლილი, რომელიც ყიდის ტიტანის ვიჯეტები.
6. როდესაც თქვენ დააჭირეთ საკანში F3 სრული ფუნქცია
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   გამოჩნდება ფორმულა ბარი ზემოთ worksheet .

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