Excel 如何找到所有符合相同排序的欄位，並傳回下一欄位的值？

excel excel vba 比對

戴芄蘭 2019-06-25 12:03:55 ‧ 10190 瀏覽

大家好，

我遇到一個難題需要大家幫忙解答，如附圖，
我需要在 B欄中，找出所有"同時符合 A2、A3、A4的數值和排序"的儲存格範圍，
然後標示符合條件的範圍，並傳回下一欄的值在 C欄。

附圖操作說明：
在B欄中找出數值符合 4、5、4，並且排序相同的第一個儲存格範圍為 B4到B6，
再找到下一欄 B7的值為5，將 5填入 C2；繼續找到符合條件的第二個儲存格
範圍為 B6到 B8，再找到下一欄 B9的值為 10，將 10填入 C3。以此類推。

感謝大家的幫忙，祝平安喜樂。

ccutmis iT邦高手 2 級 ‧ 2019-06-25 13:10:29 檢舉

我會用python的解法不會用excel vba的解法

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

Neish

iT邦研究生 1 級 ‧ 2019-06-25 13:18:26

最佳解答

VBA解法參考如下

Sub Forum()

    Dim i As Integer
    Dim b_end As Integer
    Dim c_start As Integer
    
    ---B欄倒數第三個
    b_end = Range("B1").End(xlDown).Row - 2
    
    For i = 2 To b_end
    
        If Range("B" & i) = Range("A2") And _
            Range("B" & i + 1) = Range("A3") And _
            Range("B" & i + 2) = Range("A4") Then
        
            ---找C欄空白的地方(要放值的地方)
            c_start = Columns("C").Find("").Row
            
            Range("C" & c_start) = Range("B" & i + 3)
            
        End If
    
    Next
    
End Sub

回應 3
分享
檢舉

戴芄蘭 iT邦新手 5 級 ‧ 2019-06-25 18:31:47 檢舉

感謝Neish迅速解答，幫忙非常大！

我直接套用編碼至問題舉例中，執行沒問題，
可以找出所有符合條件的值。

我實際使用會有 42組不同排序的 A欄，每組包含的值數目有 2~500個，
並且要個別在包含 10,000~30,000個值的 42組 B欄中進行比對。

我將直接套用解答的編碼並改寫條件使用，
若有發生問題，再勞各位幫忙，感謝。

ccenjor iT邦高手 1 級 ‧ 2019-06-27 18:16:03 檢舉

不好意思，我的是用函數做的比較笨。

首先增加D欄為輔助欄，在D4儲存格輸入公式「=IF(COUNTIFS(B2,$A$2,B3,$A$3,B4, $A$4)>0,B5,0)」，並將公式複製到D5:D15儲存格。

在C2儲存格輸入公式「=IFERROR(INDEX($B$2:$B$100,(SUMPRODUCT(LARGE(--($D$2:$D$100>=1)*ROW($D$2:$D$100),COUNTIF($D$2:$D$100,">0")-ROW()+2))),0)," ")」，並將公式複製到C3:C15儲存格。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 00:54:38 檢舉

我使用Neish提供的解法，如果B欄超過32,767個值，會遇到"執行階段錯誤 6: 溢位"。

自己 google之後，把 i和 b_end都宣告成 Long資料類型，就可以正常執行了。

以 A欄變數 X1有 6個值，在 B欄變數 D1中比對為例，改寫後編碼如下，若有誤請指正，謝謝。

Sub Forum()

    Dim i As Long
    Dim b_end As Long
    Dim c_start As Integer
    
    'B欄倒數第6個
    b_end = Range("B1").End(xlDown).Row - 5
    
    For i = 2 To b_end
    
        If Range("B" & i) = Range("A2") And _
            Range("B" & i + 1) = Range("A3") And _
            Range("B" & i + 2) = Range("A4") And _
            Range("B" & i + 3) = Range("A5") And _
            Range("B" & i + 4) = Range("A6") And _
            Range("B" & i + 5) = Range("A7") Then
        
            '找C欄空白的地方(要放值的地方)
            c_start = Columns("C").Find("").Row
            
            Range("C" & c_start) = Range("B" & i + 6)
            
        End If
    
    Next
    
End Sub

登入發表回應