დინამიური დიაპაზონი ავტომატური ზომით

გაქვთ თუ არა Excel-ში მონაცემების მქონე ცხრილები, რომელთა ზომა შეიძლება შეიცვალოს, ანუ სტრიქონების (სვეტების) რაოდენობა შეიძლება გაიზარდოს ან შემცირდეს მუშაობის პროცესში? თუ მაგიდის ზომები "ცურავს", მაშინ მოგიწევთ მუდმივად აკონტროლოთ ეს მომენტი და გამოასწოროთ იგი:

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

ეს ყველაფერი სულ არ მოგბეზრდებათ 😉

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

მეთოდი 1. ჭკვიანი მაგიდა

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

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

ახლა ჩვენ შეგვიძლია გამოვიყენოთ დინამიური ბმულები ჩვენს "ჭკვიან მაგიდასთან":

• მაგიდის 1 - ბმული მთელ ცხრილზე, გარდა სათაურის მწკრივისა (A2:D5)
• ცხრილი1[#ყველა] - ბმული მთელ ცხრილზე (A1:D5)
• ცხრილი 1 [პეტრე] - მითითება დიაპაზონის სვეტზე პირველი უჯრედის სათაურის გარეშე (C2:C5)
• ცხრილი1[#Headers] - ბმული "სათაურზე" სვეტების სახელებით (A1:D1)

ასეთი ცნობები მშვენივრად მუშაობს ფორმულებში, მაგალითად:

= SUM (ცხრილი 1 [მოსკოვი]) – თანხის გამოთვლა სვეტისთვის „მოსკოვი“

or

=VPR(F5;მაგიდის 1;3;0) – მოძებნეთ ცხრილში თვე F5 უჯრიდან და გამოუშვით სანკტ-პეტერბურგის ჯამი (რა არის VLOOKUP?)

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

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

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

იმათ. სმარტ ცხრილის ბმული ტექსტის სტრიქონის სახით (ბრჭყალებში!) იქცევა სრულფასოვან ბმულად და ჩამოსაშლელი სია ჩვეულებრივ აღიქვამს მას.

მეთოდი 2: დინამიური დასახელებული დიაპაზონი

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

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

ჩვენ დაგვჭირდება ორი ჩაშენებული Excel ფუნქცია, რომელიც ხელმისაწვდომია ნებისმიერ ვერსიაში - POICPOZ (მატჩი) დიაპაზონის ბოლო უჯრედის დასადგენად და ინდექსი (INDEX) დინამიური ბმულის შესაქმნელად.

ბოლო უჯრედის პოვნა MATCH-ის გამოყენებით

MATCH (ძიების_მნიშვნელობა, დიაპაზონი, შესატყვისი_ტიპი) – ფუნქცია, რომელიც ეძებს მოცემულ მნიშვნელობას დიაპაზონში (სტრიქონი ან სვეტი) და აბრუნებს იმ უჯრედის რიგით ნომერს, სადაც ის იქნა ნაპოვნი. მაგალითად, ფორმულა MATCH(“მარტი”;A1:A5;0) შედეგად დააბრუნებს რიცხვს 4, რადგან სიტყვა “მარტი” მდებარეობს A1:A5 სვეტის მეოთხე უჯრედში. ფუნქციის ბოლო არგუმენტი Match_Type = 0 ნიშნავს, რომ ჩვენ ვეძებთ ზუსტ შესატყვისს. თუ ეს არგუმენტი არ არის მითითებული, მაშინ ფუნქცია გადავა ძიების რეჟიმში უახლოეს უმცირეს მნიშვნელობისთვის - ეს არის ზუსტად ის, რისი წარმატებით გამოყენებაც შესაძლებელია ჩვენს მასივში ბოლო დაკავებული უჯრედის მოსაძებნად.

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

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

გარანტიისთვის შეგიძლიათ გამოიყენოთ ნომერი 9E + 307 (9-ჯერ 10 307-ის ხარისხზე, ანუ 9 307 ნულით) – მაქსიმალური რიცხვი, რომლითაც Excel-ს პრინციპში შეუძლია მუშაობა.

თუ ჩვენს სვეტში არის ტექსტური მნიშვნელობები, მაშინ, როგორც ყველაზე დიდი შესაძლო რიცხვის ეკვივალენტი, შეგიძლიათ ჩასვათ კონსტრუქცია REPEAT("i", 255) - ტექსტის სტრიქონი, რომელიც შედგება 255 ასოსგან "i" - ბოლო ასო. ანბანი. მას შემდეგ, რაც Excel რეალურად ადარებს სიმბოლოების კოდებს ძიებისას, ჩვენი ცხრილის ნებისმიერი ტექსტი ტექნიკურად იქნება „უფრო პატარა“ ვიდრე ასეთი გრძელი „იიიიიიიიიიიიიიიიიიიიიიიიიიიიიიიიიიიი η το“ ხაზს:

შექმენით ბმული INDEX-ის გამოყენებით

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

INDEX (დიაპაზონი; მწკრივის_რაოდენობა; სვეტის_რიცხვი)

ის იძლევა უჯრედის შიგთავსს დიაპაზონიდან მწკრივისა და სვეტის ნომრის მიხედვით, ანუ, მაგალითად, ფუნქცია =INDEX(A1:D5;3;4) ჩვენს ცხრილში ქალაქებით და წინა მეთოდიდან თვეები მისცემს 1240 - შინაარსს. მე-3 მწკრივიდან და მე-4 სვეტიდან, ანუ უჯრედები D3. თუ არის მხოლოდ ერთი სვეტი, მაშინ მისი რიცხვი შეიძლება გამოტოვოთ, ანუ ფორმულა INDEX(A2:A6;3) ბოლო ეკრანის სურათზე მისცემს "Samara".

და არის ერთი არც თუ ისე აშკარა ნიუანსი: თუ INDEX უბრალოდ არ არის შეყვანილი უჯრედში = ნიშნის შემდეგ, როგორც ყოველთვის, არამედ გამოიყენება როგორც ბოლო ნაწილის მითითება დიაპაზონზე ორწერტილის შემდეგ, მაშინ ის აღარ გასცემს. უჯრედის შინაარსი, მაგრამ მისი მისამართი! ამგვარად, ფორმულა, როგორიცაა $A$2:INDEX($A$2:$A$100;3) მიუთითებს A2:A4 დიაპაზონზე გამოსავალზე.

და აქ შემოდის MATCH ფუნქცია, რომელიც ჩავსვით INDEX-ის შიგნით, რათა დინამიურად განვსაზღვროთ სიის დასასრული:

=$A$2:INDEX($A$2:$A$100; მატჩი (რეპ („I“; 255);A2:A100))

შექმენით დასახელებული დიაპაზონი

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

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

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