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

Question

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

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 compreendê-lo completamente. Alguém pode explicar a fórmula?

    

Answer 1

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) deve procurar um número ou uma letra como o primeiro caractere e adicionar 1 se for uma letra. A função LEFT é padronizada para um único valor à esquerda character e TRUE são considerados 1 e FALSE é 0 . Se o caractere mais à esquerda for alfabético, será gerado um erro ao usá-lo em qualquer operação matemática (por exemplo, caractere > + 0), então 1 será adicionado a o 1 no parâmetro start_num do Função MID e começará em 2 . Se nenhum erro for gerado, 0 será adicionado ao 1 no parâmetro start_num .
2. O 10 é o número máximo de caracteres que a função MID tentará recuperar. Pode haver menos disponível e recuperará esses.
3. Se não houver nenhum número, a função SUMPRODUCT vai sufocar tentando converter uma cadeia de comprimento zero (essencialmente em branco) em um número. Para proteger-se contra isso, um 0 é precedido pelo resultado retornado da operação MID.
4. O resultado da operação MID (e o acrescentado 0 ) é uma string, não um número. Enquanto tudo correr bem, o resultado deve ser 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 por 1 ou adicionando 0 . por exemplo. Se você adicionar 0 a "123" você obtém um número verdadeiro de 123 .

Essas são todas as operações internas. Tudo o que você precisa fazer é expandir isso para várias colunas e permitir o processamento de matriz com o Função SUMPRODUCT como um wrapper.

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

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