ExcelのUNIQUE関数で複数の列を指定した場合、行（複数列の組み合わせ）単位での重複比較となるため、セル単位で値の重複を除くことはできません。 そこで別の関数と組み合わせてセル単位での重複削除を行ってみます。 「=UNIQUE(TOCOL(範囲))」という式で、…

ExcelのTEXTSPLIT関数を使って、テキストを縦方向に分割する方法についてです。 区切り文字（列）をTEXTSPLIT関数の第3引数として指定すれば、テキストは縦に分割されます。 TEXTSPLIT関数とTOCOL関数を併用する方法もあります。 手順 区切り文字を第3引数と…

Excelで、クロス抽出とは逆に表内の値から見出し列（行）の内容を抽出する方法です。 やり方を3つ紹介しますが、最後のものでは検索値が複数みつかるときにすべて抽出できます。 XLOOKUP/LAMBDA/BYROW(BYCOL)関数を使う方法 XLOOLUP/TOCOL関数を使う方法 FIL…

Excelのワークシート関数で、2つの文字列の違いの度合いを測るレーベンシュタイン距離（編集距離）を求める例です。1セル内の式で完結し、2つの文字列の距離のみを求める内容としています。 REDUCE/LAMBDA関数等を利用してレーベンシュタイン距離を求めるこ…

Excelで、数値のすべての約数を求めて列挙する方法についてです。 REDUCE/LAMBDA関数を使って、数値の約数をすべて求めることができます。手順 画像では、D3セルに式を入力して下方にコピーすることにより、B列に記録された各数値のすべての約数を求めていま…

Excelで、カンマで区切られた文字列のうち指定した位置の項目を求める方法についてです。他の区切り文字であっても同じようにできます。 また、後ろから数えることも可能です。 TEXTSPLIT関数とINDEX関数を組み合わせて、カンマ区切り文字列のn番目の項目を…

Excelで長らく待望された末に導入されたTEXTSPLIT関数の基本的な使い方についてです。 注意点や若干の応用についても併せて紹介します。 TEXTSPLIT関数を使って、文字列をその文字列中の区切りの文字（カンマ、コロンなど。2文字以上可）に基づき分割するこ…

Excelで、あるセルと同じ値を並べて配列、行列を作る方法です。 実用的な意味はあまりなさそうですが参考まで。 EXPAND関数を使ってあるセルを並べた配列・行列を作ることができます。手順 文字列におけるREPT関数のように、セルを指定回数繰り返すための適…

Excelの新関数、TOCOL/TOROW関数についてです。 これらの関数には、エラーや空白を削除するという機能があります。一見地味ですが、1列（行）だけのデータに対してエラーや空白セルを削除する場合にも便利です。ここではTOCOL関数の例を紹介します。 はじめ…

Excelで、カッコに囲まれた内側の文字列を取得する方法です。 比較的新しい関数を使い、1つだけ抽出する方法と複数のカッコ内をすべて抽出する方法を試してみます。基本的に多重カッコに対応できませんが、複数抽出の場合は意味のある結果が得られます。 TEX…

ExcelのXLOOKUP関数では1つの検索キーに対し複数の値を一括して取得できますが、さらに検索キーが複数になった場合は各検索キーに対し値を１つずつしか取得できなくなります。いわゆる縦横同時にスピルしない問題です。 別の関数を使って同じ結果を実現する…

ExcelのTEXTSPLIT関数は強力ですが、複数のセル（配列）を対象とした場合に自然にスピルした結果が得られません。その現象と対策についてです。 TEXTSPLIT関数の第1引数として複数のセルを与えた場合、それぞれのセルの文字列を区切り文字で分割した最初の項…

[注:当初はFILTER/SEARCH関数による例を掲載していましたが、後方一致検索に難があったため削除しました] Excelで、指定した文字（文字列）を含む行を抽出する各種の方法についてです。 いずれの方法でも、部分一致（~を含む）、前方一致（~で始まる）、後方…

Excelで、XLOOKUP関数と参照演算子を組み合わせる使い方の例です。INDEX関数でも同じようなことができますが、こちらの方が応用範囲が広そうです。 XLOOKUP関数と参照演算子（コロンや半角スペース）を併用することで、検索値に基づいた範囲参照をすることが…

Excelの新関数（2022年秋）であるTEXTSPLIT関数で、複数種類の区切り文字（文字列）を指定する方法です。 TEXTSPLIT関数の引数で区切り文字を「{"☆","〇"}」のように指定すれば、それら（☆と〇）のすべてを区切り文字（文字列）として区切った結果が得られま…

Excelで、IPアドレス（IPv4アドレス）を10進数表記から2進数表記に変換する方法についてです。 TEXTSPLIT関数等を使用してIPアドレスを2進数表記に変換することができます。手順 B列に10進数表記のIPアドレスが入力されています。 C3セルに式を入力し、下方…

Excelで、「3d6」（6面ダイスを3個振る）といった文字列が与えられたときにその結果を出力する例です。 RANDARRAY関数やTEXTBEFORE/TEXTAFTER関数を使ってダイスの結果を出力することができます。手順 画像ではB列にダイスの面数と個数を表す文字列が入力さ…

Excelで、貨幣の種類がどのようなものであっても1つの同じ式で金種計算を行う計算の例です。 式が複雑になり、決して実用的とは言えませんが参考まで。 REDUCE/LAMBDA関数を活用して、どのような金種にも対応できる金種計算を行うことができます。手順 B3:B1…

Excelで、関数によりナップザック問題（ナップサック問題）を解く例についてです。 なお、ここでのナップザック問題とは1種のアイテムについて1個選択するかしないかという選択肢しかない、0-1ナップザック問題です。 各種関数を組み合わせることでナップザ…

Excelで、指定した範囲のうち下からn行の範囲を抽出、または下からn行目（複数指定可）を抽出する方法についてです。 TAKE関数を使い、第2引数をマイナスの値にすることで下からn行を抽出できます。 CHOOSEROWS関数の第2引数をマイナスの値にすることで下か…

Excel、IPv4アドレスを昇順/降順で並べ替える方法についてです。具体的には第1オクテットを第1のキー、第2オクテットを第2のキー……として並べ替えます。 「区切り位置」メニューを使って4列に分割する方法が普通ですが、ここでは関数で並べ替える例を試して…

Excelでシンプルなクロス表を縦持ちのデータに置き換える、いわゆるアンピボットの例です。メニュー操作により進める方法も考えられますが、ここでは新しい関数を利用した方法を試してみます。 なお横持ち→縦持ち変換と異なり、横方向の見出しも抽出するので…

Excelでの、関数を使った素因数分解の例です。1セル内の式で完結させている内容となっています。 ただし、式はかなり面倒で実験的な内容といえます。 REDUCE/LAMBDA関数等を使って素因数分解を行うことができます。手順 B3セルに対象となる数値が入力されて…

Excelで、文字列を1文字ずつ各セルに分割し、所定の列数で折り返し、さらに改行を反映させる方法です。 表題にはExcel方眼紙と記しましたが、要は原稿用紙のようなマス目への転記です。 WRAPROWS関数等を組み合わせることで文字列をExcel方眼紙に転記するこ…

Excelで、文字列中に含まれる特定の部分文字列の数を数える方法についてです。 SUBSTITUTE/LEN関数を使う方法（部分文字列を空文字列に置き換えて文字数を比較する）が有名ですが、ここでは新しい関数を使う方法を試してみます。 TEXTSPLIT関数を使って、文…

Excelで、2つの行にひとまとまりのデータが記録されている場合に、1行にまとめ直す方法についてです。 3行以上の場合でも同じようにできます。 TOCOL関数やWRAPROWS関数を使って、2行ずつまとまっているデータを1行ずつに並べ替えることができます。手順 B2:…

Excelの新関数であるTEXTSPLIT関数を使うことで、文字列を区切り文字に基づいて分割することができます。 これにより分割した項目のうち最後の項目を取得する方法についてです。 「CHOOSECOLS(TEXTSPLIT(文字列,区切り文字),-1)」という式により、分割した文…

Excelで、関数によりクロス結合（CROSS JOIN、直積）を作成する方法です。 近年の関数の拡充によって方法はいろいろ考えられるようになりましたが、いずれもちょっと面倒です。 TOCOL/TEXTBEFORE関数等を利用してクロス結合（直積）を作ることができます。 R…

Excelで、既存のデータの各行の間に空白の行を挿入する方法についてです。 関数を使う方法とメニュー操作で行う方法を紹介します。いずれの方法でも、挿入する空白行を2行以上にする（1つのデータ行に対し2行以上の空白行を挿む）ことが可能です。 WRAPROWS…

Excelで、いわゆる横持ちのデータを縦持ちにする変換を関数で行う例です。 TOCOL関数等を使って横持ちのデータを縦持ちに変換することができます。手順 変換の対象となっているデータの範囲はB3:E6です。 これに対してG3セルに関数式を入力することで変換を…