excel時間データを抽出＋countifsの応用

質問

(1)
●● XX 12:00 △△　■■　★★　＊＊
○○ XX 00:04 ▲▲　□□　☆☆　＊＊
○● XX 23:34 △▲　□□　★☆　＊＊
・
・
という様なデータがあります。
23:00台～05:00台までの1時間ずつの、７つの枠に分け、上記データ（横一列が１データ）から必要な情報だけ別シートに表示させたいです。
例）
23:00台
23:34 △▲　○● XX　★☆
00:00台
00:04 ▲▲　○○ XX ☆☆

(2)
条件のセル　A1セルにDと入力してある。

↓最後のセルを表示 ↓１文字１セル　↓数字　
E　　　 ABCDE　　　　　　 ２
W　　　 EXRDW　　　　　 １
D　 JOWPRD　　　　　 ２
N　 　DUQKLN　　　　　 ３

上記のようなデータがあります。
横一列のデータを1データとみて、最初と最後の文字（＝セル）以外で、
条件のA1セル（D)があれば、右端の数字を足し算したい。

e.g. 条件がD、両端の数字はカウントしないので、答えは２＋１です。

本当に困っています。。。宜しくお願い致します。

回答No.11

MarcoRossiItaly

No.3・No.7 です。

＞sumifsなんです…。

いいえ、SUMIFS ではありません。

SUMIFS が適切なケースとは、既に No.3 で説明したとおり、AND 条件の場合です。本当は「データ2番目列」などといったご説明のほうが正しかった（複数の列に分かれていた）というのであれば、それらの列の中のどこかに「D」という値が 1 つあればその行を合計するということですから、AND ではなく OR 条件ですね？したがって SUMIFS は選びません。

仮に、「1 行の中で C 列から右には同一の名前データが 1 つしか存在しない」といえる場合、お求めの計算結果は、SUMIF を使った次式で求められます。集計の対象のシート名を Sheet1 とします。

別シート

A1　木村　など
B1　=sumif(sheet1!c:c,a1,sheet1!a:a)+sumif(sheet1!d:d,a1,sheet1!a:a)+sumif(sheet1!e:e,a1,sheet1!a:a)+sumif(sheet1!f:f,a1,sheet1!a:a)+sumif(sheet1!g:g,a1,sheet1!a:a)+sumif(sheet1!h:h,a1,sheet1!a:a)-sumif(sheet1!b:b,a1,sheet1!a:a)

最初の質問文を読んだ感じでは、C 列に「木村」が存在している場合も合計してはいけないとも受け取れますから、その場合は、上式のうち「sumif(sheet1!c:c,a1,sheet1!a:a)+」という部分を削除してください。

もっと短い数式にするためには、SUMPRODUCT を導入する必要があります。

別シート

A1　木村　など
B1　=sumproduct(sheet1!a1:a3000*(sheet1!b1:b3000<>a1)*(countif(indirect("sheet1!c"&row(1:3000)&":h"&row(1:3000)),a1)>0))

SUMIF の場合と同様、C 列が「木村」のときは合計しないというのであれば、上式の「sheet1!c」を「sheet1!d」に修正してください。

なお本当は、この手の集計でもっとお勧めな方法というのは、Sheet1 の各行にフラグを立て、オートフィルタにより該当の行のみに絞り込むことです。なぜなら非常に簡単な上、応用範囲も広いからです。

次式は、その行の途中の位置に「木村」があれば 1、「木村」がないか最後の位置にある場合は 0 を算出します。C 列を除きたければ、「c1:h1」→「d1:h1」と修正してください。

Sheet1

I1　=countif(c1:h1,別シート!A$1)-(b1=別シート!A$1)
J1　=a1*i1
J5　=sum(j1:j4)

先にフラグ（I 列の値）が算出されていると、たとえオートフィルタを使わずに SUM、SUMIF、SUMPRODUCT 関数により一発で答えを求めようとする場合であっても、極めて易しい数式にすることができます。

＞ややこしくてすみません。

ややこしさ云々よりも、的確な解答を得るには、手を抜かずに説明し、それなりに情報を開示していただくことが必要です。

回答No.10

bunjii

>時間帯入りの方のデータに関して、やってみましたが、時間データがうまく表示されず0.95～などの小数点入りデータが表示され、セルの書式設定で時刻にしても直りません…どうすればよいでしょうか。
(1)の質問ではマークのみの抽出で時間帯はキーボードからの入力です。
回答の数式は配列数式なので数式バーに入力後CtrlとShiftを押しながらEnterキーの打鍵で確定します。
条件に合う抽出したデータは加工していませんので記号の代わりにシリアル値となっているセルについては目的に合う表示形式にするだけで良いはずです。
時間（時刻も同じ）
日時に関する値は通常シリアル値で入力され表示形式で目的に合う表示にします。
時間は1時間が24分の1（1/24）なので表示形式が標準のとき小数になります。
「セルの書式設定で時刻にしても直りません…どうすればよいでしょうか。」と言われても当方には元データが無いので検証できません。

>2行目ではHを探す3行目ではI・・・という風にしたいので、ドラッグで対応できる式を使いたいです。
(2)の質問の文言からは読み取れない条件なので、そこまでするのであれば元データから配列を組み替えなければなりません。
貼付画像はSheet2のN2:W12へSheet1のA1:K10を行列入れ替えで転置し、作業用にD2:L8を配置して、A2:A8の検索値をキーインするとB2:B8へ集計されるようにしました。
N2=INDEX(Sheet1!$A$1:$K$10,COLUMN(A1),ROW(A1))
N2の数式をN2:W12へコピペし、N4:N12をセル結合しました。（先にセル結合するとオートフィルでコピーできません）
D2=IF(AND($A2<>"",COUNTIF(O$5:O$11,$A2)),(SUMPRODUCT(MAX((O$5:O$12=$A2)*ROW(O$5:O$12)))<COUNTIF(O$4:O$12,"<>0")+3)*1,0)
D2セルをD2:L8へコピペします。
B2=IF(COUNTA(A2),SUMPRODUCT(D2:L2*O$2:W$2),"")
B2セルをB8までオートフィルで下へコピーすれば完了です。
尚、回答No.9にも誤りがありましたので無視してください。
かなり複雑な条件検索なので未だ誤りがあるかも知れません。

回答No.9

bunjii

回答No.8の作業用テーブルの数式に誤りがありましたので訂正します。
=CHOOSE(MIN(COUNTIF(C2:J2,"D")+1,4),0,(SUMPRODUCT(MAX((C2:J2="D")*COLUMN(C2:J2)))<COUNTA(C2:J2)+2)*(SUMPRODUCT(MAX((C2:J2="D")*COLUMN(C2:J2)))>3),(SUMPRODUCT(LARGE((C2:J2="D")*COLUMN(C2:J2),1))<COUNTA(C2:J2)+2)*1,(SUMPRODUCT(LARGE((C2:J2="D")*COLUMN(C2:J2),2))<COUNTA(C2:J2)+2)*1)*A2
　　　　↓
=CHOOSE(MIN(COUNTIF(C2:J2,Sheet2!A$1)+1,4),0,(SUMPRODUCT(MAX((C2:J2=Sheet2!A$1)*COLUMN(C2:J2)))<COUNTA(C2:J2)+2)*(SUMPRODUCT(MAX((C2:J2=Sheet2!A$1)*COLUMN(C2:J2)))>3),(SUMPRODUCT(LARGE((C2:J2=Sheet2!A$1)*COLUMN(C2:J2),1))<COUNTA(C2:J2)+2)+(SUMPRODUCT(MAX((C2:J2=Sheet2!A$1)*COLUMN(C2:J2)))>3),(SUMPRODUCT(LARGE((C2:J2=Sheet2!A$1)*COLUMN(C2:J2),2))<COUNTA(C2:J2)+2)*1)*A2
誤りの数式では検索データが"D"と固定になっていました。また、CHOOSE関数の返り値が2のときの数式が要件を満たしていませんでしたので修正しています。

データが複数文字について追加で検証した結果は添付画像のようになります。

回答No.8

bunjii

回答に対する補足を読み直して総合的な判断でSheet1に元データを模擬的に作成し、(2)の要望と解釈できる条件で検証してみました。
Sheet2のA1に検索条件の文字を入力しSheet2のB1へ集計結果を表示しました。
この処理に於いて1つの数式で処理するには検索条件が処理困難となりますので作業用のテーブルを作成します。
作業用テーブルの作成方法は2通り考えてあります。
１．データシートの1つの列を使う方法。
貼付画像のSheet1!M2セルへ次の数式を入力し、下へ必要数コピーします。
=CHOOSE(MIN(COUNTIF(C2:J2,"D")+1,4),0,(SUMPRODUCT(MAX((C2:J2="D")*COLUMN(C2:J2)))<COUNTA(C2:J2)+2)*(SUMPRODUCT(MAX((C2:J2="D")*COLUMN(C2:J2)))>3),(SUMPRODUCT(LARGE((C2:J2="D")*COLUMN(C2:J2),1))<COUNTA(C2:J2)+2)*1,(SUMPRODUCT(LARGE((C2:J2="D")*COLUMN(C2:J2),2))<COUNTA(C2:J2)+2)*1)*A2
集計はSheet2!B1セルへ次の数式を入力します。
=SUM(Sheet1!M2:M10)
2．集計シートへ文字データの前処理テーブルを作成する方法
貼付画像のSheet2!D2セルへ次の数式を入力し、右へK列までコピーし、更に下へ必要数コピーします。
=IF(AND(Sheet1!D2=$A$1,COLUMN()<COUNTA(Sheet1!$C2:$J2)+2),1,0)
集計はSheet2!B1セルへ次の数式を入力します。
=SUMPRODUCT(D2:K10*Sheet1!A2:A10)

尚、データの文字は英字の1文字に限らず複数文字を各セルにセットされている場合も対応可能のはずです。（未検証）

補足

試してみました。

数字を検索する条件（例では"D"）は、Sheet2で下に続きます。

D が途中に来る数字の和
H が途中に来る数字の和
I が途中に来る～
A ・・・・
・
・
・
という具合に、検索する条件が変わっていきます。
2行目ではHを探す3行目ではI・・・という風にしたいので、ドラッグで対応できる式を使いたいです。

ご教示いただいた方法では、Dのみの解が出てくるので…（それしか書いていなかったので当然ですね。。。。）
わかりにくくて申し訳ありません。

回答No.7

MarcoRossiItaly

No.3 です。

補足で、「ABCDEなどの文字列は全て1セル1データ」と書かれているにもかかわらず、「データ2番目列」などとも同時に書かれています。本当はどちらでしょうか？

以下は、1 セルの中に「ABCDE」と記入されている前提で説明します。また、「D」は同一の文字列中に 1 回だけ登場することも前提とします。

＞……だと思ったのですが、結果違う数字がでます。。。

No.3 で説明したとおり、今回は SUMIFS ではなく、全て SUMIF です。正しく記述してください。

補足でお示しになったデータ配列であれば、検査されるべき列は、「ABCDEFGH」などの列（C列）です。また、加算していく列は、A列ですね。したがって No.3 の数式は、次のように修正することになりますね。

=sumif(c:c,"*"&a1&"*",a:a)-sumif(c:c,a1&"*",a:a)-sumif(c:c,"*"&a1,a:a)

＞データに関しては”文字列”であり、アルファベット1文字ではありません。

ワイルドカード文字「*」は、「0 以上の字数の文字列」を表します。したがって A1 セルに「D」が記入されているとき、「CEHWOID」などは、「"*"&a1&"*"」が表す文字列の集合に含まれます。また、「"*"&a1」が表す文字列の集合にも含まれます。

つまり、ご要望どおりの計算結果が上式により得られるはずです。

ただし、「D」が同一の文字列中に複数回登場する場合には、計算が狂います。そうした場合は、「a1&"*"&a1&"*"&a1」などの項目も加減することにより、重複を調整します。

補足

sumifsなんです…。
データは文字列ですが、一列ではありません。
分かりやすくするために、名前に変えますと下記の様になります。

A列　B列　C列　D列　E列　F列　G列　H列　I列
2　　山本 鈴木 三浦 木村 田中 山下 山本
1 加藤 島田 木村 加藤
4 木村 田原 木村
1 木村 島田 浦山 松本 木村

A列＝数字
B列＝C列～I列の中の、一番右に表示しているデータを示す
C列～I列　順番を示す

このデータから、別シートのA１セルに「木村」とあればその横に「木村」が、
順番のなか（C列～I列）の途中にきているデータの数字（A列）を足した答えを、
別シートの木村（A1）の横（B1)に表示したいです。

ややこしくてすみません。

回答No.6

bunjii

(1)は目的に合いましたか？

>（２）ですが、ABCDEなどの文字列は全て1セル1データです。
質問文のデータと配列が異なりますので再確認です。
回答No.4の補足で最後に「→文字列です。一文字ではありません。」とありますが矛盾しませんか？
最後のデータは何のためにあるのですか？
また、データが1セルに1文字は理解できますが最大幾つのセル（何文字）ですか？
質問で「条件のセル　A1セルにDと入力してある。」となっていますがこれは別のSheetに集計すると言うことでしょうか？
元データの配列と条件を与えるセルや結果を入れる（数式を入れる）セルの位置関係は重要な要素なので明示するようにしてください。
補足を待って回答させて頂きます。

補足

時間帯入りの方のデータに関して、やってみましたが、時間データがうまく表示されず0.95～などの小数点入りデータが表示され、セルの書式設定で時刻にしても直りません…どうすればよいでしょうか。

回答No.5

MackyNo1

＞１つの時間帯に１０個のデータが表示できるようにして規則的に下方向に時間帯を表示するようなレイアウトのどちらがよいのでしょうか？
→後者です。

ご希望のレイアウトにして、１つの数式で対応するにはきわめて複雑な数式を駆使する必要があります。
実際のレイアウトに応じた数式にするにはリストのレイアウトによって数値を変更する必要がありますので、ひとまず添付画像のレイアウトで希望の表示ができるか確認してみてください。

H2セル、H12セル・・・以下に23：00、0：00・・・のように入力して、セルの書式設定の表示形をでユーザー定義にして「h:mm"台"」としておきます。

I2セルに以下の式を入力し、右方向に1つ、下方向にオートフィルコピーします。

=INDEX(A:A,SMALL(INDEX((HOUR($C$2:$C$200)<>HOUR(INDEX($H:$H,INT((ROW(1:1)-1)/10)*10+2)))*10000+ROW($C$2:$C$200),),MOD(ROW(1:1)-1,10)+1))&""

J2セルの数式を以下のように変更し右方向および下方向にオートフィルコピーします。

=IF($I2="","",INDEX(B:B,SMALL(INDEX((HOUR($C$2:$C$200)<>HOUR(INDEX($H:$H,INT((ROW(2:2)-2)/10)*10+2)))*10000+ROW($C$2:$C$200),),MOD(ROW(1:1)-1,10)+1)))

これで必要なデータが表示できていますので、最終的に必要な列以外を削除し、たとえば項目３のK列をI列に移動するなら、K列を選択し、選択範囲の周囲にカーソルを置いてShiftキーを押しながらI列の部分にドラッグアンドドロップしてください。

上記の回答の操作だけでも、かなり複雑な操作や数式を使用していますので、同じレイアウトのデータにして上記の数式を試してみてうまくいくことを確認して、実際のデータで別シートを参照する数式にしてみてください。

＃この問題がうまく解決できたら（２）の質問に取りむようにしたほうが良いと思います。

補足

回答No.4の補足で最後に「→文字列です。一文字ではありません。」とありますが矛盾しませんか？最後のデータは何のためにあるのですか？

→1セル内のデータ内容は文字列（名前）です。並び順を示しており、条件の名前が入っているデータから、データ内の順番の、最初と最後でないもの（途中にあるもの）の持つ数字を足し算した結果を別シートに表示したいのです。

Dが条件だとしたら、
ABCDE　の数字は足す
DACBE の数字は足さない

また、データが1セルに1文字は理解できますが最大幾つのセル（何文字）ですか？
質問で「条件のセル　A1セルにDと入力してある。」となっていますがこれは別のSheetに集計すると言うことでしょうか？

