makroExcel

Programy w Excelu - samodzielne aplikacje pod MS Excel...

MENU

Najciekawsze działy

Poprawny CSS! Poprawny XHTML 1.0 Strict!

Słowo wstępu

Arkusz kalkulacyjny Microsoft Excel spośród wszystkich tego typu programów cieszy obecnie największą popularnością. Jego funkcjonalność można zwiekszyć przez odpowiednie wykorzystanie oferowanych w standardzie formantów, makropoleceń oraz formularzy, a także przez skojarzenie tych elementów z fragmentami procedur napisanych w języku VBA (Visual Basic for Applications). Tak stosowany Microsoft Excel jest elastycznym narzędziem pracy mogącym, w wielu przypadkach z powodzeniem, zastąpić specjalistyczne programy i aplikacje. Tych możliwości arkusza kalkulacyjnego większość użytkowników nawet nie stara się wykorzystać, a nie jest to wcale takie trudne. Na niniejszej stronie pokazano niektóre praktyczne rozwiązania mogące znaleźć zastosowanie w niemal każdej firmie. Przedstawione przykłady w większości zaczerpnięte są z pracy zawodowej autora strony.

ZAŁOŻENIA PODSTRONY 'PORADY EXCEL'

Na stronie zainstalowany jest system statystyk Google Analytics. Wśród archiwizowanych przez niego danych wchodzi również rejestr fraz, które użytkownicy wpisali do wyszukiwarki przed wejściem na niniejszą witrynę. Część z szukanych zagadnień dotyczy możliwości zastosowania makr i języka VBA (tę część przedstawiam na podstronie Zagadnienia VBA), a część dotyczy możliwości czystego Excela (i tę część przedstawiam na tej stronie). Oczywiście sprecyzowanie tematu poszukiwań jest zadaniem trudnym i opiera się w dużej mierze na moich przypuszczeniach. Poniżej przedstawiam spis artykułów w zbiorach tej podstrony.
Opracowania powstały na podstawie mojej wiedzy w tym zakresie.
Jeżeli zauważasz nieścisłości w tekście proszę o kontakt. Na podstronie Lista fraz przedstawiam spis fraz wprowadzanych do wyszukiwarki poruszających tematy opracowane na tej witrynie.

ZAWARTOŚĆ DZIAŁU PORADY EXCEL


Spis porad Do góry podstrony Do strony głównej

Formatowanie warunkowe KLIKNIJ by (roz)winąć

Zadanie:
W tabeli z wartościami liczbowymi zaznaczyć komórki z przedziału wartości od 0,5 do 1,0.
Do przykładu użyję tabeli doboru średnic rur ogrzewania.
- zaznaczam interesujący mnie zakres tabeli,
Formatowanie warunkowe w excelu
- wybieram z menu Format/formatowanie warunkowe,
Formatowanie warunkowe w excelu
- W widocznym formularzu przyciskiem 'Formatuj' mogę dobrać parametry czcionki i tła dla poszczególnych parametrów,
Formatowanie warunkowe w excelu
- wybór koloru tła komórki,
Formatowanie warunkowe w excelu
- Wynik działania:
Formatowanie warunkowe w excelu
Ze względu na fakt, że Excel 2003 przyjmuje do trzech różnych warunków formatowania, przyciskiem 'Dodaj' uzupełniam kolorowankę o zakresy liczb między 0 i 0,5 (kolor błękitny) i większych od 1 (zielony)
Formatowanie warunkowe w excelu
Dla celów użytkowych warto jest pamiętać, że przyciskiem 'Usuń' mogę zlikwidować dany warunek.
Wynik działania:
Formatowanie warunkowe w excelu
Małe uwagi: - najlepiej jest zaznaczać zakres bez nagłówków kolumn i opisu wierszy tabeli,
- formatowanie warunkowe nie pozwoli zmienić parametrów komórki ręcznie tzn. nie można zmienić koloru komórki, w której ten kolor ustala właściwość formatowania warunkowego,
- bardzo cenną możliwością jaką oferuje formatowanie warunkowe jest obsługa błędów. Jeżeli zamiast parametru wartość komórki wybierzemy z listy 'Formuła jest' a w miejscu wartości wpiszemy '=czy.błąd(a1:z100)' gdzie a1:z100 to zakres tabeli, którą formatujemy warunkowo to wszystkie błędy możemy odpowiednio wyróżnić (lub 'wygasić'),

Formatowanie warunkowe w excelu
- formatowanie warunkowe jest często wykorzystywane do 'ukrywania' zawartości komórek przez jednakowy dobór zarówno koloru czcionki jak i tła lub ustawienie koloru czcionki na biały. W ten sposób formatuje komórki tabela listy obecności prezentowanej na tej witrynie,
- wartością komórki jest też zawarty w niej tekst, dzięki czemu możemy w łatwy sposób wyróżnić wiersze z określonym wpisem,

FORMATOWANIE WARUNKOWE W EXCEL 2007 - UWAGI
Opcje formatowania warunkowego zostały w Excelu 2007 znacznie rozbudowane. Na wstążce dostępne są w czytelnie opisanych przyciskach menu 'Narzędzia główne'. Generalnie uproszczono dostęp do najczęścisj używanych opcji, wprowadzono możliwość definiowania gradientu kolorów (moim zdaniem, by ta funkcja poprawiała czytelność, a nie była tylko efektownym bajerem trzeba korzystać z niej z dużą rozwagą i wyczuciem) oraz wprowadzono duże możliwośći definiowania własnych warunków w formułach formatowania.
Formatowanie warunkowe - własne formuły. W czeluściach internetu znalazłem informację, że wprowadzenie jako własnej formuły wyrażeń:
- '=LICZ.JEŻELI($A$2:$A$6;A2)=3',
- '=LICZ.JEŻELI($A$2:$A$6;A2)>=4',
- '=LICZ.JEŻELI($A$2:$A$6;A2)=0'.
pozwala na zanzaczenie odpowiednio:
- wartości występujących trzy razy,
- wartości występujących cztery razy,
- wartości unikalnych
Formatowanie warunkowe w excelu 2007
Formatowanie warunkowe - skala kolorów dla zaznaczonego obszaru.
Formatowanie warunkowe w excelu 2007 skale kolorów
Formatowanie warunkowe - przyklada dla wartości powyżej średniej.
Formatowanie warunkowe w excelu 2007 wartości powyżej średniej

Jedną z częściej przydatnych funkcji Excela jest automatyczna zmiana formatowania komórki (kolor tła lub/i tekstu) w zależności od wprowadzonych wartości np. zaznaczenie kolorem czerwonym komórki o wartościach z szukanego zakresu. Duża część użytkowników błędnie traktuje to zagadnienie jako wymagające pisania makr. W rzeczywistości wystarczy standardowa właściwość Excela jaką jest formatowanie warunkowe. W Excelu 2007 dział ten został bardzo rozbudowany, ale już w wersji 2003 daj spore możliwości. Przykładowe zastosowania to:
- wyróżnienie komórek, których wartości mieszczę się w określonym przedziale wartości lub spełniają określony warunki,
- 'ukrycie' komórek których wartości nie chcemy pokazywać przez odpowiedni dobór koloru czcionki i tła,
- wyróżnienie lub ukrycie komórek w których powstaje błąd,
Wykorzystując tą funkcję możemy np. wyróżnić na zestawieniu materiałów do zamówienia pozycje o określonym przedziale cenowym lub nazwie, w tabelach obliczeniowych wyróżnić wartości np. mniejsze od zera lub 'wygasić' komórki o zadanych wartościach (np. plan spłaty pożyczki przy odpowiednio wysokiej racie skraca okres spłaty - przy analizie możemy wyłączyć niefortunnie wyglądające spłaty przy wartościach ujemnych) itd.
UWAGA
Przygotowałem samodzielną wtyczkę rozszerzającą możliwości Excela między innymi o funkcje i instrukcje zliczania wartości i ilości komórek wg ich koloru - opis na stronie Dodatek_GK. Zastosowanie wtyczki nie wymaga umiejętności programowania.

Spis porad Do góry podstrony Do strony głównej

Formatowanie warunkowe na podstawie wartości innej komórki niż formatowana KLIKNIJ by (roz)winąć

Punkt powstał na podstawie dyskusji z jednym z użytkowników strony, który wskazał, że zastosowane przeze mnie w jednym z plików rozwiązanie dotyczące formatowania warunkowego nie jest jedynym. Przyjęte przeze mnie opierało się na wartościach wpisanych w formatowanej komórce. Zmiana tych wartości powodowała zmianę koloru tła. Użytkownik zaproponował formatowanie oparte o formuły zawarte w innych komórkach. W ten sposób zmiana wartości komórek, w których dokonujemy obliczeń nie ma wpływu na kolor tła komórki. Warto znać tą metodę (czasem potrzebujemy zmiany formatu komórki na podstawie jej wartości, a czasem na podstawie innych parametrów). Prosty przykład stanowi modyfikację pliku przesłanego mi przez użytkownika strony.
 na podstawie wartości innej komórki niż formatowana W kolumnie B zawarte są daty, dane w komórkach E i F to zwykła tabelka, która posłuży przypisaniu wartościom liczbowym nazw dni tygodnia. W komórkach kolumny C formuła "Wyszukaj pionowo" uzyskujemy wartości tekstowe określające nazwy dni tygodni. Formuła wyszukuje wartość liczbową na uzyskaną z funkcji "Dzień tygodnia" w tabelce kolumn E i F i na tej podstawie ustala nazwę dnia tygodnia.
Innymi słowy "=WYSZUKAJ.PIONOWO(DZIEŃ.TYG(B2;2);$E$2:$F$8;2)" kolejno:
DZIEŃ.TYG(B2;2) - wartość liczbowa dnia tygodnia dla komórki B2,
$E$2:$F$8;2 - tabela, w której nastąpi wyszukanie wartości liczbowej dnia tygodnia (2 to numer kolumny z tabelki, której wartość będzie traktowana jako wynik formuły "Wyszukaj pionowo".
Pokazane na screenie formatowanie komórki "C2" oparte jest o wartość formuły z komórki "B2" - dla wartości 7 (niedziela) kolor czerwony, dla 6 (sobota) kolor oliwkowy i dla pozostałych wartości (dni robocze tygodnia) kolor błękitny. w formatowaniu nie bierze udziału komórka "C2" tj. ta dla której tło właśnie zmieniamy. Taką formuła można sformatować cały wiersz danych w zależności od komórki kolumny porządkowej lub kolumn pomocniczych.

W punkcie przedstawiono możliwość formatowania warunkowego komórki (tj. zmiany koloru tła, czcionki itp. komórki) w zależności od wartości innej komórki niż aktualnie formatowana. Warunki formatowania podane są nie za pomocą wartości, a formuł. Punkt powstał na podstawie uwag i podpowiedzi jednego z użytkowników strony.

Spis porad Do góry podstrony Do strony głównej

Formatowanie warunkowe na podstawie wartości innej komórki niż formatowana KLIKNIJ by (roz)winąć

Często w analizowanych tabeli Excela zachodzi konieczność wyszukania wartości występujących więcej niż raz lub dokładniej występujących określoną ilość razy.
wyszukiwanie duplikatów Excel
W tabeli zaznaczamy komórkę "E6" i uruchamiamy:
- dla Excela 2003 menu "Format", polecenie "Formatowanie warunkowe"

wyszukiwanie duplikatów z wykorzystaniem formatowania warunkowego
- dla Excela 2007
Dla wyświetlonego okna dialogowego standardową funkcją jest określanie wartości. Przez zmianę funkcji możemy również określić wpisywaną formułę.
zaznaczanie wyników kolorem

Jako wartość formuły wpisuję:
=LICZ.JEŻELI($E$6:$E49;E6)=12
Później wybieramy przycisk formatuj i wybieramy kolor wypełnienia - w tym przypadku czerwony
wyszukiwanie duplikatów w Excelu

Formułę ta ma następujące znaczenie: w zakresie komórek E6 - E49 wyszukaj ile komórek z tego zakresu ma wartość taką samą jak wartość w komórce E6. Sprawdź czy liczba policzonych wartości = 12. Jeśli tak to zaznacz wskazanym kolorem.
Następnie używamy 'kopiuj". Zaznaczamy całą kolumnę, wybieramy z menu podręcznego "Wklej specjalnie" i wybieramy "Formaty"
zaznaczanie duplikatów Excel

Na ostatnich zrzutach ekranu widać wyniki formatowania:
- dla kolumny E wartości, które występują 12 razy
- dla kolumny F wartości, które występują 3 razy

Jeżeli w formule zaznaczymy:
=LICZ.JEŻELI($E$6:$E49;E6)>1 - zaznaczymy wartości występujące więcej niż raz,
=LICZ.JEŻELI(E:E;E6)>2 - zaznaczymy wartości występujące więcej niż dwa razy, ale w całej kolumnie E.
zaznaczanie kolorem duplikatów Excel

W punkcie przedstawiono możliwości wyszukiwania i zaznaczania duplikatów w Excelu z wykorzystaniem formatowania warunkowego. W przykładach zrzuty ekranu. Zaznaczanie z wykorzystaniem wyszukiwania na podstawie formuły.

Spis porad Do góry podstrony Do strony głównej

Rozdzielanie i łączenie tekstu KLIKNIJ by (roz)winąć

Na początek mała dygresja. Do napisania tego tematu skłoniła mnie głównie zaistniała w mojej pracy zawodowej sytuacja - księgowość potrzebowała spis osobowy w postaci dwóch osobnych kolumn (imiona i nazwiska osobno) wykonany na podstawie posiadanego arkusza jednokolumnowego (imiona i nazwiska razem w jednej kolumnie). Zadanie wykonałem pisząc krótkie, jednorazowo użyte makro. Dopiero później znalazłem standardową funkcję Excela realizującą to zadanie. Stąd wniosek, że przed pisaniem makr należy przyjrzeć się dokładnie możliwościom samego arkusza kalkulacyjnego ;)
Zadanie:
Tabela z kolumną danych osobowych (imiona i nazwiska w jednej kolumnie) ma zostać rozdzielona na dwie osobne kolumny (jedna kolumna imion i jedna kolumna nazwisk).
- zaznaczam interesujący mnie zakres tabeli i wybieram z menu 'Dane' opcję 'Tekst jako kolumny',
Dzielenie tekstu w excelu
- Podział interesującej nas kolumny uzyskujemy w trzech kolejnych krokach obrazowanych formularzami,
- Krok 1 (wybieram typ pliku 'rozdzielany' - najcześciej dane osobowe przekazywane są ze znakami rozdzielającymi typu spacja, przecinek, średni itp.)
Dzielenie tekstu w excelu
- Krok 2 (wybierany rodzaj ograniczników oddzielających przyszłe kolumny, przy czym można zaznaczyć więcej niż jedną opcję),
Dzielenie tekstu w excelu
- Krok 3 (zaznaczmy format danych w kolumnie i miejsce docelowe - najprościej zaznaczyć kolumnę obok kolumny dzielonej),
Dzielenie tekstu w excelu
- Wynik działania:
Dzielenie tekstu w excelu
Łączenie tekstu z poszczególnych kolumn w jedną jest prostsze. Dostępne są dwa sposoby.
Sposób 1 - Możemy skorzystać ze standardowej funkcji tekstowej Excela np. =ZŁĄCZ.TEKSTY(B3;" ";C3). Działanie to we wskazanej komórce połączy zawartość komórki 'B3', znaku spacji oraz komórki 'C3'.
Łączenie tekstu w excelu
Łączenie tekstu w excelu
Sposób 2 - Możemy skorzystać ze znaku konkatencji. Formuła w komórce docelowej postaci =B3& " "&C3 da dokładnie ten sam efekt jak działanie opisane wyżej.
Wynik działania:
Łączenie tekstu w excelu
Na koniec chciałbym przytoczyć przykład zaawansowanego wykorzystania standardowych formuła Excela zaczerpnięty z książki J. Walkenbach "Excel 2003. Programowanie VBA" str. 81.
Zadanie polega na uzyskaniu uporządkowanego tekstu danych osobowych w sytuacji gdy źródłowa kolumna jest nieco chaotyczna tzn. różna ilość spacji, różne znaki rozdzielające, różna pisownia imion (pełne lub skrótowe) itp. Z sytuacją taką mamy czasem do czynienia przy imporcie danych z pliku tekstowego.
Łączenie tekstu w excelu
Kolejne formuły pośrednie i wynik ich działania:
- B2 Formuła: '=USUŃ.ZBĘDNE.ODSTĘPY(A2)' Wynik: Usuwa zbędne spacje,
- C2 Formuła: '=ZNAJDŹ(" ";B2;1)' Wynik: Lokalizuje pierwszą spację,
- D2 Formuła: '=ZNAJDŹ(" ";B2;C2+1)' Wynik: Lokalizuje drugą spację, w przypadku jej braku zwraca błąd,
- E2 Formuła: '=JEŻELI(CZY.BŁĄD(D2);C2;D2)' Wynik: Jeżeli druga spacja nie istnieje używa pierwszej,
- F2 Formuła: '=LEWY(B2;C2)' Wynik: Zwraca imię,
- G2 Formuła: '=PRAWY(B2;DŁ(B2)-E2)' Wynik: Zwraca nazwisko,
- H2 Formuła: '=F2& " " &G2' Wynik: Łączy imię z nazwiskiem rozdzielajć je spacją,
Powyższy przykład dobrze obrazuje możliwości Excela w zakresie obróbki danych tekstowych.
Wobec powyższego przykładu drobiazgami są już funkcje zmieniające wielkość liter danych tekstowych.
Zmiany wielkości liter w excelu
W poszczególnych kolumnach zastosowano funkcje:
- Kolumna C - funkcja: '=LITERY.MAŁE(B15)'
- Kolumna D - funkcja: '=LITERY.WIELKIE(C15)'
- Kolumna E - funkcja: '=Z.WIELKIEJ.LITERY(C15)'
Wynik działań na załączonym zrzucie ekranu.

