Como contar ou somar células com base na cor da célula em uma planilha do Google?
No trabalho diário com planilhas, você pode encontrar situações onde precisa contar ou somar valores de células de acordo com a cor de fundo específica da célula, conforme ilustrado na captura de tela abaixo. Por exemplo, você pode querer contabilizar ou totalizar apenas as células destacadas em uma cor específica para analisar rapidamente os dados por categoria ou status. Este guia abordará como realizar isso não apenas no Google Sheets, que não possui suporte nativo para cálculos baseados em cores, mas também no Microsoft Excel, que oferece várias abordagens diferentes, desde recursos embutidos até utilitários avançados.
Compreender como lidar com análises de dados baseadas em cores pode tornar seu trabalho mais eficiente, especialmente quando as cores são usadas para marcar status, prioridades ou categorias. Também discutiremos diferentes soluções, compararemos seus cenários de uso e forneceremos dicas práticas de operação, além de lembretes de erro para garantir que suas tarefas prossigam sem problemas.
- Contar valores de células com base na cor da célula com script em uma planilha do Google
- Somar valores de células com base na cor da célula com script em uma planilha do Google
- Contar ou somar valores de células pela cor da célula com Kutools para Excel no Microsoft Excel
Contar valores de células com base na cor da célula com script em uma planilha do Google
O Google Sheets não oferece uma opção direta para contar células com base na cor de fundo. No entanto, você pode alcançar isso com um Apps Script personalizado. Esse script atua como uma função definida pelo usuário, permitindo que você a referencie como uma fórmula. Aqui está como configurar e usar o script:
1. Clique em Ferramentas > Editor de scripts para acessar o ambiente de script. Veja a captura de tela:
2. Na janela do projeto, selecione Arquivo > Novo > Arquivo de script para abrir um novo módulo de código, conforme mostrado:
3. Quando solicitado, insira um nome para o novo código de script e confirme. Nomeie o script de maneira significativa para ajudar a identificar sua finalidade posteriormente.
4. Clique em OK, depois copie e cole o seguinte código para substituir qualquer código de amostra no módulo. Certifique-se de colá-lo exatamente como é fornecido.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Salve este arquivo de script, retorne à sua planilha e use a nova função assim como qualquer fórmula do Google Sheets. Insira: =countcoloredcells(A1:E11,A1) em uma célula em branco para contar células no intervalo A1:E11 que correspondem à cor de A1. Pressione Enter para obter o resultado. Se for solicitado permissão, autorize o script para ser executado na sua planilha.
Nota: A1:E11 é o intervalo dos seus dados; A1 é a célula de referência da cor para contar. Certifique-se de que as células de referência tenham exatamente a mesma cor e evite células mescladas para maior confiabilidade.
6. Para contar outras cores, repita a fórmula com uma célula de referência de cor diferente, conforme necessário. Se o intervalo mudar, ajuste o intervalo na fórmula de acordo.
Se você receber um erro ou um resultado inesperado, verifique novamente se o script foi salvo e se você usou a referência de cor correta. Funções baseadas no Apps Script só recalculam se a função ou seus argumentos mudarem – se você recolorir suas células mais tarde, re-insira a fórmula ou pressione Enter novamente para atualizá-la.
Somar valores de células com base na cor da célula com script em uma planilha do Google
Somar valores de células com base em uma cor de célula definida no Google Sheets requer uma abordagem semelhante usando um Apps Script. Isso é especialmente útil para planilhas financeiras, logs de status ou qualquer cenário onde as cores representam categorias com dados numéricos subjacentes.
1. No Google Sheets, abra o Editor de scripts via Ferramentas > Editor de scripts. Na janela do projeto, selecione Arquivo > Novo > Arquivo de script para adicionar um novo módulo de código. Forneça um nome exclusivo no prompt para ajudar a rastrear sua finalidade, como "SomarCélulasColoridas." Confirme para criar o módulo.
2. Clique em OK, e na nova janela do módulo de código, substitua qualquer código padrão colando o script fornecido para somar células coloridas. Certifique-se cuidadosamente de que todo o código seja copiado, pois caracteres ausentes podem resultar em erros de sintaxe.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. Após salvar o script, retorne à sua planilha e insira a fórmula =sumcoloredcells(A1:E11,A1) em uma célula em branco, depois pressione Enter. Essa fórmula soma os valores em A1:E11 onde a cor de fundo corresponde à de A1. Ao usar essa função, certifique-se de que todas as células de soma-alvo sejam numéricas; valores não numéricos serão ignorados.
Nota: A1:E11 representa o intervalo de seus dados, e A1 fornece a referência de cor. A fórmula somente somará valores numéricos visíveis – certifique-se de que células mescladas ou erros dentro do intervalo não afetem seus totais.
4. Você pode replicar o processo acima para somar valores para diferentes categorias de cores alterando a célula de referência de cor na sua fórmula. Se seus dados forem atualizados ou você alterar as cores de fundo, lembre-se de atualizar a fórmula para obter resultados atualizados.
Se a soma retornar zero ou um valor de erro, verifique se o intervalo contém números e se a correspondência de cor é exata. Além disso, o recálculo não é automático se apenas a cor da célula mudar – edite a célula da fórmula para forçar uma atualização.
Contar ou somar valores de células pela cor da célula com Kutools para Excel no Microsoft Excel
Ao trabalhar no Microsoft Excel, contar ou somar células por cor é um requisito frequente, especialmente em relatórios de gerenciamento de projetos, inventário ou controle de qualidade. O Kutools para Excel oferece um utilitário dedicado Contar por Cor, que permite obter contagens e totais diretamente por cor de fundo ou cor da fonte – isso é particularmente útil para intervalos maiores de dados e quando você precisa de resultados rápidos e repetíveis.
Uma vez instalado o Kutools para Excel, prossiga com os seguintes passos:
1. Destaque o intervalo onde deseja contar ou somar por cor, depois clique em Kutools Plus > Contar por Cor. Consulte a captura de tela abaixo para orientação:
2. A caixa de diálogo Contar por Cor aparece. Defina Formatação Padrão em Modo de Cor e escolha Fundo para Tipo de Contagem. Verifique cuidadosamente a visualização e as opções:
3. Clique em Gerar Relatório para criar uma nova planilha contendo uma divisão das contagens e somas para cada cor presente no seu intervalo. Este relatório inclui tanto o número quanto a soma das células coloridas, permitindo fácil referência ou análise adicional.
Nota: Esse recurso também pode calcular valores com base na formatação condicional ou cor da fonte. Use regras de formatação condicional para análises dinâmicas; caso contrário, a ferramenta funciona melhor para preenchimentos de cor estáticos. Quaisquer alterações nas cores das células de origem exigiriam a execução novamente do utilitário Contar por Cor para resultados atualizados. Se você enfrentar problemas, confirme que o Kutools está ativo e atualizado.
Clique para Baixar e Experimentar Gratuitamente o Kutools para Excel Agora!
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins