月別アーカイブ: 2019年3月

名前の定義2

定義された名前からの名前の定義。

分かりづらい文章ですが、 名前の定義(名前ボックス)1 では名前ボックスから名前の定義を行いましたが、数式タブの定義された名前から名前を定義するともう少しいろいろできますということです。

名前の定義(名前ボックス)1では 同じブックの中で同じ名前は使用できません(例外あり)。 としていましたがその例外は定義された名前 の名前の定義から作成することができます。

数式タブの定義された名前から名前の定義をクリックします。または名前の管理から新規をクリックします。

新しい名前のダイアログボックスが開きます。

名前を入力した後に範囲の右のプルダウンメニューからその名前を使いたいシート名を選択します。

選択したシートでのみ、その名前が有効になります。範囲が限定されているので同じ名前を使うことも可能です。コメント欄は名前の管理のダイアログボックスで有効なコメントのようです。

当然、名前の管理から一度設定した名前の内容を編集したり削除したりすることも可能です。ただ、範囲は変更できないようです。

今、サンプルの使用している表で名前を付けていろいろな計算をしようと思ったときに、列方向で各科目である国語、算数、理科、社会と4つ、各人として6人分合わせて10個の名前を定義するのは面倒ですね。まとめて設定してしまいましょう。行見出し、列見出しを含む表全体を選択します。数式タブの定義された名前の選択範囲から作成をクリックします。選択範囲から作成のダイアログボックスが開きます。上端行と左端列にチェックを入れてOKボタンをクリックします。

名前の管理で確認すると自動で各行見出し、列見出しに対応した部分に名前が自動で付けられています。

既存の計算式に名前を後から対応させたい場合は名前の引用を使います。

セルC3にはパンの価格の120であるセルB3と税率であるセルB1を掛ける数式が入っています。セルB2には税率という名前がついています。セルC3の数式を=B3*税率と変更したいです。数式自体を手作業で編集するのは面倒ですし、間違いのもとです。

数式タブの定義された名前の名前の定義の右の下向き三角▼をクリックし名前の引用をクリックします。

定義された名前の一覧が表示されますので今回は税率を選択します。OKボタンをクリックします。

自動でセルB1への参照が税率に置き換えられます。

今回は該当するセルを選択してから名前の引用をしましたが、特定のセルを選択しておく必要はありません。シート内の該当する参照を全部一気に置き換えてくれます。別のシートからの参照は置き換えてはくれないようです。

エクセルで名前を使わなくても通常のセル参照で数式は作れますので使用しない方も多いようです。これらの名前の仕組みを理解していれば簡単だと思われます。是非使ってみてください。

INDEX関数

INDEX 関数はテーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。

セル範囲の中で何行何列目かを指定するとその値を返してくれます。マイクロソフトさんのエクセル関数の説明は毎度難しいですね。正確に記述するためには仕方ないのでしょうけれど。

下のような表で吉田さんの理科の得点を取得したい場合、点数の部分をセル範囲とすると(セルB2からセルE7)2行目の3列目が吉田さんの理科の点数になりますね。INDEX関数を使えばセル範囲と何行何列目が分かればセルの値が取得できます。

INDEX関数
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。
INDEX(配列, 行番号, [列番号])
INDEX(参照, 行番号, [列番号], [領域番号])

同じ関数なのに2通りの書き方があります。ちょっとややこしいですね。上図の様に関数の挿入でINDEX関数を選択しても更にどちらかを選択するよう求められます。しかし、一応のところ同じものだと思ってもらっていいかと思います。2つの定義で違うところは、最初の引数の配列と参照ですね。あとは領域の第4引数を取るかとらないかです。

第1引数の参照はセル範囲のことですね。上図ならセルB2:セルE7になります。このデータを引っ張り出したい表が1つの場合は引数の少ない方の INDEX(配列, 行番号, [列番号]) を選択しておけばよいでしょう。ふつうはこちらですね。

吉田さんの理科の点数をINDEX関数で求める際の第1引数としてセルB2からセルE7を選択します。第2引数は選択したセル範囲の2行目にあたるので2とします。第3引数は3列目になるので3とします。

=INDEX(B2:E7,2,3)

では、配列とは何のことでしょう。突き詰めると私も自信がありませんが、セル範囲以外に配列定数 が取れるということでしょう。エクセルの表にないデータを直接数式に入れられるということです。

上の例で行けば各自の成績を直接配列定数としてINDEX関数の中に入れます。

=INDEX({88,78,79,86;85,81,85,95;69,73,86,82;74,55,72,69;58,93,82,87;91,100,94,49},2,3)

B2:E7 が生のデータになっています。ここの部分が配列定数です。

