|
<p>4组,049交作业 谢谢学委</p><div id="blogDetailDiv" style="FONT-SIZE: 16px;"><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span style="FONT-SIZE: 12pt; COLOR: red; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">第一题答案:<span lang="EN-US"><p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">1</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、<span lang="EN-US">{=SUM(IF(E5:E16<(C5:C16=TRANSPOSE(G5:G8))*TRANSPOSE(H5:H8),1,0))}<p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">2</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、<span lang="EN-US">{=SUM((D5:D16)*(E5:E16))-SUM(((C5:C16=TRANSPOSE(G5:G8))*(TRANSPOSE(H5:H8))*(D5:D16)))}<p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;"><p></p> </span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span style="FONT-SIZE: 12pt; COLOR: red; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">第二题答案:<span lang="EN-US"><p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">1</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、用辅助列 方法一<span lang="EN-US">:<p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">=VLOOKUP($G$27&$H27,$A$27:$D$42,COLUMN(C1),FALSE)<p></p></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">2</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、用<span lang="EN-US">IF</span>函数方法二:<span lang="EN-US">VLOOKUP($G$27&$H27,IF({1,0},$B$27:$B$42&$E$27:$E$42,D$27:D$42),2,0)<p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;"><p></p> </span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span style="FONT-SIZE: 12pt; COLOR: red; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">第三题答案:<span lang="EN-US"><p></p></span></span></p><p class="MsoNormal" align="left" style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">1</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、辅助列方法一:<span lang="EN-US">=IF(ISERROR(VLOOKUP($B$47&$C47,$A$27:$D$42,COLUMN(D47),FALSE)),"</span>查无此人<span lang="EN-US">",VLOOKUP($B$47&$C47,$A$27:$D$42,COLUMN(D47),FALSE)&"")<p></p></span></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">2</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">、用<span lang="EN-US">IF</span>函数方法二:<span lang="EN-US"><p></p></span></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;"><span style="mso-spacerun: yes;"> </span>=IF(ISNA(MATCH(B47,B27:B42,)),"</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: '微软雅黑','sans-serif'; mso-bidi-font-family: 宋体; mso-font-kerning: 0pt;">查无此人<span lang="EN-US">",VLOOKUP(B47&C47,CHOOSE({1,2,3},B27:B42&E27:E42,C27:C42,D27:D42),MATCH(D47,C26:D26,0)+1,))</span></span><span lang="EN-US" style="FONT-FAMILY: '微软雅黑','sans-serif';"><p></p></span></p><p></p></div> |
|