Chapter 1. Spreadsheets 101 › 1.3 公式簡介
1.3 公式簡介
公式 (Formula) 和函式 / 函數 (Function) 是試算表強大最主要的原因。根據 Microsoft 的定義，公式是對工作表中的值執行計算的方程式，函式 / 函數 (Function) 是預先製作公式 (Formula)，可以單獨使用，或做為較長公式的一部分。例如 SUM、COUNTIF 是函式；=SUM(A:A)、=COUNTIF(A1:A5,1) 是公式。而本節將使用交易記錄檔、會員資料檔兩張工作表，用五個範例由淺入深說明如何使用公式完成一些基本的運算，讓各位對於如何在試算表中使用函式 / 公式有基本的概念：
- 交易記錄檔 K 欄：計算每列貨品交易的金額
公式 | K2 的公式為 =I2*J2。 |
解說 | 每一列的貨品總金額會等於數量 × 售價，此時可以在 K2 儲存格中輸入 =I2*J2、K3 輸入 =I3*J3，以此類推。而在試算表中你不用一列一列輸入，有以下三種方法可以直接在整欄輸入公式：
順帶一提，若公式會用到其他儲存格的資料，可以直接用鼠標點選要使用的儲存格，不一定要直接在公式裡輸入，尤其是跨工作表的儲存格會方便許多。而若要選取整列或整欄儲存格，可以直接點選左方的 1、2、3 及上方的 A、B、C 即可。 |
- 交易記錄檔 L 欄：計算每筆訂單所有貨品交易金額的總和
公式 | L2 的公式為 =SUMIF(A:A,A2,K:K)。 |
解說 | 每筆交易有獨特的交易編號，若不同列的交易編號相同則代表是同筆交易。 以 L2 為例，要加總交易記錄中所有與 A2 有相同交易編號的 K 欄金額總和，應使用「=SUMIF(A:A,A2,K:K)」，就會得到交易編號 4943 的所有品項金額總和為 473，即 K2:K8 的金額總和。完成 K2 後再將公式複製到每一列。 有關 SUMIF 的用法將會在第 6.6 節更詳細的說明，在此你可以想成「所有 A:A 的資料中若值等於 A2 的值，就加總 K:K 的值」。 |
- 會員資料檔 J 欄：計算每位會員的年齡
公式 | J2 的公式為 =DATEDIF(B2,TODAY(),"Y")。 |
解說 | 可以使用「會員生日」與「今天」的差距計算會員的年齡。 以 J2 為例，使用「=DATEDIF(B2,TODAY(),"Y")」計算，會輸出會員卡號 1771 的年齡。 這個函式是由 TODAY、DATEDIF 兩個函式組成，會在第 8 章有更詳細的說明，在此你可以想成「以年 "Y" 表示 B2 和今日 TODAY() 的差距，未滿一年的部分無條件捨去」。 |
- 會員資料檔 K 欄：將會員所屬的年齡區間，包括 30 歲以下、31~40 歲、41~50 歲、51~60 歲、61 歲以上
公式 | K2 的公式為 =IFS(J2<=30,"30 歲以下",J2<=40,"31~40 歲",J2<=50,"41~50 歲",J2<=60,"51~60 歲",J2>60,"61 歲以上")。 |
解說 | 在此根據 J 欄的值判斷會員的年齡位在哪個區間。 以 K2 為例，使用「=IFS(J2<=30,"30 歲以下",J2<=40,"31~40 歲", J2<=50,"41~50 歲",J2<=60,"51~60 歲",J2>60,"61 歲以上")」計算，可以得到會員卡號 1771 的年齡在「61 歲以上」。 有關 IFS 的用法會在第 6.4 節更詳細的說明，在此可以想成「如果 J2 在 30 歲以下，就輸出 "30 歲以下" ，不是 30 歲以下而是 40 歲以下的話，就輸出 "31~40 歲"...，以此類推」。 |
- 會員資料檔 L 欄：使用交易記錄檔計算該會員在 2022~2023 年之交易總金額。
公式 | L2 的公式為 =SUMIF('交易記錄檔'!B:B,A2,'交易記錄檔'!K:K)。 |
解說 | 在計算會員近兩年消費金額時，會用到交易記錄檔的資料，而試算表的公式可以跨表計算資料。在公式中使用 「'工作表名稱'!儲存格」表示。 以 L2 為例，可使用「=SUMIF('交易記錄檔'!B:B,A2,'交易記錄檔'!K:K)」計算會員卡號 1771 近兩年的消費總金額，在此可以想成「所有 '交易記錄檔'!B:B 欄的值如果等於 A2，就加總 '交易記錄檔'!K:K 欄的值」。 |
在上述五個範例中，我們可以知道公式中除了可以使用四則運算，也能使用現成的函式，甚至同時使用多個函式、用其他函式的輸出作為其他函式的輸入 (例如 C. 將 TODAY 的輸出作為 DATEDIF 的輸入)。上述五個小題完成之結果如下圖所示：
了解試算表中公式的使用方式後，下一節將介紹如何使用資料透視表 / 樞紐分析表彙整出會員輪廓與交易資料之分布趨勢。