Calculando o Imposto de Renda 2015 com o LibreOffice Calc

Autor: Bruno Rafael Santos <rafaelocremix at gmail.com>
http://www.vivaolinux.com.br/artigo/Calculando-o-Imposto-de-Renda-2015-com-o-LibreOffice-Calc

NOÇÕES BÁSICAS SOBRE O CÁLCULO DO IR

Neste breve artigo busco apresentar algumas funções avançadas do LibreOffice Calc utilizando como exemplo uma declaração completa do Imposto de Renda 2015. Trataremos aqui principalmente de: soma condicional, intervalos, células nomeadas, condições, referências, formatos e relações. 

Algumas notas aos leitores:

• Ao longo do artigo descreverei fórmulas matemáticas e funções do LibreOffice. Para as do LibreOffice procurarei utilizar a sintaxe nativa. Eventualmente, para tornar as funções mais legíveis, utilizarei uma sintaxe parecida com a de uma linguagem script como o Python.
• Utilizarei uma notação de matemática financeira onde os valores positivos significam entrada de dinheiro e os negativos significam saída. Isto simplifica enormemente a montagem das fórmulas, mas pode ser difícil de entender no começo; procurarei tratar disso com calma à seguir.
• Aviso de imediato que este artigo é extremamente longo e chato de ler porque é cheio de detalhes maléficos sobre sintaxe de fórmulas.

Primeiro falaremos sobre como funciona o cálculo do Imposto de Renda para sabermos com o que estamos lidando. Serei breve nesta parte pois existe muita documentação na rede. Por acaso, a fonte primária de informação sobre o IR deste artigo é o próprio site da receita que possui um excelente guia sobre o assunto.

Utilizarei como exemplo uma declaração completa de Ajuste Anual feita para uma pessoa apenas, sem dependentes ou alimentandos. A mecânica do cálculo de fato muda muito pouco; a presença de dependentes e pensões alimentícias afeta mais a forma como os dados são organizados em vez de seu tratamento.

O cálculo do imposto de renda anual é relativamente simples. Ele consiste basicamente de uma fração aplicada à base de cálculo somada a uma dedução de incentivo. A obtenção da base de cálculo em si é que é complicada pois é uma combinação de somas e subtrações de rendas e deduções calculadas sobre estas rendas.

Fórmula 1 - Cálculo do Imposto de Renda

imposto_de_renda = base_de_calculo * aliquota + deducao_de_incentivo

Na tabela abaixo (tabela 1) temos os valores que a Receita aplicará este ano aos contribuintes. A base de cálculo é dada como o teto de cada categoria e isso é um ponto importante quando formos tratar a tabela mais adiante. As alíquotas são simples porcentagens e a dedução de incentivo é uma "bondade" a ser subtraída do valor obtido.

Tabela 1 - Alíquotas e Deduções aplicadas em 2015:

A receita considera como Rendimentos Tributáveis todos os valores recebidos pelo contribuinte como salário, aluguel, pensão, juros de empréstimo entre particulares e alguns outros (como indenizações, diárias etc).

Existem também os Não-Tributáveis. Estes devem ser declarados, mas não implicam na base de cálculo como bolsas de estudo (CNPQ, CAPES etc), venda do único imóvel etc, estes serão ignorados aqui.

As Deduções são gastos ou valores cobrados do contribuinte ao longo do ano, geralmente na forma de impostos, que podem ser usados para reduzir o valor de sua renda tributável até o valor da base de cálculo. As deduções básicas são IRRF (imposto de renda retido na fonte) e INSS (contribuição previdenciária oficial). Porém, existem outras formas de dedução que são:

• Previdência Privada PGBL - permite abater até 12% da renda tributável em aplicações de previdência privada.
• Saúde - custos com médicos, clínicas e planos de saúde podem ser abatidos integralmente da renda tributável.
• Instrução/Educação - alguns gastos com educação podem ser abatidos como escolas de ensino fundamental, médio, faculdade e pós-graduação lato-sensu e strictu-sensu. Cursos de inglês e alguns tipos de especializações estão de fora.

Então, resumiremos isso tudo a algumas fórmulas:

• Renda - valor total recebido de rendimentos tributáveis. Se você está contratado via CLT, isso deve ser aproximadamente o seu salário bruto vezes 12.
• INSS - valor das contribuições ao INSS ao longo do ano. Note que isso geralmente é um valor constante ao longo do ano (exceto nas férias e em meses com bônus). Sempre é 100% do valor.
• IRRF - imposto de renda retido na fonte é o valor do IR já cobrado no contra-cheque e que pode ser restituído. Sempre é 100% do valor.
• Saúde - total das deduções com saúde. Sempre é 100% do valor.
• Educação - total dos gastos com educação. Embora o valor total possa ser deduzido, ele possui um teto baixo que este ano é de R$ 3.375,83. Qualquer valor além disto é ignorado. 
  
  educacao = se(educacao > 3375,83; 3375,83; educacao) 
• PGBL - valor aplicado em previdência PGBL. Assim como os de educação, possui um teto que é de 12% da renda total, logo: 
  
  pgbl = se(pgbl > renda * 15%; renda * 15%; pgbl) 
• Deduções - valor total das deduções possíveis no ano, segundo as instruções acima. 
  
  deducoes = saude + educacao + pgbl + inss + irrf

E finalmente:

base_de_calculo = renda + deducoes

Notem que a fórmula é "+ deducoes" em vez de "- deducoes" porque estou utilizando valores negativos para sinalizar débitos/saídas e positivos para créditos/entrada. Isso facilita a montagem das fórmulas e funções, mas exige atenção à formatação dos dados e é isso que veremos na próxima parte.

Então vamos aos dados que utilizaremos como exemplo:

• Renda = R$ 84.000,00 (aproximadamente um salário bruto de R$ 7.000,00 por mês)
• IRRF = R$ -3.600,00 (valor já pago ao IR)
• INSS = R$ -14.000,00 (valor pago ao inss)
• PGBL = R$ -10.000,00 (valor pago à previdência privada)
• Saúde = R$ -8.000,00 (gastos com plano de saúde)
• Instrução = R$ -15.000,00 (valor pago com educação no ano)

ESTRUTURA DA PLANILHA DE DADOS

Sairemos agora da teoria e vamos à prática do Calc. O objetivo desta planilha é tanto ensinar como aplicar algumas ferramentas do Calc na vida prática quanto criar uma planilha que nos ajude a planejar o Imposto de Renda do ano que vem para utilizar ao máximo as deduções. A estrutura geral da planilha de dados será assim:

Tabela 2 - planilha de Movimentação Mensal

As classes abaixo do somatório são os indicadores de onde aqueles totais devem ser utilizados ao longo da planilha. A lista completa é: Renda, IRRF, INSS, Saúde, Instrução e PGBL. 

DATAS

A primeira coisa são organizar as datas. Para definir os meses podemos tanto usar texto normal quanto datas e depois formatá-las para parecerem texto. Gosto mais da segunda abordagem porque ela abre margem para outra coisa, usar a primeira data como referência para as demais. Segue o passo a passo:

1. Coloque a primeira data (Janeiro) como 01/01/2015. O Calc entenderá que é uma data e a tratará como tal.

2. Na célula abaixo, faça uma referência para a célula anterior e adicione a ela o número de dias daquele mês com a função diasnomês(). Por exemplo, se a primeira célula é A1:

= A1 + diasnomês(A1)

3. Agora estenda isto pelas próximas 10 células e teremos um ano completo.

Agora mudaremos a aparência das células. Clique com o direito na célula e escolha "Formatar Célula" ou selecione as células a formatar e vá no menu Formatar > Célula.

Logo na primeira aba - Números - podemos escolhe os formatos nas células selecionadas. No momento o Calc já deve ter escolhido tratar os dados como datas, então um dos formatos de datas está selecionados. Mudaremos o formato para considerar apenas o mês e ignorar o dia e ano:

1. Selecione um dos formatos de datas, qualquer um.

2. Vá em Código de Formato e modifique o campo para MMMM para ter o nome do mês como valor. Note que para cada M o formato muda na seguinte ordem: número do mês, número com zero, nome abreviado e nome completo.

3. Clique em Ok.

Agora todas as células selecionadas exibirão o nome do mês apenas. 

ESTILOS E FORMATOS DE VALORES

Agora vamos aos valores de créditos e débitos nas colunas. Para facilitar a montagem das fórmulas utilizaremos valores negativos para as saídas/deduções e positivos para entradas/rendas. Assim nos preocupamos apenas com a entrada dos valores e menos com contabilidade.

Ao contrário do que aconteceu com as datas, utilizaremos bastante formatos de valores monetários, então seria melhor criarmos um estilo de célula para controlar isto para nós. Para ver, editar e criar novos estilos vá até o menu Formatar > Estilos e Formatação ou aperte F11.

Isto abre um painel que geralmente fica do lado direito. São dois estilos possíveis: de célula e de página. Os de página são para criar relatórios e impressão, os de célula (os da esquerda) são os que nos interessam.

