Hasznos Excel függvény: Darabteli (CountIf)
2007 november 21Ez az a függvény, amit érdemes mielőbb megismerni, aztán pedig csűrni-csavarni.
A hozzám érkező segítségkérések nagy részére EZ a megoldás. Mivel mostanában keveset írtam ide, ezért kárpótlásként írok most pár ütősebb tippet. Olyat, amilyet kevesen ismernek 😉
Annak, aki most ismerkedik a DARABTELI függvénnyel:
Ez a függvény azokat a cellákat számolja össze, amik eleget tesznek a megadott feltételnek.
Az angol nyelvű verzióban CountIF néven szerepel, amit DARABHA névre lehetne fordítani.
Használata: DARABTELI ( Tartomány ; Feltétel )
Ez akkor válik érdekessé, ha gyakorlati példákon keresztül nézzük a működését.
1. Hogyan ellenőrizzük, hogy az új sorok szerepelnek-e már a meglevő listánkban?
Az ismertebb, oktatott módszerrel minden olyan sor mellett az a darabszám szerepel, amennyiszer szerepel a listában. Kb. erre találták ki eredetileg.
A fenti módszer szépséghibája, hogy ezzel ugyan látszik, melyik ismétlődik, de a duplák, triplák kiszűrése, eltávolítása még így is túl manuális.
És itt jön az egyik trükk:
A megoldás nem bonyolult, inkább irassuk ki, hogy hányadik előfordulása az adott listában. Így már látszik az “eredeti” és a többi “másolat”. Ezt követően már könnyedén kiszűrhetők, vagy rendezhetők az ismétlődések, az eredetik pedig biztonságban maradnak.
Az cellahivatkozások tanulmányozásához ajánlom figyelmetekbe a korábbi (abszolút, relatív és vegyes cellahivatkozások) című bejegyzésemet.
2. Sorrend kialakítása függvénnyel
Az aránylag ismert módszer segítségével olyan sorrendet kapunk, ahol a holtverseny miatt lehetnek ismétlődő sorszámok.
A másik trükk:
A holtversenyen belüli pozíció figyelembe vétele:
A példákat tartalmazó excel letölthető innen (benne egy itt még be nem mutatott példával): Excel darabteli minta
Lesz még szó a darabteliről, mert a java még hátravan… Hogy ne maradj le, iratkozz fel az értesítőre és persze a hozzászólásokban kiváncsi vagyok a véleményedre!
Excel, Felhasználóknak, függvény, haladó, tippek
54 hozzászólás
Ez a post pont jókor jött. Köszönöm.
Orso-borso 2007 november, 22 - 00:25:14
Ez a blog annyira jó, hogy szíves engedelmeddel felteszlek a Blogroll-ba nálam, és hamarosan írok egy rövid cikket, amelyben ajánlom az oldaladat.
Nagyon hasznos információkat adsz közre, köszönöm mindannyiunk nevében!
Üdv:
Horváth Attila
Horváth Attila 2007 november, 22 - 15:42:39
Orso-borso: Örülök, hogy tudtam segíteni.
Kedves Attila: Köszönöm, ez nagyon jól esett. Ezekért a pillanatokért érdemes csinálnom. Igyekezni fogok továbbra is!
Én is kiteszlek a blogrollomba.
Till Zoltán 2007 november, 23 - 10:03:50
Kedves Zoli!
Köszönöm! Annyira jó az oldalatok, hogy elkerülhetetlen volt, hogy kitegyem.
Nagyjából azonos a célpiacunk, de nem vagyunk egymás konkurenciája – ezek az igazán jó dolgok.
Gratulálok a munkátokhoz! Sok sikert!
Üdv:
Attila
Horváth Attila 2007 november, 23 - 12:10:59
Sziasztok!
Van egy countif függvényem, kéne segíteni benne, valaki legyen kedves írni a mailcímemre aki ért hozzá! Előre is köszi!!!!!
Mike 2008 január, 13 - 16:16:38
igen a mailcímem : ekisim@citromail.hu 🙂
Mike 2008 január, 13 - 16:18:03
Mike, küldd el az e-mailcímemre!
A rólunk menüpontban, vagy a http://www.hatekonysag.hu oldalon megtalálod az e-mailcímem.
Till Zoltán 2008 január, 13 - 23:57:33
Tud-e segíteni valaki abban, hogy hogyan oldjam meg egy oszlop adatainak DARABTELI összeszámolását, de úgy, hogy egy másik oszlopban vannak elhelyezve a szétválogatás feltételei? Aktualizálva: A oszlopban van ATTILA és JÓZSI szöveg mondjuk 500-szor vegyesen, B oszlopban van mindkét szövegnél hol k, hol n betű. Számolja össze ATTILÁ-nál is, és JÓZSI-nál is külön, hogy hányszor szerepel náluk “K”!
Attila 2008 február, 14 - 12:06:14
Megtaláltam a megoldást!!! Egy amerikai honlapon, egy blogon találtam meg a SUMPRODUCT (magyarul SZORZATÖSSZEG) függvényt. Érdekes, hogy a függvény beszúrásától eltérő módon kellett a zárójeles tartományt megoldani (*-gal összekötve a két feltételt), és a COUNTIF-hez hasonló módon adja az eredményt, csak összetettebb feltételrendszer esetén is alkalmazható!!!
Attila 2008 február, 19 - 21:28:09
sziasztok, én szociológus vagyok, de azért megpróbálom érhetően elmondani, hogy mit szeretnék kérdezni.
a darabteli és a gyakorisági függvények tök jól használhatók, de… van-e olyan függvény, amely a “féleségeket, fajtaságokat” kilistázza, hogy aztán megszámoltassuk vele. vagy csak olyan megoldás létezik, hogy a függvénnyel kiíratjuk, az adott tétel hol fordul elő először, és aztán ezt alakítgatjuk tovább.
hálásan köszönöm, szupi az oldal,
üdv,
replika
replika 2008 június, 6 - 13:05:02
Replika, az egyszerűbben érthető megoldást írom le, ami ugyan sok cellát használ, de működik.
Megcsinálod a fenti első trükköt, így kapsz egy olyan listát, ahol mindegyikből megmutatja az elsőt.
Ezután pedig egy darabtelivel megszámoltatod ezeket az egyeseket.
Lehetne még tömbfüggvénnyel, de azt nem ilyen egyszerű elmagyarázni.
Till Zoltán 2008 június, 6 - 13:22:47
kedves Zoltán,
köszönöm 🙂 akkor legyen a tömbfüggvény 🙂
mondok egy gyakorlati példát: egyszer egy nyitott kérdést tartalmazó kérdőívben a “melyik városból érkezett a rendezvényünkre” kapott válaszokat az adatrögzítő felvitte, és ezt kellett összesítenem. ekkor ugye az említett településeket ki kelett listáznom, majd mellé írni, hányan mondták, hogy Budapest vagy Balatonszatymaz, de előtte fel kellett egyesével vezetni az említett településneveket.
murphy törvénye alapján a bonyolult dolgokat lusta emberekre kell bízni, mert ők gyorsan megtalálják az egyszerűbb elérési vonalakat 🙂 na, én is ilyen vagyok 🙂 most rögtön utánanézek a tömbfüggvénynek, és ha megengeded, kérdezek.
nagyon tetszik ez az oldal, végre beszélgethetek olyanokkal, akik NEM SZÍNEZNEK az excelben 🙂 nem mintha nem tudnék rászűrni 😉
üdv: replika
replika 2008 június, 7 - 16:09:43
de égés, nincs is ilyen függvény 😀 na jó, még gondolkodom egy kicsit 😉
replika 2008 június, 7 - 16:21:10
nézz utána inkább a kimutatás-nak, mert az listáz és összesít is.
és iratkozz fel az e-mail értesítőre, mert fogok írni róla!
Till Zoltán 2008 június, 7 - 16:23:54
létezik tömbfüggvény, csak az nem egy konkrét függvény neve, hanem függvény(ek) bizonyos módon történő alkalmazása
Till Zoltán 2008 június, 7 - 16:25:59
köszönöm a segítséget, Zoltán. Kicsit magamba szállok, és megpróbálok rájönni. Várom a cikket!
replika 2008 június, 7 - 16:29:45
Köszönet érte, hasznosítottam!!!
JaggerMeister 2008 június, 10 - 12:01:54
Replika, én ezt úgy szoktam csinálni, hogy:
irányított szűrés (Adatok/Szűrő/Irányított szűrő) -> más helyre másolja (ez csak ugyanazon a munkalapon lehet) és bejelölöm a “Csak az egyedi rekordok megjelenítés” jelölőnégyzetet.
Innen kezdve könnyen megszámlálható, összesen hányféle különböző előfordulás található, illetve a gyakoriság v akár darabteli függvények segítségével megszámlálható az egyes “fajták” előfordulása. Ráadásul a számlálás során a szomszéd oszlop azonos sorban lévő képletére cellahivatkozást használok, így a képlet azonnal másolható, nem kell beírni a különböző tartalmakat ;).
Máthé Gábor (Balu) 2008 június, 23 - 15:51:23
Teljesen amatőr vagyok. Gyönyörködöm az oldalban. Nagyszerű az eltérő cellák összeszámlálása. Pl. Hány településről érkeztek, stb. A sorrend2-ben hogyan iratható ki automatikusan, hogy az ELSŐ: ezüst?
Ha tippet kaphatnék hozzá, igen-igen megköszönném.
Üdvözlettel, Stefan
Stefan 2008 augusztus, 28 - 11:09:56
Pardon! Tovább is van még. MÁSODIK: és HARMADIK: stb.
Ismét köszönöm a hasznos tippeket.
Stefan
Stefan 2008 augusztus, 28 - 11:15:00
Helló!
Érdekelne, hogy hogyan tudnék összeszámláltatni egy bizonyos kritériumnak megfelelő cellákat egy oszlopban, de csak azokat, amelyek mellett a szomszédos (vagy egy bizonyos) oszlopban üres cellák (vagy akár más feltél) vannak. Az ab.darab2 függvénnyel meg tudom oldani, de nem szeretnék feltételtáblát használni. Valami olyasmire gondolok, mint a szumha függvény, de a darabteli függvényel nem tudtam megoldani.
Köszi:
Attila
Attila 2009 április, 2 - 15:27:05
Sziasztok!
Szeretném megkérdezni, hogy létezik-e, készíthető-e olyan fgv., amelyik egy oszlop elemeiből 4-et átlagol, majd a következő 4-et, és így tovább, mindig négyenként vagy akárhányanként, de mindig ugyanannyit.
horakane 2009 május, 14 - 06:19:17
Attila: az ötöslottó nyerőszámos bejegyzésben van a válasz a kérdésedre.
horakane: igen, pl. ofszet és átlag függvények keresztezésével
Till Zoltán 2009 május, 14 - 13:06:40
Ma néztem ide először, igen jónak találom az oldalt!
Imre 2009 június, 7 - 19:25:23
Ha szabad, kérdezek is rögvest: hogyan tudom mondjuk az A1 cellában meghatásozni, hogy B1 tartalmától függően mi legyen C1 értéke (tartalma)? Bizonyára egyszerű, de nekem magas. Előre is köszi a segítséget!
Imre 2009 június, 7 - 19:28:07
Imre, a HA függvénnyel foglalkozó bejegyzést nézd meg!
Till Zoltán 2009 június, 7 - 20:29:16
Sziasztok!
Kerlek segitsen aki tud. A kovetkezo a kerdes. A munkahelyemen excel tablazatban tartom nyilvan a szamlakat, azokat amik bejonnek es amik kimennek. A bejovo szamlaknal van egy olyan oszlop amibe beirom a hataridot, amig ki kell fizetni. Szeretnek egy olyan oszlopot, ami kiirja nekem, hogy a “mai naphoz” viszonyitva mennyi idom van meg a kifizetesre vagy eppen mennyi a kesedelem. Ha ebben valaki tudna segiteni, nagyon halas lennek. Elore is koszonom…
Judit 2009 június, 17 - 12:46:33
Judit,
ha az a2-es cellában van a határidő, akkor ezt kell beírd a kifizetésig napokhoz:
=ma()-a2
érdemes még megnézned a feltételes formázást hozzá.
Till Zoltán 2009 június, 17 - 13:48:54
Rendben köszönöm a gyors választ. Az a probléma, hogy én nekem kéne egy olyan cella, ahol minden nap automatikusan frissiti a dátumot, hogy ne kelljen ezt megtenni manuálisan. És majd ennek a cellának a segitségével, vagyis ehhez viszonyitva kellene az az oszlop, amely kiirja a hátralevő időt vagy a késedelmet.ha esetleg van egy eljárás, függvény, megköszönném ha leirnád.
Előre is köszike
Judit 2009 június, 18 - 06:50:25
=MA()
Ha beírod ezt, így egy cellába, akkor az kijelzi az aktuális dátumot.
Az előzőleg leírt megoldással sem neked kell frissítened.
Till Zoltán 2009 június, 18 - 08:17:07
Rendben, köszönöm…
Mostmár sikerült megoldani..az első magyarázatot is megértettem, csak az van hogy a gépemen román az excel, de mégis csak úgy jött össze hogy angolul irtam be a függvényt. Mindenképp a főnököm dicsérete, a te érdemed is.
köszönöm….
Judit 2009 június, 18 - 10:41:17
Sziasztok..
Megint felmerult egy kerdes…az a helyzet hogy mar reg tanultam az excel kezelest. Ugyancsak a szamlas tablazattal dolgozom, szepitem. A kerdesem..lehet-e excelben olyat csinalni, hogy: akarok egy olyan oszlopot, ahol meg tudom jelolgetni, hogy a szamlak kozul melyik van kifizteve es melyik var meg a kifizetesre. Hogy ne kelljen beirogatni, hogy “kifizetve” es “nincs kifizetve” szeretnek olyat hogy kis pipakkal jelolni a kifizetetteket, olyan kis ablakocskaba.Csak azt nem tudom, hogy excelbe lehetseges-e ez?
Nem tudom mennyire voltam ertheto.
Valaszotokat elore is koszike
Judit 2009 június, 18 - 13:09:14
Sziasztok!
Dátum függvénnyel, ill. számolás dátumokkal kapcsolatban szeretnék segítséget kérni.
Mi a megoldása annak, ha úgy akarom kiszámolni két dátum között eltelt napok számát, ha azt akarom, hogy a képlet vegye figyelembe az indulási, ill. érkezési időpontokat is úgy, hogy vegye figyelembe a 168/1995 korm. rendelet azon részét, hogy csak azokat a napokat kell egésznapoknak tekinteni, ahol a kinntartózkodás ideje eléri a 8:00 órát.
Például:
Indulás: 2009.06.22 06:20
Érkezés: 2009.06.24 23:10
Azaz 2 nap 16:50 ~ 3 nap legyen az eredményem
Indulás: 2009.06.22 06:20
Érkezés: 2009.06.24 10:20
Azaz 2 nap 04:00 ~ 2 nap legyen az eredményem
Segítségeteket előre is köszönöm.
Üdv.
Rajkai Zsolt
Rajkai Zsolt 2009 június, 25 - 08:35:51
Sziasztok,
van egy megoldásra váró feladatom, amivel már bajlódok egy ideje, és..lassan feladom. A segítségeteket szeretném kérni.
Egy több, mint 1000 soros és 40 oszlopos adatbázisról van szó, amiből szeretnék úgy kreálni táblázatot egy másik fülön, hogy az automatikusan frissüljön, ha az adatbázis bővül, vagy csökken (oszlopok helye nem változik, a hivatkozás tartalmazni fog üres sorokat is, mivel folyamatosan bővül a bázis). Konkrétan min.4 feltételnek kell megfeleljen az összeszámolandó adatom: pl. X ügyfél osszes “aktiv” ugylete, “2009.01-ben”, Y termekre. Ezek között egyik cella sem szám, itt csak a darabot szeretném megtudni. Második lépésként hasonló összetételben az adott ügyletek értékét is összesíteni szeretném. minden oszlopom legalább 12 féle kategóriát tartalmaz. (pl ügyfélből van 38 és váltakozva fordulnak elő)
És végezetül :), ha ez működik… ezt úgy szeretném dinamikusabbá tenni :), hogy pl. ha a combo boxban változtatom az ügyfelet, és a terméket, akkor a beképletezett táblám is ennek megfelelően szippantsa ki az adatokat a bázisból 🙂
…túl sokat akarok?? Biztos van rá megoldás 🙂
Köszönöm előre is.
memo 2009 augusztus, 16 - 01:15:16
Sziasztok!
A darabteli függvény kritériumának szeretném megadni, hogy ha egy cella tartalmaz egy bizonyos számsort, akkor legyen figyelembe véve, de akkor is, ha nem kizárólag a megadott számokat tartalmazza a cella, hanem más egyebet is. A windowsban erre való ugye a csillag. És az excelben?
ghabszhak 2009 szeptember, 9 - 07:27:51
Ghabszhak, Az excelben is működnek a joker karakterek: * és ?
Ha a számok számként funkcionálnak, akkor a kisebb, nagyobb jelekkel is megoldható.
Till Zoltán 2009 szeptember, 13 - 21:39:59
Üdv!
A kérdésem az lenne, hogy
képes-e az Excel >>a cella színe alapján<< számolni bármit is, vagy ilyen kritériumot képtelenség megadni neki?
Ez azért lenne jó, mert sokszor arra vagyok kíváncsi, hogy
1. az adatmezőben hány darab piros,zöld,sárga (akármilyen) színű cella van, és sokszor az érdekelne, hogy
2. az adatmezőben – mondjuk – zölddel jelölt cellákban lévő számok összege mennyi?
(Ilyen lehetőséget sehol nem látok, pedig bizonyos saját használatra készült adatbázisokban így jelölöm ki a különböző kategóriákba tartozó adatokat magamnak.)
Köszönöm előre is segítséget!
Ákos 2009 november, 28 - 02:21:41
Ákos,
2007-es Excelben már lehet színre szűrni, képletekben, függvényekben viszont ott sem használható.
A megoldás az, hogy kell rá egy egyedi függvényt készíteni, ami kinyeri a cella háttérszínét.
A színek kezeléséről itt olvashatsz többet: http://www.hatekonysag.hu/blog/hogyan-ne-hasznaljuk-az-excel-szinezest-sajnos-igy-hasznaljak.htm
Till Zoltán 2009 november, 28 - 11:05:21
Sziasztok!
Segítségeteket kérném egy feladat megoldásához, amivel el vagyok egy ideje.
Az egyik oszlopom soraiban lévő ismétlődő kifejezésekből kiválasztanék két különböző kifejezést (rászűrök két szövegre), majd ebből a rászűrt sorokból egy másik oszlopban egy másik szöveget keresek (erre is rászűrök), és azt akarom megtudni, hogy az ehhez (2. oszlophoz) tartozó szöveghez egy 3. oszlopban lévő forintok összege mennyi. Mindezt egy másik lapon kell összegeznem.
Angol nyelvű Excelben a COUNTIFS függvénnyel meg tudtam számolni a 2. oszlop fenti celláit, de a hozzá tartozó Ft összeg már nem megy. És mindezt egy magyar nyelvű Excelbe kell áttennem.
Megköszönöm, ha van valakinek ötlete vagy megoldása.
V.Zsuzsa 2010 január, 21 - 16:13:57
Kedves Zsuzsa, a SUMIFS, vagy szumhatöbb függvény lehet megoldás, ha 2007-es Excelt használsz.
Till Zoltán 2010 január, 25 - 15:51:56
Sziasztok!
Remélem tudtok segíteni, mert elég csúnyán elakadta, és nem tudom mi lehet a gond.
Darabteli fv-t használok, de a függvény megírása után a cellába mindig nullát ír ki. És itt kezdődik a móka: ha a már megírt fv cellájára kattintok, majd a szerkesztőléc előtti “függvény beszúrása” gombra, akkor előjön a már megírt darabteli fv Függvényargumentumok című ablaka, ahol a bal alsó sarok környékén az érték után kiírja a helyes eredményt. Magyarán nekem úgy tűnik, hogy a fv képes kiszámolni a helyes megoldást (tehát nem rontottam el teljesen), de a cellába már nem írja ki.
Remélem egy tipushibát vétettem, és ki tudtok javítani!
Köszönettel
D.Zoli 2010 március, 10 - 12:18:43
Kedves Till Zoltán!
Az előbbiekben az alábbi hozzászólást tetted:
“Ákos,
2007-es Excelben már lehet színre szűrni, képletekben, függvényekben viszont ott sem használható.
A megoldás az, hogy kell rá egy egyedi függvényt készíteni, ami kinyeri a cella háttérszínét.
A színek kezeléséről itt olvashatsz többet: http://www.hatekonysag.hu/blog/hogyan-ne-hasznaljuk-az-excel-szinezest-sajnos-igy-hasznaljak.htm
Till Zoltán 2009 november, 28 – 11:05:21″
Tudnál nekem segíteni abban, hogyan készítsek egyedi fv-t a cella háttérszínénk meghatározására?
Üdv: Fani
FaniApu 2011 január, 6 - 12:28:25
Lehet, hogy ez valakinek egyszerű. Kérem segítsen!
Ezt szeretném:
Munkalap1: A oszlop “töltődik” mérésekkel. Egy mérés három érték. Sajnos a mérő progi csak oszlopba töltve ontja az adatokat. Ezt kellene puccos táblázatba rendezni egy mérést három oszlopba.
Azt szeretném, hogy a Munkalap2 (továbbiakban M2)csini táblázata vízszintesen töltődjön a Munkalap1 (M1)A oszlop mérési eredményeivel.
M1
A
1
2
3
4
5
6
7
8
9
stb
Ez a M2 táblázatában így nézzenk ki:
A B C
M1 A1, M1 A2, M1 A3
M1 A4, M1 A4, M1 A6
M1 A7, M1 A8, M1 A9
M1 A10, M1 A11, M1 A12
stb
Honey 2011 február, 12 - 15:17:47
Nem csak a lustaság miatt, de nagyon sok adatot kell így S.O.S-ben rendszereznem. Help!
Honey 2011 február, 12 - 15:19:51
Hogy néz ki a DARABTELI képlet, ha nem egy cellát kell összehasonlítani, hanem akár négy öt vagy több cellát? Tehát egy sorban az A2 cellához több cella tartozik és azok között kell sorrendet, előfordulást keresni? Pl.: A2-ben labda van, a B2-ben bőr, a C2-ben kézi van, míg az A3-ban labda van, a B3-ban bőr és a C3-ban foci szó van. =DARABTELI($B$2:$B3;B3) ez az a függvény, ami a megoldást adja, ami érdekel…
Milu 2011 június, 14 - 23:49:06
Helló !
Nagy segítség volt nekem ez az oldal, csak kerestem egy megoldást egy függvényre, és a Google ezt az oldalt hozta az első helyen, ami tök jó, nem kellett sokat keresgélnem a tökéletes megoldáshoz.
Még 1x köszi !
Nagyon jó, hogy van ilyen oldal !
Tamás
Németh Tamás Péter 2011 június, 18 - 15:18:54
Kedves Zoltán!
Kiváló az oldal, nagyon köszönöm a lehetőséget, hogy beszerezhetem a nekem létfontosságú információkat.
üdv: Zs
Molnár Zsolt 2012 május, 12 - 17:49:57
Sziasztok! Nézi még valaki ezt az oldalt? A kérdésem, hogy milyen függvénnyel kell a cellákban egy bizonyos szövegrészlet előfordulási gyakoriságát megtalálni? Tehát van egy hosszú szövegem, ami többször ismétlődik, de ebből csak egy részlet gyakoriságát szeretném összeszámolni. Nem tudom ez érthető-e így?
Köszönöm ha valaki válaszol!
bj 2012 június, 7 - 15:04:25