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。ROW関数はセルの行番号を算出します。【ROW】

Excel。ROW関数はセルの行番号を算出します。

＜関数辞典：ROW関数＞

ROW関数

読み方： ロウ　　

分類： 検索/行列 

ROW([参照])

セルの行番号を算出する

Excel。検索値を入力して、行方向のデータを列方向で表引きしたい【matrix】

Excel。検索値を入力して、行方向のデータを列方向で表引きしたい

＜VLOOKUP+ROW関数＞

行方向のデータを表引きした結果を、列方向で表示したい場合、どのようにしたら、いいのでしょうか。

次の表を用意しました。

B6に3と入力したら、B7:B10にNO3のデータを表示させたいわけです。

単に、コピー貼り付けで、行列入れ替えというのも、1回だけならば、OKですが、NOを入力するたびに、そのデータを表引きしたいので、コピーだけというわけにはいきません。

やりたいことは「表引き」です。

そこで、VLOOKUP関数をつかってみることにします。

問題になるのは、3つ目の引数の「列番号」です。

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

=VLOOKUP($B$6,$A$2:$E$4,ROW(A2),FALSE)

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

行方向のデータを列方向に表引きすることができました。

数式を確認しておきましょう。

1番目の引数は、検索値。

B6ですが、オートフィルで数式をコピーするので、絶対参照で固定する必要がありますので、$B$6

2番目の引数は、範囲。$A$2:$E$4

こちらも、オートフィルで数式をコピーしますので、絶対参照を設定します。

次がポイントになる

3番目の引数の、列番号。

下方向に、オートフィルで数式をコピーしたときに、2～4としていきたいわけです。

列番号を求めることができるROW関数をつかえば、列番号をつくることができますので、

ROW(A2)

最後は、検索方法。完全一致なので、FALSE　と設定します。

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。結合したセルにデータをスムーズにコピーするにはどうしたらいいの。【Integrated Cell】

Excel。結合したセルにデータをスムーズにコピーするにはどうしたらいいの。

＜INDEX+ROW関数＞

コピーして貼り付ける。

特に問題は無い操作だと思いますが、貼り付け先が結合されていると、スムーズに貼り付けることはできません。

次の表で確認してみましょう。

やりたいことは、A2の新宿は、C2の結合されたセルにコピーしたいわけです。

単純に、A2:A4を範囲選択して、C2を基準に貼り付けてみましょう。

すると、「この操作は結合したセルには行えません」とメッセージが表示されてしまいます。

では、セル参照とオートフィルで数式をコピーするというのは、ダメでしょうか。

C2をクリックして、=A2と数式を設定します。

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

やはり、うまくいきませんでした。

こうなると、一つずつ、コピーしなければならないのでしょうか。

それでは、時間がかかるだけでなく、面倒です。

そこで、数式を作って対応することにします。

C2をクリックして、次の数式を設定します。

=INDEX($A$2:$A$4,ROW(A2)/2)

あとは、オートフィルで数式をコピーします。

これで、対応することができました。

なぜ、この数式は対応することができるのでしょうか。

数式を確認しておきます。

INDEX関数は、

指定した列と行が交わる位置にある値を表示してくれる関数です。

最初の引数は、配列。

範囲選択なので、A2:A4。

オートフィルで数式をコピーしますので、$A$2:$A$4と絶対参照を設定します。

2つ目の引数は、行番号。

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

ROW(A2)/2

と設定しましたが、この数式の意味は、A2の行番号は2です。

C列の新宿のセル番地は、C2となっています。

C列の品川のセル番地は、C4で、C列の横浜のセル番地は、C6となっています。

つまり、結合されているので、2・4・6という行番号です。

これを、2で除算します。

すると、新宿は2÷2で1。品川は4÷2で2。と算出できます。

範囲はA2:A4で、1行目と交差するのは、新宿というデータをINDEX関数で、表示できるという仕組みです。

この数式をつかうことで、結合セルに対応した、データのコピー（のようなこと）ができるというわけです。

Excel。オートフィルの連続コピーでABCを手早く入力したい【alphabet】

Excel。オートフィルの連続コピーでABCを手早く入力したい

＜CHAR＋CODE＋ROW関数＞

AからZまでを手早く入力するには、オートフィルだけでは連続コピーすることはできません。

数値ならばいいのですが…

そこで、次のような数式を設定するだけで、オートフィルで数式をコピーするだけで、連続コピーすることができます。

A2に設定した数式は、

=CHAR(CODE("A")+ROW()-2)

です。

この数式を必要に応じて、オートフィルで数式をコピーすると、A～Zまでの連続したアルファベットを入力することができます。

では、数式を確認しておきましょう。

まず、CHAR関数は、数値を該当する文字に変更する関数です。

そこで、CODE(“A”)ですが、CODE関数は、文字を文字コードの数値に変換する関数です。

CODE(“A”)は、Aの文字コードを返す関数です。

なお、Aは65です。

よって、CHAR(65)とすれば、Aという文字を返してくれます。

ただ、=CHAR(CODE("A")　だけだと、オートフィルで数式をコピーしても、すべてAが表示されるだけで、BC…は表示してくれません。

Bの文字コードは、66で、Cの文字コードは67です。

つまり、前の文字に「+1」されています。

どうやって、「+1」すればいいのでしょうか。

下方向に、オートフィルで数式をコピーする場合には、行番号を算出するROW関数が使えないかを考えるといいです。

A2に数式を設定します。

A2は2行目なので、ROW関数をつかえば、2と返してくれます。

A2は「A」を表示したいので、65+0にしたいわけです。

A3は「B」を表示したいので、65+1にしたいわけです。

ということで、

=CHAR(CODE("A")+ROW()-2)

とROW関数の値から「-2」することで、Aを基準としてプラスする値をつくることができます。

このように、Aからのアルファベットを連続コピーで作る場合には、

CHAR関数

CODE関数

ROW関数

を組み合わせることで、対応することができます。

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです【consecutive dates】

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです

＜DATE+INT+ROW関数＞

連続した日付を入力するには、オートフィルをつかうことで、手早く設定することができます。

ところが、次のような複数の同じ数だけの連続した日付を入力したい場合、オートフィルだけでは、対応することができません。

次の表のように、2日ごと3日ごとに同じ数だけの連続した日付を入力しています。

このような場合、数式をつかって対応します。

A2には、

=DATE(2024,5,INT(ROW(A2)/2))

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

あとは、オートフィルで数式をコピーするだけで、日付を入力することができます。

数式を確認します。

DATE関数は、日付を作る関数です。

年と月は、今回直接入力しました。

日がポイントです。要するに、1をどのようにつくるのか、そして、2個1を用意することができるのかという点です。

そこで、INT+ROW関数で対応するというわけです。

INT関数は整数にする関数。

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

ROW(A2)なので、2。

それを2で除算するわけです。

結果は、2÷2なので、1ですね。

A3ならば、3÷2なので、1.5ですが、INT関数で整数化されるので、1ですね。

また、C2に設定した数式は、

=DATE(2024,5,INT(ROW(C3)/3))

設定したい日になるようにすれば、3日ごとでも4日ごとでもつくれます。

Excel。セルを選択するたびに、行全体を塗りつぶして見やすくしたい。【active cell】

Excel。セルを選択するたびに、行全体を塗りつぶして見やすくしたい。

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

行全体を見ていくと、上下別のデータを見てしまう恐れがあります。

そこで、アクティブにしたセルの行全体を塗りつぶすことができれば、定規を当てたようにわかりやすくなります。

全自動というわけにはいきませんが、条件付き書式とROW関数。

そしてCELL関数をつかった条件式を設定することで対応することができます。

では、A2:F10を範囲選択します。

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

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

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

ルールの内容に、

=row(a2)=cell("row")

と設定します。

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

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

範囲選択した中のセルをどれかアクティブにしたら、「F9」キーの再計算を実行します。

すると、そのアクティブセルのある行全体が塗りつぶされることが確認できます。

残念なのは、F9キーを押さないと変わってくれないことですね。

では、設定した条件式を確認しておきましょう。

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

範囲選択の左端のセル番地を設定します。

今回はA2なので、2と算出されます。

CELL関数は、セルのステータスというか、情報を算出する関数です。

引数に、”ROW”と設定すると、アクティブセルの行番号を算出されます。

この2つが、等しい＝TRUEとなるので、塗りつぶしが実行されるというわけです。

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。VLOOKUP関数をつかって行方向のデータを列方向で抽出表示したい【Rows and columns】

Excel。VLOOKUP関数をつかって行方向のデータを列方向で抽出表示したい

＜VLOOKUP+ROW関数＞

VLOOKUP関数は、抽出表示するのに便利な関数です。

ただ、行方向のデータを、行方向に抽出表示するのではなく、行方向のデータを、「列方向」に抽出表示したい場合、どのようにしたら、手早く数式を設定することができるのでしょうか。

 

ポイントは、下方向にオートフィルで数式をコピーしますが、3番目の引数の「列番号」をどのように設定してあげたらいいのかという点です。

その点を踏まえて、次の数式を、B7に設定します。

=VLOOKUP($B$6,$A$2:$D$4,ROW(A2),FALSE)

あとは、オートフィルで数式をコピーします。

これで、B6のNOを変えるだけで、氏名～Listeningまでを該当の番号の氏名からListeningまでを抽出表示することができます。

では、数式を確認しておきましょう。

最初の引数は「検索値」。

NOに該当するデータを抽出したいので、「B6」を設定します。

ただ、オートフィルで数式をコピーしますので、絶対参照の設定をしなければなりませんので、「$B$6」と設定します。

2つ目の引数の「範囲」。

これは、抽出元のデータベースなので、見出し行を除いた、「$A$2:$D$4」と設定します。

こちらも絶対参照が必要ですね。

3つ目の引数が「列番号」。

直接数値を入力して変更してもいいかもしれませんが、数式の数が増えれば増えるだけ、修正数が増えるので、面倒です。

2・3・4と数値を変える方法を考える必要があります。

そこで、行番号を算出することができるROW関数をつかうことで、対応することができます。

よって、「ROW(A2)」と設定することで、この問題を解決することができます。

ROW(A2)=2と算出されます。

オートフィルで下方向にコピーすれば、ROW(A3)となりますから、3と算出され、3列目のデータを抽出することができるというわけです。

最後の引数は「検索方法」は、完全一致なので「FALSE」と設定します。

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。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。並べ替えをしても、横縞模様を維持するのはどうしたらいいの【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。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【Extract】

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい

＜IF＋ROW関数　IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞

データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。

この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。

そこで、数式だけで、対応することもできます。

A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。

G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。

最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。

=IF(B2=$G$1,ROW()-1,"")

B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。

ROW関数は行番号を算出します。

「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。

今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。

F4にメインとなる数式を設定します。

設定したら、オートフィルで数式をコピーします。

=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")

長い数式ですが、これだけで、該当するデータを抽出し表示することができます。

F4:H8まで数式を設定してあります。

それでは、数式を確認していきます。

最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。

INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。

最初の引数は、「配列」です。

「$A$2:$C$8」と設定します。

オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。

2番目の引数は、「行番号」です。

SMALL($D$2:$D$8,ROW(A1))

先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。

1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。

オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。

最後の引数は、「列番号」です。

COLUMN(A1)

こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。

このように、INDEX関数をつかうことで、データを抽出することもできます。

Excel。列方向の重複を除いて、行方向の表で抽出するには、どうしたらいい【Remove duplicates】

Excel。列方向の重複を除いて、行方向の表で抽出するには、どうしたらいい

＜INDEX＋ROW関数＞

重複しているデータを除いた表を作る場合には、データタブの「重複の削除」をつかうと効率的に作成することができます。

ただし、この「重複の削除」は行方向。

つまりレコードが対象になっているので、列方向であるフィールド方向に重複したデータを除くのは、なかなか面倒な作業といえます。

次の表をつかって、やりたいことを説明していきます。

 

1行目のB1:G1のデータから重複したデータを除いたものを、A4を起点とした表をつくりたいというのが、やりたいことです。

列方向では、「重複の削除」をつかうことはできません。

そこで、先に、列方向のデータを行方向にする作業をしていきます。

列方向を行方向に変換するには、「INDEX関数」をつかうと、手早く処理することができます。

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

=INDEX($B$1:$G$1,1,ROW(B1))

あとは、必要なデータだけオートフィルで数式をコピーします。

 

INDEX関数の説明は後述するとして、重複データの削除をおこなっていきます。

このまま、データタブの「重複の削除」をおこなってしまうと、一瞬成功したように思えるのですが、内容は数式なので、結果うまくいきません。

まずは、INDEX関数で行列を入れ替えたデータを数式から値として変更します。

A4:A9を範囲選択して、コピーしたら、そのまま貼り付けの「値」をクリックします。

あとは、データタブの「重複の削除」を実行したら、完成ですね。

それでは、INDEX関数を確認しておきましょう。

最初の引数「配列」には、$B$1:$G$1と設定します。

オートフィルで数式をコピーするので、絶対参照も忘れずに設定します。

2番目の引数の行番号は、行をずらすとかしないので、「1」と設定します。

3番目の引数の列番号は、「1」「2」「3」…と列参照をずらしていきたいわけですね。

そこで、ROW関数をつかうと、オートフィルで数式をコピーするのと連動して「1」「2」「3」…と数値を変えることができます。

そこで、ROW(B1)と設定しました。

なお、ROW(A1)でもOKです。

INDEX関数はアイディアによって色々活用することができますので、試してみるといいかもしれませんね。

Excel関数辞典 VOL.69。ROW関数～RTD関数【dictionary】

Excel関数辞典　VOL.69。ROW関数～RTD関数

＜Excel関数＞

今回は、ROW関数～RTD関数までをご紹介しております。

ROW関数

読み方： ロウ　　

ROW([参照])

セルの行番号を算出する　

ROWS関数

読み方： ロウズ　　

ROWS(配列)

セル範囲の行数を算出します　

RRI関数

読み方： アールアールアイ　　

読み方： レリバント・レート・オブ・インタレスト

RRI(期間,現在価値,将来価値)

将来の価値から利率を算出する　

RSQ関数

読み方： アールエスキュー　　

RSQ(既知のy,既知のx)

回帰直線の決定係数を算出します　

RTD関数

読み方： アールティーディー　　

読み方： リアルタイムデーターサーバー

RTD(プログラムID,サーバー,トピック1,[トピック2],…)

RTDサーバーからデータを取得する　

Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。【Cumulative】

Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。

＜SUM+OFFSET+INT+ROW関数＞

やりたいことはイメージできても、実際にそれを表現するのが難しいことがあります。

例えば、4行1組となっている表で、その4行1組ごとに累計を算出したい場合も、そのようなケースだと思います。

表のD列のように算出したいわけです。

最初に、D2の数式をご紹介します。

=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)

この数式を、オートフィルでコピーするだけで、4行ごとに累計を算出することができます。

ただ、この数式だけでは、いったい何がどうなっているのか、わかりにくいので、説明していきます。

抑えておきたいのは、累計を算出する数式です。

D列に売上高の累計を単純に算出するならば、

=SUM($C$2:C2)

という始点を絶対参照にして、終点を相対参照のままにすれば、累計を算出することができました。

終点の相対参照は、オートフィルで自動的に参照がかわりますので、考えるのは、始点ということになります。

始点を4行ごとにするには、どうしたらいいのかを、考えてみましょう。

ポイントは4行おきに始点をずらしたいわけです。

「ずらす」というキーワードがでたら、OFFSET関数の登場だと思っていいでしょう。

今度は、OFFSET関数の引数を確認します。

OFFSET(参照,行数,列数,[高さ],[幅])

行数を4行おきに、「+1」すれば、うまくいきそうです。

列数は、ずらさないので、「0」

「高さ」と「幅」は、今回省略します。

あとは、どうやって「行数」をつくっていくかを考えます。

ここで、思い出したいのは、オートフィルをするということ。

オートフィルでずれた時に、変わるものといったら、行数です。

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

行方向に何かするときには、ROW関数にお世話になります。

=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)

この数式の、「INT((ROW()-2)/4)」を抽出したE列をつくってみました。

E2は、ROW関数で算出される行番号は「2」。

この値をデータの始点である2行目の「2」で減算します。

算出した値「0」を4行1組の組数の「4」で除算した値を、INT関数をつかって、整数化します。

最後に、「INT((ROW()-2)/4)」で算出された値に、「×4」する。

この4倍は、4組の「4」です。

そうすれば、OFFSET関数の行数として、つかうことができます。

色々な関数が登場しましたが、その中でも、OFFSET関数をつかうことで、今まで、手早く算出することができなかった帳票類も改善することができるかもしれませんね。

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関数をつかうと、コンパクトになるかもしれませんので、つかえないか検討してみるのもいいかもしれませんね。