Questão Valor de saída correspondente com base no intervalo


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 verificar se os termos de pesquisa contêm

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  |
+-----------------------------+--------+------+

Separador fragmentos de texto

nike
adidas
converse

Desejo adicionar uma coluna à primeira guia para gerar o valor correspondente na guia de fragmento 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 eu fico #VALUE!


1


origem




Respostas:


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 seqüências de texto de fragmento estão na tab2! C3: C5.

Agora na F3, coloque 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 guia 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)))),"")

enter image description here

enter image description here

enter image description here

Atualizar:

Se você estiver usando o Google Spreadsheets, a mesma fórmula funciona bem também. Pressionar CTRL + SHIFT + ENTER encapsula a fórmula em um nome de função ArrayFormula. Veja a imagem abaixo.

enter image description here


1



alguma idéia de como fazer uma fórmula de matriz no google sheet / excel para mac? Além disso, qual é o impacto de 9^99 na sua fórmula? - Bogdan
Verifica support.google.com/docs/answer/3093275?hl=pt para o Google Spreadsheets - pat2015
A fórmula procura a presença de todas as listas de fragmentos, uma a uma, em uma única célula da tabela principal. Se uma correspondência for encontrada, o número da linha da célula de fragmento será retornado, se não retornar um número grande (por exemplo, 9 ^ 99) muito além dos números de linha máximos no Excel. Min dessa matriz é o número da linha da string de fragmento correspondente. Manipule subtraindo o número da linha da primeira célula antes da lista, você obtém uma posição Index do número da string fragmentada começando pelo número 1. Agora aplique a função Index e retorne a string combinada da lista e coloque-a na coluna F na tabela principal. - pat2015
A partir da pesquisa na Internet - Excel para Mac - Fórmula de matriz - CONTROL + U e, em seguida, pressione ⌘ + RETURN e, para o Mac Excel 2016, CTRL + SHIFT + RETURN. No entanto, não verifiquei como não tenho Mac. - pat2015


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

enter image description here

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

  1. ALT-F11 traz a janela do VBE
  2. ALT-I ALT-M abre um novo módulo
  3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

  1. abrir a janela do VBE como acima
  2. limpar o código
  3. feche a janela do VBE

Para usar o UDF do Excel:

= minha função (A1)

Para saber mais sobre macros em geral, consulte:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

e

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

e para detalhes sobre UDFs, consulte:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

As macros devem estar habilitadas para que isso funcione!


0