Cách cố định công thức trong excel mới nhất

cách cố định công thức trong excel đang được nhiều người tìm kiếm. Cùng 123DocX tìm hiểu ngay thôi

Cách đóng băng vùng dữ liệu trong Excel – Tham khảo tương đối và tuyệt đối

28 tháng 1 năm 2022 Office Tricks 3.9 / 5 – (7 nhận xét)

Khi sử dụng các hàm trong Excel, bạn sẽ rất thường xuyên cần tham chiếu đến một vùng dữ liệu cụ thể, chẳng hạn như A1: D5. Theo mặc định, khi bạn tham chiếu đến một lựa chọn như vậy, nó sẽ ở dạng một liên kết tương đối. Vì vậy, để   sửa một vùng dữ liệu trong Excel   , còn được gọi là tham chiếu tuyệt đối đến một vùng dữ liệu cụ thể, tôi phải làm gì?

Trong bài viết này, tôi sẽ chỉ cho bạn cách hiểu   tham chiếu tương đối và tuyệt đối   , đồng thời giải thích cho bạn ý nghĩa của   ký hiệu $ trong Excel   để bạn biết khi nào cần cố định hàng và cột. hoặc sửa chữa tất cả các vùng dữ liệu.

noah dung

Dấu $ có nghĩa là gì trong excel

Biểu tượng $ là một trong những ký hiệu được sử dụng phổ biến nhất khi sử dụng công thức Excel. Tác dụng của nó là cố định hàng, cột hoặc dải dữ liệu trong Excel.

Trước khi tìm hiểu về liên kết, chúng ta cần hiểu cách xác định ô và phạm vi dữ liệu trong Excel như sau:

Xác định một ô trong Excel

Các ô trong Excel sẽ được hiển thị dưới dạng:   tên cột + số hàng.

Ví dụ

  • ô A5 là ô nằm trong cột A và hàng 5
  • ô H10 là ô nằm trong cột H và hàng 10

Xác định vùng dữ liệu trong Excel

Vùng dữ liệu trong Excel sẽ có dạng: Ô   trên cùng bên trái: Ô dưới cùng bên phải.

Ví dụ

Phạm vi dữ liệu A5: H10 là lựa chọn được xác định bởi ô trên cùng bên trái A5 và ô dưới cùng bên phải là H10.

=> Như vậy bạn đã hiểu cấu trúc ô giống như vùng dữ liệu, thì phần dưới đây khi tìm hiểu về hàng, cột và vùng dữ liệu cố định bạn sẽ hiểu dễ dàng.

Tìm hiểu thêm

  • Hướng dẫn chi tiết về cách xen kẽ màu giữa các hàng và cột trong Excel
  • Cách sử dụng hàm Vlookup trong Excel với một ví dụ
  • Cách sử dụng hàm SUMIFS trong Excel để tính tổng nhiều điều kiện

Liên kết tương đối

Liên kết tương đối là liên kết mặc định khi bạn chọn một dải dữ liệu trong Excel. Với tham chiếu tương đối, giá trị tham chiếu sẽ thay đổi dọc theo hàng hoặc cột, nghĩa là khi vị trí trên hàng hoặc cột thay đổi, giá trị tham chiếu cũng sẽ thay đổi.

Ví dụ

– Tại ô   D4 ,   nhập công thức   = C4   và nhấn   Enter.

– Lúc này giá trị hiển thị trong ô   D4   sẽ là giá trị của ô tham chiếu   C4.

– Khi   bạn kéo để sao chép công thức   từ ô   D4   sang các ô ở hàng dưới cùng hoặc cột bên, tham chiếu ô sẽ thay đổi.

Cách cố định công thức trong excel mới nhất

Liên kết tuyệt đối

Tham chiếu tuyệt đối là tham chiếu nắm bắt giá trị của tham chiếu theo hàng, cột hoặc hàng và cột. Có nghĩa là, khi bạn đã cố định một giá trị tham chiếu trong hàng, cột hoặc cả hàng và cột, khi bạn thay đổi vị trí, nó sẽ vẫn giữ nguyên giá trị tham chiếu đó.

Xem thêm:  Cách tắt dòng gạch đỏ trong word 2010 mới nhất

Có ba loại liên kết tuyệt đối:

  • =A$1:  Absolute row reference (row fixed)  – When copying a formula to another cell, only the column value changes and the row value is fixed .
  • =$A1: Tham chiếu tuyệt đối theo cột (cố định cột) – Khi copy công thức sang 1 ô khác thì chỉ có giá trị hàng thay đổi còn giá trị cột được cố định.
  • =$A$1: Tham chiếu tuyệt đối theo hàng và cột (cố định cả hàng và cột) – Khi copy công thức sang 1 ô khác thì giá trị hàng và cột đều không thay đổi.

Tham chiếu tuyệt đối theo hàng (cố định hàng)

Tham chiếu tuyệt đối theo hàng là nó sẽ cố định giá trị hàng không thay đổi. Khi đặt tham chiếu tuyệt đối theo hàng thì dấu $ sẽ đứng trước Số hàng như sau: =C$4

Khi đó, nếu ta copy công thức sang 1 ô khác thì giá trị hàng không thay đổi tức là nó đã bị cố định theo hàng đã chọn ban đầu.

Ví dụ

– Tại ô D4 ta nhập vào công thức =C$4 rồi ấn Enter

– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4

– Khi đó, ta copy công thức từ ô D4 sang các ô khác mặc dù khác hàng nhưng giá trị hàng vẫn cố định không thay đổi.

Cách cố định công thức trong excel mới nhất

Tham chiếu tuyệt đối theo cột (cố định cột)

Tham chiếu tuyệt đối theo cột là nó sẽ cố định giá trị cột. Khi đặt tham chiếu tuyệt đối theo cột thì dấu $ sẽ đứng trước Tên cột như sau: =$C4

Khi đó, nếu ta copy công thức sang 1 ô khác thì giá trị cột vẫn không thay đổi tức là nó đã bị cố định theo cột đã chọn ban đầu.

Ví dụ

– Tại ô D4 ta nhập vào công thức =$C4 rồi ấn Enter

– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4

– Khi đó, ta copy công thức từ ô D4 sang các ô khác mặc dù khác cột nhưng giá trị cột khi đó vẫn cố định không thay đổi.

Cách cố định công thức trong excel mới nhất

Tham chiếu tuyệt đối theo hàng và cột (cố định cả hàng và cột)

Tham chiếu tuyệt đối theo hàng và cột là tham chiếu sẽ cố định giá trị hàng và cột không thay đổi. Khi đặt tham chiếu tuyệt đối theo hàng và cột thì dấu $ sẽ đứng trước Tên cột và Số hàng như sau: =$C$4

Khi đó, nếu ta copy công thức sang 1 ô khác thì cho dù nó có Số hàng và Tên cột khác nó vẫn sẽ cố định hàng cột không thay đổi.

Ví dụ

– Tại ô D4 ta nhập vào công thức =$C$4 rồi ấn Enter

– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4

– Khi đó, ta copy công thức từ ô D4 sang bất kỳ 1 ô nào khác thì giá trị hàng và cột khi đó vẫn cố định không thay đổi.

Cách cố định công thức trong excel mới nhất

Cách cố định vùng dữ liệu trong Excel

Dựa vào các tham chiếu ở trên thì bạn có thể áp dụng tương tự khi muốn cố định vùng dữ liệu nào đó trong Excel như sau

  • C$4:D$8
  • $C4:$D6
  • $C$4:$D$6

** Nếu các bạn đã hiểu các dạng tham chiếu ở trên thì nhìn vào 3 cách cố định vùng dữ liệu như trên là hiểu luôn đúng không nào và mình sẽ không giải thích nữa. Nếu bạn chưa hiểu thì hãy xem thật kỹ các ví dụ ở trên hoặc để lại bình luận ở dưới để mình THÔNG thêm cho nhé :))Phím tắt chuyển đổi nhanh các dạng tham chiếu trong Excel

Để chuyển đổi nhanh giữa các dạng tham chiếu tương đối, tuyệt đối hàng, cột bạn hãy sử dụng phím F4 nhé. Cách dùng phím F4 để chuyển đổi các bạn làm theo ví dụ như sau:

Ta có công thức như sau: =SUM(C4:D8)

Lưu ý: Để cố định ô nào thì bạn đặt con trỏ chuột vào vị trí ô đó rồi ấn F4.

  • F4 lần 1: Công thức sẽ được chuyển sang tham chiếu tuyệt đối hàng và cột (cố định cả hàng và cột) như sau: =SUM($C$4:$D$8)
  • F4 lần 2: Công thức sẽ được chuyển sang tham chiếu tuyệt đối hàng (cố định hàng) như sau: =SUM(C$4:D$8)
  • F4 lần 3: Công thức sẽ được chuyển sang tham chiếu tuyệt đối cột (cố định cột) như sau: =SUM($C4:$D8) 
  • F4 lần 4: Công thức sẽ được chuyển về tham chiếu tương đối như sau: =SUM(C4:D8)
Xem thêm:  Cách thêm mục lục trong word 2013 mới nhất

Lời kết

Như vậy qua bài viết này bạn đã biết cách cố định vùng dữ liệu trong Excel và hiểu được các dạng tham chiếu tương đối, tuyệt đối là như nào rồi nhé. Nếu còn bất cứ thắc mắc gì hãy để lại comment ở bên dưới mình sẽ giải thích thêm cho nhé.

Nếu thấy bài viết hay hãy like và share ủng hộ tinh thần cho Admin nhé. Và nhớ theo dõi chuyên mục thủ thuât Excel để biết nhiều thủ thuật và các hàm Excel hơn nữa nhé. Chúc bạn thành công!

Cách cố định công thức trong excel mới nhất

Cách giữ ô tham chiếu cố định trong Excel

  • Chuyên mục:
  • Excel cơ bản

Khi bạn tham chiếu ô trong công thức, địa chỉ ô sẽ tự điều chỉnh khi bạn di chuyển đến một vùng khác hoặc sao chép công thức đến ô khác. Trong nhiều trường hợp, bạn có thể muốn cố định ô trong excel. Trong bài viết này, Học Excel Online sẽ hướng dẫn cách giữa cố định địa chỉ ô tham chiếu.

1 - đóng băng các ô trong excel
1 – cố định ô trong excel

Để giữ cố định ô trong excel trong công thức, bạn chỉ cần thêm ký hiệu $ vào trước ô tham chiếu bằng cách nhấn F4. Hãy làm như sau:

1. Chọn ô chứa công thức bạn muốn cố định.

2. Trên thanh công thức, chọn ô tham chiếu, nhấn F4.

Trong trường hợp này, là ô A1.

Cách cố định công thức trong excel mới nhất
2 – cố định ô trong excel

Và bạn có thể thấy A1 được cố định trong công thức như sau:

Cách cố định công thức trong excel mới nhất
3 – cố định ô trong excel
Cách cố định công thức trong excel mới nhất

Cách cố định công thức trong excel mới nhất

Tác giả: dtnguyen (Nguyễn Đức Thanh)

@ Học Excel Online | DTNguyen.business

· ·  · 

Bài viết khác của cùng tác giả

Khóa học mới xuất bản

  • Tất cả
  • Python
  • SQL
  • VBA
Cách cố định công thức trong excel mới nhất

Lập trình tương tác Python Excel chìa khóa tối ưu công việc

3.000.000VND 1.800.000VND

PYXL101

Xem chi tiết

Cách cố định công thức trong excel mới nhất

Khóa học Trọn bộ SQL cơ bản qua 500 câu truy vấn dành cho người mới bắt đầu

3.000.000VND 1.600.000VND

SQL100

Xem chi tiết

Cách cố định công thức trong excel mới nhất

Python cơ bản cho người mới bắt đầu

499.000VND 199.000VND

PY100

Xem chi tiết

Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel

Excel

Trong Excel khi sử dụng công thức có tham chiếu tới 1 vùng dữ liệu nếu bạn sử dụng địa chỉ tương đối công thức và vùng dữ liệu sẽ thay đổi theo khi bạn di chuyển sang ô khác. Ở bài viết này giúp các bạn cố định công thức hoặc vùng dữ liệu được tham chiếu tới trong Excel.

Cách cố định công thức trong excel mới nhất

Để cố định công thức hoặc vùng dữ liệu trong Excel sử dụng địa chỉ tuyệt đối hay chính là cách sử dụng dấu $ trong địa chỉ ô để cố định.

Trong địa chỉ ô bao gồm chỉ số cột và chỉ số hàng, bạn muốn cố định hàng hoặc cột bạn chỉ cần khóa giá trị của địa chỉ ô tương ứng.1. Cố định 1 giá trị bất kì trong công thức

Ví dụ có bảng dữ liệu dưới đây cần tính số tiền cần thanh toán cho đại lý cấp 1. Số tiền cần thanh toán sẽ bằng giá trị số lượng trong cột B nhân với đơn giá ở ô C5. Nếu các bạn chỉ nhập công thức đơn thuần = B5*C5:

Cách cố định công thức trong excel mới nhất

Khi nhấn Enter các bạn sẽ thấy giá trị chỉ đúng tại ô C7 khi copy công thức cho các ô khác các bạn sẽ thấy sẽ có ô nhận giá trị sai:

Cách cố định công thức trong excel mới nhất

Lý do công thức sẽ sai cho các ô sau vì công thức nhân đơn giá được cố định ô C5, trong công thức B5*C5 ở dạng tuyệt đối nên khi sao chép công thức cho các giá trị khác địa chỉ ô C5 được thay đổi theo, ví dụ như ở đây ô C6 không có giá trị số nên giá trị trả về lỗi Value:

Xem thêm:  Mẫu lệnh filter trong excel năm 2021
Cách cố định công thức trong excel mới nhất

– Như vậy với công thức và bài toán này bạn cần cố định giá trị đơn giá ở ô C5. Vì yêu cầu bài chỉ tính thành tiền cho đơn vị đại lý cấp 1 nên bạn có thể:

+ Cố định hàng 5 của ô C5: C$5.

+ Hoặc cố định cả hàng 5 và cột C của ô C5:$C$5.

– Bạn chỉ cần kích chọn vào công thức nhấn phím F4 lần 1 là có thể đổi địa chỉ C4 thành địa chỉ tuyệt đối (cố định cả cột và hàng):

Cách cố định công thức trong excel mới nhất

– Với yêu cầu chỉ tính tiền đại lý cấp 1 nên bài bạn có thể chỉ cần cố định hàng 5 mà không cần cố định cột C, bạn chỉ cần nhấn phím F4 lần 2 là có thể cố định hàng 5:

Cách cố định công thức trong excel mới nhất

– Với 2 cách cố định trên đều đưa ra kết quả đúng khi bạn tính thành tiền cho đại lý cấp 1:

Cách cố định công thức trong excel mới nhất

– Các bạn lưu ý khi nhấn phím F4 lần 3 chúng ta chuyển sang cố định cột C mà không cố định hàng thứ 5 của C5:

Cách cố định công thức trong excel mới nhất

– Và như vậy kết quả tính toán sẽ trả lại giá trị sai:

Cách cố định công thức trong excel mới nhất

2. Cố định các giá trị trong công thức

– Với cách tính như phần 1 các bạn đã tính thành tiền cho đại lý cấp 1. Với đại lý cấp 2, 3 bạn thực hiện thao tác tương tự là có thể đưa ra đáp án. Nhưng cách tính đó chưa tối ưu, ở bài viết này ThuThuatPhanMem.vn sẽ giới thiệu các bạn cách tính nhanh bằng cách cố định dữ liệu.

– Bài toán đặt ra tính thành tiền cho đại lý các cấp với mỗi đại lý có 1 đơn giá khác nhau.

– Phân tích bài toán.

+ Công thức chung để tính thành tiền cho 3 đại lý: Bx*K5.

Trong đó: x chạy từ 7 tới n là hàng, K là cột tương ứng các đại lý với K bằng 1 trong 3 giá trị C, D, E.

=> Như vậy trong công thức giá trị thay đổi sẽ là: x của giá trị hàng của địa chỉ Bx và K là giá trị cột của địa chỉ K5. Giá trị cố định là cột B và giá trị hàng 5.

=> Trong công thức cần cố định cột B, và hàng 5 của K => lấy địa chỉ tuyệt đối của cột B và hàng 5:

Bước 1:   Sửa lại cột B trong B7.

Tại ô cần quy đổi thành tiền của đại lý cấp 1, nhập công thức = B7 => nhấn F4 ba lần (nhấn F4 lần đầu sẽ sửa được cả hàng và cột B7, nhấn F4 lần hai sẽ sửa được hàng 7 trong B7, nhấn F4 3 lần để sửa cột B trong B7):

Cách cố định công thức trong excel mới nhất

Bước 2:   Ràng buộc hàng 5.

Tiếp tục bấm dấu nhân trong toán -> bấm chọn giá trị ô C5 -> bấm F4 2 lần là bạn đã khóa dòng địa chỉ đơn giá:

Cách cố định công thức trong excel mới nhất

Bước 3:   Nhấn   Enter   để nhận kết quả.

Cách cố định công thức trong excel mới nhất

Bước 4:   Copy công thức các giá trị ở cột đại lý cấp 1 để lấy kết quả.

Cách cố định công thức trong excel mới nhất

Bước 5:   Trong khi kéo đến giá trị ô cuối cùng của cột tác nhân cấp 1, nhấn giữ chuột và kéo sang phải để tác nhân cấp 2 và cấp 3 nhận được kết quả.

Cách cố định công thức trong excel mới nhất

Bạn có thể kiểm tra lại kết quả, sẽ không có giá trị nào bị sai. Với cách làm này, bạn đã giảm 2 lần việc tạo công thức cho các đặc vụ cấp 2 và 3. Vì vậy, bạn đã chú ý đến cách sửa dữ liệu bằng cách sử dụng địa chỉ tuyệt đối và tùy thuộc vào giá trị, hãy sửa cột. và chuỗi, bạn có thể dễ dàng chặn chúng.

Đối với mỗi địa chỉ ô, nhấn phím F4 để đóng băng dữ liệu như sau:

– Nhấn F4 lần 1: sửa các hàng, cột của địa chỉ ô tương ứng.

– Nhấn F4 hai lần: đã sửa giá trị dòng của địa chỉ ô tương ứng.

– Nhấn F4 3 lần: giá trị cột của địa chỉ ô tương ứng được sửa.

Phần trên mô tả cách sửa công thức hoặc sửa vùng dữ liệu trong công thức trong Excel. Chúc may mắn!


Video Cách cố định công thức trong excel mới nhất

Xem thêm >> https://123docx.net/tin-hoc-van-phong/

Related Posts