いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

Excel

(Excel)区切り文字による文字列の分割を関数で行う

Excelで、関数を使って区切り文字による文字列の分割を行う方法についてです。つまりはワークシート関数によるSPLIT関数の実現です。 FILTERXML関数などを使って、区切り文字による文字列の分割を行うことができます。 ただし「手順 画像ではB3セルに対象と…

(Excel)表の中のn行目(n列目)を抽出する

Excelで、表の中の(つまり指定した範囲内の)n行目の内容を取得する方法についてです。n列目の内容を取得することもできます。 「=INDEX(範囲,n,)」という式により、表の中のn行目の内容を抽出することができます。 「=INDEX(範囲,,n)」という式により、n列…

(Excel)クロス表からデータを検索・取得する

Excelで、検索値に基づいて表の中の行と列を特定し、行と列が交差するセルのデータを取得する方法についてです。 XLOOKUP関数を使ってクロス表からデータを抽出することができます。ネストは必要ですが1つの関数で済むのがメリットです。 オーソドックスな方…

(Excel)関数を使ったグループ化と集計

Excelにはメニュー操作によるグループ化と集計の機能がありますが、ここではより簡単に関数を使って値をグループ化し、その値ごとに数値を集計する例を試してみます。 UNIQUE関数を使って値をグループ化した結果を得ることができます。 さらにSUMIF関数など…

(Excel)XLOOKUP関数で複数条件の検索を行う

