мазмуну
Куттуктайбыз! Сиз марафондун акыркы күнүнө жеттиңиз 30 күндө 30 Excel функциялары. Бул узак жана кызыктуу сапар болду, анын жүрүшүндө сиз Excel функциялары жөнүндө көптөгөн пайдалуу нерселерди үйрөндүңүз.
Марафондун 30-күнүндө функцияны изилдөөгө арнайбыз КЫЙЫР (КЫЙЫР), ал текст сапта көрсөтүлгөн шилтемени кайтарат. Бул функция менен сиз көз каранды ылдый түшүүчү тизмелерди түзө аласыз. Мисалы, ылдый түшүүчү тизмеден өлкөнү тандоодо шаардын ачылуучу тизмесинде кайсы опциялар пайда болоорун аныктайт.
Ошентип, функциянын теориялык бөлүгүн жакшыраак карап көрөлү КЫЙЫР (КЫЙЫР) жана аны колдонуунун практикалык мисалдарын изилдөө. Эгер сизде кошумча маалымат же мисалдар болсо, аларды комментарийлерде бөлүшүңүз.
Функция 30: КЫЙЫР
милдети КЫЙЫР (КЫЙЫР) текст сапта көрсөтүлгөн шилтемени кайтарат.
КЫЙЫР функциясын кантип колдонсо болот?
Функциядан бери КЫЙЫР (КЫЙЫР) текст сап менен берилген шилтемени кайтарат, сиз аны төмөнкү үчүн колдоно аласыз:
- Кыймылсыз баштапкы шилтеме түзүңүз.
- Статикалык аталган диапазонго шилтеме түзүңүз.
- Барак, сап жана тилке маалыматын колдонуу менен шилтеме түзүңүз.
- Сандардын өзгөрүлбөгөн массивин түзүңүз.
Синтаксис КЫЙЫР (КЫЙЫР)
милдети КЫЙЫР (КЫЙЫР) төмөнкү синтаксиске ээ:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- кайра_текст (шилтеме_уячага) - шилтеменин тексти.
- a1 – эгерде TRUE (TRUE) барабар болсо же көрсөтүлбөсө, анда шилтеменин стили колдонулат A1; жана ЖАЛГАН болсо (ЖАЛГАН), анда стил R1C1.
Тузактар КЫЙЫР (КЫЙЫР)
- милдети КЫЙЫР (КЫЙЫР) Excel иш барагындагы маанилер өзгөргөн сайын кайра эсептелинет. Бул функция көптөгөн формулаларда колдонулса, иш китебиңизди бир топ жайлатышы мүмкүн.
- Эгерде функция КЫЙЫР (КЫЙЫР) башка Excel иш китебине шилтеме түзөт, ал иш китеби ачык болушу керек, болбосо формула ката жөнүндө кабарлайт #РЕФ! (#LINK!).
- Эгерде функция КЫЙЫР (КЫЙЫР) сап жана мамыча чегинен ашкан диапазонго шилтеме кылат, формула ката жөнүндө кабарлайт #РЕФ! (#LINK!).
- милдети КЫЙЫР (КЫЙЫР) динамикалык аталган диапазонго шилтеме кыла албайт.
1-мисал: жылдырылбаган баштапкы шилтемени түзүңүз
Биринчи мисалда C жана E тилкелери бирдей сандарды камтыйт, алардын суммасы функциянын жардамы менен эсептелет СУМ (SUM) да ушундай. Бирок, формулалар бир аз башкача. C8 уячасында формула төмөнкүдөй:
=SUM(C2:C7)
=СУММ(C2:C7)
E8 уячасында функция КЫЙЫР (КЫЙЫР) E2 баштапкы уячасына шилтеме жаратат:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Эгерде сиз барактын жогору жагына сап киргизип, январь (январь) үчүн маанини кошсоңуз, анда С тилкесиндеги сумма өзгөрбөйт. Формула сапты кошууга жооп берип, өзгөрөт:
=SUM(C3:C8)
=СУММ(C3:C8)
Бирок, функция КЫЙЫР (КЫЙЫР) баштапкы уяча катары E2ди бекитет, ошондуктан январь айы E тилкесинин суммарынын эсебине автоматтык түрдө кошулат. Аяктоочу уяча өзгөрдү, бирок баштапкы уячага таасири тийген жок.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
2-мисал: Статикалык аталган диапазонго шилтеме
милдети КЫЙЫР (КЫЙЫР) аталган диапазонго шилтеме түзө алат. Бул мисалда көк клеткалар диапазонду түзөт NumList. Мындан тышкары, динамикалык диапазон В тилкесиндеги маанилерден түзүлөт NumListDyn, бул тилкедеги сандардын санына жараша.
Эки диапазондун суммасын жөн гана анын атын функцияга аргумент катары берүү менен эсептесе болот СУМ (SUM), сиз E3 жана E4 уячаларында көрүп тургандай.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Функцияга диапазондун атын терүүнүн ордуна СУМ (SUM), Сиз иш барагынын уячаларынын биринде жазылган атка кайрылсаңыз болот. Мисалы, аты болсо NumList D7 уячасында жазылган болсо, анда E7 уячасындагы формула мындай болот:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Тилекке каршы функция КЫЙЫР (КЫЙЫР) динамикалык диапазондун шилтемесин түзө албайт, андыктан бул формуланы E8 уячасына көчүргөндө, сиз ката аласыз #РЕФ! (#LINK!).
3-мисал: барак, сап жана мамыча маалыматын колдонуу менен шилтеме түзүңүз
Сиз оңой эле саптын жана мамычанын номерлеринин негизинде шилтеме түзө аласыз, ошондой эле экинчи функциянын аргументи үчүн ЖАЛГАН (ЖАЛГАН) маанисин колдонсоңуз болот КЫЙЫР (КЫЙЫР). Стиль шилтемеси ушундайча түзүлөт R1C1. Бул мисалда биз кошумча баракчанын атын шилтемеге коштук – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4-мисал: Сандардын өзгөрүлбөгөн массивин түзүңүз
Кээде Excel формулаларында сандар массивдерин колдонуу керек болот. Төмөнкү мисалда биз В тилкесиндеги эң чоң 3 санды орточо алгыбыз келет. Сандарды D4 уячасындагыдай формулага киргизүүгө болот:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Эгер сизге чоңураак массив керек болсо, формуладагы бардык сандарды киргизгиңиз келбейт. Экинчи параметр - бул функцияны колдонуу ROW (ROW), D5 уячасына киргизилген массив формуласында аткарылгандай:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Үчүнчү вариант - бул функцияны колдонуу ROW (STRING) менен бирге КЫЙЫР (КЫЙЫР), D6 уячасындагы массив формуласы менен аткарылгандай:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Бардык 3 формуланын натыйжасы бирдей болот:
Бирок, эгер саптар барактын жогору жагына киргизилсе, экинчи формула туура эмес жыйынтыкты кайтарат, анткени формуладагы шилтемелер саптын жылышы менен бирге өзгөрөт. Эми формула үч чоң сандын орточо маанисинин ордуна 3, 4 жана 5-чоң сандардын орточо маанисин берет.
Функцияларды колдонуу КЫЙЫР (КЫЙЫР), үчүнчү формула туура сап шилтемелерин сактап, туура натыйжаны көрсөтүүнү улантат.