Excel。VLOOKUP関数で複数の離れた列を表引きしたい。【distance】

Excel。VLOOKUP関数で複数の離れた列を表引きしたい。

＜VLOOKUP関数＞

A1:H5には、四半期の売上表があります。

A8にNOを入力すると、該当するデータがA1:H5から表引きされるようにしたいわけです。

このような表引きをするならば、VLOOKUP関数をつかうと対応できそうです。

しかし、B8:E8までにVLOOKUP関数をつかって、設定する場合、列番号が異なるので複数のVLOOKUP関数を設定する必要があります。

また、連続していれば、列番号に、COLUMN関数をつかうことで、対応することもできますが、今回は、表引きしたいデータが離れています。

これでは、COLUMN関数をつかうことができません。

そこで、VLOOKUP関数の列番号に配列数式をつかうことで、対応することができます。

B8にVLOOKUP関数の数式を設定します。

=VLOOKUP(A8,A2:H5,{2,4,6,8},FALSE)

今回は、検索値が空欄時に発生する、#N/Aは考慮しておりませんので、考慮する場合には、IF関数かIFERROR関数で対応します。

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

これで、複数の離れた列から表引きすることができました。

では、VLOOKUP関数を確認しておきましょう

最初の引数は、検索値です。A8を設定します。

この番号を変えることで、該当するデータを表引きします。

2つ目の引数は、範囲です。

A2:H5。

表引きの件数が1件なので、絶対参照は不要です。

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

2つ目の引数で設定した範囲の左側から何列目のデータを抽出するのかという数値なのですが、離れた列になります。

そこで、

{2,4,6,8}

という配列数式にすることで、表引きすることができます。

通常の「()」ではなくて「{}」で囲むことで、配列数式にすることができます。

旧来は、先に表引きをすべて範囲選択して、数式の確定時にCtrl+Shift+Enterをすることで配列数式にすることができましたが、スピル機能によって、不要になりました。

数式の作成は、最初から「{}」で表引きしたい列番号を囲うだけです。

表引きしたい列は、2,4,6,8列です。

最後の引数は、検索方法です。

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

Excel。NETWORKDAYS関数は日付の間の稼働日数を算出します【NETWORKDAYS】

Excel。NETWORKDAYS関数は日付の間の稼働日数を算出します

＜関数辞典：NETWORKDAYS関数＞

NETWORKDAYS関数

読み方： ネットワークデイズ　　

分類： 日付時刻 

NETWORKDAYS(開始日,終了日,[祭日])

日付の間の稼働日数を算出します

Excel。動画で紹介。時間の引き算でトラブル発生！なぜ引き算をやってくれないの？【subtraction】

Excel。動画で紹介。時間の引き算でトラブル発生！なぜ引き算をやってくれないの？

＜Youtube＞

Excelは、時間計算が苦手だったりします。

例えば、夜勤とかで、21:00出勤で翌朝5:00に退勤した場合の勤務時間を算出したい場合は、「引き算」をつかえばいいはず。

ところが、結果は「#######」って。

なんで、どうして？

列幅を広げても、変わらない。

よく見るとメッセージが表示さている。

「負であるか、大きすぎる日付および時間は、######」って？

このようなケースのトラブルを回避する方法をご紹介しております。

Excel。積み上げ横棒グラフに合計値を表示させたい【Stacked bar chart】

Excel。積み上げ横棒グラフに合計値を表示させたい

＜積み上げ横棒グラフ＞

積み上げ横棒グラフをつくるのは、簡単なのですが、出来そうで出来ないものがあります。

それは、合計値です。

 

それ以外にも、横棒グラフならではの縦軸を反転しないとグラフの元表と同じ順番になりません。

この2点を含めて、積み上げ横棒グラフに合計値を表示させるには、どうしたらいいのかを紹介いたします。

積み上げ横棒グラフ用に用意した表です。

この表のポイントは、合計列を用意していることです。

4月から6月までの売上の合算した数値を自動的に表示する機能は、現時点のExcelにはありませんので、用意する必要があります。

E2の数式は、

=SUM(B2:D2)

といういたってシンプルな数式です。

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

積み上げ横棒グラフをつくりますので、A1:E4を範囲選択します。

E列の合計列も忘れずに含めます。

挿入タブのグラフブロックにあり、積み上げ横棒グラフをつかって、積み上げ横棒グラフを挿入します。

サイズ変更をして、グラフタイトルは今回グラフを大きくしたいので削除してあります。

挿入されましたが、縦軸が店舗名になっていません。

このような場合は、グラフのデザインタブにある「行/列の切り替え」をクリックします。

縦軸が店舗名に変えることができました。

さて、ここから修正作業を行っていきます。

グラフのデザインタブにある「グラフの種類の変更」をクリックします。

グラフの種類の変更ダイアログボックスが表示されます。

すべてのグラフタブの「組み合わせ」をクリックします。

系列名を次のように変更します。

4月売上から6月売上は、「積み上げ横棒」で、第2軸にチェックをいれます。

合計は、「集合縦棒」にして、第2軸は、何もしません。

第2軸が前面に、通常の第1軸が背面に設定されるので、4月から6月を前面にしたいので、第2軸にします。

合計はあくまでも、データラベルのためだけなので、背面の方がむしろ都合がいいというわけです。

OKボタンをクリックします。

第2横軸がグラフ上部に表示されましたが、右側に第2縦軸が表示されていないので、表示する作業を行います。

右側に第2縦軸を表示させる理由は、左側の縦軸が、上から立川・渋谷・新宿の順番になっています。

グラフの元表と順番が上下逆です。

グラフの元表と同じようにする作業をするために必要になります。

