Hướng dẫn 27 cách để tăng tốc độ làm việc trong Google Sheets

Google Sheets là một phần mềm hay chương trình chuyên dụng trong việc thiết kế và chỉnh sửa các bảng tính của bạn. Google Sheets, cùng với Google Docs và Google Slides, là một phần của ứng dụng mà Google gọi là Google Drive, Các chương trình này cũng tương tự như Microsoft Excel, Microsoft Word, và Microsoft PowerPoint của Microsoft Office. Tuy nhiên khác với Excel, Google Sheets phù hợp cho những ai chỉ cần phải sử dụng bảng tính với lượng thông tin vừa phải hay cần phải làm việc từ xa với những công cụ khác nhau và đặc biệt là cần phải chia sẻ, hợp tác và làm việc trong một nhóm nhiều người. Và đôi khi bạn sẽ phải làm việc với một lượng lớn dữ liệu trong cùng một bảng tính (spreadsheet) dẫn đến việc tab Google Sheets của bạn tải vô cùng chậm chạp. Vậy có cách nào để tăng tốc cho nó không? Trong bài viết này, hãy cùng Gitiho tìm hiểu xem lý do tại sao dẫn đến việc này cũng như 27 cách để bạn có thể giúp cho bảng tính hoạt động nhanh hơn!

Việc sử dụng Google Sheets trên web mang đến cho chúng ta rất nhiều lợi ích nhưng bất cứ ai gặp trường hợp như dưới đây, ngồi đợi thanh loading chạy vô cùng chậm trên màn hình nhưng không thể làm gì hơn đều khiến bản thân vô cùng khó chịu!

Vậy làm cách nào chúng ta có thể giải quyết tốc độ xử lý mỗi khi Google Sheets chạy chậm?

Trước hết, bạn phải biết rằng đây là một câu hỏi rất khó trả lời vì có rất nhiều yếu tố có thể ảnh hưởng khiến Google Sheets của bạn xử lý thao tác chậm hơn.

Những điều mà tôi sắp hướng dẫn các bạn trong bài viết này là một số cách được đề xuất để tối ưu hóa tốc độ xử lý và một số nguyên nhân tôi phát hiện ra dẫn đến việc này.

Các đề xuất để tăng tốc Google Sheets

• Nhận biết được mỗi khi Google Sheets xử lý chậm chạp.
• Biết được những giới hạn trong kích thước của Google Sheets.
• Cách để tính toán kích thước tối đa của Google Sheets.
• Cách để tính toán tốc độ xử lý của Google Sheets.
• Loại bỏ các cell không được dùng đến.
• Chuyển đổi các công thức thành các hằng số mỗi khi có thể.
• Sử dụng các tham chiếu với phạm vi cụ thể.
• Loại bỏ các công thức không cố định hoặc cẩn thận khi quyết định phải dùng đến.
• Cách sử dụng hàm Vlookup.
• Cách sử dụng hàm Index kết hợp với hàm Match.
• Cách sử dụng hàm Query.
• Cách sử dụng các công thức mảng (Array Formula).
• Cách sử dụng việc nhập các công thức từ những nguồn khác.
• Cách sử dụng chức năng Google Finance.
• Sử dụng các mệnh đề IF để kiểm tra trước kết quả.
• Sử dụng một cell quản lý đối với các công thức tốn thời gian xử lý.
• Sử dụng các công thức như Filter, Unique, và Array_Constrains để tạo ra các bảng hỗ trợ nhỏ hơn.
• Tránh việc tạo các chuỗi tính toán dài dòng.
• Tham chiếu dữ liệu trong cùng một bảng tính. 
• Sử dụng các cột hỗ trợ. 
• Tách bảng tính Google Sheets bị chậm thành nhiều bảng tính nhỏ.
• Cẩn thận trong việc định dạng có điều kiện cho các cell.
• Tận dụng chức năng App Script.
• Cẩn thận trong việc sử dụng các công thức tùy chỉnh. 
• Các mẹo khắc phục khác để tăng tốc mỗi khi Google Sheets bị chậm.
• Kiên nhẫn mỗi khi thực hiện các thay đổi trên điện toán đám mây (cloud).
• Biết được lúc nên chuyển sang một cơ sở dữ liệu khác.

Dù đây là những phát hiện có tính chủ quan, phần lớn là của bản thân tôi, rất nhiều mẹo sau đây thường là các phương pháp vô cùng hữu dụng khi bạn phải làm việc với các bảng tính trong Google Sheets. Vì vậy ngay cả khi bạn chỉ đang làm việc với bảng tính nhỏ vào lúc này, rất có thể bạn sẽ thấy mình gặp rắc rối với với việc bảng tính của bạn xử lý chậm chạp sau này. Lời khuyên của tôi dành cho bạn là nên tìm hiểu và làm quen với các phương pháp ở trên ngay bây giờ để có thể tận dụng chúng mỗi khi cần.

Mẹo thứ 1: Cách nhận biết mỗi khi Google Sheets xử lý chậm chạp.

Nếu như bạn đang đọc bài viết này, bạn có lẽ nhận ra một số hoặc toàn bộ các vấn đề sau.

Các phép tính trong Google Sheets tốn rất nhiều thời gian để trả về kết quả và thanh loading xuất hiện mỗi khi bạn thực hiện một thay đổi cho bảng tính của mình.

Google Sheets của bạn trở nên chậm chạp trong việc phản hồi với các cú nhấp chuột hoặc các thao tác đánh máy.

Dữ liệu không hiển thị trong cell, mặc dù bạn biết chắc rằng mình đã nhập dữ liệu vào cell đó. Hãy xem qua ví dụ dưới đây khi công thức đơn thuần không muốn hiển thị giá trị sau khi đã được nhập:

Google Sheets của bạn trở nên không chịu phản hồi. Đây là một trường hợp tồi tệ hơn và theo kinh nghiệm của tôi, nếu điều này không được giải quyết nhanh chóng, bạn thường sẽ không thể phục hồi được bảng tính.

Vì vậy, nếu bạn thấy thông báo lỗi này và bạn đã đợi vài phút nhưng không có gì xảy ra, thì bạn có thể chỉ có thể làm liều, thoát trang và sau đó làm lại từ lúc được tự động lưu nhưng sử dụng một cách tiếp cận khác.

Điều này thường đi kèm với việc máy tính của bạn đang bị quá tải với hệ thống làm mát gần như đang muốn đình công!

Mẹo thứ 2: Biết những giới hạn trong kích thước của Google Sheets

Một điều vô cùng rõ rằng nếu như bạn đang phải làm việc với một bảng tính Google Sheets lớn - những bảng tính Google Sheets với lượng lớn dữ liệu và công thức đi kèm - thì rất có khả năng bạn sẽ thấy hiệu suất làm việc của chính bản thân bị ảnh hưởng.

Do đó, sẽ chẳng có bất kỳ điều gì để tìm hiểu về lý do chính gây nên việc Google Sheets chạy chậm nếu như chúng ta không biết được giới hạn về kích thước của một bảng tính Google Sheets.

Giới hạn tổng thể:

Google Sheets có một giới hạn là 5 triệu cell trên mỗi cửa sổ làm việc hay mỗi bảng tính.

Nếu bạn thực hiện bất kỳ thao tác nào vượt quá giới hạn này (ví dụ thêm một hàng dữ liệu mới hay một trang tính mới), bạn sẽ nhận được thông báo lỗi như dưới đây:

Giới hạn cột:

Google Sheets có tổng số cột tối đa là 18.278 cột.

Nếu bạn thực hiện bất kỳ thao tác nào vượt quá giới hạn này, bạn sẽ nhận được thông báo lỗi như dưới đây:

Giới hạn kích thước của mỗi cell riêng lẻ:

Trong một cell duy nhất, có độ dài chuỗi tối đa là 50.000 ký tự (đủ cho khoảng 500 câu trung bình, hoặc khoảng 162 câu văn miêu tả).

Nếu bạn thực hiện bất kỳ thao tác nào vượt quá giới hạn này, bạn sẽ nhận được thông báo lỗi như dưới đây:

Mẹo thứ 3: Đo kích thước của Google Sheets

Chúng ta sẽ sử dụng đến App Script, để có thể nhanh chóng thiết kế một công cụ đơn giản để tính toán kích thước bảng tính của mình.

Công cụ kiểm tra bảng tính này hiển thị tổng số cell trong một bảng tính, bao nhiêu cell đã chứa dữ liệu trong đó và sau đó thống kê cho từng trang tính riêng lẻ, bao gồm số lượng mảng và số lượng các công thức không cố định (các công thức liên tục cập nhật) mà bạn có.

