Uso da função SE em uma matriz no Excel

A função SE(condição; valor_se_verdadeiro; valor_se_falso) no Microsoft Excel avalia uma condição/expressão no primeiro parâmetro e retorna o segundo parâmetro se o resultado for verdadeiro, ou o terceiro (opcional) se for falso.

É o equivalente à lógica de programação SE condição ENTÃO valor_verdadeiro SENÃO valor_falso.

Mas a função tem usos mais elaborados que se possa imaginar de início.

Ela também pode ser aplicada a matrizes (faixas de células do tipo inicial:final) na condição e nos resultados, avaliando uma expressão em cada célula da matriz na condição e retornando o respectivo valor na matriz do parâmetro verdadeiro ou falso.

Observe o exemplo básico a seguir. Nas células A1 a F1 escrevemos números inteiros (1, 15, 37, 42, 59, 85). Na célula A2, a fórmula =SE(A1:F1>40;A1:F1) faz o seguinte: avalia cada célula de A1 a F1 se o conteúdo é maior que 40; se for, então retorna o respectivo valor da célula; senão retorna FALSO (pois não informamos em SE o terceiro parâmetro caso falso). Como a fórmula em A2 resulta em múltiplos valores, o Excel exibe esse resultado distribuído nas células A2 a F2. Veja a figura.

O artigo Como Encontrar O Valor Mais Próximo Maior Ou Menor Que No Excel, em ExtendOffice, apresenta aplicações desse recurso. Utilizando o resultado do exemplo anterior de SE como parâmetro da função MÍNIMO podemos obter o menor número do conjunto que seja maior que 40. No exemplo ilustrado, a fórmula a seguir retorna 42:

=MÍNIMO(SE(A1:F1>40;A1:F1))

Analogamente, para obter o número mais próximo abaixo de (menor que) 40, a fórmula seria =MÁXIMO(SE(A1:F1<40;A1:F1)) com resultado 37.

Veja também o artigo do suporte Microsoft sobre Diretrizes e exemplos de fórmulas de matriz, para outros exemplos interessantes de fórmulas e funções aplicadas a matrizes de células/valores.

Arredondamento bancário

O arredondamento aritmético como em geral conhecemos, e que é utilizado na fórmula ARRED(Valor;CasaDecimal) de planilhas eletrônicas como Excel e Calc, funciona assim:

Para arredondar um número na N-ésima casa decimal, observamos a casa decimal seguinte. Se for um algarismo entre 0 e 4, apenas mantemos o número até a N-ésima casa decimal e descartamos as seguintes (arredondamento “para baixo”). Se for 5 em diante, aumentamos uma unidade na N-ésima casa decimal, além de descartar as casas decimais seguintes (arredondamento “para cima”).

Assim, para arredondamentos na segunda casa decimal, exemplos:

  • 12,3449 ⇒ 12,34 (4 < 5, para baixo)
  • 12,3472 ⇒ 12,35 (7 ≥ 5, para cima)
  • 12,3456 ⇒ 12,35 (5 ≥ 5, para cima)
  • 12,345 ⇒ 12,35 (5 ≥ 5, para cima)

Porém, conforme bem explicado no artigo “Um novo algoritmo para arredondamento de resultados” [PDF], por Daniel Homrich da Jornada, consultor na área de metrologia e Diretor Técnico da Certificar, o caso exato em que a casa decimal seguinte é 5, seguida apenas de zeros (ou seja, sem valores nas casas decimais após a N+1), arredondar sempre para cima gera um erro sistemático tendendo sempre ao acréscimo.

Para equilibrar estatisticamente as possibilidades do arredondamento, há um método comumente denominado “arredondamento bancário” (em inglês, “banker’s rounding”), definido em normas como a ABNT NBR 5891:2014 – Regras de arredondamento na numeração decimal [PDF]; a ISO 80000-1:2009 – Quantidades e unidades — Parte 1: Geral (sucedeu a ISO 31-0:1992), Anexo B – Arredondamento de números, o NIST SP 811:2008 – Guia para o uso de unidades no sistema internacional, Apêndice B.7; a 754-2008 – Padrão IEEE para aritmética de ponto-flutuante, Seção 4.

