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

Вход

Регистрация

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

 

= Мир MS Excel/Нахождение N максимумов в массиве, в т.ч. повторяющихся - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нахождение N максимумов в массиве, в т.ч. повторяющихся (Формулы/Formulas)
Нахождение N максимумов в массиве, в т.ч. повторяющихся
daledale Дата: Воскресенье, 27.01.2019, 21:46 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Доброго, уважаемые гуру. Знаю аналогичный вопрос задавался, но я только осваиваю Excel и так и не смог переделать те формулы, что встречал под свою задачу. Суть - есть массив из множества строк и столбцов. У каждого столбца есть заголовок. Для простоты, в примере одна строка (точнее одна строка со значениями + строка с заголовками). Нужно в ней найти максимумы значений по убыванию и вывести отдельно как значение максимума, так и заголовок столбца, в котором этот максимум находится. (см.файл)
Я начал было делать это с помощью формул ПОИСКПОЗ и НАИБОЛЬШЕЕ. И вроде бы всё хорошо - если к функции НАИБОЛЬШЕЕ вопросов нет, то вот с ПОИСКПОЗ возникла загвоздка - как известно, функция ПОИСКПОЗ, находит только первую позицию максимума, и даже если этих максимумов несколько, то выводится всё равно только позиция первого.
В общем во вложении собс-но массив и что нужно получить - чуть правее.
Большая просьба, формула должна быть универсальной - к вопросу о том, что со временем, необходимость в количестве найденных максимумов может возрасти, вплоть до длины массива.
Собс-но сам массив состоит из только целых положительных чисел от 1 и до (в теории) до бесконечности.
ps Excel 2007.
К сообщению приложен файл: excel_found_max.xls (14.0 Kb)
 
Ответить
СообщениеДоброго, уважаемые гуру. Знаю аналогичный вопрос задавался, но я только осваиваю Excel и так и не смог переделать те формулы, что встречал под свою задачу. Суть - есть массив из множества строк и столбцов. У каждого столбца есть заголовок. Для простоты, в примере одна строка (точнее одна строка со значениями + строка с заголовками). Нужно в ней найти максимумы значений по убыванию и вывести отдельно как значение максимума, так и заголовок столбца, в котором этот максимум находится. (см.файл)
Я начал было делать это с помощью формул ПОИСКПОЗ и НАИБОЛЬШЕЕ. И вроде бы всё хорошо - если к функции НАИБОЛЬШЕЕ вопросов нет, то вот с ПОИСКПОЗ возникла загвоздка - как известно, функция ПОИСКПОЗ, находит только первую позицию максимума, и даже если этих максимумов несколько, то выводится всё равно только позиция первого.
В общем во вложении собс-но массив и что нужно получить - чуть правее.
Большая просьба, формула должна быть универсальной - к вопросу о том, что со временем, необходимость в количестве найденных максимумов может возрасти, вплоть до длины массива.
Собс-но сам массив состоит из только целых положительных чисел от 1 и до (в теории) до бесконечности.
ps Excel 2007.

Автор - daledale
Дата добавления - 27.01.2019 в 21:46
vikttur Дата: Воскресенье, 27.01.2019, 21:48 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Значение из заголовка - формулой массива:
Код
=ИНДЕКС($A$1:$N$1;ПРАВБ(НАИБОЛЬШИЙ($A$2:$N$2+СТОЛБЕЦ($A$2:$N$2)*0,001;СТОЛБЕЦ(B1)/2);3))

Наибольшие по убыванию найти с помощью функции
Код
НАИБОЛЬШИЙ()

Или, ориентируясь на полученный заголовок -
Код
ГПР()


Сообщение отредактировал vikttur - Воскресенье, 27.01.2019, 21:58
 
Ответить
СообщениеЗначение из заголовка - формулой массива:
Код
=ИНДЕКС($A$1:$N$1;ПРАВБ(НАИБОЛЬШИЙ($A$2:$N$2+СТОЛБЕЦ($A$2:$N$2)*0,001;СТОЛБЕЦ(B1)/2);3))

Наибольшие по убыванию найти с помощью функции
Код
НАИБОЛЬШИЙ()

Или, ориентируясь на полученный заголовок -
Код
ГПР()

Автор - vikttur
Дата добавления - 27.01.2019 в 21:48
daledale Дата: Воскресенье, 27.01.2019, 21:55 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikktur
Спасибо за ответ.
1. Поясню. Нужно не просто максимум 1 найти. А последовательно N значений максимумов в порядке убывания.
2. И если я правильно понимаю: ПОИСКПОЗ(НАИБОЛЬШИЙ() - эта функция, в простом виде (!)при одинаковых значениях максимума в массиве выдаст только первое найденное значение столбца, в котором встретится максимум. Т.е. в моём конкретном случае для значения 32, нужно чтобы было и п2 и п9.
 
Ответить
Сообщениеvikktur
Спасибо за ответ.
1. Поясню. Нужно не просто максимум 1 найти. А последовательно N значений максимумов в порядке убывания.
2. И если я правильно понимаю: ПОИСКПОЗ(НАИБОЛЬШИЙ() - эта функция, в простом виде (!)при одинаковых значениях максимума в массиве выдаст только первое найденное значение столбца, в котором встретится максимум. Т.е. в моём конкретном случае для значения 32, нужно чтобы было и п2 и п9.

Автор - daledale
Дата добавления - 27.01.2019 в 21:55
vikttur Дата: Воскресенье, 27.01.2019, 21:57 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Пока дополнял, Вы успели прочитать неправильный совет :)
 
Ответить
СообщениеПока дополнял, Вы успели прочитать неправильный совет :)

Автор - vikttur
Дата добавления - 27.01.2019 в 21:57
Nic70y Дата: Воскресенье, 27.01.2019, 22:05 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
со 2-й без массивного ввода
Код
=ИНДЕКС($A$1:$N$1;ЕСЛИ(НАИБОЛЬШИЙ($A2:$N2;ОКРУГЛ(СТОЛБЕЦ(D1)/2;))=P2;ПОИСКПОЗ(R2;ИНДЕКС($A2:$N2;ПОДСТАВИТЬ(Q2;"п";)+1):$N2;)+ПОДСТАВИТЬ(Q2;"п";);ПОИСКПОЗ(R2;$A2:$N2;)))
К сообщению приложен файл: 0197697.xls (29.5 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениесо 2-й без массивного ввода
Код
=ИНДЕКС($A$1:$N$1;ЕСЛИ(НАИБОЛЬШИЙ($A2:$N2;ОКРУГЛ(СТОЛБЕЦ(D1)/2;))=P2;ПОИСКПОЗ(R2;ИНДЕКС($A2:$N2;ПОДСТАВИТЬ(Q2;"п";)+1):$N2;)+ПОДСТАВИТЬ(Q2;"п";);ПОИСКПОЗ(R2;$A2:$N2;)))

Автор - Nic70y
Дата добавления - 27.01.2019 в 22:05
daledale Дата: Воскресенье, 27.01.2019, 22:05 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Спасибо большое. Нюанс. Применил формулу, тяну вправо. Значения дублируются, т.е. получается п5 п7 п7 п14 п14 п1 п1 итд.


Сообщение отредактировал daledale - Воскресенье, 27.01.2019, 22:05
 
Ответить
Сообщениеvikttur
Спасибо большое. Нюанс. Применил формулу, тяну вправо. Значения дублируются, т.е. получается п5 п7 п7 п14 п14 п1 п1 итд.

Автор - daledale
Дата добавления - 27.01.2019 в 22:05
Nic70y Дата: Воскресенье, 27.01.2019, 22:07 | Сообщение № 7
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
тяну вправо
надо 2 ячейки тянуть


ЮMoney 41001841029809
 
Ответить
Сообщение
тяну вправо
надо 2 ячейки тянуть

Автор - Nic70y
Дата добавления - 27.01.2019 в 22:07
daledale Дата: Воскресенье, 27.01.2019, 22:11 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y
ОГРОМНОЕ ВАМ СПАСИБО! То, что надо!
 
Ответить
СообщениеNic70y
ОГРОМНОЕ ВАМ СПАСИБО! То, что надо!

Автор - daledale
Дата добавления - 27.01.2019 в 22:11
daledale Дата: Воскресенье, 27.01.2019, 22:16 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y
>надо 2 ячейки тянуть
Это я не вам, а вашему коллеге. У него одна формула только, показывающая только позиции и эти позиции задваивались.
В вашем случае, да понял что две.


Сообщение отредактировал daledale - Воскресенье, 27.01.2019, 22:17
 
Ответить
СообщениеNic70y
>надо 2 ячейки тянуть
Это я не вам, а вашему коллеге. У него одна формула только, показывающая только позиции и эти позиции задваивались.
В вашем случае, да понял что две.

Автор - daledale
Дата добавления - 27.01.2019 в 22:16
daledale Дата: Воскресенье, 27.01.2019, 22:24 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Прошу прощения. Да, понял логику. У меня же значения заголовков чередуются через столбец с непосредственными значениями максимумов. И да, при копировании и вставке через столбец, всё в порядке. Прошу прощения. Огромное Вам спасибо тоже за помощь!


Сообщение отредактировал daledale - Воскресенье, 27.01.2019, 22:27
 
Ответить
Сообщениеvikttur
Прошу прощения. Да, понял логику. У меня же значения заголовков чередуются через столбец с непосредственными значениями максимумов. И да, при копировании и вставке через столбец, всё в порядке. Прошу прощения. Огромное Вам спасибо тоже за помощь!

Автор - daledale
Дата добавления - 27.01.2019 в 22:24
daledale Дата: Воскресенье, 27.01.2019, 23:07 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Nic70y
Скопировал попарно, макс.значения находит ок, а вот с определением позиций возникли проблемы. см. влож файл.
К сообщению приложен файл: 0197697_err.xlsx (10.8 Kb)
 
Ответить
СообщениеNic70y
Скопировал попарно, макс.значения находит ок, а вот с определением позиций возникли проблемы. см. влож файл.

Автор - daledale
Дата добавления - 27.01.2019 в 23:07
daledale Дата: Воскресенье, 27.01.2019, 23:12 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Наибольшие по убыванию найти с помощью функции

НАИБОЛЬШИЙ()

Можно тоже вопрос, в связи с этим? Есть ли какая-то возможность, чтобы при копировании (у меня правда через столбец), чтобы последнее значение динамически менялось, ну т.е. формат функции такой: =Наибольший(A2:N2,k), т.е. чтобы это k - сначала было 1, потом при копировании 2 итд.
 
Ответить
Сообщениеvikttur
Наибольшие по убыванию найти с помощью функции

НАИБОЛЬШИЙ()

Можно тоже вопрос, в связи с этим? Есть ли какая-то возможность, чтобы при копировании (у меня правда через столбец), чтобы последнее значение динамически менялось, ну т.е. формат функции такой: =Наибольший(A2:N2,k), т.е. чтобы это k - сначала было 1, потом при копировании 2 итд.

Автор - daledale
Дата добавления - 27.01.2019 в 23:12
vikttur Дата: Воскресенье, 27.01.2019, 23:25 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Цитата
=Наибольший(A2:N2,k), т.е. чтобы это k - сначала было 1, потом при копировании 2 итд.

Я разве не это показал?
Код
...СТОЛБЕЦ(B1)/2...

Цитата
Это я не вам, а вашему коллеге. У него одна формула только,

Это формула для определения названия. Формула запысывается, естественно, через столбец. Число определять помощью НАИМЕНЬШИЙ или ГПР. Это все написано в сообщении, где формула.


Сообщение отредактировал vikttur - Воскресенье, 27.01.2019, 23:30
 
Ответить
Сообщение
Цитата
=Наибольший(A2:N2,k), т.е. чтобы это k - сначала было 1, потом при копировании 2 итд.

Я разве не это показал?
Код
...СТОЛБЕЦ(B1)/2...

Цитата
Это я не вам, а вашему коллеге. У него одна формула только,

Это формула для определения названия. Формула запысывается, естественно, через столбец. Число определять помощью НАИМЕНЬШИЙ или ГПР. Это все написано в сообщении, где формула.

Автор - vikttur
Дата добавления - 27.01.2019 в 23:25
daledale Дата: Воскресенье, 27.01.2019, 23:25 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Тоже нашёл проблему. В общем расширил массив, разумеется подправив формулу (диапазон). И при одном повторяющемся значении вышла ошибка, точнее неверно указывается позиция.
Во вложении файл. Само определение максимума я взял у вашего коллеги (формулу), а вот с определением позиции - проблема. Во вложении файл.
К сообщению приложен файл: found_max_err.xlsx (10.8 Kb)
 
Ответить
Сообщениеvikttur
Тоже нашёл проблему. В общем расширил массив, разумеется подправив формулу (диапазон). И при одном повторяющемся значении вышла ошибка, точнее неверно указывается позиция.
Во вложении файл. Само определение максимума я взял у вашего коллеги (формулу), а вот с определением позиции - проблема. Во вложении файл.

Автор - daledale
Дата добавления - 27.01.2019 в 23:25
vikttur Дата: Воскресенье, 27.01.2019, 23:49 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Из-за преобразования - отбрасывался правый ноль
Задать формат:
Код
=ИНДЕКС($A$1:$T$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($A$2:$T$2+СТОЛБЕЦ($A$2:$T$2)*0,001;СТОЛБЕЦ(B1)/2);"0,000");3))
К сообщению приложен файл: 2831472.xlsx (11.3 Kb)


Сообщение отредактировал vikttur - Воскресенье, 27.01.2019, 23:50
 
Ответить
СообщениеИз-за преобразования - отбрасывался правый ноль
Задать формат:
Код
=ИНДЕКС($A$1:$T$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($A$2:$T$2+СТОЛБЕЦ($A$2:$T$2)*0,001;СТОЛБЕЦ(B1)/2);"0,000");3))

Автор - vikttur
Дата добавления - 27.01.2019 в 23:49
daledale Дата: Воскресенье, 27.01.2019, 23:52 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Спасибо Вам большое. Завтра проверю, в любом случае отпишусь. Огромное спасибо ещё раз!
 
Ответить
Сообщениеvikttur
Спасибо Вам большое. Завтра проверю, в любом случае отпишусь. Огромное спасибо ещё раз!

Автор - daledale
Дата добавления - 27.01.2019 в 23:52
daledale Дата: Понедельник, 28.01.2019, 16:24 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur
Может я что-то не так делаю, но чет не работает. Поправка, правда, с моей стороны - в массиве могут быть 0.
В общем перенёс обе формулы, в рабочий лист, с изменением границ разумеется - (там массив несколько другой) - чет не работает.
Во вложении файл. Массив CU$:AAZ$
Значения нужно положить попарно в ячейки ABL+ABM и далее ABN+ABO итд. Формулы в ячейки ABL+ABM положил. Не работает.
Во вложении файл.
ps Массив будет расти вниз 2-3к+ строк, каждый раз все значения массива будут увеличиваться на 1 в случайном порядке.
К сообщению приложен файл: 2217119.xlsx (21.8 Kb)
 
Ответить
Сообщениеvikttur
Может я что-то не так делаю, но чет не работает. Поправка, правда, с моей стороны - в массиве могут быть 0.
В общем перенёс обе формулы, в рабочий лист, с изменением границ разумеется - (там массив несколько другой) - чет не работает.
Во вложении файл. Массив CU$:AAZ$
Значения нужно положить попарно в ячейки ABL+ABM и далее ABN+ABO итд. Формулы в ячейки ABL+ABM положил. Не работает.
Во вложении файл.
ps Массив будет расти вниз 2-3к+ строк, каждый раз все значения массива будут увеличиваться на 1 в случайном порядке.

Автор - daledale
Дата добавления - 28.01.2019 в 16:24
vikttur Дата: Понедельник, 28.01.2019, 23:06 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Три правых символа, котоые получаем функцией ПРАВБ (RIGHTB) - номер столбца.
Значит:
- или диапазон в ИНДЕКС (INDEX) начинать со столбца А;
- или от полученного номера отнимать номер столбца перед диапазоном с данными.

Если предполагается, что диапазон захватит столбцы после 999-го, в формуле нужно умножать на 0,0001


Сообщение отредактировал vikttur - Понедельник, 28.01.2019, 23:07
 
Ответить
СообщениеТри правых символа, котоые получаем функцией ПРАВБ (RIGHTB) - номер столбца.
Значит:
- или диапазон в ИНДЕКС (INDEX) начинать со столбца А;
- или от полученного номера отнимать номер столбца перед диапазоном с данными.

Если предполагается, что диапазон захватит столбцы после 999-го, в формуле нужно умножать на 0,0001

Автор - vikttur
Дата добавления - 28.01.2019 в 23:06
daledale Дата: Вторник, 29.01.2019, 10:02 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
vikttur

Спасибо за ответ.

Был массив A$:T$. Результаты вычислений писались начиная с V2, их позиции - начиная с W2.
Была ваша формула:
=ИНДЕКС($A$1:$T$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($A$2:$T$2+СТОЛБЕЦ($A$2:$T$2)*0,001;СТОЛБЕЦ(B1)/2);"0,000");3))
*********
Массив стал CU$:AAZ$. Результаты вычислений пишутся, начиная с ABL2, сами позиции - начиная с ABM2
Формулу изменил на:
=ИНДЕКС($CU$1:$AAZ$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($CU$2:$AAZ$2+СТОЛБЕЦ($CU$2:$AAZ$2)*0,001;СТОЛБЕЦ(CV1)/2);"0,000");3))
Не работает.
Что не так?


Добавлено:
Поигрался дополнительно. Все нули заменил на произвольное число >0. Ошибка исчезла. Насколько правильно работает посмотрю.


Сообщение отредактировал daledale - Вторник, 29.01.2019, 10:08
 
Ответить
Сообщениеvikttur

Спасибо за ответ.

Был массив A$:T$. Результаты вычислений писались начиная с V2, их позиции - начиная с W2.
Была ваша формула:
=ИНДЕКС($A$1:$T$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($A$2:$T$2+СТОЛБЕЦ($A$2:$T$2)*0,001;СТОЛБЕЦ(B1)/2);"0,000");3))
*********
Массив стал CU$:AAZ$. Результаты вычислений пишутся, начиная с ABL2, сами позиции - начиная с ABM2
Формулу изменил на:
=ИНДЕКС($CU$1:$AAZ$1;ПРАВБ(ТЕКСТ(НАИБОЛЬШИЙ($CU$2:$AAZ$2+СТОЛБЕЦ($CU$2:$AAZ$2)*0,001;СТОЛБЕЦ(CV1)/2);"0,000");3))
Не работает.
Что не так?


Добавлено:
Поигрался дополнительно. Все нули заменил на произвольное число >0. Ошибка исчезла. Насколько правильно работает посмотрю.

Автор - daledale
Дата добавления - 29.01.2019 в 10:02
daledale Дата: Вторник, 05.02.2019, 21:54 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
И всё таки не зашло, что-то. С вашего разрешения упрощу задачу. Условно массив из N строк. В 1-ой строке слова, причем не последовательные, а совершенно произвольные. В последующих, в столбце, соответствующим каждому слову числа, в хаотичном порядке, целые, положительные, не повторяющиеся. Нужно Создать новый массив из этих двух строк - сортируются числа по убыванию, но вместо чисел - ставятся слова-заголовки столбцов.... в общем проще показать на примере, см вложение.
К сообщению приложен файл: sort.xlsx (8.4 Kb)


Сообщение отредактировал daledale - Вторник, 05.02.2019, 22:06
 
Ответить
СообщениеИ всё таки не зашло, что-то. С вашего разрешения упрощу задачу. Условно массив из N строк. В 1-ой строке слова, причем не последовательные, а совершенно произвольные. В последующих, в столбце, соответствующим каждому слову числа, в хаотичном порядке, целые, положительные, не повторяющиеся. Нужно Создать новый массив из этих двух строк - сортируются числа по убыванию, но вместо чисел - ставятся слова-заголовки столбцов.... в общем проще показать на примере, см вложение.

Автор - daledale
Дата добавления - 05.02.2019 в 21:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нахождение N максимумов в массиве, в т.ч. повторяющихся (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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