【新手4班】第3讲（321～402）作业上交贴

yjscia520

<p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">新4班332号第3讲作业上交贴</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><font color="#ee1111"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">第</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">1</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">题：</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><font color="#f70909"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">1</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">、</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">E7=C7*D7</span></font></font></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"></span><font style="BACKGROUND-COLOR: #dddd22;"><font color="#f70909"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">F7=E7*$F$4</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"></span></font></font></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"></span><font style="BACKGROUND-COLOR: #dddd22;"><font color="#f70909"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">G7=A7&amp;"</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">卖了</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">"&amp;D7&amp;B7&amp;"</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">赚了</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">"&amp;F7&amp;"</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">元</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">"</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">（分别下拉）</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><font color="#f70909"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">2<font face="宋体">、</font><font face="Arial Narrow">C26=$B26&amp;C$25&amp;$B$25</font><font face="宋体">右拉下拉</font></span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">第</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">1</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';">题：</span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B23=MAX(B5:B22)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B24=MIN(B5:B22)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B25=AVERAGE(B5:B22)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B26=LARGE(B5:B22,2)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B27=SMALL(B5:B22,3)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B28=COUNT(B5:B22)</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">B29=COUNTIF(B5:B22,"&gt;80")</font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">B30=SUMIF(B5:B22,"&gt;60"),<font face="宋体">分别右拉</font></span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">第<font face="Arial Narrow">3</font><font face="宋体">题：</font></font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font face="Arial Narrow">1</font><font face="宋体">、数据有效性</font><font face="Arial Narrow">→</font><font face="宋体">自定义，公式</font><font face="Arial Narrow">=AND(LEFT(C4)="2",</font></span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font face="Arial Narrow">(LEN(C4)&gt;=4)*(LEN(C4)&lt;=7))</font></span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';">2<font face="宋体">、</font><font face="Arial Narrow">E22=MID(C22,7,8)</font><font face="宋体">，</font><font face="Arial Narrow">F22=IF(MOD(RIGHT(LEFT(C22,17)),2)=0,"</font><font face="宋体">女</font><font face="Arial Narrow">","</font><font face="宋体">男</font><font face="Arial Narrow">")</font></span><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></p><p></p><p></p><p></p><p></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;">3<font face="宋体">、<font face="Arial Narrow">K</font><font face="宋体">列:</font></font></font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;"><font face="宋体">条件格式，公式，</font><font face="Arial Narrow"><font face="宋体"><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font>=$j4&lt;&gt;$M4,</font><font face="宋体">格式，选择图案颜色，红色，确定。</font></font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font style="BACKGROUND-COLOR: #dddd22;"><font face="Arial Narrow">N</font><font face="宋体">列同理:</font></font></span></p><p class="p0" style="MARGIN-TOP: 0pt; MARGIN-BOTTOM: 0pt; TEXT-INDENT: 28pt;"><font style="BACKGROUND-COLOR: #dddd22;"><span style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: rgb(0,0,0); FONT-FAMILY: 'Arial Narrow'; mso-spacerun: 'yes';"><font face="宋体">条件格式，公式，<font face="Arial Narrow"><font face="宋体"><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font>=$k4&lt;&gt;$n4,</font><font face="宋体">格式，选择图案颜色，红色，确定。</font></font></span><span style="FONT-SIZE: 10.5pt; FONT-FAMILY: '宋体'; mso-spacerun: 'yes';"><br/>&nbsp;&nbsp;&nbsp;&nbsp;<p></p></span></font></p><p></p><p></p><p></p><p></p><p><!--Element not supported - Type: 8 Name: #comment--> FRjadK8p.rar (17.68 KB, 下载次数: 1)

2010-10-29 00:39 上传

点击文件名下载附件
新4班332号第3讲作业上交贴 </p><p></p><p></p><p></p><p><font color="#ff0000">通过<br/></font></p>

[此贴子已经被wjc2090742于2010-10-30 16:40:37编辑过]

wpzls

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

zb1589

<p><strong><font face="Arial Narrow" color="#ff0000" size="4">新4班379号第三讲作业</font></strong></p><p>

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

</p><p></p><p></p><p></p><p><font color="#ff0000">通过</font></p>

[此贴子已经被wjc2090742于2010-10-30 16:44:15编辑过]

guofeng123

<p>第一题：</p><p>1、E7=C7*D7,F7=E7*30%,G7=A7&amp;"卖了"&amp;D7&amp;B7&amp;"赚了"&amp;F7&amp;"元";然后分别下拉。</p><p>2、C26=$B26&amp;C$25&amp;$B$25,然后横向、竖向拉齐。</p><p>第二题：</p><p>B23=MAX(B5:B22),B24=MIN(B5:B22),B25=AVERAGE(B5:B22),B26=LARGE(B5:B22,2),B27=SMALL(B5:B22,3)</p><p>B28=COUNTA(B5:B22),B29=COUNTIF(B5:B22,"&gt;80"),B30=SUMIF(B5:B22,"&gt;=60"),然后分别右拉。</p><p>第三题：</p><p>1、选中C4:C17,选‘数据’菜单下的‘有效性’项，‘设置’标签下‘允许’下拉框选‘自定义’，‘公式’下拉框填入</p><p>‘=AND(LEFT(C4)="2",(LEN(C4)&gt;=4)*(LEN(C4)&lt;=7))’然后点确定。</p><p>2、E22=MID(C22,7,8),F22=IF(MOD(RIGHT(LEFT(C22,17)),2)=0,"女","男"),然后分别下拉。</p><p>3、选中M4:M23,点‘格式’菜单下的‘条件格式’，‘条件1’选‘公式’填入‘=(J4:J23)&lt;&gt;(M4:M23)’，下面‘格式’选‘红色’，确定。</p><p>选中N4:N23,点‘格式’菜单下的‘条件格式’，‘条件1’选‘公式’填入'=(K4:K23)&lt;&gt;(N4:N23)'，下面‘格式’选‘红色’，确定。</p><p></p><p></p><p></p><p><font color="#f70909">通过</font></p>

[此贴子已经被wjc2090742于2010-10-30 16:43:01编辑过]

kaiwens

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

david176

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

水中绿萝

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

vEuqmbZ9.rar (16.7 KB, 下载次数: 1)

2010-10-30 21:14 上传

点击文件名下载附件
【新手4班】第3讲（321～402）作业上交贴 <br/>

jimclever

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

麦茶

学习中，谢谢分享。