Excelのどうでもよいtipsの紹介

Excelのどうでもよいtipsの紹介

Transcript

1. Excelのどうでもよい Tipsの紹介 @tsuda_ahr LTDD #19
2. Excel 2016 でました。パッケージ版は廃止との由。 http://pc.watch.impress.co.jp/docs/news/20150929_723249.html
3. お値段は 14,800 円
4. Office ではなく Excel 単体? いや、実際、Excel しか 使ってないよね？ (ぇ
5. 話は違いますが Visio は健在です。みんな買いましょう。
6. さて本題
7. いくつか、どうでもいい Tip を紹介します。 1. ワークシート名をセルに表示したい 2. 条件付き書式を固定化したい 3. 結合したセルの高さを自動調整したい 4. CTRL+選択で選択ミスしたときの対応方法
8. 1.ワークシート名をセルに表示したい
9. こんなことってありませんか？ 連動させたい こんな感じで呼びたい
10. マクロで組めば瞬殺？ • 作ろうとすると、実は結構難しい
11. ポイントは2点 • 呼ばれ元のシート名が分からない • Application.Caller.Worksheet.Name (*1) で求める (ActiveSheet.Name だと、うまくいきません) • シート名を変更しても、セル側の表示が連動しない • Application.Volatile (*1) を指定する (*1) 個別の意味の詳細はぐぐって下さい
12. 完成したコード Function ThisWorksheetName() As String Application.Volatile ThisWorksheetName = Application.Caller.Worksheet.Name End Function
13. 2.条件付き書式を固定化したい
14. こんなことってありませんか？ これを固定化したい
15. 形式を選択して貼り付け、ではうまくいかない。 書式だけのコピーはできるけど、条 件付き書式の固定化はできない つまり 「数式」に対する「値」 のようなオプションはない
16. マクロで… • 無理（汗 難易度が高すぎる。 • 条件付き書式の適用結果示すプロパティが存在しない。 • たとえば、Range(“～”).Interior.Color をみても、条件付き書式の適用前の色しか取得できない。 • 無理にマクロで頑張る場合、条件付き書式を解析する必要がある。  Range(“～”).FormatConditions(n) を一つ一つみて、条件が適合しているかを判断する必要がある。
17. そこで Word ! ① Excel でコピー ② Word に貼り付け ③ Word でコピーしなおし ④ Excel に貼り直し
18. すると文字を消してもこの通り！
19. 教訓 • Excel だけに頼るな！
20. ただ、データバーとかアイコンセットとかはダメです(汗 こういうのはダメ
21. Word を買ってない人はどうすればいいのか？ • 同じことは VBA とPowershell (*1) を使って、できないこともない (汗 Sub PasteDisplayInterior() Set wsh = CreateObject("WScript.Shell") cmd = "Powershell -sta -command " _ & """" _ & " Add-Type -an System.Windows.Forms; " _ & " $a = [System.Windows.Forms.Clipboard]::GetData('HTML Format'); " _ & " [System.Windows.Forms.Clipboard]::Clear(); " _ & " [System.Windows.Forms.Clipboard]::SetData('HTML Format', $a); " _ & """" wsh.Run cmd, 0, True ActiveSheet.Paste End Sub 要するに HTML (*1) Powershell の使用は個人的な好み。
22. 3.結合したセルの高さを自動調整したい
23. 単一のセルの場合 ここをダブルクリックすると 高さを自動調整してくれる
24. 結合したセルの場合 ここをダブルクリックすると 結合したセル 逆に高さを低くされた!
25. なんで？ • 複合セルのデータは自動調整の対象から外されるから。 • 対象の行／列の、単一のセルの調整結果が設定される。 • 対象の行／列に結合セルと空セルしかない場合は、デフォルトの幅、または高さが設定される。 • なぜ外されるかと言えば、自動調整できないから。 • たとえば、以下のようなセル結合の場合、自動調整でどこのセルの高さや幅を調整すればよいか判 断できますか？
26. でもでも • 確かにセル結合していますけど、今回自動調整したいのは高さ側で、行方向にはセル 結合していないじゃないですか • だったら自動調整してくれても良いのでは？ • でもやってくれない (´･ω･`) • 「折り返して全体を表示する」にしなければ、フォントの高さには合わせてくれるようではある… 行方向は１セル 列方向は複数セルだけど
27. なんとかならないか？ • VBAで書いたらなんとかなった！ 対象のセルを選択して CTRL + SHIFT + A を押す！ ショートカットキーを 割り当てる
28. その VBA のコード (1) Sub AutoFitHeight() ' 対象のセルを決定 Dim targetCell As Range Set targetCell = ActiveCell ' 「折り返して全体を表示」を設定 targetCell.WrapText = True
29. VBA のコード (2) ' 枠の幅 Dim borderWidth As Variant borderWidth = 0.63 ' 連結したセルの幅を計算 Dim totalWidth As Variant Dim x As Range totalWidth = -borderWidth For Each x In targetCell.MergeArea.Columns totalWidth = totalWidth + x.ColumnWidth + borderWidth Next この値は当て推量かつ固定（汗
30. VBA のコード (3) ' 作業用のWorkBookを作成 Dim work As Worksheet Set work = ThisWorkbook.Sheets.Add ' 作業用のシートに、対象のセルをコピー targetCell.Copy work.Paste ' 貼り付けたセルのセル結合を解く Selection.UnMerge
31. VBA のコード (4) ' 計測した対象セルの幅を、貼り付け先のセルの幅に設定し、自動調整を実行 ActiveCell.ColumnWidth = totalWidth ActiveCell.EntireRow.AutoFit ' 自動調整で得たセルの高さを、対象のセルに設定 targetCell.RowHeight = ActiveCell.Height ' 作業シートの削除 Application.DisplayAlerts = False work.Delete Application.DisplayAlerts = True End Sub
32. なにをやっているのか？ • 結合セルでは自動調整できないので、単一セルに内容をコピーして自動調整させる。 • そのために、作業用のワークシートを作成し、そこに対象のセルをコピーする。 （元シートで操作せず他のシートにコピーするのは、元シートへの影響を極力排除したいためです） • おおよそ以下の手順で作業。 1. 対象の結合セルの幅を計測する。 2. 作業用のワークシートを追加。 3. 対象の結合セルを、作業用のワークシートにコピーする。 4. コピー先のセルの結合を解除し、単一セルにする。 5. 上記 1. で計測したセル幅を、コピー先のセルに適用し、高さの自動調整をする。 6. 自動調整で得た高さを、対象のセルの高さとして設定する。 7. 最後に作業用のワークシートを削除する。
33. 結論 • めんどうくさい。 • すなおに手動で調整しよう（汗
34. 4.CTRL+選択で選択ミスしたときの対応方法
35. 黄色の箇所だけ選択したい
36. 失敗した (´･ω･`) あと一つのところで、 違うセルを選択（汗
37. 複数選択時の「一つだけ解除」はいまだに実装されてない • 早く実装して（汗 #きっと無理
38. 次善策？ • マクロ記録を使う
39. (1) 記録開始 ALT+F11で VBEを起動 記録開始
40. (2) 選択していくと記録されていく。
41. (3) 失敗したら、マクロを停止して編集しましょう 消す 停止して 消す
42. (4) 編集したマクロを実行! すると選択されてない状態に戻る マクロを実行
43. (5) 再びマクロの記録開始から続行! 記録開始 選択 マクロで選択済みのものも 含めて記録される
44. この方法の欠点 • まあ失敗するときは、こんな準備なんてしていないわけで（汗
45. ご清聴ありがとうございました。