Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Суммировать 5 последних по критерию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммировать 5 последних по критерию (Формулы/Formulas)
Суммировать 5 последних по критерию
zegor Дата: Пятница, 04.05.2018, 11:33 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Привет, excelworld. Помоги пожалуйста справиться с такой проблемой– пытаюсь суммировать пять последних значений по критерию в растущем массиве. Сам могу посчитать просто сумму с помощью СУММЕСЛИ. А вот как ограничить суммирование только пятью последними соответствующими критерию отбора не пойму. Я вижу это примерно так =суммесли(диапазон;критерий;ПЯТЬ ПОСЛЕДНИХ).
К сообщению приложен файл: L5.xlsx (16.6 Kb)
 
Ответить
СообщениеПривет, excelworld. Помоги пожалуйста справиться с такой проблемой– пытаюсь суммировать пять последних значений по критерию в растущем массиве. Сам могу посчитать просто сумму с помощью СУММЕСЛИ. А вот как ограничить суммирование только пятью последними соответствующими критерию отбора не пойму. Я вижу это примерно так =суммесли(диапазон;критерий;ПЯТЬ ПОСЛЕДНИХ).
Автор - zegor
Дата добавления - 04.05.2018 в 11:33
bmv98rus Дата: Пятница, 04.05.2018, 11:39 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
пять последних по количеству или по качеству? Я про то что это могут быть пять равных. увидел в примере.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 04.05.2018, 11:54
 
Ответить
Сообщениепять последних по количеству или по качеству? Я про то что это могут быть пять равных. увидел в примере.
Автор - bmv98rus
Дата добавления - 04.05.2018 в 11:39
zegor Дата: Пятница, 04.05.2018, 11:44 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, по количеству наверное. Не совсем понял вопрос просто. Пять последних в столбце (сверху вниз), нижних. Тех которые напротив критерия А1.
 
Ответить
Сообщениеbmv98rus, по количеству наверное. Не совсем понял вопрос просто. Пять последних в столбце (сверху вниз), нижних. Тех которые напротив критерия А1.
Автор - zegor
Дата добавления - 04.05.2018 в 11:44
_Boroda_ Дата: Пятница, 04.05.2018, 11:49 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вариант
Код
=СУММ(--ПСТР(НАИБОЛЬШИЙ(ЕСЛИ($A$2:$A21=$A22;СТРОКА(C$2:C21)*10^6+C$2:C21);СТРОКА(C$1:C$5));4;9))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
К сообщению приложен файл: L5_1.xlsx (16.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВариант
Код
=СУММ(--ПСТР(НАИБОЛЬШИЙ(ЕСЛИ($A$2:$A21=$A22;СТРОКА(C$2:C21)*10^6+C$2:C21);СТРОКА(C$1:C$5));4;9))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
Автор - _Boroda_
Дата добавления - 04.05.2018 в 11:49
bmv98rus Дата: Пятница, 04.05.2018, 11:56 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);{1;2;3;4;5});))))
К сообщению приложен файл: 8109259.xlsx (16.9 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 04.05.2018, 12:00
 
Ответить
Сообщение
Код
=SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);{1;2;3;4;5});))))
Автор - bmv98rus
Дата добавления - 04.05.2018 в 11:56
zegor Дата: Пятница, 04.05.2018, 12:42 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, большое спасибо. Ваш вариант работает как надо.
 
Ответить
Сообщение_Boroda_, большое спасибо. Ваш вариант работает как надо.
Автор - zegor
Дата добавления - 04.05.2018 в 12:42
zegor Дата: Пятница, 04.05.2018, 13:01 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, благодарю за ответ. Заработало только когда понял что это формула массива у Вас в ячейках. Считает как надо, только при наличии меньше пяти случаев соответствия критерию считает то что есть (три значит три и так далее). То есть единственный минус– я не замечаю этого визуально при беглом просмотре. Но всё равно спасибо. Буду думать как изменить формулу что бы видеть, как-нибудь нолик в таких случаях постараюсь получать.
 
Ответить
Сообщениеbmv98rus, благодарю за ответ. Заработало только когда понял что это формула массива у Вас в ячейках. Считает как надо, только при наличии меньше пяти случаев соответствия критерию считает то что есть (три значит три и так далее). То есть единственный минус– я не замечаю этого визуально при беглом просмотре. Но всё равно спасибо. Буду думать как изменить формулу что бы видеть, как-нибудь нолик в таких случаях постараюсь получать.
Автор - zegor
Дата добавления - 04.05.2018 в 13:01
bmv98rus Дата: Пятница, 04.05.2018, 13:13 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата zegor, 04.05.2018 в 13:01, в сообщении № 7 ()
Заработало только когда понял что это формула массива у Вас в ячейках

хм, Ваще это не формула массива,
Чуть измененим
Код
=IF(COUNTIF($A$2:$A21;A22)>4;SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);{1;2;3;4;5});))));"")
К сообщению приложен файл: 4653402.xlsx (17.9 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 04.05.2018, 13:21
 
Ответить
Сообщение
Цитата zegor, 04.05.2018 в 13:01, в сообщении № 7 ()
Заработало только когда понял что это формула массива у Вас в ячейках

хм, Ваще это не формула массива,
Чуть измененим
Код
=IF(COUNTIF($A$2:$A21;A22)>4;SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);{1;2;3;4;5});))));"")
Автор - bmv98rus
Дата добавления - 04.05.2018 в 13:13
zegor Дата: Пятница, 04.05.2018, 13:21 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Наверное не тот файлик прикрепили, в этом прежние формулы. Но я вставил из сообщения, протащил и ничего не изменилось. Например откуда в ячейке G14 результат 130?
К сообщению приложен файл: 5817113.xlsx (17.1 Kb)
 
Ответить
СообщениеНаверное не тот файлик прикрепили, в этом прежние формулы. Но я вставил из сообщения, протащил и ничего не изменилось. Например откуда в ячейке G14 результат 130?
Автор - zegor
Дата добавления - 04.05.2018 в 13:21
zegor Дата: Пятница, 04.05.2018, 13:24 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, уже заметил отредактированное сообщение. Спасибо, всё работает))
 
Ответить
Сообщениеbmv98rus, уже заметил отредактированное сообщение. Спасибо, всё работает))
Автор - zegor
Дата добавления - 04.05.2018 в 13:24
Светлый Дата: Пятница, 04.05.2018, 13:26 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Все решения работают неправильно, если сверху меньше пяти суммируемых элементов.
Небольшая модификация от _Boroda_:
Код
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Пятница, 04.05.2018, 13:45
 
Ответить
СообщениеВсе решения работают неправильно, если сверху меньше пяти суммируемых элементов.
Небольшая модификация от _Boroda_:
Код
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%
Автор - Светлый
Дата добавления - 04.05.2018 в 13:26
zegor Дата: Пятница, 04.05.2018, 13:31 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Светлый, только теперь считает то что не должен был. Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".
 
Ответить
СообщениеСветлый, только теперь считает то что не должен был. Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".
Автор - zegor
Дата добавления - 04.05.2018 в 13:31
Светлый Дата: Пятница, 04.05.2018, 13:38 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата zegor, 04.05.2018 в 13:31, в сообщении № 12 ()
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто"

То есть, если "3A" в 11 строке встречается третий раз, то должно быть пусто? Не сумма предыдущих двух?

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата zegor, 04.05.2018 в 13:31, в сообщении № 12 ()
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто"

То есть, если "3A" в 11 строке встречается третий раз, то должно быть пусто? Не сумма предыдущих двух?
Автор - Светлый
Дата добавления - 04.05.2018 в 13:38
zegor Дата: Пятница, 04.05.2018, 15:45 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Да.
 
Ответить
СообщениеДа.
Автор - zegor
Дата добавления - 04.05.2018 в 15:45
Светлый Дата: Пятница, 04.05.2018, 16:11 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Код
=ЕСЛИОШИБКА(СУММ(ОСТАТ(НАИБОЛЬШИЙ(ЕСЛИ(A$2:A21=A22;СТРОКА($2:21)+C$2:C21%%%);{1;2;3;4;5});1))/1%%%;"")

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(СУММ(ОСТАТ(НАИБОЛЬШИЙ(ЕСЛИ(A$2:A21=A22;СТРОКА($2:21)+C$2:C21%%%);{1;2;3;4;5});1))/1%%%;"")
Автор - Светлый
Дата добавления - 04.05.2018 в 16:11
_Boroda_ Дата: Пятница, 04.05.2018, 16:20 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ()
Код
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%
получается 286,999999999704. Я не знаю, принципиально ли это, возможно, потом по этому столбцу будет ПОИСК или ВПР

zegor,
Цитата zegor, 04.05.2018 в 13:31, в сообщении № 12 ()
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".

Если значение #ЧИСЛО! не устраивает, то просто можно обернуть формулу в ЕСЛИОШИБКА. Вот так:
Код
=ЕСЛИОШИБКА(СУММ(--ПСТР(НАИБОЛЬШИЙ(ЕСЛИ($A1:$A$2=$A2;СТРОКА(C1:C$2)*10^6+C1:C$2);СТРОКА(C$1:C$5));4;9));)


*
А вот так уже да, не будет дробности
Цитата Светлый, 04.05.2018 в 16:11, в сообщении № 15 ()
Код
=ЕСЛИОШИБКА(СУММ(ОСТАТ(НАИБОЛЬШИЙ(ЕСЛИ(A$2:A21=A22;СТРОКА($2:21)+C$2:C21%%%);{1;2;3;4;5});1))/1%%%;"")
К сообщению приложен файл: L5_2.xlsx (20.4 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ()
Код
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%
получается 286,999999999704. Я не знаю, принципиально ли это, возможно, потом по этому столбцу будет ПОИСК или ВПР

zegor,
Цитата zegor, 04.05.2018 в 13:31, в сообщении № 12 ()
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".

Если значение #ЧИСЛО! не устраивает, то просто можно обернуть формулу в ЕСЛИОШИБКА. Вот так:
Код
=ЕСЛИОШИБКА(СУММ(--ПСТР(НАИБОЛЬШИЙ(ЕСЛИ($A1:$A$2=$A2;СТРОКА(C1:C$2)*10^6+C1:C$2);СТРОКА(C$1:C$5));4;9));)


*
А вот так уже да, не будет дробности
Цитата Светлый, 04.05.2018 в 16:11, в сообщении № 15 ()
Код
=ЕСЛИОШИБКА(СУММ(ОСТАТ(НАИБОЛЬШИЙ(ЕСЛИ(A$2:A21=A22;СТРОКА($2:21)+C$2:C21%%%);{1;2;3;4;5});1))/1%%%;"")
Автор - _Boroda_
Дата добавления - 04.05.2018 в 16:20
zegor Дата: Пятница, 04.05.2018, 16:27 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 132
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
_Boroda_,
Цитата
Я специально не стал уносить значения в дробную часть (была сначала такая мысль), чтобы не получить итогом дробное значение (или придется использовать ОКРУГЛ). Пример - строка 8. Должно получиться 287 ровно, а с
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ( писал(а)):
Перевод / Translate
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%
получается 286,999999999704. Я не знаю, принципиально ли это, возможно, потом по этому столбцу будет ПОИСК или ВПР
не принципиально. За вторую часть тоже большое спасибо.
 
Ответить
Сообщение_Boroda_,
Цитата
Я специально не стал уносить значения в дробную часть (была сначала такая мысль), чтобы не получить итогом дробное значение (или придется использовать ОКРУГЛ). Пример - строка 8. Должно получиться 287 ровно, а с
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ( писал(а)):
Перевод / Translate
=СУММ(ОСТАТ(НАИБОЛЬШИЙ((A$2:A21=A22)*(СТРОКА($2:21)+C$2:C21%%%);СТРОКА($1:$5));1))/1%%%
получается 286,999999999704. Я не знаю, принципиально ли это, возможно, потом по этому столбцу будет ПОИСК или ВПР
не принципиально. За вторую часть тоже большое спасибо.
Автор - zegor
Дата добавления - 04.05.2018 в 16:27
bmv98rus Дата: Пятница, 04.05.2018, 17:16 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
_Boroda_, Александр, а чем плох мой вариант? (кроме буковок побольше?) Количество операций может даже меньше оказаться для компа.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение_Boroda_, Александр, а чем плох мой вариант? (кроме буковок побольше?) Количество операций может даже меньше оказаться для компа.
Автор - bmv98rus
Дата добавления - 04.05.2018 в 17:16
_Boroda_ Дата: Пятница, 04.05.2018, 18:39 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Михаил, да ничем не плох, отличный вариант. Я, правда, не люблю писать в формулах константы (вдруг потом окажется, что нужно не 5 последних, а 25), но это дело вкуса
А пост выше я написал только из-за того, что
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ()
Небольшая модификация от _Boroda_

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМихаил, да ничем не плох, отличный вариант. Я, правда, не люблю писать в формулах константы (вдруг потом окажется, что нужно не 5 последних, а 25), но это дело вкуса
А пост выше я написал только из-за того, что
Цитата Светлый, 04.05.2018 в 13:26, в сообщении № 11 ()
Небольшая модификация от _Boroda_
Автор - _Boroda_
Дата добавления - 04.05.2018 в 18:39
bmv98rus Дата: Пятница, 04.05.2018, 19:04 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 04.05.2018 в 18:39, в сообщении № 19 ()
Я, правда, не люблю писать в формулах константы
аналогично, это один из редких моментов кода использовал, показалось нагляднее, а так просто выделить диапазон проще, чем 1,2,3 ..... или ваще динамику сделать Row($A$1:Index(...... ) :-)
Код
=IF(COUNTIF($A$2:$A21;A22)>4;SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);ROW($1:$5));))));"")

Я признаться думал что INDEX(C:C;N(INDEX( подвох имеет

P.S. Но --ПСТР( меня сперва удивил, потом озадачил, а потом взят на вооружение.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата _Boroda_, 04.05.2018 в 18:39, в сообщении № 19 ()
Я, правда, не люблю писать в формулах константы
аналогично, это один из редких моментов кода использовал, показалось нагляднее, а так просто выделить диапазон проще, чем 1,2,3 ..... или ваще динамику сделать Row($A$1:Index(...... ) :-)
Код
=IF(COUNTIF($A$2:$A21;A22)>4;SUMPRODUCT(INDEX(C:C;N(INDEX(LARGE(ROW($2:21)*($A$2:$A21=A22);ROW($1:$5));))));"")

Я признаться думал что INDEX(C:C;N(INDEX( подвох имеет

P.S. Но --ПСТР( меня сперва удивил, потом озадачил, а потом взят на вооружение.
Автор - bmv98rus
Дата добавления - 04.05.2018 в 19:04
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммировать 5 последних по критерию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!