Bạn cũng có thể tìm thấy mã cho công cụ này trên GitHub nếu như bạn muốn (link: https://github.com/benlcollins/apps_script/blob/master/audit_tool/code.js)

Vậy bạn đã biết cách đo kích thước của Google Sheets, còn tốc độ xử lý của chúng thì sao?

Mẹo thứ 4: Cách để tính toán tốc độ xử lý của Google Sheets.

Để đo "tốc độ xử lý" của từng phép tính khác nhau, bạn có thể sử dụng chức năng Chome’s Developer Tools.

Trong cửa sổ trình duyệt, bạn có thể truy cập Developer Toolkit bằng cách nhấn Cmd+Option+I trên máy Mac hoặc Ctrl+Shift+I (trên PC). Bạn cũng có thể truy cập được trong menu View> Developer.

Trong tab Performance của Developer Tools, bạn có thể ghi lại tốc độ xử lý của trang web (hoặc của Google Sheets trong trường hợp này) khi nó chạy và chọn vào ô đánh dấu chụp màn hình để cho bạn biết những gì đang được thực hiện và trong bao lâu.

Đối với việc tính toán tốc độ xử lý của các công thức, bạn có thể xác định thời điểm công thức bắt đầu chạy, ví dụ: được đánh dấu bằng số 1 trong hình ảnh này, và vào mốc thời gian là 3.6 giây:

Số 2 hiển thị ảnh chụp màn hình tại thời điểm bắt đầu, vì vậy bạn có thể thấy rằng kết quả của các công thức vẫn chưa được hiển thị.

Bạn có thể xác định khi nào các công thức đã hoàn thành việc tính toán (hộp màu đỏ, vào khoảng mốc thời gian là 8. 6 giây):

Và bạn có thể thấy trong ảnh chụp màn hình là công thức đã hoàn thành việc tính toán và trả về kết quả trong các cell ngay lúc đó.

Do đó bạn cũng có thể biết được khoảng thời gian cần thiết cho các công thức được xử lý là: 8. 6s - 3. 6s = 5s

Đây chính là phương pháp tôi đã sử dụng để tính toán tốc độ xử lý của công thức để thực hiện việc so sánh. Tôi đã thực hiện việc này rất nhiều lần, ở cùng một vị trí vào cùng một thời điểm trong ngày (do đó sự khác biệt về tốc độ wifi là không đáng kể) và tôi cũng thực hiện chúng nhiều lần và tính trung bình để đưa ra tip này cho bạn.

Mẹo thứ 5: Loại bỏ các cell không được dùng đến.

Google Sheets của bạn sẽ chạy chậm hơn khi chúng chứa nhiều dữ liệu hơn.

Vì vậy, một trong những điều đầu tiên cần thử, trước khi đạt được tìm hiểu đến lợi ích của việc tối ưu hóa công thức, là giảm đi kích thước Google Sheets của bạn.

Trên thực tế, các cell còn trống làm chậm hiệu suất xử lý một cách đáng kể. Do đó, tôi khuyên bạn nên xóa chúng đi bất cứ khi nào có thể, để giảm thiểu số lượng cell mà Google Sheets đang lưu trữ trong bộ nhớ.

Lý tưởng nhất là bạn nên xóa bất kỳ số lượng lớn các hàng trống bên dưới tập dữ liệu hoặc các cột ở phía bên phải tập dữ liệu của bạn.

Còn nếu như các hàng mới được thêm một cách tự động thì sao?

Đừng lo lắng nếu như dữ liệu của bạn đang được nhập tự động bằng Apps Script hoặc công cụ của bên thứ ba như Supermetrics hoặc Tiller, vì các hàng mới cần phải được tự động thêm vào Sheets của bạn theo yêu cầu. (Tuy nhiên, bạn luôn cần phải kiểm tra giả thuyết này với cài đặt hoặc ứng dụng cụ thể của mình, để xác nhận đây đúng là yêu cầu của các công cụ đó.)

Mẹo thứ 6: Chuyển đổi các công thức thành các hằng số mỗi khi có thể.

Khi bạn đã hoàn tất quá trình sử dụng một tập hợp các công thức nào đó trong bảng tính của mình, và bạn chắc chắn rằng mình sẽ không dùng đến chúng nữa, điều nên làm là chuyển đổi chúng thành các giá trị hay các hằng số mỗi khi có thể.

Điều này sẽ làm giảm đi kích thước của toàn bộ bảng tính từ đó cải thiện hiệu suất tổng thể. Đây cũng chính là phương pháp tốt nhất mà tôi có thể đề xuất cho bạn (đồng thời thao tác này cũng giúp tránh trường hợp ai đó hoặc việc gì đó xảy ra ảnh hưởng đến các công thức của bạn).

Bạn thay đổi công thức thành các giá trị bằng cách sao chép chúng và làm như sau: Edit > Paste special > Paste values

Tốt hơn hết là hãy học các tổ hợp phím tắt để tiết kiệm thời gian cho bản thân. Đánh dấu các công thức cần được chuyển đổi, Ctrl+C trên PC hoặc Cmd+C trên Mac để sao chép và sau đó Ctrl+Shift+V trên PC hoặc Cmd+Shift+V trên Mac để dán dưới dạng giá trị.

Lưu ý rằng tôi ủng hộ việc giữ một bản sao của công thức "ban đầu" ở đầu cột dữ liệu trong bảng của bạn, để bạn có một ghi chép về cách phép tính đã được thực hiện và cũng có thể nhanh chóng sử dụng lại nếu cần:

Mẹo thứ 7: Sử dụng các tham chiếu với phạm vi cụ thể.

Một tham chiếu phạm vi cụ thể có nghĩa là sử dụng một cách tham chiếu như A1: B1000 (tham chiếu cell A1 tới cell B1000) thay vì chỉ tham chiếu A: B (tham chiếu cả cột A và B) trong công thức của bạn (đồng nghĩa với việc chỉ định rõ ràng các ranh giới cho phạm vi của mình).

Nếu bạn đang làm việc với các bảng tính lớn và đang sử dụng các tham chiếu mở (tham chiếu cả cột hay cả hàng dữ liệu) mà bạn thực sự có thể giới hạn, thì bảng tính sẽ tốn thêm thời gian xử lý các phép tính để trả về kết quả cho các công thức của bạn.

Cho dù bạn không nhập nhiều dữ liệu vào trong cột A, bảng tính vẫn sẽ kiểm tra mọi cell của cột đó, bao gồm tất cả các cell còn trống, nếu như bạn sử dụng việc tham chiếu các phạm vi mở như A:B.

Lưu ý rằng mặc dù đây thường là một ý tưởng tốt để tăng tốc độ xử lý cho bạn, nhưng rõ ràng là có những tình huống khi tham chiếu mở là cần thiết, ví dụ như bạn biết rằng bảng tính sẽ có thêm dữ liệu mới trong ngày hôm sau. Việc sử dụng một phạm vi mở sẽ cho phép mọi dữ liệu mới được đưa vào tính toán.

Mẹo thứ 8: Loại bỏ các công thức không cố định hoặc cẩn thận khi quyết định phải dùng đến.

Trong Google Sheets có bốn hàm là NOW(), TODAY(), RAND() và RANDBETWEEN(), thường được biết đến như các hàm không cố định (volatile functions), có nghĩa là chúng sẽ tính toán lại mỗi khi có bất kỳ thay đổi xảy ra trong bảng tính. Tất cả các công thức liên quan cũng sẽ được tính toán lại (tức là các công thức sử dụng tham chiếu hoặc bao gồm các hàm không cố định bên trong).

Việc này sẽ cần đến toàn bộ khả năng để xử lý từ đó có thể tác động tiêu cực đến hiệu suất bảng tính của bạn (bạn sẽ chỉ cần phải chú ý đến trường hợp này nếu như bảng tính có số lượng lớn các công thức không cố định).

Để minh họa, tôi đã sao chép 100.000 hàm TODAY () xuống cột A, máy của tôi mất khoảng 5.5 giây để thực hiện.

Sau đó, tôi đã xóa 99,999 công thức này và chỉ để lại một hàm TODAY () trong cell A1 và sau đó đặt tham chiếu sau vào cell A2:= $A$1

Tiếp theo tôi sao chép tham chiếu này xuống. Các ký hiệu đô la đại diện cho các tham chiếu tuyệt đối và giữ tất cả các công thức của tôi bị khóa vào cell A1.

Chạy công thức này xuống 100.000 cell trong cột A mất khoảng 4. 1 giây, nhanh hơn gần 1. 5 giây.

Cách tốt nhất để sử dụng các công thức không cố định là sử dụng cách tham chiếu này, bởi vì nếu tôi có phải chỉnh sửa ngày tháng thì tôi chỉ cần phải thay đổi trong công thức ở cell A1 và không phải kiểm tra và nhập lại toàn bộ công thức của mình.

Mẹo thứ 9: Cách sử dụng hàm Vlookup.

Luôn sử dụng tham chiếu phạm vi cụ thể, thay vì tham chiếu mở, cho các bảng tra cứu của bạn nếu có thể (xem số 7 ở trên).
 

Giảm kích thước bảng tra cứu của bạn, nếu có thể, mặc dù điều này chỉ có ảnh hưởng tích cực tương đối nhỏ theo kinh nghiệm của tôi. Ví dụ: thay vì phải tra cứu trên toàn bộ trang tính, bạn có thể sử dụng hàm Filter (xem mẹo số 17 bên dưới) để tạo một bảng trợ giúp nhỏ hơn cho việc tra cứu trước, rồi hẵng sử dụng bảng hỗ trợ này trong hàm VLOOKUP của bạn.

Tuy nhiên, đừng bị cám dỗ để lồng hàm FILTER (hoặc bất kỳ hàm khác) bên trong hàm VLOOKUP của bạn nghĩ rằng việc tạo bảng sẽ nhanh hơn. Việc này tốn nhiều thời gian hơn bạn mong đợi.

Ví dụ: công thức này là một trường hợp không tốt và thực sự ảnh hưởng rất nhiều đến quá trình xử lý của bảng tính nếu bạn sử dụng chúng nhiều lần.

=VLOOKUP( $A1 , UNIQUE( FILTER( $K$1:$N$10000 , $K$1:$K$10000 < 100 )) , 2 , FALSE )

Còn đối với cách tiếp cận sử dụng một bảng Filter dưới dạng bảng dữ liệu hỗ trợ và tham chiếu đến nó thì tôi ưu tiên hơn nhiều và công thức cũng đơn giản hơn:

=VLOOKUP( $A1 , $F$1:$I$10 , 2 , FALSE)

Và ngay cả việc kết hợp các hàm VLOOKUP với công thức mảng để trả về giá trị cho nhiều cột với một lần tra cứu cũng tốn thời gian hơn đáng kể so với việc chỉ sử dụng hàm VLOOKUP đơn giản nhưng được thực hiện nhiều lần.

Trong Excel, việc sắp xếp dữ liệu của bạn và sử dụng mệnh đề so sánh gần đúng (TRUE là yếu tố cuối cùng trong công thức VLOOKUP của bạn) cũng nhanh hơn một chút so với việc sử dụng mệnh đề so sánh chính xác (FALSE là yếu số cuối cùng). Tốc độ xử lý của Google Sheets trong cả hai trường hợp chỉ khác nhau vô cùng ít vì vậy việc phải cân nhắc sử dụng mệnh đề so sánh nào không đáng.

Một thủ thuật khác trong việc dùng hàm VLOOKUP trong Excel là sử dụng hai hàm VLOOKUP đối sánh gần đúng thay vì một đối sánh chính xác. Nó tiết kiệm cho bạn thời gian một cách đáng kể vì thuật toán tìm kiếm cơ bản nhanh hơn rất nhiều. Tuy nhiên, tôi thấy kỹ thuật này thực sự chậm hơn một chút khi áp dụng trên Google Sheets nên tôi sẽ không khuyến khích nó.
 

Mẹo thứ 10: Cách sử dụng hàm Index kết hợp với hàm Match.

Việc kết hợp hai hàm Index-Match là một tổ hợp sử dụng để tra cứu rất mạnh mẽ và tôi muốn bạn cân nhắc dành thời gian để tìm hiểu.
 

Trong cấu trúc căn bản của nó, bạn có thể thấy các công thức như sau:

=INDEX( Sheet2!$A$2:$P$51 , MATCH($A3,Sheet2!$G$2:$G$51,0) , MATCH(C$1,Sheet2!$A$1:$P$1,0) )

trong đó bạn có lồng hai hàm MATCH vào trong để tra cứu số hàng và số cột (đó là lý do tại sao việc kết hợp hai hàm Index-Match rất linh hoạt).

Tuy nhiên cách này chỉ phù hợp với việc sử dụng một số lượng nhỏ tổ hợp Index-Match, nhưng lại không thực sự hiểu quả khi làm việc trên quy mô lớn.

Giả sử như bạn đang tìm kiếm 10.000 hàng và 10 cột. Đối với mỗi cell, bạn sẽ cần đến hai kết quả phù hợp với kết quả tra cứu của số hàng và số cột, vì vậy tổng số phép tính của bạn sẽ là:
 

Số lần hàm Match được tính cho số hàng+
Số lần hàm Match được tính cho số cột+
Tổng số lần tổ hợp hàm Index và hàm Match được thực hiện =

(10,000 hàng x 10 kết quả phù hợp) +
(10,000 hàng x 10 kết quả phù hợp) +
(10,000 hàng x 10 hàm Index được thực hiện) = 300,000 phép tính

Một cách khác tốt hơn nhiều là chia việc tra cứu sử dụng hàm Match và sử dụng kết quả để tạo thành một trang tính với các hàng và các cột dữ liệu trợ giúp, như được minh họa trong ví dụ sau:
 

Bằng cách tách cả hai hàm tra cứu Match được lồng trong công thức ở trên thành hàng và cột của riêng chúng, bạn có thể hoàn thành việc tính toán tất cả các kết quả trước một lần, sau đó sử dụng các giá trị hay dữ liệu đó trong hàm Index của bạn.

Bây giờ bạn sẽ chỉ phải tính toán hàm Index sau khi đã thực hiện thao tác tách ở trên, vì vậy tổng phép tính mà Google Sheets cần phải thực hiện là:

(10,000 kết quả phù hợp với số hàng) +
(10 kết quả phù hợp với số hàng) +
(10,000 * 10 hàm Index được thực hiện) = 110,010 phép tính

Ít hơn đáng kể so với việc lồng hai hàm vào nhau, chỉ bằng khoảng 1/3 so với tổng số phép tính cần được xử lý ở trên!

Xem thêm: Cách kết hợp hàm INDEX và MATCH thay thế hàm Vlookup trong Excel

Mẹo thứ 11: Cách sử dụng hàm Query.

Như đã được đề cập ở trên, việc sử dụng tham chiếu phạm vi mở có thể ảnh hưởng rất nhiều đến hiệu suất xử lý của Google Sheets vì bạn đang vô tình đưa thêm hàng ngàn hàng trống vào các phép tính của mình (xem số 7 ở trên).

Thay vào đó, hãy sử dụng tham chiếu phạm vi cụ thể hoặc tạo các bảng trợ giúp nhỏ hơn trước tiên sau đó hẵng đưa vào các hàm Query của bạn để tăng tốc độ xử lý.

Ví dụ: khi thực hiện các lần thử nghiệm với hàm QUERY, tôi nhận thấy rằng mỗi 20.000 hàng trống được vô tình thêm vào với phạm vi mở sẽ thêm 1 giây vào thời gian hoàn thành phép tính.

Vì vậy, thay vì một công thức như thế này:

=QUERY( Sheet2!A:E , "select *" , 1 )

Google Sheets sẽ hoàn thành tốt hơn nhiều bằng việc bạn chỉ định một phạm vi cụ thể, rõ ràng:

=QUERY( Sheet2!A1:E100000 , "select *" , 1 )

Điều này cải thiện tốc độ xử lý các hàm Query vì phép tính không còn bao gồm tất cả các hàng trống trong Sheets 2 của tập dữ liệu.

Mẹo thứ 12: Cách sử dụng các công thức mảng (Array Formula).

Nếu bạn dành nhiều thời gian đọc và tìm hiểu trên các diễn đàn trợ giúp, bạn sẽ thấy phần lớn mọi người thường nói rằng việc sử dụng một công thức mảng duy nhất sẽ nhanh hơn với việc sử dụng hàng trăm công thức hay các hàm riêng lẻ, trong khi những người còn lại phản bác và ủng hộ việc sử dụng các công thức thông thường. Vậy ý kiến nào mới đúng?

Theo kinh nghiệm của tôi, các công thức mảng sẽ hoạt động chậm trên các tập dữ liệu lớn (những tập dữ liệu có số lượng lớn hàng dữ liệu) và sẽ khiến cho bảng tính của bạn chậm chạp. Tôi thấy chúng thường có xu hướng được xử lý chậm hơn các công thức riêng lẻ.

Tuy nhiên, chúng lại rất hữu ích trong các tình huống cụ thể, như thu thập dữ liệu từ Google Form. Dù vậy tôi vẫn khuyên bạn nên tìm hiểu về chúng nhưng sử dụng một cách thận trọng.

Bản thân tôi chủ yếu sử dụng chúng để thực hiện các tính toán đang chạy trên các tập dữ liệu phát triển theo thời gian (các hàng mới luôn được cập nhật vào). Bằng cách đó tôi sẽ biết được những phép tính vẫn đang hoạt động dù cho tôi không cần cập nhật bất cứ điều gì.

Ví dụ, công thức này:

=ArrayFormula( IF( A2:A <> "" , A2:A * 100 , "" ))

là phiên bản mảng của công thức sau đây:

= IF( A2 <> "" , A2 * 100 , "" )

Công thức mảng sẽ tự động tính giá trị cho mỗi hàng dữ liệu mới được thêm vào.

Còn nếu tôi là bạn, tôi sẽ kiểm tra cả hai cách tiếp cận trên trang tính và chọn phiên bản nào thích hợp nhất (nên cân nhắc giữa tốc độ xử lý của Google Sheets so với lợi ích của việc tính toán tự động mà công thức mảng đem lại). Cách tiếp cận của việc sử dụng công thức riêng lẻ còn có thêm lợi ích là dễ gỡ lỗi và thực hiện hơn.

Nếu bạn vẫn muốn giữ các công thức mảng của mình. Hãy xem xét việc lưu trữ dữ liệu theo định kỳ (xem số 21 bên dưới), để giữ cho việc quản lý tập dữ liệu trực tiếp dễ dàng hơn.

Mẹo thứ 13: Cách sử dụng việc nhập các công thức từ những nguồn khác.

Các công thức như ImportHtml, ImportFeed, ImportData, ImportXml và ImportRange lấy dữ liệu từ những nguồn bên ngoài vào trong Google Sheets của bạn, vì vậy chúng yêu cầu phải có kết nối Internet để hoạt động.

Đương nhiên chúng sẽ chạy chậm hơn các công thức khác đang hoạt động trực tiếp trên bảng tính của bạn, từ đó dẫn đến việc ảnh hưởng đến hiệu suất làm việc của Google Sheets.

Ví dụ: sử dụng hàm ImportRange để nhập một lượng lớn dữ liệu từ bảng tính khác sẽ mất thời gian và bạn có thể sẽ thấy thông báo lỗi Loading trong một lúc:

Hàm ImportRange là một công thức chậm bởi vì nó đang kết nối với một bảng tính khác để nhập dữ liệu. Nói chung, tốt nhất là bạn giảm thiểu số lần sử dụng công thức không cần thiết để nhập dữ liệu từ nguồn bên ngoài này đi.

Tuy nhiên, khi dữ liệu của bạn bắt đầu trở nên thực sự lớn (khoảng 20.000 hàng), kết quả duy nhất mà công thức ImportRange trả về cho bạn sẽ chỉ là cửa sổ hộp thoại Error Loading data...

Nhưng nếu trong những trường hợp bắt buộc, hãy sử dụng nhiều công thức ImportRange để truy xuất dữ liệu của bạn. Và kết hợp chúng thành một công thức duy nhất bằng cách sử dụng dấu {.  .  .  ;.  .  .  } của công thức mảng, 

Ví dụ trong công thức này lấy 15.000 hàng đầu tiên và 15.000 hàng tiếp theo (tối đa 30.000) từ trang tính 1 của nguồn Sheet dữ liệu:

={ 
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UrtOONA8B2pNYCI1byDA__R9_Bbz0GWrfxP_MDotaTY/edit#gid=1789207765","Sheet1!A1:E15000") ; 
MPORTRANGE("https://docs.google.com/spreadsheets/d/1UrtOONA8B2pNYCI1byDA__R9_Bbz0GWrfxP_MDotaTY/edit#gid=1789207765","Sheet1!A15001:E30000") }

Lưu ý rằng bạn sẽ bị giới hạn tối đa là 50 hàm ImportData trong một bảng tính

Trong Google Sheets, không có công thức Import nào khác có giới hạn rõ ràng như thế này trên các trang trợ giúp của chúng và nếu có bất cứ thử nghiệm hạn chế được thực hiện để tìm ra giới hạn, con số chắc chắn sẽ lớn hơn 50.

Theo mặc định, các hàm này sẽ được tính toán lại như sau:

• ImportRange: 30 phút một lần
• ImportHtml, ImportFeed, ImportData, ImportXml: Mỗi 60 phút

Mẹo thứ 14: Cách sử dụng chức năng Google Finance

Chức năng Google Finance là một chức năng khác được dùng để lấy dữ liệu từ một nguồn bên ngoài, do đó cũng cần phải có kết nối Internet. Giống như các công thức Import ở trên, nó chắc chắn sẽ chậm hơn các hàm thông thường đang hoạt động trực tiếp trong Sheet của bạn.

Một lần nữa, chiến lược chung ở đây là giảm thiểu số lượng dữ liệu được truy xuất từ bên ngoài mà bạn thực hiện với chức năng Google Finance, chẳng hạn như giảm số lần bạn sử dụng đến chức năng này.

Và nếu có cần phải dùng đến, bạn hãy cân nhắc đến việc truy xuất dữ liệu cho cả năm với một công thức duy nhất thay cho việc truy xuất mỗi ngày 365 lần chẳng hạn.

Theo nghiên cứu, dữ liệu được nhập từ Google Finance có thể bị delay lên đến 20 phút.

Mẹo thứ 15: Sử dụng các mệnh đề IF để kiểm tra trước kết quả.

Bạn có thể sử dụng một mệnh đề IF để bao bọc và kiểm tra xem một phép tính có nên được thực hiện trước khi thực hiện nó hay không.

Giả sử như bạn có một bảng dữ liệu về sản phẩm bao gồm sách và bạn muốn sử dụng một hàm Vlookup để đưa dữ liệu của việc bán sách vào cùng với mỗi cuốn sách.

Bạn đang muốn thiết lập một hàm VLOOKUP để tìm kiếm số ISBN (số sê-ri của mỗi cuốn sách) vào trong bảng dữ liệu về doanh thu và trả về kết quả của việc bán hàng. Sẽ có một sự kém hiệu quả xảy ra bởi vì bạn đang tìm kiếm tất cả các sản phẩm của mình, không chỉ riêng sách, vì vậy tất cả những sản phẩm trả về kết quả N/A trong cột ISBN vẫn được tìm kiếm.

Thay vào đó, trước tiên hãy kiểm tra số ISBN và sau đó chỉ thực hiện việc tra cứu nếu bạn có ISBN hợp lệ.

Công thức có thể trông giống như thế này, khi mà bạn kiểm tra xem liệu giá trị tra cứu ISBN có trả về kết quả N/A hay không:

= IF( A2 <> "N/a", your_vlookup , "Not a book")

hay cụ thể hơn: 

= IF( test , calculation_if_test_true , output_message_no_calc )

Mẹo thứ 16: Sử dụng một cell quản lý đối với các công thức tốn thời gian xử lý.

Hãy tạo một cell với mục đích như nút công tắc điện cùng cách sử dụng mệnh đề IF ở trên để giữa mọi thứ trong khả năng quản lý: 
 

Hãy sử dụng chức năng Data Validation để tạo menu thả xuống trong một cell duy nhất, cell điều khiển, với các giá trị "Xử lý" và "Dừng xử lý" (hoặc bất kỳ câu nào bạn thấy phù hợp).

Sau đó, sử dụng công thức IF như sau để thực hiện các công thức tốn thời gian xử lý làm chậm Google Sheets chỉ khi bạn muốn:

=IF( $A$2 = "On Hold" , "On hold" , your_formula_here )

Dưới đây là một ví dụ về phương pháp áp dụng cell điều kiển để thực hiện một số công thức IMPORTXML truy xuất dữ liệu từ Instagram đòi hỏi hiệu suất cao (dành cho 25 tài khoản hàng đầu):

Mẹo thứ 17: Sử dụng các công thức như Filter, Unique, và Array_Constrains để tạo ra các bảng hỗ trợ nhỏ hơn.

Tất cả các hàm Filter, Unique và Array_Constrain đều chấp nhận các phạm vi như toàn bảng tính làm dữ liệu đầu vào và phạm vi được trả về sẽ là toàn bảng tính (dữ liệu đầu ra) nhỏ hơn bảng đầu vào.

Do đó chúng rất hữu dụng trong việc tạo các bảng hỗ trợ, tạo cơ hội để bạn dễ dàng phân tích dữ liệu một cách chuyên sâu.

Giả sử bạn có một bảng với 100.000 hàng dữ liệu, nhưng chỉ quan tâm đến việc thực hiện các tính toán trên một tập hợp con nhất định của bộ dữ liệu đó, thì bạn có thể sử dụng một hoặc nhiều các công thức ở trên để giảm kích thước bảng tính của mình và tạo một bảng trợ giúp mới phục vụ cho tính toán của bạn.

Ví dụ như bạn có thể sử dụng hai hàm Filter và Unique để tạo bảng trợ giúp từ một tập hợp con động của một bảng dữ liệu lớn hơn của bạn:

=UNIQUE( FILTER( A1:E100000 , B1:B100000 < 100 ))

hoặc sử dụng công thức Array_Constrain để tạo bảng trợ giúp từ một tập hợp con tĩnh của một bảng dữ liệu lớn hơn của bạn:

=ARRAY_CONSTRAIN( A1:E100000 , 10 , 4 )

Mẹo thứ 18: Tránh việc tạo các chuỗi tính toán dài dòng.

Khi nói đến chuỗi tính toán dài, ý tôi là việc tham chiếu các cell ở trong các công thức tạo nên một chuỗi dài, ví dụ: một cột dài với các cell bên dưới bằng với cell bên trên, như sau:

Để làm thử nghiệm, tôi đã tạo một cột với 100.000 bản sao của phép tính này, luôn tham chiếu đến cell bên trên và Google Sheets KHÔNG BAO GIỜ có thể hiển thị tất cả các kết quả của phép tính, mặc dù phép tính chỉ căn bản là hiển thị con số nằm ở cell bên trên. Tôi đã phải đóng tab làm việc này, đợi một lát và mở lại mới thấy rằng kết quả đã được hoàn thành.

Điều thú vị là, sau khi được thực hiện 99,099 lần thì một vấn đề nảy sinh. Một cell trống luôn xuất hiện ở lần thứ 99,100 của phép tính. Bạn có thể thấy ở dưới đây phép tính tham chiếu đến cell bên trên (A99102) nhưng lại không hiển thị giá trị 1 như các cell ở trên đang hiển thị:

Hình 18: Vấn đề nảy sinh khi thực hiện một chuỗi tính toán dài

Tôi luôn mắc phải vấn đề này khi đạt đến giới hạn 99.100 trong tất cả các lần thử nghiệm chuỗi còn lại, điều này khiến tôi nghĩ rằng đây là một loại giới hạn đối với khả năng mà Google Sheet có thể xử lý cho các phép tính sử dụng các cell được liên kết.

Tốc độ xử lý vô cùng chậm và thậm chí có thể không hoàn thành được phép tính trong một số trường hợp nhất định.

Bên cạnh đó, việc thực hiện các phép theo chuỗi như thế này cũng rất tệ. Bất kỳ lỗi nào xảy ra cũng dấn đến phản ứng dây chuyền và chuỗi bị sập. Chưa kể đến việc bạn sẽ mất rất nhiều thời gian và công sức để gỡ lỗi cho nó.

Một phương pháp hay hơn là tham chiếu đến một cell cụ thể có tham chiếu tuyệt đối và sau đó sao chép nó xuống cột của bạn, ví dụ: = $A$2

Mẹo thứ 19: Tham chiếu dữ liệu trong cùng một bảng tính. 

Việc tham chiếu dữ liệu trong cùng một bảng tính sẽ giúp mọi thứ trở nên đơn giản - luôn là một phương pháp hữu hiệu - tiết kiệm thời gian vì các công thức của bạn có thể truy cập dữ liệu và trả về kết quả nhanh hơn.

Nhưng một điều vô cùng rõ rằng là việc này không phải lúc nào cũng thực tế hoặc thuận tiện để tham chiếu dữ liệu trực tiếp trong cùng một tab làm việc Google Sheets của bạn. Tuy nhiên, nếu có thể, hãy giữ việc tham chiếu trong cùng bảng tính, tốc độ xử lý chắc chắn sẽ nhanh hơn so với việc sử dụng ImportRange để truy xuất dữ liệu từ các bảng tính Google Sheets khác.

Dù nói như vậy, ở tip thứ 21 bên dưới, tôi có thảo luận về những ưu điểm của việc tách Google Sheets chứa lượng lớn dữ liệu và xử lý chậm chạp sau đó sử dụng ImportRange để kết hợp chúng lại với nhau khi cần thiết.

Mẹo thứ 20: Sử dụng các cột hỗ trợ. 

Phần lớn người dùng đều có xu hướng tạo ra các công thức thông minh thực hiện mọi thao tác cần thiết chỉ trong một lần, việc tạo ra các cột hỗ trợ mang đến cho bạn rất nhiều lợi thế. Ưu điểm thứ nhất là chúng vô cùng dễ tạo, rất dễ theo dõi (cho cả bản thân bạn và cả những đồng nghiệp), dễ dàng trong việc gỡ rối và thậm chí tốc độ xử lý cũng nhanh hơn hẳn.

Giả sử như bạn có danh sách các URL của một trang web trong cột A và bạn muốn nhanh chóng trích xuất đường dẫn, phần URL đằng sau www.têntrang.com của link.
 

Bạn có thể sử dụng công thức này trong cột B:

=RIGHT( A1 , LEN(A1) - SEARCH( ".com" , A1 ) - 4 )

Bây giờ trong ví dụ của chúng ta, vì tên máy chủ giống nhau trong tất cả trường hợp, chúng ta không cần phải tính toán hàm Search đã lồng nhau mỗi lần.

Chúng ta có thể chạy thử công thức ở trên một lần, để nhận thấy rằng máy chủ của chúng ta dài 24 ký tự (sau đó lưu trữ tên này trong một cột hỗ trợ để đảm bảo công thức trả về kết quả đúng) và thay thế hàm Search bằng hằng số này (12 ký tự và 4 ký tự của ".com"):

 =RIGHT( A1 , LEN(A1) - 16 )

Mẹo thứ 21: Tách bảng tính Google Sheets bị chậm thành nhiều bảng tính nhỏ.

Trước hết, bạn cần biết rằng có thể đã đến lúc cân nhắc đến việc lưu trữ tập dữ liệu của bạn vào một cơ sở dữ liệu thích hợp chứ không chỉ lưu nó trong Google Sheets nếu như bạn sắp chạm đến giới hạn 5 triệu cell ở trên (xem mẹo số 27 bên dưới).

Mặc dù vậy, giả sử chúng ta đã quen làm việc và gắn bó với Google Sheets, bạn có thể đến lúc phải chia nhỏ tập dữ liệu khổng lồ của mình thành các bảng tính riêng lẻ nếu như tốc độ xử lý của Sheets như rùa bó và ảnh hưởng đến hiệu suất của bạm.

Tuy nhiên, bạn nên suy nghĩ kỹ càng và cẩn thận nếu như đang cân nhắc đến việc sử dụng cách tiếp cận này để tránh trường hợp mất dữ liệu. Chỉ thực hiện mẹo này nếu như bạn tự tin rằng bạn biết mình đang làm gì và bạn chắc chắn rằng mình đang thực sự cần đến nó.

Bạn cũng nên biết rằng cách tiếp cận này cũng có những khuyết điểm của riêng nó. Cụ thể là trước kia bạn chỉ có một bảng tính duy nhất để theo dõi, sửa đổi, cập nhật và phân tích, trong khi bây giờ, nếu như bạn tách nó ra, bạn sẽ phải theo dõi nhiều bảng tính cùng lúc và phải đảm bảo rằng các thay đổi và phân tích là nhất quán.

Cách làm an toàn cho cách tiếp cận này có thể là giảm tải dữ liệu của những năm trước hoặc các thông tin về sản phẩm hay dịch vụ không còn hiện hữu trong doanh nghiệp của bạn nữa. Nói cách khác, đó là dữ liệu chúng tôi không dự tính sẽ sử dụng lại, nhưng chúng ta cần phải lưu trữ một bản sao của nó.

Cách an toàn để tách một Google Sheets xử lý chậm chạp

Cách tốt nhất để tách Google Sheets thực sự lớn của bạn là tạo các bản sao mới, dán nhãn, đặt tiêu đề rõ ràng cho từng bảng tính mới và sau đó xóa tất cả dữ liệu không liên quan đến chủ đề mà bạn đã đặt cho bảng tính đó. Ví dụ như bạn đang muốn chuyển các dữ liệu từ năm 2015 vào một bảng tính của riêng nó, thì đây là các bước cần thiết:

• Tạo một bản sao của bảng tính chính, với tất cả dữ liệu của bạn ở bên trong
• Đặt tên cho bảng tính mới này và bao gồm năm 2015 ở đâu đó trong tiêu đề 
• Sắp xếp dữ liệu theo năm trong bảng tính mới 
• Thêm chức năng bộ lọc và yêu cầu Google Sheets hiển thị tất cả dữ liệu ngoại trừ những dữ liệu liên quan đến năm 2015 (cụ thể là ẩn dữ liệu của năm 2015)
• Xóa tất cả các dữ liệu đang được hiển thị 
• Xóa chức năng bộ lọc để bảng tính mới chỉ hiển thị dữ liệu của năm 2015

Khi bạn đã hoàn thành tất cả các bước trên cho toàn bộ dữ liệu cần được lưu trữ của mình, bạn sẽ muốn xóa các dữ liệu đó khỏi bảng tính chính. Tuy nhiên, tôi ủng hộ việc giữ một bản sao đầy đủ của bảng tính chủ trước khi bạn bắt đầu việc xóa bất kỳ dòng hay cột dữ liệu nào.

Bạn sẽ kết thúc với một bảng tính chính chỉ có các dữ liệu hiện tại hoặc trực tiếp của bạn trong đó và hy vọng rằng Google Sheets sẽ xử lý nhanh hơn.
 

Bạn cũng có thể thực hiện bất kỳ phép tính hoặc thao tác phân tích dữ liệu theo từng phần và sau đó tập hợp các dữ liệu lại với nhau trong bảng tính chính bằng cách sử dụng công thức ImportRange.

Mẹo thứ 22: Cẩn thận trong việc định dạng có điều kiện cho các cell.

Định dạng có điều kiện (Conditional formatting) là một tính năng vô cùng tiện dụng giúp bổ sung thêm ngữ cảnh vào Google Sheets của bạn, chẳng hạn như làm nổi bật các giá trị ngoại lệ hoặc các giá trị tối đa, tối thiểu trong bảng dữ liệu. Tôi sử dụng dạng định dạng này rất nhiều trong các bảng tính lập biểu để làm nổi bật các hàng dữ liệu của ngày hiện tại chẳng hạn.

Tuy nhiên, chức năng này sẽ trở nên rất chậm chạp trên các bộ dữ liệu lớn vì nó được triển khai trên cơ sở từng cell một.

Vì vậy, cách tốt nhất để sử dụng định dạng có điều kiện là trên các bảng dữ liệu nhỏ, trên các bảng dữ liệu trong bài thuyết trình hoặc báo cáo dashboard của bạn.

Mẹo thứ 23: Tận dụng chức năng App Script.

Apps Script là một Javascript dựa trên ngôn ngữ lập trình có thể được dùng để mở rộng các chức năng của Google Sheets đồng thời mở rộng khả năng tương tác với các dịch vụ khác của Google. 

Các cơ hội và tiện lợi mà App Script mang lại là vô cùng lớn, bao gồm tất cả mọi thừ từ việc tự động hóa các công việc thường ngày cần phải được lặp đi lặp lại đến việc tăng hiệu suất làm việc bằng cách tạo các công thức tùy chỉnh (xem mẹo số 24) để tiết kiệm thời gian trong việc phải thực hiện các công thức phức tạp nhiều lần.

Chức năng này hiệu quả nhất khi sử dụng để thực hiện số lượng lớn các phép tính, đặc biệt các phép tính có xu hướng lặp lại, và tốc độ xử lý của Google Sheets thường sẽ nhanh hơn so với việc thực hiện hàng ngàn các công thức thông thường.

Hãy giả sử rằng tôi có một cột chứa các dữ liệu là các con số mà tôi muốn phân loại thành Small, Medium và Large bằng cách sử dụng mệnh đề IF lồng nhau như sau:

 =IF( A1 > 500 , "Large" , IF( A1 > 250 , "Medium" , "Small" ))

Quá trình này mất khoảng 6.5 giây để tính toán cho 100.000 hàng và khoảng 30 giây nữa để sao chép và dán dưới dạng giá trị (phương pháp hay nhất để tối ưu hóa Google Sheets đang trì trệ của bạn - xem số 6 ở trên).

Thay thế cách tiếp cận này bằng chức năng Apps Script và chạy cho cùng một tập dữ liệu mất khoảng 16 giây để dán tất cả 100.000 giá trị, gần như chỉ bằng một nửa khoảng thời gian sử dụng mệnh đề IF ở trên.

Đây là mã cho chức năng này:

function columnClassifier() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var input = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var output = [];
  
  for (var i = 0; i < input.length; i++) {
    if (input[i][0] > 500) { output.push(["Large"]) }
    else if (input[i][0] > 250) { output.push(["Medium"]) }
    else { output.push(["Small"]) };
  }
  sheet.getRange(1,2,output.length,1).setValues(output);
}

Việc tối ưu hóa mã Apps Script của bạn là một chủ đề hoàn toàn khác và vô cùng chuyên sâu nếu như bạn có thắc mắc, vì vậy tôi sẽ không đưa ra bất kỳ nhận xét nào về nó trong bài viết này. Tuy nhiên, bạn cần lưu ý rằng có những giới hạn về thời gian và số lượng  mã App Script có thể được sử dụng mỗi ngày.

Mẹo thứ 24: Cẩn thận trong việc sử dụng các công thức tùy chỉnh. 

Công thức tùy chỉnh, hay còn được gọi là hàm do người dùng thiết kế hoặc tên ngắn gọn là UDF, là các hàm tùy chỉnh được viết trong cửa sổ trình chỉnh sửa tập lệnh Apps Script.

Các công thức tùy chỉnh phức tạp sẽ hoạt động rất chậm nếu bạn sử dụng chúng theo cách sử dụng các công thức thông thường với việc tìm và truy xuất dữ liệu trên cơ sở từng cell.

Thay vào đó, bạn hãy xem qua công thức tùy chỉnh dưới đây với mục đích là tăng giá tất cả sản phẩm trong một tập dữ liệu lên 15%:

/**
 * Markup a price by 15%
 *
 * @param {number} input The value to markup
 * @return The input multiplied by 1.15.
 * @customfunction
 */
function MARKUP(input) {
  return input * 1.15;
}

Và khi thực hiện trong Google Sheets sẽ trông giống như thế này:

Hình 19: Hàm App Script khi được sử dụng trong Google Sheets

Việc sao chép và dán liên tục 1.000 hàng này là SIÊU chậm. Tôi mất khoảng 10 giây để thực hiện quá trình trên, nhưng nó cũng phụ thuộc vào kết nối Wifi của bạn.

Những gì bạn cần phải làm với các công thức tùy chỉnh, để cải thiện hiệu suất của chúng, là suy nghĩ làm sao để thực hiện chúng giống với công thức mảng hơn. Phần kém hiệu quả của các công thức này là việc đọc và ghi vào bảng tính, vì điều này xảy ra thông qua Application Programming Interface (API) hay giao diện lập trình ứng dụng của bảng tính, do đó nó cũng sẽ phụ thuộc vào kết nối Wifi của bạn.

Cách tốt nhất để áp dụng các công thức tùy chỉnh được thiết kế trên App Script là giảm thiểu tối đa số lần đọc và ghi vào bảng tính nếu như có thể.

Giả sử như bạn muốn thực hiện phép tính trên 10,000 cell, thì tốt hơn hết là bạn công thức đọc hết tất cả 10,000 cell trước một lần, thực hiện phép tính rồi sau đó hẵng trả về kết quả vào trong bảng tính của bạn sau, với tất cả các cell đó được đọc như một phạm vi thay vì đọc từng cell riêng lẻ.

