Đôi khi, làm SEO, bạn có thể cần tìm các từ khóa phổ biến nhất từ một danh sách tiêu đề. Để làm điều này, bạn có thể sử dụng Google Sheets.
Dĩ nhiên, không có công thức tích hợp sẵn cho việc này. Tôi không biết về sự có sẵn của các tiện ích bổ sung. Nhưng tôi đã viết hai công thức mà bạn có thể tìm thấy hữu ích.
Tôi có hai loại giải pháp (công thức) khác nhau vì tôi tiếp cận chủ đề từ hai góc độ khác nhau – từ khóa đơn và từ khóa nhiều từ.
Trong trường hợp đầu tiên (từ khóa đơn), bạn có thể sử dụng các mệnh đề khác nhau trong chức năng Query để tìm 10 từ khóa phổ biến nhất hoặc “n” từ khóa phổ biến nhất từ danh sách tiêu đề bài viết trong một cột trên Google Sheets.
Trong trường hợp thứ hai (từ khóa nhiều từ), mọi thứ khá khác biệt. Ở đây, ngoài tiêu đề bài viết là một danh sách, bạn cũng nên chỉ định các từ khóa khác như một danh sách khác.
Công thức sẽ trả về số lần xuất hiện của từ khóa được chỉ định đó.
Để lấy từ khóa phổ biến nhất từ tiêu đề bài viết trên Google Sheets, trong cả hai trường hợp trên, như tôi đã đề cập, tôi sẽ sử dụng chức năng Query. Nhưng chúng ta cũng có thể cần sử dụng một số chức năng khác cùng với Query.
Cách lấy từ khóa phổ biến nhất từ Tiêu đề bài viết trên Google Sheets
Để giải thích công thức, chúng ta có thể cần dữ liệu mẫu. Ở đây tôi sẽ sử dụng một số tiêu đề bài viết từ blog của tôi.
Tiêu đề bài viết của tôi tuân thủ các quy tắc sau đây:
- Không có dấu ngoặc đơn hoặc ngoặc đôi trong tiêu đề.
- Tức là tiêu đề bài viết không bao gồm () (ngoặc đơn), {} (ngoặc nhọn), [] (ngoặc vuông) và <> (ngoặc nhọn).
- Nếu bạn có các dấu ngoặc đơn hoặc ngoặc đôi trong tiêu đề của mình, hãy thay thế chúng bằng lệnh Chỉnh sửa > Tìm và thay thế (trong trình đơn) trên Google Sheets.
Dưới đây là các tiêu đề mà chúng ta sẽ tìm các từ khóa phổ biến nhất trên Google Sheets.
Từ khóa đơn
Lấy công thức của tôi trước khi đọc giải thích công thức. Công thức Query sau đây phù hợp cho việc chèn vào ô B1 trong bảng chứa danh sách trên.
=Query(transpose(split(query(A2:A100,,9^9)," ")),"Select Col1, Count(Col1) where not Col1 matches 'a|an|and|as|or|where|if|in|how|by|all|for|to|use|of|the|with' group by Col1 order by Count(Col1) desc",0)Giải thích công thức:
- Công thức
query(A2:A100,,9^9)kết hợp các tiêu đề bài viết thành một ô duy nhất. Điều này giúp chúng ta tránh việc sử dụng các chức năng Join hoặc TextJoin. - Hàm Split tách kết quả của Query thành các từ dựa trên dấu cách là phân tách. Vì vậy, chúng ta sẽ có một hàng với một số từ trong mỗi ô trong hàng đó.
- Hàm Transpose thay đổi hướng của kết quả Split. Vì vậy, dữ liệu sẽ nằm trong cùng một cột.
- Query bên ngoài nhóm cột này. Hai mệnh đề quan trọng trong công thức là Select Col1 và group by Col1.
- Chúng ta đã sử dụng hàm tổng hợp Count vì điều chúng ta muốn nhận là số lần xuất hiện của các từ khóa phổ biến nhất từ các tiêu đề.
- Tôi đã sử dụng mệnh đề Matches trong đó not Col1 matches ‘a|an|and|as|or|where|if|in|how|by|all|for|to|use|of|the|with’ để loại trừ một số từ khóa khác nhau khỏi nhóm (các từ được phân tách bằng dấu |). Nếu bạn muốn, bạn có thể bổ sung thêm hoặc xóa một số từ khóa này.
- Phần của công thức, tức là order by Count(Col1) desc, dùng để sắp xếp số lần xuất hiện của từ khóa phổ biến nhất theo thứ tự giảm dần.
- Nếu bạn muốn, bạn có thể bổ sung phần limit 10 (không phải là một phần của “Công thức”) để lấy 10 từ khóa phổ biến nhất từ tiêu đề bài viết trên Google Sheets.
Chỉ vậy thôi.
Từ khóa nhiều từ (Các từ khóa cần được chỉ định trước)
Để lấy số lần xuất hiện của các từ khóa nhiều từ từ tiêu đề bài viết trên Google Sheets, ngoài tiêu đề bài viết, chúng ta nên chỉ định các từ khóa.
Vui lòng xem ảnh chụp màn hình sau đây.
Ghi chú quan trọng:
- Ở đây, tất cả các từ khóa đều chứa hai từ.
- Nếu bạn muốn tìm số lần xuất hiện của từ khóa chứa 3 từ, đừng bao gồm chúng trong danh sách trên C2:C. Trong trường hợp đó, C2:C chỉ có thể chứa các từ khóa với 3 từ.
- Điều đó có nghĩa là danh sách trong C2:C chỉ nên chứa các từ khóa duy nhất.
- Trong trường hợp hiếm, nếu một tiêu đề bài viết chứa các từ khóa nhiều từ kề nhau, công thức có thể chỉ đạt được kết quả gần như hoàn hảo.
- Ví dụ, xem điều kiện truy vấn tiêu đề bài viết đầu tiên trong google sheets. Trong này, “query function” và “google sheets” là hai từ khóa.
- Nhưng nếu tiêu đề như sau; “query function in google sheets query function”, sẽ có một vấn đề vì có hai từ khóa kề nhau ở cuối.
Trong bốn bước (bước thứ tư là tùy chọn), chúng ta có thể viết công thức để lấy số lần xuất hiện của các từ khóa nhiều từ phổ biến nhất từ tiêu đề bài viết trên Google Sheets.
Có tổng cộng bốn công thức trong mỗi bước. Chúng ta sẽ kết hợp chúng trong bước cuối cùng. Dưới đây là các bước đó.
Bước 1 – Regex
Trong ô F2, chèn công thức Regexreplace sau để thay thế các từ khóa nhiều từ trong tiêu đề bài viết bằng biểu thức chính quy (.*).
=ArrayFormula(REGEXREPLACE(A2:A100,textjoin("|",true,C2:C100),"(.*)"))
Bước 2 – Regex
Trong ô G2, chèn công thức Regexreplace khác.
=ARRAYFORMULA(IFNA(REGEXREPLACE(A2:A100,F2:F100,{"$1","$2","$3","$4"})))
Phần công thức sau trong công thức trên làm cho công thức trả lại kết quả trong 4 cột (G, H, I, và J).
{"$1","$2","$3","$4"}Nếu tiêu đề bài viết của bạn dài và chứa nhiều hơn 3 từ khóa (thông thường có thể không), bạn có thể tăng số cột trong kết quả bằng cách thay đổi phần công thức trên thành {"$1","$2","$3","$4","$5","$6"}.
Điều này có nghĩa là công thức sẽ trích xuất tối đa sáu từ khóa nhiều từ từ tiêu đề bài viết trong mỗi hàng trên Google Sheets.
Trong bước 3 bên dưới, chúng ta sẽ lấy các từ khóa nhiều từ (được liệt kê) phổ biến nhất từ tiêu đề bài viết trên Google Sheets.
Tôi đã sử dụng từ ‘liệt kê’ vì chúng ta chỉ sử dụng các giá trị (từ) trong danh sách C2:C cho việc đếm.
Bước 3 – Query
Hãy làm phẳng kết quả công thức thứ hai. Vì vậy, kết quả đầu ra nhiều cột sẽ được đặt vào một cột duy nhất để chúng ta có thể Query kết quả đó.
Trong ô K2, nhập công thức này.
=query( flatten(G2:J100), "Select Col1,Count(Col1) where Col1 is not null group by Col1 order by Count(Col1) desc",0 )
Bước 4 – Công thức cuối cùng
Cuối cùng, trong ô D2, sử dụng công thức Vlookup tùy chọn dưới đây. Đúng, công thức dưới đây là tùy chọn vì bạn có thể sử dụng công thức bước 3 là công thức cuối cùng.
=ArrayFormula(ifna(vlookup(C2:C5,K2:L6,2,0)))Hãy kết hợp tất cả các công thức lại với nhau.
=ArrayFormula(ifna(vlookup(C2:C100,query(flatten(IFNA(REGEXREPLACE(A2:A100,REGEXREPLACE(A2:A100,textjoin("|",true,C2:C100),"(.*)"),{"$1","$2","$3","$4"}))),"Select Col1,Count(Col1) where Col1 is not null group by Col1 order by Count(Col1) desc",0),2,0)))
Chỉ giữ lại công thức D2 và xóa tất cả các công thức chúng ta đã chèn vào các ô trong các bước trước đó.
Đó là tất cả những gì về cách lấy từ khóa phổ biến nhất từ tiêu đề trên Google Sheets.
Cảm ơn vì đã đọc. Thưởng thức nào!
