エクセル２０００で教えて下さい。

質問

以前によく似た質問をしましたが、一部変更になった為再度質問させて頂きます。
シート１のＥ列に数値が入るとシート２に反映、Ｆ列に数値が入るとシート３に反映させたいのです。
条件としてタイ、香港、韓国、中国が入った場合は無視して反映させない、
東京、横浜、大阪のようにシート１のＤ列に同じ文字が重複した場合は
両方とも反映させる。
シート１の同じ行上でＥ列、Ｆ列に数値が重複する事はありません。
他、補足が必要なら申し付け下さい。

（シート１）
　　　　　　　Ｄ　　　　　　Ｅ　　　　　　Ｆ
６　　　　　東京　　　　　６００
７　　　　　大阪　　　　　　　　　　　　　７００
８　　　　　福岡　　　　　８００
９　　　　　宮崎　　　　　　　　　　　　　３００
１０　　　　横浜　　　　　９００
１１　　　　タイ　　　　　１００　　　　　　
１２　　　　横浜　　　　　４００　　　　　
１３　　　　香港　　　　　　　　　　　　　６５０
１４　　　　新潟　　　　　　　　　　　　　５００
１５　　　　東京　　　　　５５０
１６　　　　秋田　　　　　　　　　　　　　７５０
１７　　　　大阪　　　　　　　　　　　　　１５０
１８　　　　韓国　　　　　４４０
１９　　　　中国　　　　　　　　　　　　　８５０

（シート２）
　　　　　　　Ｂ　　　　　　　Ｄ　　　　　　
４　　　　　６００　　　　　東京
５　　　　　８００　　　　　福岡
６　　　　　９００　　　　　横浜
７　　　　　４００　　　　　横浜
８　　　　　５５０　　　　　東京

（シート３）
　　　　　　　Ｂ　　　　　　　Ｄ　　　　　　
４　　　　　７００　　　　　大阪
５　　　　　３００　　　　　宮崎
６　　　　　５００　　　　　新潟
７　　　　　７５０　　　　　秋田
８　　　　　１５０　　　　　大阪

回答No.5

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

マクロで行わないと、いろいろ見当してみましたが、補助セルを使ったほうが楽です。場所はどこでもかまいませんが、

4行目を基点とするなら、該当行を取る数式は、どこの列のセルでもよいですから、4行目から書いてください。

また、良く式のデータを換える要素が高いなら、また、ユーザー定義関数で作ってしまったほうが楽かもしれません。

-------------------------------------------------------
今回の式は、100行目までを想定して作られています。

ここでは、仮に、[H4] ～としました。

Sheet2
　H4 ～

=SMALL(INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"大阪","宮崎","新潟","秋田","タイ","中国","香港","韓国"},0))*ROW($D$6:$D$100),,),SUMPRODUCT(NOT(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"大阪","宮崎","新潟","秋田","タイ","中国","香港","韓国"},0)))*1)+ROW(A1))

Sheet2 B4 ～下に

=IF(OR($H4="",$H4>COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,2))

Sheet2 C4 ～下に

=IF(OR($H4="",$H4>COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,1))

Sheet3 B4 ～下に

=IF(OR($H4="",$H4>COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,3))

-------------------------------------------------------
Sheet3

　H4 ～

=SMALL(INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"東京","福岡","横浜","タイ","中国","香港","韓国"},0))*ROW($D$6:$D$100),,),SUMPRODUCT(NOT(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"大阪","宮崎","新潟","秋田","タイ","中国","香港","韓国"},0)))*1)+ROW(A1))

Sheet3 B4 ～下に

=IF(OR($H4="",$H4>COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,3))

Sheet3 C4 ～下に

=IF(OR($H4="",$H4>COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,1))

お礼

何度も有難うございました。
意図している事が出来ました。
本当に有難うございました。

「またか」と思われるかもしれませんが前回のマクロの分に訂正が
ありました。
再度質問を上げる事になりますが、もし宜しければ御教授願えれば
幸いです。

回答No.6

zap35

#01です。とりあえず補助セルなしでもできるようにしたのですがとんでもなく長い式になります。補助セルを活用するかマクロを使用する方が実用的だと感じました。長いのでシート３の分は割愛します。$Bを$Cに変えれば動くと思います。

シート２　Ｂ４セル
=IF(ROW()-3<=COUNT(Sheet1!$B$6:$B$19)-SUMPRODUCT((Sheet1!$A$6:$A$19="タイ")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="韓国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="中国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="香港")*(Sheet1!$B$6:$B$19<>"")),INDEX(Sheet1!$B$1:$B$19,LARGE(INDEX((Sheet1!$B$6:$B$19<>"")*(Sheet1!$A$6:$A$19<>"タイ")*(Sheet1!$A$6:$A$19<>"韓国")*(Sheet1!$A$6:$A$19<>"中国")*(Sheet1!$A$6:$A$19<>"香港")*ROW(Sheet1!$B$6:$B$19),,),COUNT(Sheet1!$B$6:$B$19)-SUMPRODUCT((Sheet1!$A$6:$A$19="タイ")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="韓国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="中国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="香港")*(Sheet1!$B$6:$B$19<>""))+4-ROW())),"")

シート２　Ｄ４セル
=IF(ROW()-3<=COUNT(Sheet1!$B$6:$B$19)-SUMPRODUCT((Sheet1!$A$6:$A$19="タイ")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="韓国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="中国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="香港")*(Sheet1!$B$6:$B$19<>"")),INDEX(Sheet1!$A$1:$A$19,LARGE(INDEX((Sheet1!$B$6:$B$19<>"")*(Sheet1!$A$6:$A$19<>"タイ")*(Sheet1!$A$6:$A$19<>"韓国")*(Sheet1!$A$6:$A$19<>"中国")*(Sheet1!$A$6:$A$19<>"香港")*ROW(Sheet1!$B$6:$B$19),,),COUNT(Sheet1!$B$6:$B$19)-SUMPRODUCT((Sheet1!$A$6:$A$19="タイ")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="韓国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="中国")*(Sheet1!$B$6:$B$19<>""))-SUMPRODUCT((Sheet1!$A$6:$A$19="香港")*(Sheet1!$B$6:$B$19<>""))+4-ROW())),"")

お礼

何度も有難うございました。
大変参考になり勉強になりました。
おっしゃるようにかなり長い式になるというのには驚きました。
補助セル、マクロを上手く活用したいと思います。

回答No.4

Wendy02

こんばんは。Wendy02です。

一応、この先のことを考えて、マクロにしてしまいました。
現在の段階では、追加モードになっています。おそらく、どこかに日付が入るのではないかと思いますが、もし、それが入る場合は、同じ日付は排除したり、合算したりすることも可能です。

Sub SplitData()
　'2619166.00
　Dim Src1 As Range
　Dim c As Variant
　Dim Sh1 As Worksheet
　Dim Sh2 As Worksheet
　Dim Sh3 As Worksheet
　Dim i As Long
　Dim j As Long
　Const START As Integer = 4 '４行目スタート
　'シート名
　Set Sh1 = Worksheets("Sheet1")
　Set Sh2 = Worksheets("Sheet2")
　Set Sh3 = Worksheets("Sheet3")

　With Sh1
　　Set Src1 = .Range("D6", .Range("D65536").End(xlUp)).Resize(, 3)
　End With
　For Each c In Src1.Columns(1).Cells

　　Select Case VBA.Trim(c.Value)
　　　Case "東京", "横浜", "福岡"
　　　　If Sh2.Cells(65536, 2).End(xlUp).Row < 4 Then
　　　　　i = 0 '更新モード
　　　　Else
　　　　　i = Sh2.Cells(65536, 2).End(xlUp).Row - START + 1 '追加モード
　　　　End If
　　　　Sh2.Cells(4, 2).Offset(i).Value = c.Offset(, 1).Value
　　　　Sh2.Cells(4, 3).Offset(i).Value = c.Value
　　　　i = i + 1
　　　Case "大阪", "宮崎", "新潟", "秋田"
　　　　If Sh3.Cells(65536, 2).End(xlUp).Row < 4 Then
　　　　　j = 0 '更新モード
　　　　Else
　　　　　j = Sh3.Cells(65536, 2).End(xlUp).Row - START + 1 '追加モード
　　　　End If

　　　　Sh3.Cells(4, 2).Offset(j).Value = c.Offset(, 2).Value
　　　　Sh3.Cells(4, 3).Offset(j).Value = c.Value
　　　　j = j + 1
　　End Select
　Next c
　Set Src1 = Nothing
　Set Sh1 = Nothing: Set Sh2 = Nothing: Set Sh3 = Nothing
End Sub

お礼

Wendy02さん
お礼が大変遅くなった事をお詫び致します。
で、内容なんですが過去（前回のマクロの分は除く）いろいろと
ここで質問した計算式はすべて同じブックで使用しています。
シートは異なりますが同じファイルです。
だからという訳ではないですが出来れば計算式で対応出来ればと
思います。
質問の中にその事を書けばよかったのですが私がうっかりしていた
もので大変お手数をお掛け致しました。
出来れば式を用いた方法を御教授下さい。
宜しくお願い致します。

回答No.3

imogasi

#1のご回答の式に圧倒されますが
自称imogasi方式で実現できます。
ーーー
ただ関数ではなく、ＶＢＡを勉強されることをお勧めします。
単純なロジックで上例は実現できますから。
ーーー
自称imogasi方式ですが、判りやすくは、なると思いますが、作業列を、上例でＥ列用１列、Ｆ列用１列使わなければなりません。
ーー
Ｄ６：Ｆ１２に
Ｄ列　　Ｅ列　　Ｆ列
東京 600
大阪 　　 700
福岡 800
宮崎 　　 300
横浜 900
タイ 100
横浜 400
Ｇ６に
=IF(E6="","",MAX($G$5:G5)+1)
と入れて下に式を複写します。
Ｓｈｅｅｔ２に行って
Ｓｈｅｅｔ２のＡ１に
=INDEX(Sheet1!$D$6:$F$12,MATCH(ROW(),Sheet1!$G$6:$G$12,0),2)
と入れて下方向に式を複写します。
Ｂ１には
=INDEX(Sheet1!$D$6:$F$12,MATCH(ROW(),Sheet1!$G$6:$G$12,0),1)
と入れて下方向に式を複写します。
結果
600 東京
800 福岡
900 横浜
100 タイ
400 横浜
#N/A
Ｓｈｅｅｔ３も同じ理屈でＨ列に連番を振って処理します。
＃Ｎ／Ａを出さないようにする方法は、略しますが、Ｇｏｏｇｌｅで「imogasi方式」で照会し、他のＯＫＷＡＶＥの質問の私の回答を見てください。

お礼

お礼が大変遅くなった事をお詫び致します。
参考にさせて頂きます。
有難うございました。

回答No.2

zap35

＃０１です
タイ、香港、韓国、中国が入った場合は無視して反映させない条件が抜けていましたね。後で再掲します

お礼

お礼が大変遅くなった事をお詫び致します。
有難うございました。

＞タイ、香港、韓国、中国が入った場合は無視して反映させない条件が抜けていましたね。後で再掲します

またお手すきの際にでもアドバイス頂ければ幸いです。

回答No.1

zap35

各セルにペーストして下方向にコピーしてください
原理は全部一緒なので、式を理解すればいくらでも応用は利きます。
無理と思わないでがんばってみてください。

シート２　Ｂ４セル
=IF(ROW()-3<=COUNT(Sheet1!$B$6:$B$19),INDEX(Sheet1!$B$1:$B$19,LARGE(INDEX((Sheet1!$B$6:$B$19<>"")*ROW(Sheet1!$B$6:$B$19),,),COUNT(Sheet1!$B$6:$B$19)+4-ROW())),"")

シート２　Ｄ４セル
=IF(ROW()-3<=COUNT(Sheet1!$B$6:$B$19),INDEX(Sheet1!$A$1:$A$19,LARGE(INDEX((Sheet1!$B$6:$B$19<>"")*ROW(Sheet1!$B$6:$B$19),,),COUNT(Sheet1!$B$6:$B$19)+4-ROW())),"")

シート３　Ｂ４セル
=IF(ROW()-3<=COUNT(Sheet1!$C$6:$C$19),INDEX(Sheet1!$C$1:$C$19,LARGE(INDEX((Sheet1!$C$6:$C$19<>"")*ROW(Sheet1!$C$6:$C$19),,),COUNT(Sheet1!$C$6:$C$19)+4-ROW())),"")

シート３　Ｄ４セル
=IF(ROW()-3<=COUNT(Sheet1!$C$6:$C$19),INDEX(Sheet1!$A$1:$A$19,LARGE(INDEX((Sheet1!$C$6:$C$19<>"")*ROW(Sheet1!$C$6:$C$19),,),COUNT(Sheet1!$C$6:$C$19)+4-ROW())),"")