Hujayralarni ma'lumotlar bilan to'ldirishda, odatda ma'lum bir qiymatlar ro'yxatiga kiritishni cheklash kerak. Masalan, foydalanuvchi bo'limning nomini kiritishi kerak bo'lgan hujayra mavjud, u qaerda ishlashini ko'rsatmoqda. Tashkilot bo'limlari ro'yxatini oldindan tuzish va foydalanuvchiga faqat ushbu ro'yxatdagi qiymatlarni tanlashga imkon berish mantiqan to'g'ri keladi. Ushbu yondashuv matn terish jarayonini tezlashtirishga va xato xatolar sonini kamaytirishga yordam beradi.
Ochiladigan ro'yxat bilan yaratilishi mumkin
Ushbu maqolada biz yaratamiz Ochiladigan ro'yxatma'lumotlar turi bilan () dan foydalanish Ro'yxat.
Ochiladigan ro'yxat turli xil shakllarda shakllanishi mumkin.
A. Eng oddiy ochiladigan ro'yxat - to'g'ridan-to'g'ri Manba maydoniga ro'yxat elementlarini kiritish
Aytaylik, kamerada B 1 yaratish kerak ochiladigan ro'yxat o'lchov birliklarini kiritish uchun. Yacheykani tanlang B 1 va qo'ng'iroq qiling Ma'lumotlarni tekshirish.
Agar dalada bo'lsa Manba vergul bilan ajratilgan o'lchov birliklarini ko'rsating dona; kg; kv.m; kub.m, keyin tanlov ushbu to'rtta qiymat bilan cheklanadi.
Endi nima bo'lganini ko'rib chiqamiz. Yacheykani tanlang B 1 ... Yacheyka tanlanganida, elementlarni tanlash uchun katakning o'ng tomonida to'rtburchak o'q tugmasi paydo bo'ladi ochiladigan ro'yxat.
kamchiliklar Ushbu yondashuv: ro'yxat elementlarini yo'qotish oson (masalan, katakchani o'z ichiga olgan qator yoki ustunni olib tashlash orqali) B
1
); juda ko'p sonli elementlarni kiritish qulay emas. Ushbu yondashuv kichik (3-5 qiymat) o'zgarmas ro'yxatlar uchun javob beradi.
Afzalligi: ro'yxatni yaratish tezligi.
B. Ro'yxat elementlarini diapazonga kiritish (ochiladigan ro'yxat bilan bir xil varaqda)
Ochiladigan ro'yxat uchun narsalar qatoriga joylashtirilishi mumkin eXCEL varag'ikeyin esa dalada Manbaushbu diapazonga mos yozuvlar bilan ta'minlash vositasi.
Deylik, ro'yxat elementlari dona; kg; kv.m; kub.mqator hujayralariga kiritilgan A 1: A 4 keyin maydon Manba tarkibida \u003d sheet1! $ A $ 1: $ A $ 4 mavjud
Afzalligi: elementlar ro'yxatining ravshanligi va o'zgartirishning qulayligi. Ushbu yondashuv kamdan-kam o'zgaradigan ro'yxatlarga mos keladi.
kamchiliklar: agar yangi narsalar qo'shilsa, siz intervalli ma'lumotnomasini qo'lda o'zgartirishingiz kerak. To'g'ri, manba sifatida siz darhol kengroq doirani belgilashingiz mumkin, masalan, A
1:
A
100
... Ammo, keyin ochiladigan ro'yxat bo'sh satrlarni o'z ichiga olishi mumkin (masalan, ba'zi elementlar o'chirilgan bo'lsa yoki ro'yxat yangi tuzilgan bo'lsa). Bo'sh satrlarni yo'q qilish uchun siz faylni saqlashingiz kerak.
Ikkinchi kamchilik: manbalar diapazoni xuddi shu varaqda bo'lishi kerak ochiladigan ro'yxatberi qoidalar uchun siz boshqa varaqlarga yoki kitoblarga havolalardan foydalana olmaysiz (bu EXCEL 2007 va undan oldingi versiyalar uchun amal qiladi).
Birinchidan, ikkinchi kamchilikdan xalos bo'lamiz - elementlar ro'yxatini joylashtiring ochiladigan ro'yxat boshqa varaqda.
B. Ro'yxat elementlarini diapazonga kiritish (istalgan varoqda)
Boshqa ish kitobidagi kataklar qatoriga ro'yxat elementlarini kiritish
Agar sizga ro'yxatni ochiladigan ro'yxat bilan boshqa kitobga (masalan, kitobga) o'tkazish kerak bo'lsa Manba.xlsx), keyin siz quyidagilarni bajarishingiz kerak:
- kitobda Manba.xlsx kerakli narsalarning ro'yxatini tuzish;
- kitobda Source.xlsx masalan, narsalar ro'yxatini o'z ichiga olgan kataklar qatori ListExternal;
- kataklarni ochiladigan ro'yxat bilan joylashtirmoqchi bo'lgan kitobni oching;
- kerakli kataklarni tanlang, asbobni chaqiring , dalada Manba belgilang \u003d INDIRECT ("[Source.xlsx] varaq1! ListExternal");
Boshqa ish kitobida joylashgan fayllar ro'yxati bilan ishlashda fayl Manba.xlsx ochiq va bitta papkada joylashgan bo'lishi kerak, aks holda siz faylga to'liq yo'lni ko'rsatishingiz kerak. Umuman olganda, boshqa varaqlarga havolalardan qochish yoki ulardan foydalanish yaxshiroqdir Shaxsiy makro kitob Personal.xlsx yoki Qo'shimchalar.
Agar fayldagi intervalni nomlashni istamasangiz Source.xlsx, keyin formulani \u003d ga o'zgartirish kerak INDIRECT ("[Source.xlsx] varaq1! $ A $ 1: $ A $ 4")
Maslahat:
Agar varaqda qoidalar mavjud bo'lgan kataklar ko'p bo'lsa Ma'lumotlarni tekshirish, keyin siz vositadan foydalanishingiz mumkin ( Uy / Topish va tanlash / Hujayralar guruhini tanlang). Variant Ma'lumotlarni tekshirish ushbu vosita ma'lumotlar tekshiruvi o'tkaziladigan kataklarni tanlashga imkon beradi (buyruq yordamida o'rnatiladi Ma'lumotlar / Ma'lumotlarni manipulyatsiyasi / Ma'lumotlarni tasdiqlash). Kalitni tanlaganingizda Hammasidan barcha bunday hujayralar ajratib ko'rsatiladi. Variantni tanlashda Bular xuddi shu faqat faol hujayra uchun bir xil ma'lumotlarni tekshirish qoidalari o'rnatilgan hujayralar tanlanadi.
Eslatma:
Agar ochiladigan ro'yxat 25-30 dan ortiq qiymatlarni o'z ichiga oladi, u bilan ishlash noqulay bo'ladi. Ochiladigan ro'yxat bir vaqtning o'zida faqat 8 ta elementni namoyish etadi, qolganlarini ko'rish uchun aylantirish satridan foydalanishingiz kerak, bu har doim ham qulay emas.
EXCEL shrift hajmini sozlashni ta'minlamaydi Ochiladigan ro'yxat... Ko'p sonli elementlar bilan elementlarni ro'yxatlash va elementlarning qo'shimcha tasnifidan foydalanish maqsadga muvofiqdir (ya'ni bitta ochiladigan ro'yxatni 2 yoki undan ko'p qismlarga bo'lish).
Masalan, 300 nafardan ortiq ishchilari bo'lgan xodimlar ro'yxati bilan samarali ishlash uchun avval uni alifbo tartibida saralash kerak. Keyin yarating ochiladigan ro'yxatalifbo harflarini o'z ichiga olgan. Ikkinchi ochiladigan ro'yxat faqat birinchi ro'yxat tomonidan tanlangan harf bilan boshlanadigan familiyalarni o'z ichiga olishi kerak. Bunday muammoni hal qilish uchun tuzilish yoki ishlatilishi mumkin.
Excel-dagi ochiladigan ro'yxat, ehtimol, ulardan biri hisoblanadi qulay usullar ma'lumotlar bilan ishlash. Siz ulardan ikkala shaklni to'ldirishda va asboblar paneli va hajmli jadvallarni yaratishda ham foydalanishingiz mumkin. Ochiladigan ro'yxatlar ko'pincha smartfonlarda, veb-saytlarda dasturlarda qo'llaniladi. Ular o'rtacha foydalanuvchi uchun intuitivdir.
Excelga ochiladigan ro'yxatlarning namunalari bilan faylni yuklab olish uchun quyidagi tugmani bosing:
Video darsligi
Ro'yxatdagi ma'lumotlar asosida Excelda ochiladigan ro'yxatni qanday yaratish kerak
Aytaylik, bizda mevalar ro'yxati bor:
Ochiladigan ro'yxatni yaratish uchun quyidagi amallarni bajarishimiz kerak:
- "Ga o'ting Ma'lumotlar "\u003d\u003e Bo'lim" Ma'lumotlar bilan ishlash "Asboblar panelida \u003d\u003e elementni tanlang" Ma'lumotlarni tekshirish “.
- Qalqib chiquvchi oynada “ Kirish qiymatlarini tekshirish "Ustida" Tanlovlar "Ma'lumotlar turida" ni tanlang Ro'yxat “:
- Dalada " Manba "Meva nomlari qatorini kiriting \u003d $ A $ 2: $ A $ 6 yoki faqat sichqoncha kursorini qiymat kiritish maydoniga qo'ying " Manba "Va keyin sichqoncha yordamida ma'lumotlar oralig'ini tanlang:
Agar siz bir vaqtning o'zida bir nechta kataklarda ochiladigan ro'yxatlarni yaratmoqchi bo'lsangiz, ularni yaratmoqchi bo'lgan barcha katakchalarni tanlang va keyin yuqoridagi amallarni bajaring. Hujayralar havolalari mutlaq ekanligiga ishonch hosil qilish muhimdir (masalan.) $ A $ 2 ) nisbiy emas (masalan, A2 yoki $ 2 yoki $ A2 ).
Ma'lumotlarni qo'lda kiritish orqali Excelda qanday qilib ochiladigan ro'yxatni yaratish
Yuqoridagi misolda bir qator katakchalarni ajratib ko'rsatish orqali ochiladigan ro'yxat uchun ma'lumotlar ro'yxatini kiritdik. Ushbu usulga qo'shimcha ravishda siz ochiladigan ro'yxatni yaratish uchun ma'lumotlarni kiritishingiz mumkin (ularni biron bir katakchada saqlash shart emas).
Masalan, ochiladigan menyuda ikkita "Ha" va "Yo'q" so'zlarini aks ettirmoqchimiz. Buning uchun bizga kerak:
- Biz ochiladigan ro'yxatni yaratmoqchi bo'lgan katakchani tanlang;
- "Ga o'ting Ma'lumotlar "\u003d\u003e Bo'lim" Ma'lumotlar bilan ishlash "Asboblar panelida \u003d\u003e elementni tanlang" Ma'lumotlarni tekshirish “:
- Qalqib chiquvchi oynada “ Kirish qiymatlarini tekshirish "Ustida" Tanlovlar "Ma'lumotlar turida" ni tanlang Ro'yxat “:
- Dalada " Manba"" Ha "qiymatini kiriting; Yo'q ".
- “Tugmasini bosing OK “
Shundan so'ng, tizim tanlangan katakchada ochiladigan ro'yxatni yaratadi. "Ro'yxatidagi barcha narsalar Manba ", Vergul bilan ajratilgan ochiladigan menyuning turli satrlarida aks etadi.
Agar bir vaqtning o'zida bir nechta kataklarda ochiladigan ro'yxatni yaratmoqchi bo'lsangiz, kerakli katakchalarni tanlang va yuqoridagi ko'rsatmalarga amal qiling.
OFFSET funktsiyasidan foydalangan holda Excelda ochiladigan ro'yxatni qanday yaratish kerak
Yuqorida tavsiflangan usullar bilan bir qatorda siz ochiladigan ro'yxatlarni yaratish uchun formuladan ham foydalanishingiz mumkin.
Masalan, bizda mevalar ro'yxati bo'lgan ro'yxat mavjud:
Formuladan foydalanib ochiladigan ro'yxatni tuzish uchun quyidagilarni bajarish kerak:
- Biz ochiladigan ro'yxatni yaratmoqchi bo'lgan katakchani tanlang;
- "Ga o'ting Ma'lumotlar "\u003d\u003e Bo'lim" Ma'lumotlar bilan ishlash "Asboblar panelida \u003d\u003e elementni tanlang" Ma'lumotlarni tekshirish “:
- Qalqib chiquvchi oynada “ Kirish qiymatlarini tekshirish "Ustida" Tanlovlar "Ma'lumotlar turida" ni tanlang Ro'yxat “:
- Dalada " Manba ”Formulani kiriting: \u003d OFFSET (A $ 2 $; 0; 0; 5)
- “Tugmasini bosing OK “
Tizim mevalar ro'yxati bilan ochiladigan ro'yxatni yaratadi.
Ushbu formula qanday ishlaydi?
Yuqoridagi misolda biz formuladan foydalanganmiz \u003d OFFSET (ref; line_offset; column_offset; [height]; [width]).
Ushbu funktsiya beshta argumentni o'z ichiga oladi. Dalilda “ havola"($ A $ 2 misolida) ofsetni qaysi katakdan boshlash kerakligini bildiradi. Bahslarda "Offset_by_strings”Va "Ofset_by_columns" (misolda "0" qiymati ko'rsatilgan) - ma'lumotlarni ko'rsatish uchun qancha qator / ustunlarni siljitish kerak. Dalilda “ [balandlik]"" 5 "ga o'rnatildi, bu hujayralar oralig'ining balandligini bildiradi. Argument “ [kenglik]”Biz aniq ko'rsatmayapmiz, chunki bizning misolimizda diapazon bitta ustundan iborat.
Ushbu formuladan foydalanib, tizim sizga $ A $ 2 katakdan boshlanadigan va 5 ta hujayradan iborat qatorlar qatorini ochiladigan ro'yxat ma'lumotlari sifatida qaytaradi.
Ma'lumotlarni almashtirish bilan Excelda ochiladigan ro'yxatni qanday tuzish mumkin (OFFSET funktsiyasidan foydalangan holda)
Agar siz ro'yxatni yaratish uchun yuqoridagi misoldagi formuladan foydalansangiz, demak siz ma'lum bir katakchada o'rnatilgan ma'lumotlar ro'yxatini yaratasiz. Agar siz biron bir qiymatni ro'yxat elementi sifatida qo'shmoqchi bo'lsangiz, formulani qo'lda sozlashingiz kerak. Quyida yangi ma'lumotlar avtomatik ravishda namoyish qilish uchun yuklanadigan dinamik ochiladigan ro'yxatni tuzishni o'rganasiz.
Ro'yxat yaratish uchun sizga quyidagilar kerak bo'ladi:
- Biz ochiladigan ro'yxatni yaratmoqchi bo'lgan katakchani tanlang;
- "Ga o'ting Ma'lumotlar "\u003d\u003e Bo'lim" Ma'lumotlar bilan ishlash "Asboblar panelida \u003d\u003e elementni tanlang" Ma'lumotlarni tekshirish “;
- Qalqib chiquvchi oynada “ Kirish qiymatlarini tekshirish "Ustida" Tanlovlar "Ma'lumotlar turida" ni tanlang Ro'yxat “;
- Dalada " Manba ”Formulani kiriting: \u003d OFFSET (A $ 2 $; 0; 0; COUNTIF ($ A $ 2: $ A $ 100; "<>”))
- “Tugmasini bosing OK “
Ushbu formulada, "[ balandlik] ”Ma'lumotlar bilan ro'yxatning balandligini bildiruvchi dalil sifatida ko'rsatamiz - berilgan diapazonda hisoblanadigan formulani A2: A100 bo'sh bo'lmagan hujayralar soni.
Eslatma: formulaning to'g'ri ishlashi uchun ochiladigan menyuda ko'rsatiladigan ma'lumotlar ro'yxatida bo'sh satrlar bo'lmasligi muhimdir.
Ma'lumotlarni avtomatik almashtirish bilan Excelda ochiladigan ro'yxatni qanday yaratish kerak
Siz yaratgan ochiladigan ro'yxatga yangi ma'lumotlar avtomatik ravishda yuklanishi uchun siz quyidagilarni bajarishingiz kerak:
- Ochiladigan ro'yxatda ko'rsatiladigan ma'lumotlar ro'yxatini yarating. Bizning holatlarimizda bu ranglar ro'yxati. Sichqonchaning chap tugmasi bilan ro'yxatni tanlang:
- Asboblar panelida “elementni bosing Jadval sifatida formatlash “:
- Ochilgan menyudan jadval uslubini tanlang:
- "Tugmachasini bosish orqali OK "Qalqib chiquvchi oynada tanlangan kataklar qatorini tasdiqlang:
- Keyin, ochiladigan ro'yxat uchun jadvallar oralig'ini tanlang va "A" ustunining yuqorisidagi chap chekkada nomlang:
Ma'lumotlar jadvali tayyor, endi biz ochiladigan ro'yxatni yaratishimiz mumkin. Bu quyidagilarni talab qiladi:
- Biz ro'yxat yaratmoqchi bo'lgan katakchani tanlang;
- "Ga o'ting Ma'lumotlar "\u003d\u003e Bo'lim" Ma'lumotlar bilan ishlash "Asboblar panelida \u003d\u003e elementni tanlang" Ma'lumotlarni tekshirish “:
- Qalqib chiquvchi oynada “ Kirish qiymatlarini tekshirish "Ustida" Tanlovlar "Ma'lumotlar turida" ni tanlang Ro'yxat “:
- Manba maydonida ko'rsating \u003d "Jadvalingiz nomi" ... Bizning holatlarimizda biz uni " Ro'yxat “:
- Bajarildi! Ochilgan ro'yxat tuzildi, u ko'rsatilgan jadvaldagi barcha ma'lumotlarni aks ettiradi:
- Ochilgan ro'yxatga yangi qiymat qo'shish uchun ma'lumotlar jadvalidan keyin quyidagi katakka ma'lumot qo'shish kifoya:
- Jadval avtomatik ravishda ma'lumotlar doirasini kengaytiradi. Ochilgan ro'yxat shunga mos ravishda jadvalning yangi qiymati bilan to'ldiriladi:
Excel-da ochiladigan ro'yxatni qanday nusxalash mumkin
Excel yaratilgan ochiladigan ro'yxatlarni nusxalash qobiliyatiga ega. Masalan, A1 katakchasida biz kataklar qatoriga nusxalashni xohlagan ochiladigan ro'yxat mavjud A2: A6 .
Ochilgan ro'yxatni joriy formatlash bilan nusxalash uchun:
- nusxa ko'chirmoqchi bo'lgan ochiladigan ro'yxat bilan katakchani chap tugmasini bosing;
- CTRL + C ;
- oraliqdagi kataklarni tanlang A2: A6 ochiladigan ro'yxatni kiritmoqchi bo'lgan joy;
- klaviatura yorlig'ini bosing CTRL + V .
Shunday qilib, siz ochiladigan sahifani saqlagan holda nusxa ko'chirasiz original format ro'yxat (rang, shrift va boshqalar). Agar siz formatni saqlamasdan ochiladigan ro'yxatni nusxalash / joylashtirishni xohlasangiz, unda:
- nusxa ko'chirmoqchi bo'lgan ochiladigan ro'yxat bilan katakchani chap tugmasini bosing;
- klaviatura yorlig'ini bosing CTRL + C ;
- ochiladigan ro'yxatni qo'shmoqchi bo'lgan katakchani tanlang;
- sichqonchaning o'ng tugmachasini bosing \u003d\u003e ochiladigan menyuga qo'ng'iroq qiling va “tugmasini bosing Maxsus joylashtiring “;
- Bo'limda paydo bo'lgan oynada Yapıştır "Elementni tanlang" qadriyatlar bo'yicha shartlar “:
- “Tugmasini bosing OK “
Shundan so'ng, Excel faqat ochilgan ro'yxatdagi ma'lumotlarni asl hujayraning formatlashini saqlamasdan nusxa ko'chiradi.
Excelda ochiladigan ro'yxatni o'z ichiga olgan barcha kataklarni qanday tanlash mumkin
Ba'zan, nechta hujayra borligini tushunish qiyin excel fayli ochiladigan ro'yxatlarni o'z ichiga oladi. Ularni namoyish qilishning oson yo'li mavjud. Buning uchun:
- "Tugmasini bosing uy "Asboblar panelida;
- “Tugmasini bosing Toping va belgilang "Va tanlang" Hujayralar guruhini tanlang “:
- Muloqot oynasida “tanlang Ma'lumotlarni tekshirish ". Ushbu maydonda siz " Hammasidan ”Va“ Xuddi shu narsa “. “Hammasidan "Varaqdagi barcha ochiladigan ro'yxatlarni tanlaydi. Mahsulot “ xuddi shu ”Tarkibiga ko'ra ochiladigan menyu ma'lumotlariga o'xshash ochiladigan ro'yxatlarni ko'rsatadi. Bizning holatimizda biz "ni tanlaymiz hammasidan “:
Ushbu maqolada biz qanday yaratishni ko'rib chiqamiz 2007 yil excel-dagi ochiladigan ro'yxat. Masalan, katakchaning ochiladigan ro'yxatidan berilgan qiymatlarni 1 dan 5 gacha tanlash kerak bo'lganda, misol keltiramiz. Ro'yxatni o'zi yarating va sichqonchaning chap tugmasi bilan tanlang. Bosing o'ng tugmasini bosing sichqonchani tanlangan maydonda tanlang va Range name elementini tanlang.
Ochilgan oynada, Ism maydoniga bizning ro'yxatimizning nomini kiriting, qo'ng'iroq qilaylik Qiymat... Ochilgan ro'yxatdagi Maydon maydonida tanlang Kitob (yoki ro'yxatni qo'llamoqchi bo'lgan varaqning raqami). OK tugmasini bosing.
Shuningdek, ro'yxat nomini ko'rsatish uchun mavjud ro'yxatni tanlang va katak nomi maydoniga kiring, ro'yxatga nom bering. Quyidagi rasmga qarang.
Ro'yxat tuzildi. Endi biz ushbu ro'yxatni katakchaga qo'llaymiz.
Ro'yxat bog'langan katakchani tanlang. Lentada Ma'lumotlar yorlig'iga va guruhga o'ting Ma'lumotlar bilan ishlash Ma'lumotlarni tekshirish tugmasini bosing. Keyingi oynada "Parameters" yorlig'ida "Data type" maydonida ro'yxatdagi elementni tanlang Ro'yxat.
Manba maydoniga teng belgini qo'ying va ro'yxatga tayinlangan ismni yozing. Ro'yxat "Qiymat" deb nomlangan. Shunga ko'ra, kirish quyidagi rasmda ko'rsatilgandek bo'lishi kerak.
OK tugmachasini bosing va endi biz ochiladigan ro'yxati bo'lgan katakchani oldik. Qanday ko'rinadi, quyidagi rasmga qarang. Biz katakchani bosganimizda, uning o'ng tomonida uchburchak pastga tushgan kvadratni ko'ramiz. Biz maydonni bosamiz va shu bilan ro'yxatni kengaytiramiz.
Ro'yxatni nomlamasdan yaratilishi mumkin. Ya'ni:
- ro'yxatni o'zi yaratish;
- lentadagi Ma'lumotlar yorlig'iga o'ting, tugmani bosing Ma'lumotlarni tekshirish;
- ochilgan oynada, Ma'lumotlar turi maydonidagi Parametrlar yorlig'ini tanlang Ro'yxat;
- manba maydonida ushbu maydonni faollashtirish uchun sichqonchaning chap tugmachasini bosing. Keyin, ro'yxatni tashkil etadigan kataklarni tanlang;
- oK tugmasini bosing.
Hammasi, 2007 yil excel-dagi ochiladigan ro'yxat tayyor.
Agar siz Excel-da jadvalni to'ldirsangiz va ustundagi ma'lumotlar ba'zida takrorlanishi mumkin bo'lsa, masalan, mahsulot nomi yoki xodimning ismi, keyin kirmaslik uchun kerakli parametr har safar bir marta ochiladigan ro'yxatni yaratish va undan qiymatni tanlash osonroq va osonroq.
Ushbu maqolada biz Excel jadvalidagi har xil turdagi ochiladigan ro'yxatlarni qanday qilishni ko'rib chiqamiz.
Oddiy ochiladigan ro'yxatni qanday yaratish kerak
Buning uchun A1: A7 katakchalarga ro'yxatda ko'rsatiladigan ma'lumotlarni kiriting. Endi biz ochiladigan ro'yxatni yaratadigan katakchani tanlang - B2.
"Ma'lumotlar" yorlig'iga o'ting va "Ma'lumotlarni tekshirish" tugmasini bosing.
"Ma'lumotlar turi" maydonidagi "Parametrlar" yorlig'ida "Ro'yxat" -ni tanlang. Siz Manba maydoniga qiymatlarni har xil usulda kiritishingiz mumkin:
1 - ro'yxat uchun qiymatlarni vergul bilan ajratilgan holda qo'lda kiriting;
2 - ochiladigan ro'yxat uchun ma'lumotlar kiritilgan kataklar oralig'ini belgilang;
3 - nomlari bo'lgan katakchalarni tanlang, ustiga o'ng tugmasini bosing va menyudan "Ismni belgilash" bandini tanlang.
B2 katakchani tanlang va "Manba" maydoniga "\u003d" qo'ying, so'ngra yaratilgan nomni yozing.
Shunday qilib, biz Excel-da oddiy ochiladigan ro'yxatni yaratdik.
Agar sizda ustun uchun sarlavha bo'lsa va har bir qatorni qiymatlar bilan to'ldirishingiz kerak bo'lsa, unda bitta katakchani emas, balki bir qator kataklarni tanlang - B2: B9. Keyin ochiladigan ro'yxatdan har bir katakchada kerakli qiymatni tanlash mumkin bo'ladi.
Ochilgan ro'yxatga - dinamik ro'yxatga qiymatlarni qo'shing
Bunday holda, biz kerakli diapazonga qiymatlarni qo'shamiz va ular avtomatik ravishda ochiladigan ro'yxatga qo'shiladi.
Hujayralar diapazonini tanlang - D1: D8, so'ngra "Uy" yorlig'ida "Jadval sifatida formatlash" tugmachasini bosing va istalgan uslubni tanlang.
Ma'lumotlarning joylashishini tasdiqlang va "Sarlavhali jadval" maydoniga belgi qo'ying.
Yuqorida biz jadvalning sarlavhasini - "Xodimlar" deb yozamiz va uni ma'lumotlar bilan to'ldiramiz.
Ochilgan ro'yxat mavjud bo'lgan katakchani tanlang va "Ma'lumotlarni tekshirish" tugmasini bosing. Keyingi oynada, "Manba" maydoniga quyidagilarni yozing: \u003d BOShQA ("Jadval1"). Menda bitta varaqda bitta jadval bor, shuning uchun men "Table1" yozaman, agar ikkinchisi bo'lsa - "Table2" va hk.
Endi ro'yxatimizga yangi xodim nomini qo'shaylik: Ira. Bu ochiladigan ro'yxatda paydo bo'ldi. Agar biz biron bir ismni jadvaldan olib tashlasak, u ham ro'yxatdan o'chiriladi.
Boshqa varaqdagi qiymatlar bilan ochiladigan ro'yxat
Agar ochiladigan ro'yxatlar bilan jadval bitta varaqda, ushbu ro'yxatlar uchun ma'lumotlar boshqasida bo'lsa, unda bu funktsiya bizga katta yordam beradi.
2-varaqda bitta katakchani yoki katakchalarni tanlang, so'ngra "Ma'lumotlarni tasdiqlash" tugmasini bosing.
1-varaqqa o'ting, kursorni "Manba" maydoniga qo'ying va kerakli kataklarni tanlang.
Endi siz 1-varaqqa ismlar qo'shishingiz mumkin, ular 2-sahifadagi ochiladigan ro'yxatlarga qo'shiladi.
Bog'liq ochiladigan ro'yxatlarni yarating
Deylik, bizda uchta diapazon bor: xodimlarning ismlari, familiyalari va otasining ismlari. Ularning har biri uchun siz ism berishingiz kerak. Ushbu diapazon katakchalarini tanlaymiz, ularni bo'shatish ham mumkin - vaqt o'tishi bilan ochiladigan ro'yxatda paydo bo'ladigan ma'lumotlarni qo'shish mumkin bo'ladi. Sichqonchaning o'ng tugmasi bilan ularni bosing va ro'yxatdan "Ismni belgilash" -ni tanlang.
Birinchisi "Ism", ikkinchisi - "Familiya", uchinchisi - "Ota" deb nomlanadi.
Keling, tayinlangan ismlar yoziladigan yana bir qatorni yarataylik. Keling, buni "Xodimlar" deb ataymiz.
Biz diapazonlarning nomlaridan iborat birinchi ochiladigan ro'yxatni tuzamiz. E1 katakchani tanlang va "Ma'lumotlar" yorlig'ida "Ma'lumotlarni tasdiqlash" -ni tanlang.
Ma'lumotlar turi maydonida Ro'yxat-ni tanlang, Manba maydonida yoki \u003d Xodimlarni kiriting yoki nomlangan kataklarni tanlang.
Birinchi ochiladigan ro'yxat tuzildi. Endi F2 katakchada biz birinchisiga bog'liq bo'lishi kerak bo'lgan ikkinchi ro'yxat tuzamiz. Agar birinchisida "Ism" ni tanlasak, ikkinchisida ismlar ro'yxati, agar "Familiya" ni tanlasak - familiyalar ro'yxati ko'rsatiladi.
Yacheykani tanlang va "Ma'lumotlarni tekshirish" tugmasini bosing. "Ma'lumotlar turi" maydoniga "Ro'yxat" -ni tanlang, manba maydoniga quyidagilarni yozing: \u003d INDIRECT ($ E $ 1). Bu erda E1 birinchi ochiladigan ro'yxat bo'lgan katakcha.
Ushbu printsipga ko'ra, siz bog'liq bo'lgan ochiladigan ro'yxatlarni tuzishingiz mumkin.
Agar kelajakda siz nom berilgan intervalgacha qiymatlarni kiritishingiz kerak bo'lsa, masalan, "Familiya". Formulalar yorlig'iga o'ting va ismlar menejerini bosing. Endi diapazon nomidan "Familiya" ni tanlang va pastdagi oxirgi C3 katakchaning o'rniga C10 yozing. Belgilash belgisini bosing. Shundan so'ng, diapazon ko'payadi va unga avtomatik ravishda ochiladigan ro'yxatda paydo bo'ladigan ma'lumotlarni qo'shish mumkin bo'ladi.
Endi siz Excel-da ochiladigan ro'yxatni qanday qilishni bilasiz.
Bir vaqtning o'zida bir nechta kataklardan tashkil topgan ochiladigan ro'yxatni qanday yaratish mumkin (aytaylik, bu nom xarajat bilan)
Rahmat, barchasi yaxshi ishladi.
Boshqa varaqdagi qiymatlar bilan ochiladigan ro'yxat ishlamayapti, chunki ma'lumotlar tekshiruvi ochiq bo'lgan oyna boshqa oynalar bilan ishlashga imkon bermaydi, ayniqsa boshqa varaq bilan!
Bog'liq tomchi sizga foydalanuvchilar tez-tez maqtaydigan hiyla-nayrangni bajarishga imkon beradi excel shablonlari... Ishni osonroq va tezroq bajaradigan hiyla-nayrang. Shakllaringizni qulay va yoqimli his qilish uchun hiyla-nayrang.
Excel yacheykasida qaram ochiladigan ro'yxatni yaratish misoli
Sotuvchilar tovarlarga buyurtma bergan hujjatlarni to'ldirish uchun qulay shaklni yaratish uchun qaram ochiladigan ro'yxatdan foydalanish misoli. Butun assortimentdan ular sotmoqchi bo'lgan mahsulotlarni tanlashlari kerak edi.
Har bir sotuvchi avval mahsulot guruhini, so'ngra ushbu guruhdan ma'lum mahsulotni aniqladi. Shaklda guruhning to'liq nomi va mahsulotning aniq ko'rsatkichi bo'lishi kerak. Buni qo'lda yozish juda ko'p vaqtni talab qiladigan (va bezovta qiladigan) bo'lishi sababli, men juda tez va oson echim topdim - 2 ta pastga tushish.
Birinchisi, barcha mahsulot toifalarining ro'yxati, ikkinchisi tanlangan toifadagi barcha mahsulotlarning ro'yxati edi. Shunday qilib, men avvalgi ro'yxatdagi tanlovga bog'liq ravishda ochiladigan sahifani yaratdim (bu erda ikkita qaramlik ochilishini yaratish bo'yicha qo'llanma mavjud).
Shablon foydalanuvchisi bir xil natijaga erishmoqchi uy byudjeti bu erda siz toifaga va xarajatlarning kichik toifasiga muhtojsiz. Ma'lumotlarning namunasi quyidagi rasmda keltirilgan:
Masalan, agar biz O'yin-kulgi toifasini tanlasak, unda pastki toifalar ro'yxatida quyidagilar bo'lishi kerak: Kino, Teatr, Hovuz. Juda yuqori tezkor qaroragar sizning uyingiz byudjetida siz ko'proq tafsilotlarni tahlil qilmoqchi bo'lsangiz.
Excelga bog'liq ochiladigan ro'yxatdagi toifalar va pastki toifalar ro'yxati
Men o'zim taklif qilayotgan uy byudjetida o'zimni faqat toifalar bilan cheklab qo'yganimni tan olaman, chunki men uchun xarajatlarning bunday taqsimoti juda etarli (xarajatlar / daromadlar nomi subkategiya deb hisoblanadi). Ammo, agar siz ularni pastki toifalarga ajratishingiz kerak bo'lsa, unda men quyida tasvirlab beradigan usul idealdir. Foydalanishdan bexabar bo'ling!
Va yakuniy natija quyidagicha ko'rinadi:
Quyi toifalar ro'yxatiga bog'liq bo'lgan ochiladigan ro'yxat
Bunga erishish uchun biz bitta ochiladigan ro'yxatni yaratgandan ko'ra bir oz boshqacha ma'lumotlar jadvalini tuzishimiz kerak. Jadval shunday bo'lishi kerak (G2: H15 oralig'i):
Excel ish jadvallari
Ushbu jadvalda siz toifani va uning yoniga uning kichik toifalarini kiritishingiz kerak. Kategoriya nomi pastki toifalar qancha bo'lsa, shuncha marta takrorlanishi kerak. Ma'lumotlar toifadagi ustunlar bo'yicha saralanishi juda muhimdir. Keyinchalik formulani yozganimizda, bu juda muhim bo'ladi.
Jadvallarni birinchi rasmdagi rasmlardan ham foydalanishingiz mumkin. Albatta, formulalar boshqacha bo'lar edi. Bir marta ham men tarmoqda bunday echimni topdim, lekin menga yoqmadi, chunki ro'yxatning aniq uzunligi bor edi: demak, ba'zida ro'yxat bo'sh maydonlarni o'z ichiga olgan, ba'zida esa u barcha elementlarni namoyish qilmagan. Albatta, men bu cheklovdan qochib qutula olaman, lekin o'zimning qarorim menga ko'proq yoqishini tan olaman, shuning uchun men hech qachon bu echimga qaytmaganman.
Xo'sh. Endi, o'z navbatida, men qaram bo'lgan ochiladigan ro'yxatni yaratish bosqichlarini tasvirlayman.
1. Hujayralar diapazonlari nomlari
Bu ixtiyoriy qadam, usiz biz buni hech qanday muammosiz hal qila olamiz. Biroq, men ismlardan foydalanishni yaxshi ko'raman, chunki ular formulani yozishni va o'qishni ancha osonlashtiradi.
Keling, ikkita diapazonni nomlaylik. Barcha toifalar ro'yxati va toifalarning ishchi ro'yxati. Ular A3: A5 (birinchi rasmdagi yashil jadvaldagi toifalar ro'yxati) va G3: G15 (binafsha rangdagi varaqdagi takrorlanadigan toifalar ro'yxati) diapazonlari bo'ladi.
Kategoriyalar ro'yxatini nomlash uchun:
- A3: A5 oralig'ini tanlang.
- Ism maydoniga (formulalar qatorining chap qismidagi maydon) toifadagi ismni kiriting.
- Enter tugmasi bilan tasdiqlang.
Ish jadvalining G3: G15 toifalari qatori uchun xuddi shu amalni bajaring, ularni "Work_List" deb atashingiz mumkin. Ushbu diapazondan formulada foydalanamiz.
2. Kategoriya uchun ochiladigan ro'yxat yarating
Bu oddiy bo'ladi:
- Ro'yxatni joylashtirmoqchi bo'lgan katakchani tanlang. Mening vaziyatimda bu A12.
- DATA menyusidan Ma'lumotlarni tekshirish vositasini tanlang. "Kirish qiymatlarini tasdiqlash" oynasi paydo bo'ladi.
- Ma'lumot turi sifatida ro'yxatni tanlang.
- Manba uchun quyidagilarni kiriting: \u003d Turkum (quyidagi rasm).
- OK bilan tasdiqlang.
Natija quyidagicha:
Kategoriya uchun ochiladigan ro'yxat.
3. Subkategoriyaga bog'liq bo'lgan ochiladigan ro'yxatni yarating
Endi qiziqarli bo'ladi. Biz ro'yxatlarni qanday yaratishni bilamiz - biz buni faqat toifalar uchun qildik. Faqat bitta savol: "Excelga qanday qilib faqat ma'lum bir toifaga tegishli qiymatlarni tanlashini aytaman?" Ehtimol taxmin qilishingiz mumkin, men bu erda ishchi varaqdan va, albatta, formulalardan foydalanaman.
Keling, qanday qilib bilganimizdan boshlaymiz, ya'ni B12 hujayrasida ochiladigan ro'yxatni yaratish. Shunday qilib, ushbu katakchani tanlang va Ma'lumotlar / Ma'lumotlarni tasdiqlash-ni bosing va Ma'lumotlar turi - Ro'yxat.
Ro'yxat manbasiga quyidagi formulani kiriting:
"Tasdiqlashni tekshirish" oynasining ko'rinishi:
Bog'liq ochiladigan ro'yxatdagi pastki toifadagi kirish qiymatlarini tasdiqlang
Ko'rib turganingizdek, qaram ro'yxatning barcha hiyla-nayranglari OFFSET funktsiyasidan foydalanishdir. Xo'sh, deyarli barchasi. Bunga SEARCH va COUNTIF funktsiyalari yordam beradi. OFFSET funktsiyasi intervallarni dinamik ravishda aniqlashga imkon beradi. Birinchidan, diapazon siljishi boshlanishi kerak bo'lgan katakchani aniqlaymiz va keyingi argumentlarda uning o'lchamini aniqlaymiz.
Bizning misolimizda diapazon ishchi varaqdagi Subkategory ustunidan o'tadi (G2: H15). Biz H2 katakchadan harakat qilishni boshlaymiz, bu ham bizning funktsiyamiz uchun birinchi dalil. Formulada H2 katak mutlaq ma'lumotnoma sifatida yozilgan edi, chunki men ko'p kataklarda ochiladigan ro'yxatni ishlatamiz.
Ish sahifasi Kategoriya bo'yicha tartiblanganligi sababli, ochiladigan manbaning manbai bo'lishi kerak bo'lgan diapazon tanlangan toifa birinchi paydo bo'lgan joyda boshlanadi. Masalan, oziq-ovqat toifasi uchun biz H6: H11 diapazonini, transport uchun H12: H15 diapazonini va boshqalarni namoyish qilmoqchimiz, e'tibor bering, biz doimo H ustuni bo'ylab harakatlanamiz va o'zgaradigan yagona narsa bu diapazonning boshlanishi va uning balandligi ( ya'ni ro'yxatdagi narsalar soni).
Diapazonning boshi H2 katakchaga nisbatan toifadagi ustunda birinchi duch kelgan toifadagi pozitsiya raqamiga qadar (son bo'yicha) shuncha katakka ko'chiriladi. Buni misol bilan tushunish osonroq bo'ladi: "Oziqlanish" toifasi uchun oraliq H2 katakka nisbatan 4 ta katakka pastga siljitildi (4-katakdan H2 dan boshlanadi). Subcategory ustunining 4-katagida (sarlavhani o'z ichiga olmaydi, chunki biz Work_List nomli qator haqida gapiramiz), Oziq-ovqat so'zi mavjud (uning birinchi paydo bo'lishi). Ushbu faktdan biz diapazonning boshlanishini aniqlash uchun foydalanamiz. Buning uchun SEARCH funktsiyasi (OFFSET funktsiyasining ikkinchi argumenti sifatida kiritilgan) bizga xizmat qiladi:
Diapazon balandligi COUNTIF funktsiyasi bilan belgilanadi. U toifadagi barcha takrorlanishlarni, ya'ni Oziqlanish so'zini sanaydi. Ushbu so'z necha marta uchraydi, bizning doiramizda qancha pozitsiya bo'ladi. Diapazondagi pozitsiyalar soni uning balandligi. Mana funktsiya:
Albatta, ikkala funktsiya ham yuqorida tavsiflangan OFFSET funktsiyasiga kiritilgan. Shuningdek, MATCH va COUNTIF da Work_List nomli qatorga havola mavjudligini unutmang. Avval aytib o'tganimdek, oraliq nomlarini ishlatish shart emas, shunchaki $ H3: $ H15 ni kiritishingiz mumkin. Ammo formulada diapazon nomlarini ishlatish uni o'qishni soddalashtiradi va osonlashtiradi.
Hammasi shu:
Excel-ga bog'liq bo'lgan ochiladigan ro'yxatning namunasini yuklab oling
Bitta formulalar juda oddiy emas, lekin bu ishni osonlashtiradi va ma'lumotlarni kiritishdagi xatolardan himoya qiladi!
Ko'pincha, ba'zi ma'lumotlarni to'ldiradigan yoki shunchaki jadvalni to'ldiradiganlar takrorlanadigan ma'lumotlarni to'ldirishlari kerak. Xuddi shu narsani to'sib qo'ymaslik uchun, ushbu holatlar uchun Excelda bunday vosita mavjud - ochiladigan ro'yxat. Buni qanday qilishni ko'ring
Biz buni bosqichma-bosqich qilamiz
Qanday ishlaydi - siz oldindan ro'yxat tuzasiz, so'ngra ma'lum maydonlarda sichqoncha yordamida kerakli qiymatni tanlang. Vaqt va asablarni tejaydi.
Bu shunchaki amalga oshiriladi:
- Birinchidan, ma'lumotlar ro'yxatini o'zi shakllantiring.
- Ma'lumotlarni to'ldirishni rejalashtirgan maydonlarni belgilang.
- Keyin Ma'lumotlar yorlig'iga o'ting, Ma'lumotlarni tasdiqlash buyrug'i - Ma'lumotlar turi (Ro'yxat), Manba maydonida qatorni qo'ying.
Agar siz ro'yxatga yangi ma'lumotlarni qo'shishni rejalashtirmoqchi bo'lsangiz, diapazonga ro'yxat ostidagi ma'lum bir katakchalar kiritilsin.
Shu bilan bir qatorda siz diapazonni emas, balki diapazon nomini belgilaysiz. Bunday holda, siz ro'yxatda ko'rsatilganidan ko'proq ro'yxatni ko'paytirish ehtimolidan xalos bo'lasiz. Siz shunchaki oraliqda to'plagan yangi qiymatlar, albatta, unga kiritilganligiga ishonch hosil qilishingiz kerak.
- "Bo'sh katakchalarga e'tibor bermaslik" katagiga belgi qo'ying.
Endi buyruq qo'llanilgan katakchani tanlaganingizda, o'ng tomonda o'q tugmasi paydo bo'ladi.
Nuance - bu usul bilan siz faqatgina ro'yxatdagi katakchaga qiymatlarni kiritishingiz mumkin.
Nuances
Ochilish katakchaning xususiyati. Va shuning uchun, agar siz boshqa katakchani nusxa ko'chirsangiz va uni ochiladigan ro'yxatiga qo'shsangiz, hujayraning xususiyatlari almashtiriladi va ochiladigan ro'yxat yo'qoladi. Shunga ko'ra, agar sizga boshqa katakchada bir xil ochiladigan ro'yxat kerak bo'lsa, siz shunchaki katakchani nusxalashingiz va kerakli manzilga qo'yishingiz mumkin (yoki maxsus pasta orqali "qiymatlar bo'yicha shartlarni" nusxalashingiz kerak).
Maydonlarni to'ldirgandan so'ng, agar siz endi hech narsa qilishni rejalashtirmasangiz, ochiladigan ro'yxat funktsiyasini o'chirib tashlashingiz mumkin (o'q hujayralar bilan ishlashda ingl. U shu tarzda amalga oshiriladi - ochiladigan ro'yxat endi kerak bo'lmagan joyda, xuddi shu buyruq belgilanadi, ammo "Hammasini tozalash" tugmachasini bosing. Barcha ma'lumotlar saqlanadi.
Ikkinchi yo'l
Agar siz ikki nusxadagi ma'lumotlarni to'ldirmasangiz, lekin anketa kabi biror narsa qilishni xohlasangiz, chunki kombinatsiyalangan qutilar o'zboshimchalik bilan joylashtirilgan jadval, ochiladigan ro'yxatni yaratishning boshqa usulidan foydalanish yaxshiroqdir.
Buning uchun Tuzuvchi yorlig'iga o'ting (Excel 2007-2010). Ushbu yorliq sukut bo'yicha ko'rsatilmaydi. Uni ochish uchun Options (Office / Excel Options / General / lentadagi "Developer" yorlig'ini ko'rsating) ga o'tishingiz kerak.
- Ishlab chiquvchi yorlig'ida form elementlari joylashgan Qo'shish tugmasi mavjud. Bizga birlashtirilgan quti kerak.
- Sichqoncha yordamida tugma sxemasini chizib oling, so'ngra maydonni o'ng tugmasini bosing va "Format ob'ekti" ni tanlang.
- Birinchi maydonda oraliqni tanlang.
- Ikkinchi maydonda elementning seriya raqami qo'yiladigan katakchani qo'yishingiz mumkin. Bu keyinchalik ma'lumotlarni qayta ishlashda foydali bo'lishi mumkin.
- Ko'rsatiladigan narsalar ro'yxatini ham taqdim etishingiz mumkin. IN oldingi yo'l faqat 8 ta tanlov elementi avtomatik ravishda beriladi, bu erda sozlash qiyinroq bo'lsa ham, ko'proq foyda bor.
Keyin "Ok" tugmachasini bosing va undan foydalaning.
"Ko'zlar qo'rqadi, lekin qo'llar qilyapti"
