Excel。2024/2/4-2/10にINDIRECT関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/2/4-2/10にINDIRECT関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

2月4日

Excel。

IMSUB関数

読み方： アイエムサブ　　

読み方： イマジナリーサブトラクト

分類： エンジニアリング 

IMSUB(複素数1,複素数2)

複素数の差を算出する　

2月5日

Excel。

IMSUM関数

読み方： アイエムサム　　

読み方： イマジナリーサム

分類： エンジニアリング 

IMSUM(複素数1,[複素数2],…)

複素数の和を算出する　

2月6日

Excel。

IMTAN関数

読み方： アイエムタンジェント　　

読み方： イマジナリータンジェント

分類： エンジニアリング 

IMTAN(複素数)

複素数のタンジェントを算出する　

2月7日

Excel。

INDEX関数

読み方： インデックス　　

分類： 検索/行列 

INDEX(配列,行番号,[列番号])

セル範囲から縦横座標で値を抽出します　配列形式

2月8日

Excel。

INDEX関数

読み方： インデックス　　

分類： 検索/行列 

INDEX(参照,行番号,[列番号],[領域番号])

セル範囲から縦横座標で値を抽出します　セル範囲形式

2月9日

Excel。

INDIRECT関数

読み方： インダイレクト　　

分類： 検索/行列 

INDIRECT(参照文字列,[参照形式])

文字列で参照されるセルの値を算出します　

2月10日

Excel。

INFO関数

読み方： インフォ　　

読み方： インフォメーション

分類： 情報 

INFO(検査の種類)

Excelの動作環境に関する情報を返す　

Excel。VBA。表の見出しや小計・総計は残して、数値のデータだけを削除したい【Delete only numbers】

Excel。VBA。表の見出しや小計・総計は残して、数値のデータだけを削除したい

＜SpecialCellsメソッド＞

見出し行や集計行などは、残してデータの数値だけを削除したい。

範囲選択をして削除するだけですが、範囲選択が面倒になってきます。

B2:D4とB6:D8のデータを削除したいわけです。

そこで、Excel VBAでプログラム文をつくってみることにしました。

Sub 数値のみ削除()

Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

End Sub

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

B2:D4とB6:D8のデータだけを削除することができました。

数式や見出し行は残すことができました。

それでは、プログラム文を確認します。

Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

Range("a1").CurrentRegion　で、A1を起点とした連続したデータを範囲選択します。

SpecialCellsメソッドを使用すると、指定した条件に該当するセルを取得することができます。

xlCellTypeConstantsは、定数が含まれているセルという意味です。

さらに、xlNumbersというValueの設定値は、”数値”を意味します。

つまり、『数値の入っているセル』という意味になります。

よって、A1を起点とした連続する、数値が入力されているセルを範囲選択して、削除（ClearContents）するという処理ができるというわけです。

たった一行なので、用意していてもいいかもしれませんね。

Excel。複数のセル内の文字を指定した文字で、手早く連結したい【connect letters】

Excel。複数のセル内の文字を指定した文字で、手早く連結したい

＜TEXTJOIN関数＞

クレジットカードやIPアドレスなど、例えば、4桁ごとにわかれて入力されているとします。

そのわかれているセルとセルを「-（ハイフン）」で、手早く連結するには、どのようにしたらいいのでしょうか。

次の表で説明します。

 

A1:D5に4桁の数値が入力されている表があります。

A2:D2までの文字列を「-（ハイフン）」で連結したのが、F2です。

文字結合の「&（アンパサンド）」だと、セルとセルの間に「&”-“&」を入力して連結させる必要があります。

そうなると、連結するセルが増えれば増えるほど、面倒になります。

そこで、「TEXTJOIN関数」をつかうことで、手早く連結することができます。

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

=TEXTJOIN("-",TRUE,A2:D2)

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

これだけで、「-（ハイフン）」で連結した文字列をつくることができます。

=TEXTJOIN("-",TRUE,A2:D2)　の数式を確認していきます。

最初の引数は、「区切り文字」。

連結したい文字を設定します。今回は「-（ハイフン）」で連結します。

2つ目の引数は、「空のセルは無視」。

選択したセルに文字が入力されていない場合、無視するならば、TRUE。

無視しないならば、FALSEを設定します。無視しますので、TRUEを設定しました。

3つ目の引数は、テキスト1。

連結したいセルまたは、範囲です。

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

Excel。文字を統一する関数。全角のJIS関数と半角のASC関数を紹介【character】

Excel。文字を統一する関数。全角のJIS関数と半角のASC関数を紹介

＜JIS関数・ASC関数＞

データベースにおいて、文字の揺らぎなどが原因で、抽出などに影響がでます。

データは半角で統一されているのか、全角で統一されているのか、確認修正するために、全角のJIS関数と半角のASC関数があります。

そのJIS関数とASC関数を紹介しております。

Excel。複数条件を指定して、手早く別表に抽出したい【extraction】

Excel。複数条件を指定して、手早く別表に抽出したい

＜FILTER関数＞

オートフィルターをつかうことで、データを抽出することは、簡単にできます。

しかし、抽出したデータを、別の場所にコピーして貼り付けるとなると、単純な作業ですが、面倒です。

そこで、FILTER関数をつかうことで、抽出し、別の場所に抽出結果を表示することができます。

抽出条件をF1:G2に用意しました。

店舗名が新宿店で、商品名が消しゴムのデータを抽出して、F5を起点に結果を表示していきます。

F5に設定する数式は、

=FILTER(A2:D11,(B2:B11=G1)*(C2:C11=G2),"該当データなし")

これだけで、抽出して、さらに、別なところに貼り付けることができます。

FILTER関数の最初の引数は、「配列」です。

つまりデータのことなので、A2:D11を設定します。

2番目の引数は、「含む」。

条件です。

FILTER関数で複数条件を作る時には、「*（アスタリスク）」で接続させていきます。

(B2:B11=G1)*(C2:C11=G2)と設定します。

また、スピル機能によって数式の範囲が広がりますので、絶対参照にする必要はありません。

3番目の引数は、「空の場合」。

これは、抽出するデータがなかった時、どうしますかということなので、「該当データなし」と表示するようにしました。

Excelのショートカットキー。Ctrl＋Shiftキーとジャンプ系キーを紹介【shortcut】

Excelのショートカットキー。Ctrl＋Shiftキーとジャンプキー系を紹介

＜Ctrl+Shiftキー＋ジャンプ系キー＞

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

Ctrl＋Shift＋Home

選択範囲をワークシートの先頭のセルまで拡張する。

Ctrl＋Shift＋End

選択範囲を最後のセル (右下隅) まで拡張する。

Ctrl＋Shift＋Space

全選択。Ctrl+Aと同じ

Ctrl＋Shift＋Space

図形などオブジェクトを選択時すべてのオブジェクトを選択する

Ctrl＋Shift＋Tab

ブックを切り替える

Ctrl＋Shift＋Page Up

現在のシートと前のシートを選択する。

Ctrl＋Shift＋Page Down

現在のシートと次のシートを選択する。

Excel。ガンマ累積分布関数の逆関数の値を算出できるのが、GAMMAINV関数です。【GAMMAINV】

Excel。ガンマ累積分布関数の逆関数の値を算出できるのが、GAMMAINV関数です。

＜関数辞典：GAMMAINV関数＞

GAMMAINV関数

読み方： ガンマインバース　　

分類： 互換性 

GAMMAINV(確率,α,β)

ガンマ累積分布関数の逆関数の値を算出する

Access。クエリ。独自の順番でデータを並べ替えるにはどうしたらいいの。【SORT】

Access。クエリ。独自の順番でデータを並べ替えるにはどうしたらいいの。

＜クエリ:並べ替え＞

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

店舗名を、新宿・品川・渋谷の順番で並べ替えたいとします。

店舗名を昇順にしても、降順にしても、この順番で並べ替えることはできません。

また、Excelのユーザー設定リストのようなものは、Accessには用意されていません。

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

それでは、対応方法を紹介していきます。

最初に、Excelのユーザー設定リストのような並べ替えたい順番のテーブルを用意します。

作成タブのクエリデザインをつかって、クエリを作成していきます。

並べ替えを行いたいテーブルと並べ替えの順番となるテーブルを用意します。

両テーブル間にリレーションシップを設定します。店舗どうしで設定します。

フィールドを設定します。

今回は、並べ替えしたいテーブルのフィールドをすべて使用します。

最後尾に、並べ替えの順番になるテーブルから、「店舗NO」を追加します。

追加したフィールドは、あくまでも、並べ替えを行うためのものなので、非表示にします。

実行して確認します。

店舗名が、新宿・品川・渋谷の順番で並んでいることが確認できます。

このように、Excelのユーザー設定リストに該当するテーブルを用意することで、独自の順番で並べ替えをすることができます。

ところで、店舗NOで並べ替えを行った影響で、販売日が、降順の状態になっています。

折角なので、販売日を昇順にしておきましょう。

店舗NOに昇順の並べ替えを設定します。

並べ替え用に新たに、販売日を追加して、昇順に設定・非表示にします。

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

並べ替えを複雑にしたクエリもつくることができます。

Excel。2024/1/28-2/3にIMPRODUCT関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/1/27-2/3にIMPRODUCT関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

1月28日

Excel。

IMPRODUCT関数

読み方： アイエムプロダクト　　

読み方： イマジナリープロダクト

分類： エンジニアリング 

IMPRODUCT(複素数1,[複素数2],…)

複素数の積を算出する　

1月29日

Excel。

IMREAL関数

読み方： アイエムリアル　　

読み方： イマジナリーリアル

分類： エンジニアリング 

IMREAL(複素数)

複素数の実数部分を取り出す　

1月30日

Excel。

IMSEC関数

読み方： アイエムセカント　　

読み方： イマジナリーセカント

分類： エンジニアリング 

IMSEC(複素数)

複素数のセカント（正割）を算出する　

1月31日

Excel。

IMSECH関数

読み方： アイエムセカントハイパーポリック　　

読み方： イマジナリーハイパーポリックセカント

分類： エンジニアリング 

IMSECH(複素数)

複素数の双曲線正割を算出する　

2月1日

Excel。

IMSIN関数

読み方： アイエムサイン　　

読み方： イマジナリーサイン

分類： エンジニアリング 

IMSIN(複素数)

複素数のサイン（正弦）を算出する　

2月2日

Excel。

IMSINH関数

読み方： アイエムサインハイパーポリック　　

読み方： イマジナリーハイパーポリックサイン

分類： エンジニアリング 

IMSINH(複素数)

複素数の双曲線正弦（ハイパーポリックサイン）を算出する　

2月3日

Excel。

IMSQRT関数

読み方： アイエムスクエアルート　　

読み方： イマジナリースクエアルート

分類： エンジニアリング 

IMSQRT(複素数)

複素数の平方根を算出する

Excel。年月日を漢数字にした和暦で日付を表示する方法とは【Display format】

Excel。年月日を漢数字にした和暦で日付を表示する方法とは

＜表示形式＞

Excelで、日付を和暦に変更するには、表示形式の日付から、和暦を選択することで、令和5年10月15日のように表示することができます。

では、令和五年十月十五日というように、数値を漢数字にするには、どのようにしたらいいのか、その対応方法をご紹介します。

Excel。複雑な条件の合計を算出するには、DSUM関数をつかって対応します。【total】

Excel。複雑な条件の合計を算出するには、DSUM関数をつかって対応します。

＜DSUM関数＞

AND条件の合計はSUMIFS関数をつかうことで、手早く算出することができます。

OR条件の合計はオートフィルターをつかったSUBTOTAL関数という方法もあります。

しかし、AND条件とOR条件が混ざった複雑な合計の場合は、これらの関数では対応することが難しくなります。

次の表をつかって、新宿店の鉛筆の金額と渋谷店の消しゴムの金額の合計は、いくつなのでしょうか。

このような条件の場合、「新宿店の鉛筆」で合計を「渋谷店の消しゴム」で合計をそれぞれ算出したら、その合計と合計を合算させることで、算出は可能です。

ただし、この条件が増えてしまうと、合計どうしの合算数が増えてしまいます。

そこで、DSUM関数をつかうことで、手早く合計を算出することができます。

DSUM関数は、事前に、条件となる表を用意する必要があります。

条件の表は、F1:G3に設定しました。

