LibreOffice巨集應用簡介資訊室沈明禮

按讚加入E河川-交流水知識粉絲團 

  發刊期數:第0293期/ 發布日期:2018/08/24

圖1_pic

一、緣由

水利署臺北辦公區107年電腦軟硬體委外維護案有關筆記型電腦維護清單彙整,藉由財產管理系統匯出臺北辦公區資訊設備相關財產之Excel檔案,經篩選出筆記型電腦相關資料後,另儲存成ODF試算表(ods檔案),接著需要完成下列4件工作:

工作1:逐筆轉換民國之購置日期:如1020501轉成102.05.01、981201轉成98.12.01。
工作2:將帶「.」之民國購置日期再轉成西元日期,如2013/05/01、2009/12/01。
工作3:逐筆計算西元購置日期至2017/12/31之使用日數,再轉成使用年份,使用年份需計算至小數點後1位,小數點後第2位四捨五入至第1位。
工作4:逐筆累計計算出筆記型電腦數量及平均使用年份,平均使用年份之小數點後四捨五入計算方式同使用年份。

二、LibreOffice 巨集初階版

1. 逐筆之2017/12/31(訖)減去西元購置日期(起),計算起訖日期間相差之日數。
2. 起訖相差日數/365.25,所得之值(使用年份)再透過LibreOffice Calc內建之Round函式計算至小數點後1位,小數點後第2位四捨五入至第1位。
3. 逐筆累計使用年份後,計算出平均使用年份,亦透過Round函式計算至小數點後1位,小數點後第2位四捨五入至第1位。
工作1實作:LibreOffice Basic巨集實作完成,巨集內容:開啟筆記型電腦清單(含巨集).ods時,點選工具/巨集/編輯巨集/筆記型電腦清單(含巨集).ods/Standard/Module1/main1(如圖1)。
工作1清除:開啟筆記型電腦清單(含巨集).ods/「筆記型電腦1」工作表,點選工具/巨集/執行巨集/筆記型電腦清單(含巨集).ods/Standard/Module1/clrRngs1(如圖2),結果:清除購置日期(巨集轉後)欄I2~I36儲存格之內容(如圖3)。
工作1執行:開啟筆記型電腦清單(含巨集).ods/「筆記型電腦1」工作表,點選工具/巨集/執行巨集/筆記型電腦清單(含巨集).ods/Standard/Module1/main1(如圖4),結果:產出購置日期(巨集轉後)欄I2~I36儲存格之內容(如圖5)。
工作2至4:LibreOffice Calc之函式實作完成,詳筆記型電腦清單(含巨集).ods/「筆記型電腦1」工作表:購置年欄(K2~K36儲存格之公式)、購置西曆年欄(L2~L36儲存格之公式)、……、使用年份欄(Q2~Q36儲存格之公式),如購置月日欄M2儲存格公式=(SUBSTITUTE(MID(I2,FIND(".",I2,1)+1,LEN(I2)),".","/"))(如圖6)。

  • 圖1
  • 圖2
  • 圖3
  • 圖4
  • 圖5
  • 圖6

三、LibreOffice 巨集進階版

1. 逐筆之西元購置日期(起)及2017/12/31(訖)各自透過LibreOffice Basic提供之DateValue函式回傳單一整數值,使用此值計算起訖日期間相差之日數。
2. 起訖相差日數/365.25,所得之值(使用年份)再透過使用者定義函式之udn_Round函式計算至小數點後1位,小數點後第2位四捨五入至第1位。
3. 逐筆累計筆記型電腦數量及使用年份,計算出平均使用年份,亦透過udn_Round函式計算至小數點後1位,小數點後第2位四捨五入至第1位。
工作1至4實作:LibreOffice Basic巨集實作完成,巨集內容:開啟筆記型電腦清單(含巨集).ods時,點選工具/巨集/編輯巨集/筆記型電腦清單(含巨集).ods/Standard/Module2/main2(如圖7)。
工作1至4清除:開啟筆記型電腦清單(含巨集).ods/「筆記型電腦2」工作表,點選工具/巨集/執行巨集/筆記型電腦清單(含巨集).ods/Standard/Module2/clrRngs2(如圖8),結果:清除購置日期(巨集轉後)欄I2儲存格~使用年份欄Q38儲存格(含9儲存格/列*37列=333儲存格)、筆記型電腦數量欄C38儲存格之內容(如圖9)。
工作1至4執行:開啟筆記型電腦清單(含巨集).ods/「筆記型電腦2」工作表,點選工具/巨集/執行巨集/筆記型電腦清單(含巨集).ods/Standard/Module2/main2(如圖10),結果:產出購置日期(巨集轉後)欄I2~使用年份欄Q38、筆記型電腦數量欄C38之儲存格內容(如圖11)。

  • 圖7
  • 圖8
  • 圖9
  • 圖10
  • 圖11

四、LibreOffice Calc vs LibreOffice Basic

1. 字串擷取函式:Calc提供MID 函式,Basic均提供Mid 函式。
2. 字串合併函式:Calc提供CONCATENATE 函式,Basic提供「&」運算子。
3 字串搜尋函式:Calc提FIND 函式,Basic提供InStr 函式。
4. 字串取代函式:Calc提SUBSTITUTE 函式,Basic提供Mid 陳述式。
5. 數字採四捨五入指定小數點位數函式:Calc提供ROUND 函式,Basic無提供,使用者需自行撰寫函式(udn_Round)。

五、LibreOffice巨集安全性

1. 工具→選項→安全→巨集安全性→點選「低(L)不建議。」項次(如圖12)
開啟含巨集之LibreOffice文件時,不作任何確認,即可執行所有巨集巨集安全性設定為低時,請確認開啟的文件(含巨集)是安全的。
2. 工具→選項→安全→巨集安全性→點選「中(M)。」項次(如圖13)
開啟含巨集之LibreOffice文件時,會有巨集可能包含病毒之安全性警告,需點選「啟用巨集(E)」按鈕,即可執行所有巨集(如圖14)。

  • 圖12
  • 圖13
  • 圖14

六、結論

1、LibreOffice Calc提供內建函式,較LibreOffice Basic多。
2、運算較複雜之處理,透過LibreOffice Basic巨集程式撰寫較易達成。
3、LibreOffice Calc儲存格輸入小寫英文函式名稱,於儲存後自動轉成大寫英文函式名稱。
4、LibreOffice Basic英文函式名稱,大小寫無區別。
5、不受信任的來源之相關巨集請勿隨意執行,以避免電腦中毒。

七、參考資料

1、https://help.libreoffice.org/5.4/zh-TW/text/sbasic/shared/01000000.html (使用 LibreOffice Basic 進行程式設計)
2、https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=64478 (udn_Round 函式參考)
3、筆記型電腦清單(含巨集).ods檔:請下載相關檔案之筆記型電腦清單(含巨集).zip檔後解壓縮即可。
4、本文內容均於LibreOffice 6.0.3.2版環境下執行及編製。

主題回應