[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『検索結果表示について』（マツ）
高速料金リストを利用して、検索結果を１クリックで表示させたいのですが、 マクロが苦手で、アドバイスをいただきたく何卒よろしくお願いします。
まずは、下記のとおり、ピボットテーブルにて集計した高速料金リストがあります。 【リスト】
【A】 【B】 【C】 【D】 1 高速入口 2 高速出口 3 4 合計 / 点検時料金(平日) 5 ■事業所No ■高速入口 ■高速出口 ■高速料金税抜(休日) 6 1000 八草 猿投 285 7 加納 285 8 枝下 380 9 西広瀬 380 10 力石 380 11 本郷 阿久比 1,333 12 伊勢 2,571 13 一宮東 667 14 蟹江 523 15 亀山 1,380 16 久居 1,714 17 玉城 2,380
【B1】に高速入口名、【B2】に出口名をINPUTし、【D2】セルに金額表示をさせたいのです。 ボタンの表示をさせマクロの登録をしたいと思うのですが、可能でしょうか?
(まつ）
D2セルへ =SUMPRODUCT((IF(B6:B17="",C5:C16,B6:B17)=B1)*(D6:D17=B2)*(E6:E17)) とし、Ctrl + Shift + Enterで式を確定すれば金額表示されると思いますが、マクロのほうが良いですか？
(Jera)
Jeraさん
コメントありがとうございます。 早速関数を貼り付けてみましたが、#VALUEとなってしまいました･･･。 関数についても解読できず、詳しく教えてください。
(まつ)
ああああ、ごめんなさい間違えて組んでしまいました。 大変申し訳ありませんが本日は考え直す時間がありませんので、他の回答者の返答をお待ち下さい。
(Jera)
表の見方を教えて。 本郷から入って、加納で出るといくらなのかな？ 八草から入って亀山まで行くと？
ぶらっと立ち寄り
猿投 加納 枝下 西広瀬 力石 本郷 伊勢 一宮東 蟹江 亀山 久居 玉城
猿投
加納
枝下
西広瀬
力石
本郷
伊勢
一宮東
蟹江
亀山
久居
玉城
リストをこのような形にできませんか。鉄道の運賃表など普通に使われています。(NB)
載せて居られる表の感じだと、 「■高速料金税抜(休日)」 の列が行のフィールドに有る様に思います。
これをデータアイテムに来る様にすれば GETPIVOTDATA関数が使えるように成りますが 変更は出来ないのでしょうか？
例えば、 行のフィールドに「■事業所No」「■高速入口」「■高速出口」 データアイテムに「■高速料金税抜(休日)」 を入れます。 ピボットテーブルは、A4セルから配置
D2セルをアクティブにして「=」を入力後
D6セル（事業所No 1000 高速入口 八草 高速出口 猿投 の合計セル）をクリックすると
=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■事業所No",1000,"■高速入口","八草","■高速出口","猿投")
の式が出来るので、一旦 Enter で確定。
数式内の、「八草」「猿投」部分をB1,B2セルをそれぞれ参照する様に変更すると
=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■事業所No",1000,"■高速入口",B1,"■高速出口",B2)
B1,B2セルのデータに連動して、D2セルの値が参照される様に成ります。
また、ピボットテーブルの元のデータはどの様に成っていて データ量はどの程度有るのでしょう？
場合によっては、そちらから探す方が簡単かもしれません。
(HANA)
(HANA)さん コメントありがとうございます。 早速やってみました。 結果は…#REF!となってしまいました。 HANAさんが書き込んで下さったとおりに操作をしたら >数式内の、「八草」「猿投」部分をB1,B2セルをそれぞれ参照する様に変更すると エラーとなってしまうのです。 B1、B2に変更する前は確かに金額が出ていました。
また、元のデータは下記のとおりです。
A B C D E F G H I J K 1 ■事業所No 事業所名 交通料区分 パターンNo ■高速入口 ■高速出口 高速料金税抜(平日) ■高速料金税抜(休日) ■点検50km以上 点検時料金(平日) ■点検時料金(休日) 2 1000 本社 20 1 八草 猿投 285 285 0 0 3 1000 本社 20 2 八草 加納 285 285 0 0 4 1000 本社 20 3 八草 西広瀬 380 380 0 0 5 1000 本社 20 4 八草 枝下 380 380 0 0 6 1000 本社 20 5 本郷 勝川 333 333 0 0 7 1000 本社 20 6 本郷 清州東 333 333 0 0 8 1000 本社 20 7 本郷 大治南 333 333 0 0 9 1000 本社 20 8 本郷 楠 333 333 0 0 10 1000 本社 20 9 名古屋 三好 285 285 0 0 11 1000 本社 20 10 名古屋 春日井 285 285 0 0 12 1000 本社 20 11 名古屋 勝川 333 333 0 0 13 1000 本社 20 12 名古屋 豊田 380 380 0 0 14 1000 本社 20 13 八草 力石 380 380 0 0 15 1000 本社 20 14 本郷 小牧南 667 667 0 0 16 1000 本社 20 15 本郷 山田東 333 333 0 0 17 1000 本社 30 1 本郷 阿久比 1333 1333 0 0 18 1000 本社 30 2 本郷 蟹江 523 523 0 0 19 1000 本社 30 3 本郷 山田東 333 333 0 0
文字と数字がうまく表示させられませんがA1=事業場NO B1=事業場名 C1=交通料区分と続いていきます。 データ量については全部で308行あります。
こちらのほうが速いのでしょうか？
(マツ)
＞結果は…#REF!となってしまいました。 B1,B2に入力した物と 元データの文字が一致していないのでは？ 元データの方は、後ろにスペースが入っている 等
確実に一緒に成る様に、元データのセルをコピーして貼り付けてみるとどうでしょう？
＞データ量については全部で308行あります。 データ量が固定と言う事は、「重複は無い」と思えば良いですか？
ちなみに、エクセルのバージョンは何でしょう？
(HANA)
(HANA)さん
もう一度やってみました･･･。 ご指摘どおり、元データの文字が一致していないのかと思い、元データのスペースを 編集(E)-置換(E)ですべてのスペースをなくして、ピボットテーブルのデータを更新してみましたが、 やはり#REFとなってしまいました。 また、元データのセルをコピーして貼り付けもしてみましたが、同じ結果です。 B1、B2に変更する前は確かに金額が出ているわけですから、あと少し!!って感じです(汗) 何か他に考えられる不具合はありませんか??
>データ量が固定と言う事は、「重複は無い」と思えば良いですか？ そうですね。 高速の入り口･出口の名前は一緒でも、交通量区分が違っていますので、重複はないです。
>ちなみに、エクセルのバージョンは何でしょう？ EXCEL2003です･･･。
関数がうまくいくといいのですが･･･。私が想像していたとおりの処理方法です。
もう少しお付き合いください。よろしくお願いします。
(マツ)
＞何か他に考えられる不具合はありませんか?? う〜ん、分かりませんので。。。 簡単なサンプルデータを作って、それを使って同じ事をし それでは上手く行く事を確認してみて下さい。
その後、データを現在の物に近づけて行きながら試した時 どの時点で#REF!エラーが出るのか、探ってみて下さい。
＞関数がうまくいくといいのですが･･･。私が想像していたとおりの処理方法です。 この検索をする為だけにピボットテーブルを作製されるのですか？ でしたら、使わなくて済めば 手間が減りますね。
＞高速の入り口･出口の名前は一緒でも、交通量区分が違っていますので、重複はないです。 と言う事は、マッチングするためには 事業所区分・交通量区分・入口・出口 の４つの項目の確認が必要ですか？
ご使用のエクセルが2003と言う事なので、SUMPRODUCT関数を使ってみて下さい。 http://www.excel.studio-kazu.jp/lib/e3h/e3h.html ↑ライブラリの「SUMIFとSUMPRODUCT」です。
基本的には =SUMPRODUCT((事業所区分の範囲=事業所区分)*(交通量区分の範囲=交通量区分)*(入口の範囲=入口)*(出口の範囲=出口),金額の範囲) と言う形に成ります。
○○の範囲 の方は 元データの対応する範囲 =の後は 条件を入力したセル番地にして下さい。
例えば、(入口の範囲=入口) の部分は (元データ!E1:E308=B1) と言った感じになって これらの条件を「*」で繋げて、最後に「,」の後に金額の範囲 です。 括弧の数に注意して式を作ってみて下さい。 念のため、条件を書くセルには元データからセルをコピーして下さい。 上手く行くと良いですが。
(HANA)
(HANA)さん
HANAさんはうまくいくのでしょうか? 一度ｻﾝﾌﾟﾙを作ってトライしてみます。
また、ﾏｸﾛという方法は使えませんか? それほどのデータでもないと承知はしていますが･･･。
(マツ)
元のリストでオートフィルタしたほうが早いのでは・・・？ (みやほりん)(-_∂)b
(みやほりん)さん
アドバイスありがとうございます。 オートフィルタでは駄目な理由があるので、なんとか関数及びマクロで結果を求めたいのです。
コメントありがとうございました。
(マツ)
(HANA)さん
できました!! 凡ミスです･･･B1とB2に変更した箇所で、ﾀﾞﾌﾞﾙｸｫｰﾃｰｼｮﾝを削除し忘れていました。 正常に結果が求められました。 大変助かりました。 ありがとうございます。
(マツ)
【A】 【B】 【C】 【D】 1 高速入口 2 高速出口 3 4 合計 / 点検時料金(平日) 5 ■事業所No ■高速入口 ■高速出口 ■高速料金税抜(休日) 6 1000 八草 猿投 285 7 加納 285 8 枝下 380 9 西広瀬 380 10 力石 380 11 本郷 阿久比 1,333 12 伊勢 2,571 13 一宮東 667 14 蟹江 523 15 1000 合計 ****** 16 2000 久居 1,714 17 玉城 2,380
となった場合、
=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■事業所No",1000,"■高速入口",B1,"■高速出口",B2)
だと■事業所Noが2000以降、つまり1000以外の検索をかけるとエラーになってしまいます。
15行目のように合計が出るようなピボットでは上記の関数は利用できないのでしょうか?
(マツ)
＞ﾀﾞﾌﾞﾙｸｫｰﾃｰｼｮﾝを削除し忘れていました。 修正するときは、関数ダイアログを出して修正した方が 簡単だった（間違いに気づきやすかった）かもしれないですね。。。
=GETPIVOTDATA(・・・・・) の式が出来たら、数式バーの左にある[Fx]を押すと ダイアログが出てきます。 設定毎に入力する場所が有るのでどの""がどこに対応するのか 判断が簡単に成ったのかもしれません。
＞1000以外の検索をかけるとエラーになってしまいます。
それは
=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■事業所No",1000,"■高速入口",B1,"■高速出口",B2)
事業所Noが1000の中から探しているからでは。。。？ ＿＿／~~~~
(HANA)
(HANA)さん
早速の回答ありがとうございます。 >正するときは、関数ダイアログを出して修正した方が >簡単だった（間違いに気づきやすかった）かもしれないですね。。。 そうだったかもしれません･･･。 今後気をつけたいと思います。
>=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■事業所No",1000,"■高速入口",B1,"■高速出口",B2)
>事業所Noが1000の中から探しているからでは。。。？
そうゆうことですか。
1000以外すべての事業所NOについても検索できるよう変更するにはどうすればよいのでしょうか?
(マツ)
事業所Noを指定しなくても、データは重複しませんか？ 重複が無いなら、その部分を消してみてはどうでしょう？
=GETPIVOTDATA("■高速料金税抜(休日)",$A$4,"■高速入口",B1,"■高速出口",B2)
↑要らない条件は まるっと消してみる。
(HANA)
事業所NOを指定しないと、データは重複してしまいます。 ので、現状の条件で検索できるようになると助かるのですが･･･。
(マツ)
どちらが表示されれば良いのですか？ 両方？ それとも、両方は同じなのでどちらかが出れば良い？
(HANA)
(HANA)さん
重複しているものについてはどちらか1方が表示されれば問題ないです。 現状ですと、重複していないものについても、事業所NOが違うものは エラーとなってしまいますので、それを改善したいと思っています。
よろしくお願いいたします。
(マツ)
重複が有った場合、どちらでも良いのなら 事業所NOを行のフィールドに入れないピボットテーブル （金額を表示する為だけに使うピボットテーブル）を 一つ作製されてはどうでしょう？
その場合、「合計」にすると全部が合計されてしまうので 「最小値」とか「最大値」とか「平均」とかに変更。
(HANA)
(HANA)さん
コメントありがとうございました。 ご指摘どおり、事業所区分等のくくりをはずし、単に入口出口名の表示と、 金額欄は平均値にし実行しました。 長い時間お付き合いくださいましてありがとうございました。
(マツ)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.