Excelコワザの最近のブログ記事

Excel関数一覧

| コメント(0) | トラックバック(0)
できるネットで関数一覧が掲示されていたので、ここにも掲示

<参考サイト>

よく使う関数は覚えているが、こんなことができないかなぁ、と思うことが多いのが、ボクの場合は「情報関数」や「論理関数」で、迷うことが多いのもこの関数だ。便利なのは、「文字列関数」。規則性を見つければ、文字列から操作することにより、ピンポイントでデータを抽出できる場合もある。

理系ではないので、「三角関数」を使うことはほぼ、ない。「統計関数」も使う範囲は限られている。だが、見ていると、面白い。

Vlookupの参照先はSheet1!A1:E520などとなることが一般的だが、この参照先を固定化してしまおうと言うのが、「構造化」ということらしい。


この「構造化」を使うと、この参照先の入力が非常に多い場合、ラクになるのだ。つまり、参照先のSheet1!A1:E520などを名前を付けてやることで、例えば「給与」などとしたシートとそのシート内の範囲を固定化することがラクになる。


先の例に倣うと、

IF(ISNA(Vlookup(A1,Sheet1!A1:E250,3,FALSE))," " , Vlookup(A1,Sheet1!A1:E250,3,FALSE))

IF(ISNA(Vlookup(A1,給与,3,FALSE))," " , Vlookup(A1,給与,3,FALSE))

と簡略化ができるということで、入力ミスもなくなる。また、参照範囲が増えても、ちょっと変えてやるだけで、式を入れたマトリクスが全件変わるというところが「効率化」できるのだ。


<参照先>

【エクセル時短】VLOOKUP関数には「構造化参照」! テーブルを活用してデータの増減に自動対応する


Vlookup で #N/A が出る

| コメント(0) | トラックバック(0)

この問題は以前、ここに掲載した心算りだったが、掲載していなかったようだ。


Vlookup関数を使うと、データベースを参照しながら、数値を持ってこれるので、とても便利なのだが、参照先に該当のデータがなかった場合、#N/A が表示されてしまい、見栄えのよくない表ができてしまうことが多々、ある。そこで、この #N/A を表示させないためのテクニックだ。


IF(ISNA(Vlookup(A1,Sheet1!A1:E250,3,FALSE))," " , Vlookup(A1,Sheet1!A1:E250,3,FALSE))


といった構文になる。IF文で、「該当データがなければ " " (ブランク)で表示してね、という意味の文章で、ISNA文で #N/A かどうかを調べている、という事だ。探している参照元データ(A1に相当する部分)が、参照先データベース(Sheet1!A1:E250,3,)で該当するものがない場合、#N/A が還される。まぁ、計算なので、正直に出てきてしまうのだが、その表示改変といったところだ。

✵#N/Aが出ることによって、DBに該当するものが「ない」ということも大切なのだが。


<参考サイト>

【エクセル中級者向け】数式・関数のエラー表示の回避テクニック③④【IF関数+ISERR関数・ISNA関数】



久々に更新である。最近、勤務先の業務で給与計算業務を担当するようになった。あれこれとExcelを駆使することが多いが、それほどの高度のテクニックがなくても、意外と高度の処理ができてしまう。だが、甘んじることなく、合理化を勧めて・進めていきたいものだ。少人数のエキスパート集団だ。

csvで作成されたデータを読み込んだ後、Excelにして、金額部分をカンマで表示するようにボタンをクリックしたのに、カンマ表示されない。でも、そのセルの中に入って、文字をクリックすると、カンマが付く。これを、社員数✕項目数分、セル単位でクリックしていたら、日が暮れてしまう・・・

書式を #,##0 に変更したのに、カンマを打たない。ただ、そのセル内にカーソルを移し、クリックすると、#,##0 に変わる。
1234567890  →     1,234,567,890

そこで、googleで検索したところ、有用なサイトを発見した。メモ。

【参考サイト】

ボクは一部の表記を変えて、作成した。「書式再設定」である。

Sub 書式再設定()
    For Each e In Selection
        e.Value = e.Value
    Next
End Sub



今回はExcelのちょっと変わった使い方である。ワタシ自身もExcelの表上に「透過型画像」を写し込んだ表を作る時があるが、それほど回数はなく、年に数回なので、間があくと、わからなくなってしまい、また、記憶にも留めておけない場合が多い。最近、無償ソフトを発見したのだが、そのソフトがDLできないときなど、参考になるだろう。今回は下記のサイトを参考にしたが、以前に比較すると、他にもわかりやすいサイトが増えたよう思う。

<参考サイト>

<参考フリーソフト>
但し書きにあるのだが、透過型gifは作れない





ちょっと、使わないと忘れてしまう小技がある。今回はいつもは、マクロ登録ボタンを[ フォームコントロール ] を使っているのだが、ボタンに「色を付けたい」という単純な理由からだった。いつも灰色では目立たないから、少し、工夫したいと思っただけ。

<参考サイト>
定型の用紙に名簿から番号などを読み込み印刷するマクロ

自宅でプログラミング!

カラーコードをついたかったのだが、業務の時間が迫っていることもあり、今回はここで諦めて、時間の許すときに。

マクロボタン.gif


cm単位で業務を進めるケースがある。帳票上にcm単位で四角の箱を入れたいときなどであるが、はてさて、cmでは設定は? となってしまった。

38ピクセルで1cmになるようである。

ただし、プリンタの性能に左右されるようだ。でも、覚えておくと便利だ。エクセルの最上部や最左部でカラムとカラムの間を見ると、たいていはデフォルトで、幅は8.38(72ピクセル) 高さ13.5(18ピクセル)と表示される。
ピクセル側の表示を確認しながら、380ピクセルにすれば、幅が47(381ピクセル)と表示され、大凡、幅10cmの枠ができる。

この計算をしてくれるサイトもあるようだ。

<参考サイト>
サイズ計算式


また、既定のピクセル表示を変えて、操作する方法もあるようだ。

<参考サイト>

Excelのパスワード

| コメント(0) | トラックバック(0)
Excelブックにパスワードが付いているものがありますが、毎回開くたびにパスワードを入力するのはメンドウだし、使用頻度が高い時期はパスワードを覚えていても、使わなくなるとパスワードを忘れてしまい、開けなくなる・・・ そんな経験はないでしょうか。業務系で他会社とExcelファイルのやりとりをすると、メールでパスワードを別便で送付され、毎回、このメールを参照するのもメンドウです。
 
さて、これを解消する方法です。前提条件はこのファイルを開くパスワードは覚えておき、一旦ファイルを開きます。[ 名前を付けて保存 ] を選び、下部にある、[ ツール ] の [ 全般オプション ] を選び
[ 読みとりパスワード ]および [ 書き取りパスワード ] を空欄にして [ OK ] をクリックします。後は既存のファイルに同じ名前で保存して上書きすれば完了です。
PassWord_reset[1]_1394514421461.jpg
Excelを使っていて、365日にその日数の貯金をすると、6万6千円も貯金できるという記事が書かれていた。少し、興味があったので、さっそく実行してみることにした。1円〜365円mまで、任意の数値を貯金すればいいのだ。例えば、500円硬貨が余っていたので、貯金したいなら、365円+135円でもいいし、200円と300円でもいいわけだ。そして、その数値を塗りつぶす。Excelで1〜365をマス目に入れて、印刷しておいて、そこに斜線を入れて、チェックすればいい。そうすれば、66,795円貯金できるのだ。
 
(365-1)÷2*365+365=66,795
 
さぁ、そこまではいいのだが、この合計額を出したいとなると、少し難儀だ。毎回、計算しなくちゃいけないし、貯金の都度、計算するのもメンドウだなぁ、と思ったところで、表題の「色付したCellの合計を出す」Excelはないかな。検索するとヒントがたくさんあった。

<参考サイト>
Chiquilin Site■05.08.03_Excel:色付きセルを計算する

ここを参考にして、マス目計算を完成させる。ただ、時々、「自動計算しない」時があった。解消方法はPCのファンクションキー [ F9 ] を押すことだ。これで、強制的に再演算してくれる。

■ポイント
 B1セル上で [挿入]→[名前]→[定義]
 ※[ ctrl ]+[ F3 ] で呼び出すことが可能

 名__前:色番号

 参照範囲:=GET.CELL(63+NOW()*0,A1)
 →[OK]
 ※背景色の場合。文字色の場合は「63」の部分を「24」にして下さい

これで基本形は完成したので、A1の部分を必要な形式に加工して保存。
「関数」[ 色番号 ]を作ったので、該当の色番号を呼び出すマス目に =色番号 と計算式で入力すると色の値が出る。黄色ならば、「6」だ。で、6が表示されているマス目のみを計算する計算式を組む。

=SUMIF(B1:B8,5,A1:A8)


5は青だし、6は黄色だ。そして範囲を設定して、sumすればいいことになる。

水色部分を修正しました。(2017.10.19)

既定のプログラムの変更

| トラックバック(0)

 

特にどのソフトウェアと固有名詞は出さないが、「この程度はProヴァージョンじゃなくてもできてほしいな」というものが、オプションになっていて、ガチガチに固められている。そのために、いろいろな応用動作が取れないことが多い。そんなことで業務の遂行が遅れがちになるのだが、それを解消してくれる便利な無償ソフトウェアがあると新たにインストールして使用することが多い。

だが、そうすると、よく使うお金を出して購入したソフトが置き換わってしまい、いままで使っていた「既定のプログラム」が変更されてしまい、面倒なことになることが多い。そんな時には「既定のプログラム」を変更させることにより、元のソフトウェアで常時使う状態に戻すことが可能だ。ワタシの場合、つい、うっかりとファイルを開けたときに、毎回、「既定のプログラムにしますか」というポップアップを確認もれし、新たにインストールされたPDFを操作するプログラムに置き換わってしまい、面倒なことになった。

 

検索したら、丁寧な解説で読みやすいサイトがあったので、ご参考まで。

 

<参考サイト>

ファイルを開くときに使用する既定のプログラムを変更する

このアーカイブについて

このページには、過去に書かれたブログ記事のうちExcelコワザカテゴリに属しているものが含まれています。

前のカテゴリはAutoCADです。

次のカテゴリはGoogleSpreadSheetです。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

2018年1月

  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

ウェブページ

  • picture
Powered by Movable Type 4.261