Excel lifehack dla osób zajmujących się przetwarzaniem danych i raportowania

Renat nie jest pierwszy raz, kiedy autor mówi gość na Layfhakere. Wcześniej wydaliśmy doskonały materiał z nim o tym, jak stworzyć plan treningowy: głównym książka i zasobów internetowych, a także algorytm przyrostowy stworzyć plan treningowy.

Ten artykuł zawiera kilka prostych technik, które pomagają uproszczenie pracy w programie Excel. są one szczególnie przydatne dla osób zaangażowanych w sprawozdawczości zarządczej, przygotowuje szereg analityczny raportów na podstawie wyładunków raportów 1C i innych form tych prezentacji i wykresów dla przywództwo. Nie udawać, że to absolutna nowość - w takiej czy innej formie, na pewno te metody omawiane na forach lub o których mowa w art.

Prosta alternatywa dla WYSZUKAJ.PIONOWO i HLOOKUP, jeśli wymagane wartości nie są w pierwszej kolumnie tabeli: Zobacz, indeks + Match

WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) I PGR (HLOOKUP) działają tylko wtedy, gdy wartości docelowe są w pierwszej kolumnie lub wierszu tabeli, z którego chcesz pobrać dane.

W innych przypadkach, istnieją dwie możliwości:

1. Funkcja użycie VIEW (WYSZUKAJ).
   Ona ma następującą składnię: oglądanie (szukana_wartość Szukaj wektor; vektor_rezultata). Ale za to, aby działać poprawnie musisz wartości zakresu Szukaj wektor Zostały one posortowane w kolejności rosnącej:
2. MATCH użyć kombinacji funkcji (dopasowanie), a indeks (wskaźnik).
   Funkcja PODAJ.POZYCJĘ zwraca numer seryjny elementu tablicy (z jego pomocą można wybrać, w wierszu tabeli żądany element) i funkcję indeksu zwraca element macierzy o określonej liczby (wiemy funkcji MATCH).Składnia opcje:
   • MATCH (szukana_wartość massiv_poiska; match_type) - w naszym przypadku musimy rodzaj porównania „dokładnym dopasowaniu”, ona odpowiada liczbie 0.
   • INDEX (tablicy; ROW_NUM; [Column_num]). W tym przypadku nie jest konieczne, aby określić liczbę kolumn jak matryca składa się z pojedynczej linii.

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

Zadanie - wypełnienia komórki o wartości w kolumnie powyżej (zależnie od stojących w każdym wierszu tabeli, nie tylko w pierwszym rzędzie bloków książek na ten temat)

Wybierz kolumnę „osobnik”, naciśnij na taśmie w grupie przycisku „Start” w „Znajdź i wybierz» → «Zaznacz grupę komórek» → «Blank komórkowa „i rozpoczęcia wprowadzania wzoru (tj umieścić znak równości) i odnosi się do komórki z góry, po prostu przez naciśnięcie strzałek klawiatura. Następnie naciśnij Ctrl + Enter. Po tym, dane mogą być zapisywane jako wartość, ponieważ wzory nie są już potrzebne:

Jak znaleźć błędy w formule

Obliczenie wzoru część oddzielna

Zrozumieć skomplikowany wzór (w której jako parametry funkcyjne za pomocą innego funkcja, czyli niektóre funkcje są wbudowane w inne), lub znaleźć w nim źródło błędów, często trzeba obliczyć częścią. Istnieją dwa proste sposoby:

1. Aby obliczyć właściwą część wzoru w pasku formuły, wybrać część, a następnie naciśnij klawisz F9:
   
   W tym przykładzie, nie było problemu z funkcji wyszukiwania (Search) - były mieszane argumenty górę. Ważne jest, aby pamiętać, że jeśli nie anulować obliczanie funkcji i naciśnij Enter, a następnie oblicza się część pozostanie numer.
2. Nacisnąć „Oblicz wzór” w „grupy” wzoru na taśmie:
   
   
   W oknie, które się pojawi, można obliczyć krok po kroku wzór i określić na jakim etapie iw jakiej funkcja błędu (jeśli występują):
   

Jak ustalić, co decyduje o tym wzorze lub odwoływać

Aby ustalić, które z komórek zależy od wzoru określonego w grupie z „wzoru” na wstążce kliknij na przycisk „Ślad precedensów”:

Są strzałki wskazujące, z których wynik zależy.

Jeśli na wybranym obrazie w kolorze czerwonym pojawi się symbol, wzór zależy od komórek, które są w innych listach lub w innych książkach:

Kliknięcie na niego, widzimy dokładnie, gdzie wpływ na komórki lub zakresu:

Obok „ślad precedensów” znajduje się przycisk „utrzymaniu”, pracujący w ten sam sposób: wyświetla strzałki z aktywnej komórki z formułą do komórek, które zależą od niego.

Przycisk „Usuń” strzałki znajdujące się w tym samym bloku, pozwala na usunięcie precedensów opieką, lub oba rodzaje naraz strzały:

Jak znaleźć ilości (liczba, średnia) wartości komórek z wielu arkuszy

Powiedzmy, że masz kilka podobnych arkusza z danymi, które chcesz dodać, znaleźć lub przetwarzane w inny sposób:

Aby to zrobić, komórka, w której chcesz zobaczyć wynik, wprowadź standardowej formuły, takie jak suma (SUM) i wprowadzić argument z dwukropkiem i nazwisko pierwszego arkusza z listy arkuszy, czego potrzebujesz proces:

Otrzymasz ilość komórek adresowej B3 z płyty „Dane1”, „Data2”, „danych3”:

Takie adresowanie prace dotyczące kart ułożonych sukcesywnie. Składni: = funkcji (pervyy_list: posledniy_list odniesienie do zakresu!).

Jak automatycznie zbudować stereotypowe zwroty

Korzystanie z podstawowymi zasadami pracy z tekstem w programie Excel i kilka prostych funkcji, można przygotować zwroty stereotypowe raportów. Kilka zasad pracy z tekstem:

• Łączyć tekst ze znakiem & (można zastąpić jego CONCATENATE funkcyjny (CONCATENATE), ale to nie jest dużo używać).
• Tekst jest zawsze napisane w cudzysłowie, odniesienia komórkę z tekstem - nie zawsze.
• Aby uzyskać szczególny charakter „cytaty”, używając funkcji znaków (char) argument 32.

Przykład tworzenia szablonu za pomocą wyrażenia wzory:

wyniki:

W tym przypadku, oprócz funkcji SYMBOL (char) (aby wyświetlić cudzysłowów) za pomocą funkcji JEŻELI (IF), która pozwala na zmianę tekstu w W zależności od tego, czy istnieje pozytywna tendencja w sprzedaży, a funkcja tekstu (Text), co pozwala na wyświetlanie numeru w dowolnym Format. Jego składnia jest opisany poniżej:

TEXT (wartość; format)

Format jest określony w cudzysłowie w taki sam sposób, jak w przypadku wpisania format niestandardowy w „Formatowanie komórek”.

Można zautomatyzować bardziej złożonych tekstów. W mojej praktyce było zautomatyzować długie, ale rutynowe komentarze na rachunkach zarządzania w formacie „spadła / różowy w XX w sprawie planu głównie z powodu wzrostu / spadku FAKTORA1 XX, wzrostu / spadku FAKTORA2 YY... »ze zmieniającym listy czynników. Jeśli piszesz takie komentarze często i proces pisania puszki algorytmizacji - Warto raz zastanawiało stworzyć formułę lub makro, które pozwoli Ci zaoszczędzić od przynajmniej części dzieła.

Jak zapisać dane w każdej komórce po połączeniu

Przy łączeniu komórki zachował tylko jedną wartość. Excel ostrzega podczas próby scalania komórek:

