Memo/Excel

http://dexlab.net/pukiwiki/index.php?Memo/Excel
 

Excel


秒を経過時間として表示

  • A1に秒を入れると、経過時間を表示する。
    1. =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の最終更新日を取得

  • VBAを使う
    Function LastSaveTime()
      Application.Volatile
      LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last save time").Value
    End Function
    • セルの入力値
      =LastSaveTime()

セルの書式なしコピー

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

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

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

指数表示を文字列に直す

デフォルトでは"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 + 通常行の左端(アイコンが十字に変わる)をドラッグ&ドロップ

分かりやすいグラフ

重複行の表示

  • A2に以降にコピーする
    =IF(COUNTIF(B:B,B2)>1,"重複","")
  • データ
    	A列	B列
    重複	1	1
    重複	2	1

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)

ガントチャート

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

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

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

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

  • ポイント
    • 条件を「数式」にする
    • 条件指定行を固定にしたいので行に「$」を付ける
      =$G3="完了"
      (逆に列を固定したければ、G$3のようにする)
  1. 対象の範囲を選択
  2. メニューの「書式」「条件付き書式」

excel_行条件書式.png

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

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

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

添付ファイル: fileexcel_行条件書式.png 801件 [詳細] fileclip_2.png 751件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2016-12-13 (火) 14:30:15 (586d)