Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ограничить работу формулы по периоду (или по некой дате)
 
#1
18.03.2018 13:06:54
Уважаемые гуру форума, помогите пожалуйста, решить возникшую проблему с формулой. Итак ситуация: для правильной работы формулы крайне желательно иметь возможность ограничить ее работу некой датой, т.е. для примера: для февраля формула должна считать, а для марта уже нет (можно, разумеется, просто удалить формулу, но это не комильфо). Делать я это решил, используя примечания в определенных ячейках. По задумке, если примечания в контрольной ячейке нет - формула считает, если же есть (примечание представляет собой дату закрытия договора), то формула должна проверить превышает ли контрольная дата на листе дату закрытия договора и, если нет - считать, в противном случае значение =0. Такая проверка (на непревышение даты) необходима, чтобы не отменились подсчеты более ранних периодов. для получения информации из примечания я использую функцию пользователя ,найлденную на просторах сети:
Код
Function Get_Text_from_Comment(rCell As Range)
    Application.Volatile True
    On Error Resume Next
    Get_Text_from_Comment = rCell.Comment.Text
End Function
Все ОК, функция работает. Приступаю к конструированию 1 формулы:
ЕСЛИ(ЕОШ(сравнение контрольной даты и примечания)) - при отсутствии примечания будет ИСТИНА
формула СЧИТАЕТ
в противном случае формула должна смотреть далее

2 формула:
ЕСЛИ(ЕОШ(контрольная дата< примечания))
формула СЧИТАЕТ
в противном случае = 0

Обе вышеописанные формулы считают правильно, в прилагаемом примере, можно проверить это в ячейках Y9 и Y10 листа "Расходы электроэнергии", но при попытке их объединить, вставив 2-ую формулу вместо "смотреть далее", возникает ошибка #ИМЯ? в значении для ЛЖИ (ячейка Y11 примера). Естественно, при попытке внедрения формулы в реальную формулу, тоже ничего не выходит (ячейка Y13 примера). Почему так происходит, формулы работающие правильно по отдельности, не работают будучи объединенными? И как это исправить? Заранее спасибо всем откликнувшимся.

P.S. Для ясности формула должна подсчитать результат за март для Арендатора 2 и НЕ ПОСЧИТАТЬ для Арендатора 3 (договор с которым закрыт), при этом результаты за февраль для обоих арендаторов должны считаться (проверять формулу и на тех ячейках)
Изменено: OlegO - 18.03.2018 13:08:12
 
 
 
#2
18.03.2018 15:59:25
Уважаемые гуру, с названием темы наконец-то разобрались, а с советами по решению проблемы? Подскажите, если кто знает, неохота идею хоронить :cry:  
 
 
 
#3
18.03.2018 18:23:27
Прошу прощения на некоторою назойливость :oops: , но все-таки попробую спросить еще раз. Форумчане, может кто-нибудь объяснить мне ситуацию с моими формулами, в чем там дело? В моей ошибке или принципиальной невозможности использовать пользовательскую функцию внутри формулы? Поймите, хотелось бы знать ответ прежде чем отказываться от задумки которая нравится. А может кто-нибудь сможет подсказать как иным методом (не текущей пользовательской функцией) извлечь информацию из примечания ячейки, каковую информацию можно было бы использовать в дальнейшем в ФОРМУЛАХ? (вот никогда бы не подумал несколько лет назад, что буду пенять на ограничения формул, а теперь вот думаю, что чисто в VBA было бы проще решить данный вопрос, спешу предупредить сразу, в данном случае ПРИХОДИТСЯ использовать формулы). Заранее спасибо
 
 
 
#4
18.03.2018 18:54:42
Доброе время суток.
Давайте, я попробую ответить. По вашим же тестам udf-функция работает внутри формул рабочего листа. В чём проблема? Вы используете как аргумент udf-функции ссылку на объект Range (ссылка на ячейку). Могу только предположить, что когда вы формируете ссылку формулами рабочего листа в некоторых случаях Excel формирует ссылку на ячейку, а в других только значение этой ячейки. Может быть и что-то другое. Но, в этом случае VBA не считает, что аргумент - это объект Range. Попробуйте для исследования попробовать такой вариант, когда при ошибке функция вернёт текст ошибки.
Код
Function Get_Text_from_Comment(rCell As Range)
    Application.Volatile True
    On Error GoTo errHandle
    Get_Text_from_Comment = rCell.Comment.Text
Exit Function
errHandle:
    Get_Text_from_Comment = Err.Description
End Function

Я не формулист, поэтому не могу подсказать, как обернуть её формулами с учётом тех формул, формирующих аргумент для этой функции, чтобы это было близко к полевым испытаниям.
Такое же поведение можно получить, когда создаём имя, но для него формируем не ссылку на диапазон, а некоторый набор формул, возвращающих динамический диапазон по условиям. В этом случае движок Access SQL не считает такое имя именем таблицы (хотя по прямой ссылке на диапазон всё работает). С другой стороны, в элементе управления ListBox формы такое имя прекрасно работает.

Как вариант, предлагаю переделать функцию, чтобы она принимала аргументом текстовую строку. Тогда формулой АДРЕС вы формируете текстовую ссылку на требуемую ячейку, а уже в функции получаете по этому адресу ссылку на ячейку. Для активной книги, например
Код
=Get_Text_from_Comment(АДРЕС(2;3;1;1;"Лист1"))

Код функции будет
Код
Function Get_Text_from_Comment(rCell As String)
    Application.Volatile True
    On Error GoTo errHandle
    Get_Text_from_Comment = Application.Range(rCell).Comment.Text
Exit Function
errHandle:
    Get_Text_from_Comment = Err.Description
End Function
 
 
 
#5
18.03.2018 19:03:19
Андрей, а Вы не могли ли скачать прилагаемый пример, просмотреть мои формулы и указать на причину ошибки? Ситуация-то выглядит больно странно 2 достаточно простые формулы ЕСЛИ работают правильно порознь и НЕ работают будучи объединенными. Если не трудно, просмотрите мой файл-пример, а я пока проверю Ваш совет
 
 
 
#6
18.03.2018 19:25:00
Проверил, могу сказать следующее. Изначально, когда использовал udf в качестве результата она выдавала правильный ответ (текст комментария нужной ячейки), а Ваша измененная udf (в которой (rCell As String)), выдает теперь Method 'Range' of object '_Application' failed. Может эта информация поможет в решении проблемы?
Изменено: OlegO - 18.03.2018 19:26:53
 
 
 
#7
18.03.2018 19:35:07
Хочу пояснить. Ошибка о которой я писал немного необычна, если так можно сказать. Смотрите: использую формулу в которой пытаюсь объединить 2 работающие:
Код
=ЕСЛИ(ЕОШ(Y2<ДАТАЗНАЧ(Get_Text_from_Comment(ИНДЕКС(Spr_arendators;ПОИСКПОЗ($A6;Spis_arendators;0);4))));ИНДЕКС(Data_resulting_e;ПОИСКПОЗ($C5;Index_counts_e;0);СТОЛБЕЦ()-ПОИСКПОЗ("+";$1:$1;0))-СУММЕСЛИМН(Y$3:Y$6;Index_counts_e;$B5;Counts_e;"<>"&$B5);ЕСЛИ(X2<ДАТАЗНАЧ(Get_Text_from_Comment(ИНДЕКС(Spr_arendators;ПОИСКПОЗ($A6;Spis_arendators;0);4)));ИНДЕКС(Data_resulting_e;ПОИСКПОЗ($C5;Index_counts_e;0);СТОЛБЕЦ()-ПОИСКПОЗ("+";$1:$1;0))-СУММЕСЛИМН(Y$3:Y$6;Index_counts_e;$B5;Counts_e;"<>"&$B5);0))
в результате ответ в ячейке = 0 (хотя должен быть иным, для этого арендатора формула должна считать), захожу в формулу, в аргументе "Значение_если_ложь" 1 формулы ЕСЛИ стоит #ИМЯ? хотя повторюсь в ячейке не сообщение об ошибке, а 0
 
 
 
