Excel

Unit 1: Add unit title

Excel

Introdução

Objetivos de Aprendizagem

No final da lição deverá ser capaz de:

Aplicar operações ordenação e Pesquisa

Filtros automáticos e avançados

Funções de Ordenação e Pesquisa

De uma forma simplificada, pode afirmar-se que uma base de dados é um conjunto de informação que está armazenada sob a forma de linhas e colunas numa tabela, em que as linhas correspondem aos registos e os campos às colunas. No exemplo seguido, está presente uma base de dados composta por 11 campos, (categoria, código artigo, artigo, preço custo, ..., quantidade encomendar) e por 10 registos, que correspondem a cada um dos artigos em stock. Este modo de estruturar a informação permite uma maior facilidade no acesso e na gestão dos dados.

Funções de Ordenação e Pesquisa

O Excel possui comandos específicos, definidos no agrupamento (menu) DADOS [DATA], para o tratamento das bases de dados. O primeiro comando deste menu corresponde à ordenação ORDENAR [SORT]. Numa base de dados o nome dos campos deve corresponder aos títulos das colunas, indicadores da ordem de ordenação.

Ordene a tabela de artigos ascendentemente por categorias e descendente-mente por artigo...

Funções de Ordenação e Pesquisa

O Excel possui comandos específicos, definidos no agrupamento (menu) DADOS [DATA], para o tratamento das bases de dados. O primeiro comando deste menu corresponde à ordenação ORDENAR [SORT]. Numa base de dados o nome dos campos deve corresponder aos títulos das colunas, indicadores da ordem de ordenação.

Ordene a tabela de artigos ascendentemente por categorias e descendente-mente por artigo...

Funções de Ordenação e PesquisaPara visualizar unicamente os artigos a encomendar, comece por selecionar a totalidade da base de dados, B10:M20, aceda ao separador DADOS [DATA] e escolha o comando FILTROS [FILTER]. Surge automaticamente um botão de lis-tagem em cada campo para facilitar a criação de filtros. Pressione o botão de lis-tagem do campo “Situação” e escolha o valor “A Encomendar”. Automaticamente a listagem fica reduzida à apresentação dos artigos em situação de encomenda, tal como apresentado na figura anterior.

Funções de Ordenação e PesquisaPara listar os artigos a encomendar, pertencentes à categoria “CX” deve, para além do filtro já criado, definir um segundo filtro. Pressione o botão de lista-gem, correspondente ao campo “Categoria”, e seleccione o valor “CX”. Como resultado deverá obter uma pequena listagem composta pelos artigos “Caixa Organizer” e “Caixa Rolling 40lt”. Para voltar a visualizar todos os registos, pode optar por retirar os filtros aplicados em cada um dos campo ou aceder à opção FILTROS [FILTERS] do sepa-rador DADOS [DATA] e escolher o comando LIMPAR [CLEAR].

Funções de Ordenação e PesquisaOs filtros automáticos também permitem definir critérios mais precisos para a extração de registos da base de dados. Para listar os artigos cujo preço de custo está compreendido entre 100 € e 500 € ative o botão de filtro do campo “Preço Custo” e selecione a opção FIL-TROS NUMÉRICOS/FILTRO PERSONALIZADO [NUMBER FILTERS/CUSTOM FILTER] (PER-SONALIZAR [CUSTOM]). Surge a seguinte caixa de diálogo:

Funções de Ordenação e PesquisaUma vez que os filtros automáticos permitem apenas personalizar duas condições, torna-se impossível utilizá-los para visualizar unicamente os registos respeitantes aos artigos ‘101’, ‘201’, ‘301’, ‘ 401’. Por esse motivo é necessário recorrer a filtros avançados. Nos filtros avançados é imprescindível a definição de um intervalo de crité-rios. Esse intervalo é definido com uma tabela composta por duas ou mais linhas. A primeira compreende o nome dos campos da base de dados e a segundo a definição dos filtros. Para visualizar os registos dos quatro artigos pedidos comece por copiar, para as células B23: M23, a primeira linha da base de dados, com o objetivo de definir a tabela de critérios, tal como apresentado na figura anterior. Uma vez que se pretende visualizar os artigos cujos códigos correspondem a ‘101’, ‘201’, ‘301’ e ‘401’, preencha a coluna do código do artigo, da tabela de critérios, com esses valores tal como apresentado.

