月別アーカイブ: 2014年7月

5行5列のシートを作成

エクセル2013(エクセル2007、エクセル2010)はワークシートのサイズは1,048,576 行、16,384 列(XFD列)となっています。エクセルの仕様と制限

エクセル2003は行数は 65,536 で、列数は 256 です。Excel の行の高さと列の幅の制限

表示する列と行を制限して5行5列のワークシート作成するので、10行8列でも32行1列でも構いません。

gyo

5行E列までしかありません。

非表示にする列の最初の列、今回の場合は6行目を行選択します。

 

 

gyo_1

エクセル2003まではまだ行も列も少なかったので行選択や列選択でドラッグし続ければ最後の行や列まで選択できましたが、さすがに1000000行超えると大変なのでキーボードショートカットを使います。Ctrlキー + Shiftキー + 下矢印キー を同時に(CtrlキーとShiftキーを押したまま下矢印キー)押します。

gyo_2

最終行の1048576行目までが選択されますので右クリックして非表示を選択します。

同様にF列を選択しCtrlキー + Shiftキー + 右矢印キー を同時に押します。

gyo_3

 

最終行16,384 列(XFD列)までが選択されるので右クリックして非表示を選択します。

gyo_4

こんなことしても何の役にも立たないですが、数式バーやスクロールバーなどを非表示にして、アプリっぽく見せることも可能です。

gyo_5

リボンを非表示にする

ワードもエクセルも共通なのですが、ノートパソコンなどでセルの表示領域少しでも増やしたり、誤操作を減らす目的等で、リボンを非表示にすることが出来ます。

下の図のような状態はoffice2013から可能になりました。(画像はexcel2013)この時は必ず全画面表示になります。

ribon_1

リボン表示のオプションをクリックします。

ribon_2

リボンを自動的に非表示にするを選択すると名前ボックス数式バーより下のみが表示されます。一番上の図の状態(excel2007、excel2010ではタブが非表示になりません)

タブの表示にするとを選択するとコマンドが表示されなくなります。この場合、タブをクリックするとそのタブのコマンドが表示されます。

タブとコマンドの表示が通常の状態です。

ribon

 

リボンが非表示の時はリボン表示のオプションのボタンの左に一時的にリボンを表示するボタンが出現します。

ribon_3

画像を図形でトリミング

文章中に画像を挿入するときに挿入した画像の一部分のみ使用したい時はトリミングします。今回は図形(オートシェープ)で画像をトリミング(切り抜き)します。

word2007には機能がありませんので、図形の背景色を図にして対応します。図形(オートシェープ)を書いて図形(オートシェープ)を選択後描画ツールの書式タブの図形のスタイルの図形の塗りつぶしをクリックします。図をクリックし任意の画像を選択します。(図はword2007)

trim2007

 

word2010、word2013もword2007と同様にできますが、新しい機能を使ってみます。

まずは画像を挿入します。画像の上でクリックし画像を選択します。

trim

図ツールの書式のサイズのトリミングの下向き三角▼をクリックします。メニューが表示されますので図形に合わせてトリミングをポイントします。図形の一覧が表示されますのでクリックで選択します。

trim_1

選択した図形の形でトリミングされました。図形を描く時のようなドラッグは必要ありません。加増がぴったりはまる大きさに勝手に調整されます。

trim_2

 

拡大縮小は画像の四隅にある白い四角のハンドルで調整できます。この場合は今見えているものが小さくなったり大きくなったりします。

trim_8

真中の犬だけを図形の背景にしてみます。再度トリミングされた画像をクリックして選択します。右クリックからトリミングを選択します。元の写真の大きさでトリミング用の黒い太線が表示されますのでドラッグして真ん中の犬だけが図形に納まるようにします。

trim_3

上下も調整します。

 

 

 

 

trim_4

現在この図形は文字列の折り返しを設定していませんので表示される部分が行内の扱いになっていますので左上に合うように勝手に調整されます。

trim_5

一番うまくいったところで画像以外をクリックし確定します。

trim_6

うまく選択すると画像のみをずらしたり図形のみをずらしたりして表示される部分を変更することが可能です。また、文字列の折り返しを設定し、文章の中にうまく配置しましょう。(下の図は、文字列の折り返しを外周に設定しています。)

trim_7

 

テキストボックスのリンク

テキストボックスや図形(オートシェープ)に文章を入れておくと、移動も自由だし、枠線の設定したり、塗りつぶしを設定出来たりとうまく使うと面白い効果が出ますよね。

うまく使えてませんが下のようなレイアウトの文章の場合、左上から順に文章を読んでいくとつながりのあるものになっています。(図はword2013)

textboxlink

 

図形(オートシェープ)一つ一つに、テキストを入力していくと文章の修正の時面倒です。上の図形の中の文章が訂正され、文字数が変更されると下の図形もずらしていかないとおかしなことになるかもしれません。(1つの図形で文章が完結していません)

テキストボックスにリンクを設定しておけば、一番初めの図形に文章を流し込めば自動でリンク先の図形に続きの文章が入ってくれます。画像はレイアウトオブジェクトの文字列の折り返し(画像の上で右クリックして表示されるアイコンをクリック)から背面や四角などを選択しドラッグで配置しておきます。次に図形を挿入します。

textboxlink_1

挿入した図形の枠線や背景色など設定しておきます。次にすべての図形に適すが入力できるようにしておきます。図形の上で右クリックしテキストの追加をクリックします。テキストボックスや吹き出しなど、初めから文字入力できるものは必要ありません。

textboxlink_2

一番初めの図形を選択します。描画ツールの書式タブのテキストのリンクの作成をクリックします。

textboxlink_3

マウスポインターの形が変わります。次の図形の上にマウスポインターを移動します。計量カップのような形状のマウスポインターが傾いて何か流し込んでいるような形状になったところでくりっくします。

textboxlink_4

これで二つの図形はリンクが張られました。1つの図形に納まらないテキストは2つ目の図形に流し込まれます。同様に2つ目の図形をクリックしリンクの作成をクリックします。

textboxlink_5

3つ目の図形の上でクリックします。これで3つの図形はりんくが張られて1つながりのように使えます。

textboxlink_6

リンクの作成できなところでクリックするとメッセージが表示されます。テキストの追加を忘れていた場合、すでに文字の入っている空でない図形をリンクしようとしたときなどに表示されます。

textboxlink_8

 

計量カップのような形状のマウスポインターが傾いていない状多でクリックしてもリンクは作成されません。

textboxlink_7

 

リンクが作際されたので、一番上の図形に文字を入力していってもかまわないのですが、別のところに普通に文字入力したものコピペするのが楽でいいと思います。

 

textboxlink_9

文章を入力しコピーしたら、一番上の図形をクリックしカーソルを表示させます。貼り付けのボタンをクリックします。

textboxlink_10

 

図形の中にテキストが流し込まれました。

 

textboxlink_11

SmartArt グラフィック

ワードでパワーポイント的な表現を取り入れる際に図形(オートシェープ)もいいですが、SmartArt グラフィックを使えばより見栄えのするものが簡単に作成できます。

smartart smartart_1

パワーポイントで作成した、没になったプレゼン。オートシェープで作成(パワーポイントにもSmartArt グラフィックはあります。)

以下のような文章を作成しました。(図はword2013)段落番号を付けて、段落の中身をShift + Enter で改行しています。

smartSmartArt グラフィックで作成しました。文字の読みやすさは落ちると思いますが、ビジュアルな訴求がつよく、理解の補助としての役割も果たしています。

smart_1

SmartArt グラフィックを挿入し、文字を入力していきます。SmartArt グラフィックは作成後、別の形状に変更できます。色も変更可能です。

SmartArt グラフィックは後で移動も可能ですが、カーソルのある位置に挿入されます。挿入タブの図のSmartArt をクリックします。SmartArt グラフィックの選択のダイアログボックスが表示されます。ダイアログボックスの左側がカテゴリになっていますので最適なものから選択します。今回は手順をSmartArt グラフィックで表現しますので手順を選択します。

smart_2

手順の上向き矢印をクリックします。

smart_3

SmartArt グラフィックには図形の数が決まっているものがあります。手順の右下の左右逆方向矢印などは相反する2つの事柄を表すのに最適なので2つの矢印以上に矢印を増やすことは出来ません。

smart_3

上向き矢印のSmartArt グラフィックが挿入されました。画面左にここに文字を入力してくださいというボックスも表示されました。今回の上向き矢印の場合はボックスに入力した文字が、SmartArt グラフィックの下から順に表示されていきます。項目と中身を入力していきます。

smart_4

 

文字入力をします。まず見出しになるものを入力します。次に見出しの内容になる文章を入力したいのですが入力欄がありません。そのままEnterキーを叩きます。

smart_5

見出しに該当するエリアが増えましたので、とりあえずそのエリアに入力します。SMARTARTツールのデザインタブのグラフィックの作成のレベル下げをクリックします。

smart_6

そうすると字下げされて、初めに入力した文章の下位の文章として挿入されます。同じ要領で入力していきます。3つ目の下位文章を入力した後に、見出しに相当する文章を入力するところがありません。構わずに、Enterキーを叩いて下位の文章として次の見出しを入力します。

smart_7

次はレベル上げをクリックすると見出し相当になります。

smart_8

SmartArtグラフィックの変更はSMARTARTツールのデザインタブのレイアウト、色の変更SmartArtのスタイルで自由に変更可能です。

smart_9

また、SmartArtグラフィックの大きさの変更は枠の角にマウスポインターを合わせて、矢印の形でドラッグで変更可能です。移動はSmartArtグラフィックをクリックし右上に表示されるレイアウトオブジェトから、文字列の折り返しを設定したのちにドラッグしてください。

smart_10

SmartArtグラフィックの文字や図形はワードで挿入する、図形(オートシェープ)ですので個別に表示を変更することが可能です。レイアウトによっては画像を挿入するエリアが用意されているものもあります。

smart_11

レイアウトやスタイルを変更して、より分かりやすい、楽しい、目立つ文章を作成することが出来ます。なお、スタイルと色はSMARTARTツールのデザインタブの右のグラフィックのリセットで初期の設定に戻ります。

smart_12

 

また、初めに書きましたが、レイアウトの種類により図形が増やせないもの、現状より少ない物を選択した状態でwordを終了した場合、表示されていない部分のテキスト画像は削除されてしまいます。

SUMPRODUCT関数2

SUMPRODUCT関数の基本的な使用法はSUMPRODUCT関数を参照してください。

下のような商品名、価格、数量がある表で価格×数量の合計を作業列を作成せず、SUMPRODUCT関数で求めることが出来ます。

sumproduct

 

セルC20に =SUMPRODUCT(B2:B19,C2:C19) と入力します。

sumproduct_2

というのをご説明しました。

今回はすべての価格×数量の合計ではなく特定の商品の価格×数量の合計をSUMPRODUCT関数で計算します。この表では同じ商品が3回ずつ登場しています。バナナだけの価格×数量の合計を計算してみます。

セルC20に =SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19) と入力します。

sumproduct_5

説明しているサイトによっては、 =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19,C2:C19) や =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19*C2:C19) のような式もあります。

SUMPRODUCT関数は引数の配列を対応する項目の積を計算し合計する関数でした。

=SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19)  は
(A2:A19=”バナナ”)*1 と
B2:B19 と
C2:C19 の3つ引数があります。セルA2、B2、C2の値の積を出して、次にセルA3、B3、C3の積を出してとなりますが、第1引数が(A2:A19=”バナナ”)*1と謎です。

計算式のセル参照という項目で説明しましたが、セルD2に =B2:B19*C2:C19 という式を入れた場合入力されたセルD3と同じ行のセルB3とC3の積が計算されます。

sumproduct_6

 

A列は数値ではありませんが、=A2:A19 という式をセルE2に入れると2行目のバナナが答えとして返ってきます。先ほどの計算式と同じ理屈ですね。

 

sumproduct_6-1

 

次に、 =(A2:A19=”バナナ”) という式をセルF2に入れてみます。カッコは無くても今回は大丈夫ですが、さらに先に行ったとき必要です。これも同じように2行目のセルA2の値は、”バナナ”という文字列と等しいですか?等しくないですか?。という式になります。何々と等しいですか、何々より大きいですか、などのIF関数の条件式に使うような数式はTRUEかFALSEが返ってきます。

sumproduct_7

そして、TUREとFALSEにも書きましたが、TRUEに四則計算をしてやると1という数値として扱うことができます。FALSEは0になります。=(A2:A19=”バナナ”)*1 はTRUE×1なので1が返ってきます。

sumproduct_8

 

=SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19)  は第一引数が1か0になります。A列がバナナの時は1、バナナでない時は0になりますので、1*12*3(セルA2、B2、C2の値の積)、0*250*5(セルA3、B3、C3の積)と順に積を計算し合計してくれるというわけです。

他のサイトで見たという、SUMPRODUCT関数の使い方も意味は同じです。*1とせずに次の配列を掛けている =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19,C2:C19) は無駄もなくかっこいいですが、関数ボックスで見ると引数が2つなので、関数の意味をよく理解できていないうちは、なぜ範囲と範囲を掛けているのか理解が難しいかもしれません。

sumproduct_9

 

TUREかFALSEかの真偽の値に、価格(数値)を掛けることで真偽のTRUE、FALSEを1と0の数値として扱っています。

