目次

Excel

Tips

シート名の使えない文字

Excel のシート名に使えない文字について再考2017

エラーを無視して合計する

エラーを無視して合計する－配列数式

Excelでカンマと円付きの金額を数値にする

=VALUE(SUBSTITUTE(A1,"円",""))

クリップボードのCSVデータを貼り付ける場合

通常だと１つのセルにカンマ区切りで入ってしまいます。
貼り付け後にExcelメニューのデータ→区切り位置でカンマを設定すると、カンマごとにセルに分けてくれます。

テキストボックスの行間を調整する

段落ダイアログボックスが表示されるので、インデントと行間隔タブにし、行間を「固定」にし、間隔の数値を変更します。
間隔を小さくすると狭く、大きくすると広くなります。

テキストボックスの行間を調整する

重複データの抽出

※範囲は、“$”記号を付けて絶対参照とする。絶対参照と相対参照 

=COUNTIF(範囲, 検索条件)

"重複"の文字に変換する場合
=IF(COUNTIF(範囲, 検索条件)>1,"重複","")

第三十九回　Excelデータの重複チェックで大惨事を防げ！

一番最後の行までコピーする方法

コピーしたい内容が書かれたセルの右下に合わせて
マウスをダブルクリックすると、その行全てに関数がコピーされます。
http://uguisu.skr.jp/Windows/excel_cell_copy.html

ミリ秒の扱い方

Excelで時間1:2:3.5を書式hh:mm:ss.000にて、01:02:03.005とするには下記のセル関数を使用する。 

=TEXT(LEFT(A2,FIND(".",A1)-1),"hh:mm:ss")&"."&TEXT(MID(A1,FIND(".",A1)+1,3),"000")

Excelでミリ秒の差分の合計や平均を出したい場合、ミリ秒のセル値に86400(24*60*60)を掛けた別セル(例 ”＝C1*86400“)を用意して、その列で計算させる。
https://blog.sgnet.co.jp/2018/11/excel.html

異常終了した際の保存先フォルダ

Excelが異常終了した際に、バックアップされている場合があるので下記フォルダで「XLxxx.xlsb」を探しましょう。
ファイルエクスプローラーの検索で検索フィルターに更新日付を本日指定して見つけてもいいです。 

C:\Users\(ユーザー名)\AppData\Roaming\Microsoft\Excel

UTF-8形式のCSV関連

Excelで開くと文字化けするUTF-8のCSVを文字コードを変換せずに開く方法

Excel2016の最新版(2016/10更新パッチ)でUTF-8形式のCSVを保存出来るようになった。
ExcelファイルをUTF-8のCSV形式で保存する方法（Excel 2016最新機能）

読み取り専用の設定方法

開発プロジェクトなど共同で作業をしている場合、仕様書やテーブルレイアウトを他の人が参照しているため、編集したい時に編集出来ないことが多々あります。
参照するだけなら読み取り専用で開いてもらえるように2つの方法を提示します。

読み取り専用の設定/解除を追加する方法

「ツール(T)」→「ユーザー設定(C)」→「ファイル」→「読み取り専用の設定/解除」をツールバーに追加を行う。

開いた時に読み取り専用で開いてくださいという旨のメッセージを出すように設定する方法

  1. 「ファイル」 - 「名前を付けて保存」→「ツール」→「全般オプション」を選択します。
  2. 「読み取り専用を推奨する」をオンにします。
  3. 次に開いた場合にメッセージが表示されるようになります。

Excelのブックを別ウィンドウで開く

Excelのブックを別ウィンドウで開く3つの方法

  1. Excelを開きます。何らかのファイルでも、新規作成でも構いません。
  2. タスクバーに表示されたExcelのアイコンを右クリックし、[Microsoft Excel]をクリックします。

見えているセルだけをコピーする

【Excel】コピペしたら非表示にしたデータまで貼り付けられた！　エクセルで見えているセルだけをコピーするテク！

図形の線をまっすぐにする

図形を選択して、リボンの描画ツールにある図形サイズで調整します。
Excel 図形の線をまっすぐにする

