MS OFFICE - EXCEL 2013
Exemplos e Exercícios
Bruno Almeida de Jesus
PLANEJAMENTO
DIAS
CONTEÚDO
04/06
Apresentações, MS Excel (versões), Revisão (Fórmulas e Formatação Condicional)
06/06
Microsoft Office Excel – Revisão (Funções de data, estatísticas, lógicas)
11
/06
Microsoft Office Excel – Revisão (Funções de procura e referência.)
13/06
Microsoft Office Excel – Validação de células. Colar especial. Filtros.
18
/06
Microsoft Office Excel – Proteção de células, planilhas e arquivos. Vínculos e Suplementos.
20
/06
Microsoft Office Excel – Consolidação e Auditoria em Fórmulas
25
/06
Microsoft Office Excel – Tabelas dinâmicas
27/06
Microsoft Office Excel – Macros e Formulários com VBA
02/07
Microsoft Office Excel – Macros e Formulários com VBA
MICROSOFT OFFICE EXCEL
•
Recursos que exercitaremos esta semana:
•
Proteção de Células e Planilha.
•
Vínculos e Suplementos.
Protegendo Planilhas
▶
Protegendo Planilhas
◦
Podemos impedir que o usuário possa formatar ou inserir
conteúdo em determinadas células da planilha;
◦
Através da proteção de planilha, podemos manter um padrão
pré-definido das planilhas e células, além de gerenciar a
inserção e formatação de conteúdo;
◦
Antes de proteger uma planilha, precisamos definir as células
que ficarão de fora da proteção de planilha;
Exercício
▶
Para deixar uma célula livre para edição, após a
proteção de planilha:
◦
Clique com o botão direito sobre a célula a ser liberada;
◦
Selecione a opção
Formatar
células...;
◦
Clique na aba
Proteção
;
◦
Desmarque a opção
Bloqueadas
;
▶
Desta forma, mesmo que a planilha esteja bloqueada,
Exercício
▶
Para proteger uma planilha:
◦
Acesse a aba
Revisão
, bloco
Alterações
;
◦
Clique na opção
Proteger Planilha
;
◦
Mantenha a opção
Proteger a planilha e o conteúdo de
células bloqueadas
marcada;
◦
Caso queira definir uma senha para desbloqueio, digite-a
na caixa de texto
Senha para desproteger a planilha
;
◦
Nas opções
Permitir que todos os usuários desta planilha
possam
:
, podemos definir ações comuns a células
▶
Uma referência externa (também chamada de vínculo) é uma
referência para uma célula ou intervalo em uma planilha em
outra pasta de trabalho do Excel ou em uma referência para um
nome definido em outra pasta de trabalho.
▶
Você cria referências externas entre as pastas de trabalho para
trazer informações de uma pasta de trabalho de origem para
uma pasta de trabalho de destino.
Referência externa
=SOMA([Orçamento.xls]Anual!C10:C25)
Quando o livro de origem não estiver aberto no Excel, a
referência externa inclui o caminho completo.
Referência externa
NOTA Se o nome da outra planilha ou pasta de trabalho
contiver caracteres não-alfabéticos, tem de incluir o nome (ou o
caminho) entre aspas (").
As fórmulas que estabelecem uma ligação a uma pasta de
trabalho definida noutra pasta de trabalho utilizam o nome da
pasta de trabalho seguida de um ponto de exclamação (!) e do
nome. Por exemplo, a fórmula seguinte adiciona as células do
intervalo chamado Vendas da pasta de trabalho chamada
Orçamento.xlsx.
Referência externa
Suplementos
▶
Os suplementos fornecem comandos e recursos opcionais do
Microsoft Excel. Por padrão, os suplementos não estão disponíveis
imediatamente no Excel, devendo primeiramente ser instalados e (em
alguns casos) ativados para que você possa usá-los.
▶
Alguns suplementos são integrados ao Excel, como o Solver e as
Consolidação
▶
Para resumir e informar os dados de planilhas separadas,
você pode consolidar os dados de cada planilha separada
em uma planilha (ou planilha mestre).
▶
As planilhas podem estar na mesma pasta de trabalho que a
Consolidação
Exemplo:
▶
Uma planilha de valores de despesa de cada um dos escritórios
regionais:
Consolidação
●
Consolide por posição
- Use esse método quando os dados de várias
áreas de origem forem organizados na mesma ordem e usarem a
mesma linha e rótulos de coluna. Por exemplo, quando você tiver uma
série de planilhas de despesas que foram criadas com o mesmo
modelo.
●
Consolide por categoria
- Use esse método quando os dados de
várias áreas de origem foram organizados de forma diferente, mas os
mesmos rótulos de linha e coluna foram usados. Por exemplo, você
pode usar esse método quando tiver uma série de planilhas de estoque
para cada mês com o mesmo layout, mas cada planilha contiver itens
diferentes ou um número diferente de itens.
Consolidação por posição
1. Em cada planilha que contém os dados que você deseja consolidar, configure os dados seguindo estes procedimentos:
a. Verifique se cada intervalo de dados está no formato de lista: cada coluna tem um rótulo na primeira linha e contém informações semelhantes, e não existem linhas ou colunas em branco dentro da lista. b. Coloque cada intervalo em uma planilha separada, mas não insira nenhum intervalo na planilha onde
você planeja colocar a consolidação.
c. Verifique se cada intervalo tem o mesmo layout.
d. DICA Se você consolidar dados com frequência, isso pode ajudar a basear suas planilhas em um modelo de planilha que usa um layout consistente.
2. Na planilha mestre, clique na célula superior esquerda da área em que você deseja que os dados consolidados sejam exibidos.
3. OBSERVAÇÃO Para evitar substituir dados existentes na planilha de destino pelos dados que você está consolidando, verifique se você deixou células suficientes à esquerda e abaixo dessa célula para os dados consolidados.
Consolidação por posição
5. Na caixa Função, clique na caixa Nome que você deseja que o Microsoft Excel use para consolidar os dados.
6. Se a planilha que contém os dados que você deseja consolidar estiver em outra pasta de trabalho, clique em Procurar para localizar essa pasta de trabalho e clique em OK para fechar a caixa de diálogo Procurar.
7. O caminho do arquivo é digitado na caixa Referência seguido por um ponto de exclamação.
8. Se a planilha que contém os dados que você deseja consolidar estiver na pasta de trabalho atual, faça o seguinte:
a. Na caixa Referência, clique no botão Recolher Caixa de Diálogo para selecionar os dados na planilha.
b. Clique na planilha que contém os dados que você deseja consolidar, selecione os dados e clique no botão Expandir Caixa de Diálogo.
Consolidação por posição
10. Especifique como deseja atualizar a consolidação,
seguindo um destes procedimentos:
a. Para configurar a consolidação de forma a ser
atualizada automaticamente quando os dados
de origem em outra pasta de trabalho forem
alterados, marque a caixa de seleção Criar
links para dados de origem.
b.
IMPORTANTE
Marque essa caixa de
seleção apenas se a planilha que contém os
dados estiver em outra pasta de trabalho.
Depois de marcar essa caixa de seleção, não
será mais possível alterar as células e os
intervalos que estão inclusos na consolidação.
c. Para configurar a consolidação para que você
▶
Grupo
de
ferramentas
responsável pelo rastreamento
prescedente e dependente de
células que estão sendo
utilizadas em fórmulas criadas
nas planilhas.
▶
Serve para localizar as células
envolventes
em
cálculos
realizados em uma tabela
qualquer, um recurso muito útil
a ser utilizado em tabelas
complexas e com muitas
fórmulas com a finalidade de
não deixar o usuário confuso.
▶
É utilizada quando se tem a
localização exata da fórmula e
necessita localizar todas as células
envolventes utilizadas na fórmula
selecionada.
▶
No exemplo seleciona-se a célula
que possui o resultado da fórmula
(439,73) e clica na função Rastrear
Precedentes.
▶
Todas as células precedentes do
resultado serão selecionadas na
cor
Azul
e apontarão com uma
seta para a fórmula do resultado.
▶
A função para rastrear células
Dependentes é inversa a anterior,
pois é utilizada quando se tem a
localização exata das células e
necessita localizar a fórmula que
depende da célula selecionada
para gerar o resultado.
▶
No exemplo seleciona-se uma
célula, clica na função Rastrear
Dependentes e automaticamente a
fórmula que depende da célula
selecionada para gerar um
resultado será localizada através
da seta na cor
Azul
.
▶
A função para rastrear células
Dependentes é inversa a anterior,
pois é utilizada quando se tem a
localização exata das células e
necessita localizar a fórmula que
depende da célula selecionada
para gerar o resultado.
▶
No exemplo seleciona-se uma
célula, clica na função Rastrear
Dependentes e automaticamente a
fórmula que depende da célula
selecionada para gerar um
resultado será localizada através
da seta na cor
Azul
.
Obs: Se necessitar retirar as setas apenas clique em Remover Setas e selecione as setas desejadas a serem removidas.