[[20100414021259]]　『関数における昇順降順の順位付けは?』（おいちゃん）　ページの最後に飛ぶ

 『関数における昇順降順の順位付けは?』（おいちゃん）
エクセルの学校でMatch関数やLookup関数の裏技として、同じ行や列の内で最後の数値の位置や値を返す数式を
教えてもらい利用させていただいております。
整理するとこの様になると思っております。（間違っていたら教えて下さい）

 ＊数値の場合
＝Match(9^9,A1:A10)…位置　　　｛＝Match(1,0/A1:A10)}…ゼロを除いた位置
＝Lookup(9^9,A1:A10)…値　　　　＝Lookup(1,0/A1:A10,A1:A10)…ゼロを除いた値

 ＊文字列の場合
＝Match("",A1:A10,-1)…位置
＝Lookup(1.0/Istext(A1:A10),A1:A110)…値

 ＊数値も文字列も含んだ場合
{＝Match(1,0/(A1:A10<>""))}…位置
＝Lookup(1,0/(A1:A10<>""),A1:A10)…値

 これはMatch関数の照合の型やLookupの検索の方法が、検索範囲のデータが昇順で配置されていることの原則を
逆に利用したすばらしいアイデアだと思います。

 ただ、順番のはっきりしている数値はいいんですがセルの内容が文字列の場合この順番とは何を元にした順番に
基づいているのか教えてほしいのです。

 ひらがな、カタカナばかりならまだ分かり易いのですが漢字が入りアルファベットが入り記号も入り、
挙句の果てには空白文字列も入った様な場合の順番の基準は何なのか。

 単純に考えれば文字コードを使っているとしか思えないのですが、どうもそうではないようです。
例えば"ー"(8508)は"黒に似た字"(38700)より並べ替えで後ろに来ますし（昇順の場合）関数の場合でも同じような結果が出ます。

 これってどう言うことでしょうか?この仕組みをご存知の方教えてください。

 >単純に考えれば文字コードを使っているとしか思えないのですが
 文字コード体系もいろいろありますからねえ、提示された文字コードはJISコードですが、
 Unicodeで比較すれば、黑は、-24879で、ーは、12540で大小が逆転します。

 >この仕組み
 これで結論付けようかなあ　と思ったけど、念のために過去ログ検索したら

[[20090918152425]]　

 興味深いスレッドがありましたよ！！

 ichinose

 > 検索範囲のデータが昇順で配置されていることの原則を
 > 逆に利用したすばらしいアイデアだと思います。

 ↑　はどういう意味ですか？
 昇順・降順に関係なく、ランダムであっても期待値が返るでしょう？

 文字コードは関係ないと思いますよ。

 > =LOOKUP(1,0/(A1:A10<>""),A1:A10)
　
 数式バー上で　0/(A1:A10<>"")　の部分をドラッグして
 F9キーを押してみてください（解除は　ESCキー）

 =LOOKUP(9^9,A1:A10)　の数式でなぜ期待値が返るか理解できているのなら
 それでわかると思いますけど。

 （sky）

 > ↑　はどういう意味ですか？
 >昇順・降順に関係なく、ランダムであっても期待値が返るでしょう？

 ↑　はどういう意味？

 「期待値」とはなんぞや？

 >「期待値」とはなんぞや？

http://www.google.co.jp/search?q=%8A%FA%91%D2%92l&hl=ja&ie=Shift_JIS&btnG=Google+%8C%9F%8D%F5

 （ググレ）

 ichinoseさん、ありがとうございます。
 ユニコードの名前は聞いたことはありましたが詳しいことは分からず思いつきませんでした。
ただ、エクセルの関数にはCODE関数はありますがユニコードを調べる関数はありませんよね?
（教えて頂いたスレッドの中でみやほりんさんのマクロを見ましたが、マクロのことはまるっきり分からず、
自分で調べることもできませんし、またユニコード対応表のサイトも見させてもらいましたが、どの列にユニコードが
書かれているのかチンプンカンプンで分かりませんでした。）

 1.原則的にはユニコードを基本にしている。
 2.コードのどこかの部分の判別により「清音・濁音・半濁音」のルールを適用している。
 3.半平太さんの提示された「ヽ・ヾ・ゝ・ゞ」のように優先順位の無い同列の文字列が存在する?
（ちなみに、「々」は同じでしたが「〃」は違いました）
おぼろげながら見えてきましたが、まだ何かすっきりしません。ご存知の方教えて下さい。

 skyさん、ご指摘ありがとうございます。実は私がこのような質問をしたのは最初の質問のときに示した関数式の中の
数値の場合のMatch関数とLookup関数は検索範囲のデータが昇順（Match関数は降順も可）であることを前提としているため、
検索値に検索範囲の最大値より大きい値を与えることにより、検索値を求めて最後の行や列までたどり着き
その位置や値を返すと理解したのが始まりでした。

 そうであれば、文字列の場合も文字列としての昇順の"最後の文字"が分かれば数値の場合の"9^9"の代わりに
その文字を代入するだけで出来るのではないかと考えたのです。
そこで文字の順番がどうなっているか調べ始め、今回の質問になった訳です。
事実いくつかの文字の並べ替えの順番とその最後の文字を検索値として使った関数の結果とは一致していましたので
どうしても文字列の中の最期の文字が何なのか知りたかったのです。
ただ、Match関数に関しては降順での照合の型もあり、最後の文字が分からなくても最初の文字が分かれば良いわけで
（つまり降順での最後の文字）、検索値=""としたところうまく行ったのでどうしてもLookup関数のために
最後の文字を知りたかったのです。よろしくお願いします。
（おいちゃん）

 >どの列にユニコードが書かれているのか
 UTF-16という列です

 ＶＢＡでは、
 標準モジュールに

 '===============================================================================
 Function UTFcode(myvalue As Variant) As Long
    UTFcode = AscW(myvalue)
 End Function

 上記コードを記述すれば、

 例えば　セルＡ１に　　全角の　Ａ　と入力し、
        セルＢ１に　　=UTFcode(a1)  とすれば、　-223　と表示されます。
 これが、全角Ａの　Unicodeです。１６進に直せば　FF21 ですね

 Unicodeに関しては、これで確認できると思います。
 上記のコ-ドをどこに書けばよいかは、調べてみてください。

 ichinose

 ＞Lookup関数のために最後の文字を知りたかったのです。

 実際に全部の文字を並替えてみれば簡単に判明するのではありませんか？

 ※ この問題は他所でも何回か議論されています。
    一例
   　↓
 【式の意味を教えていただけないでしょうか？】
http://www.efcit.co.jp/cgi-bin2/exqalounge.cgi?print+200605/06050110.txt

 (半平太) 2010/04/18 22:09

 ichinoseさん、ありがとうございます。マクロのことは分からないので教えてもらったUTFcode関数を使って初めはやってみたのですが、
この関数は教えてもらったマクロを標準モジュールに書き込まないと使えないんですね。
でも何とか手探りでできました。これでUnicode番号は調べることができました。
（-223がFF21になるという１６進法の仕組みはまだぜんぜん分かりませんけれど・・・）

 ただ今回の昇順での並べ替えの最後の文字を調べるのには、Unicode番号の最大の文字が何なのかが分からないと分かりませんが、
JIScodeに対応するChar関数のような関数はUnicodeにありますか、あったら教えて下さい。

 半平太さん、いつもありがとうございます。教えていただいたサイト見ました。過去にも同じような疑問を感じたり、
関数の不思議に感激したりした人たちがいたことが分かりホッとしました。
そこでちょっと心配になったんですが、この問題に関しては達人の人たちも試行錯誤の中でシンプルで100％の関数を模索しているということは、
Unicodeの最大値だけではこの問題は解決しないということですかね?
（おいちゃん）
 すいません、エクセルの電源を落としたらUTFcode関数が使えなくなってしまいました。
標準モジュールは都度何か操作をしなくてはいけないのでしょうか。
再度貼り付けをしてみたのですが駄目です、ご支援を!

 ＝Match(9^9,A1:J1)…位置
0 5 10 20 30 50・・・・150　と有った場合
ランクで検索の場合は順番に並んでいるので位置検索ができます
200で検索なら150以上で　150の位置

 ランクで検索の場合は順番に並んでなくては　位置検索ができません
10　15　5　30　25　18・・・70
と並んでいた場合　20はどの位置に？？？
わかりませんよね
しかし1000なら　1000未満の数値しかないので最後尾（値のある）まで検索で

  文字検索
=Lookup(1.0/Istext(A1:A10),A1:A110)…値
{＝Match(1,0/(A1:A10<>""))}…位置
＝Lookup(1,0/(A1:A10<>""),A1:A10)…値
文字で検索してませんよ
よってcode番号はかんけいありません

 ひらがな、カタカナばかりならまだ分かり易いのですが漢字が入りアルファベットが入り記号も入り、
挙句の果てには空白文字列も入った様な場合の順番の基準は何なのか。
完全一致の場合は昇順は関係ありません
(HANAI) 葉内

 並べ替えの規則だけをクローズアップすると、

 >1.原則的にはユニコードを基本にしている。
 ちょこっと調べただけでもこれは、とても言えません。
 >Unicodeで比較すれば、黑は、-24879で、ーは、12540で大小が逆転します。
 これは、たまたまで例外も簡単にみつかりました。

例

 新規ブックの標準モジュールに 
 Option Explicit
 Sub test()
    With Range("a1:d1")
       .Value = Array("漢字", "UTF", "SJIS", "JIS")
    End With
    With Range("a2:d9")
       .Columns(1).Value = [{"ー";"黑";"噪";"二";"伝";"仁";"壱";"一"}]
       With .Columns("b:d")
          .Formula = Array("=utfcode(a2)", "=sjis(a2)", "=code(a2)")
          .Value = .Value
       End With
    End With
    MsgBox "これを漢字（フリガナなし）で昇順に並べ替えると・・・・"
    Range("a1:d9").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
        :=xlStroke, DataOption1:=xlSortNormal
 End Sub
 Function utfcode(myvalue) As Long
    utfcode = AscW(myvalue)
 End Function
 Function sjis(myvalue) As Long
    sjis = Asc(myvalue)
 End Function

 これを実行すると、ーが例外文字で漢字は、Ｓ－ＪＩＳが基準かもしれないと　思わせる現象になります。
 Uncodeにしかない文字もあるのでそれ以外の処置もあると思いますけど・・・。

 数字・英字・カナ･ひらがな・記号・･･･、漢字などで規則が個別にあるのかもしれませんねえ。

 ichinose

 (HANAI)葉内さん，ありがとうございます。
私が並べ替えの最後の文字コードを知りたく思ったのは、Match関数やLookup関数がランクでの検索をうまく使って
最後の数値やその位置を返すことに驚き、この仕組みを利用して文字の検索が同じようにできないかと思ったことがきっかけでした。

 数値なら範囲の中にありそうな値を超えた数値を代入することは桁を一つか二つ上げてあげれば良いわけで簡単なのですが、
エクセルのヘルプにアルファベットもひらがなも順番に並んでいる必要があると書いてあり、
文字となるとどういう順番を元に検索をしているのか分からず、そのためエクセルが対応できる最終コードを検索値にすれば
ほぼどんな場合でも正しい答えが返ってくるだろうと思ったのです.
（その最終コード番号の値が文字なのか、記号なのかも分かりませんが、またそんな値を検索範囲のセルの中の文字の先頭に
使っていることも決して無いとはいえませんが・・・）

 引き数に（1，0/　　　　）というような素人には思いも付かないテクニックを知る前でしたので、これが分かれば関数として
とてもシンプルでほぼ完璧な式になるだろうと考えたからです。

 しかし、このすごい裏技もこの学校で教えてもらい、エクセルの並べ替えがこれほどまでに複雑で難解なルールに
基づいているということでしたらこれ以上追求することは実際はもうあまり意味は無いのかもしれません。
すでにほぼ知的好奇心に近くなっております。

 ただこれが分かると範囲に空白を含んだときの文字列だけの（数値ではない）最終行が求められると思ったんです。

 ichinoseさん、新しいマクロありがとうございます。
依然マクロの内容は全然分かりませんが丁寧に書き方の説明も付けてくれてあるので私でも使えました。
ただ、”これを漢字で昇順に並べ替えると・・・”でOKを押すと並べ替えられると思ったのですがコード表示の画面になってしまい
慌てましたが、”データ　並べ替え”で出来ました。

 マクロで書かれた文字ばかりでなく、丸文字や半角スペース等々を入れてそれぞれのコードを見てみましたら決してUnicode順ではないですね。
でもSJISや通常のJISコードでもないみたい。一体どんな仕組みになっているいるんでしょうね。
（おいちゃん）

コメント返信：

[ 一覧(最新更新順) ]

[[20100414021259]] 『関数における昇順降順の順位付けは?』（おいちゃん） ページの最後に飛ぶ

[[20100414021259]]　『関数における昇順降順の順位付けは?』（おいちゃん）　ページの最後に飛ぶ