Tähän mennessä olemme oppineet laskemaan yhteen taulukon koko vastaavan sarakkeen Excelissä. Mutta kuinka laskemme arvot, kun meidän on vastattava sarake ja rivi. Tässä artikkelissa opimme tekemään vastaavien rivien ja sarakkeiden summan.
Tähän on kaksi kaavaa, mutta tarkastellaan ensin skenaariota.
Tässä minulla on taulukko, joka kirjaa työntekijöiden myynnin eri kuukausina. Työntekijöiden nimi voi toistaa. Katso alla oleva kuva:
Meidän on saatava toukokuun summa, jossa myyjä on Donald.
Menetelmä 1: Yhteenveto sarakkeiden otsikoista ja rivien otsikoista SUMPRODUCT -toiminnon avulla.
The SUMPRODUCT -toiminto on monipuolisin toiminto, kun on kyse summien laskemisesta ja vaikeista kriteereistä. Yleinen funktio, joka lasketaan yhteen sarakkeen ja rivin mukaan, on seuraava:
| = SUMPRODUCT ((sarakkeet)*(column_headers = column_heading)*(row_headers = row_heading) |
Sarakkeet:Se on niiden sarakkeiden 2-ulotteinen alue, jotka haluat laskea yhteen. Se ei saa sisältää otsikoita. Yllä olevassa taulukossa on C3: N7.
column_headers:Se on otsikon aluesarakkeet jonka haluat summaa. Yllä olevissa tiedoissa se on C2: N2.
column_heading: Se on otsikko, jonka haluat yhdistää. Yllä olevassa esimerkissä se on kohdassa B13.
Käytämme viivettä ilman kaavaa.
rivin_otsikot:Se on otsikon aluerivit jonka haluat summaa. Yllä olevissa tiedoissa se on B3: B10.
rivin_otsikko: Se on otsikko, jonka haluat yhdistää riveihin. Yllä olevassa esimerkissä se on F13: ssa.
Käytämme viivettä ilman kaavaa.
Kirjoita tämä kaava soluun D13 ja anna Excelin tehdä taikuutta (taikuutta ei ole olemassa)…
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Tämä palauttaa arvon:
Nyt kun muutat kuukautta tai myyjää, summa muuttuu rivin otsikon ja sarakkeen otsikon mukaisesti.
Kuinka se toimii?
Tämä yksinkertainen looginen logiikka.
(C2: N2 = B13): Tämä lausunto palauttaa joukon TOSI ja EPÄTOSI. Kaikilla sarakkeen vastaavilla arvoilla on tosi ja muilla epätosi. Tässä tapauksessa meillä on vain yksi tosi, koska alue C2: N2 sisältää vain yhden esiintymän toukokuussa 5th Sijainti.
(B3: B10 = E13): Tämä toimii samalla tavalla kuin yllä ja palauttaa taulukon TOSI ja EPÄTOSI. Kaikilla vastaavilla arvoilla on TOSI ja muilla on EPÄTOSI. Tässä tapauksessa meillä on 2 TOSIA, koska alue B3: B10 sisältää kaksi esiintymää "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Nyt kerromme lausekkeilla palautetut taulukot. Tämä toteuttaa ja logiikka ja saamme joukon 1s ja 0s. Nyt meillä on 2D -taulukko, joka sisältää 2 1s ja lepo 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Lopuksi kerromme 2D -taulukon 2D -taulukolla. Se palauttaa jälleen joukon 0 ja ehtoja vastaavat luvut.
Lopuksi, SUMPRODUCT -toiminto laskee yhteen taulukon, joka johtaa haluttuun lähtöön.
Menetelmä 2: Yhteenveto sarakkeiden otsikoista ja rivien otsikoista SUM ja IF -funktion avulla
Yleinen kaava vastaavan rivin ja sarakkeen yhteenlaskemiseen SUM- ja IF -Excel -funktion avulla on:
| = SUMMA (JOS (column_headers = column_heading, IF (row_headers = row_heading, column))) |
Kaikki muuttujat ovat samat kuin edellä selitetyssä menetelmässä. Tässä niitä on vain käytettävä eri järjestyksessä.
Kirjoita tämä kaava soluun D13:
| = SUMMA (JOS (C2: N2 = B13, JOS (B3: B10 = E13, C3: N10))) |
Tämä palauttaa oikean vastauksen. Katso alla oleva kuvakaappaus:
Kuinka se toimii?
Logiikka on sama kuin ensimmäisellä SUMPRODCUT -menetelmällä, vain mekanismi on erilainen. Jos selitän sen lyhyesti, sisäinen IF -funktio palauttaa 2D -taulukon, jonka koko on sama kuin taulukon. Tämä taulukko sisältää kahden vastaavan rivin määrän. Sitten sisäinen IF-toiminto vastaa tämän taulukon kahden sarakkeen otsikoita ja palauttaa 2D-matriisin, joka sisältää vain sarakkeen ja otsikon vastaavat numerot. Kaikki muut taulukon elementit ovat EPÄTOSIA.
Lopuksi SUM -funktio summaa tämän taulukon ja saamme summan.
Joten kyllä kaverit, näin voit tiivistää Excel -taulukon vastaavat rivit ja sarakkeet. Toivottavasti se oli selittävä ja hyödyllinen sinulle. Jos sinulla on epäilyksiä tästä aiheesta tai sinulla on muita excel/VBA -aiheisia epäilyksiä, kysy alla olevasta kommenttiosasta.
Kuinka laskea sarake Excelissä vastaavan otsikon avulla | Jos haluat saada sarakkeen summan käyttämällä vain sarakkeen nimeä, voit tehdä tämän kolmella helpolla tavalla Excelissä. SUMPRODUCT -menetelmän syntaksi summan vastaavuuden sarakkeessa on:
SUMIF ja 3D -viite Excelissä |Hauska tosiasia on, että normaali Excel 3D -viittaus ei toimi ehdollisten toimintojen, kuten SUMIF -funktion, kanssa. Tässä artikkelissa opimme saamaan 3D -viittauksen toimimaan SUMIF -toiminnon kanssa.
Suhteellinen ja ehdoton viite Excelissä | Viittaaminen Excelissä on tärkeä aihe jokaiselle aloittelijalle. Jopa kokeneet Excel -käyttäjät tekevät virheitä viittauksessa.
Dynaamisen laskentataulukon viite | Anna viitearkit dynaamisesti käyttämällä Excelin INDIRECT -toimintoa. Tämä on yksinkertaista…
Viittausten laajentaminen Excelissä | Laajeneva viite laajenee, kun sitä kopioidaan alas tai oikealle. Käytämme $ -merkkiä ennen saraketta ja rivinumeroa. Tässä yksi esimerkki…
Kaikki absoluuttisesta viittauksesta | Excelin oletusviitetyyppi on suhteellinen, mutta jos haluat, että solujen ja alueiden viittaus on ehdoton, käytä $ -merkkiä. Tässä on kaikki ehdoton viittaus Excelissä.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä nopeuttavat työtäsi Excelissä.
VLOOKUP -toiminto Excelissä | Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietojasi tietyn arvon laskemiseksi. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.