Excel编程核算集装箱箱号在教学中的应用

张明齐,陈 静

(天津海运职业学院,天津市 300457)

一、理论教学

以本专业所开设的《集装箱运输实务》课程为例,集装箱箱号的理论教学课时为2学时,根据教材内容,先行讲述集装箱箱号的理论内容。其教学内容如下:

箱主代码、顺序号和核对号

1.箱主代码:集装箱所有者的代码,由4位大写的拉丁字母表示(国内使用的集装箱用汉语拼音表示);前3位由箱主自己规定,并向国际集装箱局登记;第4位字母为U(使集装箱与其他设备相区别),表示海运集装箱代码,例如中国远洋运输(集团)公司的箱主代码为“COSU”。

3.核对号:用于计算核对箱主代码与顺序号记录的正确性。因此,它与箱主代码中的每一个字母和顺序号中的第一个数字都有直接的关系。核对号一般位于顺序号之后,用一位阿拉伯数字表示,并加方框以醒目,如“COSU 001234”的核对号是2。

核对号是由箱主代码的4位字母与顺序号的6位数字通过以下方式换算而得。具体换算步骤如下:

(1)将表示箱主代码的4位字母转化成相应的数字,字母和数字的对应关系,见表1。

表1 核对号计算箱主代码字母与数字转换表

从表1可以看出,表中去掉了11及其倍数的数字,这是因为后面的计算将把11作为模数。

(2)将前4位字母对应的数字加上后面顺序号的数字,共计10位。例如,以中国远洋运输公司的某集装箱为例,箱主代码与顺序号为:

COSU 001234

对应的数字是:13—26—30—32—0—0—1—2—3—4

(3)采用加权系数法进行计算,计算公式为:

(1) 通过试样单位体积含土量和相应的稳定梯度比Gr值的对比，验证了Gr值能够较为准确地反映排水管壁的淤塞程度，可以此为依据，对排水管淤塞进行更深入的研究。

式中Ci为10个数字中第i个数字。

(4)将S除以模数11,再取其余数,即得核对号。

仍以COSU 001234箱为例:

S=13×20+26×21+30×22+32×23+0×24+0×25+1×26+2×27+3×28+4×29除以11,取余数:3577/11=325……余数为2,所以,核对号为

二、实践教学

通过以上集装箱箱号知识体系的讲授,让同学们都掌握了集装箱箱号的组成原理。但是,在课堂上让每一位同学自己编制一个箱号,然后验算很是费时费力,且教师不易验算同学所做的正确性。为此,笔者在课堂上引进EXCEL进行验算,既快捷又准确,极大地提高了教学质量与效率。以下为笔者编写的Excel求解函数小程序界面。见图1,它不但能校验输入的箱号是否正确,还能快速给出正确的箱号校验码。另外它还有很强的程序容错性,即当输入的箱号信息与规范的箱号标准不同时,它将提示作业人员输入了错误码信息。以下给出了编程的步骤与代码,读者可以按本文介绍的方法操做,检验能否达到预期效果。

图1 核算集装箱箱号的Excel函数小程序界面图

(一)核算箱号Excel函数小程序生成步骤及代码

步骤1:请在程序界面中的红色单元格区域输入要核算的集装箱箱号信息,其它为程序辅助功能区域,在此不做一一详述。

步骤2:在A 6单元格中插入函数代码:IF(65=COD E(A 4),COD E(A 4)-55,IF(AND(COD E(A 4)>=66,COD E(A 4)<=75),COD E(A 4)-54,IF(AND(CODE(A 4)>=76,COD E(A 4)<=85),CODE(A 4)-53,IF(AND(COD E(A 4)>=86,COD E(A 4)<=90),COD E(A 4)-52,“输入不正确”))))。而B 6单元格中输入的函数公式是把A 6单元格公式中的A 4换为B4即可;C6单元格中的公式是把A 6单元格公式中的A 4换为C4即可。本公式的含义是按照本文表1中箱主代码的各个字母转换为对应的等效数值,而且,如果在A 4、B4及C4单元格输入的不是大写英文字母,本小程序就会在A 6、B6及C6单元格中提示“输入不正确”,例如,如果输入小写字母、数值或标点符号均会提示“输入不正确”。

步骤3:在D 6单元格插入函数代码:IF(CODE(D 4)=85,CODE(D 4)-53,“输入不正确”)。其含义是,D 4单元格必须输入大写英文字母“U”,否则在D 6中就会提示“输入不正确”。

步骤4:在E6单元格插入函数代码:IF(AND(E4>=0,E4<=9),E4,“输入不正确”)。F6单元格中的公式是把 E6单元格公式中的 E4换为F4即可,同理可得 G6、H6、I6及J6单元格中的公式。其含义是限制输入的每一位顺序号均在数值0至9之间,否则会提示“输入不正确”。

步骤5:在 K6单元格插入函数代码:IF(M OD(SUM PRODUCT(A 6:J6,A 8:J8),11)=10,0,M OD(SUM PRODUCT(A 6:J6,A 8:J8),11))。其含义是等效数值与相对应的权值分别相乘,并返回乘积之和。然后用所求得的和数对11做取模运算,即返回和数与11相除的余数。很显然,所得余数即为集装箱箱号的校验码,且余数为10时,将返回0,符合实际工作要求。

步骤6:在K8单元格插入函数代码:IF(K6=K4,“箱号正确”,“箱号错误”)。其含义是核对计算所得的集装箱箱号校验码与所输入的校验码是否一致。一致返回“箱号正确”,不一致返回“箱号错误”,至此完成箱号核对工作。

步骤7:完成本文程序界面中相应文字及数值的简单输入工作,至此完成核算集装箱箱号的Excel函数小程序。

(二)核算箱号Excel所用函数介绍

1.用CODE函数将大写字母转化为ASCII代码。在W indow s ASCII字符集中,大写字母A～Z对应的代码范围为65～90。由于等效数值不包括11,22和33,因此采用IF嵌套函数分段实现等效数值的转化:若ASCII代码=65,则ASCII代码减去55;若66≦ASCII代码≦75,则ASCII代码值减去54;若76≦ASCII代码≦85,则ASCII代码减去53;若86≦ASCII代码≦90,则ASCII代码减去52;否则将提示“输入不正确”。

2.用IF函数来判定输入的数值,符合条件返回对应的数值,否则返回错误信息。例如上文步骤4中的E6单元格公式中,如果在E4单元格中输入的数值大于等于数值0且小于等于数值9,则把E4中的数值赋值给E6,否则提示“输入不正确”。而A 6只不过是嵌套的IF函数,其原理完全相同,不再赘述。

3.用SUM PRODUCT函数求等效数值与对应加权系数值乘积之和。

4.MOD函数是利用等效数值与对应权值乘积之和数与11相除,相除以后所得之余数即为集装箱箱号的校验码。

[1]曾凡华.集装箱运输业务 [M].北京:机械工业出版社,2006.

[2]丁嵩冰等.集装箱箱号校验规则及其应用 [J].集装箱化,2006,(02).

[3]王宇楠.高职集装箱运输管理课程教学研究初探 [J].网络财富,2008,(10).