『資料處理過濾增益集3.9發佈』

 資料處理過濾增益集3.9發佈訊息，最近有些網友來信建議與反應一些bug問題，針對網友的反應進行一些更新與修正如下

1.更新條件處理功能介面，提供指定寫入欄列

 

 

2.更新加入單一參數公式功能介面，提供指定寫入欄列

3.新增計算PR值功能。

 

4.新增計算排名百分比功能，整合PERCENT.RANK.EXC與PERCENT.RANK.INC 兩個函數。

5.修正計算排列組合中設定數字功能無法正常運作的bug。

關於system data sqlclient sqlexception必須宣告純量變數"@"

最近在幫學校設計的網頁程式的進行資料庫轉移，原本都是利用 GridView 控制項與 AccessDataSource 連接 Access 的資料庫檔案，現在要改用 Microsoft SQL Server 2008 R2，把原先資料連接的控制項 AccessDataSource 改為 SqlDataSource。

原本以為只要把執行sql動作的語法進行修改，例如下列命令列

DeleteCommand="DELETE FROM [course] WHERE [序] = ?"

改成 DeleteCommand="DELETE FROM [course] WHERE [識別碼] =  @序"

結果執行刪除資料的動作一直出現出現下列的錯誤訊息：

 

應用程式中發生伺服器錯誤。

必須宣告純量變數 "@序"。

描述: 在執行目前 Web 要求的過程中發生未處理的例外狀況。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。
例外狀況詳細資訊: System.Data.SqlClient.SqlException: 必須宣告純量變數 "@序"。
原始程式錯誤:

 

檢查程式碼與網頁的原始檔，看不出來有任何錯誤，再測試執行更新的動作命令，同樣有(序 = @序)的參數設定如下列：

UpdateCommand="UPDATE course SET  開課年級 = @開課年級, 課程代碼 = @課程代碼, 領域別 = @領域別  WHERE (序 = @序)

執行結果卻是正常沒有錯誤訊息，反覆查了半天才發現原來漏掉設定GridView控制項的 DataKeyNames的屬性。

必須設定 DataKeyNames 屬性，以便讓 GridView 控制項的自動更新和刪除功能運作，所以設定DataKeyNames="序"就可以正常執行刪除資料的動作。

 

『資料處理過濾增益集3.6發佈』

最近把處理12年國教相關報名與後續的業務所設計的Excel巨集加入『資料處理過濾增益集3.6』中，並把一些bug做修正，下載的網址http://www.addinfactory.com。

利用Excel2010與『資料處理過濾增益集3.3』來處理配對問題

最近剛辦完國中教育會考試務的工作，在找尋監試人員時發生一個問題，因為我們開放讓老師們自行在網路登記自己要配合的夥伴(每間試場要兩位)，而每位老師只能登記自己的基本資料不能幫他人登記，要能成為夥伴必須彼此都要參加監試工作，而且都指定對方為夥伴才能成對。經過一段時間的登記，我們得到一個資料如下表，一欄是登記監試工作的老師姓名，一欄是他所選擇的夥伴姓名，夥伴姓名欄位空白的代表不指定合作的夥伴。

針對這個問題，因為可能還會承辦試場業務，我把這個功能加在『資料處理過濾增益集3.3』的[特殊操作]上，增加一個[配對組合]的功能，這功能操作很陽春

只是針對這個特別需求，使用時只要先將配對的第一個欄位資料選取，如下圖。

再點選[配對組合]即可完成配對組合。

配對結果如下，相同數字者表示配對成功為同一組。

剩下沒有成對的只要另外處理即可。

為什麼Excel的加總公式無法正常運作？

這是一個網友在Microsoft Office 系統 論壇 發問的問題，我把解決的問題錄成影片

為什麼開啟Excel2010時工作表都是一片灰色，看不到資料格？

最近有同事問到：為什麼他所使用的Excel2010，一啟動之後工作表都是灰色，看不到資料格？如下圖所示

雖然還是可以輸入資料運作，但是看起來就是怪怪的，而且在別人的電腦開啟Excel檔案都不會像上述畫面一樣，唯獨他的電腦的Excel2010會這樣，是不是Excel2010故障。其實這個畫面是[檢視]功能中的[分頁預覽]的模式。

並不是Excel2010故障，一般預設的檢視模式是[標準模式]，所以使用者開啟Excel2010看到的是下圖的樣子

至於說為什麼他的Excel2010啟動的時候都是[分頁預覽]的模式，原因是不小心動到以下的設定，在[檔案]/[資訊]/[選項]在[Excel選項]表單中的[一般]不小心把[新工作表的預設檢視]設定為[分頁預覽]的模式，只要改回[標準模式]即可解決這個問題。

如何將巨集程式放置於自訂功能區？

使用者如果希望此巨集程式能夠方便使用，可以把巨集放在自訂的功能區中，如何把巨集放置在自訂的功能區中？

舉例我們已經建置一個名為changefont的Excel2010的VBA巨集，我們要把它放置在Excel2010的功能區域中方便我們使用，我們可以依照下列步驟進行：

1.執行『檔案』/『選項』，在『Excel選項』表單中，點選『自訂功能區』選項，切換選擇命令為『巨集』

2.找到我們所建立的changefont這個巨集，然後在右邊自訂功能區中，按下下方『新增索引標籤』按鈕，新增一個自訂的『新增索引標籤』。

3.點選『重新命名』按鈕，在輸入框中輸入"變更註解字型"，按下確定。

4.然後再點選"變更註解字型"這個自訂的索引標籤下方的『新增群組(自訂)』，點選『重新命名』按鈕，"顯示名稱"改為『變更』。

5.按下中間『新增(A)』這個按鈕，再將changefont這個巨集加入"變更註解字型"這個自訂的索引標籤中的『變更』，再按『確定』。

6.完成設定之後，可以在功能區中看到所增加的"變更註解字型"這個自訂功能區，使用者只要點選『變更』群組中的changefont，即可執行該巨集。

利用VBA程式解決Excel2010大量變更註解字型的問題

接續上篇文章解決Excel2010變更註解字型的問題，有使用者在Microsoft Answers提到：如何才能將已經建立好的多筆的儲存格註解一次進行字型的變更，而不用逐筆變更。

舉例以下工作表已經在數個儲存格中加入註解，如果要變更每一個儲存格的註解字型或相關格式，只能逐筆進行，無法以全部選取的方式，一鍵完成。這個需求在Excel2010並未提供，只能透過VBA巨集程式來達成。

所以我們的目標要建立一個可以一次將所有註解字型進行變更的VBA程式。

程式碼內容如下

'-----------
'作者:楊煥謀
'歡迎引用

Sub changefont()
Dim ob As Object
Dim obrange As Range
On Error Resume Next
Set obrange = Selection
For Each ob In obrange.Cells
ob.Comment.Shape.Select True
With Selection.Font
        .Name = "微軟正黑體"   '修改此字型名稱即可變更駐解字型

        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
'        .TintAndShade = 0
'        .ThemeFont = xlThemeFontNone
    End With
 With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .ReadingOrder = xlContext
        .Orientation = xlUpward
        .AutoSize = False
        .AddIndent = False
    End With

Next





End Sub

這支程式可以將選取區中所有儲存格的註解字型變更為"微軟正黑體"  ， 使用者只要修改此字型名稱即可變更駐解字型，為了讓使用者更清楚其作用，我加上一個格式設定，讓註解變成橫書垂直方向。

如何使用這支程式？

1.因為我們要在Excel內建的VBA IDE環境中加入巨集程式，所以首先要把『開發人員』功能區叫出來，因為在Excel2007開始將VBA IDE放置在『開發人員』功能區，而此功能區沒有放在使用預設的功能區之列，所以使用者必須執行『檔案』/『選項』

在『Excel選項』表單中，點選『自訂功能區』選項，在右邊『自訂功能區』選單勾選『開發人員』(預設沒有勾選)

勾選完按確定可以看到功能區中新增一個『開發人員』功能區。

2.再點選『開發人員』/『Visual Basic』，叫出VBA IDE

2.點選所在的Excel工作簿名稱的VBAProject，舉例本範例的工作簿名稱為"如何大量變更註解字型.xlsm"，副檔名為.xlsm代表是含有巨集的工作簿。按右滑鼠右鍵，在突顯功能表中選取插入模組

3.打開新增的模組，將上述程式碼複製貼上即可。

4.將滑鼠游標移至程式碼中，按下執行按鈕進行測試即可。

5.測試的時候請註意：必須將所有要更改註解的儲存格選取，並將點選『校閱』/『註解』/『顯示所有註解』，將儲存格上的註解顯示出來，這樣才能確保處理的是註解字型而非儲存格字型。

6.執行的結果如下

7.特別註意此巨集程式沒有復原功能，建議先複製一份原稿再進行測試!

如何利用ExcelMail3.0把Excel格式的薪資表內容，分別寄送給薪資所得的個人？

很多小型企業或學校受限於預算並沒有使用專屬的薪資系統，而是利用Excel進行薪資資料的建置，因為人數不多這樣的軟體已經蠻符合需求，而且比較自由不會受限於商業薪資資料庫的限制，現在坊間很多Excel商業應用的書籍都會有會計範例，只要知道一些簡單的會計出納原則，很快可以建立Excel的薪資工作表檔案，再利用Word合併列印的功能可以將每筆的薪資資料列印出來，但是隨著資訊化的普及與環保的要求，紙張的薪資單慢慢被取代為電子薪資單，在Word合併列印的功能是可以將合併的檔案以電子郵件寄至個別之收件者，只要預設使用Outlook(不是作業系統提供的Outlook Express)做為寄信的軟體，即可達到此目的，不過在Word2007中它的功能比較單純是將Word合併後文件的內容轉成電子郵件，以Outlook2007寄給收件者，無法附加個別檔案。

而Excel本來就有與電子郵件結合的功能，但是主要是整個工作表或工作簿的寄送，但是這樣並無法滿足只寄給個人薪資的需求，如果利用ExcelMail可以達成這個目的。

讀者如果不熟悉ExcelMail的操作可以參考以下四篇文章

ExcelMail3.0群組寄信軟體介紹(一):批次寄送個別收信者資料

ExcelMail3.0群組寄信軟體介紹(二):依特定條件建立群組的功能

ExcelMail3.0群組寄信軟體介紹(三):批次寄送附加檔

ExcelMail3.0群組寄信軟體介紹(四):寫一封中式直書的邀請卡給老同學

ExcelMail就是可以把對應每一位收件者的資料內容寄給收件者，每一位收件者只會收到自己的資料而不是全部的資料(參考ExcelMail3.0群組寄信軟體介紹(一):批次寄送個別收信者資料)，但是如果資料欄位眾多的話，像公教的薪資各項資料項目多達二十幾項(保含職級、所得、保費)，如果直接以一列的內容寄給收件者，因為欄位眾多不容易閱讀，最好把它變成一張表格的樣式，這樣收件者要列印保存也比較方便。

如果直接將Excel資料寄出，收件者得到的是下圖一的內容。

(一)

如果我們可以利用ExcelMail進階的功能可以產生像下圖二的內容。

如何產生這樣的結果，ExcelMail這個功能的概念與Word合併列印很像，使用者先產生一份Word的文件，再把Word中特定標記的部分以Excel工作表中的欄位名稱取代，在ExcelMail寄信的過程中，便會進行資料取代產生信件的功能。

例如上面的Excel薪資資料表格式是一位在東部服務的宜真老師所服務學校使用的格式，大致上各校教師的薪資名目大致相同，所以。我們先把圖一的資料表加入一列(如圖三)，此列主要是要放置欄位標記，記得此欄位標記，除了是呈現信件資料所要的內容外，不要與信件其他內容有相同，以免產生錯誤取代。

 

(圖三)

然後再依照所要寄給收件者的欄位資料設計word信件如圖四，設計完存檔作為信件的主要文件。

再來的步驟請參考ExcelMail3.0群組寄信軟體介紹(四):寫一封中式直書的邀請卡給老同學 這篇文章的，執行寄件的步驟，最後收件者收到的信件內容如圖二。

注意:如果希望採pdf附件的方式寄送，office 2007版本尚須安裝(2007 Microsoft Office 增益集：Microsoft 另存 PDF 或 XPS 檔)，然後在ExcelMail程式主表單中勾選『設定』/『啟用批次word信件功能』/『以PDF寄送』，再執行寄信動作!

再論在Excel中使用『資料處理過濾增益集』進行資料合併

現在隨著校園資訊化的推展，現在台北縣政府直接請廠商寫了校務行政系統，把一些學校共同的資料集中統一管理，但是學校的事務繁雜，除了學生學習成績與資料外，還有很多業務資料待整合，現在縣府請校務行政系統負責的廠商一直在原有的校務行政系統統增加開發一些新功能，不過這種集中式管理的資料庫做到包山包海，只怕最後會出包，尤其廠商人力不足時，所以教育局將注意力聚焦在各級學校共同核心的資料，至於學校個別需求的功能，各校自行發展或找尋可用的方案解決。

