216

Wyszukiwanie wartości najbliższej

Wbudowane narzędzia i funkcje Excela pozwalają przeprowadzić różne wyszukiwania: można szukać konkretnej wartości, a także wartości skrajnych (minimum lub maksimum). Jednak co w sytuacji, gdy jeśli potrzebujemy wyszukać wartość najbliższą poszukiwanej?

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.

Usuwanie niepotrzebnych apostrofów w komórkach

Mamy skoroszyt Excela zawierający wykaz niepoprawnie sformatowanych numerów NIP. Wszystkie cyfry są umieszczone w jednym ciągu, a każdy wpis w komórce jest poprzedzony apostrofem, np. ‘1112223344. Mimo że komórkom nadajemy format „Specjalne – nr NIP”, to sposób wyświetlania numerów pozostaje bez zmian. Przyczyną jest apostrof na początku numeru. Niestety, komórek jest tak dużo, że ręczne poprawianie nie wchodzi w grę, a polecenie Zamień nie zdaje egzaminu. Jak pozbyć się tych uciążliwych apostrofów?

215

W jaki sposób szybko ukrywać i odkrywać większą liczbę kolumn?

Mam zestawienie sprzedaży z poszczególnych miesięcy wraz z kwartalnymi podsumowaniami. Potrzebuję wygodnie ukrywać kolumny z danymi miesięcznymi, ponieważ każdorazowe powtarzanie tej czynności jest czasochłonne i będziesz mógł przy tym zrobić błąd. Czy jest jakiś prosty sposób na szybkie ukrywanie i odkrywanie wielu kolumn nieznajdujących się obok siebie?

System raportowania wskaźników efektywności pracy przedstawicieli handlowych

Pracownik firmy otrzymał za zadanie dobór wskaźników efektywności, które zostaną użyte do oceny pracy przedstawicieli handlowych w firmie. KPI (Key Performance Indicators) powinny opisywać dwa obszary skuteczności przedstawiciela. Pierwszy to skuteczność w sferze sprzedaży, natomiast druga to skuteczność promowania firmy, utrzymywania relacji z obecnymi klientami oraz pozyskiwania nowych. Po zbadaniu przez pracownika możliwości systemowych oraz informacji dostępnych w systemie CRM wykorzystywanym w organizacji wyznaczył on 9 różnych wskaźników. Dodatkowo pracownik ma zaproponować sposób raportowania, śledzenia i prezentowania KPI kadrze zarządzającej.

Sumy częściowe i grupowanie danych

Załóżmy, że mamy wykaz wszystkich transakcji, które udało się sfinalizować handlowcom w minionym roku. Planujesz 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 będziesz mógł przeglądać potrzebne w danej chwili zestawy danych.

214

Znikające zero na początku wartości liczbowych

Jeżeli przenosimy do Excela różne numery identyfikacyjne, na pewno spotkamy się z pewną denerwującą właściwością arkusza. Otóż przy próbie ręcznego wprowadzenia lub wklejenia do komórki numeru rozpoczynającego się od zera program automatycznie usuwa ten znak.

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.

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? Będziesz mógł 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 będziesz mógł odkrywać potrzebne w danej chwili zestawy danych.

213

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 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.

W jaki sposób dodawać do wykresu własne znaczniki?

Przygotowałam porównawczą prezentację przychodów dwóch firm. Wykres jest typu liniowego ze znacznikami w formie punktów. Wiem, że można zmieniać kolory i kształty punktów. Niestety, nie wiem, jak wprowadzić niestandardowe znaczniki serii, a jest mi to potrzebne do prezentacji. W miejscu znaczników powinny być wstawione znaki firmowe w taki sposób, aby linie były jednoznacznie kojarzone z danym podmiotem gospodarczym. Czy jest to możliwe do wykonania w Excelu?

212

Zamiana wartości czasu na liczby

Jeśli w obliczeniach korzystasz z wartości czasu, konieczne może się okazać przekształcenie ich na zapis dziesiętny. Choćby przy prostym działaniu polegającym na przemnożeniu czasu pracy przez stawkę godzinową niezbędne jest posłużenie się ułamkiem dziesiętnym, a nie czasem w postaci godzin i minut.

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 chcemy 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ędziesz mógł z niego skorzystać praktycznie przy każdym zadaniu.

Sumowanie warunkowe według koloru

Na pewno nieraz zdarzyło się korzystać z funkcji SUMA.JEŻELI, która sumuje dane z jednej kolumny na podstawie odpowiadających im wartości w innej kolumnie (kryteriów). Czasami jednak kryteria nie są określone wartościami, ale kolorami. Takiego obliczenia funkcja SUMA.JEŻELI nie wykona.

211

Zaawansowane zliczanie wartości tekstowych

Analizy i raporty, które chcesz sporządzić, bardzo często opierają się na zestawieniach cząstkowych przygotowanych przez innych pracowników. Nie wszyscy wprowadzają dane do arkusza w odpowiedni sposób i zwykle nie przywiązują wagi do ujednoliconego zapisu. Niestety, nadanie spójności wartościom w arkuszu spada później na Ciebie.

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.

Wysyłanie arkusza pocztą e-mail

Gotowy arkusz z raportem sprzedaży planujesz przekazać swojemu szefowi. Najprostszy sposób to wysyłka tego skoroszytu pocztą elektroniczną.

210

Wyszukiwanie wartości leżącej na lewo od szukanej

Zadaniem, z którym możesz dość często się spotykać, jest przeszukiwanie arkusza pod kątem wystąpienia określonej wartości. Przy czym interesuje Cię nie sama wartość poszukiwana, ale leżąca „na lewo” od niej. Nasuwa się pomysł na wykorzystanie funkcji WYSZUKAJ.PIONOWO. Nie jest on do końca dobry, ponieważ funkcja ta nie potrafi odnieść się do danych leżących po lewej stronie wartości poszukiwanej.

Wyszukiwanie błędów w obliczeniach

Formuły w arkuszach to rzecz niezwykle popularna. Właśnie w nich tkwi potencjał Excela: dzięki formułom złożonym z funkcji arkusza można wykonać niezwykle różnorodne i skomplikowane obliczenia. Jednak czy otrzymywane wyniki obliczeń są na pewno poprawne? Czy brak komunikatów o błędach jest gwarancją poprawnego działania formuły? Oczywiście nie. Odszukanie błędu w formule, która nie komunikuje jego wystąpienia, bywa niezwykle trudne.

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.

209

Wyliczenie godzinowego kosztu pracy

Excel idealnie nadaje się do wszelkich rozliczeń czasu pracy. Niestety użytkownicy często wpadają w tę samą pułapkę i wykonują obliczenia arytmetyczne na wartościach czasu w taki sam sposób jak na zwykłych liczbach. Otrzymane wyniki mogą rozczarować i bywa, że wykonywana kalkulacja kończy się komentarzem: „Excel źle liczy”. Okazuje się, że wystarczy poznać pewne zasady, jakimi program kieruje się przy obsłudze wartości czasu.

Wybieranych danych do wykresu dwoma kliknięciami myszy

Dysponujesz zestawieniem sprzedaży produktów z ostatnich czterech miesięcy. Osiągnięte wyniki będziesz prezentowana spotkaniu kierownictwa firmy, dlatego potrzebujesz stworzyć uniwersalną prezentację, którą będziesz mógł błyskawicznie modyfikować.

Szybkie dodawanie do siebie cyfr 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?

208

Zastosowania funkcji LICZ.JEŻELI

Sumowanie to jedna z podstawowych operacji arytmetycznych. Nie zawsze jednak chcemy zliczyć wszystkie komórki w podanym zakresie, a tylko niektóre z nich.

Szybkie wyszukiwanie danych w dużych zestawieniach

Jeśli planujesz rejestrować dane dotyczące klientów, Excel idealnie nadaje się do tego celu. Z łatwością będziesz mógł 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.

Sumowanie warunkowe

Dodawanie komórek nie zawsze oznacza proste sumowanie określonego zakresu tabeli. Z tej porady dowiesz się, jak sumować komórki z określonego zakresu, które spełniają określone warunki.