A classificação falha ao mover as regras de validação de dados junto com os dados no Excel

Question

Ao classificar uma tabela ou intervalo no Excel, os valores das células e a formatação básica, como negrito, itálico, cores, etc., são movidos, mas o seguinte NÃO se move:

• Fronteiras
• Regras de validação de dados

Minha planilha possui validação de dados por célula, portanto, quando executo uma operação de classificação, ela move os valores, mas não carrega sua validação com eles.

Aqui está um exemplo antes de classificar:

E após a classificação (AZ no nome "Fruta").

Existe uma maneira de fazer o Excel mover as regras de validação de dados junto com os valores e formatos quando o Excel executa uma operação de classificação?

Editar:

Só para esclarecer, tenho duas preocupações separáveis ​​aqui:

1. Preocupação principal: se um usuário decidir clicar no botão Classificar ou na opção Classificar no cabeçalho da coluna Filtro, isso irá destruir minhas (complexas!) regras de validação de dados.
2. Preocupação secundária: Serialegalse minha planilha pudesse ser classificada usando os botões integrados do Excel para classificação (essas são as maneiras mais óbvias de classificar dados). Uma macro separada apenas para fazer uma classificação parece um pouco hackeada.

Editar:

Minha solução final foi desabilitar totalmente a classificação, protegendo a pasta de trabalho e desabilitando a classificação para todas as planilhas. Você pode pressionar os botões "Classificar", mas eles não fazem nada.

Como proteção adicional, adicionei o VBA que reaplica o Protect Workbook sempre que a pasta de trabalho é aberta e traz uma planilha com instruções de uso e avisos (como "Não classifique isso!"). -proteger a pasta de trabalho a qualquer momento, então isso não é infalível, mas acredito que é o melhor que posso fazer.

Answer 1

Você pode criar uma regra geral para lidar com valores de pH e aplicá-la a toda a coluna de valores. Basta incluir uma condição que verifique Key1 para ver se o valor é um pH; se for, verifique os critérios; caso contrário, apenas retorne TRUE para que tudo seja permitido.

Por exemplo, aqui está uma regra de validação de dados personalizada aplicada à C2:C6(coluna Valor 1) em sua tabela:

=IF(B2="pH",AND(C2>=0,C2<=14,C2=INT(C2)),TRUE)

Isso limitará os valores de pH 0<=pH<=14 e apenas números inteiros. Como a regra é aplicada a toda a coluna, a classificação não afetará a validação dos dados.

Você pode lidar com bordas de maneira semelhante se aplicá-las usando formatação condicional. Basta aplicá-lo a todos os dados da coluna com uma regra de fórmula apropriada, como

=B2="pH"

Answer 2

Olhando para pedaços de código antigo, parece que excluí e recriei os validadores:

var cellValues = activeSheet.Range[leftColumn + startingRow, Type.Missing];
cellValues.Validation.Delete();

Se você quiser fazer algo no Excel de forma programática, grave uma Macro > faça a operação manualmente > Pare a gravação da Macro > pressione Alt + F11 e visualize o código no Módulo recém-criado. Esta é uma maneira rápida de aprender a programação VBA. Boa sorte!

Answer 3

Esta não é inteiramente uma resposta à sua pergunta, mas acho que deveria ser mencionada.

A forma como seus dados são inseridos na planilha é um pouco incomum e, se não houver motivos específicos, você também pode resolver o problema alterando um pouco o design da planilha. A maneira mais direta de inserir dados como os seus é ter uma coluna por chave (Cor, pH, Diâmetro, etc.) e uma linha por Fruta. Dessa forma você pode definir regras e estilos para toda a coluna e não terá que se preocupar com a classificação.

Se você precisar acessar dados usando uma chave e um valor, poderá ser ajudado usando tabelas dinâmicas e pesquisa de tabela dinâmica (GETPIVOTDATA) ou pesquisas de linha/coluna (VLOOKUP/HLOOKUP).

No seu caso, as chaves parecem muito bem definidas (caso contrário, é um motivo para adotar a abordagem de valor-chave) e, como o Excel permite milhares de colunas, você também não ficará sem chaves. Usar uma coluna separada para cada chave também funcionará como uma espécie de formulário de entrada. Você não se esquecerá de inserir um valor para uma fruta, pois a célula desse valor estará vazia.

Se você tiver diversas amostras (nem todas as maçãs têm o mesmo diâmetro, etc.), você poderá tratar cada linha da planilha como uma única amostra.

Esta abordagem também facilitará o processamento adicional dos dados, como a criação de diagramas e tabelas dinâmicas.