Excel-dagi havolalar - mutlaq, nisbiy va aralash. Excelda nisbiy havolalar bilan ishlashda xatolar

Avtomatik rejimda formulalar yordamida hisob-kitoblarni osonlashtirish uchun hujayralarga havolalar qo'llaniladi. Yozuv turiga qarab ular uchta asosiy turga bo'linadi:

1. Nisbiy havolalar. Oddiy hisob-kitoblar uchun ishlatiladi. Formuladan nusxa ko'chirish koordinatalarni o'zgartirishni talab qiladi.
2. Mutlaq havolalar. Agar siz murakkabroq hisob-kitoblarni amalga oshirishingiz kerak bo'lsa, bu variant mos keladi. O'rnatish uchun "$" belgisidan foydalaning. Misol: $A$1.
3. aralash havolalar. Ushbu turdagi manzillash ustun yoki chiziqni alohida tuzatish zarur bo'lganda hisob-kitoblarda qo'llaniladi. Misol: $A1 yoki A$1.

Agar kiritilgan formula ma'lumotlarini nusxalash zarur bo'lsa, mutlaq va aralash manzilli havolalar qo'llaniladi. Maqolada har xil turdagi havolalar yordamida hisob-kitoblar qanday amalga oshirilganligi misollar bilan ochib beriladi.

Excelda nisbiy hujayra havolasi

Bu hujayraning joylashishini belgilaydigan belgilar to'plami. Dasturdagi havolalar avtomatik ravishda nisbiy adreslash bilan yoziladi. Masalan: A1, A2, B1, B2. Boshqa qator yoki ustunga o'tish formuladagi belgilarni o'zgartiradi. Masalan, A1 boshlang'ich pozitsiyasi. Gorizontal harakat qilish harfni B1, C1, D1 va boshqalarga o'zgartiradi. Xuddi shu tarzda, vertikal chiziq bo'ylab harakatlanayotganda o'zgarishlar sodir bo'ladi, faqat bu holda raqam o'zgaradi - A2, A3, A4, va hokazo. Agar takrorlash kerak bo'lsa qo'shni katakda bir xil turdagi hisoblash, hisoblash nisbiy mos yozuvlar yordamida amalga oshiriladi. Ushbu xususiyatdan foydalanish uchun bir necha qadamlarni bajaring:

1. Yacheykaga ma'lumotlar kiritilishi bilan kursorni harakatlantiring va sichqoncha bilan bosing. Yashil to'rtburchaklar bilan ajratib ko'rsatish hujayraning faollashishini va keyingi ishlashga tayyorligini ko'rsatadi.
2. Ctrl + C tugmalar birikmasini bosish orqali biz tarkibni clipboardga nusxalaymiz.
3. Biz ma'lumotlarni o'tkazmoqchi bo'lgan katakchani yoki oldindan yozilgan formulani faollashtiramiz.
4. Ctrl + V kombinatsiyasini bosish orqali biz tizim buferida saqlangan ma'lumotlarni uzatamiz.

Mutaxassis maslahati! Jadvaldagi bir xil turdagi hisob-kitoblarni amalga oshirish uchun hayotni buzishdan foydalaning. Oldindan kiritilgan formulani o'z ichiga olgan katakchani tanlang. Kursorni pastki o'ng burchakda paydo bo'ladigan kichik kvadrat ustiga olib boring va sichqonchaning chap tugmachasini bosib ushlab turing, bajarilgan harakatga qarab pastki qatorga yoki ekstremal ustunga torting. Sichqoncha tugmachasini bo'shatish orqali hisoblash avtomatik ravishda amalga oshiriladi. Ushbu vosita avtomatik to'ldirish belgisi deb ataladi.

Nisbiy havola misoli

Buni aniqroq qilish uchun nisbiy havola bilan formuladan foydalangan holda hisoblash misolini ko'rib chiqing. Aytaylik, sport do'konining egasi bir yillik ishdan keyin sotishdan tushgan foydani hisoblashi kerak.

Harakatlar tartibi:

1. Misol shuni ko'rsatadiki, B va C ustunlari sotilgan tovarlar miqdori va uning narxini to'ldirish uchun ishlatilgan. Shunga ko'ra, formulani yozish va javobni olish uchun D ustunini tanlang. Formula quyidagicha ko'rinadi: = B2 *C

E'tibor bering! Formulani yozish jarayonini engillashtirish uchun ozgina hiyla ishlating. "=" belgisini qo'ying, sotilgan tovarlar miqdorini bosing, "*" belgisini qo'ying va mahsulot narxini bosing. Teng belgisidan keyingi formula avtomatik ravishda yoziladi.

2. Yakuniy javob uchun "Enter" tugmasini bosing. Keyinchalik, boshqa turdagi mahsulotlardan olingan foydaning umumiy miqdorini hisoblashingiz kerak. Xo'sh, agar chiziqlar soni katta bo'lmasa, unda barcha manipulyatsiyalar qo'lda bajarilishi mumkin. Excelda bir vaqtning o'zida ko'p sonli qatorlarni to'ldirish uchun formulani boshqa kataklarga o'tkazish imkonini beruvchi bitta foydali funksiya mavjud.
3. Kursorni formula yoki yakuniy natija bilan to'rtburchakning pastki o'ng burchagiga olib boring. Qora xochning ko'rinishi kursorni pastga tortib olish mumkinligi haqida signal bo'lib xizmat qiladi. Shunday qilib, har bir mahsulot uchun olingan foydaning avtomatik hisob-kitobi alohida amalga oshiriladi.
4. Bosilgan sichqoncha tugmachasini bo'shatib, biz barcha satrlarda to'g'ri natijalarga erishamiz.

D3 katakchasini bosish orqali siz hujayra koordinatalari avtomatik ravishda o'zgartirilganligini ko'rishingiz mumkin va endi quyidagicha ko'rinadi: =B3 *C3. Bundan kelib chiqadiki, havolalar nisbiy edi.

Nisbiy havolalar bilan ishlashda mumkin bo'lgan xatolar

Shubhasiz, ushbu Excel funktsiyasi hisob-kitoblarni sezilarli darajada soddalashtiradi, ammo ba'zi hollarda qiyinchiliklar paydo bo'lishi mumkin. Keling, har bir tovar ob'ektining foyda koeffitsientini hisoblashning oddiy misolini ko'rib chiqaylik:

1. Jadval tuzing va to'ldiring: A – mahsulot nomi; B - sotilgan miqdor; C - xarajat; D - olingan miqdor. Assortimentda atigi 11 ta mahsulot bor deylik. Shuning uchun, ustunlar tavsifini hisobga olgan holda, 12 satr to'ldiriladi va foydaning umumiy miqdori D.
2. E2 katakchasini bosing va kiriting =D2/D13.
3. "Kirish" tugmasini bosgandan so'ng, birinchi ob'ektni sotishning nisbiy ulushi koeffitsienti paydo bo'ladi.
4. Ustunni pastga cho'zing va natijani kuting. Biroq, tizim “#DIV/0!” xatosini beradi.

Xatoning sababi hisob-kitoblar uchun nisbiy ma'lumotnomadan foydalanishdir. Formuladan nusxa ko'chirish natijasida koordinatalar o'zgaradi. Ya'ni, E3 uchun formula shunday ko'rinadi =D3/D13. D13 katak to'ldirilmaganligi va nazariy jihatdan nol qiymatga ega bo'lganligi sababli, dastur nolga bo'linish mumkin emasligi haqidagi ma'lumot bilan xato qiladi.

Muhim! Xatoni tuzatish uchun formulani D13 koordinatalari aniqlangan tarzda yozish kerak. Nisbiy adreslashda bunday funksiya yo'q. Buning uchun boshqa turdagi havolalar mavjud - mutlaq. 

Excelda mutlaq havolani qanday qilish mumkin

$ belgisidan foydalanish tufayli hujayra koordinatalarini tuzatish mumkin bo'ldi. Bu qanday ishlaydi, biz batafsilroq ko'rib chiqamiz. Dastur sukut bo'yicha nisbiy manzildan foydalanganligi sababli, uni mutlaq qilish uchun siz bir qator amallarni bajarishingiz kerak bo'ladi. Mutlaq manzillashdan foydalangan holda hisob-kitoblarni amalga oshirgan holda, "Bir nechta tovarlarni sotish koeffitsientini qanday topish mumkin" xatosining echimini tahlil qilaylik:

1. E2 ni bosing va havolaning koordinatalarini kiriting =D2/D13. Havola nisbiy bo'lgani uchun ma'lumotlarni tuzatish uchun belgi o'rnatilishi kerak.
2. D katak koordinatalarini tuzatish Ushbu amalni bajarish uchun ustun va satr raqamini ko'rsatadigan harfdan oldin "$" belgisini qo'ying.

Mutaxassis maslahati! Kirish vazifasini engillashtirish uchun formulani tahrirlash uchun katakchani faollashtirish va F4 tugmasini bir necha marta bosish kifoya. Qoniqarli qiymatlarni olmaguningizcha. To'g'ri formula quyidagicha: =D2/$D$13.

3. "Enter" tugmasini bosing. Amalga oshirilgan harakatlar natijasida to'g'ri natija paydo bo'lishi kerak.
4. Formuladan nusxa olish uchun markerni pastki qatorga torting.

Hisob-kitoblarda mutlaq adreslashdan foydalanish tufayli qolgan qatorlardagi yakuniy natijalar to'g'ri bo'ladi.

Excelga aralash havolani qanday qo'yish mumkin

Formulalarni hisoblash uchun nafaqat nisbiy va mutlaq havolalar, balki aralash bo'lganlar ham qo'llaniladi. Ularning o'ziga xos xususiyati shundaki, ular koordinatalardan birini tuzatadilar.

• Masalan, chiziq o'rnini o'zgartirish uchun harf belgisi oldiga $ belgisini yozish kerak.
• Aksincha, agar dollar belgisi harf belgisidan keyin yozilsa, unda qatordagi ko'rsatkichlar o'zgarishsiz qoladi.

