Динамическая сортировка данных из другой таблицы

Дата: Среда, 11.01.2017, 14:21 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Здравствуйте!
Стоит задача создать таблицу с сортировкой данных из другой таблицы.

На одном листе есть таблица с перечнем компонентов и сколько раз он был заменён.
На другом листе будет таблица ТОП 10 компонентов, наиболее часто менявшихся (из первой таблицы).
Т.е. ищем максимальное значение замен в первой таблице, записываем его и соответствующий номер компонента. И так далее по убыванию.
По форуму и в инете поискал, есть что-то похожее, но то что надо не нашёл... и, опять же встаёт вопрос - как увязать число замен с номером компонента, ВПР тут не работает, т.к. искомое значение в правом столбце. Как-то чувствую нужно использовать ПОИСКПОЗ и ИНДЕКС, но пока не получается.

К сообщению приложен файл: Sorting.xlsx (15.2 Kb)

Дата: Среда, 11.01.2017, 14:29 | Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

так нужно? (массивка)

Код

=ИНДЕКС($A$3:$A$25;ПОИСКПОЗ(НАИБОЛЬШИЙ($C$3:$C$25+СТРОКА($C$3:$C$25)%%%;СТРОКА(A1));$C$3:$C$25+СТРОКА($C$3:$C$25)%%%;0))

К сообщению приложен файл: 7655748.xlsx (16.7 Kb)

Сообщение отредактировал buchlotnik - Среда, 11.01.2017, 14:29

китин

Дата: Среда, 11.01.2017, 14:30 | Сообщение № 3

Группа: Модераторы

Ранг: Экселист

Сообщений: 7035

Репутация: 1079 ±

Замечаний: 0% ±

Excel 2007;2010;2016

так?

Код

=ИНДЕКС(A$3:A$25;ПОИСКПОЗ(НАИБОЛЬШИЙ($C$3:$C$25;СТРОКА(A1));$C$3:$C$25;0))

[p.s.]у Михаила лучше

учитывает возможность повторов

К сообщению приложен файл: 333222.xlsx (16.1 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Среда, 11.01.2017, 14:31

Дата: Среда, 11.01.2017, 14:32 | Сообщение № 4

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

китин, Игорь, посмотри в своём файле на две последние строчки - при совпадении числа замен не пашет

UPD - сам заметил

Сообщение отредактировал buchlotnik - Среда, 11.01.2017, 14:33

китин

Дата: Среда, 11.01.2017, 14:32 | Сообщение № 5

Группа: Модераторы

Ранг: Экселист

Сообщений: 7035

Репутация: 1079 ±

Замечаний: 0% ±

Excel 2007;2010;2016

уже дописАл в своем посте

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Дата: Среда, 11.01.2017, 15:26 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Да, круто! То-что надо! Ох уж эти ИНДЕКС и ПОИСКПОЗ... вот вроде принцип ясен, но на деле применить их не получается. Пытался не глядя воспроизвести... какой-то момент ускользает.
Поясните, зачем прибавлять +СТРОКА( )%%%? Что за проценты?

И не пойму, в чём принципиальная разница между формулой Игоря и Buchlotnik... почему там с повторами сбивается а тут нет... Сорри, просто охота понять её на будущее, но вконец запутался.

PS, это даже удобнее обычной сортировки (!), изменив значение СТРОКА (А1) на А3 удалось отсеять ненужную расходку, которая меняется "пачками" попадает в перечень замен, но никакого интереса не представляет ))) раньше вручную удалял их

Сообщение отредактировал rvp9321 - Среда, 11.01.2017, 15:46

Дата: Среда, 11.01.2017, 15:41 | Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Цитата

зачем прибавлять +СТРОКА( )%%%

смотрите - у вас два компонента и оба заменялись 4 раза. Функция НАИБОЛЬШИЙ ставит и на 9 и на 10 позицию значение 4 - а как ПОИСКПОЗ отличить 4 от 4, т.е. сетевой фильтр от редуктора? правильно, никак. поэтому мы добавляем к числу замен очень маленькое число СТРОКА( )%%% - т.е. одна миллионная номера строки - и это уже не 4 и 4, а 4,000024 и 4,000025, такие значения уже можно различить

Дата: Среда, 11.01.2017, 16:43 | Сообщение № 8

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Цитата buchlotnik, 11.01.2017 в 15:41, в сообщении № 7 ()

добавляем к числу замен очень маленькое число СТРОКА( )%%% - т.е. одна миллионная номера строки

т.е. проценты это как бы двигаем запятую влево? А без массива оно так не работает, поэтому Ctrl-Sh-Ent... ну вроде разобрался.
По ходу ещё вопрос. Как сказал выше, в первые строки попадают 2-3 расходника. Чтоб глаза не мозолили и места не занимали, я их исключаю. Сначала просто изменив СТРОКА (А1) на А3 в формулах, но за разный период бывает необходимо то 2 строки исключить, то 3, иногда - 1. Чтобы каждый раз не править формулы, хочу вынести отдельно ячейку, где вводить сколько строчек исключить.
Попробовал поменять вместо СТРОКА(А1) сделать СТРОКА(АДРЕС(M1;1;4;1)) - но не получается...

К сообщению приложен файл: 8795438.xlsx (17.1 Kb)

Дата: Среда, 11.01.2017, 16:50 | Сообщение № 9

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Цитата

это как бы двигаем запятую влево?

можно и так сказать, вообще это просто взятие процента (т.е. одной сотой)

Код

СТРОКА(АДРЕС(M1;1;4;1))

зачем так сложно? достаточно

Код

СТРОКА(A1)+$M$1

К сообщению приложен файл: 9056925.xlsx (17.3 Kb)

Сообщение отредактировал buchlotnik - Среда, 11.01.2017, 16:55

Дата: Четверг, 12.01.2017, 10:12 | Сообщение № 10

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Цитата buchlotnik, 11.01.2017 в 16:50, в сообщении № 9 ()

СТРОКА(A1)+$M$1

Мда... и правда так гораздо проще

Спасибо!

lenakireenko

Дата: Воскресенье, 24.03.2019, 22:03 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 10

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Добрый вечер. А не мог бы кто-то из вас помочь мне с формулой. Читала эту ветку, пробовала в своем случае - и ничего не вышло(((
В таблице эксель использую данные из разных листов. В первый лист вношу данные покупателя, по мере поступления новых покупателей, так сказать. Второй лист для своих целей я сделала и там все ок. А вот третий лист хочу сделать сортировку по мере поступления данных о дате рождения, которые берутся из первого листа. Дата рождения и ФИО и номер телефона появляются в этом листе автоматически с первого листа. А вот как забить формулу, чтобы даты рождения шли по календарному году автоматически - никак не пойму. Пока таблица пустая и там как пример Иванов Иван Иванович. Когда появятся данные о других покупателях, надо, чтобы в третьем листе они автоматом ранжировались исключительно от января к декабрю. Надеюсь, понятно изложила. И что-то не смогла приложить свой файл. В формате xls весит 200 Kb, а в xlsx нельзя, но весит все равно 105 Kb.

_Boroda_

Дата: Воскресенье, 24.03.2019, 22:12 | Сообщение № 12

Группа: Админы

Ранг: Местный житель

Сообщений: 16913

Репутация: 6617 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Прочитайте Правила форума, создайте свою тему, приложите файл

Цитата lenakireenko, 24.03.2019 в 22:03, в сообщении № 11 ()

в xlsx нельзя,

почему? Есть еще xlsb.
Весь файл не нужен, небольшой пример создайте
Эта тема закрыта

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995