巧用excel繪制顆粒級配曲線與自動計算粒組特征參數(shù)

1、巧用excel繪制顆粒級配曲線與自動計算粒組特征參數(shù)摘要：顆粒分析試驗為基礎(chǔ)土工試驗之一，其成果對土樣定名及物理力學(xué)性質(zhì)的判斷都有著重要意義。由于試驗原始數(shù)據(jù)繁多，處理步驟繁雜，而以往試驗室對顆分?jǐn)?shù)據(jù)多為人工處理，導(dǎo)致顆粒分析數(shù)據(jù)處理工作量大，且結(jié)果易出錯。利用excel的函數(shù)和圖表功能，可實現(xiàn)顆粒分析試驗從原始數(shù)據(jù)計算、顆粒級配曲線繪制到粒組特征參數(shù)計算的數(shù)據(jù)自動處理，減少人為影響，提高工作效率及計算準(zhǔn)確度。關(guān)鍵字：顆粒級配曲線；粒組特征參數(shù)；自動計算1.顆粒分析試驗數(shù)據(jù)處理的基本流程顆粒分析是通過測定干土中各粒組所占該土總質(zhì)量的百分?jǐn)?shù)的方法，借以明了顆粒大小分布情況，供分類土性、判斷土的工

2、程性質(zhì)及選料之用1，其基本原理參見文獻(xiàn)1、2。本文僅討論篩析法聯(lián)合密度計法的顆粒分析自動化數(shù)據(jù)處理。其數(shù)據(jù)處理分為五個基本步驟：篩析法計算0.075mm以上顆粒大小及含量密度計法計算0.075mm以下顆粒大小及含量繪制顆粒級配曲線從顆粒級配曲線上讀取粒組特征參數(shù)和粒徑含量的輔助數(shù)據(jù)（粒徑0.05mm,0.005mm,0.002mm所對應(yīng)的含量百分比）計算粒徑含量表。試驗成果包括顆粒級配曲線，粒組特征參數(shù)（d10，d30，d50，d60，不均勻系數(shù)Cu，曲率系數(shù)Cc）及粒徑含量表。2.目前顆粒分析電化處理數(shù)據(jù)的狀況隨著計算機(jī)的普及，目前很多試驗室已經(jīng)開始用軟件來完成顆粒分析數(shù)據(jù)的電化處理，常見的

3、主要是辦公軟件Excel34和繪圖軟件AutoCAD5。據(jù)筆者了解，目前試驗室用Excel處理顆粒分析數(shù)據(jù)的方式還主要局限于常規(guī)計算和繪制顆分曲線34，而粒組特征參數(shù)及粒徑含量的輔助數(shù)據(jù)仍采用人工讀取，這樣的做法導(dǎo)致數(shù)據(jù)精度因人而異，且結(jié)果易發(fā)生量級錯誤；用AutoCAD處理顆粒分析數(shù)據(jù)，雖已通過編制AtuoLISP程序自動計算出粒組特征參數(shù)，但由于AutoCAD的優(yōu)勢在于圖形處理而非數(shù)據(jù)處理，因此還要利用Excel表格進(jìn)行數(shù)據(jù)前期準(zhǔn)備，然后用AtuoLISP在AutoCAD中進(jìn)行繪圖和簡單計算5。3.新的解決思路Excel能夠較為方便地完成步驟的計算及繪圖，主要的困難是如何實現(xiàn)第步的模塊化。

4、鑒于圖表的數(shù)據(jù)源是兩列相關(guān)的數(shù)組，因此考慮直接通過尋找兩列數(shù)組的關(guān)系，用數(shù)值擬合的方式來自動求解特征參數(shù)和輔助數(shù)據(jù)。文獻(xiàn)3提出了用方程法（對數(shù)曲線）計算和插值法計算的兩種思路，考慮到有些曲線運用對數(shù)曲線求解誤差較大，且實際試驗過程中采集的數(shù)據(jù)點足夠多，顆粒級配曲線上兩點間線性關(guān)系較好，因此本文依據(jù)插值法來編制自動處理顆粒分析數(shù)據(jù)模塊。本次列舉的Excel方案分三個板塊：(1)原始數(shù)據(jù)輸入，(2)顆粒大小計算及顆粒級配曲線繪制,(3)粒組特征參數(shù)及粒徑含量計算。本文僅討論后兩個計算板塊。3.1顆粒大小計算及顆分曲線繪制主要計算區(qū)分三部分，第一部分為篩析法顆粒大小及含量計算；第二部分為密度計法顆粒

