Odwołania do innego arkusza odporne na usuwanie kolumn lub wierszy

Załóżmy, że mamy arkusz z danymi dostawców, który jest połączony z rejestrem faktur w innym arkuszu. Pobierane są do niego nazwy dostawców (kontrahentów) zaopatrujących szkołę w różnego rodzaju pomoce. Przy usuwaniu kontrahentów z bazy w zestawieniu faktur powstaje błąd #ADR!Natomiast po dodaniu danych nowego kontrahenta nie są one automatycznie pokazywane w zestawieniu i wszystkie łącza trzeba zmodyfikować.

Proponujemy zastosowanie odwołania za pomocą funkcji ADR.POŚR.

W tym celu:

1. W komórce A2 wpisz formułę:

=ADR.POŚR(ADRES(WIERSZ();1;;;"Kontrahenci"))

W przypadku zmiany nazwy arkusza Kontrahenci we wszystkich formułach zmodyfikujmy piąty argument funkcji ADRES. Nazwa arkusza w tej formule nie zmieni się automatycznie.

Jeśli chcemy wpisywać kontrahentów, poczynając nie od drugiego, ale od czwartego wiersza, to od funkcji WIERSZ odejmujemy liczbę 2 w pierwszym argumencie funkcji ADRES.

Jeżeli nazwy kontrahentów w arkuszu Kontrahenci nie będą zaczynały się od komórki A2, ale od C1, to od funkcji WIERSZ odejmujemy liczbę 1 w pierwszym argumencie funkcji ADRES oraz w drugim argumencie wpisujemy liczbę 3 oznaczającą trzecią kolumnę.

2. Skopiuj komórkę A2 i wklej ją do komórek niżej, np. poprzez zaznaczenie tej komórki i przeciągnięcie myszą prawej dolnej krawędzi komórki. Jeśli jest w planie dopisywanie wielu kontrahentów, to skopiuj tę formułę odpowiednio daleko. Na końcu zestawienia pojawią się wówczas wartości zerowe.

3. Aby je ukryć, wybierz Przycisk pakietu Office/Opcje programu Excel i w zakładce Zaawansowane, w sekcji Opcje wyświetlania dla tego arkusza opcję Pokaż zero w komórkach o zerowej wartości zmień na niezaznaczoną. Następnie naciśnij OK, aby zamknąć okno i zatwierdzić zmiany.

Po tych modyfikacjach arkusz z zestawieniem faktur będzie automatycznie uwzględniał każdego nowego kontrahenta wpisanego do arkusza Kontrahenci i jednocześnie w zestawieniu faktur nie będzie pojawiał się błąd, jeśli jakiś kontrahent zostanie usunięty z bazy kontrahentów.

R12Wj1F3OiHvu

Nagranie filmowe dotyczące odwołania do innego arkusza odpornego na usuwanie kolumn lub wierszy

Nagranie filmowe dotyczące odwołania do innego arkusza odpornego na usuwanie kolumn lub wierszy

Film dostępny pod adresem /preview/resource/R12Wj1F3OiHvu

Nagranie filmowe dotyczące odwołania do innego arkusza odpornego na usuwanie kolumn lub wierszy

Pobierz przykładowe dane:

Ćwiczenie 1

Posiadasz dużą bibliotekę gier, książek, filmów itp. Twoi koledzy często pożyczają od ciebie różne tytuły i niestety zdarza się tak, że nie są one zwracane. Stwórz dwa arkusze. Jeden ma zawierać tytuły wraz z ich indywidualnymi identyfikatorami, a drugi informacje o tym kto wypożyczył dany tytuł.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Zawartość arkusza „Książki” :

Rl2HUc7ZwR4An

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł NUMER. W komórkach od A2 do A5 wprowadzono kolejne liczby porządkowe. W kolumnie B, w komórce B1 wpisano tytuł TYTUŁ. W komórkach od B2 do B5 wpisano kolejne tytuły książek z serii o Harrym Potterze.

Zawartość arkusza „Wypożyczenia” :

R6tBtynAKClbo

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W ARKUSZU kolejno dodano opisy. W kolumnie A, w komórce A1 wpisano tytuł IMIĘ. W komórkach od A2 do A3 wprowadzono imiona. W kolumnie B, w komórce B1 wpisano tytuł TYTUŁ. W komórce B2 wprowadzono formułę, która brzmi następująco: =ADR.POŚR(ADRES(1+3;2;;;”Książki”))

2

Ćwiczenie 2

Zmodyfikuj arkusz z poprzedniego zadania. W drugim arkuszu dodaj daty wypożyczenia i ewentualnego oddania danego tytułu. Jeżeli dany tytuł jest wypożyczony, wtedy w pierwszym arkuszu powinna się pojawić odpowiednia informacja.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Zawartość arkusza „Książki” :

R3jGZSZ6HDRr3

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł NUMER. W komórkach od A2 do A5 wprowadzono kolejne numery tomów książek. W kolumnie B, w komórce B1 wpisano tytuł TYTUŁ. W komórkach od B2 do B5 wprowadzono tytuły książek. W kolumnie C, w komórce C1 wpisano tytuł CZY WYPOŻYCZONE. W komórce C2 wpisano formułę do obliczenia, brzmi ona następująco: =LICZ.WARUNKI(Wypożyczenia!$D$2:$D$1048576;”-”;Wypożyczenia!$B$2:$B$1048576;Książki!B2)

Zawartość arkusza „Wypożyczenia” :

RpPkSjWhwqn8t

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł IMIĘ. W komórkach od A2 do A3 wprowadzono imiona osób, które wypożyczyły książki. W kolumnie B, w komórce B1 wpisano tytuł TYTUŁ. W komórkach od B2 do B3 wprowadzono tytuły książek. W kolumnie C, w komórce C1 wpisano tytuł DATA WYPOŻYCZENIA. W komórkach C2 i C3 wpisano daty. W kolumnie D, w komórce D1 wpisano tytuł DATA ODDANIA. W komórce D2 wpisano datę.