VLOOKUP ფუნქციის გამოყენება მნიშვნელობების ჩანაცვლებისთვის

ვისაც ძალიან ეზარება ან წაკითხვის დრო არ აქვს - ნახეთ ვიდეო. დეტალები და ნიუანსები მოცემულია ქვემოთ მოცემულ ტექსტში.

პრობლემის ფორმულირება

ასე რომ, ჩვენ გვაქვს ორი მაგიდა - შეკვეთის მაგიდა и ფასების სია:

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

Solution

Excel ფუნქციების კომპლექტში, კატეგორიის ქვეშ ცნობები და მასივები (ძებნა და მითითება) არის ფუნქცია VPR (VLOOKUP).ეს ფუნქცია ეძებს მოცემულ მნიშვნელობას (ჩვენს მაგალითში ეს არის სიტყვა "ვაშლი") მითითებული ცხრილის (ფასის სიის) მარცხენა სვეტში, რომელიც მოძრაობს ზემოდან ქვემოდან და, როდესაც იპოვა, აჩვენებს მიმდებარე უჯრედის შინაარსს. (23 რუბლი) .სქემურად, ამ ფუნქციის მოქმედება შეიძლება წარმოდგენილი იყოს ასე:

ფუნქციის შემდგომი გამოყენების გასაადვილებლად, გააკეთეთ ერთი რამ ერთდროულად - მიუთითეთ ფასთა სიაში უჯრედების დიაპაზონი თქვენი საკუთარი სახელი. ამისათვის შეარჩიეთ ფასების სიის ყველა უჯრედი, გარდა „სათაურისა“ (G3: H19), აირჩიეთ მენიუდან. ჩასმა – სახელი – მინიჭება (ჩასმა - სახელი - განსაზღვრა) ან დააჭირეთ CTRL + F3 და შეიყვანეთ ნებისმიერი სახელი (სივრცის გარეშე). ფასი… ახლა, მომავალში, შეგიძლიათ გამოიყენოთ ეს სახელი ფასების სიაზე დასაკავშირებლად.

ახლა ჩვენ ვიყენებთ ფუნქციას VPR… აირჩიეთ უჯრედი, სადაც ის შეიტანება (D3) და გახსენით ჩანართი ფორმულები - ფუნქციის ჩასმა (ფორმულები - ჩასმა ფუნქცია)… კატეგორიაში ცნობები და მასივები (ძებნა და მითითება) იპოვნეთ ფუნქცია VPR (VLOOKUP) და დააჭირეთ OK… გამოჩნდება ფანჯარა ფუნქციისთვის არგუმენტების შესაყვანად:

VLOOKUP ფუნქციის გამოყენება მნიშვნელობების ჩანაცვლებისთვის

ჩვენ რიგრიგობით ვავსებთ მათ:

  • სასურველი ღირებულება (საძიებო მნიშვნელობა) – პროდუქტის დასახელება, რომელიც ფუნქციამ უნდა მოიძიოს ფასების სიის მარცხენა სვეტში. ჩვენს შემთხვევაში, სიტყვა "ვაშლი" B3 უჯრედიდან.
  • მაგიდის (მაგიდის მასივი) – ცხრილი, საიდანაც აღებულია სასურველი მნიშვნელობები uXNUMXbuXNUMX, ანუ ჩვენი ფასების სია. ცნობისთვის, ჩვენ ვიყენებთ ჩვენს საკუთარ სახელს "ფასი", რომელიც ადრე იყო მოცემული. თუ სახელი არ დაასახელეთ, შეგიძლიათ უბრალოდ აირჩიოთ ცხრილი, მაგრამ არ დაგავიწყდეთ ღილაკზე დაჭერა F4ბმულის დასამაგრებლად დოლარის ნიშნებით, რადგან წინააღმდეგ შემთხვევაში, ის ჩამოიწევს ქვემოთ ჩვენი ფორმულის კოპირებისას სვეტის D3:D30 დანარჩენ უჯრედებში.
  • სვეტის_ნომერი (სვეტის ინდექსის ნომერი) – სერიული ნომერი (არა ასო!) ფასთა ჩამონათვალის სვეტისა, საიდანაც ავიღოთ ფასების მნიშვნელობები. ფასების სიის პირველი სვეტი სახელებით არის დანომრილი 1, შესაბამისად, ჩვენ გვჭირდება ფასი ნომრით 2 სვეტიდან.
  • ინტერვალის_ძიება (დიაპაზონის ძიება) - ამ ველში შეიძლება მხოლოდ ორი მნიშვნელობის შეყვანა: FALSE ან TRUE:
      • თუ შეყვანილია მნიშვნელობა 0 or იტყუება (ცრუ), მაშინ სინამდვილეში ეს ნიშნავს, რომ მხოლოდ ძებნაა დაშვებული ზუსტი დამთხვევა, ანუ თუ ფუნქცია ვერ იპოვის ფასთა სიაში შეკვეთის ცხრილში მითითებულ არასტანდარტულ ნივთს (თუ, მაგალითად, „ქოქოსი“ არის შეყვანილი), წარმოქმნის #N/A (მონაცემების გარეშე) შეცდომას.
      • თუ შეყვანილია მნიშვნელობა 1 or TRUE (ნამდვილი), მაშინ ეს ნიშნავს, რომ თქვენ ნებას რთავთ ძიებას არა ზუსტი, არამედ სავარაუდო მატჩი, ანუ „ქოქოსის“ შემთხვევაში, ფუნქცია შეეცდება მოიძიოს პროდუქტის სახელი, რომელიც მაქსიმალურად ახლოს იქნება „ქოქოსთან“ და დააბრუნოს ამ სახელწოდების ფასი. უმეტეს შემთხვევაში, ასეთი მიახლოებითი ჩანაცვლება შეიძლება შეაფერხოს მომხმარებელს არასწორი პროდუქტის ღირებულების ჩანაცვლებით, რომელიც რეალურად იყო იქ! ასე რომ, უმეტეს რეალური ბიზნეს პრობლემებისთვის, მიახლოებითი ძებნა უმჯობესია არ დაუშვათ. გამონაკლისია, როდესაც ჩვენ ვეძებთ ციფრებს და არა ტექსტს - მაგალითად, ნაბიჯების ფასდაკლების გამოთვლისას.

ყველაფერი! რჩება დაჭერა OK და დააკოპირეთ შეყვანილი ფუნქცია მთელ სვეტში.

# N / A შეცდომები და მათი ჩახშობა

ფუნქცია VPR (VLOOKUP) აბრუნებს #N/A შეცდომას (# N/A) თუ:

  • ჩართულია ზუსტი ძებნა (არგუმენტი ინტერვალის ხედი = 0) და სასურველი სახელი არ არის მაგიდის.
  • უხეში ძებნა შედის (ინტერვალის ხედი = 1), მაგრამ მაგიდის, რომელშიც მიმდინარეობს ძიება არ არის დალაგებული სახელების ზრდის მიხედვით.
  • უჯრედის ფორმატი, საიდანაც მოდის სახელის საჭირო მნიშვნელობა (მაგალითად, B3 ჩვენს შემთხვევაში) და ცხრილის პირველი სვეტის (F3: F19) უჯრედების ფორმატი განსხვავებულია (მაგალითად, რიცხვითი და ტექსტი. ). ეს შემთხვევა განსაკუთრებით დამახასიათებელია ტექსტური სახელების ნაცვლად რიცხვითი კოდების (ანგარიშის ნომრები, იდენტიფიკატორები, თარიღები და ა.შ.) გამოყენებისას. ამ შემთხვევაში, შეგიძლიათ გამოიყენოთ ფუნქციები Ч и ტექსტის მონაცემთა ფორმატების გადასაყვანად. ეს დაახლოებით ასე გამოიყურება:

    =VLOOKUP(TEXT(B3),ფასი,0)

    ამის შესახებ მეტი შეგიძლიათ წაიკითხოთ აქ.

  • ფუნქცია ვერ პოულობს საჭირო მნიშვნელობას, რადგან კოდი შეიცავს სივრცეებს ​​ან უხილავ არასაბეჭდად სიმბოლოებს (ხაზების წყვეტა და ა.შ.). ამ შემთხვევაში, შეგიძლიათ გამოიყენოთ ტექსტური ფუნქციები TRIM (TRIM) и PRINT(სუფთა) მათი ამოღება:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),ფასი,0)

    =VLOOKUP(TRIM(CLEAN(B3));ფასი;0)

შეცდომის შეტყობინების ჩასახშობად # N / A (# N/A) იმ შემთხვევებში, როდესაც ფუნქცია ვერ პოულობს ზუსტ შესატყვისს, შეგიძლიათ გამოიყენოთ ფუნქცია Iferror (IFERROR)მაგალითად, ეს კონსტრუქცია წყვეტს VLOOKUP-ის მიერ წარმოქმნილ ნებისმიერ შეცდომას და ცვლის მათ ნულებით:

= IFERROR (VLOOKUP (B3, ფასი, 2, 0), 0)

= IFERROR (VLOOKUP (B3; ფასი; 2; 0); 0)

PS

თუ თქვენ გჭირდებათ არა ერთი მნიშვნელობის, არამედ მთელი ნაკრების ერთდროულად ამოღება (თუ რამდენიმე განსხვავებულია), მაშინ მოგიწევთ შამანიზაცია მასივის ფორმულით. ან გამოიყენეთ ახალი XLOOKUP ფუნქცია Office 365-დან.

 

  • VLOOKUP ფუნქციის გაუმჯობესებული ვერსია (VLOOKUP 2).
  • ნაბიჯის (დიაპაზონის) ფასდაკლების სწრაფი გაანგარიშება VLOOKUP ფუნქციის გამოყენებით.
  • როგორ გავაკეთოთ „მარცხენა VLOOKUP“ INDEX და MATCH ფუნქციების გამოყენებით
  • როგორ გამოვიყენოთ VLOOKUP ფუნქცია სიიდან მონაცემებით ფორმების შესავსებად
  • როგორ ამოიღოთ ცხრილიდან არა პირველი, არამედ ყველა მნიშვნელობა ერთდროულად
  • VLOOKUP2 და VLOOKUP3 ფუნქციონირებს PLEX დანამატიდან

 

დატოვე პასუხი