Czytelnik wysłał mi w tym tygodniu ciekawy problem z formułą, więc pomyślałem, że podzielę się nim jako wyzwanie z formułą. Problem jest taki:
Masz stałą miesięczną płatność, datę rozpoczęcia i określoną liczbę miesięcy. Jakiej formuły możesz użyć do zsumowania całkowitych płatności według roku, na podstawie następującego arkusza:
Innymi słowy, jaki wzór działa w E5, skopiowany do I5, aby otrzymać sumę za każdy pokazany rok?
Sam wymyśliłem formułę, ale chciałbym też zobaczyć Twoje pomysły. Jeśli jesteś zainteresowany, zostaw komentarz z proponowaną formułą.
Jeśli chcesz, możesz użyć następujących nazwanych zakresów w swojej formule: mos (C5), kwota (C6), początek (C7), koniec (C8).
Możesz pobrać arkusz roboczy poniżej.
Odpowiedź (kliknij, aby rozwinąć)Tyle wspaniałych formuł! Dziękujemy wszystkim, którzy poświęcili czas na udzielenie odpowiedzi. Poniżej znajdują się moje chaotyczne przemyślenia na temat problemu i niektóre z poniższych rozwiązań.
Uwaga: nigdy nie wyjaśniłem, jak należy obchodzić się z granicami miesiąca. Po prostu podążałem za przykładem innego arkusza roboczego. Ich kluczowe informacje to 30 płatności, począwszy od 1 marca: 10 płatności w 2017 r., 12 płatności w 2018 r. I 8 płatności (saldo) w 2019 r.
Tak więc, jeśli nie możesz zrozumieć, jak możesz spróbować rozwiązać taki problem, skup się najpierw na płatnościach. Gdy już wiesz, ile płatności przypada na rok, możesz po prostu pomnożyć tę liczbę przez kwotę i gotowe.
Jak więc możesz znaleźć liczbę płatności w danym roku? W komentarzach poniżej znajdziesz wiele dobrych pomysłów. Zauważyłem kilka wzorców, a poniżej wymieniłem kilka. To jest w toku…
Wzorce projektowe
IF + AND/OR + YEAR + MONTH
Funkcja IF jest niezawodnym trybem gotowości w tak wielu formułach i jest używana w wielu sugerowanych formułach, aby dowiedzieć się, czy rok zainteresowania znajduje się „w granicach” dat początkowej i końcowej. W wielu przypadkach IF łączy się z OR lub AND, aby zachować zwartość formuł.
IFERROR + DATEDIF + MAX + MIN
DATEDIF może zwracać różnicę między dwiema datami w miesiącach, więc chodzi o to, aby użyć wartości MAX i MIN (dla zwięzłości, zamiast IF) do obliczenia daty początkowej i końcowej dla każdego roku i pozwolić DATEDIF uzyskać miesiące pomiędzy. DATEDIF zgłasza błąd #LICZBA, gdy data początkowa nie jest wcześniejsza niż data końcowa, więc funkcja JEŻELI BŁĄD służy do wychwytywania błędu i zwracania zera. Zobacz formuły autorstwa 闫 A, Arun i Davida poniżej.
MAX + MIN + YEAR + MONTH
Formuły Roberta i Petera wykonują prawie całą pracę z MAX i MIN, bez widocznego IF. Niesamowity. Jeśli pomysł użycia MAX i MIN do zastąpienia IF jest dla Ciebie nowy, w tym artykule wyjaśniono tę koncepcję.
DAYS360
Funkcja Excel DAYS360 zwraca liczbę dni między dwiema datami na podstawie 360-dniowego roku. To sposób obliczania miesięcy w oparciu o założenie, że każdy miesiąc ma 30 dni.
SUM + DATE
To jest moje nieefektywne (ale eleganckie!) Podejście wykorzystujące funkcję DATE i stałą tablicową z liczbą dla każdego miesiąca. funkcja DATE odwraca datę każdego miesiąca w roku za pomocą stałej tablicowej, a logika boolowska jest używana do sprawdzania nakładania się.
