[エクセル] 「値の貼り付け」をショートカットキーで実行する

エクセルでショートカットキーの割り当てを変更する方法を使えば、「値の貼り付け」にショートカットキーを設定できそうです。

できそう・・・ですが、標準のままでは設定できません。「値の貼り付け」コマンドが、メニューバーのどこにもないからです。

メニューバーに「値の貼り付け」を追加

  1. ツールバーのどこでもいいので右クリック、「ユーザー設定(C)」をクリックして設定画面を開きます。
  2. コマンド(C)タブをアクティブにして、分類(G)から「編集」を探します。
  3. 右側のコマンド(D)から「値の貼り付け」を探します。
  4. そのままの状態で、メニューバーの「編集(E)」をクリックして開きます。固定されて閉じません。
  5. さっき探した「値の貼り付け」をドラッグアンドドロップして、「形式を選択して貼り付け(S)」の下にでも移動します。

「値の貼り付け」のD&D

  1. 編集(E)メニューに追加した「値の貼り付け(P)」を右クリックして、名前を変更。「値の貼り付け(&V)」にします。

「値の貼り付け」の名前変更

サンプルソースコード

あとは同じです。[Ctrl] + [B] で、「値の貼り付け」をできるようにします。

画面のちらつきを抑える

以下は、気にしなければ別にかまいません。

SendKeys でやってる以上、画面がちらつく(特に高負荷状態の時)のは抑えようがありません。

ただ、「値の貼り付け」コマンドの場合、ある条件が重なるとほぼ100%画面にゴミが一瞬、残ります。

ある条件ってのは、「値の貼り付け」が使えない時。クリップボードにHTMLが入ってたり、単純なテキストしか入ってない場合、「値の貼り付け」は使えません。使えないのに実行しようとするから、ゴミが出ます。

CommandBarControl

「値の貼り付け」が使えない時には、コマンドを実行しないようにすれば、回避できます。

「値の貼り付け」が使えるかどうか調べるには、CommandBarControl で 「値の貼り付け」コマンドの .Enabled プロパティを見れば分かります。使えない時は、SendKey する前に抜ければOKです。

サンプルソースコード

[Ctrl] + [B] で、「値の貼り付け」をできるようにします。ちらつき回避版。

| | コメント (2) | トラックバック (2)

[エクセル] ショートカットキーの割り当てを変更する

エクセルのキーボードショートカットの変更(ショートカットキーのユーザー設定)、需要はあると思うんですが、Microsoftは搭載する気はないんでしょうか?

ちょっと大変ですが、ショットカットキーをユーザーが自由に設定する方法が2つあります。

「値の貼り付け」をショートカットキーで実行する方法も参考にしてください。

[Alt] + ? 限定

[Alt] + [P] など、[Alt] キー のみしか使えませんが、割と簡単に設定できる方法です。

【裏技】あたいの磔!

1.ツール-ユーザー設定-コマンドタブから分類:編集 コマンド:値の貼りつけをツールバーにドラッグアンドドロップ

2.1のコマンドタブの[選択したボタンの編集]から[テキストのみを表示]をクリック

ツールバーにあるボタンであれば、アイコン表示をやめて、テキスト表示にする事で ( ) 内の文字がショートカットキーになります。

新規作成なら、[Alt] + [N]。上書き保存なら、[Alt] + [S]

Application.OnKey

柔軟な設定がしたい場合は、VBA を使って、Application.OnKey メソッドを実行します。

Application.OnKey(Key, Procedure)

Key:キーの組み合わせ
Procedure:実行する関数

ただし、Application.OnKey メソッドは、指定されたキーを押された時に、指定されたプロシージャー(関数)を実行するコマンドです。あらかじめ、希望のショートカットキーと同じ動作をする関数を作っておく必要があります。

Application.OnKey メソッドは、個人用マクロブック personal.xls の中に Auto_Open 関数を作成し、その中においてください。

もっと詳しい説明は、personal.xls 強化講座 その2 を参考に。

サンプルソースコード

[INSERT]キーで、行の挿入ができるようにします。VBAコードに貼りつけた後、一度エクセルを再起動してください。

Application.SendKeys

上のやり方でとりあえずOKです。ただし、この方法だと、[Ctrl] + [Z] (「編集(E)」 - 「元に戻す(U)」)が使えません。

VBAマクロで実行した作業は、エクセルの「元に戻す」コマンドが利用できません。上の方法はVBAで実行してますので、たった1行の命令でも、元に戻す事ができなくなります。

「元に戻す」を利用する為には、エクセルに対して人間が操作してるように見せかけないといけませんので、キーの組み合わせで制御するようにします。

Application.SendKeys(Keys, Wait)

Key:エクセルへ送るキー
Wait:同期/非同期。省略可。

