Excelda ochiladigan ro'yxatni qanday qilish kerak

Ochiladigan ro'yxat ma'lumotlar bilan ishlashni yanada qulayroq qilishga yordam beradigan juda foydali vositadir. Bu hujayrada bir vaqtning o'zida bir nechta qiymatlarni o'z ichiga olish imkonini beradi, ular bilan boshqa har qanday qiymatlar kabi ishlashingiz mumkin. Sizga kerak bo'lgan narsani tanlash uchun o'q belgisini bosing, shundan so'ng uXNUMXbuXNUMXbis qiymatlari ro'yxati ko'rsatiladi. Muayyan birini tanlagandan so'ng, hujayra avtomatik ravishda u bilan to'ldiriladi va formulalar uning asosida qayta hisoblab chiqiladi.

Excel ochiladigan menyuni yaratish uchun juda ko'p turli xil usullarni taqdim etadi va qo'shimcha ravishda ularni moslashuvchan tarzda sozlash imkonini beradi. Keling, ushbu usullarni batafsilroq tahlil qilaylik.

Ro'yxatni yaratish jarayoni

Qalqib chiquvchi menyuni yaratish uchun "Ma'lumotlar" - "Ma'lumotlarni tekshirish" yo'lidagi menyu bandlarini bosing. "Parametrlar" yorlig'ini topishingiz kerak bo'lgan dialog oynasi ochiladi va agar u ilgari ochilmagan bo'lsa, ustiga bosing. Unda juda ko'p sozlamalar mavjud, ammo "Ma'lumotlar turi" bandi biz uchun muhim. Barcha ma'nolardan "Ro'yxat" to'g'ri.

Qalqib chiquvchi ro'yxatga ma'lumotlarni kiritish usullari soni juda katta.

1. Xuddi shu dialog oynasining bir xil yorlig'ida joylashgan "Manba" maydonida nuqta-vergul bilan ajratilgan ro'yxat elementlarini mustaqil ko'rsatish.
   

   

2. Qiymatlarning dastlabki ko'rsatilishi. Manba maydonida kerakli ma'lumotlar mavjud bo'lgan diapazon mavjud.
   

   

3. Nomlangan diapazonni belgilash. Oldingi usulni takrorlaydigan usul, ammo diapazonni oldindan nomlash kerak.
   

   

Ushbu usullarning har biri kerakli natijani beradi. Haqiqiy vaziyatlarda ochiladigan ro'yxatlarni yaratish usullarini ko'rib chiqaylik.

Ro'yxatdagi ma'lumotlar asosida

Aytaylik, bizda turli xil mevalarning turlarini tavsiflovchi jadval mavjud.

Ushbu ma'lumotlar to'plami asosida ochiladigan menyuda ro'yxat yaratish uchun siz quyidagilarni bajarishingiz kerak:

1. Kelajakdagi ro'yxat uchun ajratilgan katakchani tanlang.
2. Tasmadagi Ma'lumotlar yorlig'ini toping. U erda biz "Ma'lumotlarni tekshirish" tugmasini bosing.
   

   

3. "Ma'lumotlar turi" bandini toping va qiymatni "Ro'yxat" ga o'tkazing.
   

   

4. "Manba" opsiyasini bildiruvchi maydonga kerakli diapazonni kiriting. E'tibor bering, mutlaq havolalar ro'yxatni nusxalashda ma'lumotlar o'zgarmasligi uchun ko'rsatilishi kerak.
   

Bundan tashqari, bir vaqtning o'zida bir nechta katakchalarda ro'yxatlarni yaratish funktsiyasi mavjud. Bunga erishish uchun ularning barchasini tanlab, yuqorida tavsiflangan amallarni bajarishingiz kerak. Shunga qaramay, siz mutlaq havolalar yozilganligiga ishonch hosil qilishingiz kerak. Agar manzilda ustun va satr nomlari yonida dollar belgisi bo'lmasa, u holda F4 tugmachasini bosish orqali ularni $ belgisi ustun va satr nomlari yonida bo'lguncha qo'shishingiz kerak.

Qo'lda ma'lumotlarni yozib olish bilan

Yuqoridagi vaziyatda ro'yxat kerakli diapazonni ajratib ko'rsatish orqali yozilgan. Bu qulay usul, lekin ba'zida ma'lumotlarni qo'lda yozib olish kerak bo'ladi. Bu ish kitobidagi ma'lumotlarning takrorlanishini oldini olishga imkon beradi.

Aytaylik, biz ikkita mumkin bo'lgan variantni o'z ichiga olgan ro'yxatni yaratish vazifasiga duch keldik: ha va yo'q. Vazifani bajarish uchun quyidagilar zarur:

1. Ro'yxat uchun katakchani bosing.
2. "Ma'lumotlar" ni oching va u erda bizga tanish bo'lgan "Ma'lumotlarni tekshirish" bo'limini toping.
   

   

3. Yana "Ro'yxat" turini tanlang.
   

   

4. Bu erda siz "Ha; Yo'q" manba sifatida. Biz ma'lumotni sanab o'tish uchun nuqta-vergul yordamida qo'lda kiritilishini ko'ramiz.

OK tugmasini bosgandan so'ng biz quyidagi natijaga ega bo'lamiz.

Keyinchalik, dastur avtomatik ravishda tegishli katakda ochiladigan menyuni yaratadi. Foydalanuvchi ochiladigan ro'yxatdagi elementlar sifatida ko'rsatgan barcha ma'lumotlar. Bir nechta katakchalarda ro'yxatni yaratish qoidalari avvalgilariga o'xshash, faqat nuqtali vergul yordamida ma'lumotni qo'lda ko'rsatishingiz kerak bo'lgan yagona istisno.

OFFSET funksiyasidan foydalanib ochiladigan ro'yxatni yaratish

Klassik usuldan tashqari, funktsiyadan foydalanish mumkin ISHLASHochiladigan menyularni yaratish uchun.

Keling, varaqni ochaylik.

Ochiladigan ro'yxat funksiyasidan foydalanish uchun siz quyidagilarni bajarishingiz kerak:

1. Kelajakdagi ro'yxatni joylashtirmoqchi bo'lgan qiziqish katakchasini tanlang.
2. "Ma'lumotlar" yorlig'ini va "Ma'lumotlarni tekshirish" oynasini ketma-ket oching.
   

   

3. "Ro'yxat" ni o'rnating. Bu avvalgi misollar bilan bir xil tarzda amalga oshiriladi. Nihoyat, quyidagi formula qo'llaniladi: =OFFSET(A$2$;0;0;5). Biz uni argument sifatida ishlatiladigan katakchalar ko'rsatilgan joyga kiritamiz.

Keyin dastur mevalar ro'yxati bilan menyu yaratadi.

Buning sintaksisi:

=OFFSET(ma'lumotnoma,satr_offset,ustun_offset,[balandlik],[kenglik])

Bu funksiya 5 ta argumentga ega ekanligini ko'ramiz. Birinchidan, ofset qilinadigan birinchi katak manzili beriladi. Keyingi ikkita argument qancha satr va ustunlarni ofset qilishni belgilaydi. Biz haqimizda gapiradigan bo'lsak, Balandlik argumenti 5 ga teng, chunki u ro'yxatning balandligini ifodalaydi. 

Excel-da ma'lumotlarni almashtirish bilan ochiladigan ro'yxat (+ OFFSET funktsiyasidan foydalangan holda)

Berilgan holatda ISHLASH belgilangan diapazonda joylashgan qalqib chiquvchi menyu yaratish imkonini berdi. Ushbu usulning kamchiligi shundaki, elementni qo'shgandan so'ng, formulani o'zingiz tahrirlashingiz kerak bo'ladi.

Yangi ma'lumotlarni kiritishni qo'llab-quvvatlaydigan dinamik ro'yxatni yaratish uchun sizga quyidagilar kerak:

1. Qiziqarli katakchani tanlang.
2. "Ma'lumotlar" yorlig'ini kengaytiring va "Ma'lumotlarni tekshirish" tugmasini bosing.
3. Ochilgan oynada yana "Ro'yxat" bandini tanlang va ma'lumotlar manbai sifatida quyidagi formulani belgilang: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. OK ni bosing.

Bu funktsiyani o'z ichiga oladi COUNTIF, zudlik bilan qancha hujayralar to'ldirilganligini aniqlash uchun (garchi u juda ko'p sonli foydalanishga ega bo'lsa-da, biz buni faqat ma'lum bir maqsad uchun yozamiz).

Formulaning normal ishlashi uchun formula yo'lida bo'sh hujayralar mavjudligini kuzatish kerak. Ular bo'lmasligi kerak.

Boshqa varaq yoki Excel fayli ma'lumotlari bilan ochiladigan ro'yxat

Agar siz boshqa hujjatdan yoki hatto bir xil fayldagi varaqdan ma'lumot olishingiz kerak bo'lsa, klassik usul ishlamaydi. Buning uchun funktsiyadan foydalaniladi BILVOSIT, bu sizga to'g'ri formatda boshqa varaqda joylashgan katakchaga havolani yoki umuman faylni kiritish imkonini beradi. Siz quyidagilarni qilishingiz kerak:

1. Ro'yxatni joylashtirgan katakchani faollashtiring.
2. Biz allaqachon bilgan oynani ochamiz. Biz ilgari boshqa diapazonlar uchun manbalarni ko'rsatgan joyda, formatda formula ko'rsatilgan =BILOSHIY(“[List1.xlsx]Vaq1!$A$1:$A$9”). Tabiiyki, List1 va Sheet1 o'rniga mos ravishda kitob va varaq nomlarini kiritishingiz mumkin. 

Diqqat! Fayl nomi kvadrat qavs ichida berilgan. Bunday holda, Excel hozirda yopilgan fayldan ma'lumot manbai sifatida foydalana olmaydi.

Shuni ham ta'kidlash kerakki, fayl nomining o'zi faqat kerakli hujjat ro'yxat qo'shiladigan papkada joylashgan bo'lsa, mantiqiy bo'ladi. Agar yo'q bo'lsa, unda siz ushbu hujjatning manzilini to'liq ko'rsatishingiz kerak.

Bog'liq ochiladigan menyularni yaratish

Bog'liq ro'yxat - bu foydalanuvchining boshqa ro'yxatdagi tanlovi mazmuniga ta'sir qiladigan ro'yxat. Faraz qilaylik, oldimizda uchta diapazonni o'z ichiga olgan stol ochildi, ularning har biriga nom berilgan.

Natijasiga boshqa roʻyxatda tanlangan variant taʼsir qiladigan roʻyxatlarni yaratish uchun quyidagi amallarni bajarishingiz kerak.

1. Diapazon nomlari bilan 1-roʻyxat yarating.
   

   

2. Manbaga kirish nuqtasida kerakli ko'rsatkichlar birma-bir ta'kidlanadi.
   

   

3. Shaxs tanlagan o'simlik turiga qarab 2-ro'yxatni tuzing. Shu bilan bir qatorda, agar siz birinchi ro'yxatda daraxtlarni ko'rsatsangiz, ikkinchi ro'yxatdagi ma'lumotlar "eman, shoxli, kashtan" va undan tashqarida bo'ladi. Ma'lumotlar manbasini kiritish joyiga formulani yozish kerak =BILOSHIY(E3). E3 – diapazon nomini o'z ichiga olgan katakcha 1.=BEVO'LOV(E3). E3 - ro'yxat nomi bilan katakcha 1.

Endi hammasi tayyor.

Ochiladigan ro'yxatdan bir nechta qiymatlarni qanday tanlash mumkin?

Ba'zan faqat bitta qiymatga ustunlik berish mumkin emas, shuning uchun bir nechtasini tanlash kerak. Keyin sahifa kodiga makro qo'shishingiz kerak. Alt + F11 tugmalar birikmasidan foydalanish Visual Basic muharririni ochadi. Va u erda kod kiritilgan.

Private Sub Worksheet_Change (ByVal maqsad diapazon sifatida)

    Xato davom ettiriladi

    Agar kesishmasa(Nishon, diapazon(«E2:E9»)) Hech narsa va maqsad emas. Hujayralar. Hisob = 1 U holda

        Application.EnableEvents = False

        Agar Len (Target.Offset (0, 1)) = 0 bo'lsa

            Maqsad.Ofset (0, 1) = Maqsad

        yana boshqa

            Target.End (xlToRight) .Ofset (0, 1) = Maqsad

        End if

        Target.ClearContents

        Application.EnableEvents = rost

    End if

End Sub 

Hujayralarning mazmuni quyida ko'rsatilishi uchun muharrirga quyidagi kodni kiritamiz.

Private Sub Worksheet_Change (ByVal maqsad diapazon sifatida)

    Xato davom ettiriladi

    Agar kesishmasa(Nishon, diapazon(«N2:K2»)) Hech narsa va maqsad emas. Hujayralar. Hisob = 1 U holda

        Application.EnableEvents = False

        Agar Len (Target.Offset (1, 0)) = 0 bo'lsa

            Maqsad.Ofset (1, 0) = Maqsad

        yana boshqa

            Target.End (xlDown) .Ofset (1, 0) = Maqsad

        End if

        Target.ClearContents

        Application.EnableEvents = rost

    End if

End Sub

Va nihoyat, bu kod bitta katakka yozish uchun ishlatiladi.

Private Sub Worksheet_Change (ByVal maqsad diapazon sifatida)

    Xato davom ettiriladi

    Agar kesishmasa(Nishon, diapazon(«C2:C5»)) Hech narsa va maqsad emas. Hujayralar. Hisob = 1 U holda

        Application.EnableEvents = False

        newVal = Maqsad

        Ilova. Bekor qilish

        oldval = Maqsad

        Agar Len (oldval) <> 0 Va oldval <> newVal Keyin

            Maqsad = Nishon & «,» & newVal

        yana boshqa

            Maqsad = newVal

        End if

        Agar Len (newVal) = 0 bo'lsa, Target.ClearContents

        Application.EnableEvents = rost

    End if

End Sub

Diapazonlarni tahrirlash mumkin.

Qidiruv bilan ochiladigan ro'yxatni qanday qilish mumkin?

Bunday holda, siz dastlab boshqa turdagi ro'yxatni ishlatishingiz kerak. "Ishlab chiquvchi" yorlig'i ochiladi, shundan so'ng siz "Qo'shish" - "ActiveX" elementini bosishingiz yoki bosishingiz kerak (agar ekran tegsa). U birlashtirilgan qutiga ega. Sizdan ushbu ro'yxatni chizish taklif qilinadi, shundan so'ng u hujjatga qo'shiladi.

Bundan tashqari, u xususiyatlar orqali sozlanadi, bu erda diapazon ListFillRange opsiyasida ko'rsatilgan. Foydalanuvchi tomonidan belgilangan qiymat ko'rsatiladigan katak LinkedCell opsiyasi yordamida sozlangan. Keyinchalik, siz faqat birinchi belgilarni yozishingiz kerak, chunki dastur avtomatik ravishda mumkin bo'lgan qiymatlarni taklif qiladi.

Avtomatik ma'lumotlarni almashtirish bilan ochiladigan ro'yxat

Ma'lumotlar diapazonga qo'shilgandan so'ng avtomatik ravishda almashtiriladigan funksiya ham mavjud. Buni qilish oson:

1. Kelajakdagi ro'yxat uchun hujayralar to'plamini yarating. Bizning holatlarimizda bu ranglar to'plami. Biz uni tanlaymiz.
   

   

2. Keyinchalik, uni jadval sifatida formatlash kerak. Xuddi shu nomdagi tugmani bosishingiz va jadval uslubini tanlashingiz kerak.
   

   

   

Keyinchalik, "OK" tugmasini bosib ushbu diapazonni tasdiqlashingiz kerak.

Olingan jadvalni tanlaymiz va A ustunining tepasida joylashgan kiritish maydoni orqali unga nom beramiz.

Hammasi shunday, jadval mavjud va u ochiladigan ro'yxat uchun asos sifatida ishlatilishi mumkin, buning uchun sizga kerak bo'ladi:

1. Ro'yxat joylashgan katakchani tanlang.
2. Ma'lumotlarni tekshirish dialog oynasini oching.
   

   

3. Biz ma'lumotlar turini "Ro'yxat" ga o'rnatamiz va qiymat sifatida = belgisi orqali jadval nomini beramiz.
   

   

   

Hamma narsa, hujayra tayyor va unda ranglarning nomlari ko'rsatilgan, chunki biz dastlab kerak edi. Endi siz yangi pozitsiyalarni oxirgisidan keyin bir oz pastroqda joylashgan katakka yozish orqali qo'shishingiz mumkin.

Jadvalning afzalligi shundaki, yangi ma'lumotlar qo'shilganda diapazon avtomatik ravishda ortadi. Shunga ko'ra, bu ro'yxatni qo'shishning eng qulay usuli.

Ochiladigan ro'yxatni qanday nusxalash mumkin?

Nusxa olish uchun Ctrl + C va Ctrl + V tugmalar birikmasidan foydalanish kifoya. Shunday qilib, ochiladigan ro'yxat formatlash bilan birga ko'chiriladi. Formatlashni olib tashlash uchun siz maxsus pastadan foydalanishingiz kerak (kontekst menyusida ushbu parametr ro'yxatni nusxalashdan keyin paydo bo'ladi), bu erda "qiymatlar bo'yicha shartlar" opsiyasi o'rnatiladi.

Ochiladigan ro'yxatni o'z ichiga olgan barcha hujayralarni tanlang

Ushbu vazifani bajarish uchun "Topish va tanlash" guruhidagi "Hujayralar guruhini tanlash" funksiyasidan foydalanishingiz kerak.

Shundan so'ng, dialog oynasi ochiladi, unda siz "Ma'lumotlarni tekshirish" menyusidagi "Hammasi" va "Bir xil" bandlarini tanlashingiz kerak. Birinchi element barcha ro'yxatlarni tanlaydi, ikkinchisi esa faqat ma'lum biriga o'xshashlarni tanlaydi.