用EXCEL解析集裝箱裝載EDI數(shù)據(jù)文件

朱育良

摘要：本文介紹利用MICROSOFT OFFICE中EXCEL電子表格程序從EDI文件中解析提取有關(guān)集裝箱有關(guān)信息，并進行分類匯總。

關(guān)鍵詞：EXCEL電子表格 EDI數(shù)據(jù) 數(shù)據(jù)解析 VBA SQL語句 分類匯總

0 引言

隨著集裝箱運輸行業(yè)發(fā)展，并持續(xù)穩(wěn)定增長，集裝箱國際貿(mào)易進出口總額不斷增加，隨之而來的是作為國際貿(mào)易貨物運輸?shù)闹饕绞街坏募b箱運輸量的不斷增加。為了抓住機遇并且迎接挑戰(zhàn)，各航運公司加速發(fā)展集裝箱運輸船隊，并逐步向大型化發(fā)展，為了應(yīng)盡可能地提高集裝箱運輸中每個環(huán)節(jié)的效率。本文以此為契機，研究由口岸、代理、公司提供給船方的集裝箱電子數(shù)據(jù)文件（EDI），通過解析集裝箱電子數(shù)據(jù)文件（EDI），直接提取集裝箱相關(guān)信息，包括積載位置、裝卸港、貨物重量和貨物的一些特殊信息（如危險品、冷藏箱、超尺寸箱等），提高配載效率和質(zhì)量，減輕船方使用船員的工作負擔。

集裝箱船舶的大型化，單港集裝箱的裝卸量大大增加，以往只能被動地等待碼頭方提供的各種所需數(shù)據(jù)，而且再次手動輸入計算機進行文件編輯和傳輸，其間費時費力并且容易出現(xiàn)錯誤，本文介紹一種利用EXCEL中VBA編程功能，自動從港方提供的EDI文件中提取集裝箱有關(guān)信息數(shù)據(jù)。

1 EDI簡介

EDI是英文Electronic Data Interchange的縮寫。聯(lián)合國歐經(jīng)會貿(mào)易程序簡化工作組（UN/ECE/WP.4）于1994年9月23日在日內(nèi)瓦舉行的第40屆會議上通過了EDI的技術(shù)定義。國際標準化組織（ISO）聯(lián)席會議同年接受了這一定義：

Electronic Data Interchange（EDI）：The electronic transferor computer to computer of commercial or administrative transactions using as agreed standard to structured the transaction 0r message data。中文譯為：電子數(shù)據(jù)交換（EDI）：是使用一種商定的標準來處理所涉及的交易或信息數(shù)據(jù)的結(jié)構(gòu)，商業(yè)或行政交易事項，從計算機到計算機的電子傳遞。

目前被廣泛使用的EDI格式國際標準主要有兩個：UN/EDIFACT和ANSIX12，現(xiàn)在各口岸、代理、貨運相關(guān)方一般采用UN/EDIFACT標準格式，常用的版本為V1.5和V2.0兩個版本（2016年7月1日起實行VGM規(guī)則，BAPLIE采用2.2版本）。本文以BAPLIE（Bayplan/stowage plan occupied and empty locations message）V2.0為介紹主體。BAPLIE國際標準文件可在http：//www.smdg.org/官方網(wǎng)站下載。

2 EDI結(jié)構(gòu)分析

一個標準的EDI數(shù)據(jù)根據(jù)其內(nèi)容的功能和性質(zhì)又可分為三個區(qū)：標題區(qū)：說明報文的性質(zhì)；明細區(qū)：詳細說明報文內(nèi)容；結(jié)尾區(qū)：核對并結(jié)束報文；EDI標準由數(shù)據(jù)元、數(shù)據(jù)段和報文三要素組成的。多個數(shù)據(jù)元組成數(shù)據(jù)段，多個數(shù)據(jù)段組成報文。數(shù)據(jù)元是報文中的最基本的單位。兩個或兩個以上的數(shù)據(jù)元構(gòu)成一個復(fù)合數(shù)據(jù)元，而此時的數(shù)據(jù)元稱為子數(shù)據(jù)元。

一個數(shù)據(jù)段包括標識符唯一標識，段標識符由3個大寫字母組成（屬必需的M），數(shù)據(jù)元通過其在段中的位置來識別，數(shù)據(jù)元由“+”分隔，以單引號（）為數(shù)據(jù)段結(jié)束符。一份完整UN/EDIFACT MESSAGE結(jié)構(gòu)如下：

本文所關(guān)心的是有關(guān)集裝箱的信息，包括集裝箱裝載位置，重量、裝卸港、集裝箱的特殊信息等，撇開報頭、報尾其他信息，著重解析集裝箱數(shù)據(jù)。

一份完整的EDI數(shù)據(jù)應(yīng)由UNH段、BGM段、DTM段、RFF段、NAD段、記錄組1、記錄組2和UNT段組成、（記錄組1又是由TDT段、LOC段、DTM段、RFF段和FTX段組成，記錄組2由LOC段、GID段、GDS段、FTX段、MEA段、DIM段、TMP段、RNG段、LOC段、RFF段）、記錄組3和記錄組4組成。（記錄組3由EQD段、EQA段和NAD段組成，記錄組4由DGS段和FTX段組成。）

每個集裝箱信息以標識符LOC+147+開始，一個集裝箱的具體數(shù)據(jù)按LOC（M）-GDS（C）-FTX（C）- MEA（M）-DIM（C）- TMP（C）-RNG（C）-LOC（M）-RFF（C）順序（），下面以一個實例說明。

（備注：V1.5 為EDI-1.5版本，V2.0為EDI-1.5版本，V2.2為今年7月1日實行集裝箱稱重后啟用的EDI-2.2版本，各版本的標識符是一致的。）

其中第1行至第8行為EDI的報頭段，包括EDI的版本，船舶信息、碼頭信息及到離港信息，第9行至等28行為2個集裝箱的完整信息，……表示其中省略了N個集裝箱信息，第18421行至第18422行為文件結(jié)束段，本文有用的數(shù)據(jù)是第9行至第18420行的每個集裝箱數(shù)據(jù)信息。

每個集裝箱數(shù)據(jù)信息從LOC+147 集裝箱裝載位置開始，包括積載位置（LOC+147）、貨物重量（MEA+WT或MEA+VGM），裝貨港（LOC+6）、卸貨港（LOC+12）、承運人（NAD+CA），集裝箱信息（EQD+CN，箱號、箱型、空/重）及特殊箱子（危險品（DGS）、冷藏箱（TMP）、超尺寸箱（DIM））等。

3 EXCEL解析過程

從上面的EDI信息分析看，集裝箱數(shù)據(jù)按標識符有順序的排列，我們就可以利用EXCEL中VBA編程功能，從EDI文件中解析出我們想知道的集裝箱數(shù)據(jù)，解析過程按下面流程圖進行（圖3-2）。

VBA是MICROSOFT OFFICE辦公軟件集成的功能，是MICROSOFT VISUAL BASIC的簡版，包含了VB的極大數(shù)功能。具有極強編程功能和執(zhí)行力，與MICROSOFT OFFICE無縫銜接。

（1）工作表設(shè)計，新建一個EXCEL工作薄，并新建3個工作表，第一個工作表命名為“EDI”， 第二個工作表命名為“清單”， 第三個表命名為 “匯總表”。“EDI表”用于存放EDI原文，“清單”用于存放已解析出來的集裝箱信息。 “匯總表”用于存放集裝箱數(shù)量統(tǒng)計。

EDI原文以單引號（）為數(shù)據(jù)段結(jié)束符，每一行為一個數(shù)據(jù)段，在“EDI表”的A列用來存放EDI數(shù)據(jù)，在B1放入“=COUNTA（A：A）”公式，用于計算EDI的總行數(shù)。

第二個工作表“清單”的第一行分別填入“Data、ID、SIZE、WT、POL、POD、E、DG、CLASS、UNNO、RF、TEMP、UNIT”用來存放集裝箱信息。（圖3-1）

表“匯總表”可以設(shè)計成如圖3-3的格式。

（2）VBA代碼編寫：VBA編輯器可用快捷鍵Alt-F11打開，VBA界面分為工程資源管理器、屬性窗口、代碼窗口，代碼窗口內(nèi)是輸入VBA代碼的區(qū)域。

4 功能使用：冷藏箱、危險品制作

如果要制作冷藏箱清單，按裝貨港、卸貨港分別分類統(tǒng)計制作，可插入新的工作表，命名為RF，表格可按體系文件格式設(shè)計成如圖4-1的式樣。

如果要制作危險品清單，可將上述代碼略作修改，就可完成，讀者自行編輯。

另外，EXCEL本身具體很強的分析統(tǒng)計功能，讀者可以通過使用分類統(tǒng)計、篩選、排序等功能，獲得各種報表。

5 總結(jié)

EDI技術(shù)己經(jīng)在集裝箱運輸?shù)暮芏喹h(huán)節(jié)得到應(yīng)用，本文是以EXCEL程序為依托，將解析的電子數(shù)據(jù)文件（EDI）應(yīng)用于實際，逐步實現(xiàn)各部分功能。首先對EDI解析的原理和系統(tǒng)結(jié)構(gòu)及EDI國際標準進行充分的了解，對真實船舶的電子數(shù)據(jù)文件（EDI）文件按照UN/EDIFACT標準進行解析，最終應(yīng)用于實際運用完成集裝箱信息的提取。在實現(xiàn)過程中遇到的主要問題和解決方法如下：

（1）電子數(shù)據(jù)文件（EDI）文件解析問題。通過對電子數(shù)據(jù)文件（EDI）文件進行結(jié)構(gòu)分析和語句分析并結(jié)合實例逐步分析電子數(shù)據(jù)文件（EDI）文件的解析過程，完成了電子數(shù)據(jù)文件（EDI）文件解析。最終將上述解析過程通過VBA編程實現(xiàn)之。

（2）制作各種報表，減輕工作強度，減少輸入誤差；

（3）現(xiàn)在每次靠泊前，代理、口岸都會將預(yù)配載圖報文EDI文件發(fā)船，船方可根據(jù)配載圖報文EDI文件，預(yù)先制作出到港裝卸作業(yè)用的各種清單。

（4）本文僅起到拋磚引玉的作用，更多的功能讀者可以自行開發(fā)、擴展。

參考文獻：

[1]李馥佳.EDI在集裝箱配載中的應(yīng)用[D].大連海事大學(xué)，2010，06.

[2]MICROSOFT OFFICE 幫助文檔.

[3]UN/ECE WP.4，UN/EDIFACT Syntax Implementation Guidelines.l993.

[4]董延丹.EXCEL函數(shù)在集裝箱業(yè)務(wù)中的應(yīng)用[J].物流技術(shù)，2013，32（3）.