Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak znaleźć sumę wartości tekstowych, w których do każdej takiej wartości tekstowej jest przypisany unikalny kod w Excelu i Arkuszach Google.
Liczby SUMA przechowywane jako tekst
Najpierw przyjrzymy się, jak sumować liczby zapisane lub sformatowane jako tekst.
W poniższym przykładzie kolumna Wynagrodzenie jest przechowywana jako tekst. Jeśli spróbujesz zsumować wartości, Excel wyświetli zero.
| 1 | =SUMA(C3:C7) |
Zamiast tego, aby wykonać operację SUMA na liczbach przechowywanych jako tekst, można użyć formuły tablicowej z funkcjami SUMA i WARTOŚĆ w następujący sposób:
| 1 | =SUMA(WARTOŚĆ(C3:C7)) |
Funkcja WARTOŚĆ konwertuje tekst reprezentujący liczbę na liczbę. Funkcja SUMA sumuje te liczby.
W programie Excel 365 i wersji programu Excel nowszej niż 2022 możesz po prostu wprowadzić formułę jak zwykle. Jednak w przypadku korzystania z programu Excel 2022 i starszego należy wprowadzić formułę tablicową, naciskając CTRL + SHIFT + ENTER (zamiast WEJŚĆ), informując program Excel, że formuła jest formułą tablicową. Po nawiasach klamrowych, które pojawiają się wokół formuły, poznasz, że jest to formuła tablicowa (patrz górny obraz). W nowszych wersjach programu Excel i Excel 365 wystarczy nacisnąć WEJŚĆ zamiast.
Przyjrzyjmy się poniższemu wyjaśnieniu, aby lepiej zrozumieć formułę.
WARTOŚĆ Funkcja
Używana jako formuła tablicowa, funkcja WARTOŚĆ konwertuje cały zakres liczb przechowywanych jako tekst na tablicę liczb i zwraca ją jako dane wejściowe funkcji SUMA.
| 1 | =SUMA({10000;6500;7500;15000;8000}) |
Aby zobaczyć, co zwraca funkcja WARTOŚĆ, wybierz żądaną funkcję i naciśnij F9.
SUMA wartości tekstowych
Aby zsumować zakres wartości tekstowych, w którym do każdej takiej wartości tekstowej przypisany jest unikalny kod, można użyć formuły tablicowej.
Poniższa tabela pokazuje, co ludzie wybierają dla trzech podanych pytań. Tabela po prawej zawiera cztery możliwe odpowiedzi, z których każda ma przypisaną wartość kodu. Musimy zsumować kody dla każdej osoby.
| 1 | =SUMA(INDEKS(I$3:I$6;N(JEŻELI(1,PODZIEL(C4:E4,H$3:H$6,0))))) |
Przejdziemy przez poniższy wzór.
Funkcja DOPASUJ
Funkcja MATCH wyszukuje określony element w zakresie i zwraca jego względną pozycję w tym zakresie. Jego składnia to:
Będąc formułą tablicową w tym przykładzie, zobaczmy, co zwraca funkcja PODAJ.POZYCJĘ.
| 1 | =SUMA(INDEKS(I$3:I$6;N(JEŻELI(1,{1,3,2}))))) |
Dla danej osoby funkcja MATCH znajduje względną pozycję każdej odpowiedzi w zakresie H3:H6. Wynikiem jest tablica pozycji.
Notatka: W formule tablicowej, aby zobaczyć, co zwraca funkcja, wybierz żądaną funkcję i naciśnij F9.
JEŚLI i N Funkcja
JEŻELI i funkcja N użyte razem zwracają następującą tablicę jako dane wejściowe dla funkcji INDEKS.
| 1 | =SUMA(INDEKS(I $3:I $6;{1,3,2})) |
Tutaj dwie funkcje zwracają tablicę względnej pozycji odpowiedzi w zakresie H3:H6. Celem użycia funkcji JEŻELI i N jest wykonanie procesu zwanego wyłuskiwaniem. Mówiąc prościej, te dwie funkcje wymuszają na funkcji INDEKS przekazanie całej tablicy wartości kodu do funkcji SUMA.
Wyjaśnimy to w następnej sekcji.
INDEKS Funkcja
Funkcja INDEKS zwraca wartość umieszczoną na przecięciu określonego wiersza i kolumny w zakresie. Jego składnia to:
Zobaczmy, jak działa jako formuła tablicowa:
| 1 | =SUMA({10,5,8}) |
Funkcja INDEX znajduje wartości kodu w zakresie I3:I6 zgodnie z podanymi numerami pozycji. Następnie zwraca tablicę wartości, tj. odpowiedni kod dla każdej odpowiedzi, do funkcji SUMA w celu wykonania operacji.
Upewnij się, że liczba wierszy i kolumn w obu Odpowiedź i Kod kolumna jest taka sama.
Funkcja SUMA
Funkcja SUMA zsumuje wartości kodu zwrócone przez funkcję INDEKS.
| 1 | =SUMA({10,5,8}) |
Wszystko to razem daje naszą początkową formułę:
| 1 | {=SUMA(INDEKS(I$3:I$6,N(JEŻELI(1,PODZIEL(C4:E4,H$3:H$6,0)))))} |
SUMA wartości tekstowych - bez funkcji JEŻELI i N
W tej sekcji wyjaśniono, jak program Excel reaguje, jeśli nie używamy funkcji JEŻELI i N w wyżej wymienionej formule.
Ten sam przykład jest używany z tymi samymi kodami i odpowiedziami.
| 1 | {=SUMA(INDEKS(I$3:I$6;DOPASUJ(C4:E4,H$3:H$6,0)))} |
Jak widać, funkcja INDEX przekazuje tylko kod pierwszej odpowiedzi do funkcji SUMA. Jeśli przyjrzysz się funkcji INDEX, naciskając F9 otrzymasz następujące elementy:
Wartość! Zwracany jest błąd, ponieważ funkcja INDEKS nie może odczytać tablicy numerów wierszy jako tablicy. Dlatego użycie funkcji JEŻELI i N załatwia sprawę.
Notatka: W programie Excel 365 możesz całkowicie pominąć używanie funkcji JEŻELI i N.
Suma tekstu - Arkusze Google
Te formuły działają tak samo w Arkuszach Google, jak w programie Excel.
