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

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

【Excel】セル内改行の応用問題(改行の置換と削除、CHAR関数を使った改行)


Excelのセル内で改行するのはAlt+Enterですが、原則としてセル内改行を用いた文書を作成してはいけません。1つのセルの中で改行をすることはデータ処理の妨げになりますので、できるだけ改行を使わないように工夫しなければなりません。

しかし、データを加工するときに、改行を置換して別の文字にする(例えばBRタグ)とか、改行を削除するということはあります。また、1行のレコードから複数行のデータを作るのに関数で改行を入れることもあります。

そこで、今回はセル内改行の応用問題として、セル内の改行の挿入と置換、削除、Excel関数を用いた改行と置換について出題します。

目次

1.セル内改行について(復習)

セル内改行の基本的な使い方についてはこちらの記事をご覧ください。

  

2.改行コードの置換

(1)セル内改行を削除する

問題

6チームのトーナメントの表を作りました。B列には予選の順位とチーム名が入力されています。

f:id:waenavi:20191112214349j:plain

 

B列のデータを用いて、別のシートに次のようなリストを作りなさい。

f:id:waenavi:20191112214413j:plain

 

解説

B列をコピーして別のシートに貼り付けます。

f:id:waenavi:20191112215449j:plain

f:id:waenavi:20191112215453j:plain

 

セルの中で改行をしています。これをスラッシュに置換します。置換の画面を出します(参考:Excelの「置換」のルールと注意点を習得するための基本トレーニング)。

f:id:waenavi:20191112215456j:plain

 

検索する文字列のところにカーソルを置きます。CtrlキーとJのキーを同時に押します。何も表示されませんが、何回も押してはいけません。

f:id:waenavi:20191112215459j:plain

 

この画面でCtrlキーとJのキーを押すと改行コードが入ります。改行を入れても画面上何も変わりません。

f:id:waenavi:20191112215502j:plain

 

置換後の文字列にはスラッシュ(/)を入れます。

f:id:waenavi:20191112215506j:plain

 

すべて置換します。改行がなくなってスラッシュ(/)になります。

f:id:waenavi:20191112215508j:plain

 

この置換の画面には、改行コードが残っています。検索する文字列のところで、DeleteキーやBackspaceキーを2、3回押します。

f:id:waenavi:20191112215511j:plain

 

改行コードが入っているかは画面上大変わかりにくいので、置換が終わったら、DeleteキーとBackspaceキーを何回か連打して、改行コードを削除して終わります。

f:id:waenavi:20191112215515j:plain

 

セルの結合を解除します。

f:id:waenavi:20191112215517j:plain

 

条件を選択して、ジャンプします。空白を選択します(参考:【Excelジャンプ】空白セルを一括選択して一括コピーして一括削除する)。

f:id:waenavi:20191112215520j:plain

 

このままセルを削除して、上にシフトすれば完成です。

f:id:waenavi:20191112215523j:plain

f:id:waenavi:20191112215528j:plain

f:id:waenavi:20191112215530j:plain

 

(2)セル内改行に置換する

問題

さらに、さきほどのリストで、スラッシュを改行に置換して次の表を作りなさい。

f:id:waenavi:20191112214454j:plain

 

解説

B列を選択します。

f:id:waenavi:20191112220606j:plain

 

念のため、検索する文字列でDeleteキーとBackspaceキーを何回か連打しておきます。

f:id:waenavi:20191112220610j:plain

 

検索する文字列にスラッシュ(/)を入れます。置換する文字列のスラッシュを消して、CtrlキーとJのキーを同時に押します。

f:id:waenavi:20191112220613j:plain

 

すべて置換します。これでスラッシュ(/)が改行になります。

f:id:waenavi:20191112220616j:plain

 

置換が終わったら、DeleteキーとBackspaceキーを何回か連打して、改行コードを削除して終わります。

f:id:waenavi:20191112220620j:plain

 

最後に行の高さを広げて完成です。

f:id:waenavi:20191112220626j:plain

 

3.CHAR関数を使った改行の練習

(1)CHAR(10)で改行する

問題

次の会員名簿は、1名の会員の情報が1行で入力されている。

f:id:waenavi:20191112221013j:plain

 

