【雲端上的管理術】-精實生產(Lean Production)-山積表之設計範例教學 @ 雲端上的管理術～Management and Life～

5-01.山積表之設計範例教學-精實生產(Lean Production).png

一般進行工程改善時，通常將現況生產線編排方式輸入「生產線平衡表」中，以了解現況編排與需求的「節拍時間（TT, Takt Time）」差異，並開始針對滿足「節拍時間（TT, Takt Time）」的作業方式進行調整，其主要方式以運用ECRS-刪除(Eliminate)、合併(Combine)、重排(Rearrange)、簡化(Simplify)方式，降低作業者之不必要作業，再以簡易「自働化」改善方案來達到作業工時的縮短，最後再依照作業工時進行重新配置工作站，來獲得製程投入人員數的改善效果。因此常常需要進行跨單位的工程改善案研討會議，山積表將會是一個很好的研討工具，可清楚說明需要調整的作業及改善後滿足「節拍時間（TT, Takt Time）」之狀況。

Step 1:首先進行山積表佈局設計，注意各顏色區域相對的儲存格位置，此佈局設計需與<生產線平衡計算表>搭配使用才能產生圖形。

Step 2:建立山積表預留區(圖形顯示區域)。

Step 3:綠色區域之佈局設計，主要對應生產線平衡表之「合併工作內容」區域(儲存格J8~O27位置)，其主要目的為將，生產線平衡表中各「工作站次」之「合併工作內容」資訊，由數字代號轉變為「工作內容」，請於紅色框框中儲存格，輸入下方【EXCEL儲存格公式說明】中對應欄位的公式後，框選儲存格AG8~AL8後下拉複製公式。

【EXCEL儲存格公式說明】

儲存格AG8公式

=IF(ISERROR(VLOOKUP(J8,$B$8:$G$24,5,0)),"",VLOOKUP(J8,$B$8:$G$24,2,0))

儲存格AH8公式

=IF(ISERROR(VLOOKUP(K8,$B$8:$G$24,5,0)),"",VLOOKUP(K8,$B$8:$G$24,2,0))

儲存格AI8公式

=IF(ISERROR(VLOOKUP(L8,$B$8:$G$24,5,0)),"",VLOOKUP(L8,$B$8:$G$24,2,0))

儲存格AJ8公式

=IF(ISERROR(VLOOKUP(M8,$B$8:$G$24,5,0)),"",VLOOKUP(M8,$B$8:$G$24,2,0))

儲存格AK8公式

=IF(ISERROR(VLOOKUP(N8,$B$8:$G$24,5,0)),"",VLOOKUP(N8,$B$8:$G$24,2,0))

儲存格AL8公式

=IF(ISERROR(VLOOKUP(O8,$B$8:$G$24,5,0)),"",VLOOKUP(O8,$B$8:$G$24,2,0))

Step 4:白色區域之佈局設計，主要對應生產線平衡表之「工作站次」區域(儲存格I8~I27位置)，其主要目的為顯示生產線平衡表中有進行工作站安排的「工作站次」，請於紅色框框中AM8儲存格，輸入下方【EXCEL儲存格公式說明】中該欄位的公式後，點選儲存格AM8後下拉複製公式。

【EXCEL儲存格公式說明】

儲存格AG8公式

=IF(AG8="","",I8)

Step 5:淡藍色區域之佈局設計，主要對應生產線平衡表之「工作內容Work contains 」區域(儲存格C8~C27位置)，其主要目的為顯示生產線平衡表中有填入的「工作內容Work contains 」，請於紅色框框中AN7儲存格，輸入下方【EXCEL儲存格公式說明】中該欄位的公式後，點選儲存格AN7後右拉複製公式。

【EXCEL儲存格公式說明】

儲存格AN7公式

=IF(VLOOKUP(AN6,$B$8:$D$27,2,0)="","",VLOOKUP(AN6,$B$8:$D$27,2,0))

Step 6:淡橘色區域之佈局設計，主要為判斷淡藍色區域顯示之「工作內容Work contains 」是否出現於淡綠色各行儲存格中，如果有就顯示該「工作內容Work contains 」之「作業時間Total hours 」，由於該「作業時間Total hours 」並非山積表所需的工作站平均工時，因此需將「作業時間Total hours 」除以合併工作後的「投入人數Workers」得到「平均工時Average hours」。

【EXCEL儲存格公式說明】

儲存格AN8公式

