Excel-də açılan siyahı necə etmək olar

Açılan siyahı məlumatla işləməyi daha rahat etməyə kömək edə biləcək inanılmaz faydalı alətdir. Bu, hər hansı digərləri kimi işləyə biləcəyiniz bir hüceyrədə eyni anda bir neçə dəyəri ehtiva etməyə imkan verir. Lazım olanı seçmək üçün sadəcə ox işarəsini vurun, bundan sonra uXNUMXbuXNUMXbis dəyərlərinin siyahısı göstərilir. Müəyyən birini seçdikdən sonra xana avtomatik olaraq onunla doldurulur və onun əsasında düsturlar yenidən hesablanır.

Excel açılan menyu yaratmaq üçün çoxlu müxtəlif üsullar təqdim edir və əlavə olaraq, onları çevik şəkildə fərdiləşdirməyə imkan verir. Bu üsulları daha ətraflı təhlil edək.

Siyahının yaradılması prosesi

Açılan menyu yaratmaq üçün “Məlumat” – “Məlumatların Təsdiqlənməsi” yolu boyunca menyu elementlərinə klikləyin. "Parametrlər" sekmesini tapmaq və əvvəllər açılmayıbsa, üzərinə klikləmək lazım olan bir informasiya qutusu açılacaq. Onun çoxlu parametrləri var, lakin “Məlumat növü” bizim üçün vacibdir. Bütün mənalar arasında “Siyahı” düzgündür.

Məlumatın açılan siyahıya daxil edilməsi üsullarının sayı olduqca böyükdür.

1. Eyni dialoq qutusunun eyni nişanında yerləşən "Mənbə" sahəsində nöqtəli vergüllə ayrılmış siyahı elementlərinin müstəqil göstəricisi.
   

   

2. Dəyərlərin ilkin göstəricisi. Mənbə sahəsi tələb olunan məlumatın mövcud olduğu aralığı ehtiva edir.
   

   

3. Adlandırılmış aralığın təyin edilməsi. Əvvəlkini təkrarlayan bir üsul, ancaq aralığı əvvəlcədən adlandırmaq lazımdır.
   

   

Bu üsullardan hər hansı biri istədiyiniz nəticəni verəcəkdir. Real həyat vəziyyətlərində açılan siyahıların yaradılması üsullarına baxaq.

Siyahıdakı məlumatlar əsasında

Tutaq ki, müxtəlif meyvələrin növlərini təsvir edən bir cədvəlimiz var.

Bu məlumat dəsti əsasında açılan menyuda siyahı yaratmaq üçün aşağıdakıları etməlisiniz:

1. Gələcək siyahı üçün qorunan xananı seçin.
2. Lentdə Data sekmesini tapın. Orada "Məlumatları yoxlayın" düyməsini sıxırıq.
   

   

3. "Məlumat növü" maddəsini tapın və dəyəri "Siyahıya" dəyişdirin.
   

   

4. "Mənbə" seçimini bildirən sahəyə istədiyiniz diapazonu daxil edin. Nəzərə alın ki, mütləq istinadlar göstərilməlidir ki, siyahının surətini çıxararkən məlumat dəyişməsin.
   

Bundan əlavə, birdən çox hüceyrədə bir anda siyahı yaratmaq funksiyası var. Buna nail olmaq üçün siz onların hamısını seçməli və əvvəllər təsvir olunan addımları yerinə yetirməlisiniz. Yenə mütləq istinadların yazıldığından əmin olmalısınız. Əgər ünvanda sütun və sətir adlarının yanında dollar işarəsi yoxdursa, o zaman sütun və sətir adlarının yanında $ işarəsi olana qədər F4 düyməsini sıxmaqla onları əlavə etmək lazımdır.

Əl ilə məlumatların qeyd edilməsi ilə

Yuxarıdakı vəziyyətdə siyahı tələb olunan diapazonu vurğulayaraq yazılmışdır. Bu əlverişli bir üsuldur, lakin bəzən məlumatları əl ilə qeyd etmək lazımdır. Bu, iş dəftərindəki məlumatların təkrarlanmasının qarşısını almağa imkan verəcəkdir.

Tutaq ki, biz iki mümkün seçimdən ibarət siyahı yaratmaq vəzifəsi ilə üzləşmişik: bəli və yox. Tapşırığı yerinə yetirmək üçün lazımdır:

