帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-9-19 00:20 | 只看該作者

[發問] 如何找出前三個交期

各位高手，請教一下~
因為用Vlookup只能找到第一個日期
想要在追蹤工作表找出各種產品的前三個交期的日期該如何設定公式?
追蹤.jpg

找出交期.zip (6.93 KB)

樓主熱帖

Adam

p212

高中生 Rank: 3 Rank: 3

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

2^#

發表於 2014-9-19 08:42 | 只看該作者

本帖最後由 p212 於 2014-9-19 08:46 編輯

回復 1# adam2010
「追蹤」工作表之儲存格B2輸入陣列公式 (以Ctrl+Shift+Enter輸入)
=IFERROR(SMALL(IF(總表!$A$2:$A$10=$A2,總表!$C$2:$C$10,FALSE),COLUMN(A$1)),"")
向右、向下複製公式
請參考！

TOP

adam2010

中學生 Rank: 2

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

3^#

發表於 2014-9-19 13:43 | 只看該作者

感謝 p212大的協助，可以使用，但是我在提問的時候遺漏了一個重點...就是有些交期會有重複的部分
也就是如果A有2個9/1交期時就會變成最近交期1 & 2nd交期都會是9/1，3rd交期才會跳下一個9/10
是不是有辦法可以找出不同的交期，謝謝！

Adam

TOP

luhpro

高中生 Rank: 3 Rank: 3

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

4^#

發表於 2014-9-20 11:03 | 只看該作者

本帖最後由 luhpro 於 2014-9-20 11:05 編輯

回復 3# adam2010
使用儲存格公式的方式我想不出來,
在此提供一個 Excel VBA 程式完成的方式:

Sub nn()
Dim iI%
Dim lRow&
Dim sItem$
Dim bNFind As Boolean
Dim dDate As Date
Dim vA(), vD
ReDim vA(0 To 2, 0)
Set vD = CreateObject("Scripting.Dictionary")
lRow = 2
With Sheets("總表")
Do While .Cells(lRow, 1) <> ""
With .Cells(lRow, 1)
sItem = .Text
dDate = .Offset(, 2)
If Not vD.exists(sItem) Then
ReDim Preserve vA(0 To 2, UBound(vA, 2) + 1)
vA(0, UBound(vA, 2)) = dDate
vD(sItem) = UBound(vA, 2)
Else
bNFind = True
For iI = 0 To 2
If dDate = vA(iI, vD(sItem)) Then bNFind = False
Next
If bNFind Then
If vA(1, vD(sItem)) = "" Then
vA(1, vD(sItem)) = dDate
vA(2, vD(sItem)) = #12/31/9999#
Else
If dDate > vA(1, vD(sItem)) Then
If dDate < vA(2, vD(sItem)) Then vA(2, vD(sItem)) = dDate
Else
If dDate < vA(0, vD(sItem)) Then
vA(0, vD(sItem)) = dDate
vA(1, vD(sItem)) = vA(0, vD(sItem))
vA(2, vD(sItem)) = vA(1, vD(sItem))
Else
vA(1, vD(sItem)) = dDate
vA(2, vD(sItem)) = vA(1, vD(sItem))
End If
End If
End If
End If
End If
End With
lRow = lRow + 1
Loop
End With
lRow = 2
With Sheets("追蹤")
Do While .Cells(lRow, 1) <> ""
With .Cells(lRow, 1)
If vD.exists(.Text) Then
For iI = 0 To 2
.Offset(, iI + 1) = vA(iI, vD(.Text))
Next
End If
End With
lRow = lRow + 1
Loop
End With
End Sub

複製代碼

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2014-9-20 12:14 | 只看該作者

回復 1# adam2010

=IFERROR(SMALL(IF(FREQUENCY(IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10),IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10,-999)),總表!$C$2:$C$10),COLUMN(A1)),"")
陣列公式輸入(CTRL+SHIFT+ENTER參鍵齊按輸入公式)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

adam2010

中學生 Rank: 2

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

6^#

發表於 2014-9-20 15:47 | 只看該作者

感謝 luhpro & ML089 兩位出手相助，都可以使用，謝謝！
結果如下(讓大家參考)

找出交期-done.zip (18.29 KB)

Adam

TOP

adam2010

中學生 Rank: 2

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

7^#

發表於 2014-9-20 16:36 | 只看該作者

請教一下 ML089 大，=IFERROR(SMALL(IF(    FREQUENCY(    IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10),       IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10,-999)    ) ,總表!$C$2:$C$10),COLUMN(A1)),"")
其實我看不太懂為何這樣就可以跳過重複值~
其中 FREQUENCY(data_array, bins_array)
的 data_array是用IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10)
                                    問: 1.為何這個IF函數沒有[value_if_false]的值，是特殊用法嗎?
                                             2.意思是取該項目的所有交期當 data_array嗎?
而分組值bins_array用 IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10,-999)
                                    問:是否代表當總表有該項目就取得其交期，若無該項目就=-999(用其他數字好像也可以)
在FREQUENCY外層的IF就更看不懂了...................還是說陣列公式不是這樣拆解的
在下實在是資質駑鈍無法參透，是不是可以ML089大稍微解說一下，謝謝！

Adam

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 2014-9-20 23:16 | 只看該作者

回復 7# adam2010

請教一下 ML089 大，=IFERROR(SMALL(IF(    FREQUENCY(    IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10),       IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10,-999)    ) ,總表!$C$2:$C$10),COLUMN(A1)),"")
其實我看不太懂為何這樣就可以跳過重複值~
其中 FREQUENCY(data_array, bins_array)
的 data_array是用IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10)
                                    問: 1.為何這個IF函數沒有[value_if_false]的值，是特殊用法嗎?
                                             2.意思是取該項目的所有交期當 data_array嗎?
而分組值bins_array用 IF($A2=總表!$A$2:$A$10,總表!$C$2:$C$10,-999)
                                    問:是否代表當總表有該項目就取得其交期，若無該項目就=-999(用其他數字好像也可以)
在FREQUENCY外層的IF就更看不懂了...................還是說陣列公式不是這樣拆解的
在下實在是資質駑鈍無法參透，是不是可以ML089大稍微解說一下，謝謝！

回答
1. IF除用判斷值 TURE/FALSE外，0值為FALSE，其他值為TURE，公式中FREQUENCY回傳值中重複項第二位及以上者為0
2. 若無該項目就=-999(用其他數字好像也可以,但值要小於總表!$C$2:$C$10)
3. FREQUENCY({1;2;3;2;3;3},{1;2;3;2;3;3})回傳值為 {1;2;3;0;0;0;0}，所以0值為重覆項不是我們要的項目。
IF(FREQUENCY({1;2;3;2;3;3},{1;2;3;2;3;3}), {1;2;3;2;3;3,}) = IF( {1;2;3;0;0;0;0},{1,2,3,2,3,3}) = {1;2;3;FALSE;FALSE;FALSE;FALSE}

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

adam2010

中學生 Rank: 2

帖子: 145
主題: 42
精華: 0
積分: 193
點名: 0
作業系統: Windows7
軟體版本: Office2010
閱讀權限: 20
性別: 男
註冊時間: 2010-11-25
最後登錄: 2024-10-10

論壇贊助勳章

9^#

發表於 2014-9-22 22:13 | 只看該作者

感謝ML089版主詳盡的說明，似通未通，再研究一下，謝謝！

Adam

TOP