第6章 数学与三角函数的应用 通过使用数学与三角函数，用户可以在工作表中完成求和、取余、随机等计算，在Excel中，根据对数值的计算，可以将数学与三角函数分为多种，包括求和函数、四舍五入函数、随机函数等。本章将以案例的形式对数学与三角函数的应用进行详细介绍。 6.1 常用的求和函数 用户通过SUM函数、SUMIF函数、SUMIFS函数、SUMPRODUCT函数、SUMSQ函数等，可以对数据进行求和操作。 6.1.1?统计所有商品销售总额 SUM函数用于对单元格区域中所有数值求和，其语法格式为： SUM(number1,[number2],...) 参数说明： number1：必需参数，表示要求和的第1个数字，可以是直接输入的数字、单元格引用或数组。 number2：可选参数，表示要求和的第2～255个数字，可以是直接输入的数字、单元格引用或数组。 注意事项 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本将被忽略；如果参数中有错误值或为不能转换成数字的文本，将会导致错误。 示例：使用SUM函数统计所有商品销售总额。 选择G2单元格，输入公式“=SUM(E2:E11)”，如图6-1所示。按Enter键确认，即可计算出销售总额，如图6-2所示。 知识点拨 SUM函数通常和条件判断函数IF嵌套使用，可以对符合条件的数据进行求和。 6.1.2?统计指定商品的销售总额 SUMIF函数用于根据指定条件对若干单元格求和，其语法格式为：SUMIF(range,criteria,sum_range) 参数说明： range：条件区域，用于条件判断的单元格区域。 criteria：求和条件，由数字、逻辑表达式等组成的判定条件。 sum_range：实际求和区域，需要求和的单元格、区域或引用。 示例：使用SUMIF函数统计指定商品的销售总额。 选择H2单元格，输入公式“=SUMIF(B2:B11,G2,E2:E11)”，如图6-3所示。按Enter键确认，即可计算出商品是“直尺”的销售总额，然后将公式向下填充，如图6-4所示。 知识点拨 上述公式“=SUMIF(B2:B11,G2,E2:E11)”中，B2:B11表示条件区域；G2为求和条件；E2:E11为实际求和区域。 动手练 对超过1000的销量进行汇总 在销售统计表中，除了对指定商品的销售额进行汇总外，用户也可以使用SUMIF函数对超过1000的销量进行汇总，如图6-5所示。 选择G2单元格，输入公式“=SUMIF(C2:C11,">1000")”，如图6-6所示。按Enter键确认，即可对超过1000的销量进行汇总，如图6-7所示。 知识点拨 上述公式中省略了第三参数，当省略第三参数时，则条件区域就是实际求和区域。 6.1.3?统计姓“李”的员工的销售额 在对数据进行条件求和时，不是每次求和的条件都完全知道。只记得求和条件的部分信息，例如姓氏，类似这种不完整、不清晰的求和条件称为模糊条件。用户可以使用SUMIF函数按模糊条件对数据求和，例如，统计姓“李”的员工的销售额。 选择H2单元格，输入公式“=SUMIF(A2:A10,"李*",E2:E10)”，如图6-8所示。按Enter键确认，即可计算出姓“李”的员工的销售额，如图6-9所示。 知识点拨 上述公式中使用了星号“*”，和问号“?”一样都是通配符，都可以代替任意的数字、字母、汉字或其他字符，区别在于可以代替的字符数量。一个“?”只能代替一个任意的字符，而一个“*”可以代替任意个数的任意字符。例如“李*”，可以代替“李亚平”“李艳”“李媛”，而 “李?”可以代替“李艳”“李媛”，但不能代替“李亚平”。 动手练 统计手机的销售数量 如果销售商品中有不同品牌的电视机、手机和计算机，要想统计出手机的销售数量，则可以使用通配符进行计算，如图6-10所示。 图6-10 选择H2单元格，输入公式“=SUMIF(B2:B12,"??手机",C2:C12)”，如图6-11所示。按Enter键确认，即可统计出手机的销售数量，如图6-12所示。 图6-11 图6-12 6.1.4?统计3月份台式电脑的入库数量 SUMIFS函数用于解决多条件求和问题，其语法格式为：SUMIFS(sum_range,criteria_range1,criteria1,...) 参数说明： sum_range：求和的实际单元格，即求和区域。 criteria_range1：为特定条件计算的单元格区域，即条件1区域。 criteria1：数字、表达式或文本形式的条件，即条件1，其定义了单元格求和的范围。 示例：使用SUMIFS函数统计3月份台式电脑的入库数量。 选择C13单元格，输入公式“=SUMIFS(D2:D10,A2:A10,"3月",C2:C10,"台式电脑")”，如图6-13所示。按Enter键确认，即可统计出3月份台式电脑的入库数量，如图6-14所示。 图6-13 图6-14 知识点拨 上述公式“=SUMIFS(D2:D10,A2:A10,"3月",C2:C10,"台式电脑")”中，D2:D10为求和区域；A2:A10为条件1所在区域；"3月"为条件1；C2:C10为条件2所在区域；"台式电脑"为条件2。 动手练 统计销售员销售指定商品的金额 在日常工作中，有时需要统计某个销售员销售指定商品的金额为多少，像这种根据多个条件进行求和的问题，则可以使用SUMIFS函数进行计算，如图6-15所示。 图6-15 选择J2单元格，输入公式“=SUMIFS(F2:F13,B2:B13,"赵佳",C2:C13,"索尼液晶电视")”，如图6-16所示。按Enter键确认，即可计算出销售员“赵佳”销售“索尼液晶电视”的金额，如图6-17所示。 图6-16 图6-17 6.1.5?计算购买商品的合计金额 SUMPRODUCT函数用于将数组间对应的元素相乘并返回乘积之和，其语法格式为： SUMPRODUCT(array1,[array2],[array3],...) 参数说明： array1：必需参数，其相应元素需要进行相乘并求和的第一个数组参数。 array2,array3,...：可选参数，2～255个数组参数，其相应元素需要进行相乘并求和。 知识点拨 数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。函数SUMPRODUCT将非数值型的数组元素作为0处理。 示例：使用SUMPRODUCT函数计算购买商品的合计金额。 选择F2单元格，输入公式“=SUMPRODUCT(B2:B8,C2:C8,1-D2:D8)”，如图6-18所示。按Enter键确认，即可计算出购买商品的合计金额，如图6-19所示。 图6-18 图6-19 知识点拨 上述公式中首先利用购买价格*数量*（1-折扣率）计算出每件商品的金额，然后再进行求和，计算出合计金额。 动手练 统计销售部女员工人数 SUMPRODUCT函数除了用来计算乘积之和外，还可以统计人数，例如统计销售部女员工人数，如图6-20所示的是两种统计方法。 图6-20 Step 01 选择F2单元格，输入公式“=SUMPRODUCT((B2:B12="女")*1,(C2:C12="销售部")*1)”，如图6-21所示。按Enter键确认，即可统计出销售部女员工的人数，如图6-22所示。 知识点拨 在SUMPRODUCT函数中包含两个数组。第一个数组判断区域B2:B12中的值是否为 “女”，第二个数组判断区域C2:C12中的值是否为“销售部”，判断结果为包含逻辑值的数组。为了让这两个数组可参加运算，需要将每个数组都乘以1，将其转换为包含1和0的数组。 Step 02 此外，选择F2单元格，输入公式“=COUNTIFS(B2:B12,"女",C2:C12,"销售部")”，如图6-23所示。按Enter键确认，也可以统计出销售部女员工的人数，如图6-24所示。 图6-23 图6-24 6.1.6?计算所有数值的平方和 SUMSQ函数用于求参数的平方和，其语法格式为：SUMSQ(numberl,number2,...) 参数说明：numberl,number2,...表示要计算平方和的数值。参数可以是数字或者是包含数字的名称、数组或引用。直接在参数列表中键入的数字、逻辑值和数字的文字表示等形式的参数均为有效参数。 示例：使用SUMSQ函数计算所有数值的平方和。 选择C2单元格，输入公式“=SUMSQ(A2:B2)”，如图6-25所示。按Enter键确认，即可计算出平方和，然后将公式向下填充，计算所有数值的平方和，如图6-26所示。 知识点拨 上述公式“=SUMSQ(A2:B2)”中使用SUMSQ函数计算3和5的平方和，即9+25。 6.2 其他数学函数的应用 除了求和函数外，FACT函数、PRODUCT函数、MMULT函数、MOD函数、GCD函数、LCM函数等，也属于数学与三角函数。 6.2.1?计算自然数的阶乘 FACT函数用于求数值的阶乘，即1*2*3*...*该数。其语法格式为： FACT(number) 参数说明：number是计算其阶乘的非负数。如果输入的number不是整数，则截去小数部分取整数。 示例：使用FACT函数计算自然数的阶乘。 选择B2单元格，输入公式“=FACT(B1)”，如图6-27所示。按Enter键确认，即可计算出0的阶乘，然后将公式向右填充，如图6-28所示 图6-27 图6-28 知识点拨 公式“=FACT(6)”的阶乘，即1*2*3*4*5*6，得到结果720。 6.2.2?计算产品的总产值 PRODUCT函数用于计算所有参数的乘积，其语法格式为：PRODUCT（number1,number2,...） 参数说明：number1,number2,...是要计算乘积的1～255个数值、逻辑值或者代表数值的字符串。 知识点拨 当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换成数字的文字时，将导致错误。如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 示例：使用PRODUCT函数计算产品的总产值。 选择D2单元格，输入公式“=PRODUCT(B2:C2)”，如图6-29所示。按Enter键确认，即可计算出总产值，然后将公式向下填充，如图6-30所示。 图6-29 图6-30 6.2.3?计算产品不同售价时的利润 MMULT函数用于求数组的矩阵乘积，其语法格式为：MMULT(array1,array2) 参数说明：array1和array2是要进行矩阵乘法运算的两个数组，可以是单元格区域、数组常量或引用。 注意事项 array1的列数必须与array2的行数相同，而且两个数组中都只能包含数值。 示例：使用MMULT函数计算产品不同售价时的利润。 选择D2:E8单元格区域，在“编辑栏”中输入公式“=MMULT(B2:B8,G2:H2)*30%”，如图6-31所示。 知识点拨 上述公式“=MMULT(B2:B8,G2:H2)*30%”中，MMULT函数将B2:B8和G2:H2单元格区域中的值对应相乘，返回一个具有14个结果的数组结果，再与利润率30%相乘，取得利润。 按Ctrl+Shift+Enter组合键确认，即可在D2:E8单元格区域显示所有产品在两种售价下的利润，如图6-32所示。 图6-32 6.2.4?计算两数相除的余数 MOD函数用于求两数相除的余数，其语法格式为：MOD(number,divisor) 参数说明：number为被除数；divisor为除数。 注意事项 余数即被除数整除后的余下部分数值。 示例：使用MOD函数计算两数相除的余数。 选择C2单元格，输入公式“=MOD(A2,B2)”，如图6-33所示。按Enter键确认，即可返回A2除以B2的余数，如图6-34所示，然后将公式向下填充，如图6-35所示。 图6-33 图6-34 图6-35 注意事项 MOD函数的所有参数必须是数值，或者可以被转换成值的数字。MOD函数的第二参数不能为0值。否则产生被0除错误“#DIV/0!”。 动手练 从身份证号码中提取性别 通常员工信息表中需要输入员工的姓名、性别、身份证号码等信息，用户可以使用MOD函数直接从身份证号码中将性别提取出来，如图6-36所示。 图6-36 选择D2单元格，输入公式“=IF(MOD(MID(E2,17,1),2)=1,"男","女")”，如图6-37所示。按Enter键确认，即可将性别信息从身份证号码中提取出来，然后将公式向下填充，如图6-38所示。 图6-37 图6-38 知识点拨 从身份证号码中提取性别的依据是判断身份证号码的第17位数是奇数还是偶数，奇数为男性，偶数为女性。上述公式使用MID函数查找出身份证号码的第17位数字，然后用MOD函数将查找到的数字与2相除得到余数，最后用IF函数进行判断并返回判断结果，当第17位数与2相除的余数等于1时，说明该数为奇数，返回“男”，否则返回“女”。 6.2.5?计算两个或两个以上整数的最大公约数 GCD函数用于求最大公约数，其语法格式为：GCD(number1,number2,...) 参数说明：number1,number2,...为1～255个数值，如果参数为非整数，则截尾取整。如果参数为非数值型，则函数GCD返回错误值#VALUE!。如果参数小于0，则函数GCD返回错误值#NUM!。 示例：使用GCD函数计算两个或两个以上整数的最大公约数。 选择D2单元格，输入公式“=GCD(A2:C2)”，如图6-39所示。按Enter键确认，即可计算出最大公约数，然后将公式向下填充，如图6-40所示。 图6-39 图6-40 知识点拨 最大公约数指两个或多个整数共有约数中最大的一个。最大公约数为1时，各数值间没有相同因数。 6.2.6?计算两个或两个以上整数的最小公倍数 LCM函数用于求最小公倍数。其语法格式为：LCM(number1,number2,...) 参数说明：number1,number2,...可计算最小公倍数的1～255个参数。如果参数不是整数，则截尾取整。如果参数为非数值型，函数LCM返回错误值#VALUE!。如果有任何参数小于0，函数LCM返回错误值#NUM!。 示例：使用LCM函数计算两个或两个以上整数的最小公倍数。 选择D2单元格，输入公式“=LCM(A2:C2)”，如图6-41所示。按Enter键确认，即可计算出最小公倍数，然后将公式向下填充，如图6-42所示。 图6-41 图6-42 知识点拨 两个或多个整数公有的倍数叫作公倍数，其中除0以外最小的一个公倍数就叫作这几个整数的最小公倍数。 6.3 四舍五入函数的应用 用户使用INT函数、ROUND函数、ROUNDUP函数、ROUNDDOWN函数、TRUNC函数、MROUND函数、FLOOR函数、EVEN函数、ODD函数等，可以对数值的小数部分进行处理。 6.3.1?计算合计支出时忽略小数 INT函数用于将数值向下取整为最接近的整数，其语法格式为：INT（number） 参数说明：number为要取整的实数。如果指定数值以外的文本，则会返回错误值“#VALUE!”。 示例：使用INT函数计算合计支出时忽略小数。 首先选择E2单元格，输入公式“=INT(D2)”，如图6-43所示。按Enter键确认，即可对支出金额进行取整，然后将公式向下填充，如图6-44所示。 图6-43 图6-44 接着选择G2单元格，输入公式“=SUM(E2:E10)”，按Enter键确认，即可计算出合计支出，如图6-45所示。 此外，选择G2单元格，输入公式“=SUMPRODUCT(INT(D2:D10))”，按Enter键确认，也可以计算出合计支出，如图6-46所示。 图6-45 图6-46 知识点拨 上述公式“=SUMPRODUCT(INT(D2:D10))”中，首先利用INT函数对D2:D10单元格区域中每个单元格中的数值截尾取整，然后使用SUMPRODUCT函数对取整后的金额汇总。 6.3.2?将收入金额保留到分位 ROUND函数用于按指定位数对数值四舍五入，其语法格式为：ROUND(number,num_digits) 参数说明：number为需要进行四舍五入的数值。num_digits为指定的位数，按此位数进行四舍五入。其中，如果num_digits大于0，则四舍五入到指定的小数位；如果num_digits等于0，则四舍五入到最接近的整数；如果num_digits小于0，则在小数点左侧进行四舍五入。 示例：使用ROUND函数将收入金额保留到分位。 选择D2单元格，输入公式“=ROUND(C2,2)”，如图6-47所示。按Enter键确认，即可将收入金额四舍五入到2位小数，然后将公式向下填充，如图6-48所示。 图6-47 图6-48 6.3.3?将收入金额向上舍入到角位 ROUNDUP函数用于按指定的位数向上舍入数值，其语法格式为：ROUNDUP(number,num_digits) 参数说明：number为需要向上舍入的任意实数。num_digits为舍入后的数字的小数位数。 知识点拨 函数ROUNDUP和函数ROUND功能相似，不同之处在于函数ROUNDUP总是向上舍入数字。如果num_digits大于0，则向上舍入到指定的小数位；如果num_digits等于0，则向上舍入到最接近的整数；如果num_digits小于0，则在小数点左侧向上进行舍入。 示例：使用ROUNDUP函数将收入金额向上舍入到角位。 选择D2单元格，输入公式“=ROUNDUP(C2,1)”，如图6-49所示。按Enter键确认，即可将收入金额向上舍入到1位小数，然后将公式向下填充，如图6-50所示。 图6-49 图6-50 6.3.4?将支出金额向下舍入到整数 ROUNDDOWN函数用于按照指定的位数向下舍入数值，其语法格式为：ROUNDDOWN(number,num_digits) 参数说明：number为需要向下舍入的任意实数。num_digits为舍入后数字的位数。 知识点拨 函数ROUNDDOWN和函数ROUND功能相似，不同之处在于函数ROUNDDOWN总是向下舍入数字。如果num_digits大于0，则向下舍入到指定的小数位；如果num_digits等于0，则向下舍入到最接近的整数；如果num_digits小于0，则在小数点左侧向下进行舍入。 示例：使用ROUNDDOWN函数将支出金额向下舍入到整数。 选择E2单元格，输入公式“=ROUNDDOWN(D2,0)”，如图6-51所示。按Enter键确认，即可将支出金额向下舍入到整数，然后将公式向下填充，如图6-52所示。 图6-51 图6-52 6.3.5?将销售金额的小数部分去掉 TRUNC函数用于将数字截为整数或保留指定位数的小数，其语法格式为：TRUNC(number,[num_digits]) 参数说明：number为必需参数，指定进行截尾操作的数字。num_digits为可选参数。用于指定截尾精度的数字。如果忽略则为0。 示例：使用TRUNC函数将销售金额的小数部分去掉。 选择E2单元格，输入公式“=TRUNC(D2)”，如图6-53所示。按Enter键确认，即可将销售金额截为整数，然后将公式向下填充，如图6-54所示。 图6-53 图6-54 知识点拨 TRUNC和INT的相似之处在于两者都返回整数。TRUNC删除数字的小数部分，INT根据数字小数部分的值将该数字向下舍入为最接近的整数。INT和TRUNC仅当作用于负数时才有所不同，例如TRUNC(-4.3)返回-4，而INT(-4.3)返回-5，因为-5是更小的数字。 动手练 根据员工入职日期计算年假天数 假设公司规定员工工作时间每满365天就可以享受3天年假，不足一年者没有年假，此时，用户可以使用TRUNC函数根据员工入职日期计算年假天数，如图6-55所示。 图6-55 选择G2单元格，输入公式“=TRUNC((TODAY()-F2)*((TODAY()-F2)>=365)/ 365*3)”，如图6-56所示。按Enter键确认，即可计算出年假天数，然后将公式向下填充，如图6-57所示。 图6-56 图6-57 知识点拨 上述公式中，TODAY函数表示今天的日期，利用今天的日期减去入职日期，求出每个员工的工作天数。然后通过表达式“*((TODAY()-F2)>=365)”判断工作天数是否大于或等于365天。如果小于365天则按0天假期处理。然后用工作天数除以365再乘以3计算年假天数。最后使用TRUNC函数将结果取整。 6.3.6?统计销售员提成金额，不足10000元忽略 FLOOR函数用于将参数向下舍入到最接近的基数的倍数。其语法格式为：FLOOR(number,significance) 参数说明：number为必需参数，指定要舍入的数值。significance为必需参数，指定要舍入到的倍数。将参数number向下舍入（沿绝对值减小的方向）为最接近的significance的倍数。如果任一参数为非数值型，则FLOOR将返回错误值#VALUE!。如果number的符号为正，且significance的符号为负，则FLOOR将返回错误值#NUM!。 示例：使用FLOOR函数统计销售员提成金额，不足10000元则忽略。 假设公司规定，销售员的提成金额为每10000元提成600元，不足10000元者忽略不计。选择C2单元格，输入公式“=FLOOR(B2,10000)/10000*600”，如图6-58所示。按Enter键确认，即可计算出提成金额，然后将公式向下填充，如图6-59所示。 知识点拨 上述公式中通过FLOOR函数将每个销售员的业绩以10000为基数，向下舍入，不足10000的尾数都被舍弃，再用转换后的业绩计算提成金额。 6.3.7?活动分组时保证每组人数为偶数 EVEN函数用于将数值向上舍入到最接近的偶数，其语法格式为：EVEN（number） 参数说明：number是将进行向上取偶的数值。如果number为非数值参数，则EVEN返回错误值#VALUE!或#NAME?。不论number的正负号如何，函数都向远离0的方向舍入，如果number恰好是偶数，则无须进行任何舍入处理。 示例：使用EVEN函数保证活动分组时每组人数为偶数。 假设1个小孩相当于0.5个大人，活动分组时为了保证每组人数为偶数，可以选择E2单元格，输入公式“=EVEN (D2)”，如图6-60所示。 图6-60 按Enter键确认，即可将总人数向上舍入到最接近的偶数，然后将公式向下填充，如图6-61所示。 图6-61 6.3.8?随机从奇数行中抽取值班人员 ODD函数用于将数值向上舍入到最接近的奇数，其语法格式为： ODD(number) 参数说明：number为要舍入的值。如果number是非数字的，则ODD返回#VALUE！错误值。不论参数number的正负号如何，数值都是沿绝对值增大的方向向上舍入。如果number恰好是奇数，则不进行舍入。 示例：使用ODD函数随机从奇数行中抽取值班人员。选择D2单元格，输入公式“=INDEX(B:B,ODD(RANDBE- TWEEN(1,ROWS (1:10)-1)))”，如图6-62所示。 图6-62 按Enter键确认，即可随机在奇数行中抽取值班人员，如图6-63所示。 图6-63 知识点拨 上述公式中首先利用ROWS函数计算B列已用区域的行数，然后减去1，再进行随机抽取数据，这可以保证RANDBETWEEN函数产生的随机数在数值限定范围之内。在产生随机数之后，利用ODD函数将所有随机数转换成奇数，再作为INDE函数的参数引用B列的姓名。 动手练 统计指定单元格区域中偶数的个数 当一个表格中存在偶数值和奇数值时，如果用户想要统计指定单元格区域中偶数的个数，如图6-64所示，则可以通过EVEN函数和其他函数嵌套来进行计算。 图6-64 选择E2单元格，输入公式“=SUMPRODUCT(N(EVEN(A2: C8)=(A2:C8)))”，如图6-65所示。 图6-65 按Enter键确认，即可计算出A2:C8单元格区域中偶数的个数，如图6-66所示。 图6-66 知识点拨 上述公式中利用EVEN函数将区域中的所有数据转换成偶数并与原数据进行比较，计算未变化的数值个数，即原区域中偶数个数。 6.4 随机函数的应用 随机函数就是可以产生随机数的函数，常用的随机函数有RAND函数和RANDBETWEEN函数。 6.4.1?产生1～10之间的不重复随机整数 RAND函数用于返回大于或等于0且小于1的均匀分布随机数，其语法格式为：RAND()：其不指定任何参数。 示例：使用RAND函数产生1～10之间的不重复的随机整数。 首先创建一个辅助区，然后选择A2单元格，输入公式“=RAND()”，按Enter键确认，即可产生一个随机数，然后将公式向下填充，如图6-67所示。 图6-67 接着选择C2:C11单元格区域，在“编辑栏”中输入公式“=RANK(A2:A11,A2: A11)”，按Ctrl+Shift+Enter组合键，即可在区域中产生10个1～10之间的不重复的随机整数，如图6-68所示。 图6-68 6.4.2?生成-10～10之间的随机整数 RANDBETWEEN函数用于产生整数的随机数，其语法格式为：RANDBETWEEN（bottom,top） 参数说明：bottom是函数返回的最小整数。top是函数返回的最大整数。 示例：使用RANDBETWEEN函数生成-10～10之间的随机整数。 选择A2:A11单元格区域，如图6-69所示。在“编辑栏”中输入公式“=RANDBETWEEN(-10,10)”，如图6-70所示。按Ctrl+Enter组合键确认，即可生成-10～10之间的随机整数，如图6-71所示。 图6-69 图6-70 图6-71 动手练 随机抽取中奖人员 在某些活动中会有抽奖环节，随机抽选一个号码决定中奖者，在Excel表格中，用户使用RANDBETWEEN和VLOOKUP函数，也可以实现类似功能，如图6-72所示。 图6-72 选择E1单元格，输入公式“=RANDBETWEEN(1,10)”，如图6-73所示。 图6-73 按Enter键确认，即可生成1～10之间的随机数，如图6-74所示。 图6-74 选择E2单元格，输入公式“=VLOOKUP(E1,$A$2:$B$11,2,FALSE)”，如图6-75所示。 图6-75 按Enter键确认，即可按照随机数求出中奖人员，如图6-76所示。 图6-76 案例实战：制作工资条 通常公司会定期给员工发放工资条，反映员工的工资情况。用户可以通过工资明细表，使用函数自动生成工资条，如图6-77所示。 图6-77 Step 01 首先打开“工资明细表”工作表，查看其中的相关数据信息，如图6-78所示。 图6-78 Step 02 打开“工资条”工作表，选择B1单元格，输入公式“=IF(MOD (ROW(),3)=1,工资明细表!A$1,IF(MOD (ROW(),3)=2,OFFSET(工资明细表!A$1, ROW()/3+1,0),""))”，如图6-79所示。 图6-79 Step 03 按Enter键确认，即可引用“工资明细表”中的“工号”，然后将公式向右填充至L1单元格，如图6-80所示。 图6-80 向右填充公式 Step 04 选择B1:L1单元格区域，将光标移至单元格区域的右下角，按住左键不放并向下拖动光标至第2行，如图6-81所示。 向下填充公式 图6-81 Step 05 选择B1:L2单元格区域，为其添加边框和底纹并设置数据的对齐方式，如图6-82所示。 添加边框和底纹 图6-82 Step 06 最后选择B1:L3单元格区域，将公式向下填充即可，如图6-83所示。 向下填充公式 图6-83 知识点拨 上述公式中，使用MOD函数取行号与3的余数来实现动态取数，然后用IF函数根据MOD函数的取余数运算结果来进行取值。当MOD函数取余数为1时，就引用工资明细表中第1行的标题；如果余数为2，则分别取各行的工资明细；如果余数为0，则取空白。 新手答疑 1. Q：如何设置数字格式？ A：选择单元格，按Ctrl+1组合键，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”选项中选择需要的数字格式即可，如图6-84所示。 2. Q：如何设置网格线的颜色？ A：单击“文件”按钮，选择“选项”选项，打开“Excel选项”对话框，选择“高级”选项，然后在“此工作表的显示选项”区域中单击“网格线颜色”下拉按钮，在弹出的列表中选择合适的颜色即可，如图6-85所示。 图6-84 图6-85 3. Q：如何快速为表格设置样式？ A：选择表格区域，在“开始”选项卡中单击“套用表格格式”下拉按钮，在弹出的列表中选择合适的表格样式，如图6-86所示。弹出“套用表格式”对话框，直接单击“确定”按钮即可，如图6-87所示。 图6-86 图6-87