Để truy xuất giá trị cuối cùng trong danh sách động, chúng tôi sẽ sử dụng tùy chọn Xác thực dữ liệu cùng với các hàm OFFSET và COUNTA trong Microsoft Excel 2010/2013.

COUNTA: Nó trả về số lượng ô chứa giá trị.

Cú pháp của hàm “COUNTA”: = COUNTA (value1, value2, value3 …….)

Ví dụ: _ _Trong phạm vi A1: A5, các ô A2, A3 và A5 chứa các giá trị và các ô A1 và A4 để trống. Chọn ô A6 và viết công thức- _ = COUNTA (A1: A5) _ _ hàm sẽ trả về 3_

img1

OFFSET: Nó trả về một tham chiếu đến một phạm vi được bù đắp một số hàng và cột từ một phạm vi hoặc ô khác.

Cú pháp của hàm OFFSET: = OFFSET (tham chiếu, hàng, cột, chiều cao, chiều rộng) _ Tham khảo: -_ Đây là ô hoặc dải ô mà bạn muốn bù trừ.

Các hàng và cột để di chuyển: _ _- Bạn muốn di chuyển bao nhiêu hàng để di chuyển điểm bắt đầu và cả hai hàng này có thể là số dương, số âm hoặc số không.

_Height and Width _ * : – Đây là kích thước của phạm vi bạn muốn trả về. Đây là trường tùy chọn . Hãy lấy một ví dụ để hiểu về hàm Offset trong Excel.

Chúng tôi có dữ liệu trong phạm vi A1: D10. Cột A chứa Mã sản phẩm, Cột B chứa Số lượng, cột C chứa giá thành mỗi sản phẩm và cột D chứa Tổng giá thành. Chúng ta cần trả về giá trị của ô C5 trong ô E2.

img2

Làm theo các bước được đề cập bên dưới.

  • Chọn ô E2 và viết công thức.

OFFSET (A1,4,2,1,1) * và nhấn Enter trên bàn phím.

  • Hàm sẽ trả về giá trị của ô C5 .

img3

img4

Trong ví dụ này, chúng ta cần lấy giá trị từ ô C5 vào E2. Ô tham chiếu của chúng tôi là ô đầu tiên trong phạm vi A1 và C5 là 4 hàng bên dưới và 2 cột bên phải từ A1. Do đó, công thức là = OFFSET (A1,4,2,1,1) hoặc = OFFSET (A1,4,2) (vì 1,1 là tùy chọn).

Bây giờ, hãy lấy một ví dụ để truy xuất giá trị cuối cùng trong danh sách động.

Chúng tôi có tên quốc gia trong một phạm vi. Bây giờ, nếu chúng tôi thêm nhiều danh sách đếm số hơn, danh sách này sẽ tự động có sẵn trong danh sách thả xuống.

img5

Để chuẩn bị một danh sách động, chúng ta cần tạo một công thức sẽ lấy giá trị cuối cùng trong cột và tự động cập nhật khi một số mới được thêm vào.

Thực hiện theo các bước dưới đây: – * Chọn ô B2.

  • Chuyển đến tab Dữ liệu, chọn Xác thực Dữ liệu từ nhóm Công cụ dữ liệu.

img6

  • Hộp thoại “Xác thực Dữ liệu” sẽ xuất hiện. Trong tab “Cài đặt”, chọn “Tùy chỉnh” từ danh sách Cho phép thả xuống.

img7

  • Hộp công thức sẽ được kích hoạt.

  • Viết công thức vào ô này.

* _ = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1) ._

  • Nhấp vào OK.

__

img8

  • Ở giai đoạn này, ô cập nhật cuối cùng là A11.

img9

  • Để kiểm tra xem Xác thực Dữ liệu có hoạt động bình thường hay không, hãy thêm tên thành phố vào ô A12.

img10

Ngay sau khi bạn thêm một mục nhập trong A12, nó sẽ được thêm vào danh sách thả xuống.

Đây là cách bạn có thể tạo một danh sách động và tự động điền các mục nhập mới vào đó trong Microsoft Excel 2010 và 2013.

__