Excel。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの【Full participation】

Excel。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの

＜IF＋LEN＋SUBSTITUTE＋CONCAT関数＞

Microsoft TeamsやOutlookなどでスタッフ全員の会議参加日時をみつけることが、比較的容易に出来るようになりましたが、これをExcelでやりたい場合はどのようにしたらいいのでしょうか？

スタッフさん個人個人のシートが用意されています。

やりたいことは、全員が「○」の日時を探すということです。

今回は、3人分のシートですが、人数が増えるとか、項目がもっと細かく区分けされていたら、目視で確認するのは、とても大変な作業だといえます。

これからご紹介する数式をつかうと、このような結果が表示されます。

「OK」と表示されているところは、3人全員が「○」と報告してきたところです。短時間で、日程調整が出来るというわけです。

では、どのような数式をつくったのか、B4の数式をみてみましょう。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

ちょっと長い数式ですが、関数ごとに確認していきましょう。

数式の内側から確認していきます。

最初の登場する関数は、「CONCAT関数」。

文字結合する関数です。

CONCATENATE関数の進化系ですね。

CONCAT関数のところだけを算出してみます。

B4に、

=CONCAT(内藤:北沢!B4)

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

CONCAT関数が便利なところは、シート間で対応できるのと、範囲選択するだけで、文字結合できる点です。

スタッフごとのシートのB4の文字を結合させた結果が表示されています。

「○」が3個あれば、全員参加というのがわかるのですが、人数が多いと「○×」だらけで見つけるのも大変です。

そこで、「SUBSTITUTE関数」をつかって、「×」を消す作業をします。

そうすれば、「○」だけ残るので、見やすくなります。

B4の数式を

=SUBSTITUTE(CONCAT(内藤:北沢!B4),"×","")

と変更します。

SUBSTITUTE関数は、置換する関数です。

「×」を空白に置換するように数式を変更してみましょう。

「×」が消えて「○」だけが残りました。

「○」だけ残ったのですが、「○」が3個あれば、「OK」と表示するように修正すれば、もっとわかりやすくなります。

そこで、次のように修正します。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

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

「○」の数を算出します。

最後に、

IF関数をつかって、

LEN関数の結果が3（文字）だったら、「OK」と表示する

というように設定すれば、完成というわけですね。

新しく加わった関数をつかうことで、今までは算出するのに苦労していたことが改善できるかもしれませんので、数式を見直してみるのもいいのかもしれませんね。

Word。Meiryo UIでフォントサイズを大きくしたら、行間が勝手に広がってしまった【Line spacing】

Word。Meiryo UIでフォントサイズを大きくしたら、行間が勝手に広がってしまった

＜行間：固定値＞

Wordの初期設定のフォントは、「游明朝」なので、読みやすいように、「Meiryo UI」などの別のフォントに変更することがあります。

ただ、ちょっと困るのは、「Meiryo UI」に変更した後に、フォントサイズを大きくすると、行間が勝手に広がってしまうことです。

これを、元の行間に戻したいのですが、どのようにしたらいいのでしょうか？

まずは、行間の初期値を調べる必要があります。

レイアウトタブにあるページ設定ダイアログボックスを表示するボタンをクリックします。

ページ設定ダイアログボックスが表示されたらば、行間の「行送り」の数値を確認します。「18pt」と表示されています。

この18ptに行間を設定してあげれば、元の行間に戻すことができます。

該当するところを範囲選択したら、ホームタブに戻り、「段落の設定」をクリックして、段落ダイアログボックスを表示します。

行間を「固定値」に変更し、先程確認した、フォント数の「18pt」を間隔に設定します。

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

これで、行間が元に戻すことができました。

フォントサイズを変更して、行間が変わってしまったときは、行間の固定値で元に戻すことができます。

Access。クエリで「～かつ～」というAND条件の判定をしたいけど、どうしたらいいの？【And】

Access。クエリで「～かつ～」というAND条件の判定をしたいけど、どうしたらいいの？

＜Access＞

店舗管理しているテーブルがあります。

該当するデータがあるのかないのかを判断した結果を表示するクエリを作りたいとします。

そして、その条件というのが、「新宿店」でかつ「売上高が1000以上」だとします。

結果としては、次のクエリを作りたいわけです。

判定という演算フィールドをつくり、「新宿店」でかつ「売上高が1000以上」という条件に合致するものに「○」を表示してあるクエリです。

どのような演算フィールドをつくったらいいのか確認していきましょう。

では、作成タブのクエリデザインをつかって、クエリを作ります。

今回は、NOフィールドを除いたフィールドを使ってクエリをつくります。

判定フィールドの演算式は、

判定: IIf([店舗]="新宿" And [売上高]>=1000,"○","")

とすることで、対応することができます。

ところが、この演算式、ちょっと注意する必要があるのです。

それは、ExcelとAccessは違うということ。

計算式は、IF+ANDという仕組みは同じですが、引数が異なっている点がポイントです。

Excelで、IF+AND関数の数式をつくるとしたら、

=IF(AND(A1="新宿",B1>=1000),"○","")　

というように、IF関数の引数として、AND関数をつかっています。

そのAND関数内に、条件を設定してきます。条件と条件の間は、「,（カンマ）」で区切っています。

ところが、考え方は同じなのですが、Accessは、Excelと異なっています。

というのもの、Accessには、ExcelのようなAND関数が用意されていないからです。

なので、条件と条件の間を「,（カンマ）」で区切るのではなく、区切りをAnd演算子として、演算式を作る必要があるのです。

要するに、

判定: IIf(And([店舗]="新宿",[売上高]>=1000),"○","")

という演算式だとAnd関数が存在していないため、エラーになってしまうということです。

ですから、

判定: IIf([店舗]="新宿" And [売上高]>=1000,"○","")

としているわけです。

ちなみに、ExcelのIF関数は、Accessにはなく、Accessでは、IIf関数となっています。

ご覧のように、やりたいことは見えていても、Excelと同じように演算式をつくると、エラーになることが多々見受けられますので、Accessでの演算フィールドをつくるときは、注意が必要というわけです。

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

今週のFacebookページの投稿　2021/7/19-2021/7/25

＜Facebookページ＞

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

7月19日

Excel。グラフ。

散布図グラフは、データそれぞれの位置関係を表しているグラフですね。

7月20日

Excel。グラフ。

レーダーチャートグラフは、要素をそれぞれの項目ごとに分解したグラフですね。

栄養素とかチーム分析などでお馴染みですね。

7月21日

Excel。グラフ。

縦軸と横軸を入れ替えるときには、「行/列の切り替え」ボタンで、一発で替えることができますね。

7月22日

Excel。グラフ。

円グラフをつくったら、同じ色一色になったときには、「行/列の切り替え」ボタンをクリックしてみましょう。

7月23日

Excel。グラフ。

グラフの構成要素をうまく選択できない時は、レイアウトか書式タブの現在の選択範囲の上の▼をクリックして、構成要素を選択すると、選択できますね。

7月24日

Excel。グラフ。

軸の表示単位を変更したい時には、軸の書式設定ダイアログボックスの表示単位を変更するといいですよね。

7月25日

Excel。グラフ。

グラフ同士で比べるときには、必ず、最小位と最大値は同じ数字にしないと、グラフの雰囲気が変わってしますので、注意しないといけないですね。

Excel。設定日以降の行全体を塗りつぶす。だけど日付を直接入力したルールだとできません【Conditional formatting】

Excel。設定日以降の行全体を塗りつぶす。だけど日付を直接入力したルールだとできません

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

条件付き書式をつかうことで、視覚的にわかりやすい資料を作ることができるます。

なにかと重宝する機能のひとつです。

細かい設定を追加することで、希望の条件で書式を設定することができるのも、条件付き書式の特徴の一つかもしれません。

ところが、日付をつかった条件だと、思ったように反映してくれないことがあります。

次のような表があって、わかりやすいように、2022/1/1以降のデータの行全体に塗りつぶしをする条件付き書式を設定したいとします。

設定すると、このようになります。

行全体に塗りつぶしを行う条件付き書式を設定するわけですから、新しいルールを作る必要があります。

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

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

「数式を使用して、書式設定するセルを決定」をクリックして、「次の数式を満たす場合に値を書式設定」に次の数式を設定します。

=$A2>=2022/1/1

ただし、これ。NGなんです。

全部塗りつぶされちゃいました。

ちなみに、

=$A2>=”2022/1/1”

も、NGなんです。

なんで、ダメなのかというと、日付はそもそもシリアル値で数値なわけです。

そして、2022/1/1は文字扱いなので、このようなことになってしまうわけです。

そのため、日付を使いたい時には、次のように数式を作る必要があります。

=$A2>=date(2022,1,1)

DATE関数をつかうと、うまくいきます。

OKボタンをクリックして確認してみましょう。

希望のように条件付き書式が設定できたことがわかります。

ただ、この直接日付を設定する必要がないならば、DATE関数を使わないといけないということを考慮すると、セル参照させるほうが、わかりやすいと思います。

このような表を用意します。

F1に、日付を用意しています。

このF1を使って条件付き書式を設定していきます。

次の数式を設定します。

=$A2>=$F$1

とてもシンプルですが、これで問題ありませんので、OKボタンをクリックして確認しておきましょう。

先程と同じように条件付き書式が反映されていることが確認できました。

このように、条件付き書式の新しいルールで、日付を使うときには、思っているように反映されないことがありますので、ちょっと注意する必要があります。

Excel。なんでそうした！元に戻すボタンがホームタブ内に移動し固定されている【Office Insider】

Excel。なんでそうした！元に戻すボタンがホームタブ内に移動し固定されている

＜Office Insider：元に戻すボタン＞

Excelだけではなくて、WordもPowerPointも同じなのですが、Microsoft365(旧称Office365)の更新アップデートが7月中旬ごろにありました。

Office Insiderのバージョン2108（ビルド 14312 20008）が反映したら、タブやリボンのデザインが変わりました。

例えば、タイトルバーのタイトル（ファイル名）の表示が中央から左側に移動しました。

この程度は、問題ないのですが、今まで、クイックアクセスツールバーにあった、「元に戻すボタン」等が、ホームタブ内に移動しました。

ショートカットの「Ctrl + Y」や「Ctrl + Z」を知っていれば、問題はありませんけども、問題なのは、ホームタブ内にしか、「元に戻すボタン」等が無いということ。

つまり、データタブに移動している状態で、元に戻すボタンを使う場合、わざわざ、ホームタブに移動しないといけないわけです。

じゃあ、クイックアクセスツールバーに追加すればいいだろうということで、元に戻すボタンの上で右クリックをして、ショートカットメニューを表示します。

クイックアクセスツールバーに追加をクリックすると、なんと、タイトルバーに表示されている、上書き保存のフロッピーディスクボタンのとなりに表示されると思いきや、リボンの下に、クイックアクセスツールバーが表示されました。

なんと、クイックアクセスツールバーも変わっていました！

翌日、WordやExcel、PowerPointを起動したら、クイックアクセスツールバーが無い！いうことがあるかもしれませんので、その時には、慌てずに対応する必要がありそうですね。

Excel。VBA。新宿と品川だったらなどOR条件の時は、SELECT CASE文がオススメです【SELECT】

Excel。VBA。新宿と品川だったらなどOR条件の時は、SELECT CASE文がオススメです

＜Excel VBA＞

データから、条件に合わせて処理をしたい時には、データを読み込んだ後に数式を作ったり、自分で入力したりするわけですが、できることならば、データを読み込んだ後に、合わせて処理をするほうが、作業的には楽で、効率的に次の作業をすることができます。

例えば、次の表。

C列の地域フィールドにデータがないので、店舗名が「新宿」「品川」だったら、東京。「横浜」「川崎」だったら、神奈川と入力させる処理をしたいとします。

