エクセル技道場>編集

エクセル技道場 HOME
エクセルの技 目次
シート
関数
名前定義
ツールバー
セル選択
ファイル
条件付き書式
書式設定
編集
印刷
入力規則
マクロ
グラフ
ピボットテーブル
エクセル・ショートカット
エクセル関数語源リスト
エクセル関数引数リスト
  1. 一行おきに参照
  2. 3列のデータを1列に
  3. 連番の入力
  4. フィルハンドルのダブルクリック
  5. 連番の入力2
  6. 連番の入力3
  7. 土日を除く日付
  8. 数値のみ削除
  9. 円単位の表を千円単位に変換  
  10. 同一データの入力  
  11. 置換  
  12. 連続データの作成(時刻)  
  13. 置換で文字列削除  
  14. セル内改行  
  15. セル内改行に置換  
  16. 数式でセル内改行  
  17. セル内改行しているセルを参照  
  18. 行列の入れ替え  
  19. オートフィルタ  
  20. オートフィルタで自動連番  
  21. 区切り位置で日付に  
  22. 置換で時刻として認識させる  
  23. 123,456円(文字列)を数値にするには  
  24. 日付にするには  
  25. 選択範囲の一部解除  
  26. 列見出しを並べ替えの対象にしない  
  27. 8桁数値を日付データに変換  
  28. 行削除  
  29. オートカルク  
  30. 重複するレコードは無視する  
  31. 複数行の行挿入  
  32. 空白セルの削除  
  33. 計算式(関数の複写)  
  34. 並べ替えがうまくいかない  
  35. 他のシートのセルを一行おきに参照  
  36. 6桁日付をYY/MM/DDにするには?  
  37. 別シートにデータを抽出  
  38. テキストボックスにセルの値を表示  
  39. ハイパーリンクをクリックするとワークブックが非表示に[XL97]  
  40. オートコレクト  
  41. 複数行の挿入  
  42. 小数点位置を固定  
  43. オートコレクト(時刻の入力を簡単に)  
  44. 二行おきにセル参照  
  45. * ,? , ~の置換

一行おきに参照

【問題】
 一行おきにセルを参照するには、どうすれば簡単にできるでしょうか?
 例えばB2:B100に

=A2
=A4
=A6
=A8
=A10
=A12



=198

といった感じにしたいのです。

【回答】
1.B2に '=A2 、B3に '=A4 と入力(あたまにシングルクォーテーションをつけます)
2.B2:B3を選択してからフィルハンドルをB100までドラッグ
3.メニュー[データ]-[区切り位置]
4.[完了]ボタンをクリック

【解説】
 フィルハンドルとは、選択範囲の右下隅にある黒い四角形のことです。
 マウスポインタをこのフィルハンドルに重ねると+のマークに変化します。+のマークに変化してからドラッグします。

 フィルハンドルが表示されないという方は、メニュー[ツール]-[オプション]の[編集]タブで、[ドラッグ アンド ドロップ編集を行う]にチェックを入れてみてください。

 あたまにシングルクォーテーションをつけないで、B2に =A2 、B3に =A4 と入力してからフィルハンドルをドラッグすると、以下のようになります。

=A2
=A4
=A4
=A6
=A6
=A8

3列のデータを1列に

【問題】
 A1:C10に以下のようなデータが入力されています。

1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
16 17 18
19 20 21
22 23 24
25 26 27
28 29 30

 これをD列に1行から順にA1,B1,C1,A2,B2,C2,A3,B3,C3,……A10,B10,C10の順番にデータを並べたいのですが、何か簡単にできるよい方法はないでしょうか。

1
2
3



28
29
30

【回答】

