[퀴즈_128] 연속단어갯수

F3=IFERROR(INDEX($B$1:$B$101,AGGREGATE(15,6,($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)/(($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)<>0)/($B$3:$B$103<>""),ROW(A1))),"")
(아래로 복붙)

G3=AGGREGATE(15,6,($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)/(($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)<>0),ROW(A2))-AGGREGATE(15,6,($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)/(($B$2:$B$102<>$B$3:$B$103)*ROW($A$3:$A$103)<>0),ROW(A1))
(아래로 복붙)

쓰레기 버전인 2019에 한계네요.

=LET(D,  B3:B12,
DT, TEXTSPLIT(TEXTJOIN(",",,IF(D=OFFSET(D,-1,0),D,"/"&D)),",","/",1),
HSTACK(TAKE(DT,,1),BYROW(ISTEXT(DT)*1,SUM)))

​

=LET(
d,FILTER(B3:B12,B3:B12<>""),
s,VSTACK(1,--(DROP(d,1)<>DROP(d,-1))),
p,FILTER(SEQUENCE(ROWS(d)),s),
HSTACK(FILTER(d,s),VSTACK(DROP(p,1),ROWS(d)+1)-p)
)

=LET(a,B3:B12, b,a<>B2:B11, 
  i,FILTER(SEQUENCE(ROWS(a)),b),
  c,DROP(VSTACK(i,ROWS(a)+1),1)-i,  
  HSTACK(FILTER(a,b),c))

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

파워쿼리로 딱 한줄이면 됩니다.

Table.Group(원본, {"데이터"}, {{"개수", each Table.RowCount(_), Int64.Type}},GroupKind.Local)

=LET(rngCn,B3:B12, xFT,FILTER(SEQUENCE(ROWS(rngCn)),rngCn<>VSTACK("",DROP(rngCn,-1))),
xVS,VSTACK(DROP(xFT,1),ROWS(rngCn)+1)-xFT, HSTACK(INDEX(rngCn,xFT),xVS))

=LET(Area, B3:B12, flag, (OFFSET(Area,-1,0)<>Area)*1,
gnum, SCAN(0, flag, SUM),
HSTACK(FILTER(Area,flag),DROP(GROUPBY(gnum,Area,COUNTA,0,0),,1)))