Zadałeś trzy pytania.
Jednym z nich jest: „Czy można w tym celu użyć funkcji WYSZUKAJ.PIONOWO ()?” Miroxlav opublikował doskonałą odpowiedź pokazującą, jak to zrobić za pomocą kolumny pomocniczej.
Drugie pytanie dotyczy tego, jak uzyskać pożądany rezultat, a trzecie pytanie, jaki może być powód, dla którego twoja formuła zwraca złą odpowiedź. Spróbuję odpowiedzieć na dwa ostatnie.
Twoja formuła jest bardzo bliska prawidłowego działania. Zróbmy rozwiązywanie problemów, aby dowiedzieć się, co jest nie tak.
Po pierwsze, twoja formuła zawiera kilka literówek - brak znaku mnożenia w 2 miejscach między nawiasami. Excel zaproponował korektę tego i usunąłem odniesienia do arkusza danych („Dane L5”!) Dla uproszczenia:
=INDEX(M5:M15,SUMPRODUCT(--(A5:A15="2018")*(B5:B15="2")*(F5:F15="Filler")*ROW(5:15)))
Program Excel ma wbudowaną funkcję rozwiązywania problemów, w której możesz podświetlić część formuły na pasku formuły i wpisać F9, aby zobaczyć wartość podświetlonej części formuły.
Kliknij komórkę zawierającą formułę, a następnie podświetl wyrażenie (A5: A15 = „2018”) na pasku formuły i naciśnij F9 (klawisz funkcyjny 9). Zobaczysz tablicę zawierającą wszystkie FALSEwartości. Oznacza to, że brak komórki w A5: A15 = „2018”. To dlatego, że masz 2018 w cytatach. Jeśli usuniesz cudzysłowy i spróbujesz ponownie, otrzymasz tablicę, {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}której oczekujesz - pierwsze 5 komórek w zakresie to = 2018.
Ten sam problem występuje w drugim wyrażeniu - usuń znaki cudzysłowu wokół 2 cali (B5:B15="2"). Trzeci wyraz (F5:F15="Filler") nie trzeba cytaty, bo „wypełniacz” to tekst, zamiast liczby.
Teraz zaznacz (A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")i wciśnij F9. Zobaczysz wynik jest {0;0;0;1;0;0;0;0;0;0;0}. Co się tu stało? Mnożenie tablic wartości logicznych konwertuje wartości Prawda / Fałsz na 1 i 0, a także wykonuje równoważnik logicznego AND (). Zatem wynik ten mówi, że jest tylko jedna pozycja w zakresie wierszy 5:15, gdzie kolumna A to 2018, kolumna B to 2, a kolumna F to „Wypełniacz”. I ta pozycja jest czwarta.
Następnie twoja formuła zwielokrotnia tę tablicę razy tablicę liczb wierszy 5:15. Używając F9, możesz zobaczyć ten wynik - {0;0;0;8;0;0;0;0;0;0;0}. Teraz SUMPRODUCT () sumuje elementy tej tablicy i zwraca wynik 8, który jest numerem wiersza, w którym wszystkie trzy warunki są spełnione. Ponownie możesz zobaczyć ten wynik za pomocą F9.
A teraz jest główny problem - po tych pośrednich obliczeniach twoja formuła jest =INDEX(M5:M15,8):, która zwraca ósmy element z zakresu M5: M15. Ale nie chcesz ósmego elementu, potrzebujesz czwartego, ponieważ zakres M5: M15 zaczyna się w rzędzie 5, a 100% znajduje się w czwartej komórce w zakresie M5: M15.
Rozwiązaniem jest odjęcie 4 od SUMPRODUCT ():
=INDEX(M5:M15,SUMPRODUCT((A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")*ROW(5:15))-4)
To zwraca poprawny wynik - 100%.
Uwagi:
- Nie udało mi się uzyskać zwrotu z formuły 81,8%, bez względu na to, jak bardzo się starałem.
- Podwójne jednoargumentowe ujemne (-) w formule nie jest konieczne. Jest często używany do konwersji tablic logicznych na 1 i 0 ', ale zwielokrotnienie tablic osiąga to.
Mam nadzieję, że to pomoże. Przedstawione przeze mnie zmiany pozwolą na prawidłowe działanie formuły. Użyj odpowiedzi Miroxlav, jeśli chcesz użyć WYSZUKAJ.PIONOWO (). Powodzenia.