いろいろな方法で入力する作業をするならば、データを読み込んだ時に、地域名が入力されていれば、作業効率がいいわけですね。

目視で自力で入力するのは、時間がかかってしまいますし、数式をつくるのも、意外と面倒です。

今回のように、いわゆる「OR条件」の場合、SELECT CASE文をつかって対応するといいように思えます。

このようにプログラム文をつくってみました。

Sub or条件()

    Dim i As Long

    Dim lastrow As Long

    

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

    For i = 2 To lastrow

        Select Case Cells(i, "D").Value

            Case "新宿", "品川"

                Cells(i, "C").Value = "東京"

            Case "横浜", "川崎"

                Cells(i, "C").Value = "神奈川"

            Case Else

                Cells(i, "C").Value = ""

        End Select

    Next

End Sub

説明は後回しにするとして、実行してみましょう。

C列の地域フィールドに、東京や神奈川といった地域名が入力されているのが確認できました。

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

最初は、変数の宣言文です。

Dim i As Long

Dim lastrow As Long

    

変数iは、For to Next文で使用します。

lastrowは、データの最終行の行番号を取得するための変数です。

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

の行で、データの最終行を取得することができます。

この最終行の行数を取得することで、何度繰り返し処理をすればいいのか設定することができます。

For i = 2 To lastrow　～　Next

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

Select Case Cells(i, "D").Value

    Case "新宿", "品川"

        Cells(i, "C").Value = "東京"

    Case "横浜", "川崎"

        Cells(i, "C").Value = "神奈川"

    Case Else

        Cells(i, "C").Value = ""

End Select

処理の本体である、Select Case文です。

Case "新宿", "品川"

    Cells(i, "C").Value = "東京"

「新宿」と「品川」だったら、C列に「東京」という文字を入力するという意味ですね。

これを必要なパーツ数つくります。

最後に、該当しない場合も作っておく必要がありますので、

Case Else

    Cells(i, "C").Value = ""

とすることで、Select Case文が完了します。

とてもわかりやすく、比較的簡単なプログラム文でつくることができますので、Select Case文も知っておくといいかもしれませんね。

ただ、ちょっと注意点もあって、OR条件というイメージを強く持ちすぎて、Case文を次のようにすると、エラーが表示されてしまい実行できません。

Case "新宿" Or "品川"

このように、「,（カンマ）」で区切るところを「Or」にすると、ダメなんですね。

Excelそのものと、Excel VBAで、少々異なることがありますので、気を付ける必要がありますね。

Excel関数辞典 VOL.50。MEDIAN関数～MOD関数【dictionary】

Excel関数辞典　VOL.50。MEDIAN関数～MOD関数

＜Excel関数＞

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

MINUTE関数

ミニット

時刻から"分"を算出します。

MINUTE(シリアル値)

MINVERSE関数

マトリック インバース（エムインバース）

配列の逆行列を算出します。

MINVERSE(配列)

MIRR関数

エムアイアールアール（モディファイド・アイ・アール・アール）

定期キャッシュフローの修正内部利益率を算出します。

MIRR(範囲,安全利率,危険利率)

MMULT関数

マトリック マルチ プリケーション（エムマルチ）

2つの配列の行列積を算出します。

MMULT(配列1,配列2)

MOD関数

モッド

除算した余りを算出します。

MOD(数値,除数)

Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。【INDIRECT】

Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。

