水月居幸福遊記

 因為要處理 excel 檔案的內容，由於數量龐大，不想 ctrl C + ctrl V N 次，所以想要動用程式來處理，這次要用的是 python，雖然說是處理 excel ，其實拿到的檔案是 ods 的格式，所以還得做一些前置工作。  工作環境：     Windows 10     python     anaconda 建立一個名為 excel 的獨立 python 工作環境   conda create -n excel   cd excel 啟用名為 excel 的工作環境   conda activate excel 這個工作環境裏要安裝這些 python 套件   python -m pip install openpyxl   python -m pip install pyexcel pyexcel-ods pyexcel-ods3 pyexcel-odsr pyexcel-xlsx pyexcel-xlsxw   關閉現行的工作環境   conda deactivate 可以寫程式取用 ods/excel 了 收工！

 如題，稍做整理   Sub resetSheetFormat () ' 重置/清除表單的「設定格式化條件」     Cells.FormatConditions.Delete End Sub Sub resetFilter () ' 重置/清除表單的篩選條件，以顯示所有的資料 ' 篩選功能還開著     ActiveSheet.ShowAllData End Sub Sub saveFile () ' 使用日期命名檔案 ' 另存新檔為 xlsx (沒有巨集的檔案)     ' 今天的日期     today = Format ( Now , "YYYYMMDD" )     ThisWorkbook.Sheets.Copy     ' 關閉詢問視窗     Application . DisplayAlerts = False     ActiveWorkbook.SaveAs Filename := "d:\somewhere\" & today & ".xlsx" , FileFormat := 51     ActiveWorkbook.Close End Sub Sub autoFitAll ()     ' 調整所有欄位 ' 自動符合內容寬度、高度     Cells. Select     Cells.EntireColumn.AutoFit     Cells.EntireRow.AutoFit End Sub Sub trunFilterOnActive () ' 確認是否有開篩選，沒開的話才打開   If Not ActiveSheet.AutoFilterMode Then     ActiveSheet. Range ( "3:3" ).AutoFilter   End If End Sub Sub turnFilterOffActive () ' 如果有開篩選的話就把它關掉 ...

 如果你把 Excel 做好的表格用 email 寄出，使用不同的瀏覽器，貼上的表格，得到的結果不太一樣。 工作環境：     Windows 10 Pro     Gmail 網頁     Edge 瀏覽器     Chrome 瀏覽器     Firefox 瀏覽器 如果你想把表格用圖的方式寄出，請用 Edge  如果你想把表格用 HTML 方式寄出(可以編輯文字)，請用 Chrome 或是 Firefox 收工！

想讀寫 Excel 文件，看了老半天，只輸出了一個空白的 Excel檔，浪費好物 laravel-excel 應該有人寫完整實做吧，果真！ 工作環境：   laravel-6   phpspreadsheet   laravel-excel 記得要先裝 phpspreadsheet ！ laravel-excel 安裝方法看這裏： https://docs.laravel-excel.com/3.1/getting-started/installation.html 使用Composer安裝： composer require maatwebsite/excel 設定 config/app.php 的 providers： + Maatwebsite\Excel\ExcelServiceProvider::class, 設定 config/app.php 的 aliases： + 'Excel' => Maatwebsite\Excel\Facades\Excel::class, $ php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" 執行成功後 vender/maatwebsite/config 下會多一個 excel.php 實做範例參看這裏： https://www.itsolutionstuff.com/post/laravel-57-import-export-excel-to-database-exampleexample.html 啟用內建資料庫 $ php artisan migrate 做一些測試用的資料 $ php artisan tinker factory(App\User::class, 20)->create(); 建立輸入 Controller(output: app/Imports/UsersImport.php) $ php artisan make:import UsersImport --model=User 建立輸出 Controller(output: app/Exports/UsersExport.ph...

有的時侯，表格很大，所謂的很大是指，水平方向很多欄位或是垂直方向很多品項，或是兩者都有，如果移動游標的話，視野會跟著移動，所以就看不到最上方或是最左邊的標題欄、或是標題列，如果能鎖定標題的話，操作資料時比較方便，不容易改錯，這時侯你需要的是「凍結窗格」，如此，指定欄位上方以及指定欄位左方所有欄列不會因為資料區視野移動而而變動。 工作環境： Excel 凍結窗格功能 指定做為資料區(可任意移動的視野範圍)最左上方的欄位 凍結窗格，通常選第一個就行(不限欄、列數)，如果是只要凍結最上方列選第二個，只要凍結最左方欄選第三個

上次遇到了同一個檔案中多個工作表合併到一個工作表裏，這次因為工作的關係又去找了多個不同的檔案，所有的工作表合併到同一個檔案中。 工作環境︰ Windows 10 M$ Office 2010 Sub CombineSheets() '活頁簿存放路徑，可自行修改存放路徑 Path = "D:\資料夾名稱\" Filename = Dir(Path & "*.xl*") '避免工作表名字相同，重新命名 i = 1 Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) ActiveSheet.Name = i i = i + 1 Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub 收工！

Excel 好像沒有檢查工作表是否存在的原生函式，所以，到網站上找了一個，在跑巨集的時侯可以用 Function sheetExists(sheetToFind As String) As Boolean     sheetExists = False     For Each Sheet In Worksheets         If sheetToFind = Sheet.Name Then             sheetExists = True             Exit Function         End If     Next Sheet End Function 收工！

假如你的工作表都長得一樣，都是從A1開始建立的，都在同一個檔案中，你可以新增一個巨集，幫你把所有的工作表合併到同一個工作表中。 Sub Combine()     Dim J As Integer       On Error Resume Next     Sheets(1).Select     Worksheets.Add     Sheets(1).Name = "Combine"     Sheets(2).Activate     Range("A1").EntireRow.Select     Selection.Copy Destination:=Sheets(1).Range("A1")       For J = 2 To Sheets.Count         Sheets(J).Activate         Range("A1").Select         Selection.CurrentRegion.Select         Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select         Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)     Next End Sub 檢視 》 巨集 》 檢視巨集 》 第一個Combine  》 執行 所有的資料全部都會合併到第一個工作表「Combine」裏面 如此就不用 [Ctrl] + [C] X [Ctrl] + [V] N 次了 收工！

只要說到資料，不免會想到用 Excel 比較方便使用，Excel 對於文字與數字的資料真的強到沒話說，But ... 這次多了一個照片，就是這個證，要有照片，才能識別當事人，既然有照片，存在 Excel 裏就是一個浮在檔案中，跨越儲存格的「物件」，所以合併列印時問題就一直生出來。 好在太陽底下沒有新鮮事，證件不可能是我第一個做，更不是我第一個用合併列印做，如此一來事情就好辦了， 合併列印的方法，請參閱前文 ，解法很簡單，依照前文，在第2步，選取收件者的時侯（就是選合併列印用的資料集的時侯），選擇資料所在的 Word 檔、 Word 檔、 Word 檔，因為很重要所以要說3次，照片在於 Word 檔裏面會存在格子裏，所以你指定照片的欄位，合併列印時就會把照片放在你的證件上了。 另外，如果用圖的話就會有排版的問題，所以原本的儲存格要做分割，比較好處理，不然光是排版要把圖放在指定的地方就很擾人了，因為設計版面的時侯排的是字，並沒有圖片的處理和自動換行的功能可用，所以也可以考慮使用直式的證別輸出（常見的識別證格式是橫式），以避開這個問題。 收工！ 開心

在用 Excel 的時侯，按下[Enter]鍵之後，就會換下一格，不會在同一個欄位把內容換下一行。 所以，如果要輸入多行的資料，在換行（斷行）的時侯，要用 [alt] + [Enter] 。 使用 [alt] + [Enter] 邊輸入邊換行 或者，在 Word 整個打好，再貼到輸入的地方。 打好的內容直接貼上

在使用 Office 整理資料的時侯，可能要進一步將 Excel 中的資料放到 Word 中處理做成摘要，過程比較常遇到需要處理特殊字元是分隔符號(tab, ^t) 和換行(^p)要取代成適當的字串。 例如：用 Excel 整理好的資料，表單內含有許多欄位，想要利用原始資料的部份欄位做成摘要，先把所有會用到的字串，和所需要的欄位整理出來，依序放到不同的欄位，沒有串接到一起，透過 Word 的取代功能，把特定的字元(tab)，取代掉，成為整理完成的摘要。 Excel 資料，大致整理成摘要的樣子(利用 =) 方法： 把 Excel 的資料複製貼到筆記本再貼到 Word 筆記本會自動將 Excel 資料用 tab 分隔，利用這個特性將筆記本格式化過的資料貼到 Word 取代 tab 成為(留白，什麼都不要輸入) 有時侯取代的功能會被縮整到整合編輯圖示中 取代 tab 取代換行成為(、) 取代換行 如果結果只有這一行 20151001 花費：早餐 (51) 、午餐 (120) 、晚餐 (120) ，總計 (291) 、 再取代 、^p 成為(留白，什麼都不要輸入) 如果有很多行 20151001 花費：早餐 (51) 、午餐 (120) 、晚餐 (120) ，總計 (291) 、 20151001 花費：早餐 (51) 、午餐 (120) 、晚餐 (120) ，總計 (291) 、 20151001 花費：早餐 (51) 、午餐 (120) 、晚餐 (120) ，總計 (291) 、 再取代 、^p 成為 ^p 可以使用的特殊字元代碼，請參見 Microsoft 官網： https://support.office.com/zh-hk/article/尋找及取代文字或其他項目-50b45f26-c4b8-4003-b9e4-315a3547f69c#bm6 搜尋時使用萬用字元尋找特定字母 收工 ！

Excel 有個很好用的功能，你想要的資料建置完成之後，可以依照需求來統計，在統計的時侯，可能會重復很多次的篩選動作，篩選是 Excel 很好用的功能，好像是 Office 2007 之後的版本，還可以啟用多重篩選，打勾和取消就能決定輸出的內容，非常方便，But... 篩選的勾選選單字很小 明明有清單，為什麼不能一次把資料選出來 為什麼要一筆一筆的在數字篩選重復輸入，再將篩選結果重貼到另一個工作表呢？ 為什麼數字篩選的自訂篩選只有2個條件可以下呢？ 有沒有發現，篩選的漏斗，右下在有個進階，這個好物可以用來解決這個問題。 給我清單，還你資料。 來試試吧。 這次的好物，進階篩選 重點： 清單的標題和資料欄位的 標題要一模一樣、標題要一模一樣、標題要一模一樣 ，所以，[Ctrl + C] 、[Ctrl + V]，把資料標題找個地方複製成為你的清單的標題。 把清單放在剛才的標題下方。 開啟進階篩選功能。 指定資料的所在範圍(資料範圍)和清單(準則範圍)的所在範圍。 指定資料和準則範圍只要按右方的按鈕，用滑鼠框好就行 bingo，只要是你的清單內容和資料內容相同的那一列都會被選出來。 要瀏覽全部的資料，[清除]篩選的內容就行。 和清單相符的資料才會被找出來 收工！

要加總某一個欄位的值，對 Excel 來說是再簡單不過的事， 如果加總某一個欄位的值時，必其另一個欄位符合特定的條件，也算容易。 要在加總某一個欄位的時侯，同時考量其他多個欄位的條件成立，才做加總的話， 以篩選A、B、C 3個欄位分別符合一定條件，此時加總第4個（D）欄位， 例如 A>5、B>3、C<100，加總D欄位值，就需要一些技巧了。 此時有幾個選擇︰ 一、界面操作法：用極少的函式(=) 二、sumproduct︰很花運算資源，也很花腦細胞 三、陣列法︰Excel 2003或是2007之後的新成員，很強，但是和我不熟 四、sumifs、或countifs函式︰直覺又快 法一︰ 用篩選下條件分別篩選3欄 (A>5、B>3、C<100 ) 分別對應到E、F、G欄，在符合條件時標註1：     也就是說，篩A，找出符合條件的，在E標註1，移除所有條件，     篩B，找出符合條件的，在F標註1，移除所有條件，     篩C，找出符合條件的，在G標註1，移除所有條件， 移除所有條件 同時篩選，E, F, G, =1 ，在H 下函式 =D 移除所有條件 加總H 法二︰ =sumproduct((A2:A10>5)*(B2:B10>3)*(C2:C10<100) 法三︰那個大括號是在設完函式後 [Ctrl + shift + Enter] 才會出現，函式也才會生效 { =if(A2:A10>5,if(B2:B10>3,if(C2:C10<100 } 法四︰ =sumifs(D2:D10,A2:A10,">5",B2:B10,">3",C2:C10,"<100 ) Countifs 和 sumifs 觀念是一樣的，有興趣的可以自已試試。

使用樞紐分析表產生的結果和使用函式產生的結果是一樣，但是它提供方便直覺的操作方式，熟悉以後可以大輻減少腦細胞殺死量與工作量。 使用樞紐分析表，通常是想找出資料裏面2個以上的欄位之間的關係，因為這樣子的關係，通常是使用2維表格的欄和列來作呈現方式，所以樞紐分析表的操作介面就是經由這樣的邏輯來配置，之後將要互動的欄位分別配置在欄標籤、和列標籤的區塊，再將要運算的欄位放到 Σ 值的區塊，就能完成工作。 使用樞紐分析表，必須把要呈現的原始內容，在要輸出樞紐分析表之前，將資料處理好，讓樞紐分析表只負責選擇要輸出的欄位和進階資料篩選的工作。 以下使用記帳的統計來舉例，大家可以自行用自已的資料試試，在這個例子中，目標是找出在不同的店家，個別消費的項目，到底花了多少錢。 選好資料區塊 > 插入 > 樞紐分析表 > 已存在的工作表 > 選樞紐分析表的配置位置 插入樞紐分析表 畫面的右方會出現樞紐分析表的操作介面，剛才指定的位置(或是新工作表)會依據操作介面指定的方式，顯示樞紐分析表的統計結果 樞紐分析表的操作介面 操作介面的欄位內容是依據插入樞紐分析時的選定的資料內容自動帶入 介面中已經帶入欄位名稱 本例想了解店家、商品分別「拖、拉、放」到「列標籤、欄標籤」的區塊，再將要運算的花費總數放到「Σ值」的區塊 針對想要運算的相關欄位進行配置， 拖、拉、放 資料 欄位 到適當的地方 將將將將！欄位放好，結果立現，而且欄位內容自動歸類統計 結果 通常2個以上欄位要做統計要透過 sumproduct 函式，偏偏使用 sumproduct 在資料多的時侯效能非常的差，而且，重新開啟檔案時間花費非常久，這種時侯使用樞紐是很好的選擇，用起來直覺，效能又好，真要挑個樞紐的弱點的話，大概是統計結果無法隨著資料變動立即自動重算加以更新，須要使用者重整樞紐分析表才行，好在重整的速度也很快，不失為權衡之下的好方式。

有時侯，為了同呈現2組分布差異很大數值，例如，其中一組的數據是6位數，另一組數據是2位數，作圖後，預設狀況很難同時顯示2組數據的分布狀況，所以就需要雙座標軸的圖形來輔助。 插入 > 直條圖 > 平面直條圖 / 群組直條圖 > 得到預設的作圖結果，但是需要調整(如下圖) > 資料數列格式 >  作出直條圖 將數據大的組調成副座標軸 >  設定副座標軸 變更圖表類形以便區分 > 變更圖表類形以便區分 選擇想要使用的圖表類形 > 確定 > 選擇想要使用的圖表類形 收工。 左邊的座標軸顯示的是圖例在上的資料；右邊的座標軸顯示的是圖例在下的資料

有時侯統計不用類別的資料時，需要進一步呈現其中一個類別的比率，例如，統計某個活動的男、女與會人數比率之後，想進一步知道女姓成員中，不同的職務類別比率為何？此時就需要動用到子母圓餅圖。 準備好資料就插入子母圓餅圖 》 插入子母圓餅圖 一開始產生的圖形可能不是預期的 》 一開始產生的圖形 加以調整成想要的樣子[資料數列格式] 》 調整參數 調整欄位數和資料表的欄位數相對應 》 收工！

如果定期做同樣的報表，每次處理的資料來源都是固定的欄位，產生的報表格式也固定，除了使用函式之外，還可以搭配巨集來加速作業。 巨集的概念就像錄放影機，先錄後放，一開始先把操作過程作錄下來，之後每次重復操作時重播(執行)一次巨集，就會從頭到底依據之前錄下來的動作操作一次，不同的地方只有原料更換過了，巨集能發揮作用，是因為在錄製巨集的時侯，把操作的過程代換使用 vb 的語法成記錄下來。 舉例來說，有個函式表格，因為原始檔案很大，調來調去、貼來貼去、殺來殺去，處理過程很長，造成函式不見，臨床症狀是函式出現「#REF!」字樣， 出現錯誤訊息的欄位 這種現象是因為函式原本參照的資料有被殺掉過，遇到這種情形可以選擇抓出臭蟲，實務上通常沒有這等美國時間做，而且為了避免產生新的錯誤，通常選擇直接修復這個錯誤，這時侯可以用考慮利用巨集。 此例的目標就是每次出現這種錯誤訊息時加以修復，具體來說，就是把重新指定參照範圍的動作錄成巨集： 新增一個巨集 為巨集鋁名時記得用好記的名字，所謂的好記的名字，通常是可以表示巨集的功能，這是為了避免巨集一多，不知道要用那個，巨集名稱不能是數字開頭，所以重要的巨集可以用a開頭，執行時出現在列表的開端便於選用 命名巨集，加上描述以便日後判讀，有些人會用比較正規做法，寫上目的、輸入項目、變數、輸出項目等 平常手動操作做什麼事，在錄製巨集的時侯就做什麼事，此例把函式修正回來 用巨集把操作動作記錄下來 錄好了，結果也是原先預期的，告訴電腦一聲，按下[停止鍵]，巨集就完成了(可是看不到) 停止錄製巨集 如果以後出現同樣的問題，把錄好的巨集叫出來[執行] 執行已經錄好的巨集 就能得到預期的結果 \(+o+)/ 執行巨集的結果 注意：Office 2007 版之後，有巨集的檔案要存成 xlsm 的格式。 建議分段錄製，一來容易找出錯誤、修正錯誤(除錯)、重新錄製，二來在不熟悉 vb 語法的時侯方便觀察語法和其對應的行為，從中學習，增進熟練度。 增加巨集的彈性，資料通常是會小輻變動的，如果每次都是手動操作，在過程中可以很快即時反應，但是利用巨集處理資料時，必須考慮彈性，例如到巨集 vb 語法中把範圍加大，或是使用可以達成相同目的的不同函式；要增加巨集的彈...

通常，最不喜歡看到日期的換算數值，而它常常出現，尤其是在你的資料貼來貼去的時侯，而且是你要送件的時侯，或是送件以後才發現跑掉了，心中無明火起，臉上卻是堆滿無奈，但是有時侯，你須要這個數值，但是你就是不知道怎麼把它叫出來 @@" 什麼時侯會要這個數值呢？在用 Excel 做甘特圖時會用到，為了聚焦，得把時間調在指定的範圍內，這個時侯可別想在圖形的參數裏，填日期在最大值和最小值，只能填入時間轉換後的數值，所以，得把這個數值找出來，找到了幾個方法…… 調整儲存格格式： 有時侯調不出來 使用函式 Datevalue(日期)： 有時侯還是不出來 貼： 既然是貼來貼去的時侯常會出現，應該「選擇性貼上」才是王道，簡單到令人感動、[選取日期格式資料] > [滑鼠右鍵] > [選擇性貼上] > [值] 將！將！你要的數值出來了，其實它很少用到，因為你看不懂那是啥東西，比起表達日期的格式，這是沒有意義的數字，但是在作甘特圖時必須用到它！

Excel 的定位系統是用水平和垂直2個維度的方式來進行，因為可以在水平或是垂直的欄位套用相同的運算邏輯，所以可以加速工作的完成，之所以能夠快速完成這樣子的工作，是因為，在從事套用動作的時侯，Excel 會自動遞延那個維度的設定，依據所在位置修正為適當的函式。 舉例來說， 垂直方向的遞延 ，當你設完函式，在垂直方向套用的時侯，假設你的 C1 的函式是 =A1+B1，向下一拉，套用之後， C2 會自動產生 =A2+B2 的函式、C3 會自動產生 =A3+B3 的函式…依此類推遞延處理，並顯示運算的結果，函式的內容，英文的部份不變，數字變大。 水平方向的遞延 ，當你設完函式，在水平方向套用的時侯，假設你的工作表2 A1 的函式是 =sheet1!A1，向右一拉，套用之後， B1 會自動產生 =sheet1!B1 的函式， C1 會自動產生 =sheet1!C1 的函式…依此類推遞延處理，並顯示運算的結果，函式的內容，數字的部份不變，英文依順序變化。 雖然表格通常是2維的，邏輯上透過2個的維度呈現完整的訊息，但是規劃和運算的時侯，是一個維度、一個維度逐次去處理的。 有時侯，就是 不要 Excel 幫你做遞延的動作 ，最基本的例子，就是計算某個項目佔全部的比重，好比是志明、春嬌、阿花的業績各佔多少比重，以算數來說算是基本功，先把3個人的業績加起來得到總數(志明業績 + 春嬌業績 + 阿花業績)，再把每個人的業績分別除以總數，就能得到個別貢獻的比重，利用 Excel 的話，在 C1 設定函式 [=B2/B5]，再套用函式一直到 C5，函式的使用，可以參見 Excel 函式 四則運算與文字處理  套用和Pseudo Code的部份。 如果直接垂直套用的話，得到的結果出乎預期，有時侯會出現錯誤訊息是很正常的事情，這只是軟體提醒你要修正，不須要太過緊張，如果每次運用函式都能一次到位，表示你已經熟練。(這裏可能就沒生意了 orz) 如果資料量極小，當然可以一個一個把函式中的除數改成 B5，但是如果有300筆資料、3000筆、3000000筆，這個時侯就沒辦法一個一個改了，不過可以發現，B5這個部份一直沒有變動，因為我們是垂直套用，所以 B 這個英文字一定不會變，在此例可以先略過，只有5在垂直套用的時侯是會變的，所以應該要讓它 固定 ，可以想...

甘特圖是一種管理的工具，它的主要資料欄位大致上是——要完成的事務，起始時間(，持續時間)，完成時間，把這些元素視覺化，用圖形的方式來表示；為了管理專案，以往會找類似 project 這種付費軟體來做，如果只是要做甘特圖付費 cp 值不高，這次看到同事填表後用 Excel 圖表也做，就自己動手試試。 如果你的資料進度只需 精準到月份 的話，不必使用到作圖的元件就能完成，有些人用畫線調粗細的方式處理，個人偏好把工作項目那一欄合併起來後，輸入工作項目名稱，在三明治中間倒油漆，   >   >   > 把框線美化一下。 如果要 精準到日 的話，可以把欄位設好，用橫條圖，工作持續時間之外地方的色彩[無填滿]。 如果不知道怎麼調整座標軸的始末可以用這招，再去調整座標軸的起始日與終止日，把值貼過去。 雖然有些軟體是專門設計來作專案管理的，甘特圖也只是其中的一環，如果只是為了呈現工作項目和時程，使用這種專業軟體可能反而沒那麼直覺，而使得上手、入手門坎變高，得不償失。