Excel: Copiar somente células visíveis

No Microsoft Excel, quando você tem uma planilha grande e usa recursos de filtros ou de ocultar linhas/colunas para facilitar a visualização, um recurso que deveria ser fácil na verdade fica em uma opção bem “escondida”:

Como copiar somente as células visíveis?

A resposta, que funciona no Excel 2007 em diante, está no artigo de suporte da Microsoft “Copiar apenas células visíveis” e cujos passos reproduzo e ilustro aqui.

O segredo está em primeiro selecionar apenas as células visíveis, com a opção Página Inicial > Localizar e Selecionar > Ir para Especial > Somente células visíveis. Depois copiar e colar.

  1. Selecione inicialmente o conjunto total de células desejadas.
  2. Na aba “Página Inicial” (ou “Início” em versões mais antigas) do menu do Excel, localize o último grupo da faixa de opções, “Editar”.
  3. Selecione a opção “Localizar e Selecionar” e, no menu que se abre, escolha “Ir para Especial”.
    Localizar e Substituir | Ir para Especial
  4. Na caixa de diálogo que se abre, escolha a opção Selecionar “Somente células visíveis” e OK.
    Selecionar | Somente células visíveis
  5. Agora que somente as células visíveis estão efetivamente selecionadas, execute Copiar (atalho de teclado Ctrl + C).
  6. Vá para a célula inicial a partir de onde deseja colocar as células copiadas e execute Colar (Ctrl + V).

Pronto!

Excel: SOMARPRODUTO Condicional

O Excel tem a função SOMARPRODUTO, que de forma conveniente retorna a soma dos produtos de intervalos (ou matrizes), ou seja, multiplica posição a posição os fatores de cada intervalo (valor na posição i de cada intervalo) e soma os produtos resultantes.

Vou mostrar um exemplo prático de SOMARPRODUTO extraído do artigo em Blog dos Cursos, por André Victor, onde vi a dica que vou apresentar neste artigo. Suponha que você tenha tabelado o preço unitário (coluna D) e a quantidade em estoque (coluna C) de vários produtos em duas filiais (Unidade 1 e Unidade 2) de uma rede de lojas, conforme ilustrado a seguir, com a fórmula exibida:

Para obter o valor total de produtos em estoque, basta usar a função SOMARPRODUTO dos intervalos C2:C9 (quantidades) e D2:D9 (respectivos preços unitários). Ou seja: 10*35,00 + 15*45,00 + … + 20*60,00 = 6.470,00, conforme resultado a seguir:

Mas e se quisermos somar condicionalmente, apenas por uma das filiais? O Excel não tem uma função “SOMARPRODUTOSE”, como tem a função SOMASE. Mas existe um truque! Se você tomar o intervalo A2:A9 onde estão os nomes da filiais, (A2:A9="Unidade 1") retorna uma lista (VERDADEIRO;FALSO;VERDADEIRO;…;FALSO). Se você utilizar esses valores lógicos em uma conta algébrica, o Excel converte automaticamente VERDADEIRO = 1 e FALSO = 0. Então, se aplicarmos a esse intervalo uma conta simples que retorne o próprio valor algébrico, como multiplicar por 1 (1*) ou inverter o sinal duas vezes (--), teremos 1*(A2:A9="Unidade 1") ou --(A2:A9="Unidade 1") resultando em (1;0;1;…0). Essas operações envolvem as diretrizes de fórmulas em matrizes no Excel que já abordei em outro artigo.

Pronto, utilize esse terceiro intervalo como parâmetro em SOMARPRODUTO e ele multiplicará por 0 onde não for Unidade 1, o que terá o efeito de desconsiderar esse produto (resultado 0) e multiplicará por 1 onde for Unidade 2, considerando o valor de cada produto onde for verdadeira a condição lógica. Ou seja: 10*35,00*1 + 15*45,00*0 + … + 20*60,00*0 = 3.925,00 Veja a fórmula aplicada na imagem do exemplo extraído do artigo citado:

Aplique a mesma lógica para a outra filial (Unidade 2) e terá o seguinte resultado final:

Engenhoso, né? Espero que tenha sido útil e agradeço a André Victor pela dica original e pelas imagens reproduzidas aqui.

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.

Esse desequilíbrio pode ser facilmente ilustrado: imagine as situações em que há apenas a casa decimal seguinte; são 10 possibilidades (0 a 9). Quando ela é zero, não há acréscimo nem decréscimo (estatisticamente, 10% das possibilidades); quando é 1, 2, 3 ou 4, são as 4 possibilidades em que há decréscimo (40%); e quando é 5, 6, 7, 8 ou 9, são 5 possibilidades em que há acréscimo (50%). Ou seja, há mais possibilidades de acréscimo do que de decré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?