☰

Что именно делает эта формула СУММПРОИЗВ и MID?

tag-icon • tag-icon microsoft-excel worksheet-function
Что именно делает эта формула СУММПРОИЗВ и MID?

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

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

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

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

решение1

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

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)
  1. Все ваши данные образца начинаются с буквы, но предполагается, ISERROR(LEFT($H2:$S2)+0)что в качестве первого символа будет использоваться либо цифра, либо буква, а затем добавляется1если это буква.ЛЕВАЯ функцияпо умолчанию используется один самый левый символ, и значение TRUE считается1и ЛОЖЬ - это0. Если самый левый символ является буквой, то при его использовании в любой математической операции возникнет ошибка (например,<персонаж>+ 0) так что1будет добавлен в1вначальный_номерпараметрФункция MIDи это начнется в2. Если ошибка не выдается, то0будет добавлен в1вначальный_номерпараметр.
  2. The10максимальное количество символов, которые функция MID попытается извлечь. Может быть доступно меньше, и она извлечет их.
  3. Если числа вообще нет, тоФункция СУММПРОИЗВзахлебнется при попытке преобразовать строку нулевой длины (по сути пустую) в число. Чтобы защититься от этого,0предшествует результату, возвращаемому операцией MID.
  4. Результат операции MID (и добавленный0) — это строка, а не число. Если все прошло хорошо, то результат должен состоять только из цифр. Строку, содержащую все цифры, можно преобразовать в истинное число, не меняя ее числового значения, умножив на1или добавление0. например, если вы добавите0к"123"вы получаете истинное число123.

Это все внутренние операции. Все, что вам нужно сделать, это расширить это до нескольких столбцов и разрешить обработку массива с помощьюФункция СУММПРОИЗВв качестве обертки.

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

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

Связанный контент