いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

Excel

(Excel)FILTER関数でクロス表からの検索をする

Excelでのクロス表からのデータ検索、いわゆるクロス抽出についてです。INDEX/XMACTH関数による抽出が一般的ですが、ここでは別の方法を試してみます。 FILTER関数をネストすることでクロス抽出をすることができます。手順 B2:E6の範囲がクロス表になってい…

(Excel)XLOOKUP関数で空白セルが0にならないようにする

ExcelのXLOOKUP関数で空白セルを取得すると値が「0」になってしまいますが、これに代わって空文字列(="")を取得する方法についてです。 XLOOKUP関数の第3引数内で空白セルを空文字列に変換しておけば、「0」ではなく空文字列を出力させることができます。 …

(Excel)各種の行列の生成と演算

Excelにおいて、各種の行列(特に数値が格納されているもの)の生成と計算を行う方法についてです。 各要素となる数値をセルに直接入力するほか、1つの式で行列を生成することもできます。 MUNIT関数で単位行列を生成できるほか、SEQUENCE関数、RANDARRAY関…

(Excel)FILTER関数で空白セルが0にならないようにする

ExcelのFILTER関数で空白セル(値がない)を抽出すると「0」になりますが、これに代わって空文字列(="")を出力させる方法についてです。空文字列に限らず任意の値を出力できます。 FILTER関数の第1引数内で空白セルを空文字列に変換しておけば、「0」では…

(Excel)AND/OR関数と*/+記号の違いと使い分け

AND関数の代わりに「*」を、OR関数の代わりに「+」を用いて論理演算を行うことができます。しかし機能的に全く同じではなく、配列を対象としたときに結果に違いが出ます。 ここでは特にAND関数と「*」演算子の比較例を見てみます。 AND関数やOR関数は引数で…

(Excel)Unicode麻雀牌の理牌

Excelで、Unicodeによる麻雀牌が並んだ文字列を理牌(ソート)する例です。 文字列を文字単位で並べ替える例の特殊ケースです。 SORTBY関数等を使ってUnicodeの麻雀牌を理牌できます。手順 画像では、B3セルに理牌の基準となる牌の並びが、さらにB7:B11の範…

(Excel)カッコが整合しているかチェックする

Excelで文字列中のカッコの開きと閉じが整合しているか、関数を使って判定してみます。なお判定対象のカッコは1種類のみとします。 使用する機会はあまりなさそうですが、LAMBDA関数のちょっとした使用例です。 SCAN/LAMBDA関数を使って開きカッコと閉じカッ…

(Excel)全角英字と全角数字のみ半角に変換する

Excelで、文字列中の全角英数字だけを半角にして全角カナや記号はそのままにする方法です。 関数を使っていますが全角数字だけの変換に比べて厄介なこともあり、かなり長い式になります。 UPPER/LOWER関数やVALUE関数を使って英数字かどうかを判定し、それら…

(Excel)MAP関数の基本的な効果

ExcelでLAMBDA関数と併用するヘルパー関数として導入されたMAP関数の効果について例とともに紹介します。 ただし使いどころはなかなか難しそうです。 MAP関数を利用することにより、本来1つの選択範囲に対し1つの(集約した)結果しか返さない関数についても…

(Excel)検索値が2次元の表の何行目/何列目にあるか求める

Excelで、検索値が表(指定範囲)の中の何行目/何列目にあるのかをそれぞれ求める方法についてです。 1行(列)だけの表だとXMATCH関数で一発ですが、2次元の表になっている場合は少し工夫が必要です。 BYCOL/BYROW関数とXMATCH関数を併用することで、検索値…

(Excel)全角数字だけを半角にする

Excelで、文字列中の全角数字だけを半角数字に変換する方法についてです。 対象となる文字が10種だけなのでメニュー操作やSUBSTITUTE関数でもできそうですが、ここでは関数を使った別の例を試してみます。 各種関数を応用して文字列中の全角数字だけを半角数…

(Excel)指定値以下の素数をすべて求める

Excelで指定値以下の素数をすべて列挙する例です。エラトステネスの篩に基づく式を用いています。 REDUCE/LAMBDA関数を用いて指定値以下の素数をすべて求めることができます。手順 画像では、B3に入力された指定値が入力されています。 これに対し、D3セルに…

(Excel)1つの式で行ごとの合計/最大値/最小値等を求める

Excelで数値の合計を行ごとに求める場合、SUM関数を各行に配置するのが普通ですが、ここでは1つの式で済む方法を試してみます。 BYROW関数とLAMBDA関数、SUM関数を使って行ごとの合計を1つの式で求めることができます。 行ごとの最大値、最小値、平均などを…

(Excel)文字列中の複数種類の文字列を置換・削除する

Excelで、文字列に含まれる複数種類の部分文字列を別の文字列に一括で置き換える方法についてです。 従来はSUBSTITUTE関数を重ねるぐらいしか適当な方法がなかったのですが、2022年2月ごろに利用可能となったLAMBDA関数等を利用することで対応できるようにな…

(Excel)文字列の先頭と末尾の空白だけ削除する

Excelで、文字列の先頭と末尾にあるスペース(全角半角は混在可)だけを削除する方法についてです。TRIM関数だと文字の間にあるスペースも削れてしまうので、他の関数で実現してみます。 SEQUENCE関数等を組み合わせて使用することで、文字列の先頭と末尾の…

(Excel)文字列の末尾の空白だけ削除する

Excelで、文字列の末尾にあるスペース(全角半角は混在可)だけを削除する方法についてです。ワークシート関数にはRTRIM関数がなく厄介ですが、他の関数を代用して実現してみます。 SEQUENCE関数等を組み合わせて使用することで、文字列の末尾のスペースだけ…

(Excel)文字列の先頭の空白だけ削除する

Excelで、文字列の先頭にあるスペース(全角半角は混在可)だけを削除する方法についてです。ワークシート関数にはLTRIM関数のようなものがありませんので、ここでは別の関数を代用してみます。 FIND関数やTRIM関数などを組み合わせて、文字列の先頭に並ぶス…

(Excel)複数の画像を合成して1枚の画像にする

Excelで、2枚以上の画像から1枚の画像を作る方法についてです。 グループ化とは異なり元の複数の画像には戻せなくなるので、部分的に隠した画像を復元させたくない場合などに応用できます。 複数の画像を選択してコピーし、貼り付けのオプションの「図」を選…

(Excel)塗り分けマップ(グラフ)に日本地図だけ表示させる

Excelの塗り分けマップグラフを作成すると、データが日本(都道府県)のものであっても世界地図が表示されます。これを日本地図に変更する方法についてです。 都道府県データに基づいて塗り分けマップを一旦作成し、陸地部分をダブルクリックします。 「デー…

(Excel)期間中の月末日を全て求める

Excelで、期間の初日と末日を指定したときに、その期間に含まれる月の末日を全て求める方法についてです。 SEQUENE関数やFILTER関数を利用して、期間中の月の末日をすべて列挙することができます。手順 B3セルとC3セルにそれぞれ期間の初日と末日が記録され…

(Excel)IPアドレスのゼロ埋め/ゼロ削除

Excelで、IPアドレスのゼロ埋め(ゼロパディング)と余分なゼロの削除(ゼロサプレス)をする方法です。 ここでは少し変わったアプローチを試してみます。 FILTERXML関数等を使ってIPアドレスのゼロ埋めと余分なゼロの削除をすることができます。 手順 ゼロ…

(Excel)テーブルの効果とメリット

Excelで、表をテーブル化する効果とそのメリットについて、操作の実例と共に紹介します。 行・列の追加時にテーブルの範囲が自動的に拡張されます。右クリック→「削除」→「テーブルの行(列)」を用いれば縮小も自動です。 数式の入力・変更がすべての行に反…

(Excel)テーブルを作成・解除する

Excelでデータの構造化参照を実現する、テーブルの作成と解除の方法についてです。 作成方法は何種類かありますが、代表的なものを紹介します。 テーブルを作成するときは、表の内部を選択した状態で「挿入」タブ→「テーブル」と選択します。 表の内部で右ク…

(Excel)FILTER関数でワイルドカードを使って抽出する

ExcelのFILTER関数では基本的に抽出条件としてワイルドカードを使うことはできませんが、適当な関数と組み合わせてなんとか実現する方法を紹介します。 FILTER関数とCOUNTIFS関数を組み合わせて、ワイルドカードを使った抽出をすることができます。手順 画像…

(Excel)2つの列で重複する値(しない値)を抽出する

Excelで2つの列を比較して、一方の列に存在する値のうち、もう一方の列にも存在するもの(または存在しないもの)を抽出する方法です。 FILTER関数とCOUNTIF関数を用いて、ある列を別の列と比較して値が一致するもの・しないものをそれぞれ抽出できます。手…

(Excel)グループ別の最大値・最小値を求める

Excelで、多数の数値を個々のグループごとに区別して、それぞれの最大値・最小値を求める方法についてです。 FILTER関数とMAXIFS(MINIFS)関数を組み合わせて、グループ別の最大値・最小値を求めることができます。手順 画像では、B列に氏名が、C列にスコアの…

(Excel)URLからファイル名を取得する

Excelで、URLの文字列からファイル名の部分を取得する方法についてです。 具体的には文字列中の最後の「/」より後の部分を取得します。 FIND関数やSEQUENCE関数等を組み合わせることで、URLからファイル名の部分を取得できます。手順 画像ではC3セルに式を入…

(Excel)最も多く出現する値(文字列)を取得する

Excelで、文字列として記録されている値のうち最も多く出現しているものを、関数を使って取得する方法についてです。 数値の場合はMODE関数で直ちに取得できますが、文字列の場合は少し工夫する必要があります。 INDEX関数やMODE関数等を組み合わせることで…

(Excel)シート名を関数で取得する

Excelで、シートの名前を関数を使って取得する方法についてです。 CELL関数を使って得られる文字列をもとに、シートの名前を取得することができます。 手順 簡便な方法 CELL関数の第2引数(対象範囲)を省略できない理由 パスが角カッコを含む場合の対応 手…

(Excel)文字列に含まれる文字の種類を数える

Excelで、文字列の中に文字が何種類あるかをカウントする方法についてです。 MID関数やUNIQUE関数等を使って、文字列中の文字の種類がいくつあるかを数えることができます。手順 画像では、B3セルに入力されている文字列の文字の種類をカウントするため、C3…