Mundarija
Birinchi qarashda (ayniqsa, yordamni o'qiyotganda), funktsiya BILVOSIT (BOSHQA) oddiy va hatto keraksiz ko'rinadi. Uning mohiyati havolaga o'xshagan matnni to'liq havolaga aylantirishdir. Bular. agar biz A1 katakka murojaat qilishimiz kerak bo'lsa, biz odatiy ravishda to'g'ridan-to'g'ri havola qilishimiz mumkin (D1 da teng belgini kiriting, A1 ni bosing va Enter tugmasini bosing) yoki biz foydalanishimiz mumkin. BILVOSIT xuddi shu maqsadda:
E'tibor bering, funktsiya argumenti - A1 ga havola - bu erda matn bo'lishi uchun tirnoq belgilariga kiritilgan.
"Xo'sh, yaxshi", deysiz. "Va nima foyda?"
Ammo birinchi taassurot bilan hukm qilmang - bu aldamchi. Bu xususiyat sizga ko'p holatlarda yordam berishi mumkin.
Misol 1. Transpoze
Janrning klassikasi: siz vertikal diametrni burishingiz kerak
yivni gorizontalga (ko'chirish). Albatta, siz maxsus qo'shimcha yoki funktsiyadan foydalanishingiz mumkin TRANSP (TRANSPOZE) massiv formulasida, lekin siz bizning yordamida olishingiz mumkin BILVOSIT:
Mantiq oddiy: keyingi katakning manzilini olish uchun biz “A” harfini maxsus “&” belgisi va funksiya bizga beradigan joriy katakning ustun raqami bilan yopishtiramiz. COLUMN (ustun).
Teskari protsedura biroz boshqacha tarzda amalga oshirilishi yaxshiroqdir. Bu vaqtdan beri biz B2, C2, D2 va boshqalar hujayralariga havolani yaratishimiz kerak, klassik "dengiz jangi" o'rniga R1C1 havola rejimidan foydalanish qulayroqdir. Ushbu rejimda bizning hujayralarimiz faqat ustun raqamida farqlanadi: B2 = R1C2, C2=R1C3, D2=R1C4 va hokazo
Bu erda ikkinchi ixtiyoriy funktsiya argumenti keladi. BILVOSIT. Agar teng bo'lsa YOLG'ON (YOLG'ON), keyin siz R1C1 rejimida havola manzilini o'rnatishingiz mumkin. Shunday qilib, biz gorizontal diapazonni vertikalga osongina o'tkazishimiz mumkin:
Misol 2. Interval bo'yicha yig'indi
Funktsiyadan foydalanib, biz allaqachon varaqdagi berilgan o'lchamdagi oyna (diapazon) bo'yicha yig'ishning bir usulini tahlil qildik ISHLASH (OFSET). Xuddi shunday muammo ham yordamida hal qilinishi mumkin BILVOSIT. Agar biz faqat ma'lum bir diapazondagi ma'lumotlarni umumlashtirishimiz kerak bo'lsa, biz uni qismlardan yopishtirishimiz va keyin uni funktsiya ichiga kiritishimiz mumkin bo'lgan to'liq huquqli havolaga aylantirishimiz mumkin. SUM (SUM):
Misol 3. Aqlli jadval ochiladigan ro'yxati
Ba'zan Microsoft Excel aqlli jadval nomlari va ustunlarini to'liq havola sifatida ko'rsatmaydi. Masalan, ochiladigan ro'yxatni yaratishga urinayotganda (tab Ma'lumotlar - ma'lumotlarni tekshirish) ustunga asoslangan xodimlari aqlli stoldan odamlar biz xatoga duch kelamiz:
Agar biz havolani funksiyamiz bilan "o'rab" olsak BILVOSIT, keyin Excel uni osongina qabul qiladi va bizning ochiladigan ro'yxatimiz aqlli jadvalning oxiriga yangi xodimlarni qo'shganda dinamik ravishda yangilanadi:
4-misol. Buzilmaydigan bog'lanishlar
Ma'lumki, Excel varaqqa satr ustunlarini qo'shish yoki o'chirishda formulalardagi mos yozuvlar manzillarini avtomatik ravishda tuzatadi. Ko'pgina hollarda, bu to'g'ri va qulay, lekin har doim ham emas. Aytaylik, ismlarni xodimlar katalogidan hisobotga o'tkazishimiz kerak:
Agar siz oddiy havolalarni qo'ysangiz (birinchi yashil katakchaga =B2 kiriting va uni pastga nusxalash), keyin, masalan, Dashani o'chirib tashlaganingizda, biz #LINK ni olamiz! unga mos keladigan yashil katakdagi xato. (#REF!). Havolalar yaratish funksiyasidan foydalanilganda BILVOSIT bunday muammo bo'lmaydi.
5-misol: Bir nechta varaqlardan ma'lumotlarni yig'ish
Aytaylik, bizda turli xil xodimlarning (Mixail, Elena, Ivan, Sergey, Dmitriy) bir xil turdagi hisobotlari bo'lgan 5 ta varaq bor:
Faraz qilaylik, barcha jadvallardagi tovarlar va oylarning shakli, o'lchami, joylashuvi va ketma-ketligi bir xil - faqat raqamlar farqlanadi.
Siz barcha varaqlardan ma'lumotlarni to'plashingiz mumkin (uni umumlashtirmang, lekin ularni bir-birining ostiga "qoziq" qilib qo'ying) faqat bitta formula bilan:
Ko'rib turganingizdek, g'oya bir xil: biz havolani berilgan varaqning kerakli katakchasiga yopishtiramiz va BILVOSIT uni “jonli”ga aylantiradi. Qulaylik uchun jadval ustidagi ustunlarning harflarini (B, C, D) va o'ngda - har bir varaqdan olinishi kerak bo'lgan qator raqamlarini qo'shdim.
Tuzoqlar
Agar siz foydalanayotgan bo'lsangiz BILVOSIT (BOSHQA) Uning kamchiliklari haqida eslash kerak:
- Agar siz boshqa faylga havola qilsangiz (fayl nomini kvadrat qavs ichida, varaq nomi va katak manzilini yopishtirish orqali), u faqat asl fayl ochiq bo'lganda ishlaydi. Agar biz uni yopsak, #LINK xatosini olamiz!
- INDIRECT dinamik nomli diapazonga murojaat qila olmaydi. Statik rejimda - muammo yo'q.
- INDIRECT - o'zgaruvchan yoki "uchuvchi" funksiya, ya'ni u oddiy funktsiyalardagi kabi faqat hujayralarga ta'sir qilish emas, balki varaqning istalgan katakchasidagi har qanday o'zgarish uchun qayta hisoblab chiqiladi. Bu ishlashga yomon ta'sir qiladi va katta INDIRECT jadvallari bilan shug'ullanmaslik yaxshiroqdir.
- Avtomatik o'lchamli dinamik diapazonni qanday yaratish mumkin
- OFFSET funksiyasi bilan varaqdagi diapazon-oyna bo'yicha yig'ish