EXCELでの並び替え

質問

ある趣味の会があります。
この会の入会者には会員番号を割り振っています。
例えば昭和５９年の入会者は入会の早い人から59001，59002，...の会員番号を割り振っています。
また，平成元年に入会した人の会員番号は01001，01002
...となります。平成５年は05001，05002...です。
この会員データをＥＸＣＥＬで管理します。
そこで質問なのですが，この会員番号を使って入会の早い人から順番に並び替えたりしたいのです。
この番号だけで実行すると平成の方が先にきてしまいます。セルに関数とか数式を入れたらいいと思うのですがそれがわかりません。教えてください。

回答No.3

hinebot · ベストアンサー

もっとスマートな方法があるかもしれませんが…。
計算用の列２つと並べ替え用の列１つ、計４つ作業列を作ります。

会員番号がA列に入っていて、計算用の列がD列、E列、F列,並べ替え用の列をG列とすると
A1に対して、D1に
=IF(VALUE(LEFT(A1,2))>=50,VALUE(LEFT(A1,2))+1925,VALUE(LEFT(A1,2))+1988)
といれます。(50の数字は、昭和何年からあるかで適当に変えてください）
そして、E1に
=D1&RIGHT(A1,3)
とします。
これで、和暦部分を西暦に換算できます。
これを会員分の行数だけコピーします。
E列を範囲指定してコピー、[編集]－[形式を選択して貼り付け]で、「値」を選んでF列に貼り付けます。
このF列をキーに並べ替えればOKです。

お礼

ありがとうございます。お礼が遅くなってすみません。
早速にためしてみたのですが、昭和の会員番号は西暦の変換されるのですが平成がうまく変換されません。
おそらく会員番号の最初の『０』を認識しないのが原因と思います。
例えば会員番号『０２１２３』では1988+02=1990とならなければいけないのに実際は1988+21=2009となります。
最初の『０』をどのように認識させたらいいかわかりません。

回答No.8

mshr1962

#2のmshr1962です。
A列が文字でなく数字の場合はこうなります。
=TEXT((LEFT(A1,2)&"/1/1")+0,"gg")&TEXT(A1,"00000")

A1に"00000"の書式をTEXT関数で設定する。

お礼

確かにＡ列を数値で入れていました。
ご指摘ありがとうございました。

回答No.7

hinebot

#3です。

先頭の0が認識されないということは、#5さんが#6で回答されているように、会員番号が数値で入力されていて、セルの書式設定で頭に0を付加されているものと思われます。

会員番号は'01123　のように文字列として入っている、つまり、必ず年号2桁＋番号3桁の形になっているものとして回答しましたから。

解決策は#6さんの通りです。

お礼

まったくご指摘の通りでした。
ありがとうございました。

回答No.6

imogasi

#5です。
＞例えば会員番号『０２１２３』の場合は１９２１２３となります。
再度やって見ました。
A1に'02123と入れて関数をB1に=IF(MID(A1,1,2)>"20","19"&A1,"20"&A1)を入れると2002123（左寄せ）
になりました。
A列のデータがどうなっているのでしょう。
書式を文字列にして、02123と入れても同じ2002123でした。
数値2123を書式ユーザー定義の00000で02123に見せているのでしょうか。
それを文字列にするには=TEXT(A1,"00000")のようにすればよい。
本当は質問にそのこと（値と書式）を明記すべきなのです。

お礼

ありがとうございました。
確かにＡ列はユーザー定義で00000としていました。
問題が解決しました。感謝します。

回答No.5

imogasi

空き列に=IF(MID(A1,1,2)>"20","19"&A1,"20"&A1)
といれ下へ複写する。その列でソートする。現８５歳以下
１５年後までOK。

お礼

ありがとうございました。
早速実行してみたのですが平成の場合が桁数が少なくなるのでうまくソートが出来ません。
例えば会員番号『０２１２３』の場合は１９２１２３となります。

回答No.4

hinebot

>計算用の列２つと並べ替え用の列１つ、計４つ作業列を作ります。

計３つの誤りです。失礼しました。

あと、
>(50の数字は、昭和何年からあるかで適当に変えてください）

これは、IF関数の最初のまとまり
VALUE(LEFT(A1,2))>=50
の"50"のことです。会員登録が昭和何年からあるか（何年以上を昭和と判断させるか）で変えてください、という意味です。

回答No.2

mshr1962

別の列に昭和と平成の判定した物を用意すればいいのでは？

暫定的に関数でやるなら
=TEXT((LEFT(A1,2)&"/1/1")+0,"gg")&A1
で30～89までが"昭"それ以外が"平"が頭に付いた文字列に置き換わります。

平成もいつかは変わることになりますので年号に対応した
数値か記号を用意することをお薦めします。

お礼

有難うございました。お礼が遅くなって申し訳ありません。平成の場合がうまく表示されません。
例えば会員番号『０２３０１』の場合は『平２３０１』と表示されます。最初の０を認識させるには。。。

回答No.1

kensaku

もう一つ列を作って、そこに連番を入れておき、並べたいときは、ここを参照する。普段は隠しておいてもいいでしょう。

お礼

お礼が遅くなって申し訳ありません。
ご解答いただきありがとうございました。
確かに連番をいれるのが簡単でいいかもしれません。
研究してみます。