• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Konsolidacja danych z wielu arkuszy innego pliku

Czyli ADR.POŚR między plikami

Podobny temat już na blogu poruszałam (zobacz tutaj), natomiast dotyczył on pobierania danych tylko z innych arkuszy. Było tam dodatkowe utrudnienie, dotyczące kolejności kolumn, natomiast dane konsolidowane były z tego samego pliku. Dzisiaj sytuacja będzie nieco inna: będziemy pobierali dane z innego pliku, z różnych jego arkuszy. Też posłużymy się funkcją ADR.POŚR, jednak do formuły “jakoś” dorzucimy nazwę pliku.

Zaczynamy!

Formatka

Mamy 2 pliki:

  1. z danymi źródłowymi
  2. podsumowujący

W pliku z danymi źródłowymi mamy 31 arkuszy, ponumerowanych od 1 do 31. W każdym z nich znajduje się identyczna co do układu tabelka z danymi, które chcemy podsumować w drugim pliku. Dane dotyczą np. liczby pracowników na zmianie dziennej i nocnej w pięciu lokalizacjach: Koło, Września, Luboń. Mosina i Swarzędz.

Tak wyglądają arkusze z danymi źródłowymi (każdy ma identyczny układ, jednak inne dane do pobrania):

Dane do pobrania - układ

Dane do pobrania – układ

A w takiej tabelce będziemy konsolidować dane:

Formatka konsolidacji

Formatka konsolidacji

Formuła

Zaczniemy od wpisania sobie nazwy pliku do żółtej komórki E1, którą nazwiemy Plik (o nazywaniu komórek pisałam min. tutaj).

Teraz sama formuła. Wbrew pozorom nie będzie ona jakoś strasznie skomplikowana. Żeby sobie ułatwić życie, w komórce C4, czyli tej, w której będę pisała uniwersalną formułę, wskażę wartość, któa ma się w niej znaleźć, czyli wartość z komórki C4 w arkuszu 1 pliku Dane.xlsx. Dostanę więc taką formułę:

='[Dane.xlsx]1'!$C$4

Zrobiłam to po to, bo nigdy nie pamiętam składni odwołania się do innego pliku. Mieszają mi się apostrofy i nawiasy kwadratowe ;). Będę więc edytowała wstawioną formułę, a zacznę od tego, że wstawię ją w funkcję ADR.POŚR i sparametryzuję nazwę pliku. Czyli tak:

=ADR.POŚR("'["&Plik&"]1'!$C$4")

Połowa sukcesu za nami. Poważnie.

Teraz trzeba tylko sparametryzować adres komórki. Zacznę od najłatwiejszego, czyli numeru wiersza. Zauważmy, że dane pobieramy zawsze tylko z 4 lub 5 wiersza arkusza. Reguła też jest taka, że zawsze dane dotyczące dnia są w wierszu 4., a nocy w 5. Ten numer załatwimy więc prostym JEŻELI, które również dokleimy, ponieważ funkcja ADR.POŚR chce od nas tekst:

=ADR.POŚR("'["&Plik&"]1'!$C$"&JEŻELI($B4="Dzień";4;5))

Komórkę $B4 zablokowałam przed kolumną B, ponieważ tę funkcję docelowo będę kopiowała, a chcę, żeby wtedy kolumna została taka sama, czyli cały czas B.

OK. Teraz gorsza sprawa: kolumna. W zależności od lokalizacji ta kolumna będzie inna. W sumie najprostszym rozwiązaniem byłoby napisanie 5 formuł, dla każdej lokalizacji oddzielnie. I w sumie to jest bardzo dobre rozwiązanie. Ale post byłby trochę krótki, więc pokażę Wam jak to zrobić w jednej formule :). Do tego celu przyjmę pewne założenie: kolumny lokalizacji są wszędzie takie same: zarówno w pliku podsumowującym jak i w plikach źródłowych (sama tworzę ten plik, więc mogę tak pójść na łatwiznę 😉 ).

