Apostila EXCEL 2007

Alessandro Trovato Treinamentos Corporativos

Apostila Excel 2007

TREINAMENTO MICROSOFT EXCEL 2007

AN OT AE S

Pgina: 1 / 179


Apostila Excel 2007

Apostila desenvolvida por Alessandro Trovato de acordo com conhecimentos adquiridos e necessidades apresentadas por seus alunos nos treinamentos de capacitao oferecidos. Microsoft Excel marca registrada da Microsoft.

Essa apostila de livre distribuio. Solicito somente manter os crditos em caso de reproduo para utilizao em treinamentos.

VISITE MEU SITE http://www.atmconsultoria.com.br Contato: [email protected] [email protected]

Data de elaborao: Fevereiro de 2010 Reviso xxx em xxxxBibliografia: Ajuda do Excel 2007

AN OT AE S

Pgina: 2 / 179


Apostila Excel 2007

Contedo Iniciando o Excel ............................................................................................................................................. 6 Criando um atalho de teclado para carregar o Excel ..................................................................................... 7 O ambiente de trabalho do Excel ................................................................................................................... 9 Teclas de atalho ........................................................................................................................................... 14 Os componentes do Excel ............................................................................................................................. 16 Coluna .......................................................................................................................................................... 17 Linha ............................................................................................................................................................ 18 Entendendo Funes .................................................................................................................................... 19 1. Mesclar clulas ..................................................................................................................................... 20 2. Funo Soma .................................................................................................................................... 21 3. Subtrao ......................................................................................................................................... 23 4. Multiplicao.................................................................................................................................... 23 5. Diviso .............................................................................................................................................. 24 6. Porcentagem .................................................................................................................................... 24 7. Funo Mximo ................................................................................................................................ 25 8. Funo Mnimo................................................................................................................................. 26 9. Funo Maior ................................................................................................................................... 26 10. Funo Menor .................................................................................................................................. 27 11. Funo Mdia ................................................................................................................................... 28 12. Funo Cont.nm ............................................................................................................................. 28 13. Funo Cont.Valores......................................................................................................................... 29 14. Funo Contar.Vazio ........................................................................................................................ 29 15. Funo Hoje...................................................................................................................................... 30 16. Funo Agora ................................................................................................................................... 30 17. Funo Hora ..................................................................................................................................... 30 18. Funo Minuto ................................................................................................................................. 31 19. Funo Segundo ............................................................................................................................... 31 20. Funo Tempo .................................................................................................................................. 32 21. Funo Dia........................................................................................................................................ 32 22. Funo Ms ...................................................................................................................................... 33 23. Funo Ano....................................................................................................................................... 33 24. Funo Data ..................................................................................................................................... 34 25. Funo Esquerda .............................................................................................................................. 34 26. Funo Direita .................................................................................................................................. 34 27. Funo Ext.texto ............................................................................................................................... 35 28. Funo Concatenar........................................................................................................................... 35 29. Funo Texto .................................................................................................................................... 36 30. Funo Maiscula ............................................................................................................................ 36 31. Funo Minscula ............................................................................................................................ 37 32. Funo Pri.Maiscula ....................................................................................................................... 37 33. Funo Romano................................................................................................................................ 37 34. Funo Valor .................................................................................................................................... 38 35. Funo Mod ..................................................................................................................................... 38 36. Funo Rept...................................................................................................................................... 39 37. Funo Int......................................................................................................................................... 40AN OT AE S

Pgina: 3 / 179


Apostila Excel 2007

38. Funo Arred .................................................................................................................................... 41 39. Validao.......................................................................................................................................... 41 40. Formatao Condicional................................................................................................................... 44 41. Funo Comentrio .......................................................................................................................... 47 42. Funo SE ......................................................................................................................................... 47 43. Funo E ........................................................................................................................................... 51 44. Funo OU ........................................................................................................................................ 52 45. Funo erros ................................................................................................................................... 53 46. Funo Cont.se ................................................................................................................................. 55 47. Funo Somase................................................................................................................................. 56 48. Autofiltro .......................................................................................................................................... 57 49. Subtotais .......................................................................................................................................... 59 50. Estrutura de Tpicos ......................................................................................................................... 62 51. Funo Escolher................................................................................................................................ 66 52. Funo ProcV (Procura Vertical) ....................................................................................................... 67 53. Funo ProcH (Procura Horizontal) .................................................................................................. 71 54. Funo ndice ................................................................................................................................... 73 55. Funo Corresp ................................................................................................................................. 76 56. Funo Desloc .................................................................................................................................. 79 57. Atribuio de nomes......................................................................................................................... 84 58. Tabela Dinmica............................................................................................................................... 86 59. Grficos ............................................................................................................................................ 98 60. Grfico Dinmico ............................................................................................................................ 104 61. Impresso no Excel ......................................................................................................................... 105 62. Modos de Visualizao ................................................................................................................... 112 63. Autosalvamento ............................................................................................................................. 113 64. Compartilhando Planilhas do Excel ................................................................................................ 115 65. Vincular clulas entre planilhas ...................................................................................................... 117 66. Vincular documentos do Word ....................................................................................................... 118 67. Classificando os dados.................................................................................................................... 120 68. Controlando alteraes na planilha ............................................................................................... 123 69. Auditoria de Frmulas .................................................................................................................... 126 70. Suplementos: Histograma .............................................................................................................. 135 71. Cenrios.......................................................................................................................................... 139 72. Atingir Meta ................................................................................................................................... 144 73. Solver.............................................................................................................................................. 147 74. Tabela de Dados ............................................................................................................................. 156 75. Funo Pgto ................................................................................................................................... 161 76. Macros ........................................................................................................................................... 163 Exerccios.................................................................................................................................................... 172 Agradecimentos ......................................................................................................................................... 179

AN OT AE S

Pgina: 4 / 179

Alessandro Trovato Treinamentos Corporativos Microsoft Excel 2007

Apostila Excel 2007

Em 30 de janeiro de 2007 a Microsoft realizou o lanamento de sua nova sute de aplicativos, o pacote Office. Dentre as opes disponveis, destaco abaixo os programas que acompanham cada verso: Home and Student, Standard, Small Business, Professional e Ultimate. Nesta apostila trataremos do aplicativo Excel.

Home and StudentWord 2007 Um conjunto completo de ferramentas de redao e composio que ajudam voc a criar e compartilhar contedo. PowerPoint 2007 Eficiente ferramenta de apresentao que permite criar apresentaes com aparncia profissional. Excel 2007 Uma eficiente ferramenta para criar planilhas e analisar e compartilhar informaes. OneNote 2007 Uma soluo de bloco de notas digital que permite reunir anotaes e informaes e um s lugar. Outlook 2007 Uma soluo integrada para voc administrar o seu tempo e gerenciar informaes, criando conexes alm de qualquer fronteira. Outlook 2007 com Business Contact Manager Uma soluo integrada para voc administrar o seu tempo e gerenciar informaes, criando conexes alm de qualquer fronteira. Access 2007 Uma soluo de banco de dados que permite controlar, comunicar e compartilhar informaes de maneira eficaz. Publisher 2007 Crie, personalize e compartilhe publicaes e materiais de marketing internamente. Groove 2007 Uma ferramenta de colaborao que ajuda as equipes a trabalharem juntas. InfoPath 2007 Uma programa de coleta de informaes com o qual voc pode criar e implantar formulrios eletrnicos.

Standard

Small Business

Professional

Ultimate

Home and Student1

Standard

Small Business

Professional

Ultimate

AN OT AE S

Pgina: 5 / 179


Apostila Excel 2007

O Excel um programa de clculos. um software de planilhas eletrnicas com muitos recursos para auxiliar no tratamento de informaes. Com o Excel podemos construir desde operaes simples at clculos mais avanados como financeiros, matemticos, lgicos, estatsticos, etc. Podemos fazer o controle de nossas despesas financeiras pessoais ou controlar o fluxo de caixa de uma empresa. Controlar fluxo de materiais em estoques, estatisticamente analisar como a empresa est entre outras funcionalidades. Os principais recursos que encontramos no aplicativo so: Planilhas: permite a montagem e organizao dos dados para anlise. Bancos de dados: podemos classificar, ordenar, pesquisar e agrupar informaes para formar banco de dados. Grficos: Com os grficos do Excel poderemos utilizar recursos visuais avanados para anlise das informaes obtidas com suas planilhas. Apresentaes: Com recursos de formatao avanados, podemos utilizar o Excel tambm para demonstrar nossos resultados. Macros: Tarefas simples ou complexas podem ser resolvidas com a criao de macros. Podemos tambm atravs da utilizao do VBA (Visual Basic for Application) criar nossas prprias funes. Voc aprender com este material a criar operaes matemticas bsicas, recursos de anlise de dados e ter muitas dicas de recursos avanados. Essa apostila servir como referncia inclusive para futuras consultas a conceitos abordados durante nosso treinamento. Em caso de dvidas podemos contar tambm com a Ajuda do prprio programa. Utilizando o assistente acionado pela opo Ajuda, localizado na Faixa de Opes (Ribbon), conforme indicado na imagem abaixo, ou pressionando a tecla a qualquer momento.

Iniciando o Excel Para carregar o Excel devemos seguir os passos abaixo (considerando que o sistema operacional utilizado seja o Windows Vista). 1.Clique no boto Iniciar (localizado no canto inferior esquerdo da tela). 2. Posicione e clique com o boto esquerdo do mouse sobre todos os programas. A lista acima da opo Todos os programas ser atualizada.

AN OT AE S

Pgina: 6 / 179

Alessandro Trovato Treinamentos Corporativos3. Clique sobre o grupo Microsoft Office.

Apostila Excel 2007

4. Na lista que ser aberta, clique sobre o atalho Microsoft Excel 2007. Veja na imagem ao lado a rea em destaque. A partir deste ponto o Excel carregado para a memria e a tela abaixo exibida. Vamos ver em detalhes o ambiente de trabalho do Excel nas pginas seguintes.

Criando um atalho de teclado para carregar o Excel Alm do modo tradicional explicado acima, podemos tambm carregar o Excel atribuindo uma tecla de atalho para sua execuo. Quando a seqncia de teclas atribuda pressionada, automaticamente o Excel carregado. Esse um recurso do sistema operacional e no do Windows. Voc poder inclusive atribuir quantos atalhos desejar para os programas que voc mais utiliza. Veja os passos abaixo.AN OT AE S

Pgina: 7 / 179

Alessandro Trovato Treinamentos Corporativos1. Clique no boto Inicar. 2. Clique em Todos os Programas 3. Clique no grupo Microsoft Office

Apostila Excel 2007

4. Posicione o mouse sobre o cone do Microsoft Office Excel 2007 e clique com o BOTO DIREITO DO MOUSE. Com isso abriremos o menu destacado ao lado. 5. Clique sobre Propriedades. A janela abaixo ser exibida. Observe na parte destacada a opo Tecla de Atalho. Clique com o mouse neste campo.

6. Pressione as teclas Ctrl + Shift + E (simultaneamente) ou Ctrl + Alt + E (se preferir). Observe que pressionando a sequncia acima no campo Tecla de Atalho as teclas aparecero na ordem pressionada. 7. Aps esse passo clique no boto OK. Pronto, para carregar o Excel basta pressionar a sequncia de teclas escolhida (CTRL + SHIFT + E ou CTRL + ALT + E). Para eliminar o atalho atribudo devemos repetir os passos acima mas pressionando a tecla Backspace no campo Tecla de atalho. Aparecer a palavra Nenhum indicando a remoo da tecla de atalho. Com este recurso voc ter um considervel aumento de produtividade!

AN OT AE S

Pgina: 8 / 179

Alessandro Trovato Treinamentos CorporativosO ambiente de trabalho do Excel 1 2

Apostila Excel 2007

8 3 4 5 7 6 12 9 10

20 18 19 17 16 15 14

11 13

21

30

24

22

29 28

27

26

25

23

H uma grande diferena na Verso 2007 do pacote Office se comparada com suas verses anteriores. H uma linha divisria da evoluo pois a verso 2007 um marco destes aplicativos. Conforme estudos de especialistas em aprendizado para pessoas que nunca utilizaram o pacote Office, seu aprendizado facilitado e realizado em menos tempo. Pessoas que j conhecem as verses anteriores tem mais dificuldade para esses aprendizado. O motivo a relao que um usurio experiente faz com a utilizao da verso anterior. A curva de aprendizagem mais longa em tempo para esses usurios. Minha dica para voc que est interessado neste curso dedicar-se a aprender bem uma verso ao aplicativo para que o aprendizado das futuras verses seja mais simplificado! Vamos a partir de agora identificar cada uma das partes da janela acima. 1. Boto Office: Uma novidade nesta verso. O Boto Office agrupa os principais controles para manipulao dos arquivos, impresso e publicao. So itens do boto Office: Novo, Abrir, Salvar, Salvar Como, Abrir do Office Live, Salvar no Office Live, Imprimir, Preparar, Enviar, Publicar e Fechar a. Novo: Permite abrir uma nova Pasta de Trabalho.AN OT AE S

Pgina: 9 / 179


Apostila Excel 2007

b. Abrir: Permite abrir Pastas de Trabalho salvas anteriormente. c. Salvar: Permite salvar arquivos que esto sendo alterados e utilizados no momento. Permite tambm salvar um arquivo novo que no tenha sido salvo ainda. d. Salvar Como: permite gerar uma cpia do arquivo aberto, mantendo as caractersticas do arquivo original preservadas. Podemos salvar inclusive a Pasta de trabalho em outras verses do Excel. e. Abrir do Office Live e Salvar no Office Live so opes para salvamento na verso da Microsoft do Office Live Workspace (que um servio de compartilhamento de arquivos gratuito). f. Imprimir: Permite a impresso da Pasta de trabalho aberta. Podemos inclusive visualizar a impresso em um dos subitens desse grupo. g. Preparar: Permite a manipulao das propriedades e traz recursos avanados para o arquivo como Edio das propriedades da pasta de trabalho, Inspecionar Documento (verifica se h informaes pessoais na pasta de trabalho), Criptografar Documento, Restringir Permisso, Adicionar uma assinatura Digital, Marcar como final e Executar Verificador de Compatibilidade. h. Enviar: Permite o envio da Pasta de Trabalho em anexo por e-mail, ou enviar um Fax utilizando um servio de Fax da Internet. i. Publicar: Permite que suas planilhas sejam salvas em ambientes compartilhadas como redes ou ainda servios como o Sharepoint da Microsoft. Encontramos nesse grupo: Servios do Excel, Servidor de Gerenciamento de documentos e Criar espao de trabalho de documentos. 2. Barra de Acesso Rpido: Permite que alguns dos principais recursos acessados do Excel sejam colocados nesta barra. No exemplo so exibidos os botes Salvar, Desfazer e Refazer. Clicando com o boto direito do mouse sobre essa borra podemos configurar quais botes sero exibidos ou ocultados. 3. Personalizar Barra de Menus de Acesso Rpido. Como o exemplo acima, permite configurar quais botes sero exibidos ou ocultados. Apresenta tambm alguns botes padro que podem ser selecionados. Veja a imagem ao lado. Os trs botes visveis esto marcados, indicando que esto sendo mostrados na Barra de Acesso Rpido. 4. Barra de Ttulos: Exibe o nome do arquivo aberto no momento e o programa em exibio.

AN OT AE S

Pgina: 10 / 179


Apostila Excel 2007

5. Guias: as guias trazem o conjunto de comandos abrigados pelo Ribbon. So elas: Incio, Inserir, Layout de pgina, Frmulas, Dados, Reviso, Exibio e Desenvolvedor. 6. Boto Minimizar: Minimiza a Janela ativa para a Barra de Tarefas do Windows. 7. Boto Restaurar / Maximizar: Permite restaurar o tamanho de uma janela de aplicativo ou maximiz-la. 8. Boto Fechar Aplicativo: Fecha o aplicativo quando selecionado. 9. Boto Fechar Arquivo: Fecha o arquivo que est em edio. 10. Boto Restaurar / Maximizar: Permite restaurar o tamanho de uma janela de arquivo ou maximiz-la. 11. Boto Minimizar Arquivo: Minimiza o arquivo que est sendo editado para a borda inferir janela do aplicativo. 12. Boto Ajuda: Carrega o assistente do Excel de Ajuda. 13. Dividir Janela Horizontalmente: Quando arrastado para a parte inferior cria uma diviso de janelas horizontal, permitindo que visualizemos 2 partes simultneas da planilha. 14. Cursor: A representao da posio de onde est posicionado o cursor e onde sero inseridos os dados em nossa planilha. 15. Barra de Frmulas: onde sero inseridas as frmulas nas clulas. A barra de frmulas exibe o contedo de uma clula quando esta estiver selecionada. 16. Inserir funo: Permite carregar o assistente do Excel para utilizao das funes. Veja a tela ao lado. Ela exibida quando pressionamos o boto. 17. Caixa de nome. A caixa de nome possui alguns recursos: a) exibe a referncia da clula que est selecionada no momento. b) Permite atribuir nomes a faixas de clulas. c) Permite mover o cursor para clulas especficas quando digitamos sua referncia. Por exemplo: se digitarmos BC123 o cursor ser transportado para a coluna BC, linha 123. 18. Galeria: Na imagem acima, a Galeria Fonte est destacada. As galerias agrupam os comandos por suas similaridades. Perceba que na Galeria Fonte somente aparecem os comandos referentes a formatao de fonte. Para cada Guia h um conjunto diferente de Galerias. Na Guia Incio encontramos: rea de Transferncia, Fonte, Alinhamento, Nmero, Estilo, Clulas e Edio. Nem todos os controles esto disponveis. H muitas outras funes de formataoAN OT AE S

Pgina: 11 / 179


Apostila Excel 2007

de fonte que podem ser acessadas quando clicamos no boto Formatar Clulas. Esse boto est localizado na parte inferior da Galeria, representado por uma seta. Veja a imagem ao lado e a seleo do boto Formatar Clulas. Quando pressionado a imagem abaixo exibida. Veja que a mesma tela da verso de formatao de clulas da verso do Excel 2003.

19. Selecionar Tudo: quando pressionado esse boto, todas as linhas e colunas da sua planilha so selecionadas. 20. RIBBON ou FAIXA DE OPES: Principal mudana na verso 2007 do Excel e dos aplicativos do pacote Office. A faixa de Opes ou Ribbon fornece todos os comandos antes encontrados no Excel nas barras de ferramentas de forma categorizada. Perceba que as guias fornecem a categoria principal do recurso que ser acessado e, dentro das guias, separado por galerias encontram-se os comandos. Para utilizar o comando basta clicar uma vez com o boto esquerdo do mouse sobre os botes. Caso tenha dvidas quanto caracterstica do boto, uma ajuda rpida poder ser exibida se o ponteiro do mouse ficar posicionado sobre o boto em dvida. Em alguns segundos uma tela aberta com a descrio da opo, qual sua tecla de atalho (caso tenha) e uma dica do que a ferramenta pode fazer. Veja ao lado um exemplo de tela de ajuda para o recurso Mesclar e Centralizar. 21. Barra de rolagem Vertical: Quando arrastada para baixo ou para cima h uma rolagem na rea de visualizao das clulas.

AN OT AE S

Pgina: 12 / 179


Apostila Excel 2007

22. Dividir Janela Verticalmente: Quando arrastada para o centro da tela permite a criao de uma diviso de janelas vertical em sua planilha. Ideal para visualizao de reas de colunas distintas ao mesmo tempo. 23. Zoom: O zoom pode ser aumentado e diminudo de acordo com a rolagem do controle de Zoom. Podemos tambm pressionar o boto + ou para interagir com o nvel de zoom na planilha. Ao clicar sobre a porcentagem de zoom exibida, uma janela aberta (muito semelhante a janela de Zoom da verso do Excel 2003). Veja a imagem ao lado. H uma outra forma tambm de aumentarmos ou diminuirmos o Zoom. Desta vez podemos utilizar o mouse e um atalho de teclado. Todo mouse possui um boto de rolagem, geralmente localizado entre os botes esquerdo e direito. Para aumentar o Zoom: segure a tecla CTRL pressionada. Mova o rolete para cima, voc ver que o zoom ser incrementado. Movendo o rolete para baixo o zoom diminudo. Experimente! 24. Barra de rolagem horizontal: Quando rolada para a direita ou para a esquerda permite a navegao pelas colunas de sua planilha. 25. Modos de visualizao: H trs formas de visualizao dos dados em sua planilha. a) O modo normal geralmente utilizado para a criao de frmulas e utilizao de funes. b) Layout de Pgina: Permite a visualizao da planilha em pginas (esse um modo novo de visualizao). c) Permite a configurao das pginas para a impresso atravs da visualizao na planilha das quebras de pgina. 26. Barra de Status: A barra de status trs informaes sobre o ambiente. Podemos encontrar funes pr-configuradas, informaes sobre o modo de trabalho, situao das teclas Caps lock e Num lock, um atalho para salvarmos macros, entre outros recursos. Veja abaixo um exemplo da barra de status com informaes. Para alterar as funes exibidas, basta clicar com o boto direito do mouse sobre qualquer uma das operaes e selecionar as novas funes desejadas.

27. Inserir Planilha: ao clicarmos neste boto uma nova planilha inserida em sua Pasta de Trabalho. O atalho de teclado para esse recurso SHIFT + F11. 28. Planilhas: As planilhas representam os espaos de trabalho do Excel. Por padro so inseridas trs planilhas iniciais. Essa configurao poder ser alterada posteriormente. Para alternar entre as planilhas, basta clicar com o boto esquerdo do mouse sobre a planilha desejada. O Atalho de teclado para navegar nas planilhas CTRL + PAGE DOWN ou CTRL + PAGE UP.

AN OT AE S

Pgina: 13 / 179


Apostila Excel 2007

29. Movimentao pelas planilhas: Esses quatro controles permitem a navegao pelas planilhas. 30. Gravao de Macro: Pressionando esse boto o assistente para gravao de macro exibido permitindo o incio da gravao de uma nova macro. Teclas de atalho Muito til para ganho de produtividade. As teclas de atalho so seqncias de teclas prconfiguradas para executar uma ao. Por exemplo: para abrir uma Pasta de Trabalho nova podemos pressionar CTRL + O. Abaixo as principais teclas de atalho: MENUS E BARRAS DE FERRAMENTAS F10 ou ALT Seleciona a barra de menus e indica nesta verso, qual a tecla de atalho pode ser utilizada para a maioria dos botes. ENTER Abre o menu selecionado, ou executa a ao do boto ou comando selecionado. ALT+BARRA DE ESPAOS Exibe o menu de atalho da barra de ttulo. REDIMENSIONAR FONTES CTRL+SHIFT+F - Abre a janela Formatar Clula, na Guia Fonte TECLAS DE FUNO F1 F2 F4 F5 F6 F7 F8 F9 F10 F11 F12 - Abre a Ajuda ou o Microsoft Office Online. - Edita a clula - Repete a ltima ao. - Escolhe o comando Ir para - Vai para o prximo painel ou quadro. - Escolhe o comando Ortografia (menu Ferramentas). - Estende uma seleo. - Atualiza as clulas - Ativa a barra de menus. - Insere uma Guia para Grfico - Escolhe o comando Salvar como (menu Arquivo).

AN OT AE S

Pgina: 14 / 179

Alessandro Trovato Treinamentos CorporativosSHIFT + TECLA DE FUNO

Apostila Excel 2007

SHIFT+F2 Insere um Comentrio na clula SHIFT+F3 Inserir funo SHIFT+F6 - Vai para o painel ou quadro anterior. SHIFT+F10 - Exibe um menu de propriedades da clula em que o curso est posicionado SHIFT+F11 Insere uma nova planilha na pasta atual. SHIFT+F12 - Escolhe o comando Salvar CTRL + TECLAS DE FUNO CTRL+F3 Janela Gerenciador de Nome CTRL+F4 - Fecha a janela. CTRL+F5 - Restaura o tamanho da janela do documento (depois de sua maximizao). CTRL+F6 - Vai para a prxima janela. CTRL+F7 - Escolhe o comando Mover (menu de atalho da barra de ttulo). CTRL+F8 - Escolhe o comando Tamanho (menu de atalho da barra de ttulo). CTRL+F9 Minimiza a planilha CTRL+F10 Maximiza / Restaura a janela do documento. CTRL+F12 - Escolhe o comando Abrir (menu Arquivo). ALT + TECLAS DE FUNO ALT+F4 ALT+F8 ALT+F11 Encerra o Microsoft Excel Abre a Janela Macro Exibe o ambiente do Microsoft Visual Basic.

CRIAR, SALVAR E ABRIR DOCUMENTOS CTRL+O - Cria um novo documento CTRL+A - Abre um documento. CTRL+W - Fecha um documento. CTRL+B - Salva um documento. MOVIMENTAO E SELEO, CTRL+T Seleciona todas as clulas da planilha ativa. SHIFT + Setas de direo Seleciona as clulas conforme movimentao das setas CTRL + Setas de direo Cursor salta at encontrar a clula que contm os ltimos dados CTRL + HOME Cursor posicionado na clula A1 CTRL + END Cursor posicionado na ltima clula alterada da planilha. CTRL + BARRA DE ESPAOS Seleciona a coluna inteira SHIFT + BARRA DE ESPAOS Seleciona a linha inteiraAN OT AE S

Pgina: 15 / 179

Alessandro Trovato Treinamentos CorporativosLOCALIZAR E SUBSTITUIR TEXTOS CTRL+L - Localiza texto, formatao e itens especiais. CTRL+U - Substitui texto IMPRESSO E VISUALIZAO DE DOCUMENTOS CTRL+P - Imprime um documento. TECLAS DE DIREO.

Apostila Excel 2007

Movimentao pela planilha no modo de visualizao

PAGE UP ou PAGE DOWN - Movimentao pela pgina de visualizao quando ela est com menos zoom. CTRL+HOME - Vai para a primeira pgina de visualizao quando ela est com menos zoom. CTRL+END - Vai para a ltima pgina de visualizao quando ela est com menos zoom.

Vamos praticar?Anote abaixo o que acontece quando voc pressiona a seguinte seqncia de teclas: CTRL + F4 - _____________________________________________________________ F10 - ___________________________________________________________________ CTRL + U - _____________________________________________________________ SHIFT + F3 - _____________________________________________________________ CTRL + K - _____________________________________________________________

Os componentes do ExcelEste aplicativo possui cinco partes fundamentais, que so: 1. Pasta de Trabalho - denominada PASTA todo arquivo que for criado no Excel. 2. Planilha - Uma planilha considerada a parte onde ser executado todo o trabalho por isso esta fundamental. Cada planilha possui no total de 17.179.869.184 clulas (isso mesmo! Dezessete bilhes, cento e setenta e nove milhes, oitocentos e sessenta e nove mil, cento e oitenta e quatro clulas). Um arquivo novo possui trs planilhas para trabalho, denominadas Plan1, Plan2 e Plan3. 3. Coluna - As colunas do Excel so representadas em letras de acordo com a ordem alfabtica crescente sendo que a ordem vai de A at XFD, e so no total de 16.384 colunas.AN OT AE S

Pgina: 16 / 179


Apostila Excel 2007

4. Linha - As linhas de uma planilha so representadas em nmeros, formam um total de 1.048.576 linhas e esto localizadas na parte vertical esquerda da planilha. 5. Clula - As clulas so formadas atravs da interseco (cruzamento) de uma coluna com uma linha e, cada clula tem um endereo referencial que mostrado na caixa de nomes que se encontra na parte superior da Planilha.

ColunaO Excel possui 16.384 colunas representadas por letras de nosso alfabeto, podemos localizar uma coluna na tela do Excel atravs das letras que se encontram acima da rea de trabalho, como no exemplo ao lado, observe a coluna D.

*

As colunas podem ser ajustadas em tamanho para aceitarem corretamente ao dados inseridos. Se uma clula recebe a entrada de valor maior do que ela suporta mostrada a seqncia de smbolos ########## indicando que no coube a representao nmerica. Para aumentar o tamanho da coluna proceda da seguinte forma: 1) Clique 2 vezes com o mouse na unio de 2 colunas (*); 2) Automaticamente a coluna ficar da largura da maior clula preenchida. Ou 1) Posicione o ponteiro do mouse entre as 2 colunas (*) 2) Segure o boto esquerdo do mouse e arraste-o para a direita ou para a esquerda para aumentar ou diminuir a coluna. 3) Quando soltar o boto do mouse a coluna ter a dimenso que vocs desejar.

Ou 1) Clique no campo Selecionar Tudo para selecionar a planilha inteira.

2) Clique duas vezes entre as colunas para dimensionar TODA a planilha. Todas as clulas da planilha sero corretamente adequadas ao maior tamanho do contedo preenchido.

AN OT AE S

Pgina: 17 / 179

Alessandro Trovato Treinamentos CorporativosOu

Apostila Excel 2007

1) Clique com o boto direito na clula que identifica a coluna 2) Clique em Largura da Coluna e faa o ajuste. Ou 1) Clique na Guia Incio 2) Localize a Galeria Clulas 3) Clique no boto Formatar. Uma janela aparecer. Clique sobre a opo Largura da Coluna. A mesma tela acima ser exibida.OBS.: O Excel no trabalha com uma rgua como o Word, ele trabalha com Pontos, Pixels e Centmetros. Quando voc configura uma Coluna est ajustando o nmero provvel de caracteres que cabero na clula. Veja a tabela abaixo:

Uma converso aproximada de pontos e pixels para centmetros mostrada na tabela abaixo.Pontos 18 36 72 108 144 Pixels 24 48 96 144 192 Centmetros 0,635 1,27 2,54 3,81 5,08

Vamos praticar?Defina as seguintes medidas para as colunas: Coluna A = 36 pixels Coluna B = 50 caracteres Coluna C = 6 cm

LinhaO Excel possui 1.048.576 linhas que podem receber formataes individuais, cada linha possui 16.384 clulas. Elas so representadas por nmeros, em ordem crescente e encontram-se a esquerda da planilha.

Linhas Diviso das linhas

Para dimension-las siga os mesmos passos anteriores, mas clicando na barra que as separa conforme indicado no desenho acima.

AN OT AE S

Pgina: 18 / 179

Alessandro Trovato Treinamentos CorporativosEntendendo Funes

Apostila Excel 2007

O Excel uma ferramenta muito poderosa para execuo de clculos. Alm das operaes bsicas (soma, subtrao, diviso, multiplicao, potenciao e porcentagem) pode trabalhar com suas funes internas que agregam muito mais potencialidades. Um detalhe muito importante que uma frmula ou funo no Excel deve ser precedida, obrigatoriamente pelo sinal de = (igual) pois o programa reconhece uma frmula ou funo a partir do momento que for iniciado com o sinal de igual. A funo um mtodo para tornar mais rpido a montagem de frmulas que envolvem clculos mais complexos e matrizes de valores. Existem funes para clculos matemticos, financeiros, estatsticos, datas, texto, entre outros. Por exemplo: vamos analisar a funo =SOMA(A1:C10) Utilizaremos a funo SOMA para somar todos os valores do intervalo da clula A1 at a clula C10, sem voc precise informar clula por clula em sua frmula. Para uma correta utilizao das funes devemos dar preferncia pelo uso das referencias das clulas nas frmulas. Sempre que uma clula for atualizada, automaticamente o resultado final que se utilizou daquela referncia ser atualizado tambm. Utilizando as referncias das clulas nas frmulas voc ficar livre para se preocupar com o que realmente interessa: o RESULTADO FINAL ou QUAL INFORMAO VOC QUER EXTRAIR DA SUA PLANILHA. Sobre funes... A seguir encontraremos uma pequena relao das funes mais utilizadas. O Excel na verso 2007 oferece mais de 300 funes (que podem ser aumentadas com a ativao de Suplementos, baixadas na Internet ou criadas atravs do VBA). Vamos conhecer algumas delas a partir de agora. Lembre-se de que os resultados aparecero na clula onde est digitada a frmula. Os operadores matemticos utilizados pelo Excel

