Jak pobierać dane z kilku plików, czyli o funkcji IMPORTRANGE

Największą zaletą Arkuszy Google jest możliwość swobodnego sięgania po różne pliki i ich zawartość. Wystarczy, że są ustawione opcje udostępniania. Zatem możemy np. pociągnąć specyfikację produktu z jednego arkusza, dane jego sprzedaży w drugiego, listę klientów którzy go kupili z trzeciego i wszystko zebrać w jednym raporcie. Do tego okrasić fotką.

Dlaczego to jest pożyteczne?

Normalnie zabawa z danymi rozproszonymi po różnych plikach wymaga kopiowania całych tabel, pilnowania czy są aktualne (zwykle nie są). Bo przecież dane sprzedaży uaktualnia co chwilę handlowiec. Specyfikacja? – ktoś zrobił literówkę w opisie i szkoda to kopiować do wszystkich plików, gdzie się znajduje ten opis. Dobrze gdy jest matryca w jednym miejscu. Specjaliści używają do tego baz danych. Tylko czy wszyscy musimy być specjalistami od IT?

To co przeczytacie niżej, można przećwiczyć używając oryginalnych danych z pokazywanego pliku. Jeśli potrzebujesz je zmieniać, zrób sobie kopię (Plik –> Utwórz kopię)

Tu wkracza IMPORTRANGE – narzędzie do ciągnięcia fragmentów zawartości innych arkuszy. I tu też pokazuje się główna zaleta idei Arkuszy. Wszystkie Akrusze są przechowywane w chmurze, więc nie ma problemu, z tym czy siedzimy przy swoim stacjonarnym komputerze gdzie rzeczy są na dysku C:, czy na lapku, gdzieś indziej. Możemy dać dostęp do naszego pliku współpracownikowi i on również będzie mógł widzieć aktualizujące się dane. Kończy się problem znany od tysięcy lat.

” A możesz mi podesłać aktualną wersję ? “

Mojżesz na górze Synaj

IMPORTRANGE – czyli narzędzie do importu zakresów

Składnia:

=IMPORTRANGE("url arkusza";"zakres")

• url arkusza – , czyli adres pliku arkusza widoczny w pasku adresu przeglądarki. Możemy odnieść się też do linku zapisanego w innej komórce naszego arkusza – wówczas cudzysłowów nie stosujemy.
• zakres – nazwa arkusza (zakładki) i zakres danych który ma być wyświetlony np. “Arkusz1!A2:B20” lub nazwany zakres -np. “cennikwiosna2047”.

Przy pierwszej próbie podłączenia arkusza, pojawia się błąd #REF – musimy wówczas najechać na tą komórkę i zezwolić na dostęp. Nie wystarczy, że my jesteśmy właścicielami danych plików – musimy za każdym razem wydać im pozwolenie na wymianę danych.

Pamiętaj, że zaimportowany zakres to będzie tabela – więc jeśli wpiszesz np. “Arkusz1!A2:B20” – to musisz mieć miejsce na wyświetlenie całości. Inaczej wystąpi błąd (Arkusz w opisie błędu powie Ci które dokładnie komórki stoją na drodze do sprawnego wklejenia tabeli).

Nazywanie zakresów przed udostępnieniem

Jeśli zamierzacie udostępniać zakres (i nie tylko) warto nadać mu własną nazwę.

Jak pewnie zauważyliście – funkcja IMPORTRANGE wymaga wpisania zakresu w cudzysłowie i ta wartość jest tam wpisana “na twardo” – np. “Arkusz1!A3:B100”. Jeśli ktoś np. zmieni nazwę arkusza w pliku do którego się udwołujecie, jak doda jakąś kolumnę – IMPORTRANGE w drugim pliku nic nie zaktualizuje i się pogubi. Natomiast jeśli odwołujemy się do nazwy własnej zakresu – np. cennikwiosna2047 to nawet jeśli ta tabela będzie przesunięta – IMPORTRANGE ją znajdzie.

Użycie IMPORTRANGE z innymi funkcjami

Funkcję można swobodnie zagnieżdżać. Można ją stosować np. wewnątrz funkcji SORT, FILTER, VLOOKUP, QUERY etc. jednym słowem wszystkich, które obsługują zakresy.

Wyszukiwanie w tabeli, która jest w innym pliku

To bardzo częsty przypadek w mojej pracy. Trzeba dorzucić jakąś nową kolumnę do zestawienia, a dane są w innym pliku, w innej kolejności i jeszcze szkoda miejsca na wrzucanie całej tabeli. Naturalne jest użycie funkcji VLOOKUP (piszę o niej szczegółowo tutaj).

Ona spisuje się świetnie również do szukania w innych plikach arkuszowych.

