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-3392-9

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: rjanus@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: Raster studio, Norbert Bogajczyk

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ępnych 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!

Od zeszłego roku na rynku dostępna jest najnowsza wersja Excel 2013. Jest ona niejako kontynuacją wcześniejszych wersji Excel 2007 i Excel 2010. Wprowadzono w niej wiele udogodnień i nowych funkcjonalności. Pierwsze, co rzuca się w oczy po otwarciu programu Excel, to nowy wygląd. W programie znalazło się też wiele nowych funkcji, które pozwolą bardziej przystępnie zaprezentować dane. W tej i kilku następnych aktualizacjach przedstawimy najważniejsze zmiany, które zostały wprowadzone w tej wersji Excela. Cykl rozpoczynamy artykułem „Excel 2013 – rozpoczęcie pracy z najnowszą wersją programu” E 041.

W Excelu można graficznie przedstawiać dane w estetycznej i przejrzystej formie. Nie wszyscy jednak wiedzą, że oferuje on również narzędzia do tworzenia prostych schematów. Jeśli np. potrzebujemy zbudować schemat organizacyjny przedsiębiorstwa albo wyniki analizy chcemy uatrakcyjnić efektownym diagramem blokowym, wszystkie potrzebne funkcje znajdziemy w Excelu. Mowa o SmartArt, narzędziu usprawniającemu tworzenie takich elementów. Dzięki niemu zaledwie kilkoma kliknięciami myszy można zbudować bardzo efektowny obiekt graficzny, który z dużą łatwością da się dostosować zarówno pod względem estetyki, jak i przejrzystości przekazu informacji. Przykład zastosowania SmartArt zawiera artykuł „Budowanie diagramów blokowych” B 002. Z kolei w artykule „Graficzna prezentacja struktury sprzedaży na wykresie bąbelkowym” G 001 przyglądamy się, jak w procesie budowania strategii sprzedażowej wykorzystać trzy parametry, a uzyskane wyniki przedstawić w czytelnej formie.

Zaletą Excela są ogromne możliwości automatyzacji pracy. Typowym problemem, z którym mierzą się użytkownicy, jest łączenie danych z kilku zestawień. Aby uzyskać kompletne zestawienie, należy ze sobą połączyć listy cząstkowe. W artykule „Szybkie łączenie kilku list w jedną” S 048 wyjaśniamy, w jaki sposób zrobić to bez konieczności żmudnego, ręcznego przeklejania danych. Kolejna typowa sytuacja to posortowanie danych do wykresów, dzięki czemu graficzna prezentacja staje się bardziej czytelna. Autorka artykułu „Sortowanie danych do wykresu bez użycia makr” S 047 wyjaśnia, jak zautomatyzować tę czynność: uporządkować dane i posortować je po wartościach.

Zapraszam do lektury

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

Redakcja odpowiada

Sprawdźmy, z jakimi problemami borykają się inni użytkownicy Excela. Te rozwiązania mogą się przydać!

Co zrobić, gdy Excel niepoprawnie rozpoznaje daty wpisywane do arkusza?

Wraz ze zmianą stanowiska pracy otrzymałem nowy komputer. Otworzyłem Excela i próbowałem wprowadzić do komórki datę. Niestety, Excel niepoprawnie ją rozpoznał, gdyż została wyrównana do lewej, co świadczy o tym, że jest kwalifikowana jako wartość tekstowa. Domyśliłem się, że datę wprowadziłem w niepoprawnym formacie. Gdzie mogę sprawdzić zapis dat, który będzie rozpoznawany przez Excela?

Domyślny format daty akceptowany przez Excela nie jest uzależniony od ustawień programu, ale od całego systemu operacyjnego zainstalowanego w komputerze.

Aby sprawdzić format daty lub zmienić jego ustawienia, należy:

1. Uruchomić panel sterowania i dwukrotnie kliknąć ikonę Ustawienia regionalne (w systemie Windows XP Professional nosi nazwę Opcje regionalne i językowe).

2. W oknie dialogowym, które się pojawiło, przejść do zakładki Data. Jeżeli pracuje Pan w systemie Windows XP, proszę wybrać uprzednio przycisk Dostosuj.

Rys. 1. Systemowy format daty

Jeżeli domyślne ustawienia dat nam nie odpowiadają, trzeba wybrać z rozwijanych list określone formaty i separatory, a następnie zatwierdzić OK. Od tego momentu będzie można je stosować w swoim arkuszu kalkulacyjnym.

Jak obliczać nadgodziny na podstawie rzeczywistego czasu pracy?

Mam arkusz zawierający rzeczywiste godziny rozpoczęcia i zakończenia pracy osób zatrudnionych w mojej firmie. W jaki sposób mogę obliczyć, ile godzin i minut ponad nominalny czas pracy zostało przepracowanych w danym dniu przez konkretnego pracownika?

Liczby w komórkach sformatowane jako czas można dodawać i odejmować. Warto jednak pamiętać o tym, że jeden dzień odpowiada liczbie 1, a godzina – ułamkowi 1/24 równemu liczbie 0,4166667. Wynikiem odejmowania dwóch komórek sformatowanych jako czas może zostać liczba ujemna. W takim przypadku komórka sformatowana jako czas będzie wyświetlała błąd, który musimy przewidzieć. Ponieważ problem stanowi obliczenie tylko nadmiarowego czasu pracy, można skorzystać z funkcji MAX i dla ujemnego wyniku odejmowania wstawiać wartość 0.

Rys. 1. Pracownicy firmy i rzeczywisty czas ich pracy

Aby to zrobić:

1. W komórce G2 wpisujemy formułę:

=MAX(E2-F2;0)

2. Kopiujemy ją do komórek niżej.

3. Jeśli komórki nie zmienią formatu liczbowego na format daty, to zaznaczamy je, klikamy prawym przyciskiem myszy, wybieramy polecenie menu Formatuj komórki, przechodzimy do zakładki Liczby, zaznaczamy kategorię Niestandardowe i w polu Typ wpisujemy:

gg:mm

Rys. 2. Formatowanie komórek poprzez ustawienie niestandardowego formatu daty

a następnie klikamy przycisk OK, aby zatwierdzić tę zmianę.

Ponieważ nie wszystkie możliwe formaty czasu znajdują się w kategorii Czas, można używać specjalnych kodów formatowania liczbowego wpisywanych w polu Typ, w kategorii Niestandardowe. Litera g oznacza godziny, m – minuty, a s – sekundy. Przy czym wpisanie dwóch takich samych liter pod rząd spowoduje wyświetlenie tej jednostki czasu zawsze w postaci dwucyfrowej.

4. Jeśli chcemy, aby wartości 0 w komórkach nie były pokazywane, to wybieramy polecenie menu Narzędzia/Opcje i w zakładce Widok opcję Wartości zerowe zmieniamy na niezaznaczoną. Jednak gdyby się okazało, że w pewnych obszarach arkusza wyświetlanie wartości zerowych jest potrzebne, to ich ukrywanie można ograniczyć tylko do obszaru pokazującego przekroczony czas pracy. Modyfikujemy wówczas format w następujący sposób:

gg:mm;;

Rys. 3. Arkusz z wyświetloną liczbą godzin nadmiarowych

Jak automatycznie oznaczać na billingu prywatne rozmowy pracowników?

Co miesiąc dostaję miesięczne billingi telefonów służbowych pracowników, które umieszczam w arkuszu Excela. Pracownicy wcześniej podali numery prywatnych telefonów, pod które mogą dzwonić ze swoich służbowych komórek. W arkuszu o nazwie Lista stworzyłem tabelę z parami telefonów traktowanych jako prywatne. Chciałbym, aby w billingu w ostatniej kolumnie została wyświetlona informacja, czy dana rozmowa jest prywatna, czy nie. W jaki sposób mogę stworzyć taką formułę, która porównywałaby pary telefonów w billingu z tabelą telefonów prywatnych?

