Colunas calculadas
funcionalAdicione uma coluna calculada com uma fórmula do Excel a partir das próprias colunas do resultado, com a barra de fórmulas, as cores e as funções do Excel.
Se você escreve fórmulas do Excel, consegue adicionar uma coluna a qualquer resultado de consulta no SQLly. Uma margem, um total acumulado, a variação em relação ao mês passado, o número de dias úteis entre duas datas: digite a fórmula que você digitaria no Excel, e a nova coluna aparece ao lado dos dados. Você não precisa saber escrevê-la em SQL, nem executar a consulta de novo.
Funciona como uma coluna calculada em uma tabela do Excel: uma fórmula para toda a coluna, aplicada em cada linha. A única coisa nova a aprender é como apontar para uma célula, porque um resultado tem nomes de colunas em vez de letras.
Adicione sua primeira coluna calculada
- Execute uma consulta. Qualquer resultado serve; digamos um com
QtyePricecolunas. - Clique com o botão direito no cabeçalho da coluna após a qual a nova coluna deve ser inserida e escolha Adicionar coluna calculada….
- A barra de fórmulas abre acima dos resultados: fx, um campo Nome,
=, e o campo de fórmula. O SQLly preenche um nome (Computed) e inicia a fórmula com uma referência à coluna que você clicou, como=!Price. - Dê um nome à coluna e digite a fórmula - por exemplo Total da linha e
=!Qty * !Price. - Pressione Enter ou clique Adicionar coluna. Esc ou Cancelar fecha a barra sem mudar nada.


De A1 até !Name
Onde o Excel diz B7, o SQLly diz !Qty: um ponto de exclamação e o nome da coluna. Todo o restante na fórmula é o Excel normal.
| No Excel (na linha 7) | No SQLly | Significa |
|---|---|---|
=B7*C7 | =!Qty * !Price | A Qty desta linha vezes o Price desta linha. |
=B7-B6 | =!Qty - !Qty[1] | A mudança em relação à linha anterior. [2] está duas linhas acima. |
=B8 | =!Qty[-1] | A linha depois. |
=B7/B$2 | =!Qty / !Qty[$1] | Comparado com a primeira linha, que permanece fixa em cada linha. |
=SUM(B5:B7) | =SUM(!Qty[2]:!Qty) | Esta linha e as duas anteriores. |
=SUM(C7:E7) | =SUM(!Jan:!Mar) | Ao longo das colunas de Jan a Mar. |
=B7*TaxRate | =!Qty * @rate | Um valor nomeado. @rate lê um -- sqlly set ou -- sqlly param: valor da consulta. |
Coloque um nome de coluna com espaços entre aspas duplas: !"Order Total", ou !"Order Total"[1] para a linha anterior. Nomes ignoram maiúsculas e minúsculas, então !qty também funciona.
As funções são do Excel, com os mesmos nomes, argumentos e respostas: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS e cerca de cem mais. Também os operadores, incluindo & para concatenar texto e %. O página de referência lista todas as funções. Uma extra vem do SQL: COALESCE(a, b, …) retorna o primeiro valor que não está vazio.
Veja o que a fórmula lê
O Excel dá uma cor a cada referência enquanto você digita uma fórmula e desenha uma moldura correspondente ao redor da célula. O SQLly faz o mesmo:
- Cada referência recebe sua própria cor - na ordem do Excel: azul, vermelho, roxo, verde, laranja e assim por diante. Uma referência que você usa duas vezes mantém uma cor. A fórmula colorida é mostrada sob o campo enquanto você digita.
- A grade contorna as células que ela lê - nas mesmas cores, e tinge essas colunas. Os contornos são desenhados para a linha que você selecionou, ou para a primeira, se nenhuma estiver, então
!Qty[1]contorna visivelmente a célula uma linha acima. - Os erros são descritos claramente abaixo do campo - como «Existe um ) sem um ( correspondente.» ou uma dica para escrever uma coluna como
!Name, e Adicionar coluna espera até que a fórmula seja interpretada corretamente.
O que acontece quando você a aplica
A coluna é calculada na hora, a partir das linhas já na tela. Nada vai ao banco de dados e nada é executado de novo, então é instantâneo mesmo quando a consulta é lenta.
O SQLLY também grava a fórmula na sua consulta como uma linha de comentário, no topo das instruções que rodaram:
-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;
Essa linha é como a coluna retorna. Execute a consulta amanhã, salve o .sql arquivo, envie para um colega: a coluna está lá todas as vezes. O banco de dados ignora o comentário, então a consulta ainda é executada em qualquer outro lugar. É uma única edição, então ⌘Z (CtrlZ no Windows e Linux) retira isso da consulta.


Uma vez criada, uma coluna calculada é uma coluna como qualquer outra. Classifique-a, filtre-a, dê um formato de número, pinte-a com formatação condicional, ou use-a em uma tabela dinâmica.
Ler e editar uma fórmula
- Clique em qualquer célula em uma coluna calculada e a barra de fórmulas mostra sua fórmula, somente leitura, com as mesmas cores e contornos. É a maneira mais rápida de verificar como um número foi calculado.
- Clique em Editar Fórmula nessa barra, ou clique com o botão direito no cabeçalho da coluna e escolha Editar fórmula…, para alterá-la. O botão agora diz Atualizar coluna.
- Aplicar uma edição substitui a linha da coluna na consulta, em vez de adicionar uma segunda, onde quer que ela esteja, inclusive dentro do bloco de trava. Renomeie a coluna no campo Nome e a linha é renomeada junto.


Fórmulas para roubar
| Coluna | Fórmula |
|---|---|
| Margem % | =IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue) |
| Total acumulado | =!Amount + N(!"Running Total"[1]) (nomeie a coluna Total acumulado) |
| Alteração em relação à linha anterior | =IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1]) |
| Crescimento % | =IFERROR(!Sales / !Sales[1] - 1, 0) |
| Indexado ao primeiro mês | =!Sales / !Sales[$1] |
| Total acumulado nos últimos 3 meses | =SUM(!Sales[2]:!Sales) |
| Dias até o envio | =!ShippedDate - !OrderDate |
| Vencimento no final do mês | =EOMONTH(!InvoiceDate, 1) |
| Dias de atraso | =MAX(0, TODAY() - !DueDate) |
| Etiqueta legível | =!Customer & ": " & TEXT(!Amount, "$#,##0.00") |
| Melhor contato | =COALESCE(!Email, !Phone, "none on file") |
| Nível | =IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze") |
Um total acumulado funciona porque uma coluna calculada pode ler seu próprio valor na linha anterior. Uma fórmula também pode ler qualquer coluna calculada adicionada antes dela, na mesma linha.
Onde difere de uma planilha
- A ordem das linhas vem da consulta.
[1]significa a linha que o banco de dados enviou antes desta. Para um total acumulado ou uma mudança do mês passado, termine a consulta com umORDER BYpara que as linhas cheguem na ordem que você quer. Ordenar a grade depois move os valores com suas linhas; não os recalcula. - Uma fórmula por coluna. Como uma coluna calculada em uma tabela do Excel, cada linha usa a mesma fórmula. Não há como sobrescrever uma única célula.
- Valores vazios são células vazias. A
NULLvindo do banco de dados conta como 0 na aritmética e como texto em branco, como faz uma célula vazia no Excel. Acima da primeira linha também fica vazio. - Os erros se parecem com os do Excel. Dividir por zero mostra
#DIV/0!nessa célula, uma função escrita incorretamente#NAME?, e assim por diante. Envolva a fórmula emIFERRORcomo você faria no Excel. - O tipo segue a fórmula. Aritmética de datas dá uma coluna de datas, comparações dão TRUE/FALSE, funções de texto dão texto. Se um
IFpode retornar um número ou"", a coluna se torna texto; retorne0em vez disso para mantê-la numérica. - Resultados muito grandes. Um resultado grande demais para caber na memória é lido do disco uma página de cada vez, e as fórmulas veem apenas as linhas de cada página. Um total acumulado então reinicia em cada página.
Mantenha junto à consulta
A fórmula já existe na consulta como uma -- sqlly column: linha. Se você também clicar no bloquear na frente do nome do resultado, o SQLly grava as colunas calculadas no bloco de trava junto com o resto da configuração da grade: ordem das colunas, larguras, formatos e cores.
Leve para o Excel
Exporte o resultado para o Excel, ou use Abrir na planilha, e cada coluna calculada chega como uma fórmula real do Excel. =!Qty * !Price na sétima linha da planilha se torna =B7*C7, !Qty[1] torna-se a linha acima, !Qty[$1] se torna B$2, e @rate torna-se um valor nomeado na planilha. Alterar um número no Excel faz a coluna recalcular. Os detalhes estão em Exportação para Excel.