ABRIL 2016 - ocam.org.mz

FORMAÇÃO

SEG2016

Excel Aplicado à Contabilidade (versão 2010 e 2013)

ABRIL2016

www.occ.pt

Raul M. S. Laureano

Excel Aplicado à Contabilidade (versão 2010 e 2013)ORDEM DOS CONTABILISTAS CERTIFICADOS

2

FICHA TÉCNICA

Título: Excel Aplicado à Contabilidade (versão 2010 e 2013)

Autor: Raul M. S. Laureano

Capa e paginação: DCI - Departamento de Comunicação e Imagem da Ordem dos Contabilistas Certificados

© Ordem dos Contabilistas Certificados, 2016

Impresso por Jorge Fernandes, Lda em abril de 2016

Depósito-Legal:

Não é permitida a utilização deste Manual, para qualquer outro fim que não o indicado, sem autorização prévia e por escrito da Ordem dos Contabilistas Certificados, entidade que detém os direitos de autor.


3

3

ÍNDICE

1. Introdução --------------------------------------------------------------------------------- 5 1.1 TESTE INICIAL DE DIAGNÓSTICO ------------------------------------------------------ 7

2. Macros de comando ----------------------------------------------------------------------- 8 2.1 CONCEITO DE MACROS ----------------------------------------------------------------- 8 2.2 GRAVAÇÃO DE MACROS COM REFERÊNCIAS RELATIVAS E COM REFERÊNCIAS ABSOLUTAS --------------------------------------------------------------------------------- 10 2.3 OPERAÇÕES NO EXCEL ÚTEIS NAS MACROS ---------------------------------------- 15 2.4 ATRIBUIÇÃO DE MACROS A BOTÕES E MENUS ------------------------------------- 18 2.5 CASO PRÁTICO: IMPORTAÇÃO DE MAPAS CONTABILÍSTICOS DE OUTRAS APLICAÇÕES -------------------------------------------------------------------------------- 22

2.5.1 Importar dados a partir de um ficheiro de texto --------------------------- 23 2.5.2 Perguntas de autoavaliação ---------------------------------------------------- 27

3. Análise de mapas com informação contabilística ---------------------------------- 29 3.1 CONCEITO DE TABELA DE DADOS --------------------------------------------------- 29 3.2 FILTRAGEM E ORDENAÇÃO ----------------------------------------------------------- 30 3.3 SUBTOTAIS ----------------------------------------------------------------------------- 40

3.3.1 Ferramenta SubtotaI ------------------------------------------------------------ 40 3.3.2 Função SUBTOTAL --------------------------------------------------------------- 42

3.4 FORMATAÇÃO CONDICIONAL -------------------------------------------------------- 44 3.5 IMPRESSÃO DE TABELAS -------------------------------------------------------------- 52 3.6 TABELAS DINÂMICAS ------------------------------------------------------------------ 55 3.7 CASO PRÁTICO: CONTAS DE EXPLORAÇÃO ----------------------------------------- 67

3.7.1 Perguntas de autoavaliação ---------------------------------------------------- 68

4. Elaboração de mapas de serviço da dívida ------------------------------------------ 69 4.1. FUNÇÕES FINANCEIRAS -------------------------------------------------------------- 69

4.1.1 Proteção de células ------------------------------------------------------------- 78 4.2. CASO PRÁTICO: CALCULADORA FINANCEIRA ------------------------------------- 80 4.3. CASO PRÁTICO: MAPA DE SERVIÇO DA DÍVIDA ------------------------------------ 80

4.3.1 Perguntas de autoavaliação ---------------------------------------------------- 81

5. Elaboração de mapas com informação contabilística-financeira --------------- 82 5.1 FUNÇÕES DE DATA -------------------------------------------------------------------- 82 5.2 FUNÇÕES DE CONTAGEM E SOMA CONDICIONADAS ------------------------------- 85 5.3 FUNÇÕES LÓGICAS -------------------------------------------------------------------- 88 5.4 FUNÇÕES DE PROCURA E REFERÊNCIA --------------------------------------------- 90 5.5 FUNÇÕES DE TEXTO ------------------------------------------------------------------ 93 5.6 FUNÇÕES DE INFORMAÇÃO ----------------------------------------------------------- 96

5.6.1 Tipos de erros -------------------------------------------------------------------- 97


4

4

5.7 FUNÇÕES FINANCEIRAS RELACIONADAS COM O CÁLCULO DE DEPRECIAÇÕES E AMORTIZAÇÕES ---------------------------------------------------------------------------- 98 5.8. CASO PRÁTICO: MAPA DE DEPRECIAÇÕES E REINTEGRAÇÕES ----------------- 101

5.8.1 Perguntas de autoavaliação --------------------------------------------------- 102

6. Utilização do Excel em tarefas de conferência ---------------------------------- 103 6.1 VALIDAÇÃO DE DADOS --------------------------------------------------------------- 103 6.2 FORMATAÇÃO CONDICIONAL BASEADA EM FÓRMULAS -------------------------- 111 6.3 CASO PRÁTICO: NUMERAÇÃO SEQUÊNCIAL DE DOCUMENTOS ------------------ 114

6.3.1 Remover dados duplicados ---------------------------------------------------- 116 6.4 CASO PRÁTICO: REGULARIDADE DE MOVIMENTOS ------------------------------- 118 6.5 CASO PRÁTICO: ELABORAÇÃO DE MAPA DE ANTIGUIDADE DAS DÍVIDAS ------ 119

6.5.1 Filtros avançados: pequeno exemplo de aplicação ------------------------ 121 6.5.2 Perguntas de autoavaliação --------------------------------------------------- 122

7. O Excel para reporte de estimativas e resultados ao gestor ------------------ 123 7.1 ENCONTRAR VALOR QUE PERMITE ATINGIR OBJETIVO -------------------------- 123 7.2 TENDÊNCIA ---------------------------------------------------------------------------- 125 7.3 ELABORAÇÃO DE GRÁFICOS AVANÇADOS ----------------------------------------- 127 7.4 CASO PRÁTICO: INDICADORES DE DESEMPENHO --------------------------------- 130

7.4.1 Perguntas de autoavaliação --------------------------------------------------- 131

8. Teste de autoavaliação --------------------------------------------------------------- 132 8.1 TESTE FINAL DE DIAGNÓSTICO ----------------------------------------------------- 134

9. Trabalho futuro ------------------------------------------------------------------------ 135 9.1 CASO PRÁTICO: DASHBOARD ------------------------------------------------------- 135 9.2 CASO PRÁTICO: VERIFICAÇÃO DE PAGAMENTOS --------------------------------- 147

10. Conclusão ------------------------------------------------------------------------------ 149

11. Bibliografia em português ---------------------------------------------------------- 150

QUESTIONÁRIO................................................................................................. 151


5 5

1. INTRODUÇÃO

Nos últimos 20 anos verificou-se um grande desenvolvimento das Tecnologias da Informação (TI). Esta evolução levou a que os processos de trabalho individual tenham mudado bastante.

No âmbito das TI encontram-se as ferramentas de produtividade, entre as quais a folha de cálculo Microsoft Excel, que quando exploradas adequadamente conduzem a um aumento da produtividade e da qualidade da informação disponibilizada.

É neste contexto de uma necessidade de formação continua que este manual “EXCEL APLICADO À CONTABILIDADE (versão 2010 e 2013)” se insere, ao pretender- -se que os leitores desenvolvam competências para uma utilização avançada de folhas de cálculo no dia-a-dia da sua vida profissional. Em particular pretende-se:

1. Evidenciar as potencialidades do Excel na análise da informação contabilística;

2. Ilustrar a aplicação do Excel para controlo financeiro e tarefas de verificação;

3. Revelar a utilidade das diversas categorias de funções do Excel na elaboração de mapas financeiros;

4. Demonstrar o contributo das macros de comando para o aumento da produtividade.

O manual encontra-se estruturado por necessidades do utilizador decorrentes do trabalho diário e não por temas do Excel, conduzindo, assim, a uma aprendizagem mais facilitada. Em cada capítulo são analisados exemplos e casos práticos em que após uma breve descrição dos comandos (funcionalidades ou ferramentas) e/ou funções do Excel utilizadas na resolução dos problemas se apresenta a sua resolução passo-a-passo. No final do capítulo haverá sempre um pequeno exercício para que o leitor, de forma autónoma, possa avaliar os conhecimentos adquiridos.

A leitura deste manual deve ser acompanhada pela prática, no ambiente Excel, sendo, para o efeito, facultado um livro de Excel com todos os exemplos e casos apresentados neste manual. Só com muita prática se consegue assimilar os conteúdos transmitidos no manual, não esquecendo que a Ajuda (Help) é fundamental quando se pretende aprofundar os conhecimentos sobre alguma funcionalidade ou função da ferramenta.

Na elaboração deste manual optou-se pela utilização do Microsoft Excel 2010 em inglês por uma questão de coerência com o idioma em que o VBA está disponível e também por grande parte das instituições de ensino superior disponibilizarem aos


6

6

alunos a versão inglesa da ferramenta. De qualquer forma, as funcionalidades do Excel aqui apresentadas são também contempladas nas versões 2013 e 2016, pelo que os leitores que já utilizem versões mais recentes não são prejudicados.

Por fim, os agradecimentos para os colegas e amigos António Martins, Isabel Pedrosa, Márcia Santos, Luís Laureano, Pedro Proença e Sérgio Pontes pela revisão deste manual em termos pedagógicos e/ou científicos.

Antes de começar a leitura sugere-se a realização de um teste de diagnóstico para que o leitor possa avaliar os seus conhecimentos. No final do manual, outro teste de diagnóstico é disponibilizado para que o leitor possa avaliar a sua aprendizagem e identificar necessidades de mais formação em Excel.

Encontro-me ao dispor dos leitores para quaisquer esclarecimentos relacionados com o manual ou com um Excel, pelo que podem enviar as vossas dúvidas, comentários, sugestões, exemplos de problemas,… para o endereço de correio eletrónico [email protected].

Boa leitura e, essencialmente, boa prática de Excel!

ISCTE-IUL, 21 de março de 2016

O autor

Raul Laureano


7 7

1.1 TESTE INICIAL DE DIAGNÓSTICO


8 8

2. MACROS DE COMANDO

2.1 CONCEITO DE MACROS

O Visual Basic for Applications (VBA) é a linguagem de programação que permite automatizar operações nas aplicações do Microsoft Office. Oferece um ambiente de programação (o editor de Visual Basic) para trabalhar com aplicações Microsoft Office (Word, Excel, Access e PowerPoint).

Existem dois tipos de macros: as macros de comandos e as macros criadas pelo utilizador através da linguagem de programação Visual Basic:

As macros de comandos armazenam sequências de operações do utilizador no Excel. O utilizador grava a sequência de operações, que fica guardada num módulo, e que poderá executá-la posteriormente. A grande vantagem das macros de comando está na facilidade da sua construção;

As macros criadas pelo utilizador através da linguagem de programação Visual Basic são escritas no Editor de Visual Basic de um modo semelhante às funções definidas pelo utilizador. Para a criação destas macros é necessário conhecer a linguagem de programação (VBA);

o As funções definidas pelo utilizador, em linguagem de programação VBA, por vezes aparecem referenciadas como uma categoria de Macros.

Instalar e ativar o VBA

Para ter acesso aos comandos relacionados com as macros e VBA é necessário:

FILE | Options... Depois separador Customize Ribbon e selecionar na janela Main Tabs a opção Developer e OK. Um novo menu aparecerá na barra de menus.

Atualmente os vírus nos computadores são comuns; por isso existem vários níveis de proteção para que os utilizadores menos experientes possam estar protegidos:


9

9

O primeiro nível de segurança é não instalar as componentes de suporte à linguagem, impedindo assim que as macros (possíveis vírus) sejam executadas;

O segundo nível de segurança (o mais frequente) é as componentes estarem já instaladas mas desativadas.

Extensão dos livros com macros

Os documentos com macros gravadas têm que ser guardados com um formato especial em que a extensão é .xlsm e não .xlsx:

Nas versões antigas do Excel (Modo de Compatibilidade) a gravação normal no formato .xls contempla também as macros.


10 10

Editor de Visual Basic

O editor de Visual Basic pode ser aberto recorrendo ao botão Visual Basic ou com o atalho Alt + F11:

O editor de Visual Basic tem a seguinte aparência:

As macros mais usuais são gravadas ou criadas nos módulos, que podem ser criados com Insert | Module.

2.2 GRAVAÇÃO DE MACROS COM REFERÊNCIAS RELATIVAS E COM REFERÊNCIAS ABSOLUTAS

Para iniciar-se a gravação duma macro (macro de comando) recorre-se ao menu Developer, premindo-se de seguida o botão Record Macro:


11

11

Atribuir o nome à macro (atenção que existem restrições), definir um atalho shortcut (pode-se usar também a tecla Shift), indicar onde gravar a macro (no livro atual ou no livro pessoal) e escrever um texto descritivo sobre a finalidade da macro:

Depois de premir o botão OK, tudo o que for feito no Excel fica gravado até que se prima o botão Stop Recording:

Durante a gravação duma macro pode alternar-se entre referências absolutas e referências relativas (botão Use Relative References premido):

De uma forma geral, quando se quer que a ação gravada seja sempre na mesma célula ou intervalo de células (range) utiliza-se a referência absoluta.

Pelo contrário, quando a ação deve ser realizada em função da célula que se encontra ativa, então recorre-se às referências relativas.


12

12

Para executar a macro recorre-se:

No editor de VB, tendo o Module selecionado, ao botão Run Macro:

No Excel, ao botão Macros:

Ver as opções disponíveis na janela macro: Run, Edit, Delete, Options...

Ainda no editor de VB, tendo o cursor numa linha do procedimento (Sub), ao botão Run Sub/UserForm:

Exemplo 1: Grave uma macro que formate os valores inseridos no intervalo B3 a B6.


13

13

Com o botão Record Macro:

Formate as células da forma adequada e termine a gravação (Stop Recording). O resultado no editor de Visual Basic é:

Note-se que as macros ficam gravadas num módulo e começam pela palavra Sub e terminam com End Sub. Os comentários no código são antecedidos por um ‘. Um módulo pode ter várias macros.

E agora, qual a diferença entre gravar a macro antes de selecionar o intervalo e depois de o ter selecionado?

Exemplo 2: Grave uma macro que escreva os dados do autor no intervalo A2:A4, formatados em bold.


14

14

Experimente, apague o conteúdo e o formato das células e execute a macro.

Exemplo 3: Grave, agora, uma macro que escreva os dados do autor a partir da célula ativa, formatados em bold.

Não esquecendo de gravar a macro com referências relativas, obtém-se:

Em termos de código, qual a diferença entre referências absolutas e referências relativas?


15

15

2.3 OPERAÇÕES NO EXCEL ÚTEIS NAS MACROS

Muitas das macros de comando estão associadas à gestão de tabelas de dados, tornando-se, por isso, necessário conhecer a forma de, por exemplo, ir para a próxima célula em branco numa coluna, copiar uma célula para uma tabela e apagar uma linha da tabela.

Selecionar a próxima célula em branco numa coluna. No exemplo, deverá ficar selecionada a célula F4 (a próxima a ser preenchida).

Selecionar a célula F2, premir a tecla End seguida da seta para baixo ↓. Fica na última célula preenchida (F3). Agora, é premir novamente a seta ↓.

Selecionar todas as células preenchidas de uma coluna (exceto a célula do título):

Selecionar a primeira célula preenchida F4; Premir em simultâneo as teclas Ctrl, Shift e ↓ .

Preencher células em branco com os valores das células anteriores:


16

16

1. Selecionar a célula A1;

2. No menu Home selecionar Find & Select e depois Go To Special... Aí, selecionar Current region (fica a tabela toda selecionada) seguido de OK:

