Modelowanie, Solver i podstawy makr (VBA)

Solver to dodatek Excela do rozwiązywania zadań optymalizacyjnych: znajduje wartości komórek zmiennych, które maksymalizują lub minimalizują wynik formuły docelowej, przy zachowaniu zadanych ograniczeń. To praktyczne zastosowanie programowania liniowego poznawanego na matematyce.

2. Elementy modelu w Solverze

Element	Rola	Przykład
Komórka celu	Formuła, którą chcemy zmaksymalizować/zminimalizować	Łączny zysk ze sprzedaży
Komórki zmienne	Wartości, które Solver może modyfikować	Liczba sztuk produktu A i B
Ograniczenia	Warunki, których nie wolno przekroczyć	Budżet ≤ 10 000 zł, produkcja ≥ 0

Przykład problemu optymalizacyjnego

Piekarnia produkuje chleb (zysk 3 zł/szt., 2 kg mąki) i bułki (zysk 1 zł/szt., 0.2 kg mąki). Ma dostępne 100 kg mąki i maksymalnie 8h pracy piekarza (chleb: 10 min/szt., bułki: 2 min/szt.). Solver znajdzie liczbę sztuk każdego produktu maksymalizującą łączny zysk, respektując oba ograniczenia.

Konfiguracja w Excelu

Dane → Solver Ustaw cel: $D$1 (formuła zysku) Do: Maks. Komórki zmienianych decyzji: $B$1:$B$2 (liczba chlebów, liczba bułek) Podlega ograniczeniom: $C$1 <= 100 (zużycie mąki <= dostępna ilość) $C$2 <= 480 (zużycie czasu w minutach <= 8h) $B$1:$B$2 >= 0 (nie można produkować ujemnej liczby sztuk)

3. Wprowadzenie do makr VBA

Makro to nagrany lub napisany ciąg poleceń automatyzujący powtarzalne czynności w Excelu — np. formatowanie raportu co tydzień w identyczny sposób. VBA (Visual Basic for Applications) to język, w którym te makra są zapisywane.

Nagrywanie makra (bez pisania kodu)

Krok po kroku

Deweloper → Zarejestruj makro → nadaj nazwę → wykonaj czynności (np. formatowanie komórek, wstawienie sumy) → Deweloper → Zatrzymaj rejestrowanie. Excel automatycznie zapisuje wykonane kroki jako kod VBA, który można uruchomić ponownie jednym kliknięciem.

Prosty kod VBA napisany ręcznie

Sub PokolorujOceny() Dim i As Integer For i = 2 To 30 If Cells(i, 2).Value < 3 Then Cells(i, 2).Interior.Color = RGB(255, 200, 200) ' czerwone tło dla ocen niedostatecznych ElseIf Cells(i, 2).Value >= 5 Then Cells(i, 2).Interior.Color = RGB(200, 255, 200) ' zielone tło dla piątek i szóstek End If Next i End Sub

Ten kod przechodzi przez wiersze 2–30 w kolumnie B i koloruje komórki w zależności od wartości oceny — coś, co ręcznie zajęłoby kilka minut, makro wykona w ułamku sekundy.

4. Ćwiczenia praktyczne

Ćwiczenie 1 — zbuduj model optymalizacyjny

Zbuduj w Excelu arkusz z modelem piekarni opisanym w sekcji 2 (formuły na zużycie mąki, czasu i zysku) i uruchom Solver, aby znaleźć optymalną liczbę chlebów i bułek do wyprodukowania.

Ćwiczenie 2 — nagraj własne makro

Nagraj makro, które formatuje zaznaczony zakres komórek: pogrubia nagłówek, dodaje obramowanie i ustawia szerokość kolumn na "Automatyczne dopasowanie". Przetestuj je na dowolnym zestawie danych.

Sprawdź wiedzę

1. Co to jest komórka celu w konfiguracji Solvera?

Komórka, której wartość Solver może dowolnie zmieniać Formuła, którą Solver ma zmaksymalizować lub zminimalizować Komórka zawierająca ograniczenie

2. W jakim języku pisane są makra w Excelu?

Python VBA (Visual Basic for Applications) JavaScript

3. Jaka jest zaleta nagrywania makra zamiast pisania kodu VBA ręcznie?

Nagrane makro działa szybciej Nie trzeba znać składni VBA — Excel sam generuje kod na podstawie wykonanych czynności Nagrane makra są bezpieczniejsze

1. Czym jest Solver?