- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 44
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2025-2-20
               
|
6#
發表於 2011-2-18 14:32
| 只看該作者
本帖最後由 Hsieh 於 2011-2-18 14:35 編輯
回復 5# kaivenliu
我沒有權限開放下載權限，請詳閱版規
我更希望你能聽我所勸，將資料表正規化
不然等你看懂我的公式可能會浪費你很多時間
定義名稱:
在第四列被選取狀態下
w=CHAR(SMALL(CODE(Sheet1!$C$2:$C$34),ROW(!$A1)))
x=SUBSTITUTE(Sheet1!$D4," ","",1)
y=CHAR(SMALL(CODE(Sheet1!$A$2:$A$34),ROW(!$A1)))
z=CHAR(SMALL(CODE(MID(Sheet1!$D$2:$D$34,FIND(" ",Sheet1!$D$2:$D$34)+2,1)),ROW(!$A1)))
請將J3插入2個空白鍵，否則公式會出錯
人員改成人 員
***********************************
D4陣列公式
{=IF(ROW(A1)>COUNTA(Sheet1!$A$2:$A$34),"",INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$A$2:$A$34=y,ROW($A$2:$A$34),""),SUMPRODUCT((MID($D$3:D3,1,1)=y)*1)+1)))}
E4陣列公式
{=IF(ROW(B1)>COUNTA(Sheet1!$A$2:$A$34),"",INDIRECT("Sheet1!E"&SMALL(IF(Sheet1!$A$2:$A$34=y,ROW($A$2:$A$34),""),SUMPRODUCT((MID($D$3:E3,1,1)=y)*1)+1)))}
F4=IF(MID(D4,1,1)=MID(D5,1,1),"",SUM($E$4:E4)-SUM($F3:F$4))
J4陣列公式
{=IF(ROW(A1)>COUNTA(Sheet1!$D$2:$D$34)," ",INDIRECT("Sheet1!D"&SMALL(IF(MID(Sheet1!$D$2:$D$34,FIND(" ",Sheet1!$D$2:$D$34)+2,1)=z,ROW($D$2:$D$34),""),SUMPRODUCT((MID($J$3:J3,FIND(" ",Sheet2!$J$3:$J3)+2,1)=z)*1)+1)))}
K4陣列公式
{=IF(ROW(B1)>COUNTA(Sheet1!$D$2:$D$34),"",INDIRECT("Sheet1!E"&SMALL(IF(MID(Sheet1!$D$2:$D$34,FIND(" ",Sheet1!$D$2:$D$34)+2,1)=z,ROW($D$2:$D$34),""),SUMPRODUCT((MID($J$3:K3,FIND(" ",Sheet2!$J$3:$J3)+2,1)=z)*1)+1)))}
L4=IF(MID(J4,FIND(" ",J4)+2,1)=MID(J5,FIND(" ",J5)+2,1),"",SUM($K$4:K4)-SUM($L3:L$4))
P4陣列公式
{=IF(ROW(A1)>COUNTA(Sheet1!$C$2:$C$34)," ",INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$C$2:$C$34=w,ROW($C$2:$C$34),""),SUMPRODUCT((MID(SUBSTITUTE($P$3:$P3&" "," ","",1)&" ",FIND(" ",SUBSTITUTE($P$3:$P3&" "," ","",1)&" ")+2,1)=w)*1)+1)))}
Q4陣列公式
{=IF(ROW(B1)>COUNTA(Sheet1!$C$2:$C$34)," ",INDIRECT("Sheet1!E"&SMALL(IF(Sheet1!$C$2:$C$34=w,ROW($C$2:$C$34),""),SUMPRODUCT((MID(SUBSTITUTE($P$3:$P3&" "," ","",1)&" ",FIND(" ",SUBSTITUTE($P$3:$P3&" "," ","",1)&" ")+2,1)=w)*1)+1)))}
R4=IF(P4=" ","",IF(MID(SUBSTITUTE($P5&" "," ","",1),FIND(" ",SUBSTITUTE($P5&" "," ","",1))+2,1)<>w,SUM($Q$3:Q4)-SUM($R$3:R3),"")) |
|
發表於 2011-2-16 23:18
| 
