2014年8月30日土曜日

EXCEL 仕事で覚えた小技　フィルタするなら、まずフィルタクリア

　EXCELでフィルタで行を絞る際、しばしば、陥る落とし穴は、既にどこかの列がフィルタで絞られていることに気がつかない、あるいは、忘れていることです。
　そのまま、フィルタをかけても、誤った結果しか、得られない、どうも変だ、出てくるはずの行がでていない、などいった罠にハマってしまうことが、多々あります。
  • 最初のフィルタ前にフィルタ全クリアが鉄則
また、これが一番、ハマルのですが
  • こまめにセーブは基本中の基本
せっかく、何時間もかけて作成した作品、編集した結果がちょっとした「気の迷い」、操作ミスで、セーブせずにEXCELを終了してしまうことがあります。「こまめにセーブ」はEXCELに限らず、いろんなシーンで言えることでもあります。
　また、これと似ているのですが、
  • 修正のたびに別文書へ
これもかなり、重要。救いの神です。修正したら、こまめに別文書でセーブ。私は、文書名の一部に日付や時刻、改版理由などを付けて、版数を管理しています。



A lifesaver's coming


投稿者 時刻: 0 件のコメント:

2014年8月28日木曜日

仕事で覚えたEXCEL小技　条件付き書式でセルに色を付ける

 条件付き書式でセルに色を付けるには、条件付き書式タブで、例えば、条件として
  • NOT(ISNA(VLOOKUP($Sheet1.$K$2,holidays,2,FALSE())))
として、色を設定しておきます。これにより、holidaysという名前を付けた祝日リスト（日付、祝日名）のテーブルを参照して、該当すれば、色がつくことになります。

 これと、以前、ご紹介した小技
 excelで前月の最終月曜日を計算する EXCEL fuction calendar
と組み合わせると、イカしたカレンダーができます。



投稿者 時刻: 0 件のコメント:

2014年8月26日火曜日

仕事で覚えたEXCEL小技　SUMとSUBTOTAL

複数のセルの合計を計算するときに使う関数としては、普通なら、
  • SUM関数で、＝SUM(A1:A10)
みたいに使いますが、
  • SUBTOTAL関数を使い、=SUBTOTAL(9,A1:A10) 
とすれば、
  • 途中のセルをフィルターで除外したら、その部分はカウントされない
という便利な効果が出てきます。

逆に言えば、SUM関数にすれば、フィルターをかけても、合計値に影響を与えることはありません。

私は合計をとるときは、SUBTOTAL関数を使うことが多いです。

ちなみに、SUBTOTALの次の9は、合計値を求めるときに指定します。３を指定すると、対象となった数値の個数（セル数）が入りますので、平均値をとるのに使えます。
  • 平均値　=SUBTOTAL(9,A1:A10)/SUBTAL(3,A1:A10)
複数の列に同じ式を使えるというのがポイント（ミソ）です。

ここでちょっと一息のコーナーです。

　ニュージーランドでの車の運転ノウハウ。レンタカーの旅は、楽しいですよー。
　ニュージーランドの道路の交差点は、信号がなく、「ラウンド・アバウト」という、道の交差が角ではなく、サークル（円型） になっているところがあります。さすがに大都会のオークランドでは信号がある交差点が多いですが、田舎では殆ど、この信号なしのラウンドアバウトばかり。信号機を付けなくてもよいし、信号を待たなくてもよいという利点があると思います。
　このランドアバウトでは、車はサークルに入る前に一旦、停止し、右から車に気をつけながら、サークルへ進入します。そして、サークルをまわりつつ、自分の行きたい道へと入ればよいわけです。サークルへ入るタイミングをつかむのが難しそうですが、そんなに車が多くないので、意外と大丈夫ですよ。
　サークルの中が２つ、３つ車線がある大きなサークルでは、回っているうちに車同士がぶつかりそうで、ちょっと怖い感じがありますが・・・。




投稿者 時刻: 0 件のコメント:

2014年8月24日日曜日

IE + VBAで仕事を効率化　Webシステムを自在に操る

　これまでに、以下の３ページで、もの凄く、簡単に、Webシステムの操り方をご紹介しましたが、この２ページは、途中で挫折しかけつつも、３ケ月もかかり、「まわり道」したおかげでのスキルの「つながり」があり、なんとか、やりたいことができたのでした。
　インターネットでいろいろ、ご指南して頂いたおかげもありますが、うまくいかない時、「まわり道」になっているなぁと思うときこそ、頑張って、精進することが大切なのだなぁと最近、つくづく思うのでした。

　人生に無駄なことなど一つもない。そして、最後まで諦めないこと。(と思う、思いたい・・・、てっ）　
   The following 2 web pages introduce how to get info from web systems automatically by IE + EXCEL VBA.
   It took 3 months for many tries and errors. I almost set back.  

　Nothing is useless in your life. Never give up! I think, want to believe.
　最後に、デバッグ方法なのですが、VBAの「ブレークポインﾄ」と「Debug.Print」でできましたよ。My debug methods are use of "Breakpoints" and "Debug.Print".
  • Debug.Print "これじゃ”& objIE.documet.URL
みたいな感じで、内容を出力する変数（パラメタ）を指定したり、特定のメッセージを出すことでプログラムが思ったとおりに動いているのか、確認します。簡単です。Very simple.



 

投稿者 時刻: 0 件のコメント:

2014年8月21日木曜日

仕事で覚えたEXCEL小技　$(ダラ)って便利だら

仕事でEXCELを最短で習得するには、近くにいる達人に聞くこと。

VLOOKUPの第２˚パラメタで、範囲を指定する際、$A$1:$E$10と通常の範囲指定の頭に$（ダラ）を指定しておく。
すると、VLOOKUP を入れたセルをコピーして、他のセルに展開するときに、
EXCELが勝手には範囲をシフトすることを封じる（防ぐ）ことができる。

EXCELの自動変換は、便利なこともあるが、邪魔になることもある。
それを封じるのが、$（ダラ）だね。便利だらー。















　ここで、ちょっとひと息。ニュージーランドのレンタカーのお話。
　２０１２年、北島のオークランドでレンタカーを借りた。大手が安全なので、ハーツ（Hertz）をネット予約。
　空港にあるハーツのカウンターでレンタカーのチェックインをテキトーな英語でどうにか済ませると、キーとカーナビ（オプションで付けた）を渡され、車の置いてある番号（例えば８４と）が告げられる。
　これだけだけだ。That's It. ハーツは空港ビルのすぐ隣に駐車場があるので、とても便利。車を返すときもこの同じ番号の場所に駐車し、キーとカーナビを空港内のカウンターへ返せばよい。凄くシンプルなシステム。

投稿者 時刻: 0 件のコメント:

2014年8月16日土曜日

IE+VBAで仕事を効率化　Webページの表（TABLE）をセルに取り込む

IE＋VBAでWebシステムを自動操作するときのノウハウ～Webページの表が
タグで作られているなら、以下のコードで表の内容をセルへゲットできるよ。
' 　j : row counter 　　i : line counter 
j=0
  For Each objTBL In objTBL.document.Frames("bodys").document.all.tags("TABLE")
    For Each objTableItem in objTBL.all
       Select Case objTableItem.tagName
       Case "TR","TD","TH"
         Select Case objTableItem.tagName
           Case "TR"
             j = J+1
             I = 0
           Case "TD","TH"
             i = i + 1
             WorkSheet(your-sheet-name).Cells(j,I).Value = objTableItem.innertext
         End Select
       End Select
     Next
   Next
やればできる。時間はかかるけど、でも次に繋がる。そのときは、楽できるはず。それを信じて・・・。　
私のVBAバイブルは、大村あつしさんの本。とりわけ、Windows APIを活用する本は秀逸。図書館で何回借りたことか。先日、図書館に行ったら、古い本のためか、本棚から姿を消していた。残念、いい本は内容が古くなっても、そのエッセンスは不滅で、いつの時代でも通用するのに・・・。 でも新しいヴァージョンが出ていました。コレですよ、コレ。最新化されたんですね。凄い！

投稿者 時刻: 0 件のコメント:

2014年8月15日金曜日

debian wheezy+avastでdatabase updateできない　そして　引数エラー(argument error)

linuxウィルス対策ソフトにavastとclamavを入れてみた。
avastはGUIだと、databese updateが失敗したけど、以下で解決。

debian wheezy+avastでdatabase updateできない　のであれば
  • ターミナルでavast-update
そのあと、debian wheezy+avastで引数エラー(argument error)　であれば、
  • sudo sysctl -w kernel.shmmax=200000000
でもこれは、一時的なので、スクリプトでやるように変更。

clamavの方は、ノートラブル。凄いね。
  • スキャン開始　clamscan -r
      • slackwareの場合、Slackbuild(sbopkg)でdownload+build+installし、freshclam その後 clamscan -r
      •   /etc/rc.d/rc.clamav start でデーモン起動で終わり。
linuxのウィルスソフトは、なぜ少ないのかなぁ・・・



投稿者 時刻: 0 件のコメント:

2014年8月13日水曜日

excel 仕事で使える小技　Qを抽出

A1セルにYYYYMMの形式でデータが入っている時に四半期のどれか（１Q、２Q、３Q、４Q）を抽出するには
  • =IF(QUOTIENT(RIGHT(A1,2)-1,3)=0,"4Q",QUOTIENT(RIGHT(A1,2)-1,3)&"Q"
ただし、日本式の4月始まりの年度でのQですよ。
頭にYYYYをつけて、YYYYnQ (ex 20141Q)にするには
  • =IF(QUOTIENT(RIGHT(A1,2)-1,3)=0,(LEFT(A1,4)-1)&"4Q",LEFT(A1,4)&QUOTIENT(RIGHT(A1,2)-1,3)&"Q")
地味ですね、本当に。もっと、サクッとできる方法があるといいんだけど・・・

Which quarters is the value of A1 cell in which data is stored like YYYYMM format?
ちなみに、１月始まりの年度の場合は、以下の式です。

  • =IF(QUOTIENT(RIGHT(A1,2)-1,3)=0,"1Q",QUOTIENT(RIGHT(A1,2)-1,3)+1&"Q")
  • =IF(QUOTIENT(RIGHT(A1,2)-1,3)=0,(LEFT(A1,4)-)&"1Q",LEFT(A1,4)&QUOTIENT(RIGHT(A1,2)-1,3)+1&"Q")



wolfgiurl
wolfgiril








ところでアマゾン（Amazon）のギフト券ですが、千円単位なので、余るときには、安いkindle本とかできっちり、千円単位にしてしまった方がよいですね。私はやられてしまった。余った金額をしぼりとられた。あちゃー。





投稿者 時刻: 0 件のコメント:

2014年8月7日木曜日

EXCELで困ったときの小技　うまくCSV化できないなら、Rubyを使え

EXCELのシートをCSV化したい場合、EXCEL標準機能でファイル保存でCSV形式にするとセル内改行やカンマが邪魔して、思い通りのCSVファイルにならないことがある。
そんなときには、EXCELの機能を利用することは諦めて、rubyスクリプトを使おう！

rubyがインストールされていれば、以下の３つのステップで簡単にできるよ。
１）ADODBでEXCELファイルをオープン
２）SQLのSELECT文で1行ずつ、読み込む。
３）読み込んだ1行に大して、セル内改行とセル内カンマを除去する

ただし、最初の1行目をフィールド名として使う場合、そのステップが必要となる。














１）ADODBでEXCELファイルをオープン
require ''win32ole'
# Float() ends in no error or error
def Float_String?(str)
  Float(str)
  true
rescue ArgumentError
  false
end
cn = WIN32OLE.new("ADODB.connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "Dtata Source=" +  ARGV[0] + ";Extended   Properties=Excel 8.0;"
cn.CursorLocation = 3
cn.Open
２）SQLのSELECT文で1行ずつ、読み込む
sql =  "SELECT + FROM  [" ARGV[1] + "$]"
rs.Open sql
# to get field  name
rec = []
rs.Fields.each do |col|
  text = col.Name
  rec,push(text)
end
puts rec. join(",")
３）読み込んだ1行に大して、セル内改行とセル内カンマを除去する
while !rs.EOF
  rec = []
  rs.Fields.each do |col|
    text = col.Value
    if text != nil and !(float_string?(text))
      text.gsub!(/(¥r¥n|¥r|¥n|,|)/,"")
    end
    rec.push(text)
  end
puts rec. join(",")
rs.MoveNext
end
rs.Close
cn.Close
10年くらい前にrubyを、５年くらい前にはSQLをほんの少しだけ、かじっていたんけど、いろんな「ひとかじり」がつながっていくことは嬉しい。実に、地味だけど。
そして、上記のコードの殆どは、インターネットでゲットできるんだけど、ポイントは、gsubによる「セル内改行とセル内カンマ」の除去。
投稿者 時刻: 0 件のコメント:

2014年8月4日月曜日

EXCEL 仕事でつかえる小技　VBAで複数セル（文字列）の連結 EXCEL Tips at work-- VBA concatenating two cells

　覚えたいくつかの事柄の断片（スキル）が、ふとした瞬間につながることがある。
　EXCELでのセルの選択状態をVBAで読むFor Each in Selectionとコピペできるメッセージを出力できるInputBox。
　小さな積み重ねだけど、できると案外、嬉しくなる。
One day, some pieces got at work connect each other and then become a tips.
"For Each in Selection" picks selected cells with the selected order and "InputBox" enables to copy and paste the message output by Inputbox.
Very tiny things but it make me happy when it come up as new idea.
　EXCELで選択している複数セル（文字列）を連結するVBA（マクロ）は、これ。
Here is the VBA which concatenates cells with the selected order.

Sub concatenate()
  For Each  r In Selection
    streach = r.Value
    If strselect = "" Then
      strselect = streach
    Else
      strselect = strselect & " " & streach
    EndIf
  Next
  InputBox Prompt:="Please Copy and Paste", Default:= strselect
End Sub
EXCELのSelectionを使うので、セルを選択した順番をキープして、連結が行われます。
規則性がない、離れたセルも連結できますよ。

Selection of EXCEL enables to pick up cells with the selected order for any cells. without any rules and even distant cells.
投稿者 時刻: 0 件のコメント:

2014年8月3日日曜日

石の上に３年、泥縄なACCESS習得方法

  知識がゼロの状態から、仕事のために自力でACCESSを使いこなす（新たなテーブルを作り、リンク関係を定義できる）までの泥縄なノウハウ習得のストーリ。
◆入門編〜マ、やるしかないっスよ！
まず、前任者が作成したACCESSをそのまま引き継いだ。幸運なことに、ちゃんとした引継の期間とトレーニングがあり、１から１０まで聞けた。最初は、専門用語や操作の仕方などわからないことばかり。でもみよう見真似でマネすれば、元データからACCESSのDBを作成し、クエリーを出して、データを検索し、加工＆集計（これはEXECLを駆使）するまで、３ケ月ぐらいでできるようになった。コツは、教えてもらったことを 、とにかくマネして、使うこと。マネしてマネしてマネる。これに尽きる。失敗が多かったのですが、なんとかなるものです。

ところで、日本にはオープンサーバという不思議な英語があるって、ご存じですか？



英語ではオープンサーバ（open server)とは言わないようだ。UNIX server,Windows server, Linux serverとか、個々のサーバで表現するようだ。
日本語のオープンサーバとは、メインフレームに代表される
独自ハードかつ独自OSというプロプライアタリな技術で構成されるサーバに対し、
UNIXやLinuxなどオープンな技術で構成されるサーバのことを指す。ややこしいのは、多くの場合、Windowsもオープンサーバに分類されることだ。Windowsはマイクロソフトのプロプライアタリなサーバなので、クローズサーバ(Close)またはクローズドサーバ（Closed Server) でよいのではないか？
ちなみに、オープンサーバを英訳するとすれば、open technology based serverはどうだろうか？しかし、英語圏で通じるためには、やはり、個々のサーバでいうのがいいのかなぁ。

へんな英語を作り出す、不思議の国、日本ですわ。

How wonder Japan is !





投稿者 時刻: 0 件のコメント:

2014年8月2日土曜日

仕事で使えるEXCEL小技　番外編　ブイルックアップVLOOKUP + ピボットPIVOT

　EXCELとの出会いは、会社の仕事でブイルックアップVLOOKUP。こんな便利なものがあったんだ！知らんかった。てっと。
　例えば、ある列の値（例ではA列）をすべての行について、判別し、特定の文字列（例では”くるみ”か”りんご”）であれば、○をつける。 
＝VLOOKUP(An,"○をつけるための表”,2,FALSE)
＜○をつけるための表＞・・・どこにあってもOK
AB
　1　くるみ　○　
　2　りんご　○　
これが使えようになったのは、職場にEXCELの師匠がいたおかげだ。本で勉強するよりも、最初は教えてもらった方が断然、習得速度が早い。

　仕事のEXCELの必須アイテム、次は、ピボット（PIVOT）だね。
このPIVOTこそ、職場にいるEXCELの師匠に聞いた方がよい。
PIVOTって何、どういう時に便利か。ひとことで言えば、横串集。いわゆる、クロス集計。





投稿者 時刻: 0 件のコメント:
登録: 投稿 (Atom)