陶金武
大家好,我是頭條號Excel自學成才
Excel行列快速轉換有以下兩種方法,各有優缺點,下面進行簡要的介紹
方法一:使用選擇性粘貼進行轉置的方法
我們複製原始數據,然後右鍵,點擊選擇性粘貼,然後在彈出的窗口中,選擇轉置,點擊確定,即可得到結果
優點:容易記憶,操作也方便,下次拿到可以直接用
缺點:將原始數據中有一個數據更改的時候,轉置的數據源不會自動更新
方法二:使用transpose函數法
需要先選中轉置後數據的位置,並且選好幾行幾列,然後在公式編輯欄裡面輸入公式:
=TRANSPOSE(A1:B8),按CTRL+SHIFT+ENTER三鍵得到結果,操作動圖如下所示:
這個的缺點很明顯,我們要對轉置後的結果是幾行幾列需要很瞭解,然後公式記憶以及輸入難度比較大
優點就是,當原始數據中更新了一個內容時,下面轉置 的數據區域也會自動的進行更新,無需再次進行手動的操作,
如我們把A4單元格的內容進行更換時,第1種技巧的方法得到的結果沒有任何的變化,而使用公式法,下面的數據自動的進行了更新~
這兩個實用的方法,你都get到了麼?
頭條號:Excel自學成才,主頁有更多精彩內容!
Excel自學成才
比如:問題中的表格如下,要把列和行互換,有三種方法。
圖一
方法一:採用選擇性粘貼的轉置來實現表格行列轉換。
步驟1、選中要進行行列互換的表格單元格區域,複製;
步驟2、點擊要放置數據的單元格位置,接著點擊“粘貼”->“選擇性粘貼”項;
步驟3、在彈出的【選擇性粘貼】窗口中,勾選【轉置】項,並點擊【確定】。
此時就可以看到表格行列轉換成功。
方法二、利用TRANSPOS函數
TRANSPOS函數可以說就是為“行列轉換”而生。
TRANSPOS函數的語法結構:TRANSPOSE(array)。
參數array:表示要轉換的工作表區域。
步驟1、我們觀察要做行列轉換的原來的表格是13*5,進行行列互換的話,互換後就變成5*13,所以首先我們要選擇轉置後要放的單元格區域位置,5*13,這裡我們選擇G2:Q6;
步驟2、選擇好後,在編輯欄裡輸入調用函數,即輸入“=TRANSPOSE”;
步驟3、設定準備轉置的單元格區域,即輸入:“=TRANSPOSE(A1:E13)”
步驟4、按下Ctrl+Shift+Enter組合鍵,表格自動完成轉置。
最後,可以對錶格進行排版一下,得到下圖:
方法三、利用INDIRECT函數和ADDRESS函數相結合
indirect函數的語法格式:=INDIRECT(ref_text,[a1])。
參數Ref_text :為對單元格的引用,一種加引號,一種不加引號。加引號,文本引用,不加引號,地址引用。
參數a1 :為一邏輯值,指明包含在單元格ref_text 中的引用的類型。a1可以為TRUE 或為 FALSE,也可以省略。
ADDRESS函數:按照給定的行號和列標,建立文本類型的單元格地址。
ADDRESS函數語法:ADDRESS(row_num,column_num,abs_num,a1,sheet_text),
Row_num參數:為在單元格引用中使用的行號,
Column_num參數:為在單元格引用中使用的列標,
ABS_num參數:為返回的引用類型,具體如下:
A1參數:為用以指定 A1 或 R1C1 引用樣式的邏輯值。如果 A1 為 TRUE 或省略,函數 ADDRESS 返回 A1 樣式的引用;如果 A1 為 FALSE,函數 ADDRESS 返回 R1C1 樣式的引用。
Sheet_text參數:為一文本,指定作為外部引用的工作表的名稱,如果省略 sheet_text,則不使用任何工作表名。
所以,以上圖一的表格,要把行和列轉置,可以直接運用公式:=INDIRECT((ADDRESS(COLUMN(A1),ROW((A1))))),具體看以下步驟:
步驟1、首先我們在轉置後要放的單元格區域的第一個單元格,現在我這裡選擇G1單元格,輸入以下公式:=INDIRECT((ADDRESS(COLUMN(A1),ROW((A1))))),即插入A1單元格的內容;
步驟2、要做行列轉換的原來的表格是13*5,進行行列互換的話,互換後就變成5*13,在步驟1的時候,轉置後的新表格第一單元格我們是放在G1,所以這裡,轉置後的新單元格區域是G1:Q5,所以我們複製G1單元格的公式,填充到G1:Q5區域的所有單元格,可以看到原來的表格行和列就互換成功了。
Office學習
能完成這個需求的技巧非常多,今天我就挑其中最簡單最實用的技巧給大家介紹一下。
一、強大無比的選擇性粘貼(轉置)
選擇性粘貼是Excel自帶的一個非常強大實用的簡單功能之一。其不僅可以直接執行粘貼的任務,而且還可以執行運算、轉置等任務。那麼我們該如何利用選擇性粘貼搞定上面的需求呢?
方法:選擇A1:N6區域,按下Ctrl+C組合鍵進行復制,然後選中A9單元格,按下Ctrl+Alt+V組合鍵打開“選擇性粘貼”對話框,其他項下勾選轉置,單擊確定即可。
二、簡單實用的Transpose函數
函數永遠都是Excel中最實用強大的功能之一,可以這樣說,如果Excel沒有函數的話,整個Excel軟件都會變得毫無魅力。Transpose函數為轉置函數,可以批量互換表格的行與列。依然以此例來進行操作,僅僅一條公式即可達到我們的目的。
注意:1.在利用transpose函數進行行列轉換時,一定記得選擇的單元格區域要與原數據區域匹配,本例中,原數據的行數等於新數據區域的列數,原數據的列數等於新數據區域的行數。
2.編輯輸入公式後,記得按住Ctrl+Shift+Enter組合鍵,而不是隻敲回車,完成數組公式的錄入。
我叫胡定祥,酷愛Excel。頭條號:傲看今朝。自由撰稿人,辦公室er.酷愛Excel,一個有兩把“刷子”的胖子。歡迎關注我,有任何問題,十分歡迎大家在評論區留言。傲看今朝
對數據進行不同維度快速拆分
案例說明:將對應人員產品的銷量,按照右邊的格式進行快速拆分
函數公式:
SUMPRODUCT(($B$3:$B$14=$G3)*($C$3:$C$14=H$2)*($D$3:$D$14))
函數講解:
這裡我們可能需要注意的就是了解相對引用和絕對引用方法的使用。比如:
($B$3:$B$14=$G3),我們固定了G3的列,這樣是我們往右邊拖動的時候,對應的姓名不會變,往下拖動姓名才會變;
($C$3:$C$14=H$2,我們固定了行,因為這樣我們往右邊拖動的時候,列可以自動發生變化,但是往下拖動的時候行不會發生變化。
Excel函數與VBA實例
在Excel表中我們經常會遇到把行轉為列,把列轉為行。當然相信還有不少人都是一個一個去把行的內容複製粘貼到列,這樣做重複了很多次而且工作效率極低。那麼有什麼方法可以快速把Excel表的行轉為列,列轉為行呢?這裡給大家介紹2個方法,保證你看完一遍就懂!
當下面表格的名字越來越多時,表格也就越來越來,查看也就越來越麻煩。
而下面這個表格顯示看起來比較舒心,這也是我們平時製作表格的方式。
那麼通過對比,我們知道哪個圖片才是我們想要的。但是有時候別人發給我們的表格並不是我們想要的方式,而且數據量很大,這時該用什麼方法變成我們想要的呢?
方法一:使用“選擇性粘貼”
步驟:選中數據區域 -- 按快捷鍵“Ctrl + C”複製 -- 在空白單元格點擊鼠標右鍵 -- “選擇性粘貼”-- “轉置”。
動圖演示:
方法二:使用行列轉置函數“TRANSPOSE”。
步驟:選擇一個單元格區域用於顯示結果(A8:F16) -- 在編輯欄輸入公式“=TRANSPOSE($A$1:$I$6)”-- 按快捷鍵“Ctrl + Shift + Enter”即可。
這個方法有一個不足之處就是:生成的區域無法直接修改。只有當原數據更改時,生成的數據才會隨著更改。
如果您覺得本文不錯,請關注+轉發+點贊+評論!
跟小小筱學辦公技能
在工作中,我們經常會用到Excel,學會一些Excel操作技巧,能讓我們在工作中省力不少,今天我們就來看看Excel中的行列互換問題。
一、通過函數來轉換行列
1、一列變一行:
=INDEX($A1:$A9,COLUMN(A1))
2、一行變一列:
=INDEX(A$5:I$5,ROW(A1))
3、一行變多行:
①先行後列:=INDEX($A$1:$I$1,(ROW(A1)-1)*3+COLUMN(A1))
②先列後行:=INDEX($A$1:$I$1,(COLUMN(A1)-1)*3+ROW(A1))
二、其他便捷轉換技巧
上面的函數一個都記不住,怎麼辦?下面在來看一些使用的行列互轉技巧吧。
1、轉置
操作方法:複製表格後,選擇“選擇性粘貼”,勾選“轉置”,不用繁瑣的函數,直接轉換行列。
2、利用剪貼板
想將多列變成一列,可以藉助剪貼板功能,將內容先複製到剪貼板中,在進行粘貼。
3、錯位引用
場景①:多列變一列
如下圖:在A5輸入“=B1”,錯位粘貼,接著複製A列中的所有數據,選擇“選擇性粘貼—數值”,將公式轉成數值,將多列變成一列。
場景②:一列變多列
在C1輸入“=C6”(第二列開始位置),接著向下、向右填充數據,然後複製“D1:G5”區域,選擇“選擇性粘貼—數值”。
以上就是一些關於Excel行列轉換的技巧了。如果想要轉換Excel表格格式,2010辦可以直接通過“另存為”功能,更改保存類型。
而低版本的Excel如果不想下載軟件,就需要藉助在線轉換工具了。
奇趣軟件技巧達人
行列轉換可以分幾種情況:
1、單列(行)的轉換
單行換單列,一般我們使用Excel的選擇性粘貼-轉置即可,多行轉多列,也是同理
同理也是可以使用Transpose函數處理,但是一般還是不推薦,好奇的同學可以嘗試一下
具體看效果圖:
2、一列轉多列
利用相對引用的原理。在旁邊的列=數據源中需要切割的位置,比如本示例中,3個一列,那麼就是等於4,使用總數/3 就是最後拆分出來的列數
3、多列轉一列
原理基本同2
具體看效果圖:
4、二維轉一維
使用PQ中的逆透視功能,完成二維轉一維,方便透視處理
Power Query 是2010版本新增的功能,2010和2013版本需要下載安裝插件,具體百度一下,按照自己的版本下載安裝,如果是2016版本,那麼自帶PQ,在數據選項卡下
我們本例是用時了,轉換功能中的逆透視其他列功能完成,非常的高效快捷!!
5、一維轉二維(多維)
利用數據透視表,可以完成多維的分析,非常的方便
一維數據是非常方便分析,一般在Excel中使用透視表即可完成一維的各種轉換分析
Excel辦公實戰
關於行列轉換,我提供一個非常簡單的方法:
案例:
怎麼把 A 列數據快速填充到黃色區域?按先行後列的習慣排列?
解決方案:
1. 在黃色區域依次填入A1~A24,用填充+拖動方法可以快速實現
- A可以是任意值
- 數據列的第一行為幾,數字就以幾開頭,比如本例的第一個數據在行 1
2. 選中黃色區域 --> 按 Ctrl+F --> 將“A”全部替換為“=a”
這就已經完成了。
Excel學習世界
EXCEL中的行列轉換常見有幾種方法:
1、選擇性粘貼
2、Transpose 函數
3、INDEX函數
下面具體來看下怎麼操作吧:
選擇性粘貼:複製→選擇性粘貼→轉置
Transpose函數:選中轉置後區域,輸入下列函數
=TRANSPOSE(A1:D5) 按住Ctrl+Shift+Enter輸入
INDEX函數:輸入下面函數,往右往下拖即可:
=INDEX($A$1:$D$5,COLUMN(A:A),ROW(1:1))
選擇性粘貼法最為簡便,但每次都需要操作一次,對於使用頻次非常高的場景可能效率會有點低。函數法不能保留格式,但是源數據發生更改可以自動更新,並且一次操作永久保留,TRANSPOSE函數使用更加簡潔,但需要事先選擇好轉置後區域,數據量較大時使用不太方便,INDEX函數使用比較靈活!結合您的需要酌情使用!
Excel精選技巧
選中整個表格,按 ctrl+c 複製,在下方點擊右鍵,點擊“選擇性粘貼”中的 “轉置”,這樣新複製出來的表格行列內容就互換了
打開鏈接可以查看操作視頻哦
https://quqi.gblhgk.com/s/839459/hQBmnFW2ze7AOlga