PRZYDATNE Triki w EXELU -Numery telefonow w czytelnym formacie.docx

Trik 1
Numery telefonów w czytelnym formacie

Otrzymałeś od swojego współpracownika wykaz klientów z ich danymi osobowymi. Znajdują się w nim także numery telefonów stacjonarnych, ale niestety wyświetlane w mało przejrzysty sposób. Zera przed numerami kierunkowymi zostały usunięte, a ponadto dużo łatwiej byłoby wybierać numer w aparacie, gdyby grupy cyfr zostały oddzielone myślnikiem. Aby dostosować sposób wyświetlania numerów, użyj formatu niestandardowego.


Rys. 1. Numery w nieczytelnym formacie

W tym celu:
1. Zaznacz komórki B2:B7 i z menu Format wybierz polecenie Komórki. Możesz także skorzystać z kombinacji klawiszy Ctrl + 1.
2. W wyświetlonym oknie przejdź do zakładki Liczby i zaznacz kategorię Niestandardowe.
3. W polu Typ wpisz taki format:

(0##) ###-##-##

4. Zatwierdź, klikając OK. Format numerów został zmieniony, jak to przedstawia rysunek 2.


Rys. 2. Poprawiony format numerów telefonów

Trik 2
Szybkie rozdzielenie imienia i nazwiska zapisanych w jednej komórce

Jeżeli imiona i nazwiska klientów są wpisane w jednej kolumnie, to nie da się ich ułożyć w kolejności alfabetycznej. Poznaj sprytną formułę, która błyskawicznie rozdzieli dane do osobnych kolumn

Twój współpracownik prowadzi rejestr osobowy klientów indywidualnych. Poprosiłeś go o przysłanie grupy klientów mieszkających w Krakowie. Chciałeś ułożyć ich w kolejności alfabetycznej, ale pojawił się problem. Imiona i nazwiska zostały wprowadzone do jednej komórki. O posortowaniu takiego zestawienia nie może być mowy. Czy czeka Cię ręczne rozdzielanie wpisów? Niekoniecznie! Zastosuj dwie sprytne formuły.

Rys. 1. Imię i nazwisko zapisane w jednej komórce

W pierwszej kolejności przenieś do kolumny B imiona klientów.

=> W tym celu:
1. Wprowadź do komórki B2 następującą formułę:

=LEWY(A2;ZNAJDŹ(" ";A2)-1)

2. Skopiuj ją poniżej, klikając dwukrotnie prawy dolny róg komórki B2 - tzw. uchwyt wypełnienia.

Wyjaśnienie działania formuły:
Funkcja LEWY zwraca zadaną liczbę znaków w danym tekście (w komórce A2) od lewej strony, czyli w naszym przykładzie zwraca imię. Za pomocą funkcji ZNAJDŹ odnajdujesz spację między imieniem i nazwiskiem. Aby nie skopiować jej do nowej komórki, od wartości formuły ZNAJDŹ(" ";A2) musisz odjąć jedynkę.

Teraz do komórek kolumny C przenieś same nazwiska.

=> W tym celu:
1. Do komórki C2 wpisz taką formułę:

=PRAWY(A2;DŁ(A2)-ZNAJDŹ(" ";A2))

2. Także skopiuj ją poniżej.

Wyjaśnienie działania formuły:
Funkcja PRAWY wydziela fragment tekstu z komórki A2, zaczynając pobieranie kolejnych znaków od prawej strony. Formuła DŁ(A2)-ZNAJDŹ(" ";A2) określa, ile znaków ma być pobranych:
- DŁ(A2) - określa liczbę liter w komórce A2,
- ZNAJDŹ(" ";A2) - zwraca wartość 5, bo na tej pozycji w komórce A2 znajduje się spacja, licząc od lewej strony,
- DŁ(A2)-ZNAJDŹ(" ";A2) - określa liczbę liter w nazwisku. W naszym przykładzie jest to 6 znaków.

Rys. 2. Imiona i nazwiska rozdzielone do osobnych komórek

Przedstawione formuły możesz zastosować w dowolnych zestawieniach, w których chcesz automatycznie rozdzielić wartości wprowadzone do jednej komórki. Wystarczy, że dostosujesz odwołania w formule i gotowe.

Trik 3
Sprawne i bezproblemowe importowanie plików tekstowych

Przeniosłeś do arkusza dane z programu obsługującego działalność Twojej firmy. Niestety teksty są ze sobą połączone w komórkach, a niektóre wartości liczbowe mają niepoprawny format. Najprawdopodobniej popełniłeś błędy w tra kcie importowania pliku tekstowego. Zobacz, jak się ich wystrzegać

Z systemu transakcyjnego firmy wyeksportowałeś dane do pliku tekstowego. Chcesz je teraz przeanalizować w Excelu. W trakcie przenoszenia danych do arkusza może się zdarzyć, że Excel niepoprawnie rozpozna niektóre wartości i będziesz zmuszony je ręcznie zmieniać. Jeśli takich danych będzie dużo, to niepotrzebnie stracisz czas. Powinieneś zatem dowiedzieć się, w jaki sposób sprawnie importować do arkusza wartości zgromadzone w pliku tekstowym.

Przykładowy plik tekstowy przedstawia rysunek 1.

Rys. 1. Dane tekstowe, które mają być analizowane w arkuszu

=> Aby poprawnie przenieść dane do Excela, wykonaj następujące czynności:
1. Z menu Plik wybierz polecenie Otwórz (ew. użyj skrótu klawiaturowego Ctrl + O).
2. Na dole okna, które się pojawi, z rozwijanej listy Pliki typu wybierz Pliki tekstowe.
3. Odszukaj i zaznacz plik tekstowy i kliknij przycisk Otwórz. Zostanie wyświetlone okno Kreator importu tekstu - krok 1 z 3. W jego dolnej części znajduje się podgląd importowanych danych.
4. Zaznacz w nim pole Rozdzielany, a także określ numer wiersza, od którego chcesz rozpocząć importowanie danych.

Rys. 2. Krok 1 Kreatora importu tekstu

5. Kliknij przycisk Dalej.
6. W kolejnym oknie określ rodzaj separatora. W przykładzie jest to tabulator.

Wskazówka
Jeżeli wybierzesz opcje Kolejne ograniczniki traktuj jako jeden, wówczas dwa kolejno po sobie następujące separatory traktowane będą jak jeden.

Rys. 3. Określenie rodzaju separatora

7. Kliknij przycisk Dalej, aby przejść do kolejnego kroku.
8. W trzecim kroku Kreatora określ, jakiego typu dane zawierają poszczególne kolumny. Wystarczy, że w ramce Podgląd danych zaznaczysz odpowiednią kolumnę i określisz format danych. Na przykład dla kolumny z datami powinieneś określić, w jakim formacie została zapisana w pliku tekstowym data. Jest to ważne, jeżeli format daty jest inny niż ten, który jest ustawiony w systemie na Twoim komputerze. W naszym pliku tekstowym daty są w formacie rok, miesiąc, dzień.

Rys. 4. Ostatni krok Kreatora

9. Kliknij przycisk Zakończ. Uzyskasz efekt jak na rysunku 5.

Rys. 5. Dane gotowe do analizy

W szybki sposób zaimportowałeś do arkusza dane zawarte w pliku tekstowym. W trakcie importowania swoich zestawień zwracaj szczególną uwagę na to, aby poprawnie określić format danych w poszczególnych kolumnach.

Trik 4
Szybkie przenoszenie formatu między komórkami

Chcesz ujednolicić format komórek w całym zestawieniu i uniknąć klikania kolejnych ikonek na pasku narzędziowy Przyjmijmy, że prowadzisz rejestr, w którym wpisujesz dane do kolejnych wierszy. Z innego arkusza skopiowałeś określony fragment danych i dokleiłeś je do jednej z kolumn swojego rejestru. Operacja przebiegła poprawnie i chcesz teraz przenieść formatowanie używane w rejestrze na nowo dodane komórki. Czy musisz wybierać kolejne ikony na pasku narzędzi, aby ujednolicić formatowanie? Niekoniecznie, są szybsze sposoby.

Rys. 1. Nowe dane, na które chcesz przenieść formatowanie

Malarz formatów

Jednym ze sposobów jest skorzystanie z malarza formatów, którego ikona jest widoczna na pasku narzędziowym.

Rys. 2. Malarz formatów

=> Aby przenieść format:
1. Zaznacz jedną z komórek bazowych (sformatowanych) i kliknij ikonę malarza formatów.
2. Komórka zostanie otoczona przerywanym obramowaniem. Zaznacz zakres docelowy - w przykładzie C7:C10.

Po zwolnieniu myszy format zostanie przeniesiony jak to przedstawia rysunek 3.

Rys. 3. Format został przeniesiony

Polecenie Wklej specjalnie

Innym sposobem jest użycie polecenia Wklej specjalnie, które umożliwia wklejanie tylko niektórych elementów.

=> Aby z niego skorzystać:
1. Zaznacz komórkę bazową i wciśnij kombinację klawiszy Ctrl + C.
2. Zaznacz komórki docelowe i z menu Edycja wybierz polecenie Wklej specjalnie.
3. W oknie dialogowym, które się pojawiło, zaznacz pole Formaty i zatwierdź, klikając OK.

Rys. 4. Wklejanie formatów

Korzystając z tego sposobu, także uzyskałeś oczekiwany efekt: błyskawiczne przeniesienie formatów między komórkami.

Trik 5
Wykres z nałożoną linią obrazującą wartość średnią

Porównanie osiągniętych wyników z wielkością przeciętną to jedna z częściej przygotowywanych prezentacji. Sprawdź, jak szybko stworzyć czytelny wykres tego typu

Analizujesz wyniki handlowców osiągnięte w minionym kwartale. Potrzebujesz przedstawić je na wykresie. Na prezentacji powinna się także znaleźć linia obrazująca przeciętny utarg handlowca, aby szybko sprawdzić, którzy pracują poniżej normy. Szybka rozbudowa tabeli źródłowej i nieznaczna zmiana właściwości wykresu pozwolą uzyskać taką prezentację.

Przykładowe dane przedstawia rysunek 1.

Rys. 1. Dane źródłowe

Rozbudowa tabeli źródłowej

W pierwszej kolejności dodaj do tabeli kolumnę, w której znajdą się wartości określające średni utarg.

=> W tym celu:
1. Do komórki C2 wprowadź następującą formułę:

=ŚREDNIA($B$2:$B$8)

2. Skopiuj ją do komórek poniżej.

Przygotowanie wykresu

Możesz już przystąpić do zbudowania wykresu i nałożenia linii obrazującej średni utarg.

=> Aby to zrobić:
1. Zaznacz zakres komórek A2:C8, uruchom kreator wykresów i wybierz typ wykresu Kolumnowy.
2. Usuń poziome linie siatki osi wartości i usuń kolor dla obszaru kreślenia (domyślnie szary). (Kliknij prawym przyciskiem myszy każdy z tych elementów i z menu podręcznego wybierz polecenie Wyczyść).
3. Na gotowym wykresie kliknij prawym przyciskiem myszy kolumnę z danymi reprezentującą średnią i z menu podręcznego wybierz polecenie Typ wykresu.

Rys. 2. Zaznaczona seria danych dla średniej

4. Wybierz z listy XY (Punktowy) i zatwierdź klikając OK.
5. Kliknij dwukrotnie myszą jeden z punktów poziomego wykresu XY (Punktowy). Otworzy się okno dialogowe Formatowanie serii danych.
6. W zakładce Desenie, w polach Linia i Znacznik zaznacz opcję Brak.
7. Przejdź do zakładki Słupki błędów X i w polu Wyświetl zaznacz opcję Razem.

Rys. 3. Formatowanie serii danych

Po zatwierdzeniu OK uzyskasz oczekiwany efekt jak na rysunku 4.

Rys. 4. Wykres z linią średnią

Wskazówka
Jeśli chcesz bardziej uwydatnić poziomą linię, kliknij ją prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Formatuj słupki błędów. W zakładce Desenie znajdziesz potrzebne opcje formatowania.