Excel formulasida hujayrani qanday muzlatish mumkin

Ko'pincha foydalanuvchilar formuladagi katakchani mahkamlash zarurati bilan duch kelishadi. Misol uchun, u formuladan nusxa ko'chirmoqchi bo'lgan holatlarda sodir bo'ladi, lekin havola o'zining asl joyidan ko'chirilgan hujayralar sonining yuqoriga va pastga siljimasligi uchun.

Bunday holda, siz Excel-da hujayra havolasini tuzatishingiz mumkin. Va bu bir vaqtning o'zida bir necha usul bilan amalga oshirilishi mumkin. Keling, ushbu maqsadga qanday erishish mumkinligini batafsil ko'rib chiqaylik.

Excel havolasi nima

Varaq hujayralardan iborat. Ularning har biri o'ziga xos ma'lumotlarni o'z ichiga oladi. Boshqa hujayralar undan hisob-kitoblarda foydalanishi mumkin. Ammo ular ma'lumotni qayerdan olishni qanday tushunishadi? Bu ularga havolalar yaratishga yordam beradi.

Har bir havola bitta harf va bitta raqamdan iborat katakchani belgilaydi. Harf ustunni, raqam esa qatorni ifodalaydi. 

Bog'lanishning uch turi mavjud: mutlaq, nisbiy va aralash. Ikkinchisi sukut bo'yicha o'rnatiladi. Mutlaq ma'lumotnoma - bu ustun va ustunning aniq manziliga ega. Shunga ko'ra, aralash - alohida ustun yoki qator o'rnatilgan.

1-usul

Ustun va satr manzillarini saqlash uchun quyidagi amallarni bajaring:

1. Formulani o'z ichiga olgan katakchani bosing.
2. Bizga kerak bo'lgan hujayra uchun formulalar satrini bosing.
3. F4 tugmachasini bosing.

Natijada, hujayra havolasi mutlaqga o'zgaradi. Bu xarakterli dollar belgisi bilan tan olinishi mumkin. Misol uchun, agar siz B2 katakchasini bossangiz va keyin F4 tugmachasini bossangiz, havola quyidagicha ko'rinadi: $B$2.

Yacheykadagi manzil qismi oldidagi dollar belgisi nimani anglatadi?

1. Agar u harf oldiga qo'yilsa, formula qayerga ko'chirilganidan qat'i nazar, ustun havolasi o'zgarmasligini bildiradi.
2. Agar dollar belgisi raqamning oldida bo'lsa, bu ipning mahkamlanganligini bildiradi. 

2-usul

Ushbu usul avvalgisi bilan deyarli bir xil, faqat F4 tugmasini ikki marta bosishingiz kerak. masalan, agar bizda B2 katak bo'lgan bo'lsa, undan keyin u B$2 bo'ladi. Oddiy so'zlar bilan aytganda, shu tarzda biz chiziqni tuzatishga muvaffaq bo'ldik. Bunday holda, ustunning harfi o'zgaradi.

Bu juda qulay, masalan, pastki katakchada yuqoridan ikkinchi katakning mazmunini ko'rsatish kerak bo'lgan jadvallarda. Bunday formulani ko'p marta bajarish o'rniga, qatorni tuzatish va ustunni o'zgartirishga ruxsat berish kifoya.

3-usul

Bu avvalgi usul bilan butunlay bir xil, faqat F4 tugmasini uch marta bosishingiz kerak. Keyin faqat ustunga havola mutlaq bo'ladi va qator o'zgarmas bo'lib qoladi.

4-usul

Aytaylik, bizda hujayraga mutlaq havola bor, lekin bu erda uni nisbiy qilish kerak edi. Buni amalga oshirish uchun F4 tugmasini shunchalik ko'p bosingki, havolada $ belgilari yo'q. Keyin u nisbiy bo'ladi va formulani ko'chirish yoki nusxalashda ustun manzili ham, satr manzili ham o'zgaradi.

Katta diapazon uchun hujayralarni mahkamlash

Yuqoridagi usullarni bajarish uchun hech qanday qiyinchilik tug'dirmasligini ko'ramiz. Ammo vazifalar aniq. Va, masalan, bir vaqtning o'zida bir nechta o'nlab formulalar mavjud bo'lsa, nima qilish kerak, ulardagi havolalar mutlaqga aylantirilishi kerak. 

Afsuski, standart Excel usullari bu maqsadga erisha olmaydi. Buning uchun siz VBA-Excel deb nomlangan maxsus qo'shimchadan foydalanishingiz kerak. U Excel bilan umumiy vazifalarni tezroq bajarishga imkon beruvchi ko'plab qo'shimcha funktsiyalarni o'z ichiga oladi.

U yuzdan ortiq foydalanuvchi tomonidan belgilangan funktsiyalar va 25 xil makrolarni o'z ichiga oladi va u muntazam ravishda yangilanadi. Bu deyarli har qanday jihat bilan ishlashni yaxshilashga imkon beradi:

1. Hujayralar.
2. Ibratli
3. Har xil turdagi funktsiyalar.
4. Havolalar va massivlar.

Xususan, ushbu plagin bir vaqtning o'zida ko'p sonli formulalardagi havolalarni tuzatishga imkon beradi. Buning uchun siz quyidagi amallarni bajarishingiz kerak:

1. Diapazonni tanlang.
2. O'rnatishdan so'ng paydo bo'ladigan VBA-Excel yorlig'ini oching. 
3. "Funktsiyalar" menyusini oching, bu erda "Formulalarni qulflash" opsiyasi joylashgan.
   

   

4. Keyinchalik, kerakli parametrni ko'rsatishingiz kerak bo'lgan dialog oynasi paydo bo'ladi. Ushbu qo'shimcha sizga ustun va ustunni alohida-alohida, bir-biriga mahkamlash imkonini beradi, shuningdek, allaqachon mavjud bo'lgan biriktirishni paket bilan olib tashlashga imkon beradi. Kerakli parametr mos keladigan radio tugma yordamida tanlangandan so'ng, "OK" tugmasini bosish orqali harakatlaringizni tasdiqlashingiz kerak.

misol

Buni aniqroq qilish uchun bir misol keltiramiz. Aytaylik, bizda mahsulot tannarxi, uning umumiy miqdori va sotishdan tushgan tushumni tavsiflovchi ma’lumotlar mavjud. Biz esa, miqdori va narxidan kelib chiqqan holda, qancha pul ishlaganimizni yo'qotishlarni ayirmasdan avtomatik ravishda aniqlashimiz kerak bo'lgan jadval yaratish vazifasiga duch keldik.

Bizning misolimizda buning uchun siz = formulasini kiritishingiz kerakB2*C2. Ko'rib turganingizdek, bu juda oddiy. Hujayra yoki uning alohida ustuni yoki satri manzilini qanday tuzatish mumkinligini tasvirlash uchun uning misolidan foydalanish juda oson. 

Albatta, ushbu misolda siz avtomatik to'ldirish belgisi yordamida formulani pastga sudrab ko'rishingiz mumkin, ammo bu holda hujayralar avtomatik ravishda o'zgartiriladi. Shunday qilib, D3 katakchada raqamlar mos ravishda 3 ga almashtiriladigan boshqa formula bo'ladi. Bundan tashqari, D4 sxemasiga ko'ra, formula = B4 * C4, D5 ko'rinishini oladi - xuddi shunday, lekin 5 raqami va boshqalar.

Agar kerak bo'lsa (ko'p hollarda bu chiqadi), unda hech qanday muammo yo'q. Ammo agar siz formulani sudrab borishda o'zgarmasligi uchun bitta katakka o'rnatishingiz kerak bo'lsa, bu biroz qiyinroq bo'ladi. 

Aytaylik, dollar daromadini aniqlashimiz kerak. Keling, uni B7 katakchaga joylashtiramiz. Keling, bir oz nostaljik bo'lib, bir dollar uchun 35 rubl narxini ko'rsatamiz. Shunga ko'ra, dollardagi daromadni aniqlash uchun rubldagi summani dollar kursiga bo'lish kerak.

Bu bizning misolimizda qanday ko'rinadi.

Agar biz avvalgi versiyaga o'xshab, formulani yozishga harakat qilsak, biz muvaffaqiyatsiz bo'lamiz. Xuddi shunday, formula mos keladiganga o'zgaradi. Bizning misolimizda u shunday bo'ladi: =E3*B8. Bu erdan biz ko'rishimiz mumkin. formulaning birinchi qismi E3 ga aylandi va biz o'zimizga bu vazifani qo'ydik, ammo formulaning ikkinchi qismini B8 ga o'zgartirishimiz shart emas. Shuning uchun biz ma'lumotnomani mutlaqga aylantirishimiz kerak. Buni F4 tugmachasini bosmasdan, faqat dollar belgisini qo'yish orqali qilishingiz mumkin.

