Praca z połączonymi tabelami w programie Microsoft Excel

Podczas wykonywania niektórych zadań w programie Excel czasami trzeba radzić sobie z kilkoma tabelami, które również są powiązane. Oznacza to, że dane z jednej tabeli są wciągane do pozostałych, a gdy są zmieniane, wartości we wszystkich połączonych zakresach tabel są ponownie obliczane.

Połączone tabele są bardzo wygodne w użyciu do przetwarzania dużej ilości informacji. Wszystkie informacje zawarte w jednej tabeli, oprócz tego, że nie jest jednorodna, nie są zbyt wygodne. Przy podobnych obiektach trudno jest pracować i je wyszukiwać. Problem ten ma na celu wyeliminowanie powiązanych tabel, między którymi informacje są rozproszone, ale jednocześnie są ze sobą powiązane. Połączone zakresy tabel mogą znajdować się nie tylko w jednym arkuszu lub jednej książce, ale także znajdować się w oddzielnych książkach (plikach). Dwie ostatnie opcje są stosowane w praktyce najczęściej, ponieważ celem tej technologii jest unikanie gromadzenia danych, a układanie ich na jednej stronie nie rozwiązuje problemu w sposób fundamentalny. Zobaczmy, jak tworzyć i jak pracować z tego rodzaju zarządzaniem danymi.

Tworzenie połączonych tabel

Przede wszystkim rozmyślmy nad pytaniem, w jaki sposób można utworzyć powiązanie między różnymi zakresami tabelarycznymi.

Metoda 1: Bezpośrednie wiązanie tabel według formuły

Najprostszym sposobem połączenia danych jest użycie formuł odwołujących się do innych zakresów tabel. Nazywa się to wiązaniem bezpośrednim. Metoda ta jest intuicyjnie zrozumiała, ponieważ wiązanie odbywa się prawie tak samo, jak tworzenie odniesień do danych w pojedynczej tablicy tablicowej.

Zobaczmy, jak przykład może tworzyć link przez bezpośrednie wiązanie. Mamy dwie tabele na dwóch arkuszach. Na jednej tabeli, lista płac jest obliczana przy użyciu wzoru przez pomnożenie liczby pracowników przez jeden współczynnik dla wszystkich.

Na drugim arkuszu znajduje się zestawienie tabelaryczne, w którym znajduje się lista pracowników wraz z ich wynagrodzeniami. Lista pracowników w obu przypadkach przedstawiona jest w jednym zamówieniu.

Konieczne jest, aby dane dotyczące stawek z drugiego arkusza były wciągane do odpowiednich komórek pierwszego.

1. Na pierwszym arkuszu wybierz pierwszą komórkę w kolumnie "Zakład" . Umieściliśmy w nim znak "=" . Następnie kliknij etykietę "Arkusz 2" , która znajduje się po lewej stronie interfejsu programu Excel nad paskiem stanu.
2. Przechodzi do drugiego obszaru dokumentu. Klikamy pierwszą komórkę w kolumnie "Licytowanie" . Następnie kliknij przycisk Enter na klawiaturze, aby wprowadzić dane do komórki, w której wcześniej ustawiono znak równości .
3. Następnie następuje automatyczne przejście do pierwszego arkusza. Jak widać, wartość stawki pierwszego pracownika z drugiej tabeli jest wciągana do odpowiedniej komórki. Ustawiając kursor na komórce zawierającej zakład, widzimy, że zwykła formuła służy do wyświetlania danych na ekranie. Ale przed współrzędnymi komórki, z której dane są wyprowadzane, pojawia się wyrażenie "Arkusz2!" , Które wskazuje nazwę obszaru dokumentu, w którym się znajdują. Ogólna formuła w naszym przypadku wygląda tak:

   =Лист2!B2

4. Teraz musisz przenieść dane o stawkach wszystkich innych pracowników przedsiębiorstwa. Oczywiście można to zrobić w taki sam sposób, w jaki wykonaliśmy zadanie dla pierwszego pracownika, ale biorąc pod uwagę, że obie listy pracowników znajdują się w tej samej kolejności, zadanie można znacznie uprościć i przyspieszyć jego rozwiązanie. Można to zrobić po prostu, kopiując formułę do poniższego zakresu. Ze względu na to, że odwołania w Excelu są domyślnie względne, kiedy są kopiowane, wartości są przesunięte, co jest tym, czego potrzebujemy. Samą procedurę kopiowania można wykonać za pomocą znacznika wypełnienia.

   Ustaw kursor w dolnej prawej części elementu za pomocą formuły. Następnie kursor powinien zostać przekształcony w marker wypełniający w postaci czarnego krzyża. Zaciskamy lewy przycisk myszy i przeciągamy kursor na samo dno kolumny.