TEKSTY NA KOLUMNY W EXCEL 2007 - UWAGI
Na wstążce szybkiego dostępu formuły tekstowe dostępne są w zakładce 'Formuły' i dalej polecenie więcej formuł.
Formuły tekstowe w Excel 2007
Polecenie 'Tekst jako kolumny' dostępne jest w osobnym przycisku zakładki 'Dane'
Tekst jako kolumny w Excel 2007
Pozostałe fukcje z wyżej opisanych dostępne są na takich samych formularzach.

Bardzo przydatną właściwością Excela jest możliwość obróbki danych tekstowych. Punkt przedstawia metodę dzielenia tekstu na kolumny na przykładzie danych osobowych, dwie metody łączenia tekstu oraz jako dodatek zaczerpnięty z literatury przedmiotu przykład obrazujący możliwości arkusza w zakresie porządkowania tekstowej zawartości komórek. W mojej pracy zawodowej z potrzebą dzielenia tekstu na kolumny lub porządkowania danych tekstowych spotykam się najczęściej w przypadku danych o imionach i nazwiskach - podane w jednej kolumnie wymagają podziału na kolumnę imion i drugą kolumnę nazwisk lub przy imporcie danych tekstowych np. dotyczących adresów i numerów zamontowanych wodomierzy itp. w punkcie przedstawiłem również kilka funkcji Excela dotyczących zmiany wielkości liter zawartości tekstowej komórek.

Spis porad Do góry podstrony Do strony głównej

Przygotowanie wydruku KLIKNIJ by (roz)winąć

Na początek mała dygresja. Najczęściej nasza praca znana jest osobom postronnym tylko z wydruków i na ich podstawie będzie oceniana. W sytuacji gdy często dokonujemy wydruków z jednego pliku z niewielkimi zmianami, warto wprowadzić datę wydruku, ilość stron itp. Każdy kto choć raz musiał przebijać się przez swoją własną pracę zastanawiając się, który wydruk był ostatni (mam tu głównie na myśli wydruki obliczeń z mojej własnej pracy dyplomowej ;) doceni możliwości Excela zakresie przygotowania wydruku - a trochę tego jest.
Krok 1 - Zaznaczam interesujący mnie obszar i w menu 'Plik/Obszar wydruku' korzystam z opcji 'Ustaw obszar wydruku'
Parametry wydruku z Excela
- Krok 2 - w wymienionym wyżej menu wybieram opcję 'Plik/Ustawienia strony'. Składa się ona z czterech zakładek, które chciałbym po kolei omówić.
- Zakładka 1 - Ustawienia strony/Strona - w zakładce tej mogę ustaić orientację strony, powiększenie zakresu drukowanego, rozmiar papieru i ewentualnie jakość wydruku oraz numerację stron,
Parametry wydruku z Excela
- Zakładka 2 - Ustawienie strony/Marginesy - możemy dobrać wielkość marginesów oraz (z tej opcji korzystam najczęściej) ustawić wyśrodkowanie w poziomie lub/i pionie (zakres wszystkich stron będzie centrowany zgodnie z zaznaczeniem),
Parametry wydruku z Excela
- Zakładka 3 - Ustawienia strony/Nagłówek i stopka - na pierwszym zrzucie ekranu rozwinąłem standardowe opcje dostępne dla nagłówka i stopki, ale osobiście zawsze korzystam z przycisków 'Stopka niestandardowa' i 'Nagłówek niestandardowy', które przedstawiam na dalszych zrzutach ekranu,
Parametry wydruku z Excela
- Stopka niestandardowa - formularz dzieli stopkę na trzy części: lewą, środkową i prawą. Dla każdej z nich w przyciskach dostępne są możliwości wstawienia nazwy pliku, arkusza, ścieżki dostępu, numeru kolejnej strony, numerów wszystkich stron, daty i czasu wydruku, doboru parametrów czcionki, wstawienia obrazu graficznego i jego formatowania. Budowa formularza dla Nagłówka niestandardowego jest analogiczna.
Ustawiona przeze mnie stopka niestandardowa:
Parametry wydruku z Excela
Ustawiony przeze mnie nagłówek niestandardowy:
Parametry wydruku z Excela
- Zakładka 4 - Ustawnienia strony/Arkusz - z ciekawszych możliwości tej zakładki mozna wymienić możliwość ustawienia opcji 'U góry powtarzaj wiersze' oraz 'Z lewej powtarzaj kolumny' - bardzo przydatne gdy jedna tabela drukowana jest na większej ilości stron. Opcja ta jest niedostępna przy wejściu z poziomu podglądu wydruku.
Drugą ciekawą możliwością omawianej zakładki jest możliwość zastąpienia na wydruku komunikatów o błędach w komórkach wpisami: "--", "n/d" lub pustą komórką. Nie pamiętając o tej możliwości w jednym z progamów udłubałem makro zastępujące wpisy o błędach zdefiniowanym napisem 'nie dotyczy' - kolejny powód dla którego przed pisaniem makr warto przyjrzeć się możliwościom 'gołego' arkusza kalkulacyjnego.
Parametry wydruku z Excela
Wynik działania:
Parametry wydruku z Excela
Na koniec można jeszcze wspomnieć o dostęnej z menu 'Widok/Podgląd podziału stron' możliwości dopasowuywania zawartości poszczególnych stron - przesuwając widoczne na arkuszu niebieskie linie można lepiej niż w standardzie dobrać zakres drukowany na poszczególnych stronach. Należy jednak pamiętać, że możliwość ta jest powiązana z zakładką 'Ustawienia strony/strona/Skalowanie' - zmiany wpływają na wartość skalowania i na odwrót - zmiany skalowania wpływają na zawartość poszczególnych stron wydruku.
Parametry wydruku z Excela
PRZYGOTOWANIE WYDRUKU W EXCEL 2007 - UWAGI
Niektóre opcje wydruku (najczęściej używane) dostępne są w postaci dodatkowych przycisków w zakładce 'Układ strony'.
Parametry wydruku z Excela 2007
Dział zakładki 'Ustawienia wydruków' jest rozwijany ikoną w swoim lewym, dolnym narożniku i stamtąd mamy dostęp do pozostałych funkcji na formularzach identycznych z wersją 2003.
Przygotowanie wydruku z Excela 2007
do tych samych formularzy mamy dostęp po rozwinięciu działu 'Opcje arkusza' zakładki 'Układ strony' (ikona w lewym, dolnym narożniku).
Opcje wydruku w Excel 2007

W punkcie przedstawiono możliwości przygotowania wydruku w programie Excel między innymi: wprowadzenie na wydruk ilości i numeracji stron, nazwy i ścieżki dostępu pliku, ewentualnego loga firmy, powtarzania na poszczególnych stronach odpowiednich wierszy i kolumn itp. Opcje te dostępne są również z poziomu VBA i mogą być wykorzystane przy pisaniu samodzielnych aplikacji.

Spis porad Do góry podstrony Do strony głównej

Praca grupowa na arkuszach KLIKNIJ by (roz)winąć

Pracę grupową na arkuszach stosuję wykonując zestawienia miesięczne. Generalnie opcja ta jest przydatna do wszystkich obliczeń, w których w wielu arkuszach mamy powtarzalny układ tabel.
Krok 1 - Zaznaczamy wszystkie intersujące nas arkusz. Możemy to zrobić na dwa sposoby:
- trzymając klawisz 'Ctrl' zaznaczamy interesujące nas arkusze (nie muszą to być arkusze kolejne),
- zaznaczmy pierwszy interesujący nas arkusz i trzymając przycik 'Shift' zaznaczamy ostatni arkusz - wszystkie pomiędzy zaznaczone zostaną na biało - praca grupowa stała się faktem.
Od tej pory wszystkie operacje dotyczące wprowadzania danych, formuł, formatowania wydruku itd. będą wykonywane na wszystkich zaznaczonych arkuszach jednocześnie. Dla mnie bardzo przydatna jest funkcja ustawiania wydruku (nagłówki i stopki niestandardowe) i realizacji druku jednocześnie z wielu arkuszy.
Zaznaczone arkusze: 'Styczeń_09', 'Luty_09', 'Kwiecień_09' i 'Maj_09'
Praca i wydruk z wielu arkuszy Excela jednocześnie
Pracę grupową kończymy używając na karcie arkusza prawego przycisku myszy i zaznaczając opcję 'Rozgrupowanie arkuszy'
Praca grupowa w Excelu
Kilka przydatnych wskazówek:
- przy pracy z dużą ilością arkuszy warto skorzystać z opcji kolorowych kart. W przypadku prac na danych miesięcznych można dobrać różne kolory dla kwartałów, a przy większej ilości lat - różne kolory dla poszczególnych roczników. Kolor dobieramy przez użycie na karcie arkusza prawego przycisku myszy i wybraniu opcji 'Kolor karty'
Grupowanie arkuszy w Excelu
- przy pracy z dużą ilością arkuszy, jeżeli nie potrzebujemy dostępu to niektóre z nich można ukryć korzystając z menu 'Format/Arkusz/Ukryj'
- przy pracy na kilku plikach jednocześnie można w razie potrzeby poprzenosić całe arkusze po prostu przeciągając w odpowiednie miejsce docelowe (nie można przenieść jedynie ostatniego arkusza danego pliku). Przenoszenie arkuszy pomiędzy plikami Excela
PRACA GRUPOWA W EXCEL 2007 - UWAGI
Arkusze grupowane są autoamtycznie po ich zaznaczeniu z przytrzymaniem klawisza 'Ctrl' - nie trzeba używać osobnego przycisku grupowania. Rozgrupowanie dostępne jest po użyciu prawego przycisku myszy na zakładce arkusza. Zmiany koloru kart itp. analogicznie.
Praca grupowa w Excel 2007

W punkcie przedstawiono możliwości jednoczesnej pracy na wielu arkuszach Excela. Praca grupowa pozwala zautomatyzować wprowadzanie danych lub/i formuł do wielu podobnych tabel jednocześnie oraz przygotować niektóre parametry druku dla wielu arkuszy Excela jednocześnie z możliwością realizacji wydruku.

Spis porad Do góry podstrony Do strony głównej

Udostępnianie skoroszytu, arkusza lub części komórek KLIKNIJ by (roz)winąć

Bardzo często w pracy danego zespołu (nie tylko firmy, ale również wspólnie realizowanych projektów studenckich) zachodzi potrzeba udostępnienia danego zakresu komórek pliku Excela większej ilości osób. Dla zachowania porządku w realizowanej pracy warto by osoba koordynująca działania udostępniała do zmian tylko ściśle określone zakresy komórek, umożliwiając jednoczesny podgląd całości. Tego typu właściwości pliku Excel realizuje za pomocą opcji pracy grupowej i udostępniania zakresów komórek.

UDOSTĘPNIANIE SKOROSZYTU - JEDNOCZESNA PRACA WIĘKSZEJ ILOŚCI OSÓB NA JEDNYM PLIKU EXCELA
Opcja ta powoduje, że na jednym pliku Excela może pracować większa ilość osób i jest przydatny np. podczas pracy w sieci.
- Z paska menu 'Narzędzia' wybieram polecenie 'Udostępnij skoroszyt',
Jednoczesna praca wielu osób na jednym pliku Excela
- Na widocznym formularzu zaznaczam opcję 'Pozwalaj na zmiany wprowadzane jednocześnie przez wielu użytkowników'
Udostępnianie skoroszytu w excelu
- W drugiej zakładce mogę zaznaczyć opcje o długości historii zmian, wprowadzania konfliktowych zmian między użytkownikami i sposobu aktualizacji,
Wielu użytkowników w jednym pliku excela
- Od tej pory wieleosób może pracować na jednym pliku, przy czym wszyscy użytkownicy będą widoczni w zakładce 'Udostępnij skoroszyt'. Próba usunięcia jednego z użytkowników skutkuje odpowiednim komunikatem o błędzie
Praca wielu osób na jednym pliku excel
WADY ROZWIĄZANIA:
- Udostępniony zostaje cały skoroszyt (w uproszczeniu plik Excela) tak więc wystarczy błąd jednego użytkownika by uszkodzić pracę wszystkich zainteresowanych,
- Praca kliku osób na jednym pliku Excela przy różnych jego wersjach (2003 i 2007) może skutkować błędami. Osobiście spotkałem się z sytuacją, gdy plik udostępniony w Excelu 2003 i 2007 ważący ok. 5MB w ciągu kilku dni potrafi zwiększyć masę do skromnych 500MB. Na forum internetowym spotkałem się z informacją, że winę za błąd ponosi Excel 2007 i radą bym zawsze zachował plik źródłowy nietknięty przez wersję 2007, by móc odtworzyć najważniejsze dane. Problem ten szerzej omówiłem na podstronach dotyczących programowania VBA,
- Excel potrafi źle rozpoznać ilość użytkowników korzystających z jednego pliku np. spotkałem się z sytuacją, że na pliku pracowało pięć osób, a program rozpoznawał ich 17 przy czym jednego z użytkowników czterokrotnie sklonował ;)
- W trybie pracy 'Skoroszyt udostępniony' brak możliwości edytowania makr - edytor VBA odpada,
OBSERWACJA OGÓLNA:
Podczas pracy w sieci Excela poszczególnych stanowisk otwierając plik udostępniony tworzą kopię na własnym stanowisku. Aktualizacja i zapisanie zmian z plikiem źródłowym następuje w chwili zapisu pliku.

UDOSTĘPNIANIE ZAKRESU KOMÓREK ARKUSZA EXCEL - JEDNOCZESNA PRACA WIĘKSZEJ ILOŚCI OSÓB NA JEDNYM PLIKU EXCELA
Części powyższych wad pozbawiona jest funkcja udostępniania tylko wybranych zakresów komórek. W mojej pracy może być wykorzystana przy zbieraniu informacji z kilku różnych jednostek administracyjnych w jedną tabelkę. Zbierający sprawozdania przygotowuje plik z tabelą i danymi wyjściwymi, które zabezpiecza przed zmianami. Następnie nakazuje poszczególnym jednostkom uzupełnić dane w wyznaczonych (np. kolorem) rubrykach. Z opcji tej można skorzystać w następujący sposób:
- Zaznaczamy chroniony zakres komórek i prawym przyciskiem myszy wybieramy opcje 'Formatuj komórki'
Zabezpieczenie komórek excela hasłem
- Na formularzu wybieram zakładkę 'Ochrona' i opcję 'Zablokuj'
Ochrona zakresu arkusza w excelu
- Z paska menu 'Narzędzia' wybieram opcję 'Chroń skoroszyt' - uwaga: wybranie tej opcji bez wskazania zakresu komórek spowoduje, że cały arkusz będzie chroniony
Ochrona pliku Excela hasłem
- Na widocznym formularzu zaznaczam wybrane opcje (tzn. na co pozwalam w chronionych komórkach) i dwukrotnie potwierdzam hasło,
Ochrona komórek w Excelu
- Obecnie przy próbie zapisania do zabezpieczonego zakresu komórek wyświetlony zostanie odpowiedni komunikat:
Hasła w Excelu
UWAGA: Zabezpieczenie hasłem zakresu komórek działa w tam arkuszu, na którym wykonano powyższe działania.

UDOSTĘPNIANIE SKOROSZYTU, ARKUSZA LUB JEGO CZĘŚCI W EXCEL 2007 - UWAGI
Opcje dostępne są w postaci opisanych przycisków zakładki 'Recenzja' na wstążce szybkiego dostępu. Generalnie używanie jest prostsze niż tych samych funkcji w Excel 2003. Zabezpiecza się cały arkusz, później za pomocą uruchamianego przyciskiem, osobnego formularza udostępniając zakresy do edycji.
Udostępnianie i ochrona arkusza w Excel 2007
Edycja zakresów arkusza w Excel 2007
Edycja zakresów dla użytkowników Excel 2007

W punkcie przedstawiono sposób przygotowania pliku Excela do pracy większej ilości osób, metodę udostępniania skoroszyu do pracy wielostanowiskowej oraz mentody udostępniania wybranego zakresu komórek do pracy kilku osób. Właściwości te bardzo często wykorzystywane są w pracy zespołowej (nie tylko firmy, ale również wspólnie realizowanych projektów studenckich).

Spis porad Do góry podstrony Do strony głównej

Wklej specjalnie i zamiana wierszy na kolumny w Excelu KLIKNIJ by (roz)winąć

Polecenia kopiuj/wklej wraz ze skrótami klawiaturowymi Ctrl+C i Ctrl+V są chyba najszerzej znanymi skrótami klawiatury komputera. Wiele prac dyplomowych nie powstałoby, gdyby nie te narzędzia ;) Zdecydowanie rzadziej używane jest w E xcelu polecenie 'Wklej specjalnie' wraz z pochodnymi funkcjami np. transpozycją (zamianą wierszy na kolumny i odwrotnie).
- Po skopiowaniu fragmentu tabeli prawym przyciskiem myszy uruchamiamy menu kontekstowe, gdzie jedną z funkcji jest 'Wklej specjalnie',
Wklejanie specjalne w excelu
- Polecenie 'Wklej specjalnie' uruchomi formularz, gdzie niektóre z funkcji to:
   - Formuły - wkleja tylko formułę nie zmieniając formatowania komórki,
   - Wartości - wkleja tylko wartości, jeżeli w komórce była formuła, wklei tym samym tylko wynik jej działania,
   - Wklej łącze - wkleja link do komórki, przydatne przy pracy na wielu plikach,
   - Podziel/przemnóż - przydatne do zmiany formatu liczb np. wyniki w jednostkach zamieniamy na wyniki w tysiącach,
   - Formaty - kopiuje formatowanie komórek (analogicznie do malarza formatów),
   - Transpozycja - wybranie tej opcji spowoduje wzajemną zamianę kolumn na wiersze w Excelu,
Zmian wierszy na kolumny w Excelu
- Po wybraniu zwyklego wklejenia, pojawia się tzw. inteligentny tag z opcjami wklejania. Funkcje tej ikonki po rozwinięciu to:
   - Zachowaj formatowanie źródłowe,
   - Dopasuj formatowanie docelowe,
   - Tylko wartości,
   - Formatowanie wartości i liczb,
   - Formatowanie wartości i źródła,
   - Zachowaj szerokości kolumn źródłowych,
   - Tylko formatowanie,
   - Połącz komórki,
Opcje wklejania w Excelu
Najczęściej używane przeze mnie funkcje to 'Wklej tylko wartości' oraz 'Zachowaj szerokości kolumn źródłowych'

W punkcie przedstawiono możliwości wklejania skopiowanegio zakresu komórek w Excelu. Jednymi z najczęściej używanych możliwości jest wklejanie tylko wartości oraz wklejanie z zachowaniem formatowania. Istotną właściwością arkusza kalkulacyjnego w tym temacie jest również możliwość wklejania z transpozycją tzn. automatyczną zmianą wierszy na kolumny i na odwrót. Ręczne wykonanie tego zadania byłoby długie i uciążliwe.

Spis porad Do góry podstrony Do strony głównej

Sumy pośrednie KLIKNIJ by (roz)winąć

Sumy pośrednie w ExceluNa początku punktu pokażę realizację zadania zasygnalizowanego w streszczeniu poniżej. W tabeli prezentowanej na ilustracji zamiast zwykłych sum w kolumnie D zastosowałem sumy pośrednie zakresów. W kolumnie E ukazane są zastosowane formuły. Najważaniejszym na co należy zwrócić uwagę to fakt, że suma pośrednia zastosowana w komórce D21 uwzględnia wcześniej zastosowane w zakresie tabeli sumy pośrednie - szybciej i prościej da ten sam efekt co sumowanie poszczególnych komórek pokazane w D22. W sytuacji gdy między pozycjami z rur stalowych i miedzianych chciałbym dać zakres z rurami z polipropylenu jest oczywisty.
Składnia funkcji wygląda następująco:
SUMA.POŚREDNIA(symbol_funkcji,zakres) ,
gdzie:
  - zakres to obszar, dla którego stosujemy funkcję Sumy.Pośredniej, może (tak jak w przykładzie), obejmować komórki gdzie zastosowano tę samą funkcję,
  - symbol funkcji to liczba określająca funkcję jaką stosujemy na zakresie oraz (nie)uwzględnianie w obliczeniach komórek ukrytych,
ZESTAWIENIE FUNKCJI DLA SUM POŚREDNICH (kolejno nazwa funkcji, numer dla uwzględniania wartości ukrytych, numer dla nieuwzględniania wartości ukrytych):
  ŚREDNIA                         1     101
  ILE.LICZB                        2     102
  ILE.NIEPUSTYCH               3     103
  MAKSIMUM                      4     104
  MINIMUM                        5     105
  ILOCZYN                         6     106
  ODCH.STANDARDOWE        7     107
  ODCH.STANDARD.POPUL     8     108
  SUMA                             9     109
  WARIANCJA                    10     110
  WARIANCJA.POPUL           11     111

Najczęściej używanymi przez mnie funkcjami były suma, ile.liczb oraz ile.niepustych. Warto jeszcze pamiętać, że bardzo ważnym zastosowaniem funkcji Suma.Pośrednia jest zliczanie wartości w obszarze na którym zastosowano autofiltr - wyrażenie postaci Suma.Pośrednia(9, zakres) podliczy sumę wartości tylko dla komórek widocznych po przefiltrowaniu.

Sumy pośrednie w ExceluUłatwienie pracy jakie oferuje standardowa funkcja Excela 'Sumy pośrednie' będąca tematem tego punktu chciałbym omówić na przykładzie. Typ tabeli prezentowany na ilustracji obok jest często stosowany dla różnego typu zestawień. W tym wypadku dla różnego materiału i średnicy rur w kolumnie D podawana i podliczana jest ich długość. W komórce D11 sumowana jest długość rur stalowych, w komórce D20 długość rur miedzianych, a w komórce D21 i D22 łączna długość rur z obydwu materiałów. W kolumnie E prezentowane są zastosowane formuły. Widać, że jeżeli w zliczeniu łącznej długości rur z obu materiałów wykorzystamy funkcję Suma(D4:D20) wynik wyjdzie błędny ponieważ oprócz długości poszczególnych odcinków zliczone zostaną również sumy w komórkach D11 i D20. Większość osób zdając sobie z tego sprawę wstawi formułę jak w komórce D22 ręcznie dodając wybrane wartości. Jest to do przyjęcia dla krótkich tabel. Dla długich zestawień np. magazynowych, sprzedaży czy zużytego materiału takie postępowanie zwiększa pracochłonność, zwiększa ryzyko błędu oraz utrudnia modyfikację tabeli. Najlepszym wyjściem jest zastosowanie funkcji Sumy pośredniej, które przy zaznaczaniu zakresów zsumuje komórki zakresu bez dublowania.
Innym zastosowaniem funkcji 'Sumy pośredniej' jest:
- sumowanie przefiltrowanych zakresów,
- sumowanie zakresów dla których część komórek została ukryta,
- realizacja na zadanym zakresie innej funkcji niż suma (pozostałe możliwości to: ŚREDNIA, ILE.LICZB, ILE.NIEPUSTYCH, MAKSIMUM, MINIMUM, ILOCZYN, ODCH.STANDARDOWE, ODCH.STANDARD.POPUL, WARIANCJA, WARIANCJA.POPUL,)

Spis porad Do góry podstrony Do strony głównej

Funkcje: Jeżeli, Lub i Oraz KLIKNIJ by (roz)winąć

Funkcja "Jeżeli" sprawdza podany warunek logiczny i w zależności od wyniku warunku wstawia odpowiednią wartość/formułę. Składnia funkcji jest stosunkowo prosta:
JEŻELI(warunek logiczny, wartość jeśli prawda, wartość jeśli fałsz)
Jako warunek logiczny można zastosować wartość liczbową. W przykładzie poniżej dla wartości mianownika "0" funkcja zapobiega wstawieniu komunikatu o błędzie, a wstawi ciąg tekstowy "----".
Funkcja "Jeżeli" ma postać: "JEŻELI(C11=0;"----";B11/C11)"
Funkcja Jeżeli - obsługa błędu
Jako warunek logiczny można zastosować również funkcję tekstową. W przykładzie funkcja tekstowa pobiera jeden znak z prawej strony ciągu tekstowego zapisanego w kolumnie "Imię". Jeżeli wartość znaku wynosi "a" wynikiem formuły jest "Kobieta", w przeciwnym wypadku wynikiem będzie wyraz "Mężczyzna" (w języku polskim imiona żeńskie kończą się literą 'a').
Funkcja "Jeżeli" ma postać: "JEŻELI(PRAWY(G3;1)="a";"Kobieta";"Mężczyzna")"
Funkcja Jeżeli z funkcją tekstową
Jako warunek logiczny można zastosować funkcję informacyjną. W przykładzie formuła sprawdza czy w wynikiem formuły we wskazanej komórce jest symbol błędu. W takim przypadku komunikat ten zostanie zastąpiony tekstem "----" (jeżeli błędu nie ma wykonana zostanie normalna formuła).
Funkcja "Jeżeli' ma postać "JEŻELI(CZY.BŁĄD(D3);"----";B3/C3)"
Uwaga: w podobny sposób można zastosować formułę "CZY.BŁĄD" dla formatowania warunkowego dobierając ten sam kolor tła i tekstu.
Funkcja Jeżeli z funkcją czy.błąd
Funkcję Jeżeli można również wewnętrznie zapętlić (do siedmiu razy) - prosty przykład zaprezentowałem na podstronie 'Zagadnienia VBA'w punkcie: Wielkanoc w Excelu.

