Jak odwoływać się do innego arkusza lub skoroszytu w programie Excel (z przykładami)

Możliwość odwoływania się do komórek i zakresów sprawia, że ​​każde narzędzie arkusza kalkulacyjnego działa. A Excel jest najlepszym i najpotężniejszym z dostępnych.

W tym samouczku omówię wszystko, co musisz wiedzieć o tym, jak odwoływać się do komórek i zakresów w programie Excel. Oprócz podstawowych odniesień na tym samym arkuszu, główna część tego samouczka będzie dotyczyła jak odwoływać się do innego arkusza lub skoroszytu w programie Excel.

Chociaż nie ma dużej różnicy w tym, jak to działa, gdy odwołujesz się do innego arkusza w tym samym pliku lub odwołujesz się do całkowicie oddzielnego pliku Excel, format tego odwołania nieco się zmienia.

Jest również kilka ważnych rzeczy, o których należy pamiętać podczas odwoływania się do innego arkusza lub innych plików zewnętrznych.

Ale martw się… nic zbyt szalonego!

Zanim skończysz z tym samouczkiem, będziesz wiedział wszystko, co trzeba wiedzieć o odwoływaniu się do komórek i zakresów w programie Excel (znajduje się w tym samym skoroszycie lub innym skoroszycie).

Zacznijmy!

Odwołanie do komórki w tym samym arkuszu

Jest to najbardziej podstawowy poziom odwołań, w którym odwołujesz się do komórki w tym samym arkuszu.

Na przykład, jeśli jestem w komórce B1 i chcę odwołać się do komórki A1, format będzie następujący:

=A1

Gdy to zrobisz, wartość w komórce, w której używasz tego odwołania, będzie taka sama jak w komórce A1. A jeśli wprowadzisz jakiekolwiek zmiany w komórce A1, zostaną one odzwierciedlone w komórce, w której użyłeś tego odniesienia.

Odwołanie do komórki w innym arkuszu

Jeśli musisz odwołać się do innego arkusza w tym samym skoroszycie, musisz użyć poniższego formatu:

Nazwa_arkusza! Adres_komórki

Najpierw masz nazwę arkusza, po której następuje znak wykrzyknika, po którym następuje odwołanie do komórki.

Jeśli więc chcesz odwołać się do komórki A1 w arkuszu 1, musisz użyć następującego odniesienia:

=Arkusz1!A1

A jeśli chcesz odwołać się do zakresu komórek w innym arkuszu, musisz użyć następującego formatu:

Nazwa_arkusza! Pierwsza_komórka:Ostatnia_komórka

Jeśli więc chcesz odwołać się do zakresu A1: C10 w innym arkuszu w tym samym skoroszycie, musisz użyć poniższego odniesienia:

=Arkusz1!A1:C10

Zauważ, że pokazałem ci tylko odwołanie do komórki lub zakresu. W rzeczywistości używałbyś ich w formułach. Ale format odniesień, o których mowa powyżej, pozostanie taki sam

W wielu przypadkach arkusz, do którego się odwołujesz, zawiera wiele słów w nazwie. Na przykład może to być Dane projektu lub Dane sprzedaży.

