Формирование диапазона в порядке возрастания

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формирование диапазона в порядке возрастания (Формулы/Formulas)

Nik57

Дата: Пятница, 06.04.2018, 16:30 | Сообщение № 1

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

Ранг: Участник

Сообщений: 96

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

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

Excel 2007

Добрый день, друзья!

Подскажите решить дилемму)) На листе 1, приложенного примера, есть условный диапазон А1:Н6. На листе 2 примера, я формирую этот же диапазон в порядке убывания по дате. Все работает, только если в выборке встречается дублирование даты, то в моем диапазоне повторяются дважды одни и те же значения (выделенный красный диапазон В4:Н5). А должно получиться, чтобы либо в строке В4:Н4, либо в строке В5:Н5 прописались вторые значения из выборки Листа1 (помидор;8;1;6;1сорт;6;8 соотвественно). Как подкорректировать формулы для данной ситуации, что то не додумаюсь для этого.
Всем заранее спасибо!

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

Ответить

Светлый

Дата: Пятница, 06.04.2018, 18:47 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Добрый день!
В B4 формула массива и протянуть вниз и вправо:

Код

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$B:$H;ОСТАТ(НАИБОЛЬШИЙ(Лист1!$A$3:$A$6+СТРОКА($3:$6)%;СТРОКА(A1));1)/1%;СТОЛБЕЦ(A1));"")

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

Ответить

Nik57

Дата: Пятница, 27.04.2018, 15:13 | Сообщение № 3

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

Ранг: Участник

Сообщений: 96

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

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

Excel 2007

Все работает, только одна проблема. Если на листе1 происходит динамические действия и число выборки увеличивается (диапазон А7:Н7), то на втором листе вот эта запись в формуле
Лист1!$A$3:$A$6+СТРОКА($3:$6)
требует ручной корректировки. Технически это проблематично из-за объемов. В таком виде, к примеру, формула не работает Лист1!$A$:$A$+СТРОКА($3:$500)
Как ее поправить?

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

Ответить

Светлый

Дата: Пятница, 27.04.2018, 16:10 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Подкорректировал:

Код

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$B:$H;ОСТАТ(НАИБОЛЬШИЙ(Лист1!$A$3:$A$500+СТРОКА(Лист1!$3:$500)%%;СТРОКА(A1));1)/1%%;СТОЛБЕЦ(A1));"")

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

Ответить

Nik57

Дата: Понедельник, 28.05.2018, 16:09 | Сообщение № 5

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

Ранг: Участник

Сообщений: 96

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

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

Excel 2007

Светлый, тут еще одна делемка.. а можно скорректировать так, чтобы вместо нулей "выдавались" пустые ячейке в диапазоне В9:Н14 второго листа?

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

Ответить

_Boroda_

Дата: Понедельник, 28.05.2018, 17:58 | Сообщение № 6

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

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Немного изменил формулу Сергея и добавил условие для пустых

Код

=ЕСЛИ($A4="";"";ИНДЕКС(Лист1!B:B;ОСТАТ(НАИБОЛЬШИЙ(Лист1!$A$3:$A$500+СТРОКА(Лист1!$3:$500)%%;СТРОКА(B1));1)/1%%))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

*Добавлено
А если даты по порядку, то

Код

=ЕСЛИ($A4="";"";ИНДЕКС(Лист1!B:B;ПОИСКПОЗ($A4;Лист1!$A$1:$A$999;)+СЧЁТЕСЛИ($A$3:$A3;$A4)))

К сообщению приложен файл: 4159234_1.xlsx (15.0 Kb) · 4159234_2.xlsx (14.5 Kb)

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