將Calc做為簡易資料庫

Calc 使用手冊 第九章將 Calc 做為簡易資料庫 適用於一般使用者

內容目錄 序言 Introduction... 1 為範圍插入名稱... 2 範圍名稱... 2 資料庫區域... 3 排序... 4 篩選... 5 自動篩選... 5 標準篩選... 7 進階篩選... 7 操作篩選結果... 8 Calc 資料庫相關函數... 8 符合條件時計數及加總儲存格資料 :COUNTIF 及 SUMIF... 10 使用 SUBTOTAL 函數忽略篩選後所隱藏之資料... 11 使用函式尋找資料... 11 使用 VLOOKUP 函數搜尋資料區域... 11 使用 HLOOKUP 函數搜尋資料區域... 12 使用 LOOKUP 函數搜尋欄或列... 12 使用 MATCH 函數尋找範圍中一個值的位置... 12 範例... 13 使用 ADDRESS 函數回傳文字型態的儲存格位置... 13 使用 INDIRECT 將字串內容轉換為儲存格或範圍的值... 14 使用 OFFSET 回傳指定範圍的儲存格或範圍... 14 利用 INDEX 函數將儲存格傳回指定範圍... 15 特殊資料庫函數... 15 結語... 16 i

序言 Introduction Calc 文件做為資料庫已可滿足許多使用者需求, 本章將介紹如何將 Calc 文件做為資料庫 資料庫中, 相關數據被記錄為一個群組項目, 每個群組作為單元訊息被視為一區域 將資訊紀錄於表中, 每張表皆有相同結構, 可視為欄與列組成 列相當於單獨的紀錄, 而欄相當於區域 Calc 試算表文件之結構與資料庫類似, 每個儲存格就像資料庫的區域, 在沒有特殊需求的情況下,Calc 可滿足資料庫之基本功能 以下列試算表為例, 每列代表一位學生之資料, 而欄位資料有作業 考試 測驗 ( 如表 1 所示 ); 藉由這些數據, 可展現試算表強大的計算功能 表 1: 學生成績表 A B C D E F G 1 Name Test 1 Test 2 Quiz 1 Quiz 2 Average Grade 2 Andy 95 93 93 92 93.25 3 Betty 87 92 65 73 79.25 4 Bob 95 93 93 92 93.25 5 Brandy 45 65 92 85 71.75 6 Frank 95 93 85 92 91.25 7 Fred 87 92 65 73 79.25 8 Ilsub 70 85 97 79 82.75 9 James 45 65 97 85 73 10 Lisa 100 97 100 93 97.5 11 Michelle 100 97 100 65 90.5 12 Ravi 87 92 86 93 89.5 13 Sal 45 65 100 92 75.5 14 Ted 100 97 100 85 95.5 15 Tom 70 85 93 65 78.25 16 Whil 70 85 93 97 86.25 1 序言 Introduction

為範圍插入名稱 Calc 文件中, 範圍的定義為一個以上之連續儲存格 除了定義範圍名稱, 亦可將範圍定義為資料庫區域, 使其範圍有類似資料庫之功能 將範圍定義名稱有下列三種原因 : 1) 將範圍定義名稱可增加其可讀性 2) 當許多不同地方參照同一個範圍名稱時, 可藉由修改範圍名稱指定之範圍, 將所有參照範圍一次指向新的範圍 3) 由工具列 助手 或是按鍵盤 F5, 可快速找到定義過名稱之範圍 範圍名稱 將儲存格範圍以有意義的名稱命名, 為函數運算需要參照範圍時最常使用 例如將一範圍命名為 Scores, 即可使用 =SUM(Scores). 函式求出範圍內總和 由功能表 插入 名稱 定義 可開啟定義名稱對話框以增加或修改範圍 ( 如圖 1) 圖 1: 為範圍定義名稱 選取含有標題列及內容之範圍後, 由功能表 插入 名稱 建立 開啟產生名稱對話框 ( 如圖 2), 可依照上方列 左欄 下方列 右欄之欄位名稱同時定義多個資料範圍 範圍資料不包含標題列, 而是以標題列為範圍命名 圖 2: 為範圍建立名稱 為範圍插入名稱 2

資料庫區域 將試算表中之儲存格範圍命名後, 即為資料庫區域, 可將此區域當作資料庫使用 範圍中每列對應一記錄, 每個儲存格對應一個區域 針對資料庫內容進行排序 群組 搜尋與計算 資料庫區域可執行與資料庫相關之動作 例如將第一列設為標題 由功能表 資料 定義範圍 可開啟定義資料庫區域對話框 ( 如圖 3), 以建立 修改或刪除資料庫區域 第一次定義區域時, 修改按鈕會顯示為增加 圖 3: 定義資料庫區域 3 為範圍插入名稱

排序 透過 Calc 文件中的排序機制, 可重新排列工作表內容 第一步驟為選取欲排序之範圍, 例如表 1 中的 A1 到 G16 儲存格 再由功能表 資料 排序 開啟排序對話框 ( 如圖 4), 最多可設定三樣排序條件 圖 4: 排序欄位名稱為 Nmae 的資料 若排序時範圍須包含欄標籤, 可由排序對話框選項標籤 ( 如圖 5) 中勾選範圍含有欄標籤核取方塊以防止欄標題與其他數據一同進行排序 以表 1 為例, 若如圖 5 有勾選範圍含有欄標籤核取方塊, 則會如圖 4 有欄標籤可作為排序依據 ; 若沒勾選範圍含有欄標籤核取方塊, 則會顯示欄標題, 如欄 A 一般排序後的結果會覆蓋顯示在現有數據上, 因此可勾選複製排序結果到核取方塊, 將排序結果複製到指定的位置, 以保留原本的數據 欲顯視篩選結果的位置可接鍵入 ( 例如工作表 3.A1), 或是選擇一已定義之位置 勾選使用者自訂排序規則核取方塊可選取預先定義的條件, 由功能表 工具 選項 OpenOffice.org Calc 排序清單 可依照自訂規則鍵入清單之中 定義排序清單常用於數據不按照字母或數字排序時, 例如一週的第一天欲由星期一開始 排序 4

篩選 圖 5: 設定排序選項 透過篩選可限制試算表中可見的列, 一般常用的篩選除了自動篩選外, 還可自訂篩選條件 注意 篩選後, 除了符合篩選條件的列資料, 其餘資料皆會隱藏 一次選取多列作為篩選範圍時, 有可能會選到隱藏的列 ; 若欲避免此情形, 必須定義多個篩選範圍個別篩選 自動篩選 啟動自動篩選後, 由下拉式選單中可使用多種篩選功能 : 全部選項可使篩選範圍中所有列資料皆顯示 標準篩選選項將開啟與標準篩選一樣之對話框 前 10 個選項即顯示前 10 大的數值, 若數值 70 有在前 10 大內, 資料範圍中有多個數值 70 時, 則會一同被篩選出 ; 此時即會篩選出超過 10 筆資料 其餘篩選項目即資料範圍中每列的值 ( 重複的不顯示 ) 5 篩選

建立自動篩選首先需要選擇欄位, 例如選取表 1 資料中欲篩選的 B C 兩欄, 在沒選擇標題列時,Calc 會詢問是否以範圍中的第一列作為列標題 ; 雖然列標題可設定於任何一列, 但僅能篩選列標題之下的內容 由功能表 資料 篩選 自動篩選 建立自動篩選, 由下拉式選單選擇篩選條件 ( 如圖 6) 圖 6: 使用自動篩選 欲移除自動篩選則重複建立自動篩選之步驟即可 換句話說, 功能表自動篩選之功能鈕可當作自動篩選之開關 ; 自動篩選關閉時, 標題列上的下拉式選單則會消失 篩選 6

標準篩選 由功能表 資料 篩選 標準篩選 開啟標準篩選對話框 ( 如圖 7), 最多可設定 8 項篩選條件 若欲關閉篩選可由功能表 資料 篩選 刪除篩選 關閉 進階篩選 圖 7: 使用標準篩選 進階篩選最多接受 8 項篩選條件, 相較於標準篩選, 進階篩選必須先將篩選條件鍵入試算表的儲存格中 1) 於 Calc 文件中找一空白範圍, 此範圍可於同文件中任何工作表上的任何位置 2) 複製欲篩選範圍的標題列至上述的範圍中 3) 鍵入篩選條件於上述標題列之下之對應位置 ( 如表 2) 篩選條件範圍同列中的每個欄位之依存關係為和, 而烈與列之間的關係為或 表 2: 進階篩選條件範例 Name Test 1 Test 2 Quiz 1 Quiz 2 Average Grade ="Andy" >80 <80 7 篩選

備註 範圍定義名稱後 ( 如圖 1), 可作為進階篩選的篩選條件及篩選結果存放位置 ; 被定義過名稱之範圍可於進階篩選對話框下拉式選單中看到 ( 如圖 8) 在設定一個或多個篩選條件後, 依下述步驟啟動進階篩選 : 1) 選擇欲篩選之工作表範圍 2) 由功能表 資料 篩選 進階篩選 開啟進階篩選對話框( 如圖 8) 3) 選擇篩選條件範圍及其他相關選項 4) 按確定 操作篩選結果 欲將篩選結果複製到新位置後可作選取 編輯或刪除 ; 若列資料因為不符合篩選條件而被隱藏, 複製篩選結果時將不被複製到 OpenOffice.org 操作原則取決於儲存格是透過何種方式被隱藏的 儲存格於群組與大綱 篩選或隱入時將被隱藏, 當有隱藏資料之範圍被拖曳或剪下貼上時, 所有的儲存格將會移動 ( 包含隱藏的儲存格 ); 而複製資料時, 篩選結果的可見儲存格 透過群組與大綱或隱入所隱藏的儲存格皆會被複製到 Calc 資料庫相關函數 表 3 列出 Calc 中較常使用之資料庫相關函數, 而函數若只有名稱末端一字母之差, 如 AVERAGE 及 AVERAGEA 函數名稱末端若有一個字母 A, 運算時會將文字資料視為數值 0, 忽略空儲存格 ; 而函數名稱末端沒有字母 A, 則僅對數字格式之資料做運算, 會忽略文字資料及空儲存格 表 3: 常使用之資料庫函數 函數 AVERAGE 描述 傳回引數的平均值 AVERAGEA 傳回引數的平均值 文字的值為 0 COUNT 圖 8: 預先定義過的範圍作為進階篩選條件 計算引數清單中有多少個值 忽略文字項目 Calc 資料庫相關函數 8

函數 COUNTA COUNTBLANK COUNTIF HLOOKUP INDEX INDIRECT LOOKUP MATCH MAX MAXA MIN 描述 計算引數清單中數值的個數 也會計算文字項目, 即使它們包含的是長度為 0 的空字串 如果引數是一個陣列或參照, 則會略過該陣列或參照內的空儲存格 傳回空的儲存格數 傳回某一儲存格範圍中符合一定條件的儲存格數 在選取的區域以下搜尋值與儲存格的參照 此函數驗證陣列的第一列是否包含特定值 INDEX 傳回子範圍, 此子範圍由列號與欄號或選用的範圍索引所指定 根據環境之不同,INDEX 會傳回參照或內容 傳回文字字串指定的參照 此函數也可用於傳回對應字串的區域 傳回單列範圍或單欄範圍的儲存格內容 可選擇以不同的欄與列傳回 ( 相同索引的 ) 指定值 傳回陣列中符合指定值的項目相關位置 傳回引數清單中的最大值 傳回引數清單中的最大值 相對於最大值, 您可以在此處輸入文字 文字的值為 0 傳回引數清單中的最小值 MINA 傳回引數清單中的最小值 您也可以在此處輸入文字 文字的值為 0 MEDIAN MODE OFFSET PRODUCT STDEV 傳回一組數字的中位數 在包含奇數值的組中, 中位數將是該組數中間的數字, 而在包含偶數值的組中, 中位數將是組數中間兩個值的平均值 傳回資料集中的最大共用值 如果有好幾個值具有相同頻率, 則會傳回最小值 值不出現兩次時發生錯誤 依指定參照點特定的列與欄數傳回儲存格偏移的值 將作為引數指定的所有數字相乘並傳回乘積 根據範例估計標準離差 STDEVA 根據範例計算估計的標準離差 文字的值為 0 STDEVP STDEVPA SUBTOTAL SUM SUMIF VAR 根據總體基數計算標準離差 根據總體基數計算標準離差 計算小計 如果一個範圍已包含小計, 則其不能用於進一步計算 將此函數和 [ 自動篩選 ] 同時使用, 以僅對篩選出的資料條目進行計算 將某一儲存格範圍中的所有數字相加 增加給定條件所指定的儲存格 當您搜尋特定值時, 此函數用於瀏覽區域 根據範例估計變異數 VARA 根據範例估計變異數 文字的值為 0 9 Calc 資料庫相關函數

函數 VARP 描述 根據總體基數計算變異數 VARPA 根據總體基數計算變異數 文字的值為 0 VLOOKUP 將 Calc 做為簡易資料庫 參照右方相鄰的儲存格之垂直搜尋 此函數會檢查陣列的第一欄是否包含特定值 接著, 此函數會傳回 Index 指定欄之同一列中的值 符合條件時計數及加總儲存格資料 :COUNTIF 及 SUMIF COUNTIF 及 SUMIF 函數於符合指定條件時進行運算, 條件可為數字 表示式 文字串, 甚至正規表示式 搜尋條件可透過參照儲存格內容或是直接鍵入 COUNTIF 函數將傳回某一儲存格範圍中符合一定條件的儲存格數 其第一個參數為搜尋範圍, 第二個參數為搜尋條件 表 7 為利用 COUNTIF 配合不同搜尋條件計數表 1 所得到的結果 SUMIF 的前兩個參數與 COUNTIF 相同, 第三個參數為欲作為加總數值的範圍 ; 此範圍與搜尋範圍是相對應的, 與符合搜尋條件之儲存格相對應的值被加總 表 4:COUNTIF 及 SUMIF 函數搜尋條件範例 標準類型函數結果描述 數字 =COUNTIF(B1:C16; 95) 3 搜尋並傳回數值為 95 的儲存格 個數 文字 =COUNTIF(B1:C16; "95") 3 搜尋並傳回數值或文字為 95 的 儲存格個數 表示式 =COUNTIF(B1:C16; ">95") 6 搜尋並傳回數值大於 95 的儲存 格個數 表示式 =COUNTIF(B1:C16; 2*45+5) 3 搜尋並傳回數值為 95 的儲存格 個數 正規表示式 =COUNTIF(B1:C16; "9.*") 12 搜尋並傳回以 9 為開頭文字的 儲存格個數 參照儲存格 =COUNTIF(B1:C16; B3) 3 搜尋並傳回與 B3 相同內容的 儲存格個數 正規表示式 =SUMIF(A1:A16; "C.*"; B1:B16) 227 加總 A 欄中以 C 為開頭文字之 相對應儲存格 (A 欄對應 B 欄 ) Calc 資料庫相關函數 10

使用 SUBTOTAL 函數忽略篩選後所隱藏之資料 表 5 為 SUBTOTAL 函數中所使用之功能對照表 篩選範圍資料後所隱藏的資料將不納入 SUBTOTAL 函數的計算範圍 例如 =SUBTOTAL(2, "B2:B16") 為計數 B2 到 B16 中篩選後而沒隱藏的儲存格個數 表 5:SUBTOTAL 函數之功能索引 Function index Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP 使用函式尋找資料 Calc 提供多種搜尋工作表中資料之方法, 例如由功能表 編輯 尋找與取代, 或由功能表 資料 篩選, 亦可透過函數 lookup 找出相對應資料 例如利用 lookup 找出學生測驗分數所對應的級分 使用 VLOOKUP 函數搜尋資料區域 使用 VLOOKUP 函數搜尋第一欄 ( 欄為垂直方向 ) 的資料, 並傳回其他欄中, 同列的相對應資料 例如搜尋表 1 A 欄 (name) 中的 Fred, 傳回與 Fred 同列, 往右側兩欄 (test2) 相對應的資料 VLOOKUP 支援兩種格式 : VLOOKUP(search_value; search_range; return_column_index) VLOOKUP(search_value; search_range; return_column_index; sort_order) 第一個參數 search_value 是指欲在陣列第一欄中搜尋的值 其搜尋的值可為數字 文字或正規表示式 例如 4 為數字,Fred 為文字,F.* 為找出以 F 開頭的字串之正規表示式 第二個參數 search_range 為搜尋及回傳值的範圍 ( 至少包含兩欄 ) 請注意搜尋僅限於範圍中的第一欄 第三個參數 return_column_index 為欲回傳對應值的欄位, 填 1 則是回傳範圍中第 1 欄 公式 =VLOOKUP("Bob"; A1:G9; 1) 是於範圍 A1:G9 第一欄中找出內容為 Bob 的儲存格, 回傳相對應的值 ; 此公式將回傳 Bob 若參數 return_column_index 為 2, 則會回傳與 Bob 同列且位於範圍第二欄 (B 欄 ) 的值 最後一個參數 sort_order 可選擇是否要填入, 其預設值為 1, 指出陣列中的第一欄是否向上排序 若第一欄未向上排序, 請輸入布林值 FALSE 或零 搜尋排序過後的欄可更快速, 且即使搜尋值未完全符合, 只要該值介於排序清單的最低值與最高值之間, 函數仍會一律傳回值 在未排序的清單中, 搜尋值必須完全符合 否則函數會傳回訊息 :#N/A 11 Calc 資料庫相關函數

搜尋值需完全相符時, 搜尋升序排序的資料範圍, 將比無排序更有效率, 但回傳的值是相同的 搜尋值若不須完全相符, 且資料範圍中無相符資料, 會回傳同欄中小於搜尋值的第一個值 例如欲在有 3 5 10 三個數值的資料範圍中搜尋 7, 會回傳 5; 搜尋 27, 回傳 10; 若搜尋 2, 則會回傳 #N/A, 因為範圍中已無小於 2 的值 使用 VLOOKUP 時機 : 欲回傳不同欄中與搜尋值同列的資料時 例如表 1 中搜尋學生姓名 (name), 回傳其測驗分數 (test 及 quiz 欄資料 ), 請注意回傳值的欄須於搜尋值的欄的右側 搜尋範圍中第一欄的資料 使用 HLOOKUP 函數搜尋資料區域 使用 HLOOKUP 函數搜尋第一列 ( 列為水平方向 ) 的資料, 並傳回其他列中, 同欄的資料 HLOOKUP 所使用的格式與 VLOOKUP 相同 : HLOOKUP(search_value; search_range; return_row_index) HLOOKUP(search_value; search_range; return_row_index; sort_order) 使用 HLOOKUP 時機 : 欲回傳不同列中與搜尋值同欄的資料時 例如表 1 中搜尋欄位名稱 Test2( 欄 C), 第 6 位同學的得分 搜尋範圍中第一列的資料 使用 LOOKUP 函數搜尋欄或列 LOOKUP 功能與 HLOOKUP 及 VLOOKUP 類似 LOOKUP 函數之搜尋範圍僅有單欄或單列 其格式有以下兩種 : LOOKUP(search_value; search_range) LOOKUP(search_value; search_range; return_range) 參數 search value 與 HLOOKUP 及 VLOOKUP 相同, 請注意其中的值必須以遞增順序排列 :...,-2, -1, 0, 1, 2,..., A-Z, FALSE, TRUE; 否則,LOOKUP 函數可能不會傳回正確的值 而參數 search range 必須為單欄或單列 例如 A7:A12 ( 僅於欄 A) 或 C5:Q5 ( 僅於列五 5) 參數 return_range 若是省略, 則傳回 search range 中與 search value 相對應的值 使用 LOOKUP 無須設定回傳值的範圍, 就如同使用 HLOOKUP 及 VLOOKUP 參數 return_column_index 填 1 回傳值的範圍須與搜尋的範圍等大, 而兩範圍可不同方向 例如搜尋範圍為一列, 回傳值的範圍為一欄 使用 LOOKUP 時機 : 搜尋範圍中的值為遞增排序時 回傳值範圍與搜尋範圍不同方向 不在同一欄列中時 使用 MATCH 函數尋找範圍中一個值的位置 使用 MATCH 函數尋找單獨欄或列中指定的值, 並回傳其位置 ( 數字 ) MATCH 函數支援的格式如下 : =MATCH(search_value; search_range) =MATCH(search_value; search_range; search_type) 其搜尋值與搜尋範圍之設定與 LOOKUP 相同 ; 最後一個參數 search type 可以是數值 1 0 或 -1 搜尋範圍以升冪排序時, 參數 search_type 填 1; 以降冪排序時, 參數 search_type 填 -1 Calc 資料庫相關函數 12

使用 MATCH 時機 : 範例 需要範圍中搜尋值的參照位置, 而不是其內容時 搜尋資料以降冪排序時 表 1 中每位學生的資料放在個別的列中, 在此透過公式傳回學生 Fred 的平均分數 ( 級分 ), 其動作流程為搜尋 A1:G16 範圍的第一欄 (A 欄 ), 找出 Fred 於哪一列, 並回傳 F 欄中與 Fred 同列的資料 可使用公式 =VLOOKUP("Fred"; A2:G16; 6), 亦可使用公式 =LOOKUP("Fred"; A2:A16; F2:F16). 一般資料範圍中第一列為標題列, 所有搜尋動作皆會檢查第一列是否為標題, 若為標題列則忽略 若欲回傳平均值欄 (Average) 的數值, 但不知是第幾欄時, 可使用 MATCH 函數傳回欄的位置 ( 數字 ) 其公式為 =VLOOKUP("Fred"; A2:G16; MATCH("Average"; A1:G1; 0)), 請注意搜尋標題列是不需排序的, 因此 MATCH 第三個參數使用 0 可練習用 HLOOKUP 找出平均值 (Average), 配合 MATCH 函數所回傳的 Fred 位置 ( 數字 ) 最後一個範例是透過公式為學生平均分數打級分 ; 平均低於 51 為 F 級,51 到 60 為 E 級,61 到 70 為 D 級,71 到 80 為 C 級,81 到 90 為 B 級,91 到 100 為 A 級 其對應關關係如表 6, 可將其放在工作表 2 中 表 6: 成績對照級分 A 1 Score Grade 2 0 F 3 51 E 4 61 D 5 71 C 6 81 B 7 91 A B 公式 =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) 中使用錢號將參照範圍定為絕對位置, 因此公式可隨意複製貼至不同位置, 而不是相對位置影響 使用 ADDRESS 函數回傳文字型態的儲存格位置 使用 ADDRESS 函數回傳包含欄 列 工作表名稱之儲存格位置 ADDRESS 函數常與 MATCH 函數搭配使用, 其格式如下 : ADDRESS(row; column) ADDRESS(row; column; abs) ADDRESS(row; column; abs; sheet) ADDRESS(1; 1) 位址解譯可為絕對位址 ( 如 $A$1) 相對位址 ( 如 A1) 或是混合格式 (A$1 或 $A1) 參數 row 為儲存格參照的列號, 參數 column 為儲存格參照的欄號,abs 參數決定參照的類型 ( 如表 7 所示 ) sheet 參數表示工作表名稱, 被視為字串, 必須在前後加上雙引號 公式 ADDRESS(MATCH("Bob";A1:A5 ; 0); 2) 參照表 1 中資料將回傳 $B$2 13 Calc 資料庫相關函數

表 7:ADDRESS 函數之 abs 參數值描述 1 絕對位置參照, 此數值為 abs 參數之預設值 公式 ADDRESS(2; 5; 1) 將得到 $E$2 2 列絕對參照 公式 ADDRESS(2; 5; 2; "Sheet2") 將得到 Sheet2.E$2 3 欄絕對參照 公式 ADDRESS(2; 5; 3) 將得到 $E2 4 相對位置參照 公式 ADDRESS(2; 5; 4) 將得到 E2 使用 INDIRECT 將字串內容轉換為儲存格或範圍的值 INDIRECT 函數可傳回一文字串所指定之參照位址內所包含的值, 其位置可為單格儲存格或一範圍 此函數之範例列於表 8 中, 其參照內容為表 1 資料 表 8:INDIRECT 函數使用範例 Example Comment INDIRECT("A2") 傳回儲存格 A2 中的值, 將得到 Bob INDIRECT(G1) 若儲存格 G1 中的值為文字 A2, 此公式將可得到 Bob SUM(INDIRECT("B1:B5")) 傳回 B1:B5 範圍內所有值的總和, 將得到 194 INDIRECT(ADDRESS(2; 1)) 傳回儲存格 $A$2 中的值, 將得到 Bob 使用 OFFSET 回傳指定範圍的儲存格或範圍 傳回依照所指定的儲存格位置 列數及欄數而算出的參照位置 ( 可指定欲傳回的列數及欄數, 正數為向下 / 右方向移動, 負數為上 / 左方向移動 ) 而傳回的參照可是單一個儲存格或一個儲存格範圍 第一個參數為函數搜尋新參照的來源參照 ( 基點 ), 第二個及第三個參數為欲由基點移動的欄列數, 也就是將回傳範圍的新基點 OFFSET 函數格式如下 : OFFSET(reference; rows; columns) OFFSET(reference; rows; columns; height) OFFSET(reference; rows; columns; height; width) 備註 函數中有包含 width 與 height 參數時, 會回傳一範圍 ; 若沒鍵入, 則會回傳新基點的值 Calc 資料庫相關函數 14

利用 INDEX 函數將儲存格傳回指定範圍 INDEX 傳回子範圍, 此子範圍由列號與欄號或選用的範圍索引所指定 根據環境之不同,INDEX 會傳回參照或內容 例如公式 =INDEX(B2:D3; 1; 1) 將回傳儲存格 B2 表 9 為使用 INDEX 函數的語法 表 9:INDEX 函數語法 語法 INDEX(reference) INDEX(reference; row) INDEX(reference; row; column) INDEX(reference; row; column; range) 描述 回傳整個範圍 回傳範圍中指定的列 傳回指定欄列的儲存格 若 row 及 column 皆為 1, 即回傳第一欄與第一列的交錯儲存格 參照範圍可包含多個範圍, 而 range 參數為指定範圍 特殊資料庫函數 表 10 所列為專門設計做為操作資料庫的函數 表 10: Calc 中之資料庫函數 函數 DAVERAGE 描述 DAVERAGE 傳回符合指定搜尋條件的所有列 ( 資料庫記錄 ) 中所有儲存格 ( 欄位 ) 的平均值 DCOUNT DCOUNT 計算資料庫中符合指定搜尋條件並且包含數值的列數 ( 記錄 ) DCOUNTA DGET DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP DCOUNTA 計算資料庫中符合指定搜尋條件並且包含數值或字母數字式數值的列數 ( 記錄 ) DGET 傳回符合指定搜尋條件的資料庫中參照儲存格的內容 DMAX 傳回資料庫中 ( 所有記錄 ) 符合指定搜尋條件的儲存格 ( 欄位 ) 的最大內容 DMIN 傳回資料庫中符合指定搜尋條件的儲存格 ( 欄位 ) 的最小內容 DPRODUCT 將儲存格內容符合搜尋條件之資料範圍的所有儲存格相乘 DSTDEV 使用符合指定條件的資料庫欄中的數字來計算基於範例基數的標準離差 記錄都當成資料的範例 DSTDEVP 計算根據符合搜尋條件的資料範圍所有儲存格的整體基數之標準變異數 DSUM 傳回符合指定搜尋條件的所有列 ( 記錄 ) 中資料庫欄位的所有儲存格總和 DVAR 傳回符合指定搜尋條件的所有列 ( 記錄 ) 中資料庫欄位的所有儲存格變異數 此範例的記錄都當成資料的範例 DVARP 計算符合指定搜尋條件的所有記錄中資料庫欄位的所有儲存格值的變異數 此範例的記錄均視為整體基數 15 特殊資料庫函數

結語 將 Calc 做為簡易資料庫 Calc 所提供之資料庫相關功能可滿足大部分人的需求, 鮮少使用的 OFFSET 及 INDEX 資料庫功能卻值得花時間學習, 因為長久下來可節省許多時間 結語 16