Columnas calculadas

funcional

Añade una columna calculada con una fórmula de Excel a partir de las columnas del propio resultado, con la barra de fórmulas, los colores y las funciones de Excel.

Si sabes escribir una fórmula de Excel, puedes agregar una columna a cualquier resultado de consulta en SQLly. Un margen, un total acumulado, el cambio del mes pasado, los días laborables entre dos fechas: escribe la fórmula que escribirías en Excel y la nueva columna aparece junto a los datos. No hace falta saber escribirla en SQL, ni volver a ejecutar la consulta.

Funciona como una columna calculada en una tabla de Excel: una fórmula para toda la columna, que se calcula en cada fila. Lo único que hay que aprender es cómo referenciar una celda, porque un resultado tiene nombres de columna en lugar de letras.

Añade tu primera columna calculada

  1. Ejecuta una consulta. Cualquier resultado funciona; por ejemplo, uno con Qty y Price columnas.
  2. Haz clic derecho en el encabezado de la columna que la nueva debe seguir, y elige Añadir columna calculada….
  3. La barra de fórmulas se abre sobre los resultados: fx, un campo Nombre, =, y el campo de fórmula. SQLly rellena un nombre (Computed) y comienza la fórmula con una referencia a la columna en la que hiciste clic, como =!Price.
  4. Pon nombre a la columna y escribe la fórmula - por ejemplo Total de línea y =!Qty * !Price.
  5. Presiona Enter o haz clic en Añadir columna. Esc o Cancelar cierra la barra sin cambiar nada.
SQLly
La barra de fórmulas sobre un resultado: cada referencia de la fórmula en su propio color y las celdas que lee delimitadas en la cuadrícula con los mismos colores.La barra de fórmulas sobre un resultado: cada referencia de la fórmula en su propio color y las celdas que lee delimitadas en la cuadrícula con los mismos colores.
La barra de fórmulas sobre un resultado: cada referencia de la fórmula en su propio color y las celdas que lee delimitadas en la cuadrícula con los mismos colores.

De A1 a !Name

Donde Excel dice B7, SQLly dice !Qty: un signo de exclamación y el nombre de la columna. Todo lo demás en la fórmula es Excel normal.

En Excel (en la fila 7)En SQLlySignifica
=B7*C7=!Qty * !PriceLa Cantidad de esta fila por el Precio de esta fila.
=B7-B6=!Qty - !Qty[1]El cambio respecto a la fila anterior. [2] está dos filas más arriba.
=B8=!Qty[-1]La fila siguiente.
=B7/B$2=!Qty / !Qty[$1]En comparación con la primera fila, que se mantiene igual en todas las filas.
=SUM(B5:B7)=SUM(!Qty[2]:!Qty)Esta fila y las dos anteriores.
=SUM(C7:E7)=SUM(!Jan:!Mar)En las columnas de enero a marzo.
=B7*TaxRate=!Qty * @rateUn valor con nombre. @rate lee un -- sqlly set o -- sqlly param: valor de la consulta.

Pon un nombre de columna con espacios entre comillas dobles: !"Order Total", o !"Order Total"[1] para la fila anterior. Los nombres ignoran mayúsculas y minúsculas, así que !qty también funciona.

Las funciones son de Excel, con los mismos nombres, argumentos y respuestas: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS y aproximadamente cien más. Lo mismo ocurre con los operadores, incluyendo & para unir texto y %. El página de referencia enumera cada función. Una función extra proviene de SQL: COALESCE(a, b, …) da el primer valor que no está vacío.

Ve lo que la fórmula lee

