Tham chiếu

Vị trí trên ribbon: Tab DVD Addin → Nhóm Công thức → ▾ của Tự động tổng

ID lệnh: btnAssignDirectReference

Cần đăng nhập license

Lệnh cần tài khoản DVDAddin còn hạn (Standard hoặc PRO). Trong thời gian dùng thử (Trial) dùng được đầy đủ.

Chức năng

Lệnh nối hai bảng dữ liệu bằng công thức tham chiếu trực tiếp dựa trên cột key chung. Add-in đối chiếu key của vùng nguồn với key của vùng đích, rồi ghi vào cột cuối của vùng nguồn một công thức trỏ thẳng tới ô giá trị tương ứng bên bảng đích.

Khác với VLOOKUP dò cả bảng, công thức tạo ra là tham chiếu tới đúng một ô — nhẹ hơn, dễ truy vết hơn và nhìn vào là biết con số lấy từ ô nào. Rất hợp khi kéo đơn giá từ sheet đơn giá sang bảng khối lượng, hoặc kéo khối lượng nghiệm thu sang bảng thanh toán giai đoạn.

Cách dùng

1. Chuẩn bị vùng nguồn: cột đầu là key tra cứu, cột cuối là nơi sẽ nhận công thức tham chiếu.
2. Chuẩn bị vùng đích có ít nhất 2 cột: cột đầu là key, cột cuối là giá trị cần tham chiếu.
3. Vào tab DVD Addin → nhóm Công thức → bấm ▾ của Tự động tổng → chọn Tham chiếu.
4. Cửa sổ công cụ mở ra ở thẻ Tham chiếu (nhóm thẻ Tính toán).
5. Chọn dải ô cho ô Vùng nguồn và ô Vùng đích (vùng đích có thể nằm ở sheet khác).
6. Bấm Gán. Hộp Xác nhận thay đổi dữ liệu đếm trước số công thức sẽ ghi và số ô sẽ bị xoá trắng — giữ tick sao lưu sheet rồi bấm Tiếp tục. Công thức tham chiếu được ghi vào cột cuối của vùng nguồn tại các hàng có key khớp. Tiến độ hiện ở thanh trạng thái của Excel; bấm Esc nếu muốn dừng giữa chừng.
7. Khi chạy xong, thông báo cho biết số công thức đã gán và số ô đã xóa trắng.

Video hướng dẫn

Tham chiếu · 6 phút 04 giây · 9 trường hợp · Tải video · File thực hành theo video

Các chương (14)

1. Giới thiệu
2. Mở lệnh
3. Cửa sổ Tham chiếu
4. 1 · Đơn giá ở sheet khác
5. 2 · Bảng giá cùng sheet
6. 3 · Mã trùng trong bảng đích
7. 4 · Cách so khớp mã
8. 5 · Dòng không có mã, ô gộp
9. 6 · Bảng đang lọc
10. 7 · Hoàn tác bằng Ctrl+Z
11. 8 · Bảng đích ở file khác
12. 9 · Khi chọn sai vùng
13. Mẹo
14. Kết thúc

Hướng dẫn bằng hình

Thực hành

Tải file mẫu của lệnh này — dữ liệu y như hình dưới (sheet KHOI LUONG), mở ra là làm theo được ngay từng bước.

Bước 1. Chuẩn bị vùng nguồn (1): cột đầu là mã hiệu (key), cột cuối là nơi nhận công thức đơn giá. Vùng đích là bảng đơn giá trên sheet DON GIA, cột A là mã hiệu, cột D là đơn giá.

Bước 2. Trên ribbon, mở tab DVD Addin → nhóm Công thức → mở Tự động tổng → chọn Tham chiếu.

Bước 3. Cửa sổ công cụ Tính toán mở ở thẻ Tham chiếu. Ở ô Vùng nguồn (1) chọn B4:E8, ở ô Vùng đích (2) chọn bảng đơn giá A5:D10 trên sheet DON GIA (gõ hoặc bấm ⤴ để chọn), rồi bấm Gán (3).

Bước 4. Hộp xác nhận đếm trước số công thức sẽ ghi và số ô sẽ bị xoá trắng ở cột cuối vùng nguồn. Giữ tick sao lưu sheet (1) rồi bấm Tiếp tục (2); khi chạy, bấm Esc để dừng giữa chừng.

Bước 5. Mỗi hàng có mã khớp nhận một công thức trỏ thẳng tới ô đơn giá bên sheet DON GIA (1), ví dụ E6 = ='DON GIA'!D8. Mã AF.8112 (2) gõ thiếu một chữ số nên không khớp mã nào bên bảng đơn giá — ô E8 bị xoá trắng; sửa mã rồi chạy lại. Thanh trạng thái báo kết quả (3).

Ví dụ

Sheet DON GIA (vùng đích) có cột A là mã hiệu và cột D là đơn giá. Sheet KHOI LUONG (vùng nguồn) có cột B là mã hiệu và cột G để trống chờ đơn giá.

• Vùng nguồn = B10:G250 trên sheet KHOI LUONG
• Vùng đích = 'DON GIA'!A5:D400

Bấm Gán: dòng có mã AF.11220 ở B37 nhận vào G37 một công thức trỏ thẳng tới ô đơn giá của mã đó trên sheet DON GIA. Sau này chủ đầu tư duyệt lại giá, chỉ cần sửa sheet đơn giá là toàn bộ bảng khối lượng cập nhật theo.

Lưu ý

• Vùng đích bắt buộc có từ 2 cột trở lên; nếu chỉ chọn một cột, lệnh không xác định được đâu là key, đâu là giá trị.
• Vị trí quy ước là cố định: cột đầu tiên của mỗi vùng luôn là key, cột cuối cùng là nơi ghi / nơi lấy giá trị. Nếu bảng của bạn xếp khác, hãy chọn vùng sao cho đúng hai cột biên này.
• Nội dung cũ ở cột cuối của vùng nguồn bị ghi đè. Nếu giữ tick sao lưu trong hộp xác nhận, bấm Ctrl + Z ngay sau lệnh để đưa sheet nguồn về như trước; xem Khôi phục dữ liệu.
• Key không tìm thấy bên vùng đích thì hàng đó không được gán công thức và ô ở cột cuối bị xóa trắng, kể cả ô bạn tự gõ tay — nên rà lại chính tả mã hiệu, khoảng trắng thừa trước khi chạy.
• Nếu một key xuất hiện nhiều lần trong vùng đích, công thức luôn trỏ tới hàng đầu tiên có key đó.
• Bấm Esc để dừng: các hàng đã xử lý giữ kết quả, phần còn lại giữ nguyên như cũ.
• Công thức tham chiếu tới ô cụ thể: nếu sau này bạn chèn/xóa dòng trong bảng đích, Excel vẫn dịch tham chiếu theo, nhưng sắp xếp lại bảng đích có thể làm lệch ý nghĩa của key.
• Nút chọn vùng ⤴ mở hộp chọn vùng không khoá Excel: cửa sổ này tạm tránh khỏi màn hình, trong lúc chọn vẫn cuộn, đổi sheet, đổi sổ được. Bấm OK hoặc Enter trên sheet để điền địa chỉ vào ô; Hủy hoặc Esc thì ô giữ nguyên. Ô đang có địa chỉ thì hộp điền sẵn địa chỉ ấy.

Liên quan

• Truy vết Lookup — kiểm tra ngược lại xem một công thức dò tìm đang lấy giá trị từ ô nào.
• Tự động tổng — thẻ cùng cửa sổ, dựng dòng SUBTOTAL cho bảng sau khi đã có đơn giá.
• Khóa tham chiếu — thêm dấu $ để cố định vùng tra cứu trước khi sao chép công thức.