將Calc做為簡易資料庫

Size: px

Start display at page:

Download "將Calc做為簡易資料庫"

驿梁
7 years ago
Views:

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

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

3 序言 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 Betty Bob Brandy Frank Fred Ilsub James Lisa Michelle Ravi Sal Ted Tom Whil 序言 Introduction

為函數運算需要參照範圍時最常使用例如將一範圍命名為 Scores, 即可使用 =SUM(Scores).

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

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

排序透過 Calc 文件中的排序機制, 可重新排列工作表內容第一步驟為選取欲排序之範圍, 例如表 1 中的 A1 到 G16 儲存格再由功能表資料排序開啟排序對話框 ( 如圖 4), 最多可設定三樣排序條件圖 4: 排序欄位名稱為 Nmae 的資料若排序時範圍須包含欄標籤, 可由排序對話框選項標籤 ( 如圖 5) 中勾選範圍含有欄標籤核取方塊以防止欄標題與其他數據一同進行排序

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

篩選圖 5: 設定排序選項透過篩選可限制試算表中可見的列, 一般常用的篩選除了自動篩選外, 還可自訂篩選條件注意篩選後, 除了符合篩選條件的列資料, 其餘資料皆會隱藏一次選取多列作為篩選範圍時, 有可能會選到隱藏的列 ; 若欲避免此情形, 必須定義多個篩選範圍個別篩選自動篩選啟動自動篩選後,

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

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

標準篩選由功能表資料篩選標準篩選開啟標準篩選對話框 ( 如圖 7), 最多可設定 8 項篩選條件若欲關閉篩選可由功能表資料篩選刪除篩選關閉進階篩選圖 7: 使用標準篩選進階篩選最多接受 8 項篩選條件, 相較於標準篩選, 進階篩選必須先將篩選條件鍵入試算表的儲存格中 1) 於 Calc 文件中找一空白範圍,

9 標準篩選由功能表資料篩選標準篩選開啟標準篩選對話框 ( 如圖 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) 按確定操作篩選結果

10 備註範圍定義名稱後 ( 如圖 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

11 函數 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 資料庫相關函數

12 函數 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

13 使用 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 資料庫相關函數

14 搜尋值需完全相符時, 搜尋升序排序的資料範圍, 將比無排序更有效率, 但回傳的值是相同的搜尋值若不須完全相符, 且資料範圍中無相符資料, 會回傳同欄中小於搜尋值的第一個值例如欲在有三個數值的資料範圍中搜尋 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

15 使用 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 資料庫相關函數

16 表 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

17 利用 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 特殊資料庫函數

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

ACI pdf

ACI pdf 09 9.1 -...9-2 9.1.1...9-2 9.1.2...9-3 9.2 -...9-4 9.2.1 PMT - ()...9-4 9.2.2...9-6 9.3 -...9-8 9.3.1 PMT - ()...9-8 9.4...9-10 9.4.1... 9-11 9.4.2...9-12 9.4.3...9-14 9.5 -...9-17 9.5.1...9-18 1 Excel...9-21