Excel colorea cada referencia mientras escribes una fórmula y dibuja un recuadro del mismo color alrededor de la celda. SQLly hace lo mismo:

  • Cada referencia tiene su propio color - en el orden de Excel: azul, rojo, morado, verde, naranja y así sucesivamente. Una referencia que se usa dos veces conserva un solo color. La fórmula coloreada se muestra debajo del campo mientras escribes.
  • La cuadrícula marca con un contorno las celdas que lee - en los mismos colores, y tiñe esas columnas. Los contornos se dibujan para la fila que has seleccionado, o la primera fila si no hay ninguna seleccionada, por lo que !Qty[1] encuadra visiblemente la celda de la fila de arriba.
  • Los errores se detallan debajo del campo - como «Hay un ) sin su ( correspondiente.» o una sugerencia para escribir una columna como !Name, y Añadir columna espera hasta que la fórmula se lea correctamente.

Qué pasa cuando lo aplicas

La columna se calcula de inmediato con las filas que ya están en pantalla. Nada se envía a la base de datos y nada se ejecuta de nuevo, así que es instantáneo incluso cuando la consulta era lenta.

SQLly también escribe la fórmula en tu consulta como una línea de comentario, al inicio de las instrucciones que se ejecutaron:

-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;

Esa línea es la que hace que la columna vuelva. Ejecuta la consulta mañana, guarda el .sql archivo, envíalo a un colega: la columna estará siempre ahí. La base de datos ignora el comentario, así que la consulta sigue funcionando en cualquier otro sitio. Es una sola edición, así que ⌘Z (CtrlZ en Windows y Linux) lo vuelve a sacar de la consulta.

SQLly
Un resultado con dos columnas calculadas, Total de Línea y Total Acumulado, junto a las columnas de las que leen, y las líneas de columna sqlly en la parte superior de la consulta.Un resultado con dos columnas calculadas, Total de Línea y Total Acumulado, junto a las columnas de las que leen, y las líneas de columna sqlly en la parte superior de la consulta.
Un resultado con dos columnas calculadas, Total de Línea y Total Acumulado, junto a las columnas de las que leen, y las líneas de columna sqlly en la parte superior de la consulta.

Una vez ahí, una columna calculada es una columna como cualquier otra. Ordénala, fíltrala, dale un formato numérico, coloréala con formato condicional, o úsala en un pivotar.

Leer y editar una fórmula

  • Haz clic en cualquier celda de una columna calculada y la barra de fórmulas muestra su fórmula, en solo lectura, con los mismos colores y contornos. Es la manera más rápida de comprobar cómo se calculó un número.
  • Haz clic en Editar fórmula en esa barra, o haz clic derecho en el encabezado de la columna y elige Editar fórmula…, para modificarla. El botón ahora dice Actualizar columna.
  • Aplicar una edición reemplaza la línea de la columna en la consulta en lugar de añadir otra, esté donde esté, incluso dentro de un bloque de bloqueo. Cambia el nombre de la columna en el campo Nombre y la línea se renombra junto con ella.
SQLly
Una celda seleccionada en una columna calculada, con la fórmula mostrada en solo lectura en la barra de fórmulas, referencias coloreadas y el botón Editar Fórmula.Una celda seleccionada en una columna calculada, con la fórmula mostrada en solo lectura en la barra de fórmulas, referencias coloreadas y el botón Editar Fórmula.
Una celda seleccionada en una columna calculada, con la fórmula mostrada en solo lectura en la barra de fórmulas, referencias coloreadas y el botón Editar Fórmula.

Fórmulas que vale la pena copiar

ColumnaFórmula
% de margen=IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue)
Total acumulado=!Amount + N(!"Running Total"[1]) (nombre de la columna Total acumulado)
Cambio respecto a la fila anterior=IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
Crecimiento %=IFERROR(!Sales / !Sales[1] - 1, 0)
Indexado al primer mes=!Sales / !Sales[$1]
Total móvil de 3 meses=SUM(!Sales[2]:!Sales)
Días hasta el envío=!ShippedDate - !OrderDate
Vence a fin de mes=EOMONTH(!InvoiceDate, 1)
Días de retraso=MAX(0, TODAY() - !DueDate)
Etiqueta legible=!Customer & ": " & TEXT(!Amount, "$#,##0.00")
Mejor contacto=COALESCE(!Email, !Phone, "none on file")
Nivel=IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

Un total acumulado funciona porque una columna calculada puede leer su propio valor en la fila anterior. Una fórmula también puede leer cualquier columna calculada añadida antes que ella, en la misma fila.

Dónde se diferencia de una hoja de cálculo

  • El orden de las filas proviene de la consulta. [1] significa la fila que la base de datos envió antes que esta. Para un total acumulado o un cambio respecto al mes anterior, termina la consulta con un ORDER BY para que las filas lleguen en el orden que deseas. Ordenar la cuadrícula después mueve los valores con sus filas; no los recalcula.
  • Una fórmula por columna. Al igual que una columna calculada en una tabla de Excel, cada fila usa la misma fórmula. No se puede sobrescribir una sola celda.
  • Los valores vacíos son celdas vacías. A NULL de la base de datos cuenta como 0 en la aritmética y como texto en blanco, igual que una celda vacía en Excel. La zona sobre la primera fila también está vacía.
  • Los errores se ven como los de Excel. Dividir entre cero muestra #DIV/0! en esa celda, una función mal escrita #NAME?, y así sucesivamente. Envuelve la fórmula en IFERROR como lo harías en Excel.
  • El tipo lo dicta la fórmula. La aritmética de fechas da una columna de fechas, las comparaciones dan TRUE/FALSE, las funciones de texto dan texto. Si un IF puede devolver un número o "", la columna se convierte en texto; devolver 0 en su lugar, para que siga siendo numérico.
  • Resultados muy grandes. Un resultado demasiado grande para mantener en memoria se lee desde el disco una página a la vez, y las fórmulas solo ven las filas de cada página. Un total acumulado se reinicia con cada página.

Guárdalo con la consulta

La fórmula ya está en la consulta como una -- sqlly column: línea. Si también haces clic en el bloqueo delante del nombre del resultado, SQLly escribe las columnas calculadas en el bloque de bloqueo junto con el resto de la configuración de la cuadrícula: orden de las columnas, anchos, formatos y colores.

Llévalo a Excel

Exporta el resultado a Excel, o usa Abrir en hoja de cálculo, y cada columna calculada llega como una fórmula real de Excel. =!Qty * !Price en la séptima fila de la hoja se convierte en =B7*C7, !Qty[1] se convierte en la fila de arriba, !Qty[$1] se convierte en B$2, y @rate se convierte en un valor con nombre en el libro de trabajo. Cambia un número en Excel y la columna se recalcula. Los detalles están en Exportación a Excel.