5. Wszystkie dane z podobnej kolumny na arkuszu 2 zostały wciągnięte do tabeli na arkuszu 1 . Jeśli zmienisz dane na Arkuszu 2, zostaną one automatycznie zmienione na pierwszym.

Metoda 2: Korzystanie z łącza INDEX-MAP

Ale co, jeśli lista pracowników w tabelach tablic nie jest w tej samej kolejności? W tym przypadku, jak wspomniano wcześniej, jedną z opcji jest ustanowienie połączenia między komórkami, które mają być połączone ręcznie. Ale jest to odpowiednie tylko dla małych tabel. W przypadku ogromnych zakresów opcja ta w najlepszym wypadku zajmie bardzo dużo czasu, aw najgorszym - w praktyce nie będzie w ogóle możliwa. Ale możesz rozwiązać ten problem za pomocą kilku operatorów INDEKS - POZYCJA . Zobaczmy, jak można to zrobić, łącząc dane w zakresach tabelarycznych omawianych w poprzedniej metodzie.

1. Wybierz pierwszy element z kolumny "Zakład" . Przejdź do Kreatora funkcji, klikając ikonę "Wstaw funkcję" .
2. W kreatorze funkcji w grupie "References and arrays" znajdujemy i wybieramy nazwę "INDEX" .
3. Ten operator ma dwie formy: formularz do pracy z tablicami i odniesienie. W naszym przypadku wymagana jest pierwsza opcja, dlatego w kolejnym oknie wyboru formularza, które zostanie otwarte, wybierz je i kliknij przycisk "OK" .
4. Okno argumentu INDEKSU zostało uruchomione. Zadaniem tej funkcji jest wyprowadzenie wartości w wybranym zakresie w linii o podanej liczbie. Ogólna formuła operatora INDEX jest następująca:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   "Array" jest argumentem zawierającym adres zakresu, z którego będziemy pobierać informacje przez numer określonego łańcucha.

   "Numer linii" jest argumentem będącym numerem tej linii. Ważne jest, aby wiedzieć, że numer wiersza powinien być określony nie w stosunku do całego dokumentu, ale tylko w odniesieniu do wybranej tablicy.

   "Numer kolumny" jest argumentem opcjonalnym. Aby konkretnie rozwiązać nasz problem, nie będziemy go używać, dlatego nie jest konieczne oddzielne opisywanie jego istoty.

   Ustawiamy kursor w polu "Array" . Następnie przejdź do Arkusza 2 i przytrzymując lewy przycisk myszy, zaznacz całą zawartość kolumny "Zakład" .

5. Po wyświetleniu współrzędnych w oknie operatora, umieść kursor w polu "Numer linii" . Wyprowadzimy ten argument za pomocą operatora LIST . Dlatego kliknij trójkąt, który znajduje się po lewej stronie linii funkcyjnej. Wyświetlana jest lista ostatnio używanych operatorów. Jeśli znajdziesz wśród nich nazwę "SZUKAJ" , możesz ją kliknąć. W przeciwnym razie kliknij ostatni element na liście - "Inne funkcje ..." .
6. Otworzy się standardowe okno kreatora . Przechodzimy do tej samej grupy "Referencje i tablice". W tym momencie na liście wybierz pozycję "SZUKAJ" . Kliknij przycisk "OK" .
7. Aktywacja okna OPERATORÓW jest aktywowana . Podana funkcja ma wyświetlać numer wartości w określonej tablicy według jej nazwy. Dzięki tej funkcji obliczamy numer linii określonej wartości dla funkcji INDEKS . Składnia MATCH jest następująca:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

   "Żądana wartość" jest argumentem zawierającym nazwę lub adres komórki zewnętrznego zakresu, w którym się znajduje. Jest to pozycja tej nazwy w zakresie docelowym i należy ją obliczyć. W naszym przypadku pierwszym argumentem będzie odniesienie do komórek na Arkuszu 1 , w którym znajdują się imiona i nazwiska pracowników.

   "Zeskanowana tablica" jest argumentem, który jest odwołaniem do tablicy, w której przeszukiwana jest określona wartość w celu określenia jej pozycji. U nas ta rola będzie wykonywać adres kolumny " Nazwa" na Arkuszu 2 .

   "Typ dopasowania" jest argumentem opcjonalnym, ale w przeciwieństwie do poprzedniej instrukcji, ten opcjonalny argument będzie potrzebny. Wskazuje, w jaki sposób operator dopasuje wartość wyszukiwania do tablicy. Ten argument może mieć jedną z trzech wartości: -1 ; 0 ; 1 . W przypadku nieuporządkowanych tablic wybierz opcję "0" . Ta opcja jest odpowiednia w naszym przypadku.

   Zacznijmy więc wypełniać pola okna argumentów. Ustawiamy kursor w polu "Wartość wyszukiwania" , kliknij pierwszą komórkę w kolumnie "Nazwa" na Arkuszu 1 .