慣れるまでは×1で数値にした物を別に分けておくと何をしているのか思い出しやすいと思います。

sumproduct_10

SUMPRODUCT関数

SUMPRODUCT関数はとっても便利ですが、ちょっと使い方が難しいです。

SUMPRODUCT関数

引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。
SUMPRODUCT(配列 1, [配列 2], [配列 3], …)

以上Excel2013 SUMPRODUCT関数のヘルプより

下のような商品名、価格、数量がある表で価格×数量の合計を出してみます。

sumproduct

 

D列に行ごとの価格と数量を掛けた数値をだして、SUM関数で合計することで計算できます。

sumproduct_1

うまくいきました。今回必要なのはセルD20の合計だけで、各商品の価格×数量は必要ないといった場合、D列自体(セルD20を除く)を作業列と言ったりします。

今回の合計を作業列なしで計算できるのがSUMPRODUCT関数です。セルC20に =SUMPRODUCT(B2:B19,C2:C19) と入力します。

sumproduct_2

 

引用したヘルプにもあるように(B2:B19,C2:C19) の要素間の積を計算します。セルB2*C2、セルB3*C3、セルB4*C4、と順に第1引数と第2引数のそれぞれを掛け合わせ、その和を返すので最後にすべてを加算してくれます。これなら作業列なしで計算できます。

また、引数の配列は2つ以上(1つは必須)指定できますので、以下のように利益率の列を追加したものでも価格×数量×利益率も同じ要領で計算できます。D列に利益率を記入します。セルD20に =SUMPRODUCT(B2:B19,C2:C19,D2:D19) と入力します。セルB2*C2*D2、セルB3*C3*D3、セルB4*C4*D4、と順に積を計算し、最後に加算します。

sumproduct_3

 

それ以外にも、引数の配列は2列でも大丈夫です。表の形が変わっていますのでご注意ください。価格が2種類あってそれぞれに売れた数量があります。B列とD列を掛けたものとC列とE列を掛けたものの合計を出す場合は =SUMPRODUCT(B2:C19,D2:E19) と入力します。sumproduct_4

 

これも同じようにセルB2*D2、セルC2*E2、セルB3*D3、セルC3*E3、と順に積を求めて和を算出してくれます。これに価格と、価格2の利益率も必要ならF列に価格の利益率G列に価格2の利益率を記入し、第3引数として F2:G19 を入れてあげれば計算できますので試してみてください。

SUMPRODUCT関数には、他にも便利な使い方がありますので別途ご紹介する予定です。

TRUEとFALSE

エクセルを使用しているとたまにTRUEやFALSEという用語が出てきます。TRUEは真(真実)である、FALSEは偽(真実ではない)とかの説明があったりします。また、条件を満たす場合を真(TRUE)条件を満たさない場合を偽(FALSE)です、という説明もあります。

また、TRUEは数値の1(ゼロ以外の数値)でFALSEは数値の0、という説明もあります。

まず、真と偽(条件を満たすか満たさないか)を確認します。下のような表があります。(図はexcel2013)

true

セルC2に =(b2=”男”) という式を入力します。見慣れない形かもしれませんが (b2=”男”)  の小かっこの中のイコールはイコールの左と右が同じものかどうかを比較しています(小かっこは見た目少しでもわかりやすいように入れています。今回の場合は小かっこなしでも同じです。)。IF関数の第1引数で使われる形です。式の左のイコールは数式を入力するときは必ず一番最初に出てきます。(配列数式は除きます)

結局、式の意味は「セルB2は男という文字ですか?」

ということになります。今回はセルB2の値は男なので一致していますので条件を満たしている(真)ということでTRUEと表示されます。

true_1

セルC2の式を下にコピーしておきます。セルB4は女ですから、結果は条件を満たしていない(偽)のでFALSEが表示されます。

true_2

キーから値を抽出 VLOOKUP関数の第4引数の設定で完全一致で検索するか近似値を使うかを指定できます。完全一致の場合は段4引数はFALSEまたは、数値の0を指定します。近似値の場合は省略するか、TRUEか数値の1(ゼロ以外の数値)を指定します。

次は、TRUEは数値の1(ゼロ以外の数値)でFALSEは数値の0というのを確認します。

TRUEは数値の1(ゼロ以外の数値)でFALSEは数値の0なので、セルC5に縦計を出してみます。セルC5に =SUM(C2:C4) という式を入力します。

true_3

TURE+TRUE+FALSEで合計いくつになるでしょうか

true_4

答えは0です。TURE+TRUE+FALSEは1+1+0で2のような気がしますが、ゼロです。ところが、SUM関数を使用せずに加算の+を使用すると1+1+0で2となります。

true_5

四則計算(TRUE*1やfalse-1など)の場合はTRUEは1,FALSEは0として扱われます。一旦数値となったものは関数の中でも数値として扱うことが出来ます。

セルC2の =(b2=”男”)  を =(b2=”男”) +0 に変更します。TRUEかFALSEに0を足します。0を足しても値は変わりませんが、今度は表示が1と0に変更されました。

true_6

そしてSUM関数の方も数値として扱われているので2と表示されるようになりました。

では逆も確かめてみます。セルA2に数値の0セルA3に数値の1を入力しました。0がFALSEで1がTRUEか確認します。セルB2には =(A2=TRUE) と入力しセルA2の値がTRUEかどうか確認します。

セルB2もB3も両方FALSEが返ってきます。数値の0も1も両方ともTRUEではないといっています。

true_7

if関数 おまけ(and関数 or関数)にAND関数について説明があります。AND関数に数値の0と1を入れてみます。AND関数は引数(カッコ)の中が真ならTRUEを返し、偽ならFALSEを返します。引数は(カッコの中)は数値の0と1です。=and(0) は数値の0は真ですか偽ですか =and(1) は数値の1は真ですか偽ですかという意味になります。

true_8

数値の0は偽ということでFASLEが返ってきました。なのでセルB2はFALSEになります。数値の1は真なのでTRUEが返ってきますのでセルB3はTRUEになりました。

true_9

 

ややこしく考えないでそんなものだと思ってください。このような考え方が出来れば、関数の使い方が広がることがあります。実例はまたの機会にご紹介します。

(矩形に)範囲選択

マイクロソフトワードで範囲選択方法をまとめておきます。

■基本はドラッグですね。
マウスの左ボタンを押さえたまま文字の上をなぞって、目的の文字列が選択されたら、左ボタンを放す。(図はword2013)

select

選択されたところの背景色が変わっていますね。

■1行単位で選択する行選択。
左余白にマウスポインターを移動するとマウスポインターの形状が白い矢印で先が右上を指す状態になります。この時クリックすると1行選択できます。

select_1

クリックの代わりにドラッグすれば複数行選択になります。select_2

ダブルクリックすると段落が選択されます。(マウスポインターのあるところの段落)

トリプルクリックで全選択になります。(すべてのページが選択されます。)または、Ctrlキー + Aキーでも全選択です。

■クリックからShift + クリックまでの間
段落をまたいだり、ページをまたいだりする場合、ドラッグだと行き過ぎたり、戻りすぎたりしてなかなかうまく選択できない場合があります。範囲選択の開始の場所でクリックしてカーソルを表示させます。次に範囲選択の終了場所にマウスポインター(アイビーム)を移動し、Shiftキーを押したまま、クリックします。

■その他の選択
文字の上でダブルクリックすると単語が選択されます。厳密には単語単位ならない場合もあるかもしれません。トリプルクリックで段落選択になります。

■矩形に選択
最後になりましたが、矩形の範囲を選択します。Altキーを押したまま斜めにドラッグすると四角く範囲選択可能です。

select_4

この選択はちょっと特殊で、削除や、書式の設定(うまくいくときが大半)が選択範囲にきっちり反映しないことがあります。コピーようだと思ってください。上の例では便利さがわかりませんが、以下のように左の費目だけを再利用したいというような場合に便利です。

select_5

エクセル(ワード)ステータスバーの色の変更

エクセルステータスバー 色の変更で検索されていたのでちょっと説明

エクセル(ワード)のステータスバーだけの色の変更はできません。

もしかしたら、xmlファイルでカスタマイズできるかもしれません。EXCEL.VisualElementsManifest.xmlあたりが怪しいのですが、うまくいきませんでした。(情報お持ちの方ご教授ください。)

下の図が、エクセル2013の通常の状態です。

color0

 

フィルタブをクリックし左のアカウントをクリックします。Officeテーマをクリックすると白と薄い灰色と濃い灰色が選択できます。上の状態が白です。

color_acount

 

薄い灰色を選択した状態が下です。タイトルバーや行見出し、列見出などが薄い灰色に変わっています。

color1

濃い灰色を選択するとタイトルバーや行見出し、列見出し以外にステータスバーの色も濃い灰色になっています。

color2

 

ステータスバーの色のみを変更することは出来ませんでしたが、いつもの緑以外のステータスバーにした方が、ステータスバーに表示されるコントラストが強くなり、視覚に障害をお持ちの場合、読みやすくなるかもしれませんね。