Hacki Excela dla osób zajmujących się raportowaniem i przetwarzaniem danych

2024 Autor: Malcolm Clapton | [email protected]. Ostatnio zmodyfikowany: 2024-01-13 02:41

W tym poście Renat Shagabutdinov, zastępca dyrektora generalnego wydawnictwa Mann, Ivanov i Ferber, dzieli się kilkoma fajnymi sztuczkami z Excela. Te wskazówki przydadzą się każdemu, kto zajmuje się różnego rodzaju raportowaniem, przetwarzaniem danych i tworzeniem prezentacji.

Ten artykuł zawiera proste techniki ułatwiające pracę w programie Excel. Są szczególnie przydatne dla tych, którzy zajmują się raportowaniem zarządczym, przygotowują różne raporty analityczne na podstawie pobrań z 1C i innych raportów, tworzą z nich prezentacje i diagramy dla kierownictwa. Nie udaję absolutnej nowości - w takiej czy innej formie techniki te były prawdopodobnie omawiane na forach lub wspominane w artykułach.

Proste alternatywy dla WYSZUKAJ. PIONOWO i WYSZUKAJ. POZIOMO, jeśli żądane wartości nie znajdują się w pierwszej kolumnie tabeli: WYSZUKAJ, INDEKS + SZUKAJ

Funkcje WYSZUKAJ. PIONOWO i WYSZUKAJ. POZIOMO działają tylko wtedy, gdy żądane wartości znajdują się w pierwszej kolumnie lub wierszu tabeli, z której planujesz pobrać dane.

W przeciwnym razie istnieją dwie opcje:

1. Użyj funkcji WYSZUKAJ.

   Ma następującą składnię: WYSZUKAJ (wyszukaj_wartość; wyszukaj_wektor; wyszukaj_wektor). Ale żeby to działało poprawnie, wartości z zakresu view_vector muszą być posortowane w kolejności rosnącej:

   

2. Użyj kombinacji funkcji PODAJ. POZYCJĘ i INDEKS.

   Funkcja PODAJ. POZYCJĘ zwraca numer porządkowy elementu w tablicy (z jej pomocą dowiesz się w którym wierszu tabeli znajduje się poszukiwany element), a funkcja INDEX zwraca element tablicy o podanym numerze (o czym dowiemy się za pomocą funkcji PODAJ. POZYCJĘ).

   

   Składnia funkcji:

   • SEARCH (search_value; search_array; match_type) – w naszym przypadku potrzebujemy typu dopasowania „dokładne dopasowanie”, odpowiada on liczbie 0.

   • INDEX (tablica; numer_linii; [numer_kolumny]). W takim przypadku nie trzeba podawać numeru kolumny, ponieważ tablica składa się z jednego wiersza.

Jak szybko wypełnić puste komórki na liście

Zadanie polega na wypełnieniu komórek w kolumnie wartościami u góry (tak, aby temat znajdował się w każdym wierszu tabeli, a nie tylko w pierwszym wierszu bloku książek na dany temat):

Wybierz kolumnę „Temat”, kliknij wstążkę w grupie „Strona główna”, przycisk „Znajdź i wybierz” → „Wybierz grupę komórek” → „Puste komórki” i zacznij wprowadzać formułę (czyli wstaw równą znak) i odnieś się do komórki u góry, klikając strzałkę w górę na klawiaturze. Następnie naciśnij Ctrl + Enter. Następnie możesz zapisać otrzymane dane jako wartości, ponieważ formuły nie są już potrzebne:

Jak znaleźć błędy w formule

Obliczanie odrębnej części wzoru

Aby zrozumieć złożoną formułę (w której inne funkcje są używane jako argumenty funkcji, to znaczy niektóre funkcje są zagnieżdżone w innych) lub znaleźć w niej źródło błędów, często trzeba obliczyć jej część. Są dwa proste sposoby:

1. Aby obliczyć część formuły bezpośrednio na pasku formuły, zaznacz tę część i naciśnij klawisz F9:

   

   W tym przykładzie wystąpił problem z funkcją SZUKAJ - zamieniono w niej argumenty. Należy pamiętać, że jeśli nie anulujesz obliczania części funkcji i naciśniesz Enter, to obliczona część pozostanie liczbą.

2. Kliknij przycisk Oblicz formułę w grupie Formuły na wstążce:

   

   W wyświetlonym oknie możesz krok po kroku przeliczyć formułę i określić, na jakim etapie i w jakiej funkcji występuje błąd (jeśli występuje):

   

Jak określić, od czego zależy wzór lub do którego się odnosi?

Aby określić, od których komórek zależy formuła, w grupie Formuły na wstążce kliknij przycisk Komórki mające wpływ:

Pojawiają się strzałki wskazujące, od czego zależy wynik obliczeń.

Jeśli wyświetlany jest symbol zaznaczony na obrazku na czerwono, formuła zależy od komórek na innych arkuszach lub w innych książkach:

Klikając na nią, możemy zobaczyć dokładnie, gdzie znajdują się wpływające komórki lub zakresy:

Obok przycisku „Komórki wpływające” znajduje się przycisk „Komórki zależne”, który działa w ten sam sposób: wyświetla strzałki od aktywnej komórki z formułą do komórek od niej zależnych.

Przycisk „Usuń strzałki”, znajdujący się w tym samym bloku, umożliwia usunięcie strzałek do komórek wpływających, strzałek do komórek zależnych lub obu typów strzałek naraz:

Jak znaleźć sumę (liczbę, średnią) wartości komórek z wielu arkuszy?

Załóżmy, że masz kilka arkuszy tego samego typu z danymi, które chcesz dodać, policzyć lub przetworzyć w inny sposób:

Aby to zrobić, w komórce, w której chcesz zobaczyć wynik, wprowadź standardową formułę, na przykład SUMA (SUM) i określ nazwę pierwszego i ostatniego arkusza z listy tych arkuszy, które musisz przetworzyć argument oddzielony dwukropkiem:

Otrzymasz sumę komórek o adresie B3 z arkuszy „Dane1”, „Dane2”, „Dane3”:

To adresowanie działa w przypadku arkuszy znajdujących się konsekwentnie … Składnia jest następująca: = FUNKCJA (pierwsza_lista: ostatnia_lista! Odniesienie do zakresu).

Jak automatycznie budować frazy szablonowe

Korzystając z podstawowych zasad pracy z tekstem w Excelu i kilku prostych funkcji, możesz przygotować szablonowe frazy do raportów. Kilka zasad pracy z tekstem:

• Łączymy tekst za pomocą znaku & (można go zastąpić funkcją CONCATENATE, ale to nie ma większego sensu).
• Tekst zawsze pisany jest w cudzysłowie, odniesienia do komórek z tekstem są zawsze bez.
• Aby uzyskać znak usługi „cudzysłów”, użyj funkcji ZNAK z argumentem 32.

Przykład tworzenia frazy szablonowej za pomocą formuł:

Wynik:

W tym przypadku oprócz funkcji ZNAK (do wyświetlania cudzysłowów) wykorzystywana jest funkcja JEŻELI, która pozwala na zmianę tekstu w zależności od tego, czy występuje pozytywny trend sprzedaży, oraz funkcja TEKST, która pozwala wyświetlić numer w dowolnym formacie. Jego składnia jest opisana poniżej:

TEKST (wartość; format)

Format jest określony w cudzysłowie, tak jakbyś wprowadzał niestandardowy format w oknie Formatowanie komórek.

Bardziej złożone teksty można również zautomatyzować. W mojej praktyce dochodziło do automatyzacji długich, ale rutynowych komentarzy do sprawozdawczości zarządczej w formacie „WSKAŹNIK spadł/wzrósł o XX w stosunku do planu, głównie ze względu na wzrost/spadek CZYNNIKA1 o XX, wzrost/spadek CZYNNIKA2 o XX YY…” ze zmieniającą się listą czynników. Jeśli często piszesz takie komentarze, a proces ich pisania może być algorytmizowany, warto raz zastanowić się nad stworzeniem formuły lub makra, które zaoszczędzą Ci przynajmniej części pracy.

Jak przechowywać dane w każdej komórce po konkatenacji?

Podczas scalania komórek zachowywana jest tylko jedna wartość. Excel ostrzega o tym podczas próby scalenia komórek:

