ВПР или макрос?

Уважаемые, добрый день!

25.05.2013	A	22334
25.05.2013	E	232345
25.05.2013	H	454545
25.05.2013	N	676776
25.05.2013	P	89797
25.05.2013	S	34423
25.05.2013	K	5656
25.05.2013	L	77687
25.05.2013	Z	7868
25.05.2013	М	89898
26.05.2013	A	768768
26.05.2013	E	23423423
26.05.2013	H	90890
26.05.2013	N	879789
26.05.2013	P	655656
26.05.2013	S	53453
26.05.2013	R	36575
26.05.2013	K	787688
26.05.2013	L	34535

Имеется данная табличка. Мне необходимо копировать содержимое строк по порядку, если в первой ячейке строки имеется дата например равная 25.05.2013. Т.е. на чистом листе мы получим:

A	22334
E	232345
H	454545
N	676776
P	89797
S	34423
K	5656
L	77687
Z	7868
М	89898

Вся загвозка в том, что диапазон таблицы должен обязательно быть фиксированным.

=ВПР(Дата$;Диапазон$;2;0) =ВПР(Дата$;Диапазон$;3;0)

Но так он будет вприть только первую строку, и если тянуть таблицу на чистом листе вниз сами понимаете получим снова первую строку в каждой следующей. А мне нужно содержимое всех строк с этой датой. И чего скрывать, затем нужно такое же содержимое с другой датой.

Как быть? Спасибо.

0

Где-то была UDF ВПР2() - там можно указать каое по счёту совпадение нужно брать.
Но быстрее конечно будет макрос, фильтрующий данные.
А вообще, чем фильтр не нравится?

0

Потестируй

Вложения

Копирование дат2.rar (23.1 Кб, 37 просмотров)

0

формула массива:

выделить диапазон, в котором будут ответы (сразу нужное количество ячеек в столбце, можно с запасом), ввести эту формулу, нажать Ctrl + Shift + Enter

в формуле $L$2:$L$17 - диапазон со значениями, которые нужно выбирать; K2:K17 - диапазон с условием (у вас это первый столбец); $O$2 - ячейка со значением, которое нужно искать в диапазоне с условием
здесь СТРОКА()-4 -4 означает что диапазон с результатом находится на 4 строки ниже, чем диапазон для поиска условий.

=ЕСЛИОШИБКА(ИНДЕКС($L$2:$L$17;НАИМЕНЬШИЙ (ЕСЛИ($O$2=K2:K17;СТРОКА(L2:L17)-1;"");СТРОКА()-4));"")

0

Привет всем!! Огромное спасибо за ответы. Я бы хотел прояснить ситуацию как можно конкретнее. Поэтому я подготовил точный пример. Во вложении будет файлик, с которым можно будет по экспериментировать.
Вложение 273777

А сейчас я расскажу механику отчета над которым решается вопрос ВПР или Макрос:

1. Имеется некая сводная табличка. Она выгружается автоматически в фаил экселя на первый лист.

Вложение 273776

В диапазоне столбцов =F:H находится необходимая нам табличка (=F14:H28) с данными.
Не мало важно сказать, что данные в сводной таблице, каждый день выгружаются с совершенно разным набором строк.
Т.е. Строк в каждой таблице может добавиться/уменьшится на 2-3, а вот в столбцах таблицы останутся на своих местах. Пример:

Вложение 273774

или

Вложение 273773

Как видите перемещается наша таблица (=F14:H28) построчно, но остается в своем диапазоне столбцов =F:H


2. Далее как уже необходимо по заданию. Мы должны вытащить из нашей таблички (=F14:H28) Brend и DISC на необходимые нам даты на следующий лист.

Вложение 273775

слева 21 справа 22.

Главное помнить, что с каждой выгрузкой сводной таблицы, наша табличка перемещается по строчкам, а также может менять их количество.

Надеюсь описал процесс понятно Спасибо!

P.s. макрос форумчанина показался мне сложным, не могли бы вы прокомментировать код?

0

Вложения то не загружаются.

1

Сообщение от toiai Вложения то не загружаются.

Спасибо! Исправил. Пробуйте снова.

0

Увы не получается. Прикрепите файлы к сообщению с теми же именами.

0

Какие-то проблемы на форуме, теперь это не возможно.









ВПР или Макрос..xlsx

0

И еще раз)). Не грузились вложения. Я бы хотел прояснить ситуацию как можно конкретнее. Поэтому я подготовил точный пример. Во вложении будет файлик, с которым можно будет по экспериментировать.


А сейчас я расскажу механику отчета над которым решается вопрос ВПР или Макрос:

1. Имеется некая сводная табличка. Она выгружается автоматически в фаил экселя на первый лист.

Вложение 273792

В диапазоне столбцов =F:H находится необходимая нам табличка (=F14:H28) с данными.
Не мало важно сказать, что данные в сводной таблице, каждый день выгружаются с совершенно разным набором строк.
Т.е. Строк в каждой таблице может добавиться/уменьшится на 2-3, а вот в столбцах таблицы останутся на своих местах. Пример:

Вложение 273790

или

Вложение 273789

Как видите перемещается наша таблица (=F14:H28) построчно, но остается в своем диапазоне столбцов =F:H


2. Далее как уже необходимо по заданию. Мы должны вытащить из нашей таблички (=F14:H28) Brend и DISC на необходимые нам даты на следующий лист.

Вложение 273793

слева 21 справа 22.

Главное помнить, что с каждой выгрузкой сводной таблицы, наша табличка перемещается по строчкам, а также может менять их количество.

Надеюсь описал процесс понятно Спасибо!

P.s. макрос форумчанина показался мне сложным, не могли бы вы прокомментировать код?

Вложение 273791

0

1) На ваш вопрос - ВПР или макрос, мой ответ - макрос.
2) вы показали на вашем примере в файле Excel, что данные копируются с листа Сводная на лист "Выборка" - 1-я дата в столбец В, 2-я дата в столбец М, а их всего 2 даты будет? или дат может быть 50 и копировать их так же нужно через 9 столбцов друг от друга?

0

Если немного изменить структуру исходных данных (см. лист "Данные" здесь использован динамический диапазон - см Диспетчер имен -Data), то можно обойтись сводной таблицей (лист "Свод данных" )

Вложения

ВПР или Макрос..xlsx (18.9 Кб, 44 просмотров)

0

вот вариант с формулой массива - на листе выборка проставить нужные даты, в исходном листе данные могут находится в диапазоне со 2-й строки до 50-й (либо увеличить диапазон в формуле)

Вложения

ВПР или Макрос..xlsx (13.5 Кб, 43 просмотров)

0

Сообщение от Pavel55 1) На ваш вопрос - ВПР или макрос, мой ответ - макрос. 2) вы показали на вашем примере в файле Excel, что данные копируются с листа Сводная на лист "Выборка" - 1-я дата в столбец В, 2-я дата в столбец М, а их всего 2 даты будет? или дат может быть 50 и копировать их так же нужно через 9 столбцов друг от друга?

Только 2 даты. И диапазон из 3 столбцов, где содержится таблица и перемещается по строкам, изменяться не будет.

Добавлено через 10 минут

Сообщение от toiai Если немного изменить структуру исходных данных (см. лист "Данные" здесь использован динамический диапазон - см Диспетчер имен -Data), то можно обойтись сводной таблицей (лист "Свод данных" )

К сожалению в том и сложность задачи, что структуру не нужно менять. А запись данных по датам на другом листке должна происходить в простые таблицы, потому что в будущем они будут тоже использованы для впр, т.е. важно чтобы их диапазоны не пересекались.

Добавлено через 3 минуты

Сообщение от mcherry вот вариант с формулой массива - на листе выборка проставить нужные даты, в исходном листе данные могут находится в диапазоне со 2-й строки до 50-й (либо увеличить диапазон в формуле)

=ЕСЛИОШИБКА(ИНДЕКС(Сводная!G2:G50;НАИМЕН ЬШИЙ(ЕСЛИ($B$1=Сводная!F2:F50;СТРОКА(Сво дная!G2:G50)-1;"");СТРОКА()-2));"")

Не могли бы более подробно разъяснить, данную формулу, я новичек в Excel. c ЕСЛИОШИБКА все понятно. Хотелось бы понимать, что от для чего и откуда. Спасибо! Первый ваш пост прочитал, есть что добавить?

Добавлено через 3 минуты

Сообщение от Pavel55 Потестируй

Очень понравился макрос, не распишите содержимое? переменные мне показались замудреными

0

Вот переделал макрос и расписал смысл каждой строчки кода.

Вложения

ВПР или Макрос.rar (23.5 Кб, 116 просмотров)

0

Сообщение от Pavel55 Вот переделал макрос и расписал смысл каждой строчки кода.

Большое спасибо очень наглядно. Мне в самом начале и показалось, что без массивов данных и записи в них не обойдется. Сможете также прокомментировать формулу:

=ЕСЛИОШИБКА(ИНДЕКС(Сводная!G2:G50;НАИМЕН ЬШИЙ(ЕСЛИ($B$1=Сводная!F2:F50;СТРОКА(Сво дная!G2:G50)-1;"");СТРОКА()-2));"")

Почему вы не выбрали ВПР?

0

по формулам лучше обрататься к автору формулы.

P.S. По мне лучше макрос, чем формула, допустим у вас будет 10.000 значений. Макрос очень быстро скопирует данные на другой лист, а формула .... она должна постоянно быть записана в ячейках листа, она постоянно будет пересчитываться и "подвешивать" файл, её нужно постоянно контролировать - до какой строки она протянута (вдруг данных больше, чем протянута формула) и т.д. Макрос написал один раз и забыл. Нажал Alt+F8, запустил макрос - результат готов и ничего не "подвешивает" файл.

0

ВПР не выбрала, т.к. он по умолчанию берет только первое найденное значение.
=ЕСЛИОШИБКА(ИНДЕКС(Сводная!G2:G50;НАИМЕН ЬШИЙ(ЕСЛИ($B$1=Сводная!F2:F50;СТРОКА(Сво дная!G2:G50)-1;"");СТРОКА()-2));"")

ИНДЕКС - возвращает значение из исходной таблицы, которое находится на пересечении указанной строки и стобца (столбец у нас один, поэтому мы его дополнительно не указываем), здесь Сводная!G2:G50 - исходная таблица, из которой нам нужно значение;
НАИМЕНЬШИЙ(ЕСЛИ($B$1=Сводная!F2:F50;СТРО КА(Сводная!G2:G50)-1;"");СТРОКА()-2)- возвращает наименьший номер строки, в которой выполняется наше условие:
ЕСЛИ() - проверка нашего условия,
СТРОКА(Сводная!G2:G50)-1 -вернет номер строки (в выбранном диапазоне) в которой это условие выполнилось (все варианты) (минус 1, т.к. 1 первую строку занимает заголовок - чтобы первая строка выбранного диапазона обозначилась как 1-я строка);
СТРОКА()-2 - этот параметр в указывает какой по порядку наименьший номер строки нам нужен (-2, т.к. 2 первые строки листа занимает заголовок)

поскольку это формула массива, то получается что для первой строки, в которой должен быть результат, выводится значение для первого выполнения условия, для второй строки - второе выполнение условия и т.д.

0

Большое спасибо ребята, тему можно закрывать. Оба совета научился и буду использовать

0