Сумма цифр содержимого ячейки

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: требуется построить формулу, которая возвращает сумму всех цифр в строке текста. Например, применяя формулу к текстовой строке «мне 24 года, а моему отцу – 43», хотелось бы получить 13 (2 + 4 + 4 + 3).

Решение. Вы знаете, что символы, которые участвуют в операции сложения, должны являться цифрами от 1 до 9. Поэтому алгоритм суммирования может быть следующим:

1 * количество единиц в строке +

2 * количество двоек в строке +

… +

9 * количество девяток в строке = результат

Чтобы узнать количество вхождений цифры (скажем, 4), можно воспользоваться функцией ПОДСТАВИТЬ с заменой цифры 4 на пустую строку. Например, ПОДСТАВИТЬ(текст;4;"") вернет текст без всех четверок. В нашем примере – «мне 2 года, а моему отцу – 3». Если подсчитать разность длин исходной и получившейся строк, то это и будет количество четверок в строке: =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;4;"")). Ответ = 2.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Чтобы обработать все цифры в текстовой строке, можно применить формулу массива:
=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9};"")). Этот фрагмент формулы вернет массив из девяти чисел. В нашем примере: {0;1;1;2;0;0;0;0;0}. Т.е., одна двойка, одна тройка и две четверки.

Таким образом, формула
=СУММ((ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9};"")))*{1;2;3;4;5;6;7;8;9}) вернет сумму произведений двух массивов: =СУММ({0;1;1;2;0;0;0;0;0}*{1;2;3;4;5;6;7;8;9}) =СУММ({0;2;3;8;0;0;0;0;0}) =13

Альтернативная стратегия: вместо функции СУММ можно использовать функцию СУММПРОИЗВ; при этом два массива следует разместить в двух аргументах функции: =СУММПРОИЗВ(ДЛСТР(A3)-ДЛСТР(ПОДСТАВИТЬ(A3;{1;2;3;4;5;6;7;8;9};""));{1;2;3;4;5;6;7;8;9}). В таком виде формула, вероятно, выглядит немного более понятной для большинства пользователей.

Изучите следующие примеры:

Примеры

Два последних примера, пожалуй, требуют пояснений. В ячейке А15 набрана формула =ПИ(). Сумма цифр в ячейке В15 – 77 – отражает сумму цифр числа π с максимально возможной точностью. Excel рассчитывает для числа π «лишь» 14 знаков после запятой. В ячейке А16 набрано время – 18:00. Если учесть, что Excel хранит дату и время, как порядковое число, то 18:00 соответствует ¾ суток. В десятичной записи – это 0,75. Вот откуда взялся результат в В16 – 12 (7 + 5)

Резюме: вы можете использовать функции СУММ или СУММПРОИЗВ с элементами массивов, чтобы подсчитать сумму всех цифр в строке текста.

Источник.


Прокомментировать