麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 請教如何把橫式表格符合條件資料改成直式另列在一個工作表內 [打印本頁]

作者: myshowin 時間: 2012-2-5 10:32 標題: 請教如何把橫式表格符合條件資料改成直式另列在一個工作表內

本帖最後由 myshowin 於 2012-2-5 10:37 編輯

我試了好久，都試不出來，拜託高手幫忙提點，感謝

※代碼為手動輸入
職工編號 2/1 2/2 2/3 2/4 2/5 2/6 2/7 2/8 2/9 2/10
610007 8 8 1 1 1 1 1
610012 1 1 1 8 8 1 1 1
610015 1 8 1 1 8 8 8
610017 1 1 1 1 8 8 1 1
610022 1 1 1 7 7 1 1 1 1

※符合代碼為8或7的資料，依下面的格式顯示
職工編號日期代碼
610007 2月1日 8
610007 2月2日 8
610012 2月6日 8
610012 2月7日 8
610015 2月3日 8
610015 2月8日 8
610015 2月9日 8
610015 2月10日 8
610017 2月7日 8
610017 2月8日 8
610022 2月4日 7
610022 2月5日 7

作者: Hsieh 時間: 2012-2-5 21:20

回復 1# myshowin

M2陣列公式=IF(ROW(A1)>COUNTIF($B$2:$K$6,">6"),"",INDIRECT("A"&MOD(SMALL(IF($B$2:$K$6>6,--(COLUMN($B$2:$K$6)*100&ROW($B$2:$K$6)),""),ROW(A1)),100)))

N2陣列公式=IF(M2="","",INDIRECT("R1C"&SMALL(IF(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)>6,COLUMN(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),0)),""),COUNTIF($M$1:M2,M2)),0))

O2陣列公式=IF(N2="","",INDEX($A$1:$K$6,MATCH(M2,$A$1:$A$6,0),MATCH(N2,$A$1:$K$1,0)))

作者: PJChen 時間: 2012-2-6 10:56

回復 2# Hsieh
請問我將資料分成二個sheet,為什麼無法代出資料?
另外N2陣列公式的R1C表示什麼?要如何運用?=IF(M2="","",INDIRECT("R1C"&SMALL(IF(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)>6,COLUMN(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),0)),""),COUNTIF($M$1:M2,M2)),0))
[attach]9463[/attach]

作者: ANGELA 時間: 2012-2-6 14:04

a2=IF(ROW(出勤!A1)>COUNTIF(出勤!$B$2:$K$6,">6"),"",INDIRECT("出勤!A"&MOD(SMALL(IF(出勤!$B$2:$K$6>6,--(COLUMN(出勤!$B$2:$K$6)*100&ROW(出勤!$B$2:$K$6)),""),ROW(出勤!A1)),100)))
b2=INDIRECT("出勤!r1c"&SMALL(IF(INDEX(出勤!$B$1:$K$6,MATCH(A2,出勤!$A$1:$A$6,),)>6,COLUMN(INDEX(出勤!$B$1:$K$6,MATCH(A2,出勤!$A$1:$A$6,),0)),""),COUNTIF(A$2:$A2,A2)),0)
c2=IF(B2="","",INDEX(出勤!$B$2:$K$6,MATCH(A2,出勤!$A$2:$A$6,0),MATCH(B2,出勤!$B$1:$K$1,0)))

作者: PJChen 時間: 2012-2-6 17:02

回復 4# ANGELA
我套上公式後,只有A2儲存格可以正確的代出資料,其餘都出現#N/A,不管我有沒有使用陣列公式都一樣,不知哪裡出錯了!
[attach]9465[/attach]

作者: register313 時間: 2012-2-6 18:53

回復 5# PJChen

出勤工作表
職工編號與代碼儲存格應為數字格式(靠右對齊)
你的職工編號與代碼儲存格為文字格式(靠左對齊)
因職工編號與代碼儲存格內出現不可見字元(清除後即可)

[attach]9467[/attach]

作者: Hsieh 時間: 2012-2-6 19:01

回復 5# PJChen

問題出在出勤工作表中的資料都是文字型態

[attach]9468[/attach]

作者: ANGELA 時間: 2012-2-7 10:08

回復 5# PJChen

請參考
http://forum.twbts.com/thread-5766-1-1.html

作者: PJChen 時間: 2012-2-7 11:56

回復 8# ANGELA

我到這個連結去看了,我照著上面寫的方法作,也完全沒有任何反應,不好意思,我完全不明白!什麼叫ALT+16的空白分隔字符?按CTRL+H 在尋找目標如何ALT+160?

這應是下載的數据,含有ALT+160的空白分隔字符。按CTRL+H 在尋找目標=>輸入ALT+160 ,或直接在數据尾的空白反白按CTR+C，再貼在尋找目標裡。
用替代取代數字後的空白即可計算。

作者: PJChen 時間: 2012-2-7 12:07

回復 7# Hsieh
您說的文字型態,我查看了一下,雖然在儲存格中看到的是"通用格式",但每個儲存格中的數字後面有都一個空格,我用鍵盤上的ctrl+H,搜尋空格也無法找到,有無法法可以一次取代數字後的空格?Angela的方法我試不出來!

作者: ANGELA 時間: 2012-2-7 12:46

回復 10# PJChen

在任一儲存格中(先按住ALT)再輸入160此時會產生一個看不見的空白字元，再把這個字元COPY 到尋找目標，按全部取代。

作者: myshowin 時間: 2012-2-7 20:21

回復 2# Hsieh

感謝您的幫忙

作者: PJChen 時間: 2012-2-7 21:35

回復 11# ANGELA
我按住Alt時出現附檔1的畫面,輸入16時出現2的畫面,再輸入0時出現附件2左邊的小框框的字,再回到Excel的畫面在輸入Alt+160的儲存格,先copy然後按ctrl+h貼上這個看不見的字元,但完全沒有任何改變,我起碼試了10次以上!

[attach]9480[/attach][attach]9481[/attach]

作者: myshowin 時間: 2012-2-7 22:05

回復 13# PJChen

請問您是要取代空白嗎?
如果是的話，應該要先ctrl+H出現取代視窗
再按alt+160 (看不見是正常的)
然後再按全部取代

我遇到會有這種情形,通常會出現這個應該是直接網頁複製資料時比較會出現,不然沒有作用

作者: myshowin 時間: 2012-2-7 22:09

回復 2# Hsieh

您好：
再請教您，
若有大於8的值，例如 9~15，仍是取8和7
又需如何修改?

不好意思一開始沒說清楚
感謝

作者: PJChen 時間: 2012-2-7 22:30

回復 14# myshowin
我照你的方法還是不行,先出現ctrl+h然後在尋找目標的地方Alt+160,結果電腦就一直噹...的發出聲音,顯示行不通!

作者: Hsieh 時間: 2012-2-7 22:30

回復 15# myshowin
你到現在也還沒說清楚
9~15有7個值，誰知道你要那些值以7取代，那些以8取代

作者: register313 時間: 2012-2-7 22:41

回復 16# PJChen

在EXCEL內比較不容易取代不可見字元

把EXCEL的資料複製到WORD
在WORD比較容易取代
取代完再貼回EXCEL

作者: myshowin 時間: 2012-2-7 22:44

回復 17# Hsieh

抱歉，不太會說明
應該說只要７和８，其他的值都不要（值有可能大於8)

作者: PJChen 時間: 2012-2-7 23:00

回復 18# register313
我依你建議在word中照著方法確實可以完成,但本著求知的精神,我真的很想知道在Excel中我的動作到底哪裡有問題,為什麼Angel教我的卻無法完成?而只是出現一些怪怪的畫面?另外我又有一個新發現,我將有空格的文字格式複製到一個新的工作表中貼上,尚未存檔的,一樣在任一儲存格中Alt+160(60還沒按),就出現另存新檔的對話框,我真的弄不明白,我很想完成Angel教的方法,但一直有問題,有誰也可以幫忙試試看要如何操作?

作者: Hsieh 時間: 2012-2-7 23:43

回復 19# myshowin

M2陣列公式
=IF(ROW(A2)>(COUNTIF($B$2:$K$6,7)+COUNTIF($B$2:$K$6,8)),"",INDIRECT("A"&MOD(SMALL(IF(($B$2:$K$6>6)*($B$2:$K$6<9),--(COLUMN($B$2:$K$6)*100&ROW($B$2:$K$6)),""),ROW(A2)),100)))
N2陣列公式
=IF(M2="","",INDIRECT("R1C"&SMALL(IF((INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)>6)*(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)<9),COLUMN(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),0)),""),COUNTIF($M$1:M2,M2)),0))
O2陣列公式
=IF(N2="","",INDEX($A$1:$K$6,MATCH(M2,$A$1:$A$6,0),MATCH(N2,$A$1:$K$1,0)))
[attach]9485[/attach]

作者: Hsieh 時間: 2012-2-7 23:50

回復 20# PJChen

你應該是使用鍵盤上排的數字，使用字元碼輸入必須按住ALT+數字鍵
[attach]9486[/attach]

作者: PJChen 時間: 2012-2-8 11:18

回復 22# Hsieh

謝謝您找到了問題的所在,在數字鍵輸入Alt+160確實可以產生一個空白字元,但無法將它copy貼上並取代為其它字元,無論我是直接copy儲存格,或將空白字元反黑選取都無法copy!

作者: Hsieh 時間: 2012-2-8 14:08

回復 23# PJChen

直接在尋找目標內使用ALT+160

作者: myshowin 時間: 2012-2-8 19:18

回復 21# Hsieh

我太笨，昨天自已試著修改了幾個小時，總是會有少，
原來問題出在下面藍字的地方
非常感謝您的解答，我獲易良多

以下是超級版主Hsieh 教學原文：
M2陣列公式
=IF(ROW(A2)>(COUNTIF($B$2:$K$6,7)+COUNTIF($B$2:$K$6,8)),"",INDIRECT("A"&MOD(SMALL(IF(($B$2:$K$6>6)*($B$2:$K$6<9),--(COLUMN($B$2:$K$6)*100&ROW($B$2:$K$6)),""),ROW(A2)),100)))
N2陣列公式
=IF(M2="","",INDIRECT("R1C"&SMALL(IF((INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)>6)*(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),)<9),COLUMN(INDEX($B$1:$K$6,MATCH(M2,$A$1:$A$6),0)),""),COUNTIF($M$1:M2,M2)),0))
O2陣列公式
=IF(N2="","",INDEX($A$1:$K$6,MATCH(M2,$A$1:$A$6,0),MATCH(N2,$A$1:$K$1,0)))

作者: PJChen 時間: 2012-2-9 15:07

回復 24# Hsieh
14F朋友昨天有提過,我也試了,結果是沒有作用.

作者: Hsieh 時間: 2012-2-9 17:35

回復 26# PJChen
[attach]9513[/attach]

作者: PJChen 時間: 2012-2-10 16:18

回復 27# Hsieh

謝謝版主耐心的教導,成功了!

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