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

Ответить


Этот вопрос предназначен для предотвращения автоматической отправки форм спам-ботами.
Смайлики
:| :) :wink: :D :lol: :( :cry: 8) :o :oops: :? :x :P :evil: :twisted: :roll: :!: :?: :idea: :arrow: :mrgreen:
Ещё смайлики…

Markdown is OFF

BBCode ВКЛЮЧЁН
[img] ВКЛЮЧЁН
[url] ВКЛЮЧЁН
Смайлики ВКЛЮЧЕНЫ

Обзор темы
   

Развернуть Обзор темы: MS Excel. Как посчитать сумму цифр числа в ячейке

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


Вернуться к началу