Excel。整数化するINT関数とTRUNC関数の違いはこうすれば、すぐにわかります。【Integerization】

Excel。整数化するINT関数とTRUNC関数の違いはこうすれば、すぐにわかります。

＜INT&TRUNC関数＞

Excelの関数には、同じような処理をする関数がチラホラあります。

例えば、整数にする関数にINT関数とTRUNC関数があります。

次の表で確認してみましょう。

B2の数式は、

=INT(A5)

と設定しています。

INT関数は、整数だけを抽出するので、A5の整数部分を算出しますので、120と算出されています。

C2の数式は、

=A5-B2

とすることで、小数部分を算出することができます。

B3の数式は、TRUNC関数をつかっています。

=TRUNC(A5)

TRUNC関数もまた、整数部分を算出する関数なので、120と算出されています。

C3の数式も、C2と同様に、減算した数式が設定されています。

=A5-B3

C2と同様に、小数部分を算出させています。

このように、INT関数をつかったとしても、TRUNC関数をつかったとしても、算出結果は変わりません。

では、なぜ、この二つの関数は存在しているのでしょうか？

それは、数値が「負数」のときに違いが発生するからです。

A5の値を「-120.34」と負数に変更してみます。

結果を見れば、一目瞭然。

TRUNC関数は、「-120」「-0.34」と算出されているのに対して、INT関数は「-121」「0.66」と違った数値を算出しまっています。

負数の場合、TRUNC関数は、0（ゼロ）に近い数値で算出するのですが、INT関数は、繰り上がる形で、0（ゼロ）から遠くなってしまうのです。

このため、「-121」と算出してしまったわけです。

整数化するにあたり、負数がないと断言できる場合は、INT関数をつかってもいいですが、確定していない場合には、TRUNC関数を使う必要があります。

このように、似ている関数というのが他にもありますが、何かときに、全く予期していない結果を算出する場合がありますので、注意する必要がありそうですね。

Word。知っていると便利。感想文でお馴染みの400字詰め原稿用紙が作れます。【Manuscript paper】

Word。知っていると便利。感想文でお馴染みの400字詰め原稿用紙が作れます。

＜原稿用紙＞

WordにもExcel同様に、知っていると便利というものが、ちょこちょこあります。

例えば、「400字詰め原稿用紙」。

あまりというか、ほとんど使うことはないのですが、Wordの機能に用意されています。

とても簡単に作ることが出来ますし、Wordで入力することもできます。

レイアウトタブに「原稿用紙設定」というボタンがありますので、クリックします。

原稿用紙設定ダイアログボックスが表示されます。

スタイルを「マス目付き原稿用紙」に設定します。これで、お馴染みの原稿用紙のデザインに変わります。

文字数×行数は、「20×20」で、400字詰め原稿用紙にすることができます。

罫線の色は、日頃使っていた、原稿用紙っぽい色にしてあげるといいですね。

用紙サイズは、A4横とします。

設定が完了したら、OKボタンをクリックします。

これで、完成です。

文字を入力してみると、縦書きで入力されることが確認できます。

もし、急に原稿用紙が必要になった場合には、Wordで代用できますし、手書きではなくてPC入力でつくることもできますので、こんなことも出来るんだと知っておくと、便利かもしれませんね。

Excel。ACCRINT関数は、定期利付債の経過利息を算出します。【ACCRINT】

Excel。ACCRINT関数は、定期利付債の経過利息を算出します。

＜関数辞典:ACCRINT関数＞

ACCRINT関数

読み方： アクリント または、アクルード・インタレスト

分類： 財務 

ACCRINT(発行日,最初の利払日,受渡日,利率,額面,頻度,[基準],[計算方式])

定期利付債の経過利息を算出します

Excel。オートフィルター不要。数式だけで別シートにデータを抽出できます。【Data extraction】

Excel。オートフィルター不要。数式だけで別シートにデータを抽出できます。

＜FILTER関数＞

データから該当するデータを抽出して、その結果を別シートに転記する作業は、簡単ですが、ちょっと面倒でした。

次のような表から、例えば、英語が50点以上のデータを別シートに転記する場合の作業を考えてみましょう。

抽出しなければ、データをコピーすることは出来ませんので、オートフィルターをつかって、英語の点数が50点以上のデータのみになるように抽出します。

その結果をコピーして、転記するシートに貼り付けて、作業は終了という流れが、比較的煩雑な作業をしなくても、対応できる方法かと思われます。

しかし、このような作業をしなくても、「FILTER関数」を使えば、とても簡単に抽出したデータを別シートに転記することができます。

では、FILTER関数をつかって、作業してみましょう。

転記先のシートに数式を作成します。

そして、この関数は、スピル機能をつかうことで、オートフィルで数式をコピーする必要もありません。

 

では、A2に次の数式を作成して確定させてみましょう。

=FILTER(成績シート!A2:E6,成績シート!E2:E6>=50)

英語の点数が50点以上のデータのみが転記されたことがわかります。

数式を確認してみましょう。

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

データの範囲ということですね。A2:E6を範囲選択しました。

次の引数は、「含む」。

条件があるフィールドを範囲選択します。

今回は、英語の点数が条件になるので、E2:E6ということになります。その範囲に、「>=50」と50以上という条件を加えます。

たった、これだけで、該当するデータを抽出し、さらに別シートに転記する作業も行うことができました。

最近のExcelには、Office365で登場した新しい関数が追加されましたので、いままで行っていた作業に新しく登場した関数をつかってみると、意外と作業効率を改善できるかもしれませんね。

また、今回紹介した、「FILTER関数」は、アイディアによって、様々な使い方ができそうなので、色々工夫して使ってみると、新たな発見がある気がしますね。

今週のFacebookページの投稿 2021/12/20-2021/12/26【one thing】

今週のFacebookページの投稿　2021/12/20-2021/12/26

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

12月20日

Excel。if関数は条件分岐関数です。

12月21日

Excel。or関数はいずれか一つでも成立するかの判断関数です。

12月22日

Excel。and関数はずべての条件が成立するかの判断関数です。

12月23日

Excel。not関数は指定した条件が成立しないことを判断関数です。

12月24日

Excel。rank.eq関数は順位をつける関数です。

12月25日

Excel。rank.avg関数は順位をつける関数です。

ちなみに同順位を平均化する

12月26日

Excel。iseven関数は偶数かの判断関数です。

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？【Consecutive columns】

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？

＜VLOOKUP＋COLUMN関数＞

リストから列方向に連続したデータを抽出したい場合、どうしたら効率よく実施することが出来るでしょうか？

次の表で確認してみましょう。

NOが2のデータを抽出したいとします。

A2に2と入力したら、B2:E2までのデータを抽出したいわけですね。

このような場合は、VLOOKUP関数をつかうことが、解決の一歩目となります。

B2に次の数式をつくったとします。

=VLOOKUP(A2,$A$6:$E$10,2,FALSE)

この数式をオートフィルで列方向にコピーしてみると、残念ながら、うまくデータを抽出することができません。

普通に、VLOOKUP関数で数式をコピーでは、対応できない数式ということがわかりました。

どこを、どのように修正すればいいのかを考えていきましょう。

最初の引数の検索値。

オートフィルで数式をコピーしたときに、検索値が動いてしまっては、検索することが出来ませんので、絶対参照をつかって、セル番地を固定しておく必要があります。

よって、検索値は、「$A$2」

2つ目の引数の範囲ですが、ここは、絶対参照をつかうことは変わりませんので、範囲は「$A$6:$E$10」

引数の範囲は、オートフィルで数式をコピーすることが前提の場合、絶対参照を設定することが多いです。

3つ目の引数の列数。

ここが一番のポイントになります。

「2」とか「3」のように、入力してしまうと、数式をコピーしただけ修正する件数が発生します。

そのため、「2」「3」…というように、連続した数値を自動的に入力する必要があります。

列方向に連続した数値が欲しい場合には、列番号を算出してくれる「COLUMN関数」をつかいます。

列番号に「COLUMN(B2)」と設定します。COLUMN(B2)は、B列なので、「2」を返してくれます。

列ではなくて行方向に連続した数値を算出させたい時には、ROW関数をつかいます。

最後の引数、検索方法は、完全一致なので、「FALSE」と入力します。

よって、修正した数式は、

=VLOOKUP($A$2,$A$6:$E$10,COLUMN(B2),FALSE)

と変更したら、オートフィルで数式をコピーして確認してみましょう。

これで、A2の数値を変更するだけで、列方向に連続するデータを容易に抽出することができました。

オートフィルターをつかって、抽出したデータをコピー&ペーストするという方法もありますが、VLOOKUPをつかってみると、簡単にデータを抽出することができます。

Excel。空白のセル。全角半角スペースの有無はCODE関数で確認できます【function: CODE】

Excel。空白のセル。全角半角スペースの有無はCODE関数で確認できます

＜CODE関数＞

見た目、空白のセルだと思ったら、全角、半角のスペース（空白）が入っていたということがあります。

また、IF関数なので、空白セルにしたい場合は、「””（ダブルコーテーション×2）」と設定しますが、本当に、「” “」のようにスペースキーによる空白とは意味が違うのでしょうか。

そこで、CODE関数をつかってみると、違いがわかります。

次の表は、CODE関数をつかった表です。

B2には、

=CODE(A2)

という数式を設定してあって、その数式をB7までオートフィルでコピーしています。

このCODE関数というのは、文字に割り振られている文字コードを算出することができる関数です。

「A」の文字コードは「65」というわけですね。

文字コードの割り振りは、結構いい加減というか、例えば「や」は9316なのに対して、小さい「ゃ」は、9315と割り振られているので、並べ替えを行うと、バラバラになってしまうわけですね。

A6には、空白に見えますが全角スペースを入力しています。

全角スペースの文字コードは「8481」。

A7には、半角スペースが入力されていて文字コードは「32」と算出されました。

このように、文字コードが割り振られていることから、全角・半角スペースも文字ということがわかるわけです。

B8に次の数式を設定しました。

=IF(A7="","",CODE(A7))

A7が空白だったら、空白「””」。

そうでなければ文字コードを表示する数式ですが、「32」と表示されていることから、半角スペースが入力されているので、空白ではないということがわかりました。

なので、A7の半角スペースを削除してみると、B8は「””」ということで、文字はないという結果が算出されています。

また、B7は、A7に文字がないので、「#VALUE!」というエラーが表示されました。

このように、IF関数なので、「半角スペースでいいじゃん」というわけにはいかないことがわかりました。

なお、文字コードはJISコードと同じです。

最後に、CODE関数の基本情報を確認しておきましょう。

CODE関数の読み方は「コード」です。

所属は、「文字列操作」です。

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

CLEAN(文字列)

Excel。ABS関数は、数値の絶対値を算出します。【ABS】

Excel。ABS関数は、数値の絶対値を算出します。

＜関数辞典：ABS関数＞

ABS関数

読み方： エービーエス　または、アブソリュート

分類： 数学/三角 

ABS(数値)

数値の絶対値を算出します

Absoluteの略です

Access。クエリ。未入力のデータを抽出したいけど、どうしたらいいの？【not entered】

Access。クエリ。未入力のデータを抽出したいけど、どうしたらいいの？

＜ヌル値：Is Null＞

Excelのオートフィルターをつかえば、空白のデータのみを抽出することや、逆に、空白以外のデータを抽出することは、容易にできるのですが、Accessのクエリを使った場合、どのようにしたらいいのでしょうか？

例えば、抽出条件に、「>0」とすればいいのでしょうか？

これでは、「0より大きいデータ」という条件になってしまうので、空白かそうでないかという抽出条件にはつかえません。

そこで、Excelの抽出条件としてつかうことがない「ヌル値」というのをつかうことで、抽出することができます。

ヌル値というのは、数値や文字列のどちらにも当てはまらないデータのことです。

ヌル値と等しいというイメージになりそうですが、「=（イコール）」のような比較演算子をつかった比較をすることはできません。

では実際に、次のテーブルをつかって、クエリをつくっていきます。

発注済みフィールドが空白のデータのみのクエリをつくっていきます。

作成タブのクエリデザインをクリックします。

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

フィールドデザインの「発注済み」フィールドの抽出条件に「is null」と入力して確定すると「Is Null」と表示されます。

それでは、実行して、データシートビューで確認してみましょう。