では、G5にDSUM関数の数式を設定します。

=DSUM(A1:D11,D1,F1:G3)

これで、AND条件とOR条件が混ざった複雑な条件での合計を算出することができます。

ではDSUM関数の引数を確認します。

最初の引数は、データベース。

表全体です。

見出し行も含めて範囲選択をしますので、A1:D11

次の引数は、フィールドです。

合計したい列見出しを選択しますので、D11

最後の引数は、条件です。

条件の表である、F1:G3を選択します。

Excel。GAMMADIST関数で、ガンマ分布の確立を算出します【GAMMADIST関数】

Excel。GAMMADIST関数で、ガンマ分布の確立を算出します

＜関数辞典：GAMMADIST関数＞

GAMMADIST関数

読み方： ガンマディスト

読み方： ガンマディストリビューション

分類： 互換性 

GAMMADIST(x,α,β,関数形式)

ガンマ分布の確立を算出します　

Excel。何か月後の何日はEDATE関数でつくれないので、どうしたらいいの【how many months later】

Excel。何か月後の何日はEDATE関数でつくれないので、どうしたらいいの

＜DATE+YEAR+MONTH関数＞

EDATE関数をつかうことで、日付から何か月後の同日をつくることはできます。

ところが、日付から何か月後の何日という日付をEDATE関数ではつくることができません。

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

EDATE関数で出来ないならば、シンプルに、日付を作ることを考えるといいわけです。

A2に2024/6/3と入力してあります。

C2に3か月後の15日の日付を作る場合は、次のように数式を設定すればいいわけです。

=DATE(YEAR(A2),MONTH(A2)+3,15)

とてもシンプルな関数で対応できます。

DATE関数は、日付を作る関数

YEAR関数は、日付から年を抽出する関数

MONTH関数は、日付から月を抽出する関数

このMONTH関数で抽出された数値に「+3」すれば、3か月後の月を算出できます。

日付には、直接「15」と設定します。

これで、3か月後の15日を作ることができました。

どうしても何か月後ということから、EDATE関数と考えがちですが、単純に当月に「+3」をして、日付には「15」と入力すればいいわけです。

Excel。TOP1位～3位までのデータなら、行全体を塗りつぶししたい【TOP3】

Excel。TOP1位～3位までのデータなら、行全体を塗りつぶししたい

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

大量なデータからTOP1位～3位がわかるように、行全体を塗りつぶしたい場合、どのようにしたらいいのでしょうか。

このような場合は、条件付き書式をつかうのですが、条件付き書式で設定されている上位10項目を使用すると、セルは塗りつぶすことはできても、行全体に塗りつぶすことはできません。

そのため、条件式を設定する必要があります。

条件ですが、TOP3の売上金額がわかれば、それ以上の売上金額ならば、TOP1～3ということがわかります。

TOP3の値を算出にはLARGE関数をつかうことで、対応することができます。

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

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

ルールの種類を「数式を使用して、書式設定するセルを決定」をクリックします。

条件式のボックスに、

=$C2>=large($C$2:$C$16,3)

と設定します。

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

このように、TOP1～TOP3のデータならば、行全体を塗りつぶすことができました。

また同じ数値でもきちんと、塗りつぶされていることがわかります。

Excel。ガンマ累積分布関数の逆関数の値を求めるならGAMMA.INV関数【GAMMA.INV】

Excel。ガンマ累積分布関数の逆関数の値を求めるならGAMMA.INV関数

＜関数辞典：GAMMA.INV関数＞

GAMMA.INV関数

読み方： ガンマ・インバース　　

分類： 統計 

GAMMA.INV(確率,α,β)

ガンマ累積分布関数の逆関数の値を算出します

Excel。2024/1/21-1/27にIMCSCH関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2024/1/21-1/27にIMCSCH関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

1月21日

Excel。

IMCSCH関数

読み方： アイエムコセカントハイパーポリック　　

読み方： イマジナリーハイパーポリックコセカント

分類： エンジニアリング 

IMCSCH(複素数)

複素数の双曲線余割を算出する　

1月22日

Excel。

IMDIV関数

読み方： アイエムディブ　　

読み方： イマジナリーディバイデッド

