Proporcjonalne rozłożenie. [solved]

[jozo2009]

Witam.

Mam następujący problem.

Załóżmy że rozdajemy sztuki cukierków w zależności od przepracowanych godzin.

Stworzyłem tabelę w której w zależności od ilości tych godzin przypisuje liczbę cukierków. Ilość do rozdysponowania podana jest w komórce C17. Podane godziny są w komórkach od A2 do A11.
Jeżeli godziny są różne każdy dostaje odpowiednią liczbę cukierków. Stworzyłem dodatkową tabelę na wypadek gdy liczba cukierków jest niepodzielna w tej proporcji na podstawie godzin.
Jeżeli po zaokrągleniu przydziału cukierków (kolumna H) ich sumaryczna liczba jest mniejsza lub większa od liczby z komórki C17- stworzona tabela wyszukuje osobę z najmniejszą liczbą godzin i największą liczbą godzin. Jeżeli jest mniejsza od ilości podanej w komórce C17, różnicę dodaje osobie z największą liczbą godzin. Wtedy M27 zgadza sie z C17. Jeżeli jest większa, odejmuje osobie z najmniejszą liczbą godzin. Wtedy M27 zgadza sie z C17.

Jest to sprawiedliwe rozdanie cukierków.

Problem pojawia się jeżeli kilka osób ma te same ilości godzin a jedna np ma większą lub mniejszą liczbę godzin. Tutaj moja tabelka i system rozdawania cukierków zawodzi. Wówczas potrafi odejmować lub dodawać różnicę kilku osobom. Wtedy M27 nie zgadza sie z C17.

Zachodzę w głowę jak zbudować ten system by w pewien sposób proporcjonalny do liczby godzin rozdawał cukierki ale nie wariował gdy powstaje różnica w zaokrągleniu przydziału, a np. wszystkie lub większość osób pracują identyczną liczbę godzin. Jesli kilka osób pracuje podobnie godzinowo rozwala się system, a problem jest jeszcze większy gdy wszyscy maja liczbę godzin identyczną a np do odjęcia lub dodania zostaje ta jedna sztuka. Czy można ją dorzucić jakiejś osobie losowo?

Dodaje przykład A gdzie moja tabela sprawdza sie bardzo dobrze, oraz przykład B gdzie zaczyna źle działać. Nie wiem jak przeskoczyć ten problem i proszę o pomoc.

[Jan_J]

Ja bym to zrobił tak.
Wypłaty (w cukierkach) muszą być całkowite, bo cukierków się nie dzieli.
Napierw przydzieliłbym wszystkim po tyle całych cukierków, ile im przysługuje ze względu wkład pracy.
W drugiej kolejności rozdysponowałbym nadwyżkę, nikomu nie ujmując.
Strategia, by nagrodzić najbardziej pracowitych, jest sprawiedliwa.
Inna sprawiedliwa jest, by dodać tym, którzy najwięcej stracili na zaokrągleniach. Ale i tak warto zadbać, by przy tym samym ułamku pierwszeństwo mieli ci, którzy przepracowali więcej.
Tak czy siak, zbudowałbym ranking według przyjętego kryterium, i przydzielał po jednym ekstra po kolei począwszy od czoła rankingu.

Co jest tu niesprawiedliwe, to że po posortowaniu, wśród osób o tym samym wkładzie pracy faworyzowani będą ci, którzy w oryginalnym spisie figurują na późniejszych pozycjach. Można temu zapobiec albo mieszając przed sortowaniem kolejność w grupach o tej samej liczbie przepracowanych godzin, albo prowadząc rejestr, w którym długoterminowo równoważyłoby się niesprawiedliwie dodane jednostki. Pierwsze prostsze niż drugie.

Warto zajrzeć do blisko związanych z tym problemem metod obsadzania mandatów po wyborach:
https://pl.wikipedia.org/wiki/Metoda_D%E2%80%99Hondta
https://pl.wikipedia.org/wiki/Metoda_Ha ... -Niemeyera
https://pl.wikipedia.org/wiki/Metoda_Sainte-Lagu%C3%AB
Przedstawiona wyżej strategia wtórnego szeregowania wg malejących części ułamkowych jest zbliżona do metody Hare’a-Niemeyera.

