基于Excel建立項目電子信息倉庫的探究

肖 楠

（中交天航南方交通建設有限公司，廣東 深圳 518000 ）

0 前言

目前，在項目管理中對紙質文件進行管理時，采用的主要方法就是分門別類地將文件放入不同的檔案盒，并對檔案盒進行排序，這就造成了有的檔案盒只有一兩份文件，有的檔案盒有幾百份文件甚至一個檔案盒都放不下的情況。以某建設項目工程部文件為例，進度計劃可能只有十幾份文件，占了檔案盒不到1/3的空間，而由于施工方案的內容多，1個檔案盒只能放下十幾份方案，那么就會存在把剩余方案放入進度計劃的檔案盒里的情況，這無疑是增加了后期查閱文件的困難程度，該問題有待解決。

1 解決方法

從對文件進行分類入手，分類可以根據不同的部門、不同的文件類別以及不同的文件作用對文件進行分類，值得注意的是，由于是使用基礎的Excel函數建立的電子信息倉庫（以下簡稱“倉庫”），分類越細化，工作量就越大。因此，分類以2級～3級目錄為宜。

第一步是建立一級目錄索引和一級文件夾，隨著工作的不斷推進，建立的一級目錄的數量越來越多，在后期查找時會很不方便，于是筆者想到利用Excel的特定函數對兩者進行鏈接，即HYPERLINK函數；同時為了方便逐級向下查找時，在查找頁面可以顯示適量內容，還用到了SMALL、INDEX、IF、VLOOKUP、MATCH以 及ROW等函數進行匹配和輔助；同時為了對表格進行修飾，還用到了IFERROR函數。

為了做到逐級查找，還需要建立1個輔助工作表，并在該工作表中定義多個名稱，定義名稱的數量為1+1級目錄名稱數量+2級非重復子目錄數量+3級非重復子目錄數量+……+n級子目錄名稱數量。

綜上所述，建立倉庫需要具備以下條件：1）總文件臺賬工作表。2）用以搜索文件的工作表。3）名稱管理工作表。4）與臺賬一一對應的文件夾及文件。

2 函數的使用

由于涉及的函數較多，因此該文采用表格對函數進行匯總，見表1。需要注意的是，在建立電子信息倉庫時，多數情況是對函數進行組合使用，因此，為了使讀者便于理解，筆者先從單一函數開始介紹，然后再對組合函數進行介紹。

表1 函數匯總表

2.1 單一函數

2.1.1 HYPERLINK函數

該函數是實現原文件與特定信息進行鏈接的關鍵函數，與“超鏈接”相似，其參數是link_location（鏈接位置）及friendly_name（顯示文本）。

鏈接位置可以是1個單元格、1個工作表、1工作簿、1個外部文件（夾）或者1個網頁；顯示文本是使用者想要表達的信息，例如在某一單元格中（假設為A1）輸入：=HYPERLINK（“#C1”，”施工方案”），按下回車鍵后，C1單元格即處于選中狀態(tài)，如圖1所示。

圖1 HYPERLINK函數演示圖

2.1.2 VLOOKUP函數

該函數是建立倉庫的關鍵函數之一，是根據特定的要求來搜索使用者想要的結果，可以是單個要求或多個要求，其參數是lookup_value（要查找的值，可為數值、引用或文本字符串）、table_array（要查找的區(qū)域）、col_index_num（返回數據在查找區(qū)域的第幾列數，為正整數）以及range_lookup（精確匹配/近似匹配，FALSE（或0）/TRUE（或1或不填））[2]。

該函數作為使用最廣泛的函數之一，其使用難度相對適中。由于對初學者來說該函數的參數相對較多、內容相對晦澀，因此該文只做實例演示，例如在某一單元格中（假設為I5）輸入：=VLOOKUP（H5,$A$2:$F$12,6,0），按下回車鍵后，I5單元格的數據即為H5單元格的內容在左側表格中所對應的數據，對I6、I7以及I8單元格進行快速填充后，就可以出現相應的數據，如圖2所示。

圖2 VLOOKUP函數演示圖

雖然該函數應用廣泛，但是操作起來較為煩瑣，該文推薦更為方便的XLOOKUP函數，XLOOKUP函數僅可以在MICROSOFT 365版本的Excel中使用。

2.1.3 MATCH函數

該函數是建立倉庫的關鍵函數之一，其作用是確定列表中某個數據的位置，與VLOOKUP函數相比，該函數比較容易理解，其參數是lookup_value（需要查找的值，可以為值（數字、文本或邏輯值）或對數字、文本或邏輯值的單元格引用）、lookup_array（要搜索的單元格區(qū)域）以及match_type（可選參數：如果填寫參數-1，那么就返回大于或等于需要查找的值的最小值；如果填寫0，那么就返回等于需要查找的值的第一個值；如果填寫1或不填，那么就返回小于或等于需要查找的值的最大值）[3]。

