Forum społeczności użytkowników OpenOffice i LibreOffice

Cześć, przychodzę z takim pytanie i prośbą o pomoc.

Czy w calc da radę stworzyć arkusz który w wyznaczonych polach będzie losowo wypełniał się podanymi danymi (w tym przypadku konkretnymi numerami pokoi)??

Cel jest taki żeby zautomatyzować i przyśpieszyć tworzenie grafiku zajęć sportowych dla poszczególnych pokoi. Grafik ma być tworzony na miesiąc i w każdym tygodniu pokój może wyjść tylko raz na zajęcia. Ponad to w kolejnych tygodniach wyjścia dla każdego pokoju muszą nastąpić w innych dniach i godzinach. W każdym z dni tygodnia jest kilka pól w które nie może być wpisany żaden pokój. Opis może być skomplikowany ale załączam plik w którym do tej pory rozpisywałem to na piechotę co zajmowało sporo czasu. Calc to potężne narzędzie a z Waszą pomocą może uda się to jakoś uprościć i przyśpieszyć.

Z góry dzięki za pomoc.

Sądzę, że udało mi się (przynajmniej częściowo) rozwiązać twój problem. Ze względu na wykorzystanie nowych funkcji programu Calc skorzystać należy z wersji nie starszej niż 24.8 (LibreOffice).
Logika tego rozwiązania jest następująca:
Tworzę ciąg tekstowy złożony z wszystkich występujących numerów pokoi. Ponieważ jeden z numerów jest przedstawiony jako 7-znakowy ciąg (126-132) zastępuję go trzy znakowym kodem „xxx”. Ponadto, aby zapewnić jednoznaczność zakodowanych numerów, każdy z nich jest poprzedzony znakiem „#”. W ten sposób powstaje ciąg tekstowy o długości 88 znaków (22 pokoje x 4).
Losowania odbywają się na tym ciągu dla wszystkich dni w tygodniu i grup w tych dniach.
Do losowania numerów wykorzystałem funkcję LOS.ZAKR.NIEUL() [ang. RANDBETWEEN.NV()]. Ta funkcja generuje losową liczbę całkowitą z podanego zakresu i nie zmienia się ona w arkuszu, jeśli wykonywane są w nim działania na komórkach niepowiązanych.
Pierwsze losowanie w każdym tygodniu zaczyna się na wszystkich elementach ciągu głównego. Górny zakres liczby losowej wynika z długości ciągu (DŁ(ciąg)/4), więc w pierwszym wierszu losowana jest liczba z zakresu 1 do 22.
W kolejnych wierszach ciąg jest skracany poprzez usunięcie z niego uprzednio wylosowanych elementów. Dzięki temu żaden numer pokoju nie może się powtórzyć w tym samym tygodniu.
Z ciągu są także usuwane te numery pokojów, które zostały wylosowane na takiej samej pozycji w tygodniach poprzedzających.
Tutaj pojawia się problem tygodnia. Norma ISO 8601:2004 mówi, że tydzień zaczyna się od poniedziałku i kończy w niedzielę. Pierwszy dzień miesiąca może jednak wypaść w dowolnym dniu tygodnia, a to oznacza, że miesiąc może nakładać się na 4, 5 lub nawet sześć tak rozumianych tygodni. Dlatego w swoim rozwiązaniu utworzyłem generator losujący numery dla sześciu kolejnych tygodni. Znajdują się one w arkuszu „Układ”.
Arkusz „Miesiąc” (zmieniłem oryginalną nazwę), zawiera grafik, który pobiera właściwe numery z tabeli w arkuszu „Układ”. Podczas pobierania usuwane są znaki „#” oraz zamieniany jest kod „xxx” na 126-132
Pod grafikiem znajdują się dodatkowe liczby pomocnicze (można je ukryć), które wyznaczają numer tygodnia, z którego będą pobierane dane w danej kolumnie grafiku,
W obszarze grafiku zdefiniowane są dwa formatowania warunkowe. Pierwsze, w zależności od miesiąca i układu dni, koloruje te pozycje grafiku, które nie są wykorzystane.
Drugie wyświetla czerwone tło w sytuacji, kiedy pole grafiku zawiera błąd. Tak się może zdarzyć, gdyż przy działaniu na liczbach losowo generowanych może się okazać, że nie można wylosować numeru pokoju.
Aby wylosować ponownie numery, należy skorzystać ze skrótu klawiaturowego CTRL+SHIFT+F9 i ewentualnie powtarzać to do aż do uzyskania pożądanego rezultatu.
Robiłem różne podejścia do tematu, dlatego załącznikiem jest wersja 5.

