EXCELで1つの範囲に対して複数条件がある場合のカウントについて教え

質問

EXCELで1つの範囲に対して複数条件がある場合のカウントについて教えてください。

勤務表で「休暇入力(Ａ列)」があり、そこにはリストで「有休」、「代休」、「振休」などを取得したら選択入力します。隣は「遅刻早退入力(Ｂ列)」があり、リストで「遅刻」や「早退」を選びます。

この時、以下の条件でのカウントを教えてください。
------------------------------------------------------------------------------　
?(Ａ列)が「有休」もしくは「代休」もしくは「振休」だった場合で且つ、
?(Ｂ列)が「遅刻」もしくは「早退」の日数
------------------------------------------------------------------------------

状況としては、遅刻や早退に有給休暇を充当した日をカウントしたいのです。
出来るだけ1つのセルに計算式を入れるだけの形が希望です。

よろしくお願いします！

回答No.7

MackyNo1 · ベストアンサー

１つの式で表示するなら以下のような数式になります。

=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$100,{"有休";"代休";"振休"},0))*ISNUMBER(MATCH($B$2:$B$100,{"早退";"遅刻"},0)))

回答No.6

NOBNNN

#4 です。
＞＞合計するのではなく、1つのセルに出したい値の合計が一気に表示される計算式を探しております。

関数ではできません。

DSUM関数
■ DSUM関数で条件に合うものを合計
http://kokoro.kir.jp/excel/dsum.html

ほか SUMIF,COUNTIF いずれも式を入力し結果を表示するセルが必須だからです。

マクロ、もしくは手動による計算で可能です。

手動で行うにはオートフィルタ機能　を使ってデーター検索し、結果を人が集計してできます。
■ オートフィルタ機能：　http://kokoro.kir.jp/excel/auto-filter.html

もし、関数とマクロ以外となれば　オートフィルタ機能しかありません.

回答No.5

noname#192430

回答No2でDCOUNT関数を紹介した者です。（回答No1はタイプミスです。申し訳ありませんが無視しておいてください。）

他の方の回答にもあるように作業用の値等を入れた列を非表示にすれば、見た目が変わりませんので是非ご検討ください。

なお会社の事情により既存の計算式等との共存が必要な場合、見た目は変えなくても計算方法等は全般的に大幅な見直しが必要になるかもしれません。煩わしい大手術は避けたいのが本音かもしれませんが・・・。

回答No.4

kmetu

> 会社の決まった勤務表のフォーマットなので、計算式の為の枠をあまり増やすことが出来ないのです。

非表示にしてしまえばフォーマットも崩れないので
なんら問題はないと思いますが…
実際に利用している列のかなり右のほう(使わないであろう場所)に
作業列を設けて非表示にしておけばABCなどの表示も
かなりの部分まで順に並んだ状態でなんら違和感なく使えますよ。

回答No.3

kmetu

ひとつのセルではありませんが

C1列に
=IF(AND(OR(A1="有休",A1="代休",A1="振休"),OR(B1="遅刻",B1="早退")),1,"")
と入力して下方向に必要なだけフィル
C列を非表示
カウントを表示したいセルに
=COUNT(C:C)
とするのでは駄目でしょうか。

補足

ご回答をありがとうございます。

会社の決まった勤務表のフォーマットなので、計算式の為の枠をあまり増やすことが出来ないのです。出来れば日々毎の値をどこかに設定してそれを合計するのではなく、1つのセルに出したい値の合計が一気に表示される計算式を探しております。

我がままですみません。

回答No.2

NOBNNN

Excel には　条件に合ったセルを数える、COUNTIF関数　があります。
　http://kokoro.kir.jp/excel/countif.html

しかしながら　今回のように数字が　遅刻、早退などの値が　時間で変化する場合は使えません
1としてかぞえるだけです。　通常は以下の通りです

>>Ａ列)が「有休」もしくは「代休」もしくは「振休」

まずは　これを判定するセルの列、たとえばこれをH列（有給日数）として　該当した場合は値を１とします。　=IF(A1=「有休」or「代休」or「振休」,1 ,0)
実際の式はそれぞれセルの名前とか入れてください。

＞＞?(Ｂ列)が「遅刻」もしくは「早退」の日数
　同様に　これを判定するセルの列、たとえばこれをI列（欠勤日数）として　
　該当した場合は値を１とします。

それぞれを最終行に合計する計算式　ex. =ＳＵＭ(H1:H50) , ex . =ＳＵＭ(I1:I50) とします

この合計を元に　差分を計算するようにすれば　ご希望通りになります。

実際には遅刻」もしくは「早退」は時間数で計算するので　0.5日　とかになるとは思いますけど。

参考URL：

http://kokoro.kir.jp/excel/if-and.html

補足

ご回答をありがとうございます。

会社の決まった勤務表のフォーマットなので、計算式の為の枠をあまり増やすことが出来ないのです。出来れば日々毎の値をどこかに設定してそれを合計するのではなく、1つのセルに出したい値の合計が一気に表示される計算式を探しております。

知恵も無いのに我がままでばかりですみません。

確かにNOBNNN様が想定されている通り、実際には数値として「全休」は数値の「１」、「半休」、「遅刻」、「早退」は「０．５」として処理しています。
社則の改編や上司(エクセル大好き！でもVBA、マクロ嫌い)からの要望に応え、なおかつ出来るだけフォームの形式は変えないようにしないといけないので激しく重たいエクセルになっています・・・(泣)

回答No.1

noname#192430

DCOUNT関数でいけそうですが、条件が込み入っているので、少々作業が必要です。

１．先ず勤務表で「休暇入力」「遅刻早退入力」以外に必ず何か記入されている列はありますか？「日付」の列などがあればそれでいいと思いますが、無ければ増設しておいてください。

２．どこでもいいので、空いている場所に7行2列にわたって下記のように入力してください。

休暇入力　遅刻早退入力
有休　　　遅刻
有休　　　早退
振休　　　遅刻
振休　　　早退
代休　　　遅刻
代休　　　早退

３．ここまで出来た状態でカウントを出したい場所に以下の式を入力します。

=DCOUNT(勤務表全体の範囲,"日付",上記２で入力した範囲)

※範囲は$付きの絶対指定で記載してください。
※上記１で述べた列の見出しは「日付」ということにしていますが、質問者様の状況に合わせて読み替えてください。

補足

早々のご回答をありがとうございます。

会社の決まった勤務表のフォーマットなので、計算式の為の枠をあまり増やすことが出来ないのです。出来れば日々毎の値をどこかに設定してそれを合計するのではなく、1つのセルに出したい値の合計が一気に表示される計算式を探しております。

我がままですみません。