わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】区切り位置指定ウィザードを使った並べ替えと応用事例のまとめ


Excelの「区切り位置指定ウィザード」は、最近では、大量のデータを処理したり、データの形式を統一するデータクレンジング(クリーニング)の時に使われる機能です。この機能は、すでにExcel2000より前には搭載されていましたが、紹介する書籍等が少なかったこともあり、知っている人があまりいないようです。

f:id:waenavi:20190529013843j:plain

 

確かに、文字列操作関数(FINDやRIGHTなど)やマクロを使うことによって、区切り文字の前後の文字列を取得することは可能です。しかし、1~2回区切るだけでわざわざ長い数式を打つのは時間の無駄です。状況に応じて臨機応変に、数式で処理するのか、区切り位置指定ウィザードで処理するのかを判断したほうが良いと思います。

そこで、今回は、あらためて区切り位置指定ウィザードの基本をおさらいするとともに、並べ替えやその他の応用事例について出題します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.区切り位置指定ウィザード

問題

次の図で、A列の文字列は、途中にスペースが何個か入っている。

f:id:waenavi:20190529000218j:plain

 

次の図のようにB列とC列に分けなさい。

f:id:waenavi:20190529000244j:plain

 

解説

A列を選択します。区切り位置(データタブ)を選びます。

f:id:waenavi:20190529000815j:plain

 

区切り位置指定ウィザードが表示されます。

f:id:waenavi:20190529000818j:plain

 

スペースで区切られています。このような文字を区切り文字(デリミタ delimiter)といいます。

Excelではこのように1つのセルに2つの情報を入力してはいけません (1セル1情報の原則、わえなびファンダメンタルExcel Program7-4の動画参照) 。2つのデータを入力する場合は、通常、区切り文字を使わず2列で入力すべきです。区切り位置指定ウィザードは、本来、2列で入力したほうが良いものを強制的に分離するときに使う機能です。

f:id:waenavi:20190529000821j:plain

 

「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認して、次に進みます。

f:id:waenavi:20190529000832j:plain

 

スペースにチェックを入れます。次に進みます。

f:id:waenavi:20190529000823j:plain

 

表示先を消して、セルB1をクリックします。完了します。

f:id:waenavi:20190529000829j:plain

 

スペースの前後で分離されました。これで完成です。

f:id:waenavi:20190529000834j:plain

 

区切り位置指定ウィザードの、第1画面では区切り文字のほうが選ばれていることに注意します。

f:id:waenavi:20190529000839j:plain

 

第2画面で区切り文字を選びます。「スペース」は全角も半角も含まれます。

f:id:waenavi:20190529000843j:plain

 

また、スペース以外にチェックがあるかどうかは関係ありません

f:id:waenavi:20190529000846j:plain

 

区切り文字のスペースが連続している場合は、まとめて1個の区切りとして扱います。

f:id:waenavi:20190529000849j:plain

 

第3画面で貼り付ける範囲の左上のセルを指定します。

f:id:waenavi:20190529000853j:plain

 

2.分割してから順序を変えて連結

問題

次の図のように、カンマで区切られた文字列がある。順序を逆にしなさい。

f:id:waenavi:20190529000323j:plain

 

解説

カンマで区切られていますので、いったんカンマで分離します。

f:id:waenavi:20190529001916j:plain

 

A列を選択します。「区切り位置」を選びます。次に進みます。

f:id:waenavi:20190529001920j:plain

 

カンマにチェックを入れます。次に進みます。

f:id:waenavi:20190529001922j:plain

 

表示先を消して、セルB1を選びます。完了します。

f:id:waenavi:20190529001925j:plain

 

カンマの前後で分離されました。

f:id:waenavi:20190529001928j:plain

 

この3つを、順序を逆にして、カンマで連結する計算式を入力します(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20190529001931j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190529001935j:plain

 

3.区切り位置指定ウィザードを使った並べ替え

問題

A列の番号は、アルファベット1文字と3桁の数をハイフンで連結している。ハイフンの後の数で昇順にしなさい。

f:id:waenavi:20190529000349j:plain

 

解説

並べ替えは列単位でキーを指定するため、ハイフンの後の数字で並べ替えをするには、2列に分ける必要があります。

f:id:waenavi:20190529002406j:plain

 

A列を選択します。「区切り位置」を選びます。次に進みます。

f:id:waenavi:20190529002409j:plain

 

区切りとなっている文字はハイフンですが、この中にないので、その他にチェックを入れます。半角でハイフンを入力します。次に進みます。

f:id:waenavi:20190529002412j:plain

 

表示先を消して、セルB2を選びます。完了します。

f:id:waenavi:20190529002415j:plain

 

ハイフンの前後で分離されました。

f:id:waenavi:20190529002417j:plain

 

数字の部分だけ分離することができました。これで並べ替えをします。C列の昇順にします。

f:id:waenavi:20190529002420j:plain

 

これで完成です。分離した列は非表示にしても構いません。

f:id:waenavi:20190529002423j:plain

 

4.オートフィルの連続データと並べ替え

(1)オートフィルの連続データが並べ替えの昇順とは限らない

問題

IPアドレスの形式で「192.168.11.1」と入力して、オートフィルをすると連番になった。

f:id:waenavi:20190529002744j:plain

 

ところが、昇順で並べ替えをすると、順番が変わる。このように、オートフィルで連続データ(連番を含む文字列)を入力したあとで、昇順で並べ替えると順序が変わることがある。その理由を述べなさい。

f:id:waenavi:20190529002747j:plain

 

 

解説

数字と文字列が混ざったセルをオートフィルすると、最後の数字が1ずつ加算されます。

f:id:waenavi:20190529003157j:plain

 

オートフィルで入力した単なる数値の連続データの場合、2<10なので、2よりも10のほうが下になります。昇順で並べ替えをしても変わることはありません。

f:id:waenavi:20190529003200j:plain

 

しかし、文字列の場合、先頭の文字から順に判定していきます。

f:id:waenavi:20190529003204j:plain

 

2>1なので、昇順で並べ替えをすると2(ニ)よりも10(イチ・ゼロ)のほうが上になります。オードフィルで連続データを入力したからと言って、それが並べ替えの時の昇順であるとは限らないのです(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。

f:id:waenavi:20190529003206j:plain

 

(2)分離して並べ替える

問題

正しく並べ替える方法を述べなさい。

解説

これを正しく並べ替えるには、ドットで分離して数値にすればよいです。A列を選択します。区切り位置です。次に進みます。

f:id:waenavi:20190529003209j:plain

 

その他にチェックを入れます。今度はドットです。次に進みます。

f:id:waenavi:20190529003212j:plain

 

表示先を消して、B2を選びます。

f:id:waenavi:20190529003214j:plain

 

4つに分かれます。

f:id:waenavi:20190529003217j:plain

 

3番目までは同じですから、4番目の数値で昇順にすれば正しく並べ替えができます。

f:id:waenavi:20190529003220j:plain

 

これで完成です。

f:id:waenavi:20190529003223j:plain

 

(3)そもそも表の作り方が間違っている

問題

このリストを正しく作り直しなさい。

解説

ところで、このようなリストを作る場合には、並べ替えることを前提として作るべきです。並べ替えを前提とする場合、連番とそれ以外の部分を1つの列で入力し、オートフィルをするのは間違いです。

f:id:waenavi:20190529004516j:plain

 

共通する部分と変わる部分(連番)を分けて入力します。

f:id:waenavi:20190529004519j:plain

 

A列は、B列とC列を計算式によって連結すればよいです。※共通部分を1か所にして絶対参照するという方法もあります。

f:id:waenavi:20190529004522j:plain

f:id:waenavi:20190529004526j:plain

 

これで並べ替えが可能となります。

f:id:waenavi:20190529004529j:plain

 

5.セル内改行で区切って並べ替える

問題

次の図で、月と金額がA列に入っている。金額の降順で並べ替えなさい。

f:id:waenavi:20190529005112j:plain

 

解説

10月の後に改行を入れます。

f:id:waenavi:20190529005830j:plain

 

改行はAltキーを押しながら、Enterキーを入れます。

f:id:waenavi:20190529005825j:plain

 

セルの中で改行するには、Altキーを押しながらEnterキーを押します(参考:【Excel】セル内改行(ショートカットキーAlt+Enter)と長い文字列の取り扱い)。

f:id:waenavi:20190529005828j:plain

 

本来、このように1つのセルの中に2つの情報を入れることは、リストの作り方として間違っています(1セル1情報の原則、参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。

f:id:waenavi:20190529005833j:plain

 

しかし、やむをえずこのようになってしまった場合には改行で分割する必要があります。A列を選択します。区切り位置を選びます。区切り位置指定ウィザードが表示されます。次に進みます。

f:id:waenavi:20190529005837j:plain

 

区切りとなっている文字は改行ですが、この中にないので、その他にチェックを入れます。

f:id:waenavi:20190529005840j:plain

 

カーソルを置きます。点滅しています。

f:id:waenavi:20190529005843j:plain

 

Ctrlキーを押しながら「J」を押します。改行されて点滅が下に隠れました。

f:id:waenavi:20190529005846j:plain

 

2列に分かれています。

f:id:waenavi:20190529005849j:plain

 

セルの中で改行するには、Altキーを押しながらEnterキーですが、区切り位置指定ウィザードの改行は、Ctrlキーを押しながら「J」です。

f:id:waenavi:20190529005853j:plain

 

次に進みます。表示先を消して、B2を選びます。完了します。

f:id:waenavi:20190529005857j:plain

 

改行の前後で分離されました。これで並べ替えをします。

f:id:waenavi:20190529005901j:plain

 

C列の降順にします。

f:id:waenavi:20190529005904j:plain

 

これで完成です。

f:id:waenavi:20190529005909j:plain

 

6.区切り位置指定ウィザードを使った応用事例

(1)完全な空白セルの状態に戻す

ジャンプ機能で空白セルを選択することができますが、一度セルに文字列を入力したもの(使用済みセル)は、文字列を削除しただけでは空白セルとはみなされないので、ジャンプ機能が使えません。この場合、区切り位置指定ウィザードを使うと、完全な空白セルの状態に戻すことができます。

 

(2)文字列を数値に変換する

「文字列」の表示形式で入力した場合や「文字列」の形式で取り込んだ場合の数値文字列を、「数値」に変換するときにも使います。

 

(3)CSV

CSVをそのままExcelにコピー・貼り付けすると、カンマ区切りの文字列のままです。それを分割するには区切り位置指定ウィザードを使います。

 


解説は以上です。


7.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 7-10、7-10-2 のYoutube動画を書き起こして、加筆修正したものです。

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