Google Sheets được đánh giá là một công cụ vô giá trong nhiều lĩnh vực công việc như bán hàng, quản lý, phân tích và kế toán. Đây là một kho tàng thực sự để xử lý dữ liệu.
Nhưng bạn có thực sự sử dụng hết tất cả các tính năng của Google Sheets chưa? Với nhiều tùy chọn như vậy, ban đầu có thể cảm thấy áp đảo. Tuy nhiên, chỉ cần học một số công thức cơ bản, bạn có thể tối đa hoá tiềm năng của công cụ mạnh mẽ này và tập trung vào những thứ quan trọng.
Dưới đây là danh sách 20 công thức Google Sheets cần thiết để giúp bạn tiến xa hơn. Dù bạn là một người dùng thường xuyên hay một người mới hoàn toàn, những mẹo nhỏ này sẽ giúp bạn tiết kiệm thời gian quý báu, để bạn tập trung vào những điều quan trọng.
Công thức Google Sheets là gì?
Công thức Google Sheets cho phép bạn thực hiện các phép tính với dữ liệu mà bạn đã nhập vào một bảng tính. Chúng giúp bạn sắp xếp danh sách một cách hiệu quả, tổng hợp các số cụ thể, trích xuất thông tin hoặc thực hiện các tác vụ phức tạp hơn như tạo biểu đồ.
Mỗi công thức bắt đầu từ một ô trống với ký hiệu “=” tiếp theo là loại tính toán mà bạn muốn thực hiện và các ô bạn muốn tham chiếu. Ví dụ, công thức “= TỔNG(A1:A20)” sẽ tổng hợp tất cả các số từ ô A1 đến A20 trong ô bạn viết công thức.
Như bạn sẽ thấy, nhiều công thức yêu cầu sử dụng thông tin trong dấu ngoặc kép. Đôi khi, tốt hơn là bạn nhập các dấu ngoặc kép này thủ công, vì sao việc sao chép và dán từ các nguồn khác có thể gây ra lỗi. Để tham chiếu giá trị của ô cụ thể trong công thức, bạn chỉ cần gõ địa chỉ ô, ví dụ A1 hoặc B2. Bạn cũng có thể tham chiếu một phạm vi ô, chẳng hạn như A1:A10 hoặc B1:C5.
Tại sao Công thức Google Sheets hữu ích?
Khi bạn xử lý một lượng lớn thông tin, các phép tính có thể trở nên phức tạp nhanh chóng. Hơn nữa, mọi sai sót nhỏ cũng có thể ảnh hưởng lớn và làm giảm hiệu suất. Tốc độ và hiệu quả của công thức Google Sheets giúp đơn giản hóa công việc đáng buồn liên quan đến quản lý dữ liệu, công việc có thể phức tạp và tốn thời gian.
Dù bạn có thể biết một số hàm thông thường trong bảng tính như tổng các ô hoặc định dạng danh sách, nhưng bạn có biết rằng giống như Google Scripts, bạn cũng có thể sử dụng công thức Google Sheets để thực hiện nhiều tác vụ phức tạp hơn?
Từ nhập dữ liệu XML trực tiếp từ một trang web đến việc tạo biểu đồ và thậm chí sử dụng Google Translate trực tiếp trong bảng tính của bạn, có một công thức cho mọi tình huống!
20 công thức tốt nhất cho Google Sheets
#1. SPLIT()
Công thức SPLIT() cho phép bạn tách thông tin từ một ô thành nhiều ô.
Ví dụ, giả sử bạn có một danh sách gồm 10 tên đầy đủ và bạn muốn tách tên và họ vào các cột riêng biệt.
Giả sử tên đầy đủ được liệt kê trong cột A. Bằng cách gõ công thức sau vào cột B, nó sẽ tự động tách và đặt tên và họ vào hai cột tiếp theo, B và C:
=SPLIT(A2, " ")Trong công thức này, bạn phải sử dụng dấu cách giữa hai dấu ngoặc kép để chỉ ra rằng hai tên phải được tách bằng dấu cách. Ví dụ, nếu trong danh sách của bạn có dấu phẩy giữa các tên, như “SMITH, JOHN”, bạn sẽ sử dụng dấu phẩy và một dấu cách thay vì dấu cách đơn: “,”. Và bạn chỉ cần kéo các ô xuống dưới để điền tên còn lại, và bạn đã làm xong!
#2. JOIN()
Ngược lại, nếu bạn muốn kết hợp các phần dữ liệu riêng biệt thành một ô duy nhất, công thức JOIN() là công thức bạn cần!
Giá sử bạn có cùng danh sách tên như trên, nhưng lần này đã tách riêng các phần tên và họ vào hai cột. Trong một ô mới, bạn gõ:
=JOIN(" ", A2:B2)Ở đây, dấu phân cách mà bạn đặt trong dấu ngoặc kép sẽ được sử dụng để phân tách nội dung của hai ô trong ô mới. Trong trường hợp này, đó là một dấu cách, nhưng bạn có thể thay thế nó bằng dấu phẩy hoặc thậm chí bỏ nó đi nếu bạn chỉ muốn kết hợp văn bản của hai ô hoặc một phạm vi ô.
Sau đó, bạn chỉ cần kéo ô màu xanh dương xuống dưới để áp dụng cùng một công thức cho phần còn lại của danh sách!
#3. SUM()
SUM() là một trong những công thức Google phổ biến nhất nhưng đơn giản nhất để tổng hợp các giá trị số trong một phạm vi ô đã chọn.
Bạn có thể nhập công thức này thủ công bằng cách gõ =SUM() và phạm vi ô bạn muốn tổng hợp, được chỉ định giữa các cặp dấu ngoặc. Nếu bạn nhập nó ở cuối danh sách các giá trị số, Sheets sẽ tự động phát hiện các số bạn muốn tổng hợp, nhưng bạn có thể điều chỉnh nó tùy theo nhu cầu của mình.
Hoặc, bạn cũng có thể chọn các ô bạn muốn tổng hợp và chọn hàm SUM trong trình đơn thả xuống.
#4. COUNTA()
Công thức COUNTA() đếm số lượng giá trị được nhập trong một tập hợp, cho dù đó là một danh sách các số hoặc từ. Điều này thực sự hữu ích nếu bạn có một danh sách các kích thước khác nhau và bạn cần biết có bao nhiêu phần tử trong mỗi danh sách. Cú pháp của công thức trông như sau:
=COUNTA(A2:A15)Lần nữa, bằng cách gõ công thức này trong một ô mới dưới một tập hợp các ô, Sheets sẽ tự động cho rằng bạn muốn đếm số lượng ô ở trên và chọn chúng cho bạn. Bạn chỉ cần nhấn Enter và xong!
#5. SPARKLINE()
Không phải lúc nào bạn cũng muốn chèn các biểu đồ phức tạp vào bảng tính, đặc biệt khi bạn làm việc với nhiều tập dữ liệu. Công thức SPARKLINE() cho phép bạn nhanh chóng và dễ dàng tạo ra các biểu đồ tóm tắt một cách dễ nhìn, phù hợp hoàn hảo với một ô duy nhất. Chúng có thể có dạng biểu đồ đường, biểu đồ cột từ một ô duy nhất hoặc biểu đồ cột.
Ví dụ, nếu bạn có một dãy số trong các ô A2 đến E2, bạn sẽ gõ:
=SPARKLINE(A2:E2)Và để hiển thị một biểu đồ cột, cột, hoặc thắng / thua, chỉ cần sử dụng một trong những phần mở rộng sau:
=SPARKLINE(A2:E2,{"charttype","bar"})
=SPARKLINE(A2:E2,{"charttype","column"})
=SPARKLINE(A2:E2,{"charttype","winloss"})#6. DETECTLANGUAGE()
Công thức này làm chính xác điều đó! Cho dù bạn cần xác định ngôn ngữ từ văn bản trong một ô duy nhất hoặc trong một danh sách với các ngôn ngữ khác nhau, bạn có thể làm điều đó ngay trong bảng tính của mình.
Chỉ cần gõ =DETECTLANGUAGE() theo sau là ô mà bạn muốn phát hiện ngôn ngữ, và Sheets sẽ xác định ngôn ngữ và hiển thị nó dưới dạng mã ngôn ngữ (một phần viết tắt cho mỗi ngôn ngữ, ví dụ “en” cho tiếng Anh, “fr” cho tiếng Pháp, vv.).
Nếu bạn có một danh sách, chỉ cần kéo tô đen xuống và tất cả chúng sẽ xuất hiện như ma thuật!
#7. SORT()
Công thức SORT() hoàn hảo để sắp xếp lại danh sách dữ liệu theo thứ tự số hoặc chữ cái. Trong công thức này, chúng ta sử dụng từ “TRUE” cho các danh sách tăng dần (trong trường hợp của số, chúng sẽ tăng khi bạn di chuyển xuống danh sách) hoặc “FALSE” để tạo danh sách giảm dần (trong trường hợp của số, chúng sẽ bắt đầu từ trên xuống danh sách). Tuy nhiên, nếu bạn không gõ gì cả, Sheets sẽ giả định “TRUE”.
Hãy xem xét danh sách số trong cột A mà bạn muốn sắp xếp theo thứ tự tăng dần. Trong cột trống B, bạn gõ =SORT(A1:A17) và các số sẽ xuất hiện ở đó, hoàn toàn được sắp xếp theo thứ tự tăng dần.
#8. SUMIF()
Giống như SUM(), công thức SUMIF() tính tổng các ô mà bạn đã chọn. Nhưng điều đặc biệt là bạn có thể đặt các điều kiện cụ thể, như được thể hiện trong cú pháp của công thức:
=SUMIF(range, criterion, [sum_range])Sử dụng danh sách này không được sắp xếp của điểm số đội, chúng ta có thể sử dụng SUMIF để chỉ tính tổng các điểm từng đội. Bắt đầu từ “xanh” được gõ trong D2, chúng ta viết công thức sau:
=SUMIF(B2:B14,D2,A2:A14)Ở đây, B2:B14 là phạm vi các ô chúng tôi đang phân tích, D2 là điều kiện chúng tôi muốn tìm thấy (“xanh”), và A2:A14 là các số chúng tôi muốn tổng hợp.
Sau đó, bạn chỉ cần kéo ô màu xanh dương để thực hiện cùng một thao tác với các ô còn lại.
#9. ARRAYFORMULA()
ARRAYFORMULA() cho phép bạn áp dụng cùng một công thức vào một mảng ô cùng một lúc. Chỉ cần gõ ‘=ARRAYFORMULA()’ và sau đó là công thức bạn muốn áp dụng trong các dấu ngoặc đơn.
Ví dụ, nếu bạn muốn nhân các số trong các ô A1:A10 với 2, bạn sẽ nhập “=ARRAYFORMULA(A1:A10*2)”.
Ở đây, dấu “*” được sử dụng để chỉ ra một phép nhân, nhưng bạn có thể thay thế nó bằng các phương trình khác tùy thuộc vào nhiệm vụ của bạn.
#10. SUBSTITUTE()
Nếu bạn cần thay đổi một từ cụ thể trong một ô, công thức SUBSTITUTE() có thể tiết kiệm rất nhiều thời gian! Chỉ cần gõ ‘=SUBSTITUTE()’, sau đó là ô mà bạn muốn thay đổi, từ bạn muốn thay đổi và từ bạn muốn thay thế, tất cả trong dấu ngoặc đơn.
Ví dụ, nếu bạn muốn thay thế từ “new” bằng từ “New” trong “new York” trong ô A2, bạn sẽ gõ:
=SUBSTITUTE(A2, "new", "New")#11. VLOOKUP()
VLOOKUP cho phép bạn tìm kiếm một giá trị cụ thể trong một cột của một bảng, sau đó trả về một giá trị từ cột khác trong cùng hàng.
Hãy giả sử bạn có một bảng với hai cột, “Mục” và “Giá”, và bạn muốn tìm giá của một sản phẩm cụ thể. Trong trường hợp bạn tìm kiếm “microphone”, bạn sẽ gõ:
=VLOOKUP("microphone", A2:B5, 2)Công thức này sẽ tìm giá trị “microphone” trong cột đầu tiên của phạm vi A2:B5 và trả về giá trị từ cột thứ hai của cùng hàng, trong trường hợp này là giá.
Ở đây, “2” chỉ ra cột để trích xuất thông tin về microphone, vì nó là cột thứ hai trong phạm vi đã chọn, nếu tính từ trái sang phải.
#12. SEARCH()
Công thức SEARCH trong Google Sheets cho phép bạn tìm vị trí của một phần văn bản cụ thể trong một chuỗi văn bản lớn hơn.
Ví dụ, giả sử chúng ta muốn tìm từ “thung lũng” trong chuỗi văn bản của ô A1.
Bằng cách nhập =SEARCH("thung lũng", A1), chúng tôi yêu cầu chương trình xác định vị trí của từ “thung lũng” trong ô A1. Trong ví dụ này, vị trí là 7, điều này có nghĩa là có 6 ký tự trước nó trong chuỗi văn bản khi tính từ trái sang phải (với ký tự “h” trong “đồi” được đánh số 1). Mặc dù ví dụ này ngắn gọn, nó thực sự hữu ích khi làm việc với các chuỗi văn bản dài hơn rất nhiều.
#13. IF()
Công thức IF() cho phép bạn kiểm tra một điều kiện và trả về một giá trị cụ thể nếu điều kiện đó đúng và một giá trị khác nếu điều kiện đó sai.
Ví dụ, nếu bạn muốn ghi lại xem các số từ một danh sách trong cột A có lớn hơn 10 hay không, bạn có thể gõ =IF(A2>10, "yes", "no").
Ở đây, dấu “>” được sử dụng và công thức sẽ trả về “yes” nếu giá trị của ô A2 lớn hơn 10 và “no” nếu giá trị là nhỏ hơn hoặc bằng 10. Để áp dụng cho toàn bộ danh sách, chỉ cần kéo ô màu xanh dương xuống dưới và nó sẽ tự động điền vào!
#14. CHAR()
Công thức nhanh nhẹn này cho phép bạn nhập mã Unicode để tạo ra một ký tự cụ thể. Chỉ cần gõ =CHAR() sau đó là số Unicode bạn muốn. Ví dụ:
=CHAR(8226)sẽ trả về một dấu chấm đầu dòng.
Hoặc có thể bạn cần một khuôn mặt cười? Đó sẽ là:
=CHAR(128578)#15. TRANSPOSE()
Bạn có một tập hợp dữ liệu được sắp xếp theo cột dọc mà bạn cần chuyển đổi thành hàng ngang hoặc ngược lại? Công thức TRANSPOSE là công thức bạn cần!
Ví dụ, giả sử bạn có bảng sau:
Để dễ dàng di chuyển tất cả các dữ liệu vào một bảng ngang, chỉ cần gõ ‘=TRANSPOSE’ vào một ô mới và kéo ô màu xanh dương trên bảng gốc. Sheets sẽ tự động chuyển đổi tất cả các dữ liệu, bao gồm cả tiêu đề cột.
#16. IMPORTXML()
Bạn có biết bạn có thể dễ dàng nhập dữ liệu trực tiếp từ một trang web vào Google Sheets? Đây đặc biệt hữu ích cho web scraping, một phần quan trọng trong nghiên cứu.
Giả sử bạn muốn nhập tất cả các liên kết được sử dụng trên một trang web cụ thể. Trong một ô trống, chỉ cần gõ công thức này, sau đó là URL của trang web trong dấu ngoặc kép và các dữ liệu bạn muốn nhập. Trong trường hợp này, đó là “//@href”, nó sẽ thu thập tất cả các liên kết.
=IMPORTXML("https://acuto.io/","//@href")#17. FILTER()
Công thức FILTER() cho phép bạn lọc và chỉ trích các dữ liệu mà bạn muốn từ một danh sách hoặc bảng.
Ví dụ, hãy cứu một bảng chứa thông tin về các mặt hàng thực phẩm và bạn muốn tạo một bảng mới chỉ với các dữ liệu liên quan đến một danh mục cụ thể, trong trường hợp này là bỏng ngô.
Ở đây, công thức =FILTER(A2:C10,A2:A10="Snack") cho biết cho Sheets lọc dữ liệu từ các ô B2:C10 bằng cách sử dụng từ “Snack” từ cột A2:A10 và chỉ đặt các thông tin đó vào một bảng mới, như ví dụ dưới đây:
#18. QUERY()
QUERY() có thể là công thức Google Sheets linh hoạt và mạnh mẽ nhất. Bạn có thể sử dụng nó để thực hiện nhiều tác vụ khác nhau, thậm chí thay thế cho các công thức khác! Điều đó bởi vì nó cho phép bạn sử dụng các hàm SQL cơ bản.
Công thức cơ bản cho hàm QUERY là:
=QUERY('interval', 'requête')Trong đó “interval” là phạm vi các ô để truy vấn và “requête” là một chuỗi chứa truy vấn.
Các từ khóa truy vấn quan trọng là SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, LABEL.
Tùy thuộc vào nhiệm vụ, bạn có thể sử dụng các kết hợp khác nhau của các hàm này, nhưng chúng phải xuất hiện theo thứ tự đúng này.
Ví dụ, nếu bạn có một tập hợp dữ liệu trong phạm vi A2:B10 và bạn muốn trích xuất tất cả các hàng với giá trị cột B lớn hơn 100, bạn sẽ sử dụng công thức sau:
=QUERY(A1:B10, "SELECT * WHERE B > 100")Trong ví dụ này, các từ khóa truy vấn là “SELECT” và “WHERE”, và dấu “*” cho Sheets biết để chọn tất cả cột từ phạm vi dữ liệu. Mặc dù ví dụ này tương đối đơn giản, tính linh hoạt của hàm QUERY có nghĩa là nó có thể được sử dụng để thực hiện nhiều tác vụ phức tạp khác, làm cho nó trở thành một trong những công thức quan trọng nhất để nắm vững!
#19. GOOGLETRANSLATE()
Một trong những lợi ích của việc sử dụng Google Sheets là bạn có thể truy cập vào một số công cụ hữu ích của Google khác ngay trong bảng tính của bạn. Hàm GOOGLETRANSLATE() chính là một ví dụ về điều này và có thể dễ dàng dịch văn bản trong một ô hoặc trên một tập hợp ô.
Hãy giả sử rằng bạn có một danh sách bằng tiếng Anh trong cột A cần được dịch sang tiếng Tây Ban Nha để sử dụng. Trong cột B, bạn chỉ cần nhập công thức, sau đó là ô bạn muốn dịch và các ngôn ngữ bạn muốn dịch sang.
=GOOGLETRANSLATE(A1,"en","es")Lưu ý rằng Google Sheets sử dụng các mã ngôn ngữ ngắn gọn, vì vậy tiếng Anh trở thành “en” và tiếng Tây Ban Nha trở thành “es”. Như với các công thức khác, sau đó bạn có thể kéo ô màu xanh dương xuống dưới để hoàn thành danh sách.
#20. IMPORTRANGE()
Cuối cùng, công thức này là lý tưởng khi bạn muốn trích xuất một dải dữ liệu cụ thể từ một bảng tính Google sang một bảng tính khác.
Bạn sẽ cần sao chép URL của bảng tính bạn muốn nhập và ghi lại phạm vi ô mà bạn muốn trích xuất.
Ví dụ:
=IMPORTRANGE("https://docs.google.com/spreadsheets/example1", "A1:B10")Bạn có thể cần cho phép truy cập vào bảng tính nguồn. Sau khi bạn làm điều này, dữ liệu đã nhập sẽ được cập nhật tự động mỗi khi dữ liệu nguồn được thay đổi.
Những điểm chính cần ghi nhớ
Bây giờ bạn đã biết nhiều hơn về công thức Google Sheets và bạn có danh sách 20 công thức Google Sheets tốt nhất, hãy sử dụng chúng và quay lại hướng dẫn này khi bạn cần làm mới kiến thức của bạn.
Hãy tóm lại những gì chúng tôi đã đề cập:
- Công thức Google Sheets là một cách dễ dàng để tổ chức dữ liệu trong bảng tính Google Sheets của bạn.
- Bạn có thể sử dụng chúng để thực hiện nhiều chức năng khác nhau, từ các phương trình toán học đơn giản đến các tác vụ phức tạp hơn.
- Chúng luôn bắt đầu bằng dấu “=” và có thể bao gồm một sự kết hợp của số, tham chiếu ô và các toán tử toán học.
- Bạn có thể tham chiếu một ô cụ thể trong công thức bằng cách chỉ định địa chỉ ô, như A1 hoặc B2, hoặc bằng cách chọn một phạm vi ô, chẳng hạn như A1:A10.
- Đừng quên rằng sao chép và dán các công thức kèm theo dấu ngoặc kép từ các nguồn khác có thể gây ra lỗi, vì vậy nếu gặp lỗi, hãy nhập chúng thủ công.
Giờ đến lượt bạn! Hãy sử dụng các công thức này để cải thiện hiệu suất làm việc của mình và tối ưu hoá bảng tính Google Sheets của bạn.
Vậy làm sao bạn có thể bị áp đảo bởi các tùy chọn nhiều của Google Sheets. Hãy tìm hiểu cách sử dụng những công thức cơ bản này và xem chúng làm bạn tiết kiệm thời gian và cải thiện hiệu năng của bạn. Bạn sẽ ngạc nhiên vì Google Sheets có thể mạnh mẽ đến mức nào!
Nếu bạn muốn tìm hiểu thêm về Google Sheets hoặc các công cụ tiếp thị khác, hãy ghé thăm Crawlan.com để biết thêm thông tin và tài nguyên.
Vậy nào, bắt đầu từ bảng tính của bạn và tối đa hoá hiệu suất ngay hôm nay!
Chú ý: Bài viết này là phiên bản thay đổi từ nội dung gốc tại bolamarketing.com
