Kategoria: Excel

Bezpieczeństwo makr

Pliki Excela zawierające kod VBA (czyli makra i/lub procedury) mogą niestety zawierać również szkodliwe oprogramowanie i mogą być niebezpieczne dla naszego komputera. Ma to znaczenie szczególnie przy otwieraniu plików nieznanego pochodzenia, pochodzących gdzieś z internetu i warto się zabezpieczyć. 
Ustawienia bezpieczeństwa makr dostępne są na karcie Developer w grupie opcji Kod:

Można je też ustawić na karcie Plik –> Opcje –> Centrum Zaufania –> Ustawienia Centrum Zaufania.

Dostępne są tu następujące opcje:

Ja preferuję tu drugą opcję czyli wyłączenie makr i włączenie powiadomień. Dotyczy to ustawień ogólnych Excela i dotyczy wszystkich plików. Po jej wyborze, przy otwarciu pliku z makrami zobaczymy taki komunikat:

Wystarczy, jeżeli naciśniemy przycisk Włącz zawartość i wszystkie makra będą dostępne.

Jest to z pewnością bardzo pożyteczne działanie, ale w sytuacji aplikacji znanych i sprawdzonych, z pewnego źródła – często uciążliwe. Jak się pozbyć tych komunikatów? Przynajmniej w stosunku do tych treści, których jesteśmy pewni?
Służy do tego inna z opcji Centrum Zaufania – Zaufane lokalizacje

Po naciśnięciu przycisku Dodaj nową lokalizację… , otwiera się  nowy formularz, w którym definiujemy wybrany folder:

Z reguły warto od razu zaznaczyć także checkbox Podfoldery tej lokalizacji są także zaufane.

W ten sposób może wybrać zaufane lokalizacje na naszym komputerze. Jeżeli chcemy zdefiniować jakiś dysk sieciowy, konieczne jest zaznaczenie wcześniejsze checkboxa Zezwalaj na zaufane lokalizacje w mojej sieci.

Funkcja ORAZ

Funkcja ORAZ to jedna z funkcji logicznych Excela. Zwraca wartość typu logicznego (Prawda lub Fałsz).
Jest odpowiednikiem iloczynu logicznego czyli koniunkcji.

Argumentami funkcji są warunki, które albo są spełnione albo nie – czyli każdy z nich zwraca wartość Prawda lub Fałsz.

Jeżeli każdy z pojedynczych warunków jest spełniony – funkcja ORAZ zwraca wartość Prawda. Jeżeli choć jeden z warunków nie jest spełniony – w wyniku działania funkcji otrzymujemy Fałsz.

Odpowiednikiem funkcji w VBA jest funkcja And().

Kurs Excel 2019 od podstaw

 

Formatowanie warunkowe z użyciem pasków danych

Formatowanie warunkowe w Excelu to bardzo obszerne narzędzie z wieloma możliwościami wizualizacji danych. Jednym z najprostszych jest sposobów jest formatowanie wstawiające do komórki paski danych.

Najprościej można pokazać to na przykładzie.
Załóżmy, że mamy arkusz z średnimi temperaturami powietrza. Zaznaczamy zakres temperatur i na karcie Narzędzia główne wybieramy Formatowanie warunkowe:

Efekt jest widoczny od razu.

Oczywiście takie formatowanie warunkowe można zmienić, dostosowując je do własnych potrzeb. Z menu formatowania warunkowego wybieramy Zarządzaj regułami, odszukujemy wprowadzoną regułą i przechodzimy do jego edycji.

 W okienku edycji widać poszczególne opcje do wyboru. Możemy je zmieniać tak, jak nam się podoba.

Po zmianie kolorów, nie zapominajmy o wartościach ujemnych.

U mnie efekt końcowy wyszedł tak:

 

A tu trochę wsparcia z Excela:

Kurs Efektywna praca w Excelu - porady i triki

Kompresja obrazów w Excelu

W pliku Excela czasami pojawia się konieczność wstawienia zdjęć. Ubocznym skutkiem tego jest jednak znaczne powiększenie rozmiaru pliku. Co można zrobić w takim przypadku? Wskazana jest kompresja wstawionych obrazków.

