[ 习题 ] 【函入5期1班】第1讲作业上交贴（4组）

juju桔桔 · 发表于 2010-1-11 21:03

afRr5ZBA.rar (31.27 KB, 下载次数: 1)

yaluolee · 发表于 2010-1-11 22:07

提示: 作者被禁止或删除内容自动屏蔽

qiqijiang · 发表于 2010-1-12 12:43

第一题      =ROW(A1)&"*"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1)   第二题                         <table cellspacing="0" cellpadding="0" width="145" border="0" style="WIDTH: 109pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="73" style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2336;"></col><col width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl26" width="145" colspan="2" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 109pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">合计销量   =SUBTOTAL(9,D28:D36)</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl26" colspan="2" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">平均销量=SUBTOTAL(1,D28:D36)</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl26" colspan="2" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">最大销量=SUBTOTAL(4,D28:D36)</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl26" colspan="2" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffcc99;">最小销量=SUBTOTAL(5,D29:D37)</td></tr></tbody></table>  第三题 (C71-100000)*0.02+100000*0.01第四题   MATCH(1,{1,2,3},)    第一个公式表示为1，和1，2，3，还有无限大的交集                     MATCH(1,{1,2,3})      第二个公式表示为1，和1，2，3的交集两个公式表示的意思是包含与被包含的关系

[此贴子已经被作者于2010-1-12 13:02:43编辑过]

学海泛舟 · 发表于 2010-1-12 13:57

第一题：           (ROW()-1)&"x"&(COLUMN()-1)&"="&(ROW()-1)*(COLUMN()-1) 第二题：            =SUBTOTAL(109,D28:D37)       =SUBTOTAL(101,D28:D37)     =SUBTOTAL(104,D28:D37)    =SUBTOTAL(105,D28:D37)第三题：          =C71*0.01+(C71-100000)*0.01*((C71-100000)>0)第四题：        =MATCH(1,{1,2,3},)  这个公式中省略了最后一个参数，有逗号，所以最后一个参数为“0“，相当于   =MATCH(1,{1,2,3},0)，代表在{1,2,3}查找等于1的第一个值的在数组{1,2,3}中的位置；          =MATCH(1,{1,2,3})这个公式中省略了最后一个参数，但无逗号，所以最后一个参数为“1“，相当于   =MATCH(1,{1,2,3},1)，代表在{1,2,3}查找小于或等于1的最大值在数组{1,2,3}中的位置。

yesmine · 发表于 2010-1-12 15:32

第一题：<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">=ROW(A1)&"*"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1)<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第二题:合计销量=SUBTOTAL(9,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">平均销量=SUBTOTAL(1,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">最大销量=SUBTOTAL(4,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">最小销量=SUBTOTAL(5,D26:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">然后根据不同的要求进行筛选即可得到效果图所要结果.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第三题:第一个=（C71-100000）*0.002+100000*0.01，向下填充，然后将第三个=C73*0.01，下拉至第四个<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第四题:<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">MATCH(1,{1,2,3},)相当于 match_type 为 0。<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">       MATCH(1,{1,2,3})相当于 match_type 为 1.

yesmine · 发表于 2010-1-12 15:35

<div style="MARGIN-TOP: 10px; FONT-SIZE: 9pt; MIN-HEIGHT: 200px; WORD-BREAK: break-all; TEXT-INDENT: 24px; LINE-HEIGHT: normal; WORD-WRAP: break-word;"> ４ 组０４６作业上交第一题：<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">=ROW(A1)&"*"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1)<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第二题:合计销量=SUBTOTAL(9,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">平均销量=SUBTOTAL(1,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">最大销量=SUBTOTAL(4,D28:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">最小销量=SUBTOTAL(5,D26:D36),向右填充即可.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">然后根据不同的要求进行筛选即可得到效果图所要结果.<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第三题:第一个=（C71-100000）*0.002+100000*0.01，向下填充，然后将第三个=C73*0.01，下拉至第四个<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;"><span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">第四题:<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">MATCH(1,{1,2,3},)相当于 match_type 为 0。<span lang="EN-US" style="FONT-FAMILY: 'font-size:10.5pt;mso-bidi-font-size:12.0pt;mso-fareast-font-family:宋体;mso-font-kerning:1.0pt;mso-ansi-language:en-us;mso-fareast-language:zh-cn;mso-bidi-language:ar-sa;;">       MATCH(1,{1,2,3})相当于 match_type 为 1. </div>[em01]

加倍珍惜 · 发表于 2010-1-12 15:43

游客，如果您要查看本帖隐藏内容请回复

vbyou127 · 发表于 2016-1-14 10:02

55656623

		自动登录	找回密码
密码			注册

yaluolee yaluolee 当前离线积分 285 IP卡狗仔卡	发表于 2010-1-11 22:07 \| 显示全部楼层工作中常用的Excel函数公式，全印在一张超大鼠标垫上提示: 作者被禁止或删除内容自动屏蔽
yaluolee yaluolee 当前离线积分 285 IP卡狗仔卡
	回复使用道具举报显身卡

[通知] [ 习题 ] 【函入5期1班】第1讲作业上交贴（4组）

4组054上交作业

4组049交作业

交作业