Excel †

---

データ分析 †

• 記事
  • Developers.IO 2019 Sapporoで「全部Excelだけで実現しようとして後悔するデータ分析」というタイトルで発表してきました #cmdevio ｜ DevelopersIO

---

テーブル構造 †

• 記事
  • [Developers.IO 2019 Sapporoこぼれ話] Excelのテーブル機能と構造化参照 ｜ DevelopersIO

---

UTF-8のCSVやTSVを開く †

csvだと強制的にShift_JISとして開いてしまう。

• Excel 2010の場合

1. データ > 外部データの取り込み > テキストファイル
2. 元のファイル：65001: Unicode (UTF-8) を選択

• 複数ある場合、テキストエディタ等でshift_jisへ変換する方が早いかも

---

秒を経過時間として表示 †

• A1に秒を入れると、経過時間を表示する。

  =TEXT(A1/86400,"dd:hh:mm:ss")

---

前月の日付を取得 †

• A1:2012/02/27
• B1:2012/1/1

  =(TEXT(A1,"yyyy/mm")&"/01")-1

---

日付の年、月、日を別々に取得 †

• A1:2012/02/27
• B1:2012/2/1

  =TEXT(A1, "yyyy/m")&"/27"

---

Excelの最終更新日を取得 †

• 記事
  • ファイルの最終更新日時を取得したい:Excel エクセルの使い方-ユーザー定義関数/VBA

• VBAを使う

  Function LastSaveTime()
    Application.Volatile
    LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last save time").Value
  End Function

  • セルの入力値

    =LastSaveTime()

---

セルの書式なしコピー †

• 通常、コピーしたいセルの右下を左ドラッグでコピーできるが、書式までコピーされる

• 書式なしコピー
  • コピーしたいセルの右下を右ドラッグでコピー。以下の選択肢が出る

オートフィルタで抽出された部分だけ合計 †

• subtotal関数を使う
  • 合計

  • その他

    値	集計関数	意味
    1	AVERAGE	平均
    2	COUNT	数値の個数
    3	COUNTA	データの個数
    4	MAX	最大値(数値)
    5	MIN	最小値(数値)
    6	PRODUCT	積
    7	STDEV	標準偏差
    8	STDEVP	標準偏差
    9	SUM	合計
    10	VAR	分散
    11	VARP	分散

---

指数表示を文字列に直す †

デフォルトでは"1234567890"は"1.E+09"になってしまう。1列を選んで書式設定 > 文字列にしても元に戻らない。
一度セルを編集すれば、文字列になるが、手間がかかる。

• Excel2003の場合

1. 文字列に戻したい列を選択
2. メニュー > データ > 区切り位置
3. 次へ > 次へ
4. 列のデータ形式で"文字列"を選択し、完了

---

カーソル行をハイライトする †

• Excel2003だと、ダブルクリックすると行の色が変わるようになる

• メニュー -> ツール -> マクロ -> Visual Basic Editor

  Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      Application.ScreenUpdating = True
  End Sub

• メニュー -> 書式 -> 条件付き書式 -> 「数式が」

  =CELL("row")=ROW()

---

列の移動 †

1. 列を選択
2. SHIFT + 通常行の左端(アイコンが十字に変わる)をドラッグ＆ドロップ

分かりやすいグラフ †

• 見栄えがよく、分かりやすいエクセルのグラフを作成する5つの簡単なテクニック | コリス
• エクセルのデータを見やすくする5つの簡単なテクニック | コリス

重複行の表示 †

• A2に以降にコピーする

  =IF(COUNTIF(B:B,B2)>1,"重複","")

• データ

  	A列	B列
  重複	1	1
  重複	2	1

Unixタイムスタンプとの相互変換 †

• つれづれなるままに - ExcelでUNIXタイムを扱う

• Excel -> Unixタイム

  =(DATE(yyyy,m,d)-25569-9/24)*60*60*24
  DATE(yyyy,m,d)に求めたい日付を入れる

• UNIXタイム -> Excel

  =(DATE(yyyy,m,d)/60/60/24)+25569+9/24
  もしくは、DATE(yyyy,m,d) の部分をUNIXタイムに変える

曜日(日〜土)の書式設定 †

• セルの書式設定を以下のようにする
  • 月〜日

    aaa

  • (月)〜(日)

    (aaa)

ガントチャート †

• http://mizhiro.mitelog.jp/myproject/2006/12/excel_7024.html

入力方式をリストからの選択式にする †

1. メニューから、「データ」->「入力規則」
2. 入力値の種類を「リスト」に変更
3. カンマ区切りで値を入力するか、シートの範囲を指定する。

• 

条件付き書式で行単位に書式設定する †

以下の例だと、「状況」によって、行全体の色を変更する。

• ポイント
  • 条件を「数式」にする
  • 条件指定行を固定にしたいので行に「$」を付ける
    

    =$G3="完了"

    (逆に列を固定したければ、G$3のようにする)

1. 対象の範囲を選択
2. メニューの「書式」「条件付き書式」
   

CSVファイル等で先頭のゼロが消えないように取り込みたい †

デフォルトだと、ダブルクォーテーションで括ってあっても、直接開いたりドラック＆ドロップでファイルを開くと、先頭のゼロが消えてしまいます。

取り込む前      取り込み後
000123456789 -> 123456789

1. メニュー[データ]->[外部データの取り込み]->[データの取り込み]と進み読み込むCSVファイルを指定します。
2. 元のデータの形式は、カンマでしたらそのまま次に進み、カンマやスペース区切りを選択し、次へ進みます。
3. セパレータ記号を正しく選択し、データのプレビューでも正しく区切られていることを確認します。
4. 次に進み、ゼロを表示したいフィールドをクリックして選択し、右上の列のデータ形式を文字列に変更します。
5. 規定値では標準の形式になっているので、これを「文字列」にします。規定値では数値として認識され、無意味なゼロが削除されてしまいます。