Baixe o app para aproveitar ainda mais
Prévia do material em texto
In fo rm át ic a Excel Avançado Escola Virtual Informática Excel Avançado �����(VFROD��9LUWXDO Ficha técnica ® Reservados todos os direitos patrimoniais e de reprodução à Fundação Bradesco Homepage: www.fb.org.br AUTORIA InfoSERVER S.A. Departamento de Treinamento COLABORADORES Departamento Escola Virtual - Fundação Bradesco PROJETO GRÁFICO E REVISÃO Setor Geração de Recursos Didáticos PUBLICAÇÃO: 2010 3 APRESENTAÇÃO Esta apostila compõe o material didático dos cursos de Informática referentes ao Pacote Offi ce 2007, composto pelos aplicativos Word, Excel, PowerPoint, Access, Outlook e Internet. Na sequência, apresentamos defi nições, orientações técnicas, procedimentos e exercícios práticos, fundamentais para qualifi car profi ssionalmente jovens e adultos, para que possam ter autonomia no uso da ferramenta, dominar as competências exigidas pelo mercado de trabalho e, assim, favorecer sua empregabilidade em qualquer área ocupacional. Lembramos que as profundas transformações ocorridas no mundo do trabalho, a velocidade da informação, a comunicação, a globalização, a difusão de novas tecnologias e de novas formas de organização do trabalho são alguns exemplos das mudanças que vêm exigindo dos trabalhadores o desenvolvimento de novas competências frente às profi ssões. Diante desse contexto, a informática é parte fundamental dessa transformação, visto que está presente em todos os setores da sociedade: no comércio, na indústria, na saúde, na educação etc. Enfi m, as informações aqui apresentadas servirão para alicerçar a base do conhecimento requerida para a construção das competências e habilidades propostas na qualifi cação de Operador de Microcomputador. 5 SUMÁRIO 1. TRABALHANDO COM NOMES ..................................................................................11 1.1 O que é um nome? .........................................................................................11 2. ORGANIZANDO DADOS ..........................................................................................15 2.1 Classifi cação de dados ....................................................................................15 2.2 Subtotais .......................................................................................................18 2.2.1 Criando subtotais ..................................................................................19 2.2.2 Removendo subtotais ............................................................................22 2.3. Filtros ...........................................................................................................23 2.3.1 Utilizando fi ltro com critérios avançados ..................................................23 2.3.2 Vários critérios em uma coluna ..............................................................26 2.3.3 Vários critérios em várias colunas em que todos os critérios devem ser verdadeiros ...................................................................................................27 2.3.4 Vários critérios em várias colunas em que qualquer critério pode ser verdadeiro ....................................................................................................28 2.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios para várias colunas ...............................................................................................28 2.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios para uma coluna ..........................................................................................................29 2.3.7 Critérios para localizar valores de texto que compartilhem alguns caracteres mas não outros .............................................................................................30 3. TABELA DINÂMICA .................................................................................................31 3.1 Criar um relatório de tabela dinâmica ou gráfi co dinâmico .................................31 4. IMPORTAÇÃO DE DADOS ........................................................................................40 4.1 Arquivo de texto .............................................................................................40 4.1.1 Importando arquivo de texto e abrindo-o ................................................40 4.1.2 Importando arquivo de texto como intervalo de dados externos ...............46 4.2 Importando dados do Access ...........................................................................46 4.3 Importando dados de uma página Web ............................................................50 4.3 Atualizando dados importados .........................................................................52 5. CENÁRIOS .............................................................................................................52 5.1 Editando um cenário .......................................................................................57 5.2 Criando um relatório de cenário .......................................................................58 5.3 Excluindo um cenário ......................................................................................60 6. SEGURANÇA DAS INFORMAÇÕES ............................................................................61 6.1 Protegendo toda a planilha ..............................................................................61 6.2 Protegendo partes da planilha .........................................................................63 6.3 Protegendo uma pasta de trabalho ..................................................................65 6.4 Desprotegendo células e planilhas ...................................................................66 6 7. VALIDAÇÃO ............................................................................................................66 7.1 Criando uma validação de dados ......................................................................67 7.2 Inserindo uma mensagem de entrada ..............................................................73 7.3 Inserindo um alerta de erro .............................................................................74 8. USANDO AUDITORIA EM CÉLULAS ..........................................................................76 8.1 Janela de inspeção .........................................................................................76 8.1.1 Adicionando células à janela de inspeção ................................................76 8.2 Realizando auditoria em planilha ......................................................................78 8.2.1 Rastreando células precedentes ou dependentes .....................................79 8.2.2 Removendo o rastreamento ...................................................................80 9. PERSONALIZANDO A ÁREA DE TRABALHO ...............................................................80 9.1 Criando barra de ferramentas .........................................................................80 9.2 Excluindo barra de ferramentas .......................................................................82 9.3 Criando menu de comandos ............................................................................82 10. CONSTRUINDO UM GRUPO DE TRABALHO ............................................................82 10.1 Salvando um grupo de pastas de trabalho personalizadas ................................82 11. SOLUCIONADO PROBLEMAS ..................................................................................83 11.1 Problemas com uma variável .........................................................................83 11.2 Problemas com mais de uma variável .............................................................87 11.3 Problemas sem solução .................................................................................9312. FUNÇÕES .............................................................................................................95 12.1 Funções deTexto ...........................................................................................95 12.1.1 Função ESQUERDA ..............................................................................95 12.1.2 Função DIREITA ..................................................................................96 12.1.3 Função MAIÚSCULA .............................................................................97 12.1.4 Função MINÚSCULA ............................................................................98 12.1.5 Função PRI.MAIÚSCULA ......................................................................98 12.1.6 Função LOCALIZAR .............................................................................99 12.1.7 Função EXT.TEXTO ............................................................................102 12.1.8 Função CONCATENAR ........................................................................103 12.2 Funções Matemáticas e Trigonométricas .......................................................104 12.2.1 Função ARRED ..................................................................................104 12.2.2 Função ARREDONDAR.PARA.CIMA ......................................................105 12.2.3 Função ARREDONDAR.PARA.BAIXO ....................................................106 12.2.4 Função INT .......................................................................................107 12.2.5 Função SOMASE ................................................................................108 12.3 Funções Estatísticas ....................................................................................112 12.3.1 Função CONT.NÚM ............................................................................112 12.3.2 Função CONT.VALORES .....................................................................113 12.3.3 Função CONTAR.VAZIO .....................................................................115 12.3.4 Função CONT.SE ...............................................................................116 7 12.4 Funções de Pesquisa e Referência ................................................................117 12.4.1 Função PROCH ..................................................................................118 12.4.2 Função PROCV ..................................................................................120 12.4.3 Função ÍNDICE .................................................................................122 12.4.4 Função CORRESP ..............................................................................125 12.5 Funções de Banco de Dados ........................................................................127 12.5.1 Função BDMÉDIA ..............................................................................128 12.5.2 Função BDCONTAR ............................................................................129 12.5.3 Função BDMÍN ..................................................................................129 12.5.4 Função BDMÁX .................................................................................130 12.5.5 Função BDMULTIPL ...........................................................................131 12.5.6 Função BDSOMA ...............................................................................132 12.6 Funções de Informações .............................................................................133 12.6.1 Função ÉERROS ................................................................................134 12.7 Funções Financeiras ....................................................................................136 12.7.1 Função VF .........................................................................................136 12.7.2 Função NPER ....................................................................................138 12.7.3 Função PGTO ....................................................................................139 12.7.4 Função VP .........................................................................................140 12.7.5 Função TAXA .....................................................................................142 13. TABELA DE DADOS .............................................................................................144 13.1 Tabela de dados com uma variável de entrada ..............................................144 13.2 Tabela de dados com duas variáveis de entrada ............................................147 14. FORMULÁRIOS PERSONALIZADOS .......................................................................148 14.1 Criando formulário ......................................................................................149 EXERCÍCIOS ............................................................................................................161 9 INTRODUÇÃO Seja bem-vindo ao Excel 2007 – Avançado! No curso de Excel Avançado, você aprenderá como organizar um banco de dados utilizando o conceito de relacionamento entre tabelas. Além disso, serão abordados recursos como o uso da importação de dados, cenários, segurança das informações, validação de dados, auditoria em células, funções, formulários personalizados etc. 11 1. Trabalhando com nomes 1.1 O que é um nome? Nome é uma referência que fazemos a uma célula ou intervalo de células. Esse nome pode ser utilizado em uma fórmula ou função, dentro de qualquer planilha da pasta de trabalho. Usando nomes, você pode facilitar muito o entendimento e a manutenção das fórmulas. Depois de adotar a prática do uso de nomes, você poderá atualizá-los, auditá-los e gerenciá-los facilmente. Exemplo Tipo Exemplo sem nome Exemplo com nome Referência =SOMA(C20:C30) =SOMA(TOTALFINAL) Constante =PRODUTO(A5,8.3) =PRODUTO(PRECO;QUANT) Fórmula =SOMA(PROCV(A1,B1:F20,5,FALSO), —G5) =SOMA(PROCURA) Tabela =C4:G36 =TABELAPRINCIPAL Para nomear uma célula ou região, faça o seguinte: 1. Abra a pasta de trabalho Exemplos e selecione a planilha Nomes. 2. Clique na célula B12. 12 Figura 1 Figura 2 3. Na guia Fórmulas, grupo Nomes Defi nidos, clique em Defi nir Nome . 4. Na caixa de diálogo Novo Nome, digite o nome desejado no campo Nome e clique em OK. Para criar um nome, algumas regras devem ser obedecidas: • O primeiro caractere do nome deve ser letra ou underline. • O nome pode ter até 255 caracteres. • Não pode haver espaços. 13 Figura 3 Ao defi nirmos um nome para uma célula ou região, ele passa a ser exclusivo da pasta de trabalho, ou seja, em qualquer planilha, podemos fazer referência a ele. Por exemplo, em qualquer célula de qualquer planilha da pasta de trabalho em que você digite =Dólar, aparecerá o valor digitado para o dólar. Agora, vamos utilizar esse nome no cálculo da célula C5. 5. Posicione o cursor na célula C5 e digite a fórmula =B5*Dólar. 6. Em seguida, é só copiar a fórmula para as demais células. Observe que não foi necessário fi xar o endereço ao utilizar um nome. Dica Você pode nomear uma célula ou região rapidamente. Basta selecionar a célula ou região, clicar na caixa Nome na barra de fórmulas, digitar o nome e pressionar Enter. 14 Para visualizar todos os nomes atribuídos a células ou regiões nas planilhas, basta clicar na seta Drop Down, na caixa Nome, à esquerda na barra de fórmulas. Figura 4 Observação A caixa Nome, na barra de fórmulas, só pode ser utilizada para criar ou exibir nomes. Para excluir um nome, faça o seguinte: 1. Na guia Fórmulas, grupo Nomes Defi nidos, clique no botão Gerenciador de Nomes . 2. Na caixa Gerenciador de Nomes, selecione o nome desejado e clique no botão Excluir. 15 Figura 5 3. Caso esse nome tenhasido utilizado em alguma fórmula ou função, aparecerá a seguinte mensagem de erro: #NOME? Vamos praticar! Exercício 1 2. Organizando dados Ao inserir dados em uma planilha, eles podem não se apresentar ordenados da maneira que você deseja visualizá-los. Com os recursos de classifi cação e a aplicação de fi ltros, são criadas novas perspectivas para ajustar os dados às suas necessidades. 2.1 Classificação de dados 1. Abra a pasta de trabalho Exemplos e selecione planilha Subtotais. 2. Selecione o rótulo de uma coluna. 16 Figura 6 Figura 7 Figura 8 3. Na guia Dados, grupo Classifi car e Filtrar, clique em Classifi car . 4. Na caixa de diálogo Classifi car, selecione a coluna desejada, valores e ordem. 17 Figura 9 Figura 10 Você pode adicionar até 64 níveis de classifi cação por meio do botão Adicionar Nível. Veja detalhes na fi gura 9. No botão Opções de classifi cação, você pode defi nir outras orientações de classifi cação. 18 2.2 Subtotais Automaticamente, você pode calcular os subtotais e os totais gerais para uma coluna, usando o comando Subtotal, grupo Estrutura de Tópicos, guia Dados. Figura 11 Os subtotais são calculados com uma função de resumo, como SOMA ou MÉDIA. Você pode exibir mais de um tipo de função de resumo para cada coluna. Os totais gerais são derivados de dados de detalhes, e não dos valores nos subtotais. Por exemplo, se você usar a função de resumo MÉDIA, a linha de total geral exibirá uma média de todas as linhas de detalhes, e não uma média dos valores das linhas de subtotal. Área de detalhes Para subtotais automáticos e estruturas de tópicos de planilha, as linhas ou colunas de subtotal são totalizadas pelos dados de resumo. Em geral, os dados de detalhes estão, imediatamente, acima ou à esquerda dos dados de resumo. 19 Figura 12 2.2.1 Criando subtotais 1. Selecione o intervalo de células desejado. Certifi que-se de que as colunas tenham um rótulo, contenham dados relacionados e que o intervalo selecionado não tenha linhas ou colunas em branco. 2. Classifi que a coluna que servirá de base para gerar o subtotal. 3. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal . 4. Na caixa de diálogo Subtotais, campo A cada alteração em, selecione a coluna a ser subtotalizada. Em nosso exemplo, é Produto. 5. No campo Usar função, clique na função que você deseja utilizar para calcular os subtotais. Em nosso exemplo, é SOMA. 6. Na caixa Adicionar subtotal a, marque a caixa de seleção de cada coluna que contenha valores a serem subtotalizados. Em nosso exemplo, é Total. 20 Figura 13 Figura 14 Veja o resultado. 21 Figura 15 7. Se você desejar uma quebra de página automática após cada subtotal, marque a caixa de seleção Quebra de página entre grupos (fi gura 15). 8. Para especifi car uma linha de resumo acima da linha de detalhes, desmarque a caixa de seleção Resumir abaixo dos dados (fi gura 15). 9. Como opção, você poderá usar o comando Subtotais novamente, repetindo as etapas de 1 a 6 para adicionar mais subtotais com funções de resumo diferentes. Para impedir que os subtotais existentes sejam sobrescritos, desmarque a caixa de seleção Substituir subtotais atuais (fi gura 15). Dica Para exibir um resumo somente dos subtotais e totais gerais, clique nos símbolos de estrutura de tópicos , ao lado dos números de linha. Use os símbolos e para exibir ou ocultar as linhas de detalhes dos subtotais individuais. 22 2.2.2 Removendo subtotais Quando você remove subtotais, o Excel remove também a estrutura de tópicos e as quebras de página que você inseriu na lista juntamente com os subtotais. 1. Clique em uma célula na lista que contém um subtotal. 2. Na guia Dados, grupo Estrutura de Tópicos, clique em Subtotal . A caixa de diálogo Subtotal é exibida. 3. Clique em Remover todos. Figura 16 Vamos praticar! Exercício 2 23 Figura 17 2.3 Filtros Os dados fi ltrados exibem somente as linhas que atendem aos critérios especifi cados e ocultam as demais. Depois de fi ltrar os dados, você pode copiá-los, editá-los, formatá-los, fazer gráfi co e imprimir o subconjunto de dados fi ltrados, sem reorganizá-los nem movê-los. Você também pode fi ltrar dados por mais de uma coluna. Os fi ltros são aditivos, o que signifi ca que cada fi ltro adicional baseia-se no fi ltro atual e ainda reduz o subconjunto de dados. 2.3.1 Utilizando filtro com critérios avançados A opção Avançado trabalha de forma diferente da opção Filtro (ambas na guia Dados, grupo Classifi car e fi ltrar) em vários aspectos: • Exibe a caixa de diálogo Filtro Avançado em vez do menu AutoFiltro. • Requer critérios avançados em um intervalo de critérios separados na planilha e acima do intervalo de células ou da tabela que se deseja fi ltrar. O Excel usa o intervalo de critérios separados na caixa de diálogo Filtro Avançado como fonte dos critérios avançados. • Ao criar o intervalo para o critério, devem-se adicionar pelo menos três linhas em branco acima do intervalo a ter os dados fi ltrados, sendo que este deve ter rótulos de coluna. Segue exemplo de planilha estruturada com intervalo de critérios e de dados a serem fi ltrados. 24 1. Abra a pasta de trabalho Exemplos e selecione a planilha FILTROS_AVANC. Figura 18 Figura 19 2. Na célula B2, digite Flauta para selecionar esse instrumento. 3. Na guia Dados, grupo Classifi car e Filtrar, clique no botão Avançado . 4. Para fi ltrar o intervalo ocultando as linhas que não obedecem aos critérios, clique em Filtrar a lista no local. 25 Figura 20 Figura 21 Figura 22 5. Para fi ltrar o intervalo para outra área da planilha, copiando as linhas que obedecem aos critérios, clique em Copiar para outro local. E, na caixa Copiar para, indique a célula a partir da qual você deseja colar as linhas. 6. Na caixa Intervalo da lista, insira a referência do intervalo em que os dados serão fi ltrados, inclusive os rótulos. 7. Na caixa Intervalo de critérios, insira a referência do intervalo de critérios, inclusive os rótulos. 8. Clique em OK e observe o resultado. 26 Figura 23 9. Para fi ltrar novos dados, altere o valor no intervalo de critérios e fi ltre os dados novamente. 10. Para visualizar todas as linhas, clique no botão Limpar. Ao denominar um intervalo de células como Critérios, a referência para o intervalo aparecerá na caixa Intervalo de critérios automaticamente. Você também pode defi nir o nome Banco de dados para o intervalo de dados a ser fi ltrado e o nome Extração para a área onde você deseja colar as linhas. Esses intervalos aparecerão, automática e respectivamente, nas caixas Intervalo da lista e Copiar para. Ao copiar linhas fi ltradas para outro local, você pode especifi car que colunas incluir na operação de cópia. Antes de fi ltrar, copie os rótulos das colunas desejadas para a primeira linha da área onde planeja colar as linhas fi ltradas. Ao fi ltrar, insira uma referência nos rótulos das colunas copiadas na caixa Copiar para. As linhas copiadas incluirão somente as colunas para as quais você copiou os rótulos. 2.3.2 Vários critérios em uma coluna Lógica booleana: (Região = Norte OU Região = Nordeste) Para localizar linhas que atendam a vários critérios para uma coluna, digite os critérios diretamente um após o outro, em linhas separadas. 27 Figura 24 Figura 25 No exemplo da fi gura 24, o intervalo de critérios fi ltrará as linhas que contêm Norte ou Nordeste na coluna Região. 2.3.3 Vários critérios em várias colunas em que todos os critérios devem ser verdadeiros Lógica booleana: (SIGLA UF = SP E POPULAÇÃO > 10000) Para localizar linhas que atendam a critérios em várias colunas, digite todos os critérios namesma linha do intervalo de critérios. No exemplo da fi gura 25, o intervalo de critérios fi ltrará as linhas que contêm SP na coluna SIGLA UF e população maior que 10.000 na coluna POPULAÇÃO. 28 2.3.4 Vários critérios em várias colunas em que qualquer critério pode ser verdadeiro Lógica booleana: (SIGLA UF = AC OU REGIÃO = Sudeste) Para localizar linhas que atendam a critérios em várias colunas, em que qualquer um pode ser verdadeiro, digite os critérios em linhas diferentes do intervalo de critérios. No exemplo da fi gura 26, o intervalo de critérios fi ltrará as linhas que contêm AC na coluna SIGLA UF ou SUDESTE na coluna REGIÃO. Figura 26 2.3.5 Vários conjuntos de critérios em que cada conjunto inclui critérios para várias colunas Lógica booleana: ((SIGLA UF = MG E POPULAÇÃO >=300000) OU (SIGLA UF = RN E POPULAÇÃO <=250000)) Para localizar linhas que atendam a vários conjuntos de critérios, em que cada conjunto inclui critérios para várias colunas, digite cada conjunto de critérios em linhas separadas. No exemplo da fi gura 27, o intervalo de critérios fi ltrará as linhas que contêm MG na coluna SIGLA UF e o número de população maior ou igual que 300.000 na coluna POPULAÇÃO; ou exibirá as linhas que contêm RN na coluna SIGLA UF e um valor menor ou igual a 250.000 na coluna POPULAÇÃO. 29 Figura 27 Figura 28 2.3.6 Vários conjuntos de critérios em que cada conjunto inclui critérios para uma coluna Lógica booleana: ((POPULAÇÃO > 1000000 E < 2000000) OU (POPULAÇÃO < 1500000)) Para localizar linhas que correspondam a vários conjuntos de critérios, em que cada conjunto inclui critérios para uma coluna, inclua várias colunas para o mesmo título da coluna. No exemplo da fi gura 28, o intervalo de critérios fi ltrará as linhas que contêm POPULAÇÃO entre 1.000.000 e 2.000.000, bem como valores menores que 1.500.000 na coluna POPULAÇÃO. 30 2.3.7 Critérios para localizar valores de texto que compartilhem alguns caracteres, mas não outros Para localizar valores de texto que compartilhem alguns caracteres, mas não outros, siga um ou mais destes procedimentos: Digite um ou mais caracteres para localizar linhas com valor de texto em uma coluna que inicie com tais caracteres. Por exemplo, se você digitar o texto São como critério, o Excel irá localizar “São Paulo”, “São Sebastião” e “São Pedro”. Os seguintes caracteres curinga podem ser utilizados como critérios de comparação. Use Para localizar ? (ponto de interrogação) Qualquer caractere único Por exemplo: antoni? Localiza:“antonio” e “antonia” * (asterisco) Qualquer número de caracteres Por exemplo: *este Localiza: “Nordeste” e “Sudeste” ~ (til) seguido de ?, * ou ~ Um ponto de interrogação, asterisco ou til Por exemplo: fy91~? Localiza: “fy91?” No seguinte intervalo de dados, o intervalo de critérios exibe as linhas que se iniciem com São* na coluna MUNICÍPIOS. Figura 29 Vamos praticar! Exercício 3 31 Figura 30 3. Tabela dinâmica Um relatório de tabela dinâmica é utilizado para resumir, analisar, explorar e apresentar dados de resumo e para ver comparações, padrões e tendências facilmente. O relatório de tabela dinâmica permite tomar decisões corretas sobre dados críticos. 3.1 Criar um relatório de tabela dinâmica ou gráfico dinâmico Para criar um relatório de tabela dinâmica ou gráfi co dinâmico, você deve se conectar à fonte de dados e inserir o local do relatório. Essa fonte não deve conter colunas ou linhas vazias. Por exemplo, linhas ou colunas em branco que são usadas para separar um bloco de dados de outro devem ser removidas. 1. Abra a pasta de trabalho Exemplos e selecione a planilha Tabela-Dinâmica. 2. Selecione uma célula em um intervalo de células ou coloque o ponto de inserção dentro da planilha. 32 3. Certifi que-se de que o intervalo de células tenha títulos de coluna, que, na tabela dinâmica, são chamados de campos. Figura 31 Figura 32 Figura 33 4. Para criar um relatório de tabela dinâmica, na guia Inserir, grupo Tabelas, clique em Tabela Dinâmica . 5. Em seguida, clique em Tabela Dinâmica. A caixa de diálogo Criar Tabela Dinâmica é exibida. 6. Clique em Selecionar uma tabela ou intervalo. 7. Digite o intervalo de células ou a referência do nome da tabela e clique em OK 33 Figura 34 Se você selecionou uma célula em um intervalo de células, ou se o ponto de inserção estava em uma tabela antes de iniciar o assistente, o intervalo de células ou a referência do nome da tabelas é exibido na caixa Tabela/Intervalo. Como alternativa, para selecionar um intervalo de células ou uma tabela, clique em Recolher Caixa de Diálogo para ocultar a caixa de diálogo temporariamente. Selecione o intervalo na planilha e pressione Expandir Caixa de Diálogo . 8. Um relatório de tabela dinâmica vazio é criado em uma nova planilha e a caixa de diálogo Lista de campos da tabela dinâmica é aberta. 1 Área de layout do relatório de tabela dinâmica. 2 Lista de campos de tabela dinâmica. 34 Agora, você está pronto para criar o relatório de tabela dinâmica. Os campos selecionados para o relatório dependem do que você deseja saber. Por exemplo, valor total por produto. Para obter a resposta, você precisa de dados sobre os produtos e seus preços. 9. Selecione os campos Produto e Preço na caixa de seleção, na Lista de campos da tabela dinâmica. Figura 35 Observe que você não precisa usar todos os campos da lista de campos para criar um relatório. Quando você seleciona um campo, o Excel coloca-o em uma área padrão do layout para você. • Os dados do campo Produto, que não contêm números, são exibidos como linhas no lado esquerdo do relatório automaticamente. • Os dados do campo Preço, que contêm números, são mostrados corretamente na área à direita. • O título sobre os dados do produto é Rótulos de Linha. O título sobre os totais do preço é Soma de Preço. Isso ocorre porque o Excel usa a função SOMA para somar campos numéricos. Agora, você conhece o total por produto. 10. Salve a pasta de trabalho. 35 Figura 36 Figura 37 Agora, observe que os dados de origem apresentam informações sobre os produtos em todos os países que fazem parte da planilha. Sendo assim, outra pergunta que você poderia fazer seria a seguinte: Quais são os totais dos preços dos produtos por país? Para obter essa resposta, adicione o campo País ao relatório de tabela dinâmica como um fi ltro de relatório. Use um fi ltro de relatório para localizar um subconjunto de dados no relatório, geralmente uma linha de produto, uma duração ou uma região geográfi ca. Para isso, faça o seguinte: 1. Clique com o botão direito do mouse sobre o campo País. 2. Selecione a opção Adicionar ao Filtro de Relatório. 3. Usando o campo País como um fi ltro de relatório, você pode ver um relatório separado por um país específi co, ou pode ver as vendas para ambos os países juntos. 36 4. Para que você possa entender melhor o exemplo, selecionaremos um país e, depois, clicaremos no botão OK. Observe o exemplo da fi gura 38: Figura 38 Figura 39 5. Note que apenas os produtos e os preços do país escolhido estão sendo exibidos na tabela dinâmica. 37 Figura 40 Observe também que, ao lado do país selecionado, será exibido o botão Filtro , que representa que os dados abaixo foram fi ltrados segundo o dado ao lado. Essa informação também aparece na lista de campos da tabela dinâmica. Para selecionar mais de um país, abra novamente a caixa para seleção e marque Selecionar vários itens. Marque os países que deseja fi ltrar e clique em OK. Figura 41 Vamos praticar! Exercício 4 38 Para utilizar dados externos, faça o seguinte: 1. Clique em Usar uma fonte de dados externa. 2. Cliqueem Escolher Conexão. Figura 43 3. A caixa de diálogo Conexões Existentes é exibida. 39 Figura 44 Figura 45 Figura 46 4. Na lista suspensa Mostrar, na parte superior da caixa de diálogo, selecione a categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as Conexões, que é o padrão. 5. Selecione uma conexão a partir da caixa de listagem Selecionar uma Conexão e clique em Abrir. 6. Ao escolher uma conexão da categoria Conexões desta Pasta de Trabalho, você reutilizará ou compartilhará uma conexão existente. Ao escolher uma conexão das categorias Arquivos de conexão da rede ou Arquivos de conexão deste computador, o arquivo de conexão será copiado na pasta de trabalho como uma nova conexão de pasta de trabalho e usado como a nova conexão para o relatório de tabela dinâmica. 7. Para colocar o relatório de tabela dinâmica em uma nova planilha, começando na célula A1, clique em Nova Planilha. 8. Para colocar o relatório de tabela dinâmica em uma planilha existente, selecione Planilha Existente. Em seguida, digite a primeira célula no intervalo de células onde deseja colocar o relatório de tabela dinâmica. 40 9. Como alternativa, clique em Recolher Caixa de Diálogo para ocultar a caixa de diálogo temporariamente. Selecione a célula inicial na planilha e pressione Expandir Caixa de Diálogo . 10. Clique em OK. 4. Importação de dados O principal benefício da conexão com dados externos (importação) é a possibilidade de analisar esses dados no Excel periodicamente, sem copiá-los repetidamente. Essa é uma operação que pode levar tempo e que está propensa a erros. Depois de conectar-se a dados externos, você também pode, automaticamente, atualizar as pastas de trabalho a partir da fonte de dados original, sempre que ela for alterada. 4.1 Arquivo de texto Há duas formas de importar dados de um arquivo de texto usando o Excel: • Abrir o arquivo de texto no Excel. • Importar o arquivo de texto como um intervalo de dados externos. Você pode importar até 1.048.576 linhas e 16.384 colunas. 4.1.1 Importando arquivo de texto e abrindo-o Você pode converter um arquivo de texto criado em outro programa em uma pasta de trabalho do Excel, usando o comando Abrir. 1. Clique no botão Offi ce e, em seguida, em Abrir. 2. Na caixa Arquivos do tipo, selecione Arquivos de texto. 3. Localize o arquivo de texto que deseja abrir e clique duas vezes nele. 41 Figura 47 Figura 48 Se o arquivo de texto for (.txt), o Excel iniciará o Assistente de importação de texto. 42 4. Se os itens do arquivo de texto estiverem separados por guias, dois-pontos, ponto e vírgula, espaços ou outros caracteres, selecione Delimitado, na caixa Tipo de dados originais. Se todos os itens do arquivo de texto tiverem o mesmo tamanho, selecione Largura fi xa. 5. Digite ou selecione um número de linha para especifi car a primeira linha dos dados que deseja importar na caixa Iniciar importação na linha. Figura 49 Figura 50 Figura 51 6. Na caixa Origem do arquivo, selecione o conjunto de caracteres usado no arquivo de texto. Na maioria dos casos, você pode deixar a confi guração Windows (ANSI) como padrão. 7. A caixa de diálogo Visualização do arquivo mostra como o texto será exibido, quando separado em colunas na planilha. 43 Figura 52 Figura 53 8. Após selecionar as confi gurações, clique em Avançar. 9. A próxima tela será visualizada conforme mostra a fi gura 52. 10. Na caixa Delimitadores, selecione o caractere que separa os dados no arquivo de texto. Se o caractere não estiver listado, marque a caixa de seleção Outros. Em seguida, digite um caractere na caixa que contém o cursor. Essas opções não se encontrarão disponíveis, se o tipo de dados for Largura fi xa. 44 11. Selecione a opção Considerar delimitadores consecutivos como um só, se os dados contiverem um delimitador de mais de um caractere entre os campos de dados, ou se contiverem vários delimitadores personalizados. Figura 54 Figura 55 12. Qualifi cador de texto – Quando o Excel encontra o caractere qualifi cador de texto, todo o texto que segue esse caractere e vem antes da próxima ocorrência desse caractere é importado como um valor, mesmo que o texto contenha um caractere delimitador. Por exemplo, se o delimitador for uma vírgula (,) e o qualifi cador de texto forem aspas (“), “São Paulo, SP” será importado em uma célula como São Paulo, SP. Se nenhum qualifi cador ou se o qualifi cador apóstrofo (‘) for especifi cado, “Dallas, Texas” será importado em duas células adjacentes como “São Paulo e SP”. 13. Após selecionar as confi gurações, clique em Avançar. 14. A tela seguinte será visualizada conforme mostra a fi gura 56. 15. Na caixa Formato dos dados da coluna, selecione o formato dos dados da coluna selecionada na seção Visualização dos dados. Se você não desejar importar a coluna selecionada, clique em Não importar coluna (Ignorar). Essa escolha fará o Excel converter os dados importados corretamente. 45 Figura 56 Figura 57 16. Clique em Concluir. 17. Faça os ajustes desejados na planilha, para que os dados importados sejam visualizados adequadamente e salve a pasta de trabalho. Vamos praticar! Exercício 5 46 4.1.2 Importando arquivo de texto como intervalo de dados externos Para importar um arquivo de texto como um intervalo de dados externos, o procedimento difere da forma anteriormente vista apenas nos primeiros passos: 1. Abra a pasta de trabalho e selecione a planilha que deverá receber os dados. 2. Na guia Dados, grupo Obter dados externos, clique no botão De Texto . 3. Localize o arquivo de texto que você deseja abrir e clique duas vezes nele. 4. Repita os passos de 4 a 17 vistos no tópico anterior. 4.2 Importando dados do Access Para importar dados do Access, faça o seguinte: 1. Na faixa de opções Dados, grupo Obter dados externos, clique no botão Do Access . 2. Na caixa de diálogo Selecionar fonte de dados, localize o arquivo do Access, selecione-o e clique no botão Abrir. Vamos usar o banco de dados Northwind 2007, que está gravado na pasta Exemplos_curso. 47 Figura 58 Figura 59 3. Em seguida, selecione a tabela Análise de Vendas e clique em OK. 48 4. Selecione o método como os dados deverão ser exibidos. Em nosso exemplo, será o modo Tabela. Figura 60 Figura 61 5. Informe onde você deseja armazenar seus dados. Em nosso exemplo, será Na nova planilha. 49 Figura 62 6. Clicando no botão Propriedades, você pode informar o tempo em que o Excel deverá verifi car se há novas atualizações na tabela, bem como formato de conexão, endereço de conexão, idioma. 7. Escolha sua confi guração e clique no botão OK. 50 8. Na caixa de diálogo Importar dados, clique no botão OK, para que a importação seja concluída. Figura 63 4.3 Importando dados de uma página Web Faça uma consulta à Web para recuperar dados atualizáveis armazenados em sua Intranet ou na Internet como uma única tabela, várias tabelas ou todo o texto de uma página da Web. Em seguida, analise os dados usando as ferramentas e os recursos do Excel. Por exemplo, você pode recuperar e atualizar cotações de ações de uma página pública da Web, ou recuperar e atualizar uma tabela de informações de vendas da página de uma empresa na Web. Consultas à Web são especialmente úteis para recuperação de dados em tabelas ou áreas pré-formatadas. Para criar ou editar uma consulta à Web, faça o seguinte: 1. Na guia Dados, no grupo Obter dados externos, clique em Da Web . 51 Figura 64 Figura 65 Ao ser exibida a caixa de diálogo Nova consulta à Web, insira a URL (endereço que especifi ca um protocolo, como HTTP ou FTP, e a localização de um objeto ou documento de que vocêdeseja obter dados). É possível digitar a URL, colá-la de um endereço copiado ou clicar na seta próxima à lista Endereço e selecionar um endereço utilizado recentemente. O comprimento máximo de uma URL é de 255 caracteres. 2. Clique em Ir. 3. Clicando no botão Opções, você pode defi nir a formatação e as confi gurações de importação. 52 4. Clique no botão , ao lado da tabela que você deseja importar, ou clique no botão , no canto superior esquerdo da página, para importar a página toda. 5. Em seguida, clique no botão Importar. Figura 66 4.3 Atualizando dados importados Para atualizar dados importados, abra a planilha desejada e clique na guia Dados, grupo Conexões, e no botão Atualizar. 5. Cenários Muitas vezes, há ocasiões que nos obrigam a fazer uma projeção de situações futuras para direcionar nossas decisões com a menor margem de erro possível. A utilização das planilhas eletrônicas nos permite visualizar cenários que podem se realizar ou não. Portanto, são hipóteses de acordo com os elementos que fornecemos para alimentar determinada situação. Para criar um cenário, primeiramente, monte a planilha com todas as fórmulas e as células que contêm os valores variáveis. As que conterão valores diferentes para a mesma 53 Figura 67 Figura 68 célula serão alimentadas pelo conjunto de cenários. A fi gura 67 mostra os valores relativos ao mês de janeiro de cada um dos três países no intervalo B8:B10. Para os meses de fevereiro a maio, será utilizada uma fórmula que multiplica o valor de janeiro pelo índice de cada mês que está no intervalo B2:B5. Por exemplo, a fórmula do primeiro valor de fevereiro é =$B8*B$2. A empresa tem diversas previsões sobre o aumento ou diminuição dos valores desses meses. Para visualizar a situação de acordo com as diversas hipóteses, criamos um conjunto de cenários. Para criar um cenário, faça o seguinte: 1. Abra a pasta de trabalho Exemplos e selecione a planilha Cenario. 2. Selecione as células variáveis do cenário (B2:B5). 3. Na guia Dados, grupo Ferramentas de Dados, clique sobre a opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários. 54 4. Na caixa Gerenciador de Cenários, clique no botão Adicionar. Figura 69 Figura 70 5. Dê um nome para o cenário, por exemplo, Baixo, e defi na quais serão as células variáveis. Automaticamente, é exibido o endereço da seleção atual. Se desejar fazer algum Comentário, utilize a caixa correspondente. 55 Figura 71 Figura 72 8. Para criar um novo cenário, por exemplo, Alto, siga os passos 4, 5 e 6 e defi na os seguintes valores na caixa Valores de cenário: $B$2=1,8; $B$3=1,9; $B$4=2 E $B$5=2,1. 9. Para atualizar os valores nas células variáveis, clique sobre o cenário desejado e, a seguir, sobre o botão Mostrar. O item Proteção permite que o cenário não seja alterado ou visualizado, caso a planilha esteja protegida. 6. Defi nidas as opções, clique em OK. 7. Será exibida a janela Valores de cenário com os valores das células selecionadas. Clique em OK. 56 Na fi gura 73, foi aplicado o índice utilizando cenário Baixo para exibir os resultados. Já na fi gura 74, foi aplicado o índice Alto. Figura 73 Figura 74 57 Figura 75 5.1 Editando um cenário Para modifi car um cenário, faça o seguinte: 1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários. 2. Clique no nome do cenário a ser alterado e, a seguir, em Editar. 3. Faça as alterações necessárias e clique em OK para alterar os valores. 58 5.2 Criando um relatório de cenário Para criar um relatório de cenário, faça o seguinte: 1.Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários. 2. Clique no botão Resumir. Figura 76 3. A caixa de diálogo Resumo do cenário exibe duas opções. Selecione Resumo do cenário para criar um relatório especifi cando os valores atuais das células variáveis e os valores das células de resultado, que dependem das células variáveis. Em nosso exemplo, as células de resultado são as referentes aos valores dos meses de fevereiro a maio. Em Células de resultado, especifi que as células que deseja representar no relatório e clique em OK. 59 Figura 77 Figura 78 Na fi gura 78, veja o exemplo do relatório Resumo do cenário. 60 4. Se você selecionar Tabela Dinâmica do Cenário, uma tabela dinâmica será criada em uma nova planilha, baseada nos dados dos cenários existentes na planilha atual. Na fi gura 79, veja o exemplo do relatório Tabela Dinâmica do Cenário. Figura 79 Figura 80 5.3 Exclusão de um cenário Para excluir um cenário, tenha certeza de que deseja fazê-lo, pois, a partir do momento que excluí-lo, ele não poderá ser recuperado. 1. Na guia Dados, grupo Ferramentas de Dados, clique na opção Teste de Hipóteses e, em seguida, em Gerenciador de Cenários. 2. Selecione o cenário desejado e clique em Excluir. Vamos praticar! Exercício 6 61 6. Segurança das informações Dependendo do grau de automação de um modelo de planilha, você pode desejar que os usuários não alterem nenhuma parte dela, ou tenham acesso a somente algumas de suas áreas exclusivamente para entrada de dados, não permitindo que sejam alterados rótulos ou fórmulas de cálculo. A seguir, apresentamos alguns níveis de proteção do Excel: • Proteger Planilha – Feita planilha por planilha, essa proteção permite alterar apenas o conteúdo das células que não estiverem travadas. • Permitir que Usuários Editem Intervalos – Permite designar usuários, computadores da rede ou grupos que possam realizar alterações em células específi cas sem digitar uma senha. • Proteger Pasta de Trabalho – Esse nível de proteção aplica-se à estrutura e à janela da pasta. Ao proteger a estrutura, torna-se impossível inserir ou excluir, ocultar ou reexibir, alterar o nome ou modifi car a disposição das planilhas na pasta de trabalho. Quando protegemos a janela, o tamanho e a posição da janela do arquivo fi cam inalterados. • Proteger e Compartilhar Pasta de Trabalho – Quando uma pasta é compartilhada, ou seja, quando é aberta por vários usuários por meio da rede e se quer controlar as alterações feitas por cada usuário, é importante inserir uma senha, sem a qual não é possível remover o histórico do controle de alterações ou do uso compartilhado. 6.1 Protegendo toda a planilha 1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger. Vamos proteger a planilha Cenário-pronta. 2. Na guia Revisão, grupo Alterações, clique em Proteger Planilha . 3. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados na planilha. 62 Figura 81 4. Digite uma senha de proteção com até 255 caracteres, podendo conter letras, números e símbolos. Ao clicar em OK, será aberta uma caixa para reinserir a senha de proteção. Cuidado Se esquecer a senha, não há como recuperá-la. 63 Figura 82 Figura 83 6.2 Protegendo partes da planilha 1. Abra a pasta de trabalho Exemplos e selecione a planilha que deseja proteger. Vamos proteger a planilha Nome-pronta. 2. Selecione as células que não deseja proteger (liberadas para a entrada de dados). Em nosso exemplo, será a célula B12. 3. Clique com o botão direito do mouse na célula e, em seguida, na opção Formatar células. 64 4. Ative a guia Proteção, desmarque a opção Bloqueadas e clique em OK. 5. Na guia Revisão, grupo Alterações, clique em Proteger Planilha . 6. Na caixa de diálogo que se abre, selecione os itens que não devem ser alterados na planilha. 7. Digite uma senha de proteção. 8. Agora, faça alguns testes e veja que apenas a célula B12 pode ser alterada. 9. Salve a pasta de trabalho.65 Figura 85 Figura 86 6.3 Protegendo uma pasta de trabalho 1. Abra a pasta de trabalho que deseja proteger. 2. Na guia Revisão, grupo Alterações, clique em Proteger Pasta de Trabalho . 3. Na caixa de diálogo Proteger Estruturas e Janelas, selecione as opções desejadas para bloquear: • Estrutura – Movimentação, exclusão e inserção de planilhas • Janelas – Botões minimizar, maximizar e restaurar 4. Digite uma senha e, em seguida, clique em OK. A pasta de trabalho estará protegida por senha contra alterações. 5. Clique no botão Offi ce e em Salvar como. 6. Na caixa de diálogo Salvar como, clique no botão Ferramentas e, e em seguida, escolha Opções gerais. 66 7. Na área Compartilhamento de arquivos, entre com as senhas de proteção e gravação. Habilite também a opção Recomendável somente leitura e clique em OK. Figura 87 8. Salve a pasta de trabalho. 6.4 Desprotegendo células e planilhas Para desproteger células ou planilhas, faça o seguinte: 1. Selecione a célula ou planilha que deseja desproteger. 2. Na guia Revisão, grupo Alterações, clique em Desproteger Planilha. 7. Validação Em muitas planilhas criadas, os usuários digitam dados para obter os resultados desejados. Por essa razão, assegurar a entrada de dados válidos é uma tarefa importante. Por exemplo, convém restringir a entrada de dados a um determinado intervalo de datas e a escolhas usando uma lista limitada, ou certifi car-se de que apenas números inteiros positivos sejam digitados. Fornecer ajuda imediata para orientar os usuários e mensagens claras quando dados inválidos forem digitados também é essencial para permitir que a entrada de dados aconteça de forma adequada. 67 Figura 88 7.1 Criando uma validação de dados Uma vez decidida que validação você deseja usar em uma planilha, confi gure-a, adotando o seguinte procedimento: 1. Abra a pasta de trabalho Exemplos e selecione a planilha Vendas. 2. Selecione uma ou mais células para validar. Vamos validar o intervalo B3:M23. 3. Na guia Dados, no grupo Ferramentas de Dados, clique em Validação de Dados. 68 4. Na guia Confi gurações, caixa Permitir, selecione a opção mais adequada. Por exemplo, você pode selecionar Lista para limitar respostas a perguntas como: • Mensalidade paga? Sim ou Não. • A quantidade vendida foi: Baixa, Média, Alta. Figura 89 Figura 90 Figura 91 5. Clique na caixa Fonte e, em seguida, digite os valores da lista separados por ponto e vírgula. 6. Em seguida, selecione a célula B3 e clique na seta para abrir a lista. 69 Figura 92 A largura da lista suspensa é determinada pela largura da célula que tem a validação de dados. Talvez seja necessário ajustar essa largura para impedir que sejam truncadas entradas válidas maiores do que a largura da lista suspensa. Você também pode criar uma lista de valores a partir de um intervalo de células. • Em Fonte, clique no botão que esconde temporariamente a janela. • Selecione o intervalo de células que deseja exibir na lista. Vejamos outros exemplos: Para limitar a entrada a um número inteiro 1. Na caixa Permitir, selecione Número Inteiro. 2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para defi nir limite superior e inferior, selecione está entre. 3. Insira o valor mínimo, máximo ou específi co a ser permitido (fi gura 95). Você também pode inserir uma fórmula que retorne um valor numérico. Por exemplo, para defi nir um limite mínimo de deduções para duas vezes o número de fi lhos na célula F1, selecione maior ou igual a na caixa Dados e digite a fórmula =2*F1 na caixa Mínimo. 70 Para limitar a entrada a um número decimal 1. Na caixa Permitir, selecione Decimal. 2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para defi nir limite superior e inferior, selecione entre. 3. Insira o valor mínimo, máximo ou específi co a ser permitido. Você também pode inserir uma fórmula que retorne um valor numérico. Por exemplo, para defi nir um limite máximo para comissões e bônus de 6% do salário de um vendedor na célula E1, selecione menor ou igual a na caixa Dados e digite a fórmula =E1*6% na caixa Máximo. Para permitir que um usuário digite porcentagens, por exemplo, 20%, selecione Decimal na caixa Permitir, selecione o tipo de restrição desejado na caixa Dados, digite o mínimo, o máximo ou um valor específi co como um decimal, por exemplo, 0,2. Para limitar a entrada a um período de tempo (data) 1. Na caixa Permitir, selecione Data. 2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir datas após um determinado dia, selecione maior que. 3. Insira a data inicial, fi nal ou uma data específi ca a ser permitida. Você também pode inserir uma fórmula que retorne um valor de data. Por exemplo, para defi nir um período de tempo entre a data de hoje e três dias da data de hoje, selecione entre na caixa Dados, digite =HOJE() na caixa Mínimo e digite =HOJE()+3 na caixa Máximo. Para limitar a entrada a um intervalo de tempo (hora) 1. Na caixa Permitir, selecione Hora. 2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir horas antes de uma determinada hora do dia, selecione menor que. 3. Insira a hora inicial, fi nal ou uma hora específi ca a ser permitida. Você também pode inserir uma fórmula que retorne um valor de hora. 71 Por exemplo, para defi nir determinado período para servir o café da manhã entre a hora de abertura do restaurante, na célula H1, e cinco horas depois que o restaurante abrir, selecione entre na caixa Dados, digite =H1 na caixa Mínimo e, em seguida, digite =H1+”5:00” na caixa Máximo. Para limitar a entrada a um texto de comprimento especifi cado 1. Na caixa Permitir, selecione Comprimento do Texto. 2. Na caixa Dados, selecione o tipo de restrição desejado. Por exemplo, para permitir até um determinado número de caracteres, selecione menor que ou igual a. 3. Insira o valor mínimo, máximo ou um comprimento específi co para o texto. Você também pode inserir uma fórmula que retorne um valor numérico. Por exemplo, para defi nir que o comprimento específi co de um campo de nome completo (C1) seja o comprimento atual de um campo de primeiro nome (A1) e de um campo de sobrenome (B1) mais 10, selecione menor ou igual a na caixa Dados e digite =SOMA(NÚM.CARACT(A1),NÚM.CARACT(B1),10) na caixa Máximo. Para calcular o que é permitido com base no conteúdo de outra célula 1. Na caixa Permitir, selecione o tipo de dado desejado. 2. Na caixa Dados, selecione o tipo de restrição desejado. 3. Na caixa ou caixas abaixo da caixa Dados, clique na célula que você deseja usar para especifi car o que é permitido. Por exemplo, para permitir entradas em uma conta somente se o resultado não exceder o orçamento, na célula E4, selecione Decimal em Permitir, menor que ou igual a em Dados e, na caixa Máximo, digite =E4. Usar uma fórmula para calcular o que é permitido 1. Na caixa Permitir, selecione Personalizado. 2. Na caixa Fórmula, insira uma fórmula que calcule um valor lógico (VERDADEIRO para entradas válidas ou FALSO para inválidas). Por exemplo: 72 Para assegurar que Insira esta fórmula A célula da conta do piquenique (B1) possa ser atualizada apenas se nada estiver orçado para a conta sem restrições (D1) e o orçamento total (D2) for menor do que os R$ 40.000 alocados. =E(D1=0,D2<40000) A célula que contém uma descrição do produto (B2) contenha apenas texto. =ÉTEXTO(B2) Para a célula que contém um orçamento publicitário projetado (B3), o subtotal para subcontratantes e serviços (E1) seja menor ou igual a R$ 800 e a quantia total do orçamento (E2) também seja menor ou igual a R$ 97.000. =E(E1<=800,E2<=97000)A célula que contém a idade de um empregado (B4) seja sempre maior do que o número de anos no emprego (F1) mais 18 (a idade mínima para contratação). =SE(B4>F1+18,VERDADEIRO,FALSO) Todos os dados no intervalo de células A1:A20 contenham valores únicos. =CONT.SE($A$1:$A$20,A1)=1 Você deve digitar a fórmula na validação de dados para a célula A1 e, em seguida, preencher as células A2 a A20, de tal modo que a validação de dados para cada célula no intervalo tenha uma fórmula similar, mas o segundo argumento para a função CONT.SE equivalha à célula atual. A célula que contém um nome de código de produto (B5) comece sempre com o prefi xo padrão ID- e tenha, no mínimo, dez caracteres de comprimento. =E(ESQUERDA(B5, 3) ="ID-",NÚM.CARACT(B5) > 9) 73 Valores Nulo Para especifi car como você deseja lidar com valores em branco (nulos), marque ou desmarque a caixa de seleção Ignorar em branco. Se os seus valores permitidos forem baseados em um intervalo de células com um nome defi nido e houver uma célula em branco em qualquer parte do intervalo, a defi nição da caixa de seleção Ignorar em branco permitirá que qualquer valor seja inserido na célula validada. Isso também é válido para qualquer célula referenciada por fórmulas de validação: se qualquer célula referenciada estiver em branco, a defi nição da caixa de seleção Ignorar em branco permitirá que qualquer valor seja inserido na célula validada. 7.2 Inserindo uma mensagem de entrada É possível inserir uma mensagem para auxiliar o usuário no preenchimento da célula. Para isso, faça o seguinte: 1. Clique na guia Mensagem de entrada. 2. Certifi que-se de que a caixa de seleção Mostrar mensagem de entrada ao selecionar célula esteja marcada. 3. Preencha o título e o texto da mensagem de entrada e clique em OK. Figura 93 74 4. Clique na seta e observe a mensagem. Figura 94 7.3 Inserindo um alerta de erro Além da mensagem para auxiliar o usuário a preencher os dados, você pode mostrar uma mensagem de erro, alertando-o sobre o que está ocorrendo e ainda selecionar o ícone que ele visualizará. Para isso, faça o seguinte: 1. Clique na guia Alerta de Erro e certifi que-se de que a caixa de seleção Mostrar alerta de erro após a inserção de dados inválidos esteja marcada. 2. Selecione uma destas opções para a caixa Estilo: • Para exibir uma mensagem informativa que não impeça a entrada de dados inválidos, selecione Informações. • Para exibir uma mensagem de aviso que não impeça a entrada de dados inválidos, selecione Aviso. • Para impedir a entrada de dados inválidos, selecione Parar. 3. Digite o título e a mensagem de erro a ser visualizada em caso de dados inválidos (erro) e clique em OK (fi gura 95). Se você não inserir um título ou texto, o título usará o padrão "Microsoft Excel" e a 75 Figura 95 Figura 96 mensagem usará o padrão seguinte: "O valor inserido não é válido. Outro usuário restringiu valores que podem ser inseridos nesta célula." 4. Ao digitar dados inválidos, uma caixa de diálogo será exibida em conformidade com a opção selecionada em Estilo. No exemplo anterior (Aviso), há a possibilidade de manter o valor ou rejeitá-lo. Dica Se você alterar a validação em uma célula, será possível aplicar, automaticamente, suas alterações em todas as outras células que têm as mesmas confi gurações. Vamos praticar! Exercício 8 76 8. Usando auditoria em células Ao copiar uma fórmula de uma célula para outra, você pode não perceber que está gerando dados errados. Muitas células podem conter a mesma fórmula e, como elas mostram sempre o resultado, é difícil visualizar quais delas possuem apenas números e quais são calculadas a partir de outros dados. Caso você faça uma alteração temporária e substitua uma fórmula por um número, esse dado pode se tornar desatualizado. Possivelmente, você não se dará conta dessa alteração, até ser questionado sobre o resultado. Para evitar inconsistências em suas planilhas, o Excel possui ferramentas de auditoria. Elas se destinam a verifi car como estão distribuídas as dependências entre as células, e analisar possíveis erros de cálculos e entrada de dados. 8.1 Janela de inspeção Quando as células estão ocultas em uma planilha, você pode inspecionar as fórmulas e seus resultados por meio da barra de ferramentas Janela de inspeção. A barra de ferramentas Janela de inspeção torna conveniente inspecionar, auditar ou confi rmar os cálculos de uma fórmula e os resultados em uma planilha extensa, pois, ao utilizá-la, não é necessário navegar várias vezes para diferentes partes da planilha. É permitida somente uma inspeção por célula. 8.1.1 Adicionando células à janela de inspeção 1. Selecione as células que você deseja inspecionar. 2. Para selecionar todas as células com fórmulas, na guia Início, no grupo Edição, clique em Localizar e Substituir, clique em Ir para Especial e, em seguida, clique em Fórmulas. 3. Na guia Fórmulas, no grupo Auditoria de fórmulas, clique em Janela de inspeção . 77 Figura 97 Figura 98 4. Clique em Adicionar inspeção de variáveis . 5. Clique em Adicionar. 6. Mova a barra de ferramentas Janela de inspeção para a parte superior, inferior, lado esquerdo ou direito da janela. 7. Para alterar a largura de uma coluna, arraste o limite no lado direito do título da coluna. 8. Para exibir a célula à qual uma entrada da barra de ferramentas Janela de inspeção se refere, clique duas vezes na entrada. As células que têm referências externas (referência a uma célula ou a um intervalo em uma planilha de outra pasta de trabalho) são exibidas na barra de ferramentas Janela de inspeção, somente quando a outra pasta de trabalho está aberta. 78 Figura 99 8.2 Realizando auditoria em uma planilha Às vezes, verifi car a precisão de uma fórmula ou localizar a origem de um erro pode ser difícil, quando ela usa células precedentes ou dependentes. Células precedentes – São células que fornecem valores a outras células. Por exemplo, se a célula D10 contiver a fórmula =B5*3, a célula B5 será um precedente da célula D10. Células dependentes – São células afetadas pelo valor de outras células. Por exemplo, se a célula D10 contiver a fórmula =B5*3, o valor da célula D10 será dependente da célula B5. Para ajudá-lo a verifi car as fórmulas, exibindo grafi camente as relações entre células e fórmulas com setas de rastreamento, você pode usar os botões Rastrear precedentes e Rastrear dependentes . As setas rastreadoras são: • Azuis – Quando apontam de uma célula que fornece dados para outra célula. • Vermelhas – Quando uma célula contém um valor de erro, como #DIV/0! • Pretas – Quando uma célula, em outra planilha ou pasta de trabalho, fi zer referência à célula selecionada, uma seta preta apontará da célula selecionada para um ícone de planilha . Caso as setas não sejam visualizadas, faça o seguinte: 1. Clique no botão Offi ce , clique em Opções do Excel e, em seguida, clique na categoria Avançado. 2. Na seção Exibir opções para esta pasta de trabalho, verifi que se a opção Tudo está selecionada em Para objetos, mostrar. 79 Figura 100 Figura 101 Se as fórmulas fi zerem referência à outra pasta de trabalho, ela deverá estar aberta, para que o Excel possa rastrear as células. 8.2.1 Rastreando células precedentes ou dependentes Para rastrear células precedentes ou dependentes, faça o seguinte: 1. Selecione a célula que contém a fórmula para a qual você deseja localizar as células precedentes ou dependentes. 2. Na guia Fórmulas, grupo Auditoria de fórmulas, clique em Rastrear precedentes ou em Rastrear dependentes . No exemplo da fi gura 101, a célula C3 tem como precedente a célula A3 e como dependente a célulaA4. 80 3. Para identifi car o próximo nível de células que fornecem dados para a célula ativa, clique em Rastrear precedentes novamente. 8.2.2 Removendo o rastreamento Para remover as setas de rastreamento, faça o seguinte: 1. Na guia Fórmulas, grupo Auditoria de fórmula, clique na seta do botão Remover Setas e, em seguida, selecione uma das opções: Figura 102 9. Personalizando a área de trabalho 9.1 Criando barra de ferramentas Dentre as várias ferramentas que o Excel possui, algumas fi cam visíveis, outras não. Para adicionar mais recursos à sua área de trabalho, faça o seguinte: 1. Clique com o botão direito em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido. 2. Clique em Personalizar e, na lista Escolher comandos em, selecione o grupo de comando Guia Suplementos e procure por Barra de Ferramentas Personalizadas. 3. Clique no botão Adicionar. 81 Figura 103 Figura 104 4. Em seguida, clique no botão OK. 5. A barra será exibida no canto superior esquerdo da janela: 82 9.2 Excluindo barra de ferramentas 1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido. 2. Selecione a barra de ferramentas desejada e clique no botão Remover. 9.3 Criando menu de comandos 1. Clique com o botão direito do mouse em uma das guias da faixa de opções e, em seguida, clique na opção Personalizar Barra de Ferramentas de Acesso Rápido. 2. Em Escolher comandos em, selecione Guia Suplementos. 3. Clique na opção Comandos de Menu e no botão Adicionar. 10. Construindo um grupo de trabalho Esse recurso permite visualizar, ao mesmo tempo, várias planilhas relacionadas e compartilhar elementos entre as pastas de trabalho e entre suas respectivas planilhas, facilitando a criação de vínculos e a consolidação de dados. Também possibilita grande economia de tempo na localização dos arquivos e no início dos trabalhos. 10.1 Salvando um grupo de pastas de trabalho personalizadas 1. Abra as pastas de trabalho que você deseja abrir como um grupo. 2. Dimensione e posicione as janelas das pastas de trabalho como deseja exibi-las na próxima vez em que forem abertas. 83 Figura 105 3. Clique na guia Exibição, grupo Janela e em Salvar Espaço de Trabalho. 4. Escolha o nome e o local do arquivo e clique no botão Salvar. 11. Solucionado Problemas Você já se deparou com um problema que apresenta diversas soluções possíveis? Como saber qual é a melhor? Se ele envolver apenas uma variável, é simples. Já se houver mais de uma variável, a questão pode tornar-se complexa. O Excel dispõe de excelentes ferramentas capazes de auxiliar na solução de questões complexas. Antes de utilizá-las, é necessário saber que tipo de problema você precisa resolver para, então, escolher que ferramenta utilizar. 11.1 Problemas com uma variável Esse tipo de problema é bastante simples de ser solucionado. O que é um problema com uma variável? É aquele em que precisamos defi nir o valor de apenas um elemento para gerar o resultado de outro elemento. 84 Por exemplo, a direção de uma empresa precisa saber quanto poderá pagar de aluguel, de forma que, somando todas as despesas, esse valor não seja superior à receita. Para isso, criou uma planilha com todos os gastos e receitas do mês e montou o problema para o Excel resolver, como mostra a fi gura seguinte. Figura 106 Primeiramente, vamos entender o problema. É necessário saber que célula deverá variar. Célula variável é aquela que precisa ter um valor a ser defi nido. Nesse caso, é o valor do aluguel (célula B8). Outro elemento a identifi car é qual será a célula de destino, que terá seu valor modifi cado de acordo com a célula variável. Portanto, em sua fórmula, a célula de destino contém a referência da célula variável. Nesse caso, o total da despesa (célula B9) é a célula de destino, pois esse valor depende do valor do aluguel para ser calculado. Identifi cados esses dois elementos, podemos cuidar da solução do problema. Como esse é um problema com uma variável (o valor do aluguel), utilizamos o recurso denomina Atingir meta. 1. Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, em Atingir meta. 85 Figura 107 Figura 108 Figura 109 2. Na caixa Defi nir célula, insira a referência para a célula que contém a fórmula que você deseja resolver. No exemplo, essa é a célula B9. 3. Na caixa Para valor, digite o resultado desejado. No exemplo, esse valor é 7185. 86 4. Na caixa Alternando célula, insira a referência para a célula que contém o volume que deseja ajustar. No exemplo, essa é a célula B8. Figura 110 Figura 111 5. Essa célula deve ser referenciada pela fórmula na célula que você especifi cou na caixa Defi nir célula. 6. Clique em OK. 7. A janela que apareceu informa se houve ou não alteração. 87 Figura 112 O resultado é o seguinte: 11.2 Problemas com mais de uma variável Agora, vamos analisar problemas que contêm mais de uma variável. Esses problemas tanto podem ser de simples resolução, como podem apresentar elevado grau de complexidade. As questões que envolvem mais de uma variável são aquelas em que há diversos fatores a serem defi nidos para gerar um resultado. No problema anterior, para encontrar um valor para a despesa total, havia diversas variáveis (despesas). Entretanto, somente uma variável não havia sido defi nida: o valor do aluguel. E se for necessário saber qual o valor máximo de cada uma das despesas? Nesse caso, teremos um problema com mais de uma variável, ou seja, todas as despesas. Para solucionar problemas dessa natureza, o Excel dispõe de excelente recurso chamado Solver. 88 O Solver Add-in é um programa de suplemento do Excel, isto é, programa que adiciona comandos ou recursos personalizados ao Microsoft Offi ce. Contudo, para usá-lo, é preciso primeiro carregá-lo. Para isso, faça o seguinte: 1. Clique no botão Offi ce e, em seguida, em Opções do Excel. 2. Clique em Suplementos e, na caixa Gerenciar, selecione Suplementos do Excel. 3. Clique em Ir para. 4. Na caixa Suplementos disponíveis, marque a caixa de seleção Solver Add-in e clique em OK. Dica Se o Solver Add-in não estiver listado na caixa Suplementos disponíveis, clique em Procurar para localizá-lo. Se você for informado de que o Solver Add-in não está atualmente instalado no computador, clique em Sim para instalá-lo. Depois de carregar o Solver Add-in, o botão Solver torna-se disponível no grupo Análise, na guia Dados. Imagine que você tenha que distribuir uma verba orçamentária para montar um escritório e precise saber a quantidade que poderá comprar de cada item. Primeiramente, há certas exigências básicas que devem ser atendidas: será necessário adquirir, exatamente, 15 computadores e, para cada computador, serão necessárias 2 cadeiras; para cada 5 computadores, haverá pelo menos uma impressora e, para cada uma delas, pelo menos 3 mesas. O valor máximo do orçamento é R$ 50.000,00. Veja que esse problema envolve diversas variáveis e, para cada uma, há uma restrição a ser atendida. O recurso Solver nos permite determinar restrições para as variáveis de um problema, constituindo excelente recurso para a solução de questões, uma vez que, por meio de fórmulas apenas, o processo poderia se tornar lento e cansativo. 89 Após fazer a pesquisa de preços, vamos montar a planilha. Figura 113 Veja que, na célula B9, há o total do orçamento, defi nido como 50.000,00. Agora, precisamos saber qual é a célula de destino, ou seja, uma célula que contém uma fórmula e que precisa ser defi nida de acordo com as células variáveis. Como temosum valor máximo a ser empregado, a célula de destino é exatamente o valor total a ser gasto na compra, que é a célula com a soma geral, cujo endereço é D6. As células variáveis são as quantidades a serem defi nidas do intervalo C2:C5. Elaborada a planilha com as devidas fórmulas, podemos recorrer ao Solver para solucionar o problema. 1. Clique na guia Dados, no grupo Análise e no botão Solver. 2. A primeira opção a defi nir na caixa Parâmetros do Solver é a célula de destino. Nesse caso, é D6, que é o total a ser gasto. Podemos defi nir três opções de valor para essa célula: • Máx – Encontra o maior valor possível para a célula de destino de acordo com as restrições. Essa é a opção de nosso problema, pois precisamos encontrar o valor mais próximo da verba disponível. 90 • Mín – Encontra o menor valor possível para a célula de destino, sempre de acordo com as restrições impostas. Minimizamos quando precisamos reduzir ao máximo o valor de uma célula. Por exemplo, quando é necessário cortar despesas. • Valor de – Permite especifi car um valor exato para a célula de destino, ou seja, quando as células variáveis precisam produzir um valor exato. Por exemplo, se você quiser saber quanto precisa economizar por mês para ter exatamente R$ 10.000,00 no fi nal do ano. 3. Células variáveis são as células que serão modifi cadas para gerar o valor defi nido para a célula de destino, que, nesse caso, são as quantidades C2:C5. Se clicarmos em Estimar, o Solver sugere as células variáveis de acordo com a fórmula da célula de destino. Pode haver até 200 células variáveis por problema. 4. Para especifi car as restrições, ou seja, colocar as condições para as células variáveis, clique em Adicionar para abrir a caixa Adicionar restrição. 5. Nossa primeira restrição será especifi car que o número de computadores deve ser 15. Então, a referência de célula será C3. E a restrição deverá ser igual a B16, que contém o número de computadores a serem adquiridos. Veja a fi gura a seguir: Figura 114 6. Após inserir uma restrição, clique em Adicionar para acrescentar mais restrições. • A segunda restrição é C5=C3*B15. A quantidade de cadeiras (C5) tem que ser igual ao número de computadores (C3) vezes 2 (B15). • A próxima é C2>=C3/B14, ou seja, a quantidade de impressoras (C2) deve ser maior ou igual ao número de computadores (C3) dividido por 5 (B14). • A outra restrição é C4>=C2*B13. O número de mesas (C4) será maior ou igual ao número de impressoras (C2) vezes 3 (B13). • Determinamos também que o valor total (D6) tem que ser menor ou igual ao total do orçamento (B9) pela expressão D6<=B9. 91 Figura 115 7. Após inserir a última restrição, clique em OK para retornar para a caixa Parâmetros do Solver. Todas as restrições serão listadas em Submeter às restrições. 8. Quando tudo estiver confi gurado, clique em Resolver. 9. A caixa Resultados do Solver será exibida. Se for encontrada uma solução cujas condições foram atendidas, signifi ca que houve sucesso no processo de solução. A partir desse ponto, há duas opções: • Manter solução do Solver – Mantém os valores das células variáveis encontrados pelo Solver. Confi rma-se o resultado. • Restaurar valores originais – As células permanecem com os valores existentes antes de iniciar o Solver. Nada é modifi cado. 92 Figura 116 Na fi gura 116, veja que o Solver conseguir otimizar ao máximo a distribuição do orçamento de 50.000,00, atendendo a todas as restrições. Mas como comprar 12,8636 impressoras ou 38,59 mesas? 10. Nesse caso, clique em Restaurar valores originais. Para solucionar o problema empregando o valor máximo do orçamento de 50.000,00, foi necessário fracionar os números. Nesse caso, precisamos inserir mais uma restrição: os valores de todas as células variáveis têm que ser números inteiros. 11. Para inserir mais uma restrição, clique no botão Solver novamente. As confi gurações permanecem inalteradas, até que sejam modifi cadas. Como vamos acrescentar uma restrição, clique em Adicionar e insira a seguinte restrição: • C2:C5=número para determinar que o intervalo que contém os valores das quantidades deve ter números inteiros. Clique em OK e em Resolver. 93 Figura 117 Figura 118 12. Veja que todas as quantidades apresentam números inteiros e que todas as condições foram satisfeitas, gerando 49.950,00 como valor total. 11.3 Problemas sem solução Há problemas cuja solução não satisfaz todas as restrições. Por exemplo, se o orçamento for de 35.000,00, será possível montar o escritório? Vamos verifi car. Na célula B9, substituímos o valor 50.000,00 por 35.000,00 e inicializamos o Solver. Como a confi guração anterior é mantida, basta clicar em Resolver. 94 Após esgotar todas as tentativas para solucionar o problema, surge a caixa Resultados do Solver com a mensagem "Não foi possível ao Solver encontrar uma solução viável", signifi cando que a questão não foi solucionada. Figura 119 Com isso, concluímos que o valor mínimo para adquirir todos os itens é o valor atual da célula D6, ou seja, 39.150,00. Vamos praticar! Exercício 9 95 12. Funções Como já vimos nos cursos de Excel Básico e Excel Intermediário, desenvolver planilhas envolve o trabalho constante com fórmulas. Existem fórmulas em que, apenas por meio dos operadores matemáticos, podemos alcançar os resultados desejados. Porém, em muitos casos, esses recursos não são sufi cientes para produzir o resultado necessário. Em situações dessa natureza, devemos recorrer a um dos recursos mais empregados para a geração de resultados por meio de fórmulas: as funções. Você já aprendeu a trabalhar com algumas delas. Como você já sabe, as funções devem seguir uma sintaxe. Por ser uma fórmula, o primeiro caractere a ser inserido é o sinal de igual (=). Depois, vem o nome da função, seguido dos seus argumentos, que devem ser colocados entre parênteses e separados por ponto e vírgula. 12.1 Funções de texto Essa categoria contém funções que manipulam valores de texto. Há diversas aplicações para elas e seu entendimento é bastante fácil. Vamos nos lembrar do operador que une ou concatena valores de texto, utilizado quando se trabalha com textos em fórmulas: &. Quando desejar concatenar um texto que não está em uma célula, digite-o entre aspas. 12.1.1 Função ESQUERDA Essa função extrai caracteres a partir da esquerda até o número de caracteres especifi cado de um texto. Por exemplo, na palavra “Petróleo”, ao se extrair os três caracteres da esquerda, obtém-se “Pet”. A sintaxe é a seguinte: =ESQUERDA(texto;[núm_caract]), onde: • Texto – É a sequência de caracteres de texto que contém os caracteres que você deseja extrair. • Núm_caract – Especifi ca o número de caracteres que Esquerda deve extrair. Se for omitido, será considerado 1. 96 Veja o exemplo a seguir: Figura 120 Na fi gura 120, os códigos dos países foram formados pelos três primeiros caracteres do nome do país. A fórmula de C2 é a seguinte: =ESQUERDA(A2;3). 12.1.2 Função DIREITA Essa função extrai os últimos caracteres de uma sequência de texto. A sintaxe é a seguinte: =DIREITA(texto;[núm_caract]), onde: • Texto – É a sequência de caracteres de texto que contém os caracteres que você deseja extrair. • Núm_caract – Especifi ca o número de caracteres que Direita deve extrair. Se for omitido, será considerado 1. 97 Figura 121 Figura 122 Veja o exemplo a seguir: 12.1.3. Função MAIÚSCULA Como o próprio nome sugere, essa função converte todo o texto para letras maiúsculas. A sintaxe é a seguinte =MAIÚSCULA(texto), onde: • Texto – É o texto que se deseja converter para maiúsculas e que pode ser uma célula ou uma sequência de caracteres de texto. Veja o exemplo a seguir: 98 12.1.4. Função
Compartilhar