@takaekokaz (大好きハワイ)posted at 2024-09-23

Excel online上の横並びのデータを縦型データに変換したい。

Q&A

Closed

Excel SharePoint PowerAutomate OfficeScripts

勤務表をSHAREPOINT内のEXCELで作成しています。
解決したいことは、横並びの日付を縦並びにして、毎日のシフトと従業員名の表を作りたいのです。
例えば、10月１日のSteveのシフトは「Ｘ］（セルAS12)です。

浅学ながら、下記のコードを作りました。
エラーは出ないのですが、日付X人数のデータ量で、FOR分を多用しているため処理が遅すぎます。
実行速度を早くするやり方を教えていただければ幸いです。

１　セルE8からAL8までの横並びの日付（もちろんシリアル値を"d"の書式）を
　　縦方向に並べ替える
２　並べ替えた日付データをセルAQ11から行方向に張り付ける。
３　AQ11セルからの各日付に対応した従業員名（NAME)とシフト(K1とかＶなど、、）
　　を従業員名はAR11からシフトはAS11からそれぞれ行方向に追加していく。

発生している問題・エラ-

とにかく、遅いです。１０分くらいかかります。


または、問題・エラーが起きている画像をここにドラッグアンドドロップ

### 該当するソースコード
<officeスクリプトです。>
function main(workbook: ExcelScript.Workbook) {
    let BLANKSheet = workbook.getWorksheet("BLANK");
    // 日付の列数を第１forのカウンターにする。
    for(let Datecl=14;Datecl <45;Datecl++){
        // 日付数値を変数DateVに入れる。
        let DateV = BLANKSheet.getRangeByIndexes(16,Datecl,1,1).getValue()
        // 名前の数を第２forのカウンターにする。名前の増減に合わせてカウンター条件を変更。
        for(let NameRow=18;NameRow<46;NameRow++){
            // 名前を変数Empnameに入れる。Empnameの列はH（12）
            let Empname = BLANKSheet.getRangeByIndexes(NameRow,12,1,1).getValue();
            // シフトを変数WorkShiftに入れる。
            let WorkShift = BLANKSheet.getRangeByIndexes(NameRow,Datecl,1,1).getValue();
            // 縦型データの最終行を取得
            let Lastrow = BLANKSheet.getRange("bp1048576").getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1

            BLANKSheet.getRangeByIndexes(Lastrow,67,1,1).setValue(DateV)
            BLANKSheet.getRangeByIndexes(Lastrow, 68, 1, 1).setValue(Empname)
            BLANKSheet.getRangeByIndexes(Lastrow, 69, 1, 1).setValue(WorkShift)
        }
    }
}

### 自分で試したこと
変数を配列にしようとしましたが、難しすぎました。

1Answer

@nak435 posted at 2024-09-23

秒で終わると思います。
AO列のセル書式を「日付」にしてください。

function main(workbook: ExcelScript.Workbook) {
    let BLANKSheet = workbook.getWorksheet("BLANK");
    
    let nameFirstRow = 9, nameCol = 2;
    let nameLastRow = BLANKSheet.getCell(nameFirstRow, nameCol).getRangeEdge(ExcelScript.KeyboardDirection.down).getRowIndex();
    let nameCount = nameLastRow - nameFirstRow + 1;
    let names = BLANKSheet.getRangeByIndexes(nameFirstRow, nameCol, nameCount, 1).getValues();

    let dateRow = 7, dateCol = 4;
    let dates = BLANKSheet.getRangeByIndexes(dateRow, dateCol, 1, 31).getValues();
    let dateCount = 0;
    for (let d = 1, day = Number(dates[0][0]); d < 32; d++) {
        if (Number(dates[0][d]) != day + 1) {
            dateCount = d;
            break;
        }
        day = Number(dates[0][d]);
    }

    let totalRows = nameCount * dateCount;
    let values = BLANKSheet.getRangeByIndexes(nameFirstRow, dateCol, nameCount, dateCount).getValues();

    let results: string[][] = [];
    for (let d = 0; d < dateCount; d++) {
        for (let n = 0; n < nameCount; n++) {
            let data = [dates[0][d], names[n][0], values[n][d]];
            results.push(data);
        }
    }

    let resultRow = 10, resultCol = 40;
    BLANKSheet.getRangeByIndexes(resultRow, resultCol, totalRows, 3).setValues(results);
}

1Like

Comments

@takaekokaz
Questioner
@nak435さま
返信が遅くなり申し訳ありません。
いつもご指導いただきありがとうございます。
「秒」で終わりました、、。
単純にfor文繰り返すことしかできなかった私には発送できなかったスクリプトでした。。
配列をもっと勉強します。
@nak435
何が遅いのかというと、セルの参照と、セルへの値の代入なんです。
そこで、セル範囲を一度に変数（配列）に持ってきて、セルを参照する代わりに、変数（配列）を参照する。また、変数（配列）にセットした値を、セル範囲に一度に書き込むことで、劇的に早くなる、ということなんです。（10分→1秒）
参照/更新するセル数が多くなるほど、全体の処理時間に大きく差が出ます。
また、これは、Excelアプリでも同様です。
今後は、これを気にしてやってみてください✌️

Are you sure you want to delete the question?

Excel online上の横並びのデータを縦型データに変換したい。

発生している問題・エラ-

1Answer

Comments

Your answer might help someone💌