Macros

Macros p/ Excel I) Introdução ................................................................................. .......................3 II) Conteúdo do Manual......................................................................... ...............7 III) Cópia Simples..................................................................................... ............8 III.1) Transformar seleção p/ valor (contígua) (Ctrl W)....................................8 III.2) Transformar seleção p/ valor (não contígua) (Ctrl Shift W)......................8 III.3) Copia fórmula / só fórmula / só formato (Ctrl Shift C / R / K )..................8 III.3.a) Busca da referência para cópia................................ ..........................8 III.3.b) Bloco de fórmulas............................................................ ...................8 III.3.c) Extensão da cópia........................................................................ .......8 III.3.d) Copia só no visível........................................................................... ...9 III.3.e) Features especiais............................................................ ..................9 III.3.f) Cópia p/ Direita..................................................................... ...............9 III.3.g) Só fórmula ou só formato......................................... ..........................9 III.3.h) Cópia Forçada................................................................................... ..9 III.4) Copia apenas fórmula (Ctrl R)........................................................... .......9 III.5) Copia Range (Ctrl Alt E).......................................................... ..................9 III.6) Copia Fórmula (Alt C)............................................................................ ....9 III.7) Copia Fórmula, sem = (Ctrl Alt C)......................................... ..................10 III.8) Copia fórmula p/ o lado (Alt V).............................................. .................10 IV) Movimentação e Seleção...................................................... ......................10 IV.1) Atalhos de movimentação de intervalos, linhas e colunas.....................10 IV.1.a) Movimento para cima e para baixo.................................................. .10 IV.1.b) Movimento para esquerda e para direita..........................................10 IV.1.c) Move seleção..................................................................... ...............10 IV.2) Vai ou Seleciona até a última linha / coluna (Ctrl Shift T)......................10 IV.3) Busca informação colunar (Ctrl F/ Ctrl Shift F) .................................... ..11 IV.4) Oculta/Agrupa linhas/colunas selecionadas (Alt G).................................12 IV.5) Busca palavras e ou frases (Ctrl J / Ctrl Shift J).................................... .12 IV.6) Procura erro (Ctrl Shift E).............................................................. ..........14 V) Formatação............................................................................................... .....14 V.1) Faz tabela de parâmetros (Ctrl E)...................................... ......................14 V.2) Faz tabela de dados (Ctrl D).................................................................. ..14 V.3) Faz título (Ctrl T).......................................................... ...........................15 V.4) Contorno de texto (Ctrl B)...................................................................... ...15 V.5) Coloca linha de cabelo (Ctrl Shift B).............................. .........................15

V.6) Ajusta Largura e Formato (Ctrl Shift L)................................................. ....15 V.7) Converte para porcentagem (Ctrl P).............................. ..........................15 V.8) Convertendo para número................................................................. ......16 V.9) Converte para inteiro c/ separação de milhar (Ctrl Z / Ctrl Shift Z ).........16 V.10) Formatação Mista (Ctrl Shift Q) ............................................................ .16 VI) Gráficos com um toque..................................................... ...........................17 VI.1) Gráfico Automático (Ctrl G / Ctrl Shift G)........................................... .....17 VI.1.a) Para programadores:........................................................... .............19 VI.1.b) Dicas........................................................................................ .........20 VII) Importação e Ajuste de Dados............................................................ .........20 VII.1) Importação de dados......................................................................... .....20 VII.2) Copia fórmulas para baixo (Ctrl Alt X)................................................. ..23 VII.3) Quebra texto na coluna corrente nas colunas da direita (Alt Q)............25 VII.4) Exclusão de Linhas / Colunas (Ctrl Alt D).............................................26 VIII) Ordenação de Bloco................................................................. ..................27 VIII.1) Ordenação (Ctrl O / Ctrl Shift O)....................................................... ....27 VIII.1.a) Introdução............................................................................... ........27 VIII.1.b) Botões de ordenação na planilha...................................................28 VIII.1.c) Proteção de linhas e colunas............................................... ...........28 VIII.1.d) Chaves Múltiplas....................................................... .....................28 VIII.1.e) Tabela blocada........................................................................... .....29 IX) Cópias Incrementadas............................................................... ...................31 IX.1) Copia visíveis (Ctrl Shift V)...................................................... ..............31 IX.2) Cópia com transformação (Ctrl Shift P).................................................31 IX.3) Layoutação para Tabela Dinâmica ...................................... ..................32 IX.4) Converte p/ tabular ............................................................................ ....34 IX.5) Cópia Heterodoxa ....................................................... ..........................36 IX.6) Cópia / Subst / Seleção de Formato & Cia (Ctrl Shift I).........................37 X) Transformações...................................................................... .......................39 X.1) Conversão de valor em fórmula (Ctrl Shift Y)..........................................39 X.2) Muda referência de Fórmulas (Ctrl Alt Y)...................................... ..........41 X.3) Ajeitando soma (Ctrl Shift M)............................................. ......................42 X.4) Põe Fórmula de Soma (Ctrl Shift S).............................. ..........................42 XI) Miscelânea.......................................................................................... ..........43 XI.1) Envia Tecla (Ctrl A / Ctrl Shift A)...................................... ......................43 XI.2) Põe Nome nas Fórmulas (Ctrl Shift N)................................................. ..44 XI.3) Vai para nome (Ctrl Alt N)............................................. ..........................44

XI.4) Operação com nome (Alt N)........................................ ...........................44 XI.5) Copia Nome Local (Ctrl Alt L )........................................................... ......45 XI.6) Valor e Tradução de Fórmula ................................................. ................45 XI.7) Mudar agrupamento.............................................................. ..................45 XI.8) Preparar para a impressão............................................................ ..........45 XII) Macros Diversas..................................................................... .....................45 XII.1) Porcentagem............................................................................ ..............45 XII.2) Problemas de conversão de linguagem.................................. ...............45 XII.3) Conversões..................................................................... .......................46 XIII) Funções ................................................................................. ....................46 XIII.1) Funções de Intervalo....................................................................... ......46 XIII.2) Funções diversas............................................................ ......................46 XIII.3) Função Vetor........................................................................... ..............47 XIII.4) Funções string........................................................................... ...........47

I)Introdução A planilha Macros contém diversas funções e macros, que podem contribuir muito para o aumento de produtividade em EXCEL. O autor dessa biblioteca é Paulo Buchsbaum ([email protected]) morador do Rio de Janeiro. 1 - Localização No EXCEL 97/2000 em português, geralmente, o diretório em que deve ficar o arquivo Macros é C:\ARQUIVOS DE PROGRAMA\MICROSOFT OFFICE\ OFFICE\XLINICIO. Particularmente no Windows 2000 e no Windows XP ele deveria ser configurado para armazenar o arquivo em um diretório similar, algumas vezes Office10 ou Office9. Em alguns micros, o diretório final é XLStart ao invés de XLInicio. 2 - Layout A primeira aba de Macros exibe um calendário onde pode se entrar diretamente o ano desejado ou clicar na seta ao lado do ano para andar de ano em ano, mostrando todos feriados, inclusive os móveis. Há links para exibir os feriados nacionais, americanos e outras datas especiais, incluindo o início das estações. Finalmente há um link para obter uma ajuda básica sobre as Macros. 3 - Cores Ele salva a paleta de cores modificada especialmente para Macros, para todos arquivos abertos, de modo a disponibilizar algumas cores, parecidas com as atuais, mas com mais opções de tons claros. O atalho Ctrl Alt K restaura a paleta padrão se a paleta do arquivo for personalizada e vice-versa. Em caso de arquivo externo que utilize muito tons escuros, às vezes é necessário usar a paleta padrão.

Abaixo as 16 cores novas que foram personalizadas em Macros com seu código RGB em lugar de outras 16 cores descartadas, extraídas da tabela de 40 cores principais selecionáveis interativamente de um total de 56 cores da paleta de cores do Excel • • • • • • • • • • • • • • • •

Bege (237,220,147) Verde Musgo (204,204,0) Verde Limão (241,254,118) Marrom (229,160,49) Rosa Escuro (255,145,182) Roxo Escuro (204,102,255) Goiaba (255,129,132) Amarelo Claro (255,255,204) Azul Céu (153,204,255) Roxo Claro (228,201,255) Laranja Claro (255,225,195) Rosa Claro ( 255,197,226) Cinza Claro (221,221,221) Verde Folha (97,215,128) Azul Cobalto (64,207,222) Verde Esmeralda (0,232,0)

4 - Salvamento automático Além disso, Macros tem o recurso de AutoSave que salva todas as pastas abertas, exceto as novas (que ainda não tem nome) e o próprio arquivo Macros. Esse processo é feito no intervalo de minutos especificado na célula I21 (Chamado de Minutos pertencente à Aba Help). Para desativar o Auto Save, preencha um 0 ou número negativos de minutos (aparece a mensagem Inativo) na mesma célula. Se número negativo só mantém o AutoSave na máquina do criador de Macros Macros permite também que se desative o AutoSave do arquivo corrente com Ctrl Alt S, que pode ser reativado mais tarde com o mesmo Ctrl Alt S. O arquivo com autosave desativado preserva esse estado mesmo saindo do Excel. Finalmente, ao fechar o Excel, o sistema não pede para salvar o arquivo Macros, mesmo que ele tenha sido alterado. (Só aceita salva explícita). 5 - Acesso a pasta Macros O Excel entra com uma pasta vazia (que é o comportamento normal de Macros), sempre que a execução não fizer menção a um arquivo específico. Para facilitar o excesso a Macros e seu calendário, o sistema disponibiliza Ctrl Q, para ir para Macros e a qualquer momento Ctrl Q volta a pasta original. 6 - Acesso aos comandos de Macros Todas as funções de Macros podem ser acessadas pelo menu disponível pelo atalho Ctrl Shift X. Esse atalho permite acesso a execução de macros internas a Macros ou externas através do uso de uma abreviatura, escolhida pelo usuário. A relação completa feita pelo usuário é acessível a todo momento seguindo-se o link situado na 2 a. coluna do cabeçalho da aba HELP. Operações sem atalho decorado (Ctrl Shift X) 1) Importa dados pondo fórmulas 2) Copia Formulas p/ tabela

3) Quebra texto em colunas 4) Deletar linhas com padrão 5) Vai para último 6) 1o. Valor Dif p/ baixo 7) 1o. Valor Dif p/ cima 8) Busca Palavras 9) Repete Busca 10) Põe Form & Larg L/C 11) Formatação Mista 12) Poe Fórmula de Soma 13) Calculadora Tabular 14) Muda Ref Form 15) Oculta / Agrupa 16) Copia Sofisticada 17) Subst Formato & Cia. 18) Subst Nomes Form 19) Vai p/ Nome 20) Atingir Meta 21) Copia endereço 22) Copia Visíveis 23) Outras Opções 24) Atalhos p/ decorar Opções com atalho para decorar 1) Copia para baixo 2) Cola Formula 3) Copia Form p/bx c/Formato 4) Copia Formato p/ Bx 5) Muda para valor 6) Muda p/valor s/ autofiltro 7) Aplica Borda Padrão 8) Aplica Caixa de Texto 9) Formato Tab Parâmetros 10) Formato Tab Dados 11) Formato Título 12) Formato Percentagem 13) Formato Inteiro 14) Formato em Milhares 15) Especifica e Faz Gráfico 16) Faz Gráfico 17) Especifica e Faz Ordenação 18) Faz Ordenação 19) Especifica e Faz Teclas 20) Faz Teclas 21) Trata Nome 22) Copia Fórmula p/ Clipboard 23) Copia Fórmula p/ Lado 24) Volta Opções incomuns (Ctrl Shift D) 1) Cor Personalizada On/Off 2) Auto-Save da Planilha On/Off 3) Valor de Fórmula e Tradução 4) Muda agrupamento esq/top

5) Busca Primeiro Erro 6) Copia Nome Local 7) Muda margem impressão 8) Copia Formula sem igual -----------------------------------9) Transf. colunar em Sub Total 10) Transf sub-total em colunas 11) Faz copia formula em diagonal 12) Conversão No.americano p/ Br 13) Conversão texto c/ No. Br para Br 14) Conversão de formato de data 15) Volta 7 - Execução de rotinas ou expressões O menu principal , acionado com Ctrl Shift X, permite a execução de expressões/ procedimentos / funções internos ou externos a Macros Isso é feito internamente no código pela função Evaluate para expressão (Quando funciona, o sistema emite a mensagem A expressão vale [ ...]) Quando não funciona o sistema tenta através do comando Run. (Aí a mensagem passa a ser O resultado é [ ]. A idéia é especificar diretamente a expressão/ procedimento ou função desejada e o sistema simplesmente exibe o resultado na tela. Sin (30) vira A expressão vale [0.5] 2+3 vira A expressão vale [5] UpperNoAc(“Alo”) vira A expressão vale [ALO] (Pasta Macros ativa ou .XLA) Macros.xls!UpperNoAc(“Alo”) vira A expressão vale [ALO] (Pasta Macros não ativa) No caso de uma expressão/procedimento/função de execução muito corriqueira pode valer a pena tabular através de uma abreviatura textual, a ser usada nesse contexto, e colocála no intervalo nomeado MacrosExt na aba Help de Macros. Pode-se inclusive misturar funções definidas pelo usuário com funções implícitas do Excel: UpperNoAc(“Alo”) & Trim(“ Mala”) devolve A expressão vale [AloMala] Outra forma (Internamente resolvido pelo comando Run) consiste em especificar o nome da rotina / função como habitual. Nesse caso, quando a única diferença é que a rotina quando está dentro de Macros, não precisa aparecer com o prefixo (Macros.xls) especificado. UpperNoAc(“Alo”) vira O resultado é [ALO]. Nesse caso, pode-se até, mas não se recomenda, adotar algumas liberalidades (omitir aspas em parâmetros tipo texto, separando a rotina dos parâmetros apenas por vírgulas) e ainda funcionar, pois é como o comando Run trabalha. Ao especificar uma rotina ou expressão, o menu o mantém na próxima execução. Uma utilidade direta é atribuir teclas a rotinas, temporariamente, para acelerar uma execução. Isso é feito a partir da rotina ATec evocada diretamente no Ctrl Shift X, ATec. Essa rotina exibe uma caixa de diálogo, onde especifica-se o atalho e o nome da rotina, separado por #, quando o atalho é especificado. Quando não é especificado assume-se que o atalho é Ctrl H. 8 - Semáforo

O Macros tem agora um sistema de semáforo, que é acionado no programa chamador de um comando de Macros, através do menu principal (Ctrl Shift X). Esse semáforo visa garantir a execução não-reentrante do código da programação do usuário, ou seja, garante que a execução do código do usuário se dará TOTALMENTE depois da execução completa da macro chamada pelo usuário dentro de Macros. Essa chamada em Macros deve ser feita usando o comando SendKeys. Macros se encarrega de desligar o semáforo estabelecido pelo usuário, liberando para continuação do código do programa principal. O desligamento representa o fim da execução do comando solicitado em Macros. Um trecho típico de programa que usa semáforo é descrito abaixo Run(“Macros.xls!Prende”) ‘ Liga o semáforo no programa principal Application.SendKeys(“^~x4~.~” ,True) ‘ Executa um comando de Macros Run(“Macros.xls!Solta”) ‘ Como testar o semáforo no programa principal Comandos ‘ Esses comandos só serão executados após o final da execução do ‘ comando solicitado de Macros em SendKeys. 9 - Personalização Para manter as customizações de padrões, cores e autosave para 1 máquina específica, existe um arquivo chamado de Pessoal.xls, que está em \\admfile1\compartilhar (para quem ainda não tem na sua máquina), que deve ser mantido no mesmo caminho de Macros. Quando aparece uma nova versão de Macros, a configuração armazenada em Pessoal.xls a sobrescreve, mantendo assim a configuração pessoal do usuário. Uma vez instalado, o arquivo Pessoal.xls pode ser acessado para customização do próprio Macros, clicando-se no botão Pess, situada no topo da aba Help. O arquivo Pessoal.xls inclui, entre outras coisas, toda a programação de teclas e macros externas personalizado para cada usuário. Dentro de Pessoal.xls, altere e salve clicando Salva Opções, ou saia sem salvar clicando em Fechar sem Salvar 10 - Personalização dos Atalhos. Na aba TEC do arquivo Pessoal.xls é possível especificar nomes de macros (correspondente ao nome real da rotina dentro do código VBA) e seu novo atalho a ser utilizado, usando a mesma convenção do comando SendKeys. Desse modo, o atalho normal (via Menu / macros ) é desligado e atribuído o novo atalho, através do comando ON KEY. Essa atribuição é feita ao se entrar em Macros e vale, naturalmente, apenas para o microcomputador onde a alteração é feita. Um exemplo é usar Num0 e especificar %z (ALT Z) como atalho, liberando o Ctrl Z para sua função habitual (Undo), juntamente com o atalho Bksp.

II)Conteúdo do Manual Do item III ao XI estão descritas as macros de uso imediato, através de atalhos de teclado, que estão divididas nos seguintes grupos: Há diversas rotinas que não tem atalho de teclado para elas, por serem menos utilizadas. Estão descritas no item XII Finalmente há algumas funções descritas no item XIII, que são úteis para planilhas.

III)Cópia Simples III.1) Transformar seleção p/ valor (contígua) (Ctrl W) Transforma área selecionada de fórmula para valor. Não funciona quando o trecho tem linhas filtradas, mas a execução é muito rápida. A utilidade é tornar a planilha mais rápida, além de “fixar” resultados de cálculos, em uma dada situação. Esse comando, bastante utilizado, economiza um click de mouse (se o usuário tiver habilitado o botão de cópia de valor)

III.2)Transformar seleção p/ valor (não contígua) (Ctrl Shift W) Transforma seleção para valor, apenas sobre relativo às células visíveis. A vantagem desse opção sobre a anterior, é que atua em trechos de linhas filtradas, mas a execução é mais lenta. Um exemplo de utilização é copiar fórmulas em tabelas com autofiltro para o lado. Não tem como fazer sem macro, a não ser manualmente.

III.3)Copia fórmula / só fórmula / só formato (Ctrl Shift C / R / K ) Com essa macro, fica bem ágil preencher fórmulas e/ou formatos na vertical e na horizontal de uma planilha. Vamos inicialmente assumir cópia para baixo. III.3.a)Busca da referência para cópia Copia as fórmulas das linhas e colunas selecionadas, para baixo, até o final da área de dados. Tenta-se determinar pelo contexto a coluna base. Inicialmente assume-se a coluna à esquerda. Se não for possível, tenta-se a coluna da direita. Caso ainda não funcione, tenta a própria coluna. Se ainda assim não funcionar, seleciona a coluna que esta preenchida, mesmo com buracos, até a maior linha. III.3.b)Bloco de fórmulas Se várias linhas e colunas são marcadas, a seleção é considerada um bloco de fórmulas a ser copiada para baixo. (com exceções que serão mais tarde explicadas) Pode-se copiar um bloco de fórmulas contíguos ocupando várias linhas, nesse caso, estende-se o bloco para baixo. A cópia, nesse caso, pode ultrapassar a linha máxima da coluna base, a fim de não “cortar “ o bloco. III.3.c)Extensão da cópia Geralmente copia pelo segmento contínuo (sem intervalos em branco) de dados da coluna usada como base. No entanto, se o usuário deixa a última linha em branco da área marcada, copia até a última célula preenchida da coluna base, independente de haver células em branco no meio.

III.3.d)Copia só no visível Atenção: Copia só para a parte visível deduzida a partir do contexto, sendo assim o comando é apropriado para se usar em Subtotais e Autofiltros. III.3.e)Features especiais Outra opção é fazer uma seleção de várias colunas onde os extremos são não vazios e há alguma célula vazia no meio. (referente à primeira linha) Nesse caso copia as colunas não vazias da direita baseada na primeira coluna da esquerda. Caso a referência seja realmente confusa, é possível marcar 2 áreas: primeira marca-se a célula base única e, a seguir, marca-se a área de fórmulas a ser copiada. Nesse caso, a coluna da célula única da 1a. área é a coluna base! III.3.f)Cópia p/ Direita Essa macro permite também copiar fórmulas para direita: A maneira mais simples, é quando se marca uma área de mais de 2 colunas, com uma ou mais linhas, quando a última coluna esteja em branco. Nesse caso, o Excel assume cópia para direita. Se além da coluna adicional, usado para indicar cópia por linha, tiver mais uma coluna em branco, copia para direita, pela linha de referência, até a última coluna utilizada, mesmo baseado em dados não contíguos. Outra maneira de assumir como cópia para a direita é usando marcação de 2 áreas, quando a coluna da 1a. célula coincidente com a primeira coluna da 2a. área (de fórmulas), se essa célula estiver em uma linha não vazia. Esse convenção tenta adivinhar a intenção do usuário, de copiar para o lado, ao invés de copiar para baixo, já que uma referência da cópia de uma coluna nela mesma, não é o processo mais comum. III.3.g)Só fórmula ou só formato Ctrl Shift R faz a mesma coisa que o Ctrl Shift C, só que mantendo o formato anterior. Ctrt Shift K faz a mesma coisa que o Ctrl Shift C, só que copia apenas o formato. III.3.h)Cópia Forçada Para encerrar, existe uma maneira de forçar que o sistema enxergue uma determinada linha e coluna limite para cópia, através do uso dos nomes CCOPMAC para a coluna desejada e LCOPMAC para a linha desejada, apenas na aba corrente! Tome o cuidado de apagar esses nomes assim que terminar seu uso. Note que esse nomes prevalecem sobre tudo. Assim se CCOPMAC está definido e LCOPMAC não está, mesmo sem seleção, a macro copia para direita.

III.4) Copia apenas fórmula (Ctrl R) É uma operação bem corriqueira mas não tem sequer um botão na personalização no Excel Interativo. Copia as fórmulas s/ a área apontada sem sobrescrever o formato anterior. Pode-se colar o mesmo conjunto de fórmulas em vários lugares e terminar com ESC

III.5)Copia Range (Ctrl Alt E) Copia o endereço (intervalo) ligado à área selecionada. Se tiver nome, utiliza-o. Pode ser usado com Ctrl V em qualquer contexto.

III.6)Copia Fórmula (Alt C) Copia fórmula que está no canto superior esquerdo da seleção, ou simplesmente, a célula corrente. Pode ser usado com Ctrl V em qualquer contexto.

III.7)Copia Fórmula, sem = (Ctrl Alt C) Copia fórmula que está no canto superior esquerdo da seleção, ou simplesmente, a célula corrente, cortando o igual no início da fórmula. A utilidade é usar a fórmula para compor uma fórmula mais complexa.. Pode ser usado com Ctrl V em qualquer contexto.

III.8)Copia fórmula p/ o lado (Alt V) Copia fórmula da célula corrente para direita, sem alterar as referências. Pergunta se deseja copiar caso a célula alvo já esteja preenchida. Se alterar “COP FORM DIR” para N faz essa mesma ação para baixo.

IV) Movimentação e Seleção IV.1)Atalhos de movimentação de intervalos, linhas e colunas (Ctrl Alt Cima, Ctrl Alt Baixo, Ctrl Alt Esquerda, Ctrl Alt Direita, Alt Cima, Alt Baixo, Alt Esquerda, Alt Direita)

IV.1.a)Movimento para cima e para baixo Movimenta as linhas (só se contíguas) envolvidas na seleção para cima (Ctrl Alt Cima) ou para baixo (Ctrl Alt Baixo) 1 posição. Não precisa selecionar a(s) linha(s) inteiras. Funciona mesmo com a célula corrente. Usando Alt Cima e Alt Baixo faz o mesmo mas só na região selecionada. IV.1.b)Movimento para esquerda e para direita Movimenta as colunas (só se contíguas) envolvidas na seleção para esquerda (Ctrl Alt Esquerda) ou para direita (Ctrl Alt Direita) 1 posição. Não precisa selecionar a(s) coluna(s) inteiras. Funciona mesmo com a célula corrente. Usando Alt Direita e Alt Esquerda faz o mesmo mas só na região selecionada. IV.1.c)Move seleção Movimenta a área selecionada para esquerda (Ctrl Alt Shift Esquerda), direita (Ctrl Alt Shift Esquerda ), para cima (Ctrl Alt Shift Cima) e para baixo (Ctrl Alt Shift Baixo). Serve como um perfeito complemento para o comando F8 que mexe com o tamanho da seleção, mantendo fixo o canto superior esquerdo.

IV.2)Vai ou Seleciona até a última linha / coluna (Ctrl Shift T) a) Quando há apenas 1 célula selecionada Vai para a última linha com dados de sua planilha. Ë o equivalente a um CTRL Seta, só que mais abrangente. É muito útil para se navegar em uma planilha, porque pula os “buracos” não preenchidos de uma planilha. b) Quando há mais de uma célula selecionada Estende sua marcação até a última linha com dados de sua planilha. Ë o equivalente a um CTRL SHIFT Seta, só que mais abrangente. Se uma célula estiver marcada, teremos uma coluna selecionada. Se um trecho de linha estiver selecionado, com o cursor a direita; teremos um retângulo selecionado. Se a linha toda que contém a célula estiver marcadas, o sistema marca todas linhas até o final.

Com 3 linhas marcadas para cima, ele estende a seleção uma linha após a área de dados, para pode introduzir a fórmula de soma, pelo comando correspondente. Há um modo de trazer para o extremo direito da linha corrente. Se é marcado apenas 1 trecho de 1 única linha, assume que o usuário deseje a última célula da direita, da seguinte forma: 2 células contíguas: vai até a última célula a direita da linha corrente 3 células contíguas: marca até a última célula a direita da linha corrente 4 células contíguas em diante: idem, com uma coluna adicional à direita Finalmente, é claro que se o usuário marcar uma coluna inteira, ele assume a marcação de colunas inteiras à direita. É muito útil para automatizar cópias e movimentos de seleções.

IV.3)Busca informação colunar (Ctrl F/ Ctrl Shift F) Há uma área de parâmetros na aba Help de Macros a partir aproximadamente da célula H6, sob a denominação Busca (Ctrl F – Ctrl Shift F). a) Quando há apenas 1 célula selecionada Busca, na coluna para baixo (Ctrl F) ou para cima (Ctrl Shift F) valor diferente do atual (se Dif = S, senão procura valor igual), selecionando-o. Útil para consultar um relatório ordenado por uma coluna, a fim de observar a diversidade de valores possíveis nessa coluna, dentre outras coisas. Ativo quando não há especificação S em Cor ou sinal de > ou < em Vdif %, como veremos adiante No caso da busca pelo diferente, quando a célula corrente é vazia aponta para a próxima não vazia. Se, o comando sendo para baixo, não há mais célula não-vazia, permanece onde está. Quando especificado valores não-nulos em Val Dif Abs e Vdif % da aba Help, busca um valor na coluna que tenha uma diferença absoluta maior que o valor dado (se especificada) e a diferença percentual (se especificada) maior que o valor dado. Se especificado o campo Esq, no caso de coluna de texto, exige que só esse número de caracteres a esquerda seja igual para considerar igual, o que é útil para percorrer planos de contas / hierarquias, onde coincida os primeiros algarismos. Esse último recurso, que equivale a uma igualdade aproximada, é muito útil para conferência da exatidão de fórmulas. Atenção: Para que o comando tenha a interpretação de busca aproximada é preciso Cor seja N. Features adicionais: •

•

Um sinal de > ou < em VDif%, dispara uma busca por um valor na coluna maior ou igual (>) ou menor ou igual(<) ao valor base. Isso é útil para pesquisar valor de exceção em um tabela que precisa estar ordenada de outro jeito. Filtrar seria uma solução capenga, pois não se consegue visualizar o contexto das linhas de exceção. Se especificado F/P/N em Form, o valor utilizado para comparação é a cor do caracter / cor de fundo/ negrito e não o valor da célula. Para exemplificar, Se MacFindDif = N e MacFindCor = F busca para baixo (Ctrl F) ou para cima (Ctrl Shift F) na coluna uma célula com a mesma cor de fundo que a célula corrente. Útil para permitir marcação de seleção com algum formato para posterior pesquisa com Ctrl F.

•

Quando se usa S na especificação MOV (MacFindMov), a base de comparação se torna móvel. Isso se pode tornar útil para buscar variações não desprezíveis em um campo numérico ordenado (MacFindDif =S) ou buscar duplicatas em um campo texto ordenado (MacFindDif = N). Atenção: o valor fixo é estabelecido a cada aplicação do comando!

b) Quando há mais de 1 célula selecionada Estende sua marcação para baixo (ou para cima), até a última (ou primeira) linha que tem o mesmo valor para coluna, de modo parecido com a opção IV-2-a. Se selecionado trecho, estende trecho. Se marcado linhas inteiras, estende seleção de linhas.

IV.4)Oculta/Agrupa linhas/colunas selecionadas (Alt G) Agrupa e desagrupa (cria tópicos) linhas e colunas, como especificado, a partir da célula corrente por contigüidade de dados para baixo (Se for por linha) ou para direita. Especificações: C – agrupa colunas. O default é agrupar por linhas X - Limpa tópicos antes de prosseguir. Quando não há outra especificação só limpa. Tnn – Agrupa todas linhas / colunas que tem tamanho / valor maior ou igual ao tamanho /valor dado. Usado especialmente para planos de contas, onde contas de maior comprimento tem estar agrupadas em cima das contas de menor comprimento. Usado ainda para colunas com número do nível hierárquico. B – Agrupa todas linhas/ colunas que estão em branco. Útil para agrupamentos onde só o primeiro dado está presente e os filhos tem o valor na mesma linha / coluna vazio e precisam estar agrupados contra a linha/ coluna preenchida. Muito útil em agrupamentos de colunas, onde a primeira coluna de cada grupo, tem um título que não se repete nas outras colunas, mesmo parecendo centralizado. Ao usar a opção B delimita pela área corrente e não pela contigüidade da linha /coluna Gnn – Agrupa cada nn linhas/ colunas. Útil para informações blocadas, por exemplo venda, estoque e cobertura em diferentes meses. Com nn = 2, agrupa o estoque e cobertura contra a venda, deixando apenas 1 coluna (a de venda) para cada mês. =nn – agrupa usando o critério de igualdade das primeiros especificado assume igualdade completa. Aplicação: quando tem primeiras posições coincidentes denota um nível de hierarquia. agrupa iguais sob linha anterior, mas só agrupa com um mínimo de especificada sem a opção = assume igualdade total.

nn caracteres. Se nn não um dado hierárquico, as Modificador: opção * que 2 ocorrências. Se opção *

Vnn – Após o agrupamento G , especifica que nn linhas / colunas serão deixada visíveis após o agrupamento. O default é 1.

IV.5)Busca palavras e ou frases (Ctrl J / Ctrl Shift J) Ctrl Shift J busca palavras / frases na coluna corrente da planilha (se não há seleção) ou na área selecionada (se há seleção) - Ctrl J repete a última busca com as mesmas especificações, pulando a célula atual, por praticidade. Basta entrar com 1 ou mais palavras / frases separadas por espaços. Frases devem ser limitadas por aspas, para incluir 2 ou mais palavras, separadas por espaços. Cada uma das palavras ou frases especificada podem ser precedidas por um prefixo que modifica a forma que a pesquisa é feita.

~ - Negação da palavra ou frase. Se não há outro prefixo, significa apenas que a palavra / frase especificada NÃO pode ocorrer em nenhum lugar da célula. A palavra / frase pode ainda ser precedida por um dos prefixos abaixo, inclusive depois da negação, se for o caso. < - Exige que a palavra ou frase ocorra no início da célula. > - Exige que a palavra ou frase ocorra no final da célula. ( – Exige que a palavra ou frase seja antecedida por espaço ou ocorra no início da célula. ( – Exige que a palavra ou frase seja sucedida por espaço ou ocorra no final da célula. = Exige que a palavra ou frase ocorra independente, sem mesclar com outros caracteres. / - Faz o próximo caractere ser interpretado literalmente, o que é uma forma de fazer com os comandos acima seja interpretados como parte da frase / palavra Ex: ~< BANANA - banana não deve ocorrer no início de uma célula. Além disso aceita-se ~ após o prefixo. Nesse caso o ~ nega apenas o prefixo, mas ainda exija que a palavra / frase ocorra na célula (~ - Exige que a palavra ou frase ocorra , mas não no início de uma palavra. )~ - Exige que a palavra ou frase ocorra, mas não no final de uma palavra. <~ - Exige que a palavra ou frase ocorra, mas não no início da célula. >~ - Exige que a palavra ou frase ocorra, mas não no final da célula. =~ - Exige que a palavra ou frase ocorra, mas não independente. Há o caso complexo, onde há dupla negação. Ex: ~<~, que significa que não é verdade que a palavra ocorra fora do início de uma célula. Assim se a palavra ocorra no meio é verdadeiro e portanto não considera. Mas se a palavra aparecer no início ou simplesmente não aparecer, o resultado é OK! Além disso, permite acionar o filtro de espaços excessivos, acentos e caracteres de controle, para facilitar a busca. Há 2 opções de busca: uma opção é mais estrita (exige que todas palavras / frases estejam presentes na célula) ou mais liberal (exige que pelo menos 1 das palavras / frases esteja presente no texto buscado na planilha) . O Ctrl J repete a direção adotada em Ctrl Shift J. Pode-se ainda optar, marcando-se, que se busque por fórmula e não por valor da fórmula. Há a opção de fazer a busca para frente ou para trás, conforme o botão pressionado. Se há uma área selecionada, ela é usada para limitar a busca na coluna. Caso não há área selecionada, faz a busca até o último valor preenchido na coluna (independente de ter células em branco no meio), se for para a frente, ou até a primeira linha, se a busca for para trás. Ex 1: amor felicidade - Busca linhas que contenham "amor" e "felicidade" ao mesmo tempo Ex 2: "amor de mãe" "felicidade" ao mesmo tempo

felicidade - Busca linhas que contenham "amor de mãe" e

Aceita espaços livremente na especificação da busca, exceto nas frases limitadas por aspas, caso em que exige que os espaços sejam respeitados.

Não é sensível a maiúsculas e minúsculas. Aceita busca em 2 colunas simultâneas ao marcar o check box BUSCA COLUNA DUPLA. Nesse caso é preciso entrar com novas especificações independentes para a 2 ª coluna, além do próprio número da coluna, uma vez que a coluna da 1a busca é a coluna corrente. Repare que nesse caso para considerar que achou o alvo, é preciso que as 2 condições sejam atendidas para suas colunas respectivas. Ex 3: ~campeão (Coluna 1 corrente) cidade (Coluna 2) Procura uma linha na planilha que não tenha a palavra campeão na coluna 1 da planilha e que tenha uma palavra iniciada por cidade na coluna 2 da planilha. Quando toda a planilha é selecionada o processo não deixa de ser eficiente pois só busca na área útil da planilha.

IV.6)Procura erro (Ctrl Shift E) Iniciando na célula corrente busca a primeira ocorrência de erro (#Value, #N/D, etc.), para baixo na coluna corrente.

V)Formatação V.1)Faz tabela de parâmetros (Ctrl E) Formata uma tabela de 2 colunas, onde a primeira coluna são os rótulos (geralmente parâmetros) e a segunda coluna são os valores. Atua na área selecionada. Fornece uma boa apresentação instantânea para tabelas de parâmetros, onde o usuário depois apenas troca a cor de fundo da 1a.coluna, se necessário. Obedece ao formato da 1ª célula, se a cor de fundo não for branco. Senão obedece ao padrão, ao lado da ajuda para a função em Macros, se houver. Senão assume fundo amarelo forte, borda média e cor de fonte preta. Caso a cor do fundo à esquerda da configuração da cor seja colorida (diferente de cinza claro), mantém as cores das colunas já existentes, atuando mais na moldura.

V.2)Faz tabela de dados (Ctrl D) Formata uma tabela de dados, onde a primeira linha são os títulos das colunas. Atua na área selecionada. Fornece uma boa apresentação instantânea para tabelas de dados, onde o usuário depois apenas troca a cor de fundo do cabeçalho, se necessário. Para formatar uma tabela com títulos tanto no topo, como à esquerda, use CTRL E, seguido de CTRL D, a partir da 2a. coluna da área desejada. Seta modo de quebra de linha, para a parte de título. Obedece ao formato da 1ª célula, se a cor de fundo não for branco. Senão obedece ao padrão, ao lado da ajuda para a função em Macros, se houver, incluindo a questão de centralização dos títulos das colunas. Senão assume fundo laranja, borda média e cor de fonte preta, títulos a esquerda. Caso a cor do fundo à esquerda da configuração da cor seja colorida (diferente de cinza claro), mantém as cores das colunas já existentes, atuando mais na moldura.

V.3)Faz título (Ctrl T) Centraliza, põe negrito e coloca fundo no trecho de linha selecionada, de forma a que o conteúdo da célula à esquerda seja um título. Seta modo de quebra de linha. É útil para criar um título instantâneo. Obedece ao formato da 1ª célula, se a cor de fundo não for branco. Senão obedece ao padrão, ao lado da ajuda para a função em Macros, se houver. Senão assume fundo verde claro, borda média e cor de fonte preta.

V.4)Contorno de texto (Ctrl B) Coloca borda externa na área selecionada, tirando qualquer bordas interna. Útil para caixa de texto, etc. Obedece ao formato da 1ª célula, se a cor de fundo não for branco. Senão obedece ao padrão, ao lado da ajuda para a função em Macros, se houver. Senão assume fundo verde claro, borda média e cor de fonte preta.

V.5) Coloca linha de cabelo (Ctrl Shift B) Coloca linha de cabelo interna e externamente em toda área selecionada. Útil para dar mais plasticidade a cor nas planilhas. Na verdade, ele copia o estilo de borda de uma área rosa do lado da janela HELP do Macros em um retângulo rosa. Assim, pode-se configurar a borda nesse pedaço livremente, para poder fazer bordas mais complexas.

V.6)Ajusta Largura e Formato (Ctrl Shift L) Ajusta a largura e formato das colunas ou linhas da área corrente que seleção, seguindo o padrão das N primeiras colunas ou linhas da seleção, sendo N informado pelo usuário. Se N é antecedido ou sucedido por H o processo é feito com as linhas, se não tiver especificação ou usa-se espaço, V ou vírgula, o processo é feito com as colunas. Se não há seleção ou a seleção inclui só uma coluna ou linha (H), expande a seleção para atingir a tabela para qual o cursor está apontando. Útil para grupos de colunas ou linhas (H) que se repetem, mas que tem diferentes necessidades de largura e de formato. Se depois do H,V,.. vier um outro número, a interpretação é diferente, o primeiro número passa a ser o número de colunas / linhas ignoradas para efeito de execução desse comando e o segundo número é que indica as colunas / linhas básicas a serem usadas para cópia de formatação de colunas / linhas. Útil para colar formato para a linha de baixo, por exemplo. Ver exemplo em Envia Tecla (Ctrl A / Ctrl Shift A) Atenção: Ele preenche minimamente as extensões, se vazias, para permitir o Ctrl * (Seleção corrente) funcionar tanto no sentido horizontal como vertical.

V.7)Converte para porcentagem (Ctrl P) Converte seleção para porcentagem com 2 casas É a forma mais comum de porcentagem, poupando 3 cliques. Pode-se especificar o número de casas desejadas, do lado do comando em Macros

V.8) Convertendo para número Acessível pelo menu em Ctrl Shift D As vezes importa-se dados defeituosos onde parte é número e parte é texto, contendo ponto como vírgula, espaço espúrios e separadores de milhares como vírgula. Essa macro é útil para ajustar isso. Há 2 opções. Se o número esteja em formato americano, escolha a opção de conversão de americano para brasileiro. Se o número estiver em formato brasileiro, mas com espaços extras ou texto após o número, escolha a opção de conversão de formato brasileiro com problemas.

V.9)Converte para inteiro c/ separação de milhar (Ctrl Z / Ctrl Shift Z ) Formata números, geralmente sem casas decimais e separador de milhar. É uma das formas mais comuns de formato de número, poupando 3 cliques. Pode-se especificar o uso (.) ou não do separador de milhares seguido do número de casas decimais, do lado do comando em Macros, como, por exemplo, .2 (Usa separador de milhares com 2 casas decimais) Outra opção, que exibe os números em milhares (Ctrl Shift Z), forma muito comum de exibir dados numéricos grandes. Abaixo também há uma especificação de formato padrão, igual ao anterior, mas podendo ser sucedida pelo número de vírgulas desejados (, milhares e ,, milhões) Formato: [.][n][,]* onde . opcional, indica separação de milhares. Default: Não n opcional, número de casas decimais. Default: 0 , opcional 0 ou mais vezes. Default: 1

V.10)Formatação Mista (Ctrl Shift Q) Formata a área selecionada (expandindo para área corrente se não houver seleção) contendo números e percentagens simultaneamente. Pede em uma entrada simples uma especificação de formato “NumPerc”, onde Perc é o número de casas dos valores que são percentagens e Num é o número de casas decimais do número, que pode ser precedido ou não de M (caso em que é feito a separação de milhares). Ponto(>) após um formato (número ou %) dá um veto para formatar o outro tipo (% ou número). Formato:

[M ou .] n [P m]

Onde M – Exibe números em milhares . – Exibe vírgula de 3 em 3 casas n – Número de casas decimais (Default=2) P – Prefixo de formato percentagem M – Número de casas decimais da percentagem (Default=0) [ ] – Opcional > Finaliza formato, não formatando os valores cujo formato não foi especificado. Ex: .12 - Formata as percentagem com 2 casas e os números com 1 casa e separador de milhares.

Comando muito útil para formatar planilhas complexas já formatadas preliminarmente, com mistura de números e percentagens. Sem essa macro poderia dar muito trabalho formatar uma planilha.

VI)Gráficos com um toque VI.1)Gráfico Automático (Ctrl G / Ctrl Shift G) Faz diversos tipos de gráficos, conforme a seleção. O gráfico pode ser orientado por seqüências horizontais (assumido como padrão, exceto se for gráfico de torta ou dispersão XY) ou orientado por seqüências verticais. A descrição abaixo refere-se aos gráficos c/ seqüências na horizontal, mas vale p/ as seqüências verticais (desde que se leia linha como coluna e vice-versa) Quanto a seleção feita: a) Célula corrente (!) Faz o gráfico da série da linha corrente, estendido p/ toda a área contígua de dados, buscando os rótulos e o título da série selecionada, mesmo que haja linhas superiores ou colunas à esquerda em branco. b) Todas linhas com 1 coluna Faz gráfico das séries indicadas pelas linhas selecionadas, com o mesmo critério acima. c) 1 linha com mais colunas, outras linhas com 1 coluna Faz gráfico das séries indicadas pelas linhas selecionadas, só que a “largura” do gráfico é referente a linha com mais de 1 coluna d) Várias linhas com mais de 1 coluna. Usa o critério c, onde o que vale é a primeira linha com mais de 1 coluna. Se o usuário teclar Ctrl G o sistema não pergunta nada e gera o gráfico, com a última especificação utilizada. No caso de já ter um gráfico gerado, vários formatos que foram configurados pelo usuário são aproveitados. Se o usuário teclar Ctrl Shift G, o sistema pede a especificação do gráfico que tem o seguinte formato ( todos itens separados pelo símbolo “/” ): Nome da aba/ Tipo e Orientação/Título/Legenda do Eixo_X/Legenda do Eixo_Y Se o usuário deixar o Nome da Aba vazio, o sistema criará automaticamente uma nova aba para inserir o gráfico. Caso o usuário queira fazer diversos gráficos rascunhos, preencha um nome rascunho qualquer e não o altere. Finalmente o nome @ irá indicar o desejo de colocar o gráfico na própria planilha corrente. Tipo e Orientação (até 2 caracteres) pode ser Tipo: B- Gráfico de Barras, L - Gráfico de Linha, (Default) D - Gráfico de Dispersão (X-Y) (Assume dados na vertical) (Uma série são valores de X, que definem o eixo horizontal e as outras são plotadas no eixo verical, a primeira delas geralmente apelidada de Y) T - Gráfico de Torta (Assume dados na vertical ) O – Gráfico de Torta 3D (Assume dados na vertical)

Orientação: H- Gráfico p/ dados horizontais (Default, exceto para Torta) V- Gráfico por dados verticais Combinações possíveis: B,L,D,T (Barra; Linha, Dispersão na horizontal e Torta na vertical) BV,LV,DV,TH (Os mesmos na vertical e Torta na horizontal) Os valores Titulo, Legenda do Eixo_X e Legenda do Eixo_Y são opcionais, não podendo, no entanto, definir um dado mais a direita sem definir o dado mais a esquerda (Por ex., não é possível definir o Eixo_Y, sem definir o Eixo_X) Ex:

T/Produção de Arroz BV/Produção de Arroz/Ton

- Torta da produção de arroz - Barras c/ série na vertical da produção de arroz

O parte da especificação que não é o nome da aba (Tipo, títulos e eixos) é armazenada no próprio gráfico, no cruzamento (no topo esquerdo) entre a linha topo e a coluna esquerda da área do gráfico, sob a forma de comentário, permitindo que a tabela origem de dados do gráfico conheça automaticamente o gráfico que deve estar associado a ele. Quando não se deseja usar o cruzamento dos rótulos com o cabeçalho para armazenar as características do gráfico, é preciso chamar uma célula da planilha ativa de GrafAba, e aí ela conterá a especificação do gráfico. Em geral os rótulos do eixo estão na linha topo da área de dados e a legenda corresponde à coluna mais a esquerda da área de dados, dependendo do tipo de dado e do tipo de gráfico. (Para gráficos orientados por linha) A utilidade dessa função é enorme, porque o trabalho de selecionar a área para o gráfico fica bastante reduzido e tratamento das categorias e dos rótulos é totalmente automático. Foi introduzido um caráter dinâmico para o título e os eixos do gráfico. O sistema permite introduzir na especificação referências a planilha onde está sendo extraído o gráfico. #C2 (coluna 2, linha corrente do gráfico), #L4 (Linha 4, coluna corrente do gráfico) e # (referência a célula B2) Finalmente pode-se embeber no título comandos iniciados por ! (ponto de exclamação). Atualmente, há o comando “!H” que faz com que o gráfico seja exibido mesmo que os dados não estejam visíveis. Nesse caso é preciso usar todas especificações de nomes para a Macros poder localizar os dados. Isso permite usar o Ctrl G para fazer gráficos, sem obrigar o usuário a visualizar os dados de forma tabular. Vale apontar que o usuário facilmente pode alterar o tipo do gráfico e outros atributos, como em qualquer gráfico do Excel. Outro recurso, útil no caso de nem todas células de dados a serem plotados estiverem preenchidas, é delimitar que as legendas das séries e os rótulos dos pontos da série estão contido na linha chamada de LinTopo e na coluna denominada ColEsq, sendo esses nomes de linhas e colunas aplicados na planilha desejada com o atalho Ctrl F3. Para gráficos com séries horizontais, a coluna é o título da série e a linha contém os rótulos dos pontos de cada série. Para gráficos com séries verticais é o oposto. Pode-se usar adicionalmente ColEsqV e GrafAbaV que serão acionados no caso de uma seleção vertical de mais de uma célula. Ou seja, quando o usuário seleciona uma célula,

usa ColEsq e GrafAba fazendo o gráfico de uma dada natureza. Quando ele seleciona mais de uma célula, passa a usar ColEsqV e GrafAbaV Quando se seleciona uma única célula para cada série a ser plotada, há um outro recurso poderoso, as colunas denominadas CIniDad e ColDir (para gráficos com séries horizontais) e as linhas de nome LIniDad e LinBase (para gráficos com séries verticais), delimitam as células que compõe cada série. Nesse caso, o sistema usa a célula corrente mais como indicador da linha base (no gráfico por linha) ou coluna base (no gráfico por coluna). As cores das séries segue uma sequência diferente do automático e são armazenadas em Macros. Siga o link do gráfico na página de ajuda. Se o gráfico já está previamente gerado as configurações gerais são mantidas, inclusive relativo às cores das séries e da seleção do eixo (principal e secundário). Se nomes de célula (LinTopo e ColEsq) não especificados sempre assume como legenda / pontos do eixo sempre a coluna mais a direita da área corrente partindo da esquerda e a linha mais abaixo saindo do topo, que não seja um valor numérico. Se todos valores forem numéricos pula-se colunas / linhas em brancos, para chegar aos rótulos e legendas. No gráfico de dispersão X xY assume-se que o gráfico não contém rótulos de pontos. Ao se mudar o gráfico a partir do aparecimento de uma série de formato divergente (percentagem para número ou vice-versa), o sistema muda o tipo do eixo, automaticamente. Mesmo se o usuário mudar uma série do eixo primário para o eixo secundário, o sistema conserva essa característica para os próximos gráficos (com o mesmo número de séries). Para manter mais de um gráfico ativo na mesma aba, basta especificar no título o nome do gráfico (após o sinal de ^). Assim o sistema sabe qual gráfico utilizar para redesenhar. É possível até deixar a seleção do gráfico a ser feita na própria planilha colocandose uma combo box, com a seleção do gráfico Há ainda um outro recurso que permite, quando há apenas uma série, acrescentar uma nova série de mesmo tamanho que a série corrente, situada na mesma linha (se gráfico é por linha, senão na mesma coluna), iniciando-se em uma coluna (ou linha, em gráfico por coluna) de nome AdicDadGr. O nome da série fica na mesma coluna (ou linha) na linha do cabeçalho. Issó útil, por exemplo, para particionar dados de uma série de 24 meses em 2 séries distintas com 12 elementos cada. Resumindo, há muitas situações em que bastará o usuário posicionar o cursor em uma célula qualquer. do gráfico desejado e clicar CTRL G para obter o gráfico desejado. VI.1.a)Para programadores: Em um pasta que se deseja fazer um gráfico, basta armazenar o nome de uma função em qualquer célula dessa pasta, a qual deve ser dado o nome de ProcGr. Assim, antes da execução do gráfico o sistema irá chamar essa rotina, fazendo alguma eventual preparação, por exemplo, testando se a origem selecionada é válida. Caso essa função retorne Falso não aciona o gráfico. Um exemplo clássico é mostrado abaixo para ilustrar um exemplo de aba que o gráfico usa a parte oculta, delimitada por CiniDad, ColDir, eixo X em LinTopo e cabeçalho da série em ColEsq, para traçar o gráfico, mas que seria desejável ficar sempre oculta, a menos que o usuário explicitamente exibisse. Programa-se uma rotina, associada a aba de dados, associado a sua ativação, para ocultar os dados do gráfico, que não deseja se exibir, para ficar uma visualização mais sumarizada. Private Sub Worksheet_Activate() Dim Ce As Range Application.ScreenUpdating = False ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

Set Ar = Selection.Areas(Selection.Areas.Count) Set Ce = ActiveCell Do While Columns(Ce.Column).Hidden Set Ce = Ce.Offset(0, 1) Loop Ce.Select Application.ScreenUpdating = True End Sub Na aba do gráfico, associada a sua ativação. Private Sub Chart_Activate() Sheets(“.....").Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 End Sub Precisa se exibir os dados para fazer com que o gráfico apareça. Finalmente na pasta coloca-se o código, que exibe os dados ao se gerar o gráfico com Ctrl G. Ele executa a função PP indicada pela célula de nome ProcGr. Public Function PP() As Boolean ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 PP = True End Function VI.1.b)Dicas •

•

Não use rótulos e legendas de série de tipo numérico. Pode ser número, mas tem que estar armazenado como texto. O sistema se confunde na hora de retraçar o gráfico. Ele espera gráfico ou texto. É claro que no caso de gráfico de dispersão o sistema assume que não existe rótulos. No gráfico de Dispersão é sempre necessário selecionar ambas séries de valores (x e y). É irrelevante ter ou não coluna / linha de texto (para orientação vertical / horizontal), pois ele não é utilizado no gráfico.

VII)Importação e Ajuste de Dados Essas operações são sem atalho e acessíveis apenas pelo menu (Ctrl Shift X) que trata principalmente com a importação ou reimportação de dados de planilhas ou sites na Internet. Como já foi falado, esse menu Ctrl Shift X dá acesso a algumas das operações mais comuns em Macros, com atalho mais difícil de decorar.

VII.1)Importação de dados Acessado pelo menu (Ctrl Shift X) Trata-se de uma importação de dados com “gabarito” de fórmulas. A partir de uma linha corrente formatada – template (cursor deve estar nessa linha) e com fórmulas em colunas chaves referindo-se às células do próprio template (não às células da aba origem). A célula selecionada no início do template funciona como delimitador do canto superior esquerdo da tabela, permitindo mais de uma tabela que recebe dados importados por aba. O sistema importa os dados de outra aba / pasta, tornando a planilha mais “larga”, após ficar recheada com as fórmulas especificadas.

Geralmente monta-se o cabeçalho desejado, já com as colunas nas posições certas! Finalmente apaga-se as partes das tabelas situadas abaixo das colunas marcadas com fundo preto. Digita-se o nome de uma aba ou pasta (contendo uma extensão, geralmente .XLS), que será trazida para baixo, formatada e com as fórmulas nas posições indicadas pelo modelo. Como explicado na própria janela de entrada, antes do nome da pasta pode haver algumas especificações em qualquer ordem • •

• • • • •

Asterisco (*) faz as fórmulas serem convertidas para valor. < N faz a importação se dar partir da linha N da origem (a linha 2 é default supondo uma linha de cabeçalho), por exemplo colocar 1 faz a importação ser feita a partir da linha 1, portanto dados de origem sem cabeçalho. Linhas maiores que 2 são úteis especialmente para importações de importações já tratadas, como em processos de sumarização, por exemplo. > N faz a importação se dar a partir da coluna N da origem. Útil especialmente quando se importa o importado para fazer sumarização, etc. @ faz com que não haja inserção na importação de dados e sim superposição dos dados. # faz moldura média em volta da tabela. | faz moldura fina em volta da tabela . (Ponto) copia apenas por número de coluna. Por exemplo 2 na coluna da linha de template traz para essa coluna dados da coluna 2 da origem, assim não tem necessidade de se especificar colunas da origem p/ excluir. A grande utilidade é fazer uma importação de poucas colunas de uma origem com muitas colunas de forma simples! (Continua com a possibilidade de inserir fórmulas no meio, referindo-se apenas às células que tiveram seus números incluídos. Ex: A B C [2 ] [1 ] [=A2+B2]

A especificação completa é guardada como comentário da 1a. célula do gabarito, que alimenta a próxima entrada de dados de importação, como sugestão, com as seguintes variáveis: • • • • • • • •

TIPO – FORMULAS implica que normalmente essa tabela é usada para renovação de fórmulas e não importação. Se o usuário confirmar esse tipo passará a ser MIXTA. VALOR – S quando converte para valor as fórmulas. Default = N BORDA – N (Sem borda), M(Média) e F(Fina). Default sem borda LINHA_ORIGEM – No. da linha na origem. Default = 2 COLUNA_ORIGEM – No. da coluna na origem. Default = 1 PASTA – Nome da pasta COPIA_POR_COLUNA – Se S copia pelo número da coluna. Default = N SOPREPOE – Se S sobrepõe o conteúdo anterior. Default = N

A linha base é também o gabarito do formato, mesmo para as colunas que não contenham fórmulas. Pode-se também usar esse recurso para mudar a ordem das colunas, colocando os dados primitivos mais a direita da área destino e puxando os dados na ordem desejada, por meio de uma fórmula simples de cópia. Dica: Quando não se especifica . (ponto) que é o modo de cópia por coluna, ainda pode se especificar uma coluna para copiar da origem por número, ao formatá-la como sublinhado no

template. Essa formatação especial NÃO é copiada para baixo. Repare porém que nesse caso essa coluna vai sobrepor o dado original que foi copiado de forma posicional. Imagine uma aba chamada Importa contendo

A

B

C

D

1

Val

Val2

Outro

Perc

2

2

3

4

0,5

3

1

2

3

0,4

5

4

1

3

0,2

5

7

8

1

0,5

Na outra aba, chamada Destino, monta-se a linha desejada (na linha 2), suponha que o layout da nova tabela já exista a partir da linha 4. Repare que está desejando se apagar a coluna referente a informação Outro.

A

B

C

D

E

F

=C2+D2

=F2

1

1

1

10%

4

Soma1

Soma2

Val

Val2

Outro

Perc

5

2

2

1

1

1

100%

1 2

♦

3

Aponta-se o cursor para a linha 2 da aba Destino e Tecla-se Ctrl Shift X e seleciona-se a opção 1 - Importa Dados mesclando com fórmulas O resultado é mostrado abaixo, hachurado em rosa. A

B

C

D

E

F

=C2+D2

=E2+F2

1

1

1

10%

4

Soma1

Soma2

Val

Val2

Perc

6

5 (=C6+D6)

4,5 (=E6+F6)

2

3

50%

7

3 (....)

3,4 (...)

1

2

40%

8

5

3,2

4

1

20%

9

15

1,5

7

8

50%

5

2

2

1

1

100%

1 2 3

Como já havia uma tabela preexistente abaixo do gabarito, o sistema colocou os dados entre o cabeçalho (suposto que já reflita o novo layout) e os dados anteriores já importados em outra ocasião. Se não houvesse nada abaixo, o sistema teria dado 2 “gabarito” e o resultado da importação.

linhas de espaço entre o

Abaixo algumas dicas de utilização: •Para fazer o processo de importação a partir do início é bem mais fácil fazer a linha Template de importação se você cola antes na linha template o cabeçalho da aba origem dos dados. Assim abre-se as colunas desejadas fisicamente, o que deixa as fórmulas mais fáceis de serem inseridas. Também fica intuitivo saber quem é preciso deletar, sem precisar ficar olhando para a aba do lado. Uma vez desenhado o template de importação, a linha de cabeçalho da origem pode ser deletada e então, dali para frente, especificar que a importação de deve dar a partir da linha 1. O cabeçalho usado como “cola” também pode ser deletado, por estética. •

•

Repare que se houver colunas que precisam ser usadas nas fórmulas no método de referencias as colunas pelo número (com prefixo .), é preciso referenciar explicitamente essa célula, para que ela possa ser usada em uma fórmula e depois especificar sua exclusão, pelo uso do fundo preto Se você quer manter as fórmulas na área importada, cuidado para não excluir (com fundo preto no template) as colunas que são citadas em fórmulas nas colunas a serem incluídas.

•Se você precisa procurar valores na própria tabela importada para fazer um “mixing” de informações de diferentes linhas, é preciso usar a técnica de nome de área (a partir de um Ctrl F3), tomando o cuidado de incluir a linha de cabeçalho na área, para o processo de importação não deslocar.

VII.2)Copia fórmulas para baixo (Ctrl Alt X) Copia as fórmulas contidas em uma linha informada para toda uma tabela situada abaixo, coluna a coluna. A primeira célula preenchida abaixo da corrente funciona é considerada uma célula da tabela. Sua expansão natural é a tabela, permitindo assim mais de uma tabela por aba. As fórmulas à esquerda da célula corrente também serão atualizadas. Isso só não acontece se for selecionado um bloco de 2 linhas, sendo a 1 a a linha que contém as fórmulas. Nesse caso só as colunas selecionadas é que terão suas fórmulas copiadas. Quando a tabela for obtida por importação, envolvendo exclusão de colunas, a linha de gabarito para cópia de fórmulas deve ser Diferente da linha da gabarito da importação, porque as colunas mudam de posição. Uma recomendação é colocar a linha de gabarito de importação colada a linha de gabarito de cópia. Funciona muito bem e não há interferência. Essa opção é muito útil para dar manutenção em uma planilha onde se deseja manter um mínimo de fórmulas para maior eficiência. Útil também em casos em que a planilha sofre acréscimos de linhas adicionais, onde não se deseja ficar com a preocupação de ficar copiando as fórmulas manualmente. Seleciona-se a linha que contém as fórmulas que devem ser copiadas para baixo na sua respectiva coluna. Note que a linha pode conter fórmulas esparsas, entremeadas por células vazias, uma vez que o processo se baseia na tabela de baixo! Pode-se selecionar 2 áreas, sendo a segunda correspondente a área destino da cópia, sendo é claro que só serão sobrescritas as colunas que contém fórmulas na linha template. Caso a segunda área seja apenas 1 célula, a seleção é expandida para a área corrente em volta. É claro que o cabeçalho da tabela é preservado!

A 1

B

=B1+D1

C

D

=A1*B1

2 3

23

42

11

8

4

31

22

14

12

5

11

41

15

15

Se na tabela acima apontarmos para o cursor para A1 ou C1, teclarmos Ctrl Shift X e escolhermos a opção 2 – Copia Linha de Fórmulas p/ Tabela temos o seguinte resultado, onde a região copiada é hachurada em rosa. A 1

B

C

D

=B1+D1

=A1-B1

3

50 (=B3+D3) 42

12 (=A3+B3) 8

4

34 (...)

22

12 (...)

12

5

56

41

15

15

2

Onde as áreas cinzas representam as fórmulas que estavam na linha 1. Uma tabela normal entremeando fórmulas e valores pode ser facilmente convertida para esse formato copiando-se a primeira linha de fórmulas e valores para a linha 1, por exemplo e depois transformando a tabela em valor selecionando-a e teclando-se Ctrl W. Guarda-se como comentário as especificações utilizadas para a cópia de fórmulas na 1a. linha do gabarito e dessa forma pode-se selecionar opções mais complexas. • • •

• •

TIPO: IMPORTA implica que normalmente essa tabela é usada para importação e não para renovação de fórmulas. Se o usuário confirmar esse tipo passará a ser MIXTA. FORMATO: S passa a colar o formato do gabarito. Default = N DEPOIS: S – Só converte para valor (se for o caso) depois da cópia feita e calculada. Isso garante valores corretos com qualquer tipo de dependência. Há casos em que isso pode ficar proibitivamente lento em planilhas grandes com dezenas de colunas de fórmulas. Nesse caso é recomendável colocar N e em último caso fazer o cálculo em 2 fases. BORDA – N (Sem borda), M(Média) e F(Fina). Default sem borda VALOR: N passa a colar fórmulas e não valores. Default = N

Dica: Deixe a tabela sempre com uma linha em branco antes e com apenas 1 linha de cabeçalho. Se for necessário 2 linhas de cabeçalho deixe uma linha em branco no meio. Se houver um bloco de dados intermediário de mais de 2 linhas (sem estar colado no gabarito) é preciso selecionar 2 células! • •

O sistema avisa se a tabela já contém apenas fórmulas em uma data coluna O sistema não sobrescreve as linhas finais com fórmulas, permitindo totais.

VII.3)Quebra texto na coluna corrente nas colunas da direita (Alt Q) Acessado pelo menu (Ctrl Shift X) ou pelo atalho Ctrl Q Quebra texto da coluna corrente, geralmente importada da Web, em colunas sucessivas a partir da coluna corrente conforme especificação dada. Se for especificada uma área, usa-se só a 1a. coluna da área selecionada, caso contrário assume a porção contígua de células preenchidas, sob a célula corrente. A especificação tem a forma [FiltroE!][FiltroS!]Modelo Onde FiltroE representa os caracteres a serem filtrados antes de quebrar a coluna. Um espaço no final indica que será (dependendo se há caracter correspondente em FiltroS) deixado apenas 1 caracter para toda região contígua de espaços + caracteres de controle. Onde FiltroS representa os caracteres correspondentes a entrarem no lugar dos caracteres filtrados. A falta de correspondência faz simplesmente o caractere de FiltroE ser apagado. E Modelo representa pedaço a pedaço o tipo da informação quebrada, sob a forma [ Campo [ Spec ] ] + Cada Campo pode ter os seguintes “tipos” P (Pedaço de texto sem espaços) M (Nome de mês por extenso ou abreviado, traduzido pelo número do mês) T (Texto a ser recuperado, incluindo espaços) N (Número a ser recuperado, incluindo número em notação científica (Finalizado com E seguido de um número inteiro). Pode ou não ter separador de milhares, mesmo que de forma inválida. Aceita números negativos em formato contábil, ladeado de parênteses. Usa digito para finalizar o campo texto a não ser que algum separador seja usado ou a especificação > para considerar campo texto até o final da entrada. Aceita % no final da especificação. D (Data a ser recuperada, aceita vários formatos alternativos, incluindo formato de nome do mês em extenso ou abreviado) S (Deixa a coluna correspondente da planilha com o conteúdo anterior) V (Número p/ ser recuperado como texto) 0 a 9 (Texto de tamanho fixo a ser recuperado) Já Spec são modificadores opcionais do campo anterior, que define basicamente quais caracteres serão pulados e/ou se o campo não deve ser exibido na planilha. Pode ser qualquer caracter diferente dos especificados acima, incluindo os especiais A, #, X, > e \ • • • • • • •

Cada “campo” pode ser seguido de caracteres a serem pulados, incluindo o próprio espaço. A faz com que o formato do campo anterior (número, data ou mês) seja Americano (mm-dd-yy e com decimal ponto (.) ) ‘ faz com o campo texto anterior seja introduzido como delimitado. Assume-se como opcional. O plique ou aspas que iniciar o campo, indicará o par correspondente que deve encerrar o campo. X faz com que o o campo anterior seja ignorado para exibição na planilha. > para fazer com que o campo texto possa ir até o final da célula (após “campo” tipo T) / faz com que o próximo caracter seja pulado. Usado para poder pular símbolos reservados. Ex: // pula o caracter /. # faz com que todos dígitos sejam pulados para campo texto

Observações

• •

• •

Qualquer caracter para pular após texto, faz com que o dígito deixe de ser separador. Para que o digito continue sendo separador, nesse caso, coloque # após o campo. Nesse caso funciona como separador, mas não é ignorado. Caracter especificado no pulo inibe caracter semelhante se válido em número. (como , por exemplo). Para pular caracteres após o número, mas que podem estar dentro do próprio número é preciso usar a especificação “0” seguido do caracter desejado. Para iniciar pulando use 0 seguido do “filtro” desejado. Esse campo é automaticamente ignorado. Para usar outro delimitador em vez de !, basta colocar antes da especificação !@, onde @ é o novo delimitador.

Cada linha de informação é quebrada em tantas colunas (“campos”) quantas informações relevantes não “anuladas” por um X, que estiverem contidas em Modelo, exceto a especificação de campo 0. Exemplo. Se na tabela acima apontarmos para o cursor para A1 ou A3, teclarmos Ctrl Shift X e escolhermos a opção 3 – Quebra Texto em Colunas e digitando-se como especificação -!NTN Temos o seguinte resultado em rosa, partindo-se da área verde

A 12/05/2003Baleia23 05/06/03Luz45 01/01/2003Maias88

1 2 3

B 15/05/03 05/06/03 01/01/03

C Baleia Luz Maias

D 23 45 88

VII.4)Exclusão de Linhas / Colunas (Ctrl Alt D) Deleta as linhas / colunas de uma tabela conforme filtro especificado, a partir da linha /coluna corrente. Útil especialmente para importações da web e outras fontes, onde há diversas linhas ou colunas em branco ou com conteúdo espúrio (---, por exemplo) que se deseja excluir. Se a seleção feita for de área, apaga trechos de linhas /colunas na área selecionada. Se a seleção for apenas de 1 célula apaga linhas /colunas inteiras. A célula analisada para ver se obedece ou não o critério do filtro é referente à coluna mais a esquerda da seleção. O filtro entrado permite várias funcionalidades: • •

B - Exclusão de linhas, com células em branco Basta manter o (B) que fica inicialmente na janela de mensagem, clicando em OK

H - Diz para fazer todo processo de exclusão na horizontal (em relação às colunas) e não vertical (sobre as linhas), como no modo default. • F – Considere fórmula e não valor. • N é usada como negação de todas opções acima Ex: N= apaga todas as linhas, cuja célula correspondente não seja o conteúdo atual, assim usa-se para se conservar todas linhas em que a coluna corrente seja o valor corrente.

• •

A - força considerar a área selecionada e não linha. ^símbolos ou >símbolos - Exclusão de linhas, com células que contenham apenas caracteres indesejáveis ou apenas 1 ou mais dentre os símbolos especificados A diferença é que ^ filtra também os caracteres de controle. Ex: ^- irá apagar linhas que contenham apenas -, caracteres de controle e espaços. Essa opção provavelmente será a mais usada, depois da anterior, porque atende a maioria das situações. •

= (s/ nada depois) Exclusão de linhas com conteúdo da coluna corrente, muito comum para eliminar todas linhas que contém um atributo indesejável da coluna corrente de uma tabela, estilo #N/D ou um texto qualquer. • =palavra Exclusão de linhas, com células que contenham o que vem depois do =. Ex: =banana, apaga todas as linhas, onde a célula vale banana. •

%modelo - Exclusão de linhas, cujas células contenham o modelo que se segue Escreva a palavra diretamente como filtro e clique em OK.

Ex: %ban*, apaga todas as linhas, onde a célula correspondente tem como modelo uma palavra que inicia como ban. (operador LIKE do VBA).

Descrição do Modelo Operador LIKE (indiferente a acento) ? Qualquer caractere único. * Zero ou mais caracteres. # Qualquer dígito único (0–9). [listadecarac] Qualquer caractere único em listadecarac. [! listadecarac] Qualquer caractere único que não esteja em listadecarac. - Dentro do colchetes configura um intervalo de letras. Ex 1: A?A* - Apaga todas as linhas cuja célula correspondente a palavra cuja 1a e 3a. letra é A. Ex 2: [A-D]# - Palavra de 2 caracteres iniciada com uma letra de A até D seguida de um dígito

VIII)Ordenação de Bloco VIII.1)Ordenação (Ctrl O / Ctrl Shift O) VIII.1.a)Introdução Comando para ordenar automaticamente por linha ou coluna, dependendo do contexto. Estando posicionado na 1a. coluna, mas não na primeira linha, assume ordenação horizontal, senão assume ordenação vertical. Internamente a ordenação horizontal é mais delicada pois ela obriga selecionar a área toda, excluindo a coluna inicial de cabeçalho. O sistema cria de forma transparente uma coluna oculta a direita da tabela, onde fica a identificação das especificações do sort (na célula chamada Xbloco). Assume-se as especificações lá armazenadas se eles já tiverem sido criadas. Para mexer nas especificações use Ctrl Shift O, para simplesmente usar as especificações já citadas ou assumir um default use Ctrl O. O item D determina que a ordenação será descendente ao invés de ascendente.

VIII.1.b)Botões de ordenação na planilha Pode-se colocar botões na sua planilha para Ordenação crescente ou decrescente. Para criar um botão, exiba Caixa Formulários, arrastar botão (2ª linha à direita) Esse botões podem ser associado então às macros OrdDown (Decrescente) ou OrdUp (Crescente). Depois é só escolher uma localização e um título adequado para os botões. VIII.1.c)Proteção de linhas e colunas Há uma esquema de proteger contra ordenação linhas abaixo e colunas à direita da planilha, comum, por exemplo, em planilhas com totais tanto em linhas como em colunas. Para a ordenação vertical, basta colocar na especificação Vnn, onde nn é o número da linha limite para ordenação vertical, abaixo da qual a ordenação vertical não mais atua. Em relação à ordenação horizontal, na especificação entra Hnn, onde nn é o número da coluna limite para ordenação horizontal, a partir do qual a ordenação horizontal não mais atua. Internamente insere-se uma linha /coluna de proteção, que depois da ordenação é excluída. VIII.1.d)Chaves Múltiplas O comando permite ainda assumir chaves múltiplas Um poderoso recurso do comando de ordenação consiste em nomear uma coluna com o nome XKey, nesse caso, a ordenação executada com o comando Ctrl O passa a utilizar uma chave dupla composta pela coluna XKey e dentro dela ordena-se pela coluna assinalada com o cursor: No exemplo abaixo, denominou-se a coluna A (Categoria) como sendo Xkey, o cursor estava em 100 (Verde), clicou-se Ctrl Shift O, especificou-se 1 e a planilha ficou com a aparência abaixo, ordenado pela coluna A + coluna D A

B

C

D

1

Categoria

Prod

Maio/02

Jun/02

2

Áudio

CD Player

100

100

3

Áudio

MD Player

200

200

4

Áudio

Cdodê

300

300

5

Cameba

Lençol

300

100

6

Cameba

Colcha

200

200

7

Cameba

Cobertor

100

300

8

Ferramenta

Alicate

200

100

9

Ferramenta

Prego

300

200

10

Ferramenta

Furadeira

100

300

Finalmente pressiona-se Ctrl O com o cursor em 200 (Verde) e a planilha passa a ficar assim, ordenada da mesma maneira, primeiro pela chave representada pela coluna A (XKey), e depois pela chave representada pela coluna C. A Categoria Áudio

B Prod CD Player

C Maio/02 100

D Jun/02 100

Áudio

MD Player

200

200

Áudio

CDodê

300

300

Cameba

Cobertor

100

300

Cameba

Colcha

200

200

Cameba

Lençol

300

100

Ferramenta

Furadeira

100

300

Ferramenta

Alicate

200

100

Ferramenta

Prego

300

200

Finalmente, se for atribuído outra coluna com o nome XKey2, a chave passa a ser tripla: Xkey + Xkey2 + Coluna do cursor

VIII.1.e)Tabela blocada Dentro da ordenação vertical proporcionada pelo comando de ordenação há o conceito de tabela blocada. Tabela blocada é aquela tem possui um padrão de um número determinado de linhas que se repete para baixo na tabela. Para esse tipo de tabela disponibiliza-se por meio desse comando de ordenação uma maneira de se ordenar (ascendente ou descendente), direto pela coluna, considerando a linha relativa dentro do bloco, de forma transparente, bastando informar a largura do bloco da 1 a. vez. ( direto o número ou B seguido do número) Ex: Na planilha abaixo clique em Ctrl Shift O e entre com 3D com a célula corrente em C5.

A

B

C

D

1

Prod

Info

Mai/02

Jun/02

2

A

Venda

100

3

A

Meta

100

4

A

Ating

100%

5

B

Venda

200

6

B

Meta

180

7

B

Ating

110%

8

C

Venda

150

9

C

Meta

190

10

C

Ating

75%

A planilha fica assim, com o bloco que contém o valor de venda de 200 na parte superior, porque contém o valor de venda mais alto.

1

A Prod

B Info

C Maio/02

2

B

Venda

200

3

B

Meta

180

4

B

Ating

110%

5

C

Venda

150

6

C

Meta

190

7

C

Ating

75%

8

A

Venda

100

9

A

Meta

100

10

A

Ating

100%

D Jun/02

Na planilha acima, digite Ctrl O com a célula ativa agora em C3, então passa a ficar assim. Repare que o bloco com o valor de meta de 180 passou a ocupar a 2ª posição, para que o bloco que contém a meta de 190 ficar na parte superior.

1

A Prod

B Info

C Maio/02

2

C

Venda

150

3

C

Meta

190

4

C

Ating

75%

5

B

Venda

200

6

B

Meta

180

7

B

Ating

110%

8

A

Venda

100

9

A

Meta

100

10

A

Ating

100%

D Jun/02

Obs.: Para fazer a ordenação por bloco, o sistema preenche uma coluna oculta, que por sua vez é usada como coluna base de ordenação. Se o bloco é especificado como 1, funciona como uma ordenação normal, sem sequer usar a coluna oculta, para otimizar.

IX)Cópias Incrementadas IX.1)Copia visíveis (Ctrl Shift V) Copia apenas as células visíveis das colunas contíguas marcadas, para um destino especificado (Linha, Coluna de uma aba, pasta dada), onde será copiado os dados de origem apenas nas células visíveis. Marque a área de origem, e informe um formulário a Linha, Coluna, Coluna Final (opcionalmente) , a Planilha (se for diferente), a Pasta (se for diferente) destino da cópia. Útil para a transposição de colunas, entre planilhas filtradas / com subtotais. Útil também para copiar uma coluna de fórmulas / valores de uma coluna para outra ou outras dentro de uma tabela filtrada. Nesse caso, a informação de Coluna Final pode ser interessante.

IX.2)Cópia com transformação (Ctrl Shift P) Faz a transformação de uma área, a partir das informações entradas em uma Caixa de Diálogo, comprimindo-a, expandindo-a, resumindo-a por dados de soma ou preenchendo-a com fórmulas. Essa ação pode ocorrer orientada por Coluna, o que descreveremos abaixo, ou Linha, conforme o parâmetro Orientação. Serve para trabalhar dados de diversas origens, intercalando-os e inserindo as fórmulas apropriadas. Como exemplo, podemos ter dados de previsão de venda a partir de um estoque inicial e entremeá-los com fórmulas para calcular os pedidos. Serve para recuperar colunas alternadas, com dados de mesma natureza e juntá-los em outra planilha para extração de gráficos e outras análises. Serve para agrupar dados, com outra visão, por exemplo transformar dados de venda mensais em dados de venda trimestrais. Há um origem, que pode ser definida como composta de 1 ou mais linhas. Cada linha é dividida em blocos de colunas (cujo tamanho é especificado em Largura do Bloco de Origem ) separados por 0 ou mais células (especificado em Número de Células entre Blocos), a coluna/linha inicial a ser copiada em relação a área é especificada em Posição Inicial na Origem (Assume-se 1, que corresponde a primeira coluna da área em questão) O parâmetro Intervalo de Origem é o intervalo de células (ex: A1:D50), se não for especificado corresponde a seleção corrente de células. O destino da cópia pode ser por cima da origem, ou pode-se especificar a Pasta, a Planilha, a Linha inicial e a Coluna inicial. Cada um desse parâmetros, quando deixado em branco, assume-se o valor correspondente à origem. A cópia dos blocos da origem, poderá ser feita no Destino, separando-se os blocos de acordo com o Número de Células entre Blocos Finalmente, os tipos de operações que podem se realizados é selecionado no parâmetro Tipo de Operação e podem ser os seguintes: Normal: Copia os blocos de células na origem, mantendo-os no destino, apenas alterando a distância entre os blocos. Essa operação pode juntar uma área que tenha buracos

intercalados, ou pode separar de forma intercalada áreas em que os “blocos” estão colados. Essa última situação geralmente é seguida por outra(s) ação (ões) para preencher os buracos assim gerados. Fórmula: Copia um bloco de fórmulas à esquerda da área desejada, para o resto da área selecionada (se for por coluna) e para baixo (se for por linha), geralmente preenchendo as colunas / linhas em branco entre os valores da área referida. O parâmetro Número de Células na Origem é ignorado. Soma: Resume no destino, os blocos de células da origem, através da soma das células pertencentes a cada bloco. Sendo assim, cada bloco de célula na origem, corresponderá a uma célula com sua soma no destino. Além disso há algumas opções adicionais: Se Valor é selecionado, apenas os resultados das fórmulas são copiados. Se Transpor é selecionado o resultado da cópia sai transposto, ou seja, linha vira coluna e vice-versa. Se Referência Absoluta é selecionada o resultado da cópia PRESERVA as fórmulas, sem qualquer tipo de conversão. Para uma conversão controlada, para a opção de cópia de fórmulas, há 2 parâmetros adicionais (Delta Lin e Delta Col) que servem para deslocar as referências não ancoradas na fórmula, de forma controlada. Assim A2 se converte em B7, se Delta Lin for 5 e Delta Col for 1. Se Copia Nomes é selecionado a cópia traz os nomes de intervalos da seleção original. Se Copia Formatos é selecionado a cópia preserva o formato da origem, célula a célula. Se Copia só Formatos só copia formato (inclusive do buraco) e não valor e/ ou fórmula. Para entender melhor como funciona, basta praticar com uma planilha rascunho, testando as diferentes opções e vendo seus efeitos. Essa função é extremamente poderosa, porque automatiza uma tarefa que manualmente seria demasiado maçante. Ex: Para copiar fórmulas para colunas a direita, pulando 2 colunas de valores, posicione o mouse na 1ª fórmula e marque até a última célula que deseja trazer a fórmula. , selecione Normal, Coluna, Na origem coloque o tamanho de bloco como 1 (já que é 1 coluna de fórmula), número de células entre blocos tanto na origem como destino 2. Deixe desmarcado todos check boxes (referência absoluta inclusive). Se houvesse 2 colunas de fórmulas (vale para qualquer número), o tamanho de bloco na origem passa para 2 e o processo funciona. De forma análoga, se houver 2 colunas de valores (vale para qualquer número), o número de células entre blocos na origem e no destino passa para 2

IX.3)Layoutação para Tabela Dinâmica Acessível pelo menu em Ctrl Shift D Copia uma área selecionada para outro lugar, conforme especificado no diálogo correspondente, preparando o destino para ser utilizado tabela dinâmica ou subtotal. É informado em uma caixa de diálogo quantas colunas a esquerda ficam fixas (parâmetro Colunas Fixas à Esquerda) e para qual linha inicial a área vai ser copiada (parâmetro Linha Inicial Destino) e qual a largura em colunas do bloco de dados D (Largura Colunas Bloco de Dados). É como se ele fatiasse as colunas por cortes verticais pela largura do bloco e fosse inserindo em linhas sucessivas.

Para entender a transformação que é feita basta acompanhar o exemplo abaixo Ex 1: Número de Colunas fixas à Esquerda : 1 (PROD) e a Largura Colunas Bloco de Dados: (1 para cada data)

1

A Prod

B Abr/02

C Maio/02

D Jun/02

2

0101532

1223

2100

1500

3

0604038

900

1200

850

1

A Prod

B Tipo

C Dado

2

0101532

Abr/02

1223

3

0604038

Abr/02

900

4

0604038

Mai/02

1200

5

0101532

Mai/02

2100

6

0604038

Jun/02

850

7

0101532

Jun/02

1500

Ex 2: Nesse caso, o Número de Colunas fixas: 1 (PROD) e a Largura Colunas Bloco de Dados: (2 para Venda e Estoque).

B

1

A Data

C Mai/02

D

E Jun/02

2

Prod

Ven

Etq

Ven

Etq

3

0101532

1225

1920

1000

1700

4

0604038

1200

1100

900

950

1

A Prod

B Data

C Ven

D Etq

2

0101532

Mai/02

1225

1920

3

0101532

Jun/02

1000

4

0604038

Mai/02

1200

1700 1100

5

0604038

Jun/02

900

950

IX.4)Converte p/ tabular Acessível pelo menu em Ctrl Shift D A partir da seleção corrente de uma tabela, “corta-a” (faz fatias) em pedaços de X em X linhas (onde X deve ser dado – largura da fatia) e coloca-os lado a lado no destino especificado. Dependendo da seleção, faz a geração completa da tabela que gerou o formato de subtotal corrente, que acaba funcionando como o inverso da macro anterior. Informe em uma Caixa de Diálogo a Linha, Coluna (Se for diferente), a Planilha (se for diferente), a Pasta (se for diferente) destino da cópia. Informe ainda a Largura X (Número de linhas) de cada fatia, o número de colunas de dados (Coluna Fixas à esquerda), que não será usado como combustível para o preenchimento da tabela (geralmente são as colunas mais a esquerda da planilha que continuarão como colunas no novo formato) e selecione se deseja Coluna pós fixas após cabeçalho. Se a tabela estiver ordenada de modo que esteja preparada para ser fatiada, tudo que esse processo fará é colocá-las (a parte não fixa) lado a lado. Cada fatia deve conter toda a variedade da chave da tabela (Produto no exemplo abaixo), geralmente a coluna mais a esquerda, ou seja, todas fatias devem ter a mesma largura em linhas. A utilidade básica é reconverter os dados que estão no formato subtotal, tabela dinâmica ou autofiltro, para um formato colunar adequado para gráficos e/ou outras aplicações. A seleção de coluna pós fixas no cabeçalho faz com que o sistema tente interpretar a coluna, logo após as colunas tidas como fixas, como descritor de colunas na tabela destino. No caso do exemplo a coluna Tipo Nesse caso, e apenas nesse caso, deve-se selecionar também a linha de cabeçalho. A saída terá uma linha de cabeçalho com o conteúdo da coluna Tipo horizontalizado de maneira adequada. As colunas fixas ( mais a coluna pós as colunas fixas, se for o caso) constituem as colunas tratadas Repete-se ainda a parte do cabeçalho após as colunas tratadas, por toda extensão do cabeçalho da nova tabela, como sua 1a. linha. Para apenas 1 coluna na origem isso é geralmente redundante. Geralmente a tabela vem ordenada pela coluna que se quer fatiar e colocar como rótulos de colunas. Se isso não acontecer a tabela é ordenada por essa coluna antes de fazer o processo. Ex: Nesse caso essa coluna é o Tipo.

1

A Prod

B Tipo

C Venda

2

0101532

01/02/97

1223

3

0604038

01/02/97

900

4

0101532

01/03/97

2100

5

0604038

01/03/97

1200

6

0101532

01/04/97

1500

7

0604038

01/04/97

850

c) Agora marque a área toda, acione o comando e deixe a coluna, a planilha e a pasta em branco, informe a linha 20 e altura 2 (porque há 2 linhas para cada data), 1 coluna de dados fixa (a coluna de produto) e SIM para coluna pós fixa no cabeçalho, que é a Data. Pronto! A tabela fica igual a tabela exemplo em V-3

A

B Venda

C Venda

D Venda

21 PROD 22 0101532

01/02/97

01/03/97

01/04/97

1223

2100

1500

23 0604038

900

1200

850

20

Exemplo 2:

1

A PROD

B TIPO

C 01/99

D 02/99

2

0101532

Venda

323

1223

3

0604038

Venda

232

900

4

0101532

Estoque

111

2100

5

0604038

Estoque

222

1200

6

0101532

Custo

123

1500

7

0604038

Custo

121

850

Nesse caso o Tamanho da Fatia permanece 2, e o número de colunas fixas continua 1 e continua tendo que selecionar coluna fixa pós cabeçalho (no caso a coluna tipo) e formato final fica. A

B 01/99

C 02/99

D 01/99

E 02/99

F 01/99

G 02/99

2

Prod

Venda

Venda

Estoq

Estoq

Custo

Custo

3

0101532

323

1223

111

2100

123

1500

4

0604038

232

900

222

1200

121

850

1

Essa tabela pode ser facilmente convertida em tabular ordenada por data. Basta transpor ela, ordenar por data (sem usar o cabeçalho) e transpor de volta. Nesse caso, a tabela fica assim:

A

B 01/99

C 01/99

D 01/99

E 02/99

F 02/99

G 02/99

2

Prod

Venda

Estoq

Custo

Venda

Estoq

Custo

3

0101532

323

111

123

1223

2100

1500

4

0604038

232

222

121

900

1200

850

1

IX.5)Cópia Heterodoxa Acessível pelo menu em Ctrl Shift D Essa macro serve especialmente para fazer cópias de fórmulas em diagonal, ou mesmo horizontal e vertical, de forma mais simples que a suprida pela opção Cópia com Transformação. O usuário entra os parâmetros desejados em um formulário Copia a fórmula da célula corrente, pulando de Incr Linha linhas e Incr Col colunas por cópia, até a linha Limite Linha e a coluna Limite Coluna (quando não há seleção, caso em que prevalece os limites da seleção) A parte relativa da formula (sem estar com o endereço ancorado por dólar - $) pode ser alterada com um deslocamento relativo de Incr Linha Fórmula linhas e Incr Coluna Fórmula colunas, relativo a cada célula destino da copia. Só Vazio é um parâmetro que só faz a cópia para células destino não preenchidas Exemplo: Copia a referência da coluna A em diagonal.

1 2 3 4

A 35 12 35

B

C =A1

D

E

F

Se marcarmos a área C1 até E3. Consideramos Limite Linha e Limite coluna como definido pela seleção. Incr Linha é 1 e Incr Coluna é 1, porque a cópia é em diagonal. Quanto a fórmula, ela fixa a coluna (Incr Coluna Fórmula = 0) e a linha da referência se desloca igual ao destino (Incr Linha Fórmula = 1). Só Vazio fica desmarcado, porque queremos copiar incondicionalmente.

Ao final, a aparência da planilha deveria ficar assim

1 2 3 4

A 35 12 35

B

C =A1

D

E

F

=A2 =A3

IX.6)Cópia / Subst / Seleção de Formato & Cia (Ctrl Shift I) Há 2 formatos diferentes de atuação desse comando. A opção sem área selecionada por Ctrl C, significa substituição / seleção de formato ou valor. Ver no final dessa seção a descrição do comando. A opção com Ctrl C corresponde à cópia de atributos selecionados de uma região para outra. De qualquer maneira aceita-se como atributos especificação de Fórmula (F), Valor, Valor diferente de Vazio (S), Cor (C), Borda (B), Fundo (U) ou Negrito (N) da área origem. Antes dessas rubrica pode se usar prefixos especiais (= ou ?) para customizar a forma de atuação, que serão abordados na descrição de cada subcomando. Cópia Possíveis usos: Copia só fórmulas de uma coluna preservando os valores em outras colunas, copiar só valores com uma determinada cor de fundo, aglutinando-os no destino. Marca-se a região de origem, emite-se um Ctrl C e seleciona-se a célula destino, como se fosse uma cópia normal. A partir daí se pressiona Ctrl Shift I e seleciona-se as especificações (cor, etc.) que deseja copiar. A cópia é avaliada pela seleção de origem linha a linha (e não coluna a coluna). Isso significa que se for desejável fazer a cópia por coluna, é preciso transpor a seleção e fazer a operação. Se especificado @ testa as especificações correspondentes na seleção de origem para decidir, linha a linha, quais delas serão copiadas. Só é copiada aquela que algum atributo de formato coincide com o formado da célula Xchave (aba help de Macros). Caso várias colunas estejam selecionadas, a linha é considerada apta para cópia quando pelo menos uma coluna se ajusta a célula Xchave. Se depois de @ vêm um número na especificação (Ex: @2), testa a condição apenas na coluna relativa dada em relação à seleção origem (Ex: Nesse caso 2a. coluna da seleção). Se especificado * antes do resto da especificação, só ocupa uma linha no destino as linhas selecionadas, ou seja, não há linhas em branco. Os prefixos (=, ?) atuam do seguinte modo. O uso de qualquer prefixo por si já liga o teste de condições da linha, de forma semelhante ao @. ? – Só testa a especificação (cor, etc.) sem copiá-la. = - Só copia sem testar. No caso de V (Valor) copia apenas as células que não são fórmulas, nas mesmas posições. No caso de F (Fórmula) copia apenas as células que são fórmulas, também nas mesmas posições. O S (Valor não nulo) também só aceita valor, mas não aceita célula em branco.

Qualquer outro atributo é testado pelo valor correspondente do atributo em Xchave na aba Help de Macros

A 1

B Num

C Valor

D Val2

2

2

=B2*2

3

4

5

4

5

=B4*3

5

10

3

5

6

12

2

2

7

2

=B7*3

2

No exemplo acima, marca-se de C2 a C7, emite-se Ctrl C , aponta-se para a célula D2 e digita-se Ctrl Shift I para fazer a formatação selecionada especial. Se for selecionado F (Fórmula), as fórmulas preenchem os buracos entre os valores da forma indicada abaixo. Esse processo é feito para todas colunas selecionadas no destino, se a origem contém apenas 1 coluna!.

A 1

B Num

C Valor

D Val2

2

2

=B2*2

=C2*2

3

4

5

2

4

5

=B4*3

=C4*3

5

10

3

5

6

12

2

2

7

2

=B7*3

=C7*3

Substituição / Seleção Outra ação feita por esse comando, que pode ser bastante útil, é na substituição seletiva de formato, quando não há área copiada com Ctrl C Nesse modelo, o comando substitui na área selecionada o padrão em XCHAVE pelo padrão em XPADRÃO, células da aba HELP ao lado da ajuda desse comando. Aceita um ou mais padrões ( C(Cor), U(Fundo), N(Negrito), B(Borda), V(Valor) e S(Valor não nulo) F(Fórmula) ) na especificação. Em relação ao prefixo que fica antes da especificação, no caso de C(Cor), por exemplo, atua do seguinte modo: ?C - Apenas testa se a cor de cada célula da área selecionada é igual a cor de XCHAVE =C - Troca cada célula a cor pelo padrão proposto em XPADRÃO caso todos testes feitos sejam positivos naquela célula (com esse e com outros formatos)

C (Sem prefixo) Faz as 2 coisas acima No caso de F(Fórmula), a fórmula é copiada a partir da 1 ª célula da área selecionada, junto com as outras ações especificadas, enquanto para todas as outras especificações a origem da cópia é a célula padrão. No caso S(Valor não nulo), V(Valor) e F(Fórmula) o teste se refere também ao fato de exigir que o conteúdo anterior seja valor (V, S) ou fórmula (F). No caso de fórmula (F) não há nenhum teste referenciando a célula padrão. Já para V ou S o critério é se o valor confere com o valor da célula padrão. Todas células que satisfazem as condições de seleção para substituição ficam selecionadas no final, permitido uma mudança interativa de valor, cor, etc. Dica: Uma ação comum nesse comando consiste em usar apenas prefixo ? para se testar uma ou mais condições (como cor de fundo), a fim de selecionar todas células que satisfazem a dada condição. Então pode-se mudar a cor de fundo, etc. Uma utilidade adicional nesse caso é acoplar seu uso a uma outra macro, para fazer alguma conta (Ctrl Shift Y) ou somar todo conteúdo (Ctrl Shift S), etc.

X)Transformações X.1)Conversão de valor em fórmula (Ctrl Shift Y) Extremamente útil para agilizar operações em planilha. Transforma, na prática, o seu Excel em uma calculadora bidimensional, partindo-se de especificações informadas em um formulário Para todas as células da seleção corrente, Inclui no final de cada valor um texto dado, convertendo-o para fórmula. @ pode ser usado no texto para simbolizar o próprio valor da célula. Ex 1:

A 1

1

2

2

3

3

• • • A 1

=1*Fator

2

=2* Fator

Marque um trecho de coluna com os valores 1, 2 e 3 Tecle CTRL + SHIFT + Y. Digite * Fator na caixa de entrada.

3

=3*Fator

No mesmo exemplo, entrar com o string @/(1+@) iria gerar as fórmulas A 1

=1/(1+1)

2

=2/(1+2)

3

=3/(1+3)

Marcar a opção Mantém inalterada fórmula com referência a célula. faz com que nenhuma fórmula que contenha uma referência a qualquer célula quer no formato A1, quer através do nome de célula seja alterada. A opção Transforma direto para valor faz com seja gravado em cada célula o resultado final obtido e não a fórmula resultante. A opção Força Atuar como Texto mostra uma intenção consciente de trabalhar com manipulação direta de texto ao invés de manipulação de fórmulas, seja numéricas ou de texto. Finalmente tem um subpainel chamado de referência, onde se pode especificar uma célula (Ex: G2) , ou apenas a parte da linha (2) ou coluna (G). O que acontece é que todas referências marcadas com # no texto original serão substituídas pela referência a uma célula, onde a parte da linha e/ou coluna pode estar fixada e a parte não fixada é tomada como referência a partir da célula onde está sendo realizada a operação. Essa referência relativa pode ser deslocada especificando-se um deslocamento de linha (Desloc Linha) e/ou um deslocamento de coluna (Desloc Coluna). Ex: Multiplicar C1:C4 por B2:B5 e colocar a fórmula correspondente em C1 Marca-se de C1 a C4 e digita-se CTRL SHIFT Y, depois especifique os parâmetros: Célula: B (porque a coluna é fixada) Desloc Lin [ 1 ] (porque se deseja multiplicar 1 linha após a célula correspondente) Descol Col [ ∅] (não é preciso entrar com nada, porque a coluna está fixada) Antes A 1 2 3 4 4

3 4 5 6

B 2 3 4 5

Depois A 1 2 3 4

3 4 5

B =2*$A2 (6) =3*$A3 (12) =4*$A4 (20) =5*$A5 (30)

4

6

Esse comando funciona para qualquer tipo de dado convencional (número, data e texto). Mesmo que marcado para converter para valor, faz primeiro a conversão para fórmula, nem que seja necessário cercar o texto da fórmula com aspas. A verificação é feita pela condição de erro da fórmula. Se a fórmula sem aspas não faz sentido ou gera uma condição de erro #NOME? indicando texto não identificado, então o comando cerca o texto da fórmula com aspas, para validá-la. Há uma outra facilidade que é o uso da barra vertical (|) na máscara para inserir um contador em um intervalo a ser manipulado, iniciando em 1. Exemplo: A 1 2 3 4 5

B

Tot Tot Tot Tot

Selecionada a área. Tecle Ctrl + Shift + Y e entre com @| em Fórmula, marque a opção transforme direto em valor. A 1 2 3 4 5

B

Tot1 Tot2 Tot3 Tot4

Se quisesse colocar um contador par seria um pouco mais complexo: seria preciso colocar na máscara: “@” & |*2 Um exemplo final, para ver o poder completo dessa abordagem seria tirar o 2ºt de cada célula, Para isso basta colocar em fórmula Ext.texto(“@”;1;2) & Ext.texto(“@”;4;1), o resultado deveria ser: Repare que a fórmula deve ser especificada em português mesmo! A 1 2 3 4 5

B

To1 To2 To3 To4

X.2)Muda referência de Fórmulas (Ctrl Alt Y) Faz com que as referências a linhas e colunas das fórmulas visíveis da área selecionada sejam transformadas, segundo 4 deslocamentos informados na entrada, separados por espaço ou vírgula:

Número de linhas para deslocar as referências relativas Número de colunas para deslocar as referências relativas Número de linhas para deslocar as referências absolutas Número de colunas para deslocar as referências absolutas

X.3)Ajeitando soma (Ctrl Shift M) Faz com que as células visíveis selecionadas de sua planilha passem a somar um valor desejado, entrado diretamente. Isso é feito de modo proporcional ao valor de cada célula. Naturalmente células vazias ou zeradas continuarão a valer 0. O default é mudar a soma para 1 (100%).

X.4)Põe Fórmula de Soma (Ctrl Shift S) Se só tem uma célula marcada, soma a coluna corrente (excetuando o título é claro) na 1ª célula visível abaixo do trecho corrente. Semelhante ao atalho do Excel Alt =, com a diferença que não precisa estar posicionado na célula que ficará com a soma. No caso de uma célula, rodeado por células vazias na vertical, soma horizontalmente na 1a. célula a direita. O sistema também pula o cabeçalho e as células não visíveis adjacentes ao cabeçalho, de forma a permitir que a soma atue apenas no intervalo visível, podendo ocultar antes e depois trechos não desejados. Se só tem 2 células marcadas juntas soma a linha / coluna na 1 ª célula à direita (no caso de 2 células lado a lado) e abaixo (no caso de 2 células juntas na vertical). Se for texto, usa separação por vírgula para concatenar. Se tem mais de 2 células selecionadas o sistema pede um numero de células para pular na soma. Formato n[H][V][M][Separador] Onde n é quantas células se intercala sem somar (soma de n em n). Default n=0 (não intercala), faz com que se some todas as células marcadas, que podem não ser contíguas, na última célula marcada. A marcação V faz com que sejam somadas apenas as células que não estejam em grupamentos internos (usando a função SubTotal) e a marcação M faz com que a função Média seja usada ao invés de função Soma. H indica que a soma se dará no sentido horizontal. Essas opções podem estar em qualquer ordem. Tanto no caso horizontal(H) ou vertical, se forem marcadas várias linhas (colunas) soma-se cada linha (coluna), colocando a fórmula na última coluna (linha) marcada. Se as células em questão são caracteres ou é especificado um separador, coloca concatenação ao invés de soma, usando o separador informado, que pode ser um símbolo ou um L (mudança de linha – que para ser exibido precisa ser formatado com Retorno Automático de Texto), um S (Espaço) ou N(indicando que não tem nenhum separador – default).

XI)Miscelânea XI.1)Envia Tecla (Ctrl A / Ctrl Shift A) Ctrl Shift A manda teclas para o aplicativo, informadas pelo usuário. O usuário pode guardar uma “lista” de macros. Para ir onde a lista está clique em Envio Teclas e coloque uma descrição e o texto com as teclas associadas. Isso estará disponível na lista disponibilizada para o usuário na solicitação da sequência de teclas. Use Ctrl + A para repetir a última sequência de teclas, conforme o último Ctrl Shift A utilizado. Esse comando usa a sintaxe do comando VBA para enviar teclas, SendKeys, sobre a qual tem um help completo. Enviar teclas inclui a possibilidade de executarmos as próprias macros, incluindo enviar teclas para formulários. De qualquer maneira resumiremos abaixo como especificar teclas: • • • • • • • •

Teclas comuns são expressas pelas próprias teclas, como A, B, C, etc. Teclas especiais ficam entre chaves, como {Esc}, {Left}, {Up},{Down}, {Right} , {Enter}, etc. Teclas que alteram o significado de outras teclas são ^ (Ctrl), + (Shift) e % (Alt), assim usa-se ^a, +^{Left}, etc. Atenção, para expressar Ctrl A, é preciso usar ^a e não ^A, que na verdade é Ctrl Shift ª {Enter} também pode ser expresso por ~ (Til). Para repetir uma tecla é preciso colocá-la entre chaves: {h 10} - h 10 vezes. Parênteses usa-se para expressar que as teclas alteradoras (^, % ou +) atua sobre todas elas. Ex: ^(ec) ,ou seja, Ctrl ( E C). Qualquer caractere especial (%, ^, ~, +, {,},[,],(,}) só pode ser literal se entre chaves.

Ex 1: ^+{Right}^f^b{Left}^f Explicação: Ctrl Shift Seta p/ Direita, Ctrl F, Ctrl B, Seta p/ Esquerda e Ctrl F – Isso marca o bloco de células com o mesmo valor e coloca moldura, usando 2 funções da Macros, que são Ctrl F para busca valor diferente e Ctrl B para colocar a borda. Ex 2: Repete 3 vezes {Down}+{Right}^+t^x{Down 2}~^{Up}^c{Down}^+{Down}+{Up}~{Down}{Down} Explicação: Seta para baixo, Shift Seta para Direita, Ctrl Shift t, Ctrl X, 2 setas para baixo, Enter, Ctrl Shift Up, Ctrl C, Seta para baixo, Ctrl Shift Seta para baixo, Shift Seta para cima, Enter, 2 setas para baixo. Ctrl Shift T – Comando da Macros que com seleção feita marca até a última célula. Transforma uma coluna de dados simples de modo que o primeiro elemento repita-se 2 vezes, levando os outros para baixo. Execução múltipla faz com que toda coluna repita cada elemento 2 vezes, ficando com extensão total 3 vezes maior. Ex 3: Transformar código de Maestro em Nome, Classe e Código {F2}=Arrumar{(}Ext.Texto{(}A9;1;45{)}{)}~{F2}=EXT.TEXTO{(}A9;47;1{)}~{F2}=EXT.TEXTO{(}A9;51;7{)}~

Ex 4: Multiplicar pelos dados da coluna anterior Entre com Ctrl Shift Y (Calculadora bidimensional), passando *# , conversão direta para valor e –1 na linha. A seguir entre com Ctrl Shift A e especifique apenas: ^+y%o (Ctrl Shift Y seguido de Alt O, que aciona o OK do formulário da calculadora. Funciona porque o Ctrl Shift Y memoriza os últimos campos entrados. A partir daí apenas Ctrl A aciona o cálculo desejado Se fosse de uma linha específica bastaria entrar o número da linha em célula e branco na linha. Ex 5: Colar formato para a linha de baixo do trecho da linha corrente até onde tiver dados +{Right}+{Right}^+t+{Down}^+l~

XI.2)Põe Nome nas Fórmulas (Ctrl Shift N) Põe nome nas fórmulas segundo especificação dada, e segundo a ordem de preferência correspondente a ordem de entrada. • • • •

L – Prioridade para substituir por nome de linha C – Prioridade para substituir por nome de coluna N – Prioridade para substituir por nome simples ou nome de intervalo T – Prioridade para substituir referência a 1a. célula de uma tabela pelo nome da coluna de uma tabela logo acima, sem aspas. Isso não chega a constituir em um verdadeiro nome para o Excel, mas é útil para critérios nas funções do grupo BD. • I – Indireto, recurso especial voltado para uma referência que não consegue virar nome, mas acessa uma fórmula, que se deseja disponibilizar diretamente no alvo. • X – Opção pouco comum e especial, que precisa estar especificado na 1a. coluna, sob a forma X...|Palavra1|Palavra2, onde um nome que se usa para substituir, que contém uma dada palavra (Palavra1) vire todo ele a palavra especificada em palavra2. Essa opção permite substituir uma possível referência de nome ou família de nomes por uma constante. Se fórmula especificar uma célula ou intervalo de outra aba que esteja nomeado, o sistema trata, desde que o nome da aba seja composto apenas de letras, números e sublinhado (iniciado por letra). Claro que esse raciocínio não vale para o tipo T. Nomes são usados sem o prefixo de aba que às vezes existe. Isso pode causar ambigüidade em alguns casos.

XI.3)Vai para nome (Ctrl Alt N) Vai para o nome / célula indicado, mesmo em outra aba, assumindo o conteúdo do clipboard como default. Ou seja, um ato comum é marcar na fórmula o endereço desejado e colocar no clipboard com Ctrl C e ao sair da edição disparar o Ctrl Alt N. O principal diferencial sobre o atalho Ctrl Y do Excel, além da facilidade de uso do Clipboard citada acima, é que você pode visualizar livremente a célula destino Além disso, o mesmo atalho volta para célula original, se nome deixado vazio e, ao contrário do Ctrl Y, pode-se seguir nomes diversas vezes e depois retornar para a origem. Se o nome iniciar com =(igual), coloca-se a célula corrente como novo ponto de retorno. Se o nome iniciar com .(ponto), busca-se a coluna / linha correspondente, quando seleção destino refere-se respectivamente a linha / coluna inteira. Para ir a um nome qualquer, sem relação com a região da planilha que está sendo visualizada pode-se usar o atalho Ctrl Y predefinido do Excel

XI.4)Operação com nome (Alt N) Processo que permite de forma segura exibir, criar ou excluir um nome. O nome default oferecido para criação / exclusão é o nome da área selecionada, quando a área tem um nome atribuído. Se não for o caso, assume o Clipboard se seu conteúdo for um nome válido. Se ainda não for o caso assume-se o conteúdo do texto a esquerda do nome ou acima do nome. O usuário pode a partir daí acatar o nome o entrar com o nome que desejar. Caso o usuário não tenha especificado nada, o sistema oferece o nome para criação (quando a área selecionada não tem um nome associado) ou exclusão(em caso contrário). No caso da exclusão, se o nome não existir oferece o nome para criação. Quando coloca-se , (local) ou .(global) no final do nome, o sistema cria o nome atribuído à área selecionada, questionando se apaga o nome anterior, se existir. Nesse caso, o sistema exibe sua localização.

No final ainda pode especificar ? que dá uma segurança adicional, pois permite criar pedindo confirmação se o nome ainda não existir. Pode se especificar ? depois de um comando de criação (, ou .) ou não. Caso não se especifique assume-se criação conforme nome tenha ou não aba especificada (estilo “aba!nome”) A especificação . (ponto) passa por cima de um eventual prefixo de aba (“aba!”) e cria o nome como global. Ao final salva o nome no Clipboard. Assim caso deseje localizar o nome ou corrigir a grafia é só teclar Alt N (cancelando ou não) A intenção do comando é substituir quase completamente o uso do Ctrl F3, exceto quando se deseja passear em todos nomes definidos.

XI.5)Copia Nome Local (Ctrl Alt L ) Uma situação comum é termos abas similares com a mesma estrutura, uma com nomes globais e outra (ou outras) com nomes locais. Da primeira vez basta copiar a aba que a aba auxiliar de mesma estrutura será criada. Posteriormente, com a manutenção da aba matriz, pode ficar complicado gerenciar a aba “fillhote”. Então surge essa funcionalidade: Marca-se a área origem que sofreu atualização de nomes com Ctrl C, posiciona-se na área destino, e se aplica Ctrl Alt L. Pode repetir o processo mais de uma vez. Ao final a área continua marcada possibilitando outros tipos de cópia.

XI.6)Valor e Tradução de Fórmula Exibe o valor da fórmula quer seja especificada em Inglês ou Português. Pode-se usar o atalho Ctrl Alt V. Exibe ainda as 2 línguas, independente da língua da fórmula

XI.7)Mudar agrupamento Use Ctrl Alt G para mudar o grupamento default da aba corrente para total a esquerda e em cima.

XI.8)Preparar para a impressão Use Ctrl Alt P para mudar as margens e eliminar a área de cabeçalho e rodapé do texto, antes de imprimir.

XII)Macros Diversas Sem atalho de teclado, podem ser chamados teclando-se ALT M M e selecionado-se a macro pelo nome. Algumas podem ser acessáveis diretamente no menu de outras opções através do Ctrl Shift D. Todas atuam sobre uma área selecionada

XII.1)Porcentagem ConvPerc - Converte porcentagem expressa em número de 0 a 100 e converte para %, tirando todas as referências a 100

XII.2)Problemas de conversão de linguagem

ConvData - Converte data que entrou de forma invertida (dia trocado com mês), estilo mm-dd-aa ou mm-dd-aaaa , etc para formato data. Células que se tornaram data por coincidência são também tratadas. Está no menu de outras opções acessável com Ctrl Shift D. ConvNum – Converte número em formato americano d,ddd.dd para formato brasileiro d.ddd,dd. Células que se tornaram números por coincidência são também tratadas. Parênteses são considerados indicador de número negativo. % são também tratadas. Está no menu de outras opções acessável com Ctrl Shift D.

XII.3)Conversões ZP_Texto - Converte número em texto, sem preenchimento de zeros à esquerda ConvNumBr - Converte texto contendo número em formato brasileiro em número formato brasileiro, baseado no 1o. número que ocorre no texto, da esquerda para a direita(modo liberal) ou se é um número apenas ladeado por espaços. (modo estrito) Ex: Cod23 fica valendo 23. Está no menu de outras opções acessável com Ctrl Shift D.

XIII)Funções Há algumas funções desenvolvidas que podem ser úteis, para serem usadas na confecção de uma planilha. É necessário anteceder seu nome por Macros.xls! Ex: Macros.xls!am(25)

=> 2a1m

XIII.1)Funções de Intervalo Concat(Range, Delim, Special) - Concatena todos os strings de um dado intervalo de células. Útil para destacar exceções, quando a maiorias das colunas de um intervalo são vazios. Delim é o delimitador ( o default é o espaço), se Special concatena só os diferentes. Antes(Célula) – Volta o primeiro número de linha anterior que não está vazio

XIII.2)Funções diversas AM(número) - Converte um número para formato anos e meses. Ex: 38 = 3a4m. Útil para planilhas de payback DV(a,b) - Divide A por B, voltando 0 se B for 0. Útil para evitar a situação de #DIV/0 em 1 planilha AddData(MData, N, Tipo) - Adiciona N (meses ou anos) a uma data Mdata, o default é meses. Se Tipo é a adiciona em anos ProcVert(S, Rng, NCol ) - Retorna o primeiro valor de S no interv RNG. com a coluna relativa NCOL não vazia PrimVis(Rng, [ Horizontal, Incremento] ) – Retorna o primeiro conteúdo não oculto na vertical (default) ou horizontal visível a partir de Rng na direção apontada por Incremento (Se 1 é para baixo (vertical) ou para direita (horizontal), se é –1 é o contrário, default = -1) ATec – Para se chamar geralmente no Ctrl Shift X (Menu principal), para atribuir uma rotina ou função a uma tecla.

XIII.3)Função Vetor AbinScan(Chave, Vet) - Busca Chave em um vetor ordenado Vet, voltando a posição no vetor se encontrou ou 0. FindVet(Ch, Vet1, [ Vet2, MaiMin ] ) – Busca Chave em um vetor Vet1 voltando a posição se encontrou (ou a componente correrspondente em Vet2) ou 0. Se MaiMin é TRUE considera a diferença entre maiúscula e minúscula, default é não considerar.

XIII.4) Funções string As funções abaixo são mais úteis para quem elabora macros, onde poupa bastante trabalho. QuebraLine(Texto,Spec,Sep,[Ind] ) – Quebra o texto Texto, segundo a especificação Spec, a saída é exibida usando o separador Sep. Usa os mesmos recursos do comando Quebra texto em colunas, 3a. opção do menu principal. Ind volta a posição final no texto. FReplace(Valor, Chave, Subst) – Substitui todas ocorrências de Chave em Valor por Subst Rat(Texto, Conjunto) - Volta a posição da última ocorrência de um caracter em CONJUNTO no string TEXTO. Ex: Rat(“Banana”, ”n”) = 5 Abaixo uma série de funções relacionada a obtenção de palavras ou trechos compostos de caracteres que estão ou não dentro de um conjunto especificado de caracteres. Scan(Inicial, Texto, Conjunto) - Volta a posição em TEXTO, a partir de INICIO, do 1o caracter que não pertence ao texto CONJUNTO. Se INICIO é negativo pesquisa na direção inversa, isso vale para todas as funções seguintes. Percorre letras dentro do CONJUNTO Ex: Scan(1,”31Alo”,”0123456789”) = 3 ScanWord(Inicial, Texto, Conjunto) - Pega o pedaço em TEXTO, a partir de INICIO, até o 1o caracter que não pertence ao texto CONJUNTO. Percorre letras dentro do CONJUNTO Ex: ScanWord(1,”Paulo Eduardo” ,”ABCDEFGHIJKLMNOPQRSTUVWXYZ”) = “Paulo” Pesq(Inicial, Texto, Conjunto) - Volta a posição em TEXTO, a partir de INICIO, do 1o caracter que pertence ao texto CONJUNTO. Percorre letras fora CONJUNTO. Ex: Pesq(1,“lima mola”,” “) = 5 GetWord(Inicial, Texto, Conjunto) - Pega o primeira palavra em TEXTO, a partir de INICIO, até o 1o caracter que pertence ao texto CONJUNTO. Percorre letras fora CONJUNTO Ex: GetWord(1,”Almoço/Lanche”,”/”) = “Almoço” LastWord(Texto, Conjunto) - Volta a última palavra de TEXTO, depois da última ocorrência de um caracter em CONJUNTO. Percorre letras fora CONJUNTO Ex: LastWord(“C:\NOTAS\LISTA8.PRN”,”\”) = “LISTA8.PRN” WordNum(Texto, Num, Delimitador) - Volta a NUMa. palavra do string TEXTO, usando os delimitadores em DELIMITADOR. Percorre letras fora CONJUNTO. A tentativa de recuperar um número de palavra que não existe retorna vazio.

WordNum(“José Carlos Pena”,2,” “) = “Carlos” UpperNoAC(Texto) – Passa para maiúsculo e tira acentuação. Ajeita(Texto, [Filtro], [High] ) - Deixa só 1 espaço entre nomes, tira os espaços no início e no final da palavra. Filtra ainda caracteres indesejáveis especificados em Filtro. Filtra todos caracteres acima de 127 se High. Filtra caracteres abaixo de 32(espaço). A combinação das 2 funções acima tende a igualar cadeias diferentes apenas por detalhes de espaçamento e acentuação. AjeitaTudo (Texto, [Filtro], [High] ) – Idem, mas além disso passa para maiúscula e tira os acentos. SuperTrim(Texto, [d]) – Tira espaços e caracteres de controle antes e depois do texto. Se D=1 tira só no final. Se D=-1 tira só no início.