[Office] Microsoft Office Excel and Google Sheet Formula Example Record
Posted On 2018-07-23
[Office] Microsoft Office Excel and Google Sheet Formula Example Record
條件式格式化
根據欄位條件，整列變色
重點是使用$絕對位置，利用相對位置會無法正確套用，但有趣的是Office Excel上的公式設定和Google Sheet上完全不同，以下列出公式不同的地方:
Excel
普通的做法只有該欄位會變色
可是利用自訂公式後，可以變成整列
|
1 2 3 4 |
=EXACT($E1,"可") or =EXACT($E1,$E$2) |
Google Sheet
而在Google Sheet上，普通的方式如下:
改成自訂公式後，
|
1 2 3 4 |
=$E:$E=$E$2 or =$E:$E="可" |
隔行隔列變更顏色 (Excel 與 Google Sheet相同)
|
1 2 3 4 5 |
=MOD(ROW(),2)=0，偶數行自動填充底色 =MOD(ROW(),2)=1，奇數行自動填充底色 =MOD(COLUMN(),2)=0，偶數列自動填充底色 =MOD(COLUMN(),2)=1，奇數列自動填充底色 |
去除字串中的空格
消除半型空白 (Excel 與 Google Sheet 相同)
|
1 2 |
=SUBSTITUTE(B2," ","") |
消除全型空白 (Excel 與 Google Sheet 相同)
(將消除半型空格後的結果，再消除全型空格)
|
1 2 |
=SUBSTITUTE(SUBSTITUTE(B2," ","")," ","") |
計算數量
條件型
COUNTIF的用法 (Excel 與 Google Sheet 相同)
|
1 2 3 4 |
=COUNTIF([搜尋的範圍, 條件]) 如果是字串的話需要加上""號 or =COUNTIF(C:C,"Asus*") |
COUNTA的用法 (Excel 與 Google Sheet 相同)
COUNT是計算範圍中包含數字的儲存格數量的方法
COUNTA是計算範圍中非空白儲存格數量的方法
|
1 2 3 4 |
=COUNT(數字型的儲存格範圍)-COUNTA(空白型的儲存格範圍) or =COUNT(A2:A6)-COUNTA(G2:G6) |
COUNTIFS的用法
Excel
COUNTIFS是計算範圍中多種條件同時都符合的數量的方法，如果該儲存格的值是利用判斷得來的True or False的話，相比的時候就可以不用T()函數，因為T( )是用來判別是否是文字用的
|
1 2 3 4 5 6 |
=COUNTIFS(NB!B:B,T(B2), NB!G:G,G2) =COUNTIFS(螢幕!B:B,T(B9), 螢幕!G:G, G11) =COUNTIFS(NB!B:B,T(B2), NB!G:G, "FALSE") =COUNTIFS(螢幕!B:B,T(B9), 螢幕!G:G, "FALSE") |
Google Sheet
與Excel稍稍不同的地方在於處理中文字的時候，Excel不需要加上2個’單引號就能夠讀取不同工作表上的內容，但如果是Google Sheet工作表是中文的話就要加上2個單引號
|
1 2 3 |
=COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"") =COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"V") |
加總
SUMIF的用法 (Excel 與 Google Sheet 相同)
SUMIF是條件符合的儲存格才進行加總
假設我有一張表格如下圖:
我想要把相同項目但是欄位不同的數量相加總起來，可以利用SUMIF的函式，
|
1 2 3 4 |
=SUMIF(Every!E:E, "Adobe Acrobat*", Every!F:F) =SUMIF(Every!E:E, "Bandizip*", Every!F:F) =SUMIF(Every!E:E, "Dropbox*", Every!F:F) |
特殊符號用法
驚嘆號 ( ! )
將其他分頁儲存格上的資料同步複製過來
|
1 2 |
={Sheet's Name}!A2 |
