今週のFacebookページの投稿 2021/5/24-2021/5/30【one thing】

今週のFacebookページの投稿　2021/5/24-2021/5/30

＜Facebookページ＞

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

5月24日

Excel。ショートカット。

Ctrl+4で、下線の設定／解除をすることができますね。

5月25日

Excel。ショートカット。

Ctrl+5で、取り消し線の設定／解除をすることができますね。

5月26日

Excel。ショートカット。

Shiftキー＋Altキー+=キーで、なんと、SUM関数が挿入されます。

確かにΣのオートSUMボタンを押すよりかは早いけど…

5月27日

Excel。ショートカット。

Ctrlキー＋PageDownキーで、次のシートに移動できます。

5月28日

Excel。ショートカット。

Ctrlキー＋PageUpキーで、前のシートに移動できます。

5月29日

Excel。ショートカット。

Altキー＋Enterキーで、セルの中で改行できます。

勝手に折り返されることから解放されますね。

5月30日

Excel。ショートカット。

Ctrlキー＋Shiftキー+*キーで、表全体を選択することが出来ちゃいますね。

大きな表やデータの時は、便利ですね。

Excel。同じデータごとに累計値を算出したいけど、どうやったら簡単に求めることができるのか。【Cumulative】

Excel。同じデータごとに累計値を算出したいけど、どうやったら簡単に求めることができるのか。

＜SUMIF関数＞

同じデータを見つけながら和算で累計値を算出すればいいことはわかっていても、膨大なデータから目視で探しながらというのは、面倒というか、できません。

例えば、次のようなデータ。

D列には、店舗別の累計値を算出してあります。

たった10件のデータですが、1件目の新宿店の売上高を次の新宿店の売上高と目視でみつけて、和算することを繰り返すのは中々面倒です。

データを店舗名ごとに並び替えてしまえば、データが密集するので、SUM関数と範囲の最初を絶対参照にする方法で累計値は簡単に算出することはできますが、データはそのままで店舗別の累計値を算出するには、どうしたらいいのでしょうか？

考え方としては、累計値を算出する数式の延長線上にあります。

累計値を算出するだけならば、D2の数式は次のように設定すれば、いいわけです。

=SUM($C$2:C2)

和算なのでSUM関数をつかいます。

それと、引数の範囲の始点を絶対参照にしておくことで、オートフィルで数式をコピーすると自動的に算出範囲が広がってくれます。

ちょっと工夫をすることで、累計値をSUM関数で算出することができるわけです。

本題にもどりましょう。考え方としては、「店舗名が新宿という条件がついている」ということですから、条件付き和算。

つまり、「SUMIF関数」を使用すればいいわけですね。

D2に設定する数式を、

=SUMIF($B$2:B2,B2,$C$2:C2)

と設定して、数式をオートフィルで数式をコピーしたら完成です。

目視で確認しながら和算させることを考えたら、時短で算出することが出来ましたね。

では、数式の説明をしておきましょう。

最初の引数の「範囲」には、「$B$2:B2」。

これは、次の引数の「条件」が含まれているデータのある範囲に該当しますから、B列の店舗名を設定します。

ポイントとなるのは、先程と同じで、始点となるセル番地を絶対参照にしておくことでした。

つぎの引数である「条件」は、「B2」を設定します。

引数の3つ目は、「合計範囲」。C列の売上高です。

ここも、始点を絶対参照にしますから、「$C$2:C2」と設定します。

今回は、データごとに累計値を算出するということでしたが、ちょっとしたアイディアで算出することができましたので、機会があれば、SUMIF関数をつかって累計値を算出してみてはいかがでしょうか。

Excel。何気なく使っている平均値を算出するAVERAGE関数にも注意点があります。【function:AVERAGE】

Excel。何気なく使っている平均値を算出するAVERAGE関数にも注意点があります。

＜AVERAGE関数＞

Excelを勉強すると、合計のSUM関数の次に覚えるのが、オートSUMボタンに収納されている平均値を算出できる「AVERAGE関数」ですね。

平均値といってはいますが、「算術平均」のことですね。

日頃から何気なくつかっていますが、注意点もあります。

それは、「数値」を対象として平均を算出しているということです。

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

B列のNO4は空白。C列のNO4には、「0（ゼロ）」を入力しています。

ご覧のように、平均値の算出結果がことなっています。

B7の数式は、

=AVERAGE(B2:B6)

この式を、C7に、オートフィルで数式をコピーしています。

「数値」を対象とするため、むやみやたらに、「0（ゼロ）」をいれてしまうと、データ対象になってしまうので、除外したい時は、空白か、あるいは、AVERAGEIF関数をつかって「0（ゼロ）」を算出対象から除外しなければなりません。

今回のようにデータが小さければ、すぐに、わかりますが、データ量が膨大な場合で、「0（ゼロ）」を除外する必要がある場合は、単純にAVERAGE関数を使うと、大幅に算出結果が変わってしまうので、注意が必要ですね。

では、次の表をみてください。

B列の平均値は、０と同じ値で算出されているのがわかります。

強引ですが、表示形式で、「0（ゼロ）」を見えなくさせています。

このように、表示上見えなくなっている場合は、セル自体に、数値が入っているわけですから、算出対象になります。

ただ、表示形式だけではなくて、Excelのオプションの詳細設定で、「ゼロ値のセルにゼロを表示する」のチェックマークがオフになっていると、痛い目にあいますので、なんか算出結果がおかしい時は、Excelのオプションも確認してみてください。

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

AVERAGE関数の読み方は「アベレージ」です。

カテゴリーとしては、「統計」に所属しています。

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

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

なお、

算術平均は、AVERAGE関数

相乗平均は、GEOMEAN関数

調和平均は、HARMEAN関数

と分かれています。

Excel。非表示対応の連番は、SUBTOTAL関数だと注意が必要。AGGREGATE関数を使います。【Sequential number】

Excel。非表示対応の連番は、SUBTOTAL関数だと注意が必要。AGGREGATE関数を使います。

＜オートフィルター+SUBTOTAL関数とAGGREGATE関数＞

オートフィルターをつかって、データを抽出しても、連番にしたいことがあります。

その場合、SUBTOTAL関数をつかうことで、対応することができるのですが、ある欠陥がありますので、注意が必要になります。

次のようなデータがあります。

 

今は、A列のNOは単なる数値の連番になっています。

オートフィルターをつかって、C列の売上高が2000以上のデータを抽出してみます。

売上高のオートフィルターにある「数値フィルター」の「指定の値以上」を使います。

なお、（すべて選択）の下に、四谷の売上高である。1227が表示されているのを覚えておくといいです。

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

2000以上と設定して、OKボタンをクリックします。

2000以上のデータが抽出されました。

NOフィールドを確認すると、当然のことながら、「連番」ではありません。

これを連番で表示したいというわけです。

非表示に対応するには、SUBTOTAL+COUNTA関数というのがお馴染みなのですが、欠陥があるのです。

A2に、次の数式を設定して、オートフィルター機能でA11まで数式をコピーしています。

=SUBTOTAL(103,$B$2:B2)

この数式を説明すると、SUBTOTAL関数の集計方法「103」というのは、COUNTA関数と同じことをするのですが、行が非表示になるとそれを除いて、数えてくれるわけです。

そして、範囲を、B2を起点とするために、開始のほうだけ、絶対参照を設定します。

こうすることで、データの範囲の拡張するにあわせて、参照する範囲を延ばすことができます。

では、通常の行の非表示を行って、動きを確認してみましょう。

非表示が対象外になるので、繰り上がる形でNOフィールドは「連番」になっていることが確認できました。

別に問題はないように思えますが、先程のオートフィルターをつかって、売上高2000円以上のデータを抽出してみましょう。

非表示にした行は戻しておきます。

NOフィールドは連番にはなっていますが、何か変ですよね。

最終データの四谷。

2000円以上でないのに表示されています？

おかしな現象は、オートフィルターオプションを表示させる前に見えていました。

四谷の売上高は1227と最小なので、本来ならば、（すべて選択）の下に表示されていなければなりません。

ところが表示されていない。

SUBTOTAL関数を使う前は、表示されていました。

どうやら、SUBTOTAL関数は、「小計」を算出する関数なので、最終行を「合計行」という認識になっているようです。

つまりデータ行ではないので、含めないで処理をしてしまうようです。

四谷の下に、合計を算出した行を追加してみると、理解できます。

C12には、SUM関数を設定しております。

これで、先程と同じようにオートフィルターでデータを抽出してみましょう。

今回は、四谷のデータが表示されていないことが確認できました。

このように、SUBTOTAL関数をつかった表でオートフィルターを使うときには注意が必要です。

では、非表示に対応した連番はつくることができないのでしょうか？

実は、SUBTOTAL関数の進化版というべきAGGREGATE関数を使うことで対応することができます。

A2には、

=AGGREGATE(3,5,$B$2:B2)

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

引数の3は、集計方法で、COUNTA関数と同じ動きをします。

引数の5は、オプション設定で、非表示に対応することができるようになります。

改めて、売上高2000円以上で抽出してみましょう。

AGGREGATE関数だと、問題はありません。

この差はなんなのかというと、

SUBTOTAL関数は、「小計」でAGGREGATE関数は、「集計」をします。

微妙ですが、全く異なっていますので、SUBTOTAL関数で希望通りにならない時には、AGGREGATE関数をつかって確認してみるというのもいいかもしれませんね。

今週のFacebookページの投稿 2021/5/17-2021/5/23【one thing】

今週のFacebookページの投稿　2021/5/17-2021/5/23

＜Facebookページ＞

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

5月17日

Excel。ショートカット。

F12キーで、名前を付けて保存ダイアログボックスが登場しますね。

5月18日

Excel。ショートカット。

F2キーで、アクティブセルの末にカーソルが入り修正が出来るようになりますね。

5月19日

Excel。ショートカット。

ShiftキーとF3キーで、関数の挿入ダイアログボックスが表示されますね。

Fxボタンと同じです。

5月20日

Excel。ショートカット。

CtrlキーとF11キーで、新しいシートを挿入することが出来ます。

ただ列幅がちょっと広めです。

5月21日

Excel。ショートカット。

Ctrl+1で、セルの書式設定ダイアログボックスを表示できます。

とてもよく使用しますね。

5月22日

Excel。ショートカット。

Ctrl+2で、太字の設定／解除をすることができますね。

5月23日

Excel。ショートカット。

Ctrl+3で、斜体の設定／解除をすることができますね。 

Excel。条件付き書式を設定したら空白は除きたいのに反映しちゃうのでどうにかしたい【Exclude white space】

Excel。条件付き書式を設定したら空白は除きたいのに反映しちゃうのでどうにかしたい

＜条件付き書式・AND関数＞

条件付き書式を設定したら、まれに、意外なリアクションをすることがあります。

例えば、次のような表。

D1の日付よりも前日だった、該当データの行全体に塗りつぶしをする条件付き書式を設定してみます。

A1:B9まで範囲選択をして、ホームタブの条件付き書式から「新しいルール」をクリックすると、新しい書式ルールダイアログボックスが表示されます。

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

=$B2<$D$1

と数式を設定して、塗りつぶしの書式を設定してみます。

この数式は、

D1の日付よりも、B列の日付が小さい。

すなわちD1より前の日付だったら。

という意味になりますから、空白は除外されているハズです。

ところが、OKボタンをクリックして確認してみると、空白のデータの行まで塗りつぶしされてしまっています。

B列には数式が設定されていて、その結果が空白になっているわけではありません。

完全な空白なのに、リアクションしてしまっています。

では、どうやったら、空白を除くことができるのでしょうか？

条件付き書式で設定する数式を修正することで対応するしか方法がありません。

A2:B9を範囲選択して、改めて、条件付き書式の「新しい書式ルール」ダイアログボックスを表示して、「数式を使用して、書式設定するセルを決定」を選択して、数式を設定します。

=and($B2<>"",$B2<$D$1)

あとは、塗りつぶしの書式を設定したらOKボタンをクリックしてみましょう。

今回は、空白データの行は対象から外れて、塗りつぶしされていないことが確認できましたね。

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

=and($B2<>"",$B2<$D$1)

どうやったら、空白を除くことができるかというのが、この数式のポイントです。

「<>」の比較演算子をつかうことで対応しました。

「空白じゃない」ものが塗りつぶしのターゲットになるわけですから、「空白だったら」という条件設定にしては、うまくいきません。

なので、今回は、「$B2<>""」とすることで、「B2が空白じゃない」という条件で判断させることができたわけです。

そして、そもそもの条件である、D1の日付以前ということですから、「$B2<$D$1」も合わせて条件にする必要があります。

AND関数をつかうことで、2つの条件を満たした場合。

すわなち「TRUE」になったら、セルを塗りつぶすという条件式を設定できたというわけです。

条件付き書式はとても便利で、様々な用途で使われていると思います。

数式をうまくつかうことで、色々対応することが可能になりますので、試してみるといいかもしれませんね。

Excel関数辞典 VOL.47。LOGINV関数～LOWER関数【dictionary】

Excel関数辞典　VOL.47。LOGINV関数～LOWER関数

＜Excel関数＞

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

LOGINV関数

ログインバース

累積確率から対数正規分布を算出

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

LOGNORMDIST関数

ログノーマルディスト/ログノーマルディストリビューション

対数正規分布の累積確率を算出

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

LOGNORM.DIST関数

ログノーマル・ディスト/ログノーマル・ディストリビューション

対数正規分布の累積確率か確率密度を算出

LOGNORM.DIST(x,平均,標準偏差)

LOGNORM.INV関数

ログノーマル・インバース

累積確率から対数正規分布を算出

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

LOOKUP関数

ルックアップ

1行/1列のセル範囲でせるを検索し対応するセルの値を返す　

LOOKUP(検索値,検索範囲,対応範囲)　ベクトル形式　

縦横を指定しないでセルを検索し対応するセルの値を返す

LOOKUP(検索値,配列)

LOWER関数

ロウアー

英字を小文字に変換する

LOWER(文字列)

Excel。VBA。日報など原版シートから月の日数分コピーしてシート名も日付に変更したい【Sheets name】

Excel。VBA。日報など原版シートから月の日数分コピーしてシート名も日付に変更したい

＜Excel VBA＞

日報シートを4月なら30シート。

2月だったら28シートをコピーしてしかも、0401のようなシート名に変更するという作業は、単純ではありますが、面倒以外のなにものでもありません。

次のような原版シートがあります。

これをベースとして、月の日数分コピーしたいわけです。

さらに、コピーしたシート名を、B1の営業日である日付と連動させたいわけですね。

しかもシート名は、「0401」のようにゼロ付で表示したいわけです。

これを手動で行うとしたら、そして、毎月発生するとしたら、「キツイ」「アキル」「ダルイ」作業でしかありません。

そこで、Excel VBAでマクロを作ったらどうなるのか、確認してみましょう。

すごく長いプログラム文になると思いきや、結構短いプログラム文で作ることが出来ます。

Sub 原版コピーシート名日付()

    Dim i As Integer

    Dim sheet_name As String

    Dim month_count As Integer

    Dim day_count As Integer

    Dim eigyou_day As Date

    

    eigyou_day = Range("b1")

    month_count = Month(eigyou_day)

    day_count = Day(DateSerial(Year(eigyou_day), month_count + 1, 1) - 1)

    

    sheet_name = Right("0" & month_count, 2) & "01"

    

    For i = 2 To day_count + 1

        

        Worksheets("原版").Copy after:=Sheets(Sheets.Count)

        ActiveSheet.Name = sheet_name

        ActiveSheet.Range("b1") = eigyou_day

               

        sheet_name = Right("0" & month_count, 2) & Right("0" & i, 2)

        eigyou_day = eigyou_day + 1

        

    Next i

    

End Sub

これを実行すると、原版を利用して、月ごとの日数分のシートが挿入されたことが確認できます。

プログラム文を確認していくことにしましょう。

お馴染みの宣言文ですね。

Dim i As Integer

Dim sheet_name As String

Dim month_count As Integer

Dim day_count As Integer

Dim eigyou_day As Date

「sheet_name」には、コピーした後のシート名で使用する変数です。

eigyou_day = Range("b1")

eigyou_dayには、B1の営業日を代入します。

month_count = Month(eigyou_day)

month_countには、B1の日付から「月」を抽出して代入しています。

day_count = Day(DateSerial(Year(eigyou_day), month_count + 1, 1) - 1)

これは、該当付きの末日の数値を算出しday_countに代入しております。

ポイントがあって、翌月1日を

「DateSerial(Year(eigyou_day), month_count + 1, 1」で、求めます。

そして、「-1」すると、先月の月末を算出することができます。

こうすることで、月末が何日なのか問題に対応することができます。

4月だったら、30日というような算出方法を用いると、うるう年に対応することが大変になってしまうので、「翌月-1」というのは、よく使う方法です。

sheet_name = Right("0" & month_count, 2) & "01"

「0401」というように「0付」で桁数を揃えたシート名を作るために、Rightをつかっています。

例えば、10月だったら、「010」として、その右から2文字分を採用するので「10」となるわけです。

ここでは、最初のシートである、「一日目」に対応させています。

For i = 2 To day_count + 1　～　Next i

繰り返し処理を行います。

これは、原版シートがあるので、原版をコピーするので、2枚目が1日目にあたるので、2からスタートさせています。

そのため、day_countで月末の数値では1つ足らないので、「+1」して多く繰り返し処理をします。

Worksheets("原版").Copy after:=Sheets(Sheets.Count)

「after:=Sheets(Sheets.Count)」で最後尾という指定ができるので、原版シートをシートの最後尾にコピーさせます。

ActiveSheet.Name = sheet_name

ActiveSheet.Range("b1") = eigyou_day

シート名とB1の値を、変更します。

sheet_name = Right("0" & month_count, 2) & Right("0" & i, 2)

次のシート名を準備しています。

「i」を日付で使うことで、1日ずつ増やしたシート名にすることができます。

eigyou_day = eigyou_day + 1

B1に挿入する営業日も「+1」させます。

このように、単純処理でしかも繰り返し同じことをするような場合には、Excel VBAでマクロをつくったらどうなるのかな？と考えてみるといいかもしれませんね。

Excel。データのバラツキ度合を算出、あまり使われない「平均偏差」のAVEDEV関数。【function:AVEDEV】

Excel。データのバラツキ度合を算出、あまり使われない「平均偏差」のAVEDEV関数。

＜AVEDEV関数＞

平均のAVERAGE関数をつかっていると、「AVEDEV」という候補が見えてくるが、いったいどんな関数なんだろうと思ったことがあるかもしれませんが、この「AVEDEV関数」は、平均偏差を算出することができる関数です。

平均偏差とは、「平均との差の絶対値」を合計してデータ件数で除算した値です。

それぞれのデータが平均値から、平均してどのぐらい離れているのかがわかるというものです。

どのようになるのか、データを使ってみてみましょう。

１０件のデータの数値が全部同じです。

当然、E1の平均値は50。

E1の数式は、

=AVERAGE(B2:B11)

E2に算出した平均偏差は、平均値と同じですから、離れていないので、離れ具合は「0」ですね。

なお、E2の数式は、

=AVEDEV(B2:B11)

では、データを少し変えてみましょう。

B3のデータを100。

B4のデータを98として、他は99とした場合でみると、平均値は99ですね。

ただ、先程とことなり、データがすべて同じではありませんので、平均値からの離れ具合である平均偏差は、「0.2」と算出されました。

もう一つサンプルをみてみましょう。

データをランダムにしてみました。

平均値は77.6。

この平均値よりも離れているデータがありますので、平均偏差の算出値は大きくなりました。

このように、平均偏差の算出した値が大きくなればなるほど、平均値からのバラツキが大きくなっていることがわかります。

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

AVEDEV関数の読み方は「アベレージ・ディビエーション」です。

カテゴリーとしては、「統計」に所属しています。

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

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

平均偏差は、標準偏差より手軽にデータのバラツキを調べることができますが、標準偏差や不偏分散のほうがよく使われているようです。

Access。年・月・日と別々のフィールドで管理されているのでクエリで年月日をつくりたい【DateSerial】

Access。年・月・日と別々のフィールドで管理されているのでクエリで年月日をつくりたい

＜Access: DateSerial関数＞

Excelだととても簡単にできることもAccessでは、できないとか、どうやったらいいのかわからないなどというケースが結構あります。

例えば、次のテーブル。

年フィールド・月フィールド・日フィールドというように、年月日を別々のフィールドで管理しているテーブルです。

日付になっていないので、期間計算など使用するとしたら、ちょっと不便です。

つまり、今回やりたいことは、「年月日をつくりたい」というわけですね。

Excelならば、DATE関数をつかうことで、簡単に年月日にすることができます。

Accessにも、Date関数が存在するのですが、使い方が全く異なっていています。

AccessのDate関数は、日付を作るというよりも、ExcelのTODAY関数に近いわけです。

例えば、=Date()とすれば、今日の日付を入力することができます。

ということで、同じ関数名であっても、処理が異なっているものがExcelとAccessの間に存在しています。

そこで、今回のように、ExcelのDate関数と同じ処理をしてくれるのが、DateSerial関数なのです。

DateSerial関数の引数を確認しておきましょう。

DateSerial ( year, month, day )

引数の「year」に注意点があって、基本数値は4桁である必要があります。

2桁だと、0～29までは、2000年～2029年と自動的に認識してくれますが、30～99だと、1930年～1999年という認識になっています。

あと、数年で2029年を迎えますので、年フィールドが2桁で使っているならば、早めに考えておく必要があるかもしれませんね。

それでは、作成タブにある「クエリデザイン」をつかって、Q年月日クエリを作っていくことにしましょう。

T年月日テーブルの全てのフィールドを今回は使用します。

そして、年月日を算出するための演算フィールドを追加します。

年月日: DateSerial([年],[月],[日])

という演算フィールドを作ってみました。

実際に入力する時には、

「[]（大カッコ）」は自動的に入力してくれますので、年月日: DateSerial(年,月,日)でOKです。

入力補助機能は有効に使う方が入力ミスを抑制することができます。

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

ご覧のように、年月日をつくることができましたね。

最初から年月日のフィールドをテーブルに作っておいて運用することができればいいのですが、様々なケースで管理運用していますので、DateSerial関数を知っているといいかもしれませんね。

Excelでは、DATE関数で、Accessでは、DateSerial関数で日付を算出するというのがポイントです。

なお、使用したT年月日の年・月・日のそれぞれのフィールドは「数値型」でしたが、「短いテキスト型」でも、DateSerial関数は、年月日を算出してくれます。

今週のFacebookページの投稿 2021/5/10-2021/5/16【one thing】

今週のFacebookページの投稿　2021/5/10-2021/5/16

＜Facebookページ＞

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

5月10日

Excel。ショートカット。

Ctrl+pで、印刷ダイヤログボックス。

Excel2010からはバックステージビューが表示されます。

5月11日

Excel。ショートカット。

Ctrl+;で、今日の日付が表示できます。

セミコロンなんですね。

5月12日

Excel。ショートカット。

Ctrl+:で、今の時間が表示できます。

コロンなんですね。

5月13日

Excel。ショートカット。

Ctrlキーとスペースキーで列選択ができますよ。

5月14日

Excel。ショートカット。

Shiftキーとスペースキーで行選択ができますよ。

5月15日

Excel。ショートカット。

CtrlキーとHomeキーで文頭に移動できますね。

A1へワープ！

5月16日

Excel。ショートカット。

CtrlキーとEndキーでデータが入っている最後に移動できますね。

テーブルだと、テーブルの末に移動ですね。

Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？【Count letters】

Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？

＜LEN+SUBSTITUTE関数＞

やりたいことは簡単でも、それをどうやって「数式化」したらいいのか、悩むケースがあります。

例えば、テキストファイルなどデータが送られてきて開いてみたら、セル内に○×の結果が詰まっていたというのが次の表。

B列のデータは、アンケート結果の○×が詰まったデータというわけです。

そのデータに○がいくつ含まれているのか？

×はいくつ含まれているのか？ということを知りたい場合、目視で数えるというのは、大変以外の何物でもありません。

Excel VBAでマクロを作成するとか、あるいは、一文字ごと、セルひとつずつに、○や×を入力し直すというのも大変ですし、MID関数とか使うのも面倒です。

今回のような場合、LEN関数とSUBSTITUTE関数のコンビネーションで、比較的簡単に問題を解決することができるのです。

とりあえず、D2にLEN+SUBSTITUTE関数のネストの数式を作ってみましょう。

D2に作る数式は、

=LEN(SUBSTITUTE(B2,"×",""))

E2の数式は、D2でつくった数式と異なります。

E2の数式は、

=LEN(B2)-D2

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

とても簡単に算出することができました。

仮に、○×▲のような３つであっても、作り方としては、同じです。

それでは、数式を確認していくことにしましょう。

D2に設定した数式。

=LEN(SUBSTITUTE(B2,"×",""))

LEN関数は、セル内の文字数を算出する関数です。

単純に=LEN(B2)とすれば、10と返してくれるわけです。

では、引数でつかっている、

SUBSTITUTE(B2,"×","")

を確認していきます。

SUBSTITUTE関数は、検索した文字を、希望する文字に置換してくれる関数です。

今回は、「×」を「””」つまり空白に置換させているわけです。

考え方のポイントとして、「○」だけ数えるには、「×」がなければいいわけです。

文字数を数えてくれる関数は、LEN関数というのがあるわけですから、「○」だけにしたい。

だったら、「×」を消しちゃえばいいのでは、という発想したわけです。

だから、「×」を「空白」にするために、SUBSTITUTE関数をつかったというわけです。

B2のセルは、×が3個あるので、それが3個の空白に置換されるわけですから、残った文字数を数えれば算出されるというわけです。

「○」の数がわかれば、あとは、全体から「○」の数を減算すれば、「×」の数を算出することができるわけですから、E2の数式は、

=LEN(B2)-D2

となるわけですね。

文字関係は、色々なアイディアで、算出したり、変えたりすることができますので、文字列関係の関数を知っておくといいのかもしれませんね。

Excel。入力文字を整える関数の一つ。半角文字に変換してくれるASC関数。【function:ASC】

Excel。入力文字を整える関数の一つ。半角文字に変換してくれるASC関数

＜ASC関数＞

Excelのデータベース機能は、オートフィルターやテーブルをはじめ、簡単で便利な機能が豊富に用意されています。

ところが、テキストファイルやCSVファイルなど読み込んだデータに限らず、入力したデータでも、「文字の揺らぎ」というのが結構やっかいなんですね。

たとえば、「全角」「半角」の統一問題があります。

そこで、文字列に含まれれる「アルファベット」「数字」「カタカナ」「記号」など半角文字に変換してくれるのが、『ASC関数』です。

A列の文字の全角をB列のように、半角に変換してくれるのが、ASC関数です。

このASC関数は、あくまでも、「全角」を「半角」に変えるだけです。

なので、漢字やひらがなといった、半角が存在していない場合には、そのまま表示されますし、半角ですでに入力しているものも、そのまま半角で表示されます。

「大文字」を「小文字」に変換するとかは、また別の関数のUPPER関数で算出します。

ASC関数の読み方は「アスキー」です。

カテゴリーとしては、「文字列操作」に所属しています。

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

ASC(文字列)

登場する時は、文字が揺らいでいることが多いかと思われます。

使い方自体は、簡単ですが、あまり使いたくない関数ではありますね。

Excel。簡単に作れるようになった、「ピクチャー円グラフ」でアピールしてみませんか？【Picture pie chart】

Excel。簡単に作れるようになった、「ピクチャー円グラフ」でアピールしてみませんか？

＜ピクチャー（絵）円グラフ＞

Excelのグラフは、アイディアによって、様々なグラフを作ることができるのですが、簡単に見えて出来ないグラフに「ピクチャー（絵）円グラフ」があります。

「ピクチャー（絵）円グラフ」とは、次のようなグラフです。

 

円グラフにイラストや写真を表示させているわけですが、単純に円グラフの塗りつぶしをしているわけではなく、実はちょっとアイディアが必要になるわけです。

次のデータから円グラフを作って、塗りつぶしできないことを確認してみましょう。

A1:B5を範囲選択して、円グラフをつくります。

グラフそのもの、系列の売上高をアクティブにして、書式タブの「図形の塗りつぶし」から「図」をクリックします。

塗りつぶしをするファイルを選択します。

今回は、「図の挿入」の「ストック画像」からサンプル写真をお借りして作っていきます。

選んで挿入ボタンをクリックします。

要素ごとに挿入した写真が表示されてしまい、一つの写真として表示することができません。

どのようにしたらいいのか、説明していきます。

C列にダミーデータを追加して円グラフを作っていきます。

ダミーの数値である。C2は、1でも構いませんが、100%の状態にしたいので、データは一つだけにします。

A1:C5を範囲選択します。

挿入タブの「すべてのグラフを表示」ボタンをクリックします。

グラフの挿入ダイアログボックスが表示されます。

すべてのグラフタブに切り替えます。

 

グラフの種類を両方とも「円」とします。第2軸がダミーでない方についていることを確認したら、OKボタンをクリックします。

グラフタイトルと凡例は、説明上大きく表示したいので、削除しております。

円グラフが重なっていますので、あとは、それぞれの塗りつぶし等を修正していきます。

書式タブのグラフの要素を「系列 "ダミー"」にします。

書式タブの「図形の塗りつぶし」を使って、図から塗りつぶしでつかう、写真を選択します。

選択方法は先ほど紹介しておりますので、割愛します。

図形の枠線を「枠線なし」にします。

ダミーは、見えていないので、変わっていないように見えますが、作業を進めていきます。

次に、「売上高」のデータを修正します。

書式タブの「図形の塗りつぶし」にある「塗りつぶしなし」をクリックします。

「図形の枠線」は枠線の太さや色を調整します。

グラフは次のようになりました。

二つの円グラフを使うことで、円グラフ全体を塗りつぶしたようにすることができます。

あとは、データラベルを表示したりして完成します。

 

作りたいグラフがスムーズにできない時には、第2軸をつかってみたらどうかなと考えてみるといいかもしれませんね。