ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / СУММПРОИЗВ со сложным условием
17 сообщений из 17, страница 1 из 1
СУММПРОИЗВ со сложным условием
    #36415041
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
Доброго времени суток.
Можно ли просуммировать значения в колонке В таблицы на основании критериев указанных в колонке А (см. приложенный файл) . При этом, в ячейках из колонки D указываются наборы критериев, которые могут задаваться со звездочкой "*" (маска для любого набора сиволов) или через точку с запятой ";" (перечисление конкретных значений, которые также могут задаваться с маской "*"). В примере суммы расчитаны вручную. Есть подозрение, что это можно реализовать используя функцию СУММПРОИЗВ, но смущают точки с запятыми. Заранее спасибо за внимание и ответы
...
Рейтинг: 0 / 0
18.01.2010, 01:00
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415061
Фотография vikttur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vikttur
Участник
Каждый критерий - в отдельную ячейку, иначе усложнять формулу для "выдергивания частей текста."
=СУММПРОИЗВ(--НЕ(ЕОШ(ПОИСК(D9;$A$2:$A$6)))*$B$2:$B$6)
Ищет ячейки с текстом D9 (например, К1).
Из примера не ясно - например, поиск К1, но не К11 - такой вариант может быть?
...
Рейтинг: 0 / 0
18.01.2010, 01:33
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415254
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
Перечислений может быть достаточно много, поэтому да - строку в ячейке нужно делить.
Если К1*, то искать и К1, и К11. Если нужно просто К1, то в условиях вводим К1 без звездочки
...
Рейтинг: 0 / 0
18.01.2010, 09:48
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415257
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
При этом, если в строке поиска введено "К1*;К11", то критерий К11 должен быть учтен только один раз
...
Рейтинг: 0 / 0
18.01.2010, 09:50
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415335
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
Изначально додумался до такого
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
    Public Function OraLike(TestValue As Range, Mask As Range) As Integer
    Dim var As Variant
    Dim i As Integer, nResult As Integer
    Dim bResult As Boolean
      nResult =  0 
      var = Split(Mask.Text, ";")
    For i =  0  To UBound(var)
       bResult = TestValue.Text Like var(i)
     If bResult Then nResult =  1 
    If bResult Then Exit For
    Next i
    OraLike = nResult
End Function
Потом понял, что использовать ее вместе с СУММПРОИЗВ не представляется возможным
...
Рейтинг: 0 / 0
18.01.2010, 10:37
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415349
Фотография vikttur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vikttur
Участник
Сложно для понимания - как это на практике выглядит?
Может быть, покажете пример, более приближенный к реальному?
Я к тому, что, возможно, найдется более простой способ отбора критериев.
...
Рейтинг: 0 / 0
18.01.2010, 10:45
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415799
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
Критерием служит определенный код. Их порядка тысячи. Могут быть, например кода К10000, К10100,К11111,К20000,К30000 и т.д.
В итоговой отчетной таблице пользователю необходимо, например, собрать в одной колонке суммы по кодам: К30000 и К20000, а в другой колонке все кода, начинающиеся с К1. Всего таких колонок около 50-ти. Состав условий для отбора критериев и количества колонок может меняться со временем. Существуют также другие отчетные формы по такому же принципу, поэтому по возможности необходима универсальность
...
Рейтинг: 0 / 0
18.01.2010, 13:13
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415883
DV68
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DV68
Гость
Ambler,
Если разнести условия по ячейкам, как и предлагал уважаемый vikttur, то можно проблему решить обычным СУММЕСЛИ.
...
Рейтинг: 0 / 0
18.01.2010, 13:40
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36415945
Ambler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ambler
Гость
Спасибо за решение, однако если строка условий, например имеет следующий вид: "К1*;К21002;К33113;...(может доходить до 20 и больше составляющих)" то в использовании на практике врядле этот подход окажется удобным
...
Рейтинг: 0 / 0
18.01.2010, 14:01
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36416113
Фотография vikttur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vikttur
Участник
При таком подходе формулы вряд ли помогут. Тем более, что нужна универсализация.
Ведь нужно научить Excel понимать, где целый код, где его часть, где в наборе критериев повторения (например, К1* и К15). Макрос с несколькими циклами, наверное.
Видится несколько решений.
Разнесение критериев по отдельным ячейкам - однозначно, иначе сначала объединяем, потом разъединяем, сами себе трудности создаем.
Если возможно, составить таблицу всех встречающихся критериев. С помощью такой "подпорки" легче делать выборку.
Сделать отдльные блоки искомых критериев. "К1*,В2,В3*,С12" хуже, чем отдельно "К1*,В3*" и "В2,С12".
Но это мое мнение, не обязательно принимать как аксиому :)
...
Рейтинг: 0 / 0
18.01.2010, 15:10
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36424668
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Ambler,

