Excel это не сложно
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
07.10.2022, 22:10:24

Войти
Хотите поблагодарить участника за дельный совет? Нажмите [Повысить]. Так вы заслуженно поднимите репутацию активному участнику.
32 837 Сообщений в 5 348 Тем от 6 479 Пользователей
Последний пользователь: Sekretland
*
Перейти на сайт Хитрости Надстройка MulTEx Обучающие тренинги Наша группа ВКонтакте
Правила форума Начало Помощь Поиск Календарь Войти Регистрация Выйти
+  Excel это не сложно
|-+  Основные форумы
| |-+  Вопросы по Excel и VBA
| | |-+  Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных
Страниц: [1]   Вниз
Печать
Автор Тема: Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных  (Прочитано 3642 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« : 28.03.2019, 11:32:15 »

Здравствуйте.
В приложенном файле пример формулы, с помощью которой я подтягиваю вчерашнее значение из предыдущего листа (ячейка U12) для учета его в сегодняшнем (ячейка I5). Листы создаются на каждый день, кроме выходных. И как доходим до выходных - после пятницы 08.03 следующий лист понедельник 11.03 - появляется #REF. Существует ли способ решить это ?
Заранее спасибо за любую помощь.
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +481/-0
Офлайн Офлайн

Сообщений: 5 697



Просмотр профиля WWW
« Ответ #1 : 28.03.2019, 12:15:06 »

А так не подойдет?
=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"ДД.ММ")&"!U12")
только для не рус.локализации может потребоваться заменить "ДД.ММ" на "DD.MM"
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #2 : 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")
, которая пока дает ошибку #ИМЯ). Возможно, это я где-то ошибаюсь, адаптируя эту формулу, пока не нашла где.
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +481/-0
Офлайн Офлайн

Сообщений: 5 697



Просмотр профиля WWW
« Ответ #3 : 29.03.2019, 09:17:36 »

В первом сообщение в ячейке А1 был текст вида "Дата: 10.03.2019". Сейчас этого текста там в некоторых листах нет. Возможно, проблема в этом. Если добавить - все работает.
Прикладываю файлик, в котором надо будет только для функции ТЕКСТ изменить при необходимости формат.
Но ошибка #ИМЯ! обычно связана с обращением к несуществующей функции или ячейке,именованному диапазону.
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #4 : 01.04.2019, 16:18:26 »

Да, Дмитрий, Вы правы, кое-где не было указания на Дату в А1.
Это исправила, однако, на моем компьютере первый же лист, где значение в I5 вычисляется предложенной Вами формулой, дает результат (теперь) #VALUE или #ЗНАЧ.
 Формат даты я поменяла с "ДД.ММ" на "DD.MM", и всё равно то же самое.
По оценщику формул вижу, что на первом параметре шага  "ПРАВБ(A1;10)" дата считывается как надо (10 первых знаков справа = 05.03.2019), а дальше, на параметре "10", и появляется #VALUE.
Можете ли Вы подсказать, с чем это может быть связано ?.. и что можно подправить.
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +481/-0
Офлайн Офлайн

Сообщений: 5 697



Просмотр профиля WWW
« Ответ #5 : 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))


Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #6 : 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.


Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +481/-0
Офлайн Офлайн

Сообщений: 5 697



Просмотр профиля WWW
« Ответ #7 : 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")
« Последнее редактирование: 02.04.2019, 18:39:14 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #8 : 03.04.2019, 17:54:47 »

Спасибо, буду пробовать еще.
Всё же тот факт, что я так и привязана к имени листа, и что присутствует "-1", чтобы попасть на день назад, вызывает опасение, что в итоге, после выходных, когда недостаточно вернуться лишь на 1 день назад, формула так и не заработает...
Записан
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #9 : 03.04.2019, 17:56:03 »

Дима, а что значит фраза "от локализации зависит" ? от локализации ячейки с датой ?
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +481/-0
Офлайн Офлайн

Сообщений: 5 697



Просмотр профиля WWW
« Ответ #10 : 04.04.2019, 09:16:01 »

привязана к имени листа, и что присутствует "-1"
-1 привязана к РАБДЕНЬ, которая выходные как раз не учитывает.
что значит фраза "от локализации зависит"
Локализация ПК. Т.е. формат даты именно на ПК, независимо от формата ячейки. Функция ТЕКСТ к ней чувствительна.

Если не хочется привязываться к именами листов и надо всегда смотреть на предыдущий лист(независимо от его имени), то только VBA. Вроде такой функции:
Код: (vb)
Function GetPrevSheetValue(CellAddr As String)
    Application.Volatile True
    Dim wsCur As Worksheet
    Set wsCur = Application.Caller.Parent
    If wsCur.Index = 1 Then
        GetPrevSheetValue = ""
    Else
        GetPrevSheetValue = wsCur.Parent.Worksheets(wsCur.Index - 1).Range(CellAddr).Value
    End If
End Function

использовать на листе просто указав адрес ячейки, значение которой надо получить из предыдущего листа:
=GetPrevSheetValue("A1")
« Последнее редактирование: 04.04.2019, 09:21:30 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #11 : 04.04.2019, 09:25:03 »

Да, спасибо огромное !  Я тоже потратила менее 5 минут на решение этой задачки с помощью VBA. Как жаль, что иногда надо обходиться без него Улыбка.
Записан
Valetnina
Постоялец
***

Репутация: +2/-0
Офлайн Офлайн

Сообщений: 153



Просмотр профиля WWW E-mail
« Ответ #12 : 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")

Единственным ее слабым местом, помимо громоздкости, считаю ГОД, так как неизвестно, как поведет себя она например в следующем году. Доживем - увидим.
Всем удачи и вдохновения Улыбка
И еще раз спасибо.
Записан
Страниц: [1]   Вверх
Печать
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2011, Simple Machines Valid XHTML 1.0! Valid CSS!
Яндекс.Метрика Рейтинг@Mail.ru