☰

O que exatamente esta fórmula SUMPRODUCT e MID está fazendo?

tag-icon • tag-icon microsoft-excel worksheet-function
O que exatamente esta fórmula SUMPRODUCT e MID está fazendo?

Em uma planilha do Excel , tenho entradas de texto H23,,,, etc. e quero somar as partes numéricas delas, ou seja ,,,, .w67P45236745

eu tenho a fórmula

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

que faz o trabalho, mas não tenho certeza do que está fazendo e detesto usá-lo sem entendê-lo completamente. Alguém pode explicar a fórmula?

Responder1

Comece com uma fórmula de célula única para evitar confusão.

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)
  1. Todos os seus dados de amostra começam com uma letra, mas o ISERROR(LEFT($H2:$S2)+0)objetivo é procurar um número ou uma letra como o primeiro caractere e adicionar1se for uma carta. OFunção ESQUERDAo padrão é um único caractere mais à esquerda e TRUE é considerado1e FALSO é0. Se o carácter mais à esquerda for alfabético então será gerado um erro ao utilizá-lo em qualquer operação matemática (por exemplo<personagem>+ 0) então1será adicionado ao1nonúm_inicialparâmetro doFunção MÉDIAe começará às2. Se nenhum erro for gerado então0será adicionado ao1nonúm_inicialparâmetro.
  2. O10é o número máximo de caracteres que a função MID tentará recuperar. Pode haver menos disponíveis e ele irá recuperá-los.
  3. Se não houver nenhum número, oFunção SOMARPRODUTOvai engasgar ao tentar converter uma string de comprimento zero (essencialmente em branco) em um número. Para se proteger contra isso, um0é precedido pelo resultado retornado da operação MID.
  4. O resultado da operação MID (e o anexo0) é uma string, não um número. Desde que tudo tenha corrido bem, o resultado deverá ser composto por todos os dígitos. Uma string contendo todos os dígitos pode ser convertida em um número verdadeiro sem alterar seu valor numérico, multiplicando-se por1ou adicionando0. por exemplo, se você adicionar0para"123"você obtém um número verdadeiro de123.

Essas são todas as operações internas. Tudo o que você precisa fazer é expandir isso para várias colunas e permitir o processamento do array com oFunção SOMARPRODUTOcomo um invólucro.

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

Com as colunas bloqueadas como absolutas, mas as linhas permanecendo relativas, preencha as linhas subsequentes.

informação relacionada