Mundarija
Tabriklaymiz! Siz marafonning oxirgi kuniga yetib keldingiz 30 kun ichida 30 ta Excel funksiyasi. Bu uzoq va qiziqarli sayohat bo'ldi, uning davomida siz Excel funksiyalari haqida ko'p foydali narsalarni bilib oldingiz.
Marafonning 30-kuni biz funktsiyani o'rganishga bag'ishlaymiz BILVOSIT (INDIRECT), bu matn qatori tomonidan belgilangan havolani qaytaradi. Ushbu funksiya yordamida siz qaram ochiladigan ro'yxatlarni yaratishingiz mumkin. Misol uchun, ochiladigan ro'yxatdan mamlakatni tanlashda shahar ochiladigan ro'yxatida qaysi variantlar paydo bo'lishini aniqlaydi.
Shunday qilib, keling, funksiyaning nazariy qismini batafsil ko'rib chiqaylik BILVOSIT (BILVOYI) va uni qo'llashning amaliy misollarini o'rganing. Agar sizda qo'shimcha ma'lumotlar yoki misollar bo'lsa, ularni sharhlarda baham ko'ring.
30-funksiya: BILOVYOT
vazifa BILVOSIT (INDIRECT) matn qatori tomonidan belgilangan havolani qaytaradi.
INDIRECT funksiyasidan qanday foydalanish mumkin?
Funktsiyadan beri BILVOSIT (INDIRECT) matn qatori orqali berilgan havolani qaytaradi, undan quyidagi maqsadlarda foydalanishingiz mumkin:
- O'zgaruvchan bo'lmagan boshlang'ich havola yarating.
- Statik nomli diapazonga havola yarating.
- Varaq, satr va ustun ma'lumotlaridan foydalangan holda havola yarating.
- O'zgarmas raqamlar qatorini yarating.
INDIRECT (INDIRECT) sintaksisi
vazifa BILVOSIT (INDIRECT) quyidagi sintaksisga ega:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_matn (link_to_cell) - havola matni.
- a1 – agar TRUE (TRUE) ga teng bo‘lsa yoki ko‘rsatilmagan bo‘lsa, u holda havola uslubi qo‘llaniladi A1; va agar FALSE (FALSE), keyin uslub R1C1.
Tuzoqlar bilvosita (bilvosita)
- vazifa BILVOSIT Excel ish varag'idagi qiymatlar har doim o'zgarganda (BEVO'LOV) qayta hisoblab chiqiladi. Agar funktsiya ko'plab formulalarda ishlatilsa, bu ish kitobingizni sezilarli darajada sekinlashtirishi mumkin.
- Agar funktsiya BILVOSIT (INDIRECT) boshqa Excel ish kitobiga havola yaratadi, u ish kitobi ochiq bo'lishi kerak yoki formula xato haqida xabar beradi #REF! (#LINK!).
- Agar funktsiya BILVOSIT (INDIRECT) satr va ustun chegarasidan oshib ketadigan diapazonga murojaat qiladi, formula xato haqida xabar beradi #REF! (#LINK!).
- vazifa BILVOSIT (INDIRECT) dinamik nomli diapazonga havola qila olmaydi.
1-misol: O'zgaruvchan bo'lmagan boshlang'ich havola yarating
Birinchi misolda C va E ustunlari bir xil raqamlarni o'z ichiga oladi, ularning yig'indilari funktsiya yordamida hisoblanadi SUM (SUM) ham xuddi shunday. Biroq, formulalar biroz boshqacha. C8 katakda formula quyidagicha:
=SUM(C2:C7)
=СУММ(C2:C7)
E8 katakda funksiya BILVOSIT (INDIRECT) E2 boshlang'ich katagiga havola yaratadi:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Agar siz varaqning yuqori qismiga qator qo'shsangiz va yanvar (yanvar) uchun qiymat qo'shsangiz, u holda C ustunidagi miqdor o'zgarmaydi. Formula qator qo'shilishi munosabati bilan o'zgaradi:
=SUM(C3:C8)
=СУММ(C3:C8)
Biroq, funktsiya BILVOSIT (BEVOVO'LOV) E2ni boshlang'ich katak sifatida belgilaydi, shuning uchun yanvar avtomatik ravishda E ustunining jami hisobiga kiritiladi. Yakuniy katak o'zgardi, lekin boshlang'ich katakka ta'sir ko'rsatmadi.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
2-misol: Statik nomli diapazonga havola
vazifa BILVOSIT (INDIRECT) nomli diapazonga havola yaratishi mumkin. Ushbu misolda ko'k hujayralar diapazonni tashkil qiladi NumList. Bundan tashqari, B ustunidagi qiymatlardan dinamik diapazon ham yaratiladi NumListDyn, bu ustundagi raqamlar soniga qarab.
Ikkala diapazon uchun yig'indini funktsiyaga argument sifatida uning nomini berish orqali hisoblash mumkin SUM (SUM), siz E3 va E4 kataklarida ko'rib turganingizdek.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Funktsiyaga diapazon nomini kiritish o'rniga SUM (SUM), Siz ishchi varaqning kataklaridan birida yozilgan nomga murojaat qilishingiz mumkin. Misol uchun, agar ism NumList D7 katakchada yozilgan bo'lsa, E7 katakdagi formula quyidagicha bo'ladi:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Afsuski, funktsiya BILVOSIT (INDIRECT) dinamik diapazon ma'lumotnomasini yarata olmaydi, shuning uchun siz ushbu formulani E8 katakchaga ko'chirganingizda xatolik paydo bo'ladi. #REF! (#LINK!).
3-misol: varaq, satr va ustun ma'lumotlaridan foydalangan holda havola yarating
Siz osongina satr va ustun raqamlari asosida havola yaratishingiz mumkin, shuningdek, ikkinchi funktsiya argumenti uchun FALSE (FALSE) qiymatidan foydalaning. BILVOSIT (BILVOSIT). Uslub havolasi shunday yaratiladi R1C1. Ushbu misolda biz havolaga qo'shimcha ravishda varaq nomini qo'shdik – “MyLinks”!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4-misol: O‘zgarmas sonlar massivini yarating
Ba'zan Excel formulalarida raqamlar qatoridan foydalanish kerak bo'ladi. Quyidagi misolda biz B ustunidagi eng katta 3 ta raqamni o‘rtacha hisoblamoqchimiz. Raqamlar D4 katakchadagi kabi formulaga kiritilishi mumkin:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Agar sizga kattaroq massiv kerak bo'lsa, unda siz formuladagi barcha raqamlarni kiritishni xohlamaysiz. Ikkinchi variant esa funksiyadan foydalanishdir Row (QAT), D5 katagiga kiritilgan massiv formulasida bajarilganidek:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Uchinchi variant - funksiyadan foydalanish Row (STRING) bilan birga BILVOSIT D6 katakdagi massiv formulasi bilan bajarilganidek (BEVOVO'LOV):
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Barcha 3 formula uchun natija bir xil bo'ladi:
Biroq, agar satrlar varaqning yuqori qismiga kiritilgan bo'lsa, ikkinchi formula noto'g'ri natija beradi, chunki formuladagi havolalar qatorni siljitish bilan birga o'zgaradi. Endi uchta eng katta raqamning o'rtacha qiymati o'rniga, formula 3, 4 va 5-sonlarning o'rtacha qiymatini qaytaradi.
Funktsiyalardan foydalanish BILVOSIT (INDIRECT), uchinchi formula to'g'ri qator havolalarini saqlaydi va to'g'ri natijani ko'rsatishda davom etadi.