Введенный текст не является правильной ссылкой или именем excel
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).
Пример функции ВПР с неправильными ссылками на диапазоны
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Проблемы с OLE
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы dDE
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Проблемы с макросами
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В поле ввода Значение к числу можно добавить знак денежной единицы или процента
Поиск решения
Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которые приводили бы к точно заданному значению, либо минимально или максимально возможному значение. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы.
Назначение средства «Поиск решения»
Если говорить упрощенно, то средство Поиск решения путем изменения значений в заданных ячейках (такие ячейки называются изменяемыми ячейками) добивается того, чтобы в ячейке с формулой (называется целевой ячейкой) было или определенное (заданное вами) значение, либо чтобы эта формула принимала минимально или максимально возможное значение. Можно также наложить ограничения на изменяемые значения, причем эти ограничения могут быть как прямыми (например, значения не должны выходить из определенного интервала), так и опосредованными, когда несколько изменяемых значений связаны каким-либо соотношением и ограничение налагается на это соотношение.
Для примера рассмотрим задачу вычисления оптимальных цен и количеств театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В театре имеются билеты трех категорий: детские, для взрослых и льготные (рис. 4.1).
В целевой ячейке В6 подсчитывается сумма стоимостей билетов всех категорий (для каждой категории билетов их стоимость подсчитывается как произведение количества билетов и их цены).
Рис. 4.1. Рабочий лист для поиска оптимальной цены театральных билетов
В главе 1 при использовании средства Подбор параметра за один раз мы могли изменять значение только одной переменной: значение цены или количество билетов одной категории. Средство Поиск решения также может изменять значения только одной переменной (т.е. может работать в режиме подбора параметра). Однако, чтобы оценить гибкость и мощь этого средства, следует использовать его для изменения нескольких переменных. Кроме того, это средство может добиться от целевой ячейки (точнее, от формулы, содержащейся в этой ячейке) не только определенного заранее значения, но и максимально или минимально возможного (для этой формулы) значения.
Кроме того, подчеркнем еще раз, в отличие от средства Подбор параметра средство Поиск решения позволяет налагать разнообразные ограничения на значения изменяемых переменных. Например, Поиск решения может найти, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 24 тыс. руб., при выполнении следующих ограничений.
Цена детских билетов постоянна и равна 60 руб.
Цена билетов для взрослых постоянна и равна 100 руб.
Цена льготных билетов постоянна и равна 90 руб.
Билетов каждой категории можно продать не более 100 шт.
В театре может быть аншлаг, но «лишних билетиков» нет.
Поиск решения немедленно найдет решение этой задачи: для того, чтобы в кассе оказалось ровно 24 тыс. руб., необходимо продать 100 детских, 90 для взрослых и 100 льготных билетов. Поиск решения по специальному алгоритму перебирает возможные комбинации значений количеств билетов разной категории для того, чтобы найти искомое решение.
Другой пример решения задачи с ограничениями показан на рис. 4.2. Здесь некий завод производит три вида изделий, но может производить в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпускаемых изделий таким образом, чтобы получить максимальный доход. Ответ, который дает Поиск решения, показан на том же рис. 4.2.
Рис. 4.2. Определение оптимальной структуры производства
Каждый, кто более-менее часто имеет дело с формулами Excel (пишет сам или пользуется файлами, созданными другими), знает, что иногда в этих формулах появляются крайне неприятные ошибки, которые приводят к их полной неработоспособности. В разнообразии ошибок легко запутаться. Но чтобы уметь быстро их исправлять, нужно знать, почему возникает та или иная ошибка и что с ней делать. Разберем их основные виды.
Если ячейка вдруг целиком заполнилась символами решётки, то варианта всего два: либо значение ячейки не помещается в нее, либо в ней введено отрицательное значение времени (случается, если формат ячейки - "Время").
Самая распространенная ошибка. Возникает тогда, когда функция поиска данных не находит искомое значение в диапазоне. Функции поиска данных это: ВПР, ГПР, ПОИСКПОЗ, ПРОСМОТР. Соответственно, решается либо изменением поискового запроса ("что ищем"), либо внесением в диапазон искомого значения. Однако, чаще всего эта ошибка вполне ожидаема и просто помогает проверить наличие того или иного значения в списке. Многие пользователи предпочитают выводить вместо нее пустые значения или какой-то значимый текст с помощью функции ЕСЛИОШИБКА. Например:
Возникает, когда в формуле используется нераспознанное программой имя. Именем Excel считает любой текст, не являющийся названием функции, ссылкой на ячейку/диапазон и не взятый в кавычки. Например, в формуле =СЕГОДНЯ()+СЕГ-A4 слово СЕГ будет распознано как имя.
Когда распознанного имени нет в списке именованных диапазонов, появляется данная ошибка. Способы решения:
- Создать нужное имя в диспетчере имен;
- Проверить правильность написания уже существующего имени;
- Проверить, верно ли написаны функции рабочего листа (опечатки приведут к возникновению ошибки)
Данная ошибка возникает в случае, когда ячейка или диапазон, на который ссылается формула, был удален, перемещен или стал недоступным. Например, если есть формула
Другой вариант возникновения - файлы, на которые есть ссылки были перемещены, удалены или переименованы. Любое из этих действий при последующем обновлении связей выдаст указанную ошибку. Вывод - будьте аккуратнее с файлами, на которые делаете ссылки.
Возникает чаще всего тогда, когда в формуле использован неверный тип данных. Помните, что текст, число или дата - разные типы данных и обрабатываются по разному. Если передать, например, функции ДЕНЬНЕД() ссылку на ячейку с текстом, то появится указанная ошибка. То же самое произойдет, если попытаетесь вычесть из 100 слово "десять". Для исправления - проверьте все аргументы Соответствуют ли они требуемым типам данных? Если нет - укажите правильные типы.
Крайне редкая ошибка, так как мало кто использует в работе оператор пересечения диапазонов (про него можно почитать тут ). Собственно, возникает тогда, когда диапазоны не пересекаются. Для исправления - укажите пересекающиеся диапазоны. Например, формула:
выдаст ошибку. А формула:
будет работать безошибочно и вернет диапазон A5:B5
выдаст эту ошибку, так как аргумент "Ставка" не может быть отрицательным.
Для исправления - введите допустимый числовой аргумент.
Знать, что означают ошибки - полбеды. Нужно еще понять, какая именно часть формулы генерирует ошибку. Для этого формулу нужно проанализировать. Советы на эту тему можно найти в этой статье . Желаем Вам только исправных формул!
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
В excel есть возможность присвоения имен ячейкам, таблицам и диапазонам. После присвоения, эти имена можно использовать в формулах, при этом формулы становятся намного понятнее пользователю.
Присвоение имени константе .
Имена можно присваивать и константам, которых даже нет в вашем файле. Например, если в своем прогнозе вы используете рост рынка в размере 5% в следующем году, то этому росту можно присвоить имя. Для этого перейдите на ленте во вкладку Формулы -> Определенные имена -> Задать имя:
Откроется диалоговое окно "Создание имени", в котором необходимо ввести Имя, Область действия этого имени (лист, книга), Примечание (чтобы не забыть, что это за константа), в поле Диапазон необходимо ввести число и нажать ОК:
Теперь эту константу можно использовать во всей книге при расчете формулы, например так:
Обратите внимание, теперь значительно проще понять как был сформирован объем продаж в 2021 году.
Важно : константой может быть не только число, но и текст. Таким образом вы можете упростить ввод часто повторяющихся слов.
Присвоение имени ячейке и формуле.
Порядок присвоения имени ячейке и формуле не сильно отличается от предыдущего примера. Перейдите на ленте в блок Задать имя и в диалоговом окне "Создание имени" на последнем шаге вместо ввода числа введите либо ссылку на ячейку, либо формулу:
Если вы хотите, чтобы ваша именованная формула при расчете использовала значение из ячейки, находящейся левее (например, A4) от той, в которой формула будет находиться (B4), обязательно перед созданием имени активируйте ячейку B4 и не используйте абсолютные ссылки.
Подробнее об абсолютных и относительных ссылках можете прочитать в статье Абсолютные и относительные ссылки в Excel .
Присвоение имени диапазону.
В присвоении имени диапазону нет ничего сложного, просто в окне Создание имени в поле Диапазон пропишите ссылку на него. Интереснее дела обстоят с использованием пересечения диапазонов (это те ячейки, которые являются общими для двух диапазонов). Предположим, есть таблица с объемом продаж трех менеджеров за 4 квартала:
Всем привет! Сегодня начинаем разбирать функцию ДВССЫЛ (INDIRECT) в Excel.
Сама по себе, функция очень проста - она превращает текст в ссылку. Например, две нижеследующие формулы аналогичны:
=ДВССЫЛ (A1) (оно же в англоязычном интерфейсе INDIRECT (A1))
После такого описания может показаться, что функция ДВССЫЛ - это какой-то атавизм из древних версий Excel, ведь, если можно простым способом обращаться к ячейке, то зачем его усложнять? Но это типичное заблуждение, с которым я и сам когда-то столкнулся на начальном этапе изучения Excel. На самом деле, функция достаточно интересная - в некоторых случаях она может заметно облегчить жизнь, хоть многие и считают, что использовать ДВССЫЛ - это моветон:)
Начнем с простого. Синтаксис функции :
- ссылка_на_текст. Обязательный аргумент, в котором указывается ссылка на ячейку в формате текста. Это может быть ссылка, на ячейку с этим текстом, например, =ДВССЫЛ(A1). Или это может быть сразу текст, например, так: =ДВССЫЛ("A1") - помним, что текст в формулах прописывается в кавычках.
- [a1]. Необязательный аргумент для определения типа ссылки. Если имеет значение ИСТИНА (TRUE) или опущен, то ссылка на текст воспринимается, как ссылка типа A1. Если указано значение ЛОЖЬ (FALSE), то воспринимается, как ссылка в стиле R1C1.
Посмотрим, как это работает.
1. Классический способ применения функции ДВССЫЛ (INDIRECT)
Имеем таблицу с наименованием городов и численностью их населения. Задача: сослаться на ячейку с численностью - пусть это будет ячейка. B2. Наши действия:
- привычный способ - обратиться через знак ровно: =B2
- через ДВССЫЛ эта же формула будет выглядеть так: ДВССЫЛ("B2"). Помним, что в кавычках указывается текст, поэтому функция сразу воспринимает указанное, как адрес необходимой ячейки.
- если в файле в отдельной ячейке указана текстом ссылка на ячейку B2 (в нашем примере это значение прописано в H2), то функция будет выглядеть следующим образом: =ДВССЫЛ(H2). Здесь мы не указываем ковычки, поэтому функция понимает, что адрес необходимой ячейки прописан в ячейке H2. Иллюстрация ниже.
Теперь, когда мы поняли, как это работает, посмотрим, где это можно использовать.
2. Транспонирование данных с помощью функции ДВССЫЛ (INDIRECT)
Задача: имеем вертикальный диапазон с названиями, но хотим преобразовать его в горизонтальный.
ДВССЫЛ в данном случае не является классическим решением задачи, но тоже с ней справится. Для этого нам нужно прописать такую формулу, которая при горизонтальном протягивании будет каждый раз ссылаться на ячейку ниже. Можно было бы прописывать руками номер строки, но в примере ниже мы воспользуемся функцией СТОЛБЕЦ (COLUMN), которая вернет номер столбца текущей ячейки. В моем примере они совпадут - данные расположены удобно, чтобы не перегружать формулу лишними вычислениями для вашей наглядности (исходник начинается со 2й строки, транспонирую в диапазон, который начинается со 2го столбца). На практике может потребоваться скорректировать полученный результат фунции СТОЛБЕЦ, например, вычитанием разницы.
3. Зависимый выпадающий список
Задача: при заполнении отчета необходимо, чтобы в выпадающем списке были не все значения, а лишь те, которые соответствуют какому-то ранее указанному признаку. Например, имеем три филиала с названиями Московский, Тверской, Тульский - каждый филиал отвечает за определенные города, находящиеся поблизости >> хотим, чтобы в отчете при выборе московского филиала в выпадающем списке появлялись только его города.
Читайте также: