Tworzenie rankingów i zestawień za pomocą funkcji Pozycja

Średniozaawansowany
Automatyzacja pracy
Analiza danych
0:00czas trwania

Jedną z podstawowych miar statystycznych jest korelacja – oznaczająca powiązania oraz wzajemne wpływanie na siebie dwóch lub większej ilości zmiennych. W jaki sposób w badaniu korelacji pomóc może arkusz kalkulacyjny? Otóż, pokażemy to na przykładzie wyników w konkursie sportowym, gdzie uwzględnimy takie parametry jak wzrost i waga zawodników. Przy pomocy Excela utworzymy rankingi, które uszeregują zawodników w zależności od wartości tych zmiennych. Dodatkowo na koniec sprawdzimy czy wzrost i waga wpływa na ostateczny rezultat w zawodach. Zapraszamy do nauki tworzenia rankingów i zestawień!

Opublikowane: 9 stycznia 2020
Lekcję prowadzi: Kamil Skonecki
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu

Zobacz krok po kroku jak wykonać lekcję

Z lekcji dowiesz się:

  • Jak w sprytny sposób tworzyć rankingi i zestawienia?

  • Co to jest „korelacja”? Jak działa?

  • Przy jakich zadaniach używać funkcji Pozycja?

Pobierz szablon do dalszej pracy
Dostępne po opłaceniu
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu

Z lekcji dowiesz się:

  • Jak w sprytny sposób tworzyć rankingi i zestawienia?

  • Co to jest „korelacja”? Jak działa?

  • Przy jakich zadaniach używać funkcji Pozycja?

W bazie danych znajdują się wyniki zawodników w konkursie sportowym, a także ich parametry dotyczące wzrostu i wagi. Pierwszy problem, który napotykamy to zupełnie inne miary porównywanych wskaźników. Wzrost wyrażony w centymetrach, a waga w kilogramach. Błędem byłoby dodanie do siebie tych dwóch parametrów, dlatego zbudujemy za pomocą funkcji Pozycja ranking, który określi nam, którego z zawodników możemy uznać za najwyższego i najsilniejszego.

Omówmy w pierwszej kolejności funkcję Pozycja.

POZYCJA(liczba;lista;[lp])

Liczba - liczba, której pozycja ma zostać określona, wartości tej nie blokujemy,

Lista - tablica danych, z których ma zostać utworzony ranking. Zakres powinien zostać zablokowany, w ten sposób przeciągając funkcje do dołu, rozmiar listy pozostanie niezmieniony.

Lp - argument określający w jakiś sposób zamierzamy uszeregować wartości:

 

 

Zaczynamy od ustalenia rankingu zawodników pod kątem ich wzrostu. Wybieramy funkcję Pozycja i jako liczbę wskazujemy pierwszą wartość komórki z listy (wzrost pierwszego zawodnika) pamiętając, że nie blokujemy wartości tej komórki. Zakres tablicy, to wzrost wszystkich zawodników i tutaj te wartości blokujemy, ponieważ przesuwając formułę do dołu, chcemy żeby zmieniała się jedynie porównywana liczba, a nie zakres tablicy. Chcemy szeregować wartości rosnąco, dlatego wybieramy 1 i przeciągamy tak napisaną formule w obrębie tablicy (rys. 1).

(Rys. 1)

Dokładnie tak samo postępujemy budując ranking w przypadku wagi zawodników (rys. 2).

(Rys. 2)

Nieprzypadkowo, tworzyliśmy ranking szeregując wartości w sposób rosnący. Dzięki temu udało się stworzyć swoisty ranking punktowy, który dla osoby najwyższej lub najsilniejszej przypisał największą liczbę punktów (w tym przypadku pozycję). Pozwala to na zsumowanie dwóch rankingów, pomimo różnej skali porównywanych czynników (rys. 3).

(Rys. 3)

Największa ilość punktów świadczy o największej sile (umownie uznajemy wzrost i wagę za definicję siły) danego zawodnika (rys. 4).

(Rys. 4)

Teraz ponownie stworzymy ranking zgodnie z metodyką jak przyjęliśmy we wcześniejszych punktach.

Zawodnik o największej liczbie punktów, zajmie „ostatnie” czyli 30 miejsce, które w tym przypadku jest najlepsze, ponieważ zajmowaną pozycję zamieniamy na punkty.

Kiedy mam już pełne zestawienie pozycji każdego zawodnika, policzymy współczynnik korelacji, między liczbą punktów, które uzyskał w zawodach, a liczbą punktów jakie uzyskał w naszym rankingu predyspozycji fizycznej. Sprawdźmy - czy siła wpływa na lepszy wynik zawodnika?

Korelacja – jest badaniem polegającym na sprawdzeniu czy dwie zmienne są ze sobą powiązane i wpływają na siebie wzajemnie. Inaczej mówiąc jak wzrost lub spadek jednej zmiennej wpływa na wartość drugiej. Prostym przykładem może być badanie korelacji między wzrostem metrażu mieszkania, a jego ceną. Łatwo się domyślić, że czym większy metraż, tym droższa nieruchomość. Możemy wtedy powiedzieć o korelacji dodatniej, czyli sytuacji, w której wzrost lub spadek jednej zmiennej powoduje analogiczną reakcję drugiej. Korelacja ujemna to oczywiście sytuacja odwrotna od opisywanej, np. im niższa cena produktu tym większa jego sprzedaż. W tym przypadku wzrost lub spadek zmiennej powoduje spadek lub wzrost drugiej zmiennej.

Ważne, aby pamiętać że wartość współczynnika korelacji jest zawarta w przedziale <-1,1> Jeżeli współczynnik, wyjdzie poza ten przedział należy sprawdzić, czy nie popełniliśmy błędu.

W zależności od wartości współczynnika wyróżnia się korelacje:

 

  • <-1, -0,5> silna korelacja ujemna

  • <-0,5, -0,3> umiarkowana korelacja ujemna

  • <-0,3, 0> słaba korelacja ujemna

  • <0, 0,3> słaba korelacja dodatnia

  • <0,3, 0,5> umiarkowana korelacja dodatnia

  • <0,5, 1> silna korelacja dodatnia

 

Tyle teorii, przejdźmy zatem do praktyki. Aby policzyć współczynnik korelacji skorzystamy z tożsamej nazwy funkcji dostępnej w arkuszu kalkulacyjnym. Jej budowa jest bardzo prosta. Wybieramy dwa zakresy komórek (tablic), które chcemy ze sobą porównać obliczając współczynnik korelacji. Nie musimy nic blokowa

, ani przeciągać, wynik otrzymujemy w jednej komórce.

Obliczymy współczynnik korelacji między rankingiem zawodników, a ich sumą punktów w zawodach sportowych. Sprawdzimy czy siła mierzona wzrostem i wagą przekłada się na końcowy wynik.

Wartość współczynnika korelacji wynosi -0,27 czyli istnieje słaba korelacja ujemna. Można uznać, że siła nie wpływa w znaczny sposób na ilość punktów, a jeśli już taki związek istnieje, to działa w sposób odwrotny. Widzimy zatem, że to ile mamy wzrostu i ile ważymy, w tym przypadku nie wpływa na naszą sprawność sportową (rys. 5).

(Rys. 5)

Dla przetestowania współczynnika korelacji zbudujemy dwie tablice (rys. 6):

(Rys. 6)

Zastosujemy w nich współczynnik korelacji analogicznie jak wcześniej (rys. 7):

(Rys. 7)

Jak łatwo się domyślić, wynik to -1. Mamy idealną korelację ujemną czyli wzrost jednej zmiennej wpływa na spadek zmiennej drugiej, co łatwo zauważyć na przygotowanym zestawie danych.

Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz gotowy arkusz z lekcji
Dostępne po opłaceniu
Kolejne kroki dostępne dla użytkowników którzy wykupili pełny dostęp

Jeszcze się wahasz?

Zobacz, co zyskasz, mając pełny dostęp:

  • Dostęp do wszystkich lekcji video
  • Materiały do ćwiczeń
  • Dodatkowe artykuły z trikami
  • Formularze gotowe od razu do użycia
  • E-booki pogłębiające Twoją wiedzę
  • Certyfikat potwierdzający Twoje umiejętności
Sprawdź co oferuje serwis Sprytny Excel »

Masz już konto w serwisie?

Zaloguj się

Adres e-mail:

Hasło

Nie pamiętam hasła
- Twój pierwszy krok do lepszych wyników
Oglądaj pełne lekcje i wiedz więcej!
Masz już konto w serwisie?
Zaloguj się »

Certyfikat rzetelności Laur zaufania SMB logo Top firma