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

Wpisy z tagiem: formatowanie

poniedziałek, 22 lutego 2016

Każdemu pewnie się zdarzyło, że wprowadzając do komórki Excela ciąg cyfr będący nie liczbą tylko tekstem - został "poprawiony" przez komputer. Często nawet wtedy, gdy komórka jest sformatowana jako Tekst.
Widać to dobrze,gdy w sąsiednich komórkach mamy przewagę liczb, a do jednej z komórek wprowadzamy ciąg cyfr np. 012345678. W większości przypadków - Excel od razu po zatwierdzeniu poprawi to na liczbę 12345678. Czasem nawet zero na początku nie jest potrzebne, a ciąg np.12345000 może nagle zmienić się na  postać typu 1,2345E+7.

Co zrobić w takich przypadkach, gdy jednak chcemy widzieć tam tekst? Sprawa jest banalnie prosta: na początku wystarczy wstawić znak ' - apostrof górny. To oznaczenie, że w tej komórce jest tekst i żadna korekta nie zostanie wprowadzona.


 



 


 

Kurs Excel w praktyce>

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.


wtorek, 06 sierpnia 2013

O danych typu Data i Czas pisałam już kilkakrotnie. Sądząc jednak z różnych wypowiedzi spotykanych na forach tematycznych - ciągle jest z tym problem. Przy okazji - polecam najlepsze forum Excela w polskiej części internetu:
Excelforum.pl

O co więc chodzi z ta datą? Wielu użytkowników Excela ma problem ze zrozumieniem tego typu danych. Przede wszystkim więc warto pamiętać o sprawie zasadniczej - data to liczba. To, co widzimy na ekranie - ładna data, gdzie nawet miesiąc jest wpisana słownie to tylko kwestia odpowiednio sformatowanego wyświetlania na ekranie, nadal jednak jest to liczba. Jej wielkość jest wyznaczona liczbą dni jakie upłynęły od "początku świata", a konkretnie - od 1 stycznia 1900 roku.
Można się o tym przekonać samemu - w kolumnie A wstawiamy liczby, w kolumnie B - odniesienia do komórek z tego samego wiersza kolumny A (np. B1=A1). Kolumnę B formatujemy jako datę - efekt jak na obrazku:

data

A co z czasem? Godzina to część ułamkowa - czyli to co po przecinku. Zmieniając formatowanie na pełna datę wraz z godziną oraz wstawiając liczby z wartościami po przecinku - widzimy to:

data

Istotne jest też tu to, że do komórek sformatowanych jako data i/lub czas nie mają odniesienia funkcje tekstowe. Dokładnie rzecz ujmując - zostanie wyznaczona wartość związana z liczbą przechowywaną w komórce, a nie sposobem jej prezentacji.
Np. Dł(B1) =1 - w komórce B1 faktycznie jest jednocyfrowa liczba 1.

Mimo, że dane w kolumnie B wydają się bardzo regularne, nie da się ich również podzielić za pomocą opcji Tekst jako kolumny.

 


 

 

 

 

 

Kurs Excel - zaawansowane funkcje i formuły


wtorek, 18 września 2012

W praktycznym wykorzystaniu często jest tak, że chcielibyśmy mieć wyświetlone dane w jakiś specjalny sposób. Szczególnie przydatne jest to w przypadku daty.
Jak sformatować sobie datę? Jest to bardzo proste. Klikamy prawym przyciskiem myszy na komórce lub obszarze komórek i z menu podręcznego wybieramy opcje Formatuj Komórki ...
Następnie wybieramy sobie odpowiadający nam forma z grupy formatów Daty.

 formatowanie daty

Wybieramy żadany przez nas format i już. 

Czasami jednak okazuje się, że standardowe formaty nas nie zadowalają i chcielibyśmy ustawić własny format. Jest to oczywiście też możliwe.
Wybieramy w takim przypadku kategorię Niestandardowe i sami wpisujemy pożądany przez nas format.

 

formatowanie daty

Zasady formatowania sa następujące:

rr - wyświetla rok w formacie dwucyfrowym np. 12
rrrr- wyświetla rok w formacie czterofyfrowym np.2012
mm- wyświetla miesiąc w formacie liczby np. 09
mmm- wyświetla miesiąc w formacie słownym skróconym np. wrz
mmmm - wyświetla miesiac w pełnej nazwie np. wrzesień
dd- wyświetla dzień w formacie liczbowym np. 18
ddd - wyświetla dzień w formacie słownym skróconym np. wt
dddd - wyświetla dzień w formacie pełnej nazwy dnia tygodnia

Ustawiając włane formatowanie np. jako dddd-dd-mm otrzymujemy dzisiejszą datę w taki sposób:

formatowanie daty

Warto przy tym pamiętać, że w samej formatowanej komórce nadal jest pełna wartość komórki. Formatowanie zmienia tylko sposób jej wyświetlania w arkuszu Excela. 

   

 

 

 

 