Warto zajrzeć.

Kurs Efektywna praca w Excelu - porady i triki

 

 

Typy danych

Podstawowe typy danych w Excelu

Boolean Typ logiczny.
Przyjmuje dwie wartości:
True – prawda
False – fałsz
Integer typ całkowity.
Obejmuje liczby całkowite z zakresu -32 768 do 32 767
Long Typ całkowity długi.
Obejmuje liczby całkowite z zakresu -2 147 483 648 do
2 147 483 647
Currency Typ wautowy.
Obejmuje liczby rzeczywiste z 15 cyfr przed i 4 po przecinku
Single Typ rzeczywisty pojedynczej precyzji.
Obejmuje liczby rzeczywiste z zakresu:
od -3.402823 E38 do -1.401298E-45
oraz od 1.401298E-45 do 3.402823E38
Double Typ rzeczywisty podwójnej precyzji.
Obejmuje liczby rzeczywiste z zakresu:
od -1.79769313486231E308 do -4.94065645841247E-324
oraz od 4.94065645841247E-324 do 1.79769313486232E308
Date Typ daty.
Obejmuje daty od 1 stycznia  100r. do 31 grudnia 9999r.
String Typ łańcuchowy często nazywany także tekstowym.
Obejmuje znaki z klawiatury – może ich być od zera do 2 bilionów
Variant Typ ogólny.
Zadeklarowanie zmiennej jako typ Variant jest w zasadzie jednoznaczne z brakiem deklaracji typu. Dostęp do danych jest jednak dłuższy i spowalnia program.

Kalkulator wyborczy

Przed nami rok wyborczy -z pewnością więc może  się przydać oparty o metodę D’Hondta kalkulator wyborczy. Oczywiście w Excelu. Nie zawiera makr i kodu VBA.
Plik jest do pobrania tu:

Kalkulator wyborczy

A jak został stworzony?

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

Do wypełnienia są żółte pola. W komórce D1 wpisujemy liczbę mandatów do podziału, w komórkach C4:M4 – T lub N dla oznaczenia koalicji, a w C5:M5 – liczbę głosów uzyskanych przez poszczególne partie. I już całą reszta jest obliczana automatycznie.
Wiersz 7 zawiera formułę sprawdzającą, czy został przekroczony prób wyborczy: 5% dla partii, 8 procent dla koalicji.
Formuła w komórce np.C7 to:
=JEŻELI(C4=”T”;JEŻELI(C6>=8%;”T”;”N”);JEŻELI(C6>=5%;”T”;”N”))

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

Formuły zawarte w wierszu 14 – to:
np. komórka C14:
=JEŻELI(LUB(C5=””;C7=”N”);0;C5)
– czyli jeżeli w tabeli z danymi w komórce C5 nie ma wartości lub w komórce C7 jest wartość N -w komórce C14 jest wartość 0.  Jeżeli żaden z tym warunków nie jest spełniony – wstawiana jest tam wartość z komórki C5 (czyli liczba otrzymanych głosów)

Od wiersza 15 – formuła wygląda tak:
np. w komórce C9:  
=JEŻELI(LUB(C14=””;C14<1);””;C$14/$B15)
– jeżeli w komórce C14 jest wartość większa od 1- jest ona dzielona przez wartość w komórce B15 czyli 2. Analogicznie – w kolejnych wierszach.

