Как ссылаться на значение строки в таблице в Excel 2007 из другой строки, чтобы таблица сортировки не меняла его?

Question

Рассмотрим таблицу с именем сотрудника, возрастом и менеджером сотрудника. Менеджер сотрудника - это ссылка на имя сотрудника в той же таблице, но в другой строке. Я попробовал это, ссылаясь на ячейку = A3

Теперь, если я отсортирую эту таблицу по возрасту, столбец менеджера сотрудника будет испорчен и не будет указывать на первоначально назначенного менеджера. Вместо этого он указывает на то, что относительно старого ряда.

Итак, как ссылаться на значение строки (manager) в таблице в Excel 2007 из другой строки (employee), чтобы таблица сортировки (по возрасту) не меняла его?

Answer 1

Самый простой способ - создать копию столбца Employee Name за пределами диапазона таблицы. Это может быть отсортировано или нет, не имеет значения. Это может быть один и тот же лист, другой лист или даже другая рабочая тетрадь, но не часть таблицы.

Затем в столбце « Manager поместите формулы, ссылающиеся на этот новый диапазон, в формате $E$1 (при условии, что копия находится в столбце E).

Answer 2

Я бы не советовал сортировать таблицы, содержащие формулы, особенно со ссылками на другие ячейки в той же таблице. Если ваши данные не являются динамическими (т. Е. Они обязательно должны сохранять все формулы), я бы скопировал таблицу и вставил только значения (используя Специальную вставку) обратно в таблицу.

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

Answer 3

У вас есть столбец идентификатора сотрудника? Если нет, добавьте серийный номер каждому сотруднику, затем вы можете выполнить VLOOKUP на основе идентификатора сотрудника менеджера, чтобы получить имя менеджера.

Документация Microsoft по VLOOKUP

Посмотрите на мой пример:

Столбец A - это идентификатор сотрудника, столбец B - это имя сотрудника, столбец C - это идентификатор сотрудника менеджера, а столбец D - это имя менеджера.

Формула, которую я использую в D1 =VLOOKUP(C1,$A$1:$B$5,2,FALSE)

Установив для четвертого параметра значение false, я сказал VLOOKUP, что данные не сортируются.

По умолчанию или когда параметр явно равен true, VLOOKUP предполагает, что данные отсортированы, и находит приблизительное значение. Установка его в ложь заставляет VLOOKUP найти точное совпадение.

Обратите внимание, что строка 1 не имеет значения в столбце C. У Аллана нет менеджера, а D1 показывает # N/A.

Answer 4

Вам нужна другая таблица где-то еще (например, другая рабочая таблица в том же файле), которая содержит отношение Employee-Manager. Там вы можете сослаться на имя менеджера из существующего списка сотрудников, как вы это делаете в исходной электронной таблице.

Сделав это, вы можете использовать функцию VLOOKUP, чтобы найти менеджера сотрудника по имени сотрудника. Учитывая ваш пример выше, для содержимого для ячейки C2 будет =Vlookup(A2,Sheet2!$A$2:$B$6,2) .

Это синтаксис для VLOOKUP: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) . Поэтому в приведенном мною примере lookup_value = ячейка сотрудника, table_array = новая таблица сотрудник-менеджер и col_index_num = 2, поскольку вы хотите вернуть 2-й столбец таблицы сотрудник-менеджер (имя менеджера). Это предполагает, что вы поместили новую таблицу сотрудник-менеджер на листе 2 и начали перечислять имена в ячейке A2.

Несмотря на то, что ваш список имен "огромный", вы сможете скопировать / вставить его на новый лист, чтобы сэкономить время при наборе текста.

Надеюсь, я объяснил это достаточно четко. Кроме того, чтобы VLOOKUP работал правильно, таблица_array должна быть отсортирована в алфавитном порядке. Использование справки на VLOOKUP даст вам больше информации.

Answer 5

Используйте абсолютную ссылку в этих ячейках, и она должна сортироваться для вас. Например; =$A$3