Blog Giải Pháp Excel: Kiến thức Excel

THAM CHIẾU TRONG EXCEL

Một tham tham chiếu xác định một ô hay vùng trên bảng tính giúp Excel biết được vị trí chứa dữ liệu mà bạn muốn sử dụng trong công thức. Sử dụng tham chiếu trong công thức giúp một công thức có thể truy cập được đến dữ liệu chứa tại nhiều nơi khác nhau của bảng tính hay ngược lại một giá trị trong một ô có thể được sử dụng bởi nhiều công thức khác nhau. Ngoài ra, Excel cho phép tham chiếu đến các ô thuộc các sheet khác trong cùng workbook hoặc khác workbook. Các tham chiếu đến các ô trong workbook khác được gọi là các liên kết (link).

Kiểu tham chiếu A1

Excel mặc định sử dụng kiểu tham chiếu A1, nghĩa là các cột được đặt tên theo các mẫu chữ cái (A đến Z và các tổ hợp chữ cái). Trong Excel 2003- các cột được đặt tên từ A đến IV (256 cột) và các dòng được đánh số từ 1 đến 65,536, còn trong Excel 2007+ thì các cột được đặt tên từ A đến XFD (16,384 cột) và cách dòng được đánh số từ 1 đến 1,048,576. Các ký tự và các con số này gọi là các tiêu đề cột và dòng (row and column headings). Để tham chiếu đến một ô, ta chỉ cần nhập vào ký tự đại diện cho cột và số thứ tự của dòng. Ví dụ tham khảo đến ô C4 thì ô này có ký tự cột là C và số dòng là 4.

Tham khảo đến worksheet khác: Xét ví dụ sau, hàm AVERAGE tính toán bình quân các giá trị thuộc vùng B1:B10 trên Worksheet Marketing trong cùng Workbook.

Ghi chú: Tên worksheet và dấu chấm than (!) được đặt trước vùng địa chỉ tham chiếu.

Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng:

Tên_sheet!Địa_chỉ_ô

Ví dụ:
=A2*Sheet2!A2
=A2*’Thong so’!B4
Khi tên sheet có chứa khoảng trắng thì để trong cặp nháy đơn ‘ ’

Tham chiếu đến địa chỉ trong workbook khác thì có dạng:

[Tên_Workbook]Tên_sheet!Địa_chỉ_ô

Ví dụ:
=A2*[Bai2.xlsx]Sheet3!A4
=A2*’[Bai tap 2.xlsx]Sheet3’!A4
Khi tên Sheet hay Workbook có chứa khoản trắng để trong cặp nháy đơn ‘ ’
=A2*’C:\Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến workbook khác mà workbook đó không mở
=A2*’\\DataServer\Excel\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến tập tin Excel chia sẽ trên máy chủ trong mạng nội bộ.

Tham chiếu vòng

Trong một số trường hợp nhập công thức, bạn nhận được hộp thoại thông báo tham chiếu vòng (Circular Reference) vì trong công thức có sử dụng giá trị trong ô mà bạn đang nhập công thức.

Ví dụ: Tại ô A3 bạn nhập vào công thức =A1+A2+A3

Nếu các phép tính của bạn yêu cầu phải tham chiếu vòng thì bạn cần phải bật tính năng cho phép tính lặplên. Cách thực hiện:

Excel 2003: Vào Tools → Options… → Calculation → chọn Iteration

Excel 2007: Nhấn vào nút Office → Excel Options → Formulas → Calculation options → chọn Enable iterative calculation.

Excel 2013: Vào File → Options → Formulas → Calculation options → chọn Enable iterative calculation.

Maximun Iterations: Số lần tính lặp tối đa cho công thức mà Excel sẽ thực hiện.
Maximun Change: Sự lệch đổi tối đa cho phép của kết quả công thức của các lần tính. Đặt số càng nhỏ thì kết quả càng chính xác nhưng Excel cần nhiều thời gian để tính hơn.

Ví dụ: Ô A1 chứa số 1, ô A2 chứa số 2, ô A3 nhập vào công thức =A1+A2+A3 và với thiết lập cho phép tính lặp 3 lần như trên thì kết quả ô A3 lần đầu tiên sau khi nhập công thức là 9. Nếu nhấn F9 thì kết quả sẽ là 18,…

Kết quả lần đầu trả về sau khi nhập công thức.

Kiểu tham chiếu 3-D

Khi chúng ta muốn phân tích dữ liệu có vùng địa chỉ giống nhau ở nhiều worksheet trong cùng workbook thì khi đó cần đến kiểu tham chiếu 3-D. Tham chiếu 3-D có dạng như ví dụ sau =SUM(Sheet2:Sheet6!A1), nghĩa là tính tổng các ô A1 nằm trong nhiều sheet (từ Sheet2 đến Sheet6).

Tham chiếu 3-D có thể dùng để tham chiếu đến các ô nằm trên các sheet khác, (có thể đặt tên (Name) cho tham chiếu 3-D), sau đó có thể dùng các hàm sau để tính toán: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, và VARPA.
Tham chiếu 3-D không thể dùng trong công thức mảng (công thức kết thúc bằng lệnh Ctrl+Shift+Enter).
Tham chiếu 3-D không thể dùng các toán tử số học (+,-,*,/,^, &) , toán tử so sánh (= > < >= <= <>, toán tử logic (and, or, not …).

Tham chiếu 3-D sẽ thay đổi như thế nào khi thực hiện các lệnh move, copy, insert, hay delete

Các ví dụ sau minh hoạ sự thay đổi của tham chiếu 3-D khi bạn thực hiện các lệnh move, copy, insert, hay delete trong worksheet. Ví dụ ta có công thức =SUM(Sheet2:Sheet4!A1:B5) để tính tổng các ô A1:B5 từ worksheet 2 đến worksheet 4. Kết quả ô A2 là 60

Insert hay copy Khi chèn hay sao chép các sheet nằm từ Sheet2 (sheet đầu - endpoint) đến Sheet4 (sheet cuối – endpoint), Excel sẽ bao gồm luôn tất cả giá trị của các ô A1:B5 trên sheet mới thêm vào kết quả tính toán.

Ví dụ: Chèn Sheet7 vào sau Sheet2 với các giá trị trong vùng A1:B5 như hình sau:

Kết quả ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 70

Delete Khi xoá các sheet từ Sheet2 đến Sheet4, Excel sẽ xoá các giá trị thuộc sheet bị xoá khỏi kết quả tính toán.

Ví dụ: Xoá Sheet7 khỏi Workbook, khi đó kết quả tại ô A2 trong Sheet TongHop

=SUM(Sheet2:Sheet4!A1:B5) → 60

Move Khi di chuyển các sheet từ Sheet2 đến Sheet6 đến một vị trí nằm ngoài vùng tham chiếu sheet, Excel sẽ loại bỏ các giá trị nằm trên sheet di chuyển khỏi kết quả tính toán.

Ví dụ: Di chuyển Sheet3 ra nằm sau Sheet4, khi đó các giá trị vùng A1:B5 trong Sheet3 bị loại khỏi kết quả tính toán. Khi đó kết quả tại ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 40

Move một Sheet đầu cuối Khi di chuyển Sheet2 hoặc Sheet6 đến vị trí khác trong cùng workbook, Excel sẽ tự hiệu chỉnh kết quả tính toán cho phù hợp với vùng tham chiếu sheet.

Ví dụ: Di chuyển Sheet4 ra nằm sau Sheet5, khi đó giá trị vùng A1:B5 trong Sheet5 sẽ bao gồm trong kết quả tính toán. Ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 100

Ví dụ: Di chuyển Sheet2 ra nằm sau Sheet4. Khi đó, công thức trong ô A2 trong Sheet TongHop biến đổi thành =SUM(Sheet3:Sheet4!A1:B5) → 50

Ví dụ: Di chuyển Sheet4 ra nằm trước Sheet2. Khi đó, công thức trong ô A2 trong Sheet TongHop biến đổi thành =SUM(Sheet2:Sheet3!A1:B5) → 30

Delete một Sheet đầu cuối Khi xoá Sheet2 hoặc Sheet6, Excel sẽ tự hiệu chỉnh kết quả tính theo cho phù hợp với vùng tham chiếu sheet mới.

Ví dụ: Xoá sheet cuối trong tham chiếu 3D Sheet4. Khi đó, công thức trong ô A2 trong Sheet TongHop biến đổi thành =SUM(Sheet2:Sheet3!A1:B5) → 30

Tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp

Tham chiếu tương đối (Relative references)

Tham chiếu tương đối trong công thức (ví dụ như A1) dựa vào vị trí tương đối của địa chỉ ô chứa công thức và địa chỉ của ô tham chiếu đến. Khi vị trí của ô chứa công thức thay đổi thì địa chỉ ô tham chiếu đến cũng thay đổi tương ứng.
Khi ta chép công thức sang các dòng hay cột khác, thì địa chỉ tham chiếu cũng tự động thay đổi tương ứng.
Excel mặc định công thức nhập vào dùng tham chiếu tương đối.

Ví dụ: Khi ta chép công thức tại ô B2 chứa tham chiếu tương đối đến ô A1 (=A1) xuống ô B3 thì khi đó công thức trong ô B3 tự động thay đổi tham chiếu đến ô A2 (=A2).

Tham chiếu tuyệt đối (Absolute references)

Một ô có công thức tham chiếu tuyệt đối đến một ô nào đó (ví dụ $A$1) khi đó dù cho ô chứa công thức bị di chuyển hay sao chép đến nơi khác thì công thức vẫn luôn luôn tham chiếu đến ô đó (A1).
Excel mặc định công thức nhập vào dùng tham chiếu tương đối, do vậy ta cần chuyển sang tham chiếu tuyệt đối khi cần thiết (bằng cách đặt dấu $ trước các tiêu đề dòng và cột muốn cố định – phím tắt là F4).

Ví dụ: Nếu ta chép một công thức (tại ô B2 xuốn ô B3) có tham chiếu tuyệt đối đến ô =$A$1 thì công thức trong ô B3 vẫn là =$A$1.

Tham chiếu hỗn hộp (Mixed references)

Tham chiếu hỗn hợp là dạng tham chiếu kết hợp 2 loại tham chiếu tương đối và tuyệt đối, trong tham chiếu hỗn hợp chỉ có cột hoặc dòng được cố định (tuyệt đối).
Tham chiếu cố định cột có dạng như $A1, $B1, ... và tham chiếu cố định dòng có dạng A$1, B$1, …. Khi vị trí của ô chứa công thức thay đổi thì phần địa chỉ tương đối (trong tham chiếu hỗn hợp) sẽ thay đổi theo còn phần địa chỉ tuyệt đối (trong tham chiếu hỗn hợp) sẽ không thay đổi.

Ví dụ: Khi ta chép công thức có chứa tham chiếu hỗn hợp (=A$1) trong ô B2 sang ô C3 thì công thức trong ô C3 thay đổi thành =B$1 (Địa chỉ tham chiếu trong công thức thay đổi 1 đơn vị từ cột A → B do không cố định cột, nhưng hàng 1 vẫn giữ nguyên do hàng đã bị cố định è công thức trong C3 là B$1).

Ví dụ: Tính cột “Thành tiền” bằng cách lấy cột “Số lượng” nhân với cột “Giá” và thống kê doanh số bán cho mỗi khách hàng theo tháng.

Tại ô E2 ta nhập vào công thức =C2*D2 với địa chỉ các ô tham chiếu là tương đối vì ta muốn khi sao chép công thức từ ô E2 xuống các ô dưới thì địa chỉ các ô tham chiếu sẽ thay đổi theo.

Công thức trong ô E3 sẽ là =C3*D3
Công thức trong ô E4 sẽ là =C4*D4
…
Công thức trong ô E21 sẽ là =C21*D21

Công thức trong ô B27 là =SUMIFS($E$2:$E$21,$A$2:$A$21,$A27,$B$2:$B$21,B$26 ), công thức này sau đó được sao chép qua các ô bên phải và các ô bên dưới nó.

Ta cần cố định tuyệt đối các vùng Thành tiền ($E$2:$E$21), vùng ra điều kiện Tháng ($A$2:$A$21), vùng ra điều kiện Khách hàng ($B$2:$B$21) vì ta muốn công thức luôn tha, chiếu đến các vùng này khi sao chép sang các ô khác.
Ta chỉ cần cố định cột ô điều kiện Tháng ($A1) do ta muốn tham chiếu dòng thay đổi khi sao chép công thức xuống các ô bên dưới nhưng tham chiếu cột không đổi khi ta sao chép công thức sáng các ô bên phải.
Ta chỉ cần cố định dòng ô điều kiện Khách hàng (B$26) do ta muốn tham chiếu dòng không thay đổi khi sao chép công thức xuống các ô bên dưới nhưng tham chiếu cột thay đổi khi ta sao chép công thức sáng các ô bên phải.

Kiểu tham chiếu R1C1

Ngoài ra chúng ta còn có thể chuyển kiểu tham chiếu từ dạng A1 sang dạng R1C1, kiểu tham chiếu R1C1 rất hữu ích cho việc tính toán vị trí dòng và cột trong VBA. Đối với kiểu R1C1, Excel ký hiệu “R” để chỉ dòng theo sau là số thứ tự dòng và ký hiệu "C" để chỉ cột theo sau là số thứ tự cột.

R: chỉ dòng
C: chỉ cột
Chỉ số sau R hay C nếu để trong móc vuông [ ] là tham chiếu tương đối
Chỉ số sau R hay C không để trong móc vuông [ ] là tham chiếu tuyệt đối
R (chính là R[0]) hay C (chính là C[0]) không có chỉ số theo sau là biểu thị cùng dòng hay cùng cột với ô hiện hành.

Để hiểu hơn về tham chiếu R1C1 xét các ví dụ sau:

Công thức trong bảng tính sử dụng tham chiếu R1C1

Giải thích công thức ô tại dòng 2 cột 5 (tương đương E2): =RC[-2]*RC[-1]

RC[-2] (tương đương C2)

o R: Cùng dòng ô hiện hành → dòng 2
o C[-2]: Dịch chuyển về bên trái ô chứa công thức hiện hành 2 cột → cột 3

RC[-1] (tương đương ô D2)

o R: Cùng dòng ô hiện hành → dòng 2
o C[-1]: Dịch chuyển về bên trái ô chứa công thức hiện hành 1 cột → cột 4

Giải thích công thức ô tại dòng 27 cột 2 (tương đương B27):

=SUMIFS(R2C5:R21C5,R2C1:R21C1,RC1,R2C2:R21C2,R26C)

R2C5:R21C5: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 5 đến ô dòng 21 cột 5 (tương đương $E$2:$E$21)
R2C1:R21C1: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 1 đến ô dòng 21 cột 1 (tương đương $A$2:$A$21)
RC1: Địa chỉ hỗn hợp của ô có dòng cùng dòng ô chứa công thức và cột 1 (tương đương $A27)
R2C2:R21C2: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 2 đến ô dòng 21 cột 2 (tương đương $B$2:$B$21)
R26C: Địa chỉ hỗn hợp của ô có dòng 26 và cùng cột với ô chứa công thức (tương đương B$26)

Tham chiếu R1C1 và Macro

Khi ghi macro, Excel mặc định sẽ sinh ra các tham chiếu dưới dạng R1C1 khi gán công thức vào ô.

Ví dụ: Ghi macro chèn công thức tính tổng cột “Thành tiền” của ví dụ trên thì Macro sinh ra như sau:

Sub Macro1()

    Range("E22").Select

    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"

    Range("E23").Select

End Sub

Ví dụ: Thủ tục chuyển đổi qua lại kiểu tham chiếu A1 và R1C1

Sub Shift_R1C1_A1()

    If Application.ReferenceStyle = xlR1C1 Then

        Application.ReferenceStyle = xlA1

    Else

        Application.ReferenceStyle = xlR1C1

    End If

End Sub

Tuỳ chọn để chuyển sang kiểu tham chiếu R1C1Excel 2003 về trước: Tools | Options | General | Settings | chọn R1C1 reference style.