We hebben dus al geleerd wat 3D-referentie is in Excel. Het leuke feit is dat de normale Excel 3D-referentie niet werkt met voorwaardelijke functies, zoals de SUMIF-functie. In dit artikel zullen we leren hoe u 3D-referentie kunt laten werken met de SUMIF-functie.
De generieke formule voor SUMIF met 3D-referentie in Excel
Het ziet er ingewikkeld uit, maar is het niet (zoveel).
| =SOMPRODUCT(SUMIF(INDIRECT("'"&name_range_of_sheet_names&"'!" & "criteria_range"),criteria,INDIRECT("'"&name_range_of_sheet_names&"'!" &"sum_range"))) |
"'"name_range_of_sheet_names"'":Het is een benoemd bereik dat de bladnamen bevat. Dit is erg belangrijk.
"criteria_bereik":Het is de tekstverwijzing van criteria die bereik bevatten. (Het moet hetzelfde zijn in alle bladen naar 3D-naslagwerk.)
criteria:Het is gewoon de voorwaarde die je wilt stellen voor het optellen. Het kan een tekst- of celverwijzing zijn.
"sum_range":Het is de tekstreferentie van het sombereik. (Het moet hetzelfde zijn in alle bladen naar 3D-naslagwerk.)
Genoeg van de theorie, laten we 3D-referentie met de SUMIF-functie werken.
Voorbeeld: Som per regio van meerdere bladen met behulp van 3D-referentie van Excel:
We nemen dezelfde gegevens als in het eenvoudige 3D-referentievoorbeeld. In dit voorbeeld heb ik vijf verschillende bladen die vergelijkbare gegevens bevatten. Elk blad bevat de gegevens van een maand. In het hoofdblad wil ik de som van de eenheden en de verzameling per regio van alle bladen. Laten we het eerst voor Units doen. De eenheden zijn in het bereik D2:D14 in alle bladen.
Als u nu de normale 3D-referentie met SUMIF-functie gebruikt,
=SOM.ALS(Jan:Apr!A2:A14,Master!B4,Jan:Apr!D2:D14)
Het zal #WAARDE teruggeven! fout. We kunnen het dus niet gebruiken. We zullen de hierboven genoemde generieke formule gebruiken.
Gebruik de bovenstaande algemene 3D-referentie SUMIF-formule van Excel en schrijf deze formule in cel C3:
| =SOMPRODUCT(SUMIF(INDIRECT("'"&Maanden&"'!" & "A2:A14"),Master!B3,INDIRECT("'"&Maanden&"'!" &"D2:D14"))) |
Hier maanden is een benoemd bereik dat de namen van bladen bevat. Dit is cruciaal.
Wanneer u op enter drukt, krijgt u uw exacte uitvoer.
Hoe werkt het?
De kern van de formule is de INDIRECT-functie en de genoemd bereik. Hier het touwtje"'"&Maanden&"'!" & "A2:A14"vertaalt naar een reeks bereikreferenties van elk blad in de genoemd bereik.
| {"'Jan'!D2:D14";"'Feb'!D2:D14";"'Mar'!D2:D14";"'Apr'!D2:D14"} |
Deze array bevat de tekstverwijzingvan reeksen, niet de werkelijke reeksen. Omdat het nu een tekstverwijzing is, kan het door de INDIRECT-functie worden gebruikt om ze om te zetten in werkelijke bereiken. Dit gebeurt voor beide INDIRECT-functies. Na het oplossen van de teksten in de INDIRECT-functies (vasthouden), ziet de formule er als volgt uit:
| =SOMPRODUCT(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"}))) |
Nu komt de SUMIF-functie in actie (niet de INDIRECT, zoals je misschien al geraden hebt). De voorwaarde komt overeen met het eerste bereik"'Jan'!A2:A14". Hier werkt de INDIRECT-functie dynamisch en converteert deze tekst naar het werkelijke bereik (dat is de reden waarom als je INDIRECT eerst probeert op te lossen met de F9-toets, je het resultaat niet krijgt). Vervolgens somt de overeenkomende waarden in het bereik op"'Jan'!D2:D14".Dit gebeurt voor elk bereik in de array. Ten slotte zullen we een array hebben die wordt geretourneerd door de SUMIF-functie.
| =SOMPRODUCT({97;82;63;73}) |
Nu doet het SOMPRODUCT waar het goed in is. Het vat deze waarde samen en we krijgen onze 3D SUMIF-functie werkend.
Dus ja jongens, dit is hoe je een 3D SUMIF-functie kunt bereiken. Dit is een beetje ingewikkeld, daar ben ik het mee eens. Er is veel ruimte voor fouten in deze 3D-formule. Ik zou willen voorstellen dat je de SUMIF-functie op elk blad in een bepaalde cel gebruikt en vervolgens de normale 3D-referentie gebruikt om die waarden samen te vatten.
Ik hoop dat ik voldoende uitleg heb gegeven. Als je twijfels hebt over Excel die verwijst naar een andere Excel/VBA-gerelateerde vraag, vraag het dan in de opmerkingen hieronder.
Relatieve en absolute referentie in Excel | Verwijzen in Excel is een belangrijk onderwerp voor elke beginner. Zelfs ervaren Excel-gebruikers maken fouten bij het verwijzen.
Dynamische werkbladreferentie | Geef referentiebladen dynamisch met behulp van de INDIRECT-functie van Excel. Dit is eenvoudig…
Referenties uitbreiden in Excel | De uitbreidende referentie wordt groter wanneer deze naar beneden of naar rechts wordt gekopieerd. Hiervoor gebruiken we het $-teken voor de kolom en het rijnummer. Hier is een voorbeeld…
Alles over absolute referentie | Het standaardverwijzingstype in Excel is relatief, maar als u wilt dat de verwijzing van cellen en bereiken absoluut is, gebruikt u het $ -teken. Hier zijn alle aspecten van absolute verwijzingen in Excel.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
De VERT.ZOEKEN-functie in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.