Google Sheets là một công cụ dựa trên đám mây mạnh mẽ (và miễn phí), giúp chúng ta sắp xếp dữ liệu hiệu quả. Bí quyết để tận dụng tối đa mọi tính năng của Google Sheets là khai thác danh sách công thức phong phú của công cụ này.
Nếu không có sẵn một bộ công thức thành thạo, bạn có thể mất hàng giờ để sắp xếp, tách, gộp, cộng, xóa và tìm kiếm dữ liệu. Ngược lại, nếu biết chính xác công thức nào cần dùng cho từng tác vụ, bạn sẽ tiết kiệm được rất nhiều thời gian (và, nói thật nhé, cả năng lượng tinh thần).
Bạn cũng có thể giúp các bảng tính có thể chia sẻ trở nên gọn gàng và dễ quản lý hơn cho mình cũng như bất kỳ ai cần sử dụng chúng.
Những gì người làm SEO cần
Là người làm SEO, chúng ta không muốn dành toàn bộ thời gian để dọn dẹp những bảng tính lộn xộn. Chúng ta muốn bảng tính trở thành nguồn tài liệu hữu ích, hoạt động vì chúng ta – đơn giản hóa công việc và trình bày rõ ràng dữ liệu quan trọng. Hôm nay, tôi muốn chia sẻ danh sách các công thức cơ bản mà tôi biết sẽ giúp bạn làm việc nhanh và thông minh hơn, để có thêm thời gian dành cho những nhiệm vụ khác mà bạn yêu thích.
Có thể bạn mới làm quen với Google Sheets và chưa biết bắt đầu từ đâu. Hoặc có thể bạn đã thích sử dụng công cụ này và muốn tận dụng tối đa mọi tính năng mà nó cung cấp. Dù ở trường hợp nào, danh sách công thức dưới đây cũng là bộ công cụ hữu ích để bạn luôn có sẵn.
Một số kiến thức cơ bản về Google Sheets trước khi bắt đầu
Trước khi đi sâu vào các công thức, chúng ta sẽ điểm qua một số kiến thức nền tảng dành cho người mới bắt đầu. Nắm được những định nghĩa cơ bản này sẽ giúp bạn dễ dàng hiểu cách sử dụng các công thức chúng tôi chia sẻ hôm nay cũng như những công thức khác mà bạn sẽ tìm hiểu sau này.
Một ô là một điểm dữ liệu riêng lẻ trong bảng tính.
Một hàng là tập hợp các ô trải dài theo chiều ngang.
Một cột là tập hợp các ô trải dài theo chiều dọc.
Một hàm là thao tác được dùng để tạo ra kết quả mong muốn.
Một công thức là sự kết hợp giữa các ô, cột, hàng, hàm và phạm vi, được dùng để tạo thành một phương trình.
Một phạm vi là vùng chọn gồm các ô trong một cột hoặc một hàng (hoặc cả hai).
Tất cả công thức phải bắt đầu bằng dấu bằng (=).
1. VLOOKUP để nhập dữ liệu
Hầu hết những người làm SEO đều đồng ý rằng hàm VLOOKUP (V là viết tắt của vertical, tức chiều dọc) là một trong những công cụ hữu ích nhất trong bộ công cụ của bạn. Hàm này cho phép bạn lấy dữ liệu từ một bảng tính sang bảng tính khác, miễn là hai bảng có chung một giá trị.
Hàm này rất hữu ích khi bạn có những bộ dữ liệu lớn cần kết hợp mà không phải mở từng ô để sao chép và dán dữ liệu theo cách thủ công. Hàm đặc biệt hữu ích khi bạn làm việc với nhiều bảng tính.
Giả sử tôi có một bảng tính gồm hai tab (xem ảnh chụp màn hình bên dưới). Một tab hiển thị danh sách keyword cùng search volume hàng tháng của từng keyword. Tab thứ hai có cùng danh sách keyword, kèm điểm SEO difficulty. Mục tiêu của tôi là dùng công thức VLOOKUP để đưa điểm SEO difficulty vào tab đầu tiên:
=VLOOKUP(search_key, range, index, is_sorted)
Lưu ý rằng điểm dữ liệu (trong trường hợp này là “Keyword”) mà tôi muốn lấy từ tab này sang tab kia phải giống nhau trong cả hai bảng. Điểm dữ liệu này cũng phải nằm ở cột đầu tiên của tab thứ hai — cột A.
Tôi muốn đưa dữ liệu ở cột thứ hai (DifficultyScore) vào bảng tính “Volume” (tab đầu tiên). Công thức của tôi sẽ như sau:
=VLOOKUP(A2,DifficultyScore!A:B,2,0)
Hãy cùng phân tích từng phần của công thức này:
Search_key là giá trị chung có trong cả hai bảng. Chúng ta sẽ bắt đầu với keyword đầu tiên trong danh sách, nằm ở ô A2.
Range là nơi bạn nhập tên tab cần lấy dữ liệu, cùng với phạm vi cột mà bạn muốn công thức đọc. Tôi muốn lấy dữ liệu ở cột A (Keyword) và B (SEO Difficulty).
Index là nơi bạn nhập số thứ tự của cột muốn lấy dữ liệu. A=1, B=2, C=3, v.v.
Cuối cùng, tôi chỉ cần đặt is_sorted thành 0 hoặc “FALSE,” vốn thường được khuyến nghị dùng trong công thức này để trả về kết quả khớp chính xác.
Để nhập công thức, tôi nhấp vào ô C2, nhập công thức rồi nhấn Enter. Giá trị chính xác được điền vào là 31. Sau đó, tôi có thể nhấp vào góc ô C2 rồi kéo xuống để điền các giá trị còn lại trong cột.
2. Dùng IF khi điều kiện đúng hoặc sai
Công thức IF cho phép bạn kiểm tra xem một điều kiện nhất định là đúng hay sai đối với danh sách dữ liệu trong bảng tính.
Công thức là =IF(condition, value_if_true, value_if_false)
- Condition là điều kiện bạn muốn kiểm tra xem đúng hay sai.
Nếu điều kiện đúng, “value_if_true” là thao tác sẽ được hiển thị. Nếu điều kiện sai, “value_if_false” sẽ được hiển thị thay thế.
Hãy xem một ví dụ cơ bản để hiểu cách công thức này hoạt động.
Lần này, tôi muốn kiểm tra danh sách keyword để xem keyword nào có hơn 5,000 khách truy cập mỗi tháng. Tôi sẽ bắt đầu với search volume của keyword đầu tiên ở ô B2. Công thức như sau:
=IF(B2>5000,"YES","NO")
Sau khi nhập công thức cho keyword đầu tiên rồi nhấn Enter, tôi có thể kéo từ ô đầu tiên (D2) xuống hết cột để các kết quả tự động điền.
Tất cả keyword có traffic trên 5.000 lượt truy cập mỗi tháng sẽ hiển thị là “YES,” còn những keyword có traffic dưới 5.000 lượt truy cập sẽ hiển thị là “NO.”
Bạn cũng có thể dùng các phép toán sau với công thức này:
Bằng (=)
Không bằng (<>)
Lớn hơn (>)
Nhỏ hơn (<)
Lớn hơn hoặc bằng (>=)
Nhỏ hơn hoặc bằng (<=)
3. Thiết lập giá trị mặc định cho lỗi bằng IFERROR
Giả sử một trong các ô của bạn hiển thị thông báo lỗi – chẳng hạn “#ERROR!” hoặc “#DIV/0!” hoặc “#VALUE!” – khi bạn thử chạy công thức.
Thông báo lỗi có thể xuất hiện khi bạn chia cho 0 hoặc sử dụng một phạm vi không xác định. Điều này có thể gây rắc rối khi bạn thực hiện phép tính liên quan đến các ô đó. Bảng tính cũng có thể trông lộn xộn và khiến người khác khó hiểu khi bạn trình bày.
IFERROR giúp bạn xử lý vấn đề này bằng cách thay giá trị lỗi bằng giá trị mặc định do bạn thiết lập.
Chỉ cần nhập công thức ban đầu cùng giá trị bạn muốn dùng làm mặc định. Bạn cũng có thể để trống bằng cách nhập " " cho value_if_error.
=IFERROR (original_formula, value_if_error)
Trong ví dụ này, tôi muốn tìm hiểu keyword nào có thể mang lại hơn 500 lượt truy cập cho website của mình bằng cách nhân search volume với click-through rate (CTR). Vì “digital marketing agency near me” (giả định) không có CTR nên công cụ trả về thông báo lỗi.
Thay vì hiển thị thông báo lỗi, tôi muốn để trống ô này, vì vậy tôi sẽ bọc công thức trong IFERROR:
=IFERROR(IF(C3*E3>500,"YES","NO"),"")
4. Tách dữ liệu vào hai ô bằng SPLIT
Tiếp theo là công thức SPLIT:
=SPLIT(Text, Delimiter)
Công thức này cho phép bạn tách dữ liệu từ một ô thành nhiều ô. Giả sử bạn có bảng tính danh bạ, trong đó họ tên đầy đủ nằm trong một cột. Để nhập dữ liệu chính xác vào cơ sở dữ liệu, trước tiên bạn cần tách tên và họ thành hai cột riêng.
Nếu muốn tách tên trong cột A thành tên và họ ở cột B và C, công thức của tôi sẽ là =SPLIT(A2, " ") trong đó A2 là phần văn bản tôi muốn tách, còn dấu phân cách là ký tự ngăn cách tên và họ – trong trường hợp này là một dấu cách.
Tôi sẽ nhấp vào ô B2; sau khi nhập công thức, dữ liệu sẽ tự điền vào cả B2 và C2. Tiếp theo, kéo từ ô đầu tiên đã điền (Bob) xuống ô cuối cùng trong cột (B12).
Tất cả tên và họ sẽ tự động được điền.
5. Đếm ô nhanh chóng bằng COUNTIF
Không cần đếm ô thủ công nữa. Với công thức COUNTIF, bạn có thể đếm các ô đáp ứng tiêu chí cụ thể.
=COUNTIF(range, criteria)
Công thức này không chỉ giúp bạn tiết kiệm thời gian mà còn đảm bảo các con số chính xác.
Trong ví dụ đơn giản hóa dưới đây, tôi muốn đếm tất cả các ô trong bảng tính có nhắc đến “Betty.” Cụ thể: =COUNTIF(B2:B15, “Betty”).
Nếu dán công thức này vào một ô trống rồi nhấn “Enter,” tôi sẽ nhận được kết quả:
6. Áp dụng một công thức cho nhiều ô bằng ARRAYFORMULA
Bạn sẽ thích công thức này.
ARRAYFORMULA cho phép bạn viết công thức một lần rồi áp dụng cho cả hàng hoặc cột. Chỉ cần lấy công thức muốn dùng và đặt bên trong ARRAYFORMULA:
=ARRAYFORMULA(array_formula)
Tôi có một công thức IF muốn áp dụng cho cột D bên dưới để xác định keyword nào nhận được hơn 5,000 lượt truy cập mỗi tháng. Tôi chọn ô đầu tiên (D2) rồi dán công thức vào thanh công thức.
Tôi chỉ cần đảm bảo đưa ô đầu tiên và ô cuối cùng vào công thức, tức là B2 và B24. Sau đó, tôi nhấn Enter để tự động điền cả cột.
=ARRAYFORMULA(IF(B2:B24>5000, "Yes","No"))
7. Dùng LEN để đếm ký tự
Công thức LEN giúp bạn đếm số ký tự trong một ô.
=LEN(insertcell)
Giả sử bạn đang xem lại meta description và tiêu đề trang của tất cả các trang trên website trong bảng tính, đồng thời muốn đếm số ký tự để đảm bảo độ dài đều phù hợp.
Nhấp vào một ô trống và nhập công thức, thay “insertcell” bằng ô chứa các ký tự bạn muốn đếm.
Trong ví dụ này, A2 là ô đầu tiên tôi muốn đếm, vì vậy công thức sẽ là =LEN(A2). Sau đó, tôi nhấn Enter và số ký tự sẽ xuất hiện.
Sau đó, tôi có thể áp dụng công thức này cho phần còn lại của cột bằng cách kéo từ ô đầu tiên xuống ô cuối cùng.
8. Dễ dàng sắp xếp các ô bằng SORT
Bạn muốn sắp xếp các ô? Đúng vậy, chúng tôi có công thức dành cho việc này. Với công thức SORT, bạn có thể sắp xếp các hàng theo giá trị trong một hoặc nhiều cột. Dùng công thức =SORT(range, sort_column, is_ascending)
- Range là nhóm ô bạn muốn sắp xếp.
- Sort_column là số thứ tự (chỉ mục) của cột bạn muốn sắp xếp (A=1, B=2, v.v.).
- Nếu muốn sắp xếp các giá trị theo thứ tự tăng dần, chỉ cần chọn “TRUE” cho is_ascending. Nếu muốn sắp xếp các giá trị theo thứ tự giảm dần, hãy chọn “FALSE”.
Hãy xem một ví dụ đơn giản.
Tôi muốn biết keyword nào trong số các keyword hàng đầu có search volume cao nhất. Công thức của tôi như sau:
=SORT(A2:B11,2,FALSE)
Công thức này rất hữu ích khi bạn muốn sắp xếp giá trị trong nhiều cột. Trong ví dụ tiếp theo, tôi muốn xác định những email có hiệu suất cao nhất.
Tôi cần thiết lập công thức để những email có tỷ lệ mở cao nhất và Số lượt hủy đăng ký thấp nhất sẽ xuất hiện đầu tiên. Công thức của tôi cho việc này là =SORT(A2:C9,2,FALSE,3,TRUE)
9. Nhập dữ liệu khác bằng IMPORTRANGE
IMPORTRANGE tương tự như VLOOKUP vì cho phép bạn nhập dữ liệu từ một bảng tính Google Sheets khác.
=IMPORTRANGE(spreadsheet_url, range_string)
- Thay spreadsheet_url bằng URL của bảng tính khác, sau đó chỉ định phạm vi ô mà bạn muốn nhập.
Nếu bảng tính mà bạn muốn lấy dữ liệu có nhiều tab, bạn cần chỉ định tab cần dùng.
Giả sử bạn đang lấy dữ liệu từ tab có tên “Keywords,” và muốn lấy dữ liệu từ các ô A2 đến H150. Công thức của bạn sẽ như sau:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1wID3VSaNi1zbiaQPZ”, “Keywords!A1:H150”)
10. Chỉ giữ lại dữ liệu UNIQUE
Những ai thường xuyên làm việc với nhiều dữ liệu đều biết rõ thông tin trùng lặp xuất hiện thường xuyên đến mức nào. Công thức UNIQUE có thể giúp bạn xử lý việc này.
Đôi khi, bạn cần gộp nhiều thành viên trong một gia đình thành một hộ gia đình, hoặc một người xuất hiện hai lần trong hệ thống vì đăng ký nhận bản tin nhiều lần bằng các địa chỉ email khác nhau.
Có vô số cách khiến dữ liệu bị trùng lặp, nhưng vấn đề này có thể nhanh chóng biến các bảng tính và hệ thống quản lý nội dung của chúng ta thành những mớ hỗn độn khổng lồ. Công thức này có thể giúp bạn giải quyết vấn đề đó.
=UNIQUE(range)
Trong ví dụ này, tôi nhấp vào một ô trống và nhập công thức =UNIQUE(A2:C24) để bao gồm cả ba cột dữ liệu, rồi nhấn Enter.
Thế là xong!
Tất cả các trường trùng lặp đều bị loại bỏ, chỉ còn lại các trường riêng biệt.
11. SEARCH một giá trị cụ thể
Bạn có thể dùng công thức SEARCH để kiểm tra một tập dữ liệu nhằm tìm một giá trị cụ thể.
Ví dụ, giả sử bạn có danh sách tất cả URL trên website trong một bảng tính và muốn xác định toàn bộ bài viết trên blog.
Thay vì rà soát và chọn thủ công từng bài đăng trên blog, bạn có thể dùng công thức này:
=SEARCH(search_query, text_to_search)
Để đánh dấu tất cả bài đăng blog là “Blog,” bạn cần kết hợp công thức trên với công thức IF mà chúng ta đã đề cập ở trên.
Công thức có thể trông như sau: =IF(SEARCH("/blog/",A2),"Blog","")
Công thức sẽ lọc ra tất cả URL có chứa /blog/, bắt đầu từ ô A2.
Các URL không chứa /blog/ sẽ trả về thông báo lỗi (#VALUE!). Để tránh điều này, bạn có thể lồng công thức vào một công thức IFERROR nhằm gắn nhãn “Other” cho các URL không thuộc blog (hoặc để trống), đồng thời giữ dữ liệu gọn gàng và không có lỗi.
=IFERROR (IF(SEARCH("/blog/",A2),"Blog",""), "Other")
12. Thêm ngày bằng TODAY
Nhập ngày vào bảng tính Google Sheets không còn phải là việc khó chịu nữa. Công thức này sẽ giúp bạn điền các khoảng ngày cho báo cáo nhanh hơn bao giờ hết.
Nếu muốn thêm ngày hôm nay, bạn chỉ cần thêm công thức sau vào ô:
=TODAY()
Bạn thậm chí không cần biết ngày hiện tại; công thức sẽ tự động lấy ngày đó cho bạn.
Nếu muốn tìm ngày cách đây hai hoặc ba tuần mà không cần mở lịch, chỉ cần thêm số ngày muốn lùi vào công thức.
=TODAY() - 14
Công thức này sẽ hiển thị ngày chính xác cách đây 14 ngày.
Ngược lại, nếu muốn lấy một ngày trong tương lai, chỉ cần cộng số ngày thay vì trừ.
=TODAY() + 7
Trong trường hợp trên, bạn sẽ thấy ngày sau hôm nay 7 ngày.
Khá tiện lợi, phải không?
13. Gộp dữ liệu ô bằng CONCATENATE
Chúng ta đã tìm hiểu cách tách dữ liệu vào các ô, nhưng còn việc gộp dữ liệu ô thì sao? Công thức này rất hữu ích khi làm việc với URL.
Đôi khi, khi tải xuống một danh sách URL, địa chỉ website không được định dạng theo cách bạn muốn.
Có thể bạn đã tải xuống một danh sách từ Google Analytics nhưng thiếu domain. Hoặc URL không có protocol ở đầu.
Thay vì cập nhật từng URL theo cách thủ công, bạn có thể dùng công thức CONCATENATE.
=CONCATENATE(range)
Sau khi hoàn tất thao tác đầu tiên, chỉ cần kéo xuống từ ô đầu tiên bạn đã nhập công thức, các ô còn lại sẽ tự động được điền.
Nếu bảng tính không có sẵn một tiền tố hoặc hậu tố riêng lẻ, bạn cũng có thể tự thêm mà không cần tạo thêm một cột ô cho nội dung đó — công thức sẽ có dạng =CONCATENATE("https://",A2)
14. Tính tổng theo danh mục bằng SUMIF
Công thức SUMIF rất hữu ích để cộng các giá trị theo danh mục dựa trên một tiêu chí cụ thể.
=SUMIF(range, criterion, [sum_range])
Trong ví dụ dưới đây, tôi muốn cộng lượt xem trang của các bài blog có hiệu suất cao nhất để xác định danh mục blog phổ biến nhất. Tôi có thể nhanh chóng cộng lượt xem trang bằng cách áp dụng công thức này cho từng danh mục.
Để tính lượt xem trang trong danh mục SEO:
- Ô range bao gồm cột ô bên dưới "Category".
- "SEO" là criterion.
- Ô sum_range bao gồm các ô trong cột bên dưới "Page Views".
Công thức sẽ cộng tổng lượt xem trang trong danh mục SEO: 36,834. Tôi sẽ dùng chính công thức này cho ba danh mục còn lại và chỉ thay đổi tiêu chí cho từng danh mục.
Không cần sắp xếp hay thực hiện thêm phép tính nào, tôi vẫn có thể xem tổng lượt xem của từng danh mục blog và dễ dàng xác định danh mục bài đăng blog nào có nhiều lượt xem trang nhất.
Lưu lại những công thức hữu ích này
Giờ đây, bạn đã biết một số công thức và hàm hữu ích có thể dùng trong Google Sheets để giúp công việc với bảng tính trở nên DỄ DÀNG HƠN RẤT NHIỀU khi làm SEO chuyên nghiệp.
Hãy đánh dấu trang này để lưu sẵn các công thức, nhờ đó bạn có thể nhanh chóng sao chép và dán khi làm việc. Chúng tôi hy vọng những công thức này sẽ giúp bạn báo cáo, sắp xếp và trình bày dữ liệu nhanh chóng, chuyên nghiệp hơn nhiều (đồng thời giúp bạn thể hiện một chút kỹ năng mới của mình)!
Đưa góc nhìn thành kế hoạch.
Chia sẻ website và mục tiêu để LionTech tư vấn giải pháp phù hợp.
Đăng ký tư vấn ↗