當你遇到圖中案例這樣的工作情況,需要將一列有規律的資料變成兩列展示,你會怎麼來實現呢?
現在,我們來講解2個解決方法。
一、用顏色篩選法
首先,我們將所有電話的單元格用顏色填充。
由於全部是隔行為電話,因此可以用條件格式裡的公式來選取這些單元格。
選中B列,點選“選單欄-開始-條件格式-新建規則”,在公示欄裡輸入公式
=(mod(row(a1),2)=1)
mod函式是求餘數,即將所有B列單元格的行數(B1單元格行號為1、B2單元格行號為2……)除以2,餘數為1的單元格填充黃色,也就是所有奇數行填充黃色。
設定完之後的效果如圖,包括B1單元格也由藍色填充變為了黃色填充。
有了顏色填充之後,我們再用篩選功能裡的顏色篩選就可以了。
點選“選單欄-開始-排序和篩選-篩選”,B1單元格就多了篩選按鈕。
點選篩選按鈕,選擇“按顏色篩選”,裡面有“黃色填充、無填充”兩個選項,先選擇無填充,就可以將姓名先篩選出來。
篩選結果出來後,複製這些姓名內容,在D1單元格點選右鍵,選擇貼上選項裡的第2個,也就是有123圖示的,代表著只貼上值,去掉所有格式。
這樣,姓名首先被篩選複製出來了。
同理,我們再去篩選黃色填充,複製到E列,電話也被篩選複製出來了。
這個時候,會發現資料少了一些,不完整?這是正常的。因為篩選的時候有些行被隱藏了,不用擔心。
點選篩選按鈕,選擇“從姓名電話中清除篩選”,去除所有篩選效果。
這個時候,D列、E列的資料就全部完整展示出來了。
接下來,點選“選單欄-開始-條件格式-清除規則-清除整個工作表規則”,將B列恢復到初始狀態。
最終,再對D列、E列調整下格式,所有資料就變成了兩列展示效果。
二、用函式公式法
第一種方法操作的步驟比較多,要想一步到位,就用函式公式最方便。
選中D列區域(D2開始,和B列一樣長度,或者超出B列長度,不能少於B列長度),在編輯欄裡輸入陣列公式:
=IFERROR(INDEX(B:B,SMALL(IF(MOD(ROW(),2)=0,ROW()),ROW(A1:A50))),“”)
公式第1部分
IF(MOD(ROW(),2)=0,ROW())
表示用B列單元格的行號除以2,如果餘數為0(即剛好整除,也就是所有偶數行),返回行號;如果餘數不為0(即所有奇數行),返回預設錯誤值。
公式第2部分
SMALL(
公式第1部分
,ROW(A1:A50))
表示用small函式將剛才返回的行號全部排到前面來,錯誤值放到後面去。後面的row(a1:a50)代表總共對50個數進行取值,這個50一定要大於B列所選單元格的數量,否則資料顯示不完整。
公式第3部分
INDEX(B:B,公式第2部分)
表示用index函式在B列,將這樣行號對應單元格的值取出來,也就是取出所有偶數行裡的內容,即所有姓名。
公式第4部分
=IFERROR(
公式第3部分
,“”)
表示用ifferror函式將錯誤值部分全部顯示為空格。
由於是陣列公式,要用Ctrl+Shift+Enter三鍵確認,確認後D列就直接顯示全部的姓名了。
有了D列的姓名,再用offset函式進行獲取對應的電話即可。
E2單元格公式為
=IFERROR(OFFSET($B$1,MATCH(D2,B:B,0),0),“”)
先用match函式在B列裡找到姓名對應的位置,然後用offset函式去取出對應的電話單元格,再用iferror函式過濾掉錯誤值。
將公式下拉填充即可。
另外,如果大家掌握了INDIRECT函式,會發現用INDIRECT函式最簡單,直接一個公式下拉就可以了。
D2單元格公式為
=IF(INDIRECT(“b”&ROW(B1)*2)=0,“”,INDIRECT(“b”&ROW(B1)*2))
公式第1部分
INDIRECT(“b”&ROW(B1)*2)
用indirect函式直接取B列所有偶數行的內容。
公式第2部分
=IF(
公式第1部分
=0,“”,
公式第1部分)
用if做個判斷,如果結果為0,就顯示空單元格;結果不為0,就正常顯示結果。這是因為多餘的單元格為顯示0,我們用if函式來過濾掉這些0的值。
然後下拉填充所有D列。
同理,E2單元格公式為:
=IF(INDIRECT(“b”&ROW(B1)*2+1)=0,“”,INDIRECT(“b”&ROW(B1)*2+1))
只要在公式裡面+1,就變為姓名下面一行了,也就是對應的電話。
另外,過濾掉excel單元格0的顯示,還可以直接系統設定裡禁用。
點選“選單欄-檔案-選項-高階”,“此工作表的工作選項”裡,預設是勾選了“在具有零值的單元格中顯示零”,去掉這個勾選,剛才就不需要用if函式來判斷0進行過濾了。
總結:
這個案例還有其他的方法也可以實現,我們這裡主要是給大家提供思路,拓展excel綜合應用的辦法。
更多相關內容,可以檢視專欄課程《Excel天天訓練營》以及免費圈子“職場交流圈”。
(此處已新增圈子卡片,請到今日頭條客戶端檢視)