【便利機能】スプレッドシートを活用したQueryの使い方

スプレッドシートでSELECTを使いQueryを活用する方法

スプレッドシートを活用することで、Excelでは機能として存在しないQuery/IMPORTRANGEなど処理速度を改善させスマートに抽出することが 可能となります。シート間の結合や、集計関数と正規表現で条件をかけながら日付をフィルタにかけた記述を公開します。

Query関数を使って、シート間で結合する方法

=QUERY({'シート名'!A2:E;'シート名'!H2:L;'シート名'!AE2:AI},"where Col1 is not null")

Query関数を使って、動的に日付を選び正規表現で条件フィルタをかけ集計関数を使って、特定のラベルをつけて表示させる。

=QUERY(IMPORTRANGE("シートID","シート名!A2:G"),
"select Col2,SUM(Col4),sum(Col5) where Col2>=date '"&TEXT(B6,"yyyy-mm-dd")&"' and Col2<=date '"&TEXT(C6,"yyyy-mm-d")&"'and( Col3 contains '"&D2&"' or Col3 matches '^"&B2&"$' or Col3 matches '^"&C2&"$') group by Col2 label Col2 '日付', SUM(Col5) 'COST', SUM(Col4) '収益'")```

シート内で、重複しないデータを抽出する方法

=UNIQUE(IMPORTRANGE("シートID","シート名!C2:C"))

ARRAYFORMULAを活用したラベル条件分岐

=ARRAYFORMULA(
 IFS(

isnumber(find("検索キー", 参照セル,1)),参照セル=ラベル,
 isnumber(find("", 参照セル,1)),"Other")
 )
 ```


<h2></h2>

Javascript 基本構文

Javascript 基本構文について

■Javascript 文字列を出力

textContentプロパティを使う。ページに文字列を表示するdocument.writeメソッドもあるが 使い勝手が悪く、現在は使われていない。

document.getEle,emtByID("ID名").textContent = aa

};

■文字列を数値に変換 Number.parseInt/ParseFlotメソッドを利用する。

「111abc」のような文字列混在の数値も、数字として解析できる部分を先頭から変換しようとする。

 aa  ="111abc";

bb=Number.parseInt(aa)
bb // 111

};

■インクリメント演算子

++演算子はインクリメント演算子とも呼ばれ、オペラントに対して１を加算した結果を 返します。

i++;
++i;
i = i + 1

//これらは同じ意味

};

■値を演算した結果を変数に代入する

複合代入演算子を利用する。左辺と右辺の値を演算した結果を左辺に代入するための演算子。

x=1; X+=2 // 3
i + =1 //同じ結果

};

■配列の作成

複数の値をまとめて管理するための型。配列はArrayコンストラクター ・リテラル表現で作成可能。

配列の参照は、ブランケット[**]には、取得したい配列のインデックス番号を指定。 入れ子の配列を参照する場合は、複数列記する。

aa = new Array("A","B","C")
aa[0] // A

■連想配列を作成 インデックス番号でアクセスできる配列に対して、連想配列は 名前をキーにアクセスできる配列。ハッシュともいう。辞書のように、意味あるキーで 値を引けるので、データの視認性が高いのが特徴。

連想配列は、Objectコンストラクター、リテラル表現いずれかで作成できる。 リテラルでは{}を利用する。原則は理れたる表現を優先して利用。

aa={a:1,b:2,c:3}

aa["a"] //1