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

数式以外のセルのデータを一括削除

セルのデータの削除方法についてです。

エクセルのセルに入力されているデータを削除したい場面はいくつもあると思います。しかし、数式が入力されているセルを削除してしまうと困ったことになることがあると思います。

自分がすべて作成したのならどこに数式が入っているか、どこのセルの値は消してもいいかは分かりやすいでしょうが、人が作ったエクセルシートはどこのデータを消してよいのかすぐには判断つきにくいと思います。

数式が入力されていないセルのデータだけを簡単に削除できれば便利だと思います。下の図は請求書からダウンロードできるエクセルブックです。

再利用しようと思ったときはどこを消せばよいでしょうか。日付や請求書番号、相手先のご住所や会社名などを、変更しますね。ここは文字列なので遠慮せずに削除して下さい。

表の部分は計算式が入っていますので慌てて上書きしてしまうと焦ってしまいます。この表の場合は文字列に見える商品名の列はVLOOKUP関数で商品番号から自動で表示していますので、商品名を消してしまいますと商品名を手入力しなければいけなくなります。

そうしますと、データを削除するときにいちいちセルを選択して数式バー数式が入っているかどうかを確認したのちにしかデータを削除できません。

数式が入っていないセルだけを選択する方法をご説明します。まず、データを消したいエリアを選択します。見出し列(商品番号や商品名)は文字列ですが、変更する必要はありませんね。小計や消費税、合計はおそらく計算式で自動計算と思われます。

変更したいデータのセルと、計算式の入っているセルをまとめて選択した状態です。ここから、計算式ではないセルだけを選択します。

ホームタブの編集の、検索と選択をクリックします。ジャンプをクリックします。

ジャンプのダイアログボックスが表示されます。

セル選択のボタンをクリックします。

定数にチェックを入れてOKボタンをクリックします。

これで数式以外のデータが入力されているセルが選択できましたのでDELETEキーで削除してください。

ダウンロードしていただけるサンプルには名前ボックスでクリアを選択していただければ商品番号の列と数量の列は自動で選択されます。

seikyusyo_6

色々な物の非表示

だからどうだということは全くありませんが、エクセルではいろいろなものを非表示にすることが可能です。

上の画像はエクセルです。一瞬何かわからないかもしれません。

  • リボンを非表示
  • 数式バーを非表示
  • 行見出し列見出しを非表示
  • 行と列を非表示
  • スクロールバーの非表示
  • 罫線を非表示
  • シート見出しを非表示

にしてあります。

 

リボンの非表示と数式バーの非表示はエクセルに対する設定ですので他のファイルに対しても有効です。(別の環境のエクセルでは非表示にしていないとなりません)

それ以外はファイル対する設定になります。

不要な行と列の非表示は5行5列のシートを作成を参照してください。Ctrlキー + Shiftキー + 下矢印キー (列の時は右矢印)がポイントです。

更にリボンを非表示にするを参照していただければリボン非表示にできます。ただし、これら以外の非表示はファイルタブのオプションから操作しますのでリボンの非表示は最後にしてください。

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

表示の下に数式バーを表示するがあるのチェックを外します。

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

次のシートで作業するときの表示設定で行列番号を表示する、枠線を表示するのチェックを外します。

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

以上で上のようないろいろなものを非表示にしたエクセルが完成です。

 

エクセル おすすめショートカット10個(本当の基本編)

エクセルを使うときに覚えておけば便利なキーボードショートカットを選んでみました。独断で選びましたがよろしくお願いします。すべてのエクセルのバージョンでは試していませんが、昔から使っているものばかりなのでwindows版のエクセルなら大丈夫だと思います。

ぜひとも覚えていただきたいショートカットトップ10

1位 元に戻す Ctrl + z
操作を失敗したときや、前の状態の方がよかった場合などに使ってください。クイックアクセスルールバーにあるボタンと同じです。

2位 やり直す Ctrl + y
元に戻すとペアで覚えてください。元に戻しすぎた場合やり直すでそのまえのじょうたいにもどります。

3位 コピー Ctrl + c
エクセルに限らずワードでも使えますし、コピーのボタンがないところでも使えることが多いです。いろいろなソフトでもCtrl + cがコピーに割り当てられているようです。

4位 貼り付け Ctrl + v
コピーしたものを張り付けます。コピーとワンセットで覚えてください。

5位 切り取り Ctrl + x
これもセットに入れておいてください。ちょうどキーが3つ並んでいますのでcopyのcを中心に3つです。

6位 すべて選択 CTRL+ a
すべてのセルを選択します。データをすべて削除するとかコピーするとかの場合便利です。

7位 直前のコマンドの繰り返し F4
直前と同じ操作をする際に使います。例えば離れたセルを同じ色で塗りつぶす場合などセルを選択しては塗りつぶしボタンをクリックするのは面倒ですね。そんな時、2回目以降はセルを選択してからF4キーをたたきます。使えないコマンドもあるようです。

8位 保存 Ctrl + s
何かの事情で強制終了してしまうと前回保存したところまで作業が戻ってしまいます。こまめに保存してください。新規保存時以外は上書き保存になります。

9位 書式設定 Ctrl + 1
右クリックからセルの書式設定でも構いませんが、マウスを持っていない場合便利です。

10位 右クリック Ctrl + F10 またはアプリケーションキー
キーボード操作中右クリックしたい場合、マウスを持ちなおさなければなりません。これを覚えておくと便利です。アプリケーションキーはキーボード右のCtrlキーの左隣にあるはずですが、ノートパソコンなどアプリケーションキーがないものもあります。

いかがでしたでしょうか。すでに全部知っているし使っているという方も多かったのではないでしょうか。7位の直前のコマンドの繰り返しと9位の書式設定以外はほとんどのアプリケーションで使えると思います。パソコンの共通ショートカットに近いものですね。

またの機会に別のショートカットもご紹介する予定です。

文字列なのに数値と認識される

わかりずらいタイトルですね。自分もあとで困る気がします。

よくあるのが1-2のような表示にしたいのでそのまま入力すると日付になってしまうという分です。

このまま表示してくればいいのですがEnterキーで確定したとたん日付の表示になってしまいます。

入力したのは1-2なんですが表示は1月2日となっています。さらに値も2017/1/2と日付になってしまっています。こうなってしまうと入力した1-2という値は保持されていません。

1-2と表示させたい場合は入力前に表示形式を文字列に変更してから1-2と入力してください。

ホームタブの数値の数値の書式から文字列を選択したのちに1-2と入力します。

文字のつもりで入力したら違う表示になってしまった場合はセルの書式を文字列にしてから入力しなおしてください。

1-2が1月2日と表示される以外にこのようになるパターンとしてはスラッシュ(/)を入力したら日付になるパターンなどもあります。

ハイフン、スラッシュ、コロンが日付、時間になるパターンですね。あとは午前、午後のAとMもあります。午前午後は0から12までの数値の後スペースAまたはPの形です。AとPは小文字でも構いません。

指数表現の数値e数値のパターンもあります。

 

商とあまり 負の数の除算

商とあまりを1つのセルに1発で表示する関数は残念ながらありません。

商とあまりを個別に計算して1つのセルに表示するしかないと思います。まずは、商の部分を計算してみます。

10割る3を計算すると答えは3.33333333という循環小数になります。今回は余りを出しますので小数点以下不要です。

使える関数としては
ROUNDDOWN関数、INT関数、TRUNC関数、QUOTIENT関数なんかが挙げられると思います。私の環境では、QUOTIENT関数がそのまま使えましたが開発タブを表示しないと、QUOTIENT関数は使えないかもしれません。

QUOTIENT関数は除算の商の整数部を返します。 商の余り (小数部) を切り捨てる場合に、この関数を使用します。
ROUNDDOWN関数は数値を指定された桁数で切り捨てます。
INT関数は指定された数値を最も近い整数に切り捨てます。
TRUNC関数は数値の小数部を切り捨てて、整数または指定した桁数に変換します。

ではエクセルで実際に比べてみたいと思います。

それぞれのセルには以下の数値、数式が入っています。

A2   10
B2     3
C2    =A2/B2
D2    =ROUNDDOWN(A2/B2,0)
E2    =INT(A2/B2)
F2    =TRUNC(A2/B2,0)
G2    =QUOTIENT(A2,B2)

ROUNDDOWN関数、INT関数、TRUNC関数、QUOTIENT関数いずれも同じ答えが出ています。(FLOOR関数忘れてましたね。時間計算3 FLOOR 関数四捨五入、切り上げ、切り捨て ROUND関数 ROUNDDOWN関数 ROUNDUP関数 TRUNC関数 INT関数

この表の割られる数(被除数)を9,8,7と減らしてみます。

INT関数だけが答えが違うところがあります。被除数がマイナスになってからほかの関数と答えが違ってきましたね。

では、商の部分はINT関数を使うかそれ以外の関数を使うかということで先にあまりに進みます。

MOD関数(エクセルでじゃんけんゲーム MOD関数)を使って余りを計算します。

H列にあまりを計算する数式
=MOD(A2,B2)
を入力しました。

余りが規則正しく繰り返されていますね。

これで答えが出たようです。検算しますね。2行目は10を3で割ったら何あまり何ですか。ということなので

10 ÷ 3 = 3あまり1

10割る3は3あまり1ですね。割る数の3と商である3を掛けて余りの1を足してあげれば10になりますよね。ほかの行、割られる数が負の場合を見てみましょう。被除数がマイナスの時はINT関数の答えはほかの関数の答えと違っていましたね。

被除数が-10の時を見てください。

-10 ÷ 3 =-3あまり2
-10 ÷ 3 =-4あまり2

の2通りの答えが出ているように思います。どちらが正しいのでしょうか

-10 ÷ 3 =-3あまり2 検算 3 × -3 +2 = -7
-10 ÷ 3 =-4あまり2 検算 3 × -4 +2 = -10

なので-10 ÷ 3 = は-4 あまり 2 となります。なんかしっくりこないような気がします。計算結果はあってますが、-10 の中に 3 がマイナス4回あるのか?ってとこですよね。日常の感覚から言えば-10 の中に 3 がマイナス3回あるので 3 × -3で-9になり、あと-1を足してあげればいいと思いますね。

-10 ÷ 3 =-3あまり-1 検算 3 × -3 -1 = -10

これが納得いきそうな答えに思えます。ということはMOD関数がおかいしいのでは?巻子のヘルプには以下のように書かれています。

表示形式 (数値)

和暦の表示形式は表示形式 日付(和暦)を参照してください。

空白のセルでホームタブの数値の数値の書式右の下向き三角▼をクリックしてその他の表示形式をクリックします。

セルの書式設定のダイアログボックスが表示されます。表示形式タブの分類が標準になっています。

一番下のユーザー定義をクリックします。

種類のところにG/標準と表示されています。その下には沢山の表示形式の例が表示されています。この中で1番長い書式を選択して中身を確認していきます。

_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

という書式を設定してみました。記号がいっぱいでとてもややこしいイメージですね。まずはこの長い書式設定の中からセミコロン(;)に注目してください。

_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

セミコロンが3つありますのでセミコロンの前後で全部で4つのパーツに分かれています。

正の数の表示形式 ; 負の数の表示形式 ; ゼロの表示形式(空白時) ; 文字列の表示形式

とそれぞれ独立しています。このセミコロンは最大3つ(4つのパーツ)まで指定できます。セミコロンが2つだと頭から3つ分の指定で、文字列の時の表示形式は特に指定なしということになります。セミコロン1つだと正の数と負の数の時だけ表示形式指定されているということです。

ちなみに
;;;

とセミコロンを3つ続けて指定すると正の数、負の数、ゼロの時、文字列いずれの場合も何も表示しないことになり、セルにどんな値を入れても何も表示されません。(数式のエラー表示はさすがに表示されますけど)

なので文字の時だけ表示するような表示形式は
;;

としておけば正の数、負の数、ゼロの時は表示なし、文字列は指定なしとなります。

次に各記号の意味についてです。

_      スペース(空白) _の代わりに半角スペースでもいい
¥     通貨スタイルの¥マーク
*     後ろの文字を繰り返してセル内を埋める
#    数値(の1桁) ゼロは表示しません
,     桁区切り
.     小数点
0    ゼロ ゼロ以外の数値はその数値、そうでなければ0を表示する
“”  ”と”の間の文字列を表示します。
?     少数部分の余分な0をスペースとして表示
@   文字列 入力された文字列そのもの

大変ですが、これであとは解読するだけになりました。

まずは正の数の場合を見てみます。
_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

  • _        この正の部分は入力値(計算結果)が正の数の場合左にスペースを空けます。
  • ¥       通貨スタイルの¥を表示します。
  • *        値が表示できる領域までスペースで埋めます。*の後ろにスペースがある。
  • #,##0.00    4桁以上なら桁区切りのカンマを表示し、小数点部分は必ず2桁表示します。
  • _       最後にスペースを取ります。

次に値が負の場合
_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

負の場合は正の場合と違うところは – (マイナス)が入っているだけなので正の数の場合と同じ表示形式で数値の頭にマイナスがつくだけです。

値が0(空白)の時は、
_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

  • _ ¥*    ここまでは正の場合、負の場合と同じです。スペース¥マーク、スペースで埋め尽くす
  • “-“      ハイフンを表示。””は任意の文字列を表示します。
    ??       0なので小数点以下はありませんが?2つなので2つスペースを表示します。
  • _       最後にスペースを取ります。

値が文字列の時は
_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* “-“??_ ;_ @_

  • _ @_     前後にスペースを取って文字を表示

となります。

自分で手入力ですべて設定する場合
_と¥の間にスペースが必要だったり_と@の間にもスペースが必要だったりします。

表示形式 日付(和暦)

表示形式はとっくにご紹介済みだと思っていましたがカテゴリ分けをしていたらまだだったことに気づいて慌てて書いています。どこかに重複していませんかね。

以下のような表で生年月日が入っています。この西暦/月/日の表示は入力したとおりに表示されています。この表示を和暦で表示したいと思います。

1996/5/7を平成8年5月7日と打ち直してもいいのですが(本当はよくないです。自動で日付の表示形式になっているので打ち直しても元の1996/5/7に戻ってしまいます。)1996年を自分で和暦に変換する必要がありますね。

1989年を境に平成と昭和の自動変換は私の処理能力では追いつきません。対象セルを選択し表示形式を変更することで和暦に変更したいと思います。

和暦に変更したいセルを選択して、ホームタブの数値の数値の書式の右にある下向き三角▼をクリックします。その他の表示形式をクリックします。

セルの書式設定のダイアログボックスが表示され増す。選択したセルは元々日付だったので分類が日付になっています。右側の種類の中にはスクロールして探しても和暦がありません。

左の分類の一番下のユーザー定義をクリックします。

現在の表示形式の西暦4桁/月/日の形である
yyyy/m/d
が選択されておりサンプルのところ現在の表示形式で表示されています。

おそらくすぐ上に[$-ja-JP]ggge”年”m”月”d”日”があると思いますのでクリックします。サンプルが和暦に代わっているのでOKボタンをクリックしてください。

表示が和暦に変更されました。

セルB2の値は1996/5/7のままで見え方だけ和暦になっています。表面上は文字ですが計算できる日付(数値)のままです。ちなみに年齢はDATEDIF関数で計算しています。

ggge”年”m”月”d”日”ですが””で囲まれているところは文字としてそのまま表示されるところになります。mは月dは日を表します。gggeが和暦の元号付きの年表示です。

e          数値だけ
ge       和暦のアルファベット付き数値 平成ならH27とか
gge 和暦の漢字1文字付き数値   平成なら平27とか
ggge 和暦の漢字と数値                            平成なら平成27とか

になります。また見ていただいたらわかる通り月と日は1桁表示と2桁表示が混じっています。ふつうはそうでいいと思うのですが表で並べると表示のがたつきが気になります。

m   月の1桁と2桁が混じる
mm     1桁の月は頭に0を付けて2桁表示にする

d   日の1桁と2桁が混じる
dd       1桁の日は頭に0を付けて2桁表示にする

gge”年”mm”月”dd”日生まれです”
という表示形式にすればがたつきがなくなり見やすくなるかもしれません。頭の[$-ja-JP]は省略可能です。

でも、まだ気になる人は気になりますね。平成1桁生まれの人はやっぱり数値1文字分文字列が短いです。

gge”年”mm”月”dd”日生まれです”のggeが和暦の漢字1文字付き数値でeが数値を表していますのでggeをggeeとeを1つ増やしてください。

これでうまくいきました。日別月間 万年スケジュールでも少し触れていますが日付から自動で曜日を表示形式で表示することが可能です。

aaa     和暦の曜日1文字 火
aaaa   和暦の曜日3文字 火曜日

となっています。

gggee”年”mm”月”dd”日””(“aaaa”)”

このように表示形式を変更してみてください。

曜日が()に囲まれて表示されています。

曜日はdddまたはddddでも表示されますが[$-ja-JP]を省略した場合は英語表記(Tuesdayのような)になりますので和暦の場合はaaaかaaaaをご利用になるのをお勧めします。

エクセルのエラー表示

数式を入力した後や、データを入力した後にエラーメッセージが表示されることがあります。エラーメッセージにはそれぞれ意味がありますので意味を理解しておけば修正作業も簡単にになると思われます。

#####

セルの幅より長い桁数の数値が入力されている場合や、日付や時刻で負の数値が入力されている場合に表示されます。文字がセルの幅より広い場合はセルの幅の分の文字が表示されエラーは発生しません。

セルの幅が不足しているので#####が発生している列見出しと隣の列の列見出しの間でダブルクリックし列の幅を最適化します。この状態でなお、#####が表示されている場合は日付や時刻で負の数値が入力されていると思われるので値と表示形式をよく確認しましょう。

#DIV/0!

数式で0による割り算が行われた場合。セル参照先が空白やゼロになっていないか確認する

#VALUE!

引数やオペランドの種類が正しくない場合に表示されます。概ね、数式が正しくない場合に表示されます。

引数が正しくない場合の例として
=IF(“a”,”正しい”,”正しくない”)
のように本来、論理式を入れるべきところに文字列を入れてしまった場合など。

#REF!

参照先のセルがない場合などに表示されます。数式をコピーしたり、列や行を削除した場合などに多いようです。
1つ上のセルの値を参照する式を1行目にコピーするとか、左隣の列の値を参照しているのに列を削除してしまい、数式自体が左に積めらた場合などです。

#N/A

数式で参照の対象が見つからないことを示しています。よくあるのがVLOOKUP関数などで参照する値が参照先にない場合に表示されます。

#NUM!

数式または関数の数値がおかしい場合に表示されます。SQRT関数はルート算出してくれますが、マイナス値はダメなようで#NUM!のエラーが表示されます。
=SQRT(-4)

#NULL!

交差演算式に指定された2つのセル範囲に交差部分がない場合などに表示されます。参照演算子の3つ目の項目参照してください。

Webクエリ

エクセルにWebの情報を貼り付けエクセルで情報を更新することができます。

定期的にWebのデータをエクセルにコピーして、計算しているような場合大変便利です。ただし、IDパスワードなどでログインが必要なページには対応していないようです(毎度ログインすれば可能だと思います)。

簡単な方法をご紹介します。今回は気象庁の天気予報で試してみます。天気予報は毎日更新されていますし、何らかのデータを取得するのに最適かもしれません。

まずは定期的にデータを取得したいページを表示します。今回は奈良県の天気です。

http://www.jma.go.jp/jp/yoho/335.html

サイト内の適当なところをドラッグしてコピーします。これは本番の取得したいデータでなくても大丈夫です。

エクセルのデータを張り付けたい範囲の左上角のセルを選択します。ホームタブのクリップボードの貼り付けのボタンの下の下向き三角▼をクリックします。

プルダウンメニューの更新可能なWebクエリをクリックします。

別ウィンドウ(ダイアログボックス)が出てきて先ほどの気象庁の奈良県の天気のページが表示されます。

うまくスクロールしたり、ダイアログボックスを拡大したりして目的の情報が表示されるようにします。ダイアログボックス内に黄色い(オレンジ?)の右向き矢印の四角いアイコンが表示されていますね。目的の情報に合う矢印のアイコンをクリックします。

範囲が選択されますこの部分のテキストデータが取り込まれます。右下の取り込みボタンをクリックします。

少し時間がかかりますが情報が取り込まれました。

見た目的に元のサイトと同じような感じですね。このままではデータが使いにくい場合はここを触らずに必要な情報のあるセルの情報をセル参照しておけばいいですね。

これで取り込みは完成です。ファイルを保存しておいてください。

Webクエリの更新

Webクエリは自動更新ではありませんので新しい情報を取得する際は更新作業が必要です。Webクエリの表の中の任意のセルを選択します。そして右クリックします。プルダウンメニューが表示されますのでクエリの編集をクリックします。

先ほどと同じダイアログボックスが表示されます。範囲は既に選択済みになっているので取り込みボタンをクリックします。

これでエクセルの情報が更新されます。毎度、ブラウザを起動してサイトを表示し、範囲選択、コピー貼り付けよりスマートだと思います。

ただし、すべてのページのすべての部分がエクセルからWebクエリとして取り込めるわけではないようです。

HTMLとして出力されているものはWebクエリとして取り込めるようですが、JavaScriptで出力されているような情報(Webページでソースの表示で表示されないような情報)は選択の黄色い矢印が表示されません。

 

ウィンドウ枠の固定

データ量の多いエクセルの表を扱うときには、ウィンドウ枠の固定というものが便利です。

50行、100行と行数の多い表はパソコンの画面ですべてのデータが一覧で見られない場合があります。

もちろんスクロールすればすべての行を見ることができますが、その場合1行目や、2行目にある表の見出しが隠れてしまい値の見出しがわからなくなってしまいます。このようなときにウィンドウ枠の固定を利用します。

いつでも表示しておきたい行の1行下を行選択します。今回は1行目を常に表示しておきたいので2行目を行選択します。

windowwaku

表示タブのウィンドウのウィンドウ枠の固定をクリックします。3つメニューが表示されますので一番上のウィンドウ枠の固定をクリックします。

windowwaku_1

選択した行からの上にある行は縦にスクロールしても常に表示されたままです。今回は縦にスクロールしても1行目は常に表示されています。

windowwaku_2

ウィンド枠の固定を解除する場合は再度ウィンドウ枠の固定をクリックしてウィンドウ枠の固定の解除をクリックします。ウィンドウ枠の固定の所が解除に替わっています。

windowwaku_3

また、列数の多い横長の表で、A列やB列などに見出しがあり、この見出しをいつでも表示しておきたい場合は固定したい列の右隣の列を選択してからウィンドウ枠の固定をします。今回はA列を常に表示しておきたいのでB列を列選択します。

windowwaku_4

ウィンド枠の固定をクリックします。横にスクロールしてもA列は常に表示されています。

windowwaku_5

さらに行も列も固定したい場合は見出し行、と見出し列の交差するセルの右下のセルを選択した後、ウィンドウ枠の固定を実施します。今回は1行目とA列を表示しておきたいのでセルB2をクリックし選択しておきます。

windowwaku_6

この状態でウィンドウ枠の固定をすると縦横にスクロールしても1行目とA列は常に表示されたままになっています。

windowwaku_7