Снова к вам обращаюсь, знатоки экселя. Есть файл на одном листе таблица (во вложении) где указан контрагент, договор, дата. Можно ли сделать через формулу массива или как то по другому, чтобы из всего это массива на другом листе выбирался номер договора на определенную дату (даты иногда повторяются), т.е., например, мне нужна таблица где в строках будут указаны все договоры заключенные 21.01.2018 и 30.01.2018. Сначала заполняются строки с договорами от 21.01.2018, затем после того как они закончились без пропуска строк заполняется номер договора заключенный 30.01.2018 (очередность не принципиально). Если мне нужна одна дата то я смог через массив написать формулу: {=ЕСЛИОШИБКА(ИНДЕКС(C5647:C5655;НАИМЕНЬШИЙ(ЕСЛИ($M$5658=I5647:I5655;СТРОКА(C5647:C5655)-5646;"");СТРОКА()-5659));"")}, а когда появляется вторая и последующие даты - у меня проблема, третий день мучаюсь и никак не выходит каменный цветок ЗЫ Может, конечно, формулой такого и не создать, а только макросом
a.i.mershik, спасибо! К сожалению, предполагаю что не подойдет, т.к. я укороченную версию написал. ,а так нужно чтобы из таблицы на листе 1, по дате выцыплялись на другой лист в таблицу с меньшим количеством заголовков. Может и в Вашем примере можно такое реализовать, прочитал бегло и кажется что не получится как я хочу.
А можно в эту формулу ({=ЕСЛИОШИБКА(ИНДЕКС(C5647:C5655;НАИМЕНЬШИЙ(ЕСЛИ($M$5658=I5647:I5655;СТРОКА(C5647:C5655)-5646;"");СТРОКА()-5659));"")}) массива поставить условие,вот это ЕСЛИ($M$5658=I5647:I5655 заменить на ЕСЛИ(I5647:I5655>21.01.2018)?
P.S. Замена в формуле работает, как ограничить, чтобы не все договоры с 21.01.2018 а, заключенные с 21.01.2018 до 01.02.2018?
Михаил Лебедев, вспомнил. там таблица кривая для сводной ошибка: "Недопустимое имя сводной таблицы и т.д.") т.к. таблица почти в 20 000 строк то переделывать анриал. Понятно если бы с нуля вел то подвел бы к сводной таблице. Все равно спасибо за идею и помощь
zhan1981 написал: ошибка: "Недопустимое имя сводной таблицы и т.д.") т.к. таблица почти в 20 000 строк то переделывать анриал
Недопустимое имя - это скорее всего шапка исх. таблицы - многоуровневая и с объединенными ячейками. В этом случае Сводная не будет работать, т.к. появляются в шапке пустые ячейки. А 20000 строк для сводной - это "плёвое дело". Формулы будут на порядок медленнее работать.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
Option Explicit
Sub tt()
Dim lr&, i&, j&, k&, r As Range, c As Range
With Sheets(1)
lr = .Cells(.Rows.Count, "I").End(xlUp).Row ' название первого столбца с которой начинается вспомогательная таблица
Set r = .Range("I4:AG" & lr) ' диапозон данных которые указываются под датами (без учета дат) вспомогательной таблицы
.[F17:F100].ClearContents 'диапозон куда будут вставлятся данные из вспомогательной таблицы
For j = 1 To 100
For i = 1 To lr - [I4].Row 'первая ячейка массива данных из кооторого будут копироватся значения
If r(i, j) <> "" Then .[F17].Offset(k) = r(i, j): k = k + 1 'первая ячейка начала диапозона в кооторый будут копироватся
Next
Next
End With
End Sub
a.i.mershik, добрый день! В ходе эксперимента нашлась траблина, а именно, если указан период например 15.01.2018-19.01.2019 и формула, просматривая весь массив натыкается на даты ранее 15-го то дополнительная таблица не выдает уникальные даты . Вложил файл для примера. т.е. есть перечень контрагентов и с одним из них заключили договор в 2017 году, а 16.01.2018 заключили допик к этому договору или новый договор (не важно, т.к. что допик что новый договор будет укзана строкой ниже по блоку строк данного контрагента) и соответственно отчет по заключенным договорам нужен за период 15.01.2018-19.01.2019, но просматривается весь массив с 2000 года.
В примере нет того как должно быть. Да и количество столбцов тоже вроде как не 30. Если в примере показать структуру таблицы кот отличается от реальной , то в результате получите нерабочий код на оригинале. Как-то так
"Все гениальное просто, а все простое гениально!!!"
Так корректируйте, укажите ограничения по столбцам и строкам, сформируйте нужный массив и переносите в него только те столбцы кот необходимы. В итоге выгружайте на лист готовый (отфильтрованный) массив.
"Все гениальное просто, а все простое гениально!!!"
Nordheim, да и вариант от a.i.mershik норм работает пока не столкнулся с тем, что в установленный период попадает дата более ранняя. В этом главный затык произошел.
На больших объемах при таких массивных формулах файлы часто подвисают. Макрос же отрабатывает мгновенно! И плюс не нужно ничего лишнего на листе формировать. Все даты в виде фильтра на форме. Выбираете нужные даты и вуаля на соседнем листе итоговый результат! А впрочем Хозяин-Барин, что привычней и удобней те и пользуется. Успехов!!!
"Все гениальное просто, а все простое гениально!!!"
Nordheim, такой вопрос. Можно в Вашем файле сделать чтобы, например, из таблицы в 10 столбцов, после выбора дат в фильтре данные попадали из столбца 1,5,2,7,4? Или совсем конкретно по файлу из примера. исходная таблица 4 столбца, после выбора фильтра данные попадали в результирующую таблицу только из столбцов 2,3 (3,2 - нарушена очередность)?