HOME 回資訊服務處首頁 Login
2006年第8期
簡訊
本院「研究成果管理系統」上線測試
「公共設施預約管理系統」開放線上預約功能
研討會訊息-「2006年數位典藏技術國際研討會(2006 ICDAT)」國人免費參加
11月份訓練推廣課程預告
試用資料庫訊息
GIS專題
淺談GPS與生活應用
資訊應用
Office小技巧
 
資訊應用 >
上一篇 | 返回電子報
 
Office小技巧
 
應科中心/黃耀輝

前言

  工作上使用微軟公司Office的機會很多,如果能善用一些既有的功能,將讓事情變得更簡單輕鬆;尤其是經常性的報表,其資料繁瑣龐大,輸入步驟機械化,適合透過指令做自動或批次處理。因此,筆者想藉本文介紹一些經常會用到的技巧,提供給讀者參考。

插入大批資料至Excel表格

  Excel可開啟很多格式的檔案,也可插入工作表、欄或列,但如要一次大量插入又要不破壞原有的表格版面(含框線、參照或計算式),將增加很多困難度;不過讀者看完本文,便會明白它是如此容易。

  最常用的插入資料方式,就是每個儲存格逐一輸入或複製再貼上,若想要多欄多列一次貼上呢?其實ASCII (American Standard Code for Information Interchange)字元當中的HT:Horizontal Tab(Physical Device Controls: Format Effectors)就是跳欄字元,LF: Line Feed + CR:Carriage Return就是跳列字元,只不過一般狀況下它們是看不到的。以下將做實例說明:

1.使用純文字編輯軟體或WORD輸入下列資料(A??、B?? 為假設文字):

 A11   HT   A22  HT  A33   HT   A44    HT   A55  LF+CR

 B11   HT   B22  HT  B33   HT   B44    HT   B55  LF+CR

 使用鍵盤輸入HT,可按Alt + 09右數字盤(NumLock)輸入,LF+CR只要按Enter即可。

2.將上述資料標記區塊,點{編輯(E)}→{複製(C)},再到Excel標記區塊,點{編輯(E)}→{選擇性貼上(S)}選【文字】,即可整批貼上而不影響原有表格結構。

 這種處理方式適合「合併列印」或「程式化」來產生大量資料,如果只是單筆作業就不需如此大費周章。

互轉 XML-XLS產生Excel 表格

  Excel 2003支援XML【註1】 讓文書自動化更上一層樓,以下就讓我們享用它的好處吧(請注意:Excel有XML試算表及XML資料二種選項,二者格式有所差異)。

  1. 使用Excel打開舊有表格文件,點{檔案(F)}→{另存新檔(A)}→檔案類型(T),選XML試算表(*.xml),存成XML檔。
  2. 使用純文字編輯軟體或WORD開啟XML檔,讀者會發現它是開放格式,簡單易懂。它記錄表格文件的詳細資料,甚至可細到單儲存格的格式及計算式。
  3. 讀者可撰寫程式或使用合併列印來重寫XML檔,要異動的部份只有資料文字部份(如"AAAAAA"、"BBBBBB")及總列數目。

 4.  範例如下:

 … …
 <Worksheet ss:Name="Sheet1">   "表格名稱 Sheet1"
 ss:ExpandedRowCount="9"   "總列數目 9"
   <Row ss:Index="4" ss:Height="17.25"/>
   <Row ss:AutoFitHeight="0" ss:Height="22.5">
    <Cell ss:StyleID="s21"><Data ss:Type="Number">AAAAAA</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">BBBBBB</Data></Cell>
    …  … "重複連續列"
   </Row>   "列結束"
 … …

5.  在Excel選{檔案(F)}→{開啟舊檔(O)}選{FileName}.xml即可載入,亦可視需要再另存成XLS檔。

計算工作天及日曆天

  工作上常會用到工作天或日曆天來計算履約期限,Excel“分析工具箱”提供了NETWORKDAYS及WORKDAY二個實用的日期函數,使用說明如下:

  1. NETWORKDAYS(Start_date,End_date,Holidays)計算期間之標準工作天數(已扣除週六及週日),其中Start_date表示開始日期,End_date為終止日期。
  2. WORKDAY(start_date,days,holidays)計算到期日,start_date引數是您想要函數開始計算的日期,而days是開始日期之前或之後的標準工作天數,包含週末與假日。天數使用正數能從啟示日期往前計算,而使用負數則能往後計算。

  選用的holiday引數可以為一段儲存格範圍的陣列或參照,包括您在計算中想排除的日期;引數資料可自人事行政局與中央氣象局網站蒐集,但必須注意契約關於履約期計算方式的規定。如果holidays引數為空白,函數會計算從Start_date開始的所有工作日。

 讀者若發現上述函數無法使用時,可點{工具(T)} {增益集(I)}勾選{分析工具箱} 來啟動。

Word的數字大寫

  有價證券之金額通常都會要求使用大寫,但大寫數字不適合程式運算,且容易搞錯,不過Word 可輕鬆轉換阿拉白數字為大寫型態,說明如下:

1.  英文大寫:

 打開WORD文件,點{插入(I)}→功能變數(F)→欄位內容-公式(L)於公式(F)欄位,輸入以下
文字:= 123456.78 \* DOLLARTEXT \*UPPER {確定},點擊滑鼠右鍵,選{更新功能變數(U)}後,即會出現"ONE HUNDRED TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX AND 78/100"。

 要修改數目時,只要點擊滑鼠右鍵,選{切換功能變數代碼(T)},會出現"{=123456.78 \* DOLLARTEXT \*UPPER}",只要修改123456.78為您要的數目,再點擊滑鼠右鍵,選{更新功能變數(U)5}即可。

2.  中文大寫:

 方法同上,只要將 "\* DOLLARTEXT \*UPPER" 改成"\* CHINESENUM2" 即可,不過無法處理小數,數目為"12345678" 時將出現 "壹仟貳佰參拾肆萬伍仟陸佰柒拾捌"。

Word、Excel的連結

  Excel雖定位為電子試算表,但也被廣泛當作資料庫,如果讀者不熟悉資料庫處理語言,則不妨參考本節酌用,或許有所助益。

  Excel同一表格間各儲存格可互相參照為眾所週知,但不同檔案間亦可參照,範例
如下:'Dir:\Path\[FileName.xls]SheetName'!$A$1:$B$100,但參照決定路徑必須注意各檔案存放的確切位置,避免檔案間失去連結;如果再善用Lookup、Match等搜尋函數,使用Excel做資料處理將更具彈性。

  Word合併列印功能的資料來源主要是Excel,除了一般常用的插入合併欄位外,更提供諸多插入功能變數,例如:Ask詢問、Fill-in顯示提示文字、If …Then…Else以條件評估、Merge Record # 合併記錄編號、Merge Sequence #合併順序編號、Next Record下一筆紀錄、Next Record If下一筆紀錄條件、Skip Record If 跳過紀錄條件等等,使用者可靈活地一次將多筆紀錄作綜合處理。

  此外,不知讀者有無大量傳送電子郵件的經驗,郵件內容若是相同時並無困難,但收件人與郵件內容需逐一配對呢?其實只要使用Excel在表格中輸入收件人姓名、電子郵件地址、其他個別資料等等,再使用Word設計好信件格式,並插入適當合併欄位後,點選合併列印工具列的 {合併到電子郵件}→訊息選項→到→插入【收件人地址欄名稱】,再配合OutLook發送郵件即可,OutLook會對各該筆紀錄之收件
人地址及其合併列印郵件來逐一發送郵件。

參考資料

【註1】:XML「可擴展標示語言」(eXtensible Markup Language)是用於標示具有結構性資訊的電子文件的標示語言。XML是根據一個國際標準 - Standard Generalized Markup Language(SGML)- International Organization for Standardization(ISO)ISO 8879:1986所製訂而成的XML的格式。




上一篇 | 返回電子報
 
 本電子報所有文字、圖片版權為中央研究院所有 。 電子報出版系統由中央研究院資訊服務處開發。