tag:blogger.com,1999:blog-27259747526331116422024-03-08T15:40:26.478-03:00Excel AvançadoVBA, Tabela Dinâmica, Importação e Exportação de DadosEvangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-2725974752633111642.post-17552817783669908052009-06-15T15:19:00.003-03:002009-06-15T15:28:02.620-03:00Procura em Lista FixaSe você precisa adicionar uma fórmula que retorno um determinado item a partir de uma lista, porém não quer escrever esta lista na planilha, você pode utilizar a fórmula abaixo:<br /><br /><span style="font-family:verdana;font-size:85%;">=EXT.TEXTO("Item1;Item2;Item3";Indice*(CaracteresItem+1)-CaracteresItem;CaracteresItem)</span><br /><br />Onde:<br /><br /><span style="font-family:verdana;font-size:85%;">"Item1;Item2;Item3": É a lista de itens onde a busca deve ser feita;</span><br /><span style="font-family:verdana;font-size:85%;">Indice: é a posição do item desejado;</span><br /><span style="font-family:verdana;font-size:85%;">CaracteresItem: é a quantidade de caracteres de cada item.</span><br /><br />Esta fórmula funciona bem para lista pequenas e todos os itens devem ter a mesma quantidade de caracteres. Por exemplo:<br /><br /><span style="font-family:verdana;font-size:85%;">=EXT.TEXTO("JAN;FEV;MAR;ABR;MAI;JUN;JUL;AGO;SET;OUT;NOV;DEZ";5*(3+1)-3;3)<br /></span><br />Neste caso, a fórmula retorna "MAI" correspondente ao item 5 informado. E o 5 poderia ser substituído por MÊS(A1) considerando que na célula A1 existe uma data válida.<br />No entanto, se você possui uma lista cujos itens possuem tamanhos diferente, pense na possibilidade de igualá-los com espaços e suprimir estes espaços após o resultado, como abaixo:<br /><br /><span style="font-family:verdana;font-size:85%;">=ARRUMAR(EXT.TEXTO("Verde ;Vermelho;Azul ";A1*9-8;8))</span><br /><br />A função ARRUMAR retirar os espaços à esquerda e à direita de um texto. É a tradução do Excel para a função TRIM.Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com4tag:blogger.com,1999:blog-2725974752633111642.post-64679171540603857962009-06-01T19:36:00.001-03:002009-06-01T19:39:12.065-03:00Método Evaluate<div>O método <em>Evaluate</em> dos objetos <em>Workbook</em>, <em>Worksheet</em> e <em>Chart</em> é uma funcionalidade que pode reduzir drasticamente seu código e o tempo de resposta da sua aplicação.</div><div> </div><br /><div>A principal característica desse método é que ele retorna o resultado de uma fórmula assim como a digitamos diretamente na planilha. Por exemplo, imagine que você queira saber quantas vezes aparece a palavra "São Paulo" em uma determinada no intervalo <strong>A1:A200</strong>. Você provavelmente digitaria em uma célula uma fórmula como:</div><div> </div><br /><div><span style="font-family:courier new;font-size:85%;">=CONT.SE(A1:A200;"São Paulo")</span></div><br /><div> </div><div>No entanto, se você precisa desse resultado no código, sem o <em>Evaluate</em> você pode escrever:</div><br /><div> </div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. </span>Dim I As Integer</span></div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. </span>Dim intQuant As Integer</span></div><br /><div><span style="font-family:courier new;font-size:85%;"></span> </div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. </span>For I = 1 To 200</span></div><div><span style="font-family:courier new;font-size:85%;"><span class="Apple-style-span" style="color: rgb(255, 255, 255); ">. . <span class="Apple-style-span" style="color: rgb(0, 0, 0); ">If Plan1.Cells(I, 1) = "São Paulo" Then</span></span></span></div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . <span class="Apple-style-span" style="color: rgb(0, 0, 0); "><span class="Apple-style-span" style="color: rgb(255, 255, 255); ">. <span class="Apple-style-span" style="color: rgb(0, 0, 0); ">intQuant = intQuant</span></span></span></span></span></div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . <span class="Apple-style-span" style="color: rgb(0, 0, 0); ">End If</span></span></span></div><div><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. </span>Next I</span></div><br /><div> </div><div>Com o <em>Evaluate</em>, você apenas digitaria:</div><div> </div><br /><div><span style="font-family:courier new;font-size:85%;"> Dim intQuant As Integer</span></div><div><span style="font-family:courier new;font-size:85%;"></span> </div><div><br /></div><div><span style="font-family:courier new;font-size:85%;">intQuant = Plan1.Evaluate("COUNTIF(A1:A200, ""São Paulo"")")</span></div><br /><div> </div><div>A fórmula é exatamente a mesma, mas tem que usar a sintaxe em inglês. Se não sabe como é o nome da função em inglês, digite a fórmula na planilha, selecione a célula com a fórmula e digite na janela imediata:</div><br /><div> </div><div><span style="font-family:courier new;font-size:85%;">?ActiveCell.Formula</span></div><br /><div> </div>Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com0tag:blogger.com,1999:blog-2725974752633111642.post-10290512165769536282009-06-01T00:42:00.000-03:002009-06-01T00:42:58.059-03:00Propriedade ScreenUpdatingSe você tem uma macro que navega pelas planilhas, faz <em>scrow</em>, copia e cola, entre outras coisas, ao executá-la essas ações acontecem como se um usuário estivesse de fato executando cada etapa, mas, mais rápido, claro. Na maioria das vezes não se deseja visualizar toda essas ações, apenas o resultado final, pois, toda essa atualização de tela tem um efeito desagradável.<br /><br />Uma outra questão que deve ser observada é que a atualização de tela, como todo processamento gráfico, tem um custo significativo de processamento. Então, se vizualizar essas atualizações durante o processo não interessa, pode-se utilizar a propriedade ScreenUpdating do objeto Application no começo da macro, conforme abaixo:<br /><br /><span style="font-family:courier new;font-size:85%;">Application.ScreenUpdating = False</span><br /><br />Dependendo do tamanho da macro, o tempo de resposta diminui significativamente.Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com0tag:blogger.com,1999:blog-2725974752633111642.post-64661320021732609072009-05-27T15:20:00.005-03:002009-05-29T10:43:44.399-03:00Tamanho de uma PlanilhaFaça o seguinte teste:<div>1. Crie uma pasta de trabalho nova</div><div>2. Na célula A1 digite "A"</div><div>3. Na célula B1 digite "B"</div><div>4. Salve a pasta de trabalho</div><div>5. Veja o tamanho do arquivo criado</div><div><br /></div><div>Agora faça o seguinte:</div><div><div>1. Crie uma pasta de trabalho nova</div><div>2. Na célula A1 digite "A"</div><div>3. Na célula IV65536 (ou XDF1048576 no caso da última versão do Excel) digite "B"</div><div>4. Salve a pasta de trabalho</div><div>5. Veja o tamanho do arquivo criado e compare com o primeiro</div><div><br /></div><div>Como pode ver, apesar de os 2 arquivos terem os mesmos dados, a posição destes dados na planilha influencia seu tamanho e, consequentemente a segunda planilha fica mais pesada. Isso ocorre porque o Excel não tem mapeadas todas as células previstas em suas planilhas. O mapeamento ocorre até o limite utilizado. Ou seja, no primeiro exemplo, o limite é B1, 2 colunas e 1 linha que é igual a 2 células. No segundo exemplo, o limite é IV65536, 256 colunas e 65.536 linhas que é igual a 16.777.216 células mapeadas. E, claro, para fazer esse mapeamento, precisa-se de espaço em disco e em memória.</div><div><br /></div><div>É muito comum colocar alguns dados auxiliares "escondidos" em áreas fora do campo de visão do usuário. Pense como alternativa colocar essas informações em uma outra planilha e, se for o caso, ocultá-la. Além de ser uma solução mais elegante, a pasta de trabalho terá seu tamanho reduzido.</div><div><br /></div><div><br /></div><div><br /></div></div>Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com0tag:blogger.com,1999:blog-2725974752633111642.post-6129355521906618322009-05-26T18:21:00.004-03:002009-05-27T12:23:05.895-03:00Repetindo ValoresSe você quer entrar com um mesmo valor em várias células, selecione todas as células que você quer que tenha o mesmo valor, digite o valor desejado - apenas a célula ativa dentro da seleção será alterada - e, ainda com a célula em modo de edição, tecle Ctrl + Enter. Pronto!<div><br /></div><div>Isso é muito útil quando se quer repetir valores em intervalos não contíguos, em planilhas filtradas e quando se quer alterar os valores sem mudar a formatação. Nestes casos, se você clicar no canto inferior direto da célula e arrastar, terá problemas.</div>Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com2tag:blogger.com,1999:blog-2725974752633111642.post-74468989614596592572009-05-25T00:48:00.012-03:002009-05-26T03:13:37.309-03:00SuplementosSe você utiliza algum suplemento em sua planilha e tem que distribuí-la depois, você vai ter que informar ao usuário a necessidade habilitar esse suplemento para que a planilha funcione corretamente.<br /><br />Você também pode automatizar a habilitação do suplemento adicionado o código abaixo ao evento <span style="font-family:courier new;"><strong>Open</strong></span> do <span style="font-family:courier new;"><strong>Workbook</strong></span>:<br /><br /><span style="font-family:courier new;font-size:85%;">If Not AddIns(“Ferramentas de análise”).Installed Then<br /><span style="color:#ffffff;">.</span> AddIns(“Ferramentas de análise”).Installed = True<br />End If</span><br /><br />No entanto, se algum usuário utiliza o Excel em outro idioma, o suplemento não será encontrado, a menos que você faça a verificação no outro idioma também. Uma maneira de resolver isso é fazer a habilitação do suplemento a partir do nome da biblioteca - o arquivo DLL - mas nesse caso você vai ter que percorrer toda a lista de suplementos disponíveis em vez de fazer uma referência direta, como abaixo:<br /><span style="font-family:courier new;font-size:85%;"></span><br /><span style="font-family:courier new;font-size:85%;">Dim I As Integer</span><br /><span style="font-family:courier new;font-size:85%;"><br />For I = 1 To AddIns.Count</span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . </span>If AddIns(I).Name = "ANALYS32.XLL" Then</span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#000000;"><span style="color:#ffffff;">. . . </span>If</span> Not AddIns(I).Installed Then</span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . . . </span>AddIns(I).Installed = True</span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . . </span>End If </span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">. . . </span>Exit For </span><br /><span style="font-family:courier new;font-size:85%;"><span style="color:#ffffff;">.</span> End If</span><br /><span style="font-family:courier new;font-size:85%;">Next I</span><br /><span style="font-family:Courier New;font-size:85%;"></span><br /><span><span class="Apple-style-span" style="font-size: medium;">Para descobrir nome da biblioteca, na janela imediata digite:</span></span><br /><span style="font-family:Courier New;font-size:85%;"></span><br /><span style="font-family:courier new;font-size:85%;">?AddIns(“Ferramentas de análise”).Name</span><br /><span style="font-family:Courier New;font-size:85%;"></span><br />Importante! Os códigos apresentados <strong>não</strong> instalam os suplementos, apenas os habilitam! Para instalação, consulte o manual do fornecedor do suplemento.<br /><span style="font-family:Courier New;font-size:85%;"></span>Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com1tag:blogger.com,1999:blog-2725974752633111642.post-76568937588355782182009-05-24T02:06:00.000-03:002009-05-24T02:56:38.512-03:00Sistemas em Excel<div>Dependendo da empresa onde você trabalha, é bem possível que existam várias planilhas sem as quais a empresa não funciona. É um controle disso, um controle daquilo, listas, <i>check lists</i>, cadastros e por aí vai. Surgem como apenas mais um documento e um chefe gosta e no outro mês pede novamente <i>a</i> <i>planilha.</i> Com algumas alterações e mais alguns dados. Sugestões surgem, alguém que conhece um pouco mais adiciona uma tabela dinâmica, gráficos e até arrisca criar algumas <i>macros</i> que aprendeu em um livrinho que comprou na banca de revistas.</div><div><br /></div><div>O circo está armado! A planilha criada sem grandes pretensões faz parte agora do processo da empresa. De fato, virou um sistema, muitas vezes com regras de negócio bastante intrincadas e uma salada de soluções técnicas desenvolvidas por alguém cheio de boas intenções mas sem nenhuma experiência com programação, estrutura de dados e etc..</div><div><br /></div><div>Há quem diga que Deus fez o mundo usando o Excel. Por isso o fez rápido. Mas, como tudo que é feito em Excel, dar manutenção não é fácil.</div><div><br /></div><div>Recentemente fui a uma grande instituição financeira para <i>dar uma olhada</i> em uma dessas planilhas. A dita já existia há 5 anos, tendo sido alterada durante todo esse tempo por diversas pessoas. Tudo que se pode imaginar de recursos do Excel foi utilizado. A planilha gera diversas outras planilhas que são enviadas para mais de 100 empresas em diversos países. E, da <i>olhada</i> surgiu um cronograma de manutenção com vários meses de trabalho. Como ela surgiu? Como apenas mais um documento que o chefe gostou e... Bem, você já sabe.</div><div><br /></div><div>Então, se você conhece bem esta história ou, principalmente, se você se viu na ingrata tarefa de dar manutenção em uma destas planilhas, este blog vai ajudá-lo com dicas, melhores práticas e é também um espaço para a troca de ideias e experiências.</div><div><br /></div><div>Seja bem vindo!</div><div><br /></div><div><br /></div>Evangelo Sacchi Rezendehttp://www.blogger.com/profile/12859790422459005746noreply@blogger.com0