Multiplicao Diviso Adio Subtrao Potenciao PorcentagemAN OT AE S

* / + ^ %

Pgina: 19 / 179

Alessandro Trovato Treinamentos Corporativos1. Mesclar clulas

Apostila Excel 2007

Em algumas situaes, necessitamos juntar ou mesclar clulas para fazerem parte de um ttulo ou representao de um dado que normalmente no caberia em uma clula apenas. A este processo chamamos de Mesclar clulas. O processo para criao muito simples. 1. Selecione as clulas que deseja mesclar.

2. Clique na guia Incio 3. Na Galeria Alinhamento, clique no boto Mesclar e Centralizar. Veja ao lado a imagem do boto em destaque. 4. As clulas sero mescladas. Veja o resultado abaixo.

Observao: Perceba que esse boto de mesclagem composto por 2 partes. A parte principal do boto representada pelo desenho e a parte secundria representada pela seta apontando para a parte inferior da tela. Quando pressionamos a seta o menu de opes ao lado exibido com mais opes de mesclagem.AN OT AE S

Pgina: 20 / 179

Alessandro Trovato Treinamentos CorporativosH outra forma de mesclar as clulas.

Apostila Excel 2007

1. Clique no boto Opes de Alinhamento (conforme destacado na imagem ao lado). 2. Ao ser pressionado, a tela abaixo ser exibida. Perceba que a mesma tela da verso 2003 do Excel. Clique na Guia Alinhamento. 3. Selecione a opo Mesclar clulas. 4. Pressione o boto OK. As clulas sero mescladas.

2. Funo Soma Sintaxe: =SOMA(...) A funo exige uma clula ou um intervalo de clulas para somar. A funo ir somar todos os valores que se encontram na faixa de valores ou matriz de dados selecionada. No exemplo abaixo essa soma ser das clulas A1 at a clula D1. Na frmula =SOMA(A1:D1) os dois pontos indicam AT, ou seja, some de A1 AT D1. A forma de utilizao da funo soma no foge a esta regra. Sempre que quiser somar valores, indique a faixa de clulas desejada. Devemos somente posicionar o cursor na clula que desejamos o resultado ANTES de digitar a funo.

Neste exemplo estamos somando todos os valores do endereo A1 at o endereo D1. A frmula seria digitada como no exemplo, e ao teclar ENTER o valor apareceria. O resultado est na clula E1: 102,5.AN OT AE S

Pgina: 21 / 179


Apostila Excel 2007

Os valores no aparecem formatados, pois ainda no aplicamos a formatao dos mesmos. Outra maneira de voc somar utilizando o Boto da Autosoma. Veja o exemplo: 1. Selecionar os valores que desejar somar. No exemplo abaixo foi selecionada as clulas de A1 at A5 (A1:A5). 2. Clique na guia Incio 3. Na Galeria Edio, clique sobre o boto Autosoma (conforme identificado na imagem ao lado). 4. Automaticamente ser inserida na clula A6 a funo =SOMA(A1:A5). Se a seleo for de linha, o resultado ser exibido direita da seleo. Se a seleo for de coluna, o resultado ser exibido abaixo da seleo. Veja mais um exemplo de Soma Agora voc deseja somar todos os valores dispostos nesta planilha usando uma nica frmula, desta vez voc ter que digitar a frmula:

Para criar a funo, clique sobre a clula D6. Digite =SOMA e abra o parentes. Agora posicione o cursor na primeira clula (A1) com as setas de direo. Segure a tecla SHIFT e com as setas de direo selecione o restante dos valores. Com isso estaremos somando todos os valores numa nica frmula, o que chamamos de somar intervalo de valores. Desta vez voc deseja somar nmeros dispostos de maneira alternada, ou seja, em endereos diferentes. Veja o exemplo ao lado. Perceba que entre as clulas somadas (B5, D5 e F5) h um ponto e vrgula (;). Ele utilizado quando queremos somar clulas no adjacentes. Ele separa tambm argumentos de uma funo.AN OT AE S

Pgina: 22 / 179


Apostila Excel 2007

Se voc deseja realizar uma soma simples pode tambm utilizar a forma direta de somar, digitando-se = (igual) e indicando ao Excel, atravs das setas de direo ou clicando sobre as clulas com o boto esquerdo do mouse, quais clulas voc deseja somar. Utilizando o teclado ou mouse, aps selecionar uma referncia pressione o sinal de + (adio) e leve o cursor at a clula que deseja selecionar. 3. Subtrao Ao contrrio da funo SOMA no existe uma funo chamada SUBTRAIR. Para contas de subtrao os valores devem ser indicados um a um. No exemplo abaixo voc deseja saber qual o salrio lquido de um funcionrio. 1) 2) 3) 4) Digite o valor do Salrio Bruto na clula B2. Digite o valor total dos descontos na clula C2. Digite a frmula da subtrao na clula D2 (=B2-C2) Pressione Enter. O resultado ser exibido.

4. Multiplicao A funo MULT tem a funo de multiplicar os valores apresentados como argumentos. Podem ser uma faixa de valores ou uma matriz de dados. Sintaxe: =MULT(nm1;nm2;...)A funo exige quais valores devero ser multiplicados ou ainda uma matriz de dados.Repare que os argumentos so separados por um ponto e vrgula (;)

Outra maneira de efetuar o clculo Voc pode utilizar a mesma forma utilizada na subtrao, mas utilizaremos a referncia das clulas para efetuar os clculos e o operador da multiplicao * (asterisco)

AN OT AE S

Pgina: 23 / 179


Apostila Excel 2007

5. Diviso Para se dividir um valor, utilizaremos tambm o mesmo mtodo. Indicaremos as referncias das clulas separadas pelo smbolo da diviso (/). No exemplo abaixo temos uma conta de Restaurante que deve ser dividida por sete pessoas. Quanto cada um deve pagar?

6. Porcentagem Porcentagem ou percentagem a frao de um nmero inteiro expressa em centsimos. Representa-se com o smbolo % (que se l "por cento"). Os clculos de porcentagens so muito usados para avaliao de resultados ou comparativos entre resultados. No Excel o clculo da porcentagem se realiza multiplicando-se o valor pela porcentagem que se deseja obter. Devemos preferencialmente utilizar as referncias das clulas para a operao. Veja o exemplo: Um cliente de sua empresa fez uma compra semestral antecipada. Voc concedeu um desconto de 7% sobre o valor total. Veja como ficaria a frmula no campo Desconto (C3).

IMPORTANTE: Uma considerao importante sobre a porcentagem que no devemos somar ou subtrair valores utilizando o smbolo de porcentagem. As duas nicas operaes possveis com o Excel so a multiplicao e a diviso.

AN OT AE S

Pgina: 24 / 179


Apostila Excel 2007

Quando multiplicamos um valor por sua porcentagem, o resultado ser somente o valor dessa porcentagem. Se voc quiser somar o valor da porcentagem com o principal, devemos fazer a operao abaixo. Veja o exemplo. Gostaria de somar 9,5% ao valor de R$ 7.500,00. Qual o resultado final?

No exemplo acima observe que iniciamos a frmula com a referncia A7 (Valor) para depois somar pela multiplicao do valor (A7) e a porcentagem (B7). O Excel utiliza as mesmas prioridades das operaes, primeiro realiza a multiplicao e diviso para depois realizar a soma e a subtrao. Outra forma de realizar a operao multiplicarmos o valor do principal pelo valor decimal da porcentagem. Por exemplo: 10% o mesmo que 0,1 25% o mesmo que 0,25 1% o mesmo que 0,01 Para achar o valor de 10% de R$ 7.500,00 multiplicaria: R$ 7.500,00 * 0,1 = R$ 750,00 Para achar o valor de 3% de R$ 7.500,00 multiplicaria: R$ 7.500,00 * 0,03 = R$ 225,00 Para somar 10% ao valor de R$ 7.500,00 multiplicaria: R$ 7.500,00 * 1,1 = R$ 8.250,00 Para descontar 10% de R$ 7.500,00 multiplicaria: R$ 7.500,00 * 0,9 = R$ 6.750,00 Dica: Esta forma de se calcular porcentagem mais simples, pois fazemos a conta diretamente, utilizando o valor decimal que representa a porcentagem. No caso da ltima operao para subtrair uma porcentagem devemos colocar na frmula quando sobrar aps a retirada. No exemplo com 10% de desconto sobraro 90% ou 0,9 em notao decimal. 7. Funo Mximo Em uma matriz de dados ou faixa de valores, exibe o maior valor lanado (valor mximo). Sintaxe: =MXIMO(...) A funo exige uma faixa de valores ou matriz de dados.

AN OT AE S

Pgina: 25 / 179


Apostila Excel 2007

Exemplo: Em uma linha de produo, o supervisor deseja saber qual o apontamento mais alto lanado durante um determinado perodo. Veja o resultado abaixo.

Onde: (B2:E6) matriz de valores que sero verificados pela funo MXIMO. 8. Funo Mnimo Em uma matriz de dados ou faixa de valores, exibe o menor valor lanado (valor mnimo). Sintaxe: =MNIMO(...) A funo exige uma faixa de valores ou matriz de dados. Exemplo: Em uma linha de produo, o supervisor deseja saber qual o menor valor de apontamento lanado durante um determinado perodo. Veja o resultado abaixo.

Onde: (B2:E6) matriz de valores que sero verificados pela funo MNIMO. 9. Funo Maior

AN OT AE S

Pgina: 26 / 179


Apostila Excel 2007

A funo MAIOR muito parecida com a funo Mximo. Ela retorna o maior valor de uma matriz de dados dependendo da posio na escala desejada (o 2 maior, o 3 maior e assim por diante). Sintaxe: =MAIOR(matriz;k) Matriz faixa de valores que sero analisados ; separador dos argumentos da funo k posio de referncia do valor a ser encontrado. Exemplo: Na matriz abaixo, retornar o 2 e o 3 maior valor.

10.

Funo Menor

A funo MENOR faz a operao inversa da funo Maior. Ela retorna, dependendo da posio desejada, qual o menor nmero de uma matriz de dados (o 2 menor, o 3 menor, e assim por diante). Sintaxe: =MENOR(matriz;k) Matriz faixa de valores que sero analisados ; separador dos argumentos da funo k posio de referncia do valor a ser encontrado. Exemplo: Na matriz abaixo, retornar o 2 menor valor e o 3 menor valor.

AN OT AE S

Pgina: 27 / 179

Alessandro Trovato Treinamentos Corporativos11. Funo Mdia

Apostila Excel 2007

A funo utilizada para calcular a mdia de uma faixa de valores ou uma matriz de dados. Aps serem somados, os valores so divididos pela quantidade de valores utilizados. Sintaxe: =MDIA(...) A funo exige uma faixa de valores ou matriz de dados. Exemplo: Em uma linha de produo, o supervisor deseja saber qual a mdia dos valores lanados durante um determinado perodo. Veja o resultado abaixo.

DICA: Outra maneira de se calcular somando os termos e dividindo pelo nmero de elementos. Futuramente voc poder utilizar uma funo de contagem para substituir o ltimo argumento da funo: =SOMA(B2:E6)/20 12. Funo Cont.nm A funo CONT.NM deve ser utilizada para contar quantas clulas de uma faixa de valores contm nmeros. Use CONT.NM para obter o nmero de entradas em um campo de nmero que esto em um intervalo ou matriz de nmeros. Esta funo NO CONTA textos inseridos na planilha. A funo conta somente nmeros. Sintaxe: =CONT.NM(...) A funo exige uma faixa de valores ou matriz de dados. Exemplo: Na amostragem de produo, contar quantos nmeros de apontamentos foram realizados.

AN OT AE S

Pgina: 28 / 179

Alessandro Trovato Treinamentos Corporativos13. Funo Cont.Valores

Apostila Excel 2007

A funo CONT.VALORES utilizada para contagem de clulas preenchidas em uma planilha. Em uma planilha pequena, utilizamos o visual para identificar e fazer a contagem. Em uma planilha grande, para no cometermos erros devemos utilizar a funo CONT.VALORES. No exemplo ao lado, a funo contar somente as clulas que estiverem preenchidas na faixa de valores de C4 a C13. Conforme formos marcando os campos de pago, a funo automaticamente recalculada exibindo a quantidade correta de pagantes. Sintaxe: =CONT.VALORES(...) A funo exige uma faixa de valores ou matriz de dados. 14. Funo Contar.Vazio Funo utilizada para contagem de clulas vazias dentro de uma matriz de dados. Sintaxe: =CONTAR.VAZIO(intervalo) O intervalo deve ser a faixa de clulas que se deseja contar. Exemplo: Quantos apontamentos em branco foram feitos na planilha abaixo?

Observe que o resultado (5) refere-se s clulas vazias no conjunto de dados (de B2 at E6)

AN OT AE S

Pgina: 29 / 179

Alessandro Trovato Treinamentos Corporativos15. Funo Hoje

Apostila Excel 2007

Esta funo insere a data do sistema operacional automaticamente em uma clula selecionada em sua planilha. Essa funo muito utilizada em planilhas utilizadas como matrizes, pois no h a necessidade da digitao constante da data. Requer um pouco de ateno, pois se esta funo for inserida por exemplo em um oramento, exibir sempre a data em que o arquivo estar sendo aberto. Sintaxe: =HOJE() A funo no exige parmetros adicionais. Veja o exemplo:

Para inserir rapidamente a data atual na sua planilha pressione CTRL + ;(CONTROL + PONTO E VRGULA)

16. Funo Agora A funo agora insere automaticamente a DATA e a HORA do sistema operacional na clula selecionada. Sintaxe: =AGORA() A funo no exige parmetros adicionais. Veja o exemplo:

17. Funo Hora A funo HORA extrai a hora de uma clula que contenha uma hora vlida.

AN OT AE S

Pgina: 30 / 179


Apostila Excel 2007

Sintaxe: =HORA(nm_srie) num_srie refere-se a clula que possui da hora completa. Veja o exemplo:

Para inserir rapidamente a HORA em uma clula selecionada pressione CTRL + SHIFT + :(CONTROL + SHIFT + DOIS PONTOS)

18. Funo Minuto A funo MINUTO extrai os minutos de uma clula que contenha uma hora vlida. Sintaxe: =MINUTO(nm_srie) num_srie refere-se a clula que possui da hora completa. Veja o exemplo:

19. Funo Segundo A funo SEGUNDO extrai os segundos de uma clula que contenha uma hora vlida. Sintaxe: =SEGUNDO(nm_srie) num_srie refere-se a clula que possui da hora completa. Veja o exemplo:

AN OT AE S

Pgina: 31 / 179


Apostila Excel 2007

20. Funo Tempo A funo TEMPO utilizada para unir as clulas separadas pelas funes HORA, MINUTO e SEGUNDO formando uma nova clula com a hora completa. Pode ser utilizada tambm para concatenar esses valores se sua planilha deriva de um sistema antigo cujas informaes so dadas em colunas separadas. Sintaxe: =TEMPO(hora;minuto;segundo) hora: clula que possui o valor das horas (B3) minuto: clula que possui o valor dos minutos (B4) segundo: clula que possui o valor dos segundos (B5) Veja o exemplo:

Observao: O resultado da funo TEMPO uma clula formatada no formato AM/PM. Para convert-la para o padro 24h devemos formatar a clula. 21. Funo Dia A funo DIA utilizada para extrair, de uma data, o valor inteiro do dia. Sintaxe: =DIA(nm_serie) A funo exige apenas uma data para a extrao do dia. Veja o exemplo.

AN OT AE S

Pgina: 32 / 179


Apostila Excel 2007

22. Funo Ms A funo MS utilizada para extrair, de uma data, o valor inteiro do ms. A palavra ms deve ser acentuada. Sintaxe: =MS(nm_serie) A funo exige apenas uma data para a extrao do ms. Veja o exemplo:

23. Funo Ano A funo ANO utilizada para extrair, de uma data, o valor inteiro do ano. Sintaxe: =ANO(nm_serie) A funo exige apenas uma data para a extrao do ano. Veja o exemplo:

Exemplo utilizando as 3 funes (DIA, MS e ANO):

AN OT AE S

Pgina: 33 / 179


Apostila Excel 2007

NA PRTICA: Podemos utilizar essas funes quando precisamos agrupar as datas por ms, por dia ou por ano, o que no d para ser feito quando as datas esto lanadas em uma nica clula. 24. Funo Data

A funo DATA utilizada para juntar as clulas DIA, MS, ANO. A funo junta esses dados para formar uma data vlida. Esta data poder ser utilizada para realizao de clculos inclusive. Sintaxe: =DATA(ano;ms;dia) Deve-se informar a funo as clulas que contm o ano, ms e dia nesta ordem para composio do ano. Veja o exemplo:

25.

Funo Esquerda

A funo ESQUERDA utilizada para extrair parte do texto esquerda de uma clula. Deve-se indicar qual a clula que possui o texto e a quantidade de caracteres que se deseja extrair. Sintaxe: =ESQUERDA(texto;nm_caract) Texto refere-se a clula que contem o texto. Nm_caract refere-se ao nmero de caracteres (letras) que se deseja extrair. Veja o exemplo:

Resultado: Extrado 17 caracteres do texto localizado em A2 (Treinamento de Ex). 26. Funo Direita

A funo Direita utilizada para extrair parte do texto direita de uma clula. Deve-se indicar qual a clula que possui o texto e a quantidade de caracteres que se deseja extrair.AN OT AE S

Pgina: 34 / 179


Apostila Excel 2007

Sintaxe: =DIREITA(texto;nm_caract) Texto refere-se a clula que contem o texto. Nm_caract refere-se ao nmero de caracteres (letras) que se deseja extrair. Veja o exemplo:

Resultado: Extrado 15 caracteres do texto localizado em A2 (amento de Excel). 27. Funo Ext.texto

A funo EXT.TEXTO utilizada para extrair parte do texto de acordo com o nmero de caracteres estipulado e a partir de uma posio pr-definida. Utilizada quando a funo esquerda e a funo direita no atendem a necessidade. Sintaxe: =EXT.TEXTO(texto;nm_inicial;nm_caract) Texto refere-se a clula que contem o texto. Nm_inicial refere-se a posio inicial de onde ser extrado o texto. Nm_caract refere-se ao nmero de caracteres (letras) que se deseja extrair. Veja o exemplo:

Resultado: Extrado 10 caracteres do texto localizado a partir da posio 5 (namento de). 28. Funo Concatenar

A funo CONCATENAR muito utilizada para agrupar duas ou mais clulas para formar um resultado nico. Podemos juntar resultados de frmulas, trechos de textos, datas e qualquer informao que esteja contida em clulas alternadas. A funo tambm permite que criemos cdigos nicos de produtos quando h duplicidades de registros em nossa base de dados. Podemos unir um registro duplicado com um campo de identificao para criar campos nicos.

AN OT AE S

Pgina: 35 / 179


Apostila Excel 2007

Sintaxe: =CONCATENAR(...) (...) insira vrias clulas separadas pelo ; (ponto e vrgula)

Resultado: Em cada uma das linhas de Resultado ocorre a juno das clulas das colunas A, B e C. Observe que existe um separador entre os campos. Se no for colocado o separador, o resultado da primeira frmula seria (Show MusicalBanda EstrelaEstdio Aberto), observe que o resultado da frmula fica ilegvel. Para colocar separadores, insira entre os ponto e vrgula o separador barra / . Exemplo: =CONCATENAR(A2; / ;B2; / ; C2) 29. Funo Texto

A funo TEXTO converte valores para o texto. Pode ser utilizado quando necessrio converter valores para texto e ainda aplicar formatao especfica sobre esta converso. Sintaxe: =TEXTO(valor;formato_texto) Valor: clula que contem o valor a ser convertido para texto. Formato_texto: formato que pode ser aplicado ao texto.

Resultado: No exemplo acima, convertemos o texto 100 para o texto R$ 100,00 formatado como moeda. Observe o parmetro de formato (R$ 0,00). 30. Funo Maiscula

A funo MAISCULA converte valores digitados em minscula para maiscula. utilizado quando a planilha j est pronta e deseja-se converter os valores sem redigit-los. Sintaxe: =MAISCULA(texto) Texto clula em que o texto se encontra.

