Найти сущность в списке сущностей и вернуть другое значение ячейки в зависимости от того, к какой категории он принадлежит, в Excel

Question

Я хочу найти хороший способ получить определенное значение на основе категории и другой характеристики. Я постараюсь проиллюстрировать это ниже;

Проблемы:

1) Я хочу проверить, к какой категории принадлежит определенная организация.

2) Основываясь на ответе в 1) И еще одном факторе, давайте назовем это очками опыта, я хочу иметь возможность получить значение перехвата между ними.

Данные:

Сущность............................XP ......................................B ......................XP .........................Б Гэндальф ......................... 80 ..........Гимли ....................Фродо ................. 0-50 .......... 2.5 3.1 .........
Фродо ............................ 21 ........Гэндальф .....Король-чародей Ангмара ..... 51-100 ...... 3,3 ......... 4,2
Гимли ............................. 39 ................... ............................................ 101-150 ..... 4.6 .......... 7.4
Король-чародей Ангмара. 140

Т.е. я действительно хочу выяснить, что у Гэндальфа будет значение 3,3, а у какого короля Ангмара будет 7,4 без чтения этих чертовых таблиц. Я был бы очень признателен за умный и бережный способ сделать это. Я пробовал сопоставлять, индексировать, Vlookuping и создавать простые макросы, не найдя того решения, которое заставит ваш желудок пощекотать особым образом. Пожалуйста, помогите мне!

Answer 1

Если я правильно понял, вы хотите

Столбец индекса 1, сопоставить столбец 2 со столбцом 5 и вернуть либо столбец значений 6, либо 7, в зависимости от того, находится объект в столбце 3 или 4.

Похоже, что вы хотите использовать Index(Match) для выполнения большей части работы, как и полагается. Кроме того, он прав на деньги, и у вас будут проблемы с сопоставлением конкретной сущности xp с группой xp oin, равной второй таблице (справа).

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

Следующее, что вам нужно сделать, это сообщить Excel, находится ли объект в столбце a или столбце b. Похоже, все выложено на одном столе. Я собираюсь предложить вам разделить его на три части.

Таблица 1

Entitiy | Конкретная сущность XP | Entitiy XP Band | А или Б | Окончательный возврат

Таблица 2

A | В

Таблица 3

XP Band | Колонка А | Колонка Б

Это означает, что если вы сопоставляете a или b с каждым правом в таблице 1, вы можете проверить таблицу 2, чтобы вернуть правильное значение из A или B

Шаг 1: XP

1.1: изменить диапазон XP на одно значение (возможно, максимальное), чтобы оно выглядело как

50 100 150

1.2: подсчитайте, в какой полосе есть право, по ROUND, как указано выше.

Шаг 2: А или Б

Напишите формулу в четвертом столбце, которая индексирует имя пользователя, а затем проверяет, находится ли оно в столбце a таблицы 2. Если это возврат "A", если нет, вернуть "B"

что-то вроде

=IF([entitity in this row] = [range A], "A", "B")

Шаг 3: Возвращаемое значение

Напишите формулу в пятом столбце, которая проверяет четвертый столбец, а затем проверяет третью таблицу на предмет окончательного числа, это может быть использование вложенных операторов IF, И или

что-то вроде

=IF([entitiy XP band] = [XP band], IF([entity A or B] = "A",[return A in table 3]),[Return B in table 3])

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

Answer 2

Вы уже дали ответ. vlookup и index - правильные функции для использования.

Мое предложение состояло бы в том, чтобы не пытаться поместить все это в одну формулу / ячейку, но разделить формулы. Прямо рядом с полем, в которое будет введено имя сущности, выполните VLOOKUP для XP. Рядом с этим, используйте MATCH чтобы найти категорию. Наконец, используйте INDEX чтобы найти значение, основанное на двух других столбцах. Вы можете использовать ROUND для округления XP до нужного значения (ROUND(XP/50) в вашем примере).

Надеюсь, это заставит ваш желудок пощекотать ;-)

Answer 3

По какой-то причине этот вопрос не был добавлен как мой, поэтому, к сожалению, я пропустил эти ответы. В любом случае, большое спасибо, вы правильно поняли проблему и нашли хорошее решение.

Фактические данные довольно велики, диапазоны XP разделены на гораздо большее количество категорий, таблица 2 состоит из AG вместо AB, и на самом деле существует 3 разных "таблицы 3" в зависимости от еще одной категории (аналогично построенной как таблица 2 с использованием XYZ), Из-за всего этого я выбрал похожее, но немного другое решение, которое, как мне казалось, я мог бы использовать для любых академических целей.

Шаг первый: получить группу XP, чтобы соответствовать было решено с помощью:

=IF(AND(0<=[XP in this row], [XP in this row]<51), "0-50", IF(AND(50<[XP in this row], [XP in this row]<101), "51-100", "101-150"))

Шаг 2_ Получение категории 1, AG:

=IF(ISERROR(MATCH([entity in this row],[range A],0)),IF(ISERROR(MATCH([entity in this row],[range B],0)), "C", "B"), "A")

Шаг 3: Поиск таблицы для использования (категория 2) XZ:

Решается так же, как шаг 2.

Шаг 4: Поиск решения для каждой сущности и для всех типов XZ

=INDEX([table X], IF([XP]=[XP band1], 2, IF([XP]=[XP band2], 3, 4)), IF([category1 for entity]="A",2,IF(([category1 for entity]="B", 3, 4)))

Шаг 5: Последний шаг, где я выбираю, какие значения X, Y и Z использовать:

=IF([category 2]="X", [table X], IF([category 2]="Y", [table Y], [table Z]))

Возможно, не самое красивое решение, но оно, безусловно, сработало и сэкономит мне много работы.

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