in ,

Cách tạo Phạm vi xác định động trong Excel

Biểu trưng Excel

Dữ liệu Excel của bạn thay đổi thường xuyên, vì vậy sẽ hữu ích khi tạo một phạm vi được xác định động tự động mở rộng và thu hẹp theo kích thước của phạm vi dữ liệu của bạn. Hãy xem làm thế nào.

Bằng cách sử dụng phạm vi xác định động, bạn sẽ không cần chỉnh sửa thủ công phạm vi của công thức, biểu đồ và PivotTable khi dữ liệu thay đổi. Điều này sẽ xảy ra tự động.

Hai công thức được sử dụng để tạo phạm vi động: OFFSET và INDEX. Bài viết này sẽ tập trung vào việc sử dụng hàm INDEX vì đây là cách tiếp cận hiệu quả hơn. OFFSET là một chức năng không ổn định và có thể làm chậm các bảng tính lớn.

Tạo một phạm vi xác định động trong Excel

Đối với ví dụ đầu tiên của chúng tôi, chúng tôi có danh sách dữ liệu một cột được thấy bên dưới.

Phạm vi dữ liệu để tạo động

Chúng tôi cần điều này linh hoạt để nếu thêm hoặc xóa nhiều quốc gia hơn, phạm vi sẽ tự động cập nhật.

Đối với ví dụ này, chúng tôi muốn tránh ô tiêu đề. Như vậy, chúng tôi muốn phạm vi $A$2:$A$6, nhưng động. Thực hiện việc này bằng cách nhấp vào Công thức > Xác định tên.

Tạo một tên được xác định trong Excel

Nhập “các quốc gia” vào ô “Tên” rồi nhập công thức bên dưới vào ô “Refers to”.

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Nhập phương trình này vào một ô bảng tính rồi sao chép nó vào hộp Tên mới đôi khi nhanh hơn và dễ dàng hơn.

Sử dụng một công thức trong một tên xác định

Cái này hoạt động ra sao?

Phần đầu tiên của công thức chỉ định ô bắt đầu của phạm vi (A2 trong trường hợp của chúng tôi) và sau đó là toán tử phạm vi (:).

=$A$2:

Việc sử dụng toán tử phạm vi buộc hàm INDEX trả về một phạm vi thay vì giá trị của một ô. Hàm INDEX sau đó được sử dụng với hàm COUNTA. COUNTA đếm số ô không trống trong cột A (trong trường hợp của chúng tôi là sáu ô).

CHỈ SỐ($A:$A,COUNTA($A:$A))

Công thức này yêu cầu hàm INDEX trả về phạm vi của ô không trống cuối cùng trong cột A ($A$6).

Kết quả cuối cùng là $A$2:$A$6, và do hàm COUNTA, nó là động, vì nó sẽ tìm hàng cuối cùng. Giờ đây, bạn có thể sử dụng tên được xác định “quốc gia” này bên trong quy tắc Xác thực dữ liệu, công thức, biểu đồ hoặc bất cứ nơi nào chúng tôi cần tham chiếu tên của tất cả các quốc gia.

Tạo phạm vi xác định động hai chiều

Ví dụ đầu tiên chỉ động về chiều cao. Tuy nhiên, với một sửa đổi nhỏ và một hàm COUNTA khác, bạn có thể tạo một phạm vi động theo cả chiều cao và chiều rộng.

Trong ví dụ này, chúng tôi sẽ sử dụng dữ liệu được hiển thị bên dưới.

Dữ liệu cho dải động hai chiều

Lần này, chúng tôi sẽ tạo một phạm vi được xác định động, bao gồm các tiêu đề. Nhấp vào Công thức > Xác định tên.

Tạo một tên được xác định trong Excel

Nhập ‘”bán hàng” vào hộp “Tên” và nhập công thức bên dưới vào hộp “Giới thiệu đến”.

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Công thức phạm vi xác định động hai chiều

Công thức này sử dụng $A$1 làm ô bắt đầu. Sau đó, hàm INDEX sử dụng một phạm vi của toàn bộ trang tính ($1:$1048576) để xem xét và trả lại từ đó.

Một trong các hàm COUNTA được sử dụng để đếm các hàng không trống và một hàm khác được sử dụng cho các cột không trống làm cho nó động theo cả hai hướng. Mặc dù công thức này bắt đầu từ A1, nhưng bạn có thể chỉ định bất kỳ ô bắt đầu nào.

Giờ đây, bạn có thể sử dụng tên đã xác định này (doanh số bán hàng) trong một công thức hoặc dưới dạng chuỗi dữ liệu biểu đồ để biến chúng thành động.

Comments

Loading…