在 SEO 工作，我總是發(fā)現(xiàn)自己在仔細(xì)研究數(shù)據(jù)并尋找加快分析過(guò)程的方法。分析數(shù)據(jù)通常是乏味、頭腦麻木和無(wú)聊的工作，因此任何可以加快在大海撈針中找到針頭的方法幾乎總是一個(gè)好主意。

如果你花時(shí)間學(xué)習(xí)這些EXCEL公式，我保證這將是值得的——你可以輕松地處理 Excel 中的數(shù)千（或更多）行，廢話不多說(shuō)，給大家介紹一下：

公式1

=if(isnumber(search("string 1", [beginning cell])),"Category 1", if(isnumber(search("string 2", [beginning cell])),"Category 2", "Other" )

如果我已經(jīng)讓你感到困惑，我深表歉意。我將更深入地研究該公式，解釋其含義并提供 3 個(gè)不同的用例，說(shuō)明它如何幫助你加快工作速度。

用例 1：關(guān)鍵字研究

當(dāng)做關(guān)鍵字研究時(shí)，可能你有數(shù)千行的數(shù)據(jù)需要研究搜索量，嘗試將相似的關(guān)鍵字放在一起以查看相似性，進(jìn)行關(guān)鍵詞分類是SEO的基本工作，那么如何快速對(duì)關(guān)鍵詞進(jìn)行分類呢？

如果你想分解整個(gè)列表（681 行數(shù)據(jù)，顯然都沒(méi)有顯示在屏幕截圖中）以找出有多少查詢包含“supplement”這個(gè)詞，或者你想知道有多少包含“muscle”。在單元格 C2 中，我們將鍵入公式：

=if(isnumber(search("supplement",A2)),"Supplement", if(isnumber(search("muscle",A2)),"Muscle","Other"))

翻譯后，此公式表示：搜索單元格 A2，如果找到“supplement”，則返回類別“supplement”。如果未找到“supplement”，則查找“muscle”，如果找到，則返回“muscle”作為類別。如果既沒(méi)有找到“muscle”也沒(méi)有找到“muscle”，則返回“other”作為類別。

我可以繼續(xù)在公式中添加我認(rèn)為合適的規(guī)格.下面的屏幕截圖顯示了這個(gè)公式的作用：

這個(gè)公式的真正威力在于它可以在整個(gè)數(shù)據(jù)集上使用，無(wú)需有人手動(dòng)瀏覽和分類每個(gè)關(guān)鍵字。雙擊單元格 C2 的右下角會(huì)將公式應(yīng)用于 C 列中的所有單元格，只要 B 列中它旁邊有一個(gè)值（這是 Excel 的規(guī)則） ，不是公式）。下面的屏幕截圖顯示了將公式應(yīng)用于所有數(shù)據(jù)的效果。請(qǐng)注意公式如何從分析單元格 A2 更改為單元格 C19 中的單元格 A19，其中應(yīng)用了該公式。

“muscle”未在屏幕截圖中列為類別，但在數(shù)據(jù)集中稍后列為類別。在這一點(diǎn)上，我還需要指出公式中的一個(gè)不足之處。如果特定查詢包含多個(gè)我們嘗試分類的字符串，它將返回它找到的第一個(gè)正字符串匹配的類別。第 29 行就是一個(gè)很好的例子。在此特定場(chǎng)景中，查詢是“supplement”，但由于公式在查找“muscle”之前先查找“supplement”?，并且在“supplement”中找到了正匹配，因此它將單元格歸類為“supplement”。

在既沒(méi)有找到“supplement”也沒(méi)有找到“muscle”的單元格中，它返回“other”。此時(shí)，我們向數(shù)據(jù)集添加了一個(gè)過(guò)濾器，可以過(guò)濾掉所有“muscle”和“supplement”查詢，以準(zhǔn)確揭示“other”的組成部分。

查看此列表，包含“protein”的查詢似乎占列表的相當(dāng)大的百分比，因此我們也可以將其添加為一個(gè)類別。從這里我們可以添加一個(gè)數(shù)據(jù)透視表并按搜索量和關(guān)鍵字?jǐn)?shù)量進(jìn)行排序。

從這里我們可以了解我們應(yīng)該將努力的目標(biāo)定位于何處以及我們需要更多關(guān)注的地方。在這一點(diǎn)上，“other”仍然是一個(gè)太大的類別，所以我會(huì)進(jìn)一步細(xì)化它以創(chuàng)建更多類別，以找出我們?nèi)绾问蛊涓呖刹僮餍浴?/span>

用例 #2：拒絕垃圾外鏈

根據(jù)你站點(diǎn)的大小，你可能會(huì)處理數(shù)千或數(shù)百萬(wàn)個(gè)外鏈跟域名，因此你需要從某個(gè)地方開(kāi)始并減少你的列表。一種方法是通過(guò)某種指標(biāo)對(duì)域進(jìn)行排序（我經(jīng)常使用Majestic 的信任流）。我使用該公式來(lái)查找與垃圾郵件域相關(guān)的常用詞，例如“submit”、“seo”、“directory”、“free”、“drugs”和“articles”，當(dāng)然還有更多（“. xyz”是我經(jīng)常看到的另一個(gè)）。該公式會(huì)在你的鏈接根域列表中找到任何指定的查詢，讓你可以快速將它們識(shí)別為垃圾郵件并將它們添加到你的拒絕列表中。下面的屏幕截圖顯示了一個(gè)示例站點(diǎn)的鏈接配置文件，按“Spam”排序，使用上面的過(guò)濾器作為標(biāo)準(zhǔn)，然后按信任流的升序排序。本例中使用的公式比我們之前的示例稍長(zhǎng)，但遵循相同的模式。

=IF(ISNUMBER(SEARCH("submit",A2)),"Spam",IF(ISNUMBER(SEARCH("seo",A2)),"Spam",
IF(ISNUMBER(SEARCH("directory",A2)),"Spam",IF(ISNUMBER(SEARCH("free",A2)),"Spam",
IF(ISNUMBER(SEARCH("drugs",A2)),"Spam",IF(ISNUMBER(SEARCH("articles",A2)),"Spam",
IF(ISNUMBER(SEARCH(".xyz",A2)),"Spam","Other")))))))

在許多情況下，你的鏈接配置文件會(huì)包含來(lái)自聽(tīng)起來(lái)合法的域的垃圾鏈接。此公式無(wú)法過(guò)濾掉所有垃圾郵件，但它通常有助于從你的列表中刪除至少一些域。此外，現(xiàn)在被公式標(biāo)記為垃圾郵件的某些域可能實(shí)際上是合法網(wǎng)站。你應(yīng)該始終分析此公式的輸出，以確保其正常工作。同樣，它可以作為你拒絕工作的起點(diǎn)，并有望減少某些域，但這絕不是你應(yīng)該查看的唯一內(nèi)容。

用例 #3：網(wǎng)站自然流量分析

這個(gè)分類公式的另一個(gè)非常酷的用例是來(lái)自 Google Analytics 的數(shù)據(jù)分析。，我經(jīng)常分析有關(guān)從自然渠道到網(wǎng)站的流量的信息。我將顯示的結(jié)果數(shù)從 10 更改為 2,500 并導(dǎo)出數(shù)據(jù)。導(dǎo)出后，我可能想知道哪些類型的頁(yè)面往往會(huì)獲得最多的流量、最高的轉(zhuǎn)化率、最多的收入，或者相反。

由于每個(gè)站點(diǎn)都不同，你會(huì)在每個(gè)站點(diǎn)上尋找不同的內(nèi)容。理想情況下，該站點(diǎn)將具有已建立的子文件夾結(jié)構(gòu)，例如 example.com/blog/article-1、example.com/supplements/product-1 或 example.com/toys/gadget-1。使用 URL 中的這些常見(jiàn)功能，你可以為它們添加任何你喜歡的標(biāo)簽，可能是“blog”或“supplements”或“toys”，并使用此分類來(lái)細(xì)分哪些類型的頁(yè)面最有效以及在哪里可以改進(jìn)。

我從 Google Search Console 導(dǎo)出了網(wǎng)站的數(shù)據(jù)，并按“compare”、“reviews”、“altermatives”和“other”劃分了他們的頁(yè)面。由此，我能夠確定我們可以改進(jìn)的地方，確定有效的方法，并向客戶展示更具體的數(shù)據(jù)。

公式2

=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n]?+@?)?(?:www\.)?([^:\/\n]+ )”)

或者

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?:\/\/)?(?:[^@\n]?+@?)?(?:www\.)?([^: \/\n]+)”)),“”)

您可能需要查找某些具有HTTP 與 HTTPS 的博客文章或登錄頁(yè)面。

您始終可以使用 Excel 的搜索欄單獨(dú)查找它們，但如果您有很長(zhǎng)的 URL 列表，這可能會(huì)很耗時(shí)。相反，使用REGEXTRACT 從您的列表中提取特定數(shù)據(jù)。

=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n]?+@?)?(?:www\.)?([^:\/\n]+ )”)

您可以使用 ARRAY 公式一次將其添加到多行。公式看起來(lái)很亂，但它有效。

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?:\/\/)?(?:[^@\n]?+@?)?(?:www\.)?([^: \/\n]+)”)),“”)

這是使用我們的示例的樣子：

這是最終結(jié)果：

概述

Excel基本函數(shù)講起來(lái)可能需要分很多篇文章，有幸網(wǎng)上有很多帖子都分享到了，大家可以搜索看看。這里我只整理了一些有難度的，我們工作會(huì)用到的分享出來(lái)，可能也要分幾個(gè)篇幅了，今天就先到這里。

分享一個(gè)工具SEO in EXCEL（網(wǎng)址：http://seoinexcel.com/）。

附：SEO要掌握的一些基本的函數(shù)截圖