Файл этой версии не может содержать формулы которые ссылаются на ячейки за пределами области
В формулах могут содержаться ссылки на ячейки из других рабочих листов, при этом листы могут находиться даже в другой рабочей книге.Для работы с такими ссылками Excel использует специальный вид записи.
1. Формат записи для ссылки на другой лист текущей книги имеет вид:
Имя листа . Адрес ячейки.
Пример формулы, использующей ссылку на ячейку другого листа =В3*Лист1.А1 (ячВ3 тек. Листа умножается на сод. ячейки А1 листа Лист1)
2. Формат записи для ссылки на ячейки другой рабочей книги:
[Полное имя книги] Имя листа . Адрес ячейки.
Пример формулы. =А2*‘С:\ Мои документы \[ Бюджет] Лист1’!А2.
Если же книга открыта, достаточно указать ее имя и лист
А2*‘[ Бюджет] Лист1’.А2.
До сих пор мы имели дело с формулами, использующие и рассчитывающих значения отдельных ячеек. А сейчас я познакомлю вас с еще одной интересной возможностью Ехcel – использование массивов в формулах.
Формулы массивов.
Массив-это набор элементов, которые могут обрабатываться как единая группа или каждый в отдельности.
В Ех массивы могут быть одно- или двухмерными. Одномерные массив –это группа ячеек, размещенные в одной строке (вектор-строка) или в одном столбце (вектор-столбец). Двухмерный массив размещается в нескольких строках и столбцах и представляет собой матрицу.
Рассмотрим пример. Требуется определить доход фирмы по месяцам года. При решении задачи нам поможет таблица.
Доход фирмы Империалпо месяцам 1-го полугодия 2007 года.
А | В | С | D |
Месяц | Расход | Приход | Доход |
Январь | |||
Февраль | |||
Март | |||
Апрель | |||
Май | |||
Июнь |
Прибегнем к нашим знаниям и для расчета дохода
1. Введем в ячейку D6 формулу =С6 – В6, затем
2. Скопируем формулу и получим 6 отдельных формул в ячейках столбца D.
А теперь поступим следующим образом.
1. Выделим ячейки Е5:Е10 – а это и есть массив - массив результата
2. В строку формул введем формулу=С6:С11- В6:В11, т.е из вектора-столбца Свычтем вектор-столбец В. А теперь сохраним формулу, но не Enter, как обычно, а комбинацией клавиш (Ctrl+ Shift+ Enter).
Обратите внимание –во все выделенные ячейки ввелась одна формула и мы получили сразу все 6-ть искомых значений.
В Excel есть тип формул, которые ссылаются на диапазон ячеек, а оперируют с ним как с одной ячейкой, такие формулы называются формулами массивов или табличными формулами.
Чтобы ввести формулу массива следует:
1. Выделить диапазон ячеек, в которые вводятся формулы,
2. В строку формул(ввода) ввести расчетную формулу с ссылками на диапазоны исходных ячеек, и
3. Нажать комбинацию клавиш Ctrl+ Shift+ Enter. При этом формула автоматически заключается в фигурные скобки. По фигурным скобкам Вы всегда сможете отличить формулу массива.
Мы решили нашу задачу двумя способами – набором отдельных формул и с использованием формулы массива. Отметим, что 2-ой способ, кроме удобства, имеет и другие преимущества.
1. Есть уверенность что, все формулы введены правильно,
2. Формулы массивов больше защищены от случайных изменений, поскольку Ехс6ен\l не позволит изменить одну формулу в ячейке, входящей в диапазон
При работе с табличными формулами нельзя манипулировать с ячейками исходных диапазонов, т.е. перемещать, удалять и вставлять отдельные ячейки, а также изменять формулу в одной из ячеек диапазона, содержащих формулу массива.
Редактирование табличных формул.
1. Выделите блок с формулами
2. Нажмите клавишу F2 и внесите изменения
3. Нажмите комбинацию клавиш Ctrl+Shift+Enter.
Если потребуется увеличить или уменьшить исходные диапазоны, проще всего,
очистить ячейки диапазона с формулой- результата и заново перезаписать ее во вновь выделенный диапазон.
Формулы массивов применяются при решении таких задач, как
1. Суммирование заданного числа наибольших (наименьших) значений массива
2. Поиск заданного значения в диапазоне
3. Определение местоположения мах, среднего, мин значения в диапазоне
4. Определение количества различий в двух диапазонах и т.д.
Создание и понимание формул облегчается, если в них используются имена ячеек и диапазонов.
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Проблемы, которые приводят к существенной потере функциональности
Некоторые формулы содержат массивы с числом элементов, превышающим поддерживаемое выбранным форматом файла. Массивы, которые содержат более 256 столбцов или 65536 строк, не будут сохранены и могут привести к разным результатам.
Что это означает. В Excel 2010 и более поздних годах можно использовать формулы массива, содержащие элементы для более чем 256 столбцов и 65 536 строк. В Excel 2007 г. это превышает ограничение для элементов массива и может возвращать другие результаты.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с массивами, в которых число элементов превышает число, поддерживаемое в более ранних версиях Excel, и внесите необходимые изменения.
Что это означает. В Excel 2007 и более поздних годах формула может содержать до 64 уровней вложенности, а в Excel 97–2003 — всего 7.
Что это означает. В Excel 2007 и более поздних годах формула может содержать до 255 аргументов, но в Excel 97–2003 число аргументов в формуле не может быть больше 30.
Что это означает. В Excel 2007 г. и более поздних гг. число операндов, которые можно использовать в формулах, составляет 1024, а в Excel 97–2003 — всего 40.
Что это означает. В Excel 2007 и более поздних User-Defined функция User-Defined(UDF), создаемая с помощью Visual Basic для приложений (VBA), может содержать до 60 аргументов, но в Excel 97–2003 число аргументов в UDF ограничено VBA только 29.
В некоторых случаях к формуле добавляется префикс _xlfn, например: =_xlfn.ЕСЛИОШИБКА (1;2).
Для всех переименованных функций доступны функции совместимости. Для предотвращения ошибок можно использовать эти функции.
Новые функции можно заменить соответствующими функциями, доступными в более ранних версиях Excel. Кроме того, можно удалить формулы с новыми функциями, заменив их результатами формул.
Некоторые формулы содержат ссылки на таблицы, которые не поддерживаются выбранным форматом файла. Эти ссылки будут преобразованы в ссылки на ячейки.
Что это означает. В Excel 2007 и более поздних годах можно использовать структурированные ссылки, чтобы работать с данными таблицы проще и понятнее при использовании формул, ссылаясь на части таблиц или целые таблицы. В Excel 97–2003 эта возможность не поддерживается, и структурированные ссылки преобразуются в ссылки на ячейки.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы со структурированными ссылками на таблицы, и замените их соответствующими ссылками на ячейки.
Одна или несколько ячеек книги содержат правила проверки данных, использующие ссылки на более чем 8192 несмежных диапазона ячеек. Такие правила проверки данных не будут сохранены.
Что это означает. В Excel 2010 г. и более поздних гг. правила проверки данных могут ссылаться на более чем 8192 невимех ячеек. В Excel 97–2007 этот тип правила проверки данных не поддерживается и будет не доступен.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие правила проверки данных, которые содержат ссылки на более чем 8192 несмежных диапазона ячеек, и внесите необходимые изменения.
Одна или несколько ячеек книги содержат правила проверки данных, использующие ссылки на значения, находящиеся на других листах. Такие правила проверки данных не будут поддерживаться в более ранних версиях Excel.
Что это означает. В Excel 2010 г. и более поздних можно использовать правила проверки данных, которые ссылаются на значения на других таблицах. В Excel 97–2007 этот тип проверки данных не поддерживается и не отображается на этом сайте.
Однако все правила проверки данных остаются доступными в книге и применяются при повторном ее повторном Excel 2010 г. или более поздней, если они не были изменены в Excel 97–2007.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие правила проверки данных, в которых есть ссылки на значения на других листах, и внесите необходимые изменения на вкладке Параметры диалогового окна Проверка данных (вкладка Данные, группа Работа с данными).
Книга содержит диапазон консолидации данных, ссылающийся на ячейки вне допустимого числа строк и столбцов для выбранного формата файла. Ссылки формул на данные в этой области будут изменены и могут отображаться некорректно в более ранней версии Excel.
Что это означает. В Excel 2007 и более поздних годах диапазоны консолидации данных могут содержать формулы, которые ссылаются на данные за пределами диапазона строк и столбцов выбранного формата файла. В Excel 97–2003 г. размер всего 256 столбцов на 65 536 строк. Формулы, ссылающиеся на данные в ячейках за пределами этого диапазона строк и столбцов, корректируются и могут отображаться неправильно.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти диапазоны консолидации данных, содержащие формулы, которые ссылаются на данные за пределами ограничения на число строк и столбцов Excel 97–2003, и внесите необходимые изменения.
Книга содержит настраиваемые описания пользовательских функций VBA. Все настраиваемые описания будут удалены.
Что это означает. В Excel 2010 г. и более поздних Visual Basic для приложений для создания функций User-Defined (UDFs) с настраиваемой описанием. Настраиваемые описания не поддерживаются в Excel 97–2007 и будут удалены.
Что необходимо сделать. Выполнять какие-либо действия не требуется, поскольку все настраиваемые описания будут удалены.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Не использовать в расчётах формулы НЕ работающие с закрытыми файлами Автор - Serge_007
Дата добавления - 30.05.2013 в 16:13
Это вопрос? Странно, что Вы его задаёте, ответ ведь очевиден.
Да, не работают. Иначе Вы видели бы результат, а не значение ошибки
Это вопрос? Странно, что Вы его задаёте, ответ ведь очевиден.
Да, не работают. Иначе Вы видели бы результат, а не значение ошибки Serge_007
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Что нужно сделать, чтобы при открытии расчетного файла срабатывали все формулы и появлялись данные без открытия источников?
Варианты
1. Запретить вставку/удаление строк в исходный файл
2. Из двух файлов сделать один и дать ему общий доступ
3. Использовать сводную таблицу
Варианты
1. Запретить вставку/удаление строк в исходный файл
2. Из двух файлов сделать один и дать ему общий доступ
3. Использовать сводную таблицу Serge_007
Первые 2 варианта не подходят - это их файл, который они ведут так, как считают нужным. Мы из него вытягиваем некоторые данные и формируем из них нужное представление для начальства. А про третий вариант можно чуть поподробнее?
Первые 2 варианта не подходят - это их файл, который они ведут так, как считают нужным. Мы из него вытягиваем некоторые данные и формируем из них нужное представление для начальства. А про третий вариант можно чуть поподробнее? DES
Ну и что что это их файл? У Вас на предприятии сотрудники решают как управлять компанией или руководство?
Если сотрудники, то да первые два варианта можно убрать. Как и любые другие. Может ваши сотрудники завтра решат столбцы в файл добавлять или картинки. А может перейдут на записи в тетрадке или вообще перестанут отмечать своё отработанное время
Ну и что что это их файл? У Вас на предприятии сотрудники решают как управлять компанией или руководство?
Если сотрудники, то да первые два варианта можно убрать. Как и любые другие. Может ваши сотрудники завтра решат столбцы в файл добавлять или картинки. А может перейдут на записи в тетрадке или вообще перестанут отмечать своё отработанное время Serge_007
Это не совсем наши сотрудники, а сторонние консультанты. Долго объяснять, но изначальная установка в моем случае такова, что я им не указ. Нашел другой выход - создал в их файле служебный лист, сделал на нем черновую выборку нужных мне данных (суть в том, что при сдвигах строк внутри файла ссылки остаются актуальными) и скрыл его, чтоб глаза им не мозолил. А начальнический файл натравил на этот лист (на нем никто ничего делать не будет, соответственно, номера ячеек меняться не будут). Думаю, должно работать
Это не совсем наши сотрудники, а сторонние консультанты. Долго объяснять, но изначальная установка в моем случае такова, что я им не указ. Нашел другой выход - создал в их файле служебный лист, сделал на нем черновую выборку нужных мне данных (суть в том, что при сдвигах строк внутри файла ссылки остаются актуальными) и скрыл его, чтоб глаза им не мозолил. А начальнический файл натравил на этот лист (на нем никто ничего делать не будет, соответственно, номера ячеек меняться не будут). Думаю, должно работать DES
Проблемы dDE
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) - она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную - это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно "но".
Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь - даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Пример функции ВПР с неправильными ссылками на диапазоны
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Проблемы с OLE
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Проблемы с макросами
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки.
Если ввести в ячейку формулу:
=ДВССЫЛ("C5")
=INDIRECT("C5")
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
Средством проверки совместимости обнаружены проблемы совместимости формул с более ранними версиями Microsoft Excel.
Начиная с Excel 2007 г., по умолчанию проверка совместимости проверяет наличие проблем в предыдущих версиях Excel. Если вас интересует только конкретная версия, снимите флажки для остальных версий.
Проблемы, отображаемые в списке Существенная потеря функциональности, необходимо устранить перед сохранением файла, чтобы избежать безвозвратной потери данных или неправильной работы функций.
Проблемы, отображаемые в списке Несущественная потеря точности, необязательно устранять перед сохранением книги: данные и функциональные возможности не теряются, но книга, открываемая в более ранней версии Excel, может выглядеть или работать несколько иначе.
Совет: Если имеется много проблем, требующих изучения, выберите команду Копировать на новый лист. Для работы над каждой проблемой используйте новый лист отчета о совместимости.
Проблемы, которые приводят к небольшой потере точности
Одно или несколько определенных в данной книге имен содержат формулы, длина которых превышает максимальный размер, допустимый для выбранного формата файла (255 символов). Эти формулы будут сохранены, но будут усечены при редактировании в более ранних версиях Excel.
Что это означает. Если именованные диапазоны в формулах превышают предел в 255 знаков, поддерживаемый в Excel 97–2003, формула работает правильно, но усекается в диалоговом окне Имя и недоступна для редактирования.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие именованные диапазоны в формулах, и внесите необходимые изменения, чтобы пользователи могли редактировать эти формулы в Excel 97–2003.
Книга содержит формулы, которые ссылаются на другие закрытые книги. Если связанные книги закрыты, то при пересчете в более ранних версиях Excel значения этих формул будут ограничены 255 знаками.
Что это означает. Если формулы в одной книге связаны с другими книгами, которые закрыты, при пересчете в Excel 97–2003 эти формулы могут отобразить не более 255 знаков. Результаты формулы могут быть усечены.
Что необходимо сделать. Найдите ячейки, содержащие формулы, которые ссылаются на другие закрытые книги, проверьте эти ссылки и внесите необходимые изменения, чтобы избежать усечения результатов формулы в Excel 97–2003.
Формула для проверки данных содержит более 255 символов.
Что это означает. Если формула проверки данных превышает заданный в Excel 97–2003 предел в 255 знаков, формула будет работать правильно, но будет усечена и недоступна для редактирования.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы проверки данных, и сократите число знаков в формулах, чтобы пользователи могли редактировать их в Excel 97–2003.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку "Отменить" на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
В этой статье
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки - бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 - не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными:
Читайте также: