excel 表格的半自動處理

朋友是業務，業務的工作之一，就是整理要給客戶的報價單。

下圖，就是朋友的工作。左邊四個欄位是原始資料。最右邊的output 欄位是他要透過查表，才能寫出公司的 part number 型號。

朋友詢問我，是否有辦法可以幫他寫個查表的程式，可以讓他不需要依賴人力來處理愚蠢的查表工作。我構思了一下，又覺得有困難。原因是，他公司的 excel 表格，合併儲存格的部分，相當不規律，我很難用 excel 轉 csv 的方式，做統一的處理。但是，仔細思考之後，發現其實可以用半自動化的方式來解這個問題。

怎樣算是半自動解呢？由於解除儲存格，將儲存格填滿的部分，對於業務來說，其實是很容易的，也不太耗時間，這個部分就還是由人手工來做。我只用程式處理最後的查表工作。

同時，仔細想想，用 script language 去處理 csv 檔，就會需要朋友手動做匯出。其實這個操作對一般人很不直覺。

既然需要的資料都已經在同一個 row 上了，就寫了一隻使用者自訂函數來處理。

在 John Bentley 所著 Programming Pearls 一書中，也有一段話寫到相似的解題想法 ---
Keeping track of our organization's budget looked difficult to me. Out of habit, I would have built a large program for the job, with a clunky user interface. The next programmer took a broader view, and implemented the program as a spreadsheet, supplemented by a few functions in Visual Basic. The interface was totally natural for the accounting people who were the main users. 

	REM ***** BASIC *****
	Function PARTNUMBER(controller, flash, grade, capacity)
	Select Case controller
	Case "S9":
	controller_fix = "50UD"
	Case "S10":
	controller_fix = "52UD"
	End Select
	Select Case flash
	Case "SLC":
	Select Case grade
	Case "Diamond":
	flashgrade = "IXU"
	Case "Commercial":
	flashgrade = "CXU"
	End Select
	Case "pSLC":
	Select Case grade
	Case "Diamond":
	flashgrade = "PXU"
	Case "Commercial":
	flashgrade = "KXU"
	End Select
	End Select
	Select Case Len(capacity)
	Case 3:
	capacity_fix = "00"+capacity
	Case 4:
	capacity_fix = "0"+capacity
	Case 5:
	capacity_fix = capacity
	End Select
	PARTNUMBER = "CFS-" + controller_fix + capacity_fix + "-" + flashgrade
	End Function

view raw Calc_user_defined_function.bas hosted with ❤ by GitHub