Зеркальная колонка на отдельном листе в Excel 2010

Question

Я пытаюсь отразить столбец из одного листа Excel в другой в той же книге Excel. Цель состоит в обновлении второго листа при редактировании первого листа. По сути, когда я добавляю / удаляю строки на первом листе, он будет делать то же самое автоматически на втором листе.

В моей колонке есть имена сотрудников, и когда люди будут входить / выходить из офиса, я буду добавлять / удалять их соответственно. Мои отчеты отслеживают разные данные, но сотрудники все одинаковы. Мой первый лист - это основной лист, а остальные получают данные соответственно. Будет ли работать формула отражения? Кроме того, как вы можете применить его ко всему столбцу?

Answer 1

Здесь идет идея без VBA: "смещение".

Допустим, ваши исходные данные находятся в "Sheet1", и вы хотите отразить каждую ячейку в "Sheet2". Для любой ячейки на Sheet2 вы можете использовать:

=OFFSET(Sheet1!$A$1,Row(A1)-1,COLUMN(A1)-1,1,1)

• Sheet1!$A$1: ваша основная ссылочная ячейка. Это единственная ячейка, которую вы не можете переместить или удалить. Вы можете оставить столбец 1 пустым и скрыть его, чтобы этого не происходило.
• Row(A1)-1: «Строка (А1)» определит, в каком ряду Листа 2 вы находитесь. «-1» - нейтрализовать смещение (вы не хотите читать ту же строку, в которой находитесь, а не следующий).
• COLUMN(A1)-1: то же, что и выше, но для определения вашей колонки.
• ,1,1): устанавливает размер диапазона для ячейки (1 на)

Пока вы не удалите свою основную ячейку ссылки (A1, для этого примера), я считаю, что она должна работать, независимо от того, что вы делаете на Sheet1.

Несколько проблем, которые вы можете найти с этим методом:

1. Если между данными есть пробелы (на листе 1), может потребоваться некоторое форматирование или дополнительные формулы, чтобы избежать появления большого числа нулей.
2. Если вы попытаетесь использовать какую-либо формулу в качестве "счетчика" или "среднего", результаты могут изменить эти нули (вы можете их скрыть, но они все равно будут влиять на формулы.

Answer 2

это не может быть сделано с простой формулой. Если вы добавляете или удаляете строки в Sheet1, формулы будет недостаточно для добавления или удаления строк в Sheet2. Вам нужен другой подход для этого.

Одним из вариантов является создание VBA, который управляет удалением и добавлением новых строк.

Другим способом может быть скрытие строк вместо их удаления. Добавьте столбец, в котором сотрудник помечен как активный или неактивный. Затем отфильтруйте таблицу, чтобы скрыть неактивных сотрудников.

Формула может использоваться для извлечения имен из Sheet1 в Sheet2, а также для извлечения активных / неактивных значений для каждой строки. Например, если имя находится в столбце А на листе 1, используйте его в листе 2

= Индекс (Лист1!$ A:$ A, строка ())

Если статус (активный / неактивный) находится в столбце B, используйте =index(Sheet1!$A:$A,row()) в другом столбце, а затем фильтр по этому столбцу.

Однако существует одна большая опасность: если на листе 1 изменяется порядок сотрудников, то все данные на листе 2 будут неверными. Порядок сотрудников будет по-прежнему соответствовать порядку на листе 1, но остальные данные не будут перемещаться с новым порядком сортировки.

Подход с VBA был бы более надежным.