5、大小及含量計算，第三部分為粒徑含量計算。計算公式可依照參考文獻(xiàn)1、2的基本原理編制，也可以套用參考文獻(xiàn)6示例的Excel公式，本文不再累敘。建議密度計法所需要的分散劑校正值、溫度校正值及液體動力粘滯系數(shù)單獨建立數(shù)據(jù)庫，計算時用vlookup函數(shù)索引。將篩析法和密度計法的計算結(jié)果，即“粒級”及“小于該粒徑的總土質(zhì)量百分?jǐn)?shù)”，通過“插入”-“圖表”-“散點圖” 繪制曲線，數(shù)據(jù)X對應(yīng)“粒徑”，數(shù)據(jù)Y對應(yīng)“小于該粒徑的總土質(zhì)量百分?jǐn)?shù)”。在“設(shè)置坐標(biāo)格式”中，將X坐標(biāo)選擇為“對數(shù)刻度（基底為10）”和“逆序刻度”。3.2粒組特征參數(shù)及輔助數(shù)據(jù)自動計算用Excel來實現(xiàn)插值法，筆者的基本思路是：對已知對應(yīng)

6、的兩組數(shù)列X和Y，通過X查Y，則先利用index、match函數(shù)查找X前、后數(shù)據(jù)X1、X2，以及彼此對應(yīng)的Y1、Y2，然后用trend函數(shù)插值得Y。例如要自動計算控制性參數(shù)d60（即X=60%,Y表示累計含量為60%時對應(yīng)的粒徑值），顆粒 “粒徑”在表格A1:N1區(qū)間，其對應(yīng)“小于該粒徑的總土質(zhì)量百分?jǐn)?shù)”在表格A2:N2區(qū)間，則首先可在X1對應(yīng)的表格A3中輸入“=INDEX(A2:M2,MATCH(60,A2:M2,1)”，在X2對應(yīng)的表格A4中輸入“=INDEX(A2:M2,MATCH(60,A2:M2,1)+1)”；在Y1對應(yīng)的表格A5中輸入“=INDEX(A1:M1,MATCH(60,A

7、2:M2,1)”，在Y2對應(yīng)的表格A6中輸入”=INDEX(A1:M1,MATCH(60,A2:M2,1)+1)”，最后在Y對應(yīng)的表格A7中輸入“=TREND(A5:A6,A3:A4,60)”即可得最終插值結(jié)果。要計算輔助性參數(shù)中粒徑0.05mm所對應(yīng)的含量百分比，則可在X1對應(yīng)的表格B3中輸入“=INDEX(A1:M1,MATCH(0.05,A1:M1,1)”，在X2對應(yīng)的表格B4中輸入“=INDEX(A1:M1,MATCH(0.05,A1:M1,1)+1)”；在Y1對應(yīng)的表格B5中輸入“=INDEX(A2:M2,MATCH(0.05,A1:M1,1)”，在Y2對應(yīng)的表格B6中輸入“=IND

8、EX(A2:M2,MATCH(0.05,A1:M1,1)+1)”，最后在Y對應(yīng)的表格B7中輸入“=TREND(B5:B6,B3:B4,0.05)”即可得最終插值結(jié)果?？刂菩詤?shù)為最終提交數(shù)據(jù)，輔助性數(shù)據(jù)將被引用到3.1節(jié)提到的粒徑含量計算中。4.結(jié)語Excel擁有強(qiáng)大的函數(shù)計算和繪圖功能，利用其建立的表格系統(tǒng)是完全可以將數(shù)據(jù)處理模板化，試驗人員僅需要通過簡單培訓(xùn)，準(zhǔn)確填入原始數(shù)據(jù)，即可獲得準(zhǔn)確的處理結(jié)果，既節(jié)省計算時間，又減少人為誤差，所以在未來的工作中，可積極推廣Excel函數(shù)在試驗數(shù)據(jù)處理中的運用。需注意的是，使用函數(shù)自動處理試驗數(shù)據(jù)時，須仔細(xì)檢驗公式的有效性，不可盲目套用，畢竟函數(shù)存在適用條件，而試驗狀況靈活多變，公式編輯不可能一勞永逸。參考文獻(xiàn)1SL237-1999.土工試驗規(guī)范.S.2GB50123-1999.土工試驗方法標(biāo)準(zhǔn).S.3潘福營.利用excel自動繪制顆粒級配曲線的方法.J.四川水力發(fā)電. 2010.V29.NO3.48-49.4羅小芳.巧用excel軟件處理顆粒分析數(shù)據(jù)并繪制曲線圖.J.西部探礦工程. 2006.NO126. 319-320