Skalowanie Osi i Danych W Excelu Z Użyciem Proporcji
Ta sekcja pokazuje skalowanie osi i danych w arkuszu Excel z użyciem proporcji. Dostajesz czyste wzory matematyczne, gotowe formuły do komórek oraz przykłady danych przed skalowaniem i po skalowaniu. Nie ma tu wykresów graficznych. Wszystko opiera się na proporcji bezpośredniej i na prostych przekształceniach liniowych w tabelach.
Cel i zakres
Celem jest poprawne przeskalowanie danych i osi w raportach tak aby zachować stały stosunek wartości. Opis obejmuje trzy typowe scenariusze. Proste przeskalowanie przez stały współczynnik. Dopasowanie przedziału wejściowego do innego przedziału wyjściowego. Normalizacja do procentów w skali od zera do stu. Każdy przypadek ma wzór w notacji matematycznej oraz formułę w Excelu.
Model proporcji bezpośredniej
Proporcja bezpośrednia zapisuje zależność \( y = kx \) dla \( k > 0 \). Z podstawowej równości stosunków \( \frac{A}{B} = \frac{C}{D} \) otrzymujesz równość iloczynów \( A \cdot D = B \cdot C \). W praktyce skaluje się wartości przez stały współczynnik \( k \).
Jeżeli chcesz przeskalować dane o współczynnik \( k \), użyj wzoru \( y = kx \). Jeżeli chcesz dopasować dane z zakresu \( [a,b] \) do zakresu \( [c,d] \), użyj wzoru \( y = \frac{(x – a)(d – c)}{b – a} + c \). Dla normalizacji do procentów użyj \( y = 100 \cdot \frac{x – a}{b – a} \).
Przeskalowanie danych przez stały współczynnik
Ten wariant stosujesz gdy zmieniasz jednostkę albo chcesz uzyskać prosty przelicznik. Przykład to zamiana kilogramów na gramy albo przeliczenie kwot po stałym kursie. W tabeli poniżej znajduje się przykład z danymi wejściowymi, komórką współczynnika oraz wynikiem po skalowaniu.
Tabela danych i formuł
| Komórka | Zawartość | Uwagi |
|---|---|---|
| A1 | Nagłówek Dane wejściowe | etykieta |
| A2 | 2,4 | wartość x |
| A3 | 5 | wartość x |
| A4 | 7,25 | wartość x |
| E1 | Nagłówek Współczynnik k | etykieta |
| F1 | 1000 | na przykład zamiana kg na g |
| B1 | Nagłówek Wynik y | etykieta |
| B2 | =A2*$F$1 | formuła mnożenia przez k |
| B3 | =A3*$F$1 | kopiuj w dół |
| B4 | =A4*$F$1 | kopiuj w dół |
Adres absolutny \( \$F\$1 \) blokuje współczynnik w kopiowanych formułach. Po obliczeniu możesz zastosować funkcję ZAOKR jeżeli kontekst wymaga określonej liczby miejsc po przecinku, na przykład =ZAOKR(B2;2).
Dopasowanie danych z jednego przedziału do innego
To liniowe przeskalowanie z przesunięciem. Użyjesz go gdy chcesz zamknąć dane w nowym zakresie. Przykład to dopasowanie wartości z zakresu od dziesięciu do pięćdziesięciu do zakresu od zera do stu. Wzór jest liniowy i zachowuje proporcje różnic.
Wzór i mapowanie zakresów
\( y = \frac{(x – a)(d – c)}{b – a} + c \) gdzie \( x \) to wartość wejściowa, \( [a,b] \) to zakres wejściowy, \( [c,d] \) to zakres docelowy. Dla normalizacji do procentów przyjmij \( c = 0 \) oraz \( d = 100 \).
Tabela z formułami w Excelu
| Komórka | Zawartość | Uwagi |
|---|---|---|
| A1 | Nagłówek Dane wejściowe | etykieta |
| A2 | 10 | min wejścia a |
| A3 | 50 | max wejścia b |
| A5 | 12 | przykład x |
| A6 | 38 | przykład x |
| C1 | Nagłówek Przedział docelowy | etykieta |
| C2 | 0 | min wyjścia c |
| C3 | 100 | max wyjścia d |
| D1 | Nagłówek Wynik y | etykieta |
| D5 | =((A5-$A$2)*($C$3-$C$2))/($A$3-$A$2)+$C$2 | formuła liniowego dopasowania |
| D6 | =((A6-$A$2)*($C$3-$C$2))/($A$3-$A$2)+$C$2 | kopiuj w dół |
Formuła działa poprawnie tylko wtedy gdy \( b \neq a \). Jeżeli zakres wejściowy jest stały w całym raporcie, trzymaj wartości a b c d w jednej sekcji i odwołuj się do nich przez adresy absolutne.
Normalizacja do procentów z użyciem proporcji
Normalizacja umieszcza każdą wartość w skali od zera do stu. Dzięki temu możesz porównywać różne kolumny bez mieszania jednostek. Jest to zwykła proporcja bezpośrednia na różnicach względem minimum.
Wzór i formuła w komórkach
\( y = 100 \cdot \frac{x – a}{b – a} \) gdzie \( a = \min(X) \) a \( b = \max(X) \). Dla stabilnej skali użyj stałych a oraz b wyliczonych raz dla całej tabeli. Dla skali ruchomej użyj funkcji MIN i MAX w formułach kolumnowych.
| Komórka | Zawartość | Uwagi |
|---|---|---|
| H1 | Nagłówek Dane | etykieta |
| H2 | 24 | wartość |
| H3 | 32 | wartość |
| H4 | 45 | wartość |
| J1 | Nagłówek Procent | etykieta |
| J2 | =(H2-MIN($H$2:$H$4))/(MAX($H$2:$H$4)-MIN($H$2:$H$4))*100 | formuła normalizacji |
| J3 | =(H3-MIN($H$2:$H$4))/(MAX($H$2:$H$4)-MIN($H$2:$H$4))*100 | kopiuj w dół |
| J4 | =(H4-MIN($H$2:$H$4))/(MAX($H$2:$H$4)-MIN($H$2:$H$4))*100 | kopiuj w dół |
Skalowanie osi bez rysowania wykresów
Nawet gdy nie wstawiasz wykresu, musisz wiedzieć jak wyglądałaby oś po skalowaniu. Ustawienia osi w Excelu wynikają z proporcji, więc planowanie skali można zapisać w tabeli parametrów. Poniżej masz prostą matrycę ustawień, którą przeniesiesz do arkusza z formularzem.
Matryca parametrów osi
| Parametr | Opis | Wartość zalecana |
|---|---|---|
| Min osi | Początek zakresu wyświetlania | równe minimum danych po skalowaniu |
| Max osi | Koniec zakresu wyświetlania | równe maksimum danych po skalowaniu |
| Jednostka główna | Odstęp między opisami na osi | stała równomierna dzieląca zakres przez pięć albo dziesięć |
| Jednostka pomocnicza | Drobniejsze znaczniki | część jednostki głównej na przykład jedna druga albo jedna piąta |
Jeżeli stosujesz normalizację do procentów, ustaw przedział od zera do stu i jednostkę główną równą dziesięć. Wtedy wszystkie tabele raportowe mają wspólną skalę opisową i łatwo je porównać.
Skalowanie z przesunięciem a proporcja bezpośrednia
Zdarzają się przeliczenia z niezerowym przesunięciem, na przykład stopnie Celsjusza do stopni Fahrenheita. To wciąż zależność liniowa, ale nie jest to czysta proporcja bezpośrednia, bo prosta nie przechodzi przez początek układu. W takim razie używasz modelu liniowego z przesunięciem.
Wzór i formuła dla przeliczenia z przesunięciem
\( y = ax + b \) gdzie \( a \) to współczynnik skali a \( b \) to przesunięcie. Dla przeliczenia C na F masz \( y = 1{,}8x + 32 \ ). W Excelu wpiszesz na przykład =1,8*A2+32 dla każdej wartości w kolumnie.
Błędy i kontrola jakości
Mieszanie jednostek
Najpierw ujednolić jednostki. Kilogramy i gramy albo litry i mililitry muszą być w tym samym systemie jeszcze przed wstawieniem do formuły. Inaczej otrzymasz wynik, który nie ma sensu.
Zły przedział wejściowy
Formuła dopasowania wymaga prawidłowego minimum i maksimum. Jeżeli zakres wejściowy został policzony z błędnych wierszy, cały wynik będzie przesunięty. Zrób krótki test na dwóch punktach brzegowych aby potwierdzić dopasowanie.
Zaokrąglanie w trakcie
Zaokrąglaj wyniki dopiero po obliczeniu wartości końcowej. W trakcie obliczeń trzymaj pełną precyzję, bo inaczej proporcja rozjedzie się na ostatnim etapie.
Mylenie proporcji bezpośredniej z odwrotną
Gdy rośnie jedna wielkość i druga maleje, nie ma proporcji bezpośredniej, tylko zależność odwrotna. Przed zastosowaniem formuły sprawdź opis zjawiska i wybierz właściwy model.
Przykłady gotowe do wklejenia
Poniżej masz trzy kompletne bloki danych. Każdy blok zawiera małą próbkę wejścia, parametry skalowania i wynik. Wystarczy wkleić do arkusza i dostosować zakresy oraz adresy absolutne.
Blok A proste przeskalowanie współczynnikiem
| Wejście x | k | Formuła | Wynik y |
|---|---|---|---|
| 1,2 | 2 | =A2*$B$1 | 2,4 |
| 3,5 | 2 | =A3*$B$1 | 7 |
| 9 | 2 | =A4*$B$1 | 18 |
Blok B dopasowanie przedziału do zera i stu
| Wejście x | a | b | Formuła | Procent |
|---|---|---|---|---|
| 10 | 10 | 50 | =(A2-$B$2)/($B$3-$B$2)*100 | 0 |
| 30 | 10 | 50 | =(A3-$B$2)/($B$3-$B$2)*100 | 50 |
| 50 | 10 | 50 | =(A4-$B$2)/($B$3-$B$2)*100 | 100 |
Blok C skalowanie z przesunięciem
| Wejście x | a | b | Formuła | Wynik y |
|---|---|---|---|---|
| -10 | 1,8 | 32 | =1,8*A2+32 | 14 |
| 0 | 1,8 | 32 | =1,8*A3+32 | 32 |
| 25 | 1,8 | 32 | =1,8*A4+32 | 77 |
Zadania do samodzielnej pracy z rozwiązaniami
Każde zadanie ma krótką treść, wzór i rozwiązanie liczone wprost w tabeli. To są wzory do natychmiastowego użycia w arkuszu.
| # | Treść | Wzór matematyczny | Formuła w Excelu | Odpowiedź |
|---|---|---|---|---|
| 1 | Przeskaluj 2,4 przez 1000 | \( y = kx \) z \( k = 1000 \) | =A2*$B$1 | 2400 |
| 2 | Dopasuj 30 z zakresu od dziesięciu do pięćdziesięciu do skali od zera do stu | \( y = \frac{(30-10)\cdot(100-0)}{50-10}+0 \) | =(30-10)/(50-10)*100 | 50 |
| 3 | Przeskaluj wartości 5 7 12 współczynnikiem 2,5 | \( y = 2{,}5x \) | =A2*$B$1 | 12,5 17,5 30 |
| 4 | Normalizuj 24 32 45 do procentów w kolumnie | \( y = 100 \cdot \frac{x – a}{b – a} \) | =(H2-MIN($H$2:$H$4))/(MAX($H$2:$H$4)-MIN($H$2:$H$4))*100 | 0 44,44 100 |
| 5 | Dopasuj 72 z zakresu od 50 do 150 do przedziału od 0 do 10 | \( y = \frac{(72-50)\cdot(10-0)}{150-50}+0 \) | =(72-50)/(150-50)*10 | 2,2 |
| 6 | Utwórz kolumnę mieszkań w m z kolumny w cm przez podział przez 100 | \( y = \frac{x}{100} \) | =A2/100 | wartości w metrach |
| 7 | Przelicz wartości netto na brutto przy stałej stawce 23 procent | \( y = x \cdot 1{,}23 \) | =A2*1,23 | brutto dla każdej pozycji |
| 8 | Skaluj serię danych tak aby maksimum wynosiło 100 bez zmiany proporcji | \( y = \frac{100}{b}x \) | =A2/(MAX($A$2:$A$100))*100 | seria ograniczona do 100 |
| 9 | Skaluj serię danych tak aby suma wyniosła 100 | \( y = \frac{100}{S}x \) gdzie \( S = \sum x \) | =A2/SUMA($A$2:$A$100)*100 | udziały procentowe |
| 10 | Sprawdź poprawność przeskalowania dwóch punktów brzegowych | \( f(a) = c \) oraz \( f(b) = d \) | =(A2-$A$2)/($A$3-$A$2)*($C$3-$C$2)+$C$2 | zgodność z zakładanym zakresem |
Podsumowanie
Skalowanie w Excelu to proste wzory proporcji bezpośredniej i liniowego dopasowania. Wystarczy poprawnie określić współczynnik skali albo parę granic zakresu, wpisać formuły z adresami absolutnymi i dopiero na końcu zastosować zaokrąglenia. To gwarantuje spójność tabel i łatwą kontrolę jakości wyników.
