今回は、IF関数～IMAGINARY関数までをご紹介しております。
IF関数
イフ
条件で分岐して異なる計算結果を返す
IF(論理式,真の場合[,偽の場合])
IFERROR関数
イフエラー
対象がエラーの場合に指定した値を返す
IFERROR(計算式,エラーの場合の値)
IFNA関数
イフエヌエー
結果が#N/Aの場合は指定した値を返す イフ・ノン・アプリカブルの略
IFNA(計算式,エラーの場合の値)
IFS関数
イフエス（イフズ）
1つまたは複数の条件で分岐して異なる計算結果を返す
IFS(論理式1,真の場合1[,論理式2,真の場合2…])
IMABS関数
アイエムアブス
複素数の絶対値を算出する
IMABS(複素数)
IMAGINARY関数
イマジナリー
複素数の虚数係数を取り出す
IMAGINARY(複素数)
会議資料はやっぱり見やすいものをリクエストされるわけです。
罫線の太さをカテゴリーの境目で変える『区切り線』にすれば、たしかに見やすくなるのですが、設定箇所が多いと、ただただ、「面倒」なわけです。
さらに、資料を作るたびに、「この間の資料よかったから、また同じようにお願い」なんて簡単にいわれたら…
そこで、Excel VBAでマクロを作っちゃいましょう。
例えば、次のような罫線が設定された表を作りたいわけです。
そして、外枠も太くなっています。
このように罫線を描くためのExcel VBAのプログラム文を確認してみましょう。
Sub 自動区切り罫線()
Dim i As Integer
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
Range("a1").CurrentRegion.BorderAround , xlThick
For i = 2 To lastrow
If Range("b" & i).Value <> Range("b" & i - 1).Value Then
Range("a" & i).Resize(1, 4).Borders(xlEdgeTop).Weight = xlThick
End If
Next i
End Sub
思った以上に少ない行数で作ることができます。
罫線がない表を用意して、実行しています。
このExcel VBAを実行してみると、ちゃんと罫線が描いてくれることが確認できます。
では、Excel VBAのプログラム文を確認しておきましょう。
お馴染みの変数宣言ですね。
Dim i As Integer
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
lastrowは、その表の最終行の行数を代入させています。
この回数分繰り返せば、必要な行数だけ繰り返し実行することができます。
Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
A1からのCurrentRegion。
つまり、表全体にたいして、Borders.LineStyleが格子罫線で、xlContinuousが線種を実線で描く。
この一行で、表全体を格子の実線を設定するという意味の行です。
つまり、シンプルな格子罫線を設定するだけならば、この一行を入れるだけでOKなわけです。
Range("a1").CurrentRegion.BorderAround , xlThick
こちらは、BorderAroundが外周でxlThickが太。
つまり、表の外周を「太い外枠」を設定しているわけです。
区切り線しなくても、この2行のマクロを設定するだけで、太い外枠の格子罫線の表を設定することが出来るわけです。
ただ、注意点があって、Excel罫線の設定する順番のルールは、Excel VBAでも同じです。
先に「太い外枠」を設定してから、「格子」を設定すると、先に設定した「太い外枠」は当然、「格子」に変わってしまいます。
太さは変更することができます。
xlThickは「太線」ですが、それ以外に、xlMediumが「中太線」。
xlThinが「細線」。
xlHairlineが「極細」と用意されています。
For i = 2 To lastrow
If Range("b" & i).Value <> Range("b" & i - 1).Value Then
Range("a" & i).Resize(1, 4).Borders(xlEdgeTop).Weight = xlThick
End If
Next i
For To Next文で、区切り線を設定しています。
For i = 2 To lastrow
表の最終行までを繰り返します。
If Range("b" & i).Value <> Range("b" & i - 1).Value Then
区切り線の条件はB列なので、今のB列のセルとその上のB列のセルの内容を比較します。
「<>（等しくない）」ならば、下記を実行するというIf～End If文です。
Range("a" & i).Resize(1, 4).Borders(xlEdgeTop).Weight = xlThick
Range("a" & i).Resize(1, 4)は、A列からResize(1, 4)で4列といういみで、A列からD列までが対象という意味で、Borders(xlEdgeTop)は、セル範囲の上側に罫線を設定します。
どのような線なのかが、Weight = xlThick。
すなわち、「太線」ということで、この一行は、A:Dの上側に太線を設定するという意味のプログラム文です。
このように、あまり長くないExcel VBAのプログラム文で、面倒な作業を済ませることができますので、少しずつ、現場でExcel VBAを使ってみるといいかもしれませんね。
Facebookページで【書いてみた】ワンポイントです。
9月21日
Excel。MOD関数。
読み方は、モッドで、除算した余りを算出
9月22日
Excel。MODE関数。
読み方は、モードで、最頻値を算出
9月23日
Excel。MODE.MULT関数。
読み方は、モード・マルチで、複数の最頻値を算出
9月24日
Excel。MODE.SNGL関数。
読み方は、モード・シングルで、最頻値を算出
9月25日
Excel。MONTH関数。
読み方は、マンスで、日付から月を算出
9月26日
Excel。MROUND関数。
読み方は、エムラウンドで、指定した数値の倍数で四捨五入
9月27日
Excel。MULTINOMIAL関数。
読み方は、マルチノミナルで、多項係数を算出します。
数値の和の階乗と数値の階乗の積との比
会議資料などで、合計を算出したら、だいたい平均値も算出することが多いかもしれませんが、その平均値だけでは、データ全体がどのような状況なのか把握できないわけです。
そこで、せっかく資料を作るなら、色々情報をプラスしてみるというのはどうでしょうか？
次のデータを用意しました。
F1に、C2:C11までのデータの平均値を算出しております。
F1の数式は、
=AVERAGE(C2:C11)
算出結果は、「66.3」。
この数値から、だいたい、60～70ぐらいなんだろうというイメージを持つと思いますが、もしかしたら、100点と10点の両極端にわかれているなんてこともありえます。
そこで、データの散らばり具合を数値化していきます。
データと平均値との差を算出します。
データと平均値との差を「偏差」と呼んでいます。
一列増やして、D列に偏差を算出してみました。
D2の数式は、
=C2-$G$1
オートフィル機能をつかって、数式をコピーしました。
次に、算出した偏差の正負を取り除くために、偏差を二乗します。
=D2^2
算出したら終わりではなくて、この偏差二乗を合算します。
この合算したした値を「偏差平方和」とか「変動」と呼んでいます。
ちょっと難しい感じになってきましたが、計算式自体は大したことをしていませんので、慌てないようにしましょう。
ゴールへ向けた、途中計算結果だと思いましょう。
データ数が多くなると、当然、この偏差平方和は、とんでもなく大きくなってしまうので、合っているのか不安になることがあります。
H2の数式は、
=SUM(E2:E11)
算出した「偏差平方和」をデータ件数で除算したものが、「分散」と呼ばれています。
分散の公式が、
分散＝偏差平方和÷データ件数なので、偏差平方和を算出したわけですが、最初から、分散のみを算出するのであれば、偏差二乗の平均値を算出しても、「分散」を算出することができます。
分散というのは、データの散らばり具合を表す一つの指標です。
ここでちょっと、統計のお話し。
実際の統計では、処理するデータが、データ全体を表しているとは限りません。
通常は、一部を取り出して処理します。
この取り出した一部のデータを「標本」と呼んでいます。
標本を対象とした分散は、データ全体を対象とした分散よりも若干大きくなるので、データの個数から1を減算させた、不偏分散を算出します。
わかりにくく感じるのは、算出している数値は「平均値からの距離」だということ。
もとに単位（点数）に戻してあげる必要があります。
先程、二乗したので、平方根を取ればいいわけですね。
SQRT関数をつかっていきます。
この算出した結果を、「標準偏差」と呼んでいます。
なんか聞いたことがあるかもしれません。
標本標準のH5の数式は、
=SQRT(H3)
分散の平方根です。
標本標準偏差のH6の数式が、
=SQRT(H4)
不偏分散の平方根です。
これで、平均値からプラスマイナス14.6点程度のバラツキがあることがわかりました。
Excelのグラフは、用途に合わせて様々なグラフを作ることができます。
今回は、グラフの復習ということ、4つをピックアップ
・Excel。グラフ縦軸の表示単位を”万”にしても、データラベルは”万”にしない方法
・Excel。横棒グラフに平均値の線を描くにはどうしたらいいの？
・Excel。集計横棒グラフの背景を平均値で色分けするグラフを作ってみよう
Excel。グラフ縦軸の表示単位を”万”にしても、データラベルは”万”にしない方法
表示単位を【単位：万円】というように表示させることは、簡単なのですが、少し問題があるのです。
＜続きはこちら＞
Excel。グラフ縦軸の表示単位を”万”にしても、データラベルは”万”にしない方法
https://infoyandssblog.blogspot.com/2017/12/exceldisplay-unit.html
Excel。横棒グラフに平均値の線を描くにはどうしたらいいの？
横棒グラフに平均値の線を描く方法をご紹介していきます。
＜続きはこちら＞
Excel。横棒グラフに平均値の線を描くにはどうしたらいいの？
https://infoyandssblog.blogspot.com/2018/01/excelhorizontal-bar-chart.html
Excel。ドットヒストグラムを使ってデータごとの頻度をグラフ化しよう
区間の頻度をグラフ化するのですが、個数だと少しわかりにくいグラフになってしまいます。そこで、登場するのが、『ドットヒストグラム』です。
＜続きはこちら＞
Excel。ドットヒストグラムを使ってデータごとの頻度をグラフ化しよう
https://infoyandssblog.blogspot.com/2018/02/exceldot-histogram.html
Excel。集計横棒グラフの背景を平均値で色分けするグラフを作ってみよう
平均値が変わっても自動的に塗り分けしてくれるグラフを作っていきましょう。
＜続きはこちら＞
Excel。集計横棒グラフの背景を平均値で色分けするグラフを作ってみよう
https://infoyandssblog.blogspot.com/2018/02/excelhorizontal-bar-chart.html
昔から使っているファイルで使用している関数をそのまま使うのもいいのですが、最近のExcelには、新しい関数が追加されています。
もしかしたら、使う使わないは別として、知っていればよかったというケースもあるかもしれません。
そこで、今回は、多重分岐でお馴染みのIF関数の大量ネストをIFS関数でおこなったらどうなるのか、確認していきます。
次の表を用意しました。
やりたいことは、D列にアルファベット評価を算出したいわけです。
その区分の条件が、90点以上がA。70点以上がB。60点以上がC。50点以上がDで、それ以外はEとしております。
Excel経験者だと、「VLOOKUP関数でやればいいじゃん。」って思うところですが、今回は、ご了承のほど。
IF関数を多重に使用したネストで数式を確認してみます。
D2の数式は、
=IF(C2>=90,"A",IF(C2>=70,"B",IF(C2>=60,"C",IF(C2>=50,"D","E"))))
ご覧のように、IF関数が4回使用することで、A～Eを算出しておりますが、結構大変ですし、面倒。
しかも、繰り返すことで、ミスも発生しやすくなります。
当然のことながら、区分の条件がさらに増えれば、それだけIF関数を繰り返す回数が増えるわけで、大変です。
そこで、新しく登場したIFS関数を使うことで、かなりスマートな数式で対応することができるようになりました。
今度は、IFS関数で、算出してみます。
D2をクリックして、IFS関数ダイアログボックスを表示しましょう。
論理式1に条件を設定して、それが真の場合なら、どうする？というのを繰り返していきます。
IF関数の中にIF関数をつくるよりも、条件を繰り返し設定していくだけで完成します。
それでは、D2にIFS関数でつくった、数式を確認しておきます。
=IFS(C2>=90,"A",C2>=70,"B",C2>=60,"C",C2>=50,"D",TRUE,"E")
とても、IF関数を繰り返すネストよりも、シンプルな数式で算出することができるようになりました。
このIFS関数。毎日毎日作るわけではないので、忘れがちになるのが、最後の「TRUE」です。
条件と完全合致ならば、いいのですが、今回のように、50未満は全部「E判定」なわけです。
つまり、『それ以外は～』の部分。
これがある場合には、最後の論理式に、「TRUE」を入力する必要があります。
Excel関数のスキルアップには、どうしても、「ネスト」というスキルは必要なので、昔から、IF関数にお世話になっていましたが、IFS関数も覚えておいて損はなさそうです。
なお、「値が真の場合」には、IF関数同様に、IF関数をネストすることもできます。
あまり意味がありませんが、このようにすることもできます。
=IFS(C2>=90,IF(B2="豊田","Y","A"),C2>=70,"B",C2>=60,"C",C2>=50,"D",TRUE,"E")
もし90点以上だったら、氏名が「豊田」だったら、「Y」にして、それ以外は、「A」のんままという数式を作ることもできます。
IFS関数も、アイディアによって、色々使える可能性がありそうですね。
Facebookページで【書いてみた】ワンポイントです。
9月14日
Excel。MIN関数。
読み方は、ミニマムで、最小値を算出
9月15日
Excel。MINA関数。
読み方は、ミニマムエーで、数値・文字列・論理値を含む最小値を算出
9月16日
Excel。MINIFS関数。
読み方は、ミニマムイフズで、条件により指定した範囲内の最小値を算出
9月17日
Excel。MINUTE関数。
読み方は、ミニッツで、時刻から"分"を算出する
9月18日
Excel。MINVERSE関数。
読み方は、マトリック インバースで、配列の逆行列を算出
9月19日
Excel。MIRR関数。
読み方は、エムアイアールアールで、定期キャッシュフローの修正内部利益率を算出
9月20日
Excel。MMULT関数。
読み方は、マトリック マルチ プリケーションで、2つの配列の行列積を算出
Excelテクニック and MS-Office recommended by PC training
見積書や請求書などのシートは、様々な数式を設定して使い勝手をよくするようにしています。
例えば、次の表で確認してみます。
=IF(B2="","",VLOOKUP(B2,$E$2:$F$7,2,0))
というIF+VLOOKUP関数の数式が設定されています。
ところが、何かのはずみで、数式を削除したら大変なことになります。
消したことがわからず、ファイルを閉じたりしたら、真っ青です。
そこで、「セルのロック」を解除することと、「シートの保護」を組み合わせて設定することで、大切な数式などを守ることができます。
タブキーでジャンプすれば？と思うかもしれませんが、見た目ではわかりません。
セルのロックを解除したセルを選択して、塗りつぶしをしてもいいのですが、アチラこちらに範囲選択があれば、かなり面倒な作業が発生します。
つまり、やりたいことは、
「セルのロックを解除したセルを塗りつぶしたい」
ということです。
～だったらセルを塗りつぶす。
ということなので、今回登場するのは「条件付き書式」です。
でも、どうやったら、セルのロックを解除しているかわかるのでしょうか？
セルの状況を知るには、関数があります。その関数は「CELL関数」です。
試しに、B2とC2の状況をB7とC7にCELL関数をつかって、セルのロックの状況を算出してみます。
なお、シートの保護は解除しております。
B7をクリックして、次の数式を設定します。手入力をお勧めします。
=CELL("protect",B2)
この数式を、オートフィル機能でC7にコピーします。
引数の、"protect"で、セルのロック状況を確認することができます。
セルのロックが解除されていると、「0」。解除されていないと「1」を算出してくれます。
これで、判断することができますので、条件付き書式を設定することができます。
該当する範囲を選択します。シート全体でもいいですが、今回は、A1:C7を範囲選択して、条件付き書式を設定していきます。
ホームタブの「条件付き書式」にある「新しいルール」をクリックします。
新しい書式ルールダイアログボックスが表示されます。
次の数式を満たす場合に値を書式設定のボックスに
=CELL("protect", A1)=0
というCELL関数の数式を設定します。
書式ボタンをクリックします。
セルの書式設定ダイアログボックスが表示されます。
このBLOGの記事を、
カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。
Exce。折れ線グラフの項目の日付が細かすぎなので、半年間隔にしたい
横軸を半年間隔で表示させて、判りやすくする方法をご紹介します。
＜続きはこちら＞
Exce。折れ線グラフの項目の日付が細かすぎなので、半年間隔にしたい
https://infoyandssblog.blogspot.com/2015/07/excel2013line-graph.html
Excel。あれれ。行を非表示にしたら、グラフがおかしくなっちゃった！
データが多い表だったので、一部を非表示にして、折れ線グラフを作成したら、なんと、摩訶不思議な事になったんだけど、どうしたらいいかな？
＜続きはこちら＞
Excel。あれれ。行を非表示にしたら、グラフがおかしくなっちゃった！
https://infoyandssblog.blogspot.com/2015/07/excellinegraph.html
スケジュールで今日の欄に色を付け、過ぎた日にもセルに色を付けたい
今日の欄のセルを自動的に塗りつぶしてもらえたり、過ぎた日にちには、それがわかるような色でセルを自動的に塗りつぶしたい。
＜続きはこちら＞
スケジュールで今日の欄に色を付け、過ぎた日にもセルに色を付けたい
https://infoyandssblog.blogspot.com/2015/08/excelschedule.html
VLOOKUP関数を設定すると、「#N/A」というエラーが表示されることがありますが、この対策方法を改めて確認しておきましょう。
VLOOKUP関数を設定した表を用意しました。
C2には、
=VLOOKUP(B2,$E$2:$F$7,2,0)
というVLOOKUP関数をつかった数式が設定されています。
なお、E1:F7の範囲には、絶対参照も設定されています。
商品名は、E1:F7の商品リストから検索されて、「鉛筆 B」と表示されています。
このC2の数式を、オートフィル機能をつかって、C6までコピーしてみます。
#N/Aというエラーが表示されました。
この「#N/A（エヌエー）」は、ノーアサインといって、「検索したけど、見つからないぞ！」というエラーです。
そりゃ、商品コードになにも入力されていませんから、見つけることはできません。
また、商品コードを入力していても、E1:F7の範囲に該当するデータがなくても、見つけることができないので、#N/Aを表示します。
そこで、商品コードが空白だからエラーが表示されるわけです。
なので、空白だったら、空白で表示して、そうでなければ、VLOOKUP関数を実施したいわけです。
「○○だったら○、そうでなければ○」というのは、分岐処理ですね。
分岐処理となれば、IF関数の登場です。
では、C2にIF+VLOOUP関数を設定していきます。
今回は、関数ダイアログボックスを使って確認してきます。
最初はIF関数ダイアログボックスを、表示させます。
論理式には、B2=””。
B2の「””（ダブルコーテーション×2連続）」は空白を意味します。
B2は空白ですか？と聞きます。
値が真の場合は「””（ダブルコーテーション×2連続）」。空白という結果を返します。
値が偽の場合には、VLOOKUP関数を設定します。
値が偽の場合のボックス内にカーソルがあることを確認したたら、名前ボックスが関数挿入ボックスに変わっていますので、▼をクリックして、直近に関数ダイアログボックスで設定した関数一覧が表示されますので、その中にVLOOKUP関数があれば、クリックします。
なければ、その他の関数からVLOOKUP関数を探します。
VLOOKUPをクリックすると、IF関数ダイアログボックスからVLOOKUP関数ダイアログボックスに変わります。
一瞬IF関数ダイアログボックスが消えてしまったように見えますが、数式バーを確認すると作成途中だとわかりますので、安心して作業を続けましょう。
ここからは、VLOOKUP関数を設定するだけです。
検索値には、B2。
範囲には、$E$2:$F$7
絶対参照を忘れずに設定しましょう。
オートフィル機能で数式をコピーする場合、だいたい範囲は絶対参照にしておいて、間違いはありません。
列番号は2
範囲の左から何列目のデータから抽出するの？ということなので、左から2列目なので、「2」
検索方法は、完全一致なので、「0」か「FALSE」を設定します。
OKボタンをクリックしたら、数式をオートフィル機能でコピーします。
C2の数式は、
=IF(B2="","",VLOOKUP(B2,$E$2:$F$7,2,0))
これで、「#N/A」に対応することができました。
IF+VLOOKUP関数以外にも、IF関数をつかうことで、エラー表示に対応することができます。
＜Facebookページ＞
Facebookページで【書いてみた】ワンポイントです。
9月7日
Excel。MAXA関数。
読み方は、マックスエーで、数値・文字列・論理値を含む最大値を算出
9月8日
Excel。MAXIFS関数。
読み方は、マックスイフズで、条件により指定した範囲内の最大値を算出
9月9日
Excel。MDETERM関数。
読み方は、マトリック ディターミナントで、配列の行列式を算出
9月10日
Excel。MDURATION関数。
読み方は、エムデュレーションで、証券に対する修正マコーレー係数を算出
Modified DURATION
9月11日
Excel。MEDIAN関数。
読み方は、メディアンで、中央値を算出
9月14日
Excel。MID関数。
読み方は、ミッドで、文字列の任意の位置から文字を取り出す
9月15日
Excel。MIDB関数。
読み方は、ミッドビーで、文字列の任意の位置から指定バイト数の文字を返す
Excelテクニック and MS-Office recommended by PC training
「～年」や「～円」と表示形式を設定することで、数値のままなので、数式にも使うことができる、表示形式。知っていると便利ですよね。
Excelだと、次のようなケースです。
C1の数式は、
=A1*B1
です。
A1には表示形式で「～円」と表示させています。
数式バーで確認すると、A1は300であることが確認できます。
では、どのような表示形式が設定されているのかを確認してみましょう。
G/標準"円" という設定をしております。
これで基本的に、Excelでは表示もされるし、計算にも使えるということで、問題はないわけです。
ところが、Accessでは、そう簡単にはいきません。
年を管理しているテーブルがあるとします。
あることを確認したいので、年1と年2を用意してあります。
同じように、2019～2021までの数値が入力されています。
このテーブルをつかって、最初は、年1に「～年」を表示するクエリを作っていきます。
クエリのデザインビューで年1を選択して、プロパティシートを表示します。
プロパティシートの標準タブにある書式に「0年」と入力するおt「0￥年」と表示が変わりますが、これで、「～年」と表示することができます。
AccessはExcelとことなり、表示したい文字を「”（ダブルコーテーション）」で囲むのではなく、「￥（半角の円マーク）」の後ろの文字を表示します。
「￥（半角の円マーク）」が挿入されているのは、そういうわけです。
では、実行して、データシートビューで確認してみましょう。
「～年」と表示されて事が確認できました。
続いて年2のフィールドも同じように「～年」と表示したいので、年1と同じように、プロパティシートの書式で設定して確認してみることにします。
設定が終わったので、データシートビューで確認してみましょう。
「年」が付いていません。ここがポイントです。
問題は、「データ型」なのです。
テーブル、年1と年2。見た目で、右揃え・左揃えになっていましたね。
ではテーブルをデザインビューでそれぞれのデータ型を確認してみましょう。
年1は、数値型ですね。
そして、年2は、短いテキスト型ですね。
昔はテキスト型といっていました。
要するに、数値型と文字型とデータ型が違ったわけです。
年1は、数値型だったのでそれにあった、「0￥年」という表示形式を設定したわけです。
年2にも、同じ設定をしましたが、年2は、文字型だったので、表示ができなかったわけです。
同じように「～年」をつけるならば、文字型に対応したい表示形式を設定する必要があるのです。
実のところ、Excelも同じなのですが、Excelは、「G/標準」というオールマイティーなものが用意してあるので、単純に後ろに「”円”」をつけるだけでよかったわけですが、Accessではデータ型がとても大切になりますので、確認して設定する必要があります。
それでは、クエリに戻って、年2を設定していきます。
プロパティシートの書式に「@￥年」と設定します。
それでは、データシートビューで確認してみましょう。
今回は、うしろに「年」を表示することができました。
このように、Excel以上に「データ型」に注意する必要がありますので、Excelのように上手くいかない時には、データ型を確認してみるといいかもしれませんね。