====== Excel ====== ===== Tips ===== ==== シート名の使えない文字 ==== [[http://qiita.com/RelaxTools/items/d173d0f94424349ca234|Excel のシート名に使えない文字について再考2017]] ==== エラーを無視して合計する ==== [[http://www.relief.jp/itnote/archives/017788.php|エラーを無視して合計する－配列数式]] ==== Excelでカンマと円付きの金額を数値にする ==== =VALUE(SUBSTITUTE(A1,"円","")) ==== クリップボードのCSVデータを貼り付ける場合 ==== 通常だと１つのセルにカンマ区切りで入ってしまいます。\\ 貼り付け後にExcelメニューのデータ→区切り位置でカンマを設定すると、カンマごとにセルに分けてくれます。 ==== テキストボックスの行間を調整する ==== 段落ダイアログボックスが表示されるので、インデントと行間隔タブにし、行間を「固定」にし、間隔の数値を変更します。\\ 間隔を小さくすると狭く、大きくすると広くなります。 [[http://excel.usefulhp.com/excel-waza/excel-waza38.htm|テキストボックスの行間を調整する]] ==== 重複データの抽出 ==== ※範囲は、"$"記号を付けて絶対参照とする。[[http://www11.plala.or.jp/koma_Excel/contents1/mame1006/mame100601.html|絶対参照と相対参照]] =COUNTIF(範囲, 検索条件) "重複"の文字に変換する場合 =IF(COUNTIF(範囲, 検索条件)>1,"重複","") [[http://www.hello-pc.net/howto-excel/excel_overlap/|第三十九回　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関連 ==== [[http://primarytext.jp/blog/1275|Excelで開くと文字化けするUTF-8のCSVを文字コードを変換せずに開く方法]] Excel2016の最新版(2016/10更新パッチ)でUTF-8形式のCSVを保存出来るようになった。\\ [[http://did2memo.net/2016/11/14/excel-csv-utf8/|ExcelファイルをUTF-8のCSV形式で保存する方法（Excel 2016最新機能）]] ==== 読み取り専用の設定方法 ==== 開発プロジェクトなど共同で作業をしている場合、仕様書やテーブルレイアウトを他の人が参照しているため、編集したい時に編集出来ないことが多々あります。\\ 参照するだけなら読み取り専用で開いてもらえるように2つの方法を提示します。\\ === 読み取り専用の設定/解除を追加する方法 === 「ツール(T)」→「ユーザー設定(C)」→「ファイル」→「読み取り専用の設定/解除」をツールバーに追加を行う。 === 開いた時に読み取り専用で開いてくださいという旨のメッセージを出すように設定する方法 === - 「ファイル」 - 「名前を付けて保存」→「ツール」→「全般オプション」を選択します。 - 「読み取り専用を推奨する」をオンにします。 - 次に開いた場合にメッセージが表示されるようになります。 ==== Excelのブックを別ウィンドウで開く ==== [[http://blog.jmiri.net/?p=995|Excelのブックを別ウィンドウで開く3つの方法]] - Excelを開きます。何らかのファイルでも、新規作成でも構いません。 - タスクバーに表示されたExcelのアイコンを右クリックし、[Microsoft Excel]をクリックします。 ==== 見えているセルだけをコピーする ==== [[https://forest.watch.impress.co.jp/docs/serial/exceltips/1275199.html|【Excel】コピペしたら非表示にしたデータまで貼り付けられた！　エクセルで見えているセルだけをコピーするテク！]] ==== 図形の線をまっすぐにする ==== 図形を選択して、リボンの描画ツールにある図形サイズで調整します。\\ [[https://yobog.hatenablog.com/entry/2016/03/26/100000|Excel 図形の線をまっすぐにする]] 直線を水平または垂直に引くには [Shift]キーを押しながらドラッグして描画します。\\ [[http://www4.synapse.ne.jp/yone/excel/excel_zu_cyokusen.html|Excelで斜めの直線をまっすぐに直したい：Excelの基本操作]] ==== 画像の背景を削除・透明化したい ==== - 画像を選択した状態で、［図ツール］の［書式］タブ→［背景の削除］をクリックします。 - 削除される領域が紫色に変わります。 - 削除する領域を調整するには、［削除する領域としてマーク］をクリックします。 - 削除する領域と保持する領域の調整が完了したら、［変更を保持］をクリックします。 [[https://forest.watch.impress.co.jp/docs/serial/exceltips/1103830.html|【Excel】シートに挿入した画像の背景を削除・透明化したい！エクセルで画像入り案内状や操作手順書の作成を効率化するテク]] ==== 文字列扱いになっている数字を数値へ一括で変換 ==== === 演算方式 === - 任意のセルに「1」を入力 - 1.をコピー - 文字列扱いになっているセル範囲を選択 - 「ホーム」タブをクリック - 「貼り付け」をクリック - 「形式を選択して貼り付け」をクリック - 「値」を選択 - 「乗算」を選択 - 「OK」をクリック [[https://excel-master.net/cells-worksheets-control/text-numerical-conversion/|文字列扱いになっている数字を数値へ一括で変換する方法]] === 「区切り位置」機能方式　=== - 対象の列の書式変更まで実施した後、対象の列を全選択 - 画面上部のメニュー内の「データ」→「区切り位置」を選択 - 「区切り位置指定ウィザード」が表示されますが、何も選択や設定を行わずに「完了」ボタンで完了 [[https://www.depthbomb.net/?p=1636|「区切り位置」機能で一括での書式設定の変更を適用]] ==== 表示レイアウト崩れ ==== Windows 10の表示スケールが125%で作成した場合、他の方の表示スケールが100%のディスプレイで見ると表示レイアウトが崩れて表示される。\\ マルチモニターを使用しているならディスプレイ毎に表示スケールが設定できるので、見比べて調整するしかない。 [[https://mseeeen.msen.jp/excel-printing-result-depends-on-windows-dpi-scaling/|[Excel] 拡大縮小とレイアウトの設定が印刷に影響していた件]] ---- ==== 図の矢印をコピーして貼り付けると行方不明 ==== - 矢印をコピー(CTR+C)して、そのまま貼り付け(CTR+V)すると近くに貼り付く - どこかのセル等をクリックした後だと、そのセルを基点として相対位置に貼り付く 例　B3の矢印をコピーし、D5セルをクリック後だと、E7に矢印が貼り付きます。\\ その為、スクロールするような位置でコピーして、クリック後に貼り付けると行方不明と思われるような位置に貼り付いていたわけです。 {{:it技術:excelcopy.jpg|}} **Excel2013以降では、クリックしたセルに貼り付くようになっているので問題無くなっている。**\\ また、CTRL+ドラッグ＆ドロップだと好きな位置へコピー出来る。 ---- ===== ExcelでCSV出力データを表示した場合の注意点 ===== Excelの普及により、帳票代わりとしてCSV形式で出力したデータをExcelで見ることが多くなりました。\\ 帳票を作成するとなると製造工数が増え、それだけ金額も増加するのですが、CSV出力にすると製造工数も帳票よりは少なく済みデータの分析や加工が出来る為、ユーザからCSV出力を求められることが多々あります。 但し、ExcelでCSV出力データを表示した場合、初期表示の書式設定はExcel側で行うため予期しない表示がされてしまいます。 ==== 注意点 ==== Microsoft Excel は、次の条件に基づいて、セルに組み込みの表示形式を自動的に適用します。 * 数値にスラッシュ記号 (/) またはハイフン (-) が含まれている場合は、日付形式に変換されることがあります。 * 数値にコロン (:) が含まれているか、または数値の後にスペースと A または P の文字が続いている場合は、時刻形式に変換されることがあります。 * 数値に文字の E (大文字または小文字、たとえば 10e5) が含まれているか、または数値に列の幅とフォントに基づいて表示可能な文字数を超える文字が含まれている場合は、その数値が指数表記の形式に変換されることがあります。 * 数値の先頭に 0 が含まれている場合、その 0 は削除されます。 http://support.microsoft.com/kb/214233/ja ==== 補足 ==== * 数値を引用符で囲んでいても文字列として扱ってはくれません。\\ * 入力した数字が12桁を超えると指数表示される。※12桁以下でも列の幅が狭いと指数表示される。\\ * 15桁しか有効表示されない。12345678901234567 → 12345678901234500 と16桁目以降は丸められる。\\ * 数値に文字の"E"が含まれている場合、指数表示される値として底を10としたときの指数部、約-307から約+307まで。\\ 例　1E307 → 1.00E+307 \\ ※1E308以降は、指数部がオーバーフローしているため数値として認識されず文字列として扱われる。 * 数値に引用符を付けてカンマ区切りにすると通貨型で表示されるが、小数3桁目は四捨五入され2桁までしか表示されない \\ "1,234.567" → 1,234.57 * 最大行数は、Excel95が16384行、Excel97～2003は65536行、Excel2007は1048576行 * CSVを表示した場合、全ての列は標準の列幅 8.38(72ピクセル)となっている。 ==== 対処方法 ==== * 数値型のコードがあった場合、先頭の0を表示したいままにするには、="001" と、"="を付け数値を引用符で囲むと文字列として扱われます。 * 数値にEのみが含まれる場合、先頭などに"*"等の数字以外をセットする。この対処方法はCSV取込を考慮した場合、かえって邪魔になってしまいますので注意が必要です。 ===== Excelファイルの差分比較 ===== * [[https://qiita.com/skanmera/items/d1dea61a7077f320524e|Excelファイルを比較するツールを作ってみた]] * [[https://qiita.com/akashima/items/4a31edc00b6768c9c4c7|Excelファイル同士をdiffするための方法]] ===== 多機能アドイン RelaxTools-Addin ===== Excelを便利にする250以上の機能を体系化したアドイン「[[https://software.opensquare.net/relaxtools/|RelaxTools-Addin]]」となります。\\ [[https://mirumi.me/relaxtools-addin|Excelを最強に。「RelaxTools Addin」の371の機能から僕のお気に入りを紹介する]] [[https://software.opensquare.net/relaxtools/downld/install/|インストール方法]] ===== トラブル対応 ===== ==== Excelファイルが保存中エラー ==== [[https://support.microsoft.com/ja-jp/help/289273/description-of-the-autorecover-functions-in-excel|Excel の自動バックアップ機能の概要]] ※基本的には、自動バックアップファイルが生成されるのは1つだけです。 Excel自動保存ファイルの保存場所 -- ユーザー名は各自変更 C:\Documents and Settings\ユーザー名\Application Data\Microsoft\Excel ファイルの拡張子「xlsb」で保存されているので、更新日時で判断する。 === Excel自動保存アドイン === Excel標準の自動保存機能とも違い、世代管理がありますのでExcelが壊れた場合でも復元出来ます。\\ [[https://www.vector.co.jp/soft/win95/business/se286196.html|Excel自動保存アドイン]]