VBA 5 napot meghaladó szolgáltatás igénybevétel

VBA 5 napot meghaladó szolgáltatás igénybevétel
2021-04-14T12:46:58+02:00
2021-04-15T22:01:49+02:00
2022-10-15T21:21:42+02:00
Nándesz
Sziasztok,

Szeretnék abban segítséget kérni, hogy hogyan lehetne egy excel táblázatból kinyerni, hogy mely ügyfél vette igénybe folyamatosan 5 napot meghaladóan a cég  szolgáltatását.
Az ügyfelek azon. szám szerint van megkülönböztetve és sorrendbe rendezve. A dátum oszlop mutatja a szolgáltatás napját. Csináltam egy segédoszlopot, ami megmutatja, hogy a soron következő szolgáltatások között hány nap telt el.
Úgy gondoltam, hogy azon-ra szűrnék a programmal és worksheetfunction.countif -fal megszámláltatnám a segédoszlopban hány 1 napot talál. Sajnos ez nem jó, mert közbe jöhet olyan nap, ahol több nap telik el két szolgáltatás között mint 1 nap, és ilyenkor újra kell indítani a számolást (18-as ügyfél). Kifogytam az ötletből, hogyan lehetne a legkézenfekvőbben megoldani (nemcsak ötlet, hanem program nyelv is érdekel, mert nem vagyok programozó :) )
Az alaptáblában jó lenne beszínezni azon sorokat, ahol 5 napot meghaladó a folyamatos szolgáltatás igénybe vétel, és emellett jó lenne a K-M oszlopban egy külön táblában vezetni, ha talál folyamatos 5 napot meghaladó szolgáltatást igénybe vevő ügyfelet. (év-hónapról)
Addig jutottam, hogy az állományból leszűröm M:N oszlopba a szűréshez szükséges azonosítókat és neveket, mert úgy gondoltam, hogy a szűrésnél  ezen lépked végig a program.
Csatolom az excelt.
Mutasd a teljes hozzászólást!
Csatolt állomány
Szia!
VBA-ban valami ilyesmi a megoldás (de a segédtábla dátum oszlopait O:P nem formázza meg dátum típusúra):

