W tym filmie zobaczymy, jak wyróżnić przybliżone wyszukiwania dopasowań z formatowaniem warunkowym.
Tutaj mamy prostą tabelę przeglądową, która pokazuje koszty materiałów dla różnych wysokości i szerokości. Formuła w K8 korzysta z funkcji INDEKS i PODAJ.POZYCJĘ w celu pobrania prawidłowego kosztu na podstawie wartości szerokości i wysokości wprowadzonych w K6 i K7.
Zwróć uwagę, że wyszukiwanie opiera się na przybliżonym dopasowaniu. Ponieważ wartości są w porządku rosnącym, funkcja PODAJ.POZYCJĘ sprawdza wartości aż do osiągnięcia większej wartości, a następnie cofa się i zwraca poprzednią pozycję.
Zbudujmy regułę formatowania warunkowego, aby podświetlić dopasowany wiersz i kolumnę.
Jak zawsze w przypadku trudniejszego formatowania warunkowego, radzę najpierw pracować z fikcyjnymi formułami, a następnie przenieść działającą formułę bezpośrednio do reguły formatowania warunkowego. W ten sposób możesz używać wszystkich narzędzi programu Excel podczas debugowania formuły, co pozwoli Ci zaoszczędzić dużo czasu.
Najpierw ustawię wzór na szerokość. Musimy zwrócić TRUE dla każdej komórki w wierszu 7, gdzie dopasowana szerokość wynosi 200.
Oznacza to, że zaczynamy naszą formułę od $ B5 = i musimy zablokować kolumnę.
= $ B5 =
Teraz nie możemy szukać 275 w kolumnie szerokości, ponieważ jej tam nie ma. Zamiast tego potrzebujemy przybliżonego dopasowania, które znajduje 200, tak jak nasza formuła wyszukiwania.
Najłatwiej to zrobić, używając funkcji WYSZUKAJ. WYSZUKAJ automatycznie wykonuje przybliżone dopasowanie i zamiast zwracać pozycję, taką jak PODAJ.POZYCJĘ, funkcja WYSZUKAJ zwraca rzeczywistą wartość dopasowania. Możemy więc napisać:
B5 $ = WYSZUKAJ (6 K $, 6 $ B $: 12 B $)
Z naszą szerokością wejściową dla wartości wyszukiwania i wszystkimi szerokościami w tabeli dla wektora wynikowego.
Jeśli użyję F9, zobaczysz wartość zwracaną przez WYSZUKAJ.
Teraz, kiedy wprowadzam formułę w tabeli, otrzymujemy wartość TRUE dla każdej komórki w wierszu o szerokości 200.
Teraz musimy rozszerzyć formułę, aby pasowała do kolumny wysokości. Aby to zrobić, dodam funkcję LUB, a następnie drugą formułę dopasowującą wysokość.
Zaczniemy formułę w ten sam sposób, ale tym razem musimy zablokować wiersz:
= 5 B $
Następnie ponownie używamy funkcji WYSZUKAJ z wysokością dla wartości wyszukiwania i wszystkimi wysokościami w tabeli jako wektorem wynikowym.
= LUB (B5 $ = WYSZUKAJ (6 K $, 6 $ B $: 12 $ B $), 5 B $ = WYSZUKAJ (7 K $, 5 $ C $: 5 $ H $))
Kiedy kopiuję formułę w tabeli, otrzymujemy PRAWDA dla każdej komórki w dopasowanej kolumnie i każdej komórki w dopasowanym wierszu - dokładnie to, czego potrzebujemy do formatowania warunkowego.
Mogę po prostu skopiować dokładnie formułę z lewej górnej komórki i utworzyć nową regułę.
Teraz, jeśli zmienię szerokość lub wysokość, podświetlenie działa zgodnie z oczekiwaniami.
Wreszcie, jeśli chcesz tylko podświetlić samą wartość wyszukiwania, jest to prosta zmiana. Po prostu edytuj formułę i zamień funkcję OR na funkcję AND.
= I (B5 $ = WYSZUKAJ (6 K $, 6 B $: 12 B $), 5 B $ = WYSZUKAJ (7 K $, 5 $ C $: 5 $ H $))
Kierunek
Formatowanie warunkowePowiązane skróty
Wprowadź te same dane w wielu komórkach Ctrl + Enter ⌃ + Return Wyświetl okno dialogowe Wklej specjalnie Ctrl + Alt + V ⌘ + ⌃ + V Przełącz odniesienia bezwzględne i względne F4 ⌘ + T
