大數據：向微軟Excel認證專家學習Excel（01

07-07

Excel01-lookup函數實現數據分層

今天給大家分享lookup函數的典型應用。比如說我要對網站客戶進行數據分層。

案例如下：

我希望對目前的顧客分成3類，按交易次數分：

1-2次：新客

3-6次：次新客

7次及以上：老客

客戶數據上萬條，肯定不能用眼睛，那樣數據還沒出來，你眼睛已經瞳孔放大了。

下面，我就用lookup來實現數據分層的效果，高效準確。

Lookup常見語法：（查找值，查找區域），用處也是查找。他屬於模糊匹配，返回查找值所對應區域的最大值。此用法必須查找區域升序。

是不是感覺很像vlookup，但參數更少些。

下面我們在表格里做輔助列如下：

>7這裡用100代替一般也足夠了，當然最好可以用max 那列的最大值更佳。

然後輸入公式：E2=LOOKUP(B2,$H$2:$J$5).

查找值是B2,查找區域也就是整個輔助列的區域，包括J列。這樣返回的結果就是J列里的某項。

比如說B2=12，12落在>7,所以結果是老客。

簡單理解可以這樣：12 在輔助列區域中最大可以算到7-100,所以返回>7的老客。

B3=4,是最多算到3-6，不能算>7，所以他只能返回次新客。

本篇結束。

Excel02-日期時間函數實現日期時間快速提取

今天給大家分享日期時間函數的典型應用。我如何把帶有日期+小時分秒的單元格里的日期和時間分別提取。

案例如下：

我希望對分離出日期和時間

數據上萬條，肯定不能用眼睛，那樣數據還沒出來，你眼睛已經瞳孔放大了。

下面，我就用函數來完美解決問題。

方法1：分列，這是最簡單的，就不談了，如果不會就自己去補基礎。免費或付費，自己學會。免費省錢，付費省錢。就看你缺哪個。說個額外話，前輩教導我，用錢幫你做事，用人幫你做事，用工具幫你做事。

方法2：我們今天的重點。為什麼方法1就可以，還需要方法2呢。分列和函數改變數據是不同的應用場景，當你接觸足夠多的報表數據處理，你就懂了。

B2= DATE(YEAR(A2),MONTH(A2),DAY(A2))

用到最基本的4個時間函數，在我的02 函數系列教程里都有講到這幾個函數，分別就是提取年月日。

追加一步，單元格格式改為日期

C2 =TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

和上面類似進行提取時分秒，追加一步，單元格格式改為時間.函數也就是hour，minute，second。也就是英語這3個詞。英語好些，學函數是很容易的。當然如果不好，也不是不能學會。沒有學不會的函數，只有不會教的老師，或學生自己瞎研究費事。

本篇結束。

Excel003-和被標記的行值相同則全部標記

今天給大家分享一個高難度的Excel實際應用，後面大家就會理解為什麼我說是高難度了。問題來自我們千人付費會員群，我已經註冊到公司，所以群改名為資越科技教程會員群。本群都是起碼購買教程，消費滿50起的會員，嚴格控制進入門檻，但離開自願，隨時。

回到正題：

問題是這樣的，簡化下問題：需要對標記和被標記的行值相同則全部標記。

準確描述問題是這樣的：比如B4被標記為4，那麼和A4一樣等於2的值，都需要被標記。

問題描述清楚了，然後怎麼實現呢？

下面介紹方法1和2。

1 、在於思路巧妙，我寫的。

2、在於函數功底很牛，我一個函數大神朋友，都出書好幾本了，也有個淘寶店鋪，專門解決疑難。

方法1：我是這樣想的。可以藉助vlookup，查找值，那值一樣的話，自然就能把一樣的都標記出來。

所以我多了輔助橙色區域。

因為標記要刪除B列數據就2條，所以輔助區域也就2行。如果數據更多，總之去源數據篩選B列不為空的，粘貼到輔助區域就行。

最終C2=IF(ISNUMBER(IFERROR(VLOOKUP(A2,$G$3:$H$4,2,0),""))=TRUE,"刪除","")。

Iferror是返回如果不存在則怎麼辦，isnumber判斷是否為數值，因為B列都是數值。

方法2：函數大神解法。

D2=IFERROR(IF(A2=LOOKUP(1,0/(B$2:B2<>""),A$2:A2),"刪除",""),""),無輔助列，一步到位。理解難度高，運用了lookup所謂的二分法什麼的，我暫時看不懂，也沒興趣如此深入研究函數。

最後說一句，Excel學習主要還是解決實際問題，而不是糾結哪個函數快，哪個函數短。關鍵是思路清晰，邏輯清楚。

本篇結束。

Excel004-VBA智能提取超級鏈接文本下的鏈接

今天來分享下如何提取超級鏈接文本下的鏈接。比如說：

百度我們要把www.baidu.com提取出來放在B列。

如何實現呢?函數肯定是不能實現的，暫時沒提取超級鏈接文本的函數，以後可能會有。

VBA解決方案如下：

插入VBA代碼步驟如下：按ALT+F11, 進入VBE編輯器，點插入模塊，輸入以下代碼：

Sub 提取鏈接()

Dim HL As Hyperlink

For Each HL In ActiveSheet.Hyperlinks

HL.Range.Offset(0, 1).Value = HL.Address『就是說把鏈接放在非單獨鏈接的後面一列。

End Sub

以下代碼的作用就是把文本下的鏈接提取，並放在後面1列。不熟悉VBA代碼如何輸入，請百度 VBA基礎，基本也就了解了。

本篇結束。

Excel005-多條件求和計算收款

今天來分享一個財務收款計算應用，問題是這樣的。

比如說我要計算各門店已收款是多少，未收款是多少。類似的應用場景是很廣泛的。源數據如下：

數據是模擬的。

結果是這樣的：顏色區域即為結果。

如何實現從表1到表2呢，手動肯定是不可能的，一步到位，估計也比較難。

問題其實不難，關鍵是先理解怎麼樣的表1記錄是已回。已回就是已經回款，那就是說回款日期不為空。

因此第一步是對錶1的款賬記錄進行分類，哪些是已經，哪些是未回款。

=IF(LEN(D2)=0,"未回","已回")

Len 代表字元的長度，if就是判斷。

這步搞定後，後面就是多條件求和了。

對於表2中的溫州已回的匯總其實就是條件1 溫州，條件2 已回，在表1進行這2個條件的求和。

最終公式如下：

=SUMIFS($C$2:$C$36,$A$2:$A$36,I2,$F$2:$F$36,J$1)

解決這個問題的關鍵是首先輔助列把表1記錄進行已回和未回分類，然後就是sumifs函數進行多條件匯總。涉及到的函數都體現在本人的原創函數實戰教程里。

本篇結束。

作者Sharpen，微軟Excel認證專家。來源數據網友博客，感謝！