Kezdőlap › Fórumok › Excel témák › Cella lekérdezés adott időszakra, eredmény összehasonlítása más értékkel
- This topic has 8 hozzászólás, 3 résztvevő, and was last updated 7 years, 7 months telt el by Tarentum.
-
SzerzőBejegyzés
-
2017-02-27-21:51 #3400
Sziasztok,
Két napja esz a kefe, mert kaptam egy határidős melót, és nagyon nem lenne jó egyesével összehasonlítani 400 bejegyzést. Feladat a következő:
Van nekünk egy műszakbeosztásunk, mivel sokan vagyunk, különböző műszakokban. Műszak jelölése legyen:
Délelőt: DE
Délután: DU
Éjszakás: N
Szabi: VA2-től lefele vannak a nevek: Pisti, Béla, Laci
B1-től jobbra pedig a dátumok, időben növekvő sorrendben: Márc. 1; Márc.2; Márc 3…stb.Az így felépített táblázatból azt látjuk, hogy:
03.01. 03.02. 03.03. 03.04. 03.05 03.06 03.07 03.08. 03.09
Pisti: DE DE V V V DE DU
Béla: DU DU DU DU DU DU DE
Laci: N N N N N V VA Fönti legyen mondjuk a műszakbeosztás. A probléma a következő:
Kaptam egy összesítő riportot, ami a szabadságkérelmeket tartalmazza. Valahogy így:
NÉV KÉRELEM KEZDET VÉG
Pisti V 03.03 03.05
Laci V 03.05 03.06
Béla V 03.02 03.05Pisti kérelmét összehasonlítva a műszakbeosztással, azt látom, hogy stimmel. Nincs vele dolgom.
Lacinál már szopó van, ott egy nap, a 03.05 nem szabi.. Ezt jeleznem kell: A kérelemben V V van, de a beosztásban N V
Bélánál megint nem stimmel: 03.02-től 03.05-ig végig DU-t látok, Szabi szerint V V V kell legyen.Lassan agyvérzést kapok ettől a cucctól.. Gondolom ezért volt könnyű lepasszolni a főnöknek, én meg nem utasíthatom vissza 🙂
Feladat:
Minden kérelmet a riportban össze kell hasonlítanom a műszakbeosztással. Ha stimmel, nincs vele dolgom (esetleg feljegyezhetem, hogy az illetőnek hány szabija volt). Ha nem stimmel, fel kell jegyeznem, hogy melyik napok térnek el. Mit látok a műszakbeosztásban, és mi van a kérelemben.
!!!HELP me PLEASE!!!
2017-02-27-22:02 #3401naná, hogy elcsúsztak a space-ek… 🙁 Bocs érte. Csatoltam Excellben is a feladatot, hogy átláthatóbb legyen.
Attachments:
You must be logged in to view attached files.2017-02-28-00:31 #3403Szia!
Én annyit tudok most segíteni, hogy feltételes formázással kiemelem azokat a műszak bejegyzéseket, ahol V-nek kellene lennie, de nem az van.
Csatoltam.
Ha a szabi bejegyzés tábla is hasonlóan lenne kitöltve, mint a műszak, de abba csak V bejegyzések vannak, akkor Power Query-vel meg lehetne csinálni úgy, hogy csak aműszal táblát meg a szabi táblát kellene aktualizálni, majd frissítés, és kész.Fontos kérdés,hogy milyen verziójú Excelt használsz? Mivel a feladat xls formátumú volt.
Imre
Attachments:
You must be logged in to view attached files.2017-02-28-00:48 #3405Ha tudsz telepíteni Power Query bővítményt, azaz 2010-es vagy annél újabb Exceled van, akkor tudok szolgálni egy megoldással, ahogy említettem.
Itt egy kép róla.
Valamiért nem látszik, de az első oszlopban ott vannak a nevek is természetesen.
Béla
Béla
Béla
Béla
LaciTehát csak azok, ahol eltérés van.
- A hozzászólás módosításra került: 7 years, 7 months telt el-horvimi.
Attachments:
You must be logged in to view attached files.2017-02-28-08:20 #3408Kedves Imre,
Köszönöm a gyors reagálást. Office 2016 van a munkahelyi gépeken. Otthon meglesem, mert itt keresztrefeszítés jár érte. Ha jól értem így lehetséges a riportból egy kivonatot csinálni? Tehát ha kérdezi a főnök, hogy melyik ember melyik requestjének melyik napjaival volt probléma, akkor ez a fetételes szűrés alkalmas lehet a feladat teljesítésére?Köszönöm, hogy fordított energiát más problémájára. Minden más már számomra bónusz ?
2017-02-28-08:24 #3409Az Office 2016 jó hír, mert abba be van építve a Power Query alapból.
Miért jár keresztre feszítés? Ezt nem értem. Telepíteni nem lehet dolgokat? Ez esetben nem kell.Egy munkahelyi problémához kértél segítséget.
A Power Query verziót este tudom feltenni, mert az otthoni gépemen van.
Igen, jól érted.Ha a szabadság igényléses tábla struktúráját megváltoztatjátok, vagy az átalakításhoz is készül egy megoldás, akkor csak be kell másolni az aktuális adatokat a Power Query riport munkafüzetbe, és az eredmény táblán nyomni egy frissítést.
Imre
2017-02-28-23:04 #34122017-03-01-10:02 #3414Sub test() Dim MyRnd1 As Double MyRnd1 = Int((999 - 1 + 1) * Rnd + 1) MsgBox MyRnd1 MsgBox Replace(TimeValue(Now), ":", "") End Sub Sub Main() 'last update 2017.feb.28 - gege - initial version ' cső Norbi! ' a makrót többféleképpen is lehet futtatni, pl. altf + F8 --> kiválasztod a "main" nevűt --> run. Vagy beleteszed a kurzort ide a makró szövegébe és F5-öt nyomsz, vagy a play gombot. ' Javaslom, hogy az adatot amin futtatni akarod a makrót, másold egy új, külön xls-be (vagy másold ebbe az adatot, az is jó) ' a fájl kiterjesztésének xlsm-nek kell hogy legyen, ha makrót akarsz belerakni (vagy xls, de az csak 256 oszlopos, az nem lesz neked elég; szóval save as xlsm) ' első munkalapra menjen a shift plan ' másodikra a requestek ' a makró kidob egy új munkalapot a harmadik helyre, a lap nevének a vége egy generált érték (hogy ne egyezzen a neve esetleg korábbi futtatások kimenetével) ' nyugodtan nézd át a makrót, írogass át benne amit akarsz ' mindenképp az A1 cellában kezdődjön az adat (fejléc az első sor, aztán adat a másodiktól) ' a munkalapokon ne legyen más csak az adat és ne legyenek üres sorok, cellák ha lehet (legalábbis az első oszlopban semmiképp) 'jelenleg ha hétvégét is belevéve kért valaki szabadságot vagy táppénzt, azt nem egyezőnek veszi a makró, de odaíratom vele a kimenethez hogy az adott nap hétvége ' figyelem: a makró csak azt nézi, hogy az adott requestben kért dolgok benn vannak-e shift planben. Azt nem nézi, hogy van-e shift planben pl. betegség vagy vacation amire nem volt request! ' ha azt is meg kéne nézni, azt majd megcsinálom, de egyelőre nem fog az agyam. valszeg azt más logikával kéne leprogramozni :D (most eszembe jutott hogy lehetne, leírom ide ha kell: ' egy tömbben eltárolni az összes request dátumát és a nevet akihez tartozik, ' majd a végén átnézni hogy van-e olyan név/dátum páros a shift planben ami sick vagy vacation, de nem volt rá request ' kérlek teszteld és ha van benne hiba, vagy más funkciót szeretnél belerakni akkor szólj. Holnap HO-ban vagyok, tudok majd vele foglalkozni kicsit. Dim MyWb As Workbook Dim MainWs As Worksheet 'shift plan Dim ReqWs As Worksheet 'requests Dim OutWs As Worksheet 'output goes here Dim MyRnd As Double Dim MyCell As Range 'variable for looping Dim MyCell_2 As Range 'another variable for looping Dim MyRow As Long Dim MyCol As Long 'variables for shiftplan Dim sTopLeft As Range 'first name's position on the top left of the shift plan (for defining the position of the requests table) Dim sLr As Long 'last row with data Dim sLc As Long 'last column with data Dim sMainRange As Range 'all the data in the shiftplan Dim sType As String 'variables for requests Dim rTopLeft As Range 'first request's left cell (for defining the position of the requests table) Dim rRange As Range 'all requests Dim rName As String Dim rTypeLong As String Dim rType As String Dim rDateStart As Long Dim rDateEnd As Long Dim rDateArray() As Long Dim rLr As Long 'last row with data Dim i As Long Dim j As Long Set MyWb = ThisWorkbook Set MainWs = MyWb.Sheets(1) 'can be defined with name as well, for example Set MainWs = MyWb.Sheets("Shitftplan") Set ReqWs = MyWb.Sheets(2) 'can be defined with name as well, for example Set ReqWs = MyWb.Sheets("Requests") Set OutWs = MyWb.Sheets.Add(, ReqWs) 'MyRnd = Int((998 - 1 + 1) * Rnd + 1) OutWs.Name = "Results_" & Replace(TimeValue(Now), ":", "") 'CURRENTLY THIS ONLY WORKS IF TOP LEFT IS A1, sorry... Set sTopLeft = MainWs.Range("A1") Set rTopLeft = ReqWs.Range("A1") rLr = ReqWs.Cells(ReqWs.Rows.Count, rTopLeft.Column).End(xlUp).Row sLr = MainWs.Cells(MainWs.Rows.Count, sTopLeft.Column).End(xlUp).Row sLc = MainWs.Cells(sTopLeft.Row, MainWs.Columns.Count).End(xlToLeft).Column Set sMainRange = MainWs.Range(sTopLeft, MainWs.Cells(sLr, sLc)) OutWs.Range("A1:F1").Value = Array("name", "date", "shitfplan_value", "request_value", "is_weekend", "comment") 'HORRIBLE HACK Call Replace_dates(MainWs.Range(MainWs.Range("B1"), MainWs.Cells(1, sLc))) 'loop through requests and put values into variables For Each MyCell In ReqWs.Range(rTopLeft.Offset(1, 0), ReqWs.Cells(rLr, rTopLeft.Column)) rName = MyCell.Value rTypeLong = MyCell.Offset(0, 1).Value Select Case rTypeLong ' Case "Morning": rType = "A" ' Case "Afternoon": rType = "B" ' Case "Night": rType = "C" ' Case "Office": rType = "D" ''' commented out because only sick / vacation requests are possible Case "Sick": rType = "X" Case "sick": rType = "X" Case "Holiday": rType = "V" Case "holiday": rType = "V" Case "Vacation": rType = "V" Case "vacation": rType = "V" Case Else: rType = "undefined" End Select rDateStart = CLng(DateValue(MyCell.Offset(0, 2).Value)) rDateEnd = CLng(DateValue(MyCell.Offset(0, 3).Value)) rDateArray = fillDates(rDateStart, rDateEnd) 'let's find the name row from the request in the shiftplan MyRow = Application.WorksheetFunction.Match(rName, sMainRange.Columns(1), 0) 'check each date and output if there is discrepancy For i = LBound(rDateArray) To UBound(rDateArray) MyCol = Application.WorksheetFunction.Match(rDateArray(i), sMainRange.Rows(1), 0) sType = MainWs.Cells(MyRow, MyCol).Value If sType <> rType Then With OutWs.Cells(j + 2, 1) .Value = rName .Offset(0, 1).Value = rDateArray(i) .Offset(0, 1).NumberFormat = "yyyy-mm-dd" .Offset(0, 2).Value = sType .Offset(0, 3).Value = rType If Application.Weekday(rDateArray(i), 2) >= 6 Then .Offset(0, 4).Value = "WEEKEND" .Offset(0, 5).Value = "Request was from " & CDate(rDateStart) & " to " & CDate(rDateEnd) End With j = j + 1 End If Next i Next 'some formatting, add more if you want OutWs.Columns.AutoFit 'terv: ciklus menjen végig a requesteken ' ciklus menjen végig a dátum intervallumokon, tegye be egy tömbbe (név, requesttype, dátumok) ' kimenet is kerüljön be egy tömbbe ' kimenetet tegye ki egy külön munkanapra (név, dátum, requesttype shitfplanről, ezzel nem egyező request érték) End Sub Sub Replace_dates(dRange As Range) 'horrible hack to make my match function work 'replace all dates in row 1 to integer numbers Dim Rng As Range For Each Rng In dRange Rng.Value = CLng(DateValue(Rng.Value)) Next End Sub Function fillDates(ByVal StartDate As Long, ByVal EndDate As Long) As Variant 'partially taken from http://stackoverflow.com/questions/29102506/get-all-dates-between-2-dates-in-vba Dim varDates() As Long Dim lngDateCounter As Long ReDim varDates(0 To EndDate - StartDate) For lngDateCounter = LBound(varDates) To UBound(varDates) varDates(lngDateCounter) = StartDate StartDate = StartDate + 1 Next lngDateCounter fillDates = varDates ClearMemory: If IsArray(varDates) Then Erase varDates lngDateCounter = Empty End Function
2017-03-01-21:32 #3415Nagyon szépen köszönöm a befektetett munkátokat!!!!! 🙂 Arról nem is beszélve, hogy milyen hamar reagáltatok.
Tisztelettel,
Norbi -
SzerzőBejegyzés
- Be kell jelentkezni a hozzászóláshoz.