Мне кажется у вас задача аналогична той, что была у меня и обсуждалась в этой теме .
Там так-же задавался набор избранных счетов, по которым нужно было вести расчёт.
Единственное что надо переделать немного алгоритм для считывания счетов вида K1*.
А сочетания вида K1*, K11 нужно просто не допускать, их обрабатывать макросом к примеру и в данном случае убирать K11 как дублирующий.
...
Рейтинг: 0 / 0
22.01.2010, 11:44
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36425170
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Подумав родил такую формулу массива:

Код: plaintext
1.
=СУММ(ЕСЛИ(ПРАВСИМВ($J$ 17 :$O$ 17 ; 1 )="*";(ЛЕВСИМВ($A$ 2 :$A$ 206 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 )=ЛЕВСИМВ($J$ 17 :$O$ 17 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 ));$A$ 2 :$A$ 206 =$J$ 17 :$O$ 17 )*B$ 2 :B$ 206 )

Где A2:A206 - диапазон счетов, B2:B206 -диапазон сумм
J17-O17 - список счетов по которым надо найти сумму, можно использовать маску звёздочка в качестве самого правого символа. Т.е. например K1*, K2*, K3000. В случае когда один и тот-же счёт попадает как в маску, так и в счёт, сумма по такому счёту будет задваиваться (ну или утраивается и т.п.), поэтому нужно выбирать правильно список счетов.
...
Рейтинг: 0 / 0
22.01.2010, 14:00
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36425460
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Немного модифицировал формулу, так как в предыдущей формуле был небольшой косяк, что если в заданном диапазоне счетов A2:A206 в каких-то ячейках не был указан счёт, но приэтом рядом в столбце стояла сумма, то эта сумма тоже подсчитывалась, причем столько раз, сколько пустых ячеек встречалось в диапазоне J17:O17. В новой формуле этот эфект убран.

Код: plaintext
=СУММ(ЕСЛИ($J$ 17 :$O$ 17 <>"";ЕСЛИ(ПРАВСИМВ($J$ 17 :$O$ 17 ; 1 )="*";(ЛЕВСИМВ($A$ 2 :$A$ 206 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 )=ЛЕВСИМВ($J$ 17 :$O$ 17 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 ));$A$ 2 :$A$ 206 =$J$ 17 :$O$ 17 )*B$ 2 :B$ 206 ))
...
Рейтинг: 0 / 0
22.01.2010, 15:06
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36425580
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Djon Player,

Придумал ещё вариант формулы массива, который позволяет исключить дублирующиеся повторения счетов в диапазоне J17:O17, т.е. если в этом диапазоне указано и K1* и K10, то сумма по счету K10 сосчитается один раз. Вобщем всё как просил автор вопроса:
Код: plaintext
=СУММ(ЕСЛИ((ЕСЛИ($J$ 17 :$O$ 17 <>"";ЕСЛИ(ПРАВСИМВ($J$ 17 :$O$ 17 ; 1 )="*";(ЛЕВСИМВ($A$ 2 :$A$ 206 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 )=ЛЕВСИМВ($J$ 17 :$O$ 17 ;ДЛСТР($J$ 17 :$O$ 17 )- 1 ));$A$ 2 :$A$ 206 =$J$ 17 :$O$ 17 )))= 0 ; 0 ; 1 )*B$ 2 :B$ 206 )/СЧЁТЕСЛИ($J$ 17 :$O$ 17 ;"<>""")
...
Рейтинг: 0 / 0
22.01.2010, 15:37
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36425602
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Хотя походу предыдущая формула работает неправильно.
...
Рейтинг: 0 / 0
22.01.2010, 15:42
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36425791
DaniilK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DaniilK
Участник
То что хочется можно сделать чисто формулами, но понадобиться одна вспом. строка на N ячеек (где N это макс.количество запятых плюс один), а также столько вспом.столбцов, сколько у Вас будет строк с условиями (в примере их три)
К1*;К2
К1*
К2;К5

но это очень головняковое решение будет...
лучше не морочить себе и людям голову и немного иначе организовать данные...
...
Рейтинг: 0 / 0
22.01.2010, 16:46
| Ответить | Цитировать | Написать  
СУММПРОИЗВ со сложным условием
    #36429696
Djon Player
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Djon Player
Участник
Привожу пример с использованием формулы из моего сообщения от 22 янв 10, 15:06.
В него так-же включен макрос, который убирает дублирующие счета в условиях суммирования.
Т.е. если в условии суммирования присутствует как счёт К1, так и К1*, то К1 удаляется как дублирующий.
Если в качестве условий суммирования задано *, то остальные условия так-же удаляются.
Принцип работы такой, набрать в условиях суммирования перечень счетов.
Формула автоматически находит сумму. Но т.к. формула не проверяет условий дублирования счетов, то на всякий случай нажать кнопку удаляющую лишние счета из условий суммирования.
...
Рейтинг: 0 / 0
25.01.2010, 17:06
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / СУММПРОИЗВ со сложным условием
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=36415349&tid=2178565]:
 0ms
get settings:
 6ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 174ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 36ms
get tp. blocked users:
 1ms
others: 222ms
total:  467ms
созд. / загр.: 467ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]