巧用EXCEL—用公式及功能打造各種日常小工具

范鵬飛+曹崢+張苗

摘要：對于EXCEL，我們每個人都不陌生，幾乎是我們日常工作中最常用的工具之一，但往往，對于其內置各種強大的功能，我們只是利用了很小一部分，實際上，很多公式，技巧，以及宏和VBA的制作可以幫我們大大的提升工作效率，下面我就給大家介紹幾個EXCEL公式，及VBA簡單的使用方法及應用。

關鍵詞：EXCEL；公式；工具

1.重復性試驗中的IF（ISERR（）及判斷IF THEN公式的應用

在對標準器進行重復性試驗時，我們通常是將重復性試驗表格打印出來，填寫記錄再經過計算，對照數據做出判定。其實，我們完全可以應用EXCEL強大的內置公式等功能，將重復性試驗編程EXCEL電子表格，只需要將重復性試驗的數據填入，之后不需重復打開或查詢計量標準檔案，無論是重復性結果，還是重復性結果的判定，都會在瞬間完成，一步到位。只需填入數據后，點選打印，將結果直接放入檔案即可。免除了重復查詢，填寫的繁瑣工作。

首先，在重復性試驗的EXCEL文件里，我們可以根據JJF1033-2016標準格式進行，并在經過貝塞爾公式計算重復性結果的單元格里輸入標準偏差公式：“=IF（ISERR（STDEV（X：Y）），""，STDEV（X：Y））”，X，Y分別填入試驗數據第一個和最后一個單元格的行列號，如B1：B10。與平常直接輸入公式=STDEV（X：Y）不同，我們需要在前面加入“IF（ISERR（STDEV（X：Y）），""，”，因為在數據沒有填寫時，結果計算單元格通常會顯示“#DIV/0！”字樣，不利于文件的打印。我們在計算公式前添加上述語句，當數據為空時，結果計算單元格就會同樣顯示空白，而不會顯示“#DIV/0！”字樣，這也是平常工作中經常會遇到的問題。

在重復性試驗判定結果單元格我們同樣需要用到公式，即在判定結果單元格輸入“=IF（ISERR（Y/X），""，IF（X<=Z，"合格"，"重新評定不確定度"））”，X填入重復性結果的單元格橫列號，Y填入重復性試驗中第一次數據的單元格橫列號，Z填入技術報告中，當做不確定度分量引入重復性試驗的數值（單次實驗標準差）。同樣在判定公式前加入IF（ISERR（））語句，即在重復性結果在沒有數值的情況下，判定結果也顯示為空，而不會顯示“#DIV/0！”字樣。同時，在表格打印區域外，找一處填寫原重復性的數據，在重復性試驗得出結果時，自動與原重復性相比較，如小于等于原重復性，判定結果顯示合格，如大于原重復性，則顯示重新評定不確定度。

同時，我們也可以在表格打印區域外，做兩個公式，公式一：將重復性作為變量，其他評定的不確定度分量作為不變量，當重復性結果大于原重復性時，直接按照新的重復性所引入的不確定度分量計算出合成和擴展不確定度。公式二：將新計算出的擴展不確定度做為變量，其他作為不變量，做不確定度驗證公式。這樣，在輸出重復性試驗數據后，我們將在表格中迅速得知本次重復性試驗結果所得到的所有結論，一目了然，不用重新翻閱技術報告，就可以得到所有結論。

2.規程一鍵查新功能HYPERLINK公式的應用

如何實現規程一鍵查新呢，我們可以選擇兩列，并分別命名為規程號和規程更新查詢。在規程號列，輸入屬于對應計量標準的各個規程號編號，比如卡尺量具檢定裝置下所屬規程JJG30-2012《通用卡尺》檢定規程的查新，規程號列下輸出JJG30即可。同時，在旁邊規程更新查詢列寫入公式：=HYPERLINK（"https：//www.baidu.com/s？wd="&X，X），X為規程之前JJG30所在單元格，如JJG30所在單元格式A1，則在規程更新查詢列B1格輸出公式：=HYPERLINK（"https：//www.baidu.com/s？wd="&A1，A1），我們就可觀察到，規程更新查詢列出現了JJG30可點擊的藍色超級鏈接，只要一點擊，就會自動打開瀏覽器，通過百度搜索JJG30規程的相關信息，我們很快就可以觀察到是否有規程更新的信息。之后所有規程的查新都可以用此公式完成，即：在規程號列，輸入JJGXXX，或JJFXXX（不要加后面年費）

這樣，我們的規程查新功能就完成了，只需點擊一下超級鏈，直接打開網頁自動查詢，方便快捷，不用你再一本一本找規程號，再在搜索引擎上按個查詢了，只需要一次把所有規程號記錄在基本庫了，便可節省我們大部分的工作時間。

3.超期提醒功能

在進行計量標準器管理時，對于標準器周期送檢日期是重要的一環，日常管理中，基本都會把日期寫到一處，但由于信息繁多，周期并不統一，對于日期的管理并不方便。如何能使日期提醒一目了然，最方便的方法就是使送檢日期隨著實時日期通過顏色動態變化來呈現。比如，在超周期顯示紅色，1個月內到期顯示黃色，其余不顯示顏色。通過3種顏色的實時變化，使標準器送檢周期變得一目了然，實時變化。那么，如何實現這樣的功能呢，我們就需要依靠EXCEL其中一項功能：條件格式。

在EXCEL，選中溯源日期單元格后，在開始選項卡中，找到條件格式并點擊，選擇新建規則，開始創建我們的日期實時提醒功能。首先選擇規則類型，選擇使用公式來確定單元格，在下面輸入我們需要的公式，比如超期顯示紅色，就輸入=（L1+365）-TODAY（）<1式中，L1代表標準器的溯源日期的單元格，公式很容易理解，就是溯源日期加365天，再減去當前的日期，如何小于1，則符合單元格條件變化。輸入公式欄右邊選擇公式的使用范圍即可。之后選擇下面的格式，選擇填充選項卡，選擇填充顏色：紅色。這樣，我們的第一個規則就創建完畢了。即：標準器有限期如已超過當前日期，則顯示紅色。

創建了第一個規則，那下面的規則我們也就很容易創建了，以同樣的方法，再創建一個月內將到期的，提醒黃色的規則。同前面步驟一樣，創建新規則。在公式中輸入=（L1+365）-TODAY（）<30，選取適用范圍和填充顏色，就完成了我們標準器周期到期動態顯示的功能。之后，我們只需像下拉填充公式的做法一樣：將鼠標移動至單元格的右下角，待其變為十字后，向下拉拽至你所需運用此條件格式的所有單元格即可。當然，如果有的標準器是半年周期，那么也很簡單，因為之前通過填充公式的方法，已將溯源日期單元格默認為一年周期，所以我需要對半年周期的進行單個設置：選定要修改條件格式的單元格，在開始選項卡中找到清除，點選清除格式，再重新按照上面步驟進行條件設置，只是將365變成184，即可達到半年周期提醒的效果了。