スプレッドシートで複数のデータを抽出したい（GAS、関数)

Question

前提・実現したいこと

スプレッドシートでデータを抽出したいです。

下記の画像のような状況で
A列を検索して別シートの右側の英単語を検索したいです。

111で抽出できるシートはいくつかあるのでそれらすべてを検索したいのですが、vlookupだと1つしか検索できず、query関数だと、検索した時に下に列が行くため、111が検索できても2行目の「222」が検索できなくなってしまいます。

GASや関数でうまく抽出できる方法はないでしょうか？

詳しい方ご教授いただけると助かります。

◎補足
実際の作業ではシートが20枚程度あり、1枚に対して200行程度あるので可能な限り、重くないようにしたいです。

Answer 1

書いてみました！

[検索]と[結果]の名前のシートの作成お願いします。
検索シートの配列はこれ↓

これで検索値を入れて実行していただければ、
結果シートに出力されると思います。

２００シートとなるとちょっと時間がかかるかも知れません。

function myfunc() {
  
  var ss = SpreadsheetApp.openById('***'); //[todo]スプレッドシートのID
  var sheetList =ss.getSheets() // 全シートの取得

  //検索用のシート
  var searchSheet = ss.getSheetByName('検索');
　var search = searchSheet.getDataRange().getValues()
 //検索値の並び替え
　var searchTrans = []
  search.forEach(e=>{
    if(e[0]!="検索したい数字"){
      searchTrans.push(e[0])
    }
  })

　
 //結果入れる用の連想配列
  var results = {}
  　
　//シートを一つ一つ確認
  for(var sheet of sheetList){

    //検索シートと結果シートを除外
    if(sheet.getSheetName()!="検索"&&sheet.getSheetName()!="結果"){

      //全データを取得
      var values = sheet.getDataRange().getValues()
      values.forEach(e=>{
　　　　　
　　　　　//検索したい単語があるかチェック
        var index = searchTrans.indexOf(e[0])
        if(index != -1  ){
          if(!results[e[0]]){
            results[e[0]] = []
          }
          results[e[0]].push([e[1],sheet.getSheetName()])
        }

      })
    }
  }
  
  //検索結果を貼り付けられる形式に変換
  var setValues = [["検索単語","単語","シート名"]]
  for(var word in results){
    setValues.push([word,"",""])
    results[word].forEach(e =>{
      setValues.push(["",e[0],e[1]])
    })
  }
　　
　　//結果シートへ貼り付け
    var setSheet = ss.getSheetByName('結果');
    setSheet.clear()
    setSheet.getRange(1,1,setValues.length,setValues[0].length).setValues(setValues)

}

---

ちょっと僕の理解がずれているかもしれませんが、

検索する数字の入った[検索シート]と
検索する数字と英単語が記載された[シートA]と[シートB]があり、
検索する数字が一致した場合は、[シートA]と[シートB]の英単語とシート名を表示したい。
と、いうことであってますかね？

関数で行うのであればindexとmatchがよろしいかと。

B2セルにはこれが入ってます。
=index(A17:B22,MATCH(A2,A17:A22,0),2)
MATCH(A2,A17:A22,0)・・・A17:A22の中に、A2の値は何番目に入っているか？　
=>結果 1 と返ってきます。

index(A17:B22,MATCH(A2,A17:A22,0),2)... A17:B22の範囲内の何行目・何列目の値を返すか？
matchの結果の1行目、一番後ろにある ２（2列目）の値を返すことになるので、
=>結果 aaa と返ってきます。

あとは、範囲を適宜変更してあげてください。
エラーが気になるのであれば、エラーを回避する関数等で除外してください。

もちろんGASを使った方が柔軟に対応できますが、
習得までそこそこ時間はかかりますので、どうしてもGASということでなければ
関数をおすすめします。

Comments

[beginner100]

ありがとうございます。
ただ、実際の作業ではシートが20枚程度あり、1枚に対して200行程度あります。

index+match関数も考えていたのですが、index+match関数では、
検索した時、右側にシートA~Tまで伸びてしまうので、パット見わからない状況になってしまうので、説明を失念していました。

GASで3行追加して、query関数で検索し、transpose関数で行と列を並び替えれば、できるとGAS作成まではしたのですが、それだと、A2のみの検索になってしまうので、どうにか検索をA列すべて検索する方法がないかと思って、質問させていただきました。
言葉足らずな部分が多く申し訳ありません。

[Tatsunosuke]

失礼しました！
そうしたら、確かにGASの方がいいかも知れませんね。
ちょっと書いてみますね。少々お待ちください。

[beginner100]

本当にありがとうございます！
これから自分で直したりできるようにこのコードを1行ずつ理解していきます。