-
Fototrend
A Microsoft Excel topic célja segítséget kérni és nyújtani Excellel kapcsolatos problémákra.
Kérdés felvetése előtt olvasd el, ha még nem tetted.
Új hozzászólás Aktív témák
-
Fferi50
őstag
válasz bozsozso #42052 üzenetére
Szia!
"úgy egészíteni, hogy a gyümölcsökhöz tartozó számokat az alján összesítse, hogy hány db"
Úgy értettem, hogy össze kellene adni a számokat (összesítse). Ha a tételszámra vagy kíváncsi, akkor Darab2 függvény a második sortól indulva.
Miután befejezted a Powerqueryben a munkát, visszaadod a táblát és a vezérlést az Excelbe. Ott pedig minden Excel függvény él természetesen.
Üdv. -
Fferi50
őstag
válasz Petium001 #42053 üzenetére
Szia!
Az oszlop utolsó nem üres cellája után beírod a képletet:
= Darabteli($D$2:$D$400;"igen")
Itt a D oszlop 2-400 cellájában vannak az értékek.
Egy buktatója van: Ha az igen nem szöveg, hanem logikai érték - pl. összehasonlítás utján kapod - akkor az IGEN() függvényre kell keresni, "igen", helyett IGEN() kell a második paraméterhez.
Használd a függvényvarázslót légy szíves, könnyebben megérted.
Üdv. -
Peterhappy
őstag
-
Fferi50
őstag
válasz Peterhappy #42056 üzenetére
Szia!
Ez azért van, mert az adott mezőben nem dátumok vannak, hanem "számnak látszó" szövegek. A kimutatás forrás mezőjét kellene átalakítanod dátummá, ahogyan ebben a hozzászólásban írták (#42032 ny.jános), a Dátumérték függvénnyel. De talán még jobb lenne a DÁTUM függvény használata, a 42025-os hozzászólásodban levő Összefűz függvény paramétereire hivatkozással:=DÁTUM (Szöveg1;Szöveg3;Szöveg5)
Vagyis a DÁTUM függvénybe az adott paraméterhez írt képletet tedd bele.
Mert az Excelben dátum tekintetében sok esetben nem az van, mint amit látsz. Jelen esetben is, hiába fűzöd össze az értékeket "dátum formátumnak megfelelően", attól az még nem lesz dátum.
De még többet látnánk, ha a forrásod egy részletét megmutatnád, amiben ezek az értékek szerepelnek. -
Peterhappy
őstag
válasz Fferi50 #42057 üzenetére
Szia!
Én nem a forrás mezőt, hanem az összefűzés eredményét próbáltam a szomszéd cellába átmásolni - ez utóbbit formázta is, míg a forrás mező átalakításánál #érték hibát jelenített meg..
Így néz ki a riport - soha ilyen rettenetes riportot nem láttam még.
Az összefűzés képlete:
=ÖSSZEFŰZ(KÖZÉP(G2;SZÖVEG.KERES("/";G2;4)+1;4);". ";HA(HOSSZ(BAL(G2;SZÖVEG.KERES("/";G2)-1))=2;"";0);BAL(G2;SZÖVEG.KERES("/";G2)-1);". ";HA(HOSSZ(KÖZÉP(G2;SZÖVEG.KERES("/";G2)+1;(SZÖVEG.KERES("/";G2;4)-1)-(SZÖVEG.KERES("/";G2))))=2;"";0);KÖZÉP(G2;SZÖVEG.KERES("/";G2)+1;(SZÖVEG.KERES("/";G2;4)-1)-(SZÖVEG.KERES("/";G2)));".")Nem szép, de elvileg működik És ebből dátumot is tud csinálni a dátumérték - más kérdés, hogy nem tudom használni.
Neked is köszönöm a segítséget, tök jó hogy ilyen türelmesek vagytok, ez nekem már nagyon magasröptű
-
ny.janos
tag
válasz Peterhappy #42060 üzenetére
Bár a Power Query-t nem igazán ismerem, épphogy csak megpróbáltam használni egyetlen alkalommal, de az biztos, hogy amennyiben a G oszlopban látható riportált adat szóköznél történő szétválasztását választod akkor kettő külön oszlopot fogsz kapni. Az elsőben lesz a kívánt dátumod, míg a másodikban az időpont. Ha ez utóbbira nincs szükséged, akkor törölheted az oszlopot. Ha így töltöd be végül az adataidat, akkor nincs szükséged utána semmilyen képletre ahhoz, hogy dátumot kapj.
-
karlkani
aktív tag
Sziasztok!
Az megoldható, hogy egy táblában, feltételes formázással módosított betűszínű cellák számát összegezzem?
-
Fferi50
őstag
válasz Peterhappy #42060 üzenetére
Szia!
Működik az adatok - szövegből oszlopok menüpont az Excelben megában. Nem kell hozzá Power Query.
Adatok - szövegből oszlopok - tagolt - a következő lapon (2. lépés) bejelölöd elválasztónak a szóközt. A következő lapon (3.lépés) az első oszlopra bejelölöd, hogy dátum NHÉ formátumban. Megadod hova kerüljön - nyilván a mellette levő oszlopba, hogy a forrás megmaradjon.
Ezután valóban dátum lesz az az oszlop, amibe az első része kerül az adatnak.
Más: Nem értem, ha a dátum oszlopodnak Dátumérték a mezőneve, akkor a kimutatásodban miért nem az az oszlop van benne?Üdv.
-
Fferi50
őstag
-
karlkani
aktív tag
válasz ny.janos #42063 üzenetére
Háttérszín alapján cellaszám, cellaérték összegzésre találtam makrót. Nekem olyan kellene, ami feltételes formázással módosított betűszínű cellák számát összegezi. A táblában több, feltételes formázással módosított betűszínű cella van, melyek közül csak a piros színűek darabszáma kell. Neten csak olyan makrót találtam, ami abban az esetben működik, ha a betűszín nincs feltételes formázással módosítva.
[ Szerkesztve ]
-
karlkani
aktív tag
válasz Fferi50 #42066 üzenetére
Szia!
Public Function CountColor(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
CountColor = CountColor + 1
End If
Next
End Function
Ezt kellene módosítani (ha lehet egyáltalán...), hogy működjön feltételes formázással módosított betűszín estén.
-
ny.janos
tag
válasz karlkani #42069 üzenetére
Makróhoz nem értek, így abban majd segítenek a nálam okosabbak, de ehhez nem is feltétlen alkalmaznék makrót.
Beszúrhatsz egy új oszlopot, ahol a feltételes formázás képletét megadod, majd a szorzatösszeg függvénnyel megkapod a kívánt végeredményt. Szorzatösszeg helyett használhatsz szum függvényt is, de azt tömbképletként kell (Ctrl+Shift+Enter) alkalmaznod.
Segédoszlop nélkül is megoldható. Pl. A1:A31 tartomány azon értékeinek összegzése, amelyek nagyobbak 30-nál:=SZORZATÖSSZEG(($A$1:$A$31)*($A$1:$A$31>30))
vagy tömbképletként{=SZUM(($A$1:$A$31)*($A$1:$A$31>30))}
[ Szerkesztve ]
-
Fferi50
őstag
válasz karlkani #42069 üzenetére
Szia!
Mivel feltételes formázás van, ezért a DisplayFormat tulajdonságot kell használni, ezt viszont függvényben sajnos nem tudja a VBA (a 2016-os legalábbis). Ezért nem függvényt, hanem Sub-ot kell használni:Public Sub CountColor(pRange1 As Range, pRange2 As Range)
Dim rng As Range, xcolor As Long, CountColor As Integer
xcolor = pRange2.DisplayFormat.Font.color
For Each rng In pRange1
If rng.DisplayFormat.Font.color = xcolor Then
CountColor = CountColor + 1
End If
Next
Range("A1").Value = CountColor 'ide azt a cella címet írd, ahová az eredményt szeretnéd
End Sub
Ezt viszont nem lehet felhasználói függvényként meghívni. A két paraméter ugyanaz, mint az általad leírt függvényben, pRange1 amiben keressük a cellákat, pRange2 aminek a színét számoljuk. Azért talán ezzel is tudsz ügyeskedni.
Más ötlet. Talán mégsem a színek szerinti összesítés a nyerő. Feltételezem, hogy a feltételes formázásnak a feltételei valamilyen táblázat alapján működnek (jó kis mondat lett a feltételek halmozásával... ), mivel írtad, hogy pl. ünnepnapok. Ez alapján is lehetne a számolást elvégezni makró nélkül, valamilyen számláló képlettel, ami megvizsgálja, hogy az adott dátum benne van-e a "táblázatban". Szóval én nem vetném el ny.janos ötletét sem.Üdv.
-
-
Fferi50
őstag
válasz karlkani #42071 üzenetére
Szia!
Az előbbi hozzászólásban szereplő makró kiegészíthető egy harmadik paraméterrel, ahova az eredményt kéred.Public Sub CountColor(pRange1 As Range, pRange2 As Range,pRange3 As Range)
Dim rng As Range, xcolor As Long, CountColor As Integer
xcolor = pRange2.DisplayFormat.Font.color
For Each rng In pRange1
If rng.DisplayFormat.Font.color = xcolor Then
CountColor = CountColor + 1
End If
Next
pRange3.Value = CountColor
End Sub
Üdv.[ Szerkesztve ]
-
Fferi50
őstag
válasz ny.janos #42073 üzenetére
Szia!
Akkor már talán egyszerűbb, ha a segédoszlopba a feltételes formázás képlete alapján értékeket írunk, mert azokat utána csak meg kell számolni.Pl. =HA(A1>30;"C";HA(A1>20;"B";HA(A1>10;"A";"X")))
Ezután már csak a betüket kell megszámolni DARABTELI függvénnyel.Üdv.
-
Bobrooney
senior tag
válasz TigerCat #42077 üzenetére
Makróbarát Excel munkafüzetként mentsd el. De elvileg mentés előtt figyelmeztet is hogy ha nem változtatsz fájl formátumot akkor elvesznek a makróid.
Szóval Fájl -> Mentés másként -> xlsm formátumba mentsd el.
Ha újra nyitod figyelj oda a makróbeállításokra (pl. ne tiltsa le azonnal).Remélem tudtam segíteni.
-
karlkani
aktív tag
válasz Fferi50 #42072 üzenetére
Szia!
Értem.
Jól sejted, van egy másik lap, onnan nézi a dátumokat.(#42073) ny.janos
Feltételes formázásnál ez a képlet szerepel:=DARABTELI(Ünnepnapok;$C1)>0
(#42076) Delila_1
Ezzel a képlettel működik:=HA(DARABTELI(Ünnepnapok;C2251)>0;DARABTELI(Ünnepnapok;C2251);"")
Annak idején segítettél nekem létrehozni ezt a makrót:
Function Orak(tartomany As Range)
Dim CV As Range, osszeg As Double, WSD As Worksheet, WF As WorksheetFunction
Set WSD = Sheets("Dátum")
Set WF = Application.WorksheetFunction
For Each CV In tartomany
If Not IsNumeric(CV) Or CV = "" Then GoTo Tovabb
If WF.CountIf(WSD.Columns(4), Cells(CV.Row, "C")) = 0 And _
WF.CountIf(WSD.Columns(6), Cells(CV.Row, "C")) = 0 And _
WF.Weekday(Cells(CV.Row, "C"), 2) < 6 Or _
WF.CountIf(WSD.Columns(2), Cells(CV.Row, "C")) > 0 Then osszeg = osszeg + CV
Tovabb:
Next
Orak = osszeg
End Function
Már többször előfordult, hogy a füzet megnyitásakor az összes olyan cella értéke, ami ezt a makrót használja összegzésre 0-ra módosult. Csak úgy tudtam újraszámoltatni, hogy a cellára álltam, szerkesztőléc, Enter (sok cellánál ez elég macerás), vagy átneveztem a fájlt, vagy töröltem a fájlra vonatkozó bejegyzést a regisztrációs adatbázisból az alábbi helyen.
HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Security\Trusted Documents\TrustRecords
Ötlet?
-
Fferi50
őstag
válasz karlkani #42081 üzenetére
Szia!
A segédoszlopban miért nem jó neked Delila képlete, hiszen az csak akkor ad 0-nál nagyobb értéket, ha előfordul az ünnepnapok között az érték. Számolásnál nem mindegy neked, hogy össze kell adni az értéket vagy megszámolni hány db 0-nál nagyobb érték van?
A második kérdésre csak tippem van: nem lehet, hogy ilyenkor értesítés nélkül le van tiltva a makró futtatás az Excelben a biztonsági beállítások között?Üdv.
-
Delila_1
Topikgazda
-
Fferi50
őstag
válasz karlkani #42083 üzenetére
Szia!
Ebben az esetben nem kell hozzá segédoszlop! Elég egy tömbképlet, hogy az eredményt lásd:
Pl.=SZUM(DARABTELI(Ünnepnapok;$C$2200:$C$2255))
Ez akkor ad jó értéket, ha egy ünnepnap csak egyszer fordul elő a keresendő értékek között. Ha többször is előfordulhat, akkor=SZUM((DARABTELI(Ünnepnapok;$C$2200:$C$2255)>0)*1)
a tömbképlet. Azaz Shift+Ctrl+Enterrel kell bevinni és kapcsos zárójelek közé teszi az Excel.
VBA-ban pedig:Range("X2").FormulaArray="=SUM((COUNTIF(Ünnepnapok,$C$2200:$C$2255)>0)*1)"
Üdv.
[ Szerkesztve ]
-
karlkani
aktív tag
-
Peterhappy
őstag
válasz Fferi50 #42064 üzenetére
Szia Feri!
Köszönöm szépen a segítséget, ez egy nagyon szép megoldás és tök jól is sikerült
Viszont egy dolgot még mindig nem értekVégre van számformátum gombom is, de hiába állítok ott be bármit is, nincs ráhatással az értékre. Dátum a forrás formátuma (általános-ra állítva nem is látszott a gomb).
"Más: Nem értem, ha a dátum oszlopodnak Dátumérték a mezőneve, akkor a kimutatásodban miért nem az az oszlop van benne?"
Utólag, direkt a print screen miatt neveztem át őket, hogy érthetőbb legyen, hol és mit csináltam. -
Fferi50
őstag
válasz Peterhappy #42087 üzenetére
Szia!
ny.janosé az ötlet, nem akarnék más tollával ékeskedni.
A számformázással küzdök egyet én is, mert most nálam is határozottan ellenkezik velem az Excel.
Üdv. -
Fferi50
őstag
válasz Peterhappy #42087 üzenetére
Szia!
Te most fogod a fejed a falba verni, én már túlvagyok rajta!
El kell felejteni az Excel dátumbontását, mert egyszerűen nem működik tisztességesen a kimutatásban. Ahhoz hogy rendesen láthassuk a nap - hónap - év felépítését és a napok "olvasható" formában szerepeljenek, mindent amit eddig gondoltunk, sutba kell dobni. Legalábbis a 2016-os Excel ilyen rettenetesen viselkedik, hiába állítgatjuk a formátumot, az neki smafu.
Mit is kellett csinálnom, hogy ilyen formátumú kimutatást kapjak:Három + oszlopot csináltam.
1. oszlop az évet tartalmazta az év függvény szerint (azaz =ÉV(A2))
2. oszlop a hónapok nevét a HÓNAP függvény és a Választ függvény használatával:=VÁLASZT(HÓNAP(A2);"JANUÁR";"FEBRUÁR";"MÁRCIUS";"ÁPRILIS";"MÁJUS";"JÚNIUS";"JÚLIUS";"AUGUSZTUS";"SZEPTEMBER";"OKTÓBER";"NOVEMBER";"DECEMBER")
3. oszlop a dátumot szöveg formátumban:=ÉV(A2) & "." & JOBB("00" & HÓNAP(A2);2) & "." &JOBB("00" & NAP(A2);2)
Ezután a kimutatásba ez a három oszlop került be és szépen is mutat.Nem tudom, mitől bolondult meg, mert a korábbi Excelben létrehozott kimutatásaim szépen működnek dátum alábontással továbbra is.
Talán a korábban létrehozott oszlopaid közül fel tudsz használni párat....
Még utána nézek, hogy Power Pivotban lehet-e valamit varázsolni. mert ez így nagyon-nagyon gáz szerintem.Üdv.
[ Szerkesztve ]
-
Peterhappy
őstag
válasz Fferi50 #42090 üzenetére
Hát ez zseniális
Egy kicsit most örülök, hogy ezek szerint nem én vagyok iszonyatosan láma, hanem az office 2016
Ennyit az egész nem ér, túl fogom élni, ha nem tudok havi összegzést csinálni, már így is nagyon-nagyon jól néz ki a dolog, ne fáradj, ne fáradtjatok vele többet! És köszönöm, le a kalappal előttetek, nagyon sokat segítettetek + hozzám vaskos mennyiségű türelem kell, szóval tényleg nagyon köszönöm mindannyiótoknak
-
Fferi50
őstag
válasz Peterhappy #42091 üzenetére
Szia!
A PowerPivotból tudja normálisan. Lépések:
A fejlesztőeszközök - Com bővítmények között aktíválod a PoverPivot bővítményt
1. Nyitsz egy új üres munkafüzetet.
2. Átmész a PP ablakba - a Kezelés menüponttal
3. Kezdőlap - Külső adatok beolvasása -- Más forrásokból - kiválasztod az Excel fájlt,
4. Kiválasztod az Excel fájlod, amiben az adatok vannak.
5. Kiválasztod a munkalapot belőle ($ jel lesz a név végén).
6.Beolvasás. - ha vannak ott olyan adatok, amelyekre nincs szükséged, törölhetők
7. Még mindig a PP ablak Kezdőlap fülén - Kimutatás
és innentől úgy, mint az Excelben, kiválasztod a mezőket és az összegzési függvényeket, a kimutatás az Excel munkalapra készül el.
Ez már normálisan formázza a dátumot. (Legalábbis nálam így volt.)Üdv.
-
vandeminek
tag
Sziasztok!
Van egy Excel fájlom, amiben a megjegyzések nem jelennek meg. Tudom, kapcsolgattam már a megjegyzések megjelenítése gombot A megjegyzést jelző kis piros háromszög megvan, sőt ha ráviszem az egeret, akkor a megjegyzés nyila is látható, de semmi más:
Mi lehet a megoldás? Volt már ilyen problémája másnak is? Ha ilyenre keresek Google barátom mindig csak a megjegyzések megjelenítése gombot hozza sajnos
Köszi!
Szerk.: közben találtam rá megoldást valahogy a szélességük lett lenullázva. Jobb klikk / jegyzet szerkesztése és átméretezés után már jó!
[ Szerkesztve ]
-
őstag
Sziasztok!
Kezdő vagyok nagyon VBA-ban és elakadtam. Ebben kérnék segítséget, mert hiába túrom a netet egyszerűen nem áll össze amit szeretnék megvalósítani.
A szituáció:
Adott több táblázatként formázott tábla.
A forrás táblákban rengeteg oszlop van (80)
A cél táblában 8 oszlop van.
Az összes forrás táblából adott 8 oszlopot (nem egymás mellett levőket) szeretnék a cél táblába másolni úgy, hogy a fejléc ne, de az összes érték átkerüljön.
Az első táblánál még csak rendben is van, hogy egyenként bemásoltatom az oszlopokat, de a második táblánál meg kellene kerestetni a céltábla adott oszlopának utolsó adatot tartalmazó celláját, hogy az alá tegye a következő oszlopot.Eddig jutottam:
Private Sub CommandButton6_Click()
Worksheets("Munka_forras").ListObjects("Tbl_forras_1").ListColumns("Név").DataBodyRange.Copy
Worksheets("Munka_cel").ListObjects("Tbl_cel").Range(2, 1).PasteSpecial xlPasteValues
Worksheets("Munka_forras").ListObjects("Tbl_forras_1").ListColumns("Cim").DataBodyRange.Copy
Worksheets("Munka_cel").ListObjects("Tbl_cel").Range(2, 2).PasteSpecial xlPasteValues
End Sub
Kérdés:
Nem lehet valahogy össze vonni, hogy ezeket a másolásokat egyszerre végezze? Tehát kijelöli az összes oszlop adatát amit szeretnék és egyszerre bemásolja?!
Hogy tudom megoldnai, hogy ne a Range(2,2) -vel monjam meg hova tegye, hanem mondjuk az oszlop nevét adjam meg ?!
A második táblánál hogy fogom megmondani, hogy a már létező adatok alá másoljon?
Ezt találtam:
With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With
Csak sehogy nem bírom beilleszteni.
Valaki tudna ebben segíteni?
Köszönöm
[ Szerkesztve ]
-
Fferi50
őstag
válasz Richard #42095 üzenetére
Szia!
1. Amennyiben a cél táblába egymás mellé kerülnek az oszlopok, akkor az Union függvény használatával egyben átmásolható a kívánt oszlopszám:
Az értékek átmásolásához célszerű a céltáblát változóba tenni:Dim celtabla As Listobject, usor As Long
Set celtabla =Worksheets("Munka_cel").Listobjects("Tbl_cel")
With Worksheets("Munka_forras").Listobjects("Tbl_forras_1").
Union(.Listcolumns("Név").Databodyrange,.Listcolumns("Cím").Databodyrange…..).Copy
End With
2. A céltábla utolsó sor után a bemásolás:celtabla.Databodyrange.Listcolumns(1).Cells(1).End(xlDown).Offset(1,0).Paste Paste:=xlPasteValues
Az első másolásnál még maradhat a Range(2,1), de ne feledd ekkor egyben jön a 8 oszlop adata már.
Természetesen a sok tábla másolását csinálhatod ciklussal és akkor a forrás tábla neve helyett az indexét használhatod.
Ha kérdésed lenne, csak írj.Üdv.
[ Szerkesztve ]
-
őstag
válasz Fferi50 #42096 üzenetére
Köszönöm, így tökéletes a kijelölés.
A másolást azonban nem hajtja végre csak akkor, ha ezt írom:
celtabla.Range(2, 1).PasteSpecial xlPasteValues
Így azonban mindig legfelülről kezdi sajnos.
Az általad írt kódra kiáll ezzel a hibával:
Amennyiben üres még a tábla:
Run-time error '91': Object variable or With block variable not setAmennyiben már van a táblában egy "adag" másolva:
Run-time error '438' Object doesn't support this property or methodMit rontok el?
"Természetesen a sok tábla másolását csinálhatod ciklussal és akkor a forrás tábla neve helyett az indexét használhatod."
Ezt pedig nem is igazán értem -
Fferi50
őstag
válasz Richard #42097 üzenetére
Szia!
Úgy tűnik, rosszul raktam össze a célba való másolást, az első másolás miatt pedig betettem egy vizsgálatot.If celtabla.Range(2, 1).Value = "" Then
celtabla.Range(2, 1).PasteSpecial xlPasteValues
Else
celtabla.ListColumns(1).DataBodyRange.Cells(1).End(xlDown).Offset(1, 0).Paste Paste:=xlPasteValues
End If
Azt írtad, hogy több táblából másolsz. Ha ez kevés számú, akkor lehet egymás után többször leírni a kódot más-más táblanevekkel. De már 3 után is megéri ciklusba szervezni. A kérdés az, hogy a táblák külön-külön munkalapon vannak-e (feltételezem), de ugyanazon munkafüzetben..
Ebben az esetben a ciklus:Sub a()
Dim sh As Worksheet, tbl As ListObject
For Each sh In Worksheets
If sh.ListObjects.Count > 0 Then
For Each tbl In sh.ListObjects
With tbl
Union(.ListColumns("Név").DataBodyRange, .ListColumns("Cím").DataBodyRange…..).Copy
End With
If celtabla.Range(2, 1).Value = "" Then
celtabla.Range(2, 1).PasteSpecial xlPasteValues
Else
celtabla.ListColumns(1).DataBodyRange.Cells(1).End(xlDown).Offset(1, 0).Paste Paste:=xlPasteValues
End If
Next
End If
Next
End Sub
Üdv.
Ps.
"a táblázat alá ugrik és amikor beilleszti a következő oszlopokat, akkor automatán kiterjeszti a táblát is."
Miért, nem kellene a táblázatot kiterjesztenie a beillesztett adatokra?[ Szerkesztve ]
-