Contagem de Intervalos do Excel dentro de uma célula

Question

Estou tentando comparar quantidade e número de entradas em uma planilha do Excel.

Por exemplo,

QTY RefDes      # of Entries    QTY Match RefDes?
3   R1,R5,R22   3               MATCH
3   C1,C2,C3    3               MATCH
3   U1-U3       1               MISMATCH
4   U1-U3, U5   2               MISMATCH

Usando =LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2),",",""))+1 para a coluna listada # de Entradas, posso comparar facilmente o QTY vs. ReDes listado quando os valores individualmente são separados por vírgulas como em R1, R5, R22.

(QTY Match RefDes? é simplesmente comparar se a coluna QTY corresponde à coluna # de inscrições).

No entanto, também tenho casos em que U1-U3 denota um intervalo de U1 a U3 ou U1, U2, U3. A quantidade real é 3, mas a fórmula acima retorna um valor de 1 porque não há separação por vírgula. Como posso explicar este caso?

    

Answer 1

Isso PODE ser feito com uma fórmula, mas há uma ressalva.

O Excel tem uma função útil chamada EVALUATE (), que avalia seqüências de texto como equações e produz um resultado numérico. A advertência tem a ver com as restrições sobre como ela pode ser usada.

Eu adivinhei que seus intervalos na Coluna U poderiam ser de qualquer tamanho, então uma boa maneira de obter a contagem seria fazer a subtração. isto é, para U1-U3, extraia o "1-3" e faça as contas. Esta fórmula faz isso:

 =IFERROR(ABS(EVALUATE((REPLACE(MID(B2,FIND("-",B2)-1,4),3,1,"")))),0)

Usando o B5 como exemplo, a parte MID () obtém caracteres em ambos os lados do "-" e fornece 1-U3. O REPLACE () remove o U e o EVALUATE () executa a subtração, dando -2. ABS () dá 2 e IFERROR () manipula as linhas sem intervalo U. Aqui estão os dados com a equação acima na Coluna E, e a soma desta coluna mais sua equação acima na Coluna F:

Agora,sobreaadvertência.Poralgumarazão,EVALUATE()estádisponívelAPENASseforusadonadefiniçãodeumintervalonomeado.Então,parausaraequaçãoacima,cliqueemqualquercélula,dêumnomeàcélula(usei"Funk" aqui) e cole a equação na caixa "Refere-se a:".

Agoraonomepodeserusadocomoumafunção.Digite"= Funk" (sem aspas) em E2 e preencha.

Se você não puder ter a coluna auxiliar, poderá combinar as duas equações na definição do nome. Mas vou avisá-lo que as fórmulas de solução de problemas na caixinha são um PITA. É uma pena que esta função não esteja disponível sem passar por esses arcos.

Espero que isso ajude.