ZAŁOŻENIA PODSTRONY 'PORADY EXCEL'    KLIKNIJ by (roz)winąć

Niniejsza podstrona powstała i będzie uzupełniania na podstawie moich własnych pomysłów dotyczących przydatności pewnych poleceń Excela oraz w oparciu o frazy, które wprowadzane przez użytkowników wyszukiwarki Google doprowadziły do odwiedzin witryny 'makroExcel - programy w excelu'. Dostęp do tych fraz i haseł zapewnia system statystyk zainstalowany na stronie głównej i podstronach serwisu. Część z szukanych zagadnień dotyczy możliwości czystego Excela (i tę część przedstawiam na tej stronie), a część dotyczy możliwości zastosowania makr i języka VBA (tę część przedstawiam na podstronie Zagadnienia VBA i całym dziale Warsztat VBA). Oczywiście sprecyzowanie tematu poszukiwań jest zadaniem trudnym i opiera się w dużej mierze na moich przypuszczeniach. Przewidywana zawartość działu to artykuły z przykładami i zrzutami ekranu oraz przykładowe pliki do pobrania. Często frazy wprowadzane do wyszukiwarki dopuszczają dowolność interpretacji i z tego względu zawartość artykułów stanowi wypadkową mojej wiedzy i przypuszczeń co do zakresu szukanego przez użytkownika materiału. Aby zapewnić przyjazną nawigację w dziale dostęp do gromadzonych artykułów zamierzam zapewnić przez wprowadzenie dwóch grup hasłowych: pełen tytuł artykułu lub zagadnienia oraz frazy wprowadzane do wyszukiwarki Google.

Wszystkie zawarte w dziale pliki są również dostępne w zakładce pliki z menu górnego strony. W wielu przypadkach zawarte bedą odsyłacze do innych podstron serwisu lub/i pozycji literatury. Mam nadzieję, że ten sposób prowadzenia strony spotka się z akceptacją. Zebrane niżej spisy tematów mają za zadanie ułatwić wyszukanie interesujących dla użytkownika zagadnień, co nie oznacza, że każdy z tych nagłówków to tytuł osobnego artykułu.

Opracowania powstały na podstawie mojej wiedzy w tym zakresie.
Jeżeli zauważasz nieścisłości w tekście proszę o kontakt.

ZAWARTOŚĆ DZIAŁU PORADY EXCEL

• Formatowanie warunkowe
• Rozdzielanie i łączenie tekstów
• Przygotowanie wydruku
• Praca grupowa na arkuszach
• Udostępnianie skoroszytu, arkusza lub części komórek
• Wklejanie specjalne. Zamiana wierszy na kolumny w Excelu
• Narzędzia zliczające Excel - Sumy pośrednie
• Funkcje Jeżeli, Lub, Oraz
• Sprawdzenie poprawności danych /prosta lista rozwijana/
• Zastosowanie funkcji Licz.Jeżeli oraz Suma.Jeżeli /formuła tablicowa/
• Sortowanie wg kolumn/alfabetycznie/wg listy
• Formuły odporne na sortowanie
• Formaty użytkownika dla wartości liczbowych i dat
• Funkcja Wyszukaj.Pionowo i jej zastosowania
• Czytelniejsze formuły - nazwy składników wzorów zamiast adresów
• Wstawianie tabeli ze strony internetowej - kwerenda WEB
• Niewielkie objętościowo zagadnienia obsługi Excel
     - rysowanie obramowań,
     - pasek stanu,
     - blokowanie okienek,
     - skrót klawiaurowy Alt + Enter,
     - karta developer na wstążce,
     - opcje ochrony formuł, struktury arkuszy, otwierania pliku,
     - rodzaje adresowania w Excelu, odwołania względne i bezwzględne,
     - skróty klawiaturowe w Excelu,
     - inspekcja formuł w Excelu - skrót Ctrl + ~,
     - wstawianie i formatowanie komentarzy,
     - szybkie przejście do właściwego arkusza w wieloarkuszowym skoroszycie,
  

Frazy wyszukiwarki / Google

• jak zmienić kolor komórki excel dla spełnionego warunku wartości
• jak w Excelu zrobić żeby warunek zmiana koloru tła
• jak zmienić kolor komórki dla spełnionego warunku wartości
• jak skrócić tabelę rat w zależnosci od długości okresu spłat
• imię i nazwisko w excelu jak rozdzielić
• jak zrobić dwie kolumny w jedną w excelu
• jak wprowadzić ilość stron na wydruku excel
• jak wprowadzić nazwę pliku na wydruku excel
• drukowanie z wielu arkuszy excel jednocześnie
• praca kilku osób na jednym pliku excel
• Jeżeli Oraz w Excelu
• Prosta lista rozwijana w Excelu
• licz.jeżeli dwa kryteria
• funkcja tablicowa excel
• jak wprowadzić tabliczkę mnożenia jedną formułą?
• edycja napisów w komórce excela
• włączenie makr w excel 2007
• znak $ w excelu

Spis porad Do góry podstrony Frazy Google 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,

- wybieram z menu Format/formatowanie warunkowe,

- W widocznym formularzu przyciskiem 'Formatuj' mogę dobrać parametry czcionki i tła dla poszczególnych parametrów,

- wybór koloru tła komórki,

- Wynik działania:

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)

Dla celów użytkowych warto jest pamiętać, że przyciskiem 'Usuń' mogę zlikwidować dany warunek.
Wynik działania:

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 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 - skala kolorów dla zaznaczonego obszaru.

Formatowanie warunkowe - przyklada dla 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 Frazy Google 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',

- 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.)

- Krok 2 (wybierany rodzaj ograniczników oddzielających przyszłe kolumny, przy czym można zaznaczyć więcej niż jedną opcję),

- Krok 3 (zaznaczmy format danych w kolumnie i miejsce docelowe - najprościej zaznaczyć kolumnę obok kolumny dzielonej),

- Wynik działania:

Łą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'.


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:

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.

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.

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ł.

Polecenie 'Tekst jako kolumny' dostępne jest w osobnym przycisku zakładki 'Dane'

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 Frazy Google 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'

- 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,

- 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),

- 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,

- 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:

Ustawiony przeze mnie nagłówek niestandardowy:

- 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.

Wynik działania:

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.

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'.

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.

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).

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 Frazy Google 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'

Pracę grupową kończymy używając na karcie arkusza prawego przycisku myszy i zaznaczając opcję 'Rozgrupowanie arkuszy'

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'

- 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).
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.

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 Frazy Google 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. Za pomocą właśnie tej opcji realizuję pracę grupową na programach: WYKUP GRUNTÓW, BAZA TELEADRESOWA i REJESTR WODOMIERZY.
- Z paska menu 'Narzędzia' wybieram polecenie 'Udostępnij skoroszyt',

- Na widocznym formularzu zaznaczam opcję 'Pozwalaj na zmiany wprowadzane jednocześnie przez wielu użytkowników'

- W drugiej zakładce mogę zaznaczyć opcje o długości historii zmian, wprowadzania konfliktowych zmian między użytkownikami i sposobu aktualizacji,

- 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

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'

- Na formularzu wybieram zakładkę 'Ochrona' i opcję 'Zablokuj'

- 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

- Na widocznym formularzu zaznaczam wybrane opcje (tzn. na co pozwalam w chronionych komórkach) i dwukrotnie potwierdzam hasło,

- Obecnie przy próbie zapisania do zabezpieczonego zakresu komórek wyświetlony zostanie odpowiedni komunikat:

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.

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 Frazy Google 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',

- 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,

- 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,

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 Frazy Google Do strony głównej

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

Na 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.

Uł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 Frazy Google 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)"

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")"

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.

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" 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" 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.




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" 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 Frazy Google 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:

Z menu 'Dane' wybieramy 'Sprawdzanie poprawności':

Teraz korzystamy z okna, które chciałbym nieco szerzej omowić:

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):

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?):

Korzystając z zakładki 'Alert o błędzie' wprowadzam styl 'Zatrzymaj' i odpowiedni komunikat:

Efekt dotychczasowych działań przy próbie wprowadzenia wartości spoza zakresu:


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



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:

Sprawdzenie 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:
- Podstrona 'Zagadnienia VBA' Formant ComboBox excela,
- Podstrona 'Programy od kuchni' Lista rozwijana ComboBox w VBA - dostosowywanie zawartości,
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 Frazy Google 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

Przykład 2 - zastosowanie formuły z odniesieniem kryterium do wartości w komórce

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).


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łą?

Do komórek wiersza 1 i kolumny A wprowadzamy cyfry od 1-10,


Zaznaczamy obszar w którym chcemy uzyskać formułę tablicową i w pasku formuł wprowadzamy wyrażenie: =A2:A11*B1:K1,


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',

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',

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',

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'

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

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)

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)


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 Frazy Google 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.

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.


A to efekt końcowy:

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"

oraz z menu "Dane" / "Sortuj"

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)

i wynik działania - kolumny B, C, D, E tabela przed sortowaniem, a w kolumnach G, H, I, J tabela po sortowaniu.

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 Frazy Google 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ę

- 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


- 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.


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

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 Frazy Google 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


W Excelu 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 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].

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 Frazy Google 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,


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 Frazy Google 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.

Tabelka przykładowa


Formuły z odwołaniami do adresów komórek


Formuły z odwołaniami do nazw składników


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


Dla wybranego zakresu wybieramy opcję wskazującą gdzie są nazwy


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 Frazy Google 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"

Krok 2 - W wyświetlonym formularzu podobnym do okna przeglądarki wpisujemy adres strony (standardowo widoczna jest strona startowa)


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"
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

Krok 5 - Na koniec zaznaczmy komórką startową wczytania tabeli.

Krok 6 - Widoczna wczytana tabela z formatowaniem

Krok 7 - Ta sama tabela 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":

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 Frazy Google 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,

- Mamy dostęp do paska narzędzi 'Obramowanie' z poleceniami wyboru rodzaju i koloru linii, wymazywania linii (gumki) i siatki obramowań

- Przykładowy efekt końcowy,

RYSOWANIE OBRAMOWAŃ W EXCEL 2007 - UWAGI
Opcje dostępne są między innymi z zakładki 'Narzędzia główne' na wstążce.

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).

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'

- 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'

- Gotowy efekt przy przewijaniu ekranu

- 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.

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.

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'

- 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ń.

- 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ą.

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 ;)

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

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

Teraz zakładka 'Popularne' i w 'Najczęściej używanych opcjach w pracy z programem Excel' stawiamy ptaszka w interesującej nas rubryce ;)

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:

Teraz w przycisku Narzędzi wybieramy pozycję Opcje ogólne

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.

Widok tabeli obliczeniowej

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.

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.


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):

zmienić rozmiar (kursor ustawia się na ramce tak by była widoczna strzałka zmiany rozmiaru):

Przez menu z prawego przycisku myszy mamy dostęp do formatowania wyglądu komentarza:


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:

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ąć

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.

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