უახლოესი რიცხვის პოვნა

პრაქტიკაში, ძალიან ხშირად არის შემთხვევები, როდესაც მე და თქვენ უნდა ვიპოვოთ უახლოესი მნიშვნელობა ნაკრებში (ცხრილში) მოცემულ რიცხვთან მიმართებაში. ეს შეიძლება იყოს, მაგალითად:

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

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

არსებობს რამდენიმე გზა - აშკარა და არც ისე აშკარა - ასეთი პრობლემის გადასაჭრელად. მოდით შევხედოთ მათ თანმიმდევრობით.

დასაწყისისთვის, წარმოვიდგინოთ მიმწოდებელი, რომელიც იძლევა ფასდაკლებებს საბითუმო ვაჭრობაში, ხოლო ფასდაკლების პროცენტი დამოკიდებულია შეძენილი საქონლის რაოდენობაზე. მაგალითად, 5 ცალზე მეტის შეძენისას მოქმედებს 2%-იანი ფასდაკლება, ხოლო 20 ცალიდან ყიდვისას - უკვე 6% და ა.შ.

როგორ სწრაფად და ლამაზად გამოვთვალოთ ფასდაკლების პროცენტი შეძენილი საქონლის რაოდენობის შეყვანისას?

მეთოდი 1: ჩადგმული IF-ები

მეთოდი სერიიდან "რა უნდა იფიქრო - უნდა გადახტე!". ჩადგმული ფუნქციების გამოყენება IF (თუ) თანმიმდევრულად შეამოწმოს თუ უჯრედის მნიშვნელობა ხვდება თითოეულ ინტერვალში და აჩვენოს ფასდაკლება შესაბამისი დიაპაზონისთვის. მაგრამ ფორმულა ამ შემთხვევაში შეიძლება აღმოჩნდეს ძალიან რთული: 

ვფიქრობ, აშკარაა, რომ ასეთი „მონსტრის თოჯინის“ გამართვა ან გარკვეული დროის შემდეგ მასში რამდენიმე ახალი პირობის დამატების მცდელობა სახალისოა.

გარდა ამისა, Microsoft Excel-ს აქვს ჩადგმული ლიმიტი IF ფუნქციისთვის - 7-ჯერ ძველ ვერსიებში და 64-ჯერ ახალ ვერსიებში. რა მოხდება, თუ მეტი გჭირდებათ?

მეთოდი 2. VLOOKUP ინტერვალის ხედით

ეს მეთოდი ბევრად უფრო კომპაქტურია. ფასდაკლების პროცენტის გამოსათვლელად გამოიყენეთ ლეგენდარული ფუნქცია VPR (VLOOKUP) სავარაუდო ძიების რეჟიმში:

სადაც

• B4 – საქონლის რაოდენობის ღირებულება პირველ ტრანზაქციაში, რომლისთვისაც ჩვენ ვეძებთ ფასდაკლებას
  
• $G$4:$H$8 – ფასდაკლების ცხრილის ბმული – „სათაურის“ გარეშე და $ ნიშნით დაფიქსირებული მისამართებით.
  
• 2 — ფასდაკლების ცხრილის სვეტის რიგითი ნომერი, საიდანაც გვინდა მივიღოთ ფასდაკლების ღირებულება
• TRUE - აქ არის დაკრძალული "ძაღლი". თუ როგორც ბოლო ფუნქციის არგუმენტი VPR მიუთითეთ იტყუება (ცრუ) ან 0, შემდეგ ფუნქცია მოძებნის მკაცრი მატჩი რაოდენობის სვეტში (და ჩვენს შემთხვევაში ის მისცემს #N/A შეცდომას, ვინაიდან ფასდაკლების ცხრილში არ არის მნიშვნელობა 49). მაგრამ თუ სამაგიეროდ იტყუება დაწერა TRUE (ნამდვილი) ან 1, მაშინ ფუნქცია მოძებნის არა ზუსტს, არამედ უახლოესი ყველაზე პატარა ღირებულება და მოგვცემს ჩვენთვის საჭირო ფასდაკლების პროცენტს.

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

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

მეთოდი 3. უახლოესი უდიდესის პოვნა INDEX და MATCH ფუნქციების გამოყენებით

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

VLOOKUP ფუნქცია აქ არ დაგვეხმარება, ამიტომ მოგიწევთ მისი ანალოგის გამოყენება - INDEX ფუნქციების მთელი რიგი. (INDEX) და უფრო გამოვლენილი (მატჩი):

აქ MATCH ფუნქცია ბოლო არგუმენტით -1 მუშაობს უახლოესი უდიდესი მნიშვნელობის პოვნის რეჟიმში, ხოლო INDEX ფუნქცია შემდეგ ამოიღებს ჩვენთვის საჭირო მოდელის სახელს მიმდებარე სვეტიდან.

მეთოდი 4. ახალი ფუნქცია VIEW (XLOOKUP)

თუ თქვენ გაქვთ Office 365-ის ვერსია ყველა განახლებით დაინსტალირებული, მაშინ VLOOKUP-ის ნაცვლად (VLOOKUP) შეგიძლიათ გამოიყენოთ მისი ანალოგი - VIEW ფუნქცია (XLOOKUP), რომელიც უკვე დეტალურად გავაანალიზე:

აქ:

• B4 – საწყისი ღირებულება იმ პროდუქტის რაოდენობისა, რომელზეც ჩვენ ვეძებთ ფასდაკლებას
• $G$4:$G$8 – დიაპაზონი, სადაც ჩვენ ვეძებთ მატჩებს
  
• $ H $ 4: $ H $ 8 – შედეგების დიაპაზონი, საიდანაც გსურთ ფასდაკლების დაბრუნება
• მეოთხე არგუმენტი (-1) მოიცავს უახლოესი უმცირესი რიცხვის ძიებას, რომელიც გვინდა ზუსტი შესატყვისის ნაცვლად.
  

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

მაგრამ, სამწუხაროდ, ყველას არ აქვს ეს ფუნქცია - მხოლოდ Office 365-ის ბედნიერი მფლობელები.

მეთოდი 5. Power Query

თუ ჯერ არ იცნობთ მძლავრ და სრულიად უფასო Power Query დანამატს Excel-ისთვის, მაშინ აქ ხართ. თუ უკვე იცნობთ, მაშინ მოდით ვცადოთ მისი გამოყენება ჩვენი პრობლემის მოსაგვარებლად.

მოდით, ჯერ გავაკეთოთ მოსამზადებელი სამუშაოები:

1. მოდით გადავიყვანოთ ჩვენი წყაროს ცხრილები დინამიურად (ჭკვიანად) კლავიატურის მალსახმობის გამოყენებით Ctrl+T ან გუნდი მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი).
2. სიცხადისთვის, მოდით მივცეთ სახელები. გაყიდვების и ფასდაკლება tab კონსტრუქტორი (დიზაინი).
3. ჩატვირთეთ თითოეული ცხრილი თავის მხრივ Power Query-ში ღილაკის გამოყენებით ცხრილიდან / დიაპაზონიდან tab თარიღი (მონაცემები - ცხრილიდან/დიაპაზონიდან). Excel-ის ბოლო ვერსიებში ამ ღილაკს დაარქვეს სახელი ფოთლებით (ფურცლიდან).
   
