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 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ę.
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.
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
Pobierz przykładowe dane:
RBkrOjaDer1ZA
Ć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ł.
Przykładowe rozwiązanie zadania:
Zawartość arkusza „Książki” :
Rl2HUc7ZwR4An
Zawartość arkusza „Wypożyczenia” :
R6tBtynAKClbo
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.