複数行ある同一項目内容を一列に並べたい(excel

質問

教えていただきたいのはexcel(2000)に関する次の内容です。
下記のようなA表があります。
A1:Lot, B1:項目a, C1:項目b, D1:項目c, E1:項目d,
A2:S01,B2:kita, C2:10, D2:100,E2:50,
A3:S01,B3:minami, C3:4, D3:8, E3:50,
A4:S02,B4:kita, C4:5, D4:25, E4:60,
A5:S02,B5:nishi, C5:2, D5:4, E5:60,
A6:S02,B6:higashi, C6:3, D6:6, E6:60,
以下、50行くらい続く。A列のLotの連番がふえていく。同じ連番は1個から4個ある(項目aの数だけ)。項目dは同連番では同じ数値が入る。

そして別のシートに下記のB表があります。
A1:Lot, B1:項目a(kita), C1:項目b(kita), D1:項目c(kita), E1:項目a(minami), F1:項目b(minami), G1:項目c(minami), H1:項目a(kita), I1:項目b(kita), J1:項目c(kita), K1:項目a(nishi), L1:項目b(nishi), M1:項目c(nishi), N1:項目a(higashi), O1:項目b(higashi), P1:項目c(higashi), Q1:項目d,
A2:S01,
A3:S02,
A4:S03,
A5:S04,
以下、A列にはLotが1個ずつ入っている。（ソート済み）B列以降の2行以降はA表からの参照値が入る。

このとき、A表からB表を完成させるためにB表のB列以降の2行以降に入れる数式は何でしょうか。
出来るとすれば数種の関数の組み合わせなのでしょうが、思いつきません。
数式で不可能であれば、excelマクロ、またはaccessでも。
よろしくお願い致します。

回答No.2

keithin · ベストアンサー

Excel2002(OfficeXP)以降を使っているなら「数式の検証」という便利な機能がついていますが，Excel2000を使っているなら次のようにして勉強してください。

準備：
B3の式を暫定的に
=SUMPRODUCT((Sheet1!$A$1:$A$6=$A3)*(Sheet1!$B$1:$B$6=B$1)*(Sheet1!$C$1:$D$1=Sheet2!B$2), Sheet1!$C$1:$D$6)
に変更する

手順１：
数式バーの中で数式中の
(Sheet1!$A$1:$A$6=$A3)
の範囲を選択し，F9キーを押す　→その部分が計算されて計算結果に置き換わる

手順２：
数式バーの中で数式中の
(Sheet1!$B$1:$B$6=B$1)
の範囲を選択し，F9キーを押す

手順３：
数式バーの中で数式中の
{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}*{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
の範囲を選択し，F9キーを押す
→｛６行×１列の配列｝×｛６行×１列の配列｝のかけ算から｛６行×１列の配列｝が得られる事を確認する

手順４：
(Sheet1!$C$1:$D$1=Sheet2!B$2)
をF9し，更にかけ算の前段と共にF9する
→｛６行×１列の配列｝×｛１行×２列の配列｝のかけ算から｛６行×２列の配列｝が得られることを理解する

最後にSUMPRODUCT(｛６行×２列の配列｝, Sheet1!$C$1:$D$6)　で，該当するセルの数値が同士がかけ算され合算される流れを理解する。

オマケ：
回答した数式を
=SUMPRODUCT((Sheet1!$A$1:$A$6=$A3)*(Sheet1!$B$1:$B$6=B$1)*(Sheet1!$C$1:$D$1=Sheet2!B$2)*Sheet1!$C$1:$D$6)
としてしまうと，エラーで計算できない理由を同様の手法で計算のステップを確認しながら理解しておいてください。

お礼

ご指導ありがとうございます。まだ、理解できていませんが時間が長くなりそうなのでわからないことが分かったときは別枠で質問させていただきます。ありがとうございました。

回答No.1

keithin

＞以下、50行くらい続く

と言った程度のデータ量なら，次のようでイイです。

＃ただし
項目ａを参照する必要はない
kitaが２度も出てくる必要は無い

簡単のため，添付図のように作成します
Ｂ３：
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A3)*(Sheet1!$B$1:$B$100=B$1)*(Sheet1!$C$1:$D$1=Sheet2!B$2),　Sheet1!$C$1:$D$100)
右に下にコピー

Ｊ３：
=VLOOKUP(A3,Sheet1!A:E,5,FALSE)

＃S03やS04はそもそも元のリストに存在しないことを示すために敢えてエラー表示を残していますが，そういうのはユルセナイときは，必要に応じて関数を追加したり条件付き書式などを利用してエラー表示を消してください。

お礼

早々の回答有難う御座います。
教えていただいたのをそのまま実行し、出来たのをみて驚いたのですが、昨日一日、なんでこうなるのかを考えていて分からずじまいで御礼が遅れてしまいました。
VLOOKUPは分かります。SUMPRODUCT使用数式についてが分かりません。
SUMPRODUCTの(Sheet1!$A$1:$A$100=$A3)でLotを位置を探し、(Sheet1!$B$1:$B$100=B$1)で項目aの位置を探し(Sheet1!$C$1:$D$1=Sheet2!B$2)で項目b or cの位置をさがして配列を作って, (Sheet1!$C$1:$D$100)の配列と掛け算、足し算すると数値なのはひとつ（他が文字列）なのでその数値が入るという理解なのですが、(Sheet1!$A$1:$A$100=$A3)*(Sheet1!$B$1:$B$100=B$1)*(Sheet1!$C$1:$D$1=Sheet2!B$2)と Sheet1!$C$1:$D$100)が同じ行数と列数になる理由が分かりません。もう少し教えていただけないでしょうか。
実際に使用する表は項目がもっとあるので、しくみも理解したいのです。
お手数ですが、よろしくお願い致します。