AN OT AE S

Pgina: 36 / 179


Apostila Excel 2007

31.

Funo Minscula

A funo MINSCULA converte valores digitados em letras maisculas para minsculas. utilizado quando a planilha j est pronta e deseja-se converter os valores sem redigit-los. Sintaxe: =MINSCULA(texto) Texto clula em que o texto se encontra.

32.

Funo Pri.Maiscula

A funo PRI.MAISCULA converte a primeira letra de uma palavra para maiscula. Muito til para converso de nomes quando digitados em letras minsculas. As emendas de nomes (de, dos, das, do, etc.) tambm so convertidas. Sintaxe: =PRI.MAISCULA(texto) Texto clula em que o texto se encontra.

33.

Funo Romano Lembra-se o quanto era difcil ficar guardando na escola que a letra M representava 1000 em algarismos romanos? Lembra aquelas converses de nmero complicadas? Com a funo Romano isso deixou de existir. Veja abaixo a sintaxe do comando. Sintaxe: =ROMANO(nm, forma) nm clula em que o nmero se encontra.

AN OT AE S

Pgina: 37 / 179


Apostila Excel 2007

Forma H quatro formas diferentes de exibio, a clssica o nmero 0 (zero). Veja o exemplo:

34.

Funo Valor

A funo VALOR utilizada quando importamos arquivos de texto para o Excel e h necessidade de realizar clculos com esses valores. Ao trazer os dados para a planilha, estes so inseridos nas clulas no formato de Texto. No Excel 2003 algumas funes no trabalham com nmeros formatados para texto, ento h a necessidade da converso. Sintaxe: =VALOR(texto) Texto clula em que o texto se encontra. Veja o exemplo:

Se a funo retornar #VALOR! Significa que voc tentou converter um texto para valor invlido. Corrija a frmula. Veja o exemplo:

35. Funo Mod A funo MOD retorna o resto depois da diviso de um nmero por seu divisor. uma funo til para checarmos mltiplos ou para checar se os nmeros so pares ou mpares. O resultado possui o mesmo sinal que divisor. Sintaxe: =MOD(nm;divisor)AN OT AE S

Pgina: 38 / 179


Apostila Excel 2007

Nm: o nmero para o qual voc deseja encontrar o resto. Divisor: o nmero pelo qual voc deseja dividir o nmero. Veja um exemplo. Estou checando qual o resto da diviso da clula A1 pela clula B1. O resultado da funo MOD 0 (zero) pois representa o resto da diviso de 74 por 2.

36.

Funo Rept

A funo REPT repete um caractere pelo nmero de vezes que for estipulado. Essa funo serve para incrementar nossos dados criando um pequeno grfico ilustrando graficamente o resultado de sua frmula ou funo. Sintaxe: =REPT(texto;nm_vezes) Texto clula em que o texto se encontra ou o texto que voc deseja repetir. Nm_vezes nmero de vezes que voc deseja repetir o caractere. Normalmente utilizamos uma clula com o resultado para referncia. Veja o exemplo:

| caractere barra vertical . Todo caractere deve estar entre aspas. Ax clula que contm o total de vezes que desejo repetir a barra.

AN OT AE S

Pgina: 39 / 179


Apostila Excel 2007

Observao: Para conjuntos de nmeros muito altos recomendo inserir uma regra de trs para fazer a proporo das barras corretamente. Imagine que voc deseja desenhar em uma escala de 100 o valor de 80 barras. Ficar muito largo. Veja o exemplo abaixo:

Para representar um nmero grande de amostras calculamos com uma regra de trs a proporo: Caso 1: 99 est para 100 assim como X est para 10, onde X o valor que apuramos. Se suas amostras forem maiores do que 100 o clculo poder ser substitudo o fator pelo maior valor da sua amostragem. 37. Funo Int A funo INT arredonda um nmero para baixo at o nmero inteiro mais prximo. Muito indicado para resultados que no podem exibir nmeros decimais, interferindo no resultado final. A funo exige apenas um argumento que o valor ou a operao que resultar no valor final para extrao. Veja duas situaes nos exemplo abaixo. O valor positivo foi arredondado para 8 enquanto o valor negativo foi arredondado para -9 pois o valor inteiro mais prximo. No outro exemplo h um clculo de idade.

AN OT AE S

Pgina: 40 / 179

Alessandro Trovato Treinamentos Corporativos38. Funo Arred

Apostila Excel 2007

A funo ARRED arredonda um nmero com casas decimais, at uma quantidade especificada de dgitos. Esta funo utilizada para controle do nmero de casas decimais que um resultado dever apresentar. Sintaxe: =ARRED(nm;nm_digitos) Nm o nmero que voc deseja arredondar. Nm_dgitos especifica o nmero de dgitos para o qual voc deseja arredondar nm. Observaes Se nm_dgitos for maior que 0, ento nm ser arredondado para o nmero especificado de casas decimais. Se nm_dgitos for 0, ento nm ser arredondado para o inteiro mais prximo. Se nm_dgitos for menor que 0, ento nm ser arredondado para a esquerda da vrgula decimal. Exemplos: =ARRED(2,15; 1) Arredonda 2,15 para uma casa decimal. =ARRED(-1,475; 2) Arredonda -1,475 para duas casas decimais =ARRED(21,5; -1) Arredonda 21,5 p/ uma casa esquerda da vrgula Resultado 2,2 Resultado -1,48 Resultado 20

39.

Validao

Para formulrios, normalmente utilizamos o comando de Validao para restringir o tipo de informao lanadas pelos usurios. Por exemplo, em um campo idade, restrinja a faixa etria, para controle de acesso a uma festa, a um concurso, etc... Veja o exemplo: Neste exemplo, foi estipulado que o campo permite somente idade entre 18 e 25 anos. O usurio digitou 50 ento o erro foi apresentado. Estipulei tambm que abaixo do campo de entrada de dados da idade, uma caixa descritiva mostraria ao usurio os valores que deveria digitar. Ela chamada Caixa de Entrada.AN OT AE S

Pgina: 41 / 179


Apostila Excel 2007

Abaixo segue a seqncia de telas para chegar a esta configurao. 1. Clique na Guia Dados. 2. Localize a galeria Ferramentas de Dados. 3. Clique no boto VALIDAO. Veja o boto em destaque ao lado. Ser exibida a tela abaixo.

Neste ponto definimos que permitido somente Nmeros Inteiros, com faixa de dados entre 18 e 25.

Neste ponto definimos a mensagem que ser exibida quando o usurio colocar o cursor sobre o campo ou quando a clula receber o foco.

AN OT AE S

Pgina: 42 / 179


Apostila Excel 2007

Neste ponto definimos a mensagem que ser exibida quando o usurio inserir um valor invlido na clula com a validao.

A validao tambm poder ser feita com base em uma lista de dados. Muitas vezes mais fcil fazer a validao com base em dados j existentes. Para fazer a validao por uma lista, devemos inicialmente criar a faixa de dados quer servir como base. Veja ao lado um exemplo. 1. Selecione a clula ou as clulas que recebero a validao.

2. 3. 4. 5. 6.

Clique na Guia Dados. Localize a Galeria Ferramentas de Dados. Clique no boto Validao. No campo Permitir escolha LISTA No campo Fonte clique no boto de captura de clulas localizado no canto direito da

caixa de preenchimento ( ). Marque da primeira ltima clula da sua lista (BA2: B11). 7. Clique no boto OK. As clulas selecionadas sero validadas. As mensagens de entrada e de erro podero ser preenchidas posteriormente. Veja acima o resultado final. A lista de departamentos lanada servir como base para a caixa de seleo da Validao.AN OT AE S

Pgina: 43 / 179


Apostila Excel 2007

Existe uma desvantagem nesse mtodo. Se voc aumentar a lista de departamentos ver que a lista no atualizada. Para que ela seja reconhecida h necessidade do acerto da rea de dados da validao. Podemos utilizar nesse caso uma lista de dados dinmica, utilizando as funes DESLOC, CONT.VALORES e nomeao de clulas para executar esse recurso. Veja o tpico DESLOC para mais informaes. Outra forma utilizando a atribuio de nomes para a validao tambm. Digamos que eu tenha nomeado os departamentos acima e atribudo o nome DEPTO para os departamentos. Repito o procedimento acima, mas, na hora de informar a faixa de clulas que compem a lista vou substituir pelo nome atribudo. Veja a tela abaixo.

Se o sinal de = (igual) for esquecido, a caixa de validao exibir somente o texto DEPTO. No se esquea dele se estiver utilizando um nome para a faixa de dados. 40. Formatao Condicional Funciona de forma similar a formatao convencional, a diferena que a formatao obedece a um critrio. Por exemplo: Em uma planilha de vendas, voc quer assinalar as que tiveram valor igual ou superior a R$ 100,00. Para executar a formatao deve-se em primeiro momento selecionar as clulas onde os valores sero verificados. 1. Selecione os dados que tero a formatao condicional. 2. Clique na Guia Incio. 3. Localize a galeria Estlo.

AN OT AE S

Pgina: 44 / 179


Apostila Excel 2007

4. Clique no boto Formatao Condicional (veja imagem em destaque acima). Um menu de opes como ao lado ser exibido. 5. Clique na opo Realar Regras das Clulas. No menu que se abrir voc ter os critrios para seleo. Vamos escolher o tipo maior do que. Uma nova janela se abrir. Veja abaixo.

6. Na parte esquerda, definiremos o valor formatar. Na segunda parte h formatos pr-configurados. Caso no deseje nenhuma das opes disponveis clique em Formato Personalizado. Ao concluir, clique no boto OK. Veja o resultado final ao lado. Observe que os valores maiores que R$ 100,00 esto marcados. Caso deseje selecionar o valor de R$ 100,00 inclusive, teramos que criar uma regra personalizada. No passo 5 deveramos escolher a opo Mais Regras... Essa opo til quando no temos formatos pr-configurados na tela de restries. Veja a tela abaixo. No campo Formatar apenas clulas com: teremos mais opes de restrio. Podemos trocar a restrio para MAIOR OU IGUAL A ... Neste caso teramos os valores iguais a R$ 100,00 tambm formatados.

AN OT AE S

Pgina: 45 / 179


Apostila Excel 2007

Nesta verso foi retirado o limite de trs condies para a formatao condicional. Voc agora pode construir uma lista com diversas checagens para a mesma clula. Para fazer isso, no menu principal da formatao condicional (quando voc clica no boto Formatao condicional), clique em Gerenciar Regras. A tela abaixo ser exibida. Para adicionar nova regra, clique no boto Nova Regra. Veja a tela abaixo com quatro diferentes regras para formatao. As regras superiores tem precedncia ou prioridade sobre as inferiores. Lembre-se de criar suas condies na ordem que deseja que elas sejam avaliadas. Caso precise, clicando nos botes Mover para Cima ou Mover para baixo, sua regra reposicionada na lista.

