Eu tenho uma coleção de strings de texto, cada uma com um valor numérico associado. Preciso somar os valores numéricos associados às entradas que "se qualificam". Uma entrada de texto se qualifica por conter uma ou mais sequências de destino designadas. Uma entrada pode conter potencialmente várias sequências de destino ou uma sequência de destino mais de uma vez. No entanto, desejo somar o valor associado apenas uma vez para a entrada se a entrada se qualificar por conter qualquer correspondência com qualquer um dos alvos ou combinações de alvos.
Por exemplo, digamos que as células A1:A3 contenham respectivamente apple, banana, peare B1:B3 contenham, cada uma, o número 1. Meus alvos de pesquisa são ae p. Todas as três entradas de texto se qualificam porque cada uma contém pelo menos uma instância de pelo menos um dos destinos. A soma dos valores associados na coluna B deve retornar um resultado de 3.
Eu tentei isso usando SUMIF e destinos curinga. Minha fórmula para este exemplo é:
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
No entanto, isso conta duas vezes as entradas que correspondem a mais de um destino. Nesse caso, todos os três contêm ae dois também contêm p, portanto produz uma soma de 5.
Como posso fazer isso sem contar duas vezes as entradas?
Responder1
É mais flexível ter seus critérios em células reais da planilha, em vez de codificados na fórmula.
Se você usar umvertical, intervalo contíguo de células (por exemplo H1:H2, ) para esse fim, e assumindo queFaixaé umverticalalcance, você pode usar estefórmula de matriz**:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))
Se você insiste em ter os critérios na fórmula, então:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))
Cumprimentos
Responder2
Aqui está uma solução relativamente simples. Com todos os valores associados 1, ele produz o resultado desejado de 3, mas atribuí valores diferentes para demonstrar que os valores corretos foram selecionados e incluí uma entrada não correspondente para uma boa medida.
A lista de entradas está na coluna C e seus valores associados na coluna D. O resultado está em E1.
Apenas algumas funções podem usar curingas, então isso usa SEARCH para a string de destino.
O método típico de lidar com vários critérios OR é adicionar os resultados de cada teste. No entanto, isso conta duas vezes quando os itens podem atender a vários critérios. Para resolver isso, os testes de critérios agregados são verificados para ver se a soma é maior que zero, e é isso que é usado com o valor associado.
O tratamento das strings de pesquisa de destino como uma matriz fica complicado, porque as funções comuns usadas para esses tipos de fórmulas calculam um resultado para a matriz inteira antes de aplicá-lo ao próximo termo. Então tratei cada critério separadamente. Para mais critérios, basta adicionar outro ISNUMBER(SEARCH("target",range))para cada um entre parênteses antes do >0teste.
SUMPRODUCT faz cálculos em estilo de matriz com uma fórmula normal e não de matriz.
A fórmula em E1 é:
=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
Responder3
Eu realmente pensei que isso seria uma SUMPRODUCT(--fórmula, mas não consigo fazer uma funcionar. Isso deve funcionar -
=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))
Onde {"d","g"}estará sua matriz de strings de pesquisa.
É uma fórmula de matriz, então, depois de inseri-la, você deve pressionar ctrl+ shft+ entre as chaves devem aparecer na barra de fórmulas ao redor de toda a função.
Observe que isso só funcionará se você estiver pesquisando em uma única coluna.
É possível que isso não funcione tão bem, considerando os curingas. Talvez regex seja o que você precisa?