Ví dụ như thay đổi công thức ở trên thành một công thức tổng quan hơn như dưới đây, do công thức này chấp nhận dữ liệu đầu vào là phạm vi và kết quả trả về cũng sẽ là phạm vi với kích thước tương đương:

/**
 * Markup a price by 15%
 *
 * @param {number} input The value or range to markup
 * @return The input multiplied by 1.15.
 * @customfunction
 */
function MARKUP_RANGE(input) {
  if (input.map) {
    return input.map(MARKUP_RANGE);
  }
  else {
    return input * 1.15;
  }
} 

Công thức tùy chỉnh này sẽ thực hiện cùng một phép tính trên 1.000 cell trong vòng chưa đầy một giây, nhanh hơn một cách đáng kể. Đây là cách nó hoạt động trong thực tế, do bây giờ nó chấp nhận dữ liệu đầu vào là phạm vi:

Hình 20: Công thức tùy chỉnh Markup_range trong Google Sheets

Lưu ý: Các công thức tùy chỉnh Apps Script chỉ được tính toán lại khi các yếu tố trong công thức của chúng thay đổi. Ngoài ra, các đối số này phải được xác định, hay nói cách khác là các yếu tố không thể là các công thức không cố định như NOW () hoặc RAND ().  Nếu các công thức tùy chỉnh của bạn cố gắng trả về kết quả bằng việc tính toán các hàm không cố định ở trên, kết quả duy nhất mà bạn nhận được là Loading....

