W programie Microsoft Excel częstym zadaniem jest odwoływanie się do komórek w innych arkuszach lub nawet w różnych plikach programu Excel. Na początku może się to wydawać nieco zniechęcające i zagmatwane, ale kiedy zrozumiesz, jak to działa, nie jest to takie trudne.
W tym artykule przyjrzymy się, jak odwołać się do innego arkusza w tym samym pliku Excel i jak odwołać się do innego pliku Excel. Omówimy również takie rzeczy, jak odwoływanie się do zakresu komórek w funkcji, jak uprościć sprawę dzięki zdefiniowanym nazwom i jak używać funkcji WYSZUKAJ.PIONOWO do odwołań dynamicznych.
Spis treści:
Jak odwołać się do innego arkusza w tym samym pliku Excel
Podstawowe odwołanie do komórki jest zapisywane jako litera kolumny, po której następuje numer wiersza.
Zatem odwołanie do komórki B3 odnosi się do komórki na przecięciu kolumny B i wiersza 3.
W przypadku odwołań do komórek w innych arkuszach to odwołanie do komórki jest poprzedzone nazwą innego arkusza. Na przykład poniżej znajduje się odwołanie do komórki B3 w arkuszu o nazwie „Styczeń”.
=January!B3
Wykrzyknik (!) Oddziela nazwę arkusza od adresu komórki.
Jeśli nazwa arkusza zawiera spacje, w odwołaniu należy ująć nazwę w pojedyncze cudzysłowy.
='January Sales'!B3
Aby utworzyć te odwołania, możesz wpisać je bezpośrednio do komórki. Jednak łatwiej i pewniej jest pozwolić programowi Excel na napisanie referencji.
Wpisz znak równości (=) w komórce, kliknij kartę Arkusz, a następnie kliknij komórkę, do której chcesz utworzyć odniesienie.
Gdy to zrobisz, Excel zapisuje odniesienie na pasku formuły.
Naciśnij klawisz Enter, aby zakończyć formułę.
Jak odwołać się do innego pliku Excela
W ten sam sposób możesz odwoływać się do komórek innego skoroszytu. Po prostu upewnij się, że masz otwarty drugi plik Excel, zanim zaczniesz wpisywać formułę.
Wpisz znak równości (=), przełącz się do innego pliku, a następnie kliknij komórkę w tym pliku, do którego chcesz się odwołać. Po zakończeniu naciśnij Enter.
Wypełniony odsyłacz zawiera nazwę drugiego skoroszytu zawartą w nawiasach kwadratowych, po której następuje nazwa arkusza i numer komórki.
=[Chicago.xlsx]January!B3
Jeśli nazwa pliku lub arkusza zawiera spacje, należy ująć odwołanie do pliku (w tym nawiasy kwadratowe) w pojedyncze cudzysłowy.
='[New York.xlsx]January'!B3
W tym przykładzie możesz zobaczyć znaki dolara ($) wśród adresów komórki. To jest bezwzględne odwołanie do komórki (dowiedz się więcej o bezwzględnych odwołaniach do komórek).
Podczas tworzenia odwołań do komórek i zakresów w różnych plikach programu Excel odwołania są domyślnie ustawiane jako bezwzględne. W razie potrzeby możesz zmienić to na odniesienie względne.
Jeśli spojrzysz na formułę, gdy skoroszyt, do którego istnieje odwołanie, jest zamknięty, będzie zawierał całą ścieżkę do tego pliku.
Chociaż tworzenie odniesień do innych skoroszytów jest proste, są one bardziej podatne na problemy. Użytkownicy tworzący lub zmieniający nazwy folderów i przenoszący pliki mogą przerwać te odniesienia i spowodować błędy.
Przechowywanie danych w jednym skoroszycie, jeśli to możliwe, jest bardziej niezawodne.
Jak odnieść się do zakresu komórek w funkcji
Odwołanie do pojedynczej komórki jest wystarczająco przydatne. Ale możesz chcieć napisać funkcję (taką jak SUMA), która odwołuje się do zakresu komórek w innym arkuszu lub skoroszycie.
Uruchom funkcję w zwykły sposób, a następnie kliknij arkusz i zakres komórek – tak samo jak w poprzednich przykładach.
W poniższym przykładzie funkcja SUMA sumuje wartości z zakresu B2: B6 w arkuszu o nazwie Sprzedaż.
=SUM(Sales!B2:B6)
Jak używać zdefiniowanych nazw dla prostych odsyłaczy
W programie Excel możesz przypisać nazwę do komórki lub zakresu komórek. Jest to bardziej znaczące niż adres komórki lub zakresu, gdy się na nie spojrzy. Jeśli używasz wielu odniesień w arkuszu kalkulacyjnym, nazwanie tych odniesień może znacznie ułatwić sprawdzenie, co zrobiłeś.
Co więcej, ta nazwa jest unikalna dla wszystkich arkuszy roboczych w tym pliku Excel.
Na przykład moglibyśmy nazwać komórkę „ChicagoTotal”, a następnie odsyłacz brzmiałby:
=ChicagoTotal
Jest to bardziej znacząca alternatywa dla standardowego odniesienia, takiego jak ten:
=Sales!B2
Utworzenie zdefiniowanej nazwy jest łatwe. Zacznij od wybrania komórki lub zakresu komórek, którym chcesz nadać nazwę.
Kliknij pole nazwy w lewym górnym rogu, wpisz nazwę, którą chcesz przypisać, a następnie naciśnij klawisz Enter.
Podczas tworzenia zdefiniowanych nazw nie można używać spacji. Dlatego w tym przykładzie słowa zostały połączone w nazwie i oddzielone wielką literą. Możesz także oddzielić wyrazy znakami takimi jak łącznik (-) lub podkreślenie (_).
Excel ma również Menedżera nazw, który ułatwia monitorowanie tych nazw w przyszłości. Kliknij opcję Formuły> Menedżer nazw. W oknie Menedżera nazw możesz zobaczyć listę wszystkich zdefiniowanych nazw w skoroszycie, gdzie się one znajdują i jakie wartości aktualnie przechowują.
Następnie możesz użyć przycisków u góry, aby edytować i usuwać te zdefiniowane nazwy.
Jak sformatować dane jako tabelę
Podczas pracy z obszerną listą powiązanych danych użycie funkcji Formatuj jako tabelę programu Excel może uprościć sposób odwoływania się do zawartych w niej danych.
Spójrz na poniższą prostą tabelę.
Można to sformatować jako tabelę.
Kliknij komórkę na liście, przejdź do zakładki „Strona główna”, kliknij przycisk „Formatuj jako tabelę”, a następnie wybierz styl.
Potwierdź, że zakres komórek jest poprawny, a tabela zawiera nagłówki.
Następnie możesz przypisać swojej tabeli znaczącą nazwę na karcie „Projekt”.
Następnie, gdybyśmy chcieli zsumować sprzedaż w Chicago, moglibyśmy odwołać się do tabeli, używając jej nazwy (z dowolnego arkusza), po której następuje nawias kwadratowy ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Możesz zobaczyć, w jaki sposób tabele mogą ułatwić tworzenie odwołań do danych funkcji agregacji, takich jak SUMA i ŚREDNIA, w porównaniu ze standardowymi odwołaniami do arkuszy.
Ta tabela jest mała do celów demonstracyjnych. Im większy stół i więcej arkuszy w skoroszycie, tym więcej korzyści zobaczysz.
Jak używać funkcji WYSZUKAJ.PIONOWO do odwołań dynamicznych
Wszystkie odwołania użyte w dotychczasowych przykładach zostały przypisane do określonej komórki lub zakresu komórek. To świetnie i często wystarcza do Twoich potrzeb.
A co, jeśli komórka, do której się odwołujesz, może się zmienić po wstawieniu nowych wierszy lub niektórych