Spis treści
W praktyce bardzo często zdarzają się przypadki, kiedy ty i ja musimy znaleźć najbliższą wartość w zbiorze (tabeli) w stosunku do danej liczby. Może to być na przykład:
- Naliczanie rabatu w zależności od ilości.
- Obliczanie wysokości premii w zależności od realizacji planu.
- Obliczanie stawek wysyłki w zależności od odległości.
- Dobór odpowiednich pojemników na towary itp.
Co więcej, zaokrąglanie może być wymagane zarówno w górę, jak i w dół, w zależności od sytuacji.
Jest kilka sposobów – oczywistych i nie tak oczywistych – na rozwiązanie takiego problemu. Spójrzmy na nie po kolei.
Na początek wyobraźmy sobie dostawcę, który udziela rabatów na sprzedaż hurtową, a procent rabatu zależy od ilości kupowanego towaru. Np. przy zakupie powyżej 5 sztuk rabat 2%, a przy zakupie od 20 sztuk już 6% itd.
Jak szybko i pięknie obliczyć procent rabatu przy wprowadzaniu ilości zakupionego towaru?
Metoda 1: Zagnieżdżone IF
Metoda z cyklu „co tu myśleć – trzeba skakać!”. Korzystanie z funkcji zagnieżdżonych IF (JEŚLI) aby sekwencyjnie sprawdzić, czy wartość komórki mieści się w każdym z przedziałów i wyświetlić rabat dla odpowiedniego zakresu. Ale formuła w tym przypadku może okazać się bardzo kłopotliwa:
Myślę, że to oczywiste, że debugowanie takiej „potwornej lalki” lub próba dodania do niej kilku nowych warunków po pewnym czasie jest fajna.
Ponadto Microsoft Excel ma limit zagnieżdżania funkcji JEŻELI — 7 razy w starszych wersjach i 64 razy w nowszych wersjach. A jeśli potrzebujesz więcej?
Metoda 2. WYSZUKAJ.PIONOWO z widokiem interwałowym
Ta metoda jest znacznie bardziej kompaktowa. Aby obliczyć procent rabatu, użyj legendarnej funkcji VPR (WYSZUKAJ.PIONOWO) w trybie wyszukiwania przybliżonego:
gdzie
- B4 – wartość ilości towaru w pierwszej transakcji, dla której szukamy rabatu
- 4 G$: 8 H$ – link do tabeli rabatowej – bez „nagłówka” iz adresami ustalonymi znakiem $.
- 2 — numer porządkowy kolumny w tabeli rabatowej, z której chcemy uzyskać wartość rabatu
- TRUE – tu pochowany jest „pies”. Jeśli jako ostatni argument funkcji VPR sprecyzować KŁAMLIWY (FAŁSZYWE) lub 0, wtedy funkcja będzie szukać ścisłe dopasowanie w kolumnie ilość (a w naszym przypadku da błąd #N/A, ponieważ w tabeli rabatowej nie ma wartości 49). Ale jeśli zamiast tego KŁAMLIWY napisać TRUE (PRAWDZIWE) lub 1, wtedy funkcja nie będzie szukała dokładnego, ale najbliższy najmniejszy wartość i da nam procent rabatu, którego potrzebujemy.
Minusem tej metody jest konieczność sortowania tabeli rabatów w porządku rosnącym według pierwszej kolumny. Jeśli takiego sortowania nie ma (lub odbywa się to w odwrotnej kolejności), to nasza formuła nie zadziała:
W związku z tym podejście to można zastosować tylko do znalezienia najbliższej najmniejszej wartości. Jeśli chcesz znaleźć najbliższy największy, musisz użyć innego podejścia.
Metoda 3. Znajdowanie najbliższej największej za pomocą funkcji INDEX i MATCH
Spójrzmy teraz na nasz problem z drugiej strony. Załóżmy, że sprzedajemy kilka modeli pomp przemysłowych o różnych wydajnościach. Tabela sprzedaży po lewej pokazuje moc wymaganą przez klienta. Musimy wybrać pompę o najbliższej maksymalnej lub równej mocy, ale nie mniejszej niż wymaga tego projekt.
Funkcja WYSZUKAJ.PIONOWO tutaj nie pomoże, więc będziesz musiał użyć jej analogu – kilka funkcji INDEKSU (INDEKS) i WIĘCEJ EKSPOZYCJONALNE (MECZ):
W tym przypadku funkcja PODAJ.POZYCJĘ z ostatnim argumentem -1 działa w trybie znajdowania najbliższej największej wartości, a funkcja INDEKS wyodrębnia potrzebną nam nazwę modelu z sąsiedniej kolumny.
Metoda 4. Nowa funkcja VIEW (XLOOKUP)
Jeśli masz wersję Office 365 z zainstalowanymi wszystkimi aktualizacjami, zamiast funkcji WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) możesz użyć jego analogu – funkcji VIEW (XWYSZUKAJ), które już szczegółowo przeanalizowałem:
Tutaj:
- B4 – początkowa wartość ilości produktu, na którą szukamy rabatu
- 4 G$: 8 G$ – zakres, w którym szukamy zapałek
- 4$H$:$8$H$ – zakres wyników, z którego chcesz zwrócić rabat
- czwarty argument (-1) zawiera wyszukiwanie najbliższej najmniejszej liczby, którą chcemy, zamiast dokładnego dopasowania.
Zaletą tej metody jest brak konieczności sortowania tabeli rabatów oraz możliwość wyszukiwania w razie potrzeby nie tylko najbliższej najmniejszej, ale także najbliższej największej wartości. Ostatnim argumentem w tym przypadku będzie 1.
Ale niestety nie wszyscy mają jeszcze tę funkcję – tylko szczęśliwi posiadacze Office 365.
Metoda 5. Power Query
Jeśli nie znasz jeszcze potężnego i całkowicie bezpłatnego dodatku Power Query dla programu Excel, to jesteś tutaj. Jeśli jesteś już zaznajomiony, spróbujmy go użyć do rozwiązania naszego problemu.
Zróbmy najpierw kilka prac przygotowawczych:
- Przekonwertujmy nasze tabele źródłowe na dynamiczne (inteligentne) za pomocą skrótu klawiaturowego Ctrl+T lub zespół Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę).
- Dla jasności nadajmy im imiona. Obroty и System zniżek Zakładka Konstruktor (Projekt).
- Załaduj kolejno każdą z tabel do dodatku Power Query za pomocą przycisku Z tabeli/zakresu Zakładka Dane (Dane — Z tabeli/zakresu). W najnowszych wersjach programu Excel nazwa tego przycisku została zmieniona na Z liśćmi (z arkusza).
- Jeśli tabele mają różne nazwy kolumn z ilościami, jak w naszym przykładzie („Ilość towarów” i „Ilość z…”), należy zmienić ich nazwy w dodatku Power Query i nazwać je tak samo.
- Następnie możesz wrócić do programu Excel, wybierając polecenie w oknie edytora Power Query Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…) a potem opcja Po prostu stwórz połączenie (Tylko utwórz połączenie).
- Wtedy zaczyna się najciekawsze. Jeśli masz doświadczenie w Power Query, to zakładam, że dalszy tok myślenia powinien być w kierunku scalania tych dwóch tabel za pomocą zapytania łączącego (merge) a la VLOOKUP, tak jak miało to miejsce w poprzedniej metodzie. W rzeczywistości będziemy musieli połączyć się w trybie dodawania, co na pierwszy rzut oka wcale nie jest oczywiste. Wybierz w zakładce Excel Dane – Pobierz dane – Połącz żądania – Dodaj (Dane — Pobierz dane — Połącz zapytania — Dołącz) a potem nasze stoły Obroty и System zniżek w wyświetlonym oknie:
- Po kliknięciu OK nasze stoły będą sklejone w jedną całość – pod sobą. Należy pamiętać, że kolumny z ilością towaru w tych tabelach znalazły się pod sobą, ponieważ. mają taką samą nazwę:
- Jeżeli pierwotna sekwencja wierszy w tabeli sprzedaży jest dla Ciebie ważna, to aby po wszystkich kolejnych przekształceniach można było ją potem przywrócić, dodaj do naszej tabeli ponumerowaną kolumnę za pomocą polecenia Dodawanie kolumny – kolumna indeksu (Dodaj kolumnę — kolumnę Indeks). Jeśli kolejność linii nie ma dla Ciebie znaczenia, możesz pominąć ten krok.
- Teraz, korzystając z rozwijanej listy w nagłówku tabeli, posortuj ją według kolumn Ilość Rosnąco:
- I główna sztuczka: kliknij prawym przyciskiem myszy nagłówek kolumny Zniżka wybierz drużynę Wypełnij (Wypełnij). Puste komórki z zero automatycznie wypełnione poprzednimi wartościami rabatów:
- Pozostaje przywrócić pierwotną sekwencję wierszy, sortując według kolumny wskaźnik (możesz go później bezpiecznie usunąć) i pozbądź się niepotrzebnych linii za pomocą filtra zero według kolumny Kod transakcji:
- Używanie funkcji WYSZUKAJ.PIONOWO do wyszukiwania i wyszukiwania danych
- Korzystanie z funkcji WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) rozróżnia wielkość liter
- WYSZUKAJ.PIONOWO XNUMXD (WYSZUKAJ.PIONOWO)