Jeszcze dodatkowa uwaga związana z wykorzystaniem funkcji LOS.ZAKR.NIEUL() i tym, co mnie zaskoczyło.
Podczas ładowania pliku następuje ponowne wygenerowanie wyników wspomnianej funkcji, ale nie następuje przeliczenie formuł w arkuszu, które odwołują się do tych wyników (???). Dlatego po załadowaniu pliku, pomimo wygenerowania nowych wartości losowych dane w arkuszu "Układ" i "Miesiąc" nie zmienią się. By otrzymać kompletne, nowe wartości należy skorzystać z CTRL+SHIFT+F9 lub w arkuszu "Układ" aktywować komórkę C7 (pierwszy wynik w pierwszym tygodniu) i nacisnąć F9. Trzeba to zrobić w tej pierwszej komórce, bo wszystkie następne formuły wiążą się z tą pierwszą, a to spowoduje przeliczenie pozostałych wyników. Naciśnięcie F9 w każdej innej komórce przeliczy wartości od tej wybranej do końca wyników.

Jermor to co zrobiłeś to super sprawa - działa to dokładnie tak jak sobie wyobrażałem . Jest tylko jeden mały problem ale nie wiem czy jest do rozwiązania. Chodzi o to że na przełomie miesięcy gdzie jeden miesiąc kończy się np. na środzie i kolejny zaczyna od czwartku może się okazać z dużym prawdopodobieństwem, że w tym tygodniu pokoje się powtórzą dwa razy. Generalnie mogę to rozwiązać wpisując ręcznie ten jeden tydzień, drukować grafik i nie zapisywać zmian żeby nie stracić formuł jednak traci to troszkę ze swojej automatyzacji. Nie mniej jednak i tak zaoszczędzi mi dużo czasu.

Wielkie dzięki za to co zrobiłeś

Sądzę, że prawdopodobieństwo nie jest tak duże (w przypadku 22 pokoi wynosi 0,00002025), jednak w razie konieczności zawsze można korzystając z CTRL+SHIFT+F9 wygenerować nowy układ.
Dołączam nieco zmodyfikowany plik. Zmieniłem działanie arkusza "Miesiąc" w ten sposób, że rozdzieliłem scaloną komórkę zawierająca napis "Grafik zajęć..." i nazwę miesiąca wraz z rokiem, na dwie oddzielne scalone komórki. W górnej pozostał napis "Grafik zajęć..." a w dolnej należy wpisać nazwę miesiąca wraz z rokiem. Grafik automatycznie zmodyfikuje liczbę dni w miesiącu oraz symbole tygodni.
Ponieważ w swoim rozwiązaniu zastosowałeś własne, skrócone symbole dni tygodnia, to w arkuszu "Układ" umieściłem tabelkę tych skrótów i z niej są one pobierane do grafiku.

Jermor to co zrobiłeś to nawet więcej niż chciałem i działa lepiej niż sobie wyobrażałem. Dzięki wielkie za to.

Czy możesz mi wytłumaczyć w jaki sposób mogę ewentualnie zmienić pola w których nie może być wpisany żaden pokój. Próbowałem to jakoś sam rozgryźć ale nie mogę do tego dojść. Teraz jest ok ale nie wiem czy kiedyś nie będę potrzebował takiej zmiany.

Grafik wypełniany jest danymi z tabeli w arkuszu "Układ". W tej tabeli wyszukiwany jest układ: dzień tygodnia i nr grupy i zwracany jest element przypisany do odpowiadającej pozycji w odpowiadającym tygodniu. Zastosowana funkcja X.WYSZUKAJ() pozwala wskazać, jaki ma być wynik, gdy danej pary wartości nie ma w tabeli. Wstawiany jest wówczas pusty ciąg. W grafiku ustalone jest formatowanie warunkowe ustawiające konkretne tło (styl "Pusty") jeśli w komórce nic nie ma.
Więc, aby zmienić układ zajętych terminów, należy w tabeli w arkuszu "Układ" wpisać właściwe symbole dnia tygodnia i grupy. Kolejność ich występowania nie ma znaczenia. Obecny układ cztery poniedziałki, wtorek, cztery środy i czwartki, piątek i kolejne cztery soboty i niedziele można zmienić na inne dni tygodnia oraz przypisać im inne grupy. Zmian można dokonać tylko w obszarze istniejących wpisów.

OK już rozumiem. Teraz już wiem jak zmieniać poszczególne dni i grupy. Czy jest możliwość żeby dodać nazwijmy to pola rezerwowe które teraz nie będą wykorzystywane ale będzie je można wykorzystać w przyszłości np. w przypadku gdybym musiał rozdzielić pokoje 126 i 132 i dać im osobne grupy lub gdyby doszły kolejne pokoje??

Kiedyś spotkaliśmy się już przy innym projekcie. Napisałem ci wówczas:

... Nieuzgodnione zostały wszystkie potrzeby i teraz zaczyna się łatanina. To najgorsze co spotyka projektanta oprogramowania. Bo gdy użytkownik zaczyna dostrzegać korzyści, wtedy rodzą mu się nowe pomysły. Ale wówczas zostajemy często w czarnej d... Gdybyśmy wiedzieli od początku, jakie są kompletne wymagania, to określilibyśmy to, czego nie można zrobić i to, co jest do zrobienia, a tak jesteśmy w sytuacji lakiernika, który oddał klientowi gotowy samochód i usłyszał: "świetna robota, wie pan, niech pan wobec tego doda pod ten lakier nowy podkład".

