SQL vs Excel - tabelas dinâmicas e agrupamento de resultados

SQL vs Excel - tabelas dinâmicas e agrupamento de resultados

Se estima que existam 750 milhões de usuários do Excel ao redor do mundo. Sem sombra de dúvidas o Excel é o programa do Microsoft Office mais popular.

Apesar de não existirem números sobre o SQL, ele com certeza não fica atrás. Se estima que 65% de todos os analistas de dados trabalhem com SQL. E considerando todos os tipos de SGBD SQL existentes, o MySQL é o mais popular.

Neste artigo vamos dar uma olhada nas funções de agregação do Excel, união entre várias tabelas e também conferir como trabalhar com Tabelas Dinâmicas, que é um recurso muito utilizado no Excel e indispensável para qualquer tipo de usuário. Neste artigo, vamos abordar estes recursos comparando o que podemos fazer no SQL e ver como podemos chegar em resultados parecidos utilizando o Excel.

Como o Excel é um programa muito utilizado, é interessante para nós que somos usuários do SQL ver como é possível migrar algumas operações de análise e agrupamento que fazemos no SQL para o Excel.

Como diferencial em relação aos demais artigos de banco de dados deste blog, este artigo vai trazer vídeos mostrando passo a passo como fazer os recursos listados no Excel. Os vídeos são cortesia da Fantastic Info Treinamentos. Você pode saber mais sobre o trabalho deles em www.fantasticinfo.com.br. Se você também se interesa por este assunto pode acompanhar o canal deles no Youtube para conferir dicas semanais sobre o Excel.


Funções de agrupamento

 

No SQL possuimos as funções SUM, MAX, MIN e AVG que permitem respectivamente obter a soma, maior valor, menor valor e média de um campo informado.

 

SELECT SUM(campo) as total FROM nome_tabela

 

No Excel também possuímos funções para obter estas informações, que seriam SOMA, MÁXIMO, MÍNIMO e MÉDIA. A diferença em relação ao SQL é que não se trabalha com nomes de colunas, mas com intervalos de células. Células são os elementos da planilha onde inserimos informações, e são referenciadas por um endereço, composto da coluna e da linha onde ela está localizada

 

SOMA(celula_inicial:celula_final)

 

Mas existe uma outra opção para trabalhar com funções de agrupamento, que é a função AGREGAR

 

 

União entre tabelas

 

No SQL para fazer a união entre tabelas podemos usar o JOIN. Quando queremos retornar dados em comum entre duas tabelas, utilizamos INNER JOIN. Caso seja necessário retornar todos os dados de uma tabela, mesmo que eles não existam na outra tabela, podemos usar LEFT JOIN ou RIGHT JOIN de acordo com a nossa necessidade. Como sabemos, para fazer o JOIN os campos de referência devem ser do mesmo tipo e guardar a mesma informação

 

 

Veja a seguir como unir planilhas no Excel

 

 

Tabela Dinâmica

 

A Tabela Dinâmica é um recurso indispensável no Excel. Ela permite fazer análises sintéticas.

No SQL para obtermos uma análise sintética podemos utilizar GROUP BY para fazer o agrupamento de dados e utilizar funções de agrupamento para extrair informações. Como exemplo,  se nosso banco de dados tem uma tabela de vendas e queremos listar os departamentos e seus totais de venda podemos fazer da seguinte forma:

 

SELECT departamento, SUM(vendas) as total FROM tabela_vendas GROUP BY departamento

 

Eventualmente, podemos utilizar HAVING para filtrar por um critério que não faz parte da tabela. Por exemplo, com base no exemplo anterior, se eu quiser ver resultados de vendas apenas dos departamentos que venderam mais de 10 mil eu poderia fazer da seguinte forma

 

SELECT departamento, SUM(vendas) as total FROM tabela_vendas GROUP BY departamento HAVING SUM(vendas) > 10000

 

Veja agora como criar tabelas dinâmicas no Excel e fazer estes tipos de análise

 

 

 

tabela dinamica com valores acumulados

 

Consolidando várias tabelas

 

No SQL para você consolidar várias tabelas e tratar como uma única origem de dados você utiliza o UNION. Para utilizar o UNION você precisa utilizar a mesma quantidade de campos, e os campos devem ter o mesmo tipo de dados. Apesar do nome não ser importante, o tipo de dados e a ordem são importantes.

 

SELECT campo1 as nome, campo2 as cidade FROM funcionarios

UNION

SELECT campo1 as nome, campo2 as cidade FROM prestadores_servicos

 

A consulta acima retornaria todos os nomes e cidades dos registros inseridos nas tabelas funcionarios e pretadores_servicos.

 

Se você quiser por exemplo fazer um agrupamento com base nestas tabelas, pode juntar estes resultados e fazer o agrupamento desta forma

 

SELECT departamento, cidade, COUNT(*) as total FROM (

SELECT campo1 as departamento, campo2 as cidade FROM funcionarios

UNION

SELECT campo1 as departamento, campo2 as cidade FROM prestadores_servicos

) GROUP BY departamento, cidade

 

Agora veja como fazer este tipo de análise no Excel

 

 

Identificando registros repetidos

 

Para identificar registros repetidos no SQL utilizamos o GROUP BY para fazer agrupamento de dados, e a função COUNT, como no exemplo a seguir

 

SELECT email, count(*) as total from funcionarios GROUP BY email ORDER BY count(*) DESC

 

Neste exemplo, se vai contar a quantidade de vezes que o e-mail aparece na tabela funcionarios. Uma forma de ver mais fácil as repetições seria utilizar o HAVING para retornar apenas os registros repetidos

 

SELECT email, count(*) as total from funcionarios GROUP BY email HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

 

Agora vamos ver como identificar valores repetidos utilizando o Excel

 

 

Converter linhas em colunas

 

Este é um recurso muito comum em análise de dados. Imagine o seguinte cenário:

 

SELECT month(data) as mes, departamento, SUM(vendas) as total FROM tabela_vendas GROUP BY departamento, month(data) ORDER BY month(data)

 

O resultado desta consulta deve ser uma lista com o mês, departamento e total de vendas.

 

Ficaria mais fácil para análise se os departamentos fossem linhas e cada mês fosse uma coluna.

Para fazer isso no SQL existem várias opções. A opção mais prática seria fazer um PIVOT.

Como a explicação ficaria um pouco extensa, eu recomendo você ver estes artigos que eu escrevi sobre este assunto. Nos links a seguir temos exemplos de como fazer isso no SQL Server e no MySQL.

 

Convertendo linhas em colunas no SQL Server usando Pivot

Convertendo linhas em colunas usando MySQL

 

No Excel podemos fazer isso de duas formas, a primeira utilizando o recurso TRANSPOR. A segunda forma, mais sofisticada, seria utilizar o PowerQuery

 

Transpor

 

Transformar linhas em colunas usando PowerQuery 

 

Bônus. Como criar um ranking

 

Para encerrar este artigo vamos mostrar como trabalhar com rankings. Neste artigo eu mostro como criar rankings utilizando SQL Server ou MySQL

 

Criando um ranking com SQL

 

E agora como fazer um ranking no Excel

 

Outros conteudos que podem ser de seu interesse

Validar CPF com MySQL
21/02/2021SQL

Validar CPF com MySQL

Aprenda a criar uma função para validar o CPF utilizando MySQL

Saiba mais...
Sites para procurar emprego na area de TI
09/01/2022SQL

Sites para procurar emprego na area de TI

Uma lista de sites no Brasil e no exterior para procurar emprego na área de tecnologia da informação

Saiba mais...

Conteúdo sobre banco de dados sem complicação!