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

Google Sheets là công cụ cực kì linh hoạt và xuất chúng phối hợp giữa thiết lập và tính toán dữ liệu dưới dạng bảng tính. Nó hoạt động dựa theo tính năng đám mây, vì vậy nó cung cấp đa dụng tương tác, thu thập dữ liệu tự động, cho dù kéo dữ liệu từ APIs bên thứ ba.

Bạn thường làm việc với những bảng tính như Excel, iWork Numbers, Zoho Sheet hoặc Open Office Calc hẳn sẽ dễ dàng sử dụng Google Sheets. Còn nếu bạn mới bắt đầu thích nghi với Sheets, hoặc đang tìm hiểu chương trình Spreadsheet thì dưới này là sáu hàm hữu ích cho dữ liệu của bạn.

Để hiểu thêm các mẹo khác trong Google SpreadSheets hãy ấn vào đây.

1. ARRAYFORMULA

Hàm này giúp bạn giảm bớt thời gian cho vào việc chỉnh sửa các công thức trong bảng tính của mình. Khi cần áp dụng các hàm cho hàng nghìn hàng và cột trong trang tính, bạn nên sử dụng ARRAYFORMULA thay thế cho những hàm không theo mảng (non-array).

Các hàm không theo mảng là những hàm mà bạn tạo trong 1 ô và sau đó sao chép-dán chúng vào một ô khác trong trang tính. Chương trình Google Sheets đủ sáng dạ để sửa đổi công thức theo địa chỉ ô. Tuy nhiên, làm như vậy, bạn sẽ mở tạo ra những vấn đề sau:

• Trang tính trở nên chậm vì các hàm riêng lẻ trong hàng ngàn ô.
• Thực hiện bất kỳ thay đổi nào trong công thức sẽ cần hàng giờ chỉnh sửa.
• Sao chép và dán các công thức không theo mảng vào các ô được chỉ định là một quá trình mệt mỏi.

Giả sử rằng bạn chịu trách nhiệm tạo một tư liệu bảng tính có tên học sinh, điểm theo môn học và tổng điểm của học sinh. Bây giờ, nếu bạn không chỉ phải tính điểm cho một vài học trò trong lớp, mà là tất cả học trò trên toàn thành phố, công thức dựa theo toán tử `+` thường thì sẽ tốn thời gian. Bạn có thể sử dụng ARRAYFORMULA như được đề cập bên dưới.

   =ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F)   

Trong công thức này, độ rộng B2:B chỉ định vô hạn. Bạn có thể làm cho nó hữu hạn bằng phương pháp chỉnh sửa phạm vi thành B2:B10 , v.v…

2. VLOOKUP và HLOOKUP

Với tỉ dụ sau sẽ chứng minh được các hàm kiếm tìm của Google Sheets là tốt nhất. Chúng cấp phép kiếm tì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ự có ích nếu bạn có các bộ dữ liệu không giống 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.

Ở tỉ dụ dưới đây, hãy tưởng tượng bạn muốn theo dấu sự thay đổi con số 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 cấm dùng phép tính trừ cho những ô.

Thay vào đó, hàm VLOOKUP kiếm tìm bảng dữ liệu theo hàng dọc cho tới khi nó tìm ra điểm chung cả 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 lúc 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 từ bảng dữ liệu của tháng Giêng. “2” chỉ ra tìm “Apple” ở cột thứ 2. “False” là giả thiết nếu không tìm thấy giá trị tương đương, thì chúng bỏ lỡ sẽ tìm ở vị trí khác. Cuối cùng, xong xuôi 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 kiếm tìm của tháng Hai trừ đi hàm kiếm tì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ứ hai tựa như như phép tính ở phía trên, nhưng lúc này 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 kiếm tìm của dữ liệu tháng Hai tựa như 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.

3. IMPORTRANGE

IMPORTRANGE là một hàm hữu ích nếu bạn cần lấy dữ liệu từ những trang tính không giống nhau của Google Sheets. Thay vì sao chép dữ liệu từ một trang tính và dán vào trang tính mong muốn khác, bạn có thể sử dụng công thức này để dành dụm thời gian.

Nếu ai đó không cần là bạn nắm giữ trang tính, bạn cần có quyền truy cập vào trang tính trước khi có thể bắt đầu sử dụng hàm IMPORTRANGE.

Trong quá trình kéo dữ liệu từ những trang tính khác nhau, bất kỳ thay đổi dữ liệu nào trên trang nguồn sẽ tự động phản ánh trên trang đích.

Do đó, đây là công thức cần phải có nếu bạn đang lập báo cáo hoặc trang tổng quát bằng phương pháp tìm nguồn dữ liệu từ các thành viên không trùng lặp trong nhóm dự án của mình.

Trong video trên, bạn có thể thấy cách dễ dàng nhập dữ liệu nhân khẩu học Hoa Kỳ từ trang tính này sang trang tính khác. Bạn chỉ cần nhắc đến đến URL của trang tính, tên trang tính và phạm vi dữ liệu mà bạn mong muốn nhập.

Công thức này sẽ không nhập bất kỳ định dạng trực giác nào từ nguồn. Bạn có thể sử dụng cú pháp công thức sau nếu muốn dùng thử:

   =IMPORTRANGE("URL","Sheet1!B3:R11")   

4. IMPORT: Chèn dữ liệu vào Google Sheets

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ừ những nguồn không trùng lặp 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ừ những dịch vụ khác. Ví dụ, bạn cũng có thể có thể nhập đồ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 chế giữa văn bản và các tính năng tự động của Google Sheets, hàm nhập dữ liệu đã trở thành đạt cụ mạnh để sàng lọc và thiết lập dữ liệu từ nguồn công cộng.

5. LEN

Hàm LEN giúp bạn truy xuất độ dài của một chuỗi dữ liệu hoặc văn bản dưới dạng ký tự. Sẽ rất có ích khi bạn mong muốn tìm ra số lượng ký tự trong một văn bản mà không cần dùng bất kỳ công cụ nào của bên thứ ba. Nói một cách đơn giản, hãy sử dụng hàm LEN và đặt văn bản trong một dấu ngoặc đơn.

Giả sử khách hàng cần bạn viết các phần Titles, Meta Titles và Meta Descriptions cho các blog họ cần. Họ cũng muốn bạn giới hạn độ dài ký tự cho các mục này ở một con số cụ thể. Bạn có thể sử dụng hàm LEN để theo dõi các mục này và độ dài ký tự của chúng.

Nếu là nhà phát triển trang web hoặc ứng dụng, bạn cũng sẽ thấy hàm LEN hữu ích trong việc quản lý nội dung trong file Google Sheets. Hãy thử sử dụng công thức sau đây.

   =LEN(B2) =LEN("Example Text")   

6. IFERROR

Bảng tính của bạn có thể bị lộn xộn nếu có quá nhiều lỗi. Lỗi thường xảy ra khi bạn áp dụng nhiều công thức trên các cột và trang tính, nhưng vẫn không có nhiều dữ liệu để trả về bất kỳ giá trị nào.

Nếu bạn chia sẻ những file như vậy với những thành viên trong nhóm hoặc khách hàng, họ cũng đều có thể không thấy hài lòng. Hơn nữa, bạn cũng sẽ khó thoát khỏi việc mắc lỗi khi kết thúc công việc. Hàm IFERROR sẽ giải cứu cho bạn.

Đặt công thức của bạn bên trong hàm IFERROR và nhắc đến đến văn bản nào sẽ hiển thị nếu có lỗi. Video trên cho thấy việc sử dụng IFERROR trong các trường hợp khi bạn quản lý bảng tính về giá mặt hàng hoặc phân loại học sinh.

Dưới đây là các cú pháp công thức có thể có ích nếu bạn mong muốn tự mình thử:

   =iferror(D4/C4, 0) =iferror(VLOOKUP(A23,$A$13:$G$18,7,false),"ID Mismatch")   

7. COUNTIF và SUMIF trong Google Sheets

SUMIF và COUNTIF dễ hơn hàm kiếm tì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 thí dụ dưới đây, bạn cũng đều có thể đếm con số táo được bán với phép tính như sau:

   =COUNTIF(B2:B10,"Apple")   

Nó bộc lộ rằng: Đếm con số các trường có từ Apple trong từ ô B2 đến B10.

Bạn cũng 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ó kiếm tì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.

8. JOIN: Kết hợp từ trong Google Sheets

Hàm JOIN dùng để làm sâu chuỗi các giá trị thành một văn bản giúp thuận lợi cho chuyện sử dụng. Hoặc dễ dàng 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ông trùng lặp 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à phù hợp nhất khi liên kết nhiều giá trị. Bạn chỉ cần chỉ ra ký tự bạn mong muốn thêm vô 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   

9. INDEX: Truy xuất giá trị ban đầu hoặc giá trị cuối

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ư hàng tuần thêm dòng dữ liệu mới, sẽ luô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 mường tượng bạn cần phải tính sự thay đổi giữa ô cuối trong bảng với mức giá trị phía trước. Và thật khó để hoàn thành được kết quả tính trong ô tương tự ở mỗi lần thay. Dùng hàm LAST có vẻ như là cực 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 1 ô bằng cách xác xác định 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)   

10. Bảng Pivot

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ũng đều có thể mở bảng Pivot bằng cách chọn dữ liệu bạn mong 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 cả những loại quả mà không được 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 ngay 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ự.

Bạn cũng đều có thể áp dụng các hàm Google Sheets nêu trên trong nhiều trường hợp khác nhau. Chắc chắn rằng bạn cũng có thể tìm thấy nhiều trường hợp mới để áp dụng các công thức này. Chúng sẽ để dành thời gian và giúp bạn diễn giải dữ liệu 1 cách dễ hiểu.

các hàm trong Google Sheets,hướng dẫn sử dụng Google Sheets,Các hàm tính của Google Sheets,các hàm cơ bản của Google Sheets

Nội dung 10 hàm hữu ích trong Google Sheets có thể bạn chưa biết được tổng hợp sưu tầm biên tập bởi: Tin Học Trường Tín. Mọi ý kiến vui lòng gửi Liên Hệ cho truongtin.top để điều chỉnh. truongtin.top tks.