[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セルの中の区切られた文字の個数をカウント』（NAGI）
初歩的な質問だったらすみません。
一つのセルに、"、"（全角読点）で区切られた文字列（人の名前）があります。
（例）
A B C D ～ AA AB 1 社員 派遣スタッフ 社員 派遣スタッフ ～ 社員数 派遣数 2 鈴木、山田、田中 4 鈴木、佐々木 ⑩、2 ～
これで、AA列に「社員」のセルの述べ人数（上のAとCの合計だったら5）を出したいです。 今は全部目で数えているのですが、人数が多いセルなどを数えるのが大変で… 同じ人が複数セルにある時も「述べ」なので単純に個数だけをカウントしたいです。
本当はAB列に派遣スタッフ数も同じように述べ人数で出したいのですが、「⑩」のような文字列と「4」のような数値が混在しているので… （丸囲みは急遽その作業に入ることになった人数、普通の数値は元々予定されていた人数、という使い分けがあります）
とりあえず「社員」の延べ人数をカウントしたいのですがどうすればよいでしょうか？ 1行目は「社員」「派遣スタッフ」と決まっているわけではなく、作業内容や時間帯が入ります。
必ず「、」で分けられているなら、その数＋１が セルに含まれてる人数となるので、
AA1 =LEN(A1&C1)-LEN(SUBSTITUTE(A1&C1,"、",""))+2 とか。 （純丸）(o^-')b
鈴木、山田、田中 これは 3人、⑩、2 は 12人ということ？ 常に 鈴木、山田、田中 といった形で、そのＡ列とＣ列の人数ということなら（純丸）さんの回答で。
(ぶらっと)
○囲み数字は1から20でいいのかな？ それとも21以上も使われてるのかな？ あと、通常の数字も最大はどれくらいかな？
もうひとつ。 Excelのバージョンは何？ (春日野馨)
純丸様、ぶらっと様、春日野馨様 説明不足で申し訳ございません
Excel2007です。 名前と派遣スタッフを入れるセルには条件付き書式が設定されています。 ○囲み数字は1から20までです。 通常の数字は0から最大50くらいでしょうか…（3ケタにはならないです）
>鈴木、山田、田中 これは 3人、⑩、2 は 12人ということ？ はい、そうです。 名前が入るセルは「折り返して全体を表示」の設定になっていて、10人ぐらいの名前が入ることがあります。 また、たまに 岡田【臨時】 のように名前の後に【】がつくこともあります。 例）鈴木、岡田【臨時】、田中 →3人
あと、「社員」列と「派遣スタッフ」列は交互に各13列、合計26列ありますが、1か所だけイレギュラーな場所があって、
9:00 | 10:00 | 10:30 | 11:00 社員 派遣 | 社員 派遣 | 社員 派遣 | 社員 派遣 鈴木、田中 5 | 鈴木、池田 ②、5 | 鈴木、池田、山田 6 | 山田 3
このように10:00・10:30だけが30分単位に分かれているんですが、計算したいのは「1時間ごとの単位」なので この場合の10時台の人数は「社員3人、派遣7人」（多い方でカウント）になり、この行の合計は「社員6人、派遣15人」になります。
最初に慌てて書いたので説明が全然足りてなくてすみません。
（NAGI）
すみません、追記です。
9:00 | 10:00 | 10:30 | 11:00 社員 派遣 | 社員 派遣 | 社員 派遣 | 社員 派遣 鈴木、田中 5 | 鈴木、池田 ②、5 | 鈴木、大野、山田 6 | 山田 3
このようになった場合の10時台の社員数は「4（鈴木・池田・大野・山田）」になります。 単純に多い方でカウントというわけではなかったです。すみません
（NAGI）
山田、4 なんてのもある？（山田さんと、他４人） あと、１０時台というのはＣ，Ｄ，Ｅ，Ｆ列と決め打ちしていいの？ それとも、Ａ列が８時だったら、Ｅ，Ｆ，Ｇ，Ｈ列になるの？
追記）それと、提案。 今回は、延べ人数を社員、派遣別にということだけど、将来、いやいや時間別にとか あれやこれやといった分析がほしくなるかも。（おうおうにして、そうなっていく） なので、現在のリストの形->時間帯別の割り当て人数（社員・派遣）の形のリスト これをつくっておいて、いろんな分析は、後者のリストを基にする。 このような構えにしておくことをお勧めするね。
(ぶらっと)
>山田、4 なんてのもある？（山田さんと、他４人） いえ、社員の列は必ず全員の名前が入ります。
10時台は実際には
H I | J K 3 10:00 | 10:30 4 社員 派遣 | 社員 派遣
5行目以下各列名前・人数
となっています。 開始は9:00と決まっているのでH～K列は変わりません。（1～3行目までは結合セルが多数あります）
合計が入るのは、社員：AN列（予定）・AP列（結果）、派遣：AO列（予定）・AQ列（結果）になります。 予定合計は最初に入力したデータを元に計算して記入、その後内容が追加変更されるたびに結果合計が変わる感じです。 関数を入れる場合、予定合計は最初に入力したら変わらないので、計算結果が出た時点でコピー→値だけ貼りつけで構わないと思っています。
ここまで書いて追記を見ました。
>現在のリストの形->時間帯別の割り当て人数（社員・派遣）の形のリスト 確かに今も社員別・派遣別の人件費をそこから割り出したりしているので（全て手計算か、簡単な数式しか入っていませんが） 他に追加要求が来る可能性も高いです。 社員のシート、派遣のシート、のように分けたものを用意するなどすればいいのかな？ 最初は社員・派遣マスタのようなものを作ろうとしていたのですが、社員は名前も人数も決まっていても派遣スタッフは名前も人数も入れ替わりが激しく、これは頓挫しています。 （それで数字だけの表記になっているのですが） やはり基本になるものを作成しておいた方がよいですよね…
（NAGI）
作業列に「区切り位置指定ウィザード」で10:00・10:30の社員の名前をセルごとに分けるまではできたのですが、 10:00と10:30それぞれ何人入るかが分からないので区切り先のセルの数が分からないのでどうすればよいか悩んでいます。 ちなみにこの表は縦にも長く伸びているので（作業内容や作業位置で分かれている）、行数も決まっていません。
社員の名前も1文字～3文字ぐらいあるので左からいくつめで区切ると決まっていないし…
やはり関数では無理でしょうか
（NAGI）
社員の延べ人数なら、「、」の数を数えれば良いので 作業列をふんだんに使ってすれば出来そうに思いますが。。。
派遣スタッフの丸付き数字の方は 面倒じゃないでしょうか。。。
関数にこだわるのでしょうか？
＞10:00・10:30だけが30分単位に分かれているんですが、計算したいのは「1時間ごとの単位」なので のイレギュラーがあったり ＞1行目は「社員」「派遣スタッフ」と決まっているわけではなく、作業内容や時間帯が入ります。 と書いてあったり ＞3 10:00 | 10:30 ＞4 社員 派遣 | 社員 派遣 と書いてあったり。。。（2行目に時間、3行目に 社員・派遣・社員・派遣・・・と並んでいる？）
シートのフォームはどうなっているのでしょうね？
(HANA)
＞ 社員の延べ人数なら、「、」の数を数えれば良いので ＞作業列をふんだんに使ってすれば出来そうに思いますが。。。 上にも書きました「10:00・10:30」のカウントが難しくて、作業用シートが別に必要なのかな？とも考えていますが…
丸囲み数字は、イレギュラーな人数と通常の人数を分けるのに必要なんです。
シートのフォームは下記のような構成です。
A B C D E F G H I J K L M … 1 表 全 体 の 見 出 し 行 2 担当者 番号 作業内容(C～E結合） （Fより右見出し行） 3 (作業時間) 9:00 10:00 10:30 11:00 … 4 (作業内容) 作業A 作業B 作業C 作業D … 5 鈴木 1 鈴木、田中 4 鈴木、山田、池田 ②、5 鈴木、大野、山田 3 鈴木、大野、和田 ①、3 6 本多 2 本多、宮本 5 本多、宮本、松田 ③、4 本多、佐々木、辻 5 本多、田中、水野 ②、6
… AH～AM AN AO AP AQ 4 （作業列） 予定人数 結果人数 5 25 30 34 42 （6行目以下略）
関数に拘るというか…私があまりマクロには詳しくないもので、関数でできないかと思ったのですが…無理ですか？
（NAGI)
イレギュラーな人数が有った時は、その数字の方が必ず前に来ますか？ それとも、どちらを前に書くかは決まってないですか？
作業列を使って良いなら、関数で出来ると思います。
(HANA)
＞イレギュラーな人数が有った時は、その数字の方が必ず前に来ますか？ 今のところ決まっていないですが、必要であれば前に統一します。
あと、丸囲み数字だけの場合もあります（新しい作業が急遽発生した場合など）
作業列を使うのは全然構いません。
（NAGI）
じゃあ、前に統一して下さい。
列番号が揃っている方が面倒で無いと思うので、BF列からを作業列に使用します。
BF5に
=IF(F5="",0,LEN(F5)-LEN(SUBSTITUTE(F5,"、",""))+1)
BG5に（↓3行で一つの式です）
=IF(G5="",0,IF(ISTEXT(G5),MATCH(LEFT(G5,1),
{"①";"②";"③";"④";"⑤";"⑥";"⑦";"⑧";"⑨";"⑩";"⑪";"⑫";"⑬";"⑭";"⑮";"⑯";"⑰";"⑱";"⑲";"⑳"},0),0)
+RIGHT(SUBSTITUTE(G5,"、",REPT(" ",5)),5))
BF5:BG5を選択して、必要範囲に（CM列まで）フィルドラッグ
AP5 =SUMIF(F$4:AM$4,"<>",BF5:CM5)-MIN(BH5,BJ5) AQ5 =SUMIF(F$4:AM$4,"=",BF5:CM5)-MIN(BI5,BK5)
F4:AM4は、一列おきに作業内容が入力されている物とします。 もしかしたら、4行目ではなく、3行目を確認する方が良いかもですね？
(HANA)
すみません、BG5の式は
=IF(G5="",0,IF(ISTEXT(G5),MATCH(LEFT(G5,1),
{"①";"②";"③";"④";"⑤";"⑥";"⑦";"⑧";"⑨";"⑩";"⑪";"⑫";"⑬";"⑭";"⑮";"⑯";"⑰";"⑱";"⑲";"⑳"},0),0)
+IF(ISERROR(RIGHT(G5,1)*1),0,RIGHT(SUBSTITUTE(G5,"、",REPT(" ",5)),5)))
を使ってみて下さい。
(HANA)
HANA様ありがとうございます。 ちゃんと計算できました！ 助かりましたm(__)m
（NAGI）
すみません、できたと思っていたのですが…
やはり10:00・10:30の計算がうまくいきません
例えば社員の名前が入っている場合、
10:00 10:30 鈴木、池田、山田、田中 鈴木、池田、大野
こうなっていたら「鈴木、池田、山田、田中、大野」で5人にならないといけないのですが、数が多い方のセルに合わせてしまって（？）4人となります。
ちなみに下記の場合
10:00 10:30 鈴木、池田、山田、田中 鈴木、池田、本多、大野
この場合「鈴木・池田・山田・田中・本多・大野」で6人とカウントしたいのですがやはりこれも4人になります。
やっぱりマクロじゃないとだめでしょうか…
（NAGI）
＞数が多い方のセルに合わせてしまって（？）4人となります。 数が多い方のセルに合うなら、「5人」ですよね？
＞この場合「鈴木・池田・山田・田中・本多・大野」で6人とカウントしたい でしたら、数式では難しいですね。
ご説明の訂正 ＞単純に多い方でカウントというわけではなかったです。すみません に気づいて居ませんでした。
＞名前が入るセルは「折り返して全体を表示」の設定になっていて、10人ぐらいの名前が入ることがあります。 と言う事なので、20人を想定して。。。さらに作業列を使います。
CN5
=COUNTIF(CP5:DI5,">*")
CO5
=SUBSTITUTE(H5&"、"&J5,"、",REPT(" ",60))
CP5
=TRIM(MID($CO5,60*COLUMN(A5)-59,60))&" "
CQ5・・・DI5までフィルドラッグ
=IF(COUNTIF($CP5:CP5,TRIM(TRIM(MID($CO5,60*COLUMN(B5)-59,60)))&" ")>0,"",TRIM(TRIM(MID($CO5,60*COLUMN(B5)-59,60)))&" ")
AP5の式は =SUMIF(F$4:AM$4,"<>",BF5:CM5)-SUM(BH5,BJ5)+CN5 に変更
で、どうでしょう？
(HANA)
HANA様
お手数おかけいたしました。 うまくいきました！ ありがとうございました。
（NAGI）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.