Posortujmy tabelę składającą się z 2 kolumn za pomocą formuł. Będziemy sortować według jednej z kolumn tabeli (rozwiążemy 2 problemy: sortowanie tabeli według liczb i sortowanie według kolumny tekstowej). Skonfigurujmy formuły sortowania tak, aby po dodaniu nowych danych do tabeli źródłowej posortowana tabela zmieniała się dynamicznie. Dzięki temu zawsze będziesz mieć posortowaną tabelę bez interwencji użytkownika. Przeprowadzimy także sortowanie dwupoziomowe: najpierw według liczb, a następnie (w przypadku liczb powtarzających się) według kolumny tekstowej.

Niech będzie tabela składająca się z 2 kolumn. Jedna kolumna to tekst: Lista owoców; a drugi jest numeryczny Wielkość sprzedaży(patrz przykładowy plik).

Zadanie 1 (Sortowanie tabeli według kolumny liczbowej)

Należy posortować wiersze tabeli według zawartości kolumny numerycznej (według wielkości sprzedaży). Zakłada się, że użytkownik na bieżąco uzupełnia wiersze tabeli, dlatego konieczne jest pisanie formuł uwzględniających dodane wartości.

Dla jasności wartości w kolumnie Wielkość sprzedaży podświetlone za pomocą (). Powtarzające się wartości są również podświetlone na żółto.

Notatka: Problem sortowania pojedynczej kolumny (listy) został rozwiązany w artykułach i.

Rozwiązanie 1

Jeśli masz pewność, że kolumna liczbowa nie zawiera żadnych wartości, problem jest łatwy do rozwiązania:

• Posortuj kolumnę liczbową za pomocą funkcji LARGE() (patrz artykuł);
• Funkcja VLOOKUP() lub kilka funkcji INDEKS()+DOPASUJ() wybierz wartości z kolumny tekstowej według odpowiadającej im wartości liczbowej.

Jednak w rzeczywistych problemach kolumna liczbowa może zawierać powtórzenia, a ponieważ funkcja VLOOKUP() w przypadku powtórzeń zawsze wybiera tylko pierwszą wartość od góry (patrz artykuł), to podejście to nie jest odpowiednie (nazwy Owoce będą wyświetlane nieprawidłowo).

Dlatego mechanizm sortowania będzie musiał zostać zaimplementowany inaczej.

INDEKS(Sprzedaż;
OKRĄGŁ(REMAT(NAJWIĘKSZY(
--(COUNTIF(Sprzedaż;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
WIERSZ()-WIERSZ($E$6));1)*1000;0)
)

Ta formuła sortuje kolumnę Wielkość sprzedaży(zakres dynamiczny Obroty) w kolejności malejącej. Luki w tabeli źródłowej są niedozwolone. Liczba wierszy w tabeli źródłowej musi być mniejsza niż 1000.

Przyjrzyjmy się formule bardziej szczegółowo:

• Formuła COUNTIF(sprzedaż;"<"&Продажи) zwraca tablicę (4:5:0:2:7:1:3:5). Oznacza to, że liczba 64 (z komórki B7 oryginalną tabelę, tj. pierwsza liczba z zakresu Obroty) więcej niż 4 wartości z tego samego zakresu; numer 74 (z komórki B8 oryginalną tabelę, tj. druga liczba z zakresu Obroty) więcej niż 5 wartości z tego samego zakresu; następna liczba 23 jest najmniejsza (nie jest większa od nikogo) itd.
• Zamieńmy teraz powyższą tablicę liczb całkowitych na tablicę liczb z częścią ułamkową, gdzie część ułamkowa będzie zawierać numer pozycji liczby w tablicy: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). Jest to realizowane za pomocą wyrażenia &","&POWTÓRZ("0";3-DŁUGOŚĆ(WIERSZ(Sprzedaż)-WIERSZ(6 $E$)))&WIERSZ(Sprzedaż)-WIERSZ(6$E$)) To właśnie w tej części wzoru obowiązuje ograniczenie do 1000 wierszy w tabeli źródłowej (patrz wyżej). W razie potrzeby można to łatwo zmienić, ale jest to bezcelowe (patrz sekcja dotycząca szybkości obliczeniowej poniżej).
• Funkcja LARGE() sortuje powyższą tablicę.
• Funkcja ROD() zwraca część ułamkową liczby, czyli liczby pozycji/1000, np. 0,005.
• Funkcja ROUND() po pomnożeniu przez 1000 zaokrągla do liczby całkowitej i zwraca numer pozycji. Teraz wszystkie numery pozycji odpowiadają numerom kolumn wolumen sprzedaży, posortowane w kolejności malejącej.
• Funkcja INDEKS() po podaniu numeru pozycji zwraca odpowiadającą jej liczbę.

Podobną formułę można zapisać, aby wyświetlić wartości w kolumnie Owoce=INDEKS(Owoce,OKRĄGŁE(...))

W przykładowym pliku, ze względu na szybkość obliczeń (patrz poniżej), ten sam typ części wzoru, tj. wszystko wewnątrz funkcji ROUND() jest umieszczane w osobnej kolumnie J . Dlatego końcowe formuły w posortowanej tabeli wyglądają następująco: =INDEKS(Owoce,J7) I =INDEKS(Sprzedaż,J7)

