Przejdź do treści
Zaawansowane formuły w Excelu: X.WYSZUKAJ, tablice dynamiczne, LET i LAMBDAModuł 1 · lekcja 1 z 3
Za darmo4 min czytania

Moduł 1 · Fundamenty: odwołania, typy danych i tabele

Odwołania względne, bezwzględne i mieszane

Po tej lekcji świadomie wybierzesz odwołanie względne, bezwzględne albo mieszane i zbudujesz formułę, którą da się skopiować na cały zakres bez ręcznych poprawek. To fundament wszystkich kolejnych modułów: każda zaawansowana formuła psuje się najpierw na źle zablokowanym adresie.

W całym kursie pracujesz na danych fikcyjnej hurtowni oświetlenia Lumen z Poznania: cennik 20 produktów, 360 faktur z 2026 roku, pięcioro handlowców i 24 klientów z całej Polski. Pliki ćwiczeniowe znajdziesz w materiałach kursu, po jednym na moduł. Każdy ma arkusz „Instrukcja” z zadaniami i arkusz „Kontrola” z wynikami, z którymi porównasz swoje obliczenia.

Przykłady zapisujemy w polskiej wersji Excela: separatorem argumentów jest średnik, a separatorem dziesiętnym przecinek. Jeśli Twój Excel ma angielskie ustawienia regionalne, zamień średniki na przecinki.

Jak Excel „przesuwa” adres przy kopiowaniu

Excel nie zapamiętuje w formule adresu „A2” jako stałego miejsca. Zapamiętuje przesunięcie: „komórka dwie kolumny w lewo, w tym samym wierszu”. Dlatego formuła =B2*C2 wpisana w D2 i skopiowana do D3 zamienia się w =B3*C3. To jest odwołanie względne i w większości kolumn obliczeniowych właśnie tego chcesz.

Kłopot pojawia się, gdy jedna z wartości leży w jednej, stałej komórce. Przykład z arkusza „Cennik”: cena sprzedaży to cena zakupu powiększona o narzut zapisany w H1. Formuła =D2*(1+H1) działa w pierwszym wierszu, ale po skopiowaniu w dół zamienia się w =D3*(1+H2), a H2 jest pusta. Wynik: cena sprzedaży równa cenie zakupu i żadnego komunikatu o błędzie. Taki cichy błąd jest groźniejszy niż #ARG!, bo nikt go nie zauważy w raporcie.

Odwołanie bezwzględne: jedna komórka z parametrem

Znak dolara blokuje część adresu. $H$1 oznacza „zawsze kolumna H i zawsze wiersz 1”. Poprawna formuła w E2 arkusza „Cennik”:

Tekst
=D2*(1+$H$1)

Po skopiowaniu w dół cena zakupu przesuwa się (D3, D4…), a narzut zostaje w H1. Gdy zmienisz narzut z 35% na 30%, przeliczą się wszystkie ceny naraz. To jest dobra praktyka modelowania: parametry, które mogą się zmienić (narzut, kurs waluty, stawka prowizji), trzymasz w osobnych, opisanych komórkach, a nie wpisujesz liczby do formuły. W module 6 poznasz zapis tych parametrów w nazwach i w funkcji LET.

Zamiast pisać dolary ręcznie, ustaw kursor na adresie w pasku formuły i naciśnij F4. Każde kolejne naciśnięcie przełącza: $H$1 → H$1 → $H1 → H1.

Trzy rodzaje odwołań w formułach Excela

F4 przełącza rodzaje odwołania w zaznaczonym adresie.

Odwołania mieszane: tabela dwuwymiarowa jedną formułą

Najwięcej czasu oszczędzają odwołania mieszane. Arkusz „Rabaty” to typowa tabela ofertowa: w B1 cena jednostkowa netto 48,50 zł, w kolumnie A ilości (10, 20, 50, 100 sztuk), w wierszu 2 rabaty (0%, 5%, 10%, 15%). Chcesz wypełnić B3:E6 wartościami zamówień.

Zamiast szesnastu osobnych formuł piszesz jedną w B3:

Tekst
=$B$1*$A3*(1-B$2)
  • $B$1: cena, zawsze ta sama komórka.
  • $A3: ilość. Kolumna A zablokowana, bo ilości są zawsze w kolumnie A; wiersz wolny, bo w każdym wierszu jest inna ilość.
  • B$2: rabat. Wiersz 2 zablokowany, bo rabaty są zawsze w wierszu 2; kolumna wolna.

