Excel

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

ExcelのSORT関数で並べ替えのキーとなる列を2つ以上指定する方法についてです。 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の数値が並んでいます。 ここでC2セルに次のように入力します。 =MIN(MOD…

(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に数字が並んでいますので…

(Excel)値が入力されている最後の行の行番号と値を取得する

Excelで、指定した列のうち値が存在する(つまり空白でない)最後の行の行番号とその値を取得する方法についてです。 IF関数、ROW関数、MAX関数の組み合わせで行番号を取得できます。 さらにINDEX関数を使って値を取得できます。手順 データはB列に記録され…

(Excel)0と1からなる三角行列を生成する

Excelで、0と1の成分からなる上三角行列と下三角行列を作る方法についてです。 SEQUENCE関数と比較条件式を使って三角行列を作ることができます。手順 B2を左上端とする上三角行列と、B8を左上端とする下三角行列を作るものとします(どちらも5行5列)。 B2…

(Excel)勾配比と角度の換算

Excelで勾配比と角度(°)を相互に変換する方法についてです。 例えば「100m進んで1m登る坂(勾配比0.01)の角度は何度(°)か?」その逆に「角度1°の坂は勾配比はいくらか?」という計算ができます。 勾配比から、角度(°)は「=DEGREES(ATAN(勾配比))」で…

(Excel)FILTER関数でのAND検索とOR検索

ExcelのFILTER関数で、複数の抽出条件を設けてAND検索やOR検索をする方法についてです。 AND検索はそれぞれの条件をカッコで囲み「*」で連結することで可能です。Googleスプレッドシートと異なり、第3引数以降に条件を記してもAND条件としては機能しません。…

(Excel)文字列から数字だけを抽出する

Excelで、文字列に含まれる数字だけを取り出す方法についてです。 対象とするのは半角数字とします。数字は文字列として抽出し、0で始まる数字もそのまま抽出するものとします。 SEQUENCE関数やMID関数等を使って文字列から数字を抽出することができます。 …

(Excel)各文字の間に指定した文字列を挿入する

Excelで、関数を使ってセル内の各文字の間に任意の文字列を挿む方法についてです。 具体的な利用例としては「1文字ごとにスペースを入れる」とか「1文字ごとにハイフンを入れる」といったものが考えられます。 MID関数やSEQUENCE関数などを利用して、文字列…

(Excel)縦1列のデータを表に並べ替える

Excelで縦1列に並んでいるデータを、関数を使って2列以上の表に並べなおす方法についてです。 INDEX関数とSEQUENCE関数を組み合わせて、縦1列のデータを2列以上の表に並べ替えることができます。手順 早速ですが結果です。 B2:B13に12個のデータが並んでいま…

(Excel)表のデータを1列に並べ替える

Excelで2列以上の表に並べられているデータを、関数を使って1列に並べなおす方法についてです。 INDEX関数とSEQUENCE関数などを用いて、表に並んでいるデータを縦1列に並べ替えることができます。手順 B2:E4の範囲に12個のデータが並んでいます。これを縦1列…

(Excel)1行おきにデータを抽出する

Excelで表から1行おきにデータを抽出する方法についてです。 FILTER関数とROW関数などを使って1行おきにデータを抽出することができます。 FILTER関数のメリットとして複数列のデータを抽出することができます。手順 B2:B8にデータが並んでいますので、ここ…

(Excel)1からnまでを繰り返す連番を作る

Excelで1,2,3,4,1,2,3,4……といったように同じ並びを繰り返す連番を、関数を使って作る方法についてです。 MOD関数を利用して同じ並びを繰り返す連番を作成できます。手順 例として、1から4までの連番を縦方向に3回繰り返すものとします(合計12行)。 B2セル…

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

Excelで、カンマやハイフンなどで多数に区切られた文字列を分割して横方向のセルに展開する方法についてです。メニューで実行する方法もありますがここでは関数で実現してみます。 VBAのSplit関数の再現です。 MID関数、SUBSTITUTE関数、SEQUENCE関数などの…