1. Siyahı üçün xana üzərinə klikləyin.
2. “Məlumat”ı açın və orada bizə tanış olan “Məlumatların yoxlanılması” bölməsini tapın.
   

   

3. Yenə də "Siyahı" növünü seçin.
   

   

4. Burada “Bəli; Mənbə kimi Xeyr. Sadalamaq üçün nöqtəli vergüldən istifadə edərək məlumatın əl ilə daxil edildiyini görürük.

OK düyməsini basdıqdan sonra aşağıdakı nəticəni əldə edirik.

Bundan sonra, proqram avtomatik olaraq müvafiq xanada açılan menyu yaradacaqdır. İstifadəçinin açılan siyahıda elementlər kimi göstərdiyi bütün məlumatlar. Bir neçə xanada siyahı yaratmaq qaydaları əvvəlkilərə bənzəyir, yeganə istisna olmaqla, məlumatı nöqtəli vergüldən istifadə edərək əl ilə göstərməlisiniz.

OFFSET funksiyasından istifadə edərək açılan siyahının yaradılması

Klassik üsula əlavə olaraq, funksiyadan istifadə etmək mümkündür SƏRƏNCAMaçılan menyular yaratmaq üçün.

Vərəqi açaq.

Açılan siyahı üçün funksiyadan istifadə etmək üçün aşağıdakıları etməlisiniz:

1. Gələcək siyahını yerləşdirmək istədiyiniz maraq xanasını seçin.
2. “Məlumat” sekmesini və “Məlumatların Təsdiqlənməsi” pəncərəsini ardıcıl olaraq açın.
   

   

3. "Siyahı"nı təyin edin. Bu, əvvəlki nümunələrdə olduğu kimi edilir. Nəhayət, aşağıdakı formula istifadə olunur: =OFSET(A$2$;0;0;5). Arqument kimi istifadə olunacaq xanaların göstərildiyi yerə daxil oluruq.

Sonra proqram meyvələrin siyahısı ilə menyu yaradacaqdır.

Bunun üçün sintaksis belədir:

=OFFSET(istinad,sətir_offset,sütun_offset,[hündürlük],[en])

Bu funksiyanın 5 arqumenti olduğunu görürük. Əvvəlcə ofset ediləcək ilk xana ünvanı verilir. Növbəti iki arqument nə qədər sətir və sütunun əvəzlənməsini təyin edir. Bizdən danışarkən, Hündürlük arqumenti 5-dir, çünki o, siyahının hündürlüyünü təmsil edir. 

Məlumatların dəyişdirilməsi ilə Excel-də açılan siyahı (+ OFFSET funksiyasından istifadə etməklə)

Verilmiş halda SƏRƏNCAM sabit diapazonda yerləşən pop-up menyu yaratmağa icazə verilir. Bu metodun dezavantajı odur ki, elementi əlavə etdikdən sonra formulanı özünüz redaktə etməli olacaqsınız.

Yeni məlumatların daxil edilməsi dəstəyi ilə dinamik siyahı yaratmaq üçün aşağıdakıları etməlisiniz:

1. Maraq xanasını seçin.
2. "Məlumat" sekmesini genişləndirin və "Məlumatların Təsdiqlənməsi" üzərinə klikləyin.
3. Açılan pəncərədə yenidən “Siyahı” elementini seçin və məlumat mənbəyi kimi aşağıdakı düsturu göstərin: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Tamam'ı basın.

Bu funksiya ehtiva edir COUNTIF, dərhal neçə hüceyrənin doldurulduğunu müəyyən etmək üçün (daha çox istifadə sayına malik olsa da, biz bunu sadəcə olaraq müəyyən bir məqsəd üçün burada yazırıq).

Düsturun normal işləməsi üçün formulun yolunda boş xanaların olub-olmadığını izləmək lazımdır. Onlar olmamalıdır.

Başqa vərəqdən və ya Excel faylından verilənlərin olduğu açılan siyahı

Başqa bir sənəddən və ya hətta eyni faylda olan vərəqdən məlumat almaq lazımdırsa, klassik üsul işləmir. Bunun üçün funksiyadan istifadə olunur DOLAYI, bu, düzgün formatda başqa bir vərəqdə yerləşən hüceyrəyə və ya ümumiyyətlə bir fayla keçid daxil etməyə imkan verir. Aşağıdakıları etməlisiniz:

1. Siyahını yerləşdirdiyimiz xananı aktivləşdirin.
2. Artıq bildiyimiz pəncərəni açırıq. Əvvəllər digər diapazonlar üçün mənbələri göstərdiyimiz yerdə, formatda bir düstur göstərilir =DOLAYI(“[List1.xlsx]Cədvəl1!$A$1:$A$9”). Təbii ki, List1 və Sheet1 əvəzinə kitab və vərəq adlarını müvafiq olaraq daxil edə bilərsiniz. 

Diqqət! Fayl adı kvadrat mötərizədə verilir. Bu halda Excel hazırda bağlı olan fayldan məlumat mənbəyi kimi istifadə edə bilməyəcək.

Onu da qeyd etmək lazımdır ki, fayl adının özü o halda məna kəsb edir ki, tələb olunan sənəd siyahının daxil ediləcəyi qovluqla eyni qovluqda yerləşir. Əgər belə deyilsə, onda siz bu sənədin ünvanını tam şəkildə göstərməlisiniz.

Asılı açılan menyuların yaradılması

Asılı siyahı, məzmunu istifadəçinin başqa siyahıdakı seçimindən təsirlənən siyahıdır. Tutaq ki, qarşımızda hər birinə ad verilmiş üç diapazondan ibarət bir masa var.

Nəticəsi başqa siyahıda seçilmiş seçimdən təsirlənən siyahıları yaratmaq üçün bu addımları yerinə yetirməlisiniz.

1. Aralıq adları ilə 1-ci siyahı yaradın.
   

   

2. Mənbəyə giriş nöqtəsində tələb olunan göstəricilər bir-bir vurğulanır.
   

   

3. İnsanın seçdiyi bitki növündən asılı olaraq 2-ci siyahı yaradın. Alternativ olaraq, əgər siz birinci siyahıda ağacları qeyd etsəniz, ikinci siyahıdakı məlumat “palıd, vələs, şabalıd” və digərləri olacaq. Düsturu məlumat mənbəyinin daxil olduğu yerə yazmaq lazımdır =DOLAYI(E3). E3 – 1 diapazonunun adını ehtiva edən xana.=DOLAYI(E3). E3 – siyahının adı olan xana 1.

İndi hər şey hazırdır.

Açılan siyahıdan birdən çox dəyəri necə seçmək olar?

Bəzən yalnız bir dəyərə üstünlük vermək mümkün deyil, ona görə də birdən çox seçilməlidir. Sonra səhifə koduna makro əlavə etməlisiniz. Alt + F11 düymələr birləşməsindən istifadə Visual Basic Redaktorunu açır. Və kod ora daxil edilir.

Şəxsi Alt İş Cədvəli_Dəyişikliyi (Rəsm kimi Hədəf ByVal)

    Error Sonraki Resume

    Əgər Kəsişməzsə(Hədəf, Aralıq(«Е2:Е9»)) Heç Bir Şey Deyil və Hədəf. Hüceyrələrin Sayısı = 1 O zaman

        Application.EnableEvents = Yanlış

        Əgər Len (Target.Offset (0, 1)) = 0 O zaman

            Hədəf.Ofset (0, 1) = Hədəf

        daha

            Target.End (xlToRight) .Offset (0, 1) = Hədəf

        Axırıncı halda

        Target.ClearContents

        Application.EnableEvents = Doğrudur

    Axırıncı halda

End Sub 

Hüceyrələrin məzmununun aşağıda göstərilməsi üçün redaktora aşağıdakı kodu daxil edirik.

Şəxsi Alt İş Cədvəli_Dəyişikliyi (Rəsm kimi Hədəf ByVal)

    Error Sonraki Resume

    Əgər kəsişməzsə(Hədəf, Aralıq(«Н2:К2»)) Heç bir şey deyil və Hədəf. Hüceyrələrin sayı = 1 O zaman

        Application.EnableEvents = Yanlış

        Əgər Len (Target.Offset (1, 0)) = 0 O zaman

            Hədəf.Ofset (1, 0) = Hədəf

        daha

            Target.End (xlDown) .Offset (1, 0) = Hədəf

        Axırıncı halda

        Target.ClearContents

        Application.EnableEvents = Doğrudur

    Axırıncı halda

End Sub

Və nəhayət, bu kod bir xanaya yazmaq üçün istifadə olunur.

Şəxsi Alt İş Cədvəli_Dəyişikliyi (Rəsm kimi Hədəf ByVal)

    Error Sonraki Resume

    Əgər kəsişməzsə(Hədəf, Aralıq(«C2:C5»)) Heç Bir Şey Deyil və Hədəfdir. Hüceyrələr. Say = 1 Onda

        Application.EnableEvents = Yanlış

        newVal = Hədəf

        Tətbiq. Geri götür

        oldval = Hədəf

        Əgər Len (oldval) <> 0 Və oldval <> newVal Sonra

            Hədəf = Hədəf & «,» & newVal

        daha

            Hədəf = yeni Val

        Axırıncı halda

        Əgər Len (newVal) = 0 O zaman Target.ClearContents

        Application.EnableEvents = Doğrudur

    Axırıncı halda

End Sub

Aralıqlar redaktə edilə bilər.

Axtarışla açılan siyahı necə etmək olar?

Bu halda, ilkin olaraq fərqli bir siyahı növündən istifadə etməlisiniz. "Developer" sekmesi açılır, bundan sonra "Daxil et" - "ActiveX" elementinə klikləmək və ya vurmaq (ekran toxunursa) lazımdır. Kombo qutusu var. Sizdən bu siyahını çəkmək təklif olunacaq, bundan sonra o, sənədə əlavə olunacaq.

Bundan əlavə, ListFillRange seçimində aralığın göstərildiyi xassələr vasitəsilə konfiqurasiya edilir. İstifadəçi tərəfindən müəyyən edilmiş dəyərin göstərildiyi xana LinkedCell seçimi ilə konfiqurasiya edilir. Sonra, yalnız ilk simvolları yazmalısınız, çünki proqram avtomatik olaraq mümkün dəyərləri təklif edəcəkdir.

Məlumatların avtomatik dəyişdirilməsi ilə açılan siyahı

Məlumatların diapazona əlavə edildikdən sonra avtomatik olaraq əvəz edilməsi funksiyası da var. Bunu etmək asandır:

1. Gələcək siyahı üçün hüceyrələr dəsti yaradın. Bizim vəziyyətimizdə bu rənglər dəstidir. Biz onu seçirik.
   

   

2. Sonra onu cədvəl kimi formatlaşdırmaq lazımdır. Eyni adlı düyməni sıxmaq və cədvəl üslubunu seçmək lazımdır.
   

   

   

Sonra, "OK" düyməsini basaraq bu diapazonu təsdiqləməlisiniz.

Yaranan cədvəli seçirik və A sütununun üstündə yerləşən giriş sahəsi vasitəsilə ona ad veririk.

Budur, bir cədvəl var və o, sizə lazım olan açılan siyahı üçün əsas kimi istifadə edilə bilər:

1. Siyahının yerləşdiyi xananı seçin.
2. Data Validation dialoqunu açın.
   

   

3. Məlumat növünü "Siyahı" olaraq təyin edirik və dəyərlər olaraq = işarəsi vasitəsilə cədvəlin adını veririk.
   

   

   

Hər şey, hüceyrə hazırdır və rənglərin adları əvvəlcə bizə lazım olduğu kimi göstərilir. İndi yeni mövqeləri sadəcə sonuncudan dərhal sonra bir az aşağıda yerləşən xanaya yazmaqla əlavə edə bilərsiniz.

Cədvəlin üstünlüyü budur ki, yeni məlumatlar əlavə edildikdə diapazon avtomatik olaraq artır. Müvafiq olaraq, bu siyahı əlavə etmək üçün ən əlverişli yoldur.

Açılan siyahını necə köçürmək olar?

Kopyalamaq üçün Ctrl + C və Ctrl + V düymələr birləşməsindən istifadə etmək kifayətdir. Beləliklə, açılan siyahı formatlaşdırma ilə birlikdə kopyalanacaq. Formatlaşdırmanı aradan qaldırmaq üçün xüsusi bir pastadan istifadə etməlisiniz (kontekst menyusunda bu seçim siyahının surətini çıxardıqdan sonra görünür), burada "dəyərlər üzrə şərtlər" seçimi təyin olunur.

Açılan siyahı olan bütün xanaları seçin

Bu tapşırığı yerinə yetirmək üçün "Tap və Seç" qrupunda "Bir qrup hüceyrə seçin" funksiyasından istifadə etməlisiniz.

Bundan sonra, "Məlumatların Təsdiqlənməsi" menyusunda "Hamı" və "Eynilər" elementlərini seçməli olduğunuz bir dialoq qutusu açılacaqdır. Birinci element bütün siyahıları seçir, ikincisi isə yalnız müəyyən olanlara oxşar olanları seçir.