Sortowanie danych w Excelu. Jak sortować dane w Excelu. Niestandardowa lista lub gotowe opcje sortowania
Sortowanie w programie Excel to wbudowana funkcja analizy danych. Za jego pomocą możesz ułożyć nazwiska w kolejności alfabetycznej, posortować rosnącą lub malejącą średnią ocen kandydatów, ustawić kolejność wierszy w zależności od koloru lub ikony itp. Ponadto za pomocą tej funkcji można szybko nadać tabeli wygodny wygląd, który pozwoli użytkownikowi szybko znaleźć potrzebne informacje, przeanalizować je i podjąć decyzje.
Sortowanie wierszy w Excelu
Kliknij, aby dodać dane, sformatować wykresy i poruszać się po dużych arkuszach kalkulacyjnych. Nie martw się o oszczędzanie. Informacje prowadzą do funkcji, której potrzebujesz. Na dużych tabletach, laptopach i komputerach stacjonarnych możesz bezpłatnie przeglądać arkusze kalkulacyjne. Funkcje są podzielone na następujące kategorie. Funkcje dodatkowe i automatyzujące.
Umożliwiają przetwarzanie danych z tabel przestawnych lub łączy dynamicznych. Służy do zaawansowanej analizy w kostkach informacyjnych. Stosuje się je, gdy zachodzi potrzeba umieszczenia bieżącej daty i godziny w arkuszu kalkulacyjnym. Możesz uzyskać dzień tygodnia, rok, numer tygodnia i wiele więcej.
Film o korzystaniu z sortowania w Excelu
Co możesz sortować?
Excel może sortować dane według tekstu (alfabetycznie lub odwrotnie), liczb (rosnąco lub malejąco), daty i godziny (od nowych do starych i odwrotnie). Możesz sortować według jednej kolumny lub kilku jednocześnie. Na przykład możesz najpierw posortować wszystkich klientów alfabetycznie, a następnie posortować ich według całkowitej kwoty ich zakupów. Ponadto Excel może sortować według niestandardowych list lub formatu (kolor komórki, kolor tekstu itp.). Zwykle sortowanie odbywa się tylko według kolumn, ale możliwe jest zastosowanie tej funkcji także do wierszy.
Umożliwiają wykonywanie obliczeń związanych z dziedziną techniczną, a także modeli w oparciu o wzory stosowane w inżynierii. Służą do obliczeń i sprawozdawczości finansowej. Wskazują charakterystykę stanu różnych części tabeli, pozwala to sprawdzić, czy komórka jest pusta, czy nie, i inne funkcje.
Służą do porównywania wartości i na podstawie porównania dają prawdziwy lub fałszywy wynik. Funkcje wyszukiwania i linki. Pozwalają znaleźć dane według kryteriów zadanych w wyszukiwaniu. Funkcje matematyczne i trygonometryczne.
Wszystkie określone ustawienia tej opcji są zapisywane w skoroszycie programu Excel, co pozwala na skorzystanie z informacji po otwarciu skoroszytu (jeśli to konieczne).
Sortowanie danych w Excelu
Sortowanie w Excelu można podzielić na proste i złożone. Sortowanie proste jest rosnące lub malejące.
Umożliwiają obliczenia statystyczne, takie jak mediana, mediana wariancji, odchylenie standardowe i inne powiązane funkcje. Funkcje tekstowe umożliwiają pracę z danymi alfanumerycznymi, porównywanie tekstu, zmianę formatowania, zmianę wielkich i małych liter, łączenie tekstu i wiele więcej.
Funkcje niestandardowe. Sortując informacje w arkuszu kalkulacyjnym, możesz przeglądać dane w dowolny sposób i szybko znajdować wartości. Możesz sortować zakres lub tabelę danych według jednej lub większej liczby kolumn danych; Na przykład możesz najpierw posortować pracowników według działu, a następnie według nazwiska.
Istnieją 2 główne typy sortowania – rosnące i malejące
Zanim zaczniesz, musisz otworzyć Excel i wypełnić pewne informacje. Na przykład możesz wypełnić 10 komórek liczbami od 1 do 10. Teraz musisz zaznaczyć całą kolumnę (w tym przypadku wszystkie 10 komórek) i wybrać „Dane - Sortowanie” na pasku menu. Otworzy się nowe okno, w którym należy określić sposób sortowania informacji rosnąco lub malejąco. Możesz na przykład wybrać element „malejąco” i kliknąć przycisk „OK”. Teraz liczby przejdą od 10 do 1. Możesz ponownie otworzyć okno sortowania i wybrać „rosnąco” - liczby przejdą od 1 do 10. Tę procedurę można również wykonać jednocześnie w 3 kolumnach. Chociaż lepiej jest przeprowadzić takie sortowanie.
Ograniczenie obowiązków związanych z tłumaczeniem automatycznym: Ten artykuł został przetłumaczony przy użyciu systemu komputerowego bez interwencji człowieka. Ponieważ ten artykuł został przetłumaczony maszynowo, może zawierać błędy słownictwa, składni lub gramatyki. Dla porównania, możesz znaleźć angielską wersję tego artykułu. . Czy mógłbyś nam pokazać jak? Dziękuję bardzo za pomoc. Pozdrawiam, Maria. Wiesz, że filtrowanie polega na ukryciu części interesujących Cię informacji i ukryciu pozostałych danych.
Można na przykład utworzyć tabelę, w której będą przechowywane informacje o produkcie znajdującym się w magazynie. Tabela będzie składać się z 3 kolumn: nazwa, kolor, ilość. Produkty muszą być tak napisane, aby było kilka z tej samej kategorii. Na przykład buty męskie są czarne (3 modele), buty męskie są czerwone (2 modele), buty damskie są białe (4 modele) itp. Ilość może być dowolna.
Ta tabela będzie niczym innym jak nazwami pól w formie nagłówka i kilkoma wpisami poniżej. Dostępne są automatyczne filtry umożliwiające szybkie i łatwe filtrowanie. Są to strzałki, które pojawiają się w każdej nazwie pola, w nagłówku tabeli i których użycie nie wymaga wiele wyjaśnień.
Od teraz możesz korzystać z filtrów w dowolnym czasie. Na tej samej strzałce znajduje się opcja Wszystko, aby usunąć filtr aplikacji. Możesz także przejść do menu Dane, Filtr, Pokaż wszystko, aby usunąć wszystkie posiadane filtry i upewnić się, że poniższa tabela jest kompletna. Jeśli rozumiesz strzałki filtra, możesz także sortować rosnąco lub malejąco według tego pola.
Aby więc włączyć autofiltr, musisz zaznaczyć cały arkusz i wybrać „Dane - Filtr - Autofiltr” na pasku menu. W komórkach z nazwami kolumn (nazwa, ilość itp.) powinna pojawić się mała ikona, a po kliknięciu otworzy się rozwijana lista. Obecność takiej strzałki oznacza, że autofiltr jest prawidłowo włączony. Na tej liście możesz posortować dane w kolejności malejącej lub rosnącej, określić, że w tabeli będzie wyświetlanych tylko pierwszych 10 pozycji (w tym przykładzie ta opcja nie będzie działać) lub wyświetlić konkretny produkt (na przykład botki męskie). Można także wybrać pozycję „Stan” i określić np., że program ma wyświetlać wszystkie produkty, których ilość jest mniejsza lub równa 10.
Zaawansowane filtry: trzy ważne korzyści
Warto także ustawić się w komórce tabeli, przejść do zakładki Dane, Sortować i filtrować i kliknąć przycisk Filtruj. W ten sposób będziesz miał słynne strzałki. Jednak zaawansowane filtry nie są tak proste i łatwe w użyciu. Jakie są zatem powody ich stosowania? Filtry zaawansowane mają zasadniczo trzy zalety w porównaniu z filtrami automatycznymi.
Dodatek do sortowania danych w programie Excel
Na przykład potrzebuję listy uczniów, którzy uzyskali średnio powyżej 5 punktów z matematyki i powyżej 7, ale tylko w trzecim trymestrze. Drugą zaletą zaawansowanych filtrów jest możliwość odzyskania wyniku filtra w innym miejscu, czyli bez „zniszczenia” oryginalnej tabeli. Zatem stół pozostaje nienaruszony, a wynik znajduje odzwierciedlenie w innym obszarze, nawet na innym arkuszu, chociaż a priori wydaje się, że nie może. Gdy uzyskasz wynik w innym obszarze, możesz także wybrać pola, które chcesz wyświetlić w filtrowanej tabeli, jest to według mnie trzecia zaleta.
Jeśli strzałka autofiltru jest niebieska, oznacza to, że ta kolumna została już posortowana.
Sztuczki sortowania
Załóżmy, że użytkownik ma tabelę zawierającą kolumnę z nazwami miesięcy w roku. A kiedy trzeba to posortować, na przykład rosnąco, okazuje się, że jest mniej więcej tak: sierpień, kwiecień, grudzień itd. Chciałbym jednak, aby sortowanie odbywało się w zwykłej kolejności, tj. Styczeń, luty, marzec itd. Można to zrobić za pomocą specjalnego ustawienia dla arkusza niestandardowego.
Filtrowanie danych w Excelu
Jaki jest zakres kryteriów? Jest to zbiór komórek, w którym zostaną ustawione warunki filtrowania tabeli. Zawiera nazwy pól, które chcesz ustawić oraz warunki. Na przykład, jeśli muszę gościć studentów z Madrytu przez 30 lat, gdzieś na arkuszu powinno znajdować się coś takiego jak poniżej.
Sortowanie według kolumn
Słowo „Miasto” i „Wiek” należy zapisać w taki sam sposób, jak w tytule oryginalnej tabeli, dlatego najlepiej zastosować metodę kopiuj-wklej. Na powyższym obrazku powiesz, że chcesz studentów, których miastem jest „Madryt” i „mają ponad 30 lat, ale pytanie o Madryt to zupełnie inna sprawa” lub którzy mają ponad 30 lat, w tym przypadku zakres kryteria. W tej grze musisz uważać na nasz sposób mówienia, który czasami wydaje się być sprzeczny z tymi zasadami. W jaki sposób przez ponad 30 lat wybierałeś studentów z Madrytu i Sewilli?
W tym celu należy zaznaczyć całą tabelę, otworzyć okno sortowania i w polu „Zamówienie” wskazać pozycję „Lista niestandardowa”. Otworzy się nowe okno, w którym możesz wybrać żądaną sekwencję miesięcy w roku. Jeśli takiej listy nie ma (np. nazwy miesięcy są w języku angielskim), możesz ją utworzyć samodzielnie, wybierając opcję „Nowa lista”.
Sortuj według wielu kolumn
W następujący sposób. Wiesz, że gwiazdka jest zwykle symbolem wieloznacznym i można jej użyć do zastąpienia dowolnego ciągu znaków o nieokreślonej długości. No cóż, to wciąż teoria, ale jak przebiega ten proces? Załóżmy, że masz stolik w ćwiczeniu 11 z przodu i pytasz o wszystkie produkty z kategorii Napoje powyżej 15 euro. Oczywiste jest, że musimy stworzyć szereg kryteriów obejmujących jedno pole zawierające cenę, a drugie zawierające kategorię. W takim przypadku należy przejść do nagłówka tabeli, skopiować pola „Kategoria” i „Cena jednostkowa” i wkleić je np. na końcu tabeli, w komórce A poniżej, wpisać kryteria tak, aby były taki sam jak na obrazku.
Sortowanie danych wcale nie jest trudne. W rezultacie możesz uzyskać wygodną tabelę lub raport, aby szybko przeglądać niezbędne informacje i podejmować decyzję.
Sortowanie danych w programie Excel to narzędzie umożliwiające prezentację informacji w przyjaznej dla użytkownika formie.
Pytanie jednak brzmi, czy chcesz, aby wynik zawierał wszystkie pola oryginalnej tabeli, czy kilka wybranych kolumn? Masz już wszystkie niezbędne komponenty do wykonania filtra zaawansowanego: oryginalną tabelę, zakres kryteriów oraz strefę wyników z wybranym przez Ciebie nagłówkiem.
Według mnie są szczegóły, które choć nie są niezbędne, pomogą przyspieszyć proces, szczególnie jeśli chcesz wykonać wiele filtrów. Najlepiej jest określić nazwę zakresu tabeli, aby można było jej używać za każdym razem, gdy trzeba filtrować i uzyskać dostęp do tabeli. W ten sposób po prostu nazwiesz zakres. Świetnie, po tym wszystkim zobaczymy proces stosowania filtra zaawansowanego.
Wartości liczbowe można sortować w kolejności rosnącej i malejącej, wartości tekstowe można sortować alfabetycznie i w odwrotnej kolejności. Dostępne są opcje – według koloru i czcionki, w dowolnej kolejności, według kilku warunków. Kolumny i wiersze są sortowane.
Kolejność sortowania w Excelu
Menu sortowania można otworzyć na dwa sposoby:
Często używane metody sortowania są prezentowane za pomocą jednego przycisku na pasku zadań:
Jeśli jesteś gdziekolwiek w arkuszu, powinieneś przejść do menu „Dane”, „Filtr”, „Filtr zaawansowany”, pojawi się poniższe okno. Można to zrobić, umieszczając każde pole w oknie dialogowym i wybierając zakres za pomocą myszy, bez pisania. Fakt, że linki pojawiają się z dolarami lub bez, nie ma znaczenia, ale zauważ, że aby określić zakres listy, nie musiałem niczego wybierać, wystarczyło wpisać nadaną przez nas nazwę rangi: „produkty”.
A jeśli chcesz, to czy jest to inny arkusz niż oryginalny stół? To znaczy, co się stanie, jeśli masz tabelę na jednym arkuszu, ale skopiujesz nagłówek wybranych pól na inny arkusz? Wielu powie mi, że nie możesz, ponieważ próbowałeś i pojawia się następujące ostrzeżenie.
Sortowanie tabeli według pojedynczej kolumny:
Jeśli wybierzesz całą tabelę i posortujesz, pierwsza kolumna zostanie posortowana. Dane w wierszach staną się zgodne z pozycją wartości w pierwszej kolumnie.
Aktywujmy więc arkusz docelowy. Oznacza to, że proces rozpoczyna się od arkusza odbiorcy. Masz oryginalną tabelę lub listę w jednym arkuszu i chcesz, aby wynik trafił do innego arkusza, cóż, umieść się w dowolnej komórce w tym innym arkuszu, zanim przejdziesz do menu Dane, filtru, filtru zaawansowanego. Cały proces wykonujesz więc z arkusza docelowego.
A tak przy okazji, co się stanie, jeśli nie umieścisz niczego w sekcji zakresu kryteriów? Dobrą rzeczą jest to, że wykonasz zaawansowany filtr bez kryteriów, co oznacza, że otrzymasz wynik dla wszystkich rekordów znajdujących się w tabeli źródłowej i pól wybranych w „Kopiuj do”.
Sortuj według koloru komórki i czcionki
Excel zapewnia użytkownikowi bogate możliwości formatowania. Dlatego możesz pracować z różnymi formatami.
Stwórzmy w tabeli szkoleniowej kolumnę „Total” i wypełnijmy komórki wartościami o różnych odcieniach. Sortujmy według koloru:
Program posortował komórki według akcentu. Użytkownik może samodzielnie wybrać kolejność sortowania kolorów. Aby to zrobić, wybierz z listy opcji narzędzia „Sortowanie niestandardowe”.
Co się stanie, jeśli nie utworzysz nagłówka z polami, które chcesz umieścić w wyniku? Otrzymasz filtr z rekordami pasującymi do zakresu kryteriów, ale ze wszystkimi polami z oryginalnej tabeli w wyniku określonej komórki. W obu przypadkach znajdziesz zakładkę Dane, Sortowanie i Filtrowanie. Tam zobaczysz przycisk „Zaawansowane”.
Jeżeli trafiłeś tutaj nie wtykając głowy w komputer, to nauczyłeś się czegoś nowego. Aby się tego dowiedzieć, postępuj zgodnie z poniższymi informacjami. Jako praktyczny przykład lepszej kompresji, na poniższym obrazku mamy tabelę zawierającą listę nazwisk biegaczy biorących udział w zawodach lekkoatletycznych oraz klasyfikację biegaczy w tym przypadku.
W oknie, które zostanie otwarte, wprowadź niezbędne parametry:
Tutaj możesz wybrać kolejność prezentowania komórek o różnych kolorach.
Dane sortowane są według czcionki, na tej samej zasadzie.
Sortowanie w programie Excel według wielu kolumn
Jak ustawić wtórny porządek sortowania w programie Excel? Aby rozwiązać ten problem, musisz ustawić kilka warunków sortowania.
Należy pamiętać, że klasyfikacja nie jest jeszcze pokazana, w takim przypadku musimy znać kolejność przybycia każdego zawodnika bez obliczania poszczególnych wyścigów. Kolejność: W tym przypadku jeśli wstawimy wartość 0, zwrócona zostanie wartość pozycji numeru na liście. Jeżeli wprowadzona wartość wynosi jeden, wartość pozycji numeru będzie zwracana etapami.
W poniższym przykładzie stosujemy formułę tak, aby wartość była wyświetlana stopniowo. Zauważ na powyższym obrazku, że funkcja zwróciła położenie końcowe znacznika biegacza. Oznacza to, że na podstawie czasów przybycia wszystkich korytarzy zawartych w macierzy czasu funkcja stopniowo przynosiła uporządkowaną wartość.
Program umożliwia dodanie kilku kryteriów jednocześnie w celu przeprowadzenia sortowania w specjalnej kolejności.
Sortowanie wierszy w Excelu
Domyślnie dane są sortowane według kolumn. Jak sortować według wierszy w programie Excel:
W ten sposób sortujesz tabelę w Excelu według kilku parametrów.
Aby odtworzyć tę funkcję dla innych sportowców, należy zablokować komórki związane z przedziałem czasowym. W tym przypadku użyjemy znaku dolara jak pokazano na obrazku poniżej, aby nie było błędów podczas kopiowania formuły. Aby odtworzyć formułę dla innych sportowców, po prostu przeciągnij pierwszą komórkę na pozostałe za pomocą uchwytu wypełniania.
Dla lepszej wizualizacji i zrozumienia możemy także skorzystać z narzędzia Sortuj i Filtruj znajdującego się na zakładce Strona główna w grupie narzędzi Edycja. Zatem po prostu wybierz dowolną komórkę z wartościami w tabeli i wybierz porządek sortowania, aby utworzyć inkrementację lub dekrementację, jak pokazano poniżej.
Sortowanie losowe w Excelu
Wbudowane opcje sortowania nie pozwalają na losowe układanie danych w kolumnie. Funkcja LOS poradzi sobie z tym zadaniem.
Na przykład musisz ułożyć zestaw określonych liczb w losowej kolejności.
Umieść kursor w sąsiedniej komórce (w lewo lub w prawo, nie ma to znaczenia). Wpisz LOS() w pasku formuły. Naciśnij enter. Kopiujemy formułę na całą kolumnę - otrzymujemy zestaw liczb losowych.
Posortujmy teraz wynikową kolumnę w kolejności rosnącej/malejącej – wartości z pierwotnego zakresu zostaną automatycznie ułożone w kolejności losowej.
Dynamiczne sortowanie tabel w MS Excel
Jeśli zastosujesz standardowe sortowanie do tabeli, nie będzie ono miało znaczenia w przypadku zmiany danych. Musimy zadbać o to, aby wartości były sortowane automatycznie. Używamy formuł.
Jeśli chcesz wykonać sortowanie dynamiczne w kolejności malejącej, użyj funkcji DUŻY.
Aby dynamicznie sortować wartości tekstowe, potrzebne będą formuły tablicowe.
Następnie, gdy dodasz dane do tabeli, proces sortowania zostanie przeprowadzony automatycznie.
Listę możesz sortować alfabetycznie, numerycznie lub chronologicznie według maksymalnie trzech pól. Dla każdej kolumny można określić rosnący lub malejący porządek sortowania.
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 rodzaje 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 filtrowaniu, uruchom polecenie Dane – Filtr – Wyświetl wszystko.
Sortowanie danych w programie Excel jest bardzo przydatną funkcją, należy jednak zachować ostrożność. Jeśli duża tabela zawiera złożone formuły i funkcje, lepiej jest wykonać operację sortowania na kopii tej tabeli.
Po pierwsze, we wzorach i funkcjach może zostać naruszone targetowanie w linkach i wówczas wyniki ich obliczeń będą błędne. Po drugie, po wielokrotnym sortowaniu można przetasować dane w tabeli tak, że trudno będzie wrócić do pierwotnej postaci. Po trzecie, jeśli tabela zawiera scalone komórki, należy je ostrożnie rozdzielić, ponieważ ten format nie jest akceptowalny przy sortowaniu.
Sortowanie danych w Excelu
Jakie narzędzia ma Excel do sortowania danych? Aby dać pełną odpowiedź na to pytanie, rozważmy je na konkretnych przykładach.
Przygotowanie tabeli do prawidłowego i bezpiecznego sortowania danych:
Teraz nasza tabela nie zawiera formuł, a jedynie wyniki ich obliczeń. Połączone komórki są również rozłączane. Pozostaje tylko usunąć dodatkowy tekst z nagłówków i tabela jest gotowa do bezpiecznego sortowania.
Aby posortować całą tabelę na podstawie jednej kolumny, wykonaj następujące czynności:
Dane posortowane w całej tabeli względem kolumny „Zysk netto”.
Jak sortować kolumnę w Excelu
Posortujmy teraz tylko jedną kolumnę bez odniesienia do innych kolumn i całej tabeli:
Kolumna jest sortowana niezależnie od pozostałych kolumn w tabeli.
Sortuj według koloru komórki w Excelu
Kopiując tabelę na osobny arkusz, przenosimy tylko jej wartości za pomocą specjalnego wklejania. Ale możliwości sortowania pozwalają nam sortować nie tylko według wartości, ale nawet według kolorów czcionek lub kolorów komórek. Dlatego musimy również przesyłać formaty danych. Dla tego:
Kopia tabeli zawiera teraz wartości i formaty. Sortujmy według koloru:
- Wybierz tabelę i wybierz narzędzie „Dane” - „Sortuj”.
- W opcjach sortowania ponownie zaznacz pole „Moje dane zawierają nagłówki kolumn” i wskaż: „Kolumna” – Zysk netto; „Sortowanie” – kolor komórki; „Zamów” – czerwony, na górze. I kliknij OK.
Na górze mamy obecnie najgorsze dane dotyczące zysku netto.
Notatka. Następnie możesz wybrać zakres A4:F12 w tej tabeli i powtórzyć drugi krok tej sekcji, określając tylko kolor różowy na górze. Zatem najpierw pójdą komórki z kolorem, a potem normalne.
Sortowanie danych jest integralną częścią analizy danych. Może zaistnieć potrzeba ułożenia nazw na liście alfabetycznie, wyświetlenia spisu zasobów i posortowania ich w kolejności malejącej lub uporządkowania wierszy według koloru lub ikony. Sortowanie danych pomaga szybko wizualizować dane i lepiej je zrozumieć, organizować i znajdować potrzebne informacje, a ostatecznie podejmować lepsze decyzje.
Dane można sortować według tekstu (od A do Z lub od Z do A), liczb (od najmniejszego do największego lub od największego do najmniejszego) oraz dat i godzin (od najstarszego do najnowszego lub od najnowszego do najstarszego) w jednej lub kilku kolumnach. Możesz także sortować według niestandardowych list, które tworzysz (na przykład lista składająca się z dużych, średnich i małych elementów) lub według formatu, w tym koloru komórki i koloru czcionki, a także według ikon.
Uwagi:
Możesz użyć Autofiltru lub formatowania warunkowego, aby znaleźć najwyższe i najniższe wartości w zakresie komórek lub tabeli, na przykład 10 najwyższych ocen lub 5 najniższych kwot sprzedaży.
Aby uzyskać więcej informacji, zobacz Filtrowanie danych w tabeli lub zakresie programu Excel i Stosowanie formatowania warunkowego w programie Excel.
Sortowanie wartości tekstowych
Uwagi: Możliwe problemy
Upewnij się, że wszystkie dane są zapisane w formie tekstowej. Jeśli kolumna, którą chcesz posortować, zawiera liczby zapisane w formacie liczbowym i liczby zapisane w formacie tekstowym, musisz sformatować wszystkie komórki jako liczby lub tekst. W przeciwnym razie liczby zapisane w formacie liczbowym będą po sortowaniu wyższe niż liczby zapisane w formacie tekstowym. Aby przekonwertować wszystkie wybrane dane do formatu tekstowego, kliknij CTRL+1 aby otworzyć okno dialogowe Format komórki, otwórz kartę Numer i w dziale Kategoria Wybierz opcję Ogólny, Numer Lub Tekst.
Usuń wszystkie wiodące spacje. Czasami dane importowane z innych aplikacji mogą być uzupełnione spacjami wiodącymi. Usuń je ze swoich danych przed sortowaniem. Można to zrobić ręcznie lub za pomocą funkcji SPACJA.
Sortowanie liczb
Uwagi:
Możliwe problemy
Upewnij się, że wszystkie liczby są zapisane w formacie liczbowym. Jeżeli wyniki różnią się od oczekiwanych, kolumna może zawierać liczby zapisane w formacie tekstowym. Na przykład liczby ujemne importowane z niektórych systemów księgowych lub liczby zawierające wiodący znak apostrofu ( " ), zapisywane są w formie tekstowej. Aby uzyskać więcej informacji, zobacz Konwertowanie liczb z formatu tekstowego na format liczbowy.
Sortuj wartości daty i godziny
Uwagi: Możliwe problemy
Upewnij się, że wszystkie wartości daty i godziny są zapisane w formacie daty i godziny. Jeśli wyniki nie będą zgodne z oczekiwaniami, kolumna może zawierać wartości daty i godziny zapisane w formacie tekstowym. Aby wartości daty i godziny mogły być poprawnie posortowane w Excelu, muszą być zapisane jako liczby porządkowe z informacją o dacie lub godzinie. Jeśli program Excel nie może rozpoznać wartości jako daty lub godziny, zostanie ona zapisana jako tekst. Aby uzyskać więcej informacji, zobacz Konwertowanie dat z formatu tekstowego na format daty.
Aby sortować według dnia tygodnia, zmień format komórek tak, aby wyświetlały dni tygodnia. Aby sortować według dni tygodnia, niezależnie od daty, przekonwertuj je na tekst za pomocą funkcji TEKST. Jednakże funkcja TEKST zwraca wartość tekstową, więc sortowanie zostanie wykonane według danych alfanumerycznych. Aby uzyskać więcej informacji, zobacz Wyświetlanie dat jako dni tygodnia.
Sortuj według wielu kolumn lub wierszy
Możesz chcieć posortować według dwóch lub więcej kolumn lub wierszy, aby pogrupować dane, które mają te same wartości w jednej kolumnie lub wierszu, a następnie posortować te grupy, które mają te same wartości w innej kolumnie lub wierszu. Na przykład, jeśli masz kolumny Dział i Pracownik, możesz najpierw posortować według Działu (aby pogrupować wszystkich pracowników według działów), a następnie według Nazwy (aby uporządkować alfabetycznie nazwiska pracowników w każdym dziale). Możesz sortować według 64 kolumn jednocześnie.
Notatka: Aby uzyskać najlepsze wyniki, należy uwzględnić nagłówki kolumn w sortowanym zakresie.
Sortuj według koloru komórki, koloru czcionki lub ikony
Jeśli zakres komórek lub kolumnę tabeli sformatowałeś ręcznie lub przy użyciu formatowania warunkowego przy użyciu koloru komórki lub koloru czcionki, możesz także sortować według koloru. Można także sortować według zestawu ikon utworzonych przy użyciu formatowania warunkowego.
Sortowanie według list niestandardowych
List niestandardowych można używać do sortowania w kolejności określonej przez użytkownika. Na przykład kolumna może zawierać wartości, według których chcesz sortować, takie jak „Wysoki”, „Średni” i „Niski”. Jak ustawić sortowanie tak, aby najpierw wyświetlało „Wysoki”, potem „Średni”, a na koniec „Niski”? Jeśli posortujesz je alfabetycznie (od A do Z), na górze pojawią się wartości „Wysokie”, ale za nimi pojawią się wartości „Niskie”, a nie „Średnie”. A podczas sortowania od Z do A wartości „Średnie” będą na samej górze. W rzeczywistości wartości „Średnie” powinny zawsze pojawiać się pośrodku, niezależnie od kolejności sortowania. Możesz rozwiązać ten problem, tworząc niestandardową listę.
Sortowanie uwzględniające wielkość liter
Sortowanie od lewej do prawej
Zazwyczaj sortowanie odbywa się od góry do dołu, ale wartości można sortować od lewej do prawej.
Notatka: Tabele nie obsługują sortowania od lewej do prawej. Najpierw przekształć tabelę w zakres: wybierz w niej dowolną komórkę i zaznacz elementy Praca z tabelami > Zamień na zasięg.
Notatka: Podczas sortowania wierszy będących częścią struktury arkusza program Excel sortuje grupy najwyższego poziomu (poziom 1), tak aby kolejność wierszy lub kolumn drążenia nie uległa zmianie, nawet jeśli są one ukryte.
Sortuj według części wartości w kolumnie
Aby posortować według części wartości w kolumnie, np. części kodu (789- W.D.G.-34), nazwisko (Regina Pokrovskaya) lub imię (Pokrovskaya Regina), należy najpierw podzielić kolumnę na dwie lub więcej części, tak aby wartość, według której chcesz sortować, znajdowała się w osobnej kolumnie. Aby rozbić wartości w komórce, możesz użyć funkcji tekstowych lub Kreatora tekstu. Aby uzyskać więcej informacji i przykładów, zobacz Dzielenie tekstu na różne komórki i Dzielenie tekstu na różne kolumny za pomocą funkcji.
Sortuj mniejszy zakres w większym
Ostrzeżenie: Można sortować wartości z zakresu będącego częścią innego zakresu, ale nie jest to zalecane, ponieważ zerwie to połączenie pomiędzy posortowanym zakresem a danymi oryginalnymi. Jeśli posortujesz dane w sposób pokazany poniżej, wybrani pracownicy zostaną powiązani z innymi działami.
Na szczęście Excel generuje ostrzeżenie, jeśli wykryje taką próbę:
Jeśli nie miałeś zamiaru sortować danych w ten sposób, wybierz opcję automatycznie rozszerzyć przydzielony zakres, W przeciwnym razie - sortuj w obrębie określonego zaznaczenia.
Jeśli wynik nie jest taki, jaki chcesz, kliknij przycisk Anulować .
Notatka: Nie można w ten sposób sortować wartości w tabeli.
Dowiedz się więcej o typowych problemach z sortowaniem
Jeśli wyniki sortowania danych nie są zgodne z oczekiwaniami, wykonaj następujące czynności:
Sprawdź, czy wartości zwracane przez formuły uległy zmianie Jeśli sortowane dane zawierają jedną lub więcej formuł, zwracane przez nie wartości mogą ulec zmianie po ponownym obliczeniu arkusza. W takim przypadku należy ponownie zastosować sortowanie, aby uzyskać najnowsze wyniki.
Pokaż ukryte wiersze i kolumny przed sortowaniem Sortowanie według kolumny nie przesuwa ukrytych wierszy, a sortowanie według wierszy nie powoduje przesuwania ukrytych kolumn. Przed posortowaniem danych warto wyświetlić ukryte wiersze i kolumny.
Sprawdź bieżące ustawienia regionalne Kolejność sortowania zależy od wybranego języka. Upewnij się, że panel kontrolny W rozdziale Ustawienia regionalne Lub standardy językowe i regionalne Ustawiono prawidłowe ustawienia regionalne. Informacje na temat zmiany ustawień regionalnych można znaleźć w Pomocy systemu Microsoft Windows.
Wprowadź nagłówki kolumn tylko w jednym wierszu Jeśli chcesz użyć wielu nagłówków linii, ustaw zawijanie słów w komórce.
Włącz lub wyłącz pasek tytułu Ogólnie zaleca się wyświetlanie wiersza nagłówka podczas sortowania według kolumn, ponieważ ułatwia to zrozumienie danych. Domyślnie wartość w nagłówku nie jest uwzględniana podczas sortowania. Jednak w niektórych przypadkach możesz chcieć włączyć lub wyłączyć nagłówek, aby wartość w nagłówku była uwzględniana lub nie podczas sortowania. Wykonaj jedną z następujących czynności.
Aby wykluczyć z sortowania pierwszy wiersz danych (nagłówek kolumny), kliknij zakładkę dom w grupie Redagowanie naciśnij przycisk Sortowanie i filtrowanie wybierz polecenie Sortowanie niestandardowe i zaznacz pole.
Aby uwzględnić w sortowaniu pierwszy wiersz danych (ponieważ nie jest to nagłówek kolumny), na karcie dom w grupie Redagowanie naciśnij przycisk Sortowanie i filtrowanie wybierz polecenie Sortowanie niestandardowe i odznacz Moje dane zawierają nagłówki.
Załóżmy, że przygotowaliśmy raport wyników sprzedażowych i finansowych według kanałów sprzedaży i menedżerów typu:
To otwiera oczy, prawda? Aby ułatwić lekturę tej tabeli, zdecydujmy, które informacje są dla nas ważne, a które nie.
Zwróćmy od razu uwagę na 2 ważne punkty.
1. Z reguły tabele tego rodzaju tworzone są za pomocą formuł. Przed sortowaniem potrzebujesz pamiętaj o usunięciu wszystkich formuł ze wszystkich komórek, w przeciwnym razie wykonanie operacji po prostu nie będzie możliwe, a jeśli się powiedzie, wszystkie wartości ulegną zmianie, ponieważ formuły będą odwoływać się do zupełnie innych komórek, niż powinny.
2. Jeśli w przyszłości będziemy potrzebować usuń sortowanie i powrócić do poprzedniego widoku tabeli, warto się tym zająć na tym etapie. Ponumerujmy wiersze oryginalnej tabeli na wypadek, gdybyśmy chcieli do niej wrócić, a wtedy będziemy mogli posortować wiersze według numeracji.
Jak usunąć formuły ze wszystkich komórek arkusza
Aby usunąć wszystkie formuły jednocześnie należy:
1. Wybierz cały obszar arkusza, dla którego musisz kliknąć lewym przyciskiem myszy w lewym górnym rogu szarego pola:
2. Skopiuj zaznaczony obszar (bez odznaczania obszaru, kliknij menu prawym przyciskiem myszy i wybierz „Kopiuj”)
3. Użyj opcji Wklej specjalnie (bez odznaczania obszaru, kliknij menu prawym przyciskiem myszy i wybierz „Wklej specjalnie” -> „Wartości”)
Cóż, teraz możesz usunąć lub ukryć dodatkowe kolumny bez obawy, że zmienisz raport.
Przed przystąpieniem bezpośrednio do sortowania należy musisz sprawdzić, czy sortowana tabela nie zawiera scalonych komórek w nagłówku lub gdzie indziej. W takim przypadku Excel przeklnie i napisze: „Wymaga to, aby komórki miały ten sam rozmiar”.
Jeśli nie zamierzasz rozdzielać komórek nagłówka i zmieniać tabeli, możesz przenieść kolumnę do sortowania do lewego skrajnego wiersza i posortować ją za pomocą przycisków sortowania.
Na przykład chcemy posortować tabelę w kolejności malejącej według przychodów. Wybierz wiersze, które mają zostać posortowane według szarego pola bez nagłówka i kliknij „Sortuj malejąco”:
OSTRZEŻENIE: Dotyczy to tylko scalone komórki w nagłówku. Jeśli w tabeli znajdują się scalone komórki, program Excel nie posortuje tych wierszy. Będziemy musieli usunąć powiązanie.
Jeśli tabela nie zawiera scalonych komórek, możesz sortować bezpośrednio w tabeli, bez przesuwania posortowanej kolumny.
W tym celu należy zaznaczyć cały obszar tabeli wraz z nagłówkiem, w menu głównym odszukać „Dane” i wybrać „Sortowanie”:
W wyskakującym oknie Excel zaoferuje sortowanie według wszystkich wskaźników wymienionych w nagłówku. Wybieramy „Zysk netto”:
Jeśli połączymy ten rodzaj sortowania z sortowaniem poprzez autofiltr, możemy posortować dane według działów, menadżerów i klientów. Dodajmy kolumnę sortującą i posortujmy dane według menedżera:
Jak widać, w rezultacie mamy wygodny raport, który bardzo wyraźnie pokazuje, którzy menedżerowie przynoszą największe zyski, a kto współpracuje z nierentownymi klientami.