O estilo padrão serve de referência para os demais, você pode edita-lo para a modificar as referências de estilo em todo o documento. Para criar um estilo novo, clique em um deles para ser o "pai" - o novo estilo herda as configurações do "pai" - ou clique diretamente no vazio com o botão direito e escolha "Novo", isto faz do Estilo Padrão o pai e esta é a minha sugestão.

No novo estilo modificaremos apenas os formatos numéricos:

1. Em organizador, podemos dar um nome para o estilo e modificar quem é o "pai". Eu o chamei de "Financeiro".

2. Em Números, escolha um dos formatos de Moeda - preferencialmente um dos vermelhos.

3. Em Código do Formato coloque o seguinte valor: [BLUE][$R$-416] #.##0,00;[RED]-[$R$-416] #.##0,00.

Isto significa: "Valores positivos em azul e com duas casas decimais; valores negativos em vermelho e com duas casas decimais".

4. Clique em Ok.

Com isso agora temos um estilo que configura automaticamente valores das células em reais e os colore de acordo com o valor. Agora basta selecionar as células que seguirão este formato e clicar no estilo para aplicá-lo à elas.

Vamos às linhas de totais e classes. Nos totais, basta a função soma() para somar os valores da coluna. Depois de aplica-lo à primeira, podemos aplicar às demais colunas estendendo para a direita, como fizermos com as datas. Quanto às classes, elas são texto puro e sem nenhum formato específico. 

CLASSES DE DADOS E INTERVALOS

Antes de montarmos as fórmulas para a Tabela 2, precisamos criar intervalos nomeados na Tabela 1 para facilitar. Por exemplo, o seguinte intervalo:

B15:AMJ15

Contém todas as células da linha 15 exceto a primeira. Por acaso, é a linha onde ficam os totais da Tabela 1. Seria mais fácil usar esse intervalo de células na planilha se ele tivesse um nome. Notem que do lado esquerdo da barra de fórmulas há um indicador da célula ou intervalo de células atuais (se não aparecer, vá no menu Exibir > Barra de Fórmulas), mas ele serve para outra coisa também:

1. Selecione o intervalo de células que corresponde ao total da Tabela 1.

2. Use Ctrl + clique para excluir a primeira coluna, que contém o nome da linha. Agora você tem um intervalo de células selecionado semelhante ao que exemplifiquei acima.

3. Vá no indicador esquerdo da barra de fórmulas e escreva um nome para este intervalo ali, por cima da seleção mesmo. Aqui é "ir_movimentação_total".

Com isso o termo ir_movimentação_total passa a ser um indicativo para aquele intervalo de células específico. Mais detalhes sobre os intervalos definidos, o que eles são ou seu escopo (se são exclusivos da planilha atual ou servem a todo o documento) podem ser vistos com o menu Inserir > Nomes Gerenciar... ou com Ctrl + F3.

Os nomes são muito úteis para planilhas mais avançadas - por exemplo podem ser usados como filtros avançados em tabelas muito grandes - mas sua maior virtude é simplificar enormemente a sintaxe das fórmulas. Veja só a diferença disto:

=SE(
    SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
    C20;
    SOMASE(ir_classes; A20; ir_movimentação_total)
    )

Para isso:

=SE(
    SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15) < C20;
    C20;
    SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15))

Faça a mesma coisa para a linha de Classes da tabela 1 (usei o nome "ir_classes") e pronto: agora podemos criar fórmulas mais amigáveis para a Tabela 2. 

CÁLCULO DAS DEDUÇÕES

Agora que temos os dados no lugar organizados, veremos como calcular as deduções automaticamente. A tabela de Deduções (Tabela 2) possui o seguinte formato: na primeira coluna colocamos as classes de deduções (Saúde, Instrução, PGBL, INSS), na segunda são os valores calculados a partir dos Totais e Classes da Movimentação Mensal, na terceira colocaremos os valores máximos para cada dedução. O formato final é assim:

Tabela 2 - Deduções:

Os nomes das classes utilizados nesta tabela devem ser os mesmos utilizados na tabela de Movimentação Mensal pois são esses nomes que servirão para conectar uma coisa com a outra.

Agora criaremos a fórmula para a coluna Executado da Tabela 2. Nós precisamos que essa célula contenha a soma dos valores da Tabela 1 onde a classe seja igual à da coluna Classes na Tabela 2 e ao mesmo tempo não ultrapasse o valor máximo na coluna Disponível.

Para a primeira parte do problema utilizaremos a função somase() que faz soma segundo uma condição. Uma versão mais radical dela, a somases(), o faz com múltiplas condições. A fórmula básica seria:

Fórmula 2 - soma condicional

SOMASE(ir_classes; A20; ir_movimentação_total)