Natomiast w komórce B13 jest  formuła:
=MAX.K(C14:M500;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:

W wierszu 8 jest wyznaczona liczba mandatów dla danej partii. Zastosowałam tu formułę:
=JEŻELI(C7=”T”;LICZ.JEŻELI(C14:C500;”>=” &$B13);0)

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

Literatura informatyczna

Kurs Analiza danych w Excelu dla początkujących

Funkcja Dzień.Tyg

Funkcja Dzień.Tyg to jedna z funkcji Daty i Czasu Excela. Zwraca wartość liczbową (od 1 do 7) odpowiadającą numerowi dnia tygodnia.

Argumenty funkcji to:

    • Liczba_kolejna – wartość daty
    • Zwracany_typ -liczba związana z ustawieniami regionalnymi daty:
      • 1 – dla ustawień niedziela=1 do sobota=7
              wartość domyślna
      • 2 – dla ustawień poniedziałek=1 do niedziela=7
      • 3 – dla ustawień poniedziałek=0 do niedziela=6

Odpowiednikiem funkcji w VBA jest funkcja WeekDay()

Kurs Excel dla HR

 

Funkcja PRAWY()

Funkcja PRAWY() to jedna z funkcji tekstowych Excela. Zwraca wartość tekstową będącą określoną ilością  ostatnich znaków tekstu.

 

Argumenty funkcji to:

    • Tekst – zmienna typu tekstowego
    • Liczba_znaków – ilość znaków, które mają być zwrócone. Jeżeli argument zostanie pominięty, wstawiana jest wartość 1.

Odpowiednikiem funkcji w VBA jest funkcja Right()

Kurs Excel dla HR

 

Funkcja LEWY()

Funkcja LEWY() to jedna z funkcji tekstowych Excela. Zwraca wartość tekstową będącą określoną ilością pierwszych znaków tekstu.

Argumenty funkcji to:

    • Tekst – zmienna typu tekstowego
    • Liczba_znaków – ilość znaków, które mają być zwrócone. Wartością domyślną jest 1

Odpowiednikiem funkcji VBA jest funkcja Left()

Kurs Analiza danych w Excelu dla początkujących

 

Daty świąt w kalendarzu

Jak w Excelu wyznaczyć daty świąt kalendarzowych? Można to zrobić samymi funkcjami, bez używania makr. I to także dla świąt ruchomych, nie mających swoich stałych dat w kalendarzu.

Po zmianie roku w komórce A1 (podświetlona na żółto), daty automatycznie się zmieniają, wskazując aktualizację dla danego roku.

 

Plik Excela widoczny na screenach można pobrać tu:

Daty świąt w kalendarzu

A zastosowane funkcje (przy założeniu, że w komórce A1 jest wpisany rok)  wyglądają tak:

Data Funkcja Nazwa święta
2022-01-01 DATA(A1;1;1) Nowy Rok
2022-01-06 DATA(A1;1;6) Trzech Króli
2022-04-17 ZAOKR.W.DÓŁ(DATA(A1;5;
DZIEŃ(MINUTA(A1/38)/2+56));7)-34		 Wielkanoc
2022-04-18 A5+1 Wielkanoc
2022-05-01 DATA(A1;5;1) 1 Maja
2022-05-03 DATA(A1;5;3) 3 Maja
2022-06-05 A5+49 Zielone Świątki
2022-06-16 A5+60 Boże Ciało
2022-08-15 DATA(A1;8;15) Wniebowzięcie
2022-11-01 DATA(A1;11;11) Wszystkich Świętych
2022-11-11 DATA(A1;11;1) Święto Niepodległości
2022-12-25 DATA(A1;12;25) Boże Narodzenie
2022-12-26 DATA(A1;12;26) Boże Narodzenie

Większość dat z powyższej tabeli wyznaczona jest za pomocą funkcji Excela Data(Rok,Miesiąc,Dzień) i wynika wprost z kalendarza. Zdecydowanie bardziej skomplikowane jest wyznaczenie daty Wielkanocy, przypadającej w danym roku na pierwszą niedzielę po wiosennej pełni księżyca. Jest kilka algorytmów obliczania tej daty, zainteresowanych odsyłam tu:

Wikipedia: wyznaczanie daty Wielkanocy

A skoro coś daje się obliczyć, to z pewnością da się to zrobić także w Excelu. Wynik widoczny jest w tabeli. Powiązane z Wielkanocą są także daty Zielonych Świątków oraz Bożego Ciała, przypadające w 49 i 60 dni po jej dacie.

W praktycznych rozwiązaniach (np. różnego rodzaju grafikach) najczęściej stosuję taką tabelę w osobnym (często ukrytym) arkuszu i przy sprawdzaniu czy w danym dniu wypada święto, korzystam z funkcji Wyszukaj.Pionowo.

 

%d bloggers like this: