[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『IF関数を使用して重複せず表示』（関数初心者）
いつもお世話になっております。
仕事で、IF関数を使用して重複せず表示をさせたいのですが、
どうしても上手くいかないのでどなたかご教示頂けませんでしょうか。
１ファイルの中に複数のSheetがあり、
１〜３Sheetのいずれかに入力がされていたらIF関数で「集約Sheet」に
表示させようと考えております。（最大４名分）
「１Sheet」は、最大１人のデータが入力されます。
「２Sheet」は、最大３人のデータが入力されます。
「３Sheet」は、最大２人のデータが入力されます。
表示例
パターン１「１Sheet」もしくは「２Sheet」もしくは「３Sheet」に１名分の
データがいずれかに入力されていたら、１番上に表示させます。
パターン２「１Sheet」に１名分のデータの入力があり、１番上に表示し、
「２Sheet」に３名分のデータが入力されていたら、
２番目・３番目・４番目に表示
パターン３「１Sheet」に１名分のデータの入力があり、１番目に表示し、
「２Sheet」に２名分のデータが入力されていたら、
２番目・３番目に表示
「３Sheet」に１名分のデータの入力があり、４番目に表示
パターン４「１Sheet」は、未入力。
「２Sheet」は、２名分のデータが入力されていたら、
１番目と、２番目に表示となりますが、
３番目の表示に２番目に表示されたデータが重複して表示されます。
１番目 ○○さん
IF(１Sheet!$I$15<>"",１Sheet!$I$15,IF(２Sheet!$I$16<>"",２Sheet!$I$16,IF(２Sheet!$I$24<>"",２Sheet!$I$24,IF(２Sheet!$I$31<>"",２Sheet!$I$31,IF(３Sheet!$I$15<>"",３Sheet!$I$15,IF(３Sheet!$I$115<>"",３Sheet!$I$115,""))))))
２番目 △△さん
IF(２Sheet!$I$16<>"",２Sheet!$I$16,IF(２Sheet!$I$25<>"",２Sheet!$I$25,IF(２Sheet!$I$31<>"",２Sheet!$I$31,IF(３Sheet!$I$15<>"",３Sheet!$I$15,IF(３Sheet!$I$115<>"",?３heet!$I$115,"")))))
３番目 △△さん
IF(２Sheet!$I$24<>"",２Sheet!$I$24,IF(２Sheet!$I$31<>"",２Sheet!$I$31,IF(３Sheet!$I$15<>"",３Sheet!$I$15,IF(３Sheet!$I$114<>"",３Sheet!$I$114,""))))
拙い説明で大変申し訳ございませんが何卒ご教示を頂きたくよろしくお願い申し上げます。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
1SheetがI15セル、2SheetがI16,I24,I31セル、3SheetがI15,I115セルに値があるとする。
集計シートのD1セルからD3セルを作業セルとして使い
D1セル:=COUNTA('1Sheet'!I15)
D2セル:=COUNTA('2Sheet'!I16,'2Sheet'!I24,'2Sheet'!I31)
D3セル:=COUNTA('3Sheet'!I15,'3Sheet'!I115)
と入力しておき
=IF(ROW(A1)<=D$1,'1Sheet'!I$15,IF(ROW(A1)<=D$1+D$2,CHOOSE(ROW(A1)-D$1,'2Sheet'!I$16,'2Sheet'!I$24,'2Sheet'!I$31),IF(ROW(A1)<=D$1+D$2+D$3,CHOOSE(ROW(A1)-D$1-D$2,'3Sheet'!I$15,'3Sheet'!I$115),"")))
と開始セルに入力して下へフィルコピーではどうか？
なお、式中で各シートの個数を数えて
=IF(ROW(A1)<=COUNTA('1Sheet'!I$15),'1Sheet'!I$15,IF(ROW(A1)<=COUNTA('1Sheet'!I$15,'2Sheet'!I$16,'2Sheet'!I$24,'2Sheet'!I$31),CHOOSE(ROW(A1)-COUNTA('1Sheet'!I$15),'2Sheet'!I$16,'2Sheet'!I$24,'2Sheet'!I$31),IF(ROW(A1)<=COUNTA('1Sheet'!I$15,'2Sheet'!I$16,'2Sheet'!I$24,'2Sheet'!I$31,'3Sheet'!I$15,'3Sheet'!I$115),CHOOSE(ROW(A1)-COUNTA('1Sheet'!I$15,'2Sheet'!I$16,'2Sheet'!I$24,'2Sheet'!I$31),'3Sheet'!I$15,'3Sheet'!I$115),"")))
でも。 こちらはD1セルからD3セルの作業セルは使わない。 （ねむねむ） 2015/10/26(月) 09:43
関数初心者の私もトライしてみました。 こんなのはどうでしょうか。作業セル使いまくりですが。 ごく基本的な関数かと思います。
【前提】 ・Sheet1のA15 ・Sheet2のA16,A24,A31 ・Sheet3のA15,A115 にデータが入るとします。（notationはこれで勘弁して下さい。１Sheetとかは感覚的に受け付けないので）
【集約シートの作成】 集約Sheet には次のように計算式を埋め込みます。 C列に求める結果が表示されるはずです。
A列 B列 C列
1 =COUNTIF($B$1:B1,"<>0") =Sheet1!A15 =IFERROR(VLOOKUP(ROW(A1),$A$1:$B$6,2,FALSE),"")
2 =COUNTIF($B$1:B2,"<>0") =Sheet2!A16 =IFERROR(VLOOKUP(ROW(A2),$A$1:$B$6,2,FALSE),"")
3 =COUNTIF($B$1:B3,"<>0") =Sheet2!A24 =IFERROR(VLOOKUP(ROW(A3),$A$1:$B$6,2,FALSE),"")
4 =COUNTIF($B$1:B4,"<>0") =Sheet2!A31 =IFERROR(VLOOKUP(ROW(A4),$A$1:$B$6,2,FALSE),"")
5 =COUNTIF($B$1:B5,"<>0") =Sheet3!A15
6 =COUNTIF($B$1:B6,"<>0") =Sheet3!A115
・A列はA1を下にコピーして下さい。 ・C列はC1を下にコピーして下さい。
(疑問点) > パターン４「１Sheet」は、未入力。 > 「２Sheet」は、２名分のデータが入力されていたら、 > １番目と、２番目に表示となりますが、 > ３番目の表示に２番目に表示されたデータが重複して表示されます。
どうして重複して表示するのでしょうか。 こうしたいという仕様なら、なぜ重複して表示するのか、理由がわかりません。 こうなってしまって困るということ？
（γ） 2015/10/26(月) 22:12
γさん、（疑問点）につきましては、こうなってしまうと困るパターンです。
アンケート形式で入力されたデータを関数で集約Sheet（申請書）に転記したいので、
同じ人物が複数行表示されると申請書として提出できないためです。
（関数初心者） 2015/11/01(日) 05:12
コメントありがとうございました。
(1) >結果に数字が含まれていると、VALUE！のエラーが表示され、上手く表示されないようですので、
うまくいかないのはどういう例なのか、書かれてはいかがですか？ 今回の結果をさらに参照しているところの問題なんでしょうか？
(2)前提について、 各シートは、上から順にデータは入力されると考えてよいのですか？ 2SheetのI16は未入力だが、I24,I31には入力されているということはありうるのですか？
(3)もうひとつ前提について > アンケート形式で入力されたデータを関数で集約Sheet（申請書）に転記したいので、 > 同じ人物が複数行表示されると申請書として提出できないためです。 2件しかないのに3件記載されていたらダメなことは、常識的にわかりますよ。 ただし、 もし、同一人物が、Sheet1にもSheet3にもあったら、重複を除く処理が必要になるのですか？ もしそうならまた話は変わって来ますよ。その辺は正確に書いてください。
---------------------------------- ＞作業セルは作らず関数を作成したい思っておりますので、 それは何故でしょうか？ そのほうが格好が良いからですか？
私は格好など気にせず、シートは広いんですから、 分かりやすさを重視して、作業セルを使うほうが良いと思いますよ。 とりわけ、初心者を標榜されるなら。（単なるHNでしたら失礼）
そもそものシートの作りや、されようとしていることを改めて説明されると、 関数に過度に依存しない方法が提示されるかもしれませんよ。
（γ） 2015/11/01(日) 09:52
（1）につきましては、加工した関数を数式のみでコピーすると、
ROWのセル番地が自動的に変更され、valueが表示されます。
例：IF(ROW(A1)･･･ → IF(ROW(A5)･･･
アンケート形式で入力された情報を関数で拾い、集約シート（申請書）に転記します。
（転記する情報：名前・名前フリガナ・性別・生年月日・同別居等）
（２）つきましては、各シートは上から順にデータは並んでいません。
はい。未入力の箇所は存在します。
生年月日の１文字でセル番地を例えますと、
【シート１】J20,J91,J101,J111【シート２】J20,J118のいずれかにデータが入力されていたら、
上からデータを関数で拾い、空欄があったら次のセル番地へ進み、何もなければ空欄とします。
（３）同一人物はシートごとおよび同一シートには登場しないです。
入力された箇所のみ集約シート（申請書）に転記します。
例えますと、
【シート１】あさん、いさん、うさん、えさん（最大４名）今回は２名のみデータ入力と仮定
【シート２】かさん、くさん（最大２名）今回は１名のみデータ入力と仮定
【集約シート（申請書）】の転記 ※データ入力されている状態と仮定
申請者 生年月日
１行目（ｾﾙ番地:C33）あさん（エラーなし） （ｾﾙ番地:O33）元号 XXOOXX（エラーなし）
２行目（ｾﾙ番地:C37）いさん（エラーなし） （ｾﾙ番地:O37）元号 XXOOXX←#VALUE!表示
３行目（ｾﾙ番地:C41）かさん（エラーなし） （ｾﾙ番地:O41）元号 XXOOXX←#VALUE!表示
４行目（ｾﾙ番地:C45）かさん（重複で表示） （ｾﾙ番地:O45）元号 XXOOXX←#VALUE!表示
作業セルにつきましては、
体裁のためも少しはありますが、職場の同じチームに説明および引継する際に集約しておいた方が
関数を修正する際に都合がよいと思ったためです。
Excelは十数年使用しておりますがまだまだ未熟なので「関数初心者」のHNにしました。
拙い説明で大変申し訳ございませんが再度ご教示頂きたくよろしくお願い致します。
（関数初心者） 2015/11/01(日) 19:11
(1) > 例：IF(ROW(A1)･･･ → IF(ROW(A5)･･･ 本来は、1,2,3,・・・という数列を使いたいわけですが、 ROW(A1)とかしておくと、コピーペイストすることで自動的に作成できますので、 数値を入力する手間が省く意図でROW(A1)という書き方を使っているのです。
ですから、そういう意図を理解して、 必ず A1からはじまるようにしないといけません。
(2) > 未入力の箇所は存在します。 ということなら、たぶん ねむねむさんはそうした前提に立っておられないと思います。 なお、私のは、たまたまですが、そう言うケースにも対応したつもりです。
(3) > 作業セルにつきましては、 > 体裁のためも少しはありますが、職場の同じチームに説明および引継する際に集約しておいた方が > 関数を修正する際に都合がよいと思ったためです。
そういう方針は、 正直申し上げて、長い式であっても正確に解釈できること、 いくら長くても苦にならない、ということが前提になりますね。
考え方を理解し易い方法をとることも、大事なことではないかと愚考します。
（γ） 2015/11/01(日) 20:34
Option Explicit
Sub test()
Dim コピー元1 As Range, コピー元2 As Range, コピー元3 As Range
Dim 転記先 As Range
Dim c As Range
With Worksheets("Sheet1")
Set コピー元1 = .Range("A15")
End With
With Worksheets("Sheet2")
Set コピー元2 = Union(.Range("A16"), .Range("A24"), .Range("A31"))
End With
With Worksheets("Sheet3")
Set コピー元3 = Union(.Range("A15"), .Range("A115"))
End With
With Worksheets("集約シート")
Set 転記先 = Union( _
.Range("C33"), .Range("C37"), .Range("C41"), .Range("C45"), .Range("C49") _
)
End With
With CreateObject("System.Collections.Queue")
For Each c In コピー元1
If c.Value <> "" Then .Enqueue Array(c.Value, c.Offset(, 2).Value)
Next
For Each c In コピー元2
If c.Value <> "" Then .Enqueue Array(c.Value, c.Offset(, 2).Value)
Next
For Each c In コピー元3
If c.Value <> "" Then .Enqueue Array(c.Value, c.Offset(, 2).Value)
Next
For Each c In 転記先
If .Count > 0 Then
c.Value = .Peek()(0)
c.Offset(, 4).Value = .Dequeue()(1)
End If
Next
End With
End Sub
（マナ） 2015/11/02(月) 21:12
こんばんは。
> そもそものシートの作りや、されようとしていることを改めて説明されると、 > 関数に過度に依存しない方法が提示されるかもしれませんよ。 と書いたのですが、その後、説明がされたのでしょうか。
まず、これは全体としてどういう目的のものでしょうか。 > アンケート形式で入力されたデータを関数で集約Sheet（申請書）に転記したいので、 > 同じ人物が複数行表示されると申請書として提出できないためです。
(1)アンケート形式で、複数の人に記入してもらうのですか？
それが申請書になるのですか？
(2)各人は、どこに記入するのですか？
シートによって、記入できる人数が違うのはなぜですか？
(3)
1SheetがI15セル、
2SheetがI16,I24,I31セル、
3SheetがI15,I115セル
と、
シートによって、行が不揃いなのはどういう理由なんですか？
(4)一つの項目だけについて質問されていますが、項目はひとつだけではないでしょう？
少なくとも、二つ以上の例を出さないと、適切な例になっていないと思います。
一つと二つでは、根本的に問題が変わってきます。
こうした全体像をもう一度説明されたほうがよろしいかと思いますよ。
（γ） 2015/11/02(月) 22:11
全体としては健康保険に入るための加入要件を確認するファイルです。
複数名が入力するのではなく、健康保険に加入希望をしている扶養者の収入状況等を確認します。
入力された情報を健康保険組合が作成しているオリジナルの申請書に転記します。
入力後ファイルを送ってもらいこちらで内容を確認し、適宜必要な添付書類の案内をするためのファイルです。
(1)アンケート形式で、複数の人に記入してもらうのですか？ それが申請書になるのですか？ 複数ではなく、一世帯分の扶養希望者を申請者に入力をしてもらいます。
入力してもらった情報を元に関数で健康保険組合の申請書に転記します。
(2)各人は、どこに記入するのですか？ シートによって、記入できる人数が違うのはなぜですか？ 配偶者・子供用、親族用に分けて質問をします。
家族構成がそれぞれ違うので人数は特定できないです。
(3) 1SheetがI15セル、 2SheetがI16,I24,I31セル、 3SheetがI15,I115セル とシートによって、行が不揃いなのはどういう理由なんですか？ 対象者によって質問が異なっているためでしたが調整しました。
(4)一つの項目だけについて質問されていますが、項目はひとつだけではないでしょう？ 少なくとも、二つ以上の例を出さないと、適切な例になっていないと思います。 一つと二つでは、根本的に問題が変わってきます。 シート＝１名に変更しました。
【抽出データ一覧】
ﾌﾘｶﾞﾅ(氏) ﾌﾘｶﾞﾅ(名) 漢字(氏) 漢字(名) 性別 年号 生年月日(和歴) 続柄 同別居 Sheet１（配偶者） I16 O16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20 Sheet２（子1人目) I16 N16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20 Sheet３（子2人目) I16 N16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20 Sheet４（子３人目) I16 N16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20 Sheet５（親族1人目) I16 N16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20 Sheet6 （親族2人目) I16 N16 I17 N17 U16 I18 J18 L18 O18 Q18 T18 V18 AA16 I20
抽出データから申請者が入力した箇所のみを集約データに転記させたいです。
【集約データSheet】※４名のみの表示です。（4名を超えることは滅多にありません。）
ﾌﾘｶﾞﾅ(氏) ﾌﾘｶﾞﾅ(名) 漢字(氏) 漢字(名) 性別 年号 生年月日(和歴) 続柄 同別居 １行目 C19 H19 C20 H20 M19 O20 P20 Q20 R20 S20 T20 U20 V19 AA19 ２行目 C23 H23 C24 H24 M23 O24 P24 Q24 R24 S24 T24 U24 V23 AA23 ３行目 C27 H27 C28 H28 M27 O28 P28 Q28 R28 S28 T28 U28 V27 AA27 ４行目 C31 H31 C32 H32 M31 O32 P32 Q32 R32 S32 T32 U32 V31 AA31
パターン１：配偶者と子供1人目と子供2人目
パターン２：子供1人目と子供2人目
パターン３：配偶者と子供1人目とと親族1人目
以前にパターン２を試したところ、集約データシートの3・4行目のところに子供2人目が表示されました。
パターン３を試したところ、集約データシートの4行目のところに親族1人目が表示されました。
説明が長文になり申し訳ございません。
拙い説明と思いますがよろしくお願い致します。
（関数初心者） 2015/11/08(日) 20:02
（γ） 2015/11/09(月) 22:44
（γ） 2015/11/15(日) 00:18
（関数初心者） 2015/11/15(日) 00:29
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.