UncleFather » 06 ноя 2016 20:57, Вс
Задача:
Необходимо одной формулой посчитать сумму цифр числа в ячейке в Microsoft Excel.
Решение:
Хорошее решение приведено в статье Excel. Сумма цифр целого числа
Процитирую статью:
Приведенная ниже формула массива возвращает сумму цифр, составляющих положительное целое число, которое содержится в ячейке А1. Например, если в этой ячейке хранится число 409, то формула вернет число 13 (сумму цифр 4, 0 и 9):
{=СУММ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)*1)}
Чтобы понять, как работает эта формула, рассмотрим последовательно ее работу.
-
ДЛСТР(A1) возвращает количество цифр числа, находящегося в ячейке А1. В нашем примере, 3.
-
Функция {=СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)))} возвращает массив целых чисел, который начинается с единицы и заканчивается числом, равным количеству цифр, которые составляют значение, хранящееся в ячейке А1. В нашем примере СТРОКА создаст следующий виртуальный массив: {1;2;3}
-
Функция ПСТР возвращает часть текстовой строки. Функция ПСТР имеет три аргумента:
(1) текст; в нашем примере содержимое ячейки А1, 409;
(2) начальная позиция; в нашем примере – массив {1;2;3}, то есть в первом заходе будет использовано значение 1, во втором – 2, в третьем – 3;
(3) количество извлекаемых символов; в нашем примере, 1.
В нашем примере функция ПСТР выглядит так: {=ПСТР(409;{1;2;3};1)}. Функция создаст массив из трех элементов: {4;0;9}.
-
К сожалению, функция ПСТР возвращает массив строк, которые содержат текст. Чтобы преобразовать строку, которая выглядит как число, в соответствующее число, следует умножить ее на единицу (что и было сделано). Можно также воспользоваться функцией ЗНАЧЕН, которая преобразует текстовую строку, содержащую число, в соответствующее значение: {=СУММ(ЗНАЧЕН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)))}
-
И, наконец, функция СУММ суммирует элементы массива {4;0;9}: {=СУММ({4;0;9})}. Эта формула возвращает значение 13.
Вы также можете «встать» в ячейку В1 и пройти по меню Формулы → Зависимости формул → Вычислить формулу. В открывшемся окне, нажимая последовательно на «Вычислить», вы будете видеть, как работает эта формула шаг за шагом:
Заметьте, что формула не работает с отрицательными числами, поскольку знак «минус» не является числом. В следующей формуле эта проблема решается с помощью функции ABS, которая возвращает модуль аргумента:
{=СУММ(ЗНАЧЕН(ПСТР(ABS(E6);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ABS(E6))));1)))}
Пример в архиве можно скачать здесь:
Примечание: Для того, чтобы ввести формулу массива в Excel, то есть нужную формулу в фигурных скобках, нужно сначала просто ввести формулу без фигурных скобок (то есть все, что внутри, включая знак равно), затем встать в строку редактирования формул и в ней нажать комбинацию клавиш Crtl+Shift+Enter.
Подробнее о формулах массива в Excel читаем книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel
[b][size=150]Задача:[/size][/b]
Необходимо одной формулой посчитать сумму цифр числа в ячейке в Microsoft Excel.
[b][size=150]Решение:[/size][/b]
Хорошее решение приведено в статье [url=http://baguzin.ru/wp/?p=3458]Excel. Сумма цифр целого числа[/url]
Процитирую статью:
Приведенная ниже формула массива возвращает сумму цифр, составляющих положительное целое число, которое содержится в ячейке [i]А1[/i]. Например, если в этой ячейке хранится число [i]409[/i], то формула вернет число [i]13[/i] (сумму цифр 4, 0 и 9):
[size=150][color=#00BF00][b][i]{=СУММ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)*1)}[/i][/b][/color][/size]
Чтобы понять, как работает эта формула, рассмотрим последовательно ее работу.
[list=1][*] [b][i]ДЛСТР(A1)[/i][/b] возвращает количество цифр числа, находящегося в ячейке [i]А1[/i]. В нашем примере, [i]3[/i].
[*] Функция[b][i] {=СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)))}[/i][/b] возвращает массив целых чисел, который начинается с единицы и заканчивается числом, равным количеству цифр, которые составляют значение, хранящееся в ячейке [i]А1[/i]. В нашем примере [i]СТРОКА[/i] создаст следующий виртуальный массив: [i]{1;2;3}[/i]
[*] Функция [b][i]ПСТР[/i][/b] возвращает часть текстовой строки. Функция [i]ПСТР[/i] имеет три аргумента:
(1) текст; в нашем примере содержимое ячейки [i]А1[/i], [i]409[/i];
(2) начальная позиция; в нашем примере – массив [i]{1;2;3}[/i], то есть в первом заходе будет использовано значение [i]1[/i], во втором – [i]2[/i], в третьем – [i]3[/i];
(3) количество извлекаемых символов; в нашем примере, [i]1[/i].
В нашем примере функция ПСТР выглядит так: [b][i]{=ПСТР(409;{1;2;3};1)}[/i][/b]. Функция создаст массив из трех элементов: [i]{4;0;9}[/i].
[*] К сожалению, функция [i]ПСТР[/i] возвращает массив строк, которые содержат текст. Чтобы преобразовать строку, которая выглядит как число, в соответствующее число, следует умножить ее на единицу (что и было сделано). Можно также воспользоваться функцией [i]ЗНАЧЕН[/i], которая преобразует текстовую строку, содержащую число, в соответствующее значение: [b][i]{=СУММ(ЗНАЧЕН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)))}[/i][/b]
[*] И, наконец, функция [b][i]СУММ[/i][/b] суммирует элементы массива [i]{4;0;9}[/i]: [b][i]{=СУММ({4;0;9})}[/i][/b]. Эта формула возвращает значение [i]13[/i].[/list]
Вы также можете «встать» в ячейку [i]В1[/i] и пройти по меню [b][i]Формулы[/i][/b] → [b][i]Зависимости формул[/i][/b] → [b][i]Вычислить формулу[/i][/b]. В открывшемся окне, нажимая последовательно на «[b][i]Вычислить[/i][/b]», вы будете видеть, как работает эта формула шаг за шагом:[attachment=2]01.jpg[/attachment]
Заметьте, что формула не работает с отрицательными числами, поскольку знак «минус» не является числом. В следующей формуле эта проблема решается с помощью функции ABS, которая возвращает модуль аргумента:
[b][i]{=СУММ(ЗНАЧЕН(ПСТР(ABS(E6);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ABS(E6))));1)))}[/i][/b][attachment=1]02.jpg[/attachment]
Пример в архиве можно скачать здесь: [attachment=0]Сумма-цифр-целого-числа.-Пример.rar[/attachment]
[b][size=140]Примечание:[/size][/b] Для того, чтобы ввести формулу массива в Excel, то есть нужную формулу в фигурных скобках, нужно сначала просто ввести формулу без фигурных скобок (то есть все, что внутри, включая знак равно), затем встать в строку редактирования формул и в ней нажать комбинацию клавиш [b][i]Crtl+Shift+Enter[/i][/b].
Подробнее о формулах массива в Excel читаем книгу [url=http://baguzin.ru/wp/?p=7153]Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel[/url]