formułka: jeżeli -> to, jeżeli -> tamto

[Druidamus]

Witam,

Próbowałem wykorzystać kilka podpowiedzi z forum odnośnie "jeżeli" w Calc LibreOffice, ale nie umiem tego posklejać by działało jak należy.
Nie wiem czy będę umiał wytłumaczyć swój problem, ale postaram się:
Chodzi mi o pewną formułkę do cennika katalogowego, do wyliczenia ceny zakupu, w której przypisane będą różne rabaty, do wyliczenia ceny zakupu. Cennik posiada kategorie rabatu (A, B, C, D, E itd) np A=10%, B=20%, C=15% oraz pusta kategorię, gdzie jest stały rabat 30% (kolumna E). Przy każdym produkcie jest cena katalogowa (np kolumna A) i kategoria rabatu (kolumna B). Obliczanie pojedynczo ponad 2tys produktów zajmie trochę czasu.

jeśli wartość w komórce B2 ="A" to wstaw stałą wartość z komórki $E$2 (X) do formuły
jeśli wartość w komórce B3 = "B" to wstaw stałą wartość z komórki $E$3 (Y) do formuły
jeśli wartość w komórce " " (nic, brak wartości, znaku, symbolu itd) to wstaw wartość z komórki $E$5 (Z) do formuły
formuła: cena zakupu = cena katalogowa - cena katalogowa *(jeśli A to X , jeśli B to Y, jeśli nic to Z)

Mógłbym to rozwiązać tez auto filtrem i sortowaniem artykułów wg kategorii rabatów, ale może formuła będzie lepszym rozwiązaniem.

[Rafkus]

Możesz użyć zagnieżdżonej formuły JEŻELI():

Kod: Zaznacz cały

=JEŻELI(B2="A"; $E$2; JEŻELI(B3="B"; $E$3; JEŻELI(...)))

W miejscu (...) kolejne formuły JEŻELI, ostatnia mogła by być taka:

Kod: Zaznacz cały

JEŻELI(Bn=""; $E$n; "Nie zdefiniowano")

gdzie n to numer odpowiedniego wiersza.
W swoim opisie problemu piszesz: B2 ="A"; B3 ="B" , wydaje mi się że tam powinna być sprawdzana wartość z konkretnej jednej komórki a więc: B2 ="A"; B2 ="B" itd.

LibreOffice ma funkcję WARUNKI( ), działającą właśnie jak zagnieżdżona funkcja JEŻELI:

Kod: Zaznacz cały

=WARUNKI(B2="A";	$E$2;
	B2="B";	$E$3;
(kolejne warunki i wyniki)
	B2="";	$E$n;
	1;	"Nie określone")

Jak działa ta formuła:
* jeśli pierwszy warunek: B2="A" jest prawdziwy to otrzymasz wartość z $E$2, jeśli nie to
** sprawdza drugi warunek: B2="B", jeśli jest prawdziwy to otrzymasz wartość z $E$3, jeśli nie to
*** (itd. sprawdzane są kolejne warunki aż do)
****sprawdza n warunek: B2="", jeśli jest prawdziwy to otrzymasz wartość z $E$n, jeśli nie to
***** 1 oznacza że warunek jest prawdziwy i w tym przypadku w wyniku otrzymasz tekst "Nie określone". Tą parę (warunek i wynik) możesz nie podawać, ale wówczas w przypadku niespełnienia którekolwiek warunku w wyniku otrzymasz błąd #N/D

[Jermor]

Wydaje mi się, że możesz to rozwiązać inaczej, chociaż każdy sposób uzyskania prawidłowego wyniku jest dobry.
Jeżeli swoje rabaty zorganizujesz w postaci tabelki opustów to możesz tę ceny obliczać w sposób pokazany na ilustracji.

obraz_2022-04-24_115058780.png (9.83 KiB) Przejrzano 7365 razy

W tym rozwiązaniu finkcja JEŻELI.BŁĄD() (dostępna tylko w LibreOffice) zwróci cenę katalogową dla każdego typu kategorii rabatowej niezgodnej z tabelką rabatów.
Przy okazji. O różnych funkcjach warunkowych możesz poczytać tutaj: https://yestok.pl/lbo/y63.php

[Rafkus]

Po odpowiednim przygotowaniu danych można także wykorzystać funkcję WYSZYKAJ():

Kod: Zaznacz cały

=WYSZUKAJ(B2;  $F$2:$F$7;  $E$2:$E$7)

gdzie:
B2 - komórka z wartością do wyszukiwania,
$F$2:$F$7 - zakres komórek, w którym będzie poszukiwana dana wartość. UWAGI: Musi być on posortowany rosnąco, do pierwszej komórki z tego zakresu (tej niby pustej) ma być wpisane: =""
$E$2:$E$7 - zakres wyników wynikowych

UWAGA: OpenOffice ma problem ze znalezieniem wartości w takiej pustej komórce, zobacz wiersz 8 na powyższym zdjęciu. Komórka B8 jest pusta i formuła z komórki C8 zwraca błąd - brak danych (w komórce B7 jest wpisane ="").

[Druidamus]

Dzięki za podpowiedzi. Sorki, że teraz odpisuję, ale byłem w szpitalu.
Wykorzystałem pomoc Refkusa i ostatecznie moja formułka "na brudno" wygląda tak

=B2-B2*(JEŻELI(C2="a";$K$1;JEŻELI(C2="b";$K$2;JEŻELI(C2="c";$K$3;(JEŻELI(C2="d";$K$4;(JEŻELI(C2="e";$K$5;(JEŻELI(C2="";$K$6))))))))))

gdzie Bn - kolumna cen katalogowych
Cn - kolumna kategorii rabatowych
Kn - kolumna z rabatami

Rabaty wymyśliłem na szybko, żeby sprawdzić czy działa.

Później przeniosę to do oryginalnego cennika i z prawdziwymi rabatami.
Jeszcze raz dzięki za pomoc.

[Rafkus]

Wybrałeś najbardziej pracochłonną, wężową formułę. Co będzie jeśli dojdzie jakiś nowy rabat? Poprawienie w przyszłości tej formuły może być nieco kłopotliwe.
Dlatego osobiście polecałbym sposób przedstawiony przez Jermora. Pozbywając się funkcji JEŻELI.BŁĄD i dostosowując ją do twojego ostatniego obrazu wystarczyła by taka formuła:

Kod: Zaznacz cały

=B2*(1- WYSZUKAJ.PIONOWO(JEŻELI(C2=""; "puste"; C2);  $J$1:$K$6; 2; 0))

W tak sformułowanej funkcji WYSZUKAJ.PIONOWO nie musisz dbać aby tabela rabatów (J1:K6) była posortowana rosnąco - w przeciwieństwie do proponowanej przeze mnie nieco wcześniej funkcji WYSZUKAJ.

[Jermor]

Tak naprawdę nie wykorzystałeś podpowiedzi @Rafkus-a.
Problem skomplikowałeś sobie przez utworzenie bardzo złożonej formuły JEŻELI(). Napisałem już wcześniej, że każdy sposób obliczenia, dający poprawny wynik jest do zaakceptowania, ale...
W twoim sposobie nie masz "łatwości" zarządzania rabatami. Gdy zechcesz dodać nową kategorię rabatów musisz przerobić całą formułę i powielić ją w całym zakresie obliczeń.
Twój wzór na cenę po rabacie, to: cena-cena*rabat, czyli po wyłączeniu stałego czynnika przed nawias: cena*(1-rabat). Zadaniem zatem jest wyodrębnienie rabatu z tabeli rabatów.
Najlepiej do tego nadają się funkcje WYSZUKAJ() i WYSZUKAJ.PIONOWO(). Funkcja WYSZUKAJ() wykorzystuje dwa jednowymiarowe wektory z danymi. Pierwszy zawiera listę szukanych elementów, drugi, który może być w innym miejscu arkusza, zawiera wartości przyporządkowane pozycjom pierwszego wektora. Każdy z tych wektorów może być inaczej zorganizowany, np. pierwszy przedstawia dane w kolumnie, a drugi odpowiadające wartości w wierszu. Obowiązującą zasadą jest to, że oba wektory muszą mieć po tyle samo elementów oraz to, że dane w pierwszym wektorze muszą być uporządkowane rosnąco. Warto poczytać sobie o działaniu tej funkcji w helpie.
Kategorie a, b, c itd. to w istocie dane ułożone narastająco. Problemem jest u ciebie kategoria "puste", czyli sytuacja, gdy w kolumnie C nic nie zostanie wpisane. Łatwo się domyślić, że pozycja "puste" powinna występować przed pierwszą kategorią ("a") tylko, że pozostawienie w tabeli rabatów pustej komórki spowoduje błąd wyniku (#N/D). Dlatego w pierwszą pozycji w tabeli rabatów (kolumna J) należy wpisać formułę ="". W efekcie kolumna J powinna zawierać wpisy ="", a, b, c, d, e. A kolumna K odpowiednie rabaty przypisane do tych kategorii.
Teraz cała formuła dla twojego zadania wyglądać będzie tak:

Kod: Zaznacz cały

=B2*(1-WYSZUKAJ(B2;$J$1:$J$6;$K$1:$K$6)

Jeśli zechcesz zwiększyć ilość kategorii rabatowych możesz powiększyć obszar tabeli rabatów bez zmian w formułach.

[Jermor]

Jeszcze uwaga, dotycząca tej złożonej konstrukcji JEŻELI().
Zamiast tego galimatiasu, możesz wykorzystać funkcję PRZEŁĄCZ() (tylko w LibreOffice), w taki sposób:

Kod: Zaznacz cały

=B2*(1-PRZEŁĄCZ(C2;"a";0,2;"b";0,1;"c";0,15;"d";0,3;"e";0,25;"";0,3))

Tu także gdy zmienią się warunki należy przerabiać formułę, ale wygląda lepiej niż JEŻELI().

[Druidamus]

Kod: Zaznacz cały

=B2*(1-WYSZUKAJ(B2;$J$1:$J$6;$K$1:$K$6)

Macie racje że dużo tego pisania było. Ale wydawało mi się najbardziej zrozumiałym dla mnie rozwiązaniem. Dlatego go użyłem
Bardzo fajny skrót, ale z błędem ;p Już go sobie naprawiłem.
No i co z wartością pustą? ""

Co do nowych kategorii, to raczej nie będzie. Ale nigdy nic nie wiadomo

[Rafkus]

No i co z wartością pustą? ""

Nieco wcześniej napisałem:

Kod: Zaznacz cały

=B2*(1- WYSZUKAJ.PIONOWO(JEŻELI(C2=""; "puste"; C2);  $J$1:$K$6; 2; 0))

Jak działa funkcja WYSZUKAJ.PIONOWO:
W tabeli rabatów $J$1:$K$6, w pierwszej kolumnie (czyli pionowo) ma znaleźć dokładną wartość (parametr 0) z komórki C2 lub jeżeli w niej nic nie ma - ma zaleźć wartość "puste" . Dla znalezionej wartości jako wynik ma być podany wynik z 2 kolumny tabeli rabatów.

Pozostając przy funkcji Wyszukaj, to można tak:

Kod: Zaznacz cały

=B2*(1-WYSZUKAJ(JEŻELI(C2=""; "puste"; C2);  $J$1:$J$6;  $K$1:$K$6)

Oczywiście w tabeli rabatów do tej pustej komórki należy wpisać słowo "puste" (tak jak masz na ostatnim swoim obrazie). Powtórzę to co pisałem wcześniej: w tej funkcji ważne jest aby kolumna w której poszukiwana jest jakaś dana (tutaj: $J$1:$J$6) była posortowana rosnąco.

[Jermor]

No i co z wartością pustą? ""

Nie rozumiem pytania.
W tabelce kategorii, w komórce J1, należy wpisać ="". Trzeba wpisać treść zaczynając od znaku =, aby Calc przyjął, że wpisujemy formułę z pustym ciągiem. Wpisanie bezpośrednio "" (czyli dwóch następujących po sobie znaków cudzysłowu) oznacza wpisanie ciągu tekstowego złożonego z tych dwóch znaków, czyli nie jest to odpowiednik pustej komórki.
Trzeba też zdawać sobie sprawę z konsekwencji utworzenia pierwszego wektora nieułożonego w kolejności narastającej. Funkcja nie sprawdza tego, czy dane są faktycznie posortowane, lecz działa tak jakby one były tak posortowane. To oznacza, że wyszukując wartość zatrzyma się na najbliższej znalezionej poniżej. Przykład. Przeszukiwany wektor ma wpisy ułożone tak: a, b, e, c, d. Wyszukiwana jest wartość "c". Funkcja po sprawdzeniu, czy to "b" stwierdza, że następna zawartość "e" jest większa od poszukiwanego "c", więc zwraca odpowiednik "b", bo to on jest położony najbliżej poniżej. Ta funkcja nie odróżni też sytuacji błędnych polegających na omyłkowym wpisaniu kategorii w postaci np. "axx" (stwierdzi, że "axx" jest mniejsze od "b", więc zwróci dane dla "a") albo "x" ("x" jest większe od ostatniego wpisu "d", więc zwróci dane odpowiadające "d").
Inaczej zadziała funkcja WYSZUKAJ.PIONOWO() jeśli jej czwartym parametrem będzie 0. Przeszukując pierwszą kolumnę, zaakceptuje dane, które będą całkowicie pasujące. Kategorie nie muszą występować w ustalonym porządku. Wartości, których nie da się dopasować zostaną zasygnalizowane jako błąd. Pozwoli to, wykorzystując funkcję JEŻELI.BŁĄD() utworzyć formułę informującą o błędnie wpisanym kodzie kategorii.

Kod: Zaznacz cały

=JEŻELI.BŁĄD(A9*(1-WYSZUKAJ.PIONOWO(B9;$G$1:$H$6;2;0));"Błąd kategorii")

W przykładzie powyżej wpisałem formułę zastosowaną w moim arkuszu. W twoim należy ją odpowiednio zmodyfikować.

[Druidamus]

Nie rozumiem pytania.

W tabeli kategorii rabatów są pozycje bez kategorii rabatowej ze stałym rabatem 30%. Nie jest to błędem kategorii, ale celowym działaniem osoby piszącej cennik.
Wpisałem różne opcje (załącznik) i wyskakuje błąd obliczenia

[Jermor]

Napisałem ci o tym, że kategorie musisz ustawić w kolejności ich kodów od najmniejszego do największego.
W tej liście kategorii "najmniejszym" kodem jest ciąg pusty. Musi on w twojej tabeli kodów zostać wpisany na pierwszym miejscu, czyli w J1 musisz wpisać ="" (znak równości i dwa cudzysłowy) i nacisnąć ENTER. Od J2 wpisujesz litery a, b, c itd. W kolumnie C pole kategorii pozostawiasz puste, jeśli takie ma być.
W ilustracjach:
Na pierwszej, kod kategorii "pusta" znajduje się na końcu listy, z punktu widzenia funkcji WYSZUKAJ() na końcu znajduje się największa wartość. Zatem gdy funkcja próbuje znaleźć na liście ciąg pusty, ta jako pierwszą pozycję znajduje literę "a". Jej kod jest większy niż ciąg pusty, więc zgodnie z tym co napisałem wcześniej, w objaśnieniu funkcji, zwrócony ma być kod pozycji najbliższej poniżej. Litera "a" jest jednak pierwszą pozycją w tym wektorze zatem nie ma pozycji niższej. Stąd wynik #N/D.
W trzecim przykładzie do J6 wpisałeś po prostu dwa cudzysłowy i one jako ciąg znakowy stały się poszukiwanym kodem rabatowym.
Rozwiązanie podsyłam.

[Druidamus]

Napisałem ci o tym, że kategorie musisz ustawić w kolejności ich kodów od najmniejszego do największego.
W tej liście kategorii "najmniejszym" kodem jest ciąg pusty. Musi on w twojej tabeli kodów zostać wpisany na pierwszym miejscu, czyli w J1 musisz wpisać ="" (znak równości i dwa cudzysłowy) i nacisnąć ENTER. Od J2 wpisujesz litery a, b, c itd. W kolumnie C pole kategorii pozostawiasz puste, jeśli takie ma być.
W ilustracjach:
Na pierwszej, kod kategorii "pusta" znajduje się na końcu listy, z punktu widzenia funkcji WYSZUKAJ() na końcu znajduje się największa wartość. Zatem gdy funkcja próbuje znaleźć na liście ciąg pusty, ta jako pierwszą pozycję znajduje literę "a". Jej kod jest większy niż ciąg pusty, więc zgodnie z tym co napisałem wcześniej, w objaśnieniu funkcji, zwrócony ma być kod pozycji najbliższej poniżej. Litera "a" jest jednak pierwszą pozycją w tym wektorze zatem nie ma pozycji niższej. Stąd wynik #N/D.
W trzecim przykładzie do J6 wpisałeś po prostu dwa cudzysłowy i one jako ciąg znakowy stały się poszukiwanym kodem rabatowym.
Rozwiązanie podsyłam.

Nie bardzo zrozumiałem o co chodzi z tym "najmniejszy największy". Pierwsze co mi przyszło do głowy to nie kategorie rabatowe, ale same procentowe rabaty. Dlatego nie ogarnąłem tego równania.

Dzięki za pomoc i plik.
Zamykam temat.