エクセル 新たな名前を入れたら別のセルに自動で表示される方法

例えば
Ａ　Ｂ　Ｃ　Ｄ　Ｈ
猫　犬　象　鳥　猫2
犬　猫　鳥　鳥　犬4
象　犬　犬　　　象2
　　　　　　　　鳥3

上の表のD4に亀と入れると、鳥の下に亀と表示され、亀の数も計算されるように作りたいです。
今のところ、カウント数は、猫の場合は　=COUNTIF(A1:D90,"猫")　というような数式を使っています。
宜しくお願いいたします。

No.4 ベストアンサー 回答者： tom04 回答日時： 2017/04/27 17:02

何度もごめんなさい。

No.2の数式でH4セルの数式が間違っていました。
=IFERROR(INDEX(B:F,INT(SMALL(Sheet2!A:E,ROW(A1))/1000),MOD(SMALL(Sheet2!A:E,ROW(A1)),1000)),"")

にしてください。
どうも失礼しました。m(_ _)m

この回答へのお礼

ご丁寧にありがとうございます！すごいですね！大変助かりました。ありがとうございました。

お礼日時：2017/04/28 10:18

No.3 回答者： tom04 回答日時： 2017/04/27 16:38

続けてお邪魔します。

この際ですので、同一行に重複があっても対応できるVBAも投稿しておきます。
もちろん作業用Sheetは不要です。

画面左下にある、Sheet1のシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
B～F列のデータを色々変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
Dim j As Long, maxRow As Long, lastRow As Long
Dim cnt As Long, c As Range, r As Range
If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub
If Target.Row > 3 Then
lastRow = Cells(Rows.Count, "H").End(xlUp).Row
If lastRow > 3 Then
Range(Cells(4, "H"), Cells(lastRow, "H")).ClearContents
End If
For j = 2 To 6
maxRow = WorksheetFunction.Max(maxRow, Cells(Rows.Count, j).End(xlUp).Row)
Next j
If maxRow > 3 Then
cnt = 3
For Each c In Range(Cells(4, "B"), Cells(maxRow, "F"))
If c <> "" Then
Set r = Range("H:H").Find(what:=c, LookIn:=xlValues, lookat:=xlWhole)
If r Is Nothing Then
cnt = cnt + 1
Cells(cnt, "H") = c
End If
End If
Next c
End If
End If
End Sub '//この行まで//

※　I列はそのまま数式で行けますので、I4セルの数式を
=IF(H4="","",COUNTIF(B:F,H4))
としてフィルハンドルで下へコピーしておいてください。m(_ _)m

No.2 回答者： tom04 回答日時： 2017/04/27 16:02

No.1です。

＞B4セルからスタートして、列はＦまでの横5列にした場合は・・・

作業用SheetのA4セル（←B4セルではありません）に↓の数式を入れてください。
=IF(COUNTIF(Sheet1!$B$4:$F4,Sheet1!B4)=1,ROW()*1000+COLUMN(),"")

A4セルのフィルハンドルでE4セルまでコピー　→　そのまま下へずぃ～～～！っとコピーしておき、
Sheet1のH4セルに
=IF(COUNTIF(Sheet1!$B$4:$F4,Sheet1!B4)=1,ROW()*1000+COLUMN(),"")
という数式を入れ下へコピーしてみてください。

※　I列（COUNTIF関数）は大丈夫ですよね。

※　今までの方法は同じ行に重複するデータはない！というコトが前提でした。
もし、同一行に同じデータが出現する場合は、関数ではかなり厄介です。
VBAであれば可能です。m(_ _)m

No.1 回答者： tom04 回答日時： 2017/04/27 14:29

こんにちは！

VBAなら一気にできそうですが・・・
関数でやってみました。一案です。
↓の画像のように別シート（画像はSheet2）を作業用として使ってみてはどうでしょうか？

Sheet2のA1セルに
=IF(COUNTIF(Sheet1!$A$1:$D1,Sheet1!A1)=1,ROW()*1000+COLUMN(),"")
という数式を入れ列（Sheet1と同じデータ列まで）・行方向（下へずぃ～～～！っとこれ以上データはない！というくらいまで）コピーしておきます。

そしてSheet1のH1セルに
=IFERROR(INDEX(A:D,INT(SMALL(Sheet2!A:D,ROW(A1))/1000),MOD(SMALL(Sheet2!A:D,ROW(A1)),1000)),"")

I1セルに
=IF(H1="","",COUNTIF(A:D,H1))

という数式を入れフィルハンドルで下へコピーすると
画像のような感じになります。

※　出現順は上の行からの順になります。m(_ _)m

この回答へのお礼

すごいです！ありがとうございます！
Sheet1のスタート位置をA1ではなく、B4セルからスタートして、列はＦまでの横5列にした場合は数式のどこを何の値に変更したら宜しいでしょうか。
ド素人で申し訳ございませんが宜しくお願いいたします。

　Ｂ　Ｃ　Ｄ　E　　　 Ｈ　Ｉ
4 猫　犬　象　鳥　　　猫　2
5 犬　猫　鳥　鳥　　　犬　4
6 象　犬　犬　　　　　象　2
　　　　　　　　　　　鳥　3

お礼日時：2017/04/27 15:04