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

Вход

Регистрация

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

 

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

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

Excel 2007
Привет, excelworld. Помоги пожалуйста справиться с такой проблемой– пытаюсь суммировать пять последних значений по критерию в растущем массиве. Сам могу посчитать просто сумму с помощью СУММЕСЛИ. А вот как ограничить суммирование только пятью последними соответствующими критерию отбора не пойму. Я вижу это примерно так =суммесли(диапазон;критерий;ПЯТЬ ПОСЛЕДНИХ).
К сообщению приложен файл: L5.xlsx(16.6 Kb)
 
Ответить
СообщениеПривет, excelworld. Помоги пожалуйста справиться с такой проблемой– пытаюсь суммировать пять последних значений по критерию в растущем массиве. Сам могу посчитать просто сумму с помощью СУММЕСЛИ. А вот как ограничить суммирование только пятью последними соответствующими критерию отбора не пойму. Я вижу это примерно так =суммесли(диапазон;критерий;ПЯТЬ ПОСЛЕДНИХ).

Автор - zegor
Дата добавления - 04.05.2018 в 11:33
bmv98rus Дата: Пятница, 04.05.2018, 11:39 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1332
Репутация: 227 ±
Замечаний: 0% ±

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


Сообщение отредактировал bmv98rus - Пятница, 04.05.2018, 11:54
 
Ответить
Сообщениепять последних по количеству или по качеству? Я про то что это могут быть пять равных. увидел в примере.

Автор - bmv98rus
Дата добавления - 04.05.2018 в 11:39
zegor Дата: Пятница, 04.05.2018, 11:44 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, по количеству наверное. Не совсем понял вопрос просто. Пять последних в столбце (сверху вниз), нижних. Тех которые напротив критерия А1.
 
Ответить
Сообщениеbmv98rus, по количеству наверное. Не совсем понял вопрос просто. Пять последних в столбце (сверху вниз), нижних. Тех которые напротив критерия А1.

Автор - zegor
Дата добавления - 04.05.2018 в 11:44
_Boroda_ Дата: Пятница, 04.05.2018, 11:49 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13251
Репутация: 5457 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1332
Репутация: 227 ±
Замечаний: 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)


Сообщение отредактировал 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, большое спасибо. Ваш вариант работает как надо.
 
Ответить
Сообщение_Boroda_, большое спасибо. Ваш вариант работает как надо.

Автор - zegor
Дата добавления - 04.05.2018 в 12:42
zegor Дата: Пятница, 04.05.2018, 13:01 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, благодарю за ответ. Заработало только когда понял что это формула массива у Вас в ячейках. Считает как надо, только при наличии меньше пяти случаев соответствия критерию считает то что есть (три значит три и так далее). То есть единственный минус– я не замечаю этого визуально при беглом просмотре. Но всё равно спасибо. Буду думать как изменить формулу что бы видеть, как-нибудь нолик в таких случаях постараюсь получать.
 
Ответить
Сообщениеbmv98rus, благодарю за ответ. Заработало только когда понял что это формула массива у Вас в ячейках. Считает как надо, только при наличии меньше пяти случаев соответствия критерию считает то что есть (три значит три и так далее). То есть единственный минус– я не замечаю этого визуально при беглом просмотре. Но всё равно спасибо. Буду думать как изменить формулу что бы видеть, как-нибудь нолик в таких случаях постараюсь получать.

Автор - zegor
Дата добавления - 04.05.2018 в 13:01
bmv98rus Дата: Пятница, 04.05.2018, 13:13 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1332
Репутация: 227 ±
Замечаний: 0% ±

Excel 2013/2016
Заработало только когда понял что это формула массива у Вас в ячейках

хм, Ваще это не формула массива,
Чуть измененим
Код
=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)


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

хм, Ваще это не формула массива,
Чуть измененим
Код
=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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, уже заметил отредактированное сообщение. Спасибо, всё работает))
 
Ответить
Сообщениеbmv98rus, уже заметил отредактированное сообщение. Спасибо, всё работает))

Автор - zegor
Дата добавления - 04.05.2018 в 13:24
Светлый Дата: Пятница, 04.05.2018, 13:26 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 658
Репутация: 179 ±
Замечаний: 0% ±

Excel 2010
Все решения работают неправильно, если сверху меньше пяти суммируемых элементов.
Небольшая модификация от _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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Светлый, только теперь считает то что не должен был. Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".
 
Ответить
СообщениеСветлый, только теперь считает то что не должен был. Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто".

Автор - zegor
Дата добавления - 04.05.2018 в 13:31
Светлый Дата: Пятница, 04.05.2018, 13:38 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 658
Репутация: 179 ±
Замечаний: 0% ±

Excel 2010
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто"

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


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Если меньше пяти то надо получать ноль или как сделал bmv98rus "пусто"

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

Автор - Светлый
Дата добавления - 04.05.2018 в 13:38
zegor Дата: Пятница, 04.05.2018, 15:45 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Да.
 
Ответить
СообщениеДа.

Автор - zegor
Дата добавления - 04.05.2018 в 15:45
Светлый Дата: Пятница, 04.05.2018, 16:11 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 658
Репутация: 179 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИОШИБКА(СУММ(ОСТАТ(НАИБОЛЬШИЙ(ЕСЛИ(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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13251
Репутация: 5457 ±
Замечаний: 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,
Если меньше пяти то надо получать ноль или как сделал 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,
Если меньше пяти то надо получать ноль или как сделал 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1332
Репутация: 227 ±
Замечаний: 0% ±

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

Автор - bmv98rus
Дата добавления - 04.05.2018 в 17:16
_Boroda_ Дата: Пятница, 04.05.2018, 18:39 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13251
Репутация: 5457 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1332
Репутация: 227 ±
Замечаний: 0% ±

Excel 2013/2016
Я, правда, не люблю писать в формулах константы
аналогично, это один из редких моментов кода использовал, показалось нагляднее, а так просто выделить диапазон проще, чем 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. Но --ПСТР( меня сперва удивил, потом озадачил, а потом взят на вооружение.
 
Ответить
Сообщение
Я, правда, не люблю писать в формулах константы
аналогично, это один из редких моментов кода использовал, показалось нагляднее, а так просто выделить диапазон проще, чем 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-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!