Hogyan kell használni a CDF összegekkel vagy az Excel SUMIF
Ebben a leckében találsz néhány érdekes példát, amelyek bemutatják, hogyan kell használni a CDF funkciót (FKERES), együtt az összeg (SUM), vagy SUMIF (SUMIF) Excel, keresni, és összeadjuk az értékeket egy vagy több kritériumnak.
Feladatok eltérő lehet, de a jelentésük ugyanaz - meg kell találni, és összegezni az értékeket egy vagy több kritérium az Excel. Mik ezek az értékek? Bármely numerikus. Mik ezek a feltételek? Bármilyen ... Mivel a számot, vagy hivatkozást tartalmazó sejt értéket, és befejezve a logikai operátorokkal, és az eredményeket Excel képleteket.
CDF és az összeget az Excel - kiszámítja a talált megfelelő értékeket
Ha dolgozni numerikus adatokat Excel, akkor gyakran már nem csak letölteni a kapcsolódó adatokat egy másik tábla, hanem összefoglalja több oszlopot vagy sort. Ehhez, akkor össze a SUM függvény és a CDF. az alábbiak szerint.
Tegyük fel, hogy van egy lista áruk értékesítési adatok több hónapig, külön oszlopban minden hónapban. Az adatok forrása - lista havi értékesítés:
Most arra van szükség, hogy egy táblázatot az eredmények az értékesítési mennyiségek az egyes termékekre.
A megoldás erre a problémára - használatát egy sor állandók egy érv oszlopszám (oszlopszám) VLOOKUP. Itt látható egy példa a képlet:
= SUM (FKERES (lookup értéket, lookup tartomány ,, HAMIS))
= SUM (CDF (keresési_érték táblázat ;; hazugság))
Mint látható, az általunk használt tömb A harmadik érv a keresés többször ugyanabban FKERES. és megkapta az értékek összege a 2. és 4. oszlopba 3.
Most ezt a kombinációt PPS és az adatmennyiséget a mi táblázatban, hogy megtalálják a teljes összeg az értékesítés B oszlopok keresztül M:
Fontos! Ha meg tömbképlet, feltétlenül nyomja meg a Ctrl + Shift + Enter helyett a szokásos megnyomásával léphet ki. Microsoft Excel lépnek a képletet kapcsos zárójelek:
Ha korlátozzuk magunkat egyszerűen az Enter billentyű megnyomásával. a számítás csak az első érték a tömb, ami hibás eredményekhez vezetnek.
Lehet, hogy vajon miért a képletet a fenti ábrán a [@Product]. mint a kívánt értéket. Ez azért van, mert az adataim lett alakítva egy táblázat a parancs táblázat (Table) fület Insert (Beszúrás). Inkább dolgozni teljesen működőképes Excel táblázatkezelő, mint egy egyszerű tartományban. Például ha beírja a képletet az egyik sejtek Excel automatikusan bemásolja a teljes oszlopot, amely megment egy pár értékes másodpercet.
Mint látható, hogy használja az FKERES függvény az Excel és az összeg meglehetősen egyszerű. Azonban nem ez az ideális megoldás, különösen akkor, ha a nagy méretű táblák. Az a tény, hogy a használata tömbképletek lelassíthatja a kérelem a munka, mint minden érték a tömbben teszi egy hívást a CDF funkciót. Kiderült, hogy a magasabb értékek a tömbben, annál nagyobb a tömbképletek a munkafüzet és a lassabb futás Excel.
Ez a probléma kiküszöbölhető segítségével funkciók kombinációját INDEX (index) és a MATCH (MATCH) helyett az FKERES (CDF) és a SUM (SUM). Később ezt a cikket látni fogja néhány példa az ilyen képletek.
Végezze egyéb számításokat, a CDF funkció az Excel
Épp most lebontották a példa arra, hogyan kell bontania az értékek több oszlopot a táblázatban, és kiszámítja az összegük. Ugyanígy, akkor végre más matematikai műveleteket az eredményekkel, amelyek vissza a CDF. Íme néhány példa a képlet:
Kiszámítjuk az átlag:
Formula keresi a A2 érték a sejtek a munkalapon Lookup táblázatban, és kiszámítja a számtani középértékek, amelyek megtalálhatók a kereszteződésekben a sorok és oszlopok B, C és D.
Mi megtaláljuk a legnagyobb:
Formula keresi a A2 érték a sejtek a munkalapon Lookup asztal és vissza a maximális értékek, amelyek megtalálhatók a kereszteződésekben a sorok és oszlopok B, C és D.
Találunk egy minimum:
Formula keresi a A2 érték a sejtek a munkalapon Lookup táblázatban és visszaadja a minimális értékek, amelyek megtalálhatók a kereszteződésekben a sorok és oszlopok B, C és D.
Kiszámítjuk a% -os összeg:
A képlet úgy néz ki az értéke A2 cella a munkalap Lookup táblázatban. ezután összegzi az értékeket, amelyek megtalálhatók a kereszteződésekben a sorok és oszlopok B, C és D, majd kiszámítja csak 30% -át az összeget.
Ha ehhez hozzátesszük a fenti képlet a táblázatban az előző példában. Az eredmény így fog kinézni:
Nézzünk egy példát, hogy jobban világos, hogy mi van egy beszélgetést. Tegyük fel, hogy van egy táblázat, amely felsorolja a neveket az ügyfelek, termékeket vásárolt, és a szám (asztal Main táblázatot). Ezen kívül, van egy második táblázat tartalmazza az árak az áruk (táblázat Lookup táblázatban). A mi feladatunk -, hogy írjon egy képlet, hogy találja az összessége meghatározott vevői megrendelések.
Mivel ez egy tömbképlet, ne felejtsük el, hogy nyomja meg a Ctrl + Shift + Enter végén a bemenet.
Lookup table - a neve a lap, ahol a tartomány nézett.
Elemezzük a komponensek a képlet, hogy megértsék, hogyan működik, és megszokott, hogy illeszkedjen az Ön igényeinek. SUM függvényt, amíg félre, mivel annak nyilvánvaló.
Mivel a formula - ez tömbképlet, ő megismétli a fenti lépéseket minden egyes érték a keresési tömbben. A végén, az összeget a függvény értékek összege szorzatából. Ez nem nehéz, ért egyet?
CDF és SUMIF - találni, és összefoglalja értékeket, amelyek megfelelnek bizonyos kritériumoknak
SUMIF funkció (SUMIF) Excel hasonló a SUM (SUM), amit most bontott, mert ez is hozzáadott értéket. Az egyetlen különbség az, hogy a SUMIF összegzi csak az értéket, amely megfelel a megadott keresési feltételeknek. Például, a legegyszerűbb formula SUMIF:
- összegzi az összes sejt-értéke A2: A10. ami több mint 10.
Nagyon egyszerű, nem? Most nézzük meg egy kicsit bonyolultabb példát. Tegyük fel, hogy van egy táblázat, amely felsorolja a neveket az eladók és a számuk ID (Lookup táblázat). Ezen kívül, van egy másik táblázat, amelyben az azonos ID társított értékesítési adatok (Main táblázatot). A mi feladatunk -, hogy megtalálja az összeg az értékesítés egy adott kiskereskedő. Két súlyosbító körülmények:
- Összefoglaló táblázat (Fő tábla) tartalmaz egy sor olyan rekordokat egyetlen azonosítót véletlenszerűen.
- Nem adhat hozzá egy oszlopot a nevét az eladók a főtábla.
Írjuk le a képlet, hogy megtalálja az összes forgalmának kérni az eladótól, és összefoglalja a mért értékeket.
Mielőtt elkezdenénk, hadd emlékeztessem SUMIF függvény szintaxisa (SUMIF):
- tartomány (Range) - az érvelés, önmagáért beszél. Ez csak a cellatartományt szeretné értékelni egy adott kritérium.
- kritériumok (feltételek) - olyan állapot, hogy az említett általános képletű, mely értékeket összegezzük. Ez lehet egy szám, egy hivatkozás egy sejt vagy más expressziós Excel függvény.
- összeg_tartomány (összeg_tartomány) - nem kötelező, de nagyon fontos érv a számunkra. Ez határozza meg a különböző kapcsolódó sejtek fognak össze. Ha nincs megadva, az Excel hozzáadja a cellák értékeit, az első érv a funkciót.
Miután összegyűjtése együtt, nézzük meg egy harmadik érv a SUMIF funkciót. Ahogy emlékszem, szeretnénk összefoglalni az összes értékesítés elkövetett egy bizonyos eladó, akinek a neve a megadott F2 (lásd az ábrát emlékeztető fent).
- tartomány (tartomány) - mivel keresünk eladó ID, ez az érv lesz az értékeket a B oszlopban a főtábla (Main táblázatot). Megadható egy sor B: B (teljes oszlop) vagy átalakításával az adatokat egy táblázat, használja az oszlop nevét Main_table [ID].
kritériumok (teszt) - az eladók, mivel nevek vannak írva egy táblázat (Keresési tábla), használja a CDF funkciót a keresési azonosítója. megfelel egy adott eladó. Név van írva sejt F2, így keressük a következő képlet segítségével:
Persze, akkor adja meg a nevét, mint a kívánt értéket közvetlenül a CDF funkciót. de jobb, ha abszolút cellahivatkozás, mert ahogy azt egy univerzális képletet, amely működik bármely megadott érték a cellában.
Mindössze annyit kell tennie, hogy tegye a darabokat össze egy, a képlet SUMIF + VPR kész:
