Funkcja wyszukaj pionowo i znajdź tekst - problem

[kristoferson]

Witam,
Mam taki problem: w kolumnie A są nazwy, w B dane1, w C dane2, w D dane 3.
W kolumnie F znajduje się cześć nazwy którą chciałbym zaleźć w kolumnie A i przypisać odpowiednie dane z kolumn B,C,D do kolumn G,H,I
Próbowałem z funkcją WYSZUKAJ.PIONOWO w połączeniu z funkcją ZNAJDŹ i ZNAJDŹ.TEKST ale nie dałem rady.
W załączniku plik z pokazaniem tego co chciałbym osiągnąć.
Może ktoś ma jakiś pomysł jak to zrobić ?

Pozdrawiam
Krzysiek

[Jan_J]

Narzędzia->Opcje->Calc->Oblicz
[ ] włącz wyrażenia regularne w formułach
[ ] kryteria wyszukiwania odnoszą się do całych komórek

Formuła dla Twojego przykładu wpisz do G2

Kod: Zaznacz cały

=PRZESUNIĘCIE(A1; PODAJ.POZYCJĘ(F2; $A$2:$A$5; 0); 1; 1; 3)

i zatwierdź Ctrl+Shift+Enter.

//było G2 przepraszam

Jak to działa: podaj.pozycję() znajduje numer pierwszego takiego wiersza w kolumnie A, który zawiera Twoją frazę z F2 jako część zawartości. Wtedy przesunięcie() pobiera 3 komórki z tego właśnie wiersza. Sposób zatwierdzania wymusza użycie rozszerzonej, wektorowej składni formuł.

[b0bi]

Janie... z ciekawości postąpiłem jak opisałeś to w poście, czyli odhaczyłem podane opcje
do G2 wpisałem podaną formułę i zatwierdziłem Shift+Ctrl+Enter
i wyskakuje błąd 522 a formuła pojawia się w nawiasie {=PRZESUNIĘCIE(A1; PODAJ.POZYCJĘ(G2; $A$2:$A$5; 0); 1; 1; 3)}
Co może być nie tak?


EDIT:
trochę się pomęczyłem ale... udało się. Na bazie tego, co Jan zaproponował wykombinowałem, że w podanej przez Niego formule wkradł się błąd
w komórce G2 powinno być zatem:

Kod: Zaznacz cały

=PRZESUNIĘCIE($A$1;PODAJ.POZYCJĘ(F2;$A$2:$A$5;0);1)

w komórce H2 powinno być:

Kod: Zaznacz cały

=PRZESUNIĘCIE($A$1;PODAJ.POZYCJĘ(F2;$A$2:$A$5;0);2)

w komórce I2 powinno być:

Kod: Zaznacz cały

=PRZESUNIĘCIE($A$1;PODAJ.POZYCJĘ(F2;$A$2:$A$5;0);3)

lub w G2

Kod: Zaznacz cały

=PRZESUNIĘCIE($A$1;PODAJ.POZYCJĘ(F2;$A$2:$A$5;0);1;1;3)

zatwierdzone przez CTRL+SHIFT+ENTER
Oczywiście bez Jana by się nie obyło. Pozdrawiam

Przy okazji... dlaczego funkcja

Kod: Zaznacz cały

=PODAJ.POZYCJĘ(F2;$A$2:$A$5;0)

po odznaczeniu, że wyszukiwanie dotyczy całych komórek zwraca wynik (w tym przypadku 2)
natomiast wpisując formułę wyszukaj.pionowo

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(F2;$A$2:$A$5;2;0)

zwraca błąd (w tym przypadku błąd:502)?? czyli w tym przypadku jakby wyszukiwanie częściowe nie działało? Jaka jest różnica między tymi funkcjami, dlaczego w pierwszym przypadku przeszukiwanie częściowe działa, a w drugim nie?

Jeszcze jedno: Dlaczego kopiowanie przez "przeciągnięcie" takiego zatwierdzenia CTRL+SHIFT+ENTER - czyli macierzy, nie działa, a jedynie CTRL+C, CTRL+V?

[Jan_J]

lub w G2

Kod: Zaznacz cały

