タグ:Excelワザ ( 162 ) タグの人気記事

パズル作成

冬ごもりの第2弾
エクセルマクロで、
オートシェイプで描いた絵を
5×10のピースにして、
パズルにします。
アスキーPCに載ってたマクロを
少々アレンジ。
b0186959_12215368.jpg


1.画像を準備
2.画像に合わせてセルの行高を変更。
 幅は8.38のまま、
 行高は40にしました。
 (画像により要調整)
3.わかりやすいように、
 セルH2~L11に格子の線を入れておきます。
b0186959_1353159.jpg

4.マクロ記述

Sub パズル作成()
'選択した画像で50分割のパズルを作成する
Dim FileName As Variant
Dim Zukei As Shape
Dim Piece As Range
Application.ScreenUpdating = False
FileName = Application.GetOpenFilename()
If FileName = False Then Exit Sub
For Each Zukei In ActiveSheet.Shapes
Zukei.Delete
Next Zukei
Range("b2").Select
ActiveSheet.Pictures.Insert(FileName).Select
Selection.Width = 270
Selection.Left = 50
Selection.Top = 40
Randomize
For Each Piece In Range("b2:f11")
Piece.CopyPicture
Range("h2").Select
ActiveSheet.Paste
Selection.Left = Rnd() * 501 + 600
Selection.Top = Rnd() * 31 + 150
Next Piece
Application.CutCopyMode = False
Range("a1").Select
Application.ScreenUpdating = True
End Sub

5.マクロ実行
 すると、
 まず、ファイルを開く画面が出て、
 目的の画像を指定、開く。
b0186959_12355377.jpg

6.すると、
 画像と、
 50ピースに切り取られた画像が
 表示されます。
b0186959_124087.jpg

7.これを、配置していきます。
 (Alt抑えながらドラッグすると、
 枠線にぴったり合って配置できます)
 わかりやすい部分から攻めて…
b0186959_12473378.jpg

8.完成!
b0186959_132625.jpg

