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。1列置きのデータを抽出し、さらに表引きも合わせて行いたい【Table pull】

Excel。1列置きのデータを抽出し、さらに表引きも合わせて行いたい

＜VLOOKUP+COLUMN関数＞

横長のデータがA1:H5にあります。

4月から6月までの売上のデータだけを抽出した表をつくりたい。

できれば、NOを入力したデータのみを表引きしたい場合、どのようにしたらいいのでしょうか。

全体の表から必要な列だけを抽出するだけならば、

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

A8に設定した数式は、

=HSTACK(A2:B5,D2:D5,F2:F5,H2:H5)

引数には、必要なフィールドを設定するだけの関数なので、必要な列だけを抽出するならば、とても便利な関数です。

ただ、これに合わせて、例えば、NOが1と3だけのデータを抽出したい。

表引きしたいとなると、HSTACK関数だけでは対応することができません。

少々複雑な数式に変更しなければならなくなります。

そこで、今回は、VLOOKUP関数とCOLUMN関数を組み合わせて、対応していきます。

B8に数式を設定しました。

=VLOOKUP($A8,$A$2:$H$5,COLUMN(A1)*2,FALSE)

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

これで、月別の売上の列と、NOが1と3だけのデータを抽出することができました。

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

最初の引数は、検索値。A8を選択します。

ただ、列固定の複合参照にする必要がありますので、$A8　とします。

2つ目の引数は、A2:H5の表全体を選択します。

オートフィルで数式をコピーしますので、絶対参照が必要です。

よって、$A$2:$H$5 とします。

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

ここがポイントになるわけです。

オートフィルで数式をコピーしたときに、列番号を2・4・6というように2の倍数が必要になります。

横方向にオートフィルした時に、数値が2の倍数にするために、列番号を求めることができる、COLUMN関数をつかってみます。

COLUMN(A1)*2

これで、A1は1と求められるので、その値に、×2すれば、2の倍数を求めることができます。

最後は、検索方法です。

完全一致なので、FALSE　と設定します。

最近、新しい関数が色々登場しております。

関数を組み合わせることで、もっと効率的に求めたり、表引きできると思いますので、試してみるといいものが、発見できるかもしれませんね。

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。複数列置きのデータを手早く合算するには、どうしたらいいの【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。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【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。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。COLUMN関数はセルの列番号を算出できる便利な関数です。【COLUMN】

Excel。COLUMN関数はセルの列番号を算出できる便利な関数です。

＜関数辞典：COLUMN関数＞

COLUMN関数

読み方： カラム　　

分類： 検索/行列 

COLUMN([参照])

セルの列番号を算出する　

Excel。行が等間隔のデータを列方向に簡単にコピーするにはどうしたいいの【Equally spaced】

Excel。行が等間隔のデータを列方向に簡単にコピーするにはどうしたいいの

＜INDEX+COLUMN関数＞

帳票の都合で、どうしても行方向ではなくて列方向に等間隔のデータをコピーするには、どのようにしたら簡単に、しかも手早く処理をすることができるのでしょうか？

例えば、次のような表の場合です。

四半期のデータが、B5・B9・B13と3行空けた等間隔にある表があります。

この表内の四半期のデータを列方向に抽出というかコピーをしたいわけです。

コピー&ペーストするだけですが、データ量が増えると面倒ですし、貼り付けで、行列をいれかえた貼り付けるやり方だと、余計なデータも含めてしまい、データの削除が面倒になってしまいます。

Excel VBAでわざわざプログラムをつくるのも、ちょっと面倒です。

このような場合、INDEX関数をつかった数式で、簡単に処理することができるので、その方法をご紹介します。

D2にINDEX関数の数式をつくっていきます。

=INDEX($B$2:$B$13,COLUMN(A1)*4)

この数式だけで、行の等間隔のデータを列方向に抽出することができます。

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

最初の引数は、配列。

データがある範囲なので、B2:B13が対象になります。

オートフィルで列方向に数式をコピーしますので、絶対参照を設定して、$B$2:$B$13。

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

ポイントは、この行番号です。

どのようにしたら、等間隔に指定することができるのかを考える必要があります。

