[[20190519192702]]　『同一行の重複を除くデータを抽出する方法』（みや）　ページの最後に飛ぶ

『同一行の重複を除くデータを抽出する方法』（みや）

いつも拝見し参考にさせていただいてます。
初めて投稿させていただきます。
同一行の重複を除くデータを抽出する方法について、ご教示いただければ幸いです。
セルに次のように入力しています。
A1は、a
B1は、a
C1は、c
D1は、空白
E1は、e
この場合において、
F1に、A1:E1範囲の空白以外のセルで、重複を除くもののうち、左から1番目を抽出。(つまり、a)
G1に、A1:E1範囲の空白以外のセルで、重複を除くもののうち、左から2番目を抽出。(つまり、c)
H1に、A1:E1範囲の空白以外のセルで、重複を除くもののうち、左から3番目を抽出。(つまり、e)
以上の操作を行う場合、F1～H1にはそれぞれどのような関数を入力すると良いでしょうか。
初歩の質問で恐縮ですが、よろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 F1:
=IFERROR(INDEX($A1:$E1,,SMALL(IF(COUNTIF(OFFSET($A1:$E1,,,,COLUMN($A1:$E1)),$A1:$E1)=1,COLUMN($A1:$E1)),COLUMN(A1))),"")
 Ctrl + Shift + Enter (配列数式入力) で確定後、右方向にコピー
（seiya） 2019/05/19(日) 20:13

▶seiyaさん
素早く分かりやすい返信ありがとうございます。
私が質問させていただいた例をそっくりそのまま入力実行してみると、
seiyaさんの数式できちんと値を求めることができました。
しかしながら、いざ、実際のデータに数式を入れてみたところ、
「数式を計算できません。開いているブックに循環参照がありますが、この場合、循環参照は表示されません。」
とメッセージが表示され、エラーとなってしまいました。
これは、質問の例でA1からE1にあたるセルの値を、他のシートから引用しているからでしょうか。
何か解決策がありましたらご教示願います。
拙い例を示してしまい申し訳ありませんでした。

（みや） 2019/05/19(日) 23:00

 A1:E1に入力されている数式の問題だと思いますが？
（seiya） 2019/05/20(月) 07:58

seiyaさん
返信いただきありがとうございます。
A1:E1(sheet1)には、次のような数式を入れています。

A1=sheet2!A1
B1=sheet3!A1
C1=sheet4!A1
D1=sheet5!A1
E1=sheet6!A1

また、sheet2からsheet6のA1には、次のような数式を入れています。

A1=IF(B1=1,a,IF(B1=2,b,IF(B1=3,c,IF(B1=4,d,IF(B1=4,e,"")))))

sheet1のA1:E1が他のsheetのセルの値により変動するため循環参照になるのでしょうか。
関数でなくマクロで解決した方が早い場合は、どうかマクロのコードをお示しいただけないでしょうか。

（みや） 2019/05/20(月) 08:41

 再現してみましたが、此方ではそのようなエラーは出ません。
 数式は下記に変更する必要がありますが(みなし空白に対応)

 =IFERROR(INDEX($A1:$E1,,SMALL(IF(($A1:$E1<>"")*(COUNTIF(OFFSET($A1:$E1,,,,COLUMN($A1:$E1)),$A1:$E1)=1),COLUMN($A1:$E1)),COLUMN(A1))),"")

 VBAなら

 Sub test()
     Dim x
     With Sheets("sheet1")
         .Range("g1:k1").ClearContents
         x = Filter(.[if((a1:e1<>"")*(countif(offset(a1:e1,,,,column(a1:e1)),a1:e1)=1),a1:e1)], False, 0)
         .Range("g1").Resize(, UBound(x) + 1).Value = x
     End With
 End Sub
（seiya） 2019/05/20(月) 09:45

 ＞質問の例でA1からE1にあたるセルの値を

 実際は A1:E1 じゃないんでしょ？

 ・実際の範囲
 ・それに合わせて変更した数式
 これを提示してください。

 式が正しく修正されていないので循環参照になるんだと思いますよ。

 ところで
 ＞D1は、空白
 ＞D1=sheet5!A1

 Sheet5のA1はどうなってるんですか？
 何も入力されていないのなら、D1は「空白」にはなりませんよね？

 以上、確認まで
