iT邦幫忙

0

請問:如何將EXCEL中欄A符合條件的欄B改為想要的值?

  • 分享至 

  • xImage

各位先進:
小弟固定要處理車輛的EXCEL報表,因為車輛所屬的地區常會異動,希望能有效率地處理異動的資料(月報表)。
舉例說明:
報表以月為單位,10個欄位中有兩個欄位與小弟的問題有關,一個是車號,一個是地區
因為調度的關係,有可能原屬於「台北」的「RCE-1517」號車,上個月調到高雄服務,報表出來後,小弟必須要將報表中車號為「RCE-1517」的列中,將地區這一欄的值,由「台北」改為「高雄」,然後做出樞紐分析
手動的作法,是將資料表用「車號」排序,再將「RCE-1517」所屬的地區欄的值,由「台北」改為「高雄」,然後再重新用第一欄的「流水號」排序...

想請問各位先進,要如何用VBA或是函數達成這項工作?
謝謝各位撥冗協助,非常感謝

看更多先前的討論...收起先前的討論...
froce iT邦大師 1 級 ‧ 2023-07-07 08:22:44 檢舉
寫當然是能寫,但你這才3步看起來也不複雜,你光學會拉個inputbox去改的時間你就改好了...
misadm iT邦高手 10 級 ‧ 2023-07-07 09:11:31 檢舉
您的手動步驟為:
1. 依車號排序
2. 找到要修改的車號
3. 變更(用鍵盤)上述車號的『地區』欄位

排序的動作應該是不需要的,直接用『尋找』即可,然後把『地區』欄位通通改成下拉式選單(資料驗證)。經過這樣的設定你的操作步驟會變成:
1. 搜尋車號
2. 選擇(用滑鼠)上述車號『地區』欄位

不知是否符合您的預期,還請參考。
terryliu iT邦新手 4 級 ‧ 2023-07-07 13:43:05 檢舉
謝謝 misadm 的回覆,月報表資料大概 2,000 多筆,用下拉一筆一筆改,好像不太理想,謝謝您
terryliu iT邦新手 4 級 ‧ 2023-07-07 13:53:38 檢舉
謝謝 force 大大,我的想法比較像是 rain_yu 提及的 VBA 方式,謝謝您撥空回覆哦
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
rain_yu
iT邦研究生 5 級 ‧ 2023-07-07 08:27:26
最佳解答

你可以使用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
看更多先前的回應...收起先前的回應...
terryliu iT邦新手 4 級 ‧ 2023-07-07 13:52:12 檢舉

謝謝 rain_yu 的回覆,我來試試看!
不過,最後的重新排序好像不用?因為它是逐列往下處理,發現符合即修改,好像沒有改變原來的順序?

terryliu iT邦新手 4 級 ‧ 2023-07-07 14:47:16 檢舉

Dear rain_yu 大大:
這一行一直出錯:
Set ws = ThisWorkbook.Worksheets("Sheet1")
我直接改成檔名,暫時是可以使用了,如下:
Set ws = Workbooks("Test.xlsx).Worksheets("Sheet1")

可以麻煩您看一下,ThisWorkbook 該怎麼修正嗎? 謝謝您

rain_yu iT邦研究生 5 級 ‧ 2023-07-11 09:06:07 檢舉

可以改成:,
Sheets("sheet名稱").Select
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
後面有ws的字眼都拿掉
Set rng = Range("A2:B" & lastRow)

btw 如果你要加的是xlsx,是不行的;
使用巨集的話,副檔名是xlsm

具體錯誤原因可能還是要給一下訊息和實際表單內容。
最有可能是因為版本不同,所以不能用。

rain_yu iT邦研究生 5 級 ‧ 2023-07-11 09:22:13 檢舉

補充一下,具體如何修正程式,還是要看你得表單
程式碼只是雛形,所以你還有很多地方要改。
舉例來說,如果以後你要輸入就修改且重新排序,
你會需要製作一個輸入的lable,輸入相關條件在執行。

terryliu iT邦新手 4 級 ‧ 2023-07-11 10:51:37 檢舉

Dear Rain_yu 大大:
謝謝您,我再測試看看,不過目前是可以用了,因為小弟下載回來的報表,檔名是一樣的。
Label的部份,的確是可以加上,執行時給兩個資料,一個是車號,一個是地區,這樣使用上會更靈活,謝謝您!

terryliu iT邦新手 4 級 ‧ 2023-07-11 11:02:28 檢舉

Dear Rain_yu 大大:
謝謝您,我測試過了,語法改為 Sheets("sheet名稱").Select 已經可以用了,謝謝您!
不知道 Label 的部份,您是否可以給個雛形,小弟再來測試看看,謝謝!

rain_yu iT邦研究生 5 級 ‧ 2023-07-11 12:40:06 檢舉

你截一下欄位圖給我,我在看怎麼設計

rain_yu iT邦研究生 5 級 ‧ 2023-07-11 15:54:35 檢舉

我新增程式碼了,我給你用輸入值做為參考的巨集;
你再根據實際需求修改程式碼吧

terryliu iT邦新手 4 級 ‧ 2023-07-12 16:10:17 檢舉

Dear rain_yu 大大:
謝謝您,我馬上來試試!
太感謝了!

rain_yu iT邦研究生 5 級 ‧ 2023-07-19 08:41:39 檢舉

你是先用車號排序然後找到要的車號後修改地區
直接改成輸入 車號,讓程式判斷找到車號後修改地區
這樣就不用重新排序了吧

rain_yu iT邦研究生 5 級 ‧ 2023-07-19 09:07:40 檢舉

只要輸入車號和現在與目標地區,就可以直接修改
幫你做了按鈕,可以直接點按鈕執行。
車號輸入錯誤和現在地區輸入錯誤都會跳訊息。
正確就會直接修改,如果需要跳修改前確認訊息,這部分你就自己研究吧
https://ithelp.ithome.com.tw/upload/images/20230719/20147438o9mMXs5EvF.png

rain_yu iT邦研究生 5 級 ‧ 2023-07-19 09:08:57 檢舉

以後有類似問題,將你的資料加工修改後,一並寫在問題內比較好解決

terryliu iT邦新手 4 級 ‧ 2023-07-19 16:51:58 檢舉

謝謝 rain_yu 大大,我正在研究您給的檔案中,有不懂的再請教您

rain_yu iT邦研究生 5 級 ‧ 2023-07-20 08:46:03 檢舉

xlsm是excel專門開啟VBA的格式,但是開起來跟excle一樣。
如果你連xlsm都不會用,那你前面究竟如何開啟與使用巨集?
一般xlsx格式是無法開啟巨集的。

rain_yu iT邦研究生 5 級 ‧ 2023-07-20 08:51:01 檢舉

不然就直接給你看程式吧,按鈕沒有就到巨集那邊點選執行也可以。
輸入列在第一列,資料列在第三列,格式要一樣,才會正常執行
https://ithelp.ithome.com.tw/upload/images/20230720/20147438ET9BJZRydC.png

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
terryliu iT邦新手 4 級 ‧ 2023-07-20 15:27:42 檢舉

我是用傳統方式,把巨集寫在 PERSONAL.XLSB 裡,再指定一個快速鍵,例如:CTRL+SHIFT+K,它就會執行了 (不是用按鈕)
我先來爬文,開始來用 xlsm,謝謝 rain_yu 大大

terryliu iT邦新手 4 級 ‧ 2023-07-20 15:37:53 檢舉

Dear rain_yu 大大
我知道問題了,因為 Google Drive 在我下載您給的範例時,會自動加上副檔名 xlsx,我剛才改好副檔名但仍不能用... (應該是被 Google 雞婆自動轉換成 xlsx 了)
您可以先壓成 Zip 再給我一次嗎? 因為我還是好奇您原本的作法,謝謝!

rain_yu iT邦研究生 5 級 ‧ 2023-07-24 08:45:06 檢舉
terryliu iT邦新手 4 級 ‧ 2023-07-26 11:09:48 檢舉

謝謝 rain-yu 大大,我成功下載了
研究中,感謝您

terryliu iT邦新手 4 級 ‧ 2023-07-26 15:28:59 檢舉

Dear rain_yu 大大:
所以,一開始的謎題也解開了,因為我不是用 xlsm 的方式,所以才會出現 Set ws = ThisWorkbook.Worksheets("Sheet1") 那行一直出錯的狀況,謝謝您!

rain_yu iT邦研究生 5 級 ‧ 2023-07-27 13:52:33 檢舉

可以用就好 (Y)/images/emoticon/emoticon12.gif

1
海綿寶寶
iT邦大神 1 級 ‧ 2023-07-07 11:04:45

我有一個笨方法
1.加一欄 C
2.加公式 C2,C3
3.複製欄 C, 貼上(回原欄 A)

https://ithelp.ithome.com.tw/upload/images/20230707/20001787gxQFv9fSWh.jpg

terryliu iT邦新手 4 級 ‧ 2023-07-07 15:24:30 檢舉

謝謝 海綿寶寶 您的回覆,我採用了 rain_yu 大大的方法,正在測試及優化中,謝謝!

我要發表回答

立即登入回答