Công thức Google Sheet trong app AppSheet: khi nào dùng, khi nào tránh

Với công thức sheet appsheet, quy tắc ngắn gọn là: công thức Google Sheet nằm trong cùng một dòng và giống hệt nhau trên cả cột thì AppSheet vẫn dùng được, nhưng cột đó chỉ đọc trong app và số chỉ cập nhật sau khi đồng bộ (sync). Còn tính toán người dùng cần thấy ngay lúc nhập, hoặc tính trên nhiều dòng, thì nên chuyển sang App formula hoặc virtual column (cột ảo, tính trong app). Tôi là Lê Minh Công, nhiều năm triển khai AppSheet cho doanh nghiệp nhỏ, và gần như ai nối Sheet vào AppSheet cũng vấp đúng chỗ này. Video bên dưới làm mẫu từng bước, còn bài viết này giải thích đầy đủ để bạn đọc mà không cần xem video.

AppSheet xử lý công thức Google Sheet thế nào?

AppSheet tính giá trị công thức khi app đọc dữ liệu từ Sheet, và tính lại khi dữ liệu được cập nhật đồng bộ về Sheet. Điện thoại không tự tính được công thức Google Sheet, nên mọi cột có công thức Sheet đều là chỉ đọc (read-only) trong app: người dùng không gõ đè được.

Có hai loại công thức bạn cần phân biệt:

• Công thức cùng dòng: chỉ dùng ô trên cùng một dòng, ví dụ C2 = A2 + B2.
• Công thức nhiều dòng: tham chiếu sang dòng khác, ví dụ C2 = C1 + 1, hoặc cộng dồn cả cột.

Loại đầu thường dùng được. Loại sau là nguồn của hầu hết cảnh báo mà bạn sẽ gặp ở các phần dưới. Trong bài tôi dùng ví dụ sheet Chi tiết đơn của file «Bán hàng 5F» với các cột: Mã đơn, Sản phẩm, Số lượng, Đơn giá, Thành tiền.

Khi nào công thức Sheet vẫn ổn: spreadsheet formula appsheet cho cột cùng dòng

Công thức Sheet vẫn ổn khi nó nằm trong cùng dòng và giống hệt nhau trên cả cột. Ví dụ cột Thành tiền trong Chi tiết đơn là =C2*D2 ở dòng 2, =C3*D3 ở dòng 3, và cứ thế đến hết bảng.

Khi cả cột đồng nhất như vậy, AppSheet nhận ra và tự chèn công thức cho dòng mới mà người dùng thêm từ app. Bạn xem thiết lập này ở thuộc tính Spreadsheet formula (Data > Columns > chọn cột > Auto Compute). Tên menu có thể đổi theo thời gian, nên nếu giao diện khác thì hãy đối chiếu tài liệu chính thức.

Đổi lại có hai cái giá cần biết:

• Cột chỉ đọc: nhân viên bán hàng không sửa tay được Thành tiền trong app.
• Số chỉ xuất hiện sau khi sync. Nhân viên đứng ở chợ không có sóng, nhập Số lượng và Đơn giá xong sẽ chưa thấy Thành tiền.

Lỗi mismatched formulas appsheet: vì sao và cách sửa

Cảnh báo «Column … contains mismatched formulas» nghĩa là các ô trong cùng một cột đang dùng những công thức khác nhau, nên AppSheet không dám gán một công thức chung cho cả cột. Lỗi thường hiện khi bạn thêm bảng hoặc bấm Regenerate (tạo lại cấu trúc bảng từ Sheet).

Cách AppSheet kiểm tra: nó đổi công thức từng ô từ dạng A1 sang dạng R1C1 rồi so sánh. Công thức nào chiếm tỉ lệ cao nhất sẽ được gán cho cả cột. Ô trống cũng được tính là một «loại công thức» (Empty Formula). Cảnh báo nêu số chỗ bị lệch và 5 công thức lệch đầu tiên kèm số dòng.