（笑） 2019/05/20(月) 10:02

 Sheet2～Sheet6のA1には数式が入ってるんですね。
 上の「ところで」以降は撤回します。無視してください。すみません。

 以上、訂正とお詫びでした
（笑） 2019/05/20(月) 10:17

＞seiya様

返信が遅くなり大変申し訳ありません。
理由はわかりませんが、VBAを使用するときちんと反映されました。
本当に助かりました。ありがとうございます。

五月雨のお願いで申し訳ないのですが、
教えていただいたVBAの操作を、次のように繰り返すコードをご教示いただけないでしょうか。

1行目　A1～E1の値を、G1～K1に左詰めで反映（空白セル・値が重複するセルを除く）
2行目　A2～A2の値を、G2～K2に左詰めで反映（空白セル・値が重複するセルを除く）
・
・
100行目　A100～A100の値を、G100～K100に左詰めで反映（空白セル・値が重複するセルを除く）

以上となります。
基本的な質問と思われますが、ご容赦ください。
ネットを調べながら教えていただいたコードに加筆を繰り返しましたが、
完成に至らず、改めてお願いさせていただきました。
なにとぞお願いいたします。

＞笑様
こちらこそ説明不足で的を得ないをしてしまい大変申しわけありません。
次回の質問から実際のデータと同じ範囲、数式をお示しするよう気を付けます。

