palm84.com

革命的愛熊主義者同盟！

トップ > Excel > 私的 Excelめも

私的 Excelめも

Excel ぷらら (旧@Palm84 )から引っ越し
2021.4.1 移転しました...
  • このページの旧URLは
    • http://www10.plala.or.jp/palm84/excel_memo.html
内容古いです！

最終更新日は 2007.5.9、ご注意。

まだまだまだなレベルですが、ぼちぼち更新して行きます...。

INDEX

参考になるサイト

インストラクターのネタ帳

説明がわかりやすいです。めちゃ便利なtipsがいっぱい。

Microsoft Office Online ホーム ページ
Microsoft Users

ダウンロード

FAQ

  • セル内改行 - Alt + Enter
  • セル内改行を検索/置換 - Ctrl + J
  • 曜日を表示
    • 月日と同じセルに表示 - ユーザー定義で m"月"d"日"(aaa) など
    • 別セルに表示 - =TEXT(A1,"(aaa)") など
マクロの削除 - モジュールの開放

マクロの選択画面から削除しただけでは、モジュールは削除されていません。Visual Basic Editor から解放が必要です。

日経パソコン オンライントップ

小技集

オートフィル

フィルハンドルでコピーが簡単に

重複行を削除

実際には削除ではなく、元データそのままで別シートへ重複分を除き貼り付け

  1. フィルタオプションの設定
  2. リスト範囲に対象セル（列）を指定
  3. 「重複するレコードは無視する」にチェック
  4. 表示行を別シートへコピー
行に連番挿入
  • 2行目から連番挿入
    • =ROW()-1
  • 2行目から「1～7」を繰り返し挿入
    • =MOD(ROW()-2,7)+1
  • 1の行に条件付書式を設定
    • 数式が=MOD(ROW()-1,10)=1でパターン指定
    • 数式が=MOD(ROW()-1,7)=1でパターン指定
セルの値を分割
  1. 分割したい位置にスペースや記号などを入力
  2. データ→「区切り位置」
  3. 「カンマやタブ...」→区切り文字指定
ふりがなを再設定
  • 選択領域にふりがなを再設定するマクロ
Sub ふりがな()
Selection.SetPhonetic
End Sub
テーブルに名前を設定
  1. 範囲選択
  2. 名前ボックスに名前を入力してEnter
配列数式

テンプレート

テンプレートの保存
  • 拡張子を xlt に変更する
  • or ファイルの種類を「テンプレート」を指定して保存する
マクロの挿入（インポート）

まず、Alt-F11 で Visual Basic Editor を起動。表示→プロジェクト エクスプローラ（画面左、通常は開いてるかな）で挿入するファイルを選択

  1. マクロを記述したファイルを拡張子 .bas にして保存（※.txtでもインポート可能ですけど）
  2. ファイル→インポート でファイルを指定

または、挿入→標準モジュール で内容を貼り付け（書き込み）

JANコードの指数表示を文字列に

短縮JANは"00000"付加

  1. A列にデータ貼り付け
  2. B1 - =IF(A1="","",IF(LEN(A1)=8,"00000"&A1,TEXT(A1,0)))
  3. B1を必要な分だけコピー（または、オートフィルを実行）
  4. B列をコピー、C列へ値の貼り付け
  5. 貼り付けたセルの書式を「文字列」に設定
マクロ

A列にデータを貼り付けてから実行。やりなおし可能。

Sub 指数表示戻し()
   Columns("B:C").Select
   Selection.Clear
   Range("B1").Value = "=IF(A1="""","""",IF(LEN(A1)=8,""00000""&A1,TEXT(A1,0)))"
   Range("B1").Copy
   Range("A65536").Select                       'A列の最下セルへ移動
   Selection.End(xlUp).Select  'Ctrl + ↑
   ActiveCell.Offset(0, 1).Select               '右のセルへ移動
   Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑
   ActiveSheet.Paste                            '数式貼り付け
   Application.CutCopyMode = False
   Selection.Copy
   Range("C1").Select
   Selection.PasteSpecial Paste:=xlValues       'C1へ値の貼り付け
   Selection.NumberFormatLocal = "@"            '書式を文字列に
   Range("A:C").Columns.AutoFit                 '列幅最適化