Skopiuj B3 na cały zakres B3:E6. Kontrola: dla 50 sztuk przy rabacie 10% wynik to 2 182,50 zł (48,50 × 50 × 0,9).

Prosta reguła na co dzień: zadaj sobie dwa pytania. „Czy przy kopiowaniu w prawo ten adres ma się przesuwać?” Jeśli nie, dolar przed literą kolumny. „Czy przy kopiowaniu w dół ma się przesuwać?” Jeśli nie, dolar przed numerem wiersza.

Pułapki, które warto znać

Wstawianie wierszy i kolumn. Excel aktualizuje adresy, także bezwzględne, gdy wstawisz wiersz nad komórką z parametrem. $H$1 zamieni się wtedy w $H$2 i formuły dalej działają. Dolar chroni przed kopiowaniem, nie przed zmianami struktury arkusza.

Odwołania do innego arkusza. Zapis =Cennik!$H$1 działa tak samo. Gdy nazwa arkusza ma spację, Excel dopisze apostrofy: ='Cennik 2026'!$H$1.

Wycinanie a kopiowanie. Gdy wycinasz komórkę (Ctrl+X) i wklejasz ją w inne miejsce, formuła zachowuje oryginalne adresy, niezależnie od dolarów. Zmienia się tylko przy kopiowaniu.

Sprawdzanie. Zaznacz komórkę i naciśnij F2: Excel pokoloruje komórki, do których odwołuje się formuła. Szybko zobaczysz, czy narzut wskazuje H1, czy pustą komórkę obok.

Jak dobrać dolary w formule przed skopiowaniem

Najważniejsze w 3 punktach

  1. Odwołanie względne przesuwa się przy kopiowaniu, bezwzględne ($H$1) zostaje w miejscu, mieszane blokuje tylko kolumnę albo tylko wiersz.
  2. Parametry (narzut, kurs, stawka) trzymaj w osobnych komórkach i odwołuj się do nich bezwzględnie, zamiast wpisywać liczby do formuł.
  3. Jedna formuła z odwołaniami mieszanymi (=$B$1*$A3*(1-B$2)) wypełnia całą tabelę dwuwymiarową.

Źródła

Materiały do pobrania

Pliki do ćwiczeń są dostępne w abonamencie.

  • Cennik, tabela rabatów, import z ERP i 360 fakturModuł 1: odwołania względne, bezwzględne i mieszane, liczby jako tekst, tabele Excela i nazwy34 KBDostępne w abonamencie
  • Sprzedaż 2026 (360 faktur) i plany handlowcówModuł 2: WARUNKI, PRZEŁĄCZ, SUMA.WARUNKÓW, LICZ.WARUNKI, MAKS.WARUNKÓW, SUMA.ILOCZYNÓW32 KBDostępne w abonamencie
  • Cennik, zamówienia, progi rabatów, kursy EUR i stany magazynówModuł 3: X.WYSZUKAJ, INDEKS, PODAJ.POZYCJĘ, X.DOPASUJ, JEŻELI.ND i JEŻELI.BŁĄD12 KBDostępne w abonamencie
  • Sprzedaż 2026 do formuł dynamicznychModuł 4: FILTRUJ, UNIKATOWE, SORTUJ, SORTUJ.WEDŁUG, MAX.K, SEKWENCJA, STOS.PION, WYBIERZ.KOLUMNY31 KBDostępne w abonamencie
  • Eksport z systemu, faktury z terminami i święta 2026Moduł 5: TEKST.PRZED, TEKST.PO, PODZIEL.TEKST, dni robocze ze świętami, wiekowanie należności11 KBDostępne w abonamencie
  • Prowizje, faktury do funkcji BRUTTO i arkusz do przegląduModuł 6: LET, LAMBDA w Menedżerze nazw, dobre praktyki i funkcje zmienne33 KBDostępne w abonamencie
  • Projekt końcowy: dane do raportu sprzedaży kwartałuModuł 7: tabele sprzedaży, klientów, produktów, planów i świąt; arkusz Kontrola z wynikami36 KBDostępne w abonamencie

Następna: Typy danych, liczby jako tekst i kolejność działań

20 lekcji, pliki i certyfikat w abonamencie.

Program kursu

Karta wymagana · anulujesz jednym kliknięciem · Mam już konto