Aproveite e explore as demais opes da formatao condicional, pois ainda podemos formatar os Primeiros e ltimos Valores (ideal para escalas), Barras de dados, Escalas de cor ou ainda como conjunto de cones. Veja abaixo cada uma dessas opes para nosso conjunto de dados.

AN OT AE S

Pgina: 46 / 179

Alessandro Trovato Treinamentos Corporativos41. Funo Comentrio

Apostila Excel 2007

A funo comentrio utilizada para inserirmos observaes sobre o contedo de uma clula, descrevendo sua utilizao ou que tipo de dados que elas devero conter. Muito til para planilhas que devem ser enviadas para outros usurios para esclarecer sobre o procedimento de preenchimento dos dados. Ao ser inserido um comentrio em uma clula, no canto superior direito aparecer uma setinha vermelha, indicando o comentrio inserido (figura 1). Para exibir o comentrio basta posicionar o mouse sobre a clula (figura 2).

42.

Funo SE

A funo SE utilizada para realizar testes condicionais suas clulas. A funo retorna VERDADEIRO ou FALSO dependendo da condio a que foi submetida a clula ou operao. Sintaxe: SE(teste_lgico;valor_se_verdadeiro;valor_se_falso) Por exemplo: Se o sexo informado em uma clula for masculino, ento execute uma funo atribuda para Homens. Se o sexo informado for feminino, ento execute a uma funo atribuda para Mulheres. Se a nota maior que sete ento constar na clula de situao do aluno o termo aprovado ou ento reprovado. SMBOLOS DE COMPARAO UTILIZADOS NO EXCEL = > >= < = 7 o teste lgico ou condio para a checagem da funo. Aprovado refere-se resposta verdadeira, ou seja, se a condio for verdadeira (a nota for maior ou igual a 7) ento a funo escrever Aprovado. ; (ponto e vrgula) o separador de lista de argumentos. Recuperao refere-se a resposta falso, ou seja, se a condio for falsa (a nota for menor que 7), a funo escrever Recuperao. OBS.: Sempre que uma funo precisar retornar um texto para a clula (Aprovado, reprovado, rejeitado, com defeito, etc...), digite o termo entre aspas. Isso informa a funo que o retorno ser um texto (ou em programao, retornar uma String). Se ela for omitida a funo tentar resolver o termo e na maioria das vezes retornar um erro (normalmente de frmula inconsistente).

AN OT AE S

Pgina: 48 / 179


Apostila Excel 2007

EXEMPLO 2: Vamos verificar o estoque de materiais, informando quando o estoque est no nvel Crtico.

Observe as frmulas:

D2 Refere-se a clula que contm o estoque atual. =18;"Sexo Masculino, maior de idade";"Sexo Masculino, menor de idade")) que outra funo SE, desta vez para tratar a idade do Homem pois se o sexo informado no for F, ser M obrigatoriamente. A funo SE requer para seu aprendizado muita prtica. No deixe de fazer os exerccios desta apostila para fixao do contedo. 43. Funo E

A funo E retornar VERDADEIRO (resultado lgico, no confundir com texto) se todos os argumentos forem verdadeiros; retornar FALSO se um ou mais argumentos forem falsos. Em uma lista de checagens todos os critrios tero quer ser verdadeiros para que a funo retorne o valor VERDADEIRO. Sintaxe: =E(...) A funo exige apenas critrios lgicos para validao. Pode ser quantos desejar. Exemplo: Para que seja autorizado uma compra de produto, todos os gerentes de uma empresa precisam aprovar com um OK uma proposta de compra. Caso algum gerente no aprove o pedido ser rejeitado.

Se um gerente no aprovar a compra, veja o resultado da frmula.

AN OT AE S

Pgina: 51 / 179


Apostila Excel 2007

Na funo SE representada na linha 10 perceba que no foi feito o teste lgico, simplesmente referenciei o resultado da funo E. O retorno da funo E foi um valor lgico FALSO. Neste caso a funo SE executou o argumento FALSO. Podemos unificar a funo SE com a Funo E para ter uma nica clula para a resposta. Veja abaixo a funo na forma utilizada.

44.

Funo OU

A funo OU retornar VERDADEIRO (resultado lgico, no confundir com texto) se APENAS UM dos argumentos for verdadeiro; retornar FALSO se todos os argumentos forem falsos. Em uma lista de checagens pelo menos UM critrio ter quer ser verdadeiro para que a funo retorne o valor VERDADEIRO. Sintaxe: =OU(...) A funo exige apenas critrios lgicos para validao. Pode ser quantos desejar. Exemplo: Em um julgamento, se apenas um juiz considerar o ru inocente ento o julgamento ser reagendado para uma nova deciso.

Agora observe se todos o julgarem culpado.

AN OT AE S

Pgina: 52 / 179


Apostila Excel 2007

Na funo SE representada na linha 10 perceba que no foi feito o teste lgico, simplesmente referenciei o resultado da funo OU. O retorno da funo OU foi um valor lgico FALSO. Neste caso a funo SE executou o argumento FALSO. Podemos unificar a funo SE com a Funo OU para ter uma nica clula para a resposta. Veja abaixo a funo na forma utilizada.

