競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

  競價人員必備的Excel函數詳解《SEM知識059期》

競價的關鍵在於成本控制,很多朋友經常會問我如何去做數據分析,也有不少朋友會問我有沒有數據分析類的表格能夠更方便有效的去做數據分析。

畢竟競價裡邊沒有多麼複雜的數據源,所以我個人主張從競價入手,而非數據分析,碰到競價中的問題,然後去找方法,去匯總融匯貫通。記得之前帶競價又一次告訴他做數據分析的時候,告訴他思路的時候,我問他之前這麼想過嗎,他說都想過,就是之前沒有找到一個合適的方法感覺無從下手,其實他缺少最後一步沒有做,沒有與分享他的問題,去尋找方法。今天小磊也不是講什麼深刻的分析方法,只是說入門,說思路,高手可以避道,謝謝。

首先,提起分析,不得不說的就是函數,函數讓數據的整合方便而高效,當然競價的數據分析也離不開數據的整合,如:從消費報表到關鍵詞的轉化報表、從關鍵詞著陸頁報表到消費報表,等等需要將2份或者以上報表匯總在一起的很多,之前就給群裡的朋友做過一次從搜尋詞消費數據與關鍵詞數據的合併,還有之前所做的關鍵詞標記等,下面我就引出第一個我們需要了解的函數:VLOOKUP(a,b,c,d)函數。

一、VLOOKUP函數介紹

vlookup函數有四個參數,我分別用a、b、c、d表示。

vlookup的作用

通過某一字段將另一個表格中的數據引入到當前表格。字段如果有朋友沒聽說過的話可以暫時理解為共同項。舉個很簡單的例子。我有2份信息表,第一份是某個班級的學生的信息,包括學生的姓名,學號,年齡,身高,民族…等數據,另外一份表格是學生的期末考試成績單,其中包括學生的學號,姓名,還有語文成績,數學成績等等。現在我需要得到一份既有學生信息(姓名,學號,年齡,身高,民族)等,還要有(語文成績,數學成績等考試成績的報表),大家應該認為這個很簡單。按照姓名一個個去找,然後寫進來就行了。或者有的學生就感覺不對了,我們應該按照學號去找,避免重名重姓的學生,很好。這塊我給大家說兩個知識點,這個姓名或者學好都可以理解為一個單獨的字段。通過兩個表格中共有的字段,將數據映射過來,這就是vlookup的簡單應用以及原理。

大家不要被四個參數abcd給嚇住,假設我們想把數學語文成績緊跟在第一張學生信息表的後邊兩列,下面我給大家通過上邊例子介紹vlookup的四個參數分別代表什麼。首先我說的一個參數a,如果我們排除重名重姓的情況通過姓名查找的話a就代表:「張三、李四、王五…」等,如果我們通過學號去找,那麼a就代表:’09001、09002、09003…’等,那b呢,b就代表你要去查找的區域,如($a$1:$d$100)區域,如下圖。a的字段必須在b區域的第一列,相當於在$a$1:$d$100區域的第一列也就是A列查找「張三」,第三個參數c就代表你要返回「張三」所在行的數據區域$a$1:$d$100的第幾列,大家應該可以清晰的看到,如果是語文成績的話應該返回第三列,c的值應該為「3」,如果查找數學成績則應該返回第四列,c的值就應該為「4」。最後一個參數d的意思是近似匹配或者完全匹配,我們查找姓名或者學號當然需要完全匹配,及d的值為「false’。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

四個參數已經給大家介紹清楚了,下面看看原始數據中如何使用。所以H3單元格中的公式應該是我紅框內的公式。最後,初學的朋友可以根據學號超找返回下數學語文成績。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

OK,關於上邊區域選擇的時候我是用了絕對引用的符號「$」,不明白的朋友可以自行百度「絕對引用’。vlookup函數就為大家簡單介紹到這裡。關於vlookup的實際中的應用大家可以參考一下文章。

vlookup函數為大家介紹完畢。

二、COUNTIF函數介紹

除了vlookup函數,COUNTIF也是不得不為大家介紹下常用的一個統計函數 countif(條件計數函數),這個函數在競價中最常用的功能就是計算某個關鍵詞的數量,計算某個關鍵詞產生的對話數等,例如,我們通過商務通數據想快速匯總出每個搜尋詞產生的對話多少條,每個著陸頁產生對話多少條等等,這中功能在數據透視表中其實也可以做到,後邊給大家附上數據透視表的學習動畫及學習地址。先給大家通過通俗的語言介紹什麼是countif函數。

一句話解釋:在一組數據中統計符合某一條件的值的數目。(某一條件可以是一個單元格如A5,也可以是一個條件)

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

1、統計消費大於等於500的關鍵詞有多少個。輸入公式=COUNTIF(B2:B10,’>=500′),結果為「3」。統計消費小於250元的關鍵詞,同理=COUNTIF(B2:B10,’

2、統計極佳對話的個數,輸入函數=COUNTIF(E2:E23,’極佳對話’),返回值「11」,表示E2:E23區域內極佳對話為11。

3、統計每個關鍵詞產生的對話數,這塊因為導出的每個關鍵詞都是產生對話的關鍵詞,我直接用countif計數,不用多條件去區分極佳和較好對話了。先復制一份關鍵詞刪除重復項,在H2處輸入函數=COUNTIF(D2:D23,G2),下拉填充,搞定。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

三、SUMIF函數入門

緊接著上邊countif給大家為大家介紹下條件求和函數吧。說起這個函數的作用。例如可以從關鍵詞報告中快速統計出來包括醫院的關鍵詞的消費總和。包括「治」的關鍵詞的消費總額,可以快速統計每個計劃的消費總和。這些功能也可以使用數據透視表進而簡化。但是入門還是個大家介紹些好。

一句話解釋:在一組數據中統計符合某一條件的值的和。(某一條件可以是一個單元格如A5,也可以是一個條件)基本同上,一個統計數目一個求和。

簡單說明,sumif有三個參數,比sumif多一個參數。第一個參數和countif相同,是區域,第二個參數也和countif相同表示條件,第三個參數表示匯總值的區域。那下邊第一個問題來說,參數一就是A2:A10區域,第二個參數就是計劃名稱,第三個參數因為我們要匯總消費,所以第三個參數就是D2:D10,這塊大家記得回顧我前面說的公式裡邊的相對引用和絕對引用。$$一定要多使用。

1、統計計劃消費的總和。

1)、復制計劃名稱,刪除重復項。

2)、在G2處輸入公式=SUMIF(A:A,F2,D:D)然後下拉填充即可看到下圖效果。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

2、統計關鍵詞中包括「醫院」的詞的消費總和。

輸入函數=SUMIF(C2:C10,’*醫院*’,D2:D10)

第二個參數我做下解釋,「*醫院*」大家都知道「*」代替任意字符。所以,我這塊表示醫院前邊有任意字符或後邊有任意字符,即只要關鍵詞中包含醫院則符合該條件。

3、統計消費高於450元的關鍵詞的消費總額。

這個比較簡單了。輸入公式=SUMIF(D2:D10,’>450′,D2:D10)即可。我就不做解釋了。

四、SUBSTITUTE函數介紹

這個函數看著比較長,參數也稍微比較多,大家不要被嚇著,其實這個就是很簡單的替換函數有時候對我們競價還是很有幫助的,這塊我就提一個例子。希望大家能夠吸收,然後據為己有。會是什麼呢?競價中大家最頭疼的可能莫過於寫創意了。很多人發現我一個一個復制也很複雜,每次復制完了還需要去修改通配符裡邊的關鍵詞。是不是很麻煩呢。

沒錯,給大家介紹的這個函數就可以做到快速批量修改通配符裡的關鍵詞。下面給大家說說思路還有操作方法吧。

1、就是你將創意分好類,這個分類就是醫院類的創意,治療類的創意,症狀類的創意,可以是現成偷來的也可以是自己針對病種寫的。這個是怕你到時候把弄得創意四不像。這個事先自己將創意復制進每個單元,不需要修改通配符裡的關鍵詞,一會統一大批量改。

2、每個單元裡邊的核心詞也就是你將要在通配符中展現的關鍵詞需要整理出來。

3、開始操作。

1)從帳戶中將需要替換的創意全部復制到excel中。

2)將通配符裡的關鍵詞替換為「△」(可以是任意一個字符,一會將他替換成該單元的核心關鍵詞)方法大家知道嗎?替換{*}為{△}

3)通過VLOOKUP函數將單元對應的核心關鍵詞匹配過來。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

4)在N1處輸入公式「=SUBSTITUTE(A2,’△’,$M$2)」,然後向右拉兩格,然後向下填充即可看到替換後的創意出現在你的面前。

競價人員必備的Excel函數詳解《SEM知識059期》 | 艾奇學院

?版權聲明本文由艾奇學院原創,未經授權嚴禁轉載!