Excelで、1つのセルに入力されている苗字と名前（姓と名）を2つのセルに分割する方法を紹介します。 なお、苗字と名前はスペースで区切られているものとしますが、他の記号で区切られている場合でも同様に、あるいは少しの変更で対応できます。 TEXTSPLIT関…

Excelで関数を使って（いま開いている）Excelファイル名を取得する方法についてです。 CELL関数とFIND/MID関数を併用する方法が考えられますが、ここでは新しい関数を使ってより正確に抽出できる方法を試します。 CELL関数とTEXTAFTER/TEXTBEFORE関数を併用…

Excelで、セル内の連続する改行を1つだけにし、さらにセルの先頭・末尾にある改行を削除する方法です。 Microsoft365で2022年9月ごろに利用可能となったTEXTSPLIT関数を使用しています。 TEXTSPLIT関数を使って改行（CHAR(10)）を区切り文字として文字列を分…

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[※2022年11月更新] Excelで、IPv4アドレスのゼロ埋め（ゼロパディング）と余分なゼロの削除（ゼロサプレス）をする方法です。 新関数のTEXTSPLIT関数を使う方法と、FILTERXMLを使う少し変わった方法を紹介します。 TEXTSPLIT/TEXTJOIN関数等を使ってIPアドレ…

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

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

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

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

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

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

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