Excel

(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関数の組み合わせで、英数字の混じったランダムな文字列を生成することができます。 長い文字列を作る場合や英字のみにする場合にはRANDARRAY関…

(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関数などの…

(Excel)縦持ちのデータを横持ちに変換する

Excelで、縦に並んだ表の値をグループごとに分類して横方向に並べる、いわゆる縦持ちから横持ちへの変換を関数で行う例です。 UNIQUE関数等を利用して縦持ちの表を横持ちに変換することができます。手順 B3:C10の範囲に、対象となる縦持ちのデータが並んでい…

(Excel)文字列を逆に並べ替える

Excelでセルに記録されている文字列を逆から(後ろから)並べ替える方法についてです。 VBAだとStrReverse関数がありますが、ここでは別の方法を試してみます。 SEQUENCE関数を利用して、文字列を逆から並べ替えることができます。手順 B3セルに文字列が記録…

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

Excelでセルに入力されている文字列を1文字ずつ分割して各セルに表示する方法についてです。 SEQUENCE関数を利用することで文字列を分割することができます。 複数行複数列にわたって分割することもできます。手順 B2セルに対象となる文字列が記録されていま…

(Excel)和暦の「1年」を「元年」と表示する

Excelでの和暦の元年表記についてです。元号は日付で区別するものとします(例:2019/4/30→平成31年4月30日、2019/5/1→令和元年5月1日)。 ここでは条件付き書式を用いる例を示します。メリットは条件式が1本で済み、Excelが対応しているすべての元号(大正…

(Excel)書式設定により改行する

セルの書式設定により改行させようとする場合の設定についてです。 「セルの書式設定」ダイアログの「表示形式」タブ内の「種類」欄で「Ctrl+J」の同時押しをすることにより、書式としての改行ができます。 同じダイアログの「配置」タブの「折り返して全体…

(Excel)セル結合をせずに複数セルの中央に文字列を表示する

Excelで複数のセルの中央に文字列を表示させる場合、セルの結合を用いる方法がありますが、その他にセルの書式設定の「横位置」の設定でも実現できます。 ただし可能なのは横方向(の中央)だけです。 セルの書式設定の「横位置」の設定により、複数のセルの…

(Excel)枠線(グリッド線)の表示・非表示を切り替える

シートに最初から表示されている枠線(グリッド線。縦横方向の薄い線)の表示・非表示を切り替える方法についてです。 「表示」タブにある「目盛線」のチェックボックスで枠線の表示・非表示を切り替えられます。手順 「表示」タブを選択して、メニューにあ…

(Excel)セル内の文字を上下逆にする

セルに記録されている文字を180度回転させ上下逆にする方法についてです(上下反転した鏡像ではありません)。 フォントの指定により文字を90度回転させ、さらにセルの書式設定により90度回転させることで、文字を180度回転させることができます。手順 対象…