- Kempingezés és sátrazás
- sziku69: Fűzzük össze a szavakat :)
- Luck Dragon: Asszociációs játék. :)
- D1Rect: Nagy "hülyétkapokazapróktól" topik
- gban: Ingyen kellene, de tegnapra
- Geri Bátyó: B550 szűk keresztmetszet, de mi és miért?
- Chosen: Canon 5D II - portrézás 2025-ben
- Doky586: Helyreállítási partíció létrehozása (javítása)
- Gurulunk, WAZE?!
- bitpork: Augusztus 2- szombat jelen állás szerint.
-
LOGOUT
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
-
#32824
Fferi50
Topikgazda
TrollBalint
#32817
Fferi50
Topikgazda
válasz
TrollBalint
#32817
üzenetére
Szia!
Mutatnál egy képet, esetleg feltennéd valahova a mintafájlt, mert sajna nem tudok mire gondolni pillanatnyilag.
Üdv.
-
#32793
Fferi50
Topikgazda
TrollBalint
#32788
Fferi50
Topikgazda
válasz
TrollBalint
#32788
üzenetére
Szia!
Szerintem "csupáncsak" annyi a baj, hogy a táblázatodban vannak üres sorok és ezek is bele vannak foglalva a képletbe - emiatt a MIN függvény természetesen 0 lesz, ami dátumformátumban 1900.01.00.
Ezért a képleteket elsőre úgy kell megadni, hogy csak a tele sorokat tartalmazza. Mivel a table forma maga automatikusan bővül, ha beviszel egy új sort az első táblába, a képlet figyelembe fogja venni "magától". Ha új nevet viszel be, azt át kell írnod a második táblába a következő sorba, a képlet itt is automatikusan működik.
Természetesen amíg a kezdő dátumhoz nem írsz semmit, addig a második tábla első oszlop adataiban 0 érték fog megjelenni.
Csak a tömbképlet működik helyesen!!!
(A sok # azért lett, mert negatív időt nem tud megjeleníteni az Excel.)Üdv.
-
#32716
Fferi50
Topikgazda
TrollBalint
#32713
Fferi50
Topikgazda
válasz
TrollBalint
#32713
üzenetére
Szia!
Próbáld ki ezeket a tömbképleteket:
G2 cella:=KICSI(HA($A$2:$A$7=F2;1;100)*1*($B$2:$B$7);1)
H2 cella:=MAX(($A$2:$A$7=F2)*1*($C$2:$C$7))
Ezután az oszlopokon lehúzod az adataid végéig.
(Tömbképlet bevitele: Shift + Ctrl + Enter)Üdv.
-
#32711
Delila_1
veterán
TrollBalint
#32710
Delila_1
veterán
válasz
TrollBalint
#32710
üzenetére
Az A oszlopot másold át az F-be. Az adathalmazból az ismétlődések eltávolítása menüpontot alkalmazva minden neved egyszer fog szerepelni ebben az oszlopban.
A képen látható képleteket vidd be a G és H oszlopba, ezekből kiszámíthatod a legkisebb és legnagyobb időpontok között eltelt időt.
A G oszlop képlete akkor lesz megfelelő, ha a B oszlopban növekvő sorrendben vannak az idők.Az FKERES angol megfelelője a VLOOUP, a Keres-é a LOOKUP.
-
#30149
Grodd
tag
TrollBalint
#30139
Grodd
tag
válasz
TrollBalint
#30139
üzenetére
Valóban, ez így tényleg járható, erre nem gondoltam. És nem is annyira macerás megadni az összes szabadszombatot, ha van PowerQuery vagy Excel 2016. Csak List.Dates-szel behívod az összes szombatot (7-es step-pel az első szombattól, amennyit akarsz), és left anti merge-el kivonod az amúgy is megadott dolgozós szombatok listáját. Így egy-két egérkattintás az egész.
-
#30125
Grodd
tag
TrollBalint
#30123
Grodd
tag
válasz
TrollBalint
#30123
üzenetére
Hasonló az alapelve annak is, csak az enyém tömörebb
És még tömörebbé lehet tenni, ha beveted a name managert a 24*(műszakvég - műszakkezdet) helyettesítésére...Az networkdays.intl nem segít a szombatáthelyezésekben, mert azzal nem a dolgozós vagy szabad szombatok listáját tudod megadni paraméterként, hanem azt, hogy a hét mely napjai legyenek hétvégék (mert ez vallásonként/nemzetenként/régiónként eléggé variálódhat).
Vagy hogy gondoltad a dolgot?
-
#30119
Grodd
tag
TrollBalint
#30117
Grodd
tag
válasz
TrollBalint
#30117
üzenetére
próbáld meg így:
=(NETWORKDAYS(kezdet,vég)*24*(műszakvég - műszakkezdet))-IF(WEEKDAY(kezdet,2)>=6,0,MIN(MAX(24*(MOD(kezdet,1)-műszakkezdet),0),24*(műszakvég - műszakkezdet))) - IF(WEEKDAY(vég,2)>=6,0,MIN(24*MAX((műszakvég-MOD(vég,1)),0),24*(műszakvég - műszakkezdet)))
Ahol "kezdet" a kezdőidőpot, "vég" a záróidőpont, "műszakkezdet" a normál műszak kezdőideje, "műszakvég" a vége.
-
#29533
sztanozs
veterán
TrollBalint
#29532
sztanozs
veterán
válasz
TrollBalint
#29532
üzenetére
-
#29529
sztanozs
veterán
TrollBalint
#29528
sztanozs
veterán
válasz
TrollBalint
#29528
üzenetére
Array fügvényt nem sima enter-rel hanem shift+ctrl+enter-rel viszel be.
Illetve sima függvényként (ez előzőleg meghatározott Range-ek használatával) a következőnek is működnie kell:
=SUMPRODUCT(SUMIF(src_a,choises,src_b))Ez ráadásul működik úgy is, hogy nincs növekvő sorba rendezve az src_a
-
#29520
sztanozs
veterán
TrollBalint
#29515
sztanozs
veterán
válasz
TrollBalint
#29515
üzenetére
sumifs szerintem a megoldás - amúgy miért gond az 52* lemásolás? megírod egyszer, és lehúzod az 52. sorig (sőt, ha több lapot kijelölsz egyszerre, akkor az összes lapon odamásolja a függvényt)
-
#29517
Delila_1
veterán
TrollBalint
#29515
Delila_1
veterán
válasz
TrollBalint
#29515
üzenetére
Még annyit, hogy az emberek lapjain mindenhol van címsor.
-
#29516
Delila_1
veterán
TrollBalint
#29515
Delila_1
veterán
válasz
TrollBalint
#29515
üzenetére
Nem írtad meg az adatok pontos elhelyezkedését, nem vetted figyelembe a Téma összefoglalóban leírtakat. Ezért úgy írtam egy makrót a megoldáshoz, ahogy elképzeltem, majd átírod.
Az első lap neve Adatok, ennek az A oszlopában vannak a tantárgyak, a B-ben a hozzá tartozó értékek.
A többi lap az egyes emberek adatait tartalmazza. Az A oszlopban választod ki az I. héthez tartozó tantárgyakat, a B-ben a II. hetit, az AZ-ben az 52. hetit.Az összes pontszám mindegyik lapon a BA1 cellában jelenik meg a makró futtatása után.
Sub OsszesPont()
Dim lap As Integer, ter As Range, CV As Range, pontok As Double
For lap = 2 To Sheets.Count
Set ter = Sheets(lap).Range("A1").CurrentRegion.Offset(1, 0)
pontok = 0
For Each CV In ter
If CV > 0 Then
pontok = pontok + Application.WorksheetFunction.VLookup(CV.Value, Sheets("Adatok").Range("A:B"), 2, 0)
End If
Next
Sheets(lap).Range("BA1") = pontok
Next
End Sub -
#28100
poffsoft
veterán
TrollBalint
#28099
poffsoft
veterán
válasz
TrollBalint
#28099
üzenetére
és azt hogyan adod meg, hogy a képletekben minden R#C# típusú substringben az R-t S-re, a C-t O-ra cserélje?
a következő gond a "," vs ";" lesz. -
#26787
Fferi50
Topikgazda
TrollBalint
#26783
Fferi50
Topikgazda
válasz
TrollBalint
#26783
üzenetére
Szia!
Mivel semmi sem tökéletes, mért pont a MS Excel lenne az....
Nekem 2010-es excelem van, az nem csinálja ezt a mókát (vagy legalábbis nem vettem észre), helyette vannak más egyéb "ötletei".Egyébként elvileg a táblázat egy-egy oszlopának a cellái egyforma formázásúak lennének (persze oszloponként lehet más-más a formázás) - ha bekapcsoltad ezt az opciót - hiszen a bővítő sor örökli az előző sor formázását. Az oszlopra is ez az elmélet, az utolsó oszlop után ha írsz, az új oszlop az előtte levő formátumát "örökli".
Üdv.
-
#26782
Fferi50
Topikgazda
TrollBalint
#26781
Fferi50
Topikgazda
válasz
TrollBalint
#26781
üzenetére
Szia!
Akkor ennek pedig működnie kellene. Hogyan bővíted a táblázatot? Makróval vagy kézi bevitellel.
Próbáld meg, hogy a táblázat oszlopait leformázod szándékaid szerint. Utána ha "kézzel" írsz a következő sorba, fel kell vennie az előző sor formátumát.
Viszont, ha makróval bővítesz valahonnan máshonnan másolva az adatokat, akkor csak az értéket szabad bemásolni a PasteSpecial metódussal, vagy értékadással átvenni az adatokat.
Ebben az esetben megmarad a formázás, ha sima copy - paste, vagy copy destination:=cella formában másolod be, akkor az sajnos "felülírja" a táblázat formázását.Üdv.
-
#26780
Fferi50
Topikgazda
TrollBalint
#26779
Fferi50
Topikgazda
válasz
TrollBalint
#26779
üzenetére
Szia!
Ez a beállítás nem munkafüzet, hanem alkalmazás(excel) szintű a help szerint. Amelyik nem ilyen, ott kiválaszthatod, melyik munkafüzetre vagy munkalapra vonatkozzon.
Inkább azt érzem problémának, hogy eredeti kérdésedben ez volt "excelben a named table-kben az egyes oszlopok alapértelmezett formátumát hol tudom beállítani". Nos a kérdés az, hogy valójában miről van szó. Adattartományról (azaz táblá(zat)ról), vagy névvel ellátott és adatokat tartalmazó tartományról.
[Egy topik a különbségekről itt található]
Úgy gondolom, a beállítás a tényleges táblázatokra vonatkozik (aminek természetesen szintén lehet külön neve), az excelben névvel ellátott tartományokra nem - talán még akkor sem, ha dinamikusan változik a tartomány offset függvény megadásával).
A megoldás szerintem a tartomány táblázattá alakítása - ami már van a 2007-es verzióban is.Üdv.
-
#26751
Fferi50
Topikgazda
TrollBalint
#26750
Fferi50
Topikgazda
válasz
TrollBalint
#26750
üzenetére
Szia!
Ott is a beállítások között kell keresgélned, csak valószínűleg lista van az adattartomány helyén.
(Nekem nem volt 2007-es excelem, de valaki más hátha besegít.)Üdv.
-
#26748
Fferi50
Topikgazda
TrollBalint
#26747
Fferi50
Topikgazda
válasz
TrollBalint
#26747
üzenetére
Szia!
Excel 2010-ben Fájl - Beállítások - Speciális - Adattartomány végén a formázás és a képletek folytatása bejelölöd.
Üdv.
-
#25964
azopi74
addikt
TrollBalint
#25957
azopi74
addikt
válasz
TrollBalint
#25957
üzenetére
Woww, egy kicsit elkapkodtam.
Nem az a gond, hogy nem egy napon van a képlet szerint (nem is azt jelenti pontosan a WithinOneDay változóm (tudom megtévesztő nevet adtam neki) hogy egy egy napon van, hanem azt, hogy egy napi munkaidőblokkban. (vagyis egy napon, reggel 9 és du 5 között, munkaidőben)
A problémát más okozza, mégpedig a FullWorkingTime változó. Ugyanis egy nap nem 24 munkaóra van, hanem csak 8
Ezért a képlet helyesen:=COUNTIFS(DayType[StartWorkingTime],">="&[StartTime],DayType[EndWorkingTime],"<="&[@EndTime],DayType[Type],"Workday")
helyett:
=COUNTIFS(DayType[StartWorkingTime],">="&[StartTime],DayType[EndWorkingTime],"<="&[@EndTime],DayType[Type],"Workday")/3
Köszi az észrevételt, javítottam. (link változatlan)
-
#25942
azopi74
addikt
TrollBalint
#25940
azopi74
addikt
válasz
TrollBalint
#25940
üzenetére
Fel tudsz tölteni egy olyan példát, ahol nem terjeszti ki? Ki kéne neki terjesztenie automatikusan . Igazság szerint el sem tudom képzelni, hogy mi lehet a gond, még találkoztam ilyennel. CSak különböző trükközésekkel lehet megoldani, ha azt akarod, hogy ne terjessze ki.
Nem csináltál véletlenül az aljára egy összesítő sort? Mert akkor az alatt már nem terjeszti tovább. -
#25941
azopi74
addikt
TrollBalint
#25939
azopi74
addikt
válasz
TrollBalint
#25939
üzenetére
"Közben felvetődött egy kérdés benne, mi a különbség, ha úgy hivatkozok egy cellára, hogy : Table1[[#ThisRow];[Column6]] vagy pedig simán [Column6]?"
Szia, itt viszonylag jól el vannak magyarázva a strukturált hivatkozások:
Egyébként [#This Row] ugyanaz, mint az @ .
"aztán majd csak kitalálnak valamit, hogy mit változtassak meg "
Ja, így szokott ez lenni
-
#25940
TrollBalint
addikt
TrollBalint
#25939
TrollBalint
addikt
válasz
TrollBalint
#25939
üzenetére
Hogy tudom azt megcsinálni, hogy mikor csinálok egy ilyen Named Table-t, akkor , ha új sort írok alá, automatikusan terjessze ki a táblázatot is rá? Mint ahogy nálad is működik. Amit én csináltam, ott nem terjeszti ki.
-
#25901
azopi74
addikt
TrollBalint
#25874
azopi74
addikt
válasz
TrollBalint
#25874
üzenetére
Hali, na megcsináltam
Algoritmusom a következő:
1: Jelöljük ki állandóba a normál munkaidőkezdetet és munkaidővéget (B1 és B2 cella) Jelen esetben 9:00 és 17:00, MunkaidőKezdet és MunkaidőZárás
2: HAtározzuk meg minden napra a normál munkaidőkezdetet és normál munkaidővéget. Ehhez kibővítettem a munkanapos táblát StartWorkingTime és EndWokringTime mezőkkel.
=[@Day]+MunkaidőKezdet
=[@Day]+MunkaidőZárás3: Határozzuk meg a normál időkezdeteket (StartDayNormalStart és EndDayNormalStart), képleteik:
=DATE(YEAR([@StartTime]),MONTH([@StartTime]),DAY([@StartTime]))+MunkaidőKezdet
és
=DATE(YEAR([@EndTime]),MONTH([@EndTime]),DAY([@EndTime]))+MunkaidőKezdet
4: Számoljuk össze a teljes munkanapok számát (FullWorkingTime) StartDay és EndDay között. :
=COUNTIFS(DayType[StartWorkingTime],">="&[StartTime],DayType[EndWorkingTime],"<="&[@EndTime],DayType[Type],"Workday")
Vagyis azoknak a munkanapoknak a száma, amik a StartTime (kezdőidő) után kezdődtek, a záróidő (EndTime) előtt záródtak
5. Számoljuk ki a töredék napokat. Ehhez döntsük el, hogy a kezdőidő vagy a záróidő munkaidőbe esek-e? (csak ekkor kell vele fognalkozni):
IfStartInWorkingTime:
=COUNTIFS(DayType[StartWorkingTime],"<"&[@StartTime],DayType[EndWorkingTime],">"&[@StartTime],DayType[Type],"Workday")
Ennek értéke 1 vagy 0 lehet, (TRUE vagy FALSE), ha 1, akkor a kezdőidő munkaidőőben van ,különben nem,
IfEndInWorkingTime:
Hasonló logikával:
=COUNTIFS(DayType[EndWorkingTime],">"&[@EndTime],DayType[StartWorkingTime],"<"&[@EndTime],DayType[Type],"Workday")
Ezután számoljuk ki a töredéknapokat:
Első nap töredéknapja (PartDayFirstDay):
=IF([@IfStartInWorkingTime],[@StartTime]-[@StartDayNormalStart],0)
Utolsó nap töredéknapja (PartDayLastDay):
=IF([@IfEndInWorkingTime],[@EndTime]-[@EndDayNormalStart],0)
6. Adjuk össze a teljes és töredéknapokat, hogy megkapjuk az összes munkaidőt:
TotalWorkingTime:
=[@FullWorkingTime]+[@PartDayFirstDay]+[@PartDayLastDay]
A táblát itt találod.
Ha valami nem OK, írj, javítom a képletet (letesztelni már nem volt időm
-
#25889
Fferi50
Topikgazda
TrollBalint
#25865
Fferi50
Topikgazda
válasz
TrollBalint
#25865
üzenetére
Szia!
"Múltkorában azt mondtad, hogy egy felhasználói függvény nem módosíthatja a cella tartalmát" Ez így nem pontos - hiszen ahová beírod a képletet, annak a cellának a tartalmát módosítja (hiszen ezért írod be). A pontos fogalmazás az, hogy másik cella tartalmát nem módosíthatja.
"Amit te írtál makró az mégis tudja" Nem tudom, melyik makróra gondoltál (a munkanaposra valószínűleg).
Két különböző dolog van a VBA -ban: A függvény (function) és az eljárás (sub). A függvény meghívható a munkalapról is (ha betartjuk az ide vonatkozó szabályokat, mint pl. a fenti is), az eljárás viszont nem.Az as Date a végén azt jelenti, hogy a függvény visszatérési értéke dátum típusú lesz.
Az eljárások képesek módosítani más cellák - adott esetben másik munkafüzetben levő cellák - adatait és jellemzőit is.
Ennél persze "kicsit" bonyolultabbak a szabályok, az egyik lényeges viszont ez.
Ha másik állományból kell importálni adatokat, ahhoz is lehet akár függvény, akár eljárás a makró típusa (a nézet menü makró listájában csak az eljárások (sub) láthatók, azok közül is csak olyanok, melyek paraméter nélkül hívhatók).
Ha a problémát vázolod, akkor biztosan akad itt segítő kéz - több is akár.
Üdv.
-
#25885
azopi74
addikt
TrollBalint
#25874
azopi74
addikt
válasz
TrollBalint
#25874
üzenetére
Hmmm, ez igényel némi időt, nem egy két perc, akár VBA-val, akár képletekkel akarod megoldani (gyakorlatilag ugyanazt a logikát kell végigkövetned)
De a kivitelezés itt már talán VBA-val némileg egyszerűbb, (kevesebbet kell agyalni) de este otthonról, ha lesz kedvem, megcsinálom szimplán képlettel
-
#25853
Fferi50
Topikgazda
TrollBalint
#25849
Fferi50
Topikgazda
válasz
TrollBalint
#25849
üzenetére
Szia!
Az idő "eltüntetésére" használhatod a Dátum(év(kiindulásicella);hónap(kiindulásicells);nap(kiindulásicella))képletet. Természetesen a saját jelöléseid figyelembe vételével.
Üdv.
-
#25852
azopi74
addikt
TrollBalint
#25849
azopi74
addikt
válasz
TrollBalint
#25849
üzenetére
Ahhoz miz szólsz, ha csinálsz Name Managerben egy KezdoDatumKorr-t, amiben ignorálod az eredeti cella időadatát, valahogy így:
=DATE(YEAR(mnapkalk[@KezdoDatum]),MONTH(mnapkalk[@KezdoDatum]),DAY(mnapkalk[@KezdoDatum]))
- (de talán van egyszerűbb módszer is erre)
és utána erre hivatkozol a KezdoDatum helyett a képletekben (mnapokkorr mezőben javítva)?
Valahogy így gondoltam:
-
#25793
azopi74
addikt
TrollBalint
#25785
azopi74
addikt
válasz
TrollBalint
#25785
üzenetére
- Named Ranges::
Az =mnapok -ért bocs, azt felesleges volt betenni name-be, hiszen nem is használtam tovább a képletet, és még a relatív hivatkozások miatt sincs értelme, hoszen úgyis táblát használtam nem range-et
(mondom, hogy fáradt voltam, meg talán kicsit sok volt a sör is előtte haverokkal, valamit próbáltam, aztán véletlenül úgy marad
De nyugodtan ki lehet szedni, ha visszamásolod az eredeti képletet : =COUNTIFS(DayType[Type],"Workday",DayType[Day],">="&[@KezdoDatum],DayType[Day],"<"&[@ZaroDatum])
és nyomsz rá egy Entert, akkor is ugyanúgy működni fog, mert tábla, ezért semmi funkciója nincs jelen esetben a named range használatának
Azt hiszem, azért csinálhattam, mert a második példádat, (az x-edik munkanap visszakeresését) eredetileg tömb függvénnyel és ennek az mnapok dinamikius named range-el akartam visszakeresni, és azért vezettem be a nevet, hogy ne legyen nagyon szofisztikált a képlet, és olvasható maradjon.Egyébként fő funkciói a named range-eknek:
- állandók, változók használata
- ha valamiért nem használhatsz táblákat, akkor is olvashatóvá és dinamikussá lehet tenni képleteket (dynamic named ranges) Utána bárhol hivatkozhatsz rájuk. Úgy gondolj rájuk, mint egy univerzális jolly joker objektumra, ami lehet akár literál, állandó, változó, tömb, vagy újrafelhasználható, meghívható metódus a programnyelvekben ;>Persze annál azért némileg korlátozottabb lehetőségekkel. Bár azért named ranges + iterációk használatával is bámulatos dolgokat lehet művelni mindenféle makró nélkül, Pl
Ez is jó:
Persze ezek extrém példák, és ez már a másik véglet, de jól mutatják a lehetőségeket.
Néha azért sajnos muszáj VBA-hoz nyúlni. Tudom ezért megköveznek, mert látom elég sok a VBA fan, aki még az összeg függvényt is legszívesebben maga makrózza le, de én annyira nem csipázom a dolgot
Főleg, hogy sok cég IT policy-ja nem is engedi a makrók használatát. CSak esetleg szigorú validációt követően. Valamennyire érthető módon, mert hát azért elég csúnya dolgokat is lehet művelni vele, 
De az igazság az, hogy ma már szinte mindent meg lehet oldani egyéb, sztenderd excel eszközökkel, , csak olyanokat nem, amire amúgy a VBA sem túl optimális megoldás. Ez persze nem mindig volt így, sokáig elengedhetetlen volt a VBA használata komolyabb feladatokhoz, de sokat okosodik az excel verzóról verzióra, ma már az esetek 99%-ban azokat a dolgokat, amikre a vállalatoknál ezer éves makrókat használnak, sokkal egyszerűbben, biztonságosabban, gyorsabban és hatákonyabban meg lehet más módon is oldani. Nem állítom, hogy mindent, de a használatban lévő VBA kódok 99%-át ki lehetne simán kukázni
Taávlati cél úgyis az, hogy eltávolítják a VBA-t az Office-ból, de azért addig még sok víz lefolyik a Dunán...- & jellel mi a gond? Az olyasmi, mint a CONCATENATE. És hol hívtam meg vele, és mit?
Ja bocs, látom már, mire gondolsz
Az nem meghívás, csak hivatkoztam a mnapkalk tábla (ami egyébként önmaga, tehát, normál esetben nem is kell, főleg, mivel tábláról van szó) különböző mezőire.
Ha kimásolod a képletet, a mnapkalk mnapok mezőjébe és rányomsz egy entert, láthatod, hogy rögtön el is tűnik a saját tábla hivatkozást automatikusan és ebből:=COUNTIFS(DayType[Type],"Workday",DayType[Day],">="&mnapkalk[@KezdoDatum],DayType[Day],"<"&mnapkalk[@ZaroDatum])
ez lesz
=COUNTIFS(DayType[Type],"Workday",DayType[Day],">="&[@KezdoDatum],DayType[Day],"<"&[@ZaroDatum])
Az & jelnek meg ehhez semmi köze, az csak a feltétel megadáshoz kell.
">=" és utána & jelt követően mehet tovább
Te hogy szoktál ilyen feltételeket megadni pl SUMIFS-ben vagy COUNTIFS-ben, meg hasonló függvényekben ?
-
#25788
Fferi50
Topikgazda
TrollBalint
#25785
Fferi50
Topikgazda
válasz
TrollBalint
#25785
üzenetére
Szia!
"ettől lehalt az egész excel"
Igazad van, szégyellem magam miatta, mert ezzel belekergettem egy végtelen ciklusba a függvényt.
Ezt most kipróbáltam és működik. Magyarázatokat is fűztem hozzá:
Function munkanapos(ByVal mettol As Date, Optional hanynap As Integer) As Date
Dim unnepek As Range, munkanap As Date, napi As Range, napok As Date, napjai As Integer
Set unnepek = Sheet1.Range("G2:G19") 'itt vannak az ünnepnapok és a dolgozós szombatok
napjai = 0 'nulláról indul a munkanap számláló
munkanap = mettol
napok = mettol 'innen indulunk
Do While napjai <= hanynap 'addig kell a ciklust csinálni, amíg a munkanapok száma eléri a kívánt értéket
Set napi = unnepek.Find(what:=napok, LookIn:=xlValues, lookat:=xlWhole) ' benne van-e az ünnep listában a dátumunk
If Not napi Is Nothing Then ' ha benne van a listában a dátumunk
If Application.Weekday(napok, vbMonday) = 6 Then ' és az szombat, akkor ez munkanap
munkanap = napok ' tehát visszatérési érték lehet
napjai = napjai + 1 'növeljük a munkanap számlálót
End If
Else 'ha nincs benne a listában a dátum
If Application.Weekday(napok, vbMonday) < 6 Then ' és nem szombati nap
munkanap = napok 'akkor lehet visszatérési érték
napjai = napjai + 1 ' növeljük a munkanap számlálót
End If
End If
napok = napok + 1 ' növeljük a dátumunkat egy nappal
Loop 'és folytatjuk a ciklust
munkanapos = munkanap ' ha vége a ciklusnak, akkor a munkanap változó aktuális értékét kell visszaadni
End FunctionNem probléma a kérdés - akkor sem, ha több van. Viszont jónéhányra a VBA help-ben megtalálod a választ.
Pl. Range.Find paramétereinek a magyarázata (illetve egyáltalán a nevesített paraméterek használata).
Szerintem nem úszod meg, hogy alaposan átnézd legalább a VBA help átlalános részét.
Az unnepekben mint írtam, benne vannak a nem munkaszüneti nap szombatok is.
Ezért kell mindkét ágat használni.
If not = magyarul "ha nem" (not ugye a logikai tagadás). Tehát az adott helyen, ha nem semmi(üres) a napi változó értéke - azaz talált a keresés egy olyan napot a listában. Keresésnél ezt a formulát érdemes alkalmazni, mert így nem fogsz hibát kapni.
Ha egyből a keresés eredményével akarnál dolgozni - pl. az abban levő dátumot szeretnéd megtudni, akkor hibaüzenetet kapsz. Van még egy csomó ilyen finomság, amit kb.20 év alatt sikerült összeszednem és még mindig tanulok új dolgokat.
A Weekday a nap számát adja vissza valóban, de hogy melyik napot tekinti a hét első napjának, az a második paraméterétől (ami itt vbMonday) függ.
A növelés logikáját szerintem megérted a makróból.Üdv.
-
#25784
Fferi50
Topikgazda
TrollBalint
#25778
Fferi50
Topikgazda
válasz
TrollBalint
#25778
üzenetére
Szia!
A javított függvényemben is sikerült egy hibát benne hagyni, ezért nem úgy számol, ahogyan kellene.
A napjai = napjai+1 sort oda kell átirni, ahol a munkanap=napok sorok vannak, az után a sorok után, mivel csak akkor szabad növelni, ha munkanapot talált.
Tehát az a rész így nézne ki:
Do While napjai <= hanynap
napok = mettol + napjai
Set napi = unnepek.Find(what:=napok, LookIn:=xlValues, lookat:=xlWhole)
If Not napi Is Nothing Then
If Application.Weekday(napok, vbMonday) = 6 Then
munkanap = napok
napjai=napjai+1
End If
Else
If Application.Weekday(napok, vbMonday) < 6 Then
munkanap = napok
napjai=napjai+1
End If
End If
LoopTermészetesen az oszlopot is elnéztem, valóban nem a H, hanem az I oszlop adatait kell átírni a G alá. Mégpedig azért, mert a függvény logikája a következő:
Az ünnepek között felsorolt hétköznap (H-P) azt jelenti, hogy az a nap nem munkanap.
Az ünnepek között felsorolt szombat viszont azt jelenti, hogy az a szombat munkanap.Elnézést kérek a hibák miatt.
Üdv.
-
#25783
azopi74
addikt
TrollBalint
#25781
azopi74
addikt
válasz
TrollBalint
#25781
üzenetére
"Annyi Range van nálad, hogy egyszerre nehéz megjegyezni."
Nálam nincs Range, csak Tábla
De ha neked jobban tetszik a range-es módszer, mert azt szoktad meg, akkor simán alakítsd vissza
Range-ekké az összes táblát (jobbegér a táblában, -> Table, -> Convert to range) és akkor "hagyományos " nézetben látod a képleteket még valami, most vettem észre, hogy valamiért (már nem tudom mit akartam, fáradt voltam biztos )) beraktam Name-be a mnapkalk táblában a mnapok képletét, de az gondolom nem zavar meg, vissza lehet rakni a képletet, semmi szerepe, hogy Name-ként legyen. -
#25780
azopi74
addikt
TrollBalint
#25778
azopi74
addikt
válasz
TrollBalint
#25778
üzenetére
Milyen sok hivatkozás és lista?
Én a te általad készített hivatkozásokat és listákat használtam, és a te képletedet használtam a nap típus meghatározására is Csak táblává alakítottam a range-eidet, mert utálok cellahivatkozásokkal szórakozni, így szerintem sokkal olvashatóbb a képlet, és átláthatóbb az egész, mintha Range hivatkozásokkal nyomnánk be (no meg a munka is sokkal gyorsabb vele, mint range-ekkel, és robusztusabb is)
Nem olvashatóbb az, hogy
=IF(COUNTIF(Munkanapok[Munkanapok],[@Day])>0,"Workday",IF(OR(WEEKDAY([@Day],2)>5,COUNTIF(Mszunet[Munkaszüneti napok],[@Day])),"Holiday","Workday"))
, mint az, hogy
=IF(COUNTIF(Settings!$K$2:$K$4,Settings!$E2)>0,"Workday",IF(OR(WEEKDAY(Settings!$E2,2)>5,COUNTIF(Settings!$I$2:$I$16,Settings!$E2)),"Holiday","Workday"))
?
(ez a te függvényed)
Ilyen adatbázis szintű feladatok, strukturált táblázatok esetén mindig egészségesebb táblákkal dolgozni minden szempontból. Megéri, egy mozdulat az egész (Insert - Table) Most részletezném minden előnyét, mert estig azokat sorolnám, kezdd el használni, sose fogz többé range-ekkel és cellákkal bajlódni
(csak amikor nagyon muszáj,, mert pl olyan szemét formában kapod az adatokat) .Táblák egyébként Excel 2002 óta vannak támogatva, valamiért mégis kevesen használják számomra érthetetlen módon.
A függvények, amiket használtam, alap excel függvények, mindegyiknek mennie kell 2007-en.
Countifs 2003 óta megy (előtte tömbfüggvényekkel kellett bajlódni ilyesmikhez)
Index, match és vlookup pedig emberemlékezet óta van az excel-ben, azok nélkül elég nehéz lenne az élet Bár vlookup-ut ki lehetne dobni . 97 óta biztosan benne vannek, előtte nem tudom, mert nem nagyon használtam. Más függvényt nem használtam.A képletek:
Eltelt munkanapok:
=COUNTIFS([Type],"Workday",[Day],"<="&[@Day])
Szerintem ezt nem kell magyarázni, (bár ezt lehetett volna sime countif-fel is, de az nem áll az ujjaimra
az mnapok - hoz használt képlet:
=COUNTIFS(DayType[Type],"Workday",DayType[Day],">="&[@KezdoDatum],DayType[Day],"<"&[@ZaroDatum])
Csak annyit tesz, hogy összeszámolja a kezdő és végső dátum közötti munkanapokat (ahol teljesül mindhárom feltétel)
Zarodatum-hoz használt kalkuláció egy kicsit összetettebb, de nem túl bonyolult:
=INDEX(DayType[Day],MATCH(VLOOKUP([KezdoDatum],DayType,3,0)+[mnapok],DayType[WorkDayCum],0),1)
VLOOKUP([KezdoDatum],DayType,3,0)+[mnapok] [I]-> Spoiler[/I]
Kikeresi a napok táblájában az adott napig eltelt munkapok számát, hozzáadja a hozzáadandó munkanap számot. Bár nem szép megoldás a vlookup, kerülendő a használata, egyrészt korlátoltsága, másrészt sebezhetősége miatt, így szinte mindig INDEX + MATCH használata javasolt helyette. De most ezt választottam az egyszerűség kedvéért.
=INDEX(DayType[Day],MATCH([I][SP]VLOOKUP([KezdoDatum],DayType,3,0)+[mnapok][/SP][/I],DayType[WorkDayCum],0),1)
Visszakeresi ugyanitt azt az első napot, ami az előző függvény által visszadott érték, vagyis ameddig az eredetileg eltelt + hozzáadandó munkanapok teltek el. Itt már ugye, ha akartam se tudtam volna VLOOKUP-ot használni a korlátai miatt, csak úgy, ha további segédoszlopot használok. Egyébként többfüggvénnyel mindenféle segédoszlop nélkül is meg lehetett volna oldani a dolgot
-
#25775
azopi74
addikt
TrollBalint
#25744
azopi74
addikt
válasz
TrollBalint
#25744
üzenetére
"Az első feladat, amivel most szenvedek, az annyi lenne, hogy egy dátumtól számolva a paraméterben megadott számnyi munkanapot leszámol és azt a dátumot adja vissza. Viszont ha ezen túl vagyok lesz olyan is, hogy két dátum között kell kiszámolni a munkanapok számát."
De minek kell ehhez makró?
Ha úgyis már megvannak a típusok naptári naponként nézve egy táblában, akkor, (ha ezt a táblát pl elnevezed DayType-nak, a napot Day-nek, a típust Type-nak, és csinálsz egy plusz mezőt (WorkDayCum) az adott napig eltelt munkanapok számának meghatározására így : =COUNTIFS([Type],"Workday",[Day],"<="&[@Day])Ekkor a két dátum (Kezdodatum, ZaroDatum) közti munkanapok száma:
=COUNTIFS(DayType[Type],"Workday",DayType[Day],">="&mnapkalk[@KezdoDatum],DayType[Day],"<"&mnapkalk[@ZaroDatum])
relációs jeleket tetszőlegesen állítsd, attól függően, hogy az első és utolsó napokat bele akarok számítani
Az adott napra (KezdoDatum) rászámolt munkapok (mnapok) pedig:
=INDEX(DayType[Day],MATCH(VLOOKUP([KezdoDatum],DayType,3,0)+[mnapok],DayType[WorkDayCum],0),1)
Tömbfüggvénnyel persze segédoszlop nélkül is megoldható, mint minden, csak az már egy kicsit szofisztikáltabb megoldás lenne
-
#25765
Fferi50
Topikgazda
TrollBalint
#25744
Fferi50
Topikgazda
válasz
TrollBalint
#25744
üzenetére
Szia!
A #25749 hozzászólásomban levő függvény nem teljesen jó sajnos, mert nem a munkanapokat számolja, hanem csak a napokat. Javítottam, ezt próbáld meg légy szíves:
Function munkanapos(ByVal mettol As Date, Optional hanynap As Integer) As Date
Dim unnepek As Range, munkanap As Date, napi As Range, napok As Date, napjai As Integer
Set unnepek = Sheet1.Range("G2:G19")
napjai = 0
munkanap = mettol
Do While napjai <= hanynap
napok = mettol + napjai
Set napi = unnepek.Find(what:=napok, LookIn:=xlValues, lookat:=xlWhole)
If Not napi Is Nothing Then
If Application.Weekday(napok, vbMonday) = 6 Then
munkanap = napok
End If
Else
If Application.Weekday(napok, vbMonday) < 6 Then
munkanap = napok
End If
End If
napjai = napjai + 1
Loop
munkanapos = munkanap
End FunctionÜdv.
-
#25749
Fferi50
Topikgazda
TrollBalint
#25744
Fferi50
Topikgazda
válasz
TrollBalint
#25744
üzenetére
Szia!
A következőt javaslom:
A Settings munkalap G oszlopa alá írd még be a H oszlopban levő adatokat ís.
Ezután írd be a következő függvényt a module1-be:Function munkanapos(ByVal mettol As Date, Optional hanynap As Integer) As Date
Dim unnepek As Range, munkanap As Date, napi As Range, napok As Date
Set unnepek = Sheet1.Range("G2:G19")
munkanap = mettol
If hanynap = 0 Then hanynap = 0
For napok = mettol To mettol + hanynap
Set napi = unnepek.Find(what:=napok, LookIn:=xlValues, lookat:=xlWhole)
If Not napi Is Nothing Then
If Application.Weekday(napok, vbMonday) = 6 Then
munkanap = napok
End If
Else
If Application.Weekday(napok, vbMonday) < 6 Then
munkanap = napok
End If
End If
Next
munkanapos = munkanap
End FunctionA függvény megmondja, hogy a kezdő dátumtól (meddig) a megadott napokkal (hanynap) később melyik lesz a munkanap.
Ez működik cellában meghívva is (csak a cellaformátumot kell dátumra állítani).Üdv.
(A makróddal kapcsolatban szerintem majd írok privit, ha neked is megfelel.)
-
#25741
Fferi50
Topikgazda
TrollBalint
#25740
Fferi50
Topikgazda
válasz
TrollBalint
#25740
üzenetére
Akkor marad a felhasználói függvény...
Próbáld ki lsz.Üdv.
-
#25739
Fferi50
Topikgazda
TrollBalint
#25738
Fferi50
Topikgazda
válasz
TrollBalint
#25738
üzenetére
Szia!
Jól sejted, az lenne a neve. Szerintem nem kellene hozzá semmi kiegészítő, viszont
verzió függő, a 2010-es excelben már benne van. Nálad milyen excel verzió fut?Üdv.
-
#25735
Fferi50
Topikgazda
TrollBalint
#25727
Fferi50
Topikgazda
válasz
TrollBalint
#25727
üzenetére
Szia!
Felhasználói függvény nem módosíthatja más cella tartalmát, ezért nem működik ha cellába írod be (kivétel a tömbfüggvény.) De a makród úgy is működne, ha egyszerűen csak a változókkal számolnál és nem írnád be cellába a változó értéket.
Amúgy várom a mintát.
Üdv.
-
#25723
Fferi50
Topikgazda
TrollBalint
#25713
Fferi50
Topikgazda
válasz
TrollBalint
#25713
üzenetére
Szia!
A munkanapok megszámolására két dolgot javaslok:
I. Az ÖSSZ.MUNKANAP.INTL függvény a következőképpen:
=ÖSSZ.MUNKANAP.INTL(MA();MA()+5;11;J1:J49)
A függvény 3. paramétere határozza meg, hogy milyen napokat tekint hétvégének. A 11 azt jelenti, hogy csak a vasárnap munkaszüneti nap.
A 4. paraméterben kell megadni az ünnepnapokat. Mivel az összes szombatot munkanapnak tekinti a 3. paraméter alapján, erre a területre kell bevinni a többi szombatot.
Kicsit sok a beviendő adat, de sorozatként megoldható könnyen. Cserébe pontosan számol és excel beépített függvény.
II. Saját függvény használata:
Function munkanapszam(ByVal mettol As Date, Optional meddig As Date) As Integer
Dim unnepek As Range, munkanap As Integer, napi As Range, napok As Date
Set unnepek = Range("J1:J7")
munkanap = 0
If meddig = 0 Then meddig = mettol
For napok = mettol To meddig
Set napi = unnepek.Find(what:=napok, LookIn:=xlValues, lookat:=xlWhole)
If Not napi Is Nothing Then
If Application.Weekday(napok, vbMonday) = 6 Then
munkanap = munkanap + 1
End If
Else
If Application.Weekday(napok, vbMonday) < 6 Then
munkanap = munkanap + 1
End If
End If
Next
munkanapszam = munkanap
End FunctionItt az a megoldás lényege, hogy az ünnepek közé felveszed a "dolgozós" szombatokat is. A függvény megnézi, hogy az ott szereplő dátum szombat-e, ha igen, akkor munkanapnak számolja. Ha nem szerepel a listában, akkor munkanap, ha H-P.
Mindkét esetben az ünnep lista egy oszlop legyen.
Üdv.
-
#25718
Delila_1
veterán
TrollBalint
#25713
Delila_1
veterán
válasz
TrollBalint
#25713
üzenetére
Itt azt mondja Horváth Imre, hogy a munkanapra cserélt szombattal nem nagyon lehet számolni.
Összesen 2 ilyen lesz az évben (aug.8. és dec.12.), ennek a 2-nek a közelében figyelj, és "gyalog" írd be a dátum+Xnap eredményét. -
#25710
Delila_1
veterán
TrollBalint
#25708
Delila_1
veterán
válasz
TrollBalint
#25708
üzenetére
Az Office telepítésénél lehet a gond.
-
#25685
Delila_1
veterán
TrollBalint
#25684
Delila_1
veterán
válasz
TrollBalint
#25684
üzenetére
Engedélyezned kell a beállításoknál a két Analysis kezdetű bővítményt az Excelben.
-
#25680
Delila_1
veterán
TrollBalint
#25678
Delila_1
veterán
válasz
TrollBalint
#25678
üzenetére
Megnyitod a füzetet. Alt+F11-gyel átváltasz a VBA editorba (VBE). Bal oldalon kijelölöd a füzetedet. Insert menü, Module. Jobb oldalon kaptál egy nagy üres területet, oda másold be a makrót. Lépj vissza a füzetbe, mentés makróbarátként.
-
#25677
Delila_1
veterán
TrollBalint
#25674
Delila_1
veterán
válasz
TrollBalint
#25674
üzenetére
Ha xls a kiterjesztés, akkor 2003-as, vagy régebbi verziójú az Exceled. Ezeknél a füzet sima mentése menti a benne lévő makró(ka)t is.
Ahhoz, hogy lássam, mi lehet a hiba, a füzetet is látnom kellene.
-
#25648
Delila_1
veterán
TrollBalint
#25647
Delila_1
veterán
válasz
TrollBalint
#25647
üzenetére
If Weekday(Application.WorkDay(Cella, Napok_szama, Range("G2:G14")), 2) = 1 Then
helyett
If Weekday(Application.WorkDay(Cella, Napok_szama, Sheets("MásikLapNeve").Range("G2:G14")), 2) = 1 Then
A két "maradék" szombat 08.08, és 12.12.
-
#25646
Delila_1
veterán
TrollBalint
#25645
Delila_1
veterán
válasz
TrollBalint
#25645
üzenetére
Próbálgatom különböző plusz napokkal, és sajnos nem mindig 101-es az eredmény.
Az idén összesen 3 szombat lesz (volt) munkanap. Annak a maradék 2-nek a környékén figyelj!
-
#25642
Delila_1
veterán
TrollBalint
#25635
Delila_1
veterán
válasz
TrollBalint
#25635
üzenetére
Összehoztam (nagy nehezen) egy felhasználói funkciót, a neve XNap.
Első paramétere a cella, ahol a dátum van, második a napok száma, amennyivel későbbi dátum kell az eredményben.Ha a kezdő dátumod az A2-ben van, és 3 munkanappal későbbi dátumot szeretnél kapni, a függvény
=XNap(A2;3)A G2:G14 tartományban vannak az idei ünnepnapok, az I oszlopban pedig a munkanapokká lefokozott szombatok.
Function XNap(Cella, Napok_szama As Integer)
Dim datum As Date, nap As Integer, x As Integer
datum = Cella
For x = 1 To Napok_szama
If Application.CountIf(Columns(9), Cella + x) > 0 Then
nap = nap + 1
Exit For
End If
Next
If nap = 1 Then
If Weekday(Application.WorkDay(Cella, Napok_szama, Range("G2:G14")), 2) = 1 Then
XNap = Application.WorkDay(Cella, Napok_szama, Range("G2:G14")) - 2
Else
XNap = Application.WorkDay(Cella, Napok_szama, Range("G2:G14")) - 1
End If
Else
XNap = Application.WorkDay(Cella, Napok_szama, Range("G2:G14"))
End If
End Function -
#25630
Delila_1
veterán
TrollBalint
#25628
Delila_1
veterán
válasz
TrollBalint
#25628
üzenetére
A csatolt füzetben a H és J oszlop tartalmát elneveztem Ünnepek, ill. Munkanapok névvel.
Az A oszlop feltételes formázását nézd meg. Fontos a két feltétel sorrendje.
-
#5303
Cuci3
tag
TrollBalint
#5302
Cuci3
tag
válasz
TrollBalint
#5302
üzenetére
Ami neked kell, az a szövegből oszlopot: Adatok/Szövegből oszlopok. Onnan a fix szélest kiválasztod, majd két tovább. Ha kell, akkor a harmadik lapon szórakozhatsz a formátummal, majd befejezés.
-
#5204
Delila_1
veterán
TrollBalint
#5193
Delila_1
veterán
válasz
TrollBalint
#5193
üzenetére
Darabszámláló függvények:
Darab(tartomány) a tartományban található számok darabszámát adja.
Darab2(tartomány) a tartományban található nem üres cellák darabszáma.
Darabüres(tartomány) a tartományban található üres cellák darabszáma.
Darabteli(tartomány;kritérium) A tartományban a kritériumnak megfelelő cellák darabszáma. -
#347
ollie
MODERÁTOR
TrollBalint
#346
válasz
TrollBalint
#346
üzenetére
Ha paraméterként IGAZ-at adsz meg, akkor nem kell megadnod minden ponthoz, csak a ponthatárokhoz.
-
#344
lapa
veterán
TrollBalint
#343
lapa
veterán
válasz
TrollBalint
#343
üzenetére
szvsz nem fogja elhinni uccse. próbálj meg rámásolni egy default cellát, majd cellaformázás - szöveg. vagy rakj elé '-t hátha.
-
#336
Gh0sT
addikt
TrollBalint
#332
Gh0sT
addikt
válasz
TrollBalint
#332
üzenetére
Küld, megcsinálom!
A számlát hova postázhatom?
Címem az adataimnál. -
#334
rdi
veterán
TrollBalint
#325
rdi
veterán
válasz
TrollBalint
#325
üzenetére
Igen ez a képlet jó és nálam simán működik.
-
#326
Gh0sT
addikt
TrollBalint
#325
Gh0sT
addikt
válasz
TrollBalint
#325
üzenetére
Elvileg meg kéne kajálja...
Próbáltad már, hogy másik cellába rakod az értéket, amit hozzá szeretnél adni, és másképp képletezed? Pl. 14 a mellette levő cellába és =C4+C5.
Szerk.: Szerintem a C4-es cellád tartalmával lesz a baj. Próbáld meg ezt beírni a C4-be: 2005.05.21.
[Szerkesztve] -
#324
Gh0sT
addikt
TrollBalint
#323
Gh0sT
addikt
válasz
TrollBalint
#323
üzenetére
Dátum értékhez szeretnél hozzáadni számot? Elvileg ez nem lehet probléma... Legalábbis nálam simán megy. Milyen Excel?
És még tömörebbé lehet tenni, ha beveted a name managert a 24*(műszakvég - műszakkezdet) helyettesítésére...
Ezért a képlet helyesen:
Próbáltad már, hogy másik cellába rakod az értéket, amit hozzá szeretnél adni, és másképp képletezed? Pl. 14 a mellette levő cellába és =C4+C5.Új hozzászólás Aktív témák
Hirdetés
Fferi50- BESZÁMÍTÁS! Intel Core i9 14900KF 24 mag 32 szál processzor garanciával hibátlan működéssel
- Bomba ár! Dell Inspiron 7400 - i7-1165G7 I 8GB I 512SSD I 14,5" 2K I HDMI I Cam I W11 I Garancia!
- BESZÁMÍTÁS! Sony PlayStation4 PRO 1TB fekete konzol extra játékokkal garanciával hibátlan működéssel
- Telefon felvásárlás!! Xiaomi Redmi 9, Xiaomi Redmi 9AT, Xiaomi Redmi 10, Xiaomi Redmi 10 2022
- ÁRGARANCIA!Épített KomPhone Ryzen 7 5800X 32/64GB RAM RX 7700 XT 12GB GAMER PC termékbeszámítással
Állásajánlatok
Cég: CAMERA-PRO Hungary Kft
Város: Budapest
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest