さんだーさんだ!(ブログ版)

2015年度より中高英語教員になりました。2020年度開校の幼小中混在校で働いています。

各セルを一文ごとに区切って縦に並べる

シリーズエクセリストへの道。
みなさん、こんな場面はありませんか?

A B C
1 発言 発言者 発言年
2 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。夏の夜の真ん中月の下。喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。 ポルノグラフィティ 2001
3 例え暗い星の見えない夜でもその名前呼ぶから。目には見えない不思議なサイン。瞼に銀のタトゥー。 YUKI 2009
4 新宿は豪雨。 東京事変 2004

こんな表を、一文ごとに区切りたい。区切るだけなら「区切り位置」でいいけど、各文をA列に縦に並べたい。ああ〜あるある〜。

このくらいの量なら手動でやってもいいですが、大量にあると面倒ですよね。
全ての文章の終わりに「。」があることに着目して、どう切り分ければよいか書いておきます*1


①各セル内「。」を数える*2

=LEN(A1)-LEN(SUBSTITUTE(A1,"。",""))

A1セル内の文字数から、A1セル内の「。」を取り去った(""で置換した)文字数を引けば、「。」の数が出る*3
これをD2セル辺りに入れとく。


②その文字数から1を引く。

=D2-1

辺りをE2セルに入れる。うーん簡単。1を引く理由は、あるセルに「。」が3つ入っていた場合、そのセル内には「3」文あるということで、下に挿入したいのは「2」行だから。

A B C D E
1 発言 発言者 発言年 挿入行数
2 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。夏の夜の真ん中月の下。喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。 ポルノグラフィティ 2001 3 2
3 例え暗い星の見えない夜でもその名前呼ぶから。目には見えない不思議なサイン。瞼に銀のタトゥー。 YUKI 2009 3 2
4 新宿は豪雨。 東京事変 2004 1 0

こんな感じ。


③E列にある数字分だけ、その行の下に新しい行を挿入する。
ここは多分マクロを組まないと無理っぽい。

Sub 行挿入()
n_max = 200 '大体全部で何行になるか計算。n_maxとして保存
	For i = 2 To n_max	'1行目はタイトル行。2行目以下、「その下に何行入れたいか」の数字
	    a = Cells(i, 5).Value 'E列にその数字(aとする)が入っている
        If a >= 1 Then 'a>0(下にa行入れたい)なら
	            For j = 1 To a
	                Rows(i + j).Insert	'1行ずつ、a行追加されるまで足し続ける
	            Next j
	        End If
	Next i
End Sub

多分こんな感じ。ヤフー知恵袋さんにお世話になりました。すると、こんな感じの表になる。

A B C D E
1 発言 発言者 発言年 挿入行数
2 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。夏の夜の真ん中月の下。喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。 ポルノグラフィティ 2001 3 2
3
4
5 例え暗い星の見えない夜でもその名前呼ぶから。目には見えない不思議なサイン。瞼に銀のタトゥー。 YUKI 2009 3 2
6
7
8 新宿は豪雨。 東京事変 2004 1 0

ちなみに今やってる作業では行数が70,000越えを達成して、初めてプログラムを回してる間に寝て翌朝作業ということをしました(始めるのが遅かっただけだけど)。


④最初の「。」までを切り出す

=LEFT(A2,FIND("。",A2))

これをF列(「発言一文」)2行目に入れておく。


⑤「発言」ー「発言一文」

=RIGHT(A2,LEN(A2)-LEN(F2))

これをG列(「発言残り」)2行目に入れておく。


さて、各発言を最初に区切る時はこれでよいが、2文目以降を切り出す時は、「発言残り」から一文ごと切り出していく必要がある。よって、たとえばF3には以下のような関数を入れる。


⑥2文目以降の切り出し

=LEFT(G2,FIND("。",G2))

そんでもって、G3セルはG2からF3を引けば良い。


⑦2文目以降の残りの切り出し

=RIGHT(G2,LEN(G2)-LEN(F3))

ここで、各発言の最初の文を切り出すときには④・⑤、2文目以降を切り出すときには⑥・⑦を適用すればよいことが分かる。あとは、最初の文を切り出すべき行には、E列に数字が入っていることに着目して、たとえばF5には以下のような関数を入れる


⑧ ④・⑥の統合(文切り出し)

=IF(E5="",LEFT(G4,FIND("。",G4)),LEFT(A5,FIND("。",A5)))

「=IF(E5="",⑥,④) #E5が空=2文目以降」って感じ。

そしてG6には以下のような関数


⑨ ⑤・⑦の統合(残り切り出し)

=IF(E5="",RIGHT(G4,LEN(G4)-LEN(F5)),RIGHT(A5,LEN(A5)-LEN(F5)))

「=IF(E5="",⑦,⑤)」って感じ。

すると、最終的には以下のような表が!

A B C D E F G
1 発言 発言者 発言年 挿入行数 発言一文 発言残り
2 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。夏の夜の真ん中月の下。喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。 ポルノグラフィティ 2001 3 2 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。 夏の夜の真ん中月の下。喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。
3 夏の夜の真ん中月の下。 喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。
4 喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。
5 例え暗い星の見えない夜でもその名前呼ぶから。目には見えない不思議なサイン。瞼に銀のタトゥー。 YUKI 2009 3 2 例え暗い星の見えない夜でもその名前呼ぶから。 目には見えない不思議なサイン。瞼に銀のタトゥー。
6 目には見えない不思議なサイン。 瞼に銀のタトゥー。
7 瞼に銀のタトゥー。
8 新宿は豪雨。 東京事変 2004 1 0 新宿は豪雨。

(表汚な…)

あとはチェックしてF列だけ残してA・D・E・G消しちゃって、ほんで消去後の
AB列選択して、

編集→ジャンプ→セルの選択→空白セル→=(一個上のセル)→Ctrl+Enter*4

で、以下の感じに!

A B C
1 発言者 発言年 発言一文
2 ポルノグラフィティ 2001 ひらりひらりと舞い遊ぶように姿見せたアゲハ蝶。
3 ポルノグラフィティ 2001 夏の夜の真ん中月の下。
4 ポルノグラフィティ 2001 喜びとしてのイエロー、憂いを帯びたブルーに、世の果てに似ている漆黒の羽。
5 YUKI 2009 例え暗い星の見えない夜でもその名前呼ぶから。
6 YUKI 2009 目には見えない不思議なサイン。
7 YUKI 2009 瞼に銀のタトゥー。
8 東京事変 2004 新宿は豪雨。

お疲れ様でした。
本当はA列に全体の通し番号を振っておいて、その直右で作業すれば、セル右下ダブルクリックで一気にしたまで同種の関数が埋まるとか小技はあるけど、練って書くのがなかなかめんどくさかったからこの辺で。

夜頑張ると朝起きるのがもはや朝じゃなくなるから、すごく損した気分。うーむ。

*1:同じことをやる場面が近々でてきそうだし

*2:どうでもいいけど「。」が句点、「、」が読点ってなんかごっちゃになる

*3:これ知った時は賢い方法もあるもんだなあと感動した

*4:わりと今まで書いてきた中で一番役立つ機能じゃないかと思う