Jeśli masz spacje lub znaki niealfabetyczne (takie jak @, !, #, - itp.), musisz użyć nazwy w pojedynczych cudzysłowach.

Na przykład, jeśli chcesz odwołać się do komórki A1 w arkuszu o nazwie Dane sprzedaży, użyjesz poniższego odwołania:

='Dane sprzedaży'!A1

A jeśli nazwa arkusza to Dane-sprzedaży, aby odwołać się do komórki A1 w tym arkuszu, musisz użyć poniższego odniesienia:

='Dane-sprzedaży'!A1

Gdy odwołujesz się do arkusza w tym samym skoroszycie, a później zmieniasz nazwę arkusza, nie musisz się martwić, że odwołanie się rozbije. Excel automatycznie zaktualizuje te odniesienia dla Ciebie.

Chociaż dobrze jest znać format tych odniesień, w praktyce nie jest dobrym pomysłem ręczne wpisywanie ich za każdym razem. Byłoby to czasochłonne i bardzo podatne na błędy.

Pokażę Ci lepszy sposób tworzenia odwołań do komórek w programie Excel.

Automatyczne tworzenie odniesienia do innego arkusza w tym samym skoroszycie

O wiele lepszym sposobem utworzenia odwołania do komórki do innego arkusza jest po prostu wskazanie w programie Excel komórki/zakresu, do którego chcesz utworzyć odwołanie, i pozwolenie, aby program Excel sam je utworzył.

Dzięki temu nie będziesz musiał się martwić brakiem wykrzyknika lub cudzysłowów lub pojawieniem się jakiegokolwiek innego problemu z formatem. Excel automatycznie utworzy dla Ciebie prawidłowe odniesienie.

Poniżej znajdują się kroki, aby automatycznie utworzyć odniesienie do innego arkusza:

1. Wybierz komórkę w bieżącym skoroszycie, w której potrzebujesz odniesienia
2. Wpisz formułę, aż będziesz potrzebować odniesienia (lub znaku równości, jeśli potrzebujesz tylko odniesienia)
3. Wybierz arkusz, do którego chcesz się odnieść
4. Wybierz komórkę/zakres, do którego chcesz się odnieść
5. Naciśnij Enter, aby uzyskać wynik formuły (lub kontynuuj pracę nad formułą)

Powyższe kroki automatycznie utworzyłyby odwołanie do komórki/zakresu w innym arkuszu. Będziesz także mógł zobaczyć te odniesienia na pasku formuły. Gdy skończysz, możesz po prostu nacisnąć klawisz Enter, a otrzymasz wynik.

Na przykład, jeśli masz jakieś dane w komórce A1:A10 w arkuszu o nazwie Dane sprzedaży i chcesz uzyskać sumę tych wartości w bieżącym arkuszu, wykonaj następujące czynności:

1. Wpisz następującą formułę w bieżącym arkuszu (gdzie potrzebujesz wyniku): =Sum(
2. Wybierz arkusz „Dane sprzedaży”.
3. Wybierz zakres, który chcesz dodać (A1:A10). Jak tylko to zrobisz, Excel automatycznie utworzy odwołanie do tego zakresu (widać to na pasku formuły)
4. Naciśnij klawisz Enter.

Podczas tworzenia długiej formuły może być konieczne odwołanie się do komórki lub zakresu w innym arkuszu, a następnie powrót do arkusza źródłowego i odwołanie się do jakiejś komórki/zakresu.

Gdy to zrobisz, zauważysz, że program Excel automatycznie wstawia odwołanie do arkusza do arkusza, w którym masz formułę. Chociaż jest to w porządku i nie szkodzi, nie jest to potrzebne. W takim przypadku możesz zachować odniesienie lub usunąć je ręcznie.

Inną rzeczą, którą musisz wiedzieć podczas tworzenia odwołań przez wybranie arkusza, a następnie komórki/zakresu, jest to, że Excel zawsze utworzy a względne odniesienie (tj. odwołania ze znakiem n0 $). Oznacza to, że jeśli skopiuję i wkleję formułę (jedną z odwołaniem do innego arkusza) do innej komórki, automatycznie dostosuje to odwołanie.

Oto przykład wyjaśniający odniesienia względne.

Załóżmy, że używam następującej formuły w komórce A1 w bieżącym arkuszu (aby odnieść się do komórki A1 w arkuszu o nazwie SalesData)

=DaneSprzedaży!A1

Teraz, jeśli skopiuję tę formułę i wkleję w komórce A2, formuła zmieni się na:

=DaneSprzedaży!A1

Dzieje się tak, ponieważ formuła jest względna i kiedy ją skopiuję i wkleję, odniesienia automatycznie się dostosują.

Jeśli chcę, aby to odwołanie zawsze odnosiło się do komórki A1 w arkuszu SalesData, będę musiał użyć poniższej formuły:

=Dane sprzedaży!$A$1

Znak dolara przed numerem wiersza i kolumny blokuje te odniesienia, aby się nie zmieniły.

Oto szczegółowy samouczek, w którym możesz dowiedzieć się więcej o odniesieniach bezwzględnych, mieszanych i względnych.

Teraz, gdy omówiliśmy, jak odwoływać się do innego arkusza w tym samym skoroszycie, zobaczmy, jak możemy odwoływać się do innego skoroszytu.

Jak odwołać się do innego skoroszytu w programie Excel

Gdy odwołujesz się do komórki lub zakresu do innego skoroszytu programu Excel, format tego odwołania zależy od tego, czy skoroszyt jest otwarty, czy zamknięty.

I oczywiście nazwa skoroszytu i arkuszy również odgrywa rolę w określaniu formatu (w zależności od tego, czy w nazwie znajdują się znaki spacji lub znaki niealfabetyczne, czy nie).

Zobaczmy więc różne formaty odwołań zewnętrznych do innego skoroszytu w różnych scenariuszach.

Odniesienie zewnętrzne do otwartego skoroszytu

Jeśli chodzi o odwoływanie się do zewnętrznego otwartego skoroszytu, musisz określić nazwę skoroszytu, nazwę arkusza i adres komórki/zakresu.

Poniżej znajduje się format, którego należy użyć, odwołując się do zewnętrznego otwartego skoroszytu

='[NazwaPliku]NazwaArkusza!AdresKomórki

Załóżmy, że masz skoroszyt „Przykładowy plik.xlsx” i chcesz odwołać się do komórki A1 w arkuszu Arkusz1 tego skoroszytu.

Poniżej znajduje się odniesienie do tego:

=[PlikPrzykładowy.xlsx]DaneSprzedaży!A1

W przypadku, gdy w zewnętrznej nazwie skoroszytu lub nazwie arkusza (lub obu) są spacje, należy dodać nazwę pliku (w nawiasach kwadratowych) i nazwę arkusza w pojedynczych cudzysłowach.

Poniżej znajdują się przykłady, w których musisz umieścić nazwy w pojedynczych cudzysłowach:

='[Przykładowy plik.xlsx]SalesData'!A1 ='[Przykładowy plik.xlsx]Dane sprzedaży'!A1 ='[Przykładowy plik.xlsx]Dane sprzedaży'!A1

Jak utworzyć odniesienie do innego skoroszytu (automatycznie)

Ponownie, chociaż dobrze jest znać format, najlepiej nie wpisywać go ręcznie.

Zamiast tego po prostu wskaż Excelowi właściwy kierunek, a stworzy on dla Ciebie te odniesienia. Jest to o wiele szybsze i znacznie mniejsze prawdopodobieństwo błędów.

Na przykład, jeśli masz jakieś dane w komórce A1:A10 w skoroszycie o nazwie „Przykładowy plik” w arkuszu o nazwie „Dane sprzedaży” i chcesz uzyskać sumę tych wartości w bieżącym arkuszu, wykonaj następujące czynności :

1. Wpisz następującą formułę w bieżącym arkuszu (gdzie potrzebujesz wyniku): =Sum(
2. Przejdź do skoroszytu „Przykładowy plik”
3. Wybierz arkusz „Dane sprzedaży”.
4. Wybierz zakres, który chcesz dodać (A1:A10). Jak tylko to zrobisz, Excel automatycznie utworzy odwołanie do tego zakresu (widać to na pasku formuły)
5. Naciśnij klawisz Enter.

Spowoduje to natychmiastowe utworzenie formuły z poprawnymi odniesieniami.

Jedną z rzeczy, które można zauważyć podczas tworzenia odwołania do skoroszytu zewnętrznego, jest to, że zawsze tworzy on odniesienia bezwzględne. Oznacza to, że przed numerami wierszy i kolumn znajduje się znak $. Oznacza to, że jeśli skopiujesz i wkleisz tę formułę do innych komórek, będzie ona nadal odnosić się do tego samego zakresu ze względu na odwołanie bezwzględne.

Jeśli chcesz to zmienić, musisz ręcznie zmienić referencje.

Odniesienie zewnętrzne do zamkniętego skoroszytu

Gdy skoroszyt zewnętrzny jest otwarty i odwołujesz się do tego skoroszytu, wystarczy podać nazwę pliku, nazwę arkusza i adres komórki/zakresu.

Ale kiedy to jest zamknięte, Excel nie ma pojęcia, gdzie szukać komórek / zakresu, do którego się odwołujesz.

Dlatego podczas tworzenia odniesienia do zamkniętego skoroszytu należy również określić ścieżkę do pliku.

Poniżej znajduje się odwołanie, które odwołuje się do komórki A1 w arkuszu Arkusz1 w skoroszycie Plik przykładowy. Ponieważ ten plik nie jest otwarty, odnosi się również do lokalizacji, w której plik jest zapisany.

='C:\Users\sumit\Desktop\[Przykładowy plik.xlsx]Arkusz1'!$A$1

Powyższe odniesienie składa się z następujących części:

• Ścieżka pliku - lokalizacja w systemie lub sieci, w której znajduje się plik zewnętrzny
• Nazwa pliku - nazwa skoroszytu zewnętrznego. Obejmuje to również rozszerzenie pliku.
• Nazwa arkusza - nazwa arkusza, w którym odwołujesz się do komórek/zakresów
• Adres komórki/zakresu - dokładny adres komórki/zakresu, do którego się odnosisz

Gdy utworzysz odwołanie zewnętrzne do otwartego skoroszytu, a następnie zamkniesz skoroszyt, zauważysz, że odwołanie zmienia się automatycznie. Po zamknięciu skoroszytu zewnętrznego program Excel automatycznie wstawia również odwołanie do ścieżki pliku.

Wpływ zmiany lokalizacji pliku na odniesienia

Kiedy tworzysz odwołanie do komórki/zakresu w zewnętrznym pliku Excel, a następnie je zamykasz, odwołanie używa teraz również ścieżki do pliku.

Ale jeśli zmienisz lokalizację pliku, nic się nie zmieni w skoroszycie (w którym tworzysz odwołanie). Ale ponieważ zmieniłeś lokalizację, link został uszkodzony.

Jeśli więc zamkniesz i otworzysz ten skoroszyt, dowiesz się, że link jest uszkodzony i musisz go zaktualizować lub całkowicie przerwać. Wyświetli się monit, jak pokazano poniżej:

Po kliknięciu Aktualizuj pojawi się kolejny monit, w którym możesz wybrać opcje edycji łączy (co pokaże poniższe okno dialogowe)

Jeśli chcesz zachować te pliki połączone, możesz określić nową lokalizację pliku, klikając opcję Aktualizuj wartości. Excel otworzy dla Ciebie okno dialogowe, w którym możesz określić nową lokalizację pliku, przechodząc tam i wybierając ją.

Odwołanie do zdefiniowanej nazwy (w tym samym lub zewnętrznym skoroszycie)

Kiedy musisz odwoływać się do komórek i zakresów, lepszym sposobem jest tworzenie zdefiniowanych nazw dla zakresów.

Jest to przydatne, ponieważ ułatwia odwoływanie się do tych zakresów za pomocą nazwy zamiast długiego i skomplikowanego adresu referencyjnego.

Na przykład łatwiej jest użyć =SalesData zamiast =[Przykładowy plik.xlsx]Arkusz1′!$A$1:$A$10

A jeśli użyłeś tej zdefiniowanej nazwy w wielu formułach i musisz zmienić odwołanie, musisz to zrobić tylko raz.

Oto kroki, aby utworzyć nazwany zakres dla zakresu komórek:

1. Zaznacz wszystkie komórki, które chcesz uwzględnić w nazwanym zakresie
2. Kliknij kartę Formuły
3. Kliknij opcję Zdefiniuj nazwę (znajduje się w grupie Nazwy zdefiniowane)
4. W oknie dialogowym Nowa nazwa nadaj temu zakresowi nazwę (w tym przykładzie używam nazwy SalesData). Pamiętaj, że w nazwie nie może być spacji
5. Zachowaj zakres jako skoroszyt (chyba że masz silny powód, aby uczynić go na poziomie arkusza)
6. Upewnij się, że pole Odnosi się do zakresu jest poprawne.
7. Kliknij OK.

Teraz Twój nazwany zakres został utworzony i możesz go używać zamiast odwołań do komórek z adresami komórek.

Na przykład, jeśli chcę uzyskać sumę wszystkich tych komórek w zakresie SalesData, możesz użyć poniższej formuły:

= SUMA(Dane sprzedaży)

A co, jeśli chcesz użyć tego nazwanego zakresu, to inne arkusze, a nawet inne skoroszyty?

Możesz!

Musisz tylko postępować zgodnie z tym samym formatem, który omówiliśmy w powyższej sekcji.

Nie musisz wracać do początku tego artykułu. Pozwólcie, że podam tutaj wszystkie przykłady, abyście wpadli na pomysł.

Skoroszyt a skoroszyt na poziomie nazwanych zakresów: Gdy tworzysz nazwany zakres na poziomie skoroszytu, może istnieć tylko jeden zakres o tej nazwie. Jeśli więc odwołujesz się do niego z zewnętrznych skoroszytów lub arkuszy roboczych w tym samym skoroszycie, nie musisz określać nazwy arkusza roboczego (ponieważ jest on dostępny do użycia w całym skoroszycie). W przypadku korzystania z zakresu nazwanego na poziomie arkusza roboczego, będziesz mógł go używać tylko w arkuszu, a odwołując się do niego, będziesz musiał również określić nazwę arkusza roboczego.

Odwoływanie się do zdefiniowanej nazwy w tym samym arkuszu lub skoroszycie

Jeśli utworzyłeś zdefiniowaną nazwę dla poziomu skoroszytu, możesz jej użyć w dowolnym miejscu w skoroszycie, używając samej zdefiniowanej nazwy.

Na przykład, jeśli chcę uzyskać sumę wszystkich komórek w nazwanym zakresie, który utworzyliśmy (SaledData), mogę użyć poniższej formuły:

=SUMA(SprzedaneDane)

Jeśli utworzyłeś nazwany zakres na poziomie arkusza, możesz użyć tej formuły tylko wtedy, gdy nazwany zakres jest tworzony w tym samym arkuszu, w którym używasz formuły.

Jeśli chcesz go użyć na innym arkuszu (np. Arkusz2), musisz użyć następującej formuły:

=SUMA(Arkusz1!$A$1:$A$10)

A jeśli w nazwie arkusza znajdują się spacje lub znaki alfanumeryczne, musisz umieścić nazwę arkusza w pojedynczych cudzysłowach.

=SUMA('Arkusz 1'!$A$1:$A$10)

Odwoływanie się do zdefiniowanej nazwy w innym skoroszycie (otwartym lub zamkniętym)

Jeśli chcesz odwołać się do nazwanego zakresu w innym skoroszycie, musisz określić nazwę skoroszytu, a następnie nazwę zakresu.

Na przykład, jeśli masz skoroszyt programu Excel o nazwie ExampleFile.xlsx i nazwanym zakresie o nazwie SalesData, możesz użyć poniższej formuły, aby uzyskać sumę tego zakresu z innego skoroszytu:

=SUMA(PlikPrzykładowy.xlsx!DaneSprzedaży)

Jeśli w nazwie pliku znajdują się spacje, należy je użyć w pojedynczych cudzysłowach.

=SUMA('Przykładowy plik.xlsx'!DaneSprzedaży)

Jeśli masz nazwane zakresy na poziomie arkusza, musisz określić nazwę skoroszytu, a także arkusza roboczego podczas odwoływania się do niego z zewnętrznego skoroszytu.

Poniżej znajduje się przykład odniesienia do nazwanego zakresu na poziomie arkusza:

=SUMA('[Przykładowy plik.xlsx]Arkusz1'!DaneSprzedaży)

Jak wspomniałem powyżej, zawsze najlepiej jest tworzyć nazwane zakresy na poziomie skoroszytu, chyba że masz silny powód, aby utworzyć pierwszy poziom arkusza.

Jeśli odwołujesz się do nazwanego zakresu w zamkniętym skoroszycie, musisz również określić ścieżkę pliku. Poniżej znajduje się przykład tego:

=SUMA('C:\Users\sumit\Desktop\Przykładowy plik.xlsx'!SalesData)

Gdy utworzysz odwołanie do nazwanego zakresu w otwartym skoroszycie, a następnie zamkniesz skoroszyt, program Excel automatycznie zmieni odwołanie i doda ścieżkę pliku.

Jak utworzyć odniesienie do nazwanego zakresu

Jeśli tworzysz i pracujesz z wieloma nazwanymi zakresami, nie jest możliwe zapamiętanie nazwy każdego z nich.

Excel pomaga, wyświetlając listę wszystkich nazwanych zakresów, które utworzyłeś i umożliwia wstawianie ich do formuł jednym kliknięciem.

Załóżmy, że utworzono nazwany zakres SalesData, którego chcesz użyć w formule, aby zsumować wszystkie wartości z nazwanego zakresu.

Oto kroki, aby to zrobić:

1. Wybierz komórkę, w której chcesz wprowadzić formułę.
2. Wprowadź formułę do punktu, w którym chcesz wstawić nazwany zakres
3. Naciśnij klawisz F3 na klawiaturze. Spowoduje to otwarcie okna dialogowego Wklej nazwę z listą wszystkich utworzonych nazw
4. Kliknij dwukrotnie nazwę, którą chcesz wstawić.

Powyższe kroki spowoduje wstawienie nazwy do formuły i będziesz mógł kontynuować pracę nad formułą.

Uwaga: Ta technika działa tylko w przypadku zakresów nazw znajdujących się w tym samym skoroszycie. Jeśli chcesz uzyskać nazwy w skoroszycie zewnętrznym, musisz tam przejść, sprawdzić nazwę za pomocą okna dialogowego Wklej nazwę, a następnie wrócić i użyć jej w swojej formule (w poprawnym formacie przy użyciu nazwy pliku).

To wszystko, co musisz wiedzieć o tym, jak odwoływać się do innych arkuszy lub skoroszytów oraz jak utworzyć odwołanie zewnętrzne w programie Excel.

Mam nadzieję, że ten samouczek okazał się przydatny.