EXCELを使って特定の文字を参照、コピーしたい

質問

エクセルシートで以下、4列の表があるのですが、
一部商品名、区分が歯抜けです。

会員番号 商品名 区分　　回数
111111111 セレクト　 通常　　 　1
111111111 　　　　 　　　 2
111111111 　　　　 　　　 3
111111111 　　　　 　　　 4
111111111 　　　　 　　　 5
222222222 通常商品 定期 1
222222222 　　　　 　　 2
333333333 通常商品 通常 1
333333333 2

これを会員番号が同じものは回数１と同じ文字列を
商品名、区分に反映できないでしょうか。

実現したい事
会員番号 商品名 区分　　回数
111111111 セレクト　 通常　　 　1
111111111 セレクト 　通常 2
111111111 セレクト 　通常 3
111111111 セレクト 　通常 4
111111111 セレクト 　通常 5
222222222 通常商品 定期 1
222222222 通常商品 定期 2
333333333 通常商品 通常 1
333333333 通常商品 通常 2

※会員番号が同じものは、空白の文字列に、
同じ商品名、区分を反映したい。

単純に会員番号に記載されている商品名・区分を
同じ会員番号で空白の箇所にも反映させたい。
（回数1にはすべて商品名、区分が入っている）

エクセルの数式内で一発でできるのではとおもうのですが、
可能でしょうか。

回答No.2

noname#204879 · ベストアンサー

１．表内の任意のセルを選択
２．[編集]→[ジャンプ]→[セル選択]を実行
３．“アクティブセル領域”に目玉を入れて[OK]をクリック
この結果を Fig-1 に示す。
４．ステップ２を実行
５．“空白セル”に目玉を入れて[OK]をクリック
この結果を Fig-2 に示す。
６．アクティブセル B3 内に式 =B2 を入力して、(まだ Enterキーは押さない！)
この結果を Fig-3 に示す。
７．Ctrl キーを抑えたままで、Enter を「エイヤッ！」と叩き付け
この結果を Fig-4 に示す。
８．列Ｂ、Ｃを選択して、[コピー]→[値の貼り付け]を実行

回答No.5

bunjii

>15万行あるのですが、想定1000行でいただいていますが、15万行でも動きますでしょうか。
動きません。
2桁多いデータ量です。
データが会員番号と回数で昇順にソートされていれば回答No.1が適切な方法です。
取引の日付順等で不揃いのときはソートしてジャンプを使うか、1000行程度に分割して処理されると良いでしょう。

お礼

限界があるんですね。承知いたしました。

回答No.4

bunjii

>列が１つ追加した場合にはどうすればいいのでしょうか。
序なので、行数が増えた時や途中に空欄の行が有るときの対策も含めて提示させて頂きます。
Sheet2!A2=IF(COUNTA(Sheet1!$A$2:$A$1001)>=ROWS($A$2:$A2),TEXT(INT(SMALL(INDEX(Sheet1!A$2:A$1001+Sheet1!E$2:E$1001*0.01,0),ROWS(A$2:A2)+COUNTBLANK(Sheet1!$A$2:$A$1001))),"0"),"")
Sheet2!E2=IF(COUNTA(Sheet1!$A$2:$A$1001)>=ROWS($A$2:$A2),ROUND(MOD(SMALL(INDEX(Sheet1!A$2:A$1001+Sheet1!E$2:E$1001*0.01,0),ROWS(E$2:E2)+COUNTBLANK(Sheet1!$A$2:$A$1001)),1)*100,0),"")
Sheet2!B2=IF(COUNTA(Sheet1!$A$2:$A$1001)>=ROWS($A$2:$A2),IF($E2=1,INDEX(Sheet1!B$1:B$1001,SUMPRODUCT((Sheet1!$A$2:$A$1001=$A2)*1,(Sheet1!$E$2:$E$1001=$E2)*ROW(B$2:B$1001))),B1),"")
Sheet2!b2を右へD2までコピーし、Sheet2!A2:E2を下へ必要数コピーします。
尚、処理対象範囲はSheet1!2:1001（行番号2～1001）の1000行です。
また、1つの会員番号について回数は1～99の範囲を想定しています。
各数式でデータをどのように処理しているかを理解すれば行数や列数が増減したときにご自身で対応できるようになりますので、この際、数式のコピペで済ませないようにしてください。

