|
<p>第一题:</p><p>1、销售额列:=C7*D7</p><p>利润列:=E7*$F$4</p><p>备注列:=A7&"卖了"&D7&B7&"赚了"&F7&"元"</p><p>2、公式为:=$B26&C$25&"加油"</p><p>第二题</p><p>1、最高分:=MAX(B5:B22) ,然后右拉 ;最低分:=MIN(B5:B22),然后右拉;</p><p>平均分:=AVERAGE(B5:B22),然后右拉;正数第二名的成绩:=LARGE(B5:B22,2)然后右拉;</p><p>倒数第三名的成绩:=SMALL(B5:B22,3),然后右拉 参加考试的人数:=COUNTA(B5:B22),然后右拉</p><p>成绩大于80分的人数: =COUNTIF(B5:B22,">80"),然后右拉 及格的总分:=SUMIF(B5:B22,">=60"),然后右拉</p><p><table cellspacing="0" cellpadding="0" width="162" border="0" style="WIDTH: 122pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="162" style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5184;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" width="162" height="19" style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 122pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><p><font face="宋体">第三题:</font></p><p><font face="宋体">1、=AND(LEFT($C4,1)="2",LEN($C4)>=4,LEN($C4)<=7)</font></p></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" height="19" style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><p><font face="宋体">2、出生年月公式为:=IF(LEN(C22)=15,"19"&MID(C22,7,6),IF(LEN(C22)=18,MID(C22,7,8)))</font></p><p><font face="宋体">性别公式为:=IF(MOD(IF(LEN(C22)=15,MID(C22,15,1),MID(C22,17,1)),2)=1,"男","女")</font></p><p><font face="宋体"></font> </p></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" height="19" style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><p><font face="宋体">3、条件公式为:M4:N22<>J4:K22</font></p><p><font face="宋体">然后选择底色为红</font></p><p><font face="宋体">结果详见附件</font></p></td></tr></tbody></table></p>
XgXHGU6j.rar
(17.8 KB, 下载次数: 1)
|
|