Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчет количества товаров в ячейке, В ячейке заказа собраны товары с артикулом. Необходимо подсчитать количество товара.
 
#1
16.04.2021 10:05:57
Всем добрый день. Помогите решить задачу. В ячейках столбца "Заказ" собраны артикулы заказанного товара менеджером. Необходимо подсчитать количество товара в ячейке и занести его в соседнюю ячейку например E11. Разделитель между артикулами может быть запятая или точка с запятой.  Проблема в том, что некоторые товары заказаны по несколько штук.  Пример ячейки E10: "1060 -3шт; 1051-3шт, 1054; 0365, 1044, 1045, 1043, 0370". Тире перед количеством штук и обозначение "шт"  - обязательны.
Какие есть варианты?
 
 
 
#2
16.04.2021 10:07:59
Юрий Алексеев,
добрый день!
Приложите пожалуйста пример файла
 
 
 
#3
16.04.2021 10:23:36
Набросал пример. Артикулы с заказами в столбце "С", количество товара должно отображаться в столбце "D".  
Изменено: Юрий Алексеев - 16.04.2021 10:27:08 (добавление)
 
 
 
#4
16.04.2021 10:44:36
Для вашего примера UDF
Код
Function iSumma(cell$)
Dim mo As Object
Dim n As Integer
 With CreateObject("VBScript.RegExp")
   .Global = True
   .MultiLine = True
   .Pattern = "\d{2,4}( ?- ?\d+ ?шт)?"
     If .test(cell) Then
       Set mo = .Execute(cell)
         For n = 0 To mo.Count - 1
           If InStr(mo(n), "шт") > 0 Then
             iSumma = iSumma + Val(Split(mo(n), "-")(1))
           Else
             iSumma = iSumma + 1
           End If
         Next
    End If
 End With
End Function
 
 
 
#5
16.04.2021 10:53:23
формула для до 5 наименований:
Код
=СУММ(ЕСЛИОШИБКА(ПСТР(C2;ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"-";СИМВОЛ(1);{1:2:3:4:5}));0)+1;ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"ш";СИМВОЛ(1);{1:2:3:4:5}));0)-ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"-";СИМВОЛ(1);{1:2:3:4:5}));0)-1)*1;0))
Изменено: Тимофеев - 16.04.2021 10:54:22
 
 
 
#6
16.04.2021 10:59:38
Юрий Алексеев    Вариант формулой
Код
=ДЛСТР(C2)*2-ДЛСТР(ПОДСТАВИТЬ(C2;",";""))-ДЛСТР(ПОДСТАВИТЬ(C2;";";""))+1
 
 
 
#7
16.04.2021 11:00:54
Как понял. Формула массива:
Код
=СУММ(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");";";ПОВТОР(" ";99));СТРОКА($1:$9)*99-98;99)^0;--(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");";";ПОВТОР(" ";99));СТРОКА($1:$9)*99-98;99);"шт";));"-";"   ");4)));))
 
 
 
#8
16.04.2021 11:07:19
Евгений Смирнов , не работает. Не учитывает количество, указанное после тире.

Тимофеев  ,тоже не работает.) Учитывает только первое вхождение -4шт
 
 
 
#9
16.04.2021 11:09:51
у меня все работает
 
 
 
#10
16.04.2021 11:12:00
Цитата
Светлый написал:
Как понял. Формула массива:
Не работает.) Берет только первый артикул.
 
 
 
#11
16.04.2021 11:14:58
Юрий Алексеев,
Вы мой вариант попробовали?
 
 
 
#12
16.04.2021 11:15:35
Цитата
Тимофеев написал:
у меня все работает
У Иванова должно быть 13 товаров. В Вашем варианте 7. Но это уже ближе к цели. Ломаю голову второй день.))
 
 
 
#13
16.04.2021 11:17:15
как 13 если 4+3 равно 7
тогда не так объяснили я по критерию считаю если есть шт и дефис
Изменено: Тимофеев - 16.04.2021 11:18:10
 
 
 
#14
16.04.2021 11:20:30
Цитата
Kuzmich написал:
Вы мой вариант попробовали?
Ваш вариант рабочий! Всем спасибо большое! Пока тему не закрывайте. Может можно сделать с помощью стандартных формул?)
 
 
 
#15
16.04.2021 11:27:29
Код
=СУММ(ЕСЛИОШИБКА(ПСТР(C2;ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"-";СИМВОЛ(1);{1:2:3:4:5}));0)+1;ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"ш";СИМВОЛ(1);{1:2:3:4:5}));0)-ЕСЛИОШИБКА(НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"-";СИМВОЛ(1);{1:2:3:4:5}));0)-1)*1;0))+ДЛСТР(C2) - ДЛСТР(ПОДСТАВИТЬ(C2;";";""))+ДЛСТР(C2) - ДЛСТР(ПОДСТАВИТЬ(C2;",";""))-ДЛСТР(C2) + ДЛСТР(ПОДСТАВИТЬ(C2;"-";""))+1
Изменено: Тимофеев - 16.04.2021 11:27:55
 
 
 
#16
16.04.2021 11:30:23
Тимофеев, отлично! Все работает! Спасибо большое всем!)
 
 
 
#17
16.04.2021 11:34:55
Цитата
Светлый написал: Формула массива
Цитата
Юрий Алексеев написал: Не работает.) Берет только первый артикул.
Формула массива вводится одновременным нажатием Ctrl+Shift+Enter.
 
 
 
#18
16.04.2021 11:56:53
Я знаю.) Не работает. Ваш пример:
 
 
 
#19
16.04.2021 12:04:18
Если 2 раза встречается <шт> то так, если больше надо добавить еще
Цитата
=ДЛСТР($C2)*2-ДЛСТР(ПОДСТАВИТЬ($C2;",";""))-ДЛСТР(ПОДСТАВИТЬ($C2;";";""))+1+ЕСЛИОШИБКА(ПСТР(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($C2;"-";ПОВТОР(" ";99));99;99));1;1)-1;0)+ЕСЛИОШИБКА(ПСТР(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($C2;"-";ПОВТОР(" ";99));99*2;99));1;1)-1;0)
А вообще надо БМВ приглашать он напишет круто
Изменено: Евгений Смирнов - 16.04.2021 12:11:01
 
 
 
#20
16.04.2021 12:10:08
Цитата
Евгений Смирнов написал:
Если 2 раза встречается  
Это не предсказуемо. Шт может вообще не быть, а может быть сколько угодно.
 
 
 
#21
16.04.2021 12:45:47
ну не сколько угодно а по любому меньше длины строки/2
 
 
 
#22
16.04.2021 13:16:45
До кучи на PQ:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Т", each Splitter.SplitTextByAnyDelimiter({";",","})([Заказ])),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Т"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded {0}", "Т", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Т.1", "Т.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","шт","",(a,b,c)=> List.Max({1, Number.From(Text.Replace(a,b,c))}),{"Т.2"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"№", "Менеджер", "Заказ"}, {{"Количество товаров", each List.Sum([Т.2]), type number}})
in
    #"Grouped Rows"
Вот горшок пустой, он предмет простой...
 
 
 
#23
16.04.2021 13:18:02
Где-то при копировании с сайта может быть глюк. Вместо пробела в формуле может вставить СИМВОЛ(160).
Новая формула массива. РАБОТАЕТ. И старая работает.
Код
=СЧЁТ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");";";ПОВТОР(" ";99));СТРОКА($1:$9)*99-98;99)^0)-СУММ(ЕСЛИОШИБКА(-ЛЕВБ(ПОДСТАВИТЬ(ПСТР(C2;ЕСЛИ(ПСТР(C2;СТРОКА($1:$98);1)="-";СТРОКА($2:$99));9);"шт";"    ");5);))
 
 
 
#24
17.04.2021 03:53:17
Да, Ваша версия тоже рабочая! Интересное решение! Спасибо большое!)

Цитата
PooHkrd написал: До кучи на PQ:
Тоже работает! Спасибо за Ваш вариант!)
 
 
 
#25
19.04.2021 07:54:16
Если интересно, ещё вариант решения придумал. Массивная:
Код
=СУММ(--ТЕКСТ(ЕСЛИОШИБКА(-ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");";";ПОВТОР(" ";99));СТРОКА($1:$9)*99-98;99);"шт";);"-";ПОВТОР(" ";99)&"-");99););"0;1;0"))
=СУММ(--ТЕКСТ(ПРАВБ(ПОДСТАВИТЬ(0&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");";";ПОВТОР(" ";99));СТРОКА($1:$9)*99-98;99);"шт";));"-";"    -");6);"1;0;0"))
Изменено: Светлый - 19.04.2021 11:17:42
 
 
 
#26
20.04.2021 06:25:45
Цитата
Светлый написал: Если интересно, ещё вариант решения придумал.
Конечно интересно! Спасибо!)
 
 
 
#27
21.04.2021 06:26:06
Всё уже решено, но где-то свербит, что можно короче формулу сделать. Лишние действия в готовых формулах. И нашёл:
Код
=СУММ(--ТЕКСТ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;",";";");"шт";);"-";"-;-");";";ПОВТОР(" ";99));СТРОКА($1:$99)*99-98;99);"1;0;0;\0"))
 
 
 
#28
23.04.2021 20:22:26
Да, интересно. Тоже работает! )
Изменено: Юрий Алексеев - 23.04.2021 20:23:37 (пример)
 
 
 
Страницы: 1
Наверх