→データは1セル内に「文字列（名前）」があります。最大9列です。
別シートのA1に条件の名前が入力してあり、その隣に足し算の結果を表記したい、という事です。

（１）は今から実行してみます。有難う御座います。

回答No.4

MackyNo1

（１）提示のデータなら関数だけで表示することもできますが、実際のデータがどのようなデータかによって、比較的計算負荷の少ない実用的な数式にすることもできますので、的確な回答をするには情報が不足しています。

たとえば、例示のＥ列のデータは重複のあるデータが入力されていますが、それ以外の列に重複がないデータの列はないのでしょうか？
さらに、Ｃ列の時間のデータ以外の列はすべて文字列（数値や日付データではない）ということでよいのでしょうか？

また、集計シートですが、同じ時間帯には最大いくつくらいのデータがあるのでしょうか？
集計するシートのレイアウトはたとえば、Ａ１セルに「23：00」と入力した場合、次の0：00は横方向のＧ１セルに入力するようなレイアウトあるいは１つの時間帯に１０個のデータが表示できるようにして規則的に下方向に時間帯を表示するようなレイアウトのどちらがよいのでしょうか？

（２）この質問は元データのレイアウトと検索条件が不明です。

たとえばABCDEとJOWPRDは１文字ずつセルに入力されているのですね。

その場合上記の文字数が5と6なので最終セルが異なるように思うのですが、そのあとの数字はどの列に入力されているのでしょうか？
このような質問では、実際のデータのレイアウト（数字の列など）を具体的に提示されたほうが良いと思います。
さらに、入力されているデータが「本当」にA～Zの1文字がセルに入力されているのでしょうか（この場合は数式が簡単ですが、一般的な文字列が入力されている場合は複雑な数式を作成する必要があります）。

＃このような掲示板では、１つのスレッドに2つの質問をすることは好ましくありませんので、今後は１つのスレッドに１つの質問をするようにしましょう。
また、今回のように複雑な処理をする場合は、数式やマクロで対応する場合でも修正処理が煩雑になりますので、元データのレイアウトなどは、できる限り正確な情報を提供するようにしましょう。

補足

（１）例示のＥ列のデータは重複のあるデータが入力されていますが、それ以外の列に重複がないデータの列はないのでしょうか？
→全て名前・地名・時間等の重複のない可能性もあり、重複のある可能性もあるデータです。

Ｃ列の時間のデータ以外の列はすべて文字列（数値や日付データではない）ということでよいのでしょうか？
全て文字列と考えてください。

集計シートですが、同じ時間帯には最大いくつくらいのデータがあるのでしょうか？
→１０ぐらいです。

集計するシートのレイアウトはたとえば、Ａ１セルに「23：00」と入力した場合、次の0：00は横方向のＧ１セルに入力するようなレイアウトあるいは１つの時間帯に１０個のデータが表示できるようにして規則的に下方向に時間帯を表示するようなレイアウトのどちらがよいのでしょうか？
→後者です。

（２）この質問は元データのレイアウトと検索条件が不明です。

たとえばABCDEとJOWPRDは１文字ずつセルに入力されているのですね。
→はい。

その場合上記の文字数が5と6なので最終セルが異なるように思うのですが、そのあとの
→すみません、数字データはA列に表示しています。ABCDEなどはデータの個数（列数）がそれぞれ違います。

入力されているデータが「本当」にA～Zの1文字がセルに入力されているのでしょうか（この場合は数式が簡単ですが、一般的な文字列が入力されている場合は複雑な数式を作成する必要があります）。
→文字列です。一文字ではありません。

宜しくお願い致します。

回答No.3

MarcoRossiItaly

まず(1)について。

質問文では別シートに表示とのご要望ですが、オートフィルタで 23:00 台などの行のみに絞り込み、その状態の表をコピーして、別シートなどに貼り付けるというのではダメですか？そのほうがずっと簡単ですが。

「23:00 台である行に 1、それ以外の行には 0」というフラグを立てるには、「23:00 台」という列（H 列）に次式を記入。

