Automatyzacja: Regex, formuły tablicowe itd.

[JedenGość]

Cześć wszystkim.

Swego czasu poprosiłem o pomoc w rozwiązaniu pewnego zadania. Otrzymałem tu szeroką pomoc, a rozwiązanie Jermora okazało się bardzo, ale to bardzo dobre. Z powodzeniem stosuję je do dzisiaj. Teraz potrzebuję nieco je zmodyfikować. O ile inne, drobne modyfikacje przeprowadzałem sam, to jednak ta mnie przerasta...

W załączniku zamieszczam plik z wyjaśnieniem, o co mi chodzi. Starałem się nie pisać rozwlekle, abym nie zaciemnił tematu. Jednak w każdej chwili doprecyzuję, cokolwiek będzie tego wymagało.

Z góry dziękuję!

[Jermor]

Chyba jestem już za stary, aby załapać, co należy zrobić w twoim zadaniu. Przede wszystkim odciąłbym się od odwołania do analogii z poprzedniego zagadnienia. Ktoś nowy musi spokojnie przeczytać, jaki jest problem i nie sugerować się innymi rozwiązaniami. A moim zdaniem jest on słabo opisany.
Przede wszystkim nie piszesz gdzie są dane źródłowe a gdzie mają powstać wyniki. Czyli, czy na podstawie arkusza "1" ma powstać arkusz "2", czy przeciwnie dane w arkuszu "1" są pobierane z arkusza "2"?
Bo z jednej strony chcesz, aby w arkuszu "2" pojawiły się właściwe liczby, czyli na podstawie danych z arkusza "1", z drugiej strony chcesz, aby w arkuszu "1" pojawiły się liczby pobierane z arkusza "2".

Piszesz:

Drugi problem to taki, aby na podstawie arkusza 2 Calc podstawiał w kolumnach C i F tego arkusza wartości z 2: data wcześniejsza niż bieżąca.

W arkuszu "2" kolumny C i F są już zajęte więc gdzie i co ma podstawiać? Ponadto wygląda na to, że chodzi chyba o kolumny C i E w arkuszu "1", bo to sugeruje zamieszczony przykład. A zapisu "... z 2: data wcześniejsza..." zupełnie nie rozumiem. Pamiętajmy, że pod pojęciem daty bieżącej rozumiemy datę tego dnia, który aktualnie biegnie (trwa).
Jeśli to arkusz "1" ma być arkuszem wynikowym, to co oznaczają dane umieszczone w kolumnach F i G?

Teraz co do cząstkowego rozwiązania:
Zakładając, że w arkuszu "1" chcesz przypisać przy wskazanej dacie liczbę odpowiadającą symbolowi, lecz pobraną z arkusza "2" z kolumny poprzedzającej tę datę, zastosowałem takie rozwiązanie:
Utworzyłem dodatkową kolumnę z datą poprzedzającą pierwszy zarejestrowany wpis. W ten sposób zapewniłem rozwiązanie sytuacji dla symboli przypisanych do pierwszej daty, one też mają poprzedzający wpis.
Obszarowi z symbolami (w arkuszu "2") nadałem nazwę "boczek" a obszarowi zawierającemu daty (także w arkuszu "2") nadałem nazwę "główka". Natomiast zakresowi tej tabeli nadałem nazwę "tabela". W arkuszu "1" do kolumn C i E wstawiłem odpowiednie formuły i przypisałem własne formatowanie.

[JedenGość]

Dobrze Cię widzieć, Jermor

Odwołąłem się do Twojego rozwiązania, bo tak jak pisałem: jest bardzo dobre i cały czas je stosuję. Gdyby teraz miało powstać coś zupełnie innego, to nie zrezygnowałbym z dotychczasowego, tylko z nowego.

Arkusz 3 i 4 to Twoje arkusze sprzed kilkunastu miesięcy.
W 4 w kolumnie B są złączone teksty z kolumny D w 3. Na tej podstawie w kolumnach E-K w 4 są generowane wyniki. To Twój pomysł. Bardzo dobry zresztą, jak już wspomniałem...

Chciałbym stworzyć formułę generującą to, co widać w 2 a bazującą na Twoim powyższym rozwiązaniu. Teksty łączone na bazie kolumny G w 1.

Arkusze 1 i 2 są powiązanie. Mają pobierać od siebie dane nawzajem, ale po kolei. Wszystko zgodnie z datami. Najpierw z 1 do 2, a później z 2 do 1.

