矢印キーで画面が動いちゃう！？　/　こんな時はどうする？

haku1569 — 2022-06-23T19:12:32+09:00

ふとした拍子に（って何！？）矢印キーでセルが移動せずに、セルは固定されてまま画面が動いてしまう場合があります。

普段は、選択したセルが移動するはずなのに

まるで、マウスで画面右、下のスクロールバーを動かしている様な状態です。

たぶん、原因は「Scroll Lock」（スクロールロック）状態になっているからだと思います。

確認するには？
Excelの画面下のステータスバーの左端に“SCROLLLOCK”と表示されているかどうか。

それか、PCによって違いますが、"Scroll Lock" のランプが点いているかどうか。

ステータスバーに表示させるためには予めその設定がされてないと表示されません。

ステータスバー設定方法は？
ステータスバーの上で右クリして「ScrollLock」をチェックしてください。

で、解除する方法は？
パソコンキーボードの「Scroll Lock」ボタンを押して解除します。デスクトップならボタンが独立しているのでダイレクトに押すだけですが、ノートPCは他のキーと合体しているので、「Fn」＋「Scroll Lock」とかかもしれません。

また、ノートPCで「Scroll Lock」ボタンが存在しないものは、「スクリーンキーボード」を表示させてそこから「Scroll Lock」ボタンを押します。

スクリーンキーボードの表示方法は？

Windowsのバージョンによってやり方が変わります。（ググってください。）

ちなみにWin7（ふるっ！）だとこんな感じ。

で、現れた「スクリーンキーボード」。

で、問題のExcelも開いておきます。

Scroll Lock状態になっていたとすると、「Scroll Lock」ボタンが押された状態になっているはずです。

もう一度押して解除しましょう。

これで、「画面が動く攻撃」をまんまとかわすことが出来ました。
めでたし！めでたし！

ほいじゃ、「Scroll Lock」ってどういう時に使えばいいの？

って話なんですが、
マウスやタッチパッドを使用せずキーボードだけで操作する場合。
これって、あまりいないと思うのですが、１つの入力装置だけ（つまりキーボードだけ）で全ての操作が出来るってのはなかなか快適らしいですよ。
それから、選択したセルは固定のまま、1行、または1列づつ画面をスクロールさせる時。たぶん必要に迫られたらやると思う。

では、「Scroll Lock」を便利に使いこなしましょう！

]]>

AVERAGEA / 文字列を含んだ平均値 / Excel / 関数

haku1569 — 2021-04-23T20:53:07+09:00

今日は、Excel の関数 AVERAGEA で、平均値を求めてみます。

AVERAGE　ではないですよ！

AVERAGEA！
そう、最後に"A"が付いてるのです。

紛らわしいので要注意！

これは、数字だけでなく文字は"0"、

理論値　TRUEは"1"、FALSEは"0"として平均値を計算する関数です。

では、さっそくやってみよ！

こんな、10が10個のデータがあります。

分かりやすいですねー。。。

AVERAGEとABERAGEAの結果はどちらも"10"同じです。

一応、AVERAGEAの式の中身を確認すると、

"=AVERAGEA(D3:D12)"　となっています。

文字が入った場合

では、次はNo10のデータが"A"になりました。

すると、AVERAGEとAVERAGEAで結果が異なりましたね！

AVERAGEでは文字列は対象外なので、

AVERAGE=90/9
=10
となります。

それに対し、

AVERAGEAでは文字列は"0"とみなされ、計算対象になるので、

AVERAGEA=90/10
=9
となります。

次は、理論値　TRUTH　の場合

VERAGEでは文字列は対象外なので、

AVERAGE=90/9
=10
となります。

それに対し、

AVERAGEAでは理論値 TRUTHは"1"とみなされ、計算対象になるので、

AVERAGEA=91/10
=9.1
となります。

次は、理論値　FALSE　の場合

VERAGEでは文字列は対象外なので、

AVERAGE=90/9
=10
となります。

それに対し、

AVERAGEAでは理論値 FALSEは"0"とみなされ、計算対象になるので、

AVERAGEA=90/10
=9
となります。

という関数です。

で、使い道は。。。あまり、浮かんでこない。。。

では、今日はこのへんで！

関数メニュー
関数一覧（カテゴリ別）
関数一覧（名前別）

]]>

フィルターの解除（クリア）がもたついてきたら。。。 / Excel / 困った時は

haku1569 — 2020-05-24T19:24:29+09:00

こんにちわー！

Excelでデータベースの様なテーブルを作っていて、データの量がだんだん多くなるに従って、フィルタの解除（クリア）が遅くなってくる時があります。

（もたもたしてんじゃねぇーよっ！）

フィルタをかけて抽出する時はパッと出来るのにそれを解除する時がもたもたしてきます。

酷いとクルクルマークが回転して「応答していません」なんてメッセージが出ることさえあります！

（ふざけんじゃねぇー！）

そんな時は怒りをおさえて、冷静になって考えてみましょう！

各データ（レコード）に関数を設定してないかどうか？

あるでしょ？

あるんです！

で、そのセルを再計算するためにもたついてくるんです！

