Lição 03 - Vinculando Planilhas e Pastas de Trabalho

LIÇÃO 3 - VINCULANDO PLANILHAS E PASTAS DE TRABALHO


a primeira lição, vimos recursos do tipo renomear, mover e excluir planilhas. Nesta lição, vamos aprofundar o assunto planilhas e pastas de trabalho. A

construção de planilhas realmente úteis quase sempre passa pela utilização de mais de uma planilha ou de mais de uma pasta de trabalho.

N

TRABALHANDO COM PLANILHAS AGRUPADAS

Vamos aprender a trabalhar com várias planilhas e pastas de trabalho através de um exercício prático. Aproveitaremos para recordar alguns recursos úteis de formatação, à medida em que formos construindo o modelo.

Prática

Já trabalhamos nas lições anteriores com a pasta de trabalho Folha de Pagamento. Neste exercício, partiremos de uma pasta de trabalho em branco para produzir um arquivo semelhante. O objetivo é uma pasta de trabalho para controlar a folha de pagamento dos meses de janeiro a abril. A cada etapa, comentaremos os recursos utilizados. Vamos começar.

Preparando a área de trabalho

1. Abra uma pasta de trabalho em branco.

2. Dê um duplo clique na guia Plan1 e renomeie a planilha para Jan. Repita o procedimento para Plan2, Plan3 e Plan4, renomeando-as respectivamente para Fev, Mar e Abr.

3. Clique na guia Plan5. Em seguida, clique no botão de visualização da última planilha, mantenha a tecla SHIFT pressionada e, finalmente, clique em Plan16.

4. Clique com o botão direito nas guias selecionadas e utilize a opção Excluir do menu de atalho. Confirme a exclusão.

5. Posicione na planilha Jan.

Inserindo dados em um grupo de planilhas

É possível selecionar um grupo de planilhas e inserir dados simultaneamente. Esse recurso é muito útil quando temos várias planilhas que se assemelham em formato e conteúdo.

6. Certifique-se de que Jan é a planilha ativa.

7. Mantenha a tecla SHIFT pressionada e clique na guia Abr. Com esse procedimento, você agrupou as quatro planilhas disponíveis na pasta de trabalho. Note que na barra de título ativa apareceu a palavra [Grupo]. Isso significa que qualquer informação digitada será inserida nas quatro planilhas de uma só vez.

8. Em C1 insira EMPRESA DE TRANSPORTES MARINGÁ.

9. Em C2 insira RESUMO DA FOLHA DE PAGAMENTO.

10.Em C3 insira MÊS:.

11.Em C5 insira ÍNDICES DE DESCONTOS.

12.Em C6 insira INSS.

13.Em C7 insira IMPOSTO DE RENDA.

14.Em C8 insira PLANO DE SAÚDE.

15.Em A10 insira FUNCIONÁRIO.

16.Em B10 insira SALÁRIO-BASE

23

EXCEL AVANÇADO - LIÇÃO 3

17.Em C10 insira DESCONTOS.

18.Em G10 insira LÍQUIDO.

Não se preocupe se algumas informações não estiverem aparecendo completamente nas células. Não altere a largura das colunas. Aplicaremos o recuo de texto automático a essas células posteriormente. Sua planilha deverá estar parecida com a figura a seguir.

Vamos interromper a digitação para observarmos o que aconteceu até aqui. Veja que a guia Jan está em destaque, porque foi dessa planilha que partimos para formar o grupo de planilhas.

Para desagrupar planilhas, você tem dois caminhos:

19.clicar em qualquer guia de planilha que não seja a guia ativa;

20.clicar com o botão direito nas guias de planilhas e selecionar a opção Desagrupar Planilhas no menu de atalho.

21.NOTA

22.No nosso exemplo, todas as planilhas que compõem a pasta de trabalho estão agrupadas. Nessa situação, se você clicar em qualquer guia de planilha que não seja ativa, conseguirá desagrupar as planilhas. Entretanto, se na pasta de trabalho você possuir mais planilhas que não estejam agrupadas, essa forma não funcionará. Para desagrupar as planilhas nesse caso, você deve clicar na guia de uma planilha que não esteja agrupada. Em qualquer situação, entretanto, o clique com o botão direito nas guias de planilhas sempre resolve.

Desagrupe as planilhas agora. Em seguida, clique em cada guia separadamente e verifique que os dados digitados até agora foram inseridos simultaneamente nas quatro planilhas. Vamos prosseguir a digitação.

23.Clique na guia Jan.

24.Agrupe as quatro planilhas.

25.Em C11 insira INSS. Em D11 insira IMPOSTO DE RENDA.

26.Em E11 insira PLANO DE SAÚDE. Em F11 insira TOTAL DE DESCONTOS.

Se estivéssemos construindo uma planilha para muitos funcionários, com possibilidade de várias alterações de nomes de um mês para o outro, talvez fosse conveniente deixar a coluna de funcionários em branco. O preenchimento dessa

24


coluna seria feito todo mês. Entretanto, podemos considerar a hipótese de incluir ou excluir nomes de funcionários posteriormente, uma vez que efetuar inclusões ou exclusões de linhas é tarefa simples no Excel. Vamos utilizar essa última abordagem, até mesmo porque utilizaremos poucos nomes no nosso exemplo. Assim, digite, a partir da célula A12, os nomes dos funcionários a seguir:

· Ana Maria Moreira Pena

· Antônio Soares da Cruz

· Carlos Roberto Valadares

· Celso Fortunato· Evandro de

Albuquerque Braga· Fabiana Gomes

Carneiro

· Flávio Eduardo de Queirós

· Heitor Alves Ferreira· Jair Moreira da Silva

Benício· José Raimundo Tavares

Salvando a pasta de trabalho

Já temos uma boa quantidade de trabalho realizado e ainda não salvamos nossa pasta de trabalho. Vamos fazer isso agora, salvando-a na pasta adequada.

27.Clique no botão Salvar.

28.Salve o arquivo na pasta Excel Avançado, com o nome Transportes Maringá.

Observe que não salvamos o arquivo como modelo. Vamos analisar essa decisão. Em primeiro lugar, devemos dizer que estamos trabalhando com uma pasta de trabalho para as folhas de pagamento dos quatro primeiro meses do ano apenas por comodidade. Numa situação prática, provavelmente estaríamos trabalhando com doze planilhas, uma para cada mês do ano. Nesse caso, se salvássemos nossa pasta de trabalho como modelo, estaríamos considerando a hipótese de utilizá-la nos próximos anos. Talvez seja complicado pensar no mesmo modelo de folha de pagamento para o próximo ano, porque muita coisa poderia mudar nesse período. Uma solução intermediária seria:

· salvar a pasta de trabalho como arquivo comum, como fizemos acima, para ser utilizada durante o ano corrente; e

· manter uma outra cópia do mesmo arquivo, após concluirmos sua elaboração, sem a inclusão dos dados referentes ao ano corrente. Ao final do ano, seria estudada a viabilidade de usar essa cópia para as folhas de pagamento do próximo ano.

Formatando o grupo de planilhas

Antes de construirmos as fórmulas necessárias, vamos melhorar a apresentação das planilhas. Assim como digitamos os dados de uma só vez, também aplicaremos a formatação às quatro planilhas simultaneamente.

NOTA

· As opções de formatação envolvem a escolha de cores, tipos de bordas, tamanhos e tipos de fontes, negrito, itálico, sublinhado, etc. Na realidade, é uma questão de gosto. Entretanto, é preciso ter em mente que recursos de formatação em exagero podem prejudicar a visualização e o entendimento das informações. Na elaboração dos exercícios deste curso, por exemplo, usamos cores menos carregadas e tipos de bordas mais finas. Nos roteiros dos exercícios, mencionaremos os recursos que utilizamos, mas fica a seu critério aplicar aqueles que lhe parecerem mais apropriados.

29.Certifique-se de que as quatro planilhas estejam agrupadas.

30.Para formatar automaticamente a largura da coluna A, dê um clique duplo na interseção das colunas A e B, no cabeçalho de colunas.

31.Selecione o intervalo C1:F3. Clique no botão Cores e aplique a cor de preenchimento amarelo-claro.

32.Com o intervalo C1:F3 ainda selecionado, clique no botão Tipos de borda e aplique bordas simples ao redor do intervalo.

25


33.Com o intervalo C1:F3 ainda selecionado, clique no botão Negrito.

34.Clique na célula C1 e altere o tamanho da fonte para 12.

35.Selecione C1:F1 e clique no botão Centralizar colunas selecionadas.


37.Selecione o intervalo C5:F9. Clique no botão Cores e aplique a cor de preenchimento cinza-médio.

38.Com o intervalo C5:F9 ainda selecionado, clique no botão Tipos de borda e aplique bordas simples ao redor do intervalo.

39.Com o intervalo C5:F9 ainda selecionado, clique no botão Negrito.


41.Com o intervalo C5:F5 selecionado, clique no botão Tipos de borda e aplique bordas simples ao redor do intervalo.

Nossa próxima etapa será formatar os títulos nas linhas 10 e 11. Vamos continuar.

42.Selecione as linhas 10 e 11.

43.Clique no menu Formatar e escolha o comando Células.

44.Clique na guia alinhamento e no painel Vertical escolha a opção Centralizado. Marque também a opção Retorno automático de texto. Clique em OK.

45.Se você seguiu corretamente os passos até aqui, provavelmente os textos em C10 e F11 não ficaram com uma aparência muito boa. Para acertar isso, aumente um pouco a largura das colunas C e F. Aumente também a largura da coluna B de forma a que o título em B10 passe a ocupar a altura de uma linha.

46.Dê um duplo clique na interseção entre as linhas 10 e 11 e entre as linhas 11 e 12. Esse procedimento ajusta automaticamente a altura das linhas.

47.Clique em B10 e aplique alinhamento centralizado.

48.Clique em G10 e aplique alinhamento centralizado.


50.Selecione C11:F11 e aplique alinhamento centralizado.

51.Selecione o intervalo A10:G11 e aplique negrito. Aplique também uma cor de preenchimento cinza-claro.

52.Selecione o intervalo A10:G21. Clique no botão Tipos de borda e aplique bordas simples externas e internas.

53.Clique no menu Ferramentas e escolha o comando Opções. Na caixa de diálogo Opções, clique na guia Exibir. Desmarque a opção Linhas de grade e clique em OK.

54.Selecione o intervalo B12:G21 e clique no botão Separador de milhares.

55.Pressione CTRL+HOME para ir para o início da planilha.

56.Clique no botão Salvar.

Terminamos a etapa de formatação. Se você quiser mais tarde alterar alguns dos formatos escolhidos, agrupe as planilhas e aplique o formato desejado.

26

DICA

Vale a pena gravar a seqüência de procedimentos dos itens 14 a 18 anteriores. Com essas tarefas simples, você produz títulos bem formatados em suas planilhas.

DICA

Vale a pena gravar a seqüência de procedimentos dos itens 14 a 18 anteriores. Com essas tarefas simples, você produz títulos bem formatados em suas planilhas.


Fórmulas em grupos de planilhas

Com o recurso de planilhas agrupadas, também é possível inserir fórmulas de uma só vez. Essa será a nossa próxima etapa.

57.Inicialmente, vamos preencher os valores dos índices de descontos. Certifique-se de que as planilhas estejam agrupadas e insira nas células F6, F7 e F8, respectivamente, os valores 8%, 15% e 3%.

58.Para nos orientarmos na construção das fórmulas, digite na célula B12 o valor 100.

59.Em C12 insira a fórmula =$F$6*B12.

60.Em D12 insira a fórmula =$F$7*B12.

61.Em E12 insira a fórmula =$F$8*B12.

62.Em F12 insira a fórmula =SOMA(C12:E12).

63.Em G12 insira a fórmula =B12-F12.

64.Selecione o intervalo C12:G12.

65.Copie as fórmulas para as demais células até à linha 21.

66.Apague o valor em B12 e desagrupe as planilhas.

67.Salve e feche o arquivo.

Nomes em planilhas agrupadas

Na lição anterior aprendemos a criar nomes para intervalos que possuem células de texto na linha superior e na coluna à esquerda. Esse recurso também pode ser aplicado de uma só vez, utilizando planilhas agrupadas.

Resumo

Vimos que agrupar planilhas proporciona uma boa economia de trabalho. É possível, de uma só vez, incluir dados, aplicar formatos, trabalhar com fórmulas, alterar opções de configuração (lembra-se de que retiramos as linhas de grade?) e trabalhar com nomes. Existem ainda outras tarefas que podem ser realizadas em planilhas agrupadas, tais como: inserir e excluir linhas e colunas, limpar conteúdo, limpar formatos, trabalhar com o pincel, copiar e colar.

68.NOTA69.No exercício que fizemos, agrupamos planilhas adjacentes. Na primeira lição,

vimos que também é possível agrupar planilhas não adjacentes. Para isso, você deve manter a tecla CTRL pressionada e clicar em cada guia de planilha que deseja agrupar.

Exercício proposto

Suponha que construímos uma pasta de trabalho para a folha de pagamento de todos os meses do ano, semelhante ao exercício que fizemos. Como lidar com a contratação e demissão de funcionários ao longo do período, de forma que cada planilha, em qualquer tempo, apresente corretamente a lista dos funcionários daquele mês, com os respectivos valores de salários e descontos?

VINCULANDO PLANILHAS

Na seção anterior, vimos como trabalhar com planilhas agrupadas para economizar tempo. Mas observe que elaboramos planilhas independentes entre si, apesar de terem sido construídas simultaneamente. Nosso objetivo agora será justamente tratar da interdependência entre planilhas.

Prática 1

27


Abra a pasta de trabalho Folha de Pagamento. Quando trabalhamos com esse arquivo, ele foi salvo na pasta Excel Avançado e, como exercício proposto, sugerimos salvá-lo também como modelo em ...\Modelos\Excel Avançado (as reticências representam o diretório de instalação do Microsoft Office). Você pode abri-lo a partir de qualquer uma dessas pastas.

Vamos novamente trabalhar com o assunto folhas de pagamento. A abordagem será diferente da seção anterior. Lá, produzimos uma pasta de trabalho para “todos os meses do ano”. Dessa vez, vamos utilizar uma pasta de trabalho como modelo para cada mês do ano.

Bem, o que nos interessa agora não é a questão dos modelos. É o seguinte. Repare que os índices de descontos estão visíveis na nossa planilha atual. Uma outra abordagem seria criar uma planilha para conter apenas esses índices. É o que faremos a seguir.

70.Clique com o botão direito sobre a guia Plan1 e escolha a opção Inserir.

71.Na caixa de diálogo Inserir, dê um duplo clique na opção Planilha, da guia Geral.

72.O Excel insere uma planilha chamada Plan2, à esquerda de Plan1. Mova-a para o lado direito de Plan1.

73.Renomeie Plan1 para Folha.

74.Renomeie Plan2 para Índices.

75.Clique na guia Folha.

76.Selecione o intervalo B12:G37 e pressione DELETE para apagar o seu conteúdo.

77.Exclua as linhas 5, 6, 7 e 8.

78.Vamos salvar o arquivo. Se você está trabalhando diretamente com o arquivo de modelo, ótimo. Basta clicar no botão Salvar. Se você abriu o arquivo da pasta c:\Excel Avançado\, aproveite agora para salvar o arquivo como modelo, com o mesmo nome Folha de Pagamento, na pasta C:\...\Modelos\Excel Avançado.

79.Clique na guia Índices.

80.Em A1 insira ÍNDICES DE DESCONTOS.

81.Em A2 insira INSS.

82.Em A3 insira IMPOSTO DE RENDA.

83.Em A4 insira PLANO DE SAÚDE.

84.Formate a tabela de índices a seu gosto.

85.Em C2, C3 e C4 insira, respectivamente, 8%, 15% e 3%.

86.Clique na guia Folha. Insira em B8 o valor 100.

87.Clique em C8 e digite =. Em seguida, clique em B8 e digite o símbolo *.

88.Após o asterisco, clique na guia Índices.

89.Na planilha Índices, clique na célula C2. Em seguida, pressione F4.

90.Pressione ENTER para finalizar a fórmula.

91.Posicione em C8. Observe na barra de fórmulas o conteúdo dessa célula. O Excel inseriu a fórmula: =B8*Índices!$C$2. Note que o nome da planilha precede a referência da célula, separado por um ponto de exclamação.

Nos exemplos que tínhamos estudado até aqui, as fórmulas se referiam sempre a células na mesma planilha. Entretanto, podemos observar agora que é possível referir-se a células em outras planilhas. Mais tarde, veremos que também é possível referir-se a nomes em outras planilhas e até a células e intervalos nomeados em pastas de trabalho diferentes. A vinculação de informações entre planilhas e arquivos diferentes proporciona ao usuário uma ferramenta poderosa

28


para construir modelos eficientes e organizados. Daqui em diante, veremos vários exemplos com essas características.

Vamos terminar o trabalho.

92.Clique em D8 e digite =. Em seguida, clique em B8 e digite *.




96.Clique em E8 e digite =. Em seguida, clique em B8 e digite *.




Falta somente construir a fórmula para o total de descontos e para o salário líquido, o que é moleza para nós. Mais interessante, contudo, é tentar refazer as fórmulas de descontos, utilizando nomes para as células da planilha Índices. Essa é a sugestão do exercício a seguir.

Exercício proposto

Atribua os nomes INSS, IRPF e P_SAUDE, respectivamente, às células C2, C3 e C4 da planilha Índices. Em seguida, refaça as fórmulas de descontos, utilizando os nomes que acabou de criar. Nota: você não precisará nem mesmo clicar na guia Índices para utilizar os nomes daquela planilha. Basta selecioná-los na caixa Nome da barra de fórmulas. Mas lembre-se: para finalizar a fórmula é necessário pressionar ENTER.

Após refazer as fórmulas de descontos, construa as demais fórmulas da planilha e copie para as células das linhas seguintes. Apague o valor em B8 e salve a pasta de trabalho Folha de Pagamento. Feche o arquivo.

Prática 2

Neste exercício, veremos mais um exemplo de vinculação de planilhas.

Abra o arquivo Distribuidora de Bebidas. O objetivo dessa pasta de trabalho é acompanhar o valor das vendas mensais da Distribuidora de Bebidas Beba & Pague, que possui três unidades de distribuição: Ponte Nova, Manhuaçu e Viçosa. Teremos três planilhas para as vendas de cada unidade e uma quarta planilha totalizará os totais da empresa.

100.Agrupe as planilhas Ponte Nova, Manhuaçu e Viçosa.

101.Selecione o intervalo B8:H31 e aplique o formato separador de milhares.

Uma dica para utilizar o botão AutoSoma

Veja que praticamente todas as fórmulas dessa pasta de trabalho conterão a função SOMA. Sabemos que é fácil construir essa função com o botão AutoSoma. Quando trabalhamos com esse botão, geralmente selecionamos o intervalo de valores que queremos somar, acrescentando um intervalo de linhas e/ou colunas adjacentes para conter os resultados. Entretanto, essa forma não funciona quando não há dados na planilha. Vejamos isso na prática.

Vamos construir as fórmulas para o sub-total na linha 11. Selecione o intervalo B8:G11 e clique no botão AutoSoma. Observe que o Excel tenta incluir a soma apenas na célula B8. E se ao invés do intervalo B8:G11, tivéssemos selecionado o intervalo B11:G11? Ainda assim não funcionaria. O que temos a fazer nesse caso é construir a fórmula em B11, digitando-a ou com o auxílio do botão AutoSoma e do mouse e, em seguida, copiá-la para as demais células da linha 11.

29


Uma outra alternativa seria inserir dados fictícios nas células, construir as fórmulas necessárias e ao final apagar os dados de exemplo. O problema dessa solução é o tempo gasto na inserção de dados fictícios. Mas há meios de você economizar esse tempo. Vamos treinar.

102.Certifique-se de que as três primeiras planilhas estejam agrupadas.

103.Digite os valores 10,00, 20,00, 30,00, 40,00, 50,00 e 60,00 para as células do intervalo B8:G8, respectivamente.

104.Agora vamos fazer uma seleção de diversos intervalos não adjacentes. Fique atento.

105.Selecione o intervalo B8:G10.

106.Mantenha a tecla CTRL pressionada e selecione o intervalo B13:G14. Se necessário, use a seta ou barra de rolagem vertical para visualizar as demais linhas da planilha.

107.Mantenha a tecla CTRL pressionada e selecione o intervalo B20:G22.

108.Mantenha a tecla CTRL pressionada e selecione o intervalo B25:G27.

109.Clique em Editar/Preencher/Abaixo. Os valores da primeira linha dos intervalos selecionados são copiados para todas as linhas do intervalo.

110.Vamos aproveitar a oportunidade e nomear o intervalo atual. Clique na caixa Nome na barra de fórmulas, digite DADOS e, em seguida, pressione ENTER.

111.Com o auxílio do botão AutoSoma, insira as fórmulas para os sub-totais nas linhas 11, 15, 23 e 28.

112.Insira em B16 a fórmula =B11+B15. Copie para as células até G16.



115.Para construir as fórmulas da coluna H, vamos selecionar alguns intervalos não adjacentes.

116.Selecione o intervalo H8:H11. Mantendo a tecla CTRL pressionada selecione H13:H16, H20:H23, H25:H29 e finalmente só a célula H31.

117.Clique no botão AutoSoma.

118.Salve a pasta de trabalho.

Totalizando as planilhas

Vamos trabalhar agora com a planilha TOTAIS.

119.Clique na guia TOTAIS. Certifique-se de que as planilhas estejam desagrupadas.

120.Selecione o intervalo B8:H31 e aplique o formato separador de milhares.

121.Clique em B8 e digite =. Vamos construir a fórmula com a ajuda do mouse.

122.Após o símbolo =, clique na guia Ponte Nova e em seguida na célula B8 da planilha Ponte Nova. Digite o símbolo +. Acompanhe a construção da fórmula na barra de fórmulas.

123.Após o símbolo +, clique na guia Manhuaçu e em seguida na célula B8 da planilha Manhuaçu. Digite o símbolo +.

124.Após o símbolo +, clique na guia Viçosa e em seguida na célula B8 da planilha Viçosa.

125.Pressione ENTER para concluir a fórmula.

Todas as células de valores na planilha TOTAIS serão as somas das células correspondentes das planilhas anteriores. Portanto, basta copiar a fórmula em B8 para as demais células da planilha TOTAIS. Vamos ver duas maneiras diferentes de fazer essa cópia.

30


126.Na planilha TOTAIS, clique na célula B8.

127.Clique na alça de preenchimento e arraste para o intervalo C8:H8. Não desfaça a seleção desse intervalo.

128.Além de preencher o intervalo com as fórmulas adequadas, podemos aproveitar a seleção para preencher as linhas abaixo.

129.Clique na alça de preenchimento na célula H8 e arraste para as linhas 9, 10 e 11. Clique em qualquer célula, para desfazer a seleção do intervalo.

Verifique o conteúdo de algumas das células do intervalo B8:H11. Por exemplo, em C10 temos a fórmula ='Ponte Nova'!C10+Manhuaçu!C10+Viçosa!C10, em H9 temos a fórmula ='Ponte Nova'!H9+Manhuaçu!H9+Viçosa!H9. As fórmulas estão corretas.

Entretanto, as bordas no intervalo H8:H11 sofreram uma alteração, devido à utilização da alça de preenchimento. O que acontece nessa situação é que, com a alça de preenchimento, você copia o conteúdo e o formato de uma célula ou intervalo enquanto arrasta para outra(s) célula(s) ou intervalo. Além do problema das bordas, os valores na linha 11 e no intervalo H8:H11 deveriam estar em Negrito, pois quando criamos esse exercício, aplicamos Negrito a essas células. Vamos resolver esses problemas e, na seqüência, veremos uma outra forma de efetuar cópias de células sem o formato.

130.Selecione o intervalo H8:H11.

131.Clique no menu Formatar/Células e selecione a guia Borda. Clique na caixa de borda dupla e em seguida, clique nas caixa Esquerda e Direita. Para finalizar, clique em OK.

132.Ainda com o intervalo H8:H11 selecionado, aplique o formato Negrito.

133.Aplique Negrito ao intervalo B11:G11.

134.Salve a pasta de trabalho.

O comando Colar Especial

135.Clique na célula B8 e em seguida no botão Copiar.

136.Selecione o intervalo B13:H16.

137.Clique no menu Editar/Colar Especial.

138.Selecione a opção Fórmulas e clique em OK.

139.Observe que, nas células de destino, os formatos originais foram conservados.

31

DICA

Convenhamos que utilizar a alça de preenchimento, em muitos casos, é mais rápido e cômodo do que a seqüência Copiar e Colar Especial. Se preferir, pode trabalhar da seguinte forma: construa todas as fórmulas das planilhas, utilize a alça de preenchimento quando necessário e deixe a etapa de formatação para o final do trabalho.

DICA

Convenhamos que utilizar a alça de preenchimento, em muitos casos, é mais rápido e cômodo do que a seqüência Copiar e Colar Especial. Se preferir, pode trabalhar da seguinte forma: construa todas as fórmulas das planilhas, utilize a alça de preenchimento quando necessário e deixe a etapa de formatação para o final do trabalho.


Vamos finalizar a planilha TOTAIS.

140.Clique na célula B8 da planilha TOTAIS e, em seguida, no botão Copiar.

141.Vamos fazer apenas uma colagem para as células restantes. Com o auxílio da barra de rolagem, visualize a parte inferior da planilha.


143.Mantenha a tecla CTRL pressionada e selecione o intervalo B25:H29.

144.Mantenha a tecla CTRL pressionada e selecione o intervalo B31:H31.

145.Clique em Editar/Colar Especial e selecione Fórmulas. Clique em OK.

146.Aperte CTRL+HOME para posicionar em A1. Salve o arquivo.

Problemas na utilização de planilhas vinculadas

Vamos gerar um problema na nossa pasta de trabalho. Certifique-se de que você tenha gravado o arquivo, pois não teremos como desfazer o comando que vamos utilizar.

147.Clique com o botão direito na guia da planilha Manhuaçu.

148.No menu de atalho, selecione Excluir e confirme a exclusão.

149.Clique na guia TOTAIS e veja o que aconteceu com as fórmulas nessa planilha.

Todas as fórmulas retornaram o erro #REF!, uma vez que o programa perdeu a referência a um elemento indispensável da fórmula, no caso, a planilha Manhuaçu. Existe uma outra maneira de trabalhar com planilhas vinculadas que, para o caso atual, evitaria o erro anterior. Trataremos disso na próxima seção.

Como dissemos, não há como desfazer o comando Excluir/Planilha. Portanto, feche o arquivo atual sem salvar.

Fórmulas tridimensionais (3-D)

Se você fechou o arquivo Distribuidora de Bebidas sem salvá-lo, ele está disponível para ser aberto no ponto em que o completamos corretamente. Vamos utilizá-lo para estudar as referências 3-D do Excel.

150.Abra a pasta de trabalho Distribuidora de Bebidas.

151.Clique na guia TOTAIS.

152.Apague todas as células que contêm valores nessa planilha. Vamos reconstruir as fórmulas em seguida, utilizando referências 3-D.

153.Clique na célula B8 e em seguida no botão AutoSoma.

154.Clique na guia Ponte Nova.

155.Mantenha a tecla SHIFT pressionada e clique na planilha Viçosa. Observe que estamos agrupando planilhas dentro de uma fórmula.

156.Note que a planilha Ponte Nova está em destaque, por ter sido a planilha de onde partimos para fazer o agrupamento. Clique agora na célula B8 e pressione ENTER para finalizar a fórmula.

Vamos aos comentários.

Clique em B8 e observe a fórmula que construímos. Vamos compará-la com a que tínhamos elaborado anteriormente.

Fórmula anterior: ='Ponte Nova'!B8+Manhuaçu!B8+Viçosa!B8 Fórmula atual: =SOMA('Ponte Nova:Viçosa'!B8)

Vamos traduzir a linguagem do Excel nos dois casos.

32


Na primeira fórmula, temos:“calcule B8 da planilha Ponte Nova mais

B8 da planilha Manhuaçu maisB8 da planilha Viçosa”

Na segunda fórmula, temos:“calcule a soma de todas as células B8

das planilhas de Ponte Nova até Viçosa”

Vamos salvar a pasta de trabalho atual com um outro nome. Assim, teremos exemplos das duas formas de vinculação de planilhas.

Clique em Arquivo/Salvar Como e salve a pasta de trabalho com o nome Distribuidora de Bebidas 2, na pasta Excel Avançado. Agora, copie a fórmula em B8 para as demais células de totalização e salve novamente a pasta de trabalho.

Você observou que usamos a função SOMA para construir uma fórmula tridimensional. Existem outras funções em que podemos utilizar referências 3-D. A lista completa é:

SOMA MÍNIMO VARCONT.VALORES MULT VARPMÉDIA DESVPAD CONT.NÚMMÁXIMO DESVPADP

Excluindo, copiando e movendo planilhas com referências 3-D

A utilização de fórmulas tridimensionais aumenta a eficiência do nosso trabalho. Veremos isso através de uma série de exercícios, onde iremos excluir e incluir planilhas. Como não há meios de desfazer o comando Excluir/Planilha, vamos salvar o arquivo atual como um modelo para facilitar o nosso trabalho.

157.Clique em Arquivo/Salvar Como.

158.Na caixa Nome do arquivo digite Distribuidora de Bebidas.

159.Na caixa Salvar como tipo selecione a opção Modelo.

160.Abra a pasta Excel Avançado, dentro da pasta Modelo, e salve o arquivo.

161.Feche a pasta de trabalho atual.

Vamos aos exercícios.

162.Clique em Arquivo/Novo e selecione a guia Excel Avançado.

163.Dê um duplo clique no modelo Distribuidora de Bebidas.

164.Clique com o botão direito sobre a guia Manhuaçu e selecione Excluir no menu de atalho. Confirme a exclusão.

165.Clique em TOTAIS e verifique que não houve problemas com as fórmulas. Aí está a eficiência da utilização de fórmulas tridimensionais. Feche o arquivo sem salvar. Vamos fazer um outro teste.



168.No exercício anterior, excluímos uma planilha localizada entre as planilhas que aparecem nas fórmulas 3-D. Exclua agora a planilha Ponte Nova.

169.Veja na planilha TOTAIS que o Excel alterou automaticamente as fórmulas para efetuar as somas de forma correta. Isso também aconteceria se, ao invés de excluir a planilha Ponte Nova, você tivesse excluído a planilha Viçosa.

170.Feche o arquivo sem salvar.



33


173.Suponha que tenha sido criada uma segunda unidade de distribuição em Ponte Nova. É possível, rapidamente, criar uma planilha para essa unidade.

174.Clique com o botão direito na guia Ponte Nova e selecione Mover ou Copiar. O Excel apresentará a caixa de diálogo abaixo. Clique em Manhuaçu, marque a opção Criar cópia e clique em OK.

Observe que os valores da planilha Ponte Nova (2) estão incluídos nas somas da planilha TOTAIS. Isso ocorreu porque criamos a cópia antes da planilha Manhuaçu e o Excel pôde perceber a inclusão dos novos dados. Se tivéssemos feito a cópia para antes da planilha Viçosa, o efeito também seria o mesmo. Mas atenção: se tivéssemos feito a cópia da planilha Ponte Nova para o final, para antes da planilha TOTAIS ou para antes da própria planilha Ponte Nova, os valores de Ponte Nova (2) não seriam incluídos na planilha TOTAIS.

175.Agora temos quatro planilhas cujos dados estão sendo somados em TOTAIS. Vejamos o que acontece se movermos alguma delas.

176.Arraste a planilha Ponte Nova(2) para a posição à direita de Manhuaçu. Observe que, nesse caso, os valores em TOTAIS continuam corretos.

177.Arraste a planilha Ponte Nova para a posição à direita de Manhuaçu. Agora temos uma modificação na planilha TOTAIS. Observe que as fórmulas dessa planilha somam células da planilha Ponte Nova até a planilha Viçosa. A planilha Manhuaçu ficou de fora dos cálculos. Se você mover a planilha Manhuaçu para a direita de Ponte Nova, ou esquerda de Viçosa, seus valores passam a ser novamente considerados nas somas da planilha TOTAIS.

178.Feche o arquivo sem salvar.

NOTA

34


· As fórmulas na planilha TOTAIS fazem referência a um intervalo de planilhas. Essa é uma característica inerente a fórmulas 3-D. E é por esse motivo que a exclusão ou inclusão de planilhas no intervalo são percebidas pelo programa. Compare essa situação com a do exemplo Folha de Pagamento que elaboramos na Prática 1. Naquele exercício, tínhamos uma planilha chamada Folha e outra chamada Índices. Algumas fórmulas comuns da planilha Folha referenciavam células da planilha Índices, mas não referenciavam um intervalo de planilhas. Nessa situação, você poderia mover qualquer uma das planilhas na sua pasta de trabalho, sem que os cálculos fossem alterados.

Refazendo o modelo

Na seção anterior, salvamos a pasta de trabalho Distribuidora de Bebidas como modelo para facilitar o nosso trabalho. Não há nada de errado nisso, pois utilizamos um recurso do Excel para ajudar-nos a resolver um problema. Entretanto, para outras situações práticas, um modelo obviamente não deve conter valores já inseridos. Vamos acertar isso agora.

179.Clique no botão Abrir.

180.Na caixa Pesquisar em, localize a pasta C:\Off95\Modelos\Excel Avançado.

181.Dê um clique duplo sobre o modelo Distribuidora de Bebidas.

182.Agrupe as planilhas Ponte Nova, Manhuaçu e Viçosa.

183.Lembra-se de que nomeamos o intervalo de valores com o nome DADOS? Pois bem, clique agora na caixa Nome da barra de fórmulas e selecione DADOS.

184.Pressione DELETE para apagar todos os valores de teste.

185.Pressione CTRL+HOME.

186.Salve e feche o modelo.

CONSOLIDAÇÃO DE PLANILHAS

Você pode agrupar informações de diversas planilhas em uma planilha mestra, utilizando o comando Consolidar, do menu Dados. É possível inclusive consolidar planilhas disponíveis em outras pastas de trabalho. Há dois tipos de consolidação: por posição ou por categoria. Vamos aprender as duas formas.

Consolidando por posição

Quando você consolida por posição, o Excel aplica a função de consolidação (Soma, Média, etc.) às mesmas referências de célula em cada planilha de apoio. Esse é o modo mais simples de consolidar, mas as suas planilhas de apoio devem ter exatamente o mesmo layout.

187.Abra o arquivo Consolidação - Exemplo 1.

188.Vamos usar duas funções de consolidação: SOMA e MÉDIA. Para isso, vamos preparar a pasta de trabalho.

189.Clique com o botão direito sobre a guia TOTAIS e escolha Mover ou Copiar.

190.Na caixa de diálogo Mover ou Copiar selecione (mover para o final).

191.Marque a caixa Criar cópia e clique em OK.

192.Dê um clique duplo na guia TOTAIS (2) e altere o nome para MÉDIAS.

193.Clique na célula A4 de MÉDIAS e altere o conteúdo para MÉDIAS.

194.Salve o arquivo atual.

195.Clique na guia TOTAIS.


197.Clique em Dados/Consolidar.

35


198.Na caixa de diálogo Consolidar, certifique-se de que a função SOMA esteja selecionada e o cursor esteja posicionado na caixa Referência.

199.Mova a janela Consolidar para uma área da tela de forma a lhe permitir acesso às guias das planilhas. Com o cursor na caixa Referência, clique na guia Ponte Nova.

200.Na planilha Ponte Nova, selecione o intervalo B8:H11. Clique no botão Adicionar da tela Consolidar.

201.Clique na guia Manhuaçu. Repare que o intervalo B8:H11 dessa planilha já está destacado. Clique no botão Adicionar.

202.Clique na guia Viçosa e em seguida no botão Adicionar. Para finalizar, clique em OK.

NOTAS

1. Não é possível efetuar o comando Dados/Consolidar em seleções múltiplas, isto é, em intervalos não adjacentes. Para consolidar intervalos não adjacentes, você deve efetuar a consolidação em diversas etapas. É preciso estar atento, pois a cada etapa você deve excluir as referências de consolidação anteriores e incluir apenas as que interessam naquela fase.

2. Observe que na caixa de diálogo Consolidar, no exemplo que fizemos anteriormente, não marcamos a opção Criar vínculos aos dados-fonte. Dessa forma, o Excel calcula a função de consolidação somente uma vez. Se você alterar os dados-fonte, os valores consolidados não serão modificados.

203.Clique na guia TOTAIS e selecione o intervalo B13:H16.

36


204.Clique em Dados/Consolidar. Certifique-se de que a função SOMA esteja selecionada.

205.Atenção: exclua os intervalos listados na caixa Todas as referências. Se não tomarmos essa providência, o Excel fará a consolidação considerando também esses intervalos.

206.Com a caixa Todas as referências vazia, clique em Referência.

207.Com o cursor na caixa Referência, clique na guia Ponte Nova. Selecione o intervalo B13:H16. Clique em Adicionar.

208.Clique na guia Manhuaçu e, em seguida, no botão Adicionar.

209.Clique na guia Viçosa e, sem seguida, no botão Adicionar. Para finalizar, clique em OK.

210.Faça agora, por etapas, a consolidação dos intervalos B20:H23, B25:H29 e B31:H31.

211.Grave o arquivo e mantenha-o aberto para o próximo exercício.

NOTA

· Dissemos anteriormente que você deve excluir os intervalos de consolidação existentes na caixa Consolidar, para efetuar uma nova consolidação na mesma planilha. Isso é realmente verdade. Suponha, entretanto, que tenhamos uma planilha consolidando apenas um intervalo de diversas outras planilhas. Nessa situação, você pode se beneficiar do fato das referências de consolidação permanecerem listadas. Isso porque essas referências são gravadas quando você salva e fecha o arquivo. Assim, se você alterar os dados-fonte e quiser refazer a consolidação, basta selecionar Dados/Consolidar e clicar no botão OK.

Criando vínculos com os dados-fonte

Na planilha MÉDIAS, consolidaremos por posição, mas criaremos vínculos com os dados-fonte.

212.Clique na guia MÉDIAS.

213.Selecione o intervalo B8:H11 e clique em Dados/Consolidar.

214.Selecione a função MÉDIA e marque a opção Criar vínculos aos dados-fonte.

215.Clique na caixa Referência.

216.Com o cursor na caixa Referência, clique na guia da planilha Ponte Nova.

217.Na planilha Ponte Nova, selecione o intervalo B8:H11. Clique em Adicionar.

218.Clique na guia Manhuaçu e, em seguida, no botão Adicionar.

219.Clique na guia Viçosa e, em seguida, no botão Adicionar. Para finalizar, clique em OK.

Observe, na planilha MÉDIAS, que o Excel adicionou alguns botões de controle à esquerda da numeração de linhas. Esses controles aparecem quando uma planilha está dividida em tópicos. O assunto tópicos será tratado posteriormente.

Clique em B19. Na barra de fórmulas o conteúdo de B19 é: =MÉDIA(B16:B18). Clique no terceiro botão com o símbolo + à esquerda da planilha. As linhas 16 a 18 tornam-se visíveis. Agora verifique o conteúdo, na barra de fórmulas, das células B16, B17 e B18. São respectivamente:

=Manhuaçu!$B$10=’Ponte Nova’!$B$10=Viçosa!$B$10

Vejamos o que aconteceu. O Excel transcreveu para a planilha MÉDIAS, nas linhas 16 a 18, as linhas 10 de cada uma das três planilhas anteriores. Ao final, adicionou a fórmula para calcular a média dos valores que estão nas linhas 16 a 18. Esse procedimento foi executado para cada uma das linhas consolidadas. Observe

37


que o programa organizou a planilha em tópicos, “escondendo” os valores que estão sendo totalizados e deixando visíveis apenas as linhas contendo a função de consolidação.

Observe ainda a ordem em que os dados aparecem nas linhas 16 a 18. Não é a ordem na qual fizemos a consolidação, ou seja, não é a ordem em que as planilhas aparecem na pasta de trabalho. O Excel ordenou alfabeticamente pelos nomes das planilhas. Um outro detalhe é que o nome Ponte Nova aparece nas fórmulas entre apóstrofos devido ao espaço em branco entre as duas palavras.

O mais importante de tudo isso é que, nesse segundo tipo de consolidação, criamos vínculos com os dados de origem. Isso significa que, se você alterar valores em qualquer das três primeiras planilhas, as médias serão recalculadas.

Para finalizar, faça a consolidação para os demais intervalo de MÉDIAS, lembrando-se de manter o vínculo com os dados de origem. Um detalhe: fique atento quando selecionar os intervalos de consolidação e os intervalos que serão consolidados. O intervalo de consolidação na planilha MÉDIAS, apesar de apresentar o mesmo layout dos intervalos que serão consolidados, não terá a mesma numeração de linhas.

Consolidando por categoria

Abra o arquivo Consolidação - Exemplo 2. Vamos realizar uma consolidação por categoria nessa pasta de trabalho.

Observe que os dados nas quatro primeiras planilhas não têm a mesma disposição. O número de alunos é diferente em cada uma das planilhas. Desejamos calcular a média de cada aluno, em relação ao número de provas que cada um deles prestou.

220.Clique na guia MÉDIAS.

221.Clique na célula A4. Não iremos selecionar nessa planilha o intervalo de consolidação, pois não sabemos quantas linhas serão necessárias. Quando selecionamos apenas uma célula na planilha de consolidação, como fizemos, o comando Consolidar preenche a área abaixo e à direita daquela célula conforme a necessidade.

222.Clique em Dados/Consolidar.

223.Selecione a função MÉDIA e marque a caixa Coluna esquerda no painel Usar rótulos na.

224.Clique na caixa Referência.

225.Com o cursor na caixa Referência, clique na guia 1ª PROVA. Selecione o intervalo A4:E8 e clique em Adicionar.

226.Clique na guia 2ª PROVA. Selecione o intervalo A4:E10 e clique em Adicionar.



229.Para finalizar, clique em OK.

Note que o Excel calculou corretamente as médias para cada aluno.

A consolidação por categoria é útil quando temos dados diferentes nas planilhas a consolidar. Você pode consolidar por linhas, por colunas, ou por linhas e colunas diferentes. Isso é definido quando você marca ou desmarca opções no painel Usar rótulos na.

VINCULANDO PLANILHAS EM PASTAS DE TRABALHO DIFERENTES

Além de vincular planilhas em uma mesma pasta de trabalho, é possível vincular planilhas em pastas de trabalho diferentes. Em muitos casos, essa técnica permite

38


criar modelos mais gerenciáveis e flexíveis, além de proporcionar economia de tempo e memória de recálculo. Vejamos como funciona.

230.Abra a pasta de trabalho Folha de Pagamento 2.

231.Trata-se do mesmo exemplo com o qual trabalhamos anteriormente nesta lição, mas essa pasta de trabalho não possui a planilha Índices. O que faremos em seguida é construir a planilha Índices em uma pasta de trabalho diferente de Folha de Pagamento 2.

232.Sem fechar a pasta de trabalho atual, clique no botão Nova pasta de trabalho.

233.Vamos trabalhar apenas com a Plan1. Exclua as demais planilhas dessa nova pasta de trabalho.

234.Em A1 insira ÍNDICES DE DESCONTOS.

235.Em A2 insira INSS. Em A3 insira IMPOSTO DE RENDA. Em A4 insira PLANO DE SAÚDE.

236.Em C2, C3 e C4, insira, respectivamente, 8%, 15% e 13%.

237.Formate a tabela de descontos a seu gosto.

238.Não altere o nome da planilha Plan1, para que possamos perceber a elaboração das fórmulas adiante.

239.Salve a pasta de trabalho com o nome Índices.

240.Clique em Janela e selecione a pasta Folha de Pagamento 2.

241.Insira o valor 1.000,00 em B8. Em seguida, posicione na célula C8.

242.Em C8 digite =.

243.Após o símbolo =, clique em B8 e digite *.

244.Após o símbolo *, clique em Janela e selecione Índices.

245.Na Plan1 da pasta Índices, clique em C2. Note que o Excel trata a referência como absoluta. Melhor ainda para nós, pois precisamos fixar o endereço de C2 nesse caso. Entretanto, também é possível trabalhar com referências mistas ou relativas, bastando para isso apertar a tecla F4 no momento da elaboração das fórmulas. Pressione ENTER ao final da fórmula.

Observe a fórmula em C8: =B8*[Índices.xls]Plan1!$C$2. Entre colchetes temos o nome da pasta de trabalho. Logo em seguida aparece o nome da planilha. Após o ponto de exclamação, temos as coordenadas da célula. Uma referência a uma pasta de trabalho que não seja a pasta atual é chamada de referência externa.

246.Seguindo o mesmo procedimento, construa as fórmulas para o restante da linha 8. O próximo passo seria copiar as fórmulas da linha 8 para as demais linhas da planilha. Não faremos isso agora. Vamos utilizar a linha 9 para aprendermos a lidar com nomes em pastas de trabalho diferentes.

247.Em C9, insira o valor 2.000,00.

248.Clique em Janela e selecione a pasta de trabalho Índices.

249.Atribua os nomes INSS, IRPF e P_SAÚDE respectivamente às células C2, C3 e C4.

250.Clique em Janela e selecione a pasta de trabalho Folha de Pagamento 2.

251.Clique em C9 e digite =.

252.Após o símbolo =, clique na célula B9 e digite *.

253.Após o símbolo *, clique em Janela e selecione Índices.

254.Na caixa Nome da pasta de trabalho Índices, selecione INSS.

255.Pressione ENTER.

256.Observe na fórmula em C9 que o nome da pasta de trabalho não ficou entre colchetes. Entretanto, o cálculo é o mesmo da fórmula em C8. Faça agora as

39


fórmulas para as demais células da linha 9, utilizando os nomes da pasta de trabalho Índices. Mantenha as pastas de trabalho abertas. Voltaremos a elas daqui a pouco.

Nas próximas seções, abordaremos questões importantes sobre a vinculação de pastas de trabalho. Utilizaremos exemplos bem simples, pois não estamos preocupados com o cálculo em si, mas com a utilização correta de referências externas.

Pastas de trabalho de apoio e dependentes

No nosso exemplo, temos células na pasta de trabalho Folha de Pagamento 2 que fazem referência a valores na pasta de trabalho Índices. Em outras palavras, temos valores em Folha de Pagamento 2 que dependem de valores de Índices. Nesse caso, dizemos que Folha de Pagamento 2 é uma pasta de trabalho dependente e Índices é uma pasta de trabalho de apoio.

Fechando a pasta de trabalho de apoio

Voltando ao nosso exemplo, siga o roteiro abaixo.

257.Clique em Janela e selecione Índices.

258.Salve e feche a pasta de trabalho Índices.

259.Observe, por exemplo, a fórmula em C8 de Folha de Pagamento 2. Agora ela inclui o caminho completo do arquivo Índices no disco rígido. Você deve ter nessa célula a fórmula: =B8*'C:\Excel Avançado\[Índices.xls]Plan1'!$C$2. Experimente abrir novamente a pasta de trabalho Índices. O Excel volta a apresentar as fórmulas na forma anterior.

260.Salve e feche a pasta de trabalho Índices.

261.Salve e feche a pasta de trabalho Folha de Pagamento 2.

Abrindo uma pasta de trabalho dependente

Vejamos algumas questões que surgem normalmente quando trabalhamos com pastas vinculadas. Você mesmo encontrará as respostas, praticando.

· Desejamos abrir uma pasta dependente. É necessário abrir também a(s) pasta(s) de apoio para que os dados sejam atualizados corretamente?

262.Abra o arquivo Folha de Pagamento 2. O Excel apresentará a caixa de diálogo abaixo.

263.

264.

265.

266.

267.

268.Se você clicar em Sim, o Excel recalculará os valores da pasta Folha de Pagamento 2, com base nos valores existentes na pasta de apoio Índices. Se você clicar em Não, os valores de Folha de Pagamento 2 atuais serão mantidos, sem qualquer recálculo. Clique em Sim.

269.Os valores em Folha de Pagamento 2 foram recalculados, mas são os mesmos que tínhamos quando salvamos o arquivo. Isso ocorreu porque não fizemos

1.

40


nenhuma alteração na pasta de trabalho Índices, que pudesse modificar os valores no momento do recálculo.

270.Clique no menu Janela. Observe que o Excel restabeleceu os vínculos sem, contudo, abrir a pasta de apoio Índices.

271.Feche o arquivo Folha de Pagamento 2.

272.O Excel exibe uma caixa de diálogo perguntando se você deseja salvar as alterações para Folha de Pagamento 2. Isso aconteceu porque, apesar de não termos feito nenhuma alteração, o próprio programa efetuou um recálculo quando restabelecemos os vínculos, e esse recálculo constitui uma alteração que deve ser salva.

273.Clique em Sim para salvar e fechar o arquivo.

· O que aconteceria se tivéssemos aberto a pasta de trabalho Índices e em seguida o arquivo Folha de Pagamento 2?

274.Nada melhor do que praticar para aprender. Abra o arquivo Índices e em seguida o arquivo Folha de Pagamento 2. Você verá que o Excel atualizará os valores em Folha de Pagamento 2 automaticamente, sem qualquer aviso. Se você achar que não houve atualização, tente fechar o arquivo Folha de Pagamento 2 sem salvar.

275.Em seguida, feche também o arquivo Índices.

Modelos com pastas de trabalho vinculadas

Nas lições anteriores, aprendemos a trabalhar com modelos de pastas de trabalho simples. Também é possível criar modelos com pastas de trabalho vinculadas.

276.Abra o arquivo Folha de Pagamento 2.

277.Restabeleça os vínculos.

278.Apague quaisquer valores de salários-base existentes. Mantenha os nomes dos funcionários e as fórmulas.

279.Clique em Arquivo/Salvar Como.

280.Na caixa Nome do arquivo digite Folha Mensal.

281.Na caixa Salvar como tipo escolha Modelo.

282.Salve o modelo na pasta C:\Off95\Modelos\Excel Avançado.

283.Feche o arquivo.

Daqui em diante, você poderá utilizar esse modelo como qualquer outro. Note que a pasta de trabalho dependente ficou gravada em C:\Off95\Modelos\Excel Avançado, enquanto a pasta de apoio Índices permaneceu na pasta C:\Excel Avançado. Não há problemas. Quando você utilizar o modelo Folha Mensal, o Excel poderá restabelecer os vínculos corretamente, pois o programa “sabe” o endereço completo da pasta de apoio. É o que veremos em seguida.


285.Dê um duplo clique no modelo Folha Mensal. Restabeleça os vínculos.

286.Digite alguns valores para os salários-base na coluna B.

287.Salve a pasta de trabalho atual com o nome Folha de Janeiro como um arquivo comum na pasta Excel Avançado.

288.Feche o arquivo Folha de Janeiro.

Quebrando vínculos entre pastas de trabalho

Vamos analisar uma situação comum na prática. Retomando o exemplo anterior, suponha que vamos preparar agora a folha de pagamento do mês de fevereiro.

41


Entretanto, suponha que os índices de descontos para o mês de fevereiro tenham sido alterados por lei, da seguinte forma:

INSS: de 8% para 6%IMPOSTO DE RENDA: de 15% para 12%PLANO DE SAÚDE: sem alteração

Obviamente, teremos que atualizar esses valores na pasta de trabalho Índices. Mas, se fizermos isso, o que acontecerá quando abrirmos a pasta de trabalho Folha de Janeiro, que já está pronta? Bem, nesse caso, o Excel perguntará se queremos restabelecer os vínculos. Se respondermos sim, o programa recalculará as fórmulas nessa pasta de trabalho, atualizando-as para os novos valores do arquivo Índices. Provavelmente, não vamos querer essa situação. Assim, o procedimento adequado seria não restabelecer os vínculos.

O problema é que o Excel sempre perguntará se desejamos restabelecer os vínculos, quando abrirmos a pasta Folha de Janeiro. Assim, o ideal seria encontrar uma forma de cortar os vínculos dessa pasta de trabalho com o arquivo Índices. Podemos realizar essa tarefa com a ajuda do comando Colar Especial. Vamos praticar.

289.Abra a pasta de trabalho Índices.

290.Em C2 digite 6%. Em C3 digite 12%.

291.Salve e feche o arquivo.

292.Abra a pasta de trabalho Folha de Janeiro. Não restabeleça os vínculos.

293.Observe que, por não ter restabelecido os vínculos, os valores de INSS e de IMPOSTO DE RENDA continuam sendo, respectivamente, 8% e 15% sobre o salário-base.

294.Selecione o intervalo C8:E30 e em seguida clique no botão Copiar.

295.Mantendo o intervalo C8:E30 selecionado, clique em Editar/Colar Especial.

296.Na caixa de diálogo Colar Especial, escolha Valores. Clique em Ok.

297.Pressione ENTER. Em seguida, observe o conteúdo na barra de fórmulas de algumas células das colunas C, D ou E. Salve e feche o arquivo.

298.Abra novamente o arquivo Folha de Janeiro e observe que o Excel não perguntará mais se você deseja restabelecer vínculos.

299.Feche novamente o arquivo Folha de Janeiro.

NOTAS

1. Você pode usar a opção Colar Especial/Valores em outras situações diferentes da que apresentamos. Como vimos, essa opção é útil quando necessitamos de alguns conjuntos de valores cujos dados não serão mais atualizados e, por algum motivo, as fórmulas nas células correspondentes podem modificá-los sem que o desejássemos.

2. Em uma pasta de trabalho muito grande, pode ser complicado quebrar os vínculos, pois você terá que executar esse procedimento para todas as fórmulas que possuem referências externas. Essas fórmulas podem estar espalhadas por várias regiões da pasta de trabalho. Como alternativa, você poderia fazer o seguinte:· Selecionar a planilha inteira. Para isso, você deve clicar no botão

disponível na interseção dos cabeçalhos de linhas e colunas.· Com a planilha totalmente selecionada, clicar em Copiar.· Com a planilha totalmente selecionada, clicar em Colar Especial e escolher a

opção Valores.O problema dessa solução é que você estará substituindo todas as suas

fórmulas pelos valores que elas retornam, inclusive aquelas que não possuem referências externas. Entretanto, se você tem certeza de que os valores da pasta de trabalho não devem sofrer nenhuma alteração posterior, essa solução é aceitável.

42


Redirecionando vínculos

Quando você renomeia uma pasta de trabalho de apoio ou move-a para outro diretório, é necessário redirecionar os vínculos na pasta de trabalho dependente, para que o Excel possa encontrar a pasta de trabalho de apoio.

300.Abra o Windows Explorer.

301.Localize e abra a pasta Excel Avançado.

302.Clique com o botão direito sobre o arquivo Índices e escolha a opção Renomear.

303.Altere o nome do arquivo para Escondido.

304.Feche o Windows Explorer.

305.No Excel, abra o arquivo Folha de Pagamento 2, da pasta Excel Avançado.

306.Tente restabelecer os vínculos. O Excel abrirá a caixa de diálogo Arquivo Não Localizado. Clique no botão Cancelar nessa tela.

307.O arquivo Folha de Pagamento 2 será aberto sem que os vínculos tenham sido restabelecidos.

308.Clique em Editar/Vínculos.

309.Na caixa de diálogo Vínculos, clique em Alterar origem.

310.Na tela Alterar Vínculos, localize e selecione o arquivo Escondido. Clique em OK.

311.O Excel volta à janela Vínculos. Clique em OK.

312.Observe as alterações efetuadas automaticamente pelo programa nas fórmulas das colunas C, D e E.

313.Salve e feche o arquivo Folha de Pagamento 2.

314.Para treinar, repita o procedimento acima, dessa vez renomeando o arquivo Escondido para Índices.

43

Documents

Lição 03 - Vinculando Planilhas e Pastas de Trabalho