3.2 座位表
案例背景
新生入学后或者每学期开学前，班主任要为本班的学生按照预先设定的座位号来编排两个方向的座位表，分别是学生面向讲台的方向和教师面向学生的方向，以方便学生同时又方便新的任课教师点名。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识：基本的数学四则运算 隐藏网格线
●函数应用：ROW函数、COLUMN函数、INDEX函数和OFFSET函数的应用。
●综述 &符号的应用
最终效果展示
3.2.1创建学生座位号码表
Step 1创建工作簿、重命名工作表
创建工作簿“座位表.xls“，然后将工作表重命名为”学生座位号码表“和”座位表“，并删除多余工作表。
Step 2输入表格标题和原始数据
①光标单击工作表标签”学生座位号码表“在单元格区域A1:B1分别输入字段标题。
②在单元格区域A2:B49输入座位号和对应的学生姓名。
Step3设置单元格格式
①设置A：B列文本居中显示。 ②选中单元格区域A1:B49，设置字号为12.
③选中单元格区域A1:B49，设置表格边框
3.2.2创建座位表
Step1输入“讲台“
①切换到“座位表“工作表，按键先后选中D1和D19单元格输入”讲台“，按 组合键可以同时输入“讲台”
Step2为 “讲台“设置边框
①选中单元格C1，单击菜单“格式”→“单元格”，弹出“单元格格式”对话框，切换到“边框”选项卡，在“线形”框中选择“黑粗”，在“边框”选项框中分别选择“上框”和“斜下框”，单击确定按钮。
②选中单元格D1，单击菜单“格式”→“单元格”，弹出“单元格格式”对话框，切换到“边框”选项卡，在“线形”框中选择“黑粗”，在“边框”选项框中分别选择“上框”和“下框”，单击确定按钮。
③选中单元格E1，单击菜单“格式”→“单元格”，弹出“单元格格式”对话框，切换到“边框”选项卡，在“线形”框中选择“黑粗”，在“边框”选项框中分别选择“上框”和“斜上框”。
④单击确定按钮。至此完成面向学生的座位表的“讲台”的图案设置。
⑤参照上面操作过程设置面向教师的座位表的“讲台”图案设置。
Step3输入正向“座位表”
选中A3：G9单元格区域，输入以下公式：然后按组合键可以在所选区域中同时输入公式。
=INDEX(学生座位号码表!$B:$B,COLUMN()+(ROW()-3)*7+1)&""
Step4输入反向“座位表”
选中A11:G17单元格区域，输入以下公式：然后按组合键可以在所选区域中同时输入公式。
=OFFSET($A$3,6-(ROW()-11),7-COLUMN())
Step5设置单元格格式
①选中单元格区域A1：G19，设置文字“字号”为“12”，“字体“为“宋体”，“对齐”方式为“居中”。 ②按键,先后选择单元格区域A3:G9和A11:G17，为该区域设置边框。
Step6取消网格线显示
单击菜单”工具“→”选项，
②在弹出“选项”对话框中单击“试图”选项卡，然后取消勾选“网格线”复选框。
单击确定按钮关闭对话框。效果如下图所示。
关键知识点讲解
1. COLUMN函数
函数名称：COLUMN
主要功能：返回给定引用的列标。
使用格式：COLUMN(reference)
参数说明：Reference 为需要得到其列标的单元格或单元格区域。
●如果省略 reference，则假定为是对函数 COLUMN 所在单元格的引用。
●如果 reference 为一个单元格区域，并且函数 COLUMN 作为水平数组输入，则函数 COLUMN 将 reference 中的列标以水平数组的形式返回。
●Reference 不能引用多个区域。
应用示例：
2. INDEX函数的数组形式
函数名称：INDEX
主要功能：返回数组中指定单元格或单元格数组的数值 使用格式：INDEX(array,row_num,column_num)。
函数用途：返回列表或数组中的元素值，此元素由行序号和列序号的索引值给定。
INDEX 函数有两种语法形式：数组和引用。数组形式通常返回数值或数值数组，引用形式通常返回引用。当函数 INDEX 的第一个参数为数组常数时，使用数组形式。
参数说明：Array 为单元格区域或数组常量。
●如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选。
●如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。
Row_num 数组中某行的行序号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。
Column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。
函数说明：
●如果同时使用 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。
●如果将 row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。
●Row_num 和 column_num 必须指向 array 中的某一单元格；否则，函数 INDEX 返回错误值 #REF!。
应用示例：
1
2.
注释 示例中的公式必须以数组公式的形式输入。在将示例复制到空白工作表中后，请选中以公式单元格开始的单元格区域 A2:A3，按 F2，再按 组合键。如果公式不是以数组公式的形式输入，则返回单个结果值 2。
案例公式说明：
本例公式为：
A3：G9=INDEX(学生座位号码表!$B:$B,COLUMN()+(ROW()-3)*7+1)&""
这里以C5为例，C5位于第3列的5行，那么这时
COLUMN()+(ROW()-3)*7+1=3+（5-3）*7+1=18
所以公式化简为：
C5= INDEX(学生座位号码表!$B:$B,，18)&““
本例中的公式其各个参数值指定INDEX函数返回“学生座位号码表”工作表的B列中的第18行与“”，即空单元格相与之后的结果。公式后面添加&“”是为了保证当引用单元格为空时，返回结果也为“空“，否则返回“0”。
3.OFFSET函数
函数名称：OFFSET
主要功能：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
使用格式：OFFSET(reference,rows,cols,height,width)
参数说明：Reference 作为偏移量参照系的引用区域。
Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。
Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。
Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。
Height 高度，即所要返回的引用区域的行数。Height 必须为正数。
Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。
函数说明：
●如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。
●如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
●函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。
应用示例：
案例公式说明：
本例中的公式为：
A11:G17=OFFSET($A$3,6-(ROW()-11),7-COLUMN())
这里以C12为例，C12位于第3列的12行，所以公式可化简为;
C12= OFFSET($A$3,5,4)
那么这时本例中的公式其各个参数值指定OFFSET函数返回A3单元格靠下5行靠右4列的1行1列即E8的值。
扩展知识点讲解
1. ROWS函数
函数名称：ROWS
主要功能：返回引用或数组的行数。
使用格式：ROWS(array)
参数说明：Array 为需要得到其行数的数组、数组公式或对单元格区域的引用。
应用示例：
2. INDEX函数的引用形式
函数名称：INDEX
主要功能：返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一连续区域。引用形式通常返回引用。
使用格式：INDEX(reference,row_num,column_num,area_num)
参数说明：reference 对一个或多个单元格区域的引用。
●如果为引用输入一个不连续的区域，必须用括号括起来。
●如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,,column_num)。
Row_num 引用中某行的行序号，函数从该行返回一个引用。
Column_num 引用中某列的列序号，函数从该列返回一个引用。
Area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。
●例如，如果引用描述的单元格为 (A1:B4,D1:E4,G1:H4)，则 area_num 1 为区域 A1:B4，area_num 2 为区域 D1:E4，而 area_num 3 为区域 G1:H4。
函数说明：
●在通过 reference 和 area_num 选择了特定的区域后，row_num 和 column_num 将进一步选择指定的单元格：row_num 1 为区域的首行，column_num 1 为首列，以此类推。函数 INDEX 返回的引用即为 row_num 和 column_num 的交叉区域。
●如果将 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整个列或行的引用。
●Row_num、column_num 和 area_num 必须指向 reference 中的单元格；否则，函数 INDEX 返回错误值 #REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。
●函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数 INDEX 的返回值可以作为引用或是数值。例如，公式 CELL("width",INDEX(A1:B2,1,2)) 等价于公式 CELL("width",B1)。CELL 函数将函数 INDEX 的返回值作为单元格引用。而在另一方面，公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。
应用示例：
关键知识点讲解
1. INDIRECT函数
函数名称：INDIRECT
主要功能：返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用函数 INDIRECT。
使用格式：INDIRECT(ref_text,a1)
参数说明：
Ref_text 为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!。
●如果 ref_text 是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。
A1 为一逻辑值，指明包含在单元格 ref_text 中的引用的类型。
函数说明：
●如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
●如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。
应用示例：
当在创建公式时，对某个特定单元格进行了引用。如果使用“剪切”命令，或是插入或删除行或列使该单元格发生了移动，则单元格引用将被更新。如果需要使得无论单元格上方的行是否被删除或是单元格是否移动，都在公式保持相同的单元格引用，请使用 INDIRECT 工作表函数。例如，如果需要始终对单元格 A10 进行引用，请使用下面的语法：
=INDIRECT("A10")
2.VLOOKUP函数
函数名称：VLOOKUP
主要功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
在 VLOOKUP 中的 V 代表垂直。
使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数说明：
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。
●如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。
●通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。
●Table_array 的第一列中的数值可以为文本、数字或逻辑值。
●文本不区分大小写。
Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值 #VALUE!；如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。
函数说明：
●如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。
●如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。
●如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。
应用示例：
在查找数据中，VLOOKUP函数是一个非常有用的函数，它的作用在于可以把不同工作表里的内容引用到同一张工作表里来，在后面的学习中还将通过其他案例介绍VLOOKUP函数的使用方法。
案例公式解析1
本例中关于INDIRECT函数的公式为：
=VLOOKUP($A4,INDIRECT("'["&C$2&"成绩表.xls]初三2班'!$A:$E"),MOD(COLUMN(),3)+3,0)
因为是C4单元格所以：
["&C$2&"成绩表.xls]
可以化简为：
[语文成绩表.xls]
因此，原公式可以化简为：
=VLOOKUP($A4,INDIRECT("'[语文成绩表.xls]初三2班'!$A:$E"),MOD(COLUMN(),3)+3,0)
其各个参数值指定INDIRECT函数返回从“语文成绩表”工作簿中“初三2班”工作表的
案例公式解析2
本案例中关于MOD函数的公式为：
= MOD(COLUMN(),3)
因为是C4单元格，所以公式可化简为：
= MOD(3,3)
其各个参数值指定MOD函数返回该单元格所在列数3除以3后的余数，此处的结果为“0”
案例公式解析3
本例中关于VLOOKUP函数的公式为：
C4= VLOOKUP($A4,INDIRECT("'[语文成绩表.xls]初三2班'!$A:$E"),MOD(COLUMN(),3)+3,0)
我们将上述两个公式的计算结果带入此公式，该公式可化简为：
C4= VLOOKUP($A4,INDIRECT("'[语文成绩表.xls]初三2班'!$A:$E"),3,0)
其各个参数值指定VLOOKUP函数首先在源工作簿“语文成绩表”的“初三2班”工作表的A：E列单元格区域精确匹配地查找A4单元格，也就是“2004152400”，发现位于A2单元格，然后返回同行（即第2行）的第三列（即C列）返回值“100”。
扩展知识点讲解
HLOOKUP函数
函数名称：HLOOKUP
主要功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。当比较值位于要查找的数据左边的一列时，请使用函数 VLOOKUP。
HLOOKUP 中的 H 代表“行”。
使用格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
参数说明：
Lookup_value 为需要在数据表第一行中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。
●Table_array 的第一行的数值可以为文本、数字或逻辑值。
●如果 range_lookup 为 TRUE，则 table_array 的第一行的数值必须按升序排列：...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数 HLOOKUP 将不能给出正确的数值。如果 range_lookup 为 FALSE，则 table_array 不必进行排序。
●文本不区分大小写。
●可以用下面的方法实现数值从左到右的升序排列：选定数值，在“数据”菜单中单击“排序”，再单击“选项”，然后单击“按行排序”选项，最后单击“确定”。在“排序依据”下拉列表框中，选择相应的行选项，然后单击“升序”选项。
Row_index_num 为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。如果 row_index_num 小于 1，函数 HLOOKUP 返回错误值 #VALUE!；如果 row_index_num 大于 table-array 的行数，函数 HLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_value 为 FALSE，函数 HLOOKUP 将查找精确匹配值，如果找不到，则返回错误值 #N/A!。
函数说明：
●如果函数 HLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于 lookup_value 的最大值。
●如果函数 HLOOKUP 小于 table_array 第一行中的最小数值，函数 HLOOKUP 返回错误值 #N/A!。
应用示例：
3.3.4 Excel中的公式操作
公式是在工作表中对数据进行分析的等式。它可以对工作表数值进行加法、减法或乘法等运算。还可以引用同一工作表中的其他单元格、同一工作薄不同工作表中的单元格、或者其它工作薄的工作表中的单元格。 公式由运算符、常量、单元格引用值、名称和工作表函数等元素构成。
1. 运算符 运算符用来对公式中的各元素进行运算操作。Excel包含算术运算符、比较运算符、文本运算符和引用运算符4种类型。（P108） ⑴算术运算符：算术运算符用来完成基本的数学运算，如加法、减法和乘法。算术运算符有十（加）、一（减）、＊（乘）、／（除）、%（百分比）、＾（乘方）。 ⑵比较运算符：比较运算符用来对两个数值进行比较，产生的结果为逻辑值True（真）或False（假）。比较运算符有＝（等于）、＞（大于）、＜（小于）、＞＝（大于等于）、＜＝（小于等于）、＜＞（不等于）。 ⑶文本运算符：文本运算符"＆"用来将一个或多个文本连接成为一个组合文本。例如"Micro"＆"soft"的结果为"Microsoft"。 ⑷引用运算符：引用运算符用来将单元格区域合并运算。引用运算符为： 区域（冒号），表示对两个引用之间，包括两个引用在内的所有区域的单元格进行引用，例如，SUM（BI：D5）。 联合（逗号），表示将多个引用合并为一个引用，例如，SUM（B5，B15，D5，D15）。
交叉（空格），表示产生同时隶属于两个引用的单元格区域的引用。
2. 运算符的运算顺序 如果公式中同时用到了多个运算符，Excel将按下面的顺序进行运算： 如果公式中包含了相同优先级的运算符，例如公式中同时包含了乘法和除法运算符，Excel将从左到右进行计算。 如果要修改计算的顺序，应把公式需要首先计算的部分括在圆括号内。 公式中运算符的顺序从高到低依次为：（冒号）、（逗号）、（空格）、负号（如一l）、％（百分比）、＾（乘幂）、＊和／（乘和除）、十和一（加和减）、＆（连接符）、比较运算符。
3.日期和时间的运算 Excel将日期存储为一系列连续的序列数，而将时间存储为小数，因为时间被看作“天”的一部分。日期和时间都是数值，因此它们也可以进行各种运算。例如，如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。如果通过将单元格的格式设置为"常规"格式，则计算结果将以数字（时间和日期均以天为单位）显示。 Excel支持两种日期系统：1900年和1904年日期系统。默认的日期系统是1900年日期系统。如果要改为1904年日期系统，应在"工具"?"选项"对话框的"重新计算"选项卡上，选定"1904年日期系统"复选框。 当在Excel中输入日期，但仅为年份输入两位数字时，则默认为： ⑴如果为年份输入的数字在00到29之间，则默认为是2000到2029年。例如，如果键入7/30/13，则默认为这个日期是2013年7月30日。 ⑵如果为年份输入的数字在30到99之间，则默认为是1930到1999年。例如，如果键入7/30/66，则默认为该日期为1966年7月30日。
4.输入公式
在Excel中可以创建许多种公式，其中既有进行简单代数运算的公式，也有分析复杂数学模型的公式。输入公式的方法有两种：一是直接键入，二是利用公式选项板。
直接键入的方法： ⑴选定需要输入公式的单元格。
⑵在所选的单元格中输入等号"＝"，如果单击了"编辑公式"（编辑栏）按钮或"粘贴函数"（常用工具栏）按钮，这时将自动插入一个等号。
⑶输入公式内容。如果计算中用到单元格中的数据，可用鼠标单击所需引用的单元格，如果输入错了，在未输入新的运算符之前，可再单击正确的单元格；也可使用手工方法引用单元格，即在光标处键入单元格的坐标。
⑷公式输入完后，按键，Excel自动计算并将计算结果显示在单元格中，公式内容显示在编辑栏中。
⑸按键（位于数字键左端），可使单元格在显示公式内容与公式结果之间进行切换。
从上述步骤可知，公式的最前面必须是等号"＝"，后面是计算的内容。例如，要在G4中建立一个公式来计算E4＋F4的值，则应在G4单元格中输入： ＝E4＋F4
输入公式后按回车键确认，结果将显示在G4单元格中。
输入公式可以使用公式选项板：
输入的公式中，如果含有函数，“公式选项板”将有助于输入函数。在公式中输入函数时，“公式选项板”将显示函数的名称、各个参数、函数功能和参数的描述、函数的当前结果和整个公式的结果。如果要显示“公式选项板”，请单击编辑栏上的“编辑公式”按钮 （“＝”）。
下面举例说明公式选项板的用法。
求和计算是一种最常用的公式计算，Excel提供了快捷的自动求和方法，即使用工具栏按钮来进行，他将自动的对活动单元格上方或左侧的数据进行求和计算。步骤如下：
⑴将光标放在求和结果单元格。⑵单击工具栏“自动求和”按钮，Excel将自动出现求和SUM函数一级求和数据区域。
⑶单击编辑栏上“输入”按钮（对号）度而定公式，或重新输入数据区域修改公式。
在计算连续单元格的数据之和时，如果在求和区域内单元格中的数字有改变，Excel会自动地更新自动求和结果。
|
