Đề Xuất 12/2022 # Cách Cố Định Vùng Dữ Liệu Trong Excel – Các Tham Chiếu Tương Đối, Tuyệt Đối / 2023 # Top 15 Like | Photomarathonasia.com

Đề Xuất 12/2022 # Cách Cố Định Vùng Dữ Liệu Trong Excel – Các Tham Chiếu Tương Đối, Tuyệt Đối / 2023 # Top 15 Like

Cập nhật nội dung chi tiết về Cách Cố Định Vùng Dữ Liệu Trong Excel – Các Tham Chiếu Tương Đối, Tuyệt Đối / 2023 mới nhất trên website Photomarathonasia.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

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

Ký hiệu $ trong excel có nghĩa là gì

4.3

/

5

(

6

bình chọn

)

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

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

Ký hiệu $ trong excel có nghĩa là gì

Ký hiệu $ là 1 trong những ký tự thường dùng khi sử dụng các công thức Excel. Tác dụng của nó là để cố định hàng, cột hay 1 vùng dữ liệu trong Excel.

Trước khi tìm hiểu về các tham chiếu chúng ta cần hiểu rõ cách định nghĩa 1 ô, 1 vùng dữ liệu trong Excel như sau:

Định nghĩa 1 ô trong Excel

Ô trong Excel sẽ được thể hiện ở dạng: Tên cột + Số hàng

Ví dụ

ô A5 – là ô nằm ở cột A và hàng thứ 5

ô H10 – là ô nằm ở cột H và hàng thứ 10

Định nghĩa 1 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ụ

Vùng dữ liệu A5:H10 – là vùng chọn được xác định bởi ô trên cùng bên trái là A5 và ô dưới cùng bên phải là H10

Tham chiếu tương đối

Tham chiếu tương đối là tham chiếu mặc định khi chọn vùng dữ liệu trong Excel. Với tham chiếu tương đối thì giá trị tham chiếu sẽ thay đổi theo hàng hay cột, tức là khi bạn thay đổi vị trí theo hàng hay cột thì giá trị tham chiếu cũng sẽ thay đổi theo.

Ví dụ

– Tại ô D4 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 bạn kéo chuột để copy công thức từ ô D4 xuống các ô ở hàng dưới hoặc cột bên thì tham chiếu đến các ô sẽ thay đổi

Tham chiếu tuyệt đối

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

Có 3 kiểu tham chiếu tuyệt đối như sau:

=A$1: Tham chiếu tuyệt đối theo hàng (cố định hàng) – Khi copy công thức sang 1 ô khác thì chỉ có giá trị cột thay đổi còn giá trị hàng được cố định.

=$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.

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.

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 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

Phím tắt chuyển đổi nhanh các dạng tham chiếu trong Excel

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)

Lời kết

Cố Định Vùng Dữ Liệu Trong Excel / 2023

Ký tự $ trong các công thức Excel có ý nghĩa như thế nào.

Đây là một trong những ký tự phổ biến nhất khi áp dụng các công thức của Excel, mục đích của nó là để cố định một ô hay một vùng dữ liệu trong excel.

Thông thường ứng dụng của việc cố định công thức áp dụng khi viết hàm.

Bạn viết hàm, bạn kéo công thức, muốn một vài chỉ tiêu cố định, một vài chỉ tiêu di chuyển. Vậy càng phải hiểu và dùng linh hoạt thao tác này.

Một số hàm thường xuyên dùng đến việc cố định dòng cột

Hàm Vlookup: Áp dụng trong trường hợp tự động tên hàng, DVT hay tên KH, Địa chỉ khi đã có mã. Chúng ta sẽ áp dụng việc cố định bảng dữ liệu tìm kiếm

Hàm Sumif/ Sumifs: Cố định vùng dữ liệu tính tổng/ Vùng điều kiện

Hàm Match: Cố định điều kiện tham chiếu và vùng tham chiếu

Hàm If: Cố định điều kiện so sánh

Rất nhiều hàm khác tại: https://excel.webkynang.vn

Hầu hết các ô, vùng không cần cố định khi công thức ấy là duy nhất. Chỉ cần công thức ấy cần phải copy, di chuyển đến các vị trí khác, có thể áp dụng thì cần cố định

Phím tắt sử dụng để cố định ô/ vùng dữ liệu trong excel:

Cho bảng dữ liệu từ A1:D10, Tính tổng số liệu cột D (cột thành tiền)

Tại D11 =sum(D1:D10

+ F4 lần 1 công thức sẽ tự động cô định cả dòng cả cột thành =sum($D$1:$D$10

+F4 lần 2 công thức sẽ tự động cố định dòng thành =sum(D$1:D$10

+ F4 lần 3 công thức sẽ tự động cố định cột thành =sum($D1:$D10

Để kết thúc công thức, ta đóng ngoặc đơn lại và nhấn enter.

Phân tích

Một ô trong Excel có địa chỉ:

Tên Cột_Số Thứ Tự Dòng : [A1]

Một Vùng dữ liệu gồm: [A1:A100]

Địa chỉ ô bên trái đầu tiền : Địa chỉ ô cuối cùng bên phải

Ví dụ:

– A1: ô A1 sẽ cho chúng ta biết nó thuộc Cột A và dòng số 1

– AZ1000 : ô AZ1000 sẽ cho chúng ta biết nó thuộc Cột AZ và dòng số 1000

– B2:D15: Đây là một vùng dữ liệu được xác định bằng địa chỉ 2 ô đầu tiên bên trái (B2) và địa chỉ ô cuối cùng bên phải (D15)

Như vậy, khi viết công thức có sử dụng địa chỉ một ô hay một vùng dữ liệu để làm tham chiếu hoặc làm điều kiện thì chúng ta cần phải cố định vùng dữ liệu hoặc ô đó để có thể copy công thức sang các ô khác mà không làm thay đổi vùng dữ liệu/ ô trong công thức.

Các kiểu cố định vùng dữ liệu

Có 3 kiểu cố định ô/ vùng dữ liệu:

– A$1: Cố định dòng mà không cố định cột. Khi copy công thức sang một cột khác thì chỉ có địa chỉ cột thay đổi, trong khi số thứ tự dòng không thay đổi.

– $A1: Cố định cột mà không cố định dòng. Khi copy công thức sang một dòng khác thì địa chỉ cột không thay đổi, trong khi số thứ tự dòng thay đổi.

– $A$1: Cố định cả dòng và cột. Khi copy công thức sang một cột khác thì cả địa chỉ cột và số thứ tự dòng đều không thay đổi.

Để cố định một vùng dữ liệu ta áp dụng nguyên tắc tương tự:

B$2:D$15

$B2:$D15

$B$2:$D$15

Cách Lọc Dữ Liệu Trong Excel / 2023

Cách lọc dữ liệu trong excel hay cách sử dụng autofiler – đây là tính năng tuyệt vời của excel. Sử dụng Autofiler trong excel 2007 2010 tuyệt vời hơn excel 2003 tương đối nhiều.

Một số điểm nổi bật của cách lọc dữ liệu trong excel 2007 2010 (autofilter)

Giao diện đẹp hơn

Tính năng lọc được nâng cấp

Hiển thị nhiều tính năng hơn excel 2003

Tên tiếng anh của chức năng lọc dữ liệu trong excel là: Autofilter

Trong autofilter có 2 phần chính:

Autofilter mặc định của excel

Advanced filter: chức năng lọc nâng cao của excel sẽ giúp các bạn lọc được theo nhiều điều kiện khác nhau, tùy ý của bạn. Thậm chí có thể di chuyển kết quả lọc được sang một vùng hoàn toàn mới.

1. Chức năng autofilter mặc định của excel (lọc dữ liệu mặc định trong excel)

1.1. Ứng dụng của chức năng lọc dữ liệu trong excel.

Theo yêu cầu quản trị của quản lý cửa hàng, giám đốc công ty, khi các bạn làm kế toán bán hàng, nhân viên bán hàng hoặc nhân viên thống kê: Các bạn sẽ phải tổng hợp tình hình bán hàng của cửa hàng theo ngày, theo tuần theo tháng hoặc theo năm. Các tiêu chí dùng để lập báo cáo bán hàng là khác nhau.

Và đặc biệt, vào một thời điểm bất kỳ nào đó trong ngày, cửa hàng trưởng yêu cầu bạn:

“Em thống kê cho anh xem, tổng doanh số của quầy Rau quả tính đến thời điểm này là bao nhiêu?”

Để làm được điều đó, bạn hãy sử dụng tính năng lọc dữ liệu trong excel (autofilter). Đây chính là cứu cánh là giải pháp cho bạn. Bạn sẽ không cần phải lo lắng gì về việc làm thế nào để lọc ra dữ liệu nữa.

– Nhìn nhanh tổng doanh số của Ngành hàng D

– Liệt kê top 10 quầy hàng có doanh số bán hàng tốt nhất

– Liệt kê những quầy hàng có doanh số trên 400.000 KVND

1.2. Bật hoặc tắt tính năng lọc dữ liệu trong excel 2007 2010

Trước khi thành thạo cách lọc dữ liệu trong excel, bạn cần phải biết làm thế nào để kích hoạt chức năng autofilter trong excel 2007 2010.

Việc xuất hiện một hình vuông nền trắng bên trong đó là một hình tam giác màu đen xuất hiện ở góc phía dưới bên phải của mỗi ô được chọn.

– Để tắt tính năng lọc dữ liệu (autofilter) trong excel đi, ta chỉ cần vào Data và chọn filter là được.

Yêu cầu 1: Cách lọc dữ liệu trong excel 2007 2010 để tìm tổng doanh số của ngành hàng D

Bước 1:

Nhấn vào hình vuông bên cạnh chữ “Ngành hàng”

Bỏ tick “select all”

Tiếp tục chọn “D”

Nhấn “OK”

Bạn tiếp tục dùng chuột bôi đen doanh số của tất cả các quầy thuộc Ngành hàng D. Khi đó, tổng doanh số của ngành hàng D sẽ hiện lên trên thanh status bar như hình sau:

Yêu cầu 2: Cách lọc dữ liệu trong excel để tìm ra top 10 quầy hàng có doanh số tốt nhất.

Với yêu cầu liệt kê TOP 10 quầy hàng có doanh số tốt nhất, thì dường như còn dễ hơn cả yêu cầu số 1.

B2: Chọn “Number filter”

Trong yêu cầu, sếp bạn muốn liệt kê 10 quầy có doanh số tốt nhất (top 10). Do đó, bạn không cần phải chỉnh sửa gì trong bảng này mà nhấn “OK” luôn.

Đây có lẽ là tùy chọn lọc dữ liệu phổ biến nhất trong excel. Ta thực hiện như sau:

Bước 1: Chọn hình vuông bên cạnh “Doanh số (KVND) “

B2: Chọn Number filters

B3: Chọn “Greater than”

Và nhấn “OK”

Cách Dùng Hàm Match Để Xác Định Vị Trí Dữ Liệu Trong Excel / 2023

Cách dùng hàm MATCH để xác định vị trí dữ liệu trong Excel

Nếu như bạn đang làm việc với Microsoft Excel, bạn có thể sử dụng hàm MATCH để xác định vị trí của một giá trị trong một hàng, cột hay bảng dữ liệu. Hàm này có nhiệm vụ sẽ trả về một số chỉ vị trí tương đối đầu tiên của dữ liệu trong danh sách, mảng hoặc phạm vi ô được chọn. Đây là một hàm dùng để tra cứu và tham chiếu dữ liệu trong Excel.

Ghi chú: Hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, và 2007; Excel cho Microsoft 365; Excel Online; Excel cho Mac; Excel cho iPad; Excel cho iPhone; và Excel cho Android.

Nội dung chính:

Tại sao bạn cần sử dụng hàm MATCH trong Excel?

Công thức hàm MATCH trong Excel

Cách sử dụng hàm MATCH trong Excel

Kết hợp hàm MATCH với các hàm Excel khác

Tại sao bạn cần sử dụng hàm MATCH trong Excel?

Trong các bảng tính lớn, đôi khi sẽ vô cùng phức tạp để tìm kiếm một giá trị cụ thể. Mặc dù Excel (và các ứng dụng Office khác) cũng hỗ trợ cung cấp chức năng tìm kiếm nhưng đôi khi vẫn không thể mang lại kết quả chính xác. Nếu bạn muốn biết một giá trị cụ thể đang nằm ở vị trí thứ mấy trong bảng dữ liệu và sau đó sẽ chuyển dữ liệu này sang một công thức Excel khác, bạn cần sử dụng chức năng của hàm MATCH trong Excel.

Kết quả tìm kiếm được hiển hị ở dạng vị trí tương đối và luôn đề cập đến vị trí của khu vực tìm kiếm. Bạn có thể tìm kiếm giá trị trong một cột hoặc một hàng. Kết quả sẽ cho thấy có bao nhiêu giá trị đếm từ phía bên trái (cho một hàng) hoặc đếm từ phía trên xuống (cho một cột) của tiêu chí tìm kiếm được lựa chọn.

Công thức hàm MATCH trong Excel

Cú pháp của hàm để cập đến cách bố trí của hàm và được bao gồm tên, dấu ngoặc, dấu tách, dấu phẩy và các đối số của hàm. Công thức của hàm MATCH là:

Lookup_value (bắt buộc): Là giá trị mà bạn muốn tìm kiếm trong danh sách dữ liệu. Đối số này có thể là một số, văn bản, giá trị logic hay tham chiếu ô.

Lookup_array (bắt buộc): Phạm vi các ô đang được tìm kiếm

Match_type (tùy chọn): Cho Excel biết cách tra cứu dữ liệu của Lookup_value với những giá trị trong Lookup_array. Giá trị mặc định của đối số này là 1. Người dùng có các lựa chọn là -1, 0 hoặc 1.

Nếu đối số Match_type bằng 1 hoặc bị bỏ qua. hàm MATCH sẽ tìm giá trị lớn nhất thỏa điều kiện nhỏ hơn hoặc bằng đối số Lookup_value. Dữ liệu Lookup_array sẽ được sắp xếp theo thứ tự tăng dần.

Nếu đối số Match_type bằng 0 thì nó sẽ tìm giá trị chính xác bằng với đối số Lookup_value. Dữ liệu Lookup_array có thể được sắp xếp theo bất kỳ thứ tự nào.

Nếu đối số Match_type bằng -1, hàm MATCH sẽ tìm giá trị nhỏ nhất phù hợp với điều kiện nhỏ hơn hoặc bằng đối số Lookup_value. Dữ liệu Lookup_array sẽ được sắp xếp theo thứ tự giảm dần.

Hàm MATCH trả về số vị trí của giá trị tra cứu trong một phạm vi hoặc bảng dữ liệu.

Nói cách khác, Excel sẽ tìm kiếm giá trị được người dùng cung cấp trong phạm vi cụ thể (Lookup_Array), sau đó trả về số vị trí của nó được tính từ đầu danh sách.

Chú ý: Hãy nhớ rằng bạn luôn bắt đầu các công thức hàm trong Excel bằng dấu bằng (=). Ngoài việc nhập công thức trực tiếp vào ô của bảng tính trong Excel, bạn cũng có thể nhập công thức hàm Excel bằng hộp thoại chức năng.

Cách sử dụng hàm MATCH trong Excel

Để có thể nhập hàm MATCH và các đối số:

Bước 1: Bạn mở một bảng tính Excel mới và nhập dữ liệu vào các cột A, B, C (như hình ảnh bên dưới). Để trống ô B2, vì ô đó sẽ là ô bạn nhập hàm MATCH vào.

Bước 2: Lựa chọn ô B2 để biến nó thành ô hiện hoạt.

Bước 3: Chọn tab Formulas từ Menu chính.

Bước 4: Chọn Lookup & Reference để mở danh sách hộp thoại chức năng.

Bước 5: Chọn MATCH trong danh sách để mở hộp thoại Function Arguments (Trong Excel cho Mac thì Formula Builder cũng sẽ được mở ra).

Bước 6: Đặt con trỏ chuột vào vị trí Lookup_value.

Bước 7: Chọn ô A2 trong bảng tính để nhập tham chiếu ô.

Bước 8: Tiếp theo, bạn hãy đặt con trỏ vào mục Lookup_array.

Bước 9: Tô đen các ô từ C2 đến C7 trong bảng tính để chọn làm phạm vi tham chiếu dữ liệu.

Bước 10: Cuối cùng, đặt con trỏ chuột vào hộp văn bản Match_type.

Bước 11: Nhập số 0 vào đối số này để tìm kết quả khớp chính xác với dữ liệu trong ô B2.

Bước 12: Lựa chọn OK để hoàn thành công thức hàm. (Trong Excel cho Mac, bạn chọn Done).

Bước 13: Kết quả là 5 sẽ xuất hiện trong ô B2 vì cụm từ “Máy giặt” đứng thứ năm trong danh sách.

Bước 14: Khi bạn nhấp chuột trái vào ô B2 , công thức hàm MATCH hoàn chỉnh sẽ được hiển thị trên thanh công thức phía trên của trang tính.

Kết hợp hàm MATCH với các hàm Excel khác

Hàm MATCH trong Excel thường được sử dụng cùng với các hàm tra cứu dữ liệu khác, chẳng hạn như hàm VLOOKUP và hàm INDEX và được sử dụng làm đầu vào cho các đối số khác, ví dụ như:

Làm đối số col_index_num của hàm VLOOKUP.

Làm đối số row_num của hàm INDEX.

Hẹn gặp lại bạn trong những bài viết sắp tới trên website của chúng tôi.

Bạn đang đọc nội dung bài viết Cách Cố Định Vùng Dữ Liệu Trong Excel – Các Tham Chiếu Tương Đối, Tuyệt Đối / 2023 trên website Photomarathonasia.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!