エクセルでデータ分析をしてみたい

質問

艦これというゲームを通してエクセルを学ぼうと考えています。
艦これでは、投入する資材（燃料・弾薬・鋼材・ボーキサイト・開発資材）の量によって出現する艦娘の出現確率が変わる大型建造システムがあるのですが、その確率やグラフをエクセルで求めてみたいです。
サンプルはWIKIに多数あり、元のデータから区切り位置を指定し、抽出してエクセルで使用できる形に変換するところまで学びました。

人が手入力で報告したデータなので、元のデータには一部不要なものがあります。まず、こういったデータを取り除くにはどうすればいいでしょうか？
例えば、下記に一部例を上げましたが、資材はすべて数値を扱うので、そこに数値以外の文字が書いてあればそれを不要なデータと判断できますが、サンプル数が10000近くあるため手動で消すには骨が折れます。

【抽出したデータ】のようなデータが複数あるのですが、Aという艦娘（結果に該当）のレシピ（資材の組み合わせ　4000/2000/5000/5200　開発資材20など）毎の出現率を求めるにはどうすればよいでしょうか？

よろしくお願いします。

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
【元のデータ】
-4000/2000/5000/5200　開発資材：20　空きドック：1　司令：79　秘書：瑞鳳改38　結果：龍驤 -- &new{2014-02-09 (日) 09:16:52};
-3500/3500/6000/6000　開発資材：20　空きドック：2　司令：104　秘書：加賀改99　結果：まるゆ -- &new{2014-02-09 (日) 09:19:51};

#NAME? &new{20140209 (日) 23:23:30};（誤りデータA）

【抽出したデータ】
燃料　弾薬　鋼材　ボーキサイト　開発資材　結果
4000　2000　5000　　　5200　　　　　　20　　　龍驤
3500　3500　6000　　　6000　　　　　　20　　　まるゆ

#NAME? #NAME? &new{20140209 :30}（誤りデータAの抽出結果）;

回答No.1

bunjii · ベストアンサー

>人が手入力で報告したデータなので、元のデータには一部不要なものがあります。まず、こういったデータを取り除くにはどうすればいいでしょうか？
http://okwave.jp/qa/q8469708.html の続きですよね？
元データのサイトを探して内容を確認しました。
稀に空欄の行や誤記入の訂正等が有るようです。
手作業での削除やデータの補正は必要かも知れません。
提示のマイナス値はABS関数で補正すればプラス値に影響が及びません。
=Sheet2!A2 → =ABS(Sheet2!A2) のようにすると良いでしょう。
尚、元データをテキストファイルとしてメモ帳やワードパッドに取り込んで不要な行を削除してからExcelへ読み込んだ方が良いと思います。

補足

再度の書き込みありがとうございます。質問は前回の引き継ぎになります。
-4000などの謎の-は不要なので、元データに対して[-]をすべて[文字なし]に置換していましたが、ABS関数で絶対値を得られるんですね。

この回答を読む限りだと、不要なデータをコンピュータに判断させて削除させていくのは難しそうなのかな。

不要なデータがすべて削除できたとして、
【抽出したデータ】から、Aという艦娘のレシピ毎の出現率を求めるにはどうすればよいでしょうか？

4000/2000/5000/5200/20 燃料/弾薬/鋼材/ボーキサイト/開発資材の順
上記のレシピでの大鳳（結果）の出現率 X%など

普通に考えれば、上のレシピでの大鳳出現回数/上のレシピの総数で求めることができますが、
サンプル数が多いため、目で追って条件にあった行を発見していくのは大変です。

回答No.5

bunjii

>【質問１】
6条件を満たすレコードを計数するにはExcel 2007以降のバージョンの組込み関数であるCOUNTIFSを使うと数式がシンプルになります。

Excel 2013で検証しましたが、元のデータを手作業で修正しないと誤差が大きくなると思います。
原因は書き込む人によってフィールドのズレが生じるためプログラムで修正するには無理があるようです。
考え方としてはログ毎に1つのシートで元データと加工データを扱う方が分かり易いようです。
A列～F列を加工後の計数用データとし、G列を仕切り用に空けてH列以降（右側）を元データの貼り付け用にすると1枚のシートに収まります。
貼付画像は2枚のシートで処理したものです。

>【質問２】
過去ログのどれが最大かを見る意味が分かりません。
期間が同じスパンになっていれば何時頃が最大かを求める意味がありそうです。
目的の範囲を全て網羅してから考えてください。
１つのログについて処理が出来れば他のログについても準用すれば考えを纏められるでしょう。

回答No.4

keithin

素材の組み合わせではなく、「400040002000500020というレシピ」で一つのデータで良いのですね。

手順：
該当のページから丸ごとコピー、必ず一度「メモ帳に貼り付け」ます
メモ帳からコピーしてエクセルに貼り付けます

並べ替えによって大まかに修正、また今回はご希望により「開発資材：」を「/」に置換しておいてからスペースで切断
結果して資材（6000/5000/7000/2000/100）、空きドック、指令、秘書、結果、その他の列に再配置します
更にオートフィルタを使って異常データの行を抽出（例えば秘書に伊潜を使ってる行に多い）、適宜修正してざっと１５００件のデータが抽出できます

回答したようにピボットテーブルレポートでデータ分析してみます。

たとえば
ページフィールドに「結果」を配置
レシピを行に配置
レシピをデータにも配置
ページフィールドからあきつ丸を抽出してみると、全76件の中で4000/6000/6000/2000/20のセットが最も数が多く、17件報告されていました。
ちなみにご質問の4000/4000/2000/5000/20」というレシピでは、あきつ丸の報告は一件もありません。

今度は、では今の「4000/6000/6000/2000/20」の資材セットから、何が合成できるか見てみます。
レシピをページフィールドに移動、結果を行に移動します
レシピを「4000/6000/6000/2000/20」で絞ってみます
すると全部で339件の報告中、最大では大和が42件報告されています。

回答No.3

bunjii

>この回答を読む限りだと、不要なデータをコンピュータに判断させて削除させていくのは難しそうなのかな。
コンピューターはどれが必要でどれが不要かを知りませんので判断基準をあなたが示さないと何もできません。
そのために種々の関数やマクロのプログラムを使います。
コンピューター自身が関数の組み合わせやマクロを自動的に組み上げません。

>普通に考えれば、上のレシピでの大鳳出現回数/上のレシピの総数で求めることができますが、サンプル数が多いため、目で追って条件にあった行を発見していくのは大変です。
条件さえ提示できれば関数の組み合わせで条件に合ったデータの数を計数できます。
あなたの質問では具体的な計数要素について提示されていませんので取り敢えず有効なデータの整理から取り掛かって考え方を纏めてください。
総数は有効データの総数と言うことでしょうから有効データの定義を決めて一致するレコード数（行数）をカウントします。
出現回数も現在提示されている燃料/弾薬/鋼材/ボーキサイト/開発資材/結果（艦名？）の全てが一致するものか、結果と幾つかの項目が一致するものを選ぶかは集計の目的で変わるでしょう。

私はこのゲームを知りませんので単純に「出現率は何%になるか」との設問に回答できません。

補足

難しく捉えられてしまっているような気がします。

データは↓にあります。

http://wikiwiki.jp/kancolle/?cmd=read&page=%A5%B3%A5%E1%A5%F3%A5%C8%2F%C2%E7%B7%BF%B4%CF%B7%FA%C2%A4%2F%A5%EC%A5%B7%A5%D4%A5%ED%A5%B055

wikiのURLは直リンクが正しく貼られないことがあるようなので、下記のwikiTOPページから↓のようにたどればデータが多数あります。
http://wikiwiki.jp/kancolle/
Top > コメント > 大型艦建造 > レシピログ55

【質問１】
3800/2500/5000/4200/20のレシピでの、あきつ丸が出る確率を求めるにはどうすればいいか

【質問２】
他のレシピでもあきつ丸の出る確率を求め、どのレシピで一番あきつ丸が出現するか求めるにはどうすればいいか

後、私はデータ分析のやり方を学びたいのであって、単純に「出現率は何%になる」という回答を求めているわけではありません。

回答No.2

keithin

ん？
そのデータからの手順で回答済みですが、関数でごしょごしょやっつける手順を採用されたんじゃなかったんですか。

＞こういったデータを取り除くにはどうすればいいでしょうか？

元データ（エクセルに貼り付けた直後）の状態でざっくり並べ替えて、イレギュラーを集めて取り除いてしまうのが一番手っ取り早そうに見えました。
とは言え実際には人手で記入されたネタの集まりなんで、「イレギュラーのパターン」を漏れなく抽出するというのは、現実的な対処ではありません。上述のような手法で大まかな処理をした後は、個別に間違い探しをしてください。

切り離しをしたあと、各列ごとにオートフィルタでチェックしてみるのも良い方法です。

＞出現率を求めるにはどうすればよいでしょうか？

具体的に何を計算してみたいのか決まってないというか、そもそも何を計算したいのか考えるところからのご質問ですね。
とりあえず手掛かりを探るため、ピボットテーブルレポートなどで整理してみると良いでしょう。
http://www.microsoft.com/japan/office/previous/xp/suminaka/excel/sousa/2000/kihon2_1.htm

補足

最初の疑問に関して、現状は前述した通り、HTMLからエクセルにデータを抽出しただけです。

不要なデータに関しては、手作業でざっくり消していくしかなさそうですね。
オートフィルタに関して後ほど調べてみます。

具体的に何を計算するかは決まっていますよ？
【抽出したデータ】から、Aという艦娘のレシピ毎の出現率です。

例えば、「4000 4000 2000 5000 20」というレシピでの「あきつ丸」の出現率をどこかのセルに表示させたいです。