Prawie wszystkie funkcje programu Excel mogą bez problemów odwoływać się do innych plików (skoroszytów) nawet gdy pliki te są zamknięte.
Niestety są pewne wyjątki. Niektóre funkcje obsługują odwołania do zakresów z innych plików ale nie działają poprawnie jeśli pliki do których się odwołują są zamknięte.
W tym filmie pokazuję taki przypadek na przykładach bardzo popularnych funkcji liczących i sumujących warunkowo.
Pokazuję także sposób na obejście tego problemu.
Użyta/e funkcja/e: LICZ.JEŻELI, SUMA.ILOCZYNÓW, LICZ.WARUNKI, SUMA.JEŻELI, SUMA.WARUNKÓW
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-770 – Funkcje odnoszące się do zamkniętych plików.xlsx
Comments 25
Świetna robota. Po raz kolejny korzystam z Pańskich poradników. W tym konkretnym przypadku myślałem, że polegnę (LICZ.JEŻELI) i praca na zamkniętym pliku = no can do. A tutaj wszystko jak zwykle dokładnie i precyzyjnie wytłumaczone.
Podziwiam pasję do tego typu rzeczy 🙂
Author
Dzięki za miłe słowa. Cieszę się, że pomagam 🙂
Staram się robić zgodnie z instrukcją i plikiem, ale coś mi nie wychodzi…
Formuła wygląda jak niżej, co robię źle? Odniesienie nadal działa tylko do otwartego pliku źródłowego, przy zamknietym jest #ADR…
=WYSZUKAJ.PIONOWO($A3;ADR.POŚR(ZŁĄCZ.TEKSTY(„’\\192.100.100.100\katalog\podkatalog\[Raport_”;$A$1;”.xlsx]”;”Arkusz1′”;”!”;”A:BB”);PRAWDA);B$2;0)
Konstrukcja powyższa (z ADR.POŚR) wynika z tego że nazwa pliku źródłowego jest zmienna i zależy od wpisanej przeze mnie daty (komórka A1).
Author
Funkcja ADR.POŚR nie będzie działała na zamkniętym pliku.
Czy jest jakiś sposób aby obejść ten problem ? Czy istnieje jakaś funkcja działająca podobnie do ADR.POŚR ale pracująca z zamkniętymi plikami ?
Z góry dziękuję za pomoc.
Author
Tylko programowaniem możesz to obejść. Ewentualnie import danych przy pomocy Power Query.
Jednak to dobrze spytać specjalistę w temacie 🙂
Panie Piotrze, a jest możliwość wybrnięcia z tego w mało bolesny sposób? Próbowałem pozbyć się ADR.POŚR i wyrzucić adres do jakiejś komórki (załóżmy że A1) w postaci:
=ZŁĄCZ.TEKSTY(„’\\100.100.100.1\katalog\podkatalog\[Raport”;$A$1;”.xlsx]Arkusz1′!A:BB”)
Ale tutaj wariuje mi funkcja wyszukaj pionowo:
=WYSZUKAJ.PIONOWO($A4;$A$1;B$2;0)
parametr: Tabela_tablica wyrzuca #ARG!
Author
I tak musisz wykorzystać funkcję ADR.POŚR
Witam serdecznie,
Chcę skorzystać z pliku bazowego (zamkniętego), zawierającego pełne dane klientów, do którego odnoszą się łącza w pliku otwartym, używanym przez biuro obsługi, zawierającym jedynie wybrane dane, zablokowane dla przypadkowej edycji.
Niestety przenoszone m.in. adresy mailowe za nic w świecie nie chcą mi się dać użyć jako takie (osoby z biura wysyłają w starym pliku w ten sposób maile do klientów klikając w adresy mailowe i przechodzą automatycznie do Outlooka.)
Czy jest jakiś sposób na „zmuszenie” Excela, aby traktował dane w komórce jako adres mailowy?
Z góry dziękuję za odpowiedź.
Pozdrawiam
Marek
Author
Zobacz ten odcinek:
https://www.youtube.com/watch?v=XzhliX7VV50
Witam ponownie,
dziękuję serdecznie, zadziałało!
U mnie co prawda są tabele, więc funkcja działa mi z nazwami nagłówków.
Teraz jeszcze muszę powalczyć z tym, że nie aktualizują mi się dane, gdy plik bazowy jest zamknięty. Ale najpierw prześledzę dokładnie ten post …
Jeszcze raz dziękuję za wskazówkę i powtórzę za poprzednikiem: Podziwiam pasję!!!
Author
Cieszę się, że pomogłem 🙂
Świetne materiały! Właśnie suma iloczynów pomogła mi rozwiązać problem 🙂
Dziękuję bardzo!
Pozdrawiam,
Hubert
Author
Super! Cieszę się, że pomogłem 🙂
A co zrobić w odwrotnej sytuacji? Czyli gdy formuły przestają działać w momencie otwarcia pliku źródłowego?
Jak otwieram plik, do którego zaciągam jakieś dane przy pomocy wyszukaj pionowo, a plik źródłowy jest zamknięty, to wszystko działa. Działa również jeśli otworzę najpierw plik źródłowy, a dopiero później plik, do którego zaciągam. Formuły wyrzucają jednak błąd #ADR jeśli najpierw otworzę plik, do którego zaciągam dane, a dopiero później plik źródłowy.
Author
Hmm, jeśli nie widzę pliku to ciężko cokolwiek pomóc. A gdy otworzysz plik do którego zaciągasz dane i przeliczysz ten plik (naciśnij F9) to błędy się nie pojawiają?
Jak otwieram tylko ten, do którego zaciągam dane to wszystko działa (po przeliczeniu F9 również).
Przykładowa formuła odnosząca się do drugiego pliku wygląda tak:
WYSZUKAJ.PIONOWO($J$11;’\\192.XXX.XX.XX\jakis_folder\inny folder\[nazwa_pliku.xlsx]lista'!$B:$N;5;0)
gdy otworzę teraz plik źródłowy (nazwa_pliku.xlsx) to formuła zmienia trochę postać i wywala błąd:
WYSZUKAJ.PIONOWO($J$11;’\\192.XXX.XX.XX\jakis_folder\inny folder\[nazwa_pliku.xlsx]#ADR'!$B:$N;5;0)
zamiast nazwy zakładki pojawia się #ADR
W przypadku gdy najpierw otwieram plik źródłowy, a dopiero później ten, do którego zaciągam
formuła wygląda tak:
WYSZUKAJ.PIONOWO($J$11;'[nazwa_pliku.xlsx]lista'!$B:$N;5;0) i również wszystko działa.
Jak w tym momencie zamknę plik źródłowy to w formule znowu pojawia się pełny adres do pliku wszystko nadal działa.
Czyli wszystko działa po za przypadkiem gdy przy otwartym pliku, na którym pracuję, otworzę plik źródłowy.
Czy to normalne zachowanie excela ?
Author
A czy sprawdzałeś co się będzie działo gdy umieścisz obydwa pliki na dysku lokalnym zamiast na sieciowym?
Czy tylko w tym jednym pliki masz takie objawy czy w każdym gdy próbujesz pisać WYSZUKAJ.PIONOWO?
Lokalnie wszystko działa w każdym przypadku.
Na szybko zrobiłem na sieci testowe małe pliki i jest podobnie. Różnica jest taka, że gdy mam otwarty plik, do którego zaciągam i otworzę źródłowy to zamiast wartości w komórkach pojawia się błąd #N/D, ale w formule adres pozostaje prawidłowy i jak zrobię Dane>Edytuj linki>Aktualizuj wartości to pojawiają się prawidłowe dane. Dziwne trochę…
Author
Ciężko mi powiedzieć, co może być przyczyną takiego zachowania. Jeśli znajdziesz wyjaśnienie koniecznie daj znać!
OK:) Dzięki za poświęcony czas.
Pozdrawiam.
A jaki „zamiennik” należy zastosować w przypadku takiego błędu – spowodowanego wykorzystaniem funkcji SUMA.JEŻELI?
czy może czeka mnie programowanie?
Author
Możesz zamiast funkcji SUMA.JEŻELI wykorzystać funkcję SUMA.ILOCZYNÓW 🙂
Rewelacja! Jeszcze nigdy tak szybko nie ustaliłem przyczyny błędu. Gratulacje!
Author
Fajnie, że pomogłem 🙂 Dzięki za oglądanie i komentarz!