Como fazer consultas SQL no Excel

Este é um artigo muito bem feito e interessante, que ensina a realizar consultas SQL usando o Excel.

Este excelente artigo, que foi tirado do site Ambiente Office e escrito pelo Felipe Costa Gualberto, ensina a realizar consultas SQL usando o famoso aplicativo da Microsoft, o Excel.

Introdução

Existem bibliotecas no VBA que permitem o uso da ferramenta, como a biblioteca Microsoft ActiveX for Data Objects (ADO). Basicamente, para fazer consultas SQL, é necessário criar uma cadeia de conexão, abrir uma conexão, executar uma consulta para obter os resultados (que ficam armazenados num objeto chamado RecordSet), e ler os resultados. Para saber como preparar sua Pasta de Trabalho para aceitar instruções SQL, veja a página Manipular uma Pasta de Trabalho Através de uma Conexão ADO.

A estratégia utilizada para fazer consultas SQL, nesta página, é acessar a própria Pasta de Trabalho e interpretar uma das Planilhas (chamada BD) como um banco de dados.

Em outra Planilha, que chamei de Temp e que mostra os resultados das consultas SQL, criei uma caixa de texto para efetuar os testes:

A Pasta de Trabalho está com muitos exemplos. Vá até o final da página para baixá-la e estudar como fazer consultas SQL utilizando a classe ADO.

Se você se interessou pelo assunto, pode evoluir o aprendizado desta página para mostrar resultados de uma consulta num Formulário. Veja Filtrar Dados em Formulários.

A função utilizada para executar a instrução SQL é:

Public Sub SQL(sSQL As String, _
               Optional rng As Range, _
               Optional bTemCabeçalho As Boolean = True, _
               Optional bApagarCampos As Boolean = True, _
               Optional wb As Workbook)
'Faz uma consulta SQL e num intervalo, numa Planilha chamada wsTemp.
'Se bTemCabeçalho = True, significa que os cabeçalhos serão gravados no intervalo ou variant de saída.
'Se bApagarCampos = True, o intervalo de largura igual ao número de recordsets de saída serão apagados
'pelo método ClearContents.
'Para funcionar, é necessário adicionar a referência Microsoft ActiveX Data Objects 2.0 ou superior

    Dim lng As Long
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    If wb Is Nothing Then Set wb = ThisWorkbook

    'Aqui cria-se a cadeia de conexão. Note que ela pode
    'variar em função da versão do Excel utilizado:
    If Application.Version < 12 Then
        cn.ConnectionString = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & wb.FullName & ";" & _
          "Extended Properties=Excel 8.0"
    Else
        cn.ConnectionString = _
          "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & wb.FullName & ";" & _
          "Extended Properties=Excel 8.0"
    End If
    cn.Open 'Abre a conexão

    Set rs = cn.Execute(sSQL)

    If rng Is Nothing Then Set rng = wsTemp.Range("A1")
    If bApagarCampos Then
        rng.Resize(, rs.Fields.Count).EntireColumn.ClearContents
    End If
    If bTemCabeçalho Then
        For lng = 0 To rs.Fields.Count - 1
            rng.Offset(, lng) = rs.Fields(lng).Name
        Next lng
        Set rng = rng.Offset(1)
    End If

    rng.CopyFromRecordset rs

    rs.Close
    cn.Close
End Sub

Se você leu a página recomendada sobre conexões ADO, não terá dificuldades em entender o código acima.

Sobre o código no módulo:

Sub Consulta()
    Dim str As String

    wsTemp.Cells.ClearContents
    str = wsTemp.txtConsulta
    str = Replace(str, Chr(11), " ")
    SQL str, wsTemp.Range("A1")
End Sub

Há um tratamento simples dos caracteres da caixa de texto da Planilha para transformar as quebras de linha em espaço.

Os exemplos mostrados são:

Mostrar Nome e Estado de todas as pessoas:
SELECT Nome, Estado
FROM [BD$]
*A palavra chave SELECT, FROM não precisam ser escritas, necessariamente, em minúsculas,
mas tenho hábito de escrevê-las assim.
Observe a forma de escrever a Planilha em que se efetuará a consulta: deve ser
entre colchetes com um cifrão no final.
Você pode colocar entre sinais de acentuação aguda também, se preferir, como:
SELECT Nome, DataNascimento
FROM `BD$`

