inst_yamamoto のすべての投稿

セルを保護する

苦労して作成した数式を誤って削除してしまったり、数式を勝手に書き換えられたくない場合は、シートの保護を使うと便利です。

校閲タブの保護にシートの保護というボタンがあります。

クリックするとシートの保護のダイアログボックスが開きます。

シートの保護を解除するためのパスワードが入力できたり、シートの保護を掛けた状態でできることを選択できたりします。これらを設定したのちにOKボタンをクリックすることでシートが保護され、値( 数式 )の変更ができなくしたり書式を変更できなくしたりと色々と可能です。

勘違いしやすいのは、初期の設定ではシートの保護はシート内のすべてのセルに対して有効だということです。これは、初期設定ではすべてのセルがロックされており、シートの保護のボタンでロックが有効になると思ってください。

特定のセルのみを保護(変更をさせたくない)する場合は、一旦すべてのセルのロックを外してから保護したいセルのみにロックをかけてからシートの保護のボタンをクリックします

全セル選択(Ctrlキー + Aキー)をします。

セルの書式設定(Ctrlキー + 1キー)を開きます。保護のタブをクリックします。

ロックのチェックを外してOKボタンをクリックします。初期設定ではここにチェックが入っています。

次に保護をかけたいセルを選択します。 セルの選択 を参照してください。CtrlキーやShiftキーをうまく使ってセルを選択してください。

セルを選択した状態のままセルの書式設定の保護タブを開いてロックのチェックを入れてください。そしてOKボタンをクリックします。

最後に校閲タブの保護のシートの保護のボタンをクリックしてシートの保護のオプションを選択してシート(セル)の保護の完成です。ワークシートを保護する によれば保護をしないセルを選択してチェックを外すとあります。操作が少なく合理的ですのでどちらか好きな方を選択してください。

セルの書式設定の保護タブにあるロックは保護されるかどうかですが、その下の表示しないはシートの保護を掛けた際に数式バーに値(数式)を表示するかしないかになります。表示しないにチェックを入れるとセルを選択した際、数式バーには何も表示されなくなってしまいます。

シートの保護のダイアグボックスではシートの保護を解除するためのパスワードを設定できます。パスワードは設定しなくても構いません。上記マイクロソフトのリンク先では 長さは 8 文字以上にする必要があり、理想は 14 文字以上のパス フレーズです。 とありますが1文字でも通るようです。これは強固なパスワードにするためのようです。

シートの保護のダイアグボックスのオプションについては読めばそれなりに理解できると思いますが、上記リンク先に詳しくあります。

保存されていない文章の回復

ふいにPCの電源が落ちてしまったり、再起動時にワード、エクセルを強制終了したりして作成中のファイルを保存せずに終了してしまって困ることありますよね。ありませんか。

ある条件下では保存していないファイルも回復することが可能です。

ファイルタブをクリックします。開くをクリックします右下の保存されていない文章の回復をクリックします。

回復可能なファイルの一覧が表示されますので選択しOKボタンをクリックします。

一般的には、
C:\Users\ここはユーザー名\AppData\Local\Microsoft\Office\UnsavedFiles
のフォルダに保存されています。AppDataフォルダは隠しファイル扱いになっているので直接ファイルを見る場合は隠しファイルを表示する設定にする必要があります。

ただしこれはオプションの保存の文章の保存で次の間隔で自動回復用データを保存する(保存しないで終了する場合、最後に自動回復されたバージョンを残す)にチェックが入っていた場合、データが一時的に保存されてます。もしこれらにチェックが入っていない場合は、自動保存されていませんので回復することはできません

名前の定義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 を参照いただいたあともう一度読み直していただければお分かりいただけるかと思います。

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

エクセル 蛍光ペン 2019

エクセル 蛍光ペンで画像をそれらしく見せる方法をご説明しました。エクセルの2019では蛍光ペンの機能があります。蛍光ペンとなっていますが、それらしい色を選んべばよりいっそう蛍光ペンに見えますね。

office365では以前からあったと思いますが。

まず描画タブを表示します。ファイルタブをクリックします。

オプションをクリックします。リボンのユーザー設定をクリックします。

Excelのオプションウィンドウの右側の描画にチェックを入れます。OKボタンをクリックします。描画タブが表示されました。

描画タブをクリックるするとペンがあります。その中から蛍光ペンを選択します。

ツールの描画が選択されていない場合はクリックしましす。マウスカーソルがペン先になります。

ドラッグすれば蛍光ペンで文字の上をなぞったようになります。

引き続きドラッグで蛍光ペン (ペン・鉛筆) が使用できます。蛍光ペンはフリーハンドで使用できますので一気にドラッグするのが良いようです。

蛍光ペン (ペン・鉛筆) を終わるときは左の描画をクリックして描画の選択を解除します。または、Escキーを叩きます。

蛍光ペン(ペン・鉛筆)は描画後編集することも可能です。 蛍光ペン(ペン・鉛筆) を使用していない状態で(通常のマウスポインタの形状の時)クリックすると図形を選択したのと同じような状態になります。

端にハンドルが表示されますのでドラッグで大きさを変更することが可能です。

また、 蛍光ペン(ペン・鉛筆) はコピーペーストが可能ですしドラッグで移動も可能です。

蛍光ペン(ペン・鉛筆) は 太さ色も変更できます。ペンの 蛍光ペン(ペン・鉛筆) が選択されている状態で再度 蛍光ペン(ペン・鉛筆) をクリックします。メニューが表示されますので色や太さを変更します。

描画後であっても右クリックしてインクの設定をクリックすることで色、透明度幅など変更することができます。

不要になった 蛍光ペン(ペン・鉛筆) 消しゴムツールを選択してクリックすると消すことが可能です。通常に、クリックして選択後Deleteキーでも削除できます。また、複数の 蛍光ペン(ペン・鉛筆) を削除する場合は投げ縄選択で大きくドラッグして複数選択後Deleteキーで削除することも可能です。

エクセル蛍光ペンで検索してくださる方が多かったのと、エクセル2019(オフィス365)ではエクセル蛍光ペンの機能がありますのでご紹介いたしました。エクセル2016以前の方は エクセル 蛍光ペン をご参照ください。

COUNTA関数

範囲内の空白ではないセルの個数を調べる関数。オートサムボタンの横関数 COUNT関数 MAX関数 MIN関数のCOUNT関数は数値のセルを調べます。

文字、数値、エラーメッセージ、数式、空白文字を数えます。空白以外をカウントするの方が正確かもしれません。

セルE1 には=COUNT(B1:B8)が入力されています。COUNT関数は数値だけを数えますので数値の123と日付の2月20日とゼロの3つがカウントされています。日付は文字列に見えますがデータとしては数値ですのでカウントされています

セルE2には=COUNTA(B1:B8)が入力されています。COUNTA関数は空白以外を数えます。空白文字のセルB6には=””が入力されていますのでカウントされます。

上図はセルに数式を表示するの方法で数式を表示しています。空白以外は何かしらの値が入っていますね。

COUNTA関数はデータが入力されている件数を調べるために使われることが多いです。また、ほかの関数の中で使われることもよくあります。

名前の定義(名前ボックス)1

どこかでご説明したとばっかり思い込んでいましたが、改めて見直すとそれらしいページのタイトルがありませんでしたので改めてエクセルのセル範囲につける名前についてご説明します。

エクセルではセルを表す場合にセル番地を使います。A1とか、A1:B5のような感じです。 R1C1参照形式という方法もありますが。

これとは別にセルに名前を付けることができます。

名前を付け方ですがセルまたはセル範囲(複数セル)を選択します。名前ボックスをクリックします。名前を入力します。

セルB2に税率という名前が付きました。これで数式でセルを名前で参照することができるようになります。

オートフィルで数式をコピーする際、セルに名前を付けておくと通常F4キーで絶対参照にするところをF4キーを使わなくてもよくなります

セル範囲(複数セル)に対して名前を付けることも可能です。

セルB3からセルB5に価格という名前を付けました。税額は価格×税率となりますのでセルC3の式を=B3*税率から=価格×税率に変更します。

セルB3もセルB4もセルB5もすべて価格という名前がついていますので、どのセルの値が計算対象か不安になるかもしれません。今回の 計算対象は同じ列の値になりますので正しい計算結果となります。

下図はセルB2からセルB4までに単価、セルC2からセルC4までに数量という名前を付けてあります。セルD2に=単価*数量という式を入力してオートフィルで下にコピーしてあります。

それぞれ同じ列の値をかけているので正しい答えが表示されています。また、適切な名前を付けた場合数式を見た際に数式の意味が把握しやすくなる事もあります。

リストから入力の際もセル範囲に名前を付けておくと便利です。

特に意味はありませんが、セルF2はプルダウンで値(単価から)選択できるようになりました。 エクセル2007ではシートを跨いでリストから入力する際は、名前を付けるしかありませんでした。

名前の管理は数式タブの定義された名前から行えます。数式を作成する際も数式の定義された名前から入力できます。もちろん直接文字入力しても構いません。

数式の中で名前を使いたい時は直接名前を入力してもいいですが、数式タブの定義された名前から数式で使用をクリックします。一覧が表示されますので使いたい名前をクリックします。

名前を付けるときの注意点ですが、同じブックの中で同じ名前は使用できません(例外あり)。また、名前の一文字目に数字は使えません。名前の1文字目は文字(多分アルファベットと日本語の多くの文字)、 下線 (_)、円記号 (¥) しか使用できません。それ以降は文字、数値、ピリオド、および下線を使用できます。大文字と小文字の “C”、”c”、”R”、”r” を1文字目にして2文字目意向を数値にすることはできないようです。おそらく R1C1参照形式と競合するからでしょう。同じ理由からA1などのセル番地を表すものも使用できません。エクセル2016では列番号はXFDまでありますので、NO1という名前はセル番地と競合するので使用できません。注意が必要です。文字数は255文字まで。大文字小文字は区別されません。参考数式内の名前