Nesta fórmula ir_classes indica onde a condição deve ser localizada, A20 indica o valor da condição que, neste caso, coincide com a coluna Classe na Tabela 2 e ir_movimentação_total o intervalo de onde os valores são somados. Essa fórmula funciona muito bem graças aos intervalos nomeados pois conforme você adiciona mais dados na Tabela 1 eles são processados diretamente na Tabela 2 sem problemas.

Mas esta fórmula possui um problema, ela só funciona corretamente para Saúde e INSS, onde a dedução é de 100%. Para Instrução a dedução máxima é um valor fixo que é dado no ano e para o PGBL é 15% da renda total apenas. A solução para isso é adicionar um se() para verificar se o valor está ultrapassando o limite. A versão modificada ficará assim:

Fórmula 3 - soma condicional com teto

=SE(
    SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
    C20;
    SOMASE(ir_classes; A20; ir_movimentação_total)
    )

Ela primeiro verifica se o valor ultrapassa o teto (coluna "Disponível" da Tabela 2), se sim, ela força o valor teto, se não, ela usa o total. Simples assim.

A coluna "Disponível" também usa somase(), mas com algumas exceções. As classes Saúde e INSS usam a formula normal do somase() que exemplifiquei anteriormente, a classe Instrução usa um valor fixo que devem ser inserido manualmente e a do PGBL usa a somase(), mas toma como referência o somatório da renda - que por acaso é uma classe também, mas não de dedução - e dela extrai os 12%. A formula fica assim:

Fórmula 4 - teto do PGBL

=-SOMASE(ir_classes; "renda"; ir_movimentação_total) * 0,12

Notem o sinal de menos no início da fórmula. Acontece aqui o seguinte, ela está sendo calculada a partir de uma classe que tem valor positivo (a "Renda") e por isso o sinal dela tem de ser invertido para ficar compatível com as demais.

Agora basta adicionarmos o somatório de cada coluna (Executado e Disponível) no fim utilizando a função soma(). Precisaremos destes totais mais tarde. 

CÁLCULO DO IMPOSTO

Estamos chegando ao final. A próxima tabela (Tabela 3) é onde calculamos o imposto de renda. Mas para ela funcionar precisamos da Tabela 4, com os valores limite do imposto.

Tabela 4 - Tabela de Cálculo:

A Tabela 4 é a mesma que é obtida no site da receita, apenas excluí os intervalos a aproveitei apenas o teto de cada categoria. É mais fácil de implementar do que usar os intervalos um a um.

Tabela 3 - Cálculo:

A Tabela 3 é curtinha, porém trabalhosa pois cada célula possui sua própria fórmula, notem que cada célula também possui um nome próprio pois isso facilita a leitura e montagem das fórmulas. Veremos agora os mais básicos:

Renda Bruta (ir_renda_bruta) - é a renda total do contribuinte. É a mesma soma que é utilizada para calcular o teto do PGBL, portanto, uma versão simplificada da Fórmula 4:

Fórmula 5 - renda bruta

= SOMASE(ir_classes; "renda"; ir_movimentação_total)

Deduções Legais (ir_deduções) - é o total Executado da Tabela 2. Basta fazer uma referência aqui, no caso do modelo, é a célula B24.

= B24

Base de Cálculo (ir_base) - já discutimos isso antes. É simplesmente Renda Bruta + Deduções:

Fórmula 6 - base de cálculo

= ir_renda_bruta + ir_deduções

IR Pago (ir_pago) - este é novidade. Ele é a soma dos IRRF da Tabela 1. Ele é utilizado para calcular a restituição/imposto a pagar logo mais:

Fórmula 7 - ir retido na fonte

= SOMASE(ir_classes; "irrf"; ir_movimentação_total)

Agora faremos Faixa de Renda e Dedução de Incentivo que são duas células interligadas. Primeiro trataremos de Faixa de Renda.

Agora que temos a nossa base de cálculo, temos que localizá-la dentro da Tabela 4 para saber em qual alíquota de imposto estamos. Como os valores são discretos, temos que comparar um por um...

Fórmula 8 - alíquota

= SE(ir_base > A42; B43;
    SE(ir_base > A41; B42;
        SE(B32 > A40; B41;
            SE(ir_base > A39; B40; 0)
            )
        )
    )

Basicamente a fórmula analisa a Tabela 4 de trás para frente, verificando se ir_base é maior que o teto, se for, então estamos no intervalo certo, se não, tentamos o próximo. Notem que os se() ficam um dentro do outro para evitar que um valor pequeno caia em mais de uma categoria.

Poderíamos utilizar a mesma abordagem para Dedução de Incentivo, mas agora que temos a alíquota podemos utilizá-la como referência para a função proc():

Fórmula 9 - dedução de incentivo

= PROC(ir_imposto; ir_aliquotas; ir_incentivos_lista)

proc() busca nas linhas ou colunas da tabela a partir de um valor específico. Sua única limitação é ser restrita a comparações diretas (nada de >, <=...) e os valores de referência devem estar em ordem crescente.

Agora que temos a alíquota e a dedução de incentivo, podemos partir para as duas células que faltam.

IR Devido é calculado da seguinte forma:

Fórmula 10 - ir_devido

= - (ir_base * ir_imposto + ir_incentivos)

É basicamente uma fração da base de calculo, mais a dedução de incentivo que reduz este valor. Depois tudo é convertido para negativo pois isso é uma dívida a pagar.

Fórmula 11 - Imposto a pagar/restituir

= ir_devido - ir_pago

Esta última fórmula é bem simples. Agora que temos a dívida do ir, abatemos dela o ir que já pagamos. Notem que é a primeira vez que usamos uma subtração, pois estamos lidando com dois números negativos.

O valor final da Fórmula 11 nos dirá se temos restituição ou mais imposto a pagar e isso nos leva à Fórmula 12:

Fórmula 12 - pagar/restituir

= SE(B35 < 0; "Imposto Devido"; "Imposto a restituir")

A nossa última fórmula é só uma firula que muda o rótulo da fórmula 11 de acordo com seu resultado.

Vocês podem baixar o modelo final (planilha_ir.ods) que fizemos aqui para ver as fórmulas funcionando e fazer aplicações se quiserem.

CONCLUSÕES

Espero que eu tenha sido objetivo o bastante para ajudar em vez de confundir mais. A maioria das pessoas que trabalham com planilhas possuem apenas umas vaga ideia do que elas podem fazer, então sugiro fortemente aos interessados uma consulta à ajuda do LibreOffice, que por sinal é bastante didática. Espero que isso ajude quem está começando, principalmente com o Calc, a explorar os recursos desta ferramenta.

Um último acréscimo: a conta acima possui um erro de 1 centavo em relação ao programa da Receita que eu não consigo corrigir. Fiquem à vontade para dar sugestões. 

CLASSIFICAÇÃO no CALC - por CAMILA OLIVEIRA

Esta funcionalidade é utilizada para ordenar os dados da planilha com base na informação de uma ou mais colunas, extremamente útil para facilitar a análise dos dados ou apresentação do resultado. A utilização é simples.

Imagem 01 – Atalho Classificação

Há duas formas de fazer, a forma mais simples e imediata é acessar os botões do menu, são duas opções: a ordenação crescente e decrescente. Primeiro selecione uma das colunas, depois clique no tipo de classificação desejada. Pronto, os dados serão imediatamente ordenados.

Imagem 02 – Menu Classificação

A segunda forma é através do acesso pelo menu Dados – Classificação. Será exibida um menu com recursos mais avançado de classificação. É possível fazer a ordenação utilizando várias colunas com níveis diferentes e marcar algumas opções adicionais. Segue um breve passo a passo:

O primeiro passo é selecionar o intervalo de dados que será ordenado. Geralmente seleciono toda a tabela para manter a consistência da informação, mas caso seja necessário é possível ordenar apenas uma coluna. Acesse o menu, na opção Dados – Classificação para definir os critérios de classificação desejado.

Caso tenha esquecido de efetuar a seleção, não tem problema, o Calc exibirá uma mensagem sinalizando que não foi selecionada todas células da tabela e será dada a opção de Estender a seleção, manter a seleção ou cancelar.

Imagem 03 – Menu Classificação

Imagem 04 – Mensagem Alerta

Será exibido um menu que conterá duas abas para definir os parâmetros de ordenação:

Aba de Critério de Ordenação: nesta aba é possível definir três níveis de ordenação, ou seja, é possível selecionar várias colunas e classificar os dados seguindo a ordem das chaves. Na imagem 4 estabeleci três chaves todas com a opção de ordenamento crescente – caso deseje é possível alterar para decrescente, como resultado serão apresentados os dados de forma ordenada com base na primeira chave, se houve dados coincidentes, será ordenado com base na segunda chave e sucessivamente.

Imagem 05 – Aba Critério de Ordenação

Na aba de opções é possível adicionar alguns critérios adicionais à classificação, as opções são:

