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

Вход

Регистрация

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

 

= Мир MS Excel/сумма трех наибольших для каждого цвета - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » сумма трех наибольших для каждого цвета (Формулы/Formulas)
сумма трех наибольших для каждого цвета
dude Дата: Суббота, 15.09.2018, 15:41 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Добрый день!
Необходимо полчить сумму сумм 3 наибольших для каждого цвета.
Решение с промежуточными формулами есть
Возможно ли в одну?
К сообщению приложен файл: xcv15.xlsx (9.9 Kb)
 
Ответить
СообщениеДобрый день!
Необходимо полчить сумму сумм 3 наибольших для каждого цвета.
Решение с промежуточными формулами есть
Возможно ли в одну?
Автор - dude
Дата добавления - 15.09.2018 в 15:41
Светлый Дата: Суббота, 15.09.2018, 23:57 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый вечер!
Сумму максимальных для каждого цвета найти удалось. Массивная формула:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21)))))

А по три наибольших - пока никаких идей.
*Всё-таки получилось:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21)))))))))

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

Сообщение отредактировал Светлый - Воскресенье, 16.09.2018, 00:14
 
Ответить
СообщениеДобрый вечер!
Сумму максимальных для каждого цвета найти удалось. Массивная формула:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21)))))

А по три наибольших - пока никаких идей.
*Всё-таки получилось:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^СТРОКА(2:21)))))))))
Автор - Светлый
Дата добавления - 15.09.2018 в 23:57
bmv98rus Дата: Воскресенье, 16.09.2018, 08:35 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 15.09.2018 в 23:57, в сообщении № 2 ()
Сумму максимальных для каждого цвета

Весьма интересно, взял на заметку, хотя есть ограничения для второй формулы, значения максимальных трех в группе не должны расходится более чем на 15, ибо потом идет потеря значащих цифр при возведении в степень. Например для RED не 2;5;5, а 2;5;17 - 10^17+10^5+10^2 = даст 100000000000100000, и 10^2 потеряно.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 15.09.2018 в 23:57, в сообщении № 2 ()
Сумму максимальных для каждого цвета

Весьма интересно, взял на заметку, хотя есть ограничения для второй формулы, значения максимальных трех в группе не должны расходится более чем на 15, ибо потом идет потеря значащих цифр при возведении в степень. Например для RED не 2;5;5, а 2;5;17 - 10^17+10^5+10^2 = даст 100000000000100000, и 10^2 потеряно.
Автор - bmv98rus
Дата добавления - 16.09.2018 в 08:35
Светлый Дата: Воскресенье, 16.09.2018, 09:55 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 16.09.2018 в 08:35, в сообщении № 3 ()
есть ограничения для второй формулы

Совершенно верно. Это связано с ограничениями разрядности обработки чисел в компьютере.
Кроме того, числа должны быть целыми.
Можно увеличить эту разницу не более 15 до 31, если в формуле перейти на 4^значение, но тогда возникает другое ограничение - количество ОДИНАКОВЫХ значений не должно превысить 3. Если использовать основание логарифма 8, то до семи одинаковых может быть, а разница 20-21.
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));4));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4))-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));4));4)))

И немного сократил предыдущую формулу:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21))))))))

И ещё ограничение: количество элементов каждого цвета должно быть не менее трёх. Хотя можно использовать ЕСЛИОШИБКА:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))));ЕСЛИОШИБКА(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))))));))
К сообщению приложен файл: xcv15-1.xlsx (10.6 Kb)

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата bmv98rus, 16.09.2018 в 08:35, в сообщении № 3 ()
есть ограничения для второй формулы

Совершенно верно. Это связано с ограничениями разрядности обработки чисел в компьютере.
Кроме того, числа должны быть целыми.
Можно увеличить эту разницу не более 15 до 31, если в формуле перейти на 4^значение, но тогда возникает другое ограничение - количество ОДИНАКОВЫХ значений не должно превысить 3. Если использовать основание логарифма 8, то до семи одинаковых может быть, а разница 20-21.
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));4));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4))-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21)-4^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*4^B2:B21);1^B2:B21);4));4));4)))

И немного сократил предыдущую формулу:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21))))))))

И ещё ограничение: количество элементов каждого цвета должно быть не менее трёх. Хотя можно использовать ЕСЛИОШИБКА:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)));ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))));ЕСЛИОШИБКА(ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)-10^ОТБР(LOG(МУМНОЖ(ТРАНСП((A2:A21=H1:M1)*10^B2:B21);1^B2:B21)))))));))
Автор - Светлый
Дата добавления - 16.09.2018 в 09:55
dude Дата: Воскресенье, 16.09.2018, 10:07 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Светлый, потрясающе! 486 знаков
а я не додумал такую индексацию
 
Ответить
СообщениеСветлый, потрясающе! 486 знаков
а я не додумал такую индексацию
Автор - dude
Дата добавления - 16.09.2018 в 10:07
Светлый Дата: Воскресенье, 16.09.2018, 11:08 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата dude, 16.09.2018 в 10:07, в сообщении № 5 ()
486 знаков

Немного причесал формулу. 381 знак:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)));ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)))));ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))))))))
К сообщению приложен файл: xcv15-2.xlsx (11.7 Kb)

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата dude, 16.09.2018 в 10:07, в сообщении № 5 ()
486 знаков

Немного причесал формулу. 381 знак:
Код
=СУММ(ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)));ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)))));ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)))-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ОТБР(LOG(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))))))))
Автор - Светлый
Дата добавления - 16.09.2018 в 11:08
Светлый Дата: Понедельник, 17.09.2018, 09:19 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Решил иначе вытягивать максимумы. Сократил формулу до 371 символа. Недостатки те же.
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-3;ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1));ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1))-1)))
Но, чувствую, можно где-то ещё сократить сильно.
* Так оно и оказалось. 287 символов.
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-ВЫБОР({1;2;3};;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10+10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10)/10))-1)

**Ещё немного причесал (281).
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-ВЫБОР({1;2;3};;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21));10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))+10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10))/10)-1)

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

Сообщение отредактировал Светлый - Понедельник, 17.09.2018, 15:01
 
Ответить
СообщениеРешил иначе вытягивать максимумы. Сократил формулу до 371 символа. Недостатки те же.
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-3;ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1));ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))-1))-1)))
Но, чувствую, можно где-то ещё сократить сильно.
* Так оно и оказалось. 287 символов.
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-ВЫБОР({1;2;3};;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10+10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10)/10))-1)

**Ещё немного причесал (281).
Код
=СУММ(ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-ВЫБОР({1;2;3};;10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21));10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))+10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21)-10^ДЛСТР(МУМНОЖ(--ТРАНСП(A2:A21=H1:M1);10^B2:B21))/10))/10)-1)
Автор - Светлый
Дата добавления - 17.09.2018 в 09:19
Мир MS Excel » Вопросы и решения » Вопросы по Excel » сумма трех наибольших для каждого цвета (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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