Перенос данных в отдельные листы по форме и условию

@Султанов · Регистрация: 25.01.2013

Author24 — интернет-сервис помощи студентам

Здравствуйте!!
Есть массив Данных в разрезе Отделов, нужно перенести каждый Отдел из Данных в отдельные листы с наименованием листа также как наименование Отдела, т.е созданием нового листа
Какой код VBA должен быть для переноса данных в отдельные листы, кто-нибудь уже решал автоматизацию отчетов и расчетов, при чем по следующим условиям:
1) формирование Формы по выбору Отдела1, данные берутся с листа Данные, сформированная Форма по нажатию кнопки копировалось в новый лист, при этом основной очищался при наборе другого, например Отдела2, и Форма менялась по составу сотрудников, входящих в Отдел2, и снова и так далее
2) Также вставки формул в зависимости от длины списка сотрудников и должности, на каждую должность своя формула расчета
Прилагаю пример какие данные, и что должно быть в результате

@Igor_Tr · 25.04.2013, 22:16

Собираете в словарь, где ключ - это отдел, а Item - все что хотите. Потом, перебирая ключи словаря, если есть лист - очищаете и перезаписываете свежие данные. Нет - создаете с именем ключа и записываете все, что к нему относится.
А форма зачем? Для лишней головной боли? Или для красоты?

@Султанов · 26.04.2013, 08:48 **[ТС]**

Igor_Tr, скажем получаю список, который увеличивается по длине или сокращется в зависимости от сокращения, перемщения должности из одного отдела в другой, как присвоить ключ и Itemы к ключю, если они в одном столбце, не перемещая наименования отдела в дргой столбец. Может как то формата ячейки, а с форматами у меня пока ни как. Не выставите ли образец в коде VBA?
Форма наподобие Отчета, в которому по Отделу прооизводтся расчеты, а вот формулы проставляются в зависимоти от длины списка должностей и их категорий, как на выложенном ранее примере

@Igor_Tr · 26.04.2013, 11:13

Киньте в формате *.xls (2003). Немного разгребу свое и прикинем. Не будет зависеть от форматов. Так понимаю, что весь список нужно разсортировать по отделам и каждый отдел (с всем, что к нему относится) - на отдельный лист? Потом в главном списке происходят какие-то изменения/перемещения/увеличения/сокращения и нужно опять то-же, но в новом составе? Это так?

@Hugo121 · 26.04.2013, 11:46

Я бы в лист данных добавил ещё один столбец с признаком отдела (можно скрытый) - так как сделано сейчас это крайне неудобно для обработки кодом (можно конечно и так исхитриться, но зачем? Создавать проблемы, чтоб их героически преодолевать (не самому

)?)
Откуда берутся цифры и формулы - совершенно непонятно.

@Султанов · 26.04.2013, 14:27 **[ТС]**

Сообщение от Igor_Tr

Так понимаю, что весь список нужно разсортировать по отделам и каждый отдел (с всем, что к нему относится) - на отдельный лист?

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

Сообщение от Igor_Tr

Потом в главном списке происходят какие-то изменения/перемещения/увеличения/сокращения и нужно опять то-же, но в новом составе? Это так?

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

Hugo121,

Сообщение от Hugo121

Я бы в лист данных добавил ещё один столбец с признаком отдела (можно скрытый) - так как сделано сейчас это крайне неудобно для обработки кодом (можно конечно и так исхитриться, но зачем? Создавать проблемы, чтоб их героически преодолевать (не самому )?)

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

Сообщение от Hugo121

Откуда берутся цифры и формулы - совершенно непонятно.

Цифры с другого отчета, кторорые проставлю кодом по ФИО и должности, чтобы наверняка не ошибся с однофамильцами, но даже если так будет контрольную цифру проверю. А вот формулы от количества сотрудников в форме, ну справочник формул супротив должности сделать можно для вставки, но вот их изменять програмно.... не научился пока, поскольку высчитвают среднюю по количсеству сотрудников, а текучесть сотрудников колоссальная. Этот отчет делать каждый день тяжко на 60 подразделений...

@Султанов · 26.04.2013, 14:30 **[ТС]**

файл формата 97-2007 Офиса

@Султанов · 26.04.2013, 16:03 **[ТС]**

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

Visual Basic

 With Worksheets("ШР")
        f = Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 3))
        f1 = Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 3)).[U]атрибуты цвета ячейки в массив[/U]        
    End With

@Igor_Tr · 27.04.2013, 16:41

Только свое разобрал.
Смотрю и не понимаю. На листе Данные - все рассортировано. Вытягивать от метки до метки (серый цвет) - и все. В чем проблема? Может еще эти метки и установить, если нужно. А как в Данные заносят записи? Вот на листе Форма появились коэф-ты. Их разноска как выполняется? Вручную?

@Султанов · 28.04.2013, 08:26 **[ТС]**

Какие метки? по формату ячейки?есть ли другой способ без формата и метол через словарь

@Igor_Tr · 28.04.2013, 15:02

Я имел ввиду заливку ячейки с наименованием отдела (не знаю, откуда и как попадают данные на лист "Данные").
Здесь сделано без привязки к форматам. Считаем, что у Вас на листе "Данные" все как теперь (добавляйте, удаляйте - нет проблем). Раскидает все по листам, укажет кол-во сотрудников. Коэфициенты, стоимость - не знаю, откуда берутся. Сами привинтите остальное. Пробуйте. Старт - с листа "Данные".

@Igor_Tr · 28.04.2013, 17:56

Смешно, конечно, но не зная, откуда и как у Вас берутся/формируются данные, у меня такое себе настойчивое чувство, что Вам надо все с точностью наоборот. Кидаю еще одну книгу. Там данные уже раскиданы. Считаем, что листа "Данные" не существует. На листе "Отдел1" нажмите надпись. Он соберет все в новый лист. При изменениях на листах отделов - нажать надпись - тоже все перезапишется. Вобщем, лепите с этих заготовок для себя что нужно. Удачи.

@Igor_Tr · 28.04.2013, 17:59

Прошляпил файл. От жары уже мозги улетают.

@Igor_Tr · 28.04.2013, 18:21

Кошмар какой-то. Сейчас опять на речку убегу. Забыл сказать, на всякий. В редакторе, Tools > Reference поключите (поставьте галочки) библиотеки:

Microsoft VBScript Regular Expressions 5.5
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects 2.5 Library

А то работать не будет.

@Султанов · 29.04.2013, 12:38 **[ТС]**

Igor_Tr, а как добавить что бы выбирала Отдел по определенному критерию

Visual Basic

 With Sheets("ØÐ")
        counter = 0
        p = Array("*ïåðàöèîí*", "*ÑÏÔ*")
        For Each currcell In .Range("a1:a" & .Rows.Count). _
         SpecialCells(xlCellTypeConstants, 3)
            If InStr(1, currcell, p) > 0 Then
                If currcell.Interior.ColorIndex = 15 Then
                    If currcell.Font.Bold = True Then
                        If currcell.Font.Color = 0 Then
                            counter = counter + 1
                            ReDim Preserve arrDPT(counter)
                            arrDPT(counter) = currcell.Row
                        End If: End If: End If: End If

@Султанов · 29.04.2013, 12:54 **[ТС]**

Igor_Tr, Премного благодарен Вам, как можно реализовать выборку определенных отделов, мне нужно с Данные только определенные блоки, но с добавлением условия, он у меня ругается на тип перемнных

@Igor_Tr · 29.04.2013, 13:24

Киньте в *.xls. В каком месте ругается?

как можно реализовать выборку определенных отделов

Он ведь Вам все раскидывает. Я не могу понять, что у Вас первое. Лист Данные, или Лист отдела. Как Вы заносите данные. Все подряд, потом сортируете? Или получаете из сторонней программы. Тяжело ориентироваться. И потом конечный результат. Если на распечатку - лучьше получать сразу все разнесенное и обновленное, и печатать только нужное. Там все полученные результаты можно удалять (а можно не удалять). Каждый вызов процедуры будет все обновлять (ищиете в кодах .Cells.Delete - и поймете.)

Добавлено через 13 минут
Я теперь открыл последнюю книгу v2_New_Отчет.xls. Нигде не ругался. Единственное, при пуске "Forwards", когда удаляется все и перезаписывается на листе Отдел1, прячется "CollectMyDATA" (!!! не уничтожается!!!). Найдите ее, правая кнопка > Формат...> Свойства и измените, чтоб не пряталась надпись.

@Султанов · 29.04.2013, 13:33 **[ТС]**

Сообщение от Igor_Tr

Киньте в *.xls. В каком месте ругается?

Он ведь Вам все раскидывает. Я не могу понять, что у Вас первое. Лист Данные, или Лист отдела. Как Вы заносите данные. Все подряд, потом сортируете? Или получаете из сторонней программы. Тяжело ориентироваться. И потом конечный результат. Если на распечатку - лучьше получать сразу все разнесенное и обновленное, и печатать только нужное. Там все полученные результаты можно удалять (а можно не удалять). Каждый вызов процедуры будет все обновлять (ищиете в кодах .Cells.Delete - и поймете.)

Первое что получаю со сторонней программы - это лист Данные, который динамичен, из этого листа мне нужны определенные отделы по условию, т.е в назаваниях сектров присуствие слов "СПФ" или "перацион", причем блок кторый залит 15 цветом и омечен полужирным шрифтом
Псоледующий шаг полученых блоков Отделов на новых листах, по ФИО определяю их продажи (отделный другой лист сведенный по ФИО), также по должности и региона присутствия определяю коэф (еще другой лист типа справочника), в итоге в зависмоти от должности и кол-ва работников прорставляю формулы, начальники получает среднюю от продажи блока, замы в зависмости от продаж менеджеров, каждый же менеджер в зависмоти от объема прождаж. В конечно счете новый лист "Отдел" со всеми расчетами идет напечать. У меня только вопрос выноса блока с Данных в новый лист по критерию

@Султанов · 29.04.2013, 13:35 **[ТС]**

вот файл Данных

@Igor_Tr · 29.04.2013, 13:40

КИНЬТЕ ЛИСТ В 2003.
Давайте так. Я не очень любитель татуировок. На листе Excel - тем более. Раскрасить, как верблюда северной Африки, а дальше - сидеть и умиляться?

То-есть, к форматам и краскам привязываться не будем, хорошо?. Кидайте. Не обещаю, что сразу (все-таки на работе), но обещаю, что обязательно.
О! Уже вижу лист.

@Султанов 54 / 39 / 3 Регистрация: 25.01.2013 Сообщений: 368
	28.04.2013, 08:26 [ТС]	10
	Какие метки? по формату ячейки?есть ли другой способ без формата и метол через словарь 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	25.04.2013, 22:16	2
	Собираете в словарь, где ключ - это отдел, а Item - все что хотите. Потом, перебирая ключи словаря, если есть лист - очищаете и перезаписываете свежие данные. Нет - создаете с именем ключа и записываете все, что к нему относится. А форма зачем? Для лишней головной боли? Или для красоты? 0

@Султанов 54 / 39 / 3 Регистрация: 25.01.2013 Сообщений: 368
	26.04.2013, 08:48 [ТС]	3
	Igor_Tr, скажем получаю список, который увеличивается по длине или сокращется в зависимости от сокращения, перемщения должности из одного отдела в другой, как присвоить ключ и Itemы к ключю, если они в одном столбце, не перемещая наименования отдела в дргой столбец. Может как то формата ячейки, а с форматами у меня пока ни как. Не выставите ли образец в коде VBA? Форма наподобие Отчета, в которому по Отделу прооизводтся расчеты, а вот формулы проставляются в зависимоти от длины списка должностей и их категорий, как на выложенном ранее примере 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	26.04.2013, 11:13	4
	Киньте в формате *.xls (2003). Немного разгребу свое и прикинем. Не будет зависеть от форматов. Так понимаю, что весь список нужно разсортировать по отделам и каждый отдел (с всем, что к нему относится) - на отдельный лист? Потом в главном списке происходят какие-то изменения/перемещения/увеличения/сокращения и нужно опять то-же, но в новом составе? Это так? 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	26.04.2013, 11:46	5
	Я бы в лист данных добавил ещё один столбец с признаком отдела (можно скрытый) - так как сделано сейчас это крайне неудобно для обработки кодом (можно конечно и так исхитриться, но зачем? Создавать проблемы, чтоб их героически преодолевать (не самому )?) Откуда берутся цифры и формулы - совершенно непонятно. 0

@Султанов 54 / 39 / 3 Регистрация: 25.01.2013 Сообщений: 368
	26.04.2013, 14:27 [ТС]	6
	Сообщение от Igor_Tr Так понимаю, что весь список нужно разсортировать по отделам и каждый отдел (с всем, что к нему относится) - на отдельный лист? да именно так, при этом они в одном столбце в списке, что наименование отдела и принадлежащие к нему, отделы отмечены серым цветом и выделены жирным шрифтом, входящие в него без формата Сообщение от Igor_Tr Потом в главном списке происходят какие-то изменения/перемещения/увеличения/сокращения и нужно опять то-же, но в новом составе? Это так? да, в форме данные также менялись в соотвествии со списком, на подобии выгрузки данных Hugo121, Сообщение от Hugo121 Я бы в лист данных добавил ещё один столбец с признаком отдела (можно скрытый) - так как сделано сейчас это крайне неудобно для обработки кодом (можно конечно и так исхитриться, но зачем? Создавать проблемы, чтоб их героически преодолевать (не самому )?) В том то и дело, что героически их надо выделять (не самому), а кодом, причем в каждом подразделении 5-13сеторов, служб, подразделений 60 Сообщение от Hugo121 Откуда берутся цифры и формулы - совершенно непонятно. Цифры с другого отчета, кторорые проставлю кодом по ФИО и должности, чтобы наверняка не ошибся с однофамильцами, но даже если так будет контрольную цифру проверю. А вот формулы от количества сотрудников в форме, ну справочник формул супротив должности сделать можно для вставки, но вот их изменять програмно.... не научился пока, поскольку высчитвают среднюю по количсеству сотрудников, а текучесть сотрудников колоссальная. Этот отчет делать каждый день тяжко на 60 подразделений... 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	27.04.2013, 16:41	9
	Только свое разобрал. Смотрю и не понимаю. На листе Данные - все рассортировано. Вытягивать от метки до метки (серый цвет) - и все. В чем проблема? Может еще эти метки и установить, если нужно. А как в Данные заносят записи? Вот на листе Форма появились коэф-ты. Их разноска как выполняется? Вручную? 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	28.04.2013, 17:56	12
	Смешно, конечно, но не зная, откуда и как у Вас берутся/формируются данные, у меня такое себе настойчивое чувство, что Вам надо все с точностью наоборот. Кидаю еще одну книгу. Там данные уже раскиданы. Считаем, что листа "Данные" не существует. На листе "Отдел1" нажмите надпись. Он соберет все в новый лист. При изменениях на листах отделов - нажать надпись - тоже все перезапишется. Вобщем, лепите с этих заготовок для себя что нужно. Удачи. 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	28.04.2013, 18:21	14
	Кошмар какой-то. Сейчас опять на речку убегу. Забыл сказать, на всякий. В редакторе, Tools > Reference поключите (поставьте галочки) библиотеки: Microsoft VBScript Regular Expressions 5.5 Microsoft Scripting Runtime Microsoft ActiveX Data Objects 2.5 Library А то работать не будет. 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	29.04.2013, 13:24	17
	Киньте в .xls. В каком месте ругается? как можно реализовать выборку определенных отделов* Он ведь Вам все раскидывает. Я не могу понять, что у Вас первое. Лист Данные, или Лист отдела. Как Вы заносите данные. Все подряд, потом сортируете? Или получаете из сторонней программы. Тяжело ориентироваться. И потом конечный результат. Если на распечатку - лучьше получать сразу все разнесенное и обновленное, и печатать только нужное. Там все полученные результаты можно удалять (а можно не удалять). Каждый вызов процедуры будет все обновлять (ищиете в кодах .Cells.Delete - и поймете.) Добавлено через 13 минут Я теперь открыл последнюю книгу v2_New_Отчет.xls. Нигде не ругался. Единственное, при пуске "Forwards", когда удаляется все и перезаписывается на листе Отдел1, прячется "CollectMyDATA" (!!! не уничтожается!!!). Найдите ее, правая кнопка > Формат...> Свойства и измените, чтоб не пряталась надпись. 0

@Султанов 54 / 39 / 3 Регистрация: 25.01.2013 Сообщений: 368
	29.04.2013, 13:33 [ТС]	18
	Сообщение от Igor_Tr Киньте в .xls. В каком месте ругается? Он ведь Вам все раскидывает. Я не могу понять, что у Вас первое. Лист Данные, или Лист отдела. Как Вы заносите данные. Все подряд, потом сортируете? Или получаете из сторонней программы. Тяжело ориентироваться. И потом конечный результат. Если на распечатку - лучьше получать сразу все разнесенное и обновленное, и печатать только нужное. Там все полученные результаты можно удалять (а можно не удалять). Каждый вызов процедуры будет все обновлять (ищиете в кодах .Cells.Delete* - и поймете.) Первое что получаю со сторонней программы - это лист Данные, который динамичен, из этого листа мне нужны определенные отделы по условию, т.е в назаваниях сектров присуствие слов "СПФ" или "перацион", причем блок кторый залит 15 цветом и омечен полужирным шрифтом Псоледующий шаг полученых блоков Отделов на новых листах, по ФИО определяю их продажи (отделный другой лист сведенный по ФИО), также по должности и региона присутствия определяю коэф (еще другой лист типа справочника), в итоге в зависмоти от должности и кол-ва работников прорставляю формулы, начальники получает среднюю от продажи блока, замы в зависмости от продаж менеджеров, каждый же менеджер в зависмоти от объема прождаж. В конечно счете новый лист "Отдел" со всеми расчетами идет напечать. У меня только вопрос выноса блока с Данных в новый лист по критерию 0

@Igor_Tr 4377 / 661 / 36 Регистрация: 17.01.2010 Сообщений: 2,134
	29.04.2013, 13:40	20
	КИНЬТЕ ЛИСТ В 2003. Давайте так. Я не очень любитель татуировок. На листе Excel - тем более. Раскрасить, как верблюда северной Африки, а дальше - сидеть и умиляться? То-есть, к форматам и краскам привязываться не будем, хорошо?. Кидайте. Не обещаю, что сразу (все-таки на работе), но обещаю, что обязательно. О! Уже вижу лист. 0