計算欄位
功能性的新增一個用 Excel 公式、從結果自身欄位算出的欄位,具備 Excel 的資料編輯列、顏色與函式。
如果你會寫 Excel 公式,就能在 SQLly 中為任何查詢結果新增欄位。無論是利潤、累計總和、與上月相比的變化,還是兩個日期之間的工作天數:輸入你在 Excel 中會輸入的公式,新欄位就會出現在資料旁邊。你不需要會用 SQL 寫出來,也不需要重新執行查詢。
它的運作方式就像 Excel 資料表中的計算欄位:整欄使用同一個公式,每一列都會計算。唯一需要學的新東西是如何指向某個儲存格,因為查詢結果使用欄位名稱而不是字母。
新增你的第一個計算欄位
- 執行查詢。 任何結果都可以;例如含
Qty和Price欄位。 - 右鍵點擊標題 新欄位應該跟隨的欄位,然後選擇 新增計算欄位….
- 公式列會顯示在結果上方: fx、一個名稱欄位、
=,以及公式欄位。SQLly 會填入一個名稱(Computed),並用你點的欄位作為公式的開頭,例如=!Price. - 為欄位命名並輸入公式 - 例如 小計 和
=!Qty * !Price. - 按 Enter 鍵 或點擊 新增欄位. Esc鍵 或 取消 會關閉輸入列,不做任何變更。


從 A1 到 !Name
當 Excel 顯示 B7,SQLly 則顯示 !Qty:一個驚嘆號和欄位名稱。公式中的其他部分都是普通 Excel。
| 在 Excel 中(於第 7 列) | 在 SQLly 中 | 表示 |
|---|---|---|
=B7*C7 | =!Qty * !Price | 這一列的 Qty 乘以這一列的 Price。 |
=B7-B6 | =!Qty - !Qty[1] | 與前一列相比的變更。 [2] 是往上兩列。 |
=B8 | =!Qty[-1] | 下一列。 |
=B7/B$2 | =!Qty / !Qty[$1] | 與第一列相比,它在每一列都保持不動。 |
=SUM(B5:B7) | =SUM(!Qty[2]:!Qty) | 這一列以及前面的兩列。 |
=SUM(C7:E7) | =SUM(!Jan:!Mar) | 橫跨一月到三月的欄位。 |
=B7*TaxRate | =!Qty * @rate | 具名的值。 @rate 會讀取一個 -- sqlly set 或 -- sqlly param: 取自查詢的值。 |
把含空格的欄位名稱用雙引號括起來: !"Order Total",或 !"Order Total"[1] 代表前一列。名稱不區分大小寫,所以 !qty 也有效。
這些函式是 Excel 的,名稱、參數和結果都相同: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS 以及大約一百個其他函式。運算子也是如此,包括 & 用於連線文字和 %。 參考頁面 列出每個函式。多出來的那一個來自 SQL: COALESCE(a, b, …) 會回傳第一個不是空值的引數。
查看公式讀取了哪些內容
Excel 在你輸入公式時會為每個參照上色,並在儲存格周圍畫出對應的框。SQLly 也一樣:
- 每個參照都有專屬顏色 - 按 Excel 的順序:藍色、紅色、紫色、綠色、橙色,依此類推。同一個引用重複使用時維持同一個顏色。你輸入時,著色後的公式會顯示在欄位下方。
- 網格會為它讀取的儲存格加上外框 - 使用相同的顏色,並為那些欄位染上淡色。外框會畫在你選取的那一列,若沒有選取則畫在第一列,因此
!Qty[1]會明顯框出上一列的儲存格。 - 錯誤會在欄位下方逐項說明。 - 比如「有一個 ) 沒有對應的 (。」或者提示要將某欄寫成
!Name,而且 新增欄位 會等到公式讀起來正確為止。
套用它之後會發生什麼
該欄位會立即從螢幕上已經顯示的列計算出來。沒有任何資料傳送到資料庫,也沒有再執行任何操作,所以即使查詢很慢也會即時完成。
SQLly 也會把公式寫進你的查詢中,作為一行註解,放在已執行語句的最上方:
-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;
這一行就是欄位回來的方式。明天再執行查詢時,儲存 .sql 檔案,直接傳給同事:那個欄位每次都會在。資料庫會忽略註解,所以同一句查詢在別處照樣能執行。只要改一次,所以 ⌘Z (CtrlZ 在 Windows 和 Linux 上)會從訊息分頁回到結果,而且


一旦建好,計算欄位就跟其他欄位沒兩樣:可以排序、篩選、給它數字格式,也可以替它上色,作法是透過 條件格式,或將它用於一個 樞軸.
讀取與編輯公式
- 點擊計算欄中的任意儲存格 ,資料編輯列就會以唯讀方式顯示它的公式,顏色與外框都相同。這是查一個數字怎麼算出來的最快方法。
- 點擊編輯公式 在該列上,或者以右鍵點擊欄位標題並選擇 編輯公式…,即可更改。按鈕上現在寫的是 更新欄位.
- 套用編輯會取代該欄位在查詢中的那一行 在查詢中原地修改,而不是新增第二條,無論它在哪裡,包括鎖定區塊內也一樣。在「名稱」欄位重新命名欄位,那一行也會跟著改名。


值得借用的公式
| 欄位 | 公式 |
|---|---|
| 利潤率 % | =IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue) |
| 累計總計 | =!Amount + N(!"Running Total"[1]) (把欄位命名為 累計總計) |
| 與上一列的差異 | =IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1]) |
| 成長 % | =IFERROR(!Sales / !Sales[1] - 1, 0) |
| 以第一個月為基準 | =!Sales / !Sales[$1] |
| 滾動三個月總計 | =SUM(!Sales[2]:!Sales) |
| 出貨天數 | =!ShippedDate - !OrderDate |
| 月底到期 | =EOMONTH(!InvoiceDate, 1) |
| 逾期天數 | =MAX(0, TODAY() - !DueDate) |
| 清晰易讀的標籤 | =!Customer & ": " & TEXT(!Amount, "$#,##0.00") |
| 最佳聯絡人 | =COALESCE(!Email, !Phone, "none on file") |
| 層級 | =IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze") |
累計總和能運作,是因為計算欄位可以讀取前一列上它自己的值。公式也可以讀取同一列上在它之前加入的任何計算欄位。
它與試算表的不同之處
- 資料列順序由查詢決定。
[1]表示在此之前資料庫傳送的那一列。對於累計總和或與上個月的變動,請在查詢結尾加上ORDER BY這樣資料列就會依照你要的順序進來。之後對網格排序只是把值連同所屬資料列一起移動,並不會重新計算。 - 每欄只有一個公式。 就像 Excel 資料表中的計算欄,每一列使用相同的公式。無法覆寫單一儲存格。
- 空值即空儲存格。 A
NULL來自資料庫的空值在算術中算作 0,作為文字時視為空白,就像 Excel 的空儲存格一樣。第一列上方同樣是空的。 - 錯誤看起來像 Excel 的。 除以零會顯示
#DIV/0!在該儲存格中,函式拼寫錯誤#NAME?,依此類推。把公式包在IFERROR如同在 Excel 中一樣。 - 型別取決於公式。 日期運算產生日期欄位,比較會產生 TRUE/FALSE,文字函式會產生文字。如果
IF可以傳回數字或"",欄位就會變成文字;傳回0即可保持數值。 - 超大結果集。 結果太大放不進記憶體時,會一次一頁地從磁碟讀取,公式只看得到每一頁的資料列。因此累計總和每一頁都會從頭算起。
隨查詢一併保留
這個公式已經存在於查詢中,作為一個 -- sqlly column: 行。如果你也點擊 lock 在結果名稱前面時,SQLly 會把計算欄位與格線的其餘設定一起寫入鎖定區塊:欄位順序、寬度、格式與顏色。
匯出到 Excel
將結果匯出到 Excel,或使用 在試算表中開啟,每個計算欄位都會作為真正的 Excel 公式傳入。 =!Qty * !Price 工作表第 7 列上的會變成 =B7*C7, !Qty[1] 會變成上一列, !Qty[$1] 變成 B$2,而且 @rate 會變成活頁簿中的命名值。在 Excel 中更改一個數字,該欄會重新計算。詳細資訊在 Excel 匯出.