早在縣府校務行政系統未發展時，其實有些學校各單位已經在進行資料數位化，很多學校希望透過電子檔案建立，把原本要反覆書寫建立的資料，變成可以流通與重複利用的資料，其實這樣的資訊化工作，對於學校這種層級的單位已經足夠，但是這樣看似簡單的任務，卻經常因為缺乏由統一的單位來主導而流於各行其事。很多單位在學校資訊單位成立之前，已經建置很多各種資料的電子檔案，可惜的是大部分沒有統一的資料格式，甚至出現不同單位在收集同樣的資料，卻用不同的表格或檔案格式呈現，導致資源的重複浪費。

理論上這個工作開始應該由學校的資訊單位來主導，偏偏現在中小學校的資訊單位幾乎是各校最後成立的單位，而且位階都很低，也沒甚麼發言權(很多資訊組長戲稱自己是資訊工友)。理想狀況當然是由資訊單位統一的訂定各種資料格式，然後將各種資料資料庫化。但是這在現實狀況是達不到，一方面現行的中小學中的資訊單位通常只是一個光桿組長，要管的事情太多不見得有時間與能力去做這樣的事。而且每一單位有其專業考量，同樣的業務表格與資料格式已經傳承許久，業務的承辦人可能還沒有熟悉業務，蕭規曹隨都來不及，遑論要創新，而且資訊單位不見得了解所有單位的業務，如果真的要做，就必須與各單位進行協商與討論，資訊單位在學校面對的不是上級單位，就是平行單位，誰要和你協商，在很多學校資訊單位是被定位為支援單位，不是政策擬訂與執行單位，我需要你，你就來支援，其他廢話少說!這是天才的中華民國文官體制所造成的，只要是必須由專業人員擔任的職務，職務位階就不會太高，所以出現外行領導內行，不行的領導行的現象並不奇怪。譬如政府宣導資安很重要，但是由位階低的資訊單位去負責計劃的訂定與執行，誰管你!做了一堆的管理方案與教育訓練，只有你自己當真!

大環境雖如此，還是得想辦法找一條自己生存之道，山不轉路轉。例如資料的交流利用方面，資訊組大概都需要一些學校各處室單位的資料，來建置學校網站，與其要求各單位把資料按照統一的規範分享出來，還不如自己想辦法把別的單位已經建立的資料，轉為自己所需的資料!

例如學校要在網站放置人員資訊查詢系統，基本至少需要有職銜、辦公室位置、分機、擔任導師班級、電子郵件、任教科目等資料，這些資料並不是由資訊單位建立的，都必須由其他單位提供，像教職員工辦公室位置的資料如何取得？本校教職員工有三百多人，每學年度除了留任的行政人員，大部分的教職員辦公室位置幾乎都會異動，安排座位的是總務單位，負責這項業務的同仁會把這個資料輸入在已經建立的一個Excel的檔，這個表格是這位同仁精心設計之後，最滿意的樣式(如下表一)。但是這樣的表格，利於視覺觀看但並不利於轉成資料庫，我們還必須手動處理，說實在的Excel的確是一個很好的資料處理的工具，尤其在資料的建立、輸入與重整，幾個複製貼上的指令，加上自動填滿，是很快就可以把資料重新安排成為行列的表格(如表二)，這樣就很方便轉成資料庫。

(表一) (表二)

但是這樣的資料我們要整合至全校教職員工的系統中我們會遇到一些問題，例如辦公室座位資料並不是全部同仁都有，例如留職停薪的同仁並不會出現在上面，也無法用排序對應複製貼上，因為資料數目不等，如何讓這些辦公室座位資料與人員資料對應，變成一個水磨工夫，只有一項資料慢慢對可能還好，如果再加上任教科目(非教師則無此資料)、擔任導師班級(不是每一個教師都擔任)，等幾項資料的匯整，這個工作就變成災難，剛開始接資訊組第一個月都在對資料建資料。

後來我第一個使用的方法是轉到Access上用資料表關聯的方法去處理，這個方法是可以很快的將兩個部分資料相同的工作表進行對應去產生一個合併的資料表，不過還是要幾個工作表與主資料表進行關聯產生新工作表之後，再處理再合併。後來我自己弄一個可以直接在Excel進行資料對應擷取的巨集，直接在Excel上面處理，後來把這個巨集放在『資料處理過濾增益集』中的『工作表資料擷取』。

我放一個範例的檔，讀者可以嘗試將此檔案中科目工作表與職稱工作表合併至員工資料工作表。範例檔下載

操作步驟影片

 讀者可以參考如何快速將Excel2007 兩個資料部分相同的工作簿合併這篇文章，道理是一樣的。