1.セルD1に '=A1 セルD2に '=B1 セルD3に '=C1 と入力
(あたまに ' をつけます)
2.D1:D3を選択しフィルハンドルをD30までドラッグ
3.そのまま(D1:D30が選択された状態)、メニュー[データ]-[区切り位置]
4.[完了]ボタンをクリック

【解説】
 上記手順で、D1:D30には以下のような式が入力されます。

=A1
=B1
=C1
=A2
=B2
=C2



=A10
=B10
=C10

 これを参照式ではなく値にしたい場合には、以下の手順です。

1.D1:D30を選択
2.Ctrl+C (コピー)、
3.右クリック−[形式を選択して貼り付け]
4.[値]にチェック
5.Enter

 マウスを使うと以下の手順で値のみコピーできます。

1.D1:D30を選択
2.右クリックで少し右方向にドラッグして、また元の位置に戻してドロップ
3.[ここに値のみをコピー]

 フィルハンドルとは、選択範囲の右下隅にある黒い四角形のことです。
 マウスポインタをこのフィルハンドルに重ねると+のマークに変化します。+のマークに変化してからドラッグします。

 フィルハンドルが表示されないという方は、メニュー[ツール]-[オプション] の[編集]タブで、[ドラッグ アンド ドロップ編集を行う]にチェックを入れてみてください。

連番の入力

【問題】
 A1:A10に1〜10の連番を入力する簡単な方法はなんでしょうか?

【回答】
1.セルA1に1を入力
2.セルA1のフィルハンドルをセルA10までドラッグ
3.Ctrlキーを押しながらドロップ

【解説】
 最後にCtrlキーを押しながらドロップしないと、A1:A10に全て1が入力されます。

 例えば、上記手順1.でセルA1に5を入力すると、5〜14の連番が入力されます。

 フィルハンドルとは、選択範囲の右下隅にある黒い四角形のことです。
 マウスポインタをこのフィルハンドルに合わせると白抜きの太い+の形から細い+の形に変化します。

フィルハンドルのダブルクリック

【問題】
 A1:B10にデータが入っています。
 セルC1に =A1*B1 という数式が入っています。
 この式をC2:C10にコピーしたいのですが、もっとも簡単な方法はなんでしょうか?

3 2 =A1*B1
2 9
5 6
8 1
1 3
3 4
7 3
6 2
4 5
7 1

【回答】
 セルC1のフィルハンドルをダブルクリック

【解説】
 フィルハンドルとは、選択範囲の右下隅にある黒い四角形のことです。
 マウスポインタをこのフィルハンドルに合わせると白抜きの太い+の形から細い+の形に変化します。
 細い+にしてからダブルクリックします。

連番の入力2

【問題】
 A1:A1000に1〜1000の連番を入力する簡単な方法はなんでしょうか?

【回答】
1.セルA1に1を入力
2.セルA1を選択した状態で、メニュー[編集]-[フィル]-[連続データの作成]
3.[列]にチェック
4.[停止値]に 1000 を入力
5.Enter

【解説】
 もちろん、以下の手順でもできます。

1.セルA1に1を入力
2.セルA1のフィルハンドルをセルA1000までドラッグ
3.Ctrlキーを押しながらドロップ

 しかし、連番の数が多い場合には、メニュー[編集]-[フィル]-[連続データの作成]を活用した方が簡単になります。

 [連続データの作成]をよく使用する方は、コマンドをメニューバーかツールバーに追加しておくと便利です。
 メニューバー/ツールバーのカスタマイズについては、以下のページをご覧になってみてください。

【エクセル技道場】−ツールバー

連番の入力3

【問題】
 A1:E10に1〜50の連番を入力する簡単な方法はなんでしょうか?

【回答】
1.セルA1に1を入力
2.A1:E10を選択
3.メニュー[編集]-[フィル]-[連続データの作成]
4.[列]にチェックが入っていることを確認して、Enter
5.メニュー[編集]-[フィル]-[連続データの作成]
6.[行]にチェックを入れて、[増分値]に10を入力して、Enter

【解説】
 [連続データの作成]をよく使用する方は、コマンドをメニューバーかツールバーに追加しておくと便利です。
 メニューバー/ツールバーのカスタマイズについては、以下のページをご覧になってみてください。

【エクセル技道場】−ツールバー

土日を除く日付

【問題】
 A列に2001年の土日を除く日付を入力する簡単な方法はなんでしょうか?

【回答】
1.セルA1に 2001/1/1 と入力
2.セルA1を選択
3.メニュー[編集]-[フィル]-[連続データの作成]
4.[列]、[日付]、[週日]にチェック
5.[停止値]に 2001/12/31 と入力
6.Enter

【解説】
 セルA1に日付を入力してから、セルA1のフィルハンドルを右クリックでドラッグ−[連続データ(週日単位)]をクリックという方法もあります。

数値のみ削除

【問題】
 大きな表内に文字、数値、数式が混在しているのですが、数値のみ消去するにはどうしたらよいでしょうか?

【回答】
1.数値を消したい範囲を選択
2.Ctrl+G(メニュー[編集]-[ジャンプ])
3.[セル選択]ボタンをクリック
4.[定数]にチェック
5.[文字]、[論理値]、[エラー値]のチェックをはずし[数値]だけにチェックを入
れる
6.Enter
7.Deleteキー

【解説】

 マクロの場合には、以下のようになります。
 範囲を選択してから実行します。

Sub Test()
  On Error Resume Next
  Selection.SpecialCells(xlCellTypeConstants, 1).ClearContents
End Sub

円単位の表を千円単位に変換

【問題】
 あるシート内の数値全てを1/1000にするにはどうしたら良いでしょうか?
 つまり円単位のデータを千円単位に一括して変換したいのです。

【回答】
1.どこか未入力のセルに 1000 を入力
2.Ctrl+C (コピー)
3.Ctrl+G (メニュー[編集]-[ジャンプ])
4.[セル選択]ボタンをクリック
5.[定数]にチェック
6.[文字]、[論理値]、[エラー値]のチェックをはずし[数値]だけにチェック
7.Enter
8.メニュー[編集]-[形式を選択して貼り付け]
9.[値]と[除算]にチェック
10.Enter
11.手順1.で1000を入力したセルを選択してDelete

【解説】
 マクロの場合には、以下のようになります。

Sub Test()
  Dim Rng As Range
 
  On Error Resume Next
  Set Rng = Cells.SpecialCells(xlCellTypeConstants, 1)
 
  With Cells.Find(What:="", LookIn:=xlFormulas, LookAt:=xlWhole)
    .Value = 1000
    .Copy
    Rng.PasteSpecial Paste:=xlValues, Operation:=xlDivide
    .ClearContents
  End With
  Set Rng = Nothing
End Sub

同一データの入力

【問題】
 A1:A1000に同一データ(例えばAAA)を入力するにはどうしたら簡単にできるでしょうか。

【回答】
1.Ctrl+G(メニュー[編集]-[ジャンプ])
2.[参照先]に A1:A1000 と入力
3.Enter
→A1:A1000が選択される
4.AAAをCtrl+Enterで入力

【解説】
 A1:A1000のように広い範囲を選択する方法は他にも

・名前ボックスに A1:A1000 と入力してEnter
・セルA1を選択して名前ボックスに A1000 と入力してShift+Enter

などがあります。

 既にセルA1に AAA が入力されていて、それをA1:A1000に入力する場合には、上記4.で Ctrl+D とします。

 Ctrl+Dのかわりに、F2キーを一回押してから、Ctrl+Enterというやり方もあります。

置換

【問題】
 B列に以下のように住所を入力しました。

大阪市天皇寺区○○○町一丁目
大阪市天皇寺区○○○町二丁目
大阪市天皇寺区△△△町三丁目

 1000位の住所を入力した後で、「天皇寺区」ではなく「天王寺区」が正しいこ
とに気がつきました。どうやって修正したら良いでしょうか?

大阪市天王寺区○○○町一丁目
大阪市天王寺区○○○町二丁目
大阪市天王寺区△△△町三丁目

【回答】
1.B列を選択
2.Ctrl+H (メニュー[編集]-[置換])
3.[検索する文字列] に 大阪市天皇寺区 と入力
4.[置換後の文字列] に 大阪市天王寺区 と入力
5.[完全に同一なセルだけを検索する] にチェックが入っていないことを確認して
[すべて置換]ボタンをクリック

【解説】
 [すべて置換]を実行する際は、一つのセルだけを選択しているかセル範囲を選択しているかで実行結果が異なります。

 一つのセルだけを選択している場合には、全てのセルが置換の対象になります。

 今回のように範囲を選択してから[すべて置換]を実行すると、その選択した範囲内が置換の対象になります。

連続データの作成(時刻)

【問題】
 A1:A3601に00:00:00から一秒刻みの時刻
 B1:B3601に1,2,3・・・3599,3600,3601という連番
 セルD1に =VLOOKUP(C1,A:B,2,FALSE) という式

 これで、セルC1に時刻を入力すると、セルD1に対応する値が返ります。

    A     B      C     D
00:00:00 1 00:01:21 82 ← =VLOOKUP(C1,A:B,2,FALSE)
00:00:01 2
00:00:02 3
00:00:03 4
00:00:04 5

 A列のデータはどのように作成したら良いでしょうか?

 セルC1に以下の時刻を入力した時にセルD1がエラーにならないように作成してみてください。

00:01:13
00:01:21

【回答】
1.セルA1に 0:0:0 を入力
2.メニュー[編集]-[フィル]-[連続データの作成]
3.[列]、[加算]にチェック、[増分値]に 0:0:1 を入力、停止値に 1:0:0 を入力
4.Enter
5.A列を選択
6.メニュー[データ]-[区切り位置]
7.[完了]ボタンをクリック

【解説】
 以下のような式で作成すると、=VLOOKUP(C1,A:B,2,FALSE) の結果がエラーになる可能性があります。

=TEXT(A1+"0:0:1","hh:mm:ss")*1
=TIMEVALUE(TEXT(A1+"0:0:1","hh:mm:ss"))
=VALUE(TEXT(A1+"0:0:1","hh:mm:ss"))
=TIME(0,0,ROW()-1)
=TIMEVALUE("0:0:"&ROW()-1)

置換で文字列削除

【問題】
 B列に以下のように住所が入力されています。全て大阪市天王寺区の住所です。

大阪市天王寺区○○○町一丁目
大阪市天王寺区○○○町二丁目
大阪市天王寺区△△△町三丁目

 これを「大阪市天王寺区」を消して以下のようにするにはどうしたら良いでしょうか?

○○○町一丁目
○○○町二丁目
△△△町三丁目

【回答】
1.B列を選択
2.Ctrl+H (メニュー[編集]-[置換])
3.[検索する文字列] に 大阪市天王寺区 を入力
4.[完全に同一なセルだけを検索する] にチェックが入っていないことを確認して
[すべて置換]ボタンをクリック

【解説】
 上記手順では、[置換後の文字列] に何も入力しないで置換を実行しています。

 [すべて置換]を実行する際は、一つのセルだけを選択しているかセル範囲を選択しているかで実行結果が異なります。

 一つのセルだけを選択している場合には、全てのセルが置換の対象になります。

 今回のように範囲を選択してから[すべて置換]を実行すると、その選択した範囲内が置換の対象になります。

セル内改行

【問題】
 セル内に以下のように エクセル技道場 と入力しています。

エクセル技道場

 これを以下のように エクセル と 技道場 の間に改行を入れるにはどうしたら良いでしょうか?

エクセル
技道場

【回答】

1.F2キー
→編集モードに
2.改行したい位置にカーソールを置いてから、Alt+Enter
3.Enter

【解説】
 Alt+Enterでセル内で改行することができます。

 表を作成していて、データ自体は幅をとらないのに、列見出しのセルの文字列が長いので列の幅を長くとっている、といった場合にAlt+Enterで途中に改行を入れると列の幅を狭くすることができます。

セル内改行に置換

【問題】
 A1:A3に以下のデータが入っています。

A,B
C,D
E,F

 これを , をセル内改行に置換して以下のようにするにはどうしたら良いでしょうか?

A
B
C
D
E
F

【回答】
1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列] に , を入力
3.[置換後の文字列]で Ctrl+J
4.[すべて置換]ボタンをクリック

