Kalkulator wyborczy

Kilka lat temu opublikowałam na tym blogu notkę poświęconą tworzenia kalkulatora wyborczego, wyznaczającego liczbę mandatów dla poszczególnych ugrupowań (metoda d’Hondta).

Kalkulator wyborczy

Oprócz opisu jak zrobić taki kalkulator w Excelu był tam też link do samego pliku.

Ostatnio okazało się jednak, że link nie działa – faktycznie, moja strona od tamtego czasu trochę się zmieniła. Niestety, sam plik też mi gdzieś się zapodział. Może nawet nie zwróciłabym na to uwagi, ale dostałam kilka maili z prośbą o aktualizację. Wybory za kilka dni, więc odtworzyłam plik i wrzuciłam na serwer.

Kalkulator wyborczy jest do pobrania tu:

Kalkulator wyborczy – plik Excela


Szybkie porównanie plików

Kilka dni temu zostałam poproszona o szybkie porównanie zawartości dwóch prostych plików. Zrobiłam to, ale żeby tym, których to dotyczyło nie umknęło, a pozostałych być może zainspirowało – pokażę rozwiązanie, które przyjęłam.

Dla uproszczenia przykładu – załóżmy, że mamy jeden plik Excela, w którym jest arkusz Spis1 zawierający spis telefonów i ich właścicieli (spis zupełnie wymyślony, wszystkie dane wymyślone i przypadkowe). Oprócz tego – jest też arkusz Spis2 – prawie identyczny.

 porównanie plików

Jak wszędzie jednak to „prawie” może stanowić wielką różnicę. Jak odnaleźć wiersze, w których występują różne dane? Jednym z prostszych sposobów może być wstawienie w jedną z kolumn w obydwu arkusza funkcji Złącz.Teksty.
Dla komórki C2 formuła będzie wyglądać tak:
=ZŁĄCZ.TEKSTY(A2;B2)
W kolejnych wierszach – analogicznie. Następnie w kolejnej w kolumnie wstawiamy formułę porównujące odpowiednie ciągi.
Komórka D2 w arkuszu SPIS2:
=JEŻELI(C2=spis2!C2;””;”różnica”)
Formuła ta oznacza, że jeżeli komórka C2 jest równa komórce C2 w arkuszu SPIS2 – to zostaw puste miejsce (wyznaczane przez podwójny cudzysłów), jeżeli nie są równe – to wpisz „róznica”.
Dodatkowo zastosowałam tu także formatowanie warunkowe, uzależnione od wartości wiersza w kolumnie D.
Efekt końcowy wygląda tak:

porównanie plików

W tym przypadku – ponieważ porównujemy tylko dwie kolumny (A i B) – można zastosować także prostszą funkcję, porównującą poszczególne komórki:
=JEŻELI(ORAZ(A2=spis2!A2;B2=spis2!B2);””;”różnica”)
Jeżeli jednak w każdym z wierszy jest więcej kolumn – formuła może okazać się zbyt długa. Wtedy zdecydowanie lepiej wykorzystąc pierwszy sposób. Warto też pamiętać o tym, że jeżeli takie porównanie ma zostać na dłużej – to może lepiej ukryć te kolumny? Taki złączony tekst wygląda mało estetycznie.

A dla przypomnienia – linki do notek omawiających funkcje wykorzystane w przykładzie:
Jeżeli
Oraz
Złącz.Teksty

Formatowanie warunkowe

Statystyki pliku udostępnionego

W swojej codziennej pracy często muszę szybko zrobić statystyki z pracujących w trybie wielodostępu plików na serwerze. Kilku różnych użytkowników edytujących poszczególne komórki, dodających kolejne wiersze do tabeli.
Najprostszym rozwiązaniem byłaby tabela przestawna, ale w plikach udotępnionych – nie działa, więc nie można jej tu wykorzystać. Jak sobie poradzić?
Ja na swoim komputerze zakładam nowy plik, kopiuję nagłówek, a w kolejnych wierszach – wstawiam łącza do pliku na serwerze.
Posłużę się tu już wcześniejszym przykładem katalogu książek. Jego pełna nazwa (wraz z ścieżką dostępu na moim komputerze) to:
E:serwerKatalog.xls

katalog

