Tham chiếu hỗn hợp, công thức mảng và tham chiếu 3 -D trong Excel

Như các bạn cũng đã biết, công thức/ hàm thường được dùng để tính toán các dữ liệu trong bảng tính Excel. Khi dữ liệu thay đổi thì các công thức này sẽ tự động được cập nhật và kết quả sẽ tự động được làm mới.

Một công thức thông thường sẽ có các thành phần như sau:

1. “Dấu bằng” (luôn luôn có và nó là kí tự đầu tiên trong mọi công thức).
2. Toán tử
3. Trị số
4. Địa chỉ tham chiếu
5. Hàm

Ví dụ như công thức =10+IF(A1>1000, B1*10%, B1). Công thức này có đầy đủ cả 5 thành phần trên, cụ thể như sau:

Trong phạm vi của bài viết này, mình sẽ tập trung hướng dẫn về thành phần “thứ tư” trong công thức, tức là địa chỉ tham chiếu. Các thành phần còn lại mình sẽ hướng dẫn trong những bài viết tiếp theo nha các bạn.

Ngoài ra, mình cũng sẽ giới thiệu sơ lược về công thức mảng và tham chiếu 3-D trong Excel để các bạn có thể tự tư duy nếu muốn tìm hiểu thêm nhé.

Đọc thêm:

• Các kỹ năng Copy và Paste trên Excel mà bạn nên biết
• Cách sử dụng 13 hàm Excel cơ bản và thường dùng nhất !

#1. Tham chiếu hỗn hợp trong các công thức Excel

Việc sử dụng chính xác các loại địa chỉ ô, khối ô trong công thức sẽ giúp cho việc tham chiếu các giá trị tính toán được chính xác. Microsoft Excel cung cấp cho chúng ta tất cả 4 loại địa chỉ ô, khối ô như trong hình bên dưới.

1.1. Địa chỉ tương đối

Địa chỉ tương đối là loại địa chỉ sẽ thay đổi khi sao chép công thức. Đây là loại địa chỉ được sử dụng nhiều nhất trong hầu hết các công thức, các hàm (như là hàm SUM, MAX, MIN, AVERAGE, …).

1.2. Địa chỉ hỗn hợp cố định dòng

• Địa chỉ hỗn hợp cố định dòng là loại địa chỉ có dòng không thay đổi khi sao chép công thức, nhưng cột thì có thể thay đổi.
• Hàm RANK, hàm trả về thứ hạng của một số trong danh sách các số cần xếp hạng là một trong những hàm thường tham chiếu đến ô, khối ô có địa chỉ hỗn hợp cố định dòng.

1.3. Địa chỉ hỗn hợp cố định cột

• Địa chỉ hỗn hợp cố định cột là loại địa chỉ có cột không thay đổi khi sao chép công thức, nhưng dòng có thể thay đổi

1.4. Địa chỉ tuyệt đối

• Địa chỉ tuyệt đối là loại địa chỉ không thay đổi khi sao chép công thức. Đây là loại địa chỉ được sử dụng phổ biến thứ nhì, sau địa chỉ tương đối.
• Địa chỉ tuyệt đối thường được sử dụng nhiều nhất trong hàm dò tìm như VLOOKUP và HLOOKUP.

#2. Công thức mảng trong Excel

Công thức mảng là một loại công thức đặc biệt, nó có thể thực hiện được nhiều phép tính đối với một hoặc nhiều thành phần trong mảng.

Một cách dễ hiểu hơn là bạn có thể xem mảng là một dòng giá trị, một cột giá trị, một sự kết hợp của các dòng và cột giá trị. Công thức mảng có thể trả về một kết quả hoặc nhiều kết quả.

Các bước tạo và ví dụ minh họa cho công thức mảng như sau:

+ Bước 1: Chọn ô hoặc khối ô mà bạn muốn tạo công thức mảng.

+ Bước 2: Nhập công thức mảng.

+ Bước 3: Sau đó nhấn tổ hợp phím Ctrl + Shift + Enter

=> Ngay sau khi nhấn tổ hợp phím Ctrl + Shift + Enter ta sẽ thu được kết quả và công thức lúc bấy giờ sẽ trở thành {=B1:E1*B2:E2}

Bạn cũng có thể nhập công thức =PRODUCT(B1, B2) tại ô B3 rồi “kéo sang” các ô còn lại C3, D3 và E3. Chúng ta vẫn thu được kết quả như trên. Ở ví dụ này thì cả 2 cách vừa hướng dẫn đều là phương án tối ưu..

#3. Tham chiếu 3-D trong Excel (tham chiếu 3 chiều)

Tham chiếu 3 chiều hay còn gọi là tham chiếu 3-D trong Excel là một dạng địa chỉ tham chiếu tới cùng một ô hoặc một khối ô (nhiều ô liên tiếp) trên nhiều trang tính khác nhau (tất nhiên trong cùng một bảng tính).

Các bước tạo và ví dụ minh họa cho tham chiếu 3-D như sau:

+ Bước 1: Nhập công thức và chọn ô hoặc khối ô cần tham chiếu ở trang tính đầu tiên.

+ Bước 2: Nhấn giữ phím Shift trên bàn phím => chọn trang tính cuối cùng (Sheet5 đó các bạn). Mục đích là để bôi đen và chọn Sheet1 đến Sheet5 ấy mà.

+ Bước 3: Nhấn phím Enter

Cuối cùng ta thu được kết quả là 3840. Biết ô A1 của Sheet1-5 có giá trị lần lượt là 2, 4, 6, 8, 10

#4. Lời kết

Okay, như vậy là mình đã hướng dẫn xong cho bạn cách tham chiếu hỗn hợp, công thức mảng và tham chiếu 3 -D trong Excel rồi nhé.

Các bạn lưu ý thêm là, khi sao chép công thức, nếu địa chỉ ô – khối ô là địa chỉ tương đối thì sẽ bị thay đổi như sau:

• Nếu công thức được sao chép theo chiều dọc thì chỉ số dòng sẽ thay đổi.
• Nếu công thức được sao chép theo chiều ngang thì chỉ số cột sẽ thay đổi.

Để thực hiện nhanh việc chuyển địa chỉ ô, khối ô từ tương đối sang các địa chỉ hỗn hợp hoặc tuyệt đối thì ta có thể đặt con trỏ chuột vào công thức của ô hoặc khối ô đó => nhấn phím F4 trên bàn phím là được.

Chẳng hạn trong công thức sau khi chọn ô A1 => bạn nhấn phím F4 lần thứ nhất ta sẽ được $A$1, bạn nhấn phím F4 lần thứ nhì ta sẽ được A$1, nhấn lần thứ ba sẽ là $A1, cuối cùng bạn nhấn lần thứ tư để quay về trạng thái A1 ban đầu.

Xin chào tạm biệt và hẹn gặp lại các bạn trong những bài viết tiếp theo nhé !

CTV: Nhựt Nguyễn – Blogchiasekienthuc.com