Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сумма чисел, по определенной дате, из одной ячейки
 
#1
20.05.2023 16:13:00
Всем привет. Возникает случай, когда надо вводит определенные суммы, и даты в одну ячейку.

Далее, нужно в другую ячейку суммировать именно те числа, которые соответствуют своим датам.

Пример: ячейка А1 содержит: 2000.00-2015/150.05-2016/500000.30-2017/230.70-2016, соответственно сначала идет число любое как сумма с копейками (разделитель между рублями и копейками это "."), потом идет "-" как разделитель между числом и датой (дата всегда это целый год), затем идет разделитель"/" который заканчивает такую связку, и начинает новую. Принципиально нет разницы между разделениями ("-" и "/") это просто условие для понятности.

Но как теперь "научить" этому таблицу?
нужно чтоб в ячейке А2 теперь складывались суммы по годам только 2015 г., то есть это будет 2000.00, в ячейке А2 за 2016 будет: 380.75, в А3 будет 500000.30.

Здесь ведь стоит задача: какой формулой научить таблицу находить в определенной ячейке любые суммы, по связке с рядом стоящим им году, а потом сложить только их и вставить в другую ячейку такую сумму.

Либо это невозможно.
 
 
 
#2
20.05.2023 16:30:33
Код
=СУММ(
(ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"/";"-");".";ПСТР(1%;2;1));"-";"</i><i>")&"</i></j>";
"//i[position() mod 2=0]")=2015)*
(ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"/";"-");".";ПСТР(1%;2;1));"-";"</i><i>")&"</i></j>";
"//i[position() mod 2=1]")))
Изменено: Тимофеев - 20.05.2023 16:36:51
 
 
 
#3
20.05.2023 18:44:57
Код
=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2;".";MID(1/2;2;1));"-";"/");"/";"</s><s>")&"</s></t>";"//s[following::*[1]='"&B$1&"']"))
 
 
 
#4
21.05.2023 20:29:07
Спасибо за ответ, оба варианта вроде рабочие, но при попытке что либо изменить сразу появляется ошибка #ИМЯ?.
Даже если ничего не меняя в ячейке enter нажмешь оно ошибку выдает, чуть тронешь ячейки с формулами то все.
Изменено: Abumislim Agilov - 21.05.2023 20:31:34
 
 
 
#5
21.05.2023 23:50:34
Abumislim Agilov, Нужен как минимум офис 2013.
Вариант для 2007.Массивный и неоптимальный. Наверняка можно сделать проще.
Изменено: memo - 22.05.2023 01:13:30
 
 
 
#6
22.05.2023 07:53:13
=SUM(IFERROR((--TRIM(MID(SUBSTITUTE(SUBSTITUTE("/"&$A$1;"-";"/");"/";REPT(" ";LEN($A$1)));ROW($1:$99)*2*LEN($A$1);LEN($A$1)))=2014+ROWS(A$2:A2))*TRIM(MID(SUBSTITUTE(SUBSTITUTE("/"&SUBSTITUTE($A$1;".";MID(1%;2;1));"-";"/");"/";REPT(" ";LEN($A$1)));(ROW($1:$99)*2-1)*LEN($A$1);LEN($A$1)));))
Изменено: БМВ - 22.05.2023 08:02:39
По вопросам из тем форума, личку не читаю.
 
 
 
#7
22.05.2023 11:04:04
Спасибо за ответ, все отлично. Я тоже не сразу подумал, что некоторые функции из данной формулы не работают на старых версиях ехсеl.
Изменено: Agilov - 22.05.2023 11:04:51
 
 
 
#8
22.05.2023 11:27:14
Agilov,  согласно вашего "файла примера" 4 обрабатываемых блока /.../ вам достаиочно
Сделал 20 на всякий случай
=СУММПРОИЗВ((A2=--(0&ПРАВСИМВ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("/"&A$1;"/";ПОВТОР(" ";254));СТРОКА($1:$20)*254;254));4)))*(0&ЛЕВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("/"&A$1;"/";ПОВТОР(" ";254));СТРОКА($1:$20)*254;254));"-";ПОВТОР(" ";254));".";ПСТР(2%;2;1));254)))
 
 
 
#9
22.05.2023 11:47:11
Цитата
написал:
Agilov,  согласно вашего "файла примера" 4 обрабатываемых блока /.../ вам достаиочно
Сделал 20 на всякий случай
=СУММПРОИЗВ((A2=--(0&ПРАВСИМВ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("/"&A$1;"/";ПОВТОР(" ";254));СТРОКА($1:$20)*254;254));4)))*(0&ЛЕВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ("/"&A$1;"/";ПОВТОР(" ";254));СТРОКА($1:$20)*254;254));"-";ПОВТОР(" ";254));".";ПСТР(2%;2;1));254)))
Один ответ лучше другого), спасибо.
 
 
 
#10
22.05.2023 13:34:09
Цитата
Павел с Востока написал:
Сделал 20 на всякий случай
тогда и 254 на всякий случай нужно увеличить иначе набежит погрешность.
500000.30-2017 - 14 знаков подобных 20  - получаем 280 соответственно последние могут со сдвигом отработать.
По вопросам из тем форума, личку не читаю.
 
 
 
#11
22.05.2023 13:56:46
Цитата
БМВ,    500000.30-2017
) да ладно - ну брал для расчета по меньше длиной блоки
Если ничего не исправлять и все такие ("500000.30-2017"), то 19
...и вобще мы их балуем (без файла то)
 
 
 
#12
22.05.2023 14:14:49
Цитата
Павел с Востока написал:
Если ничего не исправлять и все такие ("500000.30-2017"), то 19
- не понял? если максимальные 15 брать, то 21, но от куда 19?
Цитата
Павел с Востока написал:
...и вобще мы их балуем (без файла то)
вопрос в предупредить о ограничениях.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
22.05.2023 14:48:51
БМВ,  вот не хочу препинаться )
Проверьте: подставьте: этот блок 19 раз и 20 раз )
Цитата
 вопрос в предупредить о ограничениях
Ну... тогда это не только меня касается ) - а вдруг там 50 блоков? ;)
Задачу можно решить и матричным способом - количество блоков тогда увеличется вообще в разы - только зачем?
Немного передерну - пусть ТС решает
 
 
 
#14
22.05.2023 15:38:17
Цитата
Павел с Востока написал:
Проверьте: подставьте: этот блок 19 раз и 20 раз )
понятно, мы о разном я о длине блока в 14 и максимум в 21 символ, а 19 это количество блоков.
но это тоже не корректно так как число может быть из 15 + разделители + год  и это группа в 21 символ. соответственно можно ориентироваться на это.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх