Excel-ის VLOOKUP ფუნქციის COLUMN ფუნქციის კომბინაციით, შეგიძლიათ შექმნათ საძიებო ფორმულა, რომელიც აბრუნებს მრავალ მნიშვნელობას მონაცემთა ბაზის ერთი მწკრივიდან ან მონაცემთა ცხრილიდან. ისწავლეთ როგორ შექმნათ საძიებო ფორმულა, რომელიც დააბრუნებს მრავალ მნიშვნელობას მონაცემთა ერთი ჩანაწერიდან.
ამ სტატიაში მოცემული ინსტრუქციები ვრცელდება Excel 2019, 2016, 2013, 2010; და Excel Microsoft 365-ისთვის.
ბოლო ხაზი
ძიების ფორმულა მოითხოვს COLUMN ფუნქციის ჩადგმას VLOOKUP-ის შიგნით. ფუნქციის ჩადგმა გულისხმობს მეორე ფუნქციის შეყვანას, როგორც პირველი ფუნქციის ერთ-ერთ არგუმენტს.
შეიყვანეთ სახელმძღვანელოს მონაცემები
ამ სახელმძღვანელოში, COLUMN ფუნქცია შეყვანილია, როგორც სვეტის ინდექსის ნომრის არგუმენტი VLOOKUP-ისთვის. გაკვეთილის ბოლო ნაბიჯი მოიცავს საძიებო ფორმულის დამატებით სვეტებზე კოპირებას არჩეული ნაწილისთვის დამატებითი მნიშვნელობების მისაღებად.
ამ გაკვეთილის პირველი ნაბიჯი არის მონაცემების შეყვანა Excel-ის სამუშაო ფურცელში. იმისათვის, რომ მიჰყვეთ ამ სახელმძღვანელოში მოცემულ ნაბიჯებს, შეიყვანეთ ქვემოთ მოცემულ სურათზე ნაჩვენები მონაცემები შემდეგ უჯრედებში:
- შეიყვანეთ მონაცემთა ზედა დიაპაზონი D1-დან G1-მდე უჯრედებში.
- შეიყვანეთ მეორე დიაპაზონი D4-დან G10-მდე უჯრედებში.
ძიების კრიტერიუმები და ამ სახელმძღვანელოში შექმნილი საძიებო ფორმულა შეყვანილია სამუშაო ფურცლის მე-2 რიგში.
ეს სახელმძღვანელო არ შეიცავს სურათზე ნაჩვენები Excel-ის ძირითად ფორმატირებას, მაგრამ ეს არ ახდენს გავლენას საძიებო ფორმულის მუშაობაზე.
შექმენით დასახელებული დიაპაზონი მონაცემთა ცხრილისთვის
დასახელებული დიაპაზონი არის მარტივი გზა ფორმულაში მონაცემთა დიაპაზონის მითითებისთვის. იმის ნაცვლად, რომ აკრიფოთ უჯრედის მითითებები მონაცემებისთვის, ჩაწერეთ დიაპაზონის სახელი.
დასახელებული დიაპაზონის გამოყენების მეორე უპირატესობა ის არის, რომ ამ დიაპაზონის უჯრედების მითითებები არასოდეს იცვლება მაშინაც კი, როდესაც ფორმულა კოპირდება სამუშაო ფურცლის სხვა უჯრედებში. დიაპაზონის სახელები არის უჯრედების აბსოლუტური მითითებების გამოყენების ალტერნატივა ფორმულების კოპირებისას შეცდომების თავიდან ასაცილებლად.
დიაპაზონის სახელი არ შეიცავს მონაცემთა სათაურებს ან ველების სახელებს (როგორც ნაჩვენებია მე-4 რიგში), მხოლოდ მონაცემებს.
-
მონიშნეთ უჯრედები D5-დან G10-მდე სამუშაო ფურცელში.
Image -
მოათავსეთ კურსორი A სვეტის ზემოთ მდებარე სახელის ველში, ჩაწერეთ Table, შემდეგ დააჭირეთ Enter. უჯრედებს D5-დან G10-მდე აქვთ ცხრილის დიაპაზონის სახელი.
Image - VLOOKUP ცხრილის მასივის არგუმენტის დიაპაზონის სახელი მოგვიანებით გამოიყენება ამ სახელმძღვანელოში.
გახსენით VLOOKUP დიალოგის ფანჯარა
მიუხედავად იმისა, რომ შესაძლებელია საძიებო ფორმულის პირდაპირ უჯრედში ჩაწერა სამუშაო ფურცელში, ბევრ ადამიანს უჭირს სინტაქსის სწორი შენარჩუნება - განსაკუთრებით რთული ფორმულისთვის, როგორიცაა ამ სახელმძღვანელოში გამოყენებული.
როგორც ალტერნატივა, გამოიყენეთ VLOOKUP ფუნქციის არგუმენტების დიალოგური ფანჯარა. Excel-ის თითქმის ყველა ფუნქციას აქვს დიალოგური ფანჯარა, სადაც ფუნქციის თითოეული არგუმენტი ცალკე ხაზშია შეყვანილი.
-
აირჩიეთ სამუშაო ფურცლის უჯრედი E2. ეს არის ადგილი, სადაც გამოჩნდება ორგანზომილებიანი საძიებო ფორმულის შედეგები.
Image -
ლენტაზე გადადით ფორმულების ჩანართზე და აირჩიეთ მოძებნა და მითითება.
Image -
აირჩიეთ VLOOKUP, რათა გახსნათ ფუნქციის არგუმენტები დიალოგური ფანჯარა.
Image - ფუნქციის არგუმენტების დიალოგური ფანჯარა არის VLOOKUP ფუნქციის პარამეტრების შეყვანა.
შეიყვანეთ საძიებო მნიშვნელობის არგუმენტი
ჩვეულებრივ, საძიებო მნიშვნელობა ემთხვევა მონაცემთა ცხრილის პირველ სვეტში მოცემულ ველს. ამ მაგალითში, საძიებო მნიშვნელობა ეხება იმ ნაწილის სახელს, რომელიც გსურთ იპოვოთ ინფორმაცია. საძიებო მნიშვნელობის მონაცემების დასაშვები ტიპები არის ტექსტური მონაცემები, ლოგიკური მნიშვნელობები, რიცხვები და უჯრედების მითითებები.
უჯრედების აბსოლუტური ცნობები
როდესაც ფორმულები კოპირდება Excel-ში, უჯრედების მითითებები იცვლება ახალი მდებარეობის ასახვაზე. თუ ეს მოხდება, D2, საძიებელი მნიშვნელობის უჯრედის მითითება, იცვლება და ქმნის შეცდომებს უჯრედებში F2 და G2.
უჯრედების აბსოლუტური მითითებები არ იცვლება ფორმულების კოპირებისას.
შეცდომების თავიდან ასაცილებლად, გადააქციეთ უჯრედის მითითება D2 უჯრედის აბსოლუტურ მითითებად. უჯრედის აბსოლუტური მითითების შესაქმნელად დააჭირეთ F4 ღილაკს. ეს ამატებს დოლარის ნიშნებს უჯრედის მითითების გარშემო, როგორიცაა $D$2.
-
ფუნქციის არგუმენტების დიალოგურ ფანჯარაში მოათავსეთ კურსორი lookup_value ტექსტურ ველში. შემდეგ, სამუშაო ფურცელში აირჩიეთ უჯრედი D2, რომ დაამატოთ ეს უჯრედი lookup_value. უჯრედი D2 არის სადაც შეიყვანება ნაწილის სახელი.
Image -
ჩასმის წერტილის გადაადგილების გარეშე, დააჭირეთ F4 კლავიშს, რათა გადაიყვანოთ D2 უჯრედის აბსოლუტურ მითითებად $D$2.
Image - დატოვეთ VLOOKUP ფუნქციის დიალოგური ფანჯარა ღია გაკვეთილის შემდეგი ნაბიჯისთვის.
შეიყვანეთ ცხრილის მასივის არგუმენტი
ცხრილის მასივი არის მონაცემთა ცხრილი, რომელსაც ეძებს საძიებო ფორმულა თქვენთვის სასურველი ინფორმაციის მოსაძებნად. ცხრილის მასივი უნდა შეიცავდეს მონაცემთა მინიმუმ ორ სვეტს.
პირველი სვეტი შეიცავს საძიებო მნიშვნელობის არგუმენტს (რომელიც დაყენებული იყო წინა განყოფილებაში), ხოლო მეორე სვეტი იძებნება საძიებო ფორმულით, რათა იპოვოთ თქვენ მიერ მითითებული ინფორმაცია.
ცხრილის მასივის არგუმენტი უნდა იყოს შეყვანილი, როგორც დიაპაზონი, რომელიც შეიცავს უჯრედების მითითებებს მონაცემთა ცხრილისთვის, ან როგორც დიაპაზონის სახელი.
მონაცემთა ცხრილის VLOOKUP ფუნქციაში დასამატებლად, მოათავსეთ კურსორი table_array ტექსტურ ველში დიალოგურ ფანჯარაში და ჩაწერეთ Tableამ არგუმენტისთვის დიაპაზონის სახელის შესაყვანად.
Nest the COLUMN ფუნქცია
ჩვეულებრივ, VLOOKUP აბრუნებს მონაცემებს მონაცემთა ცხრილის მხოლოდ ერთი სვეტიდან.ეს სვეტი დაყენებულია სვეტის ინდექსის ნომრის არგუმენტით. თუმცა, ამ მაგალითში არის სამი სვეტი და სვეტის ინდექსის ნომერი უნდა შეიცვალოს საძიებო ფორმულის რედაქტირების გარეშე. ამის განსახორციელებლად, ჩადეთ COLUMN ფუნქცია VLOOKUP ფუნქციის შიგნით, როგორც არგუმენტი Col_index_num.
ფუნქციების ჩასმისას, Excel არ ხსნის მეორე ფუნქციის დიალოგურ ფანჯარას მისი არგუმენტების შესაყვანად. COLUMN ფუნქცია ხელით უნდა შეიყვანოთ. COLUMN ფუნქციას აქვს მხოლოდ ერთი არგუმენტი, Reference არგუმენტი, რომელიც არის უჯრედის მითითება.
COLUMN ფუნქცია აბრუნებს მითითების არგუმენტად მოწოდებული სვეტის რიცხვს. ის გარდაქმნის სვეტის ასოს რიცხვად.
საქონელის ფასის საპოვნელად გამოიყენეთ მონაცემები მონაცემთა ცხრილის მე-2 სვეტში. ეს მაგალითი იყენებს B სვეტს, როგორც მითითებას, რომ ჩასვას 2 არგუმენტში Col_index_num.
-
ფუნქციის არგუმენტები დიალოგურ ფანჯარაში მოათავსეთ კურსორი Col_index_num ტექსტურ ველში და ჩაწერეთ COLUMN(. (აუცილებლად ჩართეთ ღია მრგვალი ფრჩხილი.)
Image -
სამუშაო ფურცელში აირჩიეთ უჯრედი B1, რომ შეიყვანოთ ეს უჯრედის მითითება მითითების არგუმენტად.
Image - აკრიფეთ დახურვის მრგვალი ფრჩხილი COLUMN ფუნქციის დასასრულებლად.
შეიყვანეთ VLOOKUP დიაპაზონის საძიებო არგუმენტი
VLOOKUP's Range_lookup არგუმენტი არის ლოგიკური მნიშვნელობა (TRUE ან FALSE), რომელიც მიუთითებს, უნდა მოძებნოს თუ არა VLOOKUP-ის ზუსტი ან სავარაუდო შესატყვისი Lookup_value-სთან.
- TRUE ან გამოტოვებული: VLOOKUP აბრუნებს მჭიდრო შესატყვისს Lookup_value-სთან. თუ ზუსტი დამთხვევა ვერ მოიძებნა, VLOOKUP აბრუნებს შემდეგ უდიდეს მნიშვნელობას. Table_array-ის პირველი სვეტის მონაცემები უნდა იყოს დალაგებული ზრდადი თანმიმდევრობით.
- FALSE: VLOOKUP იყენებს ზუსტ შესატყვისს Lookup_value-სთან. თუ Table_array-ის პირველ სვეტში არის ორი ან მეტი მნიშვნელობა, რომელიც ემთხვევა საძიებო მნიშვნელობას, გამოიყენება პირველი ნაპოვნი მნიშვნელობა. თუ ზუსტი დამთხვევა ვერ მოიძებნა, ბრუნდება N/A შეცდომა.
ამ სახელმძღვანელოში მოძებნილი იქნება კონკრეტული ინფორმაცია კონკრეტული ტექნიკის ერთეულის შესახებ, ამიტომ Range_lookup დაყენებულია FALSE-ზე.
ფუნქციის არგუმენტების დიალოგურ ფანჯარაში მოათავსეთ კურსორი Range_lookup ტექსტურ ველში და აკრიფეთ False, რომ უთხრათ VLOOKUP-ს, დააბრუნოს მონაცემების ზუსტი შესატყვისი.
აირჩიეთ OK, რათა შეავსოთ საძიებო ფორმულა და დახუროთ დიალოგური ფანჯარა. უჯრედი E2 შეიცავს N/A შეცდომას, რადგან საძიებელი კრიტერიუმები არ არის შეყვანილი D2 უჯრედში. ეს შეცდომა დროებითია. ის გამოსწორდება, როდესაც საძიებო კრიტერიუმები დაემატება ამ სახელმძღვანელოს ბოლო ეტაპზე.
დააკოპირეთ საძიებო ფორმულა და შეიყვანეთ კრიტერიუმები
ძიების ფორმულა იპოვის მონაცემებს მონაცემთა ცხრილის რამდენიმე სვეტიდან ერთდროულად. ამისათვის საძიებო ფორმულა უნდა იყოს ყველა იმ ველში, საიდანაც გსურთ ინფორმაცია.
მონაცემთა ცხრილის მე-2, მე-3 და მე-4 სვეტებიდან (ფასი, ნაწილის ნომერი და მიმწოდებლის სახელი) მონაცემების მისაღებად, შეიყვანეთ ნაწილობრივი სახელი, როგორც Lookup_value.
რადგან მონაცემები სამუშაო ფურცელში ჩვეულებრივი ნიმუშია ასახული, დააკოპირეთ საძიებო ფორმულა უჯრედში E2 უჯრედებში F2 და G2 ფორმულის კოპირებისას Excel აახლებს უჯრედის შესაბამის მითითებას COLUMN ფუნქციაში (უჯრედი B1), რათა ასახოს ფორმულის ახალი მდებარეობა. Excel არ ცვლის უჯრედის აბსოლუტურ მითითებას (როგორიცაა $D$2) და დასახელებულ დიაპაზონს (ცხრილი) ფორმულის კოპირებისას.
Excel-ში მონაცემების კოპირების ერთზე მეტი გზა არსებობს, მაგრამ ყველაზე მარტივი გზაა Fill Handle-ის გამოყენება.
-
აირჩიეთ უჯრედი E2, სადაც მდებარეობს საძიებო ფორმულა, რომ გახადოთ ის აქტიურ უჯრედად.
Image -
გადაათრიეთ შევსების სახელური უჯრედში G2. უჯრედები F2 და G2 აჩვენებს N/A შეცდომას, რომელიც არის E2 უჯრედში.
Image -
საძიებო ფორმულების გამოსაყენებლად მონაცემთა ცხრილიდან ინფორმაციის მისაღებად, სამუშაო ფურცელში აირჩიეთ უჯრედი D2, ჩაწერეთ ვიჯეტი და დააჭირეთ შეიყვანეთ.
Image შემდეგი ინფორმაცია ნაჩვენებია E2-დან G2-მდე უჯრედებში.
- E2: $14,76 - ვიჯეტის ფასი
- F2: PN-98769 - ვიჯეტის ნაწილის ნომერი
- G2: Widgets Inc. - ვიჯეტების მომწოდებლის სახელი
-
VLOOKUP მასივის ფორმულის შესამოწმებლად, ჩაწერეთ სხვა ნაწილების სახელი D2 უჯრედში და დააკვირდით შედეგებს E2-დან G2-მდე უჯრედებში.
Image - თითოეული უჯრედი, რომელიც შეიცავს საძიებო ფორმულას, შეიცავს სხვადასხვა მონაცემს თქვენს მიერ მოძიებული ტექნიკის ერთეულის შესახებ.
VLOOKUP ფუნქცია ჩადგმული ფუნქციებით, როგორიცაა COLUMN, იძლევა მძლავრ მეთოდს მონაცემთა მოსაძიებლად ცხრილის შიგნით სხვა მონაცემების საძიებლად.
