Valor de saída correspondente com base no intervalo

Question

Eu tenho uma planilha com duas guias: uma é um termo de pesquisa de dados de uma campanha do Google AdWords e outra é uma lista de fragmentos de texto.

Eu quero usar uma função para retornar a seleção se os termos de pesquisa contiverem

Guia Termos de pesquisa

+-----------------------------+--------+------+
|         Search term         | Clicks | Cost |
+-----------------------------+--------+------+
| nike running shoes          |     50 | $31  |
| soccer cleats               |     30 | $40  |
| lace up boots               |     40 | $45  |
| spikeless adidas golf shoes |     20 | $15  |
| red/blue converse classics  |     15 | $20  |
| flyknit nike for men        |     25 | $30  |
+-----------------------------+--------+------+

Guia "Fragmentos de texto"

nike
adidas
converse

Eu quero adicionar uma coluna à primeira guia para gerar o valor correspondente na guia de fragmentos de texto ao lado de cada termo de pesquisa.

a saída ficaria assim:

+-----------------------------+--------+------+--------------+
|         Search term         | Clicks | Cost | matched term |
+-----------------------------+--------+------+--------------+
| nike running shoes          |     50 | $31  | nike         |
| soccer cleats               |     30 | $40  |              |
| lace up boots               |     40 | $45  |              |
| spikeless adidas golf shoes |     20 | $15  | adidas       |
| red/blue converse classics  |     15 | $20  | converse     |
| flyknit nike for men        |     25 | $30  | nike         |
+-----------------------------+--------+------+--------------+

Eu tentei usar a função SEARCH em um intervalo: =SEARCH(tab2!A1:A63,A2) , mas recebo #VALUE!

    

Answer 1

Sugiro uma solução a seguir com base em como entendi sua pergunta. Neste exemplo, o Termo de pesquisa está na planilha chamada tab1! C3: F8. As cadeias de texto de fragmentos estão na tab2! C3: C5.

Agora, em F3, ponha a seguinte fórmula e dentro da Barra de Fórmulas Pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz. A fórmula deve ser colocada entre chaves para indicar que é uma fórmula de matriz e arrastá-la para baixo ao longo do comprimento da tabela. Para que isso funcione, você deve deixar uma célula livre acima da lista de tabela de strings fragmentadas, ou seja, iniciar sua lista fragmentada na linha 2 em diante na planilha tab2

=IF(MIN(IF(ISERROR(SEARCH('tab2'!$C$3:$C$5,C3)),9^99,ROW('tab2'!$C$3:$C$5)-ROW('tab2'!$C$2)))<9^99,INDEX('tab2'!$C$3:$C$5,MIN(IF(ISERROR(SEARCH('tab2'!$C$3:$C$5,C3)),9^99,ROW('tab2'!$C$3:$C$5)-ROW('tab2'!$C$2)))),"")

Atualização:

SevocêestiverusandooGoogleSpreadsheets,amesmafórmulafuncionabemtambém.PressionarCTRL+SHIFT+ENTERencapsulaafórmulaemumnomedefunçãoArrayFormula.Vejaaimagemabaixo.

    

Answer 2

Com Sheet1 contendo os termos de pesquisa e Sheet2 contendo os fragmentos, considere a seguinte Função Definida pelo Usuário:

Public Function GetKeyWord(s As String, rng As Range) As String
    Dim s2 As String, r As Range
    s2 = LCase(" " & s & " ")

    GetKeyWord = ""

    For Each r In rng
        If InStr(1, s2, " " & r.Value & " ") > 0 Then
            GetKeyWord = r.Value
            Exit Function
        End If
    Next r
End Function

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofáceisdeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveroUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaroUDFdoExcel:

=myfunction(A1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!