Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование нескольких значений, найденных через ВПР
 
#1
13.03.2026 13:14:48
Доброго времени суток! Возникли некоторые трудности с подсчётом значений, найденных через ВПР. Итак, в столбце А есть список артикулов товаров (типа 1_0001 или 2_0003); в столбце В - соответствующая стоимость этих товаров. В столбце D перечислены заказы товаров (в виде этих артикулов/названий), причём некоторые из заказов содержат несколько артикулов, разделённых точкой с запятой (типа 1_0001; 1_0003; 2_0001). Задача, по сути, простая - найти стоимость в столбце Е каждого заказа. Через ВПР (вместо таблицы выделяя просто столбцы А и В, так как между строками артикулов могут быть другие типы данных - возможно, описание товара) я могу найти стоимость каждого заказанного товара, но только если он один в ячейке. У меня не получается заставить ВПР подсчитать сумму всех заказанных позиций в одной ячейке, товары в которой разделены точкой с запятой. Файл примера прилагаю. Заранее спасибо за любые ответы и помощь!
 
 
 
#2
13.03.2026 13:22:19
Код
=СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));0*2*ДЛСТР(D1)+1;2*ДЛСТР(D1))))
+СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));1*2*ДЛСТР(D1)+1;2*ДЛСТР(D1))))
+СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));2*2*ДЛСТР(D1)+1;2*ДЛСТР(D1))))
Для возможности масштабирования лучше слагаемые разнести по разным столбцам, но если количество в одном заказе не будет увеличиваться, то достаточно и этой формулы.
 
 
 
#3
13.03.2026 13:44:36
варианты:
=СУММ((СУММЕСЛИ(A:A;ФИЛЬТР.XML("<I><i>"&ПОДСТАВИТЬ(D1;";";"</i><i>")&"</i></I>";"//i");B:B)))
или
=СУММ((СУММЕСЛИ(A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";99));{0:1:2:3:4:5:6:7:8:9}*99+1;99));B:B)))
 
 
 
#4
13.03.2026 13:45:42
Вариант макросом.
Код
Option Explicit

Sub Сумм_заказов()
    Dim cd As Range, vv As Variant, dic As Object, arr As Variant, ya As Long, ss As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    arr = ActiveSheet.UsedRange.Columns("A:A").Value
    For ya = 1 To UBound(arr, 1)
        If Not IsEmpty(arr(ya, 1)) Then
            dic(arr(ya, 1)) = dic(arr(ya, 1)) & "+B" & ya
        End If
    Next
    
    Dim Application_Calculation As XlCalculation: Application_Calculation = Application.Calculation: Application.Calculation = xlCalculationManual

    For Each cd In ActiveSheet.UsedRange.Columns("D:D").Cells
        If Not IsEmpty(cd.Value) Then
            ss = ""
            For Each vv In Split(cd.Value, ";")
                If dic.Exists(Trim(vv)) Then
                    ss = ss & dic(Trim(vv))
                End If
            Next
            If ss = "" Then
                ss = Empty
            Else
                ss = Mid(ss, 2, Len(ss) - 1)
                ss = "=" & ss
            End If
            cd.Cells(1, 2).Formula = ss
        End If
    Next
    Application.Calculation = Application_Calculation
End Sub
Справа вариант формулами - можно тянуть вправо, если количество в заказе станет больше.
 
 
 
#5
13.03.2026 13:47:40
Здравствуйте.

А может быть без ВПР попробовать?

Код
=СУММПРОИЗВ(СЧЁТЕСЛИ(D1;"*"&$A$1:$A$12&"*")*$B$1:$B$12)
Изменено: gling - 13.03.2026 16:12:23
 
 
 
#6
13.03.2026 14:31:42
МатросНаЗебре, ПавелW, gling, благодарю, всё работает! Дополнительный вопрос: что, если мне нужно найти не сумму этих товаров в столбце Е, а среднее значение их стоимости? Как тогда переделать эти формулы?
 
 
 
#7
13.03.2026 14:38:54
Цитата
написал:
среднее значение их стоимости? Как тогда переделать эти формулы?
В файле из сообщения #4 в ячейке L1 формула примет вид:
Код
=СУММ(M1:XFD1)/СЧЁТЕСЛИМН(M1:XFD1;">0")
 
 
 
#8
13.03.2026 14:42:41
Формула из сообщения #2 примет вид:
Код
=(СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));0*2*ДЛСТР(D1)+1;2*ДЛСТР(D1))))
+СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));1*2*ДЛСТР(D1)+1;2*ДЛСТР(D1))))
+СУММЕСЛИМН(B:B;A:A;СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";2*ДЛСТР(D1)));2*2*ДЛСТР(D1)+1;2*ДЛСТР(D1)))))
/((ДЛСТР(D1)-ДЛСТР(ПОДСТАВИТЬ(D1;"; ";"")))/ДЛСТР("; ")+1)
 
 
 
#9
13.03.2026 14:55:46
Макрос примет вид:
Код
Option Explicit

Sub Сумма_заказов()
    Sheet_job 2, "+", "", ""
End Sub

Sub Среднее_заказов()
    Sheet_job 3, ",", "AVERAGE(", ")"
End Sub

Private Sub Sheet_job(xPrint As Long, prefix1 As String, prefix2 As String, postfix3 As String)
    Dim cd As Range, vv As Variant, dic As Object, arr As Variant, ya As Long, ss As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    arr = ActiveSheet.UsedRange.Columns("A:A").Value
    For ya = 1 To UBound(arr, 1)
        If Not IsEmpty(arr(ya, 1)) Then
            dic(arr(ya, 1)) = dic(arr(ya, 1)) & prefix1 & "B" & ya
        End If
    Next
    
    Dim Application_Calculation As XlCalculation: Application_Calculation = Application.Calculation: Application.Calculation = xlCalculationManual

    For Each cd In ActiveSheet.UsedRange.Columns("D:D").Cells
        If Not IsEmpty(cd.Value) Then
            ss = ""
            For Each vv In Split(cd.Value, ";")
                If dic.Exists(Trim(vv)) Then
                    ss = ss & dic(Trim(vv))
                End If
            Next
            If ss = "" Then
                ss = Empty
            Else
                ss = Mid(ss, 2, Len(ss) - 1)
                ss = prefix2 & ss & postfix3
                ss = "=" & ss
            End If
            cd.Cells(1, xPrint).Formula = ss
        End If
    Next
    Application.Calculation = Application_Calculation
End Sub
 
 
 
#10
13.03.2026 15:09:57
Попаразитирую на на формуле gling, массивная
Код
=СРЗНАЧ(ЕСЛИОШИБКА(1/(СЧЁТЕСЛИ(D1;"*"&$A$1:$A$12&"*")*$B$1:$B$12)^-1;""))
 
 
 
#11
13.03.2026 15:21:49
Wergulla, можно заменить СУММ() в #3 на СРЗНАЧ()  ...ну или на другую агрегирующую функцию
...возможно потребуется ввести как формулу массива
 
 
 
#12
13.03.2026 15:46:35
тогда тоже попаразитирую на ответах.. :)
вариант
в E1 формула массива, далее протянуть вниз
=СРЗНАЧ(ЕЧИСЛО(ПОИСК($A$1:$A$12;D1))*$B$1:$B$12)
=СУММ(ЕЧИСЛО(ПОИСК($A$1:$A$12;D1))*$B$1:$B$12)
познакомился с Excel
 
 
 
#13
13.03.2026 15:59:19
Цитата
ПавелW написал:
можно заменить СУММ() в  #3  на СРЗНАЧ()
Если в таблице не будет дублей например две 2_0001
 
 
 
#14
13.03.2026 16:07:35
Msi2102,  от чего ж? быть может там и есть дубли, но с разной стоимостью))
пожалуй в таком случае можно добавить /СЧЁТЕСЛИ(...)
Изменено: ПавелW - 13.03.2026 16:10:51
 
 
 
#15
13.03.2026 16:13:51
Среднее:
Код
=СУММПРОИЗВ(СЧЁТЕСЛИ(D1;"*"&$A$1:$A$12&"*")*$B$1:$B$12)/СУММПРОИЗВ(СЧЁТЕСЛИ(D1;"*"&$A$1:$A$12&"*"))
Изменено: gling - 13.03.2026 16:15:20
 
 
 
#16
13.03.2026 16:43:48
Можно ещё поизголяться с ФИЛЬТР.XML (массивная)
Код
=СРЗНАЧ(ЕСЛИОШИБКА(1/(($A$1:$A$12=ТРАНСП(ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(D1;"; ";"</s><s>")&"</s></t>";"//s")))*$B$1:$B$12)^-1;""))
 
 
 
#17
14.03.2026 16:21:38
вариант прям по теме:
=СУММ(ВПР(Т(ИНДЕКС(ФИЛЬТР.XML("<I><i>"&ПОДСТАВИТЬ(D1;";";"</i><i>")&"</i></I>";"//i");));A:B;2;))
или
=СУММ(ЕСЛИОШИБКА(ВПР(Т(ИНДЕКС(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(D1;";";ПОВТОР(" ";99));{0:1:2:3:4:5:6:7:8:9}*99+1;99));));A:B;2;);""))
массивки
...и здесь можно заменить СУММ на СРЗНАЧ и пр...
 
 
 
#18
19.03.2026 04:35:36
Вопрос на форуме нашёл, а вот решение не получается. Имеется формула: =ЕСЛИОШИБКА(ВПР(E7;'[Остатки.xlsx]TDSheet'!$B$13:$D$215;3;ЛОЖЬ);"0")
Но теперь в столбце В13 появились строки с одинаковым значением. Как сделать так, чтобы эти значения из столбца D суммировались?
 
 
 
#19
19.03.2026 05:34:16
ronyman, если Ваш вопрос совпадает с Темой этого топика, то приложите свой файл-пример (Excel), в которм покажите Как есть - Как надо
Согласие есть продукт при полном непротивлении сторон
 
 
 
#20
19.03.2026 07:01:31
Цитата
написал:
Как сделать так, чтобы эти значения из столбца D суммировались?

Используйте не ВПР(), а СУММЕСЛИ().  
 
 
 
#21
19.03.2026 07:35:01
Ну вот, собственно сделать так, чтобы в правой части таблицы строки с одинаковым кодом из левой части суммировались. В оригинале таблицы на разных листах.
 
 
 
#22
19.03.2026 07:41:47
Попробуйте без ВПР, так:
Код
=СУММЕСЛИ($B$5:$B$20;H5;$D$5:$D$20)

Или сводной таблицей.

Изменено: gling - 19.03.2026 07:42:25
 
 
 
Страницы: 1
Читают тему
Наверх