Загрузите образец книги
В этом руководстве будет показано, как найти сумму текстовых значений, где каждому такому текстовому значению присваивается уникальный код в Excel и Google Sheets.
SUM числа, сохраненные как текст
Сначала мы рассмотрим, как суммировать числа, сохраненные или отформатированные как текст.
В следующем примере столбец Заработная плата сохраняется в виде текста. При попытке суммировать значения Excel отобразит ноль.
| 1 | = СУММ (C3: C7) |
Вместо этого для выполнения операции SUM с числами, хранящимися в виде текста, вы можете использовать формулу массива с функциями SUM и VALUE следующим образом:
| 1 | = СУММ (ЗНАЧЕНИЕ (C3: C7)) |
Функция VALUE преобразует текст, представляющий число, в число. Функция СУММ суммирует эти числа.
В Excel 365 и версии Excel новее 2022 года вы можете просто ввести формулу как обычно. Однако при использовании Excel 2022 и более ранних версий необходимо ввести формулу массива, нажав CTRL + SHIFT + ВВОД (вместо того ВХОДИТЬ), сообщая Excel, что формула является формулой массива. Вы узнаете, что это формула массива, по фигурным скобкам, заключенным вокруг формулы (см. Верхнее изображение). В более поздних версиях Excel и Excel 365 вы можете просто нажать ВХОДИТЬ вместо.
Давайте посмотрим на следующее объяснение, чтобы лучше понять формулу.
ЗНАЧЕНИЕ Функция
Используемая как формула массива, функция VALUE преобразует весь диапазон чисел, хранящихся в виде текста, в массив чисел и возвращает его в качестве входных данных для функции SUM.
| 1 | = СУММ ({10000; 6500; 7500; 15000; 8000}) |
Чтобы увидеть, что возвращает функция VALUE, выберите необходимую функцию и нажмите F9.
СУММА текстовых значений
Чтобы СУММИРОВАТЬ диапазон текстовых значений, где каждому такому текстовому значению присваивается уникальный код, можно использовать формулу массива.
В следующей таблице записано, что люди выбирают для трех заданных вопросов. В таблице справа перечислены четыре возможных ответа, каждому из которых присвоено кодовое значение. Мы должны суммировать коды для каждого человека.
| 1 | = СУММ (ИНДЕКС (I $ 3: I $ 6; N (ЕСЛИ (1; ПОИСКПОЗ (C4: E4; H $ 3: H $ 6,0))))) |
Мы рассмотрим формулу ниже.
Функция ПОИСКПОЗ
Функция MATCH ищет указанный элемент в диапазоне и возвращает его относительное положение в этом диапазоне. Его синтаксис:
Будучи в этом примере формулой массива, давайте посмотрим, что возвращает функция MATCH.
| 1 | = СУММ (ИНДЕКС (I $ 3: I $ 6; N (IF (1, {1,3,2})))) |
Для данного человека функция ПОИСКПОЗ находит относительное положение каждого ответа в диапазоне H3: H6. В результате получается массив позиций.
Примечание: В формуле массива, чтобы просмотреть, что возвращает функция, выберите требуемую функцию и нажмите F9.
IF & N Функция
Функции IF и N, используемые вместе, возвращают следующий массив в качестве входных данных для функции INDEX.
| 1 | = СУММ (ИНДЕКС (I $ 3: I $ 6; {1,3,2})) |
Здесь две функции возвращают массив относительного положения ответов в диапазоне H3: H6. Целью использования функций IF и N является выполнение процесса, называемого разыменованием. Проще говоря, две функции заставляют функцию INDEX передавать весь массив кодовых значений функции SUM.
Мы объясним это в следующем разделе.
ИНДЕКС Функция
Функция ИНДЕКС возвращает значение, расположенное на пересечении указанной строки и столбца в диапазоне. Его синтаксис:
Давайте посмотрим, как это работает как формула массива:
| 1 | = СУММ ({10,5,8}) |
Функция ИНДЕКС находит кодовые значения в диапазоне I3: I6 в соответствии с заданными номерами позиций. Затем он возвращает массив значений, то есть соответствующий код для каждого ответа, функции SUM для выполнения операций.
Убедитесь, что количество строк и столбцов в обоих Отвечать и Код столбец такой же.
Функция СУММ
Функция SUM суммирует значения кода, возвращаемые функцией INDEX.
| 1 | = СУММ ({10,5,8}) |
Все это вместе дает нашу исходную формулу:
| 1 | {= СУММ (ИНДЕКС (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
СУММА текстовых значений - без функций IF и N
В этом разделе объясняется, как Excel реагирует, если мы не используем функции ЕСЛИ и N в вышеупомянутой формуле.
Тот же пример используется с теми же кодами и ответами.
| 1 | {= СУММ (ИНДЕКС (I $ 3: I $ 6, ПОИСКПОЗ (C4: E4, H $ 3: H $ 6,0)))} |
Как видите, функция ИНДЕКС передает в функцию СУММ только код первого ответа. Если вы внимательно изучите функцию ИНДЕКС, нажав F9 вы получите следующее:
Значение! Ошибка возвращается, потому что функция INDEX не может прочитать массив номеров строк как массив. Следовательно, использование функции IF & N делает свое дело.
Примечание: В Excel 365 вы можете полностью отказаться от использования функций ЕСЛИ и N.
Текст суммы - Google Таблицы
Эти формулы работают в Google Таблицах так же, как и в Excel.