=IF($AG$8="","",IF(AN7=$AG$8,VLOOKUP($J$8,$B$8:$G$27,6,0)/$P$8,IF($AH$8="","",IF(AN7=$AH$8,VLOOKUP($K$8,$B$8:$G$27,6,0)/$P$8,IF($AI$8="","",IF(AN7=$AI$8,VLOOKUP($L$8,$B$8:$G$27,6,0)/$P$8,IF($AJ$8="","",IF(AN7=$AJ$8,VLOOKUP($M$8,$B$8:$G$27,6,0)/$P$8,IF($AK$8="","",IF(AN7=$AK$8,VLOOKUP($N$8,$B$8:$G$27,6,0)/$P$8,IF($AL$8="","",IF(AN7=$AL$8,VLOOKUP($O$8,$B$8:$G$27,6,0)/$P$8))))))))))))

儲存格AN9公式

=IF($AG$9="","",IF(AN7=$AG$9,VLOOKUP($J$9,$B$8:$G$27,6,0)/$P$9,IF($AH$9="","",IF(AN7=$AH$9,VLOOKUP($K$9,$B$8:$G$27,6,0)/$P$9,IF($AI$9="","",IF(AN7=$AI$9,VLOOKUP($L$9,$B$8:$G$27,6,0)/$P$9,IF($AJ$9="","",IF(AN7=$AJ$9,VLOOKUP($M$9,$B$8:$G$27,6,0)/$P$9,IF($AK$9="","",IF(AN7=$AK$9,VLOOKUP($N$9,$B$8:$G$27,6,0)/$P$9,IF(AL9="","",IF(AN7=$AL$9,VLOOKUP($O$9,$B$8:$G$27,6,0)/$P$9))))))))))))

儲存格AN10公式

=IF($AG$10="","",IF(AN7=$AG$10,VLOOKUP($J$10,$B$8:$G$27,6,0)/$P$10,IF($AH$10="","",IF(AN7=$AH$10,VLOOKUP($K$10,$B$8:$G$27,6,0)/$P$10,IF($AI$10="","",IF(AN7=$AI$10,VLOOKUP($L$10,$B$8:$G$27,6,0)/$P$10,IF($AJ$10="","",IF(AN7=$AJ$10,VLOOKUP($M$10,$B$8:$G$27,6,0)/$P$10,IF($AK$10="","",IF(AN7=$AK$10,VLOOKUP($N$10,$B$8:$G$27,6,0)/$P$10,IF($AL$10="","",IF(AN7=$AL$10,VLOOKUP($O$10,$B$8:$G$27,6,0)/$P$10))))))))))))

儲存格AN11公式

=IF($AG$11="","",IF(AN7=$AG$11,VLOOKUP($J$11,$B$8:$G$27,6,0)/$P$11,IF($AH$11="","",IF(AN7=AH11,VLOOKUP($K$11,$B$8:$G$27,6,0)/$P$11,IF($AI$11="","",IF(AN7=$AI$11,VLOOKUP($L$11,$B$8:$G$27,6,0)/$P$11,IF($AJ$11="","",IF(AN7=$AJ$11,VLOOKUP($M$11,$B$8:$G$27,6,0)/$P$11,IF($AK$11="","",IF(AN7=$AK$11,VLOOKUP($N$11,$B$8:$G$27,6,0)/$P$11,IF($AL$11="","",IF(AN7=$AL$11,VLOOKUP($O$11,$B$8:$G$27,6,0)/$P$11))))))))))))

儲存格AN12公式

=IF($AG$12="","",IF(AN7=$AG$12,VLOOKUP($J$12,$B$8:$G$27,6,0)/$P$12,IF($AH$12="","",IF(AN7=$AH$12,VLOOKUP($K$12,$B$8:$G$27,6,0)/$P$12,IF($AI$12="","",IF(AN7=AI12,VLOOKUP($L$12,$B$8:$G$27,6,0)/$P$12,IF($AJ$12="","",IF(AN7=$AJ$12,VLOOKUP($M$12,$B$8:$G$27,6,0)/$P$12,IF($AK$12="","",IF(AN7=$AK$12,VLOOKUP($N$12,$B$8:$G$27,6,0)/$P$12,IF($AL$12="","",IF(AN7=$AL$12,VLOOKUP($O$12,$B$8:$G$27,6,0)/P12))))))))))))

儲存格AN13公式

=IF($AG$13="","",IF(AN7=$AG$13,VLOOKUP($J$13,$B$8:$G$27,6,0)/$P$13,IF($AH$13="","",IF(AN7=$AH$13,VLOOKUP($K$13,$B$8:$G$27,6,0)/$P$13,IF($AI$13="","",IF(AN7=$AI$13,VLOOKUP($L$13,$B$8:$G$27,6,0)/$P$13,IF($AJ$13="","",IF(AN7=$AJ$13,VLOOKUP($M$13,$B$8:$G$27,6,0)/$P$13,IF($AK$13="","",IF(AN7=$AK$13,VLOOKUP($N$13,$B$8:$G$27,6,0)/$P$13,IF($AL$13="","",IF(AN7=$AL$13,VLOOKUP($O$13,$B$8:$G$27,6,0)/P13))))))))))))

儲存格AN14公式

=IF($AG$14="","",IF(AN7=$AG$14,VLOOKUP($J$14,$B$8:$G$27,6,0)/$P$14,IF($AH$14="","",IF(AN7=$AH$14,VLOOKUP($K$14,$B$8:$G$27,6,0)/$P$14,IF($AI$14="","",IF(AN7=$AI$14,VLOOKUP($L$14,$B$8:$G$27,6,0)/$P$14,IF($AJ$14="","",IF(AN7=$AJ$14,VLOOKUP($M$14,$B$8:$G$27,6,0)/$P$14,IF($AK$14="","",IF(AN7=$AK$14,VLOOKUP($N$14,$B$8:$G$27,6,0)/$P$14,IF($AL$14="","",IF(AN7=$AL$14,VLOOKUP($O$14,$B$8:$G$27,6,0)/$P$14))))))))))))

儲存格AN15公式

=IF($AG$15="","",IF(AN7=$AG$15,VLOOKUP($J$15,$B$8:$G$27,6,0)/$P$15,IF($AH$15="","",IF(AN7=$AH$15,VLOOKUP($K$15,$B$8:$G$27,6,0)/$P$15,IF($AI$15="","",IF(AN7=$AI$15,VLOOKUP($L$15,$B$8:$G$27,6,0)/$P$15,IF($AJ$15="","",IF(AN7=$AJ$15,VLOOKUP($M$15,$B$8:$G$27,6,0)/$P$15,IF($AK$15="","",IF(AN7=$AK$15,VLOOKUP($N$15,$B$8:$G$27,6,0)/$P$15,IF($AL$15="","",IF(AN7=$AL$15,VLOOKUP($O$15,$B$8:$G$27,6,0)/P15))))))))))))

儲存格AN16公式

=IF($AG$16="","",IF(AN7=$AG$16,VLOOKUP($J$16,$B$8:$G$27,6,0)/$P$16,IF($AH$16="","",IF(AN7=$AH$16,VLOOKUP($K$16,$B$8:$G$27,6,0)/$P$16,IF($AI$16="","",IF(AN7=$AI$16,VLOOKUP($L$16,$B$8:$G$27,6,0)/$P$16,IF($AJ$16="","",IF(AN7=$AJ$16,VLOOKUP($M$16,$B$8:$G$27,6,0)/$P$16,IF($AK$16="","",IF(AN7=$AK$16,VLOOKUP($N$16,$B$8:$G$27,6,0)/$P$16,IF($AL$16="","",IF(AN7=$AL$16,VLOOKUP($O$16,$B$8:$G$27,6,0)/P16))))))))))))

儲存格AN17公式

=IF($AG$17="","",IF(AN7=$AG$17,VLOOKUP($J$17,$B$8:$G$27,6,0)/$P$17,IF($AH$17="","",IF(AN7=$AH$17,VLOOKUP($K$17,$B$8:$G$27,6,0)/$P$17,IF($AI$17="","",IF(AN7=$AI$17,VLOOKUP($L$17,$B$8:$G$27,6,0)/$P$17,IF($AJ$17="","",IF(AN7=$AJ$17,VLOOKUP($M$17,$B$8:$G$27,6,0)/$P$17,IF($AK$17="","",IF(AN7=$AK$17,VLOOKUP($N$17,$B$8:$G$27,6,0)/$P$17,IF($AL$17="","",IF(AN7=$AL$17,VLOOKUP($O$17,$B$8:$G$27,6,0)/P17))))))))))))

儲存格AN18公式

=IF($AG$18="","",IF(AN7=$AG$18,VLOOKUP($J$18,$B$8:$G$27,6,0)/$P$18,IF($AH$18="","",IF(AN7=$AH$18,VLOOKUP($K$18,$B$8:$G$27,6,0)/$P$18,IF($AI$18="","",IF(AN7=$AI$18,VLOOKUP($L$18,$B$8:$G$27,6,0)/$P$18,IF($AJ$18="","",IF(AN7=$AJ$18,VLOOKUP($M$18,$B$8:$G$27,6,0)/$P$18,IF($AK$18="","",IF(AN7=$AK$18,VLOOKUP($N$18,$B$8:$G$27,6,0)/$P$18,IF($AL$18="","",IF(AN7=$AL$18,VLOOKUP($O$18,$B$8:$G$27,6,0)/P18))))))))))))

儲存格AN19公式

=IF($AG$19="","",IF(AN7=$AG$19,VLOOKUP($J$19,$B$8:$G$27,6,0)/$P$19,IF($AH$19="","",IF(AN7=$AH$19,VLOOKUP($K$19,$B$8:$G$27,6,0)/$P$19,IF($AI$19="","",IF(AN7=$AI$19,VLOOKUP($L$19,$B$8:$G$27,6,0)/$P$19,IF($AJ$19="","",IF(AN7=$AJ$19,VLOOKUP($M$19,$B$8:$G$27,6,0)/$P$19,IF($AK$19="","",IF(AN7=$AK$19,VLOOKUP($N$19,$B$8:$G$27,6,0)/$P$19,IF($AL$19="","",IF(AN7=$AL$19,VLOOKUP($O$19,$B$8:$G$27,6,0)/$P$19))))))))))))

儲存格AN20公式

=IF($AG$20="","",IF(AN7=$AG$20,VLOOKUP($J$20,$B$8:$G$27,6,0)/$P$20,IF($AH$20="","",IF(AN7=$AH$20,VLOOKUP($K$20,$B$8:$G$27,6,0)/$P$20,IF($AI$20="","",IF(AN7=$AI$20,VLOOKUP($L$20,$B$8:$G$27,6,0)/$P$20,IF($AJ$20="","",IF(AN7=$AJ$20,VLOOKUP($M$20,$B$8:$G$27,6,0)/$P$20,IF($AK$20="","",IF(AN7=$AK$20,VLOOKUP($N$20,$B$8:$G$27,6,0)/$P$20,IF($AL$20="","",IF(AN7=$AL$20,VLOOKUP($O$20,$B$8:$G$27,6,0)/$P$20))))))))))))

儲存格AN21公式

=IF($AG$21="","",IF(AN7=$AG$21,VLOOKUP($J$21,$B$8:$G$27,6,0)/$P$21,IF($AH$21="","",IF(AN7=$AH$21,VLOOKUP($K$21,$B$8:$G$27,6,0)/$P$21,IF($AI$21="","",IF(AN7=$AI$21,VLOOKUP($L$21,$B$8:$G$27,6,0)/$P$21,IF($AJ$21="","",IF(AN7=$AJ$21,VLOOKUP($M$21,$B$8:$G$27,6,0)/$P$21,IF($AK$21="","",IF(AN7=$AK$21,VLOOKUP($N$21,$B$8:$G$27,6,0)/$P$21,IF($AL$21="","",IF(AN7=$AL$21,VLOOKUP($O$21,$B$8:$G$27,6,0)/$P$21))))))))))))

儲存格AN22公式

=IF($AG$22="","",IF(AN7=$AG$22,VLOOKUP($J$22,$B$8:$G$27,6,0)/$P$22,IF($AH$22="","",IF(AN7=$AH$22,VLOOKUP(K22,$B$8:$G$27,6,0)/$P$22,IF($AI$22="","",IF(AN7=$AI$22,VLOOKUP($L$22,$B$8:$G$27,6,0)/$P$22,IF($AJ$22="","",IF(AN7=$AJ$22,VLOOKUP($M$22,$B$8:$G$27,6,0)/$P$22,IF($AK$22="","",IF(AN7=$AK$22,VLOOKUP($N$22,$B$8:$G$27,6,0)/$P$22,IF($AL$22="","",IF(AN7=$AL$22,VLOOKUP($O$22,$B$8:$G$27,6,0)/$P$22))))))))))))

儲存格AN23公式

