[Excel] 拡張SCAN/BYROW/BYCOL

0.README.md

[Excel] 拡張SCAN/BYROW/BYCOL

元ネタはぼやきツイート

しかし #エクセル のSCAN・BYROW・BYCOLあたりは（ヘルパー関数の戻り値として）せめて一次元配列を保持できればもうちょい使いやすかったのになぁ…と思わなくもない…

より。

EXSCAN

EXWRAPERR=LAMBDA(value,IFERROR(value,IF(TYPE(value)=64,value,MAP(1,LAMBDA(_,value)))));
// [メモ] VSTACK/HSTACKはエラー値は直接には積めない→配列({エラー値})に変換してから積むとOK

EXBYROW=LAMBDA(array, function,
    DROP(REDUCE(0,SEQUENCE(ROWS(array)),
        LAMBDA(accumulator, rownum, LET(
            param,CHOOSEROWS(array,rownum),
            VSTACK(
                accumulator,
                EXWRAPERR(function(IF((rows(param)=1)*(columns(param)=1),INDEX(param,1,1),param)))
            )
        ))
    ),1)
);

EXBYCOL=LAMBDA(array, function,
    DROP(REDUCE(0,SEQUENCE(,COLUMNS(array)),
        LAMBDA(accumulator, colnum, LET(
            param,CHOOSECOLS(array,colnum),
            HSTACK(
                accumulator,
                EXWRAPERR(function(IF((rows(param)=1)*(columns(param)=1),INDEX(param,1,1),param)))
            )
        ))
    ),,1)
);

EXSCAN=LAMBDA(initial_value,array,function,LET(
    rowsnum, ROWS(array), colsnum, COLUMNS(array),
    if(AND(rowsnum>1,colsnum>1),
        SCAN(initial_value,array,function),
        IF(rowsnum<colsnum,
            DROP(REDUCE(initial_value,array,LAMBDA(accumulator,current,
                HSTACK(accumulator,EXWRAPERR(function(TAKE(accumulator,,-1),current)))
            )),,1),
            DROP(REDUCE(initial_value,array,LAMBDA(accumulator,current,
                VSTACK(accumulator,EXWRAPERR(function(TAKE(accumulator,-1),current)))
            )),1)
        )
    )
));