невозможно создать или использовать ссылку на диапазон данных так как она слишком сложна excel

Создание внешней ссылки (ссылки) на диапазон ячеев в другой книге

Вы можете ссылаться на содержимое ячеек в другой книге, создав формулу внешней ссылки. Внешняя ссылка (также называемая ссылкой) — это ссылка на ячейку или диапазон на книге Excel или ссылка на определенное имя в другой книге.

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

Вы выберите ячейку или ячейки, для которых вы хотите создать внешнюю ссылку.

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

Вы выберите ячейку или ячейки, на которые вы хотите связаться, и нажмите ввод.

Примечание: Если вы выберете несколько ячеек, например =[SourceWorkbook.xlsx]Лист1!$A$1:$A$10 и у вас есть текущая версия Microsoft 365,то можно просто нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей, нажав CTRL+SHIFT+ВВОД. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Excel возвращается в 9-ю книгу и отображаются значения из нее.

Если закрыть книгу-источник, Excel автоматически в формулу будет автоматически включен путь к файлу:

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

Вы выберите ячейку или ячейки, для которых вы хотите создать внешнюю ссылку.

Введите = (знак равенства).

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

Нажмите F3, выберите имя, на которое будет ссылаться ссылка, и нажмитеввод.

Примечание: Если именуемая ячейка ссылается на несколько ячеек и у вас есть текущая версия Microsoft 365,можно просто нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей, нажав CTRL+SHIFT+ВВОД. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Excel вы вернетесь к книге назначения и отобразит значения из именоваемого диапазона в книге-источнике.

Откройте 9-ю книгу и книгу-источник.

В книге назначения перейдите к пункту Формулы > определенные имена > определить имя.

В диалоговом окне Новое имя в поле Имя введите имя диапазона.

В поле Ссылки удалите содержимое, а затем храните курсор в поле.

Если вы хотите использовать имя функции, введите имя функции и наведите курсор в то место, где вы хотите использовать внешнюю ссылку. Например, введите =СУММ()и расположить курсор между скобами.

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

Выберите ячейку или диапазон ячеек, которые вы хотите связать, и нажмите кнопку ОК.

определенные имена > определить имя > имя.» loading=»lazy»>

Внешние ссылки особенно полезны, если не целесообразно хранить большие модели в одной книге.

Объединение данных из нескольких книг Вы можете связать книги нескольких пользователей или отделов, а затем интегрировать их в итоговую книгу. В этом случае при изменении исходных книг не придется вручную изменять итоговую книгу.

Создание различных представлений данных Вы можете ввести все данные в одну или несколько исходных книг, а затем создать книгу отчета, содержаную внешние ссылки только на данные, которые содержатся в них.

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

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

Когда источник открыт, внешняя ссылка включает имя книги в квадратных скобках ([ ]), за которым следуют имя таблицы, восклицательный пункт (!) и ячейки, от которых зависит формула. Например, следующая формула добавляет ячейки C10:C25 из книги с именем Budget.xls.

Если источник не открыт, внешняя ссылка включает весь путь.

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

В формулах, которые ссылаются на определенное имя в другой книге, используется имя книги, за которым следуют восклицательный пункт (!) и имя. Например, следующая формула добавляет ячейки из книги «Продажи» Budget.xlsx.

Вы выберите ячейку или ячейки, для которых вы хотите создать внешнюю ссылку.

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

Переключение на таблицу с ячейками, на которые вы хотите связаться.

Вы выберите ячейку или ячейки, на которые вы хотите связаться, и нажмите ввод.

Excel вернуться на исходный и отобразить значения из исходного.

Создание внешней ссылки на ячейки в разных книгах

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

В книге-источнике выберите ячейку или ячейки, которые нужно связать.

Нажмите CTRL+C или перейдите на главная> буфер обмена > Копировать.

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

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

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

Чтобы использовать ссылку в формуле, введите = перед ссылкой, выберите функцию, введите (и введите ) после ссылки.

Создание связи с книгой

Вы выберите ячейку или ячейки, для которых вы хотите создать внешнюю ссылку.

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

Переключение на таблицу с ячейками, на которые вы хотите связаться.

Вы выберите ячейку или ячейки, на которые вы хотите связаться, и нажмите ввод.

Excel вернуться на исходный и отобразить значения из исходного.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Исправление ошибки #REF! #BUSY!

Ошибка #ССЫЛКА! указывает на то, что формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.

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

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

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

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

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

В следующем примере =ВРОТ(A8;A2:D5;5;ЛОЖЬ) возвращает #REF! из-за того, что она ищет значение из столбца 5, но диапазон ссылок — A:D, который составляет всего 4 столбца.

Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).

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

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

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

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

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

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

Проблемы с OLE

Если вы использовали ссылку OLE, возвращая #REF! и запустите программу, в которую будет звонить ссылка.

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

Проблемы dDE

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

data_client

Как исправить ошибку #ССЫЛКА! в Excel

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

Вариант 1. Исправление формул

Вариант 2. Функция ЕСЛИОШИБКА

При помощи функции ЕСЛИОШИБКА можно обработать ошибки, которые возникают при написании формул, в том числе ошибку #ССЫЛКА!.

Синтаксис функции ЕСЛИОШИБКА следующий: первым аргументом идет функция, которая может вызвать ошибку, вторым аргументом функционал, который будет вызван, если ошибка произошла.

В данном случае мы оставили напоминание себе о том, что листы, при закрытой книге со сводом переименовывать нельзя. Это позволит быстро сориентироваться в ситуации и исправить формулы, как описано в варианте 1.

Спасибо, что прочитали статью. Надеюсь она была для вас полезной.

Источник

Заполнение пустых ячеек значениями из соседних ячеек

из

сделать

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :

Не снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):

Способ 2. Заполнение пустых ячеек макросом

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

Источник

Динамический диапазон с автоподстройкой размеров

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

Все это в сумме не даст вам скучать 😉

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.

Способ 2. Динамический именованный диапазон

Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:

Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(«я»;255) ;A2:A100))

Создаем именованный диапазон

Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.

Источник