Формула в этой ячейке ссылается на диапазон к которому прилегают другие значения excel
Проводим в школе анализ успеваемости детей по 1 семестру прошлого уч. года и нынешнего. Все было хорошо, НО с какого то демона на странице "среднеарифметическое" при автозаполнении вдруг вылезли ошибки в ячейках I29,h25,h26. Бьюсь головой о стену много часов. Данные полученные формулами в ячейках правильные. Но эта хрень не дает возможности построить графики (я так понял) для всех последующих предметов (столбцы H,I,J,K,L,M). ПАТЧЕМУ. Кто знает, помогите плиз.
Ошибка при автозаполнении textBox с подключенной БД
Автозавершение работает на второй форме без БД, а вод при подключении БД уже не работает. Помогите.
Получение выпадающего списка при автозаполнении combobx
Есть combobx, когда начинаешь вводить данные выпадает список автозаполнения, как бы получить доступ.
Как связать два значения из регистра сведений при автозаполнении?
Есть регистр сведений содержащий столбцы Номенклатура, Контрагент, Цена, СрокиПоставки сделал.
При автозаполнении захватывая ячейку с числом обычно значение не копируется , а увеличивается на 1.
При автозаполнении захватывая ячейку с числом обычно значение не копируется , а увеличивается на.
Там в 3/4 ячеек деление на 0. Но ошибку выдает только в трех перечисленных ячейках. Особенно достает, что в ячейке І29, где явно не деление на 0.
В примере на странице "среднеарифметическое" в ячейке I29 зеленый уголочек, он говорит о том, что есть ошибка. Какая х/з?
А посмотреть что за ошибка сложно?
Ткните на пиктограмму, которая появляется при наведении на эту ячейку.
"формула в ячейке ссылается на диапазон к которому прилегают другие значения" и что с этим делать? Что вызывает такую ошибку?
Добавлено через 29 минут
Тем более, что все делалось для всех ячеек одинаково - автозаполнением, форматы ячеек везде одинаковы, но только в трех этих выдает ошибку. Уже все тысячу раз перепроверил, ума не приложу в чем дело и сроки горят.
Добавлено через 1 час 18 минут
Как ни странно в версии офиса 2003 если пересохранить файл все нормально. Дебильный майкрософт, блин.
В ячейках N3, P9 слева вверху треугольничек. Читаю: "Формула в этой ячейке ссылается на диапазон, к которому прилегают (?) другие значения."
Очищаю D3. M3 - все, N3 успокаивается. Формула осталась все той же, но уже никто ни к кому не прилегает. А ведь убраны только значения - константы. Странно. Пробую то же со строкой 9 - фигу-с. Очищаю С9 - все, опять никто не прилегает, хотя формулы те же. Формула зависит от содержимого ячейки? СБСК.
Еще раз констатирую: каждый последующий месяц копипастился с предыдущего, и там никто ни к кому не прилегал.
К счастью, Ёкселем пользуюсь крайне редко, вот только для домашнего финанса. И я не прошу совета: что мне с этим делать. Ахерн с ним, с предупреждением, переживем. Вопрос звучит так: много ли такой хренобредятины в Ёкселе ? И как умудряются те, что с ним постоянно работает, не загреметь в Белые Столбы (или Новинки по нашему) ?
Excel 2007, режим совместимости, файл создавался в Excel 2003. Тестовый файл выдрал из рабочей книги, думал - мож контекст как-то влияет. Моноферственно - один лист - но вот тебе и пожалуйста.
я представляю какой бурной может быть степень недоумения индейца из глубоких джунглей от осознания того, сколько всякой хренобредятины напихано в мобильный телефон!
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
Параметры, Формулы, последняя группа настроек, Правила проверки ошибок
посносите оттуда все птички и у Вас в файле не будет ошибок!))
только тут речь не сколько об ошибках, сколько о ячейках, относительно которых у Excel возникли подозрения, что что-то с ними не так, это просто акцентирует внимание пользователя, а он, в свою очередь, может отказаться от всех этих подсказок, если, конечно, знает как открыть окно Параметры.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
Office 2010 - никаких предупреждений в этих ячейках нет. |
какой бурной может быть степень недоумения индейца из глубоких джунглей от осознания того, сколько всякой хренобредятины напихано в мобильный телефон! |
Индейцы из джунглей обойдутся без недоумений - решат, что это боги позволяют им общаться с душами предков. А вот индеец, слегка окончивший какой-нить Гарвард или прочий колледж будет матово недоумевать: он звонит в Бостон, а мобила ему: "Мне кажется, что ты звонишь в Жмеринку".
Ладно, оп с ними, с настройками, проживу и с предупреждениями. А если удастся скачать старую добрую версию 2003, вообще будет класс. Пока, увы, никак - по запросу "Установить Excel 2003" лезет абсолютно всякая фекаль, не имеющая к предмету минимальнейшего отношения. Один "установщик" вообще выдал: Требуется 137Мбайт, на диске свободно 5.6байт. Память крошить начал, гуано (это по испански) этакое !?
Еще раз радостно скажу: спасибо судьбе, что не свела меня на тропе работы с . по фамилии Excel.
PS В меню верхнего уровня пункта Параметры нету. Как нет и желания больше иметь дело с поделкой, больше напоминающей "Угадайку" .
Примите уверения etc
повторюсь на всякий случай еще раз, если я не имею ни малейшего понятия как поднять в воздух вертолет - мои слова, "что это бесполезное устройство напичканное всякой хренобредятиной" следует рассматривать лишь как констатацию моего полного невежества в этом вопросе, а если все это высказано еще и очень эмоционально, то становится понятно, что еще и нервы ни к черту.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
извините, мой спитч то же не лишен эмоциональности
а до Параметров в Excel таки можно добраться (в 2003 через меню Сервис, в более поздних версиях через "большую кнопку Excel" или Файл)
удачи!
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
Все нормально, no problrm.
Но не буду ничего настраивать, и так оно не сильно колышет.
А вот кто бы мне скинул прямую ссылку на скачать старую версию 2003 - был бы очень. А то, что выдает поисковик, ведет на "установить новый браузер" или "автоматически установить media загрузчик, который вам накидает всего-всего-всего. " Кроме того, что было в запросе, естественно.
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»
=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)
Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.
При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:
Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.
Способ 2. Динамический именованный диапазон
Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:
Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.
Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.
Ищем последнюю ячейку с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.
Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!
Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:
Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.
Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:
Формируем ссылку с помощью ИНДЕКС
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.
Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.
И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:
=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))
Создаем именованный диапазон
Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
Не работает формула =СУММ (диапазон) на указанный на скриншоте диапазон. Значения ячеек этого диапазона выбираются на основе других параметров. Как выйти из данной ситуации? Необходимо автоматизировать вычисление суммы выделенного диапазона
Я не включил формулу в скриншот, так как в этом нет смысла. Я хочу увидеть в некоторой (любой) ячейке сумму диапазона который выделен, но в любом случае у меня выводится число 0. Хотелось бы понять, связано ли это с тем, что значения ячеек в заданном диапазоне подвержены изменению
Полосатый жираф Алик Искусственный Интеллект (300805) Вот встань в ячейку под надписью "Количество" и покажи формулу в ней. И укажи сам интервал. Ведь не видно номеров строк! Кстати, можно и по интервалу в столбце N подсчитать нужную сумму!
Все там должно работать, даже если ты просто отметишь вот так вот эти ячейки, в строке статуса увидишь сумму.
Я не включил формулу в скриншот, так как в этом нет смысла. Я хочу увидеть в некоторой (любой) ячейке сумму диапазона который выделен, но в любом случае у меня выводится число 0. Хотелось бы понять, связано ли это с тем, что значения ячеек в заданном диапазоне подвержены изменению
Jurijus Zaksas Искусственный Интеллект (350818) Может и правильно, но слишком сложно, не нужно это. Использовал бы простые вычисления - не напоролся бы на суммирование текста.
Jurijus Zaksas Искусственный Интеллект (350818) Я ж не знал, что там у него текст, он же формулу свою позорную прятал как мог. Цифры суммируются:
=СЧЁТЕСЛИ (N3:N15;0)*2+СЧЁТЕСЛИ (N3:N15;1)*4
=СУММПРОИЗВ (НЕ (Z3:Z15)*(N3:N15=0)*2+(N3:N15=1)*4)
=СУММ (ЕСЛИ (N3:N15;4;2))
* последняя - формула массива. при вводе нажать shift+ctrl+enter
Здравствуйте! Помогите разобраться, что не так? Пытаюсь сослаться на другой лист, но выдает ошибку "ЗНАЧ". Форматы ячеек одинаковые. Пример прикрепила. Ячейки, на которые нужно обратить внимание на 2 листе и выделены оранжевым цветом. Ссылаемся на ячейки на первом листе.
Перенаправление на другой лист при нажатии на нужную ячейку
Задумка была следующая есть два листа в одном расчет по помещениям в другом сводная условно.
Копирование значения при после ввода на другой лист в первую пустую ячейку в определенном диапазоне
Добрый вечер! Помогите, пожалуйста, с проблемой. В VBA не силен, но формулами не получается.
Ошибка при ссылке на пустую ячейку
Добрый вечер. При составлении основной формулы, элементы которой ссылаются на другие ячейки.
Ошибка при копировании ячеек в другой лист
Sub Записать4х4() Temp = Лист8.Range("S5").Value For i = 1 To 4 Range(Cells(2, i), Cells(5.
Решение
Gulnaz_N, вы ссылаетесь на объединенные ячейки и эксел немного не правильно адресует
попробуйте к примеру вместо диапазона ссылаться на ячейку ='Ж.внедрения МИ'!I5 и все заработает.
Просто уберите из ссылок то что после :
АЕ, Вечер Добрый. А разве Дубли тем на этом форуме разрешены?
Ошибка при ссылке на ячейку на другой лист
Почему вопрос ко мне? Я должен был перед ответом это проверить?
Или смущает, что вы ответили спустя более чем через 3 часа после меня в дубле темы?
АЕ, Я на время не смотрел. Приехал с работы, залез на форум. Ну и по аналогии как всегда снизу вверх темы просматривал. И тут такое, честное слово на время поста не обратил внимание. Да и не ожидал дубля тут. Я что-то как всегда принял что вы модератор форума. Сорян, вы тут обычный пользователь.
Добавлено через 4 минуты
Что-то тут модераторов форума не видать.
Как в ячейку вставить формулу со ссылкой на другой лист?
Есть таблица, вид таблицы представлен во вложении. Это уже результат работы макроса. Есть куча.
Копирование на другой лист в пустую ячейку после текста
Здравствуйте! Есть код, но если его воспроизвести второй раз в экселе, то не вставляются значения.
Выделенный текст перенести на другой лист или другую ячейку
помогите сделать таблицу, в которой изначально есть строки с красным выделением. как только ячейка.
Перенесение результатов ячейки в ячейку на другой лист, в зависимости от значения исходной ячейки
Добрый день, - прошу помощи экспертов! Есть такая таблица (в вложении). Суть вопроса такова: Есть.
При нажатии на ячейку, результат перенести на следующий лист
Нужно готовый результат перенести на следующий лист . Пример:C sheet1- при нажатии на Е4.
Копировать строку с листа на лист при внесении изменений в ячейку в этой строке
я - сантехник, частенько приходится обсчитывать работы и материалы. как в экселе перенести строку с.
Читайте также: