まりふのひと

トップ > MAPS-77

次回の iPS-ntnで行いたいこと...

MAPS-77

≪目次≫


ExClipのアンインストール（削除）

  1. 以前、ogohnohito.hatenablog.jpに沿って（その後、内容を更新したので変わっていますが...）Exclip をインストールし、タスクバーにピン留めしました。
  2. タスクバーの Exclipボタンを削除します。
    1. タスクバーの Exclipボタンを右クリック ⇒ タスクバーからピン留めを外す。
  3. ExClipをインストールした場所（フォルダー）を開き、
  4. ExClipフォルダーを削除します。

Excel関数辞典の補足説明

P16．列ごとにデータを検索する（HLookup関数）

　「番外」にも書いたが、

  1. HLookup 関数は使ったことがない。
  2. 自分なら、テーブルを90度回転させ（貼り付けのオプションで［行と列を入れ替える］だけ）、VLookup関数を使う。
  3. テーブルの並べ替えが簡単に行えず、汎用性に欠けるため。絶対にVLookup に変えるべき‥‥ と思う。

上記の 2. のテーブルの行と列を入れ替え、前回、履修済みの VLookupで対応してみる。

P17．順位付けをする（Rank.EQ関数）

　特に補足することは無い...

P18．集計値を求める（SubTotal関数）

　この関数も使ったことが無い。データの間に集計行を入れるなんて‥‥　で、使うべきではないと思う。
代わりに［小計］を使うが、サンプルデータ付きのサイトがあったので、それで行ってみる。

  • 商品や項目ごとの小計を出したい（引用：なんだ！カンタン！Excel塾）kokodane.com

    1. 表は「商品」で並べ替えるので、「元の並び」に戻すのであれば、例えば「No」列を作り、連番を振っておく必要がある。
      • または、シートをコピーし、終わったら削除しておくのもよい。
    2. 表はリスト形式にしておかないと、ややこしいことになる。
      • 2行目に空行を作り、行間を小さくしておくのが まりふのひと流。

P19．エラーの場合に特定の文字を文字させる（IfError関数）

　特に補足することは無い...

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

Excel MAPS-77

≪目次≫

▲目次の先頭へ

いきさつ

　「実例でわかる！エクセル関数辞典」（インプレス）が手元にある。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 ‥‥ セルの値を１で割った余りが 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 ‥‥ VBA（Visual Vasic for Applications）ウィンドウの表示
  5. Ctrl＋G ‥‥ イミディエイト ウィンドウの表示
    1. イミディエイト ウィンドウに「Selection.SetPhonetic」を入力し、Enter。
  6. VBAウィンドウを閉じる。
  7. フリガナが表示されている‥‥ はず。
    • フリガナが正しくなければ、［ふりがなの編集］で修正する。

▲目次の先頭へ

P6b．数値や文字列を１っにつなげる

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でもう１つ 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:貼り付けのオプションで［行と列を入れ替える］だけ...

Excel／テキストボックスにセルの値を表示する

Excel MAPS-77

　昨日の草取りにで筋肉痛‥‥ と思ったら、今朝、腰にも来ている様子。まさか熱中症ではあるまいが頭もなんとなく重い...　日曜日でもあるし、家で静かにしていよう‥‥ と、「世界一やさしいExcel2016」で作った「店舗別売上集計表」を開いた。f:id:ogohnohito:20190707133643j:plain:w200:right
　気になっているのが、ドーナツグラフの中心に表示している「売上総合計/50,992円」。テキストボックスを貼り付けてあるので、『グラフデータが変更になっても、この金額は自動的に変わらない』点。

