Memo/Excel

https://dexlab.net:443/pukiwiki/index.php?Memo/Excel 		 

Excel †

↑

自動化 †

↑

データ化しやすいExcelの使い方 †

↑

スピル: 配列式で複数の値を自動計算させる †

  • Excel 2019から対応
  • メリット
    • 複数行に対して同じ計算をしたい場合、計算式のコピーをしなくて済む
↑

データ分析 †

↑

テーブル構造 †

↑

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の最終更新日を取得 †

  • 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. 

  2. メニュー > データ > 区切り位置
    3. 

  3. 次へ > 次へ
    4. 

  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. 

  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. 

  2. 入力値の種類を「リスト」に変更
    3. 

  3. カンマ区切りで値を入力するか、シートの範囲を指定する。


  • clip_2.png



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


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


  • ポイント

    • 条件を「数式」にする
      • 

    • 条件指定行を固定にしたいので行に「$」を付ける
      

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


  1. 対象の範囲を選択
    2. 

  2. メニューの「書式」「条件付き書式」


excel_行条件書式.png



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


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


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


  1. メニュー[データ]->[外部データの取り込み]->[データの取り込み]と進み読み込むCSVファイルを指定します。
    2. 

  2. 元のデータの形式は、カンマでしたらそのまま次に進み、カンマやスペース区切りを選択し、次へ進みます。
    3. 

  3. セパレータ記号を正しく選択し、データのプレビューでも正しく区切られていることを確認します。
    4. 

  4. 次に進み、ゼロを表示したいフィールドをクリックして選択し、右上の列のデータ形式を文字列に変更します。
    5. 

  5. 規定値では標準の形式になっているので、これを「文字列」にします。規定値では数値として認識され、無意味なゼロが削除されてしまいます。



  		

  
   











   

  		

 








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








 トップ
  
 	編集			凍結	  差分	バックアップ			添付		複製	名前変更 リロード  
	新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS



Last-modified: 2021-01-27 (水) 20:54:02  (262d)