現在エクセルにてデータをまとめております。
生データのシートから抽出シートにデータをまとめたいのですが
どのような関数を使用してまとめたらよいでしょうか?
生データのシートAに
「日付」「売上ランキング」「商品名」があります。
例)
9/5 13 靴A
9/5 10 靴B
9/5 12 靴C
9/5 01 服A
9/5 08 服B
9/5 04 服C
9/6 22 靴A
9/6 17 靴B
9/6 08 靴C
9/6 07 服A
9/6 03 服B
9/6 02 服C
・
・
・
このデータを抽出シートBに抽出したいと考えております。
列に「日付」、行に「商品名」
例)
9/5 9/6 9/7 9/8 9/9 ・・・・・・
靴A 13 22
服A 01 07
靴B 10 17
服B 08 03
靴C 12 08
服C 04 02
・
・
・
「商品名」と「日付」が合致した際に「売上ランキング」を表示するという関数かとは思うのですが
どのような数式になるのかわからず、申し訳ありませんがご教授頂けないでしょうか?
No.2ベストアンサー
- 回答日時:
こんにちは!
一例です。
判りやすいのは↓の画像のように作業用の列を設ける方法だと思います。
Sheet1の作業列D2セルに
=A2&C2
という数式を入れフィルハンドルでずぃ~~~!っと下へコピーしておきます。
そしてSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$B:$B,MATCH(B$1&$A2,Sheet1!$D:$D,0)),"")
という数式を入れ、フィルハンドルで列・行方向にコピーすると
画像のような感じになります。
※ 作業列が目障りであれば
遠く離れた列にするか、列を非表示にしておきます。
こんな感じではどうでしょうか?m(_ _)m
No.3
- 回答日時:
合計を求めるのではないから、基本的にINDEX関数とMATCH関数の組み合わせではないかな。
シートAの日付が、必ず順番に並んでいるなら、さらにCOUNTIF関数とOFFSET関数を組み合わせることで簡単に参照できる。
・・・以下とても長いです。覚悟してください・・・
面倒なので、まずは同じシート上に作るものとして説明します。
A1セルから「シートA」の日付が、F1セルから「シートB」の日付が入力されているとします。
まず、9/5のデータのみ注視すると次のように関数式を作ることができます。
F2セルには
=INDEX(B1:B6,MATCH(E2,C1:C6,0))
絶対参照を加え、
=INDEX(B$1:B$6,MATCH(E2,C$1:C$6,0))
これをF7セルまでコピー。
希望通りの結果を得られるはずです。
次に9/6のデータを拾うことを考えます。
日付が連続しているのであれば、同じ日付がいくつあるのかを数えることで連続するセルの数を知ることができます。
この時にCOUNTIF関数を使います。
=COUNTIF(A:A,G1)
これでG1セルの値(9/6)と同じセルの数を得られます。
次に9/6が上から何番目のセルから始まっているのかを調べます。
ここでもMATCH関数を使います。
=MATCH(G1,A;A,0)
さらに得られた値からOFFSET関数で範囲を指定します。
OFFSET(基準セル,基準セルから下,基準セルから右,縦の範囲,横の範囲)
として使うので、
B列の参照範囲は、
OFFSET(A1,MATCH(G1,A:A,0)-1,1,COUNTIF(A:A,G1),1)
C列の参照範囲は、
OFFSET(A1,MATCH(G1,A:A,0)-1,2,COUNTIF(A:A,G1),1)
と指定することになります。
絶対参照を加えて9/5で作成した数式に当てはめると、
=INDEX(OFFSET($A$1,MATCH(G$1,$A:$A,0)-1,1,COUNTIF($A:$A,G$1),1),MATCH($E2,OFFSET($A$1,MATCH(G$1,$A:$A,0)-1,2,COUNTIF($A:$A,G$1),1),0))
と結構長い数式になります。
※同じシート上にあるのでこのセルをコピーして、希望通りの動作になるか確認してください。
あとはシートが異なるということなので、シートBからシートAを参照するため、シートAの範囲に対してシート名を加えます。
=INDEX(OFFSET(シートA!$A$1,MATCH(G$1,シートA!$A:$A,0)-1,1,COUNTIF(シートA!$A:$A,G$1),1),MATCH($E2,OFFSET(シートA!$A$1,MATCH(G$1,シートA!$A:$A,0)-1,2,COUNTIF(シートA!$A:$A,G$1),1),0))
最後にG1セルとかE2セルなんて箇所をシートBに記入するときのセル番地に書き換えればOK。
シートBのB1セルから日付を入力するならば、
B2セルには、
=INDEX(OFFSET(シートA!$A$1,MATCH(B$1,シートA!$A:$A,0)-1,1,COUNTIF(シートA!$A:$A,B$1),1),MATCH($A2,OFFSET(シートA!$A$1,MATCH(B$1,シートA!$A:$A,0)-1,2,COUNTIF(シートA!$A:$A,B$1),1),0))
と、これだけ見ても訳が分からない数式になります。
そしてこのセルを必要な範囲にコピーするだけ。
※実際のシート名を指定しましょう。
このように順番に考えて作るために、1つずつ数式を組んで動作を確認し、最後に組み合わせると良いかもしれません。
※自分はG2セルからG5セルまで使って後半の数式を組みました。
・・・ポイント・・・
別のシート上のセルを参照するときはシート名を付けて範囲を指定する。
シート名+!+セル番地
セルをコピーすると参照範囲もずれてしまうので、ずれないように絶対参照させるために「$」を付ける。
列記号の前に付けると列方向にコピーしても参照範囲はずれなくなります。
行番号の前に付けると行方向にコピーしても参照範囲はずれなくなります。
この性質を利用して、参照範囲がずれてほしい場合は$を抜いたままにする。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) VBAで同フォルダ内の別ブックを開かず参照して条件の一致する行の指定セルを抽出するには? 1 2022/07/21 19:29
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- Excel(エクセル) Excelの関数でこんな処理ができますか 1 2023/02/08 13:46
- Excel(エクセル) エクセルのマクロで複数条件に当てはまるものを全て抽出したいです 7 2022/05/21 08:51
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Excel(エクセル) Excelでのデータ管理 6 2022/12/24 09:33
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- その他(Microsoft Office) Excelで該当しない項目(#N/Aの商品名)を簡単に表示・抽出させる方法についてです 1 2022/08/25 22:12
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
パソコンからL判で印刷する方法
-
Cmykで印刷すると暗くなる
-
弥生の青色申告の借方勘定科目...
-
弥生の青色申告オンライン を初...
-
エクセル 背景色のついたセル位...
-
弥生会計オンラインで、バック...
-
タックインデックスシール
-
Excelの二画面表示でマウスホバ...
-
Excelの見積書の消費税をブルタ...
-
弥生会計は毎年アップグレード...
-
100パーセント不正会計がないよ...
-
「Windows7」サポート
-
カッコの2番で計算をした結果74...
-
わかる方教えてください! 今日...
-
決算書の特別会計の処理方法に...
-
勘定科目の給料賃金と専従者給...
-
弥生会計について詳しい方教えて
-
勘定科目を教えてほしいです 会...
-
freeeの会計ソフトって、どうで...
-
自動釣銭機(富士電機ECS777)の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
パソコンからL判で印刷する方法
-
タックインデックスシール
-
弥生の青色申告オンライン を初...
-
一太郎で封筒の宛名印刷したい...
-
共有しているエクセルのファイ...
-
100パーセント不正会計がないよ...
-
勘定科目の給料賃金と専従者給...
-
男が新卒で経理の職に就くのは...
-
弥生会計について詳しい方教えて
-
EXCELで、「メモリ不足、正しく...
-
一太郎2022に古いATOKは使える...
-
出金伝票の書き方ですが、 勘定...
-
数量・会社ごとに異なる単価表...
-
Googleスプレッドシートで合計...
-
numbersで累計を計算するには
-
わかる方教えてください! 今日...
-
エクセルみたいな無料表計算ソフト
-
Excelの二画面表示でマウスホバ...
-
Excel IF構文内の計算式を有効...
-
パソコンで請求書や納品書の作...
おすすめ情報