Ponadto zmieniając w formule tablicowej funkcję DUŻY() na SMALL() uzyskujemy sortowanie w kolejności rosnącej.

Dla jasności wartości w kolumnie Wielkość sprzedaży podkreślone za pomocą ( Strona główna/ Style/ Formatowanie warunkowe/ Histogramy). Jak widać sortowanie działa.

Testowanie

Dodajmy teraz nowy wiersz do oryginalnej tabeli. W tabelach posortowanych dynamicznie musimy zadbać o odpowiednie sortowanie.

1. Do celi A15 tabela źródłowa wprowadź słowo Marchewka;
2. Do celi B15 Wchodzić Wielkość sprzedaży Marchew = 25;
3. Po wpisaniu wartości w kolumnach D I mi automatycznie wyświetli się tabela posortowana malejąco;
4. W posortowanej tabeli nowy wiersz będzie wyświetlany przedostatni.

Szybkość obliczania formuły

Na „przeciętnym” komputerze pod względem wydajności ponowne obliczenie pary takich formuły tablicowe, umiejscowiony w 100 liniach, jest praktycznie niewidoczny. W przypadku tabel zawierających 300 wierszy czas przeliczenia zajmuje 2-3 sekundy, co jest niewygodne. Lub musisz wyłączyć automatyczne przeliczanie arkuszy ( Wzory/Obliczenia/Opcje obliczeń) i okresowo naciskaj klawisz F9, albo porzuć stosowanie formuł tablicowych, zastępując je kolumnami z odpowiadającymi im formułami, albo całkowicie porzuć sortowanie dynamiczne na rzecz stosowania podejść standardowych (patrz następna sekcja).

Alternatywne podejścia do sortowania tabel

Posortujmy wiersze tabeli źródłowej za pomocą standardowego filtra (wybierz nagłówki tabeli źródłowej i kliknij CTRL+SHIFT+L). Z listy rozwijanej wybierz żądane sortowanie.

Otrzymamy wersję tabeli identyczną z naszą, jednak przy dodawaniu nowych wartości do tabeli będziemy musieli ponownie zastosować filtr.

Możesz także użyć narzędzia Sortowanie ( Dane/Sortuj i Filtruj/Sortuj). Aby to zrobić, musisz zaznaczyć wszystkie wartości tabeli źródłowej, nie licząc nagłówka, wywołać narzędzie Sortowanie, wybrać kolumnę, według której chcesz sortować i opcję sortowania.

Otrzymamy wersję tabeli identyczną z naszą, ale dodając nowe wartości, będziemy musieli także ponownie zastosować filtr.

Podobnie jak w poprzednim zadaniu załóżmy, że w kolumnie, według której przeprowadzane jest sortowanie, występują powtórzenia (powtarzają się nazwy Owoców).

Aby posortować tabelę, będziesz musiał utworzyć 2 kolumny usług (D i E).

=LICZBA JEŚLI(7 B $ 7: 14 $ B $;"<"&$B$7:$B$14)+1

Formuła ta jest analogiczna do wartości tekstowych (położenie wartości względem innych wartości na liście). Wartość tekstowa znajdująca się niżej w alfabecie ma wyższą „rangę”. Na przykład wartość Jabłka odpowiada maksymalnej „randze” wynoszącej 7 (wliczając powtórzenia).

W kolumnie E wpisz zwykłą formułę:

=LICZ.JEŻELI($D$6:D6,D7)+K7

Formuła ta uwzględnia powtórzenia wartości tekstowych i dostosowuje „rangę”. Teraz różnym wartościom Jabłek odpowiadają różne „rangi” - 7 i 8. Pozwala to wyświetlić listę posortowanych wartości. Aby to zrobić, użyj wzoru (kolumna G):

=INDEKS($B$7:$B$14,MATCH(ROW()-ROW($G$6),$E$7:$E$14,0))

Podobna formuła wyświetli odpowiedni wolumen sprzedaży (kolumna H).

Zadanie 2.1 (Sortowanie dwupoziomowe)

Teraz posortujmy oryginalną tabelę ponownie według wielkości sprzedaży. Ale teraz dla powtarzających się wartości (w kolumnie A są trzy wartości 74) wyświetlimy odpowiednie wartości w kolejności alfabetycznej.

W tym celu wykorzystamy wyniki Zadania 1.1 i Zadania 2.

Szczegóły w przykładowym pliku na arkuszu Zadanie2.

Listę możesz sortować alfabetycznie, numerycznie lub chronologicznie według maksymalnie trzech pól. Dla każdej kolumny można określić porządek sortowania rosnący i malejący.

Rosnący porządek sortowania:

Liczby zostaną posortowane w kolejności od najmniejszej liczby ujemnej do największej liczby dodatniej.

Wartości daty i godziny zostaną posortowane od najwcześniejszej do najpóźniejszej wartości.

Tekst zostanie posortowany alfabetycznie. W takim przypadku w pierwszej kolejności zostaną umieszczone wartości liczbowe określone jako tekst.

Podczas sortowania wartości logicznych w kolejności rosnącej, jako pierwsza zostanie wyświetlona wartość FAŁSZ, a po niej wartość PRAWDA.

Wartości błędów zostaną posortowane w kolejności, w jakiej zostały napotkane (z punktu widzenia sortowania wszystkie są równe).

