Jak mogę znaleźć datę odpowiadającą określonej kwocie?

Powiedzmy, że mam dwa rzędy danych:

Date    1/5/2017    1/15/2017   1/20/2017   2/15/2017   2/25/2017   3/5/2017
Received    5           10          5           5           10         10

Jak mogę utworzyć formułę, która mówi mi, w którym dniu suma otrzymanych wynosi 20? W tym przykładzie będzie to 1/20/2017.

Chcę pobrać powiązaną datę wymienioną w górnej kolumnie w celu użycia lub wyświetlenia w innej lokalizacji.

ODPOWIEDZI

Poniższe 2 sugestie dostarczają rozwiązań zaproponowanego przeze mnie pytania. Pytanie było bardzo ogólne, bez moich szczegółów, a odpowiedzi nie dotyczyły dokładnie tego, czego potrzebowałem do wykonania funkcji. Zostanie zadane nowe pytanie dotyczące mojej dokładnej specyfiki.

microsoft-excel microsoft-excel-2010

— Bartley Cai
źródło

Czy musisz zwrócić datę pobraną z górnego wiersza lub po prostu podać wskaźnik w kolumnie tej daty? Twój opis wskazuje na 1/20 jako odpowiedź. Czy możesz wyjaśnić, dlaczego to 2/15?

— fixer1234

Przepraszam, to był mój błąd. Jest 1/20. Muszę zwrócić datę odzyskaną wzdłuż górnego rzędu. @ fixer1234

— Bartley Cai

Odpowiedzi:

Wpisz tę formułę w B4 i wypełnij w prawo:

=IF(SUM($B2:B2)=20,"Quota Met","")

Możesz zastąpić dowolny tekst powiadomienia. Jeśli liczba odebranych elementów może się nie sumować dokładnie 20, wtedy możesz użyć >=20 w części SUM () formuły.

Edytuj 1 : LUB, jeśli naprawdę chcesz zobaczyć data kiedy całkowita ilość otrzymanych przedmiotów wynosiła 20, użyj TO wzór i wypełnij w prawo:

 =IF(SUM($B2:B2)=20,B1,"")

Obowiązują powyższe zastrzeżenia.

Dziękuję za komplement i upvote, @ fixer1234.

Edytuj 2 : Jeśli chcesz, aby odpowiedź była w pojedynczej komórce bez wypełnienia równania w kolumnach, wpisz to w B4 jako formułę tablicową (wprowadź formułę za pomocą CTRL-Shift Enter).

=OFFSET(A1,0,MATCH(20,MMULT(N(TRANSPOSE(COLUMN(B2:G2))>=COLUMN(B2:G2)),TRANSPOSE(B2:G2))))

Aby ten działał, musi istnieć suma bieżąca dokładnie 20 obliczone z wiersza Otrzymane przedmioty.

— Bandersnatch
źródło

Po wyjaśnieniu PO okazuje się, że nie spełni tego wymogu. Jednak nadal jest to proste i sprytne rozwiązanie, które może być przydatne dla innych czytelników. +1

— fixer1234

Właśnie przyszło mi do głowy, że twoje drugie podejście może rzeczywiście dostarczyć rozwiązania tego pytania bez użycia funkcji wyszukiwania. W miejscu, w którym potrzebna jest odpowiedź, połącz wszystkie wartości w wierszu wyników: = B4 i C4 i D4 i E4 i F4 i G4. Ponieważ wszystkie nie mające zastosowania komórki zawierają wartość Null, konkatenacja będzie datą. Będzie to jednak ciąg tekstowy, więc jeśli będzie używany w obliczeniach daty, musisz sprawdzić, czy Excel będzie obsługiwał go jak datę; może być konieczne zawinięcie go w funkcję zwracającą wartość liczbową, której można użyć w obliczeniach daty.

— fixer1234

Jeśli zamierzasz zachować wiersz z datą obliczoną, ta formuła tablicowa znajdzie datę i zachowa ją jako datę, jeśli zostanie sformatowana jako taka: =OFFSET(A4,0,MATCH(FALSE,(B4:G4=""),0))

— Bandersnatch

To pytanie było zabawne. :-)

— Bandersnatch

Oznaczono jako odpowiedź, ponieważ nie wymagało to użycia bieżącego wiersza całkowitego.

— Bartley Cai

Wygląda na to, że odpowiedź powinna być 1/20/2017 nie 2/15/2017 ... co oznacza, że nie rozumiem pytania.

Jednak najprawdopodobniej musisz to zrobić

dodać Top wiersz, który pokazuje, a nie poszczególne liczby w drugim rzędzie, ale suma bieżąca (czyli 5 15 20 25 35 45 )
użyj WYSZUKAJ.POZIOMO, aby znaleźć żądaną wartość ( =HLOOKUP(20,B1:H2,2) )

— hymie
źródło

Poprawiłem formatowanie przykładowych wierszy, które podałem, mam nadzieję, że to rozwieje wszelkie zamieszanie. Chciałbym uniknąć używania funkcji WYSZUKAJ, ponieważ moim zdaniem stałoby się to tylko powtórzeniem mojego pytania (zaproponowane przeze mnie pytanie jest tylko statycznym przykładem), a także dlatego, że żądana formuła jest małą częścią całej formuły.

— Bartley Cai

Spróbuję użyć bieżącej sumy, którą poleciłeś.

— Bartley Cai

@BartleyCai, nie jest jasne, jaki masz sprzeciw wobec funkcji wyszukiwania. To naprawdę logiczny sposób na robienie tego, co chcesz. Jeśli wolisz nie wstawiać skumulowanego wiersza na górze, możesz osiągnąć podobny wynik, dodając go poniżej wiersza Otrzymane przedmioty i używając INDEKSU i PODAJ.POZYCJĘ, aby pobrać datę.

— fixer1234

Hmm, to prawda. Myślę, że miałem pewne uprzedzenia wobec funkcji wyszukiwania. Moje zaproponowane pytanie jest zbyt ogólne dla tego, co muszę zrobić, a także pominąłem ważny szczegół; Zadam nowe pytanie z całą moją specyfiką.

— Bartley Cai