「行の挿入」は、コマンドメニューの「挿入() - 行(R)」にあります。VBAコードだと、下のようになります。

Application.SendKeys "%ir"

コマンドメニューにあるコマンドはほぼ全部キーボードで操作できるので、この方法はかなり有効だと思います。

ただ、画面がちらつく事があります。仕方ないので、諦めてください。

サンプルソースコード

[INSERT]キーで、行の挿入ができ、「元に戻す」も使えるようにします。

| | コメント (0) | トラックバック (0)

[エクセル] 重複しない本当の順位を求める

Excelのランキング系関数は、重複するデータ(値)は同じ順位とみなされます。
なので、同率の順位が発生した場合、それ以降の順位がずれていきます。

100 100 90 80 70 70 60 50
って、データがあったとします。

順位は、

1位 100
1位 100
3位 90
4位 80
5位 70
5位 70
7位 60
8位 50

になります。

これを、

1位 100
2位 90
3位 80
4位 70
5位 60
6位 50

こういう順位にする方法ですが、かなり面倒みたいです。

アンドリューのエクセルTIPS:重複する値に騙されない

エクセルの関数は重複する値に関して致命的な欠点があります。 ・・・解説・・・

MoreFuncも入れてみたんですが、VBAから使う方法が分かりません。
英語のヘルプを読んでやってみましたが、「マクロが見つかりません」とか「型が一致しません」とか「Runメソッドは失敗しました」 になるので、あきらめました。

UniqueSmall

仕方がないので自作です。
Small関数の重複しないバージョンを作ってみました。

単純な発想で、「Smallへ渡すデータ範囲が重複なければいいはず」って考え方。
重複省く方法も、ソートしてから・・・とかしてません。
超手抜き。

文字コード Shfit_JIS(シフトJIS) です。
ブラウザで見る場合、右クリックでエンコードを切り替えてください。
初期状態でほぼ100%化けてると思います。

「UniqueSmall.txt」をダウンロード






| | コメント (1) | トラックバック (0)

[エクセル] ランキング関連Excel関数

Excelでランキングを出す時に使う関数群。

順位を返す = 3位とか

統計関数のRANK()
統計関数のPERCENTRANK()

普通は、RANK関数。
偏差値みたいなのをやりたい時は、PERCENTRANK()。何パーセントぐらいか?が分かります。

トップの値を返す = 100点とか

統計関数のMAX()
統計関数のMAXA()

普通は、MAX関数。
数値以外(TRUE/FALSEとか)を含める時は、MAXA関数。

最下位の値を返す = 0点とか

統計関数のMIN()
統計関数のMINA()

上と同じ。

真ん中の値を返す = 50点とか

統計関数のMEDIAN()

データが偶数のときは、平均化されます。

任意の順位の値を返す = 8位は、35点とか

統計関数のSMALL()。
統計関数のLARGE()。

SMALLが小さいもの順で、何位のデータを返す。
LARGEは、大きいもの順。

仕様

ランキング系のExcel関数の仕様として、重複する値を省きません。

1位が3つあった時。(100点が3人、90点が1人とか)
次の順位は、4位です。

1位/2位/3位の値は、全部100点。
4位が90点になります。

2位が90点にはなりません。
こういう風にするExcel関数はないみたいです。

とりあえず対応する方法は、重複しない本当の順位を求める で。

| | コメント (0) | トラックバック (0)

[エクセル] 誕生日の計算

Excelで誕生日を計算する方法です。

関数Datediffを使う方法もありますが、単純に引き算して求める方法を紹介します。

Datediff関数、ヘルプに載ってないので不安です。当然、Fxボタンから入力できません。

Datediffの使い方は、
Google 検索: datediff
年齢を計算する-DATEDIF関数:Excel:エクセル-数式/関数のFAQ:Tips

以下の例では、A3に生年月日が入ってるとします。

ユーザー設定書式を使う

=TODAY()-A3

って入れてから、右クリック。「セルの書式設定(F)」をクリック。「表示形式」タブの「分類(C)」で「ユーザー定義」を選んで、「種類(T)」のところに「yy"歳"」と入れます。

式だけでやる

=TEXT(TODAY()-A3,"yy歳")

文字列で帰ってきます。

式だけでやる(数値として扱う)

=VALUE(TEXT(TODAY()-A3,"yy"))

数値として帰ってきます。

| | コメント (5) | トラックバック (1)

[エクセル] 結合セルを解除+同じ値をセット。

指定されたセル範囲内の結合セルを解除して、その解除したところには、結合セルだった時と同じデータをセットする方法。
普通に、解除しただけでは、左上以外は全部空白のままなのです。
結合セルのVBAでの扱い方です。


まず、基本的な話。まとめ。

