第五天，回程
 
慵懶地在旅館度過悠哉的早晨，之後在旋轉餐廳用下午茶即返台
 
精選幾張照片如下
 
房間遠眺


George, Cindy & Jane


 
泳池畔 with Stanley


 
自拍


 
Coco

 
同事合照


 
Judith & Seal


 
Coco & Cindy

椰子樹下

 
旋轉餐廳

 
更多照片，請詳http://cid-9a537a69f7dcf377.skydrive.live.com/browse.aspx/%7C597%7C61103%20%e6%b2%99%e5%b7%b4%e8%a1%8c
(繼續閱讀...)

piny 發表在 痞客邦 留言(1) 人氣()

▲top

第十二課　Choose

 

Choose(參照值，參照值為1時的動作，參照值為2時的動作，...)

 

該函數至少有二個引數，至多三十個，第一個引數為參照值，第二個引數為參照值為1時之執行動作，第三個引數為參照值為2時之執行動作，以此類推

 






























































例１，輸入春夏秋冬，將分別輸出ABCD
冬
D
　
=CHOOSE(FIND(A2,"春夏秋冬"),"A","B","C","D")
說明

　
公式同
=MID("ABCD",FIND(A2,"春夏秋冬"),1)
=VLOOKUP(A2,{"春","A";"夏","B";"秋","C";"冬","D"},2,)
=HLOOKUP(A2,{"春","夏","秋","冬";"A","B","C","D"},2,)
=INDEX({"A","B","C","D"},FIND(A2,"春夏秋冬"))
=INDEX({"A","B","C","D"},MATCH(A2,{"春","夏","秋","冬"},))
　


例２，輸入一數字，判定其為奇數或偶數
5
奇數
=CHOOSE(MOD(A13,2)+1,"偶數","奇數")
說明

　
公式同
=IF(MOD(A12,2),"奇數","偶數")



例３，輸入一數字，判定其是否大於100
378
大於100囉
　
=CHOOSE((A21>100)+1,"沒有大於100","大於100囉")
說明

　
公式同
=IF(A20>100,"大於100囉","沒有大於100")



例４，輸入一數字，判定其為幾位數（限六位數字）
1245
四位數
　
=CHOOSE(LEN(A30),"一位數","兩位數","三位數","四位數","五位數","六位數")
說明

　
公式同
=CHOOSE(LEN(A28),"一","兩","三","四","五","六")&"位數"




例５，輸入一數字，判定其數字和是否大於10
67
大於10囉
　
=CHOOSE((SUMPRODUCT(--MID(A39,ROW(INDIRECT("1:"&LEN(A39))),1))>10)+1,"沒有大於10","大於10囉")
說明

　
進階應用



例６，輸入一數字，判定其是否為三的倍數
4
不是三的倍數
　
=CHOOSE((MOD(A49,3)>0)+1,"三的倍數","不是三的倍數")
說明

　
進階應用

 

 
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第十一課　Large與Small

 

Large(範圍，第幾大)
Small(範圍，第幾小)

 

這兩個函數是計算範圍中之第幾大和第幾小，其中，邏輯值、空白值及文字皆會忽略

 






















































































100


　


呼


TRUE


@@@


-20


1


0


50


50





例１，Large的基本用法
1
100
=LARGE($A$1:$A$10,A13)
2
50
=LARGE($A$1:$A$10,A14)
3
50
=LARGE($A$1:$A$10,A15)
4
1
=LARGE($A$1:$A$10,A16)
5
0
=LARGE($A$1:$A$10,A17)
6
-20
=LARGE($A$1:$A$10,A18)
7
#NUM!
=LARGE($A$1:$A$10,A19)
8
#NUM!
=LARGE($A$1:$A$10,A20)
9
#NUM!
=LARGE($A$1:$A$10,A21)
10
#NUM!
=LARGE($A$1:$A$10,A22)
說明

　
１．數值相同時（如：50），則依序指定
２．由於範圍中僅六個數值（邏輯值、空白及文字皆會忽略），故第七大至第十大則顯示錯誤
　


