Excel бағдарламасында ашылмалы тізімді қалай жасауға болады

Ашылмалы тізім ақпаратпен жұмыс істеуді ыңғайлы етуге көмектесетін керемет пайдалы құрал. Бұл ұяшықта бірден бірнеше мәндерді қамтуға мүмкіндік береді, олармен басқалармен сияқты жұмыс істей аласыз. Қажеттісін таңдау үшін көрсеткі белгішесін басыңыз, содан кейін uXNUMXbuXNUMXbis мәндерінің тізімі көрсетіледі. Нақтысын таңдағаннан кейін ұяшық онымен автоматты түрде толтырылады және оның негізінде формулалар қайта есептеледі.

Excel ашылмалы мәзірді құрудың көптеген әртүрлі әдістерін ұсынады, сонымен қатар ол оларды икемді түрде теңшеуге мүмкіндік береді. Осы әдістерді толығырақ талдап көрейік.

Тізімді құру процесі

Қалқымалы мәзірді құру үшін «Деректер» – «Деректерді тексеру» жолындағы мәзір элементтерін басыңыз. Диалогтық терезе ашылады, онда «Параметрлер» қойындысын табу керек және егер ол бұрын ашылмаған болса, оны басыңыз. Оның көптеген параметрлері бар, бірақ біз үшін «Деректердің түрі» тармағы маңызды. Барлық мағыналардың ішінде «Тізім» дұрыс.

Қалқымалы тізімге ақпаратты енгізу әдістерінің саны өте көп.

1. Бір тілқатысу терезесінің сол қойындысында орналасқан «Көз» өрісінде нүктелі үтірмен бөлінген тізім элементтерінің тәуелсіз көрсеткіші.
   

   

2. Құндылықтарды алдын ала көрсету. Көз өрісі қажетті ақпарат қолжетімді ауқымды қамтиды.
   

   

3. Атаулы ауқымды анықтау. Алдыңғысын қайталайтын әдіс, бірақ диапазонды алдын ала атау қажет.
   

   

Осы әдістердің кез келгені қажетті нәтиже береді. Нақты өмірлік жағдайларда ашылмалы тізімдерді құру әдістерін қарастырайық.

Тізімдегі деректер негізінде

Бізде әртүрлі жемістердің түрлерін сипаттайтын кесте бар делік.

Осы ақпарат жинағы негізінде ашылмалы мәзірде тізім жасау үшін келесі әрекеттерді орындау қажет:

1. Болашақ тізім үшін сақталған ұяшықты таңдаңыз.
2. Таспадағы Деректер қойындысын табыңыз. Онда біз «Деректерді тексеру» түймесін басамыз.
   

   

3. «Деректердің түрі» тармағын тауып, мәнді «Тізімге» ауыстырыңыз.
   

   

4. «Көз» опциясын білдіретін өріске қажетті ауқымды енгізіңіз. Тізімді көшіру кезінде ақпарат жылжымайтындай абсолютті сілтемелер көрсетілуі керек екенін ескеріңіз.
   

Бұған қоса, бірден көп ұяшықта тізімдерді жасау функциясы бар. Бұған қол жеткізу үшін олардың барлығын таңдап, бұрын сипатталған қадамдарды орындау керек. Тағы да абсолютті сілтемелердің жазылғанына көз жеткізу керек. Егер мекенжайда баған және жол атауларының жанында доллар белгісі болмаса, онда $ белгісі баған және жол атауларының жанында болғанша F4 пернесін басу арқылы қосу керек.

Деректерді қолмен жазу арқылы

Жоғарыда келтірілген жағдайда тізім қажетті диапазонды бөлектеу арқылы жазылды. Бұл ыңғайлы әдіс, бірақ кейде деректерді қолмен жазу қажет. Бұл жұмыс кітабындағы ақпараттың қайталануын болдырмауға мүмкіндік береді.

Алдымызда екі ықтимал таңдаудан тұратын тізім жасау міндеті тұр делік: иә және жоқ. Тапсырманы орындау үшін қажет:

1. Тізім үшін ұяшықты басыңыз.
2. «Деректерді» ашыңыз, сонда бізге таныс «Деректерді тексеру» бөлімін табыңыз.
   

   

3. Қайтадан «Тізім» түрін таңдаңыз.
   

   

4. Мұнда сіз «Иә; Жоқ» дереккөз ретінде. Санау үшін нүктелі үтір арқылы ақпарат қолмен енгізілетінін көреміз.

OK түймесін басқаннан кейін бізде келесі нәтиже бар.

Содан кейін бағдарлама тиісті ұяшықта автоматты түрде ашылмалы мәзірді жасайды. Пайдаланушы қалқымалы тізімдегі элементтер ретінде көрсеткен барлық ақпарат. Бірнеше ұяшықтарда тізімді құру ережелері алдыңғыларға ұқсас, тек нүктелі үтірді пайдаланып ақпаратты қолмен көрсету керек екенін қоспағанда.

OFFSET функциясын пайдаланып ашылмалы тізім жасау

Классикалық әдіске қосымша функцияны қолдануға болады Диспозалыашылмалы мәзірлерді жасау үшін.

Парақты ашайық.

Ашылмалы тізім үшін функцияны пайдалану үшін келесі әрекеттерді орындау керек:

1. Болашақ тізімді орналастырғыңыз келетін қызығушылық ұяшығын таңдаңыз.
2. «Деректер» қойындысын және «Деректерді тексеру» терезесін ретімен ашыңыз.
   

   

3. «Тізімді» орнатыңыз. Бұл алдыңғы мысалдардағыдай орындалады. Соңында келесі формула қолданылады: =OFSET(A$2$;0;0;5). Біз оны аргумент ретінде пайдаланылатын ұяшықтар көрсетілген жерге енгіземіз.

Содан кейін бағдарлама жемістер тізімі бар мәзірді жасайды.

Бұл үшін синтаксис:

=OFFSET(анықтама,жолдың_ығысуы,бағанның_жылжыуы,[биіктігі],[ені])

Бұл функцияның 5 аргументі бар екенін көреміз. Біріншіден, ығыстырылатын бірінші ұяшық мекенжайы беріледі. Келесі екі аргумент қанша жолдар мен бағандарды ығыстыру керектігін көрсетеді. Біз туралы айтатын болсақ, Height аргументі 5-ке тең, себебі ол тізімнің биіктігін білдіреді. 

Деректерді алмастыратын Excel бағдарламасындағы ашылмалы тізім (+ OFFSET функциясын пайдалану арқылы)

Берілген жағдайда Диспозалы бекітілген диапазонда орналасқан қалқымалы мәзірді жасауға мүмкіндік берді. Бұл әдістің кемшілігі - элементті қосқаннан кейін формуланы өзіңіз өңдеуге тура келеді.

Жаңа ақпаратты енгізуге қолдау көрсететін динамикалық тізім жасау үшін сізге қажет:

1. Қызықты ұяшықты таңдаңыз.
2. «Деректер» қойындысын кеңейтіп, «Деректерді тексеру» түймесін басыңыз.
3. Ашылған терезеде «Тізім» элементін қайтадан таңдап, деректер көзі ретінде келесі формуланы көрсетіңіз: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. OK түймешігін басыңыз.

Бұл функцияны қамтиды COUNTIF, қанша ұяшық толтырылғанын бірден анықтау үшін (оның қолданылу саны әлдеқайда көп болса да, біз оны белгілі бір мақсат үшін ғана жазамыз).

Формуланың қалыпты жұмыс істеуі үшін формула жолында бос ұяшықтардың бар-жоғын қадағалау керек. Олар болмауы керек.

Басқа парақтың немесе Excel файлының деректері бар ашылмалы тізім

Басқа құжаттан немесе тіпті бір файлдағы парақтан ақпарат алу қажет болса, классикалық әдіс жұмыс істемейді. Ол үшін функция пайдаланылады ТӘУЕЛСІЗДІК, бұл басқа парақта немесе жалпы файлда орналасқан ұяшыққа сілтемені дұрыс пішімде енгізуге мүмкіндік береді. Сізге келесі әрекеттерді орындау қажет:

