EXcel(エクセル)でのデータの並び替えについて教えてください。
1・年号の並び替え 1つのセルにはいったデータを
明治8年
明治20年
大正10年
昭和5年
昭和10年
のように時代の順に並べたいのですが、
ふつうに並べると元号はあいうえお順となり、
年は、20年と8年では20年が先に来てしまいます。
これを一度に並び替える方法はありますか?
MID関数を使い、元号と年を別々の列に分けてソートすればできるとは思うのですが、「並び替え」機能をうまく活用して並び替えする方法はあるのでしょうか。
2・並び替えのユーザー設定リスト
(ツールーオプションーユーザー設定リスト)の
自分で登録した項目を一度に全削除する方法はありますか?一度に1項目ずつ削除するのはできるのですが、間違って登録してしまい、量が半端じゃないので。。
3・漢字交じりの日本語を単純に並び替えした場合、
全く同じ語句でもばらばらな場所にあることがありますが、これを直す方法はありますか?
わかりにくい質問で恐縮ですが、1つでもわかるものがあればお願いします。
No.5ベストアンサー
- 回答日時:
1についてですが、和暦年の並べ替えですが、他のセルを使用することなく、
そのままの状態で簡単に整列する方法です。
(和暦年のソート用に西暦年を「ふりがな」に設定しています。)
「和暦年」のセル範囲を選択し、下記の「和暦年整列用設定」マクロを実行します。
そのあとは、通常の「並べ替え」で「ふりがなを使う」で実行します。
一応、マクロの設定方法を書いておきます。
1.Alt + F11 で VBE(Visual Basic Editor)を開きます。
2.VBE のメニューから[挿入] -->[標準モジュール] を指定します。
3.コードウィンドウに下記コードをコピーして貼り付けます。
4.Alt + Q (または、右上隅の×)でウィンドウを閉じ、シートに戻ります。
5.実行するときは、整列のキーになる和暦年のセル範囲を指定して
Alt + F8 (メニューから[ツール]-->[マクロ]-->[マクロ])で
「和暦年整列用設定」を指定し、[実行]ボタンを押します。
見た目は、何も変わりませんが・・・(ふりがなを見てください。)
あとは、通常の方法で「並べ替え」を行うとご希望通りになると思います。
Sub 和暦年整列用設定()
Dim Rng As Range
Dim strPhoText As String
For Each Rng In Selection
If IsEmpty(Rng) = False Then
strPhoText = Format(DateValue(Rng.Text & "1月1日"), "yyyy")
Rng.Characters(1).PhoneticCharacters = strPhoText
End If
Next
End Sub
3の「正しく整列されない」については、「ふりがな」が同じく設定されていないことが
考えられます。
または、「ふりがな」が設定されていたり、無かったりするためと思います。
(漢字を手入力しないで他から貼り付けたりすると「ふりがな」が設定されませんから
こういうこともあり得ます。 また漢字の入力(変換)方法により同じ漢字でも
「ふりがな」が異なって設定になる場合があります。)
取り敢えず、並べ替えをするとき、「オプション」で「ふりがなを使わない」にして
実行してみてください。
必ず「ふりがな順」でないとダメなときは、「フリガナ」の再設定が必要でしょう。
これもマクロを使えば、簡単にできますので、必要にときは、その旨書き込んでください。
ありがとうございます。マクロは一度も使ったことがなかったのですが、少し調べてみましたところ、設定さえしてしまえばボタン一つで複雑な操作ができる便利な機能ということをはじめて知りました。実際使用した感じでは、処理の負担が大きくてフリーズしやすいとか、失敗すると元に戻せないとか、気を付けるべき点はあるのでしょうか?
No.10
- 回答日時:
(条件)
年数字は半角で入っているとする。(全角でも出来る。後記。)
余分な1列を使うことを許す。
明治、大正、昭和、平成の文字を入れる列を使うことを許す。
(ソートキー)
明治2年は102
明治22年は122
大正2年は202
大正12年は212
昭和2年は302
昭和12年は312
平成2年は402
平成12年は412のように
別列に文字列を作る関数
(関数)
D1:D4(どこでも使わないところ)に
明治
大正
昭和
平成と入れる。
A列に元の昭和2年、昭和12年とか入っているとして
B1に=MATCH(LEFT(A1,2),$D$1:$D$4,0)&TEXT(VALUE(MIDB(A1,5,2)),"00")
B2セル以下複写する。
年数字が全角数字なら
B1に=MATCH(LEFT(A1,2),$D$1:$D$4,0)&TEXT(VALUE(MIDB(A1,5,LENB(A1)-6)),"00")
(結果)
B列に301,401などが入る。
(操作)B列で、昇順にソートする。
#6、#8のtelescopeさんへ。
済みませんでした#6で正しく、出来ました。上記の式の一方で使わせていただきました。
なんどもありがとうございます。
imogasiさんをはじめ、telescopeさん、ja7awuさんのあげてくださった方法、いずれでもうまくできました。自分は関数があまりつかいこなせず試行錯誤しては失敗しているような状態ですので、みなさんが考えてくださった数式を拝見し、こんな考え方もあるんだ、と本当に勉強になりました。
No.9
- 回答日時:
No.5 の投稿者です。
実行してみたのでしょうか?
> マクロは一度も使ったことがなかったのですが、少し調べてみましたところ、
> 設定さえしてしまえばボタン一つで複雑な操作ができる便利な機能ということを
> はじめて知りました。
> 実際使用した感じでは、処理の負担が大きくてフリーズしやすいとか、失敗すると
> 元に戻せないとか、気を付けるべき点はあるのでしょうか?
まず、詳しく操作手順を書いたつもりですので、その通りにやってみていただきたい
と思います。
処理の負担は、データ行数分の計算式を設定するよりは、負担にならないでしょう。
気を付けるべき点は、別にありませんが、気になるようでしたら、というより一般的な
ことですが、バックアップを取ってから、実行してみてください。
Excelを自在に使いこなすには、VBAが必須ですので、これが始めるきっかけに
なればいいかなと思います。
今回は、コードの中身に触れず、とにかく実行してみましょう。
ちょっと変わった方法ですが、VBAを使うとこういうことも出来るということです。
何度も気にかけていただいてありがとうございます。とりあえず意味もわからずにそのままペーストしてみたところ、驚くほどうまくできました!(明治1年もできて感激。しかし慶応はさすがにできませんね。。)
エクセルはデーターベース専用ソフトではないから複雑なことはできないと信じていましたが、まだまだいろいろ応用できそうです。ともかくVBAが全く初めてなので勉強してみようと思います!ありがとうございます
No.8
- 回答日時:
#7のご質問に関して
MID関数は、半角と全角の区別はしませんが、
MIDB関数は、バイト数で処理します。
=value(midb(a2,5,2))
で数値が1桁の「8年」であっても「年」が2バイト文字ですから、「8」だけを取り出します。
もとの質問が半角数値でしたので、特に言及しませんでしたが、もし全角数字を使っているのなら、
=VALUE(MIDB(ASC(A1),5,2))
とすれば大丈夫だと思います。
A6とあわせて御礼申し上げます。midb関数、asc関数、大変参考になりました。数値は基本的には半角入力ですがたまに全角のところもあったりしますので、一度全部半角してやればいいことがわかりました。
No.7
- 回答日時:
#6のご回答の一部について
「C2セルに「=value(midb(a2,5,2))」 として」とありますが「2」として大丈夫ですか。年数字が1桁と2桁の場合があり得ますが。私は先ほどから悩んだ点です。LENB(A1)-6とか考えたんですが。明治+年=6バイト。
そもそも普通のデータ入力では西暦での入力が基本ということなのですね。「昭和03年」などとしてしまうと印刷の時におかしいですもんね。(この場合もソート用の行と印刷用の行と2つ作ればいいのでしょうがやはり煩雑ですね)ありがとうございます。
No.6
- 回答日時:
既に入力済みなら、並べ替え用のデータを作ったらどうでしょうか?
「明治8年」がA2セルにあるなら、
B2セルに「=left(a2,2)」、C2セルに「=value(midb(a2,5,2))」
として、下までコピーします。
「オプション」-「ユーザー設定リスト」に「明治、大正、昭和」のリストを設定しておき、
「データ」-「並べ替え」で、最優先されるキーをB列に、2番目のキーをC列にしてオプションで
並べ替えの順序を「明治、大正、昭和」にします。
B列とC列を非表示にしておけばよいでしょう。
3.については、
「並べ替え」のオプションで、「ふり仮名を使わない」にしておけば、同じ漢字は同じ位置に並ぶようです。
「ふり仮名を使う」にすると入力したときの読みの順に並びます。
他からコピーしたものは、Excelで入力したものの後に並ぶようです。
たとえば、「東風」をそれぞれ、「こち」「とうふう」「ひがしかぜ」と入力して変換したものは、もとの読みの順に、「こ」の位置、「と」の位置、「ひ」の位置に来ます。
他からコピーして貼り付けたものは、元の読みがなんであれ、最後尾に来ます。
No.4
- 回答日時:
1.に付いて
1900年(明治33年)以降であれば
1921/1/1
1930/1/1
1935/1/1
と入れて、表示形式をggge"年"としておくと、見た目には
大正10年
昭和5年
昭和10年
と表示されて、そのままソートすればお望みの通りの順番になります。しかし明治32以前があったり既に文字で入力が終わっておれば、この考え方は役立ちません。将来何かの時に役立てば。
3.の原因は、先頭部のスペース、中間部のスペース、かな英数の半角、全角が原因ではないでしょうか。
2.は「削除」である系列は全部抹消になるから、それだけの機能で十分で、全系列1発でオール削除は設けられていないと思いますが。
ご丁寧にありがとうございます。特に1については大変参考になりました。大正10年が西暦で何年かなんて考えたこともなかったので、全然思いつきませんでした。1900年以前も適用できればもっとすばらしいんですけどね。
No.3
- 回答日時:
1.昇順で並べ替えると
昭和10年
昭和5年
大正10年
明治20年
明治8年
降順では
明治8年
明治20年
大正10年
昭和5年
昭和10年
となりました。・・・・コレでいいのでは?
数値も半角でも全角でも同じでした。
2.地道な削除以外思いつきませんでした。識者の登場を待ちましょう。
3.まったく同じ語句でも、どこかからコピーした場合と日本語変換した場合とではソートの基準が異なるようです。たしか、コピペはシフトJISコード順で、変換して入力した場合はその読みの順だったと思います。
たとえば、ご質問の文字列に加えて、「昭和9年」を「あきら」で変換した「昭」を使っって追加した場合は昇順でソートするとこの「昭和9年」が先頭になってしまいました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelでの勤怠表の関数を教えて...
-
Excelの警告について
-
考えた式の戻り値が期待通りに...
-
エクセル 入力があった場合のみ...
-
エクセルを使ってQRコードを作...
-
FからI列で期限切れ及び期限7日...
-
(マクロ)参照渡しにて、違う...
-
ExcelやLibreOffice Calcの関数...
-
今まで文字化けなく開けていたc...
-
(マクロ)値を返す時は subで...
-
エクセルについての質問です。 ...
-
エクセルでファイルの最終更新...
-
EXCELの散布図で日付が1900年に...
-
エクセルについての質問です。 ...
-
オルトキーを押しながら?
-
Excelの計算が合いません。 諸...
-
ゼロを表示
-
【Excel】 1つのセルの日にちを...
-
【Excel】年月の値によって日の...
-
Excelで特定の文字列が含まれて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
エクセルデーターから必要な項...
-
エクセルでファイルの最終更新...
-
複数のテキストファイルをexcel...
-
Excelの複数条件の関数
-
【マクロ】ファイル名の一括変...
-
EXCELの散布図で日付が1900年に...
-
マクロの処理が遅くなった
-
Excelの時刻の不思議
-
エクセルでの2項目比較および...
-
Excelマクロで空白セルを詰めて...
-
エクセルの数式バーのフォント...
-
ExcelでASCを使って全角を半角...
-
エクセルで80万行、50列位のデ...
-
今まで文字化けなく開けていたc...
-
エクセルのことで教えてくださ...
-
エクセルVBA 月の中で、月~土...
-
Excelでの表の作り方
-
Excel セルにおけるフォント設...
-
エクセルの質問です。 F列からL...
おすすめ情報