Facebookページで【書いてみた】ワンポイントです。
3月15日 Excel。
XIRR関数。読み方は、エックスアイアールアールで、非定期キャッシュフローに対しる内部利益率を算出します。
3月16日 Excel。
XNPV関数。読み方は、エックスエヌピーヴイで、非定期キャッシュフローに対する正味現在価値を算出します。
3月17日 Excel。
XOR関数。読み方は、エクスクルーシブ・オアで、複数の条件で奇数の数を満たすかどうかを調べる
3月18日 Excel。
YEAR関数。読み方は、イヤーで、日付から年を算出します。
3月19日 Excel。
YEARFRAC関数。読み方は、イヤーフラクションで、2つの日付の間の期間を年数で算出
3月20日 Excel。
YEN関数。読み方は、エンで、数値を四捨五入して、円記号（￥）を付けた文字列に変換する
3月21日 Excel。
YIELD関数。読み方は、イールドで、定期利付債の利回りを算出します。
ただデータを集めて表にしただけでは、もったいないので、ちょっとした数値を算出してみると、違ったことが見えるというか想像したり、仮定をたてたりすることができるかもしれません。
例えば、次の表。
B列の各店舗における商品Aの販売数のデータですが、このままではただのデータでしかないので、例えば、販売数が101～200の店舗（件数）がいくつあるのかが、わかれば、なぜ、販売個数が多くなるのかなど、仮定をたてることも出来たりするかもしれません。
そこで、100ごとの階級として、それぞれの階級に何店舗該当するのかを算出してみようと思います。
このような階級ごとの数値のことを「度数」と呼んでいます。
Excelでは、度数を算出する関数が用意されています。
それが、「FREQUENCY関数」です。
このFREQUENCY関数ですが、ちょっと厄介な関数でして、配列関数のため、算出したい範囲を選択しておいてから、数式をつくり、確定させる時に、Ctrl + Shift + Enterキーを押して設定させる必要がありました。
しかし、Office365のExcelやExcel2019に追加された新機能である「スピル機能」をつかうことで、FREQUENCY関数が、普通の関数と同じような感じで数式を作れるようになりました。
では、スピル機能をつかった、FREQUENCY関数を確認していきましょう。
G2にFREQUENCY関数の数式を設定してきます。
FREQUENCY関数ダイアログボックスで数式を設定してもいいですが、
FREQUENCY（データ配列,区分配列）
とFREQUENCY関数は引数が2個と少ないので、手入力で作成していくといいでしょう。
=FREQUENCY(B2:B11,F2:F11)
数式を確定すると、スピル機能によって、G2の数式が、下記方向にコピー（溢れて）されます。
配列関数のときは、絶対参照が必要になりますが、スピル機能は絶対参照の設定は不要です。
なお、今まで通り、Ctrl + Shift + Enterキーをつかった、配列関数にしても、問題はありません。
ただ、G12までスピル機能で算出されていますが、これは、1001以上の度数を算出しているので、不要であったとしても、スピル機能で管理している範囲の為、G12の数式だけを削除することができません。
今回のFREQUENCY関数だけではなくて、スピル機能の登場で、今まで以上に、使い勝手がより良くなっている関数があるかもしれませんので、色々確認してみるといいかもしれませんね。
Excelには様々な関数が用意されていますが、今回は、ABS関数を紹介。
数値から「正」「負」の記号を取り除いた数値である『絶対値』を算出したいときにしようするのが、ABS関数です。
ABS関数の読み方は「アブス」といいます。
絶対値を英語だと、「Absolute value」というので、「エービーエス」って読むよりも、「アブス」ってほうがしっくりきそうですね。
ABS関数の引数は、
ABS(数値)
関数の分類は、「数学・三角」に所属しています。
次の表を使って確認してみましょう。
B2につくる数式は、
=ABS(A2)
結果はこのようになりました。
絶対値は、「0（ゼロ）」からの距離ということなので、プラスやマイナスの記号がとれた数値のみという結果になります。
ABS関数のみで使用することは少ないかもしれませんが、関数を多く知ることで、作業効率が改善できる可能性が広がりますので、少しずつ知っている関数を増やしていくのもいいかもしれませんね。
条件付き書式をつかえば、セル内の文字に条件と合致するデータが含まれていれば、そのセル自体に書式を設定することは簡単です。
例えば、次の表。
範囲選択をして、ホームタブの条件付き書式から「セルの強調表示ルール」にある「文字列」をクリックします。
しかしながら、セルは塗りつぶすことができましたが、該当するレコード。
つまり行全体に条件付き書式を設定し、反映するにはどのようにしたらいいのでしょうか？
それでは、該当するレコード。行全体に条件付き書式を設定していきます。
A2:C10まで範囲選択します。
ホームタブの条件付き書式にある「新しいルール」をクリックします。
行全体など、アレンジしたい時は、「数式を使用して、書式設定するセルを決定」をつかうことで、対応することができます。
次の数式を満たす場合に値を書式設定に、次の数式を設定します。
=find("横浜市",$C2)
FIND関数は、セル内に検索したい文字が左から何文字目にあるのかを算出することができる関数です。
今回の場合、セル内に、「横浜市」という文字がなければ、#VALUEという判定になり、条件を満たすことができないと判断されます。
「横浜市」という文字があれば、数値を算出してくれます。
また、引数内の「$C2」ですが、列固定の複合参照とすることで、行を対象として書式を反映することができます。
仮に、「C$2」のように行固定の複合参照にすれば、列を対象として書式を反映することができます。
あとは、書式ボタンをクリックして、任意の塗りつぶしを設定します。
最後に、新しいルールダイアログボックスのOKボタンをクリックします。
条件付き書式は、関数を組み合わせつかうことで、色々対応することができるので、社内の資料などで、つかってみると、よりわかりやすくなるなるかもしれませんね。
このBLOGの記事を、カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。
Excel。期間内の祝日の日数を数えたいけど、どうしたらいい？
祝日を数える方法は簡単そうですが、ちょっとアイディアが必要ですよね。
＜続きはコチラ＞
Excel。期間内の祝日・祭日の日数を数えたいけど、どうしたらいい？
https://infoyandssblog.blogspot.com/2015/12/excelholiday.html
Excel。並び替えをしても、常に1～の通し番号順にしたいけど、どうしたらいいの？
並び替えをしても、常に、1～の通し番号順にしたい
＜続きはコチラ＞
Excel。並び替えをしても、常に1～の通し番号順にしたいけど、どうしたらいいの？
https://infoyandssblog.blogspot.com/2015/12/excelsort1.html
Excel。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい
1・2・3行目のそれぞれが異なる色で塗りつぶすにはどうしたらいいのでしょうか？
＜続きはコチラ＞
Excel。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい
https://infoyandssblog.blogspot.com/2015/12/excelfill123.html
Excelだと、オートフィルターをつかうことで、平均値以上のデータを抽出することが簡単にできます。
では、Accessでは、どのようにしたらいいのでしょうか？
そもそも、テーブルのフィルターはどちらかといえば、「即席」という面があり、Accessで抽出といったら、やはりクエリをつかって抽出していくわけですね。
では、次のテーブルの売上高フィールドにある値が平均値以上のデータを抽出していくことにします。
根本的な問題として、どうやったら「平均値」を算出することができるのか？
それをどうやって条件としてつかうのかと考えるところです。
Excelのように、どこかのセルに算出しておいて、その結果を使うのかなどアレコレ考えてしまいます。
幸いAccessには、DAvg関数というのがあって、その関数をつかうことで、簡単に、平均以上を算出することが出来るのです。
>=DAvg("[売上高]","T商品A売上")
と設定します。
今回は、「以上」という条件なので、比較演算子の「>=」をつかうことで、条件を設定することができます。
なので、仮に「平均値より大きい」ということでしたら「>」になるというわけですね。
また、このDAvg関数は、
DAvg（フィールド名,テーブル名,条件）
という引数をもっています。ポイントは、テーブル名→フィールド名という順番でないということ。結構間違いやすいので、注意が必要ですね。
では、クエリを実行して確認してみましょう。
しかし本当に平均値以上なのでしょうか？
テーブルをExcelにエクスポートして確認してみましょう。
無事に、平均値以上のデータを抽出することができたようです。
今回は、Accessのテーブルデータから平均値以上のデータを抽出する方法をご紹介いたしました。
DAvg関数は、比較演算子での抽出や、Between And演算子をつかった抽出ほど、有名ではありませんが、Excelと同じように、様々な関数が用意されていますので、ちょっとした抽出をしたい時には知っておくといいかもしれませんね。
Facebookページで【書いてみた】ワンポイントです。
3月8日
Excel。WEBSERVICE関数。
読み方は、ウェブサービスで、XML形式のデータから必要な情報だけを取り出します。
3月9日
Excel。WEEKDAY関数。
読み方は、ウィークデイで、日付から曜日に該当する数値を算出する
3月10日
Excel。WEEKNUM関数。
読み方は、ウィークナンバーで、日付の週の番号を算出します。
3月11日
Excel。WEIBULL関数。
読み方は、ワイブルで、ワイブル分布の累積確率か確率密度を算出します。
3月12日
Excel。WEIBULL.DIST関数。
読み方は、ワイブル・ディストで、ワイブル分布の累積確率か確率密度を算出
Excel2010以降
3月13日
Excel。WORKDAY関数。
読み方は、ワークデイで、稼働日数後の日付を算出します。
3月14日 Excel。WORKDAY.INTL関数。
読み方は、ワークデイ・インターナショナルで、週末（曜日指定OK)と祝日を除いた日数後の日付を算出します。