教えて下さいエクセルの関数を教えて下さい A行は名字で同じ名字は家族です B行は名義人は1、同居

Question

教えて下さい
エクセルの関数を教えて下さい

A行は名字で同じ名字は家族です

B行は    名義人は1、同居人は2です

C行は    単身車は1、複数は2

D行は基準年齢より、

高齢は1か若年は2

E行で家族が

高齢単身     若年単身

高齢複数     若年複数

高齢若年

を判別したいです

goomania · Accepted Answer

このご質問の関数での回答を考えるとき、EXCELの知識は当然ですが、ある意味パズルを解く力のようなEXCEL以外の力量が問われているような気がしました。
「若単」「高単」「若複」「高複」「若高」の判別を行うにあたって、当初、他の回答者さんの回答を見ても、単身者世帯の判別は比較的容易だが、若年者および高齢者の複数世帯と高齢者若年者同居世帯の判別がやっかいだと感じていました。
しかし、実際に取り組んでみると、「世帯番号」「世帯人数」を算出すれば関数でも比較的容易に判別できることが判りました。

前提条件は以下のとおりです。
①同姓人は世帯ごとにブロック化されている。
②名義人は世帯プロックの筆頭に記載されている。

判別を行う手順は以下のとおりです。

(1)以下の作業列を用意する
F列に「世帯番号」、G列に「世帯人数」、H列に「D列世帯合計」、I列に「判別記号前部」、J列に「判別記号後部」

(2)以下の数式をそれぞれに記述し、下方向へオートフィル等でコピーする
F2「=COUNTIF($B$1:$B2,1)」
G2「=COUNTIF($F:$F,$F2)」
H2「=SUMIF($F:$F,$F2,$D:$D)」
I2「=IF(($J2="高")+($D2<>1),"若","高")」
J2「=IF($G2=1,"単",IF(($H2=$G2)+($H2=2*$G2),"複","高"))」

(3)判別結果を表示するためE2に以下の数式を記述し、下方向へオートフィル等でコピーする
E2「=$I2&$J2」

これにより、E列に判定結果が表示されます。ただし、入力されているべき項目が「空欄」である等のエラーチェックは行っておりません。

※当然I列、J列は作業列なので必ずしも必要ではなく、E列にいきなり、I列とJ列を結合した数式を記述してもよいのですが、数式が冗長になるので、あえて作業列を使い、後で結合しています。

上記の複数の数式のうち、ポイントはH列に表示する「D列世帯合計」です。
「D列世帯合計」が世帯人数と一致すれば全員若年、「D列世帯合計」が世帯人数の2倍と一致すれば全員高齢という判別が可能という点です。

添付画像を参照してください。

yoreyore · Answer

NO.4です。　状況理解が間違っていたのがわかりました。
判別は、各行のデータに基づいてするのではなくて、各住居毎の居住者の状況で住居毎にするのですね。
確かに、マクロでやった方が楽そうな気がします。
12/29に【やってみる】と書かれていますね。　正月休みにでもやってみてください。
年末で時間があったもので、excelで作業領域を作って、無理矢理やってみました。　できないわけではなさそうです。
住居の【名義人が同姓同名】であっても、（B列で住居毎の区切りが分かる⇒住居居住人数も分かる）という考えで、やってみました。
やった結果、DGET関数の使い方で、｢こう作業列を使えば、利用可能性が増える｣ということがわかりました。
EXCEL関数でもできないわけではないとうためだけに、画像を添付します。

tatsumaru77 · Answer

No7です。
以下のルールで決定しています。
１．連続した苗字が続く行数を数える。
　　１行の場合、単身家族、以外は複数家族とする。
２．区分１、区分２の値は、家族構成の決定には関知しない。
３．単身家族の場合、
　　区分３が１なら、高齢単身世帯    高単
　　区分３が２なら、若年単身世帯    若単
４．複数家族の場合
　　区分３が全て１なら、高齢複数人世帯  高複
　　区分３が全て２なら、若年複数人世帯  若複
　　上記以外なら、高齢者若年者同居世帯    若高
　　とします。
５．以下のケースは正常とします。
　　家族人数が１０人を超えた場合でも正常とします。
　　1つの家族に名義人が複数あっても正常とします。
　　単身家族でありながら（苗字が１つの場合）、区分＝２、又は、区分２＝２のデータが
　　あっても、正常とします。
６．以下のデータは不正データとし、処理を行いません。
　　エラーの場合、F列にエラーの内容を表示します。
　　苗字：空白でないこと
　　区分１：１，２のいずれかであること
　　区分２：１，２のいずれかであること
　　区分３：１，２のいずれかであること

tatsumaru77 · Answer

以下のマクロを標準モジュールに登録してください。

Option Explicit

Public Sub 家族構成()
Dim sh As Worksheet 'ワークシート
Dim maxrow As Long '最大行数
Dim wrow As Long '処理中の行
Dim kbn1 As Variant '区分１
Dim kbn2 As Variant '区分２
Dim kbn3 As Variant '区分３
Dim ectr As Long 'エラーカウンター
Dim emsg As String 'エラーメッセージ
Dim name As String '苗字
Dim prev_name As String '前回苗字
Dim st_row As Long '家族の開始行
Dim en_row As Long '家族の終了行
Set sh = ActiveSheet
maxrow = sh.Cells(Rows.Count, 1).End(xlUp).row 'sheetの最大行取得
If maxrow < 2 Then Exit Sub
sh.Range("E2:F" & maxrow).Value = ""
ectr = 0
'データのチェック
For wrow = 2 To maxrow
emsg = ""
kbn1 = sh.Cells(wrow, "B").Value
kbn2 = sh.Cells(wrow, "C").Value
kbn3 = sh.Cells(wrow, "D").Value
If sh.Cells(wrow, "A").Value = "" Then
emsg = "苗字空白 "
End If
If kbn1 <> 1 And kbn1 <> 2 Then
emsg = emsg + "区分1 "
End If
If kbn2 <> 1 And kbn2 <> 2 Then
emsg = emsg + "区分2 "
End If
If kbn3 <> 1 And kbn3 <> 2 Then
emsg = emsg + "区分3 "
End If
If emsg <> "" Then
sh.Cells(wrow, "F").Value = emsg
ectr = ectr + 1
End If
Next
If ectr <> 0 Then
MsgBox (ectr & "行分のデータが誤っています")
Exit Sub
End If
'データの処理
prev_name = ""
For wrow = 2 To maxrow
name = sh.Cells(wrow, "A").Value
'前回と苗字が異なる場合
If name <> prev_name Then
'最初でないなら家族構成を出力
If prev_name <> "" Then
Call output(sh, st_row, en_row)
End If
prev_name = name
st_row = wrow '家族の開始行記憶
End If
en_row = wrow '家族の終了行記憶
Next
'家族構成を出力
Call output(sh, st_row, en_row)
MsgBox ("処理終了")
End Sub

Private Sub output(ByVal sh As Worksheet, ByVal st_row As Long, ByVal en_row As Long)
Dim dctr As Long
Dim msg As String
Dim wrow As Long
Dim highP As Long
Dim lowP As Long
'高齢者と若年者をカウントする
highP = 0
lowP = 0
dctr = en_row - st_row + 1
For wrow = st_row To en_row
If sh.Cells(wrow, "D").Value = 1 Then
highP = highP + 1
End If
If sh.Cells(wrow, "D").Value = 2 Then
lowP = lowP + 1
End If
Next
'単身家族の場合
If dctr = 1 Then
If highP = 0 Then
msg = "若単"
Else
msg = "高単"
End If
'複数家族の場合
Else
If highP = 0 Then
msg = "若複"
ElseIf lowP = 0 Then
msg = "高複"
Else
msg = "若高"
End If
End If
'家族の人数分、構成をセット
For wrow = st_row To en_row
sh.Cells(wrow, "E").Value = msg
Next

End Sub

tatsumaru77 · Answer

世帯人数が１名の場合、例ではA列がｂのケースです。
区分１＝１（名義人）、区分２＝１（単身）になるはずですが
区分１＝２（同居者）とか
区分２＝２（同居者有）のケースは、エラー扱いするのですか、
それとも、正常扱いにするのでしょうか。

tatsumaru77 · Answer

マクロで良いなら提供可能ですが、いかがでしょうか。
あなたのパソコンのexcelがマクロが実行可能になっていることが必要です。
そして、マクロの登録方法を理解していることが必要です。
下記URLを読んで、OKなら、その旨、補足してください。
https://www.tipsfound.com/vba/01004