Hirdetés
- Gurulunk, WAZE?!
- Luck Dragon: Asszociációs játék. :)
- sziku69: Fűzzük össze a szavakat :)
- ubyegon2: Airfryer XL XXL forrólevegős sütő gyakorlati tanácsok, ötletek, receptek
- Sub-ZeRo: Euro Truck Simulator 2 & American Truck Simulator 1 (esetleg 2 majd, ha lesz) :)
- potyautas: A Magyar Néphadsereg emlékére
- D@reeo: Pi-hole és a Telekom Sagemcom F@st 5670 DNS beállítása
- gban: Ingyen kellene, de tegnapra
- GoodSpeed: A RAM-válság és annak lehetséges hatásai
- GoodSpeed: Márkaváltás sok-sok év után
-
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
-
azopi74
addikt
válasz
enelna
#26042
üzenetére
De nyitvahagyni az adott munkalapot az excelben nem felejted el? Ha se a task scheduler, se szerveroldali megoldása nem jöhet szóba, akkor is inkább egy outlook makrót javasolnék az elküldésre (persze ahhoz is bekapcsolva kell hagyni a gépet). De azt inkább az outlook topicban kérdezd
-
azopi74
addikt
válasz
enelna
#26040
üzenetére
Szia,
Biztos, hogy ezt így akarod megoldani? Csak mert ez azt csinálja, hogy az adott kliensgépen futó excelben épp aktív munkafüzet aktív munkalapját menti el pdf-ként, és küldi el az adott email címekre. Ami hasznos lehet, ha ezt excel makróból csináljuk, de így, ütemezve, nem látom nagyon értelmét.
Ha LAN-on vagy, akkor ezt a taskot érdemesebb inkébb szerver oldalról beütemezni, és akkor nem szükséges, hogy bekapcsolva legyen a kliens. Ha nincs LAN, akkor is érdemesebb a windows task scheduler-ét használni erre a célra. Ha csak egy adott helyen lévő file email-ben történő kiküldéséről van szó, akkor az teljesen jó megoldás. Ha a pdf-be való konvertálásról van szó, azt persze alapból nem tudja, (bár biztos van rá szerveroldali alkalmazás) de akkor meg nem értem az egészet. Dinamikusan frissül az adott excel tábla adott munkalapja (valami ütemezett lekérdezés van mögötte?), amiért így akarnád megoldani? Persze, ha nagyon akarod, megoldható a dolog excel makróból is, csak nem vagyok benne biztos, hogy tényleg ezt szeretnéd
-
azopi74
addikt
válasz
Delila_1
#26027
üzenetére
Naaa, ez azért már egy kicsit tákolásnak tűnik
És továbbra sem bolondbiztos, és legkevésbé sem hordozható megoldás.És akkor még azzal is foglalkoznod kell, hogy meddig legyen érvényes a képlet, persze valahogy így megoldható:
=HA(INDEX(C:C;HOL.VAN(NAGY(D:D;SOR()-1);D:D;0);1)=MAX(C:C);NAGY(D:D;SOR()-1);"")
(Ha pl C oszlopban van az eredeti, és D oszlopban a "korrigált" nyugdíjérték)
De azért nehezen tudna meggyőzni arról bárki, hogy ez akármilyen szempontból egyszerűbb, tisztább és vagy megbízhatóbb megoldás lenne, mint a
SELECT * FROM `Tábla`
WHERE Nyugdíj IN
(SELECT MAX(Nyugdíj) FROM `Tábla`)hagyományos megoldás, ami pontosan, minden körülmény között azt csinálja, amit szeretnénk, és eléggé egyenes logikát követ, és az alkalmi felhasználó is szerintem hamarabb felfogja, mint a másik módszert.
-
azopi74
addikt
válasz
azopi74
#26025
üzenetére
Amúgy nem sértésként mondom, de azt, aki ezt a borzalmat csinálta, elküldeném inkább havat lapátolni, de legalábbis biztosítanám, hogy számítógép közelébe ne kerüljön
De leginkább azt az egyént, akinek egyáltalán megfordult a fejében, hogy egy ilyen taskot excel-ből, VBA-val oldjon/oldasson meg
-
azopi74
addikt
-
azopi74
addikt
válasz
pirit28
#26023
üzenetére
Szia,
Próbáltad úgy, ahogy mondtam? (hogy C:\ helyett valahova a Users\felhasználónév alá helyezed a cuccot) ?
Mert ez nagyon jogosultság problémának tűnik. Alapból nem nagyon engedné egy Win7/Office 2007 kombó, hogy egy makró a a C:\ -ben garázdálkodjon írási jogosultsággal. persze ki lehet küszöbölni, ha nagyon akarnád, de nem tenném a helyedben -
#26009
azopi74
addikt
Csuklósbusz
#26008
azopi74
addikt
válasz
Csuklósbusz
#26008
üzenetére
Bocs, alapvető probléma, hogy én néztem be valamit, mindjárt javítom a képletet
A másik, hogy úgy látom, magyar exceled van, a függvényt is le kéne fordítani magyarra.http://hu.excel-translator.de/
De ha javítottam a képletet, lefordítom magyarra, és úgy is beírom.
A harmadik, hogy úgy látom, szövegformátumúak a celláid D oszloptól
-
#26001
azopi74
addikt
Csuklósbusz
#25999
azopi74
addikt
válasz
Csuklósbusz
#25999
üzenetére
A2-be írd be: ha B-ben vannak az adatok:
=IF(B2<>"",COUNTA($B$1
2)&".","")És húzd le
-
#26000
azopi74
addikt
Csuklósbusz
#25998
azopi74
addikt
válasz
Csuklósbusz
#25998
üzenetére
És az 1000-res és 500-as szándékosan maradt ki?
Ha pl az A oszlopban vannak az összegegek, és az első sorban B-től M-ig a címletek, akkor B2-be ezt írd: =INT(A2/B1)
C2-be pedig ezt: =INT(MOD($A2,B1)/C1) , és utána húzd el jobbra, az utolsó címletig .Aztán húzd le az egészet az utolső összegig
Van sokkal szebb megoldás is, de nem akarlak elijeszteni, mert azt mondanád rá hogy bonylult
(Képletfordításhoz: http://hu.excel-translator.de/)
-
#25997
azopi74
addikt
Csuklósbusz
#25995
azopi74
addikt
válasz
Csuklósbusz
#25995
üzenetére
Az összes lehetőséget kidobja, vagy csak a legegyszerűbbet (legkevesebb darabszámút)?
-
#25996
azopi74
addikt
Csuklósbusz
#25994
azopi74
addikt
válasz
Csuklósbusz
#25994
üzenetére
Nem értem, mi a gond. Ennél egyszerűbben nem nagyon lehet megoldani. Ez is simán excel.
Te mondtad, hogy adatbázisként használod az excelt (amire szerintem amúgy egészségesebb lenne egy adatbáziskezelő, de nem baj)
De ha így használod, akkor abból a lekérdezéket jelenleg MS Query-vel tudod megoldani.
Ha Excel 2010 van legalább, és a PowerQuery bővítvény is fent van, akkor grafikus felületen, SQL nélkül is megtudod ugyanezt csinálni némileg felhasználóbarátabb módon (MS Query-vel is tudsz grafikusan queryt csinálni, de csak nagyon egyszerűeket)"Hogy a név, utca, hsz, összeg, közül a táblázat alulra egy mezőbe újra kiírja a legnagyobb és legkissebb összegűeket... ?"
Egy mezőbe hogy tudsz több sort írni?
Olyat tudnál még csinálni, hogy a lenagyobb összegűt kikeresed (MAX függvény), és VLOOKUP-olod hozzá a többit is, de ez csak sorban az első találatot fogja kihozni, nem az összeset.
-
#25993
azopi74
addikt
Csuklósbusz
#25991
azopi74
addikt
válasz
Csuklósbusz
#25991
üzenetére
A legnagyobb nyugdíjúakat?
MS Query-be tedd be ezt az SQL statementet (Data/From Other Sources/From Microsoft Query/Excel Files) :
SELECT * FROM `C:\Users\Peter\Desktop\Book2.xlsx`.`Sheet1$` `Sheet1$`
WHERE Nyugdíj IN (SELECT
MAX(Nyugdíj) FROM `C:\Users\Peter\Desktop\Book2.xlsx`.`Sheet1$` `Sheet1$`)Természetesen a saját file-od és munkalapod elérési útjával.
-
azopi74
addikt
válasz
Delila_1
#25988
üzenetére
Szia, köszi szépen
Na ezen felbuzdulva, gondoltam összedobok egy olyan UDF tömbfüggvényt, ami visszaad visszatérési értékként egy tömböt, ami tartalmazza a paraméterként megadott tartomány celláinak színeit.
Így próbáltam:
Function IntColor2(szines As Range)
Dim k As Long
Dim ArrayCol() As Long
k = szines.Rows.Count
ReDim ArrayCol(1 To k) As Long
Dim Cell As Range
Dim i As Long
i = 1
For Each Cell In szines
ArrayCol(i) = Cell.Interior.ColorIndex
i = i + 1
Next
IntColor2 = ArrayCol()
End FunctionDe az első cella színét adja vissza a tömb összes elemeként.
Amit nem igazán értek, hiszen az alábbi eljárás - ami gyakorlatilag ugyanezt csinálja, csak eljárásban - pedig jól működik (kiírattam a tömb értékeit, és jó) :
Sub IntColor3()
Dim ArrayCol() As Long
Dim Cell As Range
Dim szines As Range
Dim i As Long
Dim k As Long
i = 1
Set szines = Range("A1:A20")
k = szines.Rows.Count
ReDim ArrayCol(1 To k) As Long
For Each Cell In szines
ArrayCol(i) = Cell.Interior.ColorIndex
i = i + 1
Next
End SubMi lehet a probléma az UDF-fel?
-
azopi74
addikt
Nem is fogod tudni sajnos VBA nélkül. Nincs sajnos olyan függvény excel-ben, ami visszadná a cella színét
Csinálnod kell egy ilyen UDF-t.
Function IntColor(CellColor As Range)
IntColor = CellColor.Interior.ColorIndex
End FunctionUtána ezt behívhatod egy segédoszlopba, és arra hivatkozva vlookup-olhatsz, sumif-elhetsz, match-elhetsz, vagy amit akarsz.. Vagy meghívhatod egy másik udf-ből vagy eljárásból.
Sajnos tömbfüggvényben nem lehet használni UDF-et, pl egy olyan nem fog működni, hogy {=MATCH(48,IntColor(A:A),0)}
pedig bármilyen beépített függvény esetén működik. (pl {=MATCH(-1,SIGN(A:A),0)}Azt nem értem, hogy miért nem, az is lehet, hogy csak alakítani kéne valamit az UDF-en, hogy működjön tömbfüggvényben is. VBA mesterek közül valakinek van ilyenre megoldása ?
-
azopi74
addikt
Tehát jól értem, hogy egy UDF-et akarsz létrehozni VBA-ban, aminek a paraméterei legyen "név", "sz", "az" és "ál", és adja vissza azoknak a mennyiségeknek (minden lap d oszlopa) az összegét, amik a "név" munkalapon vannak, és a "sz", "az" és "ál" oszlopokban szereplő érték egyezik a paraméterként átadott értékekkel? Jól értem?
-
azopi74
addikt
válasz
w.miki
#25978
üzenetére
Most olvasom, beírni? Eddig arról volt szó, hogy másolni akarsz, nem?
Szóval néha, másolgatsz, sokat, ugyanoda, néha meg manuálisan rögzíted ?
Akkor állítsd be textre fixen, és akkor jó lesz. Ha máshonnan másolod, hozni fogja az eredeti forrás formátumát, ha meg rögzítesz, marad a text. De utána ne piszkáld az egész tartományt, és ne akard kijelölni az egészet, és állítgatni a formátumokat, akkor se, ha csúnya
Ha esztétikán akarsz javítani zárd jobbra vagy balra , az nem módosítja a tartalmat és a formátumokat. -
azopi74
addikt
Ha az IMEI hossza is változhat (bár az úgy tudom, fix), akkor nem fogod tudni így megoldani, ha azt akarod, hogy minden úgy maradjon, ahogy eredetileg volt, és az bármilyen formátumú, hosszú lehet, akkor marad az eredeti megoldás, ne piszkáld, csak másold át az ereditit sima Ctrl-C Ctrl-V-vel, így minden úgy marad, ahogy eredetileg volt.
Persze, így nem lesz túl esztétikus a kimenet, ha különböző forrásokból származnak -
azopi74
addikt
válasz
azopi74
#25970
üzenetére
Ja bocs, ha a kezdő 0-kat meg akarod hagyni, ez nem megoldás. Az hogy hány 0-val kezdődik, az fix, vagy bármennyi lehet? (Nem vagyok otthon IMEI témában annyira.)
Talán érdemesebb lenni mégis szövegként kezelni. Az nem jó neked? Úgy biztosan megmaradnának a 0-k az elején.
-
azopi74
addikt
válasz
w.miki
#25967
üzenetére
Jobbegér, cellaformázás, egyedi (Format Cells, Custom):
Azt hiszem, ilyen az IMEI nemzetközileg elfogadott formátuma, ezt állítsd be:
##" "######" "######" "#
de ha, akarod, akkor olyan elválasztásokat raksz be, amiket akarsz, olyan tagolásban, ahogy akarod
Így szám marad, és a formátum is megfelelő lesz.
Le is akarod validálni a 15. ellenőrző számjegyet (check digit) ?
-
#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)
-
azopi74
addikt
Szia, mit szeretnél egész pontosan elérni? Mert attól tartok, ez nem nagyon derült ki ebből.
Legközelebb, ha választ szeretnél a kérdésedre, javaslom, töltsd fel valahova a fájlt, amivel dolgoznál (ha bizalmas adatok vanna benne, természetesen cseréld le az értéküket valamire), és azt is próbááld megfogalmazni, hogy pontosan mi a kimenet, amit el szeretnél érni, mert ezek nélkül elég nehéz értelmezni ezt. Mit akarsz SUMIFS-elni, mire kell a makró, mi a pontos feladat?
Csak intuitív módon próbálom kitalálni: cikkszámonként és nevenként akarsz összesíteni a különállő munkalapokról, amik valamiféle forgalmakat tartalmaznak nevenként?
B2-be írd be:
=SUMIFS(INDIRECT(B$1&"!"&"B:B"),INDIRECT(B$1&"!"&"A:A"),alap!$A2)
És húzd a képletet minden irányba.
Mivel nem ismerem az adataid pontos struktúráját/elhelyezkedését, ezért természetesen ezt annak megfelelően módosítsd majd . Bár azt még mindig nem értem, mi a szerepe a makrónak ebben az egészben -
#25950
azopi74
addikt
molacika91
#25944
azopi74
addikt
válasz
molacika91
#25944
üzenetére
Szia,
Sokat agyaltam rajta, de makró nélkül csak iterációval és három segédoszloppal tudtam megoldani. Bár aki nagyon profi named ranges-ben, az talán meg tudja oldani segédoszlopok nélkül is, nekem nem akart összejönni valahogy, ehhez még kicsi vagyok, el kell jobban mélyednem a témában
A lényeg, hogy csináld a következőt:
Először is állítsd be az iterációt 2-re.
(File-Options-Formulas-Enable iterations)
Ha a oszlopot akarod figyelni, menjen b2-be a következő:
=IF(AND(A2<>"",C2<>A2),NOW(),IF(A2="","N/A",B2))
c2-be ez:
=IF(A2="","",IF(OR(B2="",AND(ISNUMBER(C2),A2=C2)),C2,A2))
d2-be meg ez:
=B2=MAX(B
)Aztán feltételes formázásba állítsd be ezt a feltételt.
=D2=TRUE
És legyen igaz a teljes tartományra
Ha magyar exceled van, akkor használd a http://hu.excel-translator.de/
képletfordítót (Angolról magyarra, vesszőt pontosvesszőre cserélve)Ide feltöltöttem:
-
azopi74
addikt
Ügyes vagy, de ilyenkor (ha már feltetted a kérdést) illik azt is elárulni, hogy mi volt a probléma oka, és hogy oldottad meg, mert mások is olvassák ezt a topicot, nem csak az önkéntes "válaszolók", így valszleg más is okulna a dologból. Saját tapasztalatból emlékszem, hogy igen frusztráló tud lenni, amikor valaki rágoogle-zik egy problémára, és megtalálja ugyanazt a kérdést, ami foglalkoztatja, de azt találja, hogy " already solved". Aztán persze nincs tovább a thread. Expertek persze jól látják rögtön, mi volt a gáz a képlettel, de nem csak haladók olvassák ezt a topicot..
-
#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
-
azopi74
addikt
válasz
bara17
#25919
üzenetére
Egyébként nem akarok beleszólni, de ha a makród nem .csv-ből "másolná" a dolgokat, hanem szabályosan importálná , akkor ezt az egész fejtörést megspórolhatnád magadnak, mivel akkor nem lenne szükség text to columsra sem.
Megadhatnád neki rögtön a delimitereket (elválasztókat), ezres elválasztót, tizedes elválasztót, oszloponkánt beállíthatod az adattípust, stb.
Valahogy így
With ActiveSheet.QueryTables.Add(Connection:="TEXT;eleresi ut.csv" _
, Destination:=Range("$A$1"))
.Name = "table"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = ","
.TextFileThousandsSeparator = "."
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With -
-
-
azopi74
addikt
válasz
cellpeti
#25896
üzenetére
De pontosan milyen feliratra gondolsz?
A Title-re? (Azt hiszem, magyarban Cím)
Ha igen, akkor a megoldás:
Function GetTitleText(SheetName, ChartName As String)
'Visszaadja az adott munkalap adott nevű diagramjának címének szövegét
GetTitleText = Sheets(SheetName).ChartObjects(ChartName).Chart.ChartTitle.Text
End FunctionAztán ezt a függvényt olyan sheeten hívod meg, ahol akarod.
(Ha más feliratra gondoltál, akkor értelemszerűen azt az objektumot tedd be az UDF-be, amit akarsz)
-
azopi74
addikt
válasz
azopi74
#25901
üzenetére
Na már találtam is két hibát
1. StartDayNormalStart nem kell, helyette viszont kell StartDayNormalEnd
=DATE(YEAR([@StartTime]),MONTH([@StartTime]),DAY([@StartTime]))+MunkaidőZárás
Ugyanis ez kell a PartDayFirstDay korrekt kiszámításához:
=IF([@IfStartInWorkingTime],[@StartDayNormalEnd]-[@StartTime],0)
2. Ha ugyanaz a kezdő és záródátum, és mindkettő ideje munkaidőn belül van, nem jól kalkulál, mert záróként és nyitóként is bekalkálja a törtnapokat, ezért szükség van erre a boolean változóra:
WithinOneDay: (ha egy munkanapon belül van a starttime és endtime)
=COUNTIFS(DayType[StartWorkingTime],"<="&[StartTime],DayType[EndWorkingTime],">="&[@EndTime],DayType[Type],"Workday")
És ennek megfelelően TotalWorkingTimeCorr (ez a végső megoldás)
:
=IF([@WithinOneDay],[@EndTime]-[@StartTime],[@TotalWorkingTime])
Javítottam, itt elérhető: [link]
Remélem, más logikai bukfenc nincs
-
#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
-
azopi74
addikt
válasz
Fferi50
#25897
üzenetére
Hát szerintem az eredeti feladatleírás teljesen mindegy, hogy van-e adat a H oszlopban vagy nincs, mindenképpen üreset kell visszaadni minden negyediknek . Én legalábbis így értelmeztem.
Ezért egy ilyen jó lenne :
=IFERROR(INDIRECT(CHOOSE(MOD(ROW()-10,4)+1, "E","F","G") &INT( (ROW()-10)/4)+1),"")
Magyarul
=HAHIBA(INDIREKT(VÁLASZT(MARADÉK(SOR()-10;4)+1; "E";"F";"G") &INT( (SOR()-10)/4)+1);"")
-
-
azopi74
addikt
válasz
Fferi50
#25890
üzenetére
Szia. És ha a H oszlop nem üres? Persze a képen látszik, hogy jelen esetben az, de erre a feladatleírás szerint nincs garancia . Én ezért futtattam inkább szándékosan hibára ezeknél a dupla reciprokozással, és IFERROR-ral megadtam neki a üres sztringet . Bár szerintrm a te megoldásod is átalakítható, ha a választ (choose) függvény értéktömbjét lerövidíted három elemre
Bár nem vagyok gépnél, telefonról írok, így most nem tudom letesztelni. -
#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
-
azopi74
addikt
-
azopi74
addikt
válasz
azopi74
#25860
üzenetére
Esetleg így rövidebb és érthetőbb, és mentes egy amúgy hibát nem okozó logikai hibától
=IFERROR(INDEX(E:G,ROUNDDOWN((ROW()-6)/4,0),1/(1/(MOD(ROW()-9,4)))),"")
Ha magyar az exceled, akkor fordítsd le
http://en.excel-translator.de/
(Source: English, Target: Hungarian, Separator: Replace commas with semicolons)
=HAHIBA(INDEX(E:G;KEREK.LE((SOR()-6)/4;0);1/(1/(MARADÉK(SOR()-9;4))));"")
-
#25855
azopi74
addikt
Gravity1234
#25854
azopi74
addikt
válasz
Gravity1234
#25854
üzenetére
SUMIF függvény. Magyarul SZUMHA.
-
#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:
-
azopi74
addikt
Vagy, esetleg csinálhatod így (úgy értettem a teljesítményt szeretnéd javítani, tehát gondolom elég nagy az adatbázis) :
Létrehozol egy új query-t az excel file-ból egy új munkalapra (monjuk "ujak"):
(Data -> Get External Data -> From Other Source -> From Microsoft Query -> Excel Files -> megadod az elésérési utat)
behúzod Munka1-ből és Munka2-ből az adatokat (név, email minekettőből),
és csinálsz egy ilyen lekérdezést (SQL gompra kattintva):SELECT `Munka2$`.Név, `Munka2$`.Email
FROM {oj `Munka2$` `Munka2$` LEFT OUTER JOIN `Munka1$` `Munka1$` ON `Munka2$`.Név & `Munka2$`.Email = `Munka1$`.Név & `Munka1$`.Email}
WHERE (`Munka1$`.Név Is Null)A makróban csak rá kell frissítened az "Ujak" munkalapra, és átmásolni Munka1-be az ujakat.
Ez így sokkal (sok adat esetén több-százszor/ezerszer) gyorsabb, mint a COUNTIF/COUNTIFS (DARABTELI és társa) vagy VLOOKUP föggvények, és ez is csak "plain" excel, nem kell hozzá külső adatbázis. Ha PowerQuery kiegészítő telepíte van (Excel 2010-től felfelé elérhető), akkor ez kissé felhasználóbarátabb módon is megoldható, mint MS Query-vel. -
azopi74
addikt
ThisWorkbook-ba:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableCancelKey = xlDisabled
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"utvonal\nev.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Dim Outlook As Object, EMail As Object
Set Outlook = CreateObject("Outlook.Application")
Set EMail = Outlook.CreateItem(0)
With EMail
.to = "valaki@valahol.com; valaki@valahol.com"
.CC = ""
.BCC = ""
.Subject = "tárgy"
.Body = "szöveg"
.Attachments.Add "utvonal\nev.pdf"
.Send
End With
Set EMail = Nothing
Set Outlook = Nothing
End SubMegjegyzés:
Az "Application.EnableCancelKey = xlDisabled" Nem biztos, hogy szükséges, nekem "Code execition has been interrupted"-ot dobott nélküle, (de azért lefutott) így küszöböltem ki
-
#25794
azopi74
addikt
Attila7298
#25777
azopi74
addikt
válasz
Attila7298
#25777
üzenetére
Szia,
mit is szeretnél pontosan, és mi a gondod?
Azt szeretnéd, hogy ha több feltétel is teljesül (vagyis az "adatszürés" sheet F17 cellájába írt értéket több helyen is megtalálja a 3-ik, 5-ik, és 7-ik oszlop közül, akkor ne ugorjon egy sorral lejjebb minden egyes feltétel kiértékelésnél?
Mert akkor ne léptesd az n-et, minden egyes If - End If statement-ben, csak a végén (ha kell).
Valahogy így:
Sub masolasi2()
Dim i, p, n, sor As Integer 'i-sor, n- sor a Munka2-n
sor = Worksheets("Idöszakos").Cells(1, 1) 'utolsó sor
n = 4
t = Worksheets("Adat szürés").Cells(17, 6) 'kereset számSheets("Idöszakos").Activate
For i = 4 To sor
p = InStr(1, Cells(i, 3).Value, t, vbTextCompare)
p1 = InStr(1, Cells(i, 5).Value, t, vbTextCompare)
p2 = InStr(1, Cells(i, 7).Value, t, vbTextCompare)
If p > 0 Then
Worksheets("Sz_adat2").Cells(n, 7).Value = Cells(i, 2).Value
Worksheets("Sz_adat2").Cells(n, 1).Value = Cells(i, 1).Value
Worksheets("Sz_adat2").Cells(n, 8).Value = Cells(i, 1).Value
End If
If p1 > 0 Then
Worksheets("Sz_adat2").Cells(n, 9).Value = Cells(i, 4).Value
Worksheets("Sz_adat2").Cells(n, 1).Value = Cells(i, 1).Value
Worksheets("Sz_adat2").Cells(n, 10).Value = Cells(i, 1).Value
End If
If p2 > 0 Then
Worksheets("Sz_adat2").Cells(n, 11).Value = Cells(i, 6).Value
Worksheets("Sz_adat2").Cells(n, 1).Value = Cells(i, 1).Value
Worksheets("Sz_adat2").Cells(n, 12).Value = Cells(i, 1).Value
End If
If (p > 0) + (p1 > 0) + (p2 > 0) Then
n = n + 1
End If
Next i
End Sub -
#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 ?
-
#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
-
azopi74
addikt
válasz
csferke
#25768
üzenetére
Nem vagy ökör, csak az eredeti kérdés feltevés volt kissé rosszul fogalmazva
"Szeretném különválasztani az "A1:A2" mezőket "C1:C2" dátum és "D1:D2" óra:perc-re..."
helyett ha rögtön
"Szeretném egyberakni a C1:C2 dátum és D1:D2 óra:perc- mezőket teljes időadat mezőre, aztán különbséget képezni belőlük "
kellett volna rögtön, és akkor feltehetően megspóroltál volna pár felesleges kört.
Sőt, fel se kellett volna tenned a kérdést, mert hát a "+" az egyik legjobb módszer az összeadásra, kivonásra meg a "-" jel, szóval a TimeEnd - TimeStart, jelen esetben (c2+d2)-(c1+d1) tökéletesen járható út![;]](//cdn.rios.hu/dl/s/v1.gif)
Az általad említett D3=(C2-C1)+(D2-D1) már kissé szofisztikált, mert bár mi ismerjük az alapműveletek azonoságait, bölcsészeknek pl nem biztos, hogy ez annyira átlátható és evidens képletezés lenne
2)&".","")
![;]](http://cdn.rios.hu/dl/s/v1.gif)
Új hozzászólás Aktív témák
- HIBÁTLAN iPhone 13 Pro Max 256GB Graphite -1 ÉV GARANCIA - Kártyafüggetlen, MS3756, 100% Akkumulátor
- BESZÁMÍTÁS! GIGABYTE B650M R5 7600 32GB DDR5 1TB SSD RX 9070 16GB NZXT H700 Cooler Master 750W
- OnePlus 13 Gyors teljesítmény és modern dizájn Midnight Ocean 16/512 GB
- LG 77C4 - 77" OLED evo - 4K 144Hz - 0.1ms - NVIDIA G-Sync - FreeSync - HDMI 2.1 - 1000 Nits
- Samsung Galaxy Z Flip 5 512GB, Kártyafüggetlen, 1 Év Garanciával
Állásajánlatok
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest
Cég: BroadBit Hungary Kft.
Város: Budakeszi