質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

2984閲覧

VBAでのデータ抽出について

k_eguchi

総合スコア10

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2016/03/06 07:40

イメージ説明
VBAで集計をとるシートを作成しております。
図のBシートの「○○費」の名称をKeyにして別シートから上段のコードを引いてきており、「○○費」の名称が記載されていない列に関してはAシートからコードの件数が多い順に左から抽出していく仕組みを作ろうとしています。
(「A264710ASR」が6件あるので左に入り「B44347846CE」と「C96264BHVBC」は2件ずつなので順番は問いません。)
図の上半分が処理前で下半分が処理後になります。

例のため10行しかデータはありませんが、実際は500行程度あり、コードも規則性のない英数字で構成されています。

Aシートからコードの件数が多い順に引いてくる仕組みが思いつけません。
自分の未熟さで恥ずかしい限りですが、ご教示頂ければ有難いと思います。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

例示のAシートのデータからこの順番のリストを作成する方法が知りたいということでいいでしょうか?
「A264710ASR」「B44347846CE」「C96264BHVBC」

1. 重複の無いコードリストを作成します。
参考ページ

2. コード別に件数を集計します。
「1」の参考ページに忠実に作ると次のようなリストになってしまうと思いますが
['A264710ASR':'A264710ASR','B44347846CE':'B44347846CE','C96264BHVBC':'C96264BHVBC']
値部分にコードごとのレコード件数を入れましょう
参考ページ
「1」の参考ページの配列に追加しているところで、ついでに件数を数えて入れれば良いと思います。
['A264710ASR':6,'B44347846CE':2,'C96264BHVBC':2]
こんな感じの配列になるはずです。

3. 件数で降順にソートします。
参考ページ
ここに書かれているのは連想配列では無いので、使うには工夫が必要かも知れません。
こんな感じの結果になるはず。
['A264710ASR':6,'B44347846CE':2,'C96264BHVBC':2]

4. 出来上がったリストをセルに出力します。
この配列のkeysをセルに出力すれば終了
「1」の参考ページで出力してる部分があるので、参考になるかと

投稿2016/03/06 08:31

編集2016/03/06 08:32
hirohiro

総合スコア2068

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

k_eguchi

2016/03/06 14:53

迅速なご回答有難う御座いました。 ご教示頂いた通りに試してみようと思い3時間ほど試行錯誤してみたのですが、 2の「値部分にコードごとのレコード件数を入れましょう 」の部分からどうコードを記載して良いのかがわかりませんでした。 再度の質問になってしまい申し訳無いのですが、ご教示頂ければ有難いかと思います。 未熟なためお手数をお願いして申し訳御座いません。
hirohiro

2016/03/06 23:56 編集

> どうコードを記載して良いのかがわかりませんでした。 一気に完成品を作ろうとせずに、小さな部品から段階的に動作を見ながら作っていくと良いと思います。 最初に、連想配列はわかりますか? --- Dim Dic, i As Long, Items, Keys Set Dic = CreateObject("Scripting.Dictionary") Dic.Add "apple", 2 Dic.Add "orange", 1 Keys = Dic.Keys Cells(1, 1) = Keys(1) ''セルA1に「orange」 Items = Dic.Items Cells(2, 1) = Items(1) ''セルB1に「1」 --- 次に、「A264710ASR」の件数を数えるプログラムは作成できますか? [2]の参考ページに殆どそのままずばりが書かれています。 次に、「1」の内容のプログラムは作成できましたか? --- For i = 2 To 8 ''セルA2からセルA8までを処理する buf = Cells(i, 1).Value ''セルの値を変数bufに格納する If Not Dic.Exists(buf) Then ''まだ登録されていなかったら… Dic.Add buf, buf ''セルの値を連想配列に登録する End If Next i --- addメソッドは「配列.add キー名, 値」なので 「Dic.Add buf, buf 」この後ろのbufの部分に個数を入れればいいですね。 bufはループが回ってくる度に'A264710ASR','B44347846CE','C96264BHVBC'と入れ替わるので、都度そのIDの個数を数えて「Dic.Add buf, 個数」とすればいいです。
k_eguchi

2016/03/08 01:09

ご丁寧に回答頂き本当に有難う御座います。なんとか作成出来ました。感謝致します。
guest

0

ヒントではなく、(いくつか考えられる解決方法の内1つとしての)解です。
どうしても自分では解決できない等の場合に下記の方法で試してみてください。


Aシートについて
Aシートは編集していませんが、運用していく中でデータが追加されることと思います。

Aシート(表示そのまま)
Aシート(表示そのまま)

Bシートについて
Bシートには
Cシートから費目に対応するコードと、
Cシートに表示した集計から順番にコードを表示しています。

Bシート(表示そのまま)
Bシート(表示そのまま)

Bシート(数式表示)
Bシート(数式表示)

この解決の要になる関数を下記で説明します。
(Excelでは関数にコメントが記入できないため、便宜上Javaコードとして記載します。)

java

1// C4セルの関数(D4より右側セルにもそのままコピペ可能) 2=IFERROR( 3 IF(C$4<>"", // C4セルに費目入力があるか 4 VLOOKUP(C$4,'C'!$A:$B,2,FALSE), // 1)ある場合、Cシートからコードを取得 5 6 INDEX('C'!$B:$B,MATCH(COUNTIF($C$4:C$4,""),'C'!$E:$E,0),1)), // 2)Cシートからランクに対応するコードを取得 7 // VLOOKUPの代わりにINDEX関数を使用してCシートからコードを取得しています。(キーとなるランク値を一番左にしなかったためVLOOKUPが使えません) 8 // 検索キーはCOUNTIF関数の結果で、セル自身より左の空白セルを数えて検索するランク値としています。 9 10"") // 費目に対応するコードが無い or (費目記入なしで)ランクに対応するコードが無い 11 // → 1、2の結果がエラーの場合、空白

Cシートについて
質問文「別のシート」を便宜上Cシートとして作成しました。

Cシートには下記を表示しています。
「費目、コードの対応表」、
Bシートに費目の記載があるかの確認欄、
Aシートでの出現回数と出現ランキング(重複を同率にしない、Bシートに記載のあるものは除外する)

Cシート(表示そのまま)
Cシート(表示そのまま)

Cシート(数式表示)
Cシート(数式表示)

以上、
ご参考になりますでしょうか?

投稿2016/03/07 01:59

Aeona

総合スコア396

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

k_eguchi

2016/03/08 01:12

有難う御座います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問