なので、早くするためには再計算をさせなければいいわけです。

で、どーすっかっつーと。。

メニューの「数式」から、「計算方法の設定」で「手動」にチェックします。

通常は「自動」になってます。

で、再計算させる時は、「再計算実行」をクリックするか「Ｆ9」キーを押します。

おしまい！

]]>

共分散とは何ぞや / データ分析の解説

haku1569 — 2020-03-22T21:05:35+09:00

さて、気温とビールの売上本数の様な2つのデータの相関関係を数値で表す相関係数を求める時に “共分散” が登場します。
“分散” は知ってるけど “共分散” って何？ってことになります。

その“共分散”について考えてみようと思います。
散布図を描いてみたら気温が上がるとビールの売上本数が増えている時と言うのはどういう事かグラフをよく見てみましょう。

まず、ばらつきはあるもののデータが右上がりに分布しているようです。
と、言うことは。。
・気温が高い時は、ビールの販売本数も多くなる。
もうちょっと推測してみると、気温が平均値より高い時（暑い時）はビールの販売本数も平均値より多くなる。
・と言うことは、気温が平均値より低い時（涼しい時）はビールの販売本数も平均値より少なくなる。
また、縦軸のビールの販売本数を、1本毎じゃなくて、6本パックのパック数で数えると、縦軸の数は1/6に少なるなるけど、それは見かけの数字が少なるなるだけで、両社の関係は何も変わらない。

で、ここでこの散布図に気温とビールの販売数の平均値のラインを書き加えてその差を目盛りにしてみます。

平均値を境にグラフは4つのエリアに分かれました。

すると、なるほど先ほどの推測はほぼそうなっているのが分かります。
右上のエリアと左下のエリアは平均値に対しては真逆の状態なのですが、気温とビールの本数の関係に関しては右上がりと言う同じ状態を表しています

それではここいらで、共分散の定義はどうなっているか確認してみましょう。

共分散とは 2種類のデータ（２変量）の偏差の積の平均

変量と言うのは、独立して異なる値を取り得る量のことでここでは、気温とビールの本数のことになります。

偏差と言うのはばらつきのことで（データの値-データの平均値）で計算されます。なので平均値より小さい値の場合は符号はマイナスになります。

偏差の積はつまり(気温1-平均気温)x(ビールの販売本数1-平均本数)のことになり

それらをデータ分合計してデータの個数で割った平均値が共分散ということになります。

共分散のキモはこの偏差を掛け算しているところです。

どういうことかと言うと平均値で分割された４つのエリアの偏差の積は[B]と[D]のエリアはプラス（正の値）になると言うことです。

[B]はそれぞれ平均値より上なのでプラスxプラスでプラスの値になり、
[D]もマイナスxマイナスでプラスの値になります。
方や、[A]、[C]のエリアはプラスとマイナスの掛け算になって結果はマイナスの値になります。

で、その偏差積の平均と言うことはまずそれらを足していきます。

偏差積は平均値で出来た軸を基準にした長方形の面積になります。ただ、[A]、[C]のエリアは負の値になりますので、面積に例えるのは間違ってますけどね。

でそれらの平均値ですから、結果正の値なら右上がり、つまり気温が上がるとビールの販売本数が増える（気温が下がるとビールの販売本数が減る）
負の値なら右下がり、気温が上がるとビールの販売本数が減る（気温が下がるとビールの販売本数が増える）と言うことになります。

今回の気温とビールの販売本数のサンプルを実際に計算してみると。。

共分散の値は“53”となりました。

この“53”の意味ですが、特にありません。単位は “℃・本” って何それっ！？って意味不明の値です。

なので、この “共分散” 単体で相関関係のどんな特性を表してるかと言うと。。正の値か負の値かだけが特性を表しているだけです。

この値は共分散の値の大きさはビールの本数の単位が変わったり、大きなデータがあると大きく変わるので、共分散の値の大小と2つのデータの関係性は何も関連がありません。

また、“分散”という文字があるので右上がりの直線に対するばらつき具合を表しているのかな～と思うのですが、例えば散布図のばらつき具合と共分散の値をみると、共分散の値が同じでもばらつきが大きかったり小さかったりするので関連がありません。

まとめると、“共分散” 単体は何を表しているかと言うと結局値が正の値なら右上がり、負の値なら右下がりだけです。

関連ページ

・データ分析の解説/メニュー

いつものキッチンのやかんでお湯を沸かすのではなく小形のストーブでお湯を沸かしてコーヒーを飲んだりカップ麺を食べたら普段と違う味がするはず！

【モンベル】JETBOILジェットボイルフラッシュ●送料無料●
価格：16280円（税込、送料無料) (2020/3/22時点)

楽天で購入

]]>

VBAに登場する意味不明な用語の意味 “モジュール”とか“ステートメント”とか / Excel / VBA

haku1569 — 2020-03-15T18:42:13+09:00

Excel VBAでは色々な横文字が登場するのだけど、なんかつかみどころない単語が多い！
と、単にボキャブラリーが無いだけなのを棚に上げるのだけど、

ということで、あまり深堀は出来ないけど簡単にまとめてみた！

プロジェクト
１つのワークブック（Excelファイル）のマクロ群になります。
VBAの編集画面では左上のプロジェクトエクスプローラにの一番上の太字で表示されている“VBAProject（）”になります。（　）内はファイル名になっります。
プロジェクトに“モジュール”、“プロシージャ”、“ステートメント”が構成されます。

モジュール
VBAのコードを書いて保存する場所のことで、ここにマクロのプログラム、プロシージャが作られます。
１つのモージュールには複数のプロシージャを作ることができます。
一般的なVBAのコードは“標準モージュール”に書き込みます。またそれとは別にオブジェクトに付随するモジュールがあり、ワークシート付随する“シートモジュール”、ワークブックに付随する“ブックモジュール”、フォームに付随する“フォームモジュール”があります。

プロシージャ
sub ～ End sub で書かれる１つのマクロを実行するプログラムのまとまりのことです。
“マクロ”と言うのはソフトの操作を自動化させたものの総称なので、1つのプロシージャで１つのマクロを作る場合もありますし、複数のプロシージャで１つのマクロを構成する場合もあります。ですが、“プロシージャ”と言うのはプログラミングサイドの用語で片や“マクロ”はユーザーサイド（エクセル寄り）の用語になります。
　標準モジュールに作られるプログラムを“標準プロシージャ”と言い、「F5」キーや「マクロの実行」で実行されるプロシージャです。またオブジェクトに付随するモジュールに作られるプログラムを“イベントプロシージャ”と言い、ユーザーの操作によって起きるイベントにより自動で実行するプロシージャです。
例えば、ブックモジュールにはExcelを起動した時、保存した時等のイベントプロシージャ

シートモジュールにはセルの値を変更した時、シートを追加した時等のイベントプロシージャ

フォームモジュールにはボタンをクリックした時等のイベントプロシージャを作ります。

SubプロシージャとFunctionプロシージャ
Sub マクロ名() ～ End Subで記述されるSubプロシージャの他に、Function 関数名() ～ End Functionで記述されるFunctionプロシージャがあります。Functionプロシージャは戻り値で結果を返すことができます。

ステートメント
VBAで記述される最小単位の1行単位のプログラム。if～then、for～next 等は複数行にまたがります。
例えば、“Sub”、“Functoin”、“Dim”、“Call”、“End”、“For～ Next”、“GoTo”、“If～Then～Else” 等があります。

まだまだ、聴きなれない用語が出てきますが、今日はここいらで勘弁してやろう！？
ほなまた！

関連ページ

・Excel VBA　メニュー

・VBAを使えるようにしてみました！

]]>

セルの行番号、列番号、アドレス（絶対位置）を取得する / Excel / VBA

haku1569 — 2020-02-23T14:50:41+09:00

Excel　VBAでセルの列番号、行番号、アドレスを取得してみます。

＊下の方で使用例のExcelファイルがダウンロードできます。

１．セルの行番号を取得する

***.Row
***：行番号を取得したいセルを指定

例　セル“A1”の行番号（=1）を取得
Cells(1,1).Row

２．セルの列番号を取得する

***.Column
***：列番号を取得したいセルを指定

例　セル“B1”の列番号（=2）を取得
Cells(1,2).Column

３．セルのアドレス（絶対位置）を取得する

***.Address
***：アドレスを取得したいセルを指定

例　セル“C1”のアドレス（=$C$1）を取得
Cells(1,3).Adress

使用例

Sub Macro1()

'変数R、C、A
Dim R
Dim C
Dim A

'セル“A1”の行番号を変数Rに格納
R = Cells(1, 1).Row

'セル“B1”の列番号を変数Cに格納
C = Cells(1, 2).Column

'セル“C1”のアドレスを変数Aに格納
A = Cells(1, 3).Address

'変数R、C、Aをイミディエイトウィンドウに表示
Debug.Print (R)
Debug.Print (C)
Debug.Print (A)

End Sub

サンプルExcelファイル：セルの行、列、アドレス.zip

前のページ：
次のページ：

関連ページ

・Excel VBA　メニュー

・VBAを使えるようにしてみました！

車のバッテリーが完全に上がってしまってもかけることが出来る25,000mAhの大容量！

車のエンジンスターターだけでなく、スマホの充電、LEDライトとしても使用できます。

【送料無料】suaoki ジャンプスターター U27 超超超大容量25000mAh 最大電流2500A 防災グッズ全排気量対応ポータブル電源 USBタイプC対応スマホ/タブレットなどへ急速充電 LEDライト強制起動
価格：13800円（税込、送料別) (2020/2/23時点)

楽天で購入

]]>

IFステートメント~条件分岐 / VBA / Excel

haku1569 — 2020-02-15T18:54:07+09:00

ExcelのVBAで、ある条件をによって、処理を分岐させるのが"IF文（ステートメント）"です。

「もし※※だったら、処理A、そうじゃなかったら処理B」って言うやつです。

＊下の方でサンプルExcelファイルがダウンロードできます。

If　条件式　Then（条件を満たした時 / 真 true）処理A

　Else（条件を満たさない時 / 偽 false）処理B

End If

“Else 処理B” は省略することができます。
すると条件を満たさない時はEnd If でIfステートメントが終了します。

条件式で使用する演算子の種類

比較演算子
２つ値や式を比較して、結果を真（true）偽（false）で返します

演算子	意味
=	等しい
<>	等しくない
>	より大きい
>=	以上
<	より小さい
<=	以下

論理演算子

２つの条件式を組み合わせた条件式を作り結果を真（true）偽（false）で返します

演算子	意味	例	結果
And	理論積（且つ）	真（true）And 真（true）	真（true）
		真（true）And 偽（false）	偽（false）
		偽（false）And 真（true）	偽（false）
		偽（false）And 偽（false）	偽（false）
Or	理論和（または）	真（true）Or 真（true）	真（true）
		真（true）Or 偽（false）	真（true）
		偽（false）Or 真（true）	真（true）
		偽（false）Or 偽（false）	偽（false）
Xor	排他理論和（どちらか１つだけ真のとき真）	真（true）Xor 真（true）	偽（false）
		真（true）Xor 偽（false）	真（true）
		偽（false）Xor 真（true）	真（true）
		偽（false）Xor 偽（false）	偽（false）
Not	理論否定	Not 真（true）	偽（false）
Not	理論否定	Not 偽（false）	真（true）

使用例

１）セルの値が30以上だったら隣のセルに"合格"、じゃなかったら"追試"と表示する

Sub macro1()
'もしセルA2が30以下なら
If Cells(2, 1) >= 30 Then

'セルB2に“合格”と表示
Cells(2, 2) = "合格"
'そうでなかったら､
Else
'セルB2に“追試”と表示
Cells(2, 2) = "追試"

End If

End Sub

２）Elseの省略、セルの値が30以上だったら隣のセルに"合格"と表示する

Sub macro2()
'もしセルA2が30以下なら
If Cells(2, 1) >= 30 Then

'セルB2に“合格”と表示
Cells(2, 2) = "合格"
End If

End Sub

３）論理演算子"And"の使用例
　　算数のセルの値が30以上、且つ、国語のセルの値が30以上、だったら、隣のセルに"合格"と表示する

Sub macro3()

'もしセルA5が30以上　かつ　セルB5が30以上　ならば
If Cells(5, 1) >= 30 And Cells(5, 2) >= 30 Then

'セルC5に合格と表示
Cells(5, 3) = "合格"

End If
End Sub

　サンプルExcelファイル：if文.zip

関連ページ

・Excel VBA　メニュー

・VBAを使えるようにしてみました！

最近のマキタって電動工具だけにとどまらず、充電池をベースにした様々な機器のバリエーションを増やしてて面白いですよ。

(15日限定ポイント19倍)マキタレシプロソー 18v マキタ充電式レシプロソー JR184DRF
価格：32389円（税込、送料別) (2020/2/15時点)

楽天で購入

]]>

フィルターを解除してソートし直して最終行の次を選択するマクロ / VBA / Excel

haku1569 — 2020-02-09T21:52:30+09:00

テーブル（表）でデータの検索をしていると、色々なフィールド（列）でフィルターを掛けたり、ソート（並び替え）したりするわけだけど、今度は新たにデータを入力する場合はフィルターを解除して所定の列で昇順に並び替えをして最終行の次の行にセルを移動させなければなりません。

①フィルターのクリア
②所定の列で昇順に並び替え
③最終行の次の行にセルを移動

どんなファイルであっても検索と入力を兼ねるテーブルの場合はそれをルーチンとして常に行わなければなりません。
この３つの作業も面倒なのでマクロで自動化してしまいましょう。
そして、同じショートカットキーにマクロを登録しておくとどんなファイルでも同じショートカットでマクロを実行させることができます。

例えば、こんな受注データがあります。
今、氏名が"高橋良男"でフィルターをかけ商品コードでソートして、何かデータの検索を行っています。

で、検索の作業が終了し、新たなデータを入力しなければならなくなりました。
と、上の①~③の作業を手動でやらなければなりませんが、マクロで実行するとショートカットキーを押すだけで、
こんな感じに一発でフィルターがクリアされて受注コードで昇順でソートしてデータが入力する位置に選択セルが移動されます。
下の方にこのサンプルファイルのリンクが貼ってあります。

構成は、
Macro1_フィルターのクリア
Macro2_昇順に並び替え
Macro3_最終行の下に移動
Macro4_マクロの自動実行
の４つのマクロを作成します。

それでは、実際のVBAを見ていきましょう。
このままコピペしても使えます。

まず、フィルタをクリアするマクロでです。
ここでのポイントはフィルタを解除する時にフィルターがかかっているかどうかをif文で確認することです。
フィルターがかかっていない時はこのマクロを実行しない様にしています。
何故かと言うと、フィルターがかかっていないのにフィルター解除のマクロを実行するとエラーになってしまいます。

Sub Macro1_フィルターのクリア()

'ワークシート“受注マスタ”を選択
Worksheets("受注マスタ").Select

'セルA2（データの左上）を選択
Cells(2, 1).Select

'もしフィルターがかかっていたら
If ActiveSheet.FilterMode Then

'フィルターを解除する
ActiveSheet.ShowAllData

End If

End Sub

実際のVBAのコードウィンドウです。

次は、昇順に並び替えるマクロです。

Sub Macro2_昇順に並び替え()

'テーブル1の“受注コード”で昇順に並び変える
Call Range("テーブル1").Sort(Key1:=Range("テーブル1[受注コード]"), Order1:=xlAscending, Header:=xlYes)

End Sub

実際のVBAのコードウィンドウです。

次は、新たなデータ入力のために最終行の下に選択セルを移動させます。

Sub Macro3_最終行の下に移動()

'変数n
Dim n

'ワークシート“受注マスタ”を選択
Worksheets("受注マスタ").Select

'セル“A2”からデータのある最終行番号を変数nに保管
n = Range("A2").End(xlDown).Row

'列Aの最終行の継の行を選択
Cells(n + 1, 1).Select

End Sub

実際のVBAのコードウィンドウです。

最後はこれらの一連のマクロを実行するマクロです。

Sub Macro4_マクロの自動実行()

'Macro1を実行
Call Macro1_フィルターのクリア

'Macro2を実行
Call Macro2_昇順に並び替え

'Macro3を実行
Call Macro3_最終行の下に移動

End Sub

実際のVBAのコードウィンドウです。

サンプルExcelファイル：North Wind_20200209.zip

前のページ：
次のページ：

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！

最近はもう何が起きるかわからなくなってきましたねー。
と、言うわけで一家に一台ソーラー発電！

【限定30%OFF】suaoki ソーラーチャージャー 120W 折りたたみソーラーパネル停電対策 type-C 60W急速充電 QC3.0搭載高変換効率スマホスマートフォンタブレットパワーバンクキャンプライトラップトップ充電可能アウトドア登山などに活躍軽量
価格：23016円（税込、送料別) (2020/2/11時点)

楽天で購入

]]>

VLOOKUPのズレを防止する / 関数 /　Excel

haku1569 — 2020-02-02T22:11:37+09:00

Excelの “VLOOKUP関数” で検索する時の検索場所の指定は検索範囲の中の左からの列番号で指定します。
そこで、検索場所の列の左側に列を挿入してしまうと本来検索する列が右側にズレるため間違った列を検索してしまうことになってしまいます。
そこで、そのズレを防止するために、検索する列番号を“MATCH関数”を使って求めてやります。

“COLUMN関数”でも同じような効果があるのですが、“MATCH関数”の方がより間違いなく検索できます。

こんな様な3列の表があります。その右側でVLOOKUP関数で得意先コードから都道府県を検索しています。

①は都道府県の列番号を“3”としたVLOOKUP関数です。

②が“COLUMN関数”で見出しの“都道府県”のセル“D3”の列番号を求めてそれを列番号にしています。ただここではD3の列番号は“4”になるため“-1”して“3”にしています。

③が“MATCH関数”で表の見出しの範囲（B3:D3）から“都道府県”の列番号を求めています。

いずれも、得意先コード“2”に対し“福岡県”が正しく検索されています。

では、意地悪テストです。
得意先コードの右側に列を挿入します。
すると都道府県の列は右にズレて表の中で4列目に変わります。
すると①の検索列を“3”と指定したVLOOKUP関数では“小料理なんごく”が表示されてしまいました。

①では列の挿入によりVLOOKUP関数の検索範囲は$B$3:$E$23に拡大されるため都道府県の検索列は左から“4”番目にならなければいけません。

②の“COLUMN関数”では列の挿入により検索セルも相対的に右にズレて“E3”を検索するようになったため、“E3”の列番号“5”“-1”で4になって都道府県が表示されています。

③の“MATCH関数”は列の挿入により検索範囲がB3:E3に拡大し“都道府県”と言う文字を検索し“4”を返しVLOOKUP関数は都道府県から検索しています。

次は、表の中ではなく外側（右側）に列が送入された場合です。
ここで、都道府県が正しく表示されないのは②の“COLUMN関数”で指定した場合です。

①では“VLOOUP”の検索範囲が表の右側の列の挿入により“$C$3:$E$23”に右に移動するため“都道府県”の列は表の左から3番目であることに変わりはありません。ので正しく“福岡県”と表示されました。

では問題の“COLUMN関数”を使用した②の場合です。
列の挿入により検索するセルは“E3”になり、右にズレました。ので、“5”“-1”で“4”になり、VLOOKUP関数の検索範囲の外を指定してしまっています。

“MATCH関数”を使用した③の場合は列の挿入により検索範囲が“C3:E3”と右に移動しているだけなので“都道府県”の検索結果は“3”になり正しく“福岡県”が表示されました。

と言うことで、VLOOKUP関数を使って検索したいのは絶対的な列番号ではなく表の中の“都道府県”の列番号であるわけなので、表の中の“都道府県”の列番号を返してくれる“MATCH関数”を使うのが妥当なのは当たり前と言うことになる。。。

関連ページ
・VLOOKUPで列方向の検索をして表示させてみた
・COLUMN（COLUMNS）関数で列番号を求めてみた～

]]>

列の範囲名を自動で設定する / 数式 / Excel

haku1569 — 2020-01-13T12:06:06+09:00

Excelでは選択範囲に名前（範囲名）をつけて、それを数式や関数等で使用することが出来ますが、列（だけではないですけど）のデータに簡単に範囲名を付ける方法があるので、やってみます。

さて、こんな試験の結果を集計した表があります。

この表の範囲を選択します。

そこで、「数式」から「選択範囲から作成」を選択します。

「選択範囲から名前を作成」の画面が開きます。
「上端行」にチェックをいれてOKします。
「上端行」と言うのは、一番上の行つまりこの表では“氏名”、“国語”、“数学”の列の見出しになりますので、これにチェックすると言うことは、それぞれの列に範囲名が設定されることになります。
「左端列」にすると各行に氏名が範囲名に設定されます。

すると、この様に“氏名”、“国語”、“数学”の範囲名が出来ているのがわかります。

国語の範囲

数学の範囲

では、この範囲名を活用してみましょう
国語と数学の平均点をAVERAGE関数で求めてみましょう。

“=AVERAGE(国語)”と入力します。
通常は平均値を求める範囲（C3:C52）を指定しますが、代わりに範囲名で設定できます。

と、国語の平均点が“63点”と表示されます。
同様に数学の平均を求めます。

今度は、“数学”を手入力しないで登録してある範囲名から選択してみます。
「数式」の「数式で使用」から「数学」を選択します。

と、数式に“数学”が入力されました。

で、国語と数学の平均点が範囲名で計算できました。

で、この平均点が計算されているセルを右下の方へコピーペーストしてみます。
普通にセルの範囲指定で計算している場合は、セルの範囲を絶対範囲に指定していないと、移動した分だけ参照範囲も移動してしまい正しい結果が表示されなくなってしまいますが、範囲指定ならそんな心配も不要です。

関連ページ

]]>

Debug.Printの便利な使い方 / Excel / VBA

haku1569 — 2019-10-30T20:22:30+09:00

マクロ作って入る時に変数にちゃんと値が格納されているかとか、いちいち確認したくなる時ってありますよね。そんな時は Debug.Print を使いましょう！
VBAをコーディングしているコードウィンドウの下のイミディエイトウィンドウに表示させることができてとても便利です。

例えば、ワークシートのあるセルに入力した値を、変数に格納しそれを Debug.Printを使ってイミディエイトウィンドウに表示させてみます。

サンプルコード

Sub macro1()

'変数a
Dim a

'aにセルA1の値を格納
a = Cells(1, 1).Value

'イミディエイトウィンドウに「a＝」で変数aを表示
Debug.Print "a＝" & a

End Sub

こんな感じです。

ここで、ワークシートのセルA1に"4"を入力します。

で、マクロを実行させると
イミディエイトウィンドウに“a＝4”と表示されました

サンプルExcelファイル）debugprint.zip

前のページ：
次のページ：

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！

　最近はradikoとか、インターネットでラジオ番組を聞けるようになったけど、ギガとか、バッテリーとか、気にせずに使いたいならやっぱリアルラジオでしょ！？

【ポイント2倍！10月30日(水)23:59まで】ソニー SRF-T355 FMステレオ/AM PLLシンセサイザーラジオ

楽天で購入

]]>

一覧表から帳票を自動で印刷させる（完） / Excel / VBA

haku1569 — 2019-10-27T22:36:35+09:00

さて、色々な試作を行ってきましたが、これで一覧表（受注マスタ）から帳票（受注票）を印刷するマクロを完成させることが出来ます。

最終的に完成させるマクロを1回目と同じように言葉で表してみます。
関連する試作ページにリンクさせときますね。

受注マスタの受注票を印刷したいデータの「印刷」の列に“1”を入力する
マクロを実行させます
受注票の印刷設定が完了しているか確認するメッセージボックスを表示する
Excelのシートの最終行からデータ範囲の最後の行までジャンプしてその行番号を取得する
受注マスタのデータ範囲の一番最初の行に行きます
列「印刷」に“1”が入力されている場合は受注コードをシート受注票の受注コードに入力します。
受注票に受注コードが入力されるとVLOOKUP関数によって受注票が作成されます。
作った受注票を印刷します
次の行を見に行きます
6～9をデータ範囲の最終行まで繰り返したら終了します。

受注マスタから受注票を印刷するマクロ

Sub 印刷準備確認()

'変数a
Dim a

'変数aにはい、いいえの質問メッセージボックスの押された結果を格納
a = MsgBox("プリンタの設定は完了していますか？", vbYesNo + vbQuestion + vbDefaultButton2, "確認")

'押されたボタンaの確認
Select Case a

'はいの場合
Case vbYes

'マクロ受注票印刷を実行する
Call 受注票印刷

'いいえの場合終了
Case vbNo

End Select

End Sub
―――――――――――――――――――――――――――――――――――――
Sub 受注票印刷()

'変数i、n、s
Dim i
Dim n
Dim s

'シート受注マスタを選択
Worksheets("受注マスタ").Select

'nにデータ範囲の最後の行番号を格納
n = Cells(Rows.Count, 1).End(xlUp).Row

'iが2からnまで繰り返し処理（データ範囲が2行目から）
For i = 2 To n

'列「印刷」が"1"の時
If Cells(i, 2) = 1 Then

'sに受注コードを格納
s = Cells(i, 1).Value

'シート"受注票"のセルB4にsを入力
Worksheets("受注票").Cells(4, 2) = s

'受注票を印刷
Worksheets("受注票").PrintOut

End If

Next i

End Sub

とこんな感じになります。

印刷準備確認のメッセージボックスを表示させるマクロと受注票を印刷するマクロを分けていますが、同じモジュール（Module）に記入しています。
1つのモジュールに複数のマクロを記入すると自動的に線が引かれて区別されます
一連の処理を複数のマクロに分ける場合は１つのモジュールに記入した方が分かりやすいと思います。

受注マスタの列「印刷」に“1”を入力します。

と、この様な受注票が印刷されます。

サンプルExcelファイル）North Wind.zipで実際に実行させてみてください。
サンプルファイルの使い方ですが、
まず、受注票を実際に印刷させて（PDFで可）印刷設定を済ませておいてください。
マクロの実行のボタンとかは用意していません。マクロを表示させて「F5」キーを押すとか、ショートカットを設定するとか、原始的な方法で実行させてください。

前のページ：マクロを実行するマクロ
次のページ：

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！
・実例１一覧表から帳票を自動で印刷させる

昔「デンスケ」と言われたカセットテープレコーダがあったのをご存じでしょうか？
所謂、録り鉄とか、ヒコーキの音とか野外の生録をするテープレコーダーです。
マイクもそれなりのをステレオなので2本用意しなければなりません。でもそんなんで録音した蒸気機関車の近づく迫力のサウンドは鳥肌もんでした。。。

【中古】迅速発送+送料無料＋動作保証!値引交渉歓迎!　SONY ソニー　TC-2810　カセットデンスケtypeIV【@YA管理1-53-24430】

楽天で購入

そえが今ではマイクも一体化してこんなカッコになっちゃいました。
そう言えば、パーソナルユースで音だけってあまり記録されないよね、動画としては音も一緒に当然撮られてるけど、音だけって無いよね。
なんでだ？音楽は音だけで聞くし最近はASMRとして音を聞いたりってのもあるけど、写真や動画の様に身の回りの音だけを録音するってのはあまり無いねー。。。スマホにもICレコーダーついてるんだから、もっと音遊びできるアプリができてもいいんじゃねぇ？とか思う。

ソニー(SONY) リニアPCMレコーダー PCM-D10 [16GB /Bluetooth対応 /ハイレゾ対応] (PCMD10)

楽天で購入

]]>

マクロを実行するマクロ / Excel / VBA

haku1569 — 2019-10-20T16:02:30+09:00

　メッセージを表示させて「はい」の時に次のマクロを実行させたりするときにCallで他のマクロを実行させます。
　また、色々な処理を組み合わせたマクロの場合、１つの長いマクロを作るよりも出来るだけ短いマクロを組み合わせた方が、その後の変更や更新がしやすかったりします。そんな時は複数のマクロをCallで続けて実行させます。

たとえば、こんなセルA1に100を入力するマクロがあります。

マクロを実行させるマクロ
Sub macro2()

'macro1を実行
Call macro1

End Sub

こんな感じ

これを実行させると、macro1が実行されます。で、
こうなります。

ちなみに、複数のマクロを連続して実行する場合は

前のページ：メッセージを表示する
次のページ：

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！
・実例１一覧表から帳票を自動で印刷させる

　最近は時間のチェックもスマホになって腕時計をしている人って少なくなっているようだけど、そういう意味では腕時計は単なる機能性だけでなく、ファッション性が求められているように思う。ファッション性と言っても、単なる装飾性だけではなく機能美こそが美しいとボクは思うのだけど。。。
　この、EPSONのスポーツウォッチ「TRUME」シリーズはオールチタン製のボディーに光充電、GPSセンサー内蔵で高度、気圧、方位も表示する万能ウォッチだ！
　そうそう、高度で思うのだけど、今いる場所が海抜何メートルの場所なのか水害から身を守るための重要な指標ですよ。

【キャッシュレス還元5%対象】【60回無金利ローンok】TRUME TR-MB7011 限定200本レザーバンド付属[EPSON エプソントゥルーム Mコレクション時計防水メンズ GPS 電波ソーラー]【店頭受取対応商品】

楽天で購入

]]>

メッセージを表示する / Excel / VBA

haku1569 — 2019-10-14T17:48:03+09:00

受注マスタから受注票を印刷させるためのマクロの基本的な試作は終わったんで、他に必要なマクロの試作を作ってみます。

メッセージの表示です。
例えば繰り返し処理（For～Next）を伴うマクロは一旦実行すると途中で止めることはできません。
まして印刷（紙出し）のマクロだと間違って実行させてしまうと紙が無駄になってしまうかもしれません。ので、マクロを実行させる前に注意喚起のメッセージとか、確認のメッセージを表示させてあげます。

メッセージボックス
MsgBox("メッセージ", vbYesNo + vbQuestion + vbDefaultButton2, "タイトル")

メッセージ：表示させるメッセージ
vbYesNo：「はい」、「いいえ」のボタンを表示します
vbQuestion：質問のメッセージボックス
vbDefaultButton2：既定値を「いいえ」
タイトル：メッセージボックスのタイトル

で、ボタンを押した結果は
「はい」：vbYes
「いいえ」：vbNo

複数の条件分岐
条件分岐と言うとIF～THEN～ELSEとなるけど、複数の条件での分岐の場合は
Select Case 変数　を使います。
Case vbYes：「はい」を押した場合
　　　処理

メッセージ表示マクロ

Sub Macro1()

'変数A
Dim A

'問合せメッセージボックス表示
'ボタン「はい」、「いいえ」
'既定値「いいえ」
A = MsgBox("印刷設定は完了していますか？", vbYesNo + vbQuestion + vbDefaultButton2, "印刷設定確認")

'押されたボタンの確認
Select Case A

'「はい」の場合
Case vbYes

'セルA1に1を表示する
Cells(1, 1) = 1

End Select

こんな感じです

マクロを実行すると
メッセージボックスが表示されます。
質問のメッセージボックスは“？”が表示されます

で、「はい」を押すとセルA1に1が表示されました

サンプルExcelファイル）20191013_メッセージボックス.zip

ちなみに、他のメッセージボックスは
vbCritical：警告

vbExclamation：注意

vbInformation：情報

と言うわけで、メッセージボックスは様々な場面でメッセージを表示させられる便利な機能ですが、あまり多用すると、「ウザッ！」ってなって、いちいち「はい」を押すのさえ「めんどくせー」ってことになるから、出来るだけ使わない方がいいと思います。
それに、最初は使用方法を補佐する目的で使いがちになってしまい、使い方を覚えてしまうと要らないメッセージになってしまいます。使い慣れてきてから、それでも必要なメッセージを表示させるようにした方が良いんじゃないかなー。。

前のページ：最終行を取得する
次のページ：マクロを実行するマクロ

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！
・実例１一覧表から帳票を自動で印刷させる

　ランタンって言うと、停電の時やキャンプなどで使用する灯りってイメージだけど、LEDになって乾電池でも長時間使用できるようになってからインテリア照明としても使用できるおしゃれな物も増えてきました。
　そんな中、満を持しておしゃれ家電メーカーのバルミューダが素敵なLEDランタンを発売しました。
　キャンドルのように揺らめく光や、調光機能による明るさレベルに応じて変化する光の色など、まったりしてしまう優しい灯りです。

ランタン led 充電式収納袋プレゼント【あす楽14時迄】送料無料 P5倍BALMUDA The Lantern バルミューダザ・ランタン L02Aキャンドルランプ照明間接照明アウトドアキャンプブラック◇ホワイトおすすめ非常用おしゃれ自動調化無段階 F

楽天で購入

]]>

haku1569　Excel でらくらくデータ分析！

矢印キーで画面が動いちゃう！？　/　こんな時はどうする？

目次

AVERAGEA / 文字列を含んだ平均値 / Excel / 関数

フィルターの解除（クリア）がもたついてきたら。。。 / Excel / 困った時は

共分散とは何ぞや / データ分析の解説

VBAに登場する意味不明な用語の意味 “モジュール”とか“ステートメント”とか / Excel / VBA

セルの行番号、列番号、アドレス（絶対位置）を取得する / Excel / VBA

IFステートメント~条件分岐 / VBA / Excel

フィルターを解除してソートし直して最終行の次を選択するマクロ / VBA / Excel

VLOOKUPのズレを防止する / 関数 /　Excel

列の範囲名を自動で設定する / 数式 / Excel

Debug.Printの便利な使い方 / Excel / VBA

一覧表から帳票を自動で印刷させる（完） / Excel / VBA

マクロを実行するマクロ / Excel / VBA

メッセージを表示する / Excel / VBA

haku1569 Excel でらくらく データ分析！

矢印キーで画面が動いちゃう！？ / こんな時はどうする？

目次

AVERAGEA / 文字列を含んだ平均値 / Excel / 関数

フィルターの解除（クリア）がもたついてきたら。。。 / Excel / 困った時は

共分散とは何ぞや / データ分析の解説

VBAに登場する意味不明な用語の意味 “モジュール”とか“ステートメント”とか / Excel / VBA

セルの行番号、列番号、アドレス（絶対位置）を取得する / Excel / VBA

IFステートメント~条件分岐 / VBA / Excel

フィルターを解除してソートし直して最終行の次を選択するマクロ / VBA / Excel

VLOOKUPのズレを防止する / 関数 / Excel

列の範囲名を自動で設定する / 数式 / Excel

Debug.Printの便利な使い方 / Excel / VBA

一覧表から帳票を自動で印刷させる（完） / Excel / VBA

マクロを実行するマクロ / Excel / VBA

メッセージを表示する / Excel / VBA

haku1569　Excel でらくらくデータ分析！

矢印キーで画面が動いちゃう！？　/　こんな時はどうする？

VLOOKUPのズレを防止する / 関数 /　Excel