初めて質問箱に投稿させていただきます。よろしくご指導下さい。
※ シート(1)にデータ、シート(2)に結果を求める。
※ シート(1)のA列に大分類(8項目)、B列に中分類(35項目)、C列に小分類(15項目)、D列に金額 を入力します。 これを2行から99行に色々な組み合わせのデ-タを入力したシートを基にして
※ シート(2)のB列2行~D列2行に、シート(1)のA列~C列のデータの中で組み合わせの1番多い組 み合わせを表示し、シート(2)のE列2行にその組み合わせの金額の合計額を表示する。
※ シート(2)のB列3行~E列3行以降の行に、シート(1)の組み合わせの2番目以降の順に表示する という具合に、すべての組み合わせとその計金額を表示する。
※ このような求め方をするためには、どのような数式をつくればよいのでしょうか。ご指導ください。
シート(1)の例
A列 B列 C列 D列
1行 大分類 中分類 小分類 金 額
2行 3 B あ 1,000
3行 2 C あ 500
4行 3 B い 500
5行 1 A う 1,500
6行 2 C あ 1,000
7行 3 A う 1,500
8行 3 B あ 800
9行 2 C あ 600
10行 1 A う 700
11行 3 A あ 200
12行 3 B あ 1,000
13行 5 A い 100
14行 2 C あ 200
15行 3 A う 700
~
99行 計 10,300
シート(2)の例 (上記シート(1)の例の集計結果をシート(2)の例のように表示したい)
A列 B列 C列 D列 E列
1行 回 数 大分類 中分類 小分類 金 額
2行 4 2 C あ 2,300
3行 3 3 B あ 2,800
4行 2 1 A う 2,200
5行 2 3 A う 2,200
6行 1 3 B い 500
7行 1 3 A あ 200
8行 1 5 A い 100
9行
10行
~
30行 計 10,300
No.6ベストアンサー
- 回答日時:
こんにちは
まず、シート(1)にキーを作成します。
 ̄ ̄ ̄ ̄ ̄
手順1
E2に =A2&B2&C2 と入力します。
手順2
F2に
=IF(OR(E2="",COUNTIF(E$2:E2,E2)<>1),"",COUNTIF(E:E,E2)+ROW()/1000)
と入力します。
手順3
E2からF2を選択してコピーして、E3からE98を選択して、ペーストします。
ここまでが、キーを作成するための手順です。
次に、シート(2)に集計するための式を入力します。
 ̄ ̄ ̄ ̄ ̄