例２，Small的基本用法
1
-20
=SMALL($A$1:$A$10,A30)
2
0
=SMALL($A$1:$A$10,A31)
3
1
=SMALL($A$1:$A$10,A32)
4
50
=SMALL($A$1:$A$10,A33)
5
50
=SMALL($A$1:$A$10,A34)
6
100
=SMALL($A$1:$A$10,A35)
7
#NUM!
=SMALL($A$1:$A$10,A36)
8
#NUM!
=SMALL($A$1:$A$10,A37)
9
#NUM!
=SMALL($A$1:$A$10,A38)
10
#NUM!
=SMALL($A$1:$A$10,A39)
說明

　
１．數值相同時（如：50），則依序指定
２．由於範圍中僅六個數值（邏輯值、空白及文字皆會忽略），故第七小至第十小則顯示錯誤



例３，僅計算A47至A56中前三大之平均值
100
666.67
　
200

　
300

　
500

　
400

　
300

　
200

　
100

　
800

　
700

　
=(LARGE($A$47:$A$56,1)+LARGE($A$47:$A$56,2)+LARGE($A$47:$A$56,3))/3
說明

　
公式同
=SUM(LARGE($A$47:$A$56,{1,2,3}))/3



例４，有幾個正值呢(數列需有零值)
-2
5
　
-5

　
0

　
0

　
0

　
300

　
200

　
100

　
800

　
700

　
=MATCH(TRUE,LARGE($A$65:$A$74,ROW(1:10))=0,)-1
說明

　
公式同(數列不需有零值)
=COUNTIF($A$65:$A$74,">"&0)
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第十課　Substitute

 

若僅將參照值出現之第幾個尋找字串予以取代，則第四格引數需註明
Substitute(參照值，欲尋找的字串，欲取代的字串，欲僅取代第幾個)

 

若需要將參照值出現之尋找字串都予以取代，則第四格引數含前面逗號皆省略即可，如
Substitute(參照值，欲尋找的字串，欲取代的字串)

 












































例１，基本介紹
甲甲甲甲
乙乙乙乙
=SUBSTITUTE(A2,"甲","乙")
　
甲甲甲甲
乙甲甲甲
=SUBSTITUTE(A3,"甲","乙",1)
　
甲甲甲甲
甲乙甲甲
=SUBSTITUTE(A4,"甲","乙",2)
　
甲甲甲甲
甲甲乙甲
=SUBSTITUTE(A5,"甲","乙",3)
　
甲甲甲甲
甲甲甲甲
=SUBSTITUTE(A6,"甲","乙",5)
　
說明


　
=SUBSTITUTE(A2,"甲","乙")，則該儲存格的「甲」都會被換成「乙」呦！
=SUBSTITUTE(A2,"甲","乙",1)，則為該儲存格的第一個「甲」會被換成「乙」！(其他則不置換)
=SUBSTITUTE(A2,"甲","乙",2)，則為該儲存格的第二個「甲」會被換成「乙」！(其他則不置換)
=SUBSTITUTE(A2,"甲","乙",5)，則為該儲存格的第五個「甲」會被換成「乙」！(由於甲只有四個，故無任何一個甲會被置換)
　


　
例２，計算「甲」出現了幾次
甲甲甲乙甲丙甲
5
=LEN(A10)-LEN(SUBSTITUTE(A10,"甲",""))
說明


　
把甲置換成空白字元後，再計算與原來字元長度差異，即為「甲」出現了幾次




例３，計算「甲」和「乙」共出現了幾次
甲甲甲乙甲丙甲
6

　
=LEN(A22)-LEN(SUBSTITUTE(SUBSTITUTE(A22,"甲",""),"乙",""))
　
說明


　
SUBSTITUTE一次只可取代一組字串，惟可以合併使用




例４，輸入日期(日期格式為yyyy/m/d)，可傳回該日期是禮拜幾
2009/1/9
禮拜五
=SUBSTITUTE(TEXT(A31,"aaa"),"週","禮拜")
說明


　
與TEXT函數之進階應用
TEXT(A31,"aaa")，可傳回該日期為「週幾」
TEXT(A31,"aaaa")，可傳回該日期為「星期幾」
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第九課　Len與Lenb
 
Len(此參照值共有幾個字)
Lenb(此參照值共有幾個字元)

 

此兩個兄弟檔函數是計算字元用的，差別僅在於有無必要區分全形字元(即全形符號或中文字)，故若參照值並無全形字元，其實兩函數結論將一致。

 






































































例１，Len的應用
呼
1
=LEN(A2)
　
阿呼
2
=LEN(A3)
　
阿喜喜
3
=LEN(A4)
　
piny
4
=LEN(A5)
　
how are you
11
=LEN(A6)
　
ＰＩＮＹ
4
=LEN(A7)
　
＄＃＠％
4
=LEN(A8)
　
ＰＩＮＹ PINY
9
=LEN(A9)
　
說明


　
不管是全形字元或半形字元，皆視為一個字




例２，Lenb的應用
呼
2
=LENB(A2)
　
阿呼
4
=LENB(A3)
　
阿喜喜
6
=LENB(A4)
　
piny
4
=LENB(A5)
　
how are you
11
=LENB(A6)
　
ＰＩＮＹ
8
=LENB(A7)
　
＄＃＠％
8
=LENB(A8)
　
ＰＩＮＹ PINY
13
=LENB(A9)
　
說明


　
全形字元視為2個字，半形字元視為1個字




例３，判斷A32輸入多少全形字元
呼123
1
=LENB(A32)-LEN(A32)
　
說明


　
因為全形字元在LENB函數的計算會比LEN函數多一，故兩函數相減即為全形字元字數




例４，輸入兩位數至四位數之中文名字，基於保密性質隱藏部分名字
丁小雨
丁Ｘ雨

　
=CHOOSE(LEN(A40),,LEFT(A40)&"Ｘ",LEFT(A40)&"Ｘ"&RIGHT(A40),LEFT(A40,2)&"Ｘ"&RIGHT(A40))
說明


　
進階應用




例５，輸入多少字元就傳回多少特殊字元
1223
◎◎◎◎
=REPT("◎",LEN(A49))
　
說明


　
進階應用




例６，輸入XX股份有限公司，傳回XX(XX字元不限，且股份有限公司後面不會有字了)
台泥股份有限公司
台泥
=LEFT(A57,LEN(A57)-6)
　
說明


　
公式同
=MID(A57,1,FIND("股份有限公司",A57)-1)
=SUBSTITUTE(A57,"股份有限公司","")
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第八課　Right、Left與Mid

 

Right(參照值，從右數幾個字元)
Left(參照值，從左數幾個字元)
Mid(參照值，從第幾個開始數，共數幾個字元)

 

Right與Left之應用較一般，且皆可以Mid取代，故以下著重分享Mid函數的一些基本用法

 












































































例１，輸入TEJ公司名稱，將輸出股票代碼（僅限四位數之股票代碼）
1201       味全
1201
=MID(A2,1,4)
說明

　
基本用法



例２，輸入TEJ公司名稱，將輸出股票代碼（僅限四位數或五位數之股票代碼）
11933      嘉環東泥
11933
　
=MID(A10,1,IF(MID(A10,5,1)=" ",4,5))
　
說明

　
可以與IF函數應用



例３，輸入email，將輸出＠前之名稱
aaa@xxxx.com.tw
aaa
=MID(A19,1,FIND("@",A19)-1)
說明

　
可以與Find函數應用



例４，輸入網址，將輸出不含"http://"及"/"之內容
http://tw.news.yahoo.com/
tw.news.yahoo.com
=MID(A27,8,LEN(A27)-8)
說明

　
可以與Len函數應用



例５，輸入任意字元之英文字後加上任意字元之數字，將輸出數字部分
ABCF12358
12358
　
=MID(A35,MATCH(TRUE,ISNUMBER(--MID(A35,ROW(1:20),1)),),99)
說明

　
公式同
=MID(A35,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A35)),"",FIND({0,1,2,3,4,5,6,7,8,9},A35))),99)



例６，輸入任意字元之英文字後加上任意字元之數字，將輸出英文部分
asderfgtyhu58
asderfgtyhu
　
=MID(A44,1,MATCH(TRUE,ISNUMBER(--MID(A44,ROW(1:20),1)),)-1)
說明

　
公式同
=MID(A44,1,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A44)),"",FIND({0,1,2,3,4,5,6,7,8,9},A44)))-1)
=LEFT(A44,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A44)),"",FIND({0,1,2,3,4,5,6,7,8,9},A44)))-1)
=LEFT(A44,MIN(FIND({1;2;3;4;5;6;7;8;9;0},A44&"0123456789"))-1)



