Страницы: 1
RSS
Как сослаться на ячейку, если ссылка определяется двумя ячейками, содержащими даты?
 
Здравствуйте, уважаемые знатоки Excel-я!

Помогите, пожалуйста, разобраться со ссылками на ячейки.

Дано:
1. Произвольный период (начальная дата - конечная дата)
2. Сумма, которую нужно разложить по месяцам периода

Сумму в месяц считаю формулой:

Код
=ЕСЛИ(И(ГОД($A22)=ГОД(Q$2);МЕСЯЦ($A22)=МЕСЯЦ(Q$2);ДЕНЬ($A22)>ДЕНЬ(Q$2);$B22>=КОНМЕСЯЦА(Q$2;0));ОКРУГЛ($D22/($B22-$A22+1)*(КОНМЕСЯЦА(Q$2;0)-$A22+1);2);
ЕСЛИ(И($A22<=Q$2;$B22>=КОНМЕСЯЦА(Q$2;0));ОКРУГЛ(($D22-$H22-$R22)/РАЗНДАТ(КОНМЕСЯЦА($A22;0);КОНМЕСЯЦА($B22;-1);"m");2);
ЕСЛИ(И(ГОД($B22)=ГОД(Q$2);МЕСЯЦ($B22)=МЕСЯЦ(Q$2);ДЕНЬ($B22)>ДЕНЬ(Q$2);$B22<КОНМЕСЯЦА(Q$2;0));ОКРУГЛ($D22/($B22-$A22+1)*($B22-(Q$2-1));2);"Хрень")))


Какой месяц обсчитывается? - неполный (первый или последний) или полный - определяется сравнением "дата начала" и "дата конца" с "месяцами в шапке" (H2-U2)

По моей затее, чтобы правильно считать "промежуточные" (полные) месяцы, нужно отбросить суммы первого и последнего месяца.
во втором ЕСЛИ формулы - ($D22-$H22-$R22)
... но "границы периодов" плавающие, например, строка 12 (см. файл)

ВОПРОС: как сослаться на ячейки с суммами первого ($H22) и последнего ($R22) месяца периода.
Насколько я понимаю, исходить нужно из соответствующих ячеек с датами начала и конца периода и "МесяцГод" в "шапке" (строка 2).

В ячейке Q22 первая и последняя ячейки определены вручную правильно, поэтому ошибки нет.
Изменено: Михаил Нарвич - 30.06.2024 00:24:52
 
Здравствуйте.
Посмотрите вариант в файле. С трудом понял что вы хотите и не факт что правильно понял.
Код
=ОКРУГЛ(ЕСЛИ(ИЛИ(КОНМЕСЯЦА(H$2;0)<КОНМЕСЯЦА($A26;0);$B26<H$2);0;$D26/$C26*(МИН(КОНМЕСЯЦА(H$2;0);$B26)-МАКС(H$2;$A26)+1));2)
 
Цитата
Михаил Нарвич: Как сослаться на ячейку
Да уж...   Михаил Нарвич, вам, однако,  надо сумму распределить по месяцам? и с округлением?
вариант если столбец G делать пустым:
=ЕСЛИОШИБКА(ОКРУГЛ(($D3-СУММ($G3:G3))/($C3-МАКС(H$2-$A3;))*МАКС(МИН($B3;КОНМЕСЯЦА(H$2;0))-МАКС($A3;H$2)+1; );2); )
или "с конца":
=ОКРУГЛ(($D16-СУММ(I16:$AE16))/($C16-МАКС($B16-КОНМЕСЯЦА(H$2;0); ))*МАКС(МИН($B16;КОНМЕСЯЦА(H$2;0))-МАКС($A16;H$2)+1; );2)
Изменено: Павел \Ʌ/ - 30.06.2024 08:37:12
 
Цитата
gling написал:
Посмотрите вариант в файле. С трудом понял что вы хотите и не факт что правильно понял.

gling и Павел \Ʌ/, спасибо, но "нет". Наверное, мне нужно ещё поработать над вопросом, на который хочу получить ответ.
Изменено: Михаил Нарвич - 30.06.2024 09:02:19
 
Похоже, что я перемудрил с постановкой задачи  :oops:

Верхняя часть моей таблицы (строки 2-12) работает правильно. Но там расчёт суммы каждого месяца производится пропорционально количеству дней в данном месяце.

Я же хочу, чтобы только первый и последний месяцы периода (если они неполные) считались пропорционально дням, о остальные (промежуточные) месяцы считались как "остаток суммы делённый на количество месяцев". То есть в промежуточных месяцах суммы должны быть одинаковые.

Поэтому, вторая часть таблицы (строки 16-22), в целом, тоже работает правильно, но ... только, если в формуле я вручную указываю первый и последний месяц ($D22-$H22-$R22).

Мне нужно из общей суммы вычесть сумму первого и последнего месяца, а для этого нужно сослаться на них в формуле ($D22-$H22-$R22), а для этого я должен научиться "вычислять" их адрес.
Изменено: Михаил Нарвич - 30.06.2024 09:05:11
 
Попробую по-другому сформулировать задачу.

(работаем только со строкой 22)

Значение ячейки в столбцах H-Z рассчитывается по трём правилам:
1. "Неполный месяц в начале периода"
2. "Неполный месяц в конце периода"
3. "Полный месяц"

Задача: Определить адрес ячейки, в которой сработало правило 1 или 2, для дальнейшего использования в формуле.
Изменено: Михаил Нарвич - 30.06.2024 09:25:38
 
например так
=ЕСЛИ(И(H$2<=$B22;КОНМЕСЯЦА(H$2;0)>=$B22);$D22-СУММ($G22:G22);ОКРУГЛ(ЕСЛИ(И(H$2<=$A22;КОНМЕСЯЦА(H$2;0)>=$A22); КОНМЕСЯЦА(H$2;0)-$A22+1;ЕСЛИ(И(H$2>=$A22;КОНМЕСЯЦА(H$2;0)<=$B22);30,444; ))*$D22/$C22;2))
это значение 30,444 можно заменить на более точное
(КОНМЕСЯЦА($B22;-1)-КОНМЕСЯЦА($A22;0))/ОКРУГЛ((КОНМЕСЯЦА($B22;-1)-КОНМЕСЯЦА($A22;0))/30,44;)
(столбец G пуст)
Изменено: Павел \Ʌ/ - 30.06.2024 09:57:05
 
Павел \Ʌ/, спасибо!
Пошёл разбираться в предложенном варианте.
 
Цитата
Михаил Нарвич написал:
Мне нужно из общей суммы вычесть сумму первого и последнего месяца, а для этого нужно сослаться на них в формуле ($D22-$H22-$R22), а для этого я должен научиться "вычислять" их адрес.

В общем, текущая задача решена. Всем спасибо! "Вопрос" снимается!

Совершенно случайно до меня дошло, что мне нужны не адреса искомых ячеек, а числовые значения в них.
Эти самые значения я получил функцией ГПР, о которой когда-то слышал, но применить - не было подходящего случая.

$D22-$H22-$R22

$D22-(ГПР(КОНМЕСЯЦА($A22;-1)+1;$H$2:$R$22;СТРОКА($A22)-СТРОКА(H$2)+1))-(ГПР(КОНМЕСЯЦА($B22;-1)+1;$H$2:$R$22;СТРОКА($B22)-СТРОКА(H$2)+1))

Осталось разобраться с тем, как динамически задавать "таблицу" в ГПР (например, $H$2:$R$22), и устранить ошибки, связанные с частными случаями, которые вылезли после последней доработки.

P.S. Ответ на вопрос "на кой чёрт такие страдания?" - решаю, как могу, бухгалтерскую задачу "доходы будущих периодов".
Изменено: Михаил Нарвич - 30.06.2024 23:13:11
 