Na końcu posortowanej listy pojawią się puste komórki.

Sortowanie malejące:

W przypadku sortowania malejącego Excel sortuje rekordy w odwrotnej kolejności opisanej powyżej. Na końcu listy nadal będą wyświetlane tylko puste komórki.

Niestandardowa kolejność sortowania:

Zamiast sortowania rosnącego i malejącego można także zastosować niestandardowy porządek sortowania określony na podstawie określonej listy niestandardowej.

Sortowanie listy

Aby posortować listę, umieść wskaźnik komórki wewnątrz listy i wykonaj polecenie Dane – Sortuj.

Program Excel automatycznie wybierze listę i wyświetli okno dialogowe Zakres sortowania, w którym można określić opcje sortowania.

Można ustawić sortowanie według maksymalnie trzech pól listy i określić inny porządek sortowania dla każdego pola.

Excel automatycznie rozpoznaje nazwy pól, jeśli format komórek zawierających nazwy różni się od formatu komórek zawierających dane.

Okno dialogowe Zakres sortowania.

Jeżeli dokonany przez program wybór zakresu nie jest do końca poprawny, należy ustawić przełącznik znajdujący się na dole okna dialogowego w żądanej pozycji (Identyfikuj pola poprzez „etykiety (pierwszy wiersz zakresu)” lub „oznaczenia kolumn arkusza”).

Opcje zakresu i sortowania określone w oknie dialogowym Sortuj zostaną zapisane i wyświetlone w oknie dialogowym przy następnym otwarciu.

Możesz także sortować kolumny zakresów zamiast wierszy. W tym celu w oknie dialogowym „Zakres sortowania” kliknij przycisk „Opcje” i w oknie dialogowym „Opcje sortowania”, w grupie „Sortuj”, ustaw przełącznik w pozycji „Kolumny zakresu”.

26. Filtrowanie danych w Excelu.

Filtrowanie danych na liście pozwala wyświetlić tylko te rekordy, które spełniają określone warunki.

Autofiltr

W przypadku zastosowania autofiltru nazwy pól zostaną uzupełnione listami rozwijanymi, z których można wybrać żądaną wartość pola lub ustawić własne kryterium.

Wkładanie Autofiltru

1. Umieść wskaźnik komórki wewnątrz listy.

2. W podmenu Dane – Filtr wybierz polecenie „Autofiltr”. Obok nazw pól pojawią się przyciski strzałek, które możesz kliknąć, aby otworzyć listę.

3. Otwórz listę pola, którego wartość chcesz zastosować jako filtr (kryterium wyboru). Lista pokaże wartości komórek wybranego pola.

4. Wybierz żądany element z listy. Na ekranie zostaną wyświetlone tylko te rekordy, które odpowiadają określonemu filtrowi.

5. W razie potrzeby wybierz żądany element z listy innego pola. Na ekranie zostaną wyświetlone tylko te rekordy, które spełniają wszystkie określone warunki filtrowania (warunki poszczególnych pól łączy się za pomocą operacji logicznej „AND”).

Przyciski otwierające autofiltry dla pól, dla których określono warunki filtrowania, mają kolor niebieski.

Jeśli przed wykonaniem polecenia Autofiltru wybrałeś jedną lub więcej kolumn, listy rozwijane zostaną dodane tylko do odpowiednich pól.

Aby ponownie wyświetlić wszystkie wpisy listy na ekranie, należy wykonać polecenie „Wyświetl wszystko” z podmenu Dane – Filtr.

Kryterium filtrowania dla pojedynczego pola możesz usunąć wybierając pozycję „Wszystkie” na liście autofiltrów dla tego pola.

Aby wyłączyć funkcję autofiltru (usunąć listy rozwijane), wybierz ponownie polecenie Dane – Filtr – Autofiltr.

Stosowanie niestandardowego autofiltru

Korzystając z niestandardowego autofiltru, możesz połączyć poszczególne warunki wyboru rekordów za pomocą operatorów logicznych.

Wstaw autofiltr do listy wybierając polecenie Dane – Filtr – Autofiltr.

Otwórz listę autofiltrów dla żądanego pola i wybierz w nim element (Warunek).

W otwartym oknie dialogowym „Custom AutoFilter” (ryc. 6.3.27.) określ pierwsze kryterium.

Wybierz operator logiczny łączący pierwsze i drugie kryterium.

Okno dialogowe „Niestandardowy autofiltr”.

W niestandardowym autofiltrze możesz ustawić jedno lub dwa kryteria dla pojedynczego pola. W tym drugim przypadku można je połączyć operatorem logicznym „AND” lub „OR”.

Ustaw drugie kryterium.

Kliknij przycisk „OK”. Excel będzie filtrować rekordy na podstawie określonych przez Ciebie kryteriów.

Zaawansowany filtr

Aby ustawić złożone warunki filtrowania danych listowych, Excel udostępnia użytkownikowi tzw. filtr zaawansowany.

Zakres kryteriów

Kryteria można ustawić w dowolnym wolnym miejscu arkusza. W zakresie kryteriów możesz wprowadzić i połączyć dwa typy kryteriów:

Proste kryteria: program porówna zawartość pól z określonym kryterium (podobnie jak przy użyciu autofiltru).

Kryteria obliczone: W tym przypadku możesz także określić wartości zwracane przez formułę, których nie ma na liście.