需要注意的是，在輸入match_type參數時，必須要根據不同參數的要求進行排序：如果輸入1或不填，那么必須要對搜索的單元格區(qū)域中參數的值進行升序排列；如果輸入-1，那么必須要對搜索的單元格區(qū)域中參數的值進行降序排列；如果輸入0，則無排序要求。該參數也是該函數中最重要的參數。例如在某一單元格中（假設為D1）依次輸入：=MATCH（80,B2：B10,0）,=MATCH（80,B2：B10,1）和=MATCH（80,B2：B10,-1），依次按下回車鍵后，D2單元格的數據即為滿足要求的數據所在的行數，如圖3所示。

圖3 MATCH函數演示圖

2.2 組合函數

上述內容均為單一函數，與組合函數相比，單一函數更容易學習、操作，接下來就是對函數進行組合，從而達到期望的目標。

建立倉庫主要使用到2種組合函數。

2.2.1 查找定位函數

該組合函數是顯示使用者想要查找的最后一級子目錄的文件序列，函數如下。

=IFERROR（INDEX（文件總目錄!I：I,SMALL（IF（文件總目錄文件總目錄文件查詢文件查詢（文件總目錄）,4^12）,ROW（A2）））&"",""）

從內到外依次是ROW、IF、SMALL、INDEX以及IFERROR函數，ROW函數對文件總臺賬進行從上到下的排序；IF函數在排好的序列中對要查找的文件進行定位；SMALL函數對非查找的文件進行刪除，僅留下想要查找的文件所在子目錄的文件序列；INDEX函數對查找出的文件序列進行顯示；IFFERROR函數刪除錯誤值。

2.2.2 超鏈接跳轉函數

該組合函數是為所有根據上一組合函數（查找定位函數）而返回的文件序列添加超鏈接，將其超鏈接至文件總臺賬的相應單元格，函數如下。

=IFERROR（HYPERLINK（"#文件總目錄!J"&MATCH（VLOOKUP（,IF（{1,0},文件總目錄3089&文件總目錄，文件總目錄）,2,0）,文件總目錄!J:J，）,VLOOKUP（（{1,0},文件總目錄文件總目錄文件總目錄）,2,0））,""）

從內到外依次是IF、VLOOKUP、MATCH、HYPERLINK以及IFERROR函數，IF函數將上一組合函數返回的文件序列與某一子目錄進行合并，保證文件查找的唯一性；VLOOKUP函數對文件所對應的編號進行匹配；MATCH函數對查找的編號進行行號確定；HYPERLINK函數給返回的行號添加超鏈接；IFFERROR函數刪除錯誤值。

3 定義名稱

定義名稱的目的是為了便于在上千個文件的總臺賬中逐級選擇子目錄，從而對文件進行查找。

定義名稱需要新建1個工作表，把所有目錄名稱編輯在第一行，然后根據目錄級別，以列的方式進行編輯，見表2。

表2為筆者定義的名稱（部分），第一行為目錄及所有子目錄的名稱，第一列為一級子目錄，第二列～第三列為二級子目錄，第四列～第八列為三級子目錄，表格完成后，打開“名稱管理器（快捷鍵為Ctrl+F3）”定義名稱，彈出對話框后，單擊“新建”，根據使用者自己的需要定義名稱。方法是在下一個彈出的對話框中填寫“名稱”及“引用位置”，“名稱”填表內第一行內容，“引用位置”填“=工作表名稱！$A$2:$A$6”。

表2 名稱定義示例

該表有多少列，就需要定義多少個名稱，需要注意的是，填寫“名稱”時，必須要與第一行內容一致，不能加減空格，否則在搜索文件時，就不會出現該子目錄的名稱。

4 文件總臺賬

倉庫已經建立好，最后且最煩瑣的一步就是手動填寫文件名稱并給文件名稱添加外部文件的超鏈接，為了方便添加超鏈接，可以以一定的規(guī)律（數字序列、字母序列等）為原文件命名。

文件總臺賬只能以單一單元格進行填寫，不能合并，如果對表格的外觀有要求，筆者建議在該列前新增一列進行合并，然后再對未合并的一列進行隱藏。

5 結語

建立該倉庫主要是為了方便在項目中后期對全項目生命周期內發(fā)生的紙質文件進行查找，從而提高文件檢索速率，實現對文件的快速審核及檢查。同時也是為了避免內業(yè)資料過多地占用空間及時間。

在計算機普遍用于各企事業(yè)單位的現在，信息化必是未來的發(fā)展方向。