Excel (一般機能)

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

 
(Windows 7 Professional : Excel 2010)
(関数)データ個数と条件計算をVLooKupのようにできますでしょうか?
投稿日時: 19/01/09 18:32:54
投稿者: torao

いつもサイトで勉強をさせていただいております。
 
質問ですがよろしくお願いします。
 
・シートAに作成した帳票の行列範囲に複数の記号が入力してあります。
・上記の値から「個数に対応した条件値」を求めるために下記の関数を使用しています。
(例)
SUM(COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,{"A*","B*","C*"})*{"1:00","2:00","3:00"})
 
これで、合計値が適正に求められております。
 
・しかし、条件によっては、検索値(A・・・)*(1・・・・)の条件が増えると当然式がとても長くなってしまします。
 
そこで、「記号」「数値」を別シートに参照一覧という形で配置してシートAにて合計を求めたいと考えました。
 
実際にそのようなことは可能なのでしょうか。
どうか皆様アドバイスの程よろしくお願いします。

回答
投稿日時: 19/01/09 19:29:28
投稿者: んなっと

●Sheet2にこんな対応表
 
  A   B
1 A* 1:00
2 B* 2:00
3 C* 3:00
4 D* 4:00
 
●現在のシートに
 
=SUMPRODUCT(COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,Sheet2!$A$1:$A$10),Sheet2!$B$1:$B$10)

回答
投稿日時: 19/01/09 19:34:43
投稿者: Suzu

提示頂いた式の 意味は判りませんが、
 
普通に参照してあげれば良いのでは?
 
 
Sheet1
    A
1    1
2    2
3    3
4    4
 
Sheet2
    A
1    10
2    20
3    30
4    40
 
が有ったとして。
 
{=SUM(Sheet1!A1:A4*Sheet2!A1:A4)}
で300が出ますよ
 
1*10+2*20+3*30+4*40
 
 
でもそれだと、数式バーでいちいち ctr + shift + Enter が必要になりますので
 
私なら
・INDIRECT関数
・名前定義
のどちらかを使用すれば 参照先のアドレスを、セルで指定できますので楽と思いますよ。
 
INDIRECT の方は
 
Sheet3
  A1 : Sheet1!A1:A4
  A2 : Sheet2!A1:A4
 
{=SUM(INDIRECT(Sheet3!A1)*INDIRECT(Sheet3!A2))}
 
 
名前定義の方は、
【配列数式のガイドラインと例】
https://support.office.com/ja-jp/article/%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%AE%E3%82%AC%E3%82%A4%E3%83%89%E3%83%A9%E3%82%A4%E3%83%B3%E3%81%A8%E4%BE%8B-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
 
に書かれています。

回答
投稿日時: 19/01/09 19:59:00
投稿者: 半平太

こんな表を作る
 

 行  _A_  ____B____
  1   4   行目まで 
  2  A*          1 
  3  B*          2 
  4  C*          3 
  5  D*        4.5 
  6  E*          6 

 
そして、こんな数式にする。
 
 =SUMPRODUCT(COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,A2:INDEX(A:A,A1))*B2:INDEX(B:B,A1))/24
 
行が増えたら、A1セルで新たな最終行を指定すれば、数式自体は変更する必要がない。
 
※ INDIRECT関数を使えば少し簡単にはなるが、揮発性関数は極力使わない。

投稿日時: 19/01/09 20:23:10
投稿者: torao

皆さまありがとうございます。
 
んなっと さんのアドバイスコードを試してみました。
 
結果はバッチリだったのですが・・・
 
検索値を「実務記号」にした場合は・・・正しい数値が求められませんでした。
 
実務ファイルでの関数
=SUMPRODUCT(COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,勤務マスタ!$C$2:$C$20),勤務マスタ!$D$2:$D$20)
 
(正誤について)
 
記号を1つだけ入力して比較しました(A*の場合と出*の場合)
 
A*(Aのワイルドカード)→答え 7:54
 
出*(漢字+ワイルドカード)→答え 31:45
 
 
(勤務マスタのシート構成ですが)
 
A列=ナンバー
B列=部門名
 
C列=記号・・・A* B*・・・と記号を入力
C列=時間・・・7:45・・・と記号に掛ける数値
 
D列以降にもデータ記入あり
 
上記関数で参照する列範囲はC:Dに変更しました。
 
 
回答者様からなんの計算かわからないとのことですので、以下に説明
 
(計算式の意味ですが)
 
・合計値を週単位で計算しています。
単位は月曜単位で6週分です。
その週番号を4行目に表示させています。
・1週目の合計セル列の4行目に求めたい週番号を表示させています。
 
数式で説明すると
 
COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,記号一覧,記号毎の勤務時間...
 
$E$4:$AI$4・・・データ入力範囲
AQ$4・・・データ範囲のうち、求めたい週番号
$E7:$AI7・・・データ入力範囲に対応する週番号表示
 
 

投稿日時: 19/01/09 21:01:46
投稿者: torao

 半平太 さん  ありがとうございます。
んなっと さん  原因がわかりました。
 
参照元の時間を16進数で入力すれば正しい数値が反映されました。
 
7:45→7.75 で解決しました。
記号がどうのの問題ではなかったようです。
 
半平太さんの /24がヒントになりました。
 
取り急ぎ報告です。
 
もう少しいじってみます。
 
 
 

投稿日時: 19/01/09 21:09:45
投稿者: torao

16進数→10進数でした

回答
投稿日時: 19/01/09 21:11:40
投稿者: んなっと

1:00や2:00に文字列が混ざる可能性があるときは
 
=SUMPRODUCT(COUNTIFS($E$4:$AI$4,AQ$4,$E7:$AI7,Sheet2!$A$1:$A$10)*Sheet2!$B$1:$B$10)
 
の形。これはこれで欠点がありますが。

投稿日時: 19/01/09 22:14:30
投稿者: torao

Suzu さん
んなっと さん
半平太 さん
 
皆さま、導いていただきありがとうございます。
無事、実務ファイルにて動作確認が完了しました。
 
ようやく次のStepに移れます。
感謝感謝です。