Podczas określania prostych kryteriów należy najpierw określić nazwy pól w zakresie kryteriów i można wprowadzić tylko te nazwy pól, dla których określane są kryteria.

W wierszach bezpośrednio pod linią nazw pól określa się kryteria. Podczas określania kryteriów w programie Excel nie jest rozróżniana wielkość liter. Czy przy określaniu kryteriów można używać symboli wieloznacznych? I *.

Wszystkie kryteria podane w jednym wierszu muszą zostać wykonane jednocześnie (odpowiada operatorowi logicznemu „AND”). Aby określić połączenie kryteriów za pomocą operatora „LUB”, określ kryteria w różnych wierszach.

Stosowanie zaawansowanego filtra

Po utworzeniu zakresu kryteriów można uruchomić filtr zaawansowany i przefiltrować dane listy.

1. Umieść wskaźnik komórki wewnątrz listy. W takim przypadku Excel automatycznie rozpozna zakres listy i zaprezentuje odnośnik do zakresu w oknie dialogowym.

2. Wykonaj polecenie Dane – Filtr – Filtr zaawansowany. Umieść kursor wejściowy w polu „Zakres warunku” i zaznacz odpowiedni zakres w arkuszu.

3. Zamknij okno dialogowe klikając przycisk „OK”. Na ekranie zostaną teraz wyświetlone rekordy spełniające określone kryteria.

W każdym arkuszu można zastosować tylko jeden filtr zaawansowany.

Jeśli nie chcesz, aby w wyniku zastosowania filtra zaawansowanego wyświetlały się zduplikowane rekordy, zaznacz pole wyboru Tylko unikalne rekordy w oknie dialogowym Filtr zaawansowany.

Aby ustawić wyświetlanie wszystkich wpisów listy w arkuszu po przefiltrowaniu, należy uruchomić polecenie Dane – Filtr – Wyświetl wszystko.

Aby stworzyć wygodne i komfortowe warunki dla użytkownika podczas przeglądania i analizowania informacji umieszczonych w tabelach, program MS Excel zapewnia dość szerokie możliwości sortowania danych. Sortowanie rekordów w dużej bazie danych w Excelu...

Ułatwia użytkownikowi znalezienie interesujących go informacji.

Podczas sortowania wszystkie wiersze bazy danych zmieniają swoją lokalizację zgodnie z wybranym przez nas algorytmem, pozostając oczywiście niezmienione.

Trzy główne algorytmy sortowania w Excelu.

1. numeryczny wartości sortowane są według zasady „od najmniejszej do największej” lub odwrotnie.

2. Kolumny zawierające komórki zawierające tekst wartości są sortowane alfabetycznie w kierunku do przodu lub do tyłu.

3. Kolumny zawierające komórki zawierające Daktyle, sortowane są według zasady „od najstarszego do najnowszego” lub odwrotnie.

Sortowanie w Excelu 2007 i nowszych wersjach można dodatkowo wykonać poprzez kryteria formatowania - według koloru wypełnienia komórki, koloru czcionki komórki, zestawu ikon formatowania warunkowego.

Kontynuujmy pracę z bazą danych DB2 „Produkcja konstrukcji metalowych według lokalizacji nr 2”, utworzoną w artykule „ ».

Rozpatrywana baza szkoleniowa składa się jedynie z 6 pól (kolumn) i 10 rekordów (wierszy). Prawdziwe bazy danych zazwyczaj zawierają kilkanaście pól i tysiące rekordów! Znalezienie niezbędnych informacji w takiej tabeli nie jest łatwe! To właśnie przez pryzmat takiego zrozumienia musimy patrzeć na nasze późniejsze działania.

Dalsza praca będzie miała następującą strukturę: w tym i kolejnych artykułach z tej serii wykorzystamy różne możliwości programu SM Przewyższać , formułuj błyskawiczne odpowiedzi na różne pytania i zadania, które pojawiają się w praktycznych działaniach kierownika zakładu produkcyjnego. Oznacza to, że rozwiążemy prawdziwe problemy produkcyjne!

Utrzymanie bazy danych - zbieranie informacji - samo w sobie nic nie daje, wręcz przeciwnie, zajmuje nam trochę czasu! Jednak wyniki analizy tych informacji, pozwalające na udzielenie dokładnych odpowiedzi na różne pytania, mogą znacznie poprawić łatwość zarządzania obiektami bazy danych w prawdziwym życiu!

Na produkcji, przy odpowiedniej konstrukcji i starannym prowadzeniu baz danych, wykorzystanie wyników analiz pozwala na podwojenie, a czasem potrojenie wolumenu produkcji przy zastosowaniu tych samych powierzchni, urządzeń i przy tym samym składzie wyposażenia techniczno-technicznego.

Najprostsze sortowanie.

Najprostsze sortowanie w Excelu odbywa się za pomocą przycisków „Sortuj rosnąco” i „Sortuj malejąco” znajdujących się na pasku narzędzi „Standard”. (Na poniższym rysunku przyciski te są otoczone czerwoną elipsą.)

Zadanie 1:

Określ: który z wytworzonych wyrobów jest najcięższy i jaka jest jego masa? Kiedy powstał ten produkt?

1. Otwórz plik w MS Excel.

