[發問] 資料相隔列數不一樣的資料整理

missbb

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

course.rar (6.49 KB)

下載次數: 11

2014-3-11 07:35

有勞.

Hsieh

回復 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),)),"")
向右向下複製
course.zip (8.81 KB)

下載次數: 12

2014-3-11 08:43

missbb

回復 2# Hsieh

多謝解答.請問    LOOKUP(2,1/..... 及LOOKUP(1,1/.....的用法是甚麼?

yen956

公式太難了, 只好用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

回復 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

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

將 "_xlfn." 消去後, 也未見改善, 是本版的問題嗎? 謝謝!!
(我的是Excel 2003)

ML089

回復 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

course3.rar (20.06 KB)

下載次數: 8

2014-3-11 21:33

回復 5# Hsieh

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

有勞.

Hsieh

回復 6# yen956

你是2003版本吧？
因為iferror是2007以後才有的新函數

yen956

回復 9# Hsieh
版大你好, 是的我的是2003, 謝謝回覆!!