[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ピボットテーブルの関数です』（miyama）
以下のコードは工事ＰＴテーブルのデータをシートに貼り付けるコードです
工事ＰＴ!$D$6:O$10000はデータの範囲です
$F$1,工事ＰＴ!$D$5:$O$5は、Ｆ１は月表示です。
ｄ５からＯ５は１２ヶ月の月が表示されています
$A4,工事ＰＴ!$B$6:$B$1000は、大分類で、Ａ４には工事ＮＯが表示、
$B$6:$B$1000は工事ＮＯが表示
F$3,工事ＰＴ!$C$6:$C$10000は、Ｆ３は原価コードで、
$C$6:$C$10000は原価コードが表示
大分類と小分類の一致した、テーブルの行を取得、月が重なる
金額を表示したいのですが
正しく表示されるのもあるのですが、エラー表示となるセルもあります
ご指導お願いします
説明下手ですいません
Ｆ４に下記関数を貼付けた場合です。
=IF(ISNA(INDEX(工事ＰＴ!$D$6:O$10000,MATCH($F$1,工事ＰＴ!$D$5:$O$5,0),MATCH($A4,工事ＰＴ!$B$6:$B$1000,0)*MATCH(F$3,工事ＰＴ!$C$6:$C$10000,0))),0,INDEX(工事ＰＴ!$D$6:$O$10000,MATCH($F$1,工事ＰＴ!$D$5:$O$5,0),MATCH($A4,工事ＰＴ!$B$6:$B$1000,0)*MATCH(F$3,工事ＰＴ!$C$6:$C$10000,0)))
ピボットテーブルは以下の形です。合計:借方金額 Ｂ４(データ) 年月表示 Ｄ４(列)
工事ＮＯ Ｂ５(行) 大分類 原価コード Ｃ５(行) 小分類
合計 : 借方金額 年月表示
工事ＮＯ 原価コード 6月 7月 8月
2111 5412 1205509 2280
5435 165850 78740 5467 16462 2019 5412 2300 2009 5412 15854372
結局、元データはどの様に成っていて どの様に集計(参照？)したいのですか？
そして、ピボットテーブルはどのシートに在って 今回のご質問とどの様に関係しているのでしょう？
元データの方も [A] [B] [C] [1] [2] [3] の形でどうなっているのか 説明が在ると良いと思います。
それから、最終的にどの様に成れば良いのかも 書いておかれるのが良いと思います。
文頭に半角スペースを入れると 改行がそのまま表示されるようになります。 _←ここに半角スペース。 プレビュー画面でご確認下さい。
(HANA)
HANA様ありがとうございます 質問下手で申し訳ありません。 回答がつかなかったため、別のところで質問しました。 取消しようと思ったら、回答がありました。申し訳ありません 改めてお願いします
ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙの形は以下です。 行は工事ＮＯ（ｂ６） 列は年月表示（Ｃ４）、原価コード（Ｄ４） データは、合計：借方金額（Ｄ４）です Ｂ７に工事ＮＯを表示 年月表示はＣ５右行に表示、列は飛びます 工事ＮＯは、Ｂ７行の右に表示 合計：借方金額は、Ｃ７からＢＢ列より下に金額を表示。 合計 : 借方金額 年月表示 原価コード ＿＿＿＿＿＿＿＿6月＿＿＿＿＿＿＿＿＿＿＿＿＿ 7月 工事ＮＯ＿＿＿ 5412＿＿ 5431＿＿ 5441＿＿ 5412＿＿ 5431 2002＿＿＿＿＿134071 2003＿＿＿＿＿＿15854＿＿88000 2019＿＿＿＿＿＿2300 2111＿＿＿＿＿＿12055 1＿＿＿＿＿＿＿＿27373＿＿17615 3＿＿＿＿＿＿＿＿8020 4＿＿＿＿＿＿＿346178＿＿41104＿＿32000＿＿43055
シート集計のＦ１に、年月表示。Ｅ６行右に原価コード、Ａ７列以降に工事ＮＯを表示 Ｆ１に６月を表示したら、テーブルの金額を取得したいのですが テーブルの例からしますと ＿＿＿＿＿＿＿＿6月 ＿＿＿＿＿＿＿ 5412＿＿ 5431＿＿ 5441 2002＿＿＿＿＿134071 2003＿＿＿＿＿＿15854＿＿88000 2019＿＿＿＿＿＿2300 2111＿＿＿＿＿＿12055 1＿＿＿＿＿＿＿＿27373＿＿17615 3＿＿＿＿＿＿＿＿8020 4＿＿＿＿＿＿＿346178＿＿41104＿＿32000
上記の形でシートに貼り付けるです。
貼り付ける関数を作りました =IF(ISNA(INDEX(工事ＰＴ!$C$7:$BB$60000,MATCH($A7,工事ＰＴ$B$7:$B$60000,0),MATCH ($F$1,工事ＰＴ!$C$5:$BB$5,0)*MATCH(E$6,工事ＰＴ!$C$6:$BB$6,0))),0,INDEX(工事ＰＴ!$C$7:$BB60000,MATCH($A7,工事ＰＴ!$B$7:$B$60000,0),MATCH($F$1,工事ＰＴ! $C$5:$BB$5,0)*MATCH(E$6,工事ＰＴ!$C$6:$BB$6,0))) このコードですと、６月はうまくいくのですが ７月の場合、エラー表示となります 年月表示をクリックして、６月のチエックをはずして７月のみチエックして表示したらうまくいきます 上記処理して、表示するしかないのでしょうか よろしくお願いします
元データはどの様に成っていますか？ 上で書いて居られるピボットテーブルが出来るような 【元データ】を載せて下さい。
また、6月の希望表示結果は書いていただいているので分かりますが 7月はどの様に成れば良いのですか？
＞Ｆ１に６月を表示したら、テーブルの金額を取得したいのですが と言うのは ＞Ｅ６行右に原価コード、Ａ７列以降に工事ＮＯを表示 この部分も、ピボットテーブルを参照する と言う事ですか？
＞貼り付ける関数を作りました これはどこへ入れる関数ですか？
上の表も、どこがどのセルに対応するのか、よく分かりません。
集計 [A] ・・・・ [E] [F] [G] [1] 6月 ： [5] 原価コード [6] 工事NO 5412 5431 5441 [7] 2002 134,071 [8] 2003 15,854 88,000 [9] 2019 2,300 [10] 2111 12,055 [11] 1 27,373 17,615 [12] 3 8,020 [13] 4 346,178 41,104 32,000 [14] こんな感じで、どうなっているのか書いてもらえませんか？
(HANA)
(miyama) HANA様 ありがとうございます 元データは AからAC列までで、仕訳形式で、テーブルに使う金額等は下記の形です ・・ [Ｆ]・・・・ [Ｌ]・・・[ＡＢ] [ＡＣ] [2] 工事NO 借方金額 年月表示 原価コード [3] 2002 2,500 6月 5441 [4] 1 134,071 6月 5431 [5] 2003 15,854 6月 5441 [6] 2019 2,300 6月 5412 [7] 2111 12,055 6月 5467 [8] 1 27,373 6月 5461 [9] 3 8,020 7月 5431 [10] 4 346,178 7月 5412 [11] 以降続く >また、6月の希望表示結果は書いていただいているので分かりますが >7月はどの様に成れば良いのですか？ シート集計のＦ１に各月を表示して、表示するセルは同じ場所です。
＞Ｅ６行右に原価コード、Ａ７列以降に工事ＮＯを表示 この部分も、ピボットテーブルを参照する と言う事ですか？ はい
貼り付ける関数を作りました >これはどこへ入れる関数ですか？ シート集計のＥ７からＱ７の列までで 行はＡ列の工事ＮＯの最終行までです
シート集計の形ですが [A] ・・・・ [E] [F] [G] [1] 6月 ： [5] [6] 5412 5431 5441 工事原価コードがＱ列までです [7] 2002 134,071 [8] 2003 15,854 88,000 [9] 2019 2,300 [10] 2111 12,055 [11] 1 27,373 17,615 [12] 3 8,020 [13] 4 346,178 41,104 32,000 [14]
以降続く よろしくお願いします
載せておられるデータでピボットテーブルを作成
フィールドの設定で「データのないアイテムを表示する(S)」にチェックを入れると
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N]
[1]
[2]
[3]
[4] 合計 / 借方金額 年月表示 原価コード
[5] 6月 6月 合計 7月 7月 合計
[6] 工事NO 5412 5431 5441 5461 5467 5412 5431 5441 5461 5467
[7] 2002 2,500 2,500
[8] 2003 15,854 15,854
[9] 2019 2,300 2,300
[10] 2111 12,055 12,055
[11] 1 134,071 27,373 161,444
[12] 3 8,020 8,020
[13] 4 346,178 346,178
この様なピボットテーブルが出来ます。
集計シートで F1に「6月」を入れた場合 [A] ・・・・ [E] [F] [G] [H] [I] [1] 6月 ： [5] [6] 工事NO 5412 5431 5441 5461 5467 ←原価コード [7] 2002 2,500 [8] 2003 15,854 [9] 2019 2,300 [10] 2111 12,055 [11] 1 134,071 27,373 [12] 3 [13] 4
F1に「7月」を入れた場合 [A] ・・・・ [E] [F] [G] [H] [I] [1] 7月 ： [5] [6] 工事NO 5412 5431 5441 5461 5467 ←原価コード [7] 2002 [8] 2003 [9] 2019 [10] 2111 [11] 1 [12] 3 8,020 [13] 4 346,178
と成れば良いのですか？ データが無くても項目名（工事NO,原価コード）は表示されていれば良い。 上のピボットテーブルの月の範囲を写し取った感じ。
それとも
F1に「6月」を入れた場合 [A] ・・・・ [E] [F] [G] [H] [I] [1] 6月 ： [5] [6] 工事NO 5412 5431 5441 5461 5467 ←原価コード [7] 2002 2,500 [8] 2003 15,854 [9] 2019 2,300 [10] 2111 12,055 [11] 1 134,071 27,373 [12] [13]
F1に「7月」を入れた場合 [A] ・・・・ [E] [F] [G] [H] [I] [1] 7月 ： [5] [6] 工事NO 5412 5431 ←原価コード [7] 3 8,020 [8] 4 346,178 [9] [10] [11] [12] [13]
としたいのですか？ データが無い項目は表示しない。
(HANA)
(miyama) HANA様 ありがとうございます
>データが無くても項目名（工事NO,原価コード）は表示されていれば良い。 >上のピボットテーブルの月の範囲を写し取った感じ。 はい そのようにしたいです
では、ピボットテーブルの方で ＞フィールドの設定で「データのないアイテムを表示する(S)」にチェックを入れると の設定をして下さい。 原価コードの入っているセルを選択して（例えば、C6セル） 右クリック→フィールドの設定(N) を開くと 下の方に「データのないアイテムを表示する(S)」がありますので チェックを入れる。
それぞれの項目名は、最初の月のデータを参照すれば良いので A7に（工事NO） =IF(工事ＰＴ!B7="","",工事ＰＴ!B7) として、必要行フィルドラッグ
E6に（原価コード） =IF(工事ＰＴ!C6="","",工事ＰＴ!C6) として、Q列までフィルドラッグ
E7に（集計結果を表示する部分） =OFFSET(工事ＰＴ!$C$7,ROW(A1)-1,COLUMN(A1)+MATCH($F$1,工事ＰＴ!$5:$5,0)-4) 表示形式を「#,##0;-#,##0;」にして 必要範囲にフィルドラッグ
して下さい。
(HANA)
E7に入れる式は、INDEX関数を使って =INDEX(工事ＰＴ!7:7,COLUMN(A1)+MATCH($F$1,工事ＰＴ!$5:$5,0)-1) の様にしても良いかもしれません。
OFFSET関数を使用した場合でも =OFFSET(工事ＰＴ!$B$6,ROW(A1),COLUMN(A1)+MATCH($F$1,工事ＰＴ!$5:$5,0)-3) こちらの方が分かりやすい？
(HANA)
(miyama) HANA 様 ありがとうございました 完成しました 丁寧な御指導、感謝します
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.