Šajā rakstā mēs iemācīsimies atgriezt SUM tikai no Excel formulām.
Vienkāršiem vārdiem sakot, strādājot ar daļēji aizpildītiem datiem. Dažreiz mums ir jāatrod vērtības summa ar nosacījumu. Nosacījums ir iegūt summu, kurā vērtības tiek iegūtas tikai ar formulām.
Šajā rakstā mums būs jāizmanto šādas funkcijas:
- SUMPRODUCT funkcija
- Funkcija ISFORMULA
Funkcija SUMPRODUCT ir matemātiska funkcija programmā Excel. Tas darbojas vairākos diapazonos. Tas reizina atbilstošos masīvus un pēc tam tos pievieno.
Kopā = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Sintakse:
| = SUMPRODUCT (masīvs1, [masīvs2],…) |
masīvs: tas ir vērtību diapazons vai saraksts.
Funkcija ISFORMULA atgriež TRUE, ja šūnā_reference ir formula un citādi atgriež FALSE.
Sintakse:
| = ISFORMULA (atsauce) |
atsauce: atsauce uz šūnu, kur pārbaudīt formulu
Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mēs sniegsim datus, un mums bija nepieciešami summas rezultāti, kur tiek izmantota formula.
Izmantojiet formulu:
| = SUMPRODUCT (masīvs * ISFORMULA (masīvs)) |
Paskaidrojums:
- Funkcija ISFORMULA atgriež TRUE & FALSE, pamatojoties uz nosacījumu, kas tiek pārbaudīts šūnās.
- Funkcija SUMPRODUCT ņem vērā vērtību 1 katrai patiesai vērtībai un 0 vērtībai FALSE.
- Produkts, kas ņemts starp atbilstošajām vērtībām, ignorēs FALSE vērtības, jo vērtības tiek reizinātas ar 0. Tikai TRUE vērtības iegūst SUM, jo vērtības tiek reizinātas ar 1 s.
Piemērs:
Pārbaudīsim šo formulu, izmantojot to, izmantojot piemēru
Šeit mums ir dati par graudu cenu, kas iegūta no daudzuma produkta līdz tās vienības cenai, un dažas cenas vērtības tiek ievadītas manuāli. Tātad, ja man ir jāatrod cenas summa vērtībai, kurā formula izvilka kopējo cenu.
Tagad mēs izmantosim zemāk esošo formulu, lai iegūtu SUM
Formula:
| = SUMPRODUCT (E2: E15 * ISFORMULA (E2: E15)) |
Paskaidrojums:
- Funkcija ISFORMULA atgriež TRUE & FALSE, pamatojoties uz nosacījumu, kas tiek pārbaudīts masīva šūnās no E2 līdz E15.
- Funkcija SUMPRODUCT ņem vērā vērtību 1 katrai saņemtajai PATIESAI vērtībai un 0 vērtībai FALSE, kā parādīts zemāk.
= SUMPRODUCT (E2: E15*)
{TRUE; FALSE; FALSE; FALSE; PATIESA; PATIESA; FALSE; PATIESA; FALSE; PATIESA; PATIESA; PATIESA; PATIESA; TRUE})
- Produkts, kas ņemts starp atbilstošajām vērtībām, ignorēs FALSE vērtības, jo vērtības tiek reizinātas ar 0. Tikai TRUE vērtības iegūst SUM, jo vērtības tiek reizinātas ar 1 s, kā parādīts zemāk.
= SUMPRODUCT ({58.41; 0; 0; 0; 82.84; 95.58; 0; 90.27; 0; 37.8; 78.48; 57.97; 97.72; 77.88})
Šeit funkcijas masīvs ir norādīts kā šūnu_reference. Nospiediet Enter, lai iegūtu rezultātu.
Kā redzat iepriekšējā momentuzņēmumā, iegūto vērtību summa, ja formula pastāv.
Ja jums ir jāatrod vērtību summa, kurai nav formulas, izmantojiet funkciju NOT ar funkciju ISFORMULA.
Izmantojiet formulu:
| = SUMPRODUCT (E2: E15 * NOT (ISFORMULA (E2: E15))) |
Izmantojiet formulu un iegūstiet vērtību, kā parādīts zemāk esošajā momentuzņēmumā.
Kā redzat no iepriekš minētās formulas, jūs varat iegūt nosacījuma vērtības.
Piezīmes:
- Funkcija SUMPRODUCT neskaitliskas vērtības uzskata par 0.
- Funkcija SUMPRODUCT loģisko vērtību TRUE uzskata par 1 un False par 0.
- Argumentu masīvam jābūt vienāda garuma, pretējā gadījumā funkcija atgriež kļūdu.
Ceru, ka šis raksts par to, kā atgriezt SUM tikai no Excel formulām, ir paskaidrojošs. Vairāk rakstu par SUMPRODUCT funkcijām meklējiet šeit. Lūdzu, kopīgojiet savu jautājumu zemāk komentāru lodziņā. Mēs jums palīdzēsim.
Funkcijas SUMPRODUCT izmantošana programmā Excel
Kā noņemt tekstu programmā Excel, sākot no pozīcijas
Teksta ierakstu validācija
Izveidojiet nolaižamo sarakstu programmā Excel ar krāsu
Noņemiet teksta sākumā un beigās atstarpes programmā Excel
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai
Rediģējiet nolaižamo sarakstu
Absolūta atsauce programmā Excel
Ja ar nosacītu formatējumu
Ja ar aizstājējzīmēm
Vlookup pēc datuma