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

Вход

Регистрация

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

 

= Мир MS Excel/Нетипичное СУММЕСЛИ - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нетипичное СУММЕСЛИ (Формулы/Formulas)
Нетипичное СУММЕСЛИ
Manner Дата: Суббота, 18.09.2021, 20:31 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Уважаемые знатоки, доброго времени суток)
Существует ли волшебная формула для вот какой задачи? (я исходные данные упростил, чтобы было проще). В приложенном файле мне необходимо просуммировать значения ячеек в столбце В, соответствующие значению "ЗЕЛЕНЫЙ" из столбца А, но суммировать нужно только в том случае, если в столбце А после "ЗЕЛЕНЫЙ" обязательно сначала идет "ЖЕЛТЫЙ" и после идет "КРАСНЫЙ" (ячейки выделенные более крупным шрифтом соответствуют требованию, а более мелким нет). НО самая прелесть в том, что значений "ЖЕЛТЫЙ", которые идут после "ЗЕЛЕНЫЙ" может быть абсолютно разное количество (как пример столбцы Е F - тут условия задачи также выполнены - после значений "ЗЕЛЕНЫЙ" идет сначала "ЖЕЛТЫЙ", а потом "КРАСНЫЙ", поэтому необходимо посчитать сумму). Возможно, решение элементарно, но я как не пытался, моя извилина не справляется с этим. Буду благодарен за помощь
С уважением ко всем
К сообщению приложен файл: 3807153.xlsx (8.9 Kb)
 
Ответить
СообщениеУважаемые знатоки, доброго времени суток)
Существует ли волшебная формула для вот какой задачи? (я исходные данные упростил, чтобы было проще). В приложенном файле мне необходимо просуммировать значения ячеек в столбце В, соответствующие значению "ЗЕЛЕНЫЙ" из столбца А, но суммировать нужно только в том случае, если в столбце А после "ЗЕЛЕНЫЙ" обязательно сначала идет "ЖЕЛТЫЙ" и после идет "КРАСНЫЙ" (ячейки выделенные более крупным шрифтом соответствуют требованию, а более мелким нет). НО самая прелесть в том, что значений "ЖЕЛТЫЙ", которые идут после "ЗЕЛЕНЫЙ" может быть абсолютно разное количество (как пример столбцы Е F - тут условия задачи также выполнены - после значений "ЗЕЛЕНЫЙ" идет сначала "ЖЕЛТЫЙ", а потом "КРАСНЫЙ", поэтому необходимо посчитать сумму). Возможно, решение элементарно, но я как не пытался, моя извилина не справляется с этим. Буду благодарен за помощь
С уважением ко всем
Автор - Manner
Дата добавления - 18.09.2021 в 20:31
Светлый Дата: Воскресенье, 19.09.2021, 11:51 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Доброго!
А суммировать все или порциями?
Насколько я понял, столбец большой и таких сочетаний может быть несколько. Суммировать всё разом или каждое сочетание по отдельности?
Суммировать все зелёные перед жёлтым или только одно?

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДоброго!
А суммировать все или порциями?
Насколько я понял, столбец большой и таких сочетаний может быть несколько. Суммировать всё разом или каждое сочетание по отдельности?
Суммировать все зелёные перед жёлтым или только одно?
Автор - Светлый
Дата добавления - 19.09.2021 в 11:51
Manner Дата: Воскресенье, 19.09.2021, 14:03 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Светлый, суммировать все
Столбец большой, сочетаний может быть несколько - это верно. Суммировать нужно все разом и именно все зеленые, но именно если после идет желтый а потом красны
 
Ответить
СообщениеСветлый, суммировать все
Столбец большой, сочетаний может быть несколько - это верно. Суммировать нужно все разом и именно все зеленые, но именно если после идет желтый а потом красны
Автор - Manner
Дата добавления - 19.09.2021 в 14:03
Светлый Дата: Воскресенье, 19.09.2021, 20:30 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Сложная задачка. Проверил только на предоставленных данных. Формула массива:
Код
=СУММ(((ABS(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(3:93)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})-5)=2)+(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(3:93)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})=1)*(ОСТАТ(LOG(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(2:92)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})+1;2);1)=0))*B2:B92)
Проверяйте.
*Не всегда работает

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

Сообщение отредактировал Светлый - Воскресенье, 19.09.2021, 20:46
 
Ответить
СообщениеСложная задачка. Проверил только на предоставленных данных. Формула массива:
Код
=СУММ(((ABS(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(3:93)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})-5)=2)+(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(3:93)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})=1)*(ОСТАТ(LOG(МУМНОЖ(Ч(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(2:92)-{2;1;0};)))={"з";"ж";"к"});{1:2:4})+1;2);1)=0))*B2:B92)
Проверяйте.
*Не всегда работает
Автор - Светлый
Дата добавления - 19.09.2021 в 20:30
Manner Дата: Воскресенье, 19.09.2021, 20:58 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Светлый, логика формулы понятна попробую сейчас на нужных данных ее в деле
Спасибо большое
 
Ответить
СообщениеСветлый, логика формулы понятна попробую сейчас на нужных данных ее в деле
Спасибо большое
Автор - Manner
Дата добавления - 19.09.2021 в 20:58
Светлый Дата: Понедельник, 20.09.2021, 00:08 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата Manner, 19.09.2021 в 20:58, в сообщении № 5 ()
логика формулы понятна
Для нескольких жёлтых не работает.
Можно решить только суммой диапазонов. Вот массивная формула для трёх диапазонов:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));1))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));1));)+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));2))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));2));)+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));3))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));3));)+0))
Чтобы добавить диапазоны, можно +0 заменить на:
Код
+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));1))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));1));)
и в функциях НАИМЕНЬШИЙ задать следующее значение 4.
Формула неоптимизированная. Очень длинная. Проще было бы с дополнительным столбцом.

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

Сообщение отредактировал Светлый - Понедельник, 20.09.2021, 00:11
 
Ответить
Сообщение
Цитата Manner, 19.09.2021 в 20:58, в сообщении № 5 ()
логика формулы понятна
Для нескольких жёлтых не работает.
Можно решить только суммой диапазонов. Вот массивная формула для трёх диапазонов:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));1))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));1));)+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));2))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));2));)+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));3))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));3));)+0))
Чтобы добавить диапазоны, можно +0 заменить на:
Код
+ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));1))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));1));)
и в функциях НАИМЕНЬШИЙ задать следующее значение 4.
Формула неоптимизированная. Очень длинная. Проще было бы с дополнительным столбцом.
Автор - Светлый
Дата добавления - 20.09.2021 в 00:08
Светлый Дата: Понедельник, 20.09.2021, 06:24 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Вчера совсем крыша съехала. Получилось короткой формулой:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));СТОЛБЕЦ(A:I)));)))
Можно ещё короче, но пока не могу оптимизировать.
*Чуть короче:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2;СТРОКА(1:92));СТОЛБЕЦ(A:I)));)))
**Ещё короче:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>=НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(СТРОКА(2:93)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*СТРОКА(1:92);СТОЛБЕЦ(A:I)));)))
***Тут ошибок уже не бывает. Исключим эту функцию:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*((СТРОКА(1:92)>НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*СТРОКА(1:92);СТОЛБЕЦ(A:I)))))

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

Сообщение отредактировал Светлый - Понедельник, 20.09.2021, 13:57
 
Ответить
СообщениеВчера совсем крыша съехала. Получилось короткой формулой:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);ЕСЛИ(ABS(МУМНОЖ(ПОИСК(ЛЕВБ(Т(СМЕЩ(A1;СТРОКА(1:91)-{1;0};)));" зжк");{-1:1})-1/2)>1;СТРОКА(1:91);))>2;СТРОКА(1:92));СТОЛБЕЦ(A:I)));)))
Можно ещё короче, но пока не могу оптимизировать.
*Чуть короче:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>НАИМЕНЬШИЙ(ЕСЛИ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный");СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2;СТРОКА(1:92));СТОЛБЕЦ(A:I)));)))
**Ещё короче:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*(ЕСЛИОШИБКА((СТРОКА(1:92)>=НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(СТРОКА(2:93)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*СТРОКА(1:92);СТОЛБЕЦ(A:I)));)))
***Тут ошибок уже не бывает. Исключим эту функцию:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*((СТРОКА(1:92)>НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*СТРОКА(1:92);СТОЛБЕЦ(A:I)))))
Автор - Светлый
Дата добавления - 20.09.2021 в 06:24
Светлый Дата: Понедельник, 20.09.2021, 13:59 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Есть такие сочетания, где предыдущие формулы не работали.
Надеюсь, эта будет правильно считать:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*((СТРОКА(1:92)>НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный")*(СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный")*СТРОКА(1:92);СТОЛБЕЦ(A:I)))))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕсть такие сочетания, где предыдущие формулы не работали.
Надеюсь, эта будет правильно считать:
Код
=СУММ(B1:B92*(A1:A92="зеленый")*((СТРОКА(1:92)>НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный")*(СТРОКА(1:92)-ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91)));СТОЛБЕЦ(A:I)))*(СТРОКА(1:92)<НАИБОЛЬШИЙ((ЧАСТОТА(СТРОКА(1:91);(ABS(ПОИСК(ЛЕВБ(A2:A92);" зжк")-ПОИСК(ЛЕВБ(A1:A91);" зжк")-1/2)>1)*СТРОКА(1:91))>2)*(A1:A92="красный")*СТРОКА(1:92);СТОЛБЕЦ(A:I)))))
Автор - Светлый
Дата добавления - 20.09.2021 в 13:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нетипичное СУММЕСЛИ (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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