3. Fazer outra vez Find & Select e Go To Special..., mas agora selecionar Blanks (ficam todas as células em branco da tabela selecionadas;

4. Agora para inserir uma fórmula matriz premir as teclas = ↑ (igual à anterior). Termine premindo em simultâneo as teclas Ctrl e Enter.

Grave uma macro que faça esta operação automaticamente.

Exemplo 4: Introduza os dados de um cheque emitido em B3:B6 e grave uma macro que copie os valores para a próxima linha em branco na tabela Listagem de cheques emitidos.


17

17

Iniciar a gravação da macro:

1. Em modo de referências absolutas selecionar B3:B6

2. Fazer Copy;

3. Selecionar a célula E2 (continuando em modo absoluto);

4. Mudar para referências relativas e premir End seguido de ↓; Premir novamente ↓;

5. Fazer Paste Special... e selecionar as opções Transpose e Values:

6. Mudar para referências absolutas;

7. Selecionar o intervalo B4:B6 e apagar o seu conteúdo;

8. Selecionar a célula B4;

9. Terminar a gravação da macro.

Grave agora uma macro que formate na tabela os valores copiados.


18 18

2.4 ATRIBUIÇÃO DE MACROS A BOTÕES E MENUS

Para aumentar a rapidez na execução das macros é possível associar uma macro a:

um ícone da barra de ferramentas de acesso rápido;

uma opção de um menu;

um botão na folha.

Os passos a seguir para cada uma das opções são:

Ícone na barra de acesso rápido - a personalização da barra de ferramentas é possível realizando as seguintes operações:

1. File | Options... e Quick Access Toolbar;

2. Selecionar Macros em Choose commands from;

3. Selecionar em Customize Quick Access Toolbar o âmbito pretendido para esta personalização do Excel (só livro actual):

4. Selecionar a macro pretendida e premir o botão Add >>;

5. Premir o botão Modify... para atribuir um ícone e alterar o nome;

6. Terminar a personalização e testar o resultado.


19 19

Opção de menu - a inserção de uma opção num menu novo é possível realizando as seguintes operações:

1. File | Options... e Customize Ribbon;

2. Selecionar Macros em Choose commands from;

3. Selecionar em Customize the Ribbon o local pretendido para esta personalização do Excel (Main Tabs);

4. Premir o botão New Tab. De seguida, premindo o botão Rename atribuir os nomes ao menu (Minhas Macros) e ao grupo dentro do menu (Cheques);

5. Com o grupo Cheques selecionado, selecionar a macro pretendida e premir o botão Add >>;

6. Premir o botão Rename... para atribuir um ícone e alterar o nome;



20

20

Experimente criar um grupo dentro de um menu já existente.

Botão na folha - a inserção de um botão é possível realizando as seguintes operações:

1. Developer | Insert... e escolher Button (Form Control);

2. Desenhar um retângulo na folha onde pretende inserir o botão;

3. Na janela Assign Macro selecionar a macro pretendida (Lancar_Cheque);

4. OK.


21

21

5. Em Design Mode premir o botão direito do rato sobre o botão e explorar as opções Format Control... (mudar nome e formatar a bold) e Assign Macro..

.

(também é possível mudar o nome diretamente no botão ou Edit Text);


Botão na folha - a inserção de um botão é também possível realizando as seguintes operações:

1. Developer | Insert... e escolher Command Button (ActiveX Control);

2. Desenhar um retângulo na folha onde se pretende inserir o botão;

3. Premir botão Properties e alterar Caption (para Lançar Cheque 2)

4. No editor de VB, na folha Macros_Ex4 escrever o nome da macro a executar (Lancar_cheque). Note que a macro é executada quando faz click (premir o botão);


22

22


2.5 CASO PRÁTICO: IMPORTAÇÃO DE MAPAS CONTABILÍSTICOS DE OUTRAS APLICAÇÕES

Um software de contabilidade exporta os diversos mapas que produz para um formato de texto (.txt). Tenha em consideração o documento ExtratoConta71.txt:

Grave uma macro que abra o documento e o converta numa tabela de Excel com o seguinte layout:


23

23

Antes de gravar a macro deve fazer todo o procedimento no Excel e ir anotando os passos para, aquando da gravação, não se esquecer de nenhum.

2.5.1 Importar dados a partir de um ficheiro de texto

A importação de dados de outras aplicações, para um livro já aberto, é realizada através do menu Data:

Para importar o documento de texto os passos são:

1. Data | From Text;

2. Selecionar o ficheiro ExtratoConta71.txt e premir o botão Import para aceder ao assistente de importação de texto:


24

24

3. Como os campos não estão separados, por exemplo, por vírgulas, manter a opção Fixed Width ativa e manter igualmente a importação a partir da linha 1. Premir Next para passar à fase dois do assistente:

4. Deslocar-se com o elevador vertical para visualizar as linhas mais abaixo do documento. Fazer um duplo clique sobre a linha vertical que separa o campo 3 do campo 4 (linha a seguir ao número 21) para remover a separação dos campos (não tem interesse separar os dois campos). Premir no botão Next:


25

25

5. Alterar o formato da data para YMD. Premir o botão Finish para abrir a caixa de diálogo Import Data;

6. Selecionar a célula onde se pretende começar a colar os dados. Note-se que pode ser numa nova folha ou numa já existente. Premir o botão OK:

O resultado no Excel é:


26

26

7. Agora é apagar as linhas que estão a mais, inserir os títulos dos campos (colunas), formatar os números em moeda,…

Para juntar texto (concatenar) usa-se uma fórmula com o operador de concatenação &. Neste caso é útil para juntar o nome da empresa a que se refere o extrato e apresentá-lo na célula A1:

A1 =A5&B5

Como se vão apagar linhas, entre as quais a linha 5, deve-se converter a fórmula para valor. No modo de edição da fórmula (entrar neste modo com a tecla F2) premir a tecla F9 e de seguida a tecla Enter;

8. Considerando, agora, que apenas se pretende ter informação das vendas mensais, eliminam-se as linhas não relevantes.

Começa-se por colocar um filtro na coluna B1, selecionando todos os discriminativos exceto “Vendas Mensais”.

1 A aplicação de filtros encontra-se explicada no ponto 3.2 Filtragem e Ordenação.


27

27

De seguida selecionar as linhas visíveis e com o botão direito do rato executar Delete Row.

9. Repetir todos os passos, mas agora no modo de gravação de macros.

Note-se que a macro deve funcionar para a importação de qualquer extrato que tenha o mesmo nome e layout, isto é, os conteúdos (nome da empresa, número da conta, primeira data, …) devem ficar no Excel nas mesmas células. Para formatar os valores não esquecer de usar referências relativas! Pode ser vantajoso gravar duas macros em separado, uma para a importação e outra para as formatações. Pode também ser útil inserir um ícone para executar a macro.

2.5.2 Perguntas de autoavaliação

Assinale apenas a opção de resposta que considera mais adequada:

a) O botão ao lado não serve para

Apagar macros

Executar macros

Editar macros

Entrar no editor de Visual Basic

b) Para numa macro escrever um texto sempre na mesma célula recorre à utilização

Da operação de concatenação

De referências absolutas

Das teclas Shift e Ctrl

Nenhuma das anteriores

c) Para facilitar a execução de macros pode

Criar teclas de atalho

Criar um ícone na barra de ferramentas

Inserir um botão de comando na folha

Todas as anteriores

d) Um livro de Excel com macros deve ter a extensão

xlsb

xlsm

xlsx

É indiferente a extensão


28

28


29 29

3. ANÁLISE DE MAPAS COM INFORMAÇÃO CONTABILÍSTICA

3.1 CONCEITO DE TABELA DE DADOS

Uma forma de armazenar dados relacionados (faturas, clientes, produtos,...) numa folha de cálculo é através de uma tabela (ou lista ou base de dados). Numa tabela de dados as linhas são registos e as colunas são os campos. A primeira linha da lista contém rótulos (nomes) para as colunas (campos).

No separador Data encontram-se diversas ferramentas que facilitam a gestão das tabelas de dados, entre as quais, a ordenação, filtragem, validação e subtotais:

Na criação de tabelas devem-se ter alguns cuidados:

1. Evitar mais do que uma lista numa folha;

2. As colunas devem ter nome em apenas uma célula;

3. Não utilizar linhas em branco entre os dados;

4. Não inserir dados nas células abaixo da lista;

5. Não inserir espaços em branco no início do conteúdo das células;

6. Usar o mesmo tipo de informação e o mesmo formato para as células de cada coluna;

7. Evitar colocar dados essenciais à esquerda ou à direita da lista (os dados podem ficar ocultos quando se filtra a lista);

8. Fixar a primeira linha de forma a estar sempre visível.

Um exemplo duma tabela de dados pode ser o extrato de um fornecedor. Note-se que a tabela está no intervalo de A5:M13:


30

30

Quando as tabelas são importadas de outras aplicações é fundamental garantir que os dados estão corretamente inseridos. Os principais problemas residem nos números (e nas datas) que ficam inseridos como texto, o que acontece devido aos separadores utilizados e que o Excel, muitas vezes, não reconhece.

No exemplo, as datas não estão inseridas como datas, mas sim como texto, o que inviabiliza a utilização das funções de datas e a aritmética de datas. Uma hipótese para corrigir estas situações frequentes é gravar uma macro que substitua na coluna das datas o “.” por “-“ e, assim, o Excel já reconhecerá as datas:

1. Selecionar as duas colunas das datas;

2. File | Find & Select | Replace…;

3. Premir o botão Replace All;

4. Verificar se as datas ficaram alinhadas à direita (as datas são números).

3.2 FILTRAGEM E ORDENAÇÃO

A filtragem é um meio fácil e rápido de localizar e trabalhar com subconjuntos de dados. A filtragem oculta temporariamente as linhas que não se deseja visualizar, isto é, oculta os registos que não satisfazem as condições definidas no critério de filtragem.

Para aplicar filtros seguem-se alguns passos:

1. Com uma célula ativa da tabela (por vezes é melhor selecionar a tabela toda) premir o botão Data | Filter.

Verificar se o Excel reconheceu os títulos dos campos, colocando o símbolo de filtro em cada um:


31

31

2. Selecionar o critério pretendido. Os critérios podem ser simples, quando apenas contemplam uma condição, ou compostos, quando envolvem mais do que uma condição. Os tipos de critério a aplicar dependem da natureza do campo (data, número, texto):

Data – permite selecionar os registos de um ano, de um trimestre, de um mês, entre duas datas,…:

Número – permite selecionar os registos com valor superior a um dado número, com valor inferior à média,…:


32

32

Texto - permite selecionar os registos com o texto começado ou terminado por um dado conjunto de caracteres, com texto diferente dum dado texto, …:

Na escrita dos critérios podem-se usar dois caracteres especiais: o ? que representa um único carácter; e o * que representa um conjunto de caracteres (pode ser um conjunto vazio). Quando na tabela existem formatações de cores é possível também definir critérios baseados na cor da letra, da célula,…


33

33

Por exemplo, para selecionar os registos com valor (campo montante) no intervalo ]0 ; 1000] recorre-se a:

Após a aplicação do critério (premindo o botão OK) obtem-se:

Note-se que algumas linhas ficaram ocultas (os números das linhas aparecem a azul significando que está um filtro ativo, o que pode ser visto também pela alteração do botão associado ao título do campo do montante

- ).

3. Pode-se aplicar outro critério a um outro campo, dando origem a uma conjunção de critérios, ou remover a aplicação do filtro, premindo o botão

Data | Clear ( ).

A ordenação de tabelas (ou apenas de um intervalo de células) é uma operação muito frequente e útil. A ordenação pode ter por base apenas um critério ou múltiplos critérios, podendo a ordem ser ascendente, descendente ou uma ordem predefinida numa série.

Uma forma rápida de ordenar é através dos botões do separador Data:

(ascendente) ou (descendente). Note-se que nestes casos a ordenação é realizada segundo o campo correspondente à célula ativa.


34

34

No caso de se pretenderem definir vários critérios de ordenação recorre-se ao

botão de ordenação . É necessário verificar se a tabela fica completamente selecionada, incluindo a linha dos títulos dos campos (ficando a opção My data has headers selecionada):

Os botões disponíveis na janela Sort permitem adicionar critérios de sub-ordenação (botão Add Level), apagar um critério de sub-ordenação (botão Delete Level), copiar um critério de ordenação (botão Copy Level) e definir as opções diferenciar maiúsculas de minúsculas e/ou a orientação da ordenação – em linha ou em coluna (botão Options…):

No exemplo seguinte procede-se à ordenação da tabela segundo o montante (é um campo numérico em que se ordenam os valores do menor para o maior). Em caso de registos com o mesmo montante, o segundo critério de ordenação é a data do documento (campo data em que se ordenam as datas da mais antiga para a mais recente). Havendo ainda registos com montante e data iguais, o terceiro critério de ordenação é o texto do documento (é um campo de texto em que se ordenam os valores por ordem alfabética de A para Z):


35

35

O Excel permite também ordenar os registos não pelo seu valor, mas sim pela formatação aplicada (cor da célula, cor da letra ou icon da célula):

Exemplo 5: Mapa de vendas – ordene as vendas por: i) nome do cliente; ii) nome do cliente e data da venda (mais recente primeiro); e iii) por região (N, C, S):

A tabela de vendas encontra-se no intervalo B5:M23:

Para se realizarem as ordenações pretendidas seguem-se os seguintes passos:

Nome do cliente:

Selecionar uma célula no intervalo C5:C23;


36

36

Premir o botão .

Nome do cliente e data de venda:

Selecionar uma célula no intervalo B5:M23 (ou a tabela toda);

Premir o botão ;

Definir os dois níveis de critérios de ordenação:

Premir OK.

Região (N, C, S):

Selecionar uma célula no intervalo B5:M23 (ou a tabela toda);

Premir o botão ;

Definir como critério de ordenação a região. Como a ordem pretendida não é a alfabética (nem ascendente, nem descendente) tem que se definir uma lista (Custom List) com as três regiões:


37

37

Ao selecionar Custom List… é aberta uma nova janela:

Escrever na caixa List entries as três regiões e terminar com o botão Add:


38

38

Premir OK:

Premir OK. Obtém-se:

Exemplo 6: Mapa de vendas – aplique filtros por forma a visualizar: i) vendas de agosto; ii) vendas da primeira quinzena de agosto; iii) vendas de clientes com dois nomes; e iv) vendas com valor total inferior a 100 ou superior a 1000 euros:



39

39

Para se realizarem os filtros pretendidos, após a sua ativação com o botão Filter

do separador Data , definem-se os seguintes critérios:

Vendas de agosto (campo Data):

Vendas da primeira quinzena de agosto (campo Data):

Em:

Vendas de clientes com dois nomes (campo Nome):

Em:

Poderia ser também: nome igual a ?* *

Vendas com valor total inferior a 100 ou superior a 1000 euros (campo Valor Total)

Por exemplo, em:


40

40

3.3 SUBTOTAIS

O Excel permite obter subtotais através do comando Data | Subtotal ou da função SUBTOTAL.

3.3.1 Ferramenta SubtotaI

O comando permite obter de forma automática um resumo dos dados, apresentando alguns totais parciais e o total global. Os resultados parciais podem corresponder, entre outros, a contagens, somas e médias. Com este comando é possível definir quebras de página segundo determinado critério, permitindo, por exemplo, imprimir numa página diferente as vendas a cada cliente.

Previamente à aplicação do comando Subtotal deve-se ordenar a tabela de dados segundo o campo (critério) para o qual se pretende obter totais parciais. Após premir o botão Subtotal, as opções do comando são:

Exemplo 7: Mapa de vendas – obter subtotais por cliente.



41

41

Para se obterem os subtotais pretendidas seguem-se os seguintes passos:

Subtotal por cliente:

Selecionar a tabela (C5:M23) e ordená-la por Nome;

Premir o botão Data | Subtotal;

Indicar o pretendido, nomeadamente:

Em At each change in: indicar o campo Nome (que corresponde ao critério);

Em Use function: indicar a função pretendida, neste caso a função Sum para obter a soma dos valores das vendas a cada cliente;

Selecionar em Add subtotal to: os campos Ilíquido, I.V.A., Desconto, Valor Total e Horas, para os quais será calculada a soma a cada alteração do cliente;

