Office應用

相關資料下載
https://goo.gl/qXaBB0


關於Word



關於Excel



關於PowerPoint

Excel函數進階實務-闖關幫幫忙(2015/10/16-2015/10/31)

大家好，先票選我們準備的五個案例，作為我們闖關主題。最後選出兩個讓大家一起闖關幫幫忙，每個主題會提供至少15題，看大家的表現與互動而定，這次邀請的對象，以台北市公訓處Excel函數進階實務的學員為主，如果你想參與這次活動，請在此FB留言，我們會將剩餘名額提供出來。

https://www.facebook.com/events/1627728970812091/

Excel函數進階實務(2015/10/16-2015/10/31) from Albiter357

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

年
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")

決賽入圍

學級
D2=VLOOKUP(C2,參賽學校一覽表!$A$1:$B$48,2,FALSE)

名次-積分
E2=HLOOKUP(A2,查表!$A$1:$D$2,2,TRUE)

學級-積分
=IF(D2="大學",1,IF(D2="高中",2,3))

人數-積分
G2=VLOOKUP(C2,工作表4!$A$5:$B$51,2,FALSE)

總積分 H2=PRODUCT(E2:G2)

次數分配表 {=FREQUENCY(H2:H81,L3:L12)}

日記帳

年度
=YEAR(A2)

月份
=MONTH(A2)

日
=DAY(A2)

文字與日期時間

字數
B2=LEN(A2)

第1個  /
C2=FIND("/",A2)

第2個  /
D2=FIND("/",A2,C2+1)

年
D2=LEFT(A2,C2-1)

月
E2=MID(A2,C2+1,D2-C2-1)

日
F2=RIGHT(A2,B2-D2)

20150623-成績單

V2 =IF(T2="V",IF(U2="V","推甄成功","再加油!"),"")

排名 R2=RANK(Q2,$Q$2:$Q$13)
三主科平均  S2=AVERAGE(F2:H2)
確認主科平均 T2=IF(S2>=80,"V","")
確認排名 U2=IF(R2<=8,"V","")

推甄完整版01
=IF(IF(AVERAGE(F2:H2)>=80,"V","")="V",IF(IF(RANK(Q2,$Q$2:$Q$13)<=8,"V","")="V","推甄成功","再加油!"),"")

推甄完整版02
=IF(AVERAGE(F2:H2)>=80,IF(RANK(Q2,$Q$2:$Q$13)<=8,"推甄成功","再加油!"),"")


COUNTIF應用
---
85分以上(>=85)
F16=COUNTIF(F2:F13,">=85")

70-85分(>=70, <85)
F17=COUNTIF(F2:F13,">=70")-F16

60-70分(>=60, <70)
F18=COUNTIF(F2:F13,">=60")-F17-F16

60分以下(<60)
F19=COUNT(F2:F13)-SUM(F16:F18)

數值小數位數處理
---
INT 取整數
Y2=INT(S2)

ROUND 四捨無入
Z2=ROUND(S2,2)

ROUNDUP 無條件進位
AA=ROUNDUP(S2,2)

ROUNDDOWN 無條件捨去
AB=ROUNDDOWN(S2,2)

文字函數練習
---
姓氏02
F2=IF(LEN(B2)>3,LEFT(B2,2),LEFT(B2,1))

名字02
G2=IF(LEN(B2)<3,RIGHT(B2,1),RIGHT(B2,2))

匿名
H2=F2&"Ｏ"&IF(LEN(B2)<3,"",RIGHT(B2,1))

日期時間

0981015

=(LEFT(C5,2)+1911)&"/"&MID(C5,4,2)&"/"&RIGHT(C5,2)
=DATE(LEFT(C6,3)+1911,MID(C6,4,2),RIGHT(C6,2)

文字函數

姓氏
=IF(LEN(B2)<4,LEFT(B2,1),LEFT(B2,2))

中間名
=IF(LEN(B2)<4,MID(B2,2,1),MID(B2,3,1))

最後名
=IF(LEN(B2)>2,RIGHT(B2,1),"")

匿名
=IF(LEN(B2)<4,LEFT(B2,1),LEFT(B2,2))&"Ｏ"&IF(LEN(B2)>2,RIGHT(B2,1),"")

---
@位置
=FIND("@",G2)(B2,1),"")

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

Domain
=MID(G2,FIND("@",G2)+1,LEN(G2))

01_Excel_學生成績單_0210e


---
字串連接
=CONCATENATE(IF(LEN(B2)<4,LEFT(B2,1),LEFT(B2,2)),"Ｏ",IF(LEN(B2)>2,RIGHT(B2,1),""))

01_Excel_學生成績單_0210f

巢狀IF的決策比較

http://taipeiexcel2010.blogspot.tw/2013/08/blog-post_8.html

先比大或比小

週薪計算

30-40小時(含40)
=IF(F12<=40,IF(F12>30,(F12-30)*B20,0),10*B20)
請比較以下差異...
=IF(F11>40,10*B19,IF(F11>=30,(F11-30)*B19,0))

20150210學生成績單

U2期中考
=HLOOKUP(Q2,分數等級!$A$1:$D$2,2,TRUE)

查表如下：

0	60	70	80
D	C	B	A

>=0 and <59
>=60 and <70
>=70 and <80
>=80

http://goo.gl/Hdi0cT