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
prośba o pomoc w dobraniu formuły na dopasowanie wartościi
prośba o pomoc w dobraniu formuły na dopasowanie wartościi
- Załączniki
-
- przykład forum.ods
- (9.92 KiB) Pobrany 75 razy
Apache OpenOffice 4.1.11 na Windows 10
Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc
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:
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.
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 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.
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.
Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc
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:
Przy czym:
Kod: Zaznacz cały
=JEŻELI(B2=""; WYSZUKAJ(2; 1/CZY.LICZBA(ZNAJDŹ($K$2:$K$7; A2)); H$2:H$7); B2)
- 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
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc
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.
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.
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.
Re: prośba o pomoc w dobraniu formuły na dopasowanie wartośc
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
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