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:
- z danymi źródłowymi
- 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
A w takiej tabelce będziemy konsolidować dane:
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
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:
Dzięki, to może mi się przydac. Serdecznie pozdrawiam
Cieszę się 🙂
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
To mam nadzieję, że jeszcze się przyda 🙂
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.
Hej,
to już nie formułą – polecam Power Query albo VBA tutaj 🙂
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 🙂
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łą 🙂
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.
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ł 🙂
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 🙂
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?
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:)
Natalia, super, że Ci się udało! Gratuluję :). Czasem taka „odstawka” pomaga zebrać myśli i spojrzeć na problem z innej strony 🙂
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.
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. 🙂
Witam. Bardzo proszę o podpowiedź jak w formule: =JEŻELI.BŁĄD(WYSZUKAJ.POZIOMO(E$2;MT!$C$4:$P$5;2;FAŁSZ);0) zastąpić początek określenia tablicy odwołaniem do komórki. Chodzi o zastąpienie liter MT odwołaniem do komórki. W ten sposób formuła stanie się bardziej uniwersalna.
Z góry dziękuję za pomoc.
Dzień dobry. Nie jestem pewna, czy dobrze rozumiem: ja bym tutaj użyła funkcji ADR.POŚR, która umie tekstowo określić odwołanie do komórek.
Dzień dobry. Bardzo dziękuję za odpowiedź i chęć pomocy.
Też tak kombinowałem, ale nie potrafię poprawnie wprowadzić składni ADR.POŚR do mojej funkcji. Generalnie chodzi o to aby zamiast MT (nazwa arkusza w moim excelu) wprowadzić do funkcji odwołanie do komórki w kolumnie „B”, gdzie znajduje się skrót literowy „MT”. Dla przykładu: w B1 jest MT, B2 jest GM, itd. Wprowadzając odwołanie zamiast skrótu liter będę mógł tą funkcję kopiować w dół.
Będę niezmiernie wdzięczny za pomoc.
Można to zrobić np. tak: =ADR.POŚR(B1&wiersz). Zakładam, że wiersz jest w jakiś inny sposób ustalany. Czyli trzeba zastosować tutaj łączenie tekstów operatorem &. Proszę tak spróbować:)
Kombinuje tak, żeby przykład pokazany powyżej z zestawieniem danych dla Dzień/Noc zadziałał w moim pliku. Ze względu na formę danych wyjściowych będą konieczne modyfikacje funkcji. W moim przypadku zamiast Dzień/Noc będzie 12 miesięcy (Styczeń – Grudzień) a zamiast nazw lokalizacji – nazwa projektu. Dla tego przypadku funkcja JEŻELI chyba nie jest odpowiednia. Ilość możliwych niezagnieżdżeń funkcji w funkcji jest mniejsza niż 12.
Czy ma Pani jakieś rozwiązanie dla takiej modyfikacji Pani funkcji?
Może w takim razie połączyć funkcję WYBIERZ z WYSZUKAJ.PIONOWO? WYBIERZ potrafi wyświetlić odpowiedni numer swojego argumentu, a ten numer może być podany dzięki W.P. Może tak?
Dzień dobry
Czy mogę poprosić o link z danymi do tego ćwiczenia?
Dzień dobry, już niestety nie posiadam.