[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ピボットテーブルに文字列を表示させたい』（YUMI）
周りのExcel使用者からは「無理でしょ」と言われたのですが、 どうしても納得できないので本当にダメかどうか教えて下さい。
下記データ群から表を作成したいです。 データ群(数字も含めてすべて文字列です) 項目 名称 値 項目１ 名称１ 0.01 項目１ 名称２ 0.25 項目１ 名称３ 0.30 項目２ 名称１ 青 項目２ 名称２ 赤 項目２ 名称３ 緑 項目３ 名称１ 19850 項目３ 名称２ 18012 項目３ 名称３ 14000 項目４ 名称１ 無 項目４ 名称２ 1.5 項目４ 名称３ 13
表 名称１ 名称２ 名称３ 項目１ 0.01 0.25 0.30 項目２ 青 赤 緑 項目３ 19850 18012 14000 項目４ 無 1.5 13
ピボットテーブルで枠の形は出来たのですが、中身が出ません。 名称もしくは項目が１つだけならLOOKUPでどうにかなるのですが、 ピボットテーブルではできないでしょうか。
今は、ピボットテーブルで枠だけ作り、１項目ずつLOOKUPで埋めています。 マクロを使わないとダメでしょうか。 教えて下さい。
[エクセルのバージョン] Excel2003
[OSのバージョン] WindowsXP
INDEX関数とMATCH関数の組み合わせがぴったりと思います。(ROUGE)
INDEX関数とMATCH関数について勉強し、いろいろ試してみましたがこの関数はセルを 一つ一つ指定する形なので、項目群や名称群が増えたり減ったりした時には使えないことが わかりました。 提示した条件が足りなくて申し訳ございません。 物件によって項目１に対して名称が10だったり、項目15に対して名称が8だったりします。 そして、表が全部埋まるわけではなく、空白(該当なし)もあり得ます。
私はデータベースの管理者で、上記のようなデータ群で困っていないのですが、
現場の人が一覧表の形にして確認をしているので、その手間を減らせないかと考えて
いる次第です。
現場の人には難しいExcelの関数を教えるのは無理なので、なるべく少ない操作で
一覧表の形にしたいのです。
データ群は随時追加されていくので、出来れば空白の表だけ先に作成しておき、
データ群を更新すると表に追加されていくというのがベストですが、
その都度作成する形でも構いません。
いい方法がありましたら教えて下さい。
（YUMI）
元データが、Sheet1にこの様な状態で有る場合 Sheet1 [A] [B] [C] [1] 項目 名称 値 [2] 項目１ 名称１ 0.01 [3] 項目１ 名称２ 0.25 [4] 項目１ 名称３ 0.3 [5] 項目２ 名称１ 青 [6] 項目２ 名称２ 赤 [7] 項目２ 名称３ 緑 [8] 項目３ 名称１ 19850 [9] 項目３ 名称２ 18012 [10] 項目３ 名称３ 14000 [11] 項目４ 名称１ 無 [12] 項目４ 名称２ 1.5 [13] 項目４ 名称３ 13
●案１ おそらく、現在の方法と変わらないのでしょうけど・・・。
例えば、Sheet2にピボットテーブルで、見出しをつくり それぞれの個数を集計します。 Sheet2 [A] [B] [C] [D] [1] データの個数 / 値 名称 [2] 項目 名称１ 名称２ 名称３ [3] 項目１ 1 1 1 [4] 項目２ 1 1 1 [5] 項目３ 1 1 1 [6] 項目４ 1 1 1
Sheet3に見出し部分をSheet2から、データ部分をSheet1から参照します。 Sheet3 [A] [B] [C] [D] [1] 名称１ 名称２ 名称３ [2] 項目１ 0.01 0.25 0.3 [3] 項目２ 青 赤 緑 [4] 項目３ 19850 18012 14000 [5] 項目４ 無 1.5 13 A1に =IF(Sheet2!A2="","",Sheet2!A2) として、A列と1行目の範囲にフィルドラッグ後、A1セルの式を削除。 B3に =IF(Sheet2!B3>0,INDEX(Sheet1!$C$1:$C$100, SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)*ROW($A$1:$A$100)),),"") として、必要範囲にフィルドラッグ。
気をつける必要があるのは、関数ですのでそれぞれの範囲がデータ範囲内に納まっている事 また、Sheet1で入力をし、Sheet2で更新をした場合にのみSheet3に目的の結果が表示される （Sheet3に結果を表示させる迄に手順が必要）と言う点があげられると思います。
●案2 見出し行も関数でつくって仕舞う方法です。 結果はSheet3と同じ表示になります。 A2セルに =IF(SUM(IF(Sheet1!$A$1:$A$100<>"",1/COUNTIF(Sheet1!$A$1:$A$17,Sheet1!$A$1:$A$100)))<ROW(A2),"", INDEX(Sheet1!$A$1:$A$100,SMALL(IF(ISERROR(MATCH(Sheet1!$A$1:$A$100,Sheet1!$A$1:$A$100,0)),99^99, IF(MATCH(Sheet1!$A$1:$A$100,Sheet1!$A$1:$A$100,0)=ROW($A$1:$A$100),ROW($A$1:$A$100),99^9)),ROW(A2)),1)) をペーストして【Ctrl + Shift + Enter】で確定
B1セルに =IF(SUM(IF(Sheet1!$B$1:$B$100<>"",1/COUNTIF(Sheet1!$B$1:$B$100,Sheet1!$B$1:$B$100)))<COLUMN(B1),"", INDEX(Sheet1!$B$1:$B$100,SMALL(IF(ISERROR(MATCH(Sheet1!$B$1:$B$100,Sheet1!$B$1:$B$100,0)),99^99, IF(MATCH(Sheet1!$B$1:$B$100,Sheet1!$B$1:$B$100,0)=ROW($A$1:$A$100),ROW($A$1:$A$100),99^9)),COLUMN(B1)),1)) をペーストして【Ctrl + Shift + Enter】で確定
B2に =IF(AND($A2<>"",B$1<>""),INDEX(Sheet1!$C$1:$C$100, SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)*ROW($A$1:$A$100)),),"") をペーストして Enter で確定
それぞれ必要範囲にフィルドラッグしてください。
こちらは、範囲が広いと計算に時間がかかります。
●案3 マクロを使う方法です。 Sheet5に書き出します。 Sub 配置換え() Dim i As Long, xr As Long, xc As Long Dim dicA As Object, dicB As Object, tbl, x
Set dicA = CreateObject("scripting.dictionary")
Set dicB = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
tbl = .Cells(1, 1).Resize(.Cells(Rows.Count, 1).End(xlUp).Row, 3)
End With
ReDim x(1 To UBound(tbl, 1) + 1, 1 To UBound(tbl, 1) + 1)
For i = 2 To UBound(tbl, 1)
If Not dicA.exists(tbl(i, 1)) Then
xr = xr + 1
dicA(tbl(i, 1)) = xr + 1
x(xr + 1, 1) = tbl(i, 1)
End If
If Not dicB.exists(tbl(i, 2)) Then
xc = xc + 1
dicB(tbl(i, 2)) = xc + 1
x(1, xc + 1) = tbl(i, 2)
End If
x(dicA(tbl(i, 1)), dicB(tbl(i, 2))) = tbl(i, 3)
Next i
With Sheets("Sheet5")
.Cells.ClearContents
.Range("A1").Resize(xr + 1, xc + 1) = x
End With
Set dicA = Nothing
Set dicB = Nothing
End Sub
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.