Excel高效絕技之三——數據提取與整合

2020-12-14 騰訊網

問題背景：（接上集）上回說到，小三老闆看小三處理數據靈活運用各種工具，速度奇快，效率奇高，也想跟小三學點技術，以備不時之需。這不，一大早的小三還沒到辦公室，老闆就想好了難題要再考考小三的水平，順便學個一招半式。

老闆拿出了在網頁上複製的數據（表1、表2）來要小三提取出來做成統計表格（表3）。

表1

表2

表3

小三本來還緊張，生怕完成不了，當著老闆的面也不好百度。一看這個要求，心裡竊喜，太好辦了。用Excel的OFFSET函數和ROW函數就解決了。

Offset函數：Offset是Excel中的函數，在Excel中，OFFSET函數的功能為以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區域。並可以指定返回的行數或列數。

語法：OFFSET(reference, rows, cols, [height], [width])

OFFSET 函數語法具有下列參數：

Reference：必需。要作為偏移基準的參照。 引用必須引用單元格或相鄰單元格區域。否則, OFFSET 返回 #VALUE! 。

Rows：必需。需要左上角單元格引用的向上或向下行數。 使用 5 作為 rows 參數，可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數（這意味著在起始引用的下方）或負數（這意味著在起始引用的上方）。

Cols：必需。需要結果的左上角單元格引用的從左到右的列數。 使用 5 作為 cols 參數，可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。

高度：可選。需要返回的引用的行高。 Height 必須為正數。

寬度：可選。需要返回的引用的列寬。 Width 必須為正數。

定義不太好理解，直接上實例。

步驟一：檢查數據源表的格式是否統一。把數據源的表做成格式統一的表格，每一個車型的數據區域都是8格，每8行一個循環，格式統一才能找到規律，機器是需要按規律才能正確找到對用數據。

步驟二：編寫公式（引用）提取數據。

代碼釋義：

1、序號列第一格E2=OFFSET(B$1,(ROW(B1)-1)*8,)

提取的第一列是序號，序號的第一個值在B1單元格，所以從B$1開始，之所以絕對定位，是因為所有的序號都要參照B1的位置計算。(ROW(B1)-1)是提取行號，序號第一個理應提取B1，而B1是第一行，因此偏移0行，為(ROW(B1)-1)=1-1，*8是因為左側源數據以8行一循環，偏移8行就是下一個序號的單元格位置。E2單元格的值就是第一個序號，是B1的值，為1，公式意思是「引用(B$1,(B1的行號-1)*8,)」等於offset(B$1,(1-1)*8,)，也等於offset(B$1,0,),就是B1。

因為不需要列偏移，所以*8之後逗號為空。

2、序號列第二格公式自動填充為E3=OFFSET(B$1,(ROW(B2)-1)*8,)

還是從B$1開始計算，絕對應用B1。(ROW(B2)-1)*8提取第二個序號的行號，為(B2的行號-1)*8=(2-1)*8=8，參照B$1往下偏移8行，正好是第9行，就是第二個序號，取值為2。所以，公式的邏輯就是為了構造公差為8的等差數列，而這個數列的值就是應提取序號的行號。而之所用ROW(B1)、ROW(B2)取單元格行號進行計算，而不用常數，是因為用常數，公式填充無法遞增。而ROW(B1)、ROW(B2)……填充公式剛好遞增1，便於構造出8倍遞增的序號行號，用ROW(A1)、ROW(A2)……也是可以的。

3、車型列第一格公式則為F2= =OFFSET(B$2,(ROW(B1)-1)*8,)

因為源數據第一個車型的單元格是B2，那麼就從B$2開始為參照，取到的行號2、10、18剛好就是車型單元格的行號。ROW函數取行號還是一樣，構造公差為1的等差數列，然後乘以8，以8的倍數偏移，因此，不管是繼續用(ROW(B1)-1)*8開始，還是和B$2 同步，用(ROW(B2)-2)*8開始，意義一樣。

4、後面的列也同樣的道理，只是在B列的基準參照起點依次往後推一格即可。

相關焦點

  • Excel高效絕技之一——巧用排序比對數據
    這時候，如果數據不多，只有幾十條數據，手工複製粘貼也花費不了多少時間，如果數據太多，有幾百幾千條甚至更多的數據，一般會考慮其他工具來實現，比如通過資料庫等等。但是數據如果就一百多條或者200條左右，而且有很大程度的相似的列，手工對比很繁瑣，做成資料庫的方式又顯得小題大做，就可以通過excel排序來實現，先完成兩表數據大範圍的匹配，然後修正少數錯位的、差異的數據就好了。
  • Excel如何通過函數進行數據提取？
    Excel函數的提取分為三部分，左側數據提取、中間數據提取和右側數據的提取，一起來看看吧！一、Excel如何通過函數進行左側數據提取？excel中的函數有非常強大的作用，下面小編來教大家如何使用函數對左側的數據進行提取吧。1.我們打開excel表格，想要對左邊六位數字進行提取出來。
  • Excel如何通過篩選進行數據的提取？
    excel如何通過篩選進行數據的提取？數據提取可以通過數據裡面的高級選項進行提取出來。下面就來給大家演示一下操作。1.首先我們打開excel表格，點擊數據，點擊高級按鈕。5.這個時候就會將A列和B類列之間相同的數據提取出來了。
  • excel數據提取技巧:從混合文本中提取數字的萬能公式
    有沒有能把任何文本中包含的所有數字都提取出來的公式？當然是有的，今天就給大家帶來提取數字的萬能公式，不管數字在文本中的位置是否有規律，不管文本中數字有多少，它都能把數字提取出來。趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。在上一篇文章中，小花講解了通過觀察混合文本特徵，設置特定公式，完成數據提取的三種情景。
  • excel數據處理技巧:對任意數字進行提取的方法匯總
    今天我們要給大家分享5種不用函數公式的Excel數字提取技巧。不管是從有規律的文本、還是沒有規律的文本中提取手機號、金額、尺寸等數據，都可以用兩端對齊法、快速填充法、Power Query法、Word替換法等5種方法進行提取，特別適合Excel小白使用。趕緊來看看具體操作吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • Excel函數:提取數據透視表數據的方法
    在遇到複雜數據需要做統計分析的時候，我們會經常使用到excel的一個絕密武器，那就是數據透視表。如果在做完數據透視表之後，需要單獨提取數據透視表中的其中的單個數據應該怎麼辦呢？今天，小編就給大家分享這樣一個函數：函數功能：返回存儲在數據透視表中的數據，可以在數據透視表中檢索匯總數據函數參數：=getpivotdata(Data_field,Pivot_table,[field1,item1, field2,item2……)參數詳解：Date_field：必需。
  • 秒懂快速處理excel數據絕技之按鈕操作法
    Excel表格使我們辦公常用的軟體之一，各種報表數據通過Excel表格能夠高效快速的顯示出來，同時我們可以通過excel自帶的VBA宏程式語言對一些重複的操作進行快速處理，既提高了處理速度，又不易出錯。但是，如果我們每次此運行VBA程序的時候都需要進入編譯界面的話，這樣會大大的降低我們處理的速度，特別是當我們編寫的VBA程序不止一個時，會浪費很多的時間。
  • Excel如何用函數提取長短不一的數據？
    如果在excel表格中的數據是長短不一的，如何提取出我們需要的數據呢？如何批量提取數據，一起來看看吧。1.首先我們要將銷售員中的店名和人名都提取出來。3.可以看到數據有一個共同的特點，就是都有一個店，我們輸入雙引號，輸入店。
  • excel數據提取:查找批量訂單中的手機號碼
    如何在excel中快速的提取手機號碼，已經是一個老生常談的話題了。在CTRL+E問世後，關於提取數據的問題都變得簡單了很多，不得不承認，它的確是一個相當優秀的功能。但是它並不是萬能的，仍然有一些提取數據的問題需要公式才能解決。
  • excel數據提取：查找批量訂單中的手機號碼
    如何在excel中快速的提取手機號碼，已經是一個老生常談的話題了。在CTRL+E問世後，關於提取數據的問題都變得簡單了很多，不得不承認，它的確是一個相當優秀的功能。但是它並不是萬能的，仍然有一些提取數據的問題需要公式才能解決。
  • excel小知識第24期:excel表格中快速提取相同類型的數據
    每天進步一點點，大家好，歡迎收看excel小知識第24期：excel表格中快速提取相同類型的數據最近分享了許多的快捷鍵用法，不知道小夥伴們都學會了嗎？這些小的大家了解了後在自己的學習與工作當中沒有幫助到呢？
  • excel實用操作技巧展示,快速去除重複數據同時提取不重複數據
    我們在實際工作中，我們需要經常與各種各樣的數據打交道，當我們需要面對比較多的數據時，我們就需要使用excel表格來整理和分析，因為excel表格有很多實用的數據處理工具，這些工具可以幫我們解決數據處理問題，同時可以減輕我們的工作負擔，提高工作效率，下面我們就簡單講解一下快速去除重複數據同時提取不重複數據的相關內容
  • Excel如何通過函數提取中英文混合數據？
    Excel中中英文混合數據的提取，一般來說是使用快速填充的方法，這個比較快速，但是如果使用函數來提取的話，這個就有點小麻煩，不過小編慢慢來講解一下如何操作。1.我們打開excel表格要將商品名稱中的漢語和英語分開。
  • Excel的OFFSET函數實現數據快速提取與整合
    問題背景：小明老闆看小明處理數據靈活運用各種工具，速度奇快，效率奇高，也想跟小明學點技術，以備不時之需。這不，一大早的小明還沒到辦公室，老闆就想好了難題要再考考小明的水平，順便學個一招半式。老闆拿出了在網頁上複製的數據（圖1、表2）來要小明提取出來做成統計表格（表3）。
  • excel數據處理：快速提取多工作簿數據
    本文分享excel多個工作簿查詢數據提取匯總方法，使用到Power Query插件來完成Excel不同工作簿數據匯總.各個地區的銷售數據需要匯總，儘管工作簿模板一致，但是全國那麼多城市，工作簿也要逐一打開複製粘貼數據。工作簿容量有的大有的小，一個個打開要花費大量的時間。那有沒有什麼好方法可以不用打開工作簿直接提取數據呢？今天給大家介紹了兩種方法來實現。如圖，在桌面這個文件夾中舉例說明了五個城市的12個月的銷售數據。
  • Excel數字提取技巧:從混合文本中提取數字的方法
    前面我們分享了不用函數公式提取數字的5種方法。今天我們分享用簡單公式從混合文本中提取數字的方法。因為採用的公式很簡單，所以總體來說只適合數字在文本中的位置有一定規律的情況。如果想用公式提取沒有位置規律的數字，那就得看我們下一篇教程。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • Excel單元格裡既有文字又有數字,如何一鍵提取數字?
    如何一鍵提取數字？日常工作中，我們所面臨的數據往往紛雜凌亂，如何快速清理複雜的數據，令其清晰明了，符合我們分析的要求，是一項需要不斷探索精進的職場必備技能。對於單元格裡既有文字又有數字的情況，你還在傻傻地用複製、粘貼的方式將單元格裡的數字提取出來嗎？其實，用好一個快捷鍵，一鍵就能批量提取。
  • 提取不重複數據在Excel、SQL與Python中的處理方法
    村長今天跟大家簡單分享一下如何在Excel、SQL和Python中用不同的方法提取不重複值（數據去重）。
  • 利用ADO連接EXCEL,提取固定位置(行或者單元格)的數據
    大家好，我們繼續VBA資料庫解決方案的學習，今天講解第33講：利用ADO連接EXCEL,提取固定位置（行或者單元格）的數據。在上一講中我們學習了ADO連接EXCEL後，實現提取列數據的方法，但很多的時候，必要的數據並不是在整列中的，也有可能在行或者某單元格的固定位置，這個時候要如何處理呢？這個內容就是我今天要講解的內容。
  • Excel怎麼設置只提取指定行中的數據?
    Excel怎麼設置只提取指定行中的數據？有些時候我們需要從一個excel文件中的資料庫中提取指定的行或列中的數據。例如如圖示，是國內所有上市公司的行業統計。但是現在我們只需要其中部分上市公司的行業統計，我們怎麼辦呢，下面分享一個技巧，需要的朋友可以參考下有些時候我們需要從一個excel文件中的資料庫中提取指定的行或列中的數據。例如如圖示，是國內所有上市公司的行業統計。但是現在我們只需要其中部分上市公司的行業統計，我們怎麼辦呢，是一個個查找，然後複製嗎，當然不是。