draemonash's wiki
draemonash's wiki

Excel


設定

  • IME ON 時に行選択ショートカット (Shift+Space) を効くようにする ＠Windows7
    • IME の「設定」→「全般」タブで "Microsoft Office IME 2010" を選択し、「プロパティ」を押下
    • 「全般」タブの「キー設定」の「変更」を押下
    • 「キー設定」タブの "Shift+SPACE" の "入力/変換済み文字なし" を 「-」に変更
  • 列非表示のショートカットが効かない！
    • Win-R ⇒ 「intl.cpl」を実行
    • 「キーボードと言語」→「キーボードの変更」
    • 「詳細なキー設定」→「キーシーケンスの変更」
    • 「キーボードレイアウトの切り替え」を "割り当てなし" に変更
  • ヘルプがうまく検索されない！ @Excel2010
    • 原因:
      • F1 を押して検索されない場合、デフォルトでオンラインのヘルプを参照しに行こうとしている。
    • 解決策：
      • F1を押してヘルプを表示
      • 右下の「Office.comに接続」をクリック
      • 「このコンピュータ上のコンテンツのみを表示」にチェック
  • F1を押してもヘルプが表示されないようにする
    • レジストリ変更
      • レジストリエディタ(regedit) にて以下を作成する。(もとに戻す場合は以下を削除)
        • path：HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\＜バージョン＞\Excel\DisabledShortcutKeysCheckBoxes
        • Name：F1Key (任意)
        • Value：112,0
    • アドインを使用
      • 「F1のヘルプを無効に」にてインターネット検索し、インストールする。
      • アドイン内で上記のレジストリ設定をしていると思われるが、設定が楽なので一番おすすめ！
    • マクロ使用
      • 「マクロの保存先(I)」を「個人用マクロブック」として、「新しいマクロの記録(R)」を開始
      • ストップボタンを押して、「記録終了（R)」
      • 作成された PERSONAL.XLS に以下を記入。(Macro1() を auto_open() に変更)
      • この方法だと、他プロセスで Excel を起動した際、新プロセス側では F1 ヘルプが効いてしまう！
Sub auto_open()
    ' ヘルプキー無効化
    Application.OnKey "{F1}", ""
End Sub
  • Excel の保存先デフォルト
    • ファイル->オプション->保存->既定のファイルの場所 を変更
  • リボンに設定
    • 枠なし
    • 格子
    • 外枠太罫線
      • <区切り>
    • 中央揃え
      • <区切り>
    • セルを結合して中央揃え
    • 横方向に結合
    • 選択範囲内で中央（マクロ：CenterAcrossForSelectionCell）
      • <区切り>
    • 分割
    • シート名の変更
      • <区切り>
    • 連続データ
      • <区切り>
    • 塗りつぶしの色（2003）
    • フォントの色（2003）
    • 線の色（2003）
      • <区切り>
    • 塗りつぶしの色（2010）
    • フォントの色（2010）
    • 線の色（2010）
      • <区切り>
    • 枠線の表示
    • 条件付き書式
    • 区切り位置
    • 名前の管理
    • オブジェクトの選択

アドイン登録方法

  • .xlaを以下に登録
    • C:\Users\[ユーザ名]\AppData\Roaming\Microsoft\AddIns
  • Excel を起動、「開発」タブ⇒「アドイン」にて「有効なアドイン」に登録

アドイン集

  • OfficeTab
  • Vimxls
  • Excel2003ColorPalletKai
  • F1のヘルプを無効に

