W tym artykule dowiemy się, jak uzyskać sumę wartości tekstowych, takich jak liczby w programie Excel.
W prostych słowach, podczas pracy z wartościami tekstowymi w zbiorze danych. Czasami stawiany jest warunek, np. gdy potrzebujemy uzyskać sumę wartości tekstowych, w których do wartości tekstowej przypisany jest unikalny kod. Możesz łatwo rozwiązać ten problem, korzystając z funkcji programu Excel, jak wyjaśniono poniżej.
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji SUMA i INDEKS. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy zakres i listę z wartością kodu. Musimy znaleźć SUMA zakresu wartości tekstowych za pomocą jego kodu z listy.
Wzór ogólny:
{ = SUMA ( INDEKS ( kod , N ( JEŻELI ( 1 , DOPASUJ ( zakres , lista , 0 ) ) ) ) ) }
zakres : zakres wartości, w którym obliczana jest suma
lista : lista wartości tekstowych wraz z odpowiednimi wartościami.
kod : wartości liczbowe odpowiadające wartościom z listy
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy tabelę światową, a każdemu krajowi przypisana jest wartość kodu i musimy znaleźć sumę kodów dla odpowiedniego kontynentu.
Lista i kod dostarczający informacje do powyższej tabeli przedstawiono poniżej.
Tutaj lista i zakres kodów są podane przy użyciu narzędzia Excel do nazwanego zakresu.
Najpierw musimy znaleźć wartości odpowiadające wartościom tekstowym, wyodrębnić wartości liczbowe, a następnie wykonać operację sumowania na tych wartościach. Teraz użyjemy następującego wzoru, aby uzyskać sumę.
Użyj formuły:
{ =SUMA ( INDEKS ( kod, N ( JEŻELI (1, PODAJ.POZYCJĘ ( D3:G3 , lista , 0 ) )) ) ) }
NIE używaj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść.
Wyjaśnienie:
- Funkcja PODAJ.POZYCJĘ zwraca indeks wiersza dla wartości D3:G3 z dokładnych wartości dopasowanych na liście wartości, jak pokazano poniżej.
= SUMA ( INDEKS ( kod , N ( JEŻELI ( 1 , { 1 , 13 , 7 , 19 } ) ) ) )
- Funkcja N i JEŻELI wymusza zwrócenie przez formułę tablicy do tablicy jako argumentu funkcji INDEKS.
= SUMA ( INDEKS ( kod , { 1 , 13 , 7 , 19 } ) )
- Teraz funkcja INDEKS dopasowuje numer WIERSZ w wartościach kodu i zwraca odpowiednie wartości kodu do funkcji SUMA.
- Funkcja SUMA wykonuje operację sumowania na tych wartościach i zwraca sumę, jak pokazano poniżej.
- Jest to formuła tablicowa, ponieważ tablica wartości jest przesyłana w formule.
Tutaj zakres Code i oświetlony jest podany jako zakres nazwany. naciskać Ctrl + Shift + Enter aby uzyskać SUMA wymaganych wartości tekstowych, ponieważ jest to formuła tablicowa.
Jak widać na powyższym zrzucie, suma { 33 , 14 , 28 , 40 } daje 115.
Możesz sprawdzić wyniki, korzystając z opcji filtrowania na liście i tabeli kodów, jak pokazano poniżej. Zastosuj filtr i wybierz żądane wartości.
Teraz skopiuj formułę do innych komórek za pomocą Ctrl + D lub przeciągając w dół opcję komórki w programie Excel.
Jak widać na powyższym zrzucie, uzyskaliśmy całą sumę odpowiadającą wartościom tekstowym, wyodrębniając ich wartości kodu.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Formuła działa tylko wtedy, gdy w tabeli listy i kodów nie ma duplikatów.
- Funkcja PODAJ.POZYCJĘ przyjmuje 0 jako argument, aby uzyskać dokładne dopasowanie z listy.
- NIE używaj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść ponieważ jest to formuła tablicowa.
- Długość tablicy listy musi być taka sama jak długość kodu, w przeciwnym razie funkcja.
Mam nadzieję, że ten artykuł o tym, jak uzyskać sumę wartości tekstowych, takich jak liczby w programie Excel, jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
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ść
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty
Konwertuj cale na stopy i cale w programie Excel 2016
Dołącz imię i nazwisko w programie Excel
Policz komórki, które pasują do A lub B