როგორ დავაყენოთ მნიშვნელობების დიაპაზონი Excel-ში

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

Excel-ში უჯრედების დიაპაზონის ავტომატურად შეცვლა

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

როგორ განვახორციელოთ დიაპაზონის ავტომატური შეცვლა Excel-ში

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

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

1. თუ პრობლემა ამ გზით მოგვარდება, B სვეტში შეტანილი უჯრედების სხვა მიზნებისთვის გამოყენება შეუძლებელი იქნება.
2. ასეთი ცხრილი მოიხმარს დიდ RAM-ს, რაც შეუძლებელს გახდის დოკუმენტის გამოყენებას სუსტ კომპიუტერებზე.

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

1. გადადით "ფორმულების" ჩანართზე, რომელიც მდებარეობს მთავარ მენიუში. იქნება განყოფილება „განსაზღვრული სახელები“, სადაც არის ღილაკი „სახელის მინიჭება“, რომელზეც უნდა დავაჭიროთ.
2. შემდეგი, გამოჩნდება დიალოგური ფანჯარა, რომელშიც თქვენ უნდა შეავსოთ ველები, როგორც ნაჩვენებია ეკრანის სურათზე. მნიშვნელოვანია აღინიშნოს, რომ ჩვენ უნდა გამოვიყენოთ ფუნქცია = გადაადგილება ფუნქციასთან ერთად CHECKავტომატური განახლების დიაპაზონის შესაქმნელად. 
3. ამის შემდეგ ჩვენ უნდა გამოვიყენოთ ფუნქცია თანხა, რომელიც არგუმენტად იყენებს ჩვენს დინამიურ დიაპაზონს. 

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

OFFSET ფუნქცია Excel-ში

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

1. დაწყების უჯრედი. ამ არგუმენტით მომხმარებელს შეუძლია მიუთითოს დიაპაზონის რომელი უჯრედი ჩაითვლება ზედა მარცხნივ. ის მოახსენებს ქვემოთ და მარჯვნივ.
2. დიაპაზონი კომპენსირებულია რიგებით. ამ დიაპაზონის გამოყენებით, ჩვენ ვაყენებთ უჯრედების რაოდენობას, რომლითაც უნდა მოხდეს ოფსეტური დიაპაზონის ზედა მარცხენა უჯრედიდან. თქვენ შეგიძლიათ გამოიყენოთ არა მხოლოდ დადებითი მნიშვნელობები, არამედ ნული და მინუსი. ამ შემთხვევაში, გადაადგილება შეიძლება საერთოდ არ მოხდეს, ან განხორციელდეს საპირისპირო მიმართულებით.
3. დიაპაზონი გადანაწილებულია სვეტებით. ეს პარამეტრი წინა პარამეტრის მსგავსია, მხოლოდ ის საშუალებას გაძლევთ დააყენოთ დიაპაზონის ჰორიზონტალური გადაადგილების ხარისხი. აქ ასევე შეგიძლიათ გამოიყენოთ როგორც ნულოვანი, ასევე უარყოფითი მნიშვნელობები.
4. სიმაღლის დიაპაზონის რაოდენობა. სინამდვილეში, ამ არგუმენტის სათაური გვიჩვენებს, თუ რას ნიშნავს იგი. ეს არის უჯრედების რაოდენობა, რომლითაც დიაპაზონი უნდა გაიზარდოს.
5. დიაპაზონის მნიშვნელობა სიგანეში. არგუმენტი წინას მსგავსია, მხოლოდ ის ეხება სვეტებს.

თქვენ არ გჭირდებათ ბოლო ორი არგუმენტის მითითება, თუ არ გჭირდებათ. ამ შემთხვევაში, დიაპაზონის მნიშვნელობა იქნება მხოლოდ ერთი უჯრედი. მაგალითად, თუ მიუთითებთ ფორმულას =OFFSET(A1;0;0), ეს ფორმულა მიუთითებს იმავე უჯრედზე, როგორც პირველ არგუმენტში. თუ ვერტიკალური ოფსეტი დაყენებულია 2 ერთეულზე, მაშინ ამ შემთხვევაში უჯრედი მიუთითებს A3 უჯრედზე. ახლა მოდით დეტალურად აღვწეროთ რას ნიშნავს ფუნქცია CHECK.

COUNT ფუნქცია Excel-ში

ფუნქციის გამოყენებით CHECK ჩვენ განვსაზღვრავთ B სვეტის რამდენი უჯრა შევავსეთ ჯამში. ანუ ორი ფუნქციის გამოყენებით ვადგენთ დიაპაზონში რამდენი უჯრედია შევსებული და მიღებული ინფორმაციის საფუძველზე განვსაზღვრავთ დიაპაზონის ზომას. ამრიგად, საბოლოო ფორმულა იქნება შემდეგი: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

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

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

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

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

დინამიური დიაგრამები Excel-ში

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

1. ჩვენ ვირჩევთ ჩვენს დიაპაზონს, რის შემდეგაც ჩავსვამთ "ჰისტოგრამა დაჯგუფებით" ტიპის სქემას. თქვენ შეგიძლიათ იპოვოთ ეს ელემენტი "ჩასმა" განყოფილებაში "დიაგრამები - ჰისტოგრამა" განყოფილებაში.
2. ვაკეთებთ მაუსის მარცხენა დაწკაპუნებას ჰისტოგრამის შემთხვევით სვეტზე, რის შემდეგაც ფუნქცია =SERIES() გამოჩნდება ფუნქციის ხაზში. ეკრანის სურათზე შეგიძლიათ იხილოთ დეტალური ფორმულა. 
3. ამის შემდეგ საჭიროა გარკვეული ცვლილებების შეტანა ფორმულაში. თქვენ უნდა შეცვალოთ დიაპაზონი "Sheet1!" შემდეგ. დიაპაზონის სახელზე. ეს გამოიწვევს შემდეგ ფუნქციას: =ROW(Sheet1!$B$1;;Sheet1! შემოსავალი;1)
4. ახლა რჩება ანგარიშში ახალი ჩანაწერის დამატება, რათა შეამოწმოს, ავტომატურად განახლდება თუ არა სქემა.

მოდით შევხედოთ ჩვენს დიაგრამას.

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

დასახელებული დიაპაზონები და მათი გამოყენება

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

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

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

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

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

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

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

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

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

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

თუ სათაური შეიცავს რამდენიმე სიტყვას, Excel ავტომატურად გამოყოფს მათ ქვედა ხაზით.

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