[[20080213140737]] 『同じ種類を横並びにする』(クロ) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『同じ種類を横並びにする』(クロ)
 こんにちは。いつも勉強させていただいてます。

 今回お知恵を頂きたいのは、以下のようにA列でグルーピング、
 B列を横に並べていくという表を関数で出来ないかという事です。

 A	B
 山手線	新宿
 山手線	大塚
 山手線	駒込
 銀座線	表参道
 丸ノ内線	方南町
 日比谷線	広尾
 日比谷線	六本木
 東西線	東陽町
 東西線	西葛西

 ↑これを
 ↓こう

 A	B	C	D	・・・
 山手線	新宿	大塚	駒込
 銀座線	表参道		
 丸ノ内線	方南町		
 日比谷線	広尾	六本木	
 東西線	東陽町	西葛西	

 今まではピボットで行に沿線、列に駅を入れてカウントを値に入れ、
 >=1だったら駅名を取りにいく。
 としてから空欄行を「ジャンプで選択」して「削除」で詰めてました。

 項目はさまざまですが、仕事で結構使う作業ですので、
 もっと効率良く出来ないかとご相談にきました。
 どうぞよろしくお願いいたします。

 こちらが、参考になるように思います。↓ (#REF!MAN)
[[20070410151509]]『条件に合う複数のセルの値を表示したい』(にの)
[[20070525122529]]『重複するデータを1つだけ残し、列を行に移動したい』(KI)
[[20080109142608]]『志望校別リストの作り方』(ゴーシュ)

 #REF!MANさん

 お返事ありがとうございます。
 検索にも時間をかけたんですが、やはりまだまだ
 検索マスターへの道は遠いようです。
 リンク先を参考に、VBAも考慮に入れて時短を目指したいと思います。(クロ)

 これはSheet1のデータを元にSheet2を開くと同時にデータを転送するものです。
 Sheet2のシートモジュールへ貼り付けてくらはい。
            (弥太郎)
 '---------------------
 Private Sub Worksheet_Activate()
    Dim dic As Object, i As Long, tbl, Cnt As Integer, x
    Set dic = CreateObject("scripting.dictionary")
    Application.ScreenUpdating = False
    With Sheets("sheet1")
        tbl = .Range("a1").CurrentRegion.Value
        ReDim x(1 To UBound(tbl, 1), 1 To Columns.Count)
        For i = 1 To UBound(tbl, 1)
            If Not dic.exists(tbl(i, 1)) Then
                j = j + 1
                n = 1
                dic(tbl(i, 1)) = Array(j, n + 1)
                x(j, n) = tbl(i, 1)
                x(j, n + 1) = tbl(i, 2)
            Else
                x(dic(tbl(i, 1))(0), dic(tbl(i, 1))(1) + 1) = tbl(i, 2)
                dic(tbl(i, 1)) = Array(dic(tbl(i, 1))(0), dic(tbl(i, 1))(1) + 1)
                Cnt = IIf(dic(tbl(i, 1))(1) > Cnt, dic(tbl(i, 1))(1), Cnt)
            End If
        Next i
    End With
    Cells.ClearContents
    Cells(1, 1).Resize(j, Cnt) = x
    Application.ScreenUpdating = True
 End Sub


 マクロを実行すると
シート1のデータをシート2に振り分け集積します。
 駅名(A列)は同じ駅名が重なっていなくても,
無秩序に並んでいても,最初に出て来た順に集積します。(夕焼)

 Sub test()

 ''''''''''''''''''' A列重複データ抽出整理

 Worksheets(2).Cells(1, 1) = Worksheets(1).Cells(1, 1)
 lastrow1 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

 For i = 2 To lastrow1
   nn = 0
   lastrow2 = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row

   For j = 1 To lastrow2

       If Worksheets(2).Cells(j, 1) = Worksheets(1).Cells(i, 1) Then
       Exit For

       Else
       nn = nn + 1
       End If
    Next j

   If nn = lastrow2 Then
   Worksheets(2).Cells(lastrow2 + 1, 1) = Worksheets(1).Cells(i, 1)

   End If

 Next i

 lastrow21 = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row

  '''''''''''''''''B列データ分配配置

  For k = 1 To lastrow1
    For m = 1 To lastrow21
   If Worksheets(2).Cells(m, 1) = Worksheets(1).Cells(k, 1) Then
       lastcolmun = Worksheets(2).Cells(m, Columns.Count).End(xlToLeft).Column
        Worksheets(2).Cells(m, lastcolmun + 1) = Worksheets(1).Cells(k, 2)
    End If
    Next m

    Next k

 End Sub

 弥太郎さん 夕焼さん

 急に業務に追われてしまい、お返事が遅れまして申し訳ありません。
 コードを書いていただきましてありがとうございます。
 しばらく検証とコードの解読(というかお勉強)が出来ないのですが、
 必ず実にいたします!
 忘れた頃に質問に来るやも知れませんが、その際お手すきでしたらお付き合い下さい。

 ありがとうございました!(クロ)

 VBAも考慮に入れて時短を目指したいと思います
 とありますので、僅か10000のデータですけどこれほどの差が出ることを実証します。
 新しいBookを開いて
 標準モジュールに
 Sub 仮のデータ()
    Dim i As Long, x
    ReDim x(1 To 10000, 1 To 2)
    For i = 1 To 10000
        x(i, 1) = "A" & Int(Rnd * 1000)
        x(i, 2) = "B" & Int(Rnd * 1000)
    Next i
    Sheets("sheet1").Cells(1, 1).Resize(10000, 2) = x
 End Sub
 を実行しておくんなはれ。
 AB列にデータが並びますわなぁ。

 次に夕焼けはんのtestを実行してみてくらはい。

 それがしのコードをSheet2に貼り付けます。
 戻ってSheet2を開いてみてくらはい。
 どないでっか?
 これを時短と申さずになんと申します?
 ことほど左様にセルに直接アクセスするマクロはとっても時間がかかります。
 変数のやりとりが難しいと背を向けていてはゼニ取れるサラリーマンから置いてけ
 ぼりを食いまっせ。^^
 それと、変数を宣言しないマクロは人目に晒すものではありまへん。       
         (弥太郎)


 行が少なければFor〜Nextも有効だろうが、
10000行のデータで(-.-)y-~~できる。
dictionaryの圧勝。
でも、変数宣言jとnが抜けてます…
(通)

 >でも、変数宣言jとnが抜けてます…
 ギャッ!
 どんまい、どんまい。^^
     (弥太郎) 

 >どないでっか?
 > これを時短と申さずになんと申します?

 興味あるとこですね。もったいぶらずに
時短効果をデジタル数値で発表して,差異を見せてください。
  コヒー一杯飲む時間程度は稼ぎだせましたか。

 なお,1万件の駅名は,現実にはなく,架空の差異となりますので,
現実にいくつ駅名があるかわかりませんが
 2000件,500件などのでも比較差異を比べたら,よりよい比較データと
なりますので,報告をお願いします。

(時短歓迎?)


 > 興味あるとこですね。もったいぶらずに
 > 時短効果をデジタルで発表して,差異を見せてください。
 ご自分のPCを使って試せばすぐわかることです。

 夕焼さん、HNを固定されたらいかがですか?   (Hatch)

 >1万件の駅名は,現実にはなく
 1万件のデータ処理はよくあるのではないですか?
 表示にて差異がわかるようコード化し、提示してみてはどうでしょう。
 待っているより、それこそ時短ですよ。
 (元夏バテ)


 参考までに結果発表  (時短歓迎?)
(弥太郎)はん の時間を0として場合の凡その差異

 500件  数十秒
 2000件  数分(2分〜3分程度)
1万件   中断 待ちきれずやめ。

  件数に応じて,差異自身に大きな差異がありました。
 さて,この差異をどう見るかですね。
 (速さのため,作成するコードに囚われ気にして作る意義があるかどうか)


 (Hatch)さん
 >ご自分のPCを使って試せばすぐわかることです。

 皆さんに 「どないでっか?」 と自慢するのであれば,自慢する前に
 言い出しッぺがまず,データを示すことがスジではないかという意味ですよ。
 (ほとんどの人は,確認なんてしていないのですから。間違った憶測を与える
    こともあるわけですから。)

    (時短歓迎?)


 興味のある人が試せばよいのでは?
 試すまでもないことはコードを見ればわかるかもしれませんけどね・・・
 (おっと、時間がかかりそうということがですよ)

 > 間違った憶測を与えることもあるわけですから。
 間違っていたら指摘すればよいでしょう?

 夕焼さん=時短歓迎?さん と思って書いていますけど、違いますか?
    (Hatch)

 >HNを固定されたらいかがですか? 
ここEXCELの学校に そのような,ルールはありました?。

 (Hatch)さん=(弥太郎)はん  の代弁者? でしょうか。

 >差異を見せてください。
 >報告をお願いします。
 (弥太郎)はん  にお願いしているのですが,なぜか(Hatch)さん
が回答してくるのでしょうか。

  言いたいのは,話を先導しておいて,自分で勝手に試してみなさいというのは
 あまりにも,無責任な話だということです。(本人はどう考えているのか,読んでいないのか
 わかりませんが,代弁者?なら,本人にお伝え願います。)

 > ここEXCELの学校に そのような,ルールはありました?。
 最近もこのようなことを書いている人がいたようですけど
 マナーみたいなものではないでしょうか?

 > (Hatch)さん=(弥太郎)はん  の代弁者? でしょうか。
 そうではないです。自分の考えを書いただけです。

 > (弥太郎)はん  にお願いしているのですが
 そうでしたか・・・ 余計なことに口を挟んだようですね。
 読んでいて、分かるようにはっきりと名指ししてください。

 みっともないことはこの辺で止めにしませんか?
 てか、私が消えればよいですね・・・
 私に用事があるときは名指ししてくださいね。
 
 (追記)
 > VBAも考慮に入れて時短を目指したいと思います
 を受けての
 > どないでっか?
 ですので、
 > 皆さんに 「どないでっか?」 と自慢するのであれば
 は、ちょっと違いますよね。
   (Hatch)

 > この辺で止めにしませんか?
   みっともないことについては同感です。

 >VBAも考慮に入れて時短を目指したいと・・・
    質問者は,もともと,このように言っています。私の理解は
   これまで,マクロを使わず,手作業か,関数などを駆使して
   データ整理にたぶん,何時間・何日も費やしていたのではないで
   しょうか。そこで,マクロが使えれば利用して時短短縮を計りたい
    ということですね。
     従って,これまでのことが,マクロ化(自動化)されて,時間短縮に
   繋がれば,まずはそれで満足・成功ではないでしょうか。
      つまり,マクロの内部のコードの作り方で,どれだけ早くなるか,どのコードが
   何分早いかという意味での時短とは本質的に違っているように思います。
     まずは,理解し易い,単純なコードを使って,マクロで自動化して,何日も
      かかていたことが数十分,数分で終了ということで,今回は成功ではないかと
    考えています。
       マクロ自身を使っていない人(使っていたら失礼)に,突然,コードの
    早い・遅いの論議しても,はじまらないという気がします。
      コードを書いた人の自己満足にしかならないでしょう。(時短歓迎?)


 >    マクロ自身を使っていない人(使っていたら失礼)に,突然,コードの
 > 早い・遅いの論議しても,はじまらないという気がします。
 でも、質問者さんの今までの苦労からすれば、早い事に問題はないと思います。
 ただ、コードの内容がわからないのであれば、改めて質問してもらえば良いのではないですか?

 或いは早さではなく、わかりやすい(初心者に)コードがあれば(時短歓迎?)さんも参加されては?
 (元夏バテ)

 やってきましたなぁ、それも過去の例に漏れずHNを変えてのご登場でっか。^^
 かくれミノのを装着してのご登場、ご苦労はんです。
 昔から火事と喧嘩は大きい程面白いっちゅいますからなぁ、退屈しとる方々にはええ退
 屈凌ぎになりまっしゃろ。

 とは言うものの、一昔前の喧嘩相手INAはんに比べたらなんとのう物足りない反撃でん
 なぁ^^。
 あたしゃてっきり
 >それと、変数を宣言しないマクロは人目に晒すものではありまへん。 
 の反論やと思うとりましたんやけどなぁ・・・。

 それはともかく、それがしのスレをよ〜くお読みになればお分かりになると思うんですけど
 セルにアクセスするより、変数のやりとりの方が処理速度が圧倒的に速いと申し上げた
 つもりですねんであって、たまたま同席した夕焼けはんのんを比較の対象に使わせて貰
 うただけなんですよ、えぇ。それを自慢と受け取るのはあんさんのひがみっちゅうもんですワ。
 Excel2007では百万行セルが用意されとります。どんな少量のデータを扱うにしても変
 数のやりとりは必須ですワ。(5000程度のデータでも)
 最初から難しいと尻込みしとってはあかんと力説したつもりですけど、それが夕焼けは
 んの逆鱗に触れたみたいでんな、おおこわ。

 夕焼けはんのマクロをテストして時間を示せってでっか?
 ぶるぶる、とんどもない。5秒も経ったらCtrl+Breakですワ。^^

 Hatchはん、元夏バテはん、フォローおおきに〜。
 あんまり真剣にとりあいなはんなや。^^
        (弥太郎)


 こんにちは。質問者の(クロ)です 
 過去分に流れた上に、白熱展開の記事をあげるのも躊躇したんですが、
 検証すると言った以上有言実行させていただきます。
 今回自分のスペック部分の記述が無く、ご迷惑をお掛けしました。
 VBAはマクロ記述を元に手直しして、「手動よりは断然早いけど、
 パソコンには地道に(無駄な)動作をさせる」レベルです。
 いつもこちらのサイトで「こんな方法が!」と目から鱗を落としまくっていて、
 今回は沿線ですが、それこそ1万件の案件もあるので相談させていただきました。

 弥太郎さんのコードを試してみまして、なんだか魔法のように出来てびっくりしました。
 ウォッチウィンドウを見張りながらカチカチやったところ、
 大体の流れだけは把握できた・・・でしょうか。

 配列(Tbl)に元となるデータの沿線と駅をペアで格納して、
 dictionary(dic)にユニークな沿線を入れ
 沿線名をキーに、配列(x)に沿線-駅を格納・・・
 最後の「resize」で魔法のようにsheet2に記載された部分はhelpを読んでもいまだに謎なんですが、
 色々調べて試してみたいと思います。

 夕焼さん、コードありがとうございます。
 多分自分で時間をかけてコードを書いたとしたらきっと、
 似た感じの流れで作っていたと思います。
 ここで見つける前には「Application.ScreenUpdating = False」の記述を知らず
 がちゃがちゃとグラフシートを作成するマクロを10分もまわして
 その前でボーっとしてたら、職場の人に
 「Excelが暴走して途方にくれてるのかと思った」と言われました。(笑)

 基本的には事務ですので、目の前の作業をExcel君がやってくれればそれでいいんですが、
 今まで知らなかったDictionaryやresizeを知ってしまったからには
 どうしたって活用していきたいと思う次第です。

 今回はお付き合い下さいありがとうございました。
 これからも勉強させていただきたいと思います。

 時間云々ももっともの話だけど、どうして変数の宣言もできない者が「回答」と称して
 投稿するのか理解に苦しむ...
 そのあたりから学習するべきだろうね。
 (seiya)

 御意。
     (弥太郎)^^

 少し可笑しくなってしまいましたね。また,変数宣言論議ですか。
  変数の宣言をすることがそんなに難しく。他人に自慢するようなことと思っているのでしょうか。
   正直言って,私が変数を宣言しないのは,そんなことは,どこにも書いてある,イロハで
 VBAのまず一歩でどの教科書にも最初に書いてあるあたりまえのことだからです。
そんなことを,みなさんは質問しているのでなく,コード内の処理・プロセスの流れを
知りたいと思っていると思っています。それらに繋がらない変数宣言は蛇足と思っていますから
最終的に仕上げる人が,自分で 付け足せばそれでいいからで,変数云々にかかわって
時間をつぶしたり,余計な行を増やし板を長くしたくありません。
   ここで表示するコードは質問者に考えるヒントや処理流れの構築の一例であり,
完成品や仕上がった変数宣言まで付けた代理製作のお手本を発表する場ではないと
考えているからです。
 学生の模範解答を発表しているわけではないのです。
  言ってみれば,コードの処理流れの構築と変数宣言は別物で切り離してもなんら問題はないと考えているからです。 
  最後に,皆さんで付け足してくれればそれで言いだけの話ですから。

  私自身はそんなふうに考えていますので変数宣言自身ほとんどやりませんので
  きっと良く知っていないと思います。でも,どの教科書にも書いてある基本です
 から最後に,最初に追加してください。

    夕焼のコードは,未完成のものと理解して扱ってください。

   ◆◆ 最後に仕上げて完成させるのは みなさん・あなた自身なのですから◆◆

(夕焼)


 >コード内の処理・プロセスの流れを
 >知りたいと思っていると思っています。
 どのような変数がどのように宣言される事でどんな動きになるのか、私は重要な事だと思います。
 イロハ云々より、宣言をせずにExcel任せにしているだけではないのですか?

 >最後に,皆さんで付け足してくれればそれで言いだけの話ですから。
 たぶん、全く違うコードになっているのではないでしょうか。
 (元夏バテ)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.