파워쿼리로 만들어 봅니다.

​

=LET(EmpName, B2:B21, EvType, C2:C21, Tm, D2:D21,
ssID, MAP(SEQUENCE(ROWS(EmpName)), LAMBDA(r, COUNTIFS(TAKE(EmpName, r), INDEX(EmpName, r), TAKE(EvType, r), "Login"))),
tmValue, 1440*Tm*(1-2*ISNUMBER(MATCH(EvType,{"Login","Unlock"},0))),
res, SORT(DROP(GROUPBY(HSTACK(EmpName,ssID), HSTACK(Tm,Tm,tmValue), HSTACK(MIN,MAX,SUM),0,0),1),{2,1}),
VSTACK({"Employee","SessionID","StartTime","EndTime","ActiveMinutes"},res))

ssID는 아래 원조백수님 수식 차용함~^^

vba 코딩입니다.

=LET(rng,A2:D21,emp,UNIQUE(CHOOSECOLS(rng,2)),l,LAMBDA(e,LET(r,FILTER(rng,CHOOSECOLS(rng,2)=e),sid,SCAN(0,INDEX(r,,3),LAMBDA(a,b,IF(b="login",a+1,a))),v,HSTACK(r,sid),p,PIVOTBY(CHOOSECOLS(v,2,5),CHOOSECOLS(v,3),CHOOSECOLS(v,4),SUM,0,0),p)),emps,REDUCE("",emp,LAMBDA(a,b,VSTACK(a,l(b)))),t,FILTER(emps,CHOOSECOLS(emps,1)<>""),HSTACK(CHOOSECOLS(t,1,2,4,5),CHOOSECOLS(t,5)-CHOOSECOLS(t,4)+CHOOSECOLS(t,3)-CHOOSECOLS(t,6)))

=LET(DH,B2:B21,DC,C2:C21,DV,D2:D21,
DI, MAP(SEQUENCE(ROWS(DH)),LAMBDA(r,COUNTIFS(TAKE(DH,r),INDEX(DH,r),TAKE(DC,r),"Login"))),
RT, DROP(CHOOSECOLS(PIVOTBY(HSTACK(DH,DI),DC,DV,HSTACK(MIN,SUM),0,0),{1,2,5,7,4,10}),2),
RX, (INDEX(RT,,4)-INDEX(RT,,3)-(INDEX(RT,,6)-INDEX(RT,,5)))*24*60,
VSTACK({"EventID","Employee","StartTime","EndTime","ActiveMinutes"},SORT(HSTACK(TAKE(RT,,4),RX),2)))

​Lock, Unlock 복수 처리 추가

이 게시물의 제목을 

[퀴즈_112] 세션별 Login - Logout 시간 구하기

로 수정해주세요..

좋은 문제이니 지우지 마시구요..~