エクセル：外部ファイルからの集計

質問

よろしくお願いします

自動計測端末からフィニッシュした人のデータがcsv（ゼッケンナンバー）形式で記録されてきます。

このcsvデータをエクセルへペーストして、エクセルファイル内の選手データ（ゼッケンと名簿）にチェック（色分けか、消し込み）したいのですが

トラブル発生の場合でも、簡単に修正が可能だとありがたいです。

エクセルの選手データーは、ゼッケンと選手名が記載されています。

自動計測のcsvファイルにはゼッケンナンバーが随時記録されてきます。

フィニッシュが完了していない人を抽出したい。
フィニッシュできた人の消し込みか色分けで未フィニッシュ者の抽出をしたいのです。

９月３日に競技があるので、それまでに作成したいのですが、よろしくお願いします。

回答No.3

g_nekoru · ベストアンサー

#1です。
小学生から年配者までと言うとさっきの方法はちょっと難しいかもしれませんね。

先ほど記入した関数ですが以下のように変更してください。この方が処理が軽くなります。
=IF(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)),"",1)

それと、色分け限定になりますが、
オートフィルタ以降の説明部分（後はsheet1を表示した状態で・・・以降）を下記のようにするとcsvファイルを貼り付けるだけで自動で色分け出来るようになります。

sheet1の列番号"A"と"B"を範囲選択してください。
（Aと書かれた部分でクリックしてボタンを押しっぱなしにしてBまでマウスポインタを移動させます）
次にメニューバーから
「書式」→「条件付き書式」をクリックしてください。
条件付書式の設定が開くので条件(1)の内容を左から
「数式が」「=$C1」
にして下さい。
そして、右の方にある「書式」ボタンを押してください。
セルの書式設定が開くので
「パターン」タグをクリックし表示されたカラーパターンから好きな色を選んでください。
色を選んだら「OK」ボタンを押してセルの書式設定を閉じてください。
続いて条件付き書式の設定画面で「OK」ボタンを押してください。
csvファイルのデータを貼り付けると自動で選んだ色がフィニッシュした人のセルの色になります。

補足要求ですが、
ゼッケン番号がどのようになっているか教えてもらえるでしょうか？
ゼッケン番号が「00001」などのように0から始まるとcsvを開いたときに「1」に修正されてしまい正常に判定できなくなる可能性があります。

補足

No１(g_nekoru)さんありがとうございます

ゼッケンは４桁で、タイプ別に番号が振られています。例Ａタイプ　1001～1234　Ｂタイプ1500～1600の形式です。（不定期にCSVファイルを書き込む）
1～の形式もあるのですが、頭に0001表記していません。（そうですね。処理を考えておかねば）

CSVファイルの更新が追記形式か上書きかまだ分かっていません。

VLOOKUPで指定した場合なのですが、重複してペーストした場合VLOOKUPの範囲内で処理できれば、重複ゼッケンナンバーをペースト処理しても処理できますでしょうか？（CSVデータが更新されるたびVLOOKUPのデータを更新するか領域を増やす方法がよいのか）

そうすれば、フィニッシュするたびCSVのペーストを行い残ったゼッケンが行方不明（未計測）となると思うのですが。

回答No.7

g_nekoru

すいません、前の回答でソートしてと記載していましたが、今回の場合はVLOOKUP機能で一致するものだけを抽出しているのでソートは必要ありません。

VLOOKUP機能の一つに近似値（一致する物がなかった場合対象値以下の最も大きい数値）を抽出すると言うのがあるのですが、その機能を使う場合はソートが必要になります。

VLOOKUP(検査値,範囲,範囲内の列番号,検査条件)
検査条件：TRUE：近似値を抽出する
　　　　　FALSE：近似値を抽出しない

ボタンの作成方法はあっていると思います。

お礼

g_nekoruさん
ありがとうございます

もう今から追加機能を考える時間もありません
最終選手マスターを作成して
皆さんから教えていただいた関数を使ってみます

ありがとうございました

回答No.6

g_nekoru

カウントの関数はいくつかあり
COUNT()　　()内の数値の個数を数える
COUNTA()　 ()内の文字列(数値含)の個数を数える
COUNTBLANK()　()内の空白セルの個数を数える
COUNTIF(範囲,条件)　範囲で指定した中で条件と一致する個数を数える
と言ったところだと思います。

フィニッシュ者をsheet2、リタイヤ者をsheet3として、関数部分を下記のように変更して、
=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)),if(ISNA(VLOOKUP(A2,Sheet3!$A:$A,1,FALSE)),"","リタイヤ"),"おめでとう")

=COUNTIF(上記関数を入力した範囲,"おめでとう")
=COUNTIF(上記関数を入力した範囲,"リタイヤ")
でフィニッシュ者とリタイヤ者の人数を出せると思います。

お礼

g_nekoruさん

ありがとうございます
通常のEXCELのHELPだとCOUNTを参照するようですが

いろいろあるのですね。

また追記で作成してみます。

テストでトレースしても本番終了まで緊張します。

補足

tri_001さん

再度確認させていただきたいのですが
sheet2計測CSV貼り付け側なのですが、貼り付け後にソートすべきでしょうか？
ソートが必要であれば、ボタンを作成したほうが楽になると思いまして

ボタン作成方法なのですが
新しいマクロの登録
X列ソート
登録終了

フォーム－＞ボタンでよろしかったでしょうか？

宜しくお願いいたします。

回答No.5

g_nekoru

＞","で区切られているはずなので
それであればゼッケンナンバーのみ抽出できますね。

＞=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)),"","おめでとう")
可能です。

＞もし、途中計測があったとした場合の人数カウントなのですが
・・・
途中チェックとリタイヤの人はどういう形でデータが入ってくるのでしょうか？

お礼

g_nekoruさん
たびたびありがとうございます

計測機材に余裕があればデータ取得可能なのです。

形式は同じなので、エクセル側の書式は

エントリー人数（スタート前入力）
計測ポイントA
計測ポイントB（折返し）
フィニッシュ

総エントリ人数－スタート人数=参加人数
参加人数－ポイントX=通過確認
総エントリー－フィニッシュ人数=総フィニッシュ人数

このようにできれば、ポイントで計測できなかった選手を確認できると思いまして（計測発信器紛失など）

リタイヤに関しては、発見時手動入力になると思います。
例
リタイヤを（キャンセル含む）シートに"1"を入力して（ビットを立てる）
集計でリタイヤを外してカウントできれば残りの人数が有効選手になると思うのですが

安易にビットを立てると考えましたが、カウント処理の関数が他にあるのでしょうか？

よろしくお願いします。

回答No.4

g_nekoru

#1です。
csvファイルが常にフィニッシュ者全員のデータが入ってくるのであればsheet2のA列データに上書きで貼り付ければ大丈夫です。
上書きで最新のフィニッシュ者のデータしかない場合はsheet2のA列に記入してあるデータは消さずにその下に追加していけば大丈夫です。
ただし、上書き型の場合取り込む前にデータが更新されてしまうと登録されない人が出てきてしまうかと思います。

VLOOKUPの範囲の指定はsheet2のA列全体になっているのでA列に上書き（追加）する分にはVLOOKUP関数を変更する必要はありません。また、csvのデータを重複して入力しても問題ありません。

あと#2さんへの補足としてcsvには発信器ID　ゼッケンナンバー　通過時間が含まれているという事なので単純にゼッケンナンバーだけを抽出して張り付けることができません。
csvの正式なファイルレイアウトが必要です。発信機IDの桁数、ゼッケンナンバーの桁数、通過時間の桁数、それぞれの区切り（空白やカンマなど）の有無。
抽出方法を考えてあるのであればよいのですが。

お礼

#1(g_nekoru)さん
ありがとうございます

いまシートに最終データーをシート２へコピーしました。
これからシート１を作成してみます。

＞csvの正式なファイルレイアウトが必要
","で区切られているはずなので、別途excelでファイルを開いてコピーしようと思っています。

不明点があればHELPします

よろしくお願いいたします

補足

#1(g_nekoru)さん

関数
=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)),"",1)
で実行してみました。
テストデータ（重複あり）でフィニッシュ者に"１"表記ができました。
シート1のソートで、未計測者がわかるようになりました。

このIF関数ですが任意の文字も可能でしょうか？
例
=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)),"","おめでとう")

質問の中で色分けと消し込みと記述しましたが、フィルタを使えば抽出できるので初心者に伝えられそうです。（簡易マニュアルを作成します）

もう一つお願いがあります。

もし、途中計測があったとした場合の人数カウントなのですが
全競技者数（スタート前）－(マイナス)途中チェックとリタイヤ－(マイナス)フィニッシュ者数＝途中行方不明者数
と計算できますか？
今回教えていただいた応用で、各ポイント集計、フィニッシュ集計とすればできそうなのですが

よろしくお願いいたします

回答No.2

Wendy02

こんにちは。

本当は、レイアウトがあると助かるのですが。

こちらの想像で、データを作るのには限界がありますから、間違っていたら、ご自身で修正してください。

CSVデータは、貼り付けた場合に、マトリックス（格子形）か、列一つに出てくるか、分りませんが、汎用性を考えてマトリックスにします。（#1の方は、列一つですね。）

Sheet2のA1:D10 とします。
(A1:A40 でも、やり方は同じです。)

Sheet1のA列をゼッケン番号,B列を選手名

Sheet2!A1:A10
35　　 4　　30　　 9　　
13　　 1　　21　　 3　　
34　　40　　28　　17　
7　　 25　　18　　20　　
33　　12　　27　　15　　
23　　10　　32　　11　　
24　　39　　 8　　 2　　
22　　 5　　19　　26　　
29　　16　　38　　36　　
37　　 6　　31　　14　　

この範囲を、挿入－名前－定義

名前
HANI （任意）

参照範囲
=Sheet2!$A$1:$D$10

Sheet1のA列をゼッケン番号,B列を選手名とします。
Sheet1のA1にセルポインターを置き

書式－条件付書式－
「数式が」
「=COUNTIF(HANI,$A1)」

書式－パターン－色づけ
　　　（またはフォント）
[白なら消しこみになります]

OK-OK

で、後は、A1にセルポインターを置いたまま書式コピー（ほうきのツールアイコンをクリック）で、A1から、選手のゼッケン番号が入っている場所すべてを選択。
－または、A1をコピー－範囲を選択して、マウス右クリック－形式を選択してコピー、書式(T)

なお、その範囲を、B列に書式コピーすれば、B列も色が付きます。

お礼

No２（Wendy02）さん回答ありがとうございます

データ形式は、
CSV
発信器ID　ゼッケンナンバー　通過時間
12345 123 13:45:00

エクセル側は選手マスターですので、氏名、ゼッケンなどとなります。

No１さんNo２さんの式を入れて、テストしてみます。

ボランティアが重要な計測業務を行っているので（しかもいろいろなオペレーターが運用）、困っています。

状況によっては、計測業者の発信器が装着されないでフィニッシュする可能性もありますし(目視確認も必要）発生しそうな問題が山積みです。

なんとか、行方不明（フィニッシュ計測）を防止したいので・・・

宜しくお願いします。

回答No.1

g_nekoru

エクセルの選手データがシート名：sheet1にありゼッケンナンバーがA列、選手名がB列にあるとします。
データはゼッケンナンバーで昇順にソートしておいてください。

csvファイルを開きデータを選手データのあるブックのシート名：sheet2のA列に貼り付けます。
csvファイルに変更があった場合は張り直してください。
張るたびにゼッケンナンバーで昇順にソートしてください。

sheet1のセルC1に
=IF(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)),"",VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))
と入力してデータの数だけコピーしてください。
入力後C1セルをアクティブにしてセルの右下にマウスポインタを合わせるとポインタが「＋」に変わるのでその状態でダブルクリックするとコピーできます。

後はsheet1を表示した状態で
メニューバーから「データ」→「フィルタ」→「オートフィルタ」をクリックしてください。
セルA1～C1の右側に下向き三角のボタンが表示されると思うのでC1のボタンをクリックして「空白セル」をクリックしてください。
それで、フィニッシュした人は表示されなります。
csvデータを追加したらもう一度「空白セル」を選びなおしてください。

色分けしたい場合は「空白以外のセル」をクリックし表示されている選手全てを選択し色をつけて「すべて」をクリックすれば色分けできます。

お礼

早速の回答ありがとうございます

テストデータを作成してやってみます

簡略化をお願いしている理由が当日ボランティアが交代で運用するため（年齢層が広範囲　小中学生から年配者まで）です。

リタイヤは随時更新して、フィニッシュ時の計測データ（CSV）をペーストして未完走者を抽出したいのです。
予算の都合上選手フィニッシュ計測データしか取得できません。

No１(g_nekoru)の方式で抽出テストをさせていただきます。もし、他の方法がありましたら、宜しくお願いいたします。