Страницы: 1 2 3 След.
RSS
Выборка данных из масива по условиям
 
Добрый день!

Снова к вам обращаюсь, знатоки экселя. Есть файл на одном листе таблица (во вложении) где указан контрагент, договор, дата. Можно ли сделать через формулу массива или как то по другому, чтобы из всего это массива на другом листе выбирался номер договора на определенную дату (даты иногда повторяются), т.е., например, мне нужна таблица где в строках будут указаны все договоры заключенные 21.01.2018 и 30.01.2018. Сначала заполняются строки с договорами от 21.01.2018, затем после того как они закончились без пропуска строк заполняется номер договора заключенный 30.01.2018 (очередность не принципиально). Если мне нужна одна дата то я смог через массив написать формулу: {=ЕСЛИОШИБКА(ИНДЕКС(C5647:C5655;НАИМЕНЬШИЙ(ЕСЛИ($M$5658=I5647:I5655;СТРОКА(C5647:C5655)-5646;"");СТРОКА()-5659));"")}, а когда появляется вторая и последующие даты - у меня проблема, третий день мучаюсь и никак не выходит каменный цветок :(
ЗЫ Может, конечно, формулой такого и не создать, а только макросом :)
Изменено: zhan1981 - 06.02.2018 11:39:40
 
zhan1981, а может сделать автофильтром? вот типо такого http://www.planetaexcel.ru/techniques/2/197/
Изменено: a.i.mershik - 06.02.2018 12:10:14
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik, спасибо! К сожалению, предполагаю что не подойдет, т.к. я укороченную версию написал. ,а так нужно чтобы из таблицы на листе 1, по дате выцыплялись на другой лист в таблицу с меньшим количеством заголовков. Может и в Вашем примере можно такое реализовать, прочитал бегло и кажется что не получится как я хочу.
 
Не подходит, т.к. результирующая таблица с другой очередностью столбцов и данные для результирующей таблицы берутся не из всех столбцов :(
 
zhan1981, в ваше примере вообще всего одна таблица...покажите что есть и что хотите получить ...тогда будет понятнее
Не бойтесь совершенства. Вам его не достичь.
 
м.б. так? ("Сводная таблица")
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
a.i.mershik, на Листе 2 таблица которая нужна, в рабочей таблице столбцов гораздо больше, а в результирующей (лист 2 ) меньше по сравнению с рабочей.
 
А можно в эту формулу ({=ЕСЛИОШИБКА(ИНДЕКС(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 строк для сводной - это "плёвое дело". Формулы будут на порядок медленнее работать.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Михаил Лебедев, 20 К строк как аргумент, что нереально переделать для работы сводной таблицы
 
Цитата
zhan1981 написал:
20 К строк как аргумент,
Извините, но непонятный аргумент. Я бы даже сказал, что спорный...
Изменено: Михаил Лебедев - 07.02.2018 09:08:13
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Михаил Лебедев, аргумент в том что по этим 20К строкам надо идти и вручную приводить в порядок, чтобы сводная заработала
 
zhan1981, как вариант таким способом  с доп столбцами и макросом.
Изменено: a.i.mershik - 07.02.2018 11:24:55
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik, Спасибо!  Офигенно, конечно!
Если мне нужно добавить кол-во столбцов не пойму где в макросе надо внести изменения.  :cry:  
 
zhan1981, количество столбцов чего!? макрос только переносит данные под датами в таблицу и все...
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik, например, нужно будет добавить аналитику. Выделил желтым.
 
zhan1981,
Код
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 - 07.02.2018 12:26:22
Не бойтесь совершенства. Вам его не достичь.
 
a.i.mershik, Спасибо! Пойду экспериментировать ))
 
a.i.mershik, добрый день!
В ходе эксперимента нашлась траблина, а именно, если указан период например 15.01.2018-19.01.2019 и формула, просматривая весь массив натыкается на даты ранее 15-го то дополнительная таблица не выдает уникальные даты :(. Вложил файл для примера. т.е. есть перечень контрагентов и с одним из них заключили договор в 2017 году, а 16.01.2018 заключили допик к этому договору или новый договор (не важно, т.к. что допик что новый договор будет укзана строкой ниже по блоку строк данного контрагента) и соответственно отчет по заключенным договорам нужен за период 15.01.2018-19.01.2019, но просматривается весь массив с 2000 года.
Изменено: zhan1981 - 09.02.2018 08:52:10
 
Может так подойдет.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, спасибо. к сожалению, результирующая таблица имеет другое количество столбцов, т.е. если исходная 30 то в результирующей всего 12
 
В примере нет того как должно быть. Да и количество столбцов тоже вроде как не 30.
Если в примере показать структуру таблицы кот отличается от реальной , то в результате получите нерабочий код на оригинале.
Как-то так
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, да, к сожалению, Вы правы. Предполагал, что код по упрощенному примеру скорректирую на свой. Спасибо!
 
Так корректируйте, укажите ограничения по столбцам и строкам, сформируйте нужный массив и переносите в него только те столбцы кот необходимы. В итоге выгружайте на лист готовый (отфильтрованный) массив.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, да и вариант от a.i.mershik норм работает пока не столкнулся с тем, что в установленный период попадает дата более ранняя. В этом главный затык произошел.
 
На больших объемах  при таких массивных формулах файлы часто подвисают. Макрос же отрабатывает мгновенно! И плюс не нужно ничего лишнего на листе формировать.
Все даты в виде фильтра на форме. Выбираете нужные даты и вуаля на соседнем листе итоговый результат!  :D
А впрочем Хозяин-Барин, что привычней и удобней те и пользуется. Успехов!!! :D
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, такой вопрос. Можно в Вашем файле сделать чтобы, например, из таблицы в 10 столбцов, после выбора дат в фильтре данные попадали из столбца 1,5,2,7,4? Или совсем конкретно по файлу из примера. исходная таблица 4 столбца, после выбора фильтра данные попадали в результирующую таблицу только  из столбцов 2,3 (3,2 - нарушена очередность)?
 
Цитата
zhan1981 написал:
из столбцов 2,3 (3,2 - нарушена очередность)?
Эту фразу не понял, нужно 3-2 или 2-3?
"Все гениальное просто, а все простое гениально!!!"
Страницы: 1 2 3 След.
Наверх