Board logo

標題: [發問] 橫式資料轉換為直式資料 [打印本頁]
作者: aer    時間: 2016-7-24 07:43     標題: 橫式資料轉換為直式資料

本帖最後由 aer 於 2016-7-24 07:47 編輯

[請問公式如何寫，才能將sheet1(上圖示) 的橫式資料，轉換為sheet2(下圖示) 的直式資料？謝謝！
[attach]24733[/attach]
[[attach]24734[/attach]
作者: 准提部林    時間: 2016-7-24 12:04

A2．陣列公式：〔Shift + Ctrl → Enter〕三鍵
=TRIM(MID(SUBSTITUTE(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$B$2:$M$64="",4^8,ROW($2:$64))*100+COLUMN($B:$M)^(COLUMN(A$1)>1),ROW(A1)),"!r0c00"),)&"","梯","梯"&REPT(" ",99)),99^(COLUMN(A$1)>2),99))
作者: hcm19522    時間: 2016-7-24 12:59

http://blog.xuite.net/hcm19522/twblog/435047307
作者: aer    時間: 2016-7-24 20:31

回復 2# 准提部林
謝謝版主！公式測試沒問題。
不過，是否可解釋一下公式意思？感謝您！
作者: aer    時間: 2016-7-24 20:32

回復 3# hcm19522
謝謝您的回覆！
作者: 准提部林    時間: 2016-7-24 21:16

回復 4# aer


這公式不好解釋, 將公式分段, 請試著自行去理解:
[attach]24739[/attach]
作者: hcm19522    時間: 2016-7-25 11:20

回復 6# 准提部林
"准大" 巧妙安排程式 可懂不易寫
想請教,"!r0c00"),)&"" ,此段加 &"" 何意
作者: 准提部林    時間: 2016-7-25 12:40

回復 7# hcm19522


避空格出現0, 將其去掉可見分明!

這種公式慢, 資料多, 最好用VBA
作者: aer    時間: 2016-7-25 18:17

回復  hcm19522


避空格出現0, 將其去掉可見分明!

這種公式慢, 資料多, 最好用VBA
准提部林 發表於 2016-7-25 12:40


也請您賜教VBA 程式，因資料將近有1000筆，無限感激！
作者: 准提部林    時間: 2016-7-25 19:15

回復 9# aer


程式碼很簡單, 多看幾次應可以理解:
  1. Sub TEST()
  2. Dim Arr, Brr, i&, j%, T, N&
  3. With Sheets("Sheet1").UsedRange
  4.      Arr = .Value
  5.      ReDim Brr(1 To .Count, 1 To 3)
  6. End With

  8. For i = 2 To UBound(Arr)
  9. For j = 2 To UBound(Arr, 2)
  10.     If Arr(i, j) <> "" Then
  11.        N = N + 1: T = Split(Trim(Arr(i, j)), "梯")
  12.        Brr(N, 1) = Arr(i, 1)
  13.        Brr(N, 2) = T(0) & "梯"
  14.        Brr(N, 3) = T(1)
  15.     End If
  16. Next j
  17. Next i

  19. With Sheets("Sheet2")
  20.      .UsedRange.Clear
  21.      If N = 0 Then Exit Sub
  22.      .[A1:C1] = Array("姓名", "梯次", "日期")
  23.      .[A2:C2].Resize(N) = Brr
  24.      Application.Goto .[A1]
  25. End With
  26. End Sub
複製代碼
作者: KCC    時間: 2016-7-25 19:37

回復 9# aer


    曾幫朋友整理過類似的資料，提供幾個建議參考
   1.應考慮資料唯一性，姓名是有可能重複的，應加入身份證號或報名代號等等一併處理，不然後續常有問題
   2.注意排序需求，例如用日期或第xx梯的xx排序(用xx排序，可能得自建清單)
   3.准版已給了完整的公式和vba，後續看單位長官或需求單位常需要提供各種不同型式整理後的資料，可運用樞紐分析作初步整理再進行細項調整。
作者: aer    時間: 2016-7-25 20:47

回復 10# 准提部林
謝謝准提部林版主，如有問題再請教您，再次感謝您！
作者: aer    時間: 2016-7-25 20:49

回復 11# KCC
KCC 謝謝您的指教，此資料轉換後即是用樞紐分析表做統計分析 。
作者: handmuch    時間: 2016-7-25 22:29

本帖最後由 handmuch 於 2016-7-25 22:32 編輯

類似的問題很多人都問過 只是題目長得不太一樣     這類都是二維轉一維

可以用"樞紐分析表和樞紐分析圖精靈"來完成!!

二維轉一維教學影片
作者: Andy2483    時間: 2022-12-2 09:50

本帖最後由 Andy2483 於 2022-12-2 10:04 編輯

回復 1# aer
回復 10# 准提部林


    謝謝前輩指導