2. Aktywujemy klikając na komórkę E7 z nagłówkiem kolumny „Waga 1 sztuki, t” (możesz aktywować dowolną komórkę w kolumnie, która nas interesuje).

3. Kliknij przycisk „Sortuj malejąco” na pasku narzędzi „Standardowy”.

4. Odpowiedź na zadane pytanie czytamy w samej górnej linii bazy danych (wiersz nr 8). Najcięższym produktem w bazie jest Belka 045 z zamówienia nr 2. Belka 045 produkowana była w okresie od 23 do 25 kwietnia 2014 roku (patrz wpisy w wierszach Excela nr 8-10).

5. Bazę danych można przywrócić do formularza przed sortowaniem w Excelu (jeśli zajdzie taka potrzeba) klikając przycisk „Anuluj” na tym samym pasku narzędzi „Standard”. Możesz też zastosować opcję Sortuj rosnąco do kolumny Data w bazie danych.

Sortowanie w programie Excel według wielu kolumn.

Sortowanie w ten sposób można przeprowadzić sekwencyjnie w dwóch lub trzech kolumnach.

Zadanie nr 2:

Prezentuj rekordy bazy danych pogrupowane według zamówień oraz z nazwami produktów w obrębie zamówień ułożonymi alfabetycznie.

1. Dowolną komórkę bazy danych aktywujemy klikając myszką (np. komórka C11).

2. Kliknij przycisk menu głównego „Dane” i wybierz „Sortowanie...”.

3. W wyskakującym oknie „Zakres sortowania” wybierz wartości z list rozwijanych, jak pokazano na zrzucie ekranu po lewej stronie, i kliknij „OK”.

4. Zadanie nr 2 zakończone. Rekordy są po pierwsze sortowane według numerów zamówień, a po drugie w ramach każdego zamówienia ułożone są alfabetycznie według nazw produktów.

Wyniki.

W drugim artykule z tej serii o przechowywaniu informacji i zarządzaniu danymi Przyjrzeliśmy się podstawowym możliwościom sortowania rekordów bazy danych w Excelu. Ten bardzo prosty mechanizm programu Excel jest z pewnością znany większości z Was, drodzy czytelnicy. Mam nadzieję, że nie zająłem Wam zbyt dużo czasu banalną historią o znanej usłudze. Ale... droga do kompleksu zaczyna się od prostoty!

Szczerze mówiąc, w praktyce rzadko trzeba uciekać się do sortowania. Dlaczego? Odpowiedź na to pytanie znajdzie się w kolejnych artykułach z serii, w których omówione zostaną inne, w tym bardziej zaawansowane i wydajne mechanizmy Excela służące szybkiemu i wydajnemu przetwarzaniu danych.

błagam pełen szacunku dzieło autora Subskrybuj do ogłoszeń artykułów w oknie znajdującym się na końcu każdego artykułu lub w oknie u góry strony!

Drodzy czytelnicy, napiszcie swoje recenzje i komentarze w komentarzach.

Sortowanie danych znajdujących się w obszarze wierszy i kolumn tabeli przestawnej odbywa się domyślnie w kolejności rosnącej (rys. 1a) lub przy użyciu niestandardowych list sortowania. Nie zawsze odpowiada to użytkownikowi. Np. gdy chcemy wyświetlić klientów o najwyższych dochodach na górze listy (rys. 1b). Jeśli tabela przestawna jest posortowana w kolejności rosnącej (malejącej), należy utworzyć regułę kontrolującą kolejność sortowania według pól. Co więcej, reguła ta (dla tego pola) będzie stosowana nawet po dodaniu nowych pól do tabeli przestawnej (rys. 1c).

Ryż. 1. Sortowanie według pól Klient: (a) domyślnie – od A do Z; b) według malejącego dochodu; (c) sortuj według pól Klient nie uległo zmianie podczas dodawania pola Sektor

Pobierz notatkę w formacie lub, przykłady w formacie

Sortowanie klientów w kolejności malejącej według dochodów

Aby posortować wiersze tabeli przestawnej w kolejności malejącej dochodów, zaznacz dowolną komórkę w kolumnie Kwota według pola Dochód, na przykład E4 (ale nie tytuł) i kliknij ikonę JA znajduje się na zakładce Dane(ryc. 2). Sortowanie to przypomina standardowe, ale jest to tylko powierzchowne podobieństwo. Podczas sortowania tabeli przestawnej program Excel tworzy regułę, która będzie nadal działać po wprowadzeniu dalszych zmian w tabeli przestawnej.

Na przykładzie tabeli przestawnej umieszczonej w kolumnach G:I (rys. 1c) można zobaczyć, co się stanie po dodaniu nowego zewnętrznego pola wiersza Sektor. Tabela przestawna nadal sortuje dane w kolejności malejącej według dochodów w każdym sektorze. Na przykład w branży Produkcja Na pierwszym miejscu znajduje się General Motors z przychodami na poziomie 750 163 dolarów. Tuż za nim plasuje się Ford z przychodami w wysokości 622 794 dolarów. Nawet jeśli usuniesz pole Klient z tabeli przestawnej dokonaj dodatkowych ustawień i zwróć to pole z powrotem, ale do obszaru kolumn Excel zapamięta sortowanie klientów według malejącej kolejności dochodów.

Aby w tabeli zbiorczej, znajdującej się w kolumnach G:I (rys. 1c), sektory zostały posortowane także w kolejności malejącej przychodów, można skorzystać z trzech sposobów:

• Wybierz komórkę G4, kliknij prawym przyciskiem myszy i wybierz Zwinąć wszystkiepole aby ukryć wszystkie elementy, które dotyczą klienta. Gdy na ekranie zostanie wyświetlony tylko jeden sektor, wybierz komórkę I4 i kliknij ikonę YA na zakładce Dane aby wykonać sortowanie w kolejności malejącej. Spowoduje to utworzenie reguły sortowania dla pola Sektor. Wybierz ponownie komórkę G4, kliknij prawym przyciskiem myszy i wybierz Rozwiń wszystkiepole.
• Tymczasowo usuń pole Klient z tabeli przestawnej posortuj tabelę w kolejności malejącej przychodów (sposób opisany na rys. 2), a następnie zwróć pole ponownie Klient.
• Wykorzystaj możliwości zespołu Dodatkowyopcjesortowanie(Używam dokładnie tej metody). Aby wywołać polecenie: (a) Wybierz komórkę G4, kliknij prawym przyciskiem myszy i wybierz Sortowanie → Dodatkowyopcjesortowanie(rys. 3) lub (b) kliknij ikonę trójkąta w polu Sektor, a następnie wybierz Dodatkowyopcjesortowanie(ryc. 4). W obu przypadkach otworzy się okno Sortowanie(ryc. 5). Ustaw przełącznik w pozycji malejąco i wybierz linię Kwota według pola Dochód.

Ryż. 3. Wywołaj polecenie Dodatkowyopcjesortowanie kliknij prawym przyciskiem myszy

Ryż. 4. Wywołaj polecenie Dodatkowyopcjesortowanie korzystając z menu Sortowanie i filtry pola Sektor

Ryż. 5. Ustawianie parametrów w oknie Sektor

W lewym dolnym rogu okna dialogowego Sortowanie znajduje się przycisk Zaawansowane... Po kliknięciu tego przycisku na ekranie pojawi się okno dialogowe . W tym oknie możesz: (a) zdefiniować własną listę, która będzie używana do sortowania według pierwszego klucza (szczegóły poniżej); (b) zamiast kolumny Łączna suma Wybierz inną kolumnę jako podstawową kolumnę sortującą.

Na przykład dla tabeli przestawnej pokazanej na ryc. 6, możesz ustawić sortowanie nie według całkowitego dochodu, ale według dochodu ze sprzedaży jednego rodzaju towaru, np. Urządzenia(należy pamiętać, że klienci nie są sortowani według kolumny F, ale według kolumny C).

Ryż. 6. Dodatkowe parametry pozwalają na sortowanie klientów nie według całkowitych przychodów, ale według przychodów ze sprzedaży produktów Urządzenie

Aby posortować w ten sposób:

1. Rozwiń listę klientów znajdującą się w komórce A4.
2. Wybierz opcję Więcej opcji sortowania.
3. W oknie dialogowym Sortowanie (Klient) kliknij przycisk Dodatkowo…
4. W oknie dialogowym Dodatkowe opcje sortowania (Klient) Wybierz sekcję Kolejność sortowania i ustaw przełącznik Wartości w wyróżnionej kolumnie.
5. Kliknij pole łącza, a następnie wybierz komórkę C5. Pamiętaj, że musisz kliknąć jedną z komórek wartości Urządzenie, ponieważ w nagłówku Urządzenie Nie można kliknąć komórki C4.
6. Aby zakończyć ustawianie parametrów, kliknij dwukrotnie OK.

Nie przejmuj się, opis tego algorytmu krok po kroku podano raczej w celach edukacyjnych. Począwszy od programu Excel 2013, sortowanie danych w tabeli przestawnej stało się znacznie łatwiejsze. Teraz przyciski YA i AYA znajdują się na zakładce Dane korzystaj z inteligentnych algorytmów sortowania. Próbując sortować za pomocą tych przycisków, program spróbuje przewidzieć intencje użytkownika na podstawie tego, która komórka została podświetlona przed naciśnięciem przycisku sortowania (Rysunek 7):

• A1, C1, D1, E1, F1, F2, A30, F30 – niedostępne
• A2:A29 – ułoży alfabetycznie nazwy klientów w kolumnie A
• В1, В2, С2, D2, E2 – ułoży alfabetycznie nazwy towarów w linii 2
• B30, C30, D30, E30 – uporządkuje w kolejności malejącej (rosnącej) kwotę dochodu w wierszu 30
• sprzedaż rosnąca (malejąca) B3:B29 – moduły, C3:C29 – urządzenia, D3:D29 – części, E3:E29 – leki, F3:F29 – ogółem.

Sortowanie ręczne

