[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データから数量を抽出』（エクセル君）
エクセル初心者です。よろしくお願い致します 【Sheet1】のA列、B列に名前と商品が300行ぎっしりあります。 このデータから名前ごとに商品と数を知りたいのです。 【Sheet2】のA2に名前を入力するとB列、C列に数量の多い順に商品 と数量を抽出したいのですが、【Sheet2】B列、C列にはどのような 数式を入れればよいでしょうか？
【Sheet1】
A B
1 山田 みかん
2 田中 りんご
3 奥田 れもん
4 田中 キウイ
5 田中 キウイ
6 山田 いちご
7 田中 りんご
8 山田 みかん
9 三宅 いちご
10 田中 れもん
： ：
： ：
299 岡田 ぶどう
300 三宅 れもん
【Sheet2】
A B C
1 名前 商品 数量
2 田中 キウイ 24
3 いちご 10
4 りんご 8
5 バナナ 6
6 メロン 2
： ： ：
数式でやるのは大変だと思います。 ピボットテーブルを使用するのはどうでしょう。
ライブラリより「ピボットテーブル入門」 https://www.excel.studio-kazu.jp/lib/e2d/e2d.html
ページのフィールドに 名前 行のフィールド に 商品 データアイテム に 商品（←名前でも良いですが）
を落として、ピボットテーブルを作成後 行のフィールドを選択して ピボットテーブルツールバー [ ピボットテーブル(P) ] →並べ替えてトップテンを表示(A)
左側の、自動並べ替えオプションで ●降順 使用するフィールド(G) [ データの個数／商品 ]
と設定しておくと、ページのフィールドで名前を選び直すたびに 該当データが抽出され、数量が降順で並びます。
(HANA)
HANAさん ありがとうございます。 ピポットテーブルで出来ましたが、順番が少ない数量から多い数量になってしまいます。逆にするには どうしたら出来るのでしょうか？ それと、実は自分で【Sheet2】に集計表としてあのように作ってみました。 理想は集計表に表示できればな〜って思ってますが・・・無理ですかね（汗） すいません。（エクセル君）
HANAさん すいません。降順にすれば出来るんですね・・やってみます。 ピポットで質問いいでしょうか？ 実際は
A B C D E F
1 契約\x{fffd}\x{fffd} 売上日 部署 担当 買主 契約形態
2 ： ： ： ： ： ：
ピポットテーブルが初めてでわからないのですが、A1をクリックして
データ→ピポットテーブル/ピポットグラフ→・・・としていくと
「そのピポットテーブルのフィールド名は正しくありません・・・」と
出てまったく進まないんですが何が間違ってるんでしょうか？
エクセル2003使用してます。（エクセル君）
＞理想は集計表に表示できればな〜って思ってますが・・・ このご質問の真意がよく分かりません。
ピボットテーブルで作成した場合 希望とどこが違うのか 箇条書きにしてみて下さい。 ・「名前」を選ぶのではなく、入力したい ・「降順」にしたい。（これは解決済みとは思いますが。） 。。。等
＞「そのピポットテーブルのフィールド名は正しくありません・・・」 に関しては、ピボットテーブルの集計範囲の列の内 見出しが設定されていない列が有ると思います。
ピボットテーブルウィザード 2/3 で選択した範囲の 先頭行の全てのセルに、何か入力が有ることを確認して下さい。 もしもセルの結合がされていたら、そのセルは 入力がない事になるので、結合を解除して 必ず何かが入力されている状態にして下さい。
＞A1をクリックして と言う事なので、もしかしたらF列以降の（表がF列までの場合ですが） 集計したい表以外の列が一緒に範囲として設定されているかもしれません。 確認してみて下さい。
また、範囲は先頭行が見出し行と成るように設定する必要があります。 1行目が見出し行で始まっていない場合は、範囲の再選択が必要です。
(HANA)
ご希望に添えるかどうかわかりまへんが、こんなんどうでっか？ ＼Sheet2／のシートタブを右クリックし、コードの表示を選択して下のコードをコピペ します。
戻ってSheet2のA2に名前を入力してみまひょ。
但し、Sheet1のA1からデータがならんでいると仮定しています。
（弥太郎）
'----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dic As Object, i As Long, j As Long, tbl, x
Set dic = CreateObject("scripting.dictionary")
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "A2" Then Exit Sub
Application.EnableEvents = False
If Target = "" Then Range("b2").Resize(Range("b" & Rows.Count) _
.End(xlUp).Row, 2).ClearContents: GoTo trbl
With Sheets("sheet1")
tbl = .Range("a1").Resize(.Range("a" & Rows.Count).End(xlUp).Row, 2)
ReDim x(1 To UBound(tbl, 1), 1 To 2)
For i = 1 To UBound(tbl, 1)
If tbl(i, 1) = Target And Not dic.exists(tbl(i, 2)) Then
j = j + 1
x(j, 1) = tbl(i, 2)
x(j, 2) = 1
dic(tbl(i, 2)) = j
ElseIf tbl(i, 1) = Target And dic.exists(tbl(i, 2)) Then
x(dic(tbl(i, 2)), 2) = x(dic(tbl(i, 2)), 2) + 1
End If
Next i
End With
Range("b2").Resize(Range("b" & Rows.Count).End(xlUp).Row, 2).ClearContents
If j > 0 Then Range("b2").Resize(j, 2) = x
Range("b2").Resize(j, 2).Sort key1:=Range("c2"), order1:=xlDescending
trbl:
Application.EnableEvents = True
End Sub
HANAさん 弥太郎さん ありがとうございます。
HANAさん すいません。集計表とは【Sheet2】のエクセルの事です。
たいした表ではなくお恥ずかしいです・・・。
A2に名前が入るとその人のデータがBC列に反映されればと。
(エクセル君）
えっと、集計表がSheet2の表のことはわかってます。 そういうことではなくてですね・・・・ たとえばピボットテーブルをSheet2に作成した場合 ＞A2に名前が入ると ではなく、 「表示したい名前を選ぶと」 になりますが ＞その人のデータがBC列に反映 されると思います。 ご希望のように ＞数量の多い順に
そこで、再度お伺いしてみます。 ピボットテーブルで作成した場合 希望とどこが違うのか 箇条書きにしてみて下さい。
ご希望と違う点は、 名前を入力するか名前を選択するか という点しかないと思いますが それ以外に、私の思いつかない何か 「譲れない違う点」があるのかもしれません。 それを教えて下さい。
数式である必要がないのなら 弥太郎さんのコードを試してみられるのが良いと思います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.