各位先進:
小弟固定要處理車輛的EXCEL報表,因為車輛所屬的地區常會異動,希望能有效率地處理異動的資料(月報表)。
舉例說明:
報表以月為單位,10個欄位中有兩個欄位與小弟的問題有關,一個是車號,一個是地區
因為調度的關係,有可能原屬於「台北」的「RCE-1517」號車,上個月調到高雄服務,報表出來後,小弟必須要將報表中車號為「RCE-1517」的列中,將地區這一欄的值,由「台北」改為「高雄」,然後做出樞紐分析
手動的作法,是將資料表用「車號」排序,再將「RCE-1517」所屬的地區欄的值,由「台北」改為「高雄」,然後再重新用第一欄的「流水號」排序...
想請問各位先進,要如何用VBA或是函數達成這項工作?
謝謝各位撥冗協助,非常感謝
你可以使用Excel的VBA功能來自動處理異動的資料。以下是一個示範的VBA程式碼,可以將原屬於「台北」的「RCE-1517」號車的地區改為「高雄」:
Sub UpdateRegion()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
'設定要處理的工作表
Set ws = ThisWorkbook.Worksheets("報表")
'找出最後一行
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'找出車號欄位和地區欄位的範圍
Set rng = ws.Range("A2:B" & lastRow)
'遍歷每個格子
For Each cell In rng
'如果車號為RCE-1517,將地區改為高雄
If cell.Value = "RCE-1517" Then
cell.Offset(0, 1).Value = "高雄"
End If
Next cell
'重新排序
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
請將程式碼中的"報表"替換為你實際使用的工作表名稱。然後,你可以在Excel中按下Alt + F11進入VBA編輯器,將程式碼粘貼到一個新的模塊中,然後運行此程式碼。
這個程式碼首先找到要處理的工作表,然後找到車號欄位和地區欄位的範圍。接下來,它遍歷範圍中的每個單元格,如果車號為"RCE-1517",則將地區改為"高雄"。最後,它重新排序工作表,以恢復原始的流水號順序。
下列是用A1、B1作為車號和地區的輸入值,修改後的VBA,
原本是想看要不要做輸入值+按鈕的形式,
但是我覺得有輸入值+執行就可以
Sub UpdateRegion()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
Dim region As String
Dim carNumber As String
'設定要處理的工作表
Set ws = ThisWorkbook.Worksheets("報表")
'取得輸入值
region = ws.Range("A1").Value
carNumber = ws.Range("B1").Value
'找出最後一行
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'找出車號欄位和地區欄位的範圍
Set rng = ws.Range("A2:B" & lastRow)
'遍歷每個格子
For Each cell In rng
'如果車號符合輸入值,將地區改為輸入值
If cell.Value = carNumber Then
cell.Offset(0, 1).Value = region
End If
Next cell
'重新排序
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
謝謝 rain_yu 的回覆,我來試試看!
不過,最後的重新排序好像不用?因為它是逐列往下處理,發現符合即修改,好像沒有改變原來的順序?
Dear rain_yu 大大:
這一行一直出錯:
Set ws = ThisWorkbook.Worksheets("Sheet1")
我直接改成檔名,暫時是可以使用了,如下:
Set ws = Workbooks("Test.xlsx).Worksheets("Sheet1")
可以麻煩您看一下,ThisWorkbook 該怎麼修正嗎? 謝謝您
可以改成:,
Sheets("sheet名稱").Select
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
後面有ws的字眼都拿掉
Set rng = Range("A2:B" & lastRow)
btw 如果你要加的是xlsx,是不行的;
使用巨集的話,副檔名是xlsm
具體錯誤原因可能還是要給一下訊息和實際表單內容。
最有可能是因為版本不同,所以不能用。
補充一下,具體如何修正程式,還是要看你得表單
程式碼只是雛形,所以你還有很多地方要改。
舉例來說,如果以後你要輸入就修改且重新排序,
你會需要製作一個輸入的lable,輸入相關條件在執行。
Dear Rain_yu 大大:
謝謝您,我再測試看看,不過目前是可以用了,因為小弟下載回來的報表,檔名是一樣的。
Label的部份,的確是可以加上,執行時給兩個資料,一個是車號,一個是地區,這樣使用上會更靈活,謝謝您!
Dear Rain_yu 大大:
謝謝您,我測試過了,語法改為 Sheets("sheet名稱").Select 已經可以用了,謝謝您!
不知道 Label 的部份,您是否可以給個雛形,小弟再來測試看看,謝謝!
你截一下欄位圖給我,我在看怎麼設計
我新增程式碼了,我給你用輸入值做為參考的巨集;
你再根據實際需求修改程式碼吧
Dear rain_yu 大大:
謝謝您,我馬上來試試!
太感謝了!
你是先用車號排序然後找到要的車號後修改地區
直接改成輸入 車號,讓程式判斷找到車號後修改地區
這樣就不用重新排序了吧
只要輸入車號和現在與目標地區,就可以直接修改
幫你做了按鈕,可以直接點按鈕執行。
車號輸入錯誤和現在地區輸入錯誤都會跳訊息。
正確就會直接修改,如果需要跳修改前確認訊息,這部分你就自己研究吧
以後有類似問題,將你的資料加工修改後,一並寫在問題內比較好解決
謝謝 rain_yu 大大,我正在研究您給的檔案中,有不懂的再請教您
xlsm是excel專門開啟VBA的格式,但是開起來跟excle一樣。
如果你連xlsm都不會用,那你前面究竟如何開啟與使用巨集?
一般xlsx格式是無法開啟巨集的。
不然就直接給你看程式吧,按鈕沒有就到巨集那邊點選執行也可以。
輸入列在第一列,資料列在第三列,格式要一樣,才會正常執行
Sub UpdateRegion()
Dim lastRow As Integer
Dim i As Integer
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lastRow
If Cells(i, 7).Value = Range("B1").Value Then
If Cells(i, 4).Value = Range("D1").Value Then
Cells(i, 4).Value = Range("F1").Value
Else
MsgBox "Region values are not equal"
End If
End If
Next i
End Sub
我是用傳統方式,把巨集寫在 PERSONAL.XLSB 裡,再指定一個快速鍵,例如:CTRL+SHIFT+K,它就會執行了 (不是用按鈕)
我先來爬文,開始來用 xlsm,謝謝 rain_yu 大大
Dear rain_yu 大大
我知道問題了,因為 Google Drive 在我下載您給的範例時,會自動加上副檔名 xlsx,我剛才改好副檔名但仍不能用... (應該是被 Google 雞婆自動轉換成 xlsx 了)
您可以先壓成 Zip 再給我一次嗎? 因為我還是好奇您原本的作法,謝謝!
我有一個笨方法
1.加一欄 C
2.加公式 C2,C3
3.複製欄 C, 貼上值
(回原欄 A)