Ćwiczenie nr 4. Diagram J. Czekanowskiego

Algorytm diagramu J. Czekanowskiego został opracowany w 1926 roku. Punktem wyjścia jest zdefiniowanie macierzy współczynników oraz przeprowadzenie ich standaryzacji lub unitaryzacji (MUZ). Zostało to zrobione w pliku „Czekanowski i Wrocławska.xls” plik ten będzie służył jako przykład (c:\Cwiczenia\AEP 504d\Zajęcia nr 4).

Kolejnym krokiem jest wybór miary podobieństwa. Istnieje wiele takich miar. Najczęściej wybiera się tzw. odległość Euklidesową lub odległość metropolitalną /w literaturze przedmiotu sprawdź jak wyglądają wzory na te odległości/. My wykorzystamy ze wzoru na odległość metropolitalną - będąca sumą modułów różnic między wartościami wskaźników.

1. Obliczanie elementów macierzy odległości taksonomicznych między jednostkami przestrzennymi.

1. Przejdź do arkusza2 pliku „Czekanowski i Wrocławska.xls” skopiuj tam nagłowki tabeli MUZ z arkusza1 zaczynając od komórki A1. W miejsce MUZ wpisz „Macierz odległości”. Przedłuż tabelę do liczby 16 kolumn zawierających dane o odległości.

2. W komórkę B2 wstaw funkcję MODUŁ.LICZBY(Arkusz1!B20- Arkusz1!B20) dodaj wartość kolejnego modułu tak by końcowy wzór obejmował wszystkie wskaźniki dla województwa Dolnośląskiego. Suma powinna wynosić 0 ponieważ odległość jednostki przestrzennej od niej samej wynosi 0.

3. Edytując wzór wpisz znak $ przed każdą literą adresu komórki i przed cyfrą pierwszej komórki w każdym z modułów. Przeciągnij wzór w całej kolumnie. Skopiuj wzór do drugiego wiersza i drugiej kolumny macierzy odległości.

4. Poruszając się po przekątnej poprawiaj formułę tak by wartość cyfry zablokowanej była równa tej niezablokowanej. Jeżeli wartość komórki po poprawkach wyniesie 0 skopiuj formułę w dół i w górę kolumny. Uzupełnij całą macierz (zwróć uwagę czy jest ona kwadratowa).

2. Znalezienie wartości maksymalnej i minimalnej w macierzy odległości taksonomicznych, oraz konstrukcja przedziałów klasowych dla wartości odległości

1. Wyznacz wartość maksymalną w macierzy. W kolumnie S, w nagłówku wpisz max (2). Poniżej w komórce S2 wprowadź formułę max.k gdzie zakresem zmiennych są wartości odległości taksonomicznych dla woj. Dolnośląskiego zaś współczynnik k wynosi 1

2. Wyznacz pozycję w której jest maksymalna. Wpisz polecenie podaj.pozycję w kolumnie T, gdzie szukaną wartością będzie wartość naszego max, przeszukiwaną tablicą wiersz Dolnośląskiego, zaś typ porównania wynosi 0 (czyli dokładnie tyle samo)

3. Wyznaczenie wartość minimalnej dla każdego wiersza. W kolumnie U, w nagłówku wpisz min (2). Poniżej w komórce U2 wprowadź formułę min.k gdzie zakresem zmiennych są wartości odległości taksonomicznych dla woj. Dolnośląskiego zaś współczynnik k wynosi 2 (czyli następny po wartości 0) (analogicznie jak to miało miejsce dla formuły max.k)

4. Wyznacz pozycję w której jest minimalna, analogicznie dla punktu d.

5. Przeciągnij formuły w dół kolumn.

6. W komórce R18 wpisz max, w komórce S18 wpisz polecenie wyszukania wartości max z wcześniej wyszukanych maksimum

7. W komórce R19 wpisz min, w komórce S19 wpisz polecenie wyszukania wartości min z wcześniej wyszukanych minimum,

8. W komórce R20 wpisz średnia zaś w komórce S20 wyznacz średnią z całej macierzy odległości

9. W komórce R21 wpisz odchylenie zaś w komórce S21 wyznacz wartość odchylenia standardowego dla całej macierzy odległości

3. Sporządzenie diagramu nieuporządkowanego.

1. Zaznacz macierz odległości. Skopiuj komórki i wklej je specjalnie jako wartości rozpoczynając od komórki A24. Zmień formatowanie tak by były widoczne 4 miejsca po przecinku.

2. Konstrukcja przedziałów klasowych. W komórkę R22 wpisz: średnia-odchylenie, w komórkę S22 wprowadź odpowiednią formułę. W komórkę R23 wpisz: średnia+odchylenie, w komórkę S23 wprowadź odpowiednią formułę.

