Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip naudoti funkcijas SUMPRODUCT ir SUMIFS, norint susumuoti duomenis, atitinkančius tam tikrus kriterijus keliuose „Excel“ ir „Google“ skaičiuoklių lapuose.
Įprasta suma keliuose lapuose
Kartais jūsų duomenys gali apimti kelis „Excel“ failo darbalapius. Tai būdinga periodiškai renkamiems duomenims. Kiekviename darbaknygės lape gali būti nustatyto laikotarpio duomenų. Mes norime formulės, kuri apibendrina duomenis, esančius dviejuose ar daugiau lapų.
Funkcija SUM leidžia lengvai apibendrinti kelių lapų duomenis, naudojant a 3D nuoroda:
| 1 | = SUM (1 lapas: 2 lapas! A1) |
Tačiau tai neįmanoma naudojant funkciją SUMIFS. Vietoj to turime naudoti sudėtingesnę formulę.
Suma, jei keliuose lapuose
Šis pavyzdys apibendrins Planuojamų pristatymų skaičius kiekvienam Klientas per kelis darbalapius, kurių kiekviename yra su kitu mėnesiu susiję duomenys, naudojant SUMIFS, SUMPRODUCT ir INDIRECT funkcijas:
| 1 | = SUMPRODUKTAS (SUMIFS (NETIESIOGINIS ("'" & F3: F6 & "'!" & "D3: D7"), NETIESIOGINIS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Eikime per šią formulę.
1 veiksmas: sukurkite SUMIFS formulę tik 1 įvesties lapui:
Norėdami sudėti sumą, naudojame funkciją SUMIFS Planuojamų pristatymų skaičius pagal Klientas vienam įvesties duomenų lapui:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
2 veiksmas: pridėkite lapo nuorodą prie formulės
Formulės rezultatas išlieka tas pats, tačiau nurodome, kad įvesties duomenys yra vadinami lape '2 žingsnis'
| 1 | = SUMIFS („2 žingsnis“! D3: D7, „2 žingsnis“! C3: C7, H3) |
3 žingsnis: įdėkite funkciją „SUMPRODUCT“
Norėdami paruošti formulę, kad būtų galima atlikti SUMIFS skaičiavimus keliuose lapuose ir tada susumuoti rezultatus, aplink formulę pridedame SUMPRODUCT funkciją
| 1 | = SUMPRODUCT (SUMIFS („3 žingsnis“! D3: D7, „3 žingsnis“! C3: C7, H3)) |
Naudojant funkciją SUMIFS viename lape gaunama viena vertė. Kelių lapų funkcija SUMIFS pateikia reikšmių masyvą (po vieną kiekvienam darbalapiui). Norėdami apskaičiuoti šio masyvo vertes, naudojame funkciją SUMPRODUCT.
4 veiksmas: pakeiskite lapo nuorodą lapų pavadinimų sąrašu
Mes norime pakeisti Lapo pavadinimas formulės dalis su duomenų sąrašu, kuriame yra vertės: Sausio mėn, Vasario mėn, Kovo mėn, ir Balandžio mėn. Šis sąrašas saugomas ląstelėse F3: F6.
INDIRECT funkcija užtikrina, kad būtų rodomas teksto sąrašas Lapų pavadinimai yra laikoma galiojančios langelio nuorodos dalimi funkcijoje SUMIFS.
| 1 | = SUMPRODUKTAS (SUMIFS (NETIESIOGINIS ("'" & F3: F6 & "'!" & "D3: D7"), NETIESIOGINIS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Šioje formulėje anksčiau parašyta diapazono nuoroda:
| 1 | „3 žingsnis“! D3: D7 |
Pakeičiama:
| 1 | NETIESIOGINIS ("" "& F3: F6 &" '! "&" D3: D7 ") |
Kabutės sunkiai įskaito formulę, todėl čia ji rodoma su papildomais tarpais:
| 1 | NETIESIOGINIS ("" "& F3: F6 &" '! "&" D3: D7 ") |
Naudojant šį langelių sąrašo nuorodos būdą, taip pat galima apibendrinti duomenis iš kelių lapų, kurie neatitinka skaitinio sąrašo stiliaus. Standartinė 3D nuoroda reikalauja, kad lapų pavadinimai būtų tokio stiliaus: „Input1“, „Input2“, „Input3“ ir pan., Tačiau aukščiau pateiktas pavyzdys leidžia naudoti bet kurio Lapų pavadinimai ir kad jos būtų nurodytos atskirame langelyje.
Ląstelių nuorodų užrakinimas
Kad mūsų formules būtų lengviau skaityti, parodėme formules be užrakintų langelių nuorodų:
| 1 | = SUMPRODUKTAS (SUMIFS (NETIESIOGINIS ("'" & F3: F6 & "'!" & "D3: D7"), NETIESIOGINIS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Tačiau šios formulės neveiks tinkamai, kai bus nukopijuotos ir įklijuotos kitur jūsų faile. Vietoj to turėtumėte naudoti užrakintas langelių nuorodas:
| 1 | = SUMPRODUCT (SUMIFS (NETIESIOGINIS ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), NETIESIOGINIS ("" "ir $ F $ 3: $ F $ 6 &" '! "& „C3: C7“), H3)) |
Norėdami sužinoti daugiau, perskaitykite mūsų straipsnį apie ląstelių nuorodų užrakinimą.
Suma, jei keliuose „Google“ skaičiuoklių lapuose
Šiuo metu „Google“ skaičiuoklėse negalima naudoti funkcijos „INDIRECT“ norint nurodyti SUMPRODUCT ir SUMIFS funkcijos lapų sąrašą.
Vietoj to, kiekvienam įvesties lapui galima atlikti atskirus SUMIFS skaičiavimus ir sudėti rezultatus:
| 1234 | = SUMIFS (sausis! D3: D7, sausis! C3: C7, H3)+SUMIFS (vasaris! D3: D7, vasaris! C3: C7, H3)+SUMIFS (kovo mėn. D3: D7, kovo! C3: C7, H3)+SUMIFS (balandis! D3: D7, balandis! C3: C7, H3) |
