Giải thích phạm vi tràn trong Excel

Hướng dẫn giải thích ý tưởng về “phạm vi tràn” bằng ngôn ngữ đơn giản và đưa ra câu trả lời cho các câu hỏi phổ biến nhất.

Spilling là một trong những tính năng mới được giới thiệu trong Excel 365 như một phần của chức năng mảng động. Và để tận dụng tối đa, điều quan trọng là phải hiểu các thuật ngữ chính để bạn có thể làm theo các ví dụ và xây dựng hiệu quả các công thức mảng động của riêng mình. Hiểu khái niệm phạm vi tràn là trọng tâm của hướng dẫn này.

Phạm vi tràn trong Excel

Khi một công thức mảng động tạo ra nhiều giá trị do kết quả của phép tính, nó xuất ra hoặc “tràn” tất cả các giá trị đó lên trang tính.

Phạm vi tràn đề cập đến một mảng các giá trị được trả về bởi một công thức mảng động trong các ô lân cận.

Khi bạn chọn bất kỳ ô nào trong vùng tràn, toàn bộ phạm vi được đánh dấu bằng đường viền màu xanh lam cho biết rằng mọi thứ bên trong nó được tính bằng công thức trong ô trên cùng.

Trước đây, với các công thức mảng CSE truyền thống, chúng ta phải đoán có bao nhiêu ô để sao chép một công thức. Bây giờ, bạn chỉ cần nhập công thức vào đơn bào và để Excel lo phần còn lại.

Phạm vi tràn là động và cập nhật tự động khi dữ liệu nguồn thay đổi. Khi bạn thêm hoặc xóa các mục vào / khỏi dữ liệu nguồn, phạm vi tràn có thể mở rộng hoặc thu hẹp lại.

Nếu bạn xóa công thức trong ô đầu tiên, tất cả kết quả sẽ không còn nữa.

Toán tử phạm vi tràn (# ký tự)

Để tham chiếu đến toàn bộ phạm vi tràn, hãy đặt thẻ băm (#) sau địa chỉ của ô phía trên bên trái trong phạm vi (tức là ô chứa công thức trả về một mảng giá trị).

Ví dụ, chúng ta có một công thức DUY NHẤT trong C2, công thức này trả về nhiều kết quả. Để tham chiếu tất cả các giá trị đó, hãy nhập:

=C2#

Để tham khảo cùng một phạm vi tràn từ tờ khác, bao gồm tên của trang tính theo sau là dấu chấm than:

=Sheet1!C2#

Để tham chiếu phạm vi tràn từ một sổ làm việc khác, cũng bao gồm tên của tệp được đặt trong dấu ngoặc vuông:

=[Book1.xlsx]Sheet1!$C2#

Nó giống như tham chiếu toàn bộ phạm vi (= C2: C8), nhưng không giống như tham chiếu phạm vi thông thường, tham chiếu tràn tự động phản ánh các thay đổi trong kích thước phạm vi. Hành vi này đặc biệt hữu ích khi bạn đang “cung cấp” phạm vi tràn cho một chức năng khác, động hoặc thông thường. Bạn thậm chí có thể sử dụng nó cho các phạm vi đã đặt tên và xác thực dữ liệu, ví dụ: để tạo danh sách thả xuống phụ thuộc động.

Trong trường hợp của chúng tôi, để tìm có bao nhiêu tên duy nhất được trả về bởi công thức mảng động trong C2, hãy cung cấp tham chiếu phạm vi tràn cho hàm COUNTA:

=COUNTA(C2#)

Tiền boa. Để nhanh chóng tham chiếu đến phạm vi tràn, hãy chọn tất cả các ô bên trong hộp màu xanh lam bằng chuột. Excel sẽ tự động tạo một tham chiếu tràn thích hợp.

Ghi chú. Toán tử phạm vi tràn chỉ hỗ trợ các tham chiếu đến mở sổ làm việc. Nếu sổ làm việc nguồn bị đóng, lỗi #REF! sẽ xảy ra lỗi. Để khắc phục lỗi, chỉ cần mở sổ làm việc được giới thiệu.

3 điều bạn nên biết về phạm vi tràn trong Excel

Phạm vi tràn là một tính năng thực sự tuyệt vời giúp cuộc sống của người dùng Excel dễ dàng hơn rất nhiều. Dưới đây là một vài thông tin thú vị giúp bạn hiểu rõ hơn về khái niệm này.

Chỉ có thể chỉnh sửa ô trên cùng bên trái

Vì chỉ ô trên cùng trong vùng tràn mới chứa công thức nên chỉ ô đó có thể chỉnh sửa được. Khi bạn chọn bất kỳ ô nào khác trong vùng viền xanh lam, công thức vẫn được hiển thị trong thanh công thức nhưng chuyển sang màu xám.

Khi bạn thay đổi công thức trong ô đầu tiên và nhấn Đi vào, Excel sẽ tự động cập nhật tất cả các giá trị khác trong vùng bị tràn.

Những gì được và những gì không được bao gồm trong phạm vi tràn

Như đã đề cập ở trên, hành vi tràn là động – khi dữ liệu ban đầu thay đổi, vùng bị tràn sẽ điều chỉnh theo. Ví dụ: nếu bạn thay đổi danh sách ban đầu để nó chứa thêm một tên duy nhất (Carter), phạm vi tràn tự động mở rộng để bao gồm tên đó.

Tuy nhiên, phạm vi tràn không cập nhật khi các mục nhập mới được thêm vào ngoài phạm vi được giới thiệu.

Ví dụ: nếu công thức đề cập đến A2: A10 và một mục mới được nhập vào A11, nó sẽ không xuất hiện trong kết quả:

Để mục mới được đưa vào kết quả công thức, bạn cần thay đổi phạm vi được tham chiếu thành A2: A11.

Nếu bạn muốn những thay đổi như vậy được phản ánh nhanh chóng, thì hãy đặt dữ liệu nguồn của bạn vào Bảng Excel Và sử dụng tài liệu tham khảo có cấu trúc trong công thức của bạn. Không giống như phạm vi, bảng Excel tự động mở rộng để kết hợp các hàng mới. Hiệu quả tương tự có thể đạt được với phạm vi được đặt tên động.

Lỗi #SPILL

Nếu có thứ gì đó đang chặn phạm vi tràn (ví dụ: dữ liệu khác, khoảng trắng, ký tự không in được, công thức trong các ô bên dưới, v.v.), lỗi #SPILL sẽ xảy ra. Để giải quyết lỗi, hãy xóa các ô cản trở. Để biết thêm thông tin, vui lòng xem Lỗi SPILL trong Excel – nguyên nhân và cách khắc phục.

Để kết thúc mọi thứ, chúng tôi sẽ đăng câu trả lời cho ba câu hỏi thường gặp (3 dường như là một con số kỳ diệu trong hướng dẫn này

Làm cách nào để xóa phạm vi tràn trong Excel?

Tùy thuộc vào những gì bạn đang cố gắng đạt được, có hai giải pháp:

Để giải quyết lỗi #SPILL xảy ra do phạm vi tràn không trống, xóa dữ liệu chặn khỏi vùng bị tràn hoặc di chuyển công thức đến vị trí mới nơi có đủ ô trống để xuất tất cả kết quả. Vui lòng theo liên kết trên để biết thêm chi tiết.

Để loại bỏ tất cả các giá trị trong vùng tràn, hãy xóa công thức trong ô đầu tiên.

Làm cách nào để bạn thay đổi phạm vi tràn trong Excel?

Vì phạm vi tràn là kết quả của một công thức, nó không thể được thay đổi theo cách thủ công.

Trong trường hợp bạn cần sửa đổi hoặc cập nhật công thức, hãy chọn ô phía trên bên trái trong vùng tràn, thực hiện các thay đổi cần thiết và nhấn phím Enter.

Làm thế nào để sửa phạm vi tràn trong Excel?

Kích thước của phạm vi tràn được xác định bởi Excel và thay đổi tự động khi dữ liệu nguồn hoặc công thức được cập nhật.

Trong một số trường hợp, bạn có thể đặt kích thước phạm vi tràn từ trong chính công thức. Làm ví dụ, vui lòng xem Cách giới hạn số hàng được FILTER trả về.

Nếu bạn gặp phải lỗi #SPILL, vui lòng xem các giải pháp sau: Cách khắc phục lỗi #SPILL! lỗi trong Excel.

Làm cách nào để loại bỏ phạm vi tràn trong Excel?

Một lần nữa, câu trả lời phụ thuộc vào mục tiêu cuối cùng của bạn.

Nếu bạn đang tìm kiếm vô hiệu hóa tính năng tràn, không thể – không có cài đặt này trong Excel. Trên thực tế, đó là một trong những khả năng mới hữu ích nhất và không có ý nghĩa gì khi từ chối nó hoàn toàn. Sẽ là khôn ngoan hơn nếu học cách sử dụng nó để có lợi cho bạn

Để loại bỏ một phạm vi tràn cụ thể, xóa một công thức trong ô đầu tiên.

Đến ngăn không cho sữa công thức tràn ra ngoài vào nhiều ô, sử dụng toán tử @ để giảm nhiều giá trị thành một giá trị duy nhất. Về mặt Excel, điều này được gọi là giao lộ ngầm.

Ví dụ: công thức mảng động bên dưới nhân mỗi giá trị trong A2: A5 với 10%. Kết quả là một phạm vi tràn chiếm 4 ô.

= A2:A5*10%

Để xử lý chỉ một giá trị (trong cùng hàng với công thức) và trả về kết quả trong một đơn bào, thay đổi công thức như sau, rồi sao chép nó vào nhiều ô nếu cần.

=@A:A*10%

Bây giờ, bạn không còn là một người mới làm quen với phạm vi tràn Excel nữa, phải không? Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên website của ketoanmvb vào tuần tới!

The post Giải thích phạm vi tràn trong Excel appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | Kế Toán MVB.

source https://ketoanmvb.com/giai-thich-pham-vi-tran-trong-excel.html