見出し画像
Photo by pup0526mk

【Officeスクリプト】ある条件に一致した行を転記する💰３パターン

alice

こんにちは。aliceです。
最近はGASにキュンキュンしています💛

今日やりたいことはこちら↓
「Officeスクリプトである条件に一致した行を転記する。」


Sheet1にこんなデータが入ったExcelがあります。
A列には科目名、B列には金額が入っています。

仕事を思い出すいやーな感じのExcelですね。
いつもどおり「いもシリーズ」にしなかったことが悔やまれます😥

それはさておき、この中から金額が10,000円を超える金額の行（科目名と金額）をSheet2に転記します。


条件に一致した３行が転記されました。

for文、for…of文、filterメソッドを使ってやってみましたよ。

ではさっそく見ていきましょう。


１　for文を使って転記する


まずはfor文バージョンから。

function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getWorksheet("Sheet1");
  const sheet2 = workbook.getWorksheet("Sheet2");
  const rowCount = sheet1.getUsedRange().getRowCount();
  let row2: number = 1;

  for (let row1 = 1; row1 < rowCount; row1++) {
    if (sheet1.getCell(row1, 1).getValue() > 10000) {
      sheet2.getCell(row2, 0).setValue(sheet1.getCell(row1, 0).getValue())
      sheet2.getCell(row2, 1).setValue(sheet1.getCell(row1, 1).getValue())
      row2 += 1;
    }
  }
}

ExcelVBA的に書くとこんな感じですね。
セル１つずつに、ピコピコ転記されていくのがかわいいです🐣


２　for…of文を使って転記する

次はfor…of文を使ったバージョンです。

function main(workbook: ExcelScript.Workbook) {
    const sheet1 = workbook.getWorksheet("Sheet1");
    const values = sheet1.getUsedRange().getValues();
    values.shift();

    const records: (string|number|boolean)[][] = [];
    for(const value of values){
        if(value[1]>10000){
            records.push(value);
        }
    }

    const sheet2 = workbook.getWorksheet("Sheet2");
    sheet2.getRangeByIndexes(1,0,records.length,records[0].length).setValues(records);
}

なんとなくGASっぽいですね。
for…of文とか、shiftメソッド・pushメソッドに胸キュンです💛
また、setValuesメソッドを使って一度に転記しているところなんて、もう素敵ですね💛


イメージでいうと次の流れです。

①　Sheet１のデータを２次元配列で取得
②　Shiftメソッドを使って見出し行を削除する
③　取得したデータを入れる２次元配列を作成する
④　取得した２次元配列で条件に一致した行があったら、別の２次元配列に入れる
⑤　Sheet２に２次元配列の値を転記する


もう少し詳しく書きます。

①Sheet１のデータを２次元配列で取得

まず、getValuesメソッドを使って、Sheet１のデータを２次元配列をして取得します。

const values = sheet1.getUsedRange().getValues();


②Shiftメソッドを使って見出し行を削除する

次に、Shiftメソッドを使って１行目（見出し列）を削除します。

Shiftメソッドは配列の先頭の要素を抜き出すメソッドです。

values.shift();


③取得したデータを入れる２次元配列を作成する

ここで、条件に一致するデータを入れる２次元配列（records）を作ります。

const records: (string|number|boolean)[][] = [];


(string|number|boolean)[][]　←　この部分はデータ型です。

[][]　←　２次元配列だよん
(string|number|boolean)　←　文字列型か数値型か真偽型が入るよーん
セル範囲は何が入っているかわからないので、データ型をこのように宣言しました。


④取得した２次元配列で条件に一致した行があったら、別の２次元配列に入れる

次に、for…of文で配列を１つずつ（１行ずつ）条件に一致しているか確認して、一致していたらpushメソッドを使って２次元配列（records）に追加します。

pushメソッドは、配列の最後尾に要素を追加するメソッドです。

for(const value of values){
    if(value[1]>10000){
        records.push(value);
    }
}


⑤Sheet２に２次元配列の値を転記する

最後に、作成した２次元配列（records）をSheet2にどさっと転記します。

const sheet2 = workbook.getWorksheet("Sheet2");
sheet2.getRangeByIndexes(1,0,records.length,records[0].length).setValues(records);

getRangeByIndexesメソッド、いいですよねー。
GASと同じことができるー！！と思って見つけたとき嬉しくてテンション上がりました💛


３　filterメソッドを使って転記する

３つ目はfilterメソッドバージョンです。

function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getWorksheet("Sheet1");
  const values = sheet1.getUsedRange().getValues();
  values.shift();

  const records = values.filter(record => {
    let [kamoku,kingaku] = record;
    return kingaku > 10000;
  });

  const Sheet2 = workbook.getWorksheet("Sheet2");
  Sheet2.getRangeByIndexes(1,0,records.length,records[0].length).setValues(records);
}

filterメソッド、カッコいいですね。
アロー関数とのコラボレーションなんてキュンキュンです💛
さらに、配列の分割代入まで。
なんて楽しい世界なのでしょう💛

こちらもイメージでいうと次の流れです。


①　Sheet１のデータを２次元配列で取得
②　Shiftメソッドを使って見出し行を削除する
③　取得したデータを入れる２次元配列を作成する
④　filterメソッドを使って、条件に一致した配列を取得する
⑤　Sheet２に２次元配列の値を転記する


④だけ違うのでちょこっと確認します。


filterメソッドを使って、条件に一致した配列を取得する

filterメソッドを使って、10,000円を超える金額の行の配列を取得します。

また、さりげなく配列の分割代入をしてみました。
「金額が10,000円を超えたら」というのがわかりやすいかな？

const records = values.filter(record => {
    let [kamoku,kingaku] = record;
    return kingaku > 10000;
 });

他はfor…of文と同じですね。


以上、Officeスクリプトである条件に一致した行を転記する３パターンでした。

GASを（そこそこちゃんと）やりはじめてから、こんな書き方あるんだー！と日々発見があって楽しいです。
他にも使いたいメソッドがたくさんある( •̀ ω •́ )✧


４　ExcelVBAバージョン（おまけ）

最後はExcelVBAバージョン。

Sub GetOver10000()
    Dim row1 As Long, row2 As Long
    row2 = 2
    With Sheet1
        For row1 = 2 To .Range("A" & Rows.Count).End(xlUp).Row
            If .Range("B" & row1).Value > 10000 Then
                Sheet2.Range("A" & row2).Value = .Range("A" & row1).Value
                Sheet2.Range("B" & row2).Value = .Range("B" & row1).Value
                row2 = row2 + 1
            End If
        Next
    End With
End Sub


５　参考

Officeスクリプトのメソッドがたくさん。


最後まで読んでいただきありがとうございます🍠


Officeスクリプト記事まとめ📝


『Officeスクリプト入門講座』を書きました📝


この記事が気に入ったらサポートをしてみませんか？