#8
18.03.2018 19:53:53
Цитата
OlegO написал:
Method 'Range' of object '_Application' failed.
Скорее всего не правильно сформирован полный адрес ячейки. Вариант.
Для того чтобы тестировать непосредственно вашу проблему - нужно быть формулистом. Я, увы, не он. Я исхожу из рассуждений, бывших несколько лет назад, когда исследовалось как ведёт себя функция, если ей передаётся диапазон. Если ссылка на диапазон в открытой книге, то аргументом будет Range, а для закрытой - двумерный массив ;)
 
 
 
#9
18.03.2018 20:09:03
книга открытая, соседние листы. Парадокс именно в том, что и udf выдает правильный результат, и индекс, которым ищется ячейка с комментарием, все считает правильно, и ЕСЛИ, используя найденные данные считает правильно, а при объединении 2 функций ЕСЛИ вылезает затык, да еще странный какой-то (подробнее в моем предыдущем посту)
 
 
 
#10
18.03.2018 20:22:40
Андрей, может я совсем к вечеру отупел, но Ваш вариант udf  из последнего поста совсем не хочет считать, =Get_Text_from_Comment(АДРЕС(4;4;;;"Справочник")) выдает ЗНАЧ, хотя именно в ячейке D4 листа Справочник и находится ячейка с комментарием. Очень жаль что Вы не формулист :( . Ау, формулисты форума, отзовитесь, возможно эта задача как раз для Вас  
 
 
 
#11
19.03.2018 17:32:23
Если кому-то покажется интересным, покажу решение которое придумал: сначала проверяем все ячейки диапазона с формулами, определяя нужно ли для конкретной ячейки расчеты или уже нет (вешаем код на активацию листа):
Код
Range(Cells(2, [Data_resulting_e].Column), Cells(Cells(Rows.Count, [Objects_e].Column).End(xlUp).Row, Application.Match("-", Rows(1), 0) - 1)).ClearComments 'снятие комментариев
    x = Range(Cells(2, [Data_resulting_e].Column), Cells(Cells(Rows.Count, [Objects_e].Column).End(xlUp).Row, Application.Match("-", Rows(1), 0) - 1)).Value 'данные в массив
    For j = 1 To Application.Match("-", Rows(1), 0) - Application.Match("+", Rows(1), 0) - 1
        For i = 1 To UBound(x)
            Set note = Application.Index(Sheets("Справочник").Range("D2:D101"), Application.Match(Cells(i + 2, 4), Sheets("Справочник").Range("M2:M101"), 0))

            If Len(Cells(i + 2, 4).Value) > 0 Then 'если есть объект из списка арендаторов
                 If Not note.Comment Is Nothing Then
                    If x(1, j) > CDate(note.Comment.Text) Then
                        Sheets("Расходы электроэнергии").Cells(i+2, j + [Data_resulting_e].Column - 1).AddComment
                        Sheets("Расходы электроэнергии").Cells(i+2, j + [Data_resulting_e].Column - 1).Comment.Text Text:="Договор закрыт"
                    End If
                End If
            End If
        Next i
    Next j
затем используем UDF, только помещая ее "выше" формул расчетов:
ЕСЛИ(Get_Text_from_Comment("нужная ячейка"))=0, т.е. ситуация, когда комментария в ячейке нет
считать формулы
иначе =0
По крайней мере пока, такая конструкция работает, сейчас проверяю на рабочем файле.
Изменено: OlegO - 19.03.2018 17:45:32
 
 
 
Страницы: 1
Наверх