Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel - wyszukiwanie w tablicy części tekstu przy zadaniu 2 warunków

10 Kwi 2013 14:00 3768 15
  • Poziom 7  
    witam, mam problem z wyszukiwaniem w tablicy części tekstu przy zadaniu 2 warunków, mam 2 arkusze danych i w obydwu wystepuja 2 kolumny wspolne
    chcialbym w jednym arkuszu dopisac wartosci z drugiego arkusza spelniajace zadane kryteria
    uzywam do tego celu funkcji tablicowej indeks i podaj.pozycję

    {=INDEKS(Arkusz5!D:D;PODAJ.POZYCJĘ(2;(Arkusz5!A:A=A6)+(Arkusz5!B:B=B6);0))}

    dziala to jednak tylko jesli szukana wartość skladajaca sie z 9 znakow jest dokladnie taka sama jak w przeszukiwanej tablicy, a u mnie niestety w tablicy wartosci sa w roznej postaci i moga sie skladac z wiekszej ilosci znakow poprzez dodanie z przodu cyfr lub innych znakow a ja potrzebuje sprawdzic tylko te 9 ostatnich znakow

    dodatkowo potrzebowalbym zalozyc warunek na wyswietlanie wyniku pobieranego zgodnie z przykladem z kolmny D, ze jesli wynik<>6 znakow to ma wyswietlic kolumne E

    i moje arusze sa dosyc pojemne nawet do 800tys wierszy wiec mam wrazenie ze z poziomu VBA dzialaloby znacznie szybciej ale na chwile obecna nie posiadam takiej wiedzy
    z gory dzieki za pomoc
    ps. ze wzgledow technicznych musi to byc excel
    Excel - wyszukiwanie w tablicy części tekstu przy zadaniu 2 warunków
  • Moderator Programowanie
    Kolumna pomocnicza z funkcją PRAWY?
  • Poziom 7  
    z kopiowaniem 9 znakow do dodatkowej kolumny dziala, nawet na screenie jest dodatkowa kolumna w ktorej to chodzilo,
    ale juz powyzej 50tys wierszy przeliczanie trwa wieki , a mam pare plikow powyzej 800tys wierszy
  • Moderator Programowanie
    Hmm. Trzeba by się zastanowić nad tym, co szybciej zadziała. Makro, czy przeliczanie... 800000! formuł tablicowych. Stawiam na makro.

    Załącz kawałek arkusza z układem i formułami jak w oryginale. Przećwiczymy ;)
  • Poziom 7  
    w zalaczeniu przesylam jeden z lzejszych plikow,
    dokleilem kolumne Prawy 9 znakow ale jest problem zeby prownywal z ta kolumna
    dolozylem tez warunek, ale wkleja mi zawartosc tylko 1 komorki
    jakby jeszcze sie dalo zamiast kopiowac funkcje do wszystkich komorek w kolumnie stworzyc jakies makro ktore by to robilo automatycznie az do znalezienia pustego wiersza to byloby super

    z gory dzieki za pomoc
  • Moderator Programowanie
    Dane nie są unikatowe. Np wiersz 3 oraz 20 (Arkusz5). Dziewięć znaków od prawej oraz data dublują się. Co w takiej sytuacji?
  • Poziom 7  
    pierwsze 10 wierszy to sa moje dane testowe, sprawdzalem na mniejszej probce czy funkcje dzialaja,
    w plikach oryginalnych posiadam jeszcze 3 wspolna kolumne z dokladnym czasem tyle tylko ze czasy sie rozjezdzaja czasami, wiec ja chwilowo pominalem

    mysle ze w przypadku istnienia powtorzen warunkow wystarczy mi pierwsze znalezione porownanie
  • Moderator Programowanie
    Przeprowadziłem kilka prób. Przy tej porcji danych i na moim sprzęcie::
    1. Przeliczenie formuł przy zmianie w Arkusz5 ~50sek.
    2. Makro porównujące każdy z każdym - hmm, bociany zdążą się rozmnożyć i odfrunąć
    3. Makro z wykorzystaniem nie najszybciej bądź co bądź metody Find <38sek

    Excel - wyszukiwanie w tablicy części tekstu przy zadaniu 2 warunków

    No ale poczekajmy, może ktoś znajdzie coś szybszego...
  • Poziom 7  
    przy pomocy funkcji juz bylbym zadowolony gdyby nie problem z formatowaniem komorek

    gdyby wszystkie porownywane dane byly w tym samym formcie tzn liczby+data, czy tekst+data to funkcja tablicowa dziala
    w momencie dostawienia kolumny z funkcja Prawy zawartosc w niej jest traktowana jako tekst a porownywane jest do liczb i wywala n/d
  • Moderator Programowanie
    karolekg napisał:
    z funkcja Prawy zawartosc w niej jest traktowana jako tekst
    Pomnóż przez jeden: PRAWY(argumenty)*1
  • Pomocny post
    Poziom 33  
    Dzień dobry,
    Chciałem się włączyć do dyskusji, chociaż trudno zaproponować szybkie rozwiązanie przy tak dużej liczbie danych. Nie chcę proponować VBA, są pewnie lepsi od tego specjaliści, natomiast formuły Excela można na pewno zoptymalizować. Ale najpierw pytanie, czy w przykładzie kolumna I w arkuszu "wynik" zawiera spodziewany wynik? Warunek jest chyba nielogiczny. Proszę to przemyśleć.
    Sprawdzenie 9 ostatnich znaków nie wymaga tworzenia pomocniczej kolumny. Wszystko można zapisać w jednej formule. Tylko, żeby nie zrobiła się z tego nieczytelna "megaformuła" należy wyliczenie indeksu funkcją PODAJ.POZYCJĘ zastąpić formułą nazwaną i umieścić w Menedżerze Nazw. Ja użyłem tu nazwy "ind". Wtedy formuła końcowa przyjmie postać (sądzę, że tak to powinno wyglądać, bo jaki sens ma branie do porównania danej z Arkusza5 z wiersza bieżącego w ark. wynikowym):
    =JEŻELI(DŁ(INDEKS(Arkusz5!D:D;ind))=6;INDEKS(Arkusz5!D:D;ind);INDEKS(Arkusz5!E:E;ind))
    Formuła ind powinna być zdefiniowana w Menedżerze Nazw, ale tu trzeba zachować ostrożność ze względu na adresy względne. Przy definiowaniu ind kursor musi być w tej komórce, w której jest lub będzie końcowa formuła. Definicja ind może mieć postać (kursor w K2):
    =PODAJ.POZYCJĘ(2;(PRAWY(Arkusz5!$A:$A;9)=PRAWY(wynik!A2;9))+(Arkusz5!$B:$B=wynik!B2);0)
    Formułę końcową wstawiłem do kolumny K. Szkoda, że nie można jej przetestować na dostarczonych danych, bo prawie wszędzie wychodzi #N/D!

    Pisał Pan też w jednym z postów o problemach z kopiowaniem do dużego zakresu komórek. To nie wymaga żadnego makra. Trzeba tylko znać numer końcowego wiersza. Kopiuje Pan swoją formułę do schowka np. Ctrl+C, zaznacza Pan pierwszą komórkę zakresu kopiowania, otwiera Pan okno Przejdź do (Ctrl+G), wpisuje adres końcowy i zatwierdza przy wciśniętym Shift. Zaznaczy się Panu cały obszar, teraz tylko Ctrl+V, żeby wkleić. A jak Pan nie zna numeru końcowego wiersza, to trzeba wybrać kolumnę z danymi i kliknąć myszką dwa razy dolną krawędź dowolnej komórki - i już Pan jest w ostatniej wypełnionej komórce, można zapamiętać lub zapisać numer wiersza. W tej kolumnie, do której chce Pan kopiować to nie zadziała, bo tam nie ma jeszcze danych (chyba, że są jakieś stare, które trzeba wymienić).
  • Poziom 7  
    dziekuje panie Macieju za wskazanie bledow logicznych, juz je poprawilem, oraz za pomoc przy wklejaniu do kolumny.
    na chwile obecna poradzilem sobie z funkcja z tym ze wylicza mi poprawnie pierwsze 6 wierszy, a w nastepnych n/d, nie wiem o co chodzi skoro funckja byla wklejana w dol,
    za chwile wezme sie za przyklad uproszczenia formuly

    dodane: zeby nie bawic sie z roznymi formatami danych, wszystkie kolumny przekonwertowalem na tekst
  • Poziom 33  
    Wygląda, że wzory są OK, tylko dane nie pasują.
  • Poziom 7  
    fakt, zle dane wkleilem, jutro w pracy sprawdze na wlasciwych danych, za duzo wrazen :P

    formula nazwana dziala super, wyelimonowala mi dodawanie dodatkowej kolymny dla 9 znakow i dziala bez wzgledu na format danych czyli porownuje tekst z liczbami co nie bylo mozliwe w moim recznym wpisywaniu funkcji
    respect dla Macieja

    ale zeby nie bylo tak prosto wracam teraz do powtarzajacych sie danych co na przykladzie zaznaczylem na czerwono,
    jak teraz mozna dodac jakas petle ktora by sprawdzala wystapienie juz tej pary i przzepisywala nastepny pasujacy wiersz, to chyba juz VBA,
    i dzieki z gory
  • Pomocny post
    Poziom 33  
    Dzień dobry,
    Excel nie jest dobrym narzędziem do przetwarzania tak dużej liczby danych w sposób złożony. Próbowałem zmodyfikować te formuły, tak aby możliwe było wielokrotne szukanie tych samych danych, ale to rozwiązanie jest mało efektywne, bo działa na wszystkie rekordy, a nie tylko te zwielokrotnione. Poza tym nie wiem ile może być tych powtórzeń. Do każdego trzeba utworzyć nowy indeks. W tej chwili są 3 indeksy, można dodać następne, ale każdy kolejny spowalnia obliczenia. Myślę, że docelowo trzeba to rozwiązać inaczej, ale musi Pan wiedzieć co dokładnie chce Pan osiągnąć i jakie są uwarunkowania. Generalnie zmierzać do podziału plików do przetwarzania na mniejsze porcje.
  • Poziom 7  
    dzieki wielkie, potrojne sprawdzanie mi w zupelnosci wystarczy, jest super, wczoraj siedzialem pol dnia i nie wpadlem na takie wykorzystanie funkcji Przesuniecie, ale na wiekszych plikach trwa dluugo. tymczsowo wystarczy

    a w miedzyczasie posiedze i spobuje to na vba przerobic
    dzieki wszystkim za pomoc