Aby to wykonać:

1. W komórce F2 wpisz następującą formułę tablicową:

=JEŻELI(SUMA((Lista!$A$2:$A$30=B2) *(Lista !$B$2:$B$30=D2))>0;”TAK”;””)

Formułę tablicową zatwierdza się, naciskając jednocześnie klawisze Ctrl, Shift i Enter. Poprawnie wprowadzona zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły.

Rys. 1. Lista par telefonów wskazująca, które rozmowy są traktowane jako prywatne

Rys. 2. Miesięczny billing rozmów

Wyjaśnienie działania formuły:

Formuła ta sprawdza każdą komórkę w zakresie Lista!$A$2:$A$30 i porównuje ją z zawartością komórki B2, co w wyniku daje wartości logiczne PRAWDA lub FAŁSZ. Każda z nich jest mnożona przez odpowiadający jej wynik porównania komórek w zakresie Lista!$B$2:$B$30 z zawartością komórki D2. Przy mnożeniu wartości logicznych wartość PRAWDA jest zamieniana na liczbę 1, a wartość FAŁSZ na 0. W rezultacie do funkcji SUMA zostanie przekazana tablica wartości 0 i 1, gdzie 1 wystąpi, jedynie w przypadku gdy jednocześnie wyniki porównań w obu kolumnach dadzą wynik PRAWDA. A zatem jeśli choć w jednym wierszu arkusza Lista wartości w kolumnach A i B będą zgadzały się z zawartością komórek B2 i D2, to wynik funkcji SUMA będzie większy od 0 i formuła wyświetli tekst TAK. W przeciwnym razie wstawi pusty ciąg znaków.

Jeśli w arkuszu Lista mamy więcej par telefonów, to zwiększamy zakresy odwołujące się do tego arkusza, pamiętając, że oba zakresy muszą obejmować dokładnie taką samą liczbę wierszy. Możemy wpisać zakres większy niż liczba wypełnionych wierszy, jednak starajmy się wtedy nie przesadzić, ponieważ obliczenia formuł tablicowych trwają o wiele dłużej niż zwykłych. A to może spowodować utrudnienia przy korzystaniu z całego skoroszytu.

2. Zaznaczamy komórkę F2 i najeżdżamy myszą na dolny prawy róg komórki. Gdy kursor myszy zmieni się w czarny krzyżyk, to klikamy i przeciągamy zawartość komórki F2 do komórek poniżej. Formuła zostanie skopiowana do zaznaczonych komórek.

Rys. 3. Miesięczny billing rozmów po wypełnieniu ostatniej kolumny danych formułą tablicową

Pary telefonów, które znajdują się na liście rozmów prywatnych, zostaną oznaczone tekstem TAK w kolumnie F.

Jak dodać do siebie cyfry dowolnej liczby?

Interesuję się numerologią i w związku z tym mam pytanie: czy można w Excelu stworzyć formułę, która zwróci sumę cyfr liczby wielocyfrowej albo nawet sumę cyfr daty?

Aby obliczyć sumę cyfr liczby wielocyfrowej, należy użyć funkcji tablicowej, która wyodrębni poszczególne cyfry liczby, a następnie je zsumuje. W tym celu w komórce wynikowej wpisujemy następującą formułę tablicową:

=SUMA(WARTOŚĆ(FRAGMENT.TEKSTU(B4;WIERSZ(ADR.POŚR(”1:”&DŁ(B4)));1)))

Warto pamiętać, że formuły tablicowe zatwierdza się, naciskając jednocześnie klawisze Ctrl, Shift i Enter. Po poprawnym wprowadzeniu formuła zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły.

