Нетипичное СУММЕСЛИ
Manner
Дата: Суббота, 18.09.2021, 20:31 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Уважаемые знатоки, доброго времени суток) Существует ли волшебная формула для вот какой задачи? (я исходные данные упростил, чтобы было проще). В приложенном файле мне необходимо просуммировать значения ячеек в столбце В, соответствующие значению "ЗЕЛЕНЫЙ" из столбца А, но суммировать нужно только в том случае, если в столбце А после "ЗЕЛЕНЫЙ" обязательно сначала идет "ЖЕЛТЫЙ" и после идет "КРАСНЫЙ" (ячейки выделенные более крупным шрифтом соответствуют требованию, а более мелким нет). НО самая прелесть в том, что значений "ЖЕЛТЫЙ", которые идут после "ЗЕЛЕНЫЙ" может быть абсолютно разное количество (как пример столбцы Е F - тут условия задачи также выполнены - после значений "ЗЕЛЕНЫЙ" идет сначала "ЖЕЛТЫЙ", а потом "КРАСНЫЙ", поэтому необходимо посчитать сумму). Возможно, решение элементарно, но я как не пытался, моя извилина не справляется с этим. Буду благодарен за помощь С уважением ко всем
Уважаемые знатоки, доброго времени суток) Существует ли волшебная формула для вот какой задачи? (я исходные данные упростил, чтобы было проще). В приложенном файле мне необходимо просуммировать значения ячеек в столбце В, соответствующие значению "ЗЕЛЕНЫЙ" из столбца А, но суммировать нужно только в том случае, если в столбце А после "ЗЕЛЕНЫЙ" обязательно сначала идет "ЖЕЛТЫЙ" и после идет "КРАСНЫЙ" (ячейки выделенные более крупным шрифтом соответствуют требованию, а более мелким нет). НО самая прелесть в том, что значений "ЖЕЛТЫЙ", которые идут после "ЗЕЛЕНЫЙ" может быть абсолютно разное количество (как пример столбцы Е F - тут условия задачи также выполнены - после значений "ЗЕЛЕНЫЙ" идет сначала "ЖЕЛТЫЙ", а потом "КРАСНЫЙ", поэтому необходимо посчитать сумму). Возможно, решение элементарно, но я как не пытался, моя извилина не справляется с этим. Буду благодарен за помощь С уважением ко всем Manner
Ответить
Сообщение Уважаемые знатоки, доброго времени суток) Существует ли волшебная формула для вот какой задачи? (я исходные данные упростил, чтобы было проще). В приложенном файле мне необходимо просуммировать значения ячеек в столбце В, соответствующие значению "ЗЕЛЕНЫЙ" из столбца А, но суммировать нужно только в том случае, если в столбце А после "ЗЕЛЕНЫЙ" обязательно сначала идет "ЖЕЛТЫЙ" и после идет "КРАСНЫЙ" (ячейки выделенные более крупным шрифтом соответствуют требованию, а более мелким нет). НО самая прелесть в том, что значений "ЖЕЛТЫЙ", которые идут после "ЗЕЛЕНЫЙ" может быть абсолютно разное количество (как пример столбцы Е 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
Ответить
Сообщение Светлый, суммировать все Столбец большой, сочетаний может быть несколько - это верно. Суммировать нужно все разом и именно все зеленые, но именно если после идет желтый а потом красны Автор - 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)
Проверяйте. *Не всегда работает
Сложная задачка. Проверил только на предоставленных данных. Формула массива:Код
=СУММ(((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
Ответить
Сообщение Светлый, логика формулы понятна попробую сейчас на нужных данных ее в деле Спасибо большое Автор - Manner Дата добавления - 19.09.2021 в 20:58
Светлый
Дата: Понедельник, 20.09.2021, 00:08 |
Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 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);)));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. Формула неоптимизированная. Очень длинная. Проще было бы с дополнительным столбцом.
Для нескольких жёлтых не работает. Можно решить только суммой диапазонов. Вот массивная формула для трёх диапазонов:Код
=СУММ(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
Ответить
Сообщение Для нескольких жёлтых не работает. Можно решить только суммой диапазонов. Вот массивная формула для трёх диапазонов:Код
=СУММ(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)))))
Вчера совсем крыша съехала. Получилось короткой формулой:Код
=СУММ(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)))))
Светлый
Программировать проще, чем писать стихи.
Ответить
Сообщение Есть такие сочетания, где предыдущие формулы не работали. Надеюсь, эта будет правильно считать:Код
=СУММ(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