分類： エンジニアリング 

IMDIV(複素数1,複素数2)

複素数の商を算出する　

1月23日

Excel。

IMEXP関数

読み方： アイエムエクスプ　　

読み方： イマジナリーエクスポーネンシャル

分類： エンジニアリング 

IMEXP(複素数)

複素数の指数関数を算出する　

1月24日

Excel。

IMLN関数

読み方： アイエムログエヌ　　

読み方： イマジナリーログナチュラル

分類： エンジニアリング 

IMLN(複素数)

複素数の自然対数を算出する　

1月25日

Excel。

IMLOG10関数

読み方： アイエムログテン　　

読み方： イマジナリーログテン

分類： エンジニアリング 

IMLOG10(複素数)

複素数の常用対数を算出する　

1月26日

Excel。

IMLOG2関数

読み方： アイエムログツー　　

読み方： イマジナリーログツー

分類： エンジニアリング 

IMLOG2(複素数)

複素数の2を底とする対数を算出する　

1月27日

Excel。

IMPOWER関数

読み方： アイエムパワー　　

読み方： イマジナリーパワー

分類： エンジニアリング 

IMPOWER(複素数,数値)

複素数のべき乗を算出する

Excel。最小値のデータがわかりやすいように、行全体を塗りつぶしたい【minimum value】

Excel。最小値のデータがわかりやすいように、行全体を塗りつぶしたい

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

最小値のデータがどれなのか、わかりやすいように、行全体を塗りつぶしたい場合には、どのようにしたらいいのでしょうか。

最小値のデータならば、塗りつぶしたいということから、使う機能は条件付き書式です。

では、A2:C7を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックして、新しいルールダイアログボックスを表示します。

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

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

次の数式を満たす場合に値を書式設定の条件式のボックスに、数式を設定します。

=$C2=min($C$2:$C$7)

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

これで、最小値の行全体を塗りつぶすことができます。

なお、設定した条件式ですが、

「$C2」と列固定の複合参照にすることで、行全体を塗りつぶすことができます。

その値と、=min($C$2:$C$7)で算出される最小値が等しいならば、最小値ということがわかりますので、最小値の行全体を塗りつぶすことができるというわけです。

Excel。入力された文字が、すべて全角なのか手早くチェックしたい【full-width】

Excel。入力された文字が、すべて全角なのか手早くチェックしたい

＜LEN関数とLENB関数＞

住所などを全角で入力しているのか、手早く確認したい場合には、LEN関数とLENB関数をつかうことで、確認することができます。

A列には住所が入力されています。

入力されている文字が全角なのかを確認するために、B2に次の数式を設定しました。

=LEN(A2)*2=LENB(A2)

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

TRUEと表示されていれば、全角の文字。

FALSEと表示されていれば、一部全角の文字ではないということが確認できます。

では、どのような仕組みなのか、数式を確認してみましょう。

 

LEN関数は、半角も全角も1文字は1と算出します。

LENB関数は、半角は1と算出し、全角は2と算出します。

そこで、LEN関数をつかって算出した結果を2倍した値と、LENB関数をつかって算出した結果が同じならば、すべての文字が全角であるということが確認できるというわけです。

Excel。大量のセルに同じ文字列を一括入力できます。【Batch input】

Excel。大量のセルに同じ文字列を一括入力できます。

＜便利な機能＞

Excelの機能で、大きな範囲に同じ文字列を一括入力することができます。

範囲選択をして文字入力をしたら、Enterキーで確定させるところを、

Ctrl+Enterキーと入力することで一括入力できます。

Excel。データが揃ってから、手早く平均値を算出するには、どうしたらいいの。【data is available】

Excel。データが揃ってから、手早く平均値を算出するには、どうしたらいいの。

＜IF+AVERAGE+COUNT関数＞

平均値を算出するならば、AVERAGE関数をつかうことで、手早く算出することができます。

ただし、平均値は、データの件数で除算する必要があります。

そこで、データの一部が揃っていないものと揃っているものではデータ数に差があるので、算出された平均値の意味は、当然変わってしまいます。

データが揃っていない場合には、平均値を算出しないで、保留にするには、どのようにしたらいいのでしょうか。

