7/31/2025

Excel。1から2万までの連番を手早くつくるには、どうしたらいいの。【Continuous Data】

Excel。1から2万までの連番を手早くつくるには、どうしたらいいの。

<フィル機能:連続データ>

A2から連番を1から20000までつくりたいのですが、オートフィルのドラッグでは、20000までドラッグしなければなりません。


大変です。


また、左右どちらかに、20000までのデータがあれば、Excel側で、そこまでが表の終わりの行だと判断して、20000まで連番を振ってくれることもありますが、今回は、A列のみです。

1から2万までの連番を手早くつくるには、どうしたらいいの。

そこで、フィル機能をつかって、1から20000までの連続データをつくります。


A2をクリックして、ホームタブにある「フィル」をクリックします。


メニューの中に、「連データの作成」がありますので、クリックすると、連続データダイアログボックスが表示されます。


範囲は、「列」にします。


種類は、「加算」になっていることを確認して、


停止値には、「20000」と入力したらOKボタンをクリックします。


これで、1から20000までの連番を設定することができました。


フィル機能の連続データをつかうと、大量連番を設定するには、いいのかもしれませんね。

7/30/2025

Excelのショートカットキー。Ctrl+W~Zまでを紹介【shortcut】

Excelのショートカットキー。Ctrl+W~Zまでを紹介

<ショートカットキー>

作業効率もUPする、知っていると便利なショートカットキー。

Excelのショートカットキー

Ctrl+W

ブックを閉じる



Ctrl+X

切り取りする



Ctrl+Y

やり直す



Ctrl+Z

元に戻す

7/29/2025

Excel。累積確立から正規分布の数値を逆算するのがNORMINV関数です。【NORMINV関数】

Excel。累積確立から正規分布の数値を逆算するのがNORMINV関数です。

<関数辞典:NORMINV関数>

NORMINV関数

読み方: ノーマルインバース  

分類: 互換性 

NORMINV関数

NORMINV(確率,平均,標準偏差)

累積確立から正規分布の数値を逆算する

7/28/2025

Excel。縦横の見出しの交わるデータをXLOOKUP関数で抽出してみた【Cross Tabulation】

Excel。縦横の見出しの交わるデータをXLOOKUP関数で抽出してみた

<XLOOKUP関数>

縦横の見出しの交わるところにあるデータを抽出する方法は、色々あります。

縦横の見出しの交わるデータをXLOOKUP関数で抽出

例えば、INDEX関数とMATCH関数を組み合わせて使う方法があります。


INDEX関数とMATCH関数の組み合わせは慣れると、コツをつかむことができるのですが、少し難解なところもあります。


そこで、今回は、XLOOKUP関数だけで、対応してみたいと思います。


やりたいことは、B7に曜日、B8に時限を設定したら、B9に該当する講義内容を表示するというものです。


B7が金曜日で、B8が2限ということなので、金曜日の2限は、「PowerPoint」です。


このPowerPointをXLOOKUP関数だけで、表示していきます。


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


=XLOOKUP(B7,B1:F1,XLOOKUP(B8,A2:A5,B2:F5,"",0,1))

これで、縦見出しと横見出しがクロスするデータを抽出することができます。


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


数式の構造は、XLOOKUP関数にXLOOKUP関数をネストさせています。


最初のXLOOKUP関数ですが、

最初の引数の検索値は、曜日が設定されている、B7です。


2つ目の引数の検索範囲は、B1:F1の曜日を設定します。


3つ目の引数は、戻り範囲です。

ここに、XLOOKUP関数をネストしていきます。


4つ目以降の引数は、省略可能です。

今回は、ネストのXLOOUP関数を設定すれば大丈夫なので、省略しました。


では、ネストである、戻り範囲で設定したXLOOKUP関数をみていくことにします。


XLOOKUP(B8,A2:A5,B2:F5,"",0,1)

1つ目の引数は、検索値なので、時限が設定されているB8。


2つ目の引数は、検索範囲なので、A2:A5を設定します。


3つ目の引数は、戻り範囲なので、B2:F5の教科を範囲選択します。


4つ目の引数は、見つからない場合なので、「””(ダブルコーテーション×2)」で空白とします。


