■ 長文を一発で改行

長文を任意の位置又はセル幅で改行するときは、メニューバーの編集>フィル>文字の割付
昔やってたのに殆ど使わないから忘れてた。やっと思い出した。とっても簡単だった。

■ 面白い表示形式

セルに123456789 と入力し、位取りの「,」を設定すると。。
表示は 123,456,789 となりますね。

セルの表示形式をユーザー設定で #,##,
と設定するとどうなるでしょう。

千円未満を四捨五入して123,457
と表示しますよね。

これを #,##,”千円”
とやるとどうなるでしょう?
123,457千円 と表示されます。

表示は千円単位で、しかし結果は1円単位でやりたい場合は、これ使えせん? (^^♪

 

■ 2つのセルに入っている文字を結合して1つの文字列にするには

例えばセルA1に「かめ」 セルB2に「はうす」と入力されているとします。
これを、「かめはすう」と一つのセルに表示したい。
こんな時は、セルC1を作業セルとし、下記のように数式を入れればOK。

=A1&B1

これだけ。
数式ではなく、値として使うなら、セルC1を右クリック>コピー>貼り付けたいセルを右クリック>形式を選択して貼り付け>値にチェックを入れる>エンター

 

■ 左から---(住所録で・・・)

住所録で、愛知県とか三重県から始まる住所がありますが、これ要らない。
愛知県名古屋市中区栄二丁目
という文字列がセルA1に入っているとします。
この文字列から「愛知県」という文字を外して「名古屋市中区栄二丁目」だけの文字列にしたい。

この場合セルB1を作業セルとして次の計算式を入力する。
A列に複数文字列があるなら下方向へコピーすればいい。

=RIGHT(A1,LEN(A1)-3)   ’3文字分引く(全角、半角を問わず)
R1C1形式なら
=RIGHT(RC[-1],LEN(RC[-1])-3)

またはこんな計算式もある。
=RIGHT(A1,LEN(A1)-FIND(("県"),A1))
R1C1形式なら
=RIGHT(RC[-1],LEN(RC[-1])-FIND(("県"),RC[-1])) 

何で左からなのにRIGHTを使うんだろ?

 

■ Excelのツールバー (Excel2002)

 最近Excelのツールバーが突然純正に戻ってる。
自分の使い勝手のいいように「標準」と「書式」のツールバーを触りまくってせっかく作ってあったのに。

Excel2002のツールバーの設定は、Excel10.xlb と言うファイルに保存されているようだ。
通常、設定が変になったらこの Excel10.xlb と言う名のファイルを削除して、再度Excelを立ち上げると、純正のツールバーが再作成される。

じゃあ、これを逆手に取ればいい訳だ。
Excelを開く。>自分流にツールバーをカスタマイズする。>名前を付けて保存>○○Excel10.xlb と言う名前にする。

あとはもし、いつものツールバーが突然消えたら、
その時のExcel10.xlbというファイルを削除し、自分で作った○○Xcel10.xlb と言うファイルを持ってきて、規定のフォルダに収めたら、頭の○○を削除すればさっきまで使ってた自分流のツールバーが復活すると言う訳。

 

■ ふりがなを関数で Excel2002

 

かめ家のお世話になってる町内のごみ当番の一覧表です。
かみさんが、読みにくいから、誰でも読めるように「振り仮名」振ってよって。 あんた簡単に言うねえ。 今使ってるのはExcel2002。

平仮名がいいのか、カタカナがいいのか、半角カタカナがいいのか、わしには解りません。 え〜い、面倒だ。3コ作っちゃえ。
3行目に「順番」「氏名」の順に入っているので、その横に「しめい」「シメイ」「シメイ(半角)」と列の項目にタイトルを書いて。


1:面倒なので列2を全て選択し、(列ボタン2をクリック)メニューバーから、書式>振り仮名>設定>ふりがなのタブを開いて>種類の項目で「ひらがな(H)」にチェックを入れる。>OKで閉じる。
 そうしておいて、列2の「浜田」さんの右横のセルに、関数をぶち込みます。
 =PHONETIC(RC[-1]) A1形式なら、=PHONETIC(B4) と。 結果はどうなりましたか?「はまだ」と表示されましたね。
後は下へ複写します。 そのまま範囲が選択されて青く反転しているままの状態で、右クリック>コピー>右クリック>形式を選択して貼り付け>値>「Esc」キーを押してコピーモードをクリアします。

2:次に列2のボタンをクリックして、メニューバーから、書式>振り仮名>設定>ふりがなのタブを開いて>種類の項目で「全角カタカナ(K)」にチェックを入れて>OKで閉じる。
 そうしておいて列4に関数をぶち込みます。
 =PHONETIC(RC[-2])  A1形式なら=PHONETIC(B4) と。 結果はどうなりました?「ハマダ」と表示されましたね。
以下同文です。

3:次に列2のボタンをクリックして同様に「半角カタカナ(T)」を選んで同様にすれば、表示は半角カタカナになったでしょ。

これで、かみさんに、好きな奴選んでって言えます。

 

■ 経過月数を数える(前にもあったっけ?)

 あれから数えて幾年月(涙)。 なんてね。
 わし、電卓が苦手で、そろばんで月数を計算するんですよ。 みなさんできます? そろばんで。

 で、そろばんも最近持たないんで、じゃあExcelで。
 経過満年数、経過満月数、経過日数を計算する時は、「DATEDIF」関数を使います。 これ関数BOXにないんだけど、何で使えるか知りませんが、ここで一発。

 セルA1に「2000/12/25」と入力し、セルB1には「2002/11/17」と入力します。
 で、セルC1に =DATEDIF(A1、B1、”M”) と入力します。 するとどうですか? C1には22と表示されたでしょうか。 満22ヶ月なんですね。

 セル番地を指定するときは、そのままセル番地を、直接日付けを入力する時は、””で日付を囲むこと。

 「使えない関数」にもほんの一例がありますので、参照されたし。

 

■ 計算式の編集

 通常、Excelで計算式を入力したセルをアクティブにした状態で、「F2」を押すと、計算式の編集モードになりますが、その時のそれぞれの参照元のセルは、それぞれ違った色で囲まれている事、知ってました?
 これは、計算式の入ったセルの真ん中辺りをWクリックした時にも同様に編集モードに入りますね。  

 この時、数式バーのそれぞれの参照元のセル番地を書き換える事によって、計算式を編集出来るわけですが、、、
シート上の色で囲まれたセルの「右下のハンドル」を直接ドラッグしてセル位置を変えると、数式バーのセル番地がその動作に応じて変化するんです。

 えっ!?そんな事知ってた? 知らなかったのはわしだけですか。 ^^; 

 

■ 作業ウィンドウ

  Excel2002を立ち上げた時、画面右側にウィンドウが1つ開いて邪魔ですね。
 「操作開始」作業ウィンドウ、これを使わないなら消してしまいましょう。 作業ウィンドウの一番下にある「起動時に表示する」のチェックを外せば、次回からこの作業ウィンドウは出現しません。

 逆に次回から出現させたい場合は、メニューバーから、ツール>オプション>表示タブの一番上の「操作開始」作業ウィンドウの項目にチェックを入れます。

 

■ 可視セルの選択

 データ>集計 で折りたたんだ表や、行、列を非表示にした状態の表を、違う場所、違うシートにそのまま見えているセルだけをコピーしたい時って有りませんか? そのままコピペをすると、表が開いた状態でコピペされてしまいまい、「意味無いじゃん」って事に。

 そんな時は「可視セルの選択」をしてからコピペをしましょう。 字の如く、見えているものだけを選択してくれるんです。

 折りたたまれた状態のシート状の、コピーしたい部分だけをまずドラッグして範囲選択。
 そして、編集>ジャンプ>セル選択
 開いたダイアログボックスの中の「可視セル」にチェックを入れて「OK」で一旦閉じて、そのまま青く反転している範囲の中で右クリック>コピー>貼り付けたい場所を右クリックして>貼り付け
 これで見えている部分だけをコピペできました。

 

■ ツールボックスのカスタマイズ

 普段何気なく見ているツールボックス。 色々並んでますね。 これ全部使ってますか?
 普段使わないものは、お道具箱にしまいましょう。 そしてお道具箱の中で眠っている本当は良く使う便利なものを取り出して来ませんか。

 方法は簡単。
 ツールボックスの任意の箇所で「右クリック」>ユーザー設定
 ユーザー設定のダイアログが開くので、真ん中の「コマンド」タブを開きます。
 左の窓には「分類」が並んでいますが、これはメニューバーの内容とほぼ同じものがあるので、使いたい分類をクリックすると
 右の窓に「コマンド」、これはメニューバーをクリックしたときに出るプルダウンメニューの中身が出てきます。

 これで使いたいもの(ツールボックスの仲間にしたいもの)を右の窓から普段見ているツールボックス上に「ドラッグ」して持っていくんです。
 そして、普段使ったことも無いボタンは、上の方から「ユーザー設定」のダイアログ (おもちゃをしまう箱)の中にドラッグしてきて、しまっちゃううんです。

 さあ、これでツールボックスがすっきりして、使い勝手が良くなったでしょ。 でもこれ使いすぎると、メニューバーに何が入っていたか忘れちゃいそう。

 

■ 二つのウィンドウを開いて操作する

   シート1とシート2を同時に並べて開いてデータを参照したり、データのやり取りをしたい時、いちいちシートタブをめくって、また戻って、と大変。
 そんな時はウィンドウをもう一つ開いて同時に別々のシートを並べて見れば簡単。

 メニューバーからウィンドウ>新しいウィンドウを開く
 横に並べるもよし、上下に並べるも、重ねて並べるも指定するだけ。
 ウィンドウの行ったり来たりはそれぞれのウィンドウの中をクリックすればOK。

 

■ 列番号のの表示を変える

 通常、Excelのシートの列ボタンは「A1形式」で表示されています。 これは列Aから始まって、B、C〜IVまでの256列で構成されています。
 行は確か65536行だったっけ。

 この列ボタンの表示が私は個人的に使いづらいので、AとかBを 1 2 3〜256に変えて使っています。 なぜって?画面に見えてるうちはいいけれど、遥か右のほうにあるセルを指定する時に「え〜っと、65列目ってCの何だ?って考えられないんです。

 この1 2 3 で表示する方法をR1C1形式と言いますが、変更するには、メニューバーから
 ツール>オプション>全般タブの設定項目で「R1C1参照形式を使用する」にチェックを入れて「OK」で閉じておしまい。
 もとのA1形式に戻すには、このチェックを外すだけ。

 

■ ながーい数式を改行する

   普通、セルの中で任意に改行できるのはご存知ですね。 ところが、数式バーに入ってる数式を、あるいは入力最中の数式を、任意に改行できるのご存知ですか?
 IF文のネストとか、日付関数を駆使している時、余りにながくて、何が何だか解らなくなる時があるでしょ。
 例えばIF文だったら、

 =IF(B1=”*”,””,A1-100) なんて数式の場合、

 =IF(B1=”*”,
 ””,
 A1-100)

なんてカンマの後(一つのまとまり)でAltキーを押しながらエンターキーで改行するんです。
結構見易い(反って見難い)??

 

■ 条件に応じて書式を変える

 入力した数値によってフォントの色を変える。 あるいは入力した数値によってセルに色を付ける。
 いわゆる、条件に応じてセルの書式を変えるには、「条件付書式」を使おう。

 通常、数値にマイナスを付けて入力すると、フォントは自動的に「赤」になる。
 これを逆にして、マイナスになった時「黒」にして、プラスになった時「赤」にしてみよう。

 まず、データを入れる範囲をドラッグして「範囲選択」をする。 そして、メニューバーから、書式>条件付書式と進みましょう。
 「条件付き書式の設定」ダイアログが出ますね。

 まず「条件1」で
 「セルの値が」>「次以下」>「0」 と入力して行き、
 書式が設定されていません の右側の「書式(F)」ボタンを押して、フォントのタブで「色」を「黒」に指定して、「OK」で閉じ、
 次にそのまま「追加」ボタンを押す。

 すると「条件2}が出てくるので、
 「セルの値が」>「次の値より大きい」>「0」と入力します。
 書式が設定されていませんの右側の「書式(O)」のボタンを押して、フォントタブで「色」を「赤」に指定して「OK」で閉じます。
 
 そして最後の「OK」でダイアログを全て閉じれば、、 後は数値を入れて確認してみましょう。

 

■ 重複データを探す

 A列に名前のデータが入力されているとして、B列に重複データがあった場合に2つ目以降のデータに”重複”と表示させるには、
 B列の先頭行に =IF(COUNTIF($A$2:$A2,A2)>=2,”重複”,””)    と入力して、後はデータの最終行までドラッグしてコピーするだけ。

 重複する全てのデータに”重複”と表示するなら範囲を全て「絶対参照」にすればよい。($マークをつける)

 

■ 時間の和や差を求める

 時間を計算する時に結果がマイナスになる時は注意が必要でしたね。 では24時間を越える結果になった時はどうでしょう。
 これまた注意が必要で、例えば全員の勤務時間を集計する時に、=SUM(  )で集計するとします。
 するとどうでしょう、求めた結果は変な時間ですね。

 これはExcelが24時間を超えると自動的に1日とカウントしてしまうからです。 そこで24時間を越える結果が出る場合は「セルの書式設定」で「表示形式」を変えます。 「ユーザー定義」の中から [h]:mm:ss を選択します。
 秒までは必要ない問い場合には ssをドラッグして削除するか、自分で  [h]:mm と入力すればいい。

 

■ 連続データをすばやく入力する(オートフィル)

 アクティブセルの右下にマウスポインタを合わせると白い十字から黒い十字に形状が変わります。
 この時、そのセルに数字の「1」が入力してあるとします。そのままマウスを下へドラッグすると、連続して「1」が入力されます。 この時「Ctrl」きーを押しながらドラッグするとどうなるでしょう。「1」「2」「3」〜と数字は連続していきます。

 では、アクティブセルに「日付」が入っている場合はどうでしょう。
 そのままドラッグすると「連続した日付」になりますね。 これを「Ctrl」きーを押しながらドラッグするとどうでしょう。 同じ日付が連続して入力されます。

 入力されているデータによって「オートフィル」と「Ctrl」キーを使い分けましょう。

 また、「1」と言う数値が入力されたセルをマウスの右ボタンでオートフィルを使ってみましょう。 ダイアログが出てきて、色々設定が楽しめます。 特に一番下の「連続データ」をクリックしてやると、増分とか加算や乗算とかのオプションが多数準備されている。
 これを「日付」で連続データ(週日単位)を選択すると、「土、日曜」を除く日付データが作成されます。 出勤日のみのスケジュール表作成には使えそうかな?

 

■ ウィンドウの分割とウィンドウ枠の固定

 縦のスクロールバーの上にある▲印のその上にある座布団みたいなの、何だか知ってます? この座布団みたいな物を下にドラッグしたことありますか?
 また横のスクロールバーの▲が横向いたそのまた右にある座布団を立てたようなもの、これも左のほうにドラッグしたことありますか?
 これは、メニューバーからウィンドウ>分割 の操作を手動で任意の位置でやる方法。
 普通は?ウィンドウを分割する時は、分割したい位置のセルをアクティブにしておいてメニューバーから、ウィンドウ>分割の動作をしますが、縦方向だけとか横方向だけと言う時には手動でこの座布団をドラッグして分割します。

 これとは別に、ウィンドウメニューのプルダウンメニューには、「ウィンドウ枠の固定」と言う物があります。 これは見た目には少し濃いめの罫線でウィンドウが分割されたように見えますが、両者とも大きな表の離れた位置のセルを操作するには同じような役割をしますが、しかし大きな違いがありますね。

 「ウィンドウの分割」は、分割されたそれぞれのウィンドウが独立して機能していること。 同じシートが分割した数だけそこにあるって事です。
 もうひとつの「ウィンドウ枠の固定」はひとつのシートを位置によっては2つに、または4つに仕切ってその仕切られた左側、または上の側を固定し、その反対側の部分をスクロールする時に使います。
 ちょうど、大きな表の縦と横のタイトルを固定しておいて、データ領域をスクロールして遠いところを見に行く時に使います。

 両方の機能を上手に使い分けてみましょう。

 

■ 数式を値に変換する

 ある計算式の入ったセルを、違う場所(シートやセル)にコピーすると、参照がおかしくなり変な結果になってしまいますね。
 そこで、計算によって得られた結果の答えだけを値としてコピーしたい場合は、当該セルをまず、コピーの動作に入ります。
 編集>コピーでも良いし、セルを右クリックしてコピーを選択しても良いし、「Ctrl」+「C」でもいいです。

 次に、面倒なので、貼り付けたいセルを右クリックで選択すると、「形式を選択して貼り付け」と言う項目が出てきます。
 「形式を選択して貼り付け」を選択して出てきたダイアログの中の「値」にチェックを入れて「OK」をクリックすると、そのコピー先のセルには計算式ではなく、答えが「値」として貼り付けられます。

 

■ 月末の日付を表示する

 にしむくさむらい。 今更言うまでもありませんが、帳簿を書いている人、Excelで仕訳を書いている人がいたら、、、、、
 毎月の月末振替仕訳、日付の欄に「末日」って書いたら、パソコンも、そこまで気を利かしてくれません。

 セルA1に月の数字を入れるとしましょう。 1月なら「1」、4月なら「4」
 セルB2にその月の月末の数字を表示したい。1月なら「31」、4月なら「30」

 では、セルB2に次のように数式を入力しましょう。

  =DAY(DATE(YEAR(NOW()),A1+1,1)-1)           ---- A1形式の場合の数式

   =DAY(DATE(YEAR(NOW()),RC[-1]+1,1)-1)     ---- R1C1形式の場合の数式

 分析ツールやアドイン、はたまたVBAを使わずに日付関数を使ってやっちゃいましょう。
 =日付は(右のシリアル値から「日」を表示(今現在のうちの年数、セルA1に入力された月の数に1を足し、一日)そこから1日引いた数)って意味。

 

■ オートフィルタで抽出したデータの合計

 オートフィルタで目的のデータだけを抽出した場合、その抽出だれたデータだけを合計するのがこのSUBTOTAL関数である。
 オートフィルタでデータを抽出し、その最終端のセルで「オートSUM」ボタンを押すと、「SUM」関数ではなく「SUBTOTAL」関数が挿入される。
 「SUM」関数は非表示のデータも集計してしまうが、[SUBTOTAL」関数は、オートフィルタで非表示になった行を無視して集計する。
 「オートSUM」ボタンで自動挿入されたSUBTOTAL関数には、集計方法として自動的に「合計」が設定される。

 

■ セルの中で文字列を改行する

 文字数が多すぎて、セルからはみ出る、あるいは途中で見えなくなる。 そんなときは文字列を途中で改行しちゃいましょう。
文字を入力している最中に、任意の箇所で、「Alt」キーを押しながら「エンター」を押す。
 これで、1つのセルの中で文字列が改行出来ました。
 ただし、其の行は行の高さが2倍になりますけどね。

 

■ ダブルクリックでデータをコピーする

  縦長の表で、一番上から下までデータをコピーするとき、どうやってます?
セルを選択して、編集>コピー>セルのハンドルをドラッグ
セルを選択して、編集>コピー>範囲をドラッグ>編集>貼り付け
等々していますか?

 かなり手間なので、一連の操作をダブルクリックだけでやっちゃいましょう。
コピー元のセルをクリックしてアクティブにすると、セルの右下に小さい四角の「ハンドル」が見えますか?
 このハンドルをダブルクリックしてください。
 ただし、これが使えるのは、そのセルの左右どちらかに隣接するデータ領域があり、且つ、其の隣接セルが連続しているところまで、と言う制限はつきますが。
 何もないシートのど真ん中で、1個だけのデータが入っているセルのハンドルをダブルクリックしても、何も起きません。 ^^;

 C列に縦にデータが並んでおり、D列に合計の計算式を入れる場合、D列の先頭に計算式をいれ、其のセルのハンドルをダブルクリック。
 計算式は、C列の連続してデータが入力されているセルの最終端まで、一気に入力されるはず。

 

■ n行おきの足し算

  ある表の中で、1行おきに足し算をしたいとか、3行おきに足し算をしたいときありませんか?
 3〜4個なら、=SUM(A1+A3+〜+A7)で事足りますが、20個ぐらいになると、面倒だし、数式バーには訳のわからない程セル番地が並びますね。
 1行飛ばしても解らない。^^;

 そんなときは、「配列数式」を使いましょう。
 配列数式は、数式が参照する因数をまとめて処理する数式で、1つの数式で複数の計算を行った結果を返すことが可能です。

 数式を入力し終わったら、「Ctrl」、「Shift」の2つのキーを同時に押しておいてからエンターキーで確定します。 

 n行おきの足し算は
 =SUM(IF(MOD(ROW(セルの範囲),行数)=MOD(ROW(先頭のセル),行数),セル範囲))
 これは、もし、指定したセルの範囲の行番号を1個ずつ行数nで割り、その余りが、先頭のセルを行数nで割った時の余りと同じ結果なら、そのセルの数値を足しなさい。と言う意味。

  因みに、A1からA13までのセルに数字があるとします。 これをA1+A3+A5+〜+A13を計算したい時に、答えを表示するセルはA15としましょう。
 
 =SUM(IF(MOD(ROW(A1:A13),2)=MOD(ROW(A1),2),A1:A13))
 と入力し、「Ctrl」+「Shift」+「Enter」 で確定します。 ここで注目するのは、数式の最初と最後が{ }でくくられていますね。

 これが「配列数式」です。

 

■ シリアル値から経過月数を算出する

 2つのそれぞれのセルに日付が入っています。
 例えばセルA1に「2000/6/15」 セルA2に「2002/6/30」って。

 セルA3にその間の月数を表示するには、次のように計算式を入れましょう。 但し、両端入れで。
 積立金や、保険料の支払い回数を算出する際は最初の月も1回目と数えなければいけないからね。

 ここで、西暦が面倒な方は、キーボードからいきなり H13/6/15 と入力して下さい。 PCは勝手に日付のシリアル値で入力してくれますから。

   A         B
---------------
1  |  2001/6/15
--+------------
2  |  2002/6/30
--+------------
3  | =(YEAR(A2)-YEAR(A1))*12+(MONTH(A2)-MONTH(A1)+1)
---------------

 まず、2002から2001を引いて1。 それに12倍して年数を月数に変換。
そして、後の月から前の月を引いて1を足す。 これ両端入れ。 道路のデンシン棒の数を数えるのと同じかな?

 ちなみに、電柱の間を数えるのが「片端入れ」 かな?

 

■ ファイルが開かない

default.aspx-scid=http%3a%2f%2fwww.microsoft.com%2f isapi%2fgomscom.asp%3fTARGET%3d%2fjapan%2fsupport%2fkb%2farticles%2fJP411%2f6%2f 36.asp

 

■ 住所から郵便番号に変換

 MSIMEの機能を使い郵便番号から住所に変換することは出来ますね。 それを今度は逆に使いましょう。 

 準備は、メニューバーのツール>アドイン>郵便番号変換ウィザード にチェックを入れれば完了。
 Excelのとあるシートのとあるセルに住所が入っているとします。
 メニューバーから、ツール>ウィザード>郵便番号変換と進み、住所から郵便番号を生成 にチェックを入れ、後はお好きなように設定してください。
 これで、住所から郵便番号が変換されます。 フリーソフトを使うのも手ですが、MSの純正機能を使うのも方法かと。

 もし、[アドイン] に「郵便番号変換ウィザード」がなければ、「Microsoft Excel2K*用郵便番号変換ウィザード」をダウンロードしてください。

 Excel2000用はここ
  http://office.microsoft.com/japan/Downloads/2000/zip2kud.aspx

 Excel2002用はここ
  http://office.microsoft.com/japan/downloads/2002/zipcd7xp.aspx

 ダウンロード後、ダウンロードしたファイルをダブルクリックしてインストールするだけ。

 

■ セルに名前をつける (番外編)

 セルに名前を付ける事は出来ましたか? メニューバーから、 挿入>名前>定義 から行くのと同じことなんですよ。
 面倒だから、いきなり名前ボックスに入力しただけ。
 
 ところで、こんな名前の使い方、というよりも、足し算の無理やり計算式。

 セルA1:G1にある表の合計金額が入っているとします。 計算式でも、結果でも問題は有りませんが。
 そこのセル範囲に名前を付けるとします。 例えば「合計」 と。
 方法は、前述の通り、簡易版で、セルA1:G1をドラッグして範囲選択しておいて、「名前ボックス」に「合計」と入力します。

 そしておもむろに、セルA4辺りに =合計 C    ’合計とCの間は半角スペースを入れる
と入力して見て下さい。 結果は、合計欄と、式を代入した列との交差したセルの値を返していますか?

 「合計」欄のここの列の値はこれ。  って言うことを名前を使って無理やり表示させてるだけで、ちょっと回りくどいかなあ。
 でも、これを応用するとかなり使えないかなあ?

 

■ セルに名前をつける

 Excelで計算式を入力するとき、 =A1+B1 とか =SUM(R[-10]C:R[-1]C) とか無機質な記号と数字を並べていませんか。
A1ってなんだったっけ、R[-1]Cって何処だったッけ。 なんてことは毎日ですよね。

 この際、セルに番地みたいに名前を付けちゃえ。
 一番簡単なのは、数式バーの隣にいる「名前ボックス」。 ここに直接名前をいれてみましょう。
 名前を付けたいセルをクリックし青く反転させてアクティブにします。 通常、A1形式を選択している人は、名前ボックスにはその選択されているセルの番地が入っていますね。 例えばB2なんて。
 またR1C1参照形式を利用している人は名前ボックスにはR2C2なんて表示されています。

 ここで青く反転している名前ボックスにおもむろに任意の名前をつけてみましょう。
 例えば、
 A1には「数量」と言う名前を。 そしてB1には「単価」と言う名前を。
で、C1に計算式を入れます。 =数量*単価 
で、A1、B1にそれぞれ適当な数字を入れてみて下さい。 こうする事によって、C1にはどんな計算式が入っているか一目瞭然。

結果は >^_^<

■ VBAのフォントがおかしくなったら

http://office.microsoft.com/japan/downloads/2000/o9vbeupd.aspx

 ここへ行って、修正プログラムをダウンロードしましょう。
  シート名とかモジュールの名前がアラビア文字とか変な書体になってませんか?

■ Excel のファイルのバックアップ

 Excelで作成したファイルを保存する際に、メニューバーから、ファイル(F)>名前を付けて保存>ファイル名を付けて保存ダイアログで、メニューバーから、ツール(L)>全般オプション(G)のダイアログで、バックアップファイルを作成する(B)にチェックを入れて「OK」をクリックすると、同じルートディレクトリに「ファイル名+"のバックアップ".xlk と言う名前と拡張子の付いたファイル(バックアップファイル)が作成されます。
 万が一本体のファイルが壊れてもバックアップファイルが作られていれば、助かる可能性も大かと。

 基のExcelのファイルの「ThisWorkbook」のモジュールに、

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error GoTo ErrorHandle:
        ActiveWorkbook.SaveAs Filename:="C:\MyDocuments\ファイル名.xls", CreateBackup:=True
            On Error Resume Next
    ErrorHandle: Exit Sub
            Resume
End Sub


と書き込んでおけば当該Excelのファイルを閉じる際に、CドライブのMyDocumentsにバックアップファイルを自動で作成してくれます。
 保存先のディレクトリは自分の好みの場所に変更して下さい。
 これでほんとに上手く行くのかなあ? 

 

■ 文字列を数値に変換

 文字列を数値に変換するには Value関数を使いましょう。
 例えば、結果として ’123 と言う文字列が有ります。 これを数値の123 として扱うには、答えあるいは計算式を挿入するセルに、 =Value(ターゲットセル)
 セルA1に ’123 と言う文字列があるとします。
 セルB2に =Value(A1)と入力すると、結果は数値としての 123が返ります。

 

■ 勤務時間を計算する

 うちの会社は8:30が始業です。 定時は18:00で、タイムカードの集計は15分刻みです。
タイムレコーダ君はとてもせっかちで正確な性格で、1秒たりともごまかしは効きません。
そんな会社の今日の勤務時間を計算しました。

 私は8:30:03にタイムカードを挿入し、息を切らせて出勤。
 昼休みはおにぎりをかじりながら頑張ったのですが、1:00間の休憩を差っぴかれ、
 帰りにはタイムカードを挟んだ指があせって開いて17:59:59にガチャン。 (+_+)

A列に出勤時間、B列に退社時間、そしてC列にその日の勤務時間を算出するとして、調整した出勤時間、退社時間をそれぞれD列、E列に計算させましょう。

まず、D列には  =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)
そしてE列には   =TIME(HOUR(B1),FLOOR(MINUTE(B1),15),0)
最後にC列には =(E1-D1)*24-1

  Excelの内部では時間は「シリアル値」で管理されている。 このシリアル値の小数点以下の部分が時刻を管理していて、0〜1までを24時間に対応させている。 因みに0.25は6:00、0.5は12:00、0.75は18:00、そして1は24:00である。
 従って、時刻を24倍すると、時間単位に変換できるのである。
 1を引いたのは、休憩時間を差っぴかれたんです。

 因みに、うちの会社は、残業手当、休日手当は一切無いので、面倒な残業計算、休日出勤計算は必要ないのでここでは割愛させていただきますが、当該手当てのある会社勤務のかたは、その分頑張って考えてね。

 

■ 郵便番号から住所に変換

 日本語入力がONの状態で、郵便番号を入力し、エンターキーを押す前に漢字変換のようにスペースキーを押す。
001-1234と入力して→「スペースキー」。 すると「北海道札幌市北区-0134」が候補の中に現れますね。

 もし、変換できないようなら、IMEのツールバーの「プロパティ」>「辞書/学習」のタブで「郵便番号辞書」にチェックを入れて試してみましょう。

 

■ 循環参照 (Excelのヘルプより抜粋)

    開いているブックのいずれかに循環参照が含まれていると、開いているすべてのブックの計算が自動では行われなくなります。循環参照とは、数式が入力されているセル自体を直接的または間接的に参照している数式です。
 循環参照の基になるセルを調べるには、[循環参照] ツールバーを使います。[循環参照] ツールバーを使うと、循環参照の基になるすべてのセルに移動して数式を修正することができます。

  1. [循環参照] ツールバーの [循環参照のトレース] ボックスの先頭のセルをクリックします。

  2. セルに入力されている数式を確認します。そのセルが循環参照の原因かどうかを特定できない場合は、[循環参照のトレース] ボックスに表示されている次のセルをクリックします。

  3. ステータス バーに "循環" と表示されなくなるまで、この操作を繰り返します。

ヒント

 

■ 相対参照と絶対参照と複合参照

 =B1*C1 という計算式を D1に入力したとする。 そのセルを下に10個ドラッグして複写した場合、Excelは計算式を自動修正し、それぞれ D2には=B2*C2、D3には=B3*C3 というようにそれぞれ勝手に変化していく。
 ところが、B1は常に一定で、変化をしては困る場合、毎回計算式に「=B1*」を入力していては堪りません。
で、計算式を入力する際に、 「=B1」と入力したら次に「F4」キーを押してみてください。
 B1→$B$1→B$1→$B1と「F4キー」を押すたびに変化するのが確認できるはずです。 このときの「$」記号が絶対参照を意味する記号なのです。
 行も列も変化させたくない場合は両方に「$」をつけ、行だけを変化させたくないなら数字の前に、列を変化させたくないなら列記号の前に、「$」をつければいい訳です。
 「$」記号が無いのが相対参照、両方についていれば絶対参照、どちらかについていれば複合参照です。

因みに、計算式が入力されたセルをダブルクリックするか、選択して「F2」で編集モードにしておいて、数式バーの該当する部分をドラッグして反転させておいて[F4」を押せば、後からでも編集可能です。

 

■ Excelで時間計算

  残業計算や、出勤時間を計算するときに、よく考えちゃいませんか。
例えば、20時に出勤して、翌朝の2時に退社した場合、計算式はどうしましょう。

セルA1に 20:00 と入力し、セルB1に 2:00と入力します。 そしてセルC1に =B1-A1 と入力します。
結果はどうです?
#############となりましたか?セル幅をどれだけ広げても、答えは同じ。

これは、Excelが、時間にマイナスはありえない、といってエラーを表示するからです。
では、どうしましょう。
答えは、退社時間に「1」 を足して計算させます。 IFを使えば簡単ですね。
セルC1に  =IF(B1<A1,(B1+1)-A1,B1-A1)
と入力すればOKです。 これはExcelが 24:00=1 とするシリアル値に換算して時間を計算するためです。
2:00 と入力したセルに 1を足せば、Excelは 26:00 として計算します。

もっと簡単なのは、最初から退社時に 26:00 と入力すること。

 

■ Excelでハイパーリンクを操る

 ExcelであるセルにメールアドレスやURLを入力すると、いきなりハイパーリンクが設定されてしまいます。 便利な反面、わずらわしさもありますね。
そのセルを(ハイパーリンクを)クリックしたばかりに、とんでもない所へ飛んでいった経験はありませんか?
 このハイパーリンクを解除する方法は

1 アドレスやURLを入力して確定したら、いきなり「戻る」ボタンをクリックする。
2 当該セル以外のセルをクリックし、コピー動作に入ります。
  コピー>形式を選択して貼り付け>乗算 にチェックを入れ、>OK
3 当該セルを右クリックしハイパーリンク>ハイパーリンクの解除

因みに、ハイパーリンクが設定されたセルを右クリックして、序でに「Esc」キーを押せば、編集可能な状態にもっていけるとか。

■ 数式を表示させない

 Excelでセルに数式を入れると、数式バーにその数式が表示されます。
ただし、メニューバーの 表示>数式バー にチェックが入っている場合ですが、この数式バーに、セルに代入された数式を表示させない方法が有ります。
 数式を代入したセルを右クリック>セルの書式設定>保護>表示しない にチェックを入れる>閉じる
 メニューバーから ツール>保護>シートの保護  これでOK。
 ただし、シートに保護をかけるので、他の設定をしていないセルは書式の変更や、定数や変数の入力も出来なくなるので要注意です。

 

■ Excelのカメラ機能

 これは、コピー>貼り付け の作業で、ちょっと違うのが、このカメラ機能。
 普通、コピー動作をするのには、

@コピー元を選択し、メニューバーから、編集>コピー>コピー先を選択>編集>貼り付け
Aコピー元を選択し、「Ctrl」+「C」>コピー先を選択して、「Ctrl」+「V」
Bツールボックスから、「刷毛」とか「のり」とかを使って

こんな風にしていますね。

 これを、実は、「コピー元」をパシャっとカメラで撮って、任意のセルにグシャっと貼り付けるんです。
で、これがリンクしているので、「コピー元」に変化を加えれば、「コピー先」もその都度一緒に変化します。
 影武者的利用方法。

@まず、「コピー元」を選択します。 セル一つでも良いし、範囲を選択しても構いません。
Aコピーの操作に入ります。これはお好きな方法を取ってください。
B「コピー先」をクリックして選択しておきます。
C「Shift」キーを「押しながら」、メニューバーから「編集」を選択します。
D「画像のリンク貼り付け」をクリックします。
E出来上がり。

「コピー元」が、画像として、「画像元とリンク」して、「コピー先」に反映されているはずです。
コピー元の任意のセルの値を変更して見て下さい。 コピー先の値もその都度変化しているはずです。

 セル幅に囚われない表を作成したりする場合には便利かと。
画像として扱っているので、自由に大きさを変化させる事が出来ますよ。

 

■ セル間の移動を効率よく

 セル間の移動をスクロールバーやマウスのスクロールボタンでくりくりしていませんか?
連続したセルの終端を探すには、「エンド」キーを押した後、その連続している方向の矢印キーを押せばその終端へ一発で行きますね。
 キーボードからは面倒な方に、まず、数値等が連続して入力されているセルの中の任意のセルをクリックしてアクティブにします。 そこで、飛んで行きたい方向のセルの周りの太い線をダブルクリックしてみてください。
 例えば、右へ行きたい場合はセルの右側の枠をカチカチッ。
 どうです?一辺に右端のセルへカーソルが行きましたか?  小技でした。 えっ小枝?

 

■ 分数の表示

 1/3 とセルに入力すると 1月3日 なんて表示されませんか?
入力された数値をExcelは日付けと判断して、1月3日と表示します。 その「値」も数式バーを見れば明確ですね。
今なら「2002/1/3」 と入っているでしょう。
 これを入力どおりに「1/3」 と表示するにはどうしたらいいんでしょう。

 入力する前に、入力したいセルを選択し、メニューバーから、書式>セル>表示形式>文字列と行ってください。
これだけで分数もそのまま入力できるでしょう。
 ただし、数値としては計算できませんよ。

 もっと単純な方法が!!
 0 1/3と入力。  0の後ろに半角スペースを入れて1/3と入力するだけ。おぉ、簡単。

 

■ Excelの自動保存

 Excelで作業中、突然ハングアップ、フリーズって言う経験は有りませんか?
 せっかく作っていたファイルが「パーこ」になっちゃって。
 悔しいやら、腹が立つやら。 そんな時、勝手に上書き保存をやってくれるのが、これ。

 Excelで、メニューバーから、ツール>アドイン>自動保存 にチェックを入れて「OK」
 さらにメニューバー>ツール>自動保存>時間の設定と進みます。
 任意の時間を設定して「OK」

 これで、作業中に、指定した時間ごとに上書き保存のメッセージが出るので、必要に応じてクリックすればそこまでのデータは自動的に保存される。

● もし、アドインに「自動保存」が無い場合は、標準では不在なので、インストールしなければなりません。
   コントロールパネルの「アプリケーションの追加と削除」から,Excelに自動保存アドインを組み込んでください。

●Excel2002の場合には、ツール>オプションに自動保存のタブがあるので、そこで設定します。

■ 列ごとに日本語入力のONとOFFを切り替える

 A列には日付を半角英数字で、B列には氏名を、そしてC列には生年月日を半角英数字で、と連続で入力する場合、
いちいち、IMEの切り替えをしていませんか?
そんなときは、「入力規則」を使います。
 例えば、A列を選択した状態で、メニューバーから、
データ>入力規則 と進み、「日本語入力」のタブを開き、日本語入力のボックスで、「オフ」を選択し、「OK」で閉じれば
その列「A列」は全てIMEの入力モードを触らなくても半角英数字で入力が出来ます。
 B列には、同様に、列を選択しておいて、メニューバーから データ>入力規則と進み、「日本語入力」のタブで
日本語入力のボックスで「オン」にすれば、B列は全て「ひらがな」入力の漢字変換可能な入力になります。
 住所録や、名簿、その他データ入力には、欠かせない事前準備になるでしょう。

■ 文字列の右の1文字を抽出
  1. 「データ」→「集計」をした後の結果の、「計」の字をなくするには、

  2.  検索文字列から「計」の文字を「””」に変換する

  3.  それには「SUBSTITUTE」関数を使用する

  4.  文字列から「計」の文字が””に変換された新規の文字列が出来る。

  5.  それを、形式を選択して貼り付けで値に直してコピーする。

  6.  出来上り

 

■ 表を拡大・縮小して印刷する

 指定た用紙1枚に収まりきらない表を印刷する時は、印刷プレビュー画面で、「設定ボタン」をクリックし「ページ設定」を呼びだす。ここで「ページ」タブをクリックし、「拡大縮小印刷」の「拡大/縮小」を見てみる。 標準では「100%」に設定されているが、表が大きすぎて収まっていない場合は、▼ボタンをクリックして数値を下げる。逆に小さい表なら数値を上げて拡大印刷も出来る。 数値欄には直接、数値を入力しても可。

■ 大きな表を1ページに収めて印刷する

  印刷プレビュー画面で、「設定ボタン」をクリックし「ページ設定」を呼び出したら、「ページ」タブにある「拡大縮小印刷」の「横 1」*「縦 1」オプションを選択する。

「横 1」*「縦 1」とは、表を横、縦共に1ページ内に収める意味で、数値の変更は可。この時の縦横の比率は自動設定になる。

■ ヘッダー/フッターに画像を入れる

  Excel2002では、「ページ設定」の「ヘッダー/フッター」タブで、画像を挿入できるようになったが、Excel2000では標準設定では出来ない。 そこで先ず、ワークシートの1行目に空白行を挿入し、任意の画像をその1行目に貼り付ける。位置はもちろん任意の場所に。 行の高さは、画像より少し高めにしておく。 その後「ファイル」メニューから「ページ設定」を選んで「シート」タブをクリック。  「行のタイトル」の右端のボタンをクリックし、先ほど画像を貼り付けた1行目を、タイトルとして指定する。 これで画像がヘッダーのロゴのように印刷される。

■ 使えないショートカットキー(Excel編)

 マウスを使わなくても、これだけ覚えれば数段早くExcelを使えるよ。

Excel 一般

ファイルを開く Ctrl + O(オー)
上書保存  Ctrl + S
       
コピーの動作に入る Ctrl + C
切り取りの動作に入る Ctrl + X
貼り付けの動作に入る Ctrl + V
       
列を非表示にする Ctrl + 0(ゼロ)
非表示の列を表示する Ctrl+Shift +
行を非表示にする Ctrl + 9
非表示の行を表示する Ctrl+Shift +
       

すぐ上のセルのデータ、数式を書き込む

 Ctrl + D
すぐ上のセルの結果(値)を書き込む Ctrl+Shift +
すぐ左のセルのデータを書きこむ  Ctrl + R
式を値に変換  F2 F9
範囲選択した複数のセルに同一データを一発入力  Ctrl + エンター
関数ボックスを開いた状態にする(関数を貼り付ける) Shift + F3
現在の日付けを入力する Ctrl +
現在の時間を入力する Ctrl+Shift +
       
アクティブセルのあるところまでひとッ飛 Ctrl + BackSpace
アクティブセルのある、列全体を選択する Ctrl + space
列の挿入(列全体を選択してから)  Ctrl + +
列の削除(列全体を選択してから)  Ctrl + -
アクティブセルのある、行全体を選択する  Shift + space
行の挿入(行全体を選択してから)  Ctrl + +
行の削除(行全体を選択してから)  Ctrl + -
       
編集→ジャンプ→セル選択  F5    
編集→ジャンプ→セル選択→可視セル Alt +
デカイ範囲を選択した時に、範囲の各四隅へ移動する  Ctrl +  (ドット)
       
オートSUMを一発入力(範囲指定してから)  Shift + Alt + =
オートコンプリートの一覧を利用する  Alt +
位取り「,」を設定する Alt+Shift +
       
シート間を移動 右のシートへ  Ctrl + PageDown
シート間を移動 左のシートへ  Ctrl + PageUp
シートの挿入 Shift + F11
       
ヘルプを呼びだす  F1    
       

■ 使えない表示形式

■ 曜日を漢字で表示する(WEEKDAY関数で曜日を取り出したセルに対し)
  
月、火、水、、、、
  aaa
   
 
  
月曜日、火曜日、水曜日、、、、、
  
aaaa
  セルの表示形式>ユーザー設定で自分で打ちこむ。
  
■ 24時間以上の時間表示
    
26:00  29:30 
    [h]:mm

■ セルの内容を表示しない
   
単に見えない
   ;;
   セルの表示形式>ユーザー設定で自分で打ちこむ。

■ 分数を表示する

  16/3
     ???/???
     セルの表示形式>ユーザー設定で自分で打ちこむ。

■ 帯分数を入力する

  5 1/3   (5と1/3)
     5の右に半角スペースを入れ、続いて「1」「/」「3」 と打つだけ。
  因みに、入力したセルの表示形式を見ると「分数」になっている筈。 
 
■ 数字を漢数字にする
     123→百二十三  
  [DBNum1]

 
 123→壱百弐拾参
  [DBNum2]

  
123→百2十3
  [DBNum3]


  セルの表示形式>ユーザー設定で自分で打ちこむ。
  

■ 任意のシートを抜きだして新しいブックを作る

 たくさんあるシートから1枚のシートだけを抜きとって、新しいブックにして保存したいことありますよね。 そんな時は、ブックの「表示を小さくする」ボタンを押して、一回り小さくしておきます。
目的のシートのタブを外側の暗い何も無い場所へドラッグします。  すると、そのシートだけが抜きだされ、一つのブックが出来ます。 ただし、元のブックからは亡くなります。 任意の名前を付けて保存すれば出来上り。
 この時、シートのタブを、「Ctrl」キーを押しながらドラッグすれば、元のブックには原本が残り、新しくコピーとしてブックが出来上ります。

  入力の手間を省く

● 通常Excelは設定を変更しない場合、エンターキーを押すとカーソルは「下」へ移動する。
 この状態で、データを横に入力して表を作る場合は、「TAB」キーを利用する。
 最初のセルにデータを入力したらマウスではなく、「TAB」キーを押す。 するとカーソルは「右」に移動する。 順にデータを入力して右移動をして行き、最後のデータを入力したら、「エンターキー」を押すのである。 すると、カーソルは、次の行の「左端」に移動する。
 この動作を繰り返せば、マウスをいちいち使うことなくデータの入力がスムーズに行く。

● 住所から郵便番号、郵便番号から住所を表示させる
 MicrosoftのWebサイト http://office.microsoft.com/japan/Downloads/2000/zipcode7.aspx
から無償配布されている「郵便番号変換ウィザード(ファイル名:zipcod7.exe)をダウンロードする。
 ダウンロードしたフィルをWクリックすれば、自動的にセットアップできる。
セットアップが終ったら、Excel2000を起動し、ツール>アドインとたどり、開いたアドインのダイアログボックスで「郵便番号変換ウィザード」にチェックを入れ「OK」。
 次に、ツール>ウィザード>郵便番号変換とたどると「郵便番号変換ウィザード」が起動する。
 あとは、お好みに合わせて設定を。

● 日本語入力モードを自動的に切替える
 漢字、平仮名、数字、カタカナ、数字と横並びのセルごとに入力モードが異なる時、セルを移動するたびに入力モードの切替をしていないだろうか?
 セルを移動する度に自動的に入力モードを切替えるには「入力規則」を使う。
 例えば、A列全体を選択し、データ>入力規則とたどり、データの入力規則ダイアログで「日本語入力」のタブを開き、「日本語入力」欄で、「半角英数字」を選択し「OK」で閉じる。
 B列には同様に「日本語入力」欄で、「ひらがな」を選択し、C列には「半角カタカナ」をそれぞれ選択しておく。 これで、ABC列はそれぞれ入力と同時に日本語入力モードが自動的に切り変る。

● 上記表にあるショートカットキーを利用する。
 
● Excelの入力支援ソフトを使う
 
  谷  誠之 氏 VBA研究所  「楽々」 アドイン         http://www.vbalab.net/
  早坂清志 氏   「stepsum関数」   アドイン      ここ
 
  


 

 

戻る