Zwróć uwagę na to w oknie dialogowym Sortowanie(patrz rys. 5) można ręcznie zdefiniować reguły sortowania danych. Ale ręczne sortowanie tabeli przestawnej odbywa się również w inny, dość nietypowy sposób. W raporcie w formie tabeli przestawnej na rys. 8a przedstawia kolejność kategorii produktów, posortowaną alfabetycznie: Część, moduł, przygotowanie I Urządzenie. Należy pamiętać, że wielkość sprzedaży produktów według kategorii Szczegół, nie największy. I nie warto najpierw wyświetlać tej kategorii. Umieść wskaźnik myszy w komórce E4 i wprowadź słowo Szczegół. Wystarczy nacisnąć klawisz Wchodzić Jak Excel ustali, że zdecydowałeś się przenieść kolumnę Szczegół do ostatniej kolumny tabeli. Wszystkie wartości liczbowe związane z tą kategorią produktów zostaną przesunięte z kolumny B do kolumny E. Wartości związane z innymi kategoriami produktów zostaną przesunięte w lewo. Takie zachowanie wydaje się nielogiczne i jest typowe tylko dla tabel przestawnych programu Excel. Nie można w ten sposób uporządkować zwykłego zestawu danych programu Excel. Na ryc. Rysunek 8b przedstawia tabelę przestawną po przeniesieniu nagłówka nowej kolumny do komórki E4.

Ryż. 8. Sortowanie ręczne: (a) kategorie produktów sortowane są alfabetycznie, (b) kategoria Część jest umieszczana na końcu

Entuzjaści myszy mogą po prostu przeciągnąć i upuścić żądane nagłówki kolumn (lub poszczególne wiersze). Kliknij obszar nagłówka kolumny i przytrzymaj wskaźnik myszy nad krawędzią wybranego zakresu komórek, aż zmieni się ona w czterostronną strzałkę. Rozpocznij przeciąganie komórki w wybrane miejsce; Wskaźnik pojawi się w postaci grubej linii i szeryfów. Gdy tylko zwolnisz przycisk myszy, wartości liczbowe zostaną natychmiast przeniesione do nowej kolumny. Należy pamiętać, że w przypadku sortowania ręcznego produkty dodane do źródła danych dodawane są na końcu listy. Dzieje się tak dlatego, że Excel nie wie, gdzie dokładnie dodać nowy region.

Sortowanie danych według niestandardowych list

Innym rozwiązaniem problemu dostosowywania kolejności prezentowania pól jest tworzenie niestandardowych list. Korzystając z takiej listy, utworzone w przyszłości tabele przestawne zostaną posortowane. Domyślnie Excel zawiera cztery niestandardowe listy: dni tygodnia, miesięcy oraz skrócone nazwy dni tygodnia i miesięcy. Program sortuje nazwy dni tygodnia w naturalnej kolejności, zaczynając od pon., a kończąc na niedz. (a nie alfabetycznie).

Aby utworzyć własną listę sortowania, wykonaj następujące kroki:

1. W obszarze arkusza wolnym od danych wprowadź nazwy kategorii produktów w kolejności odpowiadającej tworzonej liście niestandardowej. Wpisz jedno imię w każdej komórce i umieść je w jednej kolumnie (ryc. 9).
2. Wybierz wynikową listę nazw kategorii produktów (komórki A10:A13).
3. Wybierz kartę wstążki Plik i na dole paska nawigacyjnego wyświetlanego w oknie po lewej stronie kliknij przycisk Opcje aby otworzyć okno dialogowe Opcje Excela.
4. Wybierz kategorię Dodatkowo, przejdź do sekcji Są pospolite i kliknij przycisk Edytuj listy.
5. W oknie dialogowym Listy w polu zostanie wyświetlony adres zakresu zawierającego wybraną wcześniej listę tytułów Importowanie listy z komórek(ryc. 10). Naciśnij przycisk Import wygenerować nową listę kategorii produktów na podstawie podanych danych. Nowa lista zostanie dodana na dole obszaru Listy.
6. Kliknij OK, aby zamknąć okno dialogowe Listy. Kliknij ponownie przycisk OK, aby zamknąć okno dialogowe Opcje Excela.

Ryż. 10. Okno Listy

Nowo utworzona lista jest zapisywana w ustawieniach programu i staje się dostępna w przyszłych sesjach Excela. Teraz wszystkie utworzone w przyszłości tabele przestawne będą automatycznie sortowane według pól produktów, zgodnie z kolejnością określoną na liście. Na ryc. Rysunek 11 przedstawia nową tabelę przestawną (która powstała na podstawie po dodaniu niestandardowej listy produktów), posortowaną według utworzonej listy.

Aby posortować wcześniej utworzone tabele przestawne według nowej listy niestandardowej, wykonaj następujące kroki:

1. Rozwiń listę pól Produkt i wybierz opcję Dodatkowe opcje sortowania.
2. W oknie dialogowym Sortowanie (produkt) wybierz przycisk rosnąco (od A do Z) według pola i z listy rozwijanej wybierz Produkt.
3. Naciśnij przycisk Dodatkowo…
4. W oknie dialogowym Dodatkowe opcje sortowania (Produkt) odznacz pole wyboru Automatyczne sortowanie.
5. Rozwiń listę Sortuj według pierwszego klucza i wybierz listę zawierającą nazwy kategorii produktów (rys. 12).
6. Kliknij dwukrotnie OK.

Notatka została napisana na podstawie książki Billa Jelena, Michaela Alexandra. . Rozdział 4.

Sortowanie danych w programie Excel polega na układaniu danych tabelarycznych w żądanej kolejności, na przykład rosnąco (od najmniejszej do największej) lub malejącej (od największej do najmniejszej). Sortowane są wartości numeryczne i tekstowe, wartości daty i godziny oraz formaty. Sortowanie danych możliwe jest zarówno według kolumn, jak i wierszy. Przed sortowaniem należy wyświetlić ukryte wiersze i kolumny.

