Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Microsoft 365)
複数シートの重複除く集計
投稿日時: 22/06/19 11:09:55
投稿者: toma100

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

回答
投稿日時: 22/06/19 11:25:50
投稿者: WinArrow
投稿者のウェブサイトに移動

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

投稿日時: 22/06/19 11:54:51
投稿者: toma100

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

回答
投稿日時: 22/06/19 12:40:01
投稿者: んなっと

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

回答
投稿日時: 22/06/19 13:42:31
投稿者: んなっと

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

投稿日時: 22/06/19 14:32:19
投稿者: toma100

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

回答
投稿日時: 22/06/19 16:55:13
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/06/19 19:42:16
投稿者: んなっと

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関数とかいうのが使えるようになるかもしれません。楽しみですね。

投稿日時: 22/06/20 06:44:24
投稿者: toma100

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

回答
投稿日時: 22/06/20 13:43:56
投稿者: WinArrow
投稿者のウェブサイトに移動

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

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

このマクロは、標準モジュールに記述(コピペ可)してください。
このマクロの起動タイミングは、別途。

回答
投稿日時: 22/06/20 17:13:39
投稿者: んなっと

もしかしたらこれもいけるかもしれません。
=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")))
でほぼ完了しているので、それ以降のやりとりは蛇足です。
理解できないとかはそちらの問題。自分で努力してください。

投稿日時: 22/06/20 21:34:21
投稿者: toma100

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