1

Есть ли способ добавить несколько предопределенных ячеек анализа / оценки в существующую электронную таблицу?

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

Есть ли хороший способ сохранить эти оценочные ячейки в качестве шаблона и применить их к вновь созданному файлу?

Пример файла / таблицы, сгенерированной из приложения:

+-------------+
| ColA | ColB |
+-------------+
|  foo | baz  |
|  bar | baz  |
|  foo |      |
|  bar |      |
+-------------+

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

+-----------------------------+
| ColA | ColB | Eval1 | Eval2 |
+-----------------------------+
|  foo | baz  |   1   |   1   |
|  bar | baz  |   0   |   1   |
|  foo |      |   1   |   0   |
|  bar |      |   0   |   0   |
+-----------------------------+

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

Этот вопрос не о каких-либо расчетах, а о процессе добавления пользовательских предопределенных формул во вновь создаваемые файлы .xlsx . Таблицы «до» и «после» в моем примере служат только для демонстрации того, куда следует добавлять столбцы, но не предназначены для передачи какой-либо семантики.

Для моей явной проблемы я всегда получаю файл со столбцами Month , Employee и WorkHours . Затем необходимо добавить столбцы с процентами, что я обычно делаю вручную и перетаскиваю их вниз для всех строк. Это не сложная задача, но мне нужен способ сделать ее доступной для моих коллег, не заставляя их вручную копировать формулы из файла.

+-----------------------------------------------------------+
|Month    Employee   WorkHours    25%    50%    75%    100% |
|01.2016  PersonA       149,25    FormA  FormB  FormC  FormD|
|01.2016  PersonB        79,00    FormA  FormB  FormC  FormD|
+-----------------------------------------------------------+

Формулы следующие:

FormA: =IF(AND(C2>0;C2<=40);0,25;"")
FormB: =IF(AND(C2>40;C2<=80);0,5;"")
FormC: =IF(AND(C2>80;C2<=120);0,75;"")
FormD: =IF(C2>120;1;"")
|источник

1 ответ1

2

Конечно, вы можете использовать подпрограмму (ы) VBA для автоматизации большинства действий, которые можно выполнять вручную (и которые можно объективно описать).  На ум приходит пара широких подходов:

  • Создайте свой текущий подход к наличию файла шаблона с предопределенными формулами.  Добавьте VBA к этому файлу, чтобы автоматизировать то, что вы делаете сейчас: скопируйте формулы из шаблона в сгенерированный файл.  Это может быть лучшим (более легким) подходом, особенно если вы планируете изменить формулы анализа / оценки - и особенно если вам нужно разрешить своим коллегам изменять шаблон.
  • Создайте новый файл Excel, который содержит только VBA. Внедрите нужные формулы листа Excel в VBA в качестве данных.  Это позволит автоматизировать процесс ввода формул в новый лист.  Этот подход может облегчить адаптацию формул к существующим данным.

Конечно, это не или / или; Вы можете разработать гибридное решение, которое сочетает в себе аспекты вышеупомянутого.

Вы должны увидеть, как добавить VBA в MS Office? для общей информации.  Здесь описано, как активировать вкладку «Разработчик» на ленте.  Если вы не знаете VBA, для начала лучше всего нажать «Запись макроса» на вкладке «Разработчик», выбрать имя и нажать «ОК», выполнить действия, которые вы хотите записать / автоматизировать, а затем нажмите «Остановить запись» (это та же кнопка, что и «Запись макроса»).  Кажется, я вспоминаю, что десять или двадцать лет назад макросы были записаны и сохранены как последовательности / сценарии нажатия клавиш; теперь они переведены в эквивалент VBA.

Вы, вероятно, захотите отредактировать код, чтобы сделать его пуленепробиваемым.  Обучение вас VBA выходит за рамки этого сайта, но мне пришло в голову, что вы захотите динамически «найти» лист, в который вы хотите поместить готовые формулы.  Вот подход к этому:

Set curw = ActiveWindow
Set curs = ActiveSheet
For Each w In Windows
    w.Activate
    For Each s In Sheets
        s.Activate
        If Range("A1") = "Month" And Range("B1") = "Employee" _
                                 And Range("C1") = "WorkHours" Then
            ' This is the sheet where I want to put the analysis/evaluation formulas.
            If Range("D1") = "" And Range("E1") = "" And Range("F1") = "" _
                                And Range("G1") = "" Then
                ' Put the formulas here.
            Else
                MsgBox "There’s already something here!"
            End If
        End If
    Next s
Next w
curw.Activate
curs.Activate

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

Вы можете попросить своих коллег открыть файл шаблона и запустить макрос для запуска макроса для установки формул.  Вы можете создать кнопку, чтобы избавить их от необходимости навигации по меню.  См. Также Как хранить макросы VBA для Office 2007, чтобы они всегда были доступны и обменивались ими с другими? для получения информации о том, как сделать макрос доступным для ваших коллег, даже не открывая файл шаблона.

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .