Så vi har redan lärt oss vad 3D -referens är i Excel. Det roliga faktum är att den normala Excel 3D -referensen inte fungerar med villkorade funktioner, som SUMIF -funktionen. I den här artikeln lär vi oss hur man får 3D -referenser att arbeta med SUMIF -funktionen.
Den generiska formeln för SUMIF med 3D -referens i Excel
Det ser komplicerat ut men det är inte (så mycket).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Criteria_range"), criteria, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" name_range_of_sheet_names "'":Det är ett namngivet område som innehåller bladnamnen. Det här är väldigt viktigt.
"criteria_range":Det är textreferensen för kriterier som innehåller intervall. (Den bör vara densamma i alla ark för 3D-referensarbete.)
kriterier:Det är helt enkelt villkoret som du vill sätta för summering. Det kan vara en text- eller cellreferens.
"sum_range":Det är textreferensen för summan. (Den bör vara densamma i alla ark för 3D-referensarbete.)
Nog om teorin, låt oss 3D referera med SUMIF -funktionen fungerar.
Exempel: Summa efter region från flera ark med 3D -referens i Excel:
Vi tar samma data som vi tog i det enkla 3D -referensexemplet. I det här exemplet har jag fem olika blad som innehåller liknande data. Varje blad innehåller en månads data. I huvudarket vill jag ha summan av enheter och samling efter region från alla blad. Låt oss göra det för enheter först. Enheterna ligger i intervallet D2: D14 i alla blad.
Om du nu använder den normala 3D -referensen med SUMIF -funktionen,
= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)
Det kommer tillbaka #VÄRDE! fel. Så vi kan inte använda den. Vi kommer att använda den generiska formeln som nämns ovan.
Använd ovanstående generiska 3D -referens SUMIF -formel för excel, skriv denna formel i cell C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Months & "'!" & "D2: D14")))) |
Här månader är ett namngivet område som innehåller namnen på blad. Detta är avgörande.
När du trycker på enter får du din exakta utmatning.
Hur fungerar det?
Kärnan i formeln är INDIRECT -funktionen och namngivet intervall. Här strängen"'" & Månader & "'!" & "A2: A14"översätter till en rad intervallreferenser för varje ark i namngivet intervall.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Denna matris innehåller textreferensav intervall, inte de faktiska intervallerna. Eftersom det nu är en textreferens kan den användas av INDIRECT -funktionen för att konvertera dem till faktiska intervall. Detta händer för båda INDIRECT -funktionerna. Efter att ha löst texterna i INDIRECT -funktionerna (håll hårt) ser formeln ut så här:
| = 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"})))) |
Nu aktiveras SUMIF -funktionen (inte INDIRECT, som du kanske har gissat). Villkoret matchas till det första intervallet"'Jan'! A2: A14". Här fungerar INDIRECT -funktionen dynamiskt och omvandlar denna text till det faktiska intervallet (det är därför som du inte får resultatet om du försöker lösa INDIRECT först med F9 -tangenten). Därefter sammanfattar de matchade värdena i intervallet"'Jan'! D2: D14".Detta händer för varje intervall i matrisen. Slutligen kommer vi att ha en array som returneras av SUMIF -funktionen.
| = SUMPRODUKT ({97; 82; 63; 73}) |
Nu gör SUMPRODUKTEN vad den gör bäst. Det summerar detta värde och vi får vår 3D SUMIF -funktion att fungera.
Så ja killar, så här kan du uppnå en 3D SUMIF -funktion. Det här är lite komplext, jag håller med om det. Det finns mycket utrymme för fel i denna 3D -formel. Jag föreslår att du använder SUMIF -funktionen på varje ark i en bestämd cell och sedan använder den normala 3D -referensen för att summera dessa värden.
Jag hoppas att jag var tillräckligt förklarande. Om du har några tvivel om excel som hänvisar till någon annan Excel/VBA -relaterad fråga, fråga i kommentarfältet nedan.
Relativ och absolut referens i Excel | Att referera i excel är ett viktigt ämne för varje nybörjare. Även erfarna excelanvändare gör misstag när de refererar.
Dynamisk kalkylbladshänvisning | Ge referensblad dynamiskt med hjälp av Excel -indirektfunktionen. Det här är enkelt …
Utöka referenser i Excel | Den expanderande referensen expanderar när den kopieras nedåt eller åt höger. Vi använder $ -tecknet före kolumn- och radnumret för att göra det. Här är ett exempel …
Allt om absolut referens | Standardreferenstypen i excel är relativ, men om du vill att referensen för celler och intervall ska vara absolut använder du $ -tecknet. Här är alla aspekter av absolut referens i Excel.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
VLOOKUP -funktionen i Excel | Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.