今回は、HLOOKUP関数～HYPGEOM.DIST関数までをご紹介しております。
HLOOKUP関数
エイチルックアップ
横方向の表からデータを検索して抽出する
HLOOKUP(検索値,範囲,行番号,検索方法)
HOUR関数
アワー
時刻から"時"を算出する
HOUR(シリアル値)
HYPERLINK関数
ハイパーリンク
他のドキュメントへのリンクを作成
HYPERLINK(リンク先[,別名])
HYPGEOMDIST関数
ハイパージオムディスト
超幾何分布の確率を算出
HYPGEOMDIST(標本の成功数,標本の大きさ,母集団の成功数,母集団の大きさ)
HYPGEOM.DIST関数
ハイパージオム・ディスト
超幾何分析の累計確率か確率密度を算出 Excel2010以降
HYPGEOM.DIST(標本の成功数,標本の大きさ,母集団の成功数,母集団の大きさ,関数形式)
累計を算出するには、SUM関数の場合、引数の最初を絶対参照にして、二つ目の引数は、相対参照にすることによって、累計を算出する計算式をつくることができるのですが、この方法を知らないと、2つの計算式をつくることで、累計を算出することになるわけです。
ただ、それは、ちょっと面倒です。
フィルターで、抽出しても、抽出したアイテムだけの累計を算出したいとなれば、さらに難易度が上がってしまいます。
そこで、ピボットテーブルをつかうことで、様々なケースに対応できしかも簡単に累計を算出することができます。
次のデータを用意しました。
営業月を算出し、その営業月の累計をピボットテーブルで算出していきます。
表の中のセルをクリック（アクティブ）して、挿入タブのピボットテーブルをクリックします。
基本的に、このダイアログボックスでは何も設定しなくていいので、OKボタンをクリックします。
「日付」にチェックマークをオンにします。
行のボックスに、旅行名が入力されました。
営業月ごとですが、月というフィールドはありません。日付を月ごとにまとめる必要があるのですが、Excel2016以降のピボットテーブルでは、ピボットテーブルが勝手にまとめてくれます。
行のボックスに「月」「日付」というフィールドが入力されました。
「日付」フィールドは不要ですので、行のボックスから削除します。
「金額」にもチェックマークをオンにします。値のボックスに「合計/金額」フィールドが入力されました。
そして、累計を算出するためのフィールドが必要なので、フィールドリストから、ドラッグアンドドロップで、値のボックスにある「合計/金額」の下に「金額」を移動すると「合計/金額2」というフィールドが入力されました。
ピボットテーブルはこのように表示されています。
「合計/金額2」は合計値なので、これを累計に変更していきます。
値のボックスにある「合計/金額2」の▼をクリックします。
値フィールドの設定をクリックします。
値フィールドの設定ダイアログボックスが表示されます。
名前の指定を「累計」に変更します。
計算の種類タブに変更して、「累計」を選んだら、OKボタンをクリックします。
これで、累計を算出することができました。
ピボットテーブルをつかうことで、様々な集計を簡単に作ることができますので、色々確認してみるといいかもしれませんね。
Excelのグラフは、用途に合わせて様々なグラフを作ることができます。
今回は、グラフの復習ということ、4つをピックアップ
・Excel。グラフの背景を塗り分けて、アピールしたグラフの作成方法
・Excel。ピクチャー円グラフを作る方法をご紹介。
・Excel。作れない複数分離円グラフを強引に作るとしたら
・Excel。二重ドーナツグラフを作ってみよう
Excel。グラフの背景を塗り分けて、アピールしたグラフの作成方法。
平均値や目標値などを描くのもいいのですが、背景を塗り分けることで、プレゼンテーション用の資料として使えるグラフにしたい場合どうしたらいいのでしょうか？
＜続きはこちら＞
Excel。グラフの背景を塗り分けて、アピールしたグラフの作成方法。
https://infoyandssblog.blogspot.com/2017/11/excelexcel2013area-graph.html
Excel。ピクチャー円グラフを作る方法をご紹介。
ピクチャー円グラフを作ろうとすると、意外と難しくなりますので、今回は、ピクチャー円グラフの作り方をご紹介していきます。
＜続きはこちら＞
Excel。ピクチャー円グラフを作る方法をご紹介。
https://infoyandssblog.blogspot.com/2017/11/excelpie-chart.html
Excel。作れない複数分離円グラフを強引に作るとしたら
複数分離円グラフの強引な作り方をご紹介していきます。
＜続きはこちら＞
Excel。作れない複数分離円グラフを強引に作るとしたら
https://infoyandssblog.blogspot.com/2017/12/excelpie-chart.html
Excel。二重ドーナツグラフを作ってみよう
円グラフは一つの項目の比率を表すことができますが、例えば次のような表を各店舗と各地域を合わせたグラフを作ろうとすると、円グラフでは対応することが出来ません。
＜続きはこちら＞
Excel。二重ドーナツグラフを作ってみよう
https://infoyandssblog.blogspot.com/2017/12/excelexcel2013double-donut.html
データはあるので、どのような状況のデータなのか判断するのに、度数分布表をつくることがあります。
A:B列にデータがあって、そのデータの参加者を30置きにした度数分布をD1:E8に算出している表があります。
D1:E8の度数分布表でも十分なのですが、数値では資料としてはわかりにくいので、グラフにしていきましょう。
度数分布表は一般的に集合縦棒グラフの棒グラフを太くした、『ヒストグラム』と呼ばれるグラフを作っていきます。
作成自体は簡単ですが、頻繁に作成されているようで、Excel2016あたりからは、グラフの作成メニューにヒストグラムが用意されているぐらいです。
ただし、Excelが用意しているヒストグラムだと、自由に作ることができませんし、度数分布表をつくっていると、トラブルが発生するので、自力で作ることをお勧めします。
念のために確認しておきましょう。
トラブルを発生させますので、D1:E8を範囲選択して、挿入タブの「統計グラフの挿入」からヒストグラムをクリックします。
ヒストグラムが表示されたのですが、何かおかしいですね。
メニューのヒストグラムから作る時には、度数分布表からではなく、元のデータを範囲選択してヒストグラムを作成する必要があります。
要するに、度数分布表は不要なのです。
改めて、A1:B128を範囲選択して、ヒストグラムを挿入します。
ヒストグラムとしてはいいのですが、度数分布表と合わせようとすると、うまくいきません。
横軸を選択して、書式タブの選択対象の書式設定をクリックします。
軸の書式設定作業ウィンドウを表示します。
これはデータの最低値が4ということで、その最低値から作成するようになっているからです。
そのため、度数分布表と合致することが難しいわけです。
即席でヒストグラムを作る場合はいいのですが、度数分布表に合わせたいなど、自分でコントロールしたヒストグラムを作るためには、集合縦棒グラフを修正する必要があります。
改めて、作成してきます。
E1:E8を範囲選択して、挿入タブの「縦棒/横棒グラフの挿入」にある集合縦棒をクリックします。
E列のみでOKです。
D列を含めると、結局削除する必要が発生します。
ここからアレンジをしていきます。
横軸が、1～7と表示されてしまっているので、横軸を修正していきます。
グラフのデザインタブの「データの選択」をクリックします。
データソースの選択ダイアログボックスが表示されます。
横（項目）軸ラベルの編集ボタンをクリックします。
軸ラベルダイアログボックスが表示されるので、軸ラベルの範囲に、階級のD2:D8を設定して、OKボタンをクリックします。
データソースの選択ダイアログボックスに戻りますので、こちらもOKボタンをクリックします。
あとは、縦棒グラフを太くして完成です。
データ系列の書式設定作業ウィンドウが表示されますので、系列オプションの使用する軸の「要素の間隔」を0%にします。
これで、縦棒グラフを太くすることができますので、あとはデータラベルを表示して、フォントサイズなど見やすいように調整して完成です。
なお、要素の間隔を4%ぐらいにすると、棒グラフ同士の間に隙間ができますので、見やすくなります。
Facebookページで【書いてみた】ワンポイントです。
8月31日
Excel。LOGNORM.DIST関数。
読み方は、ログノーマル・ディストで、対数正規分布の累積確率か確率密度を算出
9月1日
Excel。LOGNORM.INV関数。
読み方は、ログノーマル・インバースで、累積確率から対数正規分布を算出 Excel2010以降
9月2日
Excel。LOOKUP関数。
読み方は、ルックアップで、1行/1列のセル範囲でせるを検索し対応するセルの値を返す。ベクトル形式 LOOKUP(検索値,検索範囲,対応範囲)
9月3日
Excel。LOOKUP関数。
読み方は、ルックアップで、縦横を指定しないでセルを検索し対応するセルの値を返す。LOOKUP(検索値,配列)
9月4日
Excel。LOWER関数。
読み方は、ロウアーで、英字を小文字に変換する
9月5日
Excel。MATCH関数。
読み方は、マッチで、値を検索してその相対位置を算出
9月6日
Excel。MAX関数。
読み方は、マックスで、最大値を算出
Excelテクニック and MS-Office recommended by PC training
先月と今月の数値を比べ、その増減を表示するなら、IF関数などで十分ですが、会議用の資料として、見栄えのするような資料にしたいときに、条件付き書式のアイコンセットを使ってみると、いつもと異なった印象の資料を作ることができます。
次の表を用意しました。
D列の増減は、C2-B2。
今月-前月の算出結果です。
この表を会議資料としても、全く問題はありませんが、増減を数値にするよりも、増減がわかる図にしたほうがいいように思えます。
そこで、IF関数をつかってみると、次のような表現をすることができます。
増減を矢印で表現してみました。
数値よりも、一目で増減がわかるかと思います。
ただ、会議資料としては、矢印が、細いので、このあと、太くしたり、色を付けたりする必要があります。
また、この矢印は目視で入力したのではなく、IF関数をつかって算出しています。
D2の数式は、
=IF(B2>C2,"↓",IF(B2=C2,"→","↑"))
IF+IF関数のネストで三分岐している数式です。
ネストになっている計算式を作るのが苦手だと、数式を作成するだけで作業時間を費やしてしまいます。
Excel2019以降ならば、IFS関数も用意されていますが、ちょっと面倒です。
IFS関数で、D2に数式をつくると、
=IFS(C2>B2,"↑",C2=B2,"→",C2<B2,"↓")
そこで、条件付き書式のアイコンセットをつかってみることにします。
最初に、D列の増減値を算出します。
D2:D6まで範囲選択します。
ホームタブの「条件付き書式」からアイコンセットの「3つの矢印（色分け）」を選択します。
アイコンセットが表示されました。
D2:D6を範囲選択して、ホームタブの「条件付き書式」からルールの管理をクリックします。
条件付き書式ルールの管理ダイアログボックスが表示されます。
変更したいルールを選択したら、「ルールの編集」をクリックします。
アイコンのみ表示にチェックマークいれます。
緑の↑矢印は、比較演算子を「＞」値は「0」種類を「数値」に設定します。
黄の→矢印は、比較演算子を「>=」値は「0」種類を「数値」に設定します。
設定したら、OKボタンをクリックします。
条件付き書式ルールの管理ダイアログボックスに戻りますので、こちらもOKボタンをクリックします。
これで、完成しました。
文字の矢印よりも見栄えはいいように感じます。
また、今回ご紹介した方法は、IF関数の数式を作成するよりも簡単だと思われますので、色々な方法を知っておくのもいいかもしれませんね。
2020年8月。
皆様に閲覧していただいた項目のTOP10をご紹介させていただきます。
1位
Excel。VBA。アチラコチラに結合されたセル。ちまちま解除するのが面倒なんです。
https://infoyandssblog.blogspot.com/2020/08/excelvbauncoupling.html
2位
Excel。折れ線グラフを交点0からスタートさせるには？
https://infoyandssblog.blogspot.jp/2013/07/excel0.html
3位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる
https://infoyandssblog.blogspot.jp/2016/03/excel24hour-schedule24.html
4位
Excel。最低点を知りたい。だけど、0点は除いてね。って昔は大変だった
https://infoyandssblog.blogspot.com/2020/08/excel0minifs.html
5位
Excel。料金量がわかりやすい階段グラフの作り方
https://infoyandssblog.blogspot.com/2017/09/excelstaircase-graph.html
6位
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
https://infoyandssblog.blogspot.jp/2015/12/excelgraph.html
7位
Excel。時間経過の折れ線グラフ。実は散布図で作るとより綺麗に描けるのです。
https://infoyandssblog.blogspot.com/2016/08/excelgraph.html
8位
Excel。あれれ！グラフが表示されない！！そんな時は、第2軸で表示しましょう。
https://infoyandssblog.blogspot.com/2015/10/excelgraph2.html
9位
Excel。順位推移グラフを作成したいけど、どうしたらいいの？
https://infoyandssblog.blogspot.com/2019/10/excelrank-transition-graph.html
10位
Excel。事務職のデータ分析その10。散布図に平均値を表示する方法
https://infoyandssblog.blogspot.com/2017/09/excel10scatter-plot.html