Et af de almindelige spørgsmål på opslagstavlen er, hvordan man bruger SumIf-funktionen under to forskellige betingelser. Desværre er svaret, at SumIf ikke kan håndtere to forskellige forhold.
For at gøre to betingelser skal du bruge en ret kompliceret matrixformel. Tilføjelsesprogrammet til betinget sumguide giver dig mulighed for let at indtaste disse komplicerede formler.
Her er et Excel-regneark med kolonner til produkt, salgsrepræsentant og salg. Dataene findes i cellerne A2: C29.
Hvis du vil summe salget, fungerer en simpel SUM () -funktion. =SUM(C2:C29).
Mange Excellers finder SumIf-funktionen. Ved hjælp af denne funktion er det ret let at finde ud af det samlede salg for produkt ABC.=SUMIF(A2:A29,E2,C2:C29)
Det er også let at finde ud af det samlede salg foretaget af salgsrepræsentant Joe med =SUMIF(B2:B29,E2,C2:C29).
Du antager derefter, at det er muligt at finde ud af det samlede salg af produkt ABC foretaget af Joe. Der er dog ingen måde at gøre dette med SumIf-funktionen. Det viser sig, at du skal bruge et ret komplekst array eller CSE-formel.
Lad os indse det - Sum-formlen er Excel 101. SumIf-formlen er ikke langt bagud i kompleksitet. Imidlertid er CSE-formlen til at beregne det samlede ABC-salg foretaget af Joe nok til at få selv mit hoved til at dreje.
Den gode nyhed - Microsoft tilbyder guiden Betinget sum, der tillader endda en nybegynder at indtaste komplekse betingede formler baseret på 1, 2 eller flere betingelser. Guiden Betinget sum er et tilføjelsesprogram. For at tilføje denne funktionalitet til Excel skal du gå til menuen Funktioner og vælge Tilføjelsesprogrammer. Marker afkrydsningsfeltet ud for guiden Betinget sum i dialogboksen Tilføjelsesprogrammer, og vælg OK. Det er muligt, at du muligvis har brug for din installations-cd på dette tidspunkt, fordi Microsoft ikke inkluderer guiden i standardinstallationen.
Når tilføjelsesprogrammet er aktiveret med succes, vil der være et betinget sum … valg nær bunden af menuen Funktioner.
Vælg en enkelt celle i dit datasæt, og vælg Værktøjer - Betinget sum. Forudsat at dine data er pænt formateret med en enkelt række overskrifter, vil Excel gætte korrekt på rækkevidden af dine data. Vælg Næste.
I trin 2 skal du vælge den kolonne, der skal summeres. I dette tilfælde gættede guiden allerede, at du vil sammenfatte den første (og eneste) numeriske kolonne - Salg. I midten af dialogboksen er der tre rullemenuer. Disse er tilfældet med den første betingelse - Produktet er lig ABC, så vælg knappen Tilføj tilstand.
Derefter kan du tilføje din anden betingelse. I dette tilfælde vil du angive, at salgsrepræsentanten er Joe. Vælg pilen til den første rullemenu. Excel tilbyder en alfabetisk liste over de tilgængelige kolonnenavne. Vælg salgsrepræsentant.
Centreringsrullelisten er korrekt, men for fuldstændighed her kan du se, at du kunne have valgt lige, mindre end, større end, mindre end eller lige, større end eller lige eller ikke lig.
Fra den tredje rullemenu skal du vælge Joe.
Vælg knappen Tilføj betingelse.
Du er nu klar til at gå til trin 3. Tryk på knappen Næste.
I trin 3 har du to valg. I det første valg vil guiden indtaste en enkelt formel med værdierne "ABC" og "Joe" hårdkodet i formlen. Det giver dig svaret, men der er ingen mulighed for let at ændre formlen. Med andet valg opretter Excel en ny celle med værdien "ABC" og en ny celle med værdien "Joe". En tredje celle indeholder formlen, der udgør en betinget sum baseret på disse to værdier. Med denne mulighed kan du skrive nye værdier i cellerne for at se de samlede XYZ'er, der er solgt af Adam.
Guiden spørger derefter, hvor du vil have værdien for ABC. Vælg en celle, og vælg Næste. Gentag, når guiden beder dig om at vælge en celle til Joe og formlen.
Når du vælger Afslut i sidste trin, opretter Excel en lidt anden (men gyldig) version af CSE-formlen.
Denne formel beregner, at Joe solgte $ 33.338 i ABC.
Hvis du ændrer produktinputcellen fra ABC til DEF, beregnes formlen igen for at vise, at Joe solgte 24.478 $ DEF.
Guiden Betinget sum sætter komplekse formler inden for rækkevidde af alle Excel-ejere.
Yderligere Information:Hvis du vil oprette en tabel, der viser salget af hvert produkt af hver salgsrepræsentant, er der nogle specielle "pleje og fodring", som du bliver nødt til at vide om disse formler. Indtast hver salgsrepræsentant øverst i intervallet. Skriv hvert produkt ned i den venstre kolonne i området. Rediger formlen fra guiden. På billedet nedenfor peger formlen på et produktet i celle E6. Denne reference skal virkelig være $ E6. Hvis du lader henvisningen være E6 og kopierer formlen til kolonne G, ser formlen på F6 i stedet for E6, og dette ville være forkert. Tilføjelse af et dollartegn før E i E6 sørger for, at formlen altid ser på produktet i kolonne E. Formlen peger også på en salgsrepræsentant i celle F5. Denne reference skal virkelig være F $ 5. Hvis du forlod referencen som F5 og kopierede ned til række 7,F5-referencen ændres til F6, og dette er ikke korrekt. Tilføjelse af et dollartegn før række nummeret låser række nummeret, og henvisningen vil altid pege på række 5.
I redigeringstilstand (vælg cellen og tryk på F2 for at redigere), skriv en $ før E. Indtast et dollartegn før 5 i F5. Tryk ikke på Enter endnu!
Denne formel er en særlig formel. Hvis du trykker på Enter, får du et 0, som ikke er korrekt.
I stedet for at skrive Enter skal du holde Ctrl- og Shift-tasterne nede, mens du trykker på Enter. Denne magiske kombination af C trl + S hift + E nter er derfor, jeg kalder disse CSE-formler.
Der er en sidste overvejelse, før du kopierer formlen til resten af tabellen. Din tilbøjelighed kan være at kopiere F6 og indsætte på F6: G8. Hvis du prøver dette, giver Excel dig den gådefulde meddelelse "Du kan ikke ændre en del af en matrix". Excel klager over, at du ikke kan indsætte en CSE-formel i et interval, der indeholder den originale CSE-formel.
Det er let at omgå dette. Kopier F6. Indsæt til F7: F8.
Kopiér F6: F8. Indsæt til G6: G8. Du vil have en tabel med CSE-formler, der viser totaler baseret på to betingelser.
