contábeis, excel e logistica, Resumos de Microsoft Excel

Baixe contábeis, excel e logistica e outras Resumos em PDF para Microsoft Excel, somente na Docsity!

EXCEL AVANÇADO 2

FILTRANDO E IMPORTANDO DADOS

Olá! Neste capítulo aprenderemos como utilizar o Excel para manipular bases (ou listas) de dados, utilizando ferramentas de filtro, seleção e classificação de registros. Também veremos como importar dados de fontes externas e como criar consultas personalizadas. Para tanto, alguns conceitos são necessários – além de certa familiaridade com os recursos básicos do Excel –, e estes conceitos são definidos a seguir.

Filtrar dados significa selecionar os mesmos de acordo com algum critério de interesse, por exemplo, um valor específico, um intervalo de valores, um ou mais atributos nominais, data etc. É através da filtragem que podemos encontrar as informações que necessitamos, em meio a um universo de milhares de registros. É o caso de selecionar apenas habitantes de Santa Catarina em uma base de dados nacional, por exemplo.

Classificar dados/registros é um conceito que já foi definido na primeira etapa deste material. Relembrando sucintamente: classificar dados significa manipular a ordem em que os mesmos são dispostos na tabela, de forma a obter-se uma estrutura desejada (ordem alfabética, por exemplo). Essa classificação normalmente é dividida em três grandes grupos: texto, numérica ou de data. É o caso de organizar uma lista em ordem alfabética, por exemplo.

Importar dados, no contexto deste material, significa “abrir” bases de dados que não são originalmente nativas do Excel, para poder manipulá- los da mesma forma que um arquivo nativo. Isso quer dizer que os dados não necessariamente foram digitados/gerados em uma planilha Excel, mas a sua importação permite que novos dados sejam adicionados, removidos e operacionalizados exatamente com as mesmas ferramentas que estão disponíveis para arquivos nativos.

Bons estudos!

APRESEN TAÇÃO

Organização Greisse Moser Badalotti

Reitor da UNIASSELVI Prof. Hermínio Kloch

Pró-Reitora do EAD Prof.ª Francieli Stano Torres

Autor Paolo Moser

Edição Gráfica e Revisão UNIASSELVI

2.1 AUTOFILTRO

O autofiltro é a ferramenta básica de filtragem e classificação do Excel. Você pode acessá-la por meio de vários caminhos distintos (como a maioria das ferramentas do Excel). Entretanto, neste material optamos por demonstrar, para todas as ferramentas (inclusive as futuras), um único caminho, em prol da simplicidade.

Antes de falarmos da ferramenta em si, temos que tomar o cuidado de organizar corretamente os dados. Essa precaução é fundamental para o bom funcionamento dos filtros. Para tanto, devemos manter os dados sistematizados na forma de tabelas com atributos nas colunas e registros nas linhas. Lembra de quando falamos de banco de dados, no primeiro capítulo? Pois então, é exatamente isso: precisamos dados organizados na forma de um banco tabular, onde a primeira linha contém os cabeçalhos que indicam o significado de cada atributo. Para fins didáticos, consideremos os dados a seguir (Figura 1), que serão utilizados nos exemplos subsequentes. Esses dados (fictícios) referem-se ao cadastro de 10 alunos de uma instituição de Ensino Superior (já registrados em uma planilha do Excel).

FIGURA 1 – CADASTRO (FICTÍCIO) DE ALUNOS DE CURSO SUPERIOR

FONTE: O autor

A ferramenta “Filtro” 1 deve ser aplicada à linha dos cabeçalhos 2 (Linha 1). Para tanto, posicione o cursor na célula A1 (para referenciar a linha de cabeçalhos) e utilize o menu DADOS. Na seção “Classificar e Filtrar”, clique no botão “Filtro” (conforme Figura 2). Doravante, procedimentos operacionais como este serão descritos pelo seguinte padrão: DADOS >> Classificar e Filtrar > Filtro.

1 “Filtro” e “Autofiltro” são tratados como sinônimo, uma vez que os arquivos de ajuda do Excel chamam a ferramenta de Autofiltro, enquanto a tradução do software ao português optou por denominá-la apenas como Filtro. 2 O atributo “ID” refere-se ao identificador (único) de cada um dos registros da tabela.

FIGURA 2 – FERRAMENTA FILTRO

FONTE: O autor

Agora, olhe para a planilha do Excel. Note o aparecimento de pequenas setas na linha dos cabeçalhos. Estas setas são menus flutuantes que lhe permitirão filtrar (e classificar) os dados a seu próprio gosto. Por exemplo, suponha que você queira selecionar os registros de todas as alunas que se chamam “Juliana”. Para isso, clique na seta que está sobre o atributo “Nome”, no cabeçalho. No menu que aparece, desabilite a opção “(Selecionar Tudo)” e, em seguida, habilite a opção “Juliana” e clique em OK. Verifique esta configuração (antes de clicar em OK) na Figura 3.

FIGURA 3 – CONFIGURAÇÃO PARA FILTRAR O ATRIBUTO “NOME”, PARA SELECIONAR “JULIANA”

FONTE: O autor

começamos classificando o campo mais interno, para isso ativamos a ferramenta de filtro, selecionando a célula A1 e procedendo ao caminho DADOS >> Classificar e Filtrar > Filtro. Agora, selecionamos a célula C e expandimos o menu flutuante do Filtro. Em seguida, utilizamos a opção “Classificar de A a Z”. Após este procedimento, os dados devem estar configurados conforme a Figura 5.

FIGURA 5 – DADOS CLASSIFICADOS PELO CAMPO “SOBRENOME”, EM ORDEM ALFABÉTICA

FONTE: O autor

Agora, faça o mesmo procedimento para o campo “Nome”. Pronto! Os dados estão classificados, com “Nome” sendo o campo mais externo e “Sobrenome” o campo mais interno. Se tudo ocorreu corretamente, sua configuração deve estar igual à da Figura 6.

FIGURA 6 – DADOS CLASSIFICADOS COM “NOME” SENDO O CAMPO MAIS EXTERNO E “SOBRENOME” O CAMPO MAIS INTERNO

FONTE: O autor

b) Para obter um relatório dos alunos do sexo Masculino que cursam Matemática, precisaremos utilizar dois filtros. Esteja ciente também de que a ordem que aplicamos estes filtros não terá influência sobre o resultado final. Optaremos aqui por filtrar primeiramente o atributo “Sexo” para depois filtrar o atributo “Curso”, mas se você inverter a ordem, o resultado é o mesmo. Primeiramente, ative os filtros, conforme o procedimento já explicado. Agora, expanda o menu do filtro do campo “Sexo” e selecione apenas “M” (Masculino). Note que, neste momento, estão sendo exibidos todos os alunos de sexo masculino, independentemente do curso que frequentam. Sem desfazer esta pré-seleção, expanda o filtro do campo “Curso” e selecione apenas “Matemática”. Pronto! Você está diante dos alunos do sexo Masculino e que cursam Matemática. Se tudo ocorreu bem, seu resultado deve ser igual ao apresentado na Figura 7.

FIGURA 7 – RELATÓRIO DOS ALUNOS DO SEXO MASCULINO QUE CURSAM MATEMÁTICA

FONTE: O autor

2.2 FILTRO AVANÇADO

O Filtro avançado permite que o usuário faça filtragens com mais condições lógicas do que o autofiltro. Para entender sua utilidade, pense na seguinte situação: usando o banco de dados da Figura 1, suponha que desejamos saber quem são os alunos que têm idade superior a 25 anos e cursam computação ou que estão no quarto semestre ou além. Note que agora temos várias condições. Com o autofiltro, não conseguimos acomodar todas estas condições, simultaneamente.

b) Para uma mesma linha, as colunas/atributos funcionam como o operador e. c) Linhas diferentes funcionam como o operador ou.

Este é o momento de você revisar o Capítulo 1 deste caderno de estudos para garantir que o funcionamento dos operadores e e ou está bem assimilado. Lembre-se: o operador e só é verdadeiro quando ambas as condições são verdadeiras, enquanto o operador ou é verdadeiro se, pelo menos, uma das condições for verdadeira (o que não impede que todas sejam verdadeiras simultaneamente).

Agora, voltemos às condições do problema: queremos selecionar os alunos que têm idade superior a 25 anos e cursam computação ou que estão, pelo menos, no quarto semestre. Vamos lançar estas condições no intervalo de critérios.

Na célula N2, digitamos a expressão “>25”, indicando que queremos selecionar apenas os alunos com idade superior a 25 anos. Na mesma linha, na célula O2, digitamos “Computação”. Com essa configuração, estamos informando ao filtro avançado que queremos selecionar os indivíduos que têm idade superior a 25 anos e cursam computação.

Agora, na célula P3, digitamos a expressão “>=4”. Com isso, estamos informando que queremos manter somente os registros cujo semestre é igual ou superior ao quarto, ou seja, os alunos que estão, pelo menos, no quarto semestre. Se você utilizou as mesmas células de referência que usamos neste texto, sua planilha deverá estar configurada conforme a Figura 10.

FIGURA 10 – PLANILHA PRONTA PARA USO DO FILTRO AVANÇADO

FONTE: O autor

Note que com esta configuração estamos expressando exatamente a combinação de condições desejadas: alunos que têm idade superior a 25 anos e cursam computação ou que estão, pelo menos, no quarto semestre.

Agora, selecione a célula A1 1 e siga o caminho DADOS >> Classificar e Filtrar > Avançado. Você deve estar diante da janela que é mostrada na Figura 11.

1 Não é estritamente necessário selecionar a célula A1 antes de habilitar o filtro automático. Entretanto, selecionar uma célula da tabela a ser filtrada faz com que o Excel detecte automaticamente a área desta tabela, facilitando o processo.

FIGURA 11 – JANELA DO FILTRO AVANÇADO

FONTE: O autor

Agora, vejamos as opções:

a) Filtrar a lista no local: Essa opção faz a seleção na própria tabela de dados, ocultando os itens que não obedecem aos critérios. b) Copiar para outro local : Permite que você selecione outra planilha, por exemplo, para exibir os resultados da seleção. Assim, o layout da planilha de dados originais não será modificado. c) Intervalo da lista: O intervalo que contém a tabela a ser filtrada. No caso, como havíamos deixado uma célula selecionada desta tabela, o Excel reconhece o intervalo automaticamente, desde que a tabela não tenha linhas e colunas em branco. d) Intervalo de critérios : Intervalo que contém as condições que criamos na parte preparatória do processo. Logo, selecionaremos o intervalo J1:P3. e) Somente valores exclusivos : Seleciona somente um indivíduo, no caso de dois iguais no que diz respeito às condições de filtragem. Como queremos todos os registros, deixaremos esta opção desabilitada.

Ao clicar em OK, a tabela deverá ser filtrada de acordo com os critérios requeridos, ficando com a configuração da Figura 12.

FIGURA 14 – RESULTADOS DA APLICAÇÃO DO FILTRO AVANÇADO

FONTE: O autor

Note que o único registro que foi ocultado pelo filtro foi o do aluno “Maicom Aguiar”. Você sabe explicar o porquê? Basta lembrar dos critérios de filtragem: alunos que não cursam Administração ou que têm, no máximo, 25 anos de idade. Note que as condições para o referido aluno falham simultaneamente: ele possui 44 anos de idade (mais do que 25) e cursa Administração (a primeira condição busca alunos que não cursam Administração). Viu como é importante entender o comportamento dos operadores lógicos?

2.3 IMPORTAÇÃO DE ARQUIVOS DO TIPO TEXTO

Nesta seção veremos como “abrir” arquivos de texto (.txt) no ambiente do Excel. Utilizar arquivos .txt para armazenar bases de dados tem algumas vantagens, entre elas:

• Tamanho do arquivo: Arquivos .txt normalmente usam o mínimo possível de espaço de armazenamento, diferente de arquivos .xlsx, que necessitam armazenar mais parâmetros.
• Portabilidade: Arquivos de texto são reconhecidos pela maioria dos sistemas operacionais e, por ser um formato aberto (não proprietário), não necessitam de softwares pagos para sua leitura.
• Versatilidade: Muitos softwares exportam seus relatórios no formato .txt, o que permite uma compatibilidade entre diferentes programas e plataformas.

Entretanto, editar um arquivo .txt normalmente é mais trabalhoso e menos prático do que editar uma planilha no Excel. Então, a importação de arquivo de texto para o Excel representa um ganho operacional nesta manipulação. Outra vantagem é que, após feitas as modificações desejadas, o Excel permite salvar (exportar) os arquivos novamente no formato .txt. Desta forma, não se perdem as vantagens citadas acima.

Para exemplificar o uso das ferramentas de importação, primeiramente vamos criar um arquivo .txt no Bloco de Notas ou editor de texto similar. Neste exemplo, separaremos as colunas por espaços, embora você possa usar outros delimitares, como vírgulas, pontos e tabulações, por exemplo. Tome cuidado para não deixar espaços em variáveis nominais, pois neste caso o Excel entenderá como duas colunas diferentes 1.

Vamos lá! Abra um editor de texto e crie o arquivo abaixo (Figura 15), salvando-o em um diretório de sua preferência. Estes dados são dados fictícios de uma garagem de automóveis e a primeira linha corresponde ao cabeçalho. Cada linha do arquivo corresponde a um registro. Note que os atributos dos registros estão separados por espaços.

1 É muito comum o uso do sublinhado (underline) para representar espaços em variáveis nominais em arquivos txt. Por exemplo, uma variável chamada “dia da semana” poderia ser escrita como “dia_da_semana”.

FIGURA 15 – ARQUIVO DE TEXTO CONTENDO O BANCO DE DADOS (FICTÍCIO) DE UMA GARAGEM DE AUTOMÓVEIS

FONTE: O autor

A ferramenta para importação de arquivos de texto, no Excel, encontra-se no caminho DADOS >> Obter Dados Externos > De texto (Figura 16). Vamos aprender como utilizá-la no Exemplo 2.3.

