函入6期第三讲作业上交帖（5、6、7组）

2010yun

<p>

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

</p><p>选做题2不会</p><p>&nbsp;</p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">选做题2实际上是多条件计数，把以前的思路扔掉，重新整理思路再试一次。</font></p><font color="#dd4822">[em17][em17][em17]</font>

[此贴子已经被QQ9947于2010-8-27 10:22:03编辑过]

aflyf

<p>

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

</p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">[em17][em17][em17]</font></p><p><font color="#dd4822">选做题2思路非常好，但公式还可以简化，用一个SUM函数就可以了。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:28:21编辑过]

dreampool

<p><strong><font color="#ff3300" size="4">函入6期1班5组73号交作业</font></strong></p><p>

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

</p><p>选做题二只会一半 [em04]</p><p></p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">[em17][em17]</font></p><p><font color="#dd4822">选做题2按多条件计数的思路来做，问题的关键在于两点：</font></p><p><font color="#dd4822">1、如何把多条件结合起来。</font></p><p><font color="#dd4822">2、如何求出符合条件的个数。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:34:13编辑过]

小單

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

</p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">[em17][em17][em17]</font></p><p><font color="#bb4444">小单童鞋对函数及运算符了解非常啊。</font></p><p>&nbsp;</p>[em17][em17]

[此贴子已经被QQ9947于2010-8-27 10:38:56编辑过]

fk1758

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

</p><p></p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">[em17][em17][em17]</font></p><p><font color="#dd4822">选做题二思路不错，公式还可以再简化一下。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:41:29编辑过]

rhr2008

<p><strong><font color="#000000" size="4">函入6期班6组084号交作业，请老师批阅！谢谢！</font></strong></p><p>&nbsp;

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

</p><p><font color="#cc3333">----------------------------------------------</font></p><p><font color="#cc3333">&nbsp;</font></p><p><font color="#cc3333">[em17][em17][em17]</font></p><p><font color="#cc3333">都用上MMULT了，不错不错。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:45:10编辑过]

沙漠王子1

<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">第一题：</span><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF(B9:B21,"yf")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">2</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUMIF(B9:B21,"yf",F9)</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">3</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF(F9:F21,"&gt;20000")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">4</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUMIF(E9:E21,"&gt;1000")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">第二题：</span><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUMIF(B2:B80,"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">一分厂</span><span lang="EN-US"><font face="Times New Roman">",G2)</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">2</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUM((B2:B80="</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">一分厂</span><span lang="EN-US"><font face="Times New Roman">")*(C2:C80="</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">治疗费</span><span lang="EN-US"><font face="Times New Roman">")*G2:G80)<span style="mso-spacerun: yes;">&nbsp; </span></font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">数组</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">3</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUM((B2:B80="</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">一分厂</span><span lang="EN-US"><font face="Times New Roman">")*(E2:E80&gt;4))<span style="mso-spacerun: yes;">&nbsp; </span></font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">数组</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">4</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=SUMIF(D2:D80,"&gt;500")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">第三题：</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF($C$9:$J$18,C9)&gt;=5</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">第四题：</span><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF(C8:C18,"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">张</span><span lang="EN-US"><font face="Times New Roman">*")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">2</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF(C8:C18,"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">张</span><span lang="EN-US"><font face="Times New Roman">?")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 42pt; mso-char-indent-count: 4.0;"><span lang="EN-US"><font face="Times New Roman">3</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">、</span><span lang="EN-US"><font face="Times New Roman">=COUNTIF(C8:C18,"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">张</span><span lang="EN-US"><font face="Times New Roman">??")</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">选做题一：</span><span lang="EN-US"><font face="Times New Roman">=SUM((B6:B23=TEXT(F8,0))*(C6:C23=TEXT(G8,0))*D6:D23)<span style="mso-spacerun: yes;">&nbsp; </span></font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">数组</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">选做题二：</span><span lang="EN-US"><font face="Times New Roman">=RANK(J7,$J$7:$J$29,)</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><span style="mso-spacerun: yes;"><font face="Times New Roman">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">在</span><span lang="EN-US"><font face="Times New Roman">J</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">列设了辅助列，辅助列公式是</span><span lang="EN-US"><font face="Times New Roman">=H7*10000+G7</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">（总成绩</span><span lang="EN-US"><font face="Times New Roman">*10000+</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: &quot;Times New Roman&quot;; mso-hansi-font-family: &quot;Times New Roman&quot;;">实验成绩）</span></p><p></p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">1、作业要求上传附件，以后请严格按要求上交作业。</font></p><p><font color="#dd4822">2、选做题二去掉辅助列后再试试。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:46:16编辑过]

沙漠王子1

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

</p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">不要重复回贴！</font></p>

[此贴子已经被QQ9947于2010-8-27 10:47:17编辑过]

niuniu、

<p>

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

</p><p><font color="#dd4822">--------------------------------------------------------</font></p><p><font color="#dd4822">选做题二答案与参考答案不一致，请仔细看清题目要求再答题。</font></p>

[此贴子已经被QQ9947于2010-8-27 10:48:58编辑过]

悠悠雨

<p>05069，悠悠雨</p><p>

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

</p><p><font color="#cc5233">-----------------------------------------------------------------<br/></font></p><p><font color="#cc5233">本次作业要求上传附件，以后请严格按要求上交作业。</font>
        </p>

[此贴子已经被QQ9947于2010-8-26 22:26:48编辑过]