エクセルの計算式を教えてください。

質問

添付してある表のように左に入れたデータを右矢印の表のように自動反映するようにしたいのですが、この右表の「？」にはどのような式をいれればよいものでしょうか？
　右表の『Ａ商品』・『Ｙ支店』のところには、共にリストの設定がしてあります。商品のセル（Ａ商品）ではＡ商品・Ｂ商品・Ｃ商品を選ぶことができ、支店のセル（Ｙ支店）ではＸ支店・Ｙ支店・Ｚ支店を選ぶことが出来ます。
　このリストで選ぶと自動的にこの「？」に正しい数字が出るようにしたいです。

分かる方、教えていただけますよう宜しくお願いいたします。

回答No.6

imogasi · ベストアンサー

エクセル関数式を使う表で、関数式で使うところの、参照するセルを「セル結合しない」のは鉄則でしょう。（下記＊）
エクセルでは、セル結合は最小限にすべきだ。色々な面で（ＶＢＡなどでも）あとの扱いが難しくなる。経験が無いからこんなことをやってしまうのだろう。
非常に式が難しくなると思う。
それで本件やむを得ず、作業列を使った。
例データ　Ａ１：Ｄ９
x支店 a 1 x支店
b 2 x支店
c 3 x支店
y支店 a 4 y支店
b 5 y支店
c 6 y支店
z支店 a 7 z支店
b 8 z支店
c 9 z支店
（注）回答では表の列位置が乱れると思うが、質問の例そのまま＋Ｄ列（作業列）です。
Ｄ１は=A1
Ｄ２は　=IF(A2=0,D1,A2)
下方向に式を複写。結果上記の通り。
ーーー
あとは２条件の検索なので、頻繁に質問が出るもの。
ＳＵＭＰＲＯＤＵＣＴ関数を使うのが常識。
エクセル２００７ならＳＵＭＯＦＳを検討のこと。
ーーーー
Ｆ１：Ｇ２で
- a
y支店 4
Ｇ２の式は
=SUMPRODUCT((D1:D9=$F$2)*(B1:B9=$G$1)*(C1:C9))
結果
上記の通り４
ーーー
＊参考
Ａ列の全行にＸＸ支店を値としていれ、条件付き書式で直上行と同じ値の場合は、文字色を白色にして見えなくするとか。
その場合Ｘ支店などの見える位置は一番上の行になる（中央揃えは難しい）ことは、辛抱しなければならないが。

回答No.9

tom04

こんにちは！
すでに多くの方が回答されていますが・・・

極力セルの結合がない方が色々と都合が良いのですが、
今回の場合、質問の表そのままをどうしても使用したいのであれば
無理矢理って方法で↓の画像のF3セルに

=IF(OR(F2="",E3=""),"",INDEX(C2:C10,MATCH(E3,A2:A10)+MATCH(F2,B2:B10,0)-1))

という数式を入れてみました。
尚、各支店の商品名の並びは全て同じでないと正確なデータを返すことができません。

そして、余計なお世話かも知れませんが、
画像の下側がSheet2になります。
実際はこのような感じで元のデータを整理すると
簡単に希望の値を返すことができると思います。

Sheet2のH2セルに
=IF(COUNTBLANK(F2:G2)>0,"",INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(F2,B1:D1,0)))

という数式を入れています。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

回答No.8

myRange

＿＿＿＿Ｂ＿＿＿Ｃ＿＿＿Ｄ＿＿＿＿＿Ｈ＿＿＿Ｉ＿＿
０５＿Ｘ支店＿Ａ商品＿１００＿＿＿支店名＿商品名＿
０６＿＿＿＿＿Ｂ商品＿２００＿＿＿＿＿＿＿Ａ商品＿
０７＿＿＿＿＿Ｃ商品＿３００＿＿＿Ｙ支店＿■■■＿
０８＿Ｙ支店＿Ａ商品＿４００＿＿＿
０９＿＿＿＿＿Ｂ商品＿５００＿＿＿
１０＿＿＿＿＿Ｃ商品＿６００＿＿＿
１１＿Ｚ支店＿Ａ商品＿７００＿＿＿
１２＿＿＿＿＿Ｂ商品＿８００＿＿＿
１３＿＿＿＿＿Ｃ商品＿９００＿＿＿

質問では、Ｂ５～７、８～１０、１１～１３は結合セルになっており、
支店名は、Ｂ６，９、１２に表示されてるように見えますが
結合セルの場合は、【実際の値】は、上記の表のように
最初のセル（Ｂ５，８、１１）のみに入ってますのでそれを利用します。

