JAK PROSTO I SKUTECZNIE WYKORZYSTAĆ ARKUSZ KALKULACYJNY DO OBLICZENIA PARAMETRÓW PROSTEJ METODĄ NAJMNIEJSZYCH KWADRATÓW

Na tej stronie dowiesz się jak wykorzystać wbudowane funkcje arkusza kalkulacyjnego (OpenOffice Calc albo MS Excel) do szybkiego i skutecznego wyznaczenia parametrów prostej metodą najmniejszych kwadratów oraz do obliczania błędów tych parametrów.

1. Wprowadź dane, odpowiadające zmiennym x i y do arkusza kalkulacyjnego.

2. Sprawdź poprawność wpisanych liczb. Może Ci w tym pomóc stworzenie wykresu. Zaznacz obszar arkusza, który zawiera Twoje dane i z menu „Wstaw” wybierz pozycję „Wykres”. Następnie typ wykresu ustal na „XY”. Inne opcje możesz pozostawić w domyślnych ustawieniach. Na wykresie łatwo dostrzec większość błędów powstałych podczas nieprawidłowego przepisywania danych (z przyrządów pomiarowych na kartkę, a potem z kartki do komputera). Objawiają się one jako punkty wyraźnie odstające od prostoliniowego charakteru zależności y(x). Współrzędne takich punktów należy poprawić, a jeśli jest to niemożliwe (pomyłka nastąpiła w trakcie wykonywania pomiarów) to wadliwy punkt trzeba usunąć, aby nie fałszował wyników obliczeń.

3. W obszarze arkusza niezajętym przez dane zaznacz fragment o wymiarach co najmniej 2x2 komórki. Następnie (nie likwidując zaznaczenia!) wpisz następującą formułę

=REGLINP(ZAKRES_DANYCH_Z_WARTOŚCIAMI_Y;ZAKRES_DANYCH_Z_WARTOŚCIAMI_X;PRAWDA;PRAWDA)

i wciśnij Ctrl+Shift+Enter (tzn. trzymając wciśnięte klawisze Ctrl oraz Shift wciśnij klawisz Enter). Jako ZAKRES_DANYCH_Z_WARTOŚCIAMI_Y należy podać zakres komórek, w których znajdują się współrzędne y punktów pomiarowych (np. C6:C14). Analogicznie jako ZAKRES_DANYCH_Z_WARTOŚCIAMI_X podajemy zakres komórek zawierających współrzędne x punktów pomiarowych (np. B6:B14).

Przedostatni argument formuły REGLINP określa, czy wyraz wolny w równaniu prostej (y=ax+b) ma mieć koniecznie wartość 0. Jeśli podamy w tym miejscu PRAWDA, to wyraz wolny będzie obliczany normalnie. Jeśli podamy FAŁSZ, to wartość b zostanie ustalona na 0. Należy unikać wymuszania b=0 nawet wtedy, gdy teoretyczna zależność y(x) nie zawiera wyrazu wolnego. Może się bowiem zdarzyć (i często tak się dzieje), że pomiary obarczone są pewnym błędem systematycznym, który „odsuwa” całą prostą od punktu (0,0). W takim przypadku wymuszenie zerowej wartości b da gorszą wartość współczynnika a niż w przypadku. gdy jest on obliczany dla ogólnego równania prostej.

Ostatni argument formuły REGLINP informuje ją o konieczności obliczenia dodatkowych wartości statystycznych, w tym błędów współczynników a oraz b. Parametr ten należy zawsze podawać jako PRAWDA.

4. Jeśli poprzedni krok został poprawnie wykonany, to w zaznaczonych komórkach pojawiły się pewne liczby. Ich sens jest następujący. W pierwszym wierszu pierwszej kolumny znajduje się wartość współczynnika kierunkowego a (dla prostej y=ax+b), natomiast w drugim wierszu tej samej kolumny - jego błąd. W pierwszym wierszu drugiej kolumny dostaliśmy wartość wyrazy wolnego b, a w drugim wierszu tej kolumny - błąd tegoż wyrazu.

Jeśli zaznaczony został obszar o wymiarach większych niż 2x2 komórki, to w pozostałych polach pojawiają się dalsze informacje, których sens można pojąć dysponując pewną wiedzą z zakresu statystyki. Nie są one wykorzystywane przy sporządzaniu sprawozdań z ćwiczeń w pracowniach fizycznych PŁ. Zainteresowani znajdą dalsze informacje w systemach pomocy do programów OpenOffice Calc i MS Excel.

5. Błąd należy zawsze zaokrąglać do góry. Może się zdarzyć, że arkusz kalkulacyjny podając kilka pierwszych cyfr znaczących błędu, dokonał zaokrąglenia w dół. Aby się o tym przekonać trzeba zwiększyć liczbę cyfr znaczących przyciskiem
   (
   dla MS Excel). Jeśli dodanie cyfry znaczącej wykaże nieprawidłowe zaokrąglenie (w dół), wówczas przepisując wynik z arkusza należy dokonać stosownej poprawki.

6. Jeśli otrzymane wyniki mają charakter ostatecznego rezultatu, to należy koniecznie pamiętać o ich prawidłowym zaokrągleniu

---