Funções de Ordenação e PesquisaPara a resolução deste problema necessita de redefinir o intervalo de critérios digitando em cada linha da coluna “Situação” o texto “Em Ruptura”:

Sempre que se utilizam várias linhas, no intervalo de critérios, o Excel aplica a disjunção das condições estabelecidas nas diferentes linhas e conjunção no interior de cada uma. Assim esta redefinição dos critérios resulta na listagem dos artigos cujo código é ‘101’ E estão “Em Ruptura” OU cujo código é ‘201’ E estão “Em Ruptura”, etc.

Funções de Ordenação e Pesquisa

Funções de Ordenação e PesquisaRetome a visualização de todos os registos da base de dados pressionando a opção LIMPAR [CLEAR] do agrupamento ORDENAR E FILTRAR [SORT & FILTER] do separador DADOS [DATA] (optando por MOSTAR TUDO [SHOW ALL] do sub-menu FIL-TROS [FILTERS] do menu DADOS [DATA]). Elimine também os valores contidos na tabela de critérios. Para apresentar o registo da base de dados correspondente ao artigo cujo preço de custo é o mais elevado, deve começar por calcular, no intervalo de ritérios, na célula E24, o maior valor do preço de custo entre todos os artigos. Para isso, aceda à função estatística MÁXIMO [MAX] e defina os seguintes parâmetros: Máximo (E11:E20)

Funções de Ordenação e Pesquisa

Funções de Ordenação e PesquisaOs filtros automáticos podem eventualmente ser insuficientes para a extrac-ção de determinadas combinações de dados. Essa situação implica o recurso a filtros mais avançados. Os filtros avançados permitem seleccionar os dados a serem visualizados através da aplicação de um intervalo de critérios, em que apenas serão apresentados os registos que obedeçam a esses critérios.

Apresente somente os registos dos artigos ‘101’, ‘201’, ‘301’, ‘401’...

Funções de Ordenação e Pesquisa

Funções de Ordenação e PesquisaOs filtros automáticos podem eventualmente ser insuficientes para a extrac-ção de determinadas combinações de dados. Essa situação implica o recurso a filtros mais avançados. Os filtros avançados permitem seleccionar os dados a serem visualizados através da aplicação de um intervalo de critérios, em que apenas serão apresentados os registos que obedeçam a esses critérios.

Apresente somente os registos dos artigos ‘101’, ‘201’, ‘301’, ‘401’...

Funções de Ordenação e PesquisaUma vez que os filtros automáticos permitem apenas personalizar duas condições, torna-se impossível utilizá-los para visualizar unicamente os registos respeitantes aos artigos ‘101’, ‘201’, ‘301’, ‘ 401’. Por esse motivo é necessário recorrer a filtros avançados. Nos filtros avançados é imprescindível a definição de um intervalo de crité-rios. Esse intervalo é definido com uma tabela composta por duas ou mais linhas. A primeira compreende o nome dos campos da base de dados e a segundo a definição dos filtros. Para visualizar os registos dos quatro artigos pedidos comece por copiar, para as células B23: M23, a primeira linha da base de dados, com o objectivo de definir a tabela de critérios, tal como apresentado na figura anterior. Uma vez que se pretende visualizar os artigos cujos códigos correspondem a ‘101’, ‘201’, ‘301’ e ‘401’, preencha a coluna do código do artigo, da tabela de critérios, com esses valores tal como apresentado.

ExercícioPara aplicar o filtro definido, aceda ao separador DADOS [DATA] e no agrupa-mento ORDENAR E FILTRAR [sOrt & FILTER] seleccione a opção AVANÇADAS [ADVAN-CED] (seleccione o comando FILTRO [FILTER] e escolha a opção FILTRO AVANÇADO [ADVANCED FILTER]). Surge a seguinte caixa de diálogo:

Funções de Ordenação e Pesquisa

