Excel。整数の最小公倍数を求めることができるのが、LCM関数です。【LCM】

Excel。整数の最小公倍数を求めることができるのが、LCM関数です。

＜関数辞典：LCM関数＞

LCM関数

読み方： エルシーエム　　

読み方： リースト・コモン・マルチプル

分類： 数学/三角 

LCM(数値1,[数値2],…)

整数の最小公倍数を算出します 

Excel.動画で紹介。手早く2行1組で塗り分けるには、どうしたらいいの。【2rows,1set】

Excel.動画で紹介。手早く2行1組で塗り分けるには、どうしたらいいの。

＜Youtube＞

2行1組で、表を塗り分けたい場合、どのようにしたら、手早く設定することができるのでしょうか。

条件付き書式とMOD関数とROW関数を組み合わせることで、対応することができます。

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

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい【All blank】

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい

＜条件付き書式＋CONCAT関数＞

データの全てが空白の場合、わかりやすいように、そのデータ全体を塗りつぶして確認できるようにしたいのですが、どのようにしたら、いいのでしょうか。

今回はどのような表を用意しました。

1回目から5回目のデータすべてが空白だった、そのデータ全体、つまり行全体を塗りつぶしたいわけです。

条件で塗りつぶすということから、条件付き書式をつかうわけです。

そこで、問題になるのが、条件式です。

AND条件で、セルひとつずつが、空白かどうかを確認するのは、大変です。

また、COUNTBLANK関数で、空白の件数を求めて、全体の件数と空白の件数が合致するのかを確認するというのも、面倒です。

そこで、文字結合のCONCAT関数をつかうことで、楽に条件式を設定することができます。

A2:F7を範囲選択します。

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

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

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

=concat($B2:$F2)=""

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックして、完成です。

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

=concat($B2:$F2)=""

ポイントは、引数です。

$B2:$F2　の列固定した複合参照にすることで、行全体を対象にすることができます。

CONCAT関数は、セル結合なので、結合した結果、空白ならばという条件式をつくればいいわけです。

なお、行固定の複合参照にすれば、列を対象に塗りつぶすことができます。

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

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

＜Facebookページ＞

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

12月22日

Excel。

Ctrl＋}

数式で直接参照している数式が入力されたすべてのセルを選択します。

12月23日

Excel。

Ctrl＋!

マイナスの数値を赤色にしない表示形式にする

12月24日

Excel。

Ctrl＋"

アクティブなセルの 1 つ上のセルの値をコピーします。

12月25日

Excel。

Ctrl＋#

日付形式を設定する

12月26日

Excel。

Ctrl＋$

通貨スタイル (￥)を設定する

12月27日

Excel。

Ctrl＋%

セルの値を % 表示にする

12月28日

Excel。

Ctrl＋'

セルの値と数式の表示を切り替える（シングルコーテーション）

Excel。LARGE関数は指定した○番目に大きい値を算出します【LARGE】

Excel。LARGE関数は指定した○番目に大きい値を算出します

＜関数辞典：LARGE関数＞

LARGE関数

読み方： ラージ　　

分類： 統計 

LARGE(配列,順位)

指定した○番目に大きい値を算出します

Excel。VBA。他からコピーした表に「ふりがな」を設定したい【vbHiragana】

Excel。VBA。他からコピーした表に「ふりがな」を設定したい

＜Excel VBA: vbHiragana＞

Excelのふりがな情報は、直接入力した場合でないと、基本設定されていません。

Wordにある表を、Excelにコピーしてみます。

なお、Wordでは、直接入力しております。

C列にふりがな情報を表示したいので、PHONETIC関数をつかってみます。

C2には、

=PHONETIC(B2)

と設定しました。

オートフィルで数式をコピーしましたが、ふりがな情報がないので、そのまま文字情報が表示されています。

これでは、C列のふりがなをつかって、並べ替えをすることができません。

今回のように件数が少なければ、ふりがなの列に直接ふりがなを入力して対応することもできますが、件数が多くなれば、対応することが難しくなります。

そこで、Excel VBAをつかって、ふりがな情報を追加することができます。

Sub ふりがな()

    Dim i As Integer

    Dim lastrow As Long

    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    Range("b1").CurrentRegion.Offset(1, 0).SetPhonetic

    For i = 2 To lastrow

        Range("c" & i) = Range("b" & i).Phonetic.Text

        Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

    Next

End Sub

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

B2には、ふりがな情報を追加されたことが確認できました。

そして、C列には、そのふりがな情報をつかって、ふりがなを表示しております。

しかも、「ひらがな」でです。

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

lastrow = Cells(Rows.Count, "a").End(xlUp).Row

変数のlastrowは繰り返し作業のために求めています。

Range("b1").CurrentRegion.Offset(1, 0).SetPhonetic

この行で、B1からのデータにフリガナを設定しています。

コピーしたデータでもこれで、ふりがな情報を追加することができました。

なお、Excelで、こういう読みかなという、ふりがななので、異なっている場合は修正が必要です。。

For i = 2 To lastrow

    Range("c" & i) = Range("b" & i).Phonetic.Text

    Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

Next

For To Nextで繰り返し処理をしております。

Range("c" & i) = Range("b" & i).Phonetic.Text

C列にふりがな情報を表示します。

これだけだと、「カタカナ」になっています。

見出し行が「ひらがな」なので、カタカナをひらがなに変換しているのが、次の行です。

Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

Excelそのものには、カタカナをひらがなに変換する関数はありませんが、Excel VBAならば、vbHiragana　をつかうことで、対応することができます。

Excel。動画で紹介。@から入力できない？この解決方法をご紹介します。【domain】

Excel。動画で紹介。@から入力できない？この解決方法をご紹介します。

＜Youtube＞

Excelで、ドメインを@（アットマーク）から入力しようとしたら、エラーが表示されます。しかも、関数とかっていわれちゃう。

関数なんか入力した覚えなんてないのに…

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

Excel。LAMBDA関数は、関数を作成し、それらを表示名で呼び出せます【LAMBDA】

Excel。LAMBDA関数は、関数を作成し、それらを表示名で呼び出せます

＜関数辞典：LAMBDA関数＞

LAMBDA関数

読み方： ラムダ　　

分類： 論理 

LAMBDA(パラメータまたは計算,…)

カスタムの再利用可能な関数を作成し、それらを表示名で呼び出します。

Excel。オートフィルターの抽出でOR条件はどうしたらいいの【extraction】

Excel。オートフィルターの抽出でOR条件はどうしたらいいの

＜オートフィルターオプション＞

抽出するときにオートフィルターをつかうことが多いのですが、「～または～」というOR条件で、抽出したい時には、どのようにしたら、いいのでしょうか。

次の表をつかって、説明します。

今回は、商品名が「消しゴム」　または　売上金額が「2000以上」に該当するデータを抽出したいとします。

商品名が「消しゴム」を抽出したのですが、消しゴムのみしか抽出できません。

このまま売上金額が2000以上としても、抽出したい条件ではありません。

商品名が「消しゴム」　でかつ　売上金額が「2000以上」というAND条件ならば、対応することができますが、オートフィルターはOR条件での抽出には対応しておりません。

そこで、オートフィルターオプションをつかって対応します。

オートフィルターオプションをつかうには、準備が必要です。

まず、抽出条件をつくります。

見出しをコピーします。

条件行で設定すれば、AND条件に、条件列内で設定すれば、OR条件という仕組みになっています。

これで、商品名が「消しゴム」　または　売上金額が「2000以上」というOR条件を設定することができます。

あとは、データ内をクリックして、データタブにある「詳細設定」をクリックします。

フィルターオプションの設定ダイアログボックスが表示されます

検索条件範囲に「$A$1:$B$3」と先程、作成した条件を範囲選択します。

あとは、OKボタンをクリックします。

オートフィルターで、商品名が「消しゴム」　または　売上金額が「2000以上」というOR条件での抽出ができました。

Excel。KURT関数は、データセットの尖度（せんど）を算出します【KURT】

Excel。KURT関数は、データセットの尖度（せんど）を算出します

＜関数辞典：KURT関数＞

KURT関数

読み方： カート　　

分類： 統計 

KURT(数値1,[数値2],…)

データセットの尖度（せんど）を算出します　

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

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

＜Facebookページ＞

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

12月15日

Excel。

Ctrl＋.

範囲選択時、範囲選択内の四隅を順番に移動する

12月16日

Excel。

Ctrl＋>

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

12月17日

Excel。

Ctrl＋>

テキストボックス内：フォントサイズを拡大

12月18日

Excel。

Ctrl＋<

上のセルを下にコピーする

12月19日

Excel。

Ctrl＋<

テキストボックス内：フォントサイズを縮小

12月20日

Excel。

Ctrl＋@

表示形式で時刻に変更します。