End Sub

チェックディジット

  • A列にデータ貼り付け
  • B列桁数チェック・短縮JANは「00000」付加
    • =IF(A1="","",IF(LEN(A1)=8,"00000"&A1,IF(LEN(A1)=13,TEXT(A1,0),IF(LEN(A1)>13,"桁多い！",IF(LEN(A1)<13,"桁少ない！","エラー")))))
  • C列に判定結果
    • =IF(A1="","",IF(ISERROR(IF(P1=S1,"OK","ちゃいまんがな！")),"エラー",IF(P1=S1,"OK","ちゃいまんがな！")))
  • D列 - P列に桁毎に抜き出し
=IF($A1="","",VALUE(MID($B1,COLUMN()-3,1)))
  • Q列
=IF(A1="","",D1+F1+H1+J1+L1+N1+3*(E1+G1+I1+K1+M1+O1))
  • R列
=IF(A1="","",10-(MOD(Q1,10)))
  • S列
=IF(A1="","",+IF(R1>9,0,R1))

B1:S1 を選択してフィルハンドルをWクリック。

マクロ

白紙シートのA列にデータを貼り付けてから実行

Sub チェックディジット()
    Columns("B:S").Clear
    Range("B1").Formula = "=IF(A1="""","""",IF(LEN(A1)=8,""00000""&A1,IF(LEN(A1)=13,TEXT(A1,0),IF(LEN(A1)>13,""桁多い！"",IF(LEN(A1)<13,""桁少ない！"",""エラー"")))))"  'B1 に数式入力
    Range("C1").Formula = "=IF(A1="""","""",IF(ISERROR(IF(P1=S1,""OK"",""ちゃいまんがな！"")),""エラー"",IF(P1=S1,""OK"",""ちゃいまんがな！"")))"  'C1 に数式入力
    Range("B1").Select  'B1 に条件付書式
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""桁少ない！"""
    With Selection.FormatConditions(1).Font
        .Bold = True
        .ColorIndex = 3
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""桁多い！"""
    With Selection.FormatConditions(2).Font
        .Bold = True
        .ColorIndex = 3
    End With
    Range("C1").Select  'C1 に条件付書式
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""エラー"""
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""ちゃいまんがな！"""
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    Range("D1").Formula = "=IF($A1="""","""",VALUE(MID($B1,COLUMN()-3,1)))"  'D列 - P列に桁毎に抜き出し
    Range("D1").Copy Destination:=Range("E1:P1")
    Application.CutCopyMode = False
    Range("Q1").Formula = "=IF(A1="""","""",D1+F1+H1+J1+L1+N1+3*(E1+G1+I1+K1+M1+O1))"  'Q列
    Range("R1").Formula = "=IF(A1="""","""",10-(MOD(Q1,10)))"  'R列
    With Range("S1")  'S列
        .Value = "=IF(A1="""","""",+IF(R1>9,0,R1))"
        .Interior.ColorIndex = 6
    End With
    Columns("A:C").ColumnWidth = 15  '列幅
    Columns("D:P").ColumnWidth = 2
    Columns("Q:S").ColumnWidth = 3
   Range("B1:S1").Copy  'B1 - S1 をコピー
   Range("A65536").Select  'A列の最下セルへ移動
   Selection.End(xlUp).Select  'Ctrl + ↑
   ActiveCell.Offset(0, 1).Select  '右のセルへ移動
   Range(Selection, Selection.End(xlUp)).Select  'Ctrl+Shift+↑
   ActiveSheet.Paste  '数式貼り付け
   Application.CutCopyMode = False
   Range("A1").Select
