Elettracompany.com

Компьютерный справочник
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ref в excel

Исправление ошибки #ССЫЛКА!

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Ошибка #REF! показывает, что формула ссылается на недопустимую ячейку. Это чаще всего происходит, если ячейки, на которые ссылаются формулы, удаляются или вставляются.

Пример ошибки #ССЫЛКА! из-за удаления столбца

В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).

Если вы удалили столбец B, C или D, это приведет к #REF! ошибку. В этом случае мы будем удалять столбец C (2007 Sales), и формула теперь читает = Sum (B2, #REF!, C2). При использовании явных ссылок на ячейки, разделенных запятыми (в которых вы указываете на каждую ячейку по отдельности) и удалении строки или столбца, на которые указывает ссылка, приложение Excel не сможет решить эту проблему, поэтому возвращает #REF! ошибку. Это основная причина, по которой использование явных ссылок на ячейки в функциях не рекомендуется.

Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.

Измените формулу таким образом, чтобы она использовала ссылку на диапазон, а не отдельные ячейки, например = Sum (B2: D2). Теперь вы можете удалить любой столбец в диапазоне сумм, и Excel автоматически настроит формулу. Вы также можете использовать = Sum (B2: B5) для суммирования строк.

Пример функции ВПР с неправильными ссылками на диапазоны

В следующем примере функция =ВПР(A8;A2:D5;5;ЛОЖЬ) вернет ошибку #ССЫЛКА!, так как она выполняет поиск возвращаемого значения в столбце 5, но указан диапазон A:D, который содержит только 4 столбца.

Измените диапазон, чтобы он был больше, или уменьшите значение подстановки столбца в соответствии с диапазоном ссылок. = ВПР (A8; a2: «5; ложь») является допустимым диапазоном ссылок, как если бы был = ВПР (A8, a2: Д5, 4, ложь).

Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец

В этом примере формула =ИНДЕКС(B2:E5;5;5) возвращает ошибку #ССЫЛКА!, так как диапазон в ней содержит 4 строки и 4 столбца, а запрашивается значение ячейки в пятой строке и пятом столбце.

Настройте ссылки на строки или столбцы так, чтобы они находящегося в диапазоне подстановки ИНДЕКСов. = Индекс (B2: ячейка, 4, 4) возвращает допустимый результат.

Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ

В следующем примере функция INDIRECT пытается сослаться на закрытую книгу, что приводит к ошибке #ССЫЛКА!.

Откройте книгу, на которую указывает ссылка. Эта же ошибка возникает, если вы указываете на закрытую книгу с динамическим массивом.

Проблемы OLE

Если вы использовали ссылку OLE и она возвращает ошибку #ССЫЛКА!, запустите программу, которая вызывает ссылку.

Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.

Проблемы DDE

Если вы использовали раздел динамического обмена данными (DDE), возвращающий #REF! ошибка, сначала убедитесь, что вы ссылаетесь на правильную тему. Если вы по-прежнему получаете #REF! ошибка Проверьте Параметры центра управления безопасностью для внешнего содержимого, как описано в блоке блокирование или разблокирование внешнего содержимого документов Office.

Примечание. Динамический обмен данными (DDE)— это установленный протокол для обмена данными между программами Microsoft Windows.

Проблемы с макросами

Если макрос вводит в лист функцию, которая ссылается на ячейку над функцией, а ячейка, содержащая эту функцию, находится в строке 1, функция возвращает #REF! из-за отсутствия ячеек над строкой 1. Проверьте функцию, указывающую на то, что аргумент ссылается на недействительную ячейку или диапазон ячеек. Для этого может потребоваться изменение макроса в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Ошибки в формулах Excel

В этой статье мы расскажем о том, как справляться с некоторыми наиболее распространёнными ошибками формул в Excel.

Ошибка #####

Появление в ячейке такого кода ошибки означает, что столбец недостаточно широк, чтобы отобразить значение полностью.

Наведите указатель мыши на правую границу столбца рядом с его заголовком (в нашем примере это столбец A), чтобы указатель принял вид, как на рисунке ниже. Нажмите левую кнопку мыши и перетащите границу столбца до нужной ширины.

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

Ошибка #ИМЯ?

Ошибка #ИМЯ? (#NAME?) возникает в том случае, когда Excel не может распознать текст в формуле (например, из-за опечатки).

Просто исправьте SU на SUM.

Ошибка #ЗНАЧ!

Excel показывает сообщение об ошибке #ЗНАЧ! (#VALUE!) в том случае, когда для формулы введён аргумент не подходящего типа.

Читать еще:  Excel grade 5 workbook

a) Измените значение в ячейке A3.

b) Используйте функцию, которая игнорирует ячейки, содержащие текст.

Ошибка #ДЕЛ/0!

Сообщение об ошибке #ДЕЛ/0! (#DIV/0!) появляется при попытке деления числа на ноль или на пустую ячейку.

a) Изменить значение в ячейке A2 на любое число, не равное нулю.

b) Предотвратите возникновение ошибки при помощи логической функции ЕСЛИ (IF).

Пояснение: Если ячейка A2 будет равна нулю, то значением ячейки A3 будет пустая строка. Если нет – то в ячейке A3 будет вычислен результат формулы =A1/A2.

Ошибка #ССЫЛКА!

Сообщение об ошибке #ССЫЛКА! (#REF!) говорит о том, что формула ссылается на ячейку, которая не существует.

  1. В ячейке C1 содержатся ссылки на ячейки A1 и B1.
  2. Удаляем столбец B. Для этого кликаем по заголовку столбца правой кнопкой и в контекстном меню нажимаем Удалить (Delete).
  3. Выделите ячейку B1. Ссылка на ячейку B1 в формуле превратилась в ссылку на несуществующую ячейку.
  4. Чтобы исправить эту ошибку, нужно либо удалить несуществующую ссылку в формуле, либо отменить действие, кликнув по иконке Отменить (Undo) на панели быстрого доступа (или нажать Ctrl+Z).

#REF Excel

What are #REF Excel Errors?

An #REF error (the “ref” stands for reference) is the message Excel displays when a formula references a cell that no longer exists, usually caused by deleting cells that a formula is referring to. Every good financial analyst The Analyst Trifecta® Guide The ultimate guide on how to be a world-class financial analyst. Do you want to be a world-class financial analyst? Are you looking to follow industry-leading best practices and stand out from the crowd? Our process, called The Analyst Trifecta® consists of analytics, presentation & soft skills knows how to find and fix #REF Excel errors, which we will explain in detail below.

#REF Excel Error Example

Below is an example of how you may inadvertently create a #REF Excel error. To learn more, watch CFI’s free Excel course and follow the video instruction.

The first image shows three numbers being added together (5, 54, and 16). In column D we show the formula adding cells D3, D4, and D5 together, to get 75.

The next image shows what happens if row 4 is deleted. When the entire row is deleted, the formula that referenced cell D4 is no longer able to locate that cell, and it produces a #REF error in the spreadsheet.

Learn more about #REF Excel errors in our free Excel training course.

How to Find #REF Excel Errors

Method #1

A fast way to find all #REF Excel errors is to press F5 (Go To) and then click on Special, which for short is referred to as Go To Special Go To Special Go To Special in Excel is an important function for financial modeling spreadsheets. The F5 key opens Go To, select Special Shortcuts for Excel formulas, allowing you to quickly select all cells that meet certain criteria. .

When the Go To Special menu appears, select Formulas, and then check only the box that says Errors. Click OK and that will automatically take you to every cell that has a #REF! error in it.

Method #2

Another method is to press Ctrl + F (known as the Excel find function Find and Replace in Excel Learn how to search in Excel — this step by step tutorial will teach how to Find and Replace in Excel spreadsheets using Ctrl + F shortcut. Excel find and replace allows you to quickly search all cells and formulas in a spreadsheet for all instances that match your search criteria. This guide will cover how to ) and then type “#REF!” in the Find field and click Find All. This will highlight every cell with the error in it.

How to Fix #REF Excel Errors

The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace. Type “#REF!” in the Find field and leave the Replace field empty, then press Replace All. This will remove any #REF Excel errors from formulas and thus fix the problem.

As the above screenshot shows, after typing the error message you are experiencing in the “Find what” filed, leave “Replace with” blank and press “Replace All” in the bottom left corner. This will cause the reference errors to be deleted from your formulas.

More Excel Resources from CFI

We hope this has been a helpful CFI guide on Excel errors. If you want to be a master of Excel, please check out all our Excel Resources Excel Resources Learn Excel online with 100’s of free Excel tutorials, resources, guides & cheat sheets! CFI’s resources are the best way to learn Excel on your own terms. to learn the most important formulas, functions, and shortcuts.

Читать еще:  Синусоида в excel

Additional CFI guides and resources you may find useful include:

  • Excel for Dummies Excel for Beginners This Excel for beginners guide teaches you everything you need to know about Excel spreadsheets and formulas to perform financial analysis. Watch the Video and learn everything a beginner needs to know from what is Excel, to why do we use, and what are the most important keyboard shortcuts, functions, and formulas
  • Excel modeling best practices Excel Modeling Best Practices The following excel modeling best practices allow the user to provide the cleanest and most user-friendly modeling experience. Microsoft Excel is an extremely robust tool. Learning to become an Excel power user is almost mandatory for those in the fields of investment banking, corporate finance, and private equity.
  • Excel formulas cheat sheet Excel Waterfall Chart Template Download our free Excel Waterfall Chart Template .xls file and follow our instructions to create your own cash flow waterfall chart in Excel. If you’re working in Excel 2013 or earlier versions, please follow the instructions below as a workaround to build your own chart using the stacked column chart feature
  • Financial modeling guide Free Financial Modeling Guide This financial modeling guide covers Excel tips and best practices on assumptions, drivers, forecasting, linking the three statements, DCF analysis, more
  • All Excel articles Knowledge CFI self-study guides are a great way to improve technical knowledge of finance, accounting, financial modeling, valuation, trading, economics, and more.

Формула Excel, связанная с данными, превращается в #REF при открытии книги

Это случилось со мной несколько раз (но не всегда). Это очень неприятно, и я понятия не имею, почему он это делает.

A.xls содержит формулу, которая ссылается на B.xls Sheet1. Я работаю над A.xls. Я решаю открыть B.xls. Как только я это сделаю — все ссылки на Sheet1 превращаются в #REF.

и, естественно, все значения, которые были ранее корректны, теперь возвращают #REF.

Это абсолютно расстраивает, так как мне не удается отменить его, не открывая последнюю сохраненную версию A.xls. Найти и заменить не идеально, потому что A.xls относится к нескольким различным листам B.xls — и все они теперь #REF.

Любые идеи, почему? 🙁 Запуск Excel 2010.

9 ответов

Один из способов избежать проблемы:
Используйте INDIRECT() в текстовых ссылках имен файлов и листов.

Пример:
Сделать Z1 be ‘C:..[B.xls]!Sheet1’ (с помощью котировок «Не проиндексирован»)
Затем используйте =INDIRECT(Z1&»!A1″) , где вам нужно ссылаться на ячейку A1 на этом листе.

Функция INDIRECT работает только при открытии вторичного файла Excel. После закрытия все эти ссылки INDIRECT становятся #REF.

У меня такая же проблема. my is office 2010. файл excel хранился в share drive (NAS).

Решение: Перейдите в File> Вариант> Центр доверия. В правой части щелкните Параметры центра доверия. В окне «Центр доверия» с левой стороны нажмите «Защищенный просмотр», затем с правой стороны откройте «Включить защищенный просмотр» для файлов, происходящих из Интернета.

Теперь мой #REF! проблема решена.

Чтобы помочь с комментарием Джерри. Когда вы закроете A.xls, он будет помнить значения в нем из формулы. Если вы тогда меняете B.xls в любом случае, например, имя книги или имя листа, тогда, когда A.xls пытается обновить себя новыми данными, она не может быть найдена, потому что имя ссылки изменилось.

Скажите, что ваша формула имеет значение = ‘[B.xls] Sheet3’! $ B $ 1588, если вы измените имя B.xls после закрытия A.xls на «Data Saved 08.09.2013», затем, когда вы откроете A. xls он будет иметь значения из B.xls. Как только вы откроете Data Saved 08.09.2013, A.xls поймет, что данные больше не существуют и теряют его.

Работа для этого — убедиться, что вы обновляете ссылки в формуле. Это должно решить все.

Решение может состоять в том, чтобы открыть файл, в котором вы указали ссылки, а затем перейти к Data > Edit Links > Open Source . Это откроет исходный код без ошибки #ref .

Другим вариантом является сохранить две версии с ссылками и без ссылок . Версия без ссылок может быть выполнена с помощью Data > Edit Links > Break Link .

Я думаю, что это какая-то ошибка в excel, которая делает имя листа #ref .

Проблема заключается в центре доверия. Если вы работаете с сетевыми реестрами, вы должны проверить «разрешить подключения к доверенным местоположениям». Это позволяет избежать потери ссылки.

Кажется, что ссылки изменены на #REF! потому что целевой таблице (B.xls) не доверяют. Перейдя в FILE-> Options-> Trust Center-> Trust Center Settings-Trusted Locations и добавив папку, содержащую B.xls, я смог остановить эту проблему. Предположительно, вы можете добавить B.xls в «Trusted Documents» и получить тот же результат, но я не выполнил этот тест.

Читать еще:  Отображение листов в excel 2020

Изменить формат ячейки на числовой может быть из текста /других, перечисленных в списке форматов ячеек. Это можно сделать, щелкнув правой кнопкой мыши на ячейке или выбранных ячейках в Excel.

Подбирая полезные указатели из приведенных выше, это работало на меня для файлов с реферирующими ошибками при открытии связанного файла в нашей сети:

Параметры> Центр доверия> Настройки центра доверия> Надежные местоположения> Установите флажок рядом с надписью «Разрешить надежные местоположения в моей сети (не рекомендуется).

Исправлены ошибки доверия!

Решение состоит в том, чтобы перейти к файлу> Опции> Формулы и снимите флажок Данные, введенные в таблицу, недействительны .

Формула Excel для ссылки «ячейка слева»

Я пытаюсь сделать условное форматирование, чтобы цвет ячейки менялся, если значение отличается от значения в ячейке слева от него (каждый столбец-месяц, в каждой строке-расходы на определенный объект. Я хочу легко отслеживать изменения цен в течение нескольких месяцев.)

Я могу сделать это за ячейку и формат-перетащите его, но я хотел бы, чтобы общая формула применялась ко всему листу.

11 ответов:

самая короткая наиболее совместимая версия:

«RC[-1]» означает один столбец слева. «R[1]C[-1]» находится внизу слева.

второй параметр 0 означает, что первый параметр интерпретируется с использованием нотации R1C1.

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

самый простой вариант:

имеет тот недостаток, что вам нужно включить нотацию R1C1 с помощью опций, которая не работает, когда другие люди должны использовать excel.

при создании условного форматирования, установите диапазон, к которому он применяется к тому, что вы хотите (весь лист), а затем введите относительные формула (удалить $ знаки), как если бы вы только форматировали верхний левый угол.

Excel правильно применит форматирование к остальным ячейкам соответственно.

в этом примере, начиная с B1,левый ячейки А1. Просто используйте это—нет расширенной формулы требуемый.

если вы ищете что-то более продвинутое, вы можете поиграть с column() , row() и indirect(. ) .

Если вы измените ссылку на ячейку, чтобы использовать нотацию R1C1 (Инструменты|Параметры, вкладка Общие), то вы можете использовать простую нотацию и вставить ее в любую ячейку.

теперь ваша формула просто:

заполните ячейку A1, по следующей формуле :

затем autoextend направо, Вы получаете

если смещение находится вне диапазона доступной ячейки, вы получаете #REF! ошибка.

Надеюсь, вам понравится.

вместо того чтобы писать очень долго:

вы можете просто написать:

таким образом, например, вы можете написать в ячейку B2:

для ссылки на ячейку B1

еще спасибо Джейсон Янг!! Я бы никогда не придумал это решение без вашего ответа!

при создании Пользовательская Функция, я узнал, что другие ответы, связанные с функциями OFFSET и INDIRECT не может быть применен.

вместо этого, вы должны использовать Application.Caller для ссылки на ячейку, в которой была использована пользовательская функция (UDF). На втором шаге вы преобразуете индекс столбца в имя соответствующего столбца.
Наконец, вы можете ссылаться на левую ячейку с помощью активного листа

вы можете использовать сценарий VBA, который изменяет Условное форматирование выделения (возможно, вам придется соответствующим образом настроить условие и форматирование):

OFFSET возвращает ссылку относительно текущей ссылки, поэтому, если косвенная возвращает правильную ссылку, она вам не нужна.

я наткнулся на эту тему, потому что я хотел всегда ссылаться на «ячейку слева», но принципиально энергонезависимым способом (без смещения, косвенных и подобных катастроф). Глядя в сеть вверх и вниз, нет ответов. (Этот поток на самом деле не дает ответа.) После некоторой возни я наткнулся на самый удивительный метод, которым мне нравится делиться с этим сообществом:

предположим, что начальное значение 100 в E6. Предположим, я ввожу дельту к этому значению в F5, скажем 5. Затем мы рассчитаем значение продолжения (105) в F6 = E6+F5. Если вы хотите добавить еще один шаг, легко: просто скопируйте столбец F в столбец G и введите новую дельту в G5.

Это то, что мы делаем, периодически. Каждый столбец имеет дату, и эти даты должны быть в хронологическом порядке (чтобы помочь с совпадением и т. д.). Время от времени случается так, что мы забываем сделать шаг. Теперь предположим, что вы хотите вставить столбец между F и G (чтобы наверстать упущенное) и скопировать F в новый G (для повторного заполнения формулы продолжения). Это не что иное, как полная катастрофа. Попробуйте — H6 теперь скажет =F6+H5, а не (как нам абсолютно нужно) =G6+H5. (Новый G6 будет правильным.)

Это не должно работать, верно? Круговая ссылка, Понятно! Попробовать его и быть поражены. Excel, похоже, понимает, что, хотя диапазон индекса охватывает ячейку, которую мы пересчитываем, сама эта ячейка не адресуется индексом и, следовательно, не создает циклическую ссылку.

Так что теперь я дома и сухой. Вставьте столбец между F и G, и мы получим именно то, что нам нужно: значение продолжения в старом H будет ссылаться на значение продолжения, которое мы вставили в новый G.

Ссылка на основную публикацию
Adblock
detector