Se quiser fazer a consulta somente num determinado intervalo do banco
de dados ao invés de fazer a consulta em tudo, use por exemplo:
SELECT Nome, Estado
FROM [BD$A1:G20]
*Note que você deve incluir a linha do cabeçalho quando definir esse intervalo.

Mostrar Nome e Estado das pessoas que são de Minas Gerais:
SELECT Nome, Estado
FROM [BD$]
WHERE Estado='MG'
*Observe que é necessário utilizar aspas simples em MG. Caso contrário, um erro será retornado.

Mostrar Nome e Estado das pessoas que são de Minas Gerais e do Trimestre T1:
SELECT Nome, Estado
FROM [BD$]
WHERE Estado='MG' AND Trimestre='T1'
*É possível usar vários AND na cláusula WHERE.

Mostrar Nome e Estado das pessoas que são de Minas Gerais ou do Trimestre T1:
SELECT Nome, Estado, Trimestre
FROM [BD$]
WHERE Estado='MG' OR Trimestre='T1'
*Você pode misturar AND e OR na cláusula WHERE também, se quiser.

Mostrar todos os dados de BD:
SELECT *
FROM [BD$]

Mostrar todos os dados de BD do Gênero Masculino:
SELECT *
FROM [BD$]
WHERE Gênero='Masculino'

Mostrar lista de todos os salários:
SELECT Salário
FROM [BD$]

Mostrar lista de todos os salários em ordem crescente:
SELECT Salário
FROM [BD$]
ORDER BY Salário ASC
*O termo ASC, que significa ascendente, é opcional, uma vez que o interpretador sempre
considera em ordem ascendente os termos do GROUP BY se não apresentarem o sufixo ASC.

Mostrar lista de todos os salários em ordem crescente, mas eliminando os valores duplicados:
SELECT DISTINCT Salário
FROM [BD$]
ORDER BY Salário

Mostrar Nomes que começam com 'fer':
SELECT Nome
FROM [BD$]
WHERE Nome LIKE 'fer%'
*O % é um caractere curinga semelhante ao * no Excel, ou seja, representa zero ou mais caracteres quaisquer.

Mostrar Nomes que contém a expressão 'fer':
SELECT Nome
FROM [BD$]
WHERE Nome LIKE '%fer%'

Mostrar nomes cuja segunda letra é 'a':
SELECT Nome
FROM [BD$]
WHERE Nome LIKE '_a%'
*O _ é um caractere curinga semelhante ao ? no Excel, ou seja, representa um caractere não-vazio.

Mostrar Nome de pessoas com Salário maior que 10000:
SELECT Nome, Salário
FROM [BD$]
WHERE Salário > 10000

Mostrar Nome de pessoas com Salário menor ou igual a 8000:
SELECT Nome, Salário
FROM [BD$]
WHERE Salário <= 8000
*Observe que para usar o símbolo <= e >= o símbolo de = deve estar depois dos símbolos
de comparação, caso contrário, um erro será retornado.

Mostrar Nome de pessoas com Salário entre 5000 e 6000 (inclusive 5000 e 6000):
SELECT Nome, Salário
FROM [BD$]
WHERE Salário BETWEEN 5000 AND 6000

Mostrar Nome de pessoas com Salário menor que 5000 e maior que 6000:
SELECT Nome, Salário
FROM [BD$]
WHERE Salário NOT BETWEEN 5000 AND 6000

Mostrar Nomes e Salários maiores ou iguais a 10000, do segundo Trimestre, classificados em ordem crescente de salário
SELECT Nome, Salário
FROM [BD$]
WHERE Trimestre = 'T2' AND Salário >= 10000
ORDER BY Salário

Mostrar todos os campos, do segundo Trimestre, classificados em ordem crescente de Estado e em seguida em ordem
decrescente de Estado:
SELECT *
FROM [BD$]
WHERE Trimestre = 'T2'
ORDER BY Estado DESC, Salário ASC
*Observe que ao usar DESC, o campo Estado será ordenado em ordem decrescente.

Mostrar Salários de Minas Gerais do Trimestre T4 com 10% de aumento:
SELECT Salário * 1.1
FROM [BD$]
WHERE Estado = 'MG'
*Observe que o cabeçalho da saída da consulta não se chamará mais Salário,
como estava originalmente no banco de dados.
Isso aconteceu porque foi feita uma operação no campo Salário, que no caso foi uma multiplicação por 0,1.
Para manter o nome original, use:
SELECT Salário * 1.1 AS Salário
FROM [BD$]
WHERE Estado = 'MG'
*Você pode utilizar também as operações de +, -, / e \ (divisão inteira).

