Moje doświadczenia z Excelem, Accesem i programowaniem w VBA
piątek, 26 lutego 2010

Poprzednia notka dotyczyła formatowania warunkowego związanego z wartością komórki, którą chcemy wyróżnić.
Formatowanie warunkowe
Możliwe jest jednak takie powiązanie, aby komórka była zaznaczona lub nie w zależności od wartości zupełnie innej komórki. Jak to zrobić?

W Excelu 2003 z pola wyboru nie wybieramy wartość_komórki_jest tylko formuła_jest.

formatowanie warunkowe

W Excelu 2007 wybieramy opcję Użyj formuły do określenia komórek, które należy sformatować.

formatowanie warunkowe

Jak przykład weźmy prosty arkusz terminów płatności i przyjmijmy, że chcemy zaznaczyć te pozycje Płatności, których Data jest mniejsza od dziś (czyli przeterminowane.

formatowanie warunkowe

W opisie reguły wpisujemy:  
 =JEŻELI(B3i ustawiamy formatowanie na czerwone wypełnienie.
Jeżeli użyjemy funkcji zagnieżdżonej i dodatkowo wprowadzimy warunek, że wyróżnione mają zostać komórki, w których data płatności jest mniejsza niż dziś oraz Zapłacono jest puste - na czerwono wyświetlą się tylko te pozycje, które nie zostały uregulowane, a termin płatnosci minął.
=JEŻELI(ORAZ(B2

formatowanie warunkowe

Ja swoje ustawienia formatowania warunkowego ustawiłam dla komórki B2 i malarzem formatów rozciągnęłam na całą tabelę.
W ramach formatowania warunkowego można ustawić kilka warunków (niekoniecznie związanych z funkcją. W tym przypadku można by np. dołożyć nową regułę, która podświetla innym kolorem te płatności, które sa do zapłacenia dziś. A takze wszystko inne - co tylko sobie wymyślimy.

Dla tych, którzy nie czują się jeszcze zbyt biegli w takim ręcznym wpisywaniu formuł - małe ułatwienie. W dowolnej pustej komórce arkusza wprowadzamy funkcję Jeżeli , ustawiamy jej parametry w miejsce pola Wartość_jeżeli_prawda wpisując 1 , a w pole Wartość_jeżeli_fałsz - nic. Następnie z paska formuły kopiujemy funkcję i wstawiamy ją do pola formatowania formułą.

formatowanie warunkowe

Łatwo i szybko - sama tez często z tego korzystam. Szczególnie w przypadkach funkcji zagnieżdżonych.

poniedziałek, 22 lutego 2010

Jest w Excelu takie narzędzie, które nazywa się formatowaniem warunkowym. Bardzo fajne i przydatne. Wykorzystuję je często w pracy do szybkiej oceny wyników pomiarów, ale jego działanie wizualizujące pozwala na wszechstronne wykorzystanie różnych danych.
Główna zasada polega na tym, że dla komórek spełniających/niespełniajacych pewnych warunków - automatycznie zmienia się formatowanie komórki (np. kolor czcionki czy wypełnienia).  
Jak zacząć? Zaznaczamy zakres komórek, dla których chcemy ustawić formatowanie. Wybieramy opcję formatowania warunkowego.
W Excelu 2003 wygląda to tak"

 formatowanie warunkowe

W Excelu 2007 bezpośrednio na pasku narzędzi głównych:

formatowanie warunkowe

 Następnie z listy rozwijalnej wybieramy warunek do spełnienia.  

formatowanie warunkowe

 Wartości, które mają definiować dane do wyróżnienia można wpisac bezpośrednio w pola. Na prostym przykładzie - założmy, że wybieamy opcje między i wstawiamy 5 i 10. Komórki spełniające ten warunek - zaznaczamy czerwonym wypełnieniem.

formatowanie warunkowe

Oczywiście można tez uzależnić wartości progowe wartością innej komórki - wówczas w polu zamiast wpisywania liczby - przechodzimy do arkusza i zaznaczamy właściwą komórkę.

Możliwości jest tu naprawdę wiele i najlepszym sposobem jest po prostu zabawa z arkuszem i przećwiczenie choć kilku z nich.
Zakres formatowania warunkowego można rozszerzyć - wystarczy użyć tu malarza formatów, o którym pisałam tu:
Malarz formatów

 

 

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 »
piątek, 19 lutego 2010

Poprzednia notka Funkcje logiczne Excela: Jeżeli... to.... opisywała wstawianie pojedyńczej formuły. Prosty przykład ustalający znak liczby w sąsiedniej komórce z matematycznego punktu widzenia daje nam jednak nieco zafałszowany wynik. Wstawienie w komórce liczby "0" powoduje otrzymanie wyniku "+", a tak naprawdę to wprawdzie zero jest liczbą nieujemną, ale nie jest to wcale tożsame z tym, że jest to liczba dodatnia. Konieczne jest więc rozbudowanie wprowadzonej formuły.
Podstawowe pytanie: B1<0 pozostaje bez zmiany, podobnie jak wartość_jeżeli_prawda. Zmiany dokonujemy w polu wartość_jeżeli_fałsz. W oknie dialogowym formuły wstawiamy więc kolejną funkcję - wybierając ją z okienka po lewej stronie. Domyśłnie pokazują sie tam ostatnio używane funkcje, ale jest też opcja "więcej funkcji".  Wybieramy funkcję "Jeżeli":


funkcja zagnieżdżona

Otwiera się nowe okno dialogowe. Z punktu widzenia algorytmu - uściślamy teraz warunek - jaki ma być wynik formuły, jeżeli w komórce nie ma liczby ujemnej. Najprościej więc ocenić wartość logiczną zdania: B1=0. Jeżeli jest to prawda - to wynik ma być "+/-", jeżeli fałsz - "+".

funkcja zagnieżdżona

W okienku formuły zapis ten wygląda tak:
=JEŻELI(B1<0;"-";JEŻELI(B1=0;"+/-";"+")).

Oczywiście jest to tylko przykład, ilustrujący mechanizm wstawiania funkcji w funkcjach. Takich zagnieżdżeń funkcji i to różnych - można wstawiać więcej. Również w polu test_logiczny.
I najlepiej pobawić się tym samemu. To najlepszy sposób nauki.

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.

wtorek, 16 lutego 2010

Jak wstawić funkcję do komórki?

 W starszych wersjach Excela wygląda to tak:

 wstawianie funkcji

W Excelu 2007 - bardzo podobnie:

wstawianie funkcji

Wybieramy kategorię funkcji, a następnie interesująca nas funkcję.
Spróbujmy zobaczyć to na przykładzie funkcji średniej arytmetycznej

 wstawianie funkcji

W komórce B3 wstawiamy funkcję ŚREDNIA (jedna z funkcji statystycznych). Komórki, z których chcemy wyznaczyć możemy wpisać ręcznie lub - szybciej i wygodniej - zaznaczyć myszką. Wciskając przycisk przejścia do arkusza (na rysunku zaznaczony) - możemy wybrać potrzebną komórkę (lub zakres komórek).

wstawianie funkcji

Jeżeli komórki są obok siebie - można wszystkie je zaznaczyć od razu. Powrót do okna funkcji - poprzez przycisk z prawej strony.

wstawianie funkcji

Teraz wystarczy nacisnać przycisk OK - w komórce już pojawia się obliczona wartość.

wstaw funkcję

Do okna dialogowego funkcji można w każdej chwili wrócić naciskając zaznaczony na rysunku przycisk. Z jego prawej strony widoczny jest zapis formuły. Można ją także bezpośrednio edytować - np. zmienic adresy względne na bezwzględne.

 

poniedziałek, 15 lutego 2010

Jak wstawić wiele danych do formuły? Ręczne wpisywanie adresów może być bardzo żmudne, w dodatku łatwo o pomyłki. Od czego jednak myszka? Obliczmy sumę danych korzystając z autosumowania:

 zaznaczanie zakresu danych w formule

Proste, gdy zakres jest w jednym arkuszu i  dodatku ciągły. A gdy tak nie jest? Wówczas zaznaczamy każdą z pojedyńczych komórek (lub zakresów) trzymając jednocześnie wciśnięty klawisz Ctrl

 wstawianie danych do formuły

Dla przypadków dowolnej funkcji, wstawiając formułę (w menu wybieramy Wstaw - Funkcja i z listy wybieramy konkretną funkcję) i to złożoną z danych w innym arkuszu czy nawet skoroszycie korzystamy z okna dialogowego. Klikając przycisk przejścia do arkusza - okienko zostanie zminimalizowane i mamy możliwość zaznaczenia intersujacych nas komórek arkusza.

wstawiane danych do formuły

W ten sposób możemy zaznaczyć także dane z innego arkusza tego samego skoroszuty (oo prostu przechodząc do niego) lub innego skoroszytu (musi być wcześniej otwarty w tym samym oknie). Powrót - poprzez przycisk z prawej strony oknienka dialogowego:

wstawianie danych do formuły

Pełne okno dialogowe pozwala w tym momencie innego, niezależnego zakresu (tym razem wybieramy "Liczba2" - analogicznie - dalej. Jesli sa takie potrzeby.

wstawianie danych do formuły

Ostatecznie poprzez przycisk OK - wracamy do komórki, w której wpisywaliśmy formułę. Wynik gotowy.

Warto przy tym pamiętać, ze  w ramach tego samego skoroszytu - Excel wstawia adresy względne. Jeżeli w formule mamy dane z innego arkusza tego samego skoroszytu - to zmiana nazwy tego arkusza - spowoduje autmatyczną zmianę jego nazwy również w formułach. Nie mam potrzeby nic poprawiać.
Jeżeli korzystamy z danych innego skoroszytu - adresy komórek defaultowo są bezwzględne. I nie ma tu automatycznej korekty formuł w przypadku zmiany nazwy.

niedziela, 14 lutego 2010

Poprzednia notka dotyczyła automatycznego wypełniania komórek w tabeli:
Wypełnianie komórek w arkuszu
W ramach uzupełnienia jeszcze kilka szczegółów. Automatyczne wypełnianie działa bez problemu przy danych standardowych (miesiac, dzień tygodnia). Przy danych liczbowych - Excel na podstawie kilku wpisanych komórek - odczyta je jako kolejne wyrazy ciągu arytmetycznego i też bez problemu dopisze następne. Co jednak zrobić, gdy nasze potrzeby są nietypowe? Wówczas musimy stworzyć własną listę, która po zdefiniowaniu też będzie automatycznie uzupełniana. Jak to zrobić?

Robimy to w opcjach programu Excel:

opcje programu Excel

(to tak na wszelki wypadek - dla użytkowników korzystajacych zawsze z ustawień domyślnych)

Z lewej strony wybieramy "Popularne", a następnie klikamy w przycisk "Edytuj listy niestandardowe". W nowym oknie możemy już tworzyć własną listę:

definiowanie listy niestandardowej 

Potem wystarczy nacisną przycisk "Dodaj" i nasza lista jest już gotowa do wykorzystania.
Zamiast ręcznego wpisywania danych - możemy też wykorzystać dane już istniejące w arkuszu. Naciskamy przycisk przejścia do arkusza:

importowanie listy

W arkuszu zaznaczamy komórki, w których znajdują się dane do zaimportowania:

importowanie danych do listy

Powrót do opcji poprzez przycisk okna dialogowego:

import danych do listy niestandardowej

Teraz pozostaje tylko zatwierdzenie wyboru poprzez przycisk "Importuj" i mamy nowa listę.

Wstawione przez nas listy można edytować lub usuwać. Nietykalne są listy standardowe Excela.

Tagi: lista tabela
10:39, marzatela , Excel
Link Dodaj komentarz »
piątek, 12 lutego 2010

Jak szybko i efektywnie wypełnić arkusz danymi? Oczywiście, można część komórek skopiować i wkleić, ale dla danych typowych i przewidywalnych - są znacznie szybsze i efektywniejsze sposoby.
Zobaczmy to na bardzo prostym przykładzie. W komórce A2 mamy liczbę 1 i chcemy ją szybko skopiować do innych komórek.

wypełnianie komórek

Po zaznaczeniu komórki ustawiamy myszką kursor w prawym dolnym rogu komórki, aż widoczny będzie tam czarny krzyżyk (na rysunku wstawilam czerwony w celu lepszego jego zobrazowania). Następnie z wciśniętym lewym przyciskiem myszy przeciągamy w dól (lub w prawo - jeżeli kopiowanie ma być w poziomie.

wypełnianie komórek

Szybko i prosto?  Spróbujmy więc sobie utrudnić zadanie.
Skoro kolumna ma w nagłówku "Lp." - to spróbujmy wypełnić ja kolejnymi liczbami naturalnymi. Do komórki A2 pisujemy 1, do komórki A3 - 2.  

wypełnianie komórek

Tym razem zaznaczamy 2 komórki A2 i A3. Znowu przeciągamy w dół. Otrzymany efekt to:

wypełnianie komórek

Przy długiej liście - sporo musielibyśmy się namęczyć, żeby tak wypełnić komórki. 
Próbujmy dalej. Do naszej tabeli dołóżmy drugą kolumnę z oznaczeniami miesięcy. Wypełnijmy tam 2 pierwsze komórki i zaznaczmy je:

automatyczne wypełnianie tabeli

Tym razem jednak nie przeciagajmy, tylko dwukrotnie kliknijmy (lewy przycisk myszy) w krzyżyk prawego dolnego rogu.

automatyczne wypełnianie tabeli

Kolumna tabeli wypełniła się automatycznie. Gdyby tak było tam np. 100 lub 1000 wierszy - optymalizacja imponująca, prawda? Warto przy tym pamiętać, że o tym - jaka duża jest tabela nie decyduje w żadny przypadku obramowanie - to tylko ozdobniki. Excel koniec tabeli widzi w ostatnim wypełnionym wierszu sąsiedniej kolumny. No i oczywiście tabela wcale nie musi się zaczynać w komórce A1.
Wypełnianie komórek w tabeli nie musi także być tylko w pionie, wzdłuż kolumn. Doskonale działa także w wierszach, poziomo.

wypełnianie poziome

A teraz zachęcam każdego do samodzielnego przećwiczenia w Excelu. Pracy w apikacjach trudno uczyc się "na sucho". To ćwiczenie czyni mistrza.

czwartek, 11 lutego 2010

Każda komórka w arkuszu Excela ma swój adres. Na przykład pierwsza komórka (lewy górny róg), na przecięciu kolumny A i wiersza numer 1 ma swój adres A1. Zapis A1 jest to adres względny komórki, jej adres bezwzględny to $A$1. Oczywiście można to zapisać również w sposób mieszany:
$A1 - bezwzględne odwołanie do kolumny, względne do wiersza
A$1 - względne odwołanie do kolumny, bezwzględne do wiersza

Tyle teorii. Wyglada to malo ciekawie i od razu wywołuje pytanie: po co to wszystko? Cóż, jest to ważne i istotne. Najlepiej zobaczyć to na przykładzie.

W arkuszu mamy fragment tabeli. W wierszu 2 (pierwszy to nagłówek tabeli) , w komórce C2 wpisujemy formułę C2=A2/B2 - Liczbę dzielimy przez dzielnik i otrzymujemy wynik1. Podobna sytuacja występuje w komórce D2, tylko tym razem adres dzielnika jest zapisany w jako adres bezwzględny : D2=A2/$B$2. Oczywiście wynik działania jest identyczny.
Różnice zaczynją być widoczne w kolejnych wierszach. Jeżeli skopiujemy warości formuł z kolumn C i D - tam gdzie są adresy względne - automatycznie formuła zmieni adres dzielnika, tam gdzie bezwzględne - dzIelnikiem zawsze będzie komórka B2.  

   adres względny i bezwzględny

W efekcie otrzymamy tez różne wyniki :

adres względny 

W zależności więc od tego, czy przy kopiowaniu formuł chcemy lub nie na stałe odnieść się do jakiejś komórki - używamy adresów względnych lub bezwzględnych.
Excel domyślnie przy kopiowaniu używa adresów względnych. Gdy jednak będziemy wstawiać powiązania z danymi z innego skoroszytu - domyślnym ustawieniem będą adresy bezwględne.

 

 Czy są pytania?  

 
1 , 2
| < Luty 2010 > |
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


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


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!