Ошибка ref гугл таблицы

This tutorial demonstrates how to find, interpret, and fix #REF! errors in Excel and Google Sheets.

Locate #REF! Cells in Excel

An #REF! error in Excel is a message that is displayed when a formula refers to an invalid cell. Usually, the cell is not valid because it was deleted or pasted over.

Say that, in cell C2, you have the formula: “=B2+B3+B4”.

Suppose you deleted Row 3, causing the #REF! error.

Note: If you are referencing the cells as a continuous range (e.g., “=SUM(B2:B4)”), you won’t get a #REF! error after deleting a row or a column. Also, if you are specifically referencing cells and want to delete some of them, just delete the cell values. Don’t delete the whole row, and you won’t produce the #REF! error.

To locate the #REF! cells on your worksheet just follow these steps:

1. In the Ribbon, go to Home > Find & Select > Go To Special.

1. In the Go To Special window, select Formulas and uncheck all boxes except Errors. Click OK.

As a result, all the cells with the #REF! error message are selected.

Note: It’s also possible to cause a #REF! error by deleting a column or row, by having an incorrect row or a column reference in an INDEX Function, or by using incorrect range references in a VLOOKUP Function. Issues with OLE (Object Linking and Embedding), DDE (Dynamic Data Exchange), or macros can also cause the #REF! error.

Fix #REF! Errors

The best way to fix the problem above is to use Find & Replace feature in Excel. Replacing the #REF! with blanks deletes the reference errors from your formulas.

1. In the Ribbon, go to Home > Find & Select > Replace (or use the CTRL + F shortcut to open the Find & Replace window).

1. After that the Find & Replace window will open. (1) In the Find what: box type #REF! and (2) Replace with: box leave empty. Then (3) press Replace All and (4) click Close.

1. The pop-up information will inform you how many replacements were made. To close it, click OK.

As a result, the #REF! error is fixed.

Other Potential Reasons For #REF! Error and Solutions

VLOOKUP With Incorrect Range Reference

In this example, “=VLOOKUP(F2,$B$2:$C$6,3,FALSE)” returns the #REF! error, because it’s looking to return the value from the 3rd column, but the referenced range is $B$2:$C$6, which has only 2 columns. The solution is to correct the column lookup value to match the reference range (in this example: “=VLOOKUP(F2,$B$2:$C$6,2,FALSE)”).

Another way to get rid of the #REF! error is to use the IFERROR Function. If there is an error in the formula the text from the function is displayed instead of #REF! (error in this example).

INDEX With Incorrect Row or Column Reference

The #REF! error is displayed in this example (“=INDEX(B2:C6,3,3)”), because it’s looking to return the value from the 3rd row, 3rd column but the INDEX range is 5 rows by 2 columns. To correct this, choose a valid reference column (“=INDEX(B2:C6,3,2)”).

As a result, the #REF! error disappears, and cell E2 shows the correct value.

You can also use the IFERROR Function to get rid of #REF! error message.

Fix #REF! Errors in Google Sheet

#REF! errors in Google Sheets work very similar to those in Excel. In the case of a deleted cell causing the error, you can fix it with Find and replace.

Say that, in cell C6, you have the formula: “=SUM(C2+C3+C4+C5+C6)”.

If you delete Row 3, for example, that causes a #REF! error.

To fix #REF! error in Google Sheets, follow these steps:

1. In the Menu, go to Edit and choose Find & Replace.

1. The Find and replace window will open. (1) In the Find box enter #REF! and (2) the Replace box leave blank. After that, (3) click Find and (4) Replace All. (5) Press Done.

As a result, the error is fixed.

Не существует регулярного выражения для поиска формулы определенного типа на листе с точки зрения макроса. Вместо этого, если вы хотите сохранить определенные формулы, но не другие, вам нужно будет проверить каждую ячейку с формулой и определить, является ли она той, которую вы хотите сериализовать, по сравнению с той, которую вы хотите сохранить. Затем, если это тот, который вы хотите сериализовать, прочтите значения из исходного листа и запишите их в копию:

function copyFormulasAndSomeValues() {
  var source = SpreadsheetApp.getActive(). getActiveSheet();
  var remote = SpreadsheetApp.openById("some ID");
  // Copy all static values and formulas (and charts, formatting, etc.)
  var dest = source.copyTo(remote);

  // To efficiently read and write values when we know nothing
  // about the structure of invalid formulas, we will use a RangeList.
  // If structure exists (e.g. if a1 is invalid we know `A2:A100` will be too), we can combine batch
  // get/set methods with the 4 parameter getRange() method.
  var toRead = [];

  // Read in all formulas on the created sheet (no formula appears as "" e.g. nullstring).
  var formulas = copy.getDataRange().getFormulas();
  formulas.forEach(function (row, r) {
    row.forEach(function (formula, c, rowData) {
      if (!formula) return;
      // Check if this is a formula we want to replace.
      if (/* your tests here */) {
        // Store R1C1 notation for later reading.
        toRead.push(String(r + 1) + String(c + 1));
      }
    }); // End column value checking
  }); // End sheet row checking

  // If all formulas checked out, quit.
  if (toRead.length === 0)
    return;

  // Read desired values into a RangeList.
  var rangeList = source.getRangeList(toRead);
  var toWrite = rangeList.getRanges().map(function (range) {
    return range.getValue();
  });

  // Write to the same regions in the destination.
  dest.getRangeList(toRead).getRanges().forEach(function (range, i) {
    range.setValue(toWrite[i]);
  });
}

Если вы знаете структуру / расположение того, что будет действительным / недействительным, вы сможете улучшить этот метод, читая пакетно, записывая статические значения.

2 ответа

Лучший ответ

setRange(range)