Funções de Ordenação e PesquisaNesta caixa de diálogo, comece por definir o local onde pretende a apresen-tação dos resultados a obter. Se optar por FILTRAR A LISTA NO LOCAL [FILTER THE LIST IN-PLACE] a visualização do resultado, da aplicação do filtro, será efectuada na própria base de dados. À semelhança dos filtros automáticos, são ocultados os registos que não obedecem aos critérios definidos. Se optar por COPIAR PARA OUTRO LOCAL [COPY TO ANOTHER LOCATION], o resultado ficará visível numa outra área da mesma folha de cálculo. Esta última opção implica o preenchimento do campo COPIAR PARA [COPY TO] com a indicação do intervalo de célula(s) para onde devem ser copiados os registos que satisfaçam os critérios. Para este exemplo opte pela primeira situação em que o resultado surge na própria base de dados. No segundo parâmetro, INTERVALO DA LISTA [LIST RANGE], é solicitada a indicação das células que compõem a base de dados. Preencha com os endere-ços B10:M20. Para finalizar, indique o INTERVALO DE CRITÉRIOS [CRITERIA RANGE] inserindo os endereços das células que compõem a tabela dos filtros a aplicar, B23:M27. Pressione OK e deverá obter o resultado apresentado.

Funções de Ordenação e PesquisaDo conjunto dos artigos com o código ‘101’, ‘201’, ‘301’ e ’401’ seleccione os que se encontram em situação de ruptura...

Para a resolução deste problema necessita de redefinir o intervalo de critérios digitando em cada linha da coluna “Situação” o texto “Em Ruptura”:

Funções de Ordenação e PesquisaSempre que se utilizam várias linhas, no intervalo de critérios, o Excel aplica a disjunção das condições estabelecidas nas diferentes linhas e conjunção no interior de cada uma. Assim esta redefinição dos critérios resulta na listagem dos artigos cujo código é ‘101’ E estão “Em Ruptura” OU cujo código é ‘201’ E estão “Em Ruptura”, etc. Após completar a tabela de critérios, repita as operações anteriores para a aplicação do novo filtro avançado. Deverá obter uma pequena listagem com-posta pelos artigos 101 e 401. Uma outra situação interessante é a possibilidade da utilização de fórmulas no intervalo de critérios.

Funções de Ordenação e PesquisaApresente o registo correspondente ao artigo com o preço de custo mais ele-vado...

Retome a visualização de todos os registos da base de dados pressionando a opção LIMPAR [CLEAR] do agrupamento ORDENAR E FILTRAR [SORT & FILTER] do separador DADOS [DATA] (optando por MOSTAR TUDO [SHOW ALL] do sub-menu FIL-TROS [FILTERS] do menu DADOS [DATA]). Elimine também os valores contidos na tabela de critérios. Para apresentar o registo da base de dados correspondente ao artigo cujo preço de custo é o mais elevado, deve começar por calcular, no intervalo de critérios, na célula E24, o maior valor do preço de custo entre todos os artigos. Para isso, aceda à função estatística MÁXIMO [MAX] e defina os seguintes parâmetros:

Funções de Ordenação e Pesquisa

Pressione OK para obter a seguinte tabela de critérios: Pressione OK para obter a seguinte tabela de critérios:

Aplique os filtros avançados, para este novo critério, e deverá obter apenas o registo do artigo 401 da categoria “Pastas e Arquivos”, correspondente ao artigo cujo preço de custo é o mais elevado. Uma vez que, o valor do preço de custo, apresentado na tabela de critérios, depende da lista de artigos em causa, o resultado obtido é um valor variante.

ExercícioCrie, no Excel, uma base de dados para registar os automóveis usados dispo ní veis para venda, representada na figura da página seguinte.Introduza os dados formatados em conformidade com a figura. Ordene a tabela por ordem decrescente utilizando o critério ano do veículo.Utilize os filtros automáticos para visualizar apenas as viaturas com ano supe rior a 2000 e com preço base inferior a 15000 €.Utilize os filtros avançados para visualizar os veículos de serviços com o ano superior a 2002 e os veículos de ocasião com preço inferior a 2000.Grave o ficheiro com o nome Exercício_9.

Exercício

Parabéns!

Completou este Tópico