Excel。数値を右詰で1セル1桁で表示するには【1 digit】

Excel。数値を右詰で1セル1桁で表示するには

＜REPT+LEN関数とMID+SEQUENCE関数＞

請求書や領収書などの金額を1セル1文字で入力されたものをつくりたい場合、どのようにしたら、手早く、1セル1文字で表示することができるのでしょうか。

次の表をご覧ください。

A1の値をB3:I3の1つずつのセルに1桁ずつ表示したいのですが、どのようにしたら、効率よく表示することができるのでしょうか。

このような場合、SEQUENCE関数をつかうことで解決します。

なお、SEQUENCE関数とは、指定した範囲の連続した数値を自動的に生成する関数です。

SEQUENCE関数にMID関数を組み合わせすことで対応できます。

では、B3に次の数式を設定します。

=MID(A1,SEQUENCE(1,8),1)

1セルに1文字ずつ入力することができました。

数式を確認してみましょう。

MID関数は、指定した文字列の中から、任意の位置から指定した文字数分だけ文字を抽出する関数です。

最初の引数は、文字列なので、A1を設定します。

2つ目の引数は、開始位置です。

開始位置に、SEQUENCE関数をつかいます。

SEQUENCE関数はあとで説明します。

3つ目の引数は、文字数。

1文字ずつなので、1を設定します。

2つ目の引数につかっていた、SEQUENCE関数を見ていきましょう。

SEQUENCE関数の最初の引数は、行です。1行で表示したいので、1と設定します。

2つ目の引数は、列です。

今回は、最大8桁までの数値に対応することにしたので、8と設定しました。

SEQUENCE関数は、スピル対応の関数なので、自動的に数式を拡張してくれます。

MID+SEQUENCE関数で対応することができたのですが、よくみると、左詰になっています。

一番右側に「円」と表示させているので、できたら、右詰にしたい。

A1の値を修正したものがB1にあります。

B1の数式は、

=REPT("　",8-LEN(A1))&A1

A1は6桁しかありませんでした、8桁にすれば解決できますが、０をつけて00123456としても、0は表示されません。

よって、0を先頭の前につけても8桁にすることはできません。

そこで、空白と合体させることで対応することができるようになります。

何文字分の空白を追加すればいいかを判断したいので、REPT関数をつかって、空白文字を繰り返します。

数値が何桁なのかは、LEN関数をついかいます。

あとは、先ほど設定した

=MID(A1,SEQUENCE(1,8),1)を

=MID(B1,SEQUENCE(1,8),1)

と修正して完成です。

Excel。ダブらないで1から10までをランダムで並べるにはどうしたらいい

＜SORTBY+SEQUENCE+RANDARRAY関数＞

参加者名簿があります。

C列の順番には、1から参加者数の10人分である10までの数値をランダムで並べたい。

しかも、1が2回登場するようなことがないように、つまり重複しないようにしたいわけです。

今回は10名なので、自力で振り分けることもできますが、件数が多くなれば、とても大変な処理になってしまいます。

何かいい方法は、ないのでしょうか。

そこで、C2に次の数式を設定してみます。

=SORTBY(SEQUENCE(10),RANDARRAY(10))

この数式で、１から10までの数値をランダムで並べ替えることができました。

使用している関数は、スピル機能対応なので、絶対参照や、オートフィルで数式をコピーする必要はありません。

この数式がどのように動くことで、1から10までの数値をランダムで並べることができるのか、関数を確認していきます。

SORTBY関数は、並べ替えをする関数です。

最初の引数は、「配列」。

2つ目の引数が「基準配列1」なのですが、それぞれ関数をつかっていますので、最初の引数で使用している

最初の引数を使用している関数は、SEQUENCE関数です。連番を作る関数です。

SEQUENCE(10)　で、1から10までの連番を設定することができます

まず、重複しない1から10の数値を用意することができました。

この数値をランダムで並べ替える必要があるわけです。

そこで、RANDARRAY関数をつかって、乱数を発生させます。

小数の乱数を発生するので、算出結果が重複することは、まずありません。

この2つの関数をつかって、1から10までをランダムで並べ替えた順番をつくることができたというわけです。

なお、セルをアクティブなどするたびに、再計算されます。

説明の都合で、C列の順番を作成した後に、F列をつくっています。

そのため、F列の結果とC列の結果は合致しませんので、ご了承ください。

Excel。データを入力したら自動で通し番号を入力するようにしたい。【serial number】

Excel。データを入力したら自動で通し番号を入力するようにしたい。

＜SEQUENCE+COUNTA関数＞

データが入力されると自動的に通し番号が入力できれば、ほんの少しかもしれませんが、入力作業も改善できるかもしれません。

では、どのようにしたら、データを入力したら、自動的に通し番号を入力できるのでしょうか。

