A munkalapnevek listázására leginkább VBA, azaz makró megoldásokat lehet találni. De a minap egy érdekes megoldásra bukkantam itt, amit szeretnék veletek megosztani. Tulajdonképpen ez is makró, de nem VBA, hanem EXCEL4 makró. Az Excel4 makrók függvények képében még megtalálhatók a mai EXCEL verziókban is, kompatibilitási okokból. A […]
Tovább... →Tömbképletek
Elég sok anyag született már a szűrésekről itt a Bázison. Az autoszűrő és az Irányított (haladó) szűrő közös tulajdonsága, hogy nem követi a változásokat, hanem mint egy egyszerű SELECT lekérdezés, az aktuális állapotról ad egy eredményt. Ebből következően, ha változik az adatforrás, akkor újra kell futtatni a szűrést. […]
Tovább... →Azt a problémát járjuk most körbe, hogy hogyan lehet megszámolni egy oszlopban lévő különböző egyedi értékek számát. Tehát olyan, mintha az ismétlődések eltávolítása után megszámolnánk a maradék elemek számát. Alapvetően két elv szerint oldható meg a probléma. Az egyik egy segédoszlopoban használ egy képletet, és azt használjuk […]
Tovább... →A vágólap elforgatott beillesztés funkcióját mindenki ismeri. Ami a forrás tartományban vízszintesen volt, az a céltartományban függőlegesen lesz vagy fordítva. Az Excel különböző verzióiban más-más módokon lehet közvetlenül elérni, de az Irányított beillesztés párbeszédben már régóta ugyanott találjuk. Az a probléma, hogy ezzel az elforgatással a célhely […]
Tovább... →Ha olyan tartományt akarunk összegezni, amelyben bármilyen hibaüzenet van, akkor az Excel SZUM (SUM) függvénye hibát ad vissza, ahogy a következő ábrán is látható. A hiba ott keletkezik, hogy az „D” oszlopban lévő FKERES (VLOOKUP) függvény a termékkód alapján megpróbálja megkeresni a termék egységárát a termék-törzs táblában, […]
Tovább... →Egy nagyon ötletes képletet szeretnék mutatni, amivel azt lehet megvizsgálni, hogy egy tartomány minden eleme azonos-e vagy sem.
Legyen a vizsgált tartomány az A1:F1 tartományban
=AVERAGE(COUNTIF(A1:F1;A1:F1))=COUNTA(A1:F1) =ÁTLAG(DARABTELI(A1:F1;A1:F1))=DARAB2(A1:F1)
Angol rendszerben az argumentum elválasztót pontosvesszőről vesszőre kell cserélni.
Hogy működik?
COUNTIF(A1:F1;A1:F1)
Ez egy tömbképlet, ami a tartomány minden elemére megvizsgálja, hogy hányszor szerepel a tartományban, és egy tömböt ad vissza, az egyes elemek előfordulási számával. Ha minden elem egyforma, akkor mindegyik ugyanannyiszor lesz benne, tehát a visszaadott tömb minden eleme ugyanaz lesz, és ez megegyezik a tartomány celláinak a számával.
A konkrét példánál, mivel a tartománynak 6 cellája van egymás mellett, a visszaadott tömb minden elemének 6-nak kell lennie.
AVERAGE(COUNTIF(A1:F1;A1:F1))
Ha minden elem 6, akkor ezek átlaga is 6, ez pedig éppen egyenlő a tartomány elemeinek a számával. Nem marad más hátra, mint ellenőrizni, hogy a kapott érték valóban megegyezik-e a tartomány kitöltött celláinak számával. Ezt a COUNTA ( DARABTELI ) függvénnyel tudjuk lekérdezni.
Letölthető munkafüzet
Videón talán érthetőbb
Tartomány minden eleme egyforma?
A feltétel szerinti összegzés (Sumif/Szumha), illetve a megszámlálás (Countif/Darabteli) függvényeket legtöbben ismerik. Sőt, az excel 2007-től ezek több feltételt is használni képes nagy-testvérei is egyre inkább bekerülnek a köztudatba. (SumIfs/Sumhatöbb, Countifs/Darabhatöbb). Ezekkel a függvényekkel remek riportokat lehet készíteni, de azért van néhány korlát, amiket nem tudnak átlépni.
Az oktatóanyagban ezt a táblázatot használjuk példaként.
Érdekesebb kérdések ehhez a táblázathoz
- A nyereséges sorok száma
- Az „Északi” nyereségesek száma
- Az „Északi” nyereségesek összbevétele
- Északiak és Déliek együttes bevétele vagy nyeresége vagy vesztesége.
Természetesen lehetne egy „Nyereség” nevű számított oszlopot tenni a végére, de most ezt szándékosan nem tesszük.
Mi a gond az ismert feltételes összesítő függvényekkel?
- A feltételek csak konkrét értékek lehetnek (konstans, cellacím vagy számítás), de olyat már nem tudnak, hogy csak azokat a sorokat vegyék figyelembe, ahol pl. a ‘B’ oszlopban nagyobb érték van, mint a ‘C’ oszlopban
- A feltételeket csak ÉS kapcsolatba tudják hozni, azaz egy következő feltétel tovább szűkíti az előző feltéteknek megfelelő sorok számát. A feltételeket nem képesek VAGY kapcsolatba tenni.
Akkor mi a megoldás?
- Egyik lehetőség, hogy egy külön oszlopba elvégezzük a vizsgálatot. majd ezt az oszlopot értékeljük ki. Ha pl. a nyereséges sorok számát keressük, akkor az utolsó oszlopba írhatunk egy olyan képletet, ami nullát ad eredményül, ha nincs nyereség, különben pedig egyet. Ezen oszlop összesítésével megkapjuk a nyereséges cégek számát. Ez két lépés. Egyik a segédoszlop elkészítése, és a második az összegzés.
A segédoszlop képlete lehetne egy HA() függvény, de Én ezt javaslom beírni, majd lehúzni az oszlopra:
=--(B2>C2)
A zárójelben lévő kifejezés TRUE/FALSE eredményt ad, a dupla mínusz jel pedig ezt alakítja át 0/1 értékekre.
- Második lehetőség, hogy Adatbázis függvényt próbálunk bevetni, és a kritérium tartományt olyan számítással adjuk meg, ami a táblázat első sorában értékel ki. Az egész kritériumtartományt elnevezzük „Criteria” néven.
A függvény pedig a következő:
=DCOUNTA($A$1:$D$13;"Kateg";Criteria)
=DARAB2($A$1:$D$13;"Kateg";Criteria)
És újra a SUMPRODUCT (Szorzatösszeg)
- Harmadik lehetőség, hogy az egészet egy képlettel oldjuk meg.
=SUMPRODUCT(--(B2:B13>C2:C13)) =SZORZATÖSSZEG(--(B2:B13>C2:C13))
Ez a képlet belül a ‘B’ és a ‘C’ oszlopot hasonlítja össze, és egy olyan tömböt ad eredményül, aminek annyi eleme van, ahány sora a két megadott tartománynak, de ott van benne TRUE, ahol a B>C, különben FALSE. A dupla mínusz jel a logikai értékeket 0/1 értékekké alakítja. Végül a SUMPRODUCT összeadja, az eredmény a tömbben szereplő egyesek összege, azaz a nyereséges sorok száma lesz.
Letölthető munkafüzet: szamitott-felt-szerinti-osszesitesek
A kapcsolódó videóban látható a megoldás menete, és a többi kérdésre is választ kapunk
Számított feltétel szerinti összegzés
Az anyagban megmutatom, hogy lehet képlettel megoldani azt a feladatot, amikor a sor, illetve oszlop azonosítók alapján egyszerre kellene összesítést vagy megszámlálást csinálni. Volt egy korábbi anyag, ahol 2D keresést mutattam, de ez más.
Íme a probléma
Tehát ugyanazok az értékek ismétlődgetnek a fejlécben, mint az első oszlopban. Nekünk pedig az alsó, összesített tábálázatot kell összehozni
Hű, ez nagyon érdekes. nem tudom, hogy mennyire gyakori probléma, de Nekem annyira megtetszett, hogy azt éreztem, ezt mindenképpen fel kell tenni az Excel Bázisra.
PIVOT?
Mivel ismétlődő fejléc elemek vannak, PIVOT-al nem lehet megcsinálni. Legalább is Én nem találtam rá megoldást. ha lenne valaki, aki IGEN, akkor commentben ossza már meg velünk!
MACRO?
Igen, makróval meg lehet csinálni. Egymásba ágyazott ciklusokkal. Már ha érted mire gondolok. Ez nagyobb táblázat esetén meglehetősen lassú, ráadásul tudni kell programozni, és a VBA nyelv ismerete is szükséges.
A megoldás – Képlet – SUM vagy SUMPRODUCT
Nem gondolnád, Igaz? Nézzük a feladatot konkretizálva az ‘A‘ kategóriára: Összesíteni kell az adattartomány azon elemeit, ahol a fejlécben ‘A‘ van és a sor elején is ‘A‘ van. Mivel az egyik feltétel vízszintesen van, a SUMIFS (Darabhatöbb) függvény nem jön számításba. Tömbökkel kell dolgoznunk, így esélyes, hogy tömbképlet lesz a megoldás.
Általánosan kb. így néz ki:
=ÖSSZEG((fejléc="A") * (Első oszlop="A") * (adatok))
A fenti kép alapján konkrétan, még mindig csak az „A” kategóriára
=SUMPRODUCT(($B$3:$B$12="A")*($C$2:$J$2="A")*($C$3:$J$12))
A SUMPRODUCT (SZORZATÖSSZEG) remek függvény. Kiválón alkalmas hasonló módon több feltétel szerinti összegzésre. Amíg nem volt 2007-es Excel, és nem nem volt SUMIFS (DARABHATÖBB), addig így lehetett csak hasonló problémákat megoldani.
Hogy működik?
Hát, ezt írásban meglehetősen hosszadalmas lenne elmagyarázni. Inkább nézd meg a videót. Abban elmondom, hogy mi a működés alapja, és megnézzük az összegzésen kívül a megszámlálást is.
Gyakorló munkafüzet: 2D-felt-osszegzes-megszamlalas
Két dimenziós összesítés
A feladat az, hogy egy szöveges oszlopot szűrjünk az alapján, hogy csak azok a sorok maradjanak (vagy ne maradjanak), amelyek tartalmazzák egy másik tartományba előre felrögzített szavak bármelyikét.
Tehát a bal oldali listában, ami valószínűleg egy hosszabb lista, jelölni, vagy szűrni szeretnénk azokat, amikben előfordul a jobb oldali lista bármely eleme.
Alakítsuk táblázattá!
Mielőtt bármelyik megoldásnak nekikezdenénk, mindkét tartományt alakítsuk táblázattá. Ennek több előnye is van.
- Az új tételeket automatikusan beleveszi a hivatkozásba
- Egyszerűbb megadni a lista és kritérium-tartományokat
- Egyszerűbben hivatkozhatjuk őket akkor is, ha különböző lapokon vannak
Megoldás irányított szűrővel
Felállítunk egy kritériumtartományt, ahol a szűrendő szavak egymás alatt vannak, joker karakterekkel (*) kiegészítve az elején és a végén. Mivel egymás alatt vannak, VAGY kapcsolatban leszek egymással, tehát bármelyik előfordul a bal oldali oszlopban, azt szűrni fogja.
Ha csak a táblázatok nevét használjuk, akkor a táblázatok fejléce nem fog szerepelni a hivatkozásban. Emiatt mögé kell írni az [#All] kiegészítést. Tehát:
List Range: adatok[#all]
Criteria range: szurolista[#all]
Megoldás képlettel
Ebben az estben a táblázattá alakított szűrőlista elemeihez nem kell joker karaktereket adni, csak egymás alá felsorolni a szűrendő szavakat. Az adatlistát pedig kiegészítjük egy új oszloppal, ahová képlettel megállapítjuk, hogy a kizárandó elemek előfordulnak-e az adott sorban lévő szövegben.
A C4-be írt képlet megvizsgálja, hogy a szűrőlista bármely eleme benne van-e az aktuális sor „Megnevezés” oszlopában.
=SUM(IFERROR(SEARCH(szurolista;[@Megnevezés]);0)) =SZUM(HAHIBA(SZÖVEG.KERES(szurolista;[@Megnevezés]);0))
A képletet érvényesítsük a teljes oszlopra.
Ahol nullát kapunk, az azt jelenti, hogy egyik jobb oldalon felsorolt szó sem fordul elő a bal oldali lista aktuális sorában. Ennek megfelelően lehet a szűrés oszlopban szűrni a nullákat vagy a nem nullákat.
Letölthető minta munkafüzet: lista-szurese-lista-szerint
A videóban látható mindez működés közben, részletesebb magyarázatokkal, főleg ami a képlet működését illeti.
Lista szűrése másik lista szerint
Ha szükségünk lenne egy olyan képletre, ami egy tartományból listázza a valahány (három, négy, öt, stb…) legnagyobb értéket, akkor íme a megoldás. Mivel a képletnek nem csak egy eredménye van, hanem több, a feladatot tömbképlettel tudjuk megoldani.
A megoldáshoz, illetve a leírás megértéséhez ismerni kell a LARGE (NAGY) függvényt, ami egy tartományból visszaadja a valahányadik legnagyobbat. A következő függvény pl. az A1:A20 tartományból visszaadja a második legnagyobbat.
=LARGE(A1:A20;2)
A három legnagyobb érték listázása
Tehát szükségünk van az első, a második és a harmadik legnagyobb értékre az A2:A10 tartományból. A Large függvénynek két tömböt kell megadni. Az egyik az adatokat tartalmazó tartomány, a másik pedig azoknak a számoknak a felsorolása tömbkonstansként vagy tartományi hivatkozásként, amennyiedik értékeket keressük. Utóbbiakat ezután hívjuk index számoknak.
Adatok tartománya: A2-A10
Index számok: E2:E4
- Jelöljünk ki három üres cellát
- Képlet
=LARGE(A2:A10;E2:E4)
A három legnagyobb érték bekerült a három kijelölt cellába.
Ha az adatok módosulnak, és ez érinti a három legnagyobbat, akkor azok is módosulni fognak.
Az index számokat tömbkonstanssal is meg lehet adni.
Az anyaghoz tartozó videóban bemutatom a képlet létrehozását mindkét módszerrel.
Top n legnagyobb érték kiválogatása képlettel
