[Excel] Wyszukiwanie jednej wartosci w kilku plikach

Witam.

Chciałbym stworzyć wyszukiwarkę produktów. Na razie korzystam z mało ciekawego rozwiązania. Wyszukuje produktu po ID za pomocą funkcji podaj.pozycje w innym pliku. Następnie za pomocą makra wyszukuje dany wiersz i kopiuje cały do arkusza.
Niestety za każdym razem muszę modyfikować funkcję podaj.pozycję zmieniając nazwę pliku i skoroszytu. Plik, w którym wyszukuje zawsze jest w formacie: dane_data (dd.mm.rrrr) - np."dane_1.11.2011.xls" lub 11.11.2011.xls". W każdym pliku jest tylko jeden arkusz z nazwą taką samą jak plik.
Funkcja wygląda tak:
PODAJ.POZYCJĘ(A1;'C:\dane\Listopad 2011\produkt\[dane_5.11.2011.xls]dane_5.11.2011'!$A$1:$A$65536;0)

Makro:


To co chciałbym osiągnąć to możliwość wyszukiwania kilku produktów, w różnych plikach i kopiowaniem wiersza obok szukanego ID. ID w pliku szukanym jak i wynikowym zawsze jest w kolumnie A, wierszy w pliku z danymi jest około 2-2,5 tysiąca.

Liczę na jakieś sugestię.

Gamblee wrote:

Liczę na jakieś sugestię.

Można napisać makro działające na wszystkich plikach (otwiera plik -> przeszukuje i kopiuje ->zamyka plik), ten temat przewijał się kilka razy przez to forum - poszukaj, jednak to rozwiązanie będzie mało wydajne dla większej ilości plików.

Moja sugestia jest jednak taka, żeby te pliki wrzucić do tabeli w Accessie i tam je analizować. W Accessie można łatwo zrobić import pliku po wciśnięciu przycisku. Mając wszystkie dane w jednym miejscu wyszukiwanie możesz wtedy zrobić bądź w Accessie, bądź w Excelu + ADODB do łączenia się z bazą.

Proces importu mógłby być uruchamiany codziennie przez wyznaczoną osobę (zakładam że to jakieś "firmowe" rozwiązanie), bądź zautomatyzowany - uruchamiana cyklicznie aplikacja lub Windows Service aktualizujący bazę danymi z Excela. Gdybyś zdecydował się na tą opcję, za stosowną opłatą mogę przygotować Ci odpowiednią aplikację, napisz na PW.

A może dane które masz w Excelu pochodzą z jakiejś istniejącej bazy danych, do której masz masz dostęp? Wtedy można łatwiej by było łączyć się Excelem via ODBC bezpośrednio do bazy i z niej wyciągać dane.

Zmienilem troche koncpecjie i teraz dane z kilku plików wrzucam do jednego. Chciałbym wyszukiwać kilka wartości jednocześnie i usunąć cały wiersz jeżeli w komórce znajduje sie ta wartość. tych wartości na razie jest okolo 25 tysiecy, także przeszukiwanie dlugo trwa. Co byście zmienili?

marcinj12 wrote:

Moja sugestia jest jednak taka, żeby te pliki wrzucić do tabeli w Accessie i tam je analizować.

To a propos pytania "co byście zmienili".

Jeśli już się uprzeć przy pliku, to usuwanie większej ilości wierszy pojedynczo poleceniem .Delete jest baaaardzo wolne, zagnieżdżona pętla też nie pomaga. 4 pomysły na przyspieszenie:
1. Kopiować znajdowane wiersze do nowego arkusza,
2. Wykorzystać autofiltr, w zależności od ustalonych warunków: do znalezienia / skopiowania pasujących wierszy bądź usunięcia niepasujących.
3. Wykonywać przeszukiwanie poleceniem Range(...).Find() dla znalezienia pierwszej z szukanych wartości (najlepiej tej najrzadziej pasującej) + dla każdego znalezionego wiersza kilka warunków if... dla sprawdzenia pozostałych.
4. Wykorzystać SQL i ADODB do łączenia się pliku "sam ze sobą" [moja ulubiona metoda]

Tutaj w 4 poście masz przykłady na usuwanie komórek z wykorzystaniem autorfiltra i funkcji find(), można je wykorzystać do usuwania lub przerobić żeby kopiowały znalezione wiersze do nowego arkusza.

Może i wygodniej byłoby w Accessie, ale wytłumacz moje kierownictwu, żeby licencje na Access też kupili:) Dlatego pozostaje Excel, gdyż wszytko wokół niego się kręci.
Dzięki za linka na pewno, którąś z tych metod wykorzystam.

Zawsze można pobrać Accessa w wersji testowej (bodaj 60 dni), utworzyć w nim bazę i ew. formularz do importu nowych plików, a potem można korzystać z darmowej, okrojonej wersji: Access Runtime (można w nim otwierać, ale już nie edytować plik).
Potem z taką bazą można się łączyć Excelem - czy to za pomocą kwerend czy zapytań przez ADODB.

Poniżej w każdym razie masz przykład makra z pkt 4 o którym pisałem: zapytania SQL pliku Excela do samego siebie, tylko dostosować nazwy i warunek zapytania SQL (rozbudować o klauzulę WHERE...) do własnych potrzeb. W podobny sposób można się zresztą łączyć z Accessem, w przypadku Excela arkusz do którego się łączysz musi mieć niepowtarzające się nagłówki kolumn w pierwszym wierszu.

Do Accessa mnie raczej nie przekonasz Wole pozostać przy Excelu.

Wykorzystałem opcje szukania na podstawie listy, żeby zwracało mi pożądane wartości.
W bazie mam produkty, które są oceniane w skali od 1-5. Chciałbym teraz to podzielić na pół (tak żebym miał równą ilość produktów w takiej samej skali, np: po 100 pierwszych, po 200 drugich itd.) i wkleić do dwóch różnych arkuszy.

Próbuje coś kombinować z Rows.Count / 2. Macie jakieś podpowiedzi lub część kodu, który by mi pomógł?

A wklej sposób szukania na który się zdecydowałeś, bo trochę kodu się już przewinęło...
Nie wiem czy rozumiem tą ideę podziału, jak chcesz to dzielić. Ja bym posortował całość wg oceny, zliczył każdą ilość i przekopiował dokładnie połowę.

Po zmodyfikowaniu kodu wyszło mi coś takiego. Wiem, że jest błąd z For i Next. Z założenia ma filtorować po oceni i kopiować do innego skoroszytu. Chciałbym, żeby z pliku gdzie mam bazę kopiowało mi do całkiem nowego skoroszytu i tworzyło tylko dwa arkusza: próba1 i próba2.

Witam wszystkich
Możecie pomóc mi rozwiązać problem ?

Przykład:
- kolumny ABC mają w wierszu 6 sumy
- DEF wpisuje jakies dane
- GHI odejmuje sume ABC i wprowadzone dane z DEF
zwracając 0 gdy w DEF jest puste
- J sumuje GHI i dzieli przez 25

Problem:
- w J nie sumuje gdy w G lub H lub I jest puste (dla zer działa - patrz obrazek)

Szukane:
Jak powinna wyglądać funkcja w J żeby sumowanie działało dla pustych GHI ?

Gamblee wrote:

Po zmodyfikowaniu kodu wyszło mi coś takiego. Wiem, że jest błąd z For i Next. Z założenia ma filtorować po oceni i kopiować do innego skoroszytu. Chciałbym, żeby z pliku gdzie mam bazę kopiowało mi do całkiem nowego skoroszytu i tworzyło tylko dwa arkusza: próba1 i próba2.

Próbuj czegoś w ten deseń, poprawiłem kilka błędów, tyle że teraz kopiuje wszystko w ten sam arkusz:

Poprawiłem końcówkę, tak żeby nie nadpisywało mi wyników. Mam tylko problem z pobieraniem połowy danych.
Jeszcze tylko pozostaje zamiana Copy na Cut i usuniecie pustych wierszy.