Spis treści
Tabele przestawne są dobre dla każdego – szybko obliczają i są konfigurowane elastycznie, a projekt można w nich elegancko nawinąć, jeśli jest to wymagane. Ale jest też kilka much w maści, w szczególności brak możliwości stworzenia podsumowania, gdzie obszar wartości nie powinien zawierać liczb, ale tekst.
Spróbujmy obejść to ograniczenie i wymyślmy „kilka kul” w podobnej sytuacji.
Załóżmy, że nasza firma transportuje swoje produkty w kontenerach do kilku miast w naszym kraju i Kazachstanu. Kontenery wysyłane są nie częściej niż raz w miesiącu. Każdy pojemnik ma numer alfanumeryczny. Jako dane wyjściowe jest standardowa tabela z listą dostaw, z której należy zrobić pewnego rodzaju podsumowanie, aby wyraźnie zobaczyć liczbę kontenerów wysyłanych do każdego miasta i każdego miesiąca:
Dla wygody zróbmy z góry tabelę z danymi początkowymi „inteligentną” za pomocą polecenia Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę) i nadaj jej imię Dostawy Zakładka Konstruktor (Projekt). W przyszłości uprości to życie, ponieważ. możliwe będzie użycie nazwy tabeli i jej kolumn bezpośrednio w formułach.
Metoda 1. Najłatwiejsza – użyj Power Query
Dodatek Power Query to niezwykle wydajne narzędzie do ładowania i przekształcania danych w programie Excel. Ten dodatek jest domyślnie wbudowany w program Excel od 2016 roku. Jeśli masz program Excel 2010 lub 2013, możesz go pobrać i zainstalować osobno (całkowicie za darmo).
Cały proces dla jasności przeanalizowałem krok po kroku w poniższym filmie:
Jeśli nie można użyć dodatku Power Query, możesz przejść na inne sposoby — za pomocą tabeli przestawnej lub formuł.
Metoda 2. Podsumowanie pomocnicze
Dodajmy jeszcze jedną kolumnę do naszej oryginalnej tabeli, gdzie za pomocą prostej formuły obliczamy liczbę każdego wiersza w tabeli:
Oczywiście potrzebne jest -1, ponieważ w naszej tabeli mamy jednowierszowy nagłówek. Jeśli twoja tabela nie znajduje się na początku arkusza, możesz użyć nieco bardziej złożonej, ale uniwersalnej formuły, która oblicza różnicę w liczbach bieżącego wiersza i nagłówka tabeli:
Teraz w standardowy sposób zbudujemy tabelę przestawną żądanego typu na podstawie naszych danych, ale w polu wartości opuścimy pole Numer linii zamiast tego, czego chcemy pojemnik:
Ponieważ nie mamy kilku kontenerów w tym samym mieście w tym samym miesiącu, nasze zestawienie w rzeczywistości poda nie ilość, ale numery linii kontenerów, których potrzebujemy.
Dodatkowo w zakładce można wyłączyć sumy ogólne i sumy częściowe Konstruktor — sumy ogólne и Podsumy (Projekt — sumy końcowe, sumy częściowe) i w tym samym miejscu przełącz podsumowanie na wygodniejszy układ tabeli za pomocą przycisku Zgłoś makietę (Układ raportu).
Jesteśmy więc już w połowie drogi do wyniku: mamy tabelę, w której na przecięciu miasta i miesiąca znajduje się numer wiersza w tabeli źródłowej, w którym leży potrzebny nam kod kontenera.
Teraz skopiujmy podsumowanie (do tego samego lub innego arkusza) i wklejmy je jako wartości, a następnie wprowadź naszą formułę w polu wartości, która wyodrębni kod kontenera według numeru wiersza znajdującego się w podsumowaniu:
Funkcjonować IF (JEŚLI), w tym przypadku sprawdza, czy następna komórka w podsumowaniu nie jest pusta. Jeśli jest pusty, wypisz pusty ciąg tekstowy „”, tj. pozostaw komórkę pustą. Jeśli nie jest pusty, wyodrębnij z kolumny Pojemnik tabela źródłowa Dostawy zawartość komórki według numeru wiersza za pomocą funkcji INDEKS (INDEKS).
Być może jedynym niezbyt oczywistym punktem tutaj jest podwójne słowo Pojemnik w formule. Taka dziwna forma pisania:
Zaopatrzenie[[Pojemnik]:[Pojemnik]]
… jest potrzebne tylko do odniesienia się do kolumny Pojemnik był bezwzględny (jak odwołanie ze znakami $ dla zwykłych „nieinteligentnych” tabel) i nie przeskakiwał do sąsiednich kolumn podczas kopiowania naszej formuły po prawej stronie.
W przyszłości przy zmianie danych w tabeli źródłowej Dostawy, musimy pamiętać o zaktualizowaniu naszego podsumowania pomocniczego o numery wierszy, klikając je prawym przyciskiem myszy i wybierając polecenie Zaktualizuj i zapisz (Odświeżać).
Metoda 3. Formuły
Ta metoda nie wymaga tworzenia pośredniej tabeli przestawnej i ręcznej aktualizacji, ale wykorzystuje „ciężką broń” Excela – funkcję SUMMESLIMN (SUMATY). Zamiast szukać numerów wierszy w podsumowaniu, możesz je obliczyć za pomocą tego wzoru:
Przy pewnej zewnętrznej objętości jest to w rzeczywistości standardowy przypadek użycia funkcji sumowania selektywnego SUMMESLIMNA, który sumuje numery wierszy dla danego miasta i miesiąca. Ponownie, ponieważ nie mamy kilku kontenerów w tym samym mieście w tym samym miesiącu, nasza funkcja w rzeczywistości poda nie ilość, ale sam numer linii. A potem funkcja znana już z poprzedniej metody INDEKS Możesz również wyodrębnić kody kontenerów:
Oczywiście w tym przypadku nie trzeba już myśleć o aktualizacji podsumowania, ale przy dużych tabelach funkcja SUMMESLI może być zauważalnie powolny. Wtedy będziesz musiał wyłączyć automatyczną aktualizację formuł lub skorzystać z pierwszej metody – tabeli przestawnej.
Jeśli wygląd podsumowania nie jest zbyt odpowiedni dla twojego raportu, możesz wyodrębnić z niego numery wierszy do tabeli końcowej nie bezpośrednio, jak to zrobiliśmy, ale za pomocą funkcji POBIERZ.DANE.TABELI.PIVOT (POBIERZ.DANE.PIVOT). Jak to zrobić, można znaleźć tutaj.
- Jak utworzyć raport za pomocą tabeli przestawnej
- Jak skonfigurować obliczenia w tabelach przestawnych
- Selektywne liczenie za pomocą SUMIFS, COUNTIFS itp.