[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シート１の１つのセルに複数記入されている国名がシート２と合致しているか確認し合致した場合、シート２の国名のエリア部分でカウントし、エリア項目で円グラフを作成したい。』（たま）
＜シート１＞
行きたい国、複数回答
例）
A1：インド、アイスランド、タイ
A2：イタリア
A3：タイ、シンガポール
＜シート２＞
アジア
・インド
・インドネシア
・カンボジア
・シンガポール
・タイ
・
・
・
欧米
・アメリカ
・カナダ
欧州
・アイスランド
・アイルランド
・イタリア
・
・
＜やりたいこと＞
シート１で、１つのセル内に複数記入がある国名を、シート２のエリアを元に分類して、シート２において、シート２のエリア名（アジア、欧米、欧州…）という分類で円グラフを作りたい。
例のA1セルの場合だと、
インド、タイが「アジア」エリアに該当するので、１カウント
アイスランドは「欧州」エリアに該当するので、１カウント
⇒複数回答あるシート１のA1セルだけで、「アジア」と「欧州」にそれぞれ１カウントずつされる円グラフを作成したい。「アジア：１」「欧州：１」
A2セルであれば、「欧州」で1カウント
A3セルであれば、「アジア」で1カウント（2カウントしない）
＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞
参考になりますか？
<Sheet1>
__A_______________________ __B___ __C_ __D_
1 アジア 欧米 欧州
2 インド、アイスランド、タイ 1 0 1
3 イタリア 0 0 1
4 タイ、シンガポール 1 0 0
<Sheet2>
__A_________ __B_____ __C_________
1 アジア 欧米 欧州
2 インド アメリカ アイスランド
3 インドネシア カナダ アイルランド
4 カンボジア イタリア
5 シンガポール
6 タイ
Sheet1.[B2] =(SUM(COUNTIF(XLOOKUP(B$1,Sheet2!$A$1:$C$1,Sheet2!$A:$C,""),TEXTSPLIT($A2,"、")))>0)*1 ↓→にコピー
（まる） 2023/03/15(水) 18:19:40
結論：まだうまくいっていません…。
関数初心者でして初めて見る関数もあり、まずはまるさんが書いてくださったSheet1、2を作成し、
Sheet1.[B2] に関数をそのままコピー、[B3][B4]、[C2][D2]、[C3][D3]、[C4][D4]とドラッグしました。
が、いずれのセルも「0」カウントとなってしまいます。
Sheet1.[B2]は、「１」にもなりません。[B2]〜[D4]まで全て「0」です。
何がいけないのでしょうか。
申し訳ありませんが、もう少しお付き合いいただければありがたいです。
よろしくお願い致します。
（たま） 2023/03/16(木) 09:48:12
私試してみたけどできたので一個ずつ分解して何が悪いか確かめてみましょうか。
１）新しいシートのA2に、回答を入れる ２）A3:A5にアジア 欧州 欧米と手入力する ３）B2=TEXTSPLIT($A2,"、") といれると、D列までスピルしてインド、アイスランド、タイが表示される この段階で表示されなければ、「区切り文字」＝「、」が指定とあってない可能性があります。
４）B3=COUNTIF(XLOOKUP($A3,Sheet2!$A$1:$C$1,Sheet2!$A:$C,""),B$2) と入力して、D5までコピー 結果が下記の表になります。 この通りになっていなければ、さらに細分化して検証が必要です。
|[A] |[B] |[C] |[D]
[1]| | | |
[2]|インド、アイスランド、タイ|インド|アイスランド|タイ
[3]|アジア | 1| 0| 1
[4]|欧州 | 0| 1| 0
[5]|欧米 | 0| 0| 0
検証が必要な項目 国名に、不要なスペースが入っていないか？「インド」が「インド 」になっている等 エリア名に不要なスペースが入っていないか？「アジア」が「アジア 」になっている等
（稲葉） 2023/03/16(木) 10:53:06
思いつく点としては、
(1)「Sheet1」と「Sheet2」の見出し「アジア 欧米 欧州」が一致していない。 見た目、同じに見えても前後に余分なスペースが入っている場合は検索に失敗します。 「Sheet1」の[B1:D1]を消去した上で、[B1]に以下の関数を入れて試してください。 ＝＞[B1:D1]に「アジア 欧米 欧州」と表示されればOKです。「[B1:D1]を消去してから」をお忘れなく。
Sheet1.[B1] =Sheet2!A1:C1
(2)「Sheet1」A列の国が「インド、アイスランド、タイ」のように区切り文字が「、」（全角カンマ）で区切られているか確認。 又、これにも前後に余分なスペースが入っていないか確認。 「Sheet2」の国名と完全一致しているかも確認。
(3)「Sheet2」の各国名にも前後に余分なスペースが入っていないか確認。
(4)365だが、TEXTSPLIT関数が使えない。＝＞これはエラー表示になると思うので考えにくい。
以上でダメなら自分は、お手上げです。 又、最終目標は円グラフ化との事なので、これは第１段階ですが、この先は他の方にお任せします。 （まる） 2023/03/16(木) 10:57:58
教えていただきました手順を試してみたところ、
3)が実行エラー「＃NAME?」となってしまいます。
「区切り文字」に関しては「、」で間違っていません。コピーして試してもいます。
もしや私のエクセルでは「TEXTSPLIT関数」は使えないのでしょうか。
fx（関数の挿入から、検索してもhitしませんでした。。。）
エクセルの「ファイル」⇒「その他」⇒「アカウント」から
「Microsoft 365 Apps for enterprise」とあり、３６５と判断していますが違うのでしょうか。
＞まるさん
何度もありがとうございます。
1)は問題なくできました。2)、3)についても空白がなく完全一致であることを確認済みです。
4)これが心配になってきました。
昨日教えていただいた関数文では特にエラーになっていませんが、上記に書いた通り、関数の挿入での検索でhitしませんでした。。。
（たま） 2023/03/16(木) 14:37:14
TEXTSPLIT使えないとなると・・・
B2=TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",100)),(SEQUENCE(1,10)-1)*100+1,100))
これだといけます？
SEQUENCEが新しい関数なんですが、そのほかは普通の関数です。
（稲葉） 2023/03/16(木) 15:15:30
↑の式だとMIDの中が配列になってくれなかった・・・
素人考えて・・・
元データに国名-地域名の検索値を設けて
B列は
SUM(COUNTIF(国別データ!$C:$C,{インド-アジア,アイスランド-アジア,タイ-アジア}))
C列は
SUM(COUNTIF(国別データ!$C:$C,{インド-欧米,アイスランド-欧米,タイ-欧米}))
D列は
SUM(COUNTIF(国別データ!$C:$C,{インド-欧州,アイスランド-欧州,タイ-欧州}))
こんな感じの計算させてはどうでしょう？
シート「国別データ」
こんな感じに並べなおして
C2=A2&"-"&B2
として下方向にコピー
|[A] |[B] |[C]
[1] |国 |エリア名|検索値
[2] |インド |アジア |インド-アジア
[3] |インドネシア|アジア |インドネシア-アジア
[4] |カンボジア |アジア |カンボジア-アジア
[5] |シンガポール|アジア |シンガポール-アジア
[6] |タイ |アジア |タイ-アジア
[7] |アメリカ |欧米 |アメリカ-欧米
[8] |カナダ |欧米 |カナダ-欧米
[9] |イタリア |欧米 |イタリア-欧米
[10]|アイスランド|欧州 |アイスランド-欧州
[11]|アイルランド|欧州 |アイルランド-欧州
集計シート
A列、B1-D1は手入力
|[A] |[B] |[C] |[D]
[1]|回答 |アジア|欧米|欧州
[2]|インド、アイスランド、タイ | 1| 0| 1
[3]|インド、カンボジア、アメリカ、カナダ、アイルランド| 1| 1| 1
[4]|カナダ、アメリカ | 0| 1| 0
B2=SIGN(SUM(SCAN("",TRIM(MID(SUBSTITUTE($A2,"、",REPT(" ",100)),(SEQUENCE(1,10)-1)*100+1,100))&"-"&B$1,LAMBDA(a,b,COUNTIF(国別データ!$C:$C,b)))))
として、右と下にコピー
（稲葉） 2023/03/16(木) 16:15:21
最初の「SEQUENCE」が入った関数では見事にセル毎に記載ができました！
（が、その先がNGなのですね。。。残念）
そして、その後に考えてくださった内容で、確認してみましたが
集計シートのB2に指定の関数を入れると、「＃NAME?」となってしまいます。
もはや関数のレベルが高く理解はできておらず、書かれているままにコピペで試していて大変恐縮なのですが。。。
何をどう確認したらいいのかチェックする術はありますでしょうか？
（たま） 2023/03/16(木) 17:11:33
国別データシートはそのままで 集計シートのB2に B2=SIGN(SUM(COUNTIF($A2,IF(B$1=国別データ!$B$1:$B$11,"*"&国別データ!$A$1:$A$11&"*",CHAR(2))))) として、配列数式として確定、でどうでしょうか？
かなり重たい数式になると思います。
数式の以下の部分は、必要に応じてサイズを増やしてください。
国別データ!$B$1:$B$11 → 国別データ!$B$1:$B$50
国別データ!$A$1:$A$11 → 国別データ!$A$1:$A$50
|[A] |[B] |[C] |[D]
[1]|回答 |アジア|欧米|欧州
[2]|インド、アイスランド、タイ | 1| 0| 1
[3]|インド、カンボジア、アメリカ、カナダ、アイルランド| 1| 1| 1
[4]| | 0| 0| 0
「インド、アイスランド、タイ」という文字から、
国別データのアジアのみを絞り込んだ配列データで検索
アジアで絞り込めば{"*インド*","*インドネシア*","*カンボジア*","*シンガポール*"}
を文字の中から探して、合計する感じです。
ただ、*インド*で検索すると、インドネシアも引っかかるので、異なるエリアで最短一致するものがあると
カウントされてしまいます。
地理に疎いので、そういう国があるかわかりませんが・・・
頭文字3文字で検索して、怪しいのは以下の6か国ぐらいかと思います。
https://www.jpo.go.jp/system/patent/pct/tetuzuki/document/kuni-meisho/kuni-meisho-ichiran.pdf
インド インドネシア共和国 コンゴ共和国 コンゴ民主共和国 ドミニカ共和国 ドミニカ国
（稲葉） 2023/03/16(木) 17:39:14
365での新関数が「使える/使えない」の判断基準は
・例えば、「TEXTSPLIT関数」ならば、 セルに「=text」と打って、候補リストに「TEXTSPLIT」が表示されなければ、使えないと判断します。
・稲葉さんの 2023/03/16(木) 16:15:21投稿の関数では多分「SCAN関数」が使えないと推察します。 セルに「=s」と打って、候補リストに「SCAN」が表示されなければ、使えないと判断します。
ちょっと時間ができたので、今から、少し考えてみます。 （まる） 2023/03/16(木) 18:01:57
以下で、どうでしょう？
(1)制約条件としてアンケート回答は10ケ国までとする。 (2)A列を「、」で分割した各国を「B列〜K列」に表示する。 (3)グラフ用のエリア別データは「L列以降」に表示する。
<Sheet1>
__A_______________________ __B_____ __C_________ __D_ __E __F __G __H __I __J __K_ __L___ __M_ __N_
1 回答 国1 国2 国3 国4 国5 国6 国7 国8 国9 国10 アジア 欧米 欧州
2 インド、アイスランド、タイ インド アイスランド タイ 1 0 1
3 イタリア イタリア 0 0 1
4 タイ、シンガポール タイ シンガポール 1 0 0
<Sheet2>
__A_________ __B_____ __C_________
1 アジア 欧米 欧州
2 インド アメリカ アイスランド
3 インドネシア カナダ アイルランド
4 カンボジア イタリア
5 シンガポール
6 タイ
以下の関数を入力
Sheet1.[B2] =TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",100)),(SEQUENCE(1,10)-1)*100+1,100)) ↓コピー、右には自動でスピルします
Sheet1.[L1] =Sheet2!A1:C1 自動でスピルします
Sheet1.[L2] =(COUNT(MATCH($B2#,Sheet2!A:A,0))>0)*1 ↓→コピー
※Sheet1.[B2]は稲葉さん提案の式を引用させてもらいました。 （まる） 2023/03/16(木) 18:40:33
ありがとうございます。
やりたいことが…、できました!!!
＞まるさん
「TEXTSPLIT」「SCAN」、どちらも利用NGでした。
確認方法ありがとうございます。また、別案まで考えてくださりありがとうございます。
★お二方へ
お忙しい中、お時間さいてくださってありがとうございます。
即日回答いただけたこと、私の環境に合わせて検討してくださったこと、また今後の注意点まで、色々と大変有難かったです。
感謝感激でした。
私の使用環境の記載により、関数の使用有無が変わってしまい混乱とお手間を取らせてしまい大変申し訳ありませんでした。
お二方とも表を細かく記載してくださったので、関数初心者の私でも理解ができ、嬉しかったです。
（これを書くだけでも大変ですのに本当に感謝です）
初めてこちらでお世話になりましたが、とても心強いエクセル学校を見つけられました。
ありがとうございました。
（たま） 2023/03/17(金) 10:21:12
解決されたようで、よかったです。
別案としては、Microsoft365には「Power Query」という外部機能が標準で備わっていて、 これでエリア別に集計して「ピボットグラフ」として取り込む方法もあります。 データの追加、削除にもボタン1クリックで対応可能です。 「Power Query」の学習は必要ですが・・・
（まる） 2023/03/17(金) 11:00:59
"、"でサンドイッチして検索する方法です。H:I列に対応表を用意して
A B C D E F G H I
1 アジア 欧米 欧州 インド アジア
2 インド、アイスランド、タイ 1 0 1 インドネシア アジア
3 イタリア 0 0 1 カンボジア アジア
4 タイ、シンガポール 1 0 0 シンガポール アジア
5 タイ アジア
6 アメリカ 欧米
7 カナダ 欧米
8 アイスランド 欧州
9 アイルランド 欧州
10 イタリア 欧州
B2
=1*(COUNT(INDEX(FIND("、"&REPT($H$1:$H$200,$I$1:$I$200=B$1)&"、","、"&$A2&"、"),))>0)
右方向・→下方向・↓
（んなっと） 2023/03/17(金) 11:02:04
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.