Excel2007 文字列の入ったセル列を関数のみで高順位並べ替えるには？

質問

A　　　B　　　　C
１　氏名　市区町村名　・・・・・　
２　 ・
３　 ・
４　 ・
といった感じの表があったとします

市区町村名は何種類あるか分からず順番もバラバラで
機密重要文章なので並びを変えるどころか
コピーとかフォーマットを変えるなどもってのほか
だとします。

市区町村毎の件数を配列定数形式で求める必要に駆られたのですが、
文字列データセル群を関数で並べ替える方法が分からず困ってます

がちに分かりません
御指南宜しく御願いします。

回答No.3

MackyNo1 · ベストアンサー

ご希望には添えないかもしれませんが、参考までに。

たとえばA列の重複のない配列をセルに取得するなら以下の数式を入力して下方向にオートフィルします。

=INDEX(A:A,SMALL(INDEX((MATCH(A$1:A$100&"",A$1:A$100&"",)<>ROW(A$1:A$100))*100+ROW(A$1:A$100),),ROW(A1)))&""

この数式のROW（A1)の部分をROW(A1:A5)とすれば、理論上は5つの配列を含む数式になるのですが、残念ながらINDEX関数では配列として取得できません（数式部分を選択してF9キーで確認してください）。

一方以下のようにOFFSET関数を利用すれば、配列を取得する数式になりますが（F9キーで5つの配列が取得できる）、実際は配列として認識しないと思います。

=OFFSET($A$1,SMALL(INDEX((MATCH(A$1:A$100&"",A$1:A$100&"",)<>ROW(A$1:A$100))*100+ROW(A$1:A$100),)-1,ROW(A1:A5)),0)

さらにデータを昇順または降順に取得する配列数式は、データの昇順などを数式で判定しにくいため（TRANSPOSE関数で行配列を列配列に変更したデータと大小判定を利用する方法で2次元配列を取得し、TRUEの数の多い順に行数を取得するなどが考えられますが）、おそらくできないと思います。

お礼

今回は本当に脱帽です有り難う御座います。

この式のキーとなる考え方は
「match関数で全要素を検索させたら例え何要素あろうとも
それはその検索範囲の中に1つは必ず含まれていて
絶対に最上方のそのキーを含むセルでヒットする
この最上方のセルというのは1つなので
結果要素毎に集約され重複と過不足が消えるし
それが数値配列の情報でで得られる」
と、言うことだと理解しました。

この「要素の抽出」は配列数式でデータを扱う上で
基本パーツの内の1つだと思うので
過去には何らかの形で私自身実現させていたと思うのですが、
今回のmatch文の素晴らしさと裏腹に自らの衰えを感じ少し切なさを覚えました

それにしても相当過去ログを探索されたようですね
御呈示頂いた式が前回のものとは様変わりして
素晴らしくなっているように見えます

これでは僕なんて到底えらそうに出来ないですね
今回は本当に有り難う御座いました。

補足

match文に触発されてちょっと考えてみました
Ｂ列1行目から
{0;"仙台市";"大東市";"横浜市";"横浜市";"福岡市";"仙台市";"仙台市";"大東市";"名古屋市";"岡崎市";"名古屋市";"横浜市";"福岡市";"福岡市";"大東市";"大東市";"大東市";"名古屋市";"仙台市";"横浜市";"横浜市";"福岡市";"名古屋市";"横浜市";"仙台市";"名古屋市";"横浜市";0}
とデータが入っているとして(※:0は空白セルを示す)
=OFFSET($B$1,SMALL(((SMALL(INDEX(MATCH(INDEX($B$1:$B$28&"",0,0),INDEX($B$1:$B$28&"",0,0),0),0,0),ROW($B$1:$B$28))=SMALL(INDEX(MATCH(INDEX($B$2:$B$29&"",0,0),INDEX($B$2:$B$29&"",0,0),0)+1,0,0),ROW($B$1:$B$28)))*100+SMALL(INDEX(MATCH(INDEX($B$2:$B$29&"",0,0),INDEX($B$2:$B$29&"",0,0),0)+1,0,0),ROW($B$1:$B$28))),ROW(INDIRECT("a1:a"&(SUMPRODUCT((SMALL(INDEX(MATCH(B1:B28&"",B1:B28&"",0),0,0),ROW(A1:B28))<>SMALL(INDEX(MATCH(B2:B29&"",B2:B29&"",0),0,0)+1,ROW(A1:B28)))+0)-1))))-1,0,1,1)
データセットを2本用意して片方を1つずらし
その差分より単調性を見いだし、排除して
残った位置情報より参照をさせてみました

