6/30/2019

Excel。Excel VBA初心者だってやりたい!大量のファイル。ファイル名の文字はバラバラ、空白も消してまとめて整えたい!【File Name】

Excel。Excel VBA初心者だってやりたい!大量のファイル。ファイル名の文字はバラバラ、空白も消してまとめて整えたい!

<Excel VBA>

各店舗などから送られてきたデータを集めてみたところ、ファイル名が次のようになっていました。

本来は、従業員コード+氏名+test+日付(半角)という形式のファイル名にして送信してほしかったのですが、ルールに従ってくれていません。

testとしないといけないのに、テストとカタカナになっている。
2019と半角数字なのに、全角
ファイル名の中に、半角の空白や、全角の空白が入っている。

これをルールに従ったファイル名の形式に整えたいわけです。

本当は、次のようにしたほしかったわけです。

今回はサンプルなので、5件ぐらいなら、根性を入れれば修正できないこともありませんが、ファイル数が多くなると、面倒以外の何物でもありません。

イライラするだけですね。

こういう、単純な処理もマクロ。
Excel VBAを使うことで簡単にファイル名を整えることができます。

【考え方】
Excelのセル内の文字を置換することは簡単ですが、ファイル名はダイレクトに変更することは難しいので、次のような工程で変更していきます。

フォルダーにある、ファイル名をExcelに取り込む。
正しい形に整える。
整えたファイル名に置換する。

この作業工程に沿って、Excel VBAを作っていきましょう。

【Excel VBAを作ってみよう】
次のようにExcel VBAを入力します。

Sub File_Name()
    Dim buf As String, buf2 As String
    Dim cnt As Long
    Dim EndRow As Long
    Dim fname As String, fname2 As String
    Dim Newfname As String, Newfname2 As String
 
    buf = Dir("C:\Users\ Desktop\ExceVBA\*.xlsx")
    buf2 = "C:\Users\ Desktop\ExceVBA\"
 
    Do While buf <> ""
        cnt = cnt + 1
        Cells(cnt, "a") = buf
        buf = Dir()
    Loop
 
    Range("a:a").Copy Destination:=Range("b1")

    'データの最終行を検索
    EndRow = Range("a1").End(xlDown).Row
 
    For cnt = 1 To EndRow
        Cells(cnt, "b").Value = StrConv(Cells(cnt, "a"), vbNarrow)
        Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, "テスト", "test")
        Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, " ", "")
    Next
 
    For cnt = 1 To EndRow
        fname = Cells(cnt, "a").Value
        Newfname = Cells(cnt, "b").Value
     
        fname2 = buf2 & fname
        Newfname2 = buf2 & Newfname
        If Right(Newfname, 1) = "x" Then
            Name fname2 As Newfname2
        End If
    Next
 
End Sub

ちょっと長いかもしれませんが、説明をしていきます。

最初は、変数宣言のブロックですね。
Dim buf As String, buf2 As String
Dim cnt As Long
Dim EndRow As Long
Dim fname As String, fname2 As String
Dim Newfname As String, Newfname2 As String

それぞれの変数がどこでどう使っているのかは、後述します。

慣れるまでは、一行に一つの変数宣言文を書く方がいいのと思いますが、同じ型ならば、「,」(カンマ)で区切ることもできます。

パスを変数に代入します。
buf = Dir("C:\Users\ Desktop\ExceVBA\*.xlsx")
Dirの引数は、パスといって、今回修正したいファイルの場所をExcelに教えてあげる必要があります。

Excelファイルをダブルクリックで起動すると、パスの情報がないので、設定しておくといいですね。

buf2 = "C:\Users\ Desktop\ExceVBA\"
こちらは、ファイル名を置換する時に使うパスです。

なお、このパスは、使用しているPCによって変わりますので、実際に使用する時には、それぞれの環境に合わせて修正する必要があります。

このままでは使用できませんので注意しましょう。

【ファイル名をExcelに取り込む】
Do While buf <> ""
    cnt = cnt + 1
    Cells(cnt, "a") = buf
    buf = Dir()
Loop
ここで、フォルダーにあるファイルのファイル名を取り込む作業をします。

Do While buf <> ""~Loop
これで、フォルダー内にあるファイルをすべて検索することができます。
cnt = cnt + 1
Cells(cnt, "a") = buf

bufには、"C:\Users\ Desktop\ExceVBA\*.xlsx"。

つまり、ファイル名が代入されています。このファイル名を、Cells(cnt, "a")に入力します。cnt=1なので、A1に入力するわけですね。

buf = Dir()
で一度、クリアーします。
すると、Excelのシートには、次のように入力されます。

これで、フォルダー内にあるファイル名を抽出することができました。

【置換するための整えたファイル名を作る準備】
抽出したファイル名をB列にコピーして、コピーしたB列を修正していきます。
Range("a:a").Copy Destination:=Range("b1")

データの件数を数えているのが面倒なので、列選択して、B1に貼り付ければコピー完了ですね。

【ファイル名を整える】
フォルダー内に何件のファイルがあるのかを確認するのは面倒ですし、無駄な繰り返し処理をさせると処理速度が悪化しますので、繰り返し処理を行うために、データの最終行の行番号を取得させます。
EndRow = Range("a1").End(xlDown).Row


For cnt = 1 To EndRow
    Cells(cnt, "b").Value = StrConv(Cells(cnt, "a"), vbNarrow)
    Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, "テスト", "test")
    Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, " ", "")
Next

Cells(cnt, "b").Value = StrConv(Cells(cnt, "a"), vbNarrow)
vbNarrowは、全角文字を半角文字に変換する処理をしています。

この処理で、2019という全角数値は、半角数値に置換することができます。

Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, "テスト", "test")
この行は、半角文字のテストがあれば、testに置換する処理をしています。
ファイル名は全角のテストだったのでは?と思われるかもしれませんが、先程の、
Cells(cnt, "b").Value = StrConv(Cells(cnt, "a"), vbNarrow)
で、全角を半角にしています。

つまり、全角カタカナも半角カタカナに置換されてしまっているので、半角のテストを置換するようにしているわけです。

あと、半角全角の入力ミスではなくて、完全に誤植の場合、どうしたらいいですか?とよく質問があるのですが、誤植のケースが無限大ですから、直しようがありません。

Cells(cnt, "b").Value = Replace(Cells(cnt, "b").Value, " ", "")
この行は、全角・半角を問わず、空白を削除する行です。

Replaceではなくて、Trimじゃないの?と思いますが、Trimは、文字列の前後の空白は削除しますが、文字列の途中の空白は削除しません。

連続する空白でも、空白一つは残してしまうので、Replaceがオススメです。

【ファイル名を置換する】
For cnt = 1 To EndRow
    fname = Cells(cnt, "a").Value
    Newfname = Cells(cnt, "b").Value
     
    fname2 = buf2 & fname
    Newfname2 = buf2 & Newfname
    If Right(Newfname, 1) = "x" Then
        Name fname2 As Newfname2
    End If
Next

いよいよ、整えたファイル名を使って置換する処理です。

fnameには、A1のデータを代入します。同じように、Newfnameには、B1のデータを代入します。

パスを付けないと、どこにファイルがあるのか、Excelはわかりませんので、
fname2に、パス+A1として、パスが付いたファイル名を作ることができます。

Newfname2も同じような処理をしています。

なお、
buf2 = "C:\Users\ Desktop\ExceVBA\"
は、ここで使うために設定してあったわけです。

If Right(Newfname, 1) = "x" Then
    Name fname2 As Newfname2
End If
意図しないものを変換しないようにします。

拡張子の最後の一文字が、「x」でない場合は、置換させないようにします。

Name fname2 As Newfname2
Name ~ Asを使うことで、ファイル名を置換することが簡単にできます。

このように、Excelそのものでなくても、Excel VBAを使うことで、ファイル名も一括で修正することができます。

6/29/2019

Officeソフトのスキルアップに追加しました。2019/6/29

Officeソフトのスキルアップに追加しました。

<目次サイト>

このBLOGの記事を、
目次サイトの【Officeソフトのスキルアップ】に追加しました。

Excel。セル内の文字が含まれているかのチェックは簡単に出来ません。

セルの文字列に該当する文字が含まれているのかチェックしたいので、IF関数を使ってみたんだけど、うまくいかないので教えてほしい。

<続きはこちら>
Excel。セル内の文字が含まれているかのチェックは簡単に出来ません。前編
https://infoyandssblog.blogspot.com/2014/12/excel_30.html

Excel。セル内の文字が含まれているかのチェックは簡単に出来ません。後編
https://infoyandssblog.blogspot.com/2015/01/excel.html

Excel。折れ線グラフでランキング推移グラフ作る時のポイント

折れ線グラフを使ってランキング推移グラフをご紹介しようと思います。
<続きはこちら>
Excel。折れ線グラフでランキング推移グラフ作る時のポイント
https://infoyandssblog.blogspot.com/2014/12/excel.html

6/27/2019

Excel。セルの部分一致が複数条件の時はAND関数が使えない【Partial Match】

Excel。セルの部分一致が複数条件の時はAND関数が使えない

<IF+COUNTIFS関数>

新宿店の女性の顧客というような複数条件のデータをわかるようにする場合は、IF+AND関数を使うことで、簡単に算出することができるわけですが、AND関数が意外とオールマイティーではなくて、現場で困ることがあります。

今回は、その例をご紹介していきます。
次の表があります。

例えば、顧客コードの第2レベルが18で、住所は、川崎市のデータには”○”を表示させるようにしたいわけです。

簡単にいかない理由は、セルの一部のデータが含まれているかどうか、ということなわけです。しかも、複数列。

先程の、新宿店の女性のデータということならば、
=IF(AND(A3=”新宿店”,B3=”女性”),”○”,””)
というような数式を作れば、いいわけです。

ところが、セルの一部のデータが複数列で含まれているという場合は、AND関数が使えません。

では、C3に数式をIF+AND関数で作って確認してみましょう。
C3に作成する数式は、
=IF(AND(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")

MID関数は、セル内の文字データの何文字目からいくつ目までの文字というように文字データの途中を算出する関数ですね。

では、オートフィルを使って数式をコピーしてみましょう。

何も表示されません。
確認のために、C3の数式をAND関数ではなくて、OR関数に変更してみましょう。

=IF(OR(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")
としてみると、算出されていますので、AND関数内の引数にミスはないわけです。

AND関数は、完全一致を求めるので、セル内の一部では使うことができないようです。

【COUNTIFS関数を使う】
そこで、今回のようなケースの場合は、COUNTIFS関数を使うことで解決できます。

D3をクリックして、IF関数ダイアログボックスを表示させます。

論理式に、COUNTIFS関数をネストします。

COUNTIFS関数ダイアログボックスが表示されますので、設定していきます。

検索条件範囲1には、A3。通常COUNTIFS関数は、A3:A10のような範囲選択をしますが、このセルの値がということなので、一つだけのセルを選択します。

検索条件1には、"*18*"
*(アスタリスク)はワイルドカードですね。
前後で挟めば、挟んだ文字列を含むという意味ですね。

検索条件範囲2には、B3。
検索条件2には、"*川崎市*"

IF関数ダイアログボックスに戻ります。

値が真の場合には、”○”
値が偽の場合には、”” 空白を設定して、OKボタンをクリックします。

オートフィルを使って数式をコピーしましょう。

D3の数式は、
=IF(COUNTIFS(A3,"*18*",B3,"*川崎市*"),"○","")
となっています。

今回は、条件に合致するものを算出することができました。

今回のように、セル内の一部のデータが複数条件で合致するものを見つけたい場合には、IF+AND関数ではなくて、IF+COUNTIFS関数を使うと解決できます。

6/26/2019

Excel Technique_BLOG Categoryに追加しました。2019/06/26

Excel Technique_BLOG Categoryに追加しました。

<目次サイト>

このBLOGの記事を、
カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。誕生日から満年齢を求めるのには、DATEDIF関数を使ってみる。

年齢や期間を求めるには、DATEDIF関数を使う方法が有名なので、DATEDIF関数を紹介します。

<続きはこちら>
Excel。誕生日から満年齢を求めるのには、DATEDIF関数を使ってみる。
https://infoyandssblog.blogspot.com/2014/08/exceldatedif.html

Excel。誕生日から満年齢を求めるのにDATEDIF関数よりもYEARFRAC関数はいかが?

このYEARFRAC関数もなじみがないと思いますが、このYEARFRAC関数は、関数の挿入ダイアログボックスから検索して、YEARFRAC関数のダイアログボックスも表示されますので、数式を作成する場合は、YEARFRAC関数のほうがわかりやすいという面もあります。

<続きはこちら>
Excel。誕生日から満年齢を求めるのにDATEDIF関数よりもYEARFRAC関数はいかが?
https://infoyandssblog.blogspot.com/2014/08/exceldatedifyearfrac.html

Excel。グラフの中に吹き出しを描くと取り出せない。

グラフの中に、吹き出しとかの図形を描くと、グラフから描いた図形を取り出せない

<続きはこちら>
Excel。グラフの中に吹き出しを描くと取り出せない。
https://infoyandssblog.blogspot.com/2014/08/excel_22.html

Excel。グラフの凡例の項目を替えることを知っていますか?

グラフの凡例を今回は、替えることが出来ますよ。ということを紹介していきましょう。

<続きはこちら>
Excel。グラフの凡例の項目を替えることを知っていますか?
https://infoyandssblog.blogspot.com/2014/09/excel_8.html

6/24/2019

Excel。一部だけ太い縦棒グラフを描くことはできますか?【Vertical bar chart】

Excel。一部だけ太い縦棒グラフを描くことはできますか?

<部分要素変更縦棒グラフ>

数字ばかりの表よりも、グラフを使うことで、わかりやすい資料を作ることができますが、こういうグラフを描きたいと思っても、簡単にできないグラフがあります。

今回取り上げるのは、一部のデータだけが太い縦棒グラフ。

棒グラフを要素とかいいますので、「部分要素強調縦棒グラフ」、または、「太さが異なる縦棒グラフ」とでもいうグラフです。
部分要素強調縦棒グラフ

【できないことを確認してみよう】

簡単でないことを確認したいと思いますので、次のデータを用意します。

このデータを使って、集合縦棒グラフを描きます。

新宿のデータだけ太さを変更したいので、新宿のデータだけを選択します。

グラフツールの書式タブから選択対象の書式設定をクリックすると、右側にデータ要素の書式設定作業ウィンドウが表示されます。

系列のオプションにある、「要素の間隔」を減少させれば、棒グラフは太くなるはずです。

縦棒グラフは太くはなったものの…すべてが、太くなってしまいました。

これは、今回やりたいこととは違います。

縦棒グラフの色を一部だけ変更するならば、選択した要素(棒グラフ)だけを変更することができますが、太さは変えることができません。

そこで、搭乗するテクニックが、『第2軸』。
まぁ、困ったときには、第2軸でどうにかできないかな?と考えてみるといいかもしれませんね。

では、データを次のように変更します。

A1:C6を範囲選択します。

集合縦棒グラフを描いてから第2軸にするのは効率が悪いので、挿入タブのグラフにある、「すべてのグラフを表示」をクリックします。

グラフの挿入ダイアログボックスが表示されますので、すべてのグラフの組み合わせを選択します。

系列名のダミーをグラフの種類を「集合縦棒」、第2軸にチェックマークをいれて、OKボタンをクリックします。

グラフタイトルと凡例は、今回わかりやすくするために削除しておきます。

先程と同様に、新宿の縦棒グラフだけを選択します。

グラフツールの書式タブから選択対象の書式設定をクリックすると、右側にデータ要素の書式設定作業ウィンドウが表示されます。

今回は、新宿だけ太さを変更することができましたね。

あとは、第2軸の縦軸を選択して削除しましょう。
太さが異なる縦棒グラフ

このように、一部だけを強調した縦棒グラフを描くことができました。

あとは、データラベルを表示させるとか、フォントを大きくするとか、見栄えを調整するといいですね。

6/23/2019

今週のFacebookページの投稿 2019/6/17-2019/6/23

今週のFacebookページの投稿 2019/6/17-2019/6/23

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

6月17日
Excel。アクティブセルの太枠の左部線上で、ダブルクリックをするとデータ範囲の左端列に移動できますね。

6月18日
Excel。アクティブセルの太枠の右部線上で、ダブルクリックをするとデータ範囲の右端列に移動できますね。

6月19日
Excel。名前ボックスにセル番地を入力して、Enterキーを押すと、そのセル番地にジャンプできますね。

6月20日
Excel。シート名は31文字までOKです。全角半角は関係ありませんね。

6月21日
Excel。シート名には、:(コロン)は使えませんね。

6月22日
Excel。シート名には、¥(円マーク)は使えませんね。

6月23日
Excel。シート名には、/(スラッシュ)は使えませんね。ということは、日付。2013/1/1はNGなんですね。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

6/21/2019

Excel。こんなときこそ、マクロ!五十音順など一文字目が変わったら中見出し行を挿入したい【Excel VBA】

Excel。こんなときこそ、マクロ!五十音順など一文字目が変わったら中見出し行を挿入したい

<Excel VBA>

Excel関数一覧表を作ってみたけども、関数名が連続してわかりにくいので、最初の一文字目が変わったら、一行挿入して、中見出し行をいれると、わかりやすくなるかなぁ~と思ったとします。

NUMBERVALUEの次が、OCT2BINなので、OCT2BINの行の上に、Oという文字の入った行を挿入したいわけです。
完成させたいのは、次のような表。

たしかに、自力で作ろうとしても、たいした処理じゃないわけですよ。
一文字目が変わるところを視認したら、行を挿入して、先頭の一文字を入力すればいいわけですね。

だけど、A~Zまでの計26回同じ処理を繰り返すのは、はっきり言って、面倒なんですね。

このように、単純な処理の繰り返し。
そして、面倒な時こそ、マクロで処理させることができるといいですよね。

ということで、Excel VBAでマクロを作成してみましょう。比較的簡単に作成することができますよ。

Sub 先頭文字()
    Dim i As Long
    Dim 関数名 As String
    Dim 次 As String
   
    関数名 = Left(Range("a1"), 1)
    Range("a1").Select
    i = 2
     
    Do While 関数名 <> ""
        次 = Left(Cells(i, "a"), 1)
       
        If 関数名 <> 次 Then
            Rows(i).Insert , xlFormatFromRightOrBelow
            Cells(i, "a").Value = 次
        End If
        i = i + 1
        関数名 = Left(Cells(i - 1, "a"), 1)
    Loop
End Sub

では、説明していきましょう。
最初は、おなじみの変数の宣言ですね。
Dim i As Long
Dim 関数名 As String
Dim 次 As String

関数名や次など、変数名は漢字でも大丈夫ですよ。わかりやすい変数名にするのがExcel VBAになれていない人にはいいかもしれませんね。

続いて、用意した変数などに、初期値を設定します。
関数名 = Left(Range("a1"), 1)
A1の値は、関数名と入力されています。

この関数名のLeft=左から1文字分を、変数の関数名に入力します。
つまり、”関”という文字が入ります。

i = 2
下のセルの情報を得たりするために使う変数です。
2で設定しているのは、このあとで説明します。

Do While 関数名 <> ""
    次 = Left(Cells(i, "a"), 1)
       
    If 関数名 <> 次 Then
        Rows(i).Insert , xlFormatFromRightOrBelow
        Cells(i, "a").Value = 次
    End If
    i = i + 1
    関数名 = Left(Cells(i - 1, "a"), 1)
Loop
このマクロの心臓部です。
Do While~Loopは繰り返し作業ですね。

今回は、関数名 <> "" 関数名が空白でない ということなので、関数名が空白でない間繰り返すということを意味しています。

次 = Left(Cells(i, "a"), 1)
先程、iには2を設定していますので、この行は、A2の左から1文字目を”次”という変数に設定する作業をしています。

この処理で、”M”が入ります。

If 関数名 <> 次 Then
    Rows(i).Insert , xlFormatFromRightOrBelow
    Cells(i, "a").Value = 次
End If

If 関数名 <> 次 Then~End If
関数名には、”関”、次には、”M”が設定されていますので、異なっていますよね。

「<>」異なっている場合は、IFの中を実行します。
合致しているならば、処理しません。

Rows(i).Insert , xlFormatFromRightOrBelow
Rows(i)「2行目」をInsert「挿入」。つまり、2行目を選択して、挿入なので、行の挿入をします。

Cells(i, "a").Value = 次
次に入っている文字をA2に入力します。これで、”M”が入ります。

xlFormatFromRightOrBelow は、右または下方向のセルの書式をコピーすることができます。
これを入れないと、1行目が見出し行で、その塗りつぶしの書式を反映してしまうので、設定する必要がありました。

なお、xlFormatFromLeftOrAbove と設定すると、左または上方向のセルの書式をコピーすることができます。

IF Then EndIfを抜けた後の処理の確認もしておきましょう。
i = i + 1
次の行に移動するために、+1します。

関数名 = Left(Cells(i - 1, "a"), 1)
関数名も、ずっと、「関」ではマズいので、一行ずつスライドさせていきます。

人力で処理している流れをそのままExcel VBAで表現した感じ作ることができますので、少しずつ、Excel VBAに慣れていくといいかもしれませんね。

6/20/2019

Excel関数辞典 VOL.13。CONVERT関数~COTH関数

Excel関数辞典 VOL.13。CONVERT関数~COTH関数

<Excel関数>

今回は、CONVERT関数~COTH関数までをご紹介しております。

CONVERT関数は、結構お馴染みの関数ですね。単位変換で大活躍!

CONVERT関数
コンバート
数値の単位を変換する
CONVERT(数値,変換前単位,変換後単位)
1ヤードをメートルに変換することができる関数ですね。

CORREL関数
コーレル
2組のデータの相関関係を算出
CORREL(配列1,配列2)

COS関数
コサイン
角度の余弦(コサイン)を算出
COS(数値)
引数の"数値"は角度ですね。

COSH関数
ハイパーポリック コサイン
数値の双曲線余弦を算出
COSH(数値)

COT関数
コタンジェント
角度の余接を算出
COT(数値)

COTH関数
ハイパーポリック コタンジェント
数値の双曲線余接を算出
COTH(数値)


YandSシステムズのExcel関数一覧表
https://sites.google.com/view/yandsssystems/function?authuser=0

6/18/2019

Excel。今ふたたびのVLOOKUP関数。アイテムが増えたら数式直すのが面倒なんですが…【VLOOKUP】

Excel。今ふたたびのVLOOKUP関数。アイテムが増えたら数式直すのが面倒なんですが…

<名前の定義とテーブル>

VLOOKUP関数の数式の作り方は、それほど難しくないのですが、実際の現場レベルで運用するとなると、面倒なことが発生します。

次の表はVLOOKUP関数を設定してありますので、確認してみましょう。

C列には、
=IF(B2="","",VLOOKUP(B2,リスト!$A$2:$C$4,2,FALSE))
という数式が設定済みです。

B列には、入力規則のリストを使って、入力ミスの抑制と入力作業の緩和をしています。

そして、別のシートに商品リストがあります。


【商品が増えたら範囲を修正】

ExcelのVLOOKUP関数としては、IF+VLOOKUP関数にしているなど、エラーも発生しないので、問題はありませんが、現場レベルでは、大問題が潜んでいます。

それは単純なことで、商品アイテムが増えたらどうするの?ということです。

別にVLOOKUP関数の引数の範囲を拡張するのと、入力規則のリストの範囲も拡張する必要があります。別に大した処理ではありませんが、VLOOKUP関数を設定してる箇所が多いと修正箇所も増えるので、面倒です。

【名前の定義をつかってみる】

そこで、範囲に「名前」を設定してみましょう。今回は2か所設定します。

一つ目は、A2:A4を範囲選択して、名前ボックスをクリックして、「名前」=あだ名を入力することで設定できます。

今回は、『入力コード』としました。見出し名と被らないようにするといいですね。

この一つ目は、入力規則のリストの為です。
二つ目も設定してきましょう。

A2:C4を範囲選択して、『商品リスト』と名前を設定します。

【数式を「名前」を使って修正する】

数式を修正していきます。
VLOOKUP関数ダイアログボックスを表示しましょう。

範囲を一度削除して、数式タブの「数式で使用」にある、商品リストをクリックします。

ダイアログボックスはOKをクリックして、数式が次のように変わったことを確認してみましょう。

=IF(B2="","",VLOOKUP(B2,商品リスト,2,FALSE))
このように、範囲を設定した「名前」で置き換えても、結果は変わりません。

入力規則のリストも修正します。

B2:B4を範囲選択して、データの入力規則のダイアログボックスを表示します。

元の値に、先程と同様に、数式タブの「数式で使用」にある、入力コードを選択します。

そして、OKボタンをクリックします。

あと少し処理をすれば、完成します。
今やっているのは、範囲に名前を付けただけにすぎません。

結局商品アイテムが増えれば、設定した名前の範囲を修正しなければいけません。

それでは、中途半端な改善になってしまいます。

【テーブルにする】

商品アイテムの表をテーブルにします。これで、完成します。

では、シートを移動して、A1をクリックして、挿入タブから「テーブル」を選択します。

すると、テーブルの作成ダイアログボックスが表示されますので、OKボタンをクリックします。

表がテーブルになりました。

これで、商品アイテムが増えても、数式を修正する必要はありません。

このように、名前の定義とテーブルを組合すことで、作業効率を改善することができます。

6/17/2019

今週のFacebookページの投稿 2019/6/10-2019/6/16

今週のFacebookページの投稿 2019/6/10-2019/6/16

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

6月10日
Excel。ショートカット。
PageUpキーを押すと、1画面単位で上に移動できますね。

6月11日
Excel。ショートカット。
PageDownキーを押すと、1画面単位で下に移動できますね。

6月12日
Excel。ショートカット。
Altキー+PageUpキーを押すと、1画面単位で左に移動できますね。

6月13日
Excel。ショートカット。
Altキー+PageDownキーを押すと、1画面単位で右に移動できますね。

6月15日
Excel。アクティブセルの太枠の上部線上で、ダブルクリックをするとデータ範囲の先頭行に移動できますね。

6月16日
Excel。アクティブセルの太枠の下部線上で、ダブルクリックをするとデータ範囲の最終行に移動できますね。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

6/15/2019

Excel。ツリーマップグラフのアレンジがうまく作れないということで。【Tree map】

Excel。ツリーマップグラフのアレンジがうまく作れないということで。

<ツリーマップ>

Office365に追加されて、Excel2019から標準搭載された、新しいグラフの【ツリーマップ】。
ツリーマップ

データの強弱を面(タイル)の大きさでわかるようにするというグラフなのですが、ちょっとアレンジをしようとすると、なかなかうまくいかないということで、今回は【ツリーマップ】について紹介していきます。

今回は、上記にある、ツリーマップを関東と関西にわけて、次のようなツリーマップを作ってきます。

Excelでグラフを作るときの基本。
それは作りたいグラフ用のデータを作る必要があります。

その表がこれです。

第一カテゴリーのA列の地域。A3:A7とA8:A11のセルは結合しています。
第二カテゴリーのB列の店舗
売上高のC列
A列がないと、最初のツリーマップのように、地域別に分けることができません。

【ツリーマップをつくろう】

A2:C11を範囲選択します。

挿入タブのグラフにある「階層構造グラフの挿入」から「ツリーマップ」をクリックします。

ツリーマップグラフが挿入されました。

とりあえず完成しました。初期状態のツリーマップでは、第一カテゴリーが埋没していてわかりにくいので、修正していきます。

グラフツールのデザインタブにある「クイックレイアウト」をクリックして、レイアウト2をクリックします。

ツリーマップのデザインはクリックレイアウトを使うと効率的に変更することができます。

今回はグラフタイトルがあると、ツリーマップ自体が小さく表示されてしまうので、グラフタイトルは削除しておきます。

【データラベルに値を表示しよう】

データラベルは、店舗名だけが表示されています。

面積で強弱を表すグラフといっても、数値は表示すると、よりわかりやすくなります。

グラフツールのデザインタブにある「グラフ要素を追加」をクリックして、データラベルから「その他のデータラベルオプション」をクリックします。

画面の右側に、データラベルの書式設定作業ウィンドウが表示されます。

ラベルオプションに値を追加しますので、値にチェックマークをつけて、区切り文字は、改行で表示させましょう。

あとは、データラベルのフォントサイズを調整して完成ですね。関東の合算値が関西よりも大きいので、関東の方が全体的に広くなっているなど、わかりやすいグラフです。

このツリーマップは通常のグラフと異なって、完成後にサイズを変えると、Webのレスポンシブデザインのように、グラフが変化します。

縦長にグラフサイズを変更してみると、上下にカテゴリー分けされたツリーマップになりました。

なかなか面白いですね。ただしこのデータラベル、タイルの左下に表示されていますよね。

これをタイルの中央に配置したいと思っても、移動させることができません。
たぶん、レスポンシブデザインのようになっていからだと思われます。

Excelもバージョンアップするたびに、色々変わっていますので、確認してみると、現場レベルで使えるものを発見できるかもしれませんね。