[Google Sheets] 6 hàm hữu ích trong Google Sheets có thể bạn chưa biết

Hàm JOIN dùng để sâu chuỗi các giá trị thành một văn bản giúp thuận tiện cho việc sử dụng. Hoặc đơn giản là tổng hợp một vài giá trị chủ chốt hoặc một vài HTML.

Gõ & để kết nối các giá trị của các ô khác nhau và sử dụng dấu ngoặc kép với bất cứ văn bản nào bạn muốn chèn.

Ví dụ, chúng ta sử dụng phép tính sau:

=A1 & " " & B1 & " and " & A2 & " " & B2

và kết quả là "4 Apples and 5 Pears"

Sử dụng hàm JOIN là thích hợp nhất khi liên kết nhiều giá trị. Bạn chỉ cần chỉ ra ký tự bạn muốn thêm vào giữa các giá trị và các giá trị ô bạn muốn.

Ví dụ:

=JOIN(",",A1:A5)

Chúng ta có:

1,2,3,4,5

Với các dữ liệu cố định, các bảng tính càng hoạt động dễ dàng. Tuy nhiên, khi thêm các dữ liệu mới thường xuyên, chẳng hạn như mỗi tuần thêm dòng dữ liệu mới, sẽ cần phải chỉnh sửa liên tục để duy trì hoạt động của các hàm trong bảng.

Ví dụ, hãy tưởng tượng bạn luôn phải tính sự thay đổi giữa ô cuối trong bảng với giá trị phía trước. Và thật khó để có kết quả tính trong ô tương tự ở mỗi lần thay. Dùng hàm LAST dường như là rất thích hợp, nhưng không. Hàm INDEX mới là giải pháp chúng ta cần. Bởi nó cho phép bạn truy tìm giá trị của một ô bằng cách xác định vị trí cột và dòng trong mảng điều kiện.

=INDEX(A:A,1,1)

Kết hợp INDEX với COUNTA bạn có thể tạo ra một phép tính để truy xuất giá trị cuối cùng của một cột.

=INDEX(A:A,COUNTA(A:A),1)

Một trong những tính năng vượt bậc của Google Sheets là hàm nhập. Hàm này cho phép kéo dữ liệu từ các nguồn khác nhau như XML, HTML, RSS - CSV vào bảng tính của bạn phù hợp để nhập cách danh sách các bài đăng trên blog, hàng tồn kho hoặc dữ liệu khác từ các dịch vụ khác. Ví dụ, bạn có thể nhập hàng loạt link từ URL như sau:

=IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")

Nhập nội dung danh sách hoặc bảng từ URL xác định sử dụng:

=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India", "table", 4)

Nhập RSS hoặc Atom:

=IMPORTFEED("http://news.google.com/?output=atom")

Nhập các nội dung của tâp tin CSV:

=IMPORTDATA("http://www.census.gov/2010census/csv/pop_change.csv")

Sự pha trộn giữa văn bản và các tính năng tự động của Google Sheet, hàm nhập dữ liệu đã trở thành công cụ mạnh để sàng lọc và thiết lập dữ liệu từ nguồn công cộng.

Với ví dụ sau sẽ chứng minh được các hàm tìm kiếm của Google Sheet là tốt nhất. Chúng cho phép tìm kiếm để chuỗi của một từ và sau đó đọc giá trị sang cột hoặc dòng tương đương. Điều này thực sự hữu ích nếu bạn có các bộ dữ liệu khác nhau có cùng đối tượng trong bảng tính. Ví dụ, các dòng của dữ liệu về sản phẩm, nhân sự hoặc dự án.

Ở ví dụ dưới đây, hãy tưởng tượng bạn muốn theo dõi sự thay đổi số lượng táo, cam, lê của tháng Giêng và tháng Hai. Thứ tự của những trái cây có sẵn thay đổi theo từng tháng vì vậy không được sử dụng phép tính trừ cho các ô.

Thay vào đó, hàm Vlookup tìm kiếm bảng dữ liệu theo hàng dọc cho đến khi nó tìm ra điểm chung của từ và sau đó đọc theo hàng ngang để tìm giá trị tương ứng trong cột liền kề. Vlookup viết tắt của Vertical Lookup bởi vì nó tìm từ theo hàng dọc và sau đó theo hàng ngang để cho ra giá trị trong khi Hlook up viết tắt của Horizontal Lookup bởi nó tìm từ theo hàng ngang và sau đó cho giá trị theo hàng dọc.

Đầu tiên, với Vlookup sử dụng bảng và phép tính sau:

=VLOOKUP(F2,$A$2:$B$6,2,false)

Trong đó, "F2" giá trị cần tìm của "Apple" ở ô F2. Vì sử dụng Vlookup cho nên"$A$2:$B$6" của phép tính để chỉ cho Google Sheet tìm dọc theo bảng dữ liệu của tháng Giêng. "2" chỉ ra tìm "Apple" ở cột thứ 2. "False" là giả định nếu không tìm thấy giá trị tương đương, thì chúng bỏ qua sẽ tìm ở vị trí khác. Cuối cùng, hoàn thành phép tính tìm kiếm Apple trong bảng dữ liệu tháng Giêng và truy tìm giá trị 1003 trong cột thứ 2.

Để tính sự thay đổi, chúng ta cần lấy hàm tìm kiếm của tháng Hai trừ đi hàm tìm kiếm của tháng một do đó, có phép tính như sau:

=VLOOKUP(F2,$C$2:$D$6,2,false)-VLOOKUP(F2,$A$2:$B$6,2,false)

Phép tính tìm kiếm thứ 2 tương tự như phép tính ở phía trên, nhưng bây giờ chúng ta nhập dữ liệu tháng Hai và lấy chúng trừ dữ liệu tháng Giêng. Vì vậy, viết phép tính tìm kiếm của dữ liệu tháng Hai tương tự như của tháng Giêng chỉ thay "$A$2:$B$6" cho "$C$2:$D$6". Bây giờ chúng sẽ lấy số lượng táo của tháng hai (785) từ đi số lượng của tháng Giêng (785) và truy xuất ra giá trị -218.

Tương tự như vậy, Hlookup biểu diễn hàm tương đương nhưng đọc theo hàng ngang và suy tìm theo hàng dọc.

SUMIF và COUNTIF dễ hơn hàm tìm kiếm một chút. Nếu mệnh đề logic trong CountIF hoặc SumIF là đúng, Google Sheets có thể đếm số lượng các trường hợp hoặc tổng hợp giá trị tương đương.

Xem ví dụ dưới đây, bạn có thể đếm số lượng táo được bán với phép tính như sau:

=COUNTIF(B2:B10,"Apple")

Nó thể hiện rằng: Đếm số lượng các trường có từ Apple trong từ ô B2 đến B10.

Bạn có thể tính tổng trọng lượng táo đã bán sử dụng hàm SumIF.

=SUMIF(B2:B10,"Apple",C2:C10)

Nó tìm kiếm số từ Apple được nhập trong cột B sau đó tính các giá trị tổng của ô tương đương trong cột C.

Bảng Pivot là hàm hữu dụng cuối cùng tôi muốn nhắc tới trong bài này. Bạn có thể mở bảng Pivot bằng cách chọn dữ liệu bạn muốn sử dụng sau đó kích Data > Pivot Table.

Báo cáo Pivot giúp dễ dàng nhập, lọc, phân loại và tổng hợp dữ liệu thông qua giao diện người dùng. Ví dụ, với bảng Pivot bạn sẽ có kết quả tương tự giống với CountIF và SumIF cho tất cả các loại quả mà không dùng bất cứ một phép tính nào. Ngoài đếm và tổng hợp, nó có thể tính các giá trị khác như trung bình, phương sai như ảnh dưới đây.

Bạn có thể dùng bảng Pivot để tái cấu trúc dữ liệu. Nhìn vào ảnh phía dưới đây, số hóa đơn nằm ở cột đầu tiên với mỗi cột là một loại quả. Sau đó, từng ô chỉ số lượng từng loại theo thứ tự.