Moje doświadczenia z Excelem, Accesem i programowaniem w VBA
Kategorie: Wszystkie | Access | Excel | Excel - przykładowe rozwiązania | Funkcje Excela | Funkcje VBA | Różne | VBA | VBA - przykładowe rozwiązania | Znam i polecam
RSS

Excel - przykładowe rozwiązania

poniedziałek, 24 lipca 2017

Film z kanału Excel, ilustrujący zaznaczanie duplikatów z wykorzystaniem formatowania warunkowego:

Zapraszam.


 Excel - szybkie przetwarzanie danych


Kurs Excel 2010 esencja

czwartek, 03 grudnia 2015

Kończąc wątek związany z pobraniem listy dni świątecznych z Outlooka do Excela, opisany tu:

Lista świąt z Outlooka

Ponieważ opisałam tam eksport do pliku csv - to teraz w Excelu muszę zaimportować właśnie taki plik. Na wszelki wypadek przypominam, że pliki z rozszerzeniem csv to pliki tekstowe(!).

Karta Dane --> Dane zewnętrzne --> Z tekstu

import kalendarza do Excela

Po wybraniu pliku, otwiera się kreator importu.

W kroku pierwszym wybieramy opcję Rozdzielany:

import kalendarza do Excela 

Jeżeli, tak jak w tym przypadku widoczny jest brak niektórych polskich liter, musimy zmienić kodowanie - służy do tego pole Pochodzenie pliku

import kalendarza do Excela

W kroku drugim kreatora wybieramy separator rozdzielający na kolumny (w tym przypadku - przecinek):

import kalendarza do Excela 

Krok trzeci to wybranie kolumn do importu oraz ustalenie typu danych w zawartych.
Dla tego kalendarza - zaimportuję tylko 2 pierwsze kolumny - nazwa święta (jako tekst) oraz datę (jako data). Przechodzenie pomiędzy poszczególnymi kolumnami - wystarczy kliknąć na danej kolumnie.

import kalendarza do Excela

Na końcu wystarczy wskazać miejsce, gdzie importowane dane mają zostać wstawione:

 import kalendarza do Excela

Efekt końcowy wygląda tak:

import kalendarza do Excela

Chyba jednak nie o to chodziło, brużdżą tu znaki "" i to mimo, że na etapie importu zadeklarowane zostały prawidłowe typy danych. Prawdopodobnie przyczyną jest format dat pobieranych z Outlooka - zamiast np. 2015-05-03 jest 2015-5-3.
Z tym jednak można sobie poradzić szybko i prosto. Zaznaczamy kolumny i korzystając z Ctrl+H - zamieniamy " na "nic".

import kalendarza do Excela 

I już, gotowe.

 



 


 

Kurs Excel - zaawansowane techniki>

wtorek, 10 listopada 2015

W ramach wywiązywania się z obietnic (zostało mi jeszcze kilka do załatwienia) dziś odpowiedź na pytanie:jak zaimportować do Excela listę świąt z Outlooka?
Jeden z najprostszych sposobów jest taki:

W Outlooku przechodzimy do Plik --> Opcje --> Zaawansowane i wyszukujemy Export.

Eksport kalendarza kliknij, aby powiększyć obrazek

Po naciśnięciu przycisku Export, w nowym formularzu wybieramy Export do pliku:

Eksport kalendarzakliknij, aby powiększyć obrazek

Przechodzimy dalej i w kolejnym formularzu wybieramy typ pliku, do jakiego ma zostać wykonany eksport. Ja w tym przykładzie wybrałam plik csv.

 Eksport kalendarzakliknij, aby powiększyć obrazek

Następnie z listy folderów widocznych w Outlooku wybieramy Kalendarz

 Eksport kalendarzakliknij, aby powiększyć obrazek

W kolejnym kroku wybieramy miejsce zapisu eksportowanego pliku:

 Eksport kalendarzakliknij, aby powiększyć obrazek

W ostatnim formularzu wybieramy zakres dat do eksportu

 Eksport kalendarza

I już - mamy plik z wydarzeniami kalendarza. Kolejny krok czyli import listy świąt do skoroszytu Excela opisany jest tu:

Import listy świąt do Excela

 

 

 

Kurs Outlook - efektywna praca>>

czwartek, 22 października 2015

Ostatnio zadano mi pytanie, na które z marszu nie potrafiłam odpowiedzieć. Chodziło o stworzenie wykresu słupkowego opartego na seriach danych znacznie różniących się rzędem wielkości. Jest na to kilka sposobów, ale pytanie dotyczyło  wykresu z przerwaną osią Y.
Trochę poszperałam i znalazłam rozwiązanie, nieco pracochłonne, ale efekt końcowy jest. 

Załóżmy, że mamy 2 serie danych, z których jedna to wartości z przedziału od 0 do 10, a druga - od 1000 do 10000. Wstawiając obie serie na jeden wykres - ta pierwsza seria danych będzie w ogóle niewidoczna. Chcąc skorzystać z wariantu przerwania osi Y, tak aby widoczne były dwie osie, musimy po kolei zrobić tak.

  • Wstawiamy normalny wykres słupkowy oparty na obydwu seriach danych

    Wykres z łamaną osią Y
  • usuwamy pomocnicze linie siatki

Wykres z łamaną osią Y

  • zaznaczamy oś Y na wykresie, z menu podręcznego wybieramy Formatuj oś, a następnie ustawiamy maksimum osi Y na wartości, w której chcemy przerwać oś:

Wykres z łamaną osią Y

  • kopiujemy tak sformatowany wykres i wstawiamy je w inne miejsce arkusza (Ctrl+D)

Wykres z łamaną osią Y

  • w wykresie skopiowanym zmieniamy ustawienia osi Y - minimum w wartości, w której chcemy ustawić oś po przerwaniu

 Wykres z łamaną osią Y

  • w wykresie skopiowanym usuwamy legendę, obramowanie wykresu, tytuł oraz ustawiamy oś X na brak linii.  Efekt końcowy powinien wyglądać tak:

 Wykres z łamaną osią Y

  • zmieniamy wysokość wykresu początkowego - zaznaczając obszar wykresu i zmniejszając go w pionie

Wykres z łamaną osią Y

  • zmniejszamy także wykres skopiowany (tym razem od dołu),a następnie, trzymając goza uchwyt przenosimy go na wykres pierwotny, dopasowując go wizualnie do wykresu pierwotnego

 Wykres z łamaną osią Y

  • klikając na dole wykresu (tak, aby zaznaczyć wykres początkowy) - dodajemy legendę, tytuł itp.) .Można też dołożyć autokształt lub linię przerywaną w miejscu przerwania osi. Warto też zaznaczyć i zgrupować obydwa wykresy oraz autokształty - nie rozsypie się przy przenoszeniu.

Wykres z łamaną osią Y

Skoroszyt Excela z opisanym przykładem jest do pobrania tu:

Wykres z łamaną osią Y


czwartek, 18 czerwca 2015

W komentarzu do jednej z notek poświęconych formatowaniu warunkowemu w Excelu znalazł się problem, który w skrócie można opisać tak:

Jeżeli przykładowo komórka A1 ma wypełnienie (np. kolor zielony) zależne od formuły, jak spowodować aby dowolna komórka X, Y,Z niezależnie od zawartości
(liczby, formatu itp) zachowała kolor zielony i można było jednocześnie wpisywać w nią dowolną wartość liczbową (np. mnożnik)? 

Niestety, ale nawet w VBA nie da się odczytać wynikającego z formatowania warunkowego koloru wypełnienia komórki. Choć oczywiście można osiągnąć pożądany efekt.

Załóżmy, że mamy taki układ danych jak na screenie:

formatowanie warunkowe

Dla komórek A1:A3 wstawiamy formatowanie warunkowe: dla wartości większych od zera - kolor zielony, dla równych zero - niebieski, a dla mniejszych od zera - czerwony.