Bundan kelib chiqadiki, aralash adreslashdan foydalangan holda tovarlarni sotish koeffitsientini aniqlash bilan bog'liq oldingi muammoni hal qilish uchun qator raqamini belgilash kerak. Ya'ni, $ belgisi ustun harfidan keyin qo'yiladi, chunki uning koordinatalari nisbiy murojaatda ham o'zgarmaydi. Misol keltiramiz:

1. To'g'ri hisob-kitoblar uchun kiriting =D1/$D$3 va "Enter" tugmasini bosing. Dastur aniq javob beradi.
2. Formulani ustun ostidagi keyingi kataklarga siljitish va aniq natijalarga erishish uchun tutqichni pastki katakchaga torting.
3. Natijada, dastur to'g'ri hisob-kitoblarni beradi.

Diqqat! Agar siz harf oldiga $ belgisini qo'ysangiz, Excel "#DIV/0!" xatosini beradi, bu esa bu operatsiyani bajarish mumkin emasligini bildiradi.

“SuperAbsolute” manzillash

Oxir-oqibat, keling, mutlaq havolaning yana bir misolini - "SuperAbsolute" manzilini ko'rib chiqaylik. Uning xususiyatlari va farqlari qanday. Taxminan 30 raqamini oling va uni B2 katakka kiriting. Aynan shu raqam asosiy bo'ladi, u bilan bir qator harakatlarni bajarish kerak, masalan, uni kuchga ko'tarish.

1. Barcha amallarni to'g'ri bajarish uchun C ustuniga quyidagi formulani kiriting: =$B$2^$D2. D ustuniga biz darajalar qiymatini kiritamiz.

2. "Enter" tugmasini bosgandan va formulani faollashtirgandan so'ng, biz markerni ustunga cho'zamiz.
3. Biz to'g'ri natijalarga erishamiz.

Xulosa shuki, bajarilgan barcha harakatlar bitta sobit B2 katakchaga tegishli, shuning uchun:

• Formulani C3 yacheykasidan E3, F3 yoki H3 katakchalariga nusxalash natijani o'zgartirmaydi. U o'zgarishsiz qoladi - 900.
• Agar siz yangi ustun qo'yishingiz kerak bo'lsa, formulali katakning koordinatalari o'zgaradi, ammo natija ham o'zgarishsiz qoladi.

Bu "SuperAbsolute" havolasining o'ziga xos xususiyati: agar siz ko'chirishingiz kerak bo'lsa, natija o'zgarmaydi. Biroq, ma'lumotlar uchinchi tomon manbalaridan kiritilgan holatlar mavjud. Shunday qilib, ustunlar yon tomonga siljiydi va ma'lumotlar B2 ustunida eski usulda o'rnatiladi. Bu holatda nima bo'ladi? Aralashtirilganda formula bajarilgan harakatga qarab o'zgaradi, ya'ni u endi B2 ga emas, balki C2 ga ishora qiladi. Ammo kiritish B2 da amalga oshirilganligi sababli, yakuniy natija noto'g'ri bo'ladi.

Malumot! Uchinchi tomon manbalaridan makroslarni kiritish uchun siz ishlab chiquvchi sozlamalarini yoqishingiz kerak (ular sukut bo'yicha o'chirilgan). Buni amalga oshirish uchun Variantlar-ga o'ting, lenta sozlamalarini oching va "Tuzuvchi" qarshisidagi o'ng ustundagi katakchani belgilang. Shundan so'ng, ilgari oddiy foydalanuvchining ko'zidan yashiringan ko'plab funktsiyalarga kirish ochiladi.

Shu o‘rinda savol tug‘iladi: yangi ma’lumotlar ustunlari kiritilganiga qaramay, C2 katakchadagi formulani B yacheykadan asl raqam yig‘ilishi uchun o‘zgartirish mumkinmi? Jadvaldagi o'zgarishlar uchinchi tomon manbalaridan ma'lumotlarni o'rnatishda umumiy miqdorni aniqlashga ta'sir qilmasligini ta'minlash uchun siz quyidagi amallarni bajarishingiz kerak:

1. B2 katak koordinatalari o'rniga quyidagi ko'rsatkichlarni kiriting: =BILOSHIY("B2"). Natijada, ko'chirilgandan so'ng, shakllantirish tarkibi quyidagicha ko'rinadi: =BILOSHIY("B2")^$E2.
2. Ushbu funktsiya tufayli, jadvalga ustunlar qo'shilgan yoki olib tashlanganidan qat'i nazar, havola har doim B2 koordinatalari bo'lgan kvadratga ishora qiladi.

Shuni tushunish kerakki, hech qanday ma'lumotga ega bo'lmagan hujayra har doim "0" qiymatini ko'rsatadi.

Xulosa

Ta'riflangan havolalarning uchta turidan foydalanish tufayli Excelda hisob-kitoblar bilan ishlashni osonlashtiradigan ko'plab imkoniyatlar paydo bo'ladi. Shuning uchun, formulalar bilan ishlashni boshlashdan oldin, avval havolalarni va ularni o'rnatish qoidalarini o'qing.