H2　=("23:00"+0<=c2)*(c2<"23:00"+"1:00")

上式のうち「"23:00"」という部分は、セル参照に置き換えても問題ありません。「+0」が外れて、次のとおりになります。

H1　23:00
H2　=(H$1<=c2)*(c2<H$1+"1:00")

(2)について。

SUMPRODUCT 関数による計算例の回答も出ていますが、SUMIF のみで計算する場合は、次式になります。

=sumif(b:b,"*"&a1&"*",c:c)-sumif(b:b,a1&"*",c:c)-sumif(b:b,"*"&a1,c:c)

今回は、条件により検査される列が B 列のみなので、SUMIF です。2 列以上あってそれらの AND 条件で絞り込むケースでは、SUMIFS を使います。

補足

すみません、（２）ですが、ABCDEなどの文字列は全て1セル1データです。

条件
D

数字 最後のデータ　 データ　
1 　　H　　　　 　　A B C D E F G H
4　　 D 　　　　　　C E H W O I D
2 　　W　　　　 　　I W
1 　　Q　　　　　　 O D Q
3 　　Y　　　　 　　J I O D Y

という様になっています、説明不足で申し訳ありません。また、順番も変わっております。
データに関しては”文字列”であり、アルファベット1文字ではありません。

=samifs(数字の列,データ2番目列,条件セル,データ3番目列,条件セル,データ4番目列,条件セル,データ5番目列,条件セル,データ6番目列,条件セル,データ7番目列,条件セル,データ8番目列,条件セル)-samif(最後のデータ列,条件セル,数字の列)だと思ったのですが、結果違う数字がでます。。。

要は、データ内にDを含んでいるものの「数字」を足したいのですが、
データ内の最初と最後のセルにDを含むものの「数字」は足さない、という事です。
上記データだと１＋１＋３＝５をはじき出したいです。

どうぞよろしくおねがいいたします。

回答No.2

bunjii

同一シートで検証してみました。

>(1)
左側が元のデータで、右側が集計結果です。
J1=IF(COUNTIFS(C:C,">="&I1,C:C,"<"&IF(I2=0,1,I2)),INDEX(D:D,(MAX(($C$1:$C$1000<>"")*($C$1:$C$1000>=$I1)*($C$1:$C$1000<IF($I2=0,1,$I2))*ROW(J$1:J$1000))),1),"")
K1==IF(COUNTIFS($C:$C,">="&$I1,$C:$C,"<"&IF($I2=0,1,$I2)),INDEX(A:A,(MAX(($C$1:$C$1000<>"")*($C$1:$C$1000>=$I1)*($C$1:$C$1000<IF($I2=0,1,$I2))*ROW(K$1:K$1000))),1),"")
K1をL1へコピー
M1=IF(COUNTIFS($C:$C,">="&$I1,$C:$C,"<"&IF($I2=0,1,$I2)),INDEX(F:F,(MAX(($C$1:$C$1000<>"")*($C$1:$C$1000>=$I1)*($C$1:$C$1000<IF($I2=0,1,$I2))*ROW(M$1:M$1000))),1),"")
J1からM1を下へ7行目までコピー
I6セルは5:59までを検出するための指標です。
抽出するデータの配列順が元データと違うので1つの数式を右にコピーできない状態です。

>(2)
条件に合うセルの集計なのでSUMIFS関数で集計可能と考えましたは無理なようです。
SUM関数またはSUMPRODUCT関数になりますがSUM関数の場合は数式の確定時にCtrlとShiftを押しながらEnterキーの打鍵が必要です。
G3=SUMPRODUCT((FIND(A1,D1:D4)>1)*(FIND(A1,D1:D4)<LEN(D1:D4))*E1:E4)

補足

すみません、（２）ですが、ABCDEなどの文字列は全て1セル1データです。

数字 最後のデータ　データ　
1 　　H　　　　 A B C D E F G H
4 K C E H W O I K
2 W I W
1 Q O R Q
3 Y J I O H Y

という様になっています、説明不足で申し訳ありません。また、順番も変わっております。
データに関しては”文字列”であり、アルファベット1文字ではありません。
どうぞよろしくおねがいいたします。