Что именно делает эта формула SUMPRODUCT и MID?

Question

В электронной таблице Excel у меня есть текстовые записи H23 , w67 , P45 и т.д., И я хочу суммировать их числовые части, то есть 23 , 67 , 45 .

У меня есть формула

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

который выполняет работу, но я не уверен, что он делает, и я не хочу использовать это, не понимая это полностью. Кто-нибудь может объяснить формулу?

Answer 1

Начните с одной формулы клетки, чтобы избежать путаницы.

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)

1. Все ваши примеры данных начинаются с буквы, но ISERROR(LEFT($H2:$S2)+0) предназначен для поиска числа или буквы в качестве первого символа и добавления 1, если это буква. Функция LEFT по умолчанию использует один крайний левый символ, и TRUE считается 1, а FALSE - 0. Если крайний левый символ является алфавитным, тогда будет сгенерирована ошибка при использовании его в любой математической операции (например, <символ> + 0), поэтому 1 будет добавлен к 1 в параметре start_num функции MID и начнется с 2 Если ошибка не генерируется, то 0 будет добавлено к 1 в параметре start_num.
2. 10 - это максимальное количество символов, которое будет пытаться получить функция MID. Там может быть меньше доступных, и он будет получать их.
3. Если числа вообще нет, функция SUMPRODUCT захлебнется, пытаясь преобразовать строку нулевой длины (практически пустую) в число. Чтобы избежать этого, перед результатом, возвращаемым из операции MID, ставится 0.
4. Результатом операции MID (и добавленного 0) является строка, а не число. Пока все прошло хорошо, результатом должны быть все цифры. Строка, содержащая все цифры, может быть преобразована в истинное число без изменения ее числового значения путем умножения на 1 или добавления 0. Например, если вы добавите 0 к "123", вы получите истинное число 123.

Это все внутренние операции. Все, что вам нужно сделать, это развернуть это до нескольких столбцов и разрешить обработку массива с функцией SUMPRODUCT в качестве оболочки.

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

Когда столбцы заблокированы как абсолютные, но строки остаются относительными, заполните следующие строки.