如何對二維表進行匹配！

原表格！

備注：以上人名，均屬虛構，如有雷同！說明有緣！?。?/p>

咳咳！要做什么呢！

這位親想要得到不同地區(qū)，不同人的銷售量！

阿凱提問：“親！能否將你的原始數(shù)據(jù)表改成正常的一維表格嗎？就是平常常見的那種第一列是地區(qū)，第二列是姓名，第三列是銷售量那種！如果是那種，直接套用Vlookup的多條件匹配就行啦！”

網(wǎng)友回應：

阿凱內(nèi)心寫照：

我就想呀想！想呀想！用了0.1秒鐘想出來方法！

接下來是見證奇跡的時刻??！

提問：二維表，符合某種條件返回數(shù)據(jù)！什么函數(shù)最好用？？

回答：Offset

提問：Offset函數(shù)會用嗎？

回答：不會！

待我從頭細細說來?。。。?/p>

原表重新來一次！

目標：

需求簡化為，在二維表提取滿足雙條件信息！

二維表的應用首先想到的是Offset函數(shù)！

Offset函數(shù)怎么用呢？？？

OFFSET函數(shù)的功能為以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。

上面那段話你愿意讀嗎？不愿意我給你翻譯一下！

Offset函數(shù)類似于曾經(jīng)我們中學數(shù)學的坐標系公式。以某個單元格作為坐標系的坐標原點，返回符合橫縱坐標的值！

Offset最簡單用法：

=Offset（坐標原點單元格，向下移動的行數(shù)，向右移動的列數(shù)）

第二個參數(shù)，如果正數(shù)向下移動，如果負數(shù)向上移動

第三個參數(shù)，如果正數(shù)向右移動，如果負數(shù)向左移動

我以A1單元格為例，如何獲取涂黃的單元格內(nèi)容？？？

我們開始數(shù)數(shù)！從A1單元格開始，需要向下移動幾行？2行！

需要向右移動幾列？1列！

So 公式就是！=OFFSET(A1,2,1)

發(fā)現(xiàn)想要返回二維表的值！Offset是否可以完美解決呢！

下個問題，我如何能很智能的知道向下和向右移動的行數(shù)呢？

然后我發(fā)現(xiàn)了一個問題！姓名在姓名列表中的第幾位，就是向下移動幾行！地區(qū)在地區(qū)列表的第幾位，就是向右移動幾列！

給自己點贊！

那如何獲取某個單元格在列表中排在第幾位呢？

=match（內(nèi)容，列表，0）match函數(shù)的用法就是獲取某個值在列表中排名第幾！

感覺我做出來了！

當當當當！??！

公式：

=OFFSET($A$1,MATCH(B11,$A$2:$A$8,0),MATCH(A11,$B$1:$F$1,0))

小長！拆分一下公式

最外層就是Offset公式，且以A1單元格作為坐標原點，沒什么說的哈！

里面是兩個Match函數(shù)。

MATCH(B11,$A$2:$A$8,0)找姓名在姓名列表中第幾位

MATCH(A11,$B$1:$F$1,0)找地區(qū)在地區(qū)列表中第幾位

excel使用技巧大全如何匯總多個excel表格這是很多朋友都非常困擾的一個問題，今天我們來介紹一下不用公式如何來輕松的完成。

如下圖所示，在一個工作簿中有4個城市的銷量表，我們現(xiàn)在要將這4個表用數(shù)據(jù)表來匯總起來。

方法如下：1、按alt+d鍵，松開后按d鍵。這時就會打開數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?，?步選取“多重合并計算數(shù)據(jù)區(qū)域”，請看下圖。

2、點擊“自定義頁字段”

3、添加區(qū)域和設置字段數(shù)目和名稱。

4、添加要合并的所有表格，并且依次的給所有表設置名稱和字段數(shù)目。

5、點擊完成后就會生成數(shù)據(jù)透視表，將頁字段名字從“頁1”改成“城市”，“行”改成“產(chǎn)品”。

沒更改前：

更改后為：

設置ok!

下面我們就能夠通過字段位置的調(diào)整來完成模式不同的匯總。

以城市+產(chǎn)品匯總

以產(chǎn)品+城市匯總

數(shù)據(jù)透視表只是可以完成一些比較簡單的表格的合并，但一些行數(shù)列數(shù)比較多比較復雜，格式又不一致的表格就要用到SQL語句來合并了。在之后的課堂上我們會講到這個的，謝謝。

最近做一堆文件，每天從早忙到晚，做不完的還要拿回家做，一堆電子表格看得頭暈眼花，各位有沒有類似的經(jīng)歷??！

這么多年學習辦公軟件，都是見一點學一點，很少有系統(tǒng)性的學習，今天讓我們來扒一扒excel那些神奇的技巧。

技巧一、統(tǒng)計某一項中某個值的總數(shù)，比如下面這張圖在函數(shù)countif中選中政治面貌這行，然后用countif函數(shù)就可以統(tǒng)計出黨員這一項的總數(shù)了。

技巧二、選擇性粘貼，有時候我們要給某一列同時加上或者乘以一個數(shù)，總是特別麻煩，讓我們看看這個技巧。

技巧三、利用數(shù)據(jù)有效性功能來防止重復輸入。

技巧四、利用today函數(shù)計算可變動日期

除此外小編還收集了很多類似的技巧，有興趣的朋友可以關注微信號：嗶嗶網(wǎng)民，回復excel

在日常工作中，經(jīng)常需要將相似結構或內(nèi)容多個表格進行合并匯總。當數(shù)量龐大時人工進行計算比較繁瑣，工作量大且易犯錯，使用Excel中的“合并計算”功能可以輕松完成這項任務。

合并計算的數(shù)據(jù)源可以是同一工作表中的不同表格，也可以是同一工作簿中的不同工作表，還可以是不同工作簿中的表格?，F(xiàn)針對第一種情形進行演示說明：

如圖1所示的兩個結構相同的數(shù)據(jù)表“表一”和“表二”，利用合并計算可以輕松將這兩個表格進行合并，具體步驟如下。

圖1 原始表

步驟1選中B10單元格，作為合并計算后結果的存放起始位置，再單擊【數(shù)據(jù)】選項卡【數(shù)據(jù)工具】命令組的【合并計算】命令按鈕，打開【合并計算】對話框，如圖2所示。

圖2 打開【合并計算】對話框

步驟2激活【引用位置】編輯框，選中“表一”的B2:D6單元格區(qū)域，然后在【合并計算】對話框中單擊【添加】按鈕，所引用的單元格區(qū)域地址會出現(xiàn)在【所有引用位置】列表框中。使用同樣的方法將“表二”的F2:H6單元格區(qū)域添加到【所有引用位置】列表中。

步驟3依次勾選【首行】復選框和【最左列】復選框，然后單擊【確定】按鈕，即可生成合并計算結果表，如圖3所示。

圖3 生成合并計算結果表

注意：

1.在使用按類別合并的功能時，數(shù)據(jù)源列表必須包含行或列標題，并且在“合并計算”對話框的【標簽位置】組合框中勾選相應的復選框。

2.合并的結果表中包含行列標題，但在同時選中【首行】和【最左列】復選項時，所生成的合并結果表會缺失第一列的列標題。

3.合并后，結果表的數(shù)據(jù)項排列順序是按第一個數(shù)據(jù)源表的數(shù)據(jù)項順序排列的。

4.合并計算過程中不能復制數(shù)據(jù)源表的格式。如果要設置結果表的格式，可以使用【格式刷】將數(shù)據(jù)源表的格式復制到結果表中。

有的Excel表格列數(shù)很少，而行數(shù)卻很多。例如下圖，有3列，658行數(shù)據(jù)，在這種情況下直接打印，每張紙的信息量較少，打印效果不夠美觀，同時紙張得不到充分利用，造成極大的浪費。

藥品價格數(shù)據(jù)來自網(wǎng)絡

直接打印效果

多排表制作打印效果

下面，小編將介紹多排表工具，完美打印多行Excel表格

運行多排表工具

多排表工具是用Excel VBA開發(fā)的加載宏，用來解決行數(shù)多而列數(shù)少表格的實用工具，既能使版面更加美觀、協(xié)調(diào)，又能節(jié)約大量紙張。使用時只需雙擊打開即可

多排表工具.xla

運行多排表工具：加載項→多排表工具→制作多排表→顯示如圖程序主界面

參數(shù)說明

每頁排數(shù)：源表制作成多排表后，表內(nèi)所包含的排數(shù)

每排列數(shù)：每一排內(nèi)所包含的列數(shù)

每頁行數(shù)：源表制作成多排表后，每一頁所包含的行數(shù)

起始行號：源表表頭下面一行的行號

終止行號：源表最后一行的行號

如果多排表參數(shù)相對固定，可點擊保存參數(shù)，下次制作時點擊載入?yún)?shù)，就能使用原先保存的參數(shù)。

選項說明

新增工作表：打鉤表示將工作表復制另一份副本，然后在另一份本上進行多排表操作

生成鏈接多排表：打鉤表示源表內(nèi)容變化時，多排表內(nèi)容也隨之變化

清除公式：打鉤表示清除多排表內(nèi)的所有公式，并保留公式單元格的數(shù)值

每頁獨立橫表頭：打鉤表示多排表各頁都有獨立的表頭

頁末行標注顏色：打鉤是為了當多排表采用共享頭時，能直觀分清各頁之間的界限及方便對表進行頁面設置

自動修改表頭

如果選擇每頁獨立橫表頭并且多排表是2頁以上，需要修改表頭時，只需改動第1頁的表頭，然后點擊自動修改表頭，其余各頁的表頭便可自動修改完成，內(nèi)容和格式與第1頁相同。

實例操作

以下圖顯示的工作表為例，此表有3列，起始行號為第2行，終止行號為第658行。制表前先設置打印頁邊距，確定每頁打印排數(shù)和每排列數(shù)。

注意：源表的第1列應位于A列，A列不能為空列，否則必需重新調(diào)整位置。

打印預覽，確定每排行數(shù)

輸入?yún)?shù)

制作完成

打印預覽

如果要求每排表之間留有一定的間距，只需將第4列設置一定列寬，每排列數(shù)增加1即可

每排4列

以上就是多排表的介紹了，有需要的朋友可以私聊小編。

文章推薦

用Excel提取出生日期、性別、年齡及戶口所在地等信息

使用EXCEL制作一些特效表格，發(fā)揮我們的想象力，今天我們要學習的是制作立體感凹凸感表格，發(fā)揮EXCEL表格最大的優(yōu)點，同時我們也熟悉工具的應用，應用在哪些方面，起哪些作用，熟能生巧，制作立體感效果雖然不是EXCEL的強項，但是我們可以盡量發(fā)揮它最大的作用。

先看一下立體凹凸效果。

現(xiàn)在我就來一步一步來教你制作凹凸不平的效果，非常簡單。

方法一、打開表格，新建空白文件，背景填充灰度色，在隨意輸入一些內(nèi)容，這樣就有個顯明的對比。

方法二、選擇一行，鼠標右擊，選擇“設置單元格格式”在彈出的對話框中選擇“邊框”。

方法三、在彈出的邊框選項中，選擇較粗一點線條，顏色選擇灰度和背景顏色要有點差異，然后在用鼠標點擊邊框的頂端和左則。

方法四、凹進去的效果只是設置邊框的顏色位置不一樣，返方法向選擇，這樣的視覺感就像凹進去了。

在工作中將“二維數(shù)據(jù)表”轉(zhuǎn)換為“一維數(shù)據(jù)表”經(jīng)常用到吧！不會用是不是很尷尬！其實操作起來簡單的不要不要的！趕快來圍觀下！

步驟一：選中任意數(shù)據(jù)區(qū)域——點擊“數(shù)據(jù)—從表格”——彈出“創(chuàng)建表”提示框——點擊“確定”；

步驟二：彈出“查詢編輯器”——點擊“轉(zhuǎn)換”——在“逆透視列”中選擇“逆透視其他列”；

步驟三：點擊“開始”“關閉并上載”；

步驟四：此時EXCEL中會生成一個新sheet，點擊“設計—轉(zhuǎn)換為區(qū)域”——彈出提示框——點擊“確定”；

一維表，二維表分別是什么？

“維”是指物質(zhì)在時空中的衡量參數(shù)。一維是線，二維是面。

然而Excel表哥的維度，與它們根本一點親戚關系都沒有。那什么是一維表，二維表呢？相信許多表親們現(xiàn)在都還搞不清楚吧！

舉個栗子，如下圖哪個是一維表，哪個是二維表呢？

判斷數(shù)據(jù)表是一維表還是二維表最簡單的一個方法就是看列的內(nèi)容，看每一列是否是一個獨立的參數(shù)。如果每一列都是獨立的參數(shù)那就是一維表，如果有兩列及以上都是同類參數(shù)那就是二維表。

在數(shù)據(jù)關系上來說，二維表有一個很明顯的特征，就是列字段中有一部分是相同性質(zhì)的字段。而一維表沒有任何兩個字段是相同性質(zhì)的。

通過上面的判斷依據(jù)，很顯然：圖1左側(cè)的表是一維表，它的類別、產(chǎn)地是兩個不同的列參數(shù)；而右側(cè)的是二維表，它的1月、2月、3月屬于一組性質(zhì)相同的“月份”參數(shù)。

二維轉(zhuǎn)一維有什么用呢？

在數(shù)據(jù)統(tǒng)計分析中，要求所有的Table數(shù)據(jù)都必須是一維，以便于數(shù)據(jù)能夠方便地進行管理。原則上，一維表的數(shù)據(jù)再加工比二維表的要簡單得多，所以我們提倡在做數(shù)據(jù)管理的時候，基礎數(shù)據(jù)要采用一維表。

我們舉一個簡單例子： 從圖2可以看出它是個二維統(tǒng)計表，因為表哥錄入數(shù)據(jù)的時候把年份放在列上了，最近老板要求他重新對2008年的各類茶葉的產(chǎn)量進行綜合統(tǒng)計，必須一行要一個分類一個年份一個產(chǎn)量，如圖3所示，以便后續(xù)能夠快速進行數(shù)據(jù)跟蹤。

怎么實現(xiàn)呢？許多表親們會說，很簡單啊，手動復制就可以。但你仔細想想，圖2數(shù)據(jù)較少是比較容易，可如果數(shù)據(jù)有幾千條、幾萬條呢？手動復制不得累死。因此，我們需要將二維表轉(zhuǎn)為一維表格。

如果是一維數(shù)據(jù)表，只需通過單擊表頭右側(cè)的下拉按鈕，在彈出的對話框中的篩選出選中想要的數(shù)據(jù)，這里選擇中2008選項，即可統(tǒng)計出數(shù)據(jù)。是不是就非常方便了？

通過上面的例子可以看出，我們只有把二維表轉(zhuǎn)化為一維表的形式，才能以它為數(shù)據(jù)源快速地得到其他數(shù)據(jù)。那么，二維表轉(zhuǎn)化一維表步驟是怎樣的呢？下面請看具體操作方法：

EXCEL二維表如何轉(zhuǎn)一維表？

表哥向我訴苦，說是老板要求真多，“好好的一個表格，非要搞成什么一維表，不是明擺著為難人嘛”。其實二維表轉(zhuǎn)一維表并不難。下面，我就來教教各表親們?nèi)绾卧贓xcel 2016中玩轉(zhuǎn)數(shù)據(jù)改造，將二維表轉(zhuǎn)為一維表。

我們先來看看二維表改造前的樣子。

改造成一維表后，變成如下圖所示的效果，怎么實現(xiàn)呢？

那么我們就要對表格的形象進行改造，具體操作方法如下：

第一步：按快捷鍵ALT+D→P(此處先按下Alt+D鍵，然后放掉，再按Alt+P鍵)，在彈出的對話框中按下圖進行設置，并單擊下一步。

第二步：此時，頁字段數(shù)目選擇“創(chuàng)建單頁字段”，別猶豫，我們繼續(xù)下一步改造。

第三步：單擊

，然后在表格中拖動鼠標選定數(shù)據(jù)目標區(qū)域，再繼續(xù)下一步。

第四步：選擇你改造后表格的位置，“新建工作表”或者“現(xiàn)有工作表”都可以，看您心情。這里我們選擇 “新建工作表”。

第五步：此時，表格已有很大改變，如圖8。接下來，見證奇跡的時刻，在表格右側(cè)，在“數(shù)據(jù)透視表字段”窗格中，一般會自動勾選，如果沒勾選，選中“行”“列”“值”三項。

第六步：OK！Excel會自動創(chuàng)建一個新的工作表，基于原二維表形成的一維表。

復合表格怎么辦？

然而，并不是所有二維表都這么好對付。這不，表哥又遇到了麻煩。問小編我下圖10中這類“產(chǎn)地”比較復雜的遠房表格，即常見的混合二維表（又稱復合表格），應該怎么將其轉(zhuǎn)換為一維表呢？

像這類復合表格，就不得不用出Excel 2016的大招，表格專用“查詢編輯器”，下面來看看具體操作方法：

第一步：首先，單擊表格的任意位置或者選中整個表格，再選擇“數(shù)據(jù)”-“從表格”，如下所示。

Excel將自動選擇表格區(qū)域。然后選中“表包含標題”復選框，單擊“確定”按鈕，如下所示。

第二步：打開“表11-查詢編輯器”。此時，混合表中的數(shù)據(jù)將自動上載到編輯器中，如下所示。

第三步：選中表格中多重數(shù)據(jù)的區(qū)域，如，“產(chǎn)地”列，單擊“拆分列”按鈕，如下圖所示。

第四步：因為多重數(shù)據(jù)中是用頓號進行間隔的，所以這里選擇“按分隔符”，如下所示。如果每個標簽字符相同，則可以選擇“按字符數(shù)”。

第五步：彈出“按分隔符拆分列”對話框，在下拉列表中選擇“自定義”選項，在輸入框中輸入“頓號”，再單擊確定。

第六步：此時，將變成如下圖所示的表格。這里選中混合表中的產(chǎn)地區(qū)域。

第七步：選擇“轉(zhuǎn)換”選項卡，單擊“逆透視其他列”按鈕。

第八步：表格變成如下所示的效果。再選擇“屬性”列，單擊鼠標右鍵，在彈出的菜單中選擇“刪除”命令，刪除該列。

第九步：選擇“開始”選項卡，單擊“關閉并上載”按鈕，關閉表格查詢編輯器。

此時，看看數(shù)據(jù)，復合二維表已經(jīng)轉(zhuǎn)換成如下所示的一維表，大功告成。

技巧拓展：

工作中，表格數(shù)據(jù)需要改造的種類非常多。那么，根據(jù)實際情況表親們可選擇不同的改造的方法和技巧。譬如，對于簡單的一維表或二維表數(shù)據(jù)改造，除了前面介紹的方法之外。表親們還可以使用行、列互轉(zhuǎn)的方法來快速實現(xiàn)。

首先選中要轉(zhuǎn)置的源數(shù)據(jù)區(qū)域并復制，鼠標停留在一個空白單元格區(qū)域。然后右鍵選擇黏貼——選擇性粘貼——轉(zhuǎn)置。或者復制并選擇空白單元格之后，直接按Ctrl+Alt+V鍵，在彈出菜單中勾選“轉(zhuǎn)置”復選框，確定即可完成轉(zhuǎn)置。|內(nèi)容來源：中國統(tǒng)計網(wǎng)

百家號-【袁帥數(shù)據(jù)分析運營】運營者：袁帥，會展業(yè)信息化、數(shù)字化領域?qū)＜?。新社匯平臺聯(lián)合創(chuàng)始人，微會動平臺創(chuàng)始人。永洪數(shù)據(jù)科學研究院MVP。認證數(shù)據(jù)分析師、網(wǎng)絡營銷師、SEM搜索引擎營銷師、SEO工程師、中國電子商務職業(yè)經(jīng)理人。暢銷書《互聯(lián)網(wǎng)銷售寶典》聯(lián)合出品人。

關于多表匯總的問題，我在類似的問題中介紹了一種非常好的方法：Power Query（有興趣的同學，請參考僅需3分鐘，輕松幫你搞定Excel多表數(shù)據(jù)匯總）。下面我來介紹一種更為簡單的匯總多表數(shù)據(jù)的技巧。如何快速匯總下圖中的12個sheet中的數(shù)據(jù)到總表呢？

一、無鏈接簡單匯總法（各個數(shù)據(jù)源發(fā)生變化，匯總表不發(fā)生變化）

1.選擇sheet匯總中的任意空白單元格，依次單擊“數(shù)據(jù)”--“合并計算”，如下圖所示：

2.在彈出的對話框中函數(shù)設置為：“求和”。單擊引用位置的折疊按鈕選擇在各個sheet中的數(shù)據(jù)區(qū)域并添加到所有引用位置列表中。勾選標簽位置下的“首行”和“最左列”選項，單擊確定。

3.我們將得到如下的匯總表，這時候我們可以刪除B列和C列，或者將任意sheet中的B列和C列數(shù)據(jù)復制粘貼到匯總表中即可。

二、有鏈接的數(shù)據(jù)匯總（匯總結果隨數(shù)據(jù)源的變化而變化）

此種方式與上面介紹的方法唯一不同的地方就在于多勾選了一個選項，操作技巧都一樣，只是在上面設置合并計算對話框時勾選一下：“創(chuàng)建指向源數(shù)據(jù)的鏈接”即可。

得到的結果也會有所不同。

包含了公式，且前面變成分類匯總的樣式。

前幾天看到群友問到，如何將二維表轉(zhuǎn)成一維表。接下來和大家介紹一個簡單的方法。

這是一個二維表，

希望轉(zhuǎn)成這樣的

大家應該明白用意了，接下來和大家分享利用power query去解決。

①點擊數(shù)據(jù)選項卡，選擇從文件-從工作薄

②選擇對應數(shù)據(jù)所在的工作薄，點擊導入

③編輯二維表

④在power query編輯器中，選中第一列，在轉(zhuǎn)換選項卡下方選擇逆透視其他列。

完成效果

⑤開始選項，點擊關閉并上載即可完成效果，

當然你也可以進行美化格式等，【透視列】和【逆透視列】產(chǎn)生的報表，與原報表數(shù)據(jù)是相關聯(lián)的，當原報表數(shù)據(jù)變更后，在新產(chǎn)生的報表右鍵單擊，選擇【刷新】，可以更新數(shù)據(jù)。

提示：大家要轉(zhuǎn)載文章請注明來源，很多人把我原創(chuàng)的文章放在自己公眾號，請尊重人家的勞動成果！

如果你是新朋友，掃碼關注下方二維碼，便每天可以和小菜一起學習，一起提升技能！當然大家也可以技巧分享，學習更多辦公技巧哦！

每天一起學習，一起進步。