如何快速轉(zhuǎn)換一維表與二維表? Excel一維表和二維表互轉(zhuǎn)的方法

在實(shí)際工作中,我們經(jīng)常需要對(duì)數(shù)據(jù)的結(jié)構(gòu)進(jìn)行轉(zhuǎn)換。比如,為了更直觀、方便查看數(shù)據(jù),需要將下圖左邊一維表轉(zhuǎn)換成如右圖的二維表:
又或者為了更好的統(tǒng)計(jì)分析數(shù)據(jù),需要將左邊的二維表數(shù)據(jù)轉(zhuǎn)換成右邊的一維表格式:
一維表和二維表簡(jiǎn)介
所謂一維表是指由一個(gè)單一的行和多個(gè)列組成的數(shù)據(jù)表,一維表的行通常被稱為“記錄”或“行數(shù)據(jù)”,每一條記錄都包含了與之相關(guān)的多個(gè)字段,每個(gè)字段則對(duì)應(yīng)于一列數(shù)據(jù),一維表中的數(shù)據(jù)可以被快速的查找、排序和過(guò)濾。比如如下表格。
這個(gè)表格由三個(gè)字段(Field)組成,分別是"Name"、"Date"和"Sales",表格中的每一行數(shù)據(jù)稱為一條記錄(Record),這就是一個(gè)典型的一維表。
二維表也被稱為關(guān)系型表格,它由若干行和若干列組成,每一列表示一個(gè)特定的屬性或數(shù)據(jù)類(lèi)型,每一行則表示一條記錄或數(shù)據(jù)項(xiàng)。每個(gè)單元格則表示一組對(duì)應(yīng)行和列的數(shù)據(jù)值。通常,二維表是在SQL(結(jié)構(gòu)化查詢語(yǔ)言)中進(jìn)行操作和查詢的主要數(shù)據(jù)結(jié)構(gòu)。比如如下表格。
這個(gè)表格的第一行表示日期,第一列表示人名,表格中的每一個(gè)數(shù)據(jù)由行標(biāo)簽和列標(biāo)簽共同賦予其屬性,比如單元格B2的數(shù)據(jù)“289”代表Aileen在2023年4月1日的銷(xiāo)售額。這是一個(gè)典型的二維表。
下面就來(lái)詳細(xì)說(shuō)說(shuō),怎么用函數(shù)公式實(shí)現(xiàn)這兩種數(shù)據(jù)結(jié)構(gòu)的相互轉(zhuǎn)換。
1、一維表轉(zhuǎn)化成二維表
? 提取年級(jí)變成標(biāo)題行
在 D2 輸入公式:
=TRANSPOSE(UNIQUE(A2:A34))
首先用 UNIQUE 函數(shù)提取 A 列不重復(fù)的年級(jí)變成一列,再用 TRANSPOSE 函數(shù)將得到的一列數(shù)據(jù)轉(zhuǎn)置成一行。
效果如下圖所示:
? 提取對(duì)應(yīng)的名單
在 D2 單元格輸入公式:
=FILTER($B2:$B34,$A2:$A34=D1)
用 FILTER 函數(shù),以年級(jí)作為篩選條件,篩選出對(duì)應(yīng)的名單。再將公式向右拖動(dòng)填充,得到如下圖右表的效果:
2、二維表轉(zhuǎn)化成一維表
? 首先用 IF 函數(shù)判斷名單區(qū)域「A2:D15」是否為空;如果為空則返回錯(cuò)誤值 (#NAME?),否則返回第一行「A1:D1」對(duì)應(yīng)的年級(jí)。
在 F1 單元格輸入公式:
=IF(A2:D15="",x,A1:D1)
結(jié)果返回一個(gè)多行 4 列的數(shù)組,效果如下圖「F1:I14」區(qū)域所示:
? 然后利用 TOCOL 函數(shù)將這組多行 4 列的數(shù)組轉(zhuǎn)化成一列。
=TOCOL( IF(A2:D15 = "", x, A1:D1), 2, 1 )
效果如下圖 F 列所示:
TOCOL 是 Office 365 版本新增的函數(shù),非常實(shí)用和強(qiáng)大,它可以將多數(shù)組轉(zhuǎn)化為一列數(shù)據(jù)。
該函數(shù)的語(yǔ)法為:
=TOCOL(array, [ignore], [scan_by_column])
第一參數(shù)是需要轉(zhuǎn)化成列的數(shù)組,公式中 TOCOL 函數(shù)的第一個(gè)參數(shù) IF (A2:D15 = "", x, A1:D1) 是需要轉(zhuǎn)化的數(shù)組;
第二參數(shù)可以選擇是否忽略空白或錯(cuò)誤,公式中的第二個(gè)參數(shù)是 2,表示忽略區(qū)域中的錯(cuò)誤值;
第三參數(shù)表示掃描方式,可以設(shè)定是按行方向掃描數(shù)組還是按列方向掃描數(shù)組,默認(rèn)情況下按行掃描,如果要按列掃描,則值為 TRUE 或 1。
? 最后再用 TOCOL 函數(shù)將姓名區(qū)域「A2:D15」,也轉(zhuǎn)化成一列。
在 G1 單元格輸入公式:
=TOCOL(A2:D15,1,1)
公式中的第二參數(shù)是 1,表示忽略區(qū)域「A2:D15」中的空白,轉(zhuǎn)化成一列。效果如下圖 G 列所示:
3、最后小結(jié)
? 一維表轉(zhuǎn)化成二維表:首先用 UNIQUE 函數(shù)提取一列的不重復(fù)值,作為標(biāo)題行;然后用 FILTER 函數(shù),以標(biāo)題作為篩選條件,提取對(duì)應(yīng)的內(nèi)容。
? 二維表格轉(zhuǎn)化成一維表:當(dāng) TOCOL 函數(shù)第二參數(shù)為 2 時(shí),忽略錯(cuò)誤值,將標(biāo)題行轉(zhuǎn)化成一列,再利用 TOCOL 函數(shù)第二參數(shù)為 1 時(shí),忽略空白,將對(duì)應(yīng)的區(qū)域轉(zhuǎn)化成一列。
好了,今天就說(shuō)到這里結(jié)束啦~
本文來(lái)自微信公眾號(hào):秋葉 Excel (ID:excel100),作者:趙驕陽(yáng)
以上就是Excel一維表和二維表互轉(zhuǎn)的方法,希望大家喜歡,請(qǐng)繼續(xù)關(guān)注腳本之家。
相關(guān)推薦:
excel圖表復(fù)制到word后怎么保持原格式? Excel表格樣式復(fù)制技巧
Excel通配符怎么使用? Excel表格中通配符使用技巧分享
相關(guān)文章
如何用excel制作漂亮的課表? Excel表格做動(dòng)態(tài)課程表的技巧
如何用excel制作漂亮的課表?excel表格制作課程表的時(shí)候,想要做的漂亮些,并添加動(dòng)畫(huà)效果,該怎么操作呢?下面我們就來(lái)看看Excel表格做動(dòng)態(tài)課程表的技巧2022-10-12Excel表格怎么打印在一張紙上 Excel表格打印在一張紙上方法
有時(shí)候我們直接使用縮放功能將表格強(qiáng)制縮放為一張紙打印。雖然實(shí)現(xiàn)了將表格打印在一張紙上的目的,但是這么做會(huì)導(dǎo)致表格的字號(hào)過(guò)小不利于閱讀數(shù)據(jù),那么有沒(méi)有更好的操作方2022-09-30excel表格怎么讓日期和星期同時(shí)顯示 excel表格日期和星期同時(shí)顯示方法
有時(shí)需要在表格中顯示日期的同時(shí)又顯示星期,一個(gè)個(gè)輸入就很麻煩了,有什么好的辦法呢?一起來(lái)了解一下吧2022-09-25excel制作五線譜音符? Excel表格插入五線譜圖標(biāo)的技巧
excel制作五線譜音符?excel表格中想要插入一個(gè)圖標(biāo),比如五線譜音符,該怎么插入呢?下面我們就來(lái)看看Excel表格插入五線譜圖標(biāo)的技巧2022-08-11Excel怎么篩選出自己想要的內(nèi)容?excel表格自定義篩選內(nèi)容操作教程
這篇文章主要介紹了Excel怎么篩選出自己想要的內(nèi)容?excel表格篩選內(nèi)容操作教程的相關(guān)資料,需要的朋友可以參考下本文詳細(xì)內(nèi)容介紹2022-07-26Excel怎么批量刪除電子表格中不同的文字 Excel快速替換不規(guī)則字段方法
想要快速替換掉表格里不規(guī)則的字段,但是既然想批量完成操作,就一定要找到某個(gè)規(guī)律,然后將不規(guī)則字段用星號(hào)進(jìn)行替換,具體應(yīng)該如何操作替換呢2022-07-17Excel表格導(dǎo)入Word不能全部顯示怎么辦 Excel表格導(dǎo)入Word全部顯示方法
可以通過(guò)拖拉擴(kuò)大Excel表格可顯示部分解決插入的Excel電子表格數(shù)據(jù)顯示不全的問(wèn)題,下面給大家分享Word中插入的Excel表格數(shù)據(jù)顯示不完全怎么辦2022-07-15Excel怎么分割一列單元格的內(nèi)容 excel表格分列操作方法
excel表格怎么把一列里面的內(nèi)容分開(kāi)呢?下面和大家分享一下解決這個(gè)問(wèn)題的方法,希望可以幫助到有需要的朋友2022-07-13xml格式文件怎么導(dǎo)入到excel?Excel表格將XML文件導(dǎo)入的操作教程
這篇文章主要介紹了xml格式文件怎么導(dǎo)入到excel?Excel表格將XML文件導(dǎo)入的操作教程的相關(guān)資料,需要的朋友可以參考下本文詳細(xì)內(nèi)容介紹2022-07-06Excel內(nèi)容怎么保存為圖片 Excel表格內(nèi)容保存為圖片的方法
有一些朋友想要把Excel表格里的主要內(nèi)容保存成圖片,這樣直接看圖會(huì)比較方便一些,今天小編要給朋友們介紹Excel內(nèi)容保存為圖片的方法2022-07-04