Excel2007を一発で終了するには

「Office ボタン」-「Excelのオプション」から「ユーザー設定」を選択し「コマンドの選択」プルダウンメニューの「Officeメニュー」を選び真ん中より下の方の「終了」をポイントして「追加>>」して「OK」を押す。

ふ～、これでやっとExcel2007をワンクリックで終了できるようになりました、Word2007やAccess2007は開いたファイルに変更を加えない限り右上の「×」を押せば終了できたのにExcel2007はできないのです、Bookが消えるだけでプログラムは終了しません、これは仕様なんだろうけどその意味が分からないですね。

回覧板

過去何度かここに書いていることですが、既存メニューからの印刷をさせないためにいろいろ策を講じていたのですがひとつ見落としがありました、今までのものは別ブックやシートにそれが出来ないように「コピー」とか「貼り付け」、「印刷」が出来ないはずだったのですが「印刷プレビュー」をうっかり非表示にしておくのを忘れていたのです。

そこで

Private Sub workbook_open()
　　With Application 'メニュー非表示
　　　　With .CommandBars("Worksheet Menu Bar").Controls
　　　　　　.Item(1).Enabled = False
　　　　　　.Item(2).Enabled = False
　　　　End With
　　　　With .CommandBars("Standard")
　　　　　　c = .Controls.Count
　　　　　　For nh = 1 To c
　　　　　　If Left(.Controls(nh).Caption, 2) = "印刷" Then
　　　　　　　　.Controls(nh).Enabled = False
　　　　　　End If
　　　　　　Next nh
　　　　End With
　　　　.OnKey "^{p}", ""
　　　　.OnKey "^{c}", ""
　　　　.OnKey "^{v}", ""
　　　　.OnKey "^{n}", ""
　　　　.DisplayFormulaBar = False
　　End With
End Sub

としました、でもメニューやツールバーをみんな使えなくしちゃえばいいのではないかと思って

Sub メニュー非表示()
Dim nh
With Application
　　For Each nh In .CommandBars("Worksheet Menu Bar").Controls
　　　　nh.Enabled = False
　　Next nh
　　For Each nh In .CommandBars("Standard").Controls
　　　　nh.Enabled = False
　　Next nh
End With
End Sub

Sub メニュー再表示()
Dim cnt, nh
With Application
　　With CommandBars("Worksheet Menu Bar")
　　　　cnt = .Controls.Count
　　　　For nh = 1 To cnt
　　　　　　.Controls(nh).Enabled = True
　　　　Next nh
　　End With
　　With CommandBars("Standard")
　　　　cnt = .Controls.Count
　　　　For nh = 1 To cnt
　　　　　　.Controls(nh).Enabled = True
　　　　Next nh
　　End With
End With
End Sub

でももう少しスマートに出来ないかと配列を使ってみると

Sub メニュー非表示()
Dim mBar(1) As String
mBar(0) = "Worksheet Menu Bar"
mBar(1) = "Standard"
With Application
　　For i = LBound(mBar) To UBound(mBar)
　　　　For Each nh In .CommandBars(mBar(i)).Controls
　　　　　　nh.Enabled = False
　　　　Next nh
　　Next i
End With
End Sub

Sub メニュー再表示()
Dim mBar(1) As String
mBar(0) = "Worksheet Menu Bar"
mBar(1) = "Standard"
With Application
　　For i = LBound(mBar) To UBound(mBar)
　　　　For Each nh In .CommandBars(mBar(i)).Controls
　　　　　　nh.Enabled = True
　　　　Next nh
　　Next i
End With
End Sub

よし、これで行こう。

記号を削除する。

ある範囲のセルの文字列のうち記号のみを削除する作業が必要になりました、データ量にして60,000件超！

これだけあるとさすがに手作業ではとても出来ないのでVBAでやっちゃえ！と始めてはみましたがどうにもうまくいきません、"?"と"*"を""に置換しようとすると全部消えてしまうのです、これはワイルド・カードとして使われる記号でもあるのです、つまり文字としてのそれではなくワイルド・カードとして振舞ってしまうのです。

nh.Replace what:="*", replacement:="" とするとすべての文字を対象として削除して（""に置き換える）しまい、nh.Replace what:="?", replacement:=""とすると For による繰り返し処理なので一文字ずつを順に（""）に置き換え結果としてすべて消えてしまうのです。

ということは文字列として認識させればよいわけで、そこで

　　nh.Replace what:=""?"", replacement:=""
　　とか
　　nh.Replace what:=""*"", replacement:=""

とかやってみましたがだめ、そこであちこち調べまわってやっとやり方がわかりました。

"~"チルダを使うのです、つまり

　　nh.Replace what:="~?", replacement:=""

といった具合。

Sub 記号削除()

For Each nh In Selection
　　With nh
　　　　.Replace what:=" ", replacement:=""
　　　　.Replace what:="!", replacement:=""
　　　　.Replace what:="""", replacement:=""
　　　　.Replace what:="#", replacement:=""
　　　　.Replace what:="$", replacement:=""
　　　　.Replace what:="%", replacement:=""
　　　　.Replace what:="&", replacement:=""
　　　　.Replace what:="'", replacement:=""
　　　　.Replace what:="=", replacement:=""
　　　　.Replace what:="~", replacement:=""
　　　　.Replace what:="^", replacement:=""
　　　　.Replace what:="\", replacement:=""
　　　　.Replace what:="|", replacement:=""
　　　　.Replace what:="`", replacement:=""
　　　　.Replace what:="@", replacement:=""
　　　　.Replace what:="+", replacement:=""
　　　　.Replace what:="<", replacement:=""
　　　　.Replace what:=">", replacement:=""
　　　　.Replace what:="~?", replacement:=""
　　　　.Replace what:="~*", replacement:=""
　　　　.Replace what:="-", replacement:=""
　　　　.Replace what:="_", replacement:=""
　　　　.Replace what:="(", replacement:=""
　　　　.Replace what:=")", replacement:=""
　　　　.Replace what:=",", replacement:=""
　　　　.Replace what:=".", replacement:=""
　　　　.Replace what:="{", replacement:=""
　　　　.Replace what:="}", replacement:=""
　　　　.Replace what:="/", replacement:=""
　　　　.Replace what:="[", replacement:=""
　　　　.Replace what:="]", replacement:=""
　　　　.Replace what:="・", replacement:=""
　　　　.Replace what:="、", replacement:=""
　　　　.Replace what:="。", replacement:=""
　　　　.Replace what:=":", replacement:=""
　　　　.Replace what:=";", replacement:=""
　　　　.Replace what:="「", replacement:=""
　　　　.Replace what:="」", replacement:=""
　　End With
Next nh
End Sub

便利だけどうっかり

「シートを別ブックで」で書いたTips、便利なんですけど大変な落とし穴があったのです、これをよく使うシーンはフィルタをかけて可視セルを新規シートにコピーし、それを別ブックにするときです。

あるとき何の疑問も抱かずにそうして作ったブック（ほんの2,3行である）をメーカーに送ったら・・・・・

「不要な行は削除してから送ってください。」

とお叱りを受けてしまいました、えっ？と思って送ったファイルを開いてみたがほんの2,3行しかデータはないのに・・・、そこでファイルの大きさを調べてみたらなんと4.8メガバイトもあるのです、なんでだあ？

エクセルは場合によって大量のデータを削除したときに最後のセルを間違って記憶するらしいのです、ではどうするか？

不要な部分を行全体、列全体を削除して上書き保存すればいいということがわかりました、最後のセルを知るには「F5」-「セル選択(S)」-「最後のセル(S)」-OK、または「Ctrl」＋「End」でそこに飛びます。

ちょっと便利に

ブックを複数開いているとき同時にそれらのブックを見たいことがあります、そんな時「ウインドウの整列」を使うのですがメニューから辿るのが面倒です、そこでその機能をショートカット・キーに割り当てちゃえと作ったのがこれです。

Personal.xlsの「Microsoft Excel Objects」の「ThisWorkbook」に

Private Sub Workbook_Open()
　　With Application
　　　　.OnKey "^{up}", "上下に並べる"
　　　　.OnKey "^{left}", "左右に並べる"
　　　　.OnKey "^{right}", "並べる"
　　　　.OnKey "^{down}", "重ねる"
　　End With
End Sub

と記述。

「標準モジュール」に

Sub 上下に並べる()
　　Windows.Arrange arrangestyle:=xlHorizontal
End Sub

Sub 左右に並べる()
　　Windows.Arrange arrangestyle:=xlVertical
End Sub

Sub 並べる()
　　Windows.Arrange arrangestyle:=xlTiled
End Sub

Sub 重ねる()
　　Windows.Arrange arrangestyle:=xlCascade
End Sub

と記述する。

するとブックを複数開いた状態で

「ctrl」+「↑」=上下に並べる
「ctrl」+「←」=左右に並べる
「ctrl」+「→」=タイル
「ctrl」+「↓」=重ねる

といった動作がワンタッチで可能になります、これ、本当に重宝している、一番使うのが「上下に並べる」。

ファイルを開かせない

必要があって特定のエクセルファイルを開かせないことを模索していたのですがやっとできました。

Personal.xlsで
新規モジュールModule*に以下を記述

Public 開かせない As New Class1
'
Sub auto_open()
　　Set 開かせない.App = Application
End Sub

クラスモジュールclass1に以下を記述

Public WithEvents App As Application
'
Private Sub app_WorkbookOpen(ByVal 見るな As Workbook)
　　With Application
　　　　If Left(見るな.Name, 2) = "回覧" Then
　　　　　　.DisplayAlerts = False
　　　　　　.Quit
　　　　End If
　　End With
End Sub

またはアドインとして・・・・

アドインとして登録するブックの標準モジュールに以下を記述

Public evt As Class1

Workbookモジュールに以下を記述
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Set evt = Nothing
End Sub

Private Sub Workbook_Open()
　　Set evt = New Class1
End Sub

Class1というクラスモジュールに以下を記述

Dim WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal 見るな As Workbook)
　　With Application
　　　　If Left(見るな.Name, 2) = "回覧" Then
　　　　　　.DisplayAlerts = False
　　　　　　.Quit
　　　　End If
　　End With
End Sub
'======================================================
Private Sub Class_Initialize()
　　Set app = Application
End Sub
'======================================================
Private Sub Class_Terminate()
　　Set app = Nothing
End Sub

これをアドイン「.xla」として保存し、それを使えるようにしておく。

なぜこんなことを考えたかというとこの「特定のエクセルファイル」は多くの人にとても有用なファイルなのですがそれを多用して嫌味とも取れる使い方をする奴が一人いて皆が迷惑を被っているらしいのでその人間のPCに少し細工をしておこうというもの。

誰あろう「サンダルおやじ」のことですけどね、何でもかんでも回覧する困った奴で全員がすでにメールで受け取っている物まで回覧するのです。

ぶら下がっているだけで暇なものだから・・・・。

Excelで範囲選択

EXCELで範囲選択をするときの方法としてマウスで始点から終点までをドラグするのが一般的ですが別のやり方として

・矢印キーで始点を選択したら「Shift」キーを押しながら終点ま
　でを選択する。
・始点をポイントした後「F5」キーを押して「ジャンプ」ダイアロ
　グを出し「参照先」に終点のアドレスを直接入力し、「Shift」
　キーを押しながら「OK」。

なんてやり方がありますが冒頭のマウスで範囲を指定する場合、最近のPCはグラフィックの性能が上がっているのでスルスルスルーっとあられもない方にまで画面が移動してしまってなかなかうまくいかない場合があります。

こんなときはまず始点のセルをポイントします、そして終点のセルが見えるところまでスクロール、そこで「F8」キーを押し終点セルをポイントするのです。

Excelの2000と2003は「F8」を押すと右下に「拡張」という表示が出ます、2007では左下に「範囲選択の拡張」という表示が出ます、2010は持ってないけど多分同じでしょうか。

これを解除するにはもう一度「F8」キーを押すか「ESC」キーを押します。

ところで初めに「F8」キーを押してからマウス・ホイールでスクロールするとうまく行きません、解除されてしまうからです、でもスクロール・バーを使えば初めに「F8」キーを押しても大丈夫です。

不便だな

「Word2007のバグ？」でも書きましたがこれと全く同じ現象がExcel2007でも見られます、画像の合成をするのにexcelの「グループ化」でやると簡単にできるのでよく使います、合成した画像をペイントに貼り付けてそこから名前を付けて保存するのですがこの作業をExcel2007でやると合成される前の個別の画僧しかペイントに貼りつかないのです。

Excel2003ではなんら意図せずに普通にできることがexcel2007では「グループ化したつもり」の画像をすべて再選択してコピーしないとペイントに反映できないのです、すごーく、すごーく、不便！

Office2010もそうなのでしょうか？責任者出てきなさい。

Happilyさん、この不便さ、知っていましたか？

表を使わないでVLOOKUP?

図1

図1のように点数によってランク分けや表引きをするときはVLOOKUP関数をよく使います、でもこの場合別表が必要になります。

だが別表を用意することなくできないかという記事がいつも読んでいる雑誌に出ていたのでちょっとやってみました。

CHOOSE関数を使えばできるのです =CHOOSE(インデックス,値1,値2,....) の通りだがこの例ではインデックスの内容はJ列になる、インデックスは0番目はないので「1」を足しています。

VLOOKUP関数は =VLOOKUP(検査値,範囲,列番号,検査の方)の通りだが「範囲」に配列定数も使えるらしく、

{検査値1,値1;検査値2,値2;検査値3,値3}

のように「;」で区切っていくつでもセルの文字数制限いっぱいまでできそうです、イメージとしては・・・・、

検査値1 値1
検査値2 値2
検査値3 値3

「,」でセル区切り「;」で次の行という意味になり正にVLOOKUPで普段使っている表そのものです。

　　
図2　　　　　　　　　　　図3

図2のようにマウスでドラグして「F9」を押すと図3のように配列が現れます、こんがらがりそうな配列定数ですがこれを見るとなんとなく分かったような気になります。

分かってくるとなんだか面白そうでほかにもいろいろ応用が利きそうですね。

ＰＳ．
それから INDEX関数 と MATCH関数を組み合わせてもできるらしいです、

=INDEX({"不可","可","良","優","秀"},MATCH(A2,{0,200,600,700,900},1))

この場合はINDEX関数MATCH関数、それぞれの行を省略した形になるのかな？良く分かりません。

離れたセルの選択

離れたセルを選択したいときがあります、そんな場合「ctrl」キーを押しながらマウスクリックするのですがそれだと両手がふさがってしまいます。

そんな時は「Shift」+「F8」キーを押すとロックされた状態になり、後はマウスだけで事が足ります、またその時一番下に「ADD」という表示が出ます。

離れたセルを多数選択しなければならないときは使える技ですね。

範囲内でジャンプ

セルの縁にマウスカーソルを合わせてダブルクリックをすると範囲内の端まで飛べますが「End」キーを押してからいずれかの矢印キーを押すと全く同じことができます。

わざわざ書くまでもなく知られていることだと思いますがセルの縁にマウスカーソルを合わせると上下左右の矢印が現れる、つまり冒頭に書いた操作ができますよと言っているのですが実は職場で使っているexcel2000ではマウスカーソルの形が全く変わらないのです、それでも全く同じことができます。

シートを別ブックで

エクセル・ファイルをメールに添付して送りたい、しかしそのファイルには複数のシートがあり先方には見せたくない、あるいは見られても差支えないがファイルサイズがかなり大きい、またはあるシートだけを別ファイルにしたい。

こんな場合、新たにエクセルを立ち上げて新しいブックを作ってそこに見せたいシートの内容をコピー・ペーストしてメール添付したり保存したりします、こんな方法で私の周りではやっているようです。

私は次のようにしています、図のようにウインドウを小さくして別ファイルにしたいシートをアクティブにしてそのシートのタブを選択した状態で「ctrl」キーを押しながら背景のグレー部分にドラグしてボタンを離せばシートの内容がコピーされたブックが出来上がります。

結局同じことをやっているのですが私の周りでは新しいブックを作るために新たにエクセルを複数起動し、それに範囲指定してコピーしたものを貼り付けているのです、そしてほとんどの人が裏で別のエクセルを起動しています、ひとつのエクセルの中から複数のファイルを開くということをしていないので当然メモリーの使用量が膨大になりPCの動きが緩慢になりハング・アップしないのが不思議なくらいです。

ところで全く同じことはシート・タブを右クリックして現れるメニューでもできますが少し操作が煩雑になります。

追記として「ctrl」キーを押さないで上記操作をするとシートが作ったブックに移動するという動作になります、あくまでもコピーとして別ブックにする場合は「ctrl」キーを押すことを忘れてはいけません、後でシートが一枚欠落しているなんて騒ぐことになります。

ここに書いたことは昨日目の前に座っている女性業務がエクセル・ファイルを実際に送ろうとしてなかなかできないで私に聞きに来たので彼女の席の後ろに立って口頭で操作方法を実地で指示したのですがあっという間にやりたいことができてしまったので「すごい、すごい」と大騒ぎ、隣に座っていた営業も大騒ぎして神々しい目で見られてしまいました。

その後倉庫に降りて出荷作業をやっていたら一時間ほどして件の女性業務が下りてきて「うまく行かない」と聞きに来ました、どうやら彼女にとって余りの「すご技」だったらしくそれを我が物とすべく練習していたようなのです、この向上心は高く買いたい、でも彼女すぐ忘れちゃうのだが・・・・。

おまけとしてマクロを下に書きます、これだとウインドウを小さくしなくて済みます。

Sub シートをコピーして別ブックにする()
　　ActiveSheet.Copy
End Sub

範囲名で抽出

左端列に項目、最上行にフィールドという体裁で作られたごく一般的な表がありその中の「項目Aの価格B」はいくらかを知りたいときは「VLOOKUP関数」でもとめるのが一般的ですが・・・、こんな方法もあります。

　
図1　　　　　　　　　　図2

図１のように範囲名を作成して図2のようにする、図はexcel2007の場合ですがexcel2000でも使えたから以降のバージョンでも使えるはずです、要は、、、

=範囲名A+スペース(空白)+範囲名B

excel2007では「数式で使用」を使うと便利、サンプルぐらいの表ならこの機能を使うほどではないですがやたらバカでかい表で何十列もある場合でうーんと右の方だと「VLOKKUP関数」の場合列数が何番目かを数えるのも大変。

それから範囲名に先頭が計算に使うような記号や空白は使えないので「選択範囲から名前を作成」機能を使うと適当にそれらを端折ったり、アンダー・バーで補ってくれます。

OFFSET関数は便利だ！

ワークシートにデータを追加した時データ量に応じて範囲を自動で拡張したいことがあります、そんな時下のようなマクロでそれが実現できますが・・・

Sub test()
　　n = Cells(Rows.Count, 1).End(xlUp).Row
　　h = Cells(, Columns.Count).End(xlToLeft).Column
　　Set nh = Range(Cells(1), Cells(n, h))
　　nh.Select
End Sub

Sub test2()
　　ActiveSheet.UsedRange.Select
End Sub

じゃあ同じようなことをExcelの関数でできないかと思っていたのですがいつも読んでいるPCの本に正にそんな記事がありました、OFFSET関数を使う方法です。

OFFSET関数、なんだかややこしそうで使う気になれなかったのですがひとつひとつ噛み砕けばなあーんだそんなに難しくないじゃん、ということで実際にやってみました。

「範囲名の定義」で適当に範囲名を付けて「参照範囲」に、、

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

と入力するとあら不思議！データを追加して「F5」を押して「範囲名」を指定すると「範囲名」が拡張されている！

実は「範囲名の定義」の「参照範囲」に関数が使えることを初めて知りました。

範囲の左端列や最上行の一部が切れた状態ではだめだですがこれは使えますね、範囲名を「Priny_Area」にしておけばいちいち印刷範囲を指定し直さなくても済みます。

特価データを何とかしなければ・・・2

「特価データを何とかしなければ・・・。」で「方法として・・」と書いたことをほぼ目論み通り出来つつあるのですが3番目に書いた「重複データの猶予期間の新しいものと古いものを色分けする。」のところが思うようにできないでいます。

いや、出来るには出来たのです、当初マクロで日付を都度判定して色を作ろうかと思ったのですがここは「条件付き書式」をあらかじめ設定しておけばはるかに楽だし、ファイルサイズに影響を与えることもなく、マクロも早くなる。

問題はこの「条件付き書式」

　　A
1 2010/11/30
2 2010/12/31
3 2011/5/31

上のように「猶予期間」が入力してある、そして

条件１：=IF($A1="","",TEXT($A1,"yyyy/mm")＜TEXT(TODAY(),"yyyy/mm"))
条件２：=IF($A1="","",TEXT($A1,"yyyy/mm")＝TEXT(TODAY(),"yyyy/mm"))

と設定しそれぞれセルの色を赤と黄にしました、いろいろ考えてこのようにしたのですがなんかもっと簡単な式でできないのかなと思っているのです、実用上問題はないのですがもっと簡単にできると思っていたのに思っていたよりも長ったらしいので納得いかないのです。

なお、「=IF($A1="",""…」としたのはデータ量が不定のため。

「Happily」さん、もしこれを見ていたら教えてください、お願い！