3/30/2018

Excel。VLOOKUP関数の盲点。降順の以上の範囲では検索できないのでどうしよう。【VLOOKUP】

Excel。VLOOKUP関数の盲点。降順の以上の範囲では検索できないのでどうしよう。

<VLOOKUP関数とINDEX+MATCH関数>

多分岐処理をしたいときに、IF関数のネストではネストが多くなりすぎて、
VLOOKUP関数を使うやり方があるのですが、ある点に注意しないと、
算出することができません。

次の表があります。

点数によって、ランク分けしたいわけですね。

ランク分けの表は同じシートに用意しました。

A列は、表示形式のユーザー定義を使って、
”以上”を表示できるようにしています。

ただし、点数が降順になっていますね。

D2をクリックしてVLOOKUP関数ダイアログボックスを表示しましょう。

検索値は、C2
それぞれの点数ですね。この点数がどのランクなのかを知りたいわけですね。

範囲には、$A$14:$B$18
ランク表ですね。見出し行は含まないで範囲選択します。
また、オートフィルで数式をコピーしますので、
絶対参照も忘れずに設定しましょう。

列番号は、2
範囲で選択した列で、検索したい列は左から2列目にありますので、2。

検索方法は、TRUE。または、1でもOKですね。
完全一致ではなくて、「内輪の数」なので、TRUEを使います。

それでは、OKボタンをクリックして、
オートフィルを使って数式をコピーしてみましょう。

なんと、検索がうまくできていませんね。

これは、範囲で選択したランク表が降順だったのが原因なのです。
検索範囲の表は、昇順でないとVLOOKUP関数は機能してくれません。

念のため、昇順の表を使ってみたらどうなるのでしょうか?

D13:E18に昇順にしたランク表を用意しました。

D列は、先ほどと同様に、表示形式のユーザー定義を使って、
”以上”と表示させています。

では、E2にVLOOKUP関数ダイアログボックスを表示して数式を作っていきます。

範囲が、$D$14:$E$18になっているだけで、先ほどと変わっていませんね。

E2の数式は、
=VLOOKUP(C2,$D$14:$E$18,2,TRUE)
すると、昇順にした範囲だと、問題なくランクを検出してくれていますね。

ということで、VLOOKUP関数で多分岐処理を行う場合には、
範囲の表を昇順にしておくことを忘れないようにしましょう。

となるのですが、もし、降順の表の場合はどうしたらいいのでしょうか?

【範囲が降順の表】

範囲の表が降順だった場合ですが、残念ながらVLOOKUP関数は使えませんので、
別の方法でトライしていきます。

A13:B18の表を、「以上」という表から、80以上ではなくて、
80以上100以下とか0以上19以下というように、
その区間を表すようにアレンジしていきます。

なんで、このC列を用意する必要があるのかというと、
点数がどこの範囲に該当しているのかを見つけるために、
MATCH関数を使うからです。

このMATCH関数の引数に昇順と降順が影響する箇所があるので、
今回は降順の表のため、C列を用意する必要があるのです。

では、わかりやすいようにMATCH関数をF2に作ってきます。

MATCH関数ダイアログボックスでもいいのですが、
意味を確認したいので、手入力で数式を作ります。

検索値は、C2
検査範囲は、C14:C18。絶対参照も忘れないようにしましょう。

そして、ここがポイントの照合の種類
1の以下は、検査範囲が昇順で配置しておく必要があるので、
今回は、「-1以上」を使わないといけません。

検索値以上とするは、C列が必要だったわけです。

F2の数式は
=MATCH(C2,$C$14:$C$18,-1)

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

最後は、算出されたMATCH関数を元に、
INDEX関数を使ってD2:D11にランクを算出していきます。

D2をクリックして、INDEX関数ダイアログボックスを表示していくと、

引数の選択ダイアログボックスが表示されますので、領域番号は不要なので、
『配列,行番号,列番号』を選択してOKボタンをクリックしましょう。

INDEX関数ダイアログボックスが表示されますので、
引数を設定していきましょう。

配列は、$B$14:$B$18
配列といっても、抽出したいランクがある範囲ですね。

行番号は、F2
MATCH関数で算出した結果を使っています。
配列で選択した範囲の中の行を見に行くという指示をしています。

列番号は、1
配列の範囲が1列なので、省略してもいいのですが、
わかりやすいように1列目を見に行くという指示をしています。

つまり、配列の範囲の、1列F2行のデータを抽出するという意味になります。

それでは、OKボタンをクリックしましょう。

このように、降順の表でも算出することができましたね。

なお、D2の数式は、
=INDEX($B$14:$B$18,F2,1)

となっていますが、今回はわかりやすいように、
MATCH関数をF列に事前に算出させていますが、
当然、一つにまとめることができます。まとめた数式は、

=INDEX($B$14:$B$18,MATCH(C2,$C$14:$C$18,-1),1)

確かに降順の表を使っても抽出することはできますが、
昇順の表を用意するほうが楽な気もしますので、
どうしても、昇順の表が用意できない場合は、
このような方法もある程度でいいような気もしますね。

3/29/2018

Officeソフトのスキルアップに追加しました。2018/03/29

Officeソフトのスキルアップに追加しました。

<目次サイト>

このBLOGの記事を、
目次サイトの【Officeソフトのスキルアップ】に追加しました。


合計対象が増えても範囲選択が自動対応する方法
https://infoyandssblog.blogspot.jp/2016/05/excelindirect.html


行を非表示にしても条件を満たす件数を数えたい場合どうしたらいい?
https://infoyandssblog.blogspot.jp/2016/05/excelsubtotal.html


別々の列にあるデータをまとめた順位を知りたい
https://infoyandssblog.blogspot.jp/2016/05/excelrankeq.html


COUNTIF関数の条件をセル入力で設定変更することはできませんか?
https://infoyandssblog.blogspot.jp/2016/05/excelcountif.html

3/27/2018

Access。クエリで四捨五入するにRound関数ではダメなんです。【Round】

Access。クエリで四捨五入するにRound関数ではダメなんです。

<Round関数・Int関数>

四捨五入。Excelでもおなじみの処理で、ROUND関数を使えば、
簡単に四捨五入した数値を算出することができますよね。

AccessにもExcelと同じように、Round関数というのがありまして、
四捨五入をしてくれるのですが、あることを知らないと、
とんでもない落とし穴があって、
Excelで算出したものと数値が異なってしまうのです。

次のテーブルがあります。

デザインビューで確認すると次のようなテーブルです。

このテーブルの数値の小数点第1位を四捨五入しますので、
次のようなクエリを作っていきます。

フィールドに、NOと数値の各フィールドを設定して、
四捨五入をするので、Round関数を使って算出しますので、
演算フィールドを作成します。

四捨五入Round: Round([数値])

Excelと同じような感じですね。
では、保存して、データシートビューに切り替えてみましょう。

問題なく算出されたようにも見えますが、
よく見てみると、おかしなところがあります。

それは、2.5が2になっていますよね。

4.5も4。四捨五入なので、2.5は3。4.5は5になっていないといけないはず。

しかし、1.5は2。3.5は4というように、
こちらはおなじみの四捨五入になっていますよね。

これ、偶数と奇数で四捨五入の処理が違うのです。

この四捨五入、壊れているわけでも、間違っているわけでもないのです。

そして、ここがExcelのROUND関数とAccessのRound関数の違いなのです。

だから知らないと、
この結果をExcelにエクスポートして使うと誤差が生じてしまうのです。

なんでこんなことが起こっているのかというと、
AccessのRound関数は、
JIS式の丸め処理(銀行型の丸め処理と呼ばれたりもします)を行っているのです。

これは、四捨五入の欠点を補うためのものなのですが、
四捨五入というのは、
どうしても数値が大きいほうに偏ってしまう傾向にあるのです。

要するに、1~9の数値のうち、
四捨にあたる1~4は、1・2・3・4と4つ数値が該当して、
五入にあたるのが、5~9で、5・6・7・8・9と5つの数値が該当しています。