C列に連番を設定してみると、4の倍数であることがわかります。

オートフィルで列方向に移動した時に、最初が1で次が2というような数値を算出するものがあれば、この4倍というパターンがつかえそうです。

そこで登場するのが、COLUMN関数です。

COLUMN関数は、アクティブになっているセルの列番号を算出してくれる関数です。

数式を設定しているセルはD列なので、「4」と算出してしまうので、COLUMN(A1)とセル番地を設定することで、「1」と算出することができます。

オートフィルで列方向に数式をコピーすれば、COLUMN(B1)となり、「2」と算出してくれますので、4の倍数というパターンでつかうことができるわけです。

引数の列番号は、省略するので、あとは、オートフィルで数式をコピーすれば、完成というわけです。

VLOOKUP関数などでうまくできないケースをはじめ、INDEX関数が重宝するケースもありますので、色々試してみると数式の可読性も向上するかもしれませんね。

Excel。選択したフィールド数を知るにはCOLUMNS関数をつかえば把握できます。【function: COLUMNS】

Excel。選択したフィールド数を知るにはCOLUMNS関数をつかえば把握できます。

＜COLUMNS関数＞

COULUMN関数は、列番号を算出してくれますが、範囲選択した範囲の列数。

あるいは、テーブルのフィールド数を知りたい時には、COLUMN関数を終点から始点を減算するとなると、結構面倒です。

このような時には、COLUMNS関数を使えば、簡単に算出することができます。

COLUMN関数をつかったら、どうなるのかから、確認します。

G2には、

=COLUMN(B2:D9)

という数式を設定しました。スピル機能によって、G2:I2まで算出されましたが、あくまでも、B列なので、2からD列なので、4までの列番号を算出されているだけです。

ところが、COLUMNS関数をつかった、G3の数式は、

=COLUMNS(B2:D9)

と先程のCOLUMN関数と同じ範囲を設定したところ、「3」と範囲選択されている列数を算出してくれました。

このように、表やテーブルが大きい場合、フィールド数がいくつなのかを把握することが簡単にできます。

Excel VBAで列方向で繰り返し処理をする場合などに重宝しそうですね。

最後に、COLUMNS関数の基本情報を確認しておきましょう。

COLUMNS関数の読み方は「カラムズ」です。

所属は、「検索/行列」です。

COLUMNS関数の引数は、

COLUMNS([配列])

Excelには、似ている関数というのがチラホラあります。

算出結果が今回のように異なることもありますので、色々試してみるといいかもしれませんね。

Excel。列番号はCOLUMN関数をつかえば把握することができます。【function: COLUMN】

Excel。列番号はCOLUMN関数をつかえば把握することができます。

＜COLUMN関数＞

列番号を算出するには、「COLUMN関数」をつかうことで、確認することができます。

COLUMN関数の基本情報から確認しておきましょう。

COLUMN関数の読み方は「カラム」です。

所属は、「検索/行列」です。

COLUMN関数の引数は、

COLUMN([参照])

COLUMN関数や行番号を算出できるROW関数とも、あまり単独で使用することはありません。

多く使うのは、Excel VBAで、列方向に移動させて処理するときなどに使用します。

あとは、VLOOKUP関数の列番号に、COLUMN関数をつかうことで、VLOOKUP関数を列方向にオートフィルで数式をコピーするとき重宝する関数です。

B4には、E3:G6の範囲のデータから、商品名を検索抽出するために、VLOOKUP関数をつかっていますが、C列の単価にも、オートフィルで数式をコピーする前提で数式をつくるとしたら、列番号がネックになります。

列番号を商品名は、「2」。単価は「3」と設定修正する必要があります。

修正箇所が多くなると、修正自体は簡単でも面倒になります。

そこで、B4に設定するVLOOKUP関数を次のようにします。

=VLOOKUP($A$4,$E$4:$G$6,COLUMN(B1),FALSE)

3番目の引数の「列番号」のところに、COLUMN関数をつかうことで、列番号を修正しないですむようになります。

COLUMN関数は、主役になる関数ではないかもしれませんが、ちょっとしたところで、活躍する関数なので、用途に合わせ、組み込んでみると、重宝するかもしれませんね。

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？【Consecutive columns】

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？

＜VLOOKUP＋COLUMN関数＞

リストから列方向に連続したデータを抽出したい場合、どうしたら効率よく実施することが出来るでしょうか？

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

NOが2のデータを抽出したいとします。

A2に2と入力したら、B2:E2までのデータを抽出したいわけですね。

このような場合は、VLOOKUP関数をつかうことが、解決の一歩目となります。

B2に次の数式をつくったとします。

=VLOOKUP(A2,$A$6:$E$10,2,FALSE)

この数式をオートフィルで列方向にコピーしてみると、残念ながら、うまくデータを抽出することができません。

普通に、VLOOKUP関数で数式をコピーでは、対応できない数式ということがわかりました。

どこを、どのように修正すればいいのかを考えていきましょう。

最初の引数の検索値。

オートフィルで数式をコピーしたときに、検索値が動いてしまっては、検索することが出来ませんので、絶対参照をつかって、セル番地を固定しておく必要があります。

よって、検索値は、「$A$2」

2つ目の引数の範囲ですが、ここは、絶対参照をつかうことは変わりませんので、範囲は「$A$6:$E$10」

引数の範囲は、オートフィルで数式をコピーすることが前提の場合、絶対参照を設定することが多いです。

3つ目の引数の列数。

ここが一番のポイントになります。

「2」とか「3」のように、入力してしまうと、数式をコピーしただけ修正する件数が発生します。

そのため、「2」「3」…というように、連続した数値を自動的に入力する必要があります。

列方向に連続した数値が欲しい場合には、列番号を算出してくれる「COLUMN関数」をつかいます。

列番号に「COLUMN(B2)」と設定します。COLUMN(B2)は、B列なので、「2」を返してくれます。

列ではなくて行方向に連続した数値を算出させたい時には、ROW関数をつかいます。

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

よって、修正した数式は、

=VLOOKUP($A$2,$A$6:$E$10,COLUMN(B2),FALSE)

と変更したら、オートフィルで数式をコピーして確認してみましょう。

これで、A2の数値を変更するだけで、列方向に連続するデータを容易に抽出することができました。

オートフィルターをつかって、抽出したデータをコピー&ペーストするという方法もありますが、VLOOKUPをつかってみると、簡単にデータを抽出することができます。

Excel。該当する2列置きのデータを手早くコピーするにはどうしたらいい【Every two rows】

Excel。該当する2列置きのデータを手早くコピーするにはどうしたらいい

＜VLOOKUP+COLUMN関数＞

帳票のようなデータから、例えば2列置きごとに必要なデータのみを手早く抽出するとしたら、どのようにしたらいいのでしょうか？

例えば、次の表。

店舗名が新宿と品川のデータの、4月～6月の売上データを抽出したいわけです。

全店舗だとしても、範囲選択してコピーするだけでも、面倒ですし、それが決まった店舗のみとなると、範囲選択するだけでも、面倒です。

コピー&ペーストの作業しか方法がないのでしょうか？

ピボットテーブルをつかってもいいですが、実はこのようなケースの場合、VLOOKUP関数をつかうことで、手早くデータを抽出することが出来ます。

B2には、次の数式を設定しています。

=VLOOKUP($A2,$A$6:$G$10,COLUMN(A1)*2+1,FALSE)

とてもシンプルなVLOOKUP関数の数式であることが見てわかりますね。

VLOOKUP関数は、関数の特性とアイディアで、現場で大いに活躍してくれる関数の一つだといえます。

考えるポイントは、全データではなく、新宿店と品川店のみであるということ。

それと、2列置きのデータであるということ。

そこで、該当のデータのみということから、VLOOKUP関数をつかってみたらどうだろうと考えてみます。

オートフィルで数式をコピーするのが、手早く処理するためには必要です。

オートフィルでVLOOKUP関数をコピーする時にネックになるのが、引数の列番号です。

「列番号」は、数値である必要があります。

そこで、オートフィルで列方向にずらしたときに、列番号を連動させるために使用するお馴染みの関数があります。それが「COLUMN関数」です。

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

今回は、引数の範囲。$A$6:$G$10の3・5・7列目に抽出したいデータがありますので、列番号をどうにかして、3・5・7という数字になるように考える必要があります。

「COLUMN(A1)*2+1」とすることで、2列置きのデータを抽出できることに気づきました。

それでは改めてVLOOKUP+,COLUMN関数の数式を確認してみます。

検索値は、$A2。

列方向にオートフィルで数式をコピーするので、列を固定した複合参照で設定します。

範囲は、$A$6:$G$10。絶対参照を設定しておきます。

列番号は、先程紹介したように、COLUMN(A1)*2+1

検索方法は、完全一致なので、FALSE

あとは、オートフィルで数式をコピーしたら完成です。

このように、VLOOKUP関数は、請求書や納品書などの帳票だけではなくて、アイディアによって、現場で色々使える関数です。

ある規則性がある場合には、VLOOKUP関数で考えてみるというのもいいかもしれませんね。

Excel。2列1組でセルを塗りつぶししたいけど、効率よく設定するにはどうしたらいい。【Conditional formatting】

Excel。2列1組でセルを塗りつぶししたいけど、効率よく設定するにはどうしたらいい。

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

データベースのルールに則ったExcelの表をテーブルにして、テーブルスタイルを、一行おきに塗りつぶしされているスタイルを反映させれば、一行おきにセルを塗りつぶししたデザインの表を作ることはできますが、列の場合はどうしたらいいのか？

さらに、次の表のように、2列1組をセットとして、セルを塗りつぶしたい場合、効率的に処理するには、どのようにしたらいいのでしょうか？

 

残念ながら、2列１組のスタイル（デザイン）は、Excelには用意されていませんので、自分で設定しなければいけません。

当然のことながら、大きな表だったら、範囲選択して塗りつぶしの設定をするだけでも、面倒ですし、大変です。

このようなケースでは、「条件付き書式」をつかえないかということを考えてあげるといいですね。

条件付き書式をつかうとしたら、どのような条件。

要するに、数式を作ることができるのかを考えてあげれば、対応方法が見つかるかもしれません。

あと使えそうなものは、列番号。

2組が同じだと判別できるようにするために、列番号は使えないかと考えます。

そこで、次のような数式を、今回はB12に作ってみました。

=MOD(COLUMN(B1),4)

2組1列と判断するためのルールを見つけるために、列番号を4で除算して、その余りを算出した値です。

塗りつぶしした列としていない列は、4列で構成されていますので、4で除算した余りを使えば、グループ分けすることができます。

あとは、この数式をアイデアとして使い、余りが「0」と「1」だったら、塗りつぶしを行う条件付き書式を設定してあげればいいわけですね。

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

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

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

 

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

=mod(column(),4)<2

という数式を設定して、書式を設定したら、OKボタンをクリックします。

余りが「0」と「1」のセルを塗りつぶしするというルールを設定します。

こうすることで、2列1組で、セルを塗りつぶしすることができました。

どのようなルールがあるのかを見つけることができれば、条件付き書式を設定することができますので、ルールやパターンを見つけるようにするといいかもしれませんね。

Excel。セル内の文字をセルごとに一文字ずつ入力したい。またその逆は？とにかく楽したい。【Characters in cells】

Excel。セル内の文字をセルごとに一文字ずつ入力したい。またその逆は？とにかく楽したい。

＜MID&COLUMN関数とCONCAT関数＞

あるアンケート結果の表があります。

B列の回答欄は、問1から問5までの質問結果をまとめた状態です。

例えば、NO1の「54132」は、問1を「5」問2を「4」というように答えています。

しかしながら、結果を連結してしまっているので、データとして使い勝手が悪いので、次のように、セルごとに結果を表示させたいわけです。

当然、自力でコツコツと入力するとしたら、膨大な時間を費やしてしまうのは明らかです。

Excel VBAでマクロを作ってとか思いがちですが、ちょっとした関数を使えば、比較的容易に、セルごとに文字を入力することができます。

文字列を指定された位置から、指定された文字を抽出したいわけですね。

このような時には、『MID関数』がオススメです。

では、C2に次のような数式を設定します。

=MID($B2,COLUMN(A1),1)

数式をオートフィルでコピーすれば、完成します。

では、簡単にMID関数でつくった数式の説明をします。

最初の引数。

「$B2」。

元の文字列はB列にあります。

オートフィルでコピーするので、複合参照にしています。

2番目の引数には、「COLUMN(A1)」と設定しました。

この2番目の引数は、開始位置を設定するわけですが、C2の数式は、一文字目ですが、D2は、二文字目を抽出させるようにする必要があります。

要するに、開始位置をずらさないといけません。

どうにか、１・２・３…とオートフィルでコピーした時にずれてほしい。

列番号を使えば対応することができそうです。

列番号を算出するには、「COLUMN関数」を使うことで、算出できます。

3番目の引数は、何文字、抽出するの？ということですから「1」と設定すればいいわけです。

これで、セル内の文字を、一文字ずつセルに入力することができます。

さて、今度は逆で、セルごとに一文字ずつ入力されているのを、一つのセルにまとめるには、どうしたらいいのでしょうか？

問1が○とか×ならば、B2には、PHONETIC関数を使う方法もありますが、今回のように数値だと上手くいきません。

今回のようにセルの文字を結合させるのに便利な関数があります。

それが「CONCAT関数」。

このCONCAT関数は新しく登場した関数で、CONCATENATE関数が進化した関数です。

では、B2に次の数式を設定して、オートフィルでコピーしてみましょう。

B2の数式は、

=CONCAT(C2:G2)

たったこれだけです。

これで、一つのセルに、まとめることができました。

CONCATENATE関数では、この範囲選択で文字接続をすることができなかったので、少々面倒でした。

Excel。一列置きのデータを合算したいけど、簡単に算出する方法はないかな？【Every other row】

Excel。一列置きのデータを合算したいけど、簡単に算出する方法はないかな？

＜SUMIF関数・MOD関数・COLUMN関数＞

やりたいことは簡単に思っても、実際にExcelでどうやったらいいのか？効率的なのか？考えるしまうことは結構あります。

今回は、前年と今年の売上高が並んでいる表があります。

 

H列やI列は、年ごとに合算値を算出しています。

さて、この合算値を算出するのに、効率的に行う方法はどうしたらいいのでしょうか？

合算といえば、SUM関数ですね。

H2に

=SUM(B2,D2,F2)

という数式を作成する。これも正解ですね。

ただ、SUM関数を使うのでもいいのですが、一列置きにデータがありますから、選択するにしても入力するとしても、面倒ですし、ミスを発生する危険性が高くなってしまいます。

今回は、このデータ量なのでいいですが、ボリュームが増えた場合は、さらに大変になることは、簡単に想像できます。

ポイントは、「一列置き」です。

2019年と2020年と判断できれば、条件付き和算のSUMIF関数を使うことができます。

そこで、注目するのは、列番号。

その列番号を2で除算すれば、余りがあれば『奇数』。

余りがなければ『偶数』と一列置きに分ける数値を算出することができます。

合算値を算出する前の状態の表をつかって、算出していきます。

 

B8には、次の数式を設定しました。

=MOD(COLUMN(),2)

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

B列なので、2という値が算出されます。

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

2で除算すれば、奇数偶数を確認することができます。

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

この数式をフィルハンドルをつかって、コピーします。

 

これで、奇数偶数を判断することができました。

つまり、2019年と2020年の列を判断するためのツールを得ることができたわけです。

あとは、SUMIF関数をつかうことで、簡単に2019年と2020年の合算値を算出できるわけです。

 

H2の数式は、

=SUMIF($B$8:$G$8,0,$B2:$G2)

SUMIF関数のダイアログボックスで確認してみましょう。

 

範囲は、$B$8:$G$8

フィルハンドルをつかって、数式をコピーする（オートフィル）ので、絶対参照を設定しておきます。

検索条件は、0（ゼロ）。2019年が0（ゼロ）2020年が1ですね。

合計範囲は、$B2:$G2

列だけ固定した複合参照にすることで、数式をコピーしたあとに数式を修正する必要がなくなります。

これで、2019年2020年の合算値を算出することができました。

 

これで完成といえば完成なのですが、区別をするための、8行目の数値が見えたままですね。

カッコ悪いので、0と1を表示しないようにしていきます。

間違えても、削除してはダメですね。

削除したら、SUMIF関数の検索条件が機能しませんので、元も子もありません。

また、文字の色を白色にするというのも、スマートじゃないので、表示形式のユーザー定義をつかって、非常時にしていきます。

B8:G8を範囲選択して、セルの書式設定ダイアログボックスを表示します。

 

表示形式の分類をユーザー定義にして、種類に、「;;;（セミコロン×3）」と入力してOKボタンをクリックします。

これで、非表示にすることができました。

 

「;;;（セミコロン×3）」にすると、文字を非表示にすることができますので、覚えておくと重宝します。

これで、完成しましたね。

まぁ、そもそも論として、データベースにしておけば、ピボットテーブルで簡単に算出できるのですが、帳票で管理している会社も多いので、様々な手法を知っていて損はありませんね。

Excel。今再びのVLOOKUP関数 引数：列番号をいちいち修正するのは面倒。【VLOOKUP】

Excel。今再びのVLOOKUP関数　引数：列番号をいちいち修正するのは面倒。

＜VLOOKUP+COLUMN関数＞

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

A2に氏名を入力すると、その詳細データがB2:F2まで抽出されるようになっています。

数件ならば、直接見つけて、コピー&ペーストしてもいいのですが、件数が増えてくると効率が悪化します。

そこで、氏名を入力したら抽出するようにしたいわけです。

そこで、登場するのがVLOOKUP関数です。

A2に氏名を入力してから数式を作っていきましょう。ダミーデータを入力しないまま作成すると、エラーが発生して、慣れていないとビックリしますので、注意が必要ですね。

また、数式は、手入力でもOKですが、今回はダイアログボックスで作成していきます。

B2をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。

検索値には、氏名を入力するセルですので、A2を設定します。

範囲ですが、A5:F16を範囲選択します。データですが、見出し行は不要ですので、範囲から除外します。

オートフィルを使って数式をコピーしますので、絶対参照を忘れずに、$A$5:$F$16と設定します。

列番号ですが、引数の範囲で選択した範囲から、抽出したい範囲は左から何列目なのかをExcelに教える必要があります。

今回は、国語の点数を抽出したいので、左から2列目にデータがあるので、列番号は「2」と設定します。

検索方法は、完全一致ですので、Falseと設定します。
Excelでは、False=0なので、0（ゼロ）でもOKですね。

B2の数式は、
=VLOOKUP(A2,$A$5:$F$16,2,FALSE)
では、横方向にオートフィルを使って数式をコピーしてみましょう。

【エラーが表示される：2つの修正箇所がある】

残念ながら、エラーが表示されてしまいました。
原因は、2か所あります。

F2の数式を確認してみましょう。
=VLOOKUP(E2,$A$5:$F$16,2,FALSE)

1つ目は、検索値ですね。E2になっています。A2でなければいけません。
ここは、行を固定した、複合参照で設定する必要がありました。

2つ目は、列番号。
「2」のままですね。これでは、国語の点数を抽出しっぱなしになります。

オートフィルを使って数式をコピーしても、自動的に3・4と加算してくれません。

2・3か所の数式を変更するだけならいいのですが、変更する数式が多くなると修正が面倒です。
何かいい方法はないのでしょうか？

そこで、列情報を算出することができる、COLUMN関数を使うことで対応することができます。

それでは、B2の数式を修正してみましょう。

VLOOKUP関数ダイアログボックスを再度表示しても構いませんが、手入力で直接数式を修正します。
=VLOOKUP(A2,$A$5:$F$16,2,FALSE)
を
=VLOOKUP($A2,$A$4:$F$16,COLUMN(),FALSE)
と修正します。

オートフィルを使って数式をコピーして確認してみましょう。

このように、エラーが表示されず、抽出することができました。

なお、今回は、たまたま2列目だったので、COLUMN()で大丈夫でしたが、列番号と合致するように調整が必要な場合は、COLUMN()+1など加算減算することで対応することが可能です。