例７，輸出最右字元
asdfad
d
=RIGHT(A53)
說明

　
RIGHT(A53,1)=RIGHT(A53)



例８，輸入春夏秋冬，依序代表ABCD
春
A
　
=MID("ABCD",FIND(A61,"春夏秋冬"),1)
　
說明

　
MID函數也可以用來參照呦！
(繼續閱讀...)

piny 發表在 痞客邦 留言(1) 人氣()

▲top

第七課　連結字元與空白字元

 

今天來簡介此兩個常用字元

 

連結字元，連結文字與公式用，符號為「&」
空白字元，即顯示空白，符號為「""」，更一般解釋，「"」與「"」間所嵌入之任何形式皆會視為文字處理，故若中間無加入任何字元，當然就顯示空白囉

 









































































例１，連結A2與A3儲存格
我
我你
=A2&A3
　
你


　
說明


　
&可直接連結不同儲存格之文字




例２，A11輸入小於200之整數會轉換成民國XX年，輸入大於1000之整數會轉換成西元XXXX年
97
1000,"西元"&A11&"年","未設定"))">民國97年

　
=IF(A11<200,"民國"&A11&"年",IF(A11>1000,"西元"&A11&"年","未設定"))
說明


　
連結字元與空白字元之活用




例３，數字型態轉換成文字型態
123
123
=A19&""
　
說明


　
數字型態轉換成文字型態的方法之一




例４，A27輸入「春夏秋冬」，A28輸入１或２，B27可帶出參照值
春
e

　
2


　
=INDEX(C35:C42,MATCH(A27&A28,A35:A42&B35:B42,))
　
說明


　
雙參照值的活用，需陣列輸入




春
1
a

夏
1
b

秋
1
c

冬
1
d

春
2
e

夏
2
f

秋
2
g

冬
2
h





例５，A45輸入35至42之整數，可傳回A35至A42之值
35
春
=INDIRECT("A"&A45)
說明


　
INDIRECT函數的基本用法




例６，輸入任意三位數，判斷是否中了特獎或頭獎
123
中特獎囉

　
=IF(A53=B60,"中特獎囉",IF(COUNTIF(B61:B64,A53),"中頭獎囉","再接再厲"))
說明


　
「"」的基本用法




特獎
123


頭獎
345


頭獎
256


頭獎
258


頭獎
124

(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第六課　Row與Column（二）

 

Row與Column應用最廣泛之處即為可以製造任意形式的數列，只要有規則可循都可以達到，茲舉下列數例供研習之！

 

Row有一題練習題，供大家練習，題目需求

 

在Q3寫 一個 公式，往下填滿後，可製造出1,2,3,2,1,2,3,2,1,2,3,2,...之數列

 

有興趣者可於寫好答案之後，自行寄給我或Ｐ君，一週後公佈各式解答！

 












































Row的應用








例一
數列加一遞增


=ROW(A1)




例二
奇數列遞增


=ROW(A1)*2-1




例三
五的倍數


=ROW(A1)*5




例四
每個值重複三次


=ROUNDUP(ROW(A1)/3,0)




例五
一至五依序


=IF(MOD(ROW(A1),5),MOD(ROW(A1),5),5)




Column的應用







例一
數列加三遞增


=COLUMN(A1)*3-2




例二
偶數列遞增


=COLUMN(A1)*2




例三
三的倍數


=COLUMN(A1)*3




例四
偶數重複兩次


=IF(MOD(COLUMN(A1),2),COLUMN(A1)+1,COLUMN(A1))




例五
1與0


=MOD(COLUMN(A1),2)




例六
0與1


=MOD(COLUMN(A1)+1,2)



 
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top
第五課　Row與Column
 
Row與Column為Excel中應用非常廣泛的基本函數
 
茲簡述如下
 














Row為指定格之所在列數，應用如下

Column為指定格之所在欄數，應用如下
=ROW(1:1)
第一列的row為1


=COLUMN(A:A)
A欄的column為1
=ROW(5:5)
第五列的row為5


=COLUMN(H:H)
H欄的column為8
=ROW(A1)
A1的row為1


=COLUMN(A1)
A1的column為1
=ROW(E5)
E5的row為5


=COLUMN(E5)
E5的column為5
=ROW()
所在格的row為19


=COLUMN()
所在格的column為5

 
其中，ROW()與COLUMN()會因所在格不同而傳回不同值，而公式往下或往右填滿時，其亦會根據參照性質因應，進階應用情形將待後續講解！
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第四課　文字型態與數字型態（二）

 

先前提過，邏輯值亦可視為數字，惟需經過轉換

 

以下先介紹基本概念，邏輯值在做四則運算時會自動視為１和０，另一個常用的方法就是「--」

 

「--」就是將文字型態的數字或邏輯值轉換為數字

 













































100
數字型態的100



300
數字型態的300



100
文字型態的100



300
文字型態的300



TRUE
邏輯值



FALSE
邏輯值








如何轉換成文字型態



法一
=A3&""
加一個空白值給它就是文字了

法二
=""&A3
空白值亦可加前







如何轉換成數字型態



法一
=A5+0
利用四則運算後依然為本身之算式皆可使文字型態的數字或邏輯值轉換成數字
法二
=A5-0
法三
=A5*1
法四
=A5/1
法五
=A5^1
法六
=--A5
常用技巧，背起來吧

法七
=VALUE(A5)
顯示該值之數值，惟邏輯值無法轉換






再介紹如何輸入數字時直接視為文字








常用方法有兩種









方法一




先輸入「’」









方法二




將該儲存格改為文字


(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top

第三課　文字型態與數字型態


 

文字型態就是儲存格之格式視為文字
數字型態就是儲存格之格式視為數字

 

而易讓人混淆的主要為數字可為文字型態，也可為數字型態。因為兩者並不相同，故在此使用vlookup等參照函數會因為型態問題而無法參照成功。



































100
數字型態的100



300
數字型態的300



100
文字型態的100



300
文字型態的300








如何轉換成文字型態



法一
=A3&""
加一個空白值給它就是文字了

法二
=""&A3
空白值亦可加前







如何轉換成數字型態



法一
=A5+0
利用四則運算後依然為本身之算式皆可達到相同要求
法二
=A5*1
利用四則運算後依然為本身之算式皆可達到相同要求
法三
=--A5
常用技巧，背起來吧

法四
=VALUE(A5)
顯示該值之數值


法五
=A5^1
利用四則運算後依然為本身之算式皆可達到相同要求
法六
=A5-0
利用四則運算後依然為本身之算式皆可達到相同要求
法七
=A5/1
利用四則運算後依然為本身之算式皆可達到相同要求
(繼續閱讀...)

piny 發表在 痞客邦 留言(1) 人氣()

▲top

第二課　F9與F4

 

今天來簡介兩個常用的功能鍵，F9與F4

 

F9

 

重新計算公式

 

選項中關於計算方式主要有「自動」及「手動」兩種，若為「自動」，則所有儲存格在輸入後即自動產生正確結果，惟若檔案公式眾多，則該種計算方式將明顯拖累工作效率，甚者連篩選等僅改變顯示畫面等動作亦會重新計算，故此時則建議改以「手動」進行檔案編製。

 

因此，在「手動」情況下（前提是存檔前自動重算需勾選），則如何令工作表聰明地重算亦為常用技巧，以下兩種是小弟較常利用的，謹分享之。

 

按 F9 是「所有檔案」有變更過的格子重算（也就是說，若開啟了十個檔案，剛好都有變更，則按F9會讓十個檔案都重算呦）
 
按 Shift + F9 是使用中之活頁重算（這個會是最快，不過僅記得未使用之活頁皆未重算，若該活頁有參照其他活頁，而其他活頁尚有公式得重算，則結論可能失真）

 

顯示公式中之選定部分

 

在計算公式部分中選定部分，按 F9 可以顯示選定部分之計算值（請注意公式需完整選取，並注意左右括號是否對稱）

 

F4

 

於相對、絕對和混合參照間來回切換

 

在計算公式部分中選定部分，按 F4 可以來回切換。
(繼續閱讀...)

piny 發表在 痞客邦 留言(0) 人氣()

▲top
1 2 3 4 5
Blog Stats
⚠️

成人內容提醒

本部落格內容僅限年滿十八歲者瀏覽。
若您未滿十八歲，請立即離開。

已滿十八歲者，亦請勿將內容提供給未成年人士。