GoogleスプレッドシートのSPLIT関数は基本的に文字列を横にしか分割できませんが、少し工夫すれば縦横に分割できます。 SPLIT関数とTRANSPOSE関数（またはFLATTEN関数）を組み合わせれば、文字列を縦区切り文字と横区切り文字に基づいて縦横に分割できます。…

GoogleスプレッドシートのFILTER関数を使った抽出の際に、条件として1つの列に多数の検索キーを設定する例です（完全一致・OR検索）。+演算子を使う方法もありますが、より簡単に実現できます。 FILTER関数とCOUNTIF関数を併用すれば多数の検索キーによるOR…

GoogleスプレッドシートのXLOOKUP関数で、結果の範囲（第3引数）を複数列（場合によっては行）にし、さらに検索キーが複数ある場合に期待されるような結果が得られません。いわゆる縦横にスピル（Excel用語ですが）しない問題です。 ここではこれを何とかし…

Googleスプレッドシートで、文字列のうち先頭から指定した文字数の文字を削除する方法についてです。 いろいろな方法が考えられますが、対象文字列が短い場合に結果に違いが出ることが注意点です。 REPLACE関数を用いる方法などにより、文字列の先頭からn文…

Googleスプレッドシートで2022年秋ごろに導入されたXLOOKUP関数の基本的な使用例についてです。 簡単に言って表引きを行う関数ですが、従来使用されていたVLOOKUP関数より扱いやすく機能も充実しています。 XLOOKUP関数を使うことで、VLOOKUP関数よりも容易…

Googleスプレッドシートで、新しいメニューや関数の追加といった新機能の詳細を確認する方法についてです。 メニューから「ヘルプ」→「更新情報」と選択することでGoogleスプレッドシートの新機能に関する情報をチェックできます。手順 ウインドウ上部のメニ…

Googleスプレッドシートで、10進数表記されているIPアドレス（IPv4アドレス）を2進数表記に変換する方法についてです。 SPLIT関数等を使ってIPアドレスを2進数表記に変換することができます。手順 B列に対象となるIPアドレスが入力されています。 C3セルに式…

Googleスプレッドシートで、ショートカットキーの一覧を表示させる方法についてです。 併せて互換ショートカット（Excel等との互換のためのショートカット）の有効化についても触れておきます。 メニューから「ヘルプ」→「キーボードショートカット」と進め…

Googleスプレッドシートで、セル内の文字列全体ではなく一部にリンクを設定する方法についてです。 セル内の文字列の一部を選択し、「Ctrl+K」のショートカットを押すことにより、選択した部分だけにリンクを設定できます。 これにより1つのセルに複数のリン…

Googleスプレッドシートで、シートの名前を変更する方法についてです。 シートのタブをダブルクリックするとシート名が選択された状態になります。そのまま文字を入力するとシート名を変更できます。 タブの右側にある「▼」をクリックしてメニューから「名前…

Googleスプレッドシートで、表（指定範囲）のうち上から数えてn行目以外のものすべてを取得する方法についてです。 除外する行は複数指定することもできます。 FILTER関数と他の関数を組み合わせて、上からn行目（複数指定可）以外の行だけを抽出できます。 …

Googleスプレッドシートで、表（指定範囲）のうち左から数えてn列目以外の内容を取得する方法についてです。 除外する列を複数指定することもできます。 FILTER関数と他の関数を組み合わせて、左からn列目（複数指定可）以外の列だけを抽出できます。 手順 1…

Googleスプレッドシートで、あるIPアドレス（IPv4アドレス）が指定した範囲内にあるかどうを判定する方法についてです。 範囲の指定については単純に、最小値と最大値をそれぞれIPアドレスで指定するものとします。 IPアドレスを１つの数値に換算することで…

Googleスプレッドシートで、数値の偶数乗の頭にマイナスがついている場合の結果についてです。 Excelでも同じですが、ちょっと注意が必要な結果になります。 数値の偶数乗（「^」を使ったもの）にマイナスの符号をつけたものが式の最初の項である場合、その…

Googleスプレッドシートで、関数により表データに小計行を挿入する方法についてです。 REDUCE/LAMBDA関数等を使って、データに小計行を加えた表を作成することができます。手順 対象となるデータはB3:C11の範囲です。B列には品目が、C列には個数が入力されて…

Googleスプレッドシートで、表の中で最初に出現する順に値をソートする方法についてです。 実用性は薄いですが参考まで。 SORT関数とUNIQUE関数等を併用することで初出順のソートができます。手順 まずは結果です。 ソートの対象となっているのはB3:C12の範…

Googleスプレッドシートで指定値以下の素数をすべて列挙します。 Excelでも全く同じことができますがGoogleスプレッドシートでもLAMBDA関数とサポート関数が使えるようになりましたのでその記念です。参考まで。 REDUCE/LAMBDA関数でエラトステネスの篩を実…

Googleスプレッドシートで、ある列の幅をコピーして他の列の幅として貼り付ける（設定する）方法です。 コピー元の列を選択してコピー（右クリック→「コピー」、またはCtrl+Cなど）します。 コピー先の列を選択し、右クリックして「特殊貼り付け」→「列の幅…

Googleスプレッドシート独自の関数であるQUERY関数におけるSELECT句の使用についてです。 SELECT句を省略すれば全ての列が抽出されます。 WHERE句などを使用する際にSELECT句を併用する必要はありません。手順 F2セルにQUERY関数を用いた式を入力し、B2:D7の…

Googleスプレッドシートで、指定した文字（文字列）を含む行を抽出する各種の方法のまとめです。 部分一致（~を含む）のほか、前方一致（~で始まる）と後方一致（~で終わる）についても記しています。 フィルタや関数を使って、指定した文字（文字列）を含む…

Googleスプレッドシートにおける計算式中での「%」の使用についてです。 数値に「%」をつけたときと同じく、計算式中に「%」をつければ0.01をかけたのと同じ結果になります。手順 B2セルに「=200*6%」と入力したところです。結果は12となります。 aのx%を求…

Googleスプレッドシートで、「9:30:00」のような時間を「9.5」のような（何時間という）数値に変換する方法についてです。 「=VALUE(時間)*24」という式により、時間（時刻）を時間単位の数値に変換することができます。 手順 備考 手順 B列にいくつかの時間…

Googleスプレッドシートで、データ全体の代表値の一種である中央値を求める方法についてです。 MEDIAN関数を使って中央値を求めることができます。 MEDIAN関数は空白セルを無視します。つまり集計対象に空白セルをいくら含めても結果は変わりません。手順 画…

Googleスプレッドシートにおける割り算の余り（剰余）の取得についてです。 MOD関数を使い「MOD(被除数,除数)」とすることで割り算の余りを求めることができます。 演算子により求めることはできません。手順 画像ではB2セルに式を入力し、7を4で割った余り…

Googleスプレッドシートでのクロス表からの値の検索についてです。INDEX/MATCH関数を使うのが定石ですが、ここではFILTER関数を使った方法を試してみます。 FILTER関数を2重にネストすることでクロス表から値を取得することができます。手順 B2:E6の範囲がク…

Googleスプレッドシートの「検索と置換」メニューで使える先読みと後読みについてです。先読みと後読みの詳しい解説は省略し、ここでは使用例だけ紹介します。 「検索と置換」メニューで正規表現の先読みと後読みを使うことができます。手順 B3:B6に入力され…

Googleスプレッドシートでセルにカスタム数値形式を適用する場合に、特殊な文字を特殊な文字として解釈させず（エスケープして）そのまま表示させる方法についてです。 カスタム数値形式のダイアログで「#」「@」などの特殊な文字の前に「\」（画面ではバッ…

Googleスプレッドシートの表示形式メニューに現れる「書式なしテキスト」の効果についてです。 値の入力前に「書式なしテキスト」を適用すると、キー入力した内容がそのまま文字列として記録されます。 値の入力後に「書式なしテキスト」を適用すると、その…

Googleスプレッドシートで、多数の数値の中から、基準として指定した値に最も近いものを求める方法についてです。 該当する同じ数値が複数ある場合でも、1つの値だけを取得するものとします。 FILTER関数とMAX/MIN関数を使って、多数の数値の中から指定した…

Googleスプレッドシートで、文字列中の全角英数字だけを半角にして全角カナや記号はそのままにする方法です。 関数を使いますが、かなり長くなります。 REGEXEXTRACT関数を使って1文字ずつ全角英数字かどうかを判定し、それらにだけASC関数を適用すれば全角…