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

Question

Ao classificar uma tabela ou intervalo no Excel, os valores da célula e a formatação básica, como negrito, itálico, cores, etc., são movidos, mas as seguintes ações NÃO se movem:

• Bordas
• Regras de validação de dados

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

Veja um exemplo antes de classificar:

Edepoisdaclassificação(A-Znonome"Fruit".)

ExisteumamaneiradefazeroExcelmoverasregrasdevalidaçãodedadosjuntocomosvaloreseformatosquandooExcelexecutaumaoperaçãodeclassificação?

Editar:

Sóparaesclarecer,tenhoduaspreocupaçõesseparáveisaqui:

1. Preocupaçãoprincipal:seumusuáriodecidirclicarnobotãoClassificarounaopçãoClassificaremumcabeçalhodacolunaFiltro,issoestragaminhasregrasdevalidaçãodedados(complexas!).
2. Preocupaçãosecundária:serialegalseminhaplanilhapudesseserclassificadausandoosbotõesinternosdoExcelparaclassificação(essassãoasformasmaisóbviasdeclassificarosdados).Umamacroseparadaapenasparafazerumaespéciepareceumpoucohacky.

Editar:

Minhasoluçãofinalfoiapenasdesativaraclassificaçãoporcompleto,protegendoapastadetrabalhoedesativandoaclassificaçãodetodasasplanilhas.Vocêpodepressionarosbotões"Ordenar", mas eles não fazem nada.

Como uma salvaguarda adicional, adicionei o VBA que reaplica o Protect Workbook sempre que a pasta de trabalho é aberta e abre uma planilha com instruções de uso e avisos (como "Don't sort this!"). o usuário pode cancelar a proteção da pasta de trabalho a qualquer momento, portanto, isso não é infalível, mas acredito que é o melhor que posso fazer.

    

Answer 1

Você pode criar uma regra geral para manipular os valores de pH e aplicá-los a toda a coluna de valores. Você só precisa incluir uma condição que verifique a Chave1 para ver se o valor é um pH; se for, verifique os critérios; se não, apenas retorne TRUE para que tudo seja permitido.

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

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

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

Vocêpodemanipularbordasdemaneirasemelhanteseasaplicarusandoformataçãocondicional.Bastaaplicá-loatodososdadosdacolunacomumaregradefórmulaapropriada,como

=B2="pH"

    

Answer 2

Olhando para bits de código antigo, parece que eu deletei e recriou os validadores:

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

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

    

Answer 3

Esta não é inteiramente uma resposta à sua pergunta, mas eu sinto que isso deve ser mencionado.

A maneira 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 simples de inserir dados como o seu é ter uma coluna por tecla (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 precisa se preocupar com a classificação.

Se você precisar acessar dados usando uma chave e um valor, pode 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 bem definidas (caso contrário, um motivo para usar 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 um tipo de formulário de entrada. Você não esquecerá de inserir um valor para uma fruta, pois a célula desse valor estará vazia.

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

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