Colonnes calculées

fonctionnel

Ajoutez une colonne calculée avec une formule Excel à partir des colonnes du résultat lui-même, en utilisant la barre de formule d'Excel, les couleurs et les fonctions.

Si vous savez écrire une formule Excel, vous pouvez ajouter une colonne à n'importe quel résultat de requête dans SQLly. Une marge, un total cumulé, la variation par rapport au mois dernier, le nombre de jours ouvrés entre deux dates : tapez la formule que vous écririez dans Excel, et la nouvelle colonne apparaît à côté des données. Inutile de savoir l'écrire en SQL, et inutile de relancer la requête.

Ça fonctionne comme une colonne calculée dans un tableau Excel : une formule pour toute la colonne, évaluée sur chaque ligne. La seule nouveauté à apprendre, c'est la façon de désigner une cellule, car un résultat a des noms de colonnes plutôt que des lettres.

Ajoutez votre première colonne calculée

  1. Exécutez une requête. Tout résultat fonctionne ; par exemple un avec Qty et Price colonnes.
  2. Cliquez avec le bouton droit sur l'en-tête de la colonne après laquelle la nouvelle doit apparaître, et choisissez Ajouter une colonne calculée….
  3. La barre de formule s'ouvre au-dessus des résultats : fx, un champ Nom, =, et le champ de formule. SQLly préremplit un nom (Computed) et commence la formule par une référence à la colonne sur laquelle vous avez cliqué, comme =!Price.
  4. Nommez la colonne et tapez la formule - par exemple Total de la ligne et =!Qty * !Price.
  5. Appuyez sur Entrée ou cliquez Ajouter une colonne. Échap ou Annuler ferme la barre sans rien changer.
SQLly
La barre de formule au-dessus d’un résultat : chaque référence dans la formule a sa propre couleur, et les cellules qu’elle lit sont encadrées dans la grille avec les mêmes couleurs.La barre de formule au-dessus d’un résultat : chaque référence dans la formule a sa propre couleur, et les cellules qu’elle lit sont encadrées dans la grille avec les mêmes couleurs.
La barre de formule au-dessus d’un résultat : chaque référence dans la formule a sa propre couleur, et les cellules qu’elle lit sont encadrées dans la grille avec les mêmes couleurs.

De A1 à !Name

Là où Excel dit B7, SQLly dit !Qty: un point d'exclamation et le nom de la colonne. Tout le reste de la formule est du Excel standard.

Dans Excel (à la ligne 7)Dans SQLlySignifie
=B7*C7=!Qty * !PriceLa quantité de cette ligne multipliée par le prix de cette ligne.
=B7-B6=!Qty - !Qty[1]Le changement par rapport à la ligne précédente. [2] est deux lignes au-dessus.
=B8=!Qty[-1]La ligne suivante.
=B7/B$2=!Qty / !Qty[$1]Comparé à la première ligne, qui reste en place sur chaque ligne.
=SUM(B5:B7)=SUM(!Qty[2]:!Qty)Cette ligne et les deux précédentes.
=SUM(C7:E7)=SUM(!Jan:!Mar)Sur les colonnes de janvier à mars.
=B7*TaxRate=!Qty * @rateUne valeur nommée. @rate lit une -- sqlly set ou -- sqlly param: valeur de la requête.

Mettez un nom de colonne avec des espaces entre guillemets : !"Order Total", ou !"Order Total"[1] pour la ligne précédente. Les noms ne tiennent pas compte de la casse, donc !qty fonctionne aussi.

Les fonctions sont celles d'Excel, avec les mêmes noms, arguments et réponses : IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS et environ une centaine d'autres. Il en va de même pour les opérateurs, y compris & pour joindre du texte et %. Le page de référence liste chaque fonction. Une de plus vient de SQL : COALESCE(a, b, …) renvoie la première valeur qui n'est pas vide.

Voir ce que la formule lit

Excel colore chaque référence pendant que vous tapez une formule et dessine une boîte correspondante autour de la cellule. SQLly fait de même :

  • Chaque référence reçoit sa propre couleur - dans l'ordre d'Excel : bleu, rouge, violet, vert, orange, et ainsi de suite. Une référence que vous utilisez deux fois garde une couleur. La formule colorée est affichée sous le champ pendant que vous tapez.
  • La grille encadre les cellules qu'elle lit - dans les mêmes couleurs, et teinte ces colonnes. Les contours sont tracés pour la ligne sélectionnée, ou la première ligne si aucune ne l'est, donc !Qty[1] encadre visiblement la cellule située une ligne plus haut.
  • Les erreurs sont indiquées sous le champ - telles que « Il y a un ) sans ( correspondant. » ou un indice pour écrire une colonne comme !Name, et Ajouter une colonne attend que la formule soit correctement interprétée.

Que se passe-t-il lorsque vous l'appliquez

La colonne est calculée immédiatement à partir des lignes déjà visibles à l'écran. Rien n'est envoyé à la base de données et rien ne s'exécute de nouveau, donc c'est instantané même lorsque la requête était lente.

SQLly écrit également la formule dans votre requête, en une ligne de commentaire, en tête des instructions exécutées :

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

C'est cette ligne qui fait revenir la colonne. Relancez la requête demain, enregistrez le .sql fichier, envoyez-le à un collègue : la colonne est là à chaque fois. La base de données ignore le commentaire, donc la requête fonctionne toujours ailleurs. C'est une seule modification, donc ⌘Z (CtrlZ sur Windows et Linux) la retire de la requête.

SQLly
Un résultat avec deux colonnes calculées, Total de ligne et Total cumulé, à côté des colonnes qu'elles lisent, et les lignes de colonne sqlly en haut de la requête.Un résultat avec deux colonnes calculées, Total de ligne et Total cumulé, à côté des colonnes qu'elles lisent, et les lignes de colonne sqlly en haut de la requête.
Un résultat avec deux colonnes calculées, Total de ligne et Total cumulé, à côté des colonnes qu'elles lisent, et les lignes de colonne sqlly en haut de la requête.

Une fois qu'elle est là, une colonne calculée est une colonne comme les autres. Triez-la, filtrez-la, donnez-lui un format numérique, colorez-la avec mise en forme conditionnelle, ou utilisez-la dans un pivot.

Lire et modifier une formule

  • Cliquez sur n'importe quelle cellule dans une colonne calculée et la barre de formule affiche sa formule, en lecture seule, avec les mêmes couleurs et contours. C'est le moyen le plus rapide de vérifier comment un nombre a été calculé.
  • Cliquez sur Modifier la formule dans cette barre, ou faites un clic droit sur l'en-tête de la colonne et choisissez Modifier la formule…, pour la modifier. Le bouton indique maintenant Mettre à jour la colonne.
  • Appliquer une modification remplace la ligne de la colonne dans la requête plutôt que d'en ajouter une seconde, où qu'elle se trouve, y compris à l'intérieur d'un bloc verrouillé. Renommez la colonne dans le champ Nom, et la ligne est renommée du même coup.
SQLly
Une cellule sélectionnée dans une colonne calculée, avec la formule affichée en lecture seule dans la barre de formule, les références colorées et le bouton Modifier la formule.Une cellule sélectionnée dans une colonne calculée, avec la formule affichée en lecture seule dans la barre de formule, les références colorées et le bouton Modifier la formule.
Une cellule sélectionnée dans une colonne calculée, avec la formule affichée en lecture seule dans la barre de formule, les références colorées et le bouton Modifier la formule.

Des formules à s'approprier

ColonneFormule
Marge %=IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue)
Total cumulatif=!Amount + N(!"Running Total"[1]) (nommer la colonne Total cumulatif)
Changement par rapport à la ligne précédente=IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
Croissance %=IFERROR(!Sales / !Sales[1] - 1, 0)
Indexé sur le premier mois=!Sales / !Sales[$1]
Total glissant sur 3 mois=SUM(!Sales[2]:!Sales)
Jours pour expédier=!ShippedDate - !OrderDate
Échéance à la fin du mois=EOMONTH(!InvoiceDate, 1)
Jours de retard=MAX(0, TODAY() - !DueDate)
Étiquette lisible=!Customer & ": " & TEXT(!Amount, "$#,##0.00")
Meilleur contact=COALESCE(!Email, !Phone, "none on file")
Niveau=IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

Un total cumulé fonctionne parce qu'une colonne calculée peut lire sa propre valeur sur la ligne précédente. Une formule peut également lire toute colonne calculée ajoutée avant elle, sur la même ligne.

Où cela diffère d'une feuille de calcul

  • L'ordre des lignes provient de la requête. [1] signifie la ligne que la base de données a envoyée avant celle-ci. Pour un total cumulatif ou une variation par rapport au mois dernier, terminez la requête par un ORDER BY pour que les lignes arrivent dans l'ordre voulu. Trier ensuite la grille déplace les valeurs avec leurs lignes ; cela ne les recalcule pas.
  • Une formule par colonne. Comme une colonne calculée dans un tableau Excel, chaque ligne utilise la même formule. Il est impossible de remplacer la formule d'une seule cellule.
  • Les valeurs vides sont des cellules vides. A NULL de la base de données compte pour 0 dans les calculs et comme texte vide, comme une cellule vide dans Excel. Au-dessus de la première ligne, c'est vide aussi.
  • Les erreurs ressemblent à celles d'Excel. Diviser par zéro affiche #DIV/0! dans cette cellule, une fonction mal orthographiée #NAME?, et ainsi de suite. Enveloppez la formule dans IFERROR comme vous le feriez dans Excel.
  • Le type suit la formule. L'arithmétique des dates donne une colonne de dates, les comparaisons donnent TRUE/FALSE, les fonctions de texte donnent du texte. Si un IF peut renvoyer soit un nombre soit "", la colonne devient du texte ; renvoyez 0 à la place pour le garder numérique.
  • Les résultats très volumineux. Un résultat trop volumineux pour être conservé en mémoire est lu depuis le disque page par page, et les formules ne voient que les lignes de chaque page. Un total progressif recommence alors à chaque page.

Conservez-le avec la requête

La formule existe déjà dans la requête en tant que -- sqlly column: ligne. Si vous cliquez également sur le verrouiller devant le nom du résultat, SQLly écrit les colonnes calculées dans le bloc verrouillé avec le reste de la configuration de la grille : ordre des colonnes, largeurs, formats et couleurs.

Emporter vers Excel

Exportez le résultat vers Excel, ou utilisez Ouvrir dans un tableur, et chaque colonne calculée arrive sous forme de formule Excel réelle. =!Qty * !Price sur la septième ligne de la feuille devient =B7*C7, !Qty[1] devient la ligne au-dessus, !Qty[$1] devient B$2, et @rate devient une valeur nommée dans le classeur. Modifiez un nombre dans Excel et la colonne se recalcule. Les détails sont sur Export Excel.