Spis treści

Spis treści

Redakcja

Od Redakcji

Redakcja odpowiada

Edycja zawartości komórek dla zaawansowanych

Jak automatycznie otwierać skoroszyt?

Problem z sortowaniem dat w różnych formatach

Czy można automatycznie przewijać arkusz do danej pozycji na liście

Tematy publikacji w pełnej wersji

Redakcja

Redaktor prowadzący

Rafał Janus

Wydawca

Monika Kijok

Opracowanie graficzne okładki

Małgorzata Piaskowska

Opracowanie graficzne

Zbigniew Korzański

Koordynator produkcji

Mariusz Jezierski

Korekta

Zespół

ISBN 978–83–269–3195-6

Nakład: 1000 egz.

Wydawnictwo Wiedza i Praktyka sp. z .o.o.

03-918 Warszawa, ul. Łotewska 9a

Tel. 22 518 29 29, faks 22 617 60 10, e-mail: pgromulski@wip.pl

NIP: 526-19-92-256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy

XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

Skład i łamanie: Triograf Dariusz Kołacz

Miller Druk Sp. z o. o., 03-301 Warszawa, ul. Jagiellońska 82, tel.: 22 614 17 67

Copyright © by Wydawnictwo Wiedza i Praktyka sp. z o.o. Warszawa 2014

Poradnik „Excel w praktyce” wraz z przysługującym Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w poradniku „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Poradnik „Excel w praktyce” został przygotowany z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Excel w praktyce” lub w innych dostępntych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przykładów.

Wydawca nie odpowiada za treść zamieszczonej reklamy; ma prawo odmówić zamieszczenia reklamy, jeżeli jej treść lub forma są sprzeczne z linią programową bądź charakterem publikacji oraz interesem Wydawnictwa Wiedza i Praktyka.

Od Redakcji

Drodzy Czytelnicy!

Często zachodzi potrzeba, aby na jednym skoroszycie pracowało równocześnie wiele osób. Taki skoroszyt należy udostępnić innym użytkownikom, co umożliwi wspólną pracę. W przeciwnym wypadku próba otwarcia pliku już otwartego przez inną osobę skończy się ostrzegawczym komunikatem. Z artykułu „Ochrona arkusza – zezwolenie na edycję zakresów” dowiesz się, jak udostępniać skoroszyty; śledzić, akceptować bądź odrzucać zmiany w udostępnionym skoroszycie; chronić arkusz, skoroszyt lub zakres komórek; nadawać uprawnienia do zakresu komórek; zabezpieczać plik hasłem przed zmianami lub otwarciem.

Jeśli potrzebujemy sporządzić raport zawierający tabele z danymi źródłowymi oraz wykresy, powinniśmy zarezerwować dużo miejsca w arkuszu. Jeżeli dodatkowo chcemy mieć te dane w wersji drukowanej, to prawdopodobnie zużyjemy także sporo kartek papieru. Okazuje się, że możemy zoptymalizować swój raport i zastosować w arkuszu minidiagramy zamiast pełnowymiarowych wykresów. Pomocne wskazówki znajdziecie w tekście „Prezentacja graficzna – miniwykresy pozwolą zwizualizować wyniki cząstkowe analiz”. Miniwykresy są idealnym rozwiązaniem pozwalającym oszczędzić miejsce. Po odpowiednim rozplanowaniu obiektów w arkuszu może się okazać, że wszystkie elementy zmieszczą się na jednej kartce formatu A4 i do tego zachowają swoją czytelność.

Jeżeli arkusze będą wypełniać inne osoby, warto zabezpieczyć się przed niepoprawnym użytkowaniem arkusza. Do tego celu można zastosować narzędzie Excela o nazwie Sprawdzanie poprawności danych. Po nałożeniu na komórki odpowiednich reguł program będzie sam kontrolował, czy wszystkie rubryki są poprawnie wypełnione. Jeśli wpisana zostanie wartość niespełniająca kryteriów, wówczas program wyświetli ostrzeżenie. W tekście „Poprawność danych – Excel może kontrolować wypełnianie arkuszy” wyjaśniamy, jak zabrać się za to samodzielnie.

Podstawowym zadaniem kodów kreskowych jest bardzo szybka identyfikacja towaru. Do ich tworzenia wcale nie musisz wykorzystywać specjalistycznego oprogramowania. Także w Excelu można w prosty sposób zbudować kod paskowy na podstawie numeru EAN-13 (ang. European Article Number – Europejski Kod Towarowy). W artykule „Tworzenie kodów kreskowych w Excelu” przeczytasz, jak zaprojektować skoroszyt zawierający przygotowany do druku szablon etykiety z kodem paskowym, w którym można dowolnie dostosowywać kod numeryczny towaru. To wszystko bez użycia makr Visual Basic.

Zapraszam do lektury Rafał Janus

redaktor prowadzący poradnika „Excel w praktyce”(rjanus@wip.pl)

Redakcja odpowiada

EDYCJA ZAWARTOŚCI KOMÓREK DLA ZAAWANSOWANYCH

W jaki sposób mogę, po wpisaniu tekstu do komórki, kontynuować wpisywanie od nowego wiersza?

Jedną z metod na zmieszczenie i estetyczny wygląd dużej ilości tekstu w komórce jest podział na linijki. Odbywa się to za pomocą wciskania kombinacji klawiszy [ALT]+[ENTER], co powoduje rozpoczęcie wpisywania dalszego tekstu nadal w tej samej komórce, ale już w nowej linijce.

Nie zawsze jednak taki podział na wiersze jest pożądany, np. ze względu na zmianę sposobu wyświetlania komórek. Jeżeli więc otrzymaliśmy skoroszyt z dużą liczbą komórek wypełnionych z użyciem podziału na linie, ich masowa konwersja do komórek „jednolinijkowych” staje się koniecznością. Jak dokonać tego automatycznie?

Najlepszym rozwiązaniem jest skorzystanie z funkcji Znajdowanie i zamienianie ([CTRL] + [F]) i zamiana każdego wystąpienia znaku podziału linii na spację albo jego usunięcie. Po otwarciu tego okna należy przejść na zakładkę Zamień i w polu Znajdź wpisać znak podziału linii.

Rys. 1. Okno wyszukiwania tekstu

Znak podziału linii ma kod 010. Aby go wpisać, należy przytrzymać lewy klawisz [ALT] i z klawiatury numerycznej wybrać po kolei [0], [1] i [0]. Klawisz [NUMLOCK] powinien być wyłączony. Znak podziału linii będzie wyświetlany jako migająca kropka.

W polu Zamień na można wstawić pojedynczy znak spacji albo pozostawić je puste (w tym ostatnim przypadku znaki podziału linii zostaną po prostu usunięte).

Wybierając odpowiedni zakres w polu Wewnątrz (widoczne po rozwinięciu okienka przyciskiem Opcje), można zdecydować o tym, w których komórkach dokonać zmiany.

FORMATOWANIE I ZAZNACZANIE DANYCH

Przedstawimy teraz kilka sposobów na szybsze formatowanie i zaznaczanie danych.

Tabela 1. Formatowanie i zaznaczanie danych

Jeżeli chcemy…

to…

Zaznaczyć niesąsiadujące komórki lub zakresy

należy klikać poszczególne komórki, wciskając klawisz [CTRL]

Zaznaczyć tylko puste komórki w danym zakresie

należy zaznaczyć zakres komórek, na karcie Narzędzia główne kliknąć przycisk Znajdź i zaznacz, a w końcu opcję Przejdź do – specjalnie

Zaznaczyć duży zakres komórek

wybrać komórkę w rogu tego zakresu, a następnie (nie trzymając wciśniętego żadnego klawisza myszki) przewinąć arkusz do końca zakresu i kliknąć komórkę w miejscu z przytrzymanym klawiszem [SHIFT]

Dodać kolejne wartości liczby w komórkach (np. w kolumnie „Lp.”)

trzeba przeciągnąć uchwyt wypełnienia w rogu zaznaczenia, przytrzymując wciśnięty klawisz [CTRL]

Przenieść zawartość komórek bez konieczności ich kopiowania, wklejania i usuwania

powinniśmy zaznaczyć komórki, a następnie ustawić kursor na ich obramowaniu tak, aby zmienił się w strzałki skierowane w 4 strony; teraz wystarczy przeciągnąć obramowanie komórek do innej lokalizacji (jeżeli zrobimy to z wciśniętym klawiszem [CTRL], komórki zostaną skopiowane, a nie przeniesione).

Edytować kilka podobnych arkuszy jednocześnie

należy przytrzymać wciśnięty klawisz [CTRL] i kliknąć tytuły arkuszy (karty na dole ekranu), które chcemy edytować; zmiany wprowadzone na bieżącym arkuszu będą naniesione także na pozostałe zaznaczone arkusze

Jeżeli chcemy…

to…

Zablokować pierwszy wiersz z nagłówkiem tabeli w górnej części okna

w zakładce Widok należy kliknąć opcję Zablokuj okienka i wybrać Zablokuj górny wiersz

Zgrupować kolumny lub wiersze, umożliwiając ich rozwijanie i zwijanie

trzeba zaznaczyć kolumny lub wiersze, które mają zostać zwinięte, oraz na zakładce Dane wybrać polecenie Grupuj i jeszcze raz Grupuj…

Podliczać wartość komórek szybciej niż przez klikanie przycisku Autosumowanie

wystarczy użyć kombinacji klawiszy [ALT] oraz [=]

Przemnożyć wartości zakresu komórek przez 5

należy wpisać w pustej komórce „5” i ją skopiować; następnie zaznaczyć zakres komórek do przemnożenia i kliknąć je prawym klawiszem myszy; z menu Wklej specjalnie trzeba wybrać Wklej specjalnie… i w nowo otwartym oknie zaznaczyć Przemnóż oraz potwierdzić przyciskiem OK

JAK AUTOMATYCZNIE OTWIERAĆ SKOROSZYT?

Często podczas pracy z programem Excel korzystam z jednego i tego samego arkusza codziennie. Czy jest sposób na automatyczne otwieranie tego arkusza za każdym razem, gdy uruchamiam Excela?

Jest to możliwe. W tym celu gotowy plik .xls lub .xlsx należy skopiować do odpowiedniego katalogu:

l w Windows Vista: C:\Użytkownicy\\AppData\Local\Microsoft\Excel\XLStart;

l w Windows 7: C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART.

Pomiędzy podwójne slashe należy wpisać nazwę danego użytkownika.

Jeżeli nie chcemy przenosić pliku do tego katalogu (gdyż np. pracujemy na pliku współdzielonym w sieci), możemy utworzyć do niego skrót.

Automatyczne uruchamianie skoroszytu powoduje, że wraz z nim uruchamiane są wszystkie – automatyczne makra zarejestrowane w tym skoroszycie. Nie zawsze musi nam to odpowiadać. Aby zapobiec automatycznemu uruchamianiu makr podczas startu Excela, należy przytrzymać wciśnięty klawisz [SHIFT].

Wyłączenie automatycznego otwierania skoroszytu odbywa się oczywiście przez usunięcie pliku z katalogu XLStart lub XLSTART. Można jednak wyłączyć tę opcję także bez kasowania zawartości katalogu:

1. Kliknijmy przycisk Microsoft Office, a następnie wybierzmy pozycję Opcje programu Excel.

2. Z Zaawansowane w obszarze Ogólne należy znaleźć i odznaczyć opcję Przy uruchomieniu otwórz wszystkie pliki w oraz wcisnąć OK.

SZABLON NIESTANDARDOWY

Tworzenie nowych skoroszytów nierzadko polega na tworzeniu podobnie wyglądających zestawień danych. W takim przypadku nużące jest ustawianie za każdym razem takich samych parametrów czcionek czy obramowań komórek, opisywanie zawartości kolumn, zamieszczanie loga firmowego itp. Gdy Excel otrzymuje polecenie stworzenia nowego skoroszytu, robi to na podstawie wzoru pustego szablonu, zwanego Skoroszytem. Wystarczy istniejący plik .xls lub .xlsx zapisać jako Skoroszyt, aby nie zaczynać za każdym razem z pustą siatką komórek.

A zatem po kolei:

1. Przygotujmy skoroszyt, który od tej pory będzie wzorem dla nowych dokumentów.