＜INDIRECT関数/INDIRECT＋SUBSTITUTE＋(LEFT＋CELL関数＞

月ごとに集計されているシートがあります。

それぞれのシートの合計値をまとめた、「年間」シートに数値を設定して年間合計を算出した集計シートをつくりたいわけです。

今回のケースは、サンプルなので、2枚のシートですんでいますが、年間となれば12枚のシートですし、もっと多くのシートからデータをコピーする必要があるとすれば、面倒な作業となってきます。

Excel VBAでプログラムをつくってもいいのですが、数式レベルで、効率よくコピーする方法はないのでしょうか？

このような場合、どのようなパターンがあるのか、ないのかを見つけるところから考えていくといいですね。

集計先のシートのB2に、セル参照の数式を設定してみましょう。

='1月'!B5

となっています。

横方向に、オートフィルで数式をコピーすれば、

='1月'!C5

='1月'!D5

と、横方向は、うまく参照結果が表示されていますが、縦方向にオートフィルで数式をコピーしても、当たり前ですが、うまくいきません。

理由は、シート名が違うからです。

シート名を変更する”だけ”かもしれませんが、とても面倒な作業です。

よくみると、シート名と、A列のデータが同じになっています。

シート名をA列に入力されている値そのものを使うことができれば、上手くいきそうです。

そこで、登場するのが、「INDIRECT関数」です。

B2にINDIRECT関数をつかって、次のような数式に変更してみましょう。

B2の数式は、

=INDIRECT($A2&"!b5")

この関数は、

='1月'!B5

という数式をどうやったらつくれるのかをイメージして作る関数です。

A2に1月という文字があるので、それをつかいたいわけです。

オートフィルで数式をコピーすることを前提としていますので、列固定の複合参照にしています。

そして、「&（アンパサンド）」で「"!b5"」という文字を結合させています。

縦方向に、オートフィルで数式をコピーすると、きちんと参照してくれたのですが、横方向にオートフィルで数式をコピーしたら、うまくいきません。

原因は、「"!b5"」。

文字になってしまっているので、オートフィルで数式をコピーしても「b」が「C」に自動的に変わってくれることはありません。

本当は、変わってくれれば、いいのですが…

そこで、どうやったら、「b」を「c」に出来るのかを考えて、B2の数式を次のように修正しました。

=INDIRECT($A2&"!"&SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")&5)

なんか、長くなっちゃいましたが、この数式を、縦方向。

横方向にオートフィルで数式をコピーすると、綺麗に、参照することができました。

 

では、この長くなった数式の説明をしてきます。「b」を「c」にするための数式が、

SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")

です。

内側から説明しないと、わからないので、最初は、CELL("address",B1)

CELL関数は、セルの情報を算出してくれる関数で、引数にaddressをつかうと、絶対参照がついたセル番地を文字として、算出してくれます。

B1のセル情報なので、「$B$1」という文字が算出されます。

LEFT(CELL("address",B1),2)

は、LEFT(“$B$1”,2)ということなので、LEFT関数をつかって、左から2文字分を抽出します。

これで、「$B」という文字が算出されています。

「$AA$1」だったら3文字分を抽出する必要があります。

SUBSTITUTE($B,"$","")　という状態になっていることがわかります。

SUBSTITUTE関数は、置換することが出来る関数なので、「$」を空白に置換させます。

これで、「B」だけを抽出することができるます。

ということで、シート名を参照できるセルをつくることで、INDIRECT関数をつかうことで、別シートから必要なデータを参照することができます。

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

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

＜Facebookページ＞

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

7月12日

Excel。グラフ。

Excel2010では11種類のグラフを作ることができますね。

7月13日

Excel。グラフ。

棒グラフは、項目ごとに値の大きさを比較するときに使うと便利ですね。

7月14日

Excel。グラフ。

積み上げ棒グラフは、項目ごとに積み上げた値を比較するときに使うといいですね。

7月15日

Excel。グラフ。

100%積み上げ棒グラフは、棒グラフ全体を100として値を割合で表示したものですね。

ニュースで政党支持率とかで使う場合が多いですね。

7月16日

Excel。グラフ。

折れ線グラフは、時間の経過に伴う変化や傾向を表すのに向いていますね。

7月17日

Excel。グラフ。

面グラフは、データとデータの差がハッキリわかりやすいグラフですね。

7月18日

Excel。グラフ。

円グラフは、全体に占める割合を表すのに便利なグラフですね。

シェアとか構成比のグラフですね。

Excel。補助円付き円グラフをつくるのに、ちょっとしたポイントがあります。【Auxiliary circle】

Excel。補助円付き円グラフをつくるのに、ちょっとしたポイントがあります。

＜補助円付き円グラフ＞

円グラフの一部のデータを付属する別の円グラフで描く、「補助円付き円グラフ」ですが、ポイントを抑えないと、作りたいようにつくることができません。

そこで、今回は、補助円付き円グラフの作り方を確認してみましょう。

作りたいグラフは、次の補助円付き円グラフです。

東京を構成している詳細を別の円グラフ（補助円）で描いているのが、補助円付き円グラフです。

このグラフを作成するための表を次のように用意しました。

B2の東京の合計値は、B3:B5の合算値で、

=SUM(B3:B5)

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

それでは、範囲選択をして、補助円付き円グラフを作っていきます。

早速ポイントなのですが、範囲選択を気を付けないと、おかしな補助円付き円グラフになってしまいます。

合算値の東京のデータは除外した、A3:B7を範囲選択します。

挿入タブの「円またはドーナツグラフの挿入」にある、「補助円グラフ付き円」をクリックします。

すると、補助円付き円グラフが挿入されました。

今回は、説明の為、グラフを少し大きくしております。

また、グラフタイトルも削除しています。

補助円付き円グラフはできたのですが、関東と関西のデータが補助円に入っている状態です。

実は、初期設定として、範囲選択した下２行が補助円になります。

そのため、次のポイントですが、補助円なのか、主の円なのかを設定してあげる必要があります。

グレーは、渋谷のデータなので、グレーのデータだけを選択します。

グレーのデータの上で、間隔をあけてクリックを2回します。

グラフの書式タブの「グラフ要素」が「系列1 要素 "渋谷"」となっていれば選択されています。

グラフ要素の下にある「選択対象の書式設定」をクリックすると、データ要素の書式設定作業ウィンドウが右側に表示されます。

系列のオプションの使用する軸にある、「要素のプロット先」を「補助プロット」に変更します。

主要プロットが、主の円グラフにプロットされることを意味していて、補助プロットが補助円にプロットされることを意味しています。

これを、すべての要素に対して、設定を繰り返すわけです。

すべて振り分けが終わったら、凡例を削除します。グラフはここまで出来ています。

次にデータラベルを表示していきます。

パーセントは小数点第１位まで表示するようにしていきます。

グラフのデザインタブから「グラフ要素を追加」にある「データラベル」から「中央」を今回は選択します。

データラベルが表示されるので、データラベルをクリックします。

先程表示されていた、作業ウィンドウが、データラベルの書式設定に変わっていることを確認します。

分類名とパーセンテージにチェックマークをいれます。

作業ウィンドウをそのまま下方向に移動すると、表示形式があります。

カテゴリを「パーセンテージ」にして、小数点以下の桁数を「1」にします。

小数点第一位まで表示したいからですね。

これで、グラフは、このようにデータラベルが表示されました。

ここで安心してはいけません。

補助円の元であるデータラベルは「その他」になってしまっています。

ここを「東京」にする必要があります。

この「その他」というデータラベルを選択する必要がありますので、データラベルをクリックします。

これで、全部のデータラベルが選択している状態なので、もう一度データラベルをクリックします。

すると、「その他63.3%」のデータラベルだけが選択できました。

さらに、「その他」の上でクリックすると、「その他」のデータラベルだけを選択することができます。

選択出来たら、その上で、ダブルクリックします。

変更メニューが表示されますので、今回は、セルの値を使いたいので、「セルの選択」をクリックします。

データラベル参照ダイアログボックスが表示されます。

「その他」を「東京」に変更したいので、A2をクリックします。

あとは、データラベルのフォントサイズを大きくしたり、見やすいように塗りつぶしを変えたりしていきましょう。

これで、補助円付き円グラフが完成しました。

簡単そうに見えますが、ちょっとしたポイントを抑えないと作るのに悩んでしまいます。

機会があれば作ってみてはいかがでしょうか