Обработка исторических данных и формул Excel

Question

У меня есть большой объем данных в листе Excel, который пользователи вводят напрямую, добавляя новые строки с новыми данными. Каждая новая строка данных представляет клиента. У каждого клиента будет ячейка с: округом, лидером, ценой и отделом.

Что пользователи, добавляя новые данные, сначала выбирают округ, в зависимости от округа у них будет выпадающий список с разными лидерами, которые работают в этом округе для ячейки лидеров.

До сих пор столбец цены и отдела был заполнен приведенным ниже кодом "Excel". Код просто принимает значение лидера, ищет его в таблице и находит строку в таблице, где находится лидер, и возвращает номер строки в функцию индекса, которая затем может выбрать соответствующую цену и отдел.

INDEX('Pres data'!D$5:D$141;COMPARE(W524;'Pres data'!C$5:C$141;0)))

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

Я мог бы скопировать текущие значения ячейки отдела и вручную вставить ее в клиентов, которые не должны быть изменены, но это не кажется оптимальным решением.

Надеюсь, вы сможете понять настройки. :) Я понятия не имею, как справиться с проблемой, и верю, что это в первую очередь наша собственная ошибка.

Поэтому любые идеи о том, как обращаться с историческими ценностями в Excel, также были бы хорошими.

Answer 1

Я бы рекомендовал оставить исторические данные и функции на месте (возможно, переименование их отражает их статус) и создать новые таблицы и функции для новых данных. Это позволило бы кому-то увидеть, как рассчитывались исторические данные.

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

Answer 2

Есть несколько подходов к этому.

Если вы не хотите копировать существующие ячейки и вставлять их в качестве значений, вы можете ввести новый столбец, в котором указывается, какой код следует использовать. Вставьте новый столбец перед столбцом A. Для исторических значений введите "A", для новых значений введите "B". Вы можете использовать формулу, чтобы гарантировать, что каждая новая строка теперь получает значение B, например, если последнее историческое значение было введено в строке 524, и после этого нужно искать только новые значения, используйте формулу, подобную этой:

=if(row()>524;"B";"A")

(Я не знаю, что IF() и ROW() на вашем языке, но я надеюсь, что вы можете это выяснить.) Затем вы можете скрыть столбец А.

Затем добавьте новый столбец в таблицу поиска данных Pres. В столбце E добавьте новые значения. В ячейке D4 введите "A", в E4 введите "B". Затем вы можете изменить формулу поиска во всей таблице

=INDEX('Pres data'!D$5:E$141;COMPARE(W524;'Pres data'!C$5:C$141;0);COMPARE(A524;'Pres data'!$D$4:$E$4;0))