Название: Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 28.03.2019, 11:32:15 Здравствуйте.
В приложенном файле пример формулы, с помощью которой я подтягиваю вчерашнее значение из предыдущего листа (ячейка U12) для учета его в сегодняшнем (ячейка I5). Листы создаются на каждый день, кроме выходных. И как доходим до выходных - после пятницы 08.03 следующий лист понедельник 11.03 - появляется #REF. Существует ли способ решить это ? Заранее спасибо за любую помощь. Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Дмитрий Щербаков(The_Prist) от 28.03.2019, 12:15:06 А так не подойдет?
=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"ДД.ММ")&"!U12") только для не рус.локализации может потребоваться заменить "ДД.ММ" на "DD.MM" Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 28.03.2019, 22:06:42 так пока тоже не получается, пробую и с "ДД.ММ" и с "DD.MM"
С этой формулой почему-то сразу #ИМЯ, даже до выходных, при последовательных датах (вкладка "06.03" в приложенном файле). Ваша формула, Дмитрий, компактнее, это плюс. В итоге есть изначальная формула Код: (vb) =ДВССЫЛ(ПРАВБ(0&ЛЕВБ(ПРАВБ(ЯЧЕЙКА("filename";A1);5);2)-1;2)&"."&ПРАВБ(ЯЧЕЙКА("filename";A1);2)&"!U12"), которая работает, но до первого выходного дня, и формула Код: (vb) =ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"ДД.ММ")&"!U12"), которая пока дает ошибку #ИМЯ). Возможно, это я где-то ошибаюсь, адаптируя эту формулу, пока не нашла где. Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Дмитрий Щербаков(The_Prist) от 29.03.2019, 09:17:36 В первом сообщение в ячейке А1 был текст вида "Дата: 10.03.2019". Сейчас этого текста там в некоторых листах нет. Возможно, проблема в этом. Если добавить - все работает.
Прикладываю файлик, в котором надо будет только для функции ТЕКСТ изменить при необходимости формат. Но ошибка #ИМЯ! обычно связана с обращением к несуществующей функции или ячейке,именованному диапазону. Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 01.04.2019, 16:18:26 Да, Дмитрий, Вы правы, кое-где не было указания на Дату в А1.
Это исправила, однако, на моем компьютере первый же лист, где значение в I5 вычисляется предложенной Вами формулой, дает результат (теперь) #VALUE или #ЗНАЧ. Формат даты я поменяла с "ДД.ММ" на "DD.MM", и всё равно то же самое. По оценщику формул вижу, что на первом параметре шага "ПРАВБ(A1;10)" дата считывается как надо (10 первых знаков справа = 05.03.2019), а дальше, на параметре "10", и появляется #VALUE. Можете ли Вы подсказать, с чем это может быть связано ?.. и что можно подправить. Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Дмитрий Щербаков(The_Prist) от 02.04.2019, 08:55:00 Надо посмотреть как отображается дата в статус-баре Windows.
Если формат вроде "02/04/2019" - то можно сделать так: ПОДСТАВИТЬ(ПРАВБ(A1;10);".";"/") Если же там что-то вроде "4/2/2019", т.е. чисто американский формат отображения даты, то все немного хуже. И надо крутить как-то так(это в принципе будет универсальный вариант): =ДАТА(ПРАВБ(A1;4);ПСТР(ПРАВБ(A1;7);1;2);ПСТР(ПРАВБ(A1;10);1;2)) Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 02.04.2019, 16:28:53 "Повертела" :).
Про второй вариант есть вот что сказать : Да, он позволяет получить дату в виде "день/месяц/год". И эта дата представляется в формате например "43529" для даты 05/03/2019 или "43528" для даты 04/03/2019. И да, это убирает сбой, возникавший в общей формуле на шаге (ПРАВБ((A1;10)). Ура. Далее мне нужно заменить часть исходной формулы, в которой рождалась ошибка #VALUE, на этот новый вариант и вместо =ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"DD.MM")&"!U12") получаю =ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1);"DD.MM")&"!U12"). Этот новый вариант пока дает в итоге #REF и, сдается мне, дело опять в формате. Потому что финальные шаги этой формулы выглядят так : =ДВССЫЛ(ТЕКСТ(43528;"DD.MM")&"!U12") и =ДВССЫЛ("DD.03!U12") чего Excel, разумеется, принять не может, за что и выдает свое =#REF. В статус-баре к сожалению не получается увидеть тип формата, поэтому и пользуюсь оценщиком формул. Наверное, проблема опять в формате (если только я не ошиблась где-то, что допускаю, так как "верчу" уже долго и упорно). Пробовала и с DD.MM, и с ДД.ММ, и вообще без преобразования в ТЕКСТ, и без РАБДЕНЬ - не помогает. Получается, решение лежит в другой плоскости. HELP please. Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Дмитрий Щербаков(The_Prist) от 02.04.2019, 18:19:43 =ДВССЫЛ("DD.03!U12") Насторожить должно именно то, что там DD в итоге. Надо просто назначить любой ячейке с реальной датой нужный формат и посмотреть как он выглядит. Может там не ДД и не DD должно быть, а что-то еще(от локализации зависит).вообще без преобразования в ТЕКСТ, и без РАБДЕНЬ ну без них-то работать и не будет. Поэтому крутить надо все же формат. Либо сильно все усложнять. Вроде этого:=ДВССЫЛ(ТЕКСТ(День(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1));"00")&"."& ТЕКСТ(Месяц(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1));"00")&"!U12") Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 03.04.2019, 17:54:47 Спасибо, буду пробовать еще.
Всё же тот факт, что я так и привязана к имени листа, и что присутствует "-1", чтобы попасть на день назад, вызывает опасение, что в итоге, после выходных, когда недостаточно вернуться лишь на 1 день назад, формула так и не заработает... Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 03.04.2019, 17:56:03 Дима, а что значит фраза "от локализации зависит" ? от локализации ячейки с датой ?
Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Дмитрий Щербаков(The_Prist) от 04.04.2019, 09:16:01 привязана к имени листа, и что присутствует "-1" -1 привязана к РАБДЕНЬ, которая выходные как раз не учитывает.что значит фраза "от локализации зависит" Локализация ПК. Т.е. формат даты именно на ПК, независимо от формата ячейки. Функция ТЕКСТ к ней чувствительна.Если не хочется привязываться к именами листов и надо всегда смотреть на предыдущий лист(независимо от его имени), то только VBA. Вроде такой функции: Код: (vb) Function GetPrevSheetValue(CellAddr As String) использовать на листе просто указав адрес ячейки, значение которой надо получить из предыдущего листа: =GetPrevSheetValue("A1") Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 04.04.2019, 09:25:03 Да, спасибо огромное ! Я тоже потратила менее 5 минут на решение этой задачки с помощью VBA. Как жаль, что иногда надо обходиться без него :).
Название: Re:Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных Отправлено: Valetnina от 09.04.2019, 16:46:56 Здравствуйте.
Попробовала проверить "локализацию" своего компьютера в части даты с помощью формул "=СЦЕП($C$1;"*";ТЕКСТ($A$1;"ДД.ММ.ГГГГ"))" и "=СЦЕП($C$1;"*";ТЕКСТ($A$1;"DD.MM.AAAA"))", обе дают одинаковый корректный результат, что дает мне право думать, что проблема была не в формате. В любом случае, вот формула, которая работает и решает, в том числе, и проблему "выходных" : =ДВССЫЛ(ТЕКСТ(ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)));ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)))+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2));2)<>1;-1;-3)));"00")&"."&ТЕКСТ(МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)));ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)))+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2));2)<>1;-1;-3)));"00")&"!"&"$U$12") Единственным ее слабым местом, помимо громоздкости, считаю ГОД, так как неизвестно, как поведет себя она например в следующем году. Доживем - увидим. Всем удачи и вдохновения :) И еще раз спасибо. |