エクセルをスマートに使おう

#excel#エクセル#ショートカット#VBA

 

 

■ショートカット

 shift + space ... 行の選択
 ctrl + space ... 列の選択
 ctrl + -(マイナス)... 行の削除
 ctrl + +(プラス)... 行の挿入
 ctrl + ;(セミコロン)... 日付の入力
 ctrl + :(コロン)... 時刻の入力 
 ctrl + d/ctrl + r ... フィル下/フィル右 
  • エクセル特有のショートカット
  • 上のセルの値をコピーするショートカット
  • オートフィルで1000までの連番を作成
  • オートフィルで下方向へコピー
 ctrl + `(アクサン グラーブ)... セルの値と数式の表示の切り替え
★ctrl + t 又は ctrl + l(小文字エル) ... テーブルの挿入
 ctrl + k ... ハイパーリンクを追加
★ctrl + L(大文字エル)... フィルターON/OFF
 ctrl + Enter ... 一括入力・他
  • 複数セルを選択して、複数セルの一括入力。
  • アクティブセルが移動しない。
  • Ctrl+Enterは数式のみコピーする。フィル(Ctrl+D)は書式もコピーする。
 alt + = ... SUM関数の挿入
★alt + ;(セミコロン)... 可視セルのみ選択
  • Excelのメニューやコマンドで非表示があるか調べる方法はない。
  • 「Alt+;(セミコロン)」、又は、「Ctrl+g」でジャンプ画面を選択し「可視セル」を選択する。この時、仮に非表示の行や列があれば「白い線」で表示される。
 shift + F3 ... 関数の挿入
 shift + F8 ... 選択範囲を追加する
★shift + F10 ... ショートカット メニューを表示する
 F10 ... メニューバーをアクティブにする
★ctrl + alt + v ... 「形式を選択して貼り付け」ダイアログの表示
 alt + w + f ... ウィンドウ枠の固定/解除
 ctrl + y ... 直前の操作を繰り返す
★ctrl + { ... 選択範囲の数式が直接又は間接的に参照する全てのセルを選択
  • ジャンプ画面の「参照元」選択時と同じ動作
★ctrl + } ... 選択範囲のセルを直接又は間接的に参照する数式が入っている全てのセルを選択
  • ジャンプ画面の「参照先」選択時と同じ動作

 

 

 関連HP 

 

VBA

 
VBA基本(VBAって何?[=「マクロ」?])

 

■関数一覧

 COUNT関数 ... 数値/日付/時刻が入っているセルを数える
 COUNTA関数 ... 値が入っているセルを数える
 COUNTIF関数 ... 特定の文字が含まれているか?
  • 特定の文字が含まれているか? ...「=IF(COUNTIF(B3,"*県*")=1,"○","✕")」
 MOD(数値、除数)... 余りを求める
 関連HP

 

 

 

■TIPS

 フィルターを使いこなそう!

officetanaka.net

  • 何も考えずにフィルタした場合は、「その文字を含む」データを抽出する。しかし、実はワイルドカードが使える
  • 「田中」... "田中"を含むデータを抽出(※1)
  • 「*田中*」... "田中"を含むデータを抽出(※1と同じ結果になる)
  • 「田中*」... "田中"で始まるデータを抽出
  • 「*田中」... "田中"で終わるデータを抽出
  • 「?田?」... "3文字の名前で、2文字目が田"のデータを抽出
  • 「*」や「?」のエスケープは「~(チルダ)」を使う

----------

 

youtu.be

(書式)

  • セル範囲.AutoFilter  Filter  := 何列目, Criteria1:="抽出文字列"

(使用例)

  • Range("B2").AutoFilter Field:=2, Criterila1:="田中" ...セル内が"田中"を抽出
  • Range("B2").AutoFilter Field:=2, Criterila1:="田中*" ...セル内が"田中xxx"を抽出
  • Range("B2").AutoFilter Field:=2, Criterila1:="*田中" ...セル内が"xxx田中"を抽出
  • Range("B2").AutoFilter Field:=2, Criterila1:="*田中*" ...セル内が"x田中x"を抽出
  • Range("B2").AutoFilter 2, "田中" ...短縮した書き方

(複数条件)

 Sub 抽出1()
  Range("B4").AutoFilter _
  2, _
  "*" & Range("A1") & "*", _
  xlAnd, _
  "*" & Range("A2") & "*"
 End Sub 

 ----------

 

www.yosato.net

www.atmarkit.co.jp

 

 フィルタ抽出時のコピペ
  • フィルタされた状態で表の連続セルをコピーした時は、表示されていない行のデータはコピーされないので全く問題無し
  • フィルタされた状態で「ctrl + enter」で連続セルにデータを一括入力した時は、表示されていない行にはデータが入力されないので全く問題無し
  • セル1個を「ctrl + c」でコピーしたものを、フィルタされた状態の連続セルに「ctrl + v」(書式付き)でペーストした時は、表示されていない行にデータはペーストされないので問題無し
  • セル1個を「ctrl + c」でコピーしたものを、フィルタされた状態の連続セルに「ctrl + v」(値のみ)でペーストした時は、表示されていない行にデータがペーストされるので問題有り。しかし、ペーストする前に「可視セルのみ選択」しておけば表示されていない行にはデータがコピーされないので全く問題無し
  • 連続したセルを「ctrl + c」でコピーしたものを、フィルタされた状態の連続セルに「ctrl + v」(書式付き)でペーストした時は、表示されていない行にデータがペーストされるので問題有り
  • 連続したセルを「ctrl + c」でコピーしたものを、フィルタされた状態の連続セルに「ctrl + v」(値のみ)でペーストした時は、表示されていない行にデータがペーストされるので問題有り
  •  

 

 全ての図形を選択するには?
  • (方法1)マウスで図形一つを選択後に「Ctrl+a」で選択できる
  • (方法2)ジャンプ画面のセル選択でオブジェクトを選択する
 ジャンプ画面の活用(条件付き書式/データの入力規則/アクティブ列との相違)
  • 条件付き書式の設定違いは、一見では見つける事ができない
  • データ入力規則の設定違いは、一見では見つける事ができない
  • 計算式の設定違いは、一見では見つける事ができない

 

 計算式中の参照元を変更できる「カラーリファレンス
 選択している“セル”や“行・列”を見やすくハイライト(強調)
  • シート内の全てのセルに対して条件付き書式を設定する。「=CELL("row")=ROW()」(選択した行) 
  • ThisWorkbookにコードを追加する Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)  Application.ScreenUpdating = True End Sub

 

 

 


 

 セルの表示形式「ユーザ定義」
  • 「@」... セルの値を文字列としてそのまま表示する
  • 文字列を付加するには「二重引用符(”)」で囲み「"文字列"」とする。連結演算子「&」は不要
  • 円記号(¥)、プラス(+)、マイナス(-)、カッコなどの記号は「二重引用符(”)」で囲まなくてもよい。囲まなくてもよい記号は次のとおり。

    [ \ ]...円記号

    [ $ ]...ドル記号

    [ + ]...正符号

    [ ( ]...左かっこ

    [ : ]...コロン

    [ ^ ]...キャレット

    [ ' ]...アポストロフィ

    [ { ]...左中かっこ

    [ < ]...小なり記号

    [ = ]...等号

    [ - ]...負符号

    [ / ]...スラッシュ記号

    [ ) ]...右かっこ

    [ & ]...アンパサンド

    [ ~ ]...チルダ

    [ } ]...右中かっこ

    [ > ]...大なり記号

    [   ]...スペース文字

 

  • 「@」をそのまま表示するには、「"@"」または「!@」とする。びっくりマーク(!)をそのまま表示するときは「!!」とする。(「!」は書式記号文字をエスケープする
  • アスタリスクと半角スペースの組み合わせ(* )でスペースの連続を表すので、左端にあれば右揃え、右端にあれば左揃え、途中にあれば左右に分けた両端揃えを表す
  • 日付の書式

    yyyy/mm/dd...2021/03/01
    yyyy"年"mm"月"dd"日"(aaa)...2021/03/01(月)
    yyyy"年"mm"月"dd"日"(aaaa)...2021/03/01(月曜日)

 

■参考HP

 

www.youtube.com

 

excel-ubara.com

 

officetanaka.net