エクセル 抽出した値を別々に表示させたい

質問

たとえば、Aという項目のとなりの列に「ああ」、「いい」とランダムに文字列があります。重複ありです。

別シートにて集計したいのですが、該当するすべての値を横1列に表示させたいのです。重複する値を別々に表示させることはできますか？

回答No.9

macchan1 · ベストアンサー

これまでの例示されたデータなら問題なく抽出できるのですが、レイアウトが変われば当然ご希望の結果が得られません。
このようなご質問では「例えば」ではなく、できる限り実際のデータ形式を例示した方が的確な回答が得られると思います。
特にマクロで対応する場合は、当然ですがセル位置などが違うとうまく作動しませんのでご注意ください。

提示した数式の説明をすると（配列数式の意味はわかるという前提で）、

=INDEX(Sheet3!$C$1:$C$100,SMALL((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1)*ROW($A$1:$A$100),SUMPRODUCT((((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1))=0)*1)+COLUMN(A1)),1)

（Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1)*ROW($A$1:$A$100)の部分は

Sheet3のA1～A100セルの値がA1セルと等しい配列＊Sheet3のB1～B100セルの値がA1セルと等しい配列は両方が成立する配列を返します。それにROW($A$1:$A$100)を掛けるとその条件の行番号の配列が求められます（すなわち、両方の条件が成り立つ行の数字の配列が求められ成立しないところは0が返る）。

SUMPRODUCT((((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1))=0)*1）の部分は2つの検索条件に一致しないデータの個数を求めています。

SMALL(上記の配列,上記の一致しないデータの個数+COLUMN(A1))

の式は、上記の配列を小さい順に並べて、0以外の数値を除いた一番小さい数（COLUMN(A1)は1を返す、その右の列は2）になるので、2つとも一致するデータで一番初めに出てくるデータが何行目にあるかが求められます。

最後に=INDEX(Sheet3!$C$1:$C$100,SMALL関数の返す行数,1)でSheet3!$C$1:$C$100の中から該当するデータを抽出しています。

こちらではデータシートの詳細なレイアウトが分かりませんので、実際のレイアウトに沿った数式に変更してみて下さい。

回答No.8

macchan1

＃5と＃6の私の回答ではうまくいかないのでしょうか？

もしうまくいかないのでしたらどのように表示されるかお知らせください。

補足

うまくいかないというか、こちらの情報不足のためか、意味不明でした。
具体例で言います（最初からそうすればよかったですね）。

以下のような表があります
A 　　　　　　B　　　　　　　C
部門　　　 勘定科目　　　　　金額
954　　　　郵送料　　　　　９００
963　　　　消耗品費　　　　５００
911　　　　郵送料　　　　　８０
954　　　　消耗品費　　　　５００
954　　　　郵送料　　　　　２７０
954　　　　郵送料　　　　　９００

部門ごとのシートに勘定科目と金額を集計するのですが、値も別々に表示させたい。

たとえば954のシートに
　A　　　　B　　　C　　　D　　　E　　　F
　　　　　　　　　　　　　　　　　（B～Eの合計 ）
郵送料　９００　２７０　９００　　　　２０７０ 　　　　　　　　　　　　　　　　
消耗品　５００　　　　　　　　　　　　５００

旅費　　　　　　　　　　　　　　　　　　　０

としたい場合の　B、C、D、Eに入れる関数を教えてください。
一旦一つのセルにリスト形式で表示させる方法でも構いません。

できれば意味を教えていただけると助かります。
実際は並び方も、セル番号も違います。

回答No.7

harukabcde

＃４

２以降はAかつ「いい」　なり、　Bかつ「ああ」なり条件を変えるだけと思っていたのですが
---
ということは、別シートでは１行のみ使い、
その行のＡＢ列を変更しながらＣ列に表示する
ということなのですか？

転送先シート
　 A　 B　　 C1 C2 C3
1　A　ああ　123 908 567

--↑不明？
C2は、D1？
C3は、E1？

こちらは？

補足

転送先シート
　 A　 B　　 C1 C2 C3
1　A　ああ　123 908 567

--↑不明？
C2は、D1？
C3は、E1？

C1,C2,C3　は列番号でいうとC,D,E　です。
Cという項目の1番目、2番目、3番目という意味で、ランダムです、とお伝えしたかったのですが。ちなみに重複の値もありです。

回答No.6

macchan1

すみません、補足します。
#5の式はCtrl+Shift+Enterで確定して配列数式にする必要があります。

エラー値を表示しないようにする場合は、IF関数で以下のような式にして下さい。

=IF(SUMPRODUCT((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1))<COLUMN(A1),"",元の式）

回答No.5

macchan1

関数で対応するなら以下のような感じになります。
Sheet3のデータを別シートのC1セルに表示する例です。

=INDEX(Sheet3!$C$1:$C$10,SMALL((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1)*ROW($A$1:$A$100),SUMPRODUCT((((Sheet3!$A$1:$A$100=$A1)*(Sheet3!$B$1:$B$100=$B1))=0)*1)+COLUMN(A1)),1)

上記の式は、数式が複雑になるのでエラー処理はしてありません（データが無い場合に空白表示になるようにしていない）。

回答No.4

harukabcde

＃２

転送先シート
　 A　 B　　 C1 C2 C3
1　A　ああ　123 908 567

--↑不明？
C2は、D1？
C3は、E1？

1　A　ああ　123 908 567
の次は、
2　B　ええ　345
なのか、
2　A　せせ　238
なのか、
どちらでもいいのか
（ソート順は？）
にも寄ります。

補足

1　A　ああ　123 908 567
の次は、
2　B　ええ　345
なのか、
2　A　せせ　238
なのか、
どちらでもいいのか
（ソート順は？）
にも寄ります。

　　↑
次の指定も必要ですか？
１の例でいくと、Aかつ「ああ」を満たした値を抜き出す。すべての値を抜き出す（重複もあり）。としたいのです。

２以降はAかつ「いい」　なり、　Bかつ「ああ」なり条件を変えるだけと思っていたのですが。

説明が下手で申し訳ありません。

回答No.3

Wendy02

最初にすみませんが、私は、関数は苦手なので、VBAにさせてください。たぶん、どなたか関数で出来るかとは思います。とりあえず、最初に回答させていただきます。

メニューから
ツール－マクロ－Visual Basic Editor-
挿入－標準モジュール(M)
で、以下を貼り付けてください。
後は、フォーム・ボタンあたりで、マクロの登録をすると便利かと思います。
マクロ－マクロ-「TenkiPrc 」でも、実行できます。

ただし、コピー元のデータは、A1 からあるものとしています。

Sub TenkiPrc()
　Dim Sh1 As Worksheet
　Dim Sh2 As Worksheet
　Dim i As Long, j As Long
　Dim k As Long, m As Long, n As Long
　Dim myData() As Variant '配列で確保
　'シートを確認してください。
　Set Sh1 = Worksheets("Sheet1")
　Set Sh2 = Worksheets("Sheet2")
　With Sh2
　　'転送先のシートのデータの消去
　　.Range("A1").CurrentRegion.ClearContents
　　'コピー元のデータを転送先シートのA1から、そのままコピー
　　Sh1.Range("A1").CurrentRegion.Copy .Range("A1")
　　.Activate
　　Application.ScreenUpdating = False '画面の切り替え停止
　　　'並び替え
　　.Range("A1").Sort Key1:=.Range("A1"), Key2:=.Range("B1"), _
　　　　　Order1:=xlAscending, _
　　　　　Header:=xlGuess, _
　　　　　OrderCustom:=1, _
　　　　　MatchCase:=False, _
　　　　　Orientation:=xlTopToBottom, _
　　　　　SortMethod:=xlPinYin
　　m = 1: k = 0　'初期値
　　For i = 1 To .Range("A1").CurrentRegion.Rows.Count + 1
　　　'最後にセルのダミーを入れる +1
　　　ReDim Preserve myData(1, i - 1)
　　　myData(0, i - 1) = .Cells(i, 1).Value & "," & .Cells(i, 2).Value
　　　myData(1, i - 1) = .Cells(i, 3).Value
　　Next i
　　.Range("A1").CurrentRegion.ClearContents
　　For i = LBound(myData(), 2) To UBound(myData(), 2) - 1
　　　If myData(0, i) <> myData(0, i + 1) Then
　　　'上と下が違ったら、実行
　　　　.Cells(m, 1).Value = _
　　　　　 Left(myData(0, i), InStr(myData(0, i), ",") - 1)
　　　　　 'データの切り出し:左側
　　　　.Cells(m, 2).Value = _
　　　　　 Mid(myData(0, i), InStr(myData(0, i), ",") + 1)
　　　　　 'データの切り出し:右側
　　　　For n = k To i
　　　　　.Cells(m, 3).Offset(, j).Value = myData(1, n)
　　　　　　'C列からの出力
　　　　　　j = j + 1
　　　　Next n
　　　　j = 0: k = i + 1: m = m + 1
　　　End If
　　Next i
　　Application.ScreenUpdating = True '画面の切り替え
　End With
　 MsgBox "終了しました。", 64
End Sub

回答No.2

harukabcde

該当するすべての値を横1列に表示させたいのです
って、

元のデータ
　 A　 B　
1　A　ああ
2　B　いい
3　C　うう
4　A　ええ
5　B　おお
6　A　ああ
　　　↓
転送先シート
　 A　 B　　 C
1　A　ああ　ええ
2　B　いい　おお
3　C　うう

って？ことかな

「集計したいのですが」
どう集計？したいの

「重複する値を別々に表示」
転送先シート
　 A　 B　　 C　　ｄ
1　A　ああ　ええ　ああ
2　B　いい　おお
3　C　うう
ってこと？

回答No.1

Wendy02

質問の情報が不足しています。

元のデータ
　 A　 B　　 C
1　A　ああ　いい
2　B　ええ　うう
3　C　かか　いい
4　A　ああ　いい

　　　↓
転送先シート
　 A　 B　　 C
1　A　ああ　いい
2　A　ああ　いい

という意味ですか？

もし、その場合、何を基準にして、集計させるのですか？
列ですか、列の文字列ですか？

関数でお望みですか？それとも、VBAですか？

補足

すみません。
確かに情報不足でした。

元のデータ
　 A　 B　　 C
1　A　ああ　123
2　B　ええ　345
3　C　かか　234
4　A　ああ　908
5　C　いい　469
6 A せせ 238
7　A　ああ 567

　　　↓
転送先シート
　 A　 B　　 C1 C2 C3
1　A　ああ　123 908 567

例を出していただいたとおり3項目並んでいるとして、
AとBの両方を満たすという条件でCの値を求めたいのですが、複数ある場合、横一列に表示させたいのです。

簡単な関数があれば教えてください。
VBAでも、勿論うれしいのですが、知識不足なため、
今はゆっくりと学習している暇がありません。

お願いいたします