Mundarija
Excelda o'lchamini o'zgartirish mumkin bo'lgan, ya'ni ish jarayonida qatorlar (ustunlar) soni ortishi yoki kamayishi mumkin bo'lgan ma'lumotlarga ega jadvallar bormi? Agar stol o'lchamlari "suzuvchi" bo'lsa, siz doimo ushbu daqiqani kuzatib borishingiz va uni tuzatishingiz kerak bo'ladi:
- jadvalimizga havola qilingan hisobot formulalaridagi havolalar
- jadvalimizga muvofiq tuzilgan pivot jadvallarning dastlabki diapazonlari
- jadvalimizga muvofiq tuzilgan diagrammalarning dastlabki diapazonlari
- jadvalimizni ma'lumotlar manbai sifatida ishlatadigan ochiladigan menyular uchun diapazonlar
Bularning barchasi zerikishingizga yo'l qo'ymaydi 😉
Dinamik "kauchuk" diapazonni yaratish ancha qulayroq va to'g'ri bo'ladi, uning hajmi avtomatik ravishda ma'lumotlarning satrlari va ustunlari soniga mos keladi. Buni amalga oshirish uchun bir necha usullar mavjud.
1-usul. Aqlli stol
Hujayralar diapazonini belgilang va yorliqdan tanlang Uy – Jadval sifatida formatlash (Uy – Jadval sifatida formatlash):
Agar sizga yon ta'sir sifatida stolga qo'shilgan chiziqli dizayn kerak bo'lmasa, uni paydo bo'lgan yorliqda o'chirib qo'yishingiz mumkin. Konstruktor (dizayn). Shu tarzda yaratilgan har bir jadval yorliqning o'sha joyida qulayroq bilan almashtirilishi mumkin bo'lgan nom oladi. Konstruktor (dizayn) dalada Jadval nomi (Jadval nomi).
Endi biz “aqlli stol”imizga dinamik havolalardan foydalanishimiz mumkin:
- 1 stol – sarlavha qatoridan tashqari butun jadvalga havola (A2:D5)
- 1-jadval[#Hammasi] – butun jadvalga havola (A1:D5)
- 1-jadval[Piter] – birinchi katak sarlavhasisiz diapazon-ustunga havola (C2:C5)
- 1-jadval[#Headers] - ustunlar nomlari bilan "sarlavha" ga havola (A1: D1)
Bunday havolalar formulalarda juda yaxshi ishlaydi, masalan:
= Jami (1-jadval[Moskva]) - "Moskva" ustuni uchun summani hisoblash
or
=VPR(F5;1 stol;3;0) – F5 yacheykasidan oyni jadvaldan qidiring va u uchun Sankt-Peterburg summasini chiqaring (VLOOKUP nima?)
Bunday havolalardan pivot jadvallarini yaratishda yorliqda tanlash orqali muvaffaqiyatli foydalanish mumkin Insert - Pivot Table (Qo'shish - Pivot Table) va ma'lumotlar manbai sifatida aqlli jadval nomini kiritish:
Agar siz bunday jadvalning bir qismini (masalan, birinchi ikkita ustun) tanlasangiz va har qanday turdagi diagramma yaratsangiz, yangi qatorlarni qo'shganda ular avtomatik ravishda diagrammaga qo'shiladi.
Ochiladigan ro'yxatlarni yaratishda aqlli jadval elementlariga to'g'ridan-to'g'ri havolalarni ishlatib bo'lmaydi, ammo siz taktik hiyla yordamida ushbu cheklovni osongina engishingiz mumkin - funktsiyadan foydalaning. BILVOSIT (BOSHQA), bu matnni havolaga aylantiradi:
Bular. matn qatori ko'rinishidagi aqlli jadvalga havola (tirnoq ichida!) to'liq havolaga aylanadi va ochiladigan ro'yxat odatda uni qabul qiladi.
2-usul: Dinamik nomli diapazon
Agar ma'lumotlaringizni aqlli jadvalga aylantirish biron sababga ko'ra istalmagan bo'lsa, unda siz biroz murakkabroq, ammo ancha nozik va ko'p qirrali usuldan foydalanishingiz mumkin - Excelda bizning jadvalimizga tegishli dinamik nomli diapazonni yarating. Keyin, aqlli jadvalda bo'lgani kabi, yaratilgan diapazon nomini istalgan formulalar, hisobotlar, diagrammalar va hokazolarda erkin ishlatishingiz mumkin. Keling, oddiy misoldan boshlaylik:
masala: shaharlar roʻyxatiga havola qiladigan dinamik nomli diapazonni yarating va yangi shaharlarni qoʻshganda yoki ularni oʻchirib tashlaganda avtomatik ravishda kichrayadi va kichrayadi.
Bizga har qanday versiyada mavjud bo'lgan ikkita o'rnatilgan Excel funktsiyasi kerak bo'ladi - POICPOZ (MATCH) diapazonning oxirgi katakchasini aniqlash uchun va INDEKS (INDEKS) dinamik havola yaratish uchun.
MATCH yordamida oxirgi katakchani topish
MATCH(qidiruv_qiymati, diapazon, moslik_turi) – berilgan qiymatni diapazonda (satr yoki ustun) qidiradigan va u topilgan katakning tartib raqamini qaytaruvchi funksiya. Masalan, MATCH(“Mart”;A1:A5;0) formulasi natijada 4 raqamini qaytaradi, chunki “Mart” so‘zi A1:A5 ustunidagi to‘rtinchi katakda joylashgan. Match_Type = 0 funksiyasining oxirgi argumenti biz aniq moslikni qidirayotganimizni bildiradi. Agar ushbu argument ko'rsatilmagan bo'lsa, u holda funktsiya eng yaqin eng kichik qiymatni qidirish rejimiga o'tadi - aynan shu narsa bizning massivimizdagi oxirgi band qilingan katakchani topish uchun muvaffaqiyatli ishlatilishi mumkin.
Hiylaning mohiyati oddiy. MATCH yuqoridan pastgacha bo'lgan diapazondagi hujayralarni qidiradi va nazariy jihatdan berilgan qiymatga eng yaqin eng kichik qiymatni topganda to'xtashi kerak. Agar siz jadvalda mavjud bo'lgan qiymatdan aniq kattaroq qiymatni kerakli qiymat sifatida belgilasangiz, MATCH jadvalning eng oxiriga etib boradi, hech narsa topa olmaydi va oxirgi to'ldirilgan katakning tartib raqamini beradi. Va bizga kerak!
Agar bizning massivimizda faqat raqamlar bo'lsa, biz kerakli qiymat sifatida raqamni belgilashimiz mumkin, bu jadvaldagi har qanday qiymatdan kattaroqdir:
Kafolat uchun siz 9E + 307 raqamidan foydalanishingiz mumkin (9 ning kuchiga 10 marta 307, ya'ni 9 nol bilan 307) - Excel printsipial ravishda ishlashi mumkin bo'lgan maksimal raqam.
Agar bizning ustunimizda matn qiymatlari mavjud bo'lsa, unda mumkin bo'lgan eng katta raqamning ekvivalenti sifatida siz REPEAT ("i", 255) qurilishini kiritishingiz mumkin - 255 harfdan iborat "i" matn qatori - oxirgi harf Alifbo. Excel qidiruvda aslida belgilar kodlarini taqqoslaganligi sababli, jadvalimizdagi har qanday matn texnik jihatdan shunday uzun "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" qatoridan "kichikroq" bo'ladi:
INDEX yordamida havola yarating
Endi biz jadvaldagi oxirgi bo'sh bo'lmagan elementning o'rnini bilganimizdan so'ng, u bizning butun diapazonimizga havolani yaratish uchun qoladi. Buning uchun biz funktsiyadan foydalanamiz:
INDEX(diapazon; satr_raqami; ustun_raqami)
U katak tarkibini qator va ustun raqamlari bo'yicha beradi, ya'ni, oldingi usuldagi shaharlar va oylar bilan jadvalimizdagi =INDEX(A1:D5;3;4) funktsiyasi 1240 ni beradi - tarkib 3-qator va 4-ustundan, ya'ni D3 katakchalaridan. Agar bitta ustun bo'lsa, unda uning raqamini o'tkazib yuborish mumkin, ya'ni INDEX(A2:A6;3) formulasi oxirgi skrinshotda "Samara" ni beradi.
Va bitta aniq bo'lmagan nuance bor: agar INDEX odatdagidek = belgisidan keyin katakka kiritilmasa, balki yo'g'on nuqtadan keyin diapazonga havolaning yakuniy qismi sifatida ishlatilsa, u endi chiqmaydi. hujayraning mazmuni, lekin uning manzili! Shunday qilib, $A$2:INDEX($A$2:$A$100;3) kabi formula chiqishda A2:A4 diapazoniga havola beradi.
Va bu erda MATCH funksiyasi paydo bo'ladi, biz ro'yxatning oxirini dinamik ravishda aniqlash uchun INDEX ichiga kiritamiz:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("I";255);A2:A100))
Nomlangan diapazon yarating
Hammasini bir butunga yig'ish qoladi. Yorliq oching formula (formulalar) Va ni bosing Ism menejeri (Ism menejeri). Ochilgan oynada tugmani bosing yaratish (yangi), maydonga bizning diapazon nomini va formulasini kiriting Diapazon (Malumot):
Bosish qoladi OK va tayyor diapazon har qanday formulalar, ochiladigan ro'yxatlar yoki diagrammalarda ishlatilishi mumkin.
- Jadvallarni ulash va qiymatlarni qidirish uchun VLOOKUP funksiyasidan foydalanish
- Avtomatik to'ldiriladigan ochiladigan ro'yxatni qanday yaratish mumkin
- Katta hajmdagi ma'lumotlarni tahlil qilish uchun pivot jadvalini qanday yaratish kerak