グラフのデザインタブの「グラフ要素の追加」にある軸ラベルの「第2縦軸」をクリックします。

続いて、上の第2横軸と同じように、下の第1横軸の目盛を0～9000に合わせます。

第1横軸をクリックします。

書式タブにある、選択対象の書式設定をクリックします。

画面の右側に軸の書式設定作業ウィンドウが表示されます。

軸のオプションにある境界値の最小値を「0」にします。

このあと、作業ウィンドウをつかいますので、そのまま表示しておきます。

左側の第1縦軸をクリックします。

作業ウィンドウが第1縦軸に対応します。

横軸との交点を「最大項目」。

軸位置の「軸を反転する」にチェックマークをいれます。

これで、縦軸の順番は表と同じ、新宿・渋谷・立川になりました。

ただ、右側の第2縦軸はそのままなので、第2縦軸をクリックします。

第2縦軸は、横軸との交点を「自動」にします。

ここがわかりにくいので、注意ポイントです。

軸位置は、「軸を反転する」にチェックマークをいれます。

ここまでで、縦軸を表と同じ順番にすることができました。

いよいよ、本題の合計値を表示する工程に進めましょう。

合計の集合横棒グラフは、背面あるので、さわることができません。

そこで、書式タブのグラフの要素を「系列 "合計"」にします。

グラフのデザインタブにある「グラフ要素の追加」のデータラベルの「外側」をクリックします。

これで、合計値を表示することができました。

あとは、仕上げの作業です。

表示する必要がない上の横軸をクリックして、DELキーで削除します。

右側の第2縦軸をクリックします。DELキーで削除したいところですが、よくみると、目盛線が表示されているので、作業ウインドウをつかって処理します。

軸のオプションの目盛にある目盛の種類を「なし」

ラベルの位置は「なし」

に変更します。

最後に、凡例に表示されている、「合計」だけを選択して削除したら完成です。

Excel。NEGBINOMDIST関数は負の二項分布の確率を算出します【NEGBINOMDIST】

Excel。NEGBINOMDIST関数は負の二項分布の確率を算出します

＜関数辞典：NEGBINOMDIST関数＞

NEGBINOMDIST関数

読み方： ネガバイノムディスト

読み方： ネガティブバイノミアルディストリビューション

分類： 互換性 

NEGBINOMDIST(失敗数,成功数,成功率)

負の二項分布の確率を算出します

Excel。2025/6/1-6/7に範囲選択などを紹介したFacebookページのコメントです。【comment】

Excel。2025/6/1-6/7に範囲選択などを紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

6月1日

Excel

範囲選択が基本中の基本ですよね。

6月2日

Excel。

範囲選択。複数のセルを選択するときは、Ctrlキーを押しながらクリックですね。

6月3日

Excel。

範囲選択で、広い範囲を選択するときは、Shiftキーを押しながらクリック技が便利ですね。

6月4日

Excel。

範囲選択の表現は、A1:B5と書きますが、これは、A1～B5までを範囲選択という意味ですね。

6月5日

Excel。

範囲選択で、行を選択する場合は、行番号をクリックすると行選択できますね。

6月6日

Excel。

範囲選択で、列を選択する場合は、列番号をクリックすると列選択できますね。

6月7日

Excel。

範囲選択で、隣接しているセル・行・列は、Ctrlキーを押すのではなく、ドラッグ操作ですね。

Excel。VBA。特定の色で塗りつぶされたセルのみを数えたい

＜VBA：色を数える＞

Excelは、単純に塗りつぶしたセル数を求められる関数は用意されていませんので、一発で求めることはできません。

どうしても数えたい場合には、オートフィルターをつかって抽出した件数を求めることはできますが、少し面倒です。

そこで、Excel VBAをつかって、指定した色で塗りつぶされているセルだけを求めるプログラム文をつくることにしました。

今回用意した表です。

B2:D5で、A7と同じ色で塗りつぶされているセルの数を知りたいわけです。

B5とC4は数えたくないわけです。

そのため、単純に、セルが塗りつぶされているかどうかで、判断するわけにはいきません。

指定されている色を把握する必要があります。

では、Excel VBAのプログラム文を確認してみます。

Sub 指定した色を数える()

    Dim kinmuhani As Range

    Dim yasumi As Range

    Dim Cell As Range 

    Dim i As Integer 

    

    Set kinmuhani = Range("B2:D5")

    Set yasumi = Range("A7")

    

    i = 0 

    For Each Cell In kinmuhani

        If Cell.Interior.Color = yasumi.Interior.Color Then

            i = i + 1

        End If

    Next Cell

    

    Range("B7").Value = i

End Sub

まずは、実行してみましょう。

A7と同じ色で塗りつぶされているセルは2つありますので、正しく求めることができています。

では、プログラム文を確認しておきましょう。

まずは、変数宣言です。

Dim kinmuhani As Range　　範囲選択を代入させます

Dim yasumi As Range　　A7の塗りつぶしの色情報を代入します。

Dim Cell As Range　　セルをチェックする変数です。

Dim i As Integer 　　一致するセルの数を格納する変数です。

用意した変数に、指定した範囲選択を代入します。

Set kinmuhani = Range("B2:D5")

Set yasumi = Range("A7")

For Each文で範囲選択内を繰り返して、チェックします。

For Each Cell In kinmuhani

    If Cell.Interior.Color = yasumi.Interior.Color Then

        i = i + 1

    End If

Next Cell

範囲選択内のセルの塗りつぶした色と、A7の塗りつぶした色が同じならば、変数iに1を加算します。

このようなプログラムを用意しておくと、単純な塗りつぶしされているセルを数えるには重宝するかと思います。