麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 如何用公式讓他自動對應原始資料 [打印本頁]

作者: maomin 時間: 2010-7-21 21:56 標題: 如何用公式讓他自動對應原始資料

本帖最後由 maomin 於 2010-7-21 21:59 編輯

[attach]1922[/attach]

原始資料是一串連續續資料爾且偶而會變動

SHEET2是每周必須發給現場的資料,讓她們知道每天要出哪些訂單

SHEET2可以用公式讓他自動對應原始資料嗎?這樣我就不用一個一個打了,而且還會打錯

例如:我在上面日期打7/5號
預計日期    LOT          客戶
  6/21    990387       PIG
  7/12    990388       PIG
  6/17    990822-7    PIG
   .             .             .
   .             .             .

作者: Hsieh 時間: 2010-7-21 22:43

回復 1# maomin

A3陣列公式
{=IF(ROW($A1)>COUNTIF(原始資料!$A:$A,A$1),"",INDIRECT("原始資料!R"&SMALL(IF(原始資料!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}
B3陣列公式
{=IF(ROW($A1)>COUNTIF(原始資料!$A:$A,A$1),"",INDIRECT("原始資料!R"&SMALL(IF(原始資料!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}
C3陣列公式
{=IF(ROW($A1)>COUNTIF(原始資料!$A:$A,A$1),"",INDIRECT("原始資料!R"&SMALL(IF(原始資料!$A$1:$A$121=A$1,ROW($1:$121)),ROW($A1))&"C"&COLUMN()+1-INT((COLUMN()-1)/3)*3,0))}

作者: luhpro 時間: 2010-7-21 23:15

以下是我想到的方式比較直覺一些供你參考 :
1. 先在每一格前面新增一空白欄(例如 "A"、"E" ...欄),做為索引值以找出每一筆要秀的資料.

2. 在索引值那欄的第3列(即資料顯示的開始列)依據該格上方的日期找出"原始資料" 中第一筆該日期資料的列號.
A3 = MATCH(B$1,原始資料!$A$1

A$500,0)
  *** E3 、 I3 ... 皆可直接複製公式套用.

3. 依次增加索引欄的列號數字,並比對 "原始資料" 中該列號的日期是否與上方的日期相同,若相同則顯示出該列號,若不同則表示該日期資料已找尋完畢,以下皆不再顯示任何資料.
A4 = IF(A3="","",IF(INDIRECT(CONCATENATE("原始資料!","A",A3+1))=B$1,A3+1,""))
  *** E5 、 I5 ... 皆可直接複製公式套用.
  *** A5 以下皆可直接複製公式套用.

4. 依據索引欄的列號數字依次將 "預計日期"、"L O T"、"客戶"三欄的資料帶出來.
B3 = IF(A3="","",INDIRECT(CONCATENATE("原始資料!","B",A3)))
C3 = IF(A3="","",INDIRECT(CONCATENATE("原始資料!","C",A3)))
D3 = IF(A3="","",INDIRECT(CONCATENATE("原始資料!","D",A3)))
  *** F3 - H3 、 J3 - L3 ... 皆可直接複製公式套用.
  *** B4 - D4 以下皆可直接複製公式套用.

5. 公式只延伸到各欄的第70列, 若還需要延伸只需以第70列為公式範本,同時將四個資料欄一直複製下去(即Mark B70 ~ D70 然後游標移到D70右下角,按著滑鼠左鍵往下拉即可)即可.

6. 最後再將 "A"、"E" ...等索引欄 "隱藏" 起來即可.

7. 測試方式 : 直接修改 "B1"、"F1"...等儲存格的內的日期, 底下 B3 - D3 及其他 "B" - "D" 欄的值會相應而變.

8. 當於儲存格使用帶有索引功能的公式時,請務必記得原始資料不要有刪除整列(或某些情形下的整欄)的情形,不然會導致公式出現錯誤.

程式如附件,請參考.

作者: freeffly 時間: 2010-7-21 23:32

我覺得Hsieh大大的方式比較好
不用加輔助欄
又學到一個新方法

作者: luhpro 時間: 2010-7-21 23:57

我也一直在向Hsieh大學習中,
或許是自己 Excel 程度還是太淺,
常常有些公式看的不是很懂,
就像上面那些 "陣列公式" 文字好像就不能直接貼到 A3 - C3 儲存格,
在我這會被辨識成 "文字" 而非公式.

但若將 { 與 } 拿掉內容又會變成其上一格的內容,
且也不能用滑鼠下拉的方式複製到底下的儲存格內, (會有錯誤).

我想應該是我還不明白 "陣列公式" 要怎麼套用到儲存格內.

作者: maomin 時間: 2010-7-22 01:40

感謝各位大大的指教

謝謝你們,問題已經解決了

作者: Hsieh 時間: 2010-7-22 07:14

回復 5# luhpro

複製公式貼上後,將{}刪除
CTRL+SHIFT+ENTER輸入

作者: luhpro 時間: 2010-7-22 21:43

本帖最後由 luhpro 於 2010-7-22 22:56 編輯

回復 7# Hsieh

嗯...
原來要用 Ctrl + Shift + Enter 輸入才會出現 { },
之前在舊網站上看到卻一直試不出來,
找說明、查書、上網站都不得其門而入,
又發現好像都沒有人有問過想說那可能是常識,
如此就更不好意思問了.

另外不知道有與沒有 { } 的差別在哪裡呢?
謝謝你的解答.^^

回復 9# gong
嗯...這樣我就了解了,
謝謝 gong 大的解答.

作者: gong 時間: 2010-7-22 22:41

{}是陣列公式
陣列可以是一組數,在一個儲存格內存在
將該公式全選按f9看看

如果沒有{}
那就是一般公式,在該儲存格內存在著一個值,
該值為該陣列第一組數

差別為一組數與一個數

作者: Andy2483 時間: 2023-3-14 15:05

回復 1# maomin

謝謝前輩發表此主題與範例
謝謝各位前輩指導
後學藉此主題練習VBA字典與陣列,請各位前輩再指導

資料表:
[attach]35939[/attach]

結果表執行前:
[attach]35940[/attach]

執行結果:
[attach]35941[/attach]

Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 100, 1 To 3), i&, j&, k&, TT, T(1 To 4), Y, A, D$
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([原始資料!D2], [原始資料!A65536].End(3))
For i = 1 To UBound(Brr)
For k = 1 To 4: T(k) = Brr(i, k): TT = TT & "|" & T(k): Next
If Y(TT) <> "" Or T(1) = "" Then GoTo i01
A = Y(T(1) & "/a")
If Not IsArray(A) Then A = Crr
D = T(1): Y(D) = Y(D) + 1
For j = 1 To 3: A(Y(D), j) = T(j + 1): Next
Y(T(1) & "/a") = A: Y(TT) = 1: TT = ""
i01:
Next
With Sheets("SHEET2")
.UsedRange.Offset(2, 0).ClearContents
For i = 1 To 13 Step 3
D = .Cells(1, i): .Cells(3, i).Resize(Y(D), 3) = Y(D & "/a")
Next
Application.Goto .[A1]
End With
Set Y = Nothing: Erase Brr, Crr, T, A
End Sub

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