標籤：

在excel中如何快捷將一個4*4帶行列標題的表格轉為對應3*10帶行標題的表格(實際數據多)，如圖？

05-06

這個問題太好了。有這種意識，起碼已經有了正確的數據管理思維。這種操作就是常說的二維錶轉一維表。方法有多種，函數、VBA、數據透視表嚮導、逆透視。前兩者起步較高，逆透視貌似2016版以上才有。以下著重介紹一下數據透視表嚮導操作步驟。

1、順次按下alt、D、P，調出數據透視表嚮導。指定待分析數據的數據源類型中點擊多重合併計算數據區域，報表類型點數據透視表，然後下一步，再點擊下一步。

二維數據如圖：

2、選定區域中選擇你要轉換的數據區域，點擊添加後繼續下一步，完成。會自動生成一個行列值頁欄位的數據透視表。跟上圖幾乎一模一樣。

3、最重要的一步，雙擊兩個總計的交叉點，也就是上圖的992。就會生成一個新的一維數據表，如圖。改改欄位名，刪除不要的列就完成了

逆透視，版本高的話，可以用powerbi，教程自己搜。

看了其他幾個回答，有的更簡單，比如逆透視，我幾乎沒有用過逆透視，所以， [哈哈]。

如果很多個數據結構相同的sheet需要快速轉換，還是寫一段代碼，使用更方便。

------- 以下是原回答 --------

用vba，做循環，像你手寫一樣，「抄」一遍

這個是在做面板數據時，經常會遇到的問題。

各種函數公式方法，也就是嵌套ROW,MOD,COLUMN等各種轉置方法。

我覺得簡便一點的還是透視表。

如果各年份的值，都是數值型，這個方法就更適用了。如果是文本，需要其他方法：

看演示：

我的回答應該終結這個問題了。

=============================

這個問題.....不需要VBA，不需要函數，不需要手工抄寫......

這個問題本質上是從二維錶轉為 一維表，而excel恰好自帶相關功能。

如果你的excel版本是2016及以上（365），請搜索「逆透視」

如果你的excel是2013及 以下，具體操作步驟我懶得寫了，看這個鏈接吧：

EXCEL的數據逆透視功能-百度經驗?

jingyan.baidu.com

F2格的公式 =INDEX($A:$D,IF(F$1="年份",1,INT((ROW()+4)/3)),IF(F$1="代碼",1,4-MOD(-(ROW()-1),3))) 然後，選中F2:H2，及下方一些單元格，按ctrl-R向右填充公式；按alt_EID或按F2加ctrl_回車，向下填充公式。

（另註：不再建議在excel2003中使用默認快鍵ctrl_D和ctrl_S。

在excel2003中使用ctrl_D或ctrl-S時容易誤操作成為ctrl-DS，可能會產生數據被大量重複內容覆蓋並且無法撤銷。因此我建議：

在excel2003中，使用alt_EID或F2+ctrl_回車來取代ctrl-D進行向下數據填充，使用shift_F12或alt_FS來取代ctrl-S進行保存；平時養成使用alt_FS保存文件和用alt_EID進行填充的習慣。在用excel2003做重要的大量的數據統計時，我會屏蔽掉ctrl-D和ctrl_S（屏蔽掉ctrl_D的方法詳見後文注釋）。

）

圖示《excel改變數據陣列的形狀.png》如下：

excel改變數據陣列的形狀.png

用到的數據見以下代碼塊。代碼塊可供測試使用。用法：複製，到excel中點擊單元格A1，貼入，然後分列，依照分隔符號，分隔符號勾選空格，分列。然後依上面的介紹來測試。

代碼 2010 2011 2012 代碼 年份 數據 序號
001 A B C 001 2010 A 1
002 D E F 001 2011 B 2
003 G H I 001 2012 C 3
004 J K L 002 2010 D 4
005 M N O 002 2011 E 5
006 P Q R 002 2012 F 6
007 S T U 003 2010 G 7
008 V W X 003 2011 H 8
009 Y Z [ 003 2012 I 9
004 2010 J 10
004 2011 K 11
004 2012 L 12
005 2010 M 13
005 2011 N 14
005 2012 O 15
006 2010 P 16
006 2011 Q 17
006 2012 R 18
007 2010 S 19
007 2011 T 20
007 2012 U 21
008 2010 V 22
008 2011 W 23
008 2012 X 24
009 2010 Y 25
009 2011 Z 26
009 2012 [ 27

注釋：

不再建議在excel2003中使用默認快鍵ctrl_D和ctrl_S。

在excel2003中使用ctrl_D或ctrl-S時容易誤操作成為ctrl-DS，可能會使數據被大量重複內容覆蓋並且無法撤銷。因此我建議：

在excel2003中，使用alt_EID或F2+ctrl_回車來取代ctrl-D進行向下數據填充，使用shift_F12或alt_FS來取代ctrl-S進行保存；平時養成使用alt_FS保存文件和用alt_EID進行填充的習慣。在用excel2003做重要的大量的數據統計時，我會屏蔽掉ctrl-D和ctrl_S。

屏蔽掉ctrl_D的方法是：

按alt_F11，或調用excel2003菜單-工具-宏-VBA編輯器，插入-模塊，輸入以下內容：

Rem Sub = Public Sub

Sub notice() 提示語
MsgBox "向下填充數據，請改用：F2+ctrl+回車，或alt_EID(excel2003)" vbCrLf "以免在按(ctrl)^S或^D時誤按成為^DS，使數據被大量重複內容覆蓋並且無法撤銷" vbCrLf vbCrLf "保存文件，請改用shift_F12或alt_FS(excel2003)"
End Sub

Sub Maskctrl_SCtrl_D()
Rem MaskCtrl_SCtrl_D,屏蔽Ctrl_S和Ctrl_D
Application.OnKey "^s", "notice"
Application.OnKey "^d", "notice"
Call notice
End Sub

Private Sub Auto_Open()
Call Maskctrl_SCtrl_D
End Sub

然後保存。然後在excel中按alt_F8運行（下文再講在excel打開時就自動運行這個宏的方案）。此後，按ctrl_S或ctrl_D，會提示：

向下填充數據，請改用：F2+ctrl+回車，或alt_EID(excel2003)
以免在按(ctrl)^S或^D時誤按成為^DS，使數據被大量重複內容覆蓋並且無法撤銷

保存文件，請改用shift_F12或alt_FS(excel2003)

如果要在excel啟動時就運行這個宏，方案是：

將這個包含了宏的文件，另存為xls格式，如命名為 Maskctrl_SCtrl_D.xls，複製到以下文件夾中。

默認放置的文件夾位置是：

C:UsersadministratorAppDataRoamingMicrosoftExcelXLSTART

我目前安裝了xlsx兼容包，目前另存為: xlsm，xltm之一格式（xlt，xlsx與xltx格式不行，因為它們不能保存宏），複製到以上文件夾中，均能自動執行其中的宏。xla與xlam這兩種格式也能自動執行，但是不推薦使用，原因是：

這些格式的文件會被wps2019自動調用，出現在wps2019中不方便編輯的現象：每運行這個宏一次，會失去游標響應和快鍵F2的響應，需重新將游標在某個單元格雙擊之後才出現游標才能編輯，才能啟用F2有響應。（這可能是wps2019的一個bug）

同時，wps2019中是不需要屏蔽這兩個快捷鍵的，因為wps2019中，按ctrl-S保存了文件之後，仍然可以撤銷。同理，可能最好也不要使用xlsm，xltm格式，可能會對excel2007以上的版本中造成這兩個快鍵的屏蔽。

但是，另存為xla格式（excel2003載入宏*.xla)格式，對於excel2003有一個特別的情況：那就是：將xla格式的包含宏的文件直接存到或複製到：C:UsersadministratorAppDataRoamingMicrosoftAddIns

（此文件夾的默認訪問方式為：excel2003菜單-工具-載入項-瀏覽）。 此後，會在excel2003菜單-工具-載入項中顯示此宏名的列表，然後可以打開或關閉這個宏的使用；但是關閉這個宏的使用並沒有恢復原來的快鍵的使用；如果關閉了要重新打開，目前我發現只能重新啟動excel2003，這個有點不方便，待解決。 使用宏安全性低或中，並不能解決這個重新啟用快捷鍵的問題。可能是我的這個宏的設計還需要改進，或者使用密碼或鍵盤輸入來決定宏的功能，是另一個方案。待改進。

如果你想學函數的話，用int和mod函數。如果你只是想完成任務，那就複製粘貼

ctrl alt d p 把多維數據錶轉換原始數據表，然後再做透視表

推薦閱讀：

※可以通過哪些途徑學word和excel？
※excel使用各種函數時什麼時候需要絕對引用？
※excel中十多萬條數據應如何處理？

TAG:MicrosoftExcel | Excel使用 | 電子表格 |