現在エクセルにてデータをまとめております。
生データのシートから抽出シートにデータをまとめたいのですが
どのような関数を使用してまとめたらよいでしょうか?
生データのシート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で質問しましょう!
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
弥生の青色申告オンライン を初...
-
パソコンからL判で印刷する方法
-
メモ帳で、行間が1行くらいあい...
-
共有しているエクセルのファイ...
-
Googleスプレッドシートで合計...
-
一太郎で封筒の宛名印刷したい...
-
男が新卒で経理の職に就くのは...
-
数量・会社ごとに異なる単価表...
-
みつも郎で
-
pasumoについて
-
ExcelのList & List & List & L...
-
Excelで売上げデータの中の任意...
-
エクセルについて
-
和洋菓子店です。各商品の原価...
-
指定した範囲内のセルが全て入...
-
Excelとキントーンの違いはなん...
-
Excelで縦軸と横軸の両方で昇順...
-
弥生会計の青色申告ソフトを使...
-
高度計のソフト
-
エクセルの文章の修正法について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
パソコンからL判で印刷する方法
-
共有しているエクセルのファイ...
-
弥生の青色申告オンライン を初...
-
Googleスプレッドシートで合計...
-
タックインデックスシール
-
一太郎で封筒の宛名印刷したい...
-
一太郎2022に古いATOKは使える...
-
弥生会計オンラインで、バック...
-
メモ帳で、行間が1行くらいあい...
-
出金伝票の書き方ですが、 勘定...
-
スプレッドシートに関して コピ...
-
EXCELで、「メモリ不足、正しく...
-
男が新卒で経理の職に就くのは...
-
数量・会社ごとに異なる単価表...
-
numbersで累計を計算するには
-
勘定奉行の売上削除
-
Excelの改ページプレビューの既...
-
エクセル 背景色のついたセル位...
-
勘定奉行で諸口を使いたい
-
添付のエクセルの車検証・運転...
おすすめ情報