Mẹo thứ 25: Các mẹo khắc phục khác để tăng tốc mỗi khi Google Sheets bị chậm.

Đôi khi vấn đề dẫn đến việc Google Sheets phản hồi chậm không phải là do Google Sheets. Bạn nên thử tìm hiểu xem liệu các nguyên nhân dưới đây có giải quyết được vấn đề của bạn không:

• Đóng và mở lại Google Sheets (đôi khi đó là cách giải quyết đơn giản nhất lại có hiệu quả).
• Làm mới (refresh) trình duyệt của bạn.
• Xóa bộ nhớ cache và cookie của bạn.
• Thử mở bảng tính trên một trình duyệt hay một hệ điều hành khác. Và đảm bảo rằng cả trình duyệt lẫn hệ điều hành cần phải được chạy trên phiên bản mới nhất của chúng.
• Tắt các plugin hoặc tiện ích mở rộng của trình duyệt.
• Nếu có thể, hãy thử mở bảng tính trên một máy tính khác để xem liệu có phải chính máy tính của bạn có đang gặp sự cố hay không. Tốc độ xử lý của Google Sheets phụ thuộc chủ yếu vào khả năng hoạt động cục bộ.
• Thử mở bảng tính sử dụng một kết nối Wifi khác xem liệu có phải kết nối của bạn đang gặp vấn đề hay không. Phần lớn các hàm và các chức năng của Google Sheets được thực hiện trực tiếp trên bảng tính, nhưng có một số công thức truy xuất dữ liệu từ nguồn bên ngoài, như Import, yêu cầu cần phải có kết nối Internet (cũng như tất cả ứng dụng Apps Scripts chạy trên máy chủ của Google).
• Thử tắt kết nối Internet và mở lại bảng tính sử dụng truy cập ngoại tuyến (offline)