手順4
A列の前に1列挿入して、A2からA29に1から28の連番を入力します。
そちらの例では、1列目(A列)から回数となっていますが、その前に1列挿入して、
A列に行番号を入れます。 こうしておいた方が、式がすっきりします。
また、1行目にタイトル行となっていますが、タイトルの上に行挿入をした場合に
対応できるように、1番目の数値、2番目の数値と式ではっきりと認識できるように
しておきます。
手順5
B2に、
=IF(A2>COUNT(Sheet1!$F$2:$F$98),"",LARGE(Sheet1!$F:$F,A2))
と入力します。
手順6
C2に、
=IF(B2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),1))
と入力します。
手順7
D2に、
=IF(C2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),2))
と入力します。
手順8
E2に、
=IF(D2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),3))
と入力します。
手順9
F2に、
=IF(B2="","",SUMIF(Sheet1!$E:$E,C2&D2&E2,Sheet1!$D:$D))
と入力します。
手順10
B2からF2を選択してコピーして、B3からB29を選択してペーストします。
これで、基本的な形ができます。
ただし、そちらの例と異なる点が2点ほどあります。
・回数に小数点が付いている。
・同順位の場合、そちらの表示順序とことなる。
回数は、集計の直接的なキーではありませんが、並びかえを使用せずに、回数の大きい順
に表示をさせるには、集計というより、データを読み込む順番のキーとなります。
しかし、同順位が存在すると、正確な読み込みができませんので、行番号を1000で割って
強制的に順位付けを行います。
このため、回数には、小数点が付いています。 この小数点はINT関数で整数化できますが
この数値を使って、大分類、中分類、小分類を読み込んでいます。
この際、
LARGE(Sheet1!$F:$F,A2)
という、コードを検索値として使用すれば、回数を整数化しておくことも可能ですが、回数は
表示形式で整数のみ見せる、または加工時に整数化すれば、事足りるので、わざわざ、式を複雑
にする必要はありません。
ですから、”,”ボタンで、表示形式を整数に変更すればよいと思います。
また、並び順ですが、これは金額の昇順、降順などで並ぶのではなく、データが存在した行番号
の昇順か、降順かの違いなのでデータの位置によっては、必ずしも例の通りにはなりません。
金額の昇順、降順にしたいのであれば、関数に頼らずに並べ替え機能を使うのが本来の使い方
だと思います。
それから、別の方法として、ピボットテーブルを使用することで簡単に表が作成できます。
シート(1)のキー作成まで行い、E1に”分類”、F1に”回数”などとタイトルをつけて
おきます。
1) A1からF98を範囲選択して、メニューの『挿入』から『ピボットテーブル』を選択します。
2) 新規ワークシートが選択されていると思いますのでそのままOKを押します。
3) ピボットテーブルのフィールドリストにある回数を、値の欄にドラックして持っていきます。
もし、ピボットテーブルのフィールドリストが消えてしまったら、シートの行ラベルや
値と書かれたセルを選択すると表示されます。
それでも表示されない場合には、そこで右クリックして一番下の
フィールドリストを表示する
を選択します。
4) 回数の集計方法が、『データの個数 / 回数』になっていれば、そのセルの上で右クリック
して、データの集計方法から合計を選択します。
5) 金額を回数と同様に値の欄に、回数の下へドラックします。
『データの個数 / 回数』になっていれば合計に変更します。
6) 分類を行ラベルへドラックします。
7) 行ラベルの▼をクリックして空白の場所の左隣のチェックをはずします。
8) シートの『合計 / 回数』と書かれたセルの、1行下を、右クリックして
並べ替え → その他の並べ替えオプション を選択します。
9) 並べ替えオプション 降順 並べ替えの方向 行単位 を選択してOKを押します。
10)B列とC列の全体を選択してセルの書式を、"#,###"で整数化します。
多少、レイアウトは異なりますが、似たようなことはできます。
関数以外にもやり方はあるので、覚えておくとよいと思います。
長くなりましたが、ご参考まで
お礼が遅くなりすみません。セクセル初心者に分かり易くご説明いただきましてありがとうございました。お陰で求めていた書式を作ることができました。
No.5
- 回答日時:
シート2の例えばH2セルに次の式を入力してみてください。
=MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)
答えは3となるでしょうか?
I2セルには次の式を入力してみてください。
=INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0))
答えは 2Cあ と表示されますか?
ご使用のエクセルのバージョンはエクセル2010でしょうか?
何回もお手数をかけました。エクセルのバージョンが2003となっていましたので、ご指導通りの作業ができなかったと思います。すみませんでした。ありがとうございました。
No.4
- 回答日時:
回答No2,3です。
MATCH関数ではしばしば小数点を持つ数値が対象になっている場合にはエラーが起こることがあるといわれます。
こちらでの試験では問題がなくともそちらではできないということですので、小数点を扱うことの無いように多少式を変えることにします。
まずシート1の作業列ではE2セルには次の式を入力して下方にオートフィルドラッグします。回答2と同じです。
=A2&B2&C2
F2セルには次の式を入力して下方にオートフィルドラッグします。回答2と違っています。
=IF(E2="","",IF(COUNTIF(E$2:E2,E2)=1,ROUND(COUNTIF(E:E,E2)*100000+10000/ROW(A1),0),""))
次にシート2のA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))/100000),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))
今度は成功すると思いますね。
No.3
- 回答日時:
回答No2です。
こちらの示した通りに試験されているのでしょうか?
シート2のA2セルには前にも示しましたが次の式を入力して右にオートフィルドラッグしたのちに下方にもオートフィルドラッグすることで問題なく表示されるはずですが。
=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))
この式をシート2のA2セルにコピーして貼り付けても何らの問題はないはずです。
シート1の作業列ではデータが表示されているのでしょうね。
具体的にどこがどのようになったかを示していただけませんか?
すみません。初心者なものですから。
IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),"")))) の式をシート2A2セルに貼り付け、最初のIFの前に=を入力すると、後半のMATCH(LARGE(Sheet1!$F:$F,ROW(A1)),のところの式のROWにエラー箇所表示がでます。
宜しくお願いします。
No.2
- 回答日時:
作業列を作って対応します。
シート1ではE2セルに次の式を入力してE98セルまでオートフィルドラッグします。
=A2&B2&C2
F2セルには次の式を入力してF98セルまでオートフィルドラッグします。
=IF(COUNTIF(E$2:E4,E2)=1,ROUNDDOWN(COUNTIF(E:E,E2)+0.5/ROW(A1),5),"")
そこでお求めの表ですがA1セルからE1セルまでにお示しの項目名が有るとして、A2セルには次の式を入力してE2セルまで横にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))
ありがとうございました。早速入力し実行しようとしましたが、シート2の次の式で実行出来ませんでした。いかが対処したらよろしいでしょうか? なお、式は頂いたものをコピーにて貼り付けましたが?
IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))
宜しくお願いします。
No.1
- 回答日時:
一例です。
1)シート(1)のF2に=A2&B2&C2を入力、下方向にコピー
2)シート(2)B1を選択→データ→フィルタ→フィルタオプションの設定、
「指定した範囲」を選択、「リスト範囲欄」にシート(1)!A:C、抽出範囲欄にB1、「重複するレコードは無視する」を選択→OK
3)A1に「回数」入力、A2に=COUNTIF(シート(1)!F:F,B2&C2&D2)を入力、下方向にコピー
4)シート(2)を回数列で降順に並び替え
5)E1に「金額」を入力、E2に=SUMIF(シート(1)!F:F,B2&C2&D2,シート(1)!D:D)を入力下方向にコピー
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Visual Basic(VBA) VBAで質問があります 1 2022/10/19 10:32
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Visual Basic(VBA) vbaについて 主に以下のような設定をしたいです。 Aブックの表の行数が20未満だったら Bブックの 1 2023/06/08 23:40
- Visual Basic(VBA) VBAで、シート間の転記するコードをFOR~NEXTで教えてください。 9 2023/04/30 20:04
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ゴルフコンペの会費に係る消費...
-
小口現金残高が合わせのアドバ...
-
「インボイス制度」ゴルフコン...
-
タイミーでバイトを雇った際の...
-
「インボイス」ゴルフコンペの...
-
帳簿についてです。 塗装業の塗...
-
""未上場株式会社""の財務情報...
-
配達の仕事をしている個人事業...
-
個人工務店の1年間収益 以下全...
-
印紙について
-
Windows11なのですがPDFにパス...
-
銀行振込み明細書が領収書の代...
-
30万円未満の「少額減価償却資...
-
ゴルフクラブやギターは経費に...
-
工業簿記の質問です
-
給与の未払、翌月2ヶ月分振込と...
-
売り上げを1年ズラして計上すれ...
-
定期代の支給は利益0円の実費支...
-
簿記2級で実務経験ない場合の就...
-
税務調査実施日の前に申告すれ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
帳簿についてです。 塗装業の塗...
-
ゴルフコンペの会費に係る消費...
-
タイミーでバイトを雇った際の...
-
インボイス制度 不動産売買の際...
-
Windows11なのですがPDFにパス...
-
「インボイス制度」ゴルフコン...
-
会社主催のイベント費用の勘定...
-
パソコンが壊れ交換となった場...
-
振替について教えて下さい 来期...
-
給与の未払、翌月2ヶ月分振込と...
-
【確定申告】車譲渡の経理処理...
-
経理の勉強をしてます。 今、読...
-
「イオンのセルフレジの領収書」...
-
法人ですが、会計ソフトに入力...
-
賞与の計算方法がわからない
-
節税したい。経理事務所への依...
-
キャンセルされた新幹線の領収...
-
原価61万円の作業費用に粗利50...
-
「インボイス」ゴルフコンペの...
-
【数学・粗利率の計算】A社がB...
おすすめ情報