結合セル

 結合セル=単一セル範囲 ではありません。
 セルを結合した場合、結合セルの一番左上(=cells(1,1))に値がセットされ、それ以外は削除されます。
 つまり、結合セルの一番左上の値をセット/取得で結合セル全体の値を操作できます。

MergeCells プロパティ

 ヘルプに記載の "バリアント型 (Variant) の値を使用します。" がポイントです。
 設定/取得可能は、True/False/Null の3種類。
 対象は、Rangeオブジェクト。複数セルでも単一セルでもOK。

 複数セルの場合、そのセルすべてが結合セルじゃなければ、False。1つでも結合セルがあれば、Null。
 単一セルの場合、結合セルなら、True。以外は、False。Nullはありえません。

MergeArea プロパティ

 ヘルプの記載 "単一セル範囲でのみ機能します。"は間違い? 実験結果では、単一セルでのみ機能。
 対象は、Rangeオブジェクト。かえってくるのも、Rangeオブジェクト。Areasコレクションではない。


で、どうするか。流れ。

1. 対象の単一セルをセット。=(A)
2. (A)のMergeCellsプロパティを見る。
3. 結合セルなら、MergeAreaプロパティで結合セルを取得。=(B)
4. (B)の左上の値を保存。=(C)
5. (B)の結合セルを解除
6. (B)だった範囲に(C)をセット
7. ループ

単一セルに対して、処理していくのが重要です。でないと、MergeCells で Null になったり、MergeArea が「アプリケーション定義またはオブジェクト定義のエラーです。」になったりします。

ソースファイル

ということで、サンプル。きれいじゃないです。

文字コード Shfit_JIS(シフトJIS) です。
ブラウザで見る場合、右クリックでエンコードを切り替えてください。
初期状態でほぼ100%化けてると思います。

「UnMergeAndSetData.txt」をダウンロード

| | コメント (0) | トラックバック (0)

[エクセル] Rangeオブジェクト、Areasコレクション。

Rangeオブジェクト、Areasコレクション。と、セルの範囲について。
セルの呼び方は、http://arrow3.way-nifty.com/shige/2004/08/excel__2.html

Range.jpg

Rangeオブジェクトは、オブジェクトでありコレクションです。ヘルプには、「Rangeコレクション」と書かれてます。つまり、1つのセルでも複数のセルでも参照可能です。なので、Rangeオブジェクトには、単一セル/単一セル範囲(複数セル)/複数セル範囲(複数セル)の3つの参照が入ってる可能性があります。

Areasコレクションは、コレクションです。Areaオブジェクトは無くて、その役割はRangeオブジェクトがやってます。これを使うのは、複数セル範囲が参照されているRangeオブジェクトを使うときがメインです。RangeオブジェクトのAreasプロパティは、Areasコレクションを返しますので、使い方は下のとおり。

Dim MyRange As Range
Dim MyRange2 As Range
Dim MyAreas As Areas
Set MyAreas = Myrange.Areas

単一セルと複数セル

単一セルと複数セルを見分けるには、
 MyRange.Count
で、セルの数を見ます。単一セルなら、「1」。複数セルなら「2」以上が返ってきます。

複数セルの場合、
 For Each MyRange2 In MyRange
  MsgBox MyRange2.Value
 Next
で全部に処理できます。

単一セルのときは、MyRange.Value でいけます。

RangeオブジェクトのItemプロパティは普通じゃないので、MyRange.Item(5)とかしても、5つ目のセルを指定したことにはなりません。こういう事をしたい時はどうすればいいのか、不明です。

単一セル範囲と複数セル範囲

単一セル範囲と複数セル範囲を見分けるには、
 MyAreas.Count
で、領域の数を見ます。単一セル範囲なら、「1」。複数セル範囲なら「2」以上がかえってきます。

Areasコレクションの中には、単一セル範囲(=領域)づつに分解されたRangeオブジェクトがはいってます。単一セル範囲の場合は、含まれるRangeオブジェクトは1個。複数セル範囲の場合は、含まれるRangeオブジェクトは複数個。

 For Each MyRange2 In MyAreas
  MsgBox MyRange2.Count
 Next

とかで、処理できます。が、このMyRange2は、単一セルとは限りません。複数セルの可能性もあるので、Valueプロパティとかは指定出来ません。MyRange2が複数セル範囲であることはありえないので、もう一段For Eachを組んでやれば、全部の(単一)セルに対して処理可能です。

AreasコレクションのItemプロパティは普通なので、
 MyAreas.Item(1).Count
で、1つ目のセル範囲のセルの数を返します。これが、「1」なら、MyAreas.Item(1) は単一セル。「2」以上なら、複数セル(単一セル範囲)です。

意味ないけど、
 MyAreas.Item(1).Areas.Count
が、1以外になることはありえません。

| | コメント (1) | トラックバック (0)

[エクセル] セルの種類

セルの種類とセル範囲の種類です。Excelのヘルプとか読む時用に。

Cell.jpg

セルが1つか、複数かで、単一セルと複数セルに分かれます。
 ①が、単一セル。
 ②と③が複数セルです。

次に複数セルの場合。選択状態で呼び方が変わります。
 ②だけを選択している状態の時、「単一セル範囲」とか「領域」とか呼びます。
 そのまま、[CTRL]を押しながら③を選択した時、「複数セル範囲」とか「複数の領域」とか言います。
 この時、1つ目の領域は②。2つめの領域は③です。Areas.Item(1) が②を指して、Areas.Item(2)が③を指します。
 ①と②、①と③、①と②と③とかでも、「複数セル範囲」とか「複数の領域」とか言います。

Excelのヘルプとか、Webサイトでいろんな風にかかれてますが、 単純に「セル領域を選択・・・」とか書いてる場合は、「単一セル範囲」=「領域」。場合によっては、複数セル範囲の場合も。ヘルプでも書き方がいろいろで意味不明な事もよくあります。
普通にExcel使う分には、かまわないんですが、VBAでRangeオブジェクト、Areasコレクションを使ってるときには結構大事です。

| | コメント (0) | トラックバック (0)

[エクセル] ボタンによるマクロの実行方法

ExcelVBAとかで作成したマクロを実行する時、いつも、直接実行とかツールバーからとかなので、ボタンを使う方法を調べてみました。メモ。


ボタン(というかコントロール)には2種類あるようです。
(1) コントロールツールボックスの「コマンドボタン」
(2) フォームの「ボタン」

コントロールツールボックスの方は、VBAとかでも使えるタイプ。VBAでフォームを作る場合は、こっちのコントロールツールボックスの方を使うようです。CommandButtonコントロールです。
VBとかVBAとかに慣れてる/同じようにやりたい場合は、こっちの方がいいようですね。新しいタイプですし、Office全部共通ですし。プログラム!って感じ。

マクロを登録して、動かすだけなら、(2)のフォームの方が簡単です。Excel!って感じです。

今後のことも、考えて、(1)を使うことにしました。

Excel上部のメニューから、「表示(V)」-「ツールバー(T)」-「コントロールツールボックス」をクリック。ツールバーを出します。コマンドボタンをクリックして、マウスで好きな場所に描画。出来たボタンをダブルクリック。コード作成画面がでるので、

 Call マクロ名

と書きます。保存して終了。

| | コメント (0) | トラックバック (0)

[エクセル] セルの書式設定、ユーザー定義

Excelのユーザー定義の表示書式の指定方法。
Excelのヘルプで検索するときは、「ユーザー定義」で検索。トピックタイトル「表示形式で使われる書式記号」。

基本
 【正の時 ; 負の時 ; 0の時 ; 文字列】

 #;-#;0;@ → そのまま表示

共通(色)

 [黒] [青] [水] [緑] [紫] [赤] [白] [黄]
 [色1] ~ [色55]
  → 数字は色番号(vba の ClolorIndex と同じ指定、色見本)

 ex) $#,##0_);[赤]($#,##0)

数値系

 # 有効桁のみ表示
 0 必ず表示
 ? 有効桁のみ表示(小数点/分数の位置を揃える時に使う)

 , 間に入れて、桁区切り。最後において、1000で割って四捨五入。
 . 小数点
 / 分数

 % 100倍して表示
 [DBNum1] 漢数字

文字列系

 @ 文字列表示
 * 文字列繰り返し (*- で、"-----------"と表示)

 単純な文字列の表示は、二重引用符(")で囲む。

 ex) #;-#;0;@"版"*-

日付系(西洋)

 年
  yy 2桁
  yyyy 4桁
 月
  m 1桁
  mm 2桁
  mmm 英語月、省略。
  mmmm 英語月、フル。
  mmmmm 英語月、1文字。
 日
  d 1桁
  dd 2桁
  ddd 英語曜日、省略。
  dddd 英語曜日、フル。

日付系(日本)

 年
  g 元号アルファベット。(M,T,S,H)
  gg 元号一文字。(明、大、昭、平)
  ggg 元号フル。(明治、大正、昭和、平成)
  e 和暦1桁。
  ee 和暦2桁。
 日
  aaa 日本語曜日、省略。(月、火、・・・)
  aaaa 日本語曜日、フル。(月曜日、火曜日、・・・)

時間系

 h hh 時間
 m mm 分
 s ss 秒
 AM/PM AMまたはPM、12時間表示。
 A/P AまたはP、12時間表示。

 [h]:mm → 経過時間を表示。式で引き算とかをしたセルに対して、設定すること。


関連リンク:Shun's Page

| | コメント (0) | トラックバック (0)