電気屋にソフトを買いに行くとたくさんの種類のはがき印刷ソフトが販売されております。５０００円ぐらいのお手軽価格なので皆さんも一つは持っていることでしょう。

このソフト基本的には年賀状など個人として出す手紙に対して作られているので業務として使うには物足りない部分があります。

例えば、お客様コードをバーコード印刷してそのはがきをお店で持ってこられたとき、ﾊﾞｰｺｰﾄﾞﾘｰﾀﾞｰで読み取れば顧客管理も出来ます。でも市販のソフトでは難しいんですよね。

そこでエクセルを使ったダイレクトメールをお勧めします。しかしエクセルで作るうえで一番面倒なのは印刷フォームを手作業で調整しなければならないことと、連続して複数のお客様を印刷する場合が面倒です。

印刷フォームに関してはあらかじめ時間がかかりますが作成しますとあとはそれをコピーして色々と作りかえればいいのですが、問題は宛先の記載です。

一件一件名簿を見ながら手入力で入力してもいいのですが件数が多いととても入力が間に合いません。それならばボールペンを使って手書きで書いてしまった方が楽です。

という事で、今回も作ってしまいました。エクセルの関数のみを使って顧客名簿から印刷したいお客様の住所を表示させます。

今回も（ダウンロードのページ）で「ファイル番号５：はがき印刷ファイル名簿連動編」　をダウンロードしてください。

簡単に説明します。

【最初に設定する】　

シートは２つ顧客名簿と印刷です。

印刷シートで送元の住所や名前を記載してください。

次に顧客名簿を作成します。これは顧客名簿シートにあります。

顧客番号は連番でしてください。

顧客番号の小さい順に（昇順）に並び替えてください。

【使い方】　

顧客名簿シートの右に印刷番号があります。その右側Ｇ２のセル位置に印刷したい顧客の顧客番号を入力します。該当する顧客住所があれば住所や郵便番号が表示されます。なければ空白のままです。

次に印刷シートを表示させ印刷ボタンを押して印刷します。

それでは隠れている列の説明です。この表は一度入力されている時間をすべて分に換算してから計算し、それをまた時間に直しております。エクセルでは時間計算を簡単できる機能もあるのですが、今回は複合的に関数を使用するということで、ちょっと複雑ですがこのような方法をとりました。

■時間から分へ、分から時間へ
さて、Ｇ５のセルには数式が入っております。
=ROUNDDOWN(B5/100,0)*60+B5-(ROUNDDOWN(B5/100,0)*100)
結構長いですね。ROUNDDOWNは数字を切り捨てする関数です。ROUNDDOWN（数値、切捨ての位置）　となっております。

-3→　数字の３の位を切り捨て （例5265.684 → 5000)
-2→　数字の２の位を切り捨て （例5265.684 → 5200)
-1→　数字の１の位を切り捨て （例5265.684 → 5260)
0 →　数字の少数点１位を切り捨て （例5265.684 → 5265)
1 →　数字の少数点２位を切り捨て （例5265.684 → 5265.6)
2 →　数字の少数点３位を切り捨て （例5265.684 → 5265.68)


という関数です。入力されている関数ではＢ５を100で割った答えの整数部分が取得できます。つまり時間の部分です。この数字に60をかけると時間が分に変換されます。これは数式の前半「ROUNDDOWN(B5/100,0)*60」の部分です。

同じく後半部分「B5-(ROUNDDOWN(B5/100,0)*100)」は入力されている数字（時間から前半で取得した時間をひいた数字、つまり分を取得しています。例えば「1830」という数字が入力されていた場合、前半で「1080」後半で「30」という数字を得ることが出来ます。これを足すと「1110」というすべて分で表示されます。

同じように退社時間と休憩時間を分に変換して「退社時間　-　出社時間　-　休憩時間」で実際の勤務時間を計算することが出来ます。これはＪ列になります。

そして最後にＥ列でこのＪ列で得た分を時間に変換させています。Ｅ列は分で表示されている数値を60で割って一旦時間に変換させます。その整数部分はそのまま「時間」として利用します。その時間に60をかけ、Ｊ列の数字でひいた数字が「分」となります。だから桁をあわせるため、時間に100をかけそのまま分を足すと「630」といった具合に時間換算されるのです。

■入力を楽にする。
通常エクセルで時間を入力する時は「18：00」とのように「：」を入力しなければなりません。でも何度も入力していると数字だけの方が電卓を使用しているみたいに楽に入力できます。「：」を押すのが面倒となるのです。そこで表示上「：」を出す方法があります。セルの書式設定を利用するのです。

まず、顧客リストが必要となります。前回の連載で顧客リストを作っているわけですが、そのリストと印刷フォームを連動させると完成です。まずは顧客リストの改造からです。

上の図を見てください。左側の表は前回作成した表ですね。右のＦ列とＧ列は今回新しく追加する表です。まずはＦ列の印刷番号〜名前まで項目の名前を入力してください。これは単純に文字入力だけなので問題はないと思います。次にＧ列です。実はＧ列の４行目から７行目までの４行分（＃Ｎ/Ａと表示されているセル）には数式が入力されております。それは次の通りです。

顧客番号　　　　「=VLOOKUP(G$2,A$2:D$30,1)」

郵便番号　　　　「=IF(G2=G4,VLOOKUP(G$2,A$2:D$30,3),"")」

住所　　　　　　　「=IF(G2=G4,VLOOKUP(G$2,A$2:D$30,4),"")」

名前　　　　　　　「=IF(G2=G4,VLOOKUP(G$2,A$2:D$30,2),"")」

ＶＬＯＯＫＵＰ関数は表の中から検索してその検索結果を表示させる関数です。顧客番号に入力されている関数を見てください。　「=VLOOKUP(G$2,A$2:D$30,1)」　　G$2は検索したい顧客番号を入力されているセルです。　A$2:D$30は検索をしたい表の範囲です。　最後の１はその表の左から何番目を表示させるかとなります。顧客番号を表示させたならば１、名前なら２郵便番号なら３という具合にA$2:D$30で範囲指定した表の中から左から何番目を指定します。

他のセルはなんか複雑な式に見えますが、ＶＬＯＯＫＵＰという関数とＩＦという関数の２つを利用しております。

ＩＦ関数は条件によって計算結果を変えることが出来ます。実際の式は
「=ＩＦ(条件,条件にあっているときの処理,条件にあっていないときの処理)」というカンマで区切られた３つの部分から出来ております。

例えば請求金額を計算する時に5000円以上お買い上げの方は送料500円無料とします。5000円未満である場合　商品代金+500円という計算、5000円を超えた場合は商品代金のみという２つの式を使い分けなければなりません。これを単純数式で表現する事は出来ませんよね。そこでＩＦ関数を使うのです。　「=ＩＦ(商品代金＜5000,商品代金+500,商品代金)」　　　この関数の意味は商品代金が５０００円未満だと商品代金+500それ以外だと商品代金を表示させるとなります。

郵便番号から名前の欄まではこのＩＦとVLOOKUPを組み合わせた関数を入れており、入力した印刷番号と顧客番号が同じであるとき各項目の数値を抽出表示させるような関数にしております。

これで、印刷番号に印刷したい顧客番号を入力すると該当する顧客の住所名前などが各項目に表示されるようになりました。また該当する顧客番号がリスト上からなければ空白の表示になります。今回は難しい話になりましたがこの機能が使えるようになると表計算の幅が広がります。