EXCELの良いところばかりを表す、というのが私どもに求められている仕事なのかもしれませんけど、EXCELの弱み、という点を時には指摘しなければならないかもしれません。
EXCELの決定的な弱みは、やはりワークシートに最終行がある、というところだと思います。
6万5536行からEXCELは104万8576行にヴァージョンアップしましたが、取扱い得るデータ件数自体が決められています。アプリケーションに割当てられるメモリ領域やデータ保存可能な容量が割増し、WEBデータベースなどはデータ件数より、料金追加で保存容量を拡張すれば無制限、と呼ばれるご時世ですので。
その点、ACCESSなどデーターベースソフトのテーブルは、行数の制限はありませんので、サーバから年月の範囲等で抽出したデータ件数をあまり気にする事はありません。WORDなどワープロソフトはページで分割されますが、新規文書は元々罫線がなく白紙で、考えようによっては行数に制限は無いでしょうか。また、テキストエディタ、Windowsメモ帳やノートパッドなど、フォントサイズを小さめにしてデータを落とし込むとするとEXCELより大量のデータをEXCELより小サイズで出力できるかもしれません。
もっとも、データを表示させるために開いたテキストエディタで作業するだけで「保存しない」のであれば、大量データ保存に時間がかかる、保存出来ない等のトラブルまで問題にする事もないと思います。
SUM関数の SUM は「Summation」の略語とどこかで読んだかもしれないのですが、
「SUM」自体で単語だそうです。
※以下の()内は引数ではありません。
Summation ＝the whole sum(総額、総数)
The sum total is 100,000
a big sum(大金)
do a sum(計算する)
a like sum(同額)
某データシートにJOBが来るごとに随時、1行1件ずつデータ情報を追加していくというエクセル表で、毎日退社時は当日データの最終行に当日計の行として、当日分セル範囲を選択してSUM関数で小計、と毎日毎月何行追加になるか分からない、という表を作成していました。
月末には、月末日の当日小計行のさらに１行下、表の最終行を当月計として、
＝SUM(月初日1行目：末日最終行)／２
としていました。
各日各データの合計と小計行がダブっているだけだから、というわけです。
業務用で1円未満を含むちょっとややこしい資料です。
小数点以下2位
5項目分のポイントを加算
↓
5等分した値を各項目の元データから減算します。
計算結果が請求金額と同額なのを確認して、科目別の仕訳伝票を起こす作業でしたが、EXCEL2010(Win7)で、EXCEL表の合計金額が正しい値(整数値)で見えているので［OK］したのに、業務システムに入力した収支の結果の方を見ると1円不足しています。
1円も違っていました！！
EXCELの数式を信用できない例はわりとよくあります。
ROUND関数で桁上げや四捨五入した値を用いる場合、整数値を計算した後、もう1度データを手入力することがあります。
INT関数などでも同様です。値貼り付けすると、小数点以下の値までセルにちゃんと残されていることが解ります。
計算結果がどうしても合わない時は、数式やコードを見直す仕事より、慌てずに実際のお金の残高に合わせてあるデータを信用することです。
エクセルで簡単な両替ツールを考えているところです。
金種は以下の9通り、
1万円札
5千円札
1千円札
5百円玉
1百円玉
5十円玉
1十円玉
5円玉
1円玉
まず、A1に金額を入力すると、B列各セルの数式で金種ごとの枚数が計算されるという標準ツールで、実際手元にあるお金の枚数をエクセル両替ツールでチェックします。
次に作成したいのは、以下B列の各金種行に「欲しい枚数」が入力できるものです。
2万5千円分の1千円札が欲しいとか、出金は4千8百円だけど5百円玉が10枚欲しい等の両替の希望条件がある場合です。
1万円札の方から処理すると、金額を各金種で割り算して整数値にする関数を使用すれば計算できます。
金額は0以上の整数ですから、関数の性質はあまり深く考えなくても大丈夫だと思います。
1円玉の方から考えると、10円以上の端数で枚数を計算できますが、5の倍数の金種があるかないかを一度確認します。
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で［行/列の入替え］をやってみる。
縦長横長の表の体裁が目的なら、表の行数と列数が同じテーブルでは、意味ないのかどうか。
表の見出しが、縦に並ぶのと、横に並ぶのと、
表の意味がちがうかもしれない。
データの中身が結局同じならば、縦横どちらでもよいのかも。
また、実際［行/列の入替え］をやってみると、［貼付け］できる時とできない時がある。
警告［コピー領域と貼り付け領域が同じサイズかつ同じ形状でない場合は、それらの領域が重ならないようにしてください。］
コピー元の表の先頭で、表データの［行列を入替え］しようとしていたので、警告が出た。コピーした表と重なる位置で［貼り付け］することができない仕様ということ。
WEBページをEXCELに貼付けしたり、業務システムからデータ表をテキスト(.csv形式)でエクスポートしたけれど白紙の部分、空白行がとても多い。EXCELで一度開いてみると1000行くらいまでの範囲に表1-表10 のようにいくつか表題が気まぐれに書いてあるような資料の場合です。列はほとんど揃っているのですが、セクションごとの表が10行前後間隔で離れていて、表自体もタイトルだけでなく表全体に空き間隔があります。
A列に1列挿入し、1などの値を入力して追加します。オートフィルタが設定できる表の状態にしてみます。次に、オートフィルタで［空白セル］を選択したあとの状態を見ます。空白セルが真っ白でなく、どこかにまだデータがある行が見られるようなら、「空白セル」列に何か入力して「空白セルでないセル」の方にしなければなりません。再度［空白セル］選択するとシート全体が真っ白になり、なにもなければ空白行すべてを行選択して［行の削除］の操作をします。オートフィルタを解除して、全体表が整然と見えてきたらOKです。
列もずれて揃ってない部分があれば、タイトル行とデータを揃える、テーブル表にしなければなりません。
ちなみに「WEBページをEXCELに貼付けする」場合は、Webページ上で ［Ctrl＋A（すべて選択）］→［ Ctrl＋C(コピー）］ の操作で、WEBページのコンテンツをコピーしたら、EXCELワークシートに切り替え、A1セルで右クリック、値貼付けしてクリップボードのページ全体からWEBページ上のLogoや広告等のイメージを除いて、テキストデータだけを貼付けします。
日々の収支をEXCEL表にする場合です。「損得勘定」表を実際に作成してみるとき、毎月の給料日を「収入」データの列に設定すると、条件不足です。お財布の中身の金額をテーマにすると、毎日のように出費があるので、レシートや領収書を保管しておいて、あとでまとめてEXCEL表に入力することができますが、空っぽになりそうなお財布に資金を追加する時に、収入のデータを追加入力、「本日の予算」として、表計算で「収入ー支出」の数式を見ると、日々の収支が一見して明らかになります。
たいていは、頭の中で追加した資金分をバーチャル加算していて、「本日の予算」の金額入力は省略、日々の収支はアバウトになりがちです。まだまだある、と思っていたのに、もうお財布が空っぽになりそう。実際の数式に依存、感覚的な収支勘定よりも、EXCEL計算式の方が現実です。自分自身で作成したEXCEL表が気になって、終始使ったお金を見張られている気がすると、予算に余裕ができるかも。
EXCEL表計算は、コンピュータがやる仕事ですから。情け容赦なく、絶対に正しい。自分で作ったEXCEL表に従わざるを得なくなり、無情な事です。日報や日記より、自分の日常を自分で知る手掛かりになるかもしれません。
「本日の予算」を、「本日の賭け金」などと置き換えて表計算を活用したり、ほとんどの場合、実際にEXCEL表を作ってみなくても、頭の中で日々の収支のくらいできるはずなのに、と思ってしまいます。
EXCELでシンプルな表を作成します。
列Ａに本日の日付、列Ｂに本日の予算（概算で構いません)、列Ｃに本日の支出(百円以下切捨て感覚で結構です)で、Ｄ列に =B－C という数式です。次行は上行Ｄ列(前日残高)=下行Ａ列(本日の予算)で、同額でなくて構いません。子供のお小遣い帳みたいな簡単な数式なのですが、月初には確かにお財布の中にあったお金をよく覚えていて、使ったお金は大体の金額で端数の小銭を切捨てて考えます。B-Cの値（列D）は頭の中で悲観的な勘定より、もう少し残っているはずです。また、もし小さな儲けがあった日があれば、それを何日間もよく覚えていて頭の中で、或る日「あの日得した分を今日使おう」などと良かった日の記憶を何度も呼び出して自分自身で許可していたりします。しかも、得した日のデータ結果を翌日以降の列Ｄに値のみコピーして、表の数式を上書きして消してしまうみたいです。数式で計算できる正確な残高より、過去の良かった結果の方が弾みになって、将来的に儲けに繋がっていくかもしれません。
実際にEXCEL表で列B，列Cに本日の収支データを入力して、列Dの値が思いがけず少なかったという場合は、日常的に期待値が高めで、頭の中のどこかで損得勘定を間違えてしまっている可能性も高いです。
EXCELデータ100行程度の集計でしたが、照合して集計が複数項目あったのでピボットテーブルを5シート分作成したところで、EXCELの動作が不具合になってしまいました。
EXCELは操作に慣れると便利ですが、万能ではありません。割当てられたメモリ容量が不足してくると、動作が鈍くなって、上書き保存されなくなったり、酷い場合は、SUM関数の合計値を正確に出せなくなる、ということもあります。
ピボットテーブルで、抽出フィールドを変更しながらデータ処理を繰返す作業で、ワークシート5シートを交互に開いて同様の作業をしていたところ、またEXCELが固まってしまいました。抽出項目のカウント結果が表示されなくなっていて、１表あたりのデータ数を考えると、無理な作業を繰返していました。
ピボットテーブルを操作した結果を全選択 →値貼付けしてデータ値だけをシートに残すようにすると、支障なく作業を継続できると思います。
CSVのデータ表が、4行組の表になっている場合、A列に 1,2,3,4,1,2,3,4 と行頭に連番を入力してフィルコピー、オートフィルタで抽出する方法を前述しましたが、
他にも方法があります。
行頭1,2,3,4,1,2,3,4 ..連番を入力したA列を、昇順で並べ替えします。
各表データの比較が、1回の操作で比較できます。
Excelデータベース操作の基本はデータ表に空白行がない状態、A列かどこか表の1列が入力済みの一連のデータで埋まっていることです。
それから、データがすべて表示されていること。
共有で、作業中のシートにオートフィルタが設定されているのに気付かない事があります。行番号が青文字になっている場合です。また「非表示」行がある場合も要注意です。
既にシートがレポート表になっていて、表のタイトルが「セル結合」などデザインされていたりして、列選択やフィルタ操作がいったん出来ない様に設定されている時もあります。
データベース操作に書式設定はほとんど必要ありません。元データのシートをコピーしてレポートのデザインイメージをとっておいて、作業するシートの表でタイトルなどの行は削除し1行目からデータ表にします。操作のエラーがあってもなくても、作業シートをコピーしてバックアップとして保存しておくと、作業に集中できると思います。バックアップは、データが消えたり元に戻せなくなったりパソコンに何か問題が起こった時にその前の時点に手順を戻すための一番良い対策です。なにごとも問題無いのがなによりで、取り越し苦労かもしれませんが、事前にシート1回コピーして保存しておく操作は、誰でも簡単に出来ることですから、億劫がらない事が肝心です。
あとは、データの1行目は各列の項目名(フィールド名)があってもなくても構いませんが、データ数をカウントするとき列の名前があればデータ数は行数-1になること等です。
シンプルな表がありました。
3 1 4
2 1 3
・・ （以下略）
3+1 =4 ，2+1 =3 なのですが、Excelでなくても、小学生でも計算できると思います。
見出しもなにもなく嘘みたいな表で、千万の桁で四捨五入しているとか切上げしている表なのかもよく分かりませんでしたが、もしも、どこかで値が1 違うと言われると1億円、焦ってきますので、要確認です。
行の集計値を千円単位としてROUND関数で千円未満を切捨てた数値にして、この表の結果を合計行のSUM関数で計算すると、実際の残高と合わないということがよくあります。
全体の合計を計算するときは千円未満切捨てした概算とは別の列に、1の位まで正確に表示させた元データが必要で、1円まで正確なデータ列を合計した結果を、ROUND関数で千円未満切捨てしなければなりません。金額にもよりますが、前述した、切捨てして千円未満の各行を合計する、その差額分が結果に影響しているのがお解りになると思います。
Excel関数をマスターして、様々なサンプル例を学ぶことになりますが、実際に職場では、システムツールなどで、それほどレベルの高い関数式やプログラムを使っていません。たとえば、月次決算表にしても、合計セルはSUM関数で集計をするだけです。
もともと決算書類は手書き伝票を転記して、電卓や算盤で集計していたものを、今はシステム入力するパソコンで作業しているわけで、それほど複雑な計算はしていません。足し算ならA＋B＋Cなど、関数も使わずに、四則計算式で充分なのです。
データが十数行以上になると、SUM関数が必須です。プラス記号(＋)で各行を加算入力すると、数式が長くなりすぎるので、四則計算式だけで数式を作ると、限界を感じます。
SUM関数なら、マイナス値のセル・データでも気にせず、セル範囲を加算する計算式になるので、意外と便利です。
EXCEL表の合計は、たいてい一番下の行にあります。
しかしながら、新着データが日々追加されるような表は、EXCELデータの行数が続々と増えていきます。合計行が最下行にあると、合計行の手前に新着データを追加、挿入しなければならない仕組みになってしまいます。また、データが増えて数百、数千行になると、一番下の行が集計結果では、見づらくなってくるし、数式がずれてくるかもしれません。
そこで、ワークシートの上方に、合計やデータ表の集計結果を表示するスペースを作ることを考えました。
データ表の一番上の項目行は、10行目あたりにします。次行からデータ表を下方へ追加していきますが、ワークシートには充分に余裕があります。データベースの項目行にオートフィルタ設定できますので、条件付きデータ抽出を実行することも可能です。
合計の値や、件数など、１～９行目の空白のセルどこかに、関数式を作って、集計結果が見られるように準備しておきます。データ表とは異なるフォントやフォントサイズにしたり、分かりやすくデザインすることもできます。データ管理が比較的、楽になると思います。
After ”Excel Workshop Trial”