このように、発注済みフィールドが空白のデータのみのクエリを作成することができました。

このように、Is Nullとすることで、ヌル値のデータを抽出することができます。

それでは、逆に、空白でないデータはどのようにしたらいいのでしょうか？

「<>Is Null」というように、比較演算子をつかうことはできませんので、次のようにします。

抽出条件を「Is Not Null」としたら、実行して、データシートビューで確認してみましょう。

今度は、空白以外のデータを抽出することができました。

ヌル値をつかうことで、AccessもExcelと同様に、簡単に空白データを抽出することができます。

今週のFacebookページの投稿 2021/12/13-2021/12/19【one thing】

今週のFacebookページの投稿　2021/12/13-2021/12/19

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

12月13日

Excel。row関数は参照した行番号を算出関数です。

12月14日

Excel。column関数は参照した列番号を算出関数です。

12月15日

Excel。index関数は行・列位置を指定してデータを抽出関数です。

12月16日

Excel。match関数は指定したデータが範囲の何番目にあるかを算出関数です。

12月17日

Excel。indirect関数は文字列をセル番地や名前として直接的に参照する関数です。

12月18日

Excel。choose関数は値のリストから指定した位置の値を参照する関数です。

12月19日

Excel。offset関数は相対的な位置関係で指定したセルを参照する関数です。

Excel。重複データがあれば、行全体を塗りつぶしして把握したい【overlapping】

Excel。重複データがあれば、行全体を塗りつぶしして把握したい

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

重複データを把握するには、様々な方法があるのですが、今回は、重複データを削除しちゃうのではなくて、どこにあるのかを視覚的に把握したいので、重複データがあれば、行全体を塗りつぶすようにしていきます。

次のようにしたいわけです。

次のデータを用意しました。

条件付き書式をつかうのは、間違いないのですが、ポイントになるのは、どのような条件式にしたらいいのかという点ですね。

重複データを見つけるためには、ある関数を使用するのが基本となります。

それが、「COUNTIF関数」です。

考え方として、該当のデータがその範囲内で、何回登場しているのか。

つまり、2回以上登場している場合は、データが重複しているということがわかるわけです。

なので、件数を数える必要があるので、「COUNTIF関数」をつかうわけです。

それと、条件付き書式は、先に範囲選択をしている都合上、絶対参照で設定するところと、複合参照にするところがありますので、使い分けにも注意するところですね。

では、設定してきましょう。

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

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

ルールの種類を「数式を使用して、書式設定するセルを決定」に設定したら、次の数式を満たす場合に値を書式設定のボックスに、

=COUNTIF($B$2:$B$11,$B2)>1

と数式を設定します。

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

これで、重複するデータの行全体に塗りつぶしをすることができました。

設定した、数式を確認しておきましょう。

=COUNTIF($B$2:$B$11,$B2)>1

第一引数の、範囲には、絶対参照を設定しておきます。範囲が狂うと、当然結果も変わってしまうので、条件付き書式の場合でも、絶対参照を設定しておきます。

次の引数は、「$B2」。

このデータが全体の中でいくつあるのかを算出させるわけです。

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

条件付き書式と数式を組み合わせることで、視覚的にわかりやすい資料をつくることができますので、色々試してみると、いつも使っている資料も視覚的に、ひと味違う資料になるかもしれませんね。

Excel関数辞典 VOL.57。ODDFPRICE関数～OR関数【dictionary】

Excel関数辞典　VOL.57。ODDFPRICE関数～OR関数

＜Excel関数＞

今回は、ODDFPRICE関数～OR関数までをご紹介しております。

ODDFPRICE関数

オッドエフプライス：オッド・ファースト・プライス

最初の利払期間が半端な利付債の現在価格を算出します。

ODDFPRICE(受渡日,満期日,発行日,初回利払日,利率,利周り,償還価額,頻度,[基準])

ODDFYIELD関数

オッドエフイールド：オッド・ファースト・イールド

最初の利払期間が半端な利付債の利回りを算出します。

ODDFYIELD(受渡日,満期日,発行日,初回利払日,利率,現在価値,償還価額,頻度,[基準])

ODDLPRICE関数

オッドエルプライス：オッド・ラスト・プライス

最後の利払期間が半端な利付債の現在価格を算出します。

ODDLPRICE(受渡日,満期日,最終利払日,利率,利回り,償還価額,頻度,[基準])

ODDLYIELD関数

オッドエルイールド：オッド・ラスト・イールド

最後の利払期間が半端な利付債の利回りを算出します。

ODDLYIELD(受渡日,満期日,最終利払日,利率,現在価値,償還価額,頻度,[基準])

OFFSET関数

オフセット

基準のセルからの相対位置を指定する

OFFSET(参照,行数,列数,[高さ],[幅])

OR関数

オア

複数の条件のいずれか1つを満たすかどうかを調べる

OR(論理式1,[論理式2],…)

Excel。データ内で一番多い得点は何点なのか？そして何件あるのかを知りたい。

＜MODE.SNGL関数・COUNTIF関数＞

大量なデータがあれば、色々確認したいことがでてきます。

例えば次の表を使ってみます。

この程度のデータ量でしたら、目視で探すこともできそうですが、大量データの場合、一番多くの人が取った得点を知りたいとしたら、目視では大変です。

また、アンケート結果などの1～5程度の数値の件数を算出するならば、COUNTIF関数をつかうことで、算出することもできます。

ただし、０～100点というように、範囲が広い場合は、算出数が増えてしまい、効率が悪くなります。

このようなケースで使用するのが、「MODE.SNGL関数」です。

MODE.SNGL関数は、データの中で一番よく現れる値を算出することができる関数です。

一番よく現れる値のことを「最頻値」といいます。

「最頻値」は、最大値や中央値。

そして平均値（算術平均）と同じようにデータの傾向を知るために算出する項目の一つです。

D2に次の数式を作成します。

=MODE.SNGL(B2:B11)

算出結果は、54。

最多数が同数で複数ある場合も想定することができますので、その場合には、「MODE.MULT関数」を使用します。

あとは、何件あるのかを算出してみます。

B列に先程。算出した54が何件あるのかを算出したいので、COUNTIF関数をつかいます。

D5の数式は、

=COUNTIF(B2:B11,D2)

算出結果は、3件ということがわかりました。

このように、大量なデータの時に使うと、簡単に算出することができる関数というのが、Excelには色々あります。

色々調べてみて、つかってみたら、業務が改善できたというのがあるといいですね。

Excel。見栄えのする縦棒グラフをつくりたいので、絵グラフにする【Picture graph】

Excel。見栄えのする縦棒グラフをつくりたいので、絵グラフにする

＜絵グラフ＞

通常の集合縦棒グラフでも視覚的には、十分ですが、社外向け資料や、会議資料として見栄えする資料を作りたい時など、「絵グラフ」にしてみるのは、どうでしょうか？

次のようなグラフが絵グラフです。

事前に、使いたい絵（イラストなど）を用意しておきましょう。

通常通り、集合縦棒グラフをつくります。

縦棒グラフをクリックすると、棒グラフを選択することができます。

グラフの書式タブの「図形の塗りつぶし」にある「図」を選択します。

図の挿入ダイアログボックスが表示されますので、使いたい絵（アイコン）があるところまで移動し、使いたい絵を選択します。

縦棒グラフが、絵に変わりました。

絵が引き伸ばされている状態なので、200で一つの絵というように、今回は修正していきます。

グラフの書式タブにある「選択対象の書式設定」をクリックします。

右側にデータ系列の書式設定作業ウィンドウが表示されます。

「塗りつぶしと線」にある「拡大縮小と積み重ね」のチェックマークをオンにして、「単位/図」を200とします。

これで、200単位で一つの図形で表示することができました。

いつも同じ棒グラフでは、ちょっとと思ったら、絵グラフにしてみると、資料の雰囲気もかわりますので、使ってみてはいかかでしょうか。

Excel。VBA。抽出や並べ替えにも影響が！表内に小計があるのでまとめて削除したい【Delete line】

Excel。VBA。抽出や並べ替えにも影響が！表内に小計があるのでまとめて削除したい

＜Excel VBA＞

テーブル機能や、ピボットテーブルをつかいたいけど、次の表のように、小計行や合計行があって、つかうことができません。

今回のデータのように、小計行2行。

