EXCEL関数の勉強 ５
住所録・年賀状・賃金計算・アルバイト手配・請求書
１．WORDで住所録を作る
WinXPの場合
- Wordを起動します。
- [フアイル(Ｆ)]メニュー[新規作成(Ｎ)]をクリックします。
- 右側の作業ウインドウから、「このコンピュータ上のテンプレート」をクリックします。
- 現れた「テンプレート」ダイアログから、[差し込み印刷]タブをクリックします。
- アドレス帳１〜３から、何れかを選択します。
- 作業ウインドウで、「新しいリストの入力」にチエックを入れ、作成ボタンをクリックします。
下図は「エントリー１〜４」の例です。
- 記入が終わったら、My Documents 内 My Data Sourcesに保存してください。
但し、ファイルの種類: Access database file で、保存形式は「Microsoft Office アドレス(*.mdb)」に限られます。このドキュメントは、Microsoft Access のデータベースです。Accessでないと開けないようなので、編集に難儀しそうです。
下図は、その一覧表です。(未だ数行ですが)
マイクロソフトWordの住所録テンプレートは、下記のスタイルになっていることが分かりました。
２．EXCELで一般的な住所録を作る
下の図は、上記テンプレートに従って「姓」と「名」を分離して、EXCELで作りました。
(但し、会社関係はカット)
作成手順
- [姓]と[名]に名前を入力します。そうして、「ふりがな」を付けてみましょう。(会社、お役所では重要な作業です。)
- [姓]を選択します。
- [書式]メニュー →[ふりがな]→[設定]をクリックします。(まだ、この段階ではふりがなは表示されません。)
- 再度[書式]メニュー →[ふりがな]→[表示/非表示]をクリックするか[編集]をクリックすると表示されます。
私のWinXPの場合[姓]と[名]別々に行いました。 - 最初の「山田太郎」にふりがなが付いたら、「山田太郎」を選択して、太郎の右下の 「フィルハンドル」で必要な行数にコピーします。(ふりがな付きの「山田太郎」のコピーが沢山できました。)
- 折角コピーした分を[Del]キーで消します。文字は消えても書式は残るのです。
- 氏名欄に次々と名前を入力します。終ったら、[書式]メニュー →[ふりがな]→[表示/非表示]をクリックすると一発で表示されます。
- 姓名欄にふりがなが付いたら、未だ空欄の山田太郎の右欄[D4]に関数PHONETICを
「 =PHONETIC(B4:C4) 」の形で埋め込みます。数式バーに直記入でも、また、数式バー[fx]をクリックして、関数を選択し、 範囲を[B4:C4]とドラッグしてもできます。(WinME の場合は書式設定ツールバーの[fx]ボタンから) - [D4]セルに「ふりがな」ができたら、オートフィル機能で、下にドラッグすると全部に付きます。
- 姓名欄のふりがをの削除します。方法は、[書式]メニュー →[ふりがな]→[表示/非表示]をクリックすると削除されます。
- 郵便番号を「半角数字で入力します。(通常は数字は自動的に「半角化」しますが[ー]が付くと全角のまま表示されてしまうので、言語バーを 「直接入力」に切り替えます。
- 住所記入欄で空白になっている[F4]から[F13]まで選択します。
- [Ctrl]キーを押しながら[R]きーを押します。意味は、左の文字列を右にコピーしなさい。です。
([E列]と[F列]が全く同じになりました。) - 言語バー(IME)の「般」をクリックして、「人名/地名」に切り替えます。
- 次に、１行ごとに郵便番号を選択して、スペースキーの右の「変換」キーを押して住所を選択します。
住所禄の並べ替え
[データ(D)]メニュー →[並べ替え(S)]で下記が選択できます。大人数の場合に威力を発揮します。
１．５０音順 ２．郵便番号順（＝県名順になります。）３．元に戻しかったらNo 順です。
オートフィルタで「選択範囲」をしぼる」
若し、役所、銀行、企業等で、「性別、年齢、生年月日等個人情報満載のデータでしたら、絶対に必要になります。
項目欄を選択し、[データ(D)]メニュー →[フィルタ(F)]→[オートフィルタ(F)]を選択すると、選択した１つ項目または、全項目にボタンがつきます。
特に、備考欄に、級友、趣味友達、親戚などを記入しておくと、ふるいにかけることができて便利です。「フィルタ」の使用法はこちらをご覧ください。
３．年賀状用の住所録
上記住所録を訂正して使用する
姓名の[ふりがな]を[書式]メニュー →[ふりがな]→[表示/非表示]をクリックして、カットします。
姓と名が別々のセルに有っても差し支えありません。姓名の間にカーソルを置いて[Del]か[Backspace]キーで縮められます。
気になったら下記方法で同一セルに纏めます。
姓名の右側に、列を挿入し、２列に分かれた姓と名を「 =CONCATENATE(姓,名) 」で一つのセル内に纏め、２列表示を削除します。
テンプレートを利用する。
フォルダから開く
(前述による方法がありまあすが）WindowsXPでは：[マイドキュメント]→[My Data Sources] フォルダ内の、[Address.xls]を開くと下記雛形が現れます。
(Word2000では、ファイル(F)]メニューから[新規作成]で、[その他の文書]の中の[Address.xls]からになります。)
この様式の必要欄に書き込み住所録を完成させておきます。
作成した住所録の保存形式
[CSV 形式]で保存しても、[xls形式]保存と同等の働きをします。
CSV形式にする場合には「フアイルの種類」で「 CSV(カンマ区切り)(*.csv) 」を選択します。保存したフアイルを開くとき、
開くアプリケーションを指定しないと、開けません。この場合EXCELを指定します。
(EXCELで作った[CSV 形式]住所録は、他の市販年賀ソフトでも使えます。現に私はＭＳ以外の年賀ソフト２〜３で利用しました。)
はがき宛名印刷
Wordの「はがき宛名印刷ウイザード」で作る
Word[ツール(T)]メニュー →[はがきと差込印刷(E)]→[はがき印刷(J)]「宛名/文面の選択」 で宛名面の作成をクリックすると、「はがき宛名印刷ウイザード」が起動します。 用法については、こちらを参照ください。
Word2002-2003の場合、簡素化されて、ウイザードも短くなり、１分位で終了します。失敗してもすぐやり直せます。
注意すべき点は、ウイザードの「差込み印刷を指定します」で、「他の住所録フアイル」にチエックします。 参照で、作成した住所録フアイルを選択します。これで、表示される筈です。
その他
No、ふりがな、性別などの情報記入
上記雛形に[No],[氏 名(半角スペース入れ)],[連名],[敬称],[ふりがな],[性別]・・・の順で記入すると、あとあと「並べ替え」や整理に便利です。
多人数の住所録を作成する場合には、入力規則で、各列ごとに全角文字使用か、半角英数使用するかを設定しておくと、能率がアップします。
作例です。
[D2]は関数[ =CONCATENATE(B2,C2) ]で、氏名を一つのセルにまとめました。
「ふりがな」は、[D2]からは取れません。[B2][C2]から上記の手段で取り入れます。
差出人電話番号は、ウイザード差出人記入欄で漢数字で入力し、編集で、
記号から電話機を選んで頭に付けました。横書きを回避するためです。
各枠線は、テキストボックスですので、自由に編集できます。ただ、差出人電話番号には苦労させられます。「差出人欄は、テキストボックス３個を使った方が良さそうです。」
何故か、連名が表示されません。有料のソフトを使いなさいの意味かも知れません？。それなら、それで、テキストボックスで入れる手段がありますが。
IF関数に[HOUR関数,FLOOR関数(MINUTE関数)]をネストして勤務時間の計算
その前に、[FLOOR] 関数についての予備知識が必要です。
FLOOR 関数
書式：「 FLOOR(数値,基準値) 」
数式パレット注釈：指定した数値よりも０に近い数値に丸めて値を返します。
数値注釈 ：数値には丸めたい数値を指定します。
基準値-注釈 ：基準値には計算の最小単位となる数値を指定します。
MS-ヘルプより
指定された基準値の倍数のうち、最も近い値に数値を切り捨てます。
基準値 倍数の基準となる数値を指定します。
引数に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
数値と基準値の符号が異なる場合、エラー値 #NUM! が返されます。
数値の符号に関係なく、切り捨てられた値の絶対値は、数値より小さくなります。
指定された数値が基準値の倍数と等しい場合は、数値は丸められずにその値が返されます。
用語：丸め
コンピュータは２進数です。2進数では表現できない小数(例えば10進数の0.1があります)を扱うとき、通常、コンピュータは適当な”2進数”に変換します。 このように近似値に変換することを「丸め」といいます。
CEILING 関数
書式：「 CEILING(数値,基準値) 」
[FLOOR] 関数＆[CEILING] 関数の使用例
上の、注釈では、未だなんのことやら分かりかねると思います。下図をご覧ください。
FLOOR は床、CEILING は天井を意味します。指定された[基準値]の倍数には、0倍、1倍、2倍、3倍等が、
対象とする[A]列の[数値]に合わせて自動的に設定されます。図では、夫々、[0],[50],[100],[150],[200]が
戻り値になっており、端数は切り捨てられてしまいます。
この戻り値の夫々をを「床」としたとき、[A]列の数値（この場合個数）
が何個積み上げられたかを[C列]に数式で示しました。
[CEILING] 関数の場合は、基準値の0、1、2、3、4倍の[0],[50],[100],[150],[200]
の夫々を天井にした場合、後何個で天井に届くかを[E列]に数式で示しました。
非常勤職員の賃金＋残業手当計算
[IF],[HOUR],[FLOOR],[MINUTE]と場合によっては[OR],[VALUE]関数を使います。
[FLOOR] 関数の[規定値]を[15(分)]に設定して、就労時間数を求めます
残業時間には、「 =IF(OR(B4="",E4=""),"",HOUR(F4-E4)+FLOOR(MINUTE(F4-E4),15)/60) 」と[OR]関数を使用し、
２つの「条件式」を組み入れました。(また、昼食時30分も賃金に入れてあります。除きたかったら「正規時間」
から[0.5]を差し引きます。)
未入力セル,入力無しのセル[空欄]の表現[""]：=IF(B4="","",でなければ、何々しなさい。)
若し[B4]セルが[未入力セル(空欄)]ならば、正規時間のセルは「空欄」にしなさい。でなければ何々しなさい。
[IF関数]で「空白行」を[0値]にし、でなければ、○○を計算せよとします。
「空白行」を[0値]にしないと、数式「 =I4+J4 」の結果が「エラー」になります。
「正規手当」、「残業手当」とも、同数式で、「参照セル」が異なるだけです。
[基準値]に設定した[15分]の倍数 1時間は 0.041666667 (シリアル値) 45分 は 0.031250000 30分 は 0.020833334 15分 は 0.010416667 0分 は 0 参考 1 分 は 0.0006944445 (シリアル値) シリアル値については、こちらを参照ください。 「川田次郎」の場合の時間表示 検証 就労開始:[9:10]、就労終了:[17:00] 時間の計算 17:00 (17時) - 1 (シリアル値上での計算ですので、繰り入れの考えは不要です。 ここでは、解説のため用いました。) - 9:10（ 9時) -------------------------------------- 7 (時間) -------------------------------------- 分の計算１ 0.041666667(1時間を分に繰り入れ) + 0.041666667 - 0.000694444*10 ------------------------------------------------------------- 分の計算２ 0.041666667 - 0.006944444 ------------------------------------------------------------ 分の計算結果 = 0.034722223 ------------------------------------------------------------
「分の計算結果」に対して[基準値]の倍数 45分の[0.031250000] (シリアル値)が自動的に適用されます。
１時間が１なら、30分は[0.5時間],15分は[0.25時間],45分は[0.75時間]となることは、容易に想像できるところです。
（計算：戻り値は[0.031250000] を「1(時間):0.041666667=x:0.031250000」で計算し、時単位に直しますと[0.75]時間）
合計時間：7.75時間
関数式の詳細解説
=IF((B10="","",HOUR(C10-B10)+FLOOR(MINUTE(C10-B10),15)/60)
上の関数式を分解します。
=IF( 条件式 ,引数2, 引数３ )：IF関数の書式です。
B4="" ：IF関数 引数1の[条件式]です。
, ：引数1の終了の[,]です。
"" ；IF関数 引数2です[条件式]がTRUEならば、こうしなさい。
, ；IF関数 引数2の終了の[,]です。
HOUR(C10-B10)+FLOOR(MINUTE(C10-B10),15)：IF関数 引数3の内容で[条件式]がFALESならばこうせよ
HOUR(C10-B10) ：時間数を求めます。
+ ：+
FLOOR(MINUTE(C10-B10),15) ：FLOOR( ,15）で15(分)倍数の「基準値」を求めます。
FLOOR(MINUTE(C10-B10),15)/60：[MINUTE]関数は分単位です。60で割って時単位に直します
アルバイトの出勤カレンダー（アルバイト手配）
下の図は、毎日２名のアルバイト確保のための、一つの方法です。私情をはさまないよう「乱数１桁表示」('=ROUNDDOWN(RAND()*10,0))を使って、機会均等主義で全員に同じ機会を与える努力をしました。
関数を使ってカレンダーに写しました、
関数を使用した請求書
使用した関数：TODAY,CONCATENATE,VLOOKUP,ISBLANK,TEXT,&,SUBTOTAL,＆数式
TEXT関数については、前ページをご覧ください。
下記をデスクにダウンロードして、ご使用ください。オリジナルですので、自由に編集してご使用ください。
[H1]セルに「 =TODAY() 」
TODAY関数：書式は「 TODAY() 」です。
表示は[2005/9/28]のスラッシュ型です。それを、[セルの書式設定]→[表示形式]→[分類]→[日付]で元号年月日にしてあります。
TODAY関数は、EXCELを開いた日付けをリアルタイムで表示します。もし日付部分を固定しておきたい場合は、TODAY関数を使わずに、セルに直接「2005/9/23」のように入力します。
[B5]セルに「 =CONCATENATE(VLOOKUP(A5,$I$12:$J$22,2,TRUE),I23) 」
CONCATENATE関数：書式は「 CONCATENATE(文字列1,文字列2,文字列n) 」です。
引数に指定した文字列をすべてつなげた文字列にします。半角スペース、全角スペースも文字列です。VLOOKUP関数で引き出したものも文字列です。 文字列を連結して、１つの文字列にするのに、文字列演算子[&](アンバサンド)がありますが、こちらの方が使い勝手が良さそうです。
書類では、半角スペースを挿入して、くっつけました。
VLOOKUP関数：書式は「 VLOOKUP(検索値、範囲、列番号、検索の型) 」です。
VLOOKUP関数は、データを検索する関数で、別に作成してある表を参照して、コードNo．で文字列(例えば商品名、
価格など)を引き出してくれます。
本来は、請求書に載せるものでなく、別シートに置くべきものですが、参考的に乗せました。
VLOOKUP関数については、こちらを参照ください。
[A5]の数値を１字変えると、右辺の表からお得意様を引き出します。書類から言って会社名が適当なのですが、１ページに表示するため、個人名を使用しました。
[B7]セルに「 =CONCATENATE("一金 ",TEXT(F45,"#,##0"),J23) 」
"一金 "は文字列、[TEXT]関数の表示形式で操作した文字も、文字列です。セル参照の[I23](I,は1でなくiです。)も文字列です。
=IF(ISBLANK(E12),"",E12*F12)
ISBLANK関数：書式は「 ISBLANK(テスト対象) 」
注釈 ：セルの内容が空白の場合にTRUEを返します。
テストの対象注釈：テストの対象にはテストしたいデータを指定します。
テストの対象が空白セルを参照した場合が [TRUE]になり、空白セルが無ければ、[FALSE]になります。
[FALSE]の表現は感じが良くありませんが、[IF]関数の場合でも、[TRUE]の場合は[""](空欄)にしておいて、
[FALSE]の処理で「こうして欲しい」とまともな処理を指示するのが「常套手段」になっています。
[ISBLANK(テスト対象)]は、そのまま、[IF]関数引数1の「論理式」になります。
関数式の構造
=IF( 論理式 , 引数2 , 引数3 ) 「若し、AとBが[=]か[<]の関係か[>]の関係か[<>]・・・等の関係に あるときTRUEならば[引数2]に、FALESならば[引数3]にしなさい。」 ISBLANK(E12) "" E12*F12 「若し,テスト対象がブランクだったら[E12*F12]を実行しなさい。」
対象は[E12]ですが、オートフィル機能でE列全般に設定します。これは、 金額欄に[０]や[エラー]表示を避けるために使用しています。（「=IF(OR(E12=""),"",E12*F12)」でも一応できますが、 [OR]関数を使って1個では気が引けます。D 列 E 列の2箇所指定して[AND]関数を使用したところ[0]が表示されたり、 されなかったりでした。？）
[G6] ="請求No．"&TODAY()&"A-"
請求書No．をどのようにしたら良いか、の１方法として、当日のシリアル値(TODAY())に担当者Aの何番にしました。 なお、文字列のつなぎに、[&]を使用しました。
[G16,G19,G28,G37,G42]各セルに「 =SUBTOTAL(9,集計するセル範囲)
SUBTOTAL関数：書式は「 SUBTOTAL(集計方法, 範囲1, 範囲2, ...) 」です。
SUBTOTAL関数で集計方法で使用する[引数＊]とは
引数：9 （SUM関数使用=合計）
引数：4 （MAX関数使用=最大値）
引数：1 （AVERAGE関数使用＝アベ）
引数：5 （MIN関数使用=最小値）
引数：2 （COUNT関数使用=数値の個数）
引数：3 （COUNTA関数使用=空白除く個数）
引数：6 （PRODUCT関数使用=積の計算）
引数の番号によって「集計方法」を選択します。 １１個有るうち主な７個を挙げました
いろいろな関数の代用になりますが、今回は、すべて[9]のSUM関数に使用を使いました。便利なところは、
各小計をだしておいても、範囲指定で小計欄を含めても小計欄は合算されないことです。[G43]セルの場合がそうです。
[G43]に「 =SUBTOTAL(9,G12:G42) 」
[F45]に「 =G43*1.05 」
合計値に５％の消費税を加えた数式です。これが、上の請求金額欄に、呼び込まれる仕組みになっています。