Добрый день! У меня следующая проблема, которую ни как не могу решить: имеется некий долгосрочный план работ нескольких сотрудников на нескольких листах. Необходимо проводить выгрузку информации из всех листов (их 5-10) на новый лист с фильтрацией по фамилии сотрудника и промежутку работ (в течении месяца). Предполагаю, что это можно сделать через макросы, но я в этом не разбираюсь совсем. Спасибо
Добрый день! У меня следующая проблема, которую ни как не могу решить: имеется некий долгосрочный план работ нескольких сотрудников на нескольких листах. Необходимо проводить выгрузку информации из всех листов (их 5-10) на новый лист с фильтрацией по фамилии сотрудника и промежутку работ (в течении месяца). Предполагаю, что это можно сделать через макросы, но я в этом не разбираюсь совсем. Спасибоlyksor25
[offtop]Я, конечно, дико извиняюсь, а можно вопрос: многолистовая структура книги (да еще и с переменным количеством листов, как я понял) - это "священная корова"? Просто так достали на работе эти 100-листовые книги, доставшиеся в "наследство", что как красная тряпка просто ...
Плоская структура, "умные" и сводные таблицы - рулят ...
А так, макросами решается на 100%, формулами - мэйби, но не уверен
Сорри за оффтоп
[offtop]Я, конечно, дико извиняюсь, а можно вопрос: многолистовая структура книги (да еще и с переменным количеством листов, как я понял) - это "священная корова"? Просто так достали на работе эти 100-листовые книги, доставшиеся в "наследство", что как красная тряпка просто ...
Плоская структура, "умные" и сводные таблицы - рулят ...
А так, макросами решается на 100%, формулами - мэйби, но не уверен
lyksor25, тут важный нюанс - если Вас устроит вариант, допустим, для трех листов/разделов, то это можно на формулах ... но тогда при добавлении раздела придется кое-что править ... ... если же для произвольного количества листов, с возможностью их добавления, писать формулы - это уже другого уровня задача ... лично для меня - никак не вариант для "доброго совета" в паузе от рабочего процесса ... если только наиболее опытные экселисты справятся с ней без труда (на формулах) ... и такую задачу, конечно, целесообразнее решать макросами ... ... попробуйте определиться
lyksor25, тут важный нюанс - если Вас устроит вариант, допустим, для трех листов/разделов, то это можно на формулах ... но тогда при добавлении раздела придется кое-что править ... ... если же для произвольного количества листов, с возможностью их добавления, писать формулы - это уже другого уровня задача ... лично для меня - никак не вариант для "доброго совета" в паузе от рабочего процесса ... если только наиболее опытные экселисты справятся с ней без труда (на формулах) ... и такую задачу, конечно, целесообразнее решать макросами ... ... попробуйте определитьсяabtextime
lyksor25, добрый день,вот макрос с выгрузкой данных на лист Лист1(его надо добавить в книгу) ,-в файл примере,кнопка yyy на листе 1(надо сжать, загружу чуть позже),вызов макроса с Вашего листа 1.
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets("Лист1").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Columns("A:G").AutoFit Sheets("Лист1").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Лист1").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row - 1 Sheets("Лист1").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Range("A6:G6").Merge End Sub
[/vba]
lyksor25, добрый день,вот макрос с выгрузкой данных на лист Лист1(его надо добавить в книгу) ,-в файл примере,кнопка yyy на листе 1(надо сжать, загружу чуть позже),вызов макроса с Вашего листа 1.
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets("Лист1").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Columns("A:G").AutoFit Sheets("Лист1").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Лист1").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row - 1 Sheets("Лист1").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Range("A6:G6").Merge End Sub
lyksor25, добавляю макрос,с выгрузкой на лист "Итоговый лист, так должно быть",кнопка yyy
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value: m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets(Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value: m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Итоговый лист, так должно быть").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row + 1 Sheets("Итоговый лист, так должно быть").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A" & i2 & ":G" & i2).Merge End Sub
[/vba]
lyksor25, добавляю макрос,с выгрузкой на лист "Итоговый лист, так должно быть",кнопка yyy
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value: m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets(Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value: m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Итоговый лист, так должно быть").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row + 1 Sheets("Итоговый лист, так должно быть").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A" & i2 & ":G" & i2).Merge End Sub
sv2014, спасибо большое за помощь, но я пока не совсем разобрался, появилось несколько вопросов. Вот исходный код макроса:
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value: m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets(Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value: m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Итоговый лист, так должно быть").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row + 1 Sheets("Итоговый лист, так должно быть").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A" & i2 & ":G" & i2).Merge End Sub
[/vba]
1) в какой строке меняется временной промежуток, например на промежуток январь-февраль? 2) у меня имеется больше количество вкладок - 12, как это учесть ? 3) количество столбцов в моей рабочей таблице не много больше, до буквы "L".
Буду ждать от Вас помощи) Спасибо большое!!! [moder]Оформляйте коды тегами (кнопка #)[/moder]
sv2014, спасибо большое за помощь, но я пока не совсем разобрался, появилось несколько вопросов. Вот исходный код макроса:
[vba]
Код
Sub yyy() Dim z(), i&, i1&, i2&, j&, m& i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value: m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets(Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A3:G3").Merge i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:G" & i1).Value: m = 1 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And InStrRev(z(i, 5), "12") Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next i2 = Sheets("Итоговый лист, так должно быть").Range("A" & Range("A1").End(xlDown).Row).End(xlDown).Row + 1 Sheets("Итоговый лист, так должно быть").Range("A" & i2).Resize(m, UBound(z, 2)).Value = z Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit Sheets("Итоговый лист, так должно быть").Range("A" & i2 & ":G" & i2).Merge End Sub
[/vba]
1) в какой строке меняется временной промежуток, например на промежуток январь-февраль? 2) у меня имеется больше количество вкладок - 12, как это учесть ? 3) количество столбцов в моей рабочей таблице не много больше, до буквы "L".
Буду ждать от Вас помощи) Спасибо большое!!! [moder]Оформляйте коды тегами (кнопка #)[/moder]lyksor25
Сообщение отредактировал Pelena - Среда, 13.01.2016, 08:41
lyksor25, добрый день,выложите уточненный полный файл-пример со всеми столбцами и вкладками на январь-февраль,например сжать можно.Для перехода на январь-февраль с ноября-декабрь, поменяйте в функции InStrRev(в двух обращениях,строки5, 6 и16,17) 11 на 1,а 12 соответственно на 2.Попробуйте ,для изменения диапазона, поменять в Z(третья строка),соответственно G на L
lyksor25, добрый день,выложите уточненный полный файл-пример со всеми столбцами и вкладками на январь-февраль,например сжать можно.Для перехода на январь-февраль с ноября-декабрь, поменяйте в функции InStrRev(в двух обращениях,строки5, 6 и16,17) 11 на 1,а 12 соответственно на 2.Попробуйте ,для изменения диапазона, поменять в Z(третья строка),соответственно G на Lsv2014
Сообщение отредактировал sv2014 - Среда, 13.01.2016, 09:42
sv2014, ваши советы учел, макрос начал работать, все выгружает, осталось 2 проблемы: 1) при выгрузке нет расширения до 12 вкладок - работает только с вкладками "1" и "2". Как это расширить? 2) при обработке первого раздела выгружает только 2 задачи, потом все отсекается следующим разделом, можно ли сделать этот показатель "плавающим" Прикладываю пример того, что макрос делает и что делать должен. Спасибо за помощь!
sv2014, ваши советы учел, макрос начал работать, все выгружает, осталось 2 проблемы: 1) при выгрузке нет расширения до 12 вкладок - работает только с вкладками "1" и "2". Как это расширить? 2) при обработке первого раздела выгружает только 2 задачи, потом все отсекается следующим разделом, можно ли сделать этот показатель "плавающим" Прикладываю пример того, что макрос делает и что делать должен. Спасибо за помощь!lyksor25
lyksor25, добрый день,только прочитал,Ваш ответ,(вчера был занят на работе),постараюсь сделать уточненный файл сегодня-завтра(будет посвободнее),возник вопрос к выходным результатам нового файл примера-,почему еще строка 5 задача 5 16.11.2015 15.12.2015 не вошла в желаемый результат(хотя, первый столбец ноябрь,второй декабрь)
lyksor25, добрый день,только прочитал,Ваш ответ,(вчера был занят на работе),постараюсь сделать уточненный файл сегодня-завтра(будет посвободнее),возник вопрос к выходным результатам нового файл примера-,почему еще строка 5 задача 5 16.11.2015 15.12.2015 не вошла в желаемый результат(хотя, первый столбец ноябрь,второй декабрь) sv2014
Уже в большей части кода разобрался, остался только один вопрос - как начать вставлять данные из следующего раздела, сразу после окончания предыдущего. Сейчас сделал только начала написания новой части только с точной ячейки: [vba]
Код
i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:L" & i1).Value m = 1 For i = 2 To UBound(z) If z(i, 10) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11") Or InStrRev(z(i, 6), "12") Or InStrRev(z(i, 6), "11") Or InStrRev(z(i, 7), "11") Or InStrRev(z(i, 7), "12")) Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets("Лист1").Range("A20").Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Columns("A:L").AutoFit Sheets("Лист1").Range("A3:L3").Merge
[/vba]
Я задаю точное значение в команде Range("A20") и макрос начинает писать с 20 строки новый цикл. Как можно это поправить?
Уже в большей части кода разобрался, остался только один вопрос - как начать вставлять данные из следующего раздела, сразу после окончания предыдущего. Сейчас сделал только начала написания новой части только с точной ячейки: [vba]
Код
i1 = Sheets("2").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("2").Range("A3:L" & i1).Value m = 1 For i = 2 To UBound(z) If z(i, 10) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11") Or InStrRev(z(i, 6), "12") Or InStrRev(z(i, 6), "11") Or InStrRev(z(i, 7), "11") Or InStrRev(z(i, 7), "12")) Then m = m + 1: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next End If Next Sheets("Лист1").Range("A20").Resize(m, UBound(z, 2)).Value = z Sheets("Лист1").Columns("A:L").AutoFit Sheets("Лист1").Range("A3:L3").Merge
[/vba]
Я задаю точное значение в команде Range("A20") и макрос начинает писать с 20 строки новый цикл. Как можно это поправить?lyksor25
Сообщение отредактировал lyksor25 - Четверг, 14.01.2016, 13:41
lyksor25, добрый вечер,макрос yyy9 для трех вкладок(n=3),можно менять значение n Макрос yyy8 копирует на Лист2,есть кнопки на Листе 2,удалить формат,установить формат.
[vba]
Код
Sub yyy9() Dim z(), i&, i1&, i2&, j&, m&, k&, n& n = 3 i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value ReDim z1(1 To n * UBound(z), 1 To UBound(z, 2)) For i = 1 To 3 For j = 1 To UBound(z, 2): z1(i, j) = z(i, j): Next Next m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And (InStrRev(z(i, 5), "12") Or InStrRev(z(i, 5), "11")) Then m = m + 1: For j = 1 To UBound(z, 2): z1(m, j) = z(i, j): Next End If Next For k = 2 To n i1 = Sheets(k).Range("A" & Rows.Count).End(xlUp).Row z = Sheets(k).Range("A3:G" & i1).Value For j = 1 To UBound(z, 2): z1(m + 1, j) = z(1, j): Next m = m + 1 For j = 1 To UBound(z, 2): z1(m + 1, j) = z(1, j): Next For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And (InStrRev(z(i, 5), "12") Or InStrRev(z(i, 5), "11")) Then m = m + 1: For j = 1 To UBound(z, 2): z1(m, j) = z(i, j): Next End If Next Next Sheets("Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z1, 2)).Value = z1 Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit End Sub
[/vba]
lyksor25, добрый вечер,макрос yyy9 для трех вкладок(n=3),можно менять значение n Макрос yyy8 копирует на Лист2,есть кнопки на Листе 2,удалить формат,установить формат.
[vba]
Код
Sub yyy9() Dim z(), i&, i1&, i2&, j&, m&, k&, n& n = 3 i1 = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row z = Sheets("1").Range("A1:G" & i1).Value ReDim z1(1 To n * UBound(z), 1 To UBound(z, 2)) For i = 1 To 3 For j = 1 To UBound(z, 2): z1(i, j) = z(i, j): Next Next m = 3 For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And (InStrRev(z(i, 5), "12") Or InStrRev(z(i, 5), "11")) Then m = m + 1: For j = 1 To UBound(z, 2): z1(m, j) = z(i, j): Next End If Next For k = 2 To n i1 = Sheets(k).Range("A" & Rows.Count).End(xlUp).Row z = Sheets(k).Range("A3:G" & i1).Value For j = 1 To UBound(z, 2): z1(m + 1, j) = z(1, j): Next m = m + 1 For j = 1 To UBound(z, 2): z1(m + 1, j) = z(1, j): Next For i = 2 To UBound(z) If z(i, 7) = "Иванов" And (InStrRev(z(i, 3), "12") Or InStrRev(z(i, 3), "11")) And (InStrRev(z(i, 5), "12") Or InStrRev(z(i, 5), "11")) Then m = m + 1: For j = 1 To UBound(z, 2): z1(m, j) = z(i, j): Next End If Next Next Sheets("Итоговый лист, так должно быть").Range("A1").Resize(m, UBound(z1, 2)).Value = z1 Sheets("Итоговый лист, так должно быть").Columns("A:G").AutoFit End Sub
lyksor25, добавлю,при тестировании макроса yyy8 на листе Лист2,очищайте вручную все начиная со строки 4( не установил кнопку очистки на листе Лист2), строки 1:3 на листе Лист2 не трогайте,-кнопка установить формат,использует форматирование третьей строки.
lyksor25, добавлю,при тестировании макроса yyy8 на листе Лист2,очищайте вручную все начиная со строки 4( не установил кнопку очистки на листе Лист2), строки 1:3 на листе Лист2 не трогайте,-кнопка установить формат,использует форматирование третьей строки.sv2014
sv2014, Вот теперь все отлично просто, только остался мааааленький недочет, я его упустил первоначально из вида - при поиске строк содержащих критерий "Иванов" можно ли сделать так, чтобы макрос искал не только точное значение, но и учитывал такие значения как, например, "Иванов П." или "Иванов И.С." и т.д., т.е чтобы поиск проводился не по точному значению, а по части значения? Большое Вам спасибо за помощь! Очень выручаете!!!
sv2014, Вот теперь все отлично просто, только остался мааааленький недочет, я его упустил первоначально из вида - при поиске строк содержащих критерий "Иванов" можно ли сделать так, чтобы макрос искал не только точное значение, но и учитывал такие значения как, например, "Иванов П." или "Иванов И.С." и т.д., т.е чтобы поиск проводился не по точному значению, а по части значения? Большое Вам спасибо за помощь! Очень выручаете!!!lyksor25