O “arredondamento bancário” na N-ésima casa decimal pode ser definido em três regras:

  • Quando o algarismo a ser conservado for seguido de algarismo inferior a 5, permanece o número até o algarismo conservado e descartam-se os posteriores (arredonda “para baixo”).
  • Quando o algarismo a ser conservado for seguido de algarismo superior a 5, ou igual a 5 seguido de no mínimo um algarismo diferente de zero, soma-se uma unidade ao algarismo a ser conservado e descartam-se os posteriores (“para cima”).
  • Quando o algarismo a ser conservado for seguido apenas de 5, e todos os posteriores zerados, arredonda-se o algarismo conservado para o par mais próximo, ou seja, se o algarismo a ser conservado for par, apenas descarta-se o 5 posterior (“para baixo”), e se o algarismo a ser preservado for ímpar, soma-se uma unidade ao algarismo a ser conservado, descartando-se o 5 posterior (“para cima”).

Assim, a distinção entre o arredondamento aritmético e o arredondamento bancário ocorre apenas quando posteriormente ao algarismo a ser conservado há exatamente 5 (ou 500…). Enquanto no arredondamento aritmético arredonda-se sempre para cima neste caso, no arredondamento bancário arredonda-se para cima apenas quando o último algarismo a ser conservado é ímpar.

Exemplos deste caso no arredondamento bancário:

  • 12,345 ⇒ 12,34 (4 é par, para baixo)
  • 12,385 ⇒ 12,38 (8 é par, para baixo)
  • 12,315 ⇒ 12,32 (1 é ímpar, para cima)
  • 12,395 ⇒ 12,40 (9 é ímpar, para cima)

Não há até o momento função nativa do Microsoft Excel ou do LibreOffice Calc para esse tipo de arredondamento. O Portal Certificar disponibiliza para download gratuito um suplemento compatível com Excel 2003 e superiores, com a função ArredCertificar(Valor; CasaDecimal) implementada em linguagem VBA (Visual Basic for Applications).

Eu escrevi uma fórmula nativa do Excel/Calc com o mesmo efeito:

=SE(MOD(Valor*10^(Casas+1);20)=5;
TRUNCAR(Valor;Casas);
ARRED(Valor;Casas))

Vamos entendê-la em partes:

  1. Se a parte posterior à casa a ser conservada é exatamente 5 e o último algarismo a ser conservado é par (uma maneira engenhosa de testar as duas coisas ao mesmo tempo é comparar se o resto da divisão inteira desses algarismos por 20 é exatamente 5): SE(MOD(Valor*10^(Casas+1);20)=5);
    1. Arredonda-se para baixo (truncar): TRUNCAR(Valor;Casas)
  2. Caso contrário, segue-se o arredondamento aritmético normal: ARRED(Valor;Casas)

Vi postagens na internet sugerindo outras fórmulas com efeito equivalente. Por exemplo, esta postagem cita a fórmula:

=ARRED(Valor;Casas)-(MOD(Valor*10^(Casas+1);20)=5)/10^(Casas)

A fórmula acima tira proveito de que um teste lógico como MOD(...)=5 vale 1 se VERDADEIRO e 0 se FALSO; quando o algarismo a ser preservado é par, o teste retorna verdadeiro e isso faz subtrair 1 unidade desse algarismo quando o arredondamento aritmético seria para cima.

O “arredondamento bancário” costuma ser aplicado em cálculos financeiros e bancários, medições laboratoriais, aplicações de engenharia e científicas, dentre outras situações. Espero que o conceito e as fórmulas (VBA e nativa) sejam úteis para muitos.

Nota para desenvolvedores: Enquanto as planilhas de cálculo ainda não contemplam essa regra presente em tantas normas internacionais, felizmente há recursos nativos para isso nas principais linguagens de programação, como em Java a opção java.math.RoundingMode HALF_EVEN com números decimais precisos BigDecimal, o DecimalRound do .NET Framework, o decimal.ROUND_HALF_EVEN de Python etc.

Manipulando datas no Excel

O Excel tem uma vasta gama de recursos e funções para cálculos e manipulações com data e hora. Vou citar apenas alguns exemplos relativos a meses, só para ilustrar rapidamente:

  • Quer adicionar uma quantidade de meses a uma data? Tem a função DATAM(cel).
  • Precisa da diferença em meses entre duas datas? DATADIF(cel1; cel2; "m").
  • Quer extrair o número do mês de uma data? MÊS(cel).
  • Qual o último dia do mês correspondente a um data? FIMMÊS(cel; 0) (por exemplo, para 15/2/2020 — ou qualquer outro dia de fevereiro de 2020 — retorna 29/2/2020)

