Alexander A Manaeff -

 
 

MS Excel. Как посчитать сумму цифр числа в ячейке

Настройки MS Office, Open Office и пр. офисного ПО. Полезные советы и пр. То, чего не найдешь в бескрайних просторах Интернета. Решения тех проблем, которые не решаются типовыми ответами, которые можно получить в техподдержке Майкрософта - а именно: переустановить продукт или купить какой-ть другой лицензионный диск.

Модератор: UncleFather

MS Excel. Как посчитать сумму цифр числа в ячейке

Сообщение UncleFather » 06 ноя 2016 20:57, Вс

Задача:

Необходимо одной формулой посчитать сумму цифр числа в ячейке в Microsoft Excel.


Решение:

Хорошее решение приведено в статье Excel. Сумма цифр целого числа

Процитирую статью:

Приведенная ниже формула массива возвращает сумму цифр, составляющих положительное целое число, которое содержится в ячейке А1. Например, если в этой ячейке хранится число 409, то формула вернет число 13 (сумму цифр 4, 0 и 9):

{=СУММ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)*1)}

Чтобы понять, как работает эта формула, рассмотрим последовательно ее работу.
  1. ДЛСТР(A1) возвращает количество цифр числа, находящегося в ячейке А1. В нашем примере, 3.

  2. Функция {=СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)))} возвращает массив целых чисел, который начинается с единицы и заканчивается числом, равным количеству цифр, которые составляют значение, хранящееся в ячейке А1. В нашем примере СТРОКА создаст следующий виртуальный массив: {1;2;3}

  3. Функция ПСТР возвращает часть текстовой строки. Функция ПСТР имеет три аргумента:
    (1) текст; в нашем примере содержимое ячейки А1, 409;
    (2) начальная позиция; в нашем примере – массив {1;2;3}, то есть в первом заходе будет использовано значение 1, во втором – 2, в третьем – 3;
    (3) количество извлекаемых символов; в нашем примере, 1.
    В нашем примере функция ПСТР выглядит так: {=ПСТР(409;{1;2;3};1)}. Функция создаст массив из трех элементов: {4;0;9}.

  4. К сожалению, функция ПСТР возвращает массив строк, которые содержат текст. Чтобы преобразовать строку, которая выглядит как число, в соответствующее число, следует умножить ее на единицу (что и было сделано). Можно также воспользоваться функцией ЗНАЧЕН, которая преобразует текстовую строку, содержащую число, в соответствующее значение: {=СУММ(ЗНАЧЕН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)))}

  5. И, наконец, функция СУММ суммирует элементы массива {4;0;9}: {=СУММ({4;0;9})}. Эта формула возвращает значение 13.



Вы также можете «встать» в ячейку В1 и пройти по меню ФормулыЗависимости формулВычислить формулу. В открывшемся окне, нажимая последовательно на «Вычислить», вы будете видеть, как работает эта формула шаг за шагом:
01.jpg


Заметьте, что формула не работает с отрицательными числами, поскольку знак «минус» не является числом. В следующей формуле эта проблема решается с помощью функции ABS, которая возвращает модуль аргумента:
{=СУММ(ЗНАЧЕН(ПСТР(ABS(E6);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ABS(E6))));1)))}
02.jpg



Пример в архиве можно скачать здесь:


Примечание: Для того, чтобы ввести формулу массива в Excel, то есть нужную формулу в фигурных скобках, нужно сначала просто ввести формулу без фигурных скобок (то есть все, что внутри, включая знак равно), затем встать в строку редактирования формул и в ней нажать комбинацию клавиш Crtl+Shift+Enter.

Подробнее о формулах массива в Excel читаем книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel
Аватара пользователя
UncleFather
Site Admin
 
Сообщения: 1311
Зарегистрирован: 17 авг 2004 16:20, Вт

Вернуться в Office

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

Alexander A Manaeff -
@Mail.ru .