合計行、合わせて3行を削除するぐらいならば、別に問題は無いのですが、大量に小計行がある場合には、イチイチ範囲選択しては削除するというのは、面倒以外の何物でもありません。

そこで、このような面倒な単純処理こそ、Excel VBAでプログラムをつくって処理をさせるに限りますね。

プログラム文をつくってみましょう。

Sub 小計行削除()

    Dim i As Long

    Dim lastrow

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

    For i = lastrow To 1 Step -1

        If Cells(i, "a") Like "*計" Then

            Cells(i, "a").EntireRow.Delete

        End If

    Next i

End Sub

比較的短いプログラム文で対応することができました。

実行して確認してみます。

小計行を削除できれば、あとは、テーブルを挿入するにしろ、ピボットテーブルでつかうとしても、障害はありません。

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

最初は、変数宣言です。

Dim i As Long

Dim lastrow

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

lastrowには、データの最終行番号を設定します。

このあとの繰り返し文のために、データの最終行を使用します。

For i = lastrow To 1 Step -1

    If Cells(i, "a") Like "*計" Then

        Cells(i, "a").EntireRow.Delete

    End If

Next i

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

ここでポイントなのが、「Step -1」。

上位行から検索して該当した行を削除すると、1行下のセルが上に繰り上がってしまいます。

このようにしないと、繰り返しを実行している途中なので、削除されずに残ってしまうなど、問題が発生してしまいます。

なので、最終行番号から「-1」しながら繰り返すようにしています。

If Cells(i, "a") Like "*計" Then

小計・合計という文字があるかどうかの判断をしているIf文ですね。

結合されていますが、左側の列を指定してあげることで、結合を解除しなくても処理をしてくれます。

If Then EndIfで判定することができますね。

ここにもポイントがあります。

「Like "*計"」

Cells(i, "a")=”合計” としてしまうと、合計という文字でないと実行されません。

逆に、小計とすれば、今度は、合計を消すことができません。

そこで、イコールの代わりに、Like演算子を使います。

あとは、「合計という文字で終わる」と表現するために、「*（ワイルドカード）」を使えば条件行が完成です。

Cells(i, "a").EntireRow.Delete

このプログラム文の、EntireRow（エンタイアロウ）メソッドをつかうことで、行全体を選択することができます。

そして、Deleteメソッドで削除しています。

今回のような、単純作業を日々行っているようでしたら、Excel VBAをつかって、マクロをつかってみるというのも、アリかもしれませんね。

今週のFacebookページの投稿 2021/12/6-2021/12/12【one thing】

今週のFacebookページの投稿　2021/12/6-2021/12/12

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

12月6日

Excel。floor.math関数は数値を特定値の倍数にして切り捨て関数です。

ちなみにver2013からです。

12月7日

Excel。ceiling関数は数値を特定値の倍数にして切りあげる関数です。

12月8日

Excel。ceiling.math関数は数値を特定値の倍数にして切りあげる関数です。

ちなみにver2013からです。

12月9日

Excel。mround関数は最も近い基準値の倍数にして切り上げ切り捨て関数です。

12月10日

Excel。vlookup関数はデータ抽出関数です。ちなみに検索値が縦に並んだ表です。

12月11日

Excel。hlookup関数はデータ抽出関数です。ちなみに検索値が横に並んだ表です。

12月12日

Excel。lookup関数は対応範囲にあるデータ抽出関数です。

Excel。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？【DATE】

Excel。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？

＜DATE・LEFT・MID・RIGHT関数＞

見た目、日付とわかるからという感じで、何も考えないで入力している場合、Excelの機能を使えないということが、ちょこちょこあります。

例えば、次のような日付計算。

B4には、B2-B3。

C4には、C3-C3という数式が設定してあります。

B4には、#VALUE!というエラーが表示されていますが、C4は、普通に減算された結果が算出しています。

何が違うのかというと、B列の日付は、「.（ピリオド）」で区切られた日付になっています。

「.（ピリオド）」で区切られいる日付は、見た目が日付なだけで、Excelとしては、日付と認識していません。

「文字」として認識されているので、#VALUE!というエラーが表示されてしまっているのです。

最初から「/（スラッシュ）」で区切っていれば、問題はなかったのです。

今回のように、2件程度のデータならば、入力し直すことも容易ですが、件数が多い場合は、絶望的な作業となってしまいます。

そこで、「.（ピリオド）」を「/（スラッシュ）」に変換する方法を紹介していきます。

とても単純ですが、現場レベルとしては重要な作業です。

C2には、次の数式を設定してあります。

=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))

年月日を別々のセルに算出するならば、YEAR関数やMONTH関数などをつかって、抽出するわけです。

ところが、「.（ピリオド）」で区切ってしまっていると文字型になってしまっているので、YEAR関数などの日付系の関数を使うことができません。

文字型のデータならば、LEFT関数・MID関数・RIGHT関数をつかうことで、対応することができます。

そして、DATE関数をつかうことで、日付にすることができます。

DATE関数の最初の引数は、「年」の設定です。

左から4文字が年ですから、

LEFT(B2,4)

と設定することで、「年」を抽出することができます。

次の引数ですが、左から6文字目からの2文字を抽出する必要があります。

文字列の途中の文字を抽出したいので、MID関数を使います。

MID(B2,6,2)

と設定することで、「月」を抽出することができます。

最後の引数ですが、MID関数を使うよりも、右から数えた方が簡単なので、RIGHT関数を使います。

RIGHT(B2,2)

と設定することで、「日」を抽出することができます。

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

ただし、日付にはなるのですが、「0（ゼロ付）」の日付になっていません。

そのため、表示形式のユーザー定義をつかって、「0（ゼロ付）」の日付にしてあげるといいですね。

なお、TEXT関数をつかってしまうと、「0（ゼロ付）」の日付にすることはできるのですが、日付文字型になってしまうので、注意が必要です。

入力するという作業では、今回のように、もったいないというか、あとで、面倒な作業が発生することもありますので、ちょっと気を遣う必要がありますね。

Excel。大量データ。セル内改行を削除して一行にしたいなら、CLEAN関数で解決【function: CLEAN】

Excel。大量データ。セル内改行を削除して一行にしたいなら、CLEAN関数で解決

＜CLEAN関数＞

セル内改行されたデータが、少なければ、地道に手入力で修正するという方法でもいいのですが、CLEAN関数を使えば、セル内改行を削除して、一行にすることが手早くできます。

A列は、都道府県のあとに、セル内改行が設定されています。

これを解除して、B列のように一行表示にしたいわけです。

B2の数式は、

=CLEAN(A2)

あとは、オートフィルで数式をコピーすれば、一行表にすることができます。

なお、スピル機能があるExcelのバージョンならば、

=CLEAN(A2:A5)

とすれば、数式のコピーは不要です。

わざわざExcel VBAでマクロをつくるほどのことでもないわけですね。

セル内改行の削除をしたいときには、CLEAN関数と覚えておくといいかもしれませんね。

最後に、CLEAN関数の基本情報を確認しておきましょう。

CLEAN関数の読み方は「クリーン」です。

所属は、「文字列操作」です。

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

CLEAN(インデックス,値1,[値2]…)

CLEAN(文字列)

Excel。帳票で、上のセルと同じデータは「〃（おなじ）」で表示したい【Same】

Excel。帳票で、上のセルと同じデータは「〃（おなじ）」で表示したい

＜IF+COUNTIF関数＞

データベースとは別。帳票レベルにおいて、見やすくしたいということがあります。

例えば、次のような表。

A列の分類フィールドをみると、ペンが3レコードあって、クレヨンが4レコードある状態の表です。

データベースとしては、正しいのですが、帳票なので、繰り返しを「〃（同じ）」という文字で表示したい場合、どのようにしたらいいのでしょうか？

このような表にしたいわけです。

まさか、自力で、変更するのは大変ですし、置換で対応することもできません。

では、どのようにしたら効率よく「〃（同じ）」にすることができるのでしょうか？

そこで、考え方ですが、何度その文字が登場しているのかを算出させて、1回目に登場したところ以外を「〃（同じ）」にするようにすればいいわけです。

上のセルと同じ文字かどうかを、判断して、その件数を算出させるので、使用する関数はCOUNTIF関数がつかえそうですね。

では、次の表をつかって、数式をつくっていきます。

B列に分類2として、B2に次の数式を作りました。

=IF(COUNTIF($A$2:A2,A2)=1,A3,"〃")

あとは、オートフィルで数式をコピーすれば、「〃（同じ）」と表示することができました。

IF関数で「〃（同じ）」を表示させるかどうかの判定をさせていますが、論理式のCOUNTIF関数をつかうことで、容易に対応することができます。

論理式のCOUNTIF関数のところだけを取り出してみたのが、D列です。

D2の数式は、

=COUNTIF($A$2:A2,A2)

としてあります。

この数式のポイントは、最初の引数の「範囲」です。

最初のセル番地だけを、絶対参照にすることで、オートフィルで数式をコピーしたときに、自動的に、範囲が拡張する仕組みになっています。

A2ならば、A2:A2という範囲なので、「1」と算出されるわけです。

つぎのA3は、A2:A3という範囲の中でA3と同じものは2件あるので、「2」と算出することができるわけです。

こうすることで、COUNTIF関数で「1」以外のところが複数回登場しているということがわかります。

よって、「〃（同じ）」と表示させることができます。

データベースでは、「〃（同じ）」としてはダメですが、帳票として入力したい時には、IF+COUNTIF関数の組み合わせで対応することができます。

今週のFacebookページの投稿 2021/11/29-2021/12/5【one thing】

今週のFacebookページの投稿　2021/11/29-2021/12/5

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

11月29日

Excel。frequency関数は頻度集計・頻度分布関数です。

11月30日

Excel。int関数は整数化関数です。

12月1日

Excel。trunc関数は負数時の小数点以下切り捨て関数です。

ちなみにintと結果がことなります。

12月2日

Excel。round関数は四捨五入関数です。

12月3日

Excel。rounddown関数は切り捨て関数です。

12月4日

Excel。roundup関数は切り上げ関数です。

12月5日

Excel。floor関数は数値を特定値の倍数にして切り捨て関数です。

Excel。条件付き書式。日付が入力されている行だけを塗りつぶすようにしたい【Conditional formatting】

Excel。条件付き書式。日付が入力されている行だけを塗りつぶすようにしたい

＜条件付き書式＞

日付が入力されているデータがある行だけを塗りつぶすようにしたい場合はどのようにしたらいいでしょうか？

次のデータを用意しました。

この表だけだとしたら、一番古い提出日以降という条件なら、どうなるのでしょうか？

E2に一番古い日程を算出する数式を設定しました。

これ以上ならば、行全体を塗りつぶすように、条件付き書式を設定すればいいはずです。

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

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

「数式を使用して、書式設定するセルを決定」を選択して、「=$C2>=$E$2」と設定したら、OKボタンをクリックします。

結果はどうなるのでしょうか？

残念ながら、C9の空欄は塗りつぶされていませんが、C3の「未提出」という文字が入力されている行にも塗りつぶしされてしまっています。

なぜ、このようになってしまったのかというと、日付はシリアル値で管理されていることが原因です。

日付は数値な訳ですから、その数値よりもさらに、「未提出」という「文字」に割り振られている文字コードが大きいので、条件に合致してしまったわけです。

それと、E2に、資料のたびに日付を算出させるのも、効率が悪いといえます。

そこで、条件付き書式のルールを次のように変更してみます。

新しいルールダイアログボックスを表示して、条件を次のように変更します。

「=$C2/$C2=1」

それでは、OKボタンをクリックします。

C3の未提出という文字の場合でも、セルの塗りつぶしがされていないことが確認できました。

ところで、この数式の条件。

=$C2/$C2=1

日付の場合、シリアル値ですから、元は数値な訳です。

自分自身を除算すれば、数値の場合は、「1」と算出されるというわけです。

「未提出」のような文字の場合ですが、文字を除算すること自体できませんので、エラーが発生するわけですね。

なので「1」とならないわけですし、空白の場合も「1」にはなりません。

よって、日付がある行のみが、セルの塗りつぶしの対象となるわけです。

今回のように、条件付き書式は、ちょっとしたアイディアで、色々な条件にも対応することができますので、色々と使ってみると、意外な使い方が見つかるかもしれませんね。