Bazy Danych ·

Excel jako baza danych – do jakiego momentu to ma sens

Arkusz Excela pełniący funkcję prostej bazy klientów

Excel to arkusz kalkulacyjny, nie baza danych – ale można go tak używać do pewnego momentu

Excel nie jest bazą danych w technicznym sensie tego słowa (nie ma relacyjnego silnika zapytań, kontroli równoczesnego dostępu wielu użytkowników ani gwarancji integralności danych). Mimo to w praktyce bardzo wiele małych firm i jednoosobowych projektów trzyma swoją "bazę klientów" albo "bazę magazynową" właśnie w Excelu – i to działa, dopóki dane i liczba osób korzystających z pliku pozostają w rozsądnych granicach.

Jak zbudować prostą bazę danych w Excelu

Załóżmy bazę klientów:

IDNazwa firmyOsoba kontaktowaTelefonStatus
1ABC Sp. z o.o.Jan Kowalski600123456Aktywny
2XYZ HandelAnna Nowak600987654Nieaktywny

Krok 1: przekształć zakres w Tabelę Excela

Zaznacz dane i naciśnij Ctrl+T. Tabela Excela (nie tabela przestawna – to inna funkcja) daje kilka realnych korzyści względem zwykłego zakresu:

Krok 2: dodaj unikalny identyfikator (kolumna ID)

Każdy wiersz powinien mieć unikalną wartość w jednej kolumnie (np. numer klienta) – to jest "klucz", po którym możesz jednoznacznie odnaleźć konkretny wiersz, nawet jeśli nazwa firmy się zmieni albo się powtórzy.

Krok 3: walidacja danych – lista rozwijana dla kolumny Status

Bez kontroli nad tym, co ktoś wpisze w kolumnie "Status", z czasem pojawią się warianty "aktywny", "Aktywny", "AKTYWNY", "czynny" – i każda analiza na takiej kolumnie się rozpadnie. Rozwiązanie to lista rozwijana:

  1. Zaznacz zakres komórek w kolumnie Status.
  2. Dane > Sprawdzanie poprawności danych.
  3. W polu "Zezwól" wybierasz Lista.
  4. W polu "Źródło" wpisujesz dozwolone wartości oddzielone przecinkami, np. Aktywny,Nieaktywny,Potencjalny, albo wskazujesz zakres komórek z osobnej listy dozwolonych wartości.

Od tego momentu w kolumnie Status pojawia się strzałka, a wpisanie czegokolwiek poza listą jest blokowane (domyślnie) albo tylko ostrzega (jeśli zmienisz ustawienie "Alert o błędzie" na "Ostrzeżenie").

Baza magazynowa – przykład z liczeniem stanu

ID produktuNazwaStanPróg minimalnyStatus zapasu
P001Monitor 24"125
P002Klawiatura310

Kolumna "Status zapasu" może być formułą ostrzegającą o niskim stanie:

=JEŻELI(C2<D2;"Zamów";"OK")

Połączone z formatowaniem warunkowym (czerwone tło dla "Zamów"), taka tabela działa jak prosty system ostrzegania o stanach magazynowych – bez potrzeby osobnego oprogramowania, o ile liczba produktów jest rozsądna (dziesiątki, nie dziesiątki tysięcy pozycji, i nie więcej niż kilka osób aktualizujących plik naraz).

Relacyjna baza danych w Excelu – co to właściwie znaczy

Klasyczna baza relacyjna (jak w Accessie albo SQL Server) przechowuje dane w wielu, odrębnych tabelach połączonych relacjami – np. osobna tabela klientów i osobna tabela zamówień, połączone przez numer klienta, bez powtarzania danych klienta w każdym wierszu zamówienia. W Excelu taki model da się odwzorować przez Power Pivot (relacje między Tabelami Excela, jak w prawdziwej bazie relacyjnej) albo prościej – przez WYSZUKAJ.PIONOWO dociągające dane klienta do tabeli zamówień na podstawie wspólnego numeru klienta.

Drugie podejście jest prostsze, ale ma wadę: dane klienta (nazwa, adres) są "rozmnożone" w każdym wierszu zamówienia tego klienta, zamiast istnieć raz w jednej tabeli. To nie jest prawdziwa normalizacja relacyjna, tylko jej uproszczona imitacja – wystarczająca do małych zbiorów danych, kłopotliwa przy większych.

Kiedy Excel jako baza danych zaczyna szwankować

Gdzie szukać gotowych wzorów baz danych

Jeśli budujesz bazę klientów albo magazynu od zera, nie trzeba wymyślać struktury kolumn samodzielnie – warto zacząć od własnej listy pytań: jakie informacje faktycznie potrzebujesz śledzić, i czy każda z nich zasługuje na osobną kolumnę, czy da się ją wyliczyć formułą z innych kolumn (np. "Status zapasu" wyżej nie musi być wpisywany ręcznie – wynika z porównania dwóch innych kolumn). To pytanie, zadane na starcie, zwykle oszczędza więcej czasu niż szukanie gotowego szablonu, który i tak trzeba będzie dopasować do własnych potrzeb.

Przykład: dociąganie danych klienta do tabeli zamówień

Załóżmy dwie tabele w jednym skoroszycie: "Klienci" (ID klienta, Nazwa firmy, Region) i "Zamowienia" (ID zamówienia, ID klienta, Kwota). Żeby w tabeli zamówień zobaczyć nazwę firmy i region bez przepisywania ich ręcznie do każdego wiersza:

=WYSZUKAJ.PIONOWO(B2;Klienci!A:C;2;FAŁSZ)

To dociąga nazwę firmy (druga kolumna tabeli Klienci) na podstawie ID klienta z kolumny B tabeli zamówień. Analogiczna formuła z numerem kolumny 3 dociągnie region. Taki układ – jedna tabela z "głównymi" danymi klienta i druga z transakcjami – to już w praktyce uproszczona wersja relacji "jeden do wielu", znanej z prawdziwych baz danych.

Warianty tej samej bazy – z kolumną pomocniczą licznika zamówień

Jeśli chcesz wiedzieć, ile zamówień złożył każdy klient, bez budowania tabeli przestawnej, dodajesz w tabeli "Klienci" kolumnę pomocniczą:

=LICZ.JEŻELI(Zamowienia!B:B;A2)

To liczy, ile razy ID danego klienta występuje w tabeli zamówień. Wariant z sumą wartości zamówień tego klienta:

=SUMA.JEŻELI(Zamowienia!B:B;A2;Zamowienia!C:C)

Połączenie tych dwóch formuł daje prosty, działający "profil klienta" bez budowania osobnej tabeli przestawnej dla każdego raportu.

Częste błędy przy budowie baz danych w Excelu