trükk №19
Látva az eredmény a feltételes formázás, akkor érdemes létrehozni egy képlet, hogy csak azokra adatokat vetjük alá feltételes formázás. Az Excel ez nem nagyon jól végrehajtani egy ilyen lehetőség, de segít neki.
Excel felhasználók rendszeresen kérdezik, hogyan lehet elvégezni a számításokat csak bizonyos sejtek háttér színét. Ez a kérdés merül fel olyan gyakran, mert az Excel nem biztosít állandó eleme ezt a feladatot; Azonban ez lehet tenni egy egyedi funkció, amint azt trükk száma 88 „számítanak vagy összege sejtek specifikus kitöltőszínét.”
Az egyetlen baj a felhasználói funkció - nem érti a méret által megszabott feltételes formázás. Azonban egy kis gondolkodás, akkor érheti el a kívánt eredményt, anélkül egy egyedi funkcióval.
Tegyük fel, hogy van egy hosszú listát a számok a tartományban $ A $ 2: $ A $ 100. Ön kivetett ezeket a sejteket, feltételes formázás, hogy csak jelzett szám 10 és 20 között Most kell, hogy adja össze az értékeket a sejtekben, amelyek megfelelnek az újonnan megállapított kritériumoknak, és válassza ki a mennyiségét értékek feltételes formázás. Nem számít, hogy milyen formázást alkalmazzák ezeket a sejteket, azonban ismernünk kell a kritériumokat, amelyek alapján a sejteket kiemelt (ebben az esetben az a feltétel, hogy az érték 10 és 20 közötti).
Megállapítani egy cellatartományt, amelyek megfelelnek bizonyos kritériumoknak, akkor a SUMIF funkciót (SUMIF). de megadhat egyetlen kritérium. Ha több teszt tényező, akkor tömbképletként. Tömbképletet felírható:
= SZUM (HA ($ A $ 2: $ A $ 100> 10; HA ($ A $ 2: $ A $ 100<20;$A$2:$A$100)))
Az orosz változat Excel
= SZUM (HA ($ A $ 2: $ A $ 100> 10; HA ($ A $ 2: $ A $ 100<20;$А$2:$А$100)))
alternatív útvonal
Alternatív módon, lehet használni egy további oszlop (például, C oszlop) a referencia cella oszlopban A. Ezek a kapcsolatok visszatér az eredmény a B oszlopban, csak akkor, ha az érték megfelel a beállított állapot, azaz> 10 <20. Для этого сделайте следующее:
Válassza a B1 cellába, és adja meg a következő képlet szerint:
= HA (ÉS (A2> 10; A2<20);A2;"")
Az orosz változat Excel
= HA (ÉS (A2> 10; A2<20);А2;"")
Rögzítse ezt a képletet az egyes sejt-sejt B100. Ezt követően, ha oszlop A jelentése egy értéket a B oszlopban kell lennie értéket 10 és 20 közötti.
Ha gyorsan másolni a képletet a szomszédos sejtek le az utolsó sort, írja be a képletet az első cella (B2), hogy újra válassza ki a cellát, majd kattintson duplán a kitöltőjelre. Ezt meg lehet tenni kiválasztásával Szerkesztés → Töltse → Le (Edit → Fill → Le).
Most válassza ki bármelyik cella, ahol az összeget meg kell jelennie, és használja hozzá egy szabványos SUM (SUM). (El tudja rejteni a B oszlopban, ha nem akarja látni egy oszlopot a visszaadott értékek a képletet.)
A fenti két lehetőség ezt a munkát is, de az Excel biztosít egy másik funkció, amely lehetővé teszi, hogy meghatározza több feltételt. Ez a funkció tartalmazza a készlet adatbázisok és Excel adatfunkciókat nevezett DSUM (DSUM).
Hogy ellenőrizze, hogy, használja ugyanazt a számok halmaza tartományban A2: A100 e sejt C1: D2 és állítsa be ebben a tartományban SumCriteria neve. beírja a nevét a mező bal oldalán a képlet bar. Most válassza sejt C1 és enter = $ A $ 1. azaz az utalás az első cellát a munkalapon. Másolás ugyanazt a D1 cellába, és kapsz két példányban az oszlop fejlécében A. Ezeket a másolatokat fogják használni tételek DSUM feltételek (DSUM) (C1: D2). hívott SumCriteria.
A C2 cellában adja> 10. A D2 cellában típusa <20. В ячейке, где должен быть результат, введите следующий код:
= DSUM ($ A $ 1: $ A $ 100, $ A $ 1; SumCriteria)
Az orosz változat Excel
DSUM = ($ A $ 1: $ A $ 100, $ A $ 1; SumCriteria)
DSUMM funkció (DSUM) - ez az előnyös és leghatékonyabb módja annak, hogy működjön együtt a sejteket, amelyek megfelelnek bizonyos kritériumoknak. Ellentétben tömbök, beépített adatbázis funkciók kifejezetten erre a célra, és akkor is, ha utal egy rendkívül széles, és használják nagy számban, és azok negatív hatást gyakorol a konverziós arány és a hatékonyság meglehetősen kicsi, mint a tömbképletet.