最近接到一個生産主管的問題，他把今年（2022）的生産日報表Excel版本發給老闆後，老闆說一個一個點開檢視太麻煩了，要他把這個報表建立一個目錄連結起來，這樣就友善多了；他說他搞不定，檔案太多了；
接收到檔案後，我發現這個生産日報和大多數中小工廠的格式都差不多，在錄入Excel的時候，就沒有注意資料格式的規範性，到想要統計分析的時候就發現很困難。
大概看了一下報表，有以下幾個問題：
1. 每天一個工作表，建立了幾百個工作頁面；
2. 每個頁面的格式也不一定相同，關鍵是無規律；
3. 數字與文本錯誤特别多（不固定）
4. 無效的空行空列
5. 其它問題
以上的問題，等到錄完再整理的時候已經太晚了，是以古哥也很難解決這類沒有特定邏輯規律的資料，隻能說幫他建立一個目錄加上超連結，快速找到對應哪一天的生産日報；
01 建立目錄：
建立目錄的話，用一些第三方軟體是最快的，但是，有些中小工廠可能連外網的權限都沒有，更别說安裝第三方軟體了，加上第三方軟體一般都要額外的費用，是以古老師還是用Excel本身能夠有的函數來搞定建立目錄；
建立目錄的第一步就是想辦法把這幾百張工作表的名提取到一個建立頁面作為目錄，這一步完成後，再把超連結加上去；
Excel中提取工作表名的方法有單一法和批量法，單一法适合工作表1張的或者較少的，批量法适合比較多的工作表；
02 單一法：
單一法：用函數CELL加上文本提取函數即可,我們錄入函數：=TEXTAFTER(CELL("filename",A1),"]")，就可以把這張工作表名提取出來；
函數原理：用CELL函數中取出A1的位置，會傳回這個工作簿的存儲位置“E:\測試檔案\[生産日報.xlsx]1.2”，再用TEXTAFTER函數提取 “]”後的資訊，也就是文本名字，老版本沒有這個函數可以換成：
“=MID(J1,FIND("]",CELL("filename",A1))+1,100)”
03 批量法：
單一法的缺點也比較明顯，就是隻能一個工作表一個工作表提取，當工作太多的時候就需要用批量法，批量法相對來複雜一點，但隻要記住一個名稱就加一個函數就可以了；
第一步：建立一個頁面，命名為“目錄”,定位在單元格A1的同時按下Ctrl+F3，彈出定義名稱，輸入“tiqu”,引用位置錄入：“=GET.WORKBOOK(1)”
第二步：A1錄入标題名稱“序号”，A2錄入公式：=SEQUENCE(365)，表示自動生成365行的序号，代表365天；低版本可在B2用=ROW（A1）下拉填充替代；
第三步：用函數INDEX來引用定義的名稱，在C2錄入函數：=INDEX(tiqu,A2#)，自動提取所有工作表名(動态數組)；也可以一步到位的公式：=INDEX(tiqu,SEQUENCE(365))，如沒有365張工作頁，為了避免錯誤，再加上=IFERROR(INDEX(tiqu,SEQUENCE(365)),"")就可以把錯誤值傳回空值；
第四步：到了第三步已經隻差一點點了，最後用文本函數把工作表名提取出來就可以了。
新版本：=IFERROR(TEXTAFTER(INDEX(tiqu,SEQUENCE(365)),"]"),"")
舊版本：=IFERROR(MID(INDEX(tiqu,ROW(A2)),FIND("]",INDEX(tiqu,ROW(A2)))+1,100),"")
到這裡，已經把目錄全部自動建立好了，就差超連結了，明日繼續分享；
我是古哥：
從事制造行業18年，在企業營運、供應鍊管理、智能制造系統等方面具有豐富的實戰經驗。企業智能化，柔性化計劃營運管理專家，擅長通過企業流程優化規範，企業管理、導入計劃營運提升企業效率；對提高企業準時交貨率，降低企業庫存，輸出智能制造人才有豐富的經驗。學習PMC生産計劃，關注古哥計劃！