データが揃っていないところには、数式を削除して、揃ってから、数式をコピーするというのも、面倒です。

データが全て揃っているのを確認して、揃っていたら、平均値を算出する数式に変更します。

E2の数式を次のように設定します。

=IF(COUNT(B2:D2)=3,AVERAGE(B2:D2),"データ待ち")

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

これで、データが揃ってから平均値を算出するようにできました。

数式を確認します。

IF関数をつかって、データが揃っていない時には、「データ待ち」と表示するようにします。

IF関数の最初の引数である論理式には、

COUNT(B2:D2)=3

COUNT関数をつかって、データの件数を算出させています。

件数が3ならば、データが揃っているとわかる仕組みです。

2つ目の引数であり、真の場合は平均値を算出したいので、AVERAGE関数をつかって、平均値を算出します。

このように、データの件数を算出することで、データが全て揃っていなければ、平均値を算出しないようにできます。

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです【consecutive dates】

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです

＜DATE+INT+ROW関数＞

連続した日付を入力するには、オートフィルをつかうことで、手早く設定することができます。

ところが、次のような複数の同じ数だけの連続した日付を入力したい場合、オートフィルだけでは、対応することができません。

次の表のように、2日ごと3日ごとに同じ数だけの連続した日付を入力しています。

このような場合、数式をつかって対応します。

A2には、

=DATE(2024,5,INT(ROW(A2)/2))

という数式を設定します。

あとは、オートフィルで数式をコピーするだけで、日付を入力することができます。

数式を確認します。

DATE関数は、日付を作る関数です。

年と月は、今回直接入力しました。

日がポイントです。要するに、1をどのようにつくるのか、そして、2個1を用意することができるのかという点です。

そこで、INT+ROW関数で対応するというわけです。

INT関数は整数にする関数。

ROW関数は行番号を算出する関数です。

ROW(A2)なので、2。

それを2で除算するわけです。

結果は、2÷2なので、1ですね。

A3ならば、3÷2なので、1.5ですが、INT関数で整数化されるので、1ですね。

また、C2に設定した数式は、

=DATE(2024,5,INT(ROW(C3)/3))

設定したい日になるようにすれば、3日ごとでも4日ごとでもつくれます。

Excel。ガンマ分布の確立を算出するのが、GAMMA.DIST関数です。【GAMMA.DIST】

Excel。ガンマ分布の確立を算出するのが、GAMMA.DIST関数です。

＜関数辞典：GAMMA.DIST関数＞

GAMMA.DIST関数

読み方： ガンマ・ディスト　　

読み方： ガンマ・ディストリビューション

分類： 統計 

GAMMA.DIST(x,α,β,関数形式)

ガンマ分布の確立を算出する

Excel。VBA。8桁の数値から日付に変換して、さらに、年月日で表示したい【DATE】

Excel。VBA。8桁の数値から日付に変換して、さらに、年月日で表示したい

＜NumberFormatLocalプロパティ＞

データを読み込んでみたところ、日付に該当するデータが8桁の数値でした。

A2:A6のように入力されていました。

これでは、日付として扱うことが面倒なので、日付にしたいわけです。

しかも、日付は年月日で表示したい。

そこで、今回は、Excel VBAでプログラム文をつくってみることにしました。

Sub 数字から日付()

    Dim i As Integer

    Dim row_count As Integer

 

    Dim nen As String

    Dim tuki As String

    Dim niti As String

   

    row_count = Range("A1").End(xlDown).Row

    For i = 2 To row_count

        nen = Mid(Cells(i, "a"), 1, 4)

        tuki = Mid(Cells(i, "a"), 5, 2)

        niti = Mid(Cells(i, "a"), 7, 2)

           

        Cells(i, "b") = nen & "/" & tuki & "/" & niti

        Cells(i, "c") = nen & "/" & tuki & "/" & niti

        Cells(i, "c").NumberFormatLocal = "yyyy年m月d日"

    Next

End Sub

A列をC列のように、年月日の表示形式で変換することできますが、確認のために、数値から日付に変換するために、B列をつくっています。

では、プログラム文を確認します。

お馴染みの変数宣言です。

row_countには、繰り返しの上限数を代入しています。

Dim i As Integer

Dim row_count As Integer

Dim nen As String

Dim tuki As String

Dim niti As String

row_count = Range("A1").End(xlDown).Row

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

8桁の数値の1文字目から4文字が年に該当しますので、その代入処理をMid関数で処理しています。

nen = Mid(Cells(i, "a"), 1, 4)

同じように、5文字目から2文字が月です。

tuki = Mid(Cells(i, "a"), 5, 2)

最後の日は、7文字目から2文字です。

niti = Mid(Cells(i, "a"), 7, 2)

日付に変更する処理

Cells(i, "b") = nen & "/" & tuki & "/" & niti

年月日の表示形式で表示したい場合は、まずは、日付に戻す処理をします。

Cells(i, "c") = nen & "/" & tuki & "/" & niti

その後、NumberFormatLocalプロパティをつかって、年月日の表示形式を設定します。

Cells(i, "c").NumberFormatLocal = "yyyy年m月d日"

このようにして、数値8桁を日付にすることができました。

Excel。2024/1/14-1/20にIMAGINARY関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/1/14-1/20にIMAGINARY関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

1月14日

Excel。

IMAGINARY関数

読み方： イマジナリー　　

分類： エンジニアリング 

IMAGINARY(複素数)

複素数の虚数係数を取り出す　

1月15日

Excel。

IMARGUMENT関数

読み方： アイエムアーギュメント　　

読み方： イマジナリーアーギュメント

分類： エンジニアリング 

IMARGUMENT(複素数)

複素数の偏角を算出する　

1月16日

Excel。

IMCONJUGATE関数

読み方： アイエムコンジュゲイト　　

読み方： イマジナリーコンジュゲイト

分類： エンジニアリング 

IMCONJUGATE(複素数)

共益複素数を算出する　

1月17日

Excel。

IMCOS関数

読み方： アイエムコサイン　　

読み方： イマジナリーコサイン

分類： エンジニアリング 

IMCOS(複素数)

複素数のコサインを算出する　

1月18日

Excel。

IMCOSH関数

読み方： アイエムコサインハイパーポリック　　

読み方： イマジナリーハイパーポリックコサイン

分類： エンジニアリング 

IMCOSH(複素数)

複素数の双曲線余弦を算出する　

1月19日

Excel。

IMCOT関数

読み方： アイエムコタンジェント　　

読み方： イマジナリーコタンジェント

分類： エンジニアリング 

IMCOT(複素数)

複素数のコタンジェント（余接）を算出する　

1月20日

Excel。

IMCSC関数

読み方： アイエムコセカント　　

読み方： イマジナリーコセカント

分類： エンジニアリング 

IMCSC(複素数)

複素数のコセカント（余割）を算出する　

Excel。GAMMA関数でガンマ関数の値を算出できます【GAMMA】

Excel。GAMMA関数でガンマ関数の値を算出できます

＜関数辞典：GAMMA関数＞

GAMMA関数

読み方： ガンマ　　

分類： 統計 

GAMMA(x)

ガンマ関数の値を算出します

Excel。チェックで役立つ、表と表を比べて、合致しないセルがあれば、塗りつぶしたい【match】

Excel。チェックで役立つ、表と表を比べて、合致しないセルがあれば、塗りつぶしたい

＜条件付き書式＞

表と表を見比べて、同じでないセルがあるかどうかをチェックするとします。

そして、異なるデータがあれば、そのセルを塗りつぶして、わかるようにしたい場合、どのようにしたらいいのでしょうか。

データが少なければ、目視でチェックしてもいいかもしれませんが、面倒です。

そこで、条件付き書式をつかいます。

E2:G7を範囲選択します。

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

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

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

次の数式を満たす場合に値を書式設定の条件式のボックスには、次のように数式を設定します。

=A2<>E2

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定するだけです。

設定した数式には、「<>」という等しくないという比較演算子をつかっています。

等しくない場合は、TRUEと判定されるので、塗りつぶしで設定した色がセルに反映されるという仕組みです。

Excel。AVERAGE関数は、範囲に空白とゼロで平均値が異なるので注意が必要です【blank and zero】

Excel。AVERAGE関数は、範囲に空白とゼロで平均値が異なるので注意が必要です