Rys. 1. Formuła tablicowa obliczająca sumę cyfr liczby wielocyfrowej

Wyjaśnienie działania formuły:

W przedstawionej formule zastosowano funkcję WIERSZ jako drugi argument funkcji FRAGMENT.TEKSTU. W połączeniu z funkcją ADR.POŚR zwraca ona tablicę liczb, począwszy od 1 do liczby równej liczbie znaków w komórce B4. Poszczególne znaki zwracane przez funkcję FRAGMENT.TEKSTU są zamieniane na wartości liczbowe dzięki użyciu funkcji WARTOŚĆ, a następnie zsumowane.

Jeśli w komórce wpiszemy tekst, w którym będą znajdowały się inne znaki niż cyfry, to formuła zwróci błąd.

Jeśli chcemy obliczać sumę cyfr daty, należy zastosować nieco inną formułę tablicową:

=SUMA(WARTOŚĆ(FRAGMENT.TEKSTU(PODSTAW(B4;”-”;””);WIERSZ(ADR.POŚR(”1:”&DŁ(PODSTAW(B4;”-”;””))));1)))

Data musi być poprzedzona apostrofem, aby była traktowana przez Excela jako tekst. Jeśli to niemożliwe, musimy dodatkowo użyć w formule funkcji TEKST, która dokona takiej zmiany. Za jej pomocą możemy wtedy tak sformatować tekst, że użycie funkcji PODSTAW będzie niepotrzebne:

=SUMA(WARTOŚĆ(FRAGMENT.TEKSTU(TEKST(B4;”ddmmrrrr”);WIERSZ(ADR.POŚR(”1:”&DŁ(TEKST(B4;”ddmmrrrr”))));1)))

Rys. 2. Formuła tablicowa sumująca cyfry daty

W przedstawionej formule zastosowano dodatkowo funkcję PODSTAW, która z tekstu znajdującego się w komórce B4 usuwa wszystkie wystąpienia myślnika.

Jak wyświetlać dane z arkusza, którego nazwa znajduje się w komórce?

W skoroszycie wypisuję informacje dotyczące zamówień. Każde z nich znajduje się w osobnym arkuszu, którego nazwa odpowiada numerowi zamówienia. Ostatni z nich nosi nazwę Podsumowanie i chciałbym w nim wyświetlać najważniejsze dane dotyczące określonego zamówienia (po wprowadzeniu numeru zamówienia do komórki B3). W jaki sposób mogę otrzymać wartość dowolnej komórki z innego arkusza, gdy jego nazwa znajduje się w innej komórce? Czy można również dołączać inne formuły, np. wyświetlającą liczbę pozycji w danym zamówieniu?

W tym celu:

1. W komórce B5 wpisujemy formułę:

2. =ADR.POŚR(B3&″!B4″)

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Redakcja odpowiada

Co zrobić, gdy Excel niepoprawnie rozpoznaje daty wpisywane do arkusza?

Jak obliczać nadgodziny na podstawie rzeczywistego czasu pracy?

Jak automatycznie oznaczać na billingu prywatne rozmowy pracowników?

Jak dodać do siebie cyfry dowolnej liczby?

Jak wyświetlać dane z arkusza, którego nazwa znajduje się w komórce?

Triki

Opisowe komunikaty o błędach

Automatyczna zmiana daty na ostatni poniedziałek

Generowanie liczb losowych

Interpretacja braku danych na wykresie

Wyodrębnianie liczb z dłuższego wpisu w jednej komórce

Podgląd działania formuły krok po kroku

Budowanie diagramów blokowych

Excel 2013 – rozpoczęcie pracy z najnowszą wersją programu

Graficzna prezentacja struktury sprzedaży na wykresie bąbelkowym

Manipulowanie linią trendu na wykresach

Sortowanie danych do wykresu bez użycia makr

Szybkie łączenie kilku list w jedną