1. Тізімді орналастыратын ұяшықты белсендіріңіз.
2. Біз бұрыннан білетін терезені ашамыз. Біз басқа диапазондар үшін көздерді бұрын көрсеткен жерде, форматта формула көрсетілген =ЖАНА(“[List1.xlsx]1-парақ!$A$1:$A$9”). Әрине, List1 және Sheet1 орнына кітап және парақ атауларын сәйкесінше кірістіруге болады. 

Назар аударыңыз! Файл атауы шаршы жақшада беріледі. Бұл жағдайда Excel бағдарламасы қазіргі уақытта жабық файлды ақпарат көзі ретінде пайдалана алмайды.

Сондай-ақ, файл атауының өзі қажетті құжат тізім кірістірілетін қалтамен бір қалтада орналасқан жағдайда ғана мағыналы болатынын атап өткен жөн. Егер жоқ болса, онда сіз осы құжаттың мекен-жайын толық көрсетуіңіз керек.

Тәуелді ашылмалы тізімдерді жасау

Тәуелді тізім — мазмұнына пайдаланушының басқа тізімдегі таңдауы әсер ететін тізім. Біздің алдымызда үш диапазон бар, әрқайсысына атау берілген кесте бар делік.

Нәтижесі басқа тізімде таңдалған опция әсер ететін тізімдерді жасау үшін осы қадамдарды орындауыңыз керек.

1. Ауқым атаулары бар 1-ші тізімді жасаңыз.
   

   

2. Бастапқы кіру нүктесінде қажетті көрсеткіштер бір-бірден бөлектеледі.
   

   

3. Адам таңдаған өсімдік түріне байланысты 2-ші тізімді жасаңыз. Сонымен қатар, бірінші тізімде ағаштарды көрсетсеңіз, екінші тізімдегі ақпарат «емен, граб, каштан» және одан тыс болады. Мәліметтер көзінің енгізілген жеріне формуланы жазу керек =ЖАНА (E3). E3 – 1 диапазон атауын қамтитын ұяшық.=ЖАНА(E3). E3 – 1 тізім атауы бар ұяшық.

Қазір бәрі дайын.

Ашылмалы тізімнен бірнеше мәндерді қалай таңдауға болады?

Кейде тек бір мәнге артықшылық беру мүмкін емес, сондықтан біреуден көп таңдау керек. Содан кейін бет кодына макрос қосу керек. Alt + F11 пернелер тіркесімін пайдалану Visual Basic редакторын ашады. Және код сонда енгізіледі.

Жеке ішкі жұмыс парағы_өзгерту (ауқым ретіндегі мақсатты мән)

    Келесіде қатені жалғастыруда

    Егер қиылыспаса(Нысана, диапазон(«Е2:Е9»)) Ештеңе емес және мақсат емес.Ұяшықтар.Санау = 1 Сонда

        Application.EnableEvents = False

        Егер Len (Target.Offset (0, 1)) = 0 Содан кейін

            Target.Offset (0, 1) = Мақсат

        тағы

            Target.End (xlToRight) .Офсет (0, 1) = Мақсат

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

Ұяшықтардың мазмұны төменде көрсетілуі үшін редакторға келесі кодты енгіземіз.

Жеке ішкі жұмыс парағы_өзгерту (ауқым ретіндегі мақсатты мән)

    Келесіде қатені жалғастыруда

    Егер қиылыспаса(Нысана, диапазон(«Н2:К2»)) Ештеңе емес және мақсат емес.Ұяшықтар.Санау = 1 Сонда

        Application.EnableEvents = False

        Егер Len (Target.Offset (1, 0)) = 0 Содан кейін

            Target.Offset (1, 0) = Мақсат

        тағы

            Target.End (xlDown) .Offset (1, 0) = Мақсат

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Ақырында, бұл код бір ұяшыққа жазу үшін қолданылады.

