Предотвращение нарушения формул / форматирования путем перемещения ячеек в заблокированном рабочем листе

Question

Представьте себе следующую таблицу Excel:

B6 =ZÄHLENWENN(A3:C4;"<>") (мне кажется, COUNTIF на английском языке), поэтому он подсчитывает количество учеников, сидящих в комнате. Учитель может вставить свои имена в A3:C4 и формула рассчитывает. Я также поместил в форматирование, которое окрашивает места, которые не заняты серым.

Я хочу, чтобы учитель редактировал имена, но не формулу, поэтому я заблокировал рабочий лист после разблокировки A3:C4 .

Но что, если Олив хочет сесть рядом с Вереной? Когда учитель просто перемещает ее ячейку, он нарушает форматирование, хотя он не должен быть в состоянии! Иногда формулы, зависящие от перемещаемых ячеек, также могут испортиться.

Итак, как я могу предотвратить уничтожение пользователем форматирования и формул?

Редактировать для @Rajesh S:

Представьте, что учительский стол стоит в середине перед классом, и мы хотим увидеть, какой ученик сидит рядом с ним. Формула, указанная в B7 , будет иметь вид =WENN(B3="";"—";B3) (если B3 равен "" , вернуть "—" , иначе вернуть B3). Когда Olive сидит в B3 , формула возвращает Olive . Если учитель переместит Olive в C4 , формула все еще возвращает Olive .

Отредактируйте для @RajeshS и внесите ясность:

Я не говорю о редактировании значений ячеек. Когда я говорю « двигаться», я имею в виду щелчок по Оливе, перетащить его на свободное место и бросить туда.

Изменить для @RajeshS:

Вот что я сделал, чтобы получить предложенное вами решение:

1. Новый лист Excel
2. Перейти к ячейке A66
3. Colspan A66:C66
4. Введите текст Desk position (row)
5. Перейти к ячейке A68
6. Условное форматирование =ISTLEER(A68) для =$A$68:$C$69 (цвет заливки)
7. Перейти к ячейке A71
8. Введите текст Total students
9. Перейти к ячейке B71
10. Введите текст =ZÄHLENWENN(A68:C69;"<>"&"")
11. Перейти к ячейке A73
12. Colspan A73:C73
13. Введите текст Students position
14. Введите тексты Name , Original и Current в A74, B74 и C74
15. Введите тексты Adam , Olive , Claus , Bertram и Verena в A75, A76, A77, A78, A79
16. Введите тексты 1 , 2 , 3 , 4 , 5 и 6 в B75, B76, B77, B78, B79, B80
17. Перейти к ячейке C75
18. Введите формулу =KKLEINSTE(WENN($A75=$A$68:$C$69; ZEILE($A$68:$C$69)-ZEILE($A$68)+1); ZEILE($1:$1)) и создайте {} с помощью Shift+Enter
19. Увеличить формулу до C80
20. Введите имена студентов в цветной области
21. Отредактируйте формат A66:C66 (снимите флажок блокировки)
22. Применить блокировку листа (снимите отметку с каждого разрешения, кроме выбора разблокированных ячеек)
23. Вот как учитель находит таблицу: точно так же, как на первом скриншоте
24. Он нажимает на Оливу и тащит ее в C69
25. Столбец из C75 теперь имеет значения 1, 2, 1, 2, 2, 1, как на втором снимке экрана, но B68 не заполнен цветом, определенным в условном форматировании. Если бы он удалил ее с ее первоначального места и набрал ее заново, это сработало бы. И именно в этом и заключается проблема: пользователь может изменять части листа, которые он не должен изменять, перемещая ячейки.

Answer 1

Теперь я предлагаю вам сравнительно лучшее решение. Проверьте скриншоты ниже.

Оригинальное размещение:

Модифицированная посадочная олива рядом с Вереной:

Теперь, как этого добиться.

1. Запишите эту формулу для подсчета числа студентов = COUNTIF(A68:C69, "<>" & "")
2. Выберите исходное размещение (Диапазон данных, A68:C69).
3. Применить условное форматирование, используйте эту формулу, = ISBLANK (A68).

4. Нарисуйте данные, как я сделал, и напишите эту формулу в C75 (Red Cell), перетащите до последнего.

   {= МАЛЕНЬКИЙ (ЕСЛИ ($ A75 = $ A $ 68:$ C $ 69, ROW ($ A $ 68:$ C $ 69)-ROW ($ A $ 68)+1), ROW ($ 1:$ 1))}

NB:

Это формула CSE, поэтому закончите с помощью Ctrl+Shift+Enter.

У вас есть 6 мест (ячеек), где последнее место (ячейка) в пустой. Просто проверьте ПОЛОЖЕНИЕ СТУДЕНТА, после того, как ячейка Верены оставлена пустой, ее место равно 6, а строка равна 2, которая изменяется по мере того, как вы перемещаете Оливу рядом с Вереной, а также позицию Оливковой строки.

Для всех 3 формул измените диапазон данных в соответствии с вашими требованиями.

Надеюсь, это поможет вам.