Công thức excel với tên cột

Khi bạn tạo công thức, có thể bao gồm các giá trị từ các ô hoặc cột khác trên trang tính. Bạn làm điều này với tham chiếu ô hoặc cột. Bạn có thể tạo tham chiếu đến một ô riêng lẻ, một dải ô hoặc toàn bộ cột

Cũng có thể tham khảo dữ liệu từ các trang tính khác. Để biết thông tin về tham chiếu dữ liệu từ các trang tính khác, hãy xem Công thức. Dữ liệu tham khảo từ các trang tính khác

Tóm tắt các loại tham chiếu công thức

Đây là một mánh gian lận mà bạn có thể sử dụng khi xây dựng các công thức của riêng mình

Để tham chiếu điều nàyĐịnh dạng theo cách nàyVí dụÔ cá nhânTên cột, số hàng=Ngân sách1Tên cột chứa khoảng trắng hoặc kết thúc bằng một sốĐóng tên cột trong ngoặc đơn. =[Cột A]1
=DAY([Q1]1)Tham chiếu tuyệt đối (luôn tham chiếu đến ô, hàng hoặc cột cụ thể đó) Nhập ký hiệu $ trước tên cột, số hàng hoặc cả hai. =$[Cột A]$1
=[Cột B]$1
=$[Cột C]1Nhiều ô không liên tụcSử dụng dấu phẩy giữa các tham chiếu ô. =SUM(Ngân sách1, Chi phí4, [Thu nhập dự kiến]20)Một dải ô trong cùng một cộtTham chiếu ô đầu tiên trong dải ô, sau đó là ô cuối cùng, được phân tách bằng dấu. (Đại tràng). =SUM(Ngân sách1. Ngân sách12)Toàn bộ cột (bao gồm mọi ô mới được thêm)Tên cột được phân tách bằng dấu. (Đại tràng). =SUM(Ngân sách. Ngân sách) Một dải ô trên nhiều cột Tham chiếu ô trên cùng bên phải, sau đó đến ô dưới cùng bên trái, được phân tách bằng dấu. (Đại tràng). =SUM(1 tháng 1. March5)Một ô riêng lẻ, phạm vi ô hoặc toàn bộ cột từ một trang tính khác Nhập tên của tham chiếu được tạo trước đó cho một trang tính khác, được đặt trong dấu ngoặc nhọn. =COUNT({my_sheet1 Range1})

Mô tả chi tiết hơn về các loại tham chiếu

Tham chiếu các ô riêng lẻ

Trong khi xây dựng công thức của mình, bạn có thể chọn một ô để tham chiếu ô đó và làm việc với dữ liệu của ô đó trong công thức của mình. (Bạn cũng có thể nhập tên cột và số hàng theo cách thủ công để tham chiếu ô. )

Ví dụ: công thức trong cột Tổng kho của bảng quản lý hàng tồn kho sau đây sẽ tính tổng các giá trị từ các cột Kho A, Kho B và Kho C trên hàng 1

Phương pháp mặc định để bao gồm tham chiếu cột trong công thức Excel là sử dụng ký tự cột, một quy ước có thể gây khó khăn cho việc diễn giải các phần của công thức phức tạp. Microsoft đã thiết kế Excel với một phương pháp đặt tên cho các phạm vi ô và cột để đơn giản hóa việc viết và diễn giải các công thức. Bạn có thể áp dụng tên cột cho một trang tính hoặc tăng phạm vi và áp dụng nó cho toàn bộ sổ làm việc

Tờ đơn

1. 1

   Nhấp vào chữ cái của cột bạn muốn đổi tên để tô sáng toàn bộ cột

2. 2

   Nhấp vào hộp "Tên" nằm ở bên trái của thanh công thức và nhấn "Xóa" để xóa tên hiện tại

3. 3

   Nhập tên mới cho cột và nhấn "Enter. "

Sách bài tập

1. 1

   Nhấp vào chữ cái của cột bạn muốn thay đổi rồi nhấp vào tab "Công thức"

2. 2

   Nhấp vào "Xác định tên" trong nhóm Tên được xác định trong Dải băng để mở cửa sổ Tên mới

3. 3

   Nhập tên mới của cột trong hộp văn bản Tên

4. 4

   Nhấp vào menu thả xuống "Phạm vi" và chọn "Sổ làm việc" để áp dụng thay đổi cho tất cả các trang tính trong sổ làm việc. Nhấp vào "OK" để lưu các thay đổi của bạn

   Bạn có thể sử dụng các hàm ROW và COLUMN để thực hiện việc này. Nếu bạn bỏ qua đối số cho các công thức đó, thì ô hiện tại sẽ được sử dụng. Chúng có thể được sử dụng trực tiếp với hàm OFFSET hoặc bất kỳ hàm nào khác mà bạn có thể chỉ định cả hàng và cột dưới dạng giá trị số

   Ví dụ: nếu bạn nhập =ROW() vào ô D8, giá trị được trả về là 8. Nếu bạn nhập =COLUMN() vào cùng một ô, giá trị được trả về là 4

   Nếu bạn muốn ký tự cột, bạn có thể sử dụng hàm CHAR. Tôi không khuyên bạn nên sử dụng các chữ cái để đại diện cho cột, vì mọi thứ trở nên khó khăn khi chuyển sang các tên cột có hai chữ cái (dù sao thì việc chỉ sử dụng các số sẽ hợp lý hơn)

   Bất kể, nếu bạn vẫn muốn lấy chữ cái của cột, bạn chỉ cần thêm 64 vào số cột (64 nhỏ hơn một ký tự so với A), vì vậy, trong ví dụ trước, nếu bạn đặt giá trị của ô thành =CHAR(COLUMN()+64), thì giá trị được trả về . Nếu bạn muốn giá trị của một ô là chính vị trí của ô, thì công thức hoàn chỉnh sẽ là ROW0

   ---

   Chỉ là một thông tin cá nhân, tôi nhận được 64 từ bảng ASCII. Bạn cũng có thể sử dụng công thức ROW1, vì vậy công thức cập nhật sử dụng công thức này sẽ là ROW2. Bạn phải trừ đi 1 vì giá trị tối thiểu của COLUMN luôn là 1, sau đó giá trị trả về tối thiểu của toàn bộ công thức sẽ là ROW4

   Tuy nhiên, điều này sẽ không hoạt động với các cột có hai chữ cái. Trong trường hợp đó, bạn cần có công thức sau để phân tích đúng các cột có hai chữ cái

   =IF(COLUMN()>26,IF(RIGHT(CHAR(IF(MOD(COLUMN()-1,26)=0,1,MOD(COLUMN()-1,26))+64),1)="Y",CHAR(INT((COLUMN()-1)/26)+64) & "Z",CHAR(INT((COLUMN()-1)/26)+64) & CHAR(IF(MOD(COLUMN(),26)=0,1,MOD(COLUMN(),26))+64)),CHAR(COLUMN()+64))&ROW()
   

   Tôi không chắc liệu có cách nào dễ dàng hơn để làm điều đó hay không, nhưng tôi biết cách đó hoạt động từ ô 15 đến 16 mà không gặp vấn đề gì. Tuy nhiên, điều này minh họa lý do tại sao tốt nhất là tránh sử dụng các mã định danh cột dựa trên chữ cái và sử dụng các công thức thuần túy dựa trên số (e. g. sử dụng số cột thay vì chữ cái với OFFSET)