match文を使えば直接的ではないにしろ文字列データのソートも出来ましたし
matchの構文を教えて頂けた御陰で目的が達成出来そうです
有り難う御座います
m(_ _)m

回答No.2

MackyNo1

質問の趣旨がいま一つ不明確です。

>言うのが遅れましたが
この求められる配列定数形式のデータが必要なのも
実はグラフデータで、
そもそも表に起こすためなのです。

「表に起こす」とはグラフの元になる表を作成するという意味でしょうか？

>なので私自身
ピボットグラフで真っ先にやってみたのですが、
棒グラフに起こしてみたところ
求められた仕様から凡例に表示されるデータ系列名が
どうしても望ましくないものになってしまいます
おまけにピボットではこの名前を変えられないのです

どうして「望ましくない」のかよくわかりません。
もし名前を変えられたらそれでよいのでしょうか？

>結果として、
やはりデータを配列の羅列といった形式で得て
グラフデータを指定するところに直接書く必要があるのです。

「配列の羅列」とはたとえば配列数式で重複のない市町村名の配列を取得したいということでしょうか？

「グラフデータを指定するところ」に直接書くとは、グラフの元となる集計データを表示しないで、数式だけで集計した配列数式を入力するということでしょうか？

また「高順位並べ替える」の部分もよくわかりません。

たとえば配列数式でセルにデータを書きだすことはできても、その配列を1つの式で表示することはできないと思います（仮にできたとしても配列数式はグラフのデータ範囲と認めない可能性が高い）。

お礼

＞「表に起こす」
失礼しました
「グラフに起こす」と書くべきでしたね

＞もし名前を変えられたらそれで…
ピボットグラフ凡例中のデータ系列名を変えられれば
割と要求された仕様に近いのですが、
それでも先にも申しましたとおり
ピボットを置く領域の問題があります
このブック中に置くことが許されませんし
このブックの指し示す内容が他のブックに存在することも許されません
ですのでピボットを置く場所がないのです。

＞配列数式で重複のない市町村名の配列を取得したい…
正にその通りです

＞「高順位並べ替える」…
これも誤植です
正しくは「降順に並べ替える」(※昇順に並べ替えるでも構いません)
です
失礼しました。

＞配列数式はグラフのデータ範囲と認めない可能性が高い…
これは式にもよるのでしょうが
Excel2003で実現できることを確認しておりますのでご安心ください。

補足

ｐ・ｓ・
＞配列数式で重複のない市町村名の配列を取得したい…
正にその通りですなのです。

ですが、
データの整列(※ソート)さえ出来れば
あとは私の力で何とか出来ますので

今回はデータの関数による並べ替え方法を御教え頂ければ幸いです
どうぞこの1点のみを宜しく御願い致します。

回答No.1

MackyNo1

＞市区町村毎の件数を配列定数形式で求める必要に駆られたのですが、

具体的にどのようにしたいのかわかりません（配列定数形式とはどのようなものを指しているのでしょうか？）。

市町村ごとのデータの件数を調べたいならピボットテーブルを作成すれば一発で件数が表示できます。

お礼

確かにピボットテーブルが一番何より早くて簡単だと思います

言うのが遅れましたが
この求められる配列定数形式のデータが必要なのも
実はグラフデータで、
そもそも表に起こすためなのです。

なので私自身
ピボットグラフで真っ先にやってみたのですが、
棒グラフに起こしてみたところ
求められた仕様から凡例に表示されるデータ系列名が
どうしても望ましくないものになってしまいます
おまけにピボットではこの名前を変えられないのです

更に、先にも述べたように求められている仕様上
フォームには一切手を加えられません
当然作業用セルを使用できませんし
ピボットを置く領域も認められませんので
「ピボットでは駄目」となるのです。

結果として、
やはりデータを配列の羅列といった形式で得て
グラフデータを指定するところに直接書く必要があるのです。

御理解頂けましたら幸いです。