スキルアップ・問題解決なら、モーグにおまかせ！

こんにちは
よろしくお願いいたします
 
Sheet1
氏名
田中
佐藤
飯田
田中
 
Sheet2
氏名
遠藤
佐藤
飯田
和田
近藤
とほかにもシートがあります
それぞれの人数はシートごとに違います
重複を除いてすべてのシートの人数を求めたいですが、データ上、パワークエリが使えません。
また、データは追加されたり変更されたりするので数式でなんとかしたいです。
それで、
=COUNTA(UNIQUE(Sheet1!A2:A5,Sheet2!A2:A5) としてみましたができません
UNIQUEは、複数シートは指定できないようですので
どうしたら、上記の場合だったら、6名と求められますでしょうか？
よろしくお願いいたします。

数式ではありませんが
「統合」というコマンドを紹介します。
 
事前準備
　各シートのＢ列セルに数値「0」でも「1]値（そのものは意味がない）
手順
集計値を表示するシートの任意のセルを選択して
「データ」タブの「統合」をクリックします。
ダイアログで
「集計の方法」：個数
「統合元範囲」：
Sheet1!$A:$B　入力後「追加」
Sheet2!$A:$B　入力後「追加」
必要なだけ
 
「統合の基準」：「左」
OK
 
データの増減があったら、「統合」ダイアログの内容を確認して「OK」

WinArroｗ様
 
ありがとうございました！
項目ごとの人数はでました。
あとはかぞえたらいいんのですね。
 
じつは、
データの追加や変更がしょっちゅうあるので、
数式で一度に求められる方法を知りたいです。
よろしくお願いいたします。

=COUNTA(UNIQUE(FILTERXML("<y><x>"&TEXTJOIN("</x><x>",TRUE,Sheet1:Sheet2!A2:A100)&"</x></y>","//x")))　

=FILTERXML("<y><x>"&TEXTJOIN("</x><x>",TRUE,範囲)&"</x></y>","//x")
が複数の範囲をひとまとめにする式です。いろいろ試してみてください。

んなっと様
 
ありがとうございます！
XMLはわからないので、どうしても"<y><x>"の意味がわからなくて。。。
申し訳ございません。。
XMLを使わずに求める数式はございますでしょうか？
いつも申し訳ございません<(_ _)>

数式で対応するということは、
１件入力毎に、リアルタイムで、数式が働くということです。
１００件入力が発生すると、同じ計算が１００回働くということです。
つまり、その分レスポンスが悪くなります。（入力作業の足をひっぱる可能性がある）
 
集計作業は、１回だけです。
 
そのあたりも考えて、決めるとよいと思います。
 
 

Sheet1,Sheet2の2枚限定で、こんなのもあります。
=COUNTA(UNIQUE(LET(s,SEQUENCE(300),x,Sheet1!A2:A100,IFERROR(IF(s<=ROWS(x),x&"",INDEX(Sheet2!A2:A100,s-ROWS(x))&""),""))))-1
お望み通りPowerQueryもXMLも使わない方法です。
  
将来はVSTACK関数やTOCOL関数とかいうのが使えるようになるかもしれません。楽しみですね。

WinArrow様
ありがとうございました！
 
んなっと様
ありがとうございました！
すばらしい数式です！ありがとうございます！
シートはあと5枚あり、これからも増えていくのですが、
この数式のどこかを変更することで可能でございましょうか？、

数式で対応する場合、
シートの増減が発生すると、数式の変更が必須になります。
「統合」を使っても、統合元の変更が必須になります。
 
シートの増減に自動対応するには、ＶＢＡが必要です。
存在する全てのシートのＡ列のみを対象に
重複を除いた件数を取得するＶＢＡを紹介します。

Sub 重複を除く件数()
Dim Sht As Worksheet, myCell As Range
Dim myDic As Object

    Set myDic = CreateObject("Scripting.Dictionary")
    With ThisWorkbook
        For Each Sht In .Sheets
            For Each myCell In Sht.Range("A1").CurrentRegion
                If myCell.Value <> "氏名" Then
                    If Not myDic.exists(myCell.Value) Then
                        myDic.Add myCell.Value, myCell.Value
                    End If
                End If
            Next
        Next
    End With
    
    MsgBox myDic.Count
    Set myDic = Nothing
End Sub

もしかしたらこれもいけるかもしれません。
=COUNTA(UNIQUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE("|"&TEXTJOIN("|",TRUE,Sheet1:Sheet5!A$2:A$100),"|",REPT(" ",1000),SEQUENCE(500)+1),"|",REPT(" ",1000),SEQUENCE(500)),1000,1000))))-1
 32767文字の上限には引っかからないと思いますが...
 
 
それから、最初の回答の
=COUNTA(UNIQUE(FILTERXML("<y><x>"&TEXTJOIN("</x><x>",TRUE,Sheet1:Sheet2!A2:A100)&"</x></y>","//x")))
でほぼ完了しているので、それ以降のやりとりは蛇足です。
理解できないとかはそちらの問題。自分で努力してください。

WinArrow様
 
VBAを考えていただき、誠にありがとうございました！
私の会社では社員しかVBAの利用は規則で禁止されておりまして
パートの私では使えず。。
最初にお伝えすべきでした、せっかく記述を考えていただいたいのに
大変申し訳ございません(>_<)
色々と考えて頂き本当にありがとうございました<m(__)m>
 
 
んなっと様
 
ありがとうございます！
この数式を入れてやってみます！！
本当に助かります、ありがとうございました！
XML、初歩から勉強しようとしているのですが、
どうにもわからずじまいで、毎回、ご迷惑をお掛けして
大変申し訳ございません<m(__)m>
色々とありがとうございました！
、