集計・シミュレーション関連

2つのエクセルシートの差分を見つける

2007年1月14日 集計・シミュレーション関連

ちょくちょく使うマンガ＆インターネットCafeです。

最近のネットカフェは小ぎれいな個室が完備されていていいですね。

女性の利用者も結構いますし、"オタクのオンラインゲーム場所"という暗いイメージはもう古いようで。。
ビリヤードやダーツ、卓球台にとどまらず、シャワーまで完備のところまであるらしく、なんだかよくわからない空間に進化しつつありますね。。
(・_・;)

ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ

さて、ここで問題です。

取引先から、または関連会社からメール添付で大きなエクセルファイルが送られてきました。数値データが並んでいます。

あなたはそのデータを用いて資料を作成していましたが、しばらくして、「失礼しました。先ほど送ったファイルにはいくつか間違いがあったため 破棄していただき、こちらのデータをお使いください。…」とよく似た新しいファイルが再度送られてきました。
一から作業をやり直すより、違っている数字を見つけてそれに関連するところだけ修正した方が速そうですが、相違点にはまったく言及されていません。確認しようと電話をかけても担当者につながりません。時間はどんどん過ぎていきます。さて、あなたはどうやって相違点を見つけますか？並べ替え？vlookup？
2つのファイルの差分を見つける方法をいくつか紹介していきたいと思います。

複数の制約条件を満たす解を探す（ソルバー）

2006年10月7日 集計・シミュレーション関連

例えば、

[Sheet1]

【 ただし、B6セル⇒ =AVERAGE(B2:B5) 】

という表があるときに、次の条件を満たしながら平均点を最大にする算数と社会の点数を探したい場合は、以下の手順で行います。

・算数は自信が無く、平均点以下とする。
・しかし、算数は社会の得点以上はとれるものと考えられる。

(1)[ツール(T)]→[ソルバー(V)]

(2)「パラメータ設定」ウィンドウの「目的セル(E)」に「$B$6」を入力、「目標値(M)」を「最大値」とし、「変化させるセル(B)」に「$B$3,$B$5」を入れて「制約条件(U)」の「追加(A)」をクリック。

(3)算数は平均点以下という条件があるので、「制約条件の追加」ウィンドウで「$B$3 <= =$B$6」と入るようにして「追加(A)」。

(4)かつ、算数は社会の点数以上という条件があるので、「制約条件の追加」ウィンドウで「$B$3 >= =$B$5」と入るようにして「OK」。

(5)「パラメータ設定」ウィンドウで「実行(S)」をクリック。

(6)条件をすべて満たした最適解が見つかったので、「探索結果」ウィンドウで そのまま「OK」ボタンをクリック。

単純な条件のもとで最適解を探すには、以前説明した「ゴールシーク」機能で充分ですが、複数の制約条件が絡み合うもとで最適解を探すには「ソルバー」機能を使います。

[ツール(T)]メニューに[ソルバー(V)]が現れない場合は、[ツール(T)]→[アドイン(I)]から「ソルバーアドイン」を選択して「OK」ボタンをクリックしてください。ソルバー機能の組み込みが開始され、完了後に使えるようになります。

<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Shukei/47.pdf

ケースに分けてシミュレーションをかける（シナリオ）

2006年10月1日 集計・シミュレーション関連

例えば、

[Sheet1]

【 ただし、B6セル⇒ =AVERAGE(B2:B5) 】

という表があるときに、算数と社会の点数で、最悪のケース，最善のケースをシミュレーションしたい場合は、以下の手順で行います。

(1)[ツール(T)]→[シナリオ(E)]

(2)「シナリオの登録と管理」ウィンドウの「追加(A)」をクリック。

(3)「シナリオの追加」ウィンドウの「シナリオ名(N)」に「最悪のケース」と入力。次の「変化させるセル(C)」の空欄をクリックし、B3セルをクリック(「$B$3」と入ればOKです。)。そのままCTRLキーを押しながらB5セルをクリック(「$B$3,$B$5」と入ればOKです。)して「OK」。

(4)「シナリオの値」ウィンドウの「$B$3」に「40」，「$B$5」に「60」

(5)「シナリオの追加」ウィンドウの「シナリオ名(N)」に「最善のケース」と入力して「OK」。

(6)「シナリオの値」ウィンドウの「$B$3」に「80」，「$B$5」に「90」と入力して、「OK」ボタンをクリック。

これで、[ツール(T)]→[シナリオ(E)]から「シナリオの登録と管理」ウィンドウの「表示(S)」ボタンで指定したシナリオを表示することができます。

<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Shukei/46.pdf