今回は、SEQUENCE関数をつかった方法をご紹介していきます。

次の表を用意しました。

 

B列に利用者名を入力すると、A列に通し番号が自動的に入力するようにしたいわけです。

A2にSEQUENCE関数をつかった数式を設定します。

=SEQUENCE(COUNTA(B:B)-1)

あとは、スピル機能によって、ゴーストが発生しますので、オートフィルで数式をコピーする必要はありません。

B列にデータが入力されるたびに、自動的に、スピル機能によって、通し番号が自動的に入力してくれます。

数式を確認しておきましょう。

SEQUENCE関数は、「数列を返す」関数です。

最初の引数は、「行」です。

そこで、COUNTA関数でB列のデータに入力されている件数を算出させて、見出し行分を減算しますので、「-1」を追記ます。

これで、通し番号を入力することができます。

なんで、SEQUENCE関数をつかうのでしょうか。

それは、スピル機能に対応した関数だということ。

自動的に拡張してくれます。

次のような関数でも、同じような処理をすることができます。

=IF(F2<>"",ROW()-1,"")

ただ、この数式では、数式をコピーする必要があるということです。

今回は、新しい関数であるSEQUENCE関数をつかってみました。

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい【character per cell】

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい

＜MID＋TEXT＋SEQUENCE＋LEN関数＞

銀行の入出金用紙などで、口座番号を一桁ずつ右から埋めていくわけですね。

次の表のように、Excelで手早くやるにはどうしたらいいのでしょうか。

例えば、5桁として、それに満たない場合には、「0（ゼロ）」をいれるようにもしたいわけです。

これを、SEQUENCE関数を中心とした、一つの数式で、手早く処理することができます。

C2には、

=MID(TEXT(A2,"00000"),SEQUENCE(1,LEN(TEXT(A2,"00000"))),1)

という数式を設定します。

あとは、オートフィルで数式をコピーします。

MID+SEQUENCE+LEN関数の組み合わせで、1つのセルに1文字ずつわりふることができます。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

とすれば、文字列の場合ならば、対応できるのですが、数値だと、次のように、左側から割り振ってしまいます。

そのため、表示形式のTEXT関数をつかって対応する必要があるというわけです。

数式を確認しておきましょう。

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

ただ、桁が足らない場合、「0（ゼロ）」を表示したいので、TEXT関数をつかって、5桁にするようにします。

なので、最初の引数は、「TEXT(A2,"00000")」と設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(TEXT(A2,"00000"))とします。

あとの引数は省略できますので、省略。

これで、数値の場合対応することができるというわけです。

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの【one character per cell】

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの

＜MID+SEQUENCE+LEN関数＞

SEQUENCE関数の登場で、今までは複雑な数式、あるいは、中間結果を算出してから結果を求めるなどしていた数式を、シンプルな数式にすることができます。

今回は、SEQUENCE関数をつかって、手早く、文字列をセルに1文字ずつに分けて表示する方法をご紹介します。

A列には、サンプルの文字列を用意しました。

文字数はバラバラ。

A5は、「立花 宗茂」と入力してあって、苗字と名前の間に半角スペースが入力されています。

C2に、SEQUENCE関数をつかった数式を設定します。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

あとは、C5まで、オートフィルで数式をコピーするだけで、文字列をセルに1文字ずつに分けて表示することができました。

数式を確認しておきましょう。

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。

何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。

複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。

何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(A2)とします。

あとの引数は省略できますので、省略。

これで、文字列をセルに1文字ずつに分けて表示することができるというわけです。

Excel。アルファベットや丸数字はオートフィルの連続コピーができないので、どうするの【alphabet】

Excel。アルファベットや丸数字はオートフィルの連続コピーができないので、どうするの

＜CHAR+CODE+SEQUENCE関数＞

アルファベットや丸数字は、オートフィルで連続コピーをすることができません。

なぜかというと、「文字列」扱いだからですね。

ユーザー設定リストに登録してもいいのですが、面倒です。

 

そこで、関数をつかうことで、手早くアルファベットや丸数字を連続コピーすることができます。

C2に設定した数式は、

=CHAR(CODE("①")+SEQUENCE(5,,0))

SEQUENCE関数によるスピル機能によって、オートフィルをつかわないで、指示した件数の連続データをつくってくれます。

今回のポイントは、文字列には「+1」することはできませんが、文字コードならば「+1」することで、次の文字コードの文字を表示することが可能です。

そこで、一度、文字から文字コードを抽出するために、CODE関数で文字コードを算出します。

それが、CODE("①")

これで、「①」の文字コードを算出することができます。

その値に、以前はROW関数で行番号を和算させる数式をつくってオートフィルしていました。

今は、SEQUENCE関数があるので、事前に作成するデータ数がわかっているようならば、SEQUENCE関数をつかうことで、手早く連続データをつくることができます。

SEQUENCE(5,,0)を確認しておきましょう。

最初の引数は、「行」。

今回は「⑤」まで表示したいので、「5」とします。

2つ目の引数の「列」は不要なので、省略。

3つ目の引数は、「開始」文字コードに「+1」したいだけなので、省略してしまうと、1件目が「+1」されてしまうので、「0」にします。

これで、1～5の数値をつくることができます。

この数値を文字コードに和算します。

このままだと文字コードのままなので、CHAR関数をつかって、文字コードに基づく文字を表示してくれるという仕組みです。

このような方法を使うことで、アルファベットや丸数字を連続データをつくることができます。

Excel。最新関数で、手早く重複なく整数をランダムで表示したい。【integer】

Excel。最新関数で、手早く重複なく整数をランダムで表示したい。

＜SORTBY+SEQUENCE+RANDARRAY関数＞

整数の数値をランダムで表示したい。

しかも、重複しないようにしたい場合、Microsoft365やExcel2021で登場した3つの関数を使うことで、手早く算出することができます。

今回は、1～10までの整数とします。

A1に次の数式を設定します。

=SORTBY(SEQUENCE(10),RANDARRAY(10))

これで、重複しないで1～10の数値をランダムで表示することができます。

今までは、RAND関数で一度、ランダムの数値を算出して、その数値の順位を算出する。

その後、RANK.EQ関数をつかって、重複しないランダムの数値をつくっていました。

手間が結構かかったわけですね。

ところが、一つの数式で、算出できるようになったというわけです。

SORTBY関数とSEQUENCE関数とRANDARRAY関数で、算出することができるようになりました。

では、

=SORTBY(SEQUENCE(10),RANDARRAY(10))

の仕組みを確認しておきましょう。

SORTBY関数は、範囲内にある値を並べ替えする関数です。

最初の引数は、「配列」です。SEQUENCE関数をつかって、1～10の値をつくります。

SEQUENCE(10)とすることで、行ごとに1から10の数値を算出してくれます。

これで、1～10の範囲を得ることができます。

2つ目の引数は、「基準配列1」です。並べ替えを行うための基準です。

ここに、RANDARRAY関数をつかって、ランダムの数値をつくり、基準とします。

SORTBY関数とSEQUENCE関数とRANDARRAY関数は、スピル機能に対応した関数なので、オートフィルで数式をコピーする必要はありません。

そのため、A1に数式を設定するだけで済みます。

今回紹介した、SORTBY関数とSEQUENCE関数とRANDARRAY関数をはじめとして、新しい関数が色々追加されています。

確認してみると、可読性の高い数式に修正することができるかもしれませんね。

Excel。カレンダーの末日問題はSEQUENCE関数の登場で、劇的に変わりました。【last day】

Excel。カレンダーの末日問題はSEQUENCE関数の登場で、劇的に変わりました。

＜SEQUENCE+DATE関数＞

カレンダーを作るときに、いつもネックになるのが、末日問題。

例えば、3月なら31日。

2月のうるう年なら29日までの表示にしたいわけです。

単純に上のセルを「+1」すると、翌月1日が表示されてしまうので、様々な条件を考えて、IF関数をつかうなどして、対応していました。

ところが、SEQUENCE関数という新しい関数が登場したことで、全く考え方が変わりました。

このSEQUENCE関数は、指定した回数の連番を設定することができます。

SEQUENCE関数がどのような関数なのかを確認しておきます。

A1に、

=SEQUENCE(5)

と設定しました。

すると、A1:A5に1からの連番を設定してくれました。

A1に設定した数式は、スピル機能によって、自動的に数式が拡張されます。

（A2:A5はゴースト）

引数に5と設定したので、5までの連番を設定してくれたわけです。

つまり、引数にDAY関数をつかってあげれば、日にち分の連番を入力してくれるはずです。

そこで、年月をセルに用意して、簡易カレンダーを作ってみました。

A1には、年を設定します。「2024」としました。

A2には、月を設定します。「2」としました。

2024年2月は、うるう年なので29日まであります。

A5にSEQUENCE関数をつかって設定した数式は、

=SEQUENCE(DAY(DATE(A1,A2+1,0)))

これで、日にちが表示されます。

また、曜日も連動させたいので、

B5には、

=TEXT(DATE(A1,A2,A5#),"aaa")

という数式を設定します。

これで、完成です。

SEQUENCEをつかうことで、末日問題から、少しは逃げることができるかもしれませんね。

それでは、数式の内容を確認しておきましょう。

=SEQUENCE(DAY(DATE(A1,A2+1,0)))

引数の、列に設定してあるのが、DAY関数をつかって、「日」を抽出しています。

それが、

DAY(DATE(A1,A2+1,0))

DATE関数をつかって日にちをつくるわけですが、「月」を「月+1」として「日」を「0」とすることで、前月の末日をつくることができます。

そして、算出した日付の日をDAY関数で、抽出することで、末日の数値を抽出することができるというわけです。

あとは、曜日の数式です。

=TEXT(DATE(A1,A2,A5#),"aaa")

なんで、A5を参照しないで、改めてDATE関数をつかって、日付をつくっているのかというと、SEQUENCE関数は、日付ではなくて、数値を算出しています。

そのため、新たに、DATE関数をつかって日付を作る必要があります。

また、算出された結果を、オートフィルで数式をコピーするよりも、スピル機能をつかって、自動的に末日と連動させたいので、

DATE(A1,A2,A5#)

の引数の「日」を「A5#」と設定します。

今回紹介したSEQUENCE関数をはじめ、新しく追加された関数を知ることで、今まで色々考えないといけなかった数式がシンプルにすることができるかもしれませんね。

Excelの様々な関数の読み方や引数などを紹介。今回は、SECOND関数～SHEETS関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SECOND関数～SHEETS関数です。

＜Excel関数辞典：VOL.71＞

今回は、SECOND関数～SHEETS関数までをご紹介しております。

SECOND関数

読み方： セコンド　　

分類： 日付時刻 

SECOND(シリアル値)

時刻から""秒""を算出する　

SEQUENCE関数

読み方： シークエンス　　

分類： 数学/三角 

SEQUENCE(行,[列],[開始],[目盛り])

数列を返す　

SERIESSUM関数

読み方： シリーズサム　　

分類： 数学/三角 

SERIESSUM(x,n,m,係数)

べき級数を算出する　

SHEET関数

読み方： シート　　

分類： 情報 

SHEET([値])

シートが何枚目かを算出します　

SHEETS関数

読み方： シーツ　　

分類： 情報 

SHEETS([参照])

シートの数を算出します　

Excel。SEQUENCE関数。連番を作るのに画期的な関数が登場しました。【SEQUENCE】

Excel。SEQUENCE関数。連番を作るのに画期的な関数が登場しました。

＜SEQUENCE関数＞

Office365のInsiderで新しく追加された関数がいくつかあります。

そのなかで、今回は【SEQUENCE関数】をご紹介していきます。

このSEQUENCE関数。
正直いうと、あまり使用しない人が多いかもしれませんが、結構面白いことができます。

次の表をご覧ください。

5の倍数ずつ増えていき、5列したら、下の行に表示している、この表を作る場合。

例えば、A1に5とB10に10と入力して、オートフィルを使うなど、色々なアイディア・方法で作ると思います。

どの方法でも簡単に作れることは作れますが、面倒なのは間違いありません。

このように、指定した行数・列数。
そして、プラスしていく数が決まっている時に役立つ関数。
それが、【SEQUENCE関数】なのです。

それでは、A1をクリックして、SEQUENCE関数の数式を作っていきます。

SEQUENCE関数の引数は、
SEQUENCE（行,列,開始,目盛り）で構成されています。

行は、4行でつくりたいので、4。
列は、5列でつくるので、5。
開始は、5の倍数にしたいので、5。
最後の目盛りは、いくつずつプラスしていくのかということで、5倍ですから、5。

数式を完成させると、Office365のInsiderで新たに追加された『スピル』機能によって、オートフィルで数式をコピーする必要はありません。

あっという間に、数値が入力されました。

A1の数式は、
=SEQUENCE(4,5,5,5)

この関数は、スピル機能と一体になっているからこそ、使える関数ですね。

なお、この関数は、正数だけではなくて、負数にも対応しています。
=SEQUENCE(4,5,-1,-5)
という数式に変更してみると、

さらに、小数にも対応しています。

=SEQUENCE(4,5,0.1,0.5)
という数式に変更してみます。

アイディアで使える関数みたいですね。

例えば、目盛りに、RAND関数を使ってみると、ランダムで増加することができます。

数式を、
=SEQUENCE(4,5,1,RANDBETWEEN(1,10))
と変更したら、このようになりました。

RANDBETWEEN関数をつかうと、ランダムで、最小の1から最大の10の間で数値を算出することができます。

RANDBETWEEN関数をつかうことで、目盛りを固定させないで、増加させることができます。

7ずつ増加していますが、F9キーを押して、更新すると、

今度は、RANDBETWEEN関数が10と算出したので、目盛りが10として算出しました。

RANDBETWEEN関数と組み合わせて使うというアイディアで、このような方法ができることがわかりました。

今回、Office365のInsiderで追加された、新しい関数は色々登場しましたので、確認してみると面白ですし、現場でつかえるものもあるかもしれませんね。