Googleスプレッドシートで、文字列の先頭に並ぶスペース（全角と半角は混在可）や、末尾に並ぶスペース（同様）を削除する方法についてです。 TRIM関数では半角スペースしか削除できず、一方で文字間の半角スペースまで削除してしまいますので、別の方法を試…

GoogleスプレッドシートのRANK関数でデータ（第2引数）に存在しない値の順位を求めようとするとエラーになってしまいます。 そこで以下では、データに存在しない値が加わったと仮定した場合の順位を求めてみます。 RANK関数の第2引数に値を加えてしまうこと…

Googleスプレッドシートで、セル内にある「改行だけの行」をなくす方法についてです。関数でやる場合、SPLIT関数を用いる方法もありますが、ここではREGEXREPLACE関数を使った方法を試してみます。 REGEXREPLACE関数による方法、あるいは「検索と置換」メニ…

Googleスプレッドシートで、データサイズ（n）が奇数の場合は中央値そのものを、偶数の場合は中央値に最も近いデータ中の数値（小さい方の値と、大きい方の値の2通りが考えられます）を抽出する方法についてです。 方法はいくつか考えられますが、ここではSM…

Googleスプレッドシートで、文字列を1文字おきに別の字に置き換える、言い換えれば偶数番目（奇数番目）の文字だけを置き換える方法についてです。 REGEXREPLACE関数を使って、文字列中の偶数番目または奇数番目の文字だけを別の字に置き換えることができま…

Googleスプレッドシートで、ある文字列が、リストで定義された複数の単語（言葉）のいずれか（またはすべて）を含んでいるかどうかを判定する方法についてです。 利用例としては、いわゆるNGワードを含むかどうかの判定が挙げられます。 REGEXMATCH関数等を…

GoogleスプレッドシートのINDEX関数の基本的な動作についてです。 INDEX関数を使って、ARRAYFORMULA関数と同様に複数の計算結果を複数のセルに展開できます。手順 はじめにですが、INDEX関数で第2引数以降を省略すると、第1引数で指定した範囲の内容をそのま…

Googleスプレッドシートで、表の中に1つしかない値だけを抽出する方法についてです。 UNIQUE関数の仕様（引数）が拡張されExcelと同様になったため、UNIQUE関数で実現できます。 UNIQUE関数の第3引数を「TRUE」とすることにより、1つしかない値を抽出するこ…

Googleスプレッドシートのシートの行数・列数は可変となっています。そこで、現在の行数・列数を取得する方法について紹介します。 例として「=ROWS(A:A)」でシートの行数を、「=COLUMNS(1:1)」でシートの列数をカウントすることができます。 行・列の削除等…

Googleスプレッドシートで折れ線グラフを作ったとき、データがない部分については線が欠けて表示されます。 この部分を直線でつないで表示する設定についてです。 グラフエディタのペインの「カスタマイズ」タブを選択した状態で「グラフの種類」メニューの…

Googleスプレッドシートで、横軸の値が数値や日付である場合に値（軸ラベル）が一部省略され、飛び飛びに表示されることがあります。そこで設定を変更してすべての値を表示させる方法についてです。 ただし垂直なグリッド線が消えてしまうという難点がありま…

Googleスプレッドシートで、文字列を指定した文字数だけ左右にシフトし、はみでた文字は反対側に送るというローテートシフトを行う方法についてです。 MID関数等を使って文字列のローテートシフトを行うことができます。手順 画像ではB列に対象となる文字列…

Googleスプレッドシートで、日付を入力するときにカレンダーコントロールを使って入力する設定についてです。 対象のセルを選択した状態でメニューから「データ」→「データの入力規則」と選択し、ダイアログ中の「条件」から「日付」を選択することにより、…

Googleスプレッドシートで棒グラフや折れ線グラフなどを作成したとき、横軸の（水平な）線は表示されますが縦軸の（垂直な）線は表示されません。これを設定の変更により表示する方法についてです。 グラフの余白部分をダブルクリックするなどしてグラフエデ…

Googleスプレッドシートで、数値がある一定の区間内（下限値と上限値により指定）に含まれるかどうかを判定するISBETWEEN関数についてです。 ISBETWEEN関数により、数値が区間内に含まれるかどうかを判定することができます。 日付を判定の対象にすることも…

Googleスプレッドシートで、入力されている値が正しい日付かどうかを判定するISDATE関数についてです。 ISDATE関数により、値が日付かどうかを判定することができます。 テキストとして入力されている場合でも日付とみなせるものであればTRUEと判定します。…

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}」を併用すると、繰…