＜AVERAGE関数＞

Excelで平均値を算出することができる「AVERAGE関数」。

ちょっとした注意点があって、範囲選択内に「空白」と「ゼロ」がある場合、空白とゼロで算出結果が異なります。

ゼロは計算対象ですので、ゼロを除外したい場合には、「AVERAGE関数」ではなく、「AVERAGEIF関数」を使用します。

Excel。2024/1/7-1/13にIF関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/1/7-1/13にIF関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

1月7日

Excel。

HYPGEOM.DIST関数

読み方： ハイパージオム・ディスト　　

読み方： ハイパージオメトリック・ディストリビューション

分類： 統計 

HYPGEOM.DIST(標本の成功数,標本数,母集団の成功数,母集団の大きさ,関数形式)

超幾何分析の累計確率か確率密度を算出する　

1月8日

Excel。

IF関数

読み方： イフ　　

分類： 論理 

IF(論理式,[値が真の場合],[値が偽の場合])

条件で分岐して異なる計算結果を返す　

1月9日

Excel。

IFERROR関数

読み方： イフエラー　　

分類： 論理 

IFERROR(値,エラーの場合の値)

対象がエラーの場合に指定した値を返す　

1月10日

Excel。

IFNA関数

読み方： イフエヌエー　　

分類： 論理 

IFNA(値,NAの場合の値)

結果が#N/Aの場合は指定した値を返す　 　

1月11日

Excel。

IFS関数

読み方： イフズ　　

読み方： イフエス

分類： 論理 

IFS(論理式1,値が真の場合1,…)

1つまたは複数の条件で分岐して異なる計算結果を返す　

1月12日

Excel。

IMABS関数

読み方： アイエムアブス　　

読み方： イマジナリーアブソリュート

分類： エンジニアリング 

IMABS(複素数)

複素数の絶対値を算出する　

1月13日

Excel。

IMAGE関数

読み方： イメージ　　

分類： 検索/行列

IMAGE(ソース,[代替テキスト],[サイズ],[高さ],[幅])

ソースの場所からセルに画像を代替テキストとともに挿入できる。　

ソースはhttpプロトコルを使用

Access。クエリ。上位25%のデータを抽出するには、どのようにしたらいいの。【Top 25%】

Access。クエリ。上位25%のデータを抽出するには、どのようにしたらいいの。

＜トップ25%＞

テーブル内に大量のデータがあります。

上位25%を抽出する場合、どのようにしたらいいのでしょうか。

作成タブのクエリデザインから、クエリをつくります。

 

使用するフィールドを設定します。

今回は売上高の上位25%のデータを抽出します。

またわかりやすいように、抽出したデータは、降順にするとわかりやすいので、降順で並べ替えをしております。

そして、売上高の隣の列など、フィールドが設定されていないところをクリックして、クエリデザインのプロパティシートをクリックします。

プロパティシート作業ウィンドウが開いたら、選択の種類が「クエリプロパティ」になっていることを確認して、トップ値を「25%」に設定します。

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

これで、上位25%のデータだけ抽出したクエリをつくることができました。

Excel。初期投資の将来価値を算出できるのが、FVSCHEDULE関数です。【FVSCHEDULE】

Excel。初期投資の将来価値を算出できるのが、FVSCHEDULE関数です。

＜関数辞典：FVSCHEDULE関数＞

FVSCHEDULE関数

読み方： エフヴイスケジュール　　

読み方： フューチャー・バリュー・スケジュール

分類： 財務 

FVSCHEDULE(元金,利率配列)

初期投資の将来価値を算出する

Excelのショートカットキー。Ctrl＋ShiftキーとTabキーを紹介【shortcut】

Excelのショートカットキー。Ctrl＋ShiftキーとTabキーを紹介

＜Ctrl+Shiftキー＋Tabキー＞

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

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

Ctrl＋Shift＋`

数式を表示する（アクサングラーブ）

Ctrl＋Shift＋¥

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

Ctrl＋Shift＋Space

全選択。Ctrl+Aと同じ

Ctrl＋Shift＋Space

図形などオブジェクトを選択時すべてのオブジェクトを選択する

Ctrl＋Shift＋Tab

ブックを切り替える