Googleスプレッドシートで、指定した月の最初のx曜日を求める方法についてです。 月の最初の金曜日、あるいは最初の日曜日、といったように曜日を指定して求めることができます。 WORKDAY.INTL関数等を使って、月の最初のx曜日を求めることができます。手順 …

Googleスプレッドシートでロト（宝くじ）の買い目をランダムに作成する、つまりクイックピックを自分でやってみる例です。 SORTN関数やRANDARRAY関数等を使ってロトの買い目をランダムに生成することができます。 手順 ミニロトの場合 ロト6の場合 ロト7の場…

Googleスプレッドシートで、指定した月の最後のx曜日を求める方法についてです。 月の最後の金曜日、あるいは最後の日曜日、といったように曜日を指定して求めることができます。 WORKDAY.INTL関数等を使って、月の最後のx曜日を求めることができます。手順 …

Googleスプレッドシートで、IPアドレスを並べ替える方法についてです。 「テキストを列に分割」メニューを使って4列に分割すれば間違いなくできますが、ここでは関数で並べ替える例を試してみます。 QUERY関数を使ってIPアドレスを並べ替えることができます…

Googleスプレッドシートで、IPアドレスのゼロ埋め（ゼロパディング）をした文字列を生成する方法です。 SPLIT関数等を使って、IPアドレスのゼロ埋めをすることができます。手順 B列に、ゼロ埋めされていないIPアドレスが記録されています。 そこで、C3セルに…

Googleスプレッドシートで、縦横に並んだデータを縦1列に並べ替えることができる関数についてです。 FLATTEN関数を使って、複数行複数列に並んでいるデータを縦1列に並べ替えることができます。手順 B,C列に6つの値が並んでいます。 そこでE2セルに「=FLATTE…

Googleスプレッドシートでクロス結合（CROSS JOIN、直積）を作る方法についてです。 関数で実現するのはなかなか面倒でしたが、2021年1月ごろに追加されたFLATTEN関数（行列を縦1列に並べ替える関数）を使って比較的容易に実現できるようになっています。 FL…

Googleスプレッドシートで文字列の抽出をする際に便利な、REGEXEXTRACT関数の使用例を紹介します。 REGEXEXTRACT関数は、指定した正規表現にマッチする最初の部分を抽出します。 グループ化（カッコ）の効果に特徴があります。 手順 基本的な抽出 アンカーの…

Googleスプレッドシートで、画面表示の拡大率を設定する方法についてです。 ブラウザの拡大率とは別に、スプレッドシート部分の拡大縮小を設定できます。 メニュー中の「100%」などと表示されている部分を選択して拡大率を変更することができます。 数字を直…

GoogleスプレッドシートのQUERY関数を使ってピボットテーブル（クロス集計表）を作成する方法についてです。 メニュー操作で作成するピボットテーブルも便利ですが、こちらの方法でも手軽にピボットテーブルを作れます。 PIVOT句を使うことで、横方向の集計…

Googleスプレッドシートで、選択しているセル範囲に関する統計情報（値の合計や平均など）を表示させ、確認する方法についてです。 値が記録されている範囲を選択するだけで、ウインドウ下部のステータスバーに合計などの統計値が表示されます。 ▼をクリック…

GoogleスプレッドシートのQUERY関数で、抽出条件として日付を指定する方法についてです。 QUERY関数で日付を抽出条件とする場合は、第2引数で「"WHERE 列= DATE '2020-12-31'"」のような形式で指定する必要があります。 他のセルに記録された日付や、関数で…

GoogleスプレッドシートのFILTER関数で、日付を条件として抽出する方法についてです。 FILTER関数の条件にDATEVALUE関数を用いることで、日付を条件とした抽出ができます。 DATEVAULE関数の代わりに「*1」で済ませる方法もあります。 第3引数以降（AND条件）…

GoogleスプレッドシートのREGEXEXTRACT関数で、カッコによるグループ化と波カッコを使った繰り返し回数を併用した場合の挙動についてです。 REGEXEXTRACT関数の第2引数でカッコによるグループ化と波カッコを使った繰り返し回数の指定「{n}」を併用すると、繰…

Googleスプレッドシートで、セル内で改行している場合に、その中の特定の行を抽出する方法についてです。 改行中のn行目を抽出する方法と、特定の文字列を含む行を抽出する方法を紹介します。 SPLIT関数とINDEX関数を使って、セル内のn行目を抽出することが…

Googleスプレッドシートでは正規表現を用いた文字列の検索や操作を行うことが可能です。 正規表現は簡単に言えばワイルドカードの強力版のようなもので、やや複雑ですが高度な文字列操作ができます。 以下では正規表現の概要といくつかの使用例を紹介します…

GoogleスプレッドシートのFILTER関数で、文字列中に「~を含む」という条件で抽出をする方法についてです。 FIND関数などを併用する方法が知られているものの中間一致（~を含む）・前方一致（~で始まる）・後方一致（~で終わる）の切り替えが面倒なので、ここ…

GoogleスプレッドシートのQUERY関数では「WHERE ~ IS NULL」という表現が使えますが、これは空白セル（値がない）のほかに空文字列（長さ0の文字列）をも抽出します。その意味では「WHERE ~ =''」とするのと変わりません。 WHERE関数の「IS NULL」は空白セル…

GoogleスプレッドシートのUNIQUE関数を使って値の重複を取り除くことができますが、このときの値の比較はかなり厳密なものとなっています。これを使って文字列や数値の一致／不一致の判定をすることを試してみます。 UNIQUE関数を使うことで、イコール演算子…

Googleスプレッドシートで値の種類をカウントするCOUNTUNIQUE関数の挙動についてです。 COUNTUNIQUE関数は数値や文字列だけでなく、エラーについてもそれぞれの種類ごとにカウントします。 COUNTUNIQUEIFS関数も同様です。手順 画像ではD3セルに「=COUNTUNIQ…

Googleスプレッドシートで関数によるグループ化や集計を行う場合は、QUERY関数を使うのが基本です。 しかし、UNIQUE関数等を使っても同じようなことができます。 UNIQUE関数とCOUNTIF関数やSUMIF関数を組み合わせてデータのグループ化や集計ができます。手順…

Googleスプレッドシートで、ある文字列が別の文字列を含むかどうかを判定する方法についてです。 COUNTIF関数とワイルドカードを用いる方法が有名ですが意外に複雑なので、ここでは別の方法を紹介します。 REGEXMATCH関数を使って、ある文字列が別に指定した…

Googleスプレッドシートで、AND関数やOR関数とARRAYFORMULA関数を併用することで配列状の結果を得ようとして、期待どおりの結果が得られないことがあります。ここではその例と代わりの方法をみてみます。 AND関数やOR関数とARRAYFORMULA関数と併用しても、結…

Googleスプレッドシートで、IF関数を使って文字列がワイルドカードにマッチするかどうかの判定結果を返す方法についてです。 IF関数とCOUNTIF関数と組み合わせることで、ワイルドカードにマッチするかどうかの判定結果を返すことができます。 正規表現とのマ…

GoogleスプレッドシートのFILTER関数では、基本的に抽出条件としてワイルドカードを使うことはできません。ワイルドカードを使う必要があればQUERY関数とLIKE句を使うのが本筋ですが、ここでは敢えてFILTER関数と他の関数と組み合わせてワイルドカードによる…

Googleスプレッドシートで、文字列中の文字の種類別に、それぞれ何文字ずつあるのかを集計する方法です。 QUERY関数を使って文字列中の各文字の個数を集計することができます。手順 画像では、B3セルに対象となる文字列が入力されています。 これに対し、（…

GoogleスプレッドシートのSUM関数で合計を求める際に、対象にエラー値が含まれていると結果はエラーになります。そこで、エラーがあっても無視して（エラーを0とみなして）合計を求める方法を紹介します。 SUM関数とIFERROR関数を組み合わせることで、エラー…

Googleスプレッドシートで、URLからファイル名を取得する方法についてです。 具体的な考え方として、URL中の最後の「/」より後にある文字列を取得するものとします。 REGEXEXTRACT関数を使ってURLの文字列からファイル名を取得することができます。手順 画像…

Googleスプレッドシートで、多数の文字列のうち最も多く出現しているものを、関数を使って取得する方法についてです。 数値の場合はMODE関数が使えますが、文字列の場合は少し工夫が必要です。 INDEX関数やMODE関数等の組み合わせにより、最も多い文字列を取…

Googleスプレッドシートで、重なって表示されている図形や画像の重なりの順序を変更する方法についてです。 図形描画のダイアログ内で図形や画像を右クリックし、メニューから「順序」→「最背面へ」などと選択することで図形等の重なり順を変更することがで…