ორი ცხრილის შედარება

ჩვენ გვაქვს ორი ცხრილი (მაგალითად, ფასების სიის ძველი და ახალი ვერსიები), რომლებიც უნდა შევადაროთ და სწრაფად ვიპოვოთ განსხვავებები:

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

Excel-ში ნებისმიერი ამოცანისთვის, თითქმის ყოველთვის არის ერთზე მეტი გამოსავალი (ჩვეულებრივ, 4-5). ჩვენი პრობლემის გადასაჭრელად, მრავალი განსხვავებული მიდგომის გამოყენება შეიძლება:

• ფუნქცია VPR (VLOOKUP) — მოძებნეთ პროდუქტის სახელები ახალი ფასების სიიდან ძველში და აჩვენეთ ძველი ფასი ახლის გვერდით და შემდეგ დაიჭირეთ განსხვავებები
• გააერთიანეთ ორი სია ერთში და შემდეგ შექმენით კრებსითი ცხრილი მასზე დაყრდნობით, სადაც განსხვავებები აშკარად ჩანს
• გამოიყენეთ Power Query Add-in Excel-ისთვის

ავიღოთ ყველა თანმიმდევრობით.

მეთოდი 1. ცხრილების შედარება VLOOKUP ფუნქციასთან

თუ თქვენ საერთოდ არ იცნობთ ამ მშვენიერ ფუნქციას, მაშინ ჯერ ნახეთ აქ და წაიკითხეთ ან უყურეთ ვიდეო გაკვეთილს მასზე - გადაარჩინე სიცოცხლე რამდენიმე წელი.

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

ის პროდუქტები, რომლებზეც #N/A შეცდომა აღმოჩნდა, ძველ სიაში არ არის, ანუ დამატებულია. ფასების ცვლილებები ასევე აშკარად ჩანს.

დადებითი ეს მეთოდი: მარტივი და გასაგები, "ჟანრის კლასიკა", როგორც ამბობენ. მუშაობს Excel-ის ნებისმიერ ვერსიაში.

Cons ასევე არის იქ. ახალ ფასთა სიაში დამატებული პროდუქტების მოსაძიებლად მოგიწევთ იგივე პროცედურის საპირისპირო მიმართულებით შესრულება, ანუ VLOOKUP-ის დახმარებით ახალი ფასები ძველ ფასზე აწიოთ. თუ ხვალ ცხრილების ზომები შეიცვლება, მაშინ ფორმულების კორექტირება მოუწევს. ისე, და მართლაც დიდ მაგიდებზე (> 100 ათასი მწკრივი), მთელი ეს ბედნიერება წესიერად შენელდება.

მეთოდი 2: ცხრილების შედარება პივოტის გამოყენებით

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

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

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

ასეთ ცხრილში გრანდიოზულ ჯამებს აზრი არ აქვს და მათი გამორთვა შესაძლებელია ჩანართზე კონსტრუქტორი – გრანდიოზული ჯამები – გამორთვა რიგებისა და სვეტებისთვის (დიზაინი - დიდი ტოტალი).

თუ ფასები იცვლება (მაგრამ არა საქონლის რაოდენობა!), მაშინ საკმარისია უბრალოდ განაახლოთ შექმნილი რეზიუმე მასზე მაუსის მარჯვენა ღილაკით – განახლება.

დადებითი: ეს მიდგომა სიდიდის ბრძანებით უფრო სწრაფია დიდი ცხრილებით, ვიდრე VLOOKUP. 

Cons: საჭიროა ხელით დააკოპიროთ მონაცემები ერთმანეთის ქვეშ და დაამატოთ სვეტი ფასების სიის სახელწოდებით. თუ ცხრილების ზომები იცვლება, მაშინ ყველაფერი თავიდან უნდა გააკეთოთ.

მეთოდი 3: ცხრილების შედარება Power Query-თან

