Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
IDごとの出現回数の算出(高速処理方法)
投稿日時: 18/01/20 17:37:07
投稿者: ohlq

お世話になります。
先日高速処理のための勉強をしたので、より知識向上のためにご教示お願いおば。
 
同じIDが複数行出現するテーブル(シート)がある際に、
IDごとの出現回数をカウントするケースが私にはよくあります。
countifで処理すれば良いのですが、対象テーブルが数10万行あるケースでは
対象範囲が広いため処理速度が厳しくなります。
 
今までは全IDに対する出現回数のMaxを先に確認して、そのMaxの回数に合わせて対象範囲を手動で制限するという非常にみっともない処理をしていたのですが、
もっとすっきりとした方法があるでしょうか。
(もちろん関数を利用した可逆性ありの処理で)
なお、元のテーブルはIDでソート済みの状態です。
 
 
 
処理例----- 対象10万行のデータ
A(ID)
1
1
1
1
2
2
3
3
3
3
4
・・・
 
であれば次のように処理したい
A(ID) B(出現回数)
1  4
2    2
3    3
4    1
 
今までは全IDに対してMax回数が4ということを先に確認し
=counfif(A1:A10,A5)のようにして選択範囲A5のプラスマイナス4くらい(A1:A10)として、数式をコピペ処理していました。
 

回答
投稿日時: 18/01/20 18:05:38
投稿者: んなっと

速度優先でしたら関数は使わず、ピボットテーブルがおすすめです。
10万行でも一瞬です。
 
   A
 1 ID
 2  1
 3  1
 4  1
 5  1
 6  2
 7  2
 8  3
 9  3
10  3
11  3
12  4
13  5
14  5
15  5
16  5
17  6
18  6
19  6
 
 A列を選択して
→[挿入] [ピボットテーブル]
→"ID"フィールドを[行ラベル]と[Σ値]に
 
     A         B
3 行ラベル データの個数 / ID
4     1         4
5     2         2
6     3         4
7     4         1
8     5         4
9     6         3

投稿日時: 18/01/23 10:32:46
投稿者: ohlq

回答ありがとうございます。
 
申し訳ありません、ピボットは無しの方向でお願いします。
出現頻度は作成したい列の1つであり他の列も含めたテーブルを作成したいためです。(下記例でC列、D列と処理が続く・・・)
 
また、私の経験上ではピボットテーブルは非常に重たく(特にピボットテーブルの結果を参照した場合は)、基本的に避けたいのですが。

回答
投稿日時: 18/01/23 11:21:49
投稿者: もこな2

横から口出しになってしまいますが。。。

ohlq さんの引用:
出現頻度は作成したい列の1つであり他の列も含めたテーブルを作成したいためです。(下記例でC列、D列と処理が続く・・・)
よくわからなけど、ピボットテーブル挿入すると行がずれるからダメってことでしょうか?
それなら単純に別シートにピボットテーブル作れば解決しません?
 
ohlq さんの引用:
また、私の経験上ではピボットテーブルは非常に重たく(特にピボットテーブルの結果を参照した場合は)、基本的に避けたいのですが。
そりゃたくさんの列対象にしたら重いんでしょうけど、たとえばIDがA列にあるのであれば、A列だけを対処にピボットテーブル作ればいいんじゃないでしょうか?
「行ラベル」と「値」に同じ項目入れれば一瞬で出てくるような・・・・
 
>特にピボットテーブルの結果を参照した場合は
というのは状況がよくわかりませんでした。ピボットテーブルからピボットテーブルを作るってことでしょうか・・・でもそんな回りくどいことする必要ないですよね・・・
 
外してたらすみません。

回答
投稿日時: 18/01/23 11:29:18
投稿者: Suzu

横から失礼します。
 

引用:
対象テーブルが数10万行あるケースでは
対象範囲が広いため処理速度が厳しくなります。

 
速度が重要であるなら一度は目を通しておきましょう。
【Excel のパフォーマンス: 計算パフォーマンスの向上】
https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/excel-improving-calcuation-performance
 
 
引用:
ピボットテーブルは非常に重たく(特にピボットテーブルの結果を参照した場合は)

 
レコード件数が多く、速度も重要、ピボットテーブルの結果を更に参照する事も必要
 
・マクロを使う
・データベースに移行する
 
のどちらかを採るのがベターと思います
 
 
腑に落ちないのが、
全て関数で処理し、数式を恒久的に変えなくとも結果だけを得たいのは判るのですが
 
今まで、
引用:
数式をコピペ処理していました。

これを行っているのであれば
 
ピボットの結果をコピーし、値の貼付けを行っても良いのでは?
 
 
結局、並べ替えもするし、その他の事も手動で行うのですよね。
 
数式を修正しなくとも恒久的に使える関数にしてしまうと
ファイルを開く→処理→(保存)→閉じる
の全体時間としては増えませんかね?
 
極論を言ってしまえば、関数の作成者の自己満足になってしまいませんか。

回答
投稿日時: 18/01/23 11:55:47
投稿者: bi

ソートされているとのことなので
 

	A	B	C	D
1	ID			
2	1		1	
3	1		2	
4	1		3	
5	1		4	
6	2			
7	2			
8	3			
9	3			
10	3			
11	3			
12	4			

D2:=MATCH(C2,A:A)-MATCH(C2,A:A,0)+1
下方向にコピー
 
COUNTIFより若干処理は早いと思います。

投稿日時: 18/01/23 12:39:33
投稿者: ohlq

皆さまご指摘・ご回答ありがとうございます、
(んなっと様を含め以前にもなんどもお世話になっている方々ですね、感謝)
 
・ピボットテーブルを避けたい理由
今回はIDの出現頻度を結果として算出しますが、その結果をさらに参照する必要があります。ピボットテーブルで算出した結果のセルを参照すると極端に重たくなってしまいます(特にMac環境では)
>特にピボットテーブルの結果を参照した場合は
の意味はピボットテーブルで算出したセルをさらに参照するという意味でした、申し訳ありません。
 
・【Excel のパフォーマンス: 計算パフォーマンスの向上】
ありがとうございます、先日揮発性関数というのを知り意識するようになりました。よく読んできおきます。
 
・マクロ使う
これは個人的趣味なのですがマクロを使うレベルに移行するのであれば別言語で処理したいなぁ・・・と。
 
・データベースに移行
エクセルのデータベース機能を知りませんでした。調べます。おすすめですか?
 
・結局、並べ替えもするし、その他の事も手動で行うのですよね。
前提の説明が悪くて申し訳ありません。最初からソート済みのデータで、手動で行う作業はありません。
 
・数式を修正しなくとも恒久的に使える関数にしてしまうとファイルを開く→処理→(保存)→閉じるの全体時間としては増えませんかね?極論を言ってしまえば、関数の作成者の自己満足になってしまいませんか。
全体時間として増える、というのはごもっともです。ですが当方としては「値貼り付け禁止」が必須条件、関数での処理をできるだけ早くしたい。というのが目的なのです。
*この返信を書いた後に思いましたがcountifの範囲を限定範囲にして処理するのは「手動」の行為に相当し、恒久性という意味では値貼り付けと同等の行為ですね・・・。後付けですが、だからこそ回避したい処理方法でもありますね。
 
・match
【Excel のパフォーマンス: 計算パフォーマンスの向上】 を読んだ後に何故速いかまで理解するようにします、ありがとうございます。

回答
投稿日時: 18/01/23 18:32:26
投稿者: Suzu

引用:
・データベースに移行
エクセルのデータベース機能を知りませんでした。調べます。おすすめですか?

 
ここで私が言っているのは、Excelの機能としてのデータベースではなく
Excelとは別の Oracle、SQL-Server、Access などの、アプリケーションとしての データベースです。
 
Excelからそれらのデータベースに Microsoft Query を介し接続する事が可能です。
 
 
定型形式のフィールド(列)に値を入れておき、
それらの値を、特定条件に従い、抽出し集計計算を実施するのに長けています。
 
ですから、
・データの蓄積
・最終表示に近い形までの値の演算
までをデータベース側にて実施し
 
数値や文字を見せる 部分は、データベースアプリケーションよりExcelの方が勝っていますので
表示部分をExcelに任せます。
 
 
引用:
・結局、並べ替えもするし、その他の事も手動で行うのですよね。
前提の説明が悪くて申し訳ありません。最初からソート済みのデータで、手動で行う作業はありません。

 
という事は、
元々は 別のシステム/アプリケーションから吐き出されたファイルをExcelに取りこみ
操作しているのではありませんか?
 
そのファイル形式は何でしょうか?

回答
投稿日時: 18/01/23 19:08:05
投稿者: Suzu

仮に、C:\DATA\DATA.xlsx ファイル シート Sheet1 に
 

	A
1	値
2	1
3	1
4	1
5	1
6	2
7	2
8	3
9	3
10	3
11	3
12	4

 
のデータがある場合。
 
これを、Microsoft Query を使い
 
値	個数
1	4
2	2
3	4
4	1

 
の様に集計します。
 
操作方法はExcel2010の場合。
 
1. データタブ 「その他のデータソース」から「Microsoft Query」 を選択
2. データソースの選択 にて、「データベース」タブの 「Excel Files*」を選択し、OK
   ブックの選択にて、DATA.xlsx を選択。
3.「クエリウィザード - 列の選択」 にて 「>」を選択すると
  右側の クエリの列 に 【値】が表示されるので、「次へ」
4.「クエリウィザード - データの抽出」では、そのまま「次へ」
5.「クエリウィザード - 並べ替え順序の設定」でも、そのまま「次へ」
6.「クエリウィザード - 完了」では、
  「Microsoft Query でデータの表示またはクエリの編集を行う」を選択し「完了」
7.「Microsoft Query」画面が表示されますのでボタンの「SQL」をクリック
8.「SQL」 の SQLステートメントに
     SELECT `Sheet1$`.値
     FROM `C:\Data\DATA.XLSX`.`Sheet1$` `Sheet1$`
が表示されていると思います。
    これを、

      SELECT `Sheet1$`.値, Count(`Sheet1$`.値) AS '個数'
      FROM `C:\Data\DATA.XLSX`.`Sheet1$` `Sheet1$`
      GROUP BY `Sheet1$`.値
      ORDER BY `Sheet1$`.値

    に書き換え「OK」を押すと
値	個数
1.0	4
2.0	2
3.0	4
4.0	1

が得られます。
 
9.「Microsoft Query」ウィンドの右上 「x」を押すと Excel画面に戻り
   「データのインポート」が表示されますので そのまま 「OK」
 
値	'個数'
1	4
2	2
3	4
4	1
5	4
6	3

 
が得られます。
 
赤文字の部分は、データベースを操作する為の言語「SQL」と言う物で、
VBAとはまた別モノです。
 
が、これを覚えると、集計関係はとても楽ちんです。
 
 
今回の Microsoft Query は データベースの基礎的な機能を持ったツールです。
が、使いにくい 。。
 
金銭的・時間的 余裕があり、意欲があるなら Access をおススメします。
Accessなら、SQLを覚えなくとも、上記の集計はGUIのボタンを押す等の操作のみで可能です。

投稿日時: 18/01/23 20:25:36
投稿者: ohlq

Suzu様
 
私が取り扱うデータは第3者が投げてくるデータで大体はcsv(たまにexcel)になっています。
向こう側が抽出したデータを送ってくるのですが、大元のデータベースの構造は「大体は謎」です。
 
さて、
>ブックの選択にて、DATA.xlsx を選択。
の際に「このデータソースには表示できるテーブルがありません」が出てきましたが、上記エラーメッセージで調べまして、何やら色々したところ最終的に集計まで成功しました。
エクセル上からSQL使用できるのは素直に感動でした。ありがとうございます。
(ちょうど今別件で3TBのデータベースをmysqlでチョメチョメしてます)
 
エクセルのデータタブの中身今まで気づいていませんでした、自分の環境化ではFileMakerからのインポートもできたようでびっくりしております。ACCESSで遊んでみるのも楽しそうですね。
 

回答
投稿日時: 18/01/24 10:32:34
投稿者: Suzu

大本の構造は判らなくとも、送られてくるデーターの構造は決まっているのですよね。
 

引用:

何やら色々したところ最終的に集計まで成功しました。
エクセル上からSQL使用できるのは素直に感動でした。ありがとうございます。
(ちょうど今別件で3TBのデータベースをmysqlでチョメチョメしてます)

 
という事は、
今回の処理も、SQLを使えばもっと高速に処理できる事はご理解されていると思います。
 
CSV または xlsx を SQL にて操作するのは、
・MileMaker に CSV または xlsxファイル を取り込み、FileMaker にて操作
・EXCEL から Microsoft Query を経由させ CSV または xlsxファイルをデータベースとして扱う
にて可能でしょう。
 
最終目的へのデータの演算方法次第でしょうが、選択肢としては色々ありますから
その中から実際の 操作性、常例的な操作のしやすさ、等含め テストを行い決めれば良いでしょう。
 
  ※ここでの常例的は【数式を修正しなくとも恒久的に使える関数】の様に、
   EXCEL等内での関数の範囲変更を含めた操作の少なさ を意図しています。
 
-------------------------------------------------------------------------------------
一応、CSVファイルの Microsoft Query での操作方法
-------------------------------------------------------------------------------------
1. Microsoft Query データソースの選択にて「<新規データソース>」を選択「OK」
2. 新規データソースの作成 にて
      1.新規データソース名を入力 適当に決めます 仮に【TB】とします。
      2.アクセスするデータベースの種類に対応するドライバーを選択してください
        「〜*.CSV〜」を含むドライバーを選択
        (ドライバにより違いがあるかも知れませんが、
        その違いまでは把握できていません)
      3. 接続をクリックし、必要な情報を入力してください
          CSVファイルの存在するフォルダを選択
    「OK」
3. データベース タブ に 【TB】が作成されたと思います。
    その【TB】を選択し「OK」
4. クエリウィザード - 列の選択 にて、CSVファイルを選択
 以下省略

トピックに返信