|
新4班217号第3讲作业
<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">第一题</span><font face="Times New Roman">
</font></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><font face="Times New Roman">1. </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">销售额栏输入公式</span><span lang="EN-US"><font face="Times New Roman">E7=C7*D7, </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><span style="mso-spacerun: yes;"><font face="Times New Roman"> </font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">利润额栏输入公式</span><span lang="EN-US"><font face="Times New Roman">F7=E7*$F$4, </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">或者</span><span lang="EN-US"><font face="Times New Roman">E7*F4, </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">按</span><span lang="EN-US"><font face="Times New Roman">F4, </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><span style="mso-spacerun: yes;"><font face="Times New Roman"> </font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">备注栏输入公式</span><span lang="EN-US"><font face="Times New Roman">G7=A7&"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">卖了</span><span lang="EN-US"><font face="Times New Roman">"&D7&B7&"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">赚了</span><span lang="EN-US"><font face="Times New Roman">"&F7&"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">元</span><span lang="EN-US"><font face="Times New Roman">"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><font face="Times New Roman">2. </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">输入公式</span><span lang="EN-US"><font face="Times New Roman">C25=$B26&C$25&"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">加油</span><span lang="EN-US"><font face="Times New Roman">"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,下拉十字光标,下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">第二题</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">1.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">最高分</span><span lang="EN-US"><font face="Times New Roman"> B23=MAX(B5:B22)</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">2.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">最低分</span><span lang="EN-US"><font face="Times New Roman"> B24=MIN(B5:B22),</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">3.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">平均分</span><span lang="EN-US"><font face="Times New Roman"> B25=AVERAGE(B5:B22),</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">4.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">正数第二名的成绩</span><span lang="EN-US"><font face="Times New Roman"> B26=LARGE(B5:B22,2), </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">5.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">倒数第三名的成绩</span><span lang="EN-US"><font face="Times New Roman"> B27=SMALL(B5:B22,3)</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">6.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">参加考试的人数</span><span lang="EN-US"><font face="Times New Roman">B28=COUNT(B5:B22), </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">7.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">成绩大于</span><span lang="EN-US"><font face="Times New Roman">80</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">分的人数</span><span lang="EN-US"><font face="Times New Roman">B29=COUNTIF(B5:B22,">80")</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 18pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo1; tab-stops: list 18.0pt;"><span lang="EN-US" style="mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;"><font face="Times New Roman">8.<span style="FONT: 7pt "Times New Roman";"> </span></font></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">及格的总分</span><span lang="EN-US"><font face="Times New Roman">B30= =SUMIF(B5:B22,">=60",B5:B22), </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">右拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">第三题</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><font face="Times New Roman">1. </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">选中</span><span lang="EN-US"><font face="Times New Roman">C4-C17</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">区域,选中数据</span><span lang="EN-US"><font face="Times New Roman">-</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">有效性</span><span lang="EN-US"><font face="Times New Roman">-</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">设置</span><span lang="EN-US"><font face="Times New Roman">-</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">自定义,输入公式</span><span lang="EN-US"><font face="Times New Roman">=LEFT(C4,1)="2"&LEN(C4)>3&LEN(C4)<8</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><font face="Times New Roman">2. </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">出生年月日</span><span lang="EN-US"><font face="Times New Roman"> E22=TEXT(MID(C22,7,8),"00-00-00")</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 15.75pt; mso-char-indent-count: 1.5;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">性别</span><span lang="EN-US"><font face="Times New Roman"> F22=IF(MOD(MID(C22,17,1),2)=0,"</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">女</span><span lang="EN-US"><font face="Times New Roman">","</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">男</span><span lang="EN-US"><font face="Times New Roman">")</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">,下拉十字光标</span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span lang="EN-US"><font face="Times New Roman">3.</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">选中校验数据区域,选中格式</span><span lang="EN-US"><font face="Times New Roman">-</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">单击条件格式,选择单元格数值,输入不等于,公式</span><span lang="EN-US"><font face="Times New Roman">=J3</font></span></p><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt;"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman";">选中格式,选择图案,选中绿色,确定</span></p> |
|