Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: funkcje logiczne

środa, 19 listopada 2014

Tegoroczne wybory samorządowe z pewnością na długo zostaną w pamięci wszystkim, którzy uczestniczą w liczeniu głosów i przeliczaniu ich na mandaty. System informatyczny nie działa, czas najwyższy wrócić do bardziej tradycyjnych metod. I jak w wielu innych miejscach - tak i tu przydatny jest Excel.

Taki zwykły kalkulator wyborczy, oparty o metodę H'ondta bez problemu można zbudować w zwykłym skoroszycie Excela i to bez makr.

Zzipowany plik Excela do pobrania jest tu:

Kalkulator wyniku wyborów

A jak został stworzony?

Kalkulator wyników wyborów

kliknij, aby zobaczyć obrazek w większej rozdzielczości

Do wypełnienia są żółte pola. W komórce D6 wpisujemy liczbę mandatów do podziału, w komórkach C4:M4 - liczbę głosów uzyskanych przez poszczególne partie. I już całą reszta jest obliczana automatycznie.

Metoda H'ondta polega na tym, że w pierwszym kroku wstawiamy do tabeli wszystkie wyniki, w kolejnym wierszu - dzielimy te wartości przez 2, w następnym - przez 3 itd.
Wyniki tych obliczeń są umieszczone od wiersza 8 w dół.

Formuły zawarte w wierszu 8 - to:
np. komórka C8: =JEŻELI(C4<>"";C4;"") - czyli jeżeli w tabeli z danymi w kolumnie C jest wartość - jest ona pobierana do wiersza 8, jeżeli nie - pozostaje puste miejsce.

Od wiersza 9 - formuła wygląda tak:
np. w komórce C9:   =JEŻELI(C$8<>"";C$8/$B9;"") - jeżeli w komórce C8 jest wartość - jest ona dzielona przez wartość w komórce B9 czyli 2. Analogicznie - w kolejnych wierszach.

Formuła B7 jest natomiast formuła:

=MAX.K(C8:M19;D1)

Wyznacza ona k-tą wartość maksymalną z zakresu obliczeń. Wartość k jest wyznaczona przez liczbę mandatów do podziału - zapisana jest w komórce D1. Wartości większe i równe tej wartości minimalnej są oznaczone w obliczeniach na jasnoniebiesko. Tu wykorzystałam formatowanie warunkowe:

formatowanie warunkowe 

W wierszu 5 jest wyznaczona liczba mandatów dla danej partii. Zastosowałam tu formułę:
=LICZ.JEŻELI(C8:C19;">=" & $B7)

Następuje tu sprawdzenie - ile w zakresie obliczeń dla danej kolumny występuje liczb niemniejszych od B7.

I już, wszystko jasne. - można wspomóc PKW kalkulatorem wyborczym.

Dla przypomnienia - podstawowe funkcje użyte w skoroszycie to:

Jeżeli, Licz.Jeżeli, Max(k).

 


 

Kurs Excel w praktyce

środa, 19 maja 2010

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

poniedziałek, 26 kwietnia 2010

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:\serwer\Katalog.xls

katalog

W moim pliku statystyk
(C:\Documents and Settings\Desktop\Statystyki_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.

wtorek, 23 marca 2010

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ść.

 

sobota, 20 marca 2010

Funkcja OR  w VBA jest odpowiednikiem Excelowej funkcji LUB.

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

Public Function MojaFunkcjaLUB (Warunek_1 As Boolean, Warunek_2 As Boolean) As Boolean
MojaFunkcjaLUB = Warunek_1 OR Warunek_2
End Function

Argumenty oraz wartość funkcji są typu Boolean.

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.


 

piątek, 19 marca 2010

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.

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ż.

poniedziałek, 15 marca 2010

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.

czwartek, 18 lutego 2010

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.

| < Wrzesień 2017 > |
Pn Wt Śr Cz Pt So N
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!