Вычислить от числа в Excel

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую начать с Введение в формулы массива и Некоторые примеры использования формул массива. Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов — это наиболее сложное и непонятное средство Excel.

В настоящей заметке использованы материалы книги Джона Уокенбаха MS Excel 2007. Библия пользователя. — М.: Издательский дом «Вильямс», 2008. — 816 с.

Скачать заметку в формате Word, примеры в формате 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 и пройти по меню Формулы → Зависимости формул → Вычислить формулу. В открывшемся окне, нажимая последовательно на «Вычислить», вы будете видеть, как работает эта формула шаг за шагом:

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