エクセル関数について教えてください!!
エクセルファイルに以下のようなデータがあります。
A列 B列 C列
あ A10 100
あ A10 110
あ B10 120
い C10 100
い D10 100
う E10 100
あ B10 120
い C10 100
A列:販売店
B列:コード
C列:売り上げ金額
(合計8000件程度)
販売店毎での売り上げ金額を求めたいのですが、B列のコードが重複しています。
販売店+重複コードは1つに纏めての金額を求める関数を教えていただけますでしょうか。
イメージ
あ A10 210
あ B10 240
い C10 200
い D10 100
う E10 100
よろしくお願いしますm(_ _)m
No.2ベストアンサー
- 回答日時:
こんばんは!
一例です。
Sheet1のデータをSheet2に集計するようにしてみました。
↓の画像で説明すると
Sheet1に作業用の列を2列設けています。
作業列D2セルは
=A2&B2
E2セルは
=IF(COUNTIF($D$2:D2,D2)=1,ROW(),"")
として、D2・E2セルを範囲指定し、E2セルのフィルハンドルでオートフィルで下へずぃ~~~!っとコピーします。
そして、Sheet2のA2セルは
=IF(COUNT(Sheet1!$E:$E)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$E:$E,ROW(A1))))
として隣のB2セルまでコピー
C2セルに
=IF(COUNTBLANK(A2:B2),"",SUMIF(Sheet1!D:D,A2&B2,Sheet1!C:C))
という数式をいれ、最後にA2~C2セルを範囲指定し、C2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
以上、参考になればよいのですが
他に良い方法があれば読み流してくださいね。m(__)m
No.7
- 回答日時:
販売店ごとでコードごとに関数で並べ、しかもそれらの並びはいつも一定したものでない場合には意外と面倒ですね。
次のようにしてはどうでしょう。すべてのデータはシート1のA,B,C列の2行目から下方に有るとしてE列およびF列は作業列とします。
E2セルには次の式を入力します。
=A2&B2
F2セルには次の式を入力します。
=IF(A2="","",IF(COUNTIF(E$2:E2,E2)=1,IF(COUNTIF(A$2:A2,A2)=1,INT(MAX(F$1:F1))+1,INDEX(F$1:F1,MATCH(A2,A:A,0))+(COUNTIF(F$1:F1,">="&INDEX(F$1:F1,MATCH(A2,A:A,0)))-COUNTIF(F$1:F1,">="&INDEX(F$1:F1,MATCH(A2,A:A,0))+1))/1000),""))
E2およびF2セルを選択してそれらの式を下方にオートフィルドラッグします。
お求めの表を別のシートに作るとしてA1セルからC1セルにはシート1と同じ項目名を入力します。
A2セルには次の式を入力してC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)<=2,INDEX(Sheet1!$A:$B,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)),IF(COLUMN(A1)=3,SUMIF(Sheet1!$E:$E,$A2&$B2,Sheet1!$C:$C),"")))
これでシート1に新たにデータが入力されても即座に表が対応します。
No.5
- 回答日時:
参考までに
私の提示した数式は基本的に配列数式ですので表示データ数が少ない場合は簡便な方法ですが、数式入力数が多くなると再計算に時間がかかるなど、実用性に問題があります。
この場合は、「ツール」「オプション」の計算方法タブで計算方法を「手動」にしておき、データを確認したい時のみF9キーを押して再計算させるようにします。
一方、補助列にCOUNTIF関数などの数式を多数の行にデータ数分だけ入力する場合は、メモリーの消費量が多くなり、エクセルがフリーズするなどの問題が発生します。
このような場合は、数式で処理するのではなく、ピボットテーブルで集計されることをお勧めします。
たとえば「データ」「ピボットテーブルと・・・」からピボットテーブルウィザードを起動し、行フィールドにA列とB列の項目名、データフィールドに売上金額をドラッグしてみてください。
No.4
- 回答日時:
以下のような数式で重複のないA列とB列のデータとその集計値を表示することができます。
E2セルに以下の式を入力し、右方向に1つ下方向に適当数オートフィルします(数式だけで処理すると表示するデータが多い場合は動きが重くなりますので運用に工夫が必要です)。
=INDEX(A:A,SMALL(INDEX((MATCH($A$2:$A$8000&$B$2:$B$8000,$A$2:$A$8000&$B$2:$B$8000,)<>ROW($A$2:$A$8000)-1)*1000+ROW($A$2:$A$8000),),ROW(A1)))&""
G2セルには、エクセル2007をご使用なら、計算負荷が少ないSUMIFS関数を使います(2003以前のバージョンならSUMPRODUCT関数を使うことになります)。
重複のないA列とB列の組み合わせを、計算負荷が大きい数式を使用せずに表示させるなら、フィルタオプションの設定を利用します。
エクセル2007ならデータタブの「詳細設定」で(2003なら「データ」「フィルタ」「フィルタオプションの設定」)、リスト範囲にA列とB列の範囲を指定し「重複するレコードは無視する」にチェックを入れてOKし、このデータを別シートなどに貼り付けてSUMIFS関数などで集計します。
#エクセルのバージョンによって使用できる関数や機能だけでなく、操作法も違いますので、質問の際には必ずバージョンを明記するようにしましょう。
MackyNo1さん回答ありがとうございます。
そうですよね…基本的な記述が漏れておりました。バージョンは2003を使用しています。
失礼しました&助言ありがとうございます!!m(_"_)m
No.3
- 回答日時:
> 再問なのですが…コードが不規則でE列に入力できない場合はどのようにしたら良いでしょうか?
以下のページを参考にして重複データを整理してください
http://editors-blog.hakenjob.com/archives/500402 …
A列B列をリスト範囲にしてD列E列を抽出範囲にすると一括でデータを入力できます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) A列が指定数値以上になったらD列の計算式を変更 3 2022/07/11 11:03
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) EXCEL 行内のデータを2行に分けて、表を作り直したいのです。教えてください。 5 2023/06/25 14:00
- Excel(エクセル) エクセルの式を教えて下さい 2 2022/04/10 13:16
- Excel(エクセル) ある範囲のセルに何を入力すると、別のセルに○を表示させる 3 2022/04/05 15:51
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- その他(Microsoft Office) Excelで該当しない項目(#N/Aの商品名)を簡単に表示・抽出させる方法についてです 1 2022/08/25 22:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【Microsoft Forms】回答を削除...
-
Office 2021 Professional Plus...
-
マイクロソフト 一時使用コード...
-
【スプレッドシート】指定の日...
-
VLOOKUP関数について
-
英数字のみ全角から半角に変換
-
Microsoft Formsで「応答」から...
-
エクセルでXLOOKUP関数...
-
エクセルで英文字に入れた下線...
-
1つのPCに「Excel 2010」「Exc...
-
大学のレポート A4で1枚レポー...
-
マクロ自動コピペ 貼り付ける場...
-
Outlookでの時間指定送信機能に...
-
会社におけるOfficeライセンス...
-
Office Professional Academic ...
-
vb.net オブジェクト指向につい...
-
マイクロソフト オフィスについて
-
エクセルで例えば、関数を使っ...
-
Microsoft Officeを2台目のPCに...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報