Войти

Показать полную графическую версию : Внесение изменений во все листы книги Excel при помощи VB макросов


Vadikan
23-12-2005, 08:01
Доброго времени суток!

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

Исходная задача
Требуется отслеживать производительность труда работников и выдавать результаты еженедельно и кумулятивно с начала года. Из базы данных при помощи некой проприетарной программы снимаются соответствующие отчеты, однако база данных хранит информацию только за последние несколько недель и соответственно получить кумулятивную информацию за год невозможно. Кроме того, на основе полученных данных нужно вывести дополнительные критерии производительности, не фигурирущие в оригинальном отчете, но которые можно подсчитать на основе исходных данных. Помимо этого, требуется вывешивать отчеты на всеобщее обозрение обеспечивая при этом конфиденциальность - имя работника не должно фигурировать в отчете, а вместо имени используется номер, известный только работнику. Проприетарный отчет можно экспортировать в HTML (oчень криво), JPEG (бесполезно) и Excel (тоже криво). Для реализации поставленной задачи был выбран Excel, т.к. это единственное из доступных приложений способное работать с экспортированными отчетами. Ну и напоследок, отчетами должна заниматься менеджер, которая обладает очень слабыми компьютерными знаниями и не имеет понятия что такое формула в Excel :)

Проблемы, с которыми я столкнулся
1. В оригинальном ежендельном отчете фигурируют работники, чью производительность подсчитывать не надо: это могут быть люди, которые редко выполняют данную работу, мендежеры, сисадмины и т.д.
2. В оригинальном ежендельном отчете фигурируют только работники, выполнявшие работу за отчетный период. Иными словами, если человек был в отпуске или не делал данную работу, его имени в отчете не будет.
3. В кумулятивный и модифицрованный отчеты может понадобиться добавить или убрать работника (нанали / уволили).

Что было сделано
Я создал книгу Excel с 53 листами. 52 листа для ежендельных отчетов и один лист для кумулятивного отчета. Понятно, что кумулятивный отчет суммирует данные из 52 еженедельных. Был записан макрос, приводящий исходный экспортированный отчет (привожу фрагмент)

http://tools.oszone.net/Vadikan/temp/a_original.gif

к нужному виду. Макрос убирает ненужные столбцы и строки, заменяет имена работников на назначенные им номера (левый столбец) и сортирует по левому столбцу A-Z, так что вверху столбца остаются только нужные для отчета люди и данные. Таким образом я худо-бедно справился с первой из возникших проблем - сортировка по возрастанию отметает всех ненужных в отчете людей.

http://tools.oszone.net/Vadikan/temp/a_modified.gif

для последующего копирования в еженедельный отчет.

Что хотелось бы реализовать
Проблема #2 автоматизирована не была. Всего числится больше работников, чем выполняло работу за отчетный период, следовательно скопировать данные и вставить их в лоб не получится. Обхдное решение на которое я оказался способен таково. После обработки оригинального отчета макросом мендежер копирует данные и вставляет в книгу в один из 52 листов.

http://tools.oszone.net/Vadikan/temp/a_report2.gif

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

http://tools.oszone.net/Vadikan/temp/a_report3.gif

После чего ненужная более столбец TempColumn удаляется. И получается вот такой отчет:

http://tools.oszone.net/Vadikan/temp/a_report.gif

Вопрос #1: можно ли автоматизировать операцию выравнивания строк при помощи макроса? Я никак не могу сообразить как это "записать", а написать соотв. скрипт не могу из-за отсуствия знаний :) Т.е. нужен LOOP в указанном диапазоне строк с примерно таким алгоритмом:

Если ячейка B7 равна C7 идем дальше (сравниваем B8 и C8)
Если ячейка B7 не равна C7 выделяем ячейки C7:F7 и сдвигаем их вниз
и повторяем операцию в заданном заранее диапазоне строк

Вопрос #2:
Едем дальше, а именно задаемся третьей проблемой. Нужен макрос который
а) Интерактивно добавит / удалит работника в список
б) внесет изменения в уже имеющийся макрос обработки отчета

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

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

Cells.Replace What:="Фамилия1, Имя1", Replacement:="27", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="Фамилия2, Имя2", Replacement:="33", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'После добавления появляется следующий работник
Cells.Replace What:="Фамилия3, Имя3", Replacement:="35", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False



Вообще, возможно все перечисленное осуществить в комплексе или по отдельности? Возможно, есть другие и более простые варианты решения поставленной задачи и я просто перемудрил. Буду признателен авторам любых конструктивных советов. Файлы для экспериментов могу предоставить.

Сорри за длинное описание и спасибо за внимание!

Vovchick1
26-12-2005, 02:38
После обработки оригинального отчета макросом мендежер копирует данные и вставляет в книгу в один из 52 листов.
А затем начинает выравнивать строки таким образом, что цифры столбца Analyst в каждой строке должны иметь такое же значение как цифры в столбца TempColumn. Выравнивание достигается выделением ячеек и вставкой со сдвигом вниз.

А не проще записывать строки так, что-бы значение "TempColumn" совпадало с номером строки, тогда столбец "TempColumn" вообще не нужен и выравнивать ни чего не надо!!!

Внесение изменения в таблицу(в ячейку со столбцом "B" и строкой "10", на листе "Лист3")

Sheets("Лист3").Range("B10").Value = "Данные"


Нужна серия вопросов с возможностью введения ответа

Решение №1
Dim A as string 'Переменная для принятия ответа
A= InputBox("Вопрос", "Заголовок окна", "Текст по умолчанию")

Решение №2
Ещё вариант добавить форму с соответствуюшими полями:


Может ещё где понадобится!!!
Вывод сообщения, или вопроса по типу да/нет

Dim M 'Переменная для принятия ответа(тип не помню)
M = MsgBox("Сообщение или вопрос",здесь константы (через "+") , "Заголовок")

Например:
M = MsgBox("Руки мыл??? :+)", vbQuestion + vbOKCancel, "")


Извени если что не так, просто может вопрос нетак понял!!!

Vadikan
28-12-2005, 05:48
Vovchick1
Спасибо за ответы.

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

По поводу InputBox, спасибо. Это просто, и я наверное устрою добавление нужных мне строк в лист(ы). Сложнее внести в один из уже имеющихся макросов изменения на основе переменных полученных из ответов на серию вопросов. Это вообще осуществимо?

Vovchick1
29-12-2005, 00:41
Гляну, может ещё что-нибудь придумаю!!!
изменения на основе переменных полученных из ответов на серию вопросов
Во замутил, ни.... не понял!!!

Vovchick1
29-12-2005, 02:10
А не проще записывать строки так, что-бы значение "TempColumn" совпадало с номером строки, тогда столбец "TempColumn" вообще не нужен и выравнивать ни чего не надо!!!Я вот очём:
1. Ты сначала добавляеш строки;
2. Потом выравниваеш;
3. Потом удаляеш столбец.

А можно сразу записать определённую строку на определённое место!!!(см. файл)

Помимо этого макрос должен добавить строку в уже существующий макрос, который выполняет замену имен на номера. У меня возникла другая идея(не знаю покатит или нет!!!): Имена можно сохранять на определённом лисе книги, и уже оттуда брать их для замены!!!




© OSzone.net 2001-2012