"請高手幫忙"跨表多條件取值

Question

"請高手幫忙"跨表多條件取值
查出符合代號.組別條件的日期與時段
如果太複雜只要能查出符合的時段也可以
請求幫忙協助
(不要用vba巨集)

Randy Chan · Accepted Answer

工作繁複，不用VBA很難成事。

2010-12-26 17:41:54 補充：
以下公式只抓回 "時段"，公式長度 295 字元，如果要連抓 "日期 時段" , 公式長達600字元。

B5
=INDEX(一月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,一月!$C$2:$C$100))*(一月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))&INDEX(二月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,二月!$C$2:$C$100))*(二月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))&

(未完)

2010-12-26 17:43:20 補充：
(接樓上公式)

INDEX(三月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,三月!$C$2:$C$100))*(三月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))
三鍵連按：Ctrl-Shift-Enter

2010-12-26 20:33:37 補充：
公式

求 "時段"
B5
=INDEX(一月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,一月!$C$2:$C$100))*(一月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))&INDEX(二月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,二月!$C$2:$C$100))*(二月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))&INDEX(三月!$B:$B,MIN(IF(ISNUMBER(FIND(B$4,三月!$C$2:$C$100))*(三月!$D$2:$D$100=$A5),ROW($2:$100),4^8)))
三鍵連按：Ctrl-Shift-Enter

～～～

VBA
求 "日期 時段"

Sub XXX()

'將所有原資料工作表寫入下行
SHTS = Array("一月", "二月", "三月")

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

For Each SH In SHTS
    Sheets(SH).Select
    nrow = Cells(Rows.Count, 3).End(xlUp).Row
   
    For r = 2 To nrow
        xs = Split(Cells(r, 3), "/")
        For Each x In xs
            k = x & " . , : ; " & Cells(r, 4)
            dic(k) = Cells(r, 1) & " " & Cells(r, 2)
        Next
    Next
Next

Sheets("總表").Select
nrow = Cells(Rows.Count, 1).End(xlUp).Row
groups = Array("A", "B", "C", "D")

For r = 5 To nrow
    For c = 2 To 5
        k = groups(c - 2) & " . , : ; " & Cells(r, 1)
        Cells(r, c) = dic(k)
    Next
Next

Set dic = Nothing

End Sub

"請高手幫忙"跨表多條件取值

1 個解答