=PRZESUNIĘCIE($A$1;PODAJ.POZYCJĘ(F2;$A$2:$A$5;0);1;1;3)

Oczywiście; sorry, testowałem w przesuniętym bloku

Przy okazji... dlaczego funkcja

Kod: Zaznacz cały

=PODAJ.POZYCJĘ(F2;$A$2:$A$5;0)

po odznaczeniu, że wyszukiwanie dotyczy całych komórek zwraca wynik (w tym przypadku 2) natomiast wpisując formułę wyszukaj.pionowo

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(F2;$A$2:$A$5;2;0)

zwraca błąd (w tym przypadku błąd:502)?? czyli w tym przypadku jakby wyszukiwanie częściowe nie działało? Jaka jest różnica między tymi funkcjami, dlaczego w pierwszym przypadku przeszukiwanie częściowe działa, a w drugim nie?

Działa w obu, tylko przy wyszukaj() zadałeś obszar A:A w którym nie ma drugiej kolumny, żeby z niej zwrócić wynik. Ale radzę używać schematu z przesunięcie, bo wtedy wyszukuje się tylko raz, a nie osobno dla każdego wpisu.

Dlaczego kopiowanie przez "przeciągnięcie" takiego zatwierdzenia CTRL+SHIFT+ENTER - czyli macierzy, nie działa, a jedynie CTRL+C, CTRL+V?

Tak od zawsze mają formuły wektorowe. Ale tzw. szybkie wypełnienie (dwuklik na narożniku bloku) powinno działać.

[b0bi]

(Dlaczego)... wpisując formułę wyszukaj.pionowo

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(F2;$A$2:$A$5;2;0)

zwraca błąd... czyli w tym przypadku jakby wyszukiwanie częściowe nie działało?

Działa w obu, tylko przy wyszukaj() zadałeś obszar A:A w którym nie ma drugiej kolumny, żeby z niej zwrócić wynik.

No tak... przy tym kombinowaniu... zapędziłem się w ślepy róg i rzeczywiście nie zauważyłem, że zmieniłem obszar wskazując tylko na pierwszą kolumnę.
Dzięki i pozdrawiam.

[kristoferson]

Wielki dzięki,
wszystko działa jak trzeba.

Pozdrawiam
Krzysiek

[bobim]