End Sub

ふりがな編集

この下のはだめだめなので、もうちっとましに使えそうなの作ってみました。

  • ふりがな編集用マクロ（軽い方 - 追加するとエラー出るかも）
  • ふりがな編集用マクロ（ちょっと重い）
    • ふりがなが付いていないデータにマクロでふりがなを付けることができます（但し、正確とは限らない）
    • 修正したふりがなをデータに再設定できます
    • ボタンでカナの切替ができます

こんなかんじです。

f:id:palm84:20210118005010p:plain

マクロの挿入
  1. 上記ファイルをダウンロード
  2. Alt-F11 で Microsoft Visual Basic を開く
  3. Ctrl-R（または表示メニューから）でプロジェクト エクスプローラを開く
  4. 左ツリーで現在編集中のファイルを選択
  5. 右クリックから「ファイルのインポート」
  6. ダウンロードしたファイルを指定
  7. 標準モジュールの Modulexに追加される
マクロの実行
  1. Excelに戻る
  2. Alt-F8 でマクロ選択画面を出す
  3. 「FORMAT」を実行
数式入力 ＆ ふりがな再設定マクロ

あんまり使えなさそうです。すみません。正確に出ないことも多いので修正は必要。

  • A列にデータ
  • B列に計算式
  • C列に結果

データをA列に貼り付けてから実行

Sub ふりがな設定()
    Range("A65536").Select
    Selection.End(xlUp).Select   'Ctrl + ↑
    Range(Selection, Selection.End(xlUp)).Select   'Ctrl+Shift+↑
    Selection.SetPhonetic   '選択範囲にふりがな再設定
      With Selection.Phonetics
        .CharacterType = xlKatakanaHalf
        .Alignment = xlPhoneticAlignNoControl
        .Font.Name = "ＭＳ ゴシック"
        .Font.Size = 9
        .Visible = True
      End With
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(ASC(PHONETIC(RC[-1])))"
    Selection.Copy
    Range("A65536").Select
    Selection.End(xlUp).Select   'Ctrl + ↑
    ActiveCell.Offset(0, 1).Select   '右のセルへ移動
    Range(Selection, Selection.End(xlUp)).Select   'Ctrl+Shift+↑
    ActiveSheet.Paste   '数式貼り付け
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlValues   'C列へ値の貼り付け
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.WrapText = True          '折り返し表示
    Columns("A:C").ColumnWidth = 35    '列の幅を設定
    Range("A65536").End(xlUp).Select   'Ctrl + ↑
    Range(Selection, Selection.End(xlUp)).Select   'Ctrl+Shift+↑
    Selection.Rows.AutoFit     '行の高さを調整
End Sub
  • やりなおしマクロ（オールクリア）
Sub やりなおし()
    Columns("A:C").Select
    Selection.Phonetics.Visible = False
    Selection.Clear
    Range("A1").Select
End Sub
  • 半角カタカナ - .CharacterType = xlKatakanaHalf
  • 全角カタカナ - .CharacterType = xlKatakana
  • ひらがな - .CharacterType = xlHiragana

関数

  • ふりがな表示 - PHONETIC
  • 半角を全角に - JIS
  • 全角を半角に - ASC
  • 文字数 - LEN
  • 文字数（バイト数） - LENB
  • 空白の除去 - TRIM
  • 置換 - SUBSTITUTE
  • 参照先のセルが削除されてもエラーにならない - =INDIRECT("Sheet2!A1")
    • 「リンク貼り付け」の場合は参照先のセルを削除するとエラーになる
  • コード番号（10進数）を文字に - CHAR
  • 文字を10進数に - CODE
  • 2進数を10進数に - BIN2DEC
  • 2進数を16進数に - BIN2HEX
  • 単価*数量の合計を一発で計算 - SUMPRODUCT
SUBTOTAL

etc

ほなね