Trong bài viết này, mình sẽ chia sẻ cách bạn sử dụng hàm ARRAYFORMULA trong Google Sheets để có thể nhanh chóng điền công thức tính toán cho cả một cột trong Google Sheets. Công thức sẽ tự động mở rộng khi có dữ liệu mới.
Trong trường hợp bạn đang làm việc với Google Sheets và cần phải copy công thức cho tới dòng cuối cùng mỗi khi dữ liệu mới được thêm vào, ví dụ khi bạn sử dụng Google Form để thu thập dữ liệu, và với mỗi dòng dữ liệu mới, bạn cần copy công thức và áp dụng cho dữ liệu mới.
Có một vài cách để xử lý vấn đề này.
Copy công thức xuống cho các dòng bên dưới trong Google Sheets
Cách dễ nhất để copy công thức xuống cho các ô bên dưới là sử dụng fill handle trong Google Sheets. Fill handle là ô vuông ở góc dưới bên tay phải khi bạn chọn một vùng dữ liệu, bạn có thể dùng chuột để nhấn giữ và kéo fill handle này để copy nội dung hoặc công thức cho các ô bên dưới. Fill handle không chỉ copy giá trị, công thức mà còn copy cả định dạng.
Nếu bạn muốn copy công thức và bỏ qua bất cứ định dạng dữ liệu nào, bạn có thể sử dụng lựa chọn Paste special khi bấm chuột phải vào một vùng như hình minh hoạ dưới đây
Sử dụng công thức mảng ArrayFormula để copy công thức cho cả cột trong Google Sheets
Trong trường hợp bạn đang xử lý một vài trăm dòng dữ liệu trong Google Sheets, mỗi lần có dữ liệu mới, chúng ta lại cần kéo lại công thức sử dụng Fill Handle, thì thật mất thời gian và không hiệu quả. Chúng ta có cách giải quyết vấn đề này.
Đầu tiên, bạn hãy chọn ô đầu tiên trong cột, sau đó vẫn nhập công thức cần thiết cho cột đó như bình thường. Tuy nhiên, thay vì chỉ gõ B2 như công thức ở phần trên, thì bạn hãy tham chiếu tới vùng B2:B (ý nghĩa: tham chiếu từ ô B2 tới ô cuối cùng của cột B). Sau khi đã hoàn thành công thức, hãy bấm tổ hợp phím Ctrl+Shift+Enter trên máy tính Windows hoặc Cmd+Shift+Enter nếu bạn đang sử dụng máy Mac, và Google Sheets sẽ tự động thêm công thức ARRAYFORMULA bao ra ngoài công thức của bạn.
Sau khi áp dụng công thức trên, bạn thấy rằng, chúng ta chỉ cần nhập một lần công thức vào ô đầu tiên của cột, cả cột sẽ được điền công thức tương tự. Công thức mảng này sẽ hiệu quả hơn vì chúng ta chỉ cần nhập vào một ô, khi cần thay đổi hay cập nhật, chúng ta cũng chỉ cần cập nhật một ô này.
Một vấn đề duy nhất mà chúng ta đang có hiện tại là: khi chúng ta áp dụng công thức như trên, toàn bộ cột đều được áp dụng công thức, những dòng không có dữ liệu cũng được áp dụng công thức.
Cách xử lý vấn đề này như sau: thêm một hàm IF vào công thức mảng ARRAYFORMULA của chúng ta để biết khi nào chúng ta cần điền công thức và khi nào không cần. Chúng ta sẽ chỉ điền công thức khi một ô trong cột khác không trống. Google Sheets có hai cách để kiểm tra một ô có rỗng hay không.
- ISBLANK(A1) – trả về TRUE nếu ô A1 rỗng
- LEN(A!) <> 0 – trả về TRUE nếu ô A1 không rỗng, FALSE nếu A1 rỗng
Do vậy, chúng ta có thể sửa công thức cho cột C như sau:
Sử dụng ISBLANK
=ArrayFormula(IF(ISBLANK(B2:B), “”, ROUND(B2:B*18%, 2)))
Ngoài cách sử dụng ISBLANK như trên, chúng ta còn có cách khác như sau:
=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), “”))
Hoặc
=ArrayFormula(IF(B2:B=””, “”, ROUND(B2:B*18%, 2)))
Sử dụng công thức mảng ngay trong tiêu đề của bảng
Trong ví dụ trước của chúng ta, dòng tiêu đề (Product, Price, Tax, Amount) được điền trước, và công thức của chúng ta bắt đầu từ dòng thứ hai.
Chúng ta có thể làm tốt hơn công thức của chúng ta như sau: nếu công thức đang ở dòng thứ nhất, chúng ta sẽ điền tiêu đề là giá trị công thức đó. Với suy nghĩ này, chúng ta có thể kiểm tra xem công thức đang đứng ở dòng thứ mấy bằng cách sử dụng hàm ROW(), nếu kết quả của ROW(B:B) = 1, ta sẽ trả về “Tax”, nếu kết quả không phải là 1, chúng ta sẽ thực hiện tính toán như bình thường.
Công thức có thể viết như sau:
=ArrayFormula(IF(ROW(B:B)=1,”Tax”,IF(ISBLANK(B:B),””,ROUND(B:B*18%, 2))))
Như vậy, với kiến thức học được trong bài này, bạn có thể áp dụng cho trường hợp xử lý dữ liệu về từ Google Forms. Khi một dòng dữ liệu mới được thêm vào Google Forms, bạn có thể thiết lập công thức sẵn để công thức trong các cột tự tạo được mở rộng ra một cách tự động.
Làm thế nào để sử dụng VLOOKUP trong công thức mảng ARRAYFORMULA
Một ứng dụng nữa của kỹ thuật sử dụng công thức mảng này trong Google Sheets là kết hợp hàm VLOOKUP với công thức mảng ARRAYFORMULA
Giả sử bạn có một bảng “Fruits” liệt kê tên hoa quả trong cột A và giá của hoa quả đó trong cột B. Bảng thứ hai là bảng “Orders” có tên sản phẩm trong cột A, số lượng trong cột B. Chúng ta sẽ muốn điền giá trị hàng đặt vào cột C. Chúng ta có thể sử dụng công thức như sau
=ArrayFormula( IF(ROW(A:A)=1, “Total”, IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!$A$2:$B$10, 2, FALSE) * B:B, “” )))
Nếu vùng tra cứu không ở cùng trong 1 tài liệu Google Sheets, bạn có thể sử dụng hàm IMPORTRANGE để kết hợp vào trong công thức mảng phía trên.
Lưu ý: trong bài viết sử dụng dấu phẩy là dấu ngăn cách giữa các thành phần trong hàm, có thể trên tài liệu của bạn được thiết lập locales khác, nếu nhập công thức gặp lỗi, bạn có thể xem lại thiết lập này hoặc chuyển dấu phẩy thành dấu chấm phẩy khi nhập công thức.
