Zwróć dane na podstawie pasującego zakresu dat

Nasza grupa ma arkusz Excela, który służy do śledzenia obowiązków związanych z rotacją. Oto prosty przykład:

Kolumna A: START zawiera daty określające początek okresu reprezentowanego przez każdy element zamówienia.
Kolumna B: KONIEC ma daty określające koniec okresu reprezentowanego przez każdy element zamówienia.
Kolumna C: NABYWCA posiada ciągi znaków reprezentujące osobę przypisaną do danej odpowiedzialności w okresie określonym przez START i KONIEC w tej samej linii.

Sama lista jest zwykle w porządku rosnącym według daty START. Możliwe jest jednak, że arkusz może zostać ponownie ułożony.

Pomiędzy okresami zdefiniowanymi na liście nie powinno się nakładać.

To, co chcę dodać, to sekcja informacyjna z trzema wartościami (w osobnych komórkach):

POPRZEDNI byłby CZYNNIKIEM odpowiadającym okresowi bezpośrednio przed bieżącym.
AKTUALNY byłby CELEM obecnie odpowiedzialnym.
NASTĘPNY będzie NABYWCĄ odpowiadający okresowi po dniu, w którym się obecnie znajdujemy.

Zakładając, że arkusz kalkulacyjny pozostaje w odpowiedniej kolejności (rosnąco przez START), zwracanie POPRZEDNIEGO i NASTĘPNEGO powinno być łatwe po ustaleniu formuły dla BIEŻĄCEGO. Ale nie jestem nawet pewien, od czego zacząć.

microsoft-excel worksheet-function

— Iszi
źródło

Odpowiedzi:

Myślę, że to zadziała do znalezienia prądu. Testowałem to do pewnego momentu, ale zgaduję, że zignorowanie jednej cechy jest w tym przypadku nieistotne.

Najpierw dodaj kolumnę między B i C (cesjonariusz staje się odtąd D). W tej kolumnie umieść tę formułę:

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

Powinno to dać jeden wiersz z Tak, a resztę z Nie (dla bieżącego gniazda).

Teraz w swojej komórce dla obecnego cesjonariusza umieścisz:

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

Dla poprzednich: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
Dla następnych: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

Normalnie MATCH chce posortować kolumnę wyszukiwania, ale ponieważ powinniśmy mieć zawsze tylko jedno Tak w kolumnie, myślę (to jest część zgadywania), że możemy zignorować to ograniczenie.

Mój testowy arkusz kalkulacyjny miał tylko 3 wiersze, więc YMMV.

Będziesz potrzebować sprawdzania błędów dla poprzednich, gdy bieżący jest pierwszym wierszem itp., A nazwanie zakresu źródłowego jest prawdopodobnie dobrym pomysłem.

Możesz także chcieć ukryć dodatkową kolumnę.

— Oort
źródło

Nie znam się zbytnio na INDEX & MATCH, z tym wyjątkiem, że pod pewnymi względami powinny one być lepsze od WYSZUKAJ.PIONOWO. To powiedziawszy, myślę, że w ten sposób wdrożyłbym twoją sugestię za pomocą WYSZUKAJ.PIONOWO, która pozwala również na łatwe omówienie Poprzednich i Następnych: 1. C2 =IF(C1="Current","Next",IF(C3="Current","Previous",IF(AND(NOW()>A2,NOW()<B2),"Current",""))). 2. Skopiuj C2 do końca. 3. Dla „Current” zrobilibyśmy =VLOOKUP(C:D,"Current",2,FALSE). 4. Napisz podobne formuły jak w 3, dla „Poprzedni” i „Następny”.

— Iszi

Bah. Wygląda na to, że mój pomysł mógł spowodować usterkę w postaci okrągłej referencji.

— Iszi

Niezła próba i działa na prąd. Jednak w przypadku poprzednich i następnych pozycji działa tylko wtedy, gdy lista jest posortowana, ale jak stwierdzono w pytaniu, nie zawsze tak jest.

— Máté Juhász

@ MátéJuhász True. Nie jestem pewien, czy „Wstecz” i „Dalej” można naprawdę poprawnie zinterpretować bez sortowania, chyba że mamy zmienne czasy trwania okien (tak robimy) i zdefiniujemy je we wzorze. Przy sortowaniu będzie to działać w „Kolumnie pomocnika” dla wszystkich trzech:=IF(NOW()>=A2,IF(NOW()<=B2,"Current",IF(NOW()<=B3,"Previous","")),IF(AND(NOW()>=A1,NOW()<=B1),"Next",""))

— Iszi

„Zakładając, że arkusz kalkulacyjny pozostaje w odpowiedniej kolejności (rosnąco przez START) ...” Stąd założenia dotyczące poprzedniego / następnego.

— Oort

Zamówione dane wejściowe:

Bieżący cesjonariusz: currRow=match(now(),A:A,1)- przy uporządkowaniu danych znajdzie się bieżący wiersz, nie ma potrzeby stosowania kolumny pomocniczej. Ustaw nazwę komórki liczącej to currRow tylko dla łatwiejszego odwoływania się.
Cesjonariusz: =indirect("C"& currRow + x)- x: -1, 0, 1 odpowiednio dla poprzedniego, bieżącego i następnego wiersza.

Niezamówione dane wejściowe:

Bieżący wiersz: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0)- jest to formuła tablicowa, więc musisz ją wprowadzić za pomocą CTRL + SHIFT + ENTER.
Obecny cesjonariusz: taka sama formuła jak poprzednio.
Poprzedni: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0))- także formuła tablicowa.
Dalej: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0))- i nadal formuła tablicowa.

— Máté Juhász
źródło