謝謝 aer前輩發表此主題與範例
以下心得註解請再指導!謝謝

Option Explicit
Sub TEST()
Dim Arr, Brr, T, i&, N&, j%
'↑宣告變數(Arr,Brr,T)是通用型變數,(i,N)是長整數,(j)是短整數,
請教 准提部林前輩:
1.這裡的j變數為什麼要宣告為短整數?
2.什麼時候該用長整數? 短整數?
With Sheets("Sheet1").UsedRange
'↑以下是關於 名為: "Sheet1" 工作表的使用儲存格的程序
'使用儲存格是指有被編輯過的儲存格
'工作表的使用儲存格是指:能涵蓋所有 使用儲存格 的最小方正區域
'例如:
'1.開個全新的工作表>[D3]輸入 "左上角",[F10]輸入 "右下角" ,.Address=$D$3:&F$10]
'2.再在[K2]輸入 "右上角" .Address=$D$2:$K$10
'3.再在[G12]輸入 "下邊邊" .Address=$D$2:$K$12
'4.再在[H1]輸入 "上邊邊" .Address=$D$1:$K$12]
'5.再在[S20]輸入 "新右下角" .Address=$D$1:$S$20]
'謝謝 准提部林前輩常用不同的陳述方式讓後輩學習!謝謝
     Arr = .Value
     '↑令Arr是二維陣列!陣列值就是 工作表的使用儲存格的值
     ReDim Brr(1 To .Count, 1 To 3)
     '↑宣告二維Brr陣列的範圍! 縱向從1 到工作表的使用儲存格格數 列,
     '橫向從1 到3欄
End With
For i = 2 To UBound(Arr)
'↑設外順迴圈i變數從2 到Arr陣列的縱向最大列號數
   For j = 2 To UBound(Arr, 2)
   '↑設內順迴圈j變數從2 到Arr陣列的橫向向最大欄號數
      If Arr(i, j) <> "" Then
      '↑如果迴圈Arr陣列值 不是空字元??
         N = N + 1
         '↑N累加 1
         'N變數在前面都沒出現過!
         '只有宣告是 長整數,所以N的初始值是0 ,每次if條件成立就會加 1
         T = Split(Trim(Arr(i, j)), "梯")
         '↑令T變數是 迴圈Arr陣列值經過去頭尾空白字元後,
         '以"梯"字元分割的一維陣列
         'T變數在最前面是宣告為 通用型變數!此一動作會讓T變成一維陣列
         'T變數變化舉例:T=5 (T是數字),T="5" (T是字串),T=5 & 5 (T是字串)
         Brr(N, 1) = Arr(i, 1)
         '↑將Arr陣列迴圈列第一欄的值倒入 Brr陣列N列第一欄裡
         Brr(N, 2) = T(0) & "梯"
         '↑將T這一維陣列的第一個值連接"梯"成為字串後,放進Brr陣列N列第二欄裡
         '因為被Split()拆解的陣列都從(0)開始編號!
         Brr(N, 3) = T(1)
         '↑將T這一維陣列的第二個值,放進Brr陣列N列第三欄裡
      End If
   Next j
Next i
With Sheets("Sheet2")
'↑以下是關於 名為: "Sheet2" 工作表的程序
     .UsedRange.Clear
     '↑工作表的使用儲存格清除掉!
     '這個動作會讓.UsedRange.Address=$A$1
     If N = 0 Then Exit Sub
     '↑如果N變數是0,就結束程式執行!
     'N變數如果是0,是因為前面的程序IF條件都不成立!N都沒機會累加
     .[A1:C1] = Array("姓名", "梯次", "日期")
     '↑[A1],[B1],[C1]儲存格用一維陣列個別倒進去,[A1]="姓名",[B1]="梯次",[C1]="日期"
     .[A2:C2].Resize(N) = Brr
     '↑從[A2:C2]向下擴展N變數 列數的區域儲存格,以Brr陣列值倒進去
     Application.Goto .[A1]
     '↑儲存格游標跳到Sheets("Sheet2").[A1]
End With
End Sub
作者: Andy2483    時間: 2022-12-2 10:19

'使用儲存格是指有被編輯過的儲存格
'工作表的使用儲存格是指:能涵蓋所有 使用儲存格 的最小方正區域
'例如:
'1.開個全新的工作表>[D3]輸入 "左上角",[F10]輸入 "右下角" ,.Address=$D$3:&F$10]
'2.再在[K2]輸入 "右上角" .Address=$D$2:$K$10
'3.再在[G12]輸入 "下邊邊" .Address=$D$2:$K$12
'4.再在[H1]輸入 "上邊邊" .Address=$D$1:$K$12
'5.再在[S20]輸入 "新右下角" .Address=$D$1:$S$20

[attach]35553[/attach]



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