Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сумма значений по уникальному признаку в другом столбце
 
#1
28.06.2018 16:56:19
Доброго времени суток, Планетяне!

Не пойму - то ли заработался, то ли что, но не могу придумать НЕмакросную функцию для простой, на первый взгляд, задачи.
Есть 2 столбца: в первом повторяются ключи, во втором повторяются суммы по этим ключам. Как мне посчитать сумму по всем ID? То есть, если бы был промежуточный столбец с суммой по каждой строке, то это была бы сумма по этому столбцу, но его нет.
Решение необходимо без фильтрации и доп. столбцов. НЕмассивная формула с функциями рабочего листа. Если обычными функциями никак, то как быстрее макросом?
Подозреваю, что это одна из встроенных финансово-аналитических функций, но какая - не знаю  :)

Если бы можно было сделать через дополнительный столбец, то в нём была бы формула
=[@[Стоимость по ID]]/СЧЁТЕСЛИ([ID];[@ID]) и сумма по этому столбцу даст такой же результат, как сумма по 2му столбцу.

Среднего столбца из примера в оригинале нет. Сделан, чтобы показать, откуда эти суммы берутся в 3ем столбце.
Изменено: Jack Famous - 28.06.2018 17:12:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#2
28.06.2018 17:01:49
Отдельно пара ключ-ID?
И зачем путать людей разными названиями? :)
Нужно было показать пример результата

Обычная СУММЕСЛИ
 
 
 
#3
28.06.2018 17:07:45
vikttur, я не понимаю, как  :D
среднего (второго) столбца - его, типа, нет
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#4
28.06.2018 17:11:17
Тогда
=СУММПРОИЗВ(--(ПОИСКПОЗ(Таблица2[ID];Таблица2[ID];)=СТРОКА(Таблица2[ID])-1);СУММЕСЛИ(A:A;Таблица2[ID];B:B))
 
 
 
#5
28.06.2018 17:13:00
vikttur,  ну, не такая уж и обычная:)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#6
28.06.2018 17:13:45
Код
=СУММПРОИЗВ(Таблица2[Стоимость по ID]/СЧЁТЕСЛИ(Таблица2[ID];Таблица2[ID]))
 
 
 
#7
28.06.2018 17:16:31
vikttur, в вашем варианте (без правок) выдаёт ошибку "#ИМЯ" (( мой косяк — всё работает
Dyroff, ну так-то да))
V, работает! Как всегда, выручает SUMPRODUCT  :D спасибо вам большое!
Изменено: Jack Famous - 28.06.2018 18:19:05
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#8
28.06.2018 17:23:38
Или так
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A13;A2:A13);C2:C13)
Решение такое же как и у форумчанина V
Изменено: Bema - 28.06.2018 17:28:03
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#9
28.06.2018 17:28:04
Bema, спасибо и вам!
Я почти допёр (в шапке) до варианта от V, но не догадался "обернуть" в СУММПРОИЗВ, чтобы избавиться от доп. столбца (хотя очень часто именно СУММПРОИЗВ в этом сильно помогает)  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#10
28.06.2018 17:30:11
Цитата
в вашем варианте (без правок) выдаёт ошибку "#ИМЯ"
Показывать надо
 
 
 
#11
28.06.2018 17:33:00
vikttur, не вопрос  ;)
но столбца B вообще типа нет, а у вас в формуле есть. Возможно, что я не совсем корректно объяснил…
Изменено: Jack Famous - 28.06.2018 18:37:03
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
28.06.2018 17:45:20
Не загнать в UDF. По-моему, это общая проблема для SUMPRODUCT. Можно ли исправить?
Код
Public Function СуммироватьБезДублей(Где_ищем, Откуда_суммируем)
On Error GoTo er
    With Application.WorksheetFunction
        СуммироватьБезДублей = .SumProduct(Откуда_суммируем / .CountIf(Где_ищем, Где_ищем))
    End With
Exit Function
er: СуммироватьБезДублей = ""
End Function
Модераторам: отдельную тему сделать по этому вопросу или тут можно узнать?
Изменено: Jack Famous - 28.06.2018 17:47:36
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#13
28.06.2018 18:12:13
Цитата
Jack Famous написал: столбца B вообще типа нет, а у вас в формуле есть.
В таком случае не нужно суммировать по условию:
=СУММПРОИЗВ(--(ПОИСКПОЗ(A2:A13;A2:A13;)=СТРОКА(A2:A13)-1)*C2:C13)

Цитата
[СУММПРОИЗВ] Не загнать в UDF
СУММ и в UDF формулу массива
=СУММ(ЕСЛИ(ПОИСКПОЗ(A2:A13;A2:A13;)=СТРОКА(A2:A13)-1;C2:C13))
 
 
 
#14
28.06.2018 18:20:31
vikttur, прошу прощения — оба ваших решения работают (хоть первое - и не с тем столбцом). Тупанул из-за того, что у меня стиль ссылок стоял R1C1, а я скопировал и не поменял…
Спасибо вам большое!
Цитата
vikttur написал:
СУММ и в UDF формулу массива
хотел использовать не только на листе (да и не столько), но и внутри кода. Пробовал Evaluate, но это не тот случай, похоже, а FormulaArray вроде как не годится для вычислений внутри кода…  :( Смотрел тут и тут.
Изменено: Jack Famous - 28.06.2018 18:37:25
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#15
28.06.2018 18:50:50
Код
Function SumID(rID As Range, rPrice As Range) As Double
Dim dSum As Double
Dim sID As String
Dim i As Long
    sID = rID(1).Value: dSum = rPrice(1).Value

    For i = 1 To rID.Cells.Count
        If sID <> rID(i).Value Then sID = rID(i).Value: dSum = dSum + rPrice(i).Value
    Next i

    SumID = dSum
End Function
 
 
 
#16
28.06.2018 18:58:08
vikttur, я хотел бы всё-таки разобраться с СУММПРОИЗВ… Выделю время как-нибудь на этот вопрос  :)
спасибо вам  за вариант! Он зависит от сортировки, к сожалению, но я напишу свою UDF, если не разберусь с СУММПРОИЗВ  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1
Наверх