Жеке ішкі жұмыс парағы_өзгерту (ауқым ретіндегі мақсатты мән)

    Келесіде қатені жалғастыруда

    Егер қиылыспаса(Нысана, диапазон(«C2:C5»)) Ештеңе емес және мақсат емес.Ұяшықтар.Санау = 1 Сонда

        Application.EnableEvents = False

        newVal = Мақсат

        Қолданба. Болдырмау

        oldval = Мақсат

        Егер Len (oldval) <> 0 және oldval <> newVal Содан кейін

            Target = Target & «,» & newVal

        тағы

            Мақсат = newVal

        End If

        Егер Len (newVal) = 0 болса, онда Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Ауқымдарды өңдеуге болады.

Іздеу арқылы ашылмалы тізімді қалай жасауға болады?

Бұл жағдайда бастапқыда тізімнің басқа түрін пайдалану керек. «Әзірлеуші» қойындысы ашылады, одан кейін «Кірістіру» - «ActiveX» элементін басу немесе түрту (экран түртсе) қажет. Оның комбо қорабы бар. Сізге бұл тізімді салу ұсынылады, содан кейін ол құжатқа қосылады.

Одан әрі ол сипаттар арқылы конфигурацияланады, мұнда ауқым ListFillRange опциясында көрсетілген. Пайдаланушы анықтайтын мән көрсетілетін ұяшық LinkedCell опциясы арқылы конфигурацияланады. Әрі қарай, сізге бірінші таңбаларды жазу керек, өйткені бағдарлама автоматты түрде ықтимал мәндерді ұсынады.

Деректерді автоматты түрде ауыстыруы бар ашылмалы тізім

Сондай-ақ, деректер диапазонға қосылғаннан кейін автоматты түрде ауыстырылатын функция бар. Мұны істеу оңай:

1. Болашақ тізім үшін ұяшықтар жинағын жасаңыз. Біздің жағдайда бұл түстер жиынтығы. Біз оны таңдаймыз.
   

   

2. Содан кейін оны кесте ретінде пішімдеу керек. Бір аттас түймені басып, кесте стилін таңдау керек.
   

   

   

Әрі қарай, «OK» түймесін басу арқылы осы ауқымды растау керек.

Алынған кестені таңдаймыз және оған А бағанының жоғарғы жағында орналасқан енгізу өрісі арқылы атау береміз.

Міне, кесте бар және оны ашылмалы тізім үшін негіз ретінде пайдалануға болады, ол үшін сізге қажет:

1. Тізім орналасқан ұяшықты таңдаңыз.
2. Деректерді тексеру тілқатысу терезесін ашыңыз.
   

   

3. Біз деректер түрін «Тізім» деп орнатамыз және мәндер ретінде = белгісі арқылы кестенің атын береміз.
   

   

   

Барлығы, ұяшық дайын, онда түстердің атаулары бізге бастапқыда қажет болғандай көрсетілген. Енді жаңа позицияларды соңғысынан кейін сәл төменірек орналасқан ұяшыққа жазу арқылы жай ғана қосуға болады.

Бұл кестенің артықшылығы, жаңа деректер қосылған кезде ауқым автоматты түрде артады. Тиісінше, бұл тізімді қосудың ең қолайлы жолы.

Ашылмалы тізімді қалай көшіруге болады?

Көшіру үшін Ctrl + C және Ctrl + V пернелер тіркесімін пайдалану жеткілікті. Сондықтан ашылмалы тізім пішімдеумен бірге көшіріледі. Пішімдеуді жою үшін «мәндер бойынша шарттар» опциясы орнатылған арнайы қоюды пайдалану керек (контекстік мәзірде бұл опция тізімді көшіргеннен кейін пайда болады).

Ашылмалы тізімі бар барлық ұяшықтарды таңдаңыз

Бұл тапсырманы орындау үшін «Табу және таңдау» тобындағы «Ұяшықтар тобын таңдау» функциясын пайдалану керек.

Осыдан кейін диалогтық терезе ашылады, онда «Деректерді тексеру» мәзірінде «Барлығы» және «Осылар бірдей» тармақтарын таңдау керек. Бірінші элемент барлық тізімдерді таңдайды, ал екіншісі белгілі бір тізімге ұқсастарды ғана таңдайды.