お礼

ありがとうございます。検証します。

補足

15万行あるのですが、想定1000行でいただいていますが、15万行でも
動きますでしょうか。

※先ほど試したら「応答なし」になっていまいました　（悲しい）。

※いただいているロジックは正しいようです。

＞
ジャンプの方（先の投稿）は問題なかった。

回答No.3

bunjii

>エクセルの数式内で一発でできるのではとおもうのですが、可能でしょうか。
ご提示の模擬データがSheet1としてSheet2へ空欄無しの表にしたいのでしょうか？
1つの数式を右と下へコピーすれば良いと言う訳にはいかないようです。
会員番号は文字列として検証してみます。
Sheet2!A2=TEXT(INT(SMALL(INDEX(Sheet1!A$2:A$10+Sheet1!D$2:D$10*0.01,0),ROWS(A$2:A2))),"0")
Sheet2!D2=ROUND(MOD(SMALL(INDEX(Sheet1!A$2:A$10+Sheet1!D$2:D$10*0.01,0),ROWS(D$2:D2)),1)*100,0)
Sheet2!B2=IF($D2=1,INDEX(Sheet1!B$1:B$10,SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*1,(Sheet1!$D$2:$D$10=$D2)*ROW(B$2:B$10))),B1)
Sheet2!B2を右に1つコピーし、Sheet2!A2:D2を下へ必要数コピーします。
検証にはご提示の見出しの1行と2～10行のデータのみを対象に数式を構成しました。
データは会員番号がランダムに配置されていても昇順に抽出されます。

お礼

ありがとうございます。

MackyNo1さんのやり方で問題ないようにおもえるのですが、、、
検証してみたいです。

しかし列が増えまして、

会員番号（A列）　商品名（B列）　区分（C列）　チャネル（D列）　　回数（E列）

の表で、かつ　B、C、Dが空白については、

同じ会員番号＆回数1の記載をそのまま反映するとしたいです（１つ列が追加となった）。

この状態で検証するためにはどのようにすればいいのでしょうか。

＞
Sheet2!A2=TEXT(INT(SMALL(INDEX(Sheet1!A$2:A$10+Sheet1!D$2:D$10*0.01,0),ROWS(A$2:A2))),"0")
Sheet2!D2=ROUND(MOD(SMALL(INDEX(Sheet1!A$2:A$10+Sheet1!D$2:D$10*0.01,0),ROWS(D$2:D2)),1)*100,0)
Sheet2!B2=IF($D2=1,INDEX(Sheet1!B$1:B$10,SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*1,(Sheet1!$D$2:$D$10=$D2)*ROW(B$2:B$10))),B1)

↑
列が１つ追加した場合にはどうすればいいのでしょうか。

先の２つのジャンプについては列が増えても問題なく対処できています。

回答No.1

MackyNo1

Ctrl＋Shift＋：のショートカット操作で、４列のデータ範囲を選択して、Ctrl+Gでジャンプダイアログを出して「セル選択」で「空白セル」にチェックを入れ「OK」して、画面上部の数式バーに「=」と入力し、アクティブセル（画面左上の名前ボックスに表示されているセル番地、例示のデータならB3セル）の1つ上のセル（例示のデータならB2セル）を選択して「=B2」、のような数式を入力し、Ctrlキーを押しながらEnterしてください。

お礼

＞の1つ上のセル

最初の空白の直上を参照させる・・ですね。

わかりやすいです。

あるがとうございます！！！！！！！！！！！！！！！！