Cách sử dụng các hàm tham khảo trong Excel 2016 - núm vú

Tham chiếu Excel 2016 trên danh sách thả xuống của nút lệnh Tra cứu & Tham chiếu trên các Công thức của Ribbon được thiết kế để giải quyết cụ thể các khía cạnh khác nhau của các tham chiếu ô trong bảng tính. Nhóm các chức năng này bao gồm:

ĐỊA CHESS để trả về một tham chiếu ô như là một mục nhập văn bản trong một ô của trang tính
KHUÔÙC trả lại số diện tích trong danh sách các giá trị ( vùng được xác định như là một loạt các tế bào tiếp giáp hoặc một tế bào đơn trong tham chiếu tế bào)
COLUMN để trả lại số đại diện cho vị trí cột của một tham chiếu ô
COLUMNS để trả lại số cột trong một tài liệu tham khảo
FORMULATEXT để trả về công thức được tham chiếu dưới dạng một chuỗi văn bản > GETPIVOTDATA trả lại dữ liệu được lưu trong một bảng xoay Excel
HYPERLINK để tạo liên kết mở một tài liệu khác được lưu trữ trên máy tính, mạng hoặc Internet (bạn cũng có thể thực hiện lệnh Insert → Hyperlink)
< ! - 2 ->
INDIRECT để trả về một tham chiếu ô được chỉ định bởi một chuỗi văn bản và đưa nội dung trong ô mà nó đề cập đến ô đó
LOOKUP để trả lại một giá trị từ một mảng
OFFSET tới trả lại một tham chiếu đến một dãy ô được xác định bởi số hàng và cột từ một ô hoặc một dãy ô
ROW để trả lại số hàng của một ROWS tham chiếu ô
ROWS để trả lại số hàng trong dải ô hoặc mảng
999 RTD trả về dữ liệu thời gian thực từ một máy chủ đang chạy một chương trình hỗ trợ Tự động hóa COM (Mô hình Đối tượng Đối tượng)
TRANSPOSE trả về một mảng theo chiều dọc như một mảng ngang và ngược lại
Lấy gầy trên các cột và hàng
Các hàm COLUMNS và ROWS trả lại số cột và hàng trong dải hoặc mảng ô cụ thể. Ví dụ: nếu bạn có một dãy ô trong bảng tính có tên product_mix, bạn có thể tìm hiểu xem nó có bao nhiêu cột bằng cách nhập công thức sau:

= COLUMNS (product_mix)

Nếu bạn muốn biết có bao nhiêu hàng trong dãy này sử dụng, sau đó bạn nhập công thức này:

= ROWS (product_mix)

Bạn có thể sử dụng các hàm COLUMNS và ROWS cùng nhau để tính toán tổng số ô trong một dải cụ thể. Ví dụ: nếu bạn muốn biết chính xác số ô được sử dụng trong phạm vi ô product_mix, bạn tạo công thức nhân đơn giản sau bằng cách sử dụng các hàm COLUMNS và ROWS:

= COLUMNS (product_mix) * ROWS (product_mix) > Không nhầm lẫn hàm COLUMNS (plural) với hàm COLUMN (số ít) và hàm ROWS (số nhiều) với hàm ROW (số ít).Hàm COLUMN trả về số cột (như thể Excel đã sử dụng hệ thống tham chiếu R1C1) cho tham chiếu ô bạn chỉ định làm đối số duy nhất của nó. Tương tự, hàm ROW trả về số hàng cho tham chiếu ô mà bạn chỉ định là đối số của nó.

Transposing cell ranges

Chức năng TRANSPOSE cho phép bạn thay đổi hướng của một dải ô (hoặc mảng). Bạn có thể sử dụng chức năng này để chuyển vị trí của dải ô dọc, nơi dữ liệu chạy xuống các hàng của các cột liền kề đến dữ liệu chạy qua các cột của hàng liền kề và ngược lại. Để sử dụng thành công chức năng TRANSPOSE, bạn không chỉ phải chọn một phạm vi có số cột và hàng tương ứng, nhưng bạn cũng phải nhập nó như một công thức mảng.

Ví dụ: nếu bạn đang sử dụng chức năng TRANSPOSE để chuyển đổi dải ô 2 x 5 (nghĩa là một dải ô chiếm hai hàng liền kề và năm cột liền kề), bạn phải chọn dải ô trống 5 x 2 (có nghĩa là, một dãy có 5 hàng liền kề và hai cột liền kề) trong bảng tính trước khi bạn sử dụng nút Chèn Chức năng để chèn chức năng TRANSPOSE vào ô đầu tiên. Sau đó, sau khi chọn phạm vi ô 2 x 5 có chứa dữ liệu bạn muốn chuyển vị trí trong hộp văn bản Array của hộp thoại Đối số chức năng, bạn cần nhấn Ctrl + Shift + Enter để đóng hộp thoại này và nhập chức năng TRANSPOSE vào toàn bộ phạm vi ô được chọn làm công thức mảng (được bao gồm trong dấu ngoặc nhọn).

Giả sử bạn muốn chuyển các dữ liệu nhập vào ô A10: C11 (mảng 2 x 3) đến khoảng ô trống E10: F12 (mảng 3 x 2) của bảng tính. Khi bạn nhấn Ctrl + Shift + Enter để hoàn thành công thức mảng, sau khi chọn ô A10: C11 làm đối số

mảng, Excel sẽ đặt công thức mảng sau trong mỗi ô có dải:

{ = TRANSPOSE (A10: C11)} Hình dưới minh họa việc sử dụng chức năng TRANSPOSE. Dãy ô B2: C4 chứa mảng 3 x 2. Để chuyển đổi mảng 3 x 2 này trong dải ô B2: C4 sang mảng 2 x 3 trong dãy B6: D7, hãy làm theo các bước sau: Chọn dải ô trống B6: D7 trong bảng tính.

Nhấp vào nút Tra cứu & Tham khảo trên tab Công thức Ribbon và sau đó chọn tùy chọn TRANSPOSE từ trình đơn thả xuống của nút.

Excel chèn = TRANSPOSE () trên thanh công thức và mở hộp thoại Đối số hàm, nơi hộp văn bản đối số Array được chọn.

Kéo qua phạm vi ô B2: C4 trong bảng tính để hộp văn bản đối số Array chứa B2: C4 và công thức trên thanh Công thức bây giờ đọc = TRANSPOSE (B2: C4).
Nhấn Ctrl + Shift + Enter để đóng hộp thoại Chèn đối số (không nhấp OK) và để chèn công thức mảng TRANSPOSE vào ô B6: D7 như được hiển thị ở đây.

Sử dụng chức năng TRANSPOSE để thay đổi hướng của một mảng đơn giản.
Nhấn nút OK trong hộp thoại Đối số Chức năng chèn chức năng TRANSPOSE vào ô hiện hành của lựa chọn ô hiện tại.Làm điều này trả về #VALUE! lỗi giá trị cho các tế bào. Bạn phải nhớ nhấn Ctrl + Shift + Enter để đóng hộp thoại và đặt công thức vào toàn bộ ô.
Nếu tất cả những gì bạn muốn làm là dịch chuyển các tiêu đề của hàng và cột hoặc một bảng dữ liệu đơn giản, bạn không cần phải trải qua việc tạo ra một công thức mảng bằng cách sử dụng chức năng TRANSPOSE. Đơn giản chỉ cần sao chép phạm vi của các tế bào sẽ được chuyển đổi với nút lệnh Sao chép trên tab Trang chủ của Ribbon. Vị trí con trỏ ô trong ô trống đầu tiên, nơi sắp xếp dải được di chuyển trước khi bạn nhấp vào tùy chọn Chuyển vị trí trên trình đơn thả xuống của nút lệnh thả.