Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Умная таблица, суммпроизв, выставление фильтров.
 
#1
08.08.2014 10:55:47
Здравствуйте!
Исходные данные:
1) Есть таблица , в которой ведется запись и расчет затрат по конкретной операции для конкретного подразделения.
2) Есть итоговая таблица в которой считаются все затраты по конкретному подразделению.
Проблема:
1) Итоговая отчетность необходима за каждый месяц.
2) Т.е. необходимо, чтобы функция суммпроизв в итоговой таблице учитывала затраты только , например, за август.

Пытался решить вопрос через промежуточные итоги, однако не смог придумать рабочий алгоритм + нашел информацию на форуме, что эта функция не работает в связке с умной таблице.
формула в итоговой таблице выглядит так : =СУММПРОИЗВ((журнал[Подразделение]=$C2)*журнал[Затраты] ;)

Решение: ???
 
 
 
#2
08.08.2014 12:42:17
условно дату поставил в ячейку F2 (в дате важен год и месяц, с ними идет сравнение)
заменить формулу на
Код
=СУММПРОИЗВ((журнал[Дата и исходящий
номер документа]>=ДАТА(ГОД($F$2);МЕСЯЦ($F$2);1))*(журнал[Дата и исходящий
номер документа]<=ДАТА(ГОД($F$2);МЕСЯЦ($F$2)+1;0))*(журнал[Подразделение]=$B2)*(журнал[Затраты]))
 
 
 
#3
08.08.2014 13:44:23
Я прошу прощения, а Вас не затруднит формулу применить в таблице приложенной. Не очень понимаю принцип.
 
 
 
#4
08.08.2014 15:19:59
Смогу только вечером, с рабочего места файлы нельзя отправлять
 
 
 
#5
08.08.2014 21:13:48
вот и сам файл
 
 
 
#6
11.08.2014 10:12:36
Здорово, спасибо! А если нужно не календарный месяц, а  с 25 по 25 например, я просто в функции ДАТА добавляю ещё уточнение по дню?
Типа
>=ДАТА(ГОД($F$2);МЕСЯЦ($F$2);25)
или
>=ДАТА(ГОД($F$2);МЕСЯЦ($F$2);ДЕНЬ($F$2))
?
Изменено: altervister - 11.08.2014 10:16:45
 
 
 
#7
11.08.2014 10:25:27
Да также можно и за квартал делать, нужно просто месяц указывать и день указывать. Если делается за период лучше делать ссылки на ячейки, так будет удобнее, типа ячейка F2 (это с какого периода), ячейка G2 (по какой период). Ну и в формуле соответствующие изменения сделать.
 
 
 
#8
25.03.2025 00:11:23
Цитата
написал:
Код=СУММПРОИЗВ((журнал[Дата и исходящий
номер документа]>=ДАТА(ГОД($F$2);МЕСЯЦ($F$2);1))*(журнал[Дата и исходящий
номер документа]<=ДАТА(ГОД($F$2);МЕСЯЦ($F$2)+1;0))*(журнал[Подразделение]=$B2)*(журнал[Затраты]))
Всем привет!
А если в функции СуммПроизв сравнивать значение в ячейке с диапазоном чисел вместо даты, то как записывается формула?
(журнал[Дата и исходящий номер документа]>=ДАТА(ГОД($F$2);МЕСЯЦ($F$2);1)
А мне нужно при условии в функции СуммПроизв, что данные в диапазоне А:A меньше значения в конкретной ячейке C2.
Подскажите плиз
 
 
 
#9
25.03.2025 00:33:34
Цитата
JRO написал:
что данные в диапазоне А:A меньше значения в конкретной ячейке C2
- так и пишите: *(А:A<C2)
 
 
 
#10
25.03.2025 14:36:12
Цитата
написал:
- так и пишите: *(А:A<C2)
Так и сделал. Не сработало.
Значит проблема в другом?
Прикрепил файл: вкладка Ликвидность, столбцы E и F...а в соседних столбцах формула массива. Я хочу заменить на обычные, так как очень много данных - тормозит с массивом  
Изменено: JRO - 25.03.2025 14:36:29
 
 
 
#11
25.03.2025 15:01:49
Конечно тормозит! Вы в СУММПРОИЗВ запихали весь столбец. Замените все типа Продажи!$O:$O на Продажи!$O$3:$O$9999
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#12
25.03.2025 15:55:08
_Boroda_, это не проблема. Поменяю. Это файл для примера. Формула почему не считает?
 
 
 
#13
25.03.2025 17:51:21
Цитата
JRO написал:
Формула почему не считает?
не знаю, что вы там считаете, не вникал, но...
Цитата
_Boroda_ написал:
Замените все типа Продажи!$O:$O на Продажи!$O$3:$O$9999
после вставки конкретного диапазона цифирки появились... ;)
и файл тормозить перестал... хех....
Изменено: BodkhiSatva - 25.03.2025 17:52:28
 
 
 
#14
26.03.2025 14:10:20
BodkhiSatva, здравствуйте.
Есть несколько проблем в вашем файле.
$O3:$O3333 - формула не закончена и при растягивании вниз будут смещаться диапазоны.
Также формула СУММПРОИЗВ вы указали как массив, мне нужна без.
У меня файл более 50000 строк и у вас образец не тормозит просто потому что он маленький. А при использовании СУММПРОИЗВ ,как вы указали в массиве, проблема торможения на основном файле не решится.  
 
 
 
#15
26.03.2025 14:13:54
Помогите решить проблему с файлом из #10 сообщения. Прикреплен файл там.
Хочу использовать СУММПРОИЗВ, но не работает почему-то в моем файле. Не понимаю причину. Обычная формула, но не считает данные. Может не работает на большом количестве данных? более 50000 строк в основном файле  
 
 
 
#16
28.03.2025 14:06:10
Цитата
JRO написал:
$O3:$O3333 - формула не закончена и при растягивании вниз будут смещаться диапазоны.
таки исправьте... а вообще...
Цитата
JRO написал:
Также формула СУММПРОИЗВ вы указали как массив, мне нужна без.
Помогите решить проблему с файлом из #10 сообщения
вот такой вариант:
1. переводим таблицу на листе "Продажи" в умную таблицу (имена столбцов добавлены красным, исправьте по своему усмотрению)
2. меняем адреса в формулах на столбцы таблицы...

у меня тормозить перестало... правда не знаю как поведет на 50 000 строк, или ск. там у вас, не проверял, но думаю не должно...

PS вопрос - зачем вам сумма цен? бессмысленный показатель, даже не знаю, не могу представить куда он может пригодится...
Изменено: BodkhiSatva - 28.03.2025 14:31:47
 
 
 
#17
29.03.2025 13:44:55
Цитата
JRO:   Хочу использовать СУММПРОИЗВ, но не работает почему-то в моем файле. Не понимаю причину.
причина в наличии текста в столбце, в частности в шапке таблицы, а умножая на текст получаете ошибку. Чтоб заработала достаточно заменить знак умножить на точку с запятой  ( ; )
Но как выше сказано лучше использовать фиксированные диапазоны (даже с количеством, допустим, в 55000 строк отработает в разы быстрее)
Потом если СУММПРОИЗВ не фетиш, то тоже самое посчитает СУММЕСЛИМН, причём ещё быстрее:
=СУММЕСЛИМН(Продажи!M$3:M$55000;Продажи!O$3:O$55000;A11;Продажи!J$3:J$55000;E$9;Продажи!N$3:N$55000;"<"&B$2)
а чтоб "не париться" с количеством строк, то как BodkhiSatva:
Цитата
переводим таблицу на листе "Продажи" в умную таблицу
 
 
 
#18
31.03.2025 18:35:29
Павел \Ʌ/, BodkhiSatva, СУММПРОИЗВ я хочу заменить формулы массива {ВПР/ ПОИСКПОЗ}, я ищу значение при нескольких условиях и подставляю его......а так как у меня их получается в 8000 ячеек эти формулы и данные собирают с таблицы, где 50000 строк...я сделал вывод, что тормоза из-за формул массива с фигурными скобками....поэтому хочу упростить на обычные и проверить. План такой был)))
 
 
 
#19
01.04.2025 05:19:18
Насколько я помню Ваши темы (файл знакомый), Вы уже макросы используете. Так зачем опять эти заморочки с сотнями тысяч формул?
Согласие есть продукт при полном непротивлении сторон
 
 
 
#20
01.04.2025 12:57:58
Цитата
написал:
Так зачем опять эти заморочки с сотнями тысяч формул?
Здравствуйте. Все верно. Но не могу никак с макросами, с формулами как-то проще мне. Была одна общая таблица, но я разделил на несколько. В одной остались какие-то макросы.  
 
 
 
#21
01.04.2025 16:02:00
Цитата
JRO написал:
тормоза из-за формул массива с фигурными скобками.
Не совсем так. Наличие или отсутствие именно фигурных скобочек вообще ни на что не влияет. Например, обычная формула
=СУММПРОИЗВ(G6:G10*H6:H10)
ничем не отличается от массивной
=СУММ(G6:G10*H6:H10)
Просто разные способы ввода.
Важно не то, массивная формула или нет, а что и как она считает

А почему тормоза - Вам уже рассказали выше. И даже не один раз)))) И да, СУММЕСЛИМН посчитает быстрее
Цитата
Павел \Ʌ/ написал:
о тоже самое посчитает СУММЕСЛИМН, причём ещё быстрее:
Скажи мне, кудесник, любимец ба’гов...
 
 
 
Страницы: 1
Читают тему
Наверх