Excel。NOMINAL関数は名目年利率を算出できます。
<関数辞典:NOMINAL関数>
NOMINAL関数
読み方: ノミナル
分類: 財務
NOMINAL(実行利率,複利計算期間)
名目年利率を算出します
Excel・Word・PowerPoint・Accessのテクニックや研修ネタ・テキストを紹介しております。主に、講義先や現場レベルで質問があったものを中心に書いております。
NOMINAL関数
読み方: ノミナル
分類: 財務
NOMINAL(実行利率,複利計算期間)
名目年利率を算出します
大きな表から必要な列だけを抽出した別表をつくるとなると、コピーで貼り付けという作業は、ただただ、面倒な作業だといえます。
この表から、売上だけを抽出したA7:E11までの表をつくりたいわけです。
この程度の列数ならば、コピー&ペーストで解決できますが、列数が増えれば面倒です。
また、数式だった場合は、#REF!というエラーが表示される場合もあります。
値で貼り付けをつかうとしても、元の表で数値が変わった場合、抽出先のデータは、変わっていませんので、再度コピーする必要があります。
そこで、CHOOSECOLS関数をつかうことで、手早く抽出することができます。
=CHOOSECOLS(A2:H5,1,2,4,6,8)
これで、売上の列だけを抽出した別表をつくることができました。
では、数式を確認しておきましょう。
CHOOSECOLS関数は、範囲から必要な列を抽出することができる関数です。
最初の引数が、array。
範囲選択なので、A2:H5を範囲選択します。
残りの引数は、範囲選択した中で、左から必要な列番号を設定するだけです。
これで、必要な列だけの別表をつくることができます。
Facebookページに書いた、Excelの豆知識(Trivia)です。
6月15日
Excel。
Σ
オートSUMボタンをクリックして、範囲を決定するだけで合計を求めることができます。
6月16日
Excel。
オートSUMボタンで、合計するときに離れている所を計算したい時は、Ctrlキーでクリックすれば、算出できますね。
6月17日
Excel。
エラーが表示されるとビックリしますよね。
けど、ちょっと知っていると、ビックリしなくなります。
6月18日
Excel。
エラー。
#NULL!
ヌル
正式は、Null Intersection (ナル・インターセクション)です。
セル指定の「:(コロン)」や「,(カンマ)」がない時や、セル範囲に共通部分がないと登場します。
6月19日
Excel。
エラー。
#DIV/0!
ディブゼロ
正式は、DIVided by 0(ディヴァイディッド・バイ・ゼロ)です。
0で割り算をしちゃうと、登場しますね。
6月20日
Excel。
エラー。
#VALUE!
バリュー
正式は、Wrong Type Value (ローング・タイプ・バリュー)です。
不適切なデータが入っている時に登場しますね。
6月21日
Excel。
エラー。
#REF!
レフ
正式は、A REFerence to a cell that does not exit (ア・リファレンス・トゥ・ア・コール・ザット・ダズ・ノット・イグジット)です。
セルが参照できない時に登場します。
例えば、住所に横浜市という文字が含まれていたら○と判断したい。
このような場合、IF関数をつかえば、対応できるように思えます。
含まれるということで「*横浜市*」と
ワイルドカードをつかって、論理式をつくってみると…
判断の結果が!?
では、どのようにしたら、解決することができるのか。
その方法をご紹介しています。
連続する項目を表引きしたい場合、VLOOKUP関数だと何度も数式を作る必要があります。
例えば、次のような表。
VLOOKUP関数の場合、B12に設定した数式を、オートフィルで数式をコピーする場合、まず、検索値を列固定の複合参照にした上で、3番目の引数の「列番号」をコピー後に一つずつ修正する。
あるいは、COLUMN関数をつかって列番号に対応する必要があります。
慣れれば特に問題はないかもしれませんが、少々面倒です。
そこで、XLOOKUP関数をつかってみたらどうなるのか、確認をしてみましょう。
B12にXLOOKUP関数をつかった数式を設定します。
スピル機能によって、行方向には、数式をコピーする必要はありません。
列方向には、オートフィルで数式をコピーする必要があります。
これで、連続する複数項目の表引きを行うことができました。
COLUMN関数をつかわなくても大丈夫です。
では、XLOOKUP関数の数式を確認しておきましょう。
=XLOOKUP(A12,$A$2:$A$9,$B$2:$F$9,"",0,1)
最初の引数は、検索値。NOなのでA12を設定します。
2つ目の引数は、検索範囲。
検索値のある列なので、$A$2:$A$9。
オートフィルで数式をコピーする必要があるので、絶対参照を忘れずに設定します。
3つ目の引数は、戻り範囲。抽出したい列です。
連続する列なので、$B$2:$F$9。
オートフィルで数式をコピーする必要があるので、絶対参照を設定します。
ここをまとめて設定することができるので、VLOOKUP関数よりもわかりやすいかと思われます。
4つ目の引数は、見つからない場合。
見つからない場合は「””(ダブルコーテーション×2)」で空白とします。
5つ目の引数は、一致モード。
完全一致なので「0」を設定します。
最後の引数は、検索モード。
上から検索しますので、「1」と設定します。
NETWORKDAYS.INTL関数
読み方: ネットワークデイズ・インターナショナル
分類: 日付時刻
NETWORKDAYS.INTL(開始日,終了日,[週末],[祭日])
週末(曜日指定OK)と祝日を除いた日付間の日数を算出する
Facebookページに書いた、Excelの豆知識(Trivia)です。
6月8日
Excel。
範囲選択で、広い範囲の列を選択する場合は、最初の列番号をクリックして、最後の列番号のところで、Shiftキーを押しながらクリックすると選択できますね。
6月9日
Excel。
範囲選択で、広い範囲の行を選択する場合は、最初の行番号をクリックして、最後の行番号のところで、Shiftキーを押しながらクリックすると選択できますね。
6月10日
Excel。
シートの全部のセルを選択するのは、全選択ボタンをクリックします。
A列の左側と1行目の上の四角のボタンです。
6月11日
Excel。
足し算を使う記号は+。
引き算は-。
掛け算は×でなく*。
割り算は÷でなく/ですね。
6月12日
Excel。
2乗3乗のべき乗の記号は^(キャレット)ですね。
6月13日
Excel。
数学・算数のルールと同じで、()カッコの中が先に計算されます。
6月14日
Excel。
文字と文字も足す(合体)することもできます。
&を使うと、簡単にできますね。
A1:H5には、四半期の売上表があります。
このような表引きをするならば、VLOOKUP関数をつかうと対応できそうです。
しかし、B8:E8までにVLOOKUP関数をつかって、設定する場合、列番号が異なるので複数のVLOOKUP関数を設定する必要があります。
また、連続していれば、列番号に、COLUMN関数をつかうことで、対応することもできますが、今回は、表引きしたいデータが離れています。
これでは、COLUMN関数をつかうことができません。
そこで、VLOOKUP関数の列番号に配列数式をつかうことで、対応することができます。
B8にVLOOKUP関数の数式を設定します。
今回は、検索値が空欄時に発生する、#N/Aは考慮しておりませんので、考慮する場合には、IF関数かIFERROR関数で対応します。
またスピル機能によって、オートフィルで数式をコピーする必要はありません。
これで、複数の離れた列から表引きすることができました。
では、VLOOKUP関数を確認しておきましょう
最初の引数は、検索値です。A8を設定します。
この番号を変えることで、該当するデータを表引きします。
2つ目の引数は、範囲です。
A2:H5。
表引きの件数が1件なので、絶対参照は不要です。
3つ目の引数は、列番号です。
2つ目の引数で設定した範囲の左側から何列目のデータを抽出するのかという数値なのですが、離れた列になります。
そこで、
{2,4,6,8}
という配列数式にすることで、表引きすることができます。
通常の「()」ではなくて「{}」で囲むことで、配列数式にすることができます。
旧来は、先に表引きをすべて範囲選択して、数式の確定時にCtrl+Shift+Enterをすることで配列数式にすることができましたが、スピル機能によって、不要になりました。
数式の作成は、最初から「{}」で表引きしたい列番号を囲うだけです。
表引きしたい列は、2,4,6,8列です。
最後の引数は、検索方法です。
完全一致なので、FALSEを設定します。
NETWORKDAYS関数
読み方: ネットワークデイズ
分類: 日付時刻
NETWORKDAYS(開始日,終了日,[祭日])
日付の間の稼働日数を算出します
Excelは、時間計算が苦手だったりします。
例えば、夜勤とかで、21:00出勤で翌朝5:00に退勤した場合の勤務時間を算出したい場合は、「引き算」をつかえばいいはず。
ところが、結果は「#######」って。
なんで、どうして?
列幅を広げても、変わらない。
よく見るとメッセージが表示さている。
「負であるか、大きすぎる日付および時間は、######」って?
このようなケースのトラブルを回避する方法をご紹介しております。
積み上げ横棒グラフをつくるのは、簡単なのですが、出来そうで出来ないものがあります。
それは、合計値です。
それ以外にも、横棒グラフならではの縦軸を反転しないとグラフの元表と同じ順番になりません。
この2点を含めて、積み上げ横棒グラフに合計値を表示させるには、どうしたらいいのかを紹介いたします。
積み上げ横棒グラフ用に用意した表です。
E2の数式は、
=SUM(B2:D2)
といういたってシンプルな数式です。
E4までオートフィルで数式をコピーしています。
積み上げ横棒グラフをつくりますので、A1:E4を範囲選択します。
E列の合計列も忘れずに含めます。
挿入タブのグラフブロックにあり、積み上げ横棒グラフをつかって、積み上げ横棒グラフを挿入します。
4月売上から6月売上は、「積み上げ横棒」で、第2軸にチェックをいれます。
合計は、「集合縦棒」にして、第2軸は、何もしません。
第2軸が前面に、通常の第1軸が背面に設定されるので、4月から6月を前面にしたいので、第2軸にします。
合計はあくまでも、データラベルのためだけなので、背面の方がむしろ都合がいいというわけです。
OKボタンをクリックします。
右側に第2縦軸を表示させる理由は、左側の縦軸が、上から立川・渋谷・新宿の順番になっています。
グラフの元表と順番が上下逆です。
グラフの元表と同じようにする作業をするために必要になります。
これで、縦軸の順番は表と同じ、新宿・渋谷・立川になりました。
ただ、右側の第2縦軸はそのままなので、第2縦軸をクリックします。
第2縦軸は、横軸との交点を「自動」にします。
ここがわかりにくいので、注意ポイントです。
軸位置は、「軸を反転する」にチェックマークをいれます。
ここまでで、縦軸を表と同じ順番にすることができました。
合計の集合横棒グラフは、背面あるので、さわることができません。
そこで、書式タブのグラフの要素を「系列 "合計"」にします。
NEGBINOMDIST関数
読み方: ネガバイノムディスト
読み方: ネガティブバイノミアルディストリビューション
分類: 互換性
NEGBINOMDIST(失敗数,成功数,成功率)
負の二項分布の確率を算出します
Facebookページに書いた、Excelの豆知識(Trivia)です。
6月1日
Excel
範囲選択が基本中の基本ですよね。
6月2日
Excel。
範囲選択。複数のセルを選択するときは、Ctrlキーを押しながらクリックですね。
6月3日
Excel。
範囲選択で、広い範囲を選択するときは、Shiftキーを押しながらクリック技が便利ですね。
6月4日
Excel。
範囲選択の表現は、A1:B5と書きますが、これは、A1~B5までを範囲選択という意味ですね。
6月5日
Excel。
範囲選択で、行を選択する場合は、行番号をクリックすると行選択できますね。
6月6日
Excel。
範囲選択で、列を選択する場合は、列番号をクリックすると列選択できますね。
6月7日
Excel。
範囲選択で、隣接しているセル・行・列は、Ctrlキーを押すのではなく、ドラッグ操作ですね。
Excelは、単純に塗りつぶしたセル数を求められる関数は用意されていませんので、一発で求めることはできません。
どうしても数えたい場合には、オートフィルターをつかって抽出した件数を求めることはできますが、少し面倒です。
そこで、Excel VBAをつかって、指定した色で塗りつぶされているセルだけを求めるプログラム文をつくることにしました。
今回用意した表です。
B5とC4は数えたくないわけです。
そのため、単純に、セルが塗りつぶされているかどうかで、判断するわけにはいきません。
指定されている色を把握する必要があります。
では、Excel VBAのプログラム文を確認してみます。
Sub 指定した色を数える()
Dim kinmuhani As Range
Dim yasumi As Range
Dim Cell As Range
Dim i As Integer
Set kinmuhani = Range("B2:D5")
Set yasumi = Range("A7")
i = 0
For Each Cell In kinmuhani
If Cell.Interior.Color = yasumi.Interior.Color Then
i = i + 1
End If
Next Cell
Range("B7").Value = i
End Sub
まずは、実行してみましょう。
では、プログラム文を確認しておきましょう。
まずは、変数宣言です。
Dim kinmuhani As Range 範囲選択を代入させます
Dim yasumi As Range A7の塗りつぶしの色情報を代入します。
Dim Cell As Range セルをチェックする変数です。
Dim i As Integer 一致するセルの数を格納する変数です。
用意した変数に、指定した範囲選択を代入します。
Set kinmuhani = Range("B2:D5")
Set yasumi = Range("A7")
For Each文で範囲選択内を繰り返して、チェックします。
For Each Cell In kinmuhani
If Cell.Interior.Color = yasumi.Interior.Color Then
i = i + 1
End If
Next Cell
範囲選択内のセルの塗りつぶした色と、A7の塗りつぶした色が同じならば、変数iに1を加算します。
このようなプログラムを用意しておくと、単純な塗りつぶしされているセルを数えるには重宝するかと思います。
NEGBINOM.DIST関数
読み方: ネガバイノム・ディスト
読み方: ネガティブバイノミアル・ディストリビューション
分類: 統計
NEGBINOM.DIST(失敗数,成功数,成功率,関数形式)
負の二項分布の累積確率か確率密度を算出します
データを読み込んでみたところ
アルファベットの元号の列
ドットで区切られた数値の列
この2つの列から、「日付」にするには、どのようにしたら、いいのでしょうか。
色々な方法がありますが、
今回は、「DATEVALUE関数」という関数をつかってみました。
氏名が入力されている3つの表があります。
コピペするのも悪くはありません。
ただし、データ量が増えれば増えるほど、面倒です。
データを1列にまとめるならば、VSTACK関数がおすすめです。
G2に次の数式を設定します。
VSTACK関数は、列結合する関数なので、引数には、結合したい元の列を範囲選択するだけです。
このVSTACK関数は、スピル機能対応の関数なので、オートフィルで数式をコピーは不要です。
簡単に結合することができましたので、重複したデータを除きたいと思います。
重複データを除くには、データタブにある「重複の削除」をつかいたいところですが、スピル機能によって、ゴーストとして表示されています。
データそのものがあるわけではありません。
そのため、データタブの「重複の削除」はつかえません。
ここは、UNIQUE関数をつかうことで、重複データを除くことができます。
数式にUNIQUE関数を追加してみます。
=UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE)
これで、重複データを除くことができました。
UNIQUE+VSTACK関数のネストです。
修正した数式を確認します。
最初の引数は、配列。範囲なので、VSTACK関数を設定します。
2つ目の引数は、列の比較。FALSEと設定します。
「一意の行」を返すことができます。
3つ目の引数は、回数指定。
FALSEと設定します。
個別のアイテムをすべて返すことができます。
最後に、カタカナなので、五十音順。
つまり昇順にしたいのですが、スピル機能のゴーストである以上、並べ替えをつかうことはできません。
並べ替えの関数であるSORT関数をつかうことで、対応することができます。
数式をさらに修正します。
G2の数式は、SORT+UNIQUE+VSTACK関数のネストになりました。
=SORT(UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE),1,1,FALSE)
これで、並べ替えを行うことができました。
追加したSORT関数についても確認しておきましょう。
最初の引数は、配列。
範囲選択なので、UNIQUE関数の数式を設定します。
2つ目の引数は、並べ替えインデックス。
何番目の列を基準にするのかということです。
今回は、1列しかありませんので、1と設定します。
3つ目の引数は、並べ替え順序。
1なら昇順で2なら降順です。
五十音順なので1と設定します。
4つ目の引数は、並べ替え基準。
TRUEならば、列で並べ替えすることができますが、行での並べ替えなので、FALSEと設定します。
このように、最近追加された、関数を組み合わせてスピル機能と合わせてつかうことで、並べ替えや重複対応をした表をつくることができるようになりました。
作業効率もUPする、知っていると便利なショートカットキー。
Ctrl+L
テーブルの作成ダイアログ ボックスを表示
Ctrl+N
新規ブックを作成する
Ctrl+O
ブックを開く
Ctrl+P
印刷プレビューを表示
Ctrl+Q
クイック分析 オプションを表示
住所録があるのですが、都道府県順にしたいわけです。
今回は、東京都・神奈川県・埼玉県・千葉県・群馬県・栃木県・茨城県という順番にしたい。
まずは、クエリを使って、都道府県順にしてみます。
作成タブのクエリデザインをつかいます。
クエリは、テーブルの全てのフィールドをつかうことにします。
何かいい方法はないのでしょうか。
そこで、都道府県順のテーブルをつくります。
改めて、クエリをデザインビューで表示します。
確認が終わりましたので、あとは、T関東順番フィールドを非表示にして完成です。