poniedziałek, 11 czerwca 2012

Z Excela korzystamy w różnych sytuacjach. Owszem, jest to część pakietu biurowego, ale nawet w wersji Home często jest również bardzo przydatny.
Jeżeli sami korzystamy z jakeigoś pliku - nie ma problemu. Każdy ma swoje przyzwyczajenia i wpisując dane do stworzonego przez siebie arkusza - raczej nie usuniemy sobie np. wierszy czy kolumn z kluczowymi formułami. Gorzej, jeżeli z takiego arkusza korzysta inny użytkownik - również mający swoje nawyki i przyzwyczajenia, zupełnie odmienne od naszych. 

Chcąc zachować własną pracę -  musimy zabezpieczyć arkusz Excela przed zmianami wprowadzanymi przez innych użytkowników. Jak? Na wstędze Recenzja są przyciski Chroń arkusz i Chroń skoroszyt. Wystarczy z nich skorzystać, zapisać skoroszyt i ... zobaczyć, że do arkusza nie da się wprowadzić żadnych danych, gdyż nie zdjęliśmy blokady z komórek, w które powinny byc wprowadzane dane.

Pełan instrukcja, krok po kroku jak zabezpieczyć arkusz, skoroszyt, a także jak ukryć w skoroszycie Excela powiązania, formuły, kod VBA jest w tym artykule:

Zabezpiecz i ukryj

Zapraszam też do innych moich artykułów na Czytamonline:

marzatela na Czytamonline

A jak wygląda taki zabezpieczony arkusz - można zobaczyć pobierając DEMO programu do rozsyłania maili:

Mailing

 

poniedziałek, 14 lutego 2011

Formatowanie warunkowe jest jednym z ciekawszych narzędzi własnych Excela. Pisałam o tym tu:
Formatowanie warunkowe
Formatowanie warunkowe nieco bardziej zaawansowane
Przydaje się w praktycznym wykorzystaniu, gdy wyróżnienie okreśłonych danych może pomóc w analizie czy choćby uatrakcyjnić wygląd arkusza.
Problem pojawia się wtedy, gdy potrzebujemy więcej warunków formatowania niż ograniczona liczba dostępnych warunków systemowych. W takiej sytuacji możemy wykorzystać swoje własne formatowanie za pomocą kodu VBA.

Dobrym przykładem może być tu miesięczny grafik dyżurów dla 15 osób.

formatowanie arkusza Excel 

W legendzie - czyli komórkach F3 do G17 oznaczeniom L1 do L15 przypisujemy kolory wypełnienia komórki. Formatowanie to automatycznie przenosi się do samego grafika czyli komórek A1 do D32.

Zdefiniujmy najpierw konieczne funkcje i procedury


Public Function Kolorek(Symbol)

Dim Przeglad As Range 
For Each Przeglad In Range("F3:F20")
    If Przeglad.Value = Symbol Then
       Kolorek = Przeglad.Interior.Color
       Exit Function
    End If
Next
Kolorek = 0

End Function

Funkcja Kolorek wyszukuje Symbol (czyli wartość komórki będącej argumentem funkcji) w obszarze Range (w tym przypadku zdefiniowanej komórkami Legendy w arkuszu Excela). Obszar jest przeszukiwany w ramach pętli For Each...Next (zob. Pętla For Each ... Next) i gdy wynik poszukiwania jest pozytywny - funkcja Kolorek przyjmuje wartość numeru koloru tła.

Kolor mamy ustalony, potrzebna jest jeszcze procedura zmieniająca kolory w samym arkuszu.


Public Sub Pokoloruj()

Dim JakiKolor
Dim Komorka As Range
Dim JakiSymbol
 For Each Komorka In Range("B2:F40")
       JakiSymbol = Komorka.Value
       If JakiSymbol <> "" Then
              JakiKolor = Kolorek(JakiSymbol)
              If JakiKolor = 0 Then Komorka.Interior.Pattern = xlNone Else Komorka.Interior.Color = JakiKolor
             
        Else
         Komorka.Interior.Pattern = xlNone
        End If
 Next
 End Sub

Procedura Pokoloruj przechodząc przez poszczególne komórki grafika (czyli zakres B2 do F40), na podstawie ich wartości przypisanej do zmiennej Symbol w fukcji Kolorek, sprawdza i zmienia kolor komórek w grafiku.

Teraz wystarczy tylko stworzyć przycisk uruchamiający makro Pokoloruj. Prosciej jednak byłoby przypisać je do któregoś ze zdarzeń arkusza lub zdarzeń skoroszytu.  
Na przykład tak:

Private Sub Worksheet_Activate()
Pokoloruj
End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column < 8 And Target.Row < 33 Then Pokoloruj

End Sub

Makro działa w tym przypadku przy otwarciu arkusza oraz przy zmianie wartości komórek z określonego zakresu.  

środa, 16 czerwca 2010

