246
Szukaj wyniku – sterowanie parametrami modelu finansowego
Szukanie wyniku jest częścią zestawu poleceń nazywanego narzędziami analizy „co-się-stanie-gdy”. Proces zmieniania wartości w komórkach w celu sprawdzenia, jak te zmiany wpłyną na wyniki formuł w arkuszu. Na przykład, zmienianie stopy procentowej w tabeli amortyzacji w celu określenia sumy płatności. Jeśli znamy oczekiwany wynik jednej formuły, a nie znamy wartości wejściowej niezbędnej do obliczenia wyniku formuły, można użyć funkcji Szukaj wyniku. Szukanie wyniku jest metodą znajdowania określonej wartości komórki przez dostosowywanie wartości innej komórki. Szukając wyniku, Excel zmienia wartość w określonej komórce, aż formuła zależna od tej komórki zwróci żądany wynik.
Opcje formatowania warunkowego
Jednym z najbardziej popularnych narzędzi wizualizacji danych jest formatowanie warunkowe. Od wersji Excel 2007 zostało ono rozbudowane o dodatkowe możliwości. Ten artykuł jest w całości poświęcony opisowi możliwości, jakie dają opcje formatowania warunkowego. Na początku pokażemy jak dobierać odpowiednie kryteria formatowania i jak je zmieniać dla podstawowych opcji formatowania warunkowego.
245
Wymiana danych między plikami
Pracując ze skoroszytami zawierającymi odwołania do innych plików, będziesz mógł napotkać kłopot z łączami przy zmianie lokalizacji pliku. Zwykle jest to duży problem, którego rozwiązanie zabiera sporo czasu. W tym artykule przeczytasz, w jaki sposób Excel przechowuje łącza do plików znajdujących się na dyskach lokalnych, mapowanych lub w udziałach sieciowych. Pozwoli to zrozumieć, dlaczego przy zmianie lokalizacji plików powstają błędy przy automatycznej aktualizacji łącza, a także zdobyć wiedzę, jak uniknąć takich sytuacji.
Wykres dni tygodnia i okresów
Wiele zestawień wyników sprzedażowy odnosi się do dłuższych okresów, np. lat, kwartałów czy miesięcy. Można jednak napotkać dane bardziej szczegółowe o rozkładzie dziennym. Chcemy je przeanalizować, a następnie zaprezentować wyniki obliczeń na wykresie. Pojawia się problem, ponieważ zarówno analiza, jak i prezentacja graficzna powinny uwzględniać podział na dni robocze i wolne od pracy. W tym artykule pokażemy, jak za pomocą formuł przygotować odpowiednie obliczenia, a następnie wyniki przedstawić na czytelnym wykresie.
Sposoby podziału i łączenia tekstu w Excelu
Często istnieje potrzeba podzielenia danego tekstu na pojedyncze wyrazy lub wydobycia z niego określonego fragmentu. Przykładem może być zaimportowane dane z systemu finansowo-księgowych gdzie dane znajdują się w jednej komórce, np.”FV/1254784/2015/01/02”. Jest to ciąg znaków zawierających rodzaj dokumentu, jego numer, rok, miesiąc i dzień. Nas natomiast interesuje tylko numer dokumentu. Jeżeli mamy kilka takich pozycji, jesteśmy w stanie w kolejnej kolumnie przepisać te numery. Natomiast jeżeli mamy kilka tysięcy rekordów, to taka operacja będzie bardzo czasochłonna i możemy się pomylić. Excel umożliwia takie operacje przy użyciu funkcji tekstowych oraz narzędzia tekst jako kolumny. Odwrotną operacją jest łącznie kilku tekstów w jeden. Taką czynność można wykonać również przy użyciu funkcji lub formuł w Excelu.
252
Poprawianie układu danych źródłowych, aby zbudować tabelę przestawną
Raport przestawny to narzędzie, które niezwykle ułatwia analizowanie dużych zestawień danych. Aby z niego skorzystać, trzeba jednak odpowiednio przygotować tabelę źródłową. Niestety często dane mają układ uniemożliwiający utworzenie tabeli przestawnej. Z reguły dane będziesz mógł przystosować automatycznie za pomocą kilku formuł.
Nazwy zdefiniowane – szybkie wyznaczanie ważnych dat
Obliczenia, np. związane z wartościami reprezentującymi daty, są niezwykle kłopotliwe szczególnie dla początkujących użytkowników Excela. Trzeba nabrać pewnego doświadczenia i poznać zasady działania na datach, aby swobodnie pracować w arkuszu z liczbami odnoszącymi się do tych wartości. W tym artykule pokazujemy, jak przygotować swego rodzaju formuły-szablony, które będziesz mógł wykorzystywać do szybkiego wyznaczenia pewnych specjalnych dat, np. ostatniego dnia kwartału czy ostatniego czwartku w miesiącu. Jest to przydatne w tych firmach, w których dniem wypłaty pensji jest właśnie któryś ostatni dzień tygodnia w miesiącu. Dzięki nadaniu formułom nazw dostęp do nich będzie szybki i wygodny.
251
Szybkie wystawianie zaświadczeń o zarobkach z użyciem makr
Ręczne wypisywanie zaświadczeń o zarobkach jest dość żmudnym zajęciem, dlatego pokazujemy, jak przygotować bardzo przydatne narzędzie służące do szybkiego tworzenia dokumentów potwierdzających dochody pracowników. Mają takie narzędzie, kilkoma kliknięciami myszy sporządzimy zaświadczenie o zarobkach danego pracownika.
Skalowanie osi na wykresach
Przygotowując wszelkiego rodzaju harmonogramy i prognozy, bardzo często posiłkujemy się wykresami. Najczęściej przedstawiają one przebieg jakiegoś zjawiska w danym okresie. Niekiedy zbudowanie wykresu o odpowiednim układzie może sprawić nie lada kłopot, ponieważ Excel inaczej interpretuje wartości czasu czy daty niż zwykłe liczby. W tym artykule pokażemy kilka sposobów radzenia sobie z trudnościami przy tworzeniu tego typu wykresów.
Prezentacja skrajnych wartości – wykres giełdowy
Przyjmijmy, że mamy zestawienie obrotów z różnych oddziałów firmy. Planujesz je ze sobą porównać i w czytelny sposób przedstawić minimalne, maksymalne i średnie wartości. Trudno to zrealizować za pomocą najczęściej stosowanych wykresów liniowych, słupkowych czy kołowych. Natomiast wykres giełdowy, który wbrew swojej nazwie nie musi służyć wyłącznie przedstawianiu kursów akcji czy walut, idealnie nadaje się do tego celu.
244
Wyszukanie błędnego numeru PESEL
Numer PESEL jest wymagany w wielu drukach i zestawieniach. Składa się z 11 cyfr i przy wpisywaniu go do arkusza często zdarzają się pomyłki. Proponujemy zatem, aby stosować formuły, które będą sprawdzać poprawność numeru PESEL i dodatkowo wyciągać z niego określone przydatne informacje. Taka weryfikacja może być pierwszym etapem kontrolnym, m.in. przy weryfikacji kontrahenta. W tym tekście pokazujemy, jak zbudować łatwe w użyciu narzędzie, pozwalające na sprawdzenie poprawności numeru PESEL.
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.
Szybkie wyszukiwanie danych w dużych zestawieniach
Jeśli chcemy rejestrować dane dotyczące klientów, Excel idealnie nadaje się do tego celu. Z łatwością można wpisywać do odpowiednich rubryk liczbę zamówionych towarów, a także daty dokonania transakcji. Problem pojawia się, jeśli danych jest tak dużo, że tracimy kilka minut na odnalezienie konkretnej pozycji rejestru i przeanalizowanie wprowadzonych informacji. W takiej sytuacji warto wprowadzić w arkuszu mechanizmy, które, np. wyróżnią interesujące nas wartości.
243
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 artykule pokazujemy, jak takie i podobne problemy szybko rozwiązać, wykorzystując funkcje tekstowe.
Licznik pokazujący realizację budżetu
Gdy tworzymy raporty, dużo uwagi zwracamy na zbieranie danych. Jest to jednak dopiero połowa sukcesu, ponieważ dane te trzeba jeszcze przedstawić w sposób, który pozwoli odbiorcy jak najlepiej je zrozumieć, szybko wyciągnąć z nich wnioski i zapamiętać wyniki. Do tego używamy takich narzędzi jak tabele czy wykresy.
242
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.
Ustalanie wysokości bieżących i historycznych pensji pracowników
Częste pytanie, na jakie muszą odpowiadać pracownicy działów HR, to: Ile dany pracownik zarabiał w określonym czasie? Jest to potrzebne na przykład przy ustalaniu podwyżki dla pracowników. W tym artykule pokażę, w jaki sposób stworzyć formułę, która odpowie na to pytanie.
Tworzenie harmonogramu z użyciem formuł
Korzystając z Excela, nie warto ograniczać się jedynie do analizowania informacji z przeszłości. Arkusz idealnie nadaje się do tworzenia planów, harmonogramów i prognozowania przyszłych wyników czy zdarzeń. Nie trzeba przy tym mieć specjalistycznej wiedzy analitycznej lub statystycznej. Przydatne informacje, które pomogą w podjęciu trafnych decyzji, będziesz mógł uzyskać za pomocą kombinacji prostych funkcji arkuszowych. W tym haśle przeczytasz, jak zbudować zestawienie do planowania dostępności zasobów.
248
Wstęp do budowania własnych formularzy
Co zrobić, jeśli planujesz, np. wyświetlić okno dialogowe z dwoma polami do wpisywania informacji: loginu i hasła? Innym razem potrzebujesz na przyciskach w oknie dialogowym wyświetlić dowolny tekst, a nie tylko dostępne w poleceniu MsgBox. Nie wszystko da się przedstawić za pomocą wbudowanych okien dialogowych. Natomiast za pomocą formularzy UserForm będziesz mógł utworzyć dowolne okna dialogowe. W tym artykule pokażemy, jak je budować, a także w jaki sposób z nich korzystać. Powiemy także, jak stosować kontrolki i umieszczać je na formularzu UserForm.
Tygodniowy raport marży brutto
W tym artykule przedstawiamy sposób zautomatyzowania sporządzania tygodniowego raportu marży brutto na poszczególnych produktach. Hipotetyczny analityk pracuje w firmie produkcyjnej, której asortyment liczy 100 różnych produktów. Z tego zostało wytypowane 30 głównych produktów, które stanowią 60% całej sprzedaży przedsiębiorstwa. Raport został nazwany TOP30 i w każdy poniedziałek rano jest wysyłany do sprzedawców, którzy na jego podstawie ustalają politykę sprzedaży na kolejny tydzień.
Podstawy tworzenia tabel i wykresów przestawnych
Tabela przestawna jest rozbudowanym i interakcyjnym narzędziem do szybkiego podsumowania dużych ilości danych, tworzenia analiz w interesującym nas układzie oraz o określonym stopniu szczegółowości. W tym artykule pokazujemy, jak utworzyć tabelę przestawną na podstawie danych o kosztach w rozbiciu na poszczególne MPK. Następnie omawiamy, jak przygotować wykres przestawny.
247
Ustalanie wysokości bieżących i historycznych pensji pracowników
Częste pytanie, na jakie muszą odpowiadać pracownicy działów HR: Ile dany pracownik zarabiał w określonym czasie? Jest to potrzebne, na przykład przy ustalaniu podwyżki dla pracowników. W tym artykule pokażę, w jaki sposób stworzyć formułę, która odpowie na to pytanie.
Szablon faktur – zaawansowane możliwości
Z reguły rozliczane faktury mają różne formy i terminy płatności. Bardzo pomocny byłby wtedy szablon uwzględniający te różnice i umożliwiający szybkie rozliczenie faktur. Jeśli używany przez nas szablon nie ma takich usprawnień, można go udoskonalić i wyposażyć w tę, a także kilka innych przydatnych funkcji.Najpierw pokażemy, jak rozbudować szablon faktury, aby po dodaniu nowej faktury automatycznie utworzył się kolejny arkusz. Dodatkowo nowa faktura otrzyma nowy numer na podstawie faktury ostatnio wystawionej. Pokażemy także ewidencję wystawionych faktur, która może służyć jednocześnie jako ewidencja sprzedaży VAT.
Porządkowanie danych o niestandardowym układzie
Niejednokrotnie masz do czynienia z zestawieniami przygotowanymi przez współpracowników, którzy nie do końca przemyśleli konstrukcję arkusza. Dane bywają wprowadzone chaotycznie, poszczególne grupy wartości składają się ze zmiennej liczby wierszy, wobec czego najprostsza czynność, jak choćby sortowanie, staje się nie lada problemem. W tym haśle pokażemy Ci krok po kroku, jak zbudować formułę, która będzie potrafiła automatycznie sortować zestawienia o niestandardowym układzie.
250
Tworzenie harmonogramu z użyciem formuł
Korzystając z Excela, nie warto ograniczać się jedynie do analizowania informacji z przeszłości. Arkusz idealnie nadaje się do tworzenia planów, harmonogramów i prognozowania przyszłych wyników czy zdarzeń. Nie trzeba przy tym mieć specjalistycznej wiedzy analitycznej lub statystycznej. Przydatne informacje, które pomogą w podjęciu trafnych decyzji, będziesz mógł uzyskać za pomocą kombinacji prostych funkcji arkuszowych. W tym haśle przeczytasz, jak zbudować zestawienie do planowania dostępności zasobów.
Średnia sprzedaż z ostatnich 3 miesięcy (średnia krocząca)
Jedną z bardzo często wykorzystywanych metod prognozowania sprzedaży jest średnia krocząca, zwana również średnią ruchomą prostą (SMA, simple moving average). Używa się jej wtedy, gdy np. planujesz przewidzieć sprzedaż w bieżącym miesiącu na podstawie danych z trzech ostatnich miesięcy. W każdym miesiącu będziemy brać pod uwagę inne miesiące. Jeśli jest październik, to uśrednimy wynik z lipca, sierpnia i września. W listopadzie weźmiemy pod uwagę sierpień, wrzesień i październik. Zawsze trzy poprzednie miesiące.
249
Zarządzanie dokumentami kadrowymi
Jednym z obowiązków każdego pracownika działu kadrowo-płacowego jest wypełnianie zaświadczenia o zarobkach pracowników. Ręczne wpisywanie informacji bywa dość żmudnym zajęciem, dlatego warto je automatyzować. W tym haśle przeczytasz, jak samodzielnie przygotować narzędzie służące do zarządzania zaświadczeniami o dochodach pracowników.
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ą Ci 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.
241
Szybkie ukrywanie niepotrzebnych kolumn lub wierszy
Jeśli pracujemy z zestawieniem, które obejmuje niewielki zakres komórek i znajduje się na początku arkusza (lewym górnym rogu), resztę komórek będziesz mógł ukryć.
Szablon faktur – zaawansowane możliwości
Z reguły rozliczane faktury mają różne formy i terminy płatności. Bardzo pomocny byłby wtedy szablon uwzględniający te różnice i umożliwiający szybkie rozliczenie faktur. Jeśli używany przez nas szablon nie ma takich usprawnień, można go udoskonalić i wyposażyć w tę, a także kilka innych przydatnych funkcji.Najpierw pokażemy, jak rozbudować szablon faktury, aby po dodaniu nowej faktury automatycznie utworzył się kolejny arkusz. Dodatkowo nowa faktura otrzyma nowy numer na podstawie faktury ostatnio wystawionej. Pokażemy także ewidencję wystawionych faktur, która może służyć jednocześnie jako ewidencja sprzedaży VAT.
Prognozowanie z użyciem trendu liniowego i wykładniczego
Niemal każdy spotkał się w swojej pracy z koniecznością wykonania pewnych obliczeń na danych prognozowanych lub planowanych. Tendencje wzrostowe lub spadkowe analizowanych wartości pozwalają w przybliżeniu oszacować zmiany w przyszłości, a tym samym umożliwiają podjęcie pewnych działań wyprzedzających. Jeśli rozsądek podpowiada Ci, że horoskopy i wróżenie z fusów to nie najlepsze pomysły na prognozowanie zmian danych, wykorzystaj zgromadzone informacje i włącz do pracy arkusz kalkulacyjny. Excel oferuje efektywne narzędzia, z pomocą których określisz trendy zmian swoich danych.
