Excel（エクセル）での自動改行検索

質問

Excel（エクセル）で
自動で縦書きを横書きにしたいのですが可能でしょうか？

例えば添付画像のように
Ａ１～Ｃ８の表を同じシート内の薄緑の部分が自動で表示されるようにしたいです。
セルＥ２～Ｈ４にどの様な式を入れればよいか教えてください！

Ａ列に対するＢ列の種類が３行と決まっていればわかったのですが、
セルＡ４の愛知のようにランダムで２行の時があり、
それに対応する式がまったくわかりません(ノД`)・゜・。
（マクロの仕方もわかりません）

本当に困っています
ぜひお力添えのほど、よろしくお願い致します！！

回答No.2

KURUMITO · ベストアンサー

お示しの表で1行目には空の行を挿入して、2行目からデータがあるとします。
Ｄ列を作業列としてＤ1セルには0を入力します。Ｄ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2<>"",INT(MAX(D$1:D1))+1+MATCH(B2,E$2:H$2,0)*0.1,IF(B2<>"",INT(MAX(D$1:D1))+MATCH(B2,E$2:H$2,0)*0.1,""))

そこでＥ3セルには次の式を入力して横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(ROW(A1)>MAX($D:$D),"",IF(COLUMN(A1)=1,INDEX($A:$A,MATCH(ROW(A1),$D:$D,1)+1),IF(AND(COLUMN(A1)>1,COLUMN(A1)<=4,COUNTIF($D:$D,ROW(A1)+COLUMN(A1)*0.1)>0),INDEX($C:$C,MATCH(ROW(A1)+COLUMN(A1)*0.1,$D:$D,0)),"")))

お礼

助かりました(*- -)(*_ _)ペコペコ
望んでいたことができました！
回答していただき、
本当にありがとうございます。

回答No.8

tom04

こんばんは！
すでに色々回答は出ていますので、参考程度で・・・

余計なお世話かもしれませんが、A列の都道府県も自動で表示するようにしてみました。
作業用の列を1列設けています。

↓の画像で作業列D2セルに
=IF(B2="","",INDEX($A$2:A2,MAX(IF($A$2:A2<>"",ROW($A$1:A1))))&B2)
これは配列数式になってしまいますので、
この画面からB2セルにコピー＆ペーストしただけではちゃんと表示されません。
B2セルに貼り付け後、F2キーを押す、またはB2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このB2セルをオートフィルで下へずぃ～～～！っとコピーしておきます。

そして、F2セルに
=IF(COUNTA($A$2:$A$1000)<ROW(A1),"",INDEX($A$2:$A$1000,SMALL(IF($A$2:$A$1000<>"",ROW($A$1:$A$999)),ROW(A1))))
（これも配列数式になります。）
G2セルに
=IF(COUNTIF($D$2:$D$1000,$F2&G$1),SUMIF($D$2:$D$1000,$F2&G$1,$C$2:$C$1000),"")
（これは配列数式ではありません。）
G2セルを列方向へオートフィルでコピー！
最後にF2～I2セルを範囲指定し、I2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、数式は元データの1000行目まで対応できるようにしています。
以上、長々と失礼しました。m(__)m

お礼

助かりました(*- -)(*_ _)ペコペコ

＞この画面からB2セルにコピー＆ペーストしただけではちゃんと表示されません。
＞B2セルに貼り付け後、F2キーを押す、またはB2セルをダブルクリック
この文章のＢ２はＤ２のことですよね？

望んでいたことができました！
画像つきでわかりやすく回答していただき、
本当にありがとうございます。

回答No.7

kagakusuki

マクロや配列数式ではなく、作業列不要で、関数のみで自動化する方法です。

　まず、F2セルに次の数式を入力して下さい。

=IF(COUNTIF($A:$A,$E2)=0,"",IF(SUMPRODUCT((COUNTIF(OFFSET($A$1,,,ROW($B$1:$B$8)),"><")=COUNTIF(OFFSET($A$1,,,MATCH($E2,$A:$A,0)),"><"))*($B$1:$B$8=F$1))=0,"",SUMPRODUCT((COUNTIF(OFFSET($A$1,,,ROW($B$1:$B$8)),"><")=COUNTIF(OFFSET($A$1,,,MATCH($E2,$A:$A,0)),"><"))*($B$1:$B$8=F$1)*$C$1:$C$8)))

　そして、F2セルをコピーして、F2～H4の範囲に貼り付けて下さい。

　以上です。

　尚、元の表の行数が変更されたり、セルの切り取りや削除が行われる事がある場合には、F2セルに入力する数式を次の様に変更しますと、元の表が変更されても数式を変えずに済ませる事が出来ます。

=IF(COUNTIF($A:$A,$E2)=0,"",IF(SUMPRODUCT((COUNTIF(OFFSET($A$1,,,ROW(INDIRECT("B1:B"&MATCH("゛",$B:$B,-1)))),"><")=COUNTIF(OFFSET($A$1,,,MATCH($E2,$A:$A,0)),"><"))*(INDIRECT("B1:B"&MATCH("゛",$B:$B,-1))=F$1))=0,"",SUMPRODUCT((COUNTIF(OFFSET($A$1,,,ROW(INDIRECT("B1:B"&MATCH("゛",$B:$B,-1)))),"><")=COUNTIF(OFFSET($A$1,,,MATCH($E2,$A:$A,0)),"><"))*(INDIRECT("B1:B"&MATCH("゛",$B:$B,-1))=F$1)*INDIRECT("C1:C"&MATCH("゛",$B:$B,-1)))))

補足

申し訳ありません。
欲しい数式はＦ２からではなく、
Ｅ２～Ｈ４です。

回答No.6

Wendy02

すでに地域データは正規化されていますからそのままでは出来ません。以下はSumIfW関数だけでも、集計は可能です。

'//標準モジュール
Sub TestMacro1()
　Dim rng As Range
　Dim mReg
　Dim mData
　Dim reg As Range
　Dim rng1 As String, rng2 As String, rng3 As String, arg1 As String, arg2 As String, arg3 As String
　Const fTITLE As Integer = 1 ''0 タイトルなし, 1 タイトルあり
　Set rng = Range("A1").CurrentRegion
　If fTITLE > 0 Then
　　With rng
　　　Set rng = .Offset(fTITLE).Resize(.Rows.Count - fTITLE)
　　End With
　End If
　On Error Resume Next
　Set reg = rng.Columns(1).SpecialCells(4)　'空白
　With reg
　　.Formula = "=R[-1]C"
　End With
　On Error GoTo 0
　mReg = UniqArray(rng.Columns(1))
　mData = UniqArray(rng.Columns(2))
　Range("E2").Resize(UBound(mReg) + 1).Value = Application.Transpose(mReg)
　Range("F1").Resize(, UBound(mData) + 1).Value = mData
　With rng
　　rng1 = .Columns(1).Address(1, 1, xlR1C1)
　　rng2 = .Columns(2).Address(1, 1, xlR1C1)
　　rng3 = .Columns(3).Address(1, 1, xlR1C1)
　End With
　With Range("E1").CurrentRegion
　　arg1 = "RC" & .Cells(1).Column
　　arg2 = "R" & .Cells(1).Row & "C"
　　With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
　　　.Formula = "= SumIfW(" & arg1 & "," & rng1 & "," & arg2 & "," & rng2 & "," & rng3 & ")"
　　.Value = .Value
　　　On Error Resume Next
　　　Set reg = rng.Columns(1).SpecialCells(xlCellTypeFormulas)
　　　reg.ClearContents
　　　On Error GoTo 0
　　End With
　End With
　Set rng = Nothing
End Sub
Function UniqArray(rng As Range)
'ユニークを取る関数
　Dim Ar(), ret 　
　Dim c 　
　Dim i As Long
　ReDim Ar(0)
　For Each c In rng.Cells
　　If Trim(c.Value) <> "" Then
　ret = Application.Match(c.Value, Ar, 0)
　If IsError(ret) Then
　　ReDim Preserve Ar(i)
　　Ar(i) = c.Value
　　i = i + 1
　End If
　　End If
　Next
　UniqArray = Ar
End Function
Function SumIfW(arg1, rng1 As Range, arg2, rng2 As Range, rng3)
Dim i As Long
Dim dSum As Double
For i = 1 To rng1.Rows.Count
　If rng1.Cells(i, 1).Value = arg1 Then
　 If rng2.Cells(i, 1).Value = arg2 Then
　　 dSum = dSum + Val(rng3.Cells(i, 1).Value)
　 End If
　End If
Next
　SumIfW = dSum
End Function

お礼

初歩的なSumIf関数なら本に載ってたのですが、
この式をどこにどのように当てはめれば
いいのかがわかりません。

私の力不足でせっかくの回答を活用できず
申し訳ありませんでした。

回答No.5

mitarashi

#3です。間違えて検討中の式を投稿してしまいました。差し替えさせてください。
F2の式は、
{=SUM(($D$1:$D$8=$E2)*($B$1:$B$8=F$1)*($C$1:$C$8))}
です。
D1は、
{=INDEX($A:$A,MAX(IF($A$1:$A1<>"",ROW($A$1:$A1),"")))}
でOKです。
なお、該当データが無いところは空白ではなく0になりますが、ご容赦下さい。

補足

申し訳ありません。
欲しい数式はＦ２からではなく、
Ｅ２～Ｈ４です。

回答No.4

MackyNo1

ピボットテーブルを利用する場合にも、数式で簡便に計算する場合にも、現在の表のA列の空白セルには以下のような方法で上と同じ名前のデータを追加する必要があります。

A2セルからA列のデータ範囲を選択して、Ctrl+Gでジャンプダイアログを出し「セル選択」「空白セル」でOKし、現在選択されているアクティブセルの1つ上のセルを参照する数式（例示のデータならA2セルで「=A1」）を入力し、Ctrlキーを押しながらEnterしてください。

このようにしておけば、ピボットテーブルや関数で簡単に集計できます。

ちなみに関数で集計したい場合は、以下の数式をF2セルに入力し、右方向および下方向にオートフィルします(例示のレイアウトの場合）。

=SUMPRODUCT(($A$1:$A$10=$E2)*($B$1:$B$10=F$1)*$C$1:$C$10)

もし、０を空白表示したいなら、数式範囲を選択してセルの書式設定の表示形式タブで「ユーザー定義」にして、たとえば「0;;;」などと設定してみてください。

お礼

「上と同じ名前のデータを追加する方法」と
「０の空白表示」は
今後のデータ整理に役立ちそうです。
ありがとうございました(*- -)(*_ _)ペコペコ

補足

申し訳ありません。
欲しい数式はＦ２からではなく、
Ｅ２～Ｈ４です。

回答No.3

mitarashi

配列数式を勉強中です。
D列を作業列にして、Ｄ１に
{=INDEX($A:$A,MAX(IF($A$1:$A1<>"",ROW($A$1:$A1),"")))}
以下、下方にコピー
F2に、
{=SUM(($A$1:$A$8=$E2)*($B$1:$B$8=F$1)*($C$1:$C$8))}
以下、右方・下方にコピー
いずれも配列数式ですので、Ctrl+Shift+Enterで確定してください。
中括弧｛｝は便宜上つけてありますが、自動で入ります。
コピーは普通にできます。

もう一案
空白セルを上のセルの値で埋めるマクロを実行、目障りなら条件付き書式で、フォントを白くしておく
http://www.nurs.or.jp/~ppoy/access/excel/xlM061.html
その後、ピボットテーブルを使用。

参考URL：

http://office.microsoft.com/ja-jp/excel-help/HA010228458.aspx

回答No.1

PXU10652

式もマクロも要りません。こういう時のための機能が「ピボットテーブル」です。このキーワードで、色々ググって見て下さい。

お礼

ありがとうございます。
ピボットテーブルなんて眼中にありませんでした。
少し、ググって勉強しておこうと思います。