【解説】
 上記手順3.で、 Ctrl+J とするのは一回だけです。何回も Ctrl+J とするとその数だけセル内改行されます。

 セル内改行に置き換えるセルの数が少ない場合には、F2キーで編集モードにし , を選択してからAlt+Enterでも良いのですが、セルの数が多い場合には、今回の技が便利です。

 多数のセルのセル内改行を削除したい、という場合には、以下の手順です。

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列] で Ctrl+J
3.[置換後の文字列]には何も入力しない
4.[置換]ボタンをクリック

数式でセル内改行

【問題】
 A1:B1に以下のデータが入っています。

エクセル 技道場

 これを数式を使って、別のセルに以下のようにセル内改行させて表示させるには、どうしたら良いでしょうか?

エクセル技道場

【回答】
1.セルに以下の式を入力
=A1&CHAR(10)&B1
2.Ctrl+1 (メニュー[書式]-[セル])
3.[配置]タブで[折り返して全体を表示する]にチェック
4.Enter

【解説】
 例えば以下の手順でセル内改行させて、別のセルに =CODE(A2) と入力すると結果は 10 になります。
 CODE関数は、セル内の先頭文字のコード番号を返します。
 つまり、改行のコード番号は 10 ということです。

1.セルA2を選択
2.F2キー
→編集モードに
3.Alt+Enter
→セル内で改行
4.Enter

 CHAR関数は、コード番号に対応する文字を返します。
 &CHAR(10)& で改行をかませていることになるわけです。

 エクセル・技道場 と表示されますが、[配置]タブで[折り返して全体を表示する]にチェックを入れることでちゃんと改行されるようになります。
 他に[両端揃え]や[均等割付]でも改行されるようになります。

セル内改行しているセルを参照

【問題】
 セルA1にセル内改行を含む文字列が入力されています。

エクセル
技道場

 他のセルに=A1とすると

エクセル・技道場

のようにセル内改行が「・」と表示されます。
 これを以下のようにセル内改行を「/」に置き換えるにはどういう数式にすれば良いでしょうか?

エクセル/技道場

【回答】

=SUBSTITUTE(A1,CHAR(10),"/")

【解説】
 Alt+Enterでセル内改行させることができます。

 セル内改行を含む文字列が入っているセルを参照し、

エクセル技道場

のように「・」を消して表示させるには、

=CLEAN(A1)

 または、以下の数式です。

=SUBSTITUTE(A1,CHAR(10),)

行列の入れ替え

【問題】
 横方向に入力してしまったデータを縦方向にするにはどうしたら良いでしょうか?