3. Zaznacz wartości odległości nowo utworzonej macierzy. W menu znajdź polecenie formatowania warunkowego. Formatuj komórki na podstawie ich wartości: pierwsza grupa przestrzenna będzie miała wartości między wartością minimalną dla macierzy (ale większą niż 0) i wartością która jest różnicą między sumą a odchyleniem standardowym (przypisz tym komórkom kolor ciemnozielony, albo inny zgodnie z wybraną przez siebie paletą barw), kolejny przedział jest między wartością różnicy między sumą a odchyleniem standardowym oraz wartości średniej (kolor zielony), trzecia grupa ma wartości mieszczące się w przedziale od wartości średniej do wartości sumy średniej oraz odchylenia standardowego (kolor jasnozielony), w najnowszym excelu można wybrać także kolor grupy kolejnej, w starszych wersjach nie można wybrać więcej niż trzech poziomów autoformatowania, grupa ta pozostanie biała. /Tym samym uzyskano nieuporządkowany diagram Czekanowskiego. Zmień nazwę macierzy tak by odpowiadała temu stanowi/

4. Przestawianie kolumn i wierszy tak, by odległości najmniejsze znalazły się możliwie najbliżej przekątnej diagramu (tworzenie diagramu uporządkowanego metodą mechaniczną) - metoda niezalecana ale praktykowan

1. Skopiuj diagram nieuporządkowany do komórek arkusza rozpoczynając od komórki A44. Wpisz w nazwę macierzy: diagram uporządkowany mechanicznie

2. Oszacuj w którym wierszu jest najwięcej ciemnych pól. Wytnij wiersz (razem z nazwą) i wstaw wycięte komórki na pierwszym miejscu w diagramie, Wytnij kolumnę przyporządkowaną do tego województwa w którym jest najwięcej ciemnych miejsc i wstaw wycięte komórki zaraz za nazwami wierszy (nie zapomnij także o numerze kolumny).

3. Podobnie postąp z resztą wierszy i kolumn tak by u góry i z przodu diagramu występowała maksymalna liczba ciemnych pól zaś u dołu i z tyłu diagramu było maksymalnie dużo pól jasnych.

5. Przestawianie kolumn i wierszy tak, by odległości najmniejsze znalazły się możliwie najbliżej przekątnej diagramu (tworzenie diagramu uporządkowanego metodą matematyczną)

1. Sprawdź pomiędzy jakimi województwami występuje wartość minimalna w macierzy odległości. Rozpoczynając od komórki A64 wklej nagłowki macierzy odległości zaś zaraz pod spodem wartości z diagramu nieuporządkowanego (łącznie z nazwami) dla województw gdzie odległość była minimalna (powinno to być województwo nr 2 i 16). Nazwy tych kolumn zaznacz na czerwno.

2. W komórce A66 wpisz suma i poczynając od komórki B66 wylicz sumy odległości między tymi wierszami. Wylicz trzecią wartość minimalną dla sumy, oraz wyznacz kolumnę w której jest ta wartość (pierwsze dwie są między wierszami sumowanymi).

3. Wartość formuły podaj.pozycję wskazuje który wiersz powinien zostać skopiowany poniżej wartości sumy /w naszym przypadku powinien być to wiersz nr 1/. Skopiuj wiersz. Zaznacz nazwę kolumny kolorem czerwonym. Następnie do wiersza dodaj wartość sumy (jak to miało miejsce poprzednio) oraz wylicz 3 wartość minimalną, sprawdź pozycję - jeżeli jest ona zaznaczona na czerwono, wyznacz kolejne minimum poprzez zmianę formuły (w naszym przypadku 4)

4. Ponawiaj punkt c aż zaznaczysz na czerwono wszystkie wiersze /w obliczeniach nie może wystąpić dwa razy ten sam wiersz, jeżeli tak się zdarzy należy sprawdzić poprawność obliczeń/

5. Skopiuj wszystkie obliczenia z punktu 5 poniżej tych obliczeń. Usuń wszystkie  wiersze sum, przekształć macierz w macierz kwadratową zachowując kolejność wierszy. /Powstała macierz uporządkowana/

6. Zaznacz grupy przestrzenne na podstawie kształtowania się kolorów wokół przekątnej diagramu, przy założeniu że jednostki przestrzenne mogą przynależeć tylko do jednej grupy.

6. Interpretacja wyników metody Czekanowskiego.

1. Odczytaj z macierzy odległości, które województwo jest najbliżej województwa Dolnośląskiego ze względu na analizowane wskaźniki (czyli między jakimi województwami jest min 2) a jakie jest najdalej (czyli max). Podobnie postąp dla wszystkich województw.

2. Odczytaj jaka jest średnia wartość odległości w macierzy oraz jaka jest wartość odchylenia standardowego.

3. Odczytaj z diagramu uporządkowanego metodą matematyczną skład poszczególnych grup przestrzennych oraz przenieś wyniki na mapę (zachowując kolorystykę grup). Czy tworzą one jakieś zgrupowania przestrzenne?

4. Odczytaj jakie są wartości minimalne w grupach oraz wyznacz wartości średnie dla tych grup wraz z odchyleniem standardowym. Czego to dowodzi?

---