指定の範囲から値を抽出整理整頓して表示する関数。

質問

添付図面の赤罫線範囲から抽出して緑罫線範囲に表示させたいのです。

条件は
１．同じ社名は２度表示させない。(１度だけ)
２．表示の順番は昇順や降順である必要はない。
３．実際に使用する表は客先様式であるため様式は変更できない。
４．誰でも修正しやすいよう配列関数やマクロは使用しない。
５．別のセルを使用して前処理しないで１つの関数で求める。

以上、どなたか関数で解決する方法あれば教えてください。

回答No.4

_Kyle · ベストアンサー

#2-3さまに票を入れつつ…。

単なる技術的興味としてならば、
やってやれなくはありませんが…。

下記の数式はもちろん【冗談】ですが、
A13セルに入力して下方にフィルすれば、動くことは動きます。
(Excel2007で動作確認)

作業列もマクロも使いませんし、
[Ctrl]+[Shift]+[Enter]が必要な配列数式でもありません。
（「配列」は使いますがSUMPRODUCTはアリということなので）

=IF(ROW()-ROW(A$12)>=SUMPRODUCT(1/INDEX(COUNTIF(E$3:F$10,E$3:F$10&""),)),"",INDEX(E:F,INT(1/LARGE(INDEX((COUNTIF(OFFSET(E$2:F$2,1,,ROW(E$3:F$10)-ROW(E$2:F$2)),E$3:F$10)-(E$3:F$10=OFFSET(E$3:F$10,,-1))=1)/(ROW(E$3:F$10)+1/COLUMN(E$3:F$10)),),ROW()-ROW(A$12))),1/MOD(1/LARGE(INDEX((COUNTIF(OFFSET(E$2:F$2,1,,ROW(E$3:F$10)-ROW(E$2:F$2)),E$3:F$10)-(E$3:F$10=OFFSET(E$3:F$10,,-1))=1)/(ROW(E$3:F$10)+1/COLUMN(E$3:F$10)),),ROW()-ROW(A$12)),1)-COLUMN(E:F)+1))

可読性だけでなく保守性も度外視して
【突飛なセルを参照したり】【定数を数式に埋め込んだり】
してもよければもう少し短くできますが、
いずれにせよ「誰でも修正しやすい」とは到底思えません。
------------------------------------------------
【冗談】はさておき、一方、例えば、

　=UNIQUE(【セル範囲】,【番号】)
　とすれば、
　【セル範囲】の値について、重複を除いて【番号】番目の値が返る

というような関数をVBAで作ってしまえば、
このケースでいえば、
　=UNIQUE(E$3:F$10,ROW()-ROW(A$12))
として下方にフィルするだけで結果が得られます。

「エクセルに詳しくない人でも感覚的に」使えますし、
行数や表位置が変わっても「ワークシート上で簡単に」修正できます。

「メンテナンス性」というのは
本来こういうことを言うのではありませんか？

まぁ「設定」や「配布」の問題もありますし、
ご質問とは別の課題になるのでコードは紹介しませんが、
「アドバイス」として一応ご参考まで。

お礼

回答頂き、お礼遅くなりまして申し訳ありません。
こちらの思いを理解していただいたうえ、これほどまでに長い数式まで提示いただいたこと感謝しています。
まさにこの数式がやりたいことで関心を超え感動に至りましたが、おっしゃる通りこれを使うのはさすがにためらい、最終的には作業用のシートにより処理させて頂きました。
私もこれほどの数式を作り上げるスキルがあれば楽にできるのにとうらやましく思います。
丁寧な回答本当にありがとうございました。

回答No.3

mt2008

No.2です。

メンテナンス性や、オペレーションミスに気を使った帳票を作成すると言う方針ですね。
それでしたら、先の補足要求でも書きましたが、関数のみにこだわると、例え実現が可能だとしても相当難しい物になり、メンテナンスが困難になると思われます。
＃私は無理ではないかと思っています。

作業用セルが気になるのでしたら、作業用シートにしては如何ですか？「名前の定義」は作業用シートで運用しているのですから、もう一枚作業用シートが増えても大丈夫では無いでしょうか。

作業用シートを使うなら簡単です。
添付の画像の例では、作業用シートの
B2に　=IF(A2="",999,IF(COUNTIF(A$2:A2,A2)=1,ROW(),999))
C2に　="" &INDEX(A:A,SMALL(B:B,ROW(A2)-1))

を入れてあります。

お礼

回答頂き、お礼遅くなりまして申し訳ありません。
最終的には作業用のシートにより処理させて頂きました。
こちらの思いを理解していただいたうえ丁寧な回答本当にありがとうございました。

回答No.2

mt2008

意図が良く判らないので補足をお願いします。
誰でも修正しやすい使い勝手を求めるならば、条件の4はメンテナンスの事を考えると理解できるのですが、条件の5の作業セルも許可しない理由は何でしょう？

何かのテスト問題でしょうか？
テスト問題ならばどんなに複雑な関数になっても良いでしょうが、そうで無いならば見難くてメンテもしづらい物になってしまうと思いますが。

補足

返答ありがとうございます。
このエクセルファイルはいろいろな帳票を作成できるようにしています。
添付の票は、そのうちの一つとなるのですが、上の表はプルダウンにより選択して「運送会社→代理店」のトレースが分かるように、下の表は上の表に書かれている業者の住所を記入するため、運送会社or代理店のどちらかに出てきた社名を１度だけ自動表示したい次第です。

エクセルに詳しくない人でも感覚的に使えるように、また詳しい人は表面上で修正できるようにとマクロなど使用しないようにしています。それとは別に工夫している部分は以下の２つになります。
１．見たままのものが印刷出力されるように作成。
２．情報を消されないよう触れてはならないセルを実際は色分けしている。

＞作業セルも許可しない理由は何でしょう？
　これは作業セルを見えないようにした場合、知らず知らずに見えてないセルを矢印キーなどで選択してしまい、セルの情報を消してしまう可能性があることと、万一消しても気づかないことからです。保護する方法もありますが、隠れた場所に情報があること自体が他のユーザーがメンテナンスしたい場合に手間がかかるため使用していません。(効率化を図るための帳票一元ファイルであるためメンテナンスも効率的にしたく考えています。)

収支やお金に関しての情報もこのファイルに組み込んでいます。印刷や定義などの条件も全て関数(INDRECTとSUMPRODUCTなど)でのみ作成しています。また、「名前の定義」は専用のシートを作るなど分かり易くしています。

色々な思いがあって上記の条件を自分なりに思い、不可能なのかなと思いながらもいい方法があればと質問させていただいた次第です。
いい方法がありましたらよろしくお願いします。

回答No.1

maron--5

◆多分「１．同じ社名は２度表示させない。(１度だけ)」この条件だけでも
１）作業列を使わず、かつ配列関数を使用しないで関数での処理は無理だと思います