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

Вход

Регистрация

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

 

= Мир MS Excel/АГРЕГАТ - ранги диапазона значений с повторами - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » АГРЕГАТ - ранги диапазона значений с повторами (Формулы/Formulas)
АГРЕГАТ - ранги диапазона значений с повторами
panta-rhei-1 Дата: Понедельник, 15.01.2024, 22:18 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 15 ±
Замечаний: 0% ±

2019
Привет знатокам работы с диапазонами значений!

Я немного динозавр в работе с массивами и диапазонами, больше как-то с ячейками...

У меня есть 2 диапазона значений в 2 строки:
- 1 строка: просто номера спортсменов (по возрастанию от 1 до 13)
- 2 строка: набранные очки после соревнования (внимание: есть повторы)

Нужно в один этап - в одной строке (диапазоне ячеек) - вывести отранжированный список номеров спортсменов - согласно набранным очкам. Но без повторов!
Дело в том, что 6 спортсменов набрали одинаковые очки (по 65 очков - аж у 4 спортсменов, по 49 очков - у 2 спортсменов).

В два этапа всё получается*.

А вот в один хитрый этап - никак.
Многоразовая попытка из этой формулы сделать результат - не удалась.

Код
=ИНДЕКС(($A$1:$M$1;$A2:$M2);1;ПОИСКПОЗ(АГРЕГАТ(14;6;$A2:$M2;A1);$A2:$M2;0))
- такая сортировка не учитывает повторяющиеся набранные очки спорсменов и лепит повторы по первому встретившемуся в анализе спортсмену, игнорируя последующих в списке спортсменов с одинаковыми очками.

Прилагаю файл.

Для обозримости решения в два этапа две формулочки:

Код
=РАНГ(A2;$A2:$M2;0)+СЧЁТЕСЛИ($A2:A2;A2)-1

Код
=ПОИСКПОЗ(A1;$A4:$M4;0)


Заранее благодарю!
К сообщению приложен файл: rang_dlja_diapazona_agregat.xlsx (62.9 Kb)
 
Ответить
СообщениеПривет знатокам работы с диапазонами значений!

Я немного динозавр в работе с массивами и диапазонами, больше как-то с ячейками...

У меня есть 2 диапазона значений в 2 строки:
- 1 строка: просто номера спортсменов (по возрастанию от 1 до 13)
- 2 строка: набранные очки после соревнования (внимание: есть повторы)

Нужно в один этап - в одной строке (диапазоне ячеек) - вывести отранжированный список номеров спортсменов - согласно набранным очкам. Но без повторов!
Дело в том, что 6 спортсменов набрали одинаковые очки (по 65 очков - аж у 4 спортсменов, по 49 очков - у 2 спортсменов).

В два этапа всё получается*.

А вот в один хитрый этап - никак.
Многоразовая попытка из этой формулы сделать результат - не удалась.

Код
=ИНДЕКС(($A$1:$M$1;$A2:$M2);1;ПОИСКПОЗ(АГРЕГАТ(14;6;$A2:$M2;A1);$A2:$M2;0))
- такая сортировка не учитывает повторяющиеся набранные очки спорсменов и лепит повторы по первому встретившемуся в анализе спортсмену, игнорируя последующих в списке спортсменов с одинаковыми очками.

Прилагаю файл.

Для обозримости решения в два этапа две формулочки:

Код
=РАНГ(A2;$A2:$M2;0)+СЧЁТЕСЛИ($A2:A2;A2)-1

Код
=ПОИСКПОЗ(A1;$A4:$M4;0)


Заранее благодарю!

Автор - panta-rhei-1
Дата добавления - 15.01.2024 в 22:18
Gustav Дата: Вторник, 16.01.2024, 00:04 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2649
Репутация: 1114 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можно вот такую формулу попробовать, вообще без РАНГа, но с сортировкой:
Код
=INDEX(TRANSPOSE(SORT(TRANSPOSE(A1:M2);2;FALSE;1;TRUE));1;0)

Работает в последних версиях Excel или в Таблицах Google. По ощущениям, в версии Excel 2019 должна работать.

Только по-русски не СОРТИРОВКА, а СОРТ функция называется. Что-то парсер формул "халтурит". Даю тогда еще правильную редакцию на иностранном языке без перевода:
[vba]
Код
=INDEX(TRANSPOSE(SORT(TRANSPOSE(A1:M2);2;FALSE;1;TRUE));1;0)
[/vba]


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 16.01.2024, 00:07
 
Ответить
СообщениеМожно вот такую формулу попробовать, вообще без РАНГа, но с сортировкой:
Код
=INDEX(TRANSPOSE(SORT(TRANSPOSE(A1:M2);2;FALSE;1;TRUE));1;0)

Работает в последних версиях Excel или в Таблицах Google. По ощущениям, в версии Excel 2019 должна работать.

Только по-русски не СОРТИРОВКА, а СОРТ функция называется. Что-то парсер формул "халтурит". Даю тогда еще правильную редакцию на иностранном языке без перевода:
[vba]
Код
=INDEX(TRANSPOSE(SORT(TRANSPOSE(A1:M2);2;FALSE;1;TRUE));1;0)
[/vba]

Автор - Gustav
Дата добавления - 16.01.2024 в 00:04
Serge_007 Дата: Вторник, 16.01.2024, 00:18 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16450
Репутация: 2746 ±
Замечаний: ±

Excel 2016
не СОРТИРОВКА, а СОРТ
Спасибо, поправлю
Ошибка возникает из-за макрофункции SORT, её переводили как СОРТИРОВКА

panta-rhei-1, интересно оформлен файл yes


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
не СОРТИРОВКА, а СОРТ
Спасибо, поправлю
Ошибка возникает из-за макрофункции SORT, её переводили как СОРТИРОВКА

panta-rhei-1, интересно оформлен файл yes

Автор - Serge_007
Дата добавления - 16.01.2024 в 00:18
panta-rhei-1 Дата: Вторник, 16.01.2024, 03:27 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 15 ±
Замечаний: 0% ±

2019
Gustav, спасибо за находку! Но, увы, в моей настольной однопользовательской версии 2019 года не работают формулы для динамических массивов (пробовала и через F2, CTRL^Shift^Enter, пробовала подключиться в настройках к экспериментальным функциям (Office Insiders, кажется). О покупке новой версии 2021 года или ежемесячной 365 даже и не помышляю.

Поэтому по-прежнему нуждаюсь в формуле для старых (ну, какие они старые, если работают на 99%) версий.
Очень надеюсь на светлые головы!
victory
 
Ответить
СообщениеGustav, спасибо за находку! Но, увы, в моей настольной однопользовательской версии 2019 года не работают формулы для динамических массивов (пробовала и через F2, CTRL^Shift^Enter, пробовала подключиться в настройках к экспериментальным функциям (Office Insiders, кажется). О покупке новой версии 2021 года или ежемесячной 365 даже и не помышляю.

Поэтому по-прежнему нуждаюсь в формуле для старых (ну, какие они старые, если работают на 99%) версий.
Очень надеюсь на светлые головы!
victory

Автор - panta-rhei-1
Дата добавления - 16.01.2024 в 03:27
panta-rhei-1 Дата: Вторник, 16.01.2024, 03:35 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 15 ±
Замечаний: 0% ±

2019
Serge_007,
просто не с кем поговорить, когда ковыряешься в формулах, вот и лезет пестрый строителлинг про упоротого Босса)
%)
 
Ответить
СообщениеSerge_007,
просто не с кем поговорить, когда ковыряешься в формулах, вот и лезет пестрый строителлинг про упоротого Босса)
%)

Автор - panta-rhei-1
Дата добавления - 16.01.2024 в 03:35
Gustav Дата: Вторник, 16.01.2024, 10:55 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2649
Репутация: 1114 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
[offtop]
лезет пестрый строителлинг

ОРителлинг (англ. storytelling – «рассказывание историй»)

А "cтРОителлинг" - это, наверное, что-то со стройплощадкой связанное :D


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение[offtop]
лезет пестрый строителлинг

ОРителлинг (англ. storytelling – «рассказывание историй»)

А "cтРОителлинг" - это, наверное, что-то со стройплощадкой связанное :D

Автор - Gustav
Дата добавления - 16.01.2024 в 10:55
Gustav Дата: Вторник, 16.01.2024, 11:24 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2649
Репутация: 1114 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
О покупке новой версии 2021 года или ежемесячной 365 даже и не помышляю.

А у Вас есть учетная запись Microsoft ? Если да, то с ней в office.com и там автоматически предоставляется свободный доступ в Excel для Интернета (Excel for the web). И совершенно бесплатный. И там есть все нужные функции, которых иногда уже так не хватает в старых версиях (до 2021).

Либо обратите свой взгляд в сторону "корпорации добра" Google. Эти вообще не жадничают и новые появляющиеся в Google Sheets функции в течение пары недель становятся доступными на любом компьютере мира. Достаточно иметь аккаунт Google. И тоже всё совершенно бесплатно.

Попробуйте! Раз ваш босс такой рационал, ему должны понравиться подобные прогрессивные фишки. Сделайте ему таблицу Google - пусть в ней он-лайн смотрит "парад маек", а Вы будете со своего рабочего места исходные данные актуализировать.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
О покупке новой версии 2021 года или ежемесячной 365 даже и не помышляю.

А у Вас есть учетная запись Microsoft ? Если да, то с ней в office.com и там автоматически предоставляется свободный доступ в Excel для Интернета (Excel for the web). И совершенно бесплатный. И там есть все нужные функции, которых иногда уже так не хватает в старых версиях (до 2021).

Либо обратите свой взгляд в сторону "корпорации добра" Google. Эти вообще не жадничают и новые появляющиеся в Google Sheets функции в течение пары недель становятся доступными на любом компьютере мира. Достаточно иметь аккаунт Google. И тоже всё совершенно бесплатно.

Попробуйте! Раз ваш босс такой рационал, ему должны понравиться подобные прогрессивные фишки. Сделайте ему таблицу Google - пусть в ней он-лайн смотрит "парад маек", а Вы будете со своего рабочего места исходные данные актуализировать.

Автор - Gustav
Дата добавления - 16.01.2024 в 11:24
Gustav Дата: Вторник, 16.01.2024, 12:21 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2649
Репутация: 1114 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Я сегодня прямо всё утро на манеже. Держите формулу для старых версий (массивную):
Код
=ПОИСКПОЗ(НАИМЕНЬШИЙ(РАНГ(A2:M2;A2:M2;0)+A1:M1%%; A1:M1);РАНГ(A2:M2;A2:M2;0)+A1:M1%%;)

Для ее ввода:
* выделяете горизонтальный диапазон из 13 ячеек, например, A6:M6 (c активной ячейкой A6)
* F2 - переходите в режим редактирования активной ячейки A6
* вводите формулу (вставляете по Ctrl+V после копирования отсюда)
* завершаете ввод комбинацией Ctrl+Shift+Enter

Либо вариант чуть подлиннее, зато со ссылкой только на один диапазон A2:M2 с очками (без ссылки на диапазон A1:M1 с номерами спортсменов):
Код
=ПОИСКПОЗ(НАИМЕНЬШИЙ(РАНГ(A2:M2;A2:M2;0)+СТОЛБЕЦ(A2:M2)%%; СТОЛБЕЦ(A2:M2));РАНГ(A2:M2;A2:M2;0)+СТОЛБЕЦ(A2:M2)%%;)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 16.01.2024, 12:28
 
Ответить
СообщениеЯ сегодня прямо всё утро на манеже. Держите формулу для старых версий (массивную):
Код
=ПОИСКПОЗ(НАИМЕНЬШИЙ(РАНГ(A2:M2;A2:M2;0)+A1:M1%%; A1:M1);РАНГ(A2:M2;A2:M2;0)+A1:M1%%;)

Для ее ввода:
* выделяете горизонтальный диапазон из 13 ячеек, например, A6:M6 (c активной ячейкой A6)
* F2 - переходите в режим редактирования активной ячейки A6
* вводите формулу (вставляете по Ctrl+V после копирования отсюда)
* завершаете ввод комбинацией Ctrl+Shift+Enter

Либо вариант чуть подлиннее, зато со ссылкой только на один диапазон A2:M2 с очками (без ссылки на диапазон A1:M1 с номерами спортсменов):
Код
=ПОИСКПОЗ(НАИМЕНЬШИЙ(РАНГ(A2:M2;A2:M2;0)+СТОЛБЕЦ(A2:M2)%%; СТОЛБЕЦ(A2:M2));РАНГ(A2:M2;A2:M2;0)+СТОЛБЕЦ(A2:M2)%%;)

Автор - Gustav
Дата добавления - 16.01.2024 в 12:21
panta-rhei-1 Дата: Вторник, 16.01.2024, 17:06 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 15 ±
Замечаний: 0% ±

2019
Gustav, Вы - Волшебник!
Огромнейшее спасибо: оба варианта сработали на Ура!!

specool respect clap
Как же я сама не догадалась с процентами выйти из положения!
(вторая формула массива сработала также, если добавить в неё необязательный аргумент тип_сопоставления (именно в нашем случае он оказался важным)
Буду пользоваться первой формулой массива, так как это в примере были столбцы от 1 до 13, а в расчетной таблице всё же это уже никак не A:M, где-то там столбики после BB!

Попробовала также Ваш "ход расчета" с процентами и дошла до формулы для ячеек (с этой неповоротливой функцией АГРЕГАТ): - Ура2, Успех!
Код
=ОКРУГЛ((АГРЕГАТ(15;6;РАНГ($A$2:$M$2;$A$2:$M$2;0)+$A$1:$M$1%%;$A$1:$M$1)-АГРЕГАТ(15;6;РАНГ($A$2:$M$2;$A$2:$M$2;0)+0;$A$1:$M$1))*10000;0)


И спасибо отдельно за пояснения, как правильно обращаться с массивами, машинально я нажимала раньше вместо Shift почему-то Space (Пробел, видимо, в знаниях у меня такой же длиннющий). Excel for the web и Google Таблицы - возьму на заметку в свой "арсенал")) girl_curtsey На выходных попробую там Вашу изначальную формулу СОРТ, ТРАНСП (отпишусь, конечно!)).
Ах, да, опечатка, Сторителлинг! Он уже теперь во всех сферах, не только в кинодраматургии.
К сообщению приложен файл: 6678437.xlsx (64.5 Kb)


Сообщение отредактировал panta-rhei-1 - Вторник, 16.01.2024, 18:55
 
Ответить
СообщениеGustav, Вы - Волшебник!
Огромнейшее спасибо: оба варианта сработали на Ура!!

specool respect clap
Как же я сама не догадалась с процентами выйти из положения!
(вторая формула массива сработала также, если добавить в неё необязательный аргумент тип_сопоставления (именно в нашем случае он оказался важным)
Буду пользоваться первой формулой массива, так как это в примере были столбцы от 1 до 13, а в расчетной таблице всё же это уже никак не A:M, где-то там столбики после BB!

Попробовала также Ваш "ход расчета" с процентами и дошла до формулы для ячеек (с этой неповоротливой функцией АГРЕГАТ): - Ура2, Успех!
Код
=ОКРУГЛ((АГРЕГАТ(15;6;РАНГ($A$2:$M$2;$A$2:$M$2;0)+$A$1:$M$1%%;$A$1:$M$1)-АГРЕГАТ(15;6;РАНГ($A$2:$M$2;$A$2:$M$2;0)+0;$A$1:$M$1))*10000;0)


И спасибо отдельно за пояснения, как правильно обращаться с массивами, машинально я нажимала раньше вместо Shift почему-то Space (Пробел, видимо, в знаниях у меня такой же длиннющий). Excel for the web и Google Таблицы - возьму на заметку в свой "арсенал")) girl_curtsey На выходных попробую там Вашу изначальную формулу СОРТ, ТРАНСП (отпишусь, конечно!)).
Ах, да, опечатка, Сторителлинг! Он уже теперь во всех сферах, не только в кинодраматургии.

Автор - panta-rhei-1
Дата добавления - 16.01.2024 в 17:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » АГРЕГАТ - ранги диапазона значений с повторами (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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