• Diferenciar maiúscula de minúscula na classificação;
• O intervalo contém rótulos de coluna – se não for marcado a primeira linha (ou coluna) também será ordenada. Por padrão fica pré marcado.
• Incluir formatos – preservar o formato atual das células, por padrão também fica selecionada.
• Ativar classificação natural – Esta função é para ordenar corretamente uma sequência do tipo A1, A2, A3, …, A10, A11, …, A20, A21. Por padrão a ordenação é feita verificando o primeiro caractere logo todos que começam com A, depois verifica o segundo caractere e terceiro ficando assim: A1, A10, A11, A2, A20 e etc. Com a flag ativa a ordenação fica mais natural desta forma: A1, A2, A3, …, A10, A11, …, A20, A21.
• Copiar resultados da classificação para: permite copiar a seleção para outro local pré-definido. Para usar esta função, antes de classificar os dados, acesse a opção Dados – Definir Intervalo, insira um nome para o intervalo, selecione o intervalo em si e depois clique na opção Adicionar. Ao voltar para o menu de classificação, será possível ver o nome do intervalo criado na relação abaixo da flag e ao clicar Ok os dados serão colados para lá.

Imagem 06 – Intervalo de Dados

Imagem 07 – Copiar Intervalo

• Ordem de classificação personalizada – É a possibilidade de criar uma lista personalizada de ordenação, como os modelos personalizados que já existe de dia da semana e mês. É possível inserir mais modelos personalizados acessando o menu LibreOffice – Preferência – LibreOffice Calc – Listas de Classificação, clique na opção Nova para inserir uma nova relação e depois em Adicionar.

Imagem 08 – Classificação Personalizada

Imagem 09 – Personalização Classificação

• Idioma – selecionar o idioma específico para ordenação.
• Direção – Este acredito ser o mais diferente, a direção em como o deverá ser feita a classificação. Normalmente classificamos de cima para baixo, esta é a opção padrão, mas também pode ser feito da esquerda para direita.

Texto Copiado na integra de  CAMILA OLIVEIRA

FILTRO NO CALC – POR CAMILA OLIVEIRA

A maioria das funcionalidades do LibreOffice tem um nome autoexplicativo, a função de Filtro não é diferente. A ideia é possibilitar filtrar os dados de um conjunto de tabelas, quando a relação de dados é extensa é um recurso bem útil. Como exemplo, é possível exibir o resultado de um município em meio a vários, tornando a visualização e análise mais simples.

Imagem 01 – Função Filtro Aplicada

Há três opções de filtragem: auto filtro, filtro padrão e filtro avançado. Para ter acesso selecione o intervalo de dados analisado (também conhecido com matriz) e acesse o menu Dado – Filtro e selecione uma das opções de filtro. Vamos a explicação de cada funcionalidade.

Imagem 02 – Menu

Filtro – Auto filtro

O auto filtro é uma das opções mais rápidas de filtro e também mais flexível. Ao selecionar a opção, automaticamente será criado no topo da matriz uma seta para seleção da informação a ser filtrada, ao clicar no combo (ou seta) será exibida uma caixa de diálogo com várias opções de filtro. Vamos as principais seguindo a ordem apresentada na Image 4.

Imagem 03 – Auto Filtro

• As duas opções iniciais são de classificação dos dados, pode selecionar na ordem crescente ou decrescente.
• O segundo bloco são opções predefinidas de filtros. A primeira opção de 10 primeiros resultados, exibi os maiores valores da coluna, só funciona com célula que possua números, a segunda opção é seleção dos dados vazios e, por fim, os não vazios.
• Filtro padrão é a segunda opção filtragem, explicarei a seguir.
• A relação exibida é a consolidação dos dados da coluna, desmarque a opção todos abaixo e selecione o resultado que deseja exibir, se quiser desmarcar ou marcar todas as opções utilize os botões ao lado de “todos”. Agora é só confirmar e os dados serão filtrados.

Imagem 04 – Detalhe Auto Filtro

Dica: Sempre que o filtro estiver ativo, a seta ficará marcada em azul, como exemplo da imagem 02. Quando se faz muito filtros e queremos desabilitar algum, esta é a melhor forma de localizar os valores filtrados.

Filtro – Filtro Padrão

A funcionalidade é utilizada para definir critérios de filtros preestabelecidos, ao clicar na opção de Filtro Padrão poderá ser definido condições para seleção das informações, algumas das condições são >, <, contém, termina com, entre outras, acredito que cada opção são autoexplicativas, então não irei me aprofundar. É possível adicionar vários parâmetros, a medida que for sendo adicionados novos, deve-se especificar o operador E ou OU. O E determina que o filtro deverá atender as duas condições e o OU determinar uma condição ou outra.

Imagem 05 – Filtro Padrão

Imagem 06 – Detalhe Condição

Para deixar mais claro segue um exemplo, se selecionar o estado = Bahia E com população >= 5000, serão exibidos todos os municípios do estado da Bahia com população de 5.000 pessoas. No caso do emprego do OU, será exibido os municípios do estado da Bahia ou os municípios de vários estados que tenham população >= 5.000.