1行目の項目名と各データを改行で連結して、1名の会員につき2行のデータにしなさい。

f:id:waenavi:20191112222144j:plain

 

解説

会員情報のように、大量のデータを入力するには原則として、1件のレコードを1行で入力しなければなりません(参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。複数行のデータを作るには、このリストを元にして、数式で改行を挿入します。

f:id:waenavi:20191112222402j:plain

 

セル内改行を数式内で記述するときもAlt+Enterで可能ですが(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)、関数を使う方法もあります。

関数を使う場合はCHAR関数で、「CHAR(10)」と記述します。これはセル内の改行(LF=Line Feed)の文字コードが10番であることを表しています(参考:【ExcelとCSV】本気で理解したい初心者のためのCSV勉強会資料)。

f:id:waenavi:20191112232012j:plain

 

「=B2&CHAR(10)&C2&$C$1」と入力します。「所属」は絶対参照です。

f:id:waenavi:20191112232055j:plain

 

これで氏名と所属が連結できますが、改行が入っていないように見えます。

f:id:waenavi:20191112232141j:plain

 

これはセルの書式設定で、折り返しの設定になっていないことが原因です。折り返して全体を表示するボタンを押します。列幅を広げます。これで改行ができました。

f:id:waenavi:20191112232234j:plain

 

(2)SUBSTITUTEで改行を置換する

問題

SUBSTITUTE関数を用いて改行を削除しなさい。また、改行を半角のアットマーク@に置換しなさい。

f:id:waenavi:20191112232331j:plain

 

解説

前述のとおり、改行の置換は改行の画面で行うこともできますが(Ctrl+J)、関数で行うことも可能です。置換をする関数はSUBSTITUTEです。

  • =SUBSTITUTE(対象となる文字列,検索文字列,置換文字列,[置換対象])

改行を空白に置換します。改行はCHAR(10)です。改行が削除されて1行になります。

  • =SUBSTITUTE(A1,CHAR(10),"")

f:id:waenavi:20191112232445j:plain

 

改行の代わりにアットマークを挿入するには、改行を@に置換します。

  • =SUBSTITUTE(A1,CHAR(10),"@")

f:id:waenavi:20191112232531j:plain

 

(3)行数が多い場合の考え方

問題

次の会員名簿は1名の会員の情報が1行で入力されている。

f:id:waenavi:20191112221013j:plain

 

1行目の項目名と各データを、全角のコロンと改行で連結して、1名の会員につき4行のデータにしなさい。

f:id:waenavi:20191112232708j:plain

 

また、次のように1行空けながら入力しなさい。

f:id:waenavi:20191112232741j:plain

 

解説

4行のデータを作る場合も、2行の場合と考え方は同じですが、数式が長くなるので分けて考えたほうが良いです。まず、1行目の項目名と各データを連結します。なお、項目名は行固定の複合参照です。

  • =A$1&":"&A2

f:id:waenavi:20191112232948j:plain

 

4列のデータをCHAR(10)で連結します。

  • =E2&CHAR(10)&F2&CHAR(10)&G2&CHAR(10)&H2

f:id:waenavi:20191112233021j:plain

 

折り返して全体を表示するボタンを押します。列幅を広げます。これで改行ができました。

f:id:waenavi:20191112233326j:plain

 

最後にCHAR(10)を連結します。最後に改行を入れたので5行になります。

  • =E2&CHAR(10)&F2&CHAR(10)&G2&CHAR(10)&H2&CHAR(10)

f:id:waenavi:20191112233423j:plain

 

これをコピーします。

f:id:waenavi:20191112233600j:plain

 

メモ帳に貼り付けます。CHAR(10)を含むデータをメモ帳に貼り付けるとその前後にダブルクォーテーションが入ります。

f:id:waenavi:20191112233733j:plain

 

置換します。

f:id:waenavi:20191112233831j:plain

 

ダブルクォーテーションを空白にすべて置換することによって、すべて削除します。

f:id:waenavi:20191112233938j:plain

f:id:waenavi:20191112234045j:plain

 

これを全部選択してコピーします。

f:id:waenavi:20191112234118j:plain

 

Excelに貼り付けます。

f:id:waenavi:20191112234154j:plain

 


解説は以上です。


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

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

 


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