როგორ შევქმნათ ჩამოსაშლელი სია Excel-ში

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

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

სიის შექმნის პროცესი

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

მეთოდების რაოდენობა, რომლითაც ინფორმაცია შედის ამომხტარ სიაში, საკმაოდ დიდია.

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

   

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

   

3. დასახელებული დიაპაზონის მითითება. მეთოდი, რომელიც იმეორებს წინას, მაგრამ საჭიროა მხოლოდ დიაპაზონის წინასწარი დასახელება.
   

   

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

სიიდან მიღებული მონაცემების საფუძველზე

ვთქვათ, გვაქვს ცხრილი, სადაც აღწერილია სხვადასხვა ხილის სახეობები.

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

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

   

3. იპოვეთ პუნქტი "მონაცემთა ტიპი" და გადართეთ მნიშვნელობა "სიაში".
   

   

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

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

მონაცემთა ხელით ჩაწერით

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

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

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

   

3. კვლავ აირჩიეთ "სიის" ტიპი.
   

   

4. აქ თქვენ უნდა შეიყვანოთ „დიახ; არა“, როგორც წყარო. ჩვენ ვხედავთ, რომ ინფორმაცია შეყვანილია ხელით, მძიმით, ჩამოთვლისთვის.

OK-ზე დაჭერის შემდეგ გვაქვს შემდეგი შედეგი.

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

ჩამოსაშლელი სიის შექმნა OFFSET ფუნქციის გამოყენებით

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

გავხსნათ ფურცელი.

ჩამოსაშლელი სიისთვის ფუნქციის გამოსაყენებლად, თქვენ უნდა გააკეთოთ შემდეგი:

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

   

3. დააყენეთ "სია". ეს კეთდება ისევე, როგორც წინა მაგალითები. და ბოლოს, გამოიყენება შემდეგი ფორმულა: =OFFSET (A$2$;0;0;5). შევდივართ იქ, სადაც მითითებულია ის უჯრედები, რომლებიც გამოყენებული იქნება არგუმენტად.

შემდეგ პროგრამა შექმნის მენიუს ხილის სიით.

ამის სინტაქსი ასეთია:

=OFFSET(მინიშნება,ხაზის_გამონაცვლება,სვეტის_ჩანაცვლება,[სიმაღლე],[სიგანე])

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

ჩამოსაშლელი სია Excel-ში მონაცემთა ჩანაცვლებით (+ OFFSET ფუნქციის გამოყენებით)

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

ახალი ინფორმაციის შეყვანის მხარდაჭერით დინამიური სიის შესაქმნელად, თქვენ უნდა:

1. აირჩიეთ თქვენთვის საინტერესო უჯრედი.
2. გააფართოვეთ ჩანართი "მონაცემები" და დააჭირეთ "მონაცემთა დადასტურებას".
3. ფანჯარაში, რომელიც იხსნება, კვლავ აირჩიეთ "სია" ელემენტი და მიუთითეთ შემდეგი ფორმულა, როგორც მონაცემთა წყარო: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. დააწკაპუნეთ OK.

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

იმისათვის, რომ ფორმულამ ნორმალურად იმუშაოს, აუცილებელია დავადგინოთ არის თუ არა ცარიელი უჯრედები ფორმულის გზაზე. ისინი არ უნდა იყვნენ.

ჩამოსაშლელი სია სხვა ფურცლის ან Excel ფაილის მონაცემებით

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

1. გავააქტიუროთ უჯრედი, სადაც ვათავსებთ სიას.
2. ჩვენ ვხსნით ფანჯარას, რომელიც უკვე ვიცით. იმავე ადგილას, სადაც ადრე მივუთითეთ წყაროები სხვა დიაპაზონებისთვის, ფორმატში მითითებულია ფორმულა =INDIRECT(„[List1.xlsx]Sheet1!$A$1:$A$9“). ბუნებრივია, List1-ისა და Sheet1-ის ნაცვლად, შეგიძლიათ ჩასვათ თქვენი წიგნის და ფურცლის სახელები, შესაბამისად. 

ყურადღება! ფაილის სახელი მოცემულია კვადრატულ ფრჩხილებში. ამ შემთხვევაში, Excel ვერ გამოიყენებს ფაილს, რომელიც ამჟამად დახურულია ინფორმაციის წყაროდ.

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

დამოკიდებული ჩამოსაშლელების შექმნა

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

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

1. შექმენით პირველი სია დიაპაზონის სახელებით.
   

   

2. წყაროს შესვლის წერტილში სათითაოდ მონიშნულია საჭირო ინდიკატორები.
   

   

3. შექმენით მე-2 სია იმის მიხედვით, თუ რა ტიპის მცენარეს აირჩევს ადამიანმა. ალტერნატიულად, თუ პირველ სიაში მიუთითებთ ხეებს, მაშინ მეორე სიაში ინფორმაცია იქნება „მუხა, რცხილა, წაბლი“ და მის ფარგლებს გარეთ. აუცილებელია ფორმულის ჩაწერა მონაცემთა წყაროს შეყვანის ადგილას =INDIRECT(E3). E3 – უჯრედი, რომელიც შეიცავს დიაპაზონის სახელს 1.=INDIRECT(E3). E3 – უჯრედი სიის სახელით 1.

ახლა ყველაფერი მზადაა.

როგორ ავირჩიოთ რამდენიმე მნიშვნელობა ჩამოსაშლელი სიიდან?

ზოგჯერ შეუძლებელია უპირატესობის მინიჭება მხოლოდ ერთ მნიშვნელობაზე, ამიტომ უნდა შეირჩეს ერთზე მეტი. შემდეგ თქვენ უნდა დაამატოთ მაკრო გვერდის კოდს. კლავიშთა კომბინაციის გამოყენებით Alt + F11 იხსნება Visual Basic რედაქტორი. და კოდი ჩასმულია იქ.

პირადი ქვე Worksheet_Change (ByVal სამიზნე როგორც დიაპაზონი)

    წლის შეცდომა რეზიუმე შემდეგი

    If Not Intersect(Target, Range(«Е2:Е9»)) არის Nothing და Target.Cells.Count = 1 მაშინ

        Application.EnableEvents = False

        თუ Len (Target.Offset (0, 1)) = 0 მაშინ

            Target.Offset (0, 1) = Target

        სხვა

            Target.End (xlToRight) .Offset (0, 1) = Target

        ბოლო თუ

        სამიზნე.ClearContents

        Application.EnableEvents = True

    ბოლო თუ

ბოლო Sub 

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

პირადი ქვე Worksheet_Change (ByVal სამიზნე როგორც დიაპაზონი)

    წლის შეცდომა რეზიუმე შემდეგი

    If Not Intersect(Target, Range(«Н2:К2»)) არის Nothing და Target.Cells.Count = 1 მაშინ

        Application.EnableEvents = False

        თუ Len (Target.Offset (1, 0)) = 0 მაშინ

            Target.Offset (1, 0) = Target

        სხვა

            Target.End (xlDown) .Offset (1, 0) = Target

        ბოლო თუ

        სამიზნე.ClearContents

        Application.EnableEvents = True

    ბოლო თუ

ბოლო Sub

და ბოლოს, ეს კოდი გამოიყენება ერთ უჯრედში ჩასაწერად.

პირადი ქვე Worksheet_Change (ByVal სამიზნე როგორც დიაპაზონი)

    წლის შეცდომა რეზიუმე შემდეგი

    If Not Intersect(Target, Range(«C2:C5»)) არის Nothing და Target.Cells.Count = 1 მაშინ

        Application.EnableEvents = False

        newVal = სამიზნე

        აპლიკაცია.გაუქმება

        Oldval = სამიზნე

        თუ Len (oldval) <> 0 და Oldval <> newVal მაშინ

            Target = Target & «,» & newVal

        სხვა

            სამიზნე = newVal

        ბოლო თუ

        თუ Len (newVal) = 0 მაშინ Target.ClearContents

        Application.EnableEvents = True

    ბოლო თუ

ბოლო Sub

დიაპაზონები რედაქტირებადია.

როგორ შევქმნათ ჩამოსაშლელი სია ძიებით?

ამ შემთხვევაში, თავდაპირველად უნდა გამოიყენოთ სხვა ტიპის სია. იხსნება "დეველოპერი" ჩანართი, რის შემდეგაც თქვენ უნდა დააჭიროთ ან შეეხეთ (თუ ეკრანი შეხებულია) "Insert" - "ActiveX" ელემენტზე. მას აქვს კომბინირებული ყუთი. თქვენ მოგეთხოვებათ დახატოთ ეს სია, რის შემდეგაც იგი დაემატება დოკუმენტს.

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

ჩამოსაშლელი სია მონაცემთა ავტომატური ჩანაცვლებით

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

1. შექმენით უჯრედების ნაკრები მომავალი სიისთვის. ჩვენს შემთხვევაში, ეს არის ფერების ნაკრები. ჩვენ ვირჩევთ მას.
   

   

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

   

   

შემდეგი, თქვენ უნდა დაადასტუროთ ეს დიაპაზონი ღილაკზე "OK" დაჭერით.

ჩვენ ვირჩევთ მიღებულ ცხრილს და ვაძლევთ მას სახელს A სვეტის თავზე მდებარე შეყვანის ველის მეშვეობით.

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

1. აირჩიეთ უჯრედი, სადაც სია მდებარეობს.
2. გახსენით მონაცემთა დადასტურების დიალოგი.
   

   

3. ჩვენ დავაყენეთ მონაცემთა ტიპი "List" და როგორც მნიშვნელობები ვაძლევთ ცხრილის სახელს = ნიშნის საშუალებით.
   

   

   

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

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

როგორ დავაკოპიროთ ჩამოსაშლელი სია?

კოპირებისთვის საკმარისია გამოიყენოთ კლავიშთა კომბინაცია Ctrl + C და Ctrl + V. ასე რომ, ჩამოსაშლელი სია დაკოპირდება ფორმატირებასთან ერთად. ფორმატირების მოსაშორებლად, თქვენ უნდა გამოიყენოთ სპეციალური პასტა (კონტექსტურ მენიუში, ეს პარამეტრი გამოჩნდება სიის კოპირების შემდეგ), სადაც არის დაყენებული ვარიანტი „მნიშვნელობებზე პირობები“.

აირჩიეთ ყველა უჯრედი, რომელიც შეიცავს ჩამოსაშლელ სიას

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

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