И така, вече научихме какво е 3D справка в Excel. Забавният факт е, че нормалните Excel 3D препратки не работят с условни функции, като функцията SUMIF. В тази статия ще научим как да накараме 3D референции да работят с функцията SUMIF.
Общата формула за SUMIF с 3D препратка в Excel
Изглежда сложно, но не е (толкова).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Criteria_range"), критерии, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" обхват_на_име_на_листове "'": "Това е именован диапазон, който съдържа имената на листата. Това е много важно.
"range_ range":Това е текстовото позоваване на критерии, съдържащи диапазон. (Трябва да е еднакво във всички листове към 3-D справочна работа.)
критерии:Това е просто условието, което искате да поставите за сумиране. Това може да бъде текст или препратка към клетка.
"sum_range":Това е текстовото позоваване на общия диапазон. (Трябва да е еднакво във всички листове към 3-D справочна работа.)
Стига с теорията, нека 3D препратките с функция SUMIF работят.
Пример: Сума по региони от множество листове, използващи 3D препратки на Excel:
Взимаме същите данни, които взехме в простия пример за 3D препратки. В този пример имам пет различни листа, които съдържат подобни данни. Всеки лист съдържа данни за един месец. В основния лист искам сумата от единици и събиране по региони от всички листове. Нека първо го направим за Units. Единиците са в диапазона D2: D14 във всички листове.
Сега, ако използвате нормалното 3D препращане с функцията SUMIF,
= SUMIF (януари: април! A2: A14, майстор! B4, януари: април! D2: D14)
Той ще върне #VALUE! грешка. Така че не можем да го използваме. Ще използваме общата формула, спомената по -горе.
Използвайки горната обща 3D референтна формула SUMIF на excel, напишете тази формула в клетка C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Months & "'!" & "D2: D14"))) |
Тук месеци е именован диапазон, който съдържа имената на листове. Това е от решаващо значение.
Когато натиснете Enter, получавате точния си изход.
Как работи?
Ядрото на формулата е функцията INDIRECT и именен диапазон. Тук низът"'" & Месеци & "'!" & "A2: A14"се превежда в масив от препратки към диапазони на всеки лист в именен диапазон.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Този масив съдържа текстова справкана диапазони, а не на действителните диапазони. Тъй като това е текстова справка, тя може да се използва от функцията INDIRECT, за да ги преобразува в действителни диапазони. Това се случва и за двете функции INDIRECT. След разрешаване на текстовете вътре във функциите INDIRECT (задръжте здраво), формулата изглежда така:
| = SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Master! B3, INDIRECT ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}))) |
Сега функцията SUMIF влиза в действие (не ИНДИРЕКТНАТА, както може би се досещате). Условието е съобразено с първия диапазон"" Ян "! A2: A14". Тук функцията INDIRECT работи динамично и преобразува този текст в действителния диапазон (затова, ако първо се опитате да разрешите INDIRECT с помощта на клавиша F9, няма да получите резултата). След това обобщава съвпадащите стойности в диапазона"" Ян "! D2: D14".Това се случва за всеки диапазон в масива. И накрая, ще имаме масив, върнат от функцията SUMIF.
| = СУМПРОДУКТ ({97; 82; 63; 73}) |
Сега SUMPRODUCT прави това, което прави най -добре. Той обобщава тези стойности и получаваме, че нашата функция 3D SUMIF работи.
Така че да, момчета, ето как можете да постигнете 3D SUMIF функция. Това е малко сложно, съгласен съм с това. В тази 3D формула има много възможности за грешки. Предлагам ви да използвате функцията SUMIF на всеки лист в определена клетка и след това да използвате нормалните 3D препратки за сумиране на тези стойности.
Надявам се да съм бил достатъчно обяснителен. Ако имате някакви съмнения относно препращането на Excel към която и да е друга заявка, свързана с Excel/VBA, попитайте в секцията за коментари по -долу.
Относителна и абсолютна справка в Excel | Препратките в Excel са важна тема за всеки начинаещ. Дори опитни потребители на Excel правят грешки при препратките.
Справка за динамичен работен лист | Давайте справочни листове динамично, като използвате функцията INDIRECT на excel. Това е просто…
Разширяване на препратки в Excel | Разширяващата се справка се разширява, когато се копира надолу или надясно. За целта използваме знака $ преди номера на колоната и реда. Ето един пример…
Всичко за абсолютната справка | Типът позоваване по подразбиране в excel е относителен, но ако искате препратката към клетките и диапазоните да е абсолютна, използвайте знака $. Ето всички аспекти на абсолютните препратки в Excel.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.