Tạo các phạm vi động trong Google Sheets mà không cần ô trợ giúp (Helper Cell) là một phương pháp duy nhất mà bạn có thể tìm hiểu tại đây. Bạn có thể tạo các phạm vi động trong Google Sheets mà không cần ô trợ giúp (Helper Cell).
Bằng cách nói ô trợ giúp (Helper Cell), tôi muốn nói rằng chính công thức sẽ xử lý các phạm vi động mà không cần tham chiếu đến bất kỳ ô hoặc cột bổ sung nào.
Đây là sự kết hợp của các hàm giúp phạm vi trong công thức luôn luôn động. Bạn chỉ cần đặt và rời phạm vi động trong SUM, SUMIF, SUMIFS, COUNTIF, MAX, MIN hoặc bất kỳ công thức nào chấp nhận các phạm vi cột.
Sau đó, bạn có thể thêm hoặc xóa bất kỳ số hàng nào trong dữ liệu của bạn, điều đó sẽ tự động được điều chỉnh trong phạm vi. Phạm vi động trong Google Sheets mà không có các ô trợ giúp (Helper Cell) là một hiện thực.
Tôi đã trước đây đã giải thích cách tạo một phạm vi động bằng cách sử dụng hàm OFFSET trong Google Sheets. Khác biệt giữa công thức đó với công thức mới này là gì?
Công thức Dynamic Offset so với Công thức Mới (So sánh nhanh)
Cả hai đều có điểm có lợi và có mất. Đây là so sánh đó.
Trước khi tiếp tục với hướng dẫn phạm vi động mới và mới này, bạn có thể xem các hướng dẫn sau của tôi để hiểu các hàm chính sử dụng trong công thức mới này. Chúng là INDIRECT và ADDRESS. Điều này sẽ giúp bạn đọc công thức của tôi tốt hơn.
Tạo Phạm vi Động trong Google Sheets Mà Không Cần Ô Trợ Giúp (Theo Cách Tối Ưu)
Công thức Phạm vi Động dưới đây chỉ xem xét các giá trị hiện có trong một phạm vi cột và tự động điều chỉnh phạm vi khi bạn thêm giá trị mới vào cuối các giá trị hiện có. Nói cách khác, phạm vi động kết thúc khi xuất hiện một ô trống trong cột.
Lợi ích của việc sử dụng phạm vi động là bạn luôn có không gian bổ sung trong Bảng tính dưới phạm vi dữ liệu hiện có của bạn.
Nếu bạn sử dụng một phạm vi vô hạn như G2:G hoặc G:G trong một công thức, bạn không thể sử dụng các ô trống phía dưới cho mục đích khác.
Bạn chỉ cần bỏ qua một hàng và giữ cho hàng đó luôn trống. Bạn có thể sử dụng các hàng còn lại cho mục đích khác.
Bây giờ là thời điểm để xem công thức.
Tương tự: Cách Sử Dụng Phạm vi Động trong Công thức SUMIF trong Google Sheets (Sử dụng hàm Offset)
Dưới đây là Công thức Mới và Dữ liệu mẫu. Tôi đang sử dụng các phạm vi động trong hàm SUM ở đây.
Công thức dường như phức tạp. Nhưng nó thực sự không phải như vậy. Bạn có thể sử dụng nó ngay lập tức.
Công thức Phạm vi Động trong Google Sheets Mà Không Tham Chiếu Đến Ô Trợ Giúp
Trước khi giải thích cách tạo công thức phạm vi động ở trên, hãy cho tôi nói cho bạn biết cách bạn có thể sử dụng nó.
Công thức trên tính tổng cột G. Bạn có thể nhập bất kỳ giá trị nào trong các ô G10, G11, G12 và các ô khác. Nó sẽ được thêm vào phạm vi TỔNG.
Nếu xuất hiện bất kỳ ô trống nào trong cột này, công thức sẽ trả về kết quả sai. Bởi vì công thức chỉ tính tổng đến phạm vi đó.
Nếu bạn chèn một hàng vào giữa phạm vi, hãy nhớ để lại ít nhất giá trị 0 trong cột tương ứng được sử dụng (cột G ở đây). Hãy nhớ điều này nếu bạn sử dụng công thức của tôi.
Giải thích Công thức:
Bạn có thể sao chép công thức phạm vi động bên dưới.
Công thức:
=sum(G2:indirect(address(ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1))),7,4)))Ở đây, G2 là địa chỉ ô bắt đầu của phạm vi. Nó có thể là bất kỳ địa chỉ ô nào trong bất kỳ cột nào dựa trên phạm vi dữ liệu của bạn.
Dưới đây là chi tiết về cách tôi đã mã hóa công thức tuyệt vời trên.
=ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1)Nó đếm phạm vi G2:G cho đến khi xuất hiện bất kỳ ô trống nào. Tôi đã miêu tả công thức này chi tiết trong một hướng dẫn riêng biệt. Xem liên kết ngay dưới phần giải thích công thức này.
Công thức này chỉ trả về giá trị 9. Vui lòng tham khảo hình ảnh số 2 để xem dữ liệu mẫu và chú ý đặc biệt của bạn đến cột G. Nếu bạn sử dụng công thức Đếm thông thường, kết quả chỉ là 8. Công thức trên trả về số đếm + 1.
Khi công thức này bao bọc bằng một công thức Địa chỉ, nó sẽ trông như sau:
=address(9,7,4)Ở đây, bạn phải thay thế 9 bằng công thức đếm trên.
=address(ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1))),7,4)Công thức địa chỉ này trả về địa chỉ ô G9. Ở đây, số 7 là số cột của G và số 4 là ‘absolute_relative_mode’ trong hàm Address.
Công thức cuối cùng là:
=sum(G2:indirect(G9)Trong đó, G9 là công thức Địa chỉ ở trên.
Làm Thế Nào Để Đếm Cho Đến Hàng Trống Trong Google Sheets
Làm thế nào để sử dụng phạm vi động này trên Bảng Của Tôi?
Tôi biết rằng bạn có thể muốn sử dụng công thức này trong một cột khác ngoài G. Nếu như vậy, đây là điều duy nhất bạn có thể muốn thay đổi.
Trong công thức, chỉ cần thay đổi:
- G2 – Đổi địa chỉ ô này thành địa chỉ ô bắt đầu của bạn trong phạm vi.
- G – Điều này không cần phải nói.
- 7 – Đây là số thứ tự của cột G. Số thứ tự cho cột A = 1, B = 2 … và G = 7. Thay đổi nó tùy thuộc vào điều này.
Cuối cùng, bạn có thể sử dụng các phạm vi động trên Google Sheets trong các công thức khác ngoài TỔNG.
Hãy nhớ rằng chúng ta chưa sử dụng bất kỳ ô trợ giúp nào hoặc cột nào để tạo ra phạm vi động này. Đó là điều tuyệt vời của công thức này.
Chỉ sử dụng nó khi bạn hiểu giới hạn của nó trong việc xử lý các ô trống. Đó là tất cả những gì tôi muốn chia sẻ. Hãy vui vẻ!
Truy cập Bảng Google của tôi tại đây để xem công thức hoạt động.