【回答】
1.行と列を入れ替えたいセル範囲を選択
2.Ctrl+C (コピー)
3.コピー元のセル範囲と重複しないセルを選択
4.メニュー[編集]-[形式を選択して貼り付け]
5.[行列を入れ替える]にチェック
6.Enter

【解説】
 TRANSPOSE関数という行列を入れ替える関数もあります。
 例えばA1:F2にデータが入っていて、これを行列を入れ替えてA5:B10に表を作成するには、A5:B10を選択してから、=TRANSPOSE(A1:F2) をCtrl+Shift+Enterで入力します。

オートフィルタ

【問題】
 オートフィルタを使って特定の項目だけを表示させるには以下の手順です。

1.リストの範囲内のセルを選択して、メニュー[データ]-[フィルタ]-[オートフィルタ]
2.[オートフィルタ矢印](列見出しの隣にある矢印)をクリック
3.表示させたい項目をクリック

 これをもっと簡単な方法で実現できないでしょうか?

【回答】
 [オートフィルタ]ボタンをメニューバーあるいはツールバーに登録しておきます。

1.メニューバーの上で右クリック−[ユーザー設定]
2.[コマンド]タブの[分類]で[データ]をクリック
3.[コマンド]の[オートフィルタ]ボタンをドラッグしてメニューバーまたはツールバー上でドロップ

 これで、表示させたい項目が入っているセルのどれかを選択してから[オートフィルタ]ボタンをクリック、で特定項目だけを表示させることができるようになります。

【解説】
 メニューバー/ツールバーの設定関係については、以下のページをご覧ください。

【エクセル技道場】−ツールバー
http://www2.odn.ne.jp/excel/waza/toolbar.html

 オートフィルタ矢印をクリックして表示されるドロップダウン リスト ボックスに表示されるデータ数の制限は、

・XL95までは250まで
・XL97からは1000まで

となっています。

 詳しくは以下のページをご覧ください。

[XL95] オートフィルタのドロップダウンに表示されるデータの順番について
http://support.microsoft.com/default.aspx?scid=kb;ja;406389

[XL2002]オートフィルタのドロップダウンリストに表示可能なデータ数
http://support.microsoft.com/default.aspx?scid=kb;ja;401642

オートフィルタで自動連番

【問題】
 オートフィルタを実行後、表示されている項目で連番をふるにはどういう数式を使えば良いでしょうか?
 A1:B1は見出しでA2:B20にデータが入っているとします。C列に表示されている項目だけで連番をふることにします。

【回答】
1.C2:C20を選択
→C2がアクティブセルでC2:C20を選択している状態

2. =SUBTOTAL(3,$A$2:A2) をCtrl+Enterで入力

【解説】
 SUBTOTAL関数は、リストを抽出した結果として非表示になっている行は無視して計算します。このため、抽出されたリストに表示されているデータだけを対象に計算することができます。
 SUBTOTAL(集計方法, 範囲1, 範囲2, ...) という引数をとります。

 集計方法としては以下の11種類を指定することができます。

1 AVERAGE 関数
2 COUNT 関数
3 COUNTA 関数
4 MAX 関数
5 MIN 関数
6 PRODUCT 関数
7 STDEV 関数
8 STDEVP 関数
9 SUM 関数
10 VAR 関数
11 VARP 関数

 今回の式 =SUBTOTAL(3,$A$2:A2) では、集計方法として 3 (COUTA関数)を指定しています。

 COUNTA関数は、すべてのデータ型の値が計算の対象になります。すなわち、数値だけでなく文字列などが入力されているセルもカウントします。

 範囲を $A$2:A2と、絶対参照と相対参照の組み合わせにしているので、

セルC2 =SUBTOTAL(3,$A$2:A2)
セルC3 =SUBTOTAL(3,$A$2:A3)
セルC4 =SUBTOTAL(3,$A$2:A4)

といった感じでC列の数式は、A2からその行のA列までを範囲にすることになります。

区切り位置で日付に

【問題】
 A列に、日付と時間が 月/日/年 時刻 といった感じで入力されています。

5/12/00 5:45PM
5/13/00 5:26PM
5/14/00 5:37PM

 これを日付だけ表示させたいのですが、シリアル値として認識されていないようなので、表示形式ではできません。時間の部分のデータは不要なのでこれを削除して、日付だけにするにはどうしたら良いでしょうか?

【回答】
 [データ]-[区切り位置]で実現します。

1.列番号Aをクリックして、A列を選択
2.メニュー[データ]-[区切り位置]
3.[カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[区切り文字]のところで、「スペース」にチェックを入れる
5.[次へ]ボタンをクリック
6. 5/12/00 のように日付の入った、一番左の列が選択されている状態で、[列のデータ形式]で[日付]をクリックし、[MDY]を選択
7.[列のデータ形式]で 5:45PM といったデータが入っている列を選択して、[削除する]をクリック
8.[完了]ボタンをクリック

置換で時刻として認識させる

【問題】

 以下のようにデータを入力しました。

5:45PM
5:26PM
5:37PM

 しかし、これでは時刻として入力されていません。=HOUR(A1) や =MINUTE(A1) の結果は、#VALUE! となります。

 既に上記のように入力してしまったデータを時刻として認識させるには、どうしたら良いでしょうか?

【回答】

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列]に PM と入力
3.[置換後の文字列]に  PM と入力(PMの前にスペースが入っています)
4.[すべて置換]ボタンをクリック

【解説】

 5:45PMと入力すると文字列として認識されます。
 5:45 PMとスペースを入れて入力すると時刻として認識されます。
 「PM」を「 PM」に置き換えてやることで時刻として認識されるようになるわけです。
 5:45AMといったデータがある場合には、「AM」を「 AM」に置き換えます。

 5:45 PMではなくて、5:45 Pとしても時刻として認識されます。

123,456円(文字列)を数値にするには

【問題】

 以下のようなデータを入力しました。

123,456円
111,111円
100,000円

 文字列として入力されていますが、これを数値にするにはどうしたら良いでしょうか?
 また、数値に変換しますが、表示については円を最後につけたままにしたいのです。

【回答】

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列]に 円 と入力
3.[置換後の文字列]に何も入力しない
4.[すべて置換]ボタンをクリック

 これで数値に変換され、以下のように表示されます。

123,456
111,111
100,000

1.データが入っている範囲を選択
2.Ctrl+1 (メニュー[書式]-[セル])
3.[表示形式]タブの[分類]で ユーザー定義 を選択
4.[種類]に #,##0"円" と入力
5.Enter

 これで以下のように最後に円をつけて表示されます。

123,456円
111,111円
100,000円

【解説】

 関数で数値に転換するには、以下のような数式を使います。

=SUBSTITUTE(A1,"円","")*1
=VALUE(LEFT(A1,LEN(A1)-1))

日付にするには

【問題】

 以下のようなデータを入力しました。

2001.1.1
2001.2.15
2001.3.18

 これを日付として認識させるには、どうしたら良いでしょうか?

【回答】

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列]に . と入力
3.[置換後の文字列]に / と入力
4.[すべて置換]ボタンをクリック

【解説】

 データが一列だけであれば、[データ]-[区切り位置]でもできます。
 A列に日付が入っているとします。

1.列番号Aをクリックして、A列を選択
2.メニュー[データ]-[区切り位置]
3.[次へ]ボタンをクリック
4.[次へ]ボタンをクリック
5.[列のデータ形式]で日付をクリックし、[YMD]を選択
6.[完了]ボタンをクリック

 置換や区切り位置を実行後、エラー値 ##### が表示されることがあります。こ
の場合には列幅を広くすると、日付が表示されるようになります。

選択範囲の一部解除

【問題】

 Ctrlキーを押しながら奇数行のセル範囲を選択している途中で...

セルA1からF1
セルA3からF3
セルA5からF5
    :

 セルA21からF21を選択中に、間違えてセルA22からF22も選択してしまいました。

 セルA22からF22のみを選択解除するにはどうしたら良いでしょうか?

【回答】

 間違えてA21:F22を選択してしまった場合、アクティブセルはセルA21だと思います。
 Shift+↑
でA21:F21が選択範囲になります。

 A21:F21を選択しないで間違えてA22:F22を選択してしまった場合、アクティブセルはセルA22だと思います。
 
 Shift+↑
 Shift+Tab
 Shift+↑

でA21:F21が選択範囲になります。尺取虫のような動きですね。
 A21:F21を選択した後で、間違えてA22:F22も選択してしまった場合も上記と同じ手順になります。ただしこの場合、A21:F21が二重に選択された状態になってます。

【解説】

 以下の手順でも選択範囲の一部解除ができます。

1.Ctrl+F3(メニュー[挿入]-[名前]-[定義])
2.[名前]に例えば test と入力
3.[参照範囲]で正しい選択範囲に訂正
4.Enter
5.[名前ボックス]で test を選択

列見出しを並べ替えの対象にしない

【問題】

 A1:A6に以下が入力されています。

beer
kirin
asahi
sapporo
suntry
orion

 A1:A6内のセルのどれかを選択して、[標準]ツールバーの[昇順で並べ替え]ボタンをクリックすると、以下のように並べ替えされます。

asahi
beer
kirin
orion
sapporo
suntry

 しかし、セルA1にあったbeerは並べ替えの対象にしたくありません。
 A1:A6内のセルのどれかを選択してからメニュー[データ]-[並べ替え]で[範囲の先頭行]を[タイトル]にすれば、セルA1は並べ替えの対象になりませんが、面倒です。
 [昇順で並べ替え]ボタンをクリックしてもセルA1が並べ替えの対象にならないようにするにはどうしたら良いでしょうか?

【回答】

 セルA1だけ、中央揃え、太字、イタリック、アンダーラインなどを設定しておきます。

【解説】

 列見出しにデータのセルとは異なるフォント、配置、書式、パターン、または罫線を使用しておくことで、列見出しが並べ替えの対象になりません。

 太字はCtrl+B、イタリックはCtrl+I、アンダーラインはCtrl+Uで設定できます。

8桁数値を日付データに変換

【問題】

 A列に以下のように日付を八桁で入力しています。

20010501
20010505
20010510

 これを以下のように日付の形式に変換するにはどうすれば良いでしょうか?

2001/5/1
2001/5/5
2001/5/10

【回答】

1.列番号Aをクリックして、A列を選択
2.メニュー[データ]-[区切り位置]
3.[次へ]ボタンをクリック
4.[次へ]ボタンをクリック
5.[列のデータ形式]で日付をクリックし、[YMD]を選択
6.[完了]ボタンをクリック

【解説】

 区切り位置を実行後、エラー値 ##### が表示されることがあります。この場合には列幅を広くすると、日付が表示されるようになります。

行削除

【問題】

 何行かに渡って必要のなくなった行があるので削除したいと思っています。
 Deleteなどで中身を削除するのではなく、行そのものをなくして上へつめたいと思っているのですが、どうやればいいのでしょうか?

【回答】

1.削除したい行を選択
2.右クリック−[削除]

【解説】

 削除したい行を選択した状態で、Ctrlキーを押しながら −(マイナス)でも行削除ができます。

 私の場合はツールバーに行挿入ボタンを &Row として登録しているので、行の削除は行を選択しないで Alt+Shift+R でできるようになってます。
 このやり方については以下のページをご覧ください。

【エクセル技道場】−ツールバー
http://www2.odn.ne.jp/excel/waza/toolbar.html

オートカルク

【問題】

 選択したセル範囲の平均値を簡単に知るにはどうしたら良いでしょうか?

【回答】

 セル範囲を選択すると、ステータスバーに選択したセル範囲の合計が表示されます。これはオートカルク機能と言います。
 ステータスバーを右クリックすると、以下のように計算方法を選択できるショートカット メニューが表示されます。

平均
データの個数
数値の個数
最大値
最小値
合計

 これで[平均]を選択すると、セル範囲を選択すると、平均値がステータスバーに表示されます。

 ステータスバーが表示されていない場合は、メニュー[表示]-[ステータスバー]をクリックします。
 Excel を起動すると、オートカルク機能は合計の計算にリセットされます。

重複するレコードは無視する

【問題】

重複したデータがあります。

  A
1  あ
2 あ
3 あ
4 あ
5 い
6 い
7 い
8 い
9 い
10 う
11 う
12 う
13 う
14 う
15 え
16 え
17 え
18 え
19 え
20 え

と合ったとします。

この中から、重複した部分から、その中の一つを取り出す方法はないでしょうか?

結果。
 B
1 あ
2 い
3 う
4 え

【回答】

 セルA1には列見出しが、セルA2以下にデータが入っているとします。

1.A列のデータが入っているセルどれかを選択
2.メニュー[データ]-[フィルタ]-[フィルタオプションの設定]
3.[リスト範囲]が正しく選択されていることを確認
4.[抽出先]を 指定した範囲 に
5.[抽出範囲]を $B$1 に
6.[重複するレコードは無視する]にチェック
7.Enter

複数行の行挿入

【問題】

 連続してデーターを入力し、1行ずつ行を挿入したいのですがメニュー[挿入]-[行]だと1行ずつ、《Ctrl》を押しながら1行ずつ選択して、メニュー[挿入]-[行]を行うと挿入出来ますがもっと簡単にできないものでしょうか?

【回答】

 A1:A10にデータが入っているとして、以下の手順でいかがでしょうか?

1.セルB1に 1 と入力、セルB2に 2 と入力
2.B1:B2を選択して、フィルハンドルをダブルクリック
→B1:B10が選択された状態
3.Ctrl+C (コピー)
4.セルB11でCtrl+V (貼り付け)
5.B1:B20内のセルどれか一つを選択して、[標準]ツールバーの[昇順で並べ替え]ボタンをクリック
6.B列を選択してDeleteキー

【解説】

 フィルハンドルとは、選択範囲の右下隅にある黒い四角形のことです。
 マウスポインタをこのフィルハンドルに合わせると白抜きの太い+の形から細い+の形に変化します。
 細い+にしてからダブルクリックします。

空白セルの削除

【問題】

 A1:F7に以下が入力されています。

  A B C D E F
1 ●       ▲  
2   × ☆   ● ▽
3 ★         ×
4     ○      
5   ■   ▼   □
6       × ★  
7 △     □    


 これを以下のように空白セルを省いて上に詰めるようにするにはどうしたら良いでしょうか?

  A B C D E F
1 ● × ☆ ▼ ▲ ▽
2 ★ ■ ○ × ● ×
3 △     □ ★ □

【回答】

1.A1:F7を選択
2.Ctrl+G (メニュー[編集]-[ジャンプ])
3.[セル選択]ボタンをクリック
4.[空白セル]にチェック
5.Enter
6.Ctrl+ - (Ctrlキーを押しながら - キーを押す)
7.[上方向にシフト]にチェック
8.Enter

計算式(関数の複写)

【問題】

 F1:F10に数式が入っています。
 同じ数式をI5:R5に入力するにはどうしたら良いでしょうか?

【回答】

1.F1:F10を選択
2.Ctrl+H (メニュー[編集]-[置換])
3.[検索する文字列]に = と入力
4.[置換後の文字列]に # と入力
5.[すべて置換]ボタンをクリック
6.Ctrl+C (コピー)
7.セルI5を選択
8.メニュー[編集]-[形式を選択して貼り付け]
9.[行列を入れ替える]にチェック
10.Enter
11.Ctrl+H (メニュー[編集]-[置換])
12.[検索する文字列]に # と入力
13.[置換後の文字列]に = と入力
14.[すべて置換]ボタンをクリック

【解説】

 上記手順は以下を実行しています。

・数式を文字列に
・コピー
・行列を入れ替えて貼り付け
・文字列を数式に

 数式のコピーはセル参照を調整します。数式を一旦文字列に変換することでこれを回避しているわけです。

並べ替えがうまくいかない

【問題】

 以下のようなデータを入力しています。

102
103
101
104
100

 これを昇順に並べ替えしたところ、以下のようになりました。

100
101
103
104
102

 期待していた結果は、以下です。

100
101
102
103
104

 なぜ期待した結果にならないのでしょうか?
 また、どうすれば期待したように並べ替えされるようになるでしょうか?

【回答】

 数値と文字数値が混在していたことが、期待した結果に並べ替えされなかった原因です。

 以下を実行して、全てのデータを数値に転換してから、並び替えを実行します。

1.データが入っている列番号Bをクリックして、B列を選択
2.メニュー[データ]-[区切り位置]
3.[完了]ボタンをクリック

【解説】

 区切り位置指定ウィザードの3/3の[列のデータ形式]で、[G/標準]を選択すると、数字は数値に、日付は日付形式の値に、その他の値は文字列に変換されます。

 以下のページもご参考になるのでは?

[XL2000] 数字の入力方法と並べ替え順序
http://support.microsoft.com/default.aspx?scid=kb;ja;JP412061

[XL2000] 文字数値を数値に変換する方法
http://support.microsoft.com/default.aspx?scid=kb;ja;JP181298

[XL97] 表示形式を文字列から数値へ変更後も値が文字列の場合
http://support.microsoft.com/default.aspx?scid=kb;ja;JP141765

[XL97] 数字を文字列として入力する方法
http://support.microsoft.com/default.aspx?scid=kb;ja;JP405491

[XL2000] ハイフンとコーテーションを含む並べ替えが以前のバージョンと異なる
http://support.microsoft.com/default.aspx?scid=kb;ja;JP212144

[XL97] 五十音順の並べ替え規則について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP410807

他のシートのセルを一行おきに参照

【問題】

 シートが2枚あって、1枚目のシートのデータを2枚目のシートに1行置きに参照したいのです。どうしたらいいでしょうか?

sheet1                            sheet2
     A                            A        
1 リンゴ                      リンゴ
2 メロン
3 なし        メロン
4 ぶどう
5 みかん       なし
6
7            ぶどう
8
9            みかん

【回答】

1.Sheet2のセルA1に '=Sheet1!A1 と入力(あたまに ' をつけます)
2.Sheet2のA1:A2を選択してからフィルハンドルを下にドラッグ
3.メニュー[データ]-[区切り位置]
4.[完了]ボタンをクリック

6桁日付をYY/MM/DDにするには?

【問題】

 下記のような6桁の日付データが A列に入力されています。(セルの表示形式
は文字列になってます)

010802
010805
010812

 これをシリアル値に変換して以下のようにするにはどうしたら良いでしょうか?

2001/8/2
2001/8/5
2001/8/12

【回答】

1.列番号Aをクリックして、A列を選択
2.メニュー[データ]-[区切り位置]
3.[次へ]ボタンをクリック
4.[次へ]ボタンをクリック
5.[列のデータ形式]で日付をクリックし、[YMD]を選択
6.[完了]ボタンをクリック

【解説】

 バージョンによって結果が異なります。
 XL2002、XL2000及びXL97 SR-2 + Newparse.reg は上記の結果になります。

 以下のページもご参考になるのでは?

[XL2002] 数値データから日付を表すシリアル値に変換する方法
http://support.microsoft.com/default.aspx?scid=kb;ja;JP401659

[XL2000] 日付の 2 桁入力と和暦入力について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP416772

[XL97] 日付の 2 桁入力と和暦入力について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP405494

Microsoft Office 97 Service Release 2 ソフトウェア修正プログラム
http://www.microsoft.com/downloads/details.aspx?FamilyID=3724CA68-4065-4D11-9084-AF235CDE9427&displaylang=JA

Newparse.reg ファイルは、以下のページよりダウンロードができます。

Microsoft Office 97 アップデート-2 ValuPack
http://officeupdate.microsoft.com/japan/downloaddetails/office/Office/sr2aof97/ValuPack.htm

[XL98] 日付の 2 桁入力と和暦入力について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP412418

[XL95] 日付の 2 桁入力と和暦入力について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP164406

[XL97] 日付をコピー貼り付けすると誤って入力される
http://support.microsoft.com/default.aspx?scid=kb;ja;JP179584

[XL97] 記録されたマクロから入力した日付の年が正しくない
http://support.microsoft.com/default.aspx?scid=kb;ja;JP180159

[XL2000] 英語の月名と数値を入力した場合の日付認識について
http://support.microsoft.com/default.aspx?scid=kb;ja;JP180952

[XL2002]2000 で作成した和暦が含まれるファイルを開くと西暦で表示
http://support.microsoft.com/default.aspx?scid=kb;ja;JP417848

別シートにデータを抽出

【問題】

 フィルタオプションで、特定条件のフィールドだけを別シートに検索条件を作成し、抽出したいのですが 別シートには作れませんというようなメッセージが出ます。どうしたらいいのでしょう?

【回答】

1.抽出先のシートでメニュー[データ]-[フィルタ]-[フィルタオプションの設定]
2.[リスト範囲]を指定
3.[検索条件範囲]を指定
4.[抽出先]で[指定した範囲]にチェック
5.[抽出範囲]を指定
6.Enter

【解説】

 上記1.のところで、抽出先のシート以外のシート(例えば[リスト範囲]として指定したいリストが入っているシート)で、メニュー[データ]-[フィルタ]-[フィルタオプションの設定]とすると、「抽出データを作業中のシート以外へコピーすることはできません。」というエラーメッセージが出ます。

テキストボックスにセルの値を表示

【問題】

 [図形描画]ツールバーの”テキストボックス”なのですが、例えばセルA1の内
容を自動的にテキストボックスに表示したい場合には、どうすれば良いのでしょ
うか?

【回答】

1.テキストボックスを選択 
2.数式バー内をクリック 
3. =A1 と入力(または「=」を入力してからセルをマウスなどで選択) 
4.Enter

【解説】

 テキストボックスに直接 =A1 と入力するのではなく、数式バー内に入力します。

ハイパーリンクをクリックするとワークブックが非表示に[XL97]

【問題】

 EXCEL97で、セルに挿入してあるハイパーリンクをクリックすると、IEが立ちあがってリンク先を表示するまでは良いのですが、EXCELのウィンドウをアクティブにしてみると、ワークブック自体が非表示になっています。
 ハイパーリンクをクリックしてもワークブックを非表示にしないようにするには、どうすれば良いのでしょうか?

【回答】

 XL97では、ブックを開いて何も変更をしないで、ハイパーリンクをクリックすると非表示になるようです。
 シートのどこかに=NOW()を入力するなどの回避策があります。

【解説】

 以下のページもご参考になるのでは?

XL97: ハイパーリンクをクリックすると、ドキュメントが消えます。
http://support.microsoft.com/kb/q156264/

[XL95] 変更していないブックを閉じる際保存するか尋ねられる場合
http://support.microsoft.com/default.aspx?scid=kb;ja;402644

オートコレクト

【問題】

 セルに (c) を入力すると、「○の中にc」といった文字に自動的に変換されます。また他にも、 (r) を入力すると、「○の中にr」といった文字に自動的に変換されます。
 これをそれぞれ(c)、(r)と意図した通りに入力するにはどうしたら良いでしょうか?

【回答】

1.メニュー[ツール]-[オートコレクト]
2.[入力中に自動修正する]のチェックをオフに

【解説】

 オートコレクトは、スペルミスなどの入力の誤りを自動的に修正する機能です。

 [入力中に自動修正する]にチェックが入っていると、他にも hte と入力すると the に自動的に修正されたりします。
 オートコレクトには、[入力中に自動修正する]以外に、[曜日の先頭文字を大文字にする](monday → Monday)などの項目があります。

複数行の挿入

【問題】

 何百もの行があります。3行目の下(4行目ごと)に空白行を挿入したいのですが、行挿入を繰り返す以外に簡単に3行・空白・3行・空白・・・となるようにできないでしょうか?

【回答】

 A2:A500にデータが入っているとします。

1.B3:B4にそれぞれ 1 と入力(入力するのは、数値でも文字列でも何でもOKです)
2.B3:B5を選択してフィルハンドルをダブルクリック
→B3:B500が選択された状態
3.Ctrl+G (メニュー[編集]-[ジャンプ])
4.[セル選択]ボタンをクリック
5.[空白セル]にチェック
6.Enter
7.Ctrlキーを押しながら+キーを押す
8.[行全体]にチェック
9.Enter
10.B列を選択してDeleteキー

【解説】

 上記手順は、

・三行おきに空白セルを作り
・空白セルを選択
・行挿入

ということをやっています。

 以下のページもご参考になるのでは?

【エクセル技道場】−編集−複数行の行挿入
http://www2.odn.ne.jp/excel/waza/edit.html#SEC31

小数点位置を固定

【問題】

 セルに数値を入力すると、勝手に単位が変わってしまうということが起きてしまいました。例えば22と入力すると2.2、220と入力すると22といった具合に単位が勝手に変わってしまいます。
 セルの書式設定の表示形式は数値で特にほかは設定はしていません。
 どうしたら、この問題を解消できるでしょうか?

【回答】

1.メニュー[ツール]-[オプション]
2.[編集]タブで[小数点位置を固定する]のチェックをはずす
3.Enter

【解説】

 この機能、[小数点位置を固定する]は、例えば、ドル/円の為替レートのように小数点第二位まで入力するような場合に利用すると便利です。

1.メニュー[ツール]-[オプション]
2.[編集]タブで[小数点位置を固定する]にチェックを入れて
3.[入力単位]に 2 を入力
4.Enter

 こうしておいて、例えば 12567 と入力すると、自動的に 125.67 が入力されます。
 この設定にしておいて、125.00 を入力したい場合には、12500 と入力するか、125.と入力します。

オートコレクト(時刻の入力を簡単に)

【問題】

 エクセルで時刻を連続して入力する作業があります。時分はテンキー入力をしているのですが、時と分の間のコロン(:)の入力が面倒です。
 時刻の入力をもっと簡単にする方法はないでしょうか?

【回答】

1.メニュー[ツール]-[オートコレクト]
2.[入力中にオートコレクト]にチェックが入っていることを確認
3.[修正文字列]に . を入力
4.[修正後の文字列]に : を入力
5.[追加]ボタンをクリック
6.Enter

【解説】

 これで、1.23 と入力すると 1:23 (1時23分)が入力されます。
 0.12.34 と入力すると 0:12:34 (12分34秒)が入力されます。
 全てテンキーで時刻の入力ができるようになります。

 上記はXL2000の手順です。
 XL97及びXL2002の場合には、上記2.は [入力中に自動修正する]という表現になってます。またXL2002の場合には、上記1.が[オートコレクトのオプション]という表現になってます。

 上記で設定したオートコレクトを解除するには、以下の手順です。

1.メニュー[ツール]-[オートコレクト]
2.[修正文字列]に . を入力
3.[削除]ボタンをクリック
4.Enter

二行おきにセル参照

【問題】

 二行おきのセル参照をする数式の入力は、どうすれば簡単にできるでしょうか?
 例えば下図のように、A列、B列にデータが入っていて、C列に二行おきのセル参照をする数式の入力をしたいのです。
 下図は一部を抜粋したもので、実際にはもっと大きな表なので、一つ一つ数式を入力していくのは大変です。

 A   B   C
1■■■ ■■■
2■■■ ■■■
3■■■ ■■■ =MID(A3,2,7)
4■■■ ■■■ =MID(A6,2,7)
5■■■ ■■■ =MID(A9,2,7)
6■■■ ■■■
7■■■ ■■■
8■■■ ■■■
9■■■ ■■■

【回答】

1.セルC3に =MID(A3,2,7) を入力

 A   B   C
1■■■ ■■■
2■■■ ■■■
3■■■ ■■■ =MID(A3,2,7)
4■■■ ■■■
5■■■ ■■■
6■■■ ■■■
7■■■ ■■■
8■■■ ■■■
9■■■ ■■■

2.C3:C5を選択
3.適当なところ、例えばセルC9までドラッグ
→3行おきに数式がコピーされる

 A   B   C
1■■■ ■■■
2■■■ ■■■
3■■■ ■■■ =MID(A3,2,7)
4■■■ ■■■
5■■■ ■■■
6■■■ ■■■ =MID(A6,2,7)
7■■■ ■■■
8■■■ ■■■
9■■■ ■■■ =MID(A9,2,7)

→C3:C9が選択されている状態

4.Ctrl+G(メニュー[編集]-[ジャンプ])
5.[セル選択]ボタンをクリック
6.[空白セル]にチェック
7.[OK]ボタンをクリック

 A   B   C
1■■■ ■■■
2■■■ ■■■
3■■■ ■■■ =MID(A3,2,7)
4■■■ ■■■ □□□
5■■■ ■■■ □□□
6■■■ ■■■ =MID(A6,2,7)
7■■■ ■■■ □□□
8■■■ ■■■ □□□
9■■■ ■■■ =MID(A9,2,7)

 →上図のように □□□ のセルが選択される

7.Ctrl+ - (Ctrlキーを押しながら - キーを押す)
8.[上方向にシフト]にチェック
9.[OK]ボタンをクリック

→□□□ のセルが削除(行の削除ではない)され、下図のようになる。

 A   B   C
1■■■ ■■■
2■■■ ■■■
3■■■ ■■■ =MID(A3,2,7)
4■■■ ■■■ =MID(A6,2,7)
5■■■ ■■■ =MID(A9,2,7)
6■■■ ■■■
7■■■ ■■■
8■■■ ■■■
9■■■ ■■■

 以下のページもご参考になるのでは?

【エクセル技道場】−編集−空白セルの削除
http://www2.odn.ne.jp/excel/waza/edit.html#SEC32

* ,? , ~の置換

【問題】

*鈴木宗子
*田中真紀男
*大泉純一郎

と入力されたデータがあるのですが、* だけを消す必要に迫られました。

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列]に * と入力
→[置換後の文字列]には何も入力しない。
3.[すべて置き換え]ボタンをクリック

 とすると、データが全部、空白になりました。、
 どうすれば、* だけを空白に置き換えることができるでしょうか?

【回答】

1.Ctrl+H (メニュー[編集]-[置換])
2.[検索する文字列]に ~* と入力
→[置換後の文字列]には何も入力しない。
3.[すべて置き換え]ボタンをクリック

【解説】

 ? (疑問符)及び* (アスタリスク) はワイルドカード文字と呼ばれ、置換等に使用する時には、以下のように特別な意味を持ちます。

 ?(クエスチョンマーク、または疑問符)は、? 一つで一文字を意味します。
 *(アスタリスク)は、任意の数の文字を意味します。

 ?、*、~を他の文字列に置換する場合には、[検索する文字列]に~?、~*、~~のように指定してやる必要があります。


Yahoo!ブックマークに登録
エクセル(Excel)技道場 HOME | このページのトップ
Google
ウェブ全体から検索 【エクセル技道場】内を検索