Ví dụ: cột Thành tiền có 100 dòng là =C2*D2, nhưng một bạn kế toán gõ đè số 150000 vào dòng 57, và dòng 101 để trống. Hai ô đó lệch so với phần còn lại.

Cách sửa theo thứ tự:

1. Đọc cảnh báo, ghi lại số dòng của các ô lệch.
2. Mở Sheet, làm công thức các ô đó giống hệt các ô còn lại (kéo công thức xuống, xoá số gõ tay, điền công thức vào ô trống).
3. Quay lại AppSheet và Regenerate bảng.

Nguyên nhân phổ biến mà tài liệu chính thức nêu là tham chiếu ô cố định viết sai. Giả sử bạn cố định ô chiết khấu ở B2: viết $B2 thì hàng vẫn tương đối, khi kéo xuống thành $B3, $B4… và khi đổi sang R1C1 các ô trông khác nhau. Viết $B$2 thì cả cột và hàng đều cố định, các ô khớp nhau.

Dòng tổng cộng, tổng phụ: đừng để trong bảng dữ liệu

Đừng đặt dòng «Tổng cộng» hay tổng phụ ở cuối bảng dữ liệu, vì mọi dòng trong một bảng phải cùng bản chất: đều là dòng hàng của đơn. Dòng tổng dùng công thức nhiều dòng sẽ bị AppSheet bỏ qua sau khi báo cảnh báo, và tổng phụ cũng vậy.

Thói quen này rất phổ biến ở file Excel cũ của cửa hàng: dòng cuối cộng tiền cả bảng, giữa bảng chen thêm dòng «Tổng nhóm hàng». Khi chuyển lên app, hãy làm một trong hai cách:

• Chuyển tổng ra một sheet báo cáo riêng, tách khỏi sheet dữ liệu.
• Tính ngay trong app bằng virtual column, ví dụ cột Tổng tiền đơn cộng Thành tiền của các dòng cùng Mã đơn (thường dùng hàm SELECT() để lọc các dòng đó).

ARRAYFORMULA appsheet và các hàm chỉ có ở Google Sheets

Với arrayformula appsheet, khuyến nghị của tôi là tránh đặt ARRAYFORMULA trong sheet làm bảng dữ liệu của app; nếu cần thì để ở sheet phụ. Lý do nằm ở cách AppSheet đọc Google Sheet: nó xuất sheet ra file .xlsx, đọc bằng thư viện Excel, rồi đổi công thức từ A1 sang R1C1 để suy ra thuộc tính cột. Việc này lặp lại mỗi lần Regenerate.

Tài liệu chính thức có liệt kê các hàm chỉ có ở Google Sheets, không có bản tương đương trong Excel, gồm ARRAYFORMULA, QUERY, FILTER, SORT, IMPORTRANGE, SPLIT, JOIN, IMAGE… (ngoài ra còn những hàm như IMPORTXML, GOOGLEFINANCE, GOOGLETRANSLATE, SPARKLINE). Tài liệu không mô tả AppSheet xử lý cụ thể từng hàm ra sao, nên tôi không khẳng định chắc chắn «sẽ lỗi». Tôi chỉ khuyên tránh đặt chúng trong bảng dữ liệu của app, vì rủi ro không đáng.

Trả lời câu hỏi hay gặp: dùng được ở sheet phụ thì được. Ví dụ bạn dùng QUERY hoặc IMPORTRANGE để gom số liệu từ nhiều file thành báo cáo ở một tab riêng, người quản lý mở Sheet xem, còn app chỉ đọc các tab dữ liệu «sạch». Muốn hiểu thêm hàm này bạn có thể đọc bài ARRAYFORMULA trong Google Sheet. Chi tiết về hàm hỗ trợ có trong tài liệu AppSheet về cách đọc công thức Sheet.

Dữ liệu pivot appsheet: đưa về dạng hàng

AppSheet không làm việc với dữ liệu xoay (pivot): tức là sheet mà tên trường chạy dọc theo cột đầu tiên và mỗi cột là một bản ghi. App được dựng từ cấu trúc cột, nên mỗi dòng phải là một bản ghi, mỗi cột là một trường.

