Board logo

標題: [發問] 資料整理 [打印本頁]
作者: dragonbx    時間: 2011-5-5 10:51     標題: 資料整理

料號             X
9912113        -599
9912113        -599
9912113        -689
9912113        -737
9912113        -642
9912113        -658
9932294        -468
9932294        -698
9932294        -668
9932294        -596
9932294        -563
9973079        -479
9973079        -469
9973079        -559
9973079        -475
9973079        -635

我想把以上的資料改寫程

料號  9912113    9932294  ....
X1   -599                -468
X2   -599                -698
X3   -689                -668
X4   -737                -596
X5   -642                -563
X6   -658
X7
X8


以此類推我該怎麼寫函數呢?
作者: oobird    時間: 2011-5-5 11:22

加一欄，然後樞紐
[attach]5982[/attach]
作者: dragonbx    時間: 2011-5-5 11:44

我的一個料號所對應的x值數量都不一定
使用於小量的話是可以，可是我的資料很多
約2萬筆，是否有更簡便的方式呢?
作者: ANGELA    時間: 2011-5-5 12:16

資料量大更要用樞紐,公式多會拖慢速度.尤其是陣列公式.
作者: dragonbx    時間: 2011-5-5 13:11

如果像oobird 大所提供的作法的話
假如我有1000個料號那我是不是需要
自行先標示x1~xn後才可用樞紐分析呢?
作者: oobird    時間: 2011-5-5 13:22

請仔細看A欄的公式
我刻意把滑鼠放在A2就是要讓你看得見公式
A2輸入公式，快按兩下
不是你想像中那麼困難的
作者: ANGELA    時間: 2011-5-5 13:29

本帖最後由 ANGELA 於 2011-5-5 13:34 編輯

x1~XN是用一般公式去完成的
樞紐表的欄與列互調即可.
如果一定要用陣列公式
=INDEX($B:$B,SMALL(IF($A$2:$A$20000=E$1,ROW($2:$20000),60000),ROW(A1)))&""
會打哈欠的.
作者: dragonbx    時間: 2011-5-5 13:35

請問大大x1~xn我該如何寫公式呢?
作者: ANGELA    時間: 2011-5-5 13:39

oobird 超版在2F的圖片中已有指出.看編輯列.
作者: dragonbx    時間: 2011-5-5 13:41

感謝大大們的指導~~
作者: pewcwy    時間: 2011-5-5 14:25

利用"選擇貼上"功能即可x,y變換。
作者: dragonbx    時間: 2011-5-5 16:07

ANGELA  大想請教為何我使用您所提供之陣列公式帶出來的是空白呢?
麻煩您在教學一下，謝謝!!
作者: ANGELA    時間: 2011-5-5 16:24

陣列公式要用CTRL+SHIFT+ENTER 一起輸入
會自動產生大括號把公式包起來.
因為你沒附加EXCEL檔,所以我的公式是依照7f圖片位置來的.請參考後修改.
作者: dragonbx    時間: 2011-5-5 18:50

大大您好~~我將檔案上傳嚕~~在麻煩您了~~謝謝
作者: Hsieh    時間: 2011-5-5 20:32

回復 5# dragonbx

1000個料號，工作表只有256欄
你能轉置過來嗎?
ANGELA版主的公式是料號的X值清單的公式
你必須在E1開始輸入你的料號
E1陣列公式
=IF(COLUMN(A$1)>SUM(1/COUNTIF($B$2:$B$45,$B$2:$B$45)),"",INDEX($B:$B,SMALL(IF($B$2:$B$45<>$B$3:$B$46,ROW($B$2:$B$45),""),COLUMN(A$1))))
E2輸入該公式，向下複製

並不建議使用陣列公式解決
還是用樞紐解決來的恰當
作者: dragonbx    時間: 2011-5-5 20:53

回復 16# Hsieh

因為我將成式作了修改，改成所以料號頂多不超過100筆的方式
為何不用樞紐，是因為樞紐有些功能會造成我後面的巨集無法執行
所以必須使用條列式呈現，我試過大大所述可是第二行開始就會出現#NUM!
是我哪裡輸入錯誤了嗎?
作者: oobird    時間: 2011-5-5 21:10

既然用到巨集為什麼不乾脆都用巨集呢？
這樣加上許多公式會讓你的巨集跑不動
作者: dragonbx    時間: 2011-5-5 21:15

小弟功力不足~~僅能夠使用東湊西湊法完成我的需求~~@@
作者: dragonbx    時間: 2011-5-5 21:23

我找到問題點了，原來是我忘記用陣列了~感謝大大們的熱心指導唷
作者: dragonbx    時間: 2011-5-6 12:43

回復 16# Hsieh


    大大您所提供的公式我今天測試了，您原先預設的比數為45如果我現在比數有到達150筆時
會顯示#DIV/0 我有修改 B欄位的值之後就可以正常使用了，請問可以不用變更B欄位就可以直接顯示呢?

=IF(COLUMN(A$1)>SUM(1/COUNTIF($B$2:$B$45,$B$2:$B$45)),"",INDEX($B:$B,SMALL(IF($B$2:$B$45<>$B$3:$B$46,ROW($B$2:$B$45),""),COLUMN(A$1))))
作者: chin15    時間: 2011-5-6 12:54

看了半天，若由我選擇，我還是會用樞紐
樞紐完成後，複製樞紐分析表，選擇性貼上值到另一個區塊
後續的作業就可以在這個區塊任意存取不受影響了。
作者: dragonbx    時間: 2011-5-6 13:03

因為這個公式已經寫差不多了~所以想說以這種方式完成
麻煩了~~
作者: dragonbx    時間: 2011-5-6 13:06

因為有寫巨集~用樞紐的話有辦法自動跑嗎??
作者: chin15    時間: 2011-5-6 15:19

不想用樞紐，用巨集也可以啊
資料大時用公式必定吃苦頭的
  1. [attach]6016[/attach]
複製代碼
作者: dragonbx    時間: 2011-5-6 17:25

請問該如何寫巨集呢?

我點選複製代碼貼上後得到以下答案

下載 (37.34 KB)
2 小時前
作者: chin15    時間: 2011-5-6 20:26

請問該如何寫巨集呢?

我點選複製代碼貼上後得到以下答案

下載 (37.34 KB)
2 小時前
dragonbx 發表於 2011-5-6 17:25
  1. Private Sub CommandButton1_Click()
  2.     Dim d As Object, d2 As Object, a, arr, i&, m&, r&
  3.     Set d = CreateObject("scripting.dictionary")
  4.     Set d2 = CreateObject("scripting.dictionary")
  5.     a = Range([a2], [b65536].End(3))
  6.     ReDim arr(1 To UBound(a), 1 To 250)
  7.     For i = 1 To UBound(a)
  8.         If d(a(i, 1)) = "" Then
  9.             m = m + 1
  10.             d(a(i, 1)) = m
  11.             d2(a(i, 1)) = 2
  12.             arr(1, d(a(i, 1))) = a(i, 1): arr(2, d(a(i, 1))) = a(i, 2)
  13.         Else
  14.             d2(a(i, 1)) = d2(a(i, 1)) + 1
  15.             arr(d2(a(i, 1)), d(a(i, 1))) = a(i, 2)
  16.             r = IIf(r > d2(a(i, 1)), r, d2(a(i, 1)))
  17.         End If
  18.     Next
  19.     [e:iv] = ""
  20.     [f1].Resize(r, m) = arr
  21.     ReDim arr(1 To r, 0)
  22.     For i = 1 To r - 1
  23.         arr(i, 0) = [b1] & i
  24.     Next
  25.     [e2].Resize(r - 1, 1) = arr
  26. End Sub
複製代碼
作者: dragonbx    時間: 2011-5-7 09:51

可以了~~感謝大大的教學
作者: 97forum    時間: 2011-5-9 15:23

真的是受教許多，由於很多Excel上面的功能或函數很少去使用它，除非在工作上經常會需求多種不同的功能時才會想辦法去解決，真的是所謂的"書到用時方恨少"。
作者: Andy2483    時間: 2023-3-15 14:28

回復 1# dragonbx


    謝謝論壇,謝謝各位前輩
後學藉此主題練習陣列與字典,請各位前輩再指導

[attach]35950[/attach]

執行前:
[attach]35951[/attach]

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

Option Explicit
Sub Test()
Dim Brr, Crr, Y, i&, R&, M&, T2%, C%, T1$
'↑宣告變數:(Brr,Crr,Y)是通用型變數,(i,R,M)是長整數變數,
'(T2,C)是短整數變數,T1是字串變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y這通用型變數是 字典
Set Brr = Sheets(1).UsedRange: Brr.Offset(0, 4).ClearContents
'↑令Brr這通用型變數是索引號1工作表有使用的儲存格,
'令Brr變數向右偏移4欄的儲存格內容清除
Brr = Brr: For i = 2 To UBound(Brr): Y(Val(Brr(i, 1))) = "": Next
'↑令Brr是二維陣列,以Brr變數值帶入
'設順迴圈!i從2到 Brr陣列縱向最大索引列號
'令i迴圈列第1欄Brr陣列值經轉化為數值當key,item是空字元,納入Y字典
ReDim Crr(1 To UBound(Brr), 1 To Y.Count + 1): Y.RemoveAll
'↑宣告Crr是二維陣列,縱向範圍(1到Brr陣列縱向最大索引列號)列,
'橫向範圍(1到Y字典key數量+1)欄
'清空Y字典
For i = 2 To UBound(Brr)
'↑設順迴圈!i從2到 Brr陣列縱向最大索引號
   T1 = Val(Brr(i, 1)): T2 = Brr(i, 2)
   '↑令T1這字串變數是 i迴圈列第1欄Brr陣列值經轉化為數值的新字串
   If Not Y.Exists(T1 & "/C") Then
   '↑如果T1變數連接"/C"成的字串在Y字典裡沒有此key?
      C = C + 1: Y(T1 & "/C") = C: Crr(1, C + 1) = T1
      '↑令C這短整數變數累加1
      '令T1變數連接"/C"成的字串當key,item是 C變數,納入Y字典裡
      '令1列第(C變數+1)欄Crr陣列值是 T1變數
   End If
   R = Y(T1 & "/R"): R = R + 1: Y(T1 & "/R") = R
   '↑令R這長整數變數是 (T1變數連接"/R"成的字串查Y字典回傳item值):
   '令R變數累加1:令T1變數連接"/R"成的字串當key,item是R變數,放回Y字典
   If R > M Then M = R: Crr(M + 1, 1) = Brr(1, 2) & M
   '↑如果R變數大於 這M長整數變數!就令M變數是R變數值:
   '令(M變數+1)列第1欄Crr陣列值是 1列第2欄Brr陣列值連接M變數的新字串
   Crr(Y(T1 & "/R") + 1, Y(T1 & "/C") + 1) = T2
   '↑令(Y(T1 & "/R") + 1)列第(Y(T1 & "/C") + 1)欄Crr陣列值是 T2變數
   '(Y(T1 & "/R") + 1)列: T1變數連接"/R"成的字串查Y字典的item值+1 列
   '(Y(T1 & "/C") + 1)列: T1變數連接"/C"成的字串查Y字典的item值+1 欄
Next
Crr(1, 1) = Brr(1, 1)
'↑令1列第1欄Crr陣列值是 1列第1欄Brr陣列值
[E1].Resize(M + 1, C + 1) = Crr
'↑令[E1]擴展向下(M變數+1)列,向右擴展(C變數+1)欄,
'這擴展範圍儲存格值以Crr陣列值帶入
Set Y = Nothing: Erase Brr, Crr
'↑令釋放變數
End Sub



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