[Excel] Wyszukiwanie w wierszu


(Marcin Obala) #1

Witam

Chciałbym zrobić coś takiego

mam wiersze z tym że są one o wieeeeeeeeeele dłuższe i jest ich duuuuuuuużo dlatego ręczna transpozycja nie wchodzi w grę.

1234; B; C; D; E

999; X; Y; Z

666; S; A; T; A; N

Mam teraz w drugim arkuszu

A

B

C

D

E

F

G

Chciałbym do każdej z tych liter dopisać wartość z pierwszej kolumny w wierszu. Ma ktoś pomysł? Czy zostaje tylko VBA?


(rgabrysiak) #2

Cześć

Nie wiem czy dokładnie wiem co chcesz zrobić ale próbowałeś skorzystać z funkcji wyszukaj pionowo bądź poziomo (w zależności jak chcesz wyszukiwać potrzebne dane)?

http://office.microsoft.com/pl-pl/excel … 09114.aspx

http://office.microsoft.com/pl-pl/excel … 09335.aspx


([alex]) #3

Musisz to:

1234; B; C; D; E

999; X; Y; Z

666; S; A; T; A; N

podzielić do:

1234; B

1234; C

1234; D

1234; E

999; X

999; Y

999; Z

666; S

666; A

666; T

666; N

Wtedy bez problemu użyjesz wyszukaj pionowo.


(Marcin Obala) #4

Napisałem że ręczna transpozycja nie wchodzi w grę bo wierszy może być bardzo dużo jak i kolumn.

Dodatkowo Wyszukaj pionowo szuka tylko w pierwszej kolumnie i zwraca wartość z tego samego wiersza kolumnę którą chcemy.

Wyszukaj poziomo szuka tylko w pierwszym wierszu i zwraca wartość z innego wiersza tej samej kolumny czyli też nie spełnia swojego zadania.


(Dimatheus) #5

Hej,

:arrow: A co dokładnie chcesz uzyskać?

:arrow: Jakie wartości funkcja ma Ci zwrócić w poszczególnych przypadkach? Jakiś przykład?

Pozdrawiam,

Dimatheus


(Marcin Obala) #6

Chodziło mi o to żeby zrobić arkusz bez wykorzystania VBA taki że wklejam dane z jednego źródła, dane z drugiego źródła i np. w 3 arkuszu mam połączenie tych danych. W pierwszym źródle masz dane np.

C2

C53

C21

C12

D31

D81

L213

U15

X13

W drugim źródle masz:

5700123131;jakies info; inne info; C1,C2,C3,C41,C61

106012123;jakieś info; inne info; D2,D3

106123PH; jakieś info; inne info; X13

I teraz wklejam, biorę tekst jako kolumny, rozdzielam po średnikach. Następnie ostatnią powstałą kolumnę tekst jako kolumny i rozdzielam po przecinkach. I teraz w arkuszu np. numer dwa obok X13 chcę formułę która przeszukuje mi wszystkie wiersze tabeli z arkusza1, jeśli znajdzie taką wartość w wierszu to zwróci pierwszą kolumnę tego wiersza.


(Dimatheus) #7

Hej,

Generalnie prościej zrobić to przy pomocy VBA, ale skoro chcesz formułami, to może być. :slight_smile: Udało mi się uzyskać taki efekt. :smiley: Dla łatwości na moim serwerze podwieszam plik z formułami. Znajdziesz go tutaj. I czas na opis.

:arrow: Po wrzuceniu danych do zakładki i rozdzieleniu ich na kolumny na podstawie średników i przecinków, w ostatniej kolumnie tworzymy numerację od 1 do ‘n’, które służyć będą nam za indeks (zakładka Dane ),

:arrow: W zakładce Wartości w kolumnie B tworzymy formułę: =INDEKS(Dane!A:A;WYSZUKAJ(A1;Dane!B:F);1), która zwraca dane według kryterium, o którym piszesz. W danych formuły WYSZUKAJ(A1;Dane!B:F) zamiast kolumn B:F zaznaczamy cały zakres danych, które będziemy wyszukiwać łącznie z dodatkową kolumną, którą trzeba utworzyć, a o której pisałem w poprzednim punkcie. Reszta argumentów pozostaje bez zmian.

Pozdrawiam,