Załóżmy, że dział marketingu zbiera na bieżąco opinie na temat naszych produktów. Mają swój plik, więc nie ma co go kopiować do siebie, skoro jutro będzie więcej danych:

Z kolei my potrzebujemy tylko zestawienia na temat klapek wraz z cenami i opiniami.

Spis modeli klapek już mamy, więc czas wyszukać opinie.

Rozwiązanie wygląda następująco:

Vlookup szuka według identyfikatora którym jest kod produktu (A10 -pierwsza kolumna zarówno tutaj jak i w tabeli z opiniami). Parametr 3 oznacza, że chcemy wrzucić zawartość 3 kolumny, a FALSE, że poszukujemy dokładnej wartości, a nie przybliżenia.

Przykład ściągnięcia tabeli posortowanej:

Przykład ściągania dwóch tabel na raz – jedna pod drugą.

To bardzo przydatne gdy łączymy kilka dokumentów (np. kilka cenników) i mamy je wykorzystać razem. IMPORTRANGE można łączyć przy pomocy zapisu klamrowego – szerzej opisanego w innym poście.

Praca z IMPORTRANGE i funkcją QUERY

Tu podam tylko maleńki przykład możliwości funkcji QUERY (obiecuję kiedyś poświęcić jej cały wpis). Ale jeśli przeszkadzają nam wkradające się nagłówki to nadaje się ona świetnie do usunięcia ich.

Tutaj użyliśmy funkcji QUERY, która została nałożona na dwa importowane zakresy i na naszą prośbę obcina kolumnę z kodami produktów oraz powtarzające się nagłówki. W ten sposób skleiliśmy dwa cenniki , usunęliśmy chwilowo niepotrzebne dane.

Co ważne – jeśli ktoś zmodyfikuje pliki źródłowe – np. sznurówki podrożeją do 5 zł – te dane się automatycznie wprowadzą do mojego pliku.

Oznaczanie zakresów, które są wykorzystywane na zewnątrz

Dobrą praktyką jest oznaczenie nazwanego zakresu przy pomocy wypełnienia tła. Często celowo zostawiam w nim kilka pustych wierszy, bo wiem że może być potrzeba szybkiego dopisania wartości. A jak widzę pokolorowaną przestrzeń to wiem dokąd sięga nazwany zakres i nie ryzykuję, że coś piszę poza zakresem. Często nazywając zakres używam zapisu otwartego dla całej kolumny – np. Arkusz1!A2:A

Ograniczenia i sztuczki

Odświeżanie

IMPORTRANGE jest funkcją spowalniającą pracę arkuszy. Zwłaszcza gdy odwołań jest kilkanaście i ciągną duże tabele po kilka tysięcy pozycji. Działa tylko online i aktualizuje się co 30 minut lub przy modyfikacji pliku z którego idzie import.

Jeśli spodziewamy się, że importowana tabela już nie będzie się zmieniać, a arkusz robi się ciężki – zróbmy jej wersję statyczną. W tym celu należy zaznaczyć importowaną tabelę, skopiować ją (Ctrl+C), a następnie wkleić tylko wartości (Shift + Ctrl + V).

Jeśli tylko mamy miejsce, warto nad importowaną tabelą umieszczać link do niej. Wówczas od razu będziemy wiedzieć, że jest “nie stąd”. Ponadto – jeśli przekleimy tabelę na wersję statyczną – ten link pozostanie i będzie można szybko zaktualizować zawartość ponownie wpisując IMPORTRANGE.

Zdarza się, że jeśli używamy funkcji IMPORTRANGE zagnieżdżonej w innych funkcjach, a wcześniej nie dawaliśmy plikom dostępu, arkusz wypluwa błąd zamiast dać opcji nadania dostępu. Wówczas należy roboczo wkleić gdzieś w pliku gołą funkcję IMPORTRANGE.

Przycinanie importowanej tabeli.

Zdarza się, że importujemy tabelę i chcemy widzieć tylko jej część – np. pierwsze 10 wierszy i tylko 2 kolumny. Możemy to zrobić definiując ręcznie zakres (np. “Arkusz1!A1:B10”), ale możemy ograniczyć tabelę też inaczej – służy do tego funkcja ARRAY_CONSTRAIN

Składnia:

ARRAY_CONSTRAIN(zakres; ile wierszy; ile kolumn)

Załóżmy, ze chcemy wyświetlić sobie w analizie tylko 3 produkty z trzema najlepszymi opiniami. W tym celu zagnieżdżamy w sobie 3 funkcje – IMPORTRANGE, która ściągnie nam tabelę, SORT, która ją posortuje malejąco wg opinii, a na koniec ARRAY_CONSTRAIN, która ją przytnie.

Wynik wygląda tak: