Excel: lógica booleana e busca por valores com IA

Entendendo a lógica das IAs - Apresentação

Olá, sou Roberto Sabino, instrutor aqui na Alura. Estamos aqui para falar mais uma vez sobre Excel com Inteligências Artificiais (IAs). Para fins de acessibilidade, teremos a minha autodescrição abaixo.

Audiodescrição: Roberto Sabino se identifica como um homem de pele clara, rosto oval e olhos castanhos. Tem cabelos curtos e barba, ambos com partes grisalhas. Em seu corpo, há uma camiseta preta lisa. Está sentado em uma cadeira preta e usa um microfone de mesa. Ao fundo, uma parede vazia e texturizada iluminada em tons de azul.

O que Aprenderemos neste Curso?

Este curso será uma experiência diferente. Com base em um curso que realizamos na Formação Excel, trouxemos uma experiência ampliada para tentarmos aprender mais coisas usando inteligência artificial.

Não é necessário ter visto o curso da Formação, mas com certeza, se você o assistiu, vai aproveitar ainda mais. Neste curso, vamos tentar fazer com que a inteligência artificial responda às dúvidas que possamos ter em um eventual curso ou trabalho, com funções de lógica e de procura e referência no Excel.

Vamos passar por algumas tentativas e poder comparar qual é a melhor maneira de usar inteligência artificial, se precisarmos realizar funções no Excel. Utilizamos um pouco de ChatGPT, e de Bing Chat, e será muito interessante.

Este curso foi desenvolvido sob o conceito de aprendizagem ativa. Portanto, iremos interagir com os conteúdos e poderemos aplicar o que veremos no curso no nosso dia a dia, desde o primeiro momento.

Além disso, temos um Fórum no qual é possível entrar em contato com outras pessoas alunas, fazer perguntas, entrar em contato com a equipe Alura que também vai ajudar e eventualmente responder dúvidas de outras pessoas estudantes.

Sabemos que esse tema é muito interessante. Então, vamos direto para a aula!

Entendendo a lógica das IAs - Recordando a PROCV()

A ideia é discutirmos sobre lógica booleana e procura por valores com suporte de Inteligência Artificial. Já existe um curso na Formação de Excel que aborda esses temas. Nele, criamos uma planilha de registro de produtos, vendedores e algumas informações de vendas.

Explorando a Planilha de Registro

Dentro da aba "Vendas", aplicamos algumas funções de procura e lógica. Destacamos a função ÍNDICE com CORRESP, que talvez seja a mais complexa e que criamos manualmente nesse curso. Posteriormente, na aba "Consultas" realizamos algumas consultas utilizando o PROCV, PROCX e a função SE, todas já estão incorporadas na planilha.

Ao final do curso, propusemos um desafio que consistia no seguinte: através desta matriz, procuramos os quatro itens específicos abaixo:

1. Ache o preço do produto que tenha aproximadamente 20 quantidades;
2. Ache um produto com aproximadamente 35 quantidades;
3. Responda sim, se houver uma bermuda que custa 65,90;
4. Qual o produto mais caro da lista;

A primeira e a segunda ação foram realizadas com o uso da função PROCX.

Respondemos a terceira ação usando uma PROCX e a função SE. Neste passo há um pequeno problema para corrigir.

Por fim, resolvemos a quarta ação também com a PROCX, utilizando a função MÁXIMO.

Agora, o objetivo é usar a Inteligência Artificial para aprofundar nesses pontos e resolver o desafio, que é encontrar outras maneiras de alcançar os mesmos resultados.

A primeira parte do desafio é: "você consegue fazer as mesmas tarefas usando a PROCV"? A segunda parte é: "de quantas maneiras diferentes você consegue responder a essa questão?"

Vamos resolver esses problemas com a ajuda da inteligência artificial. Vamos debater e dialogar com a inteligência artificial, chegando a conclusões, se estão corretas ou não. Pretendemos aprofundar nosso conhecimento e ter uma grande conversa com as inteligências artificiais.

Resolvendo o Desafio com o ChatGPT

Para essa tarefa, escolhemos usar duas inteligências artificiais: Bard e GPT-3, pois o Bard, no momento da gravação deste vídeo, não pareceu fornecer respostas satisfatórias sem muito esforço. Ou seja, ou tínhamos que trabalhar pesadamente no prompt (linha de comando) ou informar muitos detalhes para obter respostas medianas.

Queremos ressaltar um ponto importante: eu, Sabino, trabalhei muito com VBA (Visual Basic for Applications) usando Inteligência Artificial. Na lógica e códigos do VBA, as inteligências oferecem grande auxílio. No entanto, nas funções do Excel, elas parecem ter mais dificuldades, mesmo assim, ainda são úteis e vamos utilizá-las para chegar às nossas conclusões.

Para iniciar, fizemos perguntas para as duas inteligências artificiais, ChatGPT e Bing Chat. Primeiro, copiamos a tabela abaixo localizada na aba "Desafio 1" da planilha:

Colamos essa tabela no prompt junto à pergunta abaixo:

Considere a seguinte planilha no Excel:

[cole a planilha aqui]

Agora, monte uma função que responda à seguinte questão:
Ache o preço do produto que tem aproximadamente 20 quantidades.

A questão que enviamos é exatamente a primeira da aba "Desafio 1".

Abaixo, temos a resposta do ChatGPT:

Para resolver essa questão no Excel, você pode usar a função PROCV para encontrar o preço do produto que tenha aproximadamente 20 unidades em estoque. Primeiro, você precisa criar uma coluna auxiliar para calcular a diferença entre a quantidade de estoque e 20, e depois encontrar o valor absoluto dessa diferença. Em seguida, você pode usar a função PROCV para encontrar o menor valor absoluto na coluna auxiliar e retornar o correspondente.

Vamos supor que a planilha esteja organizada da seguinte forma:

A	B	C
Produtos	Estoque	Preço Unitário
Bermuda	48	R$ 65,90
Bermuda	17	R$ 63,90
Bermuda	37	R$ 70,90
...	...	...

Na célula D2, você pode calcular a diferença entre a quantidade de estoque e 20 e obter o valor absoluto dessa diferença usando a fórmula:

=ABS(B2 - 20)

Arraste essa fórmula para as células abaixo para calcular os valores absolutos para todas as linhas.

Agora, na célula E2, você pode usar a função PROCV para encontrar o menor valor absoluto na coluna D e retornar o preço correspondente. A fórmula ficaria assim:

=PROCV(MÍNIMO (D:D), D:E, 2, FALSO)

Isso irá procurar o menor valor na coluna D (que é o valor absoluto) e retornar o preço correspondente da coluna E.

Lembrando que essa solução pressupõe que você deseja encontrar o preço para a quantidade de estoque mais próxima de 20, considerando os valores absolutos das diferenças. Se houver múltiplos produtos com quantidades próximas de 20, essa fórmula retornará o preço do primeiro produto encontrado.

O ChatGPT pode ou não conhecer o PROCX por causa da restrição de tempo. Nesse momento, estamos utilizando a versão 3.5.

No retorno acima, ele nos instruiu a realizar os seguintes passos:

1. Criar uma coluna auxiliar para calcular a diferença entre a quantidade de estoque e 20;
2. Encontrar o valor absoluto dessa diferença;
3. Utilizar a função PROCV para encontrar o menor valor absoluto na coluna auxiliar e retornar o preço correspondente.

A princípio, a sequência de instruções pode parecer sem sentido, mas, na verdade, é um método mais complexo, que funciona. Ele sugere a criação de uma coluna alternativa "D", onde a diferença seria calculada.

Por exemplo, no primeiro valor da coluna "B" teríamos 28. No segundo valor, transformaríamos -3 em 3, pois ele falou no valor absoluto da diferença. Já no quarto, teríamos 17. Após isso, podemos encontrar o mínimo.

Nesse caso, em vez de sugerir utilizar PROCV, poderíamos utilizar MÍNIMO. Mesmo assim, essa sugestão é uma forma criativa de resolver o problema.

Uma consideração não mencionada pelo ChatGPT é que PROCV automaticamente realiza essa operação, basta ordenarmos a lista pelas quantidades. Também não sabemos se ele considerou que, como a lista não está ordenada, não poderia fazer tal operação.

Seguindo as instruções do ChatGPT, ele sugere criar uma diferença absoluta, arrastar a fórmula para baixo e fazer uma PROCV. Ele quer que seja retornado o preço do produto com quantidade mais próxima de 20.

Vejamos como seria em prática o que o ChatGPT está sugerindo. Voltando à nossa planilha, vamos duplicar a aba "Desafio 1" e chamar essa cópia de "ChatGPT". Todas as instruções dele serão executadas lá.

Primeiramente, vamos inserir uma linha na coluna "E" e criar nela a coluna "Diferença". Ele sugeriu calcular a diferença utilizando a fórmula =ABS(B2 - 20). POrtanto, vamos adicioná-la na célula E3, substituindo o B2 pela célula C3, que corresponde ao primeiro valor (18).

=ABS(C3 - 20)

Em seguida, copiaremos a fórmula para as próximas 7 células, sendo a última E10. Vamos alterar a formatação da coluna "Diferença" para "Geral".

Caso estejamos procurando a quantidade e tentando retornar o preço, a PROCV não vai funcionar. Já vimos no curso anterior que não é possível fazer uma PROCV buscando algo que está à direita. O item de busca deve estar sempre à esquerda.

Nesse caso, não é um grande problema. Bastaria recortar a coluna "Diferença" com "Ctrl+X", selecionar a coluna D com o botão direito e clicar na opção "Inserir as células recortadas".

Com a coluna "Diferenças" realocada, é possível fazer.

Vamos fazer de novo aquela manipulação. Na célula G3, abaixo da primeira questão, vamos fazer o PROCV. O valor a ser procurado será o MÍNIMO dos valores iniciados D3:D10.

Vamos procurar o mínimo desses valores e responder no intervalo D3:E10. Nesse caso, o índice de coluna é 2. Para este caso, desejamos a correspondência exata, portanto, adicionaremos FALSO.

A fórmula completa se encontra abaixo.

=procv(mínimo(D3:D10);D3:E10;2;FALSO

O resultado na célula G3 é 259,9.

Comparando com o que o ChatGPT falou, realizamos o procedimento que ele indica e resolvemos essa questão.

A PROCV faz isso sozinha. A única coisa que precisamos fazer é colocar em ordem crescente; ela não executa.

Para não ficar má impressão, faremos outro procedimento na célula H3, à direita desta. Basta fazer o PROCV para procurar aproximadamente 20. Procuraremos desde estoque até o preço, no intervalo C3:E10. Na quantidade de colunas, adicionaremos três, pois são três colunas para a direita.

Por fim, vamos indicar que desejamos a correspondência aproximada por meio do valor VERDADEIRO. Agora, mudamos para correspondência aproximada.

A fórmula completa se encontra abaixo.

=procv(20;C3:E10;3;VERDADEIRO)

O resultado na célula H3 é 63,9.

Num primeiro momento, isso retorna o valor 63,9 que não é o correto. Por quê? Ele está retornando o valor 17 da coluna E9, mas é porque não está em ordem.

Vamos ordenar, selecionando as oito linhas com os valores das quatro colunas. Clicando no menu "Dados", na barra superior, selecionaremos o botão "Classificar do Menor para o Maior", ou seja, de A a Z.

Com isso, a planilha exibirá a seguinte ordem:

E agora, as duas se equiparam. Ou seja, no final, essa é uma maneira interessante e criativa de fazer. Se perguntássemos de quantas maneiras diferentes é possível fazer esse retorno, eu, Sabino, nunca pensaria nessa abordagem.

Se pudermos ordenar os valores, será muito mais fácil fazer a PROCV diretamente. Colocando em ordem crescente, podemos usar o valor VERDADEIRO, que é a procura aproximada. E no início, inserimos o valor que desejamos aproximadamente (nesse caso, 20).

Mas, no final das contas, a função PROCV com MÍNIMO tem uma vantagem: ela funciona independentemente de estar na ordem. Porém, tem a grande desvantagem de ser confusa e a necessidade de criar uma nova coluna.

A seguir, vamos ver qual foi a sugestão do Bing Chat.

Entendendo a lógica das IAs - Bing Chat usando PROCV()

Vamos pressionar "Ctrl+Z" para retornar a planilha ao estado anterior à ordenação crescente e vamos destacar nossa função executada na célula H3 em vermelho, porque ela só funciona se os valores estiverem na ordem correta.

Vamos verificar o que o Bing Chat fez. Antes disso, alteraremos o nome da aba atual de "ChatGPT" para "IAs", pois fica mais interessante.

Resolvendo o Desafio com Bing Chat

Vamos acessar a página do Bing Chat. Para quem não está acostumado, está no Microsoft Edge, o navegador da Microsoft. Basta clicar no ícone de "B" que aparece ao entrar nesse navegador e ele já vai entrar no Bing Chat.

A sua interface é meio confusa para entender, mas ele funciona relativamente bem. Inclusive, para algo da Microsoft, ele tem surpreendido.

No prompt dele, faremos a mesma pergunta.

Considere a seguinte planilha no Excel:

Produtos         Estoque     Preço Unitário
Bermuda          48          R$ 65,90
Bermuda          17          R$ 63,90
Bermuda          37          R$ 70,90
Bolsa coringa     36       R$ 145,00
Bolsa de couro    19       R$ 259,90
Boné             23          R$ 39,90
Calça jeans      24          R$ 85,90
Calça jeans      34          R$ 89,90

agora monte uma função que responda a seguinte questão: Ache o preço do produto que tenha aproximadamente 20 quantidades

Ele respondeu assim:

Olá, este é o Bing. Eu posso te ajudar com a sua planilha do Excel.

Para encontrar o preço do produto que tenha aproximadamente 20 quantidades, você pode usar a função PROCV. Essa função procura um valor em uma coluna e retorna o valor correspondente em outra coluna.

A sintaxe da função PROCV é:

=PROCV(valor_procurado;matriz tabela;num_indice_coluna;[procurar_intervalo])

Nesse caso, o valor_procurado é 20, a matriz_tabela é o intervalo A2:C9, o núm_índice_coluna é 3 e o procurar_intervalo é VERDADEIRO (para procurar por valores aproximados).

Então, a fórmula seria:

=PROCV(20;A2:C9,3,VERDADEIRO)

O resultado dessa fórmula seria R$ 39,90, que é o preço do boné que tem 23 quantidades. Esse é o produto mais próximo de 20 quantidades na planilha.

Espero que isso tenha te ajudado. Você tem mais alguma dúvida sobre o Excel?

Não vamos debater a parte em que ele diz que matriz_tabela é o intervalo de A2 até C9, porque não dissemos qual era a localização da nossa planilha.

Vamos analisar a fórmula que ele considerou, vista abaixo.

=PROCV(20;A2:C9;3;VERDADEIRO)

Para isso, nós precisamos ter alguns cuidados.

Por exemplo, digamos que não sabemos muito sobre o que é uma PROCV e decidimos simplesmente colar o resultado fornecido pela IA na nossa planilha, pois tudo vai funcionar. Se utilizarmos o intervalo de A2 até C9, teremos algum problema.

Para corrigir isso, vamos colar a fórmula na célula I3 da aba em que estamos trabalhando. Com a célula ainda selecionada, veremos um retângulo azul contornando a área que vai de A2 até C9. Para modificar esse intervalo, vamos deslocar esse retângulo para que envolva o intervalo dos nossos valores (de C3 até E10).

Isso mudará a fórmula na célula I3 para o intervalo apontado.

=PROCV(20;C3:E10;3;VERDADEIRO)

E então, vamos considerar se está certo ou não, se a função funcionar só com esse deslocamento, sem precisar ajustar nada.

Por coincidência e por causa do Chat GPT, adicionamos em vídeos anteriores a coluna "Diferença" entre as colunas "Estoque" e "Preço Unitário". Ela não existia antes. Contudo, o que o Bing Chat queria era que todas as informações fossem apresentadas nas colunas B, C e D e o preço estivesse na coluna D.

Resumindo, na realidade, pela pergunta que fizemos, o Bing Chat errou o resultado. Por coincidência, vai dar certo, mas por pura sorte.

A IA indicou 3 colunas e o valor VERDADEIRO e com isso, ela vai ficar parecida com a nossa função da célula H3 que está em vermelho.

O resultado da célula I3 após a execução do comando é o valor 63,9.

Mas vamos analisar direito, estamos aqui para analisar as respostas da inteligência artificial. Dissemos para o Bing Chat que tínhamos três colunas na planilha: "Produtos", "Estoque" e "Preço Unitário". Depois, dissemos que queremos achar o preço do produto que tem aproximadamente 20 quantidades.

Se ele está procurando 20, está buscando na quantidade. Portanto, ele teria que procurar só em "Estoque" e "Preço".

Ou seja, a resposta que ele nos deu está errada. Não seria o intervalo de A2 até C9 aqui. Seria, por exemplo, de B2 até B9, pois deveria começar em B já que temos três colunas e não queremos a primeira.

Poderíamos nos questionar: "Por que não podemos usar três colunas?"

Queremos que você guarde a resposta: a PROCV sempre vai procurar na coluna que está mais à esquerda. Então, se procurarmos na matriz inteira, estaremos procurando produtos.

Podemos procurar números, mas não vamos encontrar nada, porque ele vai procurar sempre na coluna que está mais à esquerda.

Para funcionar direito, os valores devem estar em ordem, porque a PROCV faz uma busca binária, não percorrendo todos os itens. Ela vai dividindo a tabela no meio e procurando sempre no item do meio. É um método de busca muito mais eficiente, só que, neste caso, não funciona.

Para recordar, nós usamos a PROCX, que é muito parecida com a PROCV, porém é mais nova. Ela permite fazer a busca binária, entretanto, por padrão, ela procura na ordem.

Então, neste caso, a PROCX responde corretamente. Ela é um pouco diferente, apenas inserimos:

• O valor a ser procurado — neste caso, 20;
• A matriz onde queremos procurar — neste caso, de C3 até C10;
• Onde está a resposta — neste caso, de E3 até E10 —, desde que esse intervalo tenha o mesmo tamanho do anterior;
• Um par de aspas duplas para representar um espaço e indicar o que fazer se não encontrar o valor e
• O valor -1 para indicar que é uma busca aproximada ou item menor, para ficar igual à PROCV.

=PROCX(20;C3:C10;E3:E10;"";-1)

Vamos adicionar esse comando na célula J3. Após o "Enter", o resultado exibido é 259,9.

Nesse ponto, a PROCX já resolve, mas abordamos isso no curso da Formação Excel. Então, não vamos trabalhar muito com isso, apenas marcar o valor dessa célula de azul para lembrar que se trata daquela que nós fizemos no outro curso.

Outra pergunta foi feita sequencialmente para duas inteligências artificiais. Vamos tentar compreendê-las.

A primeira foi escrita logo após a resposta com a qual trabalhamos:

De quantas formas diferentes podemos aninhar funções no Excel para fazer uma busca bidimensional?

No entanto, não vamos abordá-la agora. Em vez disso, vamos abrir uma nova conversa no Bing Chat para fazer outra pergunta, pois a pergunta feita acima atrapalharia o resultado. O Bing não permite a remoção de mensagens.

Em um novo tópico do Bing Chat, pediremos que ele seja mais preciso selecionando a opção "Mais Preciso" acima do campo de texto. Primeiro, faremos a pergunta de antes, especificando que queremos uma função do Excel.

Considere a seguinte planilha no Excel:

Produtos         Estoque     Preço Unitário
Bermuda          48          R$ 65,90
Bermuda          17          R$ 63,90
Bermuda          37          R$ 70,90
Bolsa coringa     36       R$ 145,00
Bolsa de couro    19       R$ 259,90
Boné             23          R$ 39,90
Calça jeans      24          R$ 85,90
Calça jeans      34          R$ 89,90

agora monte uma função interna do Excel que responda a seguinte questão: Ache o preço do produto que tenha aproximadamente 20 quantidades

Vamos ver se assim teremos uma resposta.

Frequentemente, percebemos que o Bing Chat mistura português e inglês, principalmente nas funções do Excel. Algumas possuem mais definições, outras não.

Na verdade, todas as informações sobre o Excel, em particular sobre as funções, são mais prevalentes em inglês do que em português. Por isso, uma vez ou outra, ele altera um pouco, trazendo um certo nível de confusão.

Para encontrar o preço do produto que tenha aproximadamente 20 quantidades, você pode usar a função INDEX e MATCH em conjunto com as funções ABS e MIN. A fórmula para encontrar o preço do produto mais próximo de 20 quantidades seria:

=INDEX(C2:C9,MATCH(MIN(ABS(B2:B9-20)),ABS(B2:B9-20),0))

Onde C2:C9 é o intervalo de células que contém os preços dos produtos e B2:B9 é o intervalo de células que contém as quantidades dos produtos. Essa fórmula retornará o preço do produto cuja quantidade é mais próxima de 20. Neste caso, o resultado seria R$ 39,90, que é o preço do Boné, pois sua quantidade (23) é a mais próxima de 20.

A função INDEX é a nossa ÍNDICE, a MATCH seria nossa CORRESP, a MIN é nossa MÍNIMO e a ABS continua como está.

Contudo, não é possível entender bem por que ele faz isso. Talvez esteja fazendo isso porque os dados estão desordenados, de forma semelhante ao ChatGPT. Ambos seguiram por caminhos muito semelhantes.

Vamos testar se a função mencionada funciona. Vamos copiá-la abaixo e levá-la à planilha.

=INDEX(C2:C9,MATCH(MIN(ABS(B2:B9-20)),ABS(B2:B9-20),0))

Vamos colá-la na célula K3 e traduzir INDEX para ÍNDICE. Isso faz sentido.

Depois, vamos traduzir MATCH para CORRESP e MIN para MÍNIMO. Além disso, vamos trocar as vírgulas por ponto e vírgula.

=ÍNDICE(C2:C9;CORRESP(MÍNIMO(ABS(B2:B9-20));ABS(B2:B9-20);0))

O intervalo C2:C9 no índice parece incorreto. Parece estranho.

Após pressionarmos "Enter", veremos que essa função dará um erro. A verdade é que isso não parece funcionar, porque ele buscou um ÍNDICE onde há uma matriz, um CORRESP do MÍNIMO de B2 até B9, menos 20.

Temos que abandonar essa ideia. Isso pode funcionar eventualmente, já que é função matricial, mas é bastante complexo de se pensar.

Dentro de CORRESP não pode ser absoluto, portanto esse trecho está errado. Em relação ao índice, temos um índice polimórfico, como se fossem dois diferentes, mas não vamos aceitar isso. Essa ideia não está boa e não conseguimos utilizá-la.

Vamos deletar essa função da K3 e retornar ao Bing Chat para ver qual é a explicação dele.

Ele diz que C2:C9 é o intervalo de células que contém os preços, que B2:B9 é o intervalo de células que contém as quantidades e que esta fórmula retornará o preço do produto cuja quantidade mais próxima é 20. Neste caso, segundo ele, o resultado seria 39, que é o preço do boné.

Para encerrar o vídeo, vamos fazer a seguinte pergunta a ele:

Se as quantidades estivessem ordenadas por ordem crescente de valor, seria mais fácil?

Vamos ver se ele vai dar a resposta óbvia, que seria um "sim".

Não, a ordem das quantidades não afetaria a fórmula que eu forneci anteriormente. A fórmula =INDEX(C2:C9,MATCH(MIN(ABS(B2:B9-20)),ABS(82:89-20),0)) funcionaria independentemente da ordem das quantidades, pois ela calcula a diferença absoluta entre cada quantidade e o valor desejado (20) e, em seguida, encontra o indice da quantidade com a menor diferença absoluta. Portanto, a ordem das quantidades não afetaria o resultado final.

A resposta que ele deu é uma resposta válida, mas não é a resposta correta no sentido de que sabemos que a função PROCV já responderia isso diretamente se as quantidades estivessem ordenadas.

Os exercícios dessa primeira aula serve para tentarmos entender a lógica da inteligência artificial, mas iremos entender aos poucos. No próximo vídeo, tentaremos concluir essa ideia inicial.

Sobre o curso Excel: lógica booleana e busca por valores com IA

O curso Excel: lógica booleana e busca por valores com IA possui 153 minutos de vídeos, em um total de 42 atividades. Gostou? Conheça nossos outros cursos de IA para Dados em Inteligência Artificial, ou leia nossos artigos de Inteligência Artificial.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Entendendo a lógica das IAs
• Aprimorando o Prompt
• Detalhando o Prompt
• Analisando as funções
• Criando desafios