Excel。条件で分岐して異なる計算結果を返すのがIF関数です。
<関数辞典:IF関数>
IF関数
読み方: イフ
分類: 論理
IF(論理式,[値が真の場合],[値が偽の場合])
条件で分岐して異なる計算結果を返す
Excel・Word・PowerPoint・Accessのテクニックや研修ネタ・テキストを紹介しております。主に、講義先や現場レベルで質問があったものを中心に書いております。
IF関数
読み方: イフ
分類: 論理
IF(論理式,[値が真の場合],[値が偽の場合])
条件で分岐して異なる計算結果を返す
昇順や降順のボタンや並べ替えのボタンをつかって、手早く並べ替えができます。
ところが、帳票のような下側に合計行などの集計行があるときに、そのままボタンだけで処理をすると、合計行などの集計行が、とんでもないことになってしまいます。
その解決方法は、範囲選択なんですね。
検索値が1つならば、VLOOKUP関数などをつかうことで、手早く検索して抽出することができます。
では、次の表のように、2つの項目が抽出条件の場合、どのようにしたら、該当するデータを抽出することができるのでしょうか。
検索したい条件は、
F1:G2にあります。
検索条件は、
店舗名は 新宿
フロアーは 2
この2つの項目に合致する販売金額を G3に抽出したいわけです。
VLOOKUP関数の場合だと、店舗名とフロアーを合体させた列を作って…など、色々考えないといけません。
そこで、XLOOKUP関数をつかえば、手早く抽出することができます。
=XLOOKUP(G1&G2,B2:B7&C2:C7,D2:D7,"",0,1)
これで、1127と抽出することができました。
では、数式を確認します。
最初の引数は、「検索値」には、G1&G2 と設定します。
検索値が2つありますので、「&(アンパサンド)」で結合します。
2つ目の引数は、「検索範囲」。
ここも検索範囲が2つありますので、「&(アンパサンド)」で結合させます。
よって、B2:B7&C2:C7
3つ目の引数は、「戻り範囲」は、抽出結果なので、D2:D7 と設定します。
4つ目の引数は、「見つからない場合」です。
見つからない場合は「””(ダブルコーテーション×2)」の空白と設定しました。
5つ目の引数は、「一致モード」です。VLOOKUP関数でいうところに、検索方法に該当します。
完全一致で対応させたいので、「0(ゼロ)」と設定します。
最後の6つ目の引数は、「検索モード」。
先頭データから検索するかなど、検索方法を設定することができます。
大きなデータではないので、「1」の先頭から最終行へ向けて検索する方法にしました。
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月19日
Excel。
OR関数
読み方: オア
分類: 論理
OR(論理式1,[論理式2],…)
複数の条件のいずれか1つを満たすかどうかを調べる
5月20日
Excel。
PDURATION関数
読み方: ピーデュレーション
分類: 財務
PDURATION(利率,現在価値,将来価値)
目標価値になるまでの投資期間を算出します Period DURATIONの略
5月21日
Excel。
PEARSON関数
読み方: ピアソン
分類: 統計
PEARSON(配列1,配列2)
ピアソンの積率相関係数を算出する
5月22日
Excel。
PERCENTILE関数
読み方: パーセンタイル
分類: 互換性
PERCENTILE(配列,率)
0%以上100%以下の データの百分位数を算出する
5月23日
Excel。
PERCENTILE.EXC関数
読み方: パーセンタイル・ エクスクルーシブ
読み方: パーセンタイル・ エクスクルード
分類: 統計
PERCENTILE.EXC(配列,率)
0%より大きくて100%未満の データの百分位数を算出します
5月24日
Excel。
PERCENTILE.INC関数
読み方: パーセンタイル・ インクルーシブ
読み方: パーセンタイル・ インクルード
分類: 統計
PERCENTILE.INC(配列,率)
0%以上100%以下の データの百分位数を算出します
5月25日
Excel。
PERCENTRANK関数
読み方: パーセントランク
分類: 互換性
PERCENTRANK(配列,x,[有効桁数])
数値の位置を百分率で算出します
Excel。
HYPGEOMDIST関数
読み方: ハイパージオムディスト
読み方: ハイパージオメトリックディストリビューション
分類: 互換性
HYPGEOMDIST(標本の成功数,標本数,母集団の成功数,母集団の大きさ)
超幾何分布の確率を算出します
連続している日付ではない表があります。
一応、5日置きではありますが、このデータから、マーカー付き折れ線グラフをつくってみます。
A1:B5を範囲選択します。
挿入タブのグラフにある、「折れ線/面グラフの挿入」のマーカー付き折れ線グラフをクリックします。
表示ない、日付が横軸に表示されていることがわかります。
たしかに、この表ならば、マーカー付き折れ線グラフではなく、集合縦棒グラフの方がマッチしているとは思います。
ですが、マーカー付き折れ線グラフにしたいけど、表示されているデータがない日付は見えないようにしたいわけです。
このトラブルの対応方法ですが、次のように処理をすれば解決します。
横(項目)軸をクリックします。
書式タブの選択対象の書式設定をクリックします。
右側に、軸の書式設定作業ウィンドウが表示されます。
Excelが気を使って、自動的に処理をしたというのが原因でした。
このように、日付が横軸の場合、表に無い日付を表示することもありますので、その時は、「テキスト軸」に変更することで対応できます。
いままで、☑をセルに表示させるには、Excel VBAをつかうとか、あるいは、文字で☑を入力するという方法を使っていました。
ところが、Excel Insider版の挿入タブに、チェックボックスが登場しました。
一刻も早く、Microsoft 365のExcelにも追加してほしいぐらいです。
なんといっても、使い勝手がいい。
設定する方法は、セルをクリックして、チェックボックスをクリックする。
その後、オートフィルでコピーしてもいい。
その逆で、範囲選択をしておいて、チェックボックスをクリックすれば、範囲選択内のセルすべてにチェックボックスを挿入することができます。
そして、☑が何件あるのかも、COUNTIF関数を使うことで対応することができます。
B6には、
=COUNTIF(C2:C4,TRUE)
という数式をつくることで、☑が何件あるのかを手早く算出することもできます。
しかも、マクロ有効ブックで保存しなくてもいいんです。
そのまま、xlsxファイルとして保存することができます。
知らないと全く設定できない。「グラデーションの設定方法」
図形やグラフの塗りつぶしを、オリジナルのグラデーションで設定する方法をご紹介しています。
データの平均値を算出するには、AVERAGE関数をつかえば、算出することができます。
次の表をつかって確認します。
=AVERAGE(B2:B11)
という数式を設定しました。
オートSUMボタンにある平均をつかってもいいですね。
では、上位20%の平均値を算出したい場合は、どのようにしたらいいのでしょうか。
考え方として、上位20%なのかを調べる必要があります。
20%圏内ならば、平均値の対象になります。
そこで、登場するのがPERCENTILE.INC関数です。
この関数は、データ全体の上位何%(今回は20%)のポジションの値を算出することができます。
=PERCENTILE.INC(B2:B11,80%)
と数式をつくりました。
これで、上位20%の数値が算出できました。
その結果、88.2
あとは、この結果以上の値のデータだけの平均値を算出すればいいというわけです。
ところで、
=PERCENTILE.INC(B2:B11,80%)
の2番目の引数の「率」に注意が必要です。
上位20%だから20%と設定しがちですが、上位20%というのは、80%の位置にあたりますので、100-上位率 の数値を設定します。
F2に上位20%の平均値を算出します。
F4の88.2以上が平均値算出の条件です。
条件付きの平均値ということで、使う関数は「AVERAGEIF関数」です。
=AVERAGEIF(B2:B11,">="&F4)
データも88.2以上のものは、89と93しかないので、その平均値は91ですので、合致しております。
今回は、説明の為、F4に一度、上位20%の数値を算出しましたが、一つの数式にしても、問題はありません。
=AVERAGEIF(B2:B11,">="&PERCENTILE.INC(B2:B11,80%))
HYPGEOM.DIST関数
読み方: ハイパージオム・ディスト
読み方: ハイパージオメトリック・ディストリビューション
分類: 統計
HYPGEOM.DIST(標本の成功数,標本数,母集団の成功数,母集団の大きさ,関数形式)
超幾何分析の累計確率か確率密度を算出する
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月12日
Excel。
OCT2HEX関数
読み方: オクトトゥヘックス
読み方: オクタルトゥヘキサデジマル
分類: エンジニアリング
OCT2HEX(数値,[桁数])
8進数を16進数に変換する
5月13日
Excel。
ODD関数
読み方: オッド
分類: 数学/三角
ODD(数値)
数値を奇数に切り上げる
5月14日
Excel。
ODDFPRICE関数
読み方: オッドエフプライス
読み方: オッドファーストプライス
分類: 財務
ODDFPRICE(受渡日,満期日,発行日,初回利払日,利率,利回り,償還価額,頻度,[基準])
最初の利払期間が半端な利付債の現在価格を算出します
5月15日
Excel。
ODDFYIELD関数
読み方: オッドエフイールド
読み方: オッドファーストイールド
分類: 財務
ODDFYIELD(受渡日,満期日,発行日,初回利払日,利率,現在価値,償還価額,頻度,[基準])
最初の利払期間が半端な利付債の利回りを算出します
5月16日
Excel。
ODDLPRICE関数
読み方: オッドエルプライス
読み方: オッドラストプライス
分類: 財務
ODDLPRICE(受渡日,満期日,最終利払日,利率,利回り,償還価額,頻度,[基準])
最後の利払期間が半端な利付債の現在価格を算出します
5月17日
Excel。
ODDLYIELD関数
読み方: オッドエルイールド
読み方: オッドラストイールド
分類: 財務
ODDLYIELD(受渡日,満期日,最終利払日,利率,現在価値,償還価額,頻度,[基準])
最後の利払期間が半端な利付債の利回りを算出する
5月18日
Excel。
OFFSET関数
読み方: オフセット
分類: 検索/行列
OFFSET(参照,行数,列数,[高さ],[幅])
基準のセルからの相対位置を指定する
データを読み込んでみたところ、日付が「.(ドット)」で区切られた日付でした。
要するに、文字型だったわけです。これを日付型にしたいわけです。
単純にデータ型を変更しても、日付にはわかってくれません。
そこで、クエリをつかって、日付型として使えるようにします。
作成タブのクエリデザインをつかいます。
CDate関数をつかえば、日付型にすることができます。
「.」であったとしても、日付型に変わってくれるのではと考えたわけです。
そしてもう一つは、テスト2という演算フィールドをつくりました。
テスト2: CDate(Replace([日付],".","/"))
演算フィールドの説明は、後に回すとして、実行してみましょう。
CDate関数だけでは、対応は無理のようです。
つまり「.(ドット)」のままではダメなようです。
そこで、
テスト2: CDate(Replace([日付],".","/"))
Replace関数をつかって、「.(ドット)」を「/」に置換させてから、CDate関数で日付型に変更しました。
これで、日付型として対応することができました。
Replace関数は、置換することができる関数です。
なお、Replace関数で、「.(ドット)」を「/」に置換するだけでは、文字型のままで、日付型になりません。
HYPERLINK関数
読み方: ハイパーリンク
分類: 検索/行列
HYPERLINK(リンク先,[別名])
他のドキュメントへのリンクを作成します
作業効率もUPする、知っていると便利なショートカットキー。
Shift+F7
類似語辞典作業ウィンドウを表示する
Shift+F8
方向キーを使用して、隣接しないセルまたは範囲を選択範囲に追加する。
Shift+F9
作業中のワークシートのみ「再計算」する
Shift+F10
コンテキスト メニューを開きます。
Shift+F11
ワークシートを新規追加する
Shift+F12
上書き保存 Ctrl+Sと同じ
最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。
AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。
この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。
=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)
長いですが、シンプルな関数しかつかっていません。
だけど、C2:C6という範囲選択が何度も登場してきます。
数式を説明すると、
SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)
SUM関数で、合計を算出します。
その値から、MAX関数で最大値をMIN関数で最小値を減算します。
この値を
COUNT(C2:C6)-2 の算出結果で除算します。
COUNT関数で数値の件数を算出することができます。
「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。
この数式で算出して、何ら問題はありません。
今回は、この数式をLET関数で、算出してみます。
LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。
ただ、どのように使うのか、わかりにくい関数といわれています。
そこで、比較的わかりやすい 範囲選択 を名前に設定してみます。
D9にLET関数をつかって数式をつくります。
数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。
では、この数式の説明をします。
LET関数の最初の引数は、名前。
プログラムでいうところに変数。あるいは、名前の定義です。
今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。
2つ目の引数は、式。
式となっていますが、数式でもいいし、範囲でも指定できます。
繰り返し設定するところを設定するといいので、「C2:C6」としました。
この名前と式を複数設定することも出来ます。
3つ目は、計算式。
SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2
C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。
このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。
ネットワークなどでお馴染みの16進数。
16進数を10進数に変換することができるのが、HEX2DEC関数です。
B2には、
=HEX2DEC(A2)
という数式を設定してあります。
オートフィルで数式をコピーします。
A2がaと入力されていて、B2の結果は10と算出されていることが確認できます。
A6のFFは、255と算出されています。
このように、16進数を10進数に変換することができます。
普通並べ替えは、行単位での並べ替えを行うようになっています。
では、列単位で並べ替えるには、どのようにしたらいいのでしょうか。
データを読み込んだ後に、オートフィルターをつかって、空白のセルを、手早く抽出したい。
単純な作業ですが、単純な作業な上、毎回毎回だと、面倒になってきます。
そこで、Excel VBAで、オートフィルターをつかって、空白のセルを抽出するプログラム文を用意しておくことにしました。
使うのは次の表です。
オートフィルターを設定して、空白のセルだけを抽出する設定は、☑マークのオンオフが地味ですが、オンオフを切り替えないといけないので、面倒です。
そこで、Excel VBAで次のようなプログラム文を作ってみました。
Sub オートフィルター空白抽出()
Range("a1").AutoFilter field:=3, Criteria1:="="
End Sub
たった一行です。
では実行してみましょう。
たった一行だけですが、用意しておくと便利です。
プログラム文のポイントは、
Range("a1").AutoFilter field:=3, Criteria1:="="
Criteria1:="="
Criteria1は、条件ですが、空白のセルを意味する比較演算子をつかった「”=”」です。
なお、
Criteria1:=""
でも、空白セルを抽出することができます。
Criteria1:="<>"
と、「”=”」のように、比較演算子をつかって設定する必要があります。
HSTACK関数
読み方: エイチスタック
分類: 検索/行列
HSTACK(array1,[array3],…)
水平方向に配列を2つの配列に積み重ね(スタック)する
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月5日
Excel。
NOW関数
読み方: ナウ
分類: 日付時刻
NOW()
現在の日付と時刻を算出します
5月6日
Excel。
NPER関数
読み方: エヌパー
読み方: ナンバー・オブ・ピリオド
分類: 財務
NPER(利率,定期支払額,現在価値,[将来価値],[支払期日])
元利均等返済における支払回数を算出します
5月7日
Excel。
NPV関数
読み方: エヌピーヴイ
読み方: ネット・プレゼント・バリュー
分類: 財務
NPV(割引率,値1,[値2],…)
キャッシュフローに基づいた正味現在価値を算出します
5月8日
Excel。
NUMBERSTRING関数
読み方: ナンバーストリング
NUMBERSTRING(数値,書式)
数値を漢数字に変換する
5月9日
Excel。
NUMBERVALUE関数
読み方: ナンバーバリュー
分類: 文字列操作
NUMBERVALUE(文字列,[小数点記号],[桁区切り記号])
特定の地域に依存しない方法で文字列を数値に変換する
5月10日
Excel。
OCT2BIN関数
読み方: オクトトゥビン
読み方: オクタルトゥバイナリ
分類: エンジニアリング
OCT2BIN(数値,[桁数])
8進数を2進数に変換する
5月11日
Excel。
OCT2DEC関数
読み方: オクトトゥデック
読み方: オクタルトゥバイナリデジマル
分類: エンジニアリング
OCT2DEC(数値)
8進数を10進数に変換する
入力欄のすべてにデータが入力されているかをチェックしたい場合には、件数を算出する関数をつかいます。
3回目まで終わっているならば、E列に○と表示させるようにしています。
まず、判断が必要になりますので、IF関数をつかいます。
そして、IF関数の論理式には、3回目、すなわち、3件データがあるかどうかを調べればいいわけです。
そこで、E2には、次の数式を設定しました。
=IF(COUNT(B2:D2)=3,"○","")
あとは、オートフィルで数式をコピーします。
件数を算出するわけですが、すべて数値なので、COUNT関数をつかっています。
もし、文字列とか、数値や文字列が混在している場合にはCOUNTA関数をつかいます。
=IF(COUNTA(B2:D2)=3,"○","")
その結果が3と等しいか判断させればいいというわけです。
条件付きの件数なので、COUNTIF関数やCOUNTIFS関数をつかってもいいですが、条件がシンプルなので、IF関数とCOUNT関数、あるいは、IF関数とCOUNTA関数で対応することができます。
途中計算など文字や数値を非表示にすることができます。これで、列や行を非表示にしなくても大丈夫です。
途中計算などを表示したまま印刷すると、ちょっとカッコが悪いというか、お客さん相手の資料なら、途中計算の結果を表示することはできませんね。
列や行を非表示にしてもいいのですが、Excelファイルを見せる場合は、列や行を非表示にすると、列や行番号がかけているのがわかります。
つまり、「非表示」にしたことがわかってしまいます。
そこで、表示形式をつかうことで『セル内の文字を非表示』にすることができます。
氏名など、個人情報保護上、表示することができない場合があります。
今回は全角の「*(アスタリスク)」に置き換えて表示したい場合の方法をご紹介します。
B2に、数式を設定します。
=LEFT(A2,1)&REPT("*",LEN(A2)-2)&RIGHT(A2,1)
最初の文字は、LEFT関数をつかいます。
LEFT(A2,1)
これで、左から1文字を抽出することができます。
これを「&(アンパサンド)」で結合します。
伏字の部分の数式が
REPT("*",LEN(A2)-2)
REPT関数の中にあるLEN関数から説明します。
LEN関数は文字数を算出する関数です。
その値から前後1文字分ずつ、つまり「-2」することで、伏字の数がわかります。
そして、REPT関数ですが、
REPT関数は、指定した文字列を指定した回数繰り返す関数です。
最初の引数の文字列に「”*”」とします。これが、伏字の「*」です。
2つ目の引数は、繰り返し回数です。
先程説明しましたLEN関数で繰り返し回数を算出しています。
最後に、RIGHT関数をつかって、右側の1文字を抽出し、結合することで、完成します。
HLOOKUP関数
読み方: エイチルックアップ
分類: 検索/行列
HLOOKUP(検索値,範囲,行番号,[検索方法])
横方向の表からデータを検索して抽出する
Facebookページに書いた、Excelの豆知識(Trivia)です。
4月28日
Excel。
NORMINV関数
読み方: ノーマルインバース
分類: 互換性
NORMINV(確率,平均,標準偏差)
累積確立から正規分布の数値を逆算する
4月29日
Excel。
NORM.INV関数
読み方: ノーマル・インバース
分類: 統計
NORM.INV(確率,平均,標準偏差)
累積確立から正規分布の数値を逆算する
4月30日
Excel。
NORMSDIST関数
読み方: ノーマルスタンダードディスト
読み方: ノーマルスタンダードディストリビューション
分類: 互換性
NORMSDIST(z)
標準正規分布の累積確率を算出する
5月1日
Excel。
NORM.S.DIST関数
読み方: ノーマル・スタンダード・ディスト
読み方: ノーマル・スタンダード・ディストリビューション
分類: 統計
NORM.S.DIST(z,関数形式)
標準正規分布の累積確率を算出します
5月2日
Excel。
NORMSINV関数
読み方: ノーマルスタンダードインバース
分類: 互換性
NORMSINV(確率)
累積確立から標準正規の数値を逆算する
5月3日
Excel。
NORM.S.INV関数
読み方: ノーマル・スタンダード・インバース
分類: 統計
NORM.S.INV(確率)
累積確立から標準正規の数値を逆算する
5月4日
Excel。
NOT関数
読み方: ノット
分類: 論理
NOT(論理式)
指定した条件が成立しないことを調べる