Funkcje "LUB" i "ORAZ" są funkcjami logicznymi. Wynikiem ich działania jest wartość "PRAWDA" lub "FAŁSZ".
Funkcja LUB w Excelu
Funkcja "LUB" sprawdza podane warunki logiczne i jeżeli przynajmniej jeden z nich jest spełniony wynikiem działania funkcji jest "Prawda". Na przykładzie z ilustracji formuła sprawdza kwoty miesięczne w nieruchomościach porównując je z wartością 4500. Jeżeli choć jeden miesiąc będzie mniejszy niż 4500 wynikiem formuły jest "Prawda".
Funkcja ORAZ w Excelu
Funkcja "ORAZ" sprawdza podane warunki logiczne i jeżeli wszystkie są spełnione wynikiem działania funkcji jest "Prawda". Na przykładzie z ilustracji formuła sprawdza kwoty miesięczne w nieruchomościach porównując je z wartością 4500. Jeżeli wszystkie będą większe niż 4500 wynikiem formuły jest "Prawda".

Formuły logiczne są wartościowe, ale w analizie tabel i wydrukach często zależy nam na bardziej wymownym wyrażeniu niż "Prawda" i "Fałsz". Poniżej dla dwóch tabel przytoczonych wcześniej pokazuję zastosowanie funkcji "Jeżeli", gdzie jako warunki stosuję funkcje logiczne "Lub" i "Oraz". Analizowana jest ta sama tabela, a efekty działania formuł są jednakowe.
Funkcja JEŻELI z funkcją LUB w Excelu

Funkcja JEŻELI z funkcją ORAZ w Excelu

W pierwszej tabeli funkcja "Jeżeli" zastosowana jest razem z funkcją "Lub".
Formuła ma postać: "JEŻELI(LUB(K21<4500;L21<4500;M21<4500);"Nie wykonany";"Wykonany")"
W drugiej tabeli zastosowana jest funkcja "Jeżeli" razem z funkcją "Oraz". Formuła ma postać:
"JEŻELI(ORAZ(K30>4500;L30>4500;M30>4500);"Wykonany";"Nie wykonany")"

Funkcja Jeżeli + LubFunkcja "Jeżeli" jest funkcją bardzo prostą w zastosowaniu, która przy umiejętnym wykorzystaniu oferuje duże możliwości. Możliwe jest jej wewnętrzne zapętlenie (do siedmiu razy) oraz stosowanie w kombinacji np. z funkcjami tekstowymi lub logicznymi.
Przykładowe zastosowanie pokazane jest na ilustracji obok. Przykład dotyczy fikcyjnego wykonania planu dla pierwszego kwartału siedmiu nieruchomości. Jeżeli jedna z wartości miesięcznych w nieruchomości jest mniejsza od 4500 plan nie jest wykonany. Generowane automatycznie przez arkusz kalkulacyjny wartości tekstowe "Wykonany" i "Nie wykonany" są wynikiem działania funkcji "Jeżeli" w kombinacji z funkcją "Lub". Pozostałe przykłady w tekście rozdziału.

Spis porad Do góry podstrony Do strony głównej

Funkcja tekstowa Podstaw KLIKNIJ by (roz)winąć

Drobna rzecz, a cieszy: w zbiorze funkcji tekstowych Excela 2007 jest dostępna funkcja "Podstaw". Pozwala ona dla wybranego tekstu (może być podany jako odwołanie do komórki) zmienić dowolną frazę tekstu na inną zdefiniowaną przez użytkownika. Zastosowanie tej funkcji dla jednej komórki i skopiowanie formuły dla pozostałych komórek arkusza pozwala szybko i łatwo zmienić część frazy w kolumnie. Jako przykład zastosowałem w kolumnie B kilka Komorek zawierających jedną wspólną frazę "drzewo". W kolumnie C widoczna jest formuła, która pozwala tą frazę zmienić na "koło".
Funkcja Podstaw
Excel 2007 zastępowanie części frazy w kolumnie

W punkcie podano prosty sposób na zmianę frazy w kolumnie dla Excela 2007. Przydatne dla osób pracujących na co dzień z dużymi arkuszami danych tekstowych np. dane osobowe. Zmiana części frazy dowolnego ciągu tekstowego jest możliwa jedną standardową i prostą w budowie formułą.

Spis porad Do góry podstrony Do strony głównej

Sprawdzanie poprawności danych /prosta lista rozwijana/ KLIKNIJ by (roz)winąć

Kilka przykładów opisanego w streszczeniu narzędzia:
Po pierwsze zaznaczmy obszar komórek dla których chcemy użyć sprawdzania poprawności:
Sprawdzenie poprawności danych w excelu
Z menu 'Dane' wybieramy 'Sprawdzanie poprawności':
Menu Dane - sprawdzanie poprawności
Teraz korzystamy z okna, które chciałbym nieco szerzej omowić:
Okno Sprawdzania poprawności Excel
Okno składa się z trzech zakładek:
- Zakładka 1 'Ustawienia'
  wybieramy rodzaj ustawień:
    - dowolna wartość (ustawienie domyślne),
    - Pełna liczba,
    - Dziesiętne,
    - Lista,
    - Data,
    - Godzina,
    - Dłg tekstu,
    - Niestandardowe,
Jeżeli wybierzemy wartość wyrażaną liczbą (pełna liczba, dziesiętne, data itp.) uruchomiona zostanie lista, gdzie możemy wybrać zakres wartości np. większe niż, większe lub równe, między wartościami itp.
- Zakładka 2 'Komunikat wejściowy'
  opcjonalnie możemy ustalić jaki napis będzie pojawiał się przy aktywowaniu kontrolowanej komórki,
- Zakładka 3 'Alert o błędzie'
  możemy ustawić jaki komunikat pojawi się przy próbie wprowadzenia wartości spoza zdefiniowanego zakresu. Oprócz treści komunikatu możemy ustawić jego 'Styl', czyli sposób reakcji Excela na próbę wpisania wartości niedozwolonej. Dostępne są trzy style: 'Zatrzymaj', 'Informacja', 'Ostrzeżenie'. Styl 'Zatrzymaj' nie dopuści do wstawienia wartości spoza zakresu. Pozostałe dwa style tylko ostrzegają, ale jak użytkownik się uprze pozwolą wpisać inną wartość.

W obecnym przykładzie w zakładce 'Ustawienia' wybrana została 'Lista'. Zaznaczamy obszar wartości tekstowych (komórki od F5 do F8 z nazwami materiału rur):
Prosta lista rozwijana w excelu
Korzystając z zakładki 'Komunikat wejściowy' możemy ustawić tekst wyświetlany po aktywowaniu jednej z komórek kontrolowanego zakresu (uwaga - w praktyce, przynajmniej moim zdaniem, opcja irytująca - ile razy można czytać jeden i ten sam komunikat?):
Komunikat informacyjny sprawdzania danych Excel
Korzystając z zakładki 'Alert o błędzie' wprowadzam styl 'Zatrzymaj' i odpowiedni komunikat:
Komunikat o błędzie sprawdzania danych Excel
Efekt dotychczasowych działań przy próbie wprowadzenia wartości spoza zakresu:
Efekt sprawdzania poprawności danych Excel

Inne przykładowe możliwości sprawdzania danych to:
WPROWADZANIE LICZB CAŁKOWITYCH DODATNICH MNIEJSZYCH NIŻ ...
W oknie wprowadzamy opcje 'Pełna liczba' i np. wartości między 0 a 200
Kontrola wprowadzania danych Excel - wartości dodatnie
Kontrola wprowadzania danych Excel - wartości całkowite

WPROWADZANIE OKREŚLONEJ ILOŚCI ZNAKÓW
W oknie wprowadzamy opcje 'Długość tekstu' i z listy wartości danych 'Równe', a w oknie tekstowym np. 11 (długość znaków w numerze PESEL). Excel zgłosi błąd, przy próbie wpisania ciągu znaków innej długości (nie eliminuje to całkowicie błędów, ale zawsze coś).

WPISYWANIE DAT Z OKREŚLONEGO PRZEDZIAŁU
W oknie wprowadzamy opcje 'Data' i z listy wartości danych 'Między', a w oknach tekstowych dwie daty np. 2007-01-01 i 2007-12-31. Excel zgłosi błąd, przy próbie wpisania daty spoza zakresu.

Jeżeli przy dacia wybierzemy opcję 'Równe' i w oknie tekstowym wprowadzimy formułę '=DZIŚ()' Excel będzie przyjmował tylko bieżącą datę (to akurat łatwiej uzyskać zwykłą formułą).

UWAGI DO EXCELA 2007
W zasadzie uwag brak - obsługa sprawdzania poprawności danych odbywa się na identycznym formularzu. Na wstążce szybkiego dostępu znaleźć ją można w zakładce 'Dane' i dalej 'Poprawność danych' - jak na ilustracji:
Kontrola wprowadzania danych Excel 2007

Prosta lista rozwijana w ExceluSprawdzenie poprawności wprowadzanych do określonych obszarów arkusza danych jest bardzo przydatne w pracy grupowej lub w sytuacji gdy arkusz dajemy do uzupełnienia współpracownikom. Bywa również stosowana w szablonach ćwićzeń projektowych z przedmiotów technicznych. Wykorzystanie tej funkcji Excela jest również najprostszą metodą wprowadzenia do arkusza listy rozwijanej (trochę imitacji, ale jednak funkcjonalnej). Dzięki temu narzędziu możemy określić, że w zadanych komórkach można wprowadzić np. jedynie wartości dodatnie, wyrazy tylko z wybranego zakresu, daty z określonego przedziału itp. Przy próbie wprowadzenia innych wartości program może całkowicie zablokować zmianę danych, wyświetlić komunikat wg wcześniej zdefiniowanej treści, wyświetlić tylko ostrzeżenie itp.
Funkcja ta jest w zasadzie niezbędna w sytuacji, gdy w arkuszu używane będą inne funkcje odnoszące się do wartości ciągów tekstowych (np. WYSZUKAJ.PIONOWO). Niewielka zmiana nazwy w danej komórce może spowodować, że działanie tego typu funkcji tekstowych będzie niezgodne z założeniami użytkownika. Sprawdzenie poprawności danych pozwala uniknąć takich lapsusów. Na ilustracji pokazałem reakcje programu na próbę wprowadzenia nazwy materiału instalacji spoza wcześniej zdefiniowanego zakresu.
Namiastka listy rozwijanej uzyskanej dzięki opisywanej funkcji nie zastąpi tradycyjnej kontrolki ComboBox. Formant ten opisywany jest między innymi na podstronie 'Zagadnienia VBA' Formant ComboBox excela.
Należy również pamiętać, że przydatne w wielu wypadkach wyświetlanie komuniatu z objaśnieniem podczas uruchomienia danego pliku Excela jest bardzo proste do uzyskania w VBA - sprawdzenie poprawności danych czystego Excela jest w tym wypadku nieprzydatne.

Spis porad Do góry podstrony Do strony głównej

Praktyczne zastosowanie listy rozwijanej w Excelu KLIKNIJ by (roz)winąć

W komórkach C5, C6, D7 wprowadzona została formuła wyszukująca w arkuszu 2 (zawarta w nim jest tabela z danymi firmy tj. nazwa, adres, numer nip) pozycję o numerze wskazanym w komórce H4. W przypadku adresu (złożenie ulicy i miasta) zastosować złożenia obydwu formuł:

=INDEKS(Arkusz2!B1:B300;H4)&" "&INDEKS(Arkusz2!C1:C300;H4)

W komórce H4 wprowadzono formułę wyszukującą pozycję w tabeli z arkusza 2, którą zajmuje nazwa firmy wskazana w komórce C4

=PODAJ.POZYCJĘ(C4;Arkusz2!A:A)

W komórce C4 wstawiana jest z listy rozwijanej nazwa firmy. W komórkach C4 i C14 wstawiane są nazwy firmy - w jednej z listy rozwijanej, której źródłem jest zakres komórek z tego samego arkusza, a w drugim z listy rozwijanej podanej jako zdefiniowana nazwa.

1)Wybieramy zakładkę Dane i później sprawdzaj poprawność danych
Praktyczne zastosowanie listy rozwijanej w Excelu
2)W formularzu zaznaczamy polecenie lista i wskazujemy zakres komórek - uwaga przy tym sposobie zaznaczenia zakres komórek musi być z tego samego arkusza
Wybór danych z listy rozwijanej w Excelu
Inny sposób został zastosowany dla komórki C14:
1)Z zakładki formuły wybieramy definiuj nazwę i w arkuszu 2 wskazujemy zakres komórek nazywając go np. LISTA_FIRM
Definiowanie nazwy w Excelu
2)Teraz dla komórki C14 wprowadzamy sprawdzanie poprawności jak poprzednio, ale zamiast adresów komórek wstawiamy znak = i nazwę listy
Praktyczne zastosowanie listy rozwijanej w Excelu
W efekcie uzyskujemy listę, która po wybraniu np. nazwy firmy lub nazwiska pracownika wstawi w określonych komórkach właściwe dane. Całość tego ciekawego efektu bez programowania.

W punkcie przedstawiono proste wyszukiwanie danych z zastosowaniem funkcji Indeks, Podaj_pozycję oraz liste rozwijaną. Przedstawiona metoda nie wymaga programowania, a pozwala szybko wyszukiwać dane zgromadzone w tabeli i wyświetlać w określonych komórkach. Można zastosować np. do wyszukiwania danych o pracownikach, danych o firmach itp. W efekcie uzyskujemy listę, która po wybraniu np. nazwy firmy lub nazwiska pracownika wstawi w określonych komórkach właściwe dane. Całość tego ciekawego efektu bez programowania.Załącznikiem punktu jest plik do ściągnięcia w dziale pliki.