Np. 2022-10-14. W 1 pojawiają się liczby z 2 (2022-10-13). W 2 pod 2022-10-14 pojawiają się liczby z 2022-10-14. Arkusz może być z bieżącymi datami i systematycznie uzupełniany, wtedy też tabela będzie rosła.


Ci F tego arkusza. Literówka. Przepraszam. Oczywiście C i E. Ale ten problem, jak widzę już rozwiązałeś. Dziękuję. Muszę tylko "odkodować" nazwy zakresów, ponieważ lepiej się czuję mając do czynienia ze standardowym nazewnictwem.

[Jan_J]

Chyba jestem już za stary, aby załapać, co należy zrobić w twoim zadaniu.

Ja milczałem, bo po (dość pobieżnej, przyznaję) lekturze nie zrozumiałem o co chodzi.

[Jermor]

Chciałbym stworzyć formułę generującą to, co widać w 2

nie wiem tylko, w jaki sposób miałoby się to odbyć. Przyglądając się wpisom doszedłem do następujących wniosków:

• Główka zestawienia zawiera wszystkie pojedyncze wystąpienia zarejestrowanych dat w arkuszu "1". Daty będą przybywać (?).
• Ilość wierszy wynika z ilości rejestrowanych symboli, obecnie jest ich 32.
• Zapisy w wierszach dla pojedynczego symbolu zaczynają się pod datą, przy której symbol pojawił się po raz pierwszy.
• Uwzględnia się sekwencyjnie symbole występujące w kolumnach B i D w arkuszu "1".
• Dla konkretnej daty symbol może wystąpić tylko raz (w kolumnie B lub D).
• Wpisywanymi wartościami jest numer porządkowy wystąpienia symbolu.
• Jeśli w dacie występującej w główce symbol nie wystąpił, powtarza się poprzedzający zapis.
• Numery porządkowe zaczynają się od wartości ujemnych albo dodatnich (nie wiem, z czego to.wynika).
• Po osiągnięciu jakiegoś progu (daty?), numery porządkowe podawane są od tego miejsca od nowa, lecz ze znakiem przeciwnym niż te podawane przed tym progiem. (Co o tym decyduje?)

[JedenGość]

Już śpieszę z odpowiedzią.

Ad 1. Tak, dat będzie przybywać.
Ad 2. Dokładnie tak. Symboli nie będzie przybywać.
Ad 3. Tak.
Ad 4. Tak.
Ad 5. Tak.
Ad 6. Nie. Tu jest właśnie nawiązanie do Twojego rozwiązania zawartego w arkuszach 3 i 4. W arkuszu 1 kolumna G zawiera 0 i 1. Formułą z kolumny B w 4 łączy 0 i 1 z kolumny D w 3 dla określonego symbolu znajdującego się w kolumnach A i B w 3.
I teraz dla symbolu Q (kolumny B i D) w arkuszu 1 taki ciąg to będzie:
000010000001000001101100010101010010000000000000.
Formuły z E-K w 4 tworzą z tego następujące zestawienie:
-4 1 -6 1 -5 2 -1 2 -3 1 -1 1 -1 1 -1 1 -2 1 -13
W arkuszu 2 jest dokładnie to samo tylko rozpisane po dacie. Jeśli w kolejnym dniu z kolumny A w 1 dany symbol nie pojawił się, to w 2 pod tą datą przepisywana jest wartość z poprzedniego dnia. Itd.
Ad 7. Tak.
Ad 8. Opisałem wyżej.
Ad 9. Jak wyżej.

Jak dalej jest coś niejasnego, to daj znać.

Pozdrawiam.

[JedenGość]

Jermor, nie masz pomysłu? Czy może moje wyjaśnienia dalej są zbyt zawiłe?

[Jermor]

Tak naprawdę, to nie mam.

1. Nagłówek tabeli. Ma zawierać pojedyncze wystąpienia każdej daty. Daty będą przybywały. Obecnie zarejestrowanych jest 776 pozycji o 105 różnych datach.
2. Wygenerowanie ciągu zero-jedynkowego jest możliwe, tylko że ten zademonstrowany powyżej ciąg dla symbolu Q ma długość 48 znaków i z niego nie wynika gdzie ma się zacząć ani gdzie się skończyć oraz jakie przerwy występują pomiędzy tymi wystąpieniami. Mówi tylko o tym, że wśród tych 105 dat przy 48 występuje ten symbol.
3. Na tej samej zasadzie można także wygenerować ciąg dat, przy których symbol jest umieszczony.
4. Wspomniane przez ciebie obliczenia (te wcześniejsze) miały tylko odpowiedzieć na pytanie ile jest kolejnych zer lub jedynek. Teraz kolejne zera (jedynki) mają być zliczane i ich kolejne wystąpienie ma pojawić się pod właściwą datą, przy czym, jeśli kolejna data nie ma przyporządkowanego symbolu, to należy powtórzyć ostatnią wartość, jaka temu symbolowi była przyporządkowana.

