[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

普通的做法只有該欄位會變色

Excel_001

Excel_002

可是利用自訂公式後，可以變成整列

=EXACT($E1,"可")
or
=EXACT($E1,$E$2)

Excel_003

Google Sheet

而在Google Sheet上，普通的方式如下:

Sheet_001

改成自訂公式後，

=$E:$E=$E$2
or
=$E:$E="可"

Sheet_002

隔行隔列變更顏色 (Excel 與 Google Sheet相同)

=MOD(ROW(),2)=0，偶數行自動填充底色
=MOD(ROW(),2)=1，奇數行自動填充底色
=MOD(COLUMN(),2)=0，偶數列自動填充底色
=MOD(COLUMN(),2)=1，奇數列自動填充底色

Excel_006

Excel_007

去除字串中的空格

消除半型空白 (Excel 與 Google Sheet 相同)

=SUBSTITUTE(B2," ","")

Excel_004

消除全型空白 (Excel 與 Google Sheet 相同)

(將消除半型空格後的結果，再消除全型空格)

=SUBSTITUTE(SUBSTITUTE(B2," ",""),"　","")

Excel_005

計算數量

條件型

COUNTIF的用法 (Excel 與 Google Sheet 相同)

=COUNTIF([搜尋的範圍, 條件]) 如果是字串的話需要加上""號
or
=COUNTIF(C:C,"Asus*")

Excel_009

COUNTA的用法 (Excel 與 Google Sheet 相同)

COUNT是計算範圍中包含數字的儲存格數量的方法

COUNTA是計算範圍中非空白儲存格數量的方法

=COUNT(數字型的儲存格範圍)-COUNTA(空白型的儲存格範圍)
or
=COUNT(A2:A6)-COUNTA(G2:G6)

Excel_010

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

Excel_011

Excel_012

Excel_013

Google Sheet

與Excel稍稍不同的地方在於處理中文字的時候，Excel不需要加上2個'單引號就能夠讀取不同工作表上的內容，但如果是Google Sheet工作表是中文的話就要加上2個單引號

=COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"")
=COUNTIFS('保固表'!A:A,T(A4),'保固表'!H:H,"V")

Sheet_003

加總

SUMIF的用法 (Excel 與 Google Sheet 相同)

SUMIF是條件符合的儲存格才進行加總

假設我有一張表格如下圖:

Excel_014

我想要把相同項目但是欄位不同的數量相加總起來，可以利用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)

Excel_015

特殊符號用法

驚嘆號 ( ! )

將其他分頁儲存格上的資料同步複製過來

={Sheet's Name}!A2

Excel_008

Add a Comment