2. Otwórzmy go w programie Excel, a następnie z menu Plik wybierzmy Zapisz jako.

3. W polu Nazwa pliku wpiszmy dowolną nazwę wzoru.

4. W polu Zapisz jako typ wybierzmy Szablon programu Excel albo – jeżeli skoroszyt zawiera makra – Szablon programu Excel z obsługą makr.

5. Wciśnijmy Zapisz.

Plik zostanie zapisany w folderze C:\Users\\AppData\Roaming\Microsoft\Excel\Szablony. Jeżeli katalogu Szablony nie ma, należy go utworzyć.

Może się także okazać, że katalog wskazany wyżej nie jest właściwy dla Szablonów w Twojej wersji MS Office. Aby odczytać poprawną ścieżkę, należy wcisnąć przycisk Microsoft Office, a następnie wybrać pozycję Opcje programu Excel. Na zakładce Centrum Zaufania wciśnijmy przycisk Ustawienia Centrum Zaufania. Teraz w obszarze Zaufane lokalizacje należy znaleźć katalog dla szablonów użytkownika (może to być np. C:\Users\\AppData\Roaming\Microsoft\Templates).

Aby z niego skorzystać przy tworzeniu nowego skoroszytu:

1. Z menu Plik wybierzmy opcję Nowy.

2. W obszarze Dostępne szablony wybierzmy pozycję Moje szablony i na karcie Szablony osobiste kliknijmy dwukrotnie szablon, którego chcemy użyć.

OTWIERANIE KILKU SKOROSZYTÓW NA RAZ

Przy pracy z wieloma skoroszytami ich otwieranie z osobna potrafi nadwerężyć naszą cierpliwość. Dlatego też warto zaznaczyć kilka plików .xls lub .xlsx w folderze, a następnie kliknąć je prawym klawiszem myszy i wybrać opcję Otwórz. Spowoduje to jednoczesne otwarcie tych skoroszytów w Excelu.

PROBLEM Z SORTOWANIEM DAT W RÓŻNYCH FORMATACH

Jak można posortować kolumnę zawierającą daty, jeśli część z nich jest w normalnym zapisie dat rozpoznanym przez Excela, natomiast pozostała część jest zapisana rzymskimi znakami, np. 2009-06-30 oraz 1.IV.2009.

Szkoda, że Czytelnik nie przedstawił przykładowego arkusza, w którym znajdowałyby się taki przypadek. W arkuszu mogą znajdować się komórki, które zawierają wartość liczbową, czyli datę, i są odpowiednio sformatowane. Ale mogą znajdować się także komórki, które zawierają wartość tekstową, mimo że niektóre z nich wyglądają tak, jakby zawierały sformatowaną datę.

W rozwiązaniu przyjęliśmy, że część komórek zawiera sformatowaną datę według formatu dd-MM-rrrr, a druga część komórek zawiera wartość tekstową z miesiącem określonym rzymskimi liczbami. Przyjęliśmy również, że wszystkie wartości tekstowe zawierają taki sam format wpisu, tj. jedną lub dwie cyfry dnia, kropkę, miesiąc w formie liter rzymskich, kropkę i czterocyfrowy rok.

Jeśli komórki zawierają dane mieszane (wartości liczbowe są przemieszane z wartościami tekstowymi) lub wszystkie daty są wartościami tekstowymi, to nie da się posortować takiego zestawienia według dat. Najpierw trzeba te wartości tekstowe skonwertować na wartości liczbowe. Najprostszym sposobem konwersji jest zaznaczenie pojedynczej kolumny danych zawierającej wartości tekstowe, które mają być transponowane na daty i na karcie Dane w grupie poleceń „Narzędzia danych” wybranie polecenia „Tekst jako kolumny” (w Excel 2003: wybieramy polecenie menu Dane//„Tekst jako kolumny”).

Następnie w trzecim kroku konwersji danych w sekcji „Format danych w kolumnie” zaznaczamy opcję Data i wybieramy kolejność elementów daty, w jakiej pokazywane są daty przed konwersją, a potem wybieramy przycisk OK. W przypadku liczb rzymskich nie jest możliwa konwersja w ten sposób. Należy w tym przypadku użyć bardziej skomplikowanej metody z użyciem odpowiednich formuł.

W tym celu:

1. W innym arkuszu lub pustej przestrzeni arkusza z dala od tabeli zawierającej daty wypisujemy tabelę konwersji liczb rzymskich na liczby arabskie (rysunek 1).

Rys. 2. Konwersja liczb rzymskich na arabskie

2. W tabeli zawierającej daty dodajemy nową kolumnę, w której za pomocą odpowiedniej formuły skonwertujemy wartości tekstowe na daty.

3. Na końcu możemy posortować zestawienie, używając do tego wartości wpisanych do tej nowej kolumny lub skopiować wartości z tej kolumny i wkleić je jako wartości do komórki zawierającej mieszane dane z datami.

CZY MOŻNA AUTOMATYCZNIE PRZEWIJAĆ ARKUSZ DO DANEJ POZYCJI NA LIŚCIE

Mam arkusz zamówień, w którym za pomocą polecenia Zablokuj okienka są zablokowane pierwsze cztery wiersze. Chciałbym, aby po wpisaniu do komórki C2 numeru zamówienia arkusz przewinął się w taki sposób, żeby szczegóły podanego zamówienia znajdowały się tuż pod zablokowanymi wierszami. W jaki sposób to zrobić?

Rys. 3. Arkusz zamówień z zablokowanymi wierszami

Powinniśmy użyć odpowiedniego kodu Visual Basic wpisanego do procedury zdarzenia uruchamiającego się przy zmianie wartości komórki w arkuszu.

W tym celu:

1. Klikamy prawym przyciskiem myszy kartę arkusza zamówień i z menu podręcznego wybieramy polecenie Wyświetl kod.

2. Otworzy się Edytor Visual Basic, a w nim okno modułu arkusza. Wpisujemy do niego kod przedstawiony na rysunku 2.

Rys. 4. Kod procedury Worksheet_Change

Na początku kodu sprawdzamy, czy zmieniła się wartość w komórce C2. Jeśli nie, procedura kończy działanie. W celu przesunięcia arkusza należy zmienić wartość właściwości ScrollRow aktywnego okna i ustawić ją na numer wiersza, który ma być pokazywany na samej górze. W procedurze wykorzystujemy metodę Match obiektu WorksheetFunction (odpowiednik funkcji arkusza PODAJEMY.POZYCJĘ), która zwraca numer wiersza komórki w kolumnie A zawierającej szukany numer zamówienia.

3. Zamykamy Edytor Visual Basic.

Teraz po wpisaniu do komórki C2 numeru zamówienia arkusz automatycznie zostanie przewinięty w taki sposób, aby wiersz z wskazanym zamówieniem znajdował się bezpośrednio pod 4. wierszem. Po wyczyszczeniu zawartości komórki C2 arkusz zostanie przewinięty na samą górę.

Rys. 5. Przewinięcie arkusza po zmianie komórki C2

Po wpisaniu do komórki C2 wartości, która nie znajduje się w kolumnie A, wystąpi błąd w procedurze. Aby się przed nim uchronić, możemy na początku kodu, tuż po deklaracji zmiennej numer, wpisać linię wyłączającą obsługę błędu: On Error Resume Next.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Redakcja odpowiada

Edycja zawartości komórek dla zaawansowanych

Jak automatycznie otwierać skoroszyt?

Problem z sortowaniem dat w różnych formatach

Czy można automatycznie przewijać arkusz do danej pozycji na liście

Co zrobić, gdy Excel zaznacza za dużo komórek

Co zrobić, aby wyświetlić dane z arkusza wskazanego w komórce

Jak wyznaczyć nadgodziny na podstawie realnego czasu pracy

Triki

Jak ułatwić sobie przewijanie dużych zestawień

Konsolidowanie danych z wielu arkuszy

Pokazywanie nazw argumentów funkcji na pasku formuły

Ochrona arkusza – zezwolenie na edycję zakresów

Prezentacja graficzna – miniwykresy pozwolązwizualizować wyniki cząstkowe analiz

Poprawność danych – Excel może kontrolować wypełnianie arkusz

Tworzenie kodów kreskowych w Excelu