画像の大きさによって、
数値を調整せんなんね(^-^;
[PR]

by school-pc | 2016-01-21 13:03 | Comments(0)

冬ごもり…モグラたたきゲーム

雪が降って寒い…^^;
おうちにこもって…
アスキーPCに載ってた
モグラたたきゲームを作りました。

モグラを描いて用意。
b0186959_14475864.png

エクセルマクロを記述。
Sub Mogura()
'モグラのイラストを10回クリックするタイムを競う
Dim LeftPoint As Single
Dim TopPoint As Single
Dim KaisiTime As Single
Dim SyuryouTime As Single
Dim myScore As Single

Randomize
LeftPoint = Rnd() * 301 + 300
TopPoint = Rnd() * 260 + 20

'1回目(最初)
If Range("c4").Value = "" Then
Range("c4").Value = 9
Range("c5").Value = Timer
ActiveSheet.Shapes("モグラ").Left = LeftPoint
ActiveSheet.Shapes("モグラ").Top = TopPoint

'10回目(最後)
ElseIf Range("c4").Value = 1 Then
SyuryoTime = Timer
KaisiTime = Range("c5").Value
myScore = SyuryoTime - KaisiTime
Range("c4:c5").Value = ""
Range("c21").Value = myScore
MsgBox "記録:" & myScore & "秒", vbInformation, "Score"

If myScore < Range("c23").Value Or Range("c23").Value = "" Then
Range("c23").Value = myScore
MsgBox "最高記録更新!", vbInformation
End If

'2~9回目
Else
Range("c4").Value = Range("c4").Value - 1
ActiveSheet.Shapes("モグラ").Left = leftPont
ActiveSheet.Shapes("モグラ").Top = TopPoint
End If
End Sub
b0186959_1455358.jpg

マクロをモグラに登録。
クリックするたびにモグラが移動。
モグラたたきのスピードを表示します。
b0186959_14573772.jpg

[PR]

by school-pc | 2016-01-20 14:57 | Comments(0)

Excelワザ_グラフの大きさを揃える

Tさんからの質問
「円グラフをピチっと、大きさを揃えて並べたい」
b0186959_11131730.jpg

プロットエリア等のサイズを
数値で揃えるのは、
マクロでしかできないみたいなので、
一般機能を使って綺麗に揃えてみましょう(^^♪
1.まずはグラフエリアのサイズを統一します。
 一つ目のグラフクリック、二つ目のグラフから
 Shift or Ctrl押しながらクリックし、
 すべてのグラフを選択。
2.書式タブ→高さと幅を 例えば「6cm」に設定。
b0186959_11243592.jpg

 サイズは揃いました。
b0186959_1322114.jpg


3.次に上と左右の間隔を揃えます。
 2段にしたいので、上4つを選択し、
 一番左と一番右のグラフの位置を
 だいたい決めます。
 そして、
 書式→配置→上揃え
 書式→配置→左右に整列
b0186959_12172449.jpg

 下の段の4つのグラフも同様に。
b0186959_1220337.jpg

4.位置とグラフサイズは揃ったが、
 円グラフの大きさがバラバラ。
 それを揃えるには、
5.デザイン→クイックレイアウト
 →例えばレイアウト1を選択。
 すべてのグラフをレイアウト1にする。
 すると、既定の円の大きさに揃います。
b0186959_1302458.jpg

全ての円が同じ大きさに揃いました(^_-)-☆
円の大きさを一番大きくしたかったら、
レイアウト3、ですね~
b0186959_1354419.jpg

[PR]

by school-pc | 2015-12-28 13:00 | Comments(0)

Excelワザ_ゴールシークを使ってBMI

忘年会やクリスマス、
ついつい食べ過ぎてしまう
今日この頃…なんてね^^;
で、
BMI
ボディマス指数(ボティマスしすう)とは、
体重と身長の関係から算出される、
人の肥満度を表す体格指数である。
一般にBMI (Body Mass Index) と呼ばれる。
計算式
 BMI= 体重kg ÷ (身長m)2
 適正体重= (身長m)2 ×22
判定基準があるみたい。
b0186959_11412426.jpg


そこで、
例》身長170cmの男性。
ギリギリセーフの
「BMI値24」の体重は何キロ?
をゴールシークを使って調べましょう。
b0186959_12115.jpg

1.セルC8に式を立てておきます。
  「=C7/(C6/100)^2」
b0186959_125823.jpg

2.セルC7をアクティブにして
データ→What-If分析→ゴールシーク
b0186959_1263495.jpg

 →OK
すると、
b0186959_1273433.jpg

3.そして、セルC7に
BMI値24の体重が「69.36」と出ました。
b0186959_1216517.jpg

身長170cmの人は「69.36kg」以上増えないように
気を付けましょう(^-^;
[PR]

by school-pc | 2015-12-12 12:10 | Comments(0)

Excelワザ_式を見えなくする

せっかく立てた式を
削除されたくない、
むしろ、数式を非表示にしておきたい。
というときに、
数式バーの式を見えないようにできます。

b0186959_20423293.jpg

株式会社や有限会社等を
はずして、顧客名だけで並べ替えたい
というときの数式が入っています。
この式を、
数式バーから消して見せましょう(^_-)-☆
1.数式の入っているセル範囲選択.
  (セルB2:B16)
2.セルの書式設定→保護タブ
  「表示しない」にチェック。
b0186959_20472465.jpg

3.ホームタブ→セル→書式
 →シートの保護→OK
4.すると、あ~~ら不思議。
 見えないですねぇ(^-^;
b0186959_2053169.jpg

5.式を見えるようにするには、
シートの保護の解除をします。

ちょっとしたネタでした~~(^_-)-☆
[PR]

by school-pc | 2015-12-09 20:53 | Comments(0)

Excelワザ_生年月日から干支を画像付きで表示

今年もあと1月あまりになりました…
来年は、申年ですねぇ
ということで、
生年月日から、干支を出し、
さらに、画像も表示できるように作ってみました。
過去記事の社員証の作成よりも簡単。
b0186959_12395659.jpg


1.干支一覧表シートに
  干支一覧表を作成。
b0186959_1243472.jpg

2.個人票シートに
  表示するための個人票を作ります。
b0186959_12585786.jpg

3.まず、生年月日から、干支を出す方法。
 個人票シートのセルC4に式を立てます。
  (ネットで干支を出す方法は色々出ていますが、
  一番簡単な方法でやってみます。)
=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(C3),12)+1,1)
※干支は西暦を12で割った余りで出すことができます。
MID関数の第1引数「文字列」に
「"申酉戌亥子丑寅卯辰巳午未"」の順番で干支を入力し、
第2引数「開始位置」には西暦を12で割った余りを求めるため
MOD関数とYEAR関数で数式を作成します。
この際に
12で割り切れる申年は余りが「0」
となってしまい、MID関数の開始位置が指定できずエラーとなってしまいます。
このためMID関数の結果に「+1」にします。
第3引数「文字数」には干支リストの「開始位置」から
1文字ずつ取り出すため「1」と指定することで
生年月日から干支が求められます。

b0186959_13421696.jpg

4.次にセルD4に画像を表示させます。
 まず、名前の定義をします。
 ・干支名が入っている範囲は干支一覧
 ・画像が入っている範囲は画像一覧
 ・干支それぞれにセル番地を名前定義
★ここで、一工夫
 ・画像という名前の範囲には、
 「=INDEX(画像一覧,1,MATCH(個人票!$C$4,干支一覧,0))」
 という式を入れておきます。
b0186959_13191829.jpg

5.セルD4をコピー
6.そのままセルD4で
  貼り付けのオプション一覧から
  「リンクされた図」を選択。
7.数式バーには、
  「=$D$4]となっている。
b0186959_13232126.jpg

8.数式バーの「=$D$4]
  を「=画像」と書き換えます。
9.すると、画像が表示されます!
b0186959_13252015.jpg

b0186959_13491299.jpg

どうでしょう?
ちょっと面白くない?(^^♪
[PR]

by school-pc | 2015-11-26 12:39 | Comments(0)

Excelワザ_Isformula関数

Mさんからさらに…(^^;
2015/11/4記事の追加。

商品コードを
だ~と入力していきたい。
が、右隣のセルに
文字列の入ったセルか
または空白のセルは
飛ばしたいので、入力規則で「ピロ~ン」と
音が出て、入力しないようにしたい。
b0186959_18153332.jpg

11/4記事のは隣のセルが
文字列だけの場合だったので、
「入力値の種類」→ユーザー設定
 「数式」→「=ISTEXT(C2)=FALSE」
で出来たが、空白のセルがある場合は、
入力できてしまう。

2つの条件にあてはまる入力規則を作るには、
バージョン2010までは、
①作業列を1列作って空白の表にも何か文字を入れておく。
②VBAを使うl。
のどちらかしか方法がなかったが…
Excel2013で追加された関数
Isformula関数で出来ます!
b0186959_17192515.jpg


ISFORMULA関数
=ISFORMULAイズ・フォーミュラ(参照)
(参照)が数式かどうかを調べます。
戻り値は、(参照)が数式ならばTRUE(真)、
数式以外ならばFALSE(偽)になります。


入力するセル範囲を選択し、
 データ→データの入力規則
 →設定タブ
 「入力値の種類」→ユーザー設定
 「数式」→「=ISFORMULA(E2)=TRUE」
※ここで、数式の入っているE列を指定するのがミソ。
b0186959_18173464.jpg

すると、
右隣が空白のセルも文字列のセルも
ピロ~ンと音が出て、入力不可になります。
b0186959_1872022.jpg


2013、進化してるやん。
が、もう、2016が出てるので、
更に進化しているのか…(^^;
[PR]

by school-pc | 2015-11-20 18:08 | Comments(0)

Excelワザ_途中のセルに入力出来ないようにする

Mさんからの質問。

データ入力する列の途中に
入力してはいけないセルが混ざっている。

入力する領域だけセルのロックを外し、
シートの保護をかける。
という操作をすると、あとから
非表示にしたい列の操作ができない。
b0186959_1255892.jpg

入力時に「ピロ~ン!」
と音が出て、入力時に気が付くように!

色を付けるぐらいでは見過ごすので、
音が出るように!

b0186959_12124292.jpg

ということで、
データの入力規則のユーザー設定を使って解決。
1.入力してはいけないセルの左のセルには、
 「小計」という文字が入っている。
 (入力するセルの左のセルには数式が入っている。)
そこで、
2.入力するセル範囲を選択し、
 データ→データの入力規則
 →設定タブ
 「入力値の種類」→ユーザー設定
 「数式」→「=ISTEXT(C2)=FALSE」
b0186959_12155683.jpg

(入力する左のセルがテキスト(文字)かどうかを調べる。
→文字でない=FALSE のみ入力値の種類とする。
→TRUEつまり文字なら入力できる値ではない。)
3.エラーメッセージタブ
 下の様に入力。
b0186959_1217793.jpg

→OK

すると、
ダーと入力している途中で、
「ピロ~ン」と音が出て、
入力エラーのメッセージが。
b0186959_1219318.jpg


これで、気が付きますね(^_-)

 →
[PR]

by school-pc | 2015-11-04 12:20 | Comments(0)

園児の満年齢を調べる

10月1日現在の園児の年齢を調べたい!
ということで…
b0186959_10205687.jpg

Datedif関数を使いましょう。
1.セル【F2】に10月1日の日付を入力しておきます。
2.セル【F4】に式
  =DATEDIF(E4,$F$2,"y")
  セル【G4】に式
  =DATEDIF(E4,$F$2,"ym")
  セル【H4】に式
  =DATEDIF(E4,$F$2,"md")
すると、10月1日現在の年齢が
◯歳◯ヶ月◯日と出ます。
b0186959_1025133.jpg

これが出来ると、
オートフィルタを使って、
3歳児だけ、4歳児だけとかを
抽出することが出来ます。
b0186959_1028413.jpg


Countif関数を使うと
セル【F24】の式→=COUNTIF($F$4:$F$23,E24)
セル【F25】の式→=COUNTIF($F$4:$F$23,E25)
セル【F26】の式→=COUNTIF($F$4:$F$23,E26)
b0186959_1551627.jpg

I様、これ使えそう?
[PR]

by school-pc | 2015-09-29 10:28 | Comments(0)

Aで始まる国は…

English Classでのゲームで
「A」で始まるCountry、
「B」で始まるCountryっていうのをして…
…じゃあ、調べてみましょう。

1.まずネットから国名一覧をコピー
  
b0186959_1042775.jpg

2.Wordにテキストのみ貼り付け。
 このサイトでコピーしたものは、
 日本語表記、( 、英語表記、国際電話の国番号、)
 の順になっています。
 
b0186959_1181740.jpg

 最終的には、Excelで処理したいので、
 Wordの置換機能で、処理しやすい形に直します。
3.「ア行[編集]」のような行を削除します。
 置換の画面で、検索文字列を「^?行[編集]^p
 と入力します。(「行[編集]」の部分はコピー&ペースト)
 (最初の「^?」の部分は、ア~ワの任意の1文字部分、
 最後の^pは段落記号、
 で、ア~ワ行[編集]段落記号の行を削除する。
 という意味になります。
b0186959_12163068.jpg

4.これをテキストファイル「国名一覧」として保存します。
b0186959_12173075.jpg

4.次にExcelからデータをインポートします。
 データ→外部ファイルの取り込み→テキストファイル
 テキストファイルのインポート→「国名一覧」選択
 →インポート
 →テキストファイルウィザード1/3→「カンマやタブによって……」チェック次へ→
b0186959_1233288.jpg

 →区切り文字を「カンマ」と「その他に(」を入力。→次へ
b0186959_12353433.jpg

→完了
b0186959_12405326.jpg

インポートできました。
b0186959_1242254.jpg

5.C列は、いらないので、削除。
6.1行目にタイトルを入力。
 A1:B1を選択し、Shift+Ctrl+↓を押すと、全データが
 選択出来るので、罫線を引きましょう。
b0186959_12472770.jpg

7.A列とB列を入れ替えます。
 B列を選択し、Shift+ドラッグで、移動挿入。
b0186959_12492740.jpg

8.A、B、Cの順に国名を並べ替えます。
 A列の任意のセルをアクティブ、昇順に並べ替えのボタンクリック。
 A~Z順に並び変わりました。
b0186959_12522121.jpg

9.次に関数で、頭文字A~Zで始まる国は何件か調べます。
10.COUNTIF関数を使います。
  D列に「A~Z」まで入力しておきます。
 E列に式「=COUNTIF(A:A,D2&"*")」
b0186959_12582217.jpg

 条件に(D列の文字にあと何か文字、という意味の*(ワイルドカード)
 を「&」でつなぎます。
 すると、「15」件と出ました。
 式をコピーします。
b0186959_1314481.jpg

「X」で始まる国はないですね(^_-)







 
[PR]

by school-pc | 2015-07-31 13:02 | Comments(0)