Você pode atribuir ao nome de cabeçalho de saída o valor que quiser (sem espaços):
SELECT Nome AS PrimeiroNome, Estado AS UF, Salário AS Remuneração
FROM [BD$]

Mostrar Nome de pessoas que o campo de Salário está em branco:
SELECT Nome, Salário
FROM [BD$]
WHERE Salário IS NULL

Mostrar Nome de pessoas que o campo de Salário não está em branco:
SELECT Nome, Salário
FROM [BD$]
WHERE Salário IS NOT NULL

Mostrar Nome, Estado e Salário das pessoas que ganham mais de 9000 e são dos estados de SP, MG ou RJ.
SELECT Nome, Estado, Salário
FROM [BD$]
WHERE Salário > 9000 AND (Estado = 'SP' OR Estado = 'MG' OR Estado = 'RJ')

Uma forma melhor de escrever a consulta acima é:
SELECT Nome, Estado, Salário
FROM [BD$]
WHERE Salário > 9000 AND Estado IN ('SP', 'MG', 'RJ')

Mostrar a soma, valor máximo, mínimo e média de salários de MG:
SELECT SUM(Salário), MAX(Salário), MIN(Salário), AVG(Salário)
FROM [BD$]
WHERE Estado = 'MG'

Mostrar a quantidade de registros que há em todo banco de dados:
SELECT COUNT(*)
FROM [BD$]

Mostrar a quantidade de registros que há no estado de MG:
SELECT COUNT(*)
FROM [BD$]
WHERE Estado = 'MG'

Mostrar quantos estados distintos tem no banco de dados:
SELECT COUNT(Estado)
FROM [BD$]

Mostrar quantos nomes há em cada Trimestre:
SELECT Trimestre, COUNT(Nome)
FROM [BD$]
GROUP BY Trimestre

Mostrar média de Salário por Trimestre por Gênero:
SELECT Trimestre, Gênero, AVG(Salário)
FROM [BD$]
GROUP BY Trimestre, Gênero

Contar Salário por Gênero de MG em que Salário é maior que 8000:
SELECT Gênero, Count(Salário)
FROM [BD$]
WHERE Salário > 8000 AND Estado='MG'
GROUP BY Gênero

Mostrar Estados que tem mais de 5 pessoas que ganham 13000 reais ou mais por mês,
e qual é o valor máximo em cada um desses grupos:
SELECT Estado, COUNT(Salário), MAX(Salário)
FROM [BD$]
WHERE Salário >= 13000
GROUP BY Estado
HAVING COUNT(*) >= 5

Mostrar o Nome e Data de Nascimento de todas as pessoas que nasceram no dia 10 de Dezembro de 1950:
SELECT Nome, DataNascimento
FROM [BD$]
WHERE DataNascimento = #12/10/1950#
*Muita atenção para como especificar uma data na cláusula WHERE. Além da data ter que estar entre símbolos de #,
o formato que se entra a data é no estúpido padrão americano, ou seja, mês/dia/ano.

Mostrar Nome e Data de Nascimento das pessoas que nasceram em 1983:
SELECT Nome, DataNascimento
FROM [BD$]
WHERE DataNascimento BETWEEN #01/01/1983# AND #12/31/1983#

Mostrar Nome e Data de Nascimento das pessoas que nasceram em Meio de 1983:
SELECT Nome, DataNascimento
FROM [BD$]
WHERE DataNascimento BETWEEN #05/01/1983# AND #05/31/1983#

Na verdade, não uso essas técnicas para filtrar datas. Para o exemplo acima,
verifico que a data 01/05/1983 e 31/05/1983 são, respectivamente, os números 30437 e 30467 e efetuo a consulta:
SELECT Nome, DataNascimento
FROM [BD$]
WHERE DataNascimento BETWEEN 30437 AND 30467
*Para descobrir esses valores, use simplesmente a função CDbl(data) no VBA ou a função do Excel =N(DATA(1983;5;1))

Adicionalmente, se você quiser filtrar horas, minutos e segundos, poderia usar algo como a consulta abaixo:
SELECT Data
FROM [BancoDeDados$]
WHERE Data BETWEEN #05/01/1983 12:00:50# AND #05/01/1983 15:00:45#

Fonte

• O artigo foi reproduzido aqui com permissão do Ambiente Office, onde ele se encontrava, anteriormente.