Tablica liczników z liczbą dni

Chcę zobaczyć, ile dni są ludzie między pewnymi blokami.

na przykład z ludźmi i dniami, w których są one wymienione w pierwszej tabeli (A1: C5) i blokach w drugiej tabeli (A6: A10) i prawidłowej odpowiedzi próbuję znaleźć wzór obok (B6: B10)

╔═══╦══════════╦══════════╦══════╗
║   ║    A     ║    B     ║   C  ║
╠═══╬══════════╬══════════╬══════╣
║ 1 ║ 15/01/14 ║ 24/04/14 ║ dave ║
║ 2 ║ 24/01/14 ║ 04/02/14 ║  jim ║
║ 3 ║ 25/01/14 ║ 20/02/14 ║ gary ║
║ 4 ║ 10/01/14 ║ 08/02/14 ║  ted ║
║ 5 ║ 19/01/14 ║ 10/02/14 ║  sid ║
╚═══╩══════════╩══════════╩══════╝
╔═══╦══════════╦══════════╗
║   ║    A     ║    B     ║
╠═══╬══════════╬══════════╣
║ 6 ║ 01/01/14 ║   145    ║
║ 7 ║ 01/02/14 ║   106    ║
║ 8 ║ 01/04/14 ║     0    ║
║ 9 ║ 01/06/14 ║     0    ║
╚═══╩══════════╩══════════╝

odpowiedź jest taka, jak powyżej, ponieważ Dave ma około 45 dni w bloku 1 (15 stycznia-28 lutego) i 55 dni w bloku 2 (1 lutego-24 marca). Podsumowując, z 12 dniami Jima w bloku 1, 35 dni Gary'ego w bloku 1 i 55 dni w bloku 2, 30 dni Teda w bloku 1 i 23 dni w bloku 1 daje wyniki pokazane w B6: B9.

Szukam formuły, którą mogę przeciągnąć (zakładam tablicę), która da mi te odpowiedzi.

Ktoś myśli?

microsoft-excel worksheet-function

— tom_j_uk
źródło

Wydaje się, że daty bloku pokrywają się z opisem, w którym to przypadku należy określić zarówno datę początkową, jak i końcową dla każdego bloku, a więc z datą rozpoczęcia bloku 1 w A7 i data zakończenia w B7 użyj tej formuły w C7 dla liczby dni:

=SUM(IF((A$1:A$5>B7)+(B$1:B$5<A7),0,IF(B$1:B$5>B7,B7,B$1:B$5)-IF(A$1:A$5<A7,A7,A$1:A$5)+1))

potwierdź za pomocą CTRL + PRZESUNIĘCIE + WCHODZIĆ i skopiuj

Zauważ, że daje to 137 próbek - Gary ma tylko 27 dni, a nie 35

Aktualizacja:

Znalazłem sposób na skrócenie powyższego użycia TEXT funkcja, tj.

=SUM(TEXT(IF(B$1:B$5>B7,B7,B$1:B$5)-IF(A$1:A$5<A7,A7,A$1:A$5)+1,"0;\0")+0)

— barry houdini
źródło

To jest dokładnie to. Moja data końcowa dla każdego bloku to początek następnego bloku, ale myślę, że ułatwi mi to uczynienie tego oczywistym, jak sugerujesz w B6: B9. Bardzo ci dziękuje za pomoc

— tom_j_uk

Nie ma problemu - znalazłem też nieco krótszy sposób - powinien uzyskać takie same wyniki - zaktualizowałem swoją odpowiedź

— barry houdini

Ponownie działa to świetnie. Dla własnego zrozumienia, czy możesz mi powiedzieć przez "0;\0" , Proszę? Myślę, że rozumiem +0 - czy to dlatego, że ostateczne wartości, które obliczyliśmy, są jak tekst, a to sprawia, że ponownie wracają do liczb „sumarycznych”?

— tom_j_uk

W pierwszej formule pierwszy IF filtruje zakresy dat, które nie pokrywają się z blokiem - jeśli nie użyjesz tego, to liczby ujemne zostaną zwrócone ... ale w formacie 0;\0 liczby dodatnie są zwracane jako liczby całkowite, liczby ujemne jako zero, więc ma taki sam efekt jak funkcja JEŻELI. ..... i tak, wtedy +0 musi konwertować te wartości tekstowe na liczby rzeczywiste

— barry houdini