返回列表 上一主題 發帖

[發問] 如何用公式讓他自動對應原始資料

maomin

帖子
7 
主題
4 
精華
0 
積分
53 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2010-7-20 
最後登錄
2020-3-9 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2010-7-21 21:56 | 只看該作者

[發問] 如何用公式讓他自動對應原始資料

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

AAA.rar (5.52 KB)

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

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

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

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

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
255  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-25 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
2#
發表於 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))}
學海無涯_不恥下問

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
3#
發表於 2010-7-21 23:15 | 只看該作者
以下是我想到的方式比較直覺一些供你參考 :
1. 先在每一格前面新增一空白欄(例如 "A"、"E" ...欄),做為索引值以找出每一筆要秀的資料.

2. 在索引值那欄的第3列(即資料顯示的開始列)依據該格上方的日期找出"原始資料" 中第一筆該日期資料的列號.
A3 = MATCH(B$1,原始資料!$A$1A$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. 當於儲存格使用帶有索引功能的公式時,請務必記得原始資料不要有刪除整列(或某些情形下的整欄)的情形,不然會導致公式出現錯誤.

程式如附件,請參考.

AAA_a.zip (17.43 KB)

TOP

freeffly

帖子
586 
主題
123 
精華
0 
積分
763 
點名
0  
作業系統
WINDOW7 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-16 
最後登錄
2017-3-14 

熱烈參與勳章好學求知勳章
4#
發表於 2010-7-21 23:32 | 只看該作者
我覺得Hsieh大大的方式比較好
不用加輔助欄
又學到一個新方法
字典兩各字 還真難理解

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
5#
發表於 2010-7-21 23:57 | 只看該作者
我也一直在向Hsieh大學習中,
或許是自己 Excel 程度還是太淺,
常常有些公式看的不是很懂,
就像上面那些 "陣列公式" 文字好像就不能直接貼到 A3 - C3 儲存格,
在我這會被辨識成 "文字" 而非公式.

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

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

TOP

maomin

帖子
7 
主題
4 
精華
0 
積分
53 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2010-7-20 
最後登錄
2020-3-9 
6#
發表於 2010-7-22 01:40 | 只看該作者
感謝各位大大的指教

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

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
255  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-25 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
7#
發表於 2010-7-22 07:14 | 只看該作者
回復 5# luhpro

複製公式貼上後,將{}刪除
CTRL+SHIFT+ENTER輸入
學海無涯_不恥下問

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
8#
發表於 2010-7-22 21:43 | 只看該作者
本帖最後由 luhpro 於 2010-7-22 22:56 編輯

回復 7# Hsieh


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

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

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

TOP

gong

帖子
455 
主題
8 
精華
1 
積分
480 
點名
0  
作業系統
WINS home 
軟體版本
OFFICE XP 
閱讀權限
100 
性別
男 
來自
平鎮 
註冊時間
2010-5-1 
最後登錄
2015-1-22 

論壇貢獻勳章
9#
發表於 2010-7-22 22:41 | 只看該作者
{}是陣列公式
陣列可以是一組數,在一個儲存格內存在
將該公式全選按f9看看

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

差別為一組數與一個數
知之為知之，不知為不知，誠實也！

TOP

Andy2483
　暱稱: 厚臉皮的學生

大學生Rank: 4

帖子
1446 
主題
40 
精華
0 
積分
1470 
點名
0  
作業系統
Windows 7 
軟體版本
Excel 2010 & 2016 
閱讀權限
50 
性別
男 
來自
台灣 
註冊時間
2020-7-15 
最後登錄
2024-10-21 
10#
發表於 2023-3-14 15:05 | 只看該作者
回復 1# maomin


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

資料表:


結果表執行前:


執行結果:


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
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

        靜思自在 : 心中常存善解、包容、感思、知足、惜福。
返回列表 上一主題