Board logo

標題: [發問] 如何找出前三個交期 [打印本頁]
作者: adam2010    時間: 2014-9-19 00:20     標題: 如何找出前三個交期

各位高手，請教一下~
因為用Vlookup只能找到第一個日期
想要在追蹤工作表找出各種產品的前三個交期的日期該如何設定公式?
[attach]19178[/attach]
[attach]19177[/attach]
[attach]19179[/attach]
作者: p212    時間: 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)),"")
向右、向下複製公式
請參考！
作者: adam2010    時間: 2014-9-19 13:43

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

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

回復 3# adam2010
使用儲存格公式的方式我想不出來,
在此提供一個 Excel VBA 程式完成的方式:
  1. Sub nn()
  2.   Dim iI%
  3.   Dim lRow&
  4.   Dim sItem$
  5.   Dim bNFind As Boolean
  6.   Dim dDate As Date
  7.   Dim vA(), vD
  8.   
  9.   ReDim vA(0 To 2, 0)
  10.   Set vD = CreateObject("Scripting.Dictionary")
  11.   
  12.   lRow = 2
  13.   With Sheets("總表")
  14.     Do While .Cells(lRow, 1) <> ""
  15.       With .Cells(lRow, 1)
  16.         sItem = .Text
  17.         dDate = .Offset(, 2)
  18.         If Not vD.exists(sItem) Then
  19.           ReDim Preserve vA(0 To 2, UBound(vA, 2) + 1)
  20.           vA(0, UBound(vA, 2)) = dDate
  21.           vD(sItem) = UBound(vA, 2)
  22.         Else
  23.           bNFind = True
  24.           For iI = 0 To 2
  25.             If dDate = vA(iI, vD(sItem)) Then bNFind = False
  26.           Next
  27.           If bNFind Then
  28.             If vA(1, vD(sItem)) = "" Then
  29.               vA(1, vD(sItem)) = dDate
  30.               vA(2, vD(sItem)) = #12/31/9999#
  31.             Else
  32.               If dDate > vA(1, vD(sItem)) Then
  33.                 If dDate < vA(2, vD(sItem)) Then vA(2, vD(sItem)) = dDate
  34.               Else
  35.                 If dDate < vA(0, vD(sItem)) Then
  36.                   vA(0, vD(sItem)) = dDate
  37.                   vA(1, vD(sItem)) = vA(0, vD(sItem))
  38.                   vA(2, vD(sItem)) = vA(1, vD(sItem))
  39.                 Else
  40.                   vA(1, vD(sItem)) = dDate
  41.                   vA(2, vD(sItem)) = vA(1, vD(sItem))
  42.                 End If
  43.               End If
  44.             End If
  45.           End If
  46.         End If
  47.       End With
  48.       lRow = lRow + 1
  49.     Loop
  50.   End With
  51.   
  52.   lRow = 2
  53.   With Sheets("追蹤")
  54.     Do While .Cells(lRow, 1) <> ""
  55.       With .Cells(lRow, 1)
  56.         If vD.exists(.Text) Then
  57.           For iI = 0 To 2
  58.             .Offset(, iI + 1) = vA(iI, vD(.Text))
  59.           Next
  60.         End If
  61.       End With
  62.       lRow = lRow + 1
  63.     Loop
  64.   End With
  65. End Sub
複製代碼
作者: ML089    時間: 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參鍵齊按輸入公式)
作者: adam2010    時間: 2014-9-20 15:47

感謝 luhpro & ML089 兩位出手相助，都可以使用，謝謝！
結果如下(讓大家參考)
[attach]19192[/attach]
作者: adam2010    時間: 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大稍微解說一下，謝謝！
作者: ML089    時間: 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}
作者: adam2010    時間: 2014-9-22 22:13

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



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)