---
title: "使用EXCEL做出動態關鍵字報告"
description: "這篇文章要教如何使用Excel做出一份可以輸入一個或多個資料後，自動進行篩選的Excel表格。"
canonical: "https://www.darrenhuang.com/dynamic-keyword-performance-excel-report.html"
markdown: "https://www.darrenhuang.com/dynamic-keyword-performance-excel-report.md"
locale: "zh-hant"
language: "zh-Hant"
translationKey: "post:dynamic-keyword-performance-excel-report"
type: article
published: "2017-02-04T00:00:00.000Z"
modified: "2023-06-26T00:00:00.000Z"
---

> 此 Markdown 版本由正式 HTML 內容自動產生。
> 引用時請使用 [canonical page](https://www.darrenhuang.com/dynamic-keyword-performance-excel-report.html)。

數位引擎文章

# 使用EXCEL做出動態關鍵字報告

這篇文章要教如何使用Excel做出一份可以輸入一個或多個資料後，自動進行篩選的Excel表格。

Darren Huang·2017年2月4日·約 6 分鐘更新於 2023年6月26日

本文目錄12 個段落

1.  [步驟一: 原始資料](#section-步驟一-原始資料)
2.  [步驟二: 確認篩選要素](#section-步驟二-確認篩選要素)
3.  [步驟三: 建立輔助欄I](#section-步驟三-建立輔助欄i)
4.  [步驟四: 建立輔助欄II\*](#section-步驟四-建立輔助欄ii)
5.  [步驟五: 建立輔助欄III](#section-步驟五-建立輔助欄iii)
6.  [步驟六: 動態表格!](#section-步驟六-動態表格)
7.  [步驟六: 加入更多篩選要素](#section-步驟六-加入更多篩選要素)
8.  [AND](#section-and)
9.  [OR](#section-or)
10.  [Mix](#section-mix)
11.  [步驟七: 簡單優化](#section-步驟七-簡單優化)
12.  [總結](#section-總結)

**本文目錄**

1.  [步驟一: 原始資料](#section-步驟一-原始資料)
2.  [步驟二: 確認篩選要素](#section-步驟二-確認篩選要素)
3.  [步驟三: 建立輔助欄I](#section-步驟三-建立輔助欄i)
4.  [步驟四: 建立輔助欄II\*](#section-步驟四-建立輔助欄ii)
5.  [步驟五: 建立輔助欄III](#section-步驟五-建立輔助欄iii)
6.  [步驟六: 動態表格!](#section-步驟六-動態表格)
7.  [步驟六: 加入更多篩選要素](#section-步驟六-加入更多篩選要素)
8.  [AND](#section-and)
9.  [OR](#section-or)
10.  [Mix](#section-mix)
11.  [步驟七: 簡單優化](#section-步驟七-簡單優化)
12.  [總結](#section-總結)

這篇文章要教如何使用Excel做出一份可以輸入一個或多個資料後，自動進行篩選的Excel表格。

我使用的最初目的是要幫公司的一個PPC帳戶進行2016年度的關鍵字成效分析，例如:

-   Conversion 大於 3，CPL小於$30的關鍵字有哪些
-   CPL大於$100，Avg. Position在3以上的關鍵字有哪些
-   Conversion 介於1~3之間，CPL大於$150，Avg. Position在3以下的有那些

這份檔案會根據上面輸入的數值，自動列出符合條件的結果，如下圖所示:

[![dynamic keyword report preview](./wp-content/uploads/2017/02/dynamic-keyword-report-preview3.gif)](./wp-content/uploads/2017/02/dynamic-keyword-report-preview3.gif)

雖然這次的例子是應用在關鍵字廣告的分析上，但熟悉了方法以後，要應用在其他地方或是加入更多的篩選條件不會是難事! 下面一步步帶大家實做一遍。[檔案這邊下載](https://drive.google.com/file/d/0B1BdomfzSCERUlQ3NkdFMDdFQTQ/view?usp=sharing)!

## 步驟一: 原始資料

[![原始資料](./wp-content/uploads/2017/01/Raw-Data.jpg)](./wp-content/uploads/2017/01/Raw-Data.jpg)

一開始的資料長得很普通，就是一般從Adwords或是其他工具export出來的樣子。因為工作的關係，我把真實的keyword, ad group, campaign都用其他東西代替掉~

(客戶是大陸公司…所以用了簡體字，請多見諒!)

## 步驟二: 確認篩選要素

這裡要想想怎麼切割數據會對分析有幫助，有人想根據CTR、有人想根據時間，沒有絕對的對或錯。

這裡我選擇用網頁轉化、CPL、和排名作為我篩選的根據!

[![決定篩選要素](./wp-content/uploads/2017/01/determine-filter-criteria.jpg)](./wp-content/uploads/2017/01/determine-filter-criteria.jpg)

## 步驟三: 建立輔助欄I

要達到想要的結果會需要3個輔助欄，第一個輔助欄是給每一個關鍵字一個數字編號，這裡用的公式是:

`=ROWS($M$9:M9)`

[![輔助欄1](./wp-content/uploads/2017/01/helper-column-1.png)](./wp-content/uploads/2017/01/helper-column-1.png)

如果好奇為什麼不在M8輸入1，M9輸入2然後一路往下拉將每個關鍵字編號而要用這個看起來麻煩的公式，其實是有原因的! 因為假使未來我們要在原始檔裡新增幾個row，上述的方法將會出現問題。

## 步驟四: 建立輔助欄II\*

輔助欄II是我們的條件欄，也是最重要的一欄。

這裡先建立一個簡單的條件式，確認可以用以後會再回頭來把條件的地方變多層次。

[![輔助欄2](./wp-content/uploads/2017/01/helper-column-2-1.png)](./wp-content/uploads/2017/01/helper-column-2-1.png)

這裡N9的公式是:

`=IF(J9>=$S$4,M9,"")`

代表的是，如果網頁轉化那一欄(J)出現的數值大於1(S4)，就回傳輔助欄I (M)的數值，否則出現空白("")。

[![step 2 - helper column ](./wp-content/uploads/2017/01/helper-column-2-2.png)](./wp-content/uploads/2017/01/helper-column-2-2.png)

上圖可以看到當網頁轉化大於或等於我們設定的標準以後，輔助欄II就會回傳該關鍵字的編號(輔助欄I)，如果S4改成2，上面就剩下編號1292的關鍵字出現在輔助欄II了。

## 步驟五: 建立輔助欄III

輔助欄3號的目的是將輔助欄2中的空白格都去除，只留下符合條件(網頁轉化大於1)的關鍵字編號，這裡的公式是:

`=IFERROR(SMALL($N$9:$N$3449,M9),"")`

這個公式的白話就是: 在O9選出N欄裡第一小的，O10選出第二小的，O11選出第三....等等，選到N欄裡最大的(最後一個符合條件的)之後，回傳空白("")。

[![輔助欄3](./wp-content/uploads/2017/01/helper-column-3.png)](./wp-content/uploads/2017/01/helper-column-3.png)

到這邊有基本Excel認知的可能已經猜出接下來的步驟了! 這三個輔助欄的動作都是為了抓出所有符合條件的關鍵字所在的列位(Row)，關鍵和INDEX+MATCH一樣，抓出他們在第幾列的目的就是為了接下來動態表格可以使用INDEX來索引資料。

## 步驟六: 動態表格!

這個步驟要建立最後要呈現的表格，使用的公式是index，把原始資料當成一個大array，利用HELPER3一條一條列出符合條件的關鍵字。

[![ dynamic table step 1](./wp-content/uploads/2017/02/dynamic-table-step-1.png)](./wp-content/uploads/2017/02/dynamic-table-step-1.png)

`=INDEX($B$9:$O$3449,$O9,COLUMNS($R$9:R9))`

1.  $B$9:$O$3449 是整個原始資料的範圍，包括三個輔助欄
2.  $O9 輔助欄三號的數字，也就是在這個範圍內，符合我們條件的關鍵字是在第幾列(row)。這裡把O鎖住，如此一來當這個公式往右拉還是只會看O的數字，而往下拉可以對到下一個符合條件的關鍵字...依此類推。
3.  COLUMNS($R$9:R9) 和上面輔助欄I的觀念一樣，將公式往右拉後，S欄會出現Ad Group，T出現Campaign，U出現Impression...等等

[![dynamic table step 2](./wp-content/uploads/2017/02/dynamic-table-step-2.png)](./wp-content/uploads/2017/02/dynamic-table-step-2.png)

最後，因為index如果沒有找到東西的話(HELPER3要看的數字是空白)會回傳很醜的#VALUE!，所以我們用一個iferror包裝，當index沒找到時出現““，最後公式得到:

`=IFERROR(INDEX($B$9:$O$3449,$O9,COLUMNS($R$9:R9)),"")`

## 步驟六: 加入更多篩選要素

到這裡我們的動態表單已經完成了，只是目前只能判斷一個"條件"，只能選擇網頁轉化次數大於或等於S4裡的數字，並不是非常的實用，所以要回到[輔助欄II](#helper2)的地方，將那裏的公式稍微加強，讓它可以一次判斷多個條件。

`=IF(J9>=$S$4,M9,"")`

上面的公式裡只有一個條件: J9是否大於或等於S4。要增加判斷的條件，在excel裡不外乎使用AND和OR這兩個公式:

### AND

[![more criteria 1](./wp-content/uploads/2017/02/more-criteria-1.png)](./wp-content/uploads/2017/02/more-criteria-1.png)

假設我們要篩選出網頁轉化大於等於2，且小於4的結果，輔助列II的公式就變成以下，這樣只有在J9>=$S$4和J9<$S$5兩個條件都為true時，這個IF公式才會回傳M9，否則出現空白(“”)。

`=IF(AND(J9>=$S$4,J9<$S$5),M9,"")`

### OR

上面這個公式會有一個小bug，當S5沒有輸入任何數值的時候，J欄裡的數會永遠大於空格("")。假設有個關鍵字有3個轉化，S4輸入2，S5留白，AND(3>=S4,3<S5)得出的結果會是FALSE，因為在原始資料裡轉化那一欄(J)一定是個數字(0,1,2,3,4....)，而數字跟空格比的話，永遠會比較大。

要避免這個結果，可以將上面那個公式再複雜一點，變成:

`=IF(AND(J9>=$S$4,OR(J9<$S$5,$S$5=""),M9,"")`

如此一來，在OR(J9<$S$5,$S$5="")這個條件裡，S5留白會回傳TRUE，因為OR裡面的兩個條件只要有一個為真($S$5="")，就為真。

### Mix

會了AND和OR我們要加入多少比較元素就可以加入多少，文章一開始我選定了3個比較的東西，最後的輔助欄II變成以下

[![more criteria 2](./wp-content/uploads/2017/02/more-criteria-2.jpg)](./wp-content/uploads/2017/02/more-criteria-2.jpg)

`=IF(AND(J9>=$S$4,OR(J9<$S$5,$S$5=""),K9>=$T$4,OR(K9<$T$5,$T$5=""),L9>$U$4,OR(L9<$U$5,$U$5="")),M9,"")`

看起來有點複雜，但是這串不過是AND和OR的結合而已，最終回傳一個TRUE或是FALSE。

## 步驟七: 簡單優化

到這邊告一段落咧，剩下的就只是一些簡單的優化跟美觀，可以把A~O欄都藏起來，因為並不需要看到他們。

另外可以在上方加入一個“數量”，讓使用者清楚看到符合所選條件的關鍵字有幾個，這裡用的公式是:

`=COUNTIF(R9:R3449,"?*")`

[![last step](./wp-content/uploads/2017/02/last-step.jpg)](./wp-content/uploads/2017/02/last-step.jpg)

## 總結

不敢說這個是絕對的最好的方法，但是是自己工作上的一點小心得，分享給大家，希望某天能派上用場。如果有疑問、建議、或是指教，歡迎下面留言或是寄信到我的信箱，感謝閱讀!

**分類**

-   [Uncategorized](/categories/uncategorized.html)

**標籤**

-   [Uncategorized](/tags/uncategorized.html)

Keep exploring

## 繼續閱讀

[全部文章 →](/articles.html)

### 相關文章

2017年3月28日

#### [利用Screaming Frog找出網站中的404頁面](/identify-broken-links-with-screaming-frog.html)

[閱讀文章 →](/identify-broken-links-with-screaming-frog.html)

2016年6月19日

#### [SEO| Bookmarklets小書籤 - SEO的實用工具](/seo-efficient-tool-bookmarklets.html)

[閱讀文章 →](/seo-efficient-tool-bookmarklets.html)

2016年5月18日

#### [SEO | Anchor Text 錨點文字的大小事](/seo-anchor-text-and-google-bomb.html)

[閱讀文章 →](/seo-anchor-text-and-google-bomb.html)

×

開啟原圖 ↗