Excel軟件在日常辦公中的實戰(zhàn)技巧應(yīng)用

王景珊

(景德鎮(zhèn)學(xué)院,江西 景德鎮(zhèn) 333000)

0 引言

談到Excel軟件的應(yīng)用,人們更多的會想到財務(wù)工作人員,Excel內(nèi)置的數(shù)學(xué)、財務(wù)、統(tǒng)計等多種函數(shù),及數(shù)據(jù)管理與分析等多種方法和工具,確實能夠滿足財務(wù)日常工作需要。隨著辦公自動化的普及,Excel軟件得到廣泛應(yīng)用。以下為部分實戰(zhàn)技巧案例,考慮到運行截圖不占用太多文章空間,采用較少數(shù)據(jù)描述操作過程,實際工作中當(dāng)數(shù)據(jù)較多時,操作步驟是相同[1]。

1 制作隨機抽樣名單

單位行政管理部門如果有重要的隨機抽樣調(diào)查,或是隨機抽取獲獎名單等類似活動時,工作人員不需要編寫程序完成,Excel軟件就提供了相應(yīng)的函數(shù),幫助工作人員快速完成此項任務(wù),以下以隨機抽獎為例,講解制作隨機抽樣名單(假設(shè)抽取3名獲獎人員)的過程,其步驟如下。

輸入需要隨機抽取的所有名單(本案例參與抽獎人員名單放置在c3:c11)——假設(shè)此次抽取3名獲獎人員——利用INDEX函數(shù)和RANDBETWEEN函數(shù)——分別在D3,D6,D9輸入公式——公式內(nèi)容為:=INDEX($C$3:$C$11,RANDBETWEEN(1,9))——按住F9(鍵盤)不放即可不斷隨機產(chǎn)生不同的名單——直到喊停確定最后的獲獎名單。

INDEX函數(shù)主要用于數(shù)據(jù)引用,其格式為INDEX(array,row_num,[column_num]),功能是返回表中給定區(qū)域(第1個參數(shù)),指定行和列(第2,3個參數(shù))位置的值,則行、列交叉位置的值就是我們需要獲取的值,如果第1個參數(shù)給定的區(qū)域為一行(或一列)的線性結(jié)構(gòu),則后面只需要指定列(或行)一個位置即可。RANDBETWEEN函數(shù)則是產(chǎn)生指定范圍的隨機數(shù),其格式為RANDBETWEEN(bottom,top),bottom是范圍的左邊界整數(shù),top是范圍的右邊界整數(shù),結(jié)果是返回bottom與top之間的隨機整數(shù)[2]。

本案例輸入的公式是INDEX($C$3:$C$11,RANDBETWEEN(1,9)),INDEX函數(shù)的第1個參數(shù)$C$3:$C$11為線性結(jié)構(gòu),一列9行數(shù)據(jù),給定了參與抽獎人員名單,INDEX函數(shù)的第2個參數(shù),則使用了RANDBETWEEN(1,9)隨機產(chǎn)生1~9之間的數(shù)字,所以在D3,D6,D9三個單元格,輸入公式=INDEX($C$3:$C$11,RANDBETWEEN(1,9)),就能夠在9名參與人員中,隨機獲取3名獲獎人員名單,按住鍵盤功能鍵[F9]不放,隨機數(shù)會不斷變化,與我們平時在電視屏幕看到的抽獎過程一樣,數(shù)據(jù)不斷在變化,直到喊停,鎖定最后結(jié)果。

2 批量圖片鏈接

如果工作人員在某個時刻需要收集的數(shù)據(jù)中含有大量圖片信息,處理的方法有多種,比如把這些圖片全部直接插入到Excel表中,理論上是可行的,但會占用表格空間。若采用圖片鏈接的方式插入圖片,數(shù)據(jù)表格清晰簡潔,感覺會比較好。但采用什么方法插入超鏈接呢,如果圖片數(shù)量少,可以把圖片一張一張的插入超鏈接,當(dāng)數(shù)量較多,多到成百上千,一張一張的插入則費時費力,所以在實際工作中最好的方式是選擇批量處理的方法插入鏈接,工作效率非常高。本案例只講解批量圖片鏈接的方法,忽略實際工作中其他數(shù)據(jù),并使用6張圖片講解批量圖片鏈接過程,圖片存放在E:photo,具體步驟如下。

2.1 批量獲取圖片文件名

(1)在E:photo新建一個文本文件(擴展名為.txt),主文件名任意,本案例取名tupian.txt。

(2)文本文件tupian.txt的內(nèi)容:dir/b/n/on>list.txt,可以把主文件名list改用其他名稱。作用是將當(dāng)前文件夾的文件名列舉出來,并寫入到list.txt文件中。

(3)將文本文件tupian.txt擴展名修改為.bat,此時會彈出一個對話框,點擊確定更改即可,保存后會產(chǎn)生一個list.txt文件。

(4)雙擊打開list.txt文件,就會發(fā)現(xiàn)E:photo文件夾里所有圖片文件的文件名瞬間全部獲取,自動放置在list.txt文件中。

2.2 批量設(shè)置超鏈接

設(shè)置超鏈接,主要借助HYPERLINK函數(shù)完成。

(1)把list.txt文件獲取到的文件名復(fù)制到Excel表格A2:A7中。

(2)超鏈接函數(shù)HYPERLINK的格式為HYPERLINK(“路徑文件名”“文件名”),格式中的兩個參數(shù),若使用單元格,則雙引號不需要。

(3)在B2單元格輸入圖片保存路徑E:photo,并復(fù)制到B3:B7。

(4)在C2單元格輸入公式=B2&A2,將文件夾路徑B2與文件名A2用&運算合并,并復(fù)制到C3:C7。

(5)將第(4)步內(nèi)容選擇性粘貼復(fù)制成文本(此時B列數(shù)據(jù)可以刪除,也可以不刪除)。

(6)在D2單元格輸入公式=HYPERLINK(C2,A2),并復(fù)制到D3:D7,這樣使用超鏈接函數(shù)HYPERLINK快速完成所有圖片的鏈接。

(7)單擊D2:D7任意一個單元格,可以鏈接打開所需要的圖片,大大節(jié)約了表格空間,效果如圖1所示。

圖1 插入圖片鏈接

(8)把A,B,C三列的內(nèi)容進行隱藏,只顯示D列的鏈接內(nèi)容即可。

A,B,C三列的內(nèi)容進行隱藏,是因為D列的鏈接需要它們,作為中間數(shù)據(jù)在表格中我們又不需要它們,所以只作隱藏不能刪除。在工作中有類似的不需要顯示的中間數(shù)據(jù),都可以作隱藏處理。

3 批量設(shè)置重要數(shù)據(jù)掩碼

通常一些重要的信息,從安全角度考慮會用掩碼顯示,比如在銀行ATM進行存款或取款時,回執(zhí)單上不會顯示完整的銀行賬號,只會顯示帶有?號的銀行賬號,在工作單位,也有類似的情況,有些數(shù)據(jù)對外是不便公開顯示全部信息,比如職工的身份證號,需要保護職工的個人隱私,每當(dāng)遇到這些情況,可使用REPLACE函數(shù)設(shè)置數(shù)據(jù)的掩碼。REPLACE函數(shù)的功能是將一個字符串中的部分字符用另一個字符串替換,其格式為REPLACE(要替換的字符串,開始位置,替換個數(shù),新的文本),本案例原始的完整身份號碼(放置在F3:F7),添加掩碼的身份證信息,如圖2所示。具體操作過程如下。

圖2 掩碼信息顯示

“學(xué)歷”字段前面增加一列,字段名為“身份證號碼”——在G3單元格輸入公式=REPLACE(F3,9,6,“??????”)——將G3公式復(fù)制到G4;G7——為確保數(shù)據(jù)安全將G3:G7數(shù)據(jù)復(fù)制并在原位置選擇性粘貼文本(可以不做)——將F列數(shù)據(jù)(身份證完整信息)設(shè)置為隱藏。

特別注意一點,添加了掩碼的身份證信息,需要依賴于身份證完整數(shù)據(jù)(放置在F3:F7),所以原始完整信息依然需要保存,否則造成數(shù)據(jù)信息不完整,就拿前面所講的銀行ATM回執(zhí)單,雖然呈現(xiàn)出來的是部分帶?號的銀行賬號,但在銀行的數(shù)據(jù)庫中一定是需要保存完整的銀行賬號的,才能夠正確記錄著每位客戶的收支信息。這也就是最后一個步驟將F列數(shù)據(jù)(身份證完整信息)設(shè)置為隱藏的原因[3]。

4 結(jié)語

Excel軟件的實戰(zhàn)技巧是基礎(chǔ)知識的延伸和拓展,在以后的工作中繼續(xù)努力,將理論和實踐有機結(jié)合在一起,探索更多方便且實用的方法和技巧應(yīng)用于日常工作,提高日常辦公的工作效率。