Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

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

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



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

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



ეს მეორე მეთოდი ამარტივებს სიაში არსებული არჩევანის რედაქტირებას, მაგრამ საგნების დამატება ან ამოღება შეიძლება პრობლემური იყოს. მას შემდეგ, რაც დასახელებული დიაპაზონი (FruitChoices, ჩვენს მაგალითში) გულისხმობს უჯრედების ფიქსირებულ დიაპაზონს ($ H $ 3: $ H $ 10, როგორც ნაჩვენებია), თუ H11 ან ქვემოთ უჯრედებს მეტი არჩევანი დაემატება, ისინი არ გამოჩნდება ჩამოსაშლელად (რადგან ეს უჯრედები არ არის FruitChoices დიაპაზონის ნაწილი).

ანალოგიურად, თუ, მაგალითად, მსხლისა და მარწყვის ჩანაწერები წაიშლება, ისინი აღარ გამოჩნდება ჩამოშლადი, მაგრამ ამის ნაცვლად, ჩამოსაშლელი მოიცავს ორ "ცარიელ" არჩევანს, რადგან ჩამოსაშლელი მაინც მიუთითებს მთელ FruitChoices დიაპაზონში. , H9 და H10 ცარიელი უჯრედების ჩათვლით.

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

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

ნორმალური (სტატიკური) დიაპაზონის სახელი გულისხმობს უჯრედების მითითებულ დიაპაზონს ($ H $ 3: $ H $ 10 ჩვენს მაგალითში, იხილეთ ქვემოთ):



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



სანამ დავიწყებთ, დარწმუნდით, რომ გადმოწერთ ჩვენი Excel მაგალითის ფაილს (დალაგების მაკროები გამორთულია).

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



ხილის არჩევანის დინამიური დიაპაზონის განსაზღვრისთვის გამოყენებული მთელი ფორმულაა: = OFFSET (FruitsHeading, 1,0, IFERROR (Match (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

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

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

ახლა მოდით დავყოთ ფორმულა ნაწილებად (თითოეული კოდის ფერადი კოდირება), რომ გავიგოთ როგორ მუშაობს: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET) FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

"შინაგანი" ნაჭერი არის OFFSET (FruitsHeading, 1,0,20, 1)ეს მიუთითებს 20 უჯრედის ბლოკში (FruitsHeading უჯრედის ქვეშ), სადაც შესაძლებელია არჩევანის შეყვანა. ეს OFFSET ფუნქცია ძირითადად ამბობს: დაიწყეთ FruitsHeading უჯრით, ჩამოდით 1 მწკრივით და 0 სვეტით, შემდეგ შეარჩიეთ 20 სტრიქონის სიგრძის და 1 სვეტის სიგანე. ეს გვაძლევს 20 რიგის ბლოკს, სადაც შედის ხილის არჩევანი.

ფორმულის შემდეგი ნაწილია ISBLANK ფუნქცია: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (above)), 0,0), 0) -1) , 20), 1)

აქ, OFFSET ფუნქცია (ზემოთ აღწერილი) შეიცვალა "ზემოთ" (კითხვის გასაადვილებლად).მაგრამ ISBLANK ფუნქცია მუშაობს უჯრედების 20 რიგის დიაპაზონში, რომლებსაც განსაზღვრავს OFFSET ფუნქცია.

ISBLANK ქმნის 20 სიმართლეს და TRUE და FALSE მნიშვნელობებს, მითითებულია თუ არა თითოეული ცალკეული უჯრედი 20 რიგის დიაპაზონში, რომელსაც მითითებულია OFFSET ფუნქცია, ცარიელია (ცარიელი) თუ არა. ამ მაგალითში, სიმრავლეში პირველი 8 მნიშვნელობები იქნება FALSE, რადგან პირველი 8 უჯრედი არ არის ცარიელი და ბოლო 12 მნიშვნელობა იქნება TRUE.

ფორმულის შემდეგი ნაწილია INDEX ფუნქცია: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ზემოთ, 0,0), 0) -1,20) , 1)

ისევ, "ზემოთ" ეხება ზემოთ აღწერილ ISBLANK და OFFSET ფუნქციებს.INDEX ფუნქცია აბრუნებს მასივს, რომელიც შეიცავს ISBLANK ფუნქციის მიერ შექმნილ 20 TRUE/FALSE მნიშვნელობებს.

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

ფორმულის შემდეგი ნაწილია MATCH ფუნქცია: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, above, 0) -1,20), 1)

MATCH ფუნქცია აბრუნებს პირველი TRUE მნიშვნელობის პოზიციას, INDEX ფუნქციის მიერ დაბრუნებულ მასივში.რადგან სიაში პირველი 8 ჩანაწერი ცარიელი არ არის, მასივის პირველი 8 მნიშვნელობა იქნება FALSE და მეცხრე მნიშვნელობა იქნება TRUE (რადგან დიაპაზონში მე -9 რიგი ცარიელია).

ასე რომ, MATCH ფუნქცია დააბრუნებს 9-ის მნიშვნელობას. ამ შემთხვევაში, ჩვენ ნამდვილად გვინდა ვიცოდეთ რამდენი ჩანაწერია სიაში, ამიტომ ფორმულა MATCH მნიშვნელობიდან გამოაქვს 1 (რომელიც იძლევა ბოლო ჩანაწერის პოზიცია). საბოლოოდ, MATCH (TRUE, ზემოთ, 0) -1 დააბრუნებს 8 – ის მნიშვნელობას.

ფორმულის შემდეგი ნაწილია IFERROR ფუნქცია: = OFFSET (FruitsHeading, 1,0, IFERROR (the ზემოთ, 20), 1)

IFERROR ფუნქცია აბრუნებს ალტერნატიულ მნიშვნელობას, თუ მითითებული პირველი მნიშვნელობის შედეგია შეცდომა.ეს ფუნქცია შედის მას შემდეგ, რაც, თუ უჯრედების მთელი ბლოკი (ყველა 20 მწკრივი) ივსება ჩანაწერებით, MATCH ფუნქცია დაუბრუნებს შეცდომას.

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

ასე რომ, თუ მთელი სია სავსეა (და შესაბამისად, MATCH აბრუნებს შეცდომას), IFERROR ფუნქცია დაგიბრუნებთ 20 – ის მნიშვნელობას (იმის ცოდნა, რომ სიაში უნდა იყოს 20 ჩანაწერი).

დაბოლოს, OFFSET (FruitsHeading, 1,0, ზემოთ, 1) აბრუნებს იმ დიაპაზონს, რომელსაც სინამდვილეში ვეძებთ: დაიწყეთ FruitsHeading უჯრედიდან, ჩამოდით 1 მწკრივზე და 0 სვეტზე, შემდეგ აირჩიეთ უბანი ეს არის მრავალი მწკრივი, სანამ სიაში არის ჩანაწერები (და 1 სვეტის სიგანე). ასე რომ, მთელი ფორმულა ერთად დააბრუნებს დიაპაზონს, რომელიც შეიცავს მხოლოდ რეალურ ჩანაწერებს (პირველი ცარიელი უჯრისკენ).

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



ფაილის მაგალითი (დინამიკური სიები), რომელიც აქ გამოყენებულია, შედის და მისი ჩამოტვირთვა შეგიძლიათ ამ ვებსაიტიდან. მაკროები არ მუშაობს, რადგან WordPress არ მოსწონს Excel წიგნები, რომელშიც მაკროებია.

სიის ბლოკში მწკრივების რაოდენობის მითითების ალტერნატივად, სიის ბლოკს შეიძლება მიენიჭოს საკუთარი დიაპაზონის სახელი, რომელიც შემდეგ შეიძლება გამოყენებულ იქნას შეცვლილ ფორმულაში. მაგალითის ფაილში, მეორე სია (სახელები) იყენებს ამ მეთოდს. აქ, სიის მთლიანი ბლოკი ("NAMES" სათაურის ქვეშ, 40 სტრიქონი ფაილის მაგალითში) ენიჭება NameBlock დიაპაზონის სახელი.NamesList- ის განსაზღვრის ალტერნატიული ფორმულაა შემდეგში: = OFFSET (NamesHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (NamesBlock), 0,0)), 0) -1, ROWS (NamesBlock)), 1)

სადაც NamesBlock ცვლის OFFSET (FruitsHeading, 1,0,20,1) და ROWS (NamesBlock) ცვლის 20 (რიგების რაოდენობა) წინა ფორმულაში.

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


SETUP UA COMPUTER BLOG