O ile wytworzenie unikatowego nagłówka jest do zrobienia, chodzi mi o sytuację rozciągającej się osi czasowej, dla której stosowanie funkcji opracowanej przez @Rafkus może stanowić problem, to zrealizowanie punktu 4. nie wydaje mi się możliwe do zrealizowania metodami samego arkusza.
W zwrotnym pliku włączyłem arkusz o nazwie "Ciągi", w którym po wybraniu symbolu można zobaczyć odpowiadające symbolowi ciągi zero-jedynkowe i dat.
Ponadto zmodyfikowałem arkusz "1" dodając pomocniczą kolumnę na początku, która posłużyła do odnalezienia wszystkich unikatowych wystąpień daty. Została wykorzystana do wytworzenia wiersza z tymi datami w arkuszu "Ciągi".

[JedenGość]

To nadal nie rozwiązuje problemu. Będę to robił ręcznie, co zajmuje sporo czasu, ale nie mam wyjścia.

Tak, czy inaczej - Jermor, dziękuję za to, co zrobiłeś.

[Jermor]

Chyba znalazłem rozwiązanie. Jest niestety rozbudowane.
Nie mogę go wysłać na forum, bo dostaję info, że plik jest za duży. Przesyłam zatem wersję okrojoną a ty sam musisz ją uzupełnić.
Arkusz "1" został rozbudowany o dodatkową kolumnę zawierającą połączenie daty z symbolami literowymi przypisanymi do tej daty. Służy ona odnalezieniu położenia symbolu przy konkretnej dacie.
Rozwiązanie utworzenia tabeli zgodnie z wzorem w arkuszu "2" znajduje się w arkuszu "Ciągi". Każdy wiersz opisujący symbol, ma poprzedzający wiersz pomocniczy, którego zadaniem jest podanie, pod każdą datą, położenia tego symbolu w rejestrze zapisów. Formuła, szukając właściwej pozycji używa obszaru rozpoczynającego się o jeden wiersz wcześniej, dzięki temu żaden symbol występujący w rejestrze nie może otrzymać pozycji nr 1.
Jeśli formuła nie może określić położenia symbolu (bo przy tej dacie on nie występuje) przypisuje mu pozycję nr 1.
Oprócz podania pozycji w rejestrze formuła wyznacza także znak liczby, jaki ma być przypisany temu symbolowi (na podstawie kolumny H w arkuszu "1"). Wartość ujemna oznacza, że symbol ma być traktowany jako -1 a dodatnia, że jako +1.
Właściwy wiersz wynikowy zawiera formułę WARUNKI(), która realizuje następujące działania:
Gdy wiersz pomocniczy zawiera 1, przenosi zawartość z komórki poprzedzającej (lewej).
Gdy wiersz pomocniczy jest > 1 a komórka poprzedzająca <0, wynikiem jest 1 (zacznie się liczenie liczb dodatnich)
Gdy wiersz pomocniczy jest <0 a komórka poprzedzająca >0, wynikiem będzie -1 (zacznie się zliczanie liczb ujemnych)
W pozostałych przypadkach do komórki poprzedzającej zostanie dodany znak liczby z komórki pomocniczej. W ten sposób zostanie dodana kolejna wartość.
Aby ukończyć zadanie, musisz w arkuszu "Ciągi" w odpowiednie miejsca przekopiować wiersze pomocnicze (do żółtych komórek)
oraz wiersze wynikowe.

[Jan_J]

Nie mogę go wysłać na forum, bo dostaję info, że plik jest za duży.

Maksymalny dozwolony rozmiar załącznika to aktualnie 128 kB. Wartość ta jest konfigurowalna z poziomu panelu admina, więc mamy do niej dostęp.
W większości przypadków pliki załączników są małe; zresztą przygotowywanie małych przykładów służy wszystkim. Zwiększenie limitu sprzyjałoby zapychaniu przestrzeni serwera, co byłoby niekorzystne, a najczęściej także nieuzasadnione. Nie widzę więc przesłanek, by na trwałe zwiększać ten parametr.
W uzasadnionych przypadkach da się chwilowo rozluźnić ograniczenie. Muszę tylko dostać sygnał, że jest taka potrzeba.

[Jermor]

Szanowny @Jan_J, także jestem tego zdania. Mamy raczej pokazać metodę niż realizować całe projekty.
Sądzę, że warto byłoby informację o dopuszczalnej wielkości pliku zamieścić (a może jest?) w FAQ. Jest tam info o typach plików (choć nie są żadne wymienione) i jak znaleźć swoje załączniki.

[JedenGość]

Jermor, dokładnie o to chodziło. Przekopiowałem formuły i na pierwszy rzut oka wygląda na dokładnie to, co miało być. Posprawdzam jeszcze na wyrywki "ręcznie", ale raczej jest ok.

Z początku się nie kleiło, ale przypomniałem sobie, że muszę włączyć wyrażenia regularne. I zaskoczyło.

Co mi pozostaje powiedzieć. Dziękuję. Nie pierwszy raz okazuje się, że na tym forum są pasjonaci, którzy chętnie dzielą się wiedzą.

[Jermor]

Przygotowałem zmodernizowaną wersję. Nie wykorzystuje ona dodatkowego wiersza dla każdego symbolu.
Wymaga, aby daty w rejestrze następowały sekwencyjnie. Ponadto dla usprawnienia obliczeń kolumny symboli w arkuszu "1" umieściłem obok siebie a kolumny ze zwrotnymi wartościami pobieranymi z arkusza "Ciągi" po nich. Dodałem też w kolumnie I formuły pozwalające kontrolować czy daty w rejestrze są uporządkowane.
Utworzyłem dodatkową kolumnę w tabeli wynikowej, w której znajduje się tekstowy ciąg pomocniczy dla każdego symbolu. Wykorzystałem do tego kolumnę, z arkusza "1" w której zliczałem ile jest różnych dat. Każda unikatowa data jest kolejnym elementem nagłówka zestawienia w tabeli arkusza "Ciągi". Jeśli symbol występuje pod jakąś datą, to ciąg zawiera trzycyfrowy numer kolejny daty w rejestrze oraz czterocyfrowy numer wiersza. Ten sposób pozwoli zarejestrować 999 unikatowych dat dla maksymalnie 9999 zapisów. Pojedynczy element zakończony jest znakiem #. Te wygenerowane ciągi tekstowe zawiera kolumna B w arkuszu "Ciągi". Do wypełnienia tabeli wykorzystałem ten sam mechanizm co w poprzedniej wersji, czyli funkcję WARUNKI().
Zasada jest następująca: każda komórka tabeli w wierszu danego symbolu

1. Sprawdza, czy pojawił się ośmioznakowy ciąg tekstowy zawierające trzy pierwsze cyfry odpowiadające numerowi kolumny w tabeli. Jeśli go nie ma (ta część formuły zwraca wówczas błąd) wstawiana jest wartość z poprzedzającej komórki w wierszu.
2. Druga część wybiera z ciągu czterocyfrową liczbę (jest to numer wiersza w rejestrze) i używa jej jako numeru wskaźnika do pobrania z kolumny G informacji czy zawiera ona liczbę większą od zera i czy jednocześnie w komórce poprzedzającej liczba jest ujemna. W takiej sytuacji wstawiana jest wartość 1.
3. Trzecia część działa odwrotnie, sprawdza, czy w kolumnie G jest liczba ujemna (tam w rzeczywistości nigdy nie ma wpisanej liczby ujemnej, komórka jest pusta, dlatego do sprawdzenia wartość w kolumnie G jest pomniejszana o 0,2) a w komórce poprzedzającej liczba dodatnia. W tej sytuacji wstawiana jest wartość -1.
4. Czwarta część działa dla wszystkich pozostałych warunków i umieszcza wynik dodawania zawartości komórki poprzedzającej i wyniku funkcji ZNAK.LICZBY() użytej wobec pomniejszonej liczby z kolumny G. Funkcja ZNAK.LICZBY() zwraca wartość -1 dla liczb ujemnych i 1 dla dodatnich.

Tak utworzony arkusz ma o połowę mniejszą wielkość pamięci. Ale i tak muszę go załączyć w wersji mini.
W arkuszu "Ciągi" są już wyliczone ciągi tekstowe dla wszystkich symboli i wypełniony formułami pierwszy wiersz (dla symbolu A). Wystarczy go skopiować w dół, do pozostałych symboli.

[JedenGość]

Dziękuję @Jermor.

Wczoraj napracowałem się nad implementacją poprzedniej wersji, tak że chyba przy niej pozostanę.

Oczywiście przyjrzę się również tej nowej i jeśli się okaże lepsza, to przejdę na nią.

Jeszcze raz dziękuję i pozdrawiam.