45. Funo erros Em algumas situaes especficas precisamos verificar se o retorno de uma funo ser um erro. Essa checagem necessria para eliminarmos a verificao manual de situaes onde h uma falha em uma funo qualquer criada. Retorna um valor lgico (VERDADEIRO ou FALSO) se referir a qualquer valor de erro (#N/D, #VALOR!, #REF!, #DIV/0!, #NM!, #NOME? ou #NULO!). Sintaxe: =ERROS(valor) A funo exige apenas uma clula que contenha a funo a ser checada. Exemplo1: a funo SOMA foi digitada errada na clula A2 (figura abaixo). Na clula A5 foi digitada a funo para verificao. O retorno for VERDADEIRO, pois h um erro na frmula.

AN OT AE S

Pgina: 53 / 179


Apostila Excel 2007

Exemplo 2: Se o retorno de uma funo PROCV for invlido, deixar o campo em branco para no interferir nos clculos das demais clulas que possuem frmulas. Para resolver este problema vamos utilizar tambm a funo SE, que far a avaliao do resultado.

Na primeira parte do exemplo, o produto cdigo 4 no existe, retornando um erro para a descrio da clula B3. O valor unitrio tambm trazido de uma tabela chamado Produtos, conseqentemente no havendo dados as frmulas do imposto e do total a pagar apresentam erro. No segundo exemplo foi inserida a funo ERROS em conjunto com a funo SE para checar a situao de erro ou no para a funo. Observe a sintaxe do comando: =SE(ERROS(B3);0;PROCV(A3;PRODUTOS;3;0)) A funo SE est aguardando o retorno da funo erros da funo inserida na clula B3. O retorno do teste lgico foi VERDADEIRO pois o cdigo do produto 4 no foi localizado. Com a condio VERDADEIRO foi inserido o valor 0 (zero) na clula, no interferindo nos clculos das demais frmulas da planilha. Se o resultado de B3 fosse um produto, a funo SE iria executar o ProcV corretamente, trazendo a descrio do produto e o valor unitrio para concluso. Veja a imagem abaixo.

AN OT AE S

Pgina: 54 / 179


Apostila Excel 2007

Leia: Se a funo ERROS retornar um valor VERDADEIRO do resultado do PROCV da clula B15, ento preencha o campo como 0 (zero), seno execute a funo Procv. 46. Funo Cont.se Outra funo do Excel que tambm utiliza critrios para poder exibir seus resultados a funo CONT.SE. A funo conta um determinado nmero de valores desde que atenda ao critrio estipulado. Por exemplo: Dentro de uma amostragem de dados voc gostaria de saber quantos registros tem o status de pago. Basta usar a funo CONT.SE.

D2:D16 Faixa de dados onde se encontra o critrio a ser analisado. ; separador de argumentos cancelada Critrio que ser analisado pela funo CONT.SE. O valor do texto tem que estar exatamente como est na planilha de dados. A funo contar somente as clulas que possurem o contedo cancelada. Nos critrios tambm podemos utilizar os operadores de comparao (>, =, 20/02/2010 restringiu a contagem para datas cujo vencimento seja maior que 20/02/2010. A faixa de dados onde o critrio devia ser procurado agora a coluna de vencimento (C2:C16). 47. Funo Somase

Em alguns trabalhos podemos deparar com uma necessidade especfica de somar parte dos valores de uma planilha. Quando precisar resolver esse problema, utilize uma soma condicional que realiza a soma de acordo com critrios pr-definidos. Por exemplo: somar o valor dos pagamentos que tem status de PG.

C2:C11 Faixa de dados onde se encontra o critrio a ser analisado. PG critrio que ser analisado. Deve estar entre aspas.AN OT AE S

Pgina: 56 / 179


Apostila Excel 2007

B2:B11 Faixa de dados onde esto os valores que sero somados. 48. Autofiltro O recurso de Autofiltro utilizado quando quisermos rapidamente restringir a exibio dos dados que esto na planilha, separando-os conforme critrios especficos. Veja o exemplo abaixo. Quero aplicar um filtro a minha base de dados para que somente os dados de venda que tiverem o Item 6 sejam exibidos.

Para aplicar o Autofiltro 1. Posicione o cursor em uma clula da sua tabela de dados. 2. Clique na guia Incio 3. Localize a galeria Edio. 4. Clique no boto Classificar e Filtrar. 5. No menu que ser apresentado, escolha Filtro. Sua planilha ir sofrer uma pequena alterao. Os ttulos de suas colunas agora apresentam um controle (uma seta apontado para a parte inferior da planilha). Veja a imagem abaixo.

AN OT AE S

Pgina: 57 / 179


Apostila Excel 2007

Atravs desses controles que aplicaremos o Filtro. Para filtrar ento o item 06 devemos clicar no controle de filtro do campo PRODUTO. A janela ao lado ser exibida. Todos os produtos esto selecionados. Devemos desmarcar todos e deixar somente o campo Item 6 selecionado. Uma forma de se fazer isso clicar na caixa de controle Selecionar Tudo para que todos os itens sejam desmarcados. Ai sim marcaremos o Item 6. Ao clicarmos no boto OK os dados sero filtrados. Veja a imagem dos dados abaixo filtrados. Veja a rea em destaque. O campo filtrado apresenta o desenho de um filtro. Outras caractersticas da filtragem: a troca de cor do nmero das linhas e as linhas que no correspondem ao filtro so ocultas.

H possibilidade ainda de aplicar um filtro diferenciado para cada tipo de dados. H filtros para campos de texto, campo de valores, entre outros. Veja a imagem do lado direito. O Campo de filtragem de Preo de Custo foi selecionado. O sexto campo do menu alterou-se para Filtros de nmero com diversas possibilidades de filtragem (com comparativo de valores, mdia, primeiros itens, entre outros). Se clicarmos em um campo de texto as opes so diferenciadas. Veja agora a imagem do lado esquerdo. Perceba que as opes agora so especficas para o texto. Para desativar o Autofiltro, basta repetir os passos que fizemos para sua ativao. Quando clicarmos no boto filtro, todos os campos de filtragem sero removidos dos ttulos das colunas.

AN OT AE S

Pgina: 58 / 179

Alessandro Trovato Treinamentos Corporativos49. Subtotais

Apostila Excel 2007

O recurso de SUBTOTAL calcula rapidamente agrupamentos de valores ou dados, dependendo da classificao aplicada a tabela. No exemplo abaixo tenho uma tabela com valores . Podemos agrupar nossos dados por clientes, itens, vendedores, regio. Para cada agrupamento podemos realizar uma contagem de dados, uma soma de valores, entre outras operaes. Na planilha abaixo iremos verificar quando foi faturado por regio.

Um detalhe muito importante que devemos classificar os dados para podermos aplicar os subtotais as suas linhas. Devemos classificar os campos que sero agrupados. Em nosso exemplo quero somar quanto faturei por regio. A classificao da planilha foi feita por regio. Para aplicar a classificao consulte tpico especfico desta apostila. Vamos ento inserir os subtotais. 1. Clique na guia Dados 2. Localize a galeria Estrutura de Tpicos 3. Clique no boto Subtotal. A tela ao lado ser exibida. Observe que a mesma tela da verso 2003 do Excel! 4. No campo A cada alterao em: devemos indicar o campo que est classificado. Em nosso caso Regio. 5. O campo Usar funo permite que utilizemos diversas funes nos dados agrupados. Vamos escolher e funoAN OT AE S

Pgina: 59 / 179


Apostila Excel 2007

soma. 6. O campo Adicionar subtotal a deve indicar o campo que desejamos somar. Neste caso vamos selecionar o campo Valor da Venda. 7. Vou abordar os demais campos mais tarde. Clique no boto OK. 8. Veja abaixo a base de dados com um totalizador para cada regio, abaixo do campo Valor da Venda.

Alguns controles novos foram exibidos. Observe do lado esquerdo da sua planilha. Os nmeros 1, 2 e 3 representam a forma que podemos agrupar os dados. Veja abaixo os exemplos de cada um dos botes pressionados. Boto 1: Os itens sero agrupados exibindo apenas o total geral. Veja a imagem abaixo.

Boto 2: Retrai todos os dados exibindo somente os subtotais.

AN OT AE S

Pgina: 60 / 179


Apostila Excel 2007

Boto 3: Expande todos os dados, exibindo a planilha completa novamente.

Se voc se recorda, ainda preciso explicar opes do menu SUBTOTAL. Substituir subtotais atuais far com que os novos subtotais sejam sobrepostos ao que est ativo na planilha. Pode ser usando por exemplo quando quisermos trocar as funes de agrupamento (soma, contagem, etc). Quebra de pgina entre grupos muito til quando precisarmos imprimir nossa planilha. inserida uma quebra de pgina a cada subtotal, permitindo que cada grupo saia em uma pgina diferente na impresso. Resumir abaixo dos dados se for desmarcado, os subtotais aparecero sobre os itens. Veja a imagem abaixo, fica mais fcil de visualizar a mudana em uma planilha parcial. Observe que o total geral est agora sendo apresentado na linha 2. O total do Leste est na linha 3.

Para remover o subtotal, basta clicar no boto remover todos da janela do SUBTOTAL.AN OT AE S

Pgina: 61 / 179

Alessandro Trovato Treinamentos Corporativos50. Estrutura de Tpicos

Apostila Excel 2007

A estrutura de tpicos pode ser utilizada quando voc precisar agrupar dados, ocultando, por exemplo, partes da planilha at uma totalizao. Neste caso a explicao mais simples se visualizarmos um exemplo. Imagine que sua empresa venda para todas as regies do Brasil, para todos os Estados e para alguns municpios. Gostaria de agrupar as Regies, depois os estados e por ltimo os municpios para visualizao dos dados ou dos totais faturados. Esse agrupamento o mesmo do recurso SUBTOTAL do Excel. nica diferena que voc poder escolher onde ser criada uma estrutura. Seja nas linhas ou colunas. Observe a planilha ao lado. Antes de iniciarmos o processo, devemos classificar as informaes de acordo com os agrupamentos que iremos realizar. A planilha acima est classificada por REGIO, seguida por ESTADO e depois por CIDADE.

Insira totalizadores para cada REGIO, ESTADO e CIDADE. Veja a imagem ao lado. Agora, vamos criar a estrutura de tpicos, primeiro passo, iremos selecionar da linha 2 at a linha 28. Essa primeira estrutura exibir o agrupamento de todos os dados para exibio do total geral. Clique na guia Dados Localize a guia Estrutura de Tpicos Clique no boto Agrupar.

AN OT AE S

Pgina: 62 / 179


Apostila Excel 2007

A planilha ficar com a seguinte caracterstica, veja a imagem abaixo. Observe que do lado esquerdo, antes da representao das linhas apareceram dois controles numerados como 1 e 2 (rea em destaque). Esses controles agrupam as informaes, ocultando as clulas que esto definidas dentro da estrutura. Clicando no nmero 1 as linhas so agrupadas, a planilha ficara como a planilha abaixo.

Ao clicarmos sobre o controle nmero 2 os dados voltam a aparecer, ou seja, as linhas ocultas so exibidas. Agora vamos definir os subtpicos. Vamos agrupar as informaes por regio. Com todas as linhas aparecendo, selecione as linhas 2 at 12 (repare que so as linhas da regio SUL). Clique na guia Dados Localize a guia Estrutura de Tpicos Clique no boto Agrupar Selecione agora as linhas 14 a 27 e repita os ltimos trs procedimentos acima. Observe como ficou sua base de dados ao lado. Um novo controle foi inserido. Agora h 2 nveis de Estrutura. Ao clicar na opo 1 o totalizador geral aparecer. Ao clicar na opo 2 somente os totais da regio aparecero. Clicando na opo 3 todos os dados sero exibidos.

AN OT AE S

Pgina: 63 / 179


Apostila Excel 2007

Veja a imagem abaixo quando o controle 2 foi selecionado.

Vamos agora agrupar as cidades. Deixe todos os registros aparecendo novamente (clique no boto 3) Repita os procedimentos acima s que desta vez selecionando as linhas referentes s cidades. Quando concluir sua planilha dever ter a seguinte visualizao final.

Um novo controle foi acionado (controle 4). Repare na imagem abaixo quando o controle nmero 3 for selecionado. Seus dados sero recolhidos e os totais das cidades sero exibidos. Para concluir, irei inserir uma estrutura para as colunas B e C. Para fazer isso deve-se selecionar as colunas B e C e agrupar com o procedimento utilizado acima. Quando o controle da coluna for recolhido, somente os valores e as regies sero exibidos.AN OT AE S

Pgina: 64 / 179


Apostila Excel 2007

Veja o exemplo abaixo. Observe que novos controles foram inseridos, s que desta vez na parte superior da sua planilha.

Para limpar a estrutura, basta selecionar as linhas ou todos os dados e seguir o procedimento abaixo: Clique na guia Dados Localize a galeria Estrutura de Tpicos. O boto Desagrupar composto por 2 partes. VeOpes Aplica o ja a imagem ao lado. Muitos botes possuem essa do boto. comando caracterstica. Clique na parte de opo. Um pequeno menu ser exibido. Clique em Limpar Estrutura de Tpicos. Sua planilha voltar a ter o status original antes do agrupamento. A opo Caixa de Dilogo da Estrutura de Tpicos apresenta as seguintes possibilidades. Veja em destaque ao lado o boto na guia.

Direo: Indica em que direo que a estrutura de tpicos ir correr para localizar o totalizador e fazer o agrupamento, no caso do Autotpico. Ele procurar linhas de totalizadores para criar a diviso da estrutura sendo que para as linhas sero procurados abaixo dos detalhes a serem agrupados e as colunas direita do detalhe do agrupamento.

AN OT AE S

Pgina: 65 / 179


Apostila Excel 2007

A opo Estilos automticos insere um Estilo padro do Excel para a linha de totais. Coloca o texto em negrito, da linha abaixo do agrupamento em Negrito.

51.

Funo Escolher

A funo ESCOLHER permite que at 29 valores ou funes acopladas a funo sejam executadas com base em um ndice informado como primeiro argumento. Um exemplo prtico voc encontrar abaixo, mas resumidamente, podemos utilizar a funo ESCOLHER para executar a soma acumulativa de valores de meses de faturamento dependendo do nmero do ms informado. O primeiro argumento seria o ms e com base as 12 possibilidades ele executar a funo programada no ndice de argumentos da funo. Sintaxe: =ESCOLHER(nm_ndice;valor1;valor2;...) Nm_ndice: especifica o argumento de valor selecionado. Nm_ndice deve ser um nmero entre 1 e 29, ou uma frmula ou referncia a uma clula que contenha um nmero entre 1 e 29. Se nm_ndice for 1, ESCOLHER retornar valor1; se for 2, ESCOLHER retornar valor2; e assim por diante. Se nm_ndice for menor do que 1 ou maior do que o nmero do ltimo valor na lista, ESCOLHER retornar o valor de erro #VALOR!. Se nm_ndice for uma frao, ser truncada para o menor inteiro antes de ser usada. Valor1,valor2: so 1 a 29 argumentos de valor a partir dos quais ESCOLHER seleciona um valor ou uma ao a ser realizada com base no nm_ndice. Exemplo 1: Com base no ms escolhido, a funo retornar a quantidade de peas produzidas.

Observe a funo: =ESCOLHER(E1;B2;B3;B4;B5;B6;B7;B8;B9;B10;B11;B12;B13)AN OT AE S

Pgina: 66 / 179


Apostila Excel 2007

O ndice foi lanado como primeiro argumento (E1). Ao ser selecionado o ms (3), a funo ESCO

Documents

Apostila EXCEL 2007