5つ目の引数は、一致モード。今回は、完全一致にしたいので、「0」を採用します。


6つ目の引数は、検索モード。上側からでかまわないので、「1」と設定します。


このように、INDEX関数+MATCH関数の組み合わせでもいいのですが、XLOOKUP関数を重ねてつかうことでも対応することができます。

7/27/2025

Excel。動画で紹介。単純にできない?!セル結合おきに塗りつぶし。【Cell Merging】

Excel。動画で紹介。単純にできない?!セル結合おきに塗りつぶし。

<Youtube>

Excelの帳票で、よく「セル結合」をみかけます。


セル結合は、見た目はいいのですが、Excelの機能からみると、結構面倒なことになります。


たとえば、セル結合おきに手早く、行全体を塗りつぶしをしたい場合です。


単純に一行おきでは、対応できません。


その対応方法をご紹介しております。

7/26/2025

Excel。NORMDIST関数は、平均と標準偏差に対する正規分布の確率を算出します【NORMDIST】

Excel。NORMDIST関数は、平均と標準偏差に対する正規分布の確率を算出します

<関数辞典:NORMDIST関数>

NORMDIST関数

読み方: ノーマルディスト

読み方: ノーマルディストリビューション

分類: 互換性 

NORMDIST関数

NORMDIST(x,平均,標準偏差,関数形式)

平均と標準偏差に対する正規分布の確率を算出します

7/25/2025

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい【Protecting a Sheet】

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい

<シートの保護・条件付き書式・CELL関数>

シートの保護をすることで、セルに入力することを防ぐことができます。


ただ、単純にシートの保護をおこなってしまうと、シート全体に保護がされてしまうので、全く入力することができません。


そこで、一部だけ入力することができるようにするのが、「セルのロック」をオフにします。

入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい

なお、シートの保護も、セルのロックも、ホームタブの書式の中にあります。


セルのロックをオフにして解除したら、シートの保護をおこないます。


これで、セルのロックをオフにしたセルのみ入力することができるのですが、見た目、どこのセルに入力することができるのか、全くわかりません。


そこで、セルのロックをオフにしているセルがわかるように、セルを塗りつぶしたいわけですが、どのようにしたいいのでしょうか。


セルのロックがオフという条件ということなので、条件付き書式をつかいます。


問題となるは、条件付き書式の条件をどのようにしたらいいのかということです。


セルの状態を確認するには、CELL関数をつかうことで対応することができます。


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

シート全体を範囲選択します。


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


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

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


条件式を設定します。

=cell(“protect”,a1)=0


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


これで、シートの保護がされても、入力することができる、ロックの解除がオフのセルを見つけることができました。


条件式にCELL関数をつかっていますが、その数式を確認しておきましょう。


CELL関数は、セルの状況を確認することができる関数です。


引数に、protect をつかうことで、セルのロックがオンかオフかを確認することができます。


1ならば、セルのロックはオンになっています。

よって、今回はオフなのを探したいので、「=0」とします。

7/24/2025

Excel。2025/6/29-7/5にCtrl+cなどショートカットキーを紹介したFacebookページのコメントです。【comment】

Excel。2025/6/29-7/5にCtrl+cなどショートカットキーを紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

6月29日

Excel。

オートSUMボタンの▼で、数値の個数は文字を数えてくれません。

氏名や商品名では使えないので、気を付けないといけませんね。



6月30日

Excel。

行の高さをマウスで変更したい時は、行番号の間の境界線上でマウスカーソルを上下の形に変わったら、ドラッグすると変更できますね。



7月1日

Excel。

列の広さをマウスで変更したい時は、列番号の間の境界線上でマウスカーソルを左右の形に変わったら、ドラッグすると変更できますね。



7月2日

Excel。

列の幅をマウスで変更したい時は、列番号の間の境界線上でマウスカーソルを左右の形に変わったら、ダブルクリックすると、自動で幅を調整してくれます。



7月3日

Excel。

行の幅をマウスで変更したい時は、行番号の間の境界線上でマウスカーソルを上下の形に変わったら、ダブルクリックすると、自動で幅を調整してくれます。



7月4日

Excel。

ショートカット。

範囲選択して、Ctrl+cでコピーボタンと同じ効果になりますね。



7月5日

Excel。

ショートカット。

範囲選択して、Ctrl+xで切り取りと同じ効果になりますね。

7/23/2025

Excel。累積確立から標準正規の数値を逆算するのがNORM.S.INV関数です。【NORM.S.INV】

Excel。累積確立から標準正規の数値を逆算するのがNORM.S.INV関数です。

<関数辞典:NORM.S.INV関数>

NORM.S.INV関数

読み方: ノーマル・スタンダード・インバース  

分類: 統計 

NORM.S.INV関数

NORM.S.INV(確率)

累積確立から標準正規の数値を逆算する

7/22/2025

【Excel時短】元データはそのまま!複数条件で「自動並べ替え別表」を作る革新関数【Multi-column】

【Excel時短】元データはそのまま!複数条件で「自動並べ替え別表」を作る革新関数

<SORTBY関数>

次の店舗別の販売金額一覧表があります。

元の表はそのままで、複数列を基準に並べ替えした別表をつくりたい

この表を地域別でかつ、販売金額を降順で並べ替えをしたい場合、どのようにしたいいのでしょうか。


そして、単純に並べ替えをするのではなく、この表はそのままで、別表で並べ替えた表を用意したい。


このような場合、A1:D8のデータを別のところにコピーして、コピーした表を使って、データタブにある並べ替えをおこなうというのが、普通だと思います。


ただ、作業としては簡単でも、作業数が多くて、面倒です。


そこで、SORTBY関数をつかうと、手早く、地域別でかつ、販売金額を降順で並べ替えをした表をつくることができます。


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


=SORTBY(A2:D8,C2:C8,1,D2:D8,-1)

これで、地域ごとの販売金額降順で別表をつくることができました。


SORTBY関数は、直接セル範囲を指定して並べ替えを行うことができます。


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


最初の引数は、配列。

範囲選択なので、A2:D8を設定します。


2番目の引数は、基準1。

最初の並べ替えをしたい条件列なので、C2:C8。


3番目の引数は、2番目の引数で設定した基準1。

これを昇順で並べ替えをしたいので、「1」を設定します。

昇順が「1」で降順が「-1」です。


あとは、繰り返して設定するだけです。

これで、元表は残ったままで、複数列を基準にした別表をつくることができます。


なお、並べ替えをするSORT関数というのもありますが、単純な並べ替えだけです。


複数列を基準にする場合には、入れ子にしなければならないので、SORTBY関数のほうが可読性が高いです。

7/21/2025

Excel。動画で紹介。数える関数のCOUNTA関数は空白は数えないはずでは?どう解決するの。【count】

Excel。動画で紹介。数える関数のCOUNTA関数は空白は数えないはずでは?どう解決するの。

<Youtube>

数値を数えてくれるのが、COUNT関数。

数値も文字は数えて、空白は除いて数えてくれるのが、COUNTA関数。

ところが、ある状況での空白は、除いてくれません。


つまり、数える対象になってしまうのです。


そのため、正しい件数を算出できない場合があります。

その対応方法だけではなく、空白を数える方法も併せて、ご紹介しております。

7/20/2025

Excel。標準正規分布の累積確率を算出するのが、NORM.S.DIST関数です。【NORM.S.DIST】

Excel。標準正規分布の累積確率を算出するのが、NORM.S.DIST関数です。

<関数辞典:NORM.S.DIST関数>

NORM.S.DIST関数

読み方: ノーマル・スタンダード・ディスト 

読み方: ノーマル・スタンダード・ディストリビューション

分類: 統計 

NORM.S.DIST関数

NORM.S.DIST(z,関数形式)

標準正規分布の累積確率を算出します


7/19/2025

Excel。VBA。範囲内の別々の色で塗りつぶされているセルの件数を知りたい【color】

Excel。VBA。範囲内の別々の色で塗りつぶされているセルの件数を知りたい

<Excel VBA>

範囲選択内で別々の色で塗りつぶされているセル。

範囲内の別々の色で塗りつぶされているセルの件数を知りたい

それぞれいくつあるのかを、数えたいわけですが、Excelには、塗りつぶされているセルを数える関数はありません。


オートフィルターを使う方法で件数を求めることはできますが、色の数が増えれば増えるほど、大変です。


そこで、今回は、Excel VBAをつかって、範囲選択内の塗りつぶした色ごとに何件あるのかを求めるプログラム文をつくってみました。


Sub 三色を数える()

    Dim kinmuhani As Range  

    Dim yasumi As Range 

    Dim SampleCell As Range  

    Dim Cell As Range   

    Dim i As Integer  

    Dim OutputCell As Range 

    

    Set kinmuhani = Range("B2:D5")

    Set yasumi = Range("A7:A9")

    

    Set OutputCell = Range("B7")


    For Each SampleCell In yasumi

        i = 0   


        For Each Cell In kinmuhani

            If Cell.Interior.Color = SampleCell.Interior.Color Then

                i = i + 1

            End If

        Next Cell


        OutputCell.Value = i


        Set OutputCell = OutputCell.Offset(1, 0)

    Next SampleCell

End Sub


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


赤が2で、他の色が1と求めることができました。


では、プログラム文を説明します。


変数宣言以降です。


Set kinmuhani = Range("B2:D5") B2:D5の色を数えいたので、B2:D5を代入しています。


Set yasumi = Range("A7:A9") 数えたい色のサンプルが入力されている範囲のA7:A9を代入します。


Set OutputCell = Range("B7") 最初の色の件数を表示するセルを代入しています。


For Each文をつかった繰り返し処理です。

1色ではないので、For Each文の中に、For Each文が設定されています。


各基準セルについてループ処理

For Each SampleCell In yasumi  A7:A9のサンプルが終わるまで繰り返し処理

    i = 0 色の件数の変数を初期化します。

For Each Cell In kinmuhani

サンプルの塗りつぶした色を同じだったら、カウントさせます。

    If Cell.Interior.Color = SampleCell.Interior.Color Then

        i = i + 1

    End If

Next Cell

    OutputCell.Value = i カウントした結果を代入

    Set OutputCell = OutputCell.Offset(1, 0)

結果を表示させる、2件目から次の行にしたいので、Offsetを使用しています。

Next SampleCell


このような方法で、塗りつぶしている色ごとの件数を求めることができます。

7/18/2025

Excel。2025/6/22-6/28にエラーなどを紹介したFacebookページのコメントです。【comment】

Excel。2025/6/22-6/28にエラーなどを紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

6月22日

Excel。エラー。

#NAME?

ネーム

正式は、Unrecognized Name (アンリコグナイズド・ネイム)です。

関数名やセル範囲名などの名前が正しくない時に登場します。



6月23日

Excel。エラー。

#NUM!

ナム

正式は、An Invalid Number (アン・インヴァリッド・ナンバー)です。

数値が大きすぎる。または、小さすぎると登場します。



6月24日

Excel。エラー。

#N/A

エヌエー

正式は、No Assign (ノー・アサイン)です。

値がない時に登場します。Vlookup関数の時によく見かけますね。



6月25日

Excel。

エラーじゃないのですが、表示が#######となると、桁あふれしているだけなので、列幅を広げれば大丈夫ですね。



6月26日

Excel。

オートSUMボタンの▼で、平均を選べば簡単に平均が算出できますね。



6月27日

Excel。

オートSUMボタンの▼で、最大値を選べば簡単にその範囲の中での最大値が算出できますね。



6月28日

Excel。

オートSUMボタンの▼で、最小値を選べば簡単にその範囲の中での最小値が算出できますね。

7/17/2025

Excel。累積確立から正規分布の数値を逆算するのがNORM.INV関数です。【NORM.INV】

Excel。累積確立から正規分布の数値を逆算するのがNORM.INV関数です。

<関数辞典:NORM.INV関数>

NORM.INV関数

読み方: ノーマル・インバース  

分類: 統計 

NORM.INV関数

NORM.INV(確率,平均,標準偏差)

累積確立から正規分布の数値を逆算する

7/16/2025

Excel。OR関数の引数がたくさんあるので、数式がわかりにくい【many】

Excel。OR関数の引数がたくさんあるので、数式がわかりにくい

<IF+OR関数>

遠方の事務所は会議に参加するのにオンラインで参加OKという表をつくりました。

OR関数の引数がたくさんあるので、数式がわかりにくい

C列に地域というフィールドがあります。


D列のオンラインというフィールドには、C列の地域が、東北・四国・九州だったらば、オンラインで会議に参加。

それ以外は、リアルで参加するという表です。


D列のオンラインには、東北・四国・九州ならば、○と判断する数式を設定したいわけです。


C2が東北だったら、四国だったら、九州だったらと判断したいわけです。


判断となれば、IF関数をつかうわけですが、IF関数だけだと、ネストだらけになってしまいま

す。

そこで、OR関数をつかうことで、可読しやすい数式にすることができます。


D2に設定した数式は、

=IF(OR(C2="東北",C2="四国",C2="九州"),"○","")


この数式でも可読性はいいのですが、OR関数の引数を「C2=」が多く繰り返される傾向にあります。

今回は、3つで済んでいますが、10あれば、「C2=」が10も作る必要があります。


これでは、可読性が悪化してしまいます。


そこで、OR関数の引数に、配列定数をつかうことで、改善することができます。


D2の数式を修正します。


=IF(OR(C2={"東北","四国","九州"}),"○","")


配列定数をつかうことで、OR関数は、

C2={"東北","四国","九州"}

と、とてもわかりやすくなりました。


これならば、10件だったとしても、「C2=」は一つだけですみます。

7/15/2025

Excel。動画で紹介。スピルさせない数式ってつくれないの【Spill】

Excel。動画で紹介。スピルさせない数式ってつくれないの

<Youtube>

Excelに新しく追加した機能。

スピル(spill)


あふれる という意味です。

わかりやすくいえば、オートフィルをしなくても数式が自動的に広がってくれる便利な機能です。


スピルで作られた数式は、数式バーで確認すると、グレーで表示されています。

この数式を、「ゴースト」といいます。


このスピル機能は便利なのですが、関数によっては、なんでもかんでも、スピル機能によって、ゴーストが発生し、余計なことをすることがあります。


場合によっては、スピル機能にならないように数式を作りたい場合、どのようにしたらいいのでしょうか。


実は、数式にあるものを追加すると、

スピルさせない数式をつくることができます。


その方法をご紹介しております。

7/14/2025

Excel。平均と標準偏差に対する正規分布の確率を求めるならNORM.DIST関数【NORM.DIST】

Excel。平均と標準偏差に対する正規分布の確率を求めるならNORM.DIST関数

<関数辞典:NORM.DIST関数>

NORM.DIST関数

読み方: ノーマル・ディスト

読み方: ノーマル・ディストリビューション

分類: 統計 

NORM.DIST関数

NORM.DIST(x,平均,標準偏差,関数形式)

平均と標準偏差に対する正規分布の確率を算出する

7/13/2025

Access。クエリ。10歳単位の年齢分布の集計をしたい【age】

Access。クエリ。10歳単位の年齢分布の集計をしたい

<Partition関数>

顧客管理のテーブルがあります。


これを10歳代ごとに何人いるのかという、年齢分布の集計を求めたい。


年齢フィールドに年齢が入力されています。


Excelにエクスポートして求めたらと思うかもしれませんが、Excelでは、度数分布を求めるのは、少々面倒です。


そこで、今回は、Accessのクエリで、10歳単位の度数分布を作ってみます。


作成タブのクエリデザインをつかいます。


区分の演算フィールドをつくります。


区分: Partition([年齢],10,100,10)


そして、件数が必要なので、NOフィールドを設定したら、集計行を表示しますので、クエリデザインタブの集計ボタンをクリックします。


NOフィールドの集計方法は、「カウント」とします。


では、実行して確認してみましょう。

Partition関数

このように、10歳代単位の年齢分布の集計を求めることができました。


今回のテーブルには20歳以下の人はいなかったので、20:29からになっています。


このように、Partition関数をつかうことで、度数分布をAccessのクエリで手早く求めることができます。

7/12/2025

Excelのショートカットキー。Ctrl+R~Vまでを紹介【shortcut】

Excelのショートカットキー。Ctrl+R~Vまでを紹介

<ショートカットキー>

作業効率もUPする、知っていると便利なショートカットキー。

Excelのショートカットキー

Ctrl+R

左のセルの内容を右にコピーする



Ctrl+S

ファイルを保存する



Ctrl+T

[ テーブルの作成 ] ダイアログ ボックスを表示



Ctrl+U

下線を引く



Ctrl+V

貼り付ける

7/11/2025

Excel。NOMINAL関数は名目年利率を算出できます。【NOMINAL】

Excel。NOMINAL関数は名目年利率を算出できます。

<関数辞典:NOMINAL関数>

NOMINAL関数

読み方: ノミナル  

分類: 財務 

NOMINAL関数

NOMINAL(実行利率,複利計算期間)

名目年利率を算出します

7/10/2025

Excel。大きな表から、必要な列だけ集めた別表をつくるに、楽な方法は無いのかな

Excel。大きな表から、必要な列だけ集めた別表をつくるに、楽な方法は無いのかな

<CHOOSECOLS関数>

大きな表から必要な列だけを抽出した別表をつくるとなると、コピーで貼り付けという作業は、ただただ、面倒な作業だといえます。


用意したA1:H5の表は、飛び飛びに月別の売上の列が用意されています。


この表から、売上だけを抽出したA7:E11までの表をつくりたいわけです。


この程度の列数ならば、コピー&ペーストで解決できますが、列数が増えれば面倒です。


また、数式だった場合は、#REF!というエラーが表示される場合もあります。


値で貼り付けをつかうとしても、元の表で数値が変わった場合、抽出先のデータは、変わっていませんので、再度コピーする必要があります。


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

CHOOSECOLS関数

A8にCHOOSECOLS関数をつかって数式を設定します。


=CHOOSECOLS(A2:H5,1,2,4,6,8)


これで、売上の列だけを抽出した別表をつくることができました。


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


CHOOSECOLS関数は、範囲から必要な列を抽出することができる関数です。


最初の引数が、array。

範囲選択なので、A2:H5を範囲選択します。


残りの引数は、範囲選択した中で、左から必要な列番号を設定するだけです。


これで、必要な列だけの別表をつくることができます。

7/09/2025

Excel。2025/6/15-6/21に#DIV/0!などを紹介したFacebookページのコメントです。【comment】

Excel。2025/6/15-6/21に#DIV/0!などを紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

6月15日

Excel。

Σ

オートSUMボタンをクリックして、範囲を決定するだけで合計を求めることができます。



6月16日

Excel。

オートSUMボタンで、合計するときに離れている所を計算したい時は、Ctrlキーでクリックすれば、算出できますね。



6月17日

Excel。

エラーが表示されるとビックリしますよね。

けど、ちょっと知っていると、ビックリしなくなります。



6月18日

Excel。

エラー。

#NULL!

ヌル

正式は、Null Intersection (ナル・インターセクション)です。

セル指定の「:(コロン)」や「,(カンマ)」がない時や、セル範囲に共通部分がないと登場します。



6月19日

Excel。

エラー。

#DIV/0!

ディブゼロ

正式は、DIVided by 0(ディヴァイディッド・バイ・ゼロ)です。

0で割り算をしちゃうと、登場しますね。



6月20日

Excel。

エラー。

#VALUE!

バリュー

正式は、Wrong Type Value (ローング・タイプ・バリュー)です。

不適切なデータが入っている時に登場しますね。



6月21日

Excel。

エラー。

#REF!

レフ

正式は、A REFerence to a cell that does not exit (ア・リファレンス・トゥ・ア・コール・ザット・ダズ・ノット・イグジット)です。

セルが参照できない時に登場します。

7/08/2025

Excel。動画で紹介。トラブル発生!IF関数では指定の文字が含まれているか判断できない【date】

Excel。動画で紹介。トラブル発生!IF関数では指定の文字が含まれているか判断できない

<Youtube>

例えば、住所に横浜市という文字が含まれていたら○と判断したい。


このような場合、IF関数をつかえば、対応できるように思えます。



含まれるということで「*横浜市*」と

ワイルドカードをつかって、論理式をつくってみると…


判断の結果が!?


では、どのようにしたら、解決することができるのか。

その方法をご紹介しています。

7/07/2025

Excel。XLOOKUP関数で連続する複数の項目を表引きしたい【Search and Extract】

Excel。XLOOKUP関数で連続する複数の項目を表引きしたい

<XLOOKUP関数>

連続する項目を表引きしたい場合、VLOOKUP関数だと何度も数式を作る必要があります。


例えば、次のような表。

XLOOKUP関数で連続する複数の項目を表引きしたい

A1:F9に表があって、A11:F13に表引きの結果を表示したいわけです。


VLOOKUP関数の場合、B12に設定した数式を、オートフィルで数式をコピーする場合、まず、検索値を列固定の複合参照にした上で、3番目の引数の「列番号」をコピー後に一つずつ修正する。

あるいは、COLUMN関数をつかって列番号に対応する必要があります。


慣れれば特に問題はないかもしれませんが、少々面倒です。


そこで、XLOOKUP関数をつかってみたらどうなるのか、確認をしてみましょう。


B12にXLOOKUP関数をつかった数式を設定します。


=XLOOKUP(A12,$A$2:$A$9,$B$2:$F$9,"",0,1)


スピル機能によって、行方向には、数式をコピーする必要はありません。


列方向には、オートフィルで数式をコピーする必要があります。


これで、連続する複数項目の表引きを行うことができました。

COLUMN関数をつかわなくても大丈夫です。


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


=XLOOKUP(A12,$A$2:$A$9,$B$2:$F$9,"",0,1)


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


2つ目の引数は、検索範囲。

検索値のある列なので、$A$2:$A$9。

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


3つ目の引数は、戻り範囲。抽出したい列です。

連続する列なので、$B$2:$F$9。

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


ここをまとめて設定することができるので、VLOOKUP関数よりもわかりやすいかと思われます。


4つ目の引数は、見つからない場合。

見つからない場合は「””(ダブルコーテーション×2)」で空白とします。


5つ目の引数は、一致モード。

完全一致なので「0」を設定します。


最後の引数は、検索モード。

上から検索しますので、「1」と設定します。

7/06/2025

Excel。週末と祝日を除いた日数を求めるにはNETWORKDAYS.INTL関数【NETWORKDAYS_INTL】

Excel。週末と祝日を除いた日数を求めるにはNETWORKDAYS.INTL関数

<関数辞典:NETWORKDAYS.INTL関数>

NETWORKDAYS.INTL関数

読み方: ネットワークデイズ・インターナショナル  

分類: 日付時刻 

NETWORKDAYS.INTL関数

NETWORKDAYS.INTL(開始日,終了日,[週末],[祭日])

週末(曜日指定OK)と祝日を除いた日付間の日数を算出する

7/05/2025

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

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

<Facebookページ>

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

Facebookページ

6月8日

Excel。

範囲選択で、広い範囲の列を選択する場合は、最初の列番号をクリックして、最後の列番号のところで、Shiftキーを押しながらクリックすると選択できますね。



6月9日

Excel。

範囲選択で、広い範囲の行を選択する場合は、最初の行番号をクリックして、最後の行番号のところで、Shiftキーを押しながらクリックすると選択できますね。



6月10日

Excel。

シートの全部のセルを選択するのは、全選択ボタンをクリックします。

A列の左側と1行目の上の四角のボタンです。



6月11日

Excel。

足し算を使う記号は+。

引き算は-。

掛け算は×でなく*。

割り算は÷でなく/ですね。



6月12日

Excel。

2乗3乗のべき乗の記号は^(キャレット)ですね。



6月13日

Excel。

数学・算数のルールと同じで、()カッコの中が先に計算されます。



6月14日

Excel。

文字と文字も足す(合体)することもできます。

&を使うと、簡単にできますね。

7/04/2025

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

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

<VLOOKUP関数>

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

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

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を設定します。

7/03/2025

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

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

<関数辞典:NETWORKDAYS関数>

NETWORKDAYS関数

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

分類: 日付時刻 

NETWORKDAYS関数

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

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

7/02/2025

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

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

<Youtube>

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


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

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


なんで、どうして?


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


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

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


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

7/01/2025

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キーで削除したいところですが、よくみると、目盛線が表示されているので、作業ウインドウをつかって処理します。


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

ラベルの位置は「なし」

に変更します。


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