შინაარსი
ყველა კლასიკური საძიებო და ტიპის ჩანაცვლების ფუნქცია VPR (VLOOKUP), GPR (HLOOKUP), უფრო გამოვლენილი (მატჩი) მათნაირებს კი ერთი მნიშვნელოვანი თვისება აქვთ – ეძებენ თავიდან ბოლომდე, ანუ წყაროს მონაცემებში მარცხნიდან მარჯვნივ ან ზემოდან ქვემოდან. როგორც კი პირველი შესატყვისი მოიძებნება, ძიება ჩერდება და მხოლოდ ჩვენთვის საჭირო ელემენტის პირველი შემთხვევა გვხვდება.
რა უნდა გავაკეთოთ, თუ გვჭირდება არა პირველი, არამედ უკანასკნელი შემთხვევა? მაგალითად, ბოლო ტრანზაქცია კლიენტისთვის, ბოლო გადახდა, უახლესი შეკვეთა და ა.შ.?
მეთოდი 1: ბოლო რიგის პოვნა მასივის ფორმულით
თუ თავდაპირველ ცხრილს არ აქვს სვეტი თარიღით ან მწკრივის სერიული ნომრით (შეკვეთა, გადახდა…), მაშინ ჩვენი ამოცანაა, ფაქტობრივად, ვიპოვოთ ბოლო მწკრივი, რომელიც აკმაყოფილებს მოცემულ პირობას. ეს შეიძლება გაკეთდეს შემდეგი მასივის ფორმულით:
აქ:
- ფუნქცია IF (თუ) სათითაოდ ამოწმებს სვეტის ყველა უჯრედს დამკვეთი და აჩვენებს ხაზის ნომერს, თუ ის შეიცავს ჩვენთვის საჭირო სახელს. ფურცელზე ხაზის ნომერი გვეძლევა ფუნქციით LINE (ROW), მაგრამ რადგან გვჭირდება მწკრივის ნომერი ცხრილში, დამატებით უნდა გამოვაკლოთ 1, რადგან ცხრილში გვაქვს სათაური.
- შემდეგ ფუნქცია MAX (MAX) ირჩევს მაქსიმალურ მნიშვნელობას მწკრივების ნომრების ჩამოყალიბებული ნაკრებიდან, ანუ კლიენტის უახლესი ხაზის რიცხვს.
- ფუნქცია ინდექსი (INDEX) აბრუნებს უჯრედის შიგთავსს ნაპოვნი ბოლო რიცხვით ნებისმიერი სხვა საჭირო ცხრილის სვეტიდან (შეკვეთის კოდი).
ეს ყველაფერი უნდა იყოს შეყვანილი როგორც მასივის ფორმულა, ანუ:
- Office 365-ში უახლესი განახლებებით დაინსტალირებული და დინამიური მასივების მხარდაჭერით, შეგიძლიათ უბრალოდ დააჭიროთ შეიყვანეთ.
- ყველა სხვა ვერსიაში, ფორმულის შეყვანის შემდეგ, თქვენ უნდა დააჭიროთ კლავიატურის მალსახმობს Ctrl+გადაიტანოს+შეიყვანეთ, რომელიც ავტომატურად დაამატებს მას ხვეულ ბრეკეტებს ფორმულების ზოლში.
მეთოდი 2: საპირისპირო ძიება ახალი LOOKUP ფუნქციით
მე უკვე დავწერე გრძელი სტატია ვიდეოთი ახალი ფუნქციის შესახებ VIEW (XLOOKUP), რომელიც გამოჩნდა Office-ის უახლეს ვერსიებში ძველი VLOOKUP-ის შესაცვლელად (VLOOKUP). BROWSE-ის დახმარებით ჩვენი ამოცანა საკმაოდ ელემენტარულად წყდება, რადგან. ამ ფუნქციისთვის (განსხვავებით VLOOKUP-ისგან), შეგიძლიათ ცალსახად დააყენოთ ძიების მიმართულება: ზემოდან ქვევით ან ქვემოდან ზევით - მისი ბოლო არგუმენტი (-1) პასუხისმგებელია ამაზე:
მეთოდი 3. მოიძიეთ სტრიქონი უახლესი თარიღით
თუ წყაროს მონაცემებში გვაქვს სვეტი სერიული ნომრით ან თარიღით, რომელიც მსგავს როლს ასრულებს, მაშინ დავალება შეცვლილია - ჩვენ უნდა ვიპოვოთ არა ბოლო (ყველაზე დაბალი) ხაზი შესატყვისით, არამედ ხაზი უახლესი ( მაქსიმალური) თარიღი.
მე უკვე დეტალურად განვიხილეთ, თუ როგორ უნდა გავაკეთოთ ეს კლასიკური ფუნქციების გამოყენებით, ახლა კი შევეცადოთ გამოვიყენოთ ახალი დინამიური მასივის ფუნქციების ძალა. მეტი სილამაზისა და მოხერხებულობისთვის, ჩვენ ასევე გარდაქმნით ორიგინალურ ცხრილს „ჭკვიან“ ცხრილად კლავიატურის მალსახმობის გამოყენებით. Ctrl+T ან ბრძანებებს მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი).
მათი დახმარებით, ეს "მკვლელი წყვილი" ძალიან მოხდენილად წყვეტს ჩვენს პრობლემას:
აქ:
- ჯერ ფუნქციონირება ფილტრი (ფილტრი) ირჩევს მხოლოდ იმ სტრიქონებს ჩვენი ცხრილიდან, სადაც არის სვეტი დამკვეთი - სახელი, რომელიც გვჭირდება.
- შემდეგ ფუნქცია GRADE (სორტირება) ახარისხებს არჩეულ სტრიქონებს თარიღის მიხედვით კლებადი მიმდევრობით, ზევით უახლესი გარიგებით.
- ფუნქცია ინდექსი (INDEX) ამოიღებს პირველ რიგს, ანუ აბრუნებს ჩვენთვის საჭირო ბოლო ვაჭრობას.
- და ბოლოს, გარე FILTER ფუნქცია ამოიღებს დამატებით პირველ და მე-1 სვეტებს შედეგებიდან (შეკვეთის კოდი и დამკვეთი) და ტოვებს მხოლოდ თარიღს და თანხას. ამისათვის გამოიყენება მუდმივთა მასივი. {0;1;0;1}, განსაზღვრავს რომელი სვეტების ჩვენება გვინდა (1) ან არ გვინდა (0).
მეთოდი 4: ბოლო შესატყვისის პოვნა Power Query-ში
კარგად, სისრულისთვის, მოდით შევხედოთ ჩვენი საპირისპირო ძიების პრობლემის გადაწყვეტას Power Query დანამატის გამოყენებით. მისი დახმარებით ყველაფერი ძალიან სწრაფად და ლამაზად წყდება.
1. მოდით გადავიყვანოთ ჩვენი ორიგინალური ცხრილი „ჭკვიანად“ კლავიატურის მალსახმობის გამოყენებით Ctrl+T ან ბრძანებებს მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი).
2. ჩატვირთეთ იგი Power Query-ში ღილაკით ცხრილიდან / დიაპაზონიდან tab თარიღი (მონაცემები - ცხრილიდან/დიაპაზონიდან).
3. ჩვენ ვახარისხებთ (სათაურში არსებული ფილტრის ჩამოსაშლელი სიის მეშვეობით) ჩვენს ცხრილს თარიღის კლებადობით, ისე, რომ უახლესი ტრანზაქციები იყოს ზემოთ.
4… ჩანართში ტრანსფორმაციის აირჩიეთ გუნდი Ით დაჯგუფება (ტრანსფორმა — ჯგუფის მიხედვით) და დააყენეთ დაჯგუფება მომხმარებელთა მიხედვით და, როგორც აგრეგაციის ფუნქცია, აირჩიეთ ვარიანტი ყველა ხაზი (ყველა მწკრივი). თქვენ შეგიძლიათ დაასახელოთ ახალი სვეტი რაც მოგწონთ - მაგალითად დაწვრილებით.
დაჯგუფების შემდეგ, ჩვენ მივიღებთ ჩვენი კლიენტების უნიკალური სახელების ჩამონათვალს და სვეტში დაწვრილებით – ცხრილები თითოეული მათგანის ყველა გარიგებით, სადაც პირველი ხაზი იქნება უახლესი ტრანზაქცია, რაც ჩვენ გვჭირდება:
5. დაამატეთ ახალი გამოთვლილი სვეტი ღილაკით მორგებული სვეტი tab სვეტის დამატება (სვეტის დამატება — მორგებული სვეტის დამატება)და შეიყვანეთ შემდეგი ფორმულა:
აქ დაწვრილებით – ეს არის სვეტი, საიდანაც ვიღებთ ცხრილებს მომხმარებლების მიხედვით და 0 {} არის იმ მწკრივის რიცხვი, რომლის ამოღებაც გვინდა (სტრიქონების ნუმერაცია Power Query-ში იწყება ნულიდან). ჩვენ ვიღებთ სვეტს ჩანაწერებით (ჩაწერა), სადაც თითოეული ჩანაწერი არის პირველი რიგი თითოეული ცხრილიდან:
რჩება ყველა ჩანაწერის შინაარსის გაფართოება ღილაკით ორმაგი ისრებით სვეტის სათაურში ბოლო გარიგება სასურველი სვეტების შერჩევა:
… და შემდეგ წაშალეთ სვეტი, რომელიც აღარ არის საჭირო დაწვრილებით მის სათაურზე მაუსის მარჯვენა ღილაკით - ამოიღეთ სვეტები (წაშლა სვეტები).
შედეგების ფურცელზე ატვირთვის შემდეგ მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…) ჩვენ მივიღებთ ისეთ მშვენიერ ცხრილს ბოლო ტრანზაქციების სიით, როგორც გვინდოდა:
როდესაც თქვენ შეცვლით წყაროს მონაცემებს, არ უნდა დაგვავიწყდეს შედეგების განახლება მათზე მაუსის მარჯვენა ღილაკით - ბრძანებაზე განახლება და შენახვა (განახლება) ან კლავიატურის მალსახმობი Ctrl+Alt+F5.
- LOOKUP ფუნქცია არის VLOOKUP-ის შთამომავალი
- როგორ გამოვიყენოთ ახალი დინამიური მასივის ფუნქციები SORT, FILTER და UNIC
- მწკრივის ან სვეტის ბოლო არა ცარიელი უჯრედის პოვნა LOOKUP ფუნქციით