Excel - Ficha de Trabalho
Facturação Avançada
O trabalho que se segue consistirá numa folha de cálculo com alguma complexidade, em que existirão diferentes
folhas no mesmo livro. Neste serão criadas Bases de Dados com dados dos clientes e produtos, onde será possível
efectuar consultas, relatórios e outras tarefas inerentes às Bases de Dados. Noutra folha será elaborada uma
factura cujo objectivo final será a sua impressão com actualização automática de stocks. Existirão outras folhas
com outras funcionalidades
1 – Inicie um novo ficheiro e guarde-o com o nome Facturação. Este ficheiro terá várias folhas
cujos nomes se indicarão durante a evolução do trabalho, a primeira terá o nome Clientes e a
segunda o nome Produtos. Todas as outras terão nomes adequados aos seus conteúdos. Sempre
que necessário configure as páginas de acordo com o que aprendeu anteriormente e as suas
necessidades.
2 – Vamos considerar que todos os elementos deste curso são potenciais clientes, como tal abra o
ficheiro Curso de Informática e copie os dados da folha Idades relativos aos alunos, incluído
o cabeçalho. Vá para a folha cliente e na célula A1 cole (Não se preocupe com o aviso de erro).
Ajuste a largura das colunas ao seu conteúdo.
3 – Elimine a coluna que contém os dias de vida e reformule a fórmula nas células relativas à idade.
4 – Formate as datas de nascimento de acordo com a seguinte tipologia: dd-mmmm-aaaa
5 – Considerando que cada coluna é um campo e que cada linha é um registo, vamos agora
adicionar mais alguns campos à nossa base. Assim, insira uma coluna antes do campo nome. Na
A1 digite Código. Atribua um código para cada elemento iniciando a numeração em 1; utilize o
preenchimento por arrastamento.
6 – Pela ordem indicada adicione os seguintes campos:
7 – Seleccione uma das células já formatadas e copie-a, de seguida seleccione as outras que contém
os nomes nos campos e cole só os formatos (Editar/ Colar especial.../ Formatos).
8 – Formate as colunas telefone, telemóvel e cartão de crédito para que os números apresentem
separadores dos milhares e cujos conteúdos fiquem centrados.
9 – Para as colunas do telefone e telemóvel crie as seguintes regras de validação (Menu Dados):
(Obs.: Poderá utilizar outras regras de validação)
10 – Para no caso de alguém digitar, nove letras em vez de nove números, determine, também, para
as células das colunas anteriores, excepto para os nomes dos campos, a seguinte formatação
Facturação Avançada.doc
Página n.º 1/9
Prof. Helder Vicente
condicional apresentada na figura a seguir. Esta deverá ser efectuada para a primeira célula e
posteriormente copiado e colado o respectivo formato para as restantes.
11 – Formate a coluna do Código Postal com o formato ####-###. Centralize o conteúdo desta
coluna.
12 – Efectue de seguida os ajustamentos que entender, melhorando o aspecto gráfico a seu gosto.
Poderá adicionar outras funcionalidades se assim o entender.
13 – Vamos agora efectuar a gestão da base de dados preenchendo os dados em falta e recorrendo,
mais uma vez aos elementos da turma, desta vez vamos utilizar um formulário. Para o efeito
aceda a Dados/ Formulários... Complete alguns elementos através deste processo ou então
directamente nas células.
14 – Através do formulário efectue algumas consultas recorrendo ao botão critérios para especificar
as condições. Por exemplo, visualizar só quem tiver mais de 17 anos.
15 – Podemos, ainda, efectuar pesquisas recorrendo a filtros
automáticos Dados/ Filtro/ Filtro automático. No campo
pretendido especifique as condições de pesquisa. Por exemplo,
visualizar só quem tiver menos de 18 anos. Podemos agora
ocultar algumas das colunas e imprimir o resultado da
pesquisa.
16 – Seleccione as colunas que contêm os dados e atribua-lhes o nome Clientes.
17 – Passe para a folha Produtos e crie a base de dados dos produtos de uma hipotética firma
armazenista. Assim, comece por criar os campos e preencha os dados de acordo com a figura
seguinte, no entanto poderá adicionar outros campos e/ou produtos de acordo com o seu
interesse pessoal. O campo Stock da coluna I será para efectuar a gestão de stocks numa fase
posterior.
Facturação Avançada.doc
Página n.º 2/9
Prof. Helder Vicente
18 – Formate esta folha a seu gosto pessoal e efectue os melhoramentos que achar convenientes.
Configure a página e adicione o cabeçalho e rodapé habitual.
19 – Seleccione as colunas que contêm os dados e atribua-lhes o nome Base_de_Dados.
20 – Pretendemos agora efectuar um Filtro avançado para o controlo de stocks inferiores a 50
unidades. Assim, copie para a célula L1 os nomes dos campos. Na célula R2 digite <50.
Sendo esta a nossa zona para definição
dos critérios da consulta.
21 – No intervalo de células AA1:AG3 desenhe e formate a seu
gosto uma forma automática com o texto “Gestão de
Stocks”.
22 – Efectue o filtro avançado de acordo com as condições
apresentadas na figura ao lado.
23 – Para melhorar a operacionalidade deste filtro, bem como a
sua impressão, construa uma macro de acordo com a tabela
apresentada a seguir. De seguida atribua-a a um botão cujo
nome será “Gestão de Stocks” ou crie uma nova barra de
ferramentas com o mesmo nome.
Nome da Macro
GestaoStocks
Funcionalidade
Defina as colunas AA:AG como área de impressão.
Execute o filtro avançado com as condições anteriores.
Pré-visualize a impressão.
Poderá ainda experimentar outras situações digitando outros critérios.
24 – Pretende-se, agora, criar uma tabela dinâmica que nos
mostre os stocks da nossa base de dados. O esquema
será o apresentado ao lado. Mude a folha para último
lugar e altere-lhe o nome para “Tabela de Stocks”.
25 – Ordene os stocks por ordem
ascendente. (Clique Bt. Dtº sobre o
campo Stock escolha Definições dos
campos... e proceda como na figura
ao lado)
26 – Para actualizar os dados pressione
que existe na barra de
este botão
ferramentas tabela dinâmica.
27 – Insira no início, uma nova folha cujo nome será Factura.
28 – Configure a página em A4, modo de orientação vertical, sendo a margem esquerda de 2,5 cm e
as restantes de 1,5 cm incluindo as margens do cabeçalho e rodapé.
Facturação Avançada.doc
Página n.º 3/9
Prof. Helder Vicente
29 – Inicie a construção da factura começando por determinar que a largura das colunas A a AP é
de 15 pixels e que o tipo de letra para toda a folha é Arial/ 8/ Normal.
30 – Da linha um até à linha dez construa o cabeçalho da factura. Introduza os elementos da firma,
n.º da factura e dados do cliente. Apresenta-se um exemplo, no entanto, mais uma vez poderá
adequar os dados de acordo com o seu interesse pessoal. Como tal não foram considerados
elementos como o número de contribuinte, matricula do veículo, hora de carga, local de
descarga, etc..., no entanto não aconselho a introduzi-los agora, mas sim numa fase posterior,
pois face à complexidade do relacionamento e condicionalismos de dados, poderá sentir
algumas dificuldades, visto estes casos não se encontrarem descritos.
31 – Construa os dados relativos à factura propriamente dita. Esta poderá ser construída de acordo
com as necessidades, no entanto deverá estar de acordo com a tipologia utilizada na Base de
Dados dos Produtos. Mais uma vez, na figura seguinte se apresenta um exemplo. Neste caso
estão previstas trinta e cinco linhas para a inserção de produtos.
32 – Falta construir o fecho da factura. Construa-o de acordo com o esquema apresentado na figura
seguinte. As células da caixa de observações estão unidas linha a linha.
Facturação Avançada.doc
Página n.º 4/9
Prof. Helder Vicente
33 – Concluída a parte gráfica da factura, passamos à parte funcional. Vamos utilizar a zona lateral
à factura para nela efectuarmos alguns cálculos auxiliares. Assim:
Célula
AR2
Fórmula/ Função
Formato/ Outros
=AK2+1
AK2
000.000 / Negrito
Z6
=PROCV(Z5;Clientes;2;Falso)
Alinhamento à esquerda / Negrito
Z7
=PROCV(Z5;Clientes;5;Falso)
Alinhamento à esquerda / Negrito
Z8
=PROCV(Z5;Clientes;6;Falso)
Alinhamento à direita / Negrito
AE8
=PROCV(Z5;Clientes;7;Falso)
Alinhamento à esquerda / Negrito
E13
=Agora()
dd-mmm-aaaa / Negrito
P13
Negrito
AC13 =PROCV(Z5;Clientes;8;Falso)
Número sem casas decimais e com
separador dos milhares / Negrito
AI13 =PROCV(Z5;Clientes;9;Falso)
Número sem casas decimais e com
separador dos milhares / Negrito
H16
=SE(B16="";"";PROCV(B16;Produtos;2;FALSO))
Alinhamento à esquerda
U16
=SE(B16="";"";PROCV(B16;Produtos;3;FALSO))
Alinhamento à direita
X16
=SE(B16="";"";PROCV(B16;Produtos;5;FALSO))
Euros
AC16 =SE(E16="";"";SE(E16<=9;0;(SE(E(E16>9;E16<20);0,05;0,1)))) %
AE16 =SE(E16="";"";X16*E16*(1-AC16))
Euros
AL16 =SE(E16="";"";PROCV(B16;Produtos;6;FALSO))
%
AQ15 Stocks actuais
AR15 IVA a 5%
AS15 IVA a 21%
AT15 Validade do Cód.
AQ16 =SE(B16="";"";PROCV(B16;Produtos;7))
AR16 =SE(AL16=0,05;AE16*AL16;"")
Euros
AS16 =SE(AL16=0,21;AE16*AL16;"")
Euros
AT17 =PROCV(B17;B$16:B16;1;FALSO)
34 – Copie a fórmula existente na célula H16. Seleccione as restantes células abaixo com a mesma
tipologia e cole só as fórmulas. Repita a operação para as restantes fórmulas existentes na
mesma linha, e na célula AT17.
Facturação Avançada.doc
Página n.º 5/9
Prof. Helder Vicente
35 – Na célula E16
coloque a regra de
validação apresentada
ao lado. Copie a
célula. Seleccione as
restantes células
abaixo com a mesma
tipologia e cole só as
regras de validação.
36 - Na célula B17
coloque a regra de
validação apresentada
ao lado. Copie a
célula. Seleccione as
restantes células
abaixo com a mesma
tipologia e cole só as
regras de validação.
37 – Na célula H16 efectue a formatação
condicional apresentada ao lado. A
letra é de cor vermelha a negrito e o
sombreado da célula a amarelo.
Efectue a mesma formatação
condicional para as colunas: Emb.,
Preço Unit., Total e IVA. Copie e cole só os formatos nas células imediatamente abaixo. Mais
uma vez terá de ajustar o aspecto gráfico. Efectue a mesma formatação para as células L52 e
AE51, copie e cole o formato para as células imediatamente abaixo.
38 – Passamos à colocação de fórmulas e formatações no fecho da factura. Assim:
Célula
Fórmula/ Função
AE51 Soma dos valores acima
L52
Soma do IVA a 5%
L53
Soma do IVA a 21%
Formato/ Outros
Negrito
AE52 =L52+L53
AE53 =AE51+AE52
Negrito
AE54 =SE(L58="";"";L58-AE53)
Se negativo – Valor a vermelho com sinal menos
39 – Na formatação da forma de pagamento começamos por formatar os botões de opção. Assim,
seleccione o 1º botão (Bt. Dtº/ Formatar controlo.../ Controlo) e efectue a ligação do botão à
célula $E$58 (célula que fica por baixo do botão “Dinheiro” e cujo conteúdo não deverá ficar
visível). Vá para a célula E58 e verifique se a mesma apresenta valor um para cheque, dois
para dinheiro e três para cartão.
40 – Vamos, agora, optimizar a funcionalidade dos botões com a introdução de mais algumas
fórmulas, funções e macros. Ou seja, quando pressionamos um dos botões limpamos os dados
relativos ao outro. Assim:
Facturação Avançada.doc
Página n.º 6/9
Prof. Helder Vicente
Célula
Fórmula/ Função
H56
=SE(E58=1;"N.º";"")
Q56
=SE(E58=1;"Banco";"")
H58
=SE(E58=1+OU(E58=2);"Montante";"")
H60
=SE(E58=3;"N.º";"")
J60
=SE(E58=3;PROCV(Z5;Clientes;10;FALSO);"")
Q60
=SE(E58=3;"Validade";"")
Agora, efectue as seguintes formatações condicionais:
Célula
Fórmula
J56
=$E$58=1
T56
=$E$58=1
L58
=$E$58=1
e
=$E$58=2
T60
=$E$58=3
J60
=$E$58=3
Tipo de letra
Negrito
Limite
Padrões
Traço simples no
contorno
Sombreado da célula:
Azul claro
Segundo traço na
base das células
Sem cor
Efectue as seguintes macros e atribua-as aos respectivos botões:
Nome da Macro
Funcionalidade
PagamentoCheque
Limpa as células J56, T56, L58 e T60
Selecciona as células pela ordem J56, T56 e L58
PagamentoDinheiro
Limpa as células J56, T56, L58 e T60
Selecciona a célula L58
PagamentoCartao
Limpa as células J56, T56, L58 e T60
Selecciona a célula T60
Atribuir ao botão:
41 – Altere a forma de deslocamento da selecção para a direita após premir a tecla de Enter (Menu
Ferramentas/ Opções.../ Editar/ Mover a selecção após premir Enter Direcção: Para a direita).
Verifique a funcionalidade das macros, principalmente a PagamentoCheque, ou seja,
pressionando a tecla Enter, a selecção salta para Banco e depois para Montante.
42 – Coloque a seguinte regra de validação para a célula da Validade do Cartão de crédito:
Facturação Avançada.doc
Página n.º 7/9
Prof. Helder Vicente
43 – Muitas outras formatações e condicionantes poderíamos colocar nesta factura, no entanto, esta,
já se encontra bastante funcional, contudo caso tenha tempo e interesse poderá fazê-lo. Vamos
então concluir a funcionalidade da factura com a gestão da base de dados. Na célula H2 da
folha produtos, digite a seguinte Função: =PROCV(A:A;Factura!$B$16:$G$50;4;FALSO).
Na célula I2 digite a função: =SE(É.NÃO.DISP(H2)=VERDADEIRO;G2;G2-H2).
44 – Passe para a folha da factura e defina uma vista com o nome Formas de pagamento para a
zona com o mesmo nome.
45 – Na folha factura efectue as seguintes macros.
Nome da Macro
ActualizarStocks
FormasPagamento
LimparFactura
ImprimirFactura
Funcionalidade
Vá à folha Produtos
Seleccione e copie a coluna I
Cole só os valores na coluna G
Seleccione uma célula vazia
Guarde o ficheiro
Volte à folha factura
Que me desloque para a vista Formas de pagamento
Copiar a célula AR2 e colá-la na célula AK2.
Seleccionar e apagar o conteúdo das células referentes a: Código do cliente;
Vendedor; Código e quantidades dos produtos; Número do cheque; Banco;
Montante; Validade e Observações.
Seleccionar as células referentes a: Código do cliente; Vendedor; Código e
quantidades dos produtos; ficando como célula activa a do Código do
cliente.
Executar a macro “ActualizarStocks”.
Definir a área de impressão da factura.
Pré-visualizar a impressão.
46 – Crie uma nova barra de ferramentas com o nome Factura.
Adicione-lhe três botões e atribua-lhes as respectivas macros de
acordo com a figura apresentada ao lado.
47 – Verifique que tudo funciona correctamente e poderá aperfeiçoar, se necessário, situações tais
como o número de cópias, Impressão a preto, etc...
48 – Chegou a altura de proteger a factura, para tal efectue os seguintes passos:
)
Seleccione as linhas 68:65536 e oculte-as:
)
Seleccione e desproteja as células onde terá de introduzir dados, assim como as células
AK2, AR2 e E58;
Facturação Avançada.doc
Página n.º 8/9
Prof. Helder Vicente
)
Seleccione as colunas AR:IV e oculte-as;
)
Proteja a folha activando simplesmente “Seleccionar células desprotegidas;
)
Remova todos os elementos que não necessita na área de trabalho. Tais como: Barras de
deslocamento, Barras de ferramentas (excepto Factura), Separadores de folhas, linhas de
grelha, etc...
49 – Concluiu-se assim esta parte trabalho. Verifique a sua funcionalidade e mas mais uma vez
poderá optimiza-lo e completá-lo de acordo com as suas necessidades e/ou interesses.
Situações ainda a desenvolver:
)
Menu
)
Folha de caixa
)
BD vendedores
)
Resultados dos vendedores
Facturação Avançada.doc
Página n.º 9/9
Prof. Helder Vicente