Header Ads

in photobook

10 hàm Excel thông dụng cho SEO

Trong quá trình làm SEO, chúng ta thường dụng Exel để lưu cái số liệu, thống kê, từ khoá... Sau đây là 10 hàm thường dùng trong Excel khi làm SEO.

1. IF

Trả về giá trị tương ứng với điều kiện.
Cú pháp: =IF (điều kiện, giá trị nếu đúng, giá trị nếu sai)


Website bạn có nhiều từ khoá đứng top 10, bạn muốn tìm từ khoá có 500 lượt truy cập mỗi tháng. Bạn viết cú pháp trả về "GOOD" với từ khoá có 500 lượt truy cập (ví dụ: 29% lượt truy cập >=500) và trả về "BAD" với những từ khoá khác.

Hàm IF trả về giá trị theo điều kiện:
=IF(B2*0.29>=500,"GOOD","BAD")



Tải file IF, IFERROR & ARRAYFORMULA

2. IFERROR

Cho phép cài đặt giá trị mặc định khi kết quả sai.
Cú pháp: =IFERROR (Hàm IF, giá trị sai)

Trở lại với ví dụ trên, hàm sau sẽ bỏ trống các ô không có giá trị.

Hàm 
IFERROR bỏ trống ô không có giá trị:
=IFERROR(IF(B2*0.29>=500,"GOOD","BAD"),"")



Tải file IF, IFERROR & ARRAYFORMULA

3. ARRAYFORMULA

Cho phép bạn chạy hàm IFERROR hàng ngàn dòng chỉ với 1 lệnh duy nhất.
Cú pháp: =ARRAYFORMULA (array_formula)

ARRAYFORMULA chuyển hàm ban đầu thành mảng, cho phép bạn chạy nhiều dòng chỉ với môt dòng lệnh duy nhất.

Hàm ARRAYFORMULA chạy 1 lần trên nhiều dòng:
=ARRAYFORMULA(IFERROR(IF(B2:B29*0.29>=500,"GOOD","BAD"),""))


Xoá hàm từ cột B2, nhập hàm sau lên cột B1


Tải file IF, IFERROR & ARRAYFORMULA

4. REGEXTRACT - Tách ký tự đặc biệt từ chuỗi

REGEXTRACT dùng ký tự tách để tách cụm từ trong văn bản.
Cú pháp: =REGEXEXTRACT (văn bản, ký tự tách)

Ứng dụng:

  • Tách tên miền khỏi các ký tự URL
  • Tách URL (bỏ domain gốc)
  • Kiểm tra URL có HTTP hoặc không có HTTPS
  • Tách email ra khỏi chuỗi văn bản
  • Kiểm tra URL có hoặc không có tụm từ nào đó (/category/guest-post)


Tách domain gốc từ danh sách URL chứa cụm từ "write for us".

Hàm REGEXTRACT tách domain gốc:
=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")








Sử dụng ARRAYFORMULA và IFERROR để chạy hàm trên nhiều cột:

=ARRAYFORMULA(IFERROR(IF(B2:B29*0.29>=500,"GOOD","BAD"),""))



Tải file REGEXTRACT

5. SPLIT - Tách chuỗi dữ liệu thành nhiều cụm

Dùng để tách chuỗi văn bản theo dấu phân cách
Cú pháp: =SPLIT (văn bản, dấu phân cách)

Ứng dụng:

  • Tách họ và tên thành 2 cột riêng
  • Tách URL thành 3 cột HTTP, domain gốc và URL
  • Tách danh sách các cụm từ phân cách bởi dấu phẩy.
  • Tách domain gốc thành 2 cột tên domain và phần mở rộng




Hàm SPLIT tách Họ và Tên:
=SPLIT(A2," ")


Sử dụng ARRAYFORMULA và IFERROR để chạy hàm trên nhiều cột:

Hàm SPLIT tách họ và tên thành 2 cột:
=IFERROR(ARRAYFORMULA(SPLIT(A2:A," ")),"")



Hàm SPLIT tách abc.com thành 2 cột:
=SPLIT(A2,".")



Tải file SPLIT

6. VLOOKUP - Trộn dữ liệu
VLOOKUP cho phép bạn tìm cụm dữ liệu bằng từ khoá - trả về giá trị tương ứng tại ô gọi là vùng.

Cú pháp: VLOOKUP(từ khoá, vùng, cột)

Ứng dụng:

  • Trộn dữ liệu từ nhiều nguồn (Trộn danh sách các domain với Ahrefs tương ứng từ sheet khác)
  • Kiểm tra xem có văn bản khác có trùng dữ liệu không (kiểm tra chéo trùng lắp trên nhiều nguồn dữ liệu)
  • Gắn email (từ cơ sở dữ liệu chính) vào danh sách khách hàng tiềm năng.


Bạn có danh sách khách hàng tiềm năng (lấy từ dữ liệu khách hàng của đối thủ từ Site Explorer), bạn cũng có một danh sách thông tin khách hàng của riêng bạn ở bảng tính khác. Sử dụng VLOOKUP chọn lọc thông tin bạn chưa có.

File lấy từ Site Explorer
Kết quả sau khi chạy hàm VLOOKUP
Hàm VLOOKUP tách dữ liệu trùng từ Site Explorer:
=IFERROR(ARRAYFORMULA(VLOOKUP(D2:D,'Master contact database '!A:C,2,false)),"")


Tải file VLOOKUP

7. IMPORTXML - Lấy dữ liệu từ bất cứ website nào

IMPORTXML cho phép bạn lấy dữ liệu (sử dụng xpath_query) từ các cấu trúc dữ liệu như XML, HTML, and RSS.

Cú pháp: =IMPORTXML(url, xpath_query)

Ứng dụng:

  • Lấy dữ liệu các thẻ meta từ các URL (title, description, h-tags...)
  • Lấy email từ website
  • Lấy thông tin profile trên mạng xã hội
  • Lấy ngày cập nhật mới nhất từ RSS (kiểm tra thời gian cập nhật tin mới nhất của website mà không phải truy cập website)




Hàm IMPORTXML lấy tiêu đề bài viết:
=IMPORTXML(“https://ahrefs.com/blog/keyword-research/”,”//title”)


Có thể sử dụng tên cột cho biến url:
=IMPORTXML(A2,"//title”)

IMPORTXML không hỗ trợ hàm ARRAYFORMULA

Các tham số XPath

  • Lấy tất cả các link: “//@href”
  • Lấy tất cả các link nội bộ: “//a[contains(@href, ‘domain.com’)]/@href”
  • Lấy tất cả các link ngoại: “//a[not(contains(@href, ‘domain.com’))]/@href”
  • Lấy thông tin từ thẻ meta description: “//meta[@name=‘description’]/@content”Lấy thông tin thẻ H1: “//h1”
  • Lấy địa chỉ email: “//a[contains(@href, ‘mailTo:’) or contains(@href, ‘mailto:’)]/@href”
  • Lấy thông tin profile mạng xã hội (Linkedln, Facebook, Twitter): “//a[contains(@href, ‘linkedin.com/in’) or contains(@href, ‘twitter.com/’) or contains(@href, ‘facebook.com/’)]/@href”
  • Lấy ngày cập nhật mới nhất (RSS): “//lastBuildDate”


Bạn có thể tìm tham số XPath của bất cứ thành phần nào bằng cách sau (với Chrome):

Chuột phải > Inpect > Chuột phải > Copy > Copy XPath



Tải file IMPORTXML

8. SEARCH - Tìm kiếm giá trị

Kiểm tra giá trị cần tìm và trả về vị trí đầu tiền tìm được giá trị.

Cú pháp: SEARCH (cụm từ tìm kiếm, giá trị cần tìm)

Ứng dụng:
  • Kiểm tra xem URL có subdomain không (dùng để phân loại danh sách nhiều domain)
  • Phân loại từ khoá theo nhu cầu (thương hiệu, thương mại…)
  • Tìm kiếm những ký tự đặc biệt, không mong muốn trong URL
  • Tìm kiếm cụm từ trong URL để phân loại liên kết (“/category/guest-post”, “resources.html”)

Bạn có danh sách đường link lấy từ Ahrefs.com, bạn muốn đường link có chứa cụm từ /blog/ sẽ ghi chú chữ “YES” bên cạnh:

Hàm SEARCH ghi chú theo điều kiện:
=IF(SEARCH(“/blog/”,A2),“YES”,””)


Hàm SEARCH kết hợp IFERROR và ARRAYFORMULA:
=IFERROR(ARRAYFORMULA(IF(SEARCH("/blog/",A2:A),"YES","")),"")




Các cụm từ tìm kiếm phổ biến:

  • Tìm cụm từ “write for us” trong danh sách đường link:
  • =IF(SEARCH(“/write-for-us/”,A2),“Write for us page”,””)
  • Tìm trang resource trong danh sách đường link:
  • =IF(SEARCH(“/resources.html”,A2),“Resource page”,””)
  • Tìm tên thương hiệu (trong danh sách từ khoá):
  • =IF(SEARCH(“brand_name”,A2),“Branded keyword”,””)
  • Tìm kiếm liên kết trong/ngoài (từ sánh sách đường link):
  • =IF(SEARCH(“yourdomain.com”,A2),“Internal Link”,“External Link”)

Tải file SEARCH


9. IMPORTRANGE - Lấy dữ liệu từ bảng tính khác

Cho phép bạn nhập dữ liệu từ bảng tính Google khác.
Cú pháp: IMPORTRANGE (spreadsheet_ID, range_to_import)

Ứng dụng:
  • Tạo bảng tính dành cho khách hàng với dữ liệu từ bảng tính chính của bạn.
  • Tìm kiếm chéo các tham số trong nhiều bảng tính (sử dụng IMPORTRANGE kết hợp với VLOOKUP)
  • Chèn dữ liệu từ bảng tính khác và sử dụng để xác thực dữ liệu.
  • Chèn thông tin liên hệ từ bảng tính chính bằng VLOOKUP


Bạn có 1 danh sách khách hàng làm SEO và ngân sách tạm gọi là IMPORTRANGE - DATA. (Tải file IMPORTRANGE - DATA).



A: Bạn muốn kết hợp danh sách này với bảng tính khác, bạn có thể chèn dữ liệu từ IMPORTRANGE - DATA với hàm sau:
=IMPORTRANGE(“SPREADSHEET_KEY”,“‘SheetName’!A2:A”)



Cách lấy mã SPREADSHEET_KEY



B: Giả sử bạn xây dựng link cho những khách hàng này trên bảng tính. Bạn liệt kê link trên 1 cột và cột kia bạn muốn ghi chú tên của khách hàng tương ứng với link. Bạn sử dụng IMPORTRANGE xây dựng bảng liệt kê của tất cả khách hàng với giá trị tương ứng.



Bảng danh sách này sẽ tự động cập nhật mỗi khi bạn thêm/xoá khách hàng trong file IMPORTRANGE - DATA.


Tải file IMPORTRANGE - FORMULASIMPORTRANGE - DATA

10.  QUERY - Trích xuất dữ liệu từ truy vấn SQL (cái này cực kỳ hay!)

QUERY cũng giống như VLOOKUP, cho phép bạn sử dụng SQL để truy vấn dữ liệu.

Cú pháp: QUERY (range, sql_query)

Ứng dụng:
  • Truy vấn dữ liệu mong muốn theo điều kiện (Chỉ liệt kê 50 bài viết của khách)
  • Tạo dữ liệu khách hàng kết hợp với bảng tính khác.
  • Trích xuất những trang muốn lấy qua truy vấn.


Trở lại ví dụ về thẻ "blog posts".



A. Nếu bạn muốn chỉ liệt kê những link có chứa "blog posts” vào bảng tính mới, sử dụng hàm sau:
=QUERY(DATA!A:B,"select A where B = 'Blog Post’")




B. Bạn có 1 file dữ liệu khổng lồ trích xuất từ Site Explorer.



Bạn muốn lấy dữ liệu thoả các điều kiện sau:
  • Link có Dofollow
  • Domain Rating (DR) > 50
  • Backlink status = active
  • Số lượng link liên kết ngoài < 50



Hàm QUERY lấy dữ liệu theo điều kiện:
=QUERY('DATA - site explorer export'!A2:R,"SELECT E where D > 50 AND H < 50 AND M = 'Dofollow' AND N <> 'REMOVED'")


* Hàm QUERY không chỉ có thể kết hợp IMPORTRANGE để truy vấn, mà còn có thể truy vấn dữ liệu từ bảng tính khác.

Tải file QUERY

Tham khảo

Bạn có thể tham khảo thêm các công thức khác tại Thư viện Google Sheet

Ngoài ra Google Sheets còn tích hợp thêm Zapier and IFTTT, bạn có thể sử dụng hàng ngàn công cụ và dịch vụ tại đây.

Ngoài ra, bạn có thể xem qua các tính năng nâng cao từ Apps Script - đây là công cụ rất mạnh!


Nguồn: ahrefs.com


1 nhận xét:

  1. Chào ad, tại trường hợp hàm importxml mình gặp một số web khi dùng chrome lấy Xpath thì hàm không nhận đc. ví dụ lấy giá trên điện máy xanh: https://www.dienmayxanh.com/may-lanh/samsung-ar13mvfhgwknsv
    hay Shopee: https://shopee.vn/Balo-Ph%E1%BB%91i-Da-Cao-C%E1%BA%A5p-EMPIRE-B-i15-i.46788019.982732676
    Nhờ ad hỗ trợ với ạ.

    Trả lờiXóa

Xây dựng bởi Kim Thăng. Được tạo bởi Blogger.