LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Saturday, December 15, 2018

特定の列の値から特定の文字だけ抜き出したい(ARRAYFORMULA関数で)


A列の日時から日付だけを取り出してB列に書き出したい


今回書いた関数

年月日を取り出す (10文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))

年月を取り出す (7文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,7))

年だけ取り出す (4文字)
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,4))



使う関数
  • ARRAYFORMULA
  • IF
  • INDIRECT
  • COUNTA
  • MID


作ったシート

試す用の表

日時日付
2018/12/15 0:00:002018/12/15
2018/12/16 0:00:002018/12/16
2018/12/17 0:00:002018/12/17
2018/12/18 0:00:002018/12/18
2018/12/19 0:00:002018/12/19
2018/12/19 0:00:002018/12/19


補足

ちなみに、ARRAYFORMULA関数を使わなければ
B2に =MID(A2,1,10) とだけ書いて下に引っ張れば同じ結果を得られます

この作業が一度だけなら良いですが
何度も同じ作業をする場合は毎回下に引っ張る作業が地味につらくなります
スクリプトで自動入力してもよいですが
ARRAYFORMULA関数でやってみたのがこの記事です


数式を分解して理解する

=MID(A2,1,10)

=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))
に書き換える

ここまで関数が多いとよくわからないので
↓この部分を分解しながら意訳してみます
:INDIRECT("A"&COUNTA(A2:A)+1)

A2以降でA列に存在する値の個数
COUNTA(A2:A)

1行目はヘッダなのでその1行分を値の個数に足して行数を合わせる
COUNTA(A2:A)+1
上にあるシートの例のようにA2以降に6個の値が入っている時の最終行は7行目になります
単純にCOUNTA(A2:A)の個数では6になってしまいます
値が入っている7行目まで範囲に入れたいので +1します

INDIRECT("A7")の形にして
INDIRECT("A"&COUNTA(A2:A)+1)

A2:A7の範囲を作って
A2:INDIRECT("A"&COUNTA(A2:A)+1)

MID(A2:A7,1,10)の形にして
MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10)

ARRAYFORMULAで囲んでA2以降にも自動で反映させる
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,10))


ちなみに西暦の年だけを取り出す場合は

このように取り出す文字数を変えるだけ
=ARRAYFORMULA(MID(A2:INDIRECT("A"&COUNTA(A2:A)+1),1,4))

LEFT関数でも同じような事ができます
=ARRAYFORMULA(LEFT(A2:INDIRECT("A"&COUNTA(A2:A)+1),4))


関連記事


IF関数を使ってみる
INDIRECT関数を使ってみる
COUNTIFSを使ってみる
MID(文字列, 開始位置, 長さ)を使ってみる

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...