Có lẽ đã đến lúc mua một máy tính mới chẳng hạn

Mẹo thứ 26: Kiên nhẫn mỗi khi thực hiện các thay đổi trên điện toán đám mây (cloud).

Bạn nên nhớ rằng nếu như bạn đang làm việc trực tiếp trên Google Sheets với các đồng nghiệp khác, các thay đổi được thực hiện bởi bất kỳ người dùng nào cũng có thể mất vài phút để đồng bộ hóa trên toàn bộ bảng tính.

Những người dùng khác hoặc bất kỳ tệp Apps Scripts cũng sẽ chỉ thực hiện trên phiên bản được lưu trong bộ nhớ cache của Sheet cho đến khi hoàn tất mọi thao tác đồng bộ hóa, và điều này có thể ảnh hưởng đến cách các cell nhất định xuất hiện. Ví dụ: bạn có thể đã tính toán xong trong bảng tính của bạn nhưng vẫn hiển thị trống hoặc #N/A trong bảng tính của các đồng nghiệp.

Ở trên chỉ là một vấn đề bạn cần ghi nhớ nhưng thành thật mà nói, đây chỉ là một vấn đề rất nhỏ. Theo kinh nghiệm của tôi, mọi thao tác được thực hiện nhanh đến mức gần như ngay lập tức, mặc dù trong trường hợp bảng tính Google Sheets thực sự lớn, việc giật lag vẫn sẽ xảy ra.

Mẹo thứ 27: Biết được lúc nên chuyển sang một cơ sở dữ liệu khác.

Sẽ có lúc, và có lẽ bạn gặp trường hợp đó, khi bạn sẽ phải biết điểm dừng và chấp nhận rằng Google Sheets sẽ không đáp ứng được việc chứa tất cả các dữ liệu của bạn. Dù cho tất cả các ưu điểm mà nó đem lại, giới hạn 5 triệu cell sẽ được lấp đầy khá nhanh với dữ liệu trong thời đại thông tin hiện nay.

Và trước khi bất kỳ bảng tính nào của bạn đạt đến giới hạn về số lượng cell, bạn nên suy nghĩ về những gì cần phải làm tiếp theo ngoài các mẹo mà tôi đã hướng dẫn cho bạn trong việc tăng tốc cho Google Sheets ở trên.

Điều duy nhất mà bạn có thể làm vào lúc này là chuyển dữ liệu của bạn vào một kho lưu trữ dữ liệu khác. Bạn vẫn có thể làm việc trên Google Sheets nhưng hãy giảm tải số lượng dữ liệu của bạn và lưu trữ các dữ liệu cũ trong một cơ sở dữ liệu chuyên dụng.

Lời kết

Đó là tất cả các mẹo mà tôi có thể giới thiệu cho bạn!

Tôi đã cố gắng làm cho bài viết này đầy đủ và hữu ích nhất có thể, nhưng tôi hoàn toàn chắc chắn rằng có vô vàn ý tưởng để giúp ích trong việc tăng tốc Goolge Sheets.

Dù bài viết này vô cùng dài, bạn hãy xem đây như là một guide hoàn chỉnh để bạn có thể tham khảo mỗi khi gặp trường hợp ỳ trệ trong Google Sheets. 

Cảm ơn bạn đã đọc và tôi hy vọng những thông tin trong bài viết này có giá trị!

Vậy trong bài viết này bạn đã nắm thêm một chút kiến thức về công cụ Google Sheet rồi, cụ thể là 27 cách mà bạn có thể thử để tăng tốc độ xử lý cho bảng tính của mình. Để tìm hiểu thêm về các kiến thức liên quan đến Google Sheet, bạn hãy truy cập trang gitiho.com nhé.

Bạn có thể tham khảo thêm:

Hướng dẫn liên kết Google Sheets với cơ sở dữ liệu của bạn, sử dụng App Script

Hướng dẫn về biểu đồ phân tán và cách thiết kế chúng trong Google Sheets