Berechnete Spalten

funktional

Füge eine Spalte hinzu, die mit einer Excel-Formel aus den Spalten des Ergebnisses selbst berechnet wird – mit Formelleiste, Farben und Funktionen wie in Excel.

Wenn du eine Excel-Formel schreiben kannst, kannst du jedem Abfrageergebnis in SQLly eine Spalte hinzufügen. Eine Marge, eine laufende Summe, die Veränderung zum Vormonat, die Anzahl der Arbeitstage zwischen zwei Daten: Tipp die Formel ein, die du auch in Excel tippen würdest, und die neue Spalte erscheint neben den Daten. Du musst nicht wissen, wie man sie in SQL schreibt, und die Abfrage nicht noch einmal ausführen.

Es funktioniert wie eine berechnete Spalte in einer Excel-Tabelle: eine Formel für die gesamte Spalte, berechnet für jede Zeile. Das Einzige, was neu zu lernen ist, ist, wie man auf eine Zelle verweist, da ein Ergebnis Spaltennamen anstelle von Buchstaben hat.

Füge deine erste berechnete Spalte hinzu

  1. Führe eine Abfrage aus. Jedes Ergebnis funktioniert; zum Beispiel eines mit Qty und Price Spalten.
  2. Klicke mit der rechten Maustaste auf die Kopfzeile der Spalte, der die neue folgen soll, und wähle Berechnete Spalte hinzufügen….
  3. Die Formelleiste öffnet sich über den Ergebnissen: fx, ein Namensfeld, =, und das Formelfeld. SQLly trägt einen Namen ein (Computed) und beginnt die Formel mit einem Bezug auf die Spalte, auf die du geklickt hast, zum Beispiel =!Price.
  4. Benenne die Spalte und tippe die Formel ein - zum Beispiel Zeilensumme und =!Qty * !Price.
  5. Drücke Eingabe oder klicke Spalte hinzufügen. Esc oder Abbrechen schließt die Leiste, ohne etwas zu ändern.
SQLly
Die Formelleiste über einem Ergebnis: jede Referenz in der Formel in ihrer eigenen Farbe, und die Zellen, die sie liest, im Raster in denselben Farben umrandet.Die Formelleiste über einem Ergebnis: jede Referenz in der Formel in ihrer eigenen Farbe, und die Zellen, die sie liest, im Raster in denselben Farben umrandet.
Die Formelleiste über einem Ergebnis: jede Referenz in der Formel in ihrer eigenen Farbe, und die Zellen, die sie liest, im Raster in denselben Farben umrandet.

Von A1 bis !Name

Wo Excel sagt B7, sagt SQLly !Qty: ein Ausrufezeichen und der Spaltenname. Alles andere in der Formel ist normales Excel.

In Excel (in Zeile 7)In SQLlyBedeutet
=B7*C7=!Qty * !PriceQty dieser Zeile mal Price dieser Zeile.
=B7-B6=!Qty - !Qty[1]Die Änderung gegenüber der vorherigen Zeile. [2] ist zwei Zeilen darüber.
=B8=!Qty[-1]Die Zeile danach.
=B7/B$2=!Qty / !Qty[$1]Im Vergleich zur ersten Zeile, die in jeder Zeile unverändert bleibt.
=SUM(B5:B7)=SUM(!Qty[2]:!Qty)Diese Zeile und die zwei davor.
=SUM(C7:E7)=SUM(!Jan:!Mar)Quer über die Spalten von Jan bis Mar.
=B7*TaxRate=!Qty * @rateEin benannter Wert. @rate liest einen -- sqlly set oder -- sqlly param: Wert aus der Abfrage.

Setze einen Spaltennamen mit Leerzeichen in doppelte Anführungszeichen: !"Order Total", oder !"Order Total"[1] für die vorherige Zeile. Namen ignorieren die Groß-/Kleinschreibung, daher !qty funktioniert ebenfalls.

Die Funktionen sind die von Excel, mit denselben Namen, Argumenten und Ergebnissen: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS und etwa hundert weitere. Gleiches gilt für die Operatoren, einschließlich & zum Verbinden von Text und %. Die Referenzseite listet jede Funktion auf. Eine zusätzliche stammt von SQL: COALESCE(a, b, …) gibt den ersten Wert zurück, der nicht leer ist.

Sehen, was die Formel liest.

Excel färbt jeden Zellbezug ein, während du eine Formel tippst, und zeichnet einen passenden Rahmen um die Zelle. SQLly macht dasselbe:

  • Jeder Verweis bekommt seine eigene Farbe - in Excels Reihenfolge: blau, rot, lila, grün, orange und so weiter. Eine Referenz, die du zweimal verwendest, bekommt dieselbe Farbe. Die farbige Formel siehst du beim Tippen unter dem Feld.
  • Das Raster umrahmt die Zellen, die es liest - in denselben Farben und tönt diese Spalten. Die Umrandung wird für die von dir gewählte Zeile gezogen, sonst für die erste, also !Qty[1] umrahmt sichtbar die Zelle eine Zeile darüber.
  • Fehler stehen unter dem Feld - wie „Es gibt eine ) ohne passende (.“ oder ein Hinweis, eine Spalte als !Name, und Spalte hinzufügen wartet, bis die Formel korrekt gelesen wird.

Was passiert, wenn du es anwendest

Die Spalte wird direkt aus den Zeilen berechnet, die bereits auf dem Bildschirm sind. Es geht nichts an die Datenbank und nichts läuft erneut, deshalb ist sie sofort da, selbst wenn die Abfrage langsam war.

SQLLY schreibt die Formel auch als Kommentarzeile in deine Abfrage, an den Anfang der ausgeführten Anweisungen:

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

So kommt die Spalte zurück. Führe die Abfrage morgen aus, speichere die .sql Datei, schicke sie an einen Kollegen: Die Spalte ist jedes Mal da. Die Datenbank ignoriert den Kommentar, also läuft die Abfrage überall sonst noch. Es ist eine einzige Änderung, also ⌘Z (StrgZ unter Windows und Linux) nimmt sie wieder aus der Abfrage heraus.

SQLly
Ein Ergebnis mit zwei berechneten Spalten, Line Total und Running Total, neben den Spalten, die sie lesen, und mit den sqlly-Spaltenzeilen oben in der Abfrage.Ein Ergebnis mit zwei berechneten Spalten, Line Total und Running Total, neben den Spalten, die sie lesen, und mit den sqlly-Spaltenzeilen oben in der Abfrage.
Ein Ergebnis mit zwei berechneten Spalten, Line Total und Running Total, neben den Spalten, die sie lesen, und mit den sqlly-Spaltenzeilen oben in der Abfrage.

Sobald sie da ist, ist eine berechnete Spalte eine Spalte wie jede andere. Sortiere sie, filtere sie, gib ihr ein Zahlenformat, färbe sie mit Bedingte Formatierung, oder benutze sie in einer Pivot-Tabelle.

Formel lesen und bearbeiten

  • Klicke auf eine beliebige Zelle in einer berechneten Spalte und die Formelleiste zeigt ihre Formel, schreibgeschützt, mit denselben Farben und Umrandungen. So siehst du am schnellsten, wie eine Zahl zustande gekommen ist.
  • Klicke auf Formel bearbeiten in dieser Leiste oder klicke mit der rechten Maustaste auf den Spaltenkopf und wähle Formel bearbeiten…, um sie zu ändern. Die Schaltfläche zeigt jetzt Spalte aktualisieren.
  • Das Anwenden einer Bearbeitung ersetzt die Zeile der Spalte in der Abfrage, anstatt eine zweite hinzuzufügen, wo auch immer sie liegt, auch innerhalb eines Sperrblocks. Benenne die Spalte im Namensfeld um, und die Zeile wird mit umbenannt.
SQLly
Eine ausgewählte Zelle in einer berechneten Spalte, mit ihrer Formel, die schreibgeschützt in der Formelleiste angezeigt wird, farbige Referenzen und die Schaltfläche Formel bearbeiten.Eine ausgewählte Zelle in einer berechneten Spalte, mit ihrer Formel, die schreibgeschützt in der Formelleiste angezeigt wird, farbige Referenzen und die Schaltfläche Formel bearbeiten.
Eine ausgewählte Zelle in einer berechneten Spalte, mit ihrer Formel, die schreibgeschützt in der Formelleiste angezeigt wird, farbige Referenzen und die Schaltfläche Formel bearbeiten.

Formeln zum Klauen

SpalteFormel
Marge %=IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue)
Laufende Summe=!Amount + N(!"Running Total"[1]) (benenne die Spalte Laufende Summe)
Änderung gegenüber der vorherigen Zeile=IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
Wachstum %=IFERROR(!Sales / !Sales[1] - 1, 0)
Indexiert auf den ersten Monat=!Sales / !Sales[$1]
Gleitender 3-Monats-Gesamtwert=SUM(!Sales[2]:!Sales)
Tage bis zum Versand=!ShippedDate - !OrderDate
Fällig am Monatsende=EOMONTH(!InvoiceDate, 1)
Tage überfällig=MAX(0, TODAY() - !DueDate)
Lesbares Label=!Customer & ": " & TEXT(!Amount, "$#,##0.00")
Bester Kontakt=COALESCE(!Email, !Phone, "none on file")
Stufe=IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

