შინაარსი
ჩვენ ცოტა ხნის წინ მივუძღვენით სტატიას Excel-ის ერთ-ერთ ყველაზე სასარგებლო ფუნქციას ე.წ VPR და აჩვენა, თუ როგორ შეიძლება მისი გამოყენება მონაცემთა ბაზიდან საჭირო ინფორმაციის სამუშაო ფურცელში ამოსაღებად. ჩვენ ასევე აღვნიშნეთ, რომ ფუნქციის გამოყენების ორი შემთხვევაა VPR და მხოლოდ ერთი მათგანი ეხება მონაცემთა ბაზის შეკითხვებს. ამ სტატიაში თქვენ შეისწავლით ფუნქციის გამოყენების სხვა ნაკლებად ცნობილ გზას VPR Excel- ში.
თუ ეს ჯერ არ გაგიკეთებიათ, აუცილებლად წაიკითხეთ ბოლო სტატია ფუნქციის შესახებ VPR, რადგან ქვემოთ მოყვანილი ყველა ინფორმაცია ვარაუდობს, რომ თქვენ უკვე იცნობთ პირველ სტატიაში აღწერილი პრინციპებს.
მონაცემთა ბაზებთან მუშაობისას, ფუნქციები VPR გადაეცემა უნიკალური იდენტიფიკატორი, რომელიც გამოიყენება იმ ინფორმაციის იდენტიფიცირებისთვის, რომელიც გვინდა ვიპოვოთ (მაგალითად, პროდუქტის კოდი ან მომხმარებლის საიდენტიფიკაციო ნომერი). ეს უნიკალური კოდი უნდა იყოს მონაცემთა ბაზაში, წინააღმდეგ შემთხვევაში VPR შეატყობინებს შეცდომას. ამ სტატიაში განვიხილავთ ფუნქციის გამოყენების ამ ხერხს VPRროდესაც ID საერთოდ არ არსებობს მონაცემთა ბაზაში. თითქოს ფუნქცია VPR გადართულია სავარაუდო რეჟიმზე და ირჩევს რა მონაცემებს მოგვაწოდოს, როცა რაიმეს პოვნა გვინდა. გარკვეულ პირობებში, ეს არის ზუსტად ის, რაც საჭიროა.
მაგალითი ცხოვრებიდან. დავალება დავსახეთ
მოდით ილუსტრაციით ეს სტატია რეალური მაგალითით - საკომისიოს გამოთვლა გაყიდვების მეტრიკის ფართო სპექტრზე დაყრდნობით. ჩვენ დავიწყებთ ძალიან მარტივი ვარიანტით, შემდეგ კი თანდათან გავართულებთ მას, სანამ პრობლემის ერთადერთი რაციონალური გადაწყვეტა არ იქნება ფუნქციის გამოყენება. VPR. ჩვენი ფიქტიური ამოცანის საწყისი სცენარი ასეთია: თუ გამყიდველი წელიწადში $30000-ზე მეტს გამოიმუშავებს, მაშინ მისი საკომისიო არის 30%. წინააღმდეგ შემთხვევაში, საკომისიო არის მხოლოდ 20%. მოდით დავდოთ იგი ცხრილის სახით:
გამყიდველი შეაქვს მათი გაყიდვების მონაცემებს B1 უჯრედში, ხოლო ფორმულა B2 უჯრედში განსაზღვრავს სწორ საკომისიოს განაკვეთს, რომელიც გამყიდველს შეუძლია მოელოდეს. თავის მხრივ, მიღებული მაჩვენებელი გამოიყენება B3 უჯრედში, რათა გამოვთვალოთ მთლიანი საკომისიო, რომელიც გამყიდველმა უნდა მიიღოს (უბრალოდ გავამრავლოთ უჯრედები B1 და B2).
ცხრილის ყველაზე საინტერესო ნაწილი შეიცავს B2 უჯრედს - ეს არის საკომისიოს განაკვეთის განსაზღვრის ფორმულა. ეს ფორმულა შეიცავს Excel ფუნქციას ე.წ IF (თუ). იმ მკითხველს, ვინც არ იცნობს ამ ფუნქციას, მე ავუხსნი როგორ მუშაობს:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
მდგომარეობა არის ფუნქციის არგუმენტი, რომელიც იღებს რომელიმეს მნიშვნელობას ჭეშმარიტი კოდი (მართალი), ან FALSE (ცრუ). ზემოთ მოცემულ მაგალითში გამოთქმა B1
მართალია, რომ B1 ნაკლებია B5-ზე?
ან შეგიძლია სხვანაირად თქვა:
მართალია, რომ წლის გაყიდვების მთლიანი რაოდენობა ზღვრულ მნიშვნელობაზე ნაკლებია?
თუ ამ კითხვას ვუპასუხებთ YES (TRUE), შემდეგ ფუნქცია ბრუნდება მნიშვნელობა თუ მართალია (მნიშვნელობა, თუ TRUE). ჩვენს შემთხვევაში, ეს იქნება B6 უჯრედის მნიშვნელობა, ანუ საკომისიოს განაკვეთი, როდესაც მთლიანი გაყიდვები ზღურბლზე დაბალია. თუ კითხვას ვუპასუხებთ არა (FALSE) შემდეგ ბრუნდება მნიშვნელობა, თუ არასწორია (მნიშვნელობა, თუ FALSE). ჩვენს შემთხვევაში, ეს არის B7 უჯრედის მნიშვნელობა, ანუ საკომისიოს განაკვეთი, როდესაც მთლიანი გაყიდვები ზღურბლზე მეტია.
როგორც ხედავთ, თუ ავიღებთ მთლიან გაყიდვებს $20000, მივიღებთ 2% საკომისიოს განაკვეთს B20 უჯრედში. თუ ჩვენ შევიყვანთ ღირებულებას $40000, მაშინ საკომისიოს განაკვეთი შეიცვლება 30% -ით:
ასე მუშაობს ჩვენი მაგიდა.
ჩვენ ვართულებთ დავალებას
ცოტა გავართულოთ საქმეები. მოდით დავაყენოთ სხვა ბარიერი: თუ გამყიდველი გამოიმუშავებს $40000-ზე მეტს, მაშინ საკომისიოს განაკვეთი იზრდება 40%-მდე:
როგორც ჩანს, ყველაფერი მარტივი და გასაგებია, მაგრამ ჩვენი ფორმულა B2 უჯრედში შესამჩნევად უფრო რთული ხდება. თუ კარგად დააკვირდებით ფორმულას, დაინახავთ, რომ ფუნქციის მესამე არგუმენტია IF (IF) გადაიქცა სხვა სრულფასოვან ფუნქციად IF (თუ). ამ კონსტრუქციას ეწოდება ფუნქციების ერთმანეთში ბუდობა. Excel სიამოვნებით უშვებს ამ კონსტრუქციებს და ისინიც კი მუშაობენ, მაგრამ მათი წაკითხვა და გაგება გაცილებით რთულია.
ჩვენ არ ჩავუღრმავდებით ტექნიკურ დეტალებს - რატომ და როგორ მუშაობს და არ შევეხებით წყობილი ფუნქციების ჩაწერის ნიუანსებს. ყოველივე ამის შემდეგ, ეს არის სტატია, რომელიც ეძღვნება ფუნქციას VPR, არ არის Excel-ის სრული სახელმძღვანელო.
რაც არ უნდა იყოს, ფორმულა უფრო რთულდება! რა მოხდება, თუ შემოგთავაზებთ საკომისიოს 50%-იან სხვა ვარიანტს იმ გამყიდველებისთვის, რომლებიც 50000$-ზე მეტს აკეთებენ გაყიდვებში. და თუ ვინმემ გაყიდა 60000$-ზე მეტი, გადაიხდის 60% საკომისიოს?
ახლა ფორმულა B2 უჯრედში, თუნდაც შეცდომების გარეშე იყოს დაწერილი, სრულიად გაუგებარი გახდა. მე ვფიქრობ, რომ ცოტანი არიან, ვისაც სურს გამოიყენოს ფორმულები 4 დონის ბუდეებით თავის პროექტებში. უფრო მარტივი გზა უნდა არსებობდეს?!
და არსებობს ასეთი გზა! ფუნქცია დაგვეხმარება VPR.
პრობლემის გადასაჭრელად ვიყენებთ VLOOKUP ფუნქციას
მოდით ცოტათი შევცვალოთ ჩვენი მაგიდის დიზაინი. ჩვენ შევინარჩუნებთ ყველა ერთსა და იმავე ველს და მონაცემს, მაგრამ ვაწყობთ მათ ახალ, უფრო კომპაქტურად:
დაუთმეთ ერთი წუთი და დარწმუნდით, რომ ახალი მაგიდა შეფასების ცხრილი შეიცავს იგივე მონაცემებს, როგორც წინა ზღვრული ცხრილი.
მთავარი იდეა არის ფუნქციის გამოყენება VPR ცხრილის მიხედვით სასურველი სატარიფო განაკვეთის დასადგენად შეფასების ცხრილი გაყიდვების მოცულობიდან გამომდინარე. გთხოვთ გაითვალისწინოთ, რომ გამყიდველს შეუძლია გაყიდოს საქონელი იმ ოდენობით, რომელიც არ არის ცხრილის ხუთი ზღვრიდან ერთ-ერთის ტოლი. მაგალითად, მას შეეძლო გაეყიდა 34988 დოლარად, მაგრამ ასეთი თანხა არ არსებობს. ვნახოთ როგორ მუშაობს VPR შეუძლია გაუმკლავდეს ასეთ სიტუაციას.
VLOOKUP ფუნქციის ჩასმა
აირჩიეთ უჯრედი B2 (სადაც გვინდა ჩვენი ფორმულის ჩასმა) და იპოვეთ VLOOKUP (VLOOKUP) Excel-ის ფუნქციების ბიბლიოთეკაში: ფორმულები (ფორმულები) > ფუნქციების ბიბლიოთეკა (ფუნქციის ბიბლიოთეკა) > ძიება და ცნობარი (ცნობები და მასივები).
გამოჩნდება დიალოგური ფანჯარა ფუნქციური არგუმენტები (ფუნქციის არგუმენტები). ჩვენ ვავსებთ არგუმენტების მნიშვნელობებს სათითაოდ, დაწყებული Lookup_value (Lookup_value). ამ მაგალითში, ეს არის B1 უჯრედის გაყიდვების მთლიანი რაოდენობა. მოათავსეთ კურსორი ველში Lookup_value (Lookup_value) და აირჩიეთ უჯრედი B1.
შემდეგი, თქვენ უნდა მიუთითოთ ფუნქციები VPRსად უნდა ვეძებოთ მონაცემები. ჩვენს მაგალითში ეს არის ცხრილი შეფასების ცხრილი. მოათავსეთ კურსორი ველში ცხრილი_მასივი (ცხრილი) და აირჩიეთ მთელი ცხრილი შეფასების ცხრილიგარდა სათაურებისა.
შემდეგი, ჩვენ უნდა განვსაზღვროთ რომელი სვეტიდან ამოვიღოთ მონაცემები ჩვენი ფორმულის გამოყენებით. ჩვენ გვაინტერესებს საკომისიოს განაკვეთი, რომელიც ცხრილის მეორე სვეტშია. ამიტომ, არგუმენტისთვის Col_index_num (სვეტის_ნომერი) შეიყვანეთ მნიშვნელობა 2.
და ბოლოს, შემოგთავაზებთ ბოლო არგუმენტს - დიაპაზონის_ძიება (interval_lookup).
მნიშვნელოვანია: სწორედ ამ არგუმენტის გამოყენება ქმნის განსხვავებას ფუნქციის გამოყენების ორ გზას შორის VPR. მონაცემთა ბაზებთან მუშაობისას არგუმენტი დიაპაზონის_ძიება (range_lookup) ყოველთვის უნდა ჰქონდეს მნიშვნელობა FALSE (FALSE) ზუსტი შესატყვისის მოსაძებნად. ჩვენი ფუნქციის გამოყენებისას VPR, ეს ველი ცარიელი უნდა დავტოვოთ, ან შევიტანოთ მნიშვნელობა ჭეშმარიტი კოდი (მართალი). ძალიან მნიშვნელოვანია ამ ვარიანტის სწორად არჩევა.
უფრო გასაგებად გაგაცნობთ ჭეშმარიტი კოდი (მართალი) მინდორში დიაპაზონის_ძიება (interval_lookup). თუმცა, თუ ველს ცარიელი დატოვებთ, ეს არ იქნება შეცდომა, რადგან ჭეშმარიტი კოდი არის მისი ნაგულისხმევი მნიშვნელობა:
ჩვენ შევავსეთ ყველა პარამეტრი. ახლა ჩვენ ვაჭერთ OK, და Excel ქმნის ფორმულას ჩვენთვის ფუნქციით VPR.
თუ ჩვენ ექსპერიმენტებს ჩავატარებთ რამდენიმე განსხვავებულ მნიშვნელობას გაყიდვების მთლიანი თანხისთვის, მაშინ დავრწმუნდებით, რომ ფორმულა მუშაობს სწორად.
დასკვნა
როცა ფუნქცია VPR მუშაობს მონაცემთა ბაზებით, არგუმენტი დიაპაზონის_ძიება (range_lookup) უნდა დაეთანხმოს FALSE (ცრუ). და მნიშვნელობა შეყვანილია როგორც Lookup_value (Lookup_value) უნდა არსებობდეს მონაცემთა ბაზაში. სხვა სიტყვებით რომ ვთქვათ, ის ეძებს ზუსტ შესატყვისს.
მაგალითში, რომელიც ჩვენ განვიხილეთ ამ სტატიაში, არ არის საჭირო ზუსტი შესატყვისის მიღება. ეს ის შემთხვევაა, როცა ფუნქცია VPR უნდა გადავიდეს სავარაუდო რეჟიმზე სასურველი შედეგის დასაბრუნებლად.
მაგალითად: ჩვენ გვინდა განვსაზღვროთ რა განაკვეთი გამოვიყენოთ საკომისიოს გამოთვლაში გამყიდველისთვის, რომლის გაყიდვების მოცულობა 34988 დოლარია. ფუნქცია VPR გვიბრუნებს მნიშვნელობას 30%, რაც აბსოლუტურად სწორია. მაგრამ რატომ აირჩია ფორმულამ მწკრივი, რომელიც შეიცავს ზუსტად 30% და არა 20% ან 40%? რა იგულისხმება სავარაუდო ძიებაში? მოდით ვიყოთ ნათელი.
როცა კამათი დიაპაზონის_ძიება (interval_lookup) აქვს მნიშვნელობა ჭეშმარიტი კოდი (TRUE) ან გამოტოვებული, ფუნქცია VPR იმეორებს პირველ სვეტს და ირჩევს ყველაზე დიდ მნიშვნელობას, რომელიც არ აღემატება საძიებო მნიშვნელობას.
მნიშვნელოვანი წერტილი: იმისათვის, რომ ამ სქემმა იმუშაოს, ცხრილის პირველი სვეტი უნდა იყოს დალაგებული ზრდის მიხედვით.