W związku z tym, jeśli miałeś formułę zależną od każdej komórki, przestanie działać po ich połączeniu (błąd # N / A w wierszach 3-4 przykładu):

Aby scalić komórki i nadal zachować dane w każdym z nich (być może masz formułę jak w tym abstrakcyjnym przykładzie; być może chcesz scalić komórki, ale zachować wszystkie dane na przyszłość lub celowo je ukryć), scal wszystkie komórki w arkuszu, zaznacz je, a następnie użyj polecenia Malarz formatów, aby przenieść formatowanie do komórek, które chcesz połączyć:

Jak zbudować pivot z wielu źródeł danych

Jeśli potrzebujesz zbudować element przestawny z kilku źródeł danych naraz, będziesz musiał dodać „Kreator tabel przestawnych i wykresów” do wstążki lub panelu szybkiego dostępu, który ma taką opcję.

Możesz to zrobić w następujący sposób: „Plik” → „Opcje” → „Pasek narzędzi szybkiego dostępu” → „Wszystkie polecenia” → „Kreator tabel przestawnych i wykresów” → „Dodaj”:

Następnie na wstążce pojawi się odpowiednia ikona, klikając, która wywołuje tego samego kreatora:

Po kliknięciu pojawia się okno dialogowe:

W nim należy wybrać pozycję „W kilku zakresach konsolidacji” i kliknąć „Dalej”. W następnym kroku możesz wybrać „Utwórz jedno pole strony” lub „Utwórz pola strony”. Jeśli chcesz samodzielnie wymyślić nazwę dla każdego ze źródeł danych, wybierz drugą pozycję:

W kolejnym oknie dodaj wszystkie zakresy, na podstawie których zostanie zbudowany pivot i nadaj im nazwy:

Następnie w ostatnim oknie dialogowym określ, gdzie zostanie umieszczony raport tabeli przestawnej - na istniejącym lub nowym arkuszu:

Raport tabeli przestawnej jest gotowy. W filtrze „Strona 1” możesz w razie potrzeby wybrać tylko jedno ze źródeł danych:

Jak obliczyć liczbę wystąpień tekstu A w tekście B („Taryfa MTS SuperMTS” - dwa wystąpienia skrótu MTS)

W tym przykładzie kolumna A zawiera kilka wierszy tekstu, a naszym zadaniem jest sprawdzenie, ile razy każdy z nich zawiera wyszukiwany tekst znajdujący się w komórce E1:

Aby rozwiązać ten problem, możesz użyć złożonej formuły składającej się z następujących funkcji:

1. DLSTR (LEN) - oblicza długość tekstu, jedynym argumentem jest tekst. Przykład: DLSTR („maszyna”) = 6.
2. SUBSTITUTE - zastępuje określony tekst w ciągu tekstowym innym. Składnia: SUBSTITUTE (tekst; stary_tekst; nowy_tekst). Przykład: SUBSTITUTE („samochód”; „auto”; „”) = „komórka”.
3. UPPER - zastępuje wszystkie znaki w ciągu wielkimi literami. Jedynym argumentem jest tekst. Przykład: GÓRNA („maszyna”) = „SAMOCHÓD”. Potrzebujemy tej funkcji do wyszukiwania bez uwzględniania wielkości liter. W końcu GÓRNY („samochód”) = GÓRNY („Maszyna”)

Aby znaleźć wystąpienie określonego ciągu tekstowego w innym, musisz usunąć wszystkie jego wystąpienia w oryginalnym i porównać długość wynikowego ciągu z oryginalnym:

DLSTR („Taryfa MTS Super MTS”) - DLSTR („Taryfa Super”) = 6

A następnie podziel tę różnicę przez długość szukanego ciągu:

6/ DLSTR („MTS”) = 2

Dokładnie dwa razy linia „MTS” jest zawarta w oryginale.

Pozostaje jeszcze napisać ten algorytm w języku formuł (oznaczamy „tekstem” tekst, w którym szukamy wystąpień, a „poszukiwanym” – tym, którego liczbą wystąpień nas interesuje):

= (DLSTR (tekst) -LSTR (SUBSTITUTE (UPPER (tekst); UPPER (szukaj), ""))) / DLSTR (szukaj)

W naszym przykładzie formuła wygląda tak:

= (DLSTR (A2) -LSTR (ZAMIENNIK (GÓRNE (A2), GÓRNE ($E $ 1), „”))) / DLSTR ($E $ 1)