（みや） 2019/05/20(月) 20:01

 いずれにしろ、循環参照になるような数式があるようなら改善するべきです。

 Sub test()
     Dim r As Range, e, x
     With Sheets("sheet1")
         .Columns("g:k").ClearContents
         For Each r In .Range("a1", .Range("a" & Rows.Count).End(xlUp))
            e = r.Resize(, 5).Address
            x = Filter(.Evaluate("if((" & e & "<>"""")*(countif(offset(" & _
                e & ",,,,column(" & e & "))," & e & ")=1)," & e & ")"), False, 0)
            If UBound(x) > -1 Then r(, 7).Resize(, UBound(x) + 1).Value = x
         Next
     End With
 End Sub
（seiya） 2019/05/20(月) 20:58

 で、実際の範囲はどこなんですか？

 循環参照になったのは
 ＞COLUMN($A1:$E1)
 ↑ の部分を変更したからだと思いますよ。

 ＞2行目　A2～A2の値を、
 ＞100行目　A100～A100の値を、

 A2～E2、A100～E100 の間違いだとして、数式案

 A1:E100 の空白は、すべて数式の ""　という前提で

 G1 =IFERROR(INDEX($A1:$E1,SMALL(IF(($A1:$E1<>"")*(MATCH($A1:$E1,$A1:$E1,0)={1,2,3,4,5}),{1,2,3,4,5}),COLUMN(A1))),"")

 Ctrl＋Shift＋Enter で確定し、K1セルまでコピー、そのまま下に100行目までコピー
　
【重要】範囲がどこであっても　COLUMN(A1) の「A1」を変更しないように

 ■ついでに ↓ の式ですが
 ＞=IF(B1=1,a,IF(B1=2,b,IF(B1=3,c,IF(B1=4,d,IF(B1=4,e,"")))))

 =IF(B1=1,"a",IF(B1=2,"b",IF(B1=3,"c",IF(B1=4,"d",IF(B1=4,"e","")))))　の間違いだとして
 B1には「1、2、3、4」以外は入力しないのなら
 ↓ でも同じことです。

 =IF(B1="","",CHOOSE(B1,"a","b","c","d"))

 以上、参考まで
（笑） 2019/05/20(月) 23:02

＞seiya様
コードをお示しいただきありがとうございます。
本日は、所用でPCから離れていますので、明日、操作しご報告します。

＞笑様
間違いを指摘いただきありがとうございます。お見込みのとおりです。
また、問題の趣旨を考えての説明をいただき、誠にありがとうございます。
実際の範囲は、
D3:AV3
です。この中のセルの値を、空白のセル・値が重複するセルを除いて、
同一行のAX3から右方向に左詰で貼り付けたいと考えています。
お示しいただいた数式については、本日所用でPCから離れていますので、
明日、操作しご報告します。
（みや） 2019/05/20(月) 23:32

 ＞実際の範囲は、 
 ＞D3:AV3

 セルの数も全然違うじゃないですか。
 前回提示の式は、あくまでセルの数は5個、という前提ですから、試してもムダですよ。
 本当にその範囲に　=Sheet2!A1　みたいな式が入ってるんですか？

 一応、数式を書いておきます。

 AX3 =IFERROR(INDEX(3:3,SMALL(IF(($D3:$AV3<>"")*(MATCH($D3:$AV3&"",$D3:$AV3&"",0)=COLUMN($D$3:$AV$3)-3),COLUMN($D$3:$AV$3)),COLUMN(A1))),"")

 Ctrl＋Shift＋Enter で確定し、右・下コピー

 参考まで
（笑） 2019/05/21(火) 00:33

笑様

こちら側の説明不足で二度手間となり大変申し訳ありません。
本来なら回答なしで当然のところ、改めて数式を示していただきありがとうございます。
D3:AV3の数式と、下行の数式につきましては次のとおりです。
D3=IF(sheet2!D3="","",sheet2!D3)/E3=IF(sheet3!D3="","",sheet3!D3)/F3=IF(sheet4!D3="","",sheet4!D3)・・AV3(略)
D4=IF(sheet2!D4="","",sheet2!D4)/E4=IF(sheet3!D4="","",sheet3!D4)/F4=IF(sheet4!D4="","",sheet4!D4)・・AV4(略)
：
D237(略)・・AV237(略)

笑様のご指摘により、実際の範囲、数式を正確に示すことの大切さが分かりました。
本当にありがとうございます。
明日、PCを操作しまたご報告します。

（みや） 2019/05/21(火) 01:08

笑様

大変お世話になっております。
お示しいただいた数式を、AX3に入力しコピーしたところ、すべてのセルに反映し目的の操作を達成できました。本当に助かりました。
厚くお礼申し上げます。誠にありがとうございました。
また、私の拙い説明のため貴重な時間を浪費してしまい、深くお詫び申し上げます。
今後、質問させていただく際は、実際のデータを正確に示させていただきます。

seiya様

大変お世話になっております。
笑様への返信にありますとおり、目的の操作は達成しましたが、seiya様に教えていただいたコードを用いて操作した結果について（私が的外れな操作をしていると存じますが）以下のとおり報告させていただきます。
時間の許すときに見ていただければ幸いです。
また、このたびは、私の至らない説明のせいで何度もお手間をお掛けし申し訳ありませんでした。
貴重な時間を浪費してしまい、深くお詫び申し上げます。

.Columns("g:k").ClearContents　→　.Columns("AX:CP").ClearContents
For Each r In .Range("a1",　→　For Each r In .Range("d3",
.Range("a" & Rows.Count)　→　.Range("d" & Rows.Count)
If UBound(x) > -1 Then r(, 7).　→　If UBound(x) > -1 Then r(, 47).

上記の変更をしたところ、部分部分で反映しましたが、全体としては、反映しない部分が残りました。
理由については、私にはわかりませんでした。
今後、VBAの勉強を進め、理解できるよう努めます。
（みや） 2019/05/21(火) 09:55

 一言、

 「ここまで嘘八百並べ立てられたのは初めてで、まるで詐欺にでもあったみたいだ。」

 >理由については、私にはわかりませんでした。
 ネットで調べても無理だと思う。
 変更した部分に
 e = r.Resize(, 5).Address
 が含まれていない。

（seiya） 2019/05/21(火) 12:26

seiya様

大変申し訳ございません。
安易な発想で質問を重ねた結果、二度手間三度手間になり、
不快な思いをさせてしまったことにつきまして、重ね重ね深くお詫び申し上げます。
今後は、安易な発想に至らぬよう、初歩からVBAを勉強したいと思います。
大変失礼いたしました。ご対応いただきましてありがとうございました。

（みや） 2019/05/21(火) 12:39

コメント返信：

[ 一覧(最新更新順) ]

[[20190519192702]] 『同一行の重複を除くデータを抽出する方法』（みや） ページの最後に飛ぶ

[[20190519192702]]　『同一行の重複を除くデータを抽出する方法』（みや）　ページの最後に飛ぶ