Aki ismeri a cellában készíthető listamenü lehetőségeit, talán felkapja a fejét a cím láttán. Ugyanis beviteli szabályból egyelőre csak egyet lehet megadni az Excelben. Tehát vagy lista, vagy egyedi szabály az ismétlődések tiltására. A kettőt együtt hogy? Hát, egy kis trükközéssel elérhető, hogy a legördülő menüből eltűnjenek […]
Tovább... →Képletek
Egy speciális technikát szeretnék bemutatni. Lényege röviden az, hogy hogyan lehet meghivatkozni az elejétől az aktuális sorig terjedő tartományt. Magyarul a megcímzett tartomány eleje fix, de a vége attól függ, meddig húzom a képletet. Működik függőlegesen és vízszintesen is. Először nézzük a technikát Azt látjuk, hogy a […]
Tovább... →A sor és oszlopméretek beállítása alapesetben nem túl bonyolult dolog. Néhány korábbi bejegyzésben már itt is olvashattatok róla. Alap oszlopszélesség megadása Oszlop és sorméret megadása centiméterben Egy kedves ismerősöm blogján olvastam egy érdekes megoldást a sormagasság beállítására képlet segítségével. Ezúton is köszönöm Kris, hogy hozzájárultál a meghivatkozáshoz! […]
Tovább... →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... →Meglehetősen sokszor találkozok azzal, hogy több munkalapos munkafüzetekben a munkalap nevét az egyik cellában konstansként is beírják. ha a lap neve megváltozik, akkor azt a cellát is manuálisan változtatni kell. Gondoltam, itt az ideje, hogy közzétegyem a megoldást, amit persze megint nem Én találtam ki, bár annyira […]
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... →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... →Főleg importált adatok esetén fordul elő gyakran, hogy egy oszlopban minden adat aposztróffal kezdődik. Ezt arra használják, hogy mindenképpen szöveges formátumú legyen az adott oszlop. Extrém esetben az üres cellák is tartalmaznak legalább egy aposztrófot. Megszabadulni ezektől nem is olyan egyszerű, de a most ismertetett műveletsorral megoldható. Alap […]
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
