W tym artykule dowiemy się, jak indeksować i sortować wartości za pomocą kolumny pomocniczej w programie Excel.
Scenariusz:
Czasami pracuje z niemanierowymi danymi. Musimy pobrać krótszą wersję jako wymagane dane z danych tabeli na podstawie kolumny pomocniczej. Tutaj najpierw zrozumiemy, jak uzyskać kolumnę pomocnika, a następnie uzyskać posortowane małe wymagane dane na podstawie danych tabeli.
Jak indeksować wartości w zakresie ?
W tym artykule będziemy musieli użyć funkcji LICZ.JEŻELI. Teraz zrobimy formułę z funkcji. Tutaj podany tekst i liczba mieszanych wartości w zakresie. Musimy je zindeksować w kolejności rosnącej najpierw numerycznie, a następnie alfabetycznie.
Wzór ogólny:-
= LICZ.JEŻELI ( lista , "<=" & wartość_f ) + ( LICZBA (lista) * CZY.TEKST (wartość_f) ) |
lista : lista numerów i tekst
f_value : pierwsza wartość zakresu
Wyjaśnienie:
- Funkcja LICZ.JEŻELI zlicza liczbę wartości w zwróconej liście
- Funkcja COUNT pobiera liczbę liczb w zakresie.
- Funkcja CZY.TEKST sprawdza, czy wartość w wybranej komórce jest tekstem, czy nie. Zwraca TRUE dla tekstu i FALSE dla liczb.
Przykład:
Wszystko to może być trudne do zrozumienia. Rozumiemy, jak uzyskać kolumnę pomocnika za pomocą wyjaśnionej formuły. Tutaj kolumna pomocnicza ma być oparta na wartościach sortowania w kolejności (najpierw liczby, a następnie alfabetycznie). W tym celu użyjemy formuły, aby uzyskać kolumnę Indeks lub rang jako kolumnę pomocniczą dla danych wydatków pokazanych poniżej.
Tutaj znajdziesz wartości w zakresie. Do celów obliczeniowych używamy nazwanego zakresu dla stałej tablicy D5:D12 jako kosztu.
Użyj wzoru:
= LICZ.JEŻELI (wydatek , "<=" & D5 ) + (LICZBA (wydatek) * CZY.TEKST (D5) ) |
Formuła wygląda tak, jak pokazano na powyższym zrzucie. Wartość i tablica są podawane w formule jako nazwany zakres i odwołanie do komórki.
Jak widać indeks pierwszej komórki w zakresie wyjdzie na 5, co oznacza, że jeśli umieścimy wydatki w kolejności sortowania, Elektryczność zajmie 5. miejsce. Teraz skopiuj formułę w innych komórkach, używając opcji przeciągania w dół lub używając klawisza skrótu Ctrl + D, jak pokazano poniżej, aby uzyskać indeks lub rangę dla pozostałych wartości.
Jak widać, teraz mamy listę indeksu lub rangi, która jest sposobem sortowania wydatków. Teraz użyjemy tej kolumny Rank jako kolumny pomocniczej, aby uzyskać krótszą wersję tabeli w tej samej kolejności na nowej liście lub tabeli.
Jak sortować wartości za pomocą kolumny pomocniczej w programie Excel?
Numer sortowania w programie Excel przy użyciu kolumny pomocniczej musi mieć numery indeksu i wymagany zakres do sortowania. Kolumna pomocnicza może być dowolną wymaganą kolejnością. Na przykład, jeśli chcesz uzyskać listę w wymaganej kolejności losowej, po prostu umieść rangę odpowiadającą wartości w kolumnie indeksu lub rang, która będzie działać jako kolumna pomocnicza.
- funkcja INDEKS
- Funkcja PODAJ.POZYCJĘ
- Funkcja WIERSZY
Teraz stworzymy formułę korzystając z powyższych funkcji. Funkcja MATCH zwróci indeks najniższego dopasowania z zakresu. Funkcja INDEKS przyjmuje indeks wiersza jako argument i zwraca odpowiednie wymagane wyniki. Ta funkcja wyszukuje
Formuła ogólna:
= INDEKS ( tablica_zwrotów , PODAJ.POZYCJĘ ( WIERSZE (odniesienie_względne ) , Indeks , 0 ) ) |
return_array : tablica, z której ma zostać zwrócona wartość
Odniesienie względne : generuje w formule porządek rosnący. Może być używany z dowolną macierzą
Indeks : Tablica indeksów tabeli
0 : dokładne dopasowanie
Wyjaśnienie:
- ROWS (relative_reference) zwraca wartość zgodnie z długością rozszerzonej formuły. Jeśli zostanie zastosowany w pierwszej komórce, będzie to jeden, potem drugi i kontynuowany, aż się przedłuży.
- Funkcja MATCH dopasowuje indeks do wartości wiersza, aby uzyskać je w kolejności rosnącej za pomocą funkcji ROWS.
- Funkcja INDEX zwraca dopasowany indeks z odpowiednią wartością.
Przykład:
Wszystko to może być trudne do zrozumienia. Zrozummy więc tę formułę, uruchamiając ją na poniższym przykładzie. Tutaj uszeregowaliśmy dane w kolejności losowej, aby uzyskać pierwsze trzy wartości na podstawie kolumny Indeks. Użyj wzoru, aby uzyskać pierwszą wartość wymaganej krótkiej tabeli.
Użyj wzoru:
= INDEKS ( B5:B13 , DOPASUJ ( WIERSZE (D5:D5);D5:D13,0)) |
Formuła wygląda tak, jak pokazano na powyższym zrzucie. Tablica wartości jest podana jako nazwany zakres i odwołanie do komórki.
Jak widać, pierwszą wartością jest liczba z zakresu, która wypada jako „Mleko”, odpowiadający jej indeks to 1. Teraz skopiuj formułę do innych komórek za pomocą opcji przeciągania w dół lub za pomocą klawisza skrótu Ctrl + D, jak pokazano poniżej, aby uzyskać reszta wartości.
Jak widać wartości są indeksowane w porządku rosnącym. Uzyskaliśmy wszystkie wartości, co również dowodzi, że formuła działa dobrze. Możemy wyodrębnić wartości liczbowe za pomocą formuły tej samej formuły.
Jak widać, mamy krótsze i posortowane wartości z tabeli za pomocą kolumny pomocniczej, która jest odpowiednio indeksowana.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami i tekstem.
- Formuła ignoruje wartość tekstową podczas porównywania liczb i ignoruje liczby podczas dopasowywania wartości tekstowych.
- Kolumna pomocnicza może być dowolnym typem wymaganej kolejności kolumn.
Mam nadzieję, że ten artykuł na temat indeksowania i sortowania wartości za pomocą kolumny pomocniczej w programie Excel jest objaśniający. Zapoznaj się z dodatkowymi artykułami na temat formuł wyszukiwania w programie Excel tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
SUMA, jeśli data jest między : Zwraca SUMA wartości między podanymi datami lub okresem w programie Excel.
Suma, jeśli data jest większa niż podana: Zwraca SUMA wartości po podanej dacie lub okresie w programie Excel.
2 sposoby sumowania według miesiąca w Excelu: Zwraca SUMA wartości w danym miesiącu w programie Excel.
Jak sumować wiele kolumn z warunkiem: Zwraca SUMA wartości w wielu kolumnach mających warunek w programie Excel
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.