Selecionar as opções Replace current subtotals, para substituir os subtotais que possam ter sido já criados, e Summary below data, para indicar que se pretende os subtotais depois do detalhe (da lista das vendas de cada cliente).

Note-se que a opção Page break between groups permite criar quebras de página no final de cada cliente e o botão Remove All permite remover todos os subtotais já existentes numa tabela de dados;


42

42

Premir o botão OK e visualizar o resultado:

O Excel inseriu automaticamente linhas para os subtotais e criou grupos que permitem com os botões ou mostrar ou esconder detalhes:

3.3.2 Função SUBTOTAL

Tal como o nome indica, a função Subtotal permite calcular um subtotal. A sua

aplicação é mais frequente em listas, filtradas ou não. Possui a seguinte sintaxe:

=SUBTOTAL(código_da_função;intervalo)


43

43

O argumento código_da_função (function_num) permite escolher qual o tipo de

total pretendido e ainda se o cálculo deve ter em conta as linhas escondidas ou

não. Se o valor do código for superior a 100 então as linhas escondidas serão

ignoradas. Se for inferior a 100 então as linhas escondidas também farão parte do

cálculo, embora linhas filtradas não. De seguida apresenta-se uma tabela com as

funções disponibilizadas:

Função

utilizada no cálculo

Código que ignora linhas escondidas

Código que inclui linhas escondidas mas não filtradas

Descrição da função

AVERAGE() 101 1 Média COUNT() 102 2 Conta números COUNTA() 103 3 Conta preenchidas MAX() 104 4 Máximo MIN() 105 5 Mínimo PRODUCT() 106 6 Produto STDEV.S() 107 7 Desvio-padrão amostra STDDEV.P() 108 8 Desvio-padrão população SUM() 109 9 Soma VAR.S() 110 10 Variância amostra VAR.P() 111 11 Variância população

Exemplo 8: Mapa de vendas – obter os subtotais número de vendas, valor total das vendas e número médio de horas por critério de filtragem: vendas de agosto.

Começa-se por inserir as fórmulas:

G4 =SUBTOTAL(2;G6:G23) - para o número de vendas fazendo a contagem nas datas;


44

44

K4 =SUBTOTAL(9;K6:K23) - para a soma do valor com IVA;

M4 =SUBTOTAL(1;M6:M23) - para a média do número de horas;

Depois das fórmulas aplica-se o filtro automático e define-se o critério. O resultado é:

3.4 FORMATAÇÃO CONDICIONAL

A formatação condicional permite aplicar formatação de células de forma seletiva e automática com base no conteúdo das mesmas. É bastante útil na deteção de células com entradas erradas ou de um determinado tipo e facilita muito a análise dos dados. Para aplicar a formatação condicional (ferramenta disponível no separador Home) devem seguir-se os seguintes passos:

1. Selecionar a célula ou intervalo de células a formatar;

2. Home | Conditional Formatting:


45

45

A escolha do tipo de formatação deve ter em consideração:

Realçar regras de células quando o objetivo é formatar células que satisfazem determinado critério:

Regras de superiores/inferiores quando a formatação visa realçar as células com maiores ou menores valores, com valores acima ou abaixo da média,…:

Barras de dados quando o objetivo é apresentar barras nas células de acordo com o seu valor, tendo a célula de maior valor a barra mais comprida:


46

46

Escalas de cores quando se pretende realçar as células com uma gradação de cores de acordo com o valor da célula:

Conjunto de ícones quando se pretende apresentar ícones nas células de acordo com os valores da célula:


47

47

Outras regras (New Rule…) quando o tipo de formatação pretendido não está contemplado nas opções anteriores. Por exemplo, quando a formatação deve ser baseada no resultado (verdadeiro ou falso) duma fórmula:

3. Definir os critérios de formatação e a respetiva formatação. Por exemplo, para formatar as células com número de horas superior ou igual a 10 horas a amarelo tem-se:

Selecionar o intervalo M6:M23;

Selecionar Home | Conditional Formatting | Highlight Cells Rules | Greater Than…:

Na janela indicar o valor 10 e escolher a formatação pretendida:


48

48

Note-se que pretendendo-se outra formatação não predefinida escolhe-se Custom Format…

Premir OK e o resultado é:

Para apagar ou alterar (editar) uma formatação condicional deve-se selecionar o intervalo de células e recorrer, respetivamente, às opções Clear Rules e Manage Rules… Nesta última opção é possível, para além de alterar a regra, adicionar uma nova ou apagar a regra existente:


49

49

Uma das alterações frequentes a uma regra visa indicar um intervalo de valores em que se aplica a formatação. No exemplo, poderá querer-se aplicar a formatação às células com número de horas no intervalo de 10 a 20, inclusive. Premir o botão Edit Rule… e selecionar between e indicar os valores limites do intervalo:

É de realçar ainda que, por vezes, não se sabe quais as células que têm definidas formatações condicionais. Nestes casos pode-se recorrer à procura deste tipo de formatação na folha.

Home | Find & Select:


50

50

Exemplo 9: Mapa de vendas – realce as células com o maior e menor número de unidades e aplique barras de dados ao valor total.

A tabela de vendas encontra-se no intervalo B5:M23 e os intervalos a aplicar a formatação condicional são o L6:L23, para as unidades (campo Unid.), e o K6:K23, para aplicar as barras de dados:

Para as unidades de maior e menor valor seguem-se os seguintes passos:

Selecionar o intervalo L6:L23;

Selecionar Home | Conditional Formatting | Top/Bottom Rules | Top10 items …:

Na janela indicar que se pretende apenas o maior valor e selecionar a formatação pretendida (pode ser a que está por defeito):


51

51

Premir OK. O resultado é:

Note-se que havendo várias células com o maior valor o formato é aplicado a todas elas.

Para aplicar uma formatação às células com o menor valor, o procedimento é similar, mas agora em Home | Conditional Formatting | Top/Bottom Rules | Bottom 10 items …

O resultado deverá ser:

Unid.211211213332121131

Para as barras de dados no campo Valor Total seguem-se os seguintes passos:

Selecionar o intervalo K6:K23;

Selecionar Home | Conditional Formatting | Data Bars | Gradient Fill e depois escolher uma cor. O resultado é:


52

52

Valor Total200.00

1,807.73200.00307.50307.50553.50200.00

1,807.73200.00553.50307.50307.50200.00

1,807.73200.00307.50553.50307.50

Experimentar ordenar a tabela!

3.5 IMPRESSÃO DE TABELAS

Regularmente as tabelas de dados estendem o seu conteúdo a várias páginas, pelo que, do ponto de vista do leitor, é necessário assegurar que todas as páginas estão identificadas (i.e., contenham o título e a identificação das colunas/linhas). As opções relacionadas com a impressão encontram-se no separador Page Layout:

Para repetir determinadas linhas e/ou colunas em todas as páginas recorre-se:

1. Page Layout | Print Titles;

2. Na janela Page Setup no último separador (Sheet) posicionar o cursor do rato em Rows to repeat at top;

3. Selecionar as linhas a repetir (1 a 5):


53

53

4. Premir o botão Print Preview para visualizar o efeito:

Explore outras opções do separador Page Layout. Por exemplo, pode ser útil:

Fazer uma quebra de página numa determinada linha. Selecione a linha onde pretende fazer a quebra e prima execute Breaks | Insert Page Break:

Inserir no rodapé (Footer) diversa informação. Por exemplo, pode ser útil indicar o autor, o caminho para o documento (livro) e a data de impressão: ©Raul Laureano C:\Users\rml\Documents\Formação\Exemplos_e_Casos_040413.xlsx 20-03-2014

Executar Page Layout | Print Titles e na janela Page Setup no separador Header/Foorter premir o botão Custom Footer…:


54

54

Depois escrever ©Raul Laureano e premir os botões Insert File Path e Insert Date:

Reduzir a dimensão da tabela por forma a ser impressa em apenas uma página. Executar Page layout | Print Titles e na janela Page Setup no separador Page marcar a opção Fit to:


55 55

3.6 TABELAS DINÂMICAS

As tabelas dinâmicas permitem apresentar os dados agrupados por categorias e dentro de cada categoria agrupados por outras categorias (de outro campo). Para cada conjugação de categorias podem-se calcular medidas estatísticas ou efetuar outros cálculos definidos pelo utilizador. Este tipo de tabelas é muito útil para resumir e classificar os dados e é facilmente atualizada (através do botão Refresh) quando a tabela (lista de dados) de origem sofre alterações. Este comando permite também apresentar os dados graficamente (gráfico dinâmico ou pivot chart).

Para criar uma tabela dinâmica (pivot table) devem seguir-se os seguintes passos:

1. Selecionar a tabela de dados (incluindo a linha com o título dos campos);

2. Separador Insert | Pivot Table:

3. Confirmar o intervalo com a tabela de dados base e indicar o local onde se quer inserir a tabela dinâmica: pode ser uma nova folha ou numa já existente:


56

56

4. Premir OK para criar a tabela:

5. Do lado esquerdo será inserida a tabela (em A3) e no lado direito aparece um painel com a lista de todos os campos da tabela de dados (original) onde se selecionam (arrastando) os campos que se pretendem visualizar na coluna (Column Labels) e/ou na linha (Row Labels). Para Σ Values arrasta-se o campo que servirá para os cálculos (por defeito fica a soma ou a contagem). Também pode ser definido em Report Filter um campo para filtrar a tabela;


57

57

6. Associados às tabelas dinâmicas surgem dois separadores (em PivotTable Tools - Ferramentas de Tabela Dinâmica):

a. Separador Options (Opções), onde se pode definir os campos e as funções de cálculo da tabela dinâmica, ordenar a tabela, criar gráficos dinâmicos, entre outras opções:

b. Separador Design (Estrutura), onde se pode definir o layout (esquema) da tabela e o estilo a aplicar:

7. No campo de cálculo (Σ Values) pode definir-se a função de cálculo, a forma de apresentar os valores (por exemplo, em valor absoluto ou em percentagem) e o formato do número.

Separador Options | Calculations:


58

58

8. Quando se pretendem analisar em simultâneo muitos dados deve-se, sempre, experimentar diversas configurações da tabela para selecionar aquela que melhor resume os dados e, consequentemente, permite a melhor interpretação.

Exemplo 10: Mapa de vendas – resuma os dados por região apresentando o número e o valor total das vendas e, também, o contributo percentual de cada região para as vendas totais:


Depois de criada a tabela dinâmica é necessário:

Indicar os campos pretendidos: Região para linha; Valor Total para o cálculo (Σ Values) três vezes (uma para a contagem, outra para a soma em euros e a última para o peso percentual):


59

59

Alterar o primeiro somatório para contagem:

Com o primeiro cálculo selecionado , alterar em Value Field Settings… a função para Count e o nome do campo para Nº:

Com o segundo cálculo selecionado , alterar em Value Field Settings… o formato numérico (Number Format) para moeda e o nome do campo para Valor:

Com o terceiro cálculo selecionado , alterar em Value Field Settings… a forma como o valor é apresentado (Show Value As) para percentagem em coluna (é o total da coluna que tem que dar 100%) e o nome do campo para Peso:


60

60


Alterar agora na tabela Row Labels para Região e Grand Total para Totais e os alinhamentos das células:

Exemplo 11: Mapa de vendas – apresente a soma das unidades por região e mês (dentro de cada região podem visualizar-se os nomes e dentro de cada mês o dia).


61

61



Indicar os campos pretendidos: Região para a linha; Nome para a linha; Data para a coluna; Unid. para o cálculo (Σ Values) e alterar a função para soma:

Selecionar as datas de cada mês e Options | Group Selection para as agrupar num mês. Por exemplo, para julho seleciona-se o intervalo B4:C4 e:


62

62

Foi criado o grupo que inclui as duas datas e inserido o botão que permite ocultar os detalhes (as datas agrupadas). Foi também acrescentado um campo à lista com o nome Data2 (que poderá ser alterado para Mês):

Note-se que, como alternativa e caso não se pretendesse visualizar os dias dentro de cada mês, poder-se-ia selecionar uma data (por exemplo, D4) e Options | Group Field para agrupar as datas do mês automaticamente:


63 63

Ajustar as formatações e alterar os nomes dos campos da tabela:

Pode-se, também, formatar o campo Data para que apareça apenas o dia do mês. Por exemplo:


64 64

Exemplo 12: Mapa de vendas – apresente o valor por hora para cada nome e com possibilidade de filtrar por região.


Indicar os campos pretendidos: Região para o filtro (Report Filter); Nome para a linha;

Criar um novo campo calculado Preço hora que corresponde ao Valor total a dividir por Horas:

Em Options | Fields, Items & Sets | Calculated Field definir o novo campo (nome do campo e fórmula de calculo) e terminar premindo os botões Add e, depois, OK:

Indicar o campo Preço hora para o cálculo (Σ Values) e alterar a função para média (average):


65

65

Ajustar as formatações necessárias e experimentar filtrar a tabela para apenas a região C:


66

66

Pode-se criar um gráfico dinâmico com os preços hora por região fazendo pequenos ajustes e:

0,00 €

50,00 €

100,00 €

150,00 €

200,00 €

250,00 €

300,00 €

N C S

Total

Total

Região

Average of Preço hora

Note-se que para apagar (limpar) todos os campos definidos recorre-se a Options | Clear | Clear All:


67 67

3.7 CASO PRÁTICO: CONTAS DE EXPLORAÇÃO

Considere as contas de exploração duma empresa no intervalo A7:S179:

Para facilitar a análise dos dados pretende-se criar:

Um outline (grupos) para cada trimestre:

Criar grupos para os vários níveis das contas de exploração;

Aplique filtros para visualizar apenas as contas com valores acima de 100 euros num dado mês;

Aplique formatações condicionais para identificar mais facilmente alguns valores.


68 68



a) O botão ao lado permite obter

Várias médias ao mesmo tempo

Uma só soma

A ordenação da tabela


b) Numa ordenação a ordem refere-se a

Um campo numérico

A um campo com datas

A um campo com texto


c) A função SUBTOTAL tem a seguinte sintaxe:

em que o argumento ref1 representa

Uma célula

Um intervalo de células

Um critério de filtragem

Um critério de ordenação

d) Numa tabela dinâmica para se obter o peso

deve-se mostrar o valor em

% do grande total

% do total da coluna

% do total da linha

% da diferença para o anterior


69 69

4. ELABORAÇÃO DE MAPAS DE SERVIÇO DA DÍVIDA

4.1. FUNÇÕES FINANCEIRAS

O Excel contempla um conjunto de funções, classificadas na categoria de funções financeiras, que podem ser aplicadas no reembolso de um empréstimo bancário por meio de prestações constantes de capital e juro, que é, hoje em dia, o método de reembolso mais comum em Portugal.

As principais funções financeiras relacionadas com capitalização e atualização de um capital ou com o valor atual ou acumulado de rendas financeiras têm argumentos comuns2:

Va é o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se va for omitido, será considerado 0. No caso dos empréstimos corresponde ao capital mutuado;

Vf é o valor futuro ou um saldo de caixa, que se deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (por exemplo, o valor futuro de determinado empréstimo é 0);

Nper é o número total de períodos (prestações) de amortização;

Pgto é o valor da prestação periódica e constante de capital e juro;

Tipo é o número 0 se o vencimento das prestações for no fim do período (prestações postecipadas) ou 1 se for no início do período (prestações antecipadas);

Período é o número de ordem de um período (prestação);

Taxa é a taxa de juro do período da prestação;

Taxa_matriz é uma matriz com as diferentes taxas de juro por período.

Estimativa é uma estimativa (previsão) para a taxa de juro;

As funções para o cálculo do valor acumulado e atual são:

2 Este ponto foi retirado de: Laureano, Raul M. S. e Santos, Luis Lopes dos (2011), “Fundamentos do Cálculo

Financeiro”, Edições Sílabo, Lisboa, ISBN 978-972-618-642-7


70

70

FV(taxa; nper; pgto; va; tipo)

[VF] – devolve o valor acumulado ou futuro de um capital ou de uma série de capitais constantes e periódicos com taxa de juro constante.

FVSCHEDULE(va; matriz_taxa)

[VFPLANO] – devolve o valor acumulado de um capital com diferentes taxas de juro que têm que dizer respeito a períodos consecutivos.

PV(taxa; nper; pgto; vf; tipo)

[VA] – devolve o valor atual de um capital ou de uma série de capitais constantes e periódicos com taxa de juro constante.


71

71

As principais funções financeiras diretamente relacionadas com reembolsos de empréstimos e, no geral, com rendas financeiras são:

PMT(taxa; nper; va; vf; tipo)

[PGTO] – devolve o valor da prestação periódica (constante de capital e juro) considerando a taxa de juro constante ao longo de todo o prazo.

PPMT(taxa; período; nper; va; vf; tipo)

[PPGTO] – devolve o reembolso de capital incluído numa determinada prestação.

IPMT(taxa; período; nper; va; vf; tipo)

[IPGTO] – devolve o juro incluído numa determinada prestação periódica.


72 72

ISPMT(taxa; período; nper; va)

[É.PGTO] – devolve o juro que um investimento rendeu durante um determinado período.

NPER(taxa; pgto; va; vf; tipo)

[NPER] – devolve o número de períodos (prestações) de reembolso, com prestação e taxa de juro constantes ao longo de toda a vida do empréstimo.

RATE(nper; pgto; va; vf; tipo; estimativa)

[TAXA] – devolve a taxa de juro reportada ao período da prestação.

CUMPRINC(taxa; nper; va; per_i; per_f; tipo)

[PGTOCAPACUM] – devolve o reembolso (acumulado) de capital efectuado entre um período inicial (per_i) e um período final (per_f) inclusive.


73

73

CUMIPMT (taxa; nper; va; per_i; per_f; tipo)

[PGTOJURACUM] – devolve o juro (acumulado) pago entre um período inicial (per_i) e um período final (per_f) inclusive.

As funções para conversão de taxas nominais em efetivas anuais e vice- -versa são:

EFFECT(taxa_nominal; núm_por_ano)

[EFECTIVA] – devolve a taxa anual de juros efetiva, dada a taxa nominal de juros (anual) e o número de períodos compostos por ano (frequência de capitalização).


74 74

NOMINAL(taxa_efectiva; núm_por_ano)

[NOMINAL] – devolve a taxa de juro nominal (anual) dada a taxa efetiva e o número de períodos compostos por ano (frequência de capitalização).

As funções que permitem avaliar investimentos (VAL), calcular taxas efetivas de rendibilidade (TIR e Yield, por exemplo) e taxas efetivas de custo (por exemplo, TAE e TAEG) têm argumentos em comum:

Fluxos_tesouraria é o intervalo de fluxos de tesouraria periódicos. Deverá haver sempre fluxos positivos, correspondentes aos recebimentos, e fluxos negativos, correspondentes aos pagamentos. A função interpreta os fluxos pela ordem em que são dados, sendo o primeiro o correspondente ao momento (período) zero, o segundo ao momento (período) um e assim sucessivamente. No caso da função XIRR os fluxos de tesouraria futuros são descontados tendo por base de cálculo o ano de 365 dias;

Estimativa é uma estimativa (previsão) para a taxa. Sendo omitido este argumento é considerado 0,1. Não sendo um elemento essencial, pelo que pode muitas vezes ser omitido, poderá, em algumas situações, ter que ser um valor introduzido com algum cuidado, de modo a se conseguir obter o verdadeiro valor da taxa interna de rendibilidade;

Tx_atualização é a taxa de juro a que são atualizados os fluxos de tesouraria do investimento;

Tx_financiamento é a taxa de juro a que foi financiado o investimento;

Tx_reinvestimento é a taxa de juro a que são reinvestidos os rendimentos obtidos;

Datas é o intervalo de datas de vencimento correspondentes aos fluxos de tesouraria não periódicos. Em vez das datas podem-se inserir os números de dias que faltam para os vencimentos de cada fluxo de tesouraria.


75 75

As funções para o cálculo do valor atual líquido de um investimento são:

NPV(tx_atualização; fluxos_tesouraria)

[VAL] – devolve o valor atual líquido de uma série de fluxos de tesouraria periódicos que ocorrem no fim do período (postecipados).

XNPV (fluxos_tesouraria; datas; estimativa)

[XVAL] – devolve o valor atual líquido de uma série de fluxos de tesouraria não periódicos que ocorrem no fim do período (postecipados).

As funções para o cálculo de taxas efetivas são:

IRR(fluxos_tesouraria; estimativa)

[TIR] – devolve a taxa interna de rendibilidade do período de uma série de fluxos de tesouraria periódicos.


76

76

MIRR(fluxos_tesouraria; tx_financiamento; tx_reinvestimento)

[MTIR] – devolve a taxa interna de rendibilidade do período de uma série de fluxos de tesouraria periódicos, com um investimento financiado a uma taxa e os rendimentos obtidos reinvestidos a uma outra taxa.

XIRR(fluxos_tesouraria; datas; estimativa)

[XTIR] – devolve a taxa interna de rendibilidade (anual) de uma série de fluxos de tesouraria não periódicos.

Exemplo 13 (Retirado do IASC Foundation: Training Material for the IFRS® for SMEs (version 2010-2): No primeiro dia do seu exercício económico uma entidade vendeu inventários no valor de 2.000.000 euros a crédito, a dois anos quando o preço de venda atual a pronto das mercadorias é de 1.652.893 euros. Calcule os réditos de juros a reconhecer neste exercício e no seguinte.

Após a definição das células de input e as de output torna-se fácil realizar os cálculos pretendidos:


77

77

Tratando-se de um financiamento é necessário calcular a taxa de juro efetiva anual por forma a calcular os juros a reconhecer em cada ano. Assumindo-se um financiamento em regime de juros compostos, a taxa efetiva anual pode ser obtida recorrendo à função IRR [TIR], sendo, no entanto, necessário definir um intervalo (J12:J14) com os fluxos de tesouraria nos momentos 0, 1 e 2:

Exemplo 14: Considere os seguintes inputs relativamente a um empréstimo:

Utilize as funções financeiras adequadas para calcular os valores oportunos.

O desenho do modelo de cálculo deve contemplar células de input (a uma cor) e células de output (a outra cor) e deve ser o mais flexível possível. Neste contexto, devem-se evitar introduzir valores constantes nas fórmulas. Estes devem ser introduzidos em células e nas fórmulas utilizam-se as referências dessas células.

Em modelos mais complexos pode ser importante proteger as células de output para evitar que utilizadores do modelo menos experientes apaguem ou alterem as fórmulas e, consequentemente, a utilidade do modelo perde-se.


78

78

4.1.1 Proteção de células

A proteção de células, folhas e livros, indicando uma senha de acesso ou não, pode ser uma ferramenta útil em termos de manter a confidencialidade dos dados ou a segurança dos modelos de cálculo desenhados.

O separador Review contempla as diferentes formas de proteção:

Por defeito todas as células estão protegidas (locked) e não escondidas. No entanto, estas propriedades só atuam quando é ativada a proteção da folha (ou do livro): Review | Protect Sheet.

Para não proteger apenas um conjunto de células os passos são:

1. Selecionar as células a não proteger (células de input G27:G34;F41:F43;F45;E47:F48;F51):

2. Com o botão direito do rato (no menu de contexto) selecionar Format Cells... Na janela Format Cells desmarcar a opção Locked (Protegida) e premir OK:


79

79

3. Review | Protect Sheet e manter apenas a opção Select unlocked cells que permite apenas selecionar as células não protegidas. Se oportuno podem-se selecionar outras opções da lista e definir uma password:

4. Premir OK. Experimentar selecionar e alterar células.

Para desativar a proteção da folha premir no separador Review o botão Unprotect Sheet:


80

80

4.2. CASO PRÁTICO: CALCULADORA FINANCEIRA

Utilize o Excel para efetuar os cálculos relacionados com financiamentos (valor

temporal do dinheiro - TVM) utilizando os conceitos de uma calculadora financeira:

4.3. CASO PRÁTICO: MAPA DE SERVIÇO DA DÍVIDA

Considere um financiamento bancário a ser reembolsado através de prestações

semestrais postecipadas constantes de capital e juro pelo prazo de 5 anos, a uma

taxa fixa. Os dados completos do empréstimo são apresentados na seguinte figura:

Utilize o Excel para: a) construir o quadro de reembolso completo; b) calcular os

totais, a TAE e a TAEG; e c) calcular os valores totais para cada ano civil do prazo

de reembolso do empréstimo.3

3 Note que este caso recorre a outras categorias de funções, pelo que pode ser aconselhável a sua resolução

ocorrer após a leitura de todo o manual.


81

81



a) A função para calcular o valor futuro tem a seguinte sintaxe: FV(taxa;nper;pgto;[va];[tipo])

O que representa o argumento tipo

O tipo de aplicação (obrigações, empréstimos,...)

O tipo de taxa de juro (efetiva ou nominal)

O momento do pagamento (início ou fim do período)

O tipo de fluxo (recebimento ou pagamento)

b) Qual o resultado da fórmula: =EFFECT(12%;2)

Erro

6,00%

12,36%

24,00%

c) Para calcular a TAEG de um crédito ao consumo pode utilizar a função

NPV [VAL]

IRR [TIR]

RATE [TAXA]

NOMINAL [NOMINAL]

d) Numa operação financeira com capitalizações trimestrais o argumento relativo à taxa de juro nas funções financeiras refere-se à taxa

Efetiva anual

Nominal (anual)

Trimestral

É indiferente


82 82

5. ELABORAÇÃO DE MAPAS COM INFORMAÇÃO CONTABILÍSTICA-FINANCEIRA

O Excel contempla um vasto conjunto de funções que permitem realizar cálculos de maior ou menor complexidade, consoante a função em causa. Estas funções estão agrupadas em categorias e que podem ser inseridas diretamente na célula ou

através do assistente (botão na barra de fórmulas: .

5.1 FUNÇÕES DE DATA

A utilização de datas numa folha de cálculo é bastante comum. As funções disponíveis no Excel permitem construir fórmulas que vão ao encontro da grande maioria das necessidades dos utilizadores desta ferramenta.

Para o Excel, uma data é um número. Esse número, que é sequencial, traduz o número de dias que decorreram desde o dia 1 de Janeiro de 1900. Assim, o número 1 corresponde ao dia 1 de Janeiro de 1900, o número 2 ao dia 2 de Janeiro de 1900 e assim sucessivamente. Ao tratar as datas como números, a criação de fórmulas sobre datas fica bastante simplificada.

As principais funções de data incluídas no Excel são as seguintes:

TODAY()

[HOJE] – devolve o número de série da data atual.

NOW()

[AGORA] – devolve o número de série da data e hora atuais. A parte decimal do número de série corresponde ao tempo.

YEAR(núm_série)

[ANO] – devolve o ano correspondente a uma data. O ano é devolvido como um número inteiro no intervalo 1900-9999.


83

83

MONTH(núm_série)

[MÊS] – devolve o mês de uma data representada por um número de série. O mês é devolvido como um número inteiro entre 1 (janeiro) e 12 (dezembro).

DAY(núm_série)

[DIA] – devolve o dia de uma data representada por um número de série. O dia é dado como um número inteiro que varia entre 1 e 31.

DATE(ano; mês; dia)

[DATA] – devolve o número de série sequencial que representa uma data específica.

DATEVALUE(texto_data)

[DATA.VALOR] – converte uma data armazenada como texto num número de série sequencial que representa essa data.

DATEDIF(data_inicial; data_final; unidade)

[DATADIF] – devolve o número de dias, meses ou anos entre duas datas. O argumento unidade pode ser “d” (devolve o número de dias), “m” (devolve o número de meses completos), “y” (devolve o número de anos completos), “ym” (devolve a diferença entre os meses na data_inicial e na data_final, sendo os dias e anos das datas ignorados), “md” (devolve a diferença entre os dias na data_inicial e na data_final, sendo os meses e anos das datas ignorados), e “yd” (devolve a diferença entre os dias da data_inicial e da data_final, sendo os anos das datas ignorados).

YEARFRAC(data_inicial; data_final; [base])

[FRACÇÃOANO] – devolve a fração do ano representado pelo número de dias entre duas datas (a data_inicial e a data_final). O argumento base pode ser 0 ou omitido (base de calendário Americana (NASD) 30/360), 1 (base Real/real), 2 (base Real/360), 3 (base Real/365) e 4 (Europeia 30/360).

WEEKDAY(núm_série;[tipo_devolvido]))

[DIA.SEMANA] – devolve o dia da semana que corresponde a uma data. Por predefinição, o dia é fornecido como um número inteiro, que se situa entre 1 (Domingo) e 7 (Sábado). No entanto, o argumento tipo_devolvido pode ser 1 ou omitido (sendo o resultado de 1 (Domingo) a 7 (Sábado), 2 (1 (Segunda-feira) a 7 (Domingo)), 3 (0 (Segunda-feira) a 6 (Domingo)), 11 (1 (Segunda-feira) a 7 (Domingo)), 12 (1 (Terça-feira) a 7 (Domingo)), 13 (1 (Quarta-feira) a 7 (Terça-feira)), 14 (1 (Quinta-feira) a 7 (Quarta-feira)), 15 (1 (Sexta-feira) a 7 (Quinta-feira)), 16 (1 (Sábado) a 7 (Sexta-feira)) ou 17 (1 (Domingo) a 7 (Sábado)).


84

84

WEEKNUM (número_série;[tipo_devolvido])

[NÚMSEMANA] – devolve o número da semana do ano de uma data específica. O argumento tipo_devolvido é opcional e identifica o dia em que começa a (primeira) semana.

EOMONTH(data_inicial; meses)

[FIMMÊS] – devolve o número de série do último dia do mês que é o número indicado de meses antes ou depois de data_inicial. Utilizar FIMMÊS para calcular as datas de vencimento que coincidem com o último dia do mês.

EDATE(data_inicial; meses)

[DATAM] – devolve um número de série que representa a data que é o número de meses indicado antes ou depois de uma data especificada (data_inicial).

WORKDAY(data_inicial; dias; [feriados])

[DIATRABALHO] – devolve um número de série que representa uma data que indica o número de dias de trabalho (úteis) que se situam antes ou depois de uma data (a data inicial). Os dias de trabalho não incluem os fins-de- -semana e quaisquer datas que sejam identificadas com o argumento feriados.

NETWORKDAYS(data_inicial; data_final; [feriados])

[DIATRABALHOTOTAL] – devolve o número de dias de trabalho (dias úteis) inteiros entre data_inicial e data_final. Os dias de trabalho excluem os fins de semana e quaisquer datas identificadas como feriados.

Exemplo 15: Considere que tem que elaborar o seguinte mapa:

Complete-o com fórmulas para que seja automaticamente atualizado.


85

85

O preenchimento do mapa requer a utilização das funções de data:

C51 =TODAY()

E55 =DATEDIF(C55;C$51;"Y") ou em alternativa E55 =INT(YEARFRAC(C55;C$51;1))

F55 =YEAR(TODAY())-YEAR(D55)

G55 =MONTH(TODAY())=MONTH(C55)

H55 =DATE(YEAR(C$51);MONTH(C55);DAY(C55))-C$51

Exemplo 16: Considere que tem que elaborar o seguinte mapa:

Complete-o com fórmulas para que seja automaticamente atualizado.

O preenchimento do mapa requer a utilização das funções de data:

F64 =WORKDAY(E64;D64;D$69:G$69)

G64 =WEEKDAY(F64;1) experimente formatar em “dddd”

H64 =F64-E64

5.2 FUNÇÕES DE CONTAGEM E SOMA CONDICIONADAS

As funções de contagem permitem contar o número de células que contenham números, cujo conteúdo satisfaça uma condição específica, que não estejam vazias ou que estejam em branco. Estas funções estão classificadas na categoria estatística. Por outro lado, as funções de soma e soma condicional encontram-se na categoria matemática e as suas correspondentes para o cálculo da média encontram-se também na categoria estatística:

COUNT(valor1; [valor2]; ...)

[CONTAR] – devolve o número de células que contêm números na lista de argumentos.


86

86

COUNTA(valor1; [valor2]; ...)

[CONTAR.VAL] – devolve o número de células que não estão vazias na lista de argumentos.

COUNTBLANK(valor1; [valor2]; ...)

[CONTAR.VAZIO] – devolve o número de células em branco num intervalo de células especificado.

COUNTIF(intervalo, critério)

[CONTAR.SE] – devolve o número de células de um intervalo que correspondem a um critério especificado. Por exemplo, pode-se contar todas as células iniciadas por uma determinada letra ou todas as células que contêm um número maior ou menor que um número especificado.

O argumento critério é introduzido na forma de um número, expressão, referência de célula ou texto (por exemplo, como 10, ">10", G3, “Raul"). O critério define quais as células que se pretende contar.

COUNTIFS(intervalo_critério1;critério1;[intervalo_critério2;critério2]…)

[CONTAR.SE.S] – devolve o número de vezes que todos os critérios, aplicados ao mesmo ou a diferentes intervalos, são satisfeitos.

SUM(núm1;[núm2];...])

[SOMA] – devolve a soma de todos os números especificados nos argumentos.

SUMIF(intervalo; critério; [intervalo_soma]))

[SOMA.SE] – devolve a soma dos valores das células de um intervalo que correspondam ao critério especificado.

SUMIFS(intervalo_soma;intervalo_critério1;critério1;[intervalo_critério2; critério2]...)

[SOMA.SE.S] – devolve a soma dos valores das células de um intervalo que correspondam a múltiplos critérios.

AVERAGE(núm1;[núm2];...])

[MÉDIA] – devolve a média aritmética de todos os números especificados nos argumentos.

AVERAGEIF(intervalo; critérios; [intervalo_média])

[MÉDIA.SE] – devolve a média aritmética de todas as células num intervalo que cumprem determinado critério.


87

87

AVERAGEIFS(intervalo_média;intervalo_critério1;critério1;[intervalo_ critério2; critério2];…)

[MÉDIA.SE.S] – devolve a média aritmética de todas as células que cumprem múltiplos critérios.

Note-se que para escrever um critério com um operador e em que o valor está numa célula usa-se a concatenação. Por exemplo, “>”&G3, sendo que G3 tem o valor 10. Assim, este critério é equivalente a “>10”, mas tem a vantagem de se poder alterar o valor mais facilmente, já que está visível na célula G3 e não dentro de uma fórmula. Se se pretendesse poder-se-ia também colocar o operador numa célula (F3) e, então, o critério seria F3&G3, que seria equivalente a “>10” se F3 e G3 tivessem, respetivamente, > e 10.

É de realçar que nos critérios podem ser utilizados os dois caracteres universais: ponto de interrogação (?) e o asterisco (*). O ? representa um qualquer carácter individual e o * uma qualquer sequência de caracteres (que pode ser nenhum carácter - conjunto vazio).

Exemplo 17: Considere que tem o seguinte mapa em que a data, ano e mês de pagamento são obtidos com fórmulas:

Com base nos dados pretende-se obter informação resumida em dois quadros, sendo que no segundo indica-se no intervalo M30:M33 a informação pretendida:


88

88

Complete-os com fórmulas para que seja automaticamente atualizado.

O preenchimento da data, ano e mês de pagamento requer a utilização das funções de data:

H22 =E22+H$19 I22 =YEAR(H22) J22 =MONTH(H22)

O preenchimento do quadro resumo requer a utilização das funções de contagem e soma condicionadas com apenas um critério:

N24 =COUNTIF(D$22:D$37;M24)

O24 =SUMIF(D$22:D$37;M24;F$22:F$37)

P24 =AVERAGEIF(D$22:D$37;M24;F$22:F$37)

N27 =COUNTA(D22:D37)

O27 =SUM(F22:F37)

Já a segunda parte do quadro obriga à utilização de funções com múltiplos critérios:

N30 =COUNTIFS(I22:I37;M30;J22:J37;M31;D22:D37;M32;B22:B37;M33)

O30 =SUMIFS(F22:F37;I22:I37;M30;J22:J37;M31;D22:D37;M32;B22:B37;M33)

P30 =AVERAGEIFS(F22:F37;I22:I37;M30;J22:J37;M31;D22:D37;M32;B22:B37;M33)

5.3 FUNÇÕES LÓGICAS

As funções lógicas permitem realizar cálculos condicionais e assinalar ou validar determinadas situações, entre elas erros nos modelos de cálculo. As principais funções são:


89

89

IF(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso])

[SE] – devolve um valor se uma condição especificada devolver o valor VERDADEIRO e outro valor se essa condição devolver o valor FALSO.

IFERROR(expressão; valor_se_erro)

[SE.ERRO] – devolve um valor definido pelo utilizador se ocorrer um erro na fórmula (expressão), e devolve o resultado da fórmula se não ocorrer nenhum erro.

AND(lógico1; [lógico2]; ...)

[E] – devolve VERDADEIRO se todos os argumentos devolverem VERDADEIRO; devolve FALSO se um ou mais argumentos devolverem FALSO.

OR(lógico1; [lógico2]; ...)

[OU] – devolve VERDADEIRO se qualquer um dos argumentos for VERDADEIRO; devolve FALSO se todos os argumentos forem FALSO.

NOT(lógico)

[NÃO] – inverte o valor lógico do argumento. Utilizar esta função quando se deseja ter certeza de que um valor não é igual a outro valor determinado.

Exemplo 18: Considere que tem que reportar ao gestor de projetos o seguinte mapa:

Atribua as despesas aos respetivos projetos e preencha as observações com um "X" de acordo com as notas ao mapa.

O preenchimento do mapa requer a utilização das funções lógicas:


90

90

F16 =IF($E16=F$15;$D16;" ") depois copiar para o intervalo F16:I24

J16 =IF(E16="A";"X";" ")

K16 =IF(AND(E16="A";D16>=50000);"X";" ")

L16 =IF(YEAR(C16)=2013;"X";" ")

M16 =IF(OR(D16<1000;D16>100000);"X";" ")

D25 =SUM(D16:D24)

5.4 FUNÇÕES DE PROCURA E REFERÊNCIA

A procura de valores numa tabela pode ser uma tarefa complicada, se o número de registos for muito elevado. Para facilitar essa pesquisa, o Excel dispõe de um conjunto de funções que são bastante úteis na introdução de fórmulas de pesquisa. Entre elas destacam-se:

VLOOKUP(valor_procurar;tabela;núm_coluna;[procura_intervalo])

[PROCV] – procura um valor específico na primeira coluna de uma tabela e, em seguida, devolve um valor de qualquer célula na mesma linha da tabela. A procura pode ser exata (quando não encontra devolve #N/A [#N/D]) ou por intervalos (neste caso a primeira coluna da tabela deve estar ordenada por ordem crescente ou alfabética).

HLOOKUP(valor_procurar;tabela;núm_linha;[procura_intervalo])

[PROCH] – procura um valor específico na linha superior de uma tabela e devolve um valor na mesma coluna de uma linha especificada na tabela.

MATCH(valor_procurar; matriz_procura; [tipo_correspondência])

[CORRESP] – devolve a posição relativa de um item num intervalo que coincida com o valor especificado. Para uma procura exata indicar no terceiro argumento o valor zero (0).

INDEX(matriz; núm_linha; [núm_coluna])

[ÍNDICE] – devolve um valor ou uma referência do valor, numa tabela ou intervalo.

CHOOSE(núm_índice; valor1; [valor2]; ...)

[SELECCIONAR] – devolve um valor específico de uma lista de valores (máximo 29) introduzidos como argumentos.


91

91

OFFSET(referência; linhas; colunas; [altura]; [largura])

[DESLOCAMENTO] – devolve a referência a um intervalo que se encontra a um certo número de linhas e colunas de uma célula ou intervalo de células.

Exemplo 19: Considere o seguinte mapa de vendas:

Preencha os campos em falta de acordo com as notas ao mapa. As tabelas auxiliares são:

O preenchimento do mapa requer a utilização da função de procura VLOOKUP [PROCV] com uma procura exata, para o desconto e margem, e por intervalos, para o prazo de pagamento (PMR).

Por exemplo, no caso desconto, o VLOOKUP vai procurar o cliente (em B5), na primeira coluna da tabela (K5:M9), que não necessita de estar ordenada por ordem crescente por ser uma procura exata (indicado com FALSE no último argumento), e quando encontrar devolve o valor da terceira coluna da tabela (se não encontrar devolve #N/A!).

F5 =VLOOKUP(B5;K$6:M$9;3;FALSE)

G5 =VLOOKUP(C5;K$13:L$15;2;FALSE)


92

92

No caso do prazo de vencimento este obriga a uma procura por intervalos. Neste caso, a primeira coluna da tabela onde o VLOOKUP vai procurar tem o limite inferior de cada escalão e deverá estar por ordem crescente. Assim, a Tabela 3 teve que ser adaptada, utilizando-se a coluna M para a procura:

H5 =VLOOKUP(D5;$M$13:$O$16;3;TRUE)

Por fim, o valor a faturar corresponde a:

I5 =(D5*E5)*(1+G5)*(1-F5)

Exemplo 20: Movimentos de caixa – calcule o movimento de caixa que corresponde a um dia de semana indicado numa célula (D61) e ao número da caixa indicado noutra célula (D62):

Neste exemplo é necessário efetuar uma procura em coluna (dia da semana) e, ao mesmo tempo, uma procura em linha (caixa). Assim, recorre-se à função INDEX [ÍNDICE] que dada uma matriz devolve um valor que fica na interceção de uma linha com uma coluna. Para saber qual a linha e a coluna da matriz correspondentes ao dia da semana e caixa indicados recorre-se à função MATCH [CORRESP]:

F62 =INDEX(C35:F41;MATCH(D61;B35:B41;0);MATCH(D62;C34:F34;0))

Verifique que o primeiro MATCH devolve 4 que corresponde à linha 4 do intervalo C35:F41 e que o segundo MATCH devolve 2 que corresponde à coluna 2 do mesmo intervalo. Em ambos utilizou-se uma procura exata.


93

93

5.5 FUNÇÕES DE TEXTO

Apesar do Excel ser basicamente destinado ao cálculo, surgem ocasiões em que é necessário utilizar e tratar texto. É possível, embora muito desaconselhável, inserir numa célula cerca de 32 mil caracteres. Algumas das funções de texto mais úteis são:

TRIM(texto; num_caracteres)

[COMPACTAR] – elimina todos os espaços anteriores e posteriores ao texto.

LEN(texto)

[NÚM.CARACT] – devolve o número de caracteres de uma cadeia de texto.

LEFT(texto; [núm_caract])

[ESQUERDA] – devolve o primeiro carácter ou primeiros caracteres numa cadeia de texto, com base no número de caracteres especificado.

RIGHT(texto; [núm_caract])

[DIREITA] – devolve o último carácter ou últimos caracteres numa cadeia de texto, baseado no número de caracteres especificado.

MID(texto; núm_inicial; núm_caract)

[SEG.TEXTO] – devolve um número específico de caracteres da cadeia de texto, começando na posição especificada, com base no número de caracteres especificado.

FIND(texto_a_localizar; no_texto; [núm_inicial])

[LOCALIZAR] – localiza uma cadeia de texto dentro de uma segunda cadeia de texto e devolve o número da posição inicial da primeira cadeia de texto a partir do primeiro carácter da segunda cadeia de texto. Esta função distingue minúsculas de maiúsculas.

SEARCH (texto_a_localizar; no_texto; [núm_inicial])

[PROCURAR] – localiza uma cadeia de texto dentro de uma segunda cadeia de texto e devolve o número da posição de início da primeira cadeia de texto do primeiro caráter da segunda cadeia de texto. Esta função não distingue minúsculas de maiúsculas.


94

94

SUBSTITUTE(texto; texto_antigo; novo_texto; [núm_da_ocorrência])

[SUBST] – coloca novo_texto no lugar de texto_antigo numa cadeia de texto.

REPLACE(texto_antigo; núm_inicial; núm_caract; novo_texto)

[SUBSTITUIR] – substitui parte de uma cadeia de texto, com base no número de caracteres especificados, por uma cadeia de texto diferente.

CONCATENATE(texto1; [texto2]; ...)

[CONCATENAR] – junta até 255 cadeias de texto numa única cadeia de texto. Os itens unidos podem ser texto, número, referências de células ou uma combinação desses itens. Equivalente à utilização do operador &.

EXACT(texto1; texto2)

[EXACTO] – compara duas cadeias de texto, devolvendo VERDADEIRO, se forem exatamente iguais, ou FALSO, se não o forem. Esta função distingue entre maiúsculas e minúsculas.

UPPER(texto)

[MAIÚSCULAS] – converte o texto para maiúsculas.

LOWER(texto)

[MINÚSCULAS] – converte o texto para minúsculas.

PROPER(texto)

[INICIAL.MAIÚSCULA] – coloca a primeira letra do texto em maiúscula e todas as outras letras do texto depois de qualquer carácter diferente de uma letra, convertendo todas as outras letras para minúsculas.

VALUE(texto)

[VALOR] – converte uma cadeia de texto que representa um número num número.

TEXT(valor; formato_texto)

[TEXTO] – converte um valor numérico em texto e permite especificar o formato de apresentação utilizando cadeias de formatação especiais. É útil para apresentar números num formato mais legível ou combinar números com texto ou símbolos. É imprescindível consultar a ajuda desta função para conhecer os códigos da formatação que se pretende.


95

95

Exemplo 21: Lista de contactos - considere que tem que um mapa com os contactos dos clientes e que pretende obter alguma informação complementar:

Obtenha o nome e o apelido do contacto no cliente, a sua rede de telefone e atribua-lhe um código de acordo com a nota ao mapa.

O preenchimento do mapa requer a utilização das funções de texto:

E47 =LEFT(C47;SEARCH(" ";C47)) note-se que a função SEARCH [PROCURAR] procura o espaço (1º espaço no texto) e devolve a sua posição na cadeia de caracteres.

F47 =RIGHT(C47;LEN(C47)-IFERROR(SEARCH(" ";C47;SEARCH(" ";C47)+1);SEARCH( " ";C47;SEARCH(" ";C47))))

uma vez que o contacto pode ter três nomes, isto é, pode ter dois espaços, tem que se saber se tem um ou dois espaços e qual a posição do último espaço para extrair os caracteres à sua direita. Então, sabe-se que a função SEARCH devolve um erro quando não encontra o texto pretendido (“ “) a partir da posição do primeiro espaço encontrado. Assim, a função IFERROR [SE.ERRO] tem um papel fundamental na extração do apelido, pois permite selecionar os caracteres à direita do segundo espaço ou do primeiro espaço.

G47 =LEFT(D47;2)

H47 =UPPER(LEFT(B47;2)&"-"&LEFT(C47;2))


96

96

5.6 FUNÇÕES DE INFORMAÇÃO

O Excel contempla um conjunto de funções que se tornam muito úteis na compreensão de erros que surgem nos modelos de cálculo criados e, também, na validação desses mesmos modelos. Algumas dessas funções são:

ISNUMBER(valor)

[É.NUM] – devolve o valor lógico verdadeiro se o argumento for um número.

ISTEXT(valor)

[É.TEXTO] – devolve o valor lógico verdadeiro se o argumento for um texto.

ISNONTEXT(valor)

[É.NÃO.TEXTO] – devolve o valor lógico verdadeiro se o argumento não for um texto (um espaço é considerado texto!).

ISLOGICAL(valor)

[É.LÓGICO] – devolve o valor lógico verdadeiro se o argumento for um valor lógico (TRUE ou FALSE).

ISBLANK(valor)

[É.CÉL.VAZIA] – devolve o valor lógico verdadeiro se o argumento corresponder a uma célula vazia.

ISERR(valor)

[É.ERRO] – devolve o valor lógico verdadeiro se o argumento é qualquer valor de erro exceto #N/D (#VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!).

ISERROR(valor)

[É.ERROS] – devolve o valor lógico verdadeiro se o argumento é qualquer valor de erro (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!).

ISNA(valor)

[É.NÃO.DISP] – devolve o valor lógico verdadeiro se o argumento tem o valor de erro #N/D.


97

97

ISREF(valor)

[É.REF] – devolve o valor lógico verdadeiro se o argumento for uma referência.

5.6.1 Tipos de erros

A utilização de fórmulas, com a inclusão ou não das diversas funções disponíveis, por vezes leva a resultados estranhos ou mesmo de erro. Alguns dos erros são efetivamente erros que se devem à indicação de operadores, operandos ou argumentos das funções errados, mas outros não são erros, mas sim indicações úteis para o utilizador. Assim, torna-se imprescindível compreender o significado dos diversos erros que surgem no desenho de modelos de cálculo:

Português Inglês Exemplo e descrição do erro

#### #### 19345678,0123

O valor numérico (número ou data) introduzido na célula é demasiado extenso para poder ser visualizado. Pode ainda ocorrer quando se subtrai uma data menor de uma maior. Dever-se-á aumentar a largura da coluna.

#DIV/0! #DIV/0! =2/0

Está-se a dividir um número por zero. Pode não ser erro!

#NOME? #NAME? =somar(2;3)

Está-se a inserir um nome que não está definido. Normalmente acontece quando um nome de função ou de célula está errado ou não se enquadrou um texto com aspas numa fórmula.

#VALOR! #VALUE! =2+"a"

Numa fórmula matemática um dos operandos é texto, ou então poder-se-á estar a indicar um intervalo de células quando é esperado um único valor.

#REF! #REF! =VLOOKUP(C11;B7:C14;3;FALSE)

Apagaram-se células que fazem parte de uma fórmula, ou está-se a indicar numa função uma referência que não existe (por exemplo, na função VLOOKUP a tabela só tem duas colunas e indica-se a coluna 3).

#N/D #N/A =VLOOKUP(C10;B7:C14;2;FALSE)

O valor que se procura não existe. Esta mensagem está normalmente associada a funções de procura e referência (por exemplo, na função VLOOKUP o conteúdo da célula C10 não é igual a nenhum no intervalo B7:B14).


98

98

Português Inglês Exemplo e descrição do erro

#NUM! #NUM! =SQRT(-2)

Está-se a utilizar um argumento que não é válido para a função, ou a função que se está a usar não encontrou nenhum resultado. Por exemplo, calcular a raiz quadrada de um número negativo ou no cálculo da função TIR.

#NULL! #NULL! =SUM(A1 D1)

Está-se a utilizar um operador de intervalo ou uma referência de células incorretas.

5.7 FUNÇÕES FINANCEIRAS RELACIONADAS COM O CÁLCULO DE DEPRECIAÇÕES E AMORTIZAÇÕES

O Excel contempla um conjunto de funções que se tornam úteis no cálculo de depreciações e amortizações e que se incluem na categoria de funções financeiras. Duas dessas funções são4:

SLN(custo; recuperação; vida útil)

[AMORT] – devolve a depreciação em linha reta de um ativo durante um período. Custo é o custo inicial do ativo; Recuperação é o valor no final da depreciação (às vezes chamado de valor de recuperação ou residual do ativo); e Vida_útil é o número de períodos nos quais o ativo se deprecia.

Exemplo: suponha que comprou um camião por 30.000 Euros, que possui uma vida útil de 10 anos e um valor de recuperação de 7.500 Euros.

A segurança de depreciação para cada ano é: SLN(30000;7500;10), ou seja, 2.250 Euros.

Se não tivesse valor de recuperação seria SLN(30000;0;10), ou seja, 3.000 Euros.

4 Outras funções são: SYD [AMORTTD]; DDB [BDD]; e DB [BD].


99

99

VDB(custo; val_residual; vida_útil; início_período; final_período; [fator]; [sem_mudança])

[BDV] - devolve a depreciação de um ativo para o período que se especificar, incluindo períodos parciais, usando o método de balanço decrescente duplo ou algum outro método especificado.

VDB é o balanço decrescente variável. Custo é o custo inicial do ativo; Val_residual é o valor no fim da depreciação; Vida_útil é o número de períodos em que o ativo se deprecia; Início_período é o período inicial para o qual se deseja calcular a depreciação (Início_período tem de usar as mesmas unidades que vida útil); Final_período é o período final para o qual se deseja calcular a depreciação (Final_período tem de usar as mesmas unidades que vida útil); Fator é a taxa em que o balanço decresce (se fator for omitido, será considerado 2, i.e., o método balanço decrescente duplo); e Sem_mudança é o valor lógico que especifica se deve mudar para depreciação de linha reta quando a depreciação for maior do que o cálculo do balanço de declínio (se sem_mudança for VERDADEIRO, o Excel não muda para depreciação de linha reta mesmo quando a depreciação for maior do que o cálculo do balanço declínio; se sem_mudança for FALSO ou omitido, o Excel muda para depreciação linear quando a depreciação é maior do que o cálculo do balanço de declínio).

Nota: todos os argumentos exceto sem_mudança têm de ser números positivos.

Exemplo: suponha que comprou um camião por 30.000 Euros e que possui uma vida útil de 10 anos. Assim, por exemplo, tem-se:

VDB(30000; 0; 10; 1; 8; 1; FALSE) é igual a 21,000 Euros, a depreciação acumulada nos sete primeiros anos (com fator 1).

VDB(30000; 0; 10; 8; 9; 1; FALSE) é igual a 3,000 Euros, a depreciação num ano (com fator 1).


100 100

Exemplo 22: Mapa de amortizações – considere que numa análise de projetos de investimento tem que elaborar o mapa que permite calcular as depreciações e amortizações em cada ano de vida útil do projeto, podendo no ano 1 considerar apenas uma fração do ano (duodécimos) a indicar na célula E3:

Elabore em Excel o mapa considerando o método das quotas constantes.

Na elaboração do mapa, embora possa recorrer a funções relacionadas com depreciações e amortizações do Excel, as funções de utilização mais geral permitem obter os resultados pretendidos:

Amortizações ano 1 (na célula E3 indica-se a percentagem que é considerada no primeiro ano – 100% ou outra taxa se forem utilizados duodécimos)

E4 =IF(COUNT($E$2:E$2)<$D4;ROUND($B4/$D4;2);IF(COUNT($E$2:E$2)=$D4;$B4-ROUND($B4/$D4;2)*($D4-1);IF(COUNT($E$2:E$2)=$D4+1;$B4-SUM($D4:D4;-$D4);0)))*E$3

Amortizações ano 2

F4 =IF(COUNT($E$2:F$2)<$D4;ROUND($B4/$D4;2);IF(COUNT($E$2:F$2)=$D4; $B4-ROUND($B4/$D4;2)*($D4-1);IF(COUNT($E$2:F$2)=$D4+1;$B4-SUM($D4:E4;-$D4);0)))

Valor líquido contabilístico (VLC)

L4 =B4-SUM(E4:K4)


101 101

Totais

B14 =SUM(B4:B12)

Poderia ser útil recorrer, por exemplo, às funções de informação associadas à função IF [SE] para apenas fazer os cálculos quando as células de input estão preenchidas. Por exemplo, para o valor líquido contabilístico (VLC) este só vai ser calculado quando o valor de aquisição e a vida útil estiverem introduzidos, caso contrário será inserido um espaço (“ ”):

L4 =IF(OR(ISBLANK(B4);ISBLANK(D4));" ";B4-SUM(E4:K4))

Com a função financeira também se poderia calcular o valor da depreciação do exercício. Por exemplo, com a função DDB:

E4 =DDB(B4;0;D4;1;1)

5.8. CASO PRÁTICO: MAPA DE DEPRECIAÇÕES E REINTEGRAÇÕES

Considere o seguinte mapa que se destina à determinação dos limites legais e controlo das depreciações de ativos fixos tangíveis e de propriedades de investimento desde que mensuradas ao modelo do custo, e das amortizações de ativos intangíveis:

Elabore em Excel o mapa oficial de depreciações e amortizações de forma a simplificar e automatizar os cálculos das amortizações, pelo método das quotas constantes.


102

102


Assinale apenas a opção de resposta que considera mais adequada, atendendo à figura:

a) Qual a fórmula que permite calcular a percentagem das vendas da Ana (célula E4) no total de vendas (em valor)

=SUMIF(A3:A7;E4;C3:C7)/2

=SUM(C5:C6)/SUM(C3:C7)

=SUMIF(A3:A7;E4;C3:C7)/COUNT(C3:C7)

=SUMIF(A3:A7;E4;C3:C7)/SUM(C3:C7)

b) Qual a fórmula a inserir em D3 para colocar um asterisco (*) nas vendas do Borges superiores à média de todas as vendas

=IF(AND(A3="Borges";C3>AVERAGE(C3:C7));"*";"")

=IF(AND(A3="Borges";C3>AVERAGE(C$3:C$7));"*";"")

=IF(OR(A3="Borges";C3>AVERAGE(C$3:C$7));"*";"")

=IF(A3="Borges" Or C3>AVERAGE (C$3:C$7);"*";"")

c) Qual a fórmula que permite obter o nome do vendedor que vendeu mais

=VLOOKUP(MAX(C3:C7);A3:C7;-3;0)

=VLOOKUP(MAX(C3:C7);A3:C7;-3)

=INDEX(A3:C7;MATCH(MAX(C3:C7);C3:C7;0);1)

=INDEX(MATCH(MAX(C3:C7);C3:C7;0);1)

d) Qual o resultado da fórmula =ISNUMBER(SUM(C3:C7)&” Euros”)

TRUE

FALSE

968812

Um erro


103 103

6. UTILIZAÇÃO DO EXCEL EM TAREFAS DE CONFERÊNCIA

O Excel pode ser muito útil na realização de tarefas de conferência dos procedimentos realizados, isto é, na validação a posteriori desses procedimentos. No entanto, algumas regras podem ser definidas para validar a introdução dos dados, evitando-se, assim, algumas tarefas de conferência. Para a definição de regras o Excel contempla, essencialmente, duas funcionalidades, a validação de dados e a formatação condicional.

6.1 VALIDAÇÃO DE DADOS

Para criar uma regra de validação de entrada de dados devem seguir-se os seguintes passos:

1. Selecionar o intervalo em que se pretende associar uma regra;

2. Separador Data | Data Validation | Data Validation…:

3. Por defeito pode introduzir-se um qualquer valor na célula (Any value no separador Settings). Mas é possível criar restrições de inserção de dados de acordo com o tipo de dado a introduzir. É também possível selecionar Ignore blank para permitir o não preenchimento da célula, podendo esta ficar vazia:


104

104

Note-se que quando se altera uma regra de validação pode-se aplicar essa alteração a todas as células que tinham a regra alterad, selecionando a

opção:

Os critérios de validação possíveis são:

Whole number e Decimal permitem restringir os números que se podem inserir sendo, no primeiro caso, apenas números inteiros e, no segundo, números reais (com decimais):

List permite indicar uma lista com os valores possíveis de serem introduzidos. Por exemplo, pode definir-se uma lista com os clientes, produtos ou prazos de pagamento. Em Source indica-se o intervalo onde se encontram os valores possíveis e seleciona-se a opção In-cell dropdown quando se pretende que apareça a lista junto à célula:


105

105

Date e Time permitem criar restrições relacionadas com data e com horas:

Text length permite indicar o número de caracteres que se podem inserir na célula:

Custom permite definir uma regra baseada numa fórmula, que deverá devolver um valor logico, sendo que TRUE [VERDADEIRO] significa que é válida a inserção:


106

106

4. Depois de definida a regra no separador Settings pode definir-se uma mensagem, no separador Input Message, para ajudar o utilizador na introdução dos dados, relembrando qual a regra de validação:

5. Por fim, é também possível definir-se uma mensagem, no separador Error Alert, para quando o utilizador inserir um valor não válido:

6. Depois de todas as definições premir o botão OK para ativar a regra no intervalo selecionado. Note-se que para apagar a regra tem-se disponível o botão Clear All.

Quando a regra de validação é aplicada à posteriori é possível assinalar com um círculo vermelho as células que não respeitam a regra definida (Circle Invalid Data). Para remover os círculos seleciona-se Clear Validation Circles:


107

107

Exemplo 23: Considere que tem o seguinte mapa (no intervalo B8:H24) e que pretende criar regras de validação para: i) Documento que só pode ser N DÉBITO, N CRÉDITO OU N FATURA; ii) Data doc. que tem que ser uma data do ano corrente; iii) Data Pagamento que tem que ser uma data posterior ou igual à Data doc.; iv) Ref. Doc. que tem que ter 10 caracteres; e v) Valor que tem que ser negativo no caso de nota de crédito e positivo nos outros dois casos:

Defina as cinco regras.

Na funcionalidade Data | Data Validation | Data Validation… definem-se as regras pretendidas para:

Documento:

Inserir no intervalo K17:K19 os três documentos existentes

Selecionar o intervalo D9:D24 e definir a regra com lista (List):


108

108

O resultado é:

Se inserir um documento não válido aparece a janela:


109

109

Data doc. - selecionar o intervalo E9:E24 e definir a regra com data (Date):

como as datas já existentes são anteriores a 2013 a opção de assinalar os dados inválidos resulta em:

Em alternativa poder-se-ia definir a regra com base numa fórmula, tendo a vantagem de ser válida qualquer que seja o ano corrente, e não só 2013:


110

110

Data Pagamento - selecionar o intervalo H9:H24 e definir a regra com fórmula (Custom):

Ref. Doc. - selecionar o intervalo G9:G24 e definir a regra com texto (Text Length):

Valor - selecionar o intervalo F9:F24 e definir a regra com fórmula (Custom):


111

111

A fórmula completa é:

=IF(AND(D9="N CRÉDITO";F9<0);TRUE;IF(AND(OR(D9="N DÉBITO";D9="N FATURA");F9>0);TRUE;FALSE))

6.2 FORMATAÇÃO CONDICIONAL BASEADA EM FÓRMULAS

No ponto 3.4 já se analisaram algumas formatações condicionais que permitem uma melhor análise aos dados. Agora, conhecendo-se as diversas funções é possível definir formatações condicionadas baseadas em fórmulas, que quando devolverem o valor lógico TRUE [VERDADEIRO] ativam a formatação definida:

Home | Conditional Formatting | New rule…


112

112

Exemplo 24: Considere que tem o seguinte mapa e que pretende criar formatações condicionadas para: i) Documento que só pode ser N DÉBITO, N CRÉDITO OU N FATURA; ii) Data doc. que tem que ser uma data igual ou anterior à data atual; iii) Data Pagamento que tem que ser uma data posterior ou igual à Data doc., mas no máximo seis meses após a Data doc.; iv) Ref. Doc. que tem que ter 10 caracteres; v) Valor que tem que ser negativo no caso de nota de crédito e positivo nos outros dois casos; e vi) assinalar no intervalo I30:I45 as células em que o preenchimento do movimento esta incompleto:

Defina as seis regras.

Na funcionalidade Home | Conditional Formatting | New rule… definem-se as fórmulas em que se baseia a formatação e a formatação pretendida:

Documento - selecionar o intervalo D30:D45 e definir a fórmula, não esquecendo de retirar os “$” para tornar as referências relativas:

=NOT(OR(D30="N DÉBITO";D30="N CRÉDITO";D30="N FATURA"))


113

113

Data doc. - selecionar o intervalo E30:E45 e definir a fórmula =E30>TODAY(), não esquecendo de retirar o “$”, para tornar a referência relativa;

Data Pagamento - selecionar o intervalo H30:H45 e definir a fórmula =OR(H30<E30;H30>EDATE(E30;6)), não esquecendo de retirar os “$”, para tornar as referências relativas;

Ref. Doc. - selecionar o intervalo G30:G45 e definir a fórmula =LEN(G30)<>10, não esquecendo de retirar o “$”, para tornar a referência relativa;

Valor - selecionar o intervalo F30:F45 e definir a fórmula

=NOT(OR(AND(D30="N CRÉDITO";F30<0);AND(D30="N DÉBITO";F30>0); AND(D30="N FATURA";F30>0))), não esquecendo de retirar os “$”, para tornar as referências relativas;

Preenchimento incompleto de registo - selecionar o intervalo I30:I45 e definir a fórmula =COUNTA(B30:H30)<>7, não esquecendo de retirar os “$”, para tornar as referências relativas:

O resultado é:


114

114

6.3 CASO PRÁTICO: NUMERAÇÃO SEQUÊNCIAL DE DOCUMENTOS

Um dos trabalhos dos contabilistas e dos auditores prende-se com a verificação da não existência de falhas nos lançamentos, nomeadamente ao nível das faturas emitidas. Desta forma, identifique falhas na numeração das faturas emitidas num extrato da conta 71. As falhas podem ser de dois tipos: números repetidos (duplicados) e numeração não sequencial.

Considere os seguintes extratos para realizar o procedimento:

Registo no Excel (B5:M23):


115 115

Extrato Primavera (A1:W163):

Para atingir o objetivo pode recorrer à formatação condicional baseada em fórmulas ou à validação de dados.

Com formatação e no caso do Mapa de Vendas (Registo Excel), com o intervalo B6:M23 selecionado, tem-se por exemplo:


116

116

Note-se que a fórmula =$D6<>MAX($D$5:$D5)+1 permite identificar os duplicados e as falhas na numeração (saltos de numeração), mas só resulta se a tabela estiver ordenada pelo número do documento. Não esquecer de colocar os $ para que resulte!

No caso do Extrato Primavera, tendo selecionado o intervalo X7:X163, pode experimentar a seguinte fórmula para identificar números (no intervalo V7:V163) duplicados:

=AND(COUNTIF(V$7:V$163;V7)<>1;NOT(ISBLANK(V7)))

Veja a diferença para a fórmula:

=AND(COUNTIFS(U$7:U$163;U7;V$7:V$163;V7)<>1;NOT(ISBLANK(V7)))

Qual a mais adequada?

6.3.1 Remover dados duplicados

No caso de serem identificados dados duplicados ou, mais usual, registos duplicados estes podem ser removidos de forma automática com a funcionalidade Remove Duplicates [Remover Duplicados]:

No exmplo do Mapa de Vendas identificam-se duas faturas com o mesmo número, mas os registos são diferentes (i.e., nem todos os dados das duas faturas com igual número são iguais, pelo que se deve ter algum cuidado antes de remover duplicados. Os passos a seguir são:

1. Selecionar o intervalo com os registos (B6:M23)

2. Data | Remove Duplicates


117

117

3. Se o objetivo for eliminar os registos duplicados, isto é, quando todos os dados de um registo são iguais aos dados de pelo menos outro registo selecionam-se todos os campos e de seguida OK:

4. Se o objetivo é eliminar os duplicados, por exemplo, de um só campo (Documento), selecionam-se apenas os campos onde se pretende identificar duplicados (neste caso só o campo Documento) e OK:

E o resultado é que um registo foi apagado (o segundo com o número 56).


118

118

6.4 CASO PRÁTICO: REGULARIDADE DE MOVIMENTOS

Uma das tarefas de verificação é avaliar a regularidade dos movimentos de algumas das contas, como, por exemplo, as de Fornecimentos e Serviços Externos. Considere o extrato de FSE no intervalo A3:I710 e recorrendo a tabelas dinâmica avalie a evolução ao longo dos meses do ano:

A avaliação fica facilitada ao recorrer-se a representações gráficas que para cada conta pode ser semelhante a:

- €

50 €

100 €

150 €

200 €

250 €

300 €

350 €

400 €

Jan Fev Mar Abr Mai Jun Jul Ago Set Out Nov Dez

Valor da conta: 6242210

-20.000 €

-15.000 €

-10.000 €

-5.000 €

- €

5.000 €

10.000 €

15.000 €

Jan Fev Mar Abr Mai Jun Jul Ago Set Out Nov Dez

Valor da conta: 6221110

Note-se que agrupar as datas por mês facilita muito a tarefa:


119

119

A elaboração de uma tabela a partir da tabela dinâmica em que aparecem os doze meses do ano torna a leitura do gráfico mais facilitada, sendo este elaborado a partir da tabela no intervalo Q2:R14 (e não da tabela dinâmica que só apresenta dois meses):

Note-se que a tabela auxiliar vai buscar os valores à tabela dinâmica recorrendo à função VLOOKUP [PROCV] em conjunto com a função IFERROR [É.ERRO]! Também o número da conta é obtido, com a concatenação, na tabela dinâmica!

6.5 CASO PRÁTICO: ELABORAÇÃO DE MAPA DE ANTIGUIDADE DAS DÍVIDAS

No final do ano elabora-se o mapa de antiguidade dos saldos de clientes com referência à data indicada em G45, tendo por base o extrato das contas de clientes:


120

120

O mapa de antiguidade tem a seguinte estrutura:

Preencha os campos referentes à antiguidade de saldos de acordo com os escalões de antiguidade indicados em D3:H5. Para facilitar, no extrato pode criar uma coluna com o número de dias em atraso, quando a data de referência é posterior à data de pagamento, ou com “por vencer”, quando a data de pagamento é posterior à data de referência.

Para calcular no intervalo K48:K63 o número de dias em atraso utiliza-se a fórmula em K48 =IF(G$45-J48<=0;"Por vencer";G$45-J48).

Para inserir os clientes, sem repetição, no campo Nome do Cliente do Mapa de Antiguidade pode ser utilizada, por exemplo, a funcionalidade filtros avançados.

Na célula D4 pode inserir-se a fórmula copiável para os outros escalões e clientes:

D4 =SUMIFS($H$48:$H$63;$K$48:$K$63;D$41;$D$48:$D$63;$B4)-SUM($C4:C4)

Note-se que foi inserido no intervalo D41:H41 o número de dias máximo de cada escalão de antiguidade e que servem para o critério a indicar no SUMIFS:



121

121

6.5.1 Filtros avançados: pequeno exemplo de aplicação

Os filtros avançados permitem copiar os registos duma tabela que satisfazem o critério definido para outro local da folha. O critério é sempre escrito num intervalo de células com, pelo menos duas células seguidas da mesma coluna, em que a célula de cima tem o nome do campo e a célula de baixo tem o valor pretendido. Este tipo de critério designa-se por critério comparado. Existem também os critérios calculados, que se baseiam em fórmulas de cálculo.

Os passos a seguir para extrair os nomes dos clientes do extrato para o mapa de antiguidade são:

1. Alterar o nome do campo no Extrato para “Nome do Cliente” para ficar exatamente igual ao do Mapa de Antiguidade;

2. Escrever o critério comparado no intervalo B48:B49, não esquecendo que o título tem que ser exatamente igual ao nome do campo (Nome do Cliente) e que como se pretendem todos os clientes utiliza-se o asterisco para definir um critério universal, isto é, qualquer conjunto de carateres:

3. Selecionar o intervalo D47:D63

4. Data | Advanced e indicar as diversas opções: selecionar a opção Copy to another location para copiar os clientes para o Mapa de Antiguidade; verificar se a lista de origem (List range) está correta; indicar em Criteria range o intervalo onde está o critério (B48:B49); indicar em Copy to para onde se quer copiar no Mapa de Antiguidade, indicando B3:B33 (o intervalo de destino tem que começar sempre pela célula que tem o nome do campo. No caso de se indicar essa célula apenas, o Excel insere em baixo (nas células necessárias) os nomes dos clientes existentes, mas apaga todas as células que não são necessárias (não selecionar apenas uma célula quando existem dados em baixo). Assim indica-se um intervalo que tenha mais células do que o número de clientes diferentes); selecionar a opção Unique records only para não serem copiados os nomes em duplicado:


122

122

5. Premir Ok e verificar que todos os clientes existentes foram copiados, sem repetição, do Extrato para o Mapa de Antiguidade:



a) Para validar a entrada de dados pode utilizar

Formatação condicional

Filtros automáticos

Funções de data


b) Na formatação condicional baseada em fórmulas para que a formatação seja ativada a fórmula deve devolver

Um número

Um texto

Um valor lógico

Um erro

c) Numa formatação condicional no intervalo G3:G7 aplicou-se com a opção

Qual o resultado

Formata 1 células

Formata 2 células

Formata 4 células

Formata todas as células

d) No intervalo G3:G7 aplicou uma formatação condicional com a fórmula: =OR(G3=MIN(G$3:G$7);G3=MAX(G$3:G$7))

Quais as células que não ficam formatadas

G3:G4

G5:G7

G5

Nenhuma fica formatada


123 123

7. O EXCEL PARA REPORTE DE ESTIMATIVAS E RESULTADOS AO GESTOR

O Excel permite realizar cálculos que são úteis aos gestores e apresentar os dados de forma visual e, consequentemente, mais acessível aos gestores. Neste ponto são exploradas as funcionalidades que permitem resolver equações a uma incógnita e a elaboração de gráficos avançados.

7.1 ENCONTRAR VALOR QUE PERMITE ATINGIR OBJETIVO

A funcionalidade Goal Seek [Atingir objetivo] está disponível no separador Data | What-if Analysis [Data | Análise de hipóteses]:

Esta funcionalidade é utilizada quando se conhece antecipadamente o resultado desejado para uma dada fórmula, mas não se sabe qual é o valor de que a fórmula necessita para atingir esse resultado.

A janela do Goal Seek obriga à indicação da célula objetivo (Set cell), de qual o valor que esta deverá atingir (To value) e qual a célula que deve ser alterada para que o objetivo seja atingido (By changing cell):

Exemplo 25: O gerente duma loja pede ao seu TOC que lhe indique qual a remuneração bruta que deverá pagar a um dos seus colaboradores para que este aufira uma remuneração líquida de 1.000 euros:


124

124

Calcule essa remuneração bruta.

Depois de se elaborar o modelo, inserindo-se uma fórmula para calcular o imposto sobre o rendimento com base nas taxas apresentadas na tabela auxiliar, executa-se o Goal Seek:

Selecionar Data | What-if Analysis | Goal Seek

Indicar os valores na janela Goal Seek:

1. Premir o botão OK. Surge uma janela a indicar se a solução foi ou não encontrada:

2. Premir novamente o botão OK e verificar no modelo o resultado:


125

125

Constata-se que a remuneração bruta terá que ser de 1.397,82 euros, mantendo-se o mesmo nível de fiscalidade.

7.2 TENDÊNCIA

O Excel contempla a função TREND5 e também o gráfico de dispersão que permite traçar a reta de regressão e, assim, caracterizar a relação linear entre X e Y e conhecer a linha de tendência dos valores.

TREND(Y_observado; X_observado; X_novos; [constante])

[TENDÊNCIA] – devolve valores de uma tendência linear, baseada na reta de regressão estimada pelo método dos mínimos quadrados ordinários. O argumento constante é opcional e deve ser indicado FALSE se a reta for Y = mX (isto é, não comtempla a ordenada na origem).

Exemplo 26: O gerente duma loja pede ao seu TOC que lhe apresente uma estimativa das vendas de 2014, tendo apresentado as vendas dos últimos cinco anos:

O valor estimado das vendas em 2014 é obtido com a fórmula:

L28 =TREND(H28:K28;H27:K27;L27)

Note-se que se fosse pretendido estimar as vendas para mais do que um ano, por exemplo 2014 e 2015 então:

1. Selecionar o intervalo L28:M28;

2. Escrever a fórmula matriz em L28 =TREND(H28:K28;H27:K27;L27:M27)

3. Premir em simultâneo as teclas CTRL, SHIFT e ENTER.

O resultado:

5 Consultar a ajuda do Excel para conhecer as funções GROWTH [CRESCIMENTO] e FORECAST

[PREVISÃO] e permitem também efetuar previsões.


126

126

É preciso ter em atenção que para apagar uma fórmula matriz tem que se selecionar o grupo de células onde está a fórmula e carregar na tecla Delete.

A análise da tendência linear pode ser também efetuada recorrendo ao gráfico Scatter (gráfico de dispersão X Y). Para o efeito:

1. Selecionar o intervalo com os valores de X (anos) e Y (Vendas). No caso G27:M28;

2. No grupo Charts do separador Insert premir o botão Scatter e selecionar o gráfico Scatter with only Markers:

Resulta:

82.000,00 €

84.000,00 €

86.000,00 €

88.000,00 €

90.000,00 €

92.000,00 €

94.000,00 €

96.000,00 €

2009 2010 2011 2012 2013 2014 2015 2016

Vendas

Vendas

3. Com o gráfico selecionado premir o botão Trendline no separador Layout e depois selecionar Linear Trendline:


127

127

O resultado permite ver a tendência e que se estima que as vendas de 2014 e 2015 continaurão a subir:

- €

20.000,00 €

40.000,00 €

60.000,00 €

80.000,00 €

100.000,00 €

120.000,00 €

2009 2010 2011 2012 2013 2014 2015 2016

Vendas

Vendas

Linear (Vendas)

7.3 ELABORAÇÃO DE GRÁFICOS AVANÇADOS

O Excel contempla um conjunto de gráficos que podem ser muito úteis na análise de dados. De entre estes destacam-se os gráficos Sparkline que são gráficos de pequena dimensão introduzidos numa célula e que permitem visualizar um conjunto de dados. Esses gráficos podem ser de três tipos: linhas, colunas e de perda/ganho e encontram-se no grupo Sparklines do separador Insert:


128

128

Exemplo 27: analisar de forma visual a evolução das vendas:

Pode-se, por exemplo, inserir um gráfico sparkline de linhas na célula M28:

1. Selecionar o gráfico sparkline line e inserir o intervalo com os dados da vendas (H28:L28) e a célula onde se pretende inserir o gráfico (M28):

2. Premir o botão OK. O resultado é:

3. Agora é possível realçar o ponto de menor e maior valor ou proceder a outros ajustamentos. No separador Design das Ferramentas Sparkline encontram-se as diferentes opções:

Selecionar as opções High Point e Low Point para adicionar marcadores aos dois pontos (no exemplo, valores de 2010 e de 2013).

Note-se que para apagar um gráfico sparkline seleciona-se o gráfico e com o botão direito do rato e executa-se Sparklines | Clear Selected Sparklines:


129

129

Os diferentes tipos de gráficos que o Excel contempla podem ser formatados por forma a se criarem outros tipos de gráficos como, por exemplo, o gráfico velocímetro ou o gráfico termómetro. Existem inúmeros sites que ajudam à criação desses gráficos:

http://treinamax.wordpress.com/2011/12/10/grafico-de-velocimentro/

http://www.officetooltips.com/excel/tips/creating_a_glossy_thermometer_chart.html


130

130

7.4 CASO PRÁTICO: INDICADORES DE DESEMPENHO

O reporte de resultados aos gestores pode ter muita influência na avaliação que este faz do desempenho da sua organização. Uma das formas de apresentar os resultados da atividade é através de um dashboard que, em geral, privilegia a representação visual dos dados.

Introduza um resumo das contas de exploração da empresa dos últimos cinco anos e uma previsão desses valores para o corrente ano. Elabore um resumo dessa informação na forma de dashboard. Por exemplo, pode recorrer a um dos modelos (templates) disponibilizados pelo Excel:

Pode também incluir os dados que lhe permitam calcular o ponto crítico, isto é, a quantidade de vendas que resulta num resultado nulo. Introduza o total dos custos fixos, o custo variável unitário, o preço de venda unitário e a quantidade vendida, e evidencie graficamente o ponto crítico:


131

131



a) Para visualizar a evolução dos resultados líquidos duma empresa numa única célula qual o gráfico mais adequado

Scatter

Sparkline Win/Loss

Sparkline Column

Nenhum das anteriores

b) Na elaboração dum gráfico termómetro recorre a que tipo de gráfico

Line

Column

Pie

Area

c) Para calcular o ponto crítico (em quantidade) recorre-se à funcionalidade

Solver

Gráfico Scatter

Filter

Goal Seek

d) Qual o resultado da seguinte fórmula em L3: =TREND(I3:K3;I2:K2;L2)

2 4

5

8


132 132

8. TESTE DE AUTOAVALIAÇÃO

Considere a folha de cálculo Excel que contém uma tabela com o extrato duma conta em que tem “vendas” identificadas por notas de débito e por faturas e as devoluções ou descontos identificados por notas de crédito.

1. Sabendo que os três últimos caracteres do campo Ref.doc. identificam o número do documento, preencha o campo Nºdoc incluindo antes do número a letra D, F ou C consoante seja, respetivamente, nota de débito, fatura ou nota de crédito (campo Referência).

H5 =

2. Preencha o campo Nº Dias Atraso com “Por vencer”, caso a data de pagamento ainda não tenha sido atingida na data de fecho (G2), ou com o número de dias em que se encontra em atraso, caso já tenha sido ultrapassada.

I5 =

3. Preencha o campo Trimestre com o trimestre em que ocorre o pagamento (data pagamento).

K5 =

4. Determine o número de notas de débito e de faturas emitidas.

M15 =

5. Qual o valor médio das faturas por vencer.

M17 =


133

133

Considere a seguinte tabela (mapa de antiguidade):

6. Indique a fórmula em R13 copiável que lhe permite obter os valores acumulados no intervalo (R13:U13).

R13 =

7. Na célula R19 foi introduzida uma fórmula que devolve o valor do cliente em R17 no trimestre em R18.

Complete a fórmula: R19 =HLOOKUP(R18;R7:V13;____________________________________________)

8. Responda às seguintes quatro questões, assinalando apenas uma das opções de resposta. Considere que hoje é dia 15 de março de 2013.


134 134

8.1 TESTE FINAL DE DIAGNÓSTICO


135 135

9. TRABALHO FUTURO

9.1 CASO PRÁTICO: DASHBOARD

No âmbito de um projeto de quatro anos de comercialização de produtos de quatro gamas diferentes, através de três sucursais, pretende-se um dashboard (painel de indicadores) de suporte à análise dos resultados obtidos.

Dispõe-se da demonstração de resultados dos quatro anos na folha DR (DR!B6:J26), em que se apresentam os valores obtidos de cada rúbrica e os objetivos iniciais determinados para cada uma delas, por ano. Adicionalmente, dispõe-se na mesma folha dos dados das vendas por produto referentes a cada uma das três sucursais, em cada um dos períodos analisados (DR!C61:T81):

Pretende-se que o dashboard tenha o seguinte aspeto:


136

136

Com esta ferramenta é possível:

Comparar as vendas de produtos entre os estabelecimentos selecionados, ter informação sobre o cumprimento dos objetivos e a expressão de cada produto no global das vendas do estabelecimento, de acordo com o período selecionado;

Apresentar graficamente a informação sobre as vendas de cada tipo de produto; apenas nos estabelecimentos pretendidos, de acordo com o período selecionado;

Apresentar em tabela as tendências da evolução dos indicadores contabilísticos nos últimos anos pretendidos, indicar o desempenho e a classificação do mesmo face a uma escala a definir;

Apresentar graficamente (gráfico circular) o indicador de desempenho dos resultados face à escala definida, de acordo com o ano selecionado.

Instruções para a resolução:

Começa-se por calcular os indicadores necessários à elaboração dos gráficos, em DR!K8:L26. Assim:

Inserir a fórmula de cálculo do grau de cumprimento dos resultados previstos para 2013:

K8 =F8/J8

Inserir a fórmula de desempenho (positivo ou negativo) de acordo com o tipo de rúbrica (rendimento/gasto) com base nas classificações (positivo/negativo) dispostas no intervalo Q15:Q16:

L8 =IF(F8>=0;IF(K8>=1;"Positivo";"Negativo");IF(K8>=1;"Negativo";"Positivo"))

Com base nos valores obtidos, já é possível alimentar a tabela com a análise das rúbricas contabilísticas relativamente aos últimos anos em análise (Dashboard!G12:M28):

Começa-se por definir os períodos de análise: os últimos 2 anos (2013 e 2012); os últimos 3 anos (2013, 2012 e 2011); e os últimos 4 anos (2013, 2012, 2011 e 2010).Estes períodos são definidos numa tabela auxiliar (DR!P5:Q7):


137

137

Cria-se, agora, uma ‘Combo box’ na célula Dashboard!H12 para que o utilizador possa escolher o período em análise:

Developer |Insert e escolher Combo Box (Form Control) e desenhar o retângulo na célula H12.

Premir o botão direito do rato sobre o controlo e selecionar Format Control… e indica-se o Input range (DR!$Q$5:$Q$7) e a Cell link (DR!$F$30):

OK


138

138

A partir deste momento, sempre que se seleciona uma das opções da ‘combo box’ (últimos 4 anos, últimos 3 anos ou últimos 2 anos), na folha Dashboard, o Excel gera o valor 1, 2 e 3, respetivamente, em DR!F30.

Compilar em DR!B30:F46 os dados visíveis no dashboard, após a seleção do período:

Na célula relativa aos valores de 2010, C33, coloca-se a fórmula para que o valor esteja visível sempre o utilizador pretenda ver a evolução dos últimos 4 anos, isto é, sempre que F30 seja 1. O mesmo raciocínio para os restantes anos:

C33 =IF($F$30=1;VLOOKUP($B33;$B:$F;MATCH(C$31;$B$7:$F$7);0);0)

D33 =IF(OR($F$30=2;$F$30=1);VLOOKUP($B33;$B:$F;MATCH(D$31;$B$7:$F$7);0);0)

E33 =IF(OR($F$30=3;$F$30=2;$F$30=1);VLOOKUP($B33;$B:$F;MATCH(E$31;$B$7:$F$7);0);0)

F33 igual à anterior (E33)

Atribuir a Dashboard!J13 o título de acordo com a opção selecionada na ‘Combo box’:

J13 =VLOOKUP(DR!F30;DR!P5:Q7;2;0)

Inserir um gráfico sparkline no intervalo Dashboard!J15:J28 que reflita a evolução no período selecionado. Assim, seleciona-se esse intervalo e Insert | Line sparkline:

E indica-se o Data Range (DR!C33:F46) e verifica-se se o Location Range está correto ($J$15:$J$28):


139

139

Para melhor se perceberem os pontos (ainda com o intervalo selecionado J15:J28) marcar a opção Design | Markers

Inserir as fórmulas que permitem preencher a restante tabela (Dashboard!K15:M28):

K15 =VLOOKUP(G15;DR!$B$8:$F$26;5;0)

L15 =VLOOKUP(G15;DR!$B$8:$K$26;10;0)

M15 =VLOOKUP(G15;DR!$B$8:$L$26;11;0)

Apresentar os indicadores do intervalo M15:M28, positivos e negativos, a verde e vermelho, respectivamente. Para tal, recorre-se à formatação condicional e definem-se duas regras, uma para cada situação.

Para o positivo selecionar Home | Conditional Formatting | Highlight Cells Rules | Text that Contains…:


140

140

Resulta:

Ainda em relação aos valores de contabilidade, cria-se um (gráfico) velocímetro que reflita o desempenho do Resultado Líquido do Exercício (RLE) do ano em análise, permitindo através das cores localizar o desempenho em níveis diferenciados conforme as categorias que se definiram em DR!P19:T24 (podem ser definidas categorias conforme os intervalos pretendidos):


141

141

A indicação de nível de 0 a 10 vai ser útil na elaboração do velocímetro, estando a leitura associada à percentagem de 0% a 100% de acordo com as categorias:

Começa-se por calcular os valores do RLE e variações nos diferentes períodos no intervalo DR!C50:F53:

C50 =C26

C51 =(C50-G26)/G26

C52 =VLOOKUP(C51;$P$20:$Q$24;2) Note-se que dado ser uma procura por intervalos, é necessário que o valor mínimo da tabela (-30% em P20) cubra o mínimo dos valores a encontrar (neste caso -28% em C51)

C53 =VLOOKUP(C52;$Q$20:$T$24;4;0)

Resulta:

Inicia-se o desenho do gráfico velocímetro, inserindo em Dashboard!P12 uma ‘Combo box’ para escolha do ano a analisar. Indica-se o Input range (DR!$Q$8:$Q$11), que compreende os 4 anos em análise, e a Cell link (DR!$I$53), que guardara o valor gerado (de 1 a 4, respetivamente, para os anos 2010 a 2013):


142

142

Apresenta-se na célula J53 o ano a que corresponde o gráfico:

J53 =VLOOKUP(I53;$P$8:$Q$11;2;0)

Selecciona-se DR!R20:R25 e insere-se o gráfico “Doughnut” (onde o somatório 10, serve para que o círculo esteja dividido exactamente ao meio):

De forma a dar o efeito pretendido, apagam-se o título e a legenda do gráfico e, premindo o botão direito do rato em cima do gráfico, seleciona- -se Format Data Series… para se colocarem os seguintes valores:


143

143

Cria-se uma tabela de apoio ao velocímetro (DR!H48:L50) e aplicam-se algumas noções de trigonometria (cosseno e seno):

I49 =0

I50 =S24 (tem o valor 10)

K51 =-COS(J50)

L51 =SIN(J50)

Colocam-se os pontos no gráfico de forma a criar o ponteiro do velocímetro:

Copiar o intervalo K50:L51 (coordenadas)

Com o gráfico selecionado fazer um colar especial e escolhem-se as seguintes opções:


144 144

Alterar o tipo de gráfico associado à série agora criada para Scatter with Straight Lines and Markers:

Primeiro garantir que a nova série é a primeira em Design | Select Data:

Agora alterar o tipo de gráfico em Design | Change Chart Type:


145

145

Faltam ainda algumas formatações.

Colocar o ponto de rotação do ponteiro no centro do gráfico, alterando os limites das escalas dos dois eixos (ordenadas e abcissas) para -1 e 1, respetivamente, para o mínimo e máximo. Assim:

Premir cada uma das escalas e ajustar os dois valores:


146

146

Remover as linhas de grelha e os eixos do gráfico, selecionando-as e premindo a tecla Delete. Resulta:

Colocar sem preenchimento a metade do gráfico circular, selecionando-a e marcar a opção ‘No fill’:

Finalmente, alterar as cores de forma a mostrar a progressão do desempenho:

. Agora cotinuar…


147 147

9.2 CASO PRÁTICO: VERIFICAÇÃO DE PAGAMENTOS

Considere que uma empresa processa os pagamentos referentes a um mês no último dia do mês seguinte. No entanto, algumas falhas têm ocorrido, tais como, pagamentos duplicados, pagamentos com valor errado e IBAN incorretos. Assim, pretende-se desenvolver mecanismos de controlo de falhas no processamento dos pagamentos.

A informação disponível relativa ao 1º trimestre encontra-se em Faturas!B4:H20 e Pagamentos!B4:E12 e pretende-se:

1. Verificar a correção do mapa “faturas”, nomeadamente:

A existência de apenas três tipos de documentos (Fatura, N Débito, e N Crédito);

Os sinais dos valores de cada documento (Fatura e N Débito com valores positivos e N Crédito com valor negativo);

Que a data de pagamento igual ou posterior à dada do documento;

Que a data de pagamento ocorre no último dia do mês seguinte;

Que todos os NIF têm 9 algarismos.

2. Verificar a correção do mapa “pagamentos”, nomeadamente:

Se a cada entidade é efetuado um pagamento por mês, que corresponde ao valor faturado no mês anterior;

Se não há IBAN repetidos (duas entidades com o mesmo IBAN);

Se há novos IBAN num dado mês;

Se existem pagamentos não efetuados (valores que não correspondem ao devido).

Para uma melhor visualização de possíveis erros aconselha-se o recurso à formatação condicional. Os mapas contem os seguintes dados e deverá alterar alguns valores para verificar a correção das fórmulas utilizadas:


148

148


149 149

10. CONCLUSÃO

Este manual de Excel Aplicado à Contabilidade foi escrito com a intensão de permitir aos leitores conhecerem algumas das inúmeras potencialidades desta ferramenta de produtividade.

Pretendeu-se ilustrar cada funcionalidade ou função do Excel com exemplos práticos no contexto da contabilidade (e auditoria), indo de exemplos relativamente simples até exemplos mais complexos em que apenas integrando funcionalidades e funções se conseguem resolver os problemas.

É evidente, que a leitura deste manual é um a preciosa ajuda para quem pretende desenvolver competências avançadas de Excel, assim o esperamos. Mas também é evidente que só com muitas leituras e com uma procura insistente de soluções para os problemas que se colocam no dia-a-dia se conseguem desenvolver cada vez mais as capacidades para a construção de modelos de cálculo avançados e com utilidade reconhecida.

Muitas das funcionalidades do Excel não foram neste manual abordadas, algumas por se considerarem de nível básico de utilização de folhas de cálculo, como, por exemplo, criação e edição de gráficos de utilização frequente, funções matemáticas, incluindo as de arredondamento, distinção entre referências absolutas e relativas e importação de dados de páginas Web. Outras, de nível avançado e com indiscutível utilidade, principalmente na construção de modelos de apoio à decisão, ficaram para outra oportunidade, entre elas, as ferramentas de análise de sensibilidade e de cenários, a funcionalidade Solver para problemas de otimização, as referências a intervalos dinâmicas com aplicação na elaboração de gráficos, e a criação de formulários.

De qualquer forma, acreditamos que a leitura deste manual, em paralelo com a aplicação dos conteúdos no Excel, ajudará a leitor no seu dia-a-dia. Para termos a certeza disso, agradecemos aos leitores que nos enviem exemplos e/ou casos da aplicação do Excel nas suas tarefas diárias. Todos juntos, aprendemos mais e melhor, todos juntos, partilhando os nossos conhecimentos ficamos mais especialistas e competentes, todos juntos…

OBRIGADO!


150 150

11. BIBLIOGRAFIA EM PORTUGUÊS

Help Online do Excel e Manuais da Microsoft.

Martins, António (2013) Excel Aplicado à Gestão, 3ª Edição, Edições Sílabo.

Peres, Paula (2007) Excel: Macros e Aplicações, Edições Sílabo.

Pinto, Mário (2011) Microsoft Excel 2010, Centro Atlântico.

Rodrigues, Luís (2011) Utilização do Excel 2010 para Economia e Gestão, FCA.

FORMANDO

QUESTIONÁRIO DE AVALIAÇÃO

1. EXPETATIVAS

2. ORGANIZAÇÃO

3. MONITORIA

Curso:

Data: / / Local:

Formador(a):

Caro (a) colega, agradecemos que nos dispense alguns minutos do seu tempo, respondendoao nosso questionário. A sua opinião tem muita importância para nós. Após preenchido, é favorentregar ao formador.

1.1 A sua expetativa em relação a este curso era:

MauFracoRazoávelMuito Bom Bom

1.2 O nível de conhecimentos com que iniciou o curso era:

No que se refere ao apoio logístico disponibilizado (instalações,equipamentos, apoio técnico-administrativo) considerou-o:

3.1 O método utilizado pelo formador foi:

3.2 O formador demonstrou possuir um nível de conhecimentos:

4. APRECIAÇÃO GLOBAL DO CURSO

4.1 No final do curso, a sua expetativa foi:

4.2 Para o exercício da profissão, os conhecimentos adquiridos são:

4.3 Globalmente considerou o curso:

4.4 Que curso/s sugere para sua realização profissional?

5. SUGESTÕES

Muito obrigado pela sua colaboração.

3.7 A utilidade e a pertinência dos exercícios e trabalhos realizados,face aos objetivos propostos, foi, em sua opinião:

3.4 A qualidade do material pedagógico-didático utilizado(manual e/ou outros documentos) foi:

3.3 O formador motivou o grupo de forma:

3.5 Os conteúdos programáticos foram abordados de forma:

3.6 A duração do curso foi em sua opinião:

3676025346