Elektroda.pl
Elektroda.pl
X
Elektroda.pl
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

EXCEL - 'wygladzanie' odchylenia standardowego ze zbioru liczb.

steelek56 03 Cze 2017 16:59 834 7
  • #1 03 Cze 2017 16:59
    steelek56
    Poziom 12  

    Witam,

    Wpadłem na ciekawy pomysł jak sobie ułatwić życie. Mianowicie co dzień w pracy muszę uzupełniać co raz to dłuższą listę wyjazdów samochodów do odbiorców. Dostawy robimy wciąż na stałą godzinę więc na podstawie odległości można wyliczyć jak długo trzeba jechać aby dojechać do celu o godzinie 6:00. Wniosek - godziny dla danych punktów są niezmienne.

    Stąd też przy wykorzystaniu funkcji WYSZUKAJ.PIONOWO utworzyłem listę wszystkich możliwych celów do których chce jechać oraz godzinę o której powinien wyjechać środek transportu aby dojechać na 6:00. Wszystko działa bardzo sprawnie, ale...

    ...i tu zaczyna się mój problem. Załadunki rozpisane co 15 minut (01:00, 01:15, 01:30...itd). Czasem po przypisaniu godzin okazuje się że np na 1:15 jest 7 załadunków a na 1:30 tylko 1. Chodzi mi o wymyślenie rozwiązania, które przeniesie kilka załadunków z wspomnianej 1:15 na 1:30 tak żeby obciążenie załadunkowego było podobne. Czyli przesunięcie 3 załadunków na 1:30 tak aby i na 1:15 i na 1:30 były po 4 załadunki.

    Próbowałem ugryźć to dodatkiem - Solver, jednak skutek był kiepski nawet jeżeli przeliczanie godzin ująć w inny sposób to nadal ustawianie warunków dla np 100 tras byłoby zadaniem na tydzień klikania.

    Myślę o czymś w stylu makra VBA, wykorzystującego jakąś pętle w celu 'wygładzenia' różnorodności tych załadunków. Niestety nie mam pojęcia jak się za to zabrać.

    W załączniku plik pokazujący w praktyce o co mi chodzi.

    Opis pliku:

    Arkusz1 komórki z kolumny A oznaczone na żółto zwykle są po prostu godziną formuła ma pokazać sposób w jaki uzyskuję godziny załadunków z arkusza wyjazdy.

    W kolumnie C zmiany jakie należałoby zrobić aby 'wygładzić' zmienność.

    w arkuszu 'Wynik' przeniesione załadunki z 6 wyjazdów o 0:30 i 0:45 w taki sposób aby ilość załadunków na jednostkę czasu (kwadrans) nie przekraczała 4. Z reguły tą ilość ustalamy na maksymalnie 5 załadunków na kwadrans.

    W obu arkuszach H14:K21 - tabelka pokazująca ilości załadunków w jednostce czasu.

    Makro do określenia jak i czy zmienić godzinę załadunku musiałoby brać wartości spośród kolumn C i D z arkusza 'Wyjazdy'. I tak jeżeli w kolumnie B optymalny załadunek to 0:45 a w kolumnie C najwcześniejszy możliwy to 0:15 i w kolumnie D najpóźniejszy możliwy to 1:15, to makro szukając zmiany zmieniałoby na jedną z - 0:15, 0:30, 0:45, 1:00, 1:15 - najlepiej możliwie najbliżej optymalnej godziny czyli 0:45.



    Będę wdzięczny za jakiekolwiek pomysły na to jak ten problem rozwiązać.

    0 7
  • #2 05 Cze 2017 12:46
    Prot
    Poziom 32  

    steelek56 napisał:

    ...i tu zaczyna się mój problem. Załadunki rozpisane co 15 minut (01:00, 01:15, 01:30...itd). Czasem po przypisaniu godzin okazuje się że np na 1:15 jest 7 załadunków a na 1:30 tylko 1. Chodzi mi o wymyślenie rozwiązania, które przeniesie kilka załadunków z wspomnianej 1:15 na 1:30 tak żeby obciążenie załadunkowego było podobne. Czyli przesunięcie 3 załadunków na 1:30 tak aby i na 1:15 i na 1:30 były po 4 załadunki.
    ...
    do określenia jak i czy zmienić godzinę załadunku musiałoby brać wartości spośród kolumn C i D z arkusza 'Wyjazdy'. :?: :?: :?:


    Widać z Twojego przykładu, że pracujesz nad nową wersją "kwadratury koła" lub "zadaniem Erdősa" :D
    EXCEL - 'wygladzanie' odchylenia standardowego ze zbioru liczb.2017-06...png Download (74.78 kB)
    Z krótkiej analizy Twojego "problemu" wynika, że w przedziwny sposób opracowałeś tablicę "Wyjazdy" gdzie tolerancja czasu wyjazdu dla poszczególnych tras waha się od 0 do 180 min :?: :D , a czas "optymalny" określony jest bez żadnej czytelnej reguły.
    Nic zatem dziwnego, że żaden z analizowanych rozkładów nie jest w stanie spełnić 2 warunku zadania :!:
    W mojej ocenie dla najbliższego założonym warunkom rozwiązania dojdziesz stosując najprostszy algorytm oparty o średni czas wyjazdu (ustalany z czasu najwcześniejszego i najpóźniejszego) :idea:

    0
  • #3 05 Cze 2017 13:03
    steelek56
    Poziom 12  

    Cóż... Różnice w najwczesniejszym załadunku a najpozniejszym były raczej losowe. Myślałem wczoraj np żeby uprościć cały proces do wprowadzenia formuły licz.jezeli z kryterium że jeżeli w kolumnie dana godzina występuje więcej niż 5 razy. To ma przesunąć godzinę o 15 minut w tył. Tylko Excel wywala mi błąd nawet nie mogę oszacować formuły. Podejrzewam że się robi tu taka dziwna nieskonczona pętla.

    0
  • #4 05 Cze 2017 13:28
    Prot
    Poziom 32  

    steelek56 napisał:
    Tylko Excel wywala mi błąd nawet nie mogę oszacować formuły


    Prawdopodobnie próbujesz używać formuł tablicowych jako zwykłe formuły :?: :cry:
    EXCEL - 'wygladzanie' odchylenia standardowego ze zbioru liczb.2017-06-0..1).png Download (87.97 kB)

    Do wyliczania liczby załadunków w poszczególnych godzinach ja stosowałem formuły jak na zrzucie :D

    Cytat:
    Różnice w najwczesniejszym załadunku a najpozniejszym były raczej losowe


    To wbrew logice i chyba zasadom logistyki :?:

    1
  • #5 09 Cze 2017 15:47
    steelek56
    Poziom 12  

    Prot napisał:
    To wbrew logice i chyba zasadom logistyki :?:


    Bardziej mialem na mysli ze widelki dla kazdego punktu byly wypisane losowo. I faktycznie jest to wbrew zasadom logistyki, jednak na sam czas przejazdu wyplywa wiele roznych czynnikow od obranej trasy przez konkretnego kierowce az po rzeczy typu zdarzenia losowe. Stad pomyslalem zeby napisac makro ktore na podstawie zawartosci kolumny zliczy ilosc danych rekordow i przesunie w sumie nawet pierwsze napotkane o 15 minut wczesniej. Jako godzine startową mógłbym obrać najpóźniejszy możliwy załadunek a przesunięcie w razie potrzeby załadunku nawet o 45 minut wczesniej nie bedzie wielkim problemem gdyż kierowca moze zrobic np pauze 45 minut przed rozladunkiem.

    Co istotne chcialbym uniknąć wielu kolumn formuł jak w przykladzie wyżej i po prostu wyliczyc liczbe rozładunków przy uruchomieniu makra.

    0
  • #6 09 Cze 2017 19:06
    Prot
    Poziom 32  

    steelek56 napisał:

    Co istotne chcialbym uniknąć wielu kolumn formuł jak w przykladzie wyżej i po prostu wyliczyc liczbe rozładunków przy uruchomieniu makra.


    IMHO bez względu na zastosowane narzędzia (funkcje Excela czy kod VBA) nie znajdziesz żadnego rozwiązania dla tak sformułowanego problemu i warunków (no bo nie masz żadnego pola manewru jak zakładasz tolerancję czasu wyjazdów = 0 :D ).

    0
  • #7 09 Cze 2017 19:18
    steelek56
    Poziom 12  

    Właśnie zakładam tolerancje nawet powiedzmy 30 minut w jedna i druga strone co daje godzine i 5 mozliwych trafien (zakładając skok co 15 minut).

    Inna rzecz to fakt, że nawet jak próbuje to jakoś ugryźć to excel jakby mial problemy z okreslaniem godziny jako liczby. Najlatwiej wychodzi mi budowanie formul w oparciu o tekst ( cudzysłowy ). Tylko, że na tym nie moge przeprowadzić działań

    0