INDEX関数は2つ種類があり、基本的に同じものだと理解しているのですが、4つ目の引数の領域のご説明がまだですね。これは、第1引数を複数指定した場合、その複数のどれですかということです。

=INDEX((B3:E8,B12:E17),2,3,2)

第1引数を小かっこで括ってセル範囲とセル範囲をカンマで区切ります。上図の場合は中間試験結果であるセルB3からセルE8までを領域1とします。期末試験結果であるセルB12からセルE17までを領域2とします。(B3:E8,B12:E17)が第1引数となります。第2引数はどちらの領域化は別として領域の2行目、第3引数は領域の3列目となります。第4引数が1の場合は領域1の方なので セルB3からセルE8まで の2行3列目の85ですが上の式の第4引数は2となっています。なので第2領域の セルB12からセルE17の2行3列の65となります。

第4引数を取る INDEX(参照, 行番号, [列番号], [領域番号]) の方は第1引数には複数の領域を設定できますが、配列定数を使い場合には複数の領域は設定できません。

配列定数

配列数式(CSEの数式)で絶対わかる配列数式と銘打ってご説明しましたがいかがでしたでしょうか。この中で配列定数という言葉が出てきましたので少しご説明します。

=SUM({60,180,100}*{1,2,3})
という数式の中括弧部分が配列定数です。 この数式は60*1と180*2と100*3の結果をSUM(合計)してくださいという意味になりますので60+360+300となりますから720という答えになります。中括弧は自分で手入力します。

これの何がうれしいのかといいますと、私的には何もうれしくありません。まず使わないでしょうね。

もう少しだけご説明しますと、先ほどの =SUM({60,180,100}*{1,2,3}) は下図で言うとD列とE列を掛けたものを合計してね。と言い換えることが可能かもしれません。

たまたま、列方向ですが行方向に考えて表自体を作り直しても同じことです。

定数というだけあって値を直に入力しており、セル参照ではありません。値を変更する場合は、直接式を編集しないとだめなのでちょっと面倒です。

この配列定数は中括弧で括るということと、値をカンマでつなげることというルールがあります。値が文字列の場合はダブルクォーテーションで括ります。

{値,”文字列”,値,値}

更に配列定数は上図の様に1列だけではなく複数列、または複数行を配列として持つことができます。(表ごと配列定数にする)

下図はよくある VLOOKUP の例です。セルA1に商品番号を入力するとセルB2に単価が表示されるという物です。

=VLOOKUP(A2,E2:G4,3,FALSE)

VLOOKUP関数の第2引数のセルE2:G4の範囲を配列定数で置き換えてみます。

=VLOOKUP(A2,{“a01″,”パン”,100;”a02″,”牛乳”,150;”a03″,”おにぎり”,120},3,FALSE)

