質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.59%

エクセルの縦棒グラフについて

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 634

izuki_y

score 64

こんにちは

タイトルの件で質問があります。

環境はWindows7 64bit / Excel2010 です。

以下のグラフを見てください。

イメージ説明
注:実在する車の名前を使わせて頂いただけでポイントと実際の車に何の関係もありません。

これを元に以下の様なグラフを作りたいです。
イメージ説明
棒は「車のタイプの平均値」、項目には「ポイント平均の一番多い車種とポイント」、「トヨタの車種とポイント」を付けたいです。

一応元のグラフから以下のグラフを抽出してみましたが、新しく項目を追加する事が出来ません。
イメージ説明

仮に項目を追加出来たとしても各タイプから、車種の大きいポイントと、トヨタ車種を探すとなると気が遠くなる作業になります(例題だと3タイプですけど実際は物凄い沢山のタイプがあって、更にそこに大量の車種が並びます)

これを表の作り方などを変えて自動的に出来る方法は無いでしょうか?
よろしくお願いいたします。


seastar3様のコメントを参考に修正

まずは元の表を手動で以下の表に変更(ここをピポッドテーブルで、簡単に作成出来るのかな?)

イメージ説明

車種先頭行番号(F2)の式:
=MATCH(B2,$B$2:$B$9,0)+1

車両末尾番号(G2)の式:
=F2+COUNTIF($B$2:$B$9,B2)-1

イメージ説明

がそれぞれ取得できる

車両毎最大値ポイント(H2)の式:
=MAX(OFFSET(INDIRECT("E"&FIXED(F2,0)),0,0,G2-F2+1,1))

INDIRECTで基点を取って、OFFSETで範囲確定する(オフセットの幅と高さは基点のセルを含める事を忘れない事)

車両毎最大車両名(I)の式:
=INDEX($D$2:$E$9,MATCH(H2,$E$2:$E$9,0),1)

VLOOKUPを使う予定だったが、車両毎平均の前に車種があるためVLOOKUPを使えなかったので、
INDEXとMATCHの複合で実現。

グラフは、車種毎平均、車種毎最大値ポイント、トヨタの車両ポイントを棒グラフで取れば大丈夫のはず(ここはあとでちゃんとまとめる)

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

0

各車のデータを集計する前の表のひな形を作ってみました。これをもとにピボットテーブルをつくりグラフにします。
イメージ説明
個々の行で最大車のポイントと車名、車種ごとのトヨタ車のポイントと車名の文字列を持っておいてピボットテーブルにも並べて、グラフの値表示に使うことになります。ただ、j列以降はボランティアの域を超えるので、ご自身で色々と工夫してみて下さい。

列名:
データ番号,車種,メーカー,車名,ポイント,車種毎平均,車種先頭行番号,車種末尾行番号,車種毎最大車ポイント,車種毎トヨタ車情報

F2の式:
=AVERAGEIF($B$2:$B$9,B2,$E$2:$E$9)

G2の式:
=MATCH(B2,$B$2:$B$9,)+1

H2の式:
=G2+COUNTIF($B$2:$B$9,B2)-1

I2の式:
=MAX(OFFSET(INDIRECT("E"&FIXED(G2,0)),0,0,H2,1))
データ番号 車種 メーカー 車名 ポイント 車種毎平均 車種先頭行番号 車種末尾行番号 車種毎最大車ポイント 車種毎トヨタ車情報
1 セダン     日産     シルフィ     8.4 7.85 2 3 9.1
2 セダン     トヨタ マークX     7.3 7.85 2 3 9.1
3 SUW     BMW 3シリーズ 9.1 8.43 4 6 9.1
4 SUW     トヨタ ハリアー     7.1 8.43 4 6 9.1
5 SUW     日産 エクストレイル 9.1 8.43 4 6 9.1
6 コンパクト トヨタ ヴィッツ     7.9 8.30 7 9 8.7
7 コンパクト ホンダ フィット     8.7 8.30 7 9 8.7
8 コンパクト BMW MINI     8.3 8.30 7 9 8.7

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/10/25 23:46

    前回に続き今回もご回答を頂きありがとうござます。
    範囲の選択にはOFFSETを使う事と基点にはINDIRECTを使うことを教えて頂きありがとうございました。

    トヨタの車両だけの課題はちょっと残ってしまいました(seastar3様の様にメーカーセルを分ければVLOOKUPかMATCH+INDEXでどうにかなりそうですが、実際は一つのセルになっていたもので…w)

    後はピボットテーブルの使い方を理解する必要があります。
    ちなみに自分が出した表からseastar3様が作って頂いた雛形をピボットテーブルで作れるものなのでしょうか?

    色々と質問ばかりで大変申し訳ありません。
    フィードバックを行い今後の作業に役に立たせていきたいと思います。
    本当にありがとうございます。

    キャンセル

  • 2018/10/26 06:38 編集

    メーカーと車種はスペースをfind関数に入れて文字数を測り、mid関数で分けて下さい。元の表は横の表なので、横に作って縦横入れ替えで新たなワークシートにコピーすればよろしいでしょう。最大値の車名を選び出すノウハウも考えてみてはどうでしょうか。match関数でポイントが最大値の行を見つけてindex関数で車名を拾うとか。ただ同ポイントのときどうするのか決める必要があるでしょう。同車種の複数のトヨタ車の車名をまとめる方法とかもググってみましょう。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.59%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る