Excel, обновлять дубликаты автоматически

Question

У меня есть таблица, что-то вроде этого:

--------------------------------------------------------------------------------------
|Id | Name                    | Category | Sub-Category     | Supplier
--------------------------------------------------------------------------------------
| 1 | Orange juice "TASTY"    | Beverage | Juice            | Le Beverage supplier
--------------------------------------------------------------------------------------
| 2 | Canned tuna "LUNA TUNA" | Food     | Canned           | Food Supplier
--------------------------------------------------------------------------------------
| 3 | Pepsi, just Pepsi       | Beverage | Soft Drink       | Le Beverage supplier
--------------------------------------------------------------------------------------
| 4 | Fanta, just fanta       | Beverage | Soft Drink       | Le Beverage supplier
--------------------------------------------------------------------------------------
| 5 | French Fries, frozen    | Food     | Frozen Food      | Food Supplier
--------------------------------------------------------------------------------------
| 6 | Pepsi, just Pepsi       | Beverage | Soft Drink       | Other Beverage Supplier
--------------------------------------------------------------------------------------
| 7 | Fanta, just fanta       | Beverage | Soft Drink       | Other Beverage Supplier
--------------------------------------------------------------------------------------
| 8 | Dog Food                | Pet Food | Tasty            | Best Dog Food Inc.
--------------------------------------------------------------------------------------

В столбцах категорий и подкатегорий изначально отсутствуют все значения, все они вводятся вручную на основе самого имени элемента и здравого смысла. Поэтому главная цель - ввести их максимально эффективно и быстро. Мы пробовали фильтровать по поставщикам; это позволило нам работать, скорее всего, в рамках одной категории (их 500) и сэкономить некоторое время, но затем появились дубликаты с тем же именем от других поставщиков, которые не были покрыты - например, Id 3, 4 и 6, 7 Затем мы попробовали выполнить фильтрацию по имени, это решает проблему с дубликатами, но вынуждает нас работать вне одного поставщика, поэтому мы постоянно переключаемся между категориями.
Мои вопросы:

1. Есть ли способ автоматически обновлять все дубликаты, когда мы используем фильтрацию по поставщикам? Под обновлением я подразумеваю автоматическое назначение категории и подкатегории всем столбцам с одинаковым именем, но с разным поставщиком. Примером случая будет:

   • установите категорию X и подкатегорию Y для строки с идентификатором 6, когда я установил для категории 3 строки значение X, а для подкатегории - Y, поскольку они имеют одинаковые имена.

2. Есть ли хороший альтернативный способ сделать это помимо того, как мы пытаемся сделать это сейчас? Я почти чувствую, что в Excel есть какой-то волшебный инструмент, который был разработан, чтобы помочь с такими задачами, как эта.

Answer 1

Я предполагаю, что данные, которые вы показали, являются ячейками A1:E9 .  Войти

=IFERROR(VLOOKUP($B3, $B$2:$D2, 2, FALSE), "")

в камеру C3 .  Перетащите / заполните его вправо, в ячейку D3 , и измените последние 2 (тот, что перед FALSE) на 3 , чтобы это выглядело так:

=IFERROR(VLOOKUP($B3, $B$2:$D2, 3, FALSE), "")

Затем выберите ячейки C3:D3 и перетащите / заполните их так далеко, как вам нужно.

Теперь введите данные, как вы обычно (в настоящее время).  (Если у вас есть какой-то механизм для генерации значения «Id» в столбце A , это не должно быть затронуто.)  Вам, естественно, нужно будет ввести значения «Категория» и «Подкатегория» в ячейки C2:D2 , поскольку они не могут быть дубликатами чего-либо.  Впоследствии, когда вы перейдете в столбцы C и D , вы увидите вышеприведенные формулы в окне формул.  Просто игнорируйте их и введите новые значения «Категория» и «Подкатегория».

Пока вы не введете повторяющееся значение в столбце B ¹ Затем, когда вы завершите ввод данных (нажатием Ctrl+Enter или вкладкой в столбец C), значения «Категория» и «Подкатегория» для первой строки с таким названием продукта автоматически появятся в столбцах C и D Если они того, что вы хотите, просто перейдите к столбцу E и продолжайте как обычно.  Если это не то, что вам нужно, просто проигнорируйте их и введите нужные значения в столбцы C и D

Иллюстрация:

Здесь я начал вводить «Pep» в ячейку B7 , и Excel предлагает «Pepsi, просто Pepsi», потому что это было введено в ячейку B4:

   

Я нажимаю клавишу Tab, чтобы принять предложение, и Excel заполняет ячейки C7 и D7 значениями «Напиток» и «Безалкогольный напиток» из ячеек C4 и D4 .

   

Эти формулы довольно просты.  VLOOKUP ищет данные в вертикальном наборе данных (т. Е. В столбце).  Мои формулы ищут значение в столбце B в текущей строке (т. Е. В имени продукта) в столбце B над текущей строкой.  Если точное совпадение найдено, в формулах показано значение из столбца C (первой) строки в столбце C этой строки, а также для столбца D

Если вы хотите сделать автоматические значения постоянными, скопируйте и вставьте значения.  Вы должны сделать это перед сортировкой данных.
________
¹ В моей системе (под управлением Excel 2013) Excel заметит, когда я начинаю вводить повторяющееся значение, и предложит значение, которое я дублирую.  Если ваша система делает это, просто нажмите Tab, чтобы принять предложение.