Hướng dẫn này đề cập đến các chức năng quan trọng cho phép bạn thực hiện mọi thứ, từ tô bóng hàng và cột thay thế cho đến tăng giá trị theo các khoảng thời gian đã chỉ định, v.v.
Chúng tôi sẽ sử dụng hàm ROW và COLUMN cho việc này. Dưới đây là một ví dụ về đầu ra từ các chức năng này
Mặc dù điều này trông không giống lắm, nhưng các hàm này cho phép tạo các công thức mạnh mẽ khi kết hợp với các hàm khác. Bây giờ, hãy xem cách tạo chúng
Lấy số hàng của một ô
cú pháp
Hàm này trả về số hàng mà một ô cụ thể nằm trong
Nếu bạn để trống hàm này, nó sẽ trả về số hàng cho ô hiện tại mà hàm này đã được đặt
Nếu bạn đặt một tham chiếu ô trong hàm này, nó sẽ trả về số hàng cho tham chiếu ô đó
Ví dụ này sẽ trả về 1 vì ô A1 ở hàng 1. Nếu là =ROW[C24] thì hàm sẽ trả về số 24 vì ô C24 ở hàng 24
ví dụ
Bây giờ bạn đã biết chức năng này hoạt động như thế nào, nó có vẻ khá vô dụng. Đây là các liên kết đến hai ví dụ trong đó chức năng này là chìa khóa
Tăng giá trị mỗi X số hàng trong Excel
Tô màu mọi hàng khác trong Excel một cách nhanh chóng
Nhận số cột của một ô
cú pháp
Hàm này trả về số cột chứa một ô cụ thể. Nó đếm từ trái sang phải, trong đó A là 1 và B là 2, v.v.
Nếu bạn để trống hàm này sẽ trả về số cột cho ô hiện tại mà hàm này đã được đặt
Nếu bạn đặt một tham chiếu ô trong hàm này, nó sẽ trả về số cột cho tham chiếu ô đó
Ví dụ này sẽ trả về 1 vì ô A1 ở hàng 1. Nếu là =COLUMN[C24] thì hàm sẽ trả về số 3 vì ô C24 nằm ở cột số 3
ví dụ
Hàm COLUMN hoạt động giống như hàm ROW ngoại trừ việc nó hoạt động trên các cột, đi từ trái sang phải, trong khi hàm ROW hoạt động trên các hàng, đi lên và xuống
Như vậy, hầu hết mọi ví dụ sử dụng ROW đều có thể được chuyển đổi thành sử dụng COLUMN dựa trên nhu cầu của bạn
ghi chú
Các hàm ROW và COLUMN là các khối xây dựng giúp bạn tạo các công thức phức tạp hơn trong Excel. Một mình, các chức năng này gần như vô dụng, nhưng nếu bạn có thể ghi nhớ chúng và giữ chúng để sử dụng sau này, bạn sẽ bắt đầu tìm thấy ngày càng nhiều cách sử dụng chúng khi làm việc trong các tập dữ liệu lớn. Các ví dụ được cung cấp ở trên trong phần ROW chỉ đề cập đến hai trong số nhiều cách khác nhau mà bạn có thể sử dụng các hàm này để tạo các bảng tính mạnh mẽ và hữu ích hơn
Số ngày sẽ nằm trong một ô tham chiếu tuyệt đối mà tôi có thể thay đổi. Có thể 5 ngày, có thể 10 ngày, v.v.
Tôi có thể viết công thức để tham chiếu ô cuối cùng trong cột A [cột ngày luôn có dữ liệu]
=ADDRESS[MATCH[9. 99999999999999E+307,A. A],1,3]
Điều này trả về Ví dụ $A5627
Bây giờ tôi cần bù kết quả đó vào Cột E nhưng cũng cần bù lại số lượng hàng được xác định trong những ngày đã chọn. Ví dụ: nếu số ngày tôi muốn là 10, tôi sẽ cần kết quả là $E5617
Công thức đó sau đó sẽ được lồng trong các tính toán của tôi. Thí dụ. =Tổng[E5. Công thức địa chỉ bù đắp bạn giúp tôi viết]
Tóm tắt. Tìm hàng cuối cùng bằng cách sử dụng cột ngày A luôn có dữ liệu, sau đó chuyển sang Cột E sao lưu các hàng được xác định trong ô tham chiếu số ngày của tôi. Công thức đó sẽ trả về một tham chiếu mà sau đó sẽ được sử dụng trong phép tính. Thí dụ. =SUM[E5. các bạn tham khảo công thức giúp mình viết]
Trả về giá trị của một phần tử trong một bảng hoặc một mảng, được chọn bởi chỉ số hàng và cột
Sử dụng dạng mảng nếu đối số đầu tiên của INDEX là hằng số mảng
cú pháp
INDEX[mảng, số_hàng, [số_cột]]
Dạng mảng của hàm INDEX có các đối số sau
mảng Bắt buộc. Một dải ô hoặc một hằng số mảng
Nếu mảng chỉ chứa một hàng hoặc cột, đối số row_num hoặc column_num tương ứng là tùy chọn
Nếu mảng có nhiều hàng và nhiều cột và chỉ row_num hoặc column_num được sử dụng, INDEX trả về một mảng gồm toàn bộ hàng hoặc cột trong mảng
row_num Bắt buộc, trừ khi có column_num. Chọn hàng trong mảng để trả về giá trị. Nếu row_num bị bỏ qua, column_num là bắt buộc
column_num Tùy chọn. Chọn cột trong mảng để trả về giá trị. Nếu column_num bị bỏ qua, row_num là bắt buộc
Nhận xét
Nếu cả hai đối số row_num và column_num đều được sử dụng, INDEX sẽ trả về giá trị trong ô tại giao điểm của row_num và column_num
row_num và column_num phải trỏ đến một ô trong mảng; . lỗi
Nếu bạn đặt row_num hoặc column_num thành 0 [không], INDEX sẽ trả về mảng giá trị cho toàn bộ cột hoặc hàng tương ứng. Để sử dụng các giá trị được trả về dưới dạng một mảng, hãy nhập hàm INDEX dưới dạng công thức mảng
Ghi chú. Nếu bạn có phiên bản Microsoft 365 hiện tại, thì bạn có thể nhập công thức vào ô trên cùng bên trái của phạm vi đầu ra, rồi nhấn ENTER để xác nhận công thức là công thức mảng động. Nếu không, công thức phải được nhập dưới dạng công thức mảng kế thừa bằng cách trước tiên chọn phạm vi đầu ra, nhập công thức vào ô trên cùng bên trái của phạm vi đầu ra, sau đó nhấn CTRL+SHIFT+ENTER để xác nhận. Excel chèn dấu ngoặc nhọn vào đầu và cuối công thức cho bạn. Để biết thêm thông tin về công thức mảng, hãy xem Nguyên tắc và ví dụ về công thức mảng
ví dụ
ví dụ 1
Những ví dụ này sử dụng hàm INDEX để tìm giá trị trong ô giao nhau nơi một hàng và một cột gặp nhau
Sao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter
Dữ liệu
Dữ liệu
Táo
Chanh
chuối
Lê
Công thức
Sự mô tả
Kết quả
=INDEX[A2. B3,2,2]
Giá trị tại giao điểm của hàng thứ hai và cột thứ hai trong phạm vi A2. B3
Lê
=INDEX[A2. B3,2,1]
Giá trị tại giao điểm của hàng thứ hai và cột đầu tiên trong phạm vi A2. B3
chuối
ví dụ 2
Ví dụ này sử dụng hàm INDEX trong công thức mảng để tìm các giá trị trong hai ô được chỉ định trong mảng 2x2.
Ghi chú. Nếu bạn có phiên bản Microsoft 365 hiện tại, thì bạn có thể nhập công thức vào ô trên cùng bên trái của phạm vi đầu ra, rồi nhấn ENTER để xác nhận công thức là công thức mảng động. Nếu không, công thức phải được nhập dưới dạng công thức mảng kế thừa bằng cách trước tiên chọn hai ô trống, nhập công thức vào ô trên cùng bên trái của phạm vi đầu ra, sau đó nhấn CTRL+SHIFT+ENTER để xác nhận. Excel chèn dấu ngoặc nhọn vào đầu và cuối công thức cho bạn. Để biết thêm thông tin về công thức mảng, hãy xem Nguyên tắc và ví dụ về công thức mảng
Công thức
Sự mô tả
Kết quả
=INDEX[{1,2;3,4},0,2]
Tìm thấy giá trị ở hàng đầu tiên, cột thứ hai trong mảng. Mảng chứa 1 và 2 ở hàng đầu tiên và 3 và 4 ở hàng thứ hai
2
Giá trị được tìm thấy ở hàng thứ hai, cột thứ hai trong mảng [giống mảng trên]
4
Đầu trang
mẫu tham khảo
Sự mô tả
Trả về tham chiếu của ô tại giao điểm của một hàng và cột cụ thể. Nếu tham chiếu được tạo thành từ các lựa chọn không liền kề, bạn có thể chọn lựa chọn để xem xét
cú pháp
INDEX[tham chiếu, row_num, [column_num], [area_num]]
Dạng tham chiếu của hàm INDEX có các đối số sau
tham chiếu Bắt buộc. Tham chiếu đến một hoặc nhiều phạm vi ô
Nếu bạn đang nhập một phạm vi không liền kề cho tham chiếu, hãy đặt tham chiếu trong ngoặc đơn
Nếu mỗi vùng trong tham chiếu chỉ chứa một hàng hoặc cột, đối số row_num hoặc column_num tương ứng là tùy chọn. Ví dụ: đối với tham chiếu một hàng, hãy sử dụng INDEX[reference,,column_num]
row_num Bắt buộc. Số hàng trong tham chiếu mà từ đó trả về một tham chiếu
column_num Tùy chọn. Số cột trong tham chiếu mà từ đó trả về tham chiếu
area_num Tùy chọn. Chọn một phạm vi trong tham chiếu để trả về giao điểm của row_num và column_num. Vùng đầu tiên được chọn hoặc nhập được đánh số 1, vùng thứ hai là 2, v.v. Nếu area_num bị bỏ qua, INDEX sử dụng khu vực 1. Tất cả các khu vực được liệt kê ở đây phải nằm trên một trang tính. Nếu bạn chỉ định các khu vực không nằm trên cùng một trang tính với nhau, điều này sẽ gây ra lỗi #VALUE. lỗi. Nếu cần sử dụng các phạm vi nằm trên các trang tính khác nhau, thì bạn nên sử dụng dạng mảng của hàm INDEX và sử dụng một hàm khác để tính toán phạm vi tạo thành mảng. Ví dụ: bạn có thể sử dụng hàm CHOOSE để tính toán phạm vi nào sẽ được sử dụng
Ví dụ: nếu Tham chiếu mô tả các ô [A1. B4,D1. E4,G1. H4], area_num 1 là phạm vi A1. B4, area_num 2 là phạm vi D1. E4 và area_num 3 là phạm vi G1. H4
Nhận xét
Sau khi tham chiếu và area_num đã chọn một phạm vi cụ thể, row_num và column_num chọn một ô cụ thể. row_num 1 là hàng đầu tiên trong phạm vi, column_num 1 là cột đầu tiên, v.v. Tham chiếu được INDEX trả về là giao điểm của row_num và column_num
Nếu bạn đặt row_num hoặc column_num thành 0 [không], INDEX sẽ trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng
row_num, column_num và area_num phải trỏ đến một ô trong tham chiếu; . lỗi. Nếu row_num và column_num bị bỏ qua, INDEX trả về khu vực trong tham chiếu được chỉ định bởi area_num
Kết quả của hàm INDEX là một tham chiếu và được diễn giải như vậy bởi các công thức khác. Tùy thuộc vào công thức, giá trị trả về của INDEX có thể được sử dụng làm tham chiếu hoặc làm giá trị. Ví dụ: công thức CELL["width",INDEX[A1. B2,1,2]] tương đương với CELL["width",B1]. Hàm CELL sử dụng giá trị trả về của INDEX làm tham chiếu ô. Mặt khác, một công thức chẳng hạn như 2*INDEX[A1. B2,1,2] dịch giá trị trả về của INDEX thành số trong ô B1
ví dụ
Sao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter
Trái cây
Giá bán
Đếm
Táo
$0. 69
40
chuối
$0. 34
38
Chanh
$0. 55
15
Những quả cam
$0. 25
25
Lê
$0. 59
40
quả hạnh
$2. 80
10
hạt điều
$3. 55
16
Đậu phộng
$1. 25
20
Quả óc chó
$1. 75
12
Công thức
Sự mô tả
Kết quả
=INDEX[A2. C6, 2, 3]
Giao điểm của hàng thứ hai và cột thứ ba trong phạm vi A2. C6, là nội dung của ô C3
38
=INDEX[[A1. C6, A8. C11], 2, 2, 2]
Giao điểm của hàng thứ hai và cột thứ hai trong khu vực thứ hai của A8. C11, là nội dung của ô B9
1. 25
=SUM[INDEX[A1. C11, 0, 3, 1]]
Tổng của cột thứ ba trong khu vực đầu tiên của phạm vi A1. C11, là tổng của C1. C11
216
=SUM[B2. CHỈ SỐ[A2. C6, 5, 2]]
Tổng của phạm vi bắt đầu từ B2 và kết thúc tại giao điểm của hàng thứ năm và cột thứ hai của phạm vi A2. A6, là tổng của B2. B6