　ググっていて‥‥

  • テキストボックスにセルの内容を表示する（moug モーグ）
    www.moug.net

    ●概要●
    　テキストボックスなどの図形にセルの参照式を入力すると、指定したセルのデータがテキストボックスのテキストとして表示されます。
    変更される可能性のあるセルのデータをテキストボックスにも表示したい…といったときに便利です。
    ●詳細●
    1. ［挿入］タブの［図形］から［テキストボックス］をクリック
    2. シートの任意の位置をドラッグしてテキストボックスを追加
    3. テキストボックスを選択した状態で、数式バーをクリック
    4. 「=」を入力
    5. データが入力されているセルをクリック
    6. ［Enter］キーを押す

　「50,992」に単位を表示する‥‥ ところでつまづいたが、表示したい一心で行った。

  1. 1行を挿入する。
  2. 挿入した行（ここでは行番号「2」）の E列（売上合計の列．ここではセルE2）に、総売上合計を参照する計算式（"=E9"）を入力し、
  3. セルの書式設定で「単位」を入れる。（例えば、#,##0百円）
    f:id:ogohnohito:20190707143005j:plain:w512
  4. f:id:ogohnohito:20190707144505j:plain:right:w240テキストボックスは 2っ用意する。
  5. 上テキストボックスには "売上総合計" と入力する。（文字列，書式は任意）
  6. 下テキストボックスは、
    1. 下テキストボックスを選択する。
    2. 数式バーをクリックし、
    3. 「=」を入力し、セルE2をクリックする。
    4. ［Enter］。
  7. 表示が確認できたら、行2 は非表示にしておく。

　頭もスッキリしたので、明日の草刈りの準備をしよう...

≪参考≫
　グラフタイトルも同様の方法で、セルの値を表示することができる。

Excel／新発見！郵便番号から住所を入力するとPhonetic関数で郵便番号を得ることができる

Excel MAPS-77

≪目次≫

いきさつ

　「世界一やさしいExcel2016」（インプレス）の 第1章、第2章に続き、第3章の「住所入り会員名簿」を Excel2013 で、経験と勘と独断（KKD）で作成した。

テキストは全部読んでいないので、右図のとおり出来ているか否かは不明だが...

住所から郵便番号を作る

　「郵便番号から住所を入力する」で新発見した。

  • テキストでは『住所1（D列）に郵便番号を入力して変換する』とあるが、これはごく普通の方法。
  • 自分流は、左側（C列）が郵便番号（半角で入力）なので、
    1. D列にカーソルが移った時、
    2. Ctrl＋R ‥‥ これで郵便番号はコピーされる
    3. F2 ‥‥ これで編集モードになる
    4. ［変換］キー押下
    だが、Ctrl＋R で書式までコピーされるので、［データの入力規制］が「オフ（英語モード）」に変わってしまうので‥‥ 使えないかぁ～
  • Excel 郵便番号から住所
    plaza.rakuten.co.jp
    郵便番号を入力し、住所を表示させる場合
    　事前にIMEのプロパティ-辞書/学習タブにて、郵便番号辞書にチェックを入れておいて下さい。
    A1に =PHONETIC(B1) と関数を入力しておき、B1に郵便番号を入れて「変換」 させてみて下さい。A1、B1それぞれに表示されたと思います。

　エッ？　早速、試してみた‥‥ ら、いいねェ～
郵便番号は全角で表示されるの asc関数で半角にすればいい‥‥
　具体的には、

  1. セルC2に計算式 "=ASC(PHONETIC(D2))" を入力しておく。
  2. セルD2は、例えば "７４２－１５１３" で住所に変換し、Ctrl＋Enter*1 でセルの入力確定する。
  3. セルC2に "742-1513" が表示される。
  4. F2 ‥‥ 　編集モードにする
  5. 住所1の続きを入力する。

　新しい行を入力する時は、C列で Ctrl＋D で上のセルの計算式をコピーし、住所1を入力すればよい。

💡追記（2019-07-26）

  1. この方法では、追加入力した文字列が郵便番号列にも加えられる...
  2. 応急対応として、郵便番号は上8桁で切ることにした。
    　=Left(ASC(PHONETIC(D2)),8)

ふりがなを作るには（マクロ）

　Excel 以外から「氏名」を取ってくると、ふりがな が付かないので‥‥

  • Excel ふりがなを作成する by 超簡単なマクロ使用（未経験者OK！）
    plaza.rakuten.co.jp
    　早速、マクロを作成してみます。
    1. [ツール]－[マクロ]－[マクロ]　で　[マクロ]ダイアログ が表示されます。
    2. マクロ名：ふりがな作成
      マクロの保存先：作業中のブック
      として、[作成]ボタン をクリックすると、Microsoft Visual Basic の編集画面が表示されます。
    3. 下図のように、プログラム（命令書：マクロ.xls-Module1（コード）の見出しの画面）を一行だけ入力します。
      Selection.SetPhonetic
      Sub と End Sub の間に入力します。
    4. 入力したら、右上の [×]ボタン で、編集画面を閉じます。
    これで準備が出来ました

年齢を+1するには

　この表には「年齢」の列がある。それ自体は問題ないとして、「何年何月現在」の年齢かが、明確でない。最低限、それが判るシート見出しにすべきではないか‥‥ と思う。
　例えば、年齢を+1する関数は‥‥ ないのではないか。で、直ぐVBAに走るが、何かいい方法はないのだろうか...

  • 選択範囲の数値に同じ数値を追加したり演算する（引用：なんだ！カンタン！Excel塾）
    kokodane.com
    f:id:ogohnohito:20190809143852p:plain:w400

理解は横に置いておき、下記コードで対応した。

Public Sub 年齢加算()
    Selection.Value = Evaluate(Selection.Address & "+1")
End Sub

サンプルデータ

*1:確定後、セルを移動させない

Excelのエクスポートで作成したpdfファイルの表を、コピペでWord経由Excelにつなげた

Excel MAPS-77

≪目次≫

いきさつ

　「世界一やさしいExcel2016」（インプレス）の第1章「イベント会場担当表」に続き、第2章の「店舗別売上集計表」をサンプルを見ながら独断と偏見で、Excel2010 で作った。
右図のサンプルとは、

  1. 色は違う（Excelのバージョンの違いがある）が、大勢には影響ない...
  2. 「積み上げ棒グラフと区分線、合計の表示、100％積み上げ」を参考に、積み上げ棒グラフに合計金額を入れてみた。
    • 区分線（レイアウト・タブ ⇒［線］⇒ 区分線）を入れたいところだが、(1)3-Dグラフには入れられない。(2)合計金額表示の副作用で入れられない。
  3. 円グラフの代わりにドーナツグラフ*1 *2 にし、中心に売上の総合計をテキストボックスで入れた。
  4. 完成した「Sec2_店舗別売上集計表.pdf」（OneDrive）
    • 上のリンクをクリックすると、ブラウザー上で見ることができる。（たぶん）

　ところで、自分も作ってみたいが、データの入力が面倒‥‥ と言う人に朗報が...

pdfファイルの表をWord経由でExcelにつなぐ

　前述のpdfファイルは

  • Excel2010の［ファイル］⇒ 保存と送信 ⇒ PDF/XPSドキュメントの作成 ⇒［PDF/XPSの作成］から作成した。
  • Windows10にも 印刷で「Microsoft Print to PDF」を選択すれば pdfファイルを作成できるが、Excelにつなぐので、Excelで作成した方がいいのではないかと...（根拠レス）

　以下は Office2013 で試行錯誤した結果‥‥

  1. pdfファイルを Acrobat Reader で開く
    　上記の「Sec2_店舗別売上集計表.pdf」は OneDrive上にあるので‥‥
    1. リンクをクリックする。
    2. pdf が表示されたら［ダウンロード］をクリックする。
    3. 保存（ダウンロード）し、
    4. ダウンロードしたファイル（Sec2_店舗別売上集計表.pdf）を Acrobat Reader で開く。
  2. pdfの表をWordにコピペする
    1. 店舗売上高の表を選択し、クリップボードにコピーする。
  3. pdfの表をWordに貼り付け、Wordの表をExcelにコピペする
    1. Wordを起動し、
    2. 印刷の向きを「横」にする。（用紙をA4判 横置きにする）
    3. 貼り付ける。
    4. 表が出来たら、表全体を選択しコピーする。
    5. Excelを起動し、
    6. 貼り付ける。
      • 貼り付けのオプションは、［貼り付け先の書式に合わせる］

  • テキストでは、E列（売上合計）および G列（前年同期差）～ I 列（構成比）は数式と書式設定で作る。
  • 円グラフは計算した値で作ることになっているので、コピペした値で作れるか‥‥ 検証していない。

*1:Excel2013で内円の大きさを変えるには、系列 "構成比" を右クリック ⇒ データ系列の書式設定 ⇒［系列のオプション］⇒ ドーナツの穴の大きさ

*2:Excel2013でデータラベルを上下に表示するには、データラベルを右クリック ⇒ データラベルの書式設定 ⇒［ラベル オプション］⇒「区切り文字」を(改行) にする。

Excel／第1月曜日の日付の求める数式をグーグル先生に教わった

Excel MAPS-77

　21日金曜日の「世界一やさしいExcel2016 in 田布施町高齢者いきいき館」の時間に、内職して「第1月曜日を求める数式」を探した‥‥ が、数式を理解できなかった...

  • 第一月曜の日付をもとめるには（引用：エクセル関数リファレンス）
    　第一月曜日の日付を求めるには、まず、DATE関数を使用して、その月の1日に1週間（7日）を足した日を求めます。次に、WEEKDAY関数を使用して、その月の1日の前日、つまり前月の末日の曜日に対応する数値を求め、先ほど求めた日から引くことで第一月曜日の日付を求めることができます。
    =DATE(B1,B2,"1")+7-WEEKDAY(DATE(B1,B2,"1")-1,2)

帰宅後、改めてサンプルを作りながら試したら確かにできた。が、相変わらず 😵 の状態...

１．数式を日本語で書いてみる
  • 8日の日付（DATE(B1,B2,"1")+7）から、1日の前日（＝前月の末日：DATE(B1,B2,"1")-1）の戻り値（曜日コード）を引く

　これまで、Weekday関数の第2引数（種類）は殆ど省略していたが、今回は「2」となっている。
改めてググる‥‥

  • Weekday関数の種類
    • 上記のWeekday関数では「2」を使っているが、Excel2010以降では 11 も使える。

≪考察≫

  1. 引数2（種類）を「2」にしているのは、月曜日の戻り値を「1」にしたいため...
  2. 8日の日付を作っているのは、上記の戻り値の最低値が「1」であるため...

　改めて実例で検証した。

  • Weekday関数の種類別戻り値（抜粋）
種類 日曜日 月曜日 火曜日 水曜日 木曜日 金曜日 土曜日
1または省略 1 2 3 4 5 6 7
2または11 7 1 2 3 4 5 6
3 6 0 1 2 3 4 5
  • 試してガッテン
基準日 基準日＋7
B		 基準日前日の曜日 Weekdayの戻り値
D		 結果
B - D
2019/4/1 2019/4/8 日曜日 7 2019/4/1
2019/5/1 2019/5/8 火曜日 2 2019/5/6
2019/6/1 2019/6/8 金曜日 5 2019/6/3
2019/8/1 2019/8/8 水曜日 3 2019/8/5

　だれがこんな法則？ を見つけたのだろうか。自分なら、即、VBAで作るところだが...

第1月曜日以外の曜日もできるはずなので...

「はず」を Excel2010 で検証した。

  • 1行目にWeekday関数の第2引数（種類）を横に入力
  • セルA4以下にWeekday関数の第1引数（シリアル値＝日付）を入力
    • 必ず1日付けとする。
  • セルB4に数式：=(A4+7)-WEEKDAY(A4-1,B$1) を入力
  • 他のセルはセルB4の数式をフィルハンドルでコピー

≪結果≫
　第1火曜日～第1日曜日ともWeekday関数の第2引数（種類）を変えるだけで出来た。

Excel2013で世界一やさしい「イベント会場担当表」を作った（宿題は未検証）

Excel MAPS-77

≪目次≫

f:id:ogohnohito:20130520050353p:plain

いきさつf:id:ogohnohito:20190624072943j:plain:right:w120

　今日は「世界一やさしいExcel2016」in 高齢者いきいき館 の初日で、テキストをいただいた。

  • 「会」の名前の発表‥‥ は、ない...
  • テキストの進め方の話もない...

　静かなスタートの中、表紙を開いたら、特別付録「実例でわかる！ エクセル関数辞典」が‥‥ おっ！ すごい‥‥
目次を見たら、最初は「エクセル2016の‥‥」、バージョンが違うので参考にならないので飛ばす...
黙って始めるのも何なので、「P.16 のイベント会場担当表を解答を見ないで作りましょう～～～」と声掛けし着手した。

第１章「イベント会場担当表」を作る

　第１章「イベント会場担当表」の「この章で作るサンプル」を読んで‥‥

  • 日付が「2016/4月」になっている。当然、2019年版を作ることになろう‥‥
    • 第１週は第1月曜日、第2週は第2月曜日となるのか‥‥
  • ４週分を作るとなると、担当者はドロップダウンリストから入力するのがベストか‥‥

  • とりあえず、第1週分は単純に入力し、表のイメージを作り、名前を付けて保存した。

  • 2016/4月4日は本当に月曜日なの？
    1. セルA2は 2016/4/1 の入力に変え、セルの書式を "yyyy/m月" とした。
    2. セルA3は日にち ”4” を入力することとし、セルの書式を "0日" とした。
    3. セルB3は Weekday関数で求めることにし、セルの書式を ”aaa” とした。
    4. セルB3は、年月はセルA2のから取り、日はセルAから作り、曜日コードを求めることにし
      　”=WEEKDAY(DATE(YEAR($A$2),MONTH($A$2),A3))” に変更した‥‥ ら、「月」と表示された。
  • セルA4以下の日付、フィルハンドルで簡単に作れるが、第２週以下も同様にする必要がある。これも面倒なので、
    1. セルA4は、”=A3+1” とし、
    2. フィルハンドルでコピーした。
  • B列の塗りつぶしは、当然、条件付き書式でしょう‥‥
    1. セルB3～B9 を選択し、
    2. ［条件付き書式］⇒ 指定の値だけを含むセルの書式設定 で、
      f:id:ogohnohito:20190623111446p:plain:w512
      • 日曜日（=1）と土曜日（=7）の塗りつぶしを指定した。
      • A列は‥‥ 面倒だし、塗りつぶす必要も無いだろうから止めといた。

  • 「予約席」の空欄は灰色で塗りつぶす（2019-06-23 更新）
    1. セルG3～G9 を選択する。
    2. ［条件付き書式］⇒ 新しいルール
    3. 指定の値を含むセルだけを書式設定を選択し、下図を入力する。
    4. 「書式」⇒［塗りつぶし］で背景色を指定し、
    5. ［OK］で閉じる。
  • 第１月曜日の日にちを関数で求める（難宿題）
    • 授業中にグーグル先生に聞いたが、理解できなかった。
担当者をドロップダウンリストより入力する（2016-06-23 更新）
  1. 新しいワークシートを挿入し、
    • シート見出しを「担当者」とする。
    • セルA1 に ”担当者” を入力する。
  2. 第1週・シートの担当者（セルC3～G9）を担当者・シートのセルA2 にコピペする。
  3. 担当者・シートの
    1. B～E列のデータを、A列に移動する。
      • 選択してドラッグするか、切り取り・貼り付けする。
    2. A列を選択し、昇順に並び替える。
      • 「セルA1を選択して‥‥」並び替えると、漏れが発生する可能性があるので、A列を選択するのがよい。
    3. 重複データの削除
      1. A列を選択する。
      2. データ・タブ/データツール・グループの［重複の削除］をクリックする。
      3. ☑先頭行をデータの見出しとして使用する にして［OK］

  4. 第１週・シートの
    1. セルC3～G9 を選択し、
    2. データ・タブ/データツール・グループの［データの入力規制］⇒ データの入力規制... を選択する。
  5. データの入力規制・ダイアログボックス/設定・タブで、
    • 入力値の種類：リスト
    • ①元の値・ボックスにカーソルを移し、②担当者・シートをアクティブにする。③セルA2～A* まで（最後の担当者まで）を選択する。
    • 元の値・ボックスに「=担当者!$A$2:$A$**」と表示されたら、［OK］で閉じる。
  6. 任意の会場担当者を選択すると［▼］が出るはず...

日にちを自動設定する

　本来の趣旨（＝世界一やさしい）とは異なるが、第1週の年/月を変更すれば、第2～3週の日にちも自動的に変わるようにする...
≪前提≫

  1. セルA1は、第1週は ”1” を、第2週は ”2” ‥‥ を入力するものとし、表示はセルの書式設定で対応（例えば「第0週」）する。
  2. セルA2は、開催する年月，日にちは 1日の日付型（例えば 2016/4/1）で入力し、セルの書式設定は「yyyy/m月」とする。
  3. セルA3～A9にはシリアル値（日付）を入れる。セルの書式設定は「d日」とする。
  4. 第1週の初日（セルA3）は、セルA2の月の第1月曜日とする。

≪入力≫

  • セルA1：1
  • セルA2：2016/4/1
  • セルA3：=(A2+7)-WEEKDAY(A2-1,2)+(A1-1)*7 ‥‥ セルA2の「第（セルA1）月曜日」の日付
    • A2+7 ‥‥ セルA2の一週間先の日付
    • Weekday (シリアル値, 種類) ‥‥ 曜日コードを得る関数
      • シリアル値（日付）：A2-1 ‥‥ セルA2の前の日、ここでは 2016/3/31 となるf:id:ogohnohito:20190624084026p:plain:right:w240
      • 種類：2 ‥‥ 曜日コードが 月曜日が1、日曜日が7になる。
    • (A1-1)*7 ‥‥ 第2週には7、第3週には14、第4週には21 を加えるための数式
  • セルA4：=A3+1 ‥‥ セルA3 の翌日
  • セルA5～A9：セルA4 の数式をコピーして作成する

≪第2～4週作成後...≫

  1. セルA1：第2週は 2、第3週は3、第4週は4 に変更する。
  2. 第2～4週のシート
    1. 作業グループを作り、
    2. セルA2 に、第1週のセルA2をコピーする数式（=第1週!A2）に変更する。
    3. 作業グループを解除する。

世界一やさしいExcel2016のサイトより引用

　帰宅後、グーグル先生にインプレスのサイトを教えてもらった。

  • 世界一やさしいExcel2016（インプレス）book.impress.co.jp
    目次
    • 準備1　エクセル 2016のリボン機能
    • 準備2　エクセル 2016の画面構成
    • 準備3　エクセル 2016の起動と保存
    • 準備4　エクセル 2016の基本操作と関数
    • 第1章　見やすくてわかりやすい担当表を作ろう
    • 第2章　見やすいグラフ付きの集計表を作ろう
    • 第3章　データベースとして利用できる会員名簿を作ろう
    付録索引
    奥付
    特別付録　実例でわかる！ エクセル関数辞典
  • サンプルデータもあったので、ダウンロードした。