VBAに依る学校事務自動化の試み
「エクセルだけで完結する差し込み印刷」
"ExcelからExcelへの差し込み印刷"
１、集金事務等の省力
１、集金事務等の省力
① 徴収状況一覧から「未納通知書」
３、各種・成績処理/個人通知などの省力化
① 成績記録から「個人宛通知帳票」
② スポーツテスト集計から「個人成績通知」
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
/////////////////////////////////////////////////////////////////////////////////////////////////////////
① さる自治体で学校事務に実用・の、実際の備品購入伺い書（稟議書）
その壱
ExcelのデータベースからExcel帳票上の書式へ差し込み・をする、詳しい仕掛けを、
実用帳票作成を通して説明して参ります。
実際の.XLSファイルの提供方法は現在・テスト中。
（マクロを多用する加減でセキュリティーへの配慮等）
見本ファイルのみgoogleDrive共有で提供中です。
（リンクは別ページ「Excelの帳票を受け渡す・方法は」記事中に）
手渡しご希望の向きはご連絡を。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
② 年間購入計画台帳から「購入伺い稟議書」
２、学校保健事務の省力
① 身体測定等記録、検診記録から
２、学校保健事務の省力
① 身体測定等記録、検診記録から
「受診勧告通知書」
② 他
② 他
３、各種・成績処理/個人通知などの省力化
① 成績記録から「個人宛通知帳票」
② スポーツテスト集計から「個人成績通知」
４、その他
前書き
最近の学校では、諸連絡などにSNSなどの利用が増えていて、便利になって・とか。
が、各家庭や個人宛の連絡、特に金銭が絡む連絡・はまだ機が熟してはいないように。
紙で、個人ごとに「お知らせ」等を作成する場合、
「個人懇談のご案内」等、記入箇所が数か所なら、
印刷した書式に、児童生徒数の名前と時刻を手書き記入すれば良いが・。
例えば、
学校集金・・
納付状況は学年ごとの一覧に記録される・として、
目視手作業での転記の際にミスが起こりがち・なので慎重に作業をせねばならず、
件数が多いと、封筒への宛名書き封筒詰め等も含めかなりの時間を要する業務に。
そこで、
「納付記録はExcelで一覧になっている・」
なら、
そこから先、手間のかかる個別のお知らせ作成・印刷は機械がそれを参照して自動で、
という。
いわゆる、”差し込み印刷” 。
(DBソフト、ワードやはがき作成ソフトなど、広く使われている)
が、ここでは、多くの方が日常的に使っているエクセルのデータシートに組み込み、
「他のソフトに頼らない、Excelだけでの差し込み印刷を実現」
の説明をしてまいります。
/////////////////////////////////////////////////////////////////////////////////////////
仕掛けの概要
イメージとしては、
名簿から定型の書式にデータを差し込む「ハガキ作成ソフト」に似ている。
が、データは現在それぞれが使用中の形のまま、通知書式も位置や項目数や書式の制限は無し。
「印刷する必要のある方は？」
の抽出は、
例えば、
「F列、５月の金額欄または、G列、累積未納項目欄に数値のある人」
など、条件を数式で設定、
マクロが、条件にマッチする個人を見極め、
「その方の任意のデータを、お知らせ文書の当該箇所に挿入しながら印字」
します。
全件の中から、"条件にマッチする個人"を判別し、その全部を連続で印刷・します。
Excelシートで、縦横一覧になっている表データ・であれば、
どのようなデータでも、 =if()関数で該当者だけを確定しながら、
（無論全件でも一件ずつでも）
様々な個別データの入った「お知らせ」を作成することが可能です。
※
（=Lookup()等で、個別のデータを曳いてくるのではなく、
記録用帳票上で、数式で抽出条件を設定・
その条件に従って該当する個人を見分けながら、「差し込んで印刷」を自動で繰り返すので、
「通知書を作成の度に・予め未納者だけの派生表を作成」
などの手間がなく、大切な記録表の改変を伴う作業をすることなく作業が進むので、思わぬデータ毀損などの事故も防げ、汎用性が高い）
lookup()利用の個票作成は、別途・「バーコード利用の図書台帳作成」で紹介いたします。
（Xlookup() への切り替え等も）
更に、
Excelならでは・の柔軟さとして、
通常のRDBのように、
一件一行（レコード）でなくても、柔軟に対処できるので、
「一件を複数行に亘って記録している票形式記録」
ような場合でも、データベースを一件一行に仕立て直すことなく、
マクロの小改で問題なく差し込み印刷ができます。
また、
数回に分けて、別々の表からデータを差し込む動作を繰り返し、その後に、
印字を実行することも出来るので、（下に事例）
難しいリレーショナルデータベースソフトを使う必要もありません。
窓付き封筒の使用で、封筒印刷も不要に。
（宛名データを窓位置に合わせ書式に配置・なども自由に）
（学校現場に無償提供を始めて以来、実際に様々な応用・活用がなされています）
記事が折りたたまれている場合があります。「続きを読む」をクリックして下さい
マクロなし・抽出数式無し・など、様々なVersionが
基本動作を書き進めるにあたって、
まず「データ」と「印刷される書式」を準備し、
「特定の個別データが転送され、書式の当該箇所に配置される・」
その様子を、マクロや関数を使わない・手動バージョン・でシミュレーションし、
ExcelからExcelの差し込みの原理や、Excelならではの利点を確認します。
マクロなし、関数無し、の最も簡単な差し込み印刷の仕掛け
Excel上の縦横データ表からExcel上の書式に差し込み・をする。
（マクロ設定の無い、まったくの素のエクセルBookです）
「お知らせ」等の（個別データが差し込まれる）書式の準備が出来ておれば、
差し込む項目数が何百項目でも手数だけ・です。
マウスクリックとショートカットキーで、次々にプリンターから個別文書が。
（一件につき、5クリック程の手動）
「どのデータを印字？」は、この場合・目視判断。（自動版では数式で判断）
（書式へのデータ配置を含め、手順を教えれば小学生でも差し込み設定、印字実行可能）
マクロによる自動差し込みでは、ワンクリックオペレーション。
それぞれに、
①「最初から条件に該当する件を全件」
②「カーソル位置から後を同全件」
③「カーソルのある行・この1件だけ」
のマクロが設定された３つのボタン・から選んで、押すだけです。
（4個目、"カーソルのある現在地から停止マークまでボタン"・も必要なら作成可）
//////////////////////////////////////////////////////////////////////////////////////////////////////
Excelで差し込み・の利点
普通に「差し込み印刷・」と言えば、MSワード。
「差し込み文書」タブから・ハガキや封筒、挨拶文など。
MSワードの場合、
「差し込まれるデータがExcelシートにある表の数値」でも、ワードに差し込んだ時点で、全て「文字」に。
あと、"書式"をExcelからそのまま引き継ぐには、DDEなどの"ハイテク"が必要・など、敷居が高い？。
そんな中で・、
仕掛けとして「差し込み印刷」が用意されていないExcelで、あえて
「Excelだけで差し込みを行う」
意義は、
数値は数値のまま・差し込む・つまり、
「差し込んだ先の印刷フォームでも数値を数値として扱える・」
ということ・。
数値を、差し込んだ「印刷する書式」の上で
「差し込まれてきた数値でグラフを描く、差し込まれた数値で計算し表示」
のようなことが簡単に実現できる・ことにあります。
これが、Wordをも凌ぐ、高度な差し込み印刷と用途の広がりを齎すことに。
あと、
設定したフォントや書式の引き継ぎで悩むこともなく、差し込んだ後の書式で元の書式から変更することさえ可能・つまり設定も超簡単！。
（これらが、学校事務に限らず、Excel to Excel の差し込みの最大の利点です）
/////////////////////////////////////////////////////////////////////////////////////////
この仕掛け特有の柔軟性と汎用性
２００７年頃の、"goo"などの質問箱に、盛んに
「ExcelからExcelへの差し込みは？」
というような質問が登場しては消えて。
検索で見える限り・・。
「"大掛かりなVB等での仕組み・"や、=lookup()等を使って" 以外では困難！」
のような専門家？からの答えが「ベストアンサー」で終わっています。
=lookup()を使う仕掛けも用途によっては有効で一応の差し込みのようなことは可能。
が、lookup関数は、
LOOKUP(検査値, 検査範囲, 対応範囲)
データを牽くにあたり「検査値」を指定する必要があり、名前とか番号とかを何らかの手立てで入力する必要が。
要するに、
「難しいので、余程でなければ・やらない」？。
※（Lookup()関数は、備品台帳管理や図書管理事務などで省力に役立つので、別ページで）
この仕掛けでは、
『「５月の入金が無かった方」
を、「条件式」で判断しながら、
該当者のデータを「未納のお知らせ」書式に送り・挿入・個別文書を完成させ印字・・
それを、全員分繰り返し、最終まで自動で印字させる・』
みたいな動作は、IF()で抽出しマクロで差込み印字・が吉。
① 動作のカスタマイズが容易
このExcelからExcelへの差し込み印刷は、
抽出に =lookup() のような関数を使用せず、
「男子で、身長が１６０㎝以上かつ体重が４０kg以下の者」
のような抽出の条件に if()関数 を使い、（入れ子で多重条件指定可能）
差し込みの転送動作や転記にはVBAを使う。
ので、
「仕掛けが単純で、汎用性が極めて高い」
という特徴があります。
また、
データ転送にVBAを使用するので、
通常の差し込み印刷が、データと印刷書式が１対１であるのに対して、
「累積未納のある方はA書式、今回のみ未納の方はB書式に差し込み、印字する」のように、
多重条件設定で「自動で複数の印刷書式を切り替えながら」差込印刷を最後まで自動実行出来、
「より詳細で複雑な差し込み印刷」
を実現。
（※ワンパスで大きさや色の違う用紙を使い分ける場合は、用紙トレーが複数同時使用可のプリンターが必要。複数トレーの切り替え動作ができない機種では、用紙を入れ替え、動作を複数回に分けます）
② 印刷帳票・レポート（差し込まれる）設定の容易さ
印刷される書式（差し込まれる書式）での、データの差し込み位置や書式等の設定が極めて簡単で容易。
- 設定中、差し込まれる一件目のデータが配置され、プレビュー表示されるので、視覚的に印刷イメージを見ながら細かく位置や書式の調整が出来る。
- 複数の文字や数字データも関数で繋げて一文として配置できるなど自由度が高い。
- 数値や日時の表示形式も印刷書式の方で自由に設定変更できる。
- 印刷済みの定型書式や画像の上にも、データを自由に配置しての連続差し込み印刷ができる。
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
実際の仕掛け作成
ここからは、
「具体的な仕掛けの仕組みや作成・」
を大きく5つの段階に分けて順に書き進めます。
仕掛けは後回しで「とりあえず使用してみたい・」
という方は、この章は読み飛ばし、次章
「データの用意」からお読みください。（準備中）
第１章 Excelだけで差し込み印刷
１、基本的な仕組み
① 差し込むデータの様子
新たに作成する場合を除き、蓄積された既存のデータの形は、ユーザーに依って様々かと。
新たに仕立て直す必要はなく、そのまま無改変でデータベースとして使用可能です。
また、
データベースソフトやハガキソフト上のデータも、シンボリックファイルでExcelに読み込んで使用可能です。
更に、
通常の縦横表形式である、「一件一行」のデータ形式ではなく
「一件が複数行に亘って記録された票形式のデータ」
でも加工することなく差し込みデータとして使用可能です。
② 差し込み動作にはマクロを使用、手動単印刷動作、連続自動をマクロボタンに配置
- 「差し込み開始ボタン」で、手動、続き、最初から、等の動作を選ぶユーザーフォームを表示します。
（停止位置は自由に設定できます） - 目視で差し込みデータを選択し、手動で一枚ずつ差し込み印刷する場合、差し込み動作マクロは不要。
- ユーザーフォームに印刷ボタン、プレビュー表示選択ボタンを表示する。
- 設定で、印刷ではなくPDFで連続保存も可能。
③ 印刷書式（差し込まれる側）設定の特徴
- 差し込む"一つのデータ"は、複数箇所に違う書式で差し込むことも出来る。（差し込んだ文字データをワードアートにして配置することも出来る）
既存の定型手書き記入書式の記入位置に（穴あき）、任意にデータを差し込める。 - 写真などの画像データにも重ねて差し込みデータを配置できる。
- 一回の差し込み動作で、複数の書式フォームにデータを差し込み複数枚印刷できる。（例えば、納品書や領収書等、用紙や書式も違う複数枚印刷を、１パスで印字）
- 差し込まれたデータは書式上でも数値や文字なので自由に加工変更したり、関数の引数やグラフデータとして使う（グラフを描かせ表示する）ことができる。
① さる自治体で学校事務に実用・の、実際の備品購入伺い書（稟議書）
（図は動作イメージです）
・差し込みの元データ帳票は、予算執行記録簿にもなっています。
・差し込まれて出来る書式は、予算支出についての「伺い書」（稟議書）です。
※、書式は実物ですが、データは全てダミーです。
※、データは事務職が従来から使っていたままを、改変せず使用。
動作
帳票１枚につき２度の転送の後、印刷動作を開始する事例です。
・一度目の転送は毎回必ず転送される、各費目の固定データ領域
・二度目は個別データ行を転送します。
（この場合の印刷帳票は２番目の行、雨量計の伺い書になっている）
※画像をクリックすると大きく鮮明に表示されます
連続印刷の教材に使っている「支出伺い」作成（データはダミーです）。
（最初の図は、シートごとの予算費目30個の内の「消耗品費」の予算執行記録簿になっている）
下の費目見出しシートは作者が作成（各費目シートへのリンクになっている）
② 差し込み文書側でのデータ変換やグラフ描画の事例
さる学校の保健教諭が実際に卒業生一人一人に手渡したものです（データはダミー）
（クリックで拡大します）
差し込まれたデータを条件式で変換して配置します。
座高はそのまま数値、
視力は0.7ならば「B」に変換して表示、
歯は、「C1」なら●に変換して歯牙票の該当位置に表示、
など、データを自由に加工可能です。
③ 保健記録から個票にデータを差し込み、
そのデータでグラフを描かせる
 |
| 差し込まれたデータを任意の位置に配置します。 記録帳票から差し込まれたデータを使ってグラフを描かせます。 （差し込まれたデータが数値のまま・なので自由に変更加工ができます） |
３ 作成
① レポート書式（差し込まれる側）
Excelのシートに普通に文書書式を作成します。
データが差し込まれる位置のセルは空けておきます。
ここからは実際にExcelにデータを書き込みながら演習してください。
・シートを2つ用意します。
・一つにはデータを置きます
項目数に制限はありません
文字とか数値とか、データの型の配慮は不要です
数式のままでも差し込み先で値に変わる
データ書式もそのまま差し込まれ、自由に変更も。
もう一つに差し込まれる文書を作成します
用紙の大きさや差し込む項目数に制限はありません
差し込まれる「文書」は、違う文面で同時複数作成も可
（一回の動作で同時に差し込み、それぞれ印刷範囲に名前をつけ・・）
記事以外のメモ（後日に）
Gif動画の動作テストです。（「差し込み印刷の動作」Gif動画を作成のための・）
新年度に更新された名簿で、
在校する兄弟姉妹（4名まで）の、
新年度の学年組と名前を、
互いの兄弟姉妹欄に書き込むマクロが動作する様子（見本）です。
（800人規模の名簿は10秒程で書き込みが完了します）
作成については、別ページで進めて参りますので、カスタマイズなどご興味の向きは、そちらをご覧ください。
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
学校事務でExcelを活用する・その壱
ExcelのデータベースからExcel帳票上の書式へ差し込み・をする、詳しい仕掛けを、
実用帳票作成を通して説明して参ります。
実際の.XLSファイルの提供方法は現在・テスト中。
（マクロを多用する加減でセキュリティーへの配慮等）
見本ファイルのみgoogleDrive共有で提供中です。
（リンクは別ページ「Excelの帳票を受け渡す・方法は」記事中に）
手渡しご希望の向きはご連絡を。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
※概略
ここでは、
最も単純な仕掛けから関数やマクロで自動化した仕掛けまで・幾つかのバージョンを順に紹介。
まず、
「ワードで差し込みが出来、年賀ハガキ作成ソフトが住所や名前を差し込んで印刷・を繰り返す・のだから、Excelでも同じことが？」
というのが仕掛け作成の始まり。
全く同じことが可能・それ以上、の差し込み印刷がExcelで簡単に実現できます。
仕掛けは極単純で、基本的にはマクロも数式も不要で、作成件数（枚数）が少ないのであれば、単純なバージョンの方が、むしろ快適です。
数式による抽出やマクロで自動化・は、省力と言うより、目視による見逃しや漏れ、転記ミス、を防ぐため・が主たる目的。
千件以上のデータから複数の条件に従って抽出し確実に印字する・ような作業が
「条件式を書き、後はボタンを押すだけ・」
で出来るのは随分の省力になり、多忙の解消にとっての助けに・
という点も小さくない。
誰もが、様々な業務に必要に応じて使い分けられるように・というのも大切で、
「便利よりも、融通の利く単純さを重視したバージョン」
から、
「定型化した作業を出来るだけ素早く確実に・
徹底して自動化したカスタマイズバージョン」
まで、
主にはオンラインで、時に、オフでサポートしながら作成します。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
ここでは、
最も単純な仕掛けから関数やマクロで自動化した仕掛けまで・幾つかのバージョンを順に紹介。
まず、
「ワードで差し込みが出来、年賀ハガキ作成ソフトが住所や名前を差し込んで印刷・を繰り返す・のだから、Excelでも同じことが？」
というのが仕掛け作成の始まり。
全く同じことが可能・それ以上、の差し込み印刷がExcelで簡単に実現できます。
仕掛けは極単純で、基本的にはマクロも数式も不要で、作成件数（枚数）が少ないのであれば、単純なバージョンの方が、むしろ快適です。
数式による抽出やマクロで自動化・は、省力と言うより、目視による見逃しや漏れ、転記ミス、を防ぐため・が主たる目的。
千件以上のデータから複数の条件に従って抽出し確実に印字する・ような作業が
「条件式を書き、後はボタンを押すだけ・」
で出来るのは随分の省力になり、多忙の解消にとっての助けに・
という点も小さくない。
誰もが、様々な業務に必要に応じて使い分けられるように・というのも大切で、
「便利よりも、融通の利く単純さを重視したバージョン」
から、
「定型化した作業を出来るだけ素早く確実に・
徹底して自動化したカスタマイズバージョン」
まで、
主にはオンラインで、時に、オフでサポートしながら作成します。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
作成実習
まずは、
① レポート書式（差し込まれる側）
Excelのシートに普通に文書書式を作成します。
データが差し込まれる位置のセルは空けておきます。
特殊な方法として、
宅配便伝票のような既成の書式をスキャナーで画像として取り込み、
それを差し込み印刷書式として使用する方法があります。
(印刷された書式用紙をそのままプリンターにセットして
印字に使う場合は位置の微調整が要ります）
･･･････････････････････････････････････････････
印字書式（差し込まれる）
さて、どこにデータが差し込み設定されているのでしょう。
黄色マーカー位置が差し込みデータです。
この段階で、それぞれに書式やフォントを設定し直す・ことが可能です。
黄色の部分が差し込まれたデータです。
一件ごと（一動作ごと）に2回の差し込みを行います。
①連番の前の項目までは費目ごとの共通データなので1回目に、
②その後は、個別データなので2回目に差し込まれます。
その後に印刷されます。
0 件のコメント:
コメントを投稿