業務部門銷售分析-日期時間函數

年
C2=YEAR(B2)

月
D2=MONTH(B2)

日
E2=DAY(B2)

週-週三 or 星期三
F2=WEEKDAY(B2,1)

陣列公式

二維矩陣9*9
{={1,2,3,4,5,6,7,8,9}*{1;2;3;4;5;6;7;8;9}}

日記帳次數分配表
K2:K6
=FREQUENCY(F2:F427,J2:J6)

日記帳-小計與資料庫函數

Google Drive分享
https://goo.gl/wjrl3Z

搭配篩選功能

總計
E1=SUBTOTAL(9,F8:F433)

平均單價
E2=SUBTOTAL(1,F8:F433)

最大
E3=SUBTOTAL(4,F8:F433)

最小
E4=SUBTOTAL(5,F8:F433)

次數
E5=SUBTOTAL(2,F8:F433)

---
資料庫函數

小計總計
A15=DSUM(日記帳!A7:F433,日記帳!F7,A1:E9)

單筆消費
B15=DAVERAGE(日記帳!A7:F433,日記帳!F7,A1:E9)

最大值
C15=DMAX(日記帳!A7:F433,日記帳!F7,A1:E9)

最小值
D15=DMIN(日記帳!A7:F433,日記帳!F7,A1:E9)

筆數
E15
E=DCOUNT(日記帳!A7:F433,日記帳!F7,A1:E9)

文字&日期函數-成績單3

匿名-1
C2=LEFT(B2,1)&"Ｏ"&RIGHT(B2,1)

匿名-2
E2=IF(LEN(D2)<=3,LEFT(D2,1),LEFT(D2,2))&"O"&IF(LEN(D2)<3,"",RIGHT(D2,1))

月
C2=MID(A2,4,2)

日期
E2=DATE(B2,C2,D2)

星期
F2=WEEKDAY(E2,2)

找@
G2=FIND("@",F2)

帳號
H2=LEFT(F2,FIND("@",F2)-1)

郵遞地址
M2=CONCATENATE(L2,J2,K2,I2)

20150819-成績單2

月份
I2=VLOOKUP(H2,$A$1:$C$12,2,FALSE)

月英文名
J2=VLOOKUP(H2,$A$1:$C$12,3,FALSE)

評等-查表
Y2=HLOOKUP(Q2,查表!$A$15:$E$16,2,TRUE)

評等1-IF
Z2=IF(Q2<60,"E",IF(Q2<70,"D",IF(Q2<80,"C",IF(Q2<90,"B","A"))))

評等2-IF
=IF(Q2>=60,IF(Q2>=70,IF(Q2>=80,IF(Q2>=90,"A","B"),"C"),"D"),"E")

follow以下邏輯
=IF(Q2>=60,"","E")

=IF(Q2>=70,"","D")

=IF(Q2>=80,"","C")

=IF(Q2>=90,"A","B")

20150819-成績單

總分
P2=SUM(F2:O2)

平均
Q2=AVERAGE(F2:O2)

名次
R2=RANK(P2,$P$2:$P$13)

最高分
S2=MAX(F2:O2)

最低分
T2=MIN(F2:O2)

科總分
F15=SUM(F2:F13)

科平均
F16=F15/COUNT(F2:F13)

應考人數
F17=COUNTA(E2:E13)

-----
取整數
F18=INT(F16)

四捨五入
F19=ROUND(F15/COUNT(F2:F13),1)

無條件進位
F20=ROUNDUP(F15/COUNT(F2:F13),1)

無條件捨去
F21=ROUNDDOWN(F15/COUNT(F2:F13),1)

自治市
V2=IF(Q2>=85,"入選","")

決選
W2=IF(U2<=10,IF(COUNTIF(F2:O2,"<60")<=1,"恭喜!","再加油!"),"")

不及格
X2=COUNTIF(F2:O2,"<60")