W związku z tym, jeśli miał formułę, która zależy od każdej komórce, to przestanie działać po ich Union (bug # N / A w liniach 3-4 przykłady):

Komórkom łączyć, zachowując dane w każdej z nich (być może masz wzór, jak w tym przykładzie, abstrakcyjne; Może chcesz się połączyć komórki, ale zachować wszystkie dane w przyszłości, lub aby ukryć swoje intencje), połączyć dowolną komórkę na arkusz, zaznacz je, a następnie użyć polecenia „Format” Malarz formatu transferu na komórkach, które trzeba i łączyć:

Jak zbudować podsumowanie kilku źródeł danych

Jeśli trzeba zbudować konsolidowane z wielu źródeł danych, konieczne jest dodanie do taśmy lub paska narzędzi Szybki dostęp „Kreator tabel przestawnych i wykresów”, w którym istnieje taka opcja.

Można to zrobić w następujący sposób: „File» → «Ustawienia» → «Pasek narzędzi Szybki dostęp» → «Cały zespół» → «Kreator tabel przestawnych i schematy» → «Dodaj»:

Po tym, że taśma będzie odpowiednia ikona, której kliknięcie powoduje dodanie mistrza:

Po kliknięciu na niego, okno dialogowe:

W nim trzeba będzie wybrać „kilku zespołach konsolidacji” i kliknij „Dalej”. W następnej sekcji, można wybrać „Utwórz pojedyncze pole strony” lub „Utwórz pole strony.” Jeśli chcesz sobie wymyślić nazwy dla każdego ze źródeł danych - aby wybrać drugą pozycję:

W następnym oknie należy dodać wszystkich zakresach, na podstawie których można zbudować podsumowanie, i poprosić ich nazwy:

Po tym punkcie, będzie gospodarzem raport tabeli przestawnej w ostatnim oknie dialogowym - na istniejącym lub nowym arkuszu:

Raport tabeli przestawnej gotowy. Filtr „Page 1” można wybrać tylko jedną z tych źródeł danych, w razie potrzeby:

Jak obliczyć liczbę wystąpień tekstu w tekście B (SuperMTS «MTS taryfowe” - dwóch wystąpień MTS skrót)

W tym przykładzie, kolumna A, istnieje kilka linii tekstu, a naszym zadaniem - aby dowiedzieć się, ile razy każdy z nich spełnia wymaganego tekstu w komórce E1:

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

1. DŁ (DŁ) - oblicza długość tekstu, jeden argument - tekstu. Przykład Len ( "urządzenie") = 6.
2. Zastępca (Zastępca) - zastępuje ciąg tekstowy pewien tekst z innej. Składnia: Zastępca (tekst; star_tekst; nov_tekst). Przykład: zastępczych ( "samochód", "samochodu", "") = "komórkowa".
3. GÓRNA (górny) - zastępuje wszystkie znaki w ciągu znaków na wielkie litery. Jedynym argumentem - tekst. Przykład: Górny ( "urządzenie") = "maszyny". Funkcja ta będzie potrzebna, aby poszukiwanie wielkości liter. Po tym wszystkim, GÓRNA ( "Maszyna") = UPPER ( "Maszyna")

Aby znaleźć wpisu ciągu tekstowego do drugiego, trzeba usunąć wszystkich jego wystąpień w oryginale i porównać długość łańcucha od oryginału:

LEN ( "Taryfa Super MTS MTS") - LEN ( "Super Cena") = 6

A następnie podzielić przez różnicę długości linii, szukaliśmy:

6 / DŁ ( "MTS") = 2

Że jest dwa razy „MTS” linia jest w oryginale.

Pozostaje być napisany w języku formuł algorytmu (oznaczonego „tekst” tekst, który szukamy wpisu i „życzenia” - liczba zdarzeń, które nas interesuje):

= (DŁ (tekst) -DLSTR (zastępcze (górny (tekst) Górny (poszukiwane); ""))) / DŁ (poszukiwane)

W tym przykładzie, wzór jest następujący:

= (DŁ (A2) -DLSTR (zastępcze (górny (A2) górny ($ e $ 1) ""))) / DŁ ($ e $ 1)