Re: [算表] excel 資料分析問題

[JieJuen (David)]

A       B       C       D     E    F       G               H       I
基準日  機構    內容    性質       科目    機構            004     005
9702    004     1121    A          1121    原始成本        50      50
9702    004     01      B          1121    虧損或盈餘      100     100
9702    004     50      C          1121    合計            150     150

首先將科目F欄填滿  (詳見 #17U8En1a 4344)
H2
=INDEX(內容,MATCH(VLOOKUP($G2,$F$13:$G$17,2,),OFFSET(內容,,1),))
H2 內容 之定義
=OFFSET($C$1:$C$9,MATCH(H$1,$B:$B,)-2+MATCH($F2,OFFSET($C$1:$C$999,
MATCH(H$1,$B:$B,)-1,),),)

$F$13:$G$17為
科目            A
會計代碼        B
原始成本        C
虧損或盈餘      D
合計            E

至於漏項
若是漏科目 自然是該科目的內容都找不到了~
漏其他小項目 就只有小項目找不到

如果漏科目要處理，就會複雜些(什麼...現在已經夠複雜了吧 XD)
請先舉例(比如是整列沒有還是有空白列)

http://2y.drivehq.com/p/MatchMany.xls

補充：漏科目(整列沒有)時 新增欄位補齊
既然已經需要新欄位 就再加上每列的關鍵字
讓查詢公式簡單一點

E       F                       G       H               I       J
                                科目    機構            004     005
1121    004|1121|科目           1121    原始成本        50      50
1121    004|1121|會計代碼       1121    虧損或盈餘      100     100
1121    004|1121|原始成本       1121    合計            150     150
1121    004|1121|虧損或盈餘     1122    原始成本        50      50
1121    004|1121|合計           1122    虧損或盈餘      50      100

E2
=IF(D2="A",C2,IF(CODE(D1)>CODE(D2),IF(B1<B2,1121,E1+1),E1))
F2
=B2&"|"&E2&"|"&VLOOKUP(D2,$G$13:$H$17,2,)
I2
=INDEX($C:$C,MATCH(J$1&"|"&$G2&"|"&$H2,$F:$F,))

詳見檔案

※ 引述《ljuber (給你吃膨餅)》之銘言：
: 軟體:excel
: 版本:任何版本
: 我有一堆資料
: 下載文字檔 其格式如下：
: 基準日   機構代號       內容      科目性質
: 9702          004       1121          A
: 9702          004       01            B
: 9702          004       50            C
: 9702          004       100           D
: 9702          004       150           E
: 9702          004       1122          A
: 9702          004       04            B
: 9702          004       50            C
: 9702          004       50            D
: 9702          004       100           E
: 9702          004       1123          A
: 9702          004       02            B
: 9702          004       50            C
: 9702          004       100           D
: 9702          004       150           E
: 9702          005       1121          A
: 9702          005       01            B
: 9702          005       50            C
: 9702          005       100           D
: 9702          005       150           E
: 9702          005       1122          A
: 9702          005       03            B
: 9702          005       50            C
: 9702          005       100           D
: 9702          005       150           E
: .
: .
: .
: 上面A 代表 科目
:        B  代表 會計代碼
:        C 代表  原始成本
:        D  代表 虧損或盈餘
:        E  代表  C＋D (合計)
: 上面這些資料
: 不知道用excel要如何寫函數 把他取回來變成下面這樣：
: (原始資料科目大約有20幾個 都是固定的科目 機構大約有35家左右)
: 另外下載資料會有的不全 可能 A項會漏報 只有下載到B,C,D,E  漏報不一定是哪項
: 有時候是E項....
: 科目                         機構    004      005     006     007     008
: 1121   原始成本                      50        50
:           虧損或盈餘                 100      100
:           合計                       150       150
: 1122   原始成本                      50         50
:           虧損或盈餘                 50        100
:           合計                       100       150
: 1123   原始成本                      50
:           虧損或盈餘                 100
:           合計                       150

--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.50.109
※ 編輯: JieJuen         來自: 218.164.50.109       (10/04 22:46)
推 ljuber:囧 的確很複雜 不過很感謝 :)
→ ljuber:我會好好研究看看 說實話這些資料是主管機關下載的單一申報
→ ljuber:漏報的問題看來只能請主管機關去請他們好好申報了XD
→ JieJuen:^^
→ JieJuen:如果新增欄不會困擾的話 是比較簡單的方法~
※ 編輯: JieJuen         來自: 218.164.49.72        (05/11 19:01)