Evite quebrar fórmulas / formatação movendo células na planilha bloqueada

Question

Imagine a seguinte tabela do Excel:

B6é=ZÄHLENWENN(A3:C4;"<>") ( COUNTIF , eu acho, em inglês), então conta a quantidade de alunos sentados na sala. O professor pode inserir seus nomes em A3:C4 e a fórmula conta. Eu também coloco uma formatação que pinta os assentos que não estão ocupados em cinza.

Eu quero que o professor edite os nomes, mas não a fórmula, então eu tranquei o planilha após desbloquear A3:C4 .

MaseseOlivequisersesentaraoladodeVerena?Quandooprofessorapenasmoveseucelular,elequebraaformatação,emboraelenãodeveriapoder!Àsvezes,fórmulasdependentesdascélulasquesãomovidosseatrapalhardepoistambém.

Então,comopossoevitarqueousuáriodestruaformataçõesefórmulas?

Editarpara@RajeshS:

Imagine,amesadoprofessorestáparadanomeiodasalaequeremosverqualalunoestásentadoaoladodela.Afórmula,colocaremB7,seria=WENN(B3="";"—";B3) (se B3 for "" , retornar "—" , senão retornar B3 ). Quando Olive estiver em B3 , a fórmula retornará Olive . Se o professor mover Olive para C4 , a fórmula ainda retornará Olive .

Editar para @RajeshS e clareza :

Eu não estou falando sobre a edição dos valores das células. Quando eu digo movimento , quero dizer clique em Olive, arraste-o para o lugar vazio e solte-o lá .

Editar para @RajeshS:

Foi o que fiz para obter sua solução proposta:

1. Nova folha de trabalho do Excel
2. Ir para o celular A66
3. Colspan A66: C66
4. Insira o texto Desk position (row)
5. Ir para o celular A68
6. Formatação condicional =ISTLEER(A68) on =$A$68:$C$69 (cor de preenchimento)
7. Ir para o celular A71
8. Insira o texto Total students
9. Ir para o celular B71
10. Insira o texto =ZÄHLENWENN(A68:C69;"<>"&"")
11. Ir para o celular A73
12. Colspan A73: C73
13. Insira o texto Students position
14. Insira os textos Name , Original e Current em A74, B74 e C74
15. Insira os textos Adam , Olive , Claus , Bertram e Verena em A75, A76, A77, A78, A79
16. Insira os textos 1 , 2 , 3 , 4 , 5 e 6 em B75, B76, B77, B78, B79, B80
17. Ir para a célula C75
18. Insira a fórmula =KKLEINSTE(WENN($A75=$A$68:$C$69; ZEILE($A$68:$C$69)-ZEILE($A$68)+1); ZEILE($1:$1)) e crie o {} com Shift + Enter
19. Aumenta a fórmula até C80
20. Insira os nomes dos alunos na área colorida
21. Edite o formato de A66: C66 (desmarque o sinalizador de bloqueio)
22. Aplicar bloqueio de planilha (desmarque todas as permissões, exceto a seleção de células desbloqueadas)
23. É assim que o professor encontra a tabela: exatamente como na sua primeira captura de tela
24. Ele clica em Olive e arrasta-a para C69
25. A coluna do C75 agora tem os valores 1, 2, 1, 2, 2, 1 como na segunda captura de tela, mas o B68 não é preenchido com a cor definida na formatação condicional. Se ele tivesse apagado ela de seu lugar original e re-digitado ela para o novo, teria funcionado. E exatamente este é o problema : um usuário pode modificar partes de uma planilha que não deve ser capaz de modificar movendo as células.

Answer 1

Agora estou sugerindo uma solução comparativamente melhor. Verifique as capturas de tela abaixo.

Assento original:

OlivedeAssentosModificadosaoladodeVerena:

Agora,comoconseguirtudoisso.

1. Escrevaestafórmulaparacontaroaluno,=COUNTIF(A68:C69,"< >" & "")
2. Selecione o assento original (intervalo de dados, A68: C69).
3. Aplique a formatação condicional, use essa fórmula, = ISBLANK (A68).

4. Desenhe os dados como eu fiz e escreva esta fórmula em C75 (Red Cell) , arraste até o fim.

   {= PEQUENO (SE ($ A75 = $ A $ 68: $ C $ 69, LINHA ($ A $ 68: $ C $ 69) -ROW ($ A $ 68) +1), LINHA ($ 1: $ 1))}

NB:

A fórmula do CSE é concluída com Ctrl + Shift + Enter.

Você tem 6 Assentos (Células), onde último Assento (Celular) em Branco. Basta verificar a POSIÇÃO DO ESTUDANTE, depois de Verena, a Célula ficar em branco, o lugar é 6 & Row é 2, que muda quando você muda Olive junto a Verena, também a posição de Olive's Row.

Para todas as 3 fórmulas altere o intervalo de dados conforme suas necessidades .

Espero que isso ajude você.