Excel z swojej defincji jest arkuszem kalkulacyjnym i czesto jest wykorzystywany do róznego rodzaju obliczeń. W poszczególnych komórkach występować mogę nie tylko liczby bezpośrednio wpisywane przez użytkownika, ale także wyniki obliczeń formuł. Wynik takiego działania bardzo często jest liczbą z wieloma miejscami po przecinku. Zbytnia dokładność nie zawsze jest konieczna, a często wygląda mało estetycznie. W takich sytuacjach najłatwiej jest sformatować komórkę ograniczając liczbę miejsc po przecinku.
Dla przypomnienia - najszybciej poprzez zaznaczenie komórki lub całego zakresu komórek i kliknięcie prawym przyciskiem myszy. Z rozwiniętego w ten sposób menu podręcznego wybieramy opcję Formatuj komórki... i ustawiamy wybrane przez nas opcje wyświetlania.

formatowanie liczb

Efekt jest, ale problem może pojawić się przy dalszym wykorzystaniu danych. Wprawdzie na ekranie widzimy liczby zaokrąglone, ale jest to tylko efekt wizualny. W komórce nadal są pełne wartości komórek i do dalszych obliczeń brane będą te pełne wartości, wywołując czasem niespodziewane efekty.

formatowanie liczb 

W komórkach B2, B3, B4 są liczby. Ich zaokrąglenia do liczby całkowitych - w odpowiednich komórkach kolumny C. Wiersz 5 to suma tych komórek. W komórce B5 wynik jest prawidłowy, ale w komórce C5 widzimy, że 1 + 1 + 1 = 4. Absurd? Tylko wizualnie - faktycznie sumowane są pełnie wartości, a nie ich sformatowany widok.
Chcąc doprowadzic do rzeczywistego zaokrąglania liczb musimy więc zastosować odpowiednie formuły. I o tym będzie kolejna notka.

ś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

sobota, 13 marca 2010

Podobnie jak filtr - sortowanie tabel jest jednym z prostszych narzędzi własnych Excela. Stosujemy je po to, aby uporządkować dane. Jak to zrobić?

W Excelu 2003 zaznaczamy kolumny tabeli do posortowania. Pamiętajmy o zaznaczeniu wszystkich i  o wyłączeniu filtrów. Następnie wybieramy z menu:
Dane --> Sortuj.... 
Otwiera się nowe okno, w którym ustawiamy poszczególne opcje sortowania:

sortowanie tabeli Excel

Bardzo ważną sprawą jest prawidłowe zaznaczenie opcji wiersza nagłówka. Excel jest tu domyślnie bardzo mądry, ale widziałam już takie tabele, w których nagłówek składał się z większej ilości wierszy, w dodatku w kilku miejscach scalonych i rozsypywało się wszystko. Jest to istotne szczególnie przy dużych tabelach.
Efekt po sortowaniu - tak jak w przykładowym pliku mini katalogu:

Excel - tabela po sortowaniu

Tabela została posortowana tylko w kolumnie Dział istnieje możliwość posortowania dodatkowo jeszcze w 2 kolumnach tzn. w ramach Działu - w drugiej kolejności np. Autor, a w trzeciej - np. Tytuł.

Excel 2007 ma więcej możliwości opcji sortowania. Zasada ogólna działania jest tu jednak taka sama.
Z wstęgi Dane wybieramy Sortuj

 sortowanie tabeli Excel

Tu okienko wygląda nieco inaczej, ale tylko pozornie. Każdą kolumnę możemy posortować klasycznie - tak jak opisałam to wyżej. Różnicą jest możliwość zdefiniowana nie trzech, a większej ilości poziomów sortowania.

sortowanie tabeli Excel

Ciekawym rozwiązaniem jest unikalna dla tej wersji mozliwość sortowania komórek według kolorów czcionki czy kolorów komórek

sortowanie tabeli Excel

W połączeniu z formatowaniem warunkowym komórek w tabeli - można w prosty i szybki sposób dotrzeć do widoku potrzebnych nam danych.

 

sobota, 20 lutego 2010

Malarz formatów Excela to małe, ale niezwykle użyteczne narządzie. Znajdziemy je na standardowym pasku narzędzi. 

W Excelu 2003 tu:

Malarz formantów

W Excelu 2007 tu:

malarz formantow

 Na czym polega jego magiczne działanie? Na prostym kopiowaniu właściwości komórki. Jeżeli w jakiejś komórce mamy ładnie ustawioną czcionkę, kolor, zadeklarowany typ danych czy obramowanie - wystarczy przejść do tej komórki, kliknąć przycisk malarza formatów, a następnie - komórkę, w której chcemy miec takie same własciwości. I już - formatowanie skopiowane.
Jeżeli formatowanie ma zostać skopiowane do większej ilości komórek - to przycisk malarza formatów klikamy dwukrotnie, a następnie zaznaczamy komórki do sformatowania.

 

06:10, marzatela , Excel
Link Dodaj komentarz »
| < 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!