კრებსითი ცხრილი ტექსტით მნიშვნელობებში

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

შევეცადოთ ამ შეზღუდვის თავიდან აცილება და მსგავს სიტუაციაში გამოვიყენოთ „წყვილი ყავარჯნები“.

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

მოხერხებულობისთვის, ბრძანების გამოყენებით წინასწარ გავხადოთ ცხრილი საწყისი მონაცემებით „ჭკვიანი“. მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი) და მიეცით მას სახელი მიწოდება tab კონსტრუქტორი (დიზაინი). მომავალში ეს გაამარტივებს ცხოვრებას, რადგან. ცხრილისა და მისი სვეტების სახელის გამოყენება პირდაპირ ფორმულებში იქნება შესაძლებელი.

მეთოდი 1. ყველაზე მარტივი - გამოიყენეთ Power Query

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

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

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

მეთოდი 2. დამხმარე რეზიუმე

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

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

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

ვინაიდან ჩვენ არ გვაქვს რამდენიმე კონტეინერი ერთსა და იმავე ქალაქში ერთ თვეში, ჩვენი შეჯამება, ფაქტობრივად, მოგვაწვდის არა რაოდენობას, არამედ იმ კონტეინერების ნომრებს, რომლებიც გვჭირდება.

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

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

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

ფუნქცია IF (თუ), ამ შემთხვევაში, ამოწმებს, რომ შეჯამების შემდეგი უჯრედი ცარიელი არ არის. თუ ცარიელია, მაშინ გამოიტანეთ ცარიელი ტექსტის სტრიქონი “”, ანუ დატოვეთ უჯრედი ცარიელი. თუ ცარიელი არ არის, მაშინ ამოიღეთ სვეტიდან კონტეინერი წყაროს ცხრილი მიწოდება უჯრედის შინაარსი მწკრივის ნომრის მიხედვით ფუნქციის გამოყენებით ინდექსი (INDEX).

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

მარაგი[[კონტეინერი]:[კონტეინერი]]

… საჭიროა მხოლოდ სვეტის მითითებისთვის კონტეინერი იყო აბსოლუტური (როგორც მინიშნება $ ნიშნებით ჩვეულებრივი „არაჭკვიანი“ ცხრილებისთვის) და არ გადაიჩეხა მეზობელ სვეტებში ჩვენი ფორმულის მარჯვნივ კოპირებისას.

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

მეთოდი 3. ფორმულები

ეს მეთოდი არ საჭიროებს შუალედური კრებსითი ცხრილის შექმნას და ხელით განახლებას, მაგრამ იყენებს Excel-ის „მძიმე იარაღს“ - ფუნქციას. SUMESLIMN (SUMIFS). იმის ნაცვლად, რომ მოიძიოთ რიგების ნომრები შეჯამებაში, შეგიძლიათ გამოთვალოთ ისინი ამ ფორმულის გამოყენებით:

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

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

თუ შეჯამების გარეგნობა არ არის ძალიან შესაფერისი თქვენი ანგარიშისთვის, მაშინ შეგიძლიათ მისგან მწკრივების ნომრები ამოიღოთ საბოლოო ცხრილში არა პირდაპირ, როგორც ჩვენ გავაკეთეთ, არამედ ფუნქციის გამოყენებით. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). როგორ გავაკეთოთ ეს შეგიძლიათ იხილოთ აქ.

• როგორ შევქმნათ ანგარიში კრებსითი ცხრილის გამოყენებით
• როგორ დავაყენოთ გამოთვლები კრებსით ცხრილებში
• შერჩევითი დათვლა SUMIFS, COUNTIFS და ა.შ.