ExcelのXMATCH関数でAND条件（かつ）やOR条件（または）を指定して値を取得する方法についてです。 考え方はXLOOKUP関数の例と同じです。 XMATCH関数の第2引数（検索範囲）として複数の列の値を&でつないだものを指定すれば、複数の列に対するAND条件にする…

VLOOKUP関数で検索範囲よりも左側の列にある値を取得する方法についてです。 VLOOKUP関数とHSTACK関数を併用すれば検索範囲の列よりも左側の値を取得できます。 ただしVLOOKUPにこだわる理由がなければXLOOKUP関数の使用をおすすめします。 手順 代替案 XLOO…

ExcelでUNIQUE関数の対象に空白セルがあると「0」として出力されてしまいますが、これを空文字列（=""）にする方法についてです。空文字列に限らず任意の値に変換することができます。 併せて、そもそも空白セルを削除してしまう方法にも触れておきます。 UN…

Excelで、数値そのものにOR条件（または）をつけて合計を求める例や、複数の列にOR条件をつけて該当する行の数値を合計する例です。どちらの方法でも、両方の条件に該当する値を差し引くという手間が必要がなく、3つ以上の条件にも対応できます。 SUM関数とI…

Excelで、値そのものにOR条件（または）をつけてカウントする（つまり、いずれかの条件を満たす値の数を数える）例や、複数の列にOR条件をつけて件数（行数）をカウントする例です。どちらの方法でも、両方の条件に該当する値の数を差し引く手間が必要がなく…

ExcelのRANK関数で、同順位（n位タイ）があってもその次の数字（n+1位）を飛ばさずに順位をつける方法についてです。 XMATCH関数とUNIQUE関数等を併用して、同順位となるものが複数あっても次の順位を飛ばさないように順位をつけられます。 手順 文字列等を…

ExcelのFILTER関数で、対象となる表のうち取得する列を指定し、その中で条件を満たす行を抽出する方法についてです。 FILTER関数とHSTACK関数を併用すれば、HSTACK関数で指定した列だけを対象としてFILTER関数を実行できます。 抽出する列の見出しを用意して…

Excelで表からの検索を行うのに便利なXLOOKUP関数の使い方についてです。 XLOOKUP関数を使うことでVLOOKUP関数よりも簡単に表引き（表からの検索による値の取得）を行うことができ、機能もより充実しています。 機能と構文 基本的な使用例 第4引数（見つから…

Excelで複数行複数列のデータを縦1列に並べ替える、TOCOL関数の使い方についてです。 TOROW関数も並べる方向が違うだけでほぼ同じです。 「=TOCOL(範囲)」という式で、複数行複数列のデータを縦1列に並べ替えることができます。 機能と構文 基本的な使用例 …

Excelで、既存の表を指定した列数の表に並べ直す方法についてです。 WRAPROWS関数とTOCOLまたはTOROW関数を併用して、表の列数を変えて並べ直すことができます。手順 次の画像では、B2:C12の範囲にある2列の表を、関数を使って4列の表に並べ直しています。 E…

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

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

Excelで、クロス抽出とは逆に表内の値から見出し列（行）の内容を抽出する方法です。 XLOOKUP関数とTOCOL関数等を使って、クロス抽出とは逆に検索値から見出しを抽出できます。 FILTER関数とTOCOL関数等を使えば、同じ検索値が複数ある場合に見出しをすべて…

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引数をマイナスの値にすることで下か…