186
Wymiana danych między Excelem i Wordem
Typowym zadaniem jest przygotowywanie w edytorze tekstu raportu, który ma zostać wzbogacony o analizy w postaci tabelarycznej lub wykresy. Niestety zestawienia wklejane do dokumentu zwykle mają niepoprawny układ, co zmusza do ich ponownego formatowania. Ponadto, jeśli zdarzyłby się jakiś błąd, to aby go poprawić, konieczny byłby powrót do skoroszytu źródłowego i ponowne przenoszenie tabeli do dokumentu. Okazuje się, że wcale nie musimy tracić na to czasu. Pełne możliwości Excela i Worda wykorzystamy , jeśli nauczymy się wymieniać dane między skoroszytami i dokumentami.
Tworzenie kodów kreskowych w Excelu
Podstawowym zadaniem kodów kreskowych szybka identyfikacja towaru. Do ich tworzenia wcale nie musimy wykorzystywać specjalistycznego oprogramowania. Także w Excelu możemy w prosty sposób zbudować kod paskowy na podstawie numeru EAN-13 (ang. European Article Number – Europejski Kod Towarowy). W tym artykule przeczytasz, jak zaprojektować skoroszyt zawierający przygotowany do druku szablon etykiety z kodem paskowym, w którym będziemy mogli dowolnie dostosowywać kod numeryczny towaru. A to wszystko bez użycia makr Visual Basic.
Oddzielanie danych rzeczywistych od prognozowanych na wykresie liniowym
Wykres liniowy najlepiej zobrazuje rozkład danych w dłuższym okresie. Czasami jednak do zaprezentowania są zarówno dane rzeczywiste, jak i prognozowane. Nie mogą być zawarte na jednej linii, ponieważ odbiorca przekazu nie zorientuje się, od którego miejsca rozpoczynają się szacunki. W tym artykule pokazujemy, w jaki sposób podzielić linię na 2 części i wyróżnić je kolorami.
185
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.
184
Odpowiedzi na pytania Czytelników
Zobacz, z jakimi typowymi problemami borykają się inni użytkownicy Excela i poznaj nasze praktyczne rozwiązania.
Najlepsze triki
Poznaj nasze porady i sztuczki, aby szybciej i efektywniej korzystać z Excela. Dzięki tym trikom szybciej wykonasz swoją pracę.
Zmiana wyglądu wykresy za pomocą suwaków i pokręteł
W tym artykule pokażemy, w jaki sposób sterować widokiem danych na wykresie za pomocą kontrolek formularza. Jest to bardzo pomocne np. przy prezentacji, gdy pokazujemy różne wersje tego samego wykresu. Za pomocą takich kontrolek jak pasek przewijania, pokrętło czy lista rozwijalna można uczynić wykres interaktywnym, a przenosząc kontrolki do innego arkusza za pomocą trybu dwuekranowego, można na bieżąco nanosić zmiany na wykresie.
183
Rachunek marży dla poszczególnych produktów i klientów
W celu redukcji kosztów firma postanowiła przeprowadzić analizę zyskowności (marży) dla każdego z produktów oraz dla każdego z klientów. W ten sposób zostaną wytypowane najbardziej rentowne grupy produktów i klientów. Kolejnymi krokami będzie analiza możliwości zwiększenia produkcji i sprzedaży tych produktów oraz rozszerzenie współpracy z grupą najbardziej opłacalnych klientów. Natomiast dla produktów o najniższej marży zostaną podjęte decyzje i działania w celu poprawienia rentowności. Składniki kalkulacji marży (1)
Odpowiedzi na pytania Czytelników
Zobacz, z jakimi typowymi problemami borykają się inni użytkownicy Excela i poznaj nasze praktyczne rozwiązania.
182
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.
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.
Optymalizacja dostaw do magazynu
Excel służy nie tylko do analizowania informacji z przeszłości. Arkusz kalkulacyjny 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, możemy uzyskać za pomocą kombinacji prostych funkcji arkuszowych. W tym haśle przeczytamy, jak zbudować zestawienie do planowania dostępności zasobów.
181
Wymiana danych między plikami
Pracując ze skoroszytami zawierającymi odwołania do innych plików, można 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 wyjaśniamy, 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 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.
Poprawianie czytelności porównań na wykresach
Jedną z częściej przygotowywanych w Excelu prezentacji jest porównanie wartości zrealizowanych z planowanymi. Aby doprowadzić wygląd wykresu do perfekcji, niekiedy trzeba przy nim spędzić nawet kilkanaście minut. Jeżeli chcemy oszczędzić ten czas, skorzystajmy z rozwiązań zawartych w tym haśle. Pokażemy, jak za pomocą podstawowych typów wykresów zbudować czytelną prezentację porównawczą.
180
Poprawność danych – kontrolowanie wypełniania arkuszy
Jeżeli Twoje 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ł, 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.
Poprawianie czytelności porównań na wykresach
Jedną z częściej przygotowywanych w Excelu prezentacji jest porównanie wartości zrealizowanych z planowanymi. Aby doprowadzić wygląd wykresu do perfekcji, niekiedy trzeba przy nim spędzić nawet kilkanaście minut. Jeżeli chcesz oszczędzić ten czas, skorzystajmy z rozwiązań zawartych w tym artykule. Pokażemy, jak za pomocą podstawowych typów wykresów zbudować czytelną prezentację porównawczą.
Odpowiedzi na pytania Czytelników
Zobacz, z jakimi typowymi problemami borykają się inni użytkownicy Excela i poznaj nasze praktyczne rozwiązania.
179
Tworzenie planów budżetowych
Jednym z pierwszych etapów budżetowania sprzedaży jest prognozowanie sprzedaży przez przedstawicieli handlowych. Takie planowanie należy 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.
Sumy częściowe i grupowanie danych
Załóżmy, że mamy wykaz wszystkich transakcji, które udało się sfinalizować handlowcom w minionym roku. Chcesz na ich podstawie wykonać roczny raport podsumowujący. Niestety, lista jest bardzo długa i nieuporządkowana. Zaznaczanie komórek i klikanie za każdym razem ikony Autosumowanie zajęłoby zbyt dużo czasu. Rozwiązaniem problemu jest zastosowanie narzędzia Sumy częściowe (znane również jako Sumy pośrednie), które błyskawicznie wygeneruje podsumowania dla wyznaczonych zakresów danych, nawet z wielu kolumn jednocześnie. W tym artykule pokażemy również, jak grupować dane, a następnie zapisać ich układ jako Widok niestandardowy. Dzięki temu jednym kliknięciem myszy można przeglądać potrzebne w danej chwili zestawy danych.
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 można przystosować automatycznie za pomocą kilku formuł.
178
Odpowiedzi na pytania Czytelników
Zobacz, z jakimi typowymi problemami borykają się inni użytkownicy Excela i poznaj nasze praktyczne rozwiązania.
Najlepsze triki
Poznaj nasze porady i sztuczki, aby szybciej i efektywniej korzystać z Excela. Dzięki tym trikom szybciej wykonasz swoją pracę.
177
Układ danych - zapisywanie kryteriów filtrowania
Przyjmijmy, że co miesiąc dokonaj prezentacji wyników sprzedażowych swojej grupy. Do przygotowania analizy wykorzystujesz roboczy raport zbiorczy zawierający wszystkie transakcje dokonane w minionych okresach. Aby wyświetlić dane konkretnego klienta, np. w celu porównania, musisz każdorazowo filtrować listę po względem różnych kryteriów. Po co niepotrzebnie tracić czas na wielokrotne filtrowanie tych samych wartości? Można ułatwić sobie zadanie, najpierw grupując dane, a następnie zapisując ich układ jako Widok niestandardowy. Dzięki temu jednym kliknięciem myszki można odkrywać potrzebne w danej chwili zestawy danych.
Podstawowe funkcje tekstowe
Często arkusze Excela zawierają nie tylko cyfry i obliczenia, ale również komórki z tekstem. Na ich podstawie można sporządzić raport bądź wyodrębnić cząstkową i jednocześnie ważną informację. Można jednak napotkać pewne trudności. Przykładowo, Excel niepotrzebnie nadał liczbom format tekstowy, nie rozpoznaje właściwego formatu daty, fragmenty tekstu powinny być zapisane w oddzielnych kolumnach, a niektóre z nich zostały połączone z wartościami liczbowymi. Czy czeka żmudne i pracochłonne porządkowanie arkusza? Niekoniecznie! W tym artykule pokażemy Ci, jak takie i podobne problemy szybko rozwiązać, wykorzystując funkcje tekstowe. Nauczysz się też korzystać z Kreatora wstawiania funkcji.
176
Sumy częściowe i grupowanie danych
Załóżmy, że mamy wykaz wszystkich transakcji, które udało się sfinalizować handlowcom w minionym roku. Chcemy na ich podstawie wykonać roczny raport podsumowujący. Niestety, lista jest bardzo długa i nieuporządkowana. Zaznaczanie komórek i klikanie za każdym razem ikony Autosumowanie zajęłoby zbyt dużo czasu. Rozwiązaniem problemu jest zastosowanie narzędzia Sumy częściowe (znane również jako Sumy pośrednie), które błyskawicznie wygeneruje podsumowania dla wyznaczonych zakresów danych, nawet z wielu kolumn jednocześnie.
Przeszukiwanie obszernych zestawień dwa razy szybciej
Poprzez kombinowanie ze sobą różnych funkcji w jednej formule można analizować oraz przeszukiwać zestawienia wszerz i wzdłuż. W tym artykule pokazujemy, jak to robić. Opisujemy kilka niezwykle uniwersalnych i elastycznych formuł, których możliwości na pewno przydadzą się w praktyce. Wystarczy dostosować odwołania do układu własnych danych, a w kilka chwil otrzymasz wyniki.
Odpowiedzi na pytania Czytelników
W arkuszu mam kilkadziesiąt kwot. Interesują mnie tylko niektóre z nich przekraczające pewną wartość. Potrzebuję policzyć, ile w danej kolumnie jest kwot większych niż 5000.
175
Zaawansowane sortowanie danych
Listy danych, które otrzymujemy od współpracowników, najczęściej są nieuporządkowane i praca z nimi jest utrudniona. Wartości tekstowe chcesz ułożyć w kolejności alfabetycznej, liczby od najmniejszej do największej, a daty od najpóźniejszej do najwcześniejszej. Ręczne uporządkowanie nawet niewielkiego zestawu danych zajmie sporo czasu. Co jeśli pozycji jest kilkaset lub kilka tysięcy? Z pomocą przychodzi Excel. Ma on narzędzie służące do sortowania danych, które jest proste w obsłudze i jednocześnie na tyle elastyczne, że będzie można z niego skorzystać praktycznie przy każdym zadaniu.
Odpowiedzi na pytania Czytelników
Często korzystam z funkcji JEŻELI w trakcie wykonywania analiz lub rozpoznawania poszczególnych wpisów na liście i oznaczania ich w kolumnie pomocniczej. Zbudowanie formuły opartej na tej funkcji nie jest specjalnie trudnym zadaniem ani pracochłonnym. Problem pojawia się wtedy, gdy w jednej formule chcę zamieścić większą liczbę warunków. Dodawanie kolejnych funkcji JEŻELI powoduje, że formuła staje się coraz dłuższa, a jej składnia mniej czytelna.
Najnowsze triki
Potrzebujesz wykonać podsumowanie warunkowe. Dane do obliczeń i kryteria znajdują się w osobnych kolumnach i nie są określone wartościami, a kolorami. Jak nakłonić Excela, aby podsumował te wartości, którym odpowiadają komórki oznaczone kolorem w innej kolumnie. Rozwiązaniem jest funkcja napisana w języku Visual Basic.
174
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 rozdziale pokażemy, jak za pomocą formuł przygotować odpowiednie obliczenia, a następnie wyniki przedstawić na czytelnym wykresie.
Ś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, jeśli np. chcesz 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.
Planowanie czasu przy pomocy formatowania warunkowego
Przy jakimkolwiek planowaniu czasu za pomocą Excela bardzo przydatne jest odznaczanie w arkuszu zaplanowanych godzin. Weźmy np. rozładunek towarów. W jednej kolumnie mamy wpisany czas (odstępy co 10 minut), a w kolejnej – liczbę paczek do rozładowania. W zależności od liczby paczek chcesz zarezerwować odpowiednią ilość czasu. W tym artykule pokażemy, jak osiągnąć ten efekt, wykorzystując do tego właśnie formatowanie warunkowe.
173
Odpowiedzi na pytania czytelników
Muszę przeprowadzić analizę czasu pracy maszyn produkcyjnych. Najpierw porównuję rzeczywiście przepracowane godziny z czasem nominalnym. W kolumnie wynikowej wstawiłem zwykłą różnicę, ale niestety w niektórych komórkach Excel wyświetlił dziwne znaki. Jak sobie z tym poradzić?
Najlepsze triki
W pierwszej kolumnie tworzonych wykazów znajduje się najczęściej numeracja porządkowa wierszy. Jeżeli do tabeli dopisujemy kolejne pozycje, to musisz także pamiętać o ręcznym zwiększaniu numeracji. Okazuje się, że możemy nakłonić Excela, aby robił to za nas.