Попробуйте так: это
Код
СТРОКА($A22)-СТРОКА(H$2)+1
заменить на это
Код
СТРОКА($A22)-1
нет смысла ссылаться на номер закрепленной строки так как она всегда равна 2, а следовательно 2-1=1
и
Код
$H$2:$U$22
замените на
Код
$H$2:ИНДЕКС($U:$U;ПОИСКПОЗ(9^9;$A:$A;1))
 
gling, спасибо!

Сейчас как раз смотрю в ролик Николая про ИНДЕКС и ПОИСКПОЗ (надо же в матчасти разобраться).

Правда, меня терзает одна мысль ...
В связи с тем, что "время не стоит на месте", очевидно предполагается рост таблицы вправо - за счёт появления новых месяцев и даже лет.
Поэтому я больше склоняюсь к определению границ ГПР-таблицы по принципу, как искал начало и конец расчётного диапазона, т.е. сопоставлением даты начала периода и датой в заголовке столбца. Но тут уж точно нужно разбираться с получением адреса ячейки.
Изменено: Михаил Нарвич - 30.06.2024 23:26:12
 
Цитата
написал:
смотрю в сторону ИНДЕКС и ПОИСКПОЗ.
Можно и смещением на 140 месяцев вправо
Код
СМЕЩ($H$2;;;ПОИСКПОЗ(9^9;$A:$A;1)-1;140)
, но мне больше нравится ИНДЕКС и ПОИСКПОЗ. Задайте последний столбец не U а Z например, если вырастет таблица вправо, ну или с еще большим запасом XFD, это последний столбец листа в Excel 2016.
Код
$H$2:ИНДЕКС($U:$U;ПОИСКПОЗ(9^9;$XFD:$XFD;1))
Изменено: gling - 30.06.2024 23:37:43
 
gling, спасибо за подсказки!

Пошёл изучать и думать.
 
ВОПРОС: как из этого получить адреса двух ячеек?

План определения диапазона для ГПР (для строки 22)

* A22 - дата начала периода; B22 - дата конца периода

1. Координаты начала диапазона (ячейка H2)
         строка = 2
         столбец = ГПР(КОНМЕСЯЦА($A22;-1)+1;2:2;1) = "янв 2024"

2. Координаты конца диапазона (ячейка R22)
         строка = СТРОКА($B22) = 22
         столбец = ГПР(КОНМЕСЯЦА($B22;-1)+1;2:2;1) = "ноя 2024"
Изменено: Михаил Нарвич - 01.07.2024 00:54:45
 
Нашёл похожую тему - ушёл изучать.

Сходил. Изучил. Результаты неутешительные: мои представления о получении адреса ячейки - не более чем фантазии.

Возвращаемся к функциям ИНДЕКС, ПОИСКПОЗ и СМЕЩ.
Изменено: Михаил Нарвич - 01.07.2024 01:19:27
 
Цитата
написал:
ГПР(КОНМЕСЯЦА($A22;-1)+1;2:2;1) = "янв 2024"
Не понятно что вы хотите получить. В формуле у вас ГПР работает, только чтобы функция работала правильно нужно добавить последний аргумент ЛОЖЬ или ИСТИНА (0 или 1). По вашей формуле вы хотите получить дату из ячейки второй строки, для чего это нужно, мне не понятно.
Если вы запишите формулу так
Код
=ГПР(--ТЕКСТ($A22;"М.ГГ");$H$2:$R$22;1;0)
то получите эту дату в числовом формате, но номер столбца вы этой формулой не получите. Отображение даты будет зависеть от формата ячейки. Хотите получить номер столбца используйте ПОИСКПОЗ()
 
gling, да, я уже понял, что
Цитата
Хотите получить номер столбца используйте ПОИСКПОЗ()
 
Цитата
gling написал:
Не понятно что вы хотите получить.

Я надеялся, что кто-то более опытный знает, как указанные "ориентиры" преобразовать в адрес. Но в соседнем форуме такому же наивному как я доходчиво объяснили, что такой возможности НЕТ.
 
Если задача осталось прежней "Разбивка суммы по месяцам", посмотрите вариант в файле.
С РАЗНДАТ() месяцы считаются не так как вам надо. РАЗНДАТ(15.01.24;16.02.24;"m")=1, а вам этого не надо.
Таблицу в файле иожно увеличивать вправо, формула не сломается.
Код
=ОКРУГЛ(ЕСЛИ(И(КОНМЕСЯЦА($A23;0)=КОНМЕСЯЦА(H$2;0);$A23>=H$2);(КОНМЕСЯЦА(H$2;0)-$A23+1)*$D23/$C23;ЕСЛИ(И(КОНМЕСЯЦА($B23;0)=КОНМЕСЯЦА(H$2;0);$B23>H$2);ДЕНЬ($B23)*$D23/$C23;ЕСЛИ(И(H$2>=$A23;H$2<=$B23);($D23-(КОНМЕСЯЦА($A23;0)-$A23+1+ДЕНЬ($B23))*$D23/$C23)/(ПОИСКПОЗ($B23;2:2;1)-ПОИСКПОЗ($A23;2:2;1)-1);0)));2)
Изменено: gling - 01.07.2024 09:55:35
 
gling, спасибо! Обязательно посмотрю.
Устранение ошибок для меня важно.
 
Цитата
Михаил Нарвич написал:
во втором ЕСЛИ формулы - ($D22-$H22-$R22)
... но "границы периодов" плавающие, например, строка 22, 23, 24 (см. файл)

ВОПРОС: как сослаться на ячейки с суммами первого ($H22) и последнего ($R22) месяца периода.
Насколько я понимаю, исходить нужно из соответствующих ячеек с датами начала и конца периода и "МесяцГод" в "шапке" (строка 2).

В общем, методом "научного тыка" пришёл я к решению в том виде, который хотел увидеть изначально.
Формула $D22-$H22-$R22 теперь выглядит вот так:
$D22-(ДВССЫЛ(АДРЕС(ЯЧЕЙКА("строка";$A22);ПОИСКПОЗ(КОНМЕСЯЦА($A22;-1)+1;$2:$2))))-(ДВССЫЛ(АДРЕС(ЯЧЕЙКА("строка";$B22);ПОИСКПОЗ(КОНМЕСЯЦА($B22;-1)+1;$2:$2))))

Всех благодарю за обсуждение! Благодаря вам я применил функции, которые раньше "обходил стороной".
ВОПРОС ЗАКРЫТ !

P.S. Один из частных случаев (все месяцы полные) добавил в начало формулы ещё три ЕСЛИ. В целом, работа таблицы, на данный момент, меня полностью устраивает. "Копеечную неточность" планирую, в будущем, добавлять в последний месяц.
Изменено: Михаил Нарвич - 08.07.2024 00:35:07
 
офф
Цитата
Михаил Нарвич:
=ЕСЛИ(И($A22=H$2;$B22=КОНМЕСЯЦА($B22;0));ОКРУГЛ($D22/РАЗНДАТ(КОНМЕСЯЦА($A22;-1);КОНМЕСЯЦА($B22;0);"m");2);
ЕСЛИ(И($A22<H$2;$B22>КОНМЕСЯЦА(H$2;0);ДЕНЬ($A22)=1;ДЕНЬ($B22)=ДЕНЬ(КОНМЕСЯЦА($B22;0)));ОКРУГЛ($D22/РАЗНДАТ(КОНМЕСЯЦА($A22;-1);КОНМЕСЯЦА($B22;0);"m");2);
ЕСЛИ(И($B22=КОНМЕСЯЦА(H$2;0);$A22=КОНМЕСЯЦА($A22;-1)+1);ОКРУГЛ($D22/РАЗНДАТ(КОНМЕСЯЦА($A22;-1);КОНМЕСЯЦА($B22;0);"m");2);


ЕСЛИ(И(ГОД($A22)=ГОД(H$2);МЕСЯЦ($A22)=МЕСЯЦ(H$2);ДЕНЬ($A22)>ДЕНЬ(H$2);$B22>=КОНМЕСЯЦА(H$2;0));ОКРУГЛ($D22/($B22-$A22+1)*(КОНМЕСЯЦА(H$2;0)-$A22+1);2);
ЕСЛИ(И($A22<=H$2;$B22>=КОНМЕСЯЦА(H$2;0);ДЕНЬ($A22)>1;ДЕНЬ($B22)<КОНМЕСЯЦА($B22;0));
           ОКРУГЛ(($D22-(@ДВССЫЛ(АДРЕС(@ЯЧЕЙКА("строка";$A22);ПОИСКПОЗ(КОНМЕСЯЦА($A22;-1)+1;$2:$2))))-(@ДВССЫЛ(АДРЕС(@ЯЧЕЙКА("строка";$B22);ПОИСКПОЗ(КОНМЕСЯЦА($B22;-1)+1;$2:$2)))))/РАЗНДАТ(КОНМЕСЯЦА($A22;0);КОНМЕСЯЦА($B22;-1);"m");2);
ЕСЛИ(И(ГОД($B22)=ГОД(H$2);МЕСЯЦ($B22)=МЕСЯЦ(H$2);ДЕНЬ($B22)>ДЕНЬ(H$2);$B22<КОНМЕСЯЦА(H$2;0));ОКРУГЛ($D22/($B22-$A22+1)*($B22-(H$2-1));2);""))))))
ВОПРОС ЗАКРЫТ
Сколько времени надо, чтоб опять открыл?  :)  
похоже
Цитата
Пошёл разбираться в предложенном варианте
не разобрался
Изменено: Павел \Ʌ/ - 08.07.2024 11:24:01
 
Цитата
Павел \Ʌ/ написал:
"Пошёл разбираться в предложенном варианте" ... похоже, не разобрался

Павел \Ʌ/, я Ваш вариант пробовал, но мне не понравился "математический" результат, поэтому дальнейшие "разбирательства" были слегка отложены.

Цитата
Павел \Ʌ/ написал:
Сколько времени надо, чтоб опять открыл?  

В таблице, которая будет обсчитываться моим решением (возможно, не совсем идеальным), строк гораздо больше чем в примере, и все комбинации достаточно разнообразные. Поэтому, как только в столбце "Check" появится число "из ряда вон выходящее", я вернусь к этой головоломке, а если не справлюсь, то не постесняюсь здесь спросить.

Исходную задачу "как в формуле сослаться на крайние значения в моей "числовой диаграмме Ганта"" я считаю, что решил. Все ваши предложения пошли мне на пользу, т.к., в той или иной степени, корректировали ход моих мыслей (за что я выше сказал СПАСИБО).
Изменено: Михаил Нарвич - 08.07.2024 12:29:13
 
Михаил Нарвич, пожалуста
Не хотел вас сильно задеть (только слегка)  ;)
Собственно, подобные изыскания  для саморазвития полезны. Хочу только отметить, что некоторые используемые вами функции волатильны (или как здесь иной раз говорят: "летучи") т.е. если не хотите чтобы "при каждом шорохе" у вас всё пересчитывалось лучше их не использовать.
Цитата
вернусь к этой головоломке
потестируйте пока, например, такие даты для A22 и B22 : 01.01.2024, 01.11.2024, 30.11.2024 ...
 
Павел \Ʌ/, спасибо за подсказку!
Посмотрю обязательно, даже несмотря на то, что, теоретически, все периоды кратны целым месяцам. Как минимум, сделаю защиту от невнимательных - "Установите правильные даты!"
Изменено: Михаил Нарвич - 08.07.2024 16:32:29
Страницы: 1
Наверх