ExcelのXLOOKUP関数を使って、複数の条件による検索をする方法についてです。 ただしここで「複数の条件」とは、複数列にわたる条件のことです。1つの列に対して2つ以上の検索値を設定して検索する、というものではありません。 XLOOKUP関数の第2引数(検索…

(Excel)一番右の値を取得する

Excelで、指定した行のうち値が存在する(空白でない)最も右の行の値を取得する方法についてです。 XLOOKUP関数を使って列の一番右の値を取得できます。 INDEX関数とFILTER関数を使う方法もあります。 手順 XLOOKUP関数を用いる方法 INDEX関数とFILTER関数…

(Excel)関数を使って抽選をする方法

EXCELで、関数を使って抽選を行う方法についてです。 すべての対象者にランダムな順位を与える例と、一定の数の当選者をランダムに決定する例を紹介します。 RANDARRAY関数やSORTBY関数などを使って、順位をつける抽選や当選者を決める抽選を行うことができ…

(Excel)表のデータを上下逆・左右逆に並べ替える

Excelで、関数を使って表の内容を上下逆あるいは左右逆に並べ替える方法についてです。データの内容に関係なく並びを逆転させます。 SORTBY関数を使って表を上下逆・左右逆に並べ替えられます。 手順 上下逆に並べ替える例 左右逆に並べ替える例 手順 画像の…

(Excel)SORTBY関数で左右方向に並べ替えることができる

ExcelのSORTBY関数には(SORT関数と異なり)並べ替え方向を指定する引数がありませんが、左右方向への並べ替えは可能です。 SORTBY関数の第2引数(並べ替え基準の範囲。第4,第6…引数も同様)で左右方向の配列を指定すれば、左右方向への並べ替えとなります。…

(Excel)1列に並んだ値を斜めに並べる

Excelで1列に並んでいるデータを、関数を使って斜め方向に並べなおす方法についてです。 対角行列とINDEX関数を用いることで、1列並んでいる値を斜めに並べることができます。手順 いきなりですが結果です。 画像ではB3:B7に並んでいる5つの値を、D3セルに式…

(Excel)任意の対角行列を生成する

Excelで対角行列(非対角成分がすべて0)を生成する方法についてです。対角の各要素の値を任意に設定することができます。 「=配列*MUNIT(配列の長さ)」という式で、配列の各要素を対角要素とする対角行列が作れます。手順 B3:E3に、対角行列の成分とする各…

(EXCEL)SORT関数で複数の並べ替えキーを指定する

ExcelのSORT関数で並べ替えのキーとなる列を2つ以上指定する方法についてです。単純にSORT関数をネストする方法もありますが、別の方法で複数のキーを設定し、昇順降順も独立して設定することができます。 SORT関数の第2引数(並べ替えキー)と第3引数(昇順…

(Excel)ランダムなパスワードを作成する

Excelで、使える文字と長さを指定してパスワード(ランダムな文字列)を作る方法についてです。 MID関数とRANDARRAY関数等を利用してパスワードを生成することができます。手順 画像ではB3セルで使用可能文字が、C3セルで長さ(パスワードの文字数)が設定さ…

(Excel)文字列中の文字をランダムに並べ替える

Excelで1つの文字列をシャッフルする、つまり1文字単位でランダムに並べ替える方法についてです。 各種関数の組み合わせにより文字列をランダムに並べ替えることができます。手順 画像ではB2セルに式を入力することにより「ABCDEvwxyz」の10文字をランダムに…

(Excel)検索値の1つ隣(上下左右)の値を取得する

Excelで、値をリストから検索し、マッチしたセルの1つ隣にある値を取得する方法についてです。 XLOOKUP関数を使って、検索値が存在するセルの1つ隣(上下または左右)にある値を取得することができます。手順 B列に駅名のリストが並んでいます。 また、E3セ…

(Excel)文字列を2文字ずつ(n文字ずつ)分割する

Excelで、文字列を2文字ずつ(n文字ずつ)区切って各セルに分割する方法についてです。 MID関数やSEQUENCE関数等の組み合わせにより、文字列を任意の文字数で区切って分割することができます。 手順 2文字ずつ分割する場合 n文字ずつ分割する場合 手順2文字…

(Excel)REPLACE関数を使って文字列中に文字を挿入する

ExcelのREPLACE関数についてです。この関数は文字列中の文字を別の文字に置き換えるための関数ですが、引数の指定により文字(文字列)を挿むこともできます。 REPLACE関数の第3引数(置き換える文字数)を「0」と指定する(または何も指定しない)ことで、…

(Excel)XLOOKUP関数でワイルドカードを使った検索をする

ExcelのXLOOKUP関数は基本的にVLOOKUP関数より簡単にデータの抽出ができますが、検索値にワイルドカードを用いる場合にはそのことを引数で指定する必要があります。 XLOOKUP関数で検索値にワイルドカードを用いる場合、第5引数(一致モード)に「2」を指定す…

(Excel)FIND関数で複数の検索文字列を指定する

ExcelのFIND関数で、検索文字列(検索条件)を複数指定して最初に出現するものの位置を取得する方法についてです。 例として文字列に含まれる「-」と「/」のうち、先に出現するものの位置を取得することができます。 FIND関数の第2引数(検索文字列)に配列…

(Excel)FIND関数で複数の位置・n番目の位置・最後の位置を取得する

ExcelのFIND関数は、検索文字(列)が出現する最初の位置を返します。 これを応用して検索文字(列)が複数回出現する場合にすべての位置を取得する方法を示します。 また、n番目に出現する位置や、最後に出現する位置も取得できますので併せて示します。 FI…

(Excel)拗音・促音を大きい文字にする

Excelで、文字列中の「ャュョ」や「ッ」といった小さいカナをまとめて大きくする、つまり大きいカナに変換する方法についてです。変換対象とする文字種が少ない場合はSUBSTITUTE関数を重ねればよいのですが、ここでは別のアプローチを試してみます。 ひらが…

(Excel)1から100までの偶数/奇数の和を求める

Excelで1から100までに含まれる偶数の和と奇数の和をそれぞれ求める方法についてです。 お約束として和の公式は用いず、1から100までの数値を出力した上で算出するものとします。 SUM関数とEVEN関数、SUM関数とISEVEN関数といった組み合わせで1から100までの…

(Excel)FILTER関数で列を抽出する

ExcelのFILTER関数は条件に該当する行を抽出するだけでなく、条件に該当する列を抽出することができます。 FILTER関数で第2引数以降(条件)として行方向の配列を指定することで、列を抽出できます。手順 C2:G5の範囲にデータが並んでいます。 ただし縦1列が…

(Excel)数値が素数かどうかを判定する

Excelで、数値が素数なのかどうかを区別する方法についてです。 MOD関数とSEQUENCE関数などを組み合わせて、数値が素数かどうかを判定することができます。手順 B2:B14の範囲に3から15の数値が並んでいます。 これらの数値が素数かどうかをそれぞれ判定しま…

(Excel)重複のないランダムな整数を生成する

Excelで、値の重複がないように整数をランダムに並べる方法についてです。 SEQUENCE関数とRANDARRAY関数などを組み合わせて、重複しないランダムな整数の並びを作ることができます。手順 適当なセルに次のように入力します。 =SORTBY(SEQUENCE(10),RANDARRAY…

(Excel)ランダムな英数字の列を生成する

Excelで、ランダムな英数字混合の文字列を生成する方法についてです。なお、英字は大文字のみとします。 RANDBETWEEN関数とBASE関数の組み合わせで、英数字の混じったランダムな文字列を生成することができます。 長い文字列を作る場合や英字のみにする場合…

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

Excelで、文字列の中に含まれている特定の(指定した)文字の数をカウントする方法についてです。 LEN関数とSUBSTITUTE関数による方法が有名ですが、ここでは少し異なる方法を試してみます。 SEQUENCE関数、MID関数等を使って文字列中の特定の文字をカウント…

(Excel)式の結果を除いて集計する

Excelで、式の結果として表示されている値以外の値を集計する方法、言い換えれば値そのものが入力されているセルだけを集計する方法についてです(逆に式の結果だけを集計することもできます)。 はじめからSUBTOTAL関数を駆使すればこのような集計は可能で…

(Excel)1つの式で累計を求める

Excelで累計を求める場合には「=SUM(B$3:B3)」といった式を置いてあとは下にコピー、というやり方が一般的ですが、1つのセルに式を入力するだけで各行(列)に累計を表示することも可能です。 MMULT関数(行列積)などを使い、1つのセルに式を入力するだけで…

(Excel)ISEVEN・ISODD関数に配列を渡すとエラーになる現象と対策

ExcelのISEVEN関数とISODD関数の伝統的な仕様についてです。 ISEVEN関数とISODD関数は、引数が配列(行列)だと「#VALUE!」を返します。 引数に「*1」を加えるとエラーになりません。他の演算や関数を加えても同様です。手順 B2:B9に数字が並んでいますので…