Na parte abaixo das condições, clique em Opções para exibir critérios adicionais de seleção. São elas:

• Distinção entre maiúsculas e minúsculas
• Expressões Regulares, como a ajuda do LibreOffice define “Se a caixa de seleção Expressões regulares estiver selecionada, você poderá usar expressões regulares no campo Valor se a caixa de listagem Condição estiver definida em ‘=’ IGUAL ou ‘<>’ DIFERENTE. Isso também se aplicará às respectivas células designadas para um filtro avançado.”
• Sem Duplicadas – exclui os dados duplicados da seleção
• Copiar dados para – copia a seleção efetuada para algum outro da planilha.

Imagem 07 – Detalhe Opções

Filtro Avançado

Geralmente é o menos utilizado, mas é bem legal. Em resumo, com esta funcionalidade é possível definir critérios de seleção através de condições especificadas em uma tabela criada em outro local da planilha. O primeiro passo para definir um filtro avançado é definir a tabela com os critérios de filtros, copie e coloque a primeira linha da relação, depois especifique o que deseja ser exibido. A digitação das condições é livre, dependendo apenas do usuário, veja o exemplo na imagem 08.

Imagem 08 – Condição Filtro Avançado

No exemplo da imagem 08 foram especificados os seguintes critérios: ( UF = BA E NomeMunc = Dantas E População <20000 ) OU ( UF = Acre E NomeMunc = Bujari ) OU (UF = Rondônia E População > 5000). Fica bem mais claro na tabela! Este mesmo critério poderia ser aplicado no Filtro Padrão, mas seria bastante complicado detalhar toda a regra.

É importante entender que na tabela as condições na mesma linha são intercaladas com E, ex.: UF = BA ENomeMunc = Dantas E População <20000. E as linhas diferentes é intercalado com OU, ex: ( UF = BA E NomeMunc = Dantas E População <20000 ) OU ( UF = Acre E NomeMunc = Bujari )

Imagem 09 – Filtro Avançado – E e OU

Após feita a definição dos critérios, selecione a matriz (tabela com os dados), acesse o menu Dado – Filtro – Filtro Avançado. Na caixa “Ler os critérios de filtragem de” selecione a tabela elaborada. Ao clicar em Ok, automaticamente a tabela será filtrada com os critérios especificados. Bem legal, caso queiram alterar os critérios, modifique a tabela, selecione novamente Filtro Avançado.

Imagem 10 – Vincula Filtro Avançado

Imagem 11 – Filtro Avançado Aplicado

Dica: Uma vez realizado o filtro avançado, ao acessar o filtro padrão será exibido uma caixa com todos os critérios especificados aplicados.

Imagem 12 – Filtro Padrão com aplicação Filtro Avançado

Para finalizar, caso queiram deletar algum feito realizado, acesse o menu de Filtro e a opção de Redefinir o filtro. Tem também a opção de ocultar o auto filtro, que faz basicamente a mesma coisa.

Imagem 13 – Redefinir Filtro

FORMATAÇÃO CONDICIONAL - CAMILA OLIVEIRA

Ao final de uma análise de dados é interessante inserir uma marcação na coluna para sinalizar se o resultado é positivo ou negativo, uma excelente forma de fazer isso é através da formatação condicional. Como o nome da função diz, é possível formatar alguns resultados com base em algumas condições parametrizadas. Quando comecei a utilizar esta funcionalidade, os recursos eram bem limitados, agora ficou bem mais robusto e mais úteis. A utilização é relativamente simples, vamos ao passo a passo.

Selecione o intervalo que deverá ser incluso a formatação, pode ser uma tabela inteira, uma coluna ou intervalo aleatório, a ser definido pelo usuário. O uso mais comum é em colunas, irei utilizar esta forma como exemplo. Após a seleção dos dados, acesse o Menu – Formatar – Formatação Condicional. Será aberta uma caixa de diálogo da funcionalidade.

Imagem 01 – Formatação Condicional

É possível fazer vários tipos de formatação: todas as células, pelo valor da célula, condição da fórmula e condicional por data, estes três últimos são mais simples. Vamos passar por cada uma das opções, iniciando pelas três últimas e concluindo com a opção de condição por todas as células, que possui mais opções de formações.

Imagem 02 – Opções Formatação Condicional

Formatação – O Valor da Célula é

A formatação é realizada com base nos valores das células, ou seja, será possível incluir critérios para definir em qual valores será aplicada a formatação especificada. Uma vez feita a definição de intervalo, será aberto um combo para selecionar o critério (valor igual a, maior que, entre e várias outras opções), a caixa de formatação irá se ajustar para inclusão dos dados de acordo com a opção do critério.

