Wartość zależna od kilku parametrów. Kombinacje funkcji.

[JedenGość]

Głowię się nad formułą, pozwalającą uzyskać odpowiednią wartość liczbową, w zależności od kilku parametrów. W użyciu głównie JEŻELI, ale różne wariacje nie przynoszą efektu.

Dlatego zwracam się (nie pierwszy raz zresztą) do Was o pomoc w rozwiązaniu problemu.

W załączniku przesyłam plik z nakreśleniem problemu.

[Jan_J]

Twoje wartości INDEX są określane dla narastających wartości FAZA, a w ramach tej samej fazy: dla narastających wartości POZIOM. Jest to tzw. porządek leksykograficzny, czyli można powiedziać: dwuetapowy porządek liniowy.
Propozycja rozwiązania wykorzystuje funkcję VLOOKUP czyli WYSZUKAJ.PIONOWO.
Funkcja ta przeprowadza wyszukiwanie na podstawie wartości kryterium. Opcja 1 wpisana jako ostatni argument powoduje tzw. wyszukiwanie aproksymacyjne względem kryterium rosnąco uporządkowanego — w tym przypadku odpowiedzią jest wpis dotyczący największej wartości z kolumny nie przekraczającej jego bieżącej wartości. Przygotowałem więc kryterium wykszukiwania w postaci 100*FAZA + POZIOM. Będzie to działać dobrze, o ile liczba poziomów w ramach jednej fazy będzie jedno- lub dwucyfrowa.

W załączniku
* obszar j12:k19 zawiera stablicowaną postać Twojej funkcji INDEX
* kolumna G przeprowadza klasyfikację Twoich danych
* w kolumnie H znajduje się porównanie wartości wpisanych jako stałe z wyliczonymi wg formuły.

JedenGosc_20230719_mod.ods

(15.35 KiB) Pobrany 79 razy

[JedenGość]

Dziękuję @Jan_J za szybką odpowiedź. Zaznajamiam się z plikiem i widzę w kolumnie G odwołania do E. Przy czym kolumnę E również wypełniłem "ręcznie". Normalnie jej nie ma...

[Jan_J]

[...]widzę w kolumnie G odwołania do E. Przy czym kolumnę E również wypełniłem "ręcznie". Normalnie jej nie ma...

Być może, ale jedyną informacją o Fazie, jaką mogłem się kierować, jest
„4. W kolumnie E “ręcznie” wpisałem nr Fazy”.
Wydaje mi się, że trzeba zastosować schemat trochę podobny do Poziomu z kolumny D. Ale domyślanie się sposobu obliczania z samych wartosci może być mylne. Zwłaszcza, że stosujesz chyba kryterium skompletowania fazy przy poziomie 10, a jak jest przy wyższych, to nie wiadomo na pewno.

[JedenGość]

Być może, ale jedyną informacją o Fazie, jaką mogłem się kierować, jest
„4. W kolumnie E “ręcznie” wpisałem nr Fazy”.

Która faza można wyczytać z POZIOMU. Legenda w kolumnach G-I (Poziom w H).

Jednak faktycznie, zawarte w pliku informacje mogą być niewystarczające, dlatego go uzupełniłem (w polach G17-K24).

Załączam plik w uzupełnionej wersji.

[Jan_J]

Nadal nie rozumiem sposobu wyznaczania Fazy w kolumnie E.

Tabelka

Kod: Zaznacz cały

FAZA	POZIOM	INDEKS
1	1-4	1
1	5-7	2
1	>=8	3
2	1-4	4
2	5-7	5
2	>=8	6
3	>=1	7

którą przepisałem do postaci nadającej się do przeszukiwania

Kod: Zaznacz cały

100*faza+poziom	indeks
100	1
105	2
108	3
200	4
205	5
208	6
300	7

opisuje zależność wartości Indeksu od wartości pary (Faza, Poziom). Moja formuła odtwarza tę zależność.

Ale od czego zależy Faza? Ja przyjąłem, że została opisana przez eksperta jako część danych wejściowych. Bo nie rozumiem sposobu jej wyznaczania.

Na przykład
* w wierszu 11. koniec serii w fazie 1 osiąga poziom 10. W następnym wierszu rozpoczyna się faza 2. Dlaczego?
* w wierszu 12. koniec serii w fazie 2 osiąga poziom 1. W następnym wierszu rozpoczyna się faza 1. Dlaczego?
* w wierszu 22. koniec serii w fazie 2 osiąga poziom 2. W nastepnym wierszu rozpoczyna się faza 1. Dlaczego?

[JedenGość]

* w wierszu 11. koniec serii w fazie 1 osiąga poziom 10. W następnym wierszu rozpoczyna się faza 2. Dlaczego?
* w wierszu 12. koniec serii w fazie 2 osiąga poziom 1. W następnym wierszu rozpoczyna się faza 1. Dlaczego?
* w wierszu 22. koniec serii w fazie 2 osiąga poziom 2. W nastepnym wierszu rozpoczyna się faza 1. Dlaczego?

Wiersz 12.
D12:

Kod: Zaznacz cały

JEŻELI($C11=1;1;D11+1)

W tym przypadku w wierszu 11 mamy POZIOM 10, czyli >=8. W wierszu 12, gdzie jest POZIOM 1, zaczyna się FAZA 2 i INDEKS 4, zgodnie z legendą J20-K20.

Wiersz 22.
J.W., tym razem zgodnie z legendą J21-K21.
Jeżeli mamy POZIOM 1, w wierszu poprzedzającym była FAZA 2 i POZIOM 2 to zaczynamy od FAZA 1 i INDEKS 1.

[Jan_J]

Ok, czyli bieżąca faza i poziom z G18:H23 (albo Faza i Indeks z G18:I24) wyznaczają nową fazę i nowy indeks w przypadku rozpoczęcia nowej serii.

Załączam modyfikację

JedenGosc_20230721_mod.ods

(21 lipca 2023: poprawiony błąd w tabeli)

(18.64 KiB) Pobrany 71 razy

* W kolumnach J:K zostawiłem Twoje dane; w kolumnach H:I sprawdzam zgodność formuł z Twoimi danymi. Wszystko to można wyrzucić.
* Twoją tabelę M2:O9 można wyrzucić. Jej rolę przejmuje M12:P19.
* Tabela z danymi M13:P19 do interpolacji zyskała nazwę TABELA_FAZ; łatwiej zarządzać nazwanym obszarem niż adresem blokowym.
* Nie ma zabezpieczenia przed przekroczeniem dozwolonego numeru poziomu w serii. Interpolacja będzie działać, o ile będzie to numer jedno- albo dwucyfrowy.

[JedenGość]

Działa! Wykonałem kilka testów i jak na tę chwilę, wszystko pięknie działa!

@Jan_J - DZIĘKUJĘ!

[JedenGość]

Jednak nie. Jest błąd.

Formuła w kolumnie E odnosi się do kolumny J ze stałymi wartościami Fazy.

Właśnie o to się rozchodzi, żebym nie musiał wypisywać faz ręcznie. Teraz to napisałem dla zobrazowania tematu.

Gdy z formuły usunę JEŻELI i odwołanie do kolumny J, to wtedy pojawiają się błędy.

Reasumując.
Widzimy tylko POZIOM. Teraz chodzi o zbudowanie formuł, które obliczą FAZĘ i INDEKS.

[Jan_J]

Tak rzeczywiście. Obliczanie numeru Fazy pobiera poprzednią wartość Fazy z kolumny J zamiast z E. Ponieważ wartości są takie same, nie jest to wielki problem. Podmieniłem formułę w załączniku ↑

[JedenGość]

To faktycznie, nie był wielki problem, ale mnie przerósł. Dziękuję!

Zastanawia mnie jeszcze kwestia WYSZUKAJ.PIONOWO.

Np.

Kod: Zaznacz cały

WYSZUKAJ.PIONOWO(100*E38+D38;TABELA_FAZ; 3; 1)

Będzie: 100*1+9=109

W TABELA_FAZ jest 108 i następne 200. Jak wyszuka 109? Przypisze 109 do pierwszej niższej w tabeli?

[Jan_J]

W TABELA_FAZ jest 108 i następne 200. Jak wyszuka 109? Przypisze 109 do pierwszej niższej w tabeli?

Do ostatniej nie wyższej niż poszukiwana. Pod warunkiem, że
* wpisy w kolumnie wyszukiwanych kategorii są uporządkowane rosnąco;
* ostatni, czwarty argument funkcji nie jest podany albo wynosi 1.

Więcej: https://help.libreoffice.org/latest/pl/ ... _id3153152

[Jan_J]

Skorygowałem błąd w tabeli. Dwa ostatnie wiersze miały niepoprawne wartości kryterium.