這一年來,老婆做社群團購上瘾了,每周都會有一大批訂單蜂擁而至,訂單都是在微信群裡接龍生成的,送貨時,就遇到了訂單列印的問題,老婆要求我對訂單進行以下處理:
1、分列:把訂單的房号和貨物清單内容分成兩列,友善查閱;
2、排序:把訂單按房号進行排序,友善鄰居來取貨時,快速查詢訂單明細。
微信接龍原始訂單格式
看到訂單格式,我就思考以下幾個問題:
1、分列問題:發現訂單中,房号與貨物清單之間,有的有空格,有的沒空格;有的有一個空格,有的有多個空格;這在EXCEL中手工操作分列處理時,要把沒有空格的加上一個分隔符,以便有可用的“分隔符号”供EXCEL軟體識别;做上述處理後,就可以在EXCEL中做“分列”操作,把訂單資料分為三列,序号1列,房号1列,貨物清單内容1列;
2、排序問題:分列後,要對訂單資料按房号進行升序排序,排序時發現問題,房号2B-4A會被排在房号2B-27H的後面,這是因為EXCEL判斷的依據是房号2B-4A的第4位字元4>房号2B-27H的第4位字元2,而不是我們以為的4<27;如果要實作正确的排序,則要在2B-4A中,4的前面補上一個0,變為2B-04A;這樣在排序時,2B-04A就會按照要求排在2B-27H的前面;做上述處理後,排序結果正确。
3、以上操作都是在EXCEL中手工完成,那麼如何用VBA代碼來達成上述所要的結果呢,這時候我求助了ChatGPT。
ChatGPT能幫我們生成代碼,但首先是你的訴求資訊要準确,不會被誤解,是以我們在描述功能需求時,要密切注意表達的内容要做到詳細并有确定性。
在ChatGPT中請求生成VBA代碼時,我的請求表述如下:
幫我寫一段VBA代碼,在EXCEL表中,在A列的每個單元格中,從右邊開始找到第一個英文字母,在這個英文字母後插入符号“,”;将A列中包含的空格全部删除;将A列内容進行“分列”,“分隔符号”為”.”,将分列後的内容寫入右側相鄰的單元格;再将C列内容進行“分列”,“分隔符号“為”,”,将分列後的内容寫入C列和D列;如果C列單元格中的字元位數為5,但左起第1個字元不是“1“,則尋找單元格中的“-“符号,在”-“符号後面插入一個”0“; 如果C列單元格中的字元位數為4,則尋找單元格中的“-“符号,在”-“符号後面插入一個”0“;對C列内容按升序進行排序,對B列内容按1、2、3等差數列重新進行編号;所有有内容的單元格加上框線。
由于ChatGPT一次處理内容篇幅有限,上述300個字的訴求内容,ChatGPT處理到一半就會中止,無法完成後半部分的代碼生成,為了讓ChatGPT能生成完成的代碼,我把上述300字的訴求内容分為兩段來發送:
第一段,
幫我寫一段VBA代碼,在EXCEL表中,在A列的每個單元格中,從右邊開始找到第一個英文字母,在這個英文字母後插入符号“,”;将A列中包含的空格全部删除;将A列内容進行“分列”,“分隔符号”為”.”,将分列後的内容寫入右側相鄰的單元格;再将C列内容進行“分列”,“分隔符号“為”,”,将分列後的内容寫入C列和D列;
第二段,
幫我寫一段VBA代碼,在EXCEL表中,如果C列單元格中的字元位數為5,但左起第1個字元不是“1“,則尋找單元格中的“-“符号,在”-“符号後面插入一個”0“; 如果C列單元格中的字元位數為4,則尋找單元格中的“-“符号,在”-“符号後面插入一個”0“;對C列内容按升序進行排序,對B列内容按1、2、3等差數列重新進行編号;所有有内容的單元格加上框線。
大家看到,上述請求内容,相比EXCEL軟體手工處理流程,增加了以下細節:
1、由于房号和後面清單内容之間時常會包含多個空格,如果以空格為分隔符号,分列時,VBA代碼會将多個空格視作多個分隔符号,最終會将内容分隔為多個空白列,這并不是我們所想的結果,是以需要先清除訂單内容中的全部空格後,才不會出現上述錯誤;
訂單内容清除空格的代碼如下:
Cells(i, "A").Value = Replace(Cells(i, "A").Value, " ", "")
2、清除全部空格後,你就會發現房号與貨物清單之間缺少分隔符号,無法實作分列操作,那麼就需要在房号後面補上分隔符号,譬如“:”,這樣就可以實作讓系統自動進行房号與貨物清單的分列操作;請求内容為:從右邊開始找到第一個英文字母,在這個英文字母後插入符号“;”.
代碼如下:
Do While letterPos > 0
If Mid(cellValue, letterPos, 1) Like "[A-Za-z]" Then
Cells(i, "A").Value = Left(cellValue, letterPos) & ":" & Right(cellValue, Len(cellValue) - letterPos)
Exit Do
End If
letterPos = letterPos – 1
Loop
3、分列後,在對房号排序時發現,單數字房号,在進行降序排序時會排到雙數字房号後面,譬如2B-4A會被排序2B-27H的後面,這樣就需要在4A前插入一個0。用代碼的處理就是先判斷房号的字元位數,如果字元位數為5,就是左起第一個英文字母前加”0”;考慮到有格式如1-4B的房号,要再加個條件“左起第一個字母不為1”;那就是,如果房号字元位數為5,左起第一個字母不為1,就在-符号後面加0;還有一個條件,如果格式如1-4B的房号,就是,如果房号字元位數為4,左起第一個字母為1,就在-符号後面加0;
代碼如下:
For i = 1 To Cells(Rows.count, "C").End(xlUp).Row
'如果字元位數為 5 且左起第 1 個字元不是 "1"
If Len(Cells(i, "C").Value) = 5 And Left(Cells(i, "C").Value, 1) <> "1" Then
'尋找單元格中的 "-" 符号
Cells(i, "C").Value = Replace(Cells(i, "C").Value, "-", "-0")
'如果字元位數為 4
ElseIf Len(Cells(i, "C").Value) = 4 Then
'尋找單元格中的 "-" 符号
Cells(i, "C").Value = Replace(Cells(i, "C").Value, "-", "-0")
End If
Next i
其它的如排序、編序号、單元格加框線的操作都不難,這裡不再贅述。
将訴求内容發送給ChatGPT獲得自動生成的VBA代碼
将訴求内容發給ChatGPT,ChatGPT自動生成代碼,再把生成的代碼張貼到EXCEL的開發工具菜單中的VBA對話框中,點選運作按鈕,EXCEL自動進行資料處理,生成我們希望得到的結果。
原始訂單資料經VBA處理後提到的結果符合訴求
附錄:完整代碼如下,
Sub ModifyData()
Dim lastRow As Long
lastRow = Cells(Rows.count, "A").End(xlUp).Row
Dim i As Long
For i = 1 To lastRow
' 從右邊開始找到第一個英文字母,在這個英文字母後插入符号":"
Dim cellValue As String
cellValue = Cells(i, "A").Value
Dim letterPos As Integer
letterPos = Len(cellValue)
Do While letterPos > 0
If Mid(cellValue, letterPos, 1) Like "[A-Za-z]" Then
Cells(i, "A").Value = Left(cellValue, letterPos) & ":" & Right(cellValue, Len(cellValue) - letterPos)
Exit Do
End If
letterPos = letterPos - 1
Loop
' 将A列中包含的空格全部删除
Cells(i, "A").Value = Replace(Cells(i, "A").Value, " ", "")
' 将A列内容進行"分列","分隔符号"為".",将分列後的内容寫入右側相鄰的單元格
Dim arr() As String
arr = Split(Cells(i, "A").Value, ".")
Cells(i, "B").Value = arr(0)
Cells(i, "C").Value = arr(1)
' 再将C列内容進行"分列","分隔符号"為":",将分列後的内容寫入C列和D列
arr = Split(Cells(i, "C").Value, ":")
Cells(i, "C").Value = arr(0)
Cells(i, "D").Value = arr(1)
Next i
'周遊 C 列的每個單元格
For i = 1 To Cells(Rows.count, "C").End(xlUp).Row
'如果字元位數為 5 且左起第 1 個字元不是 "1"
If Len(Cells(i, "C").Value) = 5 And Left(Cells(i, "C").Value, 1) <> "1" Then
'尋找單元格中的 "-" 符号
Cells(i, "C").Value = Replace(Cells(i, "C").Value, "-", "-0")
'如果字元位數為 4
ElseIf Len(Cells(i, "C").Value) = 4 Then
'尋找單元格中的 "-" 符号
Cells(i, "C").Value = Replace(Cells(i, "C").Value, "-", "-0")
End If
Next i
'對 C 列内容按升序進行排序
With Range("C1", Cells(Rows.count, "C").End(xlUp))
.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
End With
'對 B 列内容按 1、2、3 等差數列重新進行編号
For i = 1 To Cells(Rows.count, "B").End(xlUp).Row
Cells(i, "B").Value = i
Next i
'所有有内容的單元格加上框線
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
For Each rng In ws.UsedRange
If Len(rng.Value) > 0 Then
rng.Borders.LineStyle = xlContinuous
End If
Next rng
End Sub