Новости:

Новая редакция правил форума: 2.4. Если вопрос или ответ содержится во вложенном файле, все-равно кратко описывайте в сообщении вопрос или суть решения. Это необходимо, чтобы тему можно было найти через поиск.

Главное меню

Изменение ссылки в формуле по условию

Автор Димычч, 12.09.2014, 11:33

« назад - далее »

Димычч

Добрый день.
Прошу прощения, если если тема уже где-то раскрыта, но найти не получилось...
Имеем формулу: =СУММ(A4:I4)
Задача: изменять столбец I в этой формуле на любой от A до L, в зависимости от условия, в данном случае, от текущего месяца.
Нужную букву нахожу через "ПРОСМОТР" в ячейке М6.
Самую близкую по смыслу функцию, которую я нашёл - ДВССЫЛ, но она почему-то не даёт выполнять другие функции внутри себя. Например, формула
=ДВССЫЛ("СУММ(A4:"&ПРОСМОТР(МЕСЯЦ(A1);A9:A20;B9:B20)&"4")
не работает.

cheshiki1

#1
вот так должна работать. :)
=СУММ(ДВССЫЛ("A4:"&ПРОСМОТР(МЕСЯЦ(A1);A9:A20;B9:B20)&"4"))
но так лучше
=СУММ(A4:ИНДЕКС($A$4:$L$4;;ПОИСКПОЗ(ТЕКСТ(M1;"ММММ");$A$3:$L$3;0)))
или
=СУММ(A4:ИНДЕКС($A$4:$L$4;;МЕСЯЦ(M1)))

gling

Можно так =СУММ(СМЕЩ($A$4;0;0;1;ПОИСКПОЗ($M$1;$A$3:$L$3;0)))
mail: vovik100661@gmail.com;
ЯД-41001506838083.

cheshiki1

gling ф-ция СМЕЩ как и ДВССЫЛ летучие, т.е. пересчитываются при каждых изменениях на листе и если их на листе много могут привести к торможению. Поэтому если есть возможность использовать другой вариант то лучше воспользоваться им.

Димычч

Спасибо, это правда круто.... Чувствую себя просветлённым идиотом :) Но вот понять, как сработала самая маленькая формула
=СУММ(A4:ИНДЕКС($A$4:$L$4;;МЕСЯЦ(M1)))
видимо оперативной памяти у меня в голове не хватает. Там же после первого двоеточия возвращается значение "10"?

cheshiki1

#5
ЦитироватьТам же после первого двоеточия возвращается значение "10"?
не угадали. :) в таком исполнении возвращается $H$4.
что бы посмотреть как работает формула для 2007 и выше:
вкладка "Формулы"-кнопка "вычислить формулу". жмем далее и смотрим что происходит.

_Boroda_

Можно вообще без дополнительных ячеек
=СУММПРОИЗВ(A4:L4*(ТДАТА()>--("1/"&A3:L3)))
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

zs

если месяца обозначены в формате даты, то возможно использование и такой формулы
=СУММЕСЛИ(A3:L3;"<"&$M$1;A4:L4)  ;)

Димычч

Собрал в кучку все варианты :) Подправил формулы под разные форматы периодов. Не разобрался только почему не работает вариант _Boroda_ с форматом даты.

_Boroda_

Потому, что в случае дат все проще - не нужно преобразовывать текст в дату, она и так уже есть
=СУММПРОИЗВ(A9:L9*(ТДАТА()>A3:L3))
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Димычч

Спасибо. Много же времени мне понадобилось чтобы понять, что на "истина" тоже можно умножать...