Excel。1行おきの数値を合計するには、FILTER関数を使うと便利です。【Every other line】

Excel。1行おきの数値を合計するには、FILTER関数を使うと便利です。

＜SUM+FILTER+MOD+ROW関数＞

次のような表があります。

やりたいことは、1Fと2Fの金額合計を求めたいわけです。

つまり、1行おきで合算したい。

A列が1F販売金額となっていれば、SUMIF関数をつかうことで、合計を求めることもできます。

ただ今回の表は、2024年という文字列があって、2025年2026年という文字列もついています。

つまり、同じ条件でというわけにはいきません。

A列から、色々作業をしなければいけません。

では、何かいい方法はないのでしょうか。

以前は、SUMPRODUCT関数をつかって求めていましたが、FILTER関数をつかう方法で、求めてみようと思います。

E2には、FILTER関数をつかった数式を設定しました。

=SUM(FILTER(B2:B7, MOD(ROW(B2:B7),2)=0))

これで、1Fの金額合計を求めることができました。

2Fの数式は、

=SUM(FILTER(B2:B7, MOD(ROW(B2:B7),2)=1))

という数式で求められます。

数式を説明します。

SUM関数の引数の中から説明するとわかりやすいので、まずはFILTER関数から説明します。

FILTER関数は、抽出する関数です。

FILTER関数の最初の引数は、「配列」。

範囲のことなので、B2:B7を設定します。

次の引数は、「含む」。

抽出条件です。

その抽出条件には、MOD(ROW(B2:B7),2)=0

という数式を設定します。

MOD+ROW関数は、そのセルが偶数行なのか、奇数行なのかを判断するときに使う方法です。

MOD関数は、割った余りを求める関数です。

ROW関数は、セルの行番号を求める関数です。

つまり「セルの行番号を2で割った値が、ゼロ」というのが抽出条件になるというわけです。

これで、偶数行なのか奇数行なのか、わかります。偶数奇数ですから、一行おきが対象になるという仕組みです。

あとはSUM関数で合計する。

このように、FILTER関数をつかうことで、一行おきを対象にした合計を求めることができます。

Excel。2列1組で塗り分けるにはどうしたらいい【column】

Excel。2列1組で塗り分けるにはどうしたらいい

＜条件付き書式＋MOD+COLUMN関数＞

2列で1組になっている表があります。

わかりにくいので、次のように塗りつぶしをしたいのですが、どのようにしたら、手早く設定することができるのでしょうか。

 結合したセルもあるので、テーブルにすることもできませんので、単純に列方向で縞々というわけにはいきません。

このような場合、条件付き書式をつかうことで解決できます。

ただし、どのような条件式を設定したらいいのかが、ポイントになります。

そこで、列番号をつかうことで、対応することができます。

列番号を求めるのは、COLUMN関数です。

では、8行目にCOLUMN関数をつかって列番号を求めてみます。

B8に設定した数式は、

=COLUMN()

オートフィルで数式をコピーしています。

これで、列番号を求めることができました。

求めた値を4で除算して、その余りを求めてみます。

余りを求めるのはMOD関数です。

9行目に

=MOD(B8,4)

というMOD関数の数式をつくり、オートフィルで数式をコピーしてみましょう。

これで、0,1,2,3　という余りを求めることができました。

よくみてみると、0,1　と　2,3　がペアになっていることがわかります。

MOD関数の値が2より小さければという条件にすることで、2列1組で塗りつぶすことができそうです。

改めて、B1:I6を範囲選択します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

条件式のボックスには、

=mod(column(),4)<2

2より小さいとすることで、余りが0,1の組を対象として塗りつぶす条件式にしました。

あとは、書式ボタンをクリックして、塗りつぶしたい色を選択しましょう。

 

これで完成です。

Excel。除算した余りを求めるのが、MOD関数です。【mod】

Excel。除算した余りを求めるのが、MOD関数です。

＜関数辞典：MOD関数＞

MOD関数

読み方： モッド　　

分類： 数学/三角 

MOD(数値,除数)

除算した余りを算出します

Excel。カレンダー。隔週ごとに手早く塗りつぶしたいけど、どうしたらいい【Every other week】

Excel。カレンダー。隔週ごとに手早く塗りつぶしたいけど、どうしたらいい

＜条件付き書式＋MOD+WEEKNUM関数＞

次のような簡易カレンダーがあります。

わかりやすいように、週ごと。隔週で塗り分けたいと考えました。

では、どのようにしたら、手早く隔週で塗り分けることができるのでしょうか。

隔週という条件なので、条件付き書式をつかいます。

そこで、問題になるのが、条件式です。

隔週をどのように確認したらいいのでしょうか。

週番号を求めることができる、WEEKNUM関数というのがあります。

求めた週番号が、奇数か偶数かを求めれば、隔週で塗り分けることができそうです。

奇数か偶数かを求めるには、除算した余りを求めることができるMOD関数をつかいます。

早速、設定していきます。

A2:B16を範囲選択します。

ホームタブの条件付き書式から「新しいルール」を選択します。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックして、条件式を設定します。

条件式のボックスには、

=mod(weeknum($A2,1),2)

と数式を設定したら、塗りつぶしたい色を設定して、OKボタンをクリックします。

ご覧のように、隔週で塗り分けることができました。

条件式の確認をしましょう。

数式全体ですが、

=mod(weeknum($A2,1),2)

数式の最後に「=0」をつけてもいいのですが、省略しています。

=mod(weeknum($A2,1),2)

この数式が成立しているならば、「1」と算出され、TRUEという判断になります。

また、成立していないのなら「0」と算出され、FALSEという判断になります。

このため、「=0」は省略することができます。

MOD関数は、除算した余りを求めることができます。

2で除算することで、奇数か偶数かを求めることができます。

WEEKNUM関数は、週番号を求めることができます。

最初の引数は、シリアル値。日付です。

列固定の複合参照にすることで、行全体を対象にすることができます。

2つ目の引数は、「週の基準」です。

今回は、1をつかいました。1は、週の最初を日曜日として設定することができます。

用途によって、基準を変えることで、最初の曜日に対応させることができます。

このWEEKNUM関数ですが、週の基準の21をみてみると、（システム2）と表示されています。

このシステム1とシステム2の違いは何かというと、システム2は、その年の最初の木曜日を含む週をその年の第1週の場合でつかうことができるというのが、システム2です。

外資系の企業さんで使うことが多いのが、システム2のようです。

これらの関数を組み合わせてつかうことで、隔週で塗り分けることができる条件式を設定することができます。

Excel。罫線を5行ごとに区切りとわかるように罫線を変更したい【Border】

Excel。罫線を5行ごとに区切りとわかるように罫線を変更したい

＜条件付き書式+MOD+ROW関数＞

表に格子の罫線を設定しました。

大きな表だと、区切りがわかりにくいので、5件（5行）ごとに罫線を変更することにしました。

ただ、どのようにしたら、手早く設定できるのでしょうか。

そこで、条件付き書式をつかって、対応することにしました。

条件付き書式は、塗りつぶし以外にも、罫線を設定することができるからです。

A2:B11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択して、条件式を設定します。

=mod(row(),5)=1

書式ボタンをクリックします。

セルの書式設定ダイアログボックスの罫線タブに移動して、任意の線種を設定します。

今回は、わかりやすいように、赤色にしてみました。

そして、罫線の下線ボタンをクリックして設定したら、OKボタンをクリックします。

新しいルールダイアログボックスに戻りましたら、OKボタンをクリックして、完成です。

 

5件後に、設定した罫線に変わったことが確認できました。

ただ、用意されているものでないといけないので、異なったデザインで罫線を設定したい場合にはExcel VBAで対応するのもいいかもしれません。

さて、条件式を確認しておきましょう。

=mod(row(),5)=1

MOD関数は、除算の余りを算出する関数です。

ROW関数は、行番号を求めることができる関数です。

この2つの関数と条件付き書式で、一行おきに塗り分けるという時に、よく使うのですが、そのアレンジで、罫線にしてみました。

行番号を、5で割って、余りが1だったら、罫線を変更するということができるというわけです。

Excel。ABCを繰り返すデータを手早く入力するにはどうしたらいいの。【alphabet】

Excel。ABCを繰り返すデータを手早く入力するにはどうしたらいいの。

＜CHAR＋MOD＋ROW関数＞

条件はなく、単純に上位から、ABCという3クラスに割り振りたい。

だけど、データが多くて、コピーなどで対応するにも面倒です。

そこで、関数をつかって、ABCと繰り返すデータを作りたい場合、どのような数式を設定したらいいのでしょうか。

次の表をつかって、数式をつくっていくことにします。

 

D2には、

=CHAR(MOD(ROW()+1,3)+65)

という数式を設定してあります。

オートフィルで数式をコピーしたら、ABCを繰り返して表示します。

では、数式について説明します。

最初のCHAR関数は後回しにして、先にMOD関数とROW関数で何をしているのかから確認していきます。

MOD(ROW()+1,3

ROW関数は、行番号を算出する関数です。

引数の指定をしないと、アクティブの行番号を算出します。

D2なので、「2」を算出します。

その値に「+1」しますので、「3」と算出されます。

MOD関数は、除算した余りを算出する関数です。

ROW関数で算出した結果の「3」をMOD関数の2番目の引数が3になっているので、3で除算した余りは、「0」。

この「0」に65を足します。

「65」はどこからきたのかというと、「A」の文字コードが65だからです。

CHAR関数は、数値をその数値が割り振られている文字に変換する関数です。

CHAR関数をつかうことで、65はAと変換してくれるというわけです。

「65」を算出したかったので、MOD(ROW()+1,3　で、「0」を算出させるような数式をつくったというわけです。

オートフィルで数式をコピーすれば、余りは、「0と1と2」の3種類をつくることができるというわけです。

そうすることで、ABCを繰り返したデータを手早く設定できます。

Excel。手早く5件おきに罫線を変えて描くにはどうしたらいいの。【ruled line】

Excel。手早く5件おきに罫線を変えて描くにはどうしたらいいの。

＜条件付き書式＋MOD+ROW関数＞

表を見やすくしたいので、5件おきに、セルの下罫線を変えたい場合、どのようにしたら、手早く設定することができるのでしょうか。

Excel VBAでプログラムをつくってもいいかもしれませんが、条件付き書式で設定する方法をご紹介します。

見出し行を除いたデータのA2:B13を範囲選択します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

ルールの種類を「数式を使用して、書式設定するセルを決定」を選択します。

条件式のボックスに、

=mod(row(),5)=1

と設定します。

あとは罫線の書式を設定しますので、書式ボタンをクリックします。

わかりやすいように、赤色にしてみましたが、用途に合わせて線種や色を設定します。

下線に描きたいので、下線のボタンをクリックして、OKボタンをクリックします。

元のダイアログボックスが表示されますので、改めてOKボタンをクリックして完成です。

条件式を説明します。

=mod(row(),5)=1

MOD関数は、除算した余りを算出する関数です。

最初の引数にはROW関数をつかって、行番号を抽出します。

2番目の引数は、除数です。

「5件おき」なので、5で除算します。

除算した余りが、1の時が、5件おきなので、「=1」を追加します。

なお、ここの条件をアレンジすることで、様々なケースに対応することができます。

ただ、線種が少ないので、もっと色々対応した場合には、Excel VBAでプログラムをつくってみるのもいいかもしれませんね。

Excel。行を手早く3色で塗りわけるにはどうしたらいいの【paint a line】

Excel。行を手早く3色で塗りわけるにはどうしたらいいの

＜条件付き書式＋MOD＋ROW関数＞

行を交互に塗りつぶすには、MOD+ROW関数を条件式に設定した条件付き書式で、対応することができます。

それをさらにアレンジすれば、1行目・2行目・3行目を別々の色で塗りつぶすことができます。

A2:C8を範囲選択して、ホームタブの条件付き書式にある新しいルールをクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択したら、書式設定のボックスに、

=MOD(ROW(),3)=2

と数式を設定します。

書式ボタンをクリックして、塗りつぶしの色を設定したら、OKをクリックして、条件付き書式の設定が完了します。

これで、1行目の設定が完了しました。

同じように、

=MOD(ROW(),3)=1

=MOD(ROW(),3)=0

と設定すれば、1行目・2行目・3行目をバラバラに塗りつぶすことができます。

それでは、条件付き書式に設定した数式を確認しておきましょう。

=MOD(ROW(),3)=2

MOD関数は、除算した余りを算出する関数です。

最初の引数には、ROW関数をつかって、行番号を算出します。

ROW関数は、行番号を算出する関数です。

2番目の引数は、除数を設定します。

今回は、3行バラバラに設定したいので、「3」と設定します。

算出結果が、2と等しいのか判断させて合致すれば、塗りつぶすという仕組みです。

このように、MOD関数の2番目の引数の「除数」をアレンジすることで、4種類でも5種類でも塗り分けることができます。

Excel。1行おきに抽出したデータを別の場所に、手早く取り出したい【extract】

Excel。1行おきに抽出したデータを別の場所に、手早く取り出したい

＜FILTER+MOD+ROW関数＞

帳票などで、1行ごとのデータを抽出したい場合、数式をつかって、1行おきになるように判断させます。

その結果をオートフィルターで抽出して、コピーするという方法をよく採用していましたが、FILTER関数をつかうことで、手早く抽出し、別の場所に取り出すことができます。

次の表のようにしたいわけです。

 

A1:E9の表は、販売数と売上高が交互になった表であることがわかります。

売上高のデータだけ、つまり1行おきにデータを抽出したいわけですね。

どうやったら、抽出することができるのかと考えるところですが、FILTER関数をつかえば、手早く抽出することができます。

11行目に見出し行をコピーしておきます。

A12に次の数式を設定します。

=FILTER(A2:E9,MOD(ROW(B2:B9),2)=1)

スピル機能によって、オートフィルで数式をコピーする必要はありません。

FILTER関数は、わかりやすい関数なので、使い勝手もいいように思えます。

それでは、数式とFILTER関数の引数を確認しておきましょう。

最初の引数は、「配列」です。データの範囲ですから、A2:E9と設定します。

2番目の引数は、「含む」です。これは、条件のことです。

抽出する条件ですが、1行おきに抽出したいわけなので、MOD+ROW関数の組み合わせで対応することができます。

設定した条件は、

MOD(ROW(B2:B9),2)=1

ROW関数は行番号を算出する関数です。

MOD関数は、除算した余りを算出する関数です。

その値を2で除算した余りが1と等しいかという条件をつくったわけですね。

これで、一行おきにデータを抽出することができます。

なお、MOD+ROW関数で、行が交互になるような条件をつくりましたが、「販売数」・「売上高」という項目で区別できるので、FILTER関数だけでも抽出することができます。

=FILTER(A2:E9,B2:B9=”売上高”)

このようにFILTER関数と他の関数を組み合わせてつかうことで、抽出作業が改善できるかもしれませんね。

Excel。掛け算に割り算と余りを算出する関数を知りたい【multiplication division】

Excel。掛け算に割り算と余りを算出する関数を知りたい

＜PRODUCT・QUOTIENT・MOD関数＞

四則演算の関数といえば、和算のSUM関数がお馴染みですね。

掛け算や割り算と余りの関数は何なのでしょうか。

掛け算の関数は、「PRODUCT関数」をつかいます。

D2の数式は、

=PRODUCT(B2:C2)

5×2ですからD2には、「10」と算出されます。

割り算ですが、「/（スラッシュ）」をつかうことで、除算することができます。

D5の数式は、

=B5/C5

割り算の関数は、「QUOTIENT関数」をつかうことで、商を算出することができます。

D6の数式は、

=QUOTIENT(B6,C6)

B6をC6で除算したわけです。

算出結果は「2」。

四則演算のように小数点を表示することはありません。

あくまでも、商のみを算出するだけです。

小数点ではなくて、除算の余りを算出したい場合には、「MOD関数」をつかうことで解決します。

D9の数式は、

=MOD(B9,C9)

このように、四則演算でお馴染みの掛け算・割り算と余りを算出する関数でした。

Excel。2行1組でセルを塗りつぶすにはどうしたらいいの。【1 set of 2 rows】

 Excel。2行1組でセルを塗りつぶすにはどうしたらいいの。

＜条件付き書式＋MOD+ROW関数＞

条件付き書式をつかうことで、1行おきにセルを塗りつぶすように設定すれば、横縞模様にできます。

ただ、次のようにデータが2行1組になっている場合は、どのようにしたら、セルを塗りつぶせるのでしょうか。

設定方法を紹介していきます。

見出し行と見出し列を除いたデータ部分のB2:E9を範囲選択します。

ホームタブにある「条件付き書式」の「新しいルール」をクリックします。

「新しい書式ルール」ダイアログボックスが表示されます。

 

「数式を使用して、書式設定するセルを決定」を選択します。

行全体や列全体を対象にする場合には、数式を使った条件設定が必要になります。

「次の数式を満たす場合に値を書式設定」に数式を設定します。

=MOD(ROW(),4)<2

あとは、書式ボタンをクリックして、セルを塗りつぶす色を設定します。

設定後、このダイアログボックスに戻ってきたら、OKボタンをクリックして、完成です。

1行おきに塗りつぶしを設定する場合にも、MOD+ROW関数という関数の組み合わせで対応することができましたが、そのアレンジ版ですね。

MOD関数は、除算した結果の余りを算出する関数です。

ROW関数は、行番号を算出する関数です。

基本的には、MOD+ROW関数は変わりません。

1行おきの場合には、2で除算した余りが0か1かを判断させることができました。

「MOD(ROW(),4)」がどのような動きをしているのか、表の隣に算出してみます。

G2には、

=MOD(ROW(),4)

という数式を設定して、オートフィルで数式をコピーしています。

G2の算出結果は「2」。

これは、行番号が2を4で除算すれば、商が「0」で余が「2」なので、「2」と算出されたわけです。

算出結果をみると、「0と1」・「2と3」のグループでわければ、2組1行と判断できます。

そこで、「<2」を数式に加えた

=MOD(ROW(),4)<2

という数式を条件付き書式の条件に設定すると、2行1組で塗り分けることができるというわけです。

Excel。手早く2列1組で色分けをするには、どうしたらいいの【conditional formatting】

Excel。手早く2列1組で色分けをするには、どうしたらいいの

＜条件付き書式＋MOD+COLUMN関数＞

横長の帳票を見やすくしたいので、列ごとに塗りつぶししたいと考えたのですが、どのようにしたら、手早く設定することができるのでしょうか。

次の表のようにしたいわけです。

ただ、一列置きではなくて、2列１組のデータになっています。

このような場合、条件付き書式をつかうことで対応できますが、ポイントは、どのような条件を設定したらいいのかということになります。

まずは対応方法からご紹介してきます。

A1:G5を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックしたら、ルールを入力します。

「次の数式を満たす場合に値を書式設定」には、

=MOD(COLUMN(),4)<2

という数式を設定します。

書式ボタンをクリックして、塗りつぶす色を設定します。

あとは、OKボタンをクリックして、完成です。

このように、2列1組で塗りつぶすことができました。

それでは、条件付き書式で設定した数式

=MOD(COLUMN(),4)<2

について説明をします。

MOD関数は、除算した余りを算出することができる関数です。

COLUMN関数は、列番号を算出することができる関数です。

表の6行目にCOLUMN関数で算出した結果を表示してみました。

A6に設定した数式は、

=COLUMN()

オートフィルで数式をコピーしています。

A7に設定した数式は、

=MOD(A6,4)

こちらも、オートフィルで数式をコピーしています。

6行目のCOLUMN関数は、列番号を算出していることがわかります。

これをどのようにしたら、2列1組と判断させることができるのかを考えます。

そこで、4で除算してみましょう。

7行目は、列番号を4で除算した、余りが算出されています。

すると、2より小さい値という条件にすれば、2列1組とすることが容易であると判断できますね。

もし、余りが、2と3と算出している2列1組で塗りつぶしをするならば、そのような条件にすれば、対応することもできます。

条件付き書式は、設定する数式によって、様々な資料でつかる機能の一つなので、つかってみるといいかもしれませんね。

Excel。並べ替えをしても、横縞模様を維持するのはどうしたらいいの【sort】

Excel。並べ替えをしても、横縞模様を維持するのはどうしたらいいの

＜条件付き書式＋MOD+ROW関数＞

テーブル機能を表に追加すれば、表のデザインを、横縞模様を選べば、並べ替えをしても横縞模様で設定されます。

では、通常の帳票や表の場合、どうしたら効率よく、縞模様することができるのでしょうか。

横縞模様にしたいわけですから、一行おきに塗りつぶしをしたいわけですね。

一行おきに塗りつぶすということで、条件付き書式をつかうと対応することができます。

では、条件付き書式を設定していきます。

A2:C11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

「新しいルール」ダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

次の数式を満たす場合に値を書式設定のボックスに、次の数式を設定します。

=mod(row(),2)

あとは、書式ボタンをクリックして、書式を設定します。

新しいルールダイアログボックスに戻ってきたらOKボタンをクリックします。

これで、横縞模様を設定することができました。

売上高を降順にしてみます。

降順に並べ替えをしても、横縞模様が継続されていることが確認できます。

設定した数式を説明します。

=mod(row(),2)

最初のMOD関数は、除算した余りを算出することができる関数です。

何を除算するのかというと、それがROW関数です。

MOD関数の最初の引数は、ROW関数で、そのセルの行番号を算出することができる関数です。

A3は、3行目なので、「3」を算出するわけです。

今回は、1行おきなので、2で除算するので、引数の2つ目は、「2」と設定します。

さて、

行番号を2で除算した余りが、「1」と「0」が算出されるわけですね。

設定した数式には「=1」とか「=0」とかありませんが、塗り分けされています。

これは、1ならば、Excelでは、1を「TRUE」としています。

すなわち、この数式の条件は成立しているということを意味することになるので、書式が反映されるというわけです。

余りがない場合は0なので、Excelでは、0を「FALSE」としています。

FALSEですから、この数式の条件は合致していないということを意味していますので、そのままで、塗りつぶしはされないというわけです。

条件付き書式は、数式を設定することで、様々な表現をすることができます。

Excel。複数列置きのデータを手早く合算するには、どうしたらいいの【Column total】

Excel。複数列置きのデータを手早く合算するには、どうしたらいいの

＜SUMPRODUCT+(MOD+COLUMN関数＞

連続しているデータならば、SUM関数で簡単に合計値を算出できますが、2列おきにあるデータの合計値を算出したい場合、どのようにしたら手早く算出することができるのでしょうか。

次の帳票の場合で説明していきます。

B列とE列のそれぞれの販売金額の合計をH列に算出したいわけです。

このケースのように2か所ならば、Ctrlキーをつかうことで、容易に範囲選択でるので算出すること自体面倒というわけでもありません。

ただ、さらに多くのデータだった場合は、数式を作るのも面倒になっていきます。

このような場合、何かしらの「法則」がないのかが見つかれば、数式を作成するヒントになります。

今回は、合計したい列が、2列置きにあります。

2列置きの数値だけを合算する方法を考えいけばいいということになります。

そこで、SUMPRODUCT関数をつかうことで、解決することができます。

H3には、SUMPRODUCT関数をつかった数式を設定しました。

=SUMPRODUCT((MOD(COLUMN(B3:G3),3)=2)*B3:G3)

オートフィルで数式をコピーしています。

この数式で2列置きの数値を合算することができたわけですが、どのような仕組みなのかを説明していきます。

SUMPRODUCT関数は、PRODUCT＝「掛け算」とSUM＝「総和」が組み合わさった関数です。

MOD関数は、除算した余りを算出する関数です。何の余りを算出するのかというと、COLUMN関数。つまり列番号を除算するわけですね。

「MOD(COLUMN(B3:G3),3)」

今回は、3で列番号を除算した余りを算出させるわけです。

その結果が、

「MOD(COLUMN(B3:G3),3)=2」

2と等しいのかとします。

B列は、余り2ということで合致しますから、「TRUE」となるわけです。合致しなければ「FALSE」となるわけです。

Excelでは、「TRUE」は「1」で「FALSE」が「0」となっていますから、その値を、セルに入力されている値と乗算「*B3:G3」します。

すると、販売金額の列以外は、「0」に置換されるので、販売金額だけを合計することができるというわけです。

ただ、ちょっと動きがわかりにくいので、このような数式を確認するには、数式タブにある「数式の検証」をつかってみましょう。

 

途中計算が視覚として理解することができます。

Excel。隔週おきに行全体を塗りつぶしたいけど、どうしたらいいの【biweekly】

Excel。隔週おきに行全体を塗りつぶしたいけど、どうしたらいいの

＜条件付き書式：MOD+WEEKNUM関数＞

簡易カレンダーで隔週おきに行全体を塗りつぶしたい時には、どのようにしたら、効率的に作成することができるでしょうか。

Excelの日付には、第何週なのかという情報も含まれています。

第何週なのかを算出できる関数が、「WEEKNUM関数」です。

このWEEKNUM関数で算出した値を2で除算して、余りがあるのか、ないのかを判断すれば、隔週おきに分けることができます。

そして、条件で塗り分けるわけですから、条件付き書式を使えば、対応することができます。

それでは、設定方法を確認していきます。

範囲選択します。今回は、A2:B15が該当します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」のボックスに、数式を設定します。

=MOD(WEEKNUM($A2,1),2)

あとは、書式ボタンをクリックして、書式を設定したらOKボタンをクリックして完成です。

MOD関数は、余りを算出する関数です。

WEEKNUM関数が、該当する日付が第何週に当たるのかを算出する関数です。

ところで、

=MOD(WEEKNUM($A2,1),2)=1

というように、数式のうしろに、「=1」とか「=0」とかをつけていません。

これは、=MOD(WEEKNUM($A2,1),2)が、「1」か「0」のみ算出されます。

「=1」となれば、「1」はExcelでTRUE。

「=0」ならば、「0」でExcelではFALSEと設定されています。

TRUEならば、成立しているということで、条件を満たす。

すなわち、書式設定の対象となるわけです。

そのため、数式のうしろに、「=1」とか「=0」をつけていなくても、大丈夫というわけです。

あと、WEEKNUM関数の最初の引数を「$A2」と列を固定した複合参照にすることで、行全体を塗りつぶすことができます。

条件付き書式と数式を組み合わせることで、日頃使っている資料が、より一層わかりやすくなるかもしれませんので、色々試してみるといいかもしれませんね。

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの【every other row】

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの

＜SUMPRODUCT+MOD+COLUMN関数＞

Excelは基本的に上から下へ流れていくテーブル（表）でつくれば、様々なExcelの機能を使うことができます。

ただ、どうしても帳票と同じように表をつくってしまうと、簡単に算出できない場合があります。

例えば、次の表。

来店客数と売上高が一組になったデータが列方向に拡張されている表。

このような表、帳票としてはいいのですが、単純に、来店客数の合計や、売上高の合計を算出する場合、1列おきで範囲を設定する必要があります。

要するに、列が増えれば増えるほど、面倒な作業というわけです。

そこで、来店客数の合計B8には次の数式を設定することで、手早く算出することができます。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=0)*$B$3:$G$5)

また、売上高の合計C8には、次の数式を設定してあります。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=1)*$B$3:$G$5)

数式の引数を確認しましょう。

最初のSUMPRODUCT関数ですが、SUMは、和算。

PRODUCTは乗算で、乗算した結果を和算する関数です。

そして引数のMOD関数とCOLUMN関数は何をやっているのかというと、1列おきで範囲選択したいわけです。

1列おきということは、列番号をつかって、偶数か奇数なのかを判定させればいいわけです。

MOD関数は、除算した、あまりを算出する関数です。

また、COLUMN関数は列番号を算出する関数です。

よって「MOD(COLUMN($B$3:$G$5),2)」で、列番号を2で除算するという数式ですから、結果「0」だったら余りが0ということで、偶数列ということがわかります。

「MOD(COLUMN($B$3:$G$5),2)=0」と「=0」とすれば、「MOD関数の結果が0と等しいか」と判断させています。

「等しい」ならば「TRUE」、「等しくない」ならば「FALSE」と判定されます。

「TRUE=1」で「FALSE=0」とExcelでは定義されていますから、「MOD(COLUMN($B$3:$G$5),2)=0」が成立しているならば、偶数列は「1」。

奇数列は「0」と算出されるわけです。

ここで、SUMPRODUCT関数の出番。

「*$B$3:$G$5」と乗算していますが、偶数の「1」を掛ければ、その値は残り、奇数の「0」を掛ければ、「0」となるわけです。

