Sửa lỗi 'Named range does not exist' trong công thức Google Sheets

Lỗi Gặp Phải

Bạn mở một bảng tính và một ô chứa công thức sáng lên màu đỏ:

Named range 'MyRange' does not exist

Đôi khi lỗi hiển thị dưới dạng #REF!, và khi di chuột vào sẽ thấy thông báo về named range. Dù hiển thị theo cách nào, nguyên nhân đều giống nhau: một công thức như =SUM(MyRange) hoặc =VLOOKUP(A1, MyRange, 2, FALSE) đang tham chiếu đến một named range mà Sheets không tìm thấy — vì nó đã bị xóa, đổi tên, hoặc chưa bao giờ được định nghĩa trong bảng tính này.

Nguyên Nhân

Google Sheets lưu trữ named range ở cấp độ bảng tính, không phải bên trong công thức. Mỗi lần công thức chạy, Sheets tra cứu tên đó và phân giải thành một vùng ô. Nếu không tìm thấy tên, bạn sẽ gặp lỗi này. Các nguyên nhân phổ biến:

Ai đó đã xóa named range từ Data → Named ranges
Vùng dữ liệu đã được đổi tên, nhưng công thức vẫn dùng tên cũ
Bạn sao chép công thức từ bảng tính khác — named range không đi kèm theo
Một Apps Script đã xóa hoặc tạo lại với tên khác
Bạn nhập một template và các named range không được đưa vào

Cách Sửa Nhanh: Tạo Lại Named Range

Nếu bạn biết vùng ô cần bao gồm, thao tác này chỉ mất khoảng 30 giây.

Mở bảng tính
Vào Data → Named ranges (hoặc Data → Named ranges and protected ranges trong các phiên bản mới hơn)
Nhấp Add a range
Nhập đúng tên mà công thức đang sử dụng — ở đây là MyRange
Đặt vùng dữ liệu (ví dụ: Sheet1!A2:A100)
Nhấp Done

Công thức sẽ phân giải ngay lập tức. Không cần chỉnh sửa bản thân công thức.

Nếu Bạn Không Biết Vùng Dữ Liệu Đúng

Lịch sử phiên bản là lựa chọn tốt nhất. Vào File → Version history → See version history và cuộn về thời điểm trước khi vùng bị xóa. Mở bảng named ranges trong phiên bản cũ đó, ghi lại tham chiếu ô, rồi quay lại phiên bản hiện tại và tạo lại thủ công.

Lưu ý: việc xóa named range không hiển thị là "named range removed" trong lịch sử. Chúng xuất hiện dưới dạng lỗi công thức. Rất dễ bỏ sót. Nếu bảng tính được chia sẻ, hãy hỏi người chỉnh sửa gần nhất — họ có thể đã đổi tên mà không biết rằng điều đó làm hỏng các công thức liên quan.

Cách Phòng Ngừa Lâu Dài: Bảo Vệ Named Range Khỏi Bị Xóa Nhầm

Sheets không có tính năng khóa named range sẵn có. Nhưng có hai cách thực tế để ngăn sự cố tái diễn.

Cách 1: Bảo Vệ Vùng Nguồn

Khóa các ô mà named range trỏ đến sẽ không ngăn ai đó xóa tên đó. Nhưng nó báo hiệu rằng các ô này quan trọng — và ngăn chỉnh sửa nhầm dữ liệu gốc. Vào Data → Protected sheets and ranges, thêm vùng dữ liệu (ví dụ: Sheet1!A2:A100), và giới hạn quyền chỉnh sửa cho bản thân hoặc những người cụ thể.

Cách 2: Apps Script Kiểm Tra Khi Mở File

Thêm một script kiểm tra các named range bắt buộc mỗi khi file được mở. Nếu có tên nào bị thiếu, nó sẽ hiển thị cảnh báo trước khi ai đó gặp phải công thức bị lỗi.

// Tools → Apps Script → dán đoạn này → lưu → chạy onOpen một lần để cấp quyền
function onOpen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const required = ['MyRange', 'TaxRate', 'ProductList']; // các named range của bạn
  const existing = ss.getNamedRanges().map(nr => nr.getName());

  const missing = required.filter(name => !existing.includes(name));

  if (missing.length > 0) {
    SpreadsheetApp.getUi().alert(
      'Missing Named Ranges',
      'These named ranges are missing and may break formulas:\n\n' + missing.join('\n'),
      SpreadsheetApp.getUi().ButtonSet.OK
    );
  }
}

Mỗi người chỉnh sửa mở file đều nhận được cảnh báo. Các công thức bị hỏng sẽ không còn bị bỏ qua nhiều ngày nữa.

Cách 3: Apps Script Tự Động Tạo Lại Named Range Bị Thiếu

Muốn tự động phục hồi hoàn toàn? Lưu định nghĩa vùng dữ liệu trong script. Nếu một vùng bị thiếu khi mở, nó sẽ được tạo lại tự động.

function ensureNamedRanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const definitions = [
    { name: 'MyRange', a1: 'Sheet1!A2:A100' },
    { name: 'TaxRate', a1: 'Sheet1!B1' },
  ];

  const existing = ss.getNamedRanges().map(nr => nr.getName());

  definitions.forEach(def => {
    if (!existing.includes(def.name)) {
      const range = ss.getRange(def.a1);
      ss.setNamedRange(def.name, range);
      console.log(`Recreated named range: ${def.name}`);
    }
  });
}

Gắn hàm này vào onOpen và các vùng dữ liệu sẽ tự phục hồi nếu bị xóa.

Khi Công Thức Đến Từ Bảng Tính Khác

Named range chỉ có phạm vi trong một bảng tính duy nhất. Không có ngoại lệ. Sao chép công thức từ Bảng tính A sang Bảng tính B, công thức vẫn ghi MyRange — nhưng Bảng tính B chưa bao giờ biết đến tên đó.

Cách sửa: mở bảng tính gốc, vào Data → Named ranges, ghi lại tham chiếu ô (ví dụ: A2:A100), rồi định nghĩa named range tương tự trong bảng tính đích.

Kiểm Tra Sau Khi Sửa

Nhấp vào ô đang hiển thị lỗi — xác nhận rằng nó hiện đang hiển thị giá trị, không phải #REF!
Mở Data → Named ranges và xác nhận MyRange xuất hiện trong danh sách
Nhấp vào mục named range — Sheets sẽ tô sáng các ô nguồn, để bạn xác nhận chúng trỏ đến đúng dữ liệu
Nếu dùng Apps Script, chạy ensureNamedRanges() thủ công từ trình soạn thảo và kiểm tra nhật ký thực thi để xem các vùng nào đã được tạo lại

Tóm Tắt Nhanh

Nguyên nhân lỗi: Named range được tham chiếu trong công thức đã bị xóa hoặc đổi tên
Sửa ngay: Data → Named ranges → Add a range với đúng tên
Phòng ngừa: Apps Script kiểm tra khi mở, hoặc bảo vệ các ô nguồn
Sao chép liên bảng tính: Named range không được chuyển theo — phải định nghĩa lại ở bảng tính đích