[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
普通的做法只有該欄位會變色
可是利用自訂公式後,可以變成整列
=EXACT($E1,"可")
or
=EXACT($E1,$E$2)
Google Sheet
而在Google Sheet上,普通的方式如下:
改成自訂公式後,
=$E:$E=$E$2
or
=$E:$E="可"
隔行隔列變更顏色 (Excel 與 Google Sheet相同)
=MOD(ROW(),2)=0,偶數行自動填充底色
=MOD(ROW(),2)=1,奇數行自動填充底色
=MOD(COLUMN(),2)=0,偶數列自動填充底色
=MOD(COLUMN(),2)=1,奇數列自動填充底色
去除字串中的空格
消除半型空白 (Excel 與 Google Sheet 相同)
=SUBSTITUTE(B2," ","")
消除全型空白 (Excel 與 Google Sheet 相同)
(將消除半型空格後的結果,再消除全型空格)
=SUBSTITUTE(SUBSTITUTE(B2," ","")," ","")
計算數量
條件型
COUNTIF的用法 (Excel 與 Google Sheet 相同)
=COUNTIF([搜尋的範圍, 條件]) 如果是字串的話需要加上""號
or
=COUNTIF(C:C,"Asus*")
COUNTA的用法 (Excel 與 Google Sheet 相同)
COUNT是計算範圍中包含數字的儲存格數量的方法
COUNTA是計算範圍中非空白儲存格數量的方法
=COUNT(數字型的儲存格範圍)-COUNTA(空白型的儲存格範圍)
or
=COUNT(A2:A6)-COUNTA(G2:G6)
COUNTIFS的用法
Excel
COUNTIFS是計算範圍中多種條件同時都符合的數量的方法,如果該儲存格的值是利用判斷得來的True or False的話,相比的時候就可以不用T()函數,因為T( )是用來判別是否是文字用的
=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個單引號
=COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"")
=COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"V")
加總
SUMIF的用法 (Excel 與 Google Sheet 相同)
SUMIF是條件符合的儲存格才進行加總
假設我有一張表格如下圖:
我想要把相同項目但是欄位不同的數量相加總起來,可以利用SUMIF的函式,
=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)
特殊符號用法
驚嘆號 ( ! )
將其他分頁儲存格上的資料同步複製過來
={Sheet's Name}!A2