Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
СУММПРОИЗВ ссылается на диапазоны с пустыми строками
 
#1
11.10.2012 13:30:24
Здравствуйте!  
В файле пример формулы СУММПРОИЗВ. В ячейке G1 она работает, ы ячейке G2 - нет. Разница в том, что во второй формуле формула ссылается на диапазон с пустыми строками.  
Так и должно быть? Скажите, пожалуйста, можно от этого уйти, но именно в СУММПРОИЗВ?
 
 
 
#2
11.10.2012 13:59:22
СУММПРОИЗВ() не любит пустоты.  
=СУММ(ЕСЛИ($H$2=Лист1!$A1:$A21;ЕСЛИ($H$1=Лист1!$B1:$B21;Лист1!$C1:$C21)))  
 
Как вводить формулу массива:  
После введения формулы в ячейку, ввод завершается нажатием не просто Enter, а Ctrl+Shift+Enter. Формула при этом будет заключена в фигурные скобки - {}.
 
 
 
#3
11.10.2012 14:16:54
Спасибо, Vikkur, за консультацию по СУММПРОИЗВ.  
Формулу массива можно и такую использовать  
=ИНДЕКС(C1:C21;ПОИСКПОЗ(1;ЕСЛИ(A1:A21=$H$2;ЕСЛИ(B1:B21=$H$1;1));0))  
хотелось обойтись без.  
Проблема еще в том, что диапазон - это другой файл, СУММЕСЛИМН не обновляется без открытия.
 
 
 
#4
11.10.2012 14:21:40
Можно и простую:  
=ВПР(H2;ИНДЕКС(A1:A21;ПОИСКПОЗ(H1;B1:B21;)):ИНДЕКС(C1:C21;ПОИСКПОЗ(H1;B1:B21;)+СЧЁТЕСЛИ(B1:B21;H1)-2);3;)
 
 
 
#5
11.10.2012 14:34:55
Vikktur, спасибо. Формула для понимания получилась еще сложнее, чем массивная )))
 
 
 
#6
11.10.2012 16:08:00
А если проще? :)  
=ВПР(H2;начало_диапазона:конец_диапазона;3;)  
 
Начало_диапазона - ячейка столбца А:  
ИНДЕКС(A1:A21;ПОИСКПОЗ(H1;B1:B21;))  
 
Конец_диапазона - ячейка столбца С:  
ИНДЕКС(C1:C21;ПОИСКПОЗ(H1;B1:B21;)+СЧЁТЕСЛИ(B1:B21;H1)-2)
 
 
 
#7
11.10.2012 16:29:52
Vikktur, спасибо, за объяснение. Это я понял.  
 
ИНДЕКС(A1:A21;ПОИСКПОЗ(H1;B1:B21;)) - находит первую Н1 в В1:В21 и возвращает номер строки, столбец берется А. Так мы нашли левую верхнюю точку диапазона.  
 
ИНДЕКС(C1:C21;ПОИСКПОЗ(H1;B1:B21;) - выдаст строку верхней точки, но столбец С  
получается  Ах:Сх  
 
+СЧЁТЕСЛИ(B1:B21;H1)-2) посчитает количество критериев в колонке    
получается Ах:Сх+количество критериев  
-2 - потому что итог по отделу через строку написан, можно опустить наверное  
 
Отсюда вывод, что формула работает если таблица отсортирована по столбцу В.  
Это главный вопрос.
 
 
 
#8
11.10.2012 16:37:35
-2 - потому что нашли первое вхождение и посчитали количество, получилось на 1 больше, а еще лишняя запись (где ИТОГО).  
 
Да, сортировка по В обязательна.
 
 
 
#9
11.10.2012 16:43:47
Спасибо, что помогли разобраться.
 
 
 
Страницы: 1
Наверх