Bạn đã bao giờ gặp phải tình huống công thức mảng bị hỏng khi sắp xếp dữ liệu trong Google Sheets chưa? Đó là một vấn đề khá phổ biến và gây khó chịu. Nhưng đừng lo, chúng tôi đã tìm ra cách giải quyết vấn đề này và sẽ chia sẻ với bạn ngay bây giờ.
Vấn đề công thức mảng bị lỗi khi sắp xếp
Công thức mảng thường gặp vấn đề khi nằm trong phạm vi chúng ta sắp xếp. Vậy làm sao để ngăn công thức mảng bị lỗi khi sắp xếp trong Google Sheets?
Trong hầu hết các trường hợp, chúng ta có thể ngăn công thức mảng bị lỗi khi sắp xếp. Giải pháp đơn giản là di chuyển công thức mảng lên một hàng trên phạm vi sắp xếp.
Hãy cùng tôi đi vào chi tiết hơn về cách điều chỉnh công thức để giải quyết vấn đề này. Nhưng chú ý, không phải cách giải quyết này hoạt động trong tất cả các trường hợp. Dưới đây là một ví dụ về công thức mảng bị lỗi khi sắp xếp trong Google Sheets.
Như bạn có thể thấy, có một công thức mảng trong ô C2 trả về điểm số của học sinh dựa trên số điểm của họ. Đây là một công thức mảng IF lồng nhau hoạt động như sau (không phải là một giải thích công thức).
Nếu ‘Tổng điểm trên tổng số 1000’ của một học sinh là >900, công thức sẽ gán điểm “A” cho học sinh đó trong cột C. Đối với điểm số > 700, công thức gán điểm “B”, nếu điểm số > 500, công thức gán điểm “C”, ngược lại là điểm “D”.
Tôi đã gán điểm bằng một công thức mảng, đúng không? Bây giờ, tôi muốn sắp xếp tên học sinh theo thứ tự chữ cái.
Điều đó có thể thực hiện bằng cách chọn phạm vi A2:C8 và sắp xếp dữ liệu bằng cách nhấp chuột vào menu Dữ liệu Sắp xếp phạm vi theo cột A, A->Z.
Sau khi sắp xếp, kiểm tra cột C. Bạn sẽ thấy rằng sắp xếp đã làm hỏng công thức mảng trong cột đó. Điều này xảy ra vì hàng chứa công thức mảng đã di chuyển xuống.
Cách giải quyết công thức mảng bị lỗi khi sắp xếp
Để ngăn công thức mảng bị lỗi khi sắp xếp trong Google Sheets như trên, chúng ta có thể sử dụng một mẹo nhỏ. Đó là nhập công thức mảng bên ngoài phạm vi sắp xếp! Thay vì nhập công thức mảng vào ô C2, dựa trên ví dụ của tôi ở trên, bạn có thể sử dụng công thức trong ô C1.
Điều đó có nghĩa là để tránh công thức mảng bị lỗi khi sắp xếp, hãy nhập công thức mảng vào hàng tiêu đề chứa tên cột.
Nhưng bạn không thể sử dụng công thức như vậy ngay tức thì. Bạn có thể cần thay đổi một số điều nhỏ trong công thức theo cú pháp dưới đây.
={"tên cột"; công thức mảng}
Điều đó có nghĩa là bạn nên thêm tên cột vào công thức mảng. Làm thế nào?
Có hai tùy chọn/phương pháp để làm điều này.
- Nếu bạn sắp xếp dữ liệu của mình bằng lệnh Sắp xếp trong menu Dữ liệu, hãy sử dụng tùy chọn #1 hoặc #2 dưới đây.
- Nếu bạn sắp xếp dữ liệu của mình bằng cách sử dụng lệnh Sắp xếp trong menu Dữ liệu hoặc lệnh Sắp xếp trong chế độ Xem Bộ lọc, bạn có thể tuân thủ tùy chọn #2.
Bạn có thể sử dụng các phương pháp của tôi (tùy chọn #1 hoặc tùy chọn #2 dưới đây) để ngăn mọi công thức mảng bị lỗi khi sắp xếp. Tôi đã chọn công thức mảng IF lồng nhau để làm ví dụ.
Tùy chọn #1
Công thức mảng bị lỗi khi sắp xếp (cách ngăn chặn)
Dựa trên cú pháp trên và dựa trên ví dụ của tôi, đây là công thức mảng không bị lỗi khi sắp xếp trong Google Sheets.
={"Grade";ArrayFormula(if(B2:B>900,"A",if(B2:B>700,"B",if(B2:B>500,"C",if(B2:B>0,"D",)))))}
Công thức này nên được nhập vào ô C1, không phải ô C2. Vì bạn không sắp xếp hàng 1, công thức sẽ không bị hỏng khi sắp xếp.
Tùy chọn #2
Lỗi công thức mảng khi sắp xếp trong Xem Bộ lọc trong Google Sheets (cách ngăn chặn)
Công thức của tùy chọn #1 có một vấn đề khi sắp xếp dữ liệu bằng lệnh Xem Bộ lọc trong menu Dữ liệu.
Tôi đã sắp xếp cột A, A->Z bằng cách sử dụng tùy chọn sắp xếp trong menu Dữ liệu > Xem Bộ lọc. Lần này, sắp xếp đã làm hỏng công thức mảng trong cột C.
Vậy có cách nào giải quyết cả hai tình huống này (tùy chọn #1 và tùy chọn #2)? Có! Hãy sử dụng công thức này.
=ArrayFormula(if(row(A:A)=1,"Grade",if(B:B>900,"A",if(B:B>700,"B",if(B:B>500,"C",if(B:B>0,"D",))))))
Ở đây, điểm quan trọng là không sử dụng phạm vi mở như A1:A hoặc B1:B trong công thức mảng. Thay vào đó, hãy sử dụng phạm vi mở như A:A, B:B.
Đó là tất cả về cách ngăn công thức mảng bị lỗi khi sắp xếp trong Google Sheets.
Đọc thêm:
