基于Excel的聯軸器平鍵連接參數的查詢系統

高海亮

（山西煤礦機械制造股份有限公司， 山西 太原 030031）

引言

在帶式輸送機整機設計時，驅動為關鍵部分，聯軸器的選型及設計必須要涉及到的就是平鍵連接的截面尺寸及公差查詢，一般查國家標準GB/T 1095—1979如圖1所示，然后根據標準推薦值去計算對應的所需值。

圖1 GB/T1095-1979標準部分截圖

1 Excel的優勢

Excel是office三大辦公軟件之一，是辦公電腦安裝必備軟件，對于辦公族使用非常便利。處理數據和各種圖表都是非常的出色。包含有大量的公式應用及計算函數。對于繁瑣的工程計算應用公式鏈接可以高效計算，無需學習編程知識，普通人經過簡單的函數學習即可提高工作效率。現在在手機普及的年代，通過手機也可以打開查看和編輯Excel文檔。

2 設計思路

利用公式鏈接，改變軸徑對應單元格數值，需要查詢的數據單元格自動顯示截面尺寸等參數，實現快速查詢的功能。

3 設計步驟

下面介紹查詢系統的設計步驟及函數應用。

3.1 表格創建

創建如圖2所示的表格數據。從圖中可以看出，陰影部分的數據是重復的，建立輔助列，建立數據連接之后，輔助列是需要隱藏，隱藏后效果見圖1。

圖2 GB/T1095-1979數據的錄入及輔助列的建立

3.2 圖片繪制

繪制后的圖片如圖3所示，要求圖片后期處理成透明圖層格式的，否則拖動到下頁圖6所示位置時會遮擋單位格及文字。

3.3 輔助列建立

如圖2中所示，陰影部分為輔助列后期會隱藏掉，本例中用到 Excel函數為 LOOKUP，由于LOOKUP查詢的條件要求，所以建立輔助列。

圖3 透明圖片樣例

3.4 函數介紹及應用

LOOKUP函數是Excel中的運算函數，其基本用法中的向量形式要求查詢的數值必須按升序排序，否則會出現錯誤。

向量形式用法為：=LOOKUP（lookup_value,lookup_vector,result_vector）。

式中：lookup_value為函數LOOKUP在第一個向量中所要查找的數值，它可以是數字、文本、邏輯值或包含數值的名稱或引用（通俗理解為要查找的數值）；

lookup_vector為只包含一行或一列的區域，lookup_vector的數值可以是文本、數字或邏輯值；（通俗理解為要查找的數值所屬的區域）；

result_vector為只包含一行或一列的區域，其大小必須與lookup_vector相同（通俗的理解為要查找的數值所屬的區域對應的目標區域）。

式中要求所有字符包括標點符號全部為英文狀態輸入，否則Excel識別不了會報錯提醒。

3.4.1 軸上鍵槽處d-t處單元格的建立

如第15頁圖6中所示軸徑尺寸d-t（即顯示為的地方）處占用四個原始單元格，基本尺寸占用兩個單元格，合并為一個單元格后設定對齊方式為垂直居中、右對齊方式。上下公差顯示各占用一個單元格。上公差顯示所在單元格設定對齊方式為底端對齊、左對齊。下工單顯示所在單元格設定對齊方式為頂端對齊、左對齊。

基本尺寸d-t處輸入公式為：=Z12-LOOKUP（Z12，B7：B32，O7：O32）。

式中Z12為指軸徑輸入單元格地址即d值；LOOKUP（Z12，B7：B32，O7：O32）為用 lookup 函數查找對應的t值；其中B列為基本尺寸建立的輔助列，O列為對應的t值。

軸徑基本尺寸為d-t的上偏差全部為0，所以此處輸入0即可。

軸徑基本尺寸為d-t的下偏差查表的R列即可得到正確的結果，R列為建立的輔助列，此處輸入的公式為：=LOOKUP（Z12，B7：B32，R7：R32）。

3.4.2 鍵槽寬度單元格的建立

如第15頁圖6中所示軸上鍵槽寬度b（即顯示為的地方）處占用八個原始單元格，為了有更好的顯示效果，部分單元格合并后共占用五個單元格，分別用序號箭頭指出如圖4所示。單元格的對齊方式同3.4.1介紹類似。

箭頭1指單元格即鍵槽寬度基本尺寸b處公式：=LOOKUP（Z12，B7：B32，E7：E32）；

圖4 單元格占位

箭頭2指單元格輸入固定值：N9（；

箭頭 3 指單元格輸入：=LOOKUP（Z12，B7：B32，K7：K32），其中K列為鍵槽上偏差輔助列；

箭頭 4 指單元格輸入：=LOOKUP（Z12，B7：B32，L7：L32），其中L列為鍵槽下偏差輔助列；

箭頭5指單元格輸入固定值：）；

按以上建立公式連接后只要改變Z12單元格即軸徑的值，便可自動顯示其他尺寸及公差值。

3.4.3 其他單元格的建立

同理按照軸鍵尺寸及公差的公式建立方法建立輪轂的尺寸及公差。

3.5 單元格保護、保護工作表

3.5.1 只允許軸徑單元格可輸入數據，其他單元格禁止改動

將整個表格全選后，任意單元格點擊右鍵后選擇設置單元格格式，然后點擊保護選項卡，將選項卡中的鎖定和隱藏全部選中打鉤后保存。

然后選中Z12單元格即輸入軸徑單元格，右鍵設置單元格格式保護選項卡將其中的鎖定取消，只選擇隱藏選項，點擊確定保存。

點擊菜單欄的審閱選項卡后選擇保護工作表彈出圖5所示對話框，按圖示選項選擇后輸入密碼點擊確定即可，沒有權限的人員只可以瀏覽使用，不能更改任何內容。

3.5.2 美化

為了界面的美觀，可以將視圖選項卡中的標題欄、網格線、標題全部把前面的√取消，實現隱藏，最終效果如圖6所示。

4 結語

本程序功能簡單易用，可視化效果好，查詢結果一目了然，省去了繁瑣的查表計算環節，已經在技術員設計工作中推廣使用，得到了大家的一致好評。

圖5 保護工作表選項卡

圖6 系統設計的最終效果部分截圖

Excel功能全，操作方便，具有豐富的函數功能，方便的數據交換功能，可以自動創建各種數據圖表，自動計算。將繁瑣的計算簡單化、準確化、快速化。

Excel的高級應用中配合VBA的使用，個性化進行功能定制，開發自定義命令按鈕，可以做很多大型的計算及程序的開發。利用excel.vba編寫的宏命令及程序會有事半功倍的效果[1]。