W moim pliku statystyk
(C:Documents and SettingsDesktopStatystyki_katalogu.xls)
pierwszy wiersz to również nagłówek, a kolejne komórki tworzę wpisując łącza do pliku Katalog.xls.
Na wszelki wypadek wyjaśniam, że najprościej to zrobić w ten sposób, że mając jednocześnie otwarte obydwa te pliki, w komórce A2 w pliku Statystyki_katalogu wpisuję znak równości, przechodzę do pliku Katalog, również do komórki A2 i wciskam Enter.
W komórce pojawi sie wartość 1, a w polu formuły pojawia się:
=[Katalog.xls]spis!$A$2
Wpisany adres jest adresem bezwzględnym. Skopiowanie formuły do kolejnej komórki – nie zmieni jej. Konieczne jest więc wycięcie znaków $
Po korekcie:
=[Katalog.xls]spis!A2
Skopiowana w ten sposób komórka A2 na całą tabelę – przeniesie odpowiednie wartości odowiednich komórek w odpowiednie miejsca.
Jest jeszcze jeden problem. Jeżeli jakaś komórka w pliku źródłowym będzie pusta – to w pliku statystyk pojawi się zero. Spowoduje to zaciemnienie obrazu tabeli, a w wielu przypadkach – zafałszowanie statystyk. Zero jest też wartością, niekiedy bardzo istotną.
Ja radzę sobie za pomocą funkcji Jeżeli…to  
=JEŻELI([Katalog.xls]spis!A2=””;””;[Katalog.xls]spis!A2)
Jeżeli wartość komórki A2 (w pliku Katalog) jest pusta (oznaczamy to podwójnym cudzysłowem) – to wstaw „nic” (podwójny cudzysłów), w przeciwnym przypadku – wstaw wartość komórki A2.
I już.
Przy otwarciu pliku Statystyk będzie pojawiał się komunikat informujący o łączach i pytanie, czy zaktualizować ich wartość.

aktualizacja łączy

Plik statystyk jest gotowy do analizy.

Funkcje Excela: Jeżeli.Błąd

Funkcja logiczna Jeżeli.Błąd występuje dopiero od wersji Excela 2007 – wcześniej jej nie było. Niby nie jest żadną wielką rewelacją, ale znacznie ułatwia życie. Składnia jej jest bardzo prosta. Jeżeli wynikiem działania jakiegoś obliczenia jest błąd – to automatycznie przypisujemy jakąś konkretną wartość, jeżeli obliczenie jest prawidłowe – wstawiamy wynik działania funkcji

Dobrym przykładem może tu być zwykłe dzielenie przez zero:

Funkcja Jeżeli.Błąd 

Dzieląc komórkę A1=4 przez B1=0 otrzymujemy błąd #DZIEL/0! . Wykorzystując funkcję Jeżeli.Błąd – możemy wstawić albo jakiś komunikat, albo, szczególnie w przypadkach gdy wartość ta wchodzi w ciąg dalszych obliczeń – konkretną inną wartość.

 

Funkcje Excela: LUB

Funkcja LUB to kolejna funkcja logiczna Excela. Przyjmuje warości Prawda lub Fałsz. Podobnie jak funkcja ORAZ – równiez rzadko stosowana samodzielnie, za to w połaczeniu z funkcją „Jezeli… to…” – bardzo często.

Zapis  formuły wygląda tak:
=LUB(Warunek1;Warunek_2;Warunek_3;…)

Wartość PRAWDA – wtedy, gdy spełniony chociaż jeden Warunek występujący jako argument. Jeżeli wszystkie są niespełnione – FAŁSZ.

 

Funkcje VBA: AND

Funkcja AND  w VBA jest odpowiednikiem Excelowej funkcji ORAZ.  Zasada jej działania – dokładnie taka sama.

Przykładowy zapis procedury z użyciem funkcji AND:

Public Function MojaFunkcjaORAZ(Warunek_1 As Boolean, Warunek_2 As Boolean) As Boolean
MojaFunkcjaORAZ = Warunek_1 And Warunek_2
End Function

Argumenty oraz wartość funkcji są typu Boolean.

Funkcje Excela: ORAZ

Funkcja ORAZ należy do funkcji logicznych Excela. W wyniku jej działania mozemy otrzymac wartość Prawda lub Fałsz. Prosta, choć samodzielnie rzadko stosowana. Za to w połaczeniu z funkcją „Jezeli… to…” – bardzo często.

Zapis  formuły wygląda tak:
=ORAZ(Warunek1;Warunek_2;Warunek_3;…)

Wartość PRAWDA – wtedy, gdy spełnione są wszystkie Warunki wystĄpujące jako argumenty. Jeżeli którys jest niespełniony – FAŁSZ.
I już.

Funkcja VBA: If… then…

Funkcja If… then… w kodzie VBA jest odpowiednikiem funkcji Jeżeli.. to… w Excelu. Pisałam oniej wcześniej w notce:
Funkcje logiczne Excela: Jeżeli… to…
Jak zapisać tamten przykład z użyciem funkcji VBA?

Public Function JakiZnakLiczby(PodanaLiczba As Single) As String
If PodanaLiczba < 0 Then JakiZnakLiczby = „-” Else JakiZnakLiczby = „+”
End Function

Po słowie kluczowym If występuje warunek, na który odpowiedź jest typu Prawda – Fałsz. Instrukcja do wykonania w przypadku, gdy zdanie to jest logicznie prawdziwe – następuje po słowie Then. Po słowie kluczowym Else – wpisujemy kod do wykonania dla fałszu.

Przedstawiony przykład możemy nieco rozbudować:

 Public Function JakiZnakLiczby(PodanaLiczba As Single) As String
 If PodanaLiczba < 0 Then            ‚Warunek 1: Czy jest mniejsza od zera?
    JakiZnakLiczby = „ujemna”        ‚Wynik= ujemna
 Else
     If PodanaLiczba = 0 Then        ‚Warunek 2: Czy równa zero?
        JakiZnakLiczby = „zero”        ‚Wynik= zero
       Else
        JakiZnakLiczby = „dodatnia”          ‚Wynik= dodatnia
     End If                                            ‚Koniec bloku warunku 2
 End If                                                ‚Koniec bloku warunku 1
End Function

Koniec bloku każdego z warunków jest wyznaczany przez słowa kluczowe
End If.

Jeśli są pytania – to proszę o komentarz.

Funkcje logiczne Excela: Jeżeli…to…

Funkcja logiczna „Jeżeli…to…” jest jedną z moich ulubionych i czesto wykorzystywanych. Zsada jej działania jest prosta. Jeżeli jakiś warunek jest spełniony – to komórką z wstawiona formułą ma jedną wartość , jeśli nie jest – inną.

Jeżeli..to...

Wyobraźmy sobie prosty przykład, że komórka A1 ma sprawdzać znak liczby w komórce B1. W  Test_logiczny wpisujemy (można też zaznaczyć komórkę) B1<0. W polu Wartość_jeżeli_prawda „-„ (znak minusa w cudzysłowie, gdyż ten minus stanowi w tym przypadku tekstowy znak graficzny. Analogicznie w polu Wartość_jeżeli_fałsz „+”.  Potem wystarczy tylko kliknąć przycisk OK.

Zapis  formuły wyglądać będzie tak:
=JEŻELI(B1<0;„-„;„+”)
Na czerwono zaznaczyłam średniki – oznaczają one koniec poszczególnych pól formuły. Widać ten zapis także w arkuszu, w polu formuły.

Funkcja Jeżeli to

A od strony praktycznej – lubię tę funkcję i często z niej korzystam do tworzenia raportów. W jaki sposób? W pracy sporo pracujemy na udostępnionych plikach Excela na serwerze. Są to arkusze z dużą ilością modyfikowanych i dodawanych danych, nawet ilośc wierszy ulega zmianie. Dołożenie jeszcze dodatkowych raportów – stanowiłoby niepotrzebne obciążenie. Tabeli przestawnej w ogóle nie da się stworzyć i uaktualnić w arkuszu udostępnionym. W takich przypadkach na swoim komputerze tworzę osobny skoroszyt raportów i wstawiam łącza do poszczególnych komórek pliku źródłowego. Problem jednak w tym, że proste łącze:
=[Plik_zrodlowy.xls]Arkusz!$A$1
i analogicznie – w pozostałych komórkach – wszędzie tam, gdzie w pliku źródłowym jest pusta komórka – łącze wskaże „0”. Zero jednak też jest wartością i może to całkowicie zafałszować raport. I w tym momencie – korzystam właśnie z funkcji Jeżeli… to…
W pliku raportu, w komórce A1 wstawiam funkcję:
=Jeżeli(Plik_zrodlowy.xls]Arkusz!A1=””;””,[Plik_zrodlowy.xls]Arkusz!A1)
Jak to czytać? Jeżeli komórka A1 w pliku żródłowym jest pusta (dwa znaki cudzysłowu obok siebie – „” – to oznaczenie pustego łańcucha tekstowego) – to wynik formuły jest też pusty. W przeciwnym przypadku (wartość jeśli fałsz) – wynkiem komóki jest wartość komórki pliku źródłowego.
Metoda wielokrotnie sprawdzona i wykorzystywana. Nawet nie muszę pilnować ilości wierszy – i tak pokażą się tylko te potrzebne.