その結果を和算すれば、偶数列のみの合計値を算出できるというわけです。

1列おきとか1行おきとかで、合計を算出したい場合にはSUMPRODUCT関数をつかってみるといいかもしれませんね。

Excel。手早く一行おきで合計値を算出したいけど、どうしたらいいの。【Total】

Excel。手早く一行おきで合計値を算出したいけど、どうしたらいいの。

＜SUMPRODUCT+MOD+ROW関数＞

帳票をExcelにそのまま移行した場合、算出する方法は簡単でも、それをどのように数式として表現したらいいのか、難しくなることがあります。

例えば次の表。

B列の売上高の合計を一行おきに算出したいというのが、目的です。

なんで一行おきなのかというと、2行1組になっていて、1行目が2022年で2行目が2023年になっているというわけです。

年のフィールド（列）があれば、SUMIF関数がつかえるのですが、条件につかえる列がないので、つかえません。

SUM関数で、一行おきに、範囲選択するしかないのでしょうか。

それでは、ミスも発生する可能性が上がってしまうし、何よりも面倒です。

そこで、SUMPRODUCT関数をつかうことで、手早く算出することができます。

F1に次の数式を設定します。

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*$B$2:$B$7)

F2には、次の数式を設定します。

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*$B$2:$B$7)

この数式で、算出することができるわけですが、少し複雑な数式なので、説明をします。

SUMPRODUCT関数は、「積の結果」を合計する関数です。

余計な計算列を作らなくて一発で、算出したい時に使うと便利な関数です。

SUMPRODUCT関数の引数から確認していきます。

C1に、

=MOD(ROW(C2:C7),2)=0

と引数のところを抽出してみました。スピル機能で、C7まで算出されています。

ROW関数は、行番号を算出する関数です。C2の行番号は「2」です。

MOD関数は、除算した余りを算出しますので、ROW関数で算出した結果を2で除算した余りを算出しています。

C2は、2÷2なので、余り「0（ゼロ）」です。

その結果が「0」と等しいならば「TRUE」と算出され、等しくなければ「FALSE」と算出される仕組みになっています。

算出結果をみると、TRUEとFALSEが一行おきになっていることがわかります。

これで、一行おきという条件に対応することができるというわけです。

また、Excelは、「TRUE」が1で「FALSE」が0と設定されています。

PRODUCTは掛け算を意味しますので、売上高にFALSE=0を掛ければ「0」になるので、TRUEのところだけをSUMするので、一行おきに合計値を算出できます。

いままで複雑で、いくつか計算列を経由して算出していた数値も、今回使用した、SUMPRODUCT関数をつかうと、コンパクトになるかもしれませんので、つかえないか検討してみるのもいいかもしれませんね。