Board logo

標題: [發問] 資料相隔列數不一樣的資料整理 [打印本頁]
作者: missbb    時間: 2014-3-11 07:38     標題: 資料相隔列數不一樣的資料整理

各位早
本人有一自系統輸出的EXCEL檔, 每一位員工均有不同列數 的資料, 故此不知怎樣取得資料. 請問用甚麼函可以整理資料如附件.

[attach]17734[/attach]

有勞.
作者: Hsieh    時間: 2014-3-11 08:43

回復 1# missbb
J3陣列公式
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),1,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
K3陣列公式
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),4,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
向右向下複製
[attach]17735[/attach]
作者: missbb    時間: 2014-3-11 12:52

回復 2# Hsieh

多謝解答.請問    LOOKUP(2,1/..... 及LOOKUP(1,1/.....的用法是甚麼?
作者: yen956    時間: 2014-3-11 14:43

本帖最後由 yen956 於 2014-3-11 14:44 編輯

公式太難了, 只好用VBA 試試看:
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim start1, end1, 空白列 As Long, Rng, findcell As Range
  4.     Dim cour As Integer
  5.     start1 = 1
  6.    
  7.     '[A65536].End(xlUp)→由下往上找, 直到找到非空白格為止
  8.     end1 = [A65536].End(xlUp).Row
  9.     Do
  10.         Set Rng = Cells(start1, 1).Resize(end1 - start1 + 1, 1)
  11.               Set findcell = Rng.Find(What:="name", _
  12.               After:=Cells(start1, 1), _
  13.               LookIn:=xlValues, _
  14.               LookAt:=xlPart).Offset(1, 0)
  15.         
  16.         If Not findcell Is Nothing Then
  17.             空白列 = [i65536].End(xlUp).Row + 1
  18.             Cells(空白列, 9) = findcell.Offset(-1, 1)
  19.             Do               
  20.                 '假定最多只有9科才成立
  21.                 cour = Val(Mid(findcell, 7, 1))
  22.                 Cells(空白列, 9).Offset(0, cour * 2 - 1) = findcell.Offset(0, 1)
  23.                 Cells(空白列, 9).Offset(0, cour * 2) = findcell.Offset(0, 4)
  24.                 Set findcell = findcell.Offset(1, 0)
  25.             Loop Until findcell = ""
  26.         End If
  27.         start1 = findcell.Row
  28.     Loop Until findcell Is Nothing Or start1 > end1
  29. End Sub
複製代碼
作者: Hsieh    時間: 2014-3-11 15:59

回復 3# missbb
OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)
計算出每個name的範圍
其中MATCH($I3,$B:$B,0)找到name的列位置
MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0)
從name往下1000列的範圍若等於空白，這樣傳回1000個TRUE與FLASE的結果
用1去除以這1000邏輯值，則產生1000個1與#DIV/0!
用MATCH 找到第1個1，就是第一個空白位置
1除以這範圍等於報表第二列的邏輯值，只會傳回一個對應course成為1/1
用LOOKUP尋找這範圍內最後一個數值就得到該位置
作者: yen956    時間: 2014-3-11 18:09

回復 5# Hsieh
版大你好:
我下載2f的檔案, 打開結果如下:

將 "_xlfn." 消去後, 也未見改善, 是本版的問題嗎? 謝謝!!
(我的是Excel 2003)
作者: ML089    時間: 2014-3-11 20:53

回復 1# missbb

J3 =IFERROR(VLOOKUP(J$2,OFFSET($A$1,MATCH($I3,$B:$B,),,IF($I4="",99,MATCH($I4,$B:$B,)-MATCH($I3,$B:$B,)),5),2,),"")
K3 =IFERROR(VLOOKUP(J$2,OFFSET($A$1,MATCH($I3,$B:$B,),,IF($I4="",99,MATCH($I4,$B:$B,)-MATCH($I3,$B:$B,)),5),5,),"")
同時選取J3:K3，右拉下拉複製公式
   
若懂陣列可將上2式合併為一式較為精簡
作者: missbb    時間: 2014-3-11 21:35

[attach]17746[/attach]回復 5# Hsieh

大大你好, 我將LOOKUP真正放入我要處理的檔案(是相對較亂的), 但怎樣也不能完成. 請指導.

有勞.
作者: Hsieh    時間: 2014-3-11 22:53

回復 6# yen956

你是2003版本吧？
因為iferror是2007以後才有的新函數
作者: yen956    時間: 2014-3-12 04:44

回復 9# Hsieh
版大你好, 是的我的是2003, 謝謝回覆!!
作者: yen956    時間: 2014-3-12 10:05

回復 8# missbb
試試看結果如下(我試過了, 應該沒問題)：
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim blankRow, endRow As Long
  4.     Dim i As Integer
  5.    
  6.     '[A65536].End(xlUp)→由下往上找, 直到找到非空白格為止
  7.     endRow = [A65536].End(xlUp).Row
  8.    
  9.     'FYxxxx 可能不只一個
  10.     [O3] = "=MATCH(R2C15,R1C17:R1C50)+15"
  11.     i = 1
  12.     Do
  13.         i = i + 1
  14.         If Cells(i, 1) = "Employee No." Then
  15.             blankRow = [P65536].End(xlUp).Row + 1
  16.             Cells(blankRow, 16) = Cells(i, 6)
  17.             Do
  18.                 i = i + 1
  19.                 If Left(Cells(i, 1), 2) = "FY" Then
  20.                     [O2] = Cells(i, 1)
  21.                     Cells(blankRow, [O3]) = Cells(i, 10)
  22.                     Cells(blankRow, [O3] + 1) = Cells(i, 12)
  23.                     
  24.                 End If
  25.             Loop Until i >= endRow Or Cells(i + 1, 1) = "Employee No."
  26.             If i >= endRow Then Exit Sub
  27.         End If
  28.     Loop Until i >= endRow
  29. End Sub
複製代碼
作者: missbb    時間: 2014-3-12 12:51

回復 11# yen956
如用函數可以嗎? 有些同事對VBA不熟識, 要稍作更改會困難一點.:'(
作者: yen956    時間: 2014-3-12 14:02

回復 12# missbb
深感抱歉, 我也正在會學公式. 幫不上忙.
作者: missbb    時間: 2014-3-13 12:59

回復 13# yen956


    不緊要, 謝你的幫忙.
作者: Hsieh    時間: 2014-3-13 15:45

回復 8# missbb
試試看附檔，注意名稱x,y
[attach]17754[/attach]
作者: yen956    時間: 2014-3-13 19:44

回復 8# missbb
大大你好:
你的course3.xlsx檔案第43行, 如下:
FY13M2                                          FY13M2                          10.01.2013          C75                  E40
是不是忘了標示為Employee No.120008的顏色,
或是第三筆不用處理?
作者: is681003    時間: 2014-3-13 23:33

本帖最後由 Hsieh 於 2014-3-14 23:40 編輯

其中一些方式

J3陣列公式
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),1,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
K3陣列公式
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)=J$2),OFFSET($A$1,MATCH($I3,$B:$B,0),4,MATCH(1,1/(OFFSET($A$1,MATCH($I3,$B:$B,0),,1000,)=""),0),)),"")
向右向下複製
作者: missbb    時間: 2014-3-14 21:13

回復 17# is681003

多謝賜教.



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