Nội dung
Bạn có bảng với dữ liệu trong Excel có thể thay đổi kích thước, tức là số hàng (cột) có thể tăng hoặc giảm trong quá trình làm việc? Nếu kích thước bảng "nổi", thì bạn sẽ phải liên tục theo dõi khoảnh khắc này và sửa nó:
- các liên kết trong công thức báo cáo tham chiếu đến bảng của chúng tôi
- phạm vi ban đầu của bảng tổng hợp được xây dựng theo bảng của chúng tôi
- phạm vi ban đầu của biểu đồ được xây dựng theo bảng của chúng tôi
- phạm vi cho danh sách thả xuống sử dụng bảng của chúng tôi làm nguồn dữ liệu
Tất cả những điều này sẽ không khiến bạn cảm thấy nhàm chán 😉
Sẽ thuận tiện và chính xác hơn nhiều khi tạo phạm vi "cao su" động, phạm vi này sẽ tự động điều chỉnh kích thước theo số hàng và cột dữ liệu thực tế. Để thực hiện điều này, có một số cách.
Phương pháp 1. Bảng thông minh
Đánh dấu phạm vi ô của bạn và chọn từ tab Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng bảng):
Nếu bạn không cần thiết kế sọc được thêm vào bảng như một hiệu ứng phụ, thì bạn có thể tắt nó trên tab xuất hiện Constructor (Thiết kế). Mỗi bảng được tạo theo cách này sẽ nhận được một tên có thể được thay thế bằng một tên khác thuận tiện hơn ở cùng một vị trí trên tab Constructor (Thiết kế) trên đồng ruộng Tên bảng (Tên bảng).
Giờ đây, chúng ta có thể sử dụng các liên kết động tới “bảng thông minh” của mình:
- Bảng 1 - liên kết đến toàn bộ bảng ngoại trừ hàng tiêu đề (A2: D5)
- Bảng1 [#All] - liên kết đến toàn bộ bảng (A1: D5)
- Table1 [Peter] - tham chiếu đến cột phạm vi không có tiêu đề ô đầu tiên (C2: C5)
- Table1 [#Headers] - liên kết đến "tiêu đề" với tên của các cột (A1: D1)
Các tham chiếu như vậy hoạt động tốt trong các công thức, ví dụ:
= SUM (Bảng 1 [Moscow]) - tính tổng cho cột "Moscow"
or
= VPR (F5;Bảng 1; 3; 0) - tìm kiếm trong bảng cho tháng từ ô F5 và đưa ra tổng số St.Petersburg cho nó (VLOOKUP là gì?)
Các liên kết như vậy có thể được sử dụng thành công khi tạo bảng tổng hợp bằng cách chọn trên tab Chèn - Bảng tổng hợp (Chèn - Bảng tổng hợp) và nhập tên của bảng thông minh làm nguồn dữ liệu:
Nếu bạn chọn một phân đoạn của bảng như vậy (ví dụ: hai cột đầu tiên) và tạo một sơ đồ thuộc bất kỳ loại nào, thì khi thêm các dòng mới, chúng sẽ tự động được thêm vào sơ đồ.
Khi tạo danh sách thả xuống, không thể sử dụng các liên kết trực tiếp đến các phần tử của bảng thông minh, nhưng bạn có thể dễ dàng khắc phục hạn chế này bằng cách sử dụng một thủ thuật chiến thuật - sử dụng hàm GIÁN TIẾP (GIÁN TIẾP), biến văn bản thành một liên kết:
Những thứ kia. một liên kết đến một bảng thông minh dưới dạng một chuỗi văn bản (trong dấu ngoặc kép!) chuyển thành một liên kết chính thức và danh sách thả xuống bình thường nhận ra nó.
Phương pháp 2: Dải ô được đặt tên động
Nếu việc biến dữ liệu của bạn thành một bảng thông minh là không mong muốn vì lý do nào đó, thì bạn có thể sử dụng một phương pháp phức tạp hơn một chút, nhưng tinh tế và linh hoạt hơn nhiều - tạo một phạm vi được đặt tên động trong Excel tham chiếu đến bảng của chúng tôi. Sau đó, như trong trường hợp của bảng thông minh, bạn có thể thoải mái sử dụng tên của phạm vi đã tạo trong bất kỳ công thức, báo cáo, biểu đồ nào, v.v. Hãy bắt đầu với một ví dụ đơn giản:
Nhiệm vụ: tạo một phạm vi được đặt tên động sẽ tham chiếu đến danh sách các thành phố và tự động kéo dài và thu nhỏ kích thước khi thêm các thành phố mới hoặc xóa chúng.
Chúng tôi sẽ cần hai hàm Excel tích hợp sẵn trong bất kỳ phiên bản nào - POICPOZ (CUỘC THI ĐẤU) để xác định ô cuối cùng của phạm vi và INDEX (MỤC LỤC) để tạo một liên kết động.
Tìm ô cuối cùng bằng MATCH
MATCH (lookup_value, range, match_type) - một hàm tìm kiếm một giá trị nhất định trong một phạm vi (hàng hoặc cột) và trả về số thứ tự của ô nơi nó được tìm thấy. Ví dụ, công thức MATCH (“March”; A1: A5; 0) sẽ trả về kết quả là số 4, vì từ “March” nằm ở ô thứ tư trong cột A1: A5. Đối số hàm cuối cùng Match_Type = 0 có nghĩa là chúng tôi đang tìm kiếm một đối sánh chính xác. Nếu đối số này không được chỉ định, thì hàm sẽ chuyển sang chế độ tìm kiếm giá trị nhỏ nhất gần nhất - đây chính xác là những gì có thể được sử dụng thành công để tìm ô bị chiếm cuối cùng trong mảng của chúng ta.
Bản chất của thủ thuật là đơn giản. MATCH tìm kiếm các ô trong phạm vi từ trên xuống dưới và theo lý thuyết, sẽ dừng lại khi nó tìm thấy giá trị nhỏ nhất gần nhất với giá trị đã cho. Nếu bạn chỉ định một giá trị rõ ràng lớn hơn bất kỳ giá trị nào có sẵn trong bảng làm giá trị mong muốn, thì MATCH sẽ đến cuối bảng, không tìm thấy gì và cung cấp số thứ tự của ô được điền cuối cùng. Và chúng tôi cần nó!
Nếu chỉ có các số trong mảng của chúng tôi, thì chúng tôi có thể chỉ định một số làm giá trị mong muốn, rõ ràng là lớn hơn bất kỳ số nào trong bảng:
Để đảm bảo, bạn có thể sử dụng số 9E + 307 (9 nhân 10 thành lũy thừa của 307, tức là 9 với 307 số không) - số tối đa mà Excel có thể làm việc về nguyên tắc.
Nếu có các giá trị văn bản trong cột của chúng ta, thì tương đương với số lớn nhất có thể, bạn có thể chèn cấu trúc REPEAT (“i”, 255) - một chuỗi văn bản bao gồm 255 chữ cái “i” - chữ cái cuối cùng của bảng chữ cái. Vì Excel thực sự so sánh các mã ký tự khi tìm kiếm, về mặt kỹ thuật, bất kỳ văn bản nào trong bảng của chúng ta sẽ “nhỏ hơn” so với dòng “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy:
Tạo liên kết bằng INDEX
Bây giờ chúng ta đã biết vị trí của phần tử không trống cuối cùng trong bảng, nó vẫn tạo liên kết với toàn bộ phạm vi của chúng ta. Đối với điều này, chúng tôi sử dụng chức năng:
INDEX (range; row_num; column_num)
Nó cung cấp nội dung của ô từ phạm vi theo hàng và số cột, ví dụ: hàm = INDEX (A1: D5; 3; 4) trong bảng của chúng tôi với các thành phố và tháng từ phương pháp trước đó sẽ cho 1240 - nội dung từ hàng thứ 3 và cột thứ 4, tức là các ô D3. Nếu chỉ có một cột, thì số của nó có thể bị bỏ qua, tức là công thức INDEX (A2: A6; 3) sẽ cho "Samara" trong ảnh chụp màn hình cuối cùng.
Và có một sắc thái không hoàn toàn rõ ràng: nếu INDEX không chỉ được nhập vào ô sau dấu =, như thường lệ, mà được sử dụng làm phần cuối cùng của tham chiếu đến phạm vi sau dấu hai chấm, thì nó không còn đưa ra nội dung của ô, nhưng địa chỉ của nó! Do đó, một công thức như $ A $ 2: INDEX ($ A $ 2: $ A $ 100; 3) sẽ cung cấp một tham chiếu đến phạm vi A2: A4 ở đầu ra.
Và đây là nơi hàm MATCH xuất hiện, chúng tôi chèn vào bên trong INDEX để xác định động phần cuối của danh sách:
= $ A $ 2: INDEX ($ A $ 2: $ A $ 100; MATCH (REP (“I”; 255); A2: A100))
Tạo một dải ô đã đặt tên
Nó vẫn là để đóng gói tất cả thành một tổng thể duy nhất. Mở một tab công thức (Công thức) Và bấm vào Người quản lý tên (Người quản lý tên). Trong cửa sổ mở ra, hãy nhấp vào nút Tạo (Mới), nhập tên phạm vi và công thức của chúng tôi vào trường Phạm vi (Tài liệu tham khảo):
Nó vẫn còn để nhấp vào OK và phạm vi sẵn sàng có thể được sử dụng trong bất kỳ công thức, danh sách thả xuống hoặc biểu đồ nào.
- Sử dụng hàm VLOOKUP để liên kết các bảng và tra cứu giá trị
- Cách tạo danh sách thả xuống tự động điền
- Cách tạo bảng tổng hợp để phân tích một lượng lớn dữ liệu