FC2ブログ

シートの保護

2014.10.14 20:16|Excel実例
 請求書や見積書など、何度も使うテンプレート用のブックの数式や書式をいじられないようにするために、シートを保護することがあります。
 シートを保護するさいには、まず「保護しないセル」を選択し、[ホーム]タブ→「セル」グループの「書式」から「セルのロック」を解除します。

2014101401.png

 次に、同じ[ホーム]タブの「セル」グループ→「書式」から「シートの保護」を選択します。

2014101402.png

 「ロックされていないセル範囲の選択」にチェックが入っていれば問題ありません。なお、ロックされているセルを選択しても意味がないので、今回は「ロックされたセル範囲の選択」のチェックは外しています。
 シートの保護は、[校閲]タブからも行えます(ブックの保護、ブックの共有なども、[校閲]タブから行えます)。

 テンプレート用のブックを作る場合には、上記の方法の他にも、保存時の「ツール」→「全般オプション」で、「読み取り専用を推奨する」にチェックを入れ、読み取り専用ファイルにするとよいかもしれません。

2014101403.png

スポンサーサイト



金種表の作成

2014.10.13 22:10|Excel実例
 金種ごとの必要枚数を求める「金種表」を作成します。
 「QUOTIENT関数」と「MOD関数」を用います。

 QUOTIENT関数
 =QUOTIENT(分子,分母) で、分子を分母で割ったときの商の整数部を返します(小数部は切り捨て)。

 MOD関数
 =MOD(数値,除数) で、数値を除数で割ったときの余りを返します。

2014101301.png


 一万円札の単位は単純に、QUOTIENT関数で求められます。D3に「=QUOTIENT(C3,$D$2)」と入力します。D列に数式をオートフィルでコピーするので、分母は絶対参照を用います。

2014101302.png


 次に、五千円札以下の金種の必要数を求める式を記述します。
 C3をD2で割り、余った分をさらにE2で割った値がE3に入ります。なので、E3には、「=QUOTIENT(MOD($C3,D$2),E$2)」と記述します。
 オートフィルで数式をコピーして完成です。

2014101303.png

2014101304.png

簡易カレンダー

2014.09.28 21:36|Excel実例
092801.png



 薄い黄色の部分のみ入力すれば、あとは自動的に日付・曜日などが出てくる簡易カレンダーです。ここに出勤・休暇や時給計算を加えれば、パート・アルバイト用の賃金計算になるのですが、とりあえず簡易カレンダーのみにとどめました。祭日には対応していません。
 実際に入力するのは、「予定」以外の箇所です。

 開始日の欄には、年度・月度から求められる月の開始日を入力します。
 年度(B4)・月度(C4)が空白の時は空白を返すので、IF関数で OR(B4="",C4="") のTRUEの場合を「""」とします(ORは「または」の意)。そうでない場合には指定された日付に対応するシリアル値を返すDATE関数(DATE(年,月,日))を用いて、日付を返します。なので、D3には、=IF(OR(B4="",C4=""),"",DATE(B4,C4,1))と入力します。月の始まりは必ず1日ですので、DATE関数の「日」部分は「1」となります。
 終了日は、月によって28日(または29日)・30日・31日がありますので、「翌月1日から1を引いた日(DATE(年,月+1,1)-1)」と入力します。開始日と同じく、年度(B4)・月度(C4)が空白の時は空白を返すので、IF関数で OR(B4="",C4="") のTRUEの場合を「""」とします。F4には、=IF(OR(B4="",C4=""),"",DATE(B4,C4+1,1)-1) と入力します。
 なお、「~」の部分は、D4が空白のときは空白を返し、そうでない場合は「~」を返すようにしています(=IF(D4="","","~"))。
 また、開始日・入力日は、表示形式の「日付」を指定しています。

 フリガナ欄には、氏名(G4)に入力された文字のフリガナを返すようにします。フリガナを返すのは、PHONETIC関数を用います。
 G4が空白のときは空白を返すので、IF関数を用いて=IF(G4="","",PHONETIC(G4))と記述します。
 なお、G4のふりがなの設定を「ひらがな」にすると、H4に表示されるフリガナはひらがなになります。

 日付欄(B7:B37)は、表示形式をユーザー定義の「m"月"d"日"」としました(「9月1日」と表示されます)。B7は開始日なので「=D4」と記述します。B8には、「一つ前の日付が終了日(F4)以上であれば空白、そうでなければ一つ前の日付+1」という数式(=IF(B7>=$F$4,"",B7+1))を記述します(コピーするので、終了日を絶対参照にします)。フィルハンドルをドラッグ、もしくはダブルクリックで、B8の数式をB37までコピーします。

 曜日欄は、表示形式を指定して表示することもできますが、平日/休日欄で平日か休日かを区別するために、WEEKDAY関数CHOOSE関数を用いて表示します。

 WEEKDAY関数
 =WEEKDAY(シリアル値,種類)
 シリアル値には、日付の入力されているセルまたは日付を指定します。種類は1~3まであり、「1」は日曜日を「1」として返し、「2」は月曜日を「1」、「3」は火曜日を「1」として返します(月曜日が「0」)。何も指定しないと、自動的に日曜日を「1」として返します。

 CHOOSE関数
 =CHOOSE(インデックス,値1,値2,…)
インデックスでは、値で指定したリストの何番目を返すかを指定します。=CHOOSE(1,"あ","い","う","え","お")とすると、1番目の「あ」が返されます。
 WEEKDAY関数は曜日ではなく数字が返ってくるので、WEEKDAY関数の種類を「1」とした場合、日曜日を意味する「1」をさらにCHOOSE関数の値で「日曜日」と指定する必要があります。

 IF関数で、B7が空白のときは空白を返します。それ以外の場合は、WEEKDAY関数でB7の曜日の番号を返し、さらにその番号に対応する値をCHOOSE関数で返します。
 =IF(B7="","",CHOOSE(WEEKDAY(B7,1),"日曜日","月曜日","火曜日","水曜日","木曜日","金曜日","土曜日"))
 と記述します。

 平日/休日欄(D7)は、IF関数でB7またはC7が空白されば空白を返し、それ以外の場合は土曜日または日曜日であれば「休日」、それ以外は「平日」と返すようにします。=IF(OR(B7="",C7=""),"",IF(OR(C7="土曜日",C7="日曜日"),"休日","平日"))と記述します。
 数式をコピーし、平日/休日欄に条件付き書式を設定します。「セルの強調表示ルール」の「指定の値に等しい」で、「休日」に「濃い赤の文字、明るい赤の背景」を設定します。
092802.png

| 2019.11 |
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
カレンダー

10 | 2019/11 | 12
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

最新記事

カテゴリ

月別アーカイブ

検索フォーム

RSSリンクの表示

リンク

QRコード

QR

ページトップへ