=IF($AG$23="","",IF(AN7=$AG$23,VLOOKUP($J$23,$B$8:$G$27,6,0)/$P$23,IF($AH$23="","",IF(AN7=$AH$23,VLOOKUP($K$23,$B$8:$G$27,6,0)/$P$23,IF($AI$23="","",IF(AN7=$AI$23,VLOOKUP($L$23,$B$8:$G$27,6,0)/$P$23,IF($AJ$23="","",IF(AN7=$AJ$23,VLOOKUP($M$23,$B$8:$G$27,6,0)/$P$23,IF($AK$23="","",IF(AN7=$AK$23,VLOOKUP($N$23,$B$8:$G$27,6,0)/$P$23,IF($AL$23="","",IF(AN7=$AL$23,VLOOKUP($O$23,$B$8:$G$27,6,0)/$P$23))))))))))))

儲存格AN24公式

=IF($AG$24="","",IF(AN7=$AG$24,VLOOKUP($J$24,$B$8:$G$27,6,0)/$P$24,IF($AH$24="","",IF(AN7=$AH$24,VLOOKUP($K$24,$B$8:$G$27,6,0)/$P$24,IF($AI$24="","",IF(AN7=$AI$24,VLOOKUP($L$24,$B$8:$G$27,6,0)/$P$24,IF($AJ$24="","",IF(AN7=$AJ$24,VLOOKUP($M$24,$B$8:$G$27,6,0)/$P$24,IF($AK$24="","",IF(AN7=$AK$24,VLOOKUP($N$24,$B$8:$G$27,6,0)/$P$24,IF($AL$24="","",IF(AN7=$AL$24,VLOOKUP($O$24,$B$8:$G$27,6,0)/$P$24))))))))))))

儲存格AN25公式

=IF($AG$25="","",IF(AN7=$AG$25,VLOOKUP($J$25,$B$8:$G$27,6,0)/$P$25,IF($AH$25="","",IF(AN7=$AH$25,VLOOKUP($K$25,$B$8:$G$27,6,0)/$P$25,IF($AI$25="","",IF(AN7=$AI$25,VLOOKUP($L$25,$B$8:$G$27,6,0)/$P$25,IF($AJ$25="","",IF(AN7=$AJ$25,VLOOKUP($M$25,$B$8:$G$27,6,0)/$P$25,IF($AK$25="","",IF(AN7=$AK$25,VLOOKUP($N$25,$B$8:$G$27,6,0)/$P$25,IF($AL$25="","",IF(AN7=$AL$25,VLOOKUP($O$25,$B$8:$G$27,6,0)/$P$25))))))))))))

儲存格AN26公式

=IF($AG$26="","",IF(AN7=$AG$26,VLOOKUP($J$26,$B$8:$G$27,6,0)/$P$26,IF($AH$26="","",IF(AN7=$AH$26,VLOOKUP($K$26,$B$8:$G$27,6,0)/$P$26,IF($AI$26="","",IF(AN7=$AI$26,VLOOKUP($L$26,$B$8:$G$27,6,0)/$P$26,IF($AJ$26="","",IF(AN7=$AJ$26,VLOOKUP($M$26,$B$8:$G$27,6,0)/$P$26,IF($AK$26="","",IF(AN7=$AK$26,VLOOKUP($N$26,$B$8:$G$27,6,0)/$P$26,IF($AL$26="","",IF(AN7=$AL$26,VLOOKUP($O$26,$B$8:$G$27,6,0)/$P$26))))))))))))

儲存格AN27公式

=IF($AG$27="","",IF(AN7=$AG$27,VLOOKUP($J$27,$B$8:$G$27,6,0)/$P$27,IF($AH$27="","",IF(AN7=$AH$27,VLOOKUP($K$27,$B$8:$G$27,6,0)/$P$27,IF($AI$27="","",IF(AN7=$AI$27,VLOOKUP($L$27,$B$8:$G$27,6,0)/$P$27,IF($AJ$27="","",IF(AN7=$AJ$27,VLOOKUP($M$27,$B$8:$G$27,6,0)/$P$27,IF($AK$27="","",IF(AN7=$AK$27,VLOOKUP($N$27,$B$8:$G$27,6,0)/$P$27,IF($AL$27="","",IF(AN7=$AL$27,VLOOKUP($O$27,$B$8:$G$27,6,0)/$P$27))))))))))))

Step 7:咖啡色區域之佈局設計，主要為顯示山積表所需要的T/T時間，輸入下方【EXCEL儲存格公式說明】中該欄位的公式後，點選儲存格BH8後下拉複製公式。