To, co ci przesłałem, dotyczy przedstawionego przez ciebie zagadnienia, czyli rozdysponowania 22 pomieszczeń pomiędzy 6 grup w okresie 7 dni. Nawet użycie złożonego kodu pomieszczenia dotyczy tylko kodu 126-132.
W tym grafiku możesz wstawić zupełnie nowe pokoje, ale ich kody muszą być 3 znakowe, a pokoi musi być 22.

Jermor pamiętam to dobrze, a projekt który wtedy mi zrobiłeś używam do tej pory i zaoszczędza mi on dużo czasu i pomoga uniknąć błędów w mojej pracy. Czasami sam coś tam dodam więc cały czas to sobie rozwijam.

W tym przypadku tak jak napisałem wcześniej nawet sobie nie wyobrażałem że można to tak zrobić. No cóż apetyt rośnie w miarę jedzenia, początkowe założenie było takie jak napisałem i zostało zrealizowane w 200%. Nie chcę zabierać Twojego czasu i tak wiele zrobiłeś, więc nie naciskam, poza tym całkowicie rozumiem co masz na myśli. Metodą prób i błędów oraz własnej nauki z czasem może dojdę jak to zmodyfikować lub napisać to od nowa z wszystkimi założeniami, a na razie w zupełności wystarczy mi to co już mam. Wielkie dzięki za to

P.S. W przyszłości postaram się moje prośby i pytania od razu formułować przyszłościowo a nie tylko to co potrzebuję na dziś.

No więc zrobiłem ci ten rozszerzony grafik. Zrezygnowałem w nim z wierszy informujących o obiedzie i kolacji, ale rozszerzyłem go do 10 grup. W ten sposób możesz teoretycznie wyznaczyć 70 rezerwacji w tygodniu (10 grup x 7 dni). Przy każdej grupie możesz zapisać godziny ćwiczeń oraz np. miejsce. Bo 10 grup, biorąc tylko 1 godzinę ćwiczeń, oznacza 10 godzin w ciągu dnia, a to wymagałoby chyba co najmniej dwóch miejscówek, w których mogłyby odbywać się te ćwiczenia.
Zmianie uległ też arkusz "Układ". Arkusz ma włączoną ochronę i pozwala wpisać dane tylko do niechronionych komórek. Cztery pierwsze wiersze są zablokowane, aby zawsze widzieć nagłówek tabeli rezerwacji i komunikat o jej stanie.
Możesz do obszaru przeznaczonego na numery pokoi wstawić dowolne trzy znakowe symbole. Zazwyczaj stosuje się numery, ale możesz użyć dowolnych zapisów, np. A12. Pozostałością z poprzedniej wersji jest to, że jeśli wpiszesz kod pokoju "xxx" to w grafiku zostanie on zastąpiony wpisem "126-132". Nie wiedziałem, co z tym zrobić. Założyłem, że wykorzystasz co najwyżej 30 pokoi i tyle miejsca na to przeznaczyłem.
Tablica rezerwacji zawiera teraz 7 dni tygodnia po 10 pozycji dla każdego dnia. W tych pozycjach możesz wpisać symbol grupy przypisany w tym dniu. Kolejność przypisywania grup nie ma znaczenia. Dla każdego dnia tygodnia grupa może wystąpić tylko raz. Formatowanie warunkowe podświetli na czerwono te grupy, które zostaną powtórzone w obszarze jednego dnia. Tu uwaga. W wersji, którą mam (25.8.2.2) jeśli skasujesz (DELETE) powtórzone grupy, to i tak pozostanie podświetlenie na tej ostatniej. To jest chyba jakaś niedoróbka autorów, gdyż wystarczy wymusić przeliczenia albo przejść na chwilę do arkusza "Miesiąc" i powrócić do "Układ" żeby to zaznaczenie zniknęło.
W związku z tym, że każdy pokój ma wystąpić w tygodniu tylko raz, w kolumnie "Grupa" można określić rezerwację tyle razy, ile jest pokoi. Na górze arkusza wyświetlany jest komunikat o stanie rezerwacji. Rezerwacja jest kompletna, gdy ilość rezerwacji jest zgodna z ilością pokoi. Jeśli rezerwacji jest za mało, to nie wszystkie pokoje zostaną wylosowane, a jeśli jest za dużo, to pojawią się błędy, których nie można usunąć, wymuszając dodatkowe przeliczenie.

Analizując ponownie temat, stwierdziłem, że skupienie się na normie ISO wyznaczającej początek tygodnia na poniedziałek, nie ma w tym przypadku żadnego znaczenia. Wystarczy przyjąć, że pierwsze 7 dni miesiąca stanowią pierwszy septet dni, kolejne 7 dni — drugi septet itd. W ten sposób każdy miesiąc (z wyjątkiem lutego w roku nieprzestępnym) składa się z czterech pełnych okresów tygodniowych, rozpoczynających się w jednym z dni tygodnia, oraz jednego, dwóch lub trzech dni należących do piątego tygodnia.
Zmodyfikowałem zatem plik i załączam go. Nie oznacza to, że poprzednia wersja jest zła.

Jermor wielkie dzięki to jest dokładnie to czego potrzebowałem, a nawet więcej.