柔軟に働き方を選ぶ時代に必要なこと >>

以下の基準セルを右方向にフィルコピーした際に、参照範囲が99ずつ増えるような式を組みたいのですが、ご教示いただけないでしょうか。
お手数おかけしますが、なかなか上手くいかご協力いただけますと幸いです。

基準となるセルの数式
=SUMIF('2019'!$F$1200:$F$1299,設定シート!$C$2,'2019'!$J$1200:$J$1299)

基準となるセルの右横のセルの数式
=SUMIF('2019'!$F$1300:$F$1399,設定シート!$C$2,'2019'!$J$1300:$J$1399)

A 回答 (3件)

INDIRET関数が便利です。



なお、最初のセルの行番号が 1200であることを示す補助行が必要です。
補助行を1行目、数式を2行目とします。

A1: 1200
B1: =A1+100

A2(基準となるセルの数式):
=SUMIF(INDIRECT("2019!$F$"& A1 &":$F$"& A1+99),設定シート!$C$2,INDIRECT("2019!$J$"& A1 &":$J$"& A1+99))

B2(基準となるセルの右横のセルの数式):
A2を入力後、A2をセルコピーすれば作成可。以下になれば問題なし

=SUMIF(INDIRECT("2019!$F$"& B1 &":$F$"& B1+99),設定シート!$C$2,INDIRECT("2019!$J$"& B1 &":$J$"& B1+99))

ここまで完成したら、
続き(C1&C2以降)は、B1&B2を選択して横にコピーすれば伸ばせます。
    • good
    • 0
この回答へのお礼

ご丁寧にありがとうございます!
解決しました。

お礼日時:2019/02/26 14:03

質問の例ではどう見ても100ずつ増えているように見えるんですけど、


ひょっとして「1200から1299」「1300から1399」を「99増えている」と表現しているのでしょうか。
これ、100増えてますよ。範囲も100です。

・・・本題・・・

INDIRECT関数で範囲を指定するかな。
あるいは
OFFSET関数かな。

・・・
横という事ですので、基本はCOLUMN関数を使うことになるでしょう。
試しに
 =1100+100*COLUMN(A1)
と入力したセルを横にフィルでコピーしてみてください。
1200と表示された右のセルには1300と表示されるようになるはずです。
更にその右側にフィルすると1400となるはずです。

COLUMN関数が返す値が分からなければ、COLUMN関数だけの式を使って結果を確認してください。
 =COLUMN(A1)
です。
この数式が入力されたセルをフィルして結果を確認しましょう。

・・・
INDIRECT関数は文字列を範囲として認識させる関数です。
 INDIRECT("A1" & ":" & "B1")
とすると
 「A1:B1」
を範囲として返します。
 =SUM(INDIRECT("A1"&":"&"B1"))
なら、
 =SUM(A1:B1)
の結果が返ります。

A1セルに
 「100」
B1セルに
 「199」
と文字が入力されていて
 =SUM(INDIRECT("A"&A1":"&"A"&B1))
であれば、
 "A"&A1":"&"A"&B1

 "A"& 1 &":"&"A"& 100
なるので 
 =SUM(A1:A100)
という事になります。

この「1」とか「100」の部分をCOLUMN関数を使った式に置き換えてしまえばいいってこと。

・・・
OFFSET関数でも同様です。
OFFSET関数の行を示す数値をCOLUMN関数を使った式で与えてやればいい。

試行錯誤してみましょう。


・・・余談・・・

答えの数式を示しても良いのですが、それでは「分かったつもり」になるだけで質問者さんは同様の問題を解決できないままになります。
自力で問題を解消できるようにならないと「問題の解決」にはなりませんよね。
目の前にある問題は解決したように見えても、それは「問題の先送り」にすぎません。

試行錯誤しても分からない時は、どのような数式を作ったのかを示してアドバイスを求めると良いでしょう。
がんばれ。
    • good
    • 0

ごめんイメージがつかめない。


サンプル画像見せて。
どーゆーことがしたいのかな?
    • good
    • 0
この回答へのお礼

解決しました。
説明が不足しており失礼いたしました。

ありがとうございました!

お礼日時:2019/02/26 14:02

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

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

Q4142から653347と飛んでます。 これを4143からちゃんと並ぶにはどうすればいいでしょうか?

4142から653347と飛んでます。
これを4143からちゃんと並ぶにはどうすればいいでしょうか?

Aベストアンサー

これ、9から70に飛んでるよね。

①シート全体を選択
②どの行でもいいので右クリック
③再表示を選択

開いたかな?

Qエクセルで並んでる文字から1つずつ並べたい

こんにちは。
仕事でエクセルを使う機会が増えそれに関する質問です。
宜しくお願いします。

縦に文字がたくさん並んでいるところから文字を抽出したいのです。
例えば

東京都
新潟県
兵庫県
東京都
千葉県
千葉県
東京都
熊本県

と並んでる文字から文字がかぶらないように

東京都
新潟県
兵庫県
千葉県
熊本県

と文字を別の場所にならべたいのです。
どうしたらよいでしょうか。
宜しくお願い致します。

Aベストアンサー

重複削除方法を知りたいってことですよね?
こうだよ。リボンのデータ>重複削除

Qエクセル リストと完全一致するセルに色をつける

シート1のA列とB列に
aaa ccc
bbb ggg
ccc kkk
ddd ooo
と言うリストがあって、A1〜A4はAチーム、B1〜B4まではBチームと名前を付けています
シート2にAチームのリスト4個が続いているものがあればセルを赤、Bチームのリスト4個が続いているものがあればセルを黄色に塗りたいです
AチームとBチームの中には同じ品番がある時もあります
条件付き書式で設定は出来るでしょうか?

Aベストアンサー

(´・ω・`)
”○” の数を数えるんじゃないんだよなあ。

・・・本題・・・

条件付き書式ですよね。

シート2のリストの並び順は
 aaa
 ccc
 bbb
 ddd
では「Aチーム」と認識しないという事でよろしいでしょうか?
ならば、とても簡単です。

シート2の一覧において、

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

という範囲について調べれば良いという事。

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル

 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4

 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5

 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

の4パターンについてそれぞれ調べれば良いだけ。

自分なら
 aaa-bbb-ccc-ddd
のようにシート1から文字列を作り、それが調べるセルで同じパターンになるかを調べます。
シート1はA5セルから、シート2はA11セルからデータが入力されているなら、

 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A11 & A12 & A13 & A14
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A12 & A13 & A14 & A15
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A13 & A14 & A15 & A16
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A14 & A15 & A16 & A17

という条件になる。
この4つのうちの一つでも条件を満たせばセルに赤色を付ければいい。
「Bチーム」についても同様にすればいいので、
この場合、8つの条件式を設定することになります。

面倒でもこの考え方ができていないと、ちょっと条件が変わっただけで対処できずに終わります。
冒頭で「並び順」について書きましたが、並び順がシート1のリストの通りでなくとも色を付けたい場合でも、この考え方は必要ということです。

・・・
ちなみに厄介なのが、どちらのチームにも「ccc」がいるというところかな。
これが無ければ違う方法でシンプルにできるんですけどねえ。

(´・ω・`)
”○” の数を数えるんじゃないんだよなあ。

・・・本題・・・

条件付き書式ですよね。

シート2のリストの並び順は
 aaa
 ccc
 bbb
 ddd
では「Aチーム」と認識しないという事でよろしいでしょうか?
ならば、とても簡単です。

シート2の一覧において、

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

という範囲について調べれば良いという事。

 判定するセル1
 判定するセル2
 判定するセル3
 色を付け...続きを読む

QExcelでフィルターを使わずに抽出するにはどうしたらよいのでしょうか

お世話になります。
画像のようにしたいです。
よろしくお願いいたします。

Aベストアンサー

こんな感じでしょうか(Excelっぽくないけど)。
ちなみに、最初の画像のレイアウトを想定して作っています。列の間隔はご自分で調整してください。

Sub sample()
Dim i As Long
Dim c As Long
Dim r As Long
c = 4
r = 2
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(r, c).Value = Cells(i, "A").Value
Cells(r, c + 1).Value = Cells(i, "B").Value
If Cells(i, "A").Value = Cells(i + 1, "A").Value Then
r = r + 1
Else
c = c + 2
r = 2
End If
Next i
End Sub

こんな感じでしょうか(Excelっぽくないけど)。
ちなみに、最初の画像のレイアウトを想定して作っています。列の間隔はご自分で調整してください。

Sub sample()
Dim i As Long
Dim c As Long
Dim r As Long
c = 4
r = 2
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(r, c).Value = Cells(i, "A").Value
Cells(r, c + 1).Value = Cells(i, "B").Value
If Cells(i, "A").Value = Cells(i + 1, "A").Value Then
r = r + 1
Else...続きを読む

Qエクセル売価設定の変更

エクセル初心者です。

500円未満で仕入れた商品を ÷0.3の売価にし
500円以上で仕入れた商品を ÷0.5の売価なるように
数式を入力したいのですが、教えていただけますでしょうか?

Aベストアンサー

また「①未入力は空白で表示」を見落としてました。
=IF(A1="","",ROUND(A1/IF(A1<500,0.3,0.5),-1)) です。

Qエクセルの時間入力について質問です。 出勤簿を作っています。時間を入力するセルを25時間以上になる場

エクセルの時間入力について質問です。
出勤簿を作っています。時間を入力するセルを25時間以上になる場合を想定してユーザー定義の「[h]:mm」とすれば良いと調べてやってみたのですができません。

8:00→19200:00
17:00→40800:00

すべて24時間を掛け算してる感じになります。なので他にも設定する場所があるのでしょうか?調べても出てこないのでわかる方教えてください。初心者です。よろしくお願いいたします。

Aベストアンサー

そりゃ、8時間を800日と入力すればそうなるに決まっている。
”800”と入力しているでしょ?

”800”と入力して”8:00”と表示するようになっているなら、そのセルの表示形式を確認してください。
恐らく
 0":"00
となっているはずです。
これは「800」と言う数字を百の位で分けて表示しているだけで「時間」ではありません。

そもそもこのようになっていれば「25時」なら ”2500” と入力するだけで「25:00」と表示されますよ。

Qエクセル counta関数について

例えばA1をカウントしてB1に表示するときにC1に特定の文字(例えばX)があるときにA1に何が入力されていてもカウントされずB1に0と表示されるようにするにはどうしたらいいでしょうか?

Aベストアンサー

こんにちは

ご質問の文章のまま式にすれば、
 =IF(C1="X",0,COUNTA(A1))
とか。

意味を考えて別の計算にすれば、
 =COUNTA(A1)*(C1<>"X")
でも同様の結果になります。

他にも、いろいろな式が考えられることと思います。

Qエクセルでの転記の方法について

指定した範囲内で、条件を満たすセル(①)と同列の別のセル内の文字を、①と同行の別のセルに転記する方法はありますか?
検討違いな質問をしていたら申し訳ありません。

画像を添付しています。
パソコンの画面を撮影したため、見辛くてすみません。
画面右の文字が読みづらいと思いますので、こちらに記載します。
「←黄色の範囲内で●がついている人の名前を(この場合は”山田“)、水色のセルに転記したい」

Aベストアンサー

G3: =IFERROR(INDEX(B$2:F$2,MATCH("●",B3:F3,0)),"None")

Qエクセルで空白行に上の行のデータをコピー&ペーストする

excel2007を使用してます。

1行目に入力されているデータを2行目以降の空白になっている行にコピー・ペーストを自動でできるようにしたいです。

1行目 12345 abc 7777V ・・・

2行目 12345 abc 7777V ・・・

(別の作業時に1行目の内容は変更されます)
1行目 44556 xyg 555555G ・・・
2行目 12345 abc 7777V  ・・・

3行目 44556 xyg 555555G ・・・


1行目 99887 イロハ 99880D ・・・
2行目 12345 abc 777V ・・・
3行目 44556 xyg 555555G ・・・

4行目 99887 イロハ 99889D ・・・

という感じで1行目に新しいデータが入るので、1行目に入ったデータを空いている行にコピー・ペーストを自動でできるようにマクロを組みたいのですが全然うまくいきません。
ネットで
Sub Macro1()
  n = Cells(Rows.Count, "C").End(xlUp).Row + 1
  Range("C" & n).Select
  ActiveCell.FormulaR1C1 = "新宿区"
というマクロを見つけて変更してみたり

Sub Sample2()
With Range("A1")
Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count).Value = .Value
End With
End Sub
というマクロを見つけて試してみたり、変更してみたりしたのですが全部の行をコピー・ペーストすることができませんでした。
どのように書き換えたら上記のようにできるのか教えてもらえると助かります。

excel2007を使用してます。

1行目に入力されているデータを2行目以降の空白になっている行にコピー・ペーストを自動でできるようにしたいです。

1行目 12345 abc 7777V ・・・

2行目 12345 abc 7777V ・・・

(別の作業時に1行目の内容は変更されます)
1行目 44556 xyg 555555G ・・・
2行目 12345 abc 7777V  ・・・

3行目 44556 xyg 555555G ・・・


1行目 99887 イロハ 99880D ・・・
2行目 12345 abc 777V ・・・
3行目 44556 xyg 555555G ・・・

...続きを読む

Aベストアンサー

こんにちは


最終行を取得するのにどの列を使えば良いのか(必ず値がある列)わかりませんが、仮にA列を利用して良いものとすれば、

Rows(1).Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1).EntireRow
で、いかがでしょうか?

QIF関数について

A1~Z1の列の間に、特定の文字(たとえば”りんごの文字”)がセル内に含まれる場合(あかりんごなど)にその下のA2~Z2の列の間に記入された日付を抜き出す方法をご存知でしたら
どなたか教えていただけませんでしょうか、よろしくお願いします。

A2にあかりんごがあれば、B2の日付を抜き出そうとしています。

Aベストアンサー

では、HLOOKUP関数か、抜き出すのが日付ならSUMIF関数でもできるかと思います。

りんごを含む文字を検索値にする場合で、直接式にいれるなら「*りんご*」のようにしてください。


人気Q&Aランキング

おすすめ情報