今回は、MEDIAN関数～MINIFS関数までをご紹介しております。
MEDIAN関数
メディアン
中央値を算出します。
MEDIAN(数値1,[数値2]…)
MID関数
ミッド
文字列の任意の位置から文字を取り出す
MID(文字列,開始位置,文字数)
MIDB関数
ミッドビー
文字列の任意の位置から指定バイト数の文字を返す
MIDB(文字列,開始位置,バイト数)
MIN関数
ミニマム
最小値を算出します。
MIN(数値1,[数値2]…)
MINA関数
ミニマムエー
数値・文字列・論理値を含む最小値を算出します。
MINA(値1,[値2]…)
MINIFS関数
ミニマムイフズ（ミニマムイフエス）
条件により指定した範囲内の最小値を算出
MINIFS(最小範囲,条件範囲1,条件1,…)
Wordの校閲タブにある「比較」というのがあって、文書と文書を比較して、異なっているところ箇所を確認できるという機能があるのですが、Excelに、「比較」のような機能はありません。
入力した数値。
あるいは、セルに入力した文字が原版と比べて、修正があったかどうかを判断するには、どのようにしたらいいのでしょうか？
当然、セルの内容を比較したいわけですから、「IF関数をつかう」というのがオーソドックスな方法だと思いますが、Excelには、IF関数をつかわなくても、簡単に比較する関数が用意されています。
最初に数値の場合をみていきましょう。
C1に設定した数式は、
=DELTA(A1,B1)
A1とB1を比較して、合致していれば、「1」を算出します。TRUEということですね。
合致していなければ「0（ゼロ）」を算出します。FALSEということですね。
あとは、算出した値が0だったら、塗りつぶすとか、0の件数を算出するなどすることで、異なっている場所に対してアクションをとることも容易になるかと思われます。
ただ、このDELTA関数。
ちょっと残念なところがありまして、それは、「数値」にしか対応していない。
つまり、文字には対応してくれないわけです。
なので、文字でも、数値でも対応してくれるのが「EXACT関数」です。
F列のDELTA関数をつかうと、数字ではないので、#VALUE!というエラーが表示されてしまいました。
よって、このような文字では使うことができません。
E列はEXACT関数をつかって算出しています。
E2の数式は、
=EXACT(A2,C2)
DELTA関数同様に、わかりやすい関数ですね。
合致していれば、TRUEを算出してくれます。
4行目のように、合致していないと、FALSEを算出してくれます。
文字列は数値と異なり、異なっているかどうかを、目視で確認するのは、厳しいように感じます。
用意した文字列も、A4は「表現できます。」となっているところをC4では「表現できる。」と変えてみました。
EXACT関数自体の作成は、とても簡単ですが、ちょっと残念なのは、算出結果が、「TRUE」と「FALSE」で算出されてしまう点ですね。
そこで、数式を次のように変えてみます。
というように、「*1」。
1を乗算させると、TRUEは「1」。
FALSEは「0」に置き換わりますので、わかりやすくなります。
ExcelでTRUE/FALSEのときは、「*1」と覚えておくと、ちょっと便利です。
最後に、EXACT関数は、シート間でも使うことができます。
Facebookページで【書いてみた】ワンポイントです。
6月21日
Exceｌ。ファイルの保存で、ブックの名前に使えない文字は、￥ / : * ? " < > | [ ] です。
これは、パソコンのルールですので、そちらと共通ですね。
6月22日
Excel。Excel2010から、バックステージビューという印刷設定と印刷プレビューが合体した画面になります。
旧バージョンから使用していた人はだいたい、ビックリしましたね。
6月23日
Excel。オートフィル機能は、Excelの基本操作です。
どんどん使っていきたいですね。
6月24日
Excel。オートフィル機能に追加したい場合は、ファイルタブ→オプション→詳細設定→ユーザー設定リストの編集ですね。
6月25日
Excel。A1+A2という場合でも、+で計算するのではなく、SUM関数を使用する方がいいですね。
もしA1やA2に"-"などの文字が入ってきてもSUM関数ならば計算してくれますね。
6月26日
Excel。オートフィル機能で、下方向にドラッグするのは、大変なので、オートフィルハンドルをダブルクリックすると、一番下まで処理してくれますね。
6月27日
Excel。合計を一瞬で求める方法。
ご存知ですか？合計する数値が入力されているセルと、合計を表示するセルを選択して、オートSUMボタンをクリックすると、あら不思議。計算してくれます。
やりたいことは単純でも、Excel VBAにするほどじゃないし、けど、コピー&ペーストじゃ面倒という作業は結構あります。
例えば、次のような縦一列の名簿
そこで、何か効率的な方法はないかということ、このような場合には、「INDEX関数」をつかうことで、一発解決します。
ただ、準備が必要になります。
E10:G13に、配置したい順番に、番号を振っていきます。
準備はこれだけです。
E2にINDEX関数をつかった数式を作っていきます。
INDEX関数は、手入力するほうが楽な関数なので、手入力で設定することをおススメします。
=INDEX($C$2:$C$13,E10)
あとは、3列編成になるように、オートフィルで数式をコピーしていきます。
INDEX関数は、2種類ありますが、今回は、配列タイプで算出しています。
行番号は、E10。
列番号は、氏名の1列だけなので、省略しました。
たった、これだけなんですね。ですが、時間短縮の効果抜群なのが、INDEX関数の特徴でもあります。
仕組みがわかれば、INDEX関数だけで、様々な編成した表にすることができます。
例えば、次のようなパターン
縦一列を、変則的な横二列編成の表で表示してみました。
上下方向のオートフィルで数式をコピーは、セル参照でも対応できるかもしれませんが、上下方向の表を横方向にするためには、オートフィルで数式をコピーというわけにはいきません。
しかし、INDEX関数をつかうことで、このような表に編成しなおすことも容易です。
ポイントは、行番号をどのようにしたらいいのかということです。
この表のように、1行目と4行目にある数値を行番号として使用することができれば、3列編成のときのように、配置するための別表を用意する必要はありません。
今回紹介したINDEX関数を知っていると、色々現場レベルでつかるかもしれませんので、機会がありましたら使ってみてはいかかでしょうか。
平均値を算出するならば、「AVERAGE関数」。
単一条件（条件が一つだけ）で平均値を算出したいとなれば、「AVERAGEIF関数」。
そして、複数条件で平均値を算出するならば「AVERAGEIFS関数」ですね。
複数なので、「S」がつくと覚えておくといいですね。もし、覚えるのが大変という人は、AVERAGEIF関数は、覚えなくても、AVERAGEIFS関数でも、単一条件も算出することができます。
AVERAGEIFS関数の基本情報を確認しておきます。
AVERAGEIFS関数の読み方は「アベレージイフエス/アベレージイフス」です。
カテゴリーとしては、「統計」に所属しています。
AVERAGEIFS関数の引数も確認しておきましょう。
AVERAGEIFS (平均対象範囲,条件範囲1,条件1,…)
ちなみに、条件の数ですが、127個まで追加することが出来ます。
それでは、動きを確認しておきましょう。
その数式が、
=AVERAGEIFS(C2:C11,A2:A11,">="&E1,B2:B11,E2)
AVERAGEIF関数と違うところは、引数の順番ですね。
最初の引数が、平均対象範囲なので、そこだけ注意すれば、問題はあまりないように思えます。
頻度の高い関数の一つですが、アイディアで色々使える関数でもあります。
関数初心者の人も早めに抑えておきたい関数ですね。
正数と負数を一緒に表示した横棒グラフを作ろうとすると、ちょっとしたアイディアが必要になってきます。
次のデータがあります。
あとは、見やすいようにアレンジすれば、いいといえばいいのですが、気になるのが、縦軸。
「0（ゼロ）」と交点になっているので、負数のデータと縦軸の項目名がかぶってしまい見づらくなっています。
そこで、見やすいように、次のような横棒グラフにするといいのではと考えました。
要するに、整数ならば左側に、負数ならば右側に縦軸の項目名を表示してみました。
ところが、この横棒グラフは、用意したデータのままでは作ることができません。
次のように修正します。
E列の右側とF列の左側を追加しました。
E2には、
=IF(D2<0,200,NA())
F2には、
=IF(D2>0,-200,NA())
という数式が設定しており、5行目までオートフィルで数式をコピーしてあります。
この数式を説明しておきます。
D2の差額が0より大きいか、小さいかを判定させます。
真の場合ならば、「200」とし、偽の場合なら「NA()」とするIF関数です。
「200」は、あとで調整することになると思いますが、縦軸の項目名をどのぐらい離すのかという数値です。
意味的に言うと、マージンとかセル内余白といったところでしょうか。
NA()は、「#N/A」というエラーを表示させる関数です。
それでは、A1:A5とD1:F5を範囲選択して、「積み上げ横棒グラフ」を作成します。
なお、今回は説明上、大きく表示したいので、グラフタイトルを削除し、少しグラフを大きくしております。
「縦（項目）軸」を選択して、DELキーで縦軸を削除します。
灰色のデータをクリックして、グラフのデザインタブの「グラフ要素の追加」からデータララベルの「その他のデータラベルオプション」をクリックします。
オレンジ色のデータも同じようにします。
青色のデータには、値を表示するようにします。
あとは、細かいところを修正していきます。
縦軸として使っている、「御徒町」などのデータの透明にしますので、図形の塗りつぶしを「なし」。
枠線も「なし」に変更します。
横軸のフォントサイズを大きくし、横軸の最小値と最大値を調整します。
Facebookページで【書いてみた】ワンポイントです。
6月13日
Excel。ショートカット。
Ctrlキー＋Shiftキー+←キーで、アクティブセルからデータの左端までを範囲選択できますね。
6月14日
Excel。ショートカット。
Ctrlキー＋Shiftキー+→キーで、アクティブセルからデータの右端までを範囲選択できますね。
6月15日
Excel。ショートカット。
Ctrlキー＋Shiftキー+↑キーで、アクティブセルからデータの上端までを範囲選択できますね。
6月16日
Excel。ショートカット。
Ctrlキー＋Shiftキー+↓キーで、アクティブセルからデータの下端までを範囲選択できますね。
6月17日
Excel。ショートカット。
Altキー＋F4キーで、Excelを終了することができますね。
6月18日
Excel。１枚のシートは、～Excel2003までは、列は256。
行は65536。
セルは、16777216個でした。
6月19日
Excel。１枚のシートは、Excel2007～だと、列は16384。
行は1048576。
セルは、17179869184個です。
171億を越えるセルって…凄すぎる。
ある商品の評価結果の資料があります。
これでは、Aが何件、Bが何件と数えたい場合、目視で確認するしか方法はないのでしょうか？
左から1文字目は、A。
2文字目はBとセルに文字をいれる数式をつくってから、数えるという考え方もありますし、あるいは、Excel VBAでマクロをつくるという方法もあるとは思いますが、LEN関数・SUBSTITUTE関数をつかうだけで、簡単に、セル内に同じ文字がいくつあるのか算出することができるのです。
LEN関数・SUBSTITUTE関数をつかった数式をつかうことで、次のように集計することができます。
どのような数式をつくるといいのかを確認していきましょう。
C2につくった数式は、
=LEN($B2)-LEN(SUBSTITUTE($B2,C$1,""))
この数式を、オートフィルで数式をコピーすれば、セル内の同じ文字がいくつあるのかを簡単に算出することができます。
この数式を説明しておきましょう。
LEN($B2)は、B2のセル内の文字数を算出しています。
LEN関数はセル内の文字を数える関数ですね。
セル内の文字数から、LEN(SUBSTITUTE($B2,C$1,""))を減算しています。
LEN(SUBSTITUTE($B2,C$1,""))を見ていきましょう。
最初のLEN関数は、文字を数える関数でした。
何を数えているのかというと、
SUBSTITUTE($B2,C$1,"")
SUBSTITUTE関数は、指定した文字を置換する関数です。
B2の中にある、C1＝「A」を、「””」＝空白に置換させています。
H2に、
=SUBSTITUTE(B2,"A","")
の結果を見ると、何をやっているのかがわかります。
この文字数を数えて、Aも含めた最初のLEN関数で算出したあたいから、減算すれば、「A」がいくつあったのかがわかるという仕組みです。
発想としては、どうしても「A」を残すように考えてしまいますが、Aだけを残すというのが、難しいので、逆転の発想で、算出させてみました。
今回のように、全体の数から減算するというのは、よくある方法ですので、覚えておくと、いざという時にいいのかもしれませんね。
なお、同じようなパターンとしては、「○」「×」で、「○」の数はいくつなのかということもできます。
2種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。
平均値を算出するならば、「AVERAGE関数」。
そして、単一条件（条件が一つだけ）で平均値を算出したいとなれば、「AVERAGEIF関数」の登場となるわけですね。
AVERAGEIF関数も、実務でお馴染みの関数になりました。
AVERAGEIF関数の基本情報を確認しておきましょう。
AVERAGEIF関数の読み方は「アベレージイフ」です。
カテゴリーとしては、「統計」に所属しています。
AVERAGEIF関数の引数も確認しておきましょう。
AVERAGEIF (範囲,条件,[平均対象範囲])
ポイントは、最初の引数は、2番目の引数の条件が含まれている範囲となっているのが、わかりにくいところですね。
範囲ではなくて、条件が含まれている範囲としてくれるといいのですが。
では、実際に次の表を使って確認しておきましょう。
F2の数式は、
=AVERAGEIF(A2:A11,"<2022/1/1",C2:C11)
2番目の引数の条件が、「”（ダブルコーテーション）」で囲われていることがポイントですね。
直接条件を設定する時は、イメージしやすいのですが、セル参照型で、比較演算子を使うときは、もう一つポイントがあります。
では、F5の数式を確認してみましょう。
=AVERAGEIF(A2:A11,"<"&E5,C2:C11)
比較演算子だけが、「”（ダブルコーテーション）」で囲っています。
そして、「&（アンパサンド）」でセル名と結合させて使用します。
AVERAGEIF関数以外にも、～IF関数や～IFS関数がありますが、比較演算子とセル参照を組み合わせる場合には、注意するポイントがありますので、注意したいところですね。
シートの保護されたデータを範囲選択して、別のシートにコピーして貼り付ける。
何も気にすることもなく出来る処理ですが、Excel VBAをつかってマクロを実行すると、トラブルやらメッセージが表示され、スマートに実行できません。
次の表は、シートの保護を設定しています。
A1からある表を、別シートにコピーするだけですが、頻度が多いので、Excel VBAをつくって対応していきます。
Sub シートの保護コピー ()
Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")
End Sub
プログラム文を説明します。
Range("a1").CurrentRegion.Copy は、A1から始まるCurrentRegion（連続した範囲）をコピーします。
さらに、
Destination:=Worksheets("COPY").Range("a1")
で、COPYというシートのA1に貼り付けます。Destinationは、貼付け先です。
要するに、「A1から連続する範囲をコピーして、シート名COPYのA1に貼り付ける」という意味です。
なんら、問題はありません。
「シートの保護」を設定しないで、実行してみると、問題なくコピーできることが確認できます。
ところが、シートの保護を設定しておくと、どうなるでしょうか？
よくみると、シートの保護が設定されていると実行できないとのこと。
シートの保護が設定されただけで、問題なく使えていたマクロが動かなくなるわけですね。
では、どのようにしたらいいのでしょうか？
プログラム文を次のように修正してみましょう。
Sub シートの保護コピー保護解除()
Worksheets("hogo").Unprotect
Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")
Worksheets("hogo").Protect
End Sub
コピーする行の前後に、一行追加しました。
Worksheets("hogo").Unprotect
今回元のデータがあるシート名を「hogo」としています。
そのシートに設定されている「シートの保護」を解除するのが、「Unprotectメソッド」です。
コピーした後に、シートの保護を解除しているので、
Worksheets("hogo").Protect
「Protectメソッド」でシートの保護を再設定しています。
それでは、マクロを実行してみましょう。
ところが、シートの保護には、「パスワード」設定ができるわけです。
パスワード付きシートの保護だとどうなるか、確認してみます。
パスワードは「123」としました。
パスワードを入力してOKボタンをクリックすれば、無事にコピーすることができますが、いちいち、パスワードを入力するのは面倒です。
そこで、次のように、プログラム文を加筆していきます。
Sub シート保護コピー保護解除()
Worksheets("hogo").Unprotect Password:="123"
Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")
Worksheets("hogo").Protect Password:="123"
End Sub
Unprotectメソッドと、Protectメソッドのあとに、Password:="123"というPasswordプロパティを追加することで、パスワード付きシートの保護に対応することができます。
実行して確認すると、コピーも出来ていますし、シートの保護は解除されることなく、しかもパスワードもきちんと設定されていることが確認できましたね。
Facebookページで【書いてみた】ワンポイントです。
6月7日
Excel。ショートカット。
Shiftキー＋→キーで、右側のセルを範囲選択することができます。
6月8日
Excel。ショートカット。
Shiftキー＋↑キーで、上側のセルを範囲選択することができます。
6月9日
Excel。ショートカット。
Shiftキー＋↓キーで、下側のセルを範囲選択することができます。
6月10日
Excel。ショートカット。
Ctrlキー＋←キーで、データの左端のセルにジャンプすることができます。
データが無い時は、シートが対象になりますね。
6月11日
Excel。ショートカット。
Ctrlキー＋→キーで、データの右端のセルにジャンプすることができます。
データが無い時は、シートが対象になりますね。
6月12日
Excel。ショートカット。
Ctrlキー＋↑キーで、データの上端のセルにジャンプすることができます。
データが無い時は、シートが対象になりますね。
6月13日
Excel。ショートカット。
Ctrlキー＋↓キーで、データの下端のセルにジャンプすることができます。
データが無い時は、シートが対象になりますね。
「平均」といえば、AVERAGE関数と思うかもしれませんが、平均には色々な種類の平均があります。
オートSUMボタンにある、お馴染みのAVERAGE関数は、平均を算出しますが、単なる平均ではなくて、「算術平均」とか「相加平均」といったりします。
Excelには、
上位と下位から一定の割合を除外した平均を算出する、TRIMMEAN関数。
成長率などの倍率の平均を算出する、GEOMEAN関数。
時速の平均などの単位当たりの数値の平均を算出する、HARMEAN関数。
というように、色々な平均を算出する関数が用意されているのです、残念ながら、何らかの重み付けした平均である「加重平均」を算出するための関数はありません。
なぜ、加重平均が必要なのか、次の表を使って確認していきましょう。
仙台店は、セール期間ということで割引して販売したところ、割引の効果もあって、販売数が伸びているのがわかります。
そして、B列の販売価格の平均値を算出するにあたり、お馴染みの平均を算出するAVERAGE関数をつかって、B6に算出してみたのがこの表です。
販売価格の平均ではあるのですが、販売数は全く考慮していません。
しかしよく考えてみると、
新宿店の販売金額は、1500×477= 715500
仙台店の販売金額は、 700×1587 1110900
平均は、合算値をそのデータ件数で除算するわけです。
ということから、販売数を考慮したほうが合理的ですね。
安い販売価格の販売数は、高い販売価格よりも多いので全体としては、平均値は下がるはずです。
このような時につかうのが、「加重平均」です。
「加重平均」を算出するには、販売数を重み付けして、販売価格に積算してからその総和を販売数の総和で除算することで算出することができます。
このことから、今回使用する関数は、SUMPRODUCT関数とSUM関数で算出していきます。
最初に、販売価格に積算してからその総和をするために、SUMPRODUCT関数をつかった数式を作っていきます。
そして、SUM関数で販売数の合算値で除算するわけですから、
B7の数式は、
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
とします。
算出結果は、1052。
AVERAGE関数で算出した数値よりも、予想通り下がる結果となりましたね。
ご覧のように、加重平均を一発で算出するための関数は今のところありませんが、SUMPRODUCT関数とSUM関数を使うことで、算出することができますので、会議資料などで加重平均が必要な時には、覚えておくと便利かもしれませんね。
最近いろいろな関数が追加されているので、もしかしたら、登場するかもしれませんね。
今回は、LOGINV関数～LOWER関数までをご紹介しております。
MATCH関数
マッチ
値を検索してその相対位置を算出
MATCH(検査値,検査範囲,[照合の種類])
MAX関数
マックス
最大値を算出
MAX(数値1,[数値2],…)
MAXA関数
マックスエー
数値・文字列・論理値を含む最大値を算出
MAXA(数値1,[数値2],…)
MAXIFS関数
マックスイフズ
条件により指定した範囲内の最大値を算出
MAXIFS(最大範囲,条件範囲1,条件1,…)
MDETERM関数
マトリック ディターミナント
配列の行列式を算出
MDETERM(配列)
MDURATION関数
エムデュレーション
額面価格を $100 と仮定して、証券に対する修正マコーレー デュレーションを返します。
Modified DURATION
MDURATION(受渡日,満期日,利率,利回り,頻度,基準)
店舗別で管理しているテーブルはあるのですが、これをどうやったら、まとめて集計することができるのでしょうか？
大きなテーブルがあるのならば、集計機能を使ったクエリならば、簡単に算出できますが、次のように複数のテーブルに分かれて管理してる場合はどのようにしたらいいのでしょうか？
新しいテーブルを用意して、それぞれのテーブルのデータを追加してもいいのですが、その場合、店舗数が多いなどデータが膨大だと、新しいテーブルが増えてしまうなど、ファイルサイズが大きくなりデメリットも見えてきます。
このような場合、ユニオンクエリをつかうことで、テーブルを合体（UNION）することができます。
そして、作成したユニオンクエリを基に、集計を行えばいいわけですね。
では、ユニオンクエリを作っていきます。
作成タブから一発で、ユニオンクエリを作ることができませんので、作成タブの「クエリデザイン」をクリックします。
次のように入力します。
UNION
select * from T新宿売上高
とりあえず、実行してみましょう。
このクエリに名前を付けて保存します。
あとは、集計をしていくわけですが、先に、ユニオンクエリを確認しておきましょう。
select * from T横浜売上高
UNION
select * from T新宿売上高
selectは、「選択クエリ」の意味です。*は「すべてのフィールド」で、from T横浜売上高が「T横浜売上高から」ということで、T横浜売上高の全てのフィールドをつかうという意味です。
そして、UNION。
UNIONは、「結合：合体」という意味です。
このあとに、結合したいテーブルを記入するわけです。
この文章は、SQL文ですが、このぐらいならば、入力するだけなので、SQLに詳しくなくても問題はないかと思います。
集計するクエリをつくって完成ですので、クエリデザインをつかって、クエリを作成していきます。
集計行を表示するために、「集計」ボタンをクリックします。
営業月フィールドごとに集計しますので、営業月フィールドには「グループ化」。売上高フィールドには「合計」を設定して完成です。
月別の合算数を算出した表（クエリ）をつくることができました。
今回のように、ユニオンクエリというのを知っていると、さらに幅が広がりますので、機会がありましたら使ってみると、作業効率が改善できるかもしれませんね。
AVERAGE関数の兄弟のようなAVERAGEA関数。
「A（エー）」のあるなしの違いですが、次のようなデータで、平均値を算出したい場合は、とても効果抜群なのがAVERAGEA関数です。
=AVERAGE(B2:B6)
B5の「欠席」データは、文字列なので、算出対象外ということになりますので、データの件数は4件という扱いになり、4で除算することになるわけです。
ところが、欠席だろうが、人数の平均を求めたい時など、文字データも含めて算出したことがあります。
C7の数式を確認しておきましょう。
=AVERAGEA(C2:C6)
AVERAGEA関数は、文字データを「0（ゼロ）」として、算出対象として平均値を算出してくれます。
このように、文字列の扱いをどうするのかによって、AVERAGE関数なのか、AVERAGEA関数なのか使い分ける必要があります。
AVERAGEA関数の基本情報を確認しておきます。
AVERAGEA関数の読み方は「アベレージエー」です。
カテゴリーとしては、「統計」に所属しています。
AVERAGEA関数の引数も確認しておきましょう。
AVERAGEA (数値1[,数値2]…)
このAVERAGEA関数の一番の注意点は、AVERAGE関数と間違って使わないようにすることですね。
ファイルを添付してメールなどで送信する場合、パスワードを付けて送信することが多々あります。
その場合のパスワードを同じにするのも、ちょっとどうかと思いますし、かといって、「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に設定した数式は、
=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関数は、セルに文字を入力するなど、アクションをしてしまうと、値が変わってしまうので、ランダムパスワードを作成したら、すぐに、値のコピーをつかって、管理する必要があります。
Facebookページで【書いてみた】ワンポイントです。
5月31日
Excel。ショートカット。
Ctrlキー＋Shiftキー+HOMEキーで、アクティブセル～A1までを選択することが出来ちゃいますね。
6月1日
Excel。ショートカット。
Ctrlキー＋Shiftキー+ENDキーで、アクティブセル～表の右下の隅っこまでを選択することが出来ちゃいます。
6月2日
Excel。ショートカット。
Ctrlキー＋Shiftキー+ +キーで、セルの挿入ダイアログボックスが表示されます。
6月3日
Excel。ショートカット。
Ctrlキー＋Shiftキー+ -キーで、セルの削除ダイアログボックスが表示されます。
6月4日
Excel。ショートカット。
Ctrlキー＋dキーで、上のセルの内容をコピーできます。けど、使ったことが無い…
6月5日
Excel。ショートカット。
Ctrlキー＋rキーで、左のセルの内容をコピーできます。けど、使ったことが無い…
6月6日
Excel。ショートカット。
Shiftキー＋←キーで、左側のセルを範囲選択することができます。
資料を作成するにあたり、相対的に評価をつけないといけないことがあるわけです。
例えば、先生ご用達というか、「通知書」などの相対評価表です。
では、どうやったら、楽に相対評価を算出することができるのでしょうか？
次の表のD列の評価フィールドを算出していきます。
H2は、累計を算出するための数式が設定してあります。
H2の数式は、
=SUM($G$2:G2)
この数式を、H6までオートフィルで数式をコピーしています。
作業スペースをつくらないで、一発で算出するならば、XLOOKUP+ PERCENTRANK.INC関数で算出できるのですが、説明の為、数式を分解して説明していきます。
そこで登場するのが、PERCENTRANK.INC関数です。
PERCENTRANK.INC関数は、百分位を算出することできる関数です。
E列には、PERCENTRANK.INC関数をつかって百分位を算出しています。
E2の数式は、
=PERCENTRANK.INC($C$2:$C$11,C2,2)
最初の引数は、配列。
範囲選択なので、C2:C11。オートフィルで数式をコピーするので、絶対参照を設定していきます。
2つ目の引数は、C2。
これで、C2の値は、全体の中で、何パーセントの位置にあるのかがわかります。
最後の引数は、有効桁数。
小数点第何位まで表示するのかということですね。
この数式を、次のように変更する必要があります。
E2の数式を変更します。
=1-PERCENTRANK.INC($C$2:$C$11,C2,2)
百分位は、100％の位置にあるデータは100%と算出されるのはいいのですが、Aの配分累計は10%の位置で、Eの配分累計が100%となっています。
ようするに「逆」になっているので、１から減算する必要があるわけです。
あとは、=1-PERCENTRANK.INC($C$2:$C$11,C2,2) で算出した値が、どの配分累計に属しているのかを算出すればいいわけです。
VLOOKUP関数をつかって評価を検索抽出するように思われますが、F1;H6の表。
一番左側の列に、検索抽出したい値があるわけです。
これだと、VLOOKUP関数は使えません。
INDEX+MATCH関数なんて方法もありますが、XLOOKUP関数が登場したわけですから、XLOOKUP関数をつかえば、一番左側に検索抽出の列があっても問題ありません。
D2に設定した数式は、
=XLOOKUP(E2,$H$2:$H$6,$F$2:$F$6,,1,-1)
引数の確認をしておきましょう。
検索値が、「E2」。
検索範囲が、「$H$2:$H$6」 この範囲は、検索したい範囲です。
戻り範囲が、「$F$2:$F$6」 抽出したい範囲です。
見つからない場合は、省略とします。
一致モードには「1」。これは、「完全合致または次に大きい項目」を選択します。
検索モードには、「-1」の「末尾から先頭へ検索」を選択しました。
本当ならば、スピル機能を考えて範囲選択するのですが、今回は、VLOOKUP関数っぽく使用しております。
二段階の計算式を一つにまとめたのが、次の数式です。
=XLOOKUP(1-PERCENTRANK.INC($C$2:$C$11,C2,2),$H$2:$H$6,$F$2:$F$6,,1,-1)
この数式を、D2に設定すれば相対評価を算出することができます。
見慣れない関数のネストで、長い数式になると、わかりにくくなりますので、二段階で算出するのでいいと思います。
ということで、Excelには様々な関数が用意されていますので、色々組み合わせみると、意外につかえるネストとか見つかるかもしれませんね。