Imagem 03 – Valor da Célula

Uma vez definido o intervalo e critério de formatação, efetue a seleção de estilo a ser aplicado. Há cinco opções de formatação preestabelecida, mas é possível adicionar novos estilos selecionando a opção “Novo estilo” (meio óbvio, mas é bom esclarecer).

Imagem 04 – Aplicar Estilo

Ao selecionar a opção de Novo Estilo, a caixa de seleção com as opções de Novo Estilo será aberta, desta forma é possível selecionar a formatação desejada a ser aplicada na célula.  O processo é simples, selecione as características que deseja aplicar, são várias – tipo de fonte, tamanho, cores e várias outras opções, no exemplo abaixo, selecione o formato da fonte Tahoma, tamanho 10, cor Azul e formato de porcentagem. Todas as alterações selecionadas ficam evidenciadas na aba Organizador, na parte “contém”.

Imagem 05 – Detalhe Contém

Caso deseje alterar ou excluir o estilo criado, acesse o menu Formatar – Estilos e Formatação. Será aberta uma caixa com todos os estilos criados onde é possível criar um novo estilo e modificar, ocultar ou excluir um existente. Selecione a opção modificar para abrir a caixa da imagem 05 novamente e alterar a formatação.

Imagem 06 – Estilos e Formatação

Imagem 07 – Detalhe Estilo e Formatação

Formatação – A fórmula é

Nunca utilizei esta função, tentei pesquisar mais detalhes, mas as únicas explicação que encontrei foi a da ajuda do LibreOffice: “Se selecionar A fórmula é como referência, insira uma referência de célula. Se a referência da célula é um valor diferente de zero, a condição verificará”. Tentarei obter mais detalhes, assim que consegui escreverei um post especifico sobre o assunto. Um vez estabelecido o critério, aplique o estilo conforme a opção anterior.

Formatação – A Data é

Esta função é aplicada para campos com data, neste caso é possível estabelecer o critério para aplicação da formatação condicional de acordo com as opções oferecidas. Depois selecionado, defina o estilo a ser aplicado, conforme explicado anteriormente.

Imagem 08 – Formatação Condicional Data

Formatação – Todas as células

Este tipo de formatação oferecem quatro opções de formatação: escala de cores com duas entradas, escala de cortes com três entradas, barra de dados e conjunto de ícones. Esta é a funcionalidade mais interessante, pois cria uma marcação com escala de cortes, barra ou símbolos dentre o intervalo de células selecionadas. Sem dúvida é a minha favorita.

Imagem 09 – Formatação Condicional Todas as Células

A função de escala de cores pode ser aplicada de várias formas: por valores, porcentagem, fórmula, mínimo, máximo e outras opções. Selecione o critério desejado e defina o valor desejado. Duas e três entradas funcionam da mesma forma, alterando a variação da escala de cores. Nas imagens abaixo dá para ver o efeito, apliquei na coluna população e mais abaixo fiz com 3 colunas.

Imagem 10 – Escala de Cores

Imagem 11 – Aplicação da Escala de Duas Cores

Imagem 12 – Aplicação de 3 escalas.

Imagem 13 – Planilha com Escala 3 entradas

A formatação de barra de dados cria uma escala nos valores selecionados, também há várias formas de fazer. É possível deixar a seleção automática, neste caso o sistema irá analisar os valores do intervalo selecionado e irá criar uma barra de acordo com o valor da célula em comparação aos demais. Caso queiram personalizar também é possível, pode-se definir valor, min, máx, percentual e outros. Clicando em mais opções é aberta uma caixa de diálogo para personalizar a exibição.

Imagem 14 – Barra de Dados

Imagem 15 – Opções de Critério

Imagem 16 – Resultado da Barra de Resultados

 Por fim, para concluir este longo post, tem a opção de inclusão de conjunto de ícones. Este é bem legal, é possível escolher entre vários ícones, inclusive pode ser até 5 ícones. Adotar símbolos facilita a visualização do resultado e torna a planilha mais eficiente. No exemplo optei por adotar os smiles 

Imagem 17 – Conjunto de Símbolos

Imagem 18 – Resultado Conjunto de Símbolos

Após a conclusão da formatação, caso queiram alterar algumas formatação acesse o menu Formatar – Formatação Condicional – Gerenciar. Nesta caixa é possível verificar todas as formatações criadas e efetuar as manutenções. Como de costume, deixarei a planilha utilizada como modelo em anexo para consulta.

Exemplo_Formatacao_Condicional.ods

Texto Copiado na integra de  CAMILA OLIVEIRA