Excel。列方向に、AからZを手早く入力したい。【A to Z】

Excel。列方向に、AからZを手早く入力したい。

＜CHAR+SEQUENCE関数＞

AからZまでを、列方向に入力したい。

当然、手入力すればいいんですが、それでも、面倒な作業です。

そこで、次のような数式をつくってみました。

=CHAR(SEQUENCE(1,26,65,1))

この数式だけで、AからZまでを入力することができました。

SEQUENCE関数はスピル機能に対応した関数なので、オートフィルをしなくても、数式を自動拡張してくれます。

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

CHAR関数は、文字コードの文字を表示する関数です。

65がAを意味します。

ネストしている、SEQUENCE関数をみていきましょう。

SEQUENCE関数は数列を返す関数です。

最初の引数は、行です。列方向に表示したいので、1行だけですから、1。

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

AからZなので、26文字です。

列数は26列なので、26。

3つ目の引数は、開始。

文字コード65がAですから、65からはじめるので、65。

最後の引数は、目盛り。

開始に+1していきたいので、1。

これだけで、AからZまでの連続した文字列を入力することができます。

Excel。丸数字の連番を手早くつくりたい【numbers】

Excel。丸数字の連番を手早くつくりたい

＜CHAR+CODE関数＞

通常の連番ではなくて、①などの丸数字で連番を設定したい場合、①と入力してオートフィルでコピーしても、連番を入力することはできません。

A2に①と入力してオートフィルでコピーしたところ、①がコピーされるだけです。

そこで、C2に①と入力しておいてから、C3に次の数式を設定します。

=CHAR(CODE(C2)+1)

あとは、オートフィルで数式をコピーすれば、丸数字の連番を入力することができました。

もっとスムーズ数式ならば、SEQUENCE関数を「+1」のところに使う方法もありますが、丸数字は⑳までしかないので、シンプルな数式でもいいように思えます。

では、

=CHAR(CODE(C2)+1)　の確認をしましょう。

CHAR関数は、文字コードに基づく、文字を表示する関数です。

文字には、それぞれ文字コードが割り振られています。

そのコード番号という数値から該当する文字を表示してくれるわけです。

要するに①という文字コードがわかれば、+1することで、②・③…と入力することができるわけです。

それでは、①の文字コードをどうやって調べたらいいのか。

そこで登場するのが、CODE関数です。

CODE関数は、CHAR関数とは逆で、文字から、その文字の文字コードを求めることができる関数です。C2に①が入力されていますので、

CODE(C2)

とすることで、①の文字コードを求めることができるというわけです。

オートフィルで数式をコピーしたら②と表示したいのでCODE(C2)に+1　を加えてあげることで、丸数字の連番をつくることができるという仕組みです。

なお、SEQUENCE関数をつかった数式は、

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

この場合、①を用意しないで、E2に、

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

と入力するだけです。

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

Excel。オートフィルの連続コピーでABCを手早く入力したい【alphabet】

Excel。オートフィルの連続コピーでABCを手早く入力したい

＜CHAR＋CODE＋ROW関数＞

AからZまでを手早く入力するには、オートフィルだけでは連続コピーすることはできません。

数値ならばいいのですが…

そこで、次のような数式を設定するだけで、オートフィルで数式をコピーするだけで、連続コピーすることができます。

A2に設定した数式は、

=CHAR(CODE("A")+ROW()-2)

です。

この数式を必要に応じて、オートフィルで数式をコピーすると、A～Zまでの連続したアルファベットを入力することができます。

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

まず、CHAR関数は、数値を該当する文字に変更する関数です。

そこで、CODE(“A”)ですが、CODE関数は、文字を文字コードの数値に変換する関数です。

CODE(“A”)は、Aの文字コードを返す関数です。

なお、Aは65です。

よって、CHAR(65)とすれば、Aという文字を返してくれます。

ただ、=CHAR(CODE("A")　だけだと、オートフィルで数式をコピーしても、すべてAが表示されるだけで、BC…は表示してくれません。

Bの文字コードは、66で、Cの文字コードは67です。

つまり、前の文字に「+1」されています。

どうやって、「+1」すればいいのでしょうか。

下方向に、オートフィルで数式をコピーする場合には、行番号を算出するROW関数が使えないかを考えるといいです。

A2に数式を設定します。

A2は2行目なので、ROW関数をつかえば、2と返してくれます。

A2は「A」を表示したいので、65+0にしたいわけです。

A3は「B」を表示したいので、65+1にしたいわけです。

ということで、

=CHAR(CODE("A")+ROW()-2)

とROW関数の値から「-2」することで、Aを基準としてプラスする値をつくることができます。

このように、Aからのアルファベットを連続コピーで作る場合には、

CHAR関数

CODE関数

ROW関数

を組み合わせることで、対応することができます。

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい【label】

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい

＜TEXTJOIN＋CHAR関数＞

住所録から、ラベルをつくりたいと考えました。

複数のセルを一つのセルに結合しますが、結合ごとに改行をいれたいわけです。

CONCAT関数だと、改行するたびに、改行を意味する、CHAR(10)を設定しなければなりません。

そこで、TEXTJOIN関数をつかってみることにします。

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

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

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

これで、手早くセルを結合できました。

また結合の間位に改行をいれることもできました。

なお、結合だけの表示になっている場合には、ホームタブにある、「折り返して全体を表示する」をクリックすることで、改行されていることが確認できます。

では、設定した数式を確認します。

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

TEXTJOIN関数は、セル結合する関数です。

最初の引数は、「区切り文字」です。

結合した時に区切り文字を設定することができます。

今回は、改行したいわけです。改行の文字コードは、CHAR関数をつかって、CHAR(10)とすることで、改行の文字コードをいれることができます。

2つ目の引数は、「空のセルは無視」。

空のセルを無視するならば、TRUEを設定します。

3つ目以降の引数は、結合するセル範囲です。

今回は、氏名の後に「 様」を表示したいので、テキスト1に「A2:B2」。

テキスト2に、「C2&" 様"」と設定しました。

Excel。丸付き数値で順位を手早く表示するには、どうしたらいいの。【numbers】

Excel。丸付き数値で順位を手早く表示するには、どうしたらいいの。

＜CODE関数・CHAR+RANK.EQ関数＞

RANK.EQ関数をつかえば、手早く、順位を算出することができます。

ただし、表示される順位は、普通の数値です。

この順位を丸付き数値で表示するには、どのようにしたら、手早く行うことができるのでしょうか。

C列にはReadingの得点が入力されています。

D列には、C列の数値に基づいた順位を丸付き数値で表示したいわけです。

どのようにしたら、丸付き数値にすることができるのかを考えてみると、CHAR関数をつかうことで、丸付き数値にすることができそうです。

CHAR関数は、数値を文字コートの文字に変換する関数です。

まずは、①の文字コードがわからないといけませんので、CODE関数をつかって、文字コードを算出しておきます。

=CODE(A12)

これで、①は、11553であることがわかりましたので、CHAR関数とRANK.EQ関数をつかってD2に数式を設定していきましょう。

=CHAR(RANK.EQ(C2,$C$2:$C$10,0)+11552)

これで、丸付き数値で順位を表示することができました。

RANK.EQ関数の算出結果に、「+11552」をしているのは、順位が1を①で表示したいわけです。

丸付き数値の①の文字コードは11553であることがわかっていますので、1+11552にすれば、CHAR関数で丸付き数値に変更することができます。

なお、丸付き数値は⑳までしか用意されていませんので、それ以上の順位が必要な場合には、あまり適していないように思えます。

Excel。ABCを繰り返すデータを手早く入力するにはどうしたらいいの。【alphabet】

Excel。ABCを繰り返すデータを手早く入力するにはどうしたらいいの。

＜CHAR＋MOD＋ROW関数＞

条件はなく、単純に上位から、ABCという3クラスに割り振りたい。

だけど、データが多くて、コピーなどで対応するにも面倒です。

そこで、関数をつかって、ABCと繰り返すデータを作りたい場合、どのような数式を設定したらいいのでしょうか。

次の表をつかって、数式をつくっていくことにします。

 

D2には、

=CHAR(MOD(ROW()+1,3)+65)

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

オートフィルで数式をコピーしたら、ABCを繰り返して表示します。

では、数式について説明します。

最初のCHAR関数は後回しにして、先にMOD関数とROW関数で何をしているのかから確認していきます。

MOD(ROW()+1,3

ROW関数は、行番号を算出する関数です。

引数の指定をしないと、アクティブの行番号を算出します。

D2なので、「2」を算出します。

その値に「+1」しますので、「3」と算出されます。

MOD関数は、除算した余りを算出する関数です。

ROW関数で算出した結果の「3」をMOD関数の2番目の引数が3になっているので、3で除算した余りは、「0」。

この「0」に65を足します。

「65」はどこからきたのかというと、「A」の文字コードが65だからです。

CHAR関数は、数値をその数値が割り振られている文字に変換する関数です。

CHAR関数をつかうことで、65はAと変換してくれるというわけです。

「65」を算出したかったので、MOD(ROW()+1,3　で、「0」を算出させるような数式をつくったというわけです。

オートフィルで数式をコピーすれば、余りは、「0と1と2」の3種類をつくることができるというわけです。

そうすることで、ABCを繰り返したデータを手早く設定できます。

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。複数セルの内容ごとに改行して、一つのセルにまとめたい。【put together】

Excel。複数セルの内容ごとに改行して、一つのセルにまとめたい。

＜TEXTJOIN+CHAR関数＞

複数のセルに入力されているデータを、次のように、一つのセルにまとめたいのですが、セルごとの区切りがわかるように、改行してまとめたいわけです。

今回のように、結合する件数が少なければ、CONCAT関数をつかう、あるいは、「&（アンパサンド）」で、文字結合する方法があります。

ただ、問題になってくるのが、単純に結合するのではなくて、セルとセルの間には、改行した状態にしたいわけです。

また、件数が多い場合は、数式を作るのも面倒になってきます。

では、A5には、どのような数式を設定したのかというと、

=TEXTJOIN(CHAR(10),TRUE,B1:D1)

という数式です。

数式を説明していきます。

最初のTEXTJOIN関数は、文字結合を行うことができる関数です。

このTEXTJOIN関数の引数がCONCAT関数や「&（アンパサンド）」よりも効率がいいことがわかります。

最初の引数は、区切り文字です。

改行で区切りたいわけですね。

改行は、CHAR関数はJISコードで割り振られた文字コードを設定することができます。改行は、10番なので、「CHAR(10)」とします。

2つ目の引数は、空白があったらどうするのか。

範囲内に空白があった場合は、無視したいので、「TRUE」を設定します。

最後の引数は、結合したい文字の範囲です。

よって「B1:D1」

これで数式としては完成ですが、このあと、ちょっとした処理をしないといけません。

実行すると、文字が結合されただけのようにしかみえません。

そこで、「折り返して全体を表示する」をクリックすると、改行で区切られていることがわかります。

なお、行幅が広がらない時は、広げてあげる必要があります。

今回紹介したTEXTJOIN関数は、新しい関数です。

新しい関数を色々使ってみると、今までよりも、簡単でわかりやすい数式を作れるかもしれませんので、試してみるといいかもしれませんね。

Excel。RANK.EQ関数で算出した数値を丸付き数字にしたいけど、どうしたらいい【circled numbers】

Excel。RANK.EQ関数で算出した数値を丸付き数字にしたいけど、どうしたらいい

＜CHAR＋RANK.EQ関数＞

順位を算出するのには、RANK.EQ関数をつかうことで、手早く算出することができます。

ただ、資料上、通常の数値ではなく、「①」のような丸付き数値で表示したい時には、どのようにしたら、いいのでしょうか。

次の資料を使って、丸付き数字で順位を算出してみました。

C2の数式は、

=CHAR(RANK.EQ(B2,$B$2:$B$11,0)+11552)

設定したら、オートフィルで数式をコピーしています。

丸付き数字で、順位が算出していることが確認できます。

考え方として、順位を算出するわけですから、「RANK.EQ関数」をつかうことはわかります。

あとは、算出される数値を、丸付き数字にするには、どのようにしたらいいのかを考えるわけです。

表示形式では、数値を丸付き数字に変更することはできません。

そこで、丸付き数値の文字コードの数値にすることで、「CHAR関数」をつかって、丸付き数値に変更することができます。

丸付き数値の「①」の文字コードは、「11553」なので、RANK.EQ関数で算出して値に、マイナス1した、11552を足した数値を、CHAR関数をつかうことで、変更することができます。

なお、文字コードを知るためには、CODE関数をつかうことで、確認することができます。

また、丸付き数値ですが、20までしかありませんので、それ以上必要な場合は、対応できませんので、注意が必要です。

Excel。CHAR関数は、文字コードを文字に変換する関数です。【CHAR】

Excel。CHAR関数は、文字コードを文字に変換する関数です。

＜関数辞典：CHAR関数＞

CHAR関数

読み方： キャラクター　　

分類： 文字列操作 

CHAR(数値)

文字コードを文字に変換します。

Excel。CHAR関数は、文字コードの文字を表示する関数です。【function: CHAR】

Excel。CHAR関数は、文字コードの文字を表示する関数です。

＜CHAR関数＞

指定した文字コードに対応する文字を表示してくれるのが、CHAR関数です。

CHAR関数だけで使われることは少ないかもしれませんね。

というのも「A」を「65」と覚えている人は少ないでしょうから、まずは、文字コードをCODE関数で調べて、その数値をつかって、Excel VBAなどでつかったりするわけです。

CHAR関数の読み方は「キャラクター」です。

所属は、「文字列操作」です。

CHAR関数の引数も確認しておきましょう。

CHAR(数値)

引数の数値は文字コードを意味しています。

なお、半角英数はASCIIコードを使用しています。

日本語などの全角文字はJISコードに対応してます。

ただ、この文字コードの割り振りが、ちょっと厄介なんですね。

B2には、

=CHAR(A2)

という数式が設定してあります。

それをオートフィルで数式をコピーしています。

「A B C D…」の文字コードは連番で割り振られているので、Excel VBAなどでは「+1」するようにしてあげれば、A～Zを表示すること自体、簡単に出来るのですが、「ひらがな」などは、「あいうえお」ではなくて、途中に、「ぃ」など、いわゆる半音が設定されているため、単純に「+1」しても対応してくれません。

促音でつかう「っ」や拗音でつかう「ゃ」などをまとめて、割り振ってくれればよかったのですが…。

あとは、UTFコード。すなわち、UNICODEは、JISコードと異なっていますので、注意が必要です。

 

「あ」はJISコードでは、9250ですが、UNICODEでは、12354です。

ちなみに、UNICODEに対応した、UNICHAR関数とUNICODE関数というのもあります。

Excel。ファイル添付時用に強固なパスワードを使って作りたいけどどうしたらいい？【Strong password】

Excel。ファイル添付時用に強固なパスワードを使って作りたいけどどうしたらいい？

＜RANDBETWEEN関数・IF+OR+AND+CHAR関数・CONCAT関数＞

ファイルを添付してメールなどで送信する場合、パスワードを付けて送信することが多々あります。

その場合のパスワードを同じにするのも、ちょっとどうかと思いますし、かといって、「Squt!kYmx9」のようなパスワードを、その都度、自分で考えるというのも、大変です。

そこで、Excelをつかって、パスワードを生成するシートをつくっていきます。

Excel VBAでマクロをつくってもいいのですが、作ることまでもなく、関数で対応できます。

考え方として、適当な文字を10個組み合わせたいわけですね。

なお、今回は重複OKとします。

直接ランダムな文字を表示させる方法はExcelにはありません。

まずは、使えそうな関数を考えみることにします。

文字は、文字コードという数値をもっていますから、数値から文字に変換する関数が、CHAR関数です。

その数値をランダムで表示することができるのが、RANDBETWEEN関数です。

この2つの関数があれば、どうにかなりそうですね。

ただし、注意しないといけないのが、文字が文字コードに綺麗に割り振られていない点です。

文字コードの48が「0」で57が「9」

文字コードの65が「A」で90が「Z」

文字コードの97が「a」で122が「z」

というように割り振られています。

そのため、ランダムの数値を48～122という単純な条件で算出するだけではダメということになるわけです。

58～64を除くなど、除外する必要があるわけです。

それを踏まえたうえで、次のようなランダムパスワードを作成するためのシートを作りました。

E1のパスワードは、4行目に算出したランダムな文字を結合させています。

E1に設定した数式は、

=CONCAT(A4:J4)

CONCAT関数は、文字結合することが出来る関数です。

CONCATENATE関数だと、範囲選択では文字結合することができません。

セルごとに「,（カンマ）」で区切る必要があるので、新しく登場したCONCAT関数を使うほうが便利です。

A3には、ランダムで数値を算出する関数。RANDBETWEEN関数をつかって、48～122の間の乱数を算出しています。

=RANDBETWEEN(48,122)

本当ならば、文字コードがない数値を算出させないようにしたいのですが、一筋縄ではいきませんので、単純に48～122の間の数値を表示させるようにしています。

逆にA4の数式は、文字コードに該当するようにしてあげる必要があります。

A4の数式は、

=IF(OR(AND(A3>=48,A3<=57),AND(A3>=65,A3<=90),AND(A3>=97,A3<=122)),CHAR(A3),"!")

数式が長くなっていますが、

文字コードの48が「0」で57が「9」

文字コードの65が「A」で90が「Z」

文字コードの97が「a」で122が「z」

に対応させるために、OR関数とAND関数をミックスしてつかっています。別にAND関数のみでも大丈夫です。

文字コード以外の数値だったら、「!」（感嘆符：エクスクラメーション）を表示するようにしております。

あとは、A3:A4を範囲選択して、まとめて、オートフィルで数式をコピーしていきます。

パスワードの文字数が10文字なので、それにあわせて数式をコピーします。

これで、ランダムパスワードを作成することができます。

あとは、運用上の注意で、RANDBETWEEN関数は、セルに文字を入力するなど、アクションをしてしまうと、値が変わってしまうので、ランダムパスワードを作成したら、すぐに、値のコピーをつかって、管理する必要があります。

Excel。セル内で改行。任意の場所で改行する方法はいろいろあります。【new line】

Excel。セル内で改行。任意の場所で改行する方法はいろいろあります。

＜CHAR関数＞

Excelには、セル内での改行方法は色々あります。

知っているいないで、資料を効率よく、また綺麗につくることが難しい場合もありますので、今回は、三種類の改行のやり方をご紹介していきます。

最初は、お馴染みの「セル内改行」のAlt+Enterから確認していきます。

次のように、セルの列幅から溢れてしまったときに、好きな場所で改行したい時に行うのが、「Alt + Enter」というショートカットで改行することができます。

「都」と「新」の間にカーソルを表示して、「Alt + Enter」を実行してみます。

このように、セル内で改行することができました。

なんで、Alt + Enterをするのかというと、ホームタブの「折り返して全体を表示する」をつかうと、列幅に合わせて、全体を表示してしまうので、好きな所で改行することができません。

なので、Alt + Enterでの改行が必要になります。

続いては、数式（関数）をつかった、改行を紹介していきます。

先程のAlt + Enterは、一つのセルだけならば、いいのですが、実施したいセルが複数ある場合は、何度も繰り返す必要があるので、面倒な作業となってしまいます。

例えば、次のような表。

A列の都道府県とB列の市区町村を結合したのが、D列。

ただし、D列は長くなるので、任意で改行したいというケースです。

D列の数式を確認してみます。

=A2&CHAR(10)&B2

この数式は、「&（アンパサンド）」をつかった文字結合です。

CONCAT関数をつかった、

=CONCAT(A3,CHAR(10),B3)

という数式でもOKです。

ポイントは、CHAR(10)という、CHAR関数。

この関数は、該当する番号の文字を算出してくれます。

「10」というのは、改行コードの番号なので、CHAR(10)とすることで、文字結合の途中で改行することができるというわけです。

数式なので、オートフィルで数式をコピーすれば、先程の、Alt+Enterよりも早く処理をすることができます。

なお注意が必要な点があります。

数式を確定した後、算出結果は、自動的に任意の箇所で改行されませんので、ホームタブの「折り返して全体を表示する」をクリックする必要があります。

その後場合よっては、列幅を調整する必要があります。

最後の改行のケースは、表示形式の改行です。

次のような日付と曜日が表示されいる表。

B1;D1には、表示形式のユーザー定義を、

m"月"d"日"(aaa)

と設定することで、日付と曜日が表示されるようにしています。

次のように、日付と曜日を2行で表示するようにすれば、列幅をコンパクトにすることができます。

このケースでは、先程の、CHAR(10)という関数をつかって改行することができません。

なぜならば、セル内の表示方法を変えているだけなので、数式を使ってコントロールすることはできません。

また、一文字ずつではありませんので、Alt+Enterをつかっての改行も行うことができません。

表示形式で、任意の箇所に改行をしたい時は、「Ctrl + J」というショートカットキーを入力します。Ctrl + Jは、表示形式内での改行を行うことができます。

「(」の前に、Ctrl + J と入力して確定しましょう。

実行後、見た目に変化はありません。

ホームタブの「折り返して全体を表示」をクリックして、さらに、列幅を調整することで改行した表示にすることができます。

Excelは、改行の方法がアレコレ変わりますので、機会があるときに、覚えていくといいかもしれませんね。

Excel。アルファベット評価の平均を算出するには、どうしたらいいのでしょうか？【Average alphabetic rating】

Excel。アルファベット評価の平均を算出するには、どうしたらいいのでしょうか？

＜CODE関数・CHAR関数・AVERAGE関数＞

英語のテストのように、90点のような数値だと、平均値を求めることは簡単ですが、次の表にある、「理解力＝B」のようにアルファベット評価の場合、どうやってアルファベット評価の平均を算出したらいいのでしょうか？

 

この表は、A～Eの５段階評価です。

Aが一番よい評価で、Eが一番悪い評価という意味の表です。

考え方として、アルファベットごとに件数を算出して、Aが多いからAなど、見た眼で判断したら、曖昧になってしまいます。

今回以上の膨大なデータの場合、目視で判断なんか到底できません。

また、Aを1、Bを2など独自に設定した数値に置換して、その平均値を算出すればいいかもしれませんが、A～Eのアルファベットを置換するなどの作業が面倒です。

しかも、この独自に設定した数値を使った場合、最終的にアルファベットに戻さなければなりません。

その作業も置換を使うにしろ、VLOOKUP関数を使うにしろ、なかなか面倒です。

【文字を数値にする】

平均を算出することでお馴染みの、AVERAGE関数は、数値の平均を算出することはできても、文字を平均化することはできません。

逆に言えば、文字を数値化できれば算出することはできるわけです。

そこで、文字を数値にする、「CODE関数」というのがあります。

このCODE関数は、文字に割り振られている数値を算出してくれる関数です。

先程の評価表のとなりに、CODE関数をつかった表を算出してみます。

 

J2の数式は、

=CODE(B2)

この数式をオートフィルでコピーすると、A～Eまでのアルファベットに割り振られている数値が算出されます。

ちないに、Aは65です。

仮に、傾聴力から理解力までの項目がすべてAだったら、すべての項目が65になりますから、平均は65というように、平均値を算出することができます。

o2の数式は、

=AVERAGE(J2:N2)

と平均値を算出しております。

ただ、これだと数値のままなので、アルファベットで表示されていません。

【数値を文字にする】

算出された平均結果をつかい、アルファベットに戻すためには、CHAR関数を使います。

 

G2の数式は、

=CHAR(O2)

としております。

CHAR関数は、小数点以下は無視された数値を使って、文字に変換してくれます。

これで、アルファベット評価の平均した評価を算出することができました。

このままでもいいのですが、ちょっと気になることがあります。

それは、G6

5件中、Eが4件で、Dが1件。平均結果がDというのは、イメージが乖離しています。

このような場合は、平均値を算出する数式をアレンジする必要があります。

O2の数式を次のようにアレンジしました。

=ROUND(AVERAGE(J2:N2),0)

ROUND関数をつかって、四捨五入させました。

その結果が次の表です。

 

G6は、平均評価がEに変わりました。

状況によって、切り上げ、切り捨てなど、アレンジすると状況にマッチしやすくなると思います。

本来ならば、中央値や、標準偏差など様々な数値を算出する必要があるかもしれませんが、簡易版とアルファベット評価の平均を算出するアイディアをご紹介しました。

Excel。A～E評価で一番いい評価を抽出するのは意外と大変です。【Alphabet extraction】

Excel。A～E評価で一番いい評価を抽出するのは意外と大変です。

＜CHAR+MIN+CODE関数＞

下記の表があります。

試験1回目から5回目まで行って、それぞれの評価に基づいたA～Eまでの文字が入力されていて、G列には、担当者ごとの評価の中で一番いい評価を抽出しているという表です。

一件目の試験評価が、E・B・D・D・A・Aなので、A評価がG列に抽出されているわけです。

簡単そうに思えますが、意外とG列の一番いい評価を抽出するのが大変なので、確認をしていくことにしましょう。

【MIN関数では対応できない】

最初に考えるとしたら、A～EなのでAとEを比べる、例えば昇順にすれば、Aが一番最初にありますから、MIN関数を使えばいいように思えます。

ではG2にMIN関数で算出してみましょう。

算出された結果は、なんと「0（ゼロ）」。想像していたように算出してくれませんでした。

なぜ、こうなってしまったのかというと、MIN関数。

この関数は、対象が数値でないといけない関数なのです。

ただ、MIN関数を使うというアイディアは悪くないのです。

問題は、数値でないとダメということ。

【CODE関数で文字を文字コードに変換する】

コンピューターの文字というのは、アスキーコードとかUTF8など文字コードを持っていているという特徴があります。

幸い文字コードは数値です。

その文字コードを使えば、A～Eまでのアルファベットであっても数値化することができます。

文字を文字コードに変換することができる関数があります。

それが『CODE関数』
H2にF2の「A」の文字コードが、いくつなのか確認してみましょう。

H2の数式は、
=CODE(F2)
算出された結果は、65です。
Eは69という文字コードをもっています。

このCODE関数を使えばどうにかなりそうですね。

それでは、G2の数式を次のように設定してみましょう。

=MIN(CODE(B2:F2))
MIN関数とCODE関数をネストにしています。

CODE関数の引数は、範囲なので、B2:F2という範囲でも設定することが可能です。

しかし算出された結果をみると…

65と算出されてしまいました。

たしかに、１件目のデータは、Aが算出してほしいので、そのAの文字コードである65が算出されたところまではいいのですが、65という数値ではなく、「A」という文字を表示してほしいわけです。

原因は、CODE関数をつかったことで、文字が数値になってしまったからです。

なので、今度は、文字コードになった数値を文字に変換する「CHAR関数」を使う必要があります。

【CHAR関数で文字にもどす】

G2の数式をさらにアレンジします。

=CHAR(MIN(CODE(B2:F2)))

算出結果を確認して、オートフィルで数式をコピーしてみましょう。

これで、アルファベットの評価を算出することができました。

文字だけでコントロールできない場合は、文字コードとつかうという方法もありそうですね。

Excel関数辞典 VOL.09。CEILING関数～CHAR関数

Excel関数辞典　VOL.09。CEILING関数～CHAR関数

＜Excel関数＞

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

CEILING関数関係は、「発注」などで威力を発揮する関数ですね。
CHAR関数も良く使いますね。

CEILING関数
シーリング
指定した数値の倍数に切り上げる
CEILING(数値,基準値)

CEILING.MATH関数
シーリング・マス
指定した方法で倍数に切り上げる
CEILING.MATH(数値[,基準値][,モード])
※基準値を省略すると、1で指定されます
※モードに0（ゼロ）以外を指定すると0から離れた整数に切り上げされます。

CEILING.PRECISE関数
シーリング・プリサイズ
指定した数値の倍数に切り上げる
CEILING.PRECISE(数値[,基準値])
※CEILING.PRECISE関数は、基準値を省略することができます。

CELL関数
セル
セルの書式・位置・内容に関する情報を得る
CELL(検査の種類[,対象範囲])

CHAR関数
キャラクター
文字コードを文字に変換する
CHAR(数値)

YandSシステムズのExcel関数一覧表
https://sites.google.com/view/yandsssystems/function?authuser=0

Excel。アルファベット評価から最高ランクがどのアルファベットなのかは、MIN関数だけでは対応できません【RANK】

Excel。アルファベット評価から最高ランクがどのアルファベットなのかは、MIN関数だけでは対応できません

＜CHAR+MIN+CODE関数＞

次のような評価表があります。

1回目から5回目までで、それぞれA～Eランクで評価したものなのですが、
1回目から5回目の中で、
一番成績のいいアルファベット評価をH列に求めたいわけです。

例えば、1件目は「C/D/C/A/A」なので、
最高ランクは”A”と求められるといいわけです。

当然、データ量が少なければ、
目視によるパワープレイで処理してもいいのですが、
データが大量になると、大変になりますので、
工夫が必要になるわけです。

そこで、イメージとして、A～Eなので、昇順でならぶから、
最小値を求めるMIN関数で簡単に算出できる感じがしますよね。

では、実際どうなのか、確認してみましょう。

【MIN関数では算出できない！】

H2をクリックして、
=MIN(C2:G2)
という数式を設定しましょう。

0（ゼロ）と算出されてしまいました。

なんで、”A”と算出してくれなかったのか？

それは、MIN関数は、
『数値』の最小値を求めることができる関数だからです。

アルファベットは、数値ではありませんから、
算出してくれなかったわけです。

では、どのようにしたらいいのでしょうか？

【CHAR+MIN+CODE関数と配列関数で一発算出】

一発で算出する方法を、まずはご紹介します。

配列関数を使いますが、H2に次の数式を、手入力します。

配列関数なので、手入力の方があとあと楽です。

=CHAR(MIN(CODE(C2:G2)))
と入力したら、配列関数にしますので、
ctrl + shift + Enterキーを押します。

数式に、”{ }”が前後につけば、配列関数になりました。

{=CHAR(MIN(CODE(C2:G2)))}

オートフィルを使って数式をコピーしましょう。

これで、最高ランクを求めることができましたが、
配列関数はちょっと、ややこしいので、
改めて、順を追って説明していきます。

MIN関数を使いたいけど、文字だから使えないわけです。

そこで、文字には、それぞれ文字コードという番号が振られています。

文字を文字コードに変換することができる関数が、CODE関数です。

では、この表の下に、文字コードに変換した表を作ってみましょう。

C11には、
=CODE(C2)
という数式が設定されていて、
オートフィルを使って数式をコピーしました。

すると、Aは65で、
Eは69という文字コードが振られていることがわかりました。

これで、数値になりましたので、MIN関数が使えるわけです。

H11にMIN関数を使って算出してみましょう。

H11の数式は、
=MIN(C11:G11)

あとは、この文字コードをアルファベット。

すなわち文字に戻してあげればいいわけですね。

文字コードを文字に変換する関数は、CHAR関数ですね。
それでは、H11の数式をCHAR関数でネストしてみましょう。

=CHAR(MIN(C11:G11))

アルファベットに戻りましたね。
先程の配列関数は、この手順を一発で処理したわけです。

このように、アルファベットの評価を並び替えるのは簡単ですが、
今回のようなケースでは一筋縄では処理できませんので、
様々な関数を知っているといいのかもしれませんね。

Excel。アルファベットは連続コピーできないんですか！って声があったので。【Alphabet】

Excel。アルファベットは連続コピーできないんですか！って声があったので。

＜CHAR+CODE関数&ROMAN関数＞

Excelのオートフィルというテクニックは、連番を振るときとか、
4月・5月と連続して入力したいときなどに、
オートフィルハンドルをドラッグするだけで入力出来ますので、
非常に便利なのですが、
「アルファベットは出来ないんですね」
とか
「①などの○付き数字はWordの段落番号のように入力できないんですか？」
なんてことを聞くので、改めてご紹介。

【アルファベットを連続コピー】

大文字のアルファベットのA～Zをオートフィル機能で連続コピーする方法ですが、
根本の問題として、文字に数値を足すことは出来ません。

しかしながら、文字には、それぞれ、
「文字コード」という番号が振られていますので、
その文字コードを使うことで、連続コピーをすることが可能です。

では、A1にAと入力して、
A2をクリックしてCODE関数ダイアログボックスを表示しましょう。

CODE関数は、文字列の先頭の文字コードを算出する関数です。

文字列にはA1を設定して、OKボタンをクリックすると、
65という数値が算出されました。

この65がアルファベットの大文字のAに振られている番号ということがわかりました。
当然、BはAの次ですから＋1すれば、いいわけです。

=CODE(A1)+1

と数式を修正します。

この数値を今度は、文字に変換する、CHAR関数を使っていきます。

では、改めてですが、A2の数式を削除して、
CHAR関数ダイアログボックスを表示しましょう。

数値には、CODE(A1)+1　と入力してOKボタンをクリックしましょう。

あとは、A2の数式をオートフィルで連続コピーすれば完成ですね。

A2の数式は、
=CHAR(CODE(A1)+1)
と簡単な数式ではありますが、自分で入力したほうが早い気もしますが…

この数式は、他の連続コピーでも使えます。
例えば、①という○付き数字。先ほどのアルファベットと同じ方法で出来ます。

ただし、○付き数値は、⑳までしかありませんので使用する時には⑳以内に収まるケースで無いといけませんので、注意が必要ですね。

【ローマ数字を連続コピー】

ⅠやⅡといったローマ数字も連続コピーする方法は、先ほどの数式
=CHAR(CODE(A1)+1)
で求める事は出来るのですが、Ⅹまでしかありません。

では、11以降は出来ないのでしょうか？

実は、ローマ数字は、ROMAN関数を使って数値を変換する方法があります。

最初に、1～の連番を作ります。
3999と4000がある意味はあとで、わかりますので、
E1をクリックして手入力でROMAN関数を入力していきます。

手入力した意味は、書式を設定することが出来るからです。
今回は、0-古典という通常のものを使っていきます。

E1の数式は、
=ROMAN(D1,0)

この数式はE22までオートフィルでコピーしてみましょう。

先ほどのⅩよりも大きい数値もローマ数字で連続コピーをすることが出来ました。

ただし、3999までは表示してくれますが、
4000は桁あふれをするためか、エラーになってしまいますので、
3999までが可能なようです。

なお、書式を簡略化してもエラーになります。

このように、簡単そうなものでも、
意外とテクニックが必要な場合もありますので、
Excelのテクニックの種類を日々増やしていくといいかもしれませんね。