Hogyan lehet összegezni szín szerint az Excel programban? (2 hasznos módszer)
Az Excel két legfontosabb módja a színek szerinti összegzésnek a következő -
- A SUBTOTAL képlet használata az excelben és a szűrés szín szerint.
- A GET.CELL képlet alkalmazása úgy, hogy meghatározza a nevet a képlet fülön, és a SUMIF képletet alkalmazza az excelben az értékek színkódok szerinti összegzéséhez.
Beszéljük meg részletesen mindegyiket -
# 1 - Összegzés színenként a részösszeg függvény használatával
Az értékek összegének háttérszín szerint történő kiszámításának megközelítésének megértéséhez vegyük figyelembe az alábbi adattáblázatot, amely részleteket tartalmaz az USA dollárban szereplő összegekről régiónként és hónaponként.
- 1. lépés: Tegyük fel, hogy kiemelnénk azokat a cellákat, amelyek értékei negatívak a jelzés céljából, ezt feltételes formázással vagy a cellák manuális kiemelésével érhetjük el, az alábbiak szerint.
- 2. lépés: Most, hogy az excellel színezett cellák összegét elérjük, az adattáblázat alá írja be a SUBTOTAL képletét. Az alábbiakban bemutatjuk a SUBTOTAL képlet szintaxisát .
Az összegzés kiszámításához bevitt képlet:
= ÖSSZETÉT (9, D2: D13)
Itt a function_num argumentumban a „9” szám az összeg funkcionalitására utal , és a referencia argumentumot a kiszámítandó cellák tartományaként adják meg. Az alábbiakban bemutatjuk ugyanarról a képernyőképet.
- 3. lépés: Amint az a fenti képernyőképen látható, az USD összegének összegzését kiszámoltuk a világos piros háttérszínnel kiemelt összegek kiszámításához. Szűrőt alkalmazzon az adattáblához az Adatok menüben, majd válasszon egy szűrőt.
- 4. lépés: Ezután válassza ki a szűrőt szín szerint, majd válassza a világos vörös cellaszínt a „Szűrés sejtszín szerint” részben. Az alábbiakban bemutatjuk a képernyőképet, amely jobban leírja a szűrőt.
- 5. lépés: Az excel szűrő alkalmazását követően az adattábla csak a világos vörös háttér cellákra lesz szűrve, és az adattábla alján alkalmazott részösszeg képlete megjeleníti a színes cellák összegzését, amelyeket az ábra szerint szűrünk lent.
Amint a fenti képernyőképen látható, a színes cella kiszámítását az E17 cellában végezzük el, részösszeg képlet.
# 2 - Összegzés szín szerint a Get.Cell függvény használatával
A második megközelítést elmagyarázzuk, hogy elérjük az excel színsejtek összegét, amint azt az alábbi példa tárgyalja. Vegye figyelembe az adattáblát, hogy jobban megértse a módszertant.
- 1. lépés: Most emeljük ki az USD összege oszlop celláinak listáját, amelyek hajlandók vagyunk elérni a kívánt színes cellák összegét, az alábbiak szerint.
- 2. lépés: Amint a fenti képernyőképen láthatjuk, az itt látható első példával ellentétben több színünk van. Ezáltal a = GET.CELL képletet fogjuk használni azzal, hogy definiáljuk a névmezőben , és nem közvetlenül használjuk az excelben .
- 3. lépés: Most, amikor megjelenik a „Név meghatározása” párbeszédpanel, írja be a Név és a képletet elfelejtve a .CELL elemet a „Hivatkozás” mezőbe, ahogy az alábbi képernyőképen látható.
Amint a fenti képernyőképen látható, a függvényhez megadott név „CellColor”, és a = GET.CELL (38, „2. példa! $ D2)” képletet be kell írni a „Hivatkozások ide”. A képleten belül a 38 szám a cella kód információra utal, a második argumentum pedig a D2 cellaszám a referencia cellára vonatkozik. Most kattintson az OK gombra.
- 4. lépés: Most írja be a „CellColor” nevet a párbeszédpanelen definiált színkódolt cella melletti cellába, a 3. lépésben leírtak szerint.
Amint a fenti képernyőképen látható, megadjuk a 'CellColor' függvényt , amely visszaadja a háttércella színének színkódját.
Hasonlóképpen, a képletet az egész oszlopra húzza.
- 5. lépés: Most, hogy elérjük az értékek összegét az excel színeiben, beírjuk a SUMIF képletet. A SUMIF képlet szintaxisa a következő: -
Amint a fenti képernyőképen látható, a következő argumentumok kerülnek be a SUMIF képletbe: -
- A tartomány argumentum megadva az E2: E13 cellatartományhoz.
- A kritériumokat G2-ként adják meg, amelynek összesített értékeit be kell szerezni.
- A cellák tartománya D2: D13.
A SUMIF képletet lefelé húzza az összes színkódszám, amely értékeket össze kell adni.
Dolgok, amikre emlékezni kell
- A SUBTOTAL képlet szerinti megközelítés használata közben ez a funkció lehetővé teszi a felhasználók számára, hogy egyszerre csak egy szűrőszínre szűrjenek. Ez a funkció ráadásul csak egy értékoszlop hozzáadásához használható szűrőszínek szerint. Ha egynél több oszlop van soronként különböző színnel a különböző oszlopokban, akkor a SUBTOTAL csak egy adott oszlop szín szerinti szűrésének helyes eredményét jelenítheti meg.
- A GET.CELL képlet a SUMIF megközelítéssel kombinálva kiküszöböli ezt a korlátozást, mivel ez a funkció felhasználható a cellaháttér több színkódjának színeivel való összefoglalására.