Spis porad Do góry podstrony Do strony głównej

Zastosowanie funkcji LICZ.JEŻELI oraz SUMA.JEŻELI /funkcja tablicowa/ KLIKNIJ by (roz)winąć

FUNKCJA LICZ.JEŻELI
Funkcja Licz.jeżeli jest jadną z bardziej przydatnych funkcji Excela. Składnia jest prosta i w zasadzie nie wymagająca wyjaśniania:
= LICZ.JEŻELI(zakres_danych; kryterium)
Jeżeli jedno kryterium naliczania nie wystarcza można stosować kombinację kilku funkcji.
Przykładowe zastosowania funkcji LICZ.JEŻELI zaczerpnąłem z książki J. Walkenbacha 'Excel 2003'.
- Zastosowanie 1: Formuła =LICZ.JEŻELI(zakres_danych; 12)
  Opis: Liczba komórek zawierających wartość 12,
- Zastosowanie 2: Formuła =LICZ.JEŻELI(zakres_danych; 1)+LICZ.JEŻELI(zakres_danych; 12)
  Opis: Liczba komórek zawierających wartość 1 lub wartość 12,
- Zastosowanie 3: Formuła =LICZ.JEŻELI(zakres_danych; "<0")
  Opis: Liczba komórek zawierających wartości mniejsze niż 0,
- Zastosowanie 4: Formuła =LICZ.JEŻELI(zakres_danych; "<>0")
  Opis: Liczba komórek zawierających wartości różne od 0,
- Zastosowanie 5: Formuła =LICZ.JEŻELI(zakres_danych; "=>1")-LICZ.JEŻELI(zakres_danych; "<10")'
  Opis: Liczba komórek zawierających wartości z przedziału od 1 (włącznie) do 10,
- Zastosowanie 6: Formuła =LICZ.JEŻELI(zakres_danych; "tak")
  Opis: Liczba komórek zawierających słowo 'tak' - wielkość znaków nie jest rozróżniana,
- Zastosowanie 7: Formuła =LICZ.JEŻELI(zakres_danych; "8")
  Opis: Liczba komórek zawierających dowolny łańcuch tekstowy,
- Zastosowanie 8: Formuła =LICZ.JEŻELI(zakres_danych; "*s*")
  Opis: Liczba komórek zawierających literę 's' - wielkość znaków nie jest rozróżniana,
- Zastosowanie 9: Formuła =LICZ.JEŻELI(zakres_danych; "???")
  Opis: Liczba komórek zawierających trzyliterowe słowa,

FUNKCJA SUMA.JEŻELI
Funkcja SUMA.JEŻELI w odróżnieniu od funkcji LICZ.JEŻELI oblicza nie ilość komórek w zadanym zakresie, spełniających dane warunki, a ich sumę. Składnia funkcji w porównaniu z funkcją LICZ.JEŻELI jest zwiększona o jeden człon:
- Zakres - komórki z tego zakresu (najlepiej cała kolumna - wtedy najłatwiej kopiować) będą porównywane z kryterium sumowania,
- Kryteria - jakich wartości w powyższym zakresie mają być szukane. Wartość kryterium może być podana jako wartość, formuła lub odniesienie do komórki,
- Suma_zakres - dane z tego zakresu (najlepiej cała kolumna - wtedy najłatwiej kopiować) będą sumowane, jeżeli w tej samej linijce komórek pierwszego zakresu wystąpi wartość spełniająca zadane kryterium.
Uwaga: jeżeli arkusz jest duży to stosowanie całych kolumn będzie wydłużać czas obliczeń.

Przykład 1 - zastosowanie formuły z odniesieniem kryterium do konkretnej wartości
Funkcja Suma.jeżeli excel - krytrium wartości
Przykład 2 - zastosowanie formuły z odniesieniem kryterium do wartości w komórce
Funkcja Suma.jeżeli excel - krytrium adresu komórki
Przykład 3 - zastosowanie dwóch kryteriów - Powyższe przykłady obejmują tylko jedno kryterium. Jednym ze sposobów uzyskania funkcji SUMA.JEŻELI jest zastosowanie łączenia tekstów. Dla zadanego przykładu można w kolumnie A zastosować konkatencję: =B3&C3 i formuła ma wtedy postać jak na ilustracji (suma wartości dla miesiąca styczeń i budynku OR 1-10).
Funkcja Suma.jeżeli excel - dwa warunki

FORMUŁY TABLICOWE
Formuła tablicowa jest specjalną formułą obsługującą tablice tzn. zbiór komórek lub wartości przetwarzanych grupowo. Wynikiem działania formuły tablicowej może być pojedyncza wartość lub zbiór wartości wyświetlanych w oddzielnych komórkach (Excel w jednej komórce może wstawić tylko jedną wartość). Wprowadzanie formuły tablicowej odbywa się dzięki kombinacji klawiszy Ctrl+Shift+Enter. Arkusz kalkulacyjny wyświetla formułę tablicową w nawiasach {}, z tym że NIE MOŻNA samodzielnie wprowadzić tych nawiasów. Zastosowanie formuł tablicowych pozwala zrezygnować z formuł pośrednich (co jest jej podstawową zaletą), czasami (dla dużych plików) wydłuża czas wykonania obliczeń, a poza tym jest trudna do zrozumienia przy analizie arkusza co może powodować kłopoty przy jego modyfikacji (zwłaszcza przez osoby, które nie zetknęły się z nim wcześniej).

Najpierw bardzo prosty przykład obrazujący możliwości funkcji tablicowej:
Jak uzyskać tabliczkę mnożenia jedną formułą?
tabliczka mnożenia jedną formułą w Excelu - przykład funkcji tablicowej krok 1
Do komórek wiersza 1 i kolumny A wprowadzamy cyfry od 1-10,

tabliczka mnożenia jedną formułą - funkcja tablicowa krok 2
Zaznaczamy obszar w którym chcemy uzyskać formułę tablicową i w pasku formuł wprowadzamy wyrażenie: =A2:A11*B1:K1,

przykład funkcji tablicowej dla Excel krok 3 - tabliczka mnożenia jedną formułą
Wprowadzoną formułę zawierdzamy Ctrl+Shift+Enter - efekt na screenie,

Teraz coś poważniejszego:
Zastosowanie formuły tablicowej z kryterium wyrażonym formułą:

Dla podliczenia ilości wartości numerycznych (z pominięciem łańcuchów tekstowych i pustych komórek):
= SUMA(JEŻELI(CZY.LICZBA(zakres;1;0))
Po wprowadzeniu, mając aktywną komórkę wciskamy Ctrl+Shift+Enter (program uzupełni formułę znakami {})

Dla podliczenia liczby komórek z błędem:
=SUMA(JEŻELI(CZY.BŁĄD(zakres;1;0))
Po wprowadzeniu, mając aktywną komórkę wciskamy Ctrl+Shift+Enter (program uzupełni formułę znakami {})

Dla podliczenia ilości unikatowych wartości numerycznych (pomijając tekst, puste komórki są niedozwolone):
=SUMA(JEŻELI(CZĘSTOŚĆ(zakres,zakres)>0;1;0))
Po wprowadzeniu, mając aktywną komórkę wciskamy Ctrl+Shift+Enter (program uzupełni formułę znakami {})

Za książką J. Walkenbacha 'Excel 2003' przytaczam poniżej formuły tablicowe wraz z opisem (dostosowane do mojego przykładu):

Przykład 1:
=SUMA((B3:B14="styczeń")*(C3:C14="OR 1-10")*D3:D14)
Opis: Suma wartości komórek zakresu 'Wydatki', dla których miesiąc = 'styczeń' i budynek = 'OR 1-10',
Funkcja tablicowa excel Suma - dwa warunki
Przykład 2:
=SUMA((B3:B14="styczeń")*(C3:C14<>"OR 1-10")*D3:D14)
Opis: Suma wartości komórek zakresu 'Wydatki', dla których miesiąc = 'styczeń' i budynek <> 'OR 1-10',
Funkcja tablicowa excel Suma - dwa warunki
Przykład 3:
=SUMA((B3:B14="styczeń")*(C3:C14="OR 1-10"))
Opis: Ilość komórek zakresu 'Wydatki', dla których miesiąc = 'styczeń' i budynek = 'OR 1-10',
Funkcja tablicowa Suma.jeżeli - dwa warunki
Przykład 4:
=SUMA((B3:B14="styczeń")*((C3:C14="OR 1-10")+(C3:C14="OR 11")))
Opis: Ilość komórek zakresu 'Wydatki', dla których miesiąc = 'styczeń' i budynek = 'OR 1-10' lub 'OR 11'
Funkcja tablicowa excel Suma.jeżeli - trzy warunki
Przykład 5:
=SUMA((B3:B14="styczeń")*(D3:D14>=2000)*(D3:D14))
Opis: Suma wartości komórek zakresu 'Wydatki', dla których miesiąc = 'styczeń' i miesięczne wydatki >= 2000
Funkcja tablicowa excel Suma.jeżeli - trzy warunki
Przykład 6:
=SUMA((D3:D14>1000)*(D3:D14<4000)*(D3:D14))
Opis: Suma wartości komórek zakresu 'Wydatki', dla których wartość mieści się w zakresie od 1000 do 4000 (bez krańcowych wartości przedziału)
Funkcja tablicowa excel Suma.jeżeli - dwa warunki
Przykład 7:
=SUMA((D3:D14>1000)*(D3:D14<4000))
Opis: Ilość komórek zakresu 'Wydatki', dla których wartość mieści się w zakresie od 1000 do 4000 (bez krańcowych wartości przedziału)
Funkcja tablicowa excel Suma.jeżeli - dwa warunki

Po wprowadzeniu każdej z formuł, mając aktywną komórkę wciskamy Ctrl+Shift+Enter (program uzupełni formułę znakami {})

W punkcie przedstawiono funkcje SUMA.JEŻELI i LICZ.JEŻELI wraz z ich mniej typowymi zastosowaniami. Przedstawiony materiał pozwali na zastosowanie tych funkcji dla dwóch lub trzech kryteriów (w większości przypadków wystarczający zakres). Wprowadzono również pojęcie formuł tablicowych wraz z zastosowaniem do funkcji SUMA i SUMA.JEŻELI. Ten zakres materiału jest znany niewielkiej ilości użytkowników Excela, a pozwala na dość zaawansowaną analizę tabel (co rekompensuje nieco trudniejsze wprowadzanie i modyfikację tych funkcji).

Spis porad Do góry podstrony Do strony głównej

Sortowanie wg kolumn/alfabetycznie/wg listy KLIKNIJ by (roz)winąć

Polecenie sortowania dostępne w Excelu przy działaniach wg kolumny/wiersza z wartościami tekstowymi domyślnie sortuje dane alfabetycznie. W efekcie tabela jak poniżej (kolumny B, C, i E) przesortowana domyślnie (kolumny G, H, i J) nie daje wyniku zgodnego z oczekiwaniami użytkownika.
Sortowanie tabel w Excelu
Powyższa tabela została przesortowana nazw miesięcy alfabetycznie. Sortowanie zgodne z kolejnością miesięcy można uzyskać stosując w menu Dane/Sortuj przycisk opcji i wybierając z listy rozwijanej odpowiedni parametr. Sortowanie wg kolejności miesięcy
Formularz przygotowania sortowania w Excelu
Sortowanie wg kolejności z listy w Excelu
A to efekt końcowy:
Sortowanie wg nazw miesięcy Excel
Wadą tego polecenia jest brak możliwości (w Excelu 2003) zastosowania przy sortowaniu wg dwóch kryteriów.

Większe możliwości sortowania dostępne są w Excelu 2007 - przedstawię je dla przykładu sortowania wg dwóch kryteriów. Narzędzia sortowania dostępne są z menu "Narzędzia główne" / "Sortuj"
Dostęp do sortowania w Excel 2007
oraz z menu "Dane" / "Sortuj"
Menu sortuj w Excel 2007
Formularz dotyczący sortowania zmienił się, ale jest bardzo czytelny. Poniżej przedstawiam parametry ustawione dla tabeli dla dwóch kryteriów (nazwa miesiąca i nazwa dnia tygodnia)
Formularz sortowania w Excel 2007
i wynik działania - kolumny B, C, D, E tabela przed sortowaniem, a w kolumnach G, H, I, J tabela po sortowaniu.
Sortowanie wg dwóch kryteriów w Excel 2007
Zaletą Excela 2007 jest możliwość ustawienia wielu kryteriów sortowania (nie znam dokładnej liczby warunków, które można wprowadzić, ale po zadaniu kilkunastu warunków Excel nadal miał możliwość przyjęcia następnych).

W punkcie przedstawiono mozliwości sortowania danych tabelarycznych w Excel 2007 i Excel 2003. Sortowanie wg listy (nie wg alfabetu), a dla Excela 2007 sortowanie wg dwóch i więcej kryteriów.

Spis porad Do góry podstrony Do strony głównej

Formuły odporne na sortowanie KLIKNIJ by (roz)winąć

Punkt powstał na podstawie zapytania jednego z użytkowników strony. Przeredagowane zapytanie przedstawiam poniżej:
"Opiszę najpierw sytuację a później zadam pytanie
A więc...
Mam tabelę o wymiarze 3x3 zastosowałem w niej sortowanie alfabetyczne po pierwszej kolumnie, wszystko ładnie się posortowało, zakres sortowania rozszerzył się na dwie przylegające kolumny. Wartości z tak posortowanej tabeli użyłem w formule, która je tam w jakiś sposób przekształcała. Muszę dopisać kolejny wiersz do tabeli, czyli teraz ona będzie 4x3 i posortować ją znowu. Dajmy na to że wartości z dopisanego wiersza stają na drugiej pozycji od góry. Wartości w formule uległy zmianie, bo formuła zawiera odwołania do adresów w tabeli a nie do wartości i nie śledzi zmian położenia po sortowaniu.
I teraz pytanie:
Czy jest możliwe stworzenie takich odwołań aby były one przywiązane do adresu komórki, ale w trakcie sortowania śledziły te zmiany w adresie komórki? Aby po sortowaniu wynik w tej formule był taki sam jak przed sortowaniem."
Przedstawiam w jaki sposób poradziłbym sobie z przedstawionym problemem. W przygotowanym arkuszu zastosowałem tabelę
Formuły odporne na sortowanie w Excelu
- w komórkach od B6 do C11 przedstawiono dane dla materiałów budowlanych. Są to grubości (oznaczenie d) i współczynniki lambda (oznaczenie l)
- zadanie: policzyć opory cieplne warstw R = d / lambda
- w komórkach F6 - G8 obliczono opory cieplne dla warstw operując w formułach nazwami z utworzonej listy (odpowiednik odwołań do komórek) - przedstawiono w punkcie Czytelniejsze formuły - nazwy składników wzorów zamiast adresów

Porady Excel - formuły odporne na sortowanie
- w komórkach K6 - K11 skopiowano nazwy warstw
- w komórkach I6 do I8 obliczono opory cieplne dla warstw operując w formułach funkcją Wyszukaj.pionowo - przedstawiono dokładniej w punkcie Funkcja Wyszukaj.Pionowo i jej zastosowania
Składnia tej funkcji:
WYSZUKAJ.PIONOWO(Szukana_wartość; Tabela_tablica; Nr_indeksu_kolumny; Przeszukiwany zakres)
gdzie:
- Szukana_wartość - ciąg znaków, który będzie identyfikatorem wyszukiwania,
- Tabela_tablica - zaznaczamy obszar tabeli, która będzie przeszukiwana i z której będą kopiowane dane,
- Nr_indeksu_kolumny - dane ściągane są z kolumny leżącej o wskazaną ilość kolumn na prawo od kolumny, w której znaleziony został identyfikator wyszukiwania,
- Przeszukiwany_zakres - zawsze wprowadzamy 0 (wyszukiwanie dokładne), chociaż można również 1 (wyszukiwanie przybliżone)
W odwołaniach użyto nazwy skopiowane do komórek K6-K11.
Porady Excel - sortowanie formuł

Proszę porównać wartości wyników w komórkach G6-G8 (odwołania do komórek) i w komórkach (I6-I8 - odwołania z wyszukiwań) podczas sortowania alfabetycznego w komórkach B6-B11. Wyniki oparte na Wyszukaj.Pionowo są prawidłowe i odporne na sortowanie
Porady Excel - sortowanie formuł

Punkt powstał na podstawie zapytania jednego z użytkowników strony szukającego sposobu na takie określenie formuł, które byłyby odporne na sortowanie tzn. odwołania byłyby tak określone by arkusz śledził zmiany w komórkach odwołując się nie do ich adresu, a symbolu wielkości w niej zawartej. Przedstawione rozwiązanie nie jest jedynym możliwym, ale jest stosunkowo proste do uzyskania.

Spis porad Do góry podstrony Do strony głównej

Formaty użytkownika dla wartości liczbowych i dat KLIKNIJ by (roz)winąć

Excel oferuje różnego rodzaju formaty zapisu danych liczbowych. W sytuacji gdy oferowane standardy zapisu są niewystarczające (chcemy pokazać liczby jako punkty, jednostki fizyczne itp.) należy skorzystać z funkcji formatu niestandardowego.
W Excelu 2003 funkcja ta dostępna jest w menu podręcznym (prawy przycisk myszy przy aktywnej komórce) i w menu Format/Komórki
Dostęp do formatów użytkownika w Excel 2003
Menu formatów użytkownika w Excel 2003
W Excelu 2007:
Dostęp do formatów użytkownika w Excel 2007
Menu formatów użytkownika w Excel 2007
Po zaznaczeniu na wyświetlonym formularzu opcji "Niestandardowe" możemy zacząć działać:
Format liczb można utworzyć wykorzystując znaki "0" i "#" oraz tekst w cudzysłowie. Znak "0" (zero) powoduje wyświetlanie nieznaczących zer, znak "#" powoduje wyświetlanie zer znaczących. Tekst wpisany w znakach cudzysłowu traktowany jest jako opis uzupełniający. Cudzysłów trzeba wpisać bezpośrednio po liczbie. Wstawienie np. spacji pomiędzy liczbą a cudzysłowem, spowoduje brak rozpoznania formatu przez program. Formaty napisane przez użytkownika są zapamiętywane w kategorii Niestandardowe i można je stosować na każdym arkuszu w zeszycie.
Na poniższym zrzucie:
- dla komórek B2-B4 zapisano format w postaci:
00" zł ",00" groszy" powodujący zapis wartości liczbowych w postaci zł i groszy, z minimum dwoma liczbami przed i za przecinkiem,
- dla komórek B6-B8 zapisano format w postaci:
00,00" kg" powodujący zapis wartości liczbowych w postaci kg z dwoma miejscami po przecinku i minimum dwoma liczbami przed przecinkiem,
- dla komórek D2-D4 zapisano format w postaci:
00,0" kg" powodujący zapis wartości liczbowych w postaci kg z jednym miejscem po przecinku i jednym miejscem przed przecinkiem,
- dla komórek D6-D8 zapisano format w postaci:
0,0" kg" powodujący zapis wartości liczbowych w postaci kg z jednym miejscem przed i po przecinku,
Wszystkie liczby są jednakowe co do wartości.
Formaty użytkownika dla wartości liczbowych w Excel 2003
Formaty dat stosujemy znaki r - rok, m - miesiąc, d - dzień. Ich ilość określa sposób zapisu daty:
Dzień
d- 2 lub 11
dd - 02 lub 11
ddd - Pn (skrót nazwy dnia tygodnia)
dddd- Poniedziałek - pełna nazwa dnia tygodnia

Miesiąc
m 5
mm 02 lub 11
mmm sty
mmmm styczeń

Rok
rr 09
rrrr 2009

W podobny sposob można tworzyć formaty godzin. Formaty godzin można zapisać korzystając ze znaków specjalnych
g - godzina
m - minuta
s - sekunda
Przy tworzeniu formatów można stosować kolory: czerwony, niebieski, zielony, żółty, biały, czarny, błękitny, purpurowy, podając nazwę koloru w nawiasach kwadratowych, np. [Czerwony].
Formaty użytkownika dla dat i godzin w Excel 2003
Na zrzucie ekranu pokazano kilka formatów zapisu danych w postaci:
- komórka F2 zapisano format daty w postaci:
dd mmmm rrrr dddd
- komórka F3 zapisano format daty w postaci:
   [Czerwony] dddd dd mmmm rrrr
- komórka F4 zapisano format daty w postaci:
   [Niebieski]ddd dd mmm rr
- komórka F6 zapisano format godziny w postaci:
   gg:m:ss
- komórka F7 zapisano format godziny w postaci:
   "godzina " gg" minut "m "i" s "sekund"
- komórka F8 zapisano format godziny w postaci:
   "godz " g:m

W punkcie przedstawiono możliwości zastosowania nietypowych formatów użytkownika dla wartości liczbowych, dat i godzin. Dzięki wprowadzeniu własnego formatowania można uzyskać zapis wartości liczbowych w punktach, kilogramach itp. a zapis wartości dat jako np. poniedziałek 24 sierpień 2009 roku.

Spis porad Do góry podstrony Do strony głównej

Funkcja Wyszukaj.Pionowo i jej zastosowania KLIKNIJ by (roz)winąć

Jedna z ciekawszych funkcji, częściej używanych zwłaszcza przy analizie większych tabel. Występuje w wielu kombinacjach z różnymi funkcjami co zwiększa elastyczność funkcji. Poniżej przedstawiam poszczególne zastosowania rozpoczynając od najprostszego. Przy odpowiednim zastosowaniu można za jej pomocą zrealizować prostą ewidencję (wyszukiwanie cen towarów do faktury VAT, karty wydania materiału z magazynu itp.)
Składnia polecenia:
WYSZUKAJ.PIONOWO(Szukana_wartość; Tabela_tablica; Nr_indeksu_kolumny; Przeszukiwany zakres)
gdzie:
- Szukana_wartość - ciąg znaków, który będzie identyfikatorem wyszukiwania,
- Tabela_tablica - zaznaczamy obszar tabeli, która będzie przeszukiwana i z której będą kopiowane dane,
- Nr_indeksu_kolumny - dane ściągane są z kolumny leżącej o wskazaną ilość kolumn na prawo od kolumny, w której znaleziony został identyfikator wyszukiwania,
- Przeszukiwany_zakres - zawsze wprowadzamy 0 (wyszukiwanie dokładne), chociaż można również 1 (wyszukiwanie przybliżone)

Na zrzucie ekranu pokazałem tabelę źródłową (tabela 1) i tabelę z funkcją Wyszukaj.Pionowo (tabela 2). Wprowadzone parametry widoczne są na kolejnej ilustracji.
- Szukana wartość - wprowadzić można ciąg znaków lub adres komórki (przy adresie łatwiej jest kopiować formułę)
- Tabela_tablica - wskazany został obszar tabeli ze znakami $ tak uniemożliwić zmianę adresu tabeli przy kopiowaniu formuły,
- Nr_indeksu kolumny - wprowadzono 2 (wyszukanie pierwszej kolumny na prawo od szukanej wartości,
Funkcja Wyszukaj.Pionowo
Parametry funkcji Wyszukaj.Pionowo w Excel 2003
Uwaga:
Przy wyszukiwaniu zarówno ciąg znaków będący identyfikatorem wyszukiwania jak i sposób jego sformatowania jest bardzo ważny - jeżeli w ciągu znaków wprowadzimy spację to wyszukiwanie da wynik negatywny, podobnie jak w przypadku gdy w jednej tabeli szukana wartość będzie sformatowana jako liczba, a w drugiej jako tekst.
Funkcja Wyszukaj występuje też jako wyszukiwanie wierszami (Wyszukaj.Poziomo)

W punkcie przedstawiono funkcje wyszukaj.pionowo i niektóre spośród możliwości jej zastosowania. Jast to standardowa funkcja Excela bardzo przydatna przy analizie długich tabel. Punkt będzie uzupełniany.

Spis porad Do góry podstrony Do strony głównej

Nowe funkcje arkuszowe w Excelu 2007 KLIKNIJ by (roz)winąć

IFERROR/JEŻELI.BŁĄD
Składnia tej funkcji jest następująca:

JEŻELI.BŁĄD(wartość;wartość_jeżeli_błąd)
Funkcja ta sprawdza czy wartość (oczywiście wartością może być wyrażenie/formuła) zwraca błąd. Jeśli to wyrażenie zwróci jeden z następujących błędów: #N/D!, #ARG!, #ADR!, #DZIEL/0!, #LICZBA!, #NAZWA? i #ZERO! to jako wynik otrzymamy wartość_jeżeli_błąd np.

=JEŻELI.BŁĄD(2/A2;"Wystąpił błąd!")

Jeżeli w komórce A2 będzie zero lub będzie ona pusta, wynikiem formuły będzie tekst Wystąpił błąd!. A jeśli tam będzie liczba, to otrzymamy wynik dzielenia liczby 2 przez wartość komórki A2.
Oczywiście podobny mechanizm był możliwy do implementacji bez użycia tych formuł poprzez połączenie funkcji JEŻELI oraz CZY.BŁĄd, ale nowe rozwiązanie jest krótsze i bardziej eleganckie.

AVERAGEIF/ŚREDNIA.JEŻELI
Składnia funkcji jest następująca:

ŚREDNIA.JEŻELI(zakres;kryteria;średnia_zakres)
gdzie:
   -Zakres to jedna lub więcej komórek, które mają zostać uśrednione, włączając w to liczby lub nazwy, a także tablice lub odwołania zawierające liczby,
   -Kryteria to kryteria w postaci liczby, wyrażenia, odwołania do komórki lub tekstu, określające komórki, dla których zostanie obliczona średnia. Kryteria można wyrazić na przykład jako 5, "5", ">5", "jabłka" lub B2
   -Średnia_zakres (opcjonalny) to rzeczywisty zestaw komórek, dla których zostanie obliczona średnia. W przypadku pominięcia tego argumentu zostanie użyty parametr zakres.

AVERAGEIFS/ŚREDNIA.WARUNKÓW
Składnia jest następująca:

ŚREDNIA.WARUNKÓW(średnia_zakres; kryteria_zakres1;kryteria1; kryteria_zakres2; kryteria2...)
gdzie:
   -Średnia_zakres to jedna lub więcej komórek, które mają zostać uśrednione, włączając w to liczby lub nazwy, tablice lub odwołania zawierające liczby,
   -Kryteria_zakres1, kryteria_zakres2... to zakresy (od 1 do 127), w których zostaną sprawdzone skojarzone kryteria,
   -Kryteria1; kryteria2;... to kryteria (od 1 do 127) w postaci liczby, wyrażenia, odwołania do komórki lub tekstu określające komórki, które mają zostać uśrednione. Argument kryteria można wyrazić na przykład jako 5, "5", ">5", "styczeń" lub A2

SUMIFS/SUMA.WARUNKÓW
Składnia funkcji jest następująca:

SUMA.WARUNKÓW(suma_zakres; kryteria_zakres1; kryteria1,[kryteria_zakres2; kryteria2...]...)
Ta funkcja jest bardzo podobna do funkcji ŚREDNIA.WARUNKÓW, z tą różnicą, że zamiast obliczenia średniej liczymy sumę.

COUNTIFS/LICZ.WARUNKI
Składnia:

LICZ.WARUNKI(kryteria_zakres1; kryteria1;[kryteria_zakres2; kryteria2]...)
Składnia oraz użycie są również bardzo podobne do poprzednich funkcji.

W punkcie przedstawiono pięć nowych funkcji arkuszowych dostępnych w Excelu 2007. Możliwości przez nie oferowane były już wcześniej dostępne przez kombinację różnych funkcji Excela 2003, ale obecne zastosowanie jest prostsze. Należy jednak pamiętać, że zastosowanie nowych funkcji uczyni arkusz niedostępnym dla użytkowników Excela 2003 i starszych. Punkt powstał na podstawie materiałów znalezionych w sieci.

Spis porad Do góry podstrony Do strony głównej

Czytelniejsze formuły - nazwy składników wzorów zamiast adresów KLIKNIJ by (roz)winąć

Tworzenie nazwy z zaznaczenia pokazuję na przykładzie małej tabelki z obliczeniami oporu przewodzenia ciepła dla warstw materiału budowlanego. Opór cieplny (R) jako wielkość fizyczną wyznacza się jako iloraz grubości warstwy (d) i współczynnika przewodzenia ciepła materiału (lambda). W tabelce w komórkach "B6-C8" oraz "E6-F8" zebrano dane dla niektórych materiałów budowlanych, a w komórkach "B11-C13" oraz "E11-F13" zebrano obliczenia oporu cieplnego. W komórkach "C11-C13" zastosowano formuły z odwołaniami do adresów komórek, a w komórkach F11-F13" zastosowano formuły z odwołaniami nazw składników.
Tworzenie nazw z zaznaczenia z zaznaczenia w Excelu
Tabelka przykładowa

Funkcje w excelu - porady
Formuły z odwołaniami do adresów komórek

Funkcje w Excelu - porady
Formuły z odwołaniami do nazw składników

Utwórz nazwę w Excelu
Sposób uzyskania - zaznaczamy fragment tabeli i z menu "Wstaw" wybieramy "Nazwa" i "Utwórz"

Opcje tworzenia nazwy w Excelu
Dla wybranego zakresu wybieramy opcję wskazującą gdzie są nazwy

Tworzenia nazwy z zaznaczenia w Excelu 2007
Dla Excela 2007 - Zakładka "Formuły" i dalej "Utwórz z zaznaczenia"

Wadą rozwiązania jest brak odporności na sortowanie. Nazwy użyte w formułach są traktowane tak samo jak adresy, przy czym są znacznie trudniejsze do wyszukania błędów - należy na to bardzo uważać.

W punkcie przedstawiono jeden ze sposobów na uzyskanie czytelniejszych w analizie formuł Excela - zamiast odwołań do komórek w rodzaju "=C45/D45" można uzyskać "=Grubość_warstwy/Lambda_materiału". Ten typ odwołań możliwy jest do wykorzystania w niektórych rodzajach tabel. Wielu użytkownikom Excela taki typ zapisu odpowiada bardziej niż tradycyjny używający adresów komórek. Przedstawiono sposób tworzenia listy z zaznaczenia dla Excela 2003 i 2007

Spis porad Do góry podstrony Do strony głównej

Wstawianie tabeli ze strony internetowej - kwerenda WEB KLIKNIJ by (roz)winąć

Sposób pobierania danych ze strony internetowej pokazano na przykładzie strony ministerstwa infrastruktury z zestawieniem stacji meteorologicznych dla których zebrano obowiązujące do obliczeń cieplnych budynków dane klimatyczne.
Krok 1 - Z menu "Dane" podmenu "Importuj dane zewnętrzne" wybieramy polecenie "Nowa kwerenda sieci Web"
Porady Excel - kwerenda WEB
Krok 2 - W wyświetlonym formularzu podobnym do okna przeglądarki wpisujemy adres strony (standardowo widoczna jest strona startowa)
Excel wstawianie kwerendy WEB
Excel - wstawianie tebeli ze strony internetowej
Krok 3 - W prawym górnym rogu formularza widoczny jest przycisk Opcji. Jeżeli chcemy by importowana tabela była sformatowana jak na stronie w wyświetlonym formularzu zaznaczamy opcję "Pełne formatowanie HTML" Tabela ze strony internetowej w Excelu
Krok 4 - Tabele na wczytanej stronie zaznaczone będą charakterystyczną żółtą strzałką. Po podświetleniu tabeli zostanie ona otoczona pogrubioną ramką, a strzałka zmieni kolor na zielony
Porady Excel - tabela z sieci
Krok 5 - Na koniec zaznaczmy komórką startową wczytania tabeli.
Excel i sieć www
Krok 6 - Widoczna wczytana tabela z formatowaniem
Excel - wstawiona tabela www z formatowaniem
Krok 7 - Ta sama tabela bez formatowania
excel - wstawiona tabela www bez formatowania
Wstawianie tabeli ze strony www do arkusza w Excelu 2007 przebiega w zasadzie identycznie. Polecenia dostępne są w zakładce "Dane" i dalej "Pobierz dane z sieci WEB":
Excel 2007 wstawianie tabeli ze strony www

W punkcie przedstawiono sposób importowania do arkusza Excela stabelaryzowanych danych zawartych na stronie internetowej. Wskazano możliwość zachowania formatowania źródłowego danych internetowych. Zrzuty ekranu w omówieniu dotyczą Excela 2003 ze wskazaniem tych samych opcji w Excelu 2007.

Spis porad Do góry podstrony Do strony głównej

Praktyczne zastosowania stylów dla Excela 2007 KLIKNIJ by (roz)winąć

Style to jeden z bardziej przydatnych i najmniej docenianych elementów Excela 2007. Pozwalają na szybkie i estetyczne formatowanie naszych arkuszy i tabelek. Dostępne są w zakładce Narzędzia główne (obok formatowania warunkowego i formatuj jako tabelę). Podczas używania stylów cały czas w zaznaczonych komórkach mamy podgląd na żywo - wystarczy najechać kursorem na styl.
Porady Excel - zastosowanie styli dla Excela 2007
Możemy korzystać ze stylów zdefiniowanych lub modyfikować istniejące. Elementy składowe stylu, które możemy modyfikować to:
- Format liczb
- Czcionka
- Wyrównanie
- Obramowanie
- Wypełnienie tła
- Ochrona komórki
Jeśli zmienimy jakikolwiek z elementów formatowania danego stylu zmieniony zostanie wygląd wszystkich komórek, gdzie zastosowany został ten styl - jest to główna zaleta stosowania stylów. Jeżeli mamy skoroszyt z dużą ilością arkuszy i chcemy zmodyfikować nagłówki tabel - jeśli zastosowaliśmy style nie musimy wchodzić osobno do każdego arkusza, wystarczy w tym stylu zmienić nagłówek !
Zastosowanie stylów jest proste: zaznaczamy komórki, które chcemy sformatować, wybieramy Narzędzia główne - Style komórki, wybieramy styl i klikamy na niego.

To wszystko. MODYFIKOWANIE ISTNIEJĄCEGO STYLU
Aby zmodyfikować istniejący styl wybieramy Narzędzia główne - Style komórki, klikamy prawym przyciskiem myszy na żądanym stylu i wybieramy Modyfikuj. Pojawia nam się okno dialogowe z formatowaniem stylu:
Porady Excel - zastosowanie styli dla Excela 2007
TWORZENIE NOWEGO STYLU
Podobnie jak modyfikację tylko po wybraniu Narzędzia główne i style komórki trzeba kliknąć na Nowy styl komórki. Warto własnym stylom nadawać bardziej rozbudowane nazwy niż "Styl 1" i "Styl 2".

ŁĄCZENIE STYLÓW Z INNYCH SKOROSZYTÓW
Nowe i zmodyfikowane style dostępne są tylko w danym pliku. Aby zaimportować styl z innego pliku oba skoroszyty muszą być otwarte. Po wybraniu Narzędzia główne i Styl komórki klikamy na polecenie scal style. Excel skopiuje style ze wskazanego pliku do naszego aktualnie używanego.

Style to jeden z bardziej przydatnych i najmniej docenianych elementów Excela 2007. Pozwalają na szybkie i estetyczne formatowanie naszych arkuszy i tabelek. Dostępne są w zakładce Narzędzia główne (obok formatowania warunkowego i formatuj jako tabelę). Podczas używania stylów cały czas w zaznaczonych komórkach mamy podgląd na żywo - wystarczy najechać kursorem na styl.

Spis porad Do góry podstrony Do strony głównej

Praktyczne obsługa tabel dla Excela 2007 KLIKNIJ by (roz)winąć

Nowością w Excelu 2007 znacznie poprawiającą funkcjonalność i ułatwiającą obsługę list danych jest zastosowanie tabel. Tabela to rodzaj najczęściej spotykanych raportów (lista kontrahentów, sprzedaży, produktów itp.)
Porady Excel - obsługa tabel w praktyce
Taka tabela i sposób jej formatowania to podstawa we wcześniejszych wersjach Excela. Przewaga Excela 2007 rozpoczyna się od wybrania polecenia Tabela z zakładki wstawianie
Obsługa tabel w praktyce dla Excela 2007
W uwidocznionej zakładce Projektowanie dostępne są style formatowania tabeli pozwalające szybko uzyskać estetyczne zestawienia. Można również wybrać wyróżnienie pierwszej lub ostatniej kolumny, sumę kolumn itp. Dla wiersza podsumowania można wstawić oprócz sumy funkcje agregujące: zliczanie, odchylenie standardowe, wariancję czy pozostałe funkcje arkusza. Wystarczy kliknąć na komórce z podsumowaniem, by z listy rozwijanej wybrać żądaną funkcję.
Obsługa tabel w praktyce dla Excela 2007
Dodanie kolumny do tabeli jest bardzo proste - wystarczy w kolumnie C wstawić nową nazwę kolumny i tabela samoczynnie się rozszerza. Wpisując pod nagłówkiem jakąkolwiek funkcję (np. obliczającą prowizję), Excel automatycznie wypełnia resztę komórek w tabeli takimi samymi funkcjami. Zmiana formuły w którejkolwiek komórce spowoduje zmianę wszystkich formuł w danej kolumnie. Dostępne jest również usuwanie duplikatów. Podobnie proste jest wykonanie wykresu w oparciu o taką tabelę.

W punkcie przedstawiono dostępną dopiero dla Excela 2007 praktyczną i szybką obsługe tabel. Funkcje ta są rzadko wykorzystywane, a pozwalają wykonać szybko i małym nakladem sił esttyczne tabele raportów, wykonać na tych tabelach podstawowe obliczenia, a także - co często jest przez wielu poszukiwane - usunąc duplikaty dla zadanych kryteriów.

Spis porad Do góry podstrony Do strony głównej

Niewielkie objętościowo zagadnienia obsługi Excel

Możliwości Excela w edycji obramowań tabelek są powszechnie znane i używane. Czasami jednak pragniemy, by nasza tabelka była szczególnie staranna i nieco inna. Warto wtedy skorzystać z funkcji ręcznego rysowania obramowań dzięki czemu mamy mozliwość uzyskania różnych kolorów i rodzajów linii obramowań.

RYSOWANIE OBRAMOWAŃ KLIKNIJ by (roz)winąć

- Rozpoczynamy od wybrania polecenia 'Rysuj obramowania' dostępnego w menu standardowych ramek tabeli,
Nietypowe ramki excela
- Mamy dostęp do paska narzędzi 'Obramowanie' z poleceniami wyboru rodzaju i koloru linii, wymazywania linii (gumki) i siatki obramowań
Tabelki w excelu
- Przykładowy efekt końcowy,
Obramowanie tabelki excela
RYSOWANIE OBRAMOWAŃ W EXCEL 2007 - UWAGI
Opcje dostępne są między innymi z zakładki 'Narzędzia główne' na wstążce.
Obramowanie tabelki excela 2007
Prawy przycisk myszy użyty w obszarze roboczym arkusza daje dostęp do różnego rodzaju obramowań standardowych (w Excelu 2003 występowały, ale były trudniej dostępne).
Inne obramowania tabelki excela 2007
Niestandardowe obramowania tabeli excela 2007

W przypadku pracy z dużymi, nie mieszczącymi się na ekranie tabelami Excela przydatne są proste polecenia blokujące nagłówki kolumn lub wierszy tabeli oraz dzielące obszar roboczy na niezależne okna. Wykorzystanie tych poleceń bradzo ułatwia pracę.

BLOKOWANIE OKIENEK KLIKNIJ by (roz)winąć

- ustawiamy aktywną komórkę tak by po jej lewej stronie i na górze była część tabeli, która ma pozostać nieruchoma. W menu rozwijanym 'Okno' wybieramy polecenie 'Zablokuj okienka'
Blokowanie okienek w excelu
- Podobny efekt uzyskamy tylko dla wierszy lub tylko dla kolumn, jeżeli zaznaczymy wiersz/kolumnę i dopiero później w menu rozwijanym 'Okno' wybierzemy polecenie 'Zablokuj okienka'
Blokowanie wierszy lub kolumn w oknie excela
- Gotowy efekt przy przewijaniu ekranu
Ułatwienia wyświetlania w oknie excela
- Potencjalnie bardzo przydatną funkcją, zwłaszcza przy wykonywaniu dużej liczby obliczeń jest zablokowanie okien tak by wyświetlać jakby kilka ekranów w jednym, obrazujących różne części dużej tabeli (efekt bardzo podobny można uzyskać w Auto-Cadzie przy rysowaniu na rzutniach) - w Excelu wybieramy z menu 'Okno' funkcję 'Podział' przy odpowiednio ustawionej aktywnej komórce.
Podział wyswietlania tabeli excela w jednym oknie
BLOKOWANIE OKIEN W EXCEL 2007 - UWAGI
Wszystkie opisane wyżej polecenia dostępne są w wydzielonym dziale zakładki 'Widok' na wstążce szybkiego dostępu. efekty ich użycia są identyczne z opisanymi wyżej, ale dostęp dużo prostszy.
Blokowanie i podział okien w Excel 2007

Często niedocenianym szczegółem obsługi Excela są możliwości paska stanu. Można zmodyfikować go w taki sposób, by mieć szybki wgląd w podstawowe obliczenia zaznaczonego zakresu komorek.

PASEK STANU KLIKNIJ by (roz)winąć

- Na początek uruchomimy pasek stanu w Excelu 2003 - w menu 'Widok' zaznaczamy opcję 'Pasek stanu'
Pasek stanu w excelu
- Teraz klikając prawym przyciskiem myszy na pasku u dołu ekranu możemy wybrać działanie, które będzie obrazowane na pasku zadań.
Porady excel - pasek stanu
- Teraz w trakcie zaznaczania zakresu komórek na pasku zadań wyświetalny będzie wynik zdefiniowanego działania - nie musimy posługiwać się już żadną inną funkcją.
Ułatwienia obsługi excela - działania paska zadań
PASEK STANU W EXCEL 2007 - UWAGI
Jest to jeden z najbardziej rozbudowanych elementów Excela 2007 (w porównaniu z wersją 2003). Po kliknięciu prawym przyciskiem myszy na pasku uzyskujemy dostęp do całej masy funkcji, które po zanzaczeniu będą widoczne. W przypadku funkcji matematycznych w końcu jest możliwość wybrania więcej niż jednej ;)
Pasek stanu Excel 2007 - porady
Pasek stanu Excel 2007 - możliwości

W punkcie przedstawiono, krótkie informacje dotyczące mniej znanych, a przydatnych i ciekawych funkcji arkusza kalkulacyjnego Excel. Ich właściwe wykorzystanie może poprawić komfort pracy użytkwoników programu, a przez to efektywność realizowanych w Excelu działań.

SKRÓT KLAWIATUROWY ALT + ENTER KLIKNIJ by (roz)winąć

Niedawno znalazłem podpowiedź w literaturze - drobna rzecz, a cieszy. Podczas edycji długich formuł lub napisów w komórkach Excela, przydatną funkcją jest możliwość dzielenia zawartości komórki na 'linijki' tekstu. Uzyskać ten efekt możemy makrem (wykonałem coś takiego na potrzeby jednego z programów) lub zupełnie proso - lewy Alt + Enter
Edycja w komórce excela
Skrót alt + enter w excelu

W punkcie przedstawiono, malutką pchełkę ułatwiającą pracę z długimi wpisami tekstowymi lub formułami w komórce Excela - skrót lewy Alt + Enter pozwalający podzielić tekst na linijki ;)

KARTA DEVELOPER NA WSTĄŻCE KLIKNIJ by (roz)winąć

Często pojawiający się temat poszukiwań. Karta Developer przeznaczona jest dla bardziej zaawansowanych użytkowników, zainteresowanych szerszą edycją makr i programowaniem VBA. Standardowo nie pojawia się na wstążce. Uruchamianie następuje z poziomu opcji programu Excel
Karta Developer na wstążce
Teraz zakładka 'Popularne' i w 'Najczęściej używanych opcjach w pracy z programem Excel' stawiamy ptaszka w interesującej nas rubryce ;)
Uruchamianie edycji makr w excel 2007

W punkcie przedstawiono, malutką pchełkę przywracającą kartę 'Developer' na wstążkę szybkiego dostępu. Temat często szukany przez zaawansowanych użytkowników przesiadających się z wersji 2003 na 2007 Excela.

OPCJE OCHRONY FORMUŁY, STRUKTURY SKOROSZYTU I OTWARCIA PLIKU KLIKNIJ by (roz)winąć

Ten krótki punkt stanowi uzupełnienie wyżej opisanego punktu 'Udostępnianie skoroszytu, arkusza lub części komórek'. Wymienione zostały w nim możliwości zabezpieczenia:
- formuły w komórkach przed zmianą lub podglądem,
- struktury skoroszytu (dodawania/usuwania arkuszy),
- pliku ms excel przed otwarciem
Aby chronić formułę w komórce przed zapisaniem/zmodyfikowaniem/podglądem należy kolejno:
- zaznaczyć komórki, które mogą zostać nadpisane,
- z menu Format wybrać pozycję Komórki i w oknie Formatowanie komórek kliknąć zakładkę Ochrona,
- w zakładce Ochrona wyłączyć opcję Zablokuj (jeżeli zaznaczona jest opcje Ukryj to formuły zostaną ukryte tzn. uaktywnienie komórki nie spowoduje wyświetlenia formuły na pasku excela),
- w menu Narzedzia w pozycji Ochrona wybranie pozycji Chroń arkusz i podanie (z potwierdzeniem) hasła,
Uwaga: Powyższe dotyczy Excela 2003, ale w wyzej wymienionym punkcie można zobaczyć zrzuty ekranu obrazujące gdzie na wstążce szybkiego dostępu szukać wymienionych opcji,