4. თუ ცხრილებს აქვთ სხვადასხვა სვეტის სახელები რაოდენობით, როგორც ჩვენს მაგალითში ("საქონლის რაოდენობა" და "რაოდენობა ..."), მაშინ მათ უნდა გადაერქვათ სახელი Power Query-ში და დაარქვეს იგივე.
5. ამის შემდეგ შეგიძლიათ დაბრუნდეთ Excel-ში Power Query რედაქტორის ფანჯარაში ბრძანების არჩევით მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…) და შემდეგ ვარიანტი უბრალოდ შექმენით კავშირი (მხოლოდ შექმენით კავშირი).

   
   

6. შემდეგ იწყება ყველაზე საინტერესო. თუ თქვენ გაქვთ Power Query-ის გამოცდილება, მაშინ მე ვვარაუდობ, რომ შემდგომი აზროვნების ხაზი უნდა იყოს ამ ორი ცხრილის შერწყმის მიმართულებით (შეერთება) a la VLOOKUP-თან, როგორც ეს იყო წინა მეთოდში. ფაქტობრივად, დაგვჭირდება შერწყმა დამატების რეჟიმში, რაც ერთი შეხედვით სულაც არ ჩანს. აირჩიეთ Excel ჩანართში მონაცემები – მიიღეთ მონაცემები – შეაერთეთ მოთხოვნები – დაამატეთ (მონაცემები — მიიღეთ მონაცემები — შეაერთეთ მოთხოვნები — დამატება) შემდეგ კი ჩვენი მაგიდები გაყიდვების и ფასდაკლება ფანჯარაში, რომელიც გამოჩნდება:

   

7. დაწკაპვით OK ჩვენი მაგიდები ერთ მთლიანობაში იქნება დაწებებული - ერთმანეთის ქვეშ. გთხოვთ გაითვალისწინოთ, რომ ამ ცხრილებში საქონლის რაოდენობის სვეტები ერთმანეთის ქვეშ იყო, რადგან. მათ აქვთ იგივე სახელი:

   

8. თუ გაყიდვების ცხრილში მწკრივების თავდაპირველი თანმიმდევრობა თქვენთვის მნიშვნელოვანია, მაშინ იმისათვის, რომ ყველა შემდგომი ტრანსფორმაციის შემდეგ შეძლოთ მისი აღდგენა, დაამატეთ დანომრილი სვეტი ჩვენს ცხრილში ბრძანების გამოყენებით. სვეტის დამატება – ინდექსის სვეტი (სვეტის დამატება - ინდექსის სვეტი). თუ ხაზების თანმიმდევრობას არ აქვს მნიშვნელობა, მაშინ შეგიძლიათ გამოტოვოთ ეს ნაბიჯი.
9. ახლა, ჩამოსაშლელი სიის გამოყენებით ცხრილის სათაურში, დაალაგეთ იგი სვეტების მიხედვით რაოდენობა აღმავალი:

   

10. და მთავარი ხრიკი: დააწკაპუნეთ მაუსის მარჯვენა ღილაკით სვეტის სათაურზე ფასდაკლება აირჩიეთ გუნდი შევსება – ქვევით (შევსება - ქვემოთ). ცარიელი უჯრედებით null ავტომატურად ივსება წინა ფასდაკლების მნიშვნელობებით:

    

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

    
    

• VLOOKUP ფუნქციის გამოყენება მონაცემების საძიებლად და მოსაძიებლად
• VLOOKUP-ის (VLOOKUP) გამოყენება რეგისტრირებულია
• XNUMXD VLOOKUP (VLOOKUP)