ミスチルの大名曲の数々が配信決定!! 31日間無料!!【PR】

いつもお世話になっております。

添付ファイルから

2行目に
2017年4月から始まって、月間ごとに集計したものです。(5年間分)

5行目には
年度別(4月~翌年3月)に集計したものです。

月間表をみて
6行目に年度別に合計できる関数式を教えてください。m(_ _)m


※「年月」はシリアル値です

「年度別の合計」の質問画像

質問者からの補足コメント

  • 書き忘れてしまいました。

    $A$1には、「データ上の日付」があるとします。

    添付画像では
    2018年2月
    としてあります。

    「年度別の合計」の補足画像1
      補足日時:2017/07/11 18:49
  • 申し訳ございません。

    添付画像では
    2018年2月($A$1)
    としてありますが

    2018年4月($A$1)の間違いでした。

      補足日時:2017/07/11 19:23

A 回答 (2件)

セル A1 に 2018/4/1 と入力して、yyyy/mm に書式設定


セル D2 以右は 2017/4/1、2017/5/1、2017/6/1、… と入力して、yyyy/mm に書式設定
セル D5 以右は 2017/4/1、2018/4/1、2019/4/1、… と入力して、yyyy に書式設定
セル D6 に式 =IF($A1>=D5,SUM(OFFSET($D3,,(COLUMN(A1)-1)*12,,12)),"") を入力して、此れを右方にズズーッとオートフィル
「年度別の合計」の回答画像2
    • good
    • 1
この回答へのお礼

mike_gさま、ありがとうございます。

度々説明不足で申し訳ございません。
「年度別の集計」ということで
集計対象は、(4月~翌年3月)になります。

添付画像の5行の「年」は「年度」という意味でカキコいたしました。
言葉足らずで申し訳ございませんでした。m(_ _)m

mike_gさまの式をもとに

=IF(YEAR(EDATE($A$1, -3))>=YEAR(D$5),SUM(OFFSET($D3,,(COLUMN(A1)-1)*12,,12)),"")

で、無事解決することができました。

mike_gさま、ありがとうございました。m(_ _)m

お礼日時:2017/07/12 08:28

※「年月」はシリアル値です


2行目の年月が 毎月の1日目をユーザー定義で年と月に表示しているとして
D6セルに
=SUM(OFFSET($A2,1,MATCH(D5,2:2,FALSE)-1,1,12))
で右へコピーでは
2行目を右へ検索して、12列分の合計と云った感じです。
    • good
    • 1
この回答へのお礼

hallo-2007さま、ありがとうございます。

自分で質問しておきながら、混乱して訳がわからなくなっていました。
特に、1月、2月、3月の部分の扱いのところです。

=YEAR(EDATE(A1, -3))

しかし、ネット上にあったこの式で疑問を解消することができ、頭の整理ができました。


>=SUM(OFFSET($A2,1,MATCH(D5,2:2,FALSE)-1,1,12))
>で右へコピーでは
>2行目を右へ検索して、12列分の合計と云った感じです。

なるほど、このような解決方法があったんですね。
目から鱗が落ちたような心境です。

hallo-2007さま、
ご教授ありがとうございました。m(_ _)m

お礼日時:2017/07/11 20:56

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aと関連する良く見られている質問

QExcel条件付き書式での指定した日付を塗りつぶす方法について

お世話になっております。
添付画像を見本と致します。
BセルからCセルの15日前を塗りつぶし処理をしたいのですがどの様に条件付き書式を
構成すればよろしいのかご教示をいただきたい内容になります。

宜しくお願い致します。

Aベストアンサー

( ‥)ン? 「2017/7/15」前までを塗りつぶしたいという事でいいのかな?
だとして・・・
セルB4~F4までを範囲してしておき、条件付き書式より「新しいルール」から
「数式を使用して・・・」で、入力欄に 「=B3<$E$3」の式を入れ、
書式より塗りつぶしの色を設定→「OK」→「OK」にすると希望通りに。

Qコピー&ペーストではなく数式やエクセルの機能を使ってデータを反映させたい。

下記の「表①」のエクセルデータを、「表②」に反映させたいです。
「表②」のB3セルに、数式を入れて、「表①」のログイン・ログオフ時刻を「表②」へ
自動反映させたいのですが、どのような式を入れたらいいかがわかりません。

現状は、目視とコピー&ペーストで表①→表②へ貼りつけていますが、
スタッフの数が多いのとシフト勤務で出勤日数も時間もバラバラで、作業が大変です。

また、早くて正確なら他の方法でもいいです。
よろしくお願いします。


表①
A B C D E
1 氏名 日付 ログイン 日付 ログオフ
2 田中 舞子 2017/5/16 8:21:35 2017/5/16 17:34:38
3 田中 舞子 2017/5/17 8:22:40 2017/5/17 17:35:00
4 田中 舞子 2017/5/18 8:28:02 2017/5/18 17:35:55
5 田中 舞子 2017/5/19 8:22:09 2017/5/19 17:34:18
6 田中 舞子 2017/5/22 8:18:03 2017/5/22 17:33:13
7 三田 建造 2017/5/23 8:19:55 2017/5/23 17:39:44
8 三田 建造 2017/5/25 8:21:03 2017/5/25 17:38:04
9 三田 建造 2017/5/26 8:17:45 2017/5/26 17:31:54
10 三田 建造 2017/5/29 8:19:42 2017/5/29 17:32:39
11 三田 建造 2017/5/30 8:20:12 2017/5/30 17:33:26
12 牧 重三 2017/5/31 16:18:33 2017/5/31 23:31:36
13 牧 重三 2017/6/1 16:20:08 2017/6/1 23:35:33
14 牧 重三 2017/6/2 17:18:35 2017/6/2 23:41:05
15 牧 重三 2017/6/5 15:20:00 2017/6/5 23:31:54
16 牧 重三 2017/6/6 19:22:05 2017/6/6 23:41:14
17 長岐 知美 2017/6/7 8:25:48 2017/6/7 17:34:38
18 長岐 知美 2017/6/8 8:26:24 2017/6/8 17:36:52
19 長岐 知美 2017/6/9 8:26:01 2017/6/9 17:34:08
20 長岐 知美 2017/6/12 8:19:24 2017/6/12 17:32:02
21 篠田 亜紀 2017/5/21 8:19:14 2017/5/21 17:32:29
22 柿田 輝未 2017/5/28 7:34:55 2017/5/28 17:17:54
23 荒元 耕平 2017/6/4 7:32:49 2017/6/4 17:32:50
24 佐田 容子 2017/6/11 8:18:35 2017/6/11 17:41:05
25 藤島 澄人 2017/6/12 8:25:15 2017/6/12 17:59:55
26 藤島 澄人 2017/6/13 8:45:31 2017/6/13 17:41:06
27 藤島 澄人 2017/6/14 8:05:34 2017/6/14 17:28:14
28 藤島 澄人 2017/6/15 8:13:25 2017/6/15 17:30:24


表②
   A B C D E F G
1 田中 舞子 三田 建造 牧 重三 ・・・・
2 日付 ログイン ログオフ ログイン ログオフ ログイン ログオフ
3 2017/5/16 B3
4 2017/5/17
5 2017/5/18
6 2017/5/19
7 2017/5/20
8 2017/5/21
9 2017/5/22
10 2017/5/23
11 2017/5/24
12 2017/5/25
13 2017/5/26
14 2017/5/27
15 2017/5/28
16 2017/5/29
17 2017/5/30
18 2017/5/31
19 2017/6/1
20 2017/6/2
21 2017/6/3
22 2017/6/4
23 2017/6/5
24 2017/6/6
25 2017/6/7
26 2017/6/8
27 2017/6/9
28 2017/6/10
29 2017/6/11
30 2017/6/12
31 2017/6/13
32 2017/6/14
33 2017/6/15

下記の「表①」のエクセルデータを、「表②」に反映させたいです。
「表②」のB3セルに、数式を入れて、「表①」のログイン・ログオフ時刻を「表②」へ
自動反映させたいのですが、どのような式を入れたらいいかがわかりません。

現状は、目視とコピー&ペーストで表①→表②へ貼りつけていますが、
スタッフの数が多いのとシフト勤務で出勤日数も時間もバラバラで、作業が大変です。

また、早くて正確なら他の方法でもいいです。
よろしくお願いします。


表①
A B ...続きを読む

Aベストアンサー

表①のB列ですが、シリアル値「2017/5/16 8:21:35」を表示形式で「2017/5/16」と表示していませんか?もしそうであればNGです。B2セルは時刻の部分をゼロにしてください。

2人ログオンする日がある件ですが、残念ながら、この方式では実現できません。
No.1 chonamiさんの案が一番簡単そうです。たぶん、こんな感じを想定していると思われます。

【B3セル】=SUMIFS(表①!$C:$C,表①!$A:$A,B$1,表①!$B:$B,$A3)
※C3セルは、上記を参考にご自分で考えてみて下さい。

Qエクセルで三つの条件での参照する方法。 お世話様です。 コード 項目 7月 8月 9月 10月 11

エクセルで三つの条件での参照する方法。
お世話様です。

コード 項目 7月 8月 9月 10月 11月 12月
1234 野菜 8 11
9876 肉類 5 4
小計 5 8 11 4
2345 魚類 12 8
3456 麺類 1 6
小計 1 6
合計

このようなデータがあります。
コードは1000くらいあります。
小計も100くらいあります。

別シートにこれよりも項目が多い表があります。
この表に上記データを反映させたいです。

作業列を作りコードと項目を&で合体させ
ます。
それを検索値とし、別シートの7月の列にvlookup関数にします。
コードの列をフィルタ掛けし小計、合計を非表示にします。
貼り付け。

これを月別に貼り付けしようと思います。
他に効率良い方法ありますか?
言葉足らずな点は補足します。
よろしくお願いいたします。

エクセルで三つの条件での参照する方法。
お世話様です。

コード 項目 7月 8月 9月 10月 11月 12月
1234 野菜 8 11
9876 肉類 5 4
小計 5 8 11 4
2345 魚類 12 8
3456 麺類 1 6
小計 1 6
合計

このようなデータがあります。
コードは1000くらいあります。
小計も100くらいあります。

別シートにこれよりも項目が多い表があります。
この表...続きを読む

Aベストアンサー

素朴な確認をば。
小計行の数値 5、8、11、4 はどのセルとどのセルとの小計になっているの?
「あれは、単に書いたまでで、実はデタラメ!」なんて言わないでネ。回答者は真剣なので。

QExcelでコピーして貼り付けるときの処理の速さ

下記3つのコードを作りました。
sample3は、コピー、貼り付けの操作を1回で行っているので、一瞬で処理が終わります。
しかし、sample1では数十秒もかかってしまいます。また、sample2については、1秒程度です。sample1とsample2とでは、どうしてこのような処理速度の違いが出るのでしょうか。

実際の使用場面では、複数のセルをまとめてコピーする事はなく、条件に該当するセルが見つかったら、そのデータを他のシートの指定したセルに値のみを貼り付ける使い方をします。
その場合、処理速度の短縮をするには、下記コード、または、その他の方法でどのようにコピー貼り付けをするのがいいのでしょうか。

ご回答よろしくお願いします。

Sub sample1()
Dim i As Integer
For i = 1 To 10000
Cells(i, 1).Copy
Cells(i, 2).PasteSpecial Paste:=xlPasteValues
Next
End Sub

Sub sample2()
Dim i As Integer
For i = 1 To 10000
Cells(i, 2) = Cells(i, 1)
Next
End Sub

Sub sample3()
Dim i As Integer
Range(Range("a1"), Range("a1").End(xlDown)).Copy
Range("b1").PasteSpecial Pase:=xlPasteValues
Application.CutCopyMode = False
End Sub

下記3つのコードを作りました。
sample3は、コピー、貼り付けの操作を1回で行っているので、一瞬で処理が終わります。
しかし、sample1では数十秒もかかってしまいます。また、sample2については、1秒程度です。sample1とsample2とでは、どうしてこのような処理速度の違いが出るのでしょうか。

実際の使用場面では、複数のセルをまとめてコピーする事はなく、条件に該当するセルが見つかったら、そのデータを他のシートの指定したセルに値のみを貼り付ける使い方をします。
その場合、処理速度の短縮をする...続きを読む

Aベストアンサー

sample1 と sample2 の違い
Copyは、セルの塗りつぶし等セルの情報の全てメモリーに入れます。
処理が2行あります。
Cells(i, 2) = Cells(i, 1) は
Cells(i, 2) .Value= Cells(i, 1).Value を省略していますので値だけの情報です。
処理も一行ですんでいます。

sample1、sample2 と sample3 の違い
繰返しがありません。

最もパソコンに負担をかけないのは
Sub sample3()
Range("B1:B" & Range("a1").End(xlDown).Row).Value=Range("A1:A" & Range("a1").End(xlDown).Row).Value
End Sub
では、ないでしょうか。
これでも、Range("a1").End(xlDown).Row を二度実行しているので
変数に入れた方が良いのか、どうかは微妙な所です。

Qエクセルの関数で

以下の算式を満たすエクセルの関数を教えていただけないでしょうか。

セルA1(①) セルA2(②) セルA3(③) セルA4(④) セルA5(⑤)


③=①-②
④=③÷② 小数点3位未満切り捨て
⑤ ④が0.05超の場合  0.09+((④-0.05)×0.3  (上限0.1) 小数点3位未満切り捨て
④が0.05以下の場合 0.09-((0.05-④)×0.1  (下限0.06) 小数点3位未満切り捨て


ここの⑤を満たす式を教えてください。

例えば①531,116,991②646,729,949のとき⑤は0.067となります。

よろしくお願いいたします。

Aベストアンサー

0.05超の場合の式…⑥
0.05以下の場合の式…⑦
IF(④>0.05,IF(⑥>0.1,ROUNDDOWN(⑥,×),0.1),IF(⑦<0.06,0.06,ROUNDDOWN(⑦,×)))

×部分はうろ覚えです、小数点第三位なら「3」…ですかね…?

以上でどうでしょう

Qエクセル(2010)、複数の条件に合うデータの抽出について

勤務別、勤務日別のデータファイルを作成し、出勤簿を作成したいです。
データファイルに氏名別、日付別に勤務時間数を記載しました。
そのデータを基に出勤簿を作成したいです。
早番で1月1日に勤務した人が「Aさん」、遅番が「Dさん」、
同じように、早番で1月3日に勤務した人が「Bさん」、遅番が「Cさん」、
早番で1月5日に勤務した人が「Aさん」、遅番が「Dさん」と氏名が自動的に入力する計算式を教えてください。
宜しくお願いします。

Aベストアンサー

銀鱗さんの提示された数式を踏まえて(エラー処理なし)
B12セルに
=INDEX($B4:$B5,MATCH(0,INDEX($C$4:$E$5,,MATCH(B$11,$C$3:$E$3,0)),-1))
B13セルに
=INDEX($B6:$B7,MATCH(0,INDEX($C$6:$E$7,,MATCH(B$11,$C$3:$E$3,0)),-1))

自分としては、↓だったけど。
=INDEX($B4:$B5,MATCH(99,INDEX($C$4:$E$5,,MATCH(B$11,$C$3:$E$3,0))))

肝となっているMATCH関数の近似値検索(照合の型が1か省略、および-1)は、
ヘルプに掲載されているものではなく 過去の達人が見つけたものだと思っています。
二分探索木を検索動作を利用したものと言われていたと思います

Q西暦をS〇〇年〇月〇日へ

1964/11/10と入力していましてこれを
S39年11月10日と表示したいのですがどうすればいいのか教えて下さい。

Aベストアンサー

セルの書式設定でユーザ定義を加えれば、
お望みどおりの表示形式が作成できます。

Qエクセル2016 にてご質問をお願いいたします。

シート1、シート2、シート3が3個あります。

全てのシートのA1セルに同じ文字や数字を入れたいと思っております。

シート1でA1セルに入力したものがシート2、シート3も同じ数字になる。
シート2でA1セルに入力したものがシート1、シート3も同じ数字になる。

どのページでA1の文字を入力や修正を行うとすべてのシートが同じになるようにしたいです。

どうかよろしくお願いいたします。

Aベストアンサー

「ThisWorkbook」モジュールのWorkbook_SheetChangeイベントプロシジャに下記のコードを書いちゃうってのは、いかがでしょう。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Sh.Range("A1"))
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Sh.Name
Case "Sheet1"
Sheets("Sheet2").Range("A1").Value = r.Value
Sheets("Sheet3").Range("A1").Value = r.Value
Case "Sheet2"
Sheets("Sheet1").Range("A1").Value = r.Value
Sheets("Sheet3").Range("A1").Value = r.Value
Case "Sheet3"
Sheets("Sheet1").Range("A1").Value = r.Value
Sheets("Sheet2").Range("A1").Value = r.Value
End Select
Application.EnableEvents = True
End Sub

「ThisWorkbook」モジュールのWorkbook_SheetChangeイベントプロシジャに下記のコードを書いちゃうってのは、いかがでしょう。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Sh.Range("A1"))
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Sh.Name
Case "Sheet1"
Sheets("Sheet2").Range("A1").Value = r.Value
Sheets("Sheet3").Range("A1").V...続きを読む

