[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『作業列なしで重複しているコードを昇順に並び替えたい』（安針）
１行目を見出しとして使用しており、Ａ列を日付
Ｂ列に重複があるコード、Ｃ列に名前がある場合に、
コードの重複を削除して昇順に自動的に並び替えられるようにする際に、
作業列なしでやる方法を教えていただけたらと思います。
具体的には、
Ａ列 Ｂ列 Ｃ列
日付 コード 国名
2月2日 ｇ300 ドイツ
2月3日 ｄ200 オランダ
2月4日 b900 ブラジル
2月5日 h900 ハンガリー
2月6日 b100 ブラジル
2月9日 k300 カンボジア
2月10日 b900 ブラジル
2月12日 a900 アメリカ
2月13日 k100 カンボジア
2月16日 ｃ900 中国
2月17日 ｇ900 ドイツ
2月18日 k600 カンボジア
2月19日 h300 ハンガリー
2月20日 a300 アメリカ
2月23日 i200 アイスランド
2月24日 ｄ300 オランダ
2月25日 k300 カンボジア
2月26日 ｇ700 ドイツ
2月27日 j800 日本
3月2日 a900 アメリカ
3月3日 h100 ハンガリー
3月4日 b300 ブラジル
3月5日 a900 アメリカ
みたいな表です。
作業列利用する場合は、
E2=IF($B2="","",IF(COUNTIF($B$2:$B2,$B2)=1,COUNTIF(OFFSET($B$2,,,COUNT($A:$A)),"<"&$B2)+1,""))
F2=IFERROR(OFFSET($B$2,MATCH(SMALL(OFFSET($E$2,,,COUNT($A:$A)),ROW(A1)),OFFSET($E$2,,,COUNT($A:$A)),)-1,),"")
G2=IFERROR(OFFSET($C$2,MATCH(SMALL(OFFSET($E$2,,,COUNT($A:$A)),ROW(B1)),OFFSET($E$2,,,COUNT($A:$A)),0)-1,),"")
３列を下方向にオートフィル
で今までは出来ていたのですが、今回作業列（今回でいうとＥ）なしでやる必要が出てきまして。
例にあるようにコードが異なっても名前が同じ事はありますが、
コードごとに設定されている名前は一つのみです。
（今回のコードは、RANDBETWEENで適当に作っているだけですので、実際の表とは異なります、日付も本来の表では重複があります。実際のデータは、３千行〜７千行です）
恐らく配列数式かＶＢＡを使うんでしょうが、よくわからなくて教えていただけると助かります。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
重複削除は Ｂ列のみで行うのですか？ それとも Ａ列、Ｂ列のペアで重複するものを削除するのですか？ Ｂ列のみで行う場合、どれが残ってもいいのですか？、Ｂ列重複のデータでＡ列の小さなものを残すといったことが必要ですか？
アップされた式では、Ｂ列だけで判断し、たまたまＡ列に最初に登場するものを採用しておられるようですが、Ａ列は、アップされたサンプルのように あらかじめ昇順になっているんですか？
いずれにしても、エクセル標準機能の重複の削除でＯＫだと思います。（必要なら並び替えも併せて） マクロ記録をすればシンプルなコードが生成されます。
実際のデータは、３千行〜７千行です ということですから、式の埋め込みより、一発操作のほうが よろしいかと。
（β） 2015/04/24(金) 05:58
衝突しましたが やり方に拘りがなければ、 ピボットテーブルで行にコードと国名いれて、 ラベル形式、集計なしの設定にすれば 簡単に出来ませんかね？ （稲葉） 2015/04/24(金) 06:04
エクセル標準機能のほうで最初やっていたのですが、
どういう操作をしたのか履歴が残らないので問題と言われて作業列を使った関数を使うようになりました。
ただ今度は、作業列がある状態で客先には出せないと言われ、作業列なしにする方法がないかと模索しております。
（今までも隠しシートに作業列を入れてシートを非表示にしていましたが、その隠しシート自体を問題視されました。）
マクロの記録でマクロ作成だと、そのマクロを実際に実行したかの履歴がないと言われて却下されています。
逆にマクロを実行したかどうかの履歴を表示させるみたいな方法はありますでしょうか？
＞稲葉様
ピポットテーブルは思いつきませんでした。
やり方よくわかってないので、勉強してみます。
その後コードごとの合計を計算をする必要もあるので、履歴が残るようならそれもよさそうですね。
（安針） 2015/04/24(金) 06:55
>どういう操作をしたのか履歴が残らないので問題と言われて作業列を使った関数を使うようになりました。
なるほど。 通常、ＶＢＡを含むプログラム処理は、結果を作り上げますが、本当に、その処理をどうやってしたのかがわかりませんよね。 ですから、「ほんとにやったの？やった手順は正しいの？」という指摘もうなづけますね。
特に、「内部統制」で問題になりそうですね。 内部統制に関しては、一般のプログラムシステムでは、
・やった手順は正しいの？（ＩＴ全般統制の範疇） これは、本番前にシステムテスト、ユーザーアクセプタンステストを通して、第三者の検証を取得。 で、開発環境から本番環境に移行するのは、開発部隊ではなく管理部隊が行う。 本番移行手順を守らない移行はしない（やろうとしてもできない）
こういったことが担保されていることが必要で、逆に言えば、これが担保されていれば、ＯＫとみなします。
・ほんとにやったの？（業務統制の範疇） これについては、実行ログを記録し、ちゃんと実行しているか、おかしな実行がされていないか、 それを、第三者が検証できる仕組みがあればＯＫとみなします。 よりシビアに運用するなら、この実行にあたって、実行権限を持つ人だけが実行できるような仕掛けにしておくということも 統制上、有効な手段ですね。 また、実行結果と、元データをリバース検証して、間違いなく（もれなく）結果が作成されたという 検証プログラムを随時実行できる（あるいは毎回リバース検証を行う）ということを要求されることもあります。
いままでＶＢＡの世界で、このようなことを考えたこともなかったのですが、う〜ん、なるほどですねぇ。
（β） 2015/04/24(金) 07:41
追伸です。
履歴を要求される場合、「エクセル上の操作」は、一切ダメでしょうね。 たとえ、重複削除一発で、かつ正しく処理されたとしても、その様子（というか画面）をビデオにでも撮っておかなければ 確認できませんからね。対象範囲をすべて選んだか、一部しか選ばなかったかということもわかりませんもんね。
これを解決しようとすれば、まずは関数でしょうね。 ただ、もう１つ関数と同じものが、いわゆる「マクロ」と呼ばれるものですね。 操作を束ねてＶＢＡ化したものですが、提案した重複の削除をＶＢＡコード化すると「１行」になりますし どのシートのどの範囲を対象に行ったかも、その「一行」に記載されていますので、それを見てくださいと。
あとは、先にレスで申し上げたことが担保されていればＯＫだと、β的にはそう思います。 関数は検証できるけどＶＢＡコードはわからないからだめだという上司（あるいは客先）なら、どうしようもないですけど。
（β） 2015/04/24(金) 08:08
＞その後コードごとの合計を計算をする必要もあるので、履歴が残るようならそれもよさそうですね。 ピボッドなら、コードごと集計、むしろそうするための機能ですよ！！
履歴、が何を指すかわかりませんが、ピボットなら生データは残りっぱなりで、生データの値が変わるごと に更新されます。 もし、データの変更があるごとに、前の状態を残す必要があるなら、 範囲をコピーして値だけ保持するしかないと思いますが・・・ それは関数も同じだと思うので違うのかな？
βさんの解説、大変勉強になります！ βさん的にはピボットだめですかね？
（稲葉） 2015/04/24(金) 08:12
>βさん的にはピボットだめですかね？
たぶんＯＫだと思います。 たぶん と申し上げたのは、βはピボットが全くわからないから。 （何年か前に勉強しなきゃということで参考書を買ったんですが、まだ１ページも読んでいません・・・涙）
おそらく、設定項目があって、それをいじらないようにする。（いじること、そのものをできなくしておく） で、実行日時と設定（変更）日時に矛盾がないという仕掛けにしておけばＯＫだと思います。
（β） 2015/04/24(金) 08:20
なるほど 「設定に間違いがない」ことを証明するのは難しいかもしれません・・・
（稲葉） 2015/04/24(金) 09:06
管理用のファイルと提出用のファイルを分けてはどうでしょうか。 現在のファイルを管理用として、提出用を都度新規ファイルとして作成して、 シート名やファイル名（あるいはセルに）にマクロの実行時間情報を置くようにすれば、 マクロでいつ作成したものかが判別できるかと思います。
マクロはフィルタの重複の削除をマクロの記録で取るくらいでもできそうな気はします。 シートやブックの保護をかけて編集できないようにする、あるいは PDF として保存する なども考えられそうです。 （Mook） 2015/04/24(金) 09:31
＞実際のデータは、３千行〜７千行です）
7千件に数式での計算速度が間に合うか分かりませんが、結果がよければ使ってください。
現在でもちょっと無駄な計算がありますので、それは外に出した方がいいと思います。 ↓ COUNT($A:$A) これを式の数だけ同じ計算させるのは無駄すぎます。
ただ、そうは言っても「外に出す」のは結局作業セルを使うと云うことになって 変に神経質な上司の許可が得られないかも知れません。
そこで、「外」とはF1セルとG1セルにして、セルの書式(表示形式)をユーザー定義で "対象"0"行" "結果"0"件" とし、カモフラージュする。
(1) F1セル =COUNT($A:$A) (2) G1セル =COUNTIF(F:F,"*?") (3) F2セル =IFERROR(INDEX(B:B,MATCH(SMALL(INDEX((MATCH($B$2:INDEX($B:$B,$F$1),$B$2:INDEX($B:$B,$F$1),0)+1<>ROW($B$2:INDEX($B:$B,$F$1)))*($F$1+1)+COUNTIF($B$2:INDEX($B:$B,$F$1),"<"&$B$2:INDEX($B:$B,$F$1)),0),ROW(A1)),INDEX((MATCH($B$2:INDEX($B:$B,$F$1),$B$2:INDEX($B:$B,$F$1),0)+1<>ROW($B$2:INDEX($B:$B,$F$1)))*60000+COUNTIF($B$2:INDEX($B:$B,$F$1),"<"&$B$2:INDEX($B:$B,$F$1)),0),0)+1),"") (4) G2セル =IF(F2="","",VLOOKUP(F2,$B:$C,2,FALSE))
F2:G2を下にコピー
<結果図> 行 ___A___ ___B___ ______C______ _D_ _E_ ____F____ ______G______ 1 日付 コード 国名 対象23行 結果19件 2 2月2日 ｇ300 ドイツ a300 アメリカ 3 2月3日 ｄ200 オランダ a900 アメリカ 4 2月4日 b900 ブラジル b100 ブラジル 5 2月5日 h900 ハンガリー b300 ブラジル 6 2月6日 b100 ブラジル b900 ブラジル 7 2月9日 k300 カンボジア ｃ900 中国 8 2月10日 b900 ブラジル ｄ200 オランダ 9 2月12日 a900 アメリカ ｄ300 オランダ 10 2月13日 k100 カンボジア ｇ300 ドイツ 11 2月16日 ｃ900 中国 ｇ700 ドイツ 12 2月17日 ｇ900 ドイツ ｇ900 ドイツ 13 2月18日 k600 カンボジア h100 ハンガリー 14 2月19日 h300 ハンガリー h300 ハンガリー 15 2月20日 a300 アメリカ h900 ハンガリー 16 2月23日 i200 アイスランド i200 アイスランド 17 2月24日 ｄ300 オランダ j800 日本 18 2月25日 k300 カンボジア k100 カンボジア 19 2月26日 ｇ700 ドイツ k300 カンボジア 20 2月27日 j800 日本 k600 カンボジア 21 3月2日 a900 アメリカ 22 3月3日 h100 ハンガリー 23 3月4日 b300 ブラジル 24 3月5日 a900 アメリカ
※OFFSET()は揮発性関数なので使用を避けました（Index関数にします）。
（半平太） 2015/04/24(金) 11:47
ピポットテーブルかｄINDEXで行く方向で勉強しています。
半平太さんのINDEXはある程度ずつ区切って、どういう理由でどう動いているのかを見させていただいているのですが、正直この発想はなかったの連続です。
(INDEXはMATCHとの組み合わせで検索ぐらいにしか使ってませんでしたし、揮発性関数について考えていませんでした。)
（安針） 2015/04/26(日) 12:59
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.