Jeśli tak, to np. Koło zawsze będzie w kolumnie C, czyli trzeciej, itd. Ponieważ literkowe kolumny (A,B,C) kiepsko się zwiększa, więc posłużę się ich numerami. Żeby jednak to zrobić, posłużę się funkcją NR.KOLUMNY i dodatkowo powiem funkcji ADR.POŚR, żeby stosowała odwołanie W1K1, zamiast A1. Będzie trochę jak czarna magia, ale w tym typie odwołania podajemy numeryczne odwołanie zarówno do wiersza, jak i kolumny. Czyli komórka W1K1 to nic innego jak komórka w pierwszym wierszu i w pierwszej kolumnie arkusza, czyli A1. Komórka W4K3 to C4 itd. Żeby funkcja ADR.POŚR to zastosowała, w drugim jej argumencie powinniśmy wpisać 0 (lub FAŁSZ). No i oczywiście uwzględnić literki W i K, które są obowiązkowym elementem tego typu odwołań.

Wszystko do kupy wygląda tak:

=ADR.POŚR("'["&Plik&"]1'!W"&JEŻELI($B4="Dzień";4;5)&"K"&NR.KOLUMNY(C3);0)

Funkcja NR.KOLUMNY dostała od nas argument komórki wyżej (najważniejsze, że ta komórka jest w tej samej kolumnie, z której chcemy pobrać numer), z której pobiera numer kolumny. Dla porządku można byłoby ją zablokować tak: C$3, jednak nie jest to konieczne. Jak chcecie.

I jeszcze jedna istotna sprawa: trzeba pobierać wartości z określonych arkuszy. Aby to zrobić odwołamy się do wartości w kolumnie A, czyli:

 

Najważniejsze jest to, że po skopiowaniu tej formuły wszystko ładnie pięknie działa :). Zobaczcie przykładowe wartości:

Wynik konsolidacji

Wynik konsolidacji

I tyle. Takie to proste, a ja mam nadzieję, że Ci pomogłam i że to rozwiązanie Ci się przyda!

A poniżej plik (tylko skonsolidowany) do pobrania. Pamiętaj o pliku z danymi!

MalinowyExcel Konsolidacja danych z wielu arkuszy innego pliku ADR.POŚR dw.xlsx

I film, w którym krok po kroku pokazuję rozwiązanie:

 

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , , .Dodaj do zakładek Link.

16 odpowiedzi na „Konsolidacja danych z wielu arkuszy innego pliku

  1. Grażyna mówi:

    Dzięki, to może mi się przydac. Serdecznie pozdrawiam

  2. Rafał mówi:

    Dzięki za wpis. Szkoda, że wcześniej tego nie znałem, zaoszczędziłoby mi to sporo klikania, ale na pewno w przyszłości jeszcze użyje ten formuły

  3. Pawlaszczyk mówi:

    Hej,
    Potrzebował bym pomocy z adr pośr jak rozpisać formułę dla wszystkich arkuszy nawet dla nowych arkuszy chodzi mi o to aby pobierał zawsze daną całą kolumnę z kolejnych arkuszy.

  4. Natalia mówi:

    Witam, mam pewien problem i zastanawiam się czy można go rozwiązać za pomocą ADR.POŚR?

    Mam 29 arkuszy z danymi (arkusze mają dokładne taką samą zawartość), w arkuszu 30 chcę skopiować automatycznie przeciągając w dół komórkę z następującą formułą:
    =JEŻELI(ORAZ(Arkusz 1!$I$38=0;Arkusz 1!$J$38=0;Arkusz 1!$I$36=0;Arkusz 1!$J$36=0;Arkusz 1!$I$26=0;Arkusz 1!$J$26=0;Arkusz 1!$I$24=0;Arkusz 1!$J$24=0);”x”;Arkusz 1!$H$10)

    w taki sposób aby w skopiowanych komórkach w formule zmieniała się kolejno nazwa arkuszy: Arkusz 2, Arkusz 3, Arkusz 4 itd.

    W jaki sposób można to zrobić?

    Z góry dzięki za pomoc
    Pozdrawiam 🙂

    • Malina mówi:

      A powiedz co dokładnie ma robić ta formuła? Bo można tutaj użyć formuły 3D, żeby było szybciej, można ADR.POŚR, tylko napisz dokładnie co ma robić formułą 🙂

  5. Natalia mówi:

    Chodzi mi o to, żeby w ostatniej zakładce móc kontrolować np. czy dla danego Arkusza została wydana decyzja (w jednym arkuszu może tych decyzji być nawet około 20 miesięcznie), a jeśli tak to na jaką kwotę. To w zasadzie mam tutaj:
    JEŻELI(ORAZ(Arkusz 1!$I$38=0;Arkusz 1!$J$38=0;Arkusz 1!$I$36=0;Arkusz 1!$J$36=0;Arkusz 1!$I$26=0;Arkusz 1!$J$26=0;Arkusz 1!$I$24=0;Arkusz 1!$J$24=0);”x”;Arkusz 1!$H$10)
    jeżeli wszystkie argumenty będą spełnione (będą równe “0”) będzie to oznaczać że nie było decyzji i zobaczę “x”, jeśli jednak gdzieś będzie 0, będzie to oznaczać decyzję i zobaczę kwotę. Ta część działa.
    Problem w tym, że tych zakładek mam sporo i chciałabym aby przeciągnięcie w dół skopiowało formułę zmieniając jedynie arkusz na kolejny, zachowując adres komórki.

    • Malina mówi:

      Ok, rozumiem :). To tak, ja bym tutaj dała ADR.POŚR faktycznie. Najłatwiej w kolumnie roboczej obok wypisać nazwy arkuszy, z których mają zostać pobierane wartości. A ADR.POŚR będzie się do nich odwoływał 🙂

  6. Natalia mówi:

    Tak też zrobiłam. najpierw dla sprawdzenia wpisałam samą formułę ADR.POŚR:
    =ADR.POŚR(“”&A3&”!$h$10″) gdzie A3 jest odwołaniem do konkretnego arkusza, a “H10” wyświetla wartość (jest to, to samo H10 co użyte w funkcji JEŻELI).
    Jednak nijak nie potrafię połączyć tych dwu funkcji w jedną. Sama funkcja ADR.POŚR działa super, funkcja JEŻELI(ORAZ) również, ale jak próbuje je złączyć w jedną to wyskakuje błąd.
    Wiem, że coś robię źle, ale już brakuje mi pomysłu co 🙂

    • Malina mówi:

      Hej :). Jeśli dobrze widzę, to brakuje Ci apostrofu od nazwy arkusza przed wykrzyknikiem. Sprawdź koniecznie, bo na takich bzdurkach często się mylimy.
      I jaki to błąd wyskakuje?

  7. Natalia mówi:

    Metodą prób i błędów, i po pewnym czasie odstawienia na bok udało mi się dojść do tego, o co mi chodziło:
    =JEŻELI(ORAZ(ADR.POŚR(“”&A1&”!i38″)=0;ADR.POŚR(“”&A1&”!j38″)=0;ADR.POŚR(“”&A1&”!i36″)=0;ADR.POŚR(“”&A1&”!j36″)=0;ADR.POŚR(“”&A1&”!i26″)=0;ADR.POŚR(“”&A1&”!j26″)=0;ADR.POŚR(“”&A1&”!i24″)=0;ADR.POŚR(“”&A1&”!j24″)=0;);0;ADR.POŚR(“”&A1&”!h10″))
    w arkuszu docelowym utworzyłam odwołania do kolejnych arkuszy(“A1″…) i w powyższy sposób połączyłam funkcje Jeżeli(Oraz) i ADR.POŚR – i teraz wszystko działa. dzięki za podpowiedzi.
    Pozdrawiam:)

    • Malina mówi:

      Natalia, super, że Ci się udało! Gratuluję :). Czasem taka “odstawka” pomaga zebrać myśli i spojrzeć na problem z innej strony 🙂

  8. Formuła mówi:

    Witam
    mam ogromny problem i nie potrafię go rozwiązać. Może mi pomożesz. Codziennie dostaję plik z raportami wykonanej pracy moich pracowników. Jest ich ogrom. Codziennie muszę dokonać podziału na kilka grup, które muszą otrzymać kopię tego raportu ale tylko tak aby widzieć swoją grupę pracowników którzy mają przypisane konkretne identyfikatory. Identyfikatory się nie zmieniają. A mnie coś trafia jak muszę cały plik sortować a potem kopiuj i wklej i tak przez kilka godzin dziennie. Potrzebuję formuły, która z jednego pliku excel skopiuje mi wszystkie dane danej grupy do nowego pliku tak abym mogła już gotowy przesłać dalej.

    • Malina mówi:

      Hej, to ja bym szła w Power Query. Warto stworzyć słownik tych przypisań. Jeśli skorzystasz z Power Query, to trzeba scalić dane. Jeśli wolisz formułami – to na pewno WYSZUKAJ.PIONOWO do przyporządkowania tych danych, a potem ręczne przeklejanie danych do innych plików. 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *