Excel。動画で紹介。桁数が異なっているデータをゼロで桁揃えするならTEXT関数をつかいます。【Alignment】

Excel。動画で紹介。桁数が異なっているデータをゼロで桁揃えするならTEXT関数をつかいます。

＜Youtube＞

1をABC0001

10をABC0010

というように、桁数が異なっているデータを、ゼロで桁を揃えたい場合、単純に、表示形式だけで対応することはできません。

そこで、TEXT関数をつかうことで、対応することができます。

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

Excel。データ内で、一番多く登場する数値である最頻値を求めたい【Mode】

Excel。データ内で、一番多く登場する数値である最頻値を求めたい

＜MODE.SNGL関数＞

大量のデータで、一番多く登場する数値である最頻値を求めたい場合、目視ではとても大変です。

そこで、MODE.SNGL関数をつかうことで、手早く、最頻値を求めることができます。

次のデータの最頻値を求めてみましょう。

B列の得点は、1-10の範囲としてます。

10件程度ならば、目視でも、対応できるかもしれませんが、多くなれば、目視は、ほぼ無理です。

そこで、最頻値を求める関数があります。

その関数は、MODE.SNGL関数です。

では、D2をクリックして、MODE.SNGL関数をつかって最頻値を求めてみます。

D2の数式は、

=MODE.SNGL(B2:B11)

4と、算出結果がでました。

目視で確認しても、間違いなく、4が一番多くあることがわかります。

とても簡単な関数ですが、最頻値は重要なデータの一つといえます。

最頻値は、

データの傾向を把握することができます。

異常値の検出にも役立ちます。

そのため、データ分析や統計において、とても重要な指標なので、簡単ですが、平均値だけではなく、最頻値も合わせて求めるといいですね。

ただし、このMODE.SNGL関数は、数値ならば、求めることができます。

文字の場合は求めることができませんので、注意が必要です。

Excelのショートカットキー。F6～F12のFunctionキーを紹介【shortcut】

Excelのショートカットキー。F6～F12のFunctionキーを紹介

＜Functionキー＞

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

なお、Excelのバージョンによって多少変わります。

F6

シート見出しをアクティブにする

F7

スペルをチェックする。

F8

拡張選択モードを有効する

F9

ブックのすべてのワークシートを再計算する

F10

リボンにキーボード操作できるキーを表示　Altキーと同じ

F11

グラフを作成 

F12

名前を付けて保存ダイアログボックスが表示されます

Excel。LINEST関数で重回帰直線の各係数を算出できます。【LINEST関数】

Excel。LINEST関数で重回帰直線の各係数を算出できます。

＜関数辞典：LINEST関数＞

LINEST関数

読み方： ラインエスティー　　

読み方： ラインエスティメーション

分類： 統計 

LINEST(既知のy,[既知のx],[定数],[補正])

重回帰直線の各係数を算出します

Excel。VBA。罫線を手っ取り早く消したいなら、この一行だけでOKです。【Border】

Excel。VBA。罫線を手っ取り早く消したいなら、この一行だけでOKです。

＜Excel VBA：Borders.LineStyleプロパティ＞

Excelの表に、罫線が設定されています。

この罫線を消して、使いたい場合、どのようにしたら、いいのでしょうか。

次の表を用意しました。

 

外枠が、太罫線で、横罫線が、破線と二重罫線で構成されています。

A1をクリックして、表全体を選択したら、ホームタブにある、罫線ボタンから、「枠なし」を選ぶだけですが、細かいことを色々やらないといけません。

そこで、この処理をExcel VBAのプログラム文で対応してみたらどうなるのか、確認してみましょう。

Sub 罫線一発解除()

    Range("A1").CurrentRegion.Borders.LineStyle = False

End Sub

たった、一行ですが、まずは実行して確認してみましょう。

線種にかかわらず、範囲選択内の罫線が解除されていることが確認できます。

大きな表や、範囲選択次第では、複雑な表などにもつかえます。

プログラム文としてもシンプルで、一行で対応することができました。

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

Range("A1").CurrentRegion.Borders.LineStyle = False

Range("A1").CurrentRegion で、A1を起点として連続するセルを範囲選択　という意味です。

Borders.LineStyle = False　ですが、Borders.LineStyleプロパティで罫線の種類や色を設定しています。

それが、「False」。

つまり「枠なし」という設定にすることができます。

ちなみに、「True」にすると、単純な「格子」を設定することができます。

Excel。動画で紹介。ゼロならば、±0（プラスマイナスゼロ）と表示する【ZERO】

Excel。動画で紹介。ゼロならば、±0（プラスマイナスゼロ）と表示する

＜Youtube＞

桁区切りの「,」である、桁区切りスタイルを設定すると、

正数は、桁区切りの「,」

負数は、桁区切りの「,」と文字の色が「赤」でマイナスがつくわけです。

では、ゼロのときは、「±0」とプラスマイナスの記号をつけて表示したいのですが、どのようにしたらいいのでしょうか。

そこで、表示形式をつかって解決する方法をご紹介しています。

Excel。LET関数は計算結果を名前に割り当てることができます。【LET】

Excel。LET関数は計算結果を名前に割り当てることができます。

＜関数辞典：LET関数＞

LET関数

読み方： レット　　

分類： 論理 

LET(名前1,名前値1,計算または名前2,[名前値2,…)

計算結果を名前に割り当てます。

Excel。FILTER関数でOR条件の抽出条件の設定方法を確認します。【conditions】

Excel。FILTER関数でOR条件の抽出条件の設定方法を確認します。

＜FILTER関数＞

FILTER関数をつかうことで、オートフィルターなどつかわなくても、該当するデータを抽出することが容易になりました。

次の表をつかって、FILTER関数のOR条件の設定方法を確認してみましょう。

抽出条件ですが、店舗名が本店と、地域は関西のデータを抽出したいとした場合、オートフィルターだけでは、対応することができません。

店舗名を本店で抽出した段階で、地域は、本店の関東のみしか表示されません。

つまり、地域の関西は抽出できないわけです。

そこで、オートフィルオプションをつかって、検索条件をつくり、それから抽出するわけで、面倒な工程を必要とします。

そのため、FILTER関数をつかうことで、「店舗名が本店と、地域は関西のデータを抽出したい」という高度な条件にも、手早く対応することができるというわけです。

F2にFILTER関数をつかった数式を設定しました。

 

F2に設定した数式は、

=FILTER(B2:D8,(C2:C8="関西")+(B2:B8="本店"),"")

FILTER関数は、スピル機能に対応した関数です。

設定するだけで、ゴーストが発生するので、オートフィルでの数式のコピーは不要です。

「店舗名が本店と、地域は関西のデータを抽出したい」

という抽出条件に合致していることが確認できます。

FITER関数だけで、高度な条件に対応したデータを抽出できたというわけです。

FILTER関数の引数を確認すると、

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

今回は、A列を除いても対応できることを確認したかったので、B2:D8 と設定しました。

2つ目の引数、「含む」が、今回のポイントです。

OR条件の場合には、「+」をつかって、「含む」を増やすことができます。

なお、AND条件は、「*（アスタリスク）」をつかいます。

よって、2つの条件なので、(C2:C8="関西")+(B2:B8="本店")　と設定しました。

3つ目の引数、「空の場合」は、条件に合致しないデータがあった場合ということなので、「””（ダブルコーテーション×2）」で空白としました。

オートフィルターで抽出条件が単純でない場合などには、FILTER関数をつかうといいかもしれませんね。

Excel。2025/1/5-1/11にCtrl＋F10などショートカットキーを紹介したFacebookページのコメントです。【comment】

Excel。2025/1/5-1/11にCtrl＋F10などショートカットキーを紹介したFacebookページのコメントです。

＜Facebookページ＞

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

1月5日

Excel。

Ctrl＋F4

ブックを閉じる　Ctrl+Wと同じ

1月6日

Excel。

Ctrl＋F5

ブックウィンドウが元のサイズになる

1月7日

Excel。

Ctrl＋F6

複数開いているブック間を移動する

1月8日

Excel。

Ctrl＋F7

最大化されていない時に、移動コマンドを実行する

1月9日

Excel。

Ctrl＋F8

最大化していない時に、サイズコマンドを実行する

1月10日

Excel。

Ctrl＋F9

開いているブックを最小化

1月11日

Excel。

Ctrl＋F10

開いているブックを最大化

Excel。文字列のバイト数を返すのがLENB関数です。【LENB】

Excel。文字列のバイト数を返すのがLENB関数です。

＜関数辞典：LENB関数＞

LENB関数

読み方： レンビー　　

分類： 文字列操作 

LENB(文字列)

文字列のバイト数を返す

※2025年1月　Microsoft365 Excel Insider版では、完全手入力でないとつかえない

Excel。バイト系関数が統廃合で消えた！完全手入力なら対応できそう。【Insider】

Excel。バイト系関数が統廃合で消えた！完全手入力なら対応できそう。

＜Insider版＞

LENB関数やLEFTB関数といった、文字数ではなくて、バイト数を求めることができる関数があります。

それらの関数は、

LENB・LEFTB・MIDB・SEARCHB・ FINDB・REPLACEB・RIGHTB関数

この関数が、Microsoft 365 Excel Insider版から消えました！

消えた理由は、

UnicodeサロゲートをサポートするためにLEN・LEFT・MID・SEARCH・ FIND・REPLACE・RIGHT関数を改良したことに伴い、統廃合されてようです。

絵文字とかが、2文字扱いになっていたのを、半角だろうが全角だろうが、1文字として扱うそうです。

直接入力でも、数式タブからでも、表示されません。

そして、今後は、Microsoft365 Excelにも普及しそうです。

計算方法に、バージョン1とバージョン2というのが用意されていて、Microsoftさんの発表だと、今後、新規シートは、バージョン2をデフォルトにするそうなので、普及は確実みたいです。

互換性関数にもないし…

では、DATEDIF関数みたいに、全くの手入力でいけるのではと思ってやってみたところ、どうにかつかえることが判明。

A1には、=LENB(B1)

A2には、=LENB(B2)

と入力したところ、全角・半角それぞれ、きちんと結果を求めることができました。

半角か全角かを判断する時につかうLENB関数が、DATEDIF関数のようになるなら、残してくれてもよかったのにと、思ったりもしますね。

今後消えないことを願うばかりです。

Excel。動画で紹介。グラフの凡例の文字が長すぎるので編集したい【Legend】

Excel。動画で紹介。グラフの凡例の文字が長すぎるので編集したい

＜Youtube＞

グラフの凡例は、グラフのもとになる、表の文字の長さに連動しています。

凡例の文字が長すぎて、わかりにくいので、編集するには、どのようにしたらいいのでしょうか。

もとの表の文字を編集をするわけにはいかないことが多いと思われます。

そこで、グラフの凡例だけを編集する方法をご紹介します。

Excel。LEN関数は、文字列の文字数を返す関数です。【LEN】

Excel。LEN関数は、文字列の文字数を返す関数です。

＜関数辞典：LEN関数＞

LEN関数

読み方： レン　　

分類： 文字列操作 

LEN(文字列)

文字列の文字数を返す

Excel。VLOOKUP関数で0（ゼロ）が表示される問題の解決方法【Shows 0】

Excel。VLOOKUP関数で0（ゼロ）が表示される問題の解決方法

＜VLOOKUP関数＞

表引きのVLOOKUP関数でのエラーといえば、#N/Aがあります。

この#N/Aエラーは、検索値がないので、発生するエラーです。

このエラーに対応するには、IF関数やIFERROR関数をつかうことで対応することができます。

この対応方法は、有名なのですが、VLOOKUP関数には、もう一つ問題があります。

それが、0（ゼロ）が表示されてしまう問題です。

次の表で0（ゼロ）が発生する状況を確認しましょう。

B2に設定した数式は、

=VLOOKUP(A2,$A$8:$B$11,2,FALSE)

オートフィルで数式をコピーした状態です。

すると、B4には0（ゼロ）と表示されていることがわかります。

数式を確認すると、

検索値は、A列で、範囲が、A8:B11なので、おかしなところはありません。

ただ、検索値が4の場合、表引き元になる範囲をみてみると、B11は、空白になっていることが確認できます。

0（ゼロ）が表示されてしまった原因は、表引き元の値が空白だと発生したということがわかります。

本来は、0（ゼロ）で表示されるのではなく、空白でないと困るわけですね。

どのようにしたら、いいのでしょうか。

エラーではないので、IF関数やIFERROR関数で対応するというわけにはいきません。

そこで、次のように数式を修正してみましょう。

=VLOOKUP(A2,$A$8:$B$11,2,FALSE)&””

修正した数式を、オートフィルでコピーしてみると、0（ゼロ）が表示されずに、空白になっています。

このように、表引きの結果、空白ではなくて、0（ゼロ）が表示されてしまって、対応したい場合には、「&””」と空白の文字結合をすることで、対応することができます。

なお、これは、VLOOKUP関数だから発生したのではなく、XLOOKUP関数でも同様に発生します。

その場合も「&””」で対応することができます。

Excel。LEFTB関数は、文字列の左端から指定バイト数の文字を返す関数です。【LEFTB】

Excel。LEFTB関数は、文字列の左端から指定バイト数の文字を返す関数です。

＜関数辞典：LEFTB関数＞

LEFTB関数

読み方： レフトビー　　

分類： 文字列操作 

LEFTB(文字列,[バイト数])

文字列の左端から指定バイト数の文字を返す

※LEFTB関数は、2025年1月。Insider版更新で、LEFT関数の仕様変更に伴い、統合されて削除されました。今後、Microsoft 365版も同じように削除される可能性があります。

Excel。動画で紹介。非表示に対応した連番を1から自動的に再設定したい。【Hidden】

Excel。動画で紹介。非表示に対応した連番を1から自動的に再設定したい。

＜Youtube＞

オートフィルターで、抽出すると、連番などの通し番号も、非表示によって、飛び飛びの番号になってしまいます。

非表示にしても、連番を1から自動的にふりなおすには、どのようにしたらいいのでしょうか。

非表示対応するには、「AGGREGATE関数」をつかってみる方法があります。

そこで、今回は、AGGREGATE関数をつかった、非表示対応の連番の設定方法を動画で、ご紹介しております。

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。文字列の左端から文字を取り出すのがLEFT関数です。【LEFT】

Excel。文字列の左端から文字を取り出すのがLEFT関数です。

＜関数辞典：LEFT関数＞

LEFT関数

読み方： レフト　　

分類： 文字列操作 

LEFT(文字列,[文字数])

文字列の左端から文字を取り出す

Excel。2024/12/29-1/4にCtrl＋F1などショートカットキーを紹介したFacebookページのコメントです。【comment】

Excel。2024/12/29-1/4にCtrl＋F1などショートカットキーを紹介したFacebookページのコメントです。

＜Facebookページ＞

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

12月29日

Excel。

Ctrl＋~

表示形式を外します。[全般] 数値形式にします。（チルダ）

12月30日

Excel。

Ctrl＋＾

指数表示にする。（キャレット）

12月31日

Excel。

Ctrl＋￥

選択した行内で、アクティブセルと合致しないセルを選択

1月1日

Excel。

Ctrl＋｜

選択した列内で、アクティブセルと合致しないセルを選択

1月2日

Excel。

Ctrl＋F1

リボンの表示非表示

1月3日

Excel。

Ctrl＋F2

印刷プレビューを表示　Ctrl+Pと同じ

1月4日

Excel。

Ctrl＋F3

名前の管理ダイアログボックスが表示されます

Access。クエリ。結合して新しいコードをつくりたい。なお、０付きで桁を揃えたい。【combining】

Access。クエリ。結合して新しいコードをつくりたい。なお、０付きで桁を揃えたい。

＜&とFormat関数＞

次のテーブルがあります。

カテゴリーとコードを組み合わせた、新しいコードをつくりたい。

そして、コードは、0付きで桁を揃えたいわけです。

例えば、カテゴリーが「A」でコードが「1」の場合、新しいコードは、「A001」としたいわけです。

クエリの演算フィールドで新コードをつくっていきましょう。

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

テーブルのフィールドはすべてつかいます。

新コード: [カテゴリー] & Format([コード],"000")

と設定します。

実行して確認してみましょう。

新コードをつくることができました。

では、演算フィールドを確認しておきましょう。

新コード: [カテゴリー] & Format([コード],"000")

カテゴリーのフィールドとコードのフィールドを結合するのには「&（アンパサンド）」で行います。

単に結合しているのではなく、コードの桁を０付きでおこないますので、Format関数をつかって、0付きの「”000”」で揃えます。

このようFormat関数をつかうことで、0付きで桁を揃えることができます。