Дакле, већ смо научили шта је 3Д референца у Екцелу. Забавна је чињеница да уобичајено Екцел 3Д референцирање не ради са условним функцијама, попут функције СУМИФ. У овом чланку ћемо научити како да 3Д референцирање ради са функцијом СУМИФ.
Општа формула за СУМИФ са 3Д референцом у Екцелу
Изгледа компликовано, али није (толико).
| = СУМПРОДУЦТ (СУМИФ (ИНДИРЕЦТ ("'" & наме_ранге_оф_схеет_намес & "'!" & "Ранге_ранге"), цритериа, ИНДИРЕЦТ ("'" & наме_ранге_оф_схеет_намес & "'!" & "Сум_ранге"))) |
"'" наме_ранге_оф_схеет_намес "'":То је именовани опсег који садржи називе листова. Ово је веома важно.
"опсег_критеријума":То је текстуална референца критеријума који садрже опсег. (Требало би да буде исто у свим листовима за 3-Д референтно дело.)
критеријуми:То је једноставно услов који желите да поставите за сабирање. То може бити текст или референца ћелије.
"сум_ранге":То је текстуална референца опсега збира. (Требало би да буде исто на свим листовима за 3-Д референтно дело.)
Доста је било теорије, идемо на 3Д референцирање са функцијом СУМИФ.
Пример: Збир по регионима из више листова помоћу 3Д референцирања програма Екцел:
Узимамо исте податке које смо узели у једноставном 3Д референцирању. У овом примеру имам пет различитих листова који садрже сличне податке. Сваки лист садржи податке за месец дана. У главном листу желим збир јединица и збирку по регионима са свих листова. Урадимо то прво за Јединице. Јединице су у распону Д2: Д14 на свим листовима.
Сада, ако користите нормалну 3Д референцу са функцијом СУМИФ,
= СУМИФ (Јан: Апр! А2: А14, Мастер! Б4, Јан: Апр! Д2: Д14)
Вратиће #ВАЛУЕ! грешка. Тако да га не можемо користити. Користићемо горе наведену генеричку формулу.
Користећи горњу генеричку 3Д референцирајућу СУМИФ формулу екцела, упишите ову формулу у ћелију Ц3:
| = СУМПРОДУЦТ (СУМИФ (ИНДИРЕЦТ ("'" & Монтхс & "'!" & "А2: А14"), Мастер! Б3, ИНДИРЕЦТ ("'" & Монтхс & "'!" & "Д2: Д14"))) |
Ево месеци је именовани опсег који садржи називе листова. Ово је кључно.
Када притиснете ентер, добићете тачан излаз.
Како то функционише?
Језгро формуле су ИНДИРЕКТНА функција и именовани опсег. Овде је низ"'" & Месеци & "'!" & "А2: А14"преводи у низ референци опсега сваког листа у именовани опсег.
| {"'Јан'! Д2: Д14"; "'Феб'! Д2: Д14"; "'Мар'! Д2: Д14"; "'Апр'! Д2: Д14"} |
Овај низ садржи упућивање на текстопсега, а не стварних опсега. Пошто је то текстуална референца, функција ИНДИРЕЦТ га може користити за претварање у стварне опсеге. То се дешава за обе функције ИНДИРЕЦТ. Након разрешавања текстова унутар функција ИНДИРЕЦТ (држите се чврсто), формула изгледа овако:
| = СУМПРОДУЦТ (СУМИФ (ИНДИРЕКТНО (("" Јан '! А2: А14 ";"' Феб '! А2: А14 ";"' Мар '! А2: А14 ";"' Апр '! А2: А14 "}) , Мастер! Б3, ИНДИРЕЦТ ({"'Јан'! Д2: Д14"; "'Феб'! Д2: Д14"; "'Мар'! Д2: Д14"; "'Апр'! Д2: Д14"}))) |
Сада функција СУМИФ ступа у акцију (не ИНДИРЕКТНА, као што сте можда претпоставили). Услов је усклађен са првим опсегом"'Јан'! А2: А14". Овде функција ИНДИРЕЦТ ради динамички и претвара овај текст у стварни опсег (зато ако прво покушате да решите ИНДИРЕЦТ помоћу тастера Ф9, нећете добити резултат). Затим сумира подударне вредности у опсегу"'Јан'! Д2: Д14".То се дешава за сваки опсег у низу. Коначно, имат ћемо низ који враћа функција СУМИФ.
| = СУМПРОДУЦТ ({97; 82; 63; 73}) |
Сада СУМПРОДУЦТ ради оно што најбоље ради. Збраја ове вредности и наша 3Д СУМИФ функција ради.
Па да момци, овако можете постићи 3Д СУМИФ функцију. Ово је мало сложено, слажем се с тим. У овој 3Д формули постоји много простора за грешке. Предлажем да користите функцију СУМИФ на сваком листу у одређеној ћелији, а затим да користите уобичајене 3Д референце за сумирање тих вредности.
Надам се да сам довољно објаснио. Ако имате било каквих недоумица у вези са Екцел -ом позивањем на било који други Екцел/ВБА упит, питајте у одељку за коментаре испод.
Релативна и апсолутна референца у Екцелу | Референцирање у екцелу важна је тема за сваког почетника. Чак и искусни екцел корисници греше у референцирању.
Референца динамичког радног листа | Дајте референтне листове динамички користећи ИНДИРЕЦТ функцију програма Екцел. Ово је једноставно…
Проширивање референци у Екцелу | Проширујућа референца се шири када се копира надоле или надесно. За то користимо знак $ пре броја колоне и реда. Ево једног примера…
Све о апсолутним референцама | Подразумевани тип референце у екцелу је релативан, али ако желите да референца ћелија и опсега буде апсолутна, користите знак $. Ево свих аспеката апсолутног позивања у Екцелу.
Популарни чланци:
50 Екцел пречица за повећање продуктивности | Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Функција ВЛООКУП у Екцелу | Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
ЦОУНТИФ у програму Екцел 2016 | Бројте вредности са условима користећи ову невероватну функцију. Не морате филтрирати податке да бисте рачунали одређену вредност. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел | Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.