Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Плавающий диапазон для функции СЧЁТЕСЛИМН
 
Доброго времени суток, уважаемые гуру форума. Подскажите, можно ли решить возникшую проблему. Имеется формула с использованием СЧЁТЕСЛИМН (ячейка S3 лист "КП арендаторов" примера):
Код
=СЧЁТЕСЛИМН('Расходы электроэнергии'!CE3:CE8;ИНДЕКС(Data_resulting_e;ПОИСКПОЗ($A4;Objects_e;0);ПОИСКПОЗ($B3;Date_DRE;0));Counts_e;СУММПРОИЗВ((INA_e=ВПР($A3;Spr_arendators;СТОЛБЕЦ(INA_spr);0))*Counts_e))
формула считает правильно, НО мне необходимо, чтобы диапазон_условия_1 - 'Расходы электроэнергии'!CE3:CE8 был скользящим, т.е. для следующей ячейки (S4) он должен быть 'Расходы электроэнергии'!CF3:CF8  ну и т.д. Причем, учитывая особенности реального файла, протягивание формулы мне не подходит категорически, диапазон ДОЛЖЕН быть вычислен. Я могу вычислить начальную и конечную ячейку диапазона через ИНДЕКС (ячейки S7 и S8):
Код
=ИНДЕКС(Data_resulting_e;1;ПОИСКПОЗ($B3;Date_DRE;0)) 
=ИНДЕКС(Data_resulting_e;8;ПОИСКПОЗ($B3;Date_DRE;0))
но когда я пытаюсь так задать диапазон:
Код
ИНДЕКС(Data_resulting_e;1;ПОИСКПОЗ($B3;Date_DRE;0)): ИНДЕКС(Data_resulting_e;8;ПОИСКПОЗ($B3;Date_DRE;0))
функция говорит об ошибка ССЫЛКА. Пытался использовать для вычисления первой и последней ячеек диапазона АДРЕС:
Код
=АДРЕС(3;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0);;;"'Расходы электроэнергии'")
=АДРЕС(8;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0);;;"'Расходы электроэнергии'")
результат вычисления правильный, ячейки СЕ3 и СЕ8, но диапазон все равно не получается задать, та же ошибка. Так вот вопрос: можно ли задать диапазон для вычислений СУММЕСЛИМН не жестко прописанным вручную или через имя, а плавающим, вычисленным для каждой ячейки индивидуальным? Заранее спасибо всем откликнувшимся.
P.S. Учитывая объем вычислений в реальном файле, как мне кажется, крайне нежелательно использование волатильных функций
 
Цитата
OlegO написал: результат вычисления правильный, ячейки СЕ3 и СЕ8, но диапазон все равно не получается задать
Потому что результат работы АДРЕС - не ссылка, а обычный текст

А ошибка при применении ИНДЕКС - так в S8 видно же, что такая формула показывает ошибку... А разбираться в нескольких именоваых диапазонах нет желания.
 
CE3:CE8 - INDEX('Расходы электроэнергии'!3:8;1;COLUMN($CE$1)-1+ROW(A1)):INDEX('Расходы электроэнергии'!3:8;6;COLUMN($CE$1)-1+ROW(A1))
=COUNTIFS(INDEX('Расходы электроэнергии'!3:8;1;COLUMN($CE$1)-1+ROW(A1)):INDEX('Расходы электроэнергии'!3:8;6;COLUMN($CE$1)-1+ROW(A1));INDEX(Data_resulting_e;MATCH($A4;Objects_e;0);MATCH($B3;Date_DRE;0));Counts_e;SUMPRODUCT((INA_e=VLOOKUP($A3;Spr_arendators;COLUMN(INA_spr);0))*Counts_e))
 
Цитата
vikttur написал:
так в S8 видно же, что такая формула показывает ошибку..
Спасибо, vikttur, на указание места где сидела ошибка. Действительно, указываю в качестве источника для ИНДЕКС именованный диапазон, высотой (в данном случае) в 6 строк, а в качестве нижней границы ставлю 8 :oops: . Так что вариант:
Код
=СЧЁТЕСЛИМН(ИНДЕКС(Data_resulting_e;1;ПОИСКПОЗ($B3;Date_DRE;0)):ИНДЕКС(Data_resulting_e;6;ПОИСКПОЗ($B3;Date_DRE;0));ИНДЕКС(Data_resulting_e;ПОИСКПОЗ($A4;Objects_e;0);ПОИСКПОЗ($B3;Date_DRE;0));Counts_e;СУММПРОИЗВ((INA_e=ВПР($A3;Spr_arendators;СТОЛБЕЦ(INA_spr);0))*Counts_e))
заработал. Сейчас буду проверять на реальном файле. Еще раз спасибо за помощь.
 
И Вам большое спасибо за помощь, БМВ, но Ваш вариант подходит немного меньше, ведь конкретное указание на столбец COLUMN($CE$1), мне не подходит, все равно придется каким-либо образом вычислять этот столбец, а вариант с заменой COLUMN($CE$1) на ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0) получается немного длинее. И еще, исключительно в порядке замечания, а не критики, зачем в Вашем варианте используется -1+ROW(A1) ;)  
 
Столбец указан тодько начальный. Все остальные от него считаются. Вы попробуйте.
Изменено: БМВ - 22 Мар 2018 08:27:54
 
Цитата
БМВ написал:
Вы попробуйте
Да попробовал. Повторюсь, Ваш вариант, пусть и чуть измененный мною, работает правильно, просто он длиннее:
ИНДЕКС(Data_resulting_e;1;ПОИСКПОЗ($B3;Date_DRE;0)):ИНДЕКС(Data_resulting_e;6;ПОИСКПОЗ($B3;Date_DRE;0)) - мой текущий вариант
ИНДЕКС('Расходы электроэнергии'!3:8;1;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0)):ИНДЕКС('Расходы электроэнергии'!3:8;6;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0)) - Ваш вариант, подкорректированный мною
А что касается конкретного указания пусть даже первоначального столбца, то, как я понимаю, мне это не подойдет, учитывая особенности реального файла (при добавлении года на лист "Расходов..." столбец неизбежно сдвинется, поэтому я и писал в 1 посте, что диапазон ДОЛЖЕН быть вычислен (через ПОИСКПОЗ) для каждой ячейки, пусть даже это и затратнее по объемам вычислений
Изменено: OlegO - 22 Мар 2018 09:06:44
 
ну длинно можно сократить, я общий случай изобразил и  длинный
ИНДЕКС('Расходы электроэнергии'!3:8;1;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0)):ИНДЕКС('Расходы электроэнергии'!3:8;6;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0))
можно записать так:
ИНДЕКС('Расходы электроэнергии'!3:8;0;ПОИСКПОЗ($B3;Date_DRE;0)+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0))
А если месяца идут подряд то
ИНДЕКС('Расходы электроэнергии'!3:8;0;)-1+ROW(A1))+ПОИСКПОЗ("+";'Расходы электроэнергии'!$1:$1;0))
ROW(A1) даст смещение по столбцам в формулах , которые множатся в строках.
Изменено: БМВ - 22 Мар 2018 09:29:04
 
Спасибо за обновленные варианты. Скажите, БМВ, как более правильно следует указывать диапазон, вычисляя через индекс и первую и последнюю ячейку диапазона или так как Вы показали в последнем Вашем посте?
 
Разницы особой нет.  Вопрос в потребностях и возможностях. Если возможно указать размер исходного диапазона, равный по одному из измерений требуемому, то 0 определит, что берется вся строка или столбец.
Изменено: БМВ - 23 Мар 2018 08:30:31
 
спасибо
Страницы: 1
Читают тему (гостей: 1)