formatowanie warunkowe 

Chcąc przenieść kolor wypełnienia z formatowania warunkowego komórki A1 do komórki D1, z A2 do E1 oraz z A3 do F1, musimy w tych komórkach wprowadzić też formatowanie warunkowe. Uzależnione będzie jednak nie od wartości w danej komórce, a od komórek powiązanych.

Formatowanie warunkowe dla komórki D1:

formatowanie warunkowe

W ten sposób w komórce D1 będzie zawsze kolor wypełnienia uzależniony formatowaniem warunkowym od komórki A1.
W analogiczny sposób wystarczy wprowadzić formatowanie warunkowe dla komórek E1 oraz F1, odwołując się odpowiednio do komórek A2 i A3.
 



 

 

 

 

Kurs Excel w praktyce

sobota, 21 lutego 2015

Kolejna notka poświęcona formatowaniu warunkowemu to wykorzystanie ikon. Do dyspozycji mamy tu kilka różnych zestawów ikon:

formatowanie warunkowe 

Wybieramy dowolny zestaw ikon, a następnie przystępujemy do edycji. Domyślne ustawienia wyglądają tak:

formatowanie warunkowe

Formatowanie to można edytować, zmieniając ikony, ustalając wartości przypisane do poszczególnych ikon itd. Do wyboru są zestawy ikon od 3 do 5.

 

 

 

 

 

Kurs Excel - zaawansowane techniki

niedziela, 04 stycznia 2015

Skoro tworzymy własny kalendarz w Excelu, to dobrze byłoby go spersonalizować, zaznaczając ważne daty i terminy. Ponieważ w tej wersji kalendarza nie ma makr, które pozwoliłyby na zdecydowanie większą automatyzację, konieczne jest skopiowanie arkusza tak, aby każdy miesiąc był w osobnym arkuszu.

Po skopiowaniu arkusza (wystarczy kliknąć prawym przyciskiem myszy na nazwę arkusza i z menu podręcznego wybrać kopiuj), zmieniamy też jego nazwę (też prawoklik i z menu podręcznego wybieramy zmień nazwę), tak, aby każdy miesiąc miał swój arkusz.

kopiowanie arkusza

Kolejny krok to zmiana wartości w komórce A1. Dla miesiąca lutego wstawiamy datę 2015-02-01, dla marca - 2015-03-01 itd.

zmiana daty

Wszystkie wartości wewnątrz kalendarza zauktualizują się automatycznie same, nie ma potrzeby wprowadzania żadnych innych zmian.

Mając kalendarz na cały rok, możemy dodatkowo wstawić komentarze przy poszczególnych datach. Samo wstawienie prostych komentarzy oczywiście jest proste i banalne. Jak jednak wstawić obrazek do komentarza?

obrazek w komentarzu

 

Pierwszy krok to wstawienie zwykłego komentarza. Następnie klikamy prawym przyciskiem myszy w krawędź komentarza (na czarną linię obramowania)

edycja komentarza

Z menu podręcznego wybieramy opcję Formatuj komentarz:

edycja komentarza

W formularzu wybieramy zakładkę Kolory i linie:

edycja komentarza

Po kliknięciu w Kolor wybieramy opcję Efekty wypełnienia:

edycja komentarza

Pojawi się nowy formularz, w którym wybieramy zakładkę Obraz, a następnie poprzez naciśnięcie przycisku Wybierz obraz -  wyszukujemy w komputerze obraz, który chcemy wstawić:

wstawianie obrazu do komentarza

Potem wystarczy zatwierdzić i już. Efekt końcowy wygląda tak:

wstawianie obrazu do komentarza

 

sobota, 03 stycznia 2015

Kolejna notka związana z tworzeniem własnego kalendarza to opis "kolorowania" dni świątecznych.

formatowanie warunkowe

Kolumny G i H są pokolorowane na niebiesko i czerwono, aby zaznaczyć soboty i niedziele. Co jednak ze świętami? W miesiącu styczniu 2015 jest to np. dzień 6 stycznia. Owszem, można też z góry zmienić kolor czcionki w tej komórce, ale jeżeli zmienimy kalendarz na inny miesiąc - to też zostałaby na czerwono i zrobi się problem, gdyż dzień np. 6 lutego już świętem nie jest. Na szczęście Excel jest dynamiczny i bez problemu sobie z tym poradzi.

W skoroszycie założyłam dodatkowy arkusz "święta".

frormatowanie warunkowe

Wypisałam tam daty wszystkich dni świątecznych w roku 2015. Dodatkowo, aby łatwiej odwoływać się do tych dat w funkcjach - zdefiniowałam zakres:

zakres

Dla przypomnienia - definiowanie zakresu opisałam tu:

Nazwa obszaru w Excelu

Nie jest to konieczne, ale ułatwia pracę.

Kolejny krok to ustawienie formatowania warunkowego dla tych dat, które są na liście świąt. Wykorzystuję tu formatowanie warunkowe za pomocą funkcji. Przy bardziej skomplikowanych formułach formatowania warunkowego robię to często tak, że formułę tworzę w dowolnej komórce arkusza i sprawdzam wynik (formatowanie zadziała  dla wyniku funkcji Prawda czyli 1), a następnie kopiuję formułę do formularza formatowania warunkowego, usuwając ją jednocześnie z komórki arkusza.

W tym przypadku zaczęłam od komórki B5

formatowanie warunkowe

 

Formuła tu zastosowana to:
=JEŻELI(B5<>"";JEŻELI(CZY.BRAK(PODAJ.POZYCJĘ(B5;Swieta;0));;1))
Na początek sprawdzam, czy komórka nie jest pusta. Jeżeli nie - to za pomocą funkcji Podaj.Pozycję szukam zawartej w niej daty na liście świąt czyli w obszarze zdefiniowanym jako święta. Jeżeli znajdzie - to wynik działania całej funkcji wyniesie 1. I tym samy włączy się formatowanie warunkowe czyli nastąpi zmiana koloru czcionki na czerwoną. Jeżeli daty nie ma na liście (a więc nie jest to święto), funkcja Podaj.Pozycję zwróci błąd #N/D! - dlatego "opakowałam" ją w funkcję Czy.Brak - w ten sposób otrzymuję wartość logiczną odpowiadającą na pytanie: jest czy nie jest na liście.
Na końcu jeszcze modyfikuję zakres działania tej formuły na obszar dat całego kalendarza:

formatowanie warunkowe

 

Dodatkowo, również formatowaniem warunkowym wyróżniam bieżącą datę. Zasada identyczna, tylko inna funkcja:
=JEŻELI(B5=DZIŚ();1)
Jeżeli data w komórce jest równa wartości zwracanej przez funkcję Dziś() - to funkcja zwraca wartość 1 (czyli prawda) i uruchamiania się mechanizm formatowania warunkowego. W tym przypadku - ustawiłam zielone tło i zmianę czcionki na kursywę.



Kontynuując wątek tworzenia własnego kalendarza pora przyporządkowanie dat do poszczególnych dni tygodnia.Tu akurat w każdym tygodniu będą inne formuły.

Pierwszy tydzień

daty w kalendarzu

W komórkach B5:H5 musimy wyszukać - w jakiej kolumnie (czyli w jakim dniu tygodnia) znajdą się pierwsze dni tygodnia. Punktem wyjścia jest data zapisana w komórce A1 czyli pierwszy dzień danego miesiąca.
Najprościej jest w komórce B5 - wystarczy po prostu sprawdzić, czy data w A1 to poniedziałek. Formuła to:
JEŻELI(DZIEŃ.TYG($A$1;2)=1;$A$1;"")
Działa w ten sposób, ze jeżeli dzień tygodnia daty zapisanej w A1 jest równy 1 (czyli poniedziałek) - to komórka B5=A1. Jeżeli nie - to wynikiem działania funkcji jest pusty string. 

Dla komórek C5 do H5 potrzebna jest bardziej rozbudowana funkcja. Przede wszystkim musimy sprawdzić, czy komórka z lewej strony jest pusta. Jeżeli tak - to znowu sprawdzamy numer dnia tygodnia. Jeżeli natomiast w komórce po lewej stronie jest data - to zwiększamy ją o 1. Formuła dla C5 wygląda tak:
=JEŻELI(B5="";JEŻELI(DZIEŃ.TYG($A$1;2)=2;$A$1;"");B5+1)

Drugi i trzeci tydzień

Drugi i trzeci tydzień w każdym miesiącu są akurat pełne, więc nie ma problemu - w kolejnych komórkach wystarczy tylko zwiększać datę o 1 w stosunku do komórek poprzednich.
Dla B6 formuła to:
=H5+1
Dla C6:
=B6+1
W pozostałych komórkach zakresu B6:H7 - analogicznie.

Czwarty, piąty i szósty tydzień

Teoretycznie czwarty tydzień każdego miesiąca też z reguły jest pełnym tygodniem, wiec pewnie też wystarczyłoby zastosować formuły analogiczne do tych, jakie występują w drugim i trzecim tygodniu. Zastosowałam tu jednak także, obowiązkowe dla piątego tygodnia sprawdzanie, czy dany miesiąc już się skończył.
Formuła np.dla komórki B8:
=JEŻELI(H7="";"";JEŻELI(H7=NR.SER.OST.DN.MIES(H7;0);"";H7+1))
dla C8:
=JEŻELI(B8="";"";JEŻELI(B8=NR.SER.OST.DN.MIES(B8;0);"";B8+1))
Sprawdzam najpierw, czy poprzednia komórka jest pusta. Jeżeli tak - to wartość komórki bieżącej również jest pusta. Jeżeli nie - to następuje sprawdzenie, czy zawarta tam data nie jest datą ostatniego dnia miesiąca i w przypadku prawdy - funkcja wstawia pusty string, a w przypadku fałszu - zwiększa datę o 1.


Przydatne opisy funkcji:

Jeżeli()

Dzień.Tyg()

Eomonth() , a od Excela 2010 - Nr.SER.OST.DN.MIES()

piątek, 02 stycznia 2015

Podstawą każdego kalendarza są daty w nim zawarte. To oczywiste. Podobnie jest w Excelu. Chcąc stworzyć własny kalendarz - na przykład taki jak opisany w notce:

Jak zrobić sobie własny kalendarz

też musimy zacząć od daty. Na wszelki wypadek przypominam, że data w Excelu to liczba odpowiadająca liczbie dni od 1 stycznia 1900r. Szczegóły są tu:

Data i czas to liczby

Chcąc wpisać prawidłową datę - musimy użyć standardu: RRRR-MM-DD. Choć nie oznacza to, że taki format musi być widoczny również na ekranie - sposób prezentacji danych to już kwestia odpowiedniego sformatowania komórki.
W moim kalendarzu widać to w komórce A1, gdzie wpisana jest data pierwszego dnia miesiąca, którego dotyczy dana karta kalendarza. 

formatowanie daty

Jak widać na pasku formuły - wpisana jest data 2015-01-01, natomiast w samej komórce A1 widnieje styczeń 2015. Wynika to z niestandardowego formatowania komórki:

formatowanie daty

W tym przypadku zastosowałam format mmmm rrrr - w ten sposób wyświetlana jest pełna nazwa miesiąca oraz rok w postaci 4 cyfr.

W części głównej kalendarza czyli komórkach B5:H9 też zastosowałam formatowanie niestandardowe, tym razem jednak wskazując sam dzień miesiąca:

 

formatowanie daty

W tym przypadku, ponieważ efekt końcowy ma być tylko liczbą oznaczającą kolejne dni miesiąca, formatowanie to: dd.

I tyle w temacie dat - kolejna notka będzie poświęcona formułom zawartym w części głównej kalendarza.


 
1 , 2 , 3 , 4 , 5
| < Lipiec 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
31            


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


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!