MS Excel - warunkowe pobieranie danych z wielu arkuszy

Dzień dobry,

Czy jest jakiś sposób, żeby pobrać dane z innego skoroszytu Excel z wieloma arkuszami na podstawie parametru?

Chciałbym, zeby kod/funkcja przeszukała konkrenty zakres wierszy w danej kolumnie, i jeśli tam znajdzie się odpowiednie słowo, zwróciła mi odpowiednie komórki znajdujące się w tym wierszu.

Konkretny przykład:

W miesięcznym rozliczeniu mam arkusze na każdy dzień, w których wpisuje w danym wierszu: typ faktury (kuchnia, chemia, bar, inne itp.), dostawca, nr faktury, kwota brutto, kwota netto. Takich wierszy jest oczywiście kilka.

W innym skoroszycie chciałbym, aby funkcja zwróciła mi: Data (konretna komórka w danym arkuszu), dostawca, kwota netto i brutto.

Czy ogarnie się to samym funkcjami, czy trzeba użyć Power Query albo VBA? Wydaje mi się, że chyba trzeba użyć If oraz elseif, ale w programowaniu jestem słaby.

Da się formułami, wystarczy użyć formuły wyszukaj.pionowo, a adres komórki podaje się w formie:
'E:\1\[Test.xlsx]Arkusz1'C3
gdzie:
E:\ - dysk/partycja, na której znajduje się plik Test.xlsx
1 - folder w którym znajduje się plik Test.xlsx
Arkusz1 - arkusz z którego pobiera dane
C3 - komórka z której pobiera dane
W przypadku gdy plik źródłowy jest w tym samym folderze co plik docelowy ścieżkę można zacząć od razu od '[Test.xlsx]Arkusz1'C3

To bardzo pomocna funkcja! Widziałem ją wcześniej, ale myślałem, że mi się nie przyda.

Funkcja ta jednak ma dwa mankamenty, których chciałbym się pozbyć:

Gdy danego parametru nie ma, zwraca #N/D!, a wolałbym, żeby nic nie zwracała;

Przeszukuje tylko jeden arkusz naraz.

Jak chciałbym to usprawnić?
Mamy 3 parametry: kuchnia, bar, inne.
Wyszukaj pionowo "kuchnia", jeśli jest - zwróć wartość danych komórek, jeśli nie ma -> szukaj kolejnego parametru "bar". To chyba da się zamknąć w pętli JEŻELI jeśli się nie mylę, ale i tak w końcu zwróci N/D, jeśli nic nie znajdzie.

A potem to jeszcze zwinąć na wszystkie arkusze.

zadisk8 wrote:

Funkcja ta jednak ma dwa mankamenty

Wydaje się, że dla rozwiązania Twojego problemu funkcja wyszukaj.pionowo ma inny istotny mankament - wyszukuje tylko pierwsze wystąpienie poszukiwanej wartości
Wszystkie mankamenty można w zasadzie obejść ale wszystko zależy od konkretnego układu danych źródłowych i wynikowych.
Nie można nic precyzyjnie pomóc bez załączenia przykładowych plików z założonym układem danych

Prot wrote:

Wydaje się, że dla rozwiązania Twojego problemu funkcja wyszukaj.pionowo ma inny istotny mankament - wyszukuje tylko pierwsze wystąpienie poszukiwanej wartości

Zauważyłem i rozwiązałem ten problem inaczej - WYSZUKAJ.PIONOWO stosuję osobno dla każdego wiersza, wraz z funkcą JEŻELI i zapętlam, aż skończa się parametry. Niestety wyrażenie robi się coraz bardziej skomplikowane, a rośnie lawinowo wraz z ilością parametrów. Wypadałoby go skrócić, abo jakoś uporządkować.

Załączam przykładowe pliki. RK zawiera faktury z danego dnia, Podliczenie zbiera je wszystkie razem.

zadisk8 wrote:

Wypadałoby go skrócić,

Można tak jak na zrzucie
odwołan...PNG Download (40.96 kB)

zadisk8 wrote:

żeby pobrać dane z innego skoroszytu Excel z wieloma arkuszami

Nie potrzebujesz żadnych funkcji wyszukujących wystarczy bezpośrednie odwołanie, możesz zastosować odwołania tablicowe, albo import całych tabeli (czy faktur ?).

Jeszcze nie do końca wyobrażam sobie oczekiwany wynik końcowy, ale na pewno te fragmenty skopiowane formułami można znacznie skrócić. Poprawiony fragment w pliku Podliczenie zaznaczyłem na zielono.
Co miało być w lewym górnym rogu arkusza - trudno się zorientować, bo są tam na ogół błędy.

Prot wrote:

Nie potrzebujesz żadnych funkcji wyszukujących wystarczy bezpośrednie odwołanie, możesz zastosować odwołania tablicowe, albo import całych tabeli (czy faktur ?).

No tak, wystarczyłoby to po prostu zaimportować Problem polega na tym - o czym zapomniałem - że wartości te muszą być posegregowane wg. parametru. W innym przypadku mógłbym je faktycznie po prostu zaimportować. Czyli w nowym pliku potrzebuję mieć wszystkie faktury osobno z baru, osobno z kuchni itp. Możnaby zastosować funkcję WYSZUKAJ.PIONOWO z jednym parametrem (np. bar) na wszystkie arkusza, ale ona zwraca #N/D, gdy nie znajdzie parametru. Chyba muszę znaleźć inne rozwiązanie. Gdybym umiał pisać makra, to napisałbym tak:
Tabela BAR


Identyczne makra opisywałyby faktury kuchnia, chemia itp.

1. #N/D! - da się wyeliminować przez dodanie na zewnątrz formuły JEŻELI.ND albo JEŻELI.BŁĄD. Obie działają podobnie do funkcji JEŻELI, więc nie powinieneś mieć z nimi kłopotów
2. Da radę przeszukać kilka arkuszy jednocześnie - wystarczy w zakresie podać adresy zakresów w poszczególnych arkuszach rozdzielone średnikami, np. Arkusz1!C1:C3;Arkusz2!B1:B3, itd.
3. Jeśli w danej komórce formuła się zbytnio rozbudowuje (np. zaczyna mieć już kilka linii w pasku formuły) i chciałbyś tego uniknąć celem przejrzystości w poszukiwaniu ewentualnych błędów, można poszczególne formuły "wewnętrzne", zagnieżdżone zapisywać w osobnych komórkach, a potem w "zewnętrznych" formułach robić tylko odnośniki adresów komórek do tych formuł "wewnętrznych". Następnie komórki z formułami "wewnętrznymi" ukrywasz tak, jak ukrywanie kolumn czy wierszy. Dzięki temu łatwo się połapać co gdzie jest, a i łatwiej odnaleźć błąd, bo masz poszczególne "etapy" wyniku końcowego. Choć nie uważam się już za początkującego w Excelu to często tak robię, bo faktycznie jest łatwiej to ogarnąć. Czasem jak tego dużo, to przenoszę część formuł do osobnego arkusza, w którym mam same formuły, a ostateczną formułę mam w arkuszu głównym - tam gdzie dane wejściowe. Ukrywam arkusz lub arkusze pomocnicze i po kłopocie
4. Jeśli potrzebujesz wyszukiwania wielokrotnego (WYSZUKAJ.PIONOWO faktycznie szuka aż do pierwszej napotkanej wartości) to bez makr czy skryptów da radę to zrobić tylko tak jak napisał kolega @Prot - odwołania tablicowe

Edit.
Wracając do 1. Jeśli nie chcesz by cokolwiek wyświetlało w komórce (np. w przypadku błędu wstawiłeś sobie zero, ale chcesz by wyglądała ona na pustą), to robisz tak:
- w formule w miejscu gdzie wpisujesz co ma wyświetlać w przypadku błędu, podajesz jakąś dziwną wartość, tzn. taką której na pewno nie uzyskasz, np. 2,5 przy ilości zwierząt.
- obszar z tymi danymi gdzie może się pojawić N/D zaznaczasz i stosujesz formatowanie warunkowe
- w opcjach formatowania warunkowego wybierasz "tylko komórki zawierające" i jako parametr podajesz Twoją fałszywą wartość, czyli te 2,5 podane wyżej, a jako działanie które formatowanie ma wykonać zmieniasz kolor czcionki na taki jakie jest wypełnienie (czyli domyślnie na białą)
Dzięki powyższemu trikowi nie widać że "tam coś jest", na wydruku nic się nie pojawia i nie drażni i co najważniejsze nie ma N/D ;)

Dodano po 1 [godziny] 40 [minuty]:

Hmmm... Po dłuższym namyśle stwierdziłem, że chyba już wiem co ostatecznie chce osiągnąć autor tematu Kiedyś spotkałem się z tym, że nie można przekroczyć kwoty zakupu od jednego sprzedawcy, ponieważ wtedy wejdzie się w procedury przetargowe. W związku z tym konieczne było liczenie kwoty zakupu w danym roku każdego kontrahenta z osobna. I tak też na chwilę obecną rozumiem Twój problem zadisk8
Wystarczy zmienić konwencję zapisu pliku RK (Raport Kasowy?) na taki jaki jest w załączniku, czyli mamy dodatkową kolumnę o nazwie "Data" i kasjerka(?) wpisuje dane artykuły z faktury, obojętnie kiedy (nie musi być chronologicznie) i obojętnie w którym wierszu. Ważne by pierwszy wiersz był nagłówkiem tabeli, a każda kolumna miała swoją unikalną nazwę.
Do tego mamy plik Podliczenia2, w którym tworzymy tabelę przestawną
Do obszaru wiersze dodajemy "Typ" i "Dostawca", do obszaru Filtr dajemy "Data", a do obszaru Wartości dajemy "Netto" i "Brutto", ale zmieniamy tam ustawienia pola wartości na Suma zamiast licznik. W opcji filtrowania po dacie (jak już tabelka nam się wyświetli) wybieramy "Zaznacz wiele elementów" i wtedy możemy wybrać wiele dat, np. od 1 do 31 marca, jak zależy nam na raporcie miesięcznym.
Nic nie stoi na przeszkodzie by tabelę przestawną umieścić w osobnym lub nawet tym samym arkuszu pliku RK2, ale rozumiem, że chodzi o to że jedna osoba wpisuje surowe dane z faktur, a inna podlicza zestawienia, a w sieci nie da się ustawić uprawnień do pracy kilku osób na jednym pliku, albo chcemy by każdy był odpowiedzialny za swoją działkę
W razie pytań, pisz

Super! Dokładnie o to chodziło! Tabela przestawna rozwiązuje sytuację.

Ale żeby ją utowrzyć, trzeba mieć zbiorczo podsumowane wszystkie faktury, bez pustych wierszy. Jak to zrobić automatycznie dla wszystkich arkuszy?

Żeby było tak jak na screenie



EDIT: nie trzeba mieć pustych wierszy - wystarczy w tabeli przestawnej ukryć puste pozycje. Nie umiałem napisać makro, które by mi kopiowało wszystkie te faktury ze wszystkich arkuszy więc zrobiłem to na piechotę. Dziękuję za pomoc!

EDIT2: Jednak napisałem makro, które kopiuje tylko komórki z wartościami ze wszystkich arkuszy. Zostawiam kod dla potomnych