8. Po wyświetleniu współrzędnych ustawiamy kursor w polu "Skanowana tablica" i przechodzimy do etykiety "Arkusz 2" , która znajduje się w dolnej części okna Excel powyżej linii statusu. Zacisnąć lewy przycisk myszy i zaznaczyć kursorem wszystkie komórki w kolumnie "Nazwa" .
9. Po wyświetleniu współrzędnych w polu "Zeskanowana tablica" , przejdź do pola "Typ dopasowania" i ustaw tam numer "0" . Następnie ponownie wracamy do pola "Viewed array" . Chodzi o to, że wykonamy kopiowanie formuły, tak jak w poprzedniej metodzie. Adresy się przesuną, ale musimy poprawić współrzędne skanowanej macierzy. Nie powinien się przesuwać. Wybierz współrzędne za pomocą kursora i naciśnij klawisz funkcyjny F4 . Jak widać, znak dolara pojawił się przed współrzędnymi, co oznacza odniesienie od względnego do absolutnego. Następnie kliknij przycisk "OK" .
10. Wynik jest wyświetlany w pierwszej komórce kolumny "Zakład" . Ale zanim wykonamy kopię, musimy naprawić jeszcze jeden obszar, a mianowicie pierwszy argument funkcji INDEX . Aby to zrobić, wybierz element kolumny zawierający formułę i przejdź do wiersza formuły. Wybierz pierwszy argument operatora INDEX ( B2: B7 ) i kliknij przycisk F4 . Jak widać, znak dolara pojawił się w pobliżu wybranych współrzędnych. Klikamy klawisz Enter . Ogólnie formuła przyjęła następującą postać:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Teraz możesz kopiować za pomocą uchwytu do wypełniania. Nazywamy to w ten sam sposób, o którym mówiliśmy wcześniej i przedłużamy do końca zakresu tabel.
12. Jak widzimy, pomimo tego, że kolejność wierszy dla dwóch połączonych ze sobą tabel nie pokrywa się, jednak wszystkie wartości są zaostrzone według nazwisk pracowników. Osiągnięto to dzięki zastosowaniu kombinacji operatorów INDEX - POZYCJA .

Czytaj także:
Funkcja INDEKS w programie Excel
Funkcja EXPLORATION w Excelu

Metoda 3: Wykonywanie operacji matematycznych na powiązanych danych

Bezpośrednie powiązanie danych jest również dobre, ponieważ umożliwia nie tylko wyświetlanie wartości, które są wyświetlane w innych zakresach tabel w jednej z tabel, ale także wykonywanie z nimi różnych operacji matematycznych (dodawanie, dzielenie, odejmowanie, mnożenie itd.).

Zobaczmy, jak to się robi w praktyce. Zróbmy tak, aby na arkuszu 3 pojawiły się ogólne dane płac w przedsiębiorstwie bez podziału na pracowników. W tym celu stawki za personel zostaną pobrane z arkusza 2 , dodane (za pomocą funkcji SUMA ) i pomnożone przez współczynnik za pomocą formuły.

1. Wybierz komórkę, w której zostanie wyświetlony wynik obliczenia wynagrodzenia na arkuszu 3 . Kliknij przycisk "Wstaw funkcję" .
2. Powinieneś uruchomić okno Kreatora . Przechodzimy do grupy "Mathematical" i wybieramy tam nazwę "SUMM" . Następnie kliknij przycisk "OK" .
3. Funkcja SUMA zostaje przeniesiona do okna argumentów, które służy do obliczenia sumy wybranych liczb. Ma następującą składnię:

   =СУММ(число1;число2;…)

   Pola w oknie odpowiadają argumentom określonej funkcji. Choć ich liczba może osiągnąć 255 sztuk, ale dla naszego celu wystarczy tylko jeden. Ustawiamy kursor w polu "Numer1" . Kliknij etykietę "Arkusz 2" nad linią statusu.

4. Po przejściu do wybranej sekcji książki wybierz kolumnę, która ma zostać zsumowana. Robimy to za pomocą kursora, trzymając lewy przycisk myszy. Jak widać, współrzędne wybranego obszaru są natychmiast wyświetlane w polu argumentów. Następnie kliknij przycisk "OK" .
5. Następnie automatycznie przejdziemy do Arkusza 1 . Jak widać, całkowita kwota wynagrodzenia pracownika jest już wyświetlana w odpowiednim elemencie.
6. Ale to nie wszystko. Jak pamiętamy, wynagrodzenie obliczane jest przez pomnożenie stawki przez współczynnik. Dlatego ponownie wybieramy komórkę, w której znajduje się sumowanie. Następnie przejdź do paska formuły. Dodajemy symbol mnożenia ( * ) do formuły w nim, a następnie klikamy element, w którym znajduje się indeks współczynnika. Aby wykonać obliczenia, kliknij klawisz Enter na klawiaturze. Jak widać program obliczył całkowite wynagrodzenie dla przedsiębiorstwa.
7. Wróć do arkusza 2 i zmień stawkę każdego pracownika.
8. Potem wracamy do strony z całkowitą kwotą. Jak widać, w wyniku zmian w połączonej tabeli wynik całkowitej pensji został automatycznie ponownie obliczony.

Metoda 4: specjalna wkładka

Możesz także połączyć tablice tablicowe z Excelem za pomocą specjalnej wkładki.

1. Wybierz wartości, które chcesz "dokręcić" w innej tabeli. W naszym przypadku jest to zakres kolumny "Zakład" na arkuszu 2 . Kliknij wybrany fragment prawym przyciskiem myszy. Na otwartej liście wybierz pozycję "Kopiuj" . Alternatywna kombinacja to Ctrl + C. Następnie przejdź do Arkusza 1 .
2. Przechodząc do żądanego obszaru książki, zaznacz komórki, w które chcesz przeciągnąć wartości. W naszym przypadku jest to kolumna "Bet" . Kliknij wybrany fragment prawym przyciskiem myszy. W menu kontekstowym w oknie narzędziowym "Parametry wstawiania" kliknij ikonę "Wstaw łącze" .

   Istnieje również alternatywa. Nawiasem mówiąc, jest to jedyna wersja starszych wersji programu Excel. W menu kontekstowym ustawiamy kursor nad pozycją "Pasta specjalna" . W dodatkowym menu, które zostanie otwarte, wybierz element o tej samej nazwie.

3. Następnie otwiera się specjalne okno wstawiania. Kliknij przycisk "Wstaw łącze" w lewym dolnym rogu komórki.
4. Bez względu na wybraną opcję wartości z jednej tablicy tabeli zostaną wstawione do drugiej. Jeśli zmienisz dane w źródle, będą one również automatycznie zmieniać we wprowadzonym zakresie.

Lekcja: Specjalna wkładka w programie Excel

Metoda 5: związek między tabelami w kilku książkach

Ponadto możesz zorganizować łącze między obszarami tabel w różnych książkach. Używane jest specjalne narzędzie do wstawiania. Działania będą dokładnie takie same, jak te, które widzieliśmy w poprzedniej metodzie, z tym wyjątkiem, że nie będziesz musiał nawigować pomiędzy sekcjami tej samej książki, ale między plikami. Oczywiście wszystkie powiązane książki powinny być otwarte w tym samym czasie.

1. Wybierz zakres danych, które chcesz przenieść do innej książki. Kliknij prawym przyciskiem myszy i wybierz "Kopiuj" w otwartym menu.
2. Następnie przechodzimy do książki, w której te dane będą musiały zostać wstawione. Wybierz żądany zakres. Kliknij prawym przyciskiem myszy. W menu kontekstowym w grupie "Opcje wstawiania" wybierz element "Wklej link" .
3. Następnie zostaną wstawione wartości. Jeśli zmienisz dane w księdze źródłowej, tablica tabel ze skoroszytu automatycznie je pobierze. I nie jest konieczne, aby obie książki były otwarte na to. Wystarczy otworzyć tylko jeden skoroszyt i automatycznie pobierze dane z zamkniętego połączonego dokumentu, jeśli zostało wcześniej zmienione.

Należy jednak zauważyć, że w tym przypadku wstawienie będzie wykonane w postaci niezmiennych tablic. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные» . Щелкаем по значку «Изменить связи» , который расположен на ленте в блоке инструментов «Подключения» . Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь» .
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи» .
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать» . Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C .
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения» , которая размещена в группе инструментов «Параметры вставки» .
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

• ← Metody porównywania tabel w programie Microsoft Excel
• Kwadrat w programie Microsoft Excel →