エクセル 関数 データを並び替えに合わせる

質問

元シートC列にID番号があり、EC列まで受注情報や顧客情報が入っています。
C列のIDは重複、空白が混ざっています。

シートはロックをかけており、マクロのみで行の追加が行えます。
同じ顧客が複数受注することがあるので、マクロで行追加をする際には、選択行の真下に選択行を丸々コピペし、受注内容だけを削除するようにしています。
行の削除は管理者のみが行えます。
並び替えはIDや会社名など数列、マクロで行えるようになっています。

しかし顧客情報を変更する場合は複数行忘れずに変える必要等の不便があるので、別シートに各IDのデータを集めた顧客シートを作成し、そこから読み込むようにしました。

ユーザーフォームで元シートから情報を読み込み、編集は顧客情報の部分は顧客シートへ、受注内容は元シートへ記述します。
現時点では元シートへの直入力もできる状態です。

顧客シートはA列にIDを表示し、
=IF(COUNTIF(元!C$5:C6,元!C6)>1,"",元!C6)
をドラッグして、元シートにIDが追加されても重複しないIDを表示し、その隣へ顧客情報を並べています。

ところが、元シートをIDやその他の列で並び替えた時、顧客シートで読み込んでいるIDも並び替わってしまい、その横の顧客情報と一致しなくなってしまいます。

1.　元シートを並び替えた時、顧客シートの情報はIDとマッチさせたままにする
2.　元シートに新規追加した際、IDが空白の場合は、仮IDとして自動で【仮1】などを順に振っていく。後で編集可能

顧客シートは現在A列が元シートから引っ張ってきたIDですが、ID列以降横に顧客情報が並べられれば、A列の前に作業列を数行挿入してもOKです。
新規顧客は初めからIDが無いため、ID無しでその他の顧客情報を記録するところから始まります。
しかし顧客情報はIDを基に管理したいので、ID発行までは仮IDを自動で振りたいと思っています。

どうぞ宜しくお願いいたします。

回答No.4

noname#208392

＃２です。
失礼。間違えて理解していました。

そうしたら、もう一つ中間シートを作ったらどうでしょう。
中間シートは元シートのIDの列をリンク貼り付けしたシートです。どんどん増えていくのですから、空白行でも構わず中間シートに張り付けておくことを想定しています。

顧客シートは中間シートを参照します。
=IF(COUNTIF(中!C$5:C6,中!C6)>1,"",中!C6)

元シートをソートしても、中間シートで見えているレコードの順番は変わらないはずです。でもリンクはもともとリンクされていたレコードを追いかけていくはずです(手もとにエクセルがないんで確認していませんが・・・)。
それで中間シートを参照して顧客シートを作れば、元シートのレコードの順番に影響されない、アイデア通りのシートが得られると思うのです。

うまくいかなかったら恥の上塗りだな・・・・・ごめん、あらかじめ謝っとく。

回答No.3

MackyNo1

実際のシートの様子をまだ理解できていませんが（おそらくもっと合理的な管理方法があるような気がします）、たとえば顧客シートのD３セル以下に顧客名が入力されているなら、この列を基準に重複のない番号だけを表示するようにし、重複しない場合はMATCH関数で元シートのたとえばE列の顧客名を検索し、INDEX関数で顧客名に対応するC列の値を求める数式にしてはいかがでしょう。

=IF(COUNTIF($D$3:D3,D3)>1,"",INDEX(元!C:C,MATCH(D3,元!E:E,0)))

回答No.2

noname#208392

なるほどそういう風にCountIfをつかうのですか。
面白いですね。いや皮肉じゃなく、本当に興味深いと思いました。

ところで、ご質問のようなケースの場合、よく使われるのがVLookUp関数です。
この関数はご存知でしたか？もしご存じだけれどもあえてそれを使わない理由があるのなら、この回答は外していますので読み捨ててください。
もしご存じでなければ・・・・・

この関数はよく使われるので、ググるといろいろな解説が見つかります。
いまググって一番上に出てきたものをご紹介します。
http://allabout.co.jp/gm/gc/297725/

要するに、こういう風になります。
顧客シートは重複や空白なしの顧客リストです。IDとそれに付随する、名前とか連絡先とか担当者名とかそういうものが書いてあります。
元シートに顧客IDを入力すると、その入力したIDのある行を顧客シートから探してきて、その行の目的のセルの記載内容を吐き出すのがVLOOKUP関数です。
使い方のイメージはこんな風です。
=VLOOKUP(顧客ID,顧客リストの範囲,『顧客名』,FALSE)
『顧客名』のところを、連絡先とか、 担当者名とかに代えて使います。
最後の引数はFALSEにしておくと、新規顧客の場合IDがないのでエラーを返してきます。

どうでしょう。これがご質問の状況でうまい解決策になると思うのですが。

ただ、私自身はVLOOKUPなんか使いません。
それが使いたくなったら、もうACCESSに移してしまいます。
そもそもおつくりになろうとしているのはデータベースです。
ACCESSならデータベース専用ソフトですから、お作りになっているような機能はもっと自然な形で実現できます。
だから、私の一押しのお勧めはACCESSを使うこと。

補足

有難うございます。
VLookUpは顧客シートから顧客情報を元シートへ読み込む際に使っています。
基本のIDが元シートにしている点がネックになっているのか、現状ではVLookUpで元シートから顧客シートに顧客情報を読込めません(逆に読み込んでいるので)。
IDと一緒に顧客情報を元シートから一斉に顧客シートへ移し、そこから読み込むようにすれば良いのかもしれませんが、同じ手の受注ファイルが幾つもある上に扱う人間も多数居るため、なるべくデータ移動は避けたかったのです。
Accessだと楽ですが、なにゆえ使う人間のコンピューター全てにそれぞれ購入しなければならないので、残念ながらそのオプションは無しなのです。。。

回答No.1

MackyNo1

>顧客シートはA列にIDを表示し、
=IF(COUNTIF(元!C$5:C6,元!C6)>1,"",元!C6)
をドラッグして、元シートにIDが追加されても重複しないIDを表示し、その隣へ顧客情報を並べています。

数式の意味が理解できていないのですが、そもそも上記の他シートを参照する数式は自分のシートを参照する数式になっていませんので、参照先の元データを並べ替えたりすれば、当然のことですがその並び替えた値を基準にデータが表示されることになります。

どのような目的で、顧客シートに上記の数式を使用しているのか（何を表示したいのか）補足説明されると、代替案の数式が提示できると思います。

補足

早速のお返事有難うございます。

顧客シートのA列IDは元シートのIDを重複せずに並ばせたく、追加があれば自動的に追加されるようにするための数式でした。
顧客シートには、元シートのIDと顧客情報を重複無しでリストするのが目的です。
元シートには顧客シートから読み込んだ顧客情報が見えるようにしたいからです。

元シートを並び替えたら、顧客シートのIDも並び替わるのはわかりますが、その時に横に並ぶ顧客情報もIDと連動して並び替わるようにしたいです。

元シートに複数リストされているうちの一つを変更したら、同じIDの他の行の受注の顧客情報も同時に変更され、
新しい顧客を追加した場合は仮IDが自動で振られ、それを基に顧客シートの情報も追加、
ID発行後は仮IDから本IDに書き換えれば、顧客シートの情報も変わるようにしたいです。

顧客シートの情報はどんなID順に並んでいようとランダムに並んでいようと構いません。
なのでA列の横に何かワンクッション作業を入れたら、可能になるかな？と思いました。

使用中のデータを使うため、元シートにすべて入っている状態です。重複IDの場合はそれぞれの行に同じ顧客情報を入れています。
データ移動が大変なので元シートのIDベースにできたらいいなと思いましたが、ちょっと有効的ではないかもしれないと思っています。
本来なら顧客データもIDも顧客シートを基にし、元シートへ引っ張れば、元シートで並べ替えようが問題ないですよね。。。
その場合でしたら、空白IDの新規顧客の場合は仮IDを振っていく方法だけで済むかもしれません。