Aby chronić skoroszyt przed dodaniem lub usunięciem arkuszy należy włączyć opcję ochrony struktury skoroszytu tzn. z menu Narzędzia i opcji Ochrona wybrać pozycję Chroń skoroszyt i włączyć opcję struktura,

Aby chronić plik przed otwarciem (możliwe tylko dla osób znających hasło) zapisywany plik musimy zabezpieczyć hasłem: z menu Plik wybieramy pozycję Zapisz jako, a następnie w oknie dialogowym Zapisywanie jako klikamy przycisk Narzędzia:
Ochrona pliku Excela przed otwarciem
Teraz w przycisku Narzędzi wybieramy pozycję Opcje ogólne
Ochrona pliku Excela przed zapisaniem zmian

W punkcie przedstawiono, możliwości ochrony formuł Excela przed zmianą lub/i podglądem, zabezpieczenia skoroszytu Excela przed usunięciem/dodaniem arkuszy oraz zabezpieczenia pliku Excela przed otwarciem lub zapisaniem zmian. Punkt stanowi uzupełnienie wyżej opisanego 'Udostępniania skoroszytu, arkusza lub części komórek'.

RODZAJE ADRESOWANIA W EXCELU, ODWOŁANIA WZGLĘDNE I BEZWZGLĘDNE KLIKNIJ by (roz)winąć

Formuły pisane w Excelu odwołuja się do konkretnej komórki lub zakresu tych komórek. Jeżeli zakres został nazwany w formułach operujemy nazwą zakresu. Jeżeli odwołanie wykonane jest za pomocą adresu komórki możemy wyróżnić kilka metod takiego odwołania:
- Odwołanie względne - po skopiowaniu komórki odwołanie zostanie dostosowane do nowej lokalizacji. Najczęściej spotykany rodzaj odwołania, domyślny przy kopiowaniu formuł przez przeciąganie w danym zakresie. Przykład adresu: A1. Odwołanie nieprzydatne, gdy każda komórka z danego zakresu ma odwoływać się do jednej i tej samej wartości z innej komórki np. ceny jednostkowej, stałej fizycznej itp.
- Odwołanie bezwzględne - po skopiowaniu formuły do nowego zakresu komórek odwołanie nie ulegnie zmianie. Przykład adresu: $A$1,
- Odwołanie cześciowo względne - kombinacja dwóch powyższych rodzajów. Po skopiowaniu do nowego zakresu część adresu pozostanie stała. Dla adresu postaci: A$1 stała pozostanie część dotycząca wiersza, a dla adresu $A1 stała pozostanie część dotycząca kolumny,

W punkcie przedstawiono, możliwości odwołań do adresów komórek. Domyślnymi w Excelu są adresy względne (reagujące na kopiowanie formuły). Oprócz nich można używać odwołań bezwględnych stałych co do adresu wiersza, bezwzględnych stałych co do adresu kolumny lub całkowicie bezwzględnych (nie reagujących na kopiowanie). Różne rodzaje odwołań uzyskuje się przez wykorzystanie znaku $ w zapisie formuły Excela.

SKRÓTY KLAWIATUROWE W EXCELU KLIKNIJ by (roz)winąć

Na pewnym etapie zaawansowania w używaniu każdego programu przydatne zaczynają być skróty klawiaturowe (warto zwrócić uwagę na osoby używające np. Photoshopa).

Najprostsze (właściwe nie tylko dla Excela) skróty to:
Ctrl+C - kopiuj
Ctrl+V - wklej
Ctrl+X - wytnij
Ctrl+P - drukuj
Ctrl+F - wyszukaj
Ctrl+A - zaznacz wszystko
Ctrl+Z - cofnij ostatnią operację
Ctrl+Y - utwórz nowy plik,
Ctrl+I - zmień czcionkę na kursywę,
Ctrl+B - czcionka pogrubiona,
Ctrl+U - czcionka podkreślona,
Ctrl+S - zapisz plik,

Nieco inne:
Ctrl+H - zamień
Ctrl+Shift+8 - zaznaczenie całej wybranej tabeli
Alt+Tab - przechodzenie do innych aktywnych aplikacji
Ctrl+Tab - przechodzenie do innych aktywnych plików Excela
Shift+(strzałka) - zaznaczanie komórek
Ctrl+Shift+(strzałka) - jak wyżej, ale jeżeli w obszarze znajdują się puste komórki zaznaczenie zatrzyma się w tym miejscu. Kontynuacja jest możliwa po powtórzeniu kombinacji przycisków,
Ctrl+Enter - wprowadzenie w całym zaznaczonym obszarze jednakowej formuły lub jednakowych danych,
Ctrl+(strzałka) - aktywacja ostatniej niepustej komórki (jeśli aktywną jest komórka w obszarze niepustych komórek) lub skok do ostatniej pustej komórki (jeśli aktywną jest pusta komórka)
Ctrl+Shift+7 - pojedyncza ramka wokół zaznaczonego obszaru
Ctrl+Shift+= - wstawianie wiersza/kolumny lub komórki (zależnie od zaznaczenia przed wybraniem tej kombinacji klawiszy.)
Ctrl+Shift+P - wejście do zmiany rozmiaru czcionki lub uruchomienie okna formatowanie komórek
Ctrl + ; - wstawia w komórce bieżącą datę
Ctrl + ' - kopiuje zawartość lub formułę (bez zmiany odwołań) z komórki powyżej
Alt + dolna strzałka - skrót do polecenia "wybierz z listy rozwijanej"
Ctrl+PageUp - przesuwa jeden arkusz w prawo,
Ctrl+PageDown - przesuwa jeden arkusz w lewo

Edycja formuł bezpośrednio w komórkach:
Opcja przydatna dla osób dużo pracujących z Excelem
Przycisk F2 - edycja w zaznaczonej komórce, ale nie w pasku formuł). Jeżeli przycisk F2 ma pozwalać na edycję w pasku formuł należy zmienić domyślne ustawienia Excela:
W przypadku wersji Excel 2003
Menu Narzędzia / Opcje na zakładce "Edycja" zaznaczamy pole: "Edytuj bezpośrednio w komórce",

W przypadku wersji Excel 2007:
W przycisk pakietu Office (okrągły przycisk z logo w prawym górnym rogu ekranu) należy wybrać 'Opcje programu Excel" i w pozycji "Zaawansowane" zaznaczyć opcję "Zezwalaj na edytowanie bezpośrednio w komórkach".

W punkcie przedstawiono niektóre skróty klawiaturowe ułatwiające pracę w Excelu oraz możliwość edycji danych w komórkach bez użycia myszy.

INSPEKCJA FORMUŁ I SKRÓT CTRL + ~ KLIKNIJ by (roz)winąć

Prosty skrót klawiaturowy wywołujący polecenie z menu "Inspekcja formuł" wprowadzające do widoku komórek formuły, które są w nich użyte. Ułatwia analizę tabel arkusza i szukanie ewentualnych niezgodności i błędów. Pierwotny widok przywraca powtórne użycie powyższego skrótu klawiaturowego.
Inspekcja formuł w Excelu
Widok tabeli obliczeniowej
Porady Excel - Inspekcja formuł
Widok tabeli po użyciu Ctrl + ~

Inna przydatna funkcja z menu "Inspekcja formuł" to polecenie "Śledź poprzedniki". Pomaga ono przy analizie formuł tzn. sprawdzeniu czy wszystkie interesujące nas komórki zostały uwzględnione w formułach. Zależności pokazywane są jako strzałki tak jak na załączonym screenie.
Porady Excel - Śledź poprzedniki

W punkcie przedstawiono niektóre możliwości menu "inspekcja formuł" wraz ze skrótem klawiaturowym wywołującym najbardziej typową funkcję tego menu. Polecenie przydatne przy analizie większych tabel obliczeniowych.

WSTAWIANIE I FORMATOWANIE KOMENTARZY KLIKNIJ by (roz)winąć

Wstawianie komentarzy jest przydatną opcją szczególnie w sytuacjach gdy na pliku. którego jesteśmy autorem pracować ma kilka osób i chcemy zapisać podpowiedzi, objaśnienia lub uwagi dla pozostałych użytkowników.
Wstawianie komentarzy dostępne jest z poziomu menu pod prawym przyciskiem myszy.
Porady Excel - wstawianie komentarzy
Komentarz w arkuszu Excela
Standardowo w oknie komunikatu pojawia się tylko nazwa użytkownika. W oknie możemy dodać własną treść. Okno komunikatu możemy przesunąć (kursor ustawia się na ramce tak by był widoczny krzyżyk):
Możliwości komentarzy Excela
zmienić rozmiar (kursor ustawia się na ramce tak by była widoczna strzałka zmiany rozmiaru):
Zmiana rozmiaru komentarzy Excela
Przez menu z prawego przycisku myszy mamy dostęp do formatowania wyglądu komentarza:
Formatowanie komentarzy w Excelu
Edycja komentarzy w Excelu
Gotowy komentarz komórki symbolizowany jest przez czerwony trójkąt w prawym, górnym rogu komórki i widoczny po ustawieniu kursora na tym symbolu.
W ustawieniach strony dostępna jest również opcja wydruku komentarzy:
Wydruk komentarzy w Excelu

W punkcie przedstawiono niektóre możliwości wprowadzania komentarzy do komórek arkusza Excela wraz z edycją tych elementów oraz przygotowaniem do wydruku komentarzy.

SZYBKIE PRZEJŚCIE DO WŁAŚCIWEGO ARKUSZA W WIELOARKUSZOWYM SKOROSZYCIE KLIKNIJ by (roz)winąć

Szybkie przechodzenie między arkuszami Excela
Zamiast uzywać skrótów Ctrl + PageDown / Ctrl + PageUp, można użyć prawego przycisku myszy na obszarze strzałek przesuwnania arkuszy. Pojawi się spis arkuszy na którym można wybrać ten poszukiwany.

W punkcie przedstawiono sposób na łatwą nawigację w skoroszycie zawierającym wiele różnych arkuszy, przy czym nie korzystano ze skrótów klawiaturowych przesuwających po jednym arkuszu w prawo lub w lewo.

FORMUŁA AUTOMATYCZNEGO NUMEROWANIA WIERSZY KLIKNIJ by (roz)winąć

Automatyczna numeracja wierszy
Prosta formuła numerująca wiersze w zależności od tego czy komórka obok jest zajęta. Mała, ale elegancka pchełka.
=JEŻELI(B4<>"";WIERSZ()-3&".";"")
Jeżeli komórka B4 nie jest pusta to w komórce A4 wstawiany jest numer wiersza pomniejszony o 3 i uzupełniony o znak kropki.

W punkcie przedstawiono sposób na automatyczną numerację wierszy w zależności od zajęcia sąsiedniej komórki. Efekt uzyskano zastosowaniem jednej formuły.

Spis porad Do góry podstrony Do strony głównej

© 2009-2011 G. Koralewski