Qエクセル 条件付き書式の色付けルールに関して

a1=〇
a2=△
a3=□

b1=トラ
b2=ネコ
b3=イヌ

c1~c5までは以下のように動物名が入力されています。
c1=サル
c2=ネコ
c3=トラ
c4=イヌ
c5=ウサギ

d1~d5まではそれぞれの動物に対する記号が入力されています。
d1=×
d2=〇
d3=☆
d4=□
d5=△

a1~a3,c1~c5の入力値は固定で、b1~b5とd1~d5は変動します。

条件付書式で、
「a1の〇とa2の△とa3の□を、d1~d5から探して、そこと一致した動物名(c1~c5)がb1~b3にいた場合、d1~d5に色付けする(〇は赤色△は黄色□は青色)」というような条件付けは可能でしょうか?

上記のような場合は、d2が赤色、d5が黄色、c4が青色に塗りつぶされるようにしたいです。

Aベストアンサー

数式A: =AND(COUNTIF(C:C,VLOOKUP(D1,A$1:B$3,2,FALSE)),D1="〇")
数式B: =AND(COUNTIF(C:C,VLOOKUP(D1,A$1:B$3,2,FALSE)),D1="△")
数式C: =AND(COUNTIF(C:C,VLOOKUP(D1,A$1:B$3,2,FALSE)),D1="□")

QExcel 範囲条件該当で隣のセルを表示する

初めまして。
検索してみましたが、上手いことヒットしなかったので質問させて下さい。
Excel2016です。

【図解】

   A   B 
1 115 りんご
2 226 みかん
3 161 りんご
4 352 ぶどう


といった表があります。
この表を元に、別シートのC1セルに
「A列に100以上150未満の数値があれば、その値のB列のセルの値を表示する(この場合115が範囲内の数値なので、別シートC1セルに“りんご”と表示したい)」
とするには、どういった式を組めばいいでしょうか?
というか、これは可能でしょうか?

よろしければご教示願いたいです。

Aベストアンサー

No.3です。

>100から150の範囲内の数字はA列に1つ(この場合115一つのみ)しかないです。
A列に条件が一致するデータは必ず一つしかない!というコトであれば・・・
=INDEX(Sheet1!B1:B1000,SUMPRODUCT((Sheet1!A1:A1000>=100)*(Sheet1!A1:A1000<150),ROW(A1:A1000)))

としてみてください。(配列数式の操作は不要です)

※ エラー処理をしていませんので、万一該当データが存在しない場合は
B1のデータが返ります。

>今度は151から200までを表示したいです。
数式内の「100」と「150」部分を変更してください。
以上、以下、未満の使い方は数式内の等号で調整します。

※ どこかのセルに○○以上 △△未満 の数値を入れるようにしておけば
そのセルを直接参照する数式で対応できます。m(_ _)m


人気Q&Aランキング