EXCEL 2007 - FILTRAR UMA BASE DE DADOS COM UMA FUNÇÃO UDF (How to filter a database with a UDF Function)
O utilizador comum poderá pensar que filtrar ou analisar informação de uma pequena base de dados ou de uma tabela é algo para pessoas mais experimentadas em Excel. Ora bem, nem sempre é tão complicado assim, já que o poderemos fazer com recurso a uma função UDF (User Defined Function). Há algum tempo atrás, usei o modelo em anexo para filtrar dados de pequenas tabelas. A função utilizada permite listar os itens por ordem alfabética e acrescentar automaticamente outros itens, caso a base de dados ou a tabela seja modificada.
Common users may think that filtering or analyzing information from a small database or a data table is for people more experienced in Excel. Well, it's not always so complicated, since we can make use of a UDF (User Defined Function). For quite some time ago, I have used the template attached to filter data from small tables. This UDF example allows to list the items alphabetically and automatically add other items if the
database or table is modified.
Poder-se-á argumentar que a ferramenta Filtro Avançado executa o mesmo procedimento. De facto, é verdade, mas só se a tabela ou a base de dados de origem não for modificada, caso contrário teremos de executar essa ferramenta sempre que se proceder a uma adenda à tabela (por exemplo, acrescentar um novo item). Além disso, não ordena os itens alfabeticamente. Por outro lado, podíamos igualmente recorrer às tabelas dinâmicas, mas a ideia é apresentar uma alternativa que simplifique o processo para utilizadores menos experimentados.
It could be argued that the Advanced Filter tool performs the same procedure. In fact, it is true, but only if the original table / database is not changed, otherwise we will have to perform this tool whenever you make an addendum to the table (eg, adding a new item). Beyond addition, it doesn't sort the items alphabetically. On the other hand, we could also use the dynamic tables, but the idea is to present an alternative that simplifies the process for users with lower experience.
Tomemos como exemplo uma oficina de reparação de automóveis em que estão listados, numa pequena base de dados, os automóveis reparados por cada mecânico, a data da reparação e o respectivo orçamento.
Take for example a garage car service where we list on a small base data every car repaired by each mechanic, the repair date and budget.
Suponhamos agora que queríamos apurar a produção mensal de cada mecânico. Para isso, criamos outra tabela noutra folha, onde vamos aplicar a Função UDF (à qual vamos chamar "ListaUnica").
Now suppose we wanted to determine the monthly production of each mechanic. For this, we create a new table in another sheet, where we apply the UDF function (which we name "ListaUnica").
Antes, porém, vamos nomear o intervalo na coluna C ("Mecânicos") para simplificar a fórmula.
But first, let's name the range in column C ("Mecânicos") to simplify the formula.
Para introduzirmos a Função UDF, clicamos sobre o separador da folha com o botão direito e escolhemos "Ver Código". A seguir, na caixa à esquerda (VBA Project) clicar novamente com o botão direito, escolher Inserir - Módulo. Copiar depois o código seguinte para dentro do Módulo.
To introduce the UDF function, click on the tab sheet with the right mouse button and choose "View Code". Then in the left box (VBA Project) click again with the right button, choose Insert - Module. Then Copy the following code into the module.
O passo seguinte é criar a tabela com os elementos que queremos filtrar:
The next step is to create the table with the elements that we want to filter:
Numa outra folha, seleccionamos um intervalo (por exemplo, A2:A30) e na barra de fórmulas escrevemos o seguinte: "=ListaUnica(Mecânicos)". A seguir premimos "Ctrl + Shift + Enter" para que a fórmula seja uma fórmula matriz.
In another sheet, we select an interval (eg, A2:A30) and write "= ListaUnica (Mecânicos)." in the formula bar. Then press "Ctrl + Shift + Enter" to make it a matrix formula.
O aspecto final da fórmula em todas as células do intervalo seleccionado será: "{=ListaUnica(Mecânicos)}".
The formula final aspect in every cell of the selected range will be: "{=ListaUnica (Mecânicos)}."
Para apurar os valores correspondentes a cada mês e a cada mecânico, basta introduzir uma fórmula SOMA.SE.S, tal como é mostrado no modelo.
To determine the values corresponding to each month and each mechanic, just enter a formula SUMIFS as shown in the template.
Como se pode verificar, este modelo pode ser desenvolvido à medida de cada caso.
As can be seen, this template can be developed according to each case.
Common users may think that filtering or analyzing information from a small database or a data table is for people more experienced in Excel. Well, it's not always so complicated, since we can make use of a UDF (User Defined Function). For quite some time ago, I have used the template attached to filter data from small tables. This UDF example allows to list the items alphabetically and automatically add other items if the
database or table is modified.
 |
| Folha1 - Tabela de dados Sheet1 - Database Table |
Poder-se-á argumentar que a ferramenta Filtro Avançado executa o mesmo procedimento. De facto, é verdade, mas só se a tabela ou a base de dados de origem não for modificada, caso contrário teremos de executar essa ferramenta sempre que se proceder a uma adenda à tabela (por exemplo, acrescentar um novo item). Além disso, não ordena os itens alfabeticamente. Por outro lado, podíamos igualmente recorrer às tabelas dinâmicas, mas a ideia é apresentar uma alternativa que simplifique o processo para utilizadores menos experimentados.
It could be argued that the Advanced Filter tool performs the same procedure. In fact, it is true, but only if the original table / database is not changed, otherwise we will have to perform this tool whenever you make an addendum to the table (eg, adding a new item). Beyond addition, it doesn't sort the items alphabetically. On the other hand, we could also use the dynamic tables, but the idea is to present an alternative that simplifies the process for users with lower experience.
Tomemos como exemplo uma oficina de reparação de automóveis em que estão listados, numa pequena base de dados, os automóveis reparados por cada mecânico, a data da reparação e o respectivo orçamento.
Take for example a garage car service where we list on a small base data every car repaired by each mechanic, the repair date and budget.
Suponhamos agora que queríamos apurar a produção mensal de cada mecânico. Para isso, criamos outra tabela noutra folha, onde vamos aplicar a Função UDF (à qual vamos chamar "ListaUnica").
Now suppose we wanted to determine the monthly production of each mechanic. For this, we create a new table in another sheet, where we apply the UDF function (which we name "ListaUnica").
Antes, porém, vamos nomear o intervalo na coluna C ("Mecânicos") para simplificar a fórmula.
But first, let's name the range in column C ("Mecânicos") to simplify the formula.
Para introduzirmos a Função UDF, clicamos sobre o separador da folha com o botão direito e escolhemos "Ver Código". A seguir, na caixa à esquerda (VBA Project) clicar novamente com o botão direito, escolher Inserir - Módulo. Copiar depois o código seguinte para dentro do Módulo.
To introduce the UDF function, click on the tab sheet with the right mouse button and choose "View Code". Then in the left box (VBA Project) click again with the right button, choose Insert - Module. Then Copy the following code into the module.
O passo seguinte é criar a tabela com os elementos que queremos filtrar:
The next step is to create the table with the elements that we want to filter:
Numa outra folha, seleccionamos um intervalo (por exemplo, A2:A30) e na barra de fórmulas escrevemos o seguinte: "=ListaUnica(Mecânicos)". A seguir premimos "Ctrl + Shift + Enter" para que a fórmula seja uma fórmula matriz.
In another sheet, we select an interval (eg, A2:A30) and write "= ListaUnica (Mecânicos)." in the formula bar. Then press "Ctrl + Shift + Enter" to make it a matrix formula.
O aspecto final da fórmula em todas as células do intervalo seleccionado será: "{=ListaUnica(Mecânicos)}".
The formula final aspect in every cell of the selected range will be: "{=ListaUnica (Mecânicos)}."
 |
To determine the values corresponding to each month and each mechanic, just enter a formula SUMIFS as shown in the template.
Como se pode verificar, este modelo pode ser desenvolvido à medida de cada caso.
As can be seen, this template can be developed according to each case.
Comentários
Enviar um comentário