Ikkinchi katakka havolani mutlaqga aylantirganimizdan so'ng, u o'zgarishlardan himoyalangan. Endi siz uni avtomatik to'ldirish dastagidan foydalanib xavfsiz sudrab olishingiz mumkin. Formulaning joylashuvidan qat'i nazar, barcha sobit ma'lumotlar bir xil bo'lib qoladi va qabul qilinmagan ma'lumotlar moslashuvchan tarzda o'zgaradi. Barcha hujayralarda ushbu satrda tasvirlangan rubldagi daromad bir xil dollar kursiga bo'linadi.

Formulaning o'zi quyidagicha ko'rinadi:

=D2/$B$7

Diqqat! Biz ikkita dollar belgisini ko'rsatdik. Shu tarzda biz dasturga ustunni ham, qatorni ham tuzatish kerakligini ko'rsatamiz.

Makroslarda hujayra havolalari

Makros - bu harakatlarni avtomatlashtirish imkonini beruvchi kichik dastur. Excelning standart funksiyasidan farqli o'laroq, so'l darhol ma'lum bir katakchani o'rnatish va bir necha qator kodlarda ma'lum amallarni bajarish imkonini beradi. Ma'lumotni ommaviy qayta ishlash uchun foydalidir, masalan, qo'shimchalarni o'rnatishning hech qanday usuli bo'lmasa (masalan, shaxsiy kompyuter emas, balki kompaniya kompyuteri ishlatiladi).

Avval siz makroning asosiy tushunchasi boshqa ob'ektlarni o'z ichiga olishi mumkin bo'lgan ob'ektlar ekanligini tushunishingiz kerak. Ish kitoblari ob'ekti elektron kitob (ya'ni hujjat) uchun javobgardir. U ochiq hujjatning barcha varaqlari to'plami bo'lgan Sheets ob'ektini o'z ichiga oladi. 

Shunga ko'ra, hujayralar Hujayralar ob'ektidir. U ma'lum bir varaqning barcha hujayralarini o'z ichiga oladi.

Har bir ob'ekt qavs ichidagi argumentlar bilan kvalifikatsiya qilinadi. Hujayralarda ular shu tartibda havola qilinadi. Avval qator raqami, keyin esa ustun raqami yoki harfi (har ikkala format ham qabul qilinadi) ko'rsatiladi.

Masalan, C5 uyasiga havolani o'z ichiga olgan kod qatori quyidagicha ko'rinadi:

Ishchi kitoblar(“Book2.xlsm”).Shaxslar(“Roʻyxat2”).Yacheykalar(5, 3)

Ishchi kitoblar(“Book2.xlsm”).Vraqalar(“Roʻyxat2”).Yacheykalar(5, “C”)

Bundan tashqari, ob'ekt yordamida hujayraga kirishingiz mumkin Toza. Umuman olganda, u diapazonga havola berish uchun mo'ljallangan (uning elementlari, shuningdek, mutlaq yoki nisbiy bo'lishi mumkin), lekin siz shunchaki Excel hujjatidagi kabi bir xil formatda hujayra nomini berishingiz mumkin.

Bunday holda, chiziq shunday ko'rinadi.

Ish daftarlari (“Book2.xlsm”).Shaxslar(“Roʻyxat2”).Range (“C5”)

Bu variant qulayroqdek tuyulishi mumkin, ammo birinchi ikkita variantning afzalligi shundaki, siz qavs ichidagi o'zgaruvchilardan foydalanishingiz va endi mutlaq emas, balki nisbiyga o'xshash havolani berishingiz mumkin, bu natijalarga bog'liq bo'ladi. hisob-kitoblar.

Shunday qilib, makroslardan dasturlarda samarali foydalanish mumkin. Aslida, bu erda hujayralar yoki diapazonlarga barcha havolalar mutlaq bo'ladi va shuning uchun ular bilan ham tuzatilishi mumkin. To'g'ri, bu unchalik qulay emas. Algoritmdagi ko'p sonli bosqichli murakkab dasturlarni yozishda makroslardan foydalanish foydali bo'lishi mumkin. Umuman olganda, mutlaq yoki nisbiy havolalardan foydalanishning standart usuli ancha qulayroqdir. 

Xulosa

Biz hujayra ma'lumotnomasi nima ekanligini, u qanday ishlashini va nima uchun ekanligini aniqladik. Biz mutlaq va nisbiy havolalar o'rtasidagi farqni tushundik va bir turni boshqasiga aylantirish uchun nima qilish kerakligini aniqladik (oddiy so'zlar bilan aytganda, manzilni tuzatish yoki uni olib tashlash). Ko'p sonli qiymatlar bilan buni qanday qilib darhol amalga oshirishingiz mumkinligini aniqladik. Endi siz ushbu xususiyatdan to'g'ri vaziyatlarda foydalanish uchun moslashuvchanlikka egasiz.