{“a01″,”パン”,100;”a02″,”牛乳”,150;”a03″,”おにぎり”,120
この部分が配列定数です。

“a01″,”パン”,100 が1件のデータとなり、次の “a02″,”牛乳”,150 との間にセミコロン;をいれます。セミコロンで複数行のデータを記述していきます。

こうすることでセルE1からセルG4までの表がなくてもVLOOKUP関数で商品番号を入力すれば単価が返ってくるようにできます。

このようなことが配列定数で可能ですが、ちょっとも便利に感じませんね。

どうしてもエクセルの表にない値を表示したい場合(エクセルに新たに表を作成できない)などに使えるかもしれません。

マイクロソフトワード2019 音声読み上げ

音声読み上げでご紹介したものと基本同じものですが、ワード2019(office2019)はWindows10にしかインストールできないようです。Mac用のofficeは別です。

office2019同等というか先行する Office 365 Solo はWindows7でも8でも利用できるようです。

ここでご紹介するのはWindows10でoffice2019の場合は 別途TTS(Text-to-Speach) をインストールしなくても大丈夫ではないかというお話です。

私の環境ではWindows10にoffice2016をインストールしていたのですが、これを削除してoffice2019をインストールしました。office2016の時は 複数言語の TTS で読み上げ機能を使用する からダウンロードしていたのです。

MSSpeech_TTS_ja-JP_Haruka.msiというファイル名でした。

Ctrl + Alt + Space で読み上げを開始することができます。オプションからボタンを追加してもいいですが、こちらのショートカットの方が多機能です。

範囲選択をしなくてもカーソル位置から自動で読み上げを開始してくれます。また、段落単位で戻ったり先に進めたりできます。

設定ボタンをクリックすると TTS が選択できるのです。office2016の際は Haruka をダウンロードしてインストールしたのであるが2019になってインストールした覚えのない TTS が並んでいます。ということは初めから TTS がインストールされているのではないかということです。

AyumiとIchiroが加わっています。読み上げの修正等は相変わらずできないようです。ルビを振ってもダメです。ひらがなにするしかないようです。読み上げ速度は調整できますが、残念ながら遅くすると音声が低くなり、早くすると音声が高くなってしまいます。残念な点はいくつもあります。

ということで音声読み上げは Ctrl + Alt + Space です。ダメもとでお願いします。

配列数式(CSEの数式)

絶対わかる配列数式の解説です。サルでもわかるとかで煽ってみようかと思ったのですが、やっぱり配列数式は少し難しいです。それでもよくわかるようご説明します。

配列数式は、配列内の1つ以上の項目に対して複数の計算を実行できる数式です。

何を言っているかわかりますか?ちょっと難しいですね。名前の定義(名前ボックス)1 と同じようなことを名前を定義しないで行うようなものです。

余計にわかりにくくなりましたでしょうか。

まずは名前を付けて計算する方法のおさらいです。以下のような表があります。

各商品の単価と個数から小計を出して小計を合計する表です。

簡単ですね。セルD2に=b2*c2と数式を入力してオートフィルでセルD4までコピーしてセルD5にオートサムを入れればOKですね。

セルB2からセルB4に単価という名前を付けます。次にせるC2からセルC4に個数という名前を付けます。そしてセルD2に=個数*単価と入力し オートフィルでセルD4までコピーしてセルD5にオートサムを入れればOKですね。

これと同じことを名前を付けずに( 配列数式 で)やってみます。

セルD2からセルD4まで範囲選択します。

答えを出したいセルすべてを選択しておくことがポイントです。今回アクティブなセルはセルD2ですが、数式はあたかも名前を定義しているかのように、単価と名前のセルである、セルB2からセルB4とセルC2からセルC4の値をかけるような数式=B2:B4*C2:C4を入力します。

数式を入力し終えたらCtrlキー + Shiftキー + Enterキーを同時に叩きます。

{=B2:B4*C2:C4}という数式が入力されます。同時に選択されたセルにも同じ数式が入力されてそれぞれにきちんと正しい答えが表示されます。 Ctrlキー + Shiftキー + Enterキー で配列形式の数式が入力できます。手入力の中括弧でうまくいきません。これが配列数式です。

名前を付けた数式と同じようなものですね。違うのは名前を付けなくていい事、答えを出したいセルをはじめからすべて選択しておく、 Ctrlキー + Shiftキー + Enterキー で入力を終わる、の3点です。

難点は、配列数式の入っているセルだけを編集できないことです。今回の場合はセルD2だけ別の式に変更しようとしてもできないということです。

数式を変更しようとしてDeleteキーを叩くとアラートが表示されます。

更に、みかんと牛乳(2行目と3行目)の間に行を追加しようとする場合もうまくいきません。

更に、更に配列数式の入った式は数式の編集を終了時にも Ctrlキー + Shiftキー + Enterキーで終了しなければなりません。配列数式が分からない人が編集するとおかしくなることがあります。

どうやら、配列数式は面倒なことが多いようです。にもかかわらず覚えていただきたいのはそれなりのメリットがあるからです。今回の表では、各商品の小計を計算して、合計を出していますが、合計だけが欲しい場合D列の小計が不要な場合配列数式だと少しすっきりします。

セルD5に=SUM(B2:B4*C2:C4)という式を入力した場合エラーが発生します。

配列数式を使用しない場合は先ほどの小計の式をそれぞれ入力し加算してあげないとだめです。

大変面倒なことになります。なのでやっぱりセルD5には =SUM(B2:B4*C2:C4) と入力します。そして最後に Ctrlキー + Shiftキー + Enterキー します。

数式に中括弧がついて求めたい答えが表示されました。

数式を見ると{=SUM(単価*個数)}とイコールの外に中括弧がついておりちょっと気持ち悪いですね。気分的には=sum({ B2:B4*C2:C4 })のような気もしますが違います。

どうしてもなら=SUM({60,180,100}*{1,2,3})という式もあります。これは配列定数という物です。中括弧は手入力ですし、セル参照は出来ません。値を直接数式の中に入れます。

数式入力後に Ctrlキー + Shiftキー + Enterキー で配列数式になってよろしくやってくれます。

配列数式が扱える関数はほかにもありますがし、関数の答えが配列という場合もありますのでこの辺りの兼ね合いが配列数式のわかりづらいところかと思います。

セル範囲に名前を付けて数式を作る方なら配列数式はそう違和感なく使えるかと思います。いまいちよくわからない場合は 名前の定義(名前ボックス)1 を参照いただいたあともう一度読み直していただければお分かりいただけるかと思います。

ほとんどの場合配列数式以外の解決方法がありますのでわかる範囲でご利用いただければよいかと思います。