iT邦幫忙

0

如何將横列資料轉為直列並編號

  • 分享至 

  • xImage

https://ithelp.ithome.com.tw/upload/images/20180307/20095648gKSqhG00na.png

請問如何將上方横列從左到右的訂單+編號,以公式變成右邊直列從上到下的方式呈現

  1. 如橫列訂單的編號有2筆以上,則直列訂單自動加-1、-2……
    例如:儲存格A5訂單IT97802521,共有B5、C5兩個編號,故轉到N欄訂單排列時,會自動生成IT97802521、IT97802521-1兩張訂單,O欄則帶出該訂單兩個編號,以此類推…

  2. 單一訂單的編號如果有重複,則不重複抓取,僅抓取唯一值即可。
    例如:儲存格A7訂單IT96801568,雖然有三個編號,但C7號F7編號重複,故只需抓取其中1筆編號即可。故轉到N欄訂單排列時,訂單IT96801568只會有IT96801568、IT96801568-1,不會有IT96801568-2。
    儲存格A15訂單IT2D800727以此類推

  3. 空白儲存格及Y無需抓取。

特別提醒,上方橫列資料是由系統抓取,發現好像空格並非空白格,應該是有無法列印字元或隱藏字元我猜,因為選取B2:J19時使用尋找>特殊目標>空格,發現並非全為空格。
但我不知道醬會不會影響………

測試檔https://drive.google.com/open?id=1LO2TY2LldC2SwbSjhrzdFGt2H_UbcnRQ

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
ccutmis
iT邦高手 2 級 ‧ 2018-03-08 13:03:49
最佳解答

樓主試試把您的excel檔另存為"Book1.csv"(逗點分隔),
新建一個文字檔,檔名設為"gentNewOrder.vbs"
然後將下列源碼內容用記事本貼到gentNewOrder.vbs中並存檔,
把Book1.csv跟gentNewOrder.vbs放在同個目錄下,
雙擊gentNewOrder.vbs就會產出output.csv
(output.csv可以用excel軟體開啟)

Dim sysPath, sFile, oFile, objFSO, objFileS, objFileO, ii

sysPath=PathRemoveFileSpec1(WScript.ScriptFullName)
sFile = sysPath & "\Book1.csv"
oFile = sysPath & "\output.csv"
WSH.echo sysPath

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileS = objFSO.OpenTextFile( sFile, 1)
Set objFileO = objFSO.OpenTextFile( oFile, 2,true)

objFileO.writeLine "訂單,編號"

ii=0
Do Until objFileS.AtEndOfStream
	tmpLine=objFileS.ReadLine
	IF ii>0 THEN '略過標題列
		myArray = Split(tmpLine,chr(44))
		tmpORD=myArray(0)
		ordCount=0
		tmpSnFound=""
		FOR jj = 1 TO 9
			tmpSN = myArray(jj)
			IF tmpSN<>"" AND tmpSN<>"Y" AND LEN(tmpSN)>2 AND InStr(tmpSnFound,tmpSN)=0 THEN
				IF ordCount>0 THEN
					objFileO.writeLine tmpORD & "-" & ordCount & "," & tmpSN 
				ELSE
					objFileO.writeLine tmpORD & "," & tmpSN
				END IF
				
				tmpSnFound= tmpSnFound & tmpSN &","
				ordCount = ordCount + 1
				
			END IF
		NEXT
		
		
	END IF
	ii = ii + 1
Loop

objFileS.close
objFileO.close

'下列是取得當前路徑的函式不要修改它
Function PathRemoveFileSpec1(strFileName)
  ' 將Unix路徑 / 替換為 \
  strFileName = Replace(strFileName, "/", "\")
  Dim iPos
  ' 從路徑開始搜尋\,這樣從第一個字元到這個位置
  ' 就是我們所需要的
  iPos = InStrRev(strFileName, "\")
  ' 使用Left函式保留需要的部份
  PathRemoveFileSpec1 = Left(strFileName, iPos)
End Function
WilliamHuang
iT邦研究生 1 級 ‧ 2018-03-08 09:48:34
【**此則訊息已被站方移除**】
0
haoming
iT邦好手 1 級 ‧ 2018-03-08 10:20:20

我想的手動的步驟是

  1. 先轉置資料 把行列對調
  2. 用資料篩選移除重複, 移除空白, 移除Y (這幾個動作要一行一行做)
  3. 利用 counta(list) - countblank(list)-1 算出編號是第幾個順序
  4. 把訂單編號跟 步驟三算出的結果 用 concancate 合併成 h欄的資訊
  5. 再轉置一次,行列互換成原本排列方式後,利用公式把它 輸出到另外一個 試算表
  6. 合併成一列,再次移除空白行 就可完成.
0
海綿寶寶
iT邦大神 1 級 ‧ 2018-03-08 12:24:33

你要做的事情
光用 Excel 基本操作應該做不到
寫寫 VBA 或許還有機會

最好的辦法
是去改
產生這個 Excel 的那隻程式
/images/emoticon/emoticon06.gif

我要發表回答

立即登入回答