Adicione colunas adicionais à planilha com base no modelo

Question

Existe uma maneira de adicionar algumas células de análise / avaliação predefinidas a uma planilha existente?

Meu caso de uso é um aplicativo que gera um arquivo do MS Excel com dados de funcionários de um banco de dados. Precisamos avaliar esses dados de maneiras que não são possíveis no aplicativo, por isso adicionamos algumas colunas com fórmulas no Excel.

Existe uma boa maneira de salvar essas células de avaliação como um modelo e aplicá-las a um arquivo recém-gerado?

Exemplo de arquivo / tabela que é gerado a partir do aplicativo:

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

Com fórmulas adicionais que devem ser adicionadas automaticamente (atualmente, uso copiar / colar para adicionar as fórmulas de um arquivo antigo)

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

Editar para esclarecer: eu não quero nenhuma fórmula explícita, mas uma maneira mais geral sobre como adicionar colunas arbitrárias com fórmulas. Eu sou muito novo no Excel (e não toquei no VBA até agora), então não sei se isso é possível e quanto trabalho está envolvido.

Esta questão não é sobre qualquer cálculo, mas mais sobre o processo de adicionar fórmulas predefinidas personalizadas aos arquivos .xlsx recém-gerados. As tabelas before e after no meu exemplo são apenas para demonstrar onde as colunas devem ser adicionadas, mas não são destinadas a transportar qualquer semântica.

Para meu problema explícito, sempre obtenho um arquivo com as colunas Month , Employee e WorkHours . Em seguida, as colunas com as porcentagens precisam ser adicionadas, o que geralmente faço à mão e arrasto-as para todas as linhas. Esta não é uma tarefa complicada, mas eu preciso de uma maneira de torná-la acessível aos meus colegas de trabalho sem que eles copiem manualmente as fórmulas de um arquivo em algum lugar.

+-----------------------------------------------------------+
|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|
+-----------------------------------------------------------+

As fórmulas são as seguintes:

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;"")

    

Answer 1

Certamente você pode usar rotina (s) VBA para automatizar a maioria das atividades isso pode ser feito manualmente (e pode ser descrito objetivamente). Algumas abordagens amplas vêm à mente:

• Construa sua abordagem atual de ter um arquivo de modelo com as fórmulas predefinidas. Adicione o VBA a esse arquivo para automatizar o que você está fazendo agora: Copie as fórmulas do modelo para o arquivo gerado. Esta pode ser a melhor abordagem (mais fácil), especialmente se você espera mudar as fórmulas de análise / avaliação - e especialmente se você precisar permitir que seus colegas de trabalho modifiquem o modelo.
• Crie um novo arquivo do Excel que contenha apenas VBA. Incorpore as fórmulas de planilha do Excel desejadas no VBA como dados. Isso automatizaria a ação de digitar as fórmulas na nova planilha. Essa abordagem pode facilitar a adaptação das fórmulas aos dados existentes.

Claro, não é um ou outro; você pode criar uma solução híbrida que combine aspectos do acima.

Você deve ver Como adiciono o VBA no MS Office? para obter informações gerais. Isso descreve como ativar a guia "Desenvolvedor" na sua faixa de opções. Se você não conhece o VBA, uma boa maneira de começar é clicar em "Gravar Macro" na guia "Desenvolvedor", escolha um nome e clique em "OK", percorrer as ações que você deseja gravar / automatizar, e depois clique em “Stop Recording” (que é o mesmo botão que “Record Macro”). Eu me lembro que, dez ou vinte anos atrás, macros foram gravadas e armazenadas como seqüências / scripts de pressionamentos de tecla; agora eles são traduzidos para o VBA equivalente.

Você provavelmente desejará editar o código para torná-lo à prova de balas. Ensinar você VBA está além do escopo deste site, mas me ocorreu que você gostaria de "encontrar" dinamicamente a planilha onde você quer colocar as fórmulas em lata. Aqui está uma abordagem para fazer isso:

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

Se você pesquisar este site e outros recursos, você encontrará muitos exemplos de como determinar quantos dados já está na planilha de destino (para que você possa estender suas novas colunas para corresponder).

Você pode dizer aos seus colegas de trabalho para abrir o arquivo de modelo e execute uma macro para executar a macro para instalar as fórmulas. Você pode criar um botão para poupar o incômodo de precisar navegar pelos menus. Consulte também Como armazenar macros VBA para o Office 2007 tê-los sempre disponíveis e compartilhá-los com os outros? para obter informações sobre como disponibilizar a macro para seus colegas de trabalho sem precisar abrir o arquivo de modelo.