Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Суммировать ячейки исключая #Н/Д
 
#1
06.07.2011 09:58:22
{=СУММ(ЕСЛИ((Сравнение!$Y$1:$Y$50000>0%)*(Сравнение!$Y$1:$Y$50000<10%);Сравнение!$G$1:$G$50000))}  
Помогите, пожалуйста.  
Есть диапазон значений на Листе "Сравнение" периодически меняющийся, поэтому в конце диапазона появляется #Н/Д. Из-за этого в приведенной выше формуле также появляется #Н/Д.  
Можно ли как-то усовершенствовать формулу, чтобы этого не происходило.
 
 
 
#2
06.07.2011 10:00:46
{quote}{login=}{date=06.07.2011 09:58}{thema=Суммировать ячейки исключая #Н/Д}{post}{=СУММ(ЕСЛИ((Сравнение!$Y$1:$Y$50000>0%)*(Сравнение!$Y$1:$Y$50000<10%);Сравнение!$G$1:$G$50000))}  
Помогите, пожалуйста.  
Есть диапазон значений на Листе "Сравнение" периодически меняющийся, поэтому в конце диапазона появляется #Н/Д. Из-за этого в приведенной выше формуле также появляется #Н/Д.  
Можно ли как-то усовершенствовать формулу, чтобы этого не происходило.{/post}{/quote}  
Найдите по поиску динамический диапазон со СМЕЩ() например и сделайте его динамическим
 
 
 
#3
06.07.2011 10:02:21
Можно. Лучше показать на примере. Возможно, найдется более оптимальный путь. Например, динамический диапазон, подгоняющий свои размеры под данные.
 
 
 
#4
06.07.2011 10:19:38
Честно говоря впервые слышу про динамические диапазоны. Показать на примере, то есть прикрепить файл для образца?
 
 
 
#5
06.07.2011 10:21:31
да
Редко но метко ...
 
 
 
#6
06.07.2011 10:23:01
Про динамические диапажоны читайте тут:  
http://www.planetaexcel.ru/tip.php?aid=93
Редко но метко ...
 
 
 
#7
06.07.2011 10:40:39
Вот прикрепил.
 
 
 
#8
06.07.2011 10:51:21
просто избавился от ошибки на листе "сравнение"
 
 
 
#9
06.07.2011 11:10:10
Посмотрите такой вариант, с динамическими диапазонами и "Хитрыми форматами".
Редко но метко ...
 
 
 
#10
06.07.2011 11:14:04
Как-то так можно
 
 
 
#11
06.07.2011 12:03:26
Спасибо всем ограмадное, формулы просто волшебные.  
GIG_ant, не могли бы поподробнее объяснить формулу во втором примере. Я и ссылку про динамические диапазоны почитал, но видимо от скудости ума ничего не понял. Понял как делать, но не понял как пользоваться.
 
 
 
#12
06.07.2011 12:12:19
=СМЕЩ(Сравнение!$E$4;А;Б;СЧЁТЗ(Сравнение!$A:$A);Д)  
Смещаем E4 на А строк. В формуле на месте А нет ничего (А=0), начало диапазона не смещается, остается E4.  
Б=0 - смещение по столбцам. Тоже на месте.  
Следующий параметр - высота диапазона (количество строк):  
СЧЁТЗ(Сравнение!$A:$A)  
Лучше применить ПОИСКПОЗ("яя";Сравнение!$A:$A;1)  
Д в формуле нет, по умолчанию ширина диапазона 1 столбец.
 
 
 
#13
06.07.2011 12:15:17
{quote}{login=}{date=06.07.2011 12:03}{thema=}{post}GIG_ant, не могли бы поподробнее объяснить формулу во втором примере. {/post}{/quote}  
 
Какую именно -  формулу диапазонов, или суммпроизв ?
Редко но метко ...
 
 
 
#14
06.07.2011 12:23:37
Еще вариант
 
 
 
#15
06.07.2011 12:26:50
Доброго дня всем!  
Формучане, а почему вот такую формулу не сделать?!  
Или это слишком просто? :) Или есть подводные камни?!  
Подскажите.
 
 
 
#16
06.07.2011 12:27:32
GIG_ant, кажется начинаю догонять, но как Вы сделали, что в ячейке написаны слова, а на самом деле там цифры
 
 
 
#17
06.07.2011 12:29:08
см. формат ячейки
Редко но метко ...
 
 
 
#18
06.07.2011 16:49:11
GIG_ant, спасибо, про формат ячеек понял. Но чего-то не пойму почему формула СУММПРОИЗВ, что там перемножается, по идее просто суммируется.
 
 
 
#19
06.07.2011 17:10:19
Хитрость СУММПРОИЗВ в том что в ней сначала перемножаются все уловия (которые в свою очередь дают на выходе или ноль или необходимую сумму) а потом получившиеся данные суммируются. Если вам непонятно как работает формула воспользуйтесь таким способом:  
Выделите часть формулы (например одно из условий) и нажмите F9 - ексель покажет вам результат вычисления именно этой части формулы, и т. д.
Редко но метко ...
 
 
 
#20
07.07.2011 11:23:14
GIG_ant, спасибо, вроде начинаю понимать. А можно ли как-то сделать, что б считалось кол-во отклонений, с такой же разбивкой по условиям.
 
 
 
#21
07.07.2011 11:28:35
так ?
Редко но метко ...
 
 
 
#22
07.07.2011 11:32:11
А-а-а все гораздо проще, а у меня чуть мозг не взорвался. Спасибо большое.
 
 
 
#23
07.07.2011 13:27:37
Не могли бы подсказать, почему у меня не все ВПРится при использовании СМЕЩ в Имени диапазона  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#24
07.07.2011 13:33:15
Похоже, такого значения нет в именованном диапазоне.  
 
=СМЕЩ(ЛЦ!$C$1;0;0;СЧЁТЗ(ЛЦ!$C:$C);26)  
Зачем диапазон на 26 столбцов?
 
 
 
#25
07.07.2011 13:38:34
Я извиняюсь. Да тех позиций, которые в начале списка, нет в диапазоне.  
Посмотрите пожалуйста в конце списка. Диапазон в 26 столбцов потому, что м-м-м у-у-у э-э-э
 
 
 
#26
11.07.2011 09:34:51
Прикрепил новый файл меньшего размера.  
Кто-нибудь посмотрите пожалуйста почему не все ВПРится.
 
 
 
#27
11.07.2011 09:51:02
Проверьте:  
=СЧЁТЕСЛИ(ЛЦ;$A41)  
Формула покажет "0". Нет такого значения в именованном диапазоне ЛЦ.
 
 
 
#28
11.07.2011 11:02:42
vikttur, у меня нигде нет =СЧЁТЕСЛИ(ЛЦ;$A41)  
Чего-то я не пойму, где смотреть?
 
 
 
#29
11.07.2011 11:51:24
Подправил формулы для динамических диапазонов ЛЦ и Участник01. см. файл
Редко но метко ...
 
 
 
#30
11.07.2011 11:59:00
GIG_ant, а если диапазон начинается не всегда с 7-й строки, а например с 5 или 3. Всегда по разному. Почему нельзя с первой указать?
 
 
 
Страницы: 1 2 След.
Наверх