prośba o pomoc w dobraniu formuły na dopasowanie wartościi

Użytkowanie arkusza kalkulacyjnego
michal.st
Posty: 4
Rejestracja: pn kwie 11, 2022 8:03 pm

prośba o pomoc w dobraniu formuły na dopasowanie wartościi

Post autor: michal.st »

Witam Wszystkich.
Mam prośbę o wskazanie drogi jaką formułę zastosować komórce C3; C4; C5; C6 (pola zaznaczone na czerwono) żeby uzyskać taki efekt jak w kolumnie D.
Przykład
Jeżeli komórka B3 jest pusta to przeszukaj kolumnę A biorąc pod uwagę wartości z listy z kolumny G i wstaw odpowiednią wartość.
Jeżeli nie jest pusta to wstaw wartość z komórki B3

komórka A3 zawiera tekst 'MC PIO PŁATNOŚĆ KARTĄ 2345'; komórka B3 jest pusta to w komórkę C3 wstaw tekst z listy z kolumny G 'PŁATNOŚĆ KARTĄ'.

w załączniku arkusz z danymi
Załączniki
przykład forum.ods
(9.92 KiB) Pobrany 75 razy
PrzechwytywanieForumOpenOffice.JPG
Apache OpenOffice 4.1.11 na Windows 10
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc

Post autor: Jermor »

Oto moja propozycja:
Proponuję do każdego opisu operacji dodać kod oraz słowo kluczowe. Słowo kluczowe ma uprościć szukanie, czego dotyczy opis umieszczony w kolumnie A.
W załączonym pliku umieściłem takie słowa. Ponadto opis operacji uzupełniłem o pozycję "Błąd w opisie" sygnalizujący, że wprowadzony w kolumnie A opis operacji nie zawiera żadnego słowa kluczowego.
W kolumnie C znajduje się formuła macierzowa. W komórce C2 należy wpisać tę formułę i zatwierdzić ją skrótem klawiaturowym CTRL+SHIFT+ENTER. Tę formułę należy przekopiować do komórek C3, C4, C5 itd. ale nie można tego zrobić metodą zwykłego przeciągania formuły, gdyż ten sposób nie działa z formułami macierzowymi. Sposób z przeciąganiem wymaga równoczesnego przytrzymania klawisza CTRL. Bez kłopotów działa zwykłe kopiowanie, czyli CTRL+C na komórce C2 i po zaznaczeniu komórek C3...Cileś CTRL+V.
Teraz o formule:

Kod: Zaznacz cały

=JEŻELI(B2="";WYSZUKAJ.PIONOWO(SUMA(NIE(CZY.BŁĄD(ZNAJDŹ($K$2:$K$7;A2)))*$G$2:$G$7);$G$2:$H$7;2);B2)
Funkcja JEŻELI() decyduje, czy ma zostać wpisana wartość z komórki B2 (jeśli nie jest ona pusta), czy ma zostać wpisany tekst opisu.
Funkcja WYSZUKAJ.PIONOWO() pobiera treść opisu operacji z zakresu komórek G2:H7 z drugiej kolumny tego obszaru, na podstawie indeksu wyliczonego funkcją SUMA().
Metoda polega na sprawdzeniu, czy w komórce A2 znajduje się któreś ze słów kluczowych znajdujących się w obszarze K2:K7. Funkcja ZNAJDŹ() zwraca albo numer pozycji, na której słowo kluczowe zostało znalezione albo generuje błąd, jeśli słowo nie zostało znalezione. Dlatego konstrukcja NIE(CZY.BŁĄD()) ma przekształcić wynik funkcji ZNAJDŹ() w wartości 0 (gdy słowo nie zostało znalezione) lub 1 (gdy słowo wystąpiło). Każdy z tych wyników jest mnożony przez kod opisu. To dla tej konstrukcji potrzebna jest formuła macierzowa. Wytworzy ona wewnętrzny wektor składający się z tylu elementów, ile jest słów kluczowych, przy czym tylko element odpowiadający słowu kluczowemu będzie zawierał kod opisu a pozostałe będą miały wartość 0. Aby wynik nie wytworzył wektora wynikowego, użyta została funkcja SUMA(), która zwraca jedną liczbę, czyli kod opisu. Ten kod jest pierwszym argumentem funkcji: WYSZUKAJ.PIONOWO().
Oczywiście w jednym opisie wpisywanym do kolumny A nie mogą wystąpić dwa słowa kluczowe. W załączeniu plik z tym rozwiązaniem.
Załączniki
michal.st.ods
(159.43 KiB) Pobrany 80 razy
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc

Post autor: Rafkus »

Byłem pod wrażeniem prostoty formuły przedstawionej w tym poście: Sczytywanie ostatniej wartości z kolumny i z ciekawości sprawdziłem, czy można ją zastosować do tego problemu. I wyszło że można:

Kod: Zaznacz cały

=JEŻELI(B2=""; WYSZUKAJ(2; 1/CZY.LICZBA(ZNAJDŹ($K$2:$K$7; A2)); H$2:H$7); B2)
Przy czym:
  • tworząc formułę oparłem się na pliku dostarczonym przez @Jermora;
  • Podobnie jak @Jermor sugeruję stworzyć tabelę pomocniczą zawierającą kod (poszukiwane słowo kluczowe - kolumna $K$2:$K$7) oraz Opis (czyli zwracany wynik kolumna H$2:H$7);
  • poszukiwanym wyrażeniem dla otrzymania wyniku: "Błąd w opisie" jest spacja, a prezentowana formuła będzie zwracać z "uporządkowanej" rosnąco listy wartość najbliższą 2, dlatego w tym przypadku wynik ten musi znajdować się na pierwszym miejscu w tabeli pomocniczej.
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc

Post autor: Jermor »

Jest jednak problem kolego @Rafkus. Taki zapis nie działa w Apache OpenOffice, a takim posługuje się @michal.st. W tym ostatnim nie ignoruje wyników #DZIEL/0!
Stąd wniosek, że WYSZUKAJ() inaczej działa w Libre a inaczej w AOO. To kolejny kamyczek do rozbieżności (niestety) między projektami.
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
michal.st
Posty: 4
Rejestracja: pn kwie 11, 2022 8:03 pm

Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc

Post autor: michal.st »

Kolego @Jermor bardzo Tobie dziękuję za szybką reakcję i pomoc :-)
Aż trudno uwierzyć, że tak krótki i prosty kod rozwiąże mój problem ;-)
Ale już zabieram się za wdrażanie go do mojego arkusza.
@Rafkus także dziękuję za zaangażowanie :-)
Apache OpenOffice 4.1.11 na Windows 10
ODPOWIEDZ