Sub szamol() 'Segédtábla fejléc 1 sor, M:Q oszlop Cells(1, 13).Value = "azon" Cells(1, 14).Value = "nev" Cells(1, 15).Value = "kezdő dátum" Cells(1, 16).Value = "záró dátum" Cells(1, 17).Value = "eltelt nap" ' M:Q oszlop (segédtábla) készítéshez a következő üres sor tarolando_sor = 2 aktualis_sor = 2 ugyfelszam = Cells(aktualis_sor, 2).Value 'Ciklus ügyfelenként: amíg az ügyfélszám cella nem üres és az ügyfélszám nem változik While Cells(aktualis_sor, 2).Value <> "" And ugyfelszam = Cells(aktualis_sor, 2).Value kezdonap_sora = aktualis_sor kezdodatum = Cells(aktualis_sor, 4).Value eltelt_napok = 0 ' Ciklus dátum szerint: amíg az ügyfél nem változik és a dátum 0 vagy 1 nappal tér el a kezdő (előző sor) dátumtól While Cells(aktualis_sor, 2).Value <> "" And (ugyfelszam = Cells(aktualis_sor, 2).Value) And DateDiff("D", kezdodatum, Cells(aktualis_sor, 4).Value) <= 1 eltelt_napok = eltelt_napok + 1 kezdodatum = Cells(aktualis_sor, 4).Value aktualis_sor = aktualis_sor + 1 Wend ' Ha 5 nap vagy több volt a folyamatos szolgáltatás: a kezdonap_sora és a záró nap között If eltelt_napok >= 5 Then ' Dátumok színezése a kezdonap_sora és a záró nap között zöld háttér Range(Cells(kezdonap_sora, 4), Cells(aktualis_sor - 1, 4)).Interior.Color = RGB(0, 255, 0) ' Segédoszlopba (E) kiírjuk az eltel napok számát Cells(aktualis_sor - 1, 5).Value = "igénybevett napok száma: " & eltelt_napok ' Aktuális adatok tárolása a tarolando sor M:Q oszlopába Cells(tarolando_sor, 13).Value = Cells(kezdonap_sora, 2) ' azon Cells(tarolando_sor, 14).Value = Cells(kezdonap_sora, 3) ' nev Cells(tarolando_sor, 15).Value = Cells(kezdonap_sora, 4) ' datum (kezdő) Cells(tarolando_sor, 16).Value = Cells(aktualis_sor - 1, 4) ' datum (záró) Cells(tarolando_sor, 17).Value = eltelt_napok ' eltelt napok tarolando_sor = tarolando_sor + 1 End If ' aktuális ügyfél szám eltárolása, az ügyfél ciklus miatt ugyfelszam = Cells(aktualis_sor, 2).Value Wend End Sub
Mutasd a teljes hozzászólást!

  • Szia!
    Ügyfélszám és dátum szerint kellene növekvő sorba rendezni a táblázatot.
    Ha nem vagy programozó és a programnyelv érdekel, akkor VBA (makró) a kézenfekvő megoldás, 
    nem tudom miért írnád meg pl. c#-ban.
    Én úgy fognék hozzá, hogy ciklussal végigmennék az ügyfeleken, azon belül másik ciklussal a dátumokon, és megnézném, hogy az előző sor dátuma 1 nappal kevesebb-e. Ha igen, akkor folyamatos és növelném az eltelt napok számát, ha nem (több nap), akkor megszakad a folyamatossága, meg kell nézni, hogy az eltelt napok száma nagyobb-e, mint öt (ha igen, színezés, ügyfél tárolása...), ha nem akkor kezdő dátum beállítás, eltelt nap nulla és megyünk tovább.
    Mutasd a teljes hozzászólást!
  • Szia!
    VBA-ban valami ilyesmi a megoldás (de a segédtábla dátum oszlopait O:P nem formázza meg dátum típusúra):

    Sub szamol() 'Segédtábla fejléc 1 sor, M:Q oszlop Cells(1, 13).Value = "azon" Cells(1, 14).Value = "nev" Cells(1, 15).Value = "kezdő dátum" Cells(1, 16).Value = "záró dátum" Cells(1, 17).Value = "eltelt nap" ' M:Q oszlop (segédtábla) készítéshez a következő üres sor tarolando_sor = 2 aktualis_sor = 2 ugyfelszam = Cells(aktualis_sor, 2).Value 'Ciklus ügyfelenként: amíg az ügyfélszám cella nem üres és az ügyfélszám nem változik While Cells(aktualis_sor, 2).Value <> "" And ugyfelszam = Cells(aktualis_sor, 2).Value kezdonap_sora = aktualis_sor kezdodatum = Cells(aktualis_sor, 4).Value eltelt_napok = 0 ' Ciklus dátum szerint: amíg az ügyfél nem változik és a dátum 0 vagy 1 nappal tér el a kezdő (előző sor) dátumtól While Cells(aktualis_sor, 2).Value <> "" And (ugyfelszam = Cells(aktualis_sor, 2).Value) And DateDiff("D", kezdodatum, Cells(aktualis_sor, 4).Value) <= 1 eltelt_napok = eltelt_napok + 1 kezdodatum = Cells(aktualis_sor, 4).Value aktualis_sor = aktualis_sor + 1 Wend ' Ha 5 nap vagy több volt a folyamatos szolgáltatás: a kezdonap_sora és a záró nap között If eltelt_napok >= 5 Then ' Dátumok színezése a kezdonap_sora és a záró nap között zöld háttér Range(Cells(kezdonap_sora, 4), Cells(aktualis_sor - 1, 4)).Interior.Color = RGB(0, 255, 0) ' Segédoszlopba (E) kiírjuk az eltel napok számát Cells(aktualis_sor - 1, 5).Value = "igénybevett napok száma: " & eltelt_napok ' Aktuális adatok tárolása a tarolando sor M:Q oszlopába Cells(tarolando_sor, 13).Value = Cells(kezdonap_sora, 2) ' azon Cells(tarolando_sor, 14).Value = Cells(kezdonap_sora, 3) ' nev Cells(tarolando_sor, 15).Value = Cells(kezdonap_sora, 4) ' datum (kezdő) Cells(tarolando_sor, 16).Value = Cells(aktualis_sor - 1, 4) ' datum (záró) Cells(tarolando_sor, 17).Value = eltelt_napok ' eltelt napok tarolando_sor = tarolando_sor + 1 End If ' aktuális ügyfél szám eltárolása, az ügyfél ciklus miatt ugyfelszam = Cells(aktualis_sor, 2).Value Wend End Sub
    Mutasd a teljes hozzászólást!
  • Szuper!Köszi, hogy segítesz.
    While wend ciklust nem használtam eddig. Legalább ebből is tanulok!
    Kipróbálom. :)
    Mutasd a teljes hozzászólást!
  • Szia!
    Makrófelvevővel letárolhatnád a növekvő sorba rendezést, mert az eredeti táblázatodon nem fog jól működni a rendezetlensége miatt. Az első hozzászólásomban írtam erről. Ha már makrófelvevő, akkor az O:P oszlopok dátum formátumra állítását is felveheted és bemásolhatod a makró elejére.

    A rendezést kézzel is beállíthatod, mielőtt kipróbálod a makrót:
    1. szint: azon
    2. szint: datum

    üdv.: Péter
    Mutasd a teljes hozzászólást!
  • Szia, már megcsináltam.Kicsit változtattam rajta hogy jól működjön.Fél napig teszteltem, szerintem jó lesz.
    :)
    Köszi még egyszer, sokat tanultam belőle.
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd