Microsoft Excel кейбір салқын амалдары бар және SUM және INDIRECT динамикалық ауқым формулаларын пайдаланып, сізде бар деректерді оңай басқаруға болатын екі әдіс бар.
ЖАЛҒА - INDIRECT Формулаға шолу
Excel формулаларындағы INDIRECT функциясын пайдалану формулада пайдаланылатын ұяшық сілтемелерінің ауқымын формуланы өзі өңдей алмай-ақ өзгертуді жеңілдетеді.
INDIRECT ұяшық сілтемесін OFFSET және SUM функциялары сияқты дәлел ретінде қабылдайтын бірқатар функциялармен бірге пайдалануға болады.
Соңғы жағдайда, SUM функциясы үшін дәлел ретінде INDIRECT функциясын қолдану SUM функциясы қосылатын ұяшық сілтемелерінің динамикалық ауқымын жасай алады.
INDIRECT мұны аралық орналасу арқылы жасушалардағы деректерге сілтеме жасай отырып жасайды.
Мысал: SUM - INDIRECT формуласы жалпы динамикалық мәндер ауқымына арналған
Бұл мысал жоғарыдағы суретте көрсетілген деректерге негізделген.
Төмендегі нұсқаулықтарды пайдалану арқылы жасалған SUM - INDIRECT формуласы :
= SUM (INDIRECT («D» & E1 & «: D» & E2))
Бұл формулада кірістірілген INDIRECT функциясының дәлелінде E1 және E2 ұяшықтарына сілтемелер бар. INDIRECT дәлелінің қалған бөлігімен біріктірілген осы ұяшықтардағы сандар 1 және 4 ұяшықтары D1 және D4 ұяшық сілтемелерін құрайды.
Нәтижесінде, SUM функциясымен жасалған сандар ауқымы D1-D4 ұяшықтарының ауқымындағы деректер болып табылады - бұл 50.
E1 және E2 ұяшықтарындағы сандарды өзгерту арқылы; алайда, жинақталатын ауқым оңай өзгеруі мүмкін.
Бұл мысал алдымен жоғарыдағы формуланы D1: D4 ұяшықтарындағы деректерді жинақтап, F1 ұяшығындағы формуланы өңдемей D3: D6 жиынтық ауқымын өзгертеді.
03 01
Формуланы енгізу - Опциялар
Формулаға кіру нұсқалары мыналарды қамтиды:
- жоғарыдағы формуланы F1 ұяшығына тікелей енгізу және пернетақтадағы Enter пернесін басу
- INDIRECT функциясын SUM функциясының тілқатысу терезесін пайдаланып дәлел ретінде енгізу
Excel бағдарламасындағы функциялардың көбісі синтаксис туралы алаңдамай, функцияның әрбір дәлелін бөлек жолға енгізуге мүмкіндік беретін диалогтық терезе бар.
Бұл жағдайда SUM функциясының тілқатысу терезесі белгілі бір дәрежеде формуланы жеңілдету үшін пайдаланылуы мүмкін. INDIRECT функциясы SUM ішіне кірістірілгендіктен, INDIRECT функциясы және оның дәлелдері әлі де қолмен енгізілуі керек.
Төмендегі қадамдар формулаға кіру үшін SUM тілқатысу терезесін қолданады.
Тәжірибелік деректерді енгізу
Ұяшық деректері D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Төмендегі деректерді D1 - E2 ұяшықтарына енгізіңіз
SUM - INDIRECT формуласын іске қосу - SUM функциясы тілқатысу терезесін ашу
- F1 ұяшығын басыңыз - бұл мысалдың нәтижелері көрсетілетін жерде
- Таспа мәзірінің Формулалар қойындысын нұқыңыз
- Функция ашылмалы тізімін ашу үшін таспадан Math & Trig таңдаңыз
- Функцияның тілқатысу терезесін ашу үшін тізімдегі SUM түймесін басыңыз
03 02
INDIRECT функциясын енгізу - Үлкен суретті көру үшін нұқыңыз
INDIRECT формуласы SUM функциясының аргументі ретінде енгізілуі керек.
Кірістірілген функциялардың жағдайда, Excel өзінің аргументтерін енгізу үшін екінші функцияның тілқатысу терезесін ашуға рұқсат бермейді.
INDIRECT функциясы SUM функциясының тілқатысу терезесінің Сан1 жолында қолмен енгізілуі керек.
- Диалогтық терезеде Number1 жолын басыңыз
- Келесі INDIRECT функциясын енгізіңіз: INDIRECT («D» & E1 & «: D» & E2)
- Функцияны аяқтау үшін OK түймешігін басыңыз және тілқатысу терезесін жабыңыз
- 50 саны F1 ұяшығында пайда болуы керек, себебі бұл D1-D4 ұяшықтарында орналасқан деректер үшін жалпы
- F1 ұяшығын басқанда, толық формула = SUM (INDIRECT («D» & E1 & «: D» & E2)) жұмыс парағының үстіндегі формула жолында пайда болады
INDIRECT функциясын бұзу
INDIRECT арқылы D бағанында динамикалық ауқымды жасау үшін D әрпін INDIRECT функциясының дәлелінде E1 және E2 ұяшықтарында бар сандармен біріктіруіміз керек.
Бұл келесі әрекеттер арқылы жүзеге асырылады:
- амперсанд ( & ) мәтіндік деректерді (бұл жағдайда D әрпін) ұялы сілтеме арқылы (E1 және E2) біріктіру немесе біріктіру үшін пайдаланылады,
- Бұдан әрі, ұяшыққа сілтеме жасалатын мәтіндік деректер қос тырнақшамен ( «» )
- Соңында, нүктенің соңғы нүктелері қос нүктемен бөлінеді (:)
Сондықтан диапазонның бастау нүктесі таңбалармен анықталады: «D» & E1 .
Екінші таңбалар жиынтығы: «: D» & E2 қос нүктені соңғы нүктемен біріктіреді. Бұл жасалады, себебі нүкте мәтіндік сипатта болады және сондықтан тырнақшаның ішінде болуы керек.
Ортасындағы үшінші амперсанд екі бөлікті бір дәлелге келтіру үшін пайдаланылады :
«D» & E1 & «: D» & E203 03
SUM функциясының ауқымын динамикалық түрде өзгерту
Осы формуланың толық нүктесі функцияның дәлелін өңдемей, SUM функциясының ауқымын өзгертуді жеңілдету болып табылады.
Формулада INDIRECT функциясын қосу арқылы E1 және E2 ұяшықтарындағы сандарды өзгерту SUM функциясы арқылы оқылған ұяшықтар ауқымын өзгертеді.
Жоғарыдағы суретте көрсетілгендей, бұл сондай-ақ F1 ұяшығында орналасқан формуланың жауабына әкеледі, себебі ол деректердің жаңа ауқымын құрайды.
- E1 ұяшығын басыңыз
- 3 нөмірін теріңіз
- Пернетақтадағы Enter пернесін басыңыз
- E2 ұяшығын басыңыз
- 6 нөмірін теріңіз
- Пернетақтадағы Enter пернесін басыңыз
- F1 ұяшығындағы жауап 90-ға дейін өзгеруі керек - бұл D3-D6 ұяшықтарындағы сандардың жалпы саны
- B1 және B2 ұяшықтарының мазмұнын 1 және 6 арасындағы кез келген сандарға өзгерту арқылы формуланы бұдан әрі сынап көріңіз
INDIRECT және #REF! Қате мәні
#REF! F1 ұяшығында қате мәні пайда болады, егер INDIRECT функциясының дәлелі:
- ұялы сілтеме дұрыс емес
- басқа жұмыс кітабына сыртқы сілтеме бар және бұл жұмыс кітабы ашық емес
- жұмыс парағының шегінен тыс ұяшық ауқымын білдіреді (жол 1,048,576 немесе XFD бағаны)