Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


11560 / 13645 ツリー ←次へ | 前へ→

【15319】セル内容をORで調べる方法 みぃこ 04/6/21(月) 16:35 質問[未読]
【15321】Re:セル内容をORで調べる方法 IROC 04/6/21(月) 17:12 回答[未読]
【15323】Re:セル内容をORで調べる方法 ichinose 04/6/21(月) 19:47 回答[未読]
【15360】Re:セル内容をORで調べる方法 みぃこ 04/6/22(火) 14:29 お礼[未読]
【15414】Re:VBAではありませんが… みぃこ 04/6/24(木) 10:43 質問[未読]
【15440】Re:VBAではありませんが… ichinose 04/6/24(木) 20:05 発言[未読]
【15443】Re:VBAではありませんが… ichinose 04/6/24(木) 22:00 発言[未読]
【15448】Re:VBAではありませんが… みぃこ 04/6/25(金) 9:19 お礼[未読]

【15319】セル内容をORで調べる方法
質問  みぃこ E-MAIL  - 04/6/21(月) 16:35 -

引用なし
パスワード
   こんにちわ、いつも参考にさせていただいています。
セル内容をORで調べる指定の仕方があれば、ご教授お願いします。内容は下記の通りです。

事務用品類の購入記録の集計をしています。
A列:発行No.、B列:費目コード、C列:メーカー、D列:品名、E列:品番、
F列:数量、G列:単位、H列:金額、I列:発注日、J列:納入日
という項目を持つ、45行(タイトル行は別)の表を各月ごとにシート分けして
作っています。ご参考までに、そのままコピーできなくて分かりにくいですが、
6月分はこんな感じです。

No.  費目コード  メーカー   品     名       型  番      数量   単位      金額    発注日  納品日
-    5202    コクヨ     KB用紙(A4)      KB−S39N     5    箱     6,750     6/1    6/2
-    5201    キング     カラーインデックス     No.907       1    箱     4,060     6/1    6/3
-    5201    コクヨ     フラットファイル      フ−V10(B)    20    冊      840     6/1    6/3
119   5202    コクヨ     KB用紙(レター)     KB−L31TN    1    箱     4,712     6/2    6/4
     5201    コクヨ     チューブファイル      フ−E633(B)   5    冊     5,200     6/2    6/4
-    5201    コクヨ     フラットファイル      フ−V10(P)   20    冊      840     6/3    6/4
-    5201    キング     テプラテープ        SS12K       1    個      560     6/3    6/4
-    5207    川崎E     社名入り封筒        角2サイズ      100    枚     1,060     6/3    6/10
-    5202    コクヨ     KB用紙(A4)      KB−S39N     5    箱     6,750     6/7    6/9


このうち、B列の費目コードが「5201」のものについての内訳を「集計」シートの
別表に集計するようにしたいのですが、指定の仕方がよく分かりません。

D列の品名に「ファイル」という文字を含めば、「ファイル類」、「インデックス」または「仕切」という文字を含めば「インデックス類」、「ペン」または「マーカー」という文字を含めば筆記具…という風に 品目のカテゴリーごとに月別の金額をまとめたいと思っています。
それで

tuki=Month(Now)
Worksheets(MonthName(tuki)).Activate
For i = 0 To 45
   If Cells(2 + i, 2) = "5201" Then
    If Cells(2 + i, 4) = "*ファイル*" Then
      ファイル類 = ファイル類 + Cells(2 + i, 8)
      ElseIf Cells(2 + i, 4) = "*インデックス*" Or "*仕切*" Then
         インデックス類 = インデックス類 + Cells(2 + i, 8)
         ElseIf Cells(2 + i, 4) = "*ペン*" Or "*マーカー*" Then
             筆記具 = 筆記具 + Cells(2 + i, 8)
             ElseIf Cells(2 + i, 4) = "*消*" Or "*修正*" Then
                修正具 = 修正具 + Cells(2 + i, 8)
                ElseIf Cells(2 + i, 4) = "*メモ*" Or "*ノート*" Then
                    紙製品 = 紙製品 + Cells(2 + i, 8)
                    ElseIf Cells(2 + i, 4) = "*のり*" Or "*メンディング*" Then
                       接着用品 = 接着用品 + Cells(2 + i, 8)
                       ElseIf Cells(2 + i, 4) = "*クリップ*" Or "マグネットボックス" Then
                           クリップ類 = クリップ類 + Cells(2 + i, 8)
                           ElseIf Cells(2 + i, 4) = "*テプラ*" Or "*ネームランド*" Then
                              ラベルテープ = ラベルテープ + Cells(2 + i, 8)
                              Else: その他 = その他 + Cells(2 + i, 8)
     End If
  End If
Next i

Worksheets("集計").Activate
If tuki > 4 Then
  j = tuki - 1
  Else: j = tuki + 11
End If

Cells(34, j).Value = ファイル類
Cells(35, j).Value = インデックス類
Cells(36, j).Value = 筆記具
Cells(37, j).Value = 修正具
Cells(38, j).Value = 紙製品
Cells(39, j).Value = 接着用品
Cells(40, j).Value = クリップ類
Cells(41, j).Value = ラベルテープ
Cells(42, j).Value = その他


ではないかと思って試してみたのですが、別表にはゼロが表示されてしまいます。
ちなみに別表は「集計」シートのB33〜P43、
B列:カテゴリー(34行:ファイル類、35行:インデックス類、36行:筆記具、
37行:修正具、38行:紙製品、39行:接着用品、40行:クリップ類、
41行:ラベルテープ、42行:その他、43行目は各月の合計) 、
C〜N列:月(4〜翌3月)、O列:合計、P列:平均
という表です。

お手数お掛けして申し訳ありませんが、よろしくお願いします。

【15321】Re:セル内容をORで調べる方法
回答  IROC  - 04/6/21(月) 17:12 -

引用なし
パスワード
   オートフィルタで抽出してコピーしては如何でしょうか?

【15323】Re:セル内容をORで調べる方法
回答  ichinose  - 04/6/21(月) 19:47 -

引用なし
パスワード
   みぃこ さん、IROC さん
こんばんは。
>セル内容をORで調べる指定の仕方があれば、ご教授お願いします。内容は下記の通りです。
>事務用品類の購入記録の集計をしています。
>A列:発行No.、B列:費目コード、C列:メーカー、D列:品名、E列:品番、
>F列:数量、G列:単位、H列:金額、I列:発注日、J列:納入日
>という項目を持つ、45行(タイトル行は別)の表を各月ごとにシート分けして
>作っています。ご参考までに、そのままコピーできなくて分かりにくいですが、
>6月分はこんな感じです。
>
>No.  費目コード  メーカー   品     名       型  番      数量   単位      金額    発注日  納品日
>-    5202    コクヨ     KB用紙(A4)      KB−S39N     5    箱     6,750     6/1    6/2
>-    5201    キング     カラーインデックス     No.907       1    箱     4,060     6/1    6/3
>-    5201    コクヨ     フラットファイル      フ−V10(B)    20    冊      840     6/1    6/3
>119   5202    コクヨ     KB用紙(レター)     KB−L31TN    1    箱     4,712     6/2    6/4
>     5201    コクヨ     チューブファイル      フ−E633(B)   5    冊     5,200     6/2    6/4
>-    5201    コクヨ     フラットファイル      フ−V10(P)   20    冊      840     6/3    6/4
>-    5201    キング     テプラテープ        SS12K       1    個      560     6/3    6/4
>-    5207    川崎E     社名入り封筒        角2サイズ      100    枚     1,060     6/3    6/10
>-    5202    コクヨ     KB用紙(A4)      KB−S39N     5    箱     6,750     6/7    6/9
>
>
>このうち、B列の費目コードが「5201」のものについての内訳を「集計」シートの
>別表に集計するようにしたいのですが、指定の仕方がよく分かりません。
>
>D列の品名に「ファイル」という文字を含めば、「ファイル類」、「インデックス」または「仕切」という文字を含めば「インデックス類」、「ペン」または「マーカー」という文字を含めば筆記具…という風に 品目のカテゴリーごとに月別の金額をまとめたいと思っています。
見せていただいたコードを修正しました。


'===============================================================
Sub test()
  tuki = Month(Now)
  Worksheets(MonthName(tuki)).Activate
  For i = 0 To 45
   If Cells(2 + i, 2) = "5201" Then
    With Cells(2 + i, 4)
      If .Value Like "*ファイル*" Then
       ファイル類 = ファイル類 + Cells(2 + i, 8).Value
      ElseIf .Value Like "*インデックス*" Or .Value Like "*仕切*" Then
       インデックス類 = インデックス類 + Cells(2 + i, 8)
      ElseIf .Value Like "*ペン*" Or .Value Like "*マーカー*" Then
       筆記具 = 筆記具 + Cells(2 + i, 8)
      ElseIf .Value Like "*消*" Or .Value Like "*修正*" Then
       修正具 = 修正具 + Cells(2 + i, 8)
      ElseIf .Value Like "*メモ*" Or .Value Like "*ノート*" Then
       紙製品 = 紙製品 + Cells(2 + i, 8)
      ElseIf .Value Like "*のり*" Or .Value Like "*メンディング*" Then
       接着用品 = 接着用品 + Cells(2 + i, 8)
      ElseIf .Value Like "*クリップ*" Or .Value Like "*マグネットボックス*" Then
       クリップ類 = クリップ類 + Cells(2 + i, 8)
      ElseIf .Value Like "*テプラ*" Or .Value Like "*ネームランド*" Then
       ラベルテープ = ラベルテープ + Cells(2 + i, 8)
      Else
       その他 = その他 + Cells(2 + i, 8)
       End If
      End With
    End If
   Next i

  Worksheets("集計").Activate
  If tuki >= 4 Then
   j = tuki - 1
  Else
   j = tuki + 11
   End If
  Cells(34, j).Value = ファイル類
  Cells(35, j).Value = インデックス類
  Cells(36, j).Value = 筆記具
  Cells(37, j).Value = 修正具
  Cells(38, j).Value = 紙製品
  Cells(39, j).Value = 接着用品
  Cells(40, j).Value = クリップ類
  Cells(41, j).Value = ラベルテープ
  Cells(42, j).Value = その他
End Sub
ですが・・・・。

集計と言うシートに予め数式を入力しておく方法もありますよ。
例題として
6月の「ファイル類」なら、セルE34に

=SUM(IF('6月'!$B2:$B47=5201,IF(('6月'!$D$2:$D$47<>SUBSTITUTE('6月'!$D$2:$D$47,"ファイル","")),'6月'!$H$2:$H$47)))

数式は、配列数式ですから、数式入力後、セルから抜けるときはEnterキーではなく、
Ctrl+Shift+Enterキーです。

もうひとつ「インデックス類」なら、セルE35に

=SUM(IF('6月'!$B2:$B47=5201,IF(('6月'!$D$2:$D$47<>SUBSTITUTE('6月'!$D$2:$D$47,"インデックス",""))+('6月'!$D$2:$D$47<>SUBSTITUTE('6月'!$D$2:$D$47,"仕切",""))>0,'6月'!$H$2:$H$47)))

これも配列数式ですから、Ctrl+Shift+Enterキーでセルを確定します。

9*12=108式 程度なら、配列数式でも十分だと思いますし、

それぞれの月のシートの変更も集計シートに反映されますよ!!

確認してみて下さい。

【15360】Re:セル内容をORで調べる方法
お礼  みぃこ E-MAIL  - 04/6/22(火) 14:29 -

引用なし
パスワード
   ichinoseさま、IROCさま、ご教授有り難うございました。
お陰さまで解決できました。助かりました。
数式を入れておくというのは、全然思いつかなかったです。その方が、すぐに反映されていいかも知れませんね。
オートフィルターも抜けてました。ご提案いただき、有り難うございます。

取り急ぎお礼申し上げます。

【15414】Re:VBAではありませんが…
質問  みぃこ E-MAIL  - 04/6/24(木) 10:43 -

引用なし
パスワード
   こんにちわ。ichinoseさまに先日教えていただいた数式についてですが、「その他」の計算式がうまくいきません。

SUM(IF('6月'!$B2:$B47=5201,IF(AND('6月'!$D$2:$D$47<>"*ファイル",'6月'!$D$2:$D$47<>"*ペン*",$D$2:$D$47<>"*マーカー*"..略..),'6月'!$H$2:$H$47)))

かなと思ったのですが、比較するセルを範囲で指定するとtrueとfalseが混在して、最終的な結果はfalseになるようで、計算結果がゼロになってしまいます。IF文の偽の方に計算範囲を入れると、今度は全部足してしまいます。
一行一行見ていけばいけるようですが、それではとてつもなく長くなってしまいます。
お手数お掛けしますが、どう入力すればよいかご教授お願いいたします。

【15440】Re:VBAではありませんが…
発言  ichinose  - 04/6/24(木) 20:05 -

引用なし
パスワード
   ▼みぃこ さん:
こんばんは。

>こんにちわ。ichinoseさまに先日教えていただいた数式についてですが、「その他」の計算式がうまくいきません。
>
>SUM(IF('6月'!$B2:$B47=5201,IF(AND('6月'!$D$2:$D$47<>"*ファイル",'6月'!$D$2:$D$47<>"*ペン*",$D$2:$D$47<>"*マーカー*"..略..),'6月'!$H$2:$H$47)))
「その他」の条件とは
「費目コードが5201で且つ、品名に記述した全ての文字列を含まない金額の合計」
ですよね?
「=SUM(IF('6月'!$B2:$B47=5201,IF(('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"インデックス",""))*('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"仕切",""))*('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"ファイル",""))*・・・・・>0,'6月'!$H$2:$H$47)))」

とすれば、その他の合計は算出できますが(配列数式です)、

私も記述するのが面倒なくらい長くなりそうです・・・。
この問題、確か集計というシートのセルE34〜E41には、それぞれの条件に合致する
金額合計が入っていますよね?
見せていただいたコードからすると、その他は、セルE42ですよね?
だとしたら

=SUM(IF('6月'!B2:B10=5201,'6月'!H2:H10))-SUM(集計!E34:E41)

これも配列数式ですから、セルの確定は、Ctrl+Shift+Enterキーです。

これでその他の金額合計が算出されると思いませんか?

確認してみて下さい。

【15443】Re:VBAではありませんが…
発言  ichinose  - 04/6/24(木) 22:00 -

引用なし
パスワード
   >▼みぃこ さん:
>こんばんは。
>
>>こんにちわ。ichinoseさまに先日教えていただいた数式についてですが、「その他」の計算式がうまくいきません。
>>
>>SUM(IF('6月'!$B2:$B47=5201,IF(AND('6月'!$D$2:$D$47<>"*ファイル",'6月'!$D$2:$D$47<>"*ペン*",$D$2:$D$47<>"*マーカー*"..略..),'6月'!$H$2:$H$47)))
>「その他」の条件とは
>「費目コードが5201で且つ、品名に記述した全ての文字列を含まない金額の合計」
>ですよね?
>「=SUM(IF('6月'!$B2:$B47=5201,IF(('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"インデックス",""))*('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"仕切",""))*('6月'!$D$2:$D$47=SUBSTITUTE('6月'!$D$2:$D$47,"ファイル",""))*・・・・・>0,'6月'!$H$2:$H$47)))」
>
>とすれば、その他の合計は算出できますが(配列数式です)、
>
>私も記述するのが面倒なくらい長くなりそうです・・・。
>この問題、確か集計というシートのセルE34〜E41には、それぞれの条件に合致する
>金額合計が入っていますよね?
>見せていただいたコードからすると、その他は、セルE42ですよね?
>だとしたら
>
>=SUM(IF('6月'!B2:B10=5201,'6月'!H2:H10))-SUM(集計!E34:E41)
訂正です、   ↑b2:b47で      ↑H2:H47でしたね!!

>
>これも配列数式ですから、セルの確定は、Ctrl+Shift+Enterキーです。
>
>これでその他の金額合計が算出されると思いませんか?
>
>確認してみて下さい。

【15448】Re:VBAではありませんが…
お礼  みぃこ E-MAIL  - 04/6/25(金) 9:19 -

引用なし
パスワード
   おはようございます。
そうですよね、合計から引けば話は早いのでした…
何だか「足して計算しなくては!」という意識に凝り固まっていたようで、全く思いつきませんでした。
ご指摘、有り難うございました。

11560 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free