カテゴリー別アーカイブ: マイクロソフトエクセル

名前の定義(名前ボックス)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文字まで。大文字小文字は区別されません。参考数式内の名前

R1C1参照形式

通常あまりかかわりになることはないと思いますが、R1C1参照形式というものがあります。

通常エクセルでは列名はA,B,Cという感じでアルファベットで表示されていますが、 R1C1参照形式 というものを使えば列名が数値になります。

列番号が数値

ではどのようなときに使用するかですが、使用する局面は「ほぼない」といってもいいと思います。

VBAと呼ばれるスクリプトの中では使われることがあるようです。何列先とか今の列から5列分とかを指定するときアルファベットの場合は指折り数えないとわかりづらいですが、数値だと計算で簡単な場合があります。

特定の関数で R1C1参照形式 を指定できる場合もあるようです。

オプションの数式の数式の処理で R1C1参照形式 を使用するにチェックを入れると列番号が数値になります。

RはRow(行)でCはColumn(列)になるのでセルA1はR1C1で、セルB1はR1C2となり列と行の純が逆になります。更に通常の相対参照は数式の入っているセルからのオフセット位置(差し引き)になります。

セルE3からセルA1への参照は2行上の4列左という意味で=R[-2]C[-4]のようになります。計算式より上、左のセルは負の数となります。また下、右は正の数となります。同一列や、同一行の場合は数値は指定せず括弧もなくなります。

F4キーを使って絶対参照にした場合はかっこがなくなり=R1C1の様なセル番地の表記になります。

ちなみに R1C1の読み方としましては私は、「あーるいちしーいち」と発声していますが、VBAの中でFormulaR1C1プロパティというものがありこの場合は「フォーミュラーアールワンシーワン」ということもあり「 アールワンシーワン 」が正解かもしれません。

列が逆 行見出しが右

列見出しが逆といいましょうか、裏返っていると申しましょうかちょっと変わった表示です。

通常の使い道はよくわかりませんが、(横書き右から左の言語の場合?)列見出しが右に来て右から左に列見出しがA列B列C列D列E列とすることができます。

タブの並び順は同じですがいろいろなところが左右逆になっています。

というかこうなってしまっている場合の直し方ですね。

ファイルタブをクリックしてオプションをクリックします。

Excelのオプションが表示されますので詳細設定をクリックします。次のシートで作業するときの表設定のシートを右から左へ表示するのチェックを外してOKボタンをクリックします。

これで通常のエクセルと同じに戻ります。

初めて見た時は結構驚きました。

 

タブの表示

マイクロソフトワードでもエクセルでも共通なのですが、さらに言えばエクスプローラーでも共通ですがタブを表示してリボンを非表示にすることが可能です。

エクスプローラーでリボン表示

エクスプローラーでタブのみ表示(リボンの非表示)

マイクロソフトエクセルでリボン表示

マイクロソフトエクセルでタブのみ表示(リボンの非表示)

マイクロソフトワードでリボン表示

マイクロソフトワードでタブのみ表示(リボンの非表示)

エクセルのページでリボンを非表示にするでご紹介しているのですがエクスプローラーは右上の三角のような(^)ボタンをクリックすることで、ワードエクセルは右上の閉じるボタンの3つ左のリボンの表示オプションで変更できます。

または、Ctrlキーを押したままF1キーを叩くとリボンの表示非表示を変更することができます。

ワードやエクセルを初心者にお教えしているときに勝手にリボンが非表示になってしまったという質問が多く、『おそらく、Ctrlキーを押したままF1キーを押してしまった』のでしょうね。と説明しCtrlキー+F1キーで元に戻しておりました。

でも、Ctrlキーを押したままF1キーを押すなんてことがそうそうあるものだろうか?と思ってもいました。

アクティブなタブの上でダブルクリックすることでリボンが非表示になります。リボンが非表示の時いずれかのタブ(ファイル以外)をダブルクリックするとリボンが表示されます。

知りませんでした。と言う事で基本・共通操作、マイクロソフトエクセル、マイクロソフトワードに同じ記事を載せておきます。

スクロールバーを非表示にする

個人的にはたまに使うのですがエクセルのスクールバーを非表示にすることができます。少しだけ画面を広く使えます。

垂直方向のスクロールはマウスのスクロールホイールを使用することが多いのでなくてもほとんど困りません。

水平方向は矢印キーで代用できますし(縦方向も矢印キー使用可)、横スクロールが発生するエクセル表はそもそも使いにくいのでスクロールバーがあってもそう便利に感じません。(個人的感想ですが)

なのでエクセルのスクロールバーは無くても不自由しない場合が多いです。

ファイルタブのオプションをクリックしExcelのオプションの詳細設定をクリックします。

次のブックで作業するときの表示設定の水平スクロールバーを表示すると垂直スクロールバーを表示するのチェックを外します。

OKボタンをクリックします。

スクロールバーが非表示になりました。これは選択する際に『次のブックで作業するときの表示設定』とありましたようにブック単位でスクロールバーが非表示になります。同じブック内のシートすべてに影響されます。

1行を2行に分割

エクセル 1行を2行、エクセル セル 分割 上下、エクセル セル 分割 行

などのキーワードで検索していただいているようです。結構の方がこれらのキーワードでこのサイトをご覧いただいているようですので今回は、ずばり

エクセルで1行を2行に分割

の記事を書いてみます。

ワードの表では1行を2行に分割することは簡単です。表の1行の高さで2行に分割を参照してください。

エクセルで1行を2行に分割することはできません。

がーん!
出来ないものは出来ないですっ。

ではどうしようもありませんので代替案を提示させていただきます。

おそらくなんかの都合で上手のようにセルA1を2行分に分割したいのでしょうがこれは無理です。分割したセルにセル番地が振れません。セルA1上とかセルA1下とかでしょうか。。。

セルを参照する式が作れませんね。

推測するに

の様に生年月日に1行に対して性別と男・女の2行が必要になってくるということなのでしょうね。現住所の1行に対して右は3行あるよってことでしょうか。

上図は枠線を消すの方法でエクセル枠線を消しています。

答えはセル結合して中央揃えですね。

面倒ですが、1行を二行に分割したい場合は同じ行のほかの列を2行を1行にセル結合してあげれば同じものになるのではないでしょうか。

縦のセル結合を赤枠で囲っています。計算式等必要ないならワードにも表機能がありますのでそちらを使うことを検討されてもいいかもしれません。

エクセル 蛍光ペン

エクセル 蛍光ペンの検索で訪れていただいている方が多いようです。しかし残念ながら表示されるのはワードの蛍光ペンの記事です。

エクセルの蛍光ペンの記事ですが今までありませんでしたので今回新たに記事を追加しました。

が、エクセルには蛍光ペンの機能はないようです。

ご覧の様にワードの場合フォントの文字色の左が蛍光ペンですがエクセルの場合は、セルの塗りつぶしになっています。

そうです。エクセルには蛍光ペンがないので記事がありませんでした。

でも蛍光ペンというは文字の上に蛍光色らしい色があって下の文字が読めればいいので代替策を考えてみます。

文字の上を図形で多い不透明度何とかすればそれらしいものになると思います。

ではまず挿入タブの図から図形をクリックし正方形/長方形を選択し蛍光ペンの効果を付けてところに描画します。

このままでは文字が見えませんし色も違います。

まずは描画ツールの書式タブの部系のスタイルの図形の塗りつぶしの塗りつぶしの色からそれらしい色を選択します。

このままでは相変わらず下の文字が読めませんので透過性を40%くらいに設定します。

まだ図形の枠線が残っていますので描画ツールの書式タブの部系のスタイルの図形の枠線から枠線なしを選択します。

選択を解除すればエクセルで蛍光ペン的な物の出来上がりです。

オフィスの2010まではクリップオーガナイザーがあったので自分で作った図形を登録できたのですが、オフィス2013からクリップ オーガナイザーの廃止となりましたので毎回自作することになろうかと思います。

重複データの削除

アクセスを見ておりますと、重複データで検索していただいてこちらに来てくださる方が多いようです。重複行の合算や削除 (フィルタ ピボットテーブル)で重複データの削除方法をご紹介しているのですが、ほかにも簡単な方法がありますのでご紹介します。

下の図は黄色と青の同じ背景色のデータは重複データです。まったく同じ内容となっています。もちろんわかりやすいように色を付けているだけなので重複データがどれかわからない状況だと思ってください。

表の中をクリックして選択します。データタブのデータツールの重複の削除をクリックします。

重複の削除のダイアログボックスが表示されます。スクロールで隠れていますが列の見出しが列挙されており、チェックが入っています。これらはAND条件で選択されていると思ってください。名前が同じでふりがなも同じでさらにアドレスも同じで更に…というようにチェックが入っている項目すべてが同じものを重複とみなすということです。

OKボタンをクリックします。

削除したデータ件数と残ったデータ件数を表示してくれています。OKボタンをクリックします。

後ろにある重複データが削除され自動で上に詰められました。

上の画像にもありますように一意である列が確定する場合、例えば同姓同名はあり得る。住所変更前と後のデータがあるかもしれない。でもメールアドレスは、絶対重複しないというデータであれば重複の削除のダイアログボックスでメールアドレスだけをチェックしておけばOKです。

削除のダイアログボックスで列の選択を調整すればいろいろと使えます。

簡単 データ移動(コピーも)

単純な移動はセルの値の移動を参照してください。

データを移動したいセルを選択します。複数セルでも構いません。選択したセルの周りの太い枠線の上にマウスポインターを合わせます。マウスポインターの形が上下の矢印になります。

その状態でドラッグするとデータ(数式、書式)の移動になります。

ドロップ(マウスのボタンを放します。)します。

CTRLキーを押したままドラッグするとコピーになります。

移動先にデータがあった場合は上書きされてしまいます。

上書きされてしまうといけない場合はいったん移動(コピー)されてくるデータ分を空けておくのがいいです。

でもこれはちょっと面倒です。移動(コピー)先のデータを消すことなく移動(コピー)したい場合は移動(コピー)先でShiftキーを押して消されてしまうデータの移動先を決定します。

緑の線が枠ではなくて一本の線になり、端が少し伸びてアルファベットのHのような形状になります。

列を挿入する場合は工の時の様に上下の端が横に伸びます。

非常に便利です。

ちなみにCtrl+Shifht+ドラッグでコピーしながら挿入も可能です。

セルの最適幅

2013年末からOffice ワード エクセルを始めていますが、今更ながら基本的な操作をご紹介していなかったりしています。もしかしたら何かのついでに触れているかもしれませんが。

今回はセルの最適幅についてです。

#####と表示されているセルがあります。これはセルの幅より長い桁数の数値が入力されている場合や、日付や時刻で負の数値が入力されている場合に表示されます。

セルの幅を広げれば解消されますのでセルの幅を広げます。

幅を変更したい列の見出しの右側にマウスポインターを移動します。列と列の境目にマウスポインターを合わせ、左右に引っ張るような矢印に変わったらドラッグします。

セルの幅が広がったらドラッグを中止します(マウスの左ボタンを放す)。

C列の####の表示はなくなり、数値が表示されました。列幅に数値が収まらない場合に####のような表示になりますが文字列の場合は表示が切れていてもわかりません。

また、表が縦長だったりして目視できる範囲外に大きな数値があった場合などドラッグでは都合が悪いこともあります。セルC1は『県内総生産』という文字が見えていますが、実際は『県内総生産(名目)単位:100万円』なので切れてしまっています。

マウスポインターをC列とD列の間に置き左右の矢印になったらドラッグではなくダブルクリックします。そうするとセル幅は最大の文字幅(数値の桁数)に合わせて広がってくれます。(セルの最適幅)

見出しの幅が広すぎて少し不格好です。セル幅に収まらない文字列の表示を参照してください。セルの書式設定の配置で『縮小して全体を表示する』か『折り返して全体を表示する』でも構いません。

複数の列に対して最適幅を設定する場合は、複数列を選択し複数選択した列の境目でマウスポインターが左右の矢印になったらダブルクリックします。

選択範囲内の列の境目で左右の矢印になるところだったら、どこでもいいのでダブルクリック。

すべての列が最大の文字幅(数値のけた)が切れない幅(最適幅)になりました。この時ダブルクリックではなくドラッグすると選択列すべてドラッグされた列幅と同じ幅になります。