Mam podobny problem otóż, mam kolumnę z danymi, które w nazwach zawierają poszukiwane przeze mnie fragmenty. Kolejne dwie lub więcej kolumn to właśnie te fragmenty. Chcę przeszukać pierwszą kolumnę a gdy znajdzie fragment z drugiej lub trzeciej kolumny żeby go wyświetlić. Jeśli znajdzie oba to wyświetla pierwszy lub wcale. Problem w tym, że pierwsza kolumna może mieć np. 10tys pozycji a te z fragmentami tekstu różnie. Pierwsza np. 1000 a druga 50. Może ktoś podpowie jak to można ugryźć? Próbowałem opisami z powyższych postów ale nic mi nie wychodzi ;(.

[belstar]

W pliku który wstawiłeś, dane przykładowe są nieco mylące.

1. Czy wyraz cosik jest odpowiednikiem realnych danych i występuje we wszystkich wierszach?
2. Czy poszukiwane parametry występują zawsze na końcu?
3. Czy przed parametrami jest stała liczba liter w nazwie?

Wstawiłem ci plik, który może trochę zbliży cię do rozwiązania.

[bobim]

Czy wyraz cosik jest odpowiednikiem realnych danych i występuje we wszystkich wierszach?

Nie. Dane mają być różne i rzadko do siebie podobne, chociaż mogą się powtarzać.

Czy poszukiwane parametry występują zawsze na końcu?

Poszukiwane parametry mogą być w tekście nie koniecznie w środku lub na końcu, natomiast nie występują na początku.

Czy przed parametrami jest stała liczba liter w nazwie?

W nazwie może być od minimum 3 znaków do aż 100.

[milkow70]

Moja funkcja WYSZUKAJ.PIONOWO() źle wyszukuje
Mianowicie mam w komórkach M27:O40 zakres danych wyszukiwania
Dane w kolumnie M27:M40 to kryterium wyszukiwania które określa się w komórce C5 zgodnie z listą rozwijaną ($'DANE WEJŚC'.$M$27:$M$40)
Dane w kolumnach N:27:N40 i O27:O40 to wyszukiwane wartości które wyświetlane są odpowiednio w komórkach C6 i C7.

C6 =WYSZUKAJ.PIONOWO(C5;M27:O40;2)
C7 =WYSZUKAJ.PIONOWO(C5;M27:O40;3)

Wynik wyszukiwania w kilku przypadkach jest błędny (w załączniku) - w kolumnach na kolorowo oznaczono odpowiednio wartości, które wyszukiwane są prawidłowo - "ok", i które błędnie (N/D lub wartość jaka jest wyświetlona po ustaleniu kryterium wyszukiwania w komórce C5)

Gdzie tkwi błąd?

[belstar]

Gdzie tkwi błąd?

Poszukaj alternatywnych rozwiązań. http://forum.openoffice.org/pl/forum/vi ... f=4&t=2071

[belstar]

Poszukiwane parametry mogą być w tekście nie koniecznie w środku lub na końcu, natomiast nie występują na początku.

Czy parametr 2 występuje zawsze po pierwszym?

[bobim]

Nie zawsze. Ale wystarczyło by mi jakbym mógł wyszukać pierwsze wystąpienie parametru a zrezygnowałbym z wyszukiwania sytuacji gdy w jednym wierszu jest więcej niż jeden. Czyli gdy funkcja znajdzie dowolny parametr jako pierwszy wypisuje go i przechodzi sprawdzać linijkę następną. Czy funkcja wyszukaj pionowo potrzebuje do prawidłowego działania dwóch kolumn o równej długości?

[milkow70]

Gdzie tkwi błąd?

Poszukaj alternatywnych rozwiązań. http://forum.openoffice.org/pl/forum/vi ... f=4&t=2071

W dalszym ciągu się haczy. Wyszukuje mi wartości z wierszy powyżej lub #N/D
Wygląda na to, że funkcje wyszukiwania mają problem z adresowaniem komórek czy cóś.

[belstar]

Nie zawsze. Ale wystarczyło by mi jakbym mógł wyszukać pierwsze wystąpienie parametru

A modyfikacja wstawionego przykładu, żeby funkcja pobierała parametry z kolejnych wierszy by nie była wystarczająca.
Dla parametru nr 1

Kod: Zaznacz cały

=INDEKS($A$2:$A$20;MIN.K(JEŻELI(CZY.BŁĄD(SZUKAJ.TEKST($C2;$A$2:$A$20;2));"";WIERSZ(A2:A20)-MIN(WIERSZ(A2:A20))+1);WIERSZ(A$1));KOLUMNA(A$1))

Dla parametru nr2

Kod: Zaznacz cały

=INDEKS($A$2:$A$20;MIN.K(JEŻELI(CZY.BŁĄD(SZUKAJ.TEKST($D2;$A$2:$A$20;2));"";WIERSZ(A2:A20)-MIN(WIERSZ(A2:A20))+1);WIERSZ(A$1));KOLUMNA(A$1))

Dla obu

Kod: Zaznacz cały

=INDEKS($A$2:$A$20;MIN.K(JEŻELI(CZY.BŁĄD(SZUKAJ.TEKST($C2;$A$2:$A$20;2)*SZUKAJ.TEKST($D2;$A$2:$A$20;2));"";WIERSZ(A2:A20)-MIN(WIERSZ(A2:A20))+1);WIERSZ(A$1));KOLUMNA(A$1))

Wszystkie tablicowe.

[bobim]

U mnie to nie działa , błąd 504. Wszędzie wyskakuje mi cosik a. Nie wiem czy walczyć z tym bo tych parametrów może być max tysiąc i czy wtedy indeks będzie miał zastosowanie?

[belstar]

U mnie to nie działa , błąd 504

Formuła tablicowa, trzeba zatwierdzić SHIFT+CTRL+ENTER
Stworzyłem plik testowy
danych = 20000
Parametr 1 = 1000
Parametr 2 = 1000
Oba =1000
Komfort pracy nie ucierpiał znacznie, zapis po zmianie formatowania komórek jest odczuwalnie dłuższy. Tutaj wstawię mniejszy o połowę ze względu na wielkość pliku.