Ví dụ sheet Nhân viên của một văn phòng có cột A ghi «Họ tên, Phòng ban, Lương» xuống dọc, còn cột B, C, D là từng người. Hãy xoay lại bằng Paste Transpose (dán chuyển vị trong Google Sheet) để mỗi người thành một dòng.

Ngược lại, nếu bạn cần bảng tổng hợp kiểu pivot (doanh số theo tháng, theo nhóm hàng), hãy làm ở file hoặc sheet khác bằng SUMIF/SUMIFS, đừng biến bảng dữ liệu của app thành bảng xoay. Bạn có thể xem cách làm bảng tổng hợp ở bài Pivot table trong Google Sheet, và cách chuẩn bị bảng nguồn ở bài Chuẩn bị Google Sheet cho AppSheet.

App formula hay công thức Sheet: chọn thế nào?

Hãy dùng App formula cho thứ người dùng cần thấy ngay lúc nhập, vì nó chạy khi người dùng cập nhật dữ liệu trong app, kể cả khi app offline. Công thức Sheet chỉ chạy khi dữ liệu đi tới hoặc đi từ Sheet. Hai cơ chế này bổ sung cho nhau, không có bên nào đúng tuyệt đối.

Tiêu chí	Công thức Google Sheet	App formula
Chạy khi nào	Khi dữ liệu đọc từ hoặc đồng bộ về Sheet	Ngay khi người dùng nhập trong app
Offline	Không có kết quả khi offline	Chạy được khi offline
Sửa trong app	Chỉ đọc	Tuỳ cấu hình cột
Hợp với	Báo cáo, tổng hợp trong Sheet, người dùng xem trực tiếp file	Thành tiền trên form, giá trị cần thấy tức thì

Quy tắc gợi ý của tôi:

• Thành tiền trên form bán hàng: App formula, ví dụ [Số lượng] * [Đơn giá].
• Tính trên nhiều dòng như Tổng tiền đơn, Công nợ khách: virtual column.
• Báo cáo tổng hợp: sheet riêng, dùng SUMIFS hoặc công cụ báo cáo bạn quen.

Công bằng mà nói, công thức Sheet vẫn có chỗ hợp: nếu nhiều người dùng chung file Sheet ngoài app, hoặc kế toán đang quen kiểm tra số trong Sheet, giữ công thức cùng dòng ở đó là hợp lý. Đổi sang App formula nghĩa là bạn phải kiểm tra lại cột sau khi chuyển. Virtual column thì tiện nhưng tính mỗi lần app mở; với bảng rất lớn, hãy theo dõi tốc độ.

Tóm tắt và bước tiếp theo

• Cột có công thức Sheet là chỉ đọc trong app và chỉ cập nhật sau sync.
• Công thức cùng dòng, đồng nhất cả cột thì ổn; AppSheet tự chèn công thức cho dòng mới.
• Mismatched formulas: làm các ô lệch giống nhau (chú ý $B$2 thay vì $B2) rồi Regenerate.
• Không để dòng tổng trong bảng dữ liệu; tránh ARRAYFORMULA, QUERY, IMPORTRANGE… trong bảng làm dữ liệu cho app.
• Dữ liệu xoay phải đưa về dạng hàng; tổng hợp kiểu pivot làm ở sheet khác.

Việc nên làm ngay: mở Sheet Chi tiết đơn của bạn, kiểm tra từng cột có công thức xem có ô nào lệch, có dòng tổng nào nằm cuối bảng không, rồi quyết định cột nào chuyển sang App formula. Nếu muốn học bài bản cách dựng cả app từ đầu, bạn có thể xem các lớp tại trang khoá học.

Nguồn tham khảo: tài liệu chính thức của AppSheet về cột có công thức trong Sheet.

Xem thêm

• Chuẩn bị Google Sheet cho AppSheet: tiêu đề cột, định dạng, nhiều sheet
• ARRAYFORMULA trong Google Sheet: một công thức cho cả cột, kèm ví dụ