Kontrola poprawności wprowadzanych dat

Przyjmijmy, że sporządziłeś formularz, który będzie uzupełniany przez Twojego współpracownika. Do tej pory wielokrotnie się zdarzało, że daty wprowadzane do jednej z kolumn były niepoprawne. Żmudne poszukiwanie błędu spadało później na Ciebie. Aby się przed tym uchronić, możesz zastosować narzędzie Excela o nazwie Sprawdzanie poprawności.

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

Załóżmy, że otrzymaliśmy zawierający dane zaimportowane z innego programu. Niestety, wartości dotyczące różnych kategorii zostały wprowadzone do jednej komórki, a interesuje nas liczba znajdująca się wewnątrz ciągu tekstowego.

Tworzenie planów budżetowych

Jednym z pierwszych etapów budżetowania sprzedaży jest prognozowanie sprzedaży przez przedstawicieli handlowych. Takie planowanie powinieneś przeprowadzić w tabelach z podziałem na klientów i produkty (najczęściej: grupy produktów). Planuje się ilościowo (w jednostce odpowiadającej sprzedawanym produktom, np. kg), aby potem móc pomnożyć ilość przez cenę (ustalaną w odrębnym etapie budżetowania) i otrzymać wartość sprzedaży. W tym artykule pokazujemy, w jaki sposób stworzyć prostą tabelę do planowania.

Porządkowanie danych z wykorzystaniem funkcji tekstowych

Praca z rozbudowanymi arkuszami, które oprócz cyfr zawierają komórki z tekstem, to dla wielu osób codzienność. Na podstawie tych danych będziesz mógł sporządzać raporty bądź wyodrębnić cząstkową i jednocześnie ważną informację. Będziesz mógł jednak napotkać pewne trudności. Excel mógł niepotrzebnie nadać liczbom format tekstowy, nie rozpoznać właściwego formatu daty, fragmenty tekstu zapisać w oddzielnych kolumnach, a niektóre połączyć z wartościami liczbowymi. W tym rozdziale pokazujemy, jak takie i podobne problemy szybko rozwiązać, wykorzystując funkcje tekstowe.

Wykres skumulowanej sprzedaży w porównaniu z budżetem

Podczas monitorowania sprzedaży często trzeba porównać ją do budżetu. Szefowie sprzedaży chcą bowiem wiedzieć, jak wygląda wykonanie budżetu w określonym czasie, np. w połowie miesiąca. Niezwykle przydatne okazuje się prezentowanie poziomu sprzedaży narastająco, czyli pokazanie sumy dotychczasowego wyniku od początku danego okresu (np. miesiąca). Pozwala to bowiem spojrzeć całościowo na dotychczasową sprzedaż bez uwzględniania jej jednorazowych wzrostów czy spadków.

Usuwanie danych z pominięciem formuł

Utworzoną w Excelu tabelę chciałabym wykorzystywać jako szablon, do którego wraz z nadejściem nowego okresu mogłabym wprowadzić nowe dane. Formuły musiałyby pozostać bez zmian i warto je pozostawić nienaruszone. Jak mogę szybko wyczyścić tylko dane z poprzedniego okresu?

Ujednolicanie wyglądu raportów

Excel umożliwia nie tylko przeprowadzanie różnorodnych obliczeń, ale również prezentację wyników w estetycznych tabelach i na wykresach. W zakresie formatowania i kształtowania tabel nowsze wersje Excela oferują nam wiele nowych możliwości. Bez wysiłku i konieczności definiowania każdego elementu szaty graficznej arkusza z osobna szybko utworzymy efektowne tabele i zestawienia, jednolite pod względem kolorystycznym z wykresami i innymi elementami dokumentu.

Wstawianie zera na początku liczb

Przy przenoszeniu do arkusza numerów identyfikacyjnych Excel automatycznie usuwa zero, jeśli występuje ono jako pierwszy znak numeru. Co zrobić, aby tego uniknąć?

Wstawianie wykresu z Excela do Worda

Przygotowałem kilka tabel i wykresów w Excelu, które chciałbym wykorzystać w dokumentacji przygotowywanej w programie Word. W jaki sposób przenieść te treści z jednego programu do drugiego?

Tworzenie harmonogramu z użyciem formuł

Zastosowanie funkcji SUMA.ILOCZYNÓW

Funkcja SUMA.ILOCZYNÓW mnoży i sumuje dwie lub więcej tablic, zwracając jedną liczbę z wynikiem. Natomiast mało kto wie, że za pomocą tej funkcji możemy wyliczać wartość dwóch i więcej kolumn w oparciu o kryterium. W tym przykładzie pokazujemy, jak można to zrobić.

Używanie filtra zaawansowanego do filtrowania danych na podstawie wielu kryteriów

Gdy operujemy na dużej ilości danych wpisanych do wielu kolumn, często potrzebujemy narzędzia, które szybko wyfiltruje odpowiednie dane na podstawie wielu kryteriów. Takim narzędziem w programie Excel jest filtr zaawansowany. Dalej zaprezentujemy, jak przygotować zestawienie do zastosowania filtra zaawansowanego oraz jak używać kryteriów filtrowania przy użyciu tego narzędzia. Ten tekst oprzemy na przykładzie zawierającym dane pracowników. Arkusz z tymi danymi jest przedstawiony na rysunku 1.

Szybkie sortowanie danych do wykresu bez użycia makr

Często podczas analiz sprzedaży zachodzi potrzeba przedstawienia nie tylko samego wyniku sprzedaży konkretnych produktów, lecz również ich udziału w sprzedaży całej grupy produktowej. Do zobrazowania tego używa się najczęściej wykresów kołowych lub pierścieniowych. Najczęstszy problem w takiej sytuacji to nieposortowane dane, co czyni wykres trudnym do interpretacji. W tym artykule pokazujemy, jak zautomatyzować tę czynność.

Wielokrotne wpisy wyróżnione

Dość często zdarza się sytuacja, że określony wpis występuje wielokrotnie. Nie zawsze jednak powtórzenie pozycji świadczy o pomyłce. Czasami kilkukrotne wpisywanie tych samych wartości jest zamierzone. Problem może stanowić przeglądanie takiego zestawienia i wyszukiwanie w nim informacji. Aby ułatwić sobie pracę z tego typu tabelami, proponujemy oznaczenie wszystkich wpisów występujących wielokrotnie.

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?

Zliczanie błędów występujących w arkuszu

Gdy którakolwiek formuła w zestawieniu, zamiast oczekiwanych wyników, wyświetla komunikaty o błędach, jest to zapewne kłopotliwa sytuacja. Szczególnie pomocna okaże się wówczas umiejętność szybkiego wyszukania i zliczenia komórek zwracających informacje o nieprawidłowościach. Będziesz na bieżąco wiedział, ile jeszcze pozostało błędów do usunięcia.

Zliczanie błędów występujących w arkuszu

Gdy którakolwiek formuła w zestawieniu, zamiast oczekiwanych wyników, wyświetla komunikaty o błędach, jest to zapewne kłopotliwa sytuacja. Szczególnie pomocna okaże się wówczas umiejętność szybkiego wyszukania i zliczenia komórek zwracających informacje o nieprawidłowościach. Będziesz na bieżąco wiedział, ile jeszcze pozostało błędów do usunięcia.

Pozycja wpisu z uwzględnieniem 2 kolumn

Przy budowaniu formuł wyszukujących na pewno zdarzyło się korzystać z funkcji PODAJ.POZYCJĘ. Jak nazwa wskazuje, jej zadaniem jest określenie, na jakiej pozycji w zakresie komórek (tablicy) znajduje się określona wartość. Zastosowanie tej funkcji na jednokolumnowym obszarze jest dość proste. Będziesz mógł jednak napotkać taki układ danych, że będzie wymagane sprawdzenie pozycji wpisu z uwzględnieniem 2 kolumn.

Złożone warunki formatowania tworzone z użyciem formuł

W Excelu formatowanie będziesz mógł uzależnić od wyniku zwracanego przez formułę. W zasadzie możesz budować dowolne formuły, jednak warunek jest następujący: zwracanymi wartościami muszą być PRAWDA lub FAŁSZ – odpowiednio 1 i 0.

Praktyczny przykład wykorzystania pasków danych

Załóżmy, że naszym zadaniem jest przeanalizowanie wyników finansowych firmy. Proste zestawienie wartości liczbowych i dat przedstawia rysunek. Oczywiście zalety stosowania formatowania warunkowego docenimy wtedy, gdy dane będą obejmowały wiele tysięcy komórek. Zaprezentowany przykład ma jedynie charakter poglądowy.

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

Jeżeli Twoje arkusze będą wypełniać inne osoby, warto zabezpieczyć się przed niepoprawnym użytkowaniem arkusza. Do tego celu możemy zastosować narzędzie Excela o nazwie Sprawdzanie poprawności danych. Po nałożeniu na komórki odpowiednich reguł program będzie sam kontrolował, aby wszystkie rubryki były poprawnie wypełnione. Jeśli wpisana zostanie wartość niespełniająca kryteriów, wówczas program wyświetli ostrzeżenie.

Zapisywanie własnych kryteriów filtrowania

Załóżmy, że co miesiąc prezentujesz wyniki sprzedażowe swojej grupy. Do przygotowania analizy wykorzystujesz roboczy raport zbiorczy zawierający wszystkie transakcje dokonane w minionych okresach. Aby wyświetlić dane konkretnego klienta, np. do porównania, musisz każdorazowo filtrować listę pod względem różnych kryteriów. Po co niepotrzebnie tracić czas na wielokrotne filtrowanie tych samych wartości? Możesz ułatwić sobie zadanie, najpierw pogrupować dane, a następnie zapisać ich układ jako widok niestandardowy. Dzięki temu jednym kliknięciem myszy będziemy mogli odkrywać potrzebne w danej chwili zestawy danych.

Podstawy tworzenia tabele przestawnych

Tabele przestawne są świetnym narzędziem do analizy dużych zbiorów danych. Ta funkcja Excela umożliwia tworzenie wizualizacji, przygotowywanie zestawień lub analiz w dowolnym układzie z interesującym użytkownika stopniem szczegółowości. Sprawdza się również jako narzędzie zwiększające wydajność pracy, ponieważ umożliwia automatyczne przygotowanie dowolnej liczby powtarzających się zestawień. W artykule opisujemy krok po kroku wszystkie etapy tworzenia tabeli przestawnej.

Odpowiedzi na pytania Czytelników

Zobacz, z jakimi typowymi problemami borykają się inni użytkownicy Excela i poznaj nasze praktyczne rozwiązania.

Tworzenie makr bez znajomości programowania

Jest wiele czynności w Excelu, które warto zautomatyzować. Osoby znające język programowania Visual Basic napiszą makro i Excel będzie ich wyręczał w wykonywaniu żmudnych zadań. Jeżeli nie masz czasu i zapału do nauki programowania, do tworzenia prostych makr możesz wykorzystać narzędzie służące rejestrowaniu czynności wykonywanych w arkuszu. Dzięki niemu bez znajomości języka programowania możesz tworzyć swoje makra automatyzujące pracę.

Porządkowanie wyglądu raportów - motywy

Excel umożliwia nie tylko przeprowadzanie różnorodnych obliczeń, ale również prezentację wyników w estetycznych tabelach i na wykresach. W zakresie formatowania i kształtowania tabel nowsze wersje Excela oferują nam wiele nowych możliwości. Bez wysiłku i konieczności definiowania każdego elementu szaty graficznej arkusza z osobna szybko utworzymy efektowne tabele i zestawienia, jednolite pod względem kolorystycznym z wykresami i innymi elementami dokumentu.

Planowanie budżetu z wykorzystaniem Menedżera scenariuszy

Scenariusze są w Excelu częścią zestawu poleceń nazywanych narzędziami analizy symulacji. Analiza symulacji polega na zmienianiu wartości w komórkach, aby zobaczyć, jak te modyfikacje wpłyną na wynik formuł w arkuszu. Przy użyciu scenariuszy można tworzyć i zapisywać różne zestawy wartości oraz przełączać między nimi. W tym artykule przyjrzymy się, jak korzystać z menedżera scenariuszy na przykładzie fikcyjnej firmy.

Formularze w Excelu od podstaw – część 1

Praca w Excelu może być wygodniejsza, jeśli w arkuszach zastosujesz tzw. formanty formularza. Umożliwiają one szybsze wprowadzanie danych, ale także ułatwiają wyszukiwanie i modyfikowanie wykonywanych obliczeń. Możesz tworzyć w arkuszu własne formularze i to bez używania języka Visual Basic. Formanty formularza mają uproszczone opcje konfiguracji, które są dostępne i zrozumiałe dla każdego. Abyś swobodnie automatyzować obsługę arkusza, w pierwszej kolejności trzeba poznać zasady wykonywania podstawowych operacji na formantach. W tym artykule pokażemy Ci także pewne sztuczki i triki, dzięki którym pewne zadania wykonasz na skróty.

