goo blog サービス終了のお知らせ 

会社を卒業したのんちおじさん。

人生は知恵と工夫と思いやり!
優しさほど強いものはなく、本当の強さほど優しいものはない -ラルフ・W・ソックマン-

やっと終わった。

2013-10-02 16:28:23 | Excelのお話
エクセルの個人用マクロブックを「.XLS」から「.XLSB」に移植する作業をやっていたのです、エクセル2003は来年の4月でおしまいでこれからはエクセル2007以降が標準になるのでそれに合わせたのです。

職場ではまだ古いファイルが横行していますが最後近くになってみんなに配ったファイルは緊急性のないものは皆「.XLSX」で送りました、一部の人から「読めない」と言われましたがすぐに2010バージョンになるからそのあとに開くように言ってあります。

エクセルの書式設定で「00円00銭」と表示するには

2013-05-30 19:56:35 | Excelのお話
「#,##0"円".00"銭"」と書式設定すればできる・・と思ったのですが・・それだと「2810.05」が「2,810円.05銭」と「.」が入っちゃうのです。

関数では種々の方法があると思いますが別にセルが必要になるのです、どうしても書式設定で処理したい・・で調べてみたら次のような方法が見つかりました、頭のいい人がいますね。

「セルの書式設定」-「表示形式」タブから「ユーザー定義」の「種類」欄に次のように入力

・「#,##0"円"00"銭"」と入力(OKは押さない)
・その状態で[Ctrl]+[J]を入力、見た目は何も入力されない
 が改行文字が入力されている(まだOKは押さない)
・「/100」と入力(OKはまだ我慢)
・「配置」タブの「文字の制御」の「縮小して全体を表示す
 る」をチェック
・続いて「折り返して全体を表示する」をチェック
・ここでやっと「OK」を押す

調べた限りではここまで、セルに適当な「2810.05」のような数字を入力してみます、でも後ろが切れてしまうのです、そこで列フィールドの境目をダブル・クリックするとあーら不思議、出来ました。

excelで配列式

2012-12-04 20:05:14 | Excelのお話
あるメーカーに毎月定期的に商品を発注しています、アイテム数は在庫商品だけですがその数2,500を超えます、そこでこんなことが出来ないだろうかと考えました、それは・・。

合計 \59,357 MAX \1,230=みかん

の様に一行目を固定し、その行に「総合計」「最高額」「最高額の商品名」を常に表示する、というものです。

総合計と最高額は「SUM」または「SUMPRODUCT」で簡単に出ますが問題は「最高額の商品名」の表示、出来ればメモリー消費を抑えかつファイルサイズを抑えるために「配列式」を使って目的を達せられないか?

「配列式」で書きましたけどこれをいじくりまわして何とかできないかといろいろやってみました。

そして何日にも渡って試行錯誤を繰り返し、やっと出来たのが以下の式、かなり前に作りややこしいので今では解読する気も起きません、当時はまだ脳細胞も今より多かったのです。

{="合計 \"&TEXT(SUMPRODUCT($D3:$D2569,$I3:$I2569),"#,##0")&" MAX \"&TEXT(MAX($D3:$D2569*$I3:$I2569),"#,##0")&" = "&INDIRECT(ADDRESS(SUM(IF($D3:$D2569*$I3:$I2569=MAX($D3:$D2569*$I3:$I2569),ROW($A3:$A2569))),1))}

PS.
こうすると横計の列を設けなくても目的を達せられ、ファイルサイズを小さくすることに寄与するのです。

エクセルからグーグルマップを使う。

2012-06-07 19:37:02 | Excelのお話
エクセルを開いていてその中のデータを元にグーグルで検索できるようにしてあるのですが今回一歩進んで住所を元にグーグルマップでその場所の地図を開けないかとやってみたところあっけなくできてしまったのでお披露目します。

セル上で右クリックしたときのメニューに載せるためのおまじない、これは一度実行すればOK。

Sub Googlemap_On()
  With CommandBars("cell").Controls.Add(before:=1)
    .Caption = "グーグルマップ"
    .OnAction = "Google_map"
  End With
End Sub

以下がメインの記述、上の記述で右クリックメニューに載った"グーグルマップ"をクリックするとこいつが走ります。

Sub Google_map()
  CreateObject("Wscript.shell").Run _
    "http://maps.google.co.jp/maps?q=" & ActiveCell, 1
End Sub

便利なので外すこともないと思いますがメニューから除きたいときは以下のマクロを実行します。

Sub Googlemap_Off()
  CommandBars("cell").Controls("グーグルマップ").Delete
End Sub

上記いずれも「Personal.xls」に記述。

多分分かってもらえないと思う。

2012-03-23 19:34:04 | Excelのお話
イレギュラーの注文書やその他種々の依頼用のフォーマットを数年前に作り職場で使っています、ただ、見積もり回答用のものを特に作ってなく各々が好き勝手なものを使っていたり手書きで済ませていたのです、それを今回一元化できないかともう2週間以上あれこれやっているのです。

基本は表題の内容によって宛先がメーカーや仕入先と客先を切り替えると言うもの、言うは易く、行うは難しで「入力規則」でリスト表示する場合かなり骨が折れた、と言ってもまだ完成してないんですけど・・・・見通しが付いたのでちょっと書いてみます。

まず「表題」と言う名前範囲に「注文書、見積り依頼書、サンプル発送依頼、御見積書」のリスト表示を設定します。

範囲名「仕入先」(宛先)には入力規則で次の関数を入れる=IF(表題="御見積書",客先名,仕入先名)

範囲名「仕入先担当者」には同様に次の関数=IF(表題="御見積書",INDIRECT(仕入先LINK),気付_LINK)

範囲名「仕入先LINK」をテンポラリーシートに設定し次の関数を設定=OFFSET(INDIRECT("口座客名簿!C"&MATCH(仕入先,口座客名簿!C:C,0)),0,-1)

範囲名「気付_LINK」を同様に設定し次の関数設定=VLOOKUP(仕入先,仕入先一覧,2,FALSE)

ここで範囲「仕入先LINK」ではOFFSET関数でふりがなを引っ張り出しその客先の担当者リストにそのふりがなをそのまま範囲名としています、それを「仕入先担当者」欄のリスト表示にINDIRECTさせています。

もちろん、OFFSETを使った範囲の自動拡張は使えないので手作業で範囲名を設定する必要があります。

本当は画像をアップしたいのですが個人情報がいっぱい表示されるので言葉だけで書いたのですけど読んだだけで分かってもらえるでしょうか。

ああ、勘違い!

2011-12-27 19:17:01 | Excelのお話
前回の書き込みで"文字数が255文字を超えない"と書きましたけど検証してみたらこれは文字列として変数に代入できる最長の文字数でした。

よって前回のようにしなくても変数内の文字列数が255文字を超えなければ"Union"による方法で引数にだらだらと書き連ねることができるみたいです。

具体的には引数に範囲の名前を指定した場合「$A$1,$B$1,.....」となり「$A$1,」は5文字になります、何文字になるか分からないときは

Sub test()
Set 注番 = Union(Range("鹿児島県A1"),Range("鹿児島県A2"))
MsgBox Len(注番.Address)
End Sub

で分かります。

VBAで扱う範囲の名前で苦労する。その2

2011-12-27 13:30:20 | Excelのお話
結局以下のようにして担当者欄に名前がない場合は印刷を中断させることにしました。

Sub 印刷()
Dmi ShtNam As String
Dim c
Dim n
With ActiveSheet
  ShtNam = .Name
  For Each c In Range(ShtNam & "担当")
    n = n & c.Value
  Next c
  If n = "" Then
    MsgBox "名前を入力してください。"
    Exit Sub
  End If
  ActiveWorkbook.CustomViews(ShtNam).Show
  .PrintOut
End With
End Sub

VBAで扱う範囲の名前で苦労する。

2011-12-27 12:54:56 | Excelのお話
シートが複数ありそれぞれに「担当」という欄があります、またその欄には「シート名+担当」という名前が付けてあります、例えばシート名が「エプロン」ならば「エプロン担当」てな具合です。

印刷マクロで担当欄が空白の場合メッセージを出そうとしたのです、方法として次のような方法を採りました、"担当"の部分は同じなので

Sub test()
Dim ShtNam As String
ShtNam = ActiveSheet.Name
If Range(ShtNam & "担当").Value = "" Then
  MsgBox "名前がありません。"
End If


End Sub

ところがどうしても「型が一致しない」と怒られるのです、その原因がどうしても分からずずっと考えていました、そのセルは6つのセルを結合してあります、結合する前に6つのセル範囲に名前を付けその後に結合しました、どうやらこれが原因のようです。

つまり仮にA1:C1に「範囲」という名前を付けます、そしてB1にデータを入力します、そして「範囲」のデータを表示するマクロを組んでもA1のデータなのかB1のデータなのかC1のデータなのかが分からないのです、どうしてもそうしたい場合は以下のようにすればできまし。

Sub test()
For Each n In Range("範囲")
  If n.Value <> "" Then
    MsgBox n.Value
  End If
Next n
End Sub

でも面倒なので結合セルをVBAの中で名前参照したい場合は結合した後に名前を付ける、ということでOK、ということはすべての名前付範囲の名前を付け直すのということ?

複数の範囲を初期化する

2011-12-26 19:21:19 | Excelのお話
12箇所のそれぞれが離れている複数の範囲を初期化するために次のように記述したけどエラーが出ます。

Set 注番 = Union(Range("鹿児島県A1"),Range("鹿児島県A2"),......)

と、これが"鹿児島県A12"まであります、"Union"の制約として引数は30個まで、最低二つは指定が必要というのは知っています、それ以外にも何かありそうだなと調べてみたら"文字数が255文字を超えない"こととあります。

じゃあどうすれば?と今日は午後から考えていたのですが

変数 = 変数 + 1

と同じ考え方をすればいいんだと気がつきました、そして次のようにして解決。

Set 注番 = Union(Range("鹿児島県A1"),Range("鹿児島県A2"))
Set 注番 = Union(注番,Range("鹿児島県A3"),Range("鹿児島県A4"))
Set 注番 = Union(注番,Range("鹿児島県A5"),Range("鹿児島県A6"))
Set 注番 = Union(注番,...,Range("鹿児島県A12"))

  注番.ClearContents

連番保存ができない原因が分かった。

2011-12-22 19:58:16 | Excelのお話
それはWSH「Windows Scripting Host」がいつの間にかアンインストールされていたことが原因でした、この機能を使ってcommand.comやcmd.exeのコマンドシェルを実行していたのです、前の書き込みの

If nHObj.FileExists(Target) Then

の部分でファイル(Target)の存在を確認しているのですがDOSでの

C:\>dir "file"

と同じ動作、その結果を拾ってきていたわけですが実はアンインストールした記憶はまったくないのです、だってWSHがなければ不具合が出るのは重々分かっているのだから。

でも今は別の方法でやっているから今更敢えてインストールする気もありません。

連番保存をあきらめた。

2011-12-20 19:42:17 | Excelのお話
オンライン発注済のデータを取り込んで都度プリント・アウトするとともにそのデータも連番で保存するマクロを作って何年も使っていたのに今月初め突然動かなくなってしまいました。

そこでデータの連番保存をあきらめて「今日何回目」の発注かが分かるように方針を変更しました、次のマクロは変更前のものでファイル末尾に番号を入れています、同名のファイルがあったら末尾の番号に「+1」して保存しています。

  For i = 1 To UBound(r_name) 'ファイルを順に処理
    Set nHObj = CreateObject("Scripting.FileSystemObject")
begin:
    番号 = 番号 + 1
    Target = _
     "C:\IDEC\IDEC_手配済(CSV)\" _
     & Format(Now(), "yyyy") & "_" & Format(Date, "mmdd") _
     & "_" & 番号 & ".csv" 'ファイル名をセット
    If nHObj.FileExists(Target) Then
      GoTo begin
    Else
      Name r_name(i) As Target '名前を変えて保存
    End If
    Set nHObj = Nothing
  Next i

次のが新しくした部分、まず日付が今日と違う場合初期化して「+1」をしているだけ。

'-------回数をカウントする--
If Range("日付").Value <> Date Then
  Range("日付").Value = Date
  Range("回数") = 0
End If
  Range("回数") = Range("回数") + 1

そしてフッター部分にそれを反映してプリントアウト。

ActiveSheet.PageSetup.CenterFooter = _
    "&""MS UI Gothic""&20&B" & _
    Format(Date, "ggge""年""m""月""d""日""(aaa)") & " " & _
    Range("枚数").Value & "回目" & " - &P/&N"

チェックの重複と忘れをチェックするマクロ

2011-12-07 19:56:55 | Excelのお話
「ダブルクリックしたときに「レ点」を入れる(イヴェント・マクロ)」で重複を防ぐマクロを作りました、でもダブルクリックせずとも直接「レ」を入力してしまうこともあるだろうと今度はそのチェックをするマクロも作りました。

Sub レ点チェック()
Dim Rng_1 As Range, Rng_2 As Range, Rng_3 As Range, Rng_4 As Range
Dim chk_1 As Long, chk_2 As Long, chk_3 As Long, chk_4 As Long, all_chk As Long
Dim c As Variant

Set Rng_1 = Union(Range("登録済"), Range("新規登録"))
Set Rng_2 = Range("着払い:IDEC元払い")
Set Rng_3 = Union(Range("必要"), Range("不要"))
Set Rng_4 = Union(Range("同封"), Range("貴社送り"))
all_chk = 0
chk_1 = 0
chk_2 = 0
chk_3 = 0
chk_4 = 0

For Each c In Rng_1
  If c.Value = "レ" Then chk_1 = chk_1 + 1
Next
  If chk_1 = 1 Then all_chk = all_chk + 1
For Each c In Rng_2
  If c.Value = "レ" Then chk_2 = chk_2 + 1
Next
  If chk_2 = 1 Then all_chk = all_chk + 1
For Each c In Rng_3
  If c.Value = "レ" Then chk_3 = chk_3 + 1
Next
  If chk_3 = 1 Then all_chk = all_chk + 1
For Each c In Rng_4
  If c.Value = "レ" Then chk_4 = chk_4 + 1
Next
  If chk_4 = 1 Then all_chk = all_chk + 1
If all_chk < 4 Then MsgBox "重複チェックまたはチェックされていません。"
If all_chk = 4 Then MsgBox "OK!"
End Sub

だけど実に冗長でみっともないのでもっときれいにならないかと先週末から考えてやっと出来たのがこれです。


Sub レ点チェック2()
Dim Rng(1 To 4) As Range
Dim chk(1 To 4) As Long
Dim c As Variant
Dim i As Long

Set Rng(1) = Union(Range("登録済"), Range("新規登録"))
Set Rng(2) = Range("着払い:IDEC元払い")
Set Rng(3) = Union(Range("必要"), Range("不要"))
Set Rng(4) = Union(Range("同封"), Range("貴社送り"))
all_chk = 0

For i = LBound(Rng) To UBound(Rng)
  chk(i) = 0
  For Each c In Rng(i)
    If c.Value = "レ" Then
      chk(i) = chk(i) + 1
      all_chk = all_chk + 1
    End If
  Next c
Next i

If all_chk < 4 Then MsgBox "重複チェックまたはチェックされていません。"
If all_chk = 4 Then MsgBox "OK!"
End Sub

われながら実にスマートに出来たと自画自賛、ここでとても悩んだのは変数「chk」は変数「Rng」の数に等しいけど変数「Rng」の中身は必ずしも決まっていないこと、例えばRng1の内容が(A1,B1)Rng2の内容が(A3,B4,D5,E6)だったりした場合どのように処理をしたらよいか分からず初めのマクロのように範囲ごとに記述していたのです。

結果、変数の数が決まっている「Rng」には「For ...Next」を使いその入れ子として変数の数が不定のときに使える「For Each...Next」を使ったらうまく行きました。

エクセルで図形を範囲の中央に配置したい。

2011-12-05 19:06:43 | Excelのお話
エクセルで図形を範囲に貼り付けるときキー操作だけでは意図したように貼り付けが難しい場合があります、例えば複数セル範囲の中央とか、範囲の内側一杯とか、それを形状を変えずにするとか。

それぞれ記述の仕方が変わってきます、

Sub 範囲の内側一杯に図形を配置()
Dim Rng As Range
Set Rng = Range("D5:G12")
  With ActiveSheet.Shapes("Circle")
    .Top = Rng.Top
    .Left = Rng.Left
    .Height = Rng.Height
    .Width = Rng.Width
  End With
End Sub

Sub 範囲の中心に図形を配置()
Dim Rng As Range
Set Rng = Range("D5:G12")
  With ActiveSheet.Shapes("Circle")
    .Top = Rng.Top + (Rng.Height - .Height) / 2
    .Left = Rng.Left + (Rng.Width - .Width) / 2
  End With
End Sub

Sub 形状を変えずに範囲の内側一杯に図形を配置()
Dim Rng As Range
Set Rng = Range("D5:G12")
  With ActiveSheet.Shapes("Circle")
    .LockAspectRatio = msoTrue
    .Top = Rng.Top
    .Height = Rng.Height
    .Left = Rng.Left + (Rng.Width - .Width) / 2
  End With
End Sub

ダブルクリックしたときに「レ点」を入れる(イヴェント・マクロ)

2011-12-02 19:50:15 | Excelのお話
シート上で項目にチェックを付ける時コントロールツールボックスのチェックボックスを使うかマクロのツールボックスを使うのが常道ですけどセル自体にそのチェックを入れたい場合はこれらは使えません。

あるメーカー指定の申請書がエクセルで出来ていてそのフォーマットに記入して使うようになっているときそのままだと印刷してから手書きしなければなりません、これは億劫なので当然データを引っ張ってきて貼り付るのですがチェックを入れる場所は単なる四角があるだけなので出来ればそれはイヴェント・マクロでなんとかしたいのです。


そのセルが空白なら「レ点」を入れ、「レ点」が既に入っていたらクリアする。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Ca
ncel As Boolean)
Cancel = True
With Selection
  If .Value = "レ" Then
    .Value = ""
    Exit Sub
  End If
  If .Value = "" Then
    .Value = "レ"
    Exit Sub
  End If
End With
End Sub

これでいいわけですが2箇所以上の複数箇所で常に選択肢をひとつとしたい場合、つまり排他入力をさせたい場合はちょっとした工夫が必要になります。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Ca
ncel As Boolean)
Cancel = True
With Target
  Select Case .Address
'=======範囲Aと範囲B片方だけに「レ点」を入れる
    Case Range(範囲A).Address
      .Value = "レ"
      Range(範囲B).ClearContents
    Case Range(範囲B).Address
      .Value = "レ"
      Range(範囲A.ClearContents
'===========================================範囲C,範囲D,範囲Eのいずれか一箇所に「レ点」を入れる
    Case Range(範囲C).Address
      .Value = "レ"
      Range(範囲D:範囲E).ClearContents
    Case Range(範囲D).Address
      .Value = "レ"
注*      Union(Range(範囲C), Range(範囲E)).ClearContents
    Case Range(範囲E).Address
      .Value = "レ"
      Range(範囲C:範囲D).ClearContents
'===========================================
  End Select
End With
End Sub

注*は連続していない範囲を一括処理する場合にとても便利ですが
  Range(範囲C).ClearContents
  Range(範囲E).ClearContents
と記述してもまったく同じです、でも不連続の範囲が多い場合はUnionを使った方がいいかもしれません。

それと忘れちゃならないのが「Cancel = True」、これは編集状態をキャンセルするか継続するかという記述で「Cancel = False」とするとセルが編集状態のままになり使いにくいことこの上ない、だからこの記述を忘れないこと。

都道府県の一覧をEXCELで

2011-10-15 08:03:09 | Excelのお話
都道府県の一覧をEXCELのオートフィルで使いたい、確か自前のオートフィルのリストを作れたはずと思って探したらそのものずばりのページがありました「自作連続データの挿入」というところで都道府県の一覧も載っているので紹介してある通りにやってみました、結果はNG!

えっ?!なんで?何度やっても「北海道」「北海道」・・・が続いてしまうのです、これならば一つ一つ入力した方が速い、でもそれじゃあOffce-Masterの名折れ。

どこが悪いんだろうと出来上がった「ユーザー設定リスト」を注意深く見てみると「北海道」「青森県」「岩手県」・・・それぞれのデータの間が間延びして見えます、どうやら空白が入っている模様、だから「北海道」と入力してもオートフィルができなかったことが分かりました。



そこでB1に「=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))」と入力しB47までコピーしB1:B47をコピー「形式を選択して貼り付け」で「値」を選び同じ範囲に貼り付け、そして再び「ユーザー設定リスト」を作成しました、結果はもちろんOKでした。

ここでちょっと別の話題、いつも読んでいる雑誌にこんな記事があった、関数は少し長くなると解読に骨が折れることもあります、実は関数の記述に空白や改行を入れることも可能だというのでやってみたのが二つの図、こうすることで格段に読みやすく後で見たときにも判りやすいですね。





話を戻して今回は空白を取り除くのに関数を使ったのですがなぜ「TRIM関数」を使わなかったのかというとこの「空白」、そう見えるだけで普通の空白ではないようで「TRIM関数」では取り除けなかったのです、実はexcelでたまに同じようなことに遭遇します、職場のオフコンが吐き出すデータにそんなのが紛れ込んでいることがあります。

ならば空白を置換してみればと言われるかも知れないですがもちろんやってみました、これも「TRIM関数」同様単なる空白ではないのでNG、でも方法はあるものです、「空白に見える」部分をコピーして「検索する文字列」欄に貼り付けます、そして置換すればよいのです、はじめにこれが判っていれば関数を使っての冗長な方法は取らずに済んだのでした、でも関数に空白や改行を入れて使うことができるということもお披露目したかったので無駄ではなかったですね。

しかしながらこのページの主さん、訪れた人はきっと出来なくて困っている人がいると思います、補足しておいた方がよろしいかと・・・。

ちなみに今回空白に見えたのはJISのコード番号では(160)、それが判っても何の役にも立たないが、いや、マクロで一括置換するときに使えるかも、でも必ずコード番号が(160)とは限らないし・・・。