Excel

質問

お世話になります。Excelで何の関数つかえばいいのかわかりません。
月 支店名 品名 値段
1月 1 A 160
8000行ぐらい続きます。
これを
1支店 2支店
1月 2月 3月... 1月 2月 3月...
A
B
上記のように、支店が6店あり品名もAから重複抜かして1000ぐらいある中の AからGの値段データを抜き出す関数はなにをつかえばいいのかわかりません。index関数、Vlook関数は複数条件で使ったことがないのですし、カウントイフス関数は数を抜き出す関数だし...とか自分なりに考えてはいるのですがわかりません。ご存知の方お知恵拝借させて下さい。

回答No.4

dogs_cats · ベストアンサー

売上集計と売価検索の両方の関数を記載しておきます。

元データをsheet1、抽出先をsheet2とします。（添付画像）

１．売上集計
sheet2　B4式　
=IF($A4="","",SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,$B$1,Sheet1!$A:$A,B$3,Sheet1!$C:$C,$A4))

右、下方向へオートフィル

商品名は商品一覧表をお持ちでしょうからそれからコピーするのが一番早い方法ですが、商品名まで抽出したい場合の方法は、sheet1に作業列を設定する方法が簡単な方法です（配列数式方式もありますがお勧めしません）
商品名抽出不要の場合はsheet1の作業列の作成は不要。
sheet1のE2式
=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"")
下方へオートフィル

sheet2、A4式
=IF(COUNT(Sheet1!$E$2:$E$10000)<ROW(A1),"",INDEX(Sheet1!$C$2:$C$10000,SMALL(Sheet1!$E$2:$E$10000,ROW(A1))))

２．値段の抽出
仮に複数一致のデータがある場合は、最初に一致した数値を取得します。
sheet2のE4式
=IF($A4="","",INDEX(Sheet1!$D$2:$D$10000,MATCH($B$1&$A4&B$3,INDEX(Sheet1!$B$2:$B$10000&Sheet1!$C$2:$C$10000&Sheet1!$A$2:$A$10000,0),0)))
右方向、下方向へオートフィル。

処理数が多いので、時間がかかる可能性はあります。

お礼

ご回答ありがとうございます。indexもVlookも単純なパターンしかやったことなかったのですごい参考になりました本当にありがとうございました。

回答No.6

imogasi

#5です
ＶＢＡでやるなら、下記の程度の作業です。
Ｓｈｅｅｔ１＝源データシート（「その年」以外の日のデータは混じっていないものとする）
見本
売上日 支店店番 商品 販売価格
2015/1/3 1 a 1
2015/1/4 2 c 3
2015/1/5 3 a 5
2015/1/6 1 b 1
2015/1/7 2 c 3
2015/1/8 3 a 5
2015/1/9 1 c 7
2015/2/10 2 b 9
2015/2/11 3 b 1
2015/2/12 1 a 1
以下行略
ーーー
Ｓｈｅｅｔ２＝結果を出すシート
Ｓｈｅｅｔ３＝Ｓｈｅｅｔ１をシートコピーしたシート。店別にソートしておいた方がよい。
（１）商品名一覧を１列に出す。
（関数でやる場合も、この作業が必要で、その後ＳＵＭＩＦＳで月と商品名を指定して
集計データを各セルにだす方法になるだろう）
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1099550340
・関数でやる方法と
・操作でやるなら、データーフィルター「フィルタオプションの設定」ー「重複するレコードは無視する」を使う
・ＶＢＡで出す方法
がある。
・それをＳｈｅｅｔ２のＡ列に（Ａ３セルより下行に）貼り付ける。
・ほかに、Ｓｈｅｅｔ２の第２行に
商品 １月 ２月 ３月 ４月 ５月　以下右部分省略
を貼り付ける（見出しとセットする列の条件に使います）。
（３）ＶＢＥ画面の標準モジュールに下記コードを貼り付ける。
ーーーー
Sub test01()
b = Array(1, 2, 3, 4, 5, 6) '支店店番テーブル
Set sh1 = Worksheets("Sheet3")
Set sh2 = Worksheets("Sheet2")
sh2.Range("b3:m100000").Clear '集計結果を置くセル範囲をご破算
Set sh3 = Worksheets("Sheet3")
dr = Worksheets("Sheet3").Range("A100000").End(xlUp).Row 'データ最下行番号
'MsgBox dr
sh2.Cells(1, "A") = b(j) & "支店集計"
'---
For j = b(0) To b(0) '6店
For i = 2 To dr
'--１行の各フィールドのデータを捉える
m = Month(sh1.Cells(i, "A")) '月数字
'MsgBox m
br = sh1.Cells(i, "B") '支店コード
g = sh1.Cells(i, "C") '商品
p = sh1.Cells(i, "D") '売上高
If br = j Then 'その支店限定
k = k + 1 '件数
'列を探る
c = m + 1 '月の列を探す.１月は第２列から始まるので＋１
r = sh2.Range("A3:A5").Find(g).Row '商品の行を探す
sh2.Cells(r, c) = sh2.Cells(r, c) + p '商品売上高を足しこむ
End If
Next i
Next j
End Sub
ーーーー
これを実行する（Ｆ５キー）。
（４）結果シートのＳｈｅｅｔ２に、１か店分の集計表が出る。
1支店集計
商品 １月 ２月 ３月 ４月 ５月　以下右部分省略
a 2 4 16 16 2
b 2 14 ー 2 2
c 14 ー 2 2 14
---
●ピボットテーブルを勉強するのが一番得策と思う。
●関数でやるなら
商品 １月 ２月 ３月・・・
a Ｘ
b
c
・
・
のようなのを作っておいて（やり方は、前述の「フィルタオプションの設定」で空白範囲に出して、ここＡ列へコピペ、Ｘのところのセルに関数ＳＵＭＩＦＳを入れて、下方向、右方向に式を複写する方法が一番簡単かと思う。

お礼

ご回答ありがとうございます。親切にマクロのソースとか教えてくださって、ありがとうございます。利用させて頂きます。又わからないところあるかと思います。その時は、また、よろしくお願いいたします。

回答No.5

imogasi

エクセルの初心者にありがちな、エクセルの利用＝「エクセル関数を使う」しか思い浮かばない、悪いパターンです。
エクセルには、大きく分けて
（１）操作（並べ替えもある）（２）ピボットテーブルのように、限定した目的のプログラムをＭＳが組んでいて、操作で使えるようにしたもの（３）関数式（ほかに配列数式）（４）ＶＢＡがあるので、日ごろよく勉強し、課題に当たるときは、いつもどれを使うべきか考えるべきなのだ。関数だけしか知らずで、仕事にエクセルを使うのは、力不足だろう。
ーーー
この質問のおすすめ処理パターンは、ピボットテーブルの利用だと思う。
ーーー
それに質問の文章が、ブッキラボウで、内容がよくわからない。
表題も「Ｅｘｅｌ」なんて書いても、読者の理解のなんの足しにもならない。
内容を要約して、表題を付ける訓練をすること。
ーー
もっとも大きい問題は、「値段」とは売上高で、支店別＋月別＋品名別の（売上高）集計を望んでいるのだろうね。値段といえば個別の商品の値段表のようにも取れる。
値段は、日々に変わるのだろうね。
　例もそっけない。もう少し例を増やして、読者が推定で内容の理解を補えるようにすべきだ。
ーー
エクセル関数を使って、条件を指定して、条件に該当するものを（個々に）抜き出して並べるのは、関数式が複雑になって、ちょっとぐらい関数を勉強しても思いつくものではないし、回答に出ても理解できないだろう。
　集計（合計）値をだすほうが、エクセル関数でやりやすい。
ＷＥＢで「エクセル　関数　抜出し」で照会すれば、質問と回答が出て、その関数式のむつかしさが、わかる例が出てくるだろう。
「1支店 2支店」は列方向に並べるということだろうが、表が見にくいのではないか。
この辺にエクセル使用経験が豊富かどうか出てくると思う。印刷もしにくい。
別支店分は、下方向に支店分を並べるのがおすすめ。
ーー
もし間に合えば、ＶＢＡでの回答を後刻上げてみます。

お礼

言葉足らずなところ多々あり申し訳ございませんでした。ご回答ありがとうございます。又教えて頂くこと多々あると思いますのでその時は又よろしくお願いいたします。

回答No.3

-9L9-

月や支店で値段が変わるってどういうこと？「値段」とはもちろん単価のことですよね？
「上記のように」とありますが、結局「値段」はどこにどういう風に表示するんですか？
A列には一行目に１支店、２行目に１月、3行目・４行目に品名が入っていますが、B列には一行目に2支店、２行目に2月と入っていますが、3行目・４行目には何もない、以下C列以後も同じようですが、この表の意味するところは何？何のためにこんな表を作るのか、目的が全く分かりません。
質問するなら意味が分かるように書いて下さし。

お礼

ご回答ありがとうございます。言葉足らずでご迷惑おかけしました。解決しましたので、色々とありがとうございました。又わからない時はよろしくお願いいたします。

回答No.2

keithin

COUNTIFS関数は複数条件の合致する個数を数える関数です
SUMIFS関数は複数条件で合致する数字の合計を数えます。

例：
=SUMIFS(D:D,A:A,"1月",B:B,"1支店",C:C,"A")
勿論実際の式中の「"1月"」だの「"A"」だのは、あなたが実際に用意した集計表の該当セルを使って計算します。

もっとも
＞重複抜かして1000ぐらいある

わざわざこんな説明を書いているのに「具体的なデータの事例」が何も情報提供されれていないので、これじゃ計算できないのかもしれませんね。（もっともその場合は、一体「何を」計算したいのかイミフメイになりますが）

お礼

ご回答ありがとうございます。皆さんのお知恵拝借して解決しましたので、この度は色々とご迷惑おかけしました。又わからない時あると思いますので、その時は又よろしくお願いいたします。

回答No.1

PXU10652

関数ではなく、ピボットテーブルでしょう。