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