Eine laufende Summe funktioniert, weil eine berechnete Spalte ihren eigenen Wert aus der Zeile davor lesen kann. Eine Formel kann auch jede berechnete Spalte lesen, die vor ihr in derselben Zeile hinzugefügt wurde.

Wo es sich von einer Tabellenkalkulation unterscheidet

  • Die Reihenfolge der Zeilen stammt aus der Abfrage. [1] bedeutet die Zeile, die die Datenbank vor dieser gesendet hat. Für eine laufende Summe oder eine Änderung gegenüber dem Vormonat, beende die Abfrage mit einem ORDER BY damit die Zeilen in der beabsichtigten Reihenfolge ankommen. Das spätere Sortieren des Rasters verschiebt die Werte mit ihren Zeilen; es berechnet sie nicht neu.
  • Eine Formel pro Spalte. Wie eine berechnete Spalte in einer Excel-Tabelle nutzt jede Zeile dieselbe Formel. Eine einzelne Zelle lässt sich nicht überschreiben.
  • Leere Werte sind leere Zellen. A NULL aus der Datenbank zählt in der Arithmetik als 0 und als leerer Text, genauso wie eine leere Zelle in Excel. Über der ersten Zeile ist es ebenfalls leer.
  • Fehler sehen aus wie in Excel. Division durch Null zeigt #DIV/0! in dieser Zelle, eine falsch geschriebene Funktion #NAME?, und so weiter. Wickel die Formel in IFERROR wie du es in Excel tun würdest.
  • Der Typ folgt der Formel. Datumsarithmetik ergibt eine Datumsspalte, Vergleiche ergeben TRUE/FALSE, Textfunktionen ergeben Text. Wenn ein IF kann entweder eine Zahl oder "", wird die Spalte zu Text; gib 0 stattdessen zurück, um sie numerisch zu halten.
  • Sehr große Ergebnisse. Ein Ergebnis, das zu groß ist, um im Speicher zu bleiben, wird seitenweise von der Festplatte gelesen, und Formeln sehen nur die Zeilen jeder Seite. Eine laufende Summe beginnt dann mit jeder Seite von neuem.

Zusammen mit der Abfrage behalten

Die Formel ist bereits in der Abfrage als -- sqlly column: Zeile vorhanden. Wenn du auch auf das sperren vor dem Namen des Ergebnisses klicken, schreibt SQLly die berechneten Spalten zusammen mit dem Rest der Rastereinrichtung in den Sperrblock: Spaltenreihenfolge, Breiten, Formate und Farben.

Nach Excel übertragen

Exportiere das Ergebnis nach Excel oder benutze In Tabellenkalkulation öffnen, und jede berechnete Spalte erscheint als echte Excel-Formel. =!Qty * !Price in der siebten Zeilenreihe des Blattes wird =B7*C7, !Qty[1] wird die Zeile darüber, !Qty[$1] wird B$2, und @rate wird zu einem benannten Wert in der Arbeitsmappe. Ändere eine Zahl in Excel, und die Spalte rechnet sich neu. Die Details stehen auf Excel-Export.