Power Query არის Microsoft Excel-ის უფასო დანამატი, რომელიც საშუალებას გაძლევთ ჩატვირთოთ მონაცემები Excel-ში თითქმის ნებისმიერი წყაროდან და შემდეგ გადააქციოთ ეს მონაცემები ნებისმიერი სასურველი გზით. Excel 2016-ში ეს დანამატი უკვე ჩაშენებულია ნაგულისხმევად ჩანართზე თარიღი (მონაცემები), ხოლო Excel 2010-2013-ისთვის თქვენ უნდა ჩამოტვირთოთ იგი ცალკე Microsoft-ის ვებსაიტიდან და დააინსტალიროთ – მიიღეთ ახალი ჩანართი დენის მოთხოვნა.

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

ჩატვირთეთ ძველი ფასი Power Query-ში ღილაკის გამოყენებით ცხრილიდან / დიაპაზონიდან (ცხრილიდან/დიაპაზონიდან) ჩანართიდან თარიღი (თარიღი) ან ჩანართიდან დენის მოთხოვნა (დამოკიდებულია Excel-ის ვერსიაზე). ჩატვირთვის შემდეგ ჩვენ დავბრუნდებით Excel-ში Power Query-დან ბრძანებით დახურვა და ჩატვირთვა – დახურვა და ჩატვირთვა… (დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…):

… და ფანჯარაში, რომელიც გამოჩნდება, აირჩიეთ უბრალოდ შექმენით კავშირი (მხოლოდ კავშირი).

იგივე გაიმეორეთ ახალი ფასების სიაში. 

ახლა შევქმნათ მესამე მოთხოვნა, რომელიც გააერთიანებს და შეადარებს წინა ორიდან მონაცემებს. ამისათვის აირჩიეთ Excel-ში ჩანართზე მონაცემები – მიიღეთ მონაცემები – Combine Requests – Combine (მონაცემები — მიიღეთ მონაცემები — შერწყმის მოთხოვნები — შერწყმა) ან დააჭირეთ ღილაკს შეუთავსეთ (შერწყმა) tab დენის მოთხოვნა.

შეერთების ფანჯარაში აირჩიეთ ჩვენი ცხრილები ჩამოსაშლელ სიებში, აირჩიეთ სვეტები მათში საქონლის სახელებით და ბოლოში დააყენეთ შეერთების მეთოდი – სრული გარე (სრული გარე):

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

შედეგად, ჩვენ ვიღებთ მონაცემთა გაერთიანებას ორივე ცხრილიდან:

უკეთესია, რა თქმა უნდა, გადარქმევა სვეტების სახელები სათაურში ორჯერ დაწკაპუნებით უფრო გასაგებზე:

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

რჩება დაწკაპუნება OK და ატვირთეთ მიღებული ანგარიში Excel-ში იმავე ღილაკის გამოყენებით დახურეთ და გადმოწერეთ (დახურვა და ჩატვირთვა) tab მთავარი (მთავარი):

სილამაზის.

უფრო მეტიც, თუ მომავალში რაიმე ცვლილება მოხდება ფასების სიებში (ხაზები დაემატება ან წაიშლება, ფასები იცვლება და ა.შ.), მაშინ საკმარისი იქნება ჩვენი მოთხოვნების განახლება კლავიატურის მალსახმობით. Ctrl+Alt+F5 ან ღილაკით განაახლეთ ყველაფერი (ყველას განახლება) tab თარიღი (თარიღი).

დადებითი: ალბათ ყველაზე ლამაზი და მოსახერხებელი გზა. ჭკვიანურად მუშაობს დიდ მაგიდებთან. არ საჭიროებს ხელით რედაქტირებას ცხრილების ზომის შეცვლისას.

Cons: საჭიროებს Power Query დანამატს (Excel 2010-2013) ან Excel 2016-ის ინსტალაციას. წყაროს მონაცემებში სვეტების სახელები არ უნდა შეიცვალოს, წინააღმდეგ შემთხვევაში მივიღებთ შეცდომას "სვეტი ასეთი და ასეთი ვერ მოიძებნა!" მოთხოვნის განახლების მცდელობისას.

• როგორ შევაგროვოთ მონაცემები Excel-ის ყველა ფაილიდან მოცემულ საქაღალდეში Power Query-ის გამოყენებით
• როგორ მოვძებნოთ შესატყვისები ორ სიას შორის Excel-ში
• ორი სიის შერწყმა დუბლიკატების გარეშე