Veja na documentação de suporte da Microsoft a referência completa das 25 funções de data e hora disponíveis no Excel.

O Microsoft Excel armazena datas como números de série sequenciais, para facilitar o uso em cálculos. Um inteiro corresponde a um dia, sendo os horários representados pela parte fracionária. Para ver o número de série de uma data, basta formatar a célula como “Geral”, ao invés de um formato de data.

  • Quer somar 10 dias a uma data? A fórmula simples de adição =cel+10 obtém o resultado.
  • Precisa da diferença em dias entre duas datas? Basta a subtração =cel2-cel1 (a função DATADIF(cel1; cel2; "d") daria o mesmo resultado).

Curiosidade: Por padrão, 1º de janeiro de 1900 é o número de série 1. Isso significa que o Excel tem a limitação de não conseguir representar nativamente datas anteriores ao ano de 1900. Se precisar superar essa limitação, terá que recorrer a programação Visual Basic for Applications (VBA). Ou então, migrar para o software livre LibreOffice Calc, que não tem essa limitação.

O LibreOffice Calc também tem um conjunto de funções para data e hora similar ao Excel.

Referências:

Planilha eletrônica em finanças: centavos × frações

Um erro bastante comum que vejo no uso de planilhas eletrônicas em cálculos financeiros e monetários é o tratamento incorreto de centavos na divisão não-inteira e cálculos com números fracionários com mais de duas casas decimais. Nesse artigo vou abordar o problema e soluções para tratá-lo adequadamente.

Problema: Formatação exibida × valor armazenado

Quando se trabalha com valores monetários (financeiros), é comum usar a formatação de número das células como “Moeda”, “Contábil” ou ainda “Número”, com duas casas decimais. Isso faz a planilha eletrônica exibir os valores sempre arredondados em duas casas decimais, para representar adequadamente os centavos.

Por padrão, essa precisão de casas decimais da formatação é usada apenas na exibição, mas o armazenamento e cálculos do valor usam toda a precisão que a planilha comporta (o Excel 2010, por exemplo, tem precisão máxima de 15 dígitos).

Se o valor da célula tem parte fracionária com mais de duas casas decimais — um caso típico é o resultado de uma divisão não inteira — mas apenas a exibição é formatada com duas casas decimais (para os centavos), isso pode resultar em divergências de arredondamento de centavos, quando esse valor é usado em cálculos.

Veja o exemplo, células das colunas B e C formatadas como moeda (duas casas decimais):

A B C
1 n v n*v
2 3 R$ 0,33 R$ 1,00
3 3 R$ 0,67 R$ 2,00
4 3 R$ 0,33 R$ 0,99
5 3 R$ 0,67 R$ 2,01

Por que aparentemente os cálculos nas linhas 2 e 3 são iguais aos das linhas 4 e 5, mas os valores resultantes da multiplicação (coluna C) são diferentes? Porque os valores armazenados nas células B2 e B3 são as fórmulas =1/3 (0,333333333333333) e =1/4 (0,666666666666667) respectivamente, enquanto nas células B4 e B5 são efetivamente 0,33 e 0,67.

Solução 1: Precisão de cálculo igual à exibição

Uma solução, fácil de aplicar mas que deve ser usada com muito cuidado, porque afeta globalmente a planilha eletrônica e faz com que valores digitados percam definitivamente a precisão de casas decimais excedentes, é utilizar uma opção do Excel que faz com que a precisão de cálculo seja igual ao exibido.

No Excel 2010:

  1. Clique na guia Arquivo, clique em Opções e, em seguida, clique na categoria Avançado.
  2. Na seção Ao calcular esta pasta de trabalho, selecione a pasta de trabalho desejada e, em seguida, marque a caixa de seleção Definir precisão conforme exibido.
  3. Selecione OK.

Observe que o Excel alerta que dados (digitados) perderão definitivamente a precisão (casas decimais excedentes ao exibido). Isso significa que se houvesse um valor digitado e armazenado como 0,666667 na célula formatada como 2 casas decimais, após ativada a opção o valor seria definitivamente convertido para 0,670000. Isso não afeta porém valores que são fórmulas como =2/3.

Referência: Ajuda do Excel 2010 – Alterar a precisão dos cálculos em uma pasta de trabalho.

Solução 2: Utilizar fórmulas de arredondamento

Outra forma de evitar/solucionar essa divergência é utilizar nas células fórmulas que explicitamente arredondem ou trunquem os valores armazenados para duas casas decimais. Existem várias fórmulas para isso. Peguemos o exemplo de tratar a fração a/b (por exemplo, 1/3 ou 2/3):

  • =ARRED(a/b;2) → Arredonda um número (primeiro parâmetro) até a quantidade especificada de dígitos (segundo parâmetro, no caso, 2 casas decimais). A regra de arredondamento é a comum: valor 5 em diante na casa decimal seguinte arredonda para cima, 0 a 4 para baixo (trunca).
  • =ARREDONDAR.PARA.CIMA(a/b;2) → Sempre arredonda um número para cima afastando-o de zero. Ou seja, “para cima” é aumentar o valor absoluto; no caso de valores negativos, -0,333 resulta em -0,34.
  • =TETO(a/b;0,01) → Arredonda um número sempre para cima, para o próximo múltiplo significativo (do segundo parâmetro, no caso, 0,01 = 1 centavo). Para números positivos, funciona igual a ARREDONDAR.PARA.CIMA.
  • TRUNCAR(a/b;2) → Trunca um número até a quantidade de casas decimais especificadas no segundo parâmetro (no caso, 2; se omitido assumiria o padrão 0, para truncar para inteiro).
  • =ARREDONDAR.PARA.BAIXO(a/b;2) → Arredonda um número para baixo, aproximando-o de zero.
  • =ARREDMULTB(a/b;0,01) → Arredonda um número para baixo até o múltiplo ou a significância mais próxima (segundo parâmetro, no caso, 0,01) É a função oposta a TETO.

Os mais comuns são arredondar (ARRED) ou truncar (TRUNCAR).

Referência: Ajuda do Excel 2010 – Arredondar um número.

Curso de Excel Avançado gratis online

O professor Julio Battisti oferece, em seu portal, muitos cursos on-line sobre informática e outros temas de amplo interesse, que podem ser comprados para realização on-line com emissão de certificado de participação por valores módicos.

Além de muitos cursos, estão também disponíveis muitas apostilas (e-books) e videoaulas.

Alguns destes cursos tem seu conteúdo disponível gratuitamente. É o caso do curso de Excel Avançado em 120 lições (http://www.juliobattisti.com.br/excel120avancado/indice.htm), dividido em 6 módulos. São 420 páginas de conteúdo. O programa dos módulos é o seguinte:

  • Módulo 1 – Trabalhando com Listas de dados
  • Módulo 2 – Mais sobre Listas. Exportação e Importação de dados
  • Módulo 3 – Tabelas Dinâmicas
  • Módulo 4 – Análise, Cenários e Consolidação de Dados
  • Módulo 5 – Macros e programação VBA no Excel
  • Módulo 6 – VBA – O Modelo de Objetos do Excel

O curso é aberto ao público, não requer inscrição ou registro, basta acessar o link inicial do curso e ir seguindo as páginas e links dos módulos e lições, acompanhando as exposições e praticando os exercícios no seu Excel.

Nota: A aparência das ilustrações no curso segue a interface existente até o Excel 2003, baseada nos menus e barras de tarefas tradicionais, e não na nova organização de interface integrada de comandos em abas da faixa de opções (em inglês, ribbon) introduzida a partir do Microsoft Office 2007.

No site do professor Batisti, há também a opção de se adquirir o E-Book do curso Excel Avançado (http://www.juliobattisti.com.br/cursos/excelavancado/indice.asp), livro eletrônico com mais de 400 páginas.

Outro dia em uma banca de revistas me chamou atenção o livro Excel Avançado, por Robert Martim, Editora Digerati, por R$ 29,95. Cobre o Excel 2007 e inclui CD-ROM. O que mais me atraiu foi o currículo do autor: Pós-graduado em Finanças pela Universidade de Londres, Microsoft Office 2003 Expert, MCP e MVP Excel. Como o livro estava lacrado, não pude conferir o conteúdo. Alguém conhece?