Rodzaje danych do sortowania i kolejność sortowania

Sortuj wartości liczbowe w Excelu

Sortowanie rosnące wartości liczbowych to układ wartości, w którym wartości są ułożone od najmniejszej do największej (od minimum do maksimum).

Odpowiednio sortowanie wartości liczbowych w porządku malejącym to uporządkowanie wartości od największej do najmniejszej (od maksimum do minimum).

Sortowanie wartości tekstowych w Excelu

„Sortowanie od A do Z” – sortowanie danych w kolejności rosnącej;

„Sortuj od Z do A” – sortowanie danych w kolejności malejącej.

Aby posortować wartości tekstowe alfabetycznie, wartości te są ze sobą porównywane. Jak wiadomo, podczas zapisywania tekstu komputer stosuje schemat, w którym każdy znak ma swój własny, unikalny numer, zwany kodem znaku. To właśnie te kody są porównywane w celu ustalenia, która wartość tekstowa jest większa, a która mniejsza.

Wartości tekstowe mogą zawierać znaki alfabetyczne, numeryczne i specjalne. W takim przypadku liczby można zapisać zarówno w formacie numerycznym, jak i tekstowym. Liczby przechowywane w formacie liczbowym są mniejsze niż liczby przechowywane w formacie tekstowym. Aby poprawnie posortować wartości tekstowe, wszystkie dane muszą być zapisane w formacie tekstowym. Ponadto, gdy do komórek wstawiane są dane tekstowe z innych aplikacji, mogą one zawierać spacje na początku. Przed rozpoczęciem sortowania należy usunąć spacje początkowe (lub inne znaki niedrukowalne) z sortowanych danych, w przeciwnym razie sortowanie nie zostanie wykonane poprawnie.

Dane tekstowe można sortować z uwzględnieniem wielkości liter. W tym celu w opcjach sortowania należy zaznaczyć pole „Rozważ przypadek”.

Zazwyczaj wielkie litery mają mniejsze cyfry niż małe litery.

Sortuj wartości daty i godziny

„Sortuj najstarsze do najnowszego” polega na sortowaniu wartości daty i godziny od najwcześniejszej do najnowszej.

„Sortuj nowe do starych” polega na sortowaniu wartości daty i godziny od najnowszej do najwcześniejszej.

Sortowanie formatów

Microsoft Excel 2007 i nowsze wersje umożliwiają sortowanie według formatowania. Ta metoda sortowania jest używana, gdy zakres komórek jest formatowany przy użyciu koloru wypełnienia komórki, koloru czcionki lub zestawu ikon. Kolory wypełnień i czcionek w Excelu mają swoje własne kody i to właśnie te kody są używane podczas sortowania formatów.

Sortuj według listy niestandardowej

Dane tabelaryczne można sortować według niestandardowych list, takich jak lista miesięcy, lista dni tygodnia, lista działów strukturalnych przedsiębiorstwa, lista numerów personelu pracowników i tak dalej. Excel ma możliwość tworzenia własnych list sortowania danych. Przy tej kolejności sortowania dane do sortowania i wartości listy muszą się zgadzać.

Opcje sortowania

Sortuj według kolumny

W dowolnej wersji programu Excel domyślnie ustawione jest sortowanie według kolumn, to znaczy wartości komórek wybranej kolumny są ułożone w żądanej kolejności, a wiersze zakresu są zamieniane w zależności od położenia komórki w posortowanej kolumnie.Aby posortować tabelę po kolumnach, wystarczy ustawić znacznik wyboru w dowolnej komórce tabeli i kliknąć jedną z ikon symbolizujących sortowanie od minimum do maksimum lub od maksimum do minimum. Tabela zostanie posortowana według kolumny zawierającej znacznik wyboru.

Sortuj według ciągu

W ustawieniach programu można zmienić sortowanie po kolumnach na sortowanie po wierszach. Jeśli ustawione jest sortowanie według wierszy, wartości komórek wybranego wiersza zostaną ułożone w określonej kolejności, a kolumny zostaną zamienione w zależności od pozycji komórek w posortowanym wierszu.

Sortowanie wielopoziomowe

Tak więc, jeśli sortujesz według kolumny, wówczas wiersze zostaną zamienione, jeśli dane zostaną posortowane według wiersza, wówczas kolumny zostaną zamienione.

Excel umożliwia sortowanie danych nie tylko według jednej kolumny czy jednego wiersza, ale poprzez utworzenie innej liczby poziomów sortowania. Na przykład w programie Excel 2007 jest ich 64. Poziomy sortowania można dodawać, usuwać, kopiować i zamieniać.

Między innymi możesz uwzględnić wielkość liter lub zignorować ją podczas sortowania.

Dodatek do sortowania danych w programie Excel

Microsoft Excel udostępnia użytkownikom dość duży zestaw standardowych narzędzi do sortowania wartości różnego typu, jednak istnieją zadania, których rozwiązanie przy użyciu standardowych narzędzi jest albo niewygodne, albo czasochłonne, np. sortowanie każdego wiersza/kolumny w taki sposób sposób, w jaki sortowanie odbywa się tylko w obrębie wiersza/kolumny i nie wpływa na sąsiednie komórki.