まりふのひと

世界一やさしいExcel付録の関数辞典で手こずったぁ~

≪目次≫

目次の先頭へ


いきさつ

 「実例でわかる!エクセル関数辞典」(インプレス)が手元にある。f:id:ogohnohito:20190831090028j:plain:right:h100世界一やさしいエクセル2016」の付録なので、、、、、、ザ~っと目を通して「読み終えた」ことにしていた。
 草刈りに出られない雨読の日が続いたのを機に、

  1. 破棄を目的に自炊し、
  2. Acrobat Readerで)結果の確認していて‥‥
  3. もう一度、復習しよう‥‥ と。

書くことにより理解も進み、記憶のリニューアルも出来た。が、膨大になり過ぎて...
目次の先頭へ


P2.基本的な集計関数

ここで使用する関数

合計(Sum)
  • 構文:SUM(数値1, [数値2], ...)
    • 引数:数値、セル参照、またはセル範囲を最大 255個まで指定する。
平均(Average)
  • 構文:AVERAGE(数値1, [数値2], ...)
    • 引数 :数値、セル参照、またはセル範囲を最大最大 255個まで指定する。
最小(Min)
  • 構文:MIN(数値1, [数値2], ...)
    • 引数:数値、数値配列、または数値を含む範囲を参照する名前かセル参照を最大 255 個まで指定する。
最大(Max)
  • 構文:MAX(数値1, [数値2], ...)
    • 引数:数値、数値配列、または数値を含む範囲を参照する名前かセル参照を最大 255 個まで指定する。

サンプルデータ

10月 11月 12月
渋谷店 3476 3096 3163
新宿店 3138 2914 3244
池袋店 4061 3382 3309
合計
平均
最小値
最大値

  • 課題
    • 平均は、書式で小数点以下第1位まで表示する。

番外

  • f:id:ogohnohito:20190902062313j:plain:right:w320「整数は小数点を表示せず、小数点位置を縦に揃える‥‥」昔やった手順を思い出すのに半日を要したぁ~
    1. 基本のセルの書式:#,##0.?
    2. 条件付き書式で、整数時の書式を変える。
      • 満たす数式:=Mod(セル,1)=0 ‥‥ セルの値を1で割った余りが 0 ならば‥‥
      • 書式 ⇒ 表示形式:#,##0_._0
  • 😵苦戦して解ったこと
    1. 上記表をExcel2013に貼り付け、
      1. 10月の合計~最大値を求める。
      2. 10月の渋谷店~最大値までを選択し、Ctrl+1
      3. 表示形式/ユーザー設定 を #,##0_._0 としても‥‥
      4. カンマ付き表示になるだけ...
      5. 表示形式/ユーザー設定 を #,##0.? にすると‥‥
      6. 平均はまともな表示に、整数値は小数点が付くが、小数点の右に空白なない。(正常?)

    2. 初心に帰り?‥‥上記表をExcelに貼り付け、貼り付け先の書式に合わせる
      1. 10月の合計~最大値を求める。
      2. 10月の渋谷店~最大値までを選択し、Ctrl+1
      3. 表示形式/ユーザー設定 を #,##0_._0 とすると‥‥
      4. 整数値は右にスペースが2っ入った。(予定どおり)
      5. 平均の書式は #,##0.0 で、表示が揃った。

    3. 今回、小数点が付くのは平均のみであるので、手抜きする‥‥
      1. 上記表をExcelに貼り付け、貼り付け先の書式に合わせる
      2. 数式を入力する。但し平均は、小数点以下第1位を四捨五入する。
      3. 表全体のセルの書式を #,##0_._0 とし、
      4. 平均のセルの書式を #,##0.0 とする。

はてなブログの表を Excel に貼り付けたままでは、入力したセルの書式が反映されないのは何故だろう...
  • Excel2016でも同じだった。
  • 一旦、書式をクリアすると正常に表示された。

割算の余り(剰余)を求める(Mod 関数)
  • 構文:MOD(数値, 除数)
    • 数値: 必ず指定する。 除算の分子となる数値を指定する。
    • 除数: 必ず指定する。 除算の分母となる数値を指定する。
小数点の位置を揃えるには(小数点以下の異なる桁数が混在する場合)
(引用:初心者のためのOffice講座)

目次の先頭へ

P4.数値を丸める

ここで使用する関数

四捨五入(Round)
  • 構文:ROUND(数値, 桁数)
    • 数値:四捨五入の対象となる数値を指定する。
    • 桁数:数値を四捨五入した結果の桁数を指定する。
      • 桁数 に 0 を指定すると、数値は最も近い整数として四捨五入される。
      • 桁数 に正の数を指定すると、数値の小数点以下 (右側) について、指定した桁数分が四捨五入される。
      • 桁数 に負の数を指定すると、数値の小数点の左側 (整数部分) が四捨五入される。
切り上げ(RoudUp)
  • 構文:ROUNDUP(数値, 桁数)
    • 引数:基本的には Roud に同じ。
切り捨て(RoudDown)
  • 構文:ROUNDDOWN(数値, 桁数)
    • 引数:基本的には Round に同じ。

サンプルデータ

税率 0.08
税抜価格 税抜×税率 四捨五入 切り捨て 切り上げ
商品A 2643
商品B 8751
商品C 1334

  • 課題
    • 税抜×税率 は、小数点以下2位まで、他は整数表示する。

番外

二捨三入に使える MRound関数

 Excelのヘルプを見ていて見つけた関数で『MROUND は、目的の倍数に丸められた数値を返します』とあった。

  • 構文:MROUND(数値, 倍数)
    • 数値:丸めの対象となる数値を指定します。
    • 倍数:切り上げまたは切り捨てて丸められた数値が、その倍数となるような数値を指定します。

更にググって‥‥

Excel関数で丸める(引用:総合案内所/五捨五超入と四分位数と放射線のサイト)

cals-web.net

◆二捨三入・七捨八入(スウェディッシュ・ラウンディング)
 1セントや2セント硬貨を廃止するが,5セント硬貨を残すような場合,二捨三入・七捨八入(スウェディッシュ・ラウンディング)が行われることがある。
スウェディッシュ・ラウンディングは MROUND(X,5) で求めることができる。
数式もとの数値丸めの幅丸めた結果
MROUND(41,5)41540
MROUND(68,5)68570
MROUND(94,5)94595

  • これまでは =ROUND(A2*2,0)/2 で行っていた。
     早速、試して機能を確認した。
Excelで JIS Z8401 による丸めが出来る?

≪いきさつ≫

  • 現役の頃、ステンレス鋼板1枚を重さ(質量)は、JIS G4310で行っていた。20190903185541
  • JIS G8401(数値の丸め方)の要旨
    www.gs.niigata-u.ac.jp

    条件 3
     小数第 ( n + 1 ) 位の数字が 5 で,小数第 ( n + 2 ) 位以下の数値が不明なとき,あるいは 0 であるときは,次の判断による。
    • 小数第 n 位が偶数のとき,切り捨てる。
    • 小数第 n 位が奇数のとき,切り上げる。
  • 昔使っていた Microsoft Accessの Round は JIS G8401に沿った丸め方であったが、Excelでは違うようだ...
    • 関数で銀行型丸め(最近接偶数への丸め)を行う方法(引用:エクセルの神髄)
      excel-ubara.com エクセルの関数で、ROUNDは四捨五入です。
      しかし、VBAでのRoundは、銀行型丸め(最近接偶数への丸め)で、Accessも銀行型丸めとなっています。四捨五入では、どうしても大きくなる傾向があるようです。
       銀行型丸めでは、.5の時は偶数に丸めます。計算式です。
        B2=ROUND(A1,0) C2=IF(MOD(ABS(A2),1)=0.5,EVEN(ABS(A2)-0.5)/SIGN(A2),ROUND(A2,0))
      • MODは、剰余を返す関数。
      • EVENは、最も近い偶数に切り上げる関数。
      • ABSは、絶対値を返す関数。
      • SIGNは、数値の符号を返す関数。
      上記では、整数で丸めています。
  • ExcelでJIS丸めを関数でしたいのですがどうすればできますでしょうか? (引用:Yahoo!知恵袋
    detail.chiebukuro.yahoo.co.jp

     仮にA1セルの値を整数に丸めるとしたら
      =(FLOOR(A1+0.5,2)+CEILING(A1-0.5,2))/2
    のようにします。
Floor関数のヘルプ
  • 書式:FLOOR(数値, 基準値)
    • 数値:丸めの対象となる数値を指定します。
    • 基準値:必ず指定します。 倍数の基準となる数値を指定します。
    • 解説:
      • 引数に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
      • 数値が正で基準値が負の場合、エラー値 #NUM! が返されます。
      • 数値の符号が正の場合、値は切り捨てられ、0 の方に近い値に調整されます。 数値の符号が負の場合、値は切り捨てられ、0 から離れた値に調整されます。 指定された数値が基準値の倍数と等しい場合は、数値は丸められずにその値が返されます。
Ceiling関数のヘルプ
  • 書式:CEILING(数値, 基準値)
    • 数値:必ず指定します。 丸めの対象となる数値を指定します。
    • 基準値:必ず指定します。 倍数の基準となる数値を指定します。
    • 解説:
      • 引数に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
      • 数値の符号に関係なく、切り上げられた値の絶対値は、数値より大きくなります。 指定された数値が基準値の倍数と等しい場合は、数値は丸められずにその値が返されます。
      • 数値と基準値がどちらも負の数である場合、値は小さい方 (0 から遠い方) の数値に切り上げられます。
      • 数値が負の数で、基準値が正の数である場合、値は大きい方 (0 に近い方) の数値に切り上げられます。

≪結果≫

  1. Yahoo!知恵袋さんの数式をデータを作って試したが、上手く出来た。
  2. しかし、数式を今は理解できない‥‥ ので、
  3. もし頼まれたらオリジナル関数で対応しよう。1行で済むし、検証済みだし...
    Public Function JISまるめ(argValue As Long) As Long
      JISまるめ = Round(argValue, 0)
    End Function

目次の先頭へ


P6a.フリガナ

Phonetic関数

  • 書式:PHONETIC(参照)
    • 参照: 必ず指定します。 ふりがなの文字列を含む 1 つまたは複数のセル参照*1 を指定します。

サンプルデータ

ヨミ
飯島
松田 育代
森川 太一

課題

  • ヨミ には、姓+名のフリガナを入れる。
     但し、インターネット(Excel以外)からExcelにコピペしたデータは、フリガナは無いので、姓・名を入力し直すか、後述の方法でフリガナを付ける必要がある。

番外

 下記サイトを参考に、ここでは使い捨てマクロ(VBA)でフリガナを作ってみます。

Excel ふりがなを作成する by 超簡単なマクロ使用(未経験者OK!)(引用:パソってますか?)

  1. 上表をExcelにコピペし、修整する。(修整は任意)f:id:ogohnohito:20190829170859j:plain:right:w240
  2. 姓・名の列のフリガナを表示する。(フリガナは無い)
  3. セルA2:B4(姓と名のデータ)を選択する。

  4. Alt+F11 ‥‥ VBAVisual Vasic for Applications)ウィンドウの表示
  5. Ctrl+G ‥‥ イミディエイト ウィンドウの表示
    1. イミディエイト ウィンドウに「Selection.SetPhonetic」を入力し、Enter。
  6. VBAウィンドウを閉じる。
  7. フリガナが表示されている‥‥ はず。
    • フリガナが正しくなければ、[ふりがなの編集]で修正する。

目次の先頭へ


P6b.数値や文字列を1っにつなげる

Concatenate関数

  • 書式:CONCATENATE(文字列1, [文字列 2], ...)
    • 文字列1:必須。結合する最初の項目です。 この項目には、テキスト値、数字、セル参照のいずれかを指定できます。
    • 文字列2:省略可能。結合するその他の文字列です。 最大で 255 個の項目、合計 8,192 文字を指定できます。

サンプルデータ

都道府県 市区 以下の住所 住所
東京都 世田谷区 北沢1-0-12
神奈川県 横浜市 港北区新横浜2-0-8
群馬県 前橋市 横手町3-0-1

課題

  • 都道府県市区以下の住所 を結合したものを 住所 に入れる。

番外

 Excel2016に朗報!

値を連結させる「&」「CONCATENATE」「CONCAT」の違いと「TEXTJOIN」
(引用:株式会社 デジタルワールド)

dw230.jpセルの値や文字列などをつなげる3つの方法
 &(アンド/アンバーサンド)
 CONCATENATE(コンカテネイト)
 CONCAT(コンカット)
  • 「&」と「CONCATENATE」はまったく同じで個々をつなげられます
  • CONCAT関数はEXCEL2016に追加された新しい関数で、「&」と「CONCATENATE」の機能プラス範囲が指定できるようになりました

文字を連結させる関数はEXCEL2016でもう1つ TEXTJOIN(テキストジョイン)関数が追加されました
 TEXTJOIN (区切り記号, 空の時の文字列を無視, 文字列1, 文字列2,・・・)
 元々「&」でつなげた方が楽だし「CONCATENATE」イラネと思ってましたが範囲が指定ができるなら「CONCAT」は使えます。
これでやっぱり「CONCATENATE」必要なくなります
 非常に有益な情報である。
個人的には、

  • 「&」を使い「Concatenate」は使ったことがない。(関数名を覚えられない)
  • 発音は‥‥
    Concatenate:コンキャテネイト,コンキャティネイト
    Concat:コンキャット Cat は「猫」と同じ発音なので...

目次の先頭へ


P7.文字列を別の文字列に置き換える

Substitute関数

  • 書式:SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
  • -文字列:置き換える文字を含む文字列、またはセル範囲。
  • -置換対象:文字列の中に検索文字列が複数あるとき、左から何番目の文字列を置き換えるかを指定する。省略するとすべての検索文字列が置換文字列に置き換わる。

サンプルデータ

氏名 旧部署名 新部署名
渡部 等 企画制作部営業課
田部 淳 総務部総務課
片岡 美穂 総務部人事課

課題

 旧部署名の2番目の「総務」を「経理」に変換したものを「新部署名」に入れる

番外

  1. 会社の組織替えで「総務部総務課」が「総務部経理課」に変わったため‥‥ と言うより、Substitute関数の「置換対象」の事例として無理やり作った感がある。
  2. 現実的には「総務課」を「経理課」に置換すべきで、「置換対象」で行うのはリスクが大きい。(たぶん)
  3. 更に、上手く置換できたので「旧部署名」を削除する‥‥ 訳にはいかないことを、理解すべきである。
    • 「新部署名」を「旧部署名」にコピーする場合は、値のみを貼り付ける必要がある。

P8a.数値の個数を求める

Count関数

  • 書式:COUNT(値1, [値2], ...)
    • 値1:必ず指定します。 数値の個数を調べる 1 つ目の項目、セル参照、またはセル範囲。文字列や空白は数えない
    • 値2:省略可能です。項目は最大 255、セル参照、またはセル範囲。

サンプルデータ

生物 地理 物理
飯島 聡 82 88
稲垣 涼子 41 66
佐藤 文弘 未受験 58 47
松田 育代 67 75
森川 太一 61 73 91
受験者数

目次の先頭へ

P8b.今日に日付を表示する

Today関数

  • 書式:TODAY()
    • 引数はない。

課題

 今日の日付を表示したいセルに =Today() を入力する。

P9a.現在の日付と時刻を表示する

Now関数

  • 書式:NOW()
    • 引数はない。

課題

 今日の日付と時刻をを表示したいセルに =Now() を入力する。

番外

  1. 時刻は、関数を入力した直後の 時分 となる。
    • 秒が表示されないのは書式のため。yyyy/m/d h:mm:ss とすると表示される。 
  2. 入力後、何もしないと、時刻は変わらない。
  3. F9 を押下(数式・タブ/計算方法・グループ の[再計算実行])すると、最新の時刻になる。

目次の先頭へ


P9b.指定した月数後の日付を求める

EDate関数

  • 書式:EDATE(開始日, 月)
    • 開始日: 必ず指定します。
    • :必ず指定します。 開始日から起算した月数を指定します。 "月" に正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返します。

サンプルデータ

製品番号 製品名 保証期間(年) 製造日 保証期限
A001 コードレス掃除機 3 2016/4/17
A002 シュレッダー 1 2016/3/6
A003 ヘッドフォン 2 2016/5/23

番外

  1. 初めて使う関数で、「エクスビレイション デイト」の略とあったが、「エクスビレイション」の意味を知りたくてググったがヒットしなかった。
  2. Excel2016関数名の読み方 一覧 ()www.helpforest.comには、「エクスピレイション・デイト」とあったので‥‥
  3. goo辞書を検索すると‥‥dictionary.goo.ne.jp
    • Expiration (期限の)満了,満期(日)とあった。
  4. まりふ流は ”=Date(Year(製造日)+保証期間(年),Month(製造日),Date(製造日)” 方式だが、f:id:ogohnohito:20190905084742j:plain:right:w320
    2020/02/29 の「12ヶ月後」で差が出た。
    • EDate():2021/02/28
    • まりふ流:2021/03/01

目次の先頭へ


P10.年数、月数、日数を計算する

DateDif関数

  • 書式:DATEDIF(開始日,終了日,単位)
    • 単位
      • "Y" :期間の年数です。
      • "M":期間の月数です。
      • "D":期間の日数です。
      • "MD":開始日から終了日までの日数です。 日付の月数および年数は無視されます。
      • "YM":開始日から終了日までの月数です。 日付の日数および年数は無視されます。
      • "YD":開始日から終了日までの日数です。 日付の年数は無視されます。

サンプルデータ

現場名 工事開始 工事終了 工期(ヶ月)
あおば町3丁目 2016/11/20 2019/3/4
わかば町5丁目 2016/10/24 2020/8/29
もえぎ町2丁目 2016/12/3 2020/5/16

番外

  • DateDif の Difは Difference(違い、差異)と思う...
DATEDIFの謎を追え(引用:なにぬねのーつ 2007/12/30)

www.niji.or.jp EXCELには経過年、経過月、経過日を計算するDATEDIFという関数があります。雑誌やWEB等でも紹介されていますのでご存知の方も多いと思います。一様に経過月の計算にバグがあるので使用する際は注意した方がいいとの記述があります。なぜ、バグが存在する関数をマイクロソフトがほっらかしにしておくのでしょう?調べていくとこの関数は非公開関数であること。ロータス1-2-3との互換のために作られた一時しのぎの関数であることがわかりました。
まとめ
 もしあなたがEXCELのDATEDIF関数を使いたいのなら実態を理解することは重要かもしれません。MD,YDオプションはほとんど使い物にならないこと→YM,Mオプションは月末の処理で多少誤差が生じること→Yオプションは閏日(2/29)の処理に問題があることがわかりました。もともと非公開関数ですからバグがあるからと言ってマイクロソフトに文句を言っても始まりません。癖を知って使えばいいのです。

YEARFRAC関数で日付も参照して年齢算出(引用:マイナビニュース 2019/02/06)


news.mynavi.jpYEARFRAC関数で日付も参照して年齢算出
 今回の例では、2019年2月4日現在において、1972年7月10日生まれの人の年齢を計算していますが、その時点ではまだ47歳になっていません。日付も参照して、正しい年齢を計算する方法は多数ありますが、ここでは開始日から終了日までの日数を年単位で表示する「YEARFRAC関数」を使ってみましょう。

  • YEARFRAC 関数
    • 書式:YEARFRAC(開始日, 終了日, [基準])
      • 開始日:必ず指定します。 起算日を表す日付を指定します。
      • 終了日:必ず指定します。 対象期間の最終日を表す日付を指定します。
      • 基準:省略可能です。 計算に使用する基準日数を示す数値を指定します。
        • 0 または省略:30 日/360 日 (NASD 方式)
        • 1:実際の日数/実際の日数
        • 2:実際の日数/360 日
        • 3:実際の日数/365 日
        • 4:30 日/360 日 (ヨーロッパ方式)
  • 試行f:id:ogohnohito:20190905094413j:plain:right:w320
    • 余談だが、法律上は「誕生日の前日に歳をとる」とのこと。
      YearFrank関数は「正味 何年生きているか」を計算するようだ。

目次の先頭へ


P11.条件を指定して数値を合計する

SumIf関数

  • 書式:SUMIF(範囲, 検索条件, [合計範囲])
    • 範囲:必ず指定します。 抽出条件を評価セルの範囲。
    • 抽出条件:必ず指定します。 数値、式、セル参照をテキスト、または対象となるセルを定義する関数の形式で抽出条件。
      重要:文字列条件や、論理記号または数学記号を含む条件は、二重引用符 (") で囲む必要があります。 条件が数値の場合、二重引用符は不要です。
    • 合計範囲:省略可能です。 追加は、範囲引数で指定されている以外のセルを追加する場合は、実際のセルです。 Excel が範囲引数で指定されているセルを追加する場合は、合計範囲を省略すると、(同じで、セルを検索条件が適用されます)。

サンプルデータ

時間帯 来店者数
6月3日 AM 66
6月3日 PM 58
6月4日 AM 77
6月4日 PM 68
午前の来店者数
午後の来店者数

目次の先頭へ


P12.条件に合うデータの個数を求める

CountIf関数

  • 書式:COUNTIF(範囲, 検索条件)
    • 範囲:必ず指定します。数えるセルのグループ。 範囲には、数値、配列、名前付き範囲、(数値を含む) 参照が入ります。 空の値とテキスト値は無視されます。
    • 検索条件:必ず指定します。個数の計算対象となるセルを決定する条件を、数値、式、セル参照、または文字列で指定します。

サンプルデータ

生物 地理 物理
飯島 聡 82 88
稲垣 涼子 41 66
佐藤 文弘 未受験 58 47
松田 育代 67 75
森川 太一 61 73 91
70点以上

目次の先頭へ


P13.指定した条件を満たす数値の平均を求める

AverageIF関数

  • 書式:AVERAGEIF(範囲, 検索条件, [平均範囲])
    • 範囲:必ず指定します。 平均する 1 つまたは複数のセル (数値、または数値を含む名前、配列、セル参照) を指定します。
    • 検索条件:必ず指定します。 平均の対象となるセルを定義する条件を数値、式、セル参照、または文字列で指定します。 たとえば、検索条件は 32、"32"、">32"、"Windows"、または B4 のようになります。
    • 平均範囲:省略可能です。 平均する実際のセルを指定します。 何も指定しないと、範囲が使用されます。

サンプルデータ

性別 年齢 購入金額
男性 10代 2300
男性 20代 3600
女性 30代 4700
女性 20代 1800 20代平均購入金額
女性 30代 8800
男性 50代 5200 30代平均購入金額
女性 20代 6300

目次の先頭へ


P14.条件ごとに値を使い分ける

If関数

  • 書式:IF(論理式, [値が真の場合], [値が偽の場合])
    • (Excel2013のヘルプには、引数の説明は載っていない)
論理式とは

excel-ubara.com Microsoftのヘルプによると、
IF関数
構文: IF(logical_test, value_if_true, [value_if_false])
  • logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
  • value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
  • value_if_false - [optional] The value to return when logical_test evaluates to FALSE.
この logical expression を訳しているので論理式になったのでしょう。
 論理式は、真(True)か偽(False)のどちらかの値になります。注意してもらいたいのは、真の値は1つではないことです。エクセルにおいては、偽(False)は0です、そして真(True)は0以外の数値です。
 偽(False)=0
 真(True)<>0
になります、通常は真(True)=1ですが、0以外は全て真(True)と判定されます。

サンプルデータ

売上目標 売上 目標
渋谷店 1300000 1418800
新宿店 1100000 963400
池袋店 1220000 1336600
  • 課題:売上が売上目標を超えている時は、目標に "達成" 、超えていない時は "未達成" と表示する。

番外

And関数
  • 書式:AND (logical1, [logical2], ...)
    • logical1:必須。 TRUE または FALSE に評価できるテスト対象の 1 つ目の条件。
    • logical2:省略可能です。 TRUE または FALSE のいずれかに評価される可能性があるその他の条件 (最大255条件)。

 AND 関数は、すべての引数が TRUE と評価された場合は TRUE を返し、1 つ以上の引数が FALSE と評価された場合は FALSE を返します。
たとえば、AND 関数を IF 関数の論理式引数として使用することにより、1 つの条件だけでなく、さまざまな条件をテストできます。

Or関数
  • 書式:OR(論理式1, [論理式2], ...)
    • 論理式1:必須。 TRUE または FALSE に評価できるテスト対象の 1 つ目の条件。
    • 論理式2:省略可能です。 TRUE または FALSE のいずれかに評価される可能性があるその他の条件 (最大255条件)。

 OR 関数は、いずれかの引数が TRUE と評価された場合は TRUE を返し、すべての引数が FALSE と評価された場合は FALSE を返します。

IF関数でのAND/OR関数の使い方
  • サンプルデータ

算数 国語 英語 合否1 合否2
飯島 聡 80 82 88
稲垣 涼子 41 66 92
佐藤 文弘 85 58 47
松田 育代 82 85 90
森川 太一 61 80 75

  • 課題1:算数、国語、英語の全科目が80以上の場合、合否1に「合格」、以外は「不合格」を入れる。
    1. If関数の例:=IF(算数>=80,IF(国語>=80,IF(英語>=80,"合格","不合格"),"不合格"),"不合格")
    2. And関数併用:=IF(AND(算数>=80,国語>=80,英語>=80),"合格","不合格")
  • 課題2:算数、国語、英語の1科目でも80以上の場合「合格」、以外は「不合格」を入れる。
    1. If関数の例:=IF(算数>=80,"合格",IF(国語>=80,"合格",IF(英語>=80,"合格","不合格")))
    2. Or関数併用:=IF(OR(算数>=80,国語>=80,英語>=80),"合格","不合格")

目次の先頭へ


P15.行ごとに数値やデータを検索する

VLookup関数

  • 書式:VLOOKUP(検索値, 範囲, 列番号, [検索の型])
    • 検索値:必須。検索の対象となる値。 検索する値は、範囲引数で指定したセル範囲の最初の列にある必要があります。
    • 範囲:必須。VLOOKUP が検索値と戻り値を検索するセル範囲。
       セル範囲の最初の列には、検査値が含まれている必要があります。 また、セル範囲には、検索する戻り値も含める必要があります。
    • 列番号:必須。戻り値を含む列の番号。範囲の左端の列が1 になります。
    • 検索の型:省略可。VLOOKUP を使用して、近似一致を検索するか、完全一致を検索するかを指定する論理値です。
      • 近似一致-1/TRUEは、テーブルの最初の列が数値またはアルファベット順に並べ替えられていることを前提とし、最も近い値を検索します。 この引数を省略した場合は、TRUE が指定されたものとみなされます。
      • 完全一致-0/FALSEを指定すると、最初の列の正確な値が検索されます。

サンプルデータ

検索型番 価格
型番 在庫数 価格
ML-101 19 860
ML-301 11 990
MX-406 34 1120
MX-606 23 2380

  • 課題:型番を検索して価格を表示する

P16.列ごとにデータを検索する

HLookup関数

  • 書式:HLOOKUP(検索値, 範囲, 行番号, [検索の型])
    • 検索値:必ず指定します。 テーブルの上端行で検索する値を指定します。 検索値には、値、参照、または文字列を指定します。
    • 範囲:必ず指定します。 データを検索する情報のテーブルです。 セル範囲への参照またはセル範囲名を使用します。
      • 範囲の上端行の列のデータは、文字列、数値、論理値のいずれでもかまいません。
      • 検索の型に TRUE を指定した場合、範囲の上端行の列のデータは、昇順で配置しておく必要があります。
    • 行番号:必ず指定します。 一致する値を返す、範囲内の行番号。 行番号に 1 を指定すると、範囲の最初の行の値が返され、行番号に 2 を指定すると、範囲の 2 番目の行の値が返され、以降同様に処理されます。
    • 検索の型:省略可能です。 HLOOKUP を使用して検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを指定する論理値です。
      • TRUE を指定するか省略した場合、近似値が返されます。 つまり、完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。
      • FALSE を指定した場合、HLOOKUP では完全に一致する値が検索されます。 完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。

サンプルデータ

 現役の頃には使ったことがないので、内容を理解するため、行・列番号まで入れた。

A B C D E F
1 納品番号 納品先
2
3
4 受注番号 101 102 103 104 105
5 商品名 ペンキ(赤) スプレー ペンキ(緑) ペンキ(黄) スプレー
6 単価 3500 1250 3500 3500 1250
7 納品番号 49976 49988 50012 50034 50128
8 納品先 神奈川 群馬 埼玉 茨城 静岡

  • 課題:セルB1 に入力した「納品番号」の「納品先」をセルD1 に表示する。

番外

 HLookup 関数は使ったことがないので、入力例を詳しく‥‥

  • 入力例:=HLookup(B1,B7:F8,2,False)f:id:ogohnohito:20190909160133p:plain:right:w480
    • 検索値 B1:検索する納品番号
    • 範囲 B7:F8:検索するテーブルの範囲。最上行が納品番号となる。
    • 行番号 2:結果を返す行で、納品先となる。
    • 検索方法 False:完全一致。セルB1 の納品番号が無ければ、#N/A となる。
      • テキストでは "False" となっているが、False は 0 なので...

 自分なら、テーブルを90度回転*2 させて、VLookup関数を使う。f:id:ogohnohito:20190909163521j:plain:right:w400
∵ テーブルの並べ替えが簡単に行えず、汎用性に欠けるため。絶対にVLookup に変えるべき‥‥ と思う。
目次の先頭へ


P17.順位付けをする

Rank.EQ関数

  • 書式:RANK.EQ(数値,範囲,[順序])
    • 数値:必ず指定します。 範囲内での順位 (位置) を調べる数値を指定します。
    • 範囲:必ず指定します。 数値の範囲の配列またはその範囲への参照を指定します。 範囲に含まれる数値以外の値は無視されます。
    • 順序:省略可能です。 範囲内の数値を並べる方法を指定します。
      • 順序に 0 (ゼロ) を指定するか、順序を省略すると、範囲内の数値が...3、2、1 のように降順に並べ替えられます。
      • 順序に 0 以外の数値を指定すると、範囲内の数値が 1、2、3、... のように昇順で並べ替えられます。
  • 解説:
    • RANK.EQ 関数では、重複した数値は同じ順位と見なされます。 ただし、数値が重複していると、それ以降の数値の順位がずれていきます。 たとえば、昇順に並べられた整数のリストがあり、そのリストに 10 が 2 度現れ、その順位が 5 であるとき、11 の順位は 7 となります (順位が 6 の数値はありません)。

サンプルデータ

氏名 点数 順位
藤崎 陽子 82
国分 啓太 61
佐藤 良一 77
馬場 明子 53
新田 亜美 94

目次の先頭へ

P18.集計値を求める

SubTotal関数

  • 説明:リストまたはデータベースの集計値を返します。 通常は、Excel デスクトップ アプリケーションの [データ] タブの [アウトライン] で [小計] を使用して、リストに集計行を挿入する方が簡単です。 作成した集計リストを修正するときは、SUBTOTAL 関数を編集します。
  • 書式:SUBTOTAL(集計方法,範囲 1,[範囲 2],...)
    • 集計方法:必ず指定します。 番号 1 ~ 11 または 101 ~ 111 を集計に使用する関数として指定します。 1 ~ 11 には手動で非表示にした行が含まれるのに対して、101 ~ 111 ではそれらを除外します。つまり、フィルター処理されたセルは常に除外されます。

集計方法
(非表示の値も含める)
集計方法
(非表示の値を無視する)
関数
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

  • 範囲 1:必ず指定します。 集計する最初の名前付き範囲または参照を指定します。
  • 範囲 2,...:省略可能です。 集計する名前付き範囲または参照を 2 ~ 254 個まで指定します。

  • 解説
    • 範囲 1、範囲 2などの範囲内に他の集計がある場合 (または入れ子になった集計)、 それらの入れ子になった集計は、二重計算を回避するために無視されます。
    • ##SUBTOTAL 関数では、列のデータ、つまり縦方向の範囲が集計されます。 行のデータ、つまり横方向の範囲を集計するための関数ではありません。

サンプルデータ

 これまで使ったこともないし、今後も使わないので、結果までも入れた。

A B C
1 3月の支出
2 日付 項目 支出(円)
3 12日 野菜、肉 1260
4 15日 パン 540
5 22日 飲み物 648
6 26日 食材 2500
7 食費合計
8 11日 ガス 3462
9 11日 電気 2890
10 27日 水道 3014
11 光熱費合計
12 支出合計

  • 課題:①、②に直上の縦計を、③に支出合計を求める。
    • ① =SUBTOTAL(9,C3:C6)
    • ② =SUBTOTAL(9,C8:C10)
    • ③ =SUBTOTAL(9,C3:C11) ‥‥ SubTotal関数を使っている①と②は除いて計算される。

番外

f:id:ogohnohito:20190914140229j:plain:right:w320 例えば右図にように(1)「分類」を入力し、(2)分類で並べ替え、(3)[小計]で集計すべきで、SubTotal関数を使わないと集計出来ないような表は「表の作り方に問題がある」と思う。

目次の先頭へ


P19.エラーの場合に特定の文字を文字させる

IfError関数

 IFERROR 関数を使用して、数式のエラーをトラップし、処理することができます。 IFERROR は、数式がエラーと評価された場合に指定した値を返します。それ以外の場合は、数式の結果が返されます。

  • 書式:IFERROR(値, エラーの場合の値)
    • :必ず指定します。 エラーかどうかをチェックする引数です。
    • エラーの場合の値:必ず指定します。 数式がエラー値と評価された場合に返す値を指定します。次のエラーの種類はが評価されます
       #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!。

サンプルデータ

3月期売上 6月期売上 売上増減
赤羽支店 240 300
荻窪支店 360 270
大森支店 なし 440

  • 課題:
    • 売上増減(6月期売上/3月期売上)を計算する。
    • 売上値がない場合(エラーとなった場合)は「確認中」と表示する。

目次の先頭へ


*1:連続した範囲

*2:貼り付けのオプションで[行と列を入れ替える]だけ...