Znajdź obiekt na liście podmiotów i zwróć inną wartość komórki na podstawie kategorii, do której należy w Excelu

Chcę znaleźć dobry sposób na uzyskanie określonej wartości w oparciu o kategorię i inną cechę. Postaram się to zilustrować poniżej;

Problemy:

1) Chcę sprawdzić, do jakiej kategorii należy dany podmiot.

2) W oparciu o odpowiedź w 1) ORAZ inny czynnik, nazwijmy go punktami doświadczenia, chcę mieć możliwość uzyskania wartości punktu przecięcia między nimi.

Dane:

Podmiot ............................ XP ............. A ...... ................... B ...................... XP ....... ..... A ............. B Gandalf ......................... 80 .......... Gimli ............ ........ Frodo ................. 0-50 .......... 2.5 ......... 3.1
Frodo ............................ 21 ........ Gandalf ..... Czarnoksiężnik z Angmaru. .... 51-100 ...... 3.3 ........ 4.2
Gimli ............................. 39 ................... ............................................ 101-150 ... ..4.6 .......... 7.4
Czarnoksiężnik z Angmar..140

To znaczy. naprawdę chcę się dowiedzieć, że Gandalf miałby wartość 3,3, a Który król Angmaru miałby 7,4 bez czytania przeklętych stołów. Byłbym bardzo wdzięczny za mądry i chudy sposób na to. Próbowałem dopasowywać, indeksować, przeglądać i budować proste makra bez znalezienia takiego rozwiązania, które sprawi, że żołądek będzie łaskotał w szczególny sposób. Proszę pomóż mi!

Już dałeś odpowiedź. vlookup i index są właściwymi funkcjami do użycia.

Moją sugestią byłoby nie próbować umieszczać tego wszystkiego w jednej formule / komórce, ale podzielić formuły. Zaraz obok pola, w którym zostanie wprowadzona nazwa jednostki, wykonaj VLOOKUP dla XP. Oprócz tego użyj MATCH znaleźć kategorię. Na koniec użyj INDEX znaleźć wartość na podstawie dwóch pozostałych kolumn. Możesz użyć ROUND zaokrąglić XP do odpowiedniej wartości ( ROUND(XP/50) w twoim przykładzie).

Mam nadzieję, że to sprawia, że ​​twój żołądek łaskocze

Jeśli dobrze to zrozumiałem, chcesz

Kolumna indeksu 1, Dopasuj kolumnę 2 do kolumny 5 i Zwróć zarówno kolumna wartości 6 lub 7, w zależności od tego, czy jednostka znajduje się w kolumnie 3 czy 4

Wygląda na to, że chcesz użyć Index (Match), aby wykonać większość pracy, jak sugeruje agtoever. Również ma rację, jeśli chodzi o pieniądze, będziesz miał problem z dopasowaniem konkretnej jednostki xp do pasma xp, co odpowiada drugiej tabeli (po prawej).

Pierwszą rzeczą do zrobienia jest zaokrąglenie XP, jak podano, a także zmiana wartości w prawej kolumnie z XP na pojedyncze wartości, które pasują.

Następną rzeczą, którą musisz zrobić, to powiadomić program Excel, czy obiekt znajduje się w kolumnie a czy kolumnie b. Wygląda na to, że cały układ jest jednym stołem bankomatowym. Sugeruję podzielenie go na trzy.

Tabela 1

Podmiot | Określona jednostka XP | Entity XP Band | A lub B | Zwrot końcowy

Tabela 2

A | b

Tabela 3

XP Band | Kolumna A | Kolumna B

Oznacza to, że jeśli odwzorujesz a lub b na każdą jednostkę w tabeli 1, możesz następnie sprawdzić tabelę 2, aby zwrócić poprawną wartość z A lub B

Krok 1: XP

1.1: zmień pasmo XP na jedną wartość (prawdopodobnie maksymalną), aby wyglądało

50 100 150

1.2: oblicz, który zespół jednostka znajduje się w ROUND, jak powyżej.

Krok 2: A lub B

Napisz formułę w czwartej kolumnie, która indeksuje nazwę encji, a następnie sprawdź, czy jest w kolumnie a w tabeli 2. Jeśli jest to zwrot „A”, jeśli nie, zwróć „B”

coś jak

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

Krok 3: Wartość zwrotna

Napisz formułę w piątej kolumnie, która sprawdza czwartą kolumnę, a następnie sprawdza trzecią tabelę pod kątem ostatecznej liczby, może to być użycie zagnieżdżonych statystyk IF ORAZ lub

coś jak

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

Mam nadzieję, że to wyjaśnienie kodu psuedo działa. Jeśli potrzebujesz więcej wyjaśnień, napiszę konkretny przykład, kiedy wrócę wieczorem do domu.

Z jakiegoś powodu to pytanie nie zostało dodane jako moje, więc niestety nie trafiłem na te odpowiedzi. W każdym razie bardzo dziękuję, zrozumiałeś problem poprawnie i znalazłeś fajne rozwiązanie.

Rzeczywiste dane są dość duże, pasma XP są podzielone na znacznie więcej kategorii, tabela 2 składa się z AG zamiast AB i faktycznie są 3 różne „tabele 3” w zależności od jeszcze innej kategorii (podobnie zbudowanej jak tabela 2 z użyciem XYZ) . Ze względu na to wszystko wybrałem podobne, ale nieco inne rozwiązanie, które, jak sądziłem, mogę udostępnić dla każdego celu akademickiego.

Krok pierwszy: Dopasuj pasmo XP do rozwiązania przez:

=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"))

Krok 2_ Uzyskiwanie kategorii 1, A-G:

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

Krok 3: Znalezienie tabeli do użycia (kategoria 2) X-Z:

Rozwiązany w taki sam sposób jak krok 2.

Krok 4: Znalezienie rozwiązania dla każdego podmiotu i dla wszystkich typów X-Z

=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)))

Krok 5: Ostatni krok, w którym wybieram wartość X, Y i Z do użycia:

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

Być może nie jest to najładniejsze rozwiązanie, ale z pewnością zrobiło to sztuczkę i zaoszczędzi mi dużo pracy.

Jeszcze raz dziękuję za zaproponowane rozwiązania, prawdopodobnie udało się je rozwiązać w szczuplejszy sposób.