Realizacja tych obliczeń w arkuszu, jako że sortowanie nie jest operacją zautomatyzowaną w języku formuł, wymagałaby zbudowania funkcji sortującej i funkcji mieszającej. Wyważanie drzwi otwartych.

Napisałem szkic w Pythonie. Załączam go niżej. Przypadek samych zer w liczbie godzin generuje wyjątek.

Odpowiednio przygotowana funkcja Pythona może pełnić rolę makra w skoroszycie.

Kod wraz z uruchomieniem Twoich testów:

Kod: Zaznacz cały

#
# funkcja wyliczająca przydziały sumujące się do podanej puli
# wariant = 1: dodatkowe sztuki przyznane tym, którym zaokrąglanie odrzuciło najwięcej
# wariant = 2: dodatkowe sztuki przyznane tym, którzy najwięcej przepracowali
#
def przydzial(godziny, pula, wariant):
    s = sum(godziny)
    premia = [ pula*x/s for x in godziny ] 
    # przydziela liczbę całkowitą premii z dostępnej puli
    # nadwyżka jest co najwyżej ułamkiem dla każdego pracownika
    wyplata = [int(x) for x in premia ] 
    # nadwyżka jest liczbą całkowitą
    delta = pula - sum(wyplata)
    if delta > 0:
        # buduje ranking do rozdysponowania nadwyżek
        match wariant:
            case 1:
                ranking = sorted(((x-y,g,i) for i,(x,g,y) in enumerate(zip(premia, godziny, wyplata))), reverse = True)
            case 2:
                ranking = sorted(((x,i) for i, x in enumerate(godziny)), reverse = True)
            case _:
                raise Exception
        # rozdysponowuje nadwyżkę po 1 sztuce wśród pracowników
        # począwszy od stojących najwyżej w rankingu
        for i in range(delta):
            wyplata[ranking[i][-1]] += 1
    return wyplata

# testy według Twoich scenariuszy A i B

pula = 172
prac1 = (168, 175, 145, 155, 174, 0, 0, 0, 0, 0,)
prac2 = (168, 168, 168, 168, 168, 0, 0, 0, 0, 0,)

msg = '''wariant {}
przepracowane: {}
pula premii: {}
premia: {}
suma premii: {}
'''
for godziny in (prac1, prac2):
    wyplaty = przydzial(godziny, pula, 1)
    print(msg.format('wg odrzuconych ułamków', godziny, pula, wyplaty, sum(wyplaty)))

    wyplaty = przydzial(godziny, pula, 2)
    print(msg.format('wg przepracowanych godzin',godziny, pula, wyplaty, sum(wyplaty)))

oraz wyniki

Kod: Zaznacz cały

wariant wg odrzuconych ułamków
przepracowane: (168, 175, 145, 155, 174, 0, 0, 0, 0, 0)
pula premii: 172
premia: [35, 37, 30, 33, 37, 0, 0, 0, 0, 0]
suma premii: 172

wariant wg przepracowanych godzin
przepracowane: (168, 175, 145, 155, 174, 0, 0, 0, 0, 0)
pula premii: 172
premia: [36, 37, 30, 32, 37, 0, 0, 0, 0, 0]
suma premii: 172

wariant wg odrzuconych ułamków
przepracowane: (168, 168, 168, 168, 168, 0, 0, 0, 0, 0)
pula premii: 172
premia: [34, 34, 34, 35, 35, 0, 0, 0, 0, 0]
suma premii: 172

wariant wg przepracowanych godzin
przepracowane: (168, 168, 168, 168, 168, 0, 0, 0, 0, 0)
pula premii: 172
premia: [34, 34, 34, 35, 35, 0, 0, 0, 0, 0]
suma premii: 172

[Jermor]

