iT邦幫忙

0

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

大家好,

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

https://ithelp.ithome.com.tw/upload/images/20190625/20118578PKIX19Kj6A.jpg

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

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

ccutmis iT邦高手 8 級 ‧ 2019-06-25 13:10:29 檢舉
我會用python的解法 不會用excel vba的解法

1 個回答

1
Neish
iT邦研究生 2 級 ‧ 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
戴芄蘭 iT邦新手 5 級 ‧ 2019-06-25 18:31:47 檢舉

感謝Neish迅速解答,幫忙非常大!

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

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

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

ccenjor iT邦新手 4 級 ‧ 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

我要發表回答

立即登入回答