そのため、どうしても、該当数が多い五入のほうに偏ってしまうわけです。

そこで、偏りを抑制するために、奇数と偶数に分けて、
真ん中の5を切り捨てる場合と、切り上げる場合を半分ずつにしたのが、
このJIS式の丸め処理なのです。

これをAccessのRound関数は採用しているわけですね。

では、どのようにしたら、
Excelと同じような四捨五入を行うことができるのでしょうか?

次のような演算フィールドを作成してみましょう。

演算フィールドは、四捨五入Int関数: Int([数値]+0.5)

では、結果を見てみましょう。

2.5は3。4.5も5と算出されていますよね。

このように、ExcelのROUND関数と同じ結果を求める場合には、
Int関数を使う必要があるのです。

Int([数値]+0.5)を説明すると、
2.4+0.5ならば2.9なので整数化するInt関数を使えば、2と算出されて、
2.5+0.5ならば、3なので、3と算出されるわけです。

さて、小数点第1位を四捨五入しているわけですが、
小数点第2位を四捨五入したい場合はどうしたらいいのでしょうか?

Excelでは、ROUND関数で引数に、1とか使えばいいのですが、
Round関数は使えませんので、次のような演算フィールドで対応します。

四捨五入Int関数02: Int([数値]*10+0.5)/10

10倍して+0.5して整数化して、10で除算してもとに戻すという数式ですね。

また、Round関数の演算フィールドに引数を追加しています。

これは、Round関数ではダメなことを確認するためです。

それと、数値フィールドに>4という抽出条件を加えていますが、
これは、データを見やすくするためのものですので、本来は不要です。

では確認してみましょう。

4.65は4.7に、4.75は4.8になっていますよね。

このように、Accessにも四捨五入を行う、Round関数というのがありますが、
Excelとちょっと異なる動きをしますので、ご注意のほど。

3/26/2018

Excel関数一覧表にアイテムを追加しました。【function:2018/03/26】

Excel関数一覧表にアイテムを追加しました。

<Excel関数一覧>

「Excelテクニック and MS-Office recommended by PC training」の目次サイト
Officeソフトのスキルアップサイト」のExcel関数一覧表に次のアイテムを追加しました。

DURATION デュレーション
定期的に利子に支払われる証券の年間マコーレー係数を算出

MDURATION エムデュレーション
証券に対する修正マコーレー係数を算出 Modified DURATION

PDURATION ピーデュレーション
目標価値になるまでの投資期間を算出 Period DURATION

RECEIVED   レシーブド
割引債の償還価格を算出

INTRATE イントレート
満期に償還される証券の利率を算出

YIELD イールド
定期利付債の利回りを算出

YIELDDISC イールドディスク
割引債の年利回りを算出

YIELDMAT  イールドマット
満期利付債の利回りを算出

DISC   ディスク
割引債の割引率を算出

PRICEDISC プライスディスク
割引債の額面100に対する価格を算出

3/25/2018

今週のFacebookページの投稿 2018/03/19-2018/03/25

今週のFacebookページの投稿 2018/03/19-2018/03/25

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

3月19日
Excel。絶対参照を設定するときには、
F4キーを2回クリックする列$行で表示されて、
列が相対で、行が絶対の複合参照の設定できますね。

3月20日
Excel。絶対参照を設定するときには、
F4キーを3回クリックする$列行で表示されて、
列が絶対で、行が相対の複合参照の設定できますね。

3月21日
Excel。絶対参照を設定するときには、
F4キーを4回クリックすると一巡して、通常の相対参照に戻りますね。

3月22日
Excel。複合参照は、絶対参照を理解してから使うほうがいいですね。
混乱の基ですね。

3月23日
Excel。関数で、よく聞く、"引数"って、
計算結果を求めるために必要な値のことですが、
なかなかわかりにくいので、カッコの中身という感覚でいいと思いますね。

3月24日
Excel。If関数でよく使う、比較演算子。
=は、等しいという意味ですね。

3月25日
Excel。If関数でよく使う、比較演算子。
<>は、等しくないという意味ですね。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

Excelのショートカット一覧はこちらで書いております。
http://yandss.p2.weblife.me/shortcutkey.html

3/24/2018

Excel。Office365のExcel2016で追加された、CONCAT関数とTEXTJOIN関数

Excel。Office365のExcel2016で追加された、CONCAT関数とTEXTJOIN関数

<CONCAT関数&TEXTJOIN関数>

【CONCAT関数】

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

C列からF列までのセルを結合して、
1つのセルに神奈川県横浜市神奈川区西寺尾としたい場合
どうしたらいいでしょうか?

セルを結合する場合には、”&”で結合する方法がありますよね。

H2に
=C2&D2&E2&F2
という数式を作ると、簡単に文字結合できるので、
神奈川県横浜市神奈川区西寺尾という結果を算出することが出来ます。

ただ、結合するセル数が多いと、”&”で結合するのは結構面倒ですよね。

CONCATENATE関数を使ってみても同じ結果になりますよね。

J2をクリックして、
CONCATENATE関数ダイアログボックスを表示して算出してみましょう。

J2の数式は、
=CONCATENATE(C2,D2,E2,F2)

このCONCATENATE関数でも文字結合できるのですが、
先程の”&”と同じように結合するセル数が多いと、とても大変ですね。

そこで、Office365のExcel2016で新たに追加された関数の一つに、
CONCAT関数(コンカット関数)というのが登場しました。

このCONCAT関数は、CONCATENATE関数の進化版でして、
基本的にCONCATENATE関数と同じく、文字結合することが出来るのですが、
CONCAT関数のメリットは、結合するセル数が増えたときに、
その効果を実感することが出来ます。

L2をクリックして、CONCAT関数ダイアログボックスを表示しましょう。

テキスト1に、C2:F2と入力して、OKボタンをクリックします。

このCONCAT関数は、
なんと、セルを範囲選択できるようになったのです!画期的ですね。

仮に、結合したいセル数が20個でも、
範囲選択するだけで算出することができるのです。

なお、L2の数式は、
=CONCAT(C2:F2)

【TEXTJOIN関数】

文字結合をするのに登場した、
CONCAT関数であっても、
以前からあるCONCATENATE関数や”&”を使ったとしても、
次の場合はどうしたらいいでしょうか?

やりたいのは、C列からF列の文字を結合するのですが、
その間を”-“(ハイフン)で連結していきたい。

つまり、SOU-1-a-内田としたいわけです。

先程と同様に簡単じゃないの?と思われるかもしれませんが、
意外な落とし穴があるのです。

では、H9に”&”を使って数式を作ってみましょう。

H9の数式は、
=C9&"-"&D9&"-"&E9&"-"&F9

セルの間に”-“を入れるだけですが、この数だけでも結構面倒なのに、
10個のセルが対象だったら大変ですよね。

しかも、オートフィルで数式をコピーしてみると、
データがないセルも関係なくいれてしまうので、
H10は、SHO-1--齋藤と、”-“(ハイフン)が連続してしまっています。

当然、”-“(ハイフン)は不要ですから削除するためには、
数式を変更しなければいけません。

では、CONCATENATE関数を使ってみたらどうなるでしょうか?

I9にCONCATENATE関数ダイアログボックスを表示しましょう。

途中に”-“(ハイフン)を設定していく必要がありますので、
セル数が少ない場合はいいですが、多くなると、先程の”&”同様に、
面倒な作業が発生してしまいます。

I9の数式は、
=CONCATENATE(C9,"-",D9,"-",E9,"-",F9)

そして結果を見てみると、

データのないセルのところは、“&”と同じく、
”-“が重なってしまっていますね。

そこで、この問題を解決するために登場したのが、TEXTJOIN関数なのです。

J9にTEXTJOIN関数ダイアログボックスを表示しましょう。

区切り文字には、"-"
今回は、”-“(ハイフン)で区切りたいので、”-“としています。

空のセルは無視には、TRUE
これをTRUEにすると、空のセル。
つまりデータがないところは関与しなくなります。

テキスト1以降は、結合したいセルをクリックしていきます。

そして、OKボタンをクリックして、
オートフィルで数式をコピーしてみましょう。

注目する点は、J10とJ12。データがないところは関与しないので、
”-“(ハイフン)が重なっていませんよね。

あまり使う機会はないのかもしれませんが、
新しく加わった、CONCAT関数とTEXTJOIN関数というのがあるということを
知っておいてもいいかもしれませんね。

3/23/2018

Officeソフトのスキルアップに追加しました。2018/03/23

Officeソフトのスキルアップに追加しました。

<目次サイト>

このBLOGの記事を、
目次サイトの【Officeソフトのスキルアップ】に追加しました。


あれれ?ちゃんと入力したはずなのに、ちゃんと表示してくれないという質問
https://infoyandssblog.blogspot.jp/2016/05/exceldisplay-format.html



テーブルの集計行のようなものをテーブルを使用しないで作ってほしい
https://infoyandssblog.blogspot.jp/2016/05/excelsubtotal_26.html



テーブルでSUMIF関数を使うと算出されるけど、数式が変?というご質問
https://infoyandssblog.blogspot.jp/2016/05/excelsumifsumif.html


あれれ?LOOKUP関数を使ってみたら、ちゃんと検索されないぞ!
https://infoyandssblog.blogspot.jp/2016/05/excellooklookup.html

3/21/2018

Excel。事務職のデータ分析その19。分析ツールを使って順位とパーセンタイル順位を簡単に算出してみる【Analysis tool】

Excel。事務職のデータ分析その19。分析ツールを使って順位とパーセンタイル順位を簡単に算出してみる

<分析ツール>

様々な資料を作るにあたり、
順位と百分位(パーセントタイル順位)を算出したりするだけでも、
簡単にその資料が改善されるわけですね。

ただ、関数を使って算出してもいいのですが、
入力して関数の数式を作るにしても、少し時間がかかるわけですね。

そこで、『分析ツール』を使ってみると、
簡単に、順位と百分位の順位を算出することができますので、
確認してみましょう。

では、次の表があります。

A列にNO。通し番号ですね。
B列に氏名が、C列に得点が入力されています。

このC列の順位と百分位(パーセンタイル順位)を
『分析ツール』を使って算出してみましょう。

まずは、『分析ツール』が使える状態になっているかどうか確認しましょう。

データタブの右端に、『データ分析』がなければ、
アドインする必要がありますので、ファイルタブのオプションをクリックして、
Excelのオプションダイアログボックスを表示しましょう。

アドインの分析ツールを選択して、設定ボタンをクリックします。

アドインダイアログボックスが表示されますので、
有効なアドインにある、『分析ツール』にチェックマークをつけて、
OKボタンをクリックします。

データタブに、「データ分析」が登場しましたので、早速クリックしましょう。

データ分析ダイアログボックスが表示されますので、
「順位と百分位数」を選択してOKボタンをクリックします。

順位と百分位数ダイアログボックスが表示されますので、
入力範囲には、C列の得点を選びますので、C1:C16を範囲選択します。

絶対参照が設定されますので、$C$1:$C$16と変わります。

先頭行をラベルとして使用にチェックマークを付けます。

出力オプションは、このシート上に算出しますので、
出力先をオンにして、E1をクリックします。絶対参照が設定されますので、
$E$1と変わります。

あとはOKボタンをクリックしましょう。

順位と百分位(パーセンタイル順位)が表示されましたね。

F列の得点は降順にならび、それに基づいた順位がG列に算出されて、
H列に百分位が算出されています。

このように、アドインの『分析ツール』を使うことで、
簡単に算出することができます。

ただ、ちょっと困ったことに、
氏名が無いので、氏名の列を追加しましょう。

VLOOKUP関数を使えば簡単に算出することができますね。

F2をクリックして、VLOOKUP関数を作成しましょう。
F2の数式は、
=VLOOKUP(E2,$A$2:$C$16,2,FALSE)
となっています。

これで完成しました。

このように、『分析ツール』を使うことで、
順位と百分位を簡単に算出することができます。