NOTE

用EXCEL寫日記

W2 108-欄列的鎖定(F4)、公式設定
W3 104-儲存格格式設定0"歲"、Ctrl+Shift+下、COUNTIF(range, criteria)  | 110-欄位移動、走勢圖、設定的格式化條件

[教學備忘]
210 修正溢位：使用範圍名稱建立公式、出現欄框表示使用動態函數中。 轉表格會出現溢位，=@ (實際為名稱前皆加上@) 此步驟為中斷溢位 或 中斷溢出範圍。使用如XLOOKUP也為使用動態函數的情境。

208  僅顯示小於等於50的值，儲存格自訂格式  [<=50]0;  

302  yyyy/mm/ddd(aaa)    

301 XY散佈圖+泡泡圖(廣告費,銷售金額,市佔率)

[Pivot Analysis]
1.來源表格日期正確(無錯誤日期)，樞紐可顯示日期階層。

[中華民國年轉西元年與格式修正]
1120601  >>  19110000+貼上值+資料剖析-日期+修改為日期

[儲存格格式設定]
0：所有數字     #：數字、與 0相近但不顯示無數學意義之 0 。
儲存格格式設定為 $#,##0.00：前置$ 且每三位數加入,   小數兩位做四捨五入， # 和 0 表示為數字， # 不顯示數學上無意義的 0 ，例如095顯示為 95 ，5.10 顯示 5.1。(可試作格式以全部採0的差異)
ex1. $#,##0.00：輸入25000.005 --> $25000.01
ex2. $ #,##0.00 "萬";;  ;;將隱藏負數或零值   ex3. "NTD" #,##0.00 "萬";;

ex4. 0.00  vs.  0.??   ?：對齊小數點位置。
年格式 yy, yyyy：2023 > 23(yy) 2023(yyyy)。
月格式 m, mm, mmm, mmmm > 6(m)/ 06(mm)/ Jun(mmm)/ June(mmmm)
日格式d, dd, ddd, dddd：請測試；星期格式 ddd, dddd： Mon(ddd)/ Monday(dddd)  ；“文字”：顯示引號內文字。

在EXCEL繪製填寫底線: 選種儲存格，自訂格式@*_

隱藏零值  自訂格式>「類別」「自訂」，「類型」輸入「0;-0;;@」。

[快速鍵備忘]
Ctrl+/: 比對欄位差異(加色塊) ； Ctrl+T:表格化；Ctrl+下: 移至最下；Ctrl+Shift+下: 框選至最下；Ctrl+B粗體；+4底線
Ctrl+E: 快速填入(起始格先輸入資料)、輸入部分資料可為拆解擷取，輸入左方跨欄資料可為合併，也可加入字元可為增刪修改資料。
Ctrl+1: 儲存格格式
Ctrl+; 輸入日期 =TODAY()、Ctrl+shift+; 輸入時間 =NOW()
日期的替代方案
=TEXT(日期,"dddd") 英文星期    =TEXT(日期,"ddd") 英文星期縮寫   =TEXT(日期,"[$-404]dddd") 星期六  =TEXT(日期,"[$-404]ddd") 週六
=RIGHT(TEXT(G13,"[$-404]aaaa;@"))  =RIGHT(TEXT(G13,"[$-404]aaaa")) 可取出星期六的六(一個字)

Ctrl+G 到(定位)同F5 >特殊/空格。 情境1:隔行加總:全選>定位>選空格 Alt+=。
Alt+F1: 產生圖表； Alt+Shift+F1: 新增工作表。

[情境應用]
月份排班表
第一列為跨欄置中的年月日(2023/05/01 ex.M1)、第二列(日期列B2=M1, C2=B2+1, 向右填充, Ctrl+Shift+->, Ctrl+1, 自訂格式 d)呈現整個月單日。
第三列(星期列) B3=B2, 向右填充,  Ctrl+Shift+->, Ctrl+1, 自訂格式 aaa 呈現星期幾，只呈現一個字 =RIGHT(TEXT(日期,"[$-404]DDDD") ),1)；
以上修改M1為每月第一天，可自動將日期對應星期幾。

[多條件篩選/多重篩選]
表格先轉為一般範圍，進階篩選 多個條件 1.完全符合Direct 可使用 ="=Direct"  2. 含有Book與前置任何字元  *Book   [使用進階準則] 
AND/OR [參考文章]

小技巧
1. 關於日期  yyyy/mm/dd  e/mm/dd e:民國年數字

2.快速依據上一列(如類別名稱)填滿多個空白格：Ctel+G尋找特殊目標"空格"、=上一格欄位(=A2)、Ctrl+Enter > 全體空格套用手動之輸入格式。

3.樞紐分析的結果，在任一數值儲存格，直接右鍵>排序A>Z，即可排序。橫向(左至右)數值排序，選單一儲存格，排序/"更多排序選項"，改左到右。

4.Word 空白處 按Ctrl+F9 出現{ }符號，輸入EQ \x\to(Y)，EQ後面需有空格，Y則可填入希望文字，最後按Shift+F9，即可產生帶上線條之文字。

COUNTIF
=COUNTIF(I2:I752,">="&M1)  儲存格用&連接  ">=" &  儲存格M1
星號（*）代表任意長度任意文字，如要比對張開頭，後面兩個字，可用問號?  =COUNTIF(A2:A30, "張??")

IFS
確認兩個以上的條件，條件1為真的顯示、條件2為真的顯示、不然都沒有則顯示。可以巢狀結構的 IF 函數，或使用 IFS。
EX.  A2平均值，若大於均值+標準差則為H，小於均值-標準差則為L，都不是即介於H與L之間則為M。
=IFS(A2>$C$222,"H",A2<$CT$223,"L","TRUE","M")    \    =IF(A2>$CT$222,"H",IF(A2<$C$223,"L","M"))

EXCEL中呼叫Power Query: 資料/ 取得資料/ 從其他來源/ 空白查詢

[資料轉換與清理]
(1)取消資料行樞紐：(以洲別、國家、多年份為例)
a.直接 資料>從表格/範圍 (會提示轉為表格)。  註: EXCEL資料表先轉為表格 > 資料>取得資料>從其他來源>從表格與範圍
b.進入Power Query - 洲別  國家 年份 (三個層次，可一次轉換)。
c.選須轉換的欄位即可-年份(洲別、國家不選) > 轉換 >取消資料行樞紐
d.例如有更多年份欄位，可使用原POWER QUERY，重新整理預覽(只針對取消資料行樞紐)。
e.取消其他資料行樞紐-數十個欄位時，改選洲別、國家，兩者公式效果一樣  = Table.UnpivotOtherColumns(已變更類型, {"洲別", "國家"}, "屬性", "值") 

 
<*沒有Power Query請看下方 二維轉一維>

(2)亂碼數據的處理：
文字資料處理(亂碼CSV)：CSV亂碼先用記事本存成.txt，然後先打開EXCEL，再開.txt，會進入資料剖析即可。
可用筆記本開有亂碼的CSV檔，轉存時記得切換編碼為ANSI，存檔類型 選全部檔案，再存成.csv，再用EXCEL開啟。

[關聯式資料表-EXCEL]
EXCEL關聯式資料表(多表格進行關聯)>樞紐分析
1.三個表格格式化為表格/取名  2.表格產生後，資料/關聯圖 (始可點按) 3.新增>交易/店家 <>店家/分店編號；交易/產品編號  <>商品資料表/商品編號。 4.樞紐分析> #使用此活頁簿的資料模型 (*不直接點按確定)、再進行樞紐分析。

[多表格整合附加與合併-EXCEL+Power Query]   --不使用VLOOKUP
1.資料範圍先轉表格、 資料>取得資料>從其他來源>從表格與範圍> 進入Power Query 
2. 附加查詢(Union-變長、同欄位多表格整合)將查詢附加為新查詢(產生新表)、附加查詢(累加)。
    合併查詢(Merge-變寬、跨表格同欄位連結整合) 將查詢合併為新查詢(只用合併時)、Table展開選欄位。 
3.整合後的表格，再做樞紐分析，或使用TP分析。

[資料整理2]
EXCEL 二維的分析表轉換成一維  (舊版沒有Power Query改用使用樞紐分析表與圖精靈)Excel / 檔案 【選項】/ 自訂功能區，【由此選擇命令】下列選項，選擇【不在功能區的命令】>【樞紐分析表和樞紐分析圖精靈】  右邊【自訂功能區】下列選項展開【資料】選項，點選下方【新增群組】，更名 【樞紐分析表與圖精靈】再將所選取的【樞紐分析表和樞紐分析圖精靈】選項，加入（新增）到【樞紐分析表與圖精靈】

製作關鍵字按鈕 進行如題名之篩選

SUBTOTAL 計算(加總) 篩選過的欄位數值   =SUBTOTAL(計算方法,資料範圍)  
計算「篩選後」的數值？（加總、平均皆可），使用「SUBTOTAL」替代SUM 函數
SUBTOTAL 函數：=SUBTOTAL(計算方式, 資料範圍)
#  AVERAGE  COUNT  COUNTA (包含隱藏的列)   1, 2, 3   (忽略隱藏列)   101  102  103   #範例 平均值：=SUBTOTAL(101,[S求學])  篩選平均值

自訂清單    [檔案] > [選項] > [進階]。向下捲動至 [一般] 區段，並按一下 [編輯自訂清單]> [匯入]。  [教學點我]

WORD  上標、下標與  平方   M²  Alt+0178   M³  Alt+0179