●Ｉ７にセットする式

=INDEX(D5:D13,MATCH(H7,B5:B13,0)+MATCH(I6,C5:C7,0)-1,0)

ＩＮＤＥＸ（求める値のある範囲、その範囲での行、その範囲での列）

ＩＮＤＥＸ（Ｄ５～Ｄ１３，支店の位置＋商品の位置－１、０）
★求める値のある範囲が１列なので、列は省略または０でいい

【Ｙ支店、Ａ商品を例として説明】

●MATCH(H7,B5:B13,0)

　　支店の位置は、Ｂ５～Ｂ１３の範囲を検索
　　Ｙ支店の位置：４　←　Ｂ５～Ｂ１３の中で４番目

●MATCH(I6,C5:C7,0)

　　商品の位置は、Ｃ５～Ｃ７の３セルを検索
　　Ａ商品の位置：１　←　Ｃ５～Ｃ７の中で１番目

●求める値は、Ｄ８（Ｄ５～Ｄ１３の４番目）にあるので
上記結果から、４を求めるには

　支店の位置＋商品の位置－１　＝　Ｄ５～Ｄ１３の中での位置
　　　　４　＋　１　－１　＝　４番目

●よって
ＩＮＤＥＣ（Ｄ５：Ｄ１３、４、０）＝　４００　が求まる

以上です。

回答No.7

popuplt

chiizu2様の画像を拝借しまして、支店名は結合されているとして
[F4]=INDEX(C2:C10,MATCH(E4,A2:A10)+MATCH(F3,B2:B4)-1)
※エラー処理なし。

回答No.5

noname#204879

B　　　 C　　 D　E　F　G　　H　　　 I
5　 Ｘ支店　Ａ商品　1
6　　　　　 Ｂ商品　2　　　　　　　　　 Ａ商品
7　　　　　 Ｃ商品　3　　　　　 Ｙ支店　　　 4
8　 Ｙ支店　Ａ商品　4
9　　　　　 Ｂ商品　5
10　　　　　Ｃ商品　6
11　Ｚ支店　Ａ商品　7
12　　　　　Ｂ商品　8
13　　　　　Ｃ商品　9

I7: =VLOOKUP(I6,OFFSET(INDIRECT(ADDRESS(MATCH(H7,B1:B13,0),3)),,,3,2),2,FALSE)

回答No.4

135ok

表を次のように変える方法もあるかと思います。
無理ならば、無視してください。

　　　　　Ａ商品　　Ｂ商品　　Ｃ商品
Ｘ商品　　　１　　　　２　　　　３
Ｙ商品　　　４　　　　５　　　　６
Ｚ商品　　　７　　　　８　　　　９

回答No.3

KURUMITO

次のように作業列を作って対応することでしょう。
例えばお示しの表がＡ2セルからＣ10セルの範囲にあるとします。
そこで作業列としてＤ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(A2="",B2=""),"",IF(A2<>"",A2&"/"&B2,LEFT(D1,FIND("/",D1)-1)&"/"&B2))
答えですがＦ4にＹ支店と入力し、Ｇ3にＡ商品と入力するとして、その販売数をＧ4セルに表示させるとしたらＧ4セルには次の式を入力すればよいでしょう。
=IF(OR(F4="",G3=""),"",INDEX(C2:D10,MATCH(F4&"/"&G3,D2:D10,0),1))

回答No.2

chiizu2

すみません
先ほどの表で
支店名のセルを結合してしまうと
B・Cに対応する支店名のデータが
無くなってしまいDSUM関数が
うまく機能しないので
結合させずに全ての商品名に対して
支店名を入力してください
支店名表示がうるさいようだったら
不要な支店名を
セルの書式設定→フォント→文字色→白で見えなくさせてください
条件設定のセルについても下の例のようにしてもいいです

回答No.1

chiizu2

DSUM関数を使ったらいかがですか
ただしこの場合
条件設定のセルは同じ行に記述する必要がありますので
質問のようなひょうにしたければ
どこか別の場所に条件を記述する
H2→=E4
I2→=F3
F4→=DSUM(A1:C10,"販売数量",H2:I3)として
F3の商品名とE4の支店名を変化させれば
条件に一致した販売数量の合計が出力されます
下の表の場合
条件設定のH列とI列を非表示に設定しておけば
この2列は見えなくなりますし印刷もされません