12月21日

Excel。

Ctrl＋{

数式で直接参照しているセルをすべて選択します。参照元

Excel。前回以上の良い数値の件数を手早く求めたい。【last time】

Excel。前回以上の良い数値の件数を手早く求めたい。

＜SUMPRODUCT関数＞

ある競技の結果表があります。

1回目よりも2回目の成績がいい件数は何件あるのかを求めたい。

このような場合、D列とかに、IF関数をつかって、2回目の値が大きければ、○とかを表示させて、その結果を数えるという方法で、求めたりします。

それでもいいのですが、SUMPRODUCT関数だけで、求めることができます。

では、C9にSUMPRODUCT関数をつかった数式を設定します。

C9に設定した数式は、

=SUMPRODUCT((B2:B7<C2:C7)*1)

これだけで、3件と算出することができました。

SUMPRODUCT関数は、「総和」を求めることができるSUM関数と、乗算のPRODUCT関数が合わさった関数です。

行ごとに、B2:B7<C2:C7の条件が成立しているならば、TRUE。

成立していなければ、FALSEと算出されます。

TRUEとFALSEでは、合算することができません。

Excelでは、TRUEが1で、FALSEが0と定義されています。

そこで「×1」することで、数値化することができます。

TRUEは1となります。

この値を合算することで、2回目の方が大きい件数を求めることができるという仕組みです。

Excel。動画で紹介。条件が一つだけの合計ならば、SUMIF関数をつかいます。【TOTAL】

Excel。動画で紹介。条件が一つだけの合計ならば、SUMIF関数をつかいます。

＜Youtube＞

単純な合計は、オートSUMボタンの合計のSUM関数で算出できます。

ただ、条件が付いてくるとなると、SUM関数では対応できません。

条件が一つだけ（単一条件）の合計ならば、SUMIF関数をつかうことで、手早く算出することができます。

基本的な関数ですので、改めて確認していきます。

Excel。半角文字を全角に変換するのがJIS関数です。【JIS】

Excel。半角文字を全角に変換するのがJIS関数です。

＜関数辞典：JIS関数＞

JIS関数

読み方： ジス　　

分類： 文字列操作 

JIS(文字列)

半角文字を全角に変換する

Excel。文字列の一部が該当したら行全体をぬりつぶしたい【Fill】

Excel。文字列の一部が該当したら行全体をぬりつぶしたい

＜条件付き書式+FIND関数＞

B列の住所。

その中で、横浜市ならば、行全体を塗りつぶしたいのですが、どのようにしたらいいのでしょうか。

横浜市ならば、塗りつぶすということは、条件付き書式をつかって対応します。

あと、問題になってくるのが、条件式です。

どのような条件式を作ればいいのでしょうか。

横浜市ならばということですから、横浜市をどのように確認させるかということになります。

ただ、住所の列は、横浜市　だけではなく、都道府県を含め、市区町村などもすべて含まれているデータです。

横浜市　という条件。

つまり完全一致のデータではなく、部分一致の条件式をどのように作るのかというのが、ポイントになるわけです。

さて、部分一致ということならば、「ワイルドカード」をつかいたくなりますが、今回は、文字を検索する「FIND関数」だけをつかって、対応していきます。

A2:B6を範囲選択します。

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

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

「数式を使用して、書式設定するセルを決定」をクリックして、条件式を設定します。

=FIND("横浜市",$B2)

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

OKボタンをクリックして、完成です。

このように、横浜市が含まれている行全体を塗りつぶすことができました。

では、条件式を確認しておきます。

=FIND("横浜市",$B2)

FIND関数は、セル内に、最初の引数で設定した文字が最初に出てくる文字数を返す。

すなわち、含まれているかを確認することができる関数です。

最初の引数は、「横浜市」と設定します。

2つ目の引数で、検索対象のセルを設定します。

また、列固定の複合参照にすることで、行全体を対象にすることができます。

行全体を塗りつぶすことができるというわけです。

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

Excelのショートカットキー。F1～F5のFunctionキーを紹介

＜Functionキー＞

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

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

F1

ヘルプを表示します。

F2

アクティブなセルを編集する

F3

[名前の 貼り付け ] ダイアログ ボックスを表示

F4

直前の動作を繰り返す

数式作成時は絶対参照・複合参照を設定

F5

ジャンプダイアログボックスが表示されます

Excel。動画で紹介。天気予報でお馴染みの「マーカー内ラベル折れ線グラフ」の作り方【line graph】

Excel。動画で紹介。天気予報でお馴染みの「マーカー内ラベル折れ線グラフ」の作り方

＜Youtube＞

天気予報で、一日の温度の推移などでつかっている、「マーカー内ラベル折れ線グラフ」。

マーカー内ラベル折れ線グラフをつくるには、どのようにしたらいいのでしょうか。

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

Excel。ピボットテーブルをつかえば、手早く順位も求めることができます。【Ranking】

Excel。ピボットテーブルをつかえば、手早く順位も求めることができます。

＜ピボットテーブル＞

大きな表などを集計するには、ピボットテーブルをつかうことで、手早く集計することができます。

そのピボットテーブルで、順位も合わせて、手早く求めることができるようになっています。

元になるデータです。

では、表内をクリックして、挿入タブのピボットテーブルをクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されます。

ダイアログボックスは、そのままOKボタンをクリックします。

新しいシートが追加され、右側には、ピボットテーブルのフィールド作業ウィンドウが表示されます。

行のレイアウトボックスには、「商品名」を設定します。

値のレイアウトボックスには、「売上金額」を2つ設定します。

「合計/売上金額2」のC4をクリックします。

ピボットテーブル分析タブのフィールドの設定をクリックします。

値フィールドダイアログボックスが表示されます。

名前の設定　を　「順位」　に変更します。

計算の種類タブ　に変更します。

計算の種類を「降順での順位」にします。

あとは、OKボタンをクリックします。

これで、手早く集計と順位を求めることができました。

ピボットテーブルは集計以外でも様々な計算をおこなうことができます。

Excel。ISTEXT関数は対象が文字列の場合にTRUEを返します。【ISTEXT】

Excel。ISTEXT関数は対象が文字列の場合にTRUEを返します。

＜関数辞典：ISTEXT関数＞

ISTEXT関数

読み方： イズテキスト　　

分類： 情報 

ISTEXT(テストの対象)

対象が文字列の場合にTRUEを返す

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

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

＜Facebookページ＞

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

12月8日

Excel。

Ctrl＋9

選択した行を非表示にする。

12月9日

Excel。

Ctrl＋0

選択した列を非表示にする。

12月10日

Excel。

Ctrl＋;

今日の日付を入力する（セミコロン）

12月11日

Excel。

Ctrl＋:

現在時刻を入力する(コロン)

12月12日

Excel。

Ctrl＋+

セルの挿入ダイアログ ボックスを表示する

12月13日

Excel。

Ctrl＋-

セルの削除ダイアログ ボックスを表示する

12月14日

Excel。

Ctrl＋*

セルの周囲の現在の選択範囲を選択

Access。クエリ。クロス集計クエリをウィザードで作ってみよう【Cross Tabulation】

Access。クエリ。クロス集計クエリをウィザードで作ってみよう

＜クロス集計クエリ＞

大きなデータを取り扱うことがあるAccess。

集計するのに、Excelのピボットテーブルのような集計をする場合に、クロス集計クエリというクエリがあります。

クエリデザインでつくってもいいのですが、ウィザードが用意されていますので、今回は、クロス集計クエリウィザードをつかって、クロス集計を作ってみようと思います。

用意したテーブルです。

テーブルは閉じておきます。

作成タブの「クエリウィザード」をクリックします。

 

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

クロス集計クエリウィザードを選択して、OKボタンをクリックします。

「Microsoft Accessのセキュリティに関する通知」ダイアログボックスが表示された場合は、開くボタンをクリックします。

クロス集計クエリウィザードが表示されます。

STEP1は、クロス集計クエリの元になるテーブルやクエリを選択します。

選択したら、次へボタンをクリックします。

STEP2は、行見出しにするフィールドを選択します。今回は日付フィールドにします。

設定が終わりましたら、次へボタンをクリックします。

STEP3は、列見出しにするフィールドを選択します。商品名を使うことにします。

設定が終わったら、次へボタンをクリックします。

STEP4は、集計する値です。今回は、個数の合計数を求めることにしますので、個数フィールドを選択し、集計方法を「合計」で設定しました。

なお、集計値を表示するに☑をいれると、データの合計値のフィールドを用意することができます。合計値が不要な場合は、チェックをはずします。

設定が終わりましたら、次へボタンをクリックします。

STEP5は、クエリ名を設定したら、完了ボタンをクリックします。

これでクロス集計クエリが完成しました。

左から2列目の「合計 個数」フィールドが、ウィザードのSTEP4であった、集計値を表示するに☑を入れた場合、表示されるフィールドです。

このフィールドが不要でしたら、チェックを外すという仕組みです。

このように、ウィザードをつくると手早く、クロス集計クエリをつくることができます。

Excel。動画で紹介。同じデータが繰り返すなら「〃」という文字に置換したい。【same】

Excel。動画で紹介。同じデータが繰り返すなら「〃」という文字に置換したい。

＜Youtube＞

同じデータが繰り返すので、繰り返すのがわかるように「〃」という文字に置換したい。

このリクエストを叶えるには、IF関数とCOUNTIF関数を組み合わせて数式をつくることで、対応することができます。

ちなみに、「〃」は、「おなじ」という読みで変換できます。

ただし、レコードからみると、このような置換はオススメできませんが、このような方法を使えば、できるというわけです。

Excel。ISREF関数で、対象がセル参照の場合かどうかわかります【ISREF】

Excel。ISREF関数で、対象がセル参照の場合かどうかわかります

＜関数辞典：ISREF関数＞

ISREF関数

読み方： イズリファレンス　　

分類： 情報 

ISREF(テストの対象)

対象がセル参照の場合にTRUEを返す

Excel。文字列の最頻値は、どのようにして求めたらいいのでしょうか。【Mode】

Excel。文字列の最頻値は、どのようにして求めたらいいのでしょうか。

＜UNIQUE関数,SORT関数,COUNTIF関数＞

分析の一つのデータに「最頻値」というのがあります。

最頻値は、データ内で一番多くあるデータです。

その最頻値を求めるには、MODE.SNGL関数というのが用意されています。

では、次のデータで、一番多い都道府県名を求めてみましょう。

 D2に設定した数式は、

=MODE.SNGL(B2:B16)

ところが、#N/Aというエラーが表示されてしまいました。

原因は、どこにあるのでしょうか。

MODE.SNGL関数の引数を確認してみると、原因がわかります。

数値1・数値2…となっています。

つまりMODE.SNGL関数は数値ならば、最頻値を求めることができるのですが、都道府県名という文字列では、最頻値を求めることができません。

ピボットテーブルをつかうのもいいのですが、今回は関数で対応していきます。

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

=UNIQUE(B2:B16)

UNIQUE関数は、データから重複を除いた、一意のデータを抽出することができる関数です。

まずは、重複を除いた一意のデータを抽出します。

UNIQUE関数は、スピル機能対応の関数なので、D3以降は、ゴーストが発生します。

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

続いて、件数を求めます。

単一条件の件数を求めるので、COUNTIF関数をつかいます。

E2をクリックします。

E2に設定する数式は、

=COUNTIF(B2:B16,D2#)

スピル機能対応の設定にしましたので、ゴーストが発生します。

引数の最初の範囲は、B2:B16

2つ目の引数は、検索条件。

D2#とします。

「#」は、範囲内の1件という意味です。

これで、最頻値を求めることができました。

ただ、できることならば、件数が多い順にしたいわけですが、一つの表ではないので、件数を降順にするわけにはいきません。

そこで、SORT関数をつかって、新たに表を作ります。

=SORT(D2:E7,2,-1,FALSE)

これで、件数が降順のリストをつくることができました。

最頻値は、千葉県であることが、よりわかりやすくなりました。

このSORT関数もスピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。

引数も確認しておきましょう。

最初の引数は、配列。D2:E7を設定します。

2つ目の引数は、「並べ替えインデックス」。

どの列で並べ替えるのかということなので、2列目ですから、2と設定します。

3つ目の引数は、「並べ替え順序」。

降順にしたいので、「-1」で設定します。

4つ目の引数は、「並べ替え基準」。

行で並べ替えをしますので、FALSEで設定します。

新しく追加された関数を組み合わせてつかうことで、文字列の最頻値を求めることができました。

今回は関数という条件をつけましたが、ピボットテーブルをつかえば、もっと手早く求めることができます。

Excel。元利均等返済における指定期間の利息を算出できるのが、ISPMT関数です。【ISPMT】

Excel。元利均等返済における指定期間の利息を算出できるのが、ISPMT関数です。

＜関数辞典：ISPMT関数＞

ISPMT関数

読み方： アイエスピーエムティー　　

読み方： イズ・ペイメント

分類： 財務 

ISPMT(利率,期,期間,現在価値)

元利均等返済における指定期間の利息を算出します。