Dimatheus


(Marcin Obala) #8

Ale proste się to wydaje. Trzeba poczytać o funkcjach więcej.


(Dimatheus) #9

Hej,

Czyli rozumiem, że działa i to tak, jak chciałeś, by działało?

Pozdrawiam,

DImatheus


(Marcin Obala) #10

Dokładnie tak. Próbowałem podobnie ale nie wiedziałem że funkcja wyszukaj zwraca ostatnią kolumnę. Dzięki bardzo.


(Dimatheus) #11

Hej,

To jest po postu mieszanie formułami… :slight_smile: Dość często wykorzystuję je w pracy. :slight_smile:

Drobiazg. :slight_smile:

Pozdrawiam,

Dimatheus


(Marcin Obala) #12

Aktualizacja:

podane rozwiązanie nie działa zbyt dobrze. Ostatnio to zauważyłem. Nie wyszukuje wartości dokładnej tylko przybliżoną. Np. jak w drugiej linijce dodać np. C11 a usunę z pierwszej to i tak dopasuje mi wartość z pierwszej linijki zamiast z drugiej.


(Dimatheus) #13

Hej,

Faktycznie, dokładnie tak jest - w przypadku tej funkcji nie da się zdefiniować, by wyszukiwano wartości dokładnie dopasowane. Konieczna jest więc mała modyfikacja formuł. Zamiast funkcji Wyszukaj można zastosować Podaj.Pozycję. Oczywiście generalnie działa ona tylko z jedną tablicą, więc wrzucenie większej ilości kolumn w argumentach uraczy nas wynikiem #N/D!. Dlatego najlepiej poszczególne przeszukiwania zakresów połączyć formułą Jeżeli.Błąd - przeszukujemy najpierw kolumnę B; jeżeli nie znalazł tam wyniku, to ma przeszukać kolumnę C, później D i tak dalej. Ostatecznie formuła dla komórki B1 w zakładce Wartości będzie wyglądać tak:

=INDEKS(Dane!A:A;JEŻELI.BŁĄD(PODAJ.POZYCJĘ(A1;Dane!B:B;0);JEŻELI.BŁĄD(PODAJ.POZYCJĘ(A1;Dane!C:C;0);JEŻELI.BŁĄD(PODAJ.POZYCJĘ(A1;Dane!D:D;0);PODAJ.POZYCJĘ(A1;Dane!E:E;0))));1)

Pliczek na serwerze podmieniłem, by łatwiej można to było zobaczyć. :slight_smile:

Pozdrawiam,

Dimatheus


(Marcin Obala) #14

Niestety to u mnie się nie sprawdzi bo czasami w wierszu są 3 kolumny a czasami 150. Napiszę po prostu program do tego i będzie najprościej.


(Dimatheus) #15

Hej,

To zdecydowanie zmienia postać rzeczy. Myślę, że Excel miałby problemy z funkcją, która do kolumn odwołuje się po kilkadziesiąt razy… Nie mówiąc już o pisaniu samej formuły.

Ewentualnie zostaje napisać makro, które będzie przeszukiwać poszczególne zakresy i jeśli nie znajdzie wartości, będzie przeskakiwać do następnej kolumny i rozpoczynać proces poszukiwania na nowo.

Pozdrawiam,

Dimatheus


([alex]) #16

Czy to że jest podzielone w przykładzie który podałeś:

5700123131;jakies info; inne info; C1,C2,C3,C41,C61

106012123;jakieś info; inne info; D2,D3

106123PH; jakieś info; inne info; X13

W pierwszym wierszu masz wszędzie prefiks “C”, w drugim - “D”, w trzecim - “X” to podałeś dla czytelności czy tak masz?

Chodzi o to że jeżeli każdy wiersz ma swój prefiks to da się to dosyć prosto rozwiązać, podwójnym wyszukiwaniem.


(Dimatheus) #17

Hej,

Marcin511 na początku pisał o dowolnych ciągach znaków i takie podawał w przykładzie. Dlatego podejrzewam, że nie ma w ciągach liczbowych żadnych zależności. Gdyby były, faktycznie można by zastosować wyszukaj.pionowo i wyszukaj.poziomo - rozwiązanie byłoby gotowe. :slight_smile:

Pozdrawiam,

Dimatheus