Taipei Excel 2010: 2013

2013年12月4日星期三

雲端硬碟分享

比對資料
=IF(ISERROR(VLOOKUP(C4,$R$4:$R$28,1,FALSE)),"V","")

2013年12月2日星期一

完成SUM, AVERAGE, MAX, MIN
COUNT, COUNTA
COUNTIF
IF

使用COUNTIF製作次數分配表

01_Excel_學生成績單_01，下載

巢狀IF
=IF(Q2>=60,IF(Q2>=70,IF(Q2>=80,"A","B"),"C"),"D")

多重條件
=IF(AND(Q2>=70,S2<=10,C2="男"),"師大附中",IF(AND(Q2>=70,S2<=10,C2="女"),"北一女","加油!!"))

ROUND, ROUNDUP, ROUNDDOWN, INT
01_Excel_學生成績單_02，下載

稱謂
=LEFT(B2,1)&"同學"&RIGHT(B2,2)

郵件帳號
=LEFT(D2,FIND("@",D2)-1)"))

郵件域名
=RIGHT(D2,LEN(D2)-FIND("@",D2))
=MID(D2,FIND("@",D2)+1,LEN(D2)-FIND("@",D2))

01_Excel_學生成績單_03，下載

Excel高階函數應用專題班_第4期

範例下載，Download

2013年11月14日星期四

儲存格位址練習

1.B53原來的
=COUNTIF(B2:B52,A53)

複製公式，先做水平方向，再做垂直方向

2.往右拖曳1格
=COUNTIF(C2:C52,B53) (X)
=COUNTIF(B2:B52,$A$53) (V) 固定 "喝"儲存格位址

繼續往右複製

3.往下拖曳1格(由C53)
=COUNTIF(C3:C53,$A$53) (X)
=COUNTIF(B2:B52,$A53) (V) 固定 "喝"儲存格位址，只需固定欄位址

4.B5目前的
=COUNTIF(B2:B52,$A53)

往下拖曳1格
=COUNTIF(B3:B53,$A54) (X)
=COUNTIF(B$2:B$52,$A53) (V) 固定COUNTIF的來源(第一欄)儲存格位址，只需固定列號位址

大功告成!! 嘻。

----
其他 B57
=COUNTA(B2:B52)-SUM(B53:B56)

2013年11月12日星期二

1113_Google文件分享

請點選連結，http://goo.gl/b77tbH

Google表單範例：

期末謝師宴滿意度意見蒐集

0903_父親節團購單

2013年8月8日星期四

合併列與OFFSET

OFFSET函數，Download

排班表

=IF(MOD(7,B5)=0,TRUE,FALSE)

=IF(OR(B5=1,B5=7),TRUE,FALSE)

兩個檔案下載，Download

期末謝師宴調查

07_期末謝師宴調查_0808.xlsx，Download

週薪計算

=TEXT(B2,"mm/dd")&"上班，共"&B10&"小時"

30小時以內(含30)
=IF(F12>=30,30*B19,F12*B19)

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

40小時以上
=IF(F12>40,(F12-40)*B21,0)

兩個檔案下載，Download
03_Excel_計算週薪_0425.xls
03_Excel_計算週薪_0808.xls

2013年8月7日星期三

決賽入圍名單

次數分配表
={FREQUENCY(B5:B51,G2:G5)}

步驟：
1.原始資料
2.區間值
3.選取顯示次數儲存格範圍
4.輸入公式
=FREQUENCY(B5:B51,G2:G5)
5.游標停在公式內，按Ctrl+Shift+Enter(輸入陣列公式)

04_Excel_決賽入圍名單_0808.xlsx，Download

學生成績單

排除不及格總分
=SUMIF(F2:O2,">=60")

排除不及格平均
=SUMIF(F2:O2,">=60")/(COUNT(F2:O2)-COUNTIF(F2:O2,"<60"))

01_Excel_學生成績單_0808.xls，Download

英文名***
01_Excel_學生成績單_0808_2.xls，Download

First-空格
=FIND(" ",A2)

Second-空格
=FIND(" ",A2,B2+1)

First-Name
=LEFT(A2,B2-1)

Second-Name
=MID(A2,B2+1,C2-B2-1)

Third-Name
=RIGHT(A2,LEN(A2)-C2)

First-***
=LEFT(D2,LEN(D2)-3)&"***"

Second-***
=MID(A2,B2+1,C2-B2-1-3)&"***"

Third-***
=MID(A2,C2+1,LEN(A2)-C2-3)&"***"

完整名
=G2&" "&H2&" "&I2

日記帳_資料分析彙整

02_Excel_管理日記帳_0808.xls，Download

02_Excel_管理日記帳_0808_2.xls，Download

第三期範例下載

Samples_0808.zip，Download

2013年8月6日星期二

管理日記帳

年
=YEAR(A2)

月
=MONTH(A2)

日
=DAY(A2)

星期
=WEEKDAY(A2,1)

小計
=SUBTOTAL(9,J2:J427)

資料庫函數 DSUM
=DSUM(日記帳!A1:J427,日記帳!J1,A12:A15)

02_Excel_管理日記帳_0806.xls，Download

2013年8月5日星期一

學生成績單

SUM
AVERAGE
COUNT
MAX
MIN
班上同學有幾人COUNTA
RANK
R2=RANK(P2,$P$2:$P$13)
S2=RANK(P2,總分)

排序法比名次

1.保留原來順序，建立編號欄
2.比大小，依照總分或平均排序 (遞減)
3.新增名次欄，輸入名次數字，1:12
4.再依編號欄排 (遞增)

前三名
T2=IF(R2<=3,"妳好棒!","")

甄選一
Z2=IF(Y2<=1,IF(S2<=3,"高興!","加油!"),"歹勢!")

01_Excel_學生成績單_0806.xls，Download

由學號代姓名，學生基本資料工作表
B2=VLOOKUP(A2,期中考!$A$2:$B$13,2,FALSE)

等級，新增查表範圍 (查表工作表)

0	60	70	80	90
E	D	C	B	A

AA2=HLOOKUP(Q2,查表!$A$1:$E$2,2,TRUE)

---
單名與複姓

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

名字
=IF(LEN(A2)=2,RIGHT(A2,1),RIGHT(A2,2))

---
@位址
F2=FIND("@",E2)

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

網址
H2=RIGHT(E2,LEN(E2)-F2)
H2=RIGHT(E2,LEN(E2)-FIND("@",E2))

網址2
=MID(E2,FIND("@",E2)+1,LEN(E2)-FIND("@",E2))

通訊地址1
=M2&K2&L2&MID(J2,4,LEN(J2)-3)

通訊地址2
=CONCATENATE(M2,K2,L2,MID(J2,4,LEN(J2)-3))

---
01_Excel_學生成績單_0806_2.xls，Download

Excel高階函數應用專題班_第3期

Excel高階函數應用專題班_第3期_CaseStudy，Download

意見蒐集Google表單，連結

2013年7月10日星期三

0711課程

期末謝師宴調查_分享_0709.zip ，Download

=IF(ISERROR(VLOOKUP(E2,表單回應!$D$2:$E$25,2,FALSE))=TRUE,"X","V")

興趣資料彙整完成版，Download

2013年7月8日星期一

期末教師謝師宴調查

各位老師：

請填寫，連結

資料分析下載，Download

學員資料，下載

2013年6月5日星期三

0605_決賽入圍名單

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

E2=HLOOKUP(A2,積分表!$B$1:$E$2,2,TRUE)

04_Excel_決賽入圍名單_060501，Download

次數分配表
G5:G8={FREQUENCY(B5:B51,F5:F8)}

04_Excel_決賽入圍名單_060502，Download

0605_日記帳管理

B11=DSUM(日記帳!A1:F427,日記帳!F1,A10:A11)

C2=VLOOKUP(E2,查表!$A$1:$B$12,2,FALSE)

D2=HLOOKUP(E2,查表!$D$1:$G$2,2,TRUE)

02_Excel_管理日記帳_060503，Download

0605_學習成績單

V2=IF(R2<=5,IF(COUNTIF(F2:O2,"<60")<1,IF(G2>=97,"直升台大","保送建中"),"再加油!"),"")

01_Excel_學生成績單_060405，Download

2013年6月3日星期一

0604_學習成績單

01_Excel_學生成績單_060401，Download

F2=LEFT(E2,FIND("@",E2)-1)
G2=RIGHT(E2,LEN(E2)-FIND("@",E2))
H2=MID(E2,FIND("@",E2)+1,LEN(E2)-FIND("@",E2))

01_Excel_學生成績單_060402，Download

B2=LEFT(A2,2)&"年級，學號"&RIGHT(A2,3)
C2=CONCATENATE(LEFT(A2,2),"年級，學號",RIGHT(A2,3))

S2=IF(R2<=5,"進入甄選","")
T2=COUNTIF(F2:O2,"<60")
U2=IF(R2<=5,IF(COUNTIF(F2:O2,"<60")<1,"保送建中","再加油!"),"")

01_Excel_學生成績單_060403，Download

01_Excel_學生成績單_060404，Download

02_Excel_管理日記帳_060401，Download

2013年5月28日星期二

通訊錄下載

請下載通訊錄，Download

Google表單蒐集

請點選連結，輸入個人姓名、電子郵件!

2013年5月27日星期一

Google雲端服務

下載講義，Download

2013年4月25日星期四

課後提醒與邀請

在這裡先列出課程最後，需要回覆給大家的問題，以免我忘記了，首先是週薪計算，將巢狀IF、水平VLOOUP與陣列公式的整合應用，然後是排班表，重新計算加班與各請假別的時薪彙整，計算當月薪資，以上!

歡迎加入Facebook社團。
https://www.facebook.com/groups/177614625728117/

2013年4月24日星期三

第二天課程

範例及講義下載，Download

02_Excel_管理日記帳_結果01，Download
02_Excel_管理日記帳_結果02，Download
03_Excel_計算週薪_結果_0425，Download
05_Excel_排班表_結果_0425，Download

2013年4月23日星期二

決賽入圍名單

04_Excel_決賽入圍名單_結果，Download

2013年4月22日星期一

學生成績單試算

01_Excel_學生成績單_結果 Download

加總 SUM
P2=SUM(F2:O2)

平均 AVERAGE
Q2=AVERAGE(F2:O2)

最高分 MAX
E14=MAX(F2:F13)

最低分 MIN
E15=MIN(F2:F13)

名次(方法一) RANK+儲存格範圍(參照)
R2=RANK(Q2,$Q$2:$Q$13)

名次(方法二) RANK+名稱
S2=RANK(Q2,平均)

操作小技巧(分辨)：
複製儲存格 vs. 複製公司內容

80-90 Q15
R15=COUNTIF(平均,">80")-COUNTIF(平均,">90")
70-80 Q16
R16=COUNTIF(平均,">70")-COUNTIF(平均,">80")
60-70 Q17
R17=COUNTIF(平均,">60")-COUNTIF(平均,">70")

次數分配表(2)
選取R19:22，建立FREQUENCY陣列公式
{=FREQUENCY(Q2:Q13,Q19:Q22)}

輸入陣列公式 Ctrl+Shift+Enter

基測一 IF
U2=IF(Q2>=83,"晉級","")

基測二巢狀IF
V2=IF(Q2>=83,"晉級",IF(Q2>=75,"候補",""))

---
姓名二 C1
C2=VLOOKUP(A2,期中考!$A$1:$B$13,2,FALSE)

姓氏1 D1
D2=LEFT(B2,1)

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

名字1 F1
F2=RIGHT(B2,2)

名字2 G1
G2=IF(LEN(B2)=2,RIGHT(B2,1),RIGHT(B2,2))

@位址 I1
I2=FIND("@",H2)

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

網址 K1
K2=MID(H2,FIND("@",H2)+1,LEN(H2)-FIND("@",H2))

E-Mail2 L1
L2=CONCATENATE(J2,"_at_",K2)

E-Mail3 M1
M2=J2&"_at_"&K2

Samples Download

Excel高階函數應用專題班_第1期_CaseStudy，Download

意見蒐集Google表單，連結

日期	星期	時間	課程	講座
上課地點：			E區綜合教學大樓E304電腦教室
04/23	二	0840~0910	報到暨班務說明	教務組
04/23	二	0920~1010	公式與函數	張大明老師
04/23	二	1020~1110	問題模型與解決流程初階	張大明老師
04/23	二	1120~1530	實務案例：學生成績彙整(日期時間、邏輯、文字、數值等函數)	張大明老師
04/23	二	1540~1630	實務案例：個人理財應用(財務函數)	張大明老師
04/25	四	0920~1010	問題模型與解決流程進階	張大明老師
04/25	四	1020~1430	實務案例：報表分析應用（查閱與參照函數）	張大明老師
04/25	四	1440~1630	實務案例：工作流程管理(資訊、資料庫函數)	張大明老師

訂閱：文章 (Atom)

2013年12月4日 星期三

2013年12月2日 星期一

2013年11月14日 星期四

2013年11月12日 星期二

2013年8月8日 星期四

2013年8月7日 星期三

2013年8月6日 星期二

2013年8月5日 星期一

2013年7月10日 星期三

2013年7月8日 星期一

2013年6月5日 星期三

2013年6月3日 星期一

2013年5月28日 星期二

2013年5月27日 星期一

2013年4月25日 星期四

2013年4月24日 星期三

2013年4月23日 星期二

2013年4月22日 星期一