Problem
Istnieje jeden test główny (test A) i trzy warianty (test B, test C i test D). Wszystkie 4 testy zawierają te same 19 pytań, ale ułożonych w innej kolejności.
Pierwsza tabela na poniższym ekranie to „klucz pytań” i pokazuje, w jaki sposób pytania z testu A są uporządkowane w pozostałych 3 testach. Druga tabela to „klucz odpowiedzi”, który przedstawia poprawne odpowiedzi na wszystkie 19 pytań we wszystkich testach.
Powyżej: poprawne odpowiedzi w I5: K23, formuła zasłonięta
Na przykład odpowiedź na pytanie nr 1 w teście A brzmi C.To samo pytanie pojawia się jako pytanie nr 4 w teście B, więc odpowiedź na pytanie nr 4 w teście B również brzmi C.
Pierwsze pytanie w teście B jest takie samo jak pytanie nr 13 w teście A, a odpowiedź na oba to E.
Wyzwanie
Jaką formułę można wprowadzić w I5 (to jest i jak w „igloo”) i skopiować w I5: K23, aby znaleźć i wyświetlić poprawne odpowiedzi dla testów B, C i D?
Plik Excela znajdziesz poniżej. Zostaw swoją odpowiedź jako komentarz poniżej.
Poradnik
-
Ten problem jest trudny do skonfigurowania. Bardzo łatwo się pogubić. Pamiętaj, liczby w C5: E23 mówią tylko, gdzie możesz znaleźć dane pytanie. Po tym nadal musisz znaleźć pytanie :)
-
Ten problem można rozwiązać za pomocą INDEX i MATCH, co wyjaśniono w tym artykule. Część rozwiązania polega na dokładnym blokowaniu odwołań do komórek. Jeśli masz problemy z tego rodzaju odniesieniami, poćwicz budowanie pokazanej tutaj tabliczki mnożenia. Ten problem wymaga starannie skonstruowanych odwołań do komórek!
-
Może się okazać, że myślisz, że możesz to zrobić szybciej ręcznie. Tak, na niewielką liczbę pytań. Jednak przy większej liczbie pytań (wyobraź sobie 100, 500, 1000 pytań) podejście ręczne staje się znacznie trudniejsze. Dobra formuła z przyjemnością poradzi sobie z tysiącami pytań i nie popełni błędów :)
Istnieją dwa sposoby interpretacji tego wyzwania. Kiedy ustawiłem problem, pożyczyłem bezpośrednio z przykładu przesłanego mi przez czytelnika. Okazuje się, że jest to trudniejsze podejście (interpretacja nr 2 poniżej), głównie dlatego, że tak łatwo jest się zdezorientować, próbując zrozumieć tabelę. Poniżej wyjaśniam obie interpretacje wraz z formułami, których można użyć w każdej z nich.
Interpretacja nr 1 (niepoprawna)
C5: E23 pokazuje te same pytania z testu A, po prostu zmienione. Na przykład w teście B…
Możesz znaleźć pytanie # 1 z testu A na pozycji # 13
Możesz znaleźć pytanie # 2 z testu A na pozycji # 3
Możesz znaleźć pytanie # 3 z testu A na pozycji # 7
=INDEX($H$5:$H$23,C5)
Dzięki odpowiedziom do testu A w tablicy H5: H23 INDEKS po prostu pobiera wartość przy użyciu liczby z kolumny C dla numeru wiersza. Nie ma nic prostszego niż to. To nie jest poprawna odpowiedź na to wyzwanie, ale i tak jest to fajny przykład.
Interpretacja nr 2 (poprawna)
Druga interpretacja jest bardziej skomplikowana. C5: E23 to klucz, który mówi ci tylko, gdzie możesz znaleźć pytanie z testu A. Nie zgłasza numeru pytania, tylko pewnego rodzaju indeks. Na przykład w teście B…
Możesz znaleźć pytanie # 1 z testu A na pozycji # 4
Możesz znaleźć pytanie # 2 z testu A na pozycji # 19
Możesz znaleźć pytanie # 3 z testu A na pozycji # 2
To jest trudniejszy problem. Zamiast powiedzieć, jakie pytanie z testu A znajduje się na danej pozycji, kluczem jest wskazanie, gdzie można znaleźć poszukiwane pytanie. Poniższy wzór jest jedną poprawną odpowiedzią na ten problem, ponieważ zwróci odpowiedzi pokazane w pierwotnym wyzwaniu.
=INDEX($H$5:$H$23,MATCH($G5,C$5:C$23,0))
Zwróć uwagę na mieszane odwołania wewnątrz MATCH, które zostały starannie skonfigurowane, aby zmieniać się w razie potrzeby, gdy formuła jest kopiowana w całej tabeli.
$ G5 - kolumna jest zablokowana, wiersz się zmieni
C $ 5: C $ 23 - wiersze są zablokowane, kolumny ulegną zmianie
