missbb

帖子: 215
主題: 70
精華: 0
積分: 290
點名: 0
作業系統: window xp
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2012-6-27
最後登錄: 2024-1-29

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-3-11 07:38 | 只看該作者

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

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

course.rar (6.49 KB)

有勞.

樓主熱帖

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

2^#

發表於 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),)),"")
向右向下複製

course.zip (8.81 KB)

學海無涯_不恥下問

TOP

missbb

中學生 Rank: 2

帖子: 215
主題: 70
精華: 0
積分: 290
點名: 0
作業系統: window xp
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2012-6-27
最後登錄: 2024-1-29

3^#

發表於 2014-3-11 12:52 | 只看該作者

回復 2# Hsieh

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

TOP

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

4^#

發表於 2014-3-11 14:43 | 只看該作者

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

公式太難了, 只好用VBA 試試看:

Option Explicit
Private Sub CommandButton1_Click()
Dim start1, end1, 空白列 As Long, Rng, findcell As Range
Dim cour As Integer
start1 = 1
'[A65536].End(xlUp)→由下往上找, 直到找到非空白格為止
end1 = [A65536].End(xlUp).Row
Do
Set Rng = Cells(start1, 1).Resize(end1 - start1 + 1, 1)
Set findcell = Rng.Find(What:="name", _
After:=Cells(start1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart).Offset(1, 0)
If Not findcell Is Nothing Then
空白列 = [i65536].End(xlUp).Row + 1
Cells(空白列, 9) = findcell.Offset(-1, 1)
Do
'假定最多只有9科才成立
cour = Val(Mid(findcell, 7, 1))
Cells(空白列, 9).Offset(0, cour * 2 - 1) = findcell.Offset(0, 1)
Cells(空白列, 9).Offset(0, cour * 2) = findcell.Offset(0, 4)
Set findcell = findcell.Offset(1, 0)
Loop Until findcell = ""
End If
start1 = findcell.Row
Loop Until findcell Is Nothing Or start1 > end1
End Sub

複製代碼

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

5^#

發表於 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尋找這範圍內最後一個數值就得到該位置

學海無涯_不恥下問

TOP

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

6^#

發表於 2014-3-11 18:09 | 只看該作者

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

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 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式合併為一式較為精簡

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

missbb

中學生 Rank: 2

帖子: 215
主題: 70
精華: 0
積分: 290
點名: 0
作業系統: window xp
軟體版本: 2007
閱讀權限: 20
性別: 女
註冊時間: 2012-6-27
最後登錄: 2024-1-29

8^#

發表於 2014-3-11 21:35 | 只看該作者

course3.rar (20.06 KB) 回復 5# Hsieh

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

有勞.

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

9^#

發表於 2014-3-11 22:53 | 只看該作者

回復 6# yen956

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

學海無涯_不恥下問

TOP

yen956

高中生 Rank: 3 Rank: 3

帖子: 522
主題: 36
精華: 1
積分: 603
點名: 0
作業系統: win xp sp3
軟體版本: Office 2003
閱讀權限: 50
性別: 男
註冊時間: 2012-12-13
最後登錄: 2021-7-11

10^#

發表於 2014-3-12 04:44 | 只看該作者

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

TOP