直線を水平または垂直に引くには [Shift]キーを押しながらドラッグして描画します。
Excelで斜めの直線をまっすぐに直したい：Excelの基本操作

画像の背景を削除・透明化したい

  1. 画像を選択した状態で、［図ツール］の［書式］タブ→［背景の削除］をクリックします。
  2. 削除される領域が紫色に変わります。
  3. 削除する領域を調整するには、［削除する領域としてマーク］をクリックします。
  4. 削除する領域と保持する領域の調整が完了したら、［変更を保持］をクリックします。

【Excel】シートに挿入した画像の背景を削除・透明化したい！エクセルで画像入り案内状や操作手順書の作成を効率化するテク

文字列扱いになっている数字を数値へ一括で変換

演算方式

  1. 任意のセルに「1」を入力
  2. 1.をコピー
  3. 文字列扱いになっているセル範囲を選択
  4. 「ホーム」タブをクリック
  5. 「貼り付け」をクリック
  6. 「形式を選択して貼り付け」をクリック
  7. 「値」を選択
  8. 「乗算」を選択
  9. 「OK」をクリック

文字列扱いになっている数字を数値へ一括で変換する方法

「区切り位置」機能方式　

  1. 対象の列の書式変更まで実施した後、対象の列を全選択
  2. 画面上部のメニュー内の「データ」→「区切り位置」を選択
  3. 「区切り位置指定ウィザード」が表示されますが、何も選択や設定を行わずに「完了」ボタンで完了

「区切り位置」機能で一括での書式設定の変更を適用

表示レイアウト崩れ

Windows 10の表示スケールが125%で作成した場合、他の方の表示スケールが100%のディスプレイで見ると表示レイアウトが崩れて表示される。
マルチモニターを使用しているならディスプレイ毎に表示スケールが設定できるので、見比べて調整するしかない。

[Excel] 拡大縮小とレイアウトの設定が印刷に影響していた件

図の矢印をコピーして貼り付けると行方不明

  1. 矢印をコピー(CTR+C)して、そのまま貼り付け(CTR+V)すると近くに貼り付く
  2. どこかのセル等をクリックした後だと、そのセルを基点として相対位置に貼り付く

例　B3の矢印をコピーし、D5セルをクリック後だと、E7に矢印が貼り付きます。
その為、スクロールするような位置でコピーして、クリック後に貼り付けると行方不明と思われるような位置に貼り付いていたわけです。

Excel2013以降では、クリックしたセルに貼り付くようになっているので問題無くなっている。
また、CTRL+ドラッグ＆ドロップだと好きな位置へコピー出来る。

ExcelでCSV出力データを表示した場合の注意点

Excelの普及により、帳票代わりとしてCSV形式で出力したデータをExcelで見ることが多くなりました。
帳票を作成するとなると製造工数が増え、それだけ金額も増加するのですが、CSV出力にすると製造工数も帳票よりは少なく済みデータの分析や加工が出来る為、ユーザからCSV出力を求められることが多々あります。 但し、ExcelでCSV出力データを表示した場合、初期表示の書式設定はExcel側で行うため予期しない表示がされてしまいます。

注意点

Microsoft Excel は、次の条件に基づいて、セルに組み込みの表示形式を自動的に適用します。

http://support.microsoft.com/kb/214233/ja

補足

対処方法

Excelファイルの差分比較

多機能アドイン RelaxTools-Addin

Excelを便利にする250以上の機能を体系化したアドイン「RelaxTools-Addin」となります。
Excelを最強に。「RelaxTools Addin」の371の機能から僕のお気に入りを紹介する

インストール方法

トラブル対応

Excelファイルが保存中エラー

Excel の自動バックアップ機能の概要

※基本的には、自動バックアップファイルが生成されるのは1つだけです。

Excel自動保存ファイルの保存場所 

-- ユーザー名は各自変更
C:\Documents and Settings\ユーザー名\Application Data\Microsoft\Excel

ファイルの拡張子「xlsb」で保存されているので、更新日時で判断する。

Excel自動保存アドイン

Excel標準の自動保存機能とも違い、世代管理がありますのでExcelが壊れた場合でも復元出来ます。
Excel自動保存アドイン