Tips

  • テキストボックスにセルの内容を表示する
    • テキストボックスを選択し、"数式バーに"表示したいセル番号を記述する ex) =A1
  • 離れたセルを複数選択
    • Shift - 矢印キーにて複数選択 ⇒ Shift - F8 押下後 ⇒ 再度複数選択
  • 条件付き書式のコピーの挙動
    • 図１：書式コピー前
    • 図２：書式コピー後
    • 2行目：B2 標準コピー⇒ C2 書式ペースト のパターン
    • 3行目：B3 書式コピー⇒ C3 書式ペースト のパターン
      • 【考察１】「書式コピー」も「標準コピー」も貼り付け後の結果は変わらない。
      • 【考察２】書式ペーストを行うと、条件付き書式ルールが複製されてしまう。
  • 空白以外の最端セル取り出し
    • 【最左上セル取り出し（数値）】IF( COUNT( B2:H2 ) = 0, "", INDEX( B2:H2, MATCH( 0, INDEX( 0/B2:H2, ), ) ) )
    • 【最右下セル取り出し（数値）】IF( COUNT( B2:H2 ) = 0, "", LOOKUP( 10^10, B2:H2 ) )
    • 【最左上セル取り出し（文字列＆数値）】IF( COUNTIF( B2:H2, "<>" ) = 0, "", INDEX( B2:H2, MATCH( 1, INDEX( 1/( B2:H2 <> 0 ), ), 0 ) ) )
    • 【最右下セル取り出し（文字列＆数値）】IF( COUNTIF( B2:H2, "<>" ) = 0, "", LOOKUP( 1, 0/( B2:H2 <> "" ), B2:H2 ) )
      • ただし、最左上セル取り出しは"0"を検索対象として見なさない
  • 空白以外の最端セル取り出し（ExcelVBA）
    • 【最右下セル取り出し】Evaluate("INDEX(セル範囲,MATCH(""*"",セル範囲,0))")
    • 【最左上セル取り出し】Evaluate("INDEX(セル範囲,MATCH(""*"",セル範囲,-1))")
  • 条件検索関連
    • 複数条件 加算
      • =SUMPRODUCT( ( ( $B:$B="みかん" ) * ( $C:$C="M" ) ), $F:$F )
    • 複数条件 検索（SUMPRODUCT）
      • =INDEX( $F:$F, SUMPRODUCT( ( $B:$B="みかん" ) * ( $C:$C="M" ) * ROW( $A:$A ) ) )
        • なぜかうまく検索できないことがあるため、使用しないこと。。
    • 複数条件 検索（VLOOKUP）
      • =VLOOKUP( "みかん"&"M", $E:$F, COLUMNS( $E:$F ) )
    • 条件付き 最大値
      • =SUMPRODUCT( MAX( ( $B$3:$B$9="みかん" ) * ( $C$3:$C$9="M" ) * ( $F$3:$F$9 ) ) )
        • 注意１：列選択はできない。
        • 注意２：最小値の算出はできない。（条件に合わないものは「0」となり、他の数値よりもっとも小さくなるので）
  • 重複データの連番
    • countif 関数の範囲にて、末尾行のみ相対参照にする
      • =COUNTIF( $B$1:$B3, $B3 )
  • ビットシフト
    • 1 * 2^8 ⇒ 1 を左 8 ビットシフトさせる
    • （階乗を利用する。ex. a^b ⇒ a の b 乗）
  • 行・列の上限を縮める
    • 行の上限を 65535 から 50 行まで縮めたい場合、51 行目から 65535 行目まで選択し非表示とすることで、行上限が縮まったように見える。
  • 非表示行・列を探索する
    • 全行の特定の列(※)に値を格納する。
    • 全行に「=AGGREGATE(3,5,<※セル指定>)」を指定する。
    • 非表示行が 0、表示行が 1 となる。
  • 「名前の定義」で同じシートのセルに対して参照する方法
    • !$C$100
  • コピー＆行挿入ペースト時に条件付き書式のゴミが作成される対策
    • 行挿入後、「コピー元の行を含めて」行ペーストする。
    • 列も同様。
  • 末尾の単語を取得する
    • CLEAN(RIGHT(SUBSTITUTE(A1,"\",REPT(CHAR(9),200)),200))
    • 解説）デリミタ"\"を「200文字の制御文字CHAR(9)」で置換し、文字列の末尾200文字を取得する。
  • 分数の入力方法
    • セル入力時 "0 1/2"と入力する
  • 複数列を一列に展開する方法
    • Excel の範囲をコピーしテキストエディタに貼り付け後、タブを改行に変換する。
  • セル内の改行を別の文字列へ変換
    • 置換ウィンドウの「検索する文字列」に "Ctrl+I（大文字アイ）"を入力して置換。
  • タイミングチャート用条件付き書式（添付の「タイミングチャート例.xls」参照）
    • 書式条件１
      • 条件式：=AND( E4<>"", F4<>"", OR( AND( E4<F4, $D4>=E4, $D4<F4 ), AND( E4>F4, $D4<E4, $D4>=F4 ) ) )
      • 適用範囲：=$E$4:$O$14
      • 書式：右縦 罫線
    • 書式条件２
      • 条件式：=AND( E4<>"", $D4=E4)
      • 適用範囲：=$E$4:$O$14
      • 書式：下 罫線
  • 非表示セルへの貼り付け動作について
    • 実験目的
      • セルの貼り付け時、非表示となっているセル（フィルタ時、行/列非表示時）に対して貼り付けされてしまう場合がある。
      • その動作を実験し、考察する。（図は例図、表は実行結果）
    • 結論
      • 単一セル貼り付け以外は非表示セルに貼り付けされてしまうため、フィルタ後の貼り付けは「単一セル貼り付けのみ」を使用すること。
パターン コピー元 ペースト先 可視セルのみ貼り付け
単一セル D2 E4:E6 ○
複数列選択時 D2:E2 D4:E6 ×
複数行選択時 B4:B6 E4:E6 ×
単一セル（形式選択貼付） D2 E4:E6 ×
  • 大量データでの高速VLOOKUP
=IF(
    INDEX(
        Sheet1!$A$1:$B$200000,
        MATCH(
            $A1,
            Sheet1!$A$1:$B$200000,
            1
        ),
        1
    )=$A1,
    VLOOKUP(
        $A1,
        Sheet1!$A$1:$B$200000,
        2,
        TRUE
    ),
    NA()
)
  • Excel 2003 カラーパレット
  • セル内の改行について
    • セル内の改行コードは LF (CHAR(10))である。
    • そのため、VBA から vbNewLine（CRLF） 指定で改行すると、見えない改行（CR）が挿入される
    • CR が挿入された場合、 =SUBSTITUTE(A1,CHAR(13),"") で置換すれば見えない改行は消える。
  • 表示形式
値 =TEXT($B4,0x00000000) =TEXT($B4,0x########) =TEXT($B4,0.000) =TEXT($B4,000,000)
1 0x00000001 0x1 1.000 000,001
23 0x00000023 0x23 23.000 000,023
2230 0x00002230 0x2230 2230.000 002,230
012 0x00000012 0x12 12.000 000,012
2222222 0x02222222 0x2222222 2222222.000 2,222,222
222222222 2x22222222 2x22222222 222222222.000 222,222,222
  • R1C1 アクセス方法
    • B2 ⇔ INDIRECT( "R2C2" , FALSE )
    • INDIRECT( "R" & ROW() & "C" & COLUMN() , FALSE )
  • countif 使用例
    • =COUNTIF($B3,"*世田谷区*")
    • =COUNTIF($A$1,"<>検索文字列")
    • =COUNTIF($A$1,">=4")（>,>=,<,<=）
  • ファイルパスからファイル名を取得
    • =splitstr(B2,"\",getstrnum(B2,"\"))
      • ただし、UserDefFunc.xla をインストールしておく必要あり！
  • 行数が違う複数シートに共通の言葉を追加する方法（ 置換例 参照）
    • 複数シートを選択
    • E 列に数式を挿入
      • E3 = IF( B3<>"", D3&"[-]", "★★★" )
    • E 列をコピーして、D 列に値コピー
    • 「★★★」を「」に置換（置換オプションは以下を指定）
      • 「セル内容が完全に同一であるものを検索する」
      • 検索場所「シート」
  • vlookup＋match 関数活用法
    • vlookup 参照元と参照先の列順が異なっている場合でも、値が取得できる。
      • Sheet2!C3 = VLOOKUP( $B3, Sheet1!$B:$F, MATCH( C$2, Sheet1!$B$2:$F$2, 0), FALSE )
  • 「既定の図形に設定」について
    • 既定の図形に設定すると、設定したブック内で有効になる
    • Excel を終了しても、保存される
    • 別ブックでは適用されない
    • 類似の図形も適用される。
    • テキストの配置（左詰めや上詰めなど）は保存されない
  • 数式のデバッグ方法
    • セル選択中に「F2」を押してテキスト編集モードにする。
    • デバッグしたい箇所を選択して「F9」を押すと計算結果に展開される。
  • 条件付き書式増殖の条件
    • 添付の「条件付き書式増殖の条件.xlsm」参照
  • ハイパーリンク関数
    • シート内のセルにジャンプ
      • =HYPERLINK("#C5","C5セルにジャンプ！")
    • ハイパーリンクのジャンプ先を可変にする方法
  • 【hyperlink関数】HYPERLINK(<リンク先>,<表示文字列>)
    • 【hyperlink関数（同シート）】HYPERLINK("#A1","⇒")
    • 【hyperlink関数（別シート）】HYPERLINK("#Sheet1!A1","⇒")
    • 【hyperlink関数（別ブック絶対パス１）】HYPERLINK("[C:\test\test.xlsx]Sheet3!A1","⇒")
    • 【hyperlink関数（別ブック絶対パス２）】HYPERLINK("C:\test\test.xlsx","⇒")
    • 【hyperlink関数（別ブック相対パス１）】HYPERLINK(".\test\test.xlsx","⇒")
    • 【hyperlink関数（別ブック相対パス２）】HYPERLINK("..\test\test.xlsx","⇒")
  • Excel ２プロセス同時起動
    • コマンドプロンプトにて以下を実行
      • 例１）"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /x
      • 例２）"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /x c:\test\test.xlsx

ショートカットキー

Ctrl Shift Alt Key 機能
Ctrl Shift 9[)] 選択行を表示
Ctrl Shift 0 選択列を表示
Ctrl Shift 5[%] [パーセンテージ] 表示形式 (小数点以下の桁数 0) を設定
Ctrl Shift 3[#] [日付] 表示形式
Ctrl Shift :[*] アクティブ セル領域 (アクティブ セルを含み、空白の行と列で囲まれているデータ領域) を選択
Ctrl Shift @[`] 数式と計算結果の表示を切り替え
Ctrl Shift U 折りたたまれた数式バーを展開し、展開されていた数式バーを折りたたみ
Ctrl 1 [セルの書式設定] ダイアログ ボックスを表示
Ctrl 8 アウトライン記号の表示と非表示を切り替え
Ctrl 9 選択行を非表示
Ctrl 0 選択列を非表示
Alt F1 現在の範囲からグラフを作成
Alt PageUp(Down) 一画面分左に(右に)横スクロール
Shift Alt = オートSUM機能
Shift F3 [関数の挿入] ダイアログ ボックスを表示
Shift F11 新規シートの挿入
F4 直前の操作を繰り返す
F4 【文字入力時】相対参照から絶対参照へ(複数回押すことで、行のみ列のみ選択可能)
F5 [ジャンプ] ダイアログ ボックスを表示
F7 [スペルチェック] ダイアログ ボックスを表示して、作業中のワークシートまたは選択した範囲内のスペルをチェック
F12 名前を付けて保存
F2 ⇒ F9 計算式を見せずに数値に変える
F2 【数式入力時】編集モード⇔入力モード切替

タグ：

+ タグ編集
  • タグ：
タグの更新に失敗しました
エラーが発生しました。ページを更新してください。
ページを更新
「Excel」をウィキ内検索
最終更新：2017年11月07日 20:57