Ja także poszedłem śladem @Jan_J. Spróbowałem jednak obejść się bez makr i efekt tego przedstawiam.
Jedynym ograniczeniem jest skorzystanie z LibreOffice w wersji co najmniej 24.8, gdyż od tego wydania dostępna jest funkcja sortowania.
Oto mój zamysł:
Pracownicy mają otrzymać część całkowitą, odpowiadającą procentowemu udziałowi w zadaniu.
W wyniku tego można określić, ile jeszcze jednostek pozostało do podziału.
Jednostki te zostaną rozdysponowane osobom, dla których ułamkowa wartość udziału jest największa, bez względu na liczbę przepracowanych godzin.
Może się okazać, że niektóre wartości ułamkowe się powtórzą, a liczba powtórzeń będzie większa niż liczba pozostałych do rozdzielenia jednostek. W takiej sytuacji osoby, które mają taką samą wartość ułamkową, zostaną wybrane losowo, do wyczerpania pozostałych jednostek do podziału.
Tę losowość zrealizowałem następująco: Część ułamkową udziału zaokrągliłem do 6 miejsc po przecinku i dodałem do niej liczbę losową, wygenerowaną z zakresu 10 do 99, podzieloną przez 10 000 000. W ten sposób część ułamkowa, z bardzo dużym prawdopodobieństwem będzie zawierała unikatowe wartości. W przypadku gdyby wartość losowa powtórzyła się dla takiej samej 6-cyfrowej grupy w ułamku, przydział dostanie pierwsza osoba tej listy.
Przy pomocy funkcji SORTUJ() tworzone jest zestawienie osób w porządku malejącym zmodyfikowanego ułamka.
W dodatkowej kolumnie, równoległej z kolumną malejących wartości ułamka, tworzone jest zestawienie kolejnych jednostek do wydania. Ponieważ dodać można tylko jedną jednostkę, formuła wyznacza ją tylko do wartości liczby jednostek do rozdysponowania, w pozostałych wierszach pozostawiając 0.
Suma jednostek części całkowitej oraz odpowiedniej wartości do wydania tworzy ostateczna wartość.
Przykład tego podejścia załączam w pliku w arkuszu "Premia".
Arkusz ten zawiera trzy scenariusze związane z godzinami. Pierwszy scenariusz, "Godziny różne", to stan podanych godzin w "Arkusz1", drugi scenariusz, "Godziny równe", to stan godzin podanych w pliku "przykład B", trzeci scenariusz, "Godziny losowe", generuje we wszystkich komórkach rozpisanych godzin losową ich wartość z zakresu od 50 do 200. Wyboru scenariusza dokonasz z rozwijalnej listy wyboru. Możesz oczywiście wpisywać swoje własne wartości.
Na uwagę zasługuje jeszcze jedna informacja. Funkcja SORTUJ() jest funkcją macierzową i jej wynikiem jest utworzenie obszaru tej funkcji. Z jakichś powodów potrafi zawłaszczyć do swojego obszaru także kolumny przylegające do nominalnego obszaru wynikowego, jeśli zawierają one jakieś wartości, co utrudnia w następstwie dokonywanie modyfikacji. Dlatego pozostawiłem dwie puste kolumny, zaznaczając je kolorem,

[Jermor]

Jeszcze raz spojrzałem na przedstawione powyżej rozwiązanie. Jego niedogodnością jest to, że formuły macierzowe generują niemodyfikowalny obszar wyników. Niemodyfikowalny w tym sensie, że jeśli zechcemy dodać nowych pracowników (lub usunąć zwolnionych), czyli zmienimy zakres sortowania, to — pomimo zmodyfikowania adresu obszaru w formule — musimy ponownie utworzyć tę formułę, wskazując jej nowy, zmieniony zakres.
Wykorzystując zaproponowany przeze mnie sposób rozliczenia premii, opracowałem nowy plik. Pozwala on na zmienianie obszaru pracowniczego oraz kopiowanie formuł do nowych zakresów.
W tym skoroszycie obszarowi danych pracowniczych przypisałem nazwy:

• "osoby", jest to obszar sortowanych danych.
• "godziny", obszar wykazu przepracowanych godzin.
• "reszta", obszar zawierający zmodyfikowaną resztę z dzielenia.

Nazwy te zostały użyte w wykorzystanej funkcji SORTUJ.WEDŁUG(). Sortowanie realizowane jest wg dwóch kluczy: "reszta" - malejąco i "godziny" - także malejąco. Dzięki temu, gdyby dwie "reszty" były takie same wyżej sytuowana będzie osoba, która przepracowała więcej godzin.
Występują w nim także trzy scenariusze tak jak w skoroszycie dodanym w poprzednim poście.
Przy pomocy formatowania warunkowego wskazywane są wartości do wypłaty, które zostały zmodyfikowane.

[jozo2009]

Dzięki - fantastyczne rozwiązanie!