Tworzenie tabel przestawnych za pomocą makr

Excela umożliwia gromadzenie, przechowywanie i obróbkę olbrzymich ilości danych. Jednak z pewnością wiesz już, że dane to nie wszystko. Ważna jest umiejętność i możliwość takiego grupowania wartości, aby móc wyciągnąć z nich jakąś informację, posiąść wiedzę. Właśnie do takich celów wyśmienicie nadają się tabele przestawne. A jeśli do tworzenia tabel przestawnych użyjemy makr, otrzymamy ogromne możliwości.

Przenoszenie danych z arkusza do dokumentu

Dość często zdarza się, że zestawienia analityczne lub wykresy przygotowane w arkuszu powinny się znaleźć w ogólnym raporcie i stanowić uzupełnienie jego części opisowej. Trzeba zatem w dokumencie Worda zawierającym tekst osadzić tabele lub obiekty graficzne. Okazuje się, że wykorzystanie do tego celu standardowej operacji Kopiuj/Wklej nie zawsze jest wskazane lub ogranicza pewne funkcje umożliwiające łatwiejszą pracę z dokumentem. Z tego artykułu dowiesz się, jak poprawnie przenosić dane z Excela do Worda.

Operacje logiczne w formatowaniu warunkowym

Za pomocą narzędzia formatowania warunkowego możesz nadawać formaty komórkom lub całym zakresom na podstawie zdefiniowanych przez Ciebie kryteriów. Excel udostępnia mnóstwo predefiniowanych ustawień formatowania, których stosowanie nie wymaga od Ciebie w zasadzie żadnej wiedzy dotyczącej operacji logicznych. Niestety, chcąc zastosować bardziej zaawansowaną regułę formatowania, wówczas gotowe opcje mogą być niewystarczające i będziesz zmuszony od podstaw zbudować własny warunek, opierając się na formule wykorzystującej wartości logiczne.

Zaawansowane przykłady wykorzystania formuł warunkowych

 W tym artykule przedstawiamy kilka praktycznych przykładów użycia formuł warunkowych w praktycznych zastosowaniach, z jakimi może spotkać się użytkownika Excela. Omawiamy możliwości wykorzystania sumowania warunkowego. Pokazujemy, jak automatycznie rozliczać na bilingu prywatne połączenia pracowników. Na koniec wyjaśniamy, w jakiś sposób wyróżnić w zestawieniach skrajne wartości.

Tabele przestawne - triki

Tabele przestawne są znakomitym narzędziem Excela, umożliwiającym przeprowadzenie wygodnej analizy nawet niezwykle obszernych zakresów danych. Jeśli opanujesz metody dostosowywania danych i tabel przestawnych do swoich potrzeb, istotnie zwiększysz efektywność pracy. Skupisz bowiem uwagę na wybranej grupie danych, a nie całej tabeli.

Warunki logiczne w formułach tablicowych

Warunki w formułach tablicowych tworzy się podobnie jak w zwykłych formułach. Konieczne jest utworzenie działania logicznego, które w wyniku zwróci wartości logiczne PRAWDA/FAŁSZ lub odpowiednio jedynki i zera. Różnica polega jednak na tym, że w formule tablicowej takich sprawdzeń występuje bardzo wiele, ponieważ operuje na rozległych zakresach danych. Używa się zatem równań z operatorami porównawczymi, które opierają się na zakresach komórek (np. A1:A5>0), a nie na pojedynczych komórkach (np. A1>0). Zadaniem użytkownika jest przekazanie wielu wyników cząstkowych przeznaczonych do dalszych obliczeń.

Tworzenie zaawansowanych formuł obliczeniowych

Niektóre tabele, z którymi mamy do czynienia, nie zawsze mają układ listy i analizowanie ich za pomocą prostych formuł obliczeniowych jest utrudnione. Szef nie będzie chciał słuchać tłumaczeń, że dane były niepoprawnie przygotowane. Ważny jest efekt końcowy, czyli właściwie przeprowadzone obliczenia. Nasza w tym głowa, jak je wykonamy. W takich sytuacjach musimy korzystać z niestandardowych rozwiązań.

Wykres prognozy z oznaczeniem marginesu błędu

W trakcie przeprowadzania różnego rodzaju prognoz lub badań statystycznych prawie zawsze przyjmuje się określony margines błędu. W przypadku zestawień liczbowych bardzo łatwo możemy go uwzględnić dzięki zastosowaniu odpowiednich formuł. Problemy pojawiają się wówczas, gdy dopuszczalne wartości błędu chcemy zaznaczyć na wykresie. Excel jest przygotowany do budowania takich prezentacji. Aby dowiedzieć się, jak je tworzyć, warto skorzystać z tego hasła. Znajdują się w nim także odpowiedzi na następujące pytania:

Ukrywanie danych w tabeli przestawnej

Jeżeli zamierzasz budować wielowariantowe raporty, to tabela przestawna okaże się najlepszym narzędziem do tego celu. Niestety, stanowi specyficzny zakres arkusza i przez to zmiana struktury danych jest utrudniona. Okazuje się, że są sposoby na sprytne omijanie ograniczeń dotyczących układu raportu przestawnego. Z tego artykułu dowiesz się, jak zmienić zakres danych tabeli przestawnej i jak za pomocą makra filtrować dane w raporcie przestawnym.

Raporty produkcyjne – grupowanie pól tabeli przestawnej

Tabela przestawna umożliwia wykonanie takiej operacji bez konieczności dodawania pomocniczych kolumn w zestawieniu, z którego jest budowana. Wykonuje się je przy użyciu opcji grupowania pól tabeli przestawnej. Przykładem będą raporty firmy zajmującej się produkcją i sprzedażą farb różnego przeznaczenia. Asortyment produktów firmy dzieli się na pięć różnych grup ze względu na ich zastosowanie. Są to: farby do fasad, farby do podłóg, farby do ścian i sufitów, farby do ogrodzeń oraz farby do kuchni i łazienek. Każda grupa zawiera 25 kolorów oraz maksymalnie 5 różnych pojemności. Daje to ogromny asortyment produktów (375 różnych wyrobów). Z systemu produkcyjnego możliwy do pobrania jest zestaw danych za 2013 rok, jak na rysunku 1.

Planowanie budżetu z użyciem narzędzia Tabela danych

Budżet firmy na kolejny rok powstaje we współpracy z różnymi działami przedsiębiorstwa, np. sprzedaży, produkcji, zasobów ludzkich itd. Jest to długotrwały i skomplikowany proces, ponieważ planowaniu podlega większość z obszarów działalności danej organizacji. Wynikiem takiej wewnętrznej kooperacji jest plan funkcjonowania firmy na przyszły okres (przeważnie roku), wyznaczający cele w wielu aspektach i dziedzinach funkcjonowania jednostki. W tym artykule przyjrzymy się firmie dystrybucyjnej obsługującej około 80 klientów, której asortyment towarów liczy w przybliżeniu 120 pozycji. Na jej przykładzie pokażemy, jak w prosty i szybki sposób wyliczyć wszystkie warianty modelu dla kombinacji dwóch parametrów przyjmujących kilkanaście różnych wartości (łącznie 135 kombinacji) .

Wizualizacja wyników sprzedażowych za pomocą tabeli przestawnej

Często użytkowników nie stać na zakup drogich programów Business Intelligence przeznaczonych do analizy dużych zbiorów danych w różnych układach i o odmiennym poziomie szczegółowości. Jednak od pracowników zajmujących się analizą wymagane są zestawienia i raporty pokazujące dane w różnych układach, często będące wynikiem analiz opracowanych na podstawie tysięcy rekordów.

Unikanie błędnych zaokrągleń

Arkusz kalkulacyjny Excel pozwala przeprowadzić obliczenia nie tylko szybko, ale i bardzo dokładnie. Paradoksalnie właśnie duża dokładność obliczeń może mieć czasem fatalne skutki i być przyczyną błędów w zestawieniach czy różnych innych dokumentach tworzonych w arkuszu kalkulacyjnym. Zdarza się to wówczas, gdy użytkownik programu nie ma odpowiedniej wiedzy na temat sposobu przeprowadzania obliczeń na wartościach zaokrąglanych.