いきなり答える備忘録

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

Excel

(Excel)TOCOL関数の使い方

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

(Excel)表を指定した列数に並べ直す

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

(Excel)UNIQUE関数で複数列から値の重複を除く

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

(Excel)TEXTSPLIT関数で縦に分割する

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

(Excel)クロス抽出の逆の抽出をする

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

(Excel)レーベンシュタイン距離を求める

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

(Excel)すべての約数を求める

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

(Excel)カンマ区切り文字列からn番目の項目を求める

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

(Excel)TEXTSPLIT関数の使い方

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

(Excel)セルを指定回数並べた配列・行列をつくる

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

(Excel)TOCOL/TOROW関数をエラーや空白セルの削除に使う

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

(Excel)カッコ内の文字列を抽出する

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

(Excel)XLOOKUP関数を縦横にスピルさせる

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

(Excel)TEXTSPLIT関数をスピル(複数のテキストに適用)させる

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

(Excel)特定の文字(文字列)を含む行の抽出方法いろいろ

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

(Excel)XLOOKUP関数と参照演算子を使った範囲参照

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

(Excel)TEXTSPLIT関数で複数種の区切り文字を指定する

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

(Excel)IPアドレスを2進数表記に変換する

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

(Excel)ダイスロールのシミュレート

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

(Excel)一般化した金種計算の例

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

(Excel)ナップザック問題を解く(総当たり法)

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

(Excel)表の下からn行/n行目を取得する

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

(Excel)アルファベットの連番を生成する

ExcelでA,B,C……Z、さらにAA,AB,AC……ZZ、と続くアルファベットの連番を作成する方法です。 ADDRESS関数を使って数値に対応する列番号(記号)を取得する方法が有名ですが、XFDまでしか取得できない欠点がありますので、ここでは別の方法を試してみます。 BASE…

(Excel)IPアドレスをソートする

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

(Excel)クロス表を縦持ちに変換する

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

(Excel)素因数分解をする

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

(Excel)改行を含む文字列のExcel方眼紙への転記

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

(Excel)文字列中の特定の文字列をカウントする

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

(Excel)データを2行ずつ1行に並べ直す

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

(Excel)TEXTSPLIT関数で分割した最後の項目を取得する

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