「database」タグアーカイブ

EXCEL WORKSHOP TRIAL

EXCEL◇ACCESSだったらできるかも

Microsoft Officeには種類があります。おもなエディションは、

Personal Edition　WORD, EXCEL, OUTLOOK

Home & Business　WORD, EXCEL, POWERPOINT, OUTLOOK

Professional　WORD, EXCEL, ACCESS, POWERPOINT, OUTLOOK

OFFICEに［ACCESS］というデータベースソフトがあることは知っているけれど、使ったことはないという人は多いと思います。あらゆる勤務先のPCにOffice Professional が入っているわけではありません。

ACCESSの初心者には、もともとSQLとかORACLEやIBM等データベース技術者、VB（VisualBasic）プログラマーなど、WindowsPCがはじめてで、OFFICEをやらなければならず、マウスでACCESS、のようです。

PCでWORD、EXCELを知っているから、ACCESSもやってみたい、というならば、WORDの［差込印刷］、EXCELの［データ］などのスキルがそのまま活かせます。EXCELとの違いが、ACCESSということです。EXCELで出来なかったことが、ACCESSなら出来るかもしれません。

ACCESSの［テーブル］は、EXCELのデータ表と同じデータです。

ACCESSの［クエリ］は、EXCELのフィルタで抽出されたデータ表と同じです。

ACCESSの［フォーム］は、入力フォーム、検索フォームにあたります。

ACCESSの［レポート］は、WORDの差込印刷と要領が同じです。データを出力（印刷）する画面イメージを作成します。

ACCESSの［マクロ］は、WORDやEXCELのマクロと使い方が異なります。VBAやコードを知らない人でも、作業手順を設定してACCESSマクロを組むことができます。さらにマクロを開発する場合に［モジュール］でVBAを記述します。

ACCESSは、WORDやEXCELのように、マウス操作で直感的に「なにか」作業を進めることが難しいのかもしれません。本やWEBサイトでACCESSのサンプル例で学んだので、だいたい解っているんだけれど、仕事で実際に管理しているファイルやデータに関して、情報を置き換えして「デスクトップやドキュメントにMyACCESSツールを作って置く」などと、なかなか..

アレ、欲しいんだけど、ACCESSで。

本などで学習して2～3年くらい経って、身近で「はじめてACCESS本を読んでいる」だれかに「ACCESSを教える」くらい、喋れるという事実にお気付きになるかもしれません。ということは理解しているのに..

EXCEL WORKSHOP TRIAL

EXCEL◇ データ区切り位置なら、空いてる右隣の列に

Webページから情報をコピーして、強引にEXCELに貼り付ける。
ワークシートに行列セルの枠線があるので表に見えますが、じつはA列しか入っていないみたいです。

EXCELメニューの［区切り文字］の要領を、一度操作して経験して頂きたいと思います。

ちゃんとしたCSVデータ形式でなくても、EXCEL「データ」メニューの［区切り位置］機能を使って、整った表の書式が得られるかもしれません。

［区切り位置］ウィザード画面に従って操作します。
1/3［カンマやタブなど‥］にチェックがある状態で［次へ］
2/3 設定をします。

A列を選択して「区切り文字」のいずれかにチェックを入れ、［プレビュー］に縦線が設定されていれば［次へ］すすみます。

データの間がスペースで区切られていれば［スペース］、文の区切りが句読点とか同じ記号や括弧で文章が区切れるなら［その他］をチェックして、目印になる記号を入力、［プレビュー］でイメージを確認します。［プレビュー］に区切り線が出ていなければ「区切り位置」は成功していません。

3/3 については、気になるようであれば詳細設定などをあとで確認します。とりあえず［完了］

つまりは［区切り文字］は、EXCELが既存データで表作成する機能です。何行かのデータの各行に同じ記号があれば、その記号を各行つないで表の縦の線を作り、２列目のデータを空いている右隣の列に移動して分割します。
1行に同じ記号があれば、2列目、3列目、4列目‥　という表になります。

スペースも1文字分の記号ですので［スペース］をチェックするとペース毎に右列を作ります。
csv(カンマ区切り)データは「,」業務システムのサーバからエクスポートされたテキストデータ等で、カンマ記号(,)で綺麗に整列しています。
［タブ区切り］データは、［スペース区切り］と同様ですが、EXCEL表でデータ入力するとき「TABキー」を使って隣のセルにカーソルを移動しますが、「区切り位置」ではその逆の操作をしているわけです。

EXCEL WORKSHOP TRIAL

EXCEL◇ オートフィルタ― 空白行削除

オートフィルタは、選択したデータを抽出することができますが、逆に、不要なデータ行をフィルタで抽出して、削除することができます。

たとえばデータ表に、番号だけでデータがない行がある場合に、オートフィルタで「空白セル」にチェックを入れて実行します。（すべてを選択）のチェックをはずして、一番下に（空白セル）のチェックのみ。OKすると、空白行が抽出されますので、データ表範囲の空白行を行選択して行の削除をしますと、データ表がすっきりします。

他の列のデータが表示され、データが必要な場合は、そのデータ列でさらに空白行セルをチェックして抽出します。

空白セルばかりでなく、不要なデータ行があれば、フィルタで空白セルと同様に、チェックを入れて抽出して削除することができます。

［Deleat］キーでデータ削除することと、行削除の操作することは、意味が違います。

ですが、いずれにしろ、行削除やデータ削除を操作したあとで、やっぱり消さない方がよかったと気付いたら、ショートカットで・キー Ctrl + Z（元に戻す）を試します。Ctrl キーを押しながら、Zキーを何度か押す、削除したデータが元に戻るまで、 Ctrl + Z　を繰返します。

この元に戻す操作は、ショートカット・キー Ctrl + S (Save) で上書き保存しても、EXCELが開いたままの状態であれば、Ctrl + Zで元に戻すことができます。

しかしながら、いったんEXCELを終了して、閉じてしまうと、上書き保存した状態のまま元に戻らなくなります。

データベース作業の前に、念のためバックアップ(シートのコピー操作)を1回、心得ておくことが大事です。

EXCEL WORKSHOP TRIAL

EXCEL◇SUBTOTAL とSUM関数

オートフィルタでデータ抽出して、表の列データを合計する場合です。
どこか空いているセルに［Σ］ AutoSum (オートサム・ボタン)を使って合計を計算する数式を作ろうとすると、「=SUBTATAL」 が表示されています。オートフィルタをオフにしている時はSUMなのですが、オートフィルタが実行されているとSUBTATAL です。

#NAME?

SUBTATAL は、オートフィルタを条件でセットあるいはリセットして表示されているデータの合計を計算しています。
SUM関数の合計値は、選択範囲や選択データの合計を計算しますので、SUBTATALの場合と計算結果が異なる場合と同じ値の場合があります。SUM関数を使うときは、オートフィルタで非表示のセルも引数で選択していると合計されています。

EXCEL WORKSHOP TRIAL

EXCEL◇相対参照と 絶対参照 ふたたび


「相対参照」 と「絶対参照」　の例

　（相対参照）A1　（絶対参照） $Ａ$１　とします。

絶対参照の参照セルの値や数式は、コピーしても、データ表を移動しても、参照元の値が変わりません。

また、
　Ａ$１　列の相対参照、行の絶対参照

行の先頭セルの値や数式を、列方向(右方向)にコピーする

　$Ａ１　列の絶対参照、行の相対参照

列の先頭セルの値や数式を、行方向(下方向)にコピーする

いずれか、絶対参照を逆して値や数式をコピーすると、先頭行、先頭列の値がずらっと連続でコピーされてしまいます。

もし、絶対参照が必要で、どちらか解らなくなっってしまったら、途中の適当なセルや、行の一番端のセルに、実際の値を代入してみればすぐに解ると思います。




EXCEL WORKSHOP TRIAL

EXCEL◇データベース操作の準備

Excelデータベース操作の基本はデータ表に空白行がない状態、A列かどこか表の1列が入力済みの一連のデータで埋まっていることです。

それから、データがすべて表示されていること。

共有で、作業中のシートにオートフィルタが設定されているのに気付かない事があります。行番号が青文字になっている場合です。また「非表示」行がある場合も要注意です。

既にシートがレポート表になっていて、表のタイトルが「セル結合」などデザインされていたりして、列選択やフィルタ操作がいったん出来ない様に設定されている時もあります。

データベース操作に書式設定はほとんど必要ありません。元データのシートをコピーしてレポートのデザインイメージをとっておいて、作業するシートの表でタイトルなどの行は削除し1行目からデータ表にします。操作のエラーがあってもなくても、作業シートをコピーしてバックアップとして保存しておくと、作業に集中できると思います。バックアップは、データが消えたり元に戻せなくなったりパソコンに何か問題が起こった時にその前の時点に手順を戻すための一番良い対策です。なにごとも問題無いのがなによりで、取り越し苦労かもしれませんが、事前にシート1回コピーして保存しておく操作は、誰でも簡単に出来ることですから、億劫がらない事が肝心です。

あとは、データの1行目は各列の項目名(フィールド名)があってもなくても構いませんが、データ数をカウントするとき列の名前があればデータ数は行数-1になること等です。

EXCEL WORKSHOP TRIAL

EXCEL◇空白行を削除する

EXCEL表でデータ加工作業をする場合、まず、データ表に空白行がないかを確認します。データ表がその空白行で区切られて、上下2表に分かれてしまうからです。タイトル行と接している上表の結果しか出ません。

作業手順書など、EXCEL行に文章入力するスタッフは、セルに入力すると改行して空白行を入れる習慣があるかもしれませんし、また途中のどこかの1行を行選択、Deleatしてデータを消しただけで「行の削除」をしないと、空白行が出来てしまいます。

表全体の行数、最終行が何行目のデータ表か確認して空白行は「行を削除」します。

EXCEL WORKSHOP TRIAL

EXCEL◇そもそものExcelオペレータ

パソコンをはじめたばかりだった頃に、学校や公民館でOAインストラクターやIT講習講師を担当しました。

講習会テキストでEXCELを学習しましたが、テキストのサンプルデータで5行×5列の表作成、SUM関数で合計、AVERAGE関数で平均値、全体合計値と各項目の構成比、グラフ作成など。EXCELとはどんなソフトなのか、EXCEL操作を実習する講習です。

しかし、実際、オフィスワークでEXCELはどのように使われているのだろう。

EXCEL2000は65536行あるけど、5×5行の表を作成して、あと6万5千5百行余りも空白行、EXCELとは、いったい何に使うのだろう。

実際、講習会の受講生の方からの問い合わせに対応できずに、困った経験があります。「問屋さんから送信されてきた経理データ」という、色付きでキレイな表だったのですが、講習会のテキストにはそのような実例が載っていません。「実はパソコンで事務経験がないので、経理のEXCELはよく解りません」と恐縮して、パソコンの先生などと呼べないかしら、と。

その後「オフィスでEXCELがどんな風に使われているか、知りたい」と相談すると、OAオペレータの職場に案内されました。


お陰さまで、いまやパソコンで経理や決算データ処理も出来るし、EXCELで数万行以上の顧客データベースを集計したり、売上実績管理表、基幹システム等の運用管理などまで、出来るようになりました！

EXCEL WORKSHOP TRIAL

EXCEL◇上書き保存10分

Excelデータベースのデータ件数が数万行ともなると関数やマクロ、「保存」操作にも時間がかかります。左手でCtrlキーを押しながらSキーで上書き保存、でステータスバーに「保存しています。残り時間10分」などよくあります。せっかくの作業を失いたくないので習慣でCtrl＋Sキーを無意識にたびたび操作していることがあり、その度パソコン作業が中断してしまうのです。

EXCEL WORKSHOP TRIAL

EXCEL◇ふりがな五十音昇順

PHONETIC関数・・Excel表の地名や人名のふりがな、どうしよう、ふりがなが全部合っているデータと、一部合っている、全然合ってないデータが出てくるので困ってしまいます。PHONETIC関数の法則で、漢字の読み方が、音読み訓読みが適当に組合せてあります。元データにふりがなが無い時、いちおうPHONETIC関数を使ってみるのですが、地名や人名は固有名詞なので正確なふりがなを求めてしまいます。PHONETIC関数の読みが正確かどうかを調べて、違っているふりがなをキーボードで入力修正しなければなりません。正しい地名や人名のふりがな入力する作業を考えると、あまりに時間がかかり過ぎます。

Vlookup関数でデータ抽出する場合、検索値列の地名や人名を五十音順の昇順に並べ替えしますが、ふりがなは省略できます。
表の左端列の地名や人名リストを昇順で並べ替えする場合に、ふりがなは必要ではありません。元表の市区町村名を漢字表記のまま、並べ替えの[A-Zボタン]で昇順にします。PHONETIC関数の結果と同じことで、EXCELの仕様で、昇順は固定され決定されているはずだからです。Vlookup関数で検索するわけですから、読み方が正確な五十音順でなくても、Excelが認識する昇順であれば、大丈夫です。

EXCEL WORKSHOP TRIAL

EXCEL◇データベースの合計行

EXCEL表の合計は、たいてい一番下の行にあります。

しかしながら、新着データが日々追加されるような表は、EXCELデータの行数が続々と増えていきます。合計行が最下行にあると、合計行の手前に新着データを追加、挿入しなければならない仕組みになってしまいます。また、データが増えて数百、数千行になると、一番下の行が集計結果では、見づらくなってくるし、数式がずれてくるかもしれません。

そこで、ワークシートの上方に、合計やデータ表の集計結果を表示するスペースを作ることを考えました。

データ表の一番上の項目行は、10行目あたりにします。次行からデータ表を下方へ追加していきますが、ワークシートには充分に余裕があります。データベースの項目行にオートフィルタ設定できますので、条件付きデータ抽出を実行することも可能です。

合計の値や、件数など、１～９行目の空白のセルどこかに、関数式を作って、集計結果が見られるように準備しておきます。データ表とは異なるフォントやフォントサイズにしたり、分かりやすくデザインすることもできます。データ管理が比較的、楽になると思います。

EXCEL WORKSHOP TRIAL

EXCEL◇都道府県市区町村データベース

デスクワークで非常に多いのは、住所項目を含むデータベースを取扱う作業です。データ数千件から十万件以上、もはや私達は住所情報を1件ずつデータを入力しません。CSV形式データをExcelにインポートして、住所リストを必要条件で抽出できるように加工します。

たとえば都道府県名とそれ以外、住所を2列に分割します。また、市区町村からはじまる住所リストを要求されますので、「都道府県＋郡」の左列と、それ以外「市区町村名以下」列に2分割して右列を別シートに値貼付け、市区町村住所リストにします。
「郡」「市」「町」などの文字を含む地名があるので気をつけなければなりません。「郡」郡山市、「市」四日市市、市原市　等　あと「都」京都府京都市 等