em “Avançar” e “Concluir”. Neste momento, o Excel lhe perguntará se você deseja inserir os dados importados na planilha existente ou em uma nova planilha. Neste exemplo, utilizaremos a planilha existente. Neste caso, basta clicar em OK. Pronto, seus dados foram importados (Figura 18) e podem ser manipulados como se tivessem sido nativamente gerados no Excel.

FIGURA 18 – DADOS DE TEXTO IMPORTADOS NO EXCEL

FONTE: O autor

2.4 IMPORTAÇÃO DE DADOS DA INTERNET

Importar dados da internet é muito semelhante a importar dados de arquivos de texto, com a diferença de que agora a base de dados não se encontra em seu computador, mas sim na internet. Muitas instituições tornam seus dados disponíveis na rede mundial de computadores ( world wide web ), e você pode acessá-los diretamente pelo excel, através do caminho DADOS >> Obter Dados Externos > Da Web, conforme Figura 19. Apenas tenha certeza de estar devidamente conectado à Internet.

FIGURA 19 – FERRAMENTADE IMPORTAÇÃO DE DADOS DA WEB

FONTE: O autor

O procedimento é muito semelhante ao da importação de dados de texto, e será explicado no Exemplo 2.4.

Exemplo 2.4. Suponha que você deseja obter dados das ações de maior valor da Bolsa de Valores de São Paulo (IBOVESPA), diretamente deste portal e em tempo real. Como você procederia?

Resolução 2.4. Inicialmente, precisamos saber o endereço da web onde se encontram os dados desejados. Não se preocupe, você pode fazer

esta consulta diretamente dentro do ambiente do Excel. Então, proceda ao caminho DADOS >> Obter Dados Externos > Da Web. Um navegador (muito semelhante ao Internet Explorer) aparecerá. Para fins didáticos, forneceremos diretamente o link para o arquivo desejado, mas lembre-se, você pode navegar neste console do mesmo jeito que navegaria no seu browser padrão. Então, digite o endereço <http://pregao-online.bmfbovespa. com.br/> 1. Utilizaremos aqui figuras para o pregão do dia 19/03/2017. No seu caso, as cotações devem variar, mas o layout provavelmente será parecido. Note a pequena seta amarela que está destacada na Figura 20. Utilizaremos ela para importar os dados das ações com maiores altas, em tempo real.

1 Dependendo da versão e do modelo do site, o Excel pode fornecer o seguinte aviso: “Deseja continuar executando Scripts nesta página?”. Basta clicar em “Sim”. Nenhum dano ou vírus será transferido ao seu computador (desde que o site seja confiável).

FIGURA 20 – BOTÃO DE IMPORTAÇÃO DE DADOS DA WEB , EM TEMPO REAL (DESTACADO)

FONTE: O autor

Agora, basta clicar nesta seta, seguido de “Importar”, escolher a célula ou a planilha onde os dados serão importados e, pronto, você tem os dados à sua disposição para qualquer manipulação, como se eles tivessem sido

Exemplificaremos o uso do Assistente de consulta no Exemplo 2.5.

Exemplo 2.5. Suponha que os dados da Figura 23 representem o catálogo (fictício) de uma livraria, com os respectivos preços. Estes dados são atualizados de acordo com os reajustes dos preços e da chegada de novos títulos. Suponha também que estes dados estão salvos em um local específico e conhecido, e que você deseja gerar relatórios a partir destes dados, em uma nova planilha. Neste relatório, você deseja visualizar os títulos que custam mais do que R$ 100,00. Como você procederia?

FONTE: O autor

FIGURA 22 – ACESSO AO MICROSOFT QUERY

FIGURA 23 – CADASTRO (FICTÍCIO) DOS TÍTULOS DE UMA LIVRARIA

FONTE: O autor

Resolução 2.5. Em uma nova planilha do Excel, proceda ao caminho DADOS >>> Obter Dados Externos >> De Outras Fontes > Do Microsoft Query. Na janela que aparece, clique em “Excel Files*” e, em seguida, clique em OK. Na janela que aparece, procure o arquivo onde você salvou a planilha base , abrindo-a em seguida. Clique em “Opções” e habilite a opção “Tabelas do sistema” 1 , clicando em OK em seguida. Agora, adicione a tabela desejada, selecionando-a e utilizando o botão. Em seguida, clique em “Avançar”. Agora, programe as condições desejadas, no caso, “preço maior do que R$ 100,00”. Então